风清扬123(提问者) 回复 axingdrop table tmp_YQTIME;
create table tmp_YQTIME as
--计算逾期起期、逾期时长
SELECT DISTINCT c_Client_Cde,
--c_bank_pk,
c_vouc_no,
MIN(T_WITHHOLD_DAY) T_WITHHOLD_DAY/*,
(to_date(TO_CHAR(SYSDATE, \'yyyymmdd\'), \'yyyymmdd\') -
MIN(T_WITHHOLD_DAY)) yq_day*/
FROM ((
---未决采取状态判别方法
SELECT c_Client_Cde,
/*c_bank_pk,*/
c_vouc_no,
MIN(T_WITHHOLD_DAY) T_WITHHOLD_DAY
FROM (SELECT T2.c_Client_Cde,
/* t2.c_bank_pk,*/
t2.c_vouc_no,
T1.T_WITHHOLD_DAY
FROM (SELECT C_BANK_PK, MIN(T.T_WITHHOLD_DAY) T_WITHHOLD_DAY
FROM HASTUDY.TB_CLM_MQHK_TOT T
WHERE C_STATUS = \'A\' ---IN (\'A\', \'P\') --不考虑部分逾期情况，因与理赔期次重合
GROUP BY C_BANK_PK) T1,
HASTUDY.TB_CLM_TOT_INFO T2
WHERE T2.C_BANK_PK = T1.C_BANK_PK
and t2.n_clm_real <=0)
GROUP BY c_Client_Cde, /*c_bank_pk,*/ c_vouc_no)
UNION
(
----已决有赔款采取期次填充方法
SELECT T2.c_Client_Cde,
/* t2.c_bank_pk,*/
t2.c_vouc_no,
MIN(t1.t_withhold_day) t_withhold_day
FROM (SELECT c_bank_pk, MIN(t_withhold_day) t_withhold_day
FROM (SELECT c_bank_pk,
t_withhold_day,
exp(SUM(ln(SIGN(n_clm) / 2 +
ABS(SIGN(n_clm)) / 2 +
(1 - SIGN(n_clm) / 2 -
ABS(SIGN(n_clm)) / 2) * 0.01))
over(PARTITION BY c_bank_pk ORDER BY
t_withhold_day DESC)) bal
FROM (SELECT t.c_bank_pk,
t.t_withhold_day,
t.n_clm_tot - t1.n_repay_tot n_clm
FROM (SELECT c_bank_pk,
t_withhold_day,
SUM(n_clm_got_pnpl +
n_clm_got_itrt) over(PARTITION BY c_bank_pk ORDER BY t_withhold_day) n_clm_tot
FROM hastudy.tb_clm_mqhk_tot t
WHERE t.n_clm_got_pnpl + n_clm_got_itrt > 0) t,
(SELECT t2.c_bank_pk,
t1.n_repay_pnpl + t1.n_repay_itrt n_repay_tot
FROM hastals.mv_all_vouc t1,
hastudy.tb_clm_tot_info t2
WHERE t1.n_clm_real > 0
AND t1.c_vouc_no = t2.c_vouc_no
AND t2.c_loan_contract =
t1.c_contract_no) t1
WHERE t1.c_bank_pk = t.c_bank_pk))
WHERE bal >= 1
GROUP BY c_bank_pk) t1,
hastudy.tb_clm_tot_info t2
WHERE t1.c_bank_pk = t2.c_bank_pk
and t2.n_clm_real >0
GROUP BY T2.c_Client_Cde, /*t2.c_bank_pk,*/ t2.c_vouc_no))
GROUP BY c_Client_Cde,/*c_bank_pk,*/c_vouc_no;
select t.c_stud_source as 贷款类型,
s1.T_GRADUAT_TM as 毕业时间,
t.c_client_nme as 姓名,
t.c_certf_no as 身份证号,
t.c_dpt_cde as 归属机构,
t.c_owner_cde as 所属银行,
t.bankname as 经办行,
bk.c_prin_phone as 经办行电话,
t.t_credit_sdate as 贷款起期,
t.t_credit_edate as 贷款止期,
t.t_first_pay_day as 第一次还款日,
t.zhanbao_wj_pnpl as 未决本金,
t.zhanbao_wj_itrt as 未决利息,
t.n_clm_pnpl as 理赔本金,
t.n_clm_itrt as 理赔利息,
t.n_clm_pnpl + t.n_clm_itrt as 理赔金额,
to_date(TO_CHAR(SYSDATE, \'yyyymmdd\'), \'yyyymmdd\') -
nvl(t.t_first_pay_day, trunc(s1.T_GRADUAT_TM, \'YY\') + 211) as 进入还款期天数, --首次还款日为空的设置为毕业年份的7月31日
t.n_year_sum as 承保本金,
t.n_cur_balance as 贷款余额,
t.n_clm_real as 已决金额,
t.n_loss_pnpl as 逾期本金,
t.n_loss_itrt as 逾期利息,
t.n_loss_pnpl + t.n_loss_itrt as 逾期金额,
t4.T_WITHHOLD_DAY as 连续逾期起期,
(to_date(TO_CHAR(SYSDATE, \'yyyymmdd\'), \'yyyymmdd\') -t4.T_WITHHOLD_DAY) as 逾期天数,
t2.TotolNUM as 贷款笔数,
t3.YQNUM as 逾期笔数,
nvl2(onl.C_HME_TEL,\'是\',\'否\') as 是否有电话,
onl.C_PHONE as 联系号码,
t.dq_flag as 到期标识,
case
when t.dq_flag = \'已到期\' then
ceil(sysdate - nvl(t.t_credit_edate, t.t_insrnc_end_tm))
end as 到期天数,
s1.C_school_NME as 就读学校,
t5.t_oper_tm 最近一次拨打时间,
t5.c_handler 最近一次操作人,
t5.c_handle_status 最近一次联系状态,
t5.c_overdue_reason 逾期原因,
t5.c_proc_desc 操作结论
from hastals.mv_all_stud t,
(SELECT DISTINCT C_CLIENT_CDE,
MAX(C_CLIENT_NME) C_school_NME,
MAX(T_GRADUAT_TM) T_GRADUAT_TM
FROM (SELECT DISTINCT T1.C_CLIENT_CDE,
T3.C_CLIENT_NME,
t1.T_GRADUAT_TM
FROM (SELECT *
FROM HASTUDY.TB_INDIV_schl
WHERE C_SCHL_CDE IS NOT NULL) T1,
HASTUDY.TB_CLIENT_SCHL t3
WHERE T1.C_SCHL_CDE = t3.c_client_cde)
GROUP BY C_CLIENT_CDE) s1,
(select t.c_cust_cde, t.c_certf_no, count(1) as TotolNUM
from hastals.mv_all_vouc t
group by t.c_cust_cde, t.c_certf_no) t2,
(select t.c_cust_cde, t.c_certf_no, count(1) as YQNUM
from hastals.mv_all_vouc t
where t.hk_status = \'逾期\'
group by t.c_cust_cde, t.c_certf_no) t3,
(select t.c_client_cde, min(t.t_withhold_day) as t_withhold_day
from TMP_YQTIME t
group by t.c_client_cde) t4,
(select distinct t.c_client_cde,
t.t_oper_tm,
t.c_handler,
(case
when t.c_handle_status = \'973004\' then
\'成功\'
else
\'失败\'
end) c_handle_status,
t1.c_dict_nme c_overdue_reason,
t.c_proc_desc
from HASTUDY.TB_rsk_ZCZQ t,
(select *
from hastudy.tb_comm_dict
where c_dict_cde is not null) t1
where t.c_overdue_reason = t1.c_dict_cde(+)
and t.c_id = any (select max(c_id)
from HASTUDY.TB_rsk_ZCZQ
where c_client_cde = t.c_client_cde
and c_oper_type = \'885001\')) t5,
TB_ONL_APPLY_LOAN onl,
TB_CLIENT_BANK bk
where t.hk_status = \'逾期\'
and t.c_cust_cde = s1.C_CLIENT_CDE(+)
and t.c_cust_cde = t2.c_cust_cde(+)
and t.c_certf_no = t2.c_certf_no(+)
and t.c_cust_cde = t3.c_cust_cde(+)
and t.c_certf_no = t3.c_certf_no(+)
and t.c_cust_cde = t4.c_Client_Cde(+)
and t.c_cust_cde = t5.c_client_cde(+)
and t.c_cust_cde=onl.c_client_cde(+)
and t.c_cust_cde=bk.c_client_cde(+)
1min目标场景问卷
立即参与
