aonestar.public

Parameters

Name	Type	Mode
post_date	date	IN (DEFAULT NULL)
user_name	text	IN (DEFAULT NULL)
status	text	OUT
msg	text	OUT

Definition

DECLARE
    chg record;
    _folio_seq int;
    _remark TEXT;
    post_result json;
    posted_count int = 0;
    posted_tran_id int;
BEGIN
    post_date := coalesce(post_date, fn_system_date());

/* ---- (1) Per-booking requirement → group folio "ครั้งเดียว" ----
       per-booking requirement ของ group ผูกที่ group booking เสมอ (charge_schedule.register_id IS NULL),
       ไม่เคยผูกราย registration → post ครั้งเดียวลง group folio (รวมหลายรายการ dept เดียวกันด้วย SUM) */
    FOR chg IN
        SELECT b.id AS booking_id, b.folio_id AS target_folio_id, cs.charge_date,
               dp.id AS charge_dept, dp.code AS dept_code,
               SUM(cs.total_amount)::t_money AS charge_amount, 1 AS charge_qty,
               ARRAY_AGG(cs.id) AS charge_ids
        FROM charge_schedule cs
            JOIN booking b            ON b.id = cs.booking_id
            JOIN folio gf             ON gf.id = b.folio_id AND gf.status = 'A' AND gf.folio_type = 'G'
            LEFT JOIN guest_request rq ON rq.id = cs.request_id
            LEFT JOIN inclusion inc    ON inc.id = rq.inclusion_id
            LEFT JOIN department dp    ON dp.id = COALESCE(cs.charge_dept, rq.charge_dept, inc.charge_dept)
        WHERE cs.charge_date = post_date AND cs.charge_type = 'requirement' AND NOT cs.posted
            AND cs.register_id IS NULL AND cs.booking_id IS NOT NULL
            AND b.book_type = 'G' AND b.status IN ('R','C','I')
        GROUP BY b.id, b.folio_id, cs.charge_date, dp.id, dp.code
    LOOP
        posted_tran_id := NULL;
        IF (chg.charge_qty > 0) AND (chg.charge_amount <> 0) THEN
            _folio_seq := fn_get_target_folio_seq(chg.target_folio_id, chg.charge_dept, true);

post_result := sp_post_transaction(chg.target_folio_id, chg.charge_dept, chg.charge_amount, chg.charge_qty,
                                              folio_seq => _folio_seq, user_name => user_name, reference => 'AUTO',
                                              remark => null, charge_date => chg.charge_date, post_flag => 'A');
            IF post_result->>'result_code' = '4' THEN
                RAISE EXCEPTION SQLSTATE '90003'
                    USING MESSAGE = post_result->>'result_msg',
                          DETAIL  = 'folio_id='||COALESCE(chg.target_folio_id::text,'null')||', '||chg::text;
            END IF;
            posted_tran_id := (post_result->'result_data'->>'id')::int;
            posted_count   := posted_count + 1;
        END IF;

UPDATE charge_schedule cs SET posted = TRUE, tran_id = posted_tran_id
        WHERE cs.id = ANY(chg.charge_ids);
    END LOOP;

