AP Turnover

Description
Categories: BI Publisher, Financials, Procurement
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 NameSQL textValidation
COA ID For Summary Mask
 
Number
Dummy Reporting Level
 
Dummy Report Type
 
Chart of Accounts ID
 
Number
Ledger Name Value
 
Summary Mask
 
Prepayment Processing
 
LOV Oracle
Reporting Level
 
LOV Oracle
Show Operating Currency
 
LOV Oracle
Currency
 
LOV Oracle
Liability Account To
 
Liability Account From
 
Supplier To
 
LOV Oracle
Supplier From
 
LOV Oracle
Period End Date
 
Date
Period Start Date
 
Date
Operating Unit To
 
LOV Oracle
Operating Unit From
 
LOV Oracle
Report Type
 
LOV Oracle
Ledger Name
 
LOV Oracle