BEGIN
mo_global.init('ONT');
END;
BEGIN
mo_global.init('ONT');
END;
SELECT ac.customer_name, ac.customer_number, ooh.order_number,
ooh.cust_po_number, ooh.ordered_date,
ooh.flow_status_code status_code, ooh.order_firmed_date,
ood.organization_code warehouse, ooh.fob_point_code fob,
ooh.freight_terms_code, ooh.shipping_method_code,
ooh.shipment_priority_code, ooh.transactional_curr_code curr_code,
ot.NAME order_type,
(SELECT s.NAME
FROM jtf.jtf_rs_salesreps s
WHERE s.org_id = ood.operating_unit
AND s.salesrep_id = ooh.salesrep_id) sales_parson,
(SELECT qpl.NAME
FROM qp_list_headers_vl qpl
WHERE list_header_id = ooh.price_list_id) price_list,
(SELECT rat.NAME
FROM ra_terms rat
WHERE rat.term_id = ooh.payment_term_id) payment_term_name,
ooh.orig_sys_document_ref,
ooh.attribute1 header_ddf1, ooh.attribute2 header_ddf2,
ooh.attribute3 header_ddf3, ooh.attribute4 header_ddf4,
ooh.attribute5 header_ddf5, ooh.attribute6 header_ddf6,
----------------Line Info below-----------
ool.line_number, ool.ordered_item, ool.ordered_quantity,
ool.shipped_quantity, ool.schedule_ship_date,
ool.order_quantity_uom uom, ool.request_date,
ool.flow_status_code line_status_code,
ool.tax_value,
(SELECT transaction_type_code FROM ont.oe_transaction_types_all where transaction_type_id =ool.LINE_TYPE_ID)LINE_TYPE,
(SELECT process_name FROM oe_workflow_assignments WHERE order_type_id = ooh.order_type_id and line_type_id= ool.line_type_id) WF_PROCESS_NAME,
(SELECT NAME
FROM pa_projects_all
WHERE project_id = ool.project_id) project_name,
(SELECT task_name
FROM pa_tasks
WHERE task_id = ool.task_id) task_name, ool.return_reason_code,
ool.reference_type, ool.unit_selling_price unit_price, ool.tax_code,
ool.tax_rate, ool.tax_value, ship_to.address1 ship_to_address1,
ship_to.address2 ship_to_address2, ship_to.address3 ship_to_address3,
ship_to.city ship_to_city, ship_to.state ship_to_state,
ship_to.nls_territory bill_to_territory,
ship_to.postal_code ship_to_postal_code,
ship_to.province ship_to_pin, bill_to.address1 bill_to_address1,
bill_to.address2 bill_to_address2, bill_to.address3 bill_to_address3,
bill_to.city bill_to_city, bill_to.state bill_to_state,
bill_to.nls_territory bill_to_territory,
bill_to.postal_code bill_to_postal_code,
bill_to.province bill_to_pin, ool.attribute1 line_ddf1,
ool.attribute2 line_ddf2, ool.attribute3 line_ddf3,
ool.attribute4 line_ddf4,ool.attribute5 line_ddf5,
ool.attribute6 line_ddf6,
(select lookup_code from apps.oe_lookups where upper (lookup_type) = 'FOB' and enabled_flag = 'Y' and upper (lookup_code) = upper (ooh.fob_point_code) )FOB,
(select lookup_code from apps.oe_lookups where upper (lookup_type) = 'FREIGHT_TERMS' and enabled_flag = 'Y' and upper (lookup_code) = upper (ooh.freight_terms_code) )Freight_Terms,
(Select CHARGE_AMOUNT from OE_CHARGE_LINES_V ocl where ocl.HEADER_ID=ool.HEADER_ID and ocl.LINE_ID=ool.LINE_ID) FREIGHT_CHARGE,
ool.line_category_code,
(SELECT order_number FROM oe_order_headers_all WHERE header_id = ool.return_attribute1) RMA_SALES_ORDER,
(SELECT line_number FROM oe_order_lines_all WHERE header_id = ool.return_attribute1 AND line_id = ool.return_attribute2) RMA_SO_Line_No
FROM oe_order_headers_all ooh,
oe_order_lines_all ool,
oe_transaction_types_tl ot,
ar_customers ac
, org_organization_definitions ood
----------------------Bill To Customer-------------------------------
,(SELECT hp.party_name, hp.party_number, hca.account_number,
hca.cust_account_id, hp.party_id, hps.party_site_id,
hcsu.cust_acct_site_id, hps.location_id, hl.address1,
hl.address2, hl.address3, hl.city, hl.state,
ter.nls_territory, hl.postal_code, hl.province,
hcsu.site_use_code, hcsu.site_use_id, hcsa.bill_to_flag
FROM hz_parties hp,
hz_party_sites hps,
hz_locations hl,
hz_cust_accounts_all hca,
hz_cust_acct_sites_all hcsa,
hz_cust_site_uses_all hcsu,
fnd_territories ter
WHERE hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND hp.party_id = hca.party_id
AND hcsa.party_site_id = hps.party_site_id
AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
AND hca.cust_account_id = hcsa.cust_account_id
AND hl.country = ter.territory_code
AND hcsu.site_use_code = 'BILL_TO') bill_to
----------------------Ship To Customer-------------------------------
,(SELECT hp.party_name, hp.party_number, hca.account_number,
hca.cust_account_id, hp.party_id, hps.party_site_id,
hcsu.cust_acct_site_id, hps.location_id, hl.address1,
hl.address2, hl.address3, hl.city, hl.state,
ter.nls_territory, hl.postal_code, hl.province,
hcsu.site_use_code, hcsu.site_use_id, hcsa.bill_to_flag
FROM hz_parties hp,
hz_party_sites hps,
hz_locations hl,
hz_cust_accounts_all hca,
hz_cust_acct_sites_all hcsa,
hz_cust_site_uses_all hcsu,
fnd_territories ter
WHERE hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND hp.party_id = hca.party_id
AND hcsa.party_site_id = hps.party_site_id
AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
AND hca.cust_account_id = hcsa.cust_account_id
AND hl.country = ter.territory_code
AND hcsu.site_use_code = 'SHIP_TO') ship_to
WHERE ooh.header_id = ool.header_id
AND ooh.order_type_id = ot.transaction_type_id
AND ac.customer_id = ooh.sold_to_org_id
-- AND ooh.flow_status_code = 'BOOKED'
AND ood.organization_id = ooh.ship_from_org_id
AND ood.organization_code = NVL (:p_inv_org_code, ood.organization_code)
AND ooh.order_number = NVL (:p_sales_order_no, ooh.order_number)
AND ship_to.cust_account_id = ooh.sold_to_org_id
AND ship_to.site_use_id = ooh.ship_to_org_id
AND bill_to.cust_account_id = ooh.sold_to_org_id
AND bill_to.site_use_id = ooh.invoice_to_org_id
ORDER BY order_number
/
