未过账数据查询

最新推荐文章于 2023-05-18 10:27:23 发布

原创

最新推荐文章于 2023-05-18 10:27:23 发布 · 502 阅读

0 ·

CC 4.0 BY-SA版权

该博客主要涉及未过账数据的查询方法，通过Gl_Balances和Gl_je_Lines表联合查询，筛选特定期间和账户段的未平账记录，并进行余额计算。查询结果包括账户类型、期初余额、期间借贷发生额等关键信息。

–余额

select gb.code_combination_id,
gb.ledger_id,
gb.period_name,
gb.translated_flag,
gb.period_year,
gb.period_num,
gcc.account_type,
gcc.enabled_flag,
gcc.summary_flag,
gb.currency_code,
gcc.segment1,
gcc.segment2,
gcc.segment3,
gcc.segment4,
gcc.segment5,
gcc.segment6,
gcc.segment7,
gcc.segment8,
gcc.segment9,
gb.period_net_dr,
gb.period_net_cr,
gb.begin_balance_dr,
gb.begin_balance_cr,
gb.begin_balance_dr - gb.begin_balance_cr + gb.period_net_dr -
gb.period_net_cr,
sysdate
from apps.Gl_Balances@EBSPROD gb, apps.Gl_Code_Combinations@EBSPROD gcc
where gb.code_combination_id = gcc.code_combination_id
and gb.period_year = ’ || arg_period_year || ’
and gb.period_num = ‘|| v_period_num ||
’
and gb.ledger_id not in
(’’ 2 ‘’, ‘’ 3 ‘’, ‘’ 25 ‘’, ‘’ 104 ‘’, ‘’ 264 ‘’, ‘’ 327 ‘’, ‘’ 369 ‘’)
and gcc.segment1 not in
(’’ 0011 ‘’, ‘’ 0021 ‘’, ‘’ 0023 ‘’, ‘’ 0111 ‘’, ‘’ 0121 ‘’, ‘’ 0131 ‘’, ‘’ 0161 ‘’, ‘’ 0171 ‘’, ‘’ 0200 ‘’, ‘’ 0211 ‘’, ‘’ 0221 ‘’, ‘’ 0231 ‘’)
and abs(gb.begin_balance_dr - gb.begin_balance_cr) +
abs(gb.period_net_dr - gb.period_net_cr) <> 0
and gb.currency_code =
(select gls.currency_code
from apps.gl_ledgers@EBSPROD gls
where gls.ledger_id = gb.ledger_id);

select gjl.code_combination_id,
gjl.ledger_id,
gjl.period_name,
null translated_flag,
‘20’ || substr(gjl.period_name, 5, 6) period_year,
decode(substr(gjl.period_name, 1, 3),
‘JAN’,
1,
‘FEB’,
‘2’,
‘MAR’,
‘3’,
‘APR’,
‘4’,
‘MAY’,
‘5’,
‘JUN’,
‘6’,
‘JUL’,
‘7’,
‘AUG’,
‘8’,
‘SEP’,
‘9’,
‘OCT’,
‘10’,
‘NOV’,
‘11’,
‘DEC’,
‘12’,
‘13’) period_num,
gcc.account_type,
gcc.enabled_flag,
gcc.summary_flag,
gjh.currency_code,
gcc.segment1,
gcc.segment2,
gcc.segment3,
gcc.segment4,
gcc.segment5,
gcc.segme

最低0.47元/天解锁文章