AP Open Balances Revaluation

Description
Categories: BI Publisher, Financials, Procurement
Application: Payables
Source: AP Open Balances Revaluation Report
Short Name: APOBRR
DB package: AP_OPEN_BAL_REV_RPT_PKG
SELECT
NVL(payables4.pay_inv_amt_due4,0)        tax_amt
,CASE WHEN payables4.pay_inv_amt_due4 <> 0 AND :transaction_type ='PREPAYMENT' 
           AND (payables5.prepay_entered_dr1 - :invoice_amt_entered)!=0 THEN
          AP_OPEN_BAL_REV_RPT_PKG.vat_calc_amt(payables3.pay_inv_amt_due3
		                                        ,payables4.pay_inv_amt_due4
		                                        ,:invoice_amt_entered)
          ELSE :invoice_amt_entered-(payables1.pay_inv_amt_due1+
		                             payables2.pay_inv_amt_due2+
									 payables3.pay_inv_amt_due3+
									 payables5.prepay_entered_dr1+
                                     payables6.inv_cm_amt_due+
									 payables7.cm_inv_amt_due) END      inv_amt_due
,CASE WHEN payables4.pay_inv_amt_due_er4 <> 0 AND :transaction_type ='PREPAYMENT' 
           AND (payables5.prepay_entered_dr1 - :invoice_amt_entered)!=0 THEN
          AP_OPEN_BAL_REV_RPT_PKG.vat_calc_amt(payables3.pay_inv_amt_due_er3
		                                        ,payables4.pay_inv_amt_due_er4
		                                        ,:invoice_amt_accounted)
          ELSE :invoice_amt_accounted-(payables1.pay_inv_amt_due_er1+
		                               payables2.pay_inv_amt_due_er2+
									   payables3.pay_inv_amt_due_er3+
									   payables5.prepay_accounted_dr1+
									   payables6.inv_cm_amt_due_er+
									   payables7.cm_inv_amt_due_er) END   inv_amt_due_er
,CASE WHEN payables4.pay_inv_amt_due_er4 <> 0 AND :transaction_type ='PREPAYMENT' 
            AND (payables5.prepay_entered_dr1 - :invoice_amt_entered)!=0 THEN
		  AP_OPEN_BAL_REV_RPT_PKG.amtduereval(AP_OPEN_BAL_REV_RPT_PKG.vat_calc_amt(payables3.pay_inv_amt_due3
		                                        ,payables4.pay_inv_amt_due4
		                                        ,:invoice_amt_entered)
		                                        ,:exchange_rate
												,:transaction_currency)
		  ELSE AP_OPEN_BAL_REV_RPT_PKG.amtduereval((:invoice_amt_entered - 
		                                           (payables1.pay_inv_amt_due1+
												    payables2.pay_inv_amt_due2+
													payables3.pay_inv_amt_due3+
													payables5.prepay_entered_dr1+
													payables6.inv_cm_amt_due+
													payables7.cm_inv_amt_due))
		                                         ,:exchange_rate
												 ,:transaction_currency) END inv_amt_due_reval
