#1
left join
(select m.accnum,m.overtime ove,m.renegetimes,m.backstatein24,m.data_date from message_base_seg_info_history m
right join(
select accnum,max(data_date) data_date from message_base_seg_info_history group by accnum
) n
on m.accnum = n.accnum and m.data_date = n.data_date
) c
on b.accnum = c.accnum
left join perloannmain_accnum d
on b.accnum = d.accnum
left join perloannmain_bef_accnum i
on b.accnum = i.accnum
left join d_coreaccstatemap e
on d.accountstate = e.accstate
left join m_loanopen f
on b.accnum = f.accnum
left join rpyv_accnum g
on b.accnum = g.accnum
where b.data_from = '2'
group by
b.ACCNUM,
b.LOANBALANCE,
b.HIGHESTBALANCE,
b.REMAINMONTH,
b.BACKDATE,
b.LASTDATE,
b.NOWBACK,
b.FACTBACK,
b.CUROVETIME,
b.CUROVELIMIT,
c.ove,
RENEGETIMES,
d.ACCOUNTSTATE,
BACKSTATEIN24,
b.DATA_FROM,
b.KZ1,
b.COLLECT_DATE,
d.curovelimit,
d.nowback,
d.loanbalance,
e.coreaccstate,
c.data_date,
d.file_date,
f.opendate,
d.bois_maturity_date,
i.curovelimit,
d.bois_last_unpaid_due_date,
d.bois_current_bue_money,
f.back_plan,
f.interval
) i
on (a.accnum = i.accnum)
when matched then
update set
a.LOANBALANCE =i.LOANBALANCE ,
a.HIGHESTBALANCE=i.HIGHESTBALANCE,
a.REMAINMONTH =i.REMAINMONTH ,
a.BACKDATE =i.BACKDATE ,
a.LASTDATE =i.LASTDATE ,
a.NOWBACK =i.NOWBACK ,
a.FACTBACK =i.FACTBACK ,
a.OVERTIME =i.OVERTIME ,
a.CUROVETIME =i.CUROVETIME ,
a.CUROVELIMIT =i.CUROVELIMIT ,
a.RENEGETIMES =i.RENEGETIMES ,
a.PADPD1 =i.PADPD1 ,
a.PADPD2 =i.PADPD2 ,
a.PADPD3 =i.PADPD3 ,
a.PADPD4 =i.PADPD4 ,
a.ACCOUNTSTATE =i.ACCOUNTSTATE ,
a.BACKSTATEIN24 =i.BACKSTATEIN24 ,
a.DATA_FROM =i.DATA_FROM ,
a.KZ1 =i.KZ1 ,
a.COLLECT_DATE =i.COLLECT_DATE
when not matched then
insert
(
a.ACCNUM ,
a.LOANBALANCE ,
a.HIGHESTBALANCE ,
a.REMAINMONTH ,
a.BACKDATE ,
a.LASTDATE ,
a.NOWBACK ,
a.FACTBACK ,
a.OVERTIME ,
a.CUROVETIME ,
a.CUROVELIMIT ,
a.RENEGETIMES ,
a.PADPD1 ,
a.PADPD2 ,
a.PADPD3 ,
a.PADPD4 ,
a.ACCOUNTSTATE ,
a.BACKSTATEIN24 ,
a.DATA_FROM ,
a.KZ1 ,
a.COLLECT_DATE
)
values
(
i.ACCNUM ,
i.LOANBALANCE ,
i.HIGHESTBALANCE ,
i.REMAINMONTH ,
i.BACKDATE ,
i.LASTDATE ,
i.NOWBACK ,
i.FACTBACK ,
i.OVERTIME ,
i.CUROVETIME ,
i.CUROVELIMIT ,
i.RENEGETIMES ,
i.PADPD1 ,
i.PADPD2 ,
i.PADPD3 ,
i.PADPD4 ,
i.ACCOUNTSTATE ,
i.BACKSTATEIN24 ,
i.DATA_FROM ,
i.KZ1 ,
i.COLLECT_DATE
) ;
begin
commit;
exception
when others then
dbms_output.put_line('ERROR');
rollback;
end;
delete from advance_loanback
where substr(backstatein24,-2) = 'CC';
compute_padpd(fileDate);
begin
commit;
exception
when others then
dbms_output.put_line('ERROR');
rollback;
end;
end BASIC_TO_ADVANCE;