aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_tr_registration_request_gen_charges
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