GL General Ledger - (180 Char)

SELECT 
       l2.name LEDGER_NAME , 
       l2.ledger_id LEDGER_ID,
       nvl(bal.begin_balance_dr,0) BEGIN_DR,
       nvl(bal.begin_balance_cr,0) BEGIN_CR,
       nvl(bal.period_net_dr,0) PERIOD_DR,
       nvl(bal.period_net_cr,0) PERIOD_CR,
       bal.period_name PERIOD_NAME,
       null BAL_DATA,
       null FLEXDATA, 
       cc.code_combination_id CCID,
       bal.period_num  PERIOD_NUM,
       bal.period_year  PERIOD_YEAR,
       cc.template_id  TEMPLATE_ID, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('bal_field', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_BALANCING', 'N', 'VALUE') BAL_FIELD, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('bal_field', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_BALANCING', 'N', 'VALUE') BAL_FIELD_W_DEP, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('bal_desc', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_BALANCING', 'N', 'FULL_DESCRIPTION') BAL_DESC, 
	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', 'N', 'SECURITY') BAL_SECURE,
	GL_GLRGNL_XMLP_PKG.CP_1_p CP_1, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_field', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'N', 'VALUE') ACCT_FIELD, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_field', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'N', 'VALUE') ACCT_FIELD_W_DEP, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('flexfield', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') FLEXFIELD, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('flexdesc', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'ALL', 'Y', 'DESCRIPTION') FLEXDESC, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('flexfield_pad', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'ALL', 'Y', 'PADDED_VALUE') FLEXFIELD_PAD, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('flex_secure', 'SQLGL', 'GL#', CC.CHART_OF_ACCOUNTS_ID, NULL, CC.CODE_COMBINATION_ID, 'ALL', 'Y', 'SECURITY') FLEX_SECURE,
	GL_GLRGNL_XMLP_PKG.OLD_CCID_p OLD_CCID,
		GL_GLRGNL_XMLP_PKG.new_recordformula(cc.code_combination_id) NEW_RECORD,
	GL_GLRGNL_XMLP_PKG.begin_balformula(nvl ( bal.begin_balance_cr , 0 ), nvl ( bal.begin_balance_dr , 0 )) BEGIN_BAL, 
	GL_GLRGNL_XMLP_PKG.end_balformula(nvl ( bal.begin_balance_cr , 0 ), nvl ( bal.period_net_cr , 0 ), nvl ( bal.begin_balance_dr , 0 ), nvl ( bal.period_net_dr , 0 )) END_BAL,  
	GL_GLRGNL_XMLP_PKG.bad_startformula(:new_record, bal.period_num, bal.period_year, nvl ( bal.begin_balance_dr , 0 ), nvl ( bal.begin_balance_cr , 0 ), nvl ( bal.period_net_dr , 0 ), nvl ( bal.period_net_cr , 0 )) BAD_START, 
	GL_GLRGNL_XMLP_PKG.bad_endformula(nvl ( bal.period_net_dr , 0 ), :TOTAL_DR, nvl ( bal.period_net_cr , 0 ), :TOTAL_CR, cc.template_id) BAD_END,
	GL_GLRGNL_XMLP_PKG.OLD_END_DR_p OLD_END_DR,
	GL_GLRGNL_XMLP_PKG.OLD_END_CR_p OLD_END_CR,
	GL_GLRGNL_XMLP_PKG.LAST_PERIOD_YEAR_p LAST_PERIOD_YEAR
FROM  GL_LEDGERS L,
            GL_LEDGER_SET_ASSIGNMENTS LS,
            GL_LEDGERS L2,
            GL_CODE_COMBINATIONS CC,
            GL_PERIOD_STATUSES PS,
            GL_BALANCES BAL
WHERE l.ledger_id = :P_LEDGER_ID
AND   ls.ledger_set_id(+) =l.ledger_id
AND   l2.ledger_id = nvl(ls.ledger_id, l.ledger_id)
AND   l2.currency_code = :P_LEDGER_CURRENCY
AND   ps.application_id = 101
AND   ps.ledger_id = l2.ledger_id
AND   ps.effective_period_num >= :START_EFF_PERIOD_NUM
AND   ps.effective_period_num <= :END_EFF_PERIOD_NUM
AND   cc.chart_of_accounts_id = :STRUCT_NUM
AND   &WHERE
&WHERE_DAS
AND   bal.code_combination_id = cc.code_combination_id
AND   bal.ledger_id = l2.ledger_id
AND   bal.period_name = ps.period_name
AND   bal.currency_code = decode(:P_CURRENCY_TYPE, 'S','STAT',:P_LEDGER_CURRENCY)
AND   bal.actual_flag = :P_ACTUAL_FLAG
AND   &WHERE_ACTUAL_TYPE
AND   &WHERE_INDEX
AND 
(      (      nvl(bal.begin_balance_dr, 0) - 
                    nvl(bal.begin_balance_cr,0) != 0
        OR nvl(bal.period_net_dr,0) != 0
        OR nvl(bal.period_net_cr,0) != 0)
OR (EXISTS
  (SELECT
                 'has data'
   FROM   gl_je_lines jel, gl_je_headers jeh, gl_je_batches jeb
   WHERE jel.code_combination_id = bal.code_combination_id
   AND      jel.period_name = bal.period_name
   AND      jel.ledger_id = bal.ledger_id
   AND      jel.status = 'P'
   AND    ((nvl(jel.accounted_dr,0) != 0 OR nvl(jel.accounted_cr,0) != 0) OR
                (nvl(jel.accounted_dr,0) = 0 AND nvl(jel.accounted_cr,0) = 0 AND
                 jel.stat_amount is not NULL))
   AND      jeh.je_header_id = jel.je_header_id+0
   AND      jeh.currency_code = bal.currency_code
   AND      jeh.actual_flag = bal.actual_flag
   AND      nvl(jeh.budget_version_id, -1) = nvl(bal.budget_version_id, -1)
   AND      nvl(jeh.encumbrance_type_id, -1) = nvl(bal.encumbrance_type_id, -1)
   AND      jeh.status = 'P'
   AND      jeb.je_batch_id = jeh.je_batch_id + 0
   AND      jeb.status = 'P')))
ORDER BY l2.name, &ORDERBY_BAL, &ORDERBY_ACCT, 
         &ORDERBY_ALL, ps.period_year,
         ps.period_num