AP Prepayment Balance
Description
Categories: BI Publisher
Imported from BI Publisher
Application: Payables
Source: AP Prepayment Balance Report
Short Name: APPPBR
DB package: AP_BAL_PKG
Application: Payables
Source: AP Prepayment Balance Report
Short Name: APPPBR
DB package: AP_BAL_PKG
Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS
Contact us to schedule a demo or if you need help with the installation
SELECT rownum unq_ident /*Bug 16613775*/ ,ai.invoice_id prepay_id ,decode ( :format ,'EXCEL' ,'="' || TO_CHAR (ai.invoice_num) || '"' ,ai.invoice_num ) invoice_number ,TO_CHAR(ai.gl_date, FND_PROFILE.value ('ICX_DATE_FORMAT_MASK')) gl_date ,ai.invoice_date invoice_date ,ai.doc_sequence_value doc_sequence_value ,ai.invoice_type_lookup_code invoice_type ,pov.vendor_name supplier_name ,pov.segment1 supplier_code ,ai.payment_status_flag paid_unpaid ,TO_CHAR(ai.earliest_settlement_date, FND_PROFILE.value ('ICX_DATE_FORMAT_MASK')) date_settled ,ai.invoice_currency_code curr_code ,aid.accounting_date accounting_date ,hou.organization_id operating_unit_id ,hou.name organization_name ,NVL(xdl.unrounded_entered_dr,0) - NVL(xdl.unrounded_entered_cr,0) adv_amt_fr_curr ,NVL(xdl.unrounded_accounted_dr,0) - NVL(xdl.unrounded_accounted_cr,0) adv_amt_fn_curr ,AP_BAL_PKG.prepay_amt_applied(ai.invoice_id,:gd_from_date) pre_amt_from_fr ,AP_BAL_PKG.prepay_amt_applied(ai.invoice_id,:gd_from_date) * NVL(ai.exchange_rate,1) pre_amt_from_fn ,AP_BAL_PKG.prepay_amt_applied(ai.invoice_id,:gd_to_date) pre_amt_to_fr ,AP_BAL_PKG.prepay_amt_applied(ai.invoice_id,:gd_to_date) * NVL(ai.exchange_rate,1) pre_amt_to_fn /*Bug11655402 : Added decode below */ ,DECODE(ai.payment_status_flag, 'Y', NVL(aid.prepay_amount_remaining,aid.amount), NVL(aid.prepay_amount_remaining,0)) bal_amt_fr_curr ,DECODE(ai.payment_status_flag, 'Y', NVL(aid.prepay_amount_remaining,aid.amount), NVL(aid.prepay_amount_remaining,0)) * NVL(ai.exchange_rate,1) bal_amt_fn_curr /*,TO_CHAR(ac.check_date, FND_PROFILE.value ('ICX_DATE_FORMAT_MASK')) payment_date*/ /*Bug11791895*/ ,AP_BAL_PKG.get_no_of_holds(ai.invoice_id) holdbacks ,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_ff_select', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') acct_ff ,fnd_flex_xml_publisher_apis.process_kff_combination_1('bal_select', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'N', 'VALUE') balancing_segment ,fnd_flex_xml_publisher_apis.process_kff_combination_1('cost_ctr_select', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'N', 'VALUE') cost_center_segment ,fnd_flex_xml_publisher_apis.process_kff_combination_1('natural_acct_select', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'N', 'VALUE') natural_acct_segment ,fnd_flex_xml_publisher_apis.process_kff_combination_1('bal_select_desc', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'N', 'FULL_DESCRIPTION') balancing_desc ,fnd_flex_xml_publisher_apis.process_kff_combination_1('cost_ctr_select_desc', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'N', 'FULL_DESCRIPTION') cost_center_desc ,fnd_flex_xml_publisher_apis.process_kff_combination_1('natural_acct_select', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'N', 'VALUE') natural_acct_desc ,ai.attribute3 issuing_dept_code /*,NVL(avprpv.prepay_amount_applied,0) pre_amt_appl_fr_curr ,NVL(avprpv.prepay_amount_applied,0) * NVL(ai.exchange_rate,1) pre_amt_appl_fn_curr ,decode ( :format ,'EXCEL' ,'="' || TO_CHAR (decode(aip.invoice_payment_type, 'PREPAY', ai.invoice_num, ac.check_number)) || '"' ,decode(aip.invoice_payment_type, 'PREPAY', ai.invoice_num, ac.check_number) ) int_num ,ac.check_id check_id*/ /*Bug11791895*/ ,AP_BAL_PKG.get_settlement_date(AI.invoice_id,AIL.line_number) settlement_date ,AP_BAL_PKG.description(gcc.segment4,'SEGMENT4') desc_segment4 ,AP_BAL_PKG.description(ai.attribute3,'ATTRIBUTE3') desc_attribute3 ,gcc.segment1 segment1 ,gcc.segment2 segment2 ,gcc.segment3 segment3 ,gcc.segment4 segment4 ,gcc.segment5 segment5 ,gcc.segment6 segment6 ,gcc.segment7 segment7 ,gcc.segment8 segment8 ,gcc.segment9 segment9 ,gcc.segment10 segment10 ,gcc.segment11 segment11 ,gcc.segment12 segment12 ,gcc.segment13 segment13 ,gcc.segment14 segment14 ,gcc.segment15 segment15 ,gcc.segment16 segment16 ,gcc.segment17 segment17 ,gcc.segment18 segment18 ,gcc.segment19 segment19 ,gcc.segment20 segment20 ,gcc.segment21 segment21 ,gcc.segment22 segment22 ,gcc.segment23 segment23 ,gcc.segment24 segment24 ,gcc.segment25 segment25 ,gcc.segment26 segment26 ,gcc.segment27 segment27 ,gcc.segment28 segment28 ,gcc.segment29 segment29 ,gcc.segment30 segment30 ,xah.gl_transfer_status_code gl_transfer_status_code ,gjh.status posting_status FROM ap_invoices ai ,ap_invoice_lines_all ail ,ap_invoice_distributions_all aid ,xla_ae_headers xah ,xla_ae_lines xal ,gl_code_combinations gcc ,po_vendors pov ,xla_distribution_links xdl ,hr_operating_units hou /*,ap_invoice_payments_all aip*/ /*Commenetd as code cleanup for Bug 16613775*/ /*,ap_checks_all ac */ /*Commenetd as code cleanup for Bug 16613775*/ ,gl_import_references gir ,gl_je_headers gjh /*Bug13389653 - Removed table avprpv */ /*,(SELECT avprpv.org_id org_id ,avprpv.prepay_id prepay_id ,avprpv.prepay_line_number prepay_line_number ,avprpv.prepay_amount_applied prepay_amount_applied FROM ap_view_prepays_fr_prepay_v avprpv WHERE NVL(avprpv.accounting_date, :gd_from_date) <= :gd_to_date) avprpv */ WHERE ai.invoice_id = ail.invoice_id AND ail.invoice_id = aid.invoice_id AND ail.line_number = aid.invoice_line_number AND xal.code_combination_id = gcc.code_combination_id AND ai.vendor_id = pov.vendor_id AND ai.invoice_type_lookup_code = 'PREPAYMENT' AND ai.set_of_books_id = xah.ledger_id --Bug8260360 AND aid.accounting_event_id = xah.event_id AND xdl.source_distribution_id_num_1 = aid.invoice_distribution_id AND xal.ae_header_id = xah.ae_header_id AND xdl.ae_header_id = xal.ae_header_id AND xdl.ae_line_num = xal.ae_line_num AND hou.organization_id = ai.org_id AND xah.application_id = 200 AND xal.application_id = 200 AND xdl.application_id = 200 /* AND xdl.rounding_class_code <> 'LIABILITY'*/ /*Commented for bug 16733413*/ AND xal.accounting_class_code <> 'LIABILITY' /*Added for bug 16733413*/ AND xdl.source_distribution_type <> 'AP_PMT_DIST' --Used to confirm the Invoice is Accounted (Final) AND xah.accounting_entry_status_code <> 'D' --Used to confirm the Invoice is Accounted AND aid.accrual_posted_flag = 'Y' --Used to Obtain the records with the Balance Type 'Actual' AND xah.balance_type_code = 'A' AND NVL (aid.accounting_date, ai.gl_date) <= :gd_to_date AND ai.cancelled_date IS NULL /*AND ai.invoice_id = aip.invoice_id(+) */ /*Commented as code cleanup for Bug 16613775 Start*/ /*AND aip.check_id = ac.check_id(+) AND ac.void_date IS NULL Bug 16613775 End */ /* Bug13389653 */ /* AND ail.invoice_id = avprpv.prepay_id(+) AND ail.line_number = avprpv.prepay_line_number(+) AND ail.org_id = avprpv.org_id(+) */ /* End of Bug13389653 */ AND xal.gl_sl_link_id = gir.gl_sl_link_id(+) AND xal.gl_sl_link_table = gir.gl_sl_link_table(+) AND gir.je_header_id = gjh.je_header_id(+) AND xal.ledger_id = NVL(gjh.ledger_id,xal.ledger_id) AND &flex_where_gl_account &gc_currency &gc_status &gc_org_where &gc_supplier &gc_where_clause &gc_additional_where ORDER BY gl_date |
| Parameter Name | SQL text | Validation | |
|---|---|---|---|
| Operating Unit |
|
LOV Oracle | |
| Period From |
|
LOV Oracle | |
| Period To |
|
LOV Oracle | |
| Supplier From |
|
LOV Oracle |
