逾期明细报查询

这是一个工作中，业务题目提出的报表需求，涉及到的关联表较多

SELECT
u1.pm1.dpt_name 放款营业部,
u1.belong_name AS 管理营业部,
‘’ AS 销售团队,
‘’ AS 业务主任工号,
‘’ AS 业务主任,
u1.user_name 客户经理,
u1.job_number AS 客户经理工号,
bbr.user_name 客服,
u2.job_number AS 客服工号,
tbla.cust_name AS 客户姓名,
tbbl.cert_no AS 身份证,
tbbl.finance_product_value 产品类型,
tbbl.contract_amount AS 合同金额,
tbbl.LAST_APPROVE_AMT AS 审批金额,
tbbl.Last_Deadline_Value AS 还款期限,
start_query.START_DATE AS 协定还款日,
tblr.term_no AS 当前期数,
to_char ( tbbl.create_time, ‘yyyy-MM-dd’ ) AS 申请日期,
substr( uu.end_date, 0, 10 ) AS 审批日期,
tbbl.contract_date 签约日期,
tbbl.credited_date AS 放款日期,
( CASE WHEN tblr.term_no = 1 THEN tbbl.contract_amount ELSE p1.residue_principal END ) 剩余本金,
‘逾期’ AS 还款状态,
tblr.INTEREST_AMT AS 本期应还利息,
tblr.PRINCIPAL_AMT AS 本期应还本金,
tlol.over_level 逾期期数,
( ceil( ( sysdate - to_date ( tblr.repay_date, ‘yyyy-mm-dd’ ) ) - 1 ) ) AS 逾期天数,
all_prin.M_prin 逾期本金,
all_prin.M_inte 逾期利息,
( tlol.CURR_OVERDUE_INTER * ( ceil( ( sysdate - to_date ( tblr.repay_date, ‘yyyy-mm-dd’ ) ) - 1 ) ) ) AS 罚息金额,
all_prin.M_prin + all_prin.M_inte + tlol.CURR_OVERDUE_INTER AS 逾期总金额,
tlol2.OverdueCount AS 逾期次数,
to_char ( tlol.create_time, ‘yyyy-MM-dd’ ) AS 首次逾期时间,
‘’ AS 最后账户分配时间,
bl_no.num AS 借款次数
FROM
T_BUSI_PAY_PROCESS tbpp
JOIN T_BUSI_LOAN_ACCOUNT tbla ON tbpp.borrow_no = tbla.borrow_no
AND tbla.STATUS = ‘1’
JOIN T_BUSI_BASIC_LOAN tbbl ON tbbl.agreement_no = tbpp.borrow_no
LEFT JOIN T_SYS_ORGAN pm1 ON pm1.id = tbbl.depart_id
LEFT JOIN T_SYS_USER u1 ON tbbl.creator = u1.id
LEFT JOIN (
SELECT
ipieces_no,
end_date,
curr_phase,
user_name
FROM
(
SELECT
t.ipieces_no,
t.end_date,
t.curr_phase,
t.user_name,
row_number ( ) OVER ( PARTITION BY ipieces_no ORDER BY t.end_date ASC ) AS row_flg
FROM
T_BUSI_CL_APPR t
WHERE
t.curr_phase = ‘初审人员’
) temp
WHERE
temp.row_flg = ‘1’
) uu ON uu.ipieces_no = tbbl.ipieces_no
LEFT JOIN (
SELECT
ipieces_no,
end_date,
curr_phase,
user_name
FROM
(
SELECT
t.ipieces_no,
t.end_date,
t.curr_phase,
t.user_name,
row_number ( ) OVER ( PARTITION BY ipieces_no ORDER BY t.end_date ASC ) AS row_flg
FROM
T_BUSI_CL_APPR t
WHERE
t.curr_phase = ‘终审人员’
) temp
WHERE
temp.row_flg = ‘1’
) uu2 ON uu2.ipieces_no = tbbl.ipieces_no
LEFT JOIN (
SELECT
bca.user_id,
bca.ipieces_no,
u.user_name,
u.job_number,
u.belong_name
FROM
T_BUSI_CL_APPR bca,
t_sys_user u
WHERE
u.id = bca.user_id
AND bca.opinion IN ( ‘382’, ‘383’ )
) u2 ON tbbl.ipieces_no = u2.ipieces_no
JOIN T_BUSI_LOAN_OVERDUE_LATE tlol ON tlol.borrow_no = tbpp.borrow_no
AND tlol.over_status = ‘1’
AND tbpp.cur_period = tlol.over_periods
LEFT JOIN T_BUSI_LOAN_REPAY_PLAN tblr ON ( tblr.borrow_no = tbpp.borrow_no AND tbpp.cur_period = tblr.term_no AND tblr.is_settle = ‘0’ )
LEFT JOIN (
SELECT
borrow_no,
residue_principal,
term_no
FROM
(
SELECT
borrow_no,
residue_principal,
term_no,
ROW_NUMBER ( ) OVER ( PARTITION BY borrow_no ORDER BY term_no ASC ) AS RN
FROM
T_BUSI_LOAN_REPAY_PLAN
WHERE
is_settle = ‘0’
GROUP BY
borrow_no,
residue_principal,
term_no
) rest
WHERE
rest.RN = 1
) rest_amount ON rest_amount.borrow_no = tblr.borrow_no
LEFT JOIN T_BUSI_LOAN_REPAY_PLAN p1 ON ( tblr.borrow_no = p1.borrow_no AND p1.term_no = rest_amount.term_no - 1 )
LEFT JOIN (
SELECT
borrow_no,
term_no,
( sum( RESIDUE_PRINCIPAL ) + sum( INTEREST_AMT ) ) AS Resprint
FROM
T_BUSI_LOAN_REPAY_PLAN
WHERE
is_settle = ‘0’
GROUP BY
borrow_no,
term_no
) p2 ON ( tblr.borrow_no = p2.borrow_no AND tblr.term_no = p2.term_no )
LEFT JOIN (
SELECT
borrow_no,
sum( PRINCIPAL_AMT ) AS M_prin,
sum( INTEREST_AMT ) AS M_inte
FROM
T_BUSI_LOAN_REPAY_PLAN
WHERE
is_settle = ‘0’
AND to_date ( REPAY_DATE, ‘yyyy-MM-dd’ ) < sysdate
GROUP BY
borrow_no
) all_prin ON all_prin.borrow_no = tlol.borrow_no
LEFT JOIN ( SELECT count( * ) AS num, borrow_no FROM T_BUSI_LOAN_REPAY_PLAN WHERE is_settle = ‘1’ GROUP BY borrow_no ) settle ON tblr.borrow_no = settle.borrow_no
LEFT JOIN ( SELECT borrow_no, count( * ) AS OverdueCount FROM T_BUSI_LOAN_OVERDUE_LATE GROUP BY borrow_no ) tlol2 ON tlol2.borrow_no = tlol.borrow_no
LEFT JOIN ( SELECT borrow_no, CREATE_TIME, ROW_NUMBER ( ) OVER ( PARTITION BY borrow_no ORDER BY CREATE_TIME ASC ) AS RN FROM T_BUSI_LOAN_OVERDUE_LATE ) first_over ON ( tlol.borrow_no = first_over.borrow_no AND first_over.RN = 1 )
LEFT JOIN (
SELECT
borrow_no,
UPDATE_TIME,
ROW_NUMBER ( ) OVER ( PARTITION BY borrow_no ORDER BY UPDATE_TIME DESC ) AS RN
FROM
T_BUSI_LOAN_REPAY_PLAN
WHERE
IS_SETTLE = ‘1’
AND update_time IS NOT NULL
) last_back ON ( tlol.borrow_no = last_back.borrow_no AND last_back.RN = 1 )
LEFT JOIN ( SELECT count( * ) AS num, agreement_no FROM T_BUSI_BASIC_LOAN GROUP BY agreement_no ) bl_no ON tbbl.agreement_no = bl_no.agreement_no
LEFT JOIN (
SELECT
borrow_no,
start_date
FROM
fas_user.T_BUSI_LOAN_REPAY_PLAN
WHERE
to_date ( start_date, ‘yyyy-MM-dd hh24miss’ ) < to_date ( add_months ( trunc ( sysdate, ‘MM’ ), 1 ) ) AND to_date ( start_date, ‘yyyy-MM-dd hh24miss’ ) > to_date ( trunc ( sysdate, ‘MM’ ) )
) start_query ON start_query.borrow_no = tlol.borrow_no
LEFT JOIN (
SELECT
user_id,
user_name,
ipieces_no,
end_date,
curr_phase
FROM
(
SELECT
t.ipieces_no,
t.end_date,
t.curr_phase,
t.user_name,
user_id,
row_number ( ) OVER ( PARTITION BY ipieces_no ORDER BY t.end_date ASC ) AS row_flg
FROM
FAS_USER.T_BUSI_CL_APPR t
WHERE
t.curr_phase = ‘客服人员’
AND t.opinion = ‘382’
) temp
WHERE
temp.row_flg = ‘1’
) bbr ON bbr.ipieces_no = tbbl.ipieces_no
LEFT JOIN FAS_USER.T_SYS_USER u2 ON bbr.user_id = u2.id
LEFT JOIN T_BUSI_LOAN_REPORT t ON ( t.borrow_no = tbpp.borrow_no AND t.apply_type = ‘2’ AND t.is_report = ‘0’ )
LEFT JOIN T_ACCT_SUB_ACCOUNT tasa ON tasa.basic_cust_id = tbpp.basic_cust_id
LEFT JOIN T_ACCT_PERSONAL_CUSTOMER tapc ON tapc.basic_cust_id = tbpp.basic_cust_id
WHERE
tbpp.is_default = ‘1’
AND tbpp.is_settle = ‘0’
AND tbbl.CREDITED_DATE IS NOT NULL
AND tbbl.ITEM_STATUS_KEY = ‘008’