O2C Cycle Complete Query

O2C Cycle Complete Query, www.askhareesh.com

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_IDAND      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

*/