同事没有注意把gl_balances表中的数据删除了,然后对日记账进行了过账,导致镜像被覆盖,数据无法恢复,为了防止后期出错,就需要手动恢复被删除的数据。
下面是个人写的代码,还有很多需要优化的地方,但是鉴于还有很多工作量就先往后放放吧,哈哈哈。
declare
-- --4,7,10月 季度发生额为当月发生额
-- Local variables here
--create table cux_test as select * from gl_balances;
l_b_period_name varchar2(20) := '2018-10';
l_period_name varchar2(20) := '2018-11';
begin
-- Test statements here
--更新当月生成了日记账的内容
insert into cux_test
(ledger_id,
code_combination_id,
currency_code,
period_name,
actual_flag,
last_update_date,
last_updated_by,
period_type,
period_year,
period_num,
period_net_dr,
period_net_cr,
quarter_to_date_dr,
quarter_to_date_cr,
project_to_date_dr,
project_to_date_cr,
begin_balance_dr,
begin_balance_cr,
period_net_dr_beq,
period_net_cr_beq,
begin_balance_dr_beq,
begin_balance_cr_beq,
quarter_to_date_dr_beq,
quarter_to_date_cr_beq,
project_to_date_dr_beq,
project_to_date_cr_beq)
select l.ledger_id,
l.code_combination_id,
l.currency_code,
l.period_name,
l.actual_flag,
sysdate,
fnd_global.user_id,
'41',
to_number(substr(l_period_name, 1, 4)),
to_number(substr(l_period_name, 6, 2)),
nvl(l.entered_dr, 0), --借方发生(原)
nvl(l.entered_cr, 0), --贷方发生(原)
nvl(gb.quarter_to_date_dr, 0) + nvl(l.entered_dr, 0),
nvl(gb.quarter_to_date_cr, 0) + nvl(l.entered_cr, 0),
/* nvl(l.entered_dr, 0), --季初贷方(当月发生额)
nvl(l.entered_cr, 0), --季初借方(当月发生额) */
nvl(gb.project_to_date_dr, 0) + nvl(l.entered_cr, 0),
nvl(gb.project_to_date_cr, 0) + nvl(l.entered_cr, 0),
nvl(gb.period_net_dr, 0) + nvl(gb.begin_balance_dr, 0), --三月份期初借方(原)
nvl(gb.period_net_cr, 0) + nvl(gb.begin_balance_cr, 0), --期初贷方(原)
nvl(l.accounted_dr, 0), --借方(本)
nvl(l.accounted_cr, 0), --贷方(本)
nvl(gb.period_net_dr_beq, 0) + nvl(gb.begin_balance_dr_beq, 0), --期初借方(本)
nvl(gb.period_net_cr_beq, 0) + nvl(gb.begin_balance_cr_beq, 0), --期初贷方(本)
nvl(gb.quarter_to_date_dr_beq, 0) + nvl(l.accounted_dr, 0), --季度借方
nvl(gb.quarter_to_date_cr_beq, 0) + nvl(l.accounted_cr, 0), --季度贷方
/* nvl(l.accounted_dr, 0), --季初贷方(当月发生额)
nvl(l.accounted_cr, 0), --季初借方(当月发生额)*/
nvl(gb.project_to_date_dr_beq, 0) + nvl(l.accounted_dr, 0), --项目借方
nvl(gb.project_to_date_cr_beq, 0) + nvl(l.accounted_cr, 0) --项目贷方
from (select jl.ledger_id,
jl.code_combination_id,
h.currency_code,
jl.period_name,
h.actual_flag,
sum(nvl(jl.entered_dr, 0)) entered_dr,
sum(nvl(jl.entered_cr, 0)) entered_cr,
sum(nvl(jl.accounted_dr, 0)) accounted_dr,
sum(nvl(jl.accounted_cr, 0)) accounted_cr
from gl_je_lines jl, gl_je_headers h
where jl.je_header_id = h.je_header_id
and jl.period_name = l_period_name
and jl.status = 'P'
and jl.ledger_id = 2082
and h.actual_flag = 'A'
group by jl.ledger_id,
jl.code_combination_id,
h.currency_code,
jl.period_name,
h.actual_flag) l,
(select *
from gl_balances b
where b.period_name = l_b_period_name) gb
where l.code_combination_id = gb.code_combination_id(+)
and l.currency_code = gb.currency_code(+);
--更新当月未生成日记账的内容
insert into cux_test
(ledger_id,
code_combination_id,
currency_code,
period_name,
actual_flag,
last_update_date,
last_updated_by,
period_type,
period_year,
period_num,
period_net_dr,
period_net_cr,
quarter_to_date_dr,
quarter_to_date_cr,
project_to_date_dr,
project_to_date_cr,
begin_balance_dr,
begin_balance_cr,
period_net_dr_beq,
period_net_cr_beq,
begin_balance_dr_beq,
begin_balance_cr_beq,
quarter_to_date_dr_beq,
quarter_to_date_cr_beq,
project_to_date_dr_beq,
project_to_date_cr_beq)
select ledger_id,
code_combination_id,
currency_code,
l_period_name,
actual_flag,
sysdate,
fnd_global.user_id,
period_type,
period_year,
period_num + 1,
0, --借方发生(原)
0, --贷方发生(原)
quarter_to_date_dr,
quarter_to_date_cr,
/* 0,
0,*/
project_to_date_dr,
project_to_date_cr,
nvl(period_net_dr, 0) + nvl(begin_balance_dr, 0), --期初借方(原)
nvl(period_net_cr, 0) + nvl(begin_balance_cr, 0), --期初贷方(原)
0, --借方(本)
0, --贷方(本)
nvl(period_net_dr_beq, 0) + nvl(begin_balance_dr_beq, 0), --期初借方(本)
nvl(period_net_cr_beq, 0) + nvl(begin_balance_cr_beq, 0), --期初贷方(本)
quarter_to_date_dr_beq, --季度借方
quarter_to_date_cr_beq, --季度贷方
/* 0,
0,*/
project_to_date_dr_beq, --项目借方
project_to_date_cr_beq --项目贷方
from (select *
from cux_test b
where b.period_name = l_b_period_name
and b.ledger_id = 2082) gb1
where gb1.code_combination_id not in
(select gb.code_combination_id
from cux_test gb, cux_test c
where gb.code_combination_id = c.code_combination_id
and gb.period_name = l_b_period_name
and c.period_name = l_period_name);
commit;
end;