AP Payments Drilldown

SELECT  DISTINCT
    gjh.je_source source,
    gjh.je_category category,
    gjb.name journal,
    gjl.je_line_num line_num,
    gjh.actual_flag actual_flag,
    gjh.period_name period,
    gjl.effective_date gl_date,
    gcc.segment1 fund,
    gcc.segment2 FUNCTION,
    gcc.segment3 object,
    gcc.segment4 sub_object,
    gcc.segment5 org,
    gcc.segment6 yr,
    gcc.segment7 program,
    gjl.entered_dr debit,
    gjl.entered_cr credit,
    Flv.displayed_field type,
    NULL purchase_document_num,
    pv.vendor_name vendor,
    aia.invoice_num,
    aid.distribution_line_number,
    aca.CHECK_NUMBER check_num,
    to_date(aca.check_date) trx_date
    FROM
    ap_payment_history_all aph,
    ap_payment_hist_dists phd,
    xla_distribution_links xdl,
    ap_invoice_distributions_all aid,
    ap_invoices_all aia,
    po_vendors pv,
    ap_checks_all aca,
    xla.xla_ae_lines xal,
    xla.xla_ae_headers xah,
    gl_import_references gir,
    gl_je_lines gjl,
    gl_je_headers gjh,
    gl_je_batches gjb,
    gl_code_combinations gcc,
    ap_lookup_codes flv
    WHERE
    phd.payment_history_id             = aph.payment_history_id
    AND xdl.source_distribution_type     = 'AP_PMT_DIST'
    AND xdl.source_distribution_id_num_1 = payment_hist_dist_id
    AND NVL("SOURCE_DISTRIBUTION_ID_NUM_2",(-99))= -99
    AND phd.invoice_distribution_id      = aid.invoice_distribution_id
    AND aid.invoice_id                   = aia.invoice_id
    AND aia.vendor_id                    = pv.vendor_id
    AND aca.check_id                     = aph.check_id
    AND xal.ae_header_id                 = xdl.ae_header_id
    AND xal.ae_line_num                  = xdl.ae_line_num
    AND xal.application_id               = xdl.application_id
    AND xal.ae_header_id                 = xah.ae_header_id
    AND xal.ae_header_id                 = xah.application_id
    AND gir.gl_sl_link_id                = xal.gl_sl_link_id
    AND gir.gl_sl_link_table             = xal.gl_sl_link_table
    AND gjl.je_header_id                 = gir.je_header_id
    AND gjl.je_line_num                  = gir.je_line_num
    AND gjh.je_header_id                 = gir.je_header_id
    AND gjh.je_category                  = 'Payments'
    AND gjh.actual_flag                  = 'A'
    AND gjh.status                       = 'P'
    AND gjl.status                       = 'P'
    AND gjb.je_batch_id                  = gjh.je_batch_id
    AND gjl.code_combination_id          = gcc.code_combination_id
    AND flv.lookup_type                  = 'PAYMENT METHOD'
    AND lookup_code                      = aca.payment_method_lookup_code;
    

AP to GL Drilldown (for Invoices)

Reference Note: Doc ID 605707.1



SELECT
  gjh.je_source source,
  gjh.je_category category,
  gjb.name journal,
  gjl.je_line_num line_num,
  gjh.actual_flag actual_flag,
  gjh.period_name period,
  gjl.effective_date gl_date,
  gcc.segment1 fund,
  gcc.segment2 FUNCTION,
  gcc.segment3 object,
  gcc.segment4 sub_object,
  gcc.segment5 org,
  gcc.segment6 yr,
  gcc.segment7 program,
  DECODE(SIGN(aid.amount),    -1,0,aid.amount) debit,
  DECODE(SIGN(aid.amount),1,0,-aid.amount) credit,
  pha.segment1 purchase_document_num,
  to_number(NULL) purchase_line_num,
  to_number(NULL) shipment_num,
  to_number(NULL) purchase_distribution_num,
  pv.vendor_name vendor,
  ai.invoice_num invoice_num,
  to_number(aid.distribution_line_number) invoice_distribution_num,
  to_number(NULL) check_num,
  aid.expenditure_type,
  hou.name expenditure_org,
  to_date(ai.invoice_date) trx_date
FROM
  apps.gl_je_headers gjh,
  apps.gl_je_lines gjl,
  apps.gl_je_batches gjb,
  apps.gl_import_references gir,
  xla.xla_ae_lines xal,
  xla.xla_ae_headers xah,
  gl_code_combinations gcc,
  xla_distribution_links xdl,
  ap_invoice_distributions_all aid,
  ap_invoice_lines_all ail,
  ap_invoices_all ai,
  po_headers_all pha,
  apps.ap_suppliers pv ,
  hr_all_organization_units hou,
  GL_PERIOD_STATUSES ps
WHERE
  gjh.je_category                = 'Purchase Invoices'
