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
,OOH.ATTRIBUTE1 HEADER_DDF1,OOH.ATTRIBUTE2 HEADER_DDF2,OOH.ATTRIBUTE3 HEADER_DDF3,OOH.ATTRIBUTE4 HEADER_DDF4
,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 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
,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
,(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,OOH.ATTRIBUTE2 LINE_DDF2,OOH.ATTRIBUTE3 LINE_DDF3,OOH.ATTRIBUTE4 LINE_DDF4
FROM
OE_ORDER_HEADERS_ALL OOH
,OE_ORDER_LINES_ALL OOL
,OE_TRANSACTION_TYPES_TL OT
,AR_CUSTOMERS AC
-- ,MTL_PARAMETERS MP
,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