GL Trial Balance - Expanded
Description
- Categories
- BI Publisher
- Application
- General Ledger
- Source
- Trial Balance - Expanded (XML)
- Short Name
- GLYRLBGE_XML
- DB package
- GL_GLYRLBGE_XMLP_PKG
Imported from BI Publisher
Expanded Trial Balance
Expanded Trial Balance
SQL 419 lines
select -- null ACCOUNT, --null COMPANY, fnd_flex_xml_publisher_apis.process_kff_combination_1('accounting_segment', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE') ACCOUNT, fnd_flex_xml_publisher_apis.process_kff_combination_1('balancing_segment', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') COMPANY, substr(fnd_flex_xml_publisher_apis.process_kff_combination_1('accounting_segment', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE'),1,3) Groups, substr(fnd_flex_xml_publisher_apis.process_kff_combination_1('accounting_segment', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE'),1,2) Sub_Class, substr(fnd_flex_xml_publisher_apis.process_kff_combination_1('accounting_segment', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE'),1,1) Class, decode(:P_CURRENCY_TYPE, 'T', decode(sign(sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0))), 1, sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)),0) + sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)), 'S', decode(sign(sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0))), 1, sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)),0) + sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)), 'E', sum(decode(bl.translated_flag, 'R', decode(sign(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)), 1, decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0),0) + decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0), decode(sign(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr_beq,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr_beq,0),0)), 1, decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr_beq,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr_beq,0),0),0) + decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_dr_beq,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr_beq,0),0)))) BEGIN_DR, decode(:P_CURRENCY_TYPE, 'T', decode(sign(sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0))), -1, abs(sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0))),0) + sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)), 'S', decode(sign(sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0))), -1, abs(sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0))),0) + sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)), 'E', sum(decode(bl.translated_flag, 'R', decode(sign(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)), -1, abs(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)),0) + decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0), decode(sign(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr_beq,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr_beq,0),0)), -1, abs(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr_beq,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr_beq,0),0)),0) + decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_cr_beq,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr_beq,0),0)))) BEGIN_CR, decode(:P_CURRENCY_TYPE, 'T',sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_dr,0),0)), 'S',sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_dr,0),0)), 'E',sum(decode(bl.translated_flag, 'R',decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_dr,0),0), decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_dr_beq,0),0)))) PER_DR, decode(:P_CURRENCY_TYPE, 'T',sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_cr,0),0)), 'S',sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_cr,0),0)), 'E',sum(decode(bl.translated_flag, 'R',decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_cr,0),0), decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_cr_beq,0),0)))) PER_CR, decode(:P_CURRENCY_TYPE, 'T', decode(sign(sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0))), 1, sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)),0) + sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)), 'S', decode(sign(sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0))), 1, sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)),0) + sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)), 'E', sum(decode(bl.translated_flag, 'R', decode(sign(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)), 1, decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0),0) + decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0), decode(sign(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr_beq,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr_beq,0),0)), 1, decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr_beq,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr_beq,0),0),0) + decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_dr_beq,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr_beq,0),0)))) + decode(:P_CURRENCY_TYPE, 'T',sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_dr,0),0)), 'S',sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_dr,0),0)), 'E',sum(decode(bl.translated_flag, 'R',decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_dr,0),0), decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_dr_beq,0),0)))) END_DR, decode(:P_CURRENCY_TYPE, 'T', decode(sign(sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0))), -1, abs(sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0))),0) + sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)), 'S', decode(sign(sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0))), -1, abs(sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0))),0) + sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)), 'E', sum(decode(bl.translated_flag, 'R', decode(sign(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)), -1, abs(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)),0) + decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0), decode(sign(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr_beq,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr_beq,0),0)), -1, abs(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr_beq,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr_beq,0),0)),0) + decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_cr_beq,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr_beq,0),0)))) + decode(:P_CURRENCY_TYPE, 'T',sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_cr,0),0)), 'S',sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_cr,0),0)), 'E',sum(decode(bl.translated_flag, 'R',decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_cr,0),0), decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_cr_beq,0),0)))) END_CR, decode(:P_CURRENCY_TYPE, 'T', decode(sign(sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0))), 1, sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)),0) + sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)), 'S', decode(sign(sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0))), 1, sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)),0) + sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)), 'E', sum(decode(bl.translated_flag, 'R', decode(sign(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)), 1, decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0),0) + decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0), decode(sign(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr_beq,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr_beq,0),0)), 1, decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr_beq,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr_beq,0),0),0) + decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_dr_beq,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr_beq,0),0)))) + decode(:P_CURRENCY_TYPE, 'T',sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_dr,0),0)), 'S',sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_dr,0),0)), 'E',sum(decode(bl.translated_flag, 'R',decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_dr,0),0), decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_dr_beq,0),0)))) - (decode(:P_CURRENCY_TYPE, 'T', decode(sign(sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0))), -1, abs(sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0))),0) + sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)), 'S', decode(sign(sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0))), -1, abs(sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0))),0) + sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)), 'E', sum(decode(bl.translated_flag, 'R', decode(sign(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)), -1, abs(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)),0) + decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0), decode(sign(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr_beq,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr_beq,0),0)), -1, abs(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr_beq,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr_beq,0),0)),0) + decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_cr_beq,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr_beq,0),0)))) + decode(:P_CURRENCY_TYPE, 'T',sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_cr,0),0)), 'S',sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_cr,0),0)), 'E',sum(decode(bl.translated_flag, 'R',decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_cr,0),0), decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_cr_beq,0),0))))) NET, max(LR.TARGET_LEDGER_name) LEDGER_NAME_H, --GL_GLYRLBGE_XMLP_PKG.c_bal_lpromptformula(:C_BAL_LPROMPT) C_BAL_LPROMPT, fnd_flex_xml_publisher_apis.process_kff_combination_1('disp_company', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') DISP_COMPANY, fnd_flex_xml_publisher_apis.process_kff_combination_1('company_name', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'DESCRIPTION') COMPANY_NAME, GL_GLYRLBGE_XMLP_PKG.comp_net_crformula(:COMP_NET) COMP_NET_CR, fnd_flex_xml_publisher_apis.process_kff_combination_1('bal_secure', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'SECURITY') BAL_SECURE, GL_GLYRLBGE_XMLP_PKG.cl_net_crformula(:CL_NET) CL_NET_CR, GL_GLYRLBGE_XMLP_PKG.sc_net_crformula(:SC_NET) SC_NET_CR, GL_GLYRLBGE_XMLP_PKG.gr_net_crformula(:GR_NET) GR_NET_CR, fnd_flex_xml_publisher_apis.process_kff_combination_1('account_desc', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'DESCRIPTION') ACCOUNT_DESC, fnd_flex_xml_publisher_apis.process_kff_combination_1('account_value', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE') ACCOUNT_VALUE, GL_GLYRLBGE_XMLP_PKG.zero_indicatorformula( decode(:P_CURRENCY_TYPE, 'T', decode(sign(sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0))), -1, abs(sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0))),0) + sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)), 'S', decode(sign(sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0))), -1, abs(sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0))),0) + sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)), 'E', sum(decode(bl.translated_flag, 'R', decode(sign(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)), -1, abs(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)),0) + decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0), decode(sign(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr_beq,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr_beq,0),0)), -1, abs(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr_beq,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr_beq,0),0)),0) + decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_cr_beq,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr_beq,0),0)))) , decode(:P_CURRENCY_TYPE, 'T', decode(sign(sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0))), 1, sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)),0) + sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)), 'S', decode(sign(sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0))), 1, sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)),0) + sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)), 'E', sum(decode(bl.translated_flag, 'R', decode(sign(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)), 1, decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0),0) + decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0), decode(sign(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr_beq,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr_beq,0),0)), 1, decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr_beq,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr_beq,0),0),0) + decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_dr_beq,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr_beq,0),0)))), decode(:P_CURRENCY_TYPE, 'T',sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_dr,0),0)), 'S',sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_dr,0),0)), 'E',sum(decode(bl.translated_flag, 'R',decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_dr,0),0), decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_dr_beq,0),0)))), decode(:P_CURRENCY_TYPE, 'T',sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_cr,0),0)), 'S',sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_cr,0),0)), 'E',sum(decode(bl.translated_flag, 'R',decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_cr,0),0), decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_cr_beq,0),0)))) ) ZERO_INDICATOR, GL_GLYRLBGE_XMLP_PKG.net_crformula( decode(:P_CURRENCY_TYPE, 'T', decode(sign(sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0))), 1, sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)),0) + sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)), 'S', decode(sign(sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0))), 1, sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)),0) + sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)), 'E', sum(decode(bl.translated_flag, 'R', decode(sign(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)), 1, decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0),0) + decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0), decode(sign(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr_beq,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr_beq,0),0)), 1, decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr_beq,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr_beq,0),0),0) + decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_dr_beq,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr_beq,0),0)))) + decode(:P_CURRENCY_TYPE, 'T',sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_dr,0),0)), 'S',sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_dr,0),0)), 'E',sum(decode(bl.translated_flag, 'R',decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_dr,0),0), decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_dr_beq,0),0)))) - (decode(:P_CURRENCY_TYPE, 'T', decode(sign(sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0))), -1, abs(sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0))),0) + sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)), 'S', decode(sign(sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0))), -1, abs(sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0))),0) + sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)) - sum(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)), 'E', sum(decode(bl.translated_flag, 'R', decode(sign(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)), -1, abs(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0)),0) + decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr,0),0), decode(sign(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr_beq,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr_beq,0),0)), -1, abs(decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_dr_beq,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr_beq,0),0)),0) + decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.begin_balance_cr_beq,0),0) - decode(bl.period_name,:FIRST_PERIOD_NAME,nvl(bl.begin_balance_cr_beq,0),0)))) + decode(:P_CURRENCY_TYPE, 'T',sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_cr,0),0)), 'S',sum(decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_cr,0),0)), 'E',sum(decode(bl.translated_flag, 'R',decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_cr,0),0), decode(bl.period_name,:P_PERIOD_NAME,nvl(bl.period_net_cr_beq,0),0)))))) NET_CR, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_secure', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'SECURITY') ACCT_SECURE FROM gl_balances BL, gl_code_combinations CC, GL_LEDGERS GLL, GL_LEDGER_SET_ASSIGNMENTS ASG, GL_LEDGER_RELATIONSHIPS LR WHERE bl.currency_code = :ACTUAL_CURRENCY AND bl.period_name in (:FIRST_PERIOD_NAME,:P_PERIOD_NAME) AND bl.actual_flag = 'A' AND &CURRENCY_WHERE AND &ACCESS_WHERE AND cc.template_id is null AND CC.SUMMARY_FLAG = 'N' AND &ACCOUNTING_SEGMENT_WHERE AND &BALANCING_SEGMENT_WHERE AND cc.chart_of_accounts_id = :STRUCT_NUM AND cc.code_combination_id = bl.code_combination_id AND GLL.LEDGER_ID = :P_LEDGER_ID AND ASG.LEDGER_SET_ID(+) = GLL.LEDGER_ID AND LR.TARGET_LEDGER_ID = NVL(ASG.LEDGER_ID, GLL.LEDGER_ID) AND LR.SOURCE_LEDGER_ID = NVL(ASG.LEDGER_ID, GLL.LEDGER_ID) AND LR.TARGET_CURRENCY_CODE = :P_LEDGER_CURRENCY AND LR.SOURCE_LEDGER_ID = BL.LEDGER_ID AND LR.TARGET_LEDGER_ID = BL.LEDGER_ID GROUP BY fnd_flex_xml_publisher_apis.process_kff_combination_1('accounting_segment', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE'), fnd_flex_xml_publisher_apis.process_kff_combination_1('balancing_segment', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'), LR.TARGET_LEDGER_NAME --ADDED ,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_secure', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'SECURITY'),fnd_flex_xml_publisher_apis.process_kff_combination_1('account_value', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE'),fnd_flex_xml_publisher_apis.process_kff_combination_1('account_desc', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'DESCRIPTION'),fnd_flex_xml_publisher_apis.process_kff_combination_1('bal_secure', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'SECURITY'),fnd_flex_xml_publisher_apis.process_kff_combination_1('company_name', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'DESCRIPTION'),fnd_flex_xml_publisher_apis.process_kff_combination_1('disp_company', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') order by --2,1 LEDGER_NAME_H,COMPANY,CLASS,SUB_CLASS,GROUPS,ACCOUNT,BEGIN_DR,BEGIN_CR,PER_DR,PER_CR,END_DR, END_CR,NET |
Parameters 9
| Parameter | Type | List of values SQL |
|---|---|---|
| Ledger/Ledger Set | LOV Oracle | |
| Ledger Currency | LOV Oracle | |
| Currency Type | LOV Oracle | |
| Entered Currency | LOV Oracle | |
| Period | LOV Oracle | |
| Account From | Char | |
| Account To | Char | |
| Balancing Segment | Char | |
| Precision | LOV Oracle |