SELECT ooha.order_number, ooha.ordered_date sales_order_date,
rc.customer_name, rc.customer_number,
bill_loc.address1 invoice_to_address1,
bill_loc.address2 invoice_to_address2,
bill_loc.address3 invoice_to_address3,
bill_loc.address4 invoice_to_address4,
DECODE (bill_loc.city,
NULL, NULL,
bill_loc.city || ', '
)
|| DECODE (bill_loc.state,
NULL, bill_loc.province,
bill_loc.state || ', '
)
|| DECODE (bill_loc.postal_code,
NULL, NULL,
bill_loc.postal_code || ', '
)
|| DECODE (bill_loc.country, NULL, NULL, bill_loc.country)
invoice_to_address5,hl.address1 ship_to_address1, hl.address2 ship_to_address2,
hl.address3 ship_to_address3, hl.address4 ship_to_address4,
DECODE (hl.city, NULL, NULL, hl.city || ', ')
|| DECODE (hl.state, NULL, hl.province || ', ', hl.state || ', ')
|| DECODE (hl.postal_code, NULL, NULL, hl.postal_code || ', ')
|| DECODE (hl.country, NULL, NULL, hl.country) ship_to_address5,
ooha.cust_po_number,ras.NAME sales_person, ooha.sales_channel_code,
oos.NAME Order_Source,
ooha.flow_status_code Header_Order_Status,
ot.NAME order_type,
ooha.freight_terms_code header_fright_terms,
ooha.fob_point_code Header_FOB,
ship_from_org.organization_code ship_from, term.NAME payment_terms,
SUM (oola.ordered_quantity * oola.unit_selling_price) sub_total,
SUM (oola.tax_value) tax_value,
SUM ( NVL (oola.ordered_quantity, 1)
* NVL (oola.unit_selling_price, 1)
+ oola.tax_value
) total
FROM hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hcas,
hz_party_sites hps,
hz_parties hp,
hz_locations hl,
oe_order_headers_all ooha,
oe_order_lines_all oola,
ra_customers rc,
ra_salesreps_all ras,
mtl_parameters ship_from_org,
ra_terms_tl term,
hz_cust_site_uses_all bill_su,
hz_party_sites bill_ps,
hz_locations bill_loc,
oe_order_sources oos,
hz_cust_acct_sites_all bill_cas,
oe_transaction_types_tl ot
WHERE ooha.order_type_id = ot.transaction_type_id
AND ot.LANGUAGE = USERENV ('LANG')
AND oola.ship_to_org_id = hcsu.site_use_id
AND ooha.payment_term_id = term.term_id(+)
AND term.LANGUAGE(+) = USERENV ('LANG')
AND ooha.ship_from_org_id = ship_from_org.organization_id(+)
AND ooha.salesrep_id = ras.salesrep_id
AND rc.customer_id = ooha.sold_to_org_id
AND ooha.order_source_id = oos.order_source_id
AND ooha.header_id = oola.header_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.party_site_id = hps.party_site_id
AND hps.party_id = hp.party_id
AND hl.location_id = hps.location_id
AND ooha.invoice_to_org_id = bill_su.site_use_id(+)
AND bill_su.cust_acct_site_id = bill_cas.cust_acct_site_id(+)
AND bill_cas.party_site_id = bill_ps.party_site_id(+)
AND bill_loc.location_id(+) = bill_ps.location_id
AND ooha.ordered_date BETWEEN :date1 AND :date2
AND rc.customer_name = :cust
GROUP BY ooha.order_number,
term.NAME,
ship_from_org.organization_code,
ooha.freight_terms_code,
ooha.ordered_date,
ras.NAME,
ooha.sales_channel_code,
rc.customer_name,
ooha.cust_po_number,
rc.customer_number,
hl.address1,
hl.address2,
hl.address3,
hl.address4,
hl.city,
hl.country,
hl.state,
hl.province,
hl.postal_code,
bill_loc.address1,
bill_loc.address2,
bill_loc.address3,
bill_loc.address4,
bill_loc.city,
bill_loc.state,
bill_loc.postal_code,
bill_loc.country,
bill_loc.province,
oos.NAME,
ooha.flow_status_code,
ot.NAME,
ooha.fob_point_code
0 comments:
Post a Comment