Scribd
Upload
9 views5 pages

GL Account Balance SQL Queries

The first document contains two SQL queries. The first query sums the beginning and ending balances for general ledger account code combinations for a given period. The second query returns bank account and branch information by joining various tables, including the bank account name and number, bank name and branch, and the associated general ledger code combination. The second document contains multiple SQL queries related to journal entries. The first query returns details of journal entry lines like the source, category, period, batch, and flexdata segment values. The subsequent queries filter on specific flexdata segment values or sum the debits and credits for a given code combination.

Uploaded by

Khalil Shafeek
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
9 views5 pages

GL Account Balance SQL Queries

The first document contains two SQL queries. The first query sums the beginning and ending balances for general ledger account code combinations for a given period. The second query returns bank account and branch information by joining various tables, including the bank account name and number, bank name and branch, and the associated general ledger code combination. The second document contains multiple SQL queries related to journal entries. The first query returns details of journal entry lines like the source, category, period, batch, and flexdata segment values. The subsequent queries filter on specific flexdata segment values or sum the debits and credits for a given code combination.

Uploaded by

Khalil Shafeek
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

GL Account Balance Query

SELECT gcc.concatenated_segments Code_combination,

SUM(NVL(gb.begin_balance_dr,0)-NVL(gb.begin_balance_cr,0)) beginning_bal,

SUM(NVL(gb.begin_balance_dr,0)-NVL(gb.begin_balance_cr,0) +

(NVL(gb.period_net_Dr,0) - NVL(gb.period_net_cr,0))) end_bal

FROM gl_balances gb,

gl_code_combinations_kfv gcc

WHERE gb.code_combination_id = gcc.code_combination_id

--AND gcc.CONCATENATED_SEGMENTS = '01-000-1110-0000-000' -- Enter GL Account

--AND gb.ledger_id = 1 -- Enter the Ledger

AND gb.Actual_flag = 'A'

AND gb.period_name = 'JUN-18' --Enter the Period

AND gb.currency_code = (SELECT currency_code FROM gl_ledgers WHERE ledger_id =


gb.ledger_id)
GROUP BY gcc.concatenated_segments;

Query to find Bank information

SELECT cba.bank_account_name "Bank Account Name",


cba.bank_account_num "Bank Account Number",
cba.multi_currency_allowed_flag "Multi Currency Flag",
cba.zero_amount_allowed "Zero Amount Flag",
cba.account_classification "Account Classification",
bb.bank_name "Bank Name",
bb.bank_branch_type "Bank Branch Type",
bb.bank_branch_name "Bank Branch Name",
bb.bank_branch_number "Bank Branch Number",
bb.eft_swift_code "Swift Code",
-- [Link] "Description",
[Link] "Operating Unit",
gcf.concatenated_segments "GL Code Combination"
FROM ce_bank_accounts cba,
ce_bank_acct_uses_all bau,
cefv_bank_branches bb,
hr_operating_units ou,
gl_code_combinations_kfv gcf
WHERE cba.bank_account_id = bau.bank_account_id
AND cba.bank_branch_id = bb.bank_branch_id
AND ou.organization_id = bau.org_id
AND cba.asset_code_combination_id = gcf.code_combination_id
AND (cba.end_date IS NULL OR cba.end_date > TRUNC(SYSDATE))
-- ORDER BY TO_NUMBER(cba.bank_account_num);
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,
[Link] BATCH_NAME ,
[Link] 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 ,
[Link] 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 '13' AND '13'
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 [Link]
|| '' = '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, [Link], [Link]
select sum(debits)-sum(credits) from (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,
[Link] BATCH_NAME ,
[Link] 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 ,
[Link] 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 '13' AND '13'
AND CC.SEGMENT4 = '13211500'-- AND '13211400'
-- 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 [Link]
|| '' = '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, [Link], [Link])
where period_name = 'JUN-18'

You might also like