/* TPA Report */


SELECT
	p.patient_unique_id as P_id,
	CONCAT( p.patient_name, ' (', IFNULL( p_cons.contact_details, '--' ), ')' ) as P_name,
	admt.admission_no as admit_no,
	admt.admission_date as admit_dt,
	IFNULL(CONCAT(tpa_org.organisation_name,' (',tpa_pol.policy_name,')'),'--') as tpa_dtls,
	IFNULL(tpa_stat.status,'N/A') as tpa_stat,
	(CASE WHEN admt.is_deceased = 'Y' THEN 'EXPIRED' WHEN admt.is_deceased = 'N' AND admt.is_discharged = 'Y' THEN 'DISCHARGED' ELSE 'ACTIVE' END) as pat_stat,
	IFNULL(admt.discharged_date,'--') as discharged_date,
	tpa_stat.tpa_ini_app_approved_at as init_apprv_dt,
	tpa_stat.tpa_initial_approve_amount as init_apprv_amt,
	tpa_stat.tpa_sanction_approved_at as finl_apprv_dt,
	tpa_stat.tpa_sanction_amount as finl_apprv_amt,
	tpa_stat.tpa_pay_approved_at as tpa_pay_dt,
	tpa_stat.paid_amt as tpa_paid 
FROM
	ipd_admission as admt
	INNER JOIN patients p ON admt.patient_id = p.id
	LEFT JOIN contacts p_cons ON p_cons.id = (
	SELECT
		p_cons1.id 
	FROM
		contacts p_cons1 
	WHERE
		p_cons1.data_source_id = p.id 
		AND p_cons1.data_source = 'Patient' 
		AND p_cons1.contact_type = 'M' 
		AND p_cons1.is_active = 'Y' 
	ORDER BY
		p_cons1.id DESC 
	LIMIT 1 
	)
	INNER JOIN tpa_policies tpa_pol ON admt.policy_id = tpa_pol.id
	LEFT JOIN tpa_organisations tpa_org ON tpa_pol.tpa_id = tpa_org.id
	LEFT JOIN tpa_patient_claim_workflow tpa_stat ON tpa_stat.ipd_admission_id = admt.id