INTRODUCTION
This blog explains how we get complete order details about the for a sales order in oracle apps
CODE
SELECT OOH.ORDER_NUMBER
, OOH.FLOW_STATUS_CODE HEADERSTATUS
, OOH.ORDERED_DATE ORDER_DATE
, OTT.NAME ORDER_TYPE
, HP.PARTY_NUMBER CUSTOMER_NUMBER
, HP.PARTY_NAME CUSTOMER_NAME
, QLHT.NAME PRICELIST_NAME
, RSA.NAME SALESREP_NAME
, HCSU1.LOCATION CUST_SHIPTO_LOC
, HL1.ADDRESS1 SHIPTO_ADDRESS
, HL1.CITY SHIPTO_CITY
, HL1.POSTAL_CODE SHIPTO_POSTAL_CODE
, HCSU2.LOCATION CUST_BILLTO_LOC
, HL2.ADDRESS1 BILLTO_ADDRESS
, HL2.CITY BILLTO_CITY
, HL2.POSTAL_CODE BILLTO_POSTAL_CODE
, OOL.ORDERED_ITEM
, OOL.ORDERED_QUANTITY
, OOL.ORDER_QUANTITY_UOM UOM
, OOL.UNIT_SELLING_PRICE UNIT_PRICE
, OOL.FLOW_STATUS_CODE LINESTATUS
, WDD.RELEASED_STATUS RELEASED_STATUS
, WDA.DELIVERY_ID DELIVERYID
, WND.DELIVERY_TYPE DELIVERYTYPE
, RCTA.TRX_NUMBER INVOICENO
, RCTA.TRX_DATE INVOICEDATE
, RCTLA.LINE_TYPE
, ARAA.AMOUNT_APPLIED INVOICE_AMOUNT
, ACRA.RECEIPT_NUMBER RECEIPTNO
, ACRA.RECEIPT_DATE RECEIPTDATE
, ACRA.AMOUNT RECEIPTAMOUNT
, ACRA.TYPE RECEIPTTYPE
, APSA.AMOUNT_APPLIED PAYMENTAMOUNT
, HCAA.ACCOUNT_NUMBER ACCTNO
, HCAA.ACCOUNT_NAME ACCTNAME
FROM OE_ORDER_HEADERS_ALL OOH
, OE_ORDER_LINES_ALL OOL
, OE_TRANSACTION_TYPES_TL OTT
, HZ_PARTIES HP
, QP_LIST_HEADERS_TL QLHT
, RA_SALESREPS_ALL RSA
, HZ_PARTY_SITES HPS
, HZ_CUST_ACCT_SITES_ALL HCAS1
, HZ_CUST_SITE_USES_ALL HCSU1
, HZ_LOCATIONS HL1
, HZ_CUST_ACCT_SITES_ALL HCAS2
, HZ_CUST_SITE_USES_ALL HCSU2
, HZ_LOCATIONS HL2
, MTL_SYSTEM_ITEMS_B MSIB
, WSH_DELIVERY_DETAILS WDD
, WSH_DELIVERY_ASSIGNMENTS WDA
, WSH_NEW_DELIVERIES WND
, RA_CUSTOMER_TRX_ALL RCTA
, RA_CUSTOMER_TRX_LINES_ALL RCTLA
, AR_RECEIVABLE_APPLICATIONS_ALL ARAA
, AR_CASH_RECEIPTS_ALL ACRA
, AR_PAYMENT_SCHEDULES_ALL APSA
, HZ_CUST_ACCOUNTS_ALL HCAA
WHERE OOH.ORG_ID = 204
AND OOH.ORDER_NUMBER = 15025
AND OOH.HEADER_ID = OOL.HEADER_ID
AND OOH.ORDER_TYPE_ID = OTT.TRANSACTION_TYPE_ID
AND OOH.SOLD_TO_ORG_ID = HP.PARTY_ID
AND OOH.PRICE_LIST_ID = QLHT.LIST_HEADER_ID
AND OOH.SALESREP_ID = RSA.SALESREP_ID
–AND HP.PARTY_ID=HPS.PARTY_ID
AND HPS.PARTY_SITE_ID = HCAS1.PARTY_SITE_ID
AND HCAS1.CUST_ACCT_SITE_ID = HCSU1.CUST_ACCT_SITE_ID
AND OOH.SHIP_TO_ORG_ID = HCSU1.SITE_USE_ID
AND HPS.LOCATION_ID = HL1.LOCATION_ID
AND HPS.PARTY_SITE_ID = HCAS2.PARTY_SITE_ID
AND HCAS2.CUST_ACCT_SITE_ID = HCSU2.CUST_ACCT_SITE_ID
AND OOH.SHIP_TO_ORG_ID = HCSU2.SITE_USE_ID
AND HPS.LOCATION_ID = HL2.LOCATION_ID
AND OOL.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND OOL.ORG_ID = MSIB.ORGANIZATION_ID
AND WDD.SOURCE_HEADER_ID = OOH.HEADER_ID –AND WDD.SOURCE_LINE_ID=OOL.LINE_ID
AND WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
AND WDA.DELIVERY_ID = WND.DELIVERY_ID
AND RCTA.CUSTOMER_TRX_ID = RCTLA.CUSTOMER_TRX_ID
AND RCTA.INTERFACE_HEADER_ATTRIBUTE1 = TO_CHAR( OOH.ORDER_NUMBER )
AND RCTLA.INTERFACE_LINE_ATTRIBUTE6 = TO_CHAR( OOL.LINE_ID )
AND ARAA.APPLIED_CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
AND ARAA.CASH_RECEIPT_ID = ACRA.CASH_RECEIPT_ID
AND ARAA.APPLIED_PAYMENT_SCHEDULE_ID = APSA.PAYMENT_SCHEDULE_ID
AND ACRA.PAY_FROM_CUSTOMER = HCAA.CUST_ACCOUNT_ID
