JL General Ledger Inventory and Balances Book - draft

Description
Categories: BI Publisher
Application: Latin America Localizations
Source: General Ledger Inventory and Balances Book Report (XML) - Not Supported: Reserved For Future Use
Short Name: JLCBGLTRB_XML
DB package: JL_JGZZGLTRB_XMLP_PKG
SELECT 
  ACCOUNT ,
  ACCOUNT_SEGMENT_VALUE,
  BALANCING_SEGMENT_VALUE,
  COST_CENTER_VALUE,
  INTERCOMPANY_SEGMENT_VALUE,
  MANAGEMENT_SEGMENT_VALUE,
  SEC_TRACK_SEGMENT_VALUE,
  ACCOUNT_TYPE,
  &SELECT_LE LEGAL_ENTITY_ID,
  decode(:P_ZERO_BYB, 'Y', 0, YEAR_BEGIN_DR) ADJ_BEGIN_YEAR_DR,
  decode(:P_ZERO_BYB, 'Y', 0, YEAR_BEGIN_CR) ADJ_BEGIN_YEAR_CR,
  ADJ_FIRST_PERIOD_DR,
  ADJ_FIRST_PERIOD_CR,
  FROM_PERIOD_BEGIN_DR - nvl(YEAR_BEGIN_DR, 0) PRIOR_PERIOD_DR,
  FROM_PERIOD_BEGIN_CR - nvl(YEAR_BEGIN_CR, 0) PRIOR_PERIOD_CR,
  PERIODS_TOTAL_DR PERIOD_DR,
  PERIODS_TOTAL_CR PERIOD_CR,
  TO_PERIOD_END_DR - decode(:P_ZERO_BYB, 'Y', YEAR_BEGIN_DR, 0) END_BALANCE_DR,
  TO_PERIOD_END_CR - decode(:P_ZERO_BYB, 'Y', YEAR_BEGIN_CR, 0) END_BALANCE_CR,
   decode(SIGN(decode(:P_ZERO_BYB,'Y',0,YEAR_BEGIN_DR - YEAR_BEGIN_CR)),1,YEAR_BEGIN_DR - YEAR_BEGIN_CR,0) NET_BEGIN_DR_BAL,
   decode(SIGN(decode(:P_ZERO_BYB,'Y',0,YEAR_BEGIN_DR - YEAR_BEGIN_CR)),-1,YEAR_BEGIN_CR - YEAR_BEGIN_DR,0) NET_BEGIN_CR_BAL,
   decode(SIGN(decode(:P_ZERO_BYB, 'Y',(TO_PERIOD_END_DR - YEAR_BEGIN_DR) - (TO_PERIOD_END_CR - YEAR_BEGIN_CR),TO_PERIOD_END_DR - TO_PERIOD_END_CR)),1,decode(:P_ZERO_BYB, 'Y',(TO_PERIOD_END_DR - YEAR_BEGIN_DR) - (TO_PERIOD_END_CR - YEAR_BEGIN_CR),TO_PERIOD_END_DR - TO_PERIOD_END_CR),0) NET_ENDING_DR_BAL,
   decode(SIGN(decode(:P_ZERO_BYB, 'Y',(TO_PERIOD_END_DR - YEAR_BEGIN_DR) - (TO_PERIOD_END_CR - YEAR_BEGIN_CR),TO_PERIOD_END_DR - TO_PERIOD_END_CR)),-1,decode(:P_ZERO_BYB, 'Y''Y', YEAR_BEGIN_DR, 0) END_BALANCE_DR,
  TO_PERIOD_END_CR - decode(:P_ZERO_BYB, 'Y', YEAR_BEGIN_CR, 0) END_BALANCE_CR,
  decode(SIGN(decode(:P_ZERO_BYB,'Y',0,YEAR_BEGIN_DR - YEAR_BEGIN_CR)),1,YEAR_BEGIN_DR - YEAR_BEGIN_CR,0) NET_BEGIN_DR_BAL,
  decode(SIGN(decode(:P_ZERO_BYB,'Y',0,YEAR_BEGIN_DR - YEAR_BEGIN_CR)),-1,YEAR_BEGIN_CR - YEAR_BEGIN_DR,0) NET_BEGIN_CR_BAL,
  decode(SIGN(decode(:P_ZERO_BYB, 'Y',(TO_PERIOD_END_DR - YEAR_BEGIN_DR) - (TO_PERIOD_END_CR - YEAR_BEGIN_CR),TO_PERIOD_END_DR - TO_PERIOD_END_CR)),1,decode(:P_ZERO_BYB, 'Y',(TO_PERIOD_END_DR - YEAR_BEGIN_DR) - (TO_PERIOD_END_CR - YEAR_BEGIN_CR),TO_PERIOD_END_DR - TO_PERIOD_END_CR),0) NET_ENDING_DR_BAL,
  decode(SIGN(decode(:P_ZERO_BYB, 'Y',(TO_PERIOD_END_DR - YEAR_BEGIN_DR) - (TO_PERIOD_END_CR - YEAR_BEGIN_CR),TO_PERIOD_END_DR - TO_PERIOD_END_CR)),-1,decode(:P_ZERO_BYB, 'Y',(TO_PERIOD_END_CR - YEAR_BEGIN_CR) - (TO_PERIOD_END_DR - YEAR_BEGIN_DR),TO_PERIOD_END_CR - TO_PERIOD_END_DR),0) NET_ENDING_CR_BAL
FROM
(SELECT
  &SELECT_ACCOUNT	ACCOUNT,
  max(&SELECT_ACCT_SEG)	ACCOUNT_SEGMENT_VALUE,
  max(&SELECT_BAL_SEG)	BALANCING_SEGMENT_VALUE,
  max(&SELECT_CC_SEG)	COST_CENTER_VALUE,
  max(&SELECT_IC_SEG)	INTERCOMPANY_SEGMENT_VALUE,
  max(&SELECT_MGT_SEG) 	MANAGEMENT_SEGMENT_VALUE,
  max(&SELECT_ST_SEG)	SEC_TRACK_SEGMENT_VALUE,
  max(gcc.account_type)	ACCOUNT_TYPE,
  max(null) LEGAL_ENTITY_ID,
  fnd_flex_xml_publisher_apis.process_kff_combination_1('acct', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') ACCT, 
  fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_desc', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'N', 'DESCRIPTION') ACCT_DESC,
  sum(decode(bal.period_name, :FIRST_PERIOD_NAME, decode(:P_CURRENCY_TYPE, 'E', decode(nvl(bal.translated_flag,'R'),'R', nvl(bal.begin_balance_dr, 0), nvl(bal.begin_balance_dr_beq, 0)), nvl(bal.begin_balance_dr, 0)), 0))  YEAR_BEGIN_DR,
  sum(decode(bal.period_name, :FIRST_PERIOD_NAME, decode(:P_CURRENCY_TYPE, 'E', decode(nvl(bal.translated_flag,'R'), 'R', nvl(bal.begin_balance_cr, 0), nvl(bal.begin_balance_cr_beq, 0)), nvl(bal.begin_balance_cr, 0)), 0))  YEAR_BEGIN_CR,
  sum(decode(:P_PERIOD_FROM, :FIRST_PERIOD_NAME, 0, decode(:FIRST_PERIOD_ADJ_FLAG, 'Y', decode(bal.period_name, :FIRST_PERIOD_NAME, decode(:P_CURRENCY_TYPE, 'E', decode(nvl(bal.translated_flag,'R'), 'R', nvl(bal.period_net_dr, 0), nvl(bal.period_net_dr_beq, 0)), nvl(bal.period_net_dr, 0)), 0), 0)))  ADJ_FIRST_PERIOD_DR,
  sum(decode(:P_PERIOD_FROM, :FIRST_PERIOD_NAME, 0, decode(:FIRST_PERIOD_ADJ_FLAG, 'Y', decode(bal.period_name, :FIRST_PERIOD_NAME, decode(:P_CURRENCY_TYPE, 'E', decode(nvl(bal.translated_flag, 'R'),'R', nvl(bal.period_net_cr, 0), nvl(bal.period_net_cr_beq, 0)), nvl(bal.period_net_cr, 0)), 0), 0)))  ADJ_FIRST_PERIOD_CR,
  sum(decode(bal.period_name, :P_PERIOD_FROM, decode(:P_CURRENCY_TYPE, 'E', decode(nvl(bal.translated_flag,'R'), 'R', nvl(bal.begin_balance_dr, 0), nvl(bal.begin_balance_dr_beq, 0)), nvl(bal.begin_balance_dr, 0)), 0))  FROM_PERIOD_BEGIN_DR,
  sum(decode(bal.period_name, :P_PERIOD_FROM, decode(:P_CURRENCY_TYPE, 'E', decode(nvl(bal.translated_flag, 'R'),'R', nvl(bal.begin_balance_cr, 0), nvl(bal.begin_balance_cr_beq, 0)), nvl(bal.begin_balance_cr, 0)), 0))  FROM_PERIOD_BEGIN_CR,
  sum(decode(bal.period_name, :FIRST_PERIOD_NAME, decode(:P_PERIOD_FROM, :FIRST_PERIOD_NAME, decode(:P_CURRENCY_TYPE, 'E', decode(nvl(bal.translated_flag, 'R'),'R', nvl(bal.period_net_dr, 0), nvl(bal.period_net_dr_beq, 0)), nvl(bal.period_net_dr, 0)), 0), decode(:P_CURRENCY_TYPE, 'E', decode(nvl(bal.translated_flag, 'R'),'R', nvl(bal.period_net_dr, 0), nvl(bal.period_net_dr_beq, 0)), nvl(bal.period_net_dr, 0))))	PERIODS_TOTAL_DR,
  sum(decode(bal.period_name, :FIRST_PERIOD_NAME, decode(:P_PERIOD_FROM, :FIRST_PERIOD_NAME, decode(:P_CURRENCY_TYPE, 'E', decode(nvl(bal.translated_flag, 'R'),'R', nvl(bal.period_net_cr, 0), nvl(bal.period_net_cr_beq, 0)), nvl(bal.period_net_cr, 0)), 0), decode(:P_CURRENCY_TYPE, 'E', decode(nvl(bal.translated_flag, 'R'),'R', nvl(bal.period_net_cr, 0), nvl(bal.period_net_cr_beq, 0)), nvl(bal.period_net_cr, 0))))	PERIODS_TOTAL_CR,
  sum(decode(bal.period_name, :P_PERIOD_TO, decode(:P_CURRENCY_TYPE, 'E', decode(nvl(bal.translated_flag,'R'), 'R', nvl(bal.begin_balance_dr, 0) + nvl(bal.period_net_dr, 0), nvl(bal.begin_balance_dr_beq, 0) + nvl(bal.period_net_dr_beq, 0)), nvl(bal.begin_balance_dr, 0) + nvl(bal.period_net_dr, 0)), 0))  TO_PERIOD_END_DR,
  sum(decode(bal.period_name, :P_PERIOD_TO, decode(:P_CURRENCY_TYPE, 'E', decode(nvl(bal.translated_flag, 'R'),'R', nvl(bal.begin_balance_cr, 0) + nvl(bal.period_net_cr, 0), nvl(bal.begin_balance_cr_beq, 0) + nvl(bal.period_net_cr_beq, 0)), nvl(bal.begin_balance_cr, 0) + nvl(bal.period_net_cr, 0)), 0))  TO_PERIOD_END_CR,
  fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_value_desc', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'N', 'DESCRIPTION') ACCT_VALUE_DESC, 
  fnd_flex_xml_publisher_apis.process_kff_combination_1('balancing_segment_desc', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'N', 'DESCRIPTION') BALANCING_SEGMENT_DESC, 
  fnd_flex_xml_publisher_apis.process_kff_combination_1('cost_center_desc', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'N', 'DESCRIPTION') COST_CENTER_DESC, 
  fnd_flex_xml_publisher_apis.process_kff_combination_1('intercompany_segment_desc', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'GL_INTERCOMPANY', 'N', 'DESCRIPTION') INTERCOMPANY_SEGMENT_DESC, 
  fnd_flex_xml_publisher_apis.process_kff_combination_1('management_segment_desc', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'GL_MANAGEMENT', 'N', 'DESCRIPTION') MANAGEMENT_SEGMENT_DESC, 
  fnd_flex_xml_publisher_apis.process_kff_combination_1('sec_track_segment_desc', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'GL_SECONDARY_TRACKING', 'N', 'DESCRIPTION') SEC_TRACK_SEGMENT_DESC
FROM
  GL_CODE_COMBINATIONS	gcc,
  GL_BALANCES		bal
WHERE gcc.chart_of_accounts_id = :COA_ID
and decode(:P_REPORT_TYPE, null, :PER_TO_END_DATE, nvl(gcc.start_date_active, :PER_TO_END_DATE)) <= :PER_TO_END_DATE
and &WHERE_ACCT_RANGE
&WHERE_ACCT_CLASS
and bal.ledger_id = :P_LEDGER_ID
and bal.code_combination_id = gcc.code_combination_id
and bal.period_name IN ( select per.period_name 
                          from GL_PERIODS per
			 where  per.period_set_name = :PERIOD_SET_NAME
                         &WHERE_PERIOD)
and bal.currency_code = decode(:P_CURRENCY_TYPE, 'T', :P_LEDGER_CURRENCY, :P_ENTERED_CURRENCY)
&WHERE_TRANSLATED_FLAG
and bal.actual_flag = 'A'
and bal.template_id IS NULL
&WHERE_DEGREE
&WHERE_DAS
GROUP BY &SELECT_ACCOUNT,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE'),fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_desc', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'N', 'DESCRIPTION'),fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_value_desc', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'N', 'DESCRIPTION'),fnd_flex_xml_publisher_apis.process_kff_combination_1('balancing_segment_desc', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'N', 'DESCRIPTION'),fnd_flex_xml_publisher_apis.process_kff_combination_1('cost_center_desc', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'N', 'DESCRIPTION'),fnd_flex_xml_publisher_apis.process_kff_combination_1('intercompany_segment_desc', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'GL_INTERCOMPANY', 'N', 'DESCRIPTION'),fnd_flex_xml_publisher_apis.process_kff_combination_1('management_segment_desc', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'GL_MANAGEMENT', 'N', 'DESCRIPTION'),fnd_flex_xml_publisher_apis.process_kff_combination_1('sec_track_segment_desc', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'GL_SECONDARY_TRACKING', 'N', 'DESCRIPTION')) &FROM_LNSV
&WHERE_LE
 ORDER BY ACCOUNT_SEGMENT_VALUE, LEGAL_ENTITY_ID
Parameter Name SQL text Validation
JGZZ_SRS_LEDGER_ID_UNVALIDATED
 
Number
Chart Of Accounts Id
 
LOV Oracle
Access Set Id
 
Number
User Parameter 5
 
User Parameter 4
 
User Parameter 3
 
User Parameter 2
 
User Parameter 1
 
Page Number Format
 
LOV Oracle
Trial Balance Type
 
LOV Oracle
Zero Beginning of Year Balance
 
LOV Oracle
Account Delimiter
 
LOV Oracle
Account Class
 
LOV Oracle
Top Level Parent Account
 
Account Level
 
LOV Oracle
Period To
 
LOV Oracle
Period From
 
LOV Oracle
Account To
 
Account From
 
Entered Currency
 
LOV Oracle
Currency Type
 
LOV Oracle
Ledger Currency
 
LOV Oracle
Ledger
 
LOV Oracle