Example:
SELECT
/*+ ORDERED USE_NL(ls l2 bal) INDEX(per GL_PERIOD_STATUSES_U4)
INDEX(l GL_LEDGERS_U2) INDEX(ls GL_LEDGER_SET_ASSIGNMENTS_N1)
INDEX(l2 GL_LEDGERS_U2) INDEX(bal GL_BALANCES_N1) */
l2.name LEDGER_NAME,
l2.ledger_id LEDGER_ID ,
MAX(bal.period_name) PERIOD_NAME,
SUM(NVL(bal.begin_balance_dr,0)) BEGIN_DR ,
SUM(NVL(bal.begin_balance_cr,0)) BEGIN_CR ,
SUM(NVL(bal.begin_balance_dr,0) + NVL(bal.period_net_dr,0)) END_DR ,
SUM(NVL(bal.begin_balance_cr,0) + NVL(bal.period_net_cr,0)) END_CR
FROM gl_ledgers l ,
gl_ledger_set_assignments ls,
gl_ledgers l2 ,
gl_period_statuses per ,
gl_code_combinations cc ,
gl_balances bal
WHERE CC.SEGMENT1 BETWEEN '01' AND '01'
AND CC.SEGMENT2 BETWEEN '000' AND '000'
AND CC.SEGMENT3 BETWEEN '1110' AND '1110'
AND CC.SEGMENT4 BETWEEN '0000' AND '00001'
AND CC.SEGMENT5 BETWEEN '000' AND '000'
-- Add segments as needed and modify segment values --
AND cc.chart_of_accounts_id = &chart_of_accounts_id
AND l.ledger_id = &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 = '¤cy_code'
AND per.application_id = 101
AND per.ledger_id = l2.ledger_id
-- For the period range use per.effective_period_num --
AND per.effective_period_num BETWEEN '&START_EFFECTIVE_PERIOD_NUM' AND '&END_EFFECTIVE_PERIOD_NUM'
AND bal.code_combination_id = cc.code_combination_id
-- Specify period name for specifc period --
AND bal.period_name = per.period_name
AND bal.actual_flag = '&actual_flag'
AND bal.currency_code = '¤cy_code'
AND bal.ledger_id = l2.ledger_id
AND bal.template_id IS NULL
GROUP BY l2.name ,
l2.ledger_id ,
per.period_year,
per.period_num
ORDER BY l2.name ,
per.period_year,
per.period_num ;
Example:
SELECT
/*+ ORDERED
USE_NL(jel jeh jeb cat src)
INDEX(jel GL_JE_LINES_N1)
INDEX(jeh GL_JE_HEADERS_U1)
INDEX(jeb GL_JE_BATCHES_U1)
INDEX(cat GL_JE_CATEGORIES_TL_U1)
INDEX(src GL_JE_SOURCES_TL_U1) */
NVL(src.user_je_source_name, '**********') SOURCE ,
NVL(cat.user_je_category_name, '**********') CATEGORY ,
jel.period_name PERIOD_NAME,
jeb.name BATCH_NAME ,
jeh.name HEADER_NAME,
(CC.SEGMENT1
|| '-'
|| SEGMENT2
|| '-'
|| SEGMENT3
|| '-'
|| SEGMENT4
|| '-'
|| SEGMENT5
|| '-'
|| SEGMENT6
|| '-'
|| SEGMENT7
|| '-'
|| SEGMENT8
|| '-'
|| SEGMENT9
|| '-'
|| SEGMENT10
|| '-'
|| SEGMENT11
|| '-'
|| SEGMENT12
|| '-'
|| SEGMENT13
|| '-'
|| SEGMENT14
|| '-'
|| SEGMENT15
|| '-'
|| SEGMENT16
|| '-'
|| SEGMENT17
|| '-'
|| SEGMENT18
|| '-'
|| SEGMENT19
|| '-'
|| SEGMENT20
|| '-'
|| SEGMENT21
|| '-'
|| SEGMENT22
|| '-'
|| SEGMENT23
|| '-'
|| SEGMENT24
|| '-'
|| SEGMENT25
|| '-'
|| SEGMENT26
|| '-'
|| SEGMENT27
|| '-'
|| SEGMENT28
|| '-'
|| SEGMENT29
|| '-'
|| SEGMENT30) FLEXDATA ,
jel.description DESCRIPTION,
jeh.external_reference REFERENCE ,
jel.accounted_cr DEBITS ,
jel.accounted_dr CREDITS ,
jel.ledger_id LEDGER_ID
FROM gl_code_combinations cc,
gl_je_lines jel ,
gl_je_headers jeh ,
gl_je_batches jeb ,
gl_je_categories cat ,
gl_je_sources src
WHERE CC.SEGMENT1 BETWEEN '01' AND '01'
AND CC.SEGMENT2 BETWEEN '000' AND '000'
AND CC.SEGMENT3 BETWEEN '1110' AND '1110'
AND CC.SEGMENT4 BETWEEN '0000' AND '00001'
AND CC.SEGMENT5 BETWEEN '000' AND '000'
-- Add segments as needed and modify segment values --
AND cc.CHART_OF_ACCOUNTS_ID = &CHART_OF_ACCOUNTS_ID
AND jel.code_combination_id = cc.code_combination_id
AND jel.status
|| '' = 'P'
AND
(
jel.accounted_cr != 0
OR jel.accounted_dr != 0
)
AND jeh.je_header_id = jel.je_header_id
AND jeh.actual_flag = '&actual_flag'
AND jeh.currency_code != 'STAT'
AND jeb.je_batch_id = jeh.je_batch_id
AND jeb.average_journal_flag = 'N'
AND src.je_source_name = jeh.je_source
AND cat.je_category_name = jeh.je_category
ORDER BY cc.segment11, cc.segment12, cc.segment13, cc.segment14, cc.segment1, cc.segment2, cc.segment3, cc.segment4, cc.segment5, cc.segment6, cc.segment7, cc.segment8,
cc.segment9, cc.segment10, cc.segment11, cc.segment12, cc.segment13, cc.segment14, cc.segment15, cc.segment16, cc.segment17, cc.segment18, cc.segment19, cc.segment20,
cc.segment21, cc.segment22, cc.segment23, cc.segment24, cc.segment25, cc.segment26, cc.segment27, cc.segment28, cc.segment29, cc.segment30, src.user_je_source_name,
cat.user_je_category_name, jeb.name, jeh.name
No comments:
Post a Comment