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';