GL General Ledger - (132 Char)
Description
Categories: BI Publisher
Imported from BI Publisher
Description: General Ledger
Application: General Ledger
Source: General Ledger - (132 Char) (XML)
Short Name: GLGENLED_XML
DB package: GL_GLRGNL_XMLP_PKG
Description: General Ledger
Application: General Ledger
Source: General Ledger - (132 Char) (XML)
Short Name: GLGENLED_XML
DB package: GL_GLRGNL_XMLP_PKG
Run
GL General Ledger - (132 Char) and other Oracle EBS reports with Blitz Report™ on our demo environment
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 | |
---|---|---|---|
Ledger/Ledger Set |
|
LOV Oracle | |
Ledger Currency |
|
LOV Oracle | |
Currency Type |
|
LOV Oracle | |
Type |
|
LOV Oracle | |
Balance Type |
|
LOV Oracle | |
Budget or Encumbrance Name |
|
LOV Oracle | |
Starting Period |
|
LOV Oracle | |
Ending Period |
|
LOV Oracle | |
Account From |
|
Char | |
Account To |
|
Char |