ORDER TO CASH - Complete Query R12

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 = :ORG_ID       

AND OOH.ORDER_NUMBER = :ORDER_NUMBER       

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