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 |
