SELECT p.MR_CODE, p.PATIENT_NM,p.ADDRESS,
            r.REGISTRY_DT,r.IN_OUT_STS, pg.price_group_code, deb.debitur_nm,
            COALESCE(su_ri.SRVC_UNIT_NM,su_rj.SRVC_UNIT_NM) SRVC_UNIT_NM,
            COALESCE(sc_ri.SERVICE_CLASS_NAME,sc_rj.SERVICE_CLASS_NAME) SERVICE_CLASS_NAME,
            roomd.*, coalesce(pay.terbayar,0) terbayar, bil.jaminan, bil.total, bil.total_patient
        FROM
            registry r JOIN patient p ON r.MR_ID=p.MR_ID
            LEFT JOIN (
                SELECT uv.REGISTRY_ID, max(rm.ROOM_MUTATION_ID) ROOM_MUTATION_ID
                FROM unit_visit uv JOIN room_mutation rm ON uv.UNIT_VISIT_ID=rm.UNIT_VISIT_ID
                GROUP BY uv.REGISTRY_ID) rm_max ON r.REGISTRY_ID=rm_max.REGISTRY_ID
            LEFT JOIN room_mutation rm ON rm_max.ROOM_MUTATION_ID=rm.ROOM_MUTATION_ID
            LEFT JOIN room ON rm.ROOM_ID=room.ROOM_ID
            LEFT JOIN service_unit su_ri ON room.SRVC_UNIT_ID=su_ri.SRVC_UNIT_ID
            LEFT JOIN service_class sc_ri ON rm.SERVICE_CLASS_ID=sc_ri.SERVICE_CLASS_ID
            LEFT JOIN (
                SELECT uv.REGISTRY_ID, max(uv.UNIT_VISIT_ID) UNIT_VISIT_ID
                FROM unit_visit uv WHERE registry_no IS NOT NULL
                GROUP BY uv.REGISTRY_ID) uv_max ON r.REGISTRY_ID=uv_max.REGISTRY_ID
            LEFT JOIN unit_visit uv ON uv_max.UNIT_VISIT_ID=uv.UNIT_VISIT_ID
            LEFT JOIN service_unit su_rj ON uv.UNIT_ID_TO=su_rj.SRVC_UNIT_ID
            LEFT JOIN service_class sc_rj ON r.SERVICE_CLASS_ID=sc_rj.SERVICE_CLASS_ID
            LEFT JOIN price_group pg ON r.price_group_id = pg.price_group_id
            LEFT JOIN patient_debitur pdeb ON r.registry_id = pdeb.registry_id
            LEFT JOIN debitur deb ON pdeb.debitur_id = deb.debitur_id
            JOIN (
                (
                    SELECT 'Transaksi Penggunaan Tempat TIdur' ket,r.REGISTRY_ID, sg.S_GROUP_NM, ms.MEDICAL_SERVICE_NAME, '' MEDICAL_ACTOR,
                       sc.SERVICE_CLASS_NAME SC_DETIL,rm.MUTATION_DT startDate,rm.UNTIL_DT endDate,
                       b.SOURCE_CHARGE, b.DISCOUNT, b.NETTO_CHARGE, COALESCE(b.DEBITUR_CHARGE, 0) DEBITUR_CHARGE,
                       COALESCE(b.NETTO_CHARGE, 0) -  COALESCE(b.DEBITUR_CHARGE, 0)  IUR
                    FROM
                        registry r JOIN patient p ON r.MR_ID=p.MR_ID
                        JOIN unit_visit uv ON r.REGISTRY_ID=uv.REGISTRY_ID
                        JOIN room_mutation rm ON uv.UNIT_VISIT_ID=rm.UNIT_VISIT_ID
                        JOIN room ON rm.ROOM_ID=room.ROOM_ID
                        JOIN medical_service ms ON room.MEDICAL_SERVICE_ID=ms.MEDICAL_SERVICE_ID
                        JOIN service_class sc ON rm.SERVICE_CLASS_ID=sc.SERVICE_CLASS_ID
                        JOIN service_group sg ON ms.S_GROUP_ID=sg.S_GROUP_ID
                        JOIN billing b ON rm.BILLING_ID=b.BILLING_ID
                    WHERE r.REGISTRY_ID='110207075802745101RSHA'
                        AND (EXISTS (SELECT * FROM payment_billing pbil WHERE pbil.billing_id = b.billing_id AND pbil.payment_id = '110207092536229353RSHA')
                            OR COALESCE(b.NETTO_CHARGE, 0) -  COALESCE(b.DEBITUR_CHARGE, 0) = 0)
                )
                UNION (
                    SELECT 'Transaksi Tindakan' ket,r.REGISTRY_ID,sg.S_GROUP_NM, ms.MEDICAL_SERVICE_NAME,
                        CASE WHEN visDoc.employee_nm is not null THEN
                            ' (' || visDoc.employee_nm || ')'
                        WHEN emp.employee_nm IS NOT NULL THEN
                            ' (' || emp.employee_nm || ')'
                        ELSE '' END MEDICAL_ACTOR,
                        sc.SERVICE_CLASS_NAME SC_DETIL,b.BILLING_DT startDate, null endDate,
                        b.SOURCE_CHARGE, b.DISCOUNT, b.NETTO_CHARGE, COALESCE(b.DEBITUR_CHARGE, 0) DEBITUR_CHARGE,
                        COALESCE(b.NETTO_CHARGE, 0) -  COALESCE(b.DEBITUR_CHARGE, 0)  IUR
                    FROM
                        registry r JOIN patient p ON r.MR_ID=p.MR_ID
                        JOIN billing b ON r.REGISTRY_ID=b.REGISTRY_ID
                        JOIN medical_service ms ON b.MEDICAL_SERVICE_ID=ms.MEDICAL_SERVICE_ID
                        JOIN service_class sc ON b.SERVICE_CLASS_ID=sc.SERVICE_CLASS_ID
                        LEFT JOIN service_group sg ON ms.S_GROUP_ID=sg.S_GROUP_ID
                        LEFT JOIN (
                            SELECT emp.employee_nm, vis.billing_id FROM visite vis LEFT JOIN employee emp ON vis.employee_id = emp.employee_id
                        ) visDoc ON b.billing_id = visDoc.billing_id
                        LEFT JOIN employee emp ON b.employee_charge = emp.employee_id
                    WHERE r.REGISTRY_ID='110207075802745101RSHA'
                       AND (sg.S_GROUP_CODE!='SKI' OR sg.S_GROUP_CODE IS NULL)
                        AND (EXISTS (SELECT * FROM payment_billing pbil WHERE pbil.billing_id = b.billing_id AND pbil.payment_id = '110207092536229353RSHA')
                            OR COALESCE(b.NETTO_CHARGE, 0) -  COALESCE(b.DEBITUR_CHARGE, 0) = 0)
                    ORDER BY sg.S_GROUP_NM, ms.MEDICAL_SERVICE_NAME
                )
            ORDER BY ket,startDate
            ) roomd ON r.REGISTRY_ID=roomd.REGISTRY_ID
        LEFT JOIN (
            SELECT payment.REGISTRY_ID,SUM(payment.PAYMENT_AMT) terbayar FROM payment
            WHERE payment.REGISTRY_ID='110207075802745101RSHA'
            GROUP BY payment.REGISTRY_ID
            ) pay ON pay.REGISTRY_ID=r.REGISTRY_ID
        LEFT JOIN (
            SELECT b.REGISTRY_ID,sum(b.NETTO_CHARGE) total,
                SUM(COALESCE(b.NETTO_CHARGE, 0) -  COALESCE(b.DEBITUR_CHARGE, 0)) total_patient, sum(coalesce(db.CHARGE,0)) jaminan
            FROM billing b left join debitur_billing db on b.BILLING_ID=db.BILLING_ID
            WHERE b.REGISTRY_ID='110207075802745101RSHA'
                AND (EXISTS (SELECT * FROM payment_billing pbil WHERE pbil.billing_id = b.billing_id AND pbil.payment_id = '110207092536229353RSHA')
                    OR COALESCE(b.NETTO_CHARGE, 0) -  COALESCE(b.DEBITUR_CHARGE, 0) = 0)
            GROUP BY REGISTRY_ID
        ) bil ON r.REGISTRY_ID=r.REGISTRY_ID
        WHERE r.REGISTRY_ID='110207075802745101RSHA'