FROM
	(SELECT  NVL(sum(nvl(XAL.entered_dr,0))-sum(nvl(XAL.entered_cr,0)),0)     pay_inv_amt_due1 
            ,NVL(sum(nvl(XAL.accounted_dr,0))-sum(nvl(XAL.accounted_cr,0)),0)   pay_inv_amt_due_er1 
FROM
ap_invoices                  AIA 
,xla_events                 xe
,xla_distribution_links     XDL
,xla_ae_lines               XAL
,gl_import_references 		GIR
,gl_je_headers        		GJH
WHERE aia.invoice_id=:invoice_id_tag 
AND     aia.invoice_type_lookup_code NOT IN ('PREPAYMENT')
AND     aia.payment_status_flag             <>'Y'  --bug7581755
AND     xe.application_id=200
AND     xe.event_id=xdl.event_id
--AND     xe.event_type_code IN ('PAYMENT CANCELLED','PAYMENT UNCLEARED','PAYMENT CLEARED')  
AND     XDL.application_id=200
AND     XDL.applied_to_source_id_num_1	    =:invoice_id_tag
AND     xdl.applied_to_entity_code='AP_INVOICES'
AND     xdl.source_distribution_type='AP_PMT_DIST'	
AND 	XAL.ae_header_id					=XDL.ae_header_id 
AND 	XAL.ae_line_num						=XDL.ae_line_num 
AND 	XAL.application_id					=200 
AND     XAL.accounting_class_code   		='LIABILITY' 
AND 	GIR.gl_sl_link_id					=XAL.gl_sl_link_id 
AND 	GIR.gl_sl_link_table				=XAL.gl_sl_link_table 
AND 	GJH.je_header_id					=GIR.je_header_id 
AND 	GJH.status							='P'
AND     GJH.ledger_id                       = &gc_ledger_id
AND     XAL.ACCOUNTING_DATE<= :P_AS_OF_DATE
) payables1
,(SELECT  NVL(SUM(NVL(xdl.unrounded_entered_dr,0)-NVL(xal.unrounded_entered_cr,0)),0)      pay_inv_amt_due2
,NVL(SUM(NVL(xdl.unrounded_accounted_dr,0)-NVL(xdl.unrounded_accounted_cr,0)),0)      pay_inv_amt_due_er2 
FROM 
         ap_invoices                  ai
        ,ap_invoices                  aipre
        ,ap_invoice_distributions     aid
        ,ap_invoice_distributions     aidpre
        ,xla_distribution_links       xdl
        ,xla_events                   xe
        ,xla_ae_lines                 xal
        ,gl_import_references 		GIR
		,gl_je_headers        		GJH
        ,gl_code_combinations       gcc	
WHERE
      ai.invoice_id=:invoice_id_tag
  AND    aidpre.line_type_lookup_code='ITEM'	  
  AND    aid.invoice_id=ai.invoice_id
  AND    ai.payment_status_flag<>'Y'  --bug7581755
  AND    aidpre.invoice_distribution_id=aid.prepay_distribution_id
  AND    aipre.invoice_id=aidpre.invoice_id
  AND    xdl.application_id=200
  AND    xdl.event_id=aid.accounting_event_id
  AND    xal.ae_header_id=xdl.ae_header_id
  AND    xal.ae_line_num=xdl.ae_line_num
  AND    xal.application_id=200
  AND    xal.accounting_class_code='LIABILITY'
  AND    xe.event_id=xdl.event_id
  AND    xe.application_id=200
  AND    xe.event_type_code IN ('PREPAYMENT UNAPPLIED','PREPAYMENT APPLIED')
  AND 	GCC.code_combination_id		=XAL.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 	GJH.je_header_id			=GIR.je_header_id
  AND 	GJH.status					='P'
  AND     GJH.ledger_id                       = &gc_ledger_id
  AND     XAL.accounting_date		<= :P_AS_OF_DATE
) payables2
,(SELECT  NVL(sum(NVL(xdl.unrounded_ENTERED_cR,0)-NVL(xdl.unrounded_entered_dr,0)),0)                  pay_inv_amt_due3
		   ,NVL(sum(NVL(xdl.unrounded_accounted_cr,0)-NVL(xdl.unrounded_accounted_dr,0)),0)              pay_inv_amt_due_er3
FROM
         ap_invoices              ai 
        ,ap_invoice_distributions aid
        ,ap_invoice_distributions aidinv
        ,ap_invoices              aiinv
        ,xla_events               xe
        ,xla_ae_lines             xal
        ,xla_distribution_links   xdl
        ,gl_import_references 		GIR
	    ,gl_je_headers        		GJH
WHERE
         ai.invoice_id=:invoice_id_tag
  AND    aid.invoice_id=ai.invoice_id
  AND    ai.payment_status_flag <>'Y'  --bug7581755
  AND    aid.line_type_lookup_code='ITEM'
  AND    aidinv.prepay_distribution_id=aid.invoice_distribution_id
  AND    aiinv.invoice_id=aidinv.invoice_id
  AND    xe.event_type_code IN ('PREPAYMENT APPLIED','PREPAYMENT UNAPPLIED')
  AND    xe.application_id=200
  AND    xe.event_id=xdl.event_id
  AND    xdl.event_id=aidinv.accounting_event_id
  AND    xdl.application_id=200
  AND    xal.ae_header_id=xdl.ae_header_id
  AND    xal.ae_line_num=xdl.ae_line_num
  AND    xal.accounting_class_code='PREPAID_EXPENSE'
  AND    xal.application_id=200
  AND 	 GIR.gl_sl_link_id					=XAL.gl_sl_link_id 
  AND 	 GIR.gl_sl_link_table				=XAL.gl_sl_link_table 
  AND 	 GJH.je_header_id					=GIR.je_header_id 
  AND 	 GJH.status							='P'
  AND     GJH.ledger_id                       = &gc_ledger_id
  AND     XAL.accounting_date				<= :P_AS_OF_DATE
	) payables3
