aonestar.public

Parameters

Name	Type	Mode
request_data	jsonb	IN
booking_id	integer	IN (DEFAULT NULL)
booking_item	integer	IN (DEFAULT NULL)
register_id	integer	IN (DEFAULT NULL)
gen_inclusive	boolean	IN (DEFAULT false)
rateplan_id	integer	IN (DEFAULT NULL)
result_code	text	OUT
result_msg	text	OUT
msg_code	text	OUT

Definition

DECLARE 
    r record;
    request_id int;
    requests int[] = '{}';
    _charge_dept int;
    error     TEXT;
    v_state   TEXT;
    v_detail  TEXT;
    v_hint    TEXT;
    v_context TEXT;
    def_charge_to_book bool;
    request record;
    --inc record;
BEGIN 
    --RAISE NOTICE 'register_id: %, data: %', register_id, jsonb_pretty(request_data);
    result_code = '0'; msg_code = '00'; result_msg = 'Success';  

    -- Set default charge_to_booking to True for group booking
    IF register_id IS NOT NULL THEN 
        def_charge_to_book := (SELECT group_id IS NOT NULL FROM registration WHERE id = $4);
    ELSE  
        def_charge_to_book := (SELECT book_type='G' FROM booking WHERE id = $2);
    END IF;

    IF gen_inclusive THEN     
        DELETE FROM guest_request rq 
        WHERE rq.inclusive  
            AND rq.booking_id   IS NOT DISTINCT FROM $2
            AND rq.booking_item IS NOT DISTINCT FROM $3
            AND rq.register_id  IS NOT DISTINCT FROM $4;
    
        INSERT INTO guest_request (booking_id, booking_item, register_id, inclusion_id, multiplier, frequency, 
            charge_dept, charge_rate, qty, remark, charge_to_booking, inclusive)
        SELECT $2, $3, $4, inc.inclusion_id, inc.multiplier, inc.frequency, 
             inc.charge_dept, inc.charge_rate, inc.qty, 'Inclusive', def_charge_to_book, TRUE
        FROM sp_get_rate_inclusions(rateplan_id) AS inc;   
    END IF;

    -- Save additional requirements
    IF (request_data IS NOT NULL) THEN                     
        FOR r IN 
            SELECT *
            FROM jsonb_to_recordset(request_data) 
            AS (id int,
               inclusion_id int,
               charge_type smallint,
               frequency smallint, 
               charge_dept int,
               charge_rate t_money,
               qty int,
               start_date date,
               end_date date,
               request_time time,
               remark TEXT,
               charge_to_booking bool,
               inclusive bool
           )
           WHERE inclusive IS NULL OR NOT inclusive
         LOOP
            --_charge_dept = (SELECT charge_dept FROM inclusion WHERE id = r.inclusion_id);
            IF (r.id IS NULL) OR (r.id < 1) THEN
                INSERT INTO guest_request (booking_id, booking_item, register_id, inclusion_id, multiplier, frequency, --charge_dept, 
                                             charge_rate, qty, start_date, end_date, request_time, remark, charge_to_booking, inclusive)
                VALUES ($2, $3, $4, r.inclusion_id, r.charge_type, r.frequency, --_charge_dept, 
                        r.charge_rate, r.qty, r.start_date, r.end_date, r.request_time, r.remark, 
                        COALESCE(r.charge_to_booking, def_charge_to_book), 
                        COALESCE(r.inclusive, FALSE))
                RETURNING id INTO request_id;
            ELSE
                UPDATE guest_request rq
                SET inclusion_id      = r.inclusion_id,
                    multiplier        = r.charge_type,
                    frequency         = r.frequency,
                    --charge_dept       = _charge_dept,
                    charge_rate       = r.charge_rate,
                    qty               = r.qty,
                    start_date        = r.start_date,
                    end_date          = r.end_date,
                    request_time      = r.request_time,
                    remark            = r.remark,
                    charge_to_booking = coalesce(r.charge_to_booking, rq.charge_to_booking, def_charge_to_book),
                    inclusive         = COALESCE(r.inclusive, FALSE)
                WHERE rq.id = r.id;
            
                request_id = r.id;
            END IF;
        
            requests = requests || request_id;
        
         END LOOP;

         FOR request in (
            SELECT gr.id, inc.name 
            from guest_request gr left join inclusion inc on inc.id = gr.inclusion_id 
            WHERE (gr.booking_id IS NOT DISTINCT FROM $2) 
               AND (gr.booking_item IS NOT DISTINCT FROM $3)
               AND (gr.register_id  IS NOT DISTINCT FROM $4)
               AND gr.id <> ALL(requests)
               AND NOT gr.inclusive)
         LOOP 
            IF EXISTS(Select * from charge_schedule cs where cs.request_id = request.id and cs.posted) THEN
                PERFORM sp_raise_error('20102', 'Unable to delete %s, charges have been posted', request.name);
            END IF;
            DELETE FROM guest_request gr where gr.id = request.id;
         END LOOP; 

     END IF;    
END

sp_save_guest_requests

Parameters

Definition