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_id int;
    _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());

    /* Charge to booking folio (group or members that charge to booking) */
    FOR chg IN 
        SELECT COALESCE(cs.booking_id, rg.booking_id) AS booking_id, b.folio_id, rm.room_number, cs.charge_date, dp.id AS charge_dept, 
            dp.code AS dept_code, cs.charge_amount, sum(cs.charge_qty)::int AS charge_qty,array_agg(cs.id) AS charge_ids
        FROM charge_schedule cs 
            LEFT JOIN guest_request rq ON rq.id = cs.request_id 
            LEFT JOIN inclusion inc ON inc.id = rq.inclusion_id 
            LEFT JOIN registration rg ON rg.id = cs.register_id 
            LEFT JOIN booking b ON b.id = COALESCE(cs.booking_id, rg.booking_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 = $1 AND cs.charge_type = 'requirement' AND NOT cs.posted 
             AND (b.book_type = 'G' OR cs.charge_to_booking) --AND cs.booking_id IS NOT NULL
             AND (rg.status = 'I' or b.status in ('R','C','I'))
        GROUP BY 1,2,3,4,5,6,7 
    LOOP
        posted_tran_id = NULL;
        IF (chg.charge_qty > 0) AND (chg.charge_amount <> 0) THEN 
            _folio_id  := coalesce(chg.folio_id, sp_create_booking_folio(chg.booking_id));
            _folio_seq := fn_get_target_folio_seq(_folio_id, chg.charge_dept, true);
            _remark    := 'Room '||chg.room_number; --iif(chg.room_count = 1, 'Room '||chg.rooms, chg.room_count||' rooms');   
           
            post_result := sp_post_transaction(_folio_id, chg.dept_code, chg.charge_amount, chg.charge_qty, _folio_seq, NULL, user_name, 
                                              'AUTO', _remark, null, chg.charge_date, post_flag => 'A');
            posted_count := posted_count + 1;    
            IF post_result->>'result_code' = '4' THEN
                --msg = post_result->>'result_msg'||' ('||chg.charge_ids::TEXT||')';
                --RETURN;
                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 charge_schedule cs 
        SET posted = TRUE, 
            tran_id = posted_tran_id
        WHERE cs.id = ANY(chg.charge_ids); 
    END LOOP;

    /* Charge to 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 
            LEFT JOIN guest_request rq ON rq.id = cs.request_id 
            LEFT JOIN inclusion inc ON inc.id = rq.inclusion_id 
            LEFT JOIN registration rg ON rg.id = cs.register_id 
            LEFT JOIN department dp ON dp.id = COALESCE(cs.charge_dept, rq.charge_dept, inc.charge_dept)
        WHERE cs.charge_date = $1 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_id  := chg.folio_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');   
           
            post_result := sp_post_transaction(_folio_id, chg.dept_code, chg.charge_amount, chg.charge_qty, _folio_seq, NULL, user_name, 
                                              'AUTO', null, null, chg.charge_date, post_flag => 'A');
            posted_count := posted_count + 1;    
            IF post_result->>'result_code' = '4' THEN
                --msg = post_result->>'result_msg'||' ('||chg.charge_id::TEXT||')';
                --RETURN;           
                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 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