GL General Ledger - (132 Char)

Description
Categories: BI Publisher, Financials
Application: General Ledger
Source: General Ledger - (132 Char) (XML)
Short Name: GLGENLED_XML
DB package: GL_GLRGNL_XMLP_PKG
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
Parameter Name SQL text Validation
Page Size
 
Number
GL_SRS_CURRENCY_TYPE
 
LOV Oracle
Chart of Accounts ID
 
Number
Data Access Set ID
 
Number
Account To
 
Account From
 
Ending Period
 
LOV Oracle
Starting Period
 
LOV Oracle
Budget or Encumbrance Name
 
LOV Oracle
Balance Type
 
LOV Oracle
Type
 
LOV Oracle
Currency Type
 
LOV Oracle
Ledger Currency
 
LOV Oracle
Ledger/Ledger Set
 
LOV Oracle