SELECT
c.CONTRACT_ID,
c.CONTRACT_NBR,
gc.GUARANTY_COMPANY_NAME AS gc,
decode(c.BANK_CONTRACT_ID, 472, 'cfc', 'other') AS model,
c.PAYOFF_DATE,
c.person_id,
rg.REGION_NAME AS SELLERPLACE_REGION,
c.CONTRACT_STATUS_3 AS STATUS,
a.UNPAID_PENALTY,
a.REMAINING_INST,
(SELECT
trunc(SYSDATE) - MIN(i.DATE_INSTALMENT) AS dpd
FROM dwcn.instalment i
where i.instalment_TYPE = 'R'
AND i.PAYMENT_AMOUNT < i.INSTALMENT_AMOUNT
) as dpd
FROM dwcn.contract c
JOIN dwcn.salroom s ON c.SALROOM_ID = s.SALROOM_ID
JOIN dwcn.hc_region rg ON s.HCREGION_ID = rg.HCREGION_ID
JOIN dwcn.bank_contract bc ON c.BANK_CONTRACT_ID = bc.BANK_CONTRACT_ID
JOIN dwcn.guaranty_company gc ON bc.guaranty_company_id = gc.guaranty_company_id
join
(SELECT
i.CONTRACT_ID,
SUM(CASE
WHEN i.instalment_TYPE = 'P' THEN
i.INSTALMENT_AMOUNT - nvl(PAYMENT_AMOUNT, 0)
ELSE
0
END) AS UNPAID_PENALTY,
SUM(CASE
WHEN i.instalment_TYPE = 'R' THEN
i.INSTALMENT_AMOUNT - nvl(PAYMENT_AMOUNT, 0)
ELSE
0
END) AS REMAINING_INST
FROM dwcn.instalment i
group by i.CONTRACT_ID) a on a.CONTRACT_ID=c.CONTRACT_ID
join (SELECT t.person_id,
substr(MAX(CASE WHEN rel2person_code = 1 AND contact_code = 2 AND contact_status = 'a' and is_contact_phone=1 THEN to_char(dtime_modiftime, 'yyyy-mm-dd hh24:mi:ss') || nvl(contact, '') ELSE NULL END), 20, 20) AS contact_mobil
FROM (SELECT /*a.id_source,*/
a.person_id,
a.contact,
a.area_code,
a.contact_status,
a.contact_ext,
b.contact_code, /*b.contact_name,*/
c.rel2person_code, /*c.rel2person_name,*/
a.dtime_modiftime,
a.owner_name,
a.is_contact_phone,
RANK() over(PARTITION BY a.person_id, b.contact_code, c.rel2person_code, a.contact_status,a.is_contact_phone ORDER BY a.dtime_modiftime DESC) rn
FROM dwcn.t_contact a
JOIN dwcn.clt_contact_type b ON a.contact_type_id = b.contact_type_id
JOIN dwcn.clt_rel2person_type c ON a.rel2person_type_id = c.rel2person_type_id
WHERE a.source_table = 'C'
) t
WHERE rn = 1
GROUP BY t.person_id) b on b.person_id=c.person_id
WHERE c.CONTRACT_STATUS_3 IN ('a', 'k')
and c.CONTRACT_ID='7675157'
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7899089/viewspace-740402/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7899089/viewspace-740402/