Untitled
unknown
plain_text
20 days ago
4.4 kB
1
Indexable
WITH combined_loan_data AS ( SELECT ls.id, ls.loan_id, ls.product_partnership_id, ls.product_id, ls.as_of_date, ls.total_principal_outstanding, ls.max_dpd_days, ls.interest_dpd_days, ls.dpd_days, ls.total_principal_overdue, ls.total_interest_overdue, ls.total_amount_overdue, ls.loan_status, ls.loan_stage, ls.loan_closed_date, dis.partner_id, dis.loan_start_date, dis.state_code_originator, dis.tenure, dis.loan_amount, dis.interest_rate, dis.appform_id, CASE WHEN ls.dpd_days > 0 THEN ls.sma_tag ELSE NULL END AS sma_tag, CASE WHEN ls.sma_tag = 'NPA' THEN 'Y' ELSE 'N' END AS npa_flag FROM "omni-lms".loan_summary ls LEFT JOIN "omni-lms".disbursement dis ON ls.loan_id = dis.loan_id AND ls.product_partnership_id = dis.product_partnership_id AND ls.product_id = dis.product_id WHERE ls.product_partnership_id IN ('8626882d-9006-4b8f-b597-114c94f30e86', '27ed3d36-b91f-4766-afea-305791104f15') AND ls.funding_allocation = 1 AND ls.deleted = 0 AND ls.as_of_date = '2025-02-28' ), accrual_data AS ( SELECT accr.loan_id, accr.product_id, accr.product_partnership_id, COALESCE(SUM(accr.interest_accrued), 0) AS total_interest_accrued FROM "omni-lms".accrual accr WHERE accr.funding_allocation = 1 AND accr.deleted = 0 AND accr.product_partnership_id IN ('8626882d-9006-4b8f-b597-114c94f30e86', '27ed3d36-b91f-4766-afea-305791104f15') GROUP BY accr.loan_id, accr.product_id, accr.product_partnership_id ), charges_data AS ( SELECT charge.loan_id, charge.product_id, charge.product_partnership_id, COALESCE(SUM(charge.charge_accrued), 0) AS total_charges_accrued FROM "omni-lms".charges_accrual charge WHERE charge.funding_allocation = 1 AND charge.deleted = 0 AND charge.product_partnership_id IN ('8626882d-9006-4b8f-b597-114c94f30e86', '27ed3d36-b91f-4766-afea-305791104f15') GROUP BY charge.loan_id, charge.product_id, charge.product_partnership_id ), customer_info AS ( SELECT lt.appform_id, lt.originator_customer_id AS customer_id, appl.name AS customer_name, appl.applicant_type AS customer_type, ap.loan_application_id, ap.product_id, ap.loan_id FROM "appform".loan_term lt INNER JOIN "appform".applicant appl ON appl.appform_id = lt.appform_id INNER JOIN "appform".appform ap ON ap.id = appl.appform_id WHERE appl.applicant_type = 'borrower' ), colending_bank_data AS ( SELECT p3.partnership_product_id as ppid, opm.partner_name AS bank_name FROM "partner-service".partner_product_partnership p3 JOIN "partner-service".omni_partner_master opm ON opm.partner_id = p3.lender_id ) SELECT cld.as_of_date AS snapshot_date, cld.product_id, cld.product_partnership_id, ci.loan_application_id AS loan_application_id_los, ci.loan_id AS loan_account_number, ci.appform_id AS appform_id, 'Gold Loans' AS product_name, 'New Loan program' AS program_name, ci.customer_id AS customer_id, ci.customer_name AS customer_name, cld.loan_start_date AS loan_start_date, cld.loan_amount AS loan_amount_at_disbursal, cld.interest_rate AS base_interest_rate, cld.tenure AS tenure_months, cld.total_principal_outstanding AS pos, cld.total_principal_overdue AS principal_overdue, cld.total_interest_overdue AS interest_overdue, cld.total_amount_overdue AS total_overdue, accr.total_interest_accrued AS accrued_interest, chg.total_charges_accrued AS accrued_charge, cld.max_dpd_days AS loan_dpd, cld.interest_dpd_days AS interest_dpd, cld.dpd_days AS principal_dpd, cld.sma_tag AS asset_classification, NULL AS ksf_excess_plus_advance_emi, cld.loan_status AS loan_status, cld.loan_stage AS loan_stage, cld.loan_closed_date AS loan_closure_date FROM combined_loan_data cld LEFT JOIN customer_info ci ON ci.appform_id = cld.appform_id LEFT JOIN accrual_data accr ON accr.loan_id = cld.loan_id AND accr.product_partnership_id = cld.product_partnership_id AND accr.product_id = cld.product_id LEFT JOIN charges_data chg ON chg.loan_id = cld.loan_id AND chg.product_partnership_id = cld.product_partnership_id AND chg.product_id = cld.product_id LEFT JOIN colending_bank_data bank ON cld.product_partnership_id = bank.ppid :: varchar;
Editor is loading...
Leave a Comment