Oracle Cloud Fusion R13 Account Payable (AP) Prepayment Balance SQL
=========================================================================
SELECT rownum unq_ident
,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
,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
,--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
,--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
,gl_import_references gir
,gl_je_headers gjh
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 xal.accounting_class_code <> 'LIABILITY'
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 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
No comments:
Post a Comment