,(SELECT
		NVL(SUM(NVL(XDL.UNROUNDED_entered_dr,0))-SUM(NVL(XAL.UNROUNDED_entered_cr,0)),0)     pay_inv_amt_due4
		,NVL(SUM(NVL(XDL.UNROUNDED_accounted_dr,0))-SUM(NVL(XAL.UNROUNDED_accounted_cr,0)),0) pay_inv_amt_due_er4
FROM
		ap_invoices_all 				AI
		,ap_invoice_distributions_all 	AID
		,xla_distribution_links 	XDL
		,xla_ae_lines 				XAL
		,gl_import_references 		GIR
		,gl_je_headers        		GJH
		,gl_code_combinations 		GCC
WHERE   AI.invoice_id = :invoice_id_tag
AND     AI.invoice_type_lookup_code = 'PREPAYMENT'
AND		AID.invoice_id				=AI.invoice_id
AND     AI.payment_status_flag      <>'Y'  --bug7581755
AND 	XDL.event_id		        =AID.accounting_event_id
AND 	XDL.source_distribution_id_num_1 = AID.invoice_distribution_id
AND 	XDL.application_id			=200
AND     xdl.rounding_class_code in ('RTAX','NRTAX')
AND 	XAL.ae_header_id			=XDL.ae_header_id
AND 	XAL.ae_line_num				=XDL.ae_line_num
AND     XAL.ledger_id               =AI.set_of_books_id
AND 	XAL.application_id			=200
AND 	GCC.code_combination_id		=XAL.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 	GJH.je_header_id			=GIR.je_header_id
AND 	GJH.status					='P'
AND     GJH.ledger_id                       = &gc_ledger_id
AND     XAL.ACCOUNTING_DATE			<= :P_AS_OF_DATE
   )payables4
