create or replace procedure P_V_PCRS_LOANDTL_NOPLAN(pk_contract_in in varchar2, param_date in varchar2,error_mes out varchar2) is
begin
/* delete from temp_p_crs_loan_dtl@test04;*/
delete from v_P_CRS_LOAN_DTL_temp ;
--基础信息段
insert into temp_p_crs_loan_dtl@test04
(business_no, custid, cur_overdue_total_int, ts)
select ld.business_no,
ld.custid,
ld.cur_overdue_total_int,
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')
from V_P_CRS_LOAN_DTL@test04 ld
where not exists
(select *
from temp_p_crs_loan_dtl@test04 dl
where dl.business_no = ld.business_no
and dl.custid = ld.custid
and dl.cur_overdue_total_int = ld.cur_overdue_total_int);
insert into v_P_CRS_LOAN_DTL_temp
(FINORGCODE,
LOANTYPE,
LOANBIZTYPE,
BUSINESS_NO,
AREACODE,
STARTDATE,
ENDDATE,
CURRENCY,
CREDIT_TOTAL_AMT,
SHARE_CREDIT_TOTAL_AMT,
MAX_DEBT_AMT,
GUARANTEEFORM,
PAYMENT_RATE,
PAYMENT_MONTHS,
NO_PAYMENT_MONTHS,
PLAN_REPAY_DT,
LAST_REPAY_DT,
PLAN_REPAY_AMT,
LAST_REPAY_AMT,
BALANCE,
CUR_OVERDUE_TOTAL_INT,
CUR_OVERDUE_TOTAL_AMT,
OVERDUE31_60DAYS_AMT,
OVERDUE61_90DAYS_AMT,
OVERDUE91_180DAYS_AMT,
OVERDUE_180DAYS_AMT,
SUM_OVERDUE_INT,
MAX_OVERDUE_INT,
CLASSIFY5,
LOAN_STAT,
REPAY_MONTH_24_STAT,
OVERDRAFT_180DAYS_BAL,
LOAN_ACCOUNT_STAT,
CUSTNAME,
CERTTYPE,
CERTNO,
CUSTID,
BAKE,
TS)
select param.param_code FINORGCODE,
'4' LOANTYPE,
'92' LOANBIZTYPE,
va.cont_code BUSINESS_NO,
ba.areaclcode AREACODE,
to_char(to_date(ww.begin_date, 'yyyy-mm-dd'), 'yyyymmdd') STARTDATE,
to_char(to_date(ipcc.max_plan_date, 'yyyy-mm-dd'), 'yyyymmdd') ENDDATE,
money.currtypecode CURRENCY,
ipc.corpus_sum CREDIT_TOTAL_AMT,
ipc.corpus_sum SHARE_CREDIT_TOTAL_AMT,
ipc.corpus_sum MAX_DEBT_AMT,
F_PCS_GUARANTEE(va.pk_contract) GUARANTEEFORM,
'03' PAYMENT_RATE,
nvl(ipcc.total_repay_count,0) PAYMENT_MONTHS,
case when nvl(ipcc.total_repay_count,0) - nvl(F_PCS_paymonth_temp(va.pk_contract,param_date),0) < 0 then 0
else nvl(ipcc.total_repay_count,0) - nvl(F_PCS_paymonth_temp(va.pk_contract,param_date),0) end NO_PAYMENT_MONTHS,
to_char(to_date(param_date, 'yyyy-mm-dd'), 'yyyymmdd') PLAN_REPAY_DT,
to_char(to_date(F_PCS_LAST_REPAY_DT_TEMP(va.pk_contract,ww.begin_date,param_date), 'yyyy-mm-dd'), 'yyyymmdd') LAST_REPAY_DT,
CASE WHEN to_date(param_date,'yyyy-mm-dd') > to_date(ipcc.max_plan_date, 'yyyy-mm-dd') THEN F_PCS_PLAN_AMT_temp(va.pk_contract,param_date) + nvl(f_pcs_last_repay_am_temp(va.pk_contract,ww.begin_date,param_date),0)
ELSE F_PCS_PLAN_AMT_temp(va.pk_contract,param_date) END PLAN_REPAY_AMT,
nvl(f_pcs_last_repay_am_temp(va.pk_contract,ww.begin_date,param_date),0) LAST_REPAY_DT,
F_PCS_PLAN_BAL_AMT_TEMP(va.pk_contract,param_date) BALANCE,
F_PCS_OVERDUE_TOTAL_INT_temp(va.pk_contract,param_date) CUR_OVERDUE_TOTAL_INT,
F_PCS_OVERDUE_TOTAL_AMT_temp(va.pk_contract,param_date) CUR_OVERDUE_TOTAL_AMT,
F_PCS_OVERDUE_DAYS_AMT_temp(va.pk_contract,param_date, 31, 60) OVERDUE31_60DAYS_AMT,
F_PCS_OVERDUE_DAYS_AMT_temp(va.pk_contract,param_date, 61, 90) OVERDUE61_90DAYS_AMT,
F_PCS_OVERDUE_DAYS_AMT_temp(va.pk_contract,param_date, 91, 180) OVERDUE91_180DAYS_AMT,
F_PCS_OVERDUE_DAYS_AMT_temp(va.pk_contract,param_date, 180, -1) OVERDUE_180DAYS_AMT,
F_PCS_SUM_OVERDUE_INT(va.pk_contract,param_date,'0') SUM_OVERDUE_INT,
F_PCS_SUM_OVERDUE_INT(va.pk_contract,param_date,'1') MAX_OVERDUE_INT,
f_ccs_getclass5(va.cont_code,
to_char(sysdate, 'yyyymmdd')) CLASSIFY5,
F_PCS_ACCOUNT_STATUS(va.pk_contract,param_date) LOAN_STAT,
F_PCS_REPAY_24_STAT_temp(va.pk_contract,param_date,ww.begin_date) REPAY_MONTH_24_STAT, --24个月(
0 OVERDRAFT_180DAYS_BAL,
(case
when exists (select 1
from temp_p_crs_loan_dtl@test04 ld
where ld.business_no = va.cont_code
and ld.custid = cu.customer_code) then
1
else
2
end) LOAN_ACCOUNT_STAT,
cu.customer_name CUSTNAME,
case
when cu.identity_type = 1 then '0'
when cu.identity_type = 5 then '2'
when cu.identity_type = 4 then '3'
else 'X'
end CERTTYPE,
cu.identity_no CERTNO, --证件号码
cu.customer_code CUSTID,
'',
to_char(sysdate,'yyyy-MM-dd HH24:mi:ss')
from yls_contract_c va
left join bd_areacl ba on va.district_sign = ba.pk_areacl
inner join yls_customer cu on va.pk_customer_lessee = cu.pk_customer
left join (select p.param_code
from yls_parameter p
where p.param_name = '个人征信金融机构代码') param on 1 = 1
left join (select w.begin_date, w.pk_initial, w.cont_code
from (select br.begin_date,
ch.pk_initial,
ch.cont_code,
br.pk_contract,
row_number() over(partition by ch.pk_initial order by br.check_date desc) rn
from yls_begin_rent br
inner join yls_contract_h ch on br.pk_contract =
ch.pk_contract
where br.billstatus = 9) w
where w.rn = 1) ww on va.cont_code = ww.cont_code
left join (select pc.pk_contract,
round(sum(nvl(pc.lease_corpus,0)),0) corpus_sum
from yls_inout_plan_c pc
where pc.direction = 0
and pc.rent_type = 1
and pc.if_begin = 0
and pc.inout_type = 1
and pc.trans_type = '0001AA10000000007NGV'
group by pc.pk_contract) ipc on ipc.pk_contract =
va.pk_contract
left join (select max(pc.plan_date) max_plan_date,
months_between(to_date(to_char(to_date(max(pc.plan_date),'yyyy-mm-dd'), 'yyyy-mm'), 'yyyy-mm'),
to_date(to_char(to_date(min(pc.plan_date),
'yyyy-mm-dd'),
'yyyy-mm'),
'yyyy-mm'))+1 total_repay_count,
pc.pk_contract
from yls_inout_plan_c pc
where pc.direction = 0
and pc.rent_type = 1
and pc.if_begin = 0
and pc.inout_type = 1
and pc.trans_type = '0001AA10000000007NGV'
group by pc.pk_contract) ipcc on ipcc.pk_contract =
va.pk_contract
left join bd_currtype money on money.pk_currtype =
va.pk_currency
left join (select max(ld.cur_overdue_total_int) max_cur_overdue_int,
ld.business_no,
ld.custid
from temp_p_crs_loan_dtl@test04 ld
group by ld.business_no, ld.custid) w on w.business_no =
va.cont_code
and w.custid =
cu.customer_code
where va.pk_contract =pk_contract_in ;
insert into v_P_CRS_LOAN_DTL@test04 select * from v_P_CRS_LOAN_DTL_temp;
commit;
error_mes := '1' ;
exception
when others then
error_mes := '表名:V_P_CRS_LOAN_DTL' || CHR(13) || '错误代码:' || SQLCODE ||
CHR(13) || '错误信息:' || SQLERRM;
dbms_output.put_line(error_mes);
end P_V_PCRS_LOANDTL_NOPLAN ;
create or replace function F_PCS_PLAN_AMT_temp(pk_contract_in in varchar2,sys_date in varchar2)
return number is
--类型定义
cursor my_cursor is
select ipc.plan_date,
sum(nvl(ipc.lease_cash,0)) lease_cash,
ipc.trans_type,
sum(nvl(ww.gather_cash, 0)) gather_cash
from v_inout_temp_view ipc
left join (select ip.pk_inout_plan,
sum(nvl(ga.gather_cash, 0)) gather_cash
from yLS_GATHER_ACCOUNT_B ga
inner JOIN YLS_ACCOUNT_RECORD ar
ON ar.pk_account_record =
ga.pk_account_record
inner join v_inout_temp_view ip
on ga.pk_inout_plan = ip.pk_inout_plan
and ar.dr = 0
and ga.source_bill is null
where ar.trans_date < sys_date
and ip.pk_contract =pk_contract_in
group by ip.pk_inout_plan) ww
on ipc.pk_inout_plan = ww.pk_inout_plan
where ipc.plan_date < sys_date
and ipc.pk_contract =pk_contract_in
group by ipc.trans_type,ipc.plan_date ;
inter_month integer;
lease_cash number := 0 ;
months_between integer;
max_date varchar2(10);
begin
SELECT MAX(IPC.PLAN_DATE) MAX_DATE into max_date FROM YLS_INOUT_PLAN_C IPC
where IPC.PK_CONTRACT = pk_contract_in
AND ipc.direction = 0
and ipc.rent_type = 1
and ipc.if_begin = 0
and ipc.inout_type = 1
and ipc.trans_type = '0001AA10000000007NGV';
select months_between(to_date(to_char(to_date(sys_date,
'yyyy-mm-dd'),'yyyy-mm'),'yyyy-mm'),
to_date(to_char(to_date(max_date,
'yyyy-mm-dd'),
'yyyy-mm'),
'yyyy-mm'))
into months_between
from dual;
if months_between < 0 then lease_cash := 0 ;
return lease_cash;
end if;
for v_cursor in my_cursor loop
select months_between(to_date(to_char(to_date(sys_date,'yyyy-mm-dd'), 'yyyy-mm'), 'yyyy-mm'),
to_date(to_char(to_date(v_cursor.plan_date,
'yyyy-mm-dd'),
'yyyy-mm'),
'yyyy-mm'))
into inter_month
from dual;
if inter_month > 0 then
lease_cash := lease_cash + round(nvl(v_cursor.lease_cash,0.00),0) - round(nvl(v_cursor.gather_cash,0),0) ;
end if ;
end loop;
if lease_cash is null then lease_cash := 0 ;
end if ;
return lease_cash;
end F_PCS_PLAN_AMT_temp;
wx :15625230623