Notes

-- Delete statements for resort inclusion metadata
DELETE FROM reservation.rh_pmt_opt_incl_list_assgn WHERE pmt_opt_incl_list_hdr_id IN (
SELECT pmt_opt_incl_list_hdr_id FROM reservation.rh_pmt_opt_incl_list_hdr WHERE incl_list_hdr_nm = 'CWP Bonus Time Special'
);

DELETE FROM reservation.rh_pmt_opt_incl_list_dtl WHERE pmt_opt_incl_list_hdr_id IN (
SELECT pmt_opt_incl_list_hdr_id FROM reservation.rh_pmt_opt_incl_list_hdr WHERE incl_list_hdr_nm = 'CWP Bonus Time Special'
);

DELETE FROM reservation.rh_pmt_opt_incl_list_hdr WHERE incl_list_hdr_nm = 'CWP Bonus Time Special';

-- Delete statements for black out day metadata
DELETE FROM reservation.rh_pmt_opt_black_out_dt_assgn WHERE black_out_dt_hdr_id IN (
SELECT black_out_dt_hdr_id FROM reservation.rh_black_out_dt_hdr WHERE hdr_nm = 'WvR Special Event Window'
);

DELETE FROM RESERVATION.RH_BLACK_OUT_DT_DTL WHERE black_out_dt_hdr_id IN (
SELECT black_out_dt_hdr_id FROM RESERVATION.RH_BLACK_OUT_DT_HDR WHERE HDR_NM = 'WvR Special Event Window'
);

DELETE FROM reservation.rh_pmt_opt_ruleset_grp WHERE pmt_opt_ruleset_grp_name = 'CW Bonus Time RuleSet Group';

DELETE FROM reservation.rh_pmt_opt_elgbty_rule WHERE rule_set_name = 'CW Bonus Time Rule';

DELETE FROM reservation.rh_room_rate_rule WHERE room_rate_rule_name = 'CW Bonus Time Stay Rule';

DELETE FROM reservation.rh_room_pmt_opt_asgnmt WHERE bkg_chrg_rule_set_id IN (
SELECT bkg_chrg_rule_set_id FROM reservation.rh_bkg_chrg_rule_set WHERE bkg_chrg_rule_set_name = 'CW Bonus Time RULES'
);

DELETE FROM reservation.rh_room_pmt_opt WHERE room_pmt_opt_name = 'CW Bonus Time';

DELETE FROM reservation.rh_room_pmt_opt_asgnmt WHERE room_pmt_opt_id IN (
SELECT room_pmt_opt_id FROM reservation.rh_room_pmt_opt WHERE room_pmt_opt_name = 'CW Bonus Time'
);

DELETE FROM reservation.rh_room_chrg_fctr_asgnmt WHERE room_chrg_rule_id IN (
SELECT room_chrg_rule_id FROM reservation.rh_room_chrg_rule WHERE room_chrg_rule_name = 'CW Bonus Time'
);

DELETE FROM reservation.rh_room_chrg_rule WHERE room_chrg_rule_name = 'CW Bonus Time';

DELETE FROM reservation.rh_bkg_chrg_rule_set WHERE bkg_chrg_rule_set_name = 'CW Bonus Time RULES';

DELETE FROM reservation.rh_bkg_chrg WHERE bkg_chrg_name = 'CW Bonus Time';

-- Delete statements for package status update
DELETE FROM RESERVATION.RH_ROOM_CHRG_RULE_ASGNMT WHERE room_rate_plan_id = 116;
DELETE FROM RESERVATION.RH_PMT_OPT_RULESET_GRP_ASGNMT WHERE room_rate_plan_id = 116;

DELETE FROM RESERVATION.RH_ROOM_CHRG_RULE_ASGNMT WHERE room_rate_plan_id = 115;
DELETE FROM RESERVATION.RH_PMT_OPT_RULESET_GRP_ASGNMT WHERE room_rate_plan_id = 115;

DELETE FROM RESERVATION.RH_ROOM_CHRG_RULE_ASGNMT WHERE room_rate_plan_id = 114;
DELETE FROM RESERVATION.RH_PMT_OPT_RULESET_GRP_ASGNMT WHERE room_rate_plan_id = 114;

DELETE FROM RESERVATION.RH_ROOM_CHRG_RULE_ASGNMT WHERE room_rate_plan_id = 113;
DELETE FROM RESERVATION.RH_PMT_OPT_RULESET_GRP_ASGNMT WHERE room_rate_plan_id = 113;

DELETE FROM RESERVATION.RH_ROOM_CHRG_RULE_ASGNMT WHERE room_rate_plan_id = 112;
DELETE FROM RESERVATION.RH_PMT_OPT_RULESET_GRP_ASGNMT WHERE room_rate_plan_id = 112;

DELETE FROM RESERVATION.RH_ROOM_CHRG_RULE_ASGNMT WHERE room_rate_plan_id = 111;
DELETE FROM RESERVATION.RH_PMT_OPT_RULESET_GRP_ASGNMT WHERE room_rate_plan_id = 111;

DELETE FROM RESERVATION.RH_PMT_OPT_RULESET_GRP_ASGNMT WHERE room_rate_plan_id = 3;
DELETE FROM RESERVATION.RH_ROOM_CHRG_RULE_ASGNMT WHERE room_rate_plan_id = 3;

-- Delete statements for package status update
DELETE FROM RESERVATION.RH_PMT_OPT_RULESET_GRP_ASGNMT WHERE room_rate_plan_id IN (116, 115, 114, 113, 112, 111, 3);

DELETE FROM util_wyn.pkg_status WHERE pkg_name = 'Insert_CWP_Bonus_Time_Metadata.sql';

COMMIT;