GL Trial Balance - Expanded

Description
Categories: BI Publisher, Financials
Application: General Ledger
Source: Trial Balance - Expanded (XML)
Short Name: GLYRLBGE_XML
DB package: GL_GLYRLBGE_XMLP_PKG
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
Parameter Name SQL text Validation
Chart Of Accounts Id
 
Number
GL_SRS_LEDGER_ID_UNVALIDATED
 
Number
Data Access Set Id
 
Number
Precision
 
LOV Oracle
Balancing Segment
 
Account To
 
Account From
 
Period
 
LOV Oracle
Entered Currency
 
LOV Oracle
Currency Type
 
LOV Oracle
Ledger Currency
 
LOV Oracle
Ledger/Ledger Set
 
LOV Oracle