Untitled

WITH
	customer_data AS (
		SELECT
			rc.loan_id,
			rc.product_partnership_id,
			rc.debit_date,
			rc.repayment_id,
			rc.payment_method,
			rc.repayment_purpose,
			rc.receipt_date,
			rc.amount_paid,
			rc.as_of_date,
			rc.transfer_date,
			rc.created_at,
			rc.id,
			rc.utr,
			rc.credit_bank_account_number,
			rc.collection_account_type,
			rc.amount_received AS customer_debit_amount
		FROM
			"omni-lms".repayment_collection rc
		WHERE
			rc.deleted = 0 and rc.loan_id='CBI_Test_17092025_001'
	),
	originator_data AS (
		SELECT
			rpa.loan_id,
			rpa.product_partnership_id,
			rpa.product_id,
			rpa.repayment_id,
			Sum(rpa.amount_allocated) AS originator_allocation
		FROM
			"omni-lms".repayment_collection rc
			LEFT OUTER JOIN "omni-lms".repayment_allocation rpa ON rc.repayment_id = rpa.repayment_id
			AND rc.product_partnership_id = rpa.product_partnership_id
			JOIN "omni-lms".disbursement dis ON rpa.loan_id = dis.loan_id
			AND rpa.product_partnership_id = dis.product_partnership_id
			AND rpa.product_id = dis.product_id
		WHERE
			rpa.deleted = 0
			AND rc.deleted = 0
			AND dis.deleted = 0
			AND rpa.funding_allocation != 1.0
			AND rpa.partner_id = dis.partner_id and rpa.loan_id='CBI_Test_17092025_001'
		GROUP BY
			rpa.loan_id,
			rpa.product_id,
			rpa.product_partnership_id,
			rpa.repayment_id
	),
	lender_data AS (
		SELECT
			rpa.loan_id,
			rpa.product_partnership_id,
			rpa.product_id,
			rpa.repayment_id,
			Sum(rpa.amount_allocated) AS lender_allocation
		FROM
			"omni-lms".repayment_collection rc
			LEFT OUTER JOIN "omni-lms".repayment_allocation rpa ON rc.repayment_id = rpa.repayment_id
			AND rc.product_partnership_id = rpa.product_partnership_id
			JOIN "omni-lms".disbursement dis ON rpa.loan_id = dis.loan_id
			AND rpa.product_partnership_id = dis.product_partnership_id
		WHERE
			rpa.deleted = 0
			AND rc.deleted = 0
			AND dis.deleted = 0
			AND rpa.funding_allocation != 1.0
			AND rpa.partner_id != dis.partner_id and rpa.loan_id='CBI_Test_17092025_001'
		GROUP BY
			rpa.loan_id,
			rpa.product_partnership_id,
			rpa.repayment_id,
			rpa.product_id
	),
	rep_summ AS (
		SELECT
			*
		FROM
			"omni-lms".repayment_summary
		WHERE
			deleted = 0
			AND funding_allocation = 1
			AND latest = 'TRUE' and loan_id='CBI_Test_17092025_001'
	),
	collection_summary_recast_data AS (
		SELECT
			*
		FROM
			"omni-lms".collection_summary_recast csr
		WHERE
			deleted = 0
			AND latest = 'TRUE'
	),
	transf_data AS (
		SELECT
			td.loan_id,
			td.product_partnership_id,
			td.product_id,
			td.repayment_id,
			string_agg(
				td.transfer_date::text,
				','
				ORDER BY
					td.transfer_date ASC
			) AS transfer_date,
			string_agg(
				td.escrow_transfer_utr::text,
				','
				ORDER BY
					td.transfer_date ASC
			) AS escrow_transfer_utr,
			string_agg(
				td.collection_id::text,
				','
				ORDER BY
					td.transfer_date ASC
			) AS collection_id,
			sum(csr.total_principal_paid) AS total_principal_paid,
			sum(csr.total_interest_paid) AS total_interest_paid
		FROM
			"omni-lms".transfer_data td
			LEFT JOIN collection_summary_recast_data csr ON td.loan_id = csr.loan_id
			AND td.product_partnership_id = csr.product_partnership_id
			AND td.product_id = csr.product_id
			AND td.collection_id = csr.collection_id
		WHERE
			td.deleted = 0
		GROUP BY
			td.loan_id,
			td.product_partnership_id,
			td.product_id,
			td.repayment_id
	),
	loan_id_mapping_data AS (
		SELECT
			dis.loan_id AS parent_loan_id,
			dis.product_partnership_id,
			lim.related_loan_id,
			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
		WHERE
			lim.related_loan_id IS NULL
			OR (
				lim.mapping_type = 'LENDER'
				AND lim.deleted = 0
			)
	),
	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
	cd.loan_id AS loan_id,
	lim.related_loan_id AS lender_loan_id,
	bank.bank_name AS lender_name,
	cd.repayment_id AS orig_payment_ref_number,
	cd.debit_date AS customer_debit_date,
	cd.debit_date AS value_date,
	cd.receipt_date AS orig_receipt_date,
	rs.excess_amount AS excess_amount,
	bank.bank_name AS credit_bank_name,
	cd.credit_bank_account_number AS credit_bank_account_number,
	cd.collection_account_type AS credit_account_type,
	to_char(to_timestamp(cd.created_at / 1000), 'YYYY-MM-DD') AS omni_receipt_date,
	cd.amount_paid AS allocation_to_cx_loan,
	cd.payment_method AS payment_method,
	cd.repayment_purpose AS repayment_purpose,
	cd.utr AS utr,
	COALESCE(od.originator_allocation, 0) AS originator_share,
	COALESCE(ld.lender_allocation, 0) AS bank_nbfc_share,
	COALESCE(ltd.transfer_date, '') AS escrow_transfer_date,
	COALESCE(ltd.escrow_transfer_utr, '') AS escrow_transfer_utr,
	COALESCE(ltd.collection_id, '') AS otd_id,
	cd.customer_debit_amount AS customer_debit_amount,
	(cd.customer_debit_amount - cd.amount_paid) AS parked_towards_charges,
	COALESCE(ltd.total_principal_paid, 0) AS principal_allocation_recast,
	COALESCE(ltd.total_interest_paid, 0) AS interest_allocation_recast
