aonestar.public

Parameters

Name	Type	Mode
status	text	INOUT
msg	text	INOUT
details	jsonb	INOUT

Definition

BEGIN
    IF NOT fn_sys_param('CASHIER', 'ALWAYS_CLOSE_CITY_LEDGER', false) THEN
        status := 'success';
        msg    := 'City ledger check is disabled';
        RETURN;
    END IF;

WITH open_city_ledger AS (
        SELECT fi.item_id, fi.folio_id, fi.folio_seq, f.folio_type,
               COALESCE(rm.room_number,
                        CASE f.folio_type WHEN 'C' THEN 'Cash Sale'
                                          WHEN 'G' THEN 'Group'
                                          WHEN 'B' THEN 'Deposit' END) AS room_number,
               COALESCE(g.full_name, gb.full_name, cs."name")          AS name,
               SUM(-t.acc_amount)                                      AS city_ledger_amount,
               fi.balance
        FROM transactions t
            INNER JOIN folio_items      fi ON fi.item_id = t.folio_item
            INNER JOIN folio            f  ON f.id       = fi.folio_id
            INNER JOIN department       d  ON d.id       = t.dept_id
            INNER JOIN department_group dg ON dg.id      = d.group_id
            LEFT JOIN registration rg ON rg.id = f.register_id
            LEFT JOIN room         rm ON rm.id = rg.room_id
            LEFT JOIN guest        g  ON g.id  = rg.guest_id
            LEFT JOIN booking      bk ON bk.id = f.booking_id
            LEFT JOIN guest        gb ON gb.id = bk.guest_id
            LEFT JOIN cash_sale    cs ON cs.folio_id = f.id
        WHERE t.active
          AND d.post_type = 'S'
          AND dg.category = 'city'
          AND NOT fi.closed
        GROUP BY fi.item_id, f.folio_type, rm.room_number,
                 g.full_name, gb.full_name, cs."name"
        ORDER BY room_number, folio_id, folio_seq
    )
    SELECT jsonb_agg(open_city_ledger) FROM open_city_ledger INTO details;

status := iif(details IS NULL, 'success', 'error');
    IF details IS NOT NULL THEN
        RAISE EXCEPTION SQLSTATE '90002'
            USING MESSAGE = 'There are city ledger folios not closed', DETAIL = details;
    END IF;
END

sp_endday_check_city_ledger

Description

Parameters

Definition