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 |
