DROP PROCEDURE IF EXISTS shims_advance_payment;
CREATE PROCEDURE `shims_advance_payment`(
	adm_id INT ( 11 ), char_src VARCHAR (10))
BEGIN
	SELECT
		id,
		payment_mode,
		date,
		payment_type,
		IF(payment_type='A', paid_amount, 0) as paid_amount,
		IF(payment_type!='A', paid_amount, 0) as refund_amount,
		patient_service_charge_id,
		note
	FROM
		(
		SELECT
			pp.id,
			pp.charge_source,
			pp.payment_mode_id AS payment_mode,
			pp.created_at AS date,
			pp.payment_type,
			pp.paid_amount,
			0 AS patient_service_charge_id,
			pp.note,
			pp.admission_id 
		FROM
			patient_payments AS pp 
		LEFT JOIN patient_service_charges as psc ON psc.id=pp.patient_service_charge_id
		WHERE
			(pp.payment_type = 'A'
			AND pp.admission_id=adm_id)
			OR
			(pp.payment_type = 'R' AND
			pp.note= 'Advance'
			AND pp.admission_id=adm_id)
			OR
			(pp.payment_type = 'J' AND
			pp.payment_mode_id= 'A'
			AND psc.service_source_id=adm_id)
		) AS advanace_payment 
	WHERE
	charge_source= char_src
	ORDER BY date ASC;
END