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;
    default_charge_dept int;
    post_result json;
    posted_count int = 0;
    _folio_seq int;
    _folio_id int;
    _remark TEXT;
    posted_tran_id int;
    other_charge_msg text;
BEGIN 
    CALL sp_auto_post_rooms(post_date, user_name, status, msg);
    /*
    status              := 'success';
    default_charge_dept := fn_default('ROOM_CHARGE_DEPT', 0);
    post_date           := COALESCE(post_date, fn_system_date());

    /* Charge to booking folio */
    FOR chg IN     
        WITH bk AS (
            SELECT cs.booking_id, cs.register_id, cs.charge_folio_id, cs.charge_date, cs.charge_dept, dp.code AS dept_code, dp.name AS dept_name,  
                cs.room_id, rm.room_number, cs.split_room_charge, cs.charge_qty, cs.charge_amount,
                IIF(cs.charge_type = 'inclusion', 'I', 'R') AS post_flag
            FROM sp_get_charge_schedule(post_date => $1, posted => FALSE) cs
                LEFT JOIN room rm ON rm.id = cs.room_id
                LEFT JOIN department dp ON dp.id = cs.charge_dept
            WHERE cs.charge_to_booking 
        ), single_charge AS (
            SELECT bk.booking_id, bk.split_room_charge, bk.charge_folio_id, bk.charge_date, bk.charge_dept, bk.dept_code, bk.dept_name, bk.post_flag, 
                1 AS charge_qty, 
                SUM(bk.charge_qty * bk.charge_amount) AS charge_amount,
                COUNT(DISTINCT bk.room_id) AS room_count,
                STRING_AGG(DISTINCT room_number, ',' ORDER BY room_number) AS rooms,
                ARRAY_AGG(bk.register_id) AS register_ids
            FROM bk
            WHERE bk.split_room_charge = FALSE   
            GROUP BY 1,2,3,4,5,6,7,8
        ), split_charge AS (
            SELECT bk.booking_id, bk.split_room_charge, bk.charge_folio_id, bk.charge_date, bk.charge_dept, bk.dept_code, bk.dept_name, bk.post_flag,
                bk.charge_qty, bk.charge_amount,
                1 AS room_count,
                room_number AS rooms,
                ARRAY[bk.register_id] AS register_ids
            FROM bk
            WHERE bk.split_room_charge = TRUE 
        )
        SELECT * --charge_folio_id, charge_date, charge_dept, dept_code, dept_name, charge_qty, charge_amount, room_count, rooms, register_ids 
        FROM single_charge
        UNION ALL 
        SELECT * --charge_folio_id, charge_date, charge_dept, dept_code, dept_name, charge_qty, charge_amount, room_count, rooms, register_ids 
        FROM split_charge
    LOOP
        posted_tran_id = NULL;
        IF (chg.charge_qty > 0) AND (chg.charge_amount <> 0) THEN 
            _folio_id  := coalesce(chg.charge_folio_id, sp_create_booking_folio(chg.booking_id));
            _folio_seq := fn_get_target_folio_seq(_folio_id, chg.charge_dept, true);
            _remark    := iif(chg.room_count = 1, 'Room '||chg.rooms, chg.room_count||' rooms');   
           -- RAISE NOTICE 'Post room> %/%, RSV# %, %', _folio_id, _folio_seq, chg.booking_id, _remark;
           -- PERFORM sp_log_debug('sp_auto_post_room_charges', format('folio: %L/%L, book: %', _folio_id, _folio_seq, chg.booking_id, _remark));
           
            post_result := sp_post_transaction(_folio_id, chg.dept_code, chg.charge_amount, chg.charge_qty, _folio_seq, NULL, user_name, 
                                              'AUTO', _remark, chg.dept_name, chg.charge_date, post_flag => chg.post_flag);
            posted_count := posted_count + 1;    
            IF post_result->>'result_code' = '4' THEN
                RAISE EXCEPTION SQLSTATE '90003' USING MESSAGE = post_result->>'result_msg', DETAIL = 'folio_id='||COALESCE(_folio_id::TEXT,'NULL')||', '||chg::text;
            END IF;
            posted_tran_id := (post_result->'result_data'->>'id')::int;
        END IF;
    
        UPDATE registration_rates rr 
        SET posted = TRUE, 
            tran_id = posted_tran_id 
        WHERE rr.register_id = ANY(chg.register_ids) AND rr.charge_date = $1; 
    END LOOP;

    /* Charge to guest folio */
    FOR chg IN 
        SELECT cs.register_id, cs.charge_folio_id, rm.room_number, cs.charge_date, cs.charge_dept, dp.code AS dept_code, dp.name AS dept_name, 
            cs.charge_dept, cs.charge_amount, cs.charge_qty, 
            IIF(cs.charge_type = 'inclusion', 'I', 'R') AS post_flag
        FROM sp_get_charge_schedule(post_date => $1, posted => false) cs
            LEFT JOIN room rm ON rm.id = cs.room_id
            LEFT JOIN department dp ON dp.id = cs.charge_dept
        WHERE cs.charge_to_booking = FALSE
    LOOP
        posted_tran_id := NULL;
        IF (chg.charge_qty > 0) AND (chg.charge_amount <> 0) THEN 
            _folio_seq  := fn_get_target_folio_seq(chg.charge_folio_id, chg.charge_dept, true);
            _remark     := 'Room '||chg.room_number;
            post_result := sp_post_transaction(chg.charge_folio_id, chg.dept_code, chg.charge_amount, chg.charge_qty, _folio_seq, NULL, user_name, 
                                              'AUTO', _remark, chg.dept_name, chg.charge_date, post_flag => chg.post_flag);
            posted_count = posted_count + 1;
            IF post_result->>'result_code' = '4' THEN
                RAISE EXCEPTION SQLSTATE '90003' USING MESSAGE = post_result->>'result_msg';
            END IF;
            posted_tran_id := (post_result->'result_data'->>'id')::int;
        END IF;
       
        UPDATE registration_rates rr 
        SET posted = TRUE, 
            tran_id = posted_tran_id 
        WHERE rr.register_id = chg.register_id AND rr.charge_date = $1;  

        UPDATE registration rg SET room_posted = TRUE WHERE rg.id = chg.register_id;
    END LOOP;

    SELECT rc.status, rc.msg 
    FROM sp_auto_post_requirement_charges($1, user_name) rc
    INTO status, other_charge_msg;

    msg := posted_count||' rooms posted, '||other_charge_msg;   
    */
END;