,(SELECT CASE WHEN (:TRANSACTION_TYPE = 'PREPAYMENT' AND :invoice_amt_entered = NVL(SUM(NVL(xdl.unrounded_entered_dr,0)-NVL(xdl.unrounded_entered_cr,0)),0)) 
                   THEN TO_NUMBER(0)
			  WHEN (:TRANSACTION_TYPE = 'PREPAYMENT' AND NVL(SUM(NVL(xdl.unrounded_entered_dr,0)-NVL(xdl.unrounded_entered_cr,0)),0) = 0) 
			       THEN TO_NUMBER(:invoice_amt_entered)
			  WHEN (:TRANSACTION_TYPE = 'PREPAYMENT' AND SUM(NVL(xdl.unrounded_entered_dr,0)-NVL(xdl.unrounded_entered_cr,0)) is null) 
			       THEN TO_NUMBER(:invoice_amt_entered)
              ELSE TO_NUMBER(0) END  prepay_entered_dr1
		,CASE WHEN (:TRANSACTION_TYPE = 'PREPAYMENT' AND :invoice_amt_accounted = NVL(SUM(NVL(xdl.unrounded_accounted_dr,0)-NVL(xdl.unrounded_accounted_cr,0)),0)) 
		           THEN TO_NUMBER(0)
			  WHEN (:TRANSACTION_TYPE = 'PREPAYMENT' AND NVL(SUM(NVL(xdl.unrounded_accounted_dr,0)-NVL(xdl.unrounded_accounted_cr,0)),0) = 0) 
			       THEN TO_NUMBER(:invoice_amt_accounted)
			  WHEN (:TRANSACTION_TYPE = 'PREPAYMENT' AND SUM(NVL(xdl.unrounded_accounted_dr,0)-NVL(xdl.unrounded_accounted_cr,0)) is null) 
			       THEN TO_NUMBER(:invoice_amt_accounted)
              ELSE TO_NUMBER(0) END  prepay_accounted_dr1
FROM 
         ap_invoices                aia
        ,ap_lookup_codes            alc
        ,ap_invoice_payments        aip
        ,ap_checks                  ac
		,xla_events                 xe
        ,xla_distribution_links     xdl
        ,xla_ae_lines               xal
        ,gl_import_references 		GIR
		,gl_je_headers        		GJH
		,gl_code_combinations 		GCC
WHERE   aia.invoice_id=:invoice_id_tag
AND     aia.invoice_type_lookup_code IN ('PREPAYMENT')
AND     aia.invoice_id=aip.invoice_id
AND     aia.payment_status_flag<>'Y'  --bug7581755
AND     ac.check_id=aip.check_id
AND     alc.lookup_type='PAYMENT TYPE' 
AND     alc.lookup_code=ac.payment_type_flag 
AND     xdl.applied_to_entity_code='AP_INVOICES'
AND     xdl.source_distribution_type='AP_PMT_DIST'			 			 
AND     xdl.applied_to_source_id_num_1=:invoice_id_tag
AND     xdl.application_id=200
AND     xe.event_id = xdl.event_id
AND     xe.application_id=200
AND     aip.accounting_event_id=xe.event_id
AND     xal.ae_header_id=xdl.ae_header_id
AND     xal.ae_line_num=xdl.ae_line_num
AND     xal.accounting_class_code='LIABILITY'
AND     xal.application_id=200 
--and rounding_class_code='LIABILITY'
AND     xdl.ae_header_id=xal.ae_header_id
AND     xdl.ae_line_num=xal.ae_line_num
AND 	GCC.code_combination_id		=XAL.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 	GJH.je_header_id			=GIR.je_header_id
AND 	GJH.status					='P'
AND     GJH.ledger_id                       = &gc_ledger_id
AND     GJH.ledger_id = &gc_ledger_id
AND     XAL.ACCOUNTING_DATE			<= :P_AS_OF_DATE)payables5
,(SELECT  NVL(SUM(xdl.unrounded_entered_dr),0)-NVL(SUM(xdl.unrounded_entered_cr),0) inv_cm_amt_due 
        ,NVL(SUM(xdl.unrounded_accounted_dr),0)-NVL(SUM(xdl.unrounded_accounted_cr),0) inv_cm_amt_due_er
FROM 
 ap_invoices                   aia  --bug7581755
,ap_invoice_distributions      AID
,xla_events                    xe
,xla_distribution_links        xdl
,xla_ae_lines                  xal 
,gl_import_references 		   GIR
,gl_je_headers        		   GJH
WHERE
        AID.parent_invoice_id=:invoice_id_tag
AND     aia.invoice_id = aid.invoice_id  --bug7581755
AND     aia.payment_status_flag<>'Y'  --bug7581755
AND     XDL.source_distribution_id_num_1=aid.invoice_distribution_id 
AND   	XDL.application_id=200
AND     xe.event_type_code in ('CREDIT MEMO VALIDATED','DEBIT MEMO VALIDATED')
AND     xe.application_id=200
AND     xe.event_id=xdl.event_id
AND   	XAL.ae_header_id=xdl.ae_header_id
AND   	XAL.ae_line_num=xdl.ae_line_num
AND   	XAL.application_id=200
AND   	XAL.accounting_class_code='LIABILITY'
AND 	GIR.gl_sl_link_id					=XAL.gl_sl_link_id 
AND 	GIR.gl_sl_link_table				=XAL.gl_sl_link_table 
AND 	GJH.je_header_id					=GIR.je_header_id 
AND 	GJH.status							='P'
AND     GJH.ledger_id                       =&gc_ledger_id
AND     XAL.ACCOUNTING_DATE<= :P_AS_OF_DATE) payables6
,(SELECT  NVL(SUM(NVL(xdl.unrounded_entered_cr,0)-NVL(xdl.unrounded_entered_dr,0)),0)   cm_inv_amt_due
,NVL(SUM(NVL(xdl.unrounded_accounted_cr,0)-NVL(xdl.unrounded_accounted_dr,0)),0)      cm_inv_amt_due_er 
FROM 
         ap_invoices                      ai
        ,ap_invoices                      aicm
        ,ap_invoice_distributions         aid
        ,ap_invoice_distributions         aidcm
        ,xla_distribution_links           xdl
        ,xla_events                       xe
        ,xla_ae_lines                     xal
        ,gl_import_references 		      GIR
		,gl_je_headers        		      GJH
        ,gl_code_combinations             gcc	
WHERE
      ai.invoice_id=:invoice_id_tag
  AND    aid.invoice_id=ai.invoice_id
  AND    ai.payment_status_flag<>'Y'  --bug7581755
  AND    aidcm.invoice_distribution_id=aid.corrected_invoice_dist_id
  AND    aicm.invoice_id=aidcm.invoice_id
  AND    aid.dist_match_type='DIST_CORRECTION'
  AND    XDL.source_distribution_id_num_1=aid.invoice_distribution_id 
  AND    xdl.application_id=200
  AND    xdl.event_id=aid.accounting_event_id
  AND    xal.ae_header_id=xdl.ae_header_id
  AND    xal.ae_line_num=xdl.ae_line_num
  AND    xal.application_id=200
  AND    xal.accounting_class_code='LIABILITY'
  AND    xe.event_id=xdl.event_id
  AND    xe.application_id=200
  AND    xe.event_type_code IN ('CREDIT MEMO VALIDATED','DEBIT MEMO VALIDATED')
  AND 	 GCC.code_combination_id		=XAL.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 	 GJH.je_header_id			    =GIR.je_header_id
  AND 	 GJH.status					    ='P'
  AND    GJH.ledger_id                  =&gc_ledger_id
  AND    XAL.accounting_date		    <= :P_AS_OF_DATE) payables7
Parameter Name SQL text Validation
Dummy
 
Supplier
 
LOV Oracle
Include Domestic Invoice
 
LOV Oracle
Exchange Rate
 
Currency
 
LOV Oracle
Exchange Rate Type
 
LOV Oracle
As of Date
 
Date
Operating Unit
 
LOV Oracle