FROM
	customer_data cd
	LEFT JOIN rep_summ rs ON cd.loan_id = rs.loan_id
	AND cd.repayment_id = rs.repayment_id 
	LEFT JOIN originator_data od ON cd.loan_id = od.loan_id
	AND cd.repayment_id = od.repayment_id 
	LEFT JOIN lender_data ld ON cd.loan_id = ld.loan_id
	AND cd.repayment_id = ld.repayment_id 
	LEFT JOIN transf_data ltd ON cd.loan_id = ltd.loan_id
	AND cd.product_partnership_id = ltd.product_partnership_id
	AND cd.repayment_id = ltd.repayment_id
	AND (
		ltd.product_id IS NULL
		OR od.product_id != ltd.product_id
	)
	LEFT JOIN loan_id_mapping_data lim ON lim.parent_loan_id = ld.loan_id
	AND lim.product_partnership_id = ld.product_partnership_id
	LEFT JOIN colending_bank_data bank ON cd.product_partnership_id = bank.ppid::varchar
	where cd.loan_id='CBI_Test_17092025_001'
ORDER BY
	cd.loan_id;



select * from "omni-lms".collection_summary_recast where loan_id='CBI_Test_17092025_001' and deleted=0 and latest=TRUE;
select * from "omni-lms".loan_id_mapping limit 10;

------reposting_report
select * from "omni-lms".loan_id_mapping limit 10;
select * from "omni-lms".repayment_collection where loan_id='CBI_Test_17092025_001' and deleted=0;

