/*遍历gws_payment_notice表,根据Wfr_No查询gws_write_off_record
根据gws_write_off_record的source_id查询gws_payment_notice_detail,
如果不存在,则对gws_payment_notice_detail作添加操作,对gws_payment_notice作更新操作*/
create or replace procedure proc_init_paymentNotice as
CURSOR c_paymentNotice is
select * from gws_payment_notice t_pn where t_pn.is_deleted = 'n';
/*付款通知单记录变量*/
r_paymentNotice gws_payment_notice%rowtype;
/*核销记录变量*/
c_wfr gws_write_off_record%rowtype;
/*根据sourceid查询gws_payment_notice_detail记录数*/
i_num number(10) := 0;
begin
OPEN c_paymentNotice;
LOOP
fetch c_paymentNotice
into r_paymentNotice;
exit when c_paymentNotice%notfound;
begin
select *
into c_wfr
from gws_write_off_record t_wfr
where t_wfr.wfr_no = r_paymentNotice.Wfr_No;
/*查询是否已存在gws_payment_notice_detail*/
select count(1)
into i_num
from gws_payment_notice_detail t
where t.source_id = c_wfr.source_id;
/*不存在gws_payment_notice_detail,才作添加,更新操作*/
if i_num = 0 then
/*添加gws_payment_notice_detail表*/
insert into gws_payment_notice_detail
(ID,
GMT_CREATE,
GMT_MODIFIED,
PAYMENT_NOTICE_ID,
SOURCE_ID,
AMOUNT,
UN_EXCHANGE_AMOUNT,
CREATOR,
MODIFIER,
IS_DELETED)
values
(seq_gws_payment_notice_detail.nextval,
sysdate,
sysdate,
r_paymentNotice.ID,
c_wfr.source_id,
r_paymentNotice.Amount,
r_paymentNotice.Un_Exchange_Gain_Loss,
'wb_cj189958',
'wb_cj189958',
'n');
/*更新paymentNotice表*/
update gws_payment_notice
set exchange_date = c_wfr.exchange_date,
exchange_type = c_wfr.exchange_type,
exchange_rate = c_wfr.exchange_rate,
source_type = c_wfr.source_type,
gmt_modified = sysdate,
modifier = 'wb_cj189958'
where ID = r_paymentNotice.ID;
end if;
exception
when no_data_found then
dbms_output.put_line('未查到核销记录,wfr_no='||r_paymentNotice.Wfr_No);
when too_many_rows then
dbms_output.put_line('查到多条核销记录,wfr_no='||r_paymentNotice.Wfr_No);
when others then
dbms_output.put_line('系统异常');
end;
END LOOP;
close c_paymentNotice;
begin
commit; /*提交*/
dbms_output.put_line('存储过程执行成功');
exception
when others then
rollback; /*回滚*/
dbms_output.put_line('存储过程执行失败');
end;
end proc_init_paymentNotice;
根据gws_write_off_record的source_id查询gws_payment_notice_detail,
如果不存在,则对gws_payment_notice_detail作添加操作,对gws_payment_notice作更新操作*/
create or replace procedure proc_init_paymentNotice as
CURSOR c_paymentNotice is
select * from gws_payment_notice t_pn where t_pn.is_deleted = 'n';
/*付款通知单记录变量*/
r_paymentNotice gws_payment_notice%rowtype;
/*核销记录变量*/
c_wfr gws_write_off_record%rowtype;
/*根据sourceid查询gws_payment_notice_detail记录数*/
i_num number(10) := 0;
begin
OPEN c_paymentNotice;
LOOP
fetch c_paymentNotice
into r_paymentNotice;
exit when c_paymentNotice%notfound;
begin
select *
into c_wfr
from gws_write_off_record t_wfr
where t_wfr.wfr_no = r_paymentNotice.Wfr_No;
/*查询是否已存在gws_payment_notice_detail*/
select count(1)
into i_num
from gws_payment_notice_detail t
where t.source_id = c_wfr.source_id;
/*不存在gws_payment_notice_detail,才作添加,更新操作*/
if i_num = 0 then
/*添加gws_payment_notice_detail表*/
insert into gws_payment_notice_detail
(ID,
GMT_CREATE,
GMT_MODIFIED,
PAYMENT_NOTICE_ID,
SOURCE_ID,
AMOUNT,
UN_EXCHANGE_AMOUNT,
CREATOR,
MODIFIER,
IS_DELETED)
values
(seq_gws_payment_notice_detail.nextval,
sysdate,
sysdate,
r_paymentNotice.ID,
c_wfr.source_id,
r_paymentNotice.Amount,
r_paymentNotice.Un_Exchange_Gain_Loss,
'wb_cj189958',
'wb_cj189958',
'n');
/*更新paymentNotice表*/
update gws_payment_notice
set exchange_date = c_wfr.exchange_date,
exchange_type = c_wfr.exchange_type,
exchange_rate = c_wfr.exchange_rate,
source_type = c_wfr.source_type,
gmt_modified = sysdate,
modifier = 'wb_cj189958'
where ID = r_paymentNotice.ID;
end if;
exception
when no_data_found then
dbms_output.put_line('未查到核销记录,wfr_no='||r_paymentNotice.Wfr_No);
when too_many_rows then
dbms_output.put_line('查到多条核销记录,wfr_no='||r_paymentNotice.Wfr_No);
when others then
dbms_output.put_line('系统异常');
end;
END LOOP;
close c_paymentNotice;
begin
commit; /*提交*/
dbms_output.put_line('存储过程执行成功');
exception
when others then
rollback; /*回滚*/
dbms_output.put_line('存储过程执行失败');
end;
end proc_init_paymentNotice;