SELECT
	payment_date, charge_source, payment_mode_id, payment_type, pp.note,
	case when payment_type in ('P', 'A') then pp.paid_amount else 0 end as amount_debit,
	case when payment_type in ('J', 'R', 'T') then pp.paid_amount else 0 end  as amount_credit
FROM
	patient_payments pp
	INNER JOIN pharmacy_billing_header pbh ON pp.patient_service_charge_id = pbh.id 
	AND pp.charge_source = 'Pharmacy' AND pbh.opd_ipd_ot_flag = 'I' AND pbh.opd_ipd_no = '2'

UNION

SELECT
	payment_date, charge_source, payment_mode_id, payment_type, pp.note,
	case when payment_type in ('P', 'A') then pp.paid_amount else 0 end as amount_debit,
	case when payment_type in ('J', 'R', 'T') then pp.paid_amount else 0 end  as amount_credit
FROM
	patient_payments pp
	INNER JOIN patient_service_charges psc ON pp.patient_service_charge_id = psc.id 
	AND pp.charge_source = 'Service' AND psc.service_source_id = '2' AND psc.billing_module_id = 6

UNION

SELECT
	payment_date, charge_source, payment_mode_id, payment_type, pp.note,
	pp.paid_amount as amount_debit,
	0 as amount_credit
FROM
	patient_payments pp
	INNER JOIN ipd_admission ia ON pp.admission_id = ia.id 
	AND payment_type = 'A'
	AND ia.id = '2'
	
ORDER BY payment_date, charge_source, amount_debit desc