WITH
	loan_id_mapping_data AS (
		SELECT
			dis.loan_id AS parent_loan_id,
			dis.product_partnership_id,
			lim.related_loan_id,
			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
		WHERE
			lim.related_loan_id IS NULL
			OR (
				lim.mapping_type = 'LENDER'
				AND lim.deleted = 0
			)
	),
	customer_data AS (
		SELECT
			rc.loan_id,
			rc.product_partnership_id,
			rc.debit_date,
			rc.repayment_id,
			rc.payment_method,
			rc.repayment_purpose,
			rc.receipt_date,
			rc.amount_paid,
			rc.as_of_date,
			rc.transfer_date,
			rc.created_at,
			rc.id,
			rc.utr,
			rc.credit_bank_account_number,
			rc.collection_account_type,
			rc.amount_received AS customer_debit_amount
		FROM
			"omni-lms".repayment_collection rc
		WHERE
			rc.deleted = 0
	),
	rep_summ AS (
		SELECT
			*
		FROM
			"omni-lms".repayment_summary
		WHERE
			deleted = 0
			AND funding_allocation = 1
			AND latest = 'TRUE'
	),
	allocation_summary AS (
		SELECT
			ra.loan_id,
			ra.product_partnership_id,
			ra.product_id,
			ra.due_date,
			ra.repayment_id,
			SUM(
				CASE
					WHEN ra.balance_type = 'PRINCIPAL' THEN ra.amount_allocated
					ELSE 0
				END
			) AS total_principal,
			SUM(
				CASE
					WHEN ra.balance_type = 'INTEREST' THEN ra.amount_allocated
					ELSE 0
				END
			) AS total_interest
		FROM
			"omni-lms".repayment_allocation ra
		WHERE
			ra.deleted = 0
			AND ra.funding_allocation = 1
			AND ra.payment_status = 'PAID' and ra.loan_id='10009262605_2'
		GROUP BY
			ra.loan_id,
			ra.product_partnership_id,
			ra.product_id,
			ra.due_date,
			ra.repayment_id
	),
	customer_info AS (
		SELECT
			lt.appform_id AS appform_id,
			appl.name AS customer_name,
			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'
	),
	foreclosure_charges AS (
		SELECT 
			*
			from 
			"omni-lms".charges_accrual
			where deleted=0 
			and funding_allocation = 1 
	)
SELECT
	lim.related_loan_id AS customer_id,
	cd.loan_id AS loan_id,
	ci.customer_name AS customer_name,
	cd.repayment_id AS repayment_id,
	cd.amount_paid AS total_paid_amount,
	cd.debit_date AS customer_paid_date,
	cd.debit_date AS value_date,
	cd.payment_method AS instrument_type,
	cd.utr AS utr,
	als.total_principal AS princiapl_collection,
	als.total_interest AS interest_collection,
	0 AS bounce_charge,
	rs.excess_amount AS excess_amount,
	0 AS late_payment_interest_collection,
	0 AS late_payment_charges_collection,
	0 as legal_charges_collected,
	CASE 
        WHEN COALESCE(rs.excess_amount, 0) > 0 THEN 'YES' 
        ELSE 'NO' 
    END AS excess_identifier,
	'Reciept Posting' AS receipt_status
FROM
	customer_data cd
	LEFT JOIN rep_summ rs ON cd.loan_id = rs.loan_id
	AND cd.repayment_id = rs.repayment_id
	LEFT JOIN loan_id_mapping_data lim ON lim.parent_loan_id = cd.loan_id
	AND lim.product_partnership_id = cd.product_partnership_id
	LEFT JOIN customer_info ci ON ci.loan_id = cd.loan_id
	LEFT JOIN allocation_summary als on als.loan_id = cd.loan_id
	AND als.product_partnership_id = cd.product_partnership_id
	AND als.repayment_id = cd.repayment_id
ORDER BY
	cd.loan_id;