GL Trial Balance - Expanded

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Expanded Trial Balance
Application: General Ledger
Source: Trial Balance - Expanded (XML)
Short Name: GLYRLBGE_XML
DB package: GL_GLYRLBGE_XMLP_PKG
Run GL Trial Balance - Expanded and other Oracle EBS reports with Blitz Report™ on our demo environment
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(