JL General Ledger Inventory and Balances Book Report- Not Supported: Reserved For Future Use

➜ Back to Library
Description
Categories: BI Publisher
Imported from 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
Run JL General Ledger Inventory and Balances Book Report- Not Supported: Reserved For Future Use and other Oracle EBS reports with Blitz Report™ on our demo environment
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',(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, 
	ACCT ACCT, 
	ACCT_DESC ACCT_DESC, 
	JL_JGZZGLTRB_XMLP_PKG.le_nameformula(&SELECT_LE, :LE_NAME) LE_NAME, 
	JL_JGZZGLTRB_XMLP_PKG.le_addr1formula(&SELECT_LE, :LE_ADDR1) LE_ADDR1, 
	JL_JGZZGLTRB_XMLP_PKG.le_addr2formula(&SELECT_LE, :LE_ADDR2) LE_ADDR2, 
	JL_JGZZGLTRB_XMLP_PKG.le_addr3formula(&SELECT_LE, :LE_ADDR3) LE_ADDR3, 
	JL_JGZZGLTRB_XMLP_PKG.le_cityformula(&SELECT_LE, :LE_CITY) LE_CITY, 
	JL_JGZZGLTRB_XMLP_PKG.le_postal_codeformula(&SELECT_LE, :LE_POSTAL_CODE) LE_POSTAL_CODE, 
	JL_JGZZGLTRB_XMLP_PKG.le_service_typeformula(&SELECT_LE, :LE_SERVICE_TYPE) LE_SERVICE_TYPE, 
	JL_JGZZGLTRB_XMLP_PKG.le_tax_payer_idformula(&SELECT_LE, :LE_TAX_PAYER_ID) LE_TAX_PAYER_ID, 
	JL_JGZZGLTRB_XMLP_PKG.acct_valueformula(ACCOUNT_SEGMENT_VALUE, :ACCT_VALUE_DESC) ACCT_VALUE, 
	ACCT_VALUE_DESC ACCT_VALUE_DESC, 
	BALANCING_SEGMENT_DESC BALANCING_SEGMENT_DESC, 
	COST_CENTER_DESC COST_CENTER_DESC, 
	INTERCOMPANY_SEGMENT_DESC INTERCOMPANY_SEGMENT_DESC, 
	MANAGEMENT_SEGMENT_DESC MANAGEMENT_SEGMENT_DESC, 
	SEC_TRACK_SEGMENT_DESC SEC_TRACK_SEGMENT_DESC, 
	JL_JGZZGLTRB_XMLP_PKG.acct_level1_valueformula(ACCOUNT_SEGMENT_VALUE, :ACCT_VALUE_DESC) ACCT_LEVEL1_VALUE, 
	JL_JGZZGLTRB_XMLP_PKG.acct_level1_value_descformula(ACCOUNT_SEGMENT_VALUE, :ACCT_VALUE_DESC) ACCT_LEVEL1_VALUE_DESC, 
	JL_JGZZGLTRB_XMLP_PKG.acct_level2_valueformula(ACCOUNT_SEGMENT_VALUE, :ACCT_VALUE_DESC) ACCT_LEVEL2_VALUE, 
	JL_JGZZGLTRB_XMLP_PKG.acct_level2_value_descformula(ACCOUNT_SEGMENT_VALUE, :ACCT_VALUE_DESC) ACCT_LEVEL2_VALUE_DESC, 
	JL_JGZZGLTRB_XMLP_PKG.acct_level3_valueformula(ACCOUNT_SEGMENT_VALUE, :ACCT_VALUE_DESC) ACCT_LEVEL3_VALUE, 
	JL_JGZZGLTRB_XMLP_PKG.acct_level3_value_descformula(ACCOUNT_SEGMENT_VALUE, :ACCT_VALUE_DESC) ACCT_LEVEL3_VALUE_DESC, 
	JL_JGZZGLTRB_XMLP_PKG.acct_level4_valueformula(ACCOUNT_SEGMENT_VALUE, :ACCT_VALUE_DESC) ACCT_LEVEL4_VALUE, 
	JL_JGZZGLTRB_XMLP_PKG.acct_level4_value_descformula(ACCOUNT_SEGMENT_VALUE, :ACCT_VALUE_DESC) ACCT_LEVEL4_VALUE_DESC, 
	JL_JGZZGLTRB_XMLP_PKG.acct_level5_valueformula(ACCOUNT_SEGMENT_VALUE, :ACCT_VALUE_DESC) ACCT_LEVEL5_VALUE, 
	JL_JGZZGLTRB_XMLP_PKG.acct_level5_value_descformula(ACCOUNT_SEGMENT_VALUE, :ACCT_VALUE_DESC) ACCT_LEVEL5_VALUE_DESC, 
	JL_JGZZGLTRB_XMLP_PKG.acct_level6_valueformula(ACCOUNT_SEGMENT_VALUE, :ACCT_VALUE_DESC) ACCT_LEVEL6_VALUE, 
	JL_JGZZGLTRB_XMLP_PKG.acct_level6_value_descformula(ACCOUNT_SEGMENT_VALUE, :ACCT_VALUE_DESC) ACCT_LEVEL6_VALUE_DESC, 
	JL_JGZZGLTRB_XMLP_PKG.acct_level7_valueformula(ACCOUNT_SEGMENT_VALUE, :ACCT_VALUE_DESC) ACCT_LEVEL7_VALUE, 
	JL_JGZZGLTRB_XMLP_PKG.acct_level7_value_descformula(ACCOUNT_SEGMENT_VALUE, :ACCT_VALUE_DESC) ACCT_LEVEL7_VALUE_DESC, 
	JL_JGZZGLTRB_XMLP_PKG.acct_level8_valueformula(ACCOUNT_SEGMENT_VALUE, :ACCT_VALUE_DESC) ACCT_LEVEL8_VALUE, 
	JL_JGZZGLTRB_XMLP_PKG.acct_level8_value_descformula(ACCOUNT_SEGMENT_VALUE, :ACCT_VALUE_DESC) ACCT_LEVEL8_VALUE_DESC, 
	JL_JGZZGLTRB_XMLP_PKG.acct_level9_valueformula(ACCOUNT_SEGMENT_VALUE, :ACCT_VALUE_DESC) ACCT_LEVEL9_VALUE, 
	JL_JGZZGLTRB_XMLP_PKG.acct_level9_value_descformula(ACCOUNT_SEGMENT_VALUE, :ACCT_VALUE_DESC) ACCT_LEVEL9_VALUE_DESC, 
	JL_JGZZGLTRB_XMLP_PKG.acct_level10_value_descformula(ACCOUNT_SEGMENT_VALUE, :ACCT_VALUE_DESC) ACCT_LEVEL10_VALUE_DESC, 
	JL_JGZZGLTRB_XMLP_PKG.acct_level10_valueformula(ACCOUNT_SEGMENT_VALUE, :ACCT_VALUE_DESC) ACCT_LEVEL10_VALUE,
  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',(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
Ledger 
 
 LOV Oracle
Ledger Currency