|
|
經過研究，該問題終於解決了。
---created:2006/11/4
---purpose: insert a query result into temporary table gl_entry_line_interface
-------------------------------------------------------------------------------
declare
cursor cu is
select -- rownum,
gll.effective_date effective_date,
GLL.JE_HEADER_ID je_header_id,
GLL.JE_LINE_NUM je_line_num,
gll.code_combination_id code_combination_id,
gll.period_name period_name,
gll.accounted_dr accounted_dr,
gll.accounted_cr accounted_cr
from apps.gl_je_lines gll,
apps.gl_je_headers glh,
apps.gl_je_batches glb
where gll.je_header_id=glh.je_header_id
and glh.je_batch_id=glb.je_batch_id
and glh.currency_code='RMB'
and glh.status='P' --- 表示該分錄已經過帳
and glh.set_of_books_id=1001
-- and glh.period_name between
and gll.effective_date BETWEEN to_date('2006/5/1','yyyy/mm/dd') and to_date('2006/6/30','yyyy/mm/dd')
and gll.code_combination_id BETWEEN 1048 AND 1051
-- order by gll.effective_date asc, gll.code_combination_id, period_name;
order by gll.code_combination_id,gll.effective_date;
lr cu%rowtype;
begin
open cu;
loop
fetch cu into lr;
exit when cu%notfound;
insert into gl_entry_line_interface
(effective_date,
je_header_id,
je_line_num ,
code_combination_id ,
period_name,
accounted_dr,
accounted_cr )
values
(lr.effective_date,
lr.je_header_id,
lr.je_line_num,
lr.code_combination_id,
lr.period_name,
lr.accounted_dr,
lr.accounted_cr);
end loop;
close cu;
commit;
end;
------------------------------------------------
select * from gl_entry_line_temp_1
-----------------------------------------------------------
----modified: 2006/11/4
----purpose: insert values of temporary table gl_entry_line_temp_2 into temporay table gl_entry_line_temp_1
----method:
--(1) create temporary table gl_entry_line_temp_1 and temporay table gl_entry_line_temp_2
--(2) cursor cu_3 is control code_combination_id and period_name,
-- cursor cu_2 is control the result queried by paramenter lr_3.code_combination_id and lr_3.period_name.
-- cursor cu_1 is control inserting values of cursor cu_1 into temporary table gl_entry_line_temp_2.
-- (3) creating a package and creating a procedure in the package created.
---key caution:
--(1) Compositor rule of cursor cu_1 is the same as cursor cu_2.
--(2) Values of column 'je_line_balance' of temp table gl_entry_line_temp_2 and values of column 'je_line_balance' of temp table gl_entry_line_temp_1 vary with compositor rule of cursor cu_1 and cursor cu_2.
declare
--i number:=1;
cursor cu_3 is
select distinct code_combination_id,period_name
from gl_entry_line_interface
order by code_combination_id asc,period_name desc;
cursor cu_2(p_code_id number, p_period_code varchar2) is
select effective_date,je_header_id,je_line_num,code_combination_id,period_name,accounted_dr,accounted_cr
from gl_entry_line_interface
where code_combination_id=p_code_id
and period_name=p_period_code
-- where period_name in ('MAY-06','JUN-06')
order by code_combination_id,effective_date;
-- where rownum<=156;
-- rowindex number,
cursor cu_1(p_je_header_id number,
p_line_num number,
p_code_combination_id number,
p_period_name varchar2) is
select --rownum,
--effective_date,
je_header_id,
je_line_num,
code_combination_id,
period_name,
accounted_dr,
accounted_cr
from gl_entry_line_interface
where code_combination_id=p_code_combination_id
and period_name=p_period_name
and je_header_id=p_je_header_id
and je_line_num=p_line_num
order by code_combination_id,effective_date;
-- and rownum<=rowindex;
lr_1 cu_1%rowtype;
lr_2 cu_2%rowtype;
s_dr number:=0;
s_cr number:=0;
-- s_dr_1 number:=0;
-- s_cr_1 number:=0;
p_balance_peirod number;
begin
-- for i in 1..156 loop
--open cu_3;
--loop ---------------------------------------------->3
for lr_3 in cu_3 loop
p_balance_peirod:=gl_loss_income_detail_pkg.gl_main_segment_balance_period
( 1001,
'RMB',
gl_loss_income_detail_pkg.gl_get_previous_period(lr_3.period_name),
lr_3.code_combination_id);
s_dr:=0;
s_cr:=0;
open cu_2(lr_3.code_combination_id,
lr_3.period_name);
loop
fetch cu_2 into lr_2;
exit when cu_2%notfound;
open cu_1(lr_2.je_header_id,
lr_2.je_line_num,
lr_2.code_combination_id,
lr_2.period_name);
loop
fetch cu_1 into lr_1;
exit when cu_1%notfound;
s_dr:=s_dr+nvl(lr_1.accounted_dr,0);
s_cr:=s_cr+nvl(lr_1.accounted_cr,0);
if (lr_1.je_header_id=lr_2.je_header_id and lr_1.je_line_num=lr_2.je_line_num
and lr_1.code_combination_id=lr_2.code_combination_id
and lr_1.period_name=lr_2.period_name) then
-- insert into test_2
-- (a1,
-- b1,
-- c1,
-- d1,
-- e1)
-- values
-- (lr_1.je_header_id,
-- lr_1.je_line_num,
-- gl_loss_income_detail_pkg.gl_main_segment_balance_period
-- ( 1001,
-- 'RMB',
-- gl_loss_income_detail_pkg.gl_get_previous_period(lr_2.period_name),
-- lr_2.code_combination_id)+s_dr-s_cr,
-- 1959116.2+s_dr-s_cr,
-- lr_1.accounted_dr,
-- lr_1.accounted_cr);
--s_dr:=s_dr+nvl(lr_1.accounted_dr,0);
--s_cr:=s_cr+nvl(lr_1.accounted_cr,0);
insert into
gl_entry_line_temp_2
(effective_date,
je_header_id,
je_line_num,
code_combination_id,
period_name,
accounted_dr,
accounted_cr,
je_line_balance)
values
(
lr_2.effective_date,
lr_2.je_header_id,
lr_2.je_line_num,
lr_2.code_combination_id,
lr_2.period_name,
lr_2.accounted_dr,
lr_2.accounted_cr,
-- gl_loss_income_detail_pkg.gl_main_segment_balance_period
-- ( 1001,
-- 'RMB',
-- gl_loss_income_detail_pkg.gl_get_previous_period(lr_1.period_name),
-- lr_1.code_combination_id)+s_dr-s_cr);
p_balance_peirod+s_dr-s_cr);
-- s_dr:=0;
-- s_cr:=0;
end if;
end loop;
close cu_1;
end loop;
close cu_2;
demo_pkg.do_insert_temp;
delete from gl_entry_line_temp_2;
end loop; ----------------------------------------------------->3
-- close cu_3;
commit;
end;
--------------------------------------------------------------------------------------------------
-- testing the result.
--------------------------------------------------------------------------------------------------
select --gll.rowid
gt.effective_date,
glb.name batch_name,
-- GLL.JE_HEADER_ID je_header_id,
-- GLL.JE_LINE_NUM je_line_num,
-- gll.code_combination_id,
fnd_flex_ext.get_segs('SQLGL','GL#', 50268, gll.code_combination_id) CONCAT_SEGMENTS,---會計科目
gll.period_name,
gll.entered_dr,
gll.entered_cr,
gll.accounted_dr accounted_dr,
gll.accounted_cr accounted_cr,
gt.je_line_balance
from apps.gl_je_lines gll,
gl_entry_line_temp_1 gt,
apps.gl_je_headers glh,
apps.gl_je_batches glb
where gll.je_header_id=gt.je_header_id
and gll.je_line_num=gt.je_line_num
-- and gll.effective_date=gt.effective_date
and gll.je_header_id=glh.je_header_id
and glh.je_batch_id=glb.je_batch_id
and glh.currency_code= _currency_code
and glh.status='P'
and glh.set_of_books_id=_set_of_book_id
-- and glh.period_name between
and gll.effective_date between _date_from and _date_to
and gll.code_combination_id BETWEEN _account_id_from AND _account_id_to
order by gt.code_combination_id,gt.effective_date |
|
楼主: tjzhao2005
IP卡
狗仔卡
楼主
显身卡
发表于 2006-11-6 18:50