Untitled

WITH disbursement_data AS(
  SELECT 
    dis.loan_id, 
    dis.partner_id, 
    dis.product_partnership_id, 
    dis.product_id, 
    dis.loan_start_date, 
    dis.tenure, 
    dis.loan_amount, 
    dis.interest_rate, 
    dis.loan_disbursal_date, 
    dis.state_code_originator, 
    dis.appform_id, 
    case when olls.dpd_days > 0 then olls.sma_tag else null end AS sma_tag, 
    case when olls.sma_tag = 'NPA' THEN 'Y' ELSE 'N' end AS npa_flag, 
    olls.dpd_days 
  FROM 
    "omni-lms".disbursement dis 
    right join "omni-lms".loan_summary olls on olls.loan_id = dis.loan_id 
    and olls.product_partnership_id = dis.product_partnership_id 
    and olls.product_id = dis.product_id 
  WHERE 
    dis.product_partnership_id in('0d849e78-1d3f-49e7-a452-8e062ac1e293', '746123f3-4b52-4ed3-877d-5df7f3a0ae9a') 
    AND olls.as_of_date = '2024-12-31' 
    AND dis.deleted = 0 
    and olls.deleted = 0 
    AND olls.funding_allocation = 1
), 
colending AS(
  SELECT 
    * 
  FROM 
    "omni-lms".colending 
  WHERE 
    product_partnership_id IN ('0d849e78-1d3f-49e7-a452-8e062ac1e293', '746123f3-4b52-4ed3-877d-5df7f3a0ae9a') 
    AND deleted = 0
), 
colending_data AS (
  SELECT 
    colending.loan_id, 
    colending.partner_id, 
    colending.product_partnership_id, 
    colending.product_id, 
    colending.interest_rate, 
    dd.loan_disbursal_date, 
    colending.loan_start_date, 
    colending.loan_amount, 
    colending.funding_allocation 
  FROM 
    colending 
    INNER JOIN disbursement_data dd ON dd.loan_id = colending.loan_id 
    AND dd.product_partnership_id = colending.product_partnership_id
), 
interest_days_config AS (
  SELECT 
    * 
  FROM 
    (
      SELECT 
        DISTINCT lcm.product_id, 
        alm.allowed_value, 
        lcm.effective_date, 
        Rank() OVER (
          partition BY product_id, 
          alm.config_name 
          ORDER BY 
            lcm.id DESC
        ) AS rk 
      FROM 
        "omni-lms".lms_config_product_mapping lcm 
        JOIN "omni-lms".allowed_lms_config alm ON alm.id = lcm.config_id 
      WHERE 
        lcm.config_name = 'interestDaysBasis' 
        AND lcm.date_type = 'DISBURSAL'
    ) AS config 
  WHERE 
    rk = 1
), 
loan_data AS (
  SELECT 
    * 
  FROM 
    "omni-lms".loan_summary 
  WHERE 
    deleted = 0 
    AND product_partnership_id IN ('0d849e78-1d3f-49e7-a452-8e062ac1e293', '746123f3-4b52-4ed3-877d-5df7f3a0ae9a') 
    AND funding_allocation != 1.0 
    AND as_of_date = '2024-12-31'
), 
loans_with_interest_days AS (
  SELECT 
    cd.*, 
    idc.allowed_value AS interest_days_basis 
  FROM 
    colending_data cd 
    LEFT JOIN interest_days_config idc ON cd.product_id = idc.product_id 
  WHERE 
    idc.effective_date <= cd.loan_start_date 
    AND idc.effective_date <= cd.loan_disbursal_date
), 
accrual AS (
  SELECT 
    Max(as_of_date) AS as_of_date, 
    loan_id, 
    product_id, 
    product_partnership_id, 
    Sum(interest_accrued) AS total_interest_accrued 
  FROM 
    "omni-lms".accrual 
  WHERE 
    funding_allocation != 1.0 
    AND deleted = 0 
    AND product_partnership_id IN ('0d849e78-1d3f-49e7-a452-8e062ac1e293', '746123f3-4b52-4ed3-877d-5df7f3a0ae9a') 
  GROUP BY 
    loan_id, 
    product_partnership_id, 
    product_id
), 
accrual_data AS (
  SELECT 
    accrual.* 
  FROM 
    accrual 
    INNER JOIN disbursement_data dd ON dd.loan_id = accrual.loan_id 
    AND accrual.product_partnership_id IN ('0d849e78-1d3f-49e7-a452-8e062ac1e293', '746123f3-4b52-4ed3-877d-5df7f3a0ae9a')
), 
originator_data AS (
  SELECT 
    ls.id, 
    dd.loan_id, 
    dd.partner_id, 
    dd.product_partnership_id, 
    dd.product_id, 
    dd.interest_rate, 
    dd.loan_start_date, 
    dd.state_code_originator, 
    dd.tenure, 
    dd.loan_amount, 
    ls.as_of_date, 
    ls.total_principal_outstanding, 
    ls.dpd_days, 
    ls.total_principal_overdue, 
    ls.total_interest_overdue, 
    ls.total_amount_overdue, 
    ls.loan_status, 
    ls.loan_closed_date, 
    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, 
    dd.sma_tag as customer_sma_tag, 
    dd.npa_flag as customer_npa_tag, 
    dd.dpd_days as customer_dpd, 
    COALESCE(ad.total_interest_accrued, 0) AS total_interest_accrued, 
    lwid.interest_days_basis, 
    dd.appform_id 
  FROM 
    loan_data ls 
    INNER JOIN disbursement_data dd ON dd.loan_id = ls.loan_id 
    AND dd.product_partnership_id = ls.product_partnership_id 
    AND dd.product_id = ls.product_id 
    LEFT JOIN accrual_data ad ON ad.loan_id = ls.loan_id 
    AND ad.product_partnership_id = ls.product_partnership_id 
    AND ad.product_id = ls.product_id 
    AND ad.as_of_date = ls.as_of_date 
    JOIN loans_with_interest_days lwid ON lwid.loan_id = dd.loan_id 
    AND lwid.product_id = dd.product_id
), 
lender_data AS (
  SELECT 
    ls.id, 
    cd.product_id, 
    dd.state_code_originator, 
    cd.product_partnership_id, 
    cd.funding_allocation, 
    cd.interest_rate, 
    cd.loan_start_date, 
    cd.loan_amount, 
    ls.partner_id, 
    ls.as_of_date, 
    ls.loan_id, 
    ls.total_principal_outstanding, 
    ls.dpd_days, 
    ls.total_principal_overdue, 
    ls.total_interest_overdue, 
    ls.total_amount_overdue, 
    ls.loan_status, 
    ls.loan_closed_date, 
    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, 
    ls.number_of_paid_installments + ls.number_of_unpaid_installments AS tenure, 
    COALESCE(ad.total_interest_accrued, 0) AS total_interest_accrued, 
    lwid.interest_days_basis 
  FROM 
    loan_data ls 
    INNER JOIN disbursement_data dd ON dd.loan_id = ls.loan_id 
    AND dd.product_partnership_id = ls.product_partnership_id 
    AND dd.product_id != ls.product_id 
    INNER JOIN colending_data cd ON cd.loan_id = ls.loan_id 
    AND ls.product_partnership_id = cd.product_partnership_id 
    AND cd.product_id = ls.product_id 
    LEFT JOIN accrual_data ad ON ad.loan_id = ls.loan_id 
    AND ad.product_partnership_id = ls.product_partnership_id 
    AND ad.product_id = ls.product_id 
    AND ad.as_of_date = ls.as_of_date 
    JOIN loans_with_interest_days lwid ON lwid.loan_id = cd.loan_id 
    AND lwid.product_id = cd.product_id
), 
customer_info AS (
  SELECT 
    lt.appform_id AS appform_id, 
    lt.originator_customer_id AS customer_id, 
    appl.NAME AS customer_name, 
    appl.applicant_type AS customer_type, 
    ap.loan_application_id AS loan_application_id, 
    ap.product_id AS product_id, 
    ap.loan_id AS 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'
), 
loan_id_mapping_data AS (
  SELECT 
    dis.loan_id as parent_loan_id, 
    dis.product_partnership_id, 
    lim.related_loan_id, 
    opm.partner_name, 
    dis.partner_id 
  FROM 
    "omni-lms".disbursement dis 
    left join "omni-lms".loan_id_mapping as lim on dis.loan_id = lim.parent_loan_id 
    and dis.product_partnership_id = lim.product_partnership_id 
    left join "partner-service".partner_product_partnership psppp on dis.partner_id = psppp.lender_id :: varchar 
    and dis.product_partnership_id = psppp.partnership_product_id :: varchar 
    inner JOIN "partner-service".omni_partner_master opm ON opm.partner_id :: varchar = dis.partner_id 
  where 
    dis.deleted = 0 
    AND (
      lim.related_loan_id IS NULL 
      OR (
        lim.mapping_type = 'LENDER' 
        AND lim.deleted = 0
      )
    )
), 
lender_interest_rate AS (
  SELECT 
    loan_id, 
    product_partnership_id, 
    product_id, 
    interest_rate, 
    ROW_NUMBER() OVER (
      PARTITION BY loan_id, 
      product_partnership_id, 
      product_id 
      ORDER BY 
        effective_date DESC
    ) AS rn 
  FROM 
    "omni-lms".interest_rate 
  WHERE 
    product_partnership_id IN ('0d849e78-1d3f-49e7-a452-8e062ac1e293', '746123f3-4b52-4ed3-877d-5df7f3a0ae9a') 
    AND effective_date <= '2024-12-31' 
    AND deleted = 0
), 
latest_interest_rate AS (
  SELECT 
    * 
  FROM 
    lender_interest_rate 
  WHERE 
    rn = 1
), 
originator_interest_rate AS (
  SELECT 
    lir.loan_id, 
    lir.product_partnership_id, 
    lir.interest_rate AS roi_ksf 
  FROM 
    latest_interest_rate lir 
    JOIN originator_data od ON lir.loan_id = od.loan_id 
    AND lir.product_partnership_id = od.product_partnership_id 
    AND lir.product_id = od.product_id
), 
lender_interest_rate_split AS (
  SELECT 
    lir.loan_id, 
    lir.product_partnership_id, 
    lir.interest_rate AS hurdle_rate, 
    lir.interest_rate AS roi_bank_nbfc 
  FROM 
    latest_interest_rate lir 
    JOIN lender_data ld ON lir.loan_id = ld.loan_id 
    AND lir.product_partnership_id = ld.product_partnership_id 
    AND lir.product_id = ld.product_id
), 
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 
  ld.as_of_date AS snapshot_date, 
  ld.product_id AS product_id, 
  ld.product_partnership_id AS product_partnership_id, 
  ld.funding_allocation AS funding_allocation, 
  '' AS deal_name, 
  ci.customer_id AS customer_id, 
  od.loan_id AS account_number, 
  COALESCE(od.state_code_originator, '45') AS branch_code, 
  ci.appform_id AS appform_id, 
  ci.customer_name AS customer_name, 
  oir.roi_ksf AS roi_ksf, 
