aonestar.public

Parameters

Name	Type	Mode

Definition

DECLARE 
	_arrival date;
	_departure date;
	_adult int;
	_child int;
	_infant int;
	_start_date date;  
	_end_date date;
	_charge_date date;
 	_charge_qty int;
 	_charge_amount t_money;
 	system_date date = fn_system_date();
BEGIN
	PERFORM sp_log_debug('sp_tr_registration_request_gen_charges', NEW::text);
	DELETE FROM charge_schedule WHERE register_id = NEW.register_id AND NOT posted;

SELECT r.arrival, --iif(r.arrival < system_date, system_date, r.arrival), 
		r.departure, r.adult + r.extra_adult, r.child + r.extra_child, r.infant + r.extra_infant
    FROM registration R 
    WHERE r.id = NEW.register_id
    INTO _arrival, _departure, _adult, _child, _infant;

_start_date = CASE frequency 
	                 WHEN 1 THEN iif(_arrival = _departure, _arrival, _arrival +1)  -- Daily except arrival
	                 WHEN 2 THEN _arrival       -- Daily except departure
	                 WHEN 3 THEN _arrival       -- Once on arrival
	                 WHEN 4 THEN _departure     -- Once on departure
	                 WHEN 9 THEN NEW.start_date -- custom period
	              END;	
	             
	_end_date   = CASE frequency 
	                 WHEN 1 THEN _arrival     -- Daily except arrival
	                 WHEN 2 THEN iif(_arrival = _departure, _departure, _departure -1) -- Daily except departure
	                 WHEN 3 THEN _arrival     -- Once on arrival
	                 WHEN 4 THEN _departure   -- Once on departure
	                 WHEN 9 THEN NEW.end_date -- custom period
	              END;	
	             
    _charge_qty = COALESCE(NEW.qty, 1) 
		           * CASE COALESCE(multiplier,1)
		                WHEN 1 THEN 0                 -- per booking
		                WHEN 2 THEN 1                 -- per room
		                WHEN 3 THEN (_adult + _child) -- per person
		                WHEN 4 THEN _adult            -- per adult
		                WHEN 5 THEN _child            -- per child
		                WHEN 6 THEN _infant           -- per infant
		                ELSE 1
		             END 
		           * CASE frequency 
		                WHEN 1 THEN end_date - start_date    -- Daily except arrival
		                WHEN 2 THEN end_date - start_date    -- Daily except departure
		                WHEN 3 THEN 1                        -- Once on arrival
		                WHEN 4 THEN 1                        -- Once on departure
		                WHEN 9 THEN end_date - start_date +1 -- custom period
		             END;
	            
	FOR _charge_date IN SELECT fn_date_range(_start_date, _end_date) LOOP
		INSERT INTO charge_schedule(register_id, request_id, charge_date, charge_type, charge_dept, charge_qty, charge_amount, charge_to_booking)
		SELECT NEW.register_id, NEW.id, _charge_date, 'requirement', NEW.charge_dept, _charge_qty, NEW.charge_rate, NEW.charge_to_booking
		FROM charge_schedule 
		WHERE NOT EXISTS(SELECT * FROM charge_schedule cs 
					    WHERE cs.register_id = NEW.register_id AND cs.request_id = NEW.id AND cs.charge_date = _charge_date);
	END LOOP;

RETURN NULL;
END