7 Replies Latest reply: Jun 27, 2012 8:01 AM by O.Developer RSS

    How to get sales order data with GL tables

    899296
      I want to get sales order details from my GL query. I need to get item number, quantity and customer PO from there. Here is my query from GL.
      SELECT PH.po_header_id,
        I.GL_DATE,
        R.je_header_id JE_HEADER_ID,
        R.je_line_num JE_LINE_NUM,
        aeh.org_id ORG_ID ,
        I.invoice_num TRX_NUMBER_C ,
        ael.description COMMENTS ,
        ael.ACCOUNTED_CR ACCOUNTED_CR,
        ael.ACCOUNTED_DR ACCOUNTED_DR,
        ael.code_combination_id CODE_COMBINATION_ID,
        ael.currency_code CURRENCY_CODE,
        ael.entered_cr ENTERED_CR,
        ael.entered_dr ENTERED_DR,
        PH.segment1 PO_ORDER_NUMBER,
        GL.segment1 company,
        GL.segment2,
        GL.segment3,
        GL.segment4,
        GL.segment5 account
        ,ael.description
        ,PD.quantity_ordered, PD.amount_billed, D.quantity_invoiced,  D.unit_price
      FROM gl_import_references R, --ok
        gl_je_categories jc,
        ap_ae_lines_all AEL, --ok
        ap_ae_headers_all AEH, --ok
        ap_invoice_distributions_all D,
        ap_invoices_all I,
        ap_accounting_events_all AE,
        po_releases_all PR,
        po_headers_all PH,
        po_distributions_all PD,
        po_vendor_sites_all VS,
        gl_code_combinations GL
      WHERE 1=1
      AND jc.je_category_name       = aeh.ae_category
      AND PR.po_release_id(+)       = PD.po_release_id
      AND PH.po_header_id(+)        = PD.po_header_id
      AND PD.po_distribution_id(+)  = D.po_distribution_id
      AND ael.third_party_sub_id    = VS.vendor_site_id
      AND AE.accounting_event_id    = AEH.accounting_event_id
      AND I.invoice_id              = AE.source_id
      AND AE.source_table           = 'AP_INVOICES'
      AND AEH.ae_header_id          = AEL.ae_header_id
      AND DECODE(ael.source_table,'AP_INVOICE_DISTRIBUTIONS', AEL.source_id,NULL) = D.invoice_distribution_id(+)
      AND AEL.gl_sl_link_id         = R.gl_sl_link_id
      and ael.code_combination_id = GL.code_combination_id