SELECT invoice_payment.pay_doc_type inv_pay_doc_type
,invoice_payment.pay_acct_num inv_pay_acct_num
,invoice_payment.pay_header_id inv_pay_header_id
,invoice_payment.pay_line_num inv_pay_line_num
,invoice_payment.pay_check_id inv_pay_check_id
,invoice_payment.pay_doc_num inv_pay_doc_num
,invoice_payment.pay_payment_date inv_pay_payment_date
,invoice_payment.pay_gl_date inv_pay_gl_date
,invoice_payment.liability_account inv_liability_account
,SUM(invoice_payment.pay_hed_ent_dr) inv_pay_hed_ent_dr
,SUM(invoice_payment.pay_hed_acct_dr) inv_pay_hed_acct_dr
,SUM(invoice_payment.pay_detail_ent_cr) inv_pay_detail_ent_cr
,SUM(invoice_payment.pay_detail_ent_dr) inv_pay_detail_ent_dr
,SUM(invoice_payment.pay_detail_acct_cr) inv_pay_detail_acct_cr
,SUM(invoice_payment.pay_detail_acct_dr) inv_pay_detail_acct_dr
,invoice_payment.pay_dist_type inv_pay_dist_type
,invoice_payment.pay_invoice_id inv_invoice_id
FROM
(SELECT DECODE(xal.accounting_class_code,'LIABILITY'
,'Payment/'||alc.displayed_field
,INITCAP(xal.accounting_class_code)) pay_doc_type
,DECODE(xal.accounting_class_code,'LIABILITY',1,2) pay_acct_num
,xal.ae_header_id pay_header_id
,xal.ae_line_num pay_line_num
,ac.check_id pay_check_id
,ac.check_number pay_doc_num
,ac.check_date pay_payment_date
,ac.cleared_date pay_gl_date
,fnd_flex_xml_publisher_apis.process_kff_combination_1('flex_acct', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'N', 'VALUE') liability_account
,DECODE(xal.accounting_class_code,'LIABILITY',(NVL(xdl.unrounded_entered_dr,0)- NVL(xdl.unrounded_entered_cr,0)),0) pay_hed_ent_dr
,DECODE(xal.accounting_class_code,'LIABILITY',(NVL(xdl.unrounded_accounted_dr,0) - NVL(xdl.unrounded_accounted_cr,0)),0) pay_hed_acct_dr
,DECODE(xal.accounting_class_code,'LIABILITY',0,DECODE(xah.event_type_code,'PAYMENT UNCLEARED'
,-1*(NVL(xdl.unrounded_entered_dr,0)),'PAYMENT CANCELLED'
,-1*(NVL(xdl.unrounded_entered_dr,0)),(NVL(xdl.unrounded_entered_cr,0)))) pay_detail_ent_cr
,DECODE(xal.accounting_class_code,'LIABILITY',0,DECODE(xah.event_type_code,'PAYMENT UNCLEARED'
,-1*(NVL(xdl.unrounded_entered_cr,0)),'PAYMENT CANCELLED'
,-1*(NVL(xdl.unrounded_entered_cr,0)),(NVL(xdl.unrounded_entered_dr,0)))) pay_detail_ent_dr
,DECODE(xal.accounting_class_code,'LIABILITY',0,DECODE(xah.event_type_code,'PAYMENT UNCLEARED'
,-1*(NVL(xdl.unrounded_accounted_dr,0)),'PAYMENT CANCELLED'
,-1*(NVL(xdl.unrounded_accounted_dr,0)),(NVL(xdl.unrounded_accounted_cr,0)))) pay_detail_acct_cr
,DECODE(xal.accounting_class_code,'LIABILITY',0,DECODE(xah.event_type_code,'PAYMENT UNCLEARED'
,-1*(NVL(xdl.unrounded_accounted_cr,0)),'PAYMENT CANCELLED'
,-1*(NVL(xdl.unrounded_accounted_cr,0)),(NVL(xdl.unrounded_accounted_dr,0)))) pay_detail_acct_dr
,alc.displayed_field pay_dist_type
,aia.invoice_id pay_invoice_id
FROM ap_invoices aia
,ap_invoice_payments aip
,ap_checks ac
,ap_payment_history aph
,ap_lookup_codes alc
,ap_system_parameters asps
,xla_events xe
,xla_distribution_links xdl
,xla_ae_lines xal
,xla_ae_headers xah
,gl_code_combinations gcc
WHERE aia.vendor_id = :SUP_ID
AND aia.org_id = :ORGANIZATION_ID
AND aia.vendor_site_id = :SUP_SITE_ID
AND aia.invoice_currency_code = :CURRENCY
AND aia.invoice_id = aip.invoice_id
AND aia.gl_date BETWEEN :PERIOD_START_DATE_PARAM AND :PERIOD_END_DATE_PARAM
AND aip.check_id = ac.check_id
AND ac.check_id = aph.check_id
AND alc.lookup_type = 'PAYMENT TYPE'
AND asps.org_id = aia.org_id
AND alc.lookup_code = ac.payment_type_flag
AND xe.application_id = 200
AND aph.accounting_event_id = xe.event_id
AND xah.application_id = xe.application_id
AND xe.event_id = xah.event_id
AND xah.ledger_id = :LEDGER_ID_PARAM
AND xal.application_id = xe.application_id
AND xah.ae_header_id = xal.ae_header_id
AND xdl.application_id = xe.application_id
AND xah.ae_header_id = xdl.ae_header_id
AND xal.ae_line_num = xdl.ae_line_num
AND xdl.source_distribution_type = 'AP_PMT_DIST'
AND (xdl.applied_to_source_id_num_1 = aia.invoice_id
OR xdl.applied_to_source_id_num_1 = aip.check_id)
AND xdl.accounting_line_code NOT IN ('AP_LIAB_AWT_PMT')
AND gcc.code_combination_id = xal.code_combination_id
AND ((:PRPMT_PROCESSING_PARAM = 2)
OR (:PRPMT_PROCESSING_PARAM = 1
AND (aia.invoice_type_lookup_code IN ('CREDIT','DEBIT','STANDARD','AWT','EXPENSE REPORT','MIXED')
OR (aia.invoice_type_lookup_code = 'PREPAYMENT'
AND (CASE WHEN asps.recon_accounting_flag='Y'
THEN ac.cleared_date
WHEN asps.recon_accounting_flag='N'
THEN ac.check_date END) IS NOT NULL))))
AND ((xal.accounting_class_code = 'LIABILITY'
AND gcc.code_combination_id = :ACCOUNT_ID)
OR(xal.accounting_class_code <> 'LIABILITY'))
GROUP BY 'Payment/'||alc.displayed_field
,DECODE(xal.accounting_class_code,'LIABILITY',1,2)
,xal.ae_header_id
,xal.ae_line_num
,ac.check_id
,ac.check_number
,ac.check_date
,ac.cleared_date
,alc.displayed_field
,aia.invoice_id
,xal.accounting_class_code
,xdl.unrounded_entered_dr
,xdl.unrounded_entered_cr
,xdl.unrounded_accounted_dr
,xdl.unrounded_accounted_cr
,xah.event_type_code
,fnd_flex_xml_publisher_apis.process_kff_combination_1('flex_acct', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'N', 'VALUE')
ORDER BY 6,3,2)invoice_payment
GROUP BY invoice_payment.pay_doc_type
,invoice_payment.pay_acct_num
,invoice_payment.pay_header_id
,invoice_payment.pay_line_num
,invoice_payment.pay_check_id
,invoice_payment.pay_doc_num
,invoice_payment.pay_payment_date
,invoice_payment.pay_gl_date
,invoice_payment.liability_account
,invoice_payment.pay_dist_type
,invoice_payment.pay_invoice_id
ORDER BY 6,3,2 |
