-- H11.0-P10.4 — Inpatient Daily Revenue Automation
-- Purpose: One set-based read-only query for all currently admitted inpatients.
-- Oracle writes: NONE.
-- Direct legacy procedure invocation: NONE.
-- Important LOS rule:
--   * LOS is calculated for every current inpatient from the first VISIT_START_DATE in the
--     latest uninterrupted sequence of RCP_VISIT rows where CLASSCODE=1.
--   * Closing one inpatient visit and opening another inpatient visit remains one continuous stay
--     unless any non-inpatient CLASSCODE visit occurs between them.
--   * The RCP_VISIT history scan is restricted to currently admitted patient keys for performance.
-- Runtime bind: :branch_id.
--
-- This query was derived by decomposing the legacy inpatient revenue procedure
-- into its underlying revenue sources. It intentionally does not require a visit
-- end date and therefore supports patients who are still admitted and in a room.

WITH active_base AS (
    SELECT /*+ MATERIALIZE */ DISTINCT
        v.hosp_id,
        v.patientid,
        v.visit_id,
        v.patfinanaccount,
        v.pkgid,
        v.visit_start_date,
        ee.financlassid,
        NVL(v.multi_finan_flag, 0) AS multi_finan_flag,
        fc.compenrname AS financial_class,
        emp.emp_en_data AS attending_doctor,
        nat.nat_name_en AS nationality,
        mi.completepatname_en AS patient_name,
        TO_CHAR(v.visit_start_date, 'HH24:MI') AS admission_time,
        TRUNC(v.visit_start_date) AS admission_date,
        bed.place_en_name AS bed,
        room.place_en_name AS room,
        hrc.unit_en_name AS unit_name,
        hrc.unit_id,
        hrc.nurs_cat,
        nut.desc_en AS nursing_unit_type
    FROM rcp_bed_trans bt
    JOIN stp_beds sb
      ON sb.bedid = bt.bedid
    JOIN stp_places bed
      ON bed.place_id1 = sb.bedid
     AND bed.hosp_id = bt.hosp_id
    JOIN stp_places room
      ON room.place_id1 = bed.parent_place_id
     AND room.hosp_id = bed.hosp_id
    JOIN rcp_entryexit ee
      ON ee.patientid = bt.patientid
     AND ee.branchid = bt.hosp_id
     AND ee.visit_id = bt.visit_id
    JOIN rcp_visit v
      ON v.patientid = ee.patientid
     AND v.hosp_id = ee.branchid
     AND v.visit_id = ee.visit_id
    JOIN rcp_masterindex mi
      ON mi.patientid = v.patientid
     AND mi.hosp_id = v.hosp_id
    JOIN reg_financial_class fc
      ON fc.hosp_id = ee.branchid
     AND fc.financlassid = ee.financlassid
    LEFT JOIN prs_emp emp
      ON emp.branchid = ee.branchid
     AND emp.emp_id = ee.responsibledr
    LEFT JOIN stp_hrc hrc
      ON hrc.unit_id = room.unit_id
     AND hrc.hosp_id = room.hosp_id
    LEFT JOIN stp_nationalities nat
      ON nat.nat_code = mi.nat_code
    LEFT JOIN stp_nursingunit_type nut
      ON nut.code = hrc.nurs_cat
    WHERE bt.hosp_id = :branch_id
      AND TRUNC(bt.startdate) <= TRUNC(SYSDATE)
      AND bt.enddate IS NULL
      AND NVL(ee.baby_normal_bed, 1) = 1
),
active_patient_keys AS (
    /* Performance guard: RCP_VISIT history is scanned only for patients who are currently in a room. */
    SELECT /*+ MATERIALIZE */ DISTINCT hosp_id, patientid
    FROM active_base
),
visit_sequence AS (
    /* Consecutive visit order for active patients only. Any non-inpatient CLASSCODE breaks the chain. */
    SELECT
        v.hosp_id,
        v.patientid,
        v.visit_id,
        v.classcode,
        v.visit_start_date,
        v.visit_end_date,
        LAG(v.classcode) OVER (
            PARTITION BY v.hosp_id, v.patientid
            ORDER BY v.visit_start_date, v.visit_id
        ) AS previous_classcode
    FROM rcp_visit v
    JOIN active_patient_keys k
      ON k.hosp_id = v.hosp_id
     AND k.patientid = v.patientid
    WHERE v.visit_start_date <= SYSDATE
),
visit_runs AS (
    /* Start a new inpatient run only when CLASSCODE=1 follows a non-inpatient visit (or no prior visit). */
    SELECT
        s.hosp_id,
        s.patientid,
        s.visit_id,
        s.classcode,
        s.visit_start_date,
        s.visit_end_date,
        SUM(
            CASE
                WHEN s.classcode = 1 AND NVL(s.previous_classcode, -1) <> 1 THEN 1
                ELSE 0
            END
        ) OVER (
            PARTITION BY s.hosp_id, s.patientid
            ORDER BY s.visit_start_date, s.visit_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS inpatient_run_no
    FROM visit_sequence s
),
continuous_inpatient_los AS (
    /* For the current inpatient visit, return the first start date in its latest uninterrupted CLASSCODE=1 run. */
    SELECT
        a.hosp_id,
        a.patientid,
        a.visit_id,
        MIN(r.visit_start_date) AS continuous_los_start_date
    FROM active_base a
    JOIN visit_runs current_visit
      ON current_visit.hosp_id = a.hosp_id
     AND current_visit.patientid = a.patientid
     AND current_visit.visit_id = a.visit_id
     AND current_visit.classcode = 1
    JOIN visit_runs r
      ON r.hosp_id = current_visit.hosp_id
     AND r.patientid = current_visit.patientid
     AND r.inpatient_run_no = current_visit.inpatient_run_no
     AND r.classcode = 1
    GROUP BY a.hosp_id, a.patientid, a.visit_id
),
unit_counts AS (
    SELECT u.unit_id,
           f_get_no_of_bed_with_unit(u.unit_id) AS bed_counts
    FROM (
        SELECT DISTINCT unit_id
        FROM active_base
        WHERE unit_id IS NOT NULL
    ) u
),
active_patients AS (
    SELECT
        a.*,
        NVL(c.continuous_los_start_date, a.visit_start_date) AS los_start_date,
        TRUNC(SYSDATE) - TRUNC(NVL(c.continuous_los_start_date, a.visit_start_date)) + 1 AS los,
        TRUNC(SYSDATE) - TRUNC(NVL(c.continuous_los_start_date, a.visit_start_date)) + 1 AS total_los,
        uc.bed_counts
    FROM active_base a
    LEFT JOIN continuous_inpatient_los c
      ON c.hosp_id = a.hosp_id
     AND c.patientid = a.patientid
     AND c.visit_id = a.visit_id
    LEFT JOIN unit_counts uc
      ON uc.unit_id = a.unit_id
),
revenue_lines AS (
    /* 1) Services - single finance / patient share */
    SELECT
        ap.patfinanaccount,
        CASE
            WHEN ap.multi_finan_flag = 1 THEN 0
            WHEN NVL(t.pkg_treatment, 0) > 0
                THEN NVL(t.priceover, 0) - NVL(t.patpriceover, 0)
            ELSE NVL(t.srvprice, 0) - NVL(t.patsrvprice, 0)
        END AS credit_value,
        CASE
            WHEN NVL(t.pkg_treatment, 0) > 0 THEN NVL(t.patpriceover, 0)
            ELSE NVL(t.patsrvprice, 0)
        END - NVL(t.bill_discount_val, 0) AS cash_value
    FROM active_patients ap
    JOIN pacc_pat_tran t
      ON t.patfinanaccount = ap.patfinanaccount
     AND t.hosp_id = ap.hosp_id

    UNION ALL

    /* 2) Services - multi finance company shares */
    SELECT
        ap.patfinanaccount,
        NVL(mf.finanshare, 0) AS credit_value,
        0 AS cash_value
    FROM active_patients ap
    JOIN pacc_pat_tran t
      ON t.patfinanaccount = ap.patfinanaccount
     AND t.hosp_id = ap.hosp_id
    JOIN pacc_pat_tran_multifinan mf
      ON mf.patfinanacc = t.patfinanaccount
     AND mf.branch_id = t.hosp_id
     AND mf.donservserial = t.donesrvserial
    WHERE ap.multi_finan_flag = 1

    UNION ALL

    /* 3) Pharmacy / store items - single finance and patient share */
    SELECT
        ap.patfinanaccount,
        (CASE WHEN st.storetranscode = 4 THEN -1 ELSE 1 END) *
        CASE
            /* Legacy parity: multi-finance store rows keep FINANSHARE1 in the base line,
               while additional financiers are added from MMSTORETRANSITEMS_MULTIFINAN. */
            WHEN ap.multi_finan_flag = 1 THEN NVL(i.finanshare1, 0)
            WHEN NVL(i.pkg_treatment, 0) > 0 THEN NVL(i.tot_finan_share_over, 0)
            ELSE NVL(i.tot_finan_share, 0)
        END AS credit_value,
        (CASE WHEN st.storetranscode = 4 THEN -1 ELSE 1 END) *
        CASE
            WHEN NVL(i.pkg_treatment, 0) > 0 THEN NVL(i.tot_pat_share_over, 0)
            ELSE NVL(i.tot_pat_share, 0)
        END AS cash_value
    FROM active_patients ap
    JOIN mmstoretransactions st
      ON st.patfinanaccount = ap.patfinanaccount
     AND st.branchid = ap.hosp_id
    JOIN mmstoretransitems i
      ON i.branchid = st.branchid
     AND i.transserial = st.transserial
     AND i.storetranscode = st.storetranscode
    WHERE st.storetranscode IN (7, 4)

    UNION ALL

    /* 4) Pharmacy / store items - multi finance company shares */
    SELECT
        ap.patfinanaccount,
        (CASE WHEN mf.storetranscode = 4 THEN -1 ELSE 1 END) * NVL(mf.finanshare, 0) AS credit_value,
        0 AS cash_value
    FROM active_patients ap
    JOIN mmstoretransactions st
      ON st.patfinanaccount = ap.patfinanaccount
     AND st.branchid = ap.hosp_id
    JOIN mmstoretransitems_multifinan mf
      ON mf.branch_id = st.branchid
     AND mf.storetranscode = st.storetranscode
     AND mf.transserial = st.transserial
     AND mf.branch_id = ap.hosp_id
    WHERE ap.multi_finan_flag = 1
      AND st.storetranscode IN (7, 4)

    UNION ALL

    /* 5) Residence, nursing, doctor follow-up and sterilization - single finance
       Legacy SP_INPATIENT_REVENUE_DATA references IN_OUT_PKG inside the residence row query.
       The flag belongs to the residence transaction row (r), not REG_PACKAGES. */
    SELECT
        ap.patfinanaccount,
        CASE
            WHEN ap.multi_finan_flag = 1 THEN 0
            ELSE
                (
                    CASE
                        WHEN NVL(ap.pkgid, 0) > 0 AND NVL(r.in_out_pkg, 0) = 1
                            THEN NVL(r.priceover, 0) + NVL(r.medcare_over, 0) + NVL(r.followup_value_over, 0) + NVL(r.setrilization_fees_over, 0)
                        ELSE NVL(r.totalval, 0) + NVL(r.medcare, 0) + NVL(r.followup_value, 0) + NVL(r.setrilization_fees_value, 0)
                    END
                    -
                    CASE
                        WHEN NVL(ap.pkgid, 0) > 0 AND NVL(r.in_out_pkg, 0) = 1
                            THEN NVL(r.patpriceover, 0) + NVL(r.patmedcare_over, 0) + NVL(r.patfollowup_value_over, 0) + NVL(r.patsetrilization_fees_over, 0)
                        ELSE NVL(r.patval, 0) + NVL(r.patmedcare, 0) + NVL(r.patfollowup_value, 0) + NVL(r.patsetrilization_fees_value, 0)
                    END
                )
        END AS credit_value,
        CASE
            WHEN ap.multi_finan_flag = 1 THEN
                /* Legacy parity: on multi-finance residence rows, the base row carries the
                   patient's accumulated residence share and residence difference without
                   subtracting the single-finance discount columns. */
                (
                    CASE
                        WHEN NVL(ap.pkgid, 0) > 0 AND NVL(r.in_out_pkg, 0) = 1
                            THEN NVL(r.patpriceover, 0) + NVL(r.patmedcare_over, 0) + NVL(r.patfollowup_value_over, 0) + NVL(r.patsetrilization_fees_over, 0)
                        ELSE NVL(r.patval, 0) + NVL(r.patmedcare, 0) + NVL(r.patfollowup_value, 0) + NVL(r.patsetrilization_fees_value, 0)
                    END
                    + CASE WHEN NVL(ap.pkgid, 0) > 0 AND NVL(r.in_out_pkg, 0) = 1 THEN NVL(r.pat_res_diff_value_over, 0) ELSE NVL(r.pat_res_diff_value, 0) END
                )
            ELSE
                (
                    CASE
                        WHEN NVL(ap.pkgid, 0) > 0 AND NVL(r.in_out_pkg, 0) = 1
                            THEN NVL(r.patpriceover, 0) + NVL(r.patmedcare_over, 0) + NVL(r.patfollowup_value_over, 0) + NVL(r.patsetrilization_fees_over, 0)
                        ELSE NVL(r.patval, 0) + NVL(r.patmedcare, 0) + NVL(r.patfollowup_value, 0) + NVL(r.patsetrilization_fees_value, 0)
                    END
                    + CASE WHEN NVL(ap.pkgid, 0) > 0 AND NVL(r.in_out_pkg, 0) = 1 THEN NVL(r.pat_res_diff_value_over, 0) ELSE NVL(r.pat_res_diff_value, 0) END
                    - NVL(r.bill_discount_val, 0)
                    - NVL(r.bill_discount_val_res_diff, 0)
                    - NVL(r.bill_discount_val_medcare_cash, 0)
                    - NVL(r.bill_discount_val_foll_cash, 0)
                    - NVL(r.bill_discount_val_fees_cash, 0)
                )
        END AS cash_value
    FROM active_patients ap
    JOIN pacc_pat_res_tran r
      ON r.patfinanaccount = ap.patfinanaccount
     AND r.hosp_id = ap.hosp_id
    WHERE NVL(r.totalval, 0) >= 0

    UNION ALL

    /* 6) Residence - multi finance company shares */
    SELECT
        ap.patfinanaccount,
        NVL(mf.finanshare, 0)
        + NVL(mf.foll_finanshare, 0)
        + NVL(mf.care_finanshare, 0)
        + NVL(mf.ster_finanshare, 0) AS credit_value,
        0 AS cash_value
    FROM active_patients ap
    JOIN pacc_pat_res_tran_multifinan mf
      ON mf.patfinanacc = ap.patfinanaccount
     AND mf.branch_id = ap.hosp_id
    WHERE ap.multi_finan_flag = 1

    UNION ALL

    /* 7) Operations - single finance / patient share */
    SELECT
        ap.patfinanaccount,
        CASE
            WHEN ap.multi_finan_flag = 1 THEN 0
            WHEN NVL(o.pkg_in_out, 0) = 1 THEN 0
            ELSE NVL(o.totalprice, 0) - NVL(o.patprice, 0)
        END AS credit_value,
        CASE
            WHEN ap.multi_finan_flag = 1 THEN NVL(o.patprice, 0)
            WHEN NVL(o.pkg_in_out, 0) = 1 THEN 0
            ELSE NVL(o.patprice, 0)
        END - NVL(o.bill_discount_val, 0) AS cash_value
    FROM active_patients ap
    JOIN pacc_pat_oper_items_tran o
      ON o.patfinanaccount = ap.patfinanaccount
     AND o.hosp_id = ap.hosp_id

    UNION ALL

    /* 8) Operations - multi finance company shares */
    SELECT
        ap.patfinanaccount,
        NVL(mf.finanshare, 0) AS credit_value,
        0 AS cash_value
    FROM active_patients ap
    JOIN pacc_pat_oper_items_multifinan mf
      ON mf.patfinanacc = ap.patfinanaccount
     AND mf.branch_id = ap.hosp_id
    JOIN pacc_pat_oper_items_tran o
      ON o.patfinanaccount = mf.patfinanacc
     AND o.hosp_id = ap.hosp_id
     AND o.billitemid = mf.billitemid
     AND o.service_id = mf.service_id
     AND o.ser = mf.ser
    WHERE ap.multi_finan_flag = 1

    UNION ALL

    /* 9) Packages - single finance */
    SELECT
        ap.patfinanaccount,
        NVL(p.pkg_price, 0) - NVL(p.pat_share, 0) AS credit_value,
        NVL(p.pat_share, 0) - NVL(p.bill_discount_val, 0) AS cash_value
    FROM active_patients ap
    JOIN pacc_pat_packages p
      ON p.pat_finanacc = ap.patfinanaccount

    UNION ALL

    /* 10) Packages - multi finance company shares */
    SELECT
        ap.patfinanaccount,
        NVL(mf.finanshare, 0) AS credit_value,
        0 AS cash_value
    FROM active_patients ap
    JOIN pacc_pat_packages_multifinan mf
      ON mf.patfinanacc = ap.patfinanaccount
     AND mf.branch_id = ap.hosp_id
    WHERE ap.multi_finan_flag = 1
),
revenue_totals AS (
    SELECT
        patfinanaccount,
        SUM(NVL(cash_value, 0)) AS patient_share_total,
        SUM(NVL(credit_value, 0)) AS company_share_total
    FROM revenue_lines
    GROUP BY patfinanaccount
)
SELECT
    ap.attending_doctor,
    ap.financlassid,
    ap.financial_class,
    ap.los,
    ap.nationality,
    ap.patient_name,
    ap.patientid,
    ap.patfinanaccount,
    ap.admission_time,
    ap.admission_date,
    ap.bed,
    ap.room,
    ap.los_start_date,
    ap.total_los,
    ap.unit_name,
    ap.unit_id,
    ap.nurs_cat,
    ap.nursing_unit_type,
    ap.bed_counts,
    NVL(rt.patient_share_total, 0) AS patient_share_total,
    NVL(rt.company_share_total, 0) AS company_share_total
FROM active_patients ap
LEFT JOIN revenue_totals rt
  ON rt.patfinanaccount = ap.patfinanaccount
ORDER BY ap.nurs_cat, ap.unit_id, ap.room, ap.bed, ap.patient_name;
