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
Application: Payables
Source: AP Open Balances Revaluation Report
Short Name: APOBRR
DB package: AP_OPEN_BAL_REV_RPT_PKG
Run
AP Open Balances Revaluation (1) and other Oracle EBS reports with Blitz Reportâ„¢ on our demo environment
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 |
|
Date | |
| Exchange Rate Type |
|
LOV Oracle | |
| Currency |
|
LOV Oracle | |
| Exchange Rate |
|
Char | |
| Include Domestic Invoice |
|
LOV Oracle | |
| Supplier |
|