Vendor Ledger Report Query

SELECT flag,

  vendor_num,

  vendor_name,

  vendor_site_id,

  vendor_site_code,

  state_code,

  project_code,

  transaction_type,

  transaction_num,

  doc_category_name,

  document_num,

  po_number,

  transaction_date,

  gl_date,

  status,

  description,

  currency,

  SUM(entered_dr) entered_dr,

  SUM(entered_cr) entered_cr,

  SUM(accounted_dr) accounted_dr,

  SUM(accounted_cr) accounted_cr

FROM

  (SELECT 1 flag,

    ap.invoice_id invoice_id,

    aps.segment1 vendor_num,

    aps.vendor_name vendor_name,

    ap.VENDOR_SITE_ID vendor_site_id,

    apss.vendor_site_code vendor_site_code,

    apss.state state_code,

    gl.segment2 project_code,

    ap.invoice_type_lookup_code transaction_type,

    ap.invoice_num transaction_num,

    fdsc.name doc_category_name,

    ap.voucher_num document_num,

    (SELECT poh.segment1

    FROM po_headers_all poh,

      po_distributions_all pod,

      ap_invoice_distributions_all apd

    WHERE poh.po_header_id    =pod.po_header_id

    AND pod.po_distribution_id=apd.po_distribution_id

    AND apd.invoice_id        =ap.invoice_id

    AND rownum                =1

    ) po_number,

    ap.invoice_date transaction_date,

    ap.gl_date gl_date,

    DECODE(ap_invoices_pkg.get_posting_status( ap.invoice_id),'Y','Accounted','P','Partial','Unaccounted') status,

    ap.description description,

    ap.invoice_currency_code currency,

    DECODE(xl.accounting_class_code,'LIABILITY',NVL(xl.entered_dr,0),'PREPAID_EXPENSE',-1*xl.entered_cr,0) entered_dr,

    DECODE(xl.accounting_class_code,'LIABILITY',NVL(xl.entered_cr,0),0) entered_cr,

    DECODE(xl.accounting_class_code,'LIABILITY',NVL(xl.accounted_dr,0),'PREPAID_EXPENSE',-1*xl.accounted_cr,0) accounted_dr,

    DECODE(xl.accounting_class_code,'LIABILITY', NVL(xl.accounted_cr,0),0) accounted_cr

  FROM ap_invoices_all ap ,

    ap_suppliers aps,

    ap_supplier_sites_all apss,

    gl_code_combinations gl,

    FND_DOC_SEQUENCE_CATEGORIES FDSC,

    xla.xla_transaction_entities xte,

    xla_events xe,

    xla_ae_lines xl,

    xla_ae_headers xah

  WHERE ap_invoices_pkg.get_posting_status( ap.invoice_id) IN ('Y','P')

  AND ap.invoice_type_lookup_code                          <>'PREPAYMENT'

  AND gl.code_combination_id                                =ap.accts_pay_code_combination_id

  AND FDSC.CODE (+)                                         = ap.DOC_CATEGORY_CODE

  AND FDSC.table_name (+)                                   = 'AP_INVOICES_ALL'

  AND aps.VENDOR_ID                                         = ap.VENDOR_ID

  AND ap.VENDOR_SITE_ID                                     =apss.VENDOR_SITE_ID

  AND TRUNC(xah.accounting_date) BETWEEN :CP_GL_FROM_DATE AND :CP_GL_TO_DATE

  AND ap.org_id                   =:p_org_id

  AND ap.vendor_id                =:p_vendor_id

  AND source_id_int_1             =ap.invoice_id

  AND xte.SOURCE_ID_INT_1         =ap.invoice_id

  AND xte.ENTITY_CODE             ='AP_INVOICES'

  AND xe.entity_id                =xte.entity_id

  AND xe.event_id                 =xah.event_id

  AND xah.gl_transfer_status_code ='Y'

  AND xl.ae_header_id             =xah.ae_header_id

  AND xl.accounting_class_code   IN ('LIABILITY','PREPAID_EXPENSE')

  )

GROUP BY flag,

  invoice_id,

  vendor_num,

  vendor_name,

  vendor_site_id,

  vendor_site_code,

  state_code,

  project_code,

  transaction_type,

  transaction_num,

  doc_category_name,

  document_num,

  po_number,

  transaction_date,

  gl_date,

  status,

  description,

  currency

  

  UNION ALL

  

SELECT flag,

  vendor_num,

  vendor_name,

  vendor_site_id,

  vendor_site_code,

  state_code,

  project_code,

  transaction_type,

  transaction_num,

  doc_category_name,

  document_num,

  po_number,

  transaction_date,

  gl_date,

  status,

  description,

  currency,

  SUM(entered_dr) entered_dr,

  SUM(entered_cr) entered_cr,

  SUM(accounted_dr) accounted_dr,

  SUM(accounted_cr) accounted_cr

FROM

  (SELECT 2 flag,

    apc.check_id check_id,

    aps.segment1 vendor_num,

    aps.vendor_name vendor_name,

    -- null pan,

    apc.VENDOR_SITE_ID vendor_site_id,

    apss.vendor_site_code vendor_site_code,

    --NULL gstin,

    apss.state state_code,

    NULL project_code,

    'PAYMENT' transaction_type,

    TO_CHAR( apc.check_number) transaction_num,

    fdsc.name doc_category_name,

    TO_CHAR(apc.check_voucher_num) document_num,

    NULL po_number,

    apc.check_date transaction_date,

    xah.accounting_date gl_date,

    DECODE(xah.gl_transfer_status_code,'Y','Accounted','Unaccounted') status,

    apc.description description,

    apc.currency_code currency,

    NVL(xl.entered_dr,0) entered_dr,

    NVL(xl.entered_cr,0) entered_cr,

    NVL(xl.accounted_dr,0) accounted_dr,

    NVL(xl.accounted_cr,0) accounted_cr

  FROM ap_checks_all apc ,

    xla.xla_transaction_entities xte,

    xla_events xe,

    xla_ae_lines xl,

    xla_ae_headers xah,

    ap_suppliers aps,

    ap_supplier_sites_all apss,

    FND_DOC_SEQUENCE_CATEGORIES FDSC

  WHERE xah.gl_transfer_status_code ='Y'

  AND FDSC.CODE (+)                 = apc.DOC_CATEGORY_CODE

  AND FDSC.table_name (+)           = 'AP_CHECKS_ALL'

  AND xte.SOURCE_ID_INT_1           =apc.check_id

  AND xte.ENTITY_CODE               ='AP_PAYMENTS'

  AND xe.entity_id                  =xte.entity_id

  AND xe.event_id                   =xah.event_id

  AND xl.ae_header_id               =xah.ae_header_id

  AND xl.accounting_class_code      ='LIABILITY'

  AND aps.VENDOR_ID(+)              = apc.VENDOR_ID

  AND apc.VENDOR_SITE_ID            =apss.VENDOR_SITE_ID (+)

  AND TRUNC(xah.accounting_date) BETWEEN :CP_GL_FROM_DATE AND :CP_GL_TO_DATE

  AND apc.org_id    =:p_org_id

  AND apc.vendor_id =:p_vendor_id

  )

GROUP BY flag,

check_id,

  vendor_num,

  vendor_name,

  vendor_site_id,

  vendor_site_code,

  state_code,

  project_code,

  transaction_type,

  transaction_num,

  doc_category_name,

  document_num,

  po_number,

  transaction_date,

  gl_date,

  status,

  description,

  currency

  

ORDER BY vendor_num,

  vendor_site_code,

  gl_date,

  transaction_date;