O2C QUERY

SELECT OOH.HEADER_ID,
       HCSUA.LOCATION SHIPTO,
       HCSUA1.LOCATION BILTO,
      -- HL.ADDRESS1||','||CHR(10)||HL.STATE,
       HL.ADDRESS1||' '||HL.STATE||' '||HL.POSTAL_CODE||' '||HL.COUNTRY||' '||HL.CITY,
       HL1.ADDRESS1
FROM  OE_ORDER_HEADERS_ALL         OOH ,
      OE_ORDER_LINES_ALL           OOL,
      QP_LIST_HEADERS              QLH,
      QP_LIST_LINES                QLL,
      HZ_PARTIES                   HP,
      HZ_CUST_ACCOUNTS             HCA,
      HZ_CUST_SITE_USES_ALL        HCSUA,
      HZ_CUST_SITE_USES_ALL        HCSUA1,
      HZ_LOCATIONS                 HL,
      HZ_LOCATIONS                 HL1,
      HZ_PARTY_SITES               HPS,
      HZ_CUST_ACCT_SITES_ALL       HCASA,
      WSH_DELIVERY_DETAILS         WDD,
      WSH_DELIVERY_ASSIGNMENTS     WDA,
      WSH_NEW_DELIVERIES           WND,
      MTL_SYSTEM_ITEMS_B           MSIB,
      RA_CUSTOMER_TRX_ALL          RCTA,
      RA_CUSTOMER_TRX_LINES_ALL    RCTLA,
      RA_CUST_TRX_LINE_GL_DIST_ALL RGDA,
      AR_CASH_RECEIPTS_ALL         ACRA,
      XLA_TRANSACTION_ENTITIES     XTE,
      XLA_EVENTS                   XE,
      XLA_AE_HEADERS               XAH,
      XLA_AE_LINES                 XAL,
      XLA_DISTRIBUTION_LINKS       XDL,
      GL_IMPORT_REFERENCES         GIR,
      GL_JE_BATCHES                GJB,
      GL_JE_HEADERS                GJH,
      GL_JE_LINES                  GJL
WHERE OOH.HEADER_ID=39441
AND   OOH.HEADER_ID=OOL.HEADER_ID
AND   QLH.LIST_HEADER_ID=OOH.PRICE_LIST_ID
AND  QLL.LIST_LINE_ID=OOL.PRICE_LIST_ID
AND  HP.PARTY_ID=HCA.PARTY_ID
AND  HCA.CUST_ACCOUNT_ID=OOH.SOLD_TO_ORG_ID
AND  HCSUA.SITE_USE_ID=OOH.SHIP_TO_ORG_ID
AND  HCSUA1.SITE_USE_ID=OOH.INVOICE_TO_ORG_ID
AND  HL.LOCATION_ID=HPS.LOCATION_ID
AND  HL1.LOCATION_ID=HPS.LOCATION_ID
AND  HPS.PARTY_SITE_ID=HCASA.PARTY_SITE_ID
AND  HCASA.CUST_ACCT_SITE_ID=HCSUA.CUST_ACCT_SITE_ID
AND  WDD.SOURCE_HEADER_ID=OOH.HEADER_ID
AND  WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND  WDA.DELIVERY_ID=WND.DELIVERY_ID
AND  WDD.ORGANIZATION_ID=MSIB.ORGANIZATION_ID
AND  WDD.INVENTORY_ITEM_ID=MSIB.INVENTORY_ITEM_ID
AND  RCTA.INTERFACE_HEADER_ATTRIBUTE1=TO_CHAR(OOH.ORDER_NUMBER)
AND  RCTA.ORG_ID=OOH.ORG_ID
AND  RCTA.CUSTOMER_TRX_ID=RCTLA.CUSTOMER_TRX_ID
AND  RCTLA.SALES_ORDER=TO_CHAR(OOH.ORDER_NUMBER)
AND  RCTLA.CUSTOMER_TRX_LINE_ID=RGDA.CUSTOMER_TRX_LINE_ID
AND  RCTA.CUSTOMER_TRX_ID=RGDA.CUSTOMER_TRX_ID
AND  ACRA.PAY_FROM_CUSTOMER=RCTA.SOLD_TO_CUSTOMER_ID
AND  ACRA.ORG_ID=OOH.ORG_ID
AND  ACRA.RECEIPT_NUMBER='155464'
AND  ACRA.CUSTOMER_SITE_USE_ID=RCTA.BILL_TO_SITE_USE_ID
AND  XTE.TRANSACTION_NUMBER=ACRA.RECEIPT_NUMBER
AND  XTE.ENTITY_CODE='RECEIPT'
AND  XTE.ENTITY_ID=XE.ENTITY_ID
AND  XE.EVENT_ID=XAH.EVENT_ID
AND  XAH.AE_HEADER_ID=XAL.AE_HEADER_ID
AND  XAL.ACCOUNTING_CLASS_CODE='CASH'
AND  XDL.AE_HEADER_ID=XAH.AE_HEADER_ID
AND  XDL.AE_LINE_NUM=XAL.AE_LINE_NUM
--AND  GIR.REFERENCE_5=XTE.ENTITY_ID
AND  GIR.REFERENCE_6=TO_CHAR(XE.EVENT_ID)
AND  GIR.REFERENCE_7=TO_CHAR(XAH.AE_HEADER_ID)
AND  GIR.GL_SL_LINK_ID=XAL.GL_SL_LINK_ID
--AND  GIR.CREATED_BY
AND  GIR.JE_BATCH_ID=GJB.JE_BATCH_ID
AND  GJB.JE_BATCH_ID=GJH.JE_BATCH_ID
AND  GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
--AND  GIR.JE_HEADER_ID=GJH.JE_HEADER_ID
--AND  GIR.JE_LINE_NUM=GJL.JE_LINE_NUM