AP Trial Balance

Description
Categories: Enginatics
Repository: Github
Application: Payables
Source: Accounts Payable Trial Balance
Short Name: APTBRPT
DB package: XLA_TB_AP_REPORT_PVT
with xtb as (select 
   xtbg.*
  from 
   ( &P_SQL_STATEMENT AND :L_RUN_DETAIL_REPORT = 'Y' ) xtbg),
aptb_trx as (select 
   'Transaction' record_type,
   xtb.ledger_id,
   xtb.ledger_name,
   xtb.ledger_short_name,
   xtb.ledger_currency_code currency,
   xtb.account 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,
   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
  from 
   xtb),
aptb_tpt as (select 
   'Third Party Total' record_type,
   xtb.ledger_id,
   xtb.ledger_name,
   xtb.ledger_short_name,
   xtb.ledger_currency_code 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,
   null transaction_id,
   null transaction_type,
   null transaction_number,
   to_date(null) gl_date,
   null payment_status,
   null cancelled_date,
   to_number(null) transaction_original_amount,
   sum(xtb.src_acctd_rounded_rem_amt) transaction_remaining_amount
  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,
   xtb.third_party_name,
   xtb.third_party_number),
aptb_acc as (select 
   'Account Total' record_type,
   xtb.ledger_id,
   xtb.ledger_name,
   xtb.ledger_short_name,
   xtb.ledger_currency_code 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,
   to_date(null) gl_date,
   null payment_status,
   null cancelled_date,
   to_number(null) transaction_original_amount,
   sum(xtb.src_acctd_rounded_rem_amt) transaction_remaining_amount
  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),
aptb_ldg as (select 
   'Ledger Total' record_type,
   aptb_acc.ledger_id,
   aptb_acc.ledger_name,
   aptb_acc.ledger_short_name,
   aptb_acc.currency,
   null account,
   to_number(null) code_combination_id,
   sum(aptb_acc.gl_balance) gl_balance,
   sum(aptb_acc.other_sources_amount) other_sources_amount,
   sum(aptb_acc.subledger_manuals_amount) subledger_manuals_amount,
   sum(aptb_acc.difference) difference,
   null third_party_name,
   null third_party_number,
   null transaction_id,
   null transaction_type,
   null transaction_number,
   to_date(null) gl_date,
   null payment_status,
   null cancelled_date,
   to_number(null) transaction_original_amount,
   sum(aptb_acc.transaction_remaining_amount) transaction_remaining_amount
  from 
   aptb_acc
  group by 
   aptb_acc.ledger_id,
   aptb_acc.ledger_name,
   aptb_acc.ledger_short_name,
   aptb_acc.currency)
--
-- Main Query Starts Here
--
select 
 aptb.record_type
,aptb.ledger_name
,aptb.account
,aptb.third_party_name
,aptb.third_party_number
,aptb.currency
,aptb.gl_balance
&l_manual_other_amount_columns
,aptb.transaction_remaining_amount
,aptb.difference 
&l_detail_columns 
&gcc_segment_columns
from 
 (select 
   aptb_trx.*
  from 
   aptb_trx &l_detail_where
  union all 
  select 
   aptb_tpt.*
  from 
   aptb_tpt
  union all 
  select 
   aptb_acc.*
  from 
   aptb_acc
  union all 
  select 
   aptb_ldg.*
  from 
   aptb_ldg) aptb,
 gl_code_combinations_kfv gcck
where
 gcck.code_combination_id (+) = aptb.code_combination_id 
order by 
 aptb.ledger_name,
 aptb.account nulls last,
 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
Report Mode
 
LOV Oracle
XLA_SRS_SUBLEDGERS
 
LOV Oracle
Start Date
 
Date
Chart of Accounts
 
Number
Account Balance
 
LOV Oracle
Security
 
LOV Oracle
P_INC_SLA_MANUALS_UNPOSTED
 
Char
P_REPORT_STYLE
 
Char
Operating Unit
 
LOV Oracle
Report Definition
 
LOV Oracle
Journal Source
 
LOV Oracle
As of Date
 
Date
Third Party Name
 
LOV Oracle
Show Transaction Detail
 
LOV Oracle
Include Write Offs
 
LOV Oracle
Payables Account From
 
LOV
Payables Account To
 
LOV
Include SLA Manuals/Other Sources
 
LOV Oracle
Show Transaction Detail
,aptb.transaction_type
,aptb.transaction_number
,aptb.transaction_original_amount
,aptb.gl_date
,aptb.payment_status
,aptb.cancelled_date
Show Transaction Detail
where :p_show_trx_detail_flag = 'Y'
Show Transaction Detail
select null from dual
Include SLA Manuals/Other Sources
,aptb.other_sources_amount
,aptb.subledger_manuals_amount
Include SLA Manuals/Other Sources
select null from dual
Report Definition
select distinct
',gcck.'||lower(fifsv.application_column_name)||' "'||fifsv.form_left_prompt||'"'||chr(10)||
',xxen_util.segment_description(gcck.'||lower(fifsv.application_column_name)||', '''||fifsv.application_column_name||''', gcck.chart_of_accounts_id)'||' "'||fifsv.form_left_prompt||' desc"' text,
min(fifsv.id_flex_num) over (partition by fifsv.application_column_name, fifsv.form_left_prompt) min_id_flex_num,
min(fifsv.segment_num) over (partition by fifsv.application_column_name, fifsv.form_left_prompt) min_segment_num
from
fnd_id_flex_segments_vl fifsv
where
fifsv.application_id=101 and
fifsv.id_flex_code='GL#' and
fifsv.id_flex_num in
 (select gl.chart_of_accounts_id
  from xla_tb_definitions_vl xtdv,
       gl_ledgers gl
  where xtdv.definition_code = :p_definition_code
  and   gl.ledger_id = xtdv.ledger_id
 )
order by
min_id_flex_num,
min_segment_num