DROP FUNCTION IF EXISTS fn_service_charge_dtls;
CREATE FUNCTION fn_service_charge_dtls(a_service_charge_id int)
RETURNS TEXT
BEGIN
DECLARE	v_return TEXT;

	SELECT JSON_ARRAYAGG(JSON_OBJECT(	
			'service_item_name', service_item_name,
			'net_amount', net_amount
			))
			INTO	v_return
			FROM
				(
					SELECT	si.service_item_name as service_item_name,
							scd.total_service_time as total_service_time,
							scd.applicable_rate as applicable_rate,
							scd.net_amount as net_amount
					FROM	patient_service_charge_details scd
					INNER JOIN	service_items	si ON	si.id= scd.service_id
					WHERE	scd.status IN ('B', 'A', 'C')
					AND		scd.patient_service_charge_id = a_service_charge_id
				) sc_detail;
	return v_return;
END;