Post date: May 18, 2011 4:21:6 AM
/* Formatted on 2011/05/13 14:32 (Formatter Plus v4.8.8) */
SELECT DISTINCT gjh.NAME journal_name, gjb.NAME batch_name,
gjh.je_source SOURCE, gjh.je_category CATEGORY,
gjh.currency_code,
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7
|| '.'
|| gcc.segment8
|| '.'
|| gcc.segment9 ACCOUNT,
NVL (gjl.entered_dr, 0) debit_valas,
NVL (gjl.entered_cr, 0) credit_valas,
NVL (gjl.accounted_dr, 0) debit,
NVL (gjl.accounted_cr, 0) credit, xah.event_type_code,
xal.accounting_class_code,
NVL (xal.entered_dr, 0) debit_valas_xla,
NVL (xal.entered_cr, 0) credit_valas_xla,
NVL (xal.accounted_dr, 0) debit_xla,
NVL (xal.accounted_cr, 0) credit_xla, xal.gl_sl_link_id,
aca.check_number payment_number
--,(AIPA.AMOUNT * NVL (AIpa.EXCHANGE_RATE, 1)) payment_amount
FROM gl_je_headers gjh,
gl_je_lines gjl,
gl_je_batches gjb,
gl_import_references gir,
gl_code_combinations gcc,
xla_ae_lines xal,
xla_ae_headers xah,
ap_payment_history_all apha,
ap_checks_all aca,
ap_invoice_payments_all aipa
WHERE gjh.je_header_id = gjl.je_header_id
AND gjh.je_batch_id = gjb.je_batch_id
AND gjh.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gjl.code_combination_id = gcc.code_combination_id
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND xal.ae_header_id = xah.ae_header_id
AND xal.application_id = xah.application_id
AND xah.event_id = apha.accounting_event_id
AND apha.check_id = aca.check_id
AND aca.check_id = aipa.check_id
AND gjh.period_name =
'Oct-10'
-- 'Jan-10','Feb-10','Mar-10','Apr-10','May-10','Jun-10','Jul-10','Aug-10','Oct-10','Nov-10','Dec-10')
AND gcc.segment3 in ( '211111')--,'211112','211113','211114','216112')
AND gjh.actual_flag = 'A'
AND gjh.status = 'P'
AND gcc.enabled_flag = 'Y'
--and aia.INVOICE_NUM = 'KB10010914'
AND (NVL (xal.accounted_dr, 0) - NVL (xal.accounted_cr, 0)) <> 0
Query above can be reconciled with Payable Posted Payment Register Report (PPPR) for certain period and account