--   lir.hurdle_rate AS hurdle_rate, 
--   lir.roi_bank_nbfc AS roi_bank_nbfc, 
  od.tenure AS tenure_ksf, 
--   ld.tenure AS tenure_bank_nbfc, 
  od.loan_start_date AS loan_start_date_ksf, 
--   ld.loan_start_date AS loan_start_date_bank_nbfc, 
  od.interest_days_basis AS int_calculation_method_ksf, 
--   ld.interest_days_basis AS int_calculation_method_bank_nbfc, 
  od.loan_amount AS total_share_at_time_of_clm2, 
  od.loan_amount - ld.loan_amount AS ksf_share, 
--   ld.loan_amount AS bank_nbfc_share, 
  od.total_principal_outstanding AS ksf_pos, 
--   ld.total_principal_outstanding AS bank_nbfc_pos, 
  od.dpd_days AS ksf_dpd, 
--   ld.dpd_days AS bank_nbfc_dpd, 
  od.total_principal_overdue AS ksf_principal_overdue, 
--   ld.total_principal_overdue AS bank_nbfc_principal_overdue, 
  od.total_interest_overdue AS ksf_interest_overdue, 
--   ld.total_interest_overdue AS bank_nbfc_interest_overdue, 
  od.total_amount_overdue AS ksf_total_overdue, 
