Untitled

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;