/* ---- (2) Charge-to-booking requirement (per-room/per-pax) → group folio (split-aware) ---- */
    FOR chg IN
        WITH req AS (
            SELECT cs.id AS charge_id, b.folio_id AS target_folio_id, gf.split_room_charge,
                   rm.room_number::text AS room_number, rg.room_id, cs.charge_date,
                   dp.id AS charge_dept, dp.code AS dept_code,
                   cs.charge_amount, cs.charge_qty, cs.total_amount
            FROM charge_schedule cs
                JOIN registration rg ON rg.id = cs.register_id
                JOIN booking b       ON b.id = rg.booking_id
                JOIN folio gf        ON gf.id = b.folio_id AND gf.status = 'A' AND gf.folio_type = 'G'
                LEFT JOIN guest_request rq ON rq.id = cs.request_id
                LEFT JOIN inclusion inc    ON inc.id = rq.inclusion_id
                LEFT JOIN department dp    ON dp.id = COALESCE(cs.charge_dept, rq.charge_dept, inc.charge_dept)
                LEFT JOIN room rm          ON rm.id = rg.room_id
            WHERE cs.charge_date = post_date AND cs.charge_type = 'requirement' AND NOT cs.posted
                AND cs.register_id IS NOT NULL AND cs.charge_to_booking AND rg.status = 'I'
        ), single_req AS (
            SELECT target_folio_id, charge_date, charge_dept, dept_code,
                   1 AS charge_qty, SUM(total_amount)::t_money AS charge_amount,
                   COUNT(DISTINCT room_id)::int AS room_count,
                   STRING_AGG(DISTINCT room_number, ',' ORDER BY room_number) AS rooms,
                   ARRAY_AGG(charge_id) AS charge_ids
            FROM req WHERE NOT split_room_charge AND charge_dept IS NOT NULL
            GROUP BY target_folio_id, charge_date, charge_dept, dept_code
        ), split_req AS (
            SELECT target_folio_id, charge_date, charge_dept, dept_code,
                   charge_qty, charge_amount,
                   1 AS room_count, room_number AS rooms,
                   ARRAY[charge_id] AS charge_ids
            FROM req WHERE split_room_charge AND charge_dept IS NOT NULL
        )
        SELECT * FROM single_req
        UNION ALL
        SELECT * FROM split_req
    LOOP
        posted_tran_id := NULL;
        IF (chg.charge_qty > 0) AND (chg.charge_amount <> 0) THEN
            _folio_seq := fn_get_target_folio_seq(chg.target_folio_id, chg.charge_dept, true);
            _remark    := IIF(chg.room_count = 1, 'Room '||chg.rooms, chg.room_count||' rooms');

post_result := sp_post_transaction(chg.target_folio_id, chg.charge_dept, chg.charge_amount, chg.charge_qty,
                                              folio_seq => _folio_seq, user_name => user_name, reference => 'AUTO',
                                              remark => _remark, charge_date => chg.charge_date, post_flag => 'A');
            IF post_result->>'result_code' = '4' THEN
                RAISE EXCEPTION SQLSTATE '90003'
                    USING MESSAGE = post_result->>'result_msg',
                          DETAIL  = 'folio_id='||COALESCE(chg.target_folio_id::text,'null')||', '||chg::text;
            END IF;
            posted_tran_id := (post_result->'result_data'->>'id')::int;
            posted_count   := posted_count + 1;
        END IF;

UPDATE charge_schedule cs SET posted = TRUE, tran_id = posted_tran_id
        WHERE cs.id = ANY(chg.charge_ids);
    END LOOP;

/* ---- (3) Own-account requirement (ราย registration) → own guest folio ---- */
    FOR chg IN
        SELECT cs.id AS charge_id, cs.register_id, rg.folio_id, cs.charge_date,
               dp.id AS charge_dept, dp.code AS dept_code, cs.charge_amount, cs.charge_qty
        FROM charge_schedule cs
            JOIN registration rg ON rg.id = cs.register_id
            LEFT JOIN guest_request rq ON rq.id = cs.request_id
            LEFT JOIN inclusion inc    ON inc.id = rq.inclusion_id
            LEFT JOIN department dp    ON dp.id = COALESCE(cs.charge_dept, rq.charge_dept, inc.charge_dept)
        WHERE cs.charge_date = post_date AND cs.charge_type = 'requirement' AND NOT cs.posted
            AND cs.register_id IS NOT NULL AND NOT cs.charge_to_booking AND rg.status = 'I'
    LOOP
        posted_tran_id := NULL;
        IF (chg.charge_qty > 0) AND (chg.charge_amount <> 0) THEN
            _folio_seq := fn_get_target_folio_seq(chg.folio_id, chg.charge_dept, true);

post_result := sp_post_transaction(chg.folio_id, chg.charge_dept, chg.charge_amount, chg.charge_qty,
                                              folio_seq => _folio_seq, user_name => user_name, reference => 'AUTO',
                                              remark => null, charge_date => chg.charge_date, post_flag => 'A');
            IF post_result->>'result_code' = '4' THEN
                RAISE EXCEPTION SQLSTATE '90003'
                    USING MESSAGE = post_result->>'result_msg',
                          DETAIL  = 'folio_id='||COALESCE(chg.folio_id::text,'null')||', '||chg::text;
            END IF;
            posted_tran_id := (post_result->'result_data'->>'id')::int;
            posted_count   := posted_count + 1;
        END IF;

UPDATE charge_schedule cs SET posted = TRUE, tran_id = posted_tran_id
        WHERE cs.id = chg.charge_id;
    END LOOP;

status := 'success';
    msg    := posted_count||' requirements posted';
END;

sp_auto_post_requirement_charges

Parameters

Definition