AP Turnover
Description
Categories: BI Publisher
Imported from BI Publisher
Application: Payables
Source: AP Turnover Report
Short Name: APTURNOVERRPT
DB package: AP_TURNOVER_RPT_PKG
Application: Payables
Source: AP Turnover Report
Short Name: APTURNOVERRPT
DB package: AP_TURNOVER_RPT_PKG
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 |
| Parameter Name | SQL text | Validation | |
|---|---|---|---|
| Ledger Name |
|
LOV Oracle | |
| Report Type |
|
LOV Oracle | |
| Operating Unit From |
|
LOV Oracle | |
| Operating Unit To |
|
LOV Oracle | |
| Period Start Date |
|
Date | |
| Period End Date |
|
Date | |
| Supplier From |
|
LOV Oracle | |
| Supplier To |
|
LOV Oracle | |
| Liability Account From |
|
Char | |
| Liability Account To |
|
Char | |
| Currency |
|
LOV Oracle | |
| Show Operating Currency |
|
LOV Oracle | |
| Reporting Level |
|
LOV Oracle | |
| Prepayment Processing |
|
LOV Oracle | |
| Summary Mask |
|
Char |