--   ld.total_amount_overdue AS bank_nbfc_total_overdue, 
  od.sma_tag AS ksf_asset_classification, 
--   ld.sma_tag AS bank_nbfc_asset_classification, 
  NULL AS ksf_excess_plus_advance_emi, 
--   NULL AS bank_nbfc_excess_plus_advance_emi, 
  od.loan_status AS ksf_loan_status, 
--   ld.loan_status AS bank_nbfc_loan_status, 
  od.loan_closed_date AS ksf_loan_closure_date, 
--   ld.loan_closed_date AS bank_nbfc_loan_closure_date, 
  NULL AS ksf_principal_waiver, 
--   NULL AS bank_nbfc_principal_waiver, 
  NULL AS ksf_interest_waiver, 
--   NULL AS bank_nbfc_interest_waiver, 
  NULL AS ksf_charges_waiver, 
--   NULL AS bank_nbfc_charges_waiver, 
  od.total_interest_accrued AS accrued_interest_ksf, 
--   ld.total_interest_accrued AS accrued_interest_bank_nbfc, 
  od.npa_flag as ksf_npa_flag, 
--   ld.npa_flag as bank_nbfc_npa_flag, 
  limd.related_loan_id as lender_loan_id, 
  bank.bank_name as lender_name, 
  od.customer_sma_tag as customer_sma_sub_categories, 
  od.customer_npa_tag as customer_npa_tag, 
  od.customer_dpd as customer_dpd 
FROM 
  originator_data od 
  INNER JOIN lender_data ld ON ld.loan_id = od.loan_id 
  AND ld.product_partnership_id = od.product_partnership_id 
  AND od.as_of_date = ld.as_of_date 
  LEFT JOIN customer_info ci ON ci.appform_id = od.appform_id 
  left join loan_id_mapping_data limd on limd.parent_loan_id = od.loan_id 
  and limd.product_partnership_id = od.product_partnership_id 
  LEFT JOIN originator_interest_rate oir ON oir.loan_id = od.loan_id 
  AND oir.product_partnership_id = od.product_partnership_id 
  LEFT JOIN lender_interest_rate_split lir ON lir.loan_id = od.loan_id 
  AND lir.product_partnership_id = od.product_partnership_id 
  LEFT JOIN colending_bank_data bank ON od.product_partnership_id = bank.ppid :: varchar;