AP Open Balances Revaluation (1)

Description
Categories: BI Publisher
Imported from BI Publisher
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
Operating Unit
 
LOV Oracle
As of Date