账户余额查询SQL（分类帐）
/*BEGIN
MO_GLOBAL.INIT('AR');
END;*/
select je_line_num,
ae_header_id,
doc_sequence_value,
accounting_date,
description,
accounted_dr,
accounted_cr,
entered_dr,
entered_cr,
currency_code,
code_accounts,
sourcedescription,
startdate,
enddate
from (
select distinct xah.ae_header_id as ae_header_id,
xal.ae_line_num as je_line_num,
xah.doc_sequence_value as doc_sequence_value,
xal.accounting_date as accounting_date,
null as description,
xal.accounted_dr as accounted_dr,
xal.accounted_cr as accounted_cr,
xal.entered_dr as entered_dr,
xal.entered_cr as entered_cr,
xal.currency_code as currency_code,
substr(gcc_ori.concatenated_segments,
instr(gcc_ori.concatenated_segments, '-', 1, 2) + 1,
instr(gcc_ori.concatenated_segments, '-', 1, 2)) as code_accounts,
substr(fa_rx_flex_pkg.get_description(101,
'GL#',
gcc_ori.chart_of_accounts_id,
'ALL',
gcc_ori.concatenated_segments),
instr(fa_rx_flex_pkg.get_description(101,
'GL#',
gcc_ori.chart_of_accounts_id,
'ALL',
gcc_ori.concatenated_segments),
'-',
1,
2) + 1,
instr(fa_rx_flex_pkg.get_description(101,
'GL#',
gcc_ori.chart_of_accounts_id,
'ALL',
gcc_ori.concatenated_segments),
'-',
3,
3)) as sourcedescription,
to_char(:begindate, 'yyyy-mm-dd') as startdate,
to_char(:enddate, 'yyyy-mm-dd') as enddate
from xla_transaction_entities xte,
xla_entity_types_tl xett,
xle_entity_profiles le,
xla_events xe,
xla_event_types_tl xent,
xla_ae_headers xah,
xla_ae_lines xal,
xla_lookups xlp,
xla_distribution_links xdl,
gl_code_combinations_kfv gcc_ori
where 1 = 1
and xte.entity_id = xe.entity_id
and xte.application_id = xe.application_id
and xte.legal_entity_id = le.legal_entity_id(+)
and xah.event_id = xe.event_id
and xah.application_id = xe.application_id(+)
and xent.event_type_code = xe.event_type_code
and xent.application_id(+) = xe.application_id
and xent.language = 'ZHS'
and xah.ae_header_id(+) = xal.ae_header_id
and xlp.lookup_type(+) = 'XLA_ACCOUNTING_CLASS'
and xlp.lookup_code(+) = xal.accounting_class_code
and xal.ae_header_id = xdl.ae_header_id
and xal.ae_line_num = xdl.ae_line_num(+)
and xal.application_id = xdl.application_id(+)
and xett.entity_code = xte.entity_code
and xett.application_id = xte.application_id
and xal.code_combination_id = gcc_ori.code_combination_id
and xett.language = 'ZHS'
and substr(gcc_ori.concatenated_segments,
instr(gcc_ori.concatenated_segments, '-', 1, 2) + 1,
instr(gcc_ori.concatenated_segments, '-', 1, 2)) =
nvl(:bank,
substr(gcc_ori.concatenated_segments,
instr(gcc_ori.concatenated_segments, '-', 1, 2) + 1,
instr(gcc_ori.concatenated_segments, '-', 1, 2)))
--and xah.period_name = 'Jul-10'
and to_char(xal.accounting_date, 'yyyy-mm-dd') between
to_char(:begindate, 'yyyy-mm-dd') and
to_char(:enddate, 'yyyy-mm-dd')
union all
select headers.je_header_id as ae_header_id,
lines.je_line_num as je_line_num,
headers.doc_sequence_value as doc_sequence_value,
headers.default_effective_date as accounting_date,
lines.description as description,
lines.accounted_dr as accounted_dr,
lines.accounted_cr as accounted_cr,
lines.entered_dr as entered_dr,
lines.entered_cr as entered_cr,
headers.currency_code as currency_code,
lines.segment3 as code_accounts,
substr(fa_rx_flex_pkg.get_description(101,
'GL#',
gcc_ori.chart_of_accounts_id,
'ALL',
gcc_ori.concatenated_segments),
instr(fa_rx_flex_pkg.get_description(101,
'GL#',
gcc_ori.chart_of_accounts_id,
'ALL',
gcc_ori.concatenated_segments),
'-',
1,
2) + 1,
instr(fa_rx_flex_pkg.get_description(101,
'GL#',
gcc_ori.chart_of_accounts_id,
'ALL',
gcc_ori.concatenated_segments),
'-',
3,
3)) as sourcedescription,
to_char(:begindate, 'yyyy-mm-dd') as startdate,
to_char(:enddate, 'yyyy-mm-dd') as enddate
from gl_je_headers_v headers,
gl_je_lines_v lines,
gl_code_combinations_kfv gcc_ori
where headers.je_header_id = lines.je_header_id
-- and headers.batch_period_name_qry = 'Jul-10'
and headers.je_category = '1'
and lines.segment3 = nvl(:bank, lines.segment3)
and lines.code_combination_id = gcc_ori.code_combination_id
and to_char(headers.default_effective_date, 'yyyy-mm-dd') between
to_char(:begindate, 'yyyy-mm-dd') and
to_char(:enddate, 'yyyy-mm-dd')
)
order by accounting_date,
doc_sequence_value
/*账户余额分三部分，一部分是GL的手工帐，另外是由xla表得到的ap和ar的数据.由三部分的本币借贷方金额计算得出帐户余额；计算公式为：上期余额+借方-贷方=本期余额。
（上期余额由开帐金额得出）*/
MO_GLOBAL.INIT('AR');
END;*/
select je_line_num,
ae_header_id,
doc_sequence_value,
accounting_date,
description,
accounted_dr,
accounted_cr,
entered_dr,
entered_cr,
currency_code,
code_accounts,
sourcedescription,
startdate,
enddate
from (
select distinct xah.ae_header_id as ae_header_id,
xal.ae_line_num as je_line_num,
xah.doc_sequence_value as doc_sequence_value,
xal.accounting_date as accounting_date,
null as description,
xal.accounted_dr as accounted_dr,
xal.accounted_cr as accounted_cr,
xal.entered_dr as entered_dr,
xal.entered_cr as entered_cr,
xal.currency_code as currency_code,
substr(gcc_ori.concatenated_segments,
instr(gcc_ori.concatenated_segments, '-', 1, 2) + 1,
instr(gcc_ori.concatenated_segments, '-', 1, 2)) as code_accounts,
substr(fa_rx_flex_pkg.get_description(101,
'GL#',
gcc_ori.chart_of_accounts_id,
'ALL',
gcc_ori.concatenated_segments),
instr(fa_rx_flex_pkg.get_description(101,
'GL#',
gcc_ori.chart_of_accounts_id,
'ALL',
gcc_ori.concatenated_segments),
'-',
1,
2) + 1,
instr(fa_rx_flex_pkg.get_description(101,
'GL#',
gcc_ori.chart_of_accounts_id,
'ALL',
gcc_ori.concatenated_segments),
'-',
3,
3)) as sourcedescription,
to_char(:begindate, 'yyyy-mm-dd') as startdate,
to_char(:enddate, 'yyyy-mm-dd') as enddate
from xla_transaction_entities xte,
xla_entity_types_tl xett,
xle_entity_profiles le,
xla_events xe,
xla_event_types_tl xent,
xla_ae_headers xah,
xla_ae_lines xal,
xla_lookups xlp,
xla_distribution_links xdl,
gl_code_combinations_kfv gcc_ori
where 1 = 1
and xte.entity_id = xe.entity_id
and xte.application_id = xe.application_id
and xte.legal_entity_id = le.legal_entity_id(+)
and xah.event_id = xe.event_id
and xah.application_id = xe.application_id(+)
and xent.event_type_code = xe.event_type_code
and xent.application_id(+) = xe.application_id
and xent.language = 'ZHS'
and xah.ae_header_id(+) = xal.ae_header_id
and xlp.lookup_type(+) = 'XLA_ACCOUNTING_CLASS'
and xlp.lookup_code(+) = xal.accounting_class_code
and xal.ae_header_id = xdl.ae_header_id
and xal.ae_line_num = xdl.ae_line_num(+)
and xal.application_id = xdl.application_id(+)
and xett.entity_code = xte.entity_code
and xett.application_id = xte.application_id
and xal.code_combination_id = gcc_ori.code_combination_id
and xett.language = 'ZHS'
and substr(gcc_ori.concatenated_segments,
instr(gcc_ori.concatenated_segments, '-', 1, 2) + 1,
instr(gcc_ori.concatenated_segments, '-', 1, 2)) =
nvl(:bank,
substr(gcc_ori.concatenated_segments,
instr(gcc_ori.concatenated_segments, '-', 1, 2) + 1,
instr(gcc_ori.concatenated_segments, '-', 1, 2)))
--and xah.period_name = 'Jul-10'
and to_char(xal.accounting_date, 'yyyy-mm-dd') between
to_char(:begindate, 'yyyy-mm-dd') and
to_char(:enddate, 'yyyy-mm-dd')
union all
select headers.je_header_id as ae_header_id,
lines.je_line_num as je_line_num,
headers.doc_sequence_value as doc_sequence_value,
headers.default_effective_date as accounting_date,
lines.description as description,
lines.accounted_dr as accounted_dr,
lines.accounted_cr as accounted_cr,
lines.entered_dr as entered_dr,
lines.entered_cr as entered_cr,
headers.currency_code as currency_code,
lines.segment3 as code_accounts,
substr(fa_rx_flex_pkg.get_description(101,
'GL#',
gcc_ori.chart_of_accounts_id,
'ALL',
gcc_ori.concatenated_segments),
instr(fa_rx_flex_pkg.get_description(101,
'GL#',
gcc_ori.chart_of_accounts_id,
'ALL',
gcc_ori.concatenated_segments),
'-',
1,
2) + 1,
instr(fa_rx_flex_pkg.get_description(101,
'GL#',
gcc_ori.chart_of_accounts_id,
'ALL',
gcc_ori.concatenated_segments),
'-',
3,
3)) as sourcedescription,
to_char(:begindate, 'yyyy-mm-dd') as startdate,
to_char(:enddate, 'yyyy-mm-dd') as enddate
from gl_je_headers_v headers,
gl_je_lines_v lines,
gl_code_combinations_kfv gcc_ori
where headers.je_header_id = lines.je_header_id
-- and headers.batch_period_name_qry = 'Jul-10'
and headers.je_category = '1'
and lines.segment3 = nvl(:bank, lines.segment3)
and lines.code_combination_id = gcc_ori.code_combination_id
and to_char(headers.default_effective_date, 'yyyy-mm-dd') between
to_char(:begindate, 'yyyy-mm-dd') and
to_char(:enddate, 'yyyy-mm-dd')
)
order by accounting_date,
doc_sequence_value
/*账户余额分三部分，一部分是GL的手工帐，另外是由xla表得到的ap和ar的数据.由三部分的本币借贷方金额计算得出帐户余额；计算公式为：上期余额+借方-贷方=本期余额。
（上期余额由开帐金额得出）*/
--创建临时表
create table BALANCESTEMP
(
je_line_num number,
headerID number(38),
dr number,
cr number,
BALANCE VARCHAR2(4000) not null,
BANKNAME VARCHAR2(4000) not null,
MM DATE not null,
ID NUMBER not null
)
create or replace function gab_func
(
line_num number,
ae_header_id number,
dr number,
cr number,
startdate date,
enddate date,
accounting_date date,
brankacct varchar2
) return char is
pragma autonomous_transaction;
bltablecut number(38);
lstablecut number(38);
bl varchar(4000);
str number;
cf varchar(4000);
x number;
ct number;
jishu number := 0;
c number;
c1 number;
c2 number;
enbl number;
balance number;
begin
/**查询余额表 2010-06-01至用户参数开始日期前的所有数据**/
select count(*)
into bltablecut
from (select distinct xal.accounting_date as accounting_date,
xal.ae_header_id as a,
xah.doc_sequence_value as doc_sequence_value,
xal.accounted_dr as accounted_dr,
xal.accounted_cr as accounted_cr,
substr(gcc_ori.concatenated_segments,
instr(gcc_ori.concatenated_segments,
'-',
1,
2) + 1,
instr(gcc_ori.concatenated_segments,
'-',
1,
2)) as code_accounts
from xla_transaction_entities xte,
xla_entity_types_tl xett,
xle_entity_profiles le,
xla_events xe,
xla_event_types_tl xent,
xla_ae_headers xah,
xla_ae_lines xal,
xla_lookups xlp,
xla_distribution_links xdl,
gl_code_combinations_kfv gcc_ori
where 1 = 1
and xte.entity_id = xe.entity_id
and xte.application_id = xe.application_id
and xte.legal_entity_id = le.legal_entity_id(+)
and xah.event_id = xe.event_id
and xah.application_id = xe.application_id(+)
and xent.event_type_code = xe.event_type_code
and xent.application_id(+) = xe.application_id
and xent.language = 'ZHS'
and xah.ae_header_id(+) = xal.ae_header_id
and xlp.lookup_type(+) = 'XLA_ACCOUNTING_CLASS'
and xlp.lookup_code(+) = xal.accounting_class_code
and xal.ae_header_id = xdl.ae_header_id
and xal.ae_line_num = xdl.ae_line_num(+)
and xal.application_id = xdl.application_id(+)
and xett.entity_code = xte.entity_code
and xett.application_id = xte.application_id
and xal.code_combination_id = gcc_ori.code_combination_id
and xett.language = 'ZHS'
and substr(gcc_ori.concatenated_segments,
instr(gcc_ori.concatenated_segments, '-', 1, 2) + 1,
instr(gcc_ori.concatenated_segments, '-', 1, 2)) =
nvl(brankacct,
substr(gcc_ori.concatenated_segments,
instr(gcc_ori.concatenated_segments, '-', 1, 2) + 1,
instr(gcc_ori.concatenated_segments, '-', 1, 2)))
and to_char(xal.accounting_date, 'yyyy-mm-dd') between
'2010-06-30' and to_char(startdate - 1, 'yyyy-mm-dd')
union all
select headers.default_effective_date as accounting_date,
headers.je_header_id as a,
headers.doc_sequence_value as doc_sequence_value,
lines.accounted_dr as accounted_dr,
lines.accounted_cr as accounted_cr,
lines.segment3
from gl_je_headers_v headers,
gl_je_lines_v lines,
gl_code_combinations_kfv gcc_ori
where headers.je_header_id = lines.je_header_id
and headers.je_category = '1'
and lines.segment3 = nvl(brankacct, lines.segment3)
and lines.code_combination_id = gcc_ori.code_combination_id
and to_char(headers.default_effective_date, 'yyyy-mm-dd') between
'2010-06-30' and to_char(startdate - 1, 'yyyy-mm-dd'))
order by accounting_date,
doc_sequence_value;
/**查询2010-06-01至用户参数开始时间之间的数据*判断是否有余额*/
select count(*)
into lstablecut
from balancestemp b1
where b1.bankname = brankacct
and b1.mm between to_date('2010-6-30', 'yyyy-mm-dd') and
to_date(to_char(startdate - 1, 'yyyy-mm-dd'), 'yyyy-mm-dd');
if to_char(startdate - 1, 'yyyy-mm-dd') = '2010-06-30' then
--判断用户参数开始日期是否为开帐日期
lstablecut := lstablecut;
end if;
if to_char(startdate - 1, 'yyyy-mm-dd') <> '2010-06-30' then
lstablecut := lstablecut - 1;
end if;
select count(*)
into jishu
from balancestemp b
where mm =
to_date(to_char(accounting_date, 'yyyy-mm-dd'), 'yyyy-mm-dd') --判断是否已经存在
and b.bankname = brankacct
and b.headerid = ae_header_id
and b.je_line_num = line_num;
dbms_output.put_line(lstablecut || '：LStableCUTLStableCUT');
dbms_output.put_line(bltablecut || '：BLtableCUTBLtableCUT');
if lstablecut = bltablecut then
--如果临时表科目总记录数与余额表记录数相等;则直接进行余额递减操作
dbms_output.put_line(jishu || '：jishu');
if jishu <= 0 then
select max(id)
into x
from balancestemp
where mm <=
to_date(to_char(enddate, 'yyyy-mm-dd'), 'yyyy-mm-dd')
and bankname = brankacct;
select bt.balance into bl from balancestemp bt where bt.id = x;
str := to_number(bl);
cf := to_char(str + nvl(dr, 0) - nvl(cr, 0));
select max(id) into c from balancestemp;
c1 := c + 1;
insert into balancestemp
(je_line_num, headerid, dr, cr, balance, mm, bankname, id)
values
(line_num,
ae_header_id,
dr,
cr,
cf,
accounting_date,
brankacct,
c1);
commit;
return cf;
end if;
end if;
if lstablecut <> bltablecut then
--如果临时表科目总记录数与余额表记录数 不相等; 则将用户参数开始日期前的所有数据初始化
if jishu <= 0 then
enbl := startbalance(startdate, brankacct);
select max(id)
into x
from balancestemp
where mm <=
to_date(to_char(enddate, 'yyyy-mm-dd'), 'yyyy-mm-dd')
and bankname = brankacct;
select bt.balance into bl from balancestemp bt where bt.id = x;
str := to_number(bl);
cf := to_char(str + nvl(dr, 0) - nvl(cr, 0));
select max(id) into c from balancestemp;
c1 := c + 1;
insert into balancestemp
(je_line_num, headerid, dr, cr, balance, mm, bankname, id)
values
(line_num,
ae_header_id,
dr,
cr,
cf,
accounting_date,
brankacct,
c1);
commit;
return cf;
end if;
end if;
return cf;
end;
create or replace function startbalance
(
startdate date,
brankacct varchar2
) return char is
pragma autonomous_transaction;
bl varchar(4000);
x number;
str number;
cf varchar(4000);
c number;
c1 number;
c2 number;
cut number;
enbl number;
begin
declare
cursor cc is
select ae_header_id,
doc_sequence_value,
je_line_num,
accounting_date,
accounted_dr,
accounted_cr,
entered_dr,
entered_cr,
code_accounts
from (select distinct xal.accounting_date as accounting_date,
xah.doc_sequence_value as doc_sequence_value,
xal.ae_line_num as je_line_num,
xal.ae_header_id as ae_header_id,
xal.accounted_dr as accounted_dr,
xal.accounted_cr as accounted_cr,
xal.entered_dr as entered_dr,
xal.entered_cr as entered_cr,
substr(gcc_ori.concatenated_segments,
instr(gcc_ori.concatenated_segments,
'-',
1,
2) + 1,
instr(gcc_ori.concatenated_segments,
'-',
1,
2)) as code_accounts
from xla_transaction_entities xte,
xla_entity_types_tl xett,
xle_entity_profiles le,
xla_events xe,
xla_event_types_tl xent,
xla_ae_headers xah,
xla_ae_lines xal,
xla_lookups xlp,
xla_distribution_links xdl,
gl_code_combinations_kfv gcc_ori
where 1 = 1
and xte.entity_id = xe.entity_id
and xte.application_id = xe.application_id
and xte.legal_entity_id = le.legal_entity_id(+)
and xah.event_id = xe.event_id
and xah.application_id = xe.application_id(+)
and xent.event_type_code = xe.event_type_code
and xent.application_id(+) = xe.application_id
and xent.language = 'ZHS'
and xah.ae_header_id(+) = xal.ae_header_id
and xlp.lookup_type(+) = 'XLA_ACCOUNTING_CLASS'
and xlp.lookup_code(+) = xal.accounting_class_code
and xal.ae_header_id = xdl.ae_header_id
and xal.ae_line_num = xdl.ae_line_num(+)
and xal.application_id = xdl.application_id(+)
and xett.entity_code = xte.entity_code
and xett.application_id = xte.application_id
and xal.code_combination_id =
gcc_ori.code_combination_id
and xett.language = 'ZHS'
and substr(gcc_ori.concatenated_segments,
instr(gcc_ori.concatenated_segments,
'-',
1,
2) + 1,
instr(gcc_ori.concatenated_segments,
'-',
1,
2)) =
nvl(brankacct,
substr(gcc_ori.concatenated_segments,
instr(gcc_ori.concatenated_segments,
'-',
1,
2) + 1,
instr(gcc_ori.concatenated_segments,
'-',
1,
2)))
and to_char(xal.accounting_date, 'yyyy-mm-dd') between
'2010-06-30' and
to_char(startdate - 1, 'yyyy-mm-dd')
union all
select headers.default_effective_date as accounting_date,
headers.doc_sequence_value as doc_sequence_value,
lines.je_line_num as je_line_num,
headers.je_header_id as ae_header_id,
lines.accounted_dr as accounted_dr,
lines.accounted_cr as accounted_cr,
lines.entered_dr as entered_dr,
lines.entered_cr as entered_cr,
lines.segment3
from gl_je_headers_v headers,
gl_je_lines_v lines,
gl_code_combinations_kfv gcc_ori
where headers.je_header_id = lines.je_header_id
and headers.je_category = '1'
and lines.segment3 = nvl(brankacct, lines.segment3)
and lines.code_combination_id =
gcc_ori.code_combination_id
and to_char(headers.default_effective_date,
'yyyy-mm-dd') between '2010-06-30' and
to_char(startdate - 1, 'yyyy-mm-dd'))
order by accounting_date,
doc_sequence_value;
begin
for i in cc
loop
select max(id)
into x
from balancestemp
where mm <
to_date(to_char(startdate, 'yyyy-mm-dd'), 'yyyy-mm-dd')
and bankname = brankacct;
select bt.balance into bl from balancestemp bt where bt.id = x;
str := to_number(bl);
select count(*)
into cut
from balancestemp
where mm = to_date(to_char(i.accounting_date, 'yyyy-mm-dd'),
'yyyy-mm-dd')
and bankname = brankacct
and je_line_num = i.je_line_num
and headerid = i.ae_header_id;
if cut <= 0 then
cf := to_char(str + nvl(i.accounted_dr, 0) -
nvl(i.accounted_cr, 0));
select max(id) into c from balancestemp;
c1 := c + 1;
insert into balancestemp
(je_line_num,
headerid,
dr,
cr,
balance,
mm,
bankname,
id)
values
(i.je_line_num,
i.ae_header_id,
i.accounted_dr,
i.accounted_cr,
cf,
i.accounting_date,
brankacct,
c1);
commit;
end if;
end loop;
return cf;
end;
end;
--调用方法
成长
/ | \
学习 总结 分享
QQ交流群：122230156