AND gjh.je_source                = 'Payables'
AND gjb.status                   = 'P'
AND gjh.status                   = 'P'
AND gjl.status                   = 'P'
AND gjh.actual_flag              = 'A'
AND gjb.JE_BATCH_ID              = gjh.JE_BATCH_ID
AND gjh.je_header_id             = gjl.je_header_id
AND gjl.CODE_COMBINATION_ID      = gcc.CODE_COMBINATION_ID
AND gjl.je_header_id             = gir.je_header_id
AND gjl.je_line_num              = gir.je_line_num
AND gir.gl_sl_link_id            = xal.gl_sl_link_id
AND gir.gl_sl_link_table         = xal.gl_sl_link_table
AND xal.application_id           = 200
AND xal.ae_header_id             = xah.ae_header_id
AND xah.application_id           = 200
AND xal.application_id           = xah.application_id
AND xal.ae_header_id             = xdl.ae_header_id
AND xal.ae_line_num              = xdl.ae_line_num
AND xdl.SOURCE_DISTRIBUTION_TYPE = 'AP_INV_DIST'
  --AND gjh.period_name = 'SEP-14'
AND xdl.SOURCE_DISTRIBUTION_ID_NUM_1 = aid.invoice_distribution_id
AND aid.invoice_id                   = ail.invoice_id
AND aid.invoice_line_number          = ail.line_number
AND ai.invoice_id                    = ail.invoice_id
AND pha.po_header_id (+)             = ail.po_header_id
AND ai.vendor_id                     = pv.vendor_id
AND aid.expenditure_organization_id  = hou.organization_id(+)
AND gjl.effective_date BETWEEN ps.start_date AND ps.end_date
  -- change start
  -- user using date range instead of period to retrieve records
AND ps.application_id = 101
AND gjh.period_name   = ps.period_name
AND PS.start_date     = to_date(TO_CHAR(gjl.effective_date, 'MON-YYYY'),
  'MON-YYYY')
AND ps.end_date = TRUNC(last_day(gjl.effective_date))
--
and ps.period_name = 'SEP-14';

AP to GL Drilldown (Encumbrance Transactions)

SELECT
  gjh.je_source source,
  gjh.je_category category,
  gjb.name batch_name,
  gjl.je_line_num line_num,
  gjh.actual_flag actual_flag,
  gjh.period_name period,
  gjl.effective_date gl_date,
  gcc.segment1 fund,
  gcc.segment2 FUNCTION,
  gcc.segment3 object,
  gcc.segment4 sub_object,
  gcc.segment5 org,
  gcc.segment6 yr,
  gcc.segment7 program,
  DECODE(SIGN(aid.amount),    -1,0,aid.amount) debit,
  DECODE(SIGN(aid.amount),1,0,-aid.amount) credit,
  gjh.je_category type,
  pha.segment1 purchase_document_num,
  pv.vendor_name,                                                   
  aia.invoice_num invoice_num,                                      
  TO_NUMBER(AID.DISTRIBUTION_LINE_NUMBER) invoice_distribution_num, 
  aid.expenditure_type,
  to_date(aia.invoice_date) trx_date
FROM
  apps.gl_je_batches jb,
  apps.gl_je_headers gjh,
  apps.gl_je_lines gjl,
  apps.gl_je_batches gjb,
  apps.gl_import_references gir,
  gl_code_combinations gcc,
  ap_encumbrance_lines_all ael ,
  ap_invoice_distributions_all Aid, 
  po_headers_all pha,
  po_distributions_all pda,
  po_vendors pv,
  ap_invoices_all aia,
  gl_period_statuses ps
WHERE
  jb.je_batch_id                    = gjh.je_batch_id
AND gjh.je_header_id                = gjl.je_header_id
AND gjl.je_header_id                = gir.je_header_id (+)
AND gjh.je_batch_id                 = gjb.je_batch_id
AND gjl.je_line_num                 = gir.je_line_num (+)
AND gjh.actual_flag                 = 'E'
AND gjh.je_source                   = 'Payables'
AND gjh.je_category                 = 'Purchase Invoices'
AND gjb.status                      = 'P'
AND gjh.status                      = 'P'
AND gjl.status                      = 'P'
AND gcc.code_combination_id         = gjl.code_combination_id
AND gir.gl_sl_link_id               = ael.gl_sl_link_id (+)
AND ael.invoice_distribution_id     = aid.invoice_distribution_id (+)
AND aid.po_distribution_id          = pda.po_distribution_id (+)
AND pda.po_header_id                = pha.po_header_id (+)
AND aid.invoice_id                  = aia.invoice_id (+)
AND aia.vendor_id                   = pv.vendor_id (+)
  -- change start
  -- user uses date range rather than period to run the report
AND ps.application_id = 101
AND gjh.period_name   = ps.period_name
AND PS.start_date     = to_date(TO_CHAR(gjl.effective_date, 'MON-YYYY'),
  'MON-YYYY')
AND ps.end_date = TRUNC(last_day(gjl.effective_date));
--and ps.period_name = 'SEP-14';