/*
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 ;
-- 调用方法
695
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
