LEDGER BY SOURCE AND CATEGORY
/* Formatted on 3/11/2022 11:40:34 AM (QP5 v5.365) */
SELECT gl.name "LEDGER_NAME",
--b.name "JOURNAL_BATCH_NAME",
-- h.name "JOURNAL_NAME",
s.user_je_source_name "JOURNAL_SOURCE",
c.user_je_category_name "JOURNAL_CATEGORY",
h.period_name,
h.currency_code,
-- l.je_line_num "JOURNAL_LINE_NUM",
gcc.concatenated_segments "ACCOUNT_CODE_COMBINATION",
gcc.segment1 "LEGAL_ENTITY",
gcc.segment2 "BRANCH",
gcc.segment3 "ACCOUNT",
gcc.segment4 "INTERCOMPANY",
gcc.segment5 "FUTURE1",
gcc.segment6 "FUTURE2",
gcc.segment7 "FUTURE3",
-- l.entered_dr,
-- l.entered_cr,
-- l.accounted_dr,
-- l.accounted_cr,
SUM (NVL (entered_dr, 0) - NVL (entered_cr, 0)) net_entered,
SUM (NVL (accounted_dr, 0) - NVL (accounted_cr, 0)) net_accounted
FROM apps.gl_je_headers h,
apps.gl_je_lines l,
apps.gl_je_categories c,
apps.gl_je_sources s,
apps.gl_code_combinations_kfv gcc,
apps.gl_je_batches b,
apps.gl_ledgers gl
WHERE l.je_header_id = h.je_header_id
AND b.je_batch_id = h.je_batch_id
AND h.je_category = c.je_category_name
AND h.je_source = s.je_source_name
AND gcc.code_combination_id = l.code_combination_id
AND h.ledger_id = gl.ledger_id
AND h.ledger_id = l.ledger_id
AND h.period_name = 'FEB-22'
AND gl.name = 'CAD WESCO' --IN ('USD WESCO')
GROUP BY gl.name,
-- h.name,
-- b.name,
h.period_name,
-- l.je_line_num,
gcc.concatenated_segments,
h.currency_code,
gcc.segment1,
gcc.segment2,
gcc.segment3,
gcc.segment4,
gcc.segment5,
gcc.segment6,
gcc.segment7,
c.user_je_category_name,
s.user_je_source_name
-- l.entered_dr,
-- l.entered_cr,
-- l.accounted_dr,
-- l.accounted_cr
ORDER BY gcc.concatenated_segments
-- b.name,
-- h.name,
-- l.je_line_num;
GL Balances
SELECT ledger.name
"LEDGER_NAME",
bal.currency_code,
bal.actual_flag,
bal.period_name,
bal.period_year,
cc.code_combination_id,
cc.segment1
|| '-'
|| cc.segment2
|| '-'
|| cc.segment3
|| '-'
|| cc.segment4
|| '-'
|| cc.segment5
|| '-'
|| cc.segment6
|| '-'
|| cc.segment7
"DISTRIBUTION",
cc.segment1,
cc.segment2,
cc.segment3,
cc.segment4,
cc.segment5,
cc.segment6,
cc.segment7,
(NVL (bal.begin_balance_dr, 0) - NVL (bal.begin_balance_cr, 0))
"OPEN BAL",
NVL (bal.period_net_dr, 0)
"DEBIT",
NVL (bal.period_net_cr, 0)
"CREDIT",
(NVL (bal.period_net_dr, 0) - NVL (bal.period_net_cr, 0))
"NET MOVEMENT",
((NVL (bal.period_net_dr, 0) + NVL (bal.begin_balance_dr, 0)))
- (NVL (bal.period_net_cr, 0) + NVL (bal.begin_balance_cr, 0))
"CLOSE BAL",
cc.summary_flag
FROM apps.gl_balances bal,
apps.gl_ledgers ledger,
apps.gl_code_combinations_kfv cc,
apps.gl_periods calendar
WHERE bal.code_combination_id = cc.code_combination_id
AND bal.ledger_id = ledger.ledger_id
AND bal.translated_flag IS NULL --functional balance only
AND bal.period_name = calendar.period_name
--- control by period name
-- and bal.period_name = 'Dec-2016'
---control by dates - useful for secondary ledgers which have different calendar than primry
-- AND TO_DATE ('01-SEP-2020', 'DD-MON-YYYY') <= calendar.start_Date
-- AND TO_DATE ('30-SEP-2020', 'DD-MON-YYYY') >= calendar.end_Date
AND bal.period_name = 'SEP-20'
AND ledger.accounted_period_type = calendar.period_type
AND ledger.period_set_name = calendar.period_set_name
AND ledger.name = 'CAD WESCO'
AND bal.actual_flag = 'A'
AND cc.summary_flag = 'N';
-------------------------------------
Omkar Script - GL Balances
/* Formatted on 8/17/2021 8:49:59 AM (QP5 v5.365) */
SELECT gld.NAME,
gb.actual_flag,
gb.period_name,
gb.PERIOD_YEAR,
gcc.code_combination_id,
gcc.segment1
|| '-'
|| gcc.segment2
|| '-'
|| gcc.segment3
|| '-'
|| gcc.segment4
|| '-'
|| gcc.segment5
|| '-'
|| gcc.segment6
|| '-'
|| gcc.segment7
"DISTRIBUTION",
gcc.segment1,
gcc.segment2,
gcc.segment3,
gcc.segment4,
gcc.segment5,
gcc.segment6,
gcc.segment7,
SUM (NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0))
"OPEN BAL",
NVL (gb.period_net_dr, 0)
"DEBIT",
NVL (gb.period_net_cr, 0)
"CREDIT",
SUM (NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0))
"NET MOVEMENT",
SUM ((NVL (gb.period_net_dr, 0) + NVL (gb.begin_balance_dr, 0)))
- SUM (NVL (gb.period_net_cr, 0) + NVL (gb.begin_balance_cr, 0))
"CLOSE BAL"
--gb.translated_flag
--gb.template_id
FROM gl_balances gb, gl_code_combinations gcc, gl_ledgers gld
WHERE gcc.code_combination_id = gb.code_combination_id
AND gb.actual_flag = 'A'
-- AND gb.template_id IS NULL
AND gb.ledger_id = gld.ledger_id
AND gb.period_name = 'JUN-21'
AND gLD.name = 'USD WESCO'
GROUP BY gld.NAME,
gb.actual_flag,
gb.period_name,
gb.PERIOD_YEAR,
gcc.code_combination_id,
gcc.segment1
|| '-'
|| gcc.segment2
|| '-'
|| gcc.segment3
|| '-'
|| gcc.segment4
|| '-'
|| gcc.segment5
|| '-'
|| gcc.segment6
|| '-'
|| gcc.segment7,
gcc.segment1,
gcc.segment2,
gcc.segment3,
gcc.segment4,
gcc.segment5,
gcc.segment6,
gcc.segment7,
NVL (gb.period_net_dr, 0),
NVL (gb.period_net_cr, 0)
--gb.translated_flag,
-- gb.template_id
HAVING SUM ((NVL (gb.period_net_dr, 0) + NVL (gb.begin_balance_dr, 0)))
- SUM (NVL (gb.period_net_cr, 0) + NVL (gb.begin_balance_cr, 0)) <>
0;
Journal Reversal Query for the Given Period
SELECT (SELECT NAME
FROM apps.gl_ledgers
WHERE ledger_id = jrnl.ledger_id AND name ! = 'USD Consolidated')
ledger,
(SELECT name
FROM apps.gl_je_batches
WHERE je_batch_id = reversed_jrnl.je_batch_id)
batch_name,
MAX (reversed_jrnl.name)
journal_name, --reversed_journal_name
MAX (src.user_je_source_name)
journal_source,
MAX (cat.user_je_category_name)
journal_category,
--max(reversed_jrnl.accrual_rev_period_name) period_name,
MAX (reversed_jrnl.period_name)
period_name,
MAX (reversed_jrnl.accrual_rev_period_name)
reversal_period_name,
MAX (reversed_jrnl.currency_code)
currency_code,
lines.je_line_num
journal_line_num,
MAX (gcc.concatenated_segments)
account_code_combination,
MAX (gcc.segment1)
legal_entity,
MAX (gcc.segment2)
"BRANCH",
MAX (gcc.segment3)
"ACCOUNT",
MAX (gcc.segment4)
intercompany,
MAX (gcc.segment5)
future1,
MAX (gcc.segment6)
future2,
MAX (gcc.segment7)
future3,
MAX (lines.entered_dr)
entered_dr,
MAX (lines.entered_cr)
entered_cr,
MAX (lines.accounted_dr)
accounted_dr,
MAX (lines.accounted_cr)
accounted_cr,
SUM (NVL (entered_dr, 0) - NVL (entered_cr, 0))
net_entered,
SUM (NVL (accounted_dr, 0) - NVL (accounted_cr, 0))
net_accounted,
MAX (usr.user_name)
user_name
FROM apps.GL_JE_BATCHES_HEADERS_V jrnl,
apps.fnd_user usr,
gl.gl_je_headers reversed_jrnl,
apps.gl_je_sources src,
apps.gl_je_categories cat,
apps.gl_je_lines lines,
apps.gl_code_combinations_kfv gcc
WHERE --usr.user_name = 'JFLORE'
jrnl.created_by = usr.user_id -- Get only Jorunals created by Joe Flore
AND jrnl.period_name = :p_period -- Get only Journals for the period specified by the user-entered parameter. ie: 'APR-21'
AND reversed_jrnl.je_header_id = jrnl.ACCRUAL_REV_JE_HEADER_ID -- Retrieve the associated ""Reversal Journal"" if one exists
--and reversed_jrnl.accrual_rev_period_name = :p_period -- for the period being extracted
AND src.je_source_name = reversed_jrnl.je_source
AND cat.je_category_name = reversed_jrnl.je_category
AND lines.je_header_id = reversed_jrnl.je_header_id
AND gcc.code_combination_id = lines.code_combination_id
AND reversed_jrnl.REVERSED_JE_HEADER_ID IS NOT NULL
GROUP BY jrnl.ledger_id,
jrnl.je_batch_id,
reversed_jrnl.je_batch_id,
jrnl.je_header_id,
lines.je_header_id,
lines.je_line_num,
reversed_jrnl.created_by
ORDER BY ledger,
batch_name,
journal_name,
journal_line_num;