AP Turnover

Description

Imported from BI Publisher
Application: Payables
Source: AP Turnover Report
Short Name: APTURNOVERRPT
DB package: AP_TURNOVER_RPT_PKG

AP Turnover and other Oracle EBS reports with Blitz Report™ on our demo environment

Copy

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

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

Download

Related Blitz Reports

GL Account Analysis GL Account Analysis (Distributions) AP Open Balances Revaluation (1) JL Argentine Payables Supplier Statement- Not Supported: Reserved For Future Use AP Invoice History Report- Not Supported: Reserved For Future Use JL Chilean Payables Supplier Statement- Not Supported: Reserved For Future Use CE General Ledger Reconciliation GL Account Analysis (Distributions) 11g CAC Invoice Price Variance

Blitz Report™

Blitz Report™ provides multiple benefits:

Perfectly formatted Excel reports
Create new or ad-hoc reports in minutes
User configurable reports
Includes seeded reports for all core areas
Toolkits for tuning, management and support
Data governance and audit tracking
Central report repository & version control
Imports BI-Publisher and Discoverer reports
EBS forms interface for user adoption

Find out more

Features

Download

FAQ