AP Trial Balance
Description
Categories: Enginatics, R12 only
Repository: Github
Repository: Github
Application: Payables
Source: Accounts Payable Trial Balance
Short Name: APTBRPT
DB package: XLA_TB_AP_REPORT_PVT
For scheduling the report to run periodically, use the 'as of relative period close' offset parameter. This is the relative period offset to the current period, so when the current period changes, the period close as of date will also be automatically updated when the r ... more
Source: Accounts Payable Trial Balance
Short Name: APTBRPT
DB package: XLA_TB_AP_REPORT_PVT
For scheduling the report to run periodically, use the 'as of relative period close' offset parameter. This is the relative period offset to the current period, so when the current period changes, the period close as of date will also be automatically updated when the r ... more
with xtb as (select xtbg.* from ( &P_TEMPLATE_SQL_STATEMENT &P_SQL_STATEMENT ) xtbg ), aptb_trx as (select 'Transaction' record_type, xtb.ledger_id, xtb.ledger_name, xtb.ledger_short_name, xtb.ledger_currency_code currency, xtb.source_trx_curr trx_entered_currency, xtb.account, xtb.code_combination_id, to_number(null) gl_balance, to_number(null) other_sources_amount, to_number(null) subledger_manuals_amount, to_number(null) difference, xtb.third_party_name, xtb.third_party_number, xtb.source_entity_id transaction_id, xtb.source_trx_type transaction_type, xtb.source_trx_number transaction_number, aia.description invoice_description, aia.exchange_rate invoice_exchange_rate, aia.exchange_rate_type invoice_exchange_rate_type, aia.exchange_date invoice_exchange_date, to_date(xtb.source_trx_gl_date,'YYYY-MM-DD') gl_date, xtb.user_trx_identifier_value_7 payment_status, xtb.user_trx_identifier_value_9 cancelled_date, xtb.src_acctd_rounded_orig_amt transaction_original_amount, xtb.src_acctd_rounded_rem_amt transaction_remaining_amount, xtb.src_entered_rounded_orig_amt entered_currency_original_amt, xtb.src_entered_rounded_rem_amt entered_currency_remaining_amt from xtb, ap_invoices_all aia, xla_transaction_entities xte where xte.application_id=200 and xte.entity_code='AP_INVOICES' and xte.source_id_int_1=aia.invoice_id and xte.entity_id=xtb.source_entity_id ), aptb_acc as (select 'GL Account' record_type, xtb.ledger_id, xtb.ledger_name, xtb.ledger_short_name, xtb.ledger_currency_code currency, null trx_entered_currency, xtb.account, xtb.code_combination_id, xtb.gl_balance, xtb.non_ap_amount other_sources_amount, xtb.manual_sla_amount subledger_manuals_amount, nvl(xtb.gl_balance,0) - ( nvl(xtb.non_ap_amount,0) + nvl(xtb.manual_sla_amount,0) + nvl(sum(xtb.src_acctd_rounded_rem_amt),0) ) difference, null third_party_name, null third_party_number, null transaction_id, null transaction_type, null transaction_number, null invoice_description, null invoice_exchange_rate, null invoice_exchange_rate_type, null invoice_exchange_date, to_date(null) gl_date, null payment_status, null cancelled_date, to_number(null) transaction_original_amount, to_number(null) transaction_remaining_amount, to_number(null) entered_currency_original_amt, to_number(null) entered_currency_remaining_amt from xtb group by xtb.ledger_id, xtb.ledger_name, xtb.ledger_short_name, xtb.ledger_currency_code, xtb.account, xtb.gl_balance, xtb.non_ap_amount, xtb.manual_sla_amount, xtb.code_combination_id ) -- -- Main Query Starts Here -- select aptb.record_type, aptb.ledger_name, aptb.account account_combination, aptb.third_party_name, aptb.third_party_number, aptb.gl_balance, aptb.other_sources_amount, aptb.subledger_manuals_amount, aptb.transaction_remaining_amount, aptb.entered_currency_remaining_amt, aptb.difference, aptb.transaction_original_amount, aptb.entered_currency_original_amt, &reval_columns aptb.transaction_type, aptb.transaction_number, aptb.invoice_description, aptb.invoice_exchange_rate, aptb.invoice_exchange_rate_type, aptb.invoice_exchange_date, aptb.gl_date, aptb.payment_status, aptb.cancelled_date, &gcc_segment_columns xxen_util.meaning(gcck.gl_account_type,'ACCOUNT_TYPE',0) account_type &lp_init_sql from (select aptb_trx.*, decode(aptb_trx.currency,:p_reval_currency,1,(select gdr.conversion_rate from gl_daily_conversion_types gdct, gl_daily_rates gdr where aptb_trx.currency=gdr.from_currency and gdr.to_currency=:p_reval_currency and :p_reval_conv_date=gdr.conversion_date and gdct.user_conversion_type=:p_reval_conv_type and gdct.conversion_type=gdr.conversion_type)) reval_conv_rate from aptb_trx union all select aptb_acc.*, decode(aptb_acc.currency,:p_reval_currency,1,(select gdr.conversion_rate from gl_daily_conversion_types gdct, gl_daily_rates gdr where aptb_acc.currency=gdr.from_currency and gdr.to_currency=:p_reval_currency and :p_reval_conv_date=gdr.conversion_date and gdct.user_conversion_type=:p_reval_conv_type and gdct.conversion_type=gdr.conversion_type)) reval_conv_rate from aptb_acc ) aptb, gl_code_combinations_kfv gcck where 1=1 and nvl(:p_report_code,'?') = nvl(:p_report_code,'?') and nvl(:p_operating_unit,'?') = nvl(:p_operating_unit,'?') and nvl(:p_relative_period,'?') = nvl(:p_relative_period,'?') and gcck.code_combination_id (+) = aptb.code_combination_id order by aptb.ledger_name, aptb.account nulls last, decode(aptb.record_type,'GL Account',1,2), aptb.third_party_name nulls last, aptb.third_party_number nulls last, aptb.transaction_id nulls last, aptb.gl_date nulls last |
| Parameter Name | SQL text | Validation | |
|---|---|---|---|
| Operating Unit | LOV | ||
| Report Definition | LOV | ||
| Journal Source | LOV | ||
| As of Date | Date | ||
| Third Party Name |
| LOV | |
| Include Write Offs | LOV Oracle | ||
| Payables Account From | LOV | ||
| Payables Account To | LOV | ||
| Include SLA Manuals/Other Sources | LOV Oracle | ||
| Revaluation Currency | LOV | ||
| Revaluation Rate Type | LOV | ||
| Revaluation Date | Date | ||
| As of Relative Period Close | LOV |