Ellis improvement

1. DELETE FROM VD_VENDOR_PLAN_CANCEL_POLICY C
2. WHERE C.ROOM_PLAN_SEQ NOT IN (SELECT P.ROOM_PLAN_SEQ FROM VD_VENDOR_ROOM_PLAN P)

1. TRUNCATE TABLE VD_VENDOR_PLAN_CANCEL_POLICY_MASTER; #
2. UPDATE VD_VENDOR_PLAN_CANCEL_POLICY SET POLICY_MASTER_SEQ = NULL WHERE POLICY_MASTER_SEQ IS NOT NULL; #
3. -- 1/ migrate all plan policy into plan_policy_master
4. INSERT INTO VD_VENDOR_PLAN_CANCEL_POLICY_MASTER
5. (ROOM_PLAN_SEQ, DEFAULT_YN, NON_REFUNDABLE_YN, USE_POLICY_OPTION_YN, APPLIED_FROM_DATE, APPLIED_TO_DATE, DATE_OPTION_YN )
6. SELECT
7. O.ROOM_PLAN_SEQ AS ROOM_PLAN_SEQ
8. , CASE WHEN ROW_NUMBER() OVER = 1 THEN 'Y' ELSE 'N' END AS DEFAULT_YN
9. , P.NON_REFUNDABLE_YN
10. ,CASE WHEN ROW_NUMBER() OVER = 1 THEN 'Y' ELSE 'N' END AS USE_POLICY_OPTION_YN
11. , O.APPLIED_FROM_DATE
12. , O.APPLIED_TO_DATE
13. , 'N' AS DATE_OPTION_YN
14. FROM VD_VENDOR_PLAN_CANCEL_POLICY O
15. INNER JOIN VD_VENDOR_ROOM_PLAN P ON P.ROOM_PLAN_SEQ = O.ROOM_PLAN_SEQ
16. WHERE O.POLICY_MASTER_SEQ IS NULL
17. GROUP BY O.ROOM_PLAN_SEQ, O.APPLIED_FROM_DATE, O.APPLIED_TO_DATE
18. ; # # # #
19. -- 3/ check plans is non-refund = 'Y' and don't have orginal plan and don't have any policy and then create a policy_master_Seq with default_yn = 'Y' and from now -> 2099
20. INSERT INTO VD_VENDOR_PLAN_CANCEL_POLICY_MASTER
21. (ROOM_PLAN_SEQ, DEFAULT_YN, NON_REFUNDABLE_YN, USE_POLICY_OPTION_YN, APPLIED_FROM_DATE, APPLIED_TO_DATE, DATE_OPTION_YN , FIRST_INSERT_UNO )
22. SELECT P.ROOM_PLAN_SEQ, 'Y' AS DEFAULT_YN, P.NON_REFUNDABLE_YN, 'Y' AS USE_POLICY_OPTION_YN
23. , '20240101' AS APPLIED_FROM_DATE, '20990101' AS APPLIED_TO_DATE, 'N' AS DATE_OPTION_YN, 30000 AS FIRST_INSERT_UNO
24. FROM VD_VENDOR_ROOM_PLAN P
25. WHERE P.NON_REFUNDABLE_YN = 'Y' AND P.ORIGIN_ROOM_PLAN_SEQ IS NULL
26. AND P.ROOM_PLAN_SEQ NOT IN (SELECT DISTINCT M.ROOM_PLAN_SEQ FROM VD_VENDOR_PLAN_CANCEL_POLICY_MASTER M WHERE M.NON_REFUNDABLE_YN = 'Y' )
27. AND P.ROOM_PLAN_SEQ NOT IN (SELECT DISTINCT C.ROOM_PLAN_SEQ FROM VD_VENDOR_PLAN_CANCEL_POLICY C); #
28. -- 4/ check plans is non-refund = 'N' and don't have original plan, -> create policy master seq
29. INSERT INTO VD_VENDOR_PLAN_CANCEL_POLICY_MASTER
30. (ROOM_PLAN_SEQ, DEFAULT_YN, NON_REFUNDABLE_YN, USE_POLICY_OPTION_YN, APPLIED_FROM_DATE, APPLIED_TO_DATE, DATE_OPTION_YN , FIRST_INSERT_UNO )
31. SELECT P.ROOM_PLAN_SEQ, 'Y' AS DEFAULT_YN, P.NON_REFUNDABLE_YN, 'Y' AS USE_POLICY_OPTION_YN
32. , '20240101' AS APPLIED_FROM_DATE, '20990101' AS APPLIED_TO_DATE, 'N' AS DATE_OPTION_YN, 30000 AS FIRST_INSERT_UNO
33. FROM VD_VENDOR_ROOM_PLAN P WHERE P.NON_REFUNDABLE_YN = 'N' AND P.ORIGIN_ROOM_PLAN_SEQ IS NULL
34. AND P.ROOM_PLAN_SEQ NOT IN (SELECT DISTINCT M.ROOM_PLAN_SEQ FROM VD_VENDOR_PLAN_CANCEL_POLICY_MASTER M); #
35. -- 5/ update policy_master_seq in vd_Vendor_cancel_policy
36. UPDATE VD_VENDOR_PLAN_CANCEL_POLICY O
37. INNER JOIN VD_VENDOR_PLAN_CANCEL_POLICY_MASTER M ON M.ROOM_PLAN_SEQ = O.ROOM_PLAN_SEQ
38. AND M.APPLIED_FROM_DATE = O.APPLIED_FROM_DATE AND M.APPLIED_TO_DATE = O.APPLIED_TO_DATE
39. SET O.POLICY_MASTER_SEQ = M.POLICY_MASTER_SEQ
40. WHERE O.POLICY_MASTER_SEQ IS NULL
41. ;
45. SELECT * FROM VD_VENDOR_PLAN_CANCEL_POLICY C
46. INNER JOIN VD_VENDOR_ROOM_PLAN R ON R.ROOM_PLAN_SEQ = C.ROOM_PLAN_SEQ
47. WHERE POLICY_MASTER_SEQ IS NOT NULL;
50. SELECT (SELECT COUNT FROM VD_VENDOR_ROOM_PLAN WHERE ORIGIN_ROOM_PLAN_SEQ IS NULL AND NON_REFUNDABLE_YN = 'Y') AS OLD_NON_REF
51. , (SELECT COUNT FROM VD_VENDOR_PLAN_CANCEL_POLICY_MASTER M WHERE DEFAULT_YN = 'Y' AND M.NON_REFUNDABLE_YN = 'Y') AS NEW_NON_REF
53. , (SELECT COUNT FROM VD_VENDOR_ROOM_PLAN WHERE ORIGIN_ROOM_PLAN_SEQ IS NULL AND NON_REFUNDABLE_YN = 'N') AS OLD_REF
54. , (SELECT COUNT FROM VD_VENDOR_PLAN_CANCEL_POLICY_MASTER M WHERE DEFAULT_YN = 'Y' AND M.NON_REFUNDABLE_YN = 'N') AS NEW_REF
55. , (SELECT COUNT FROM VD_VENDOR_PLAN_CANCEL_POLICY_MASTER) AS COUNT_MASTER
56. , (SELECT COUNT FROM VD_VENDOR_PLAN_CANCEL_POLICY_MASTER WHERE DEFAULT_YN = 'N') AS COUNT_COUNT_OPTION -- DEV CREATE FIRST
57. ;

-- check case non refund = N but not cxl item
SELECT * FROM VD_VENDOR_PLAN_CANCEL_POLICY_MASTER
WHERE NON_REFUNDABLE_YN = 'N'
AND POLICY_MASTER_SEQ NOT IN (SELECT VD_VENDOR_PLAN_CANCEL_POLICY.POLICY_MASTER_SEQ
FROM VD_VENDOR_PLAN_CANCEL_POLICY);

need write sql to check data first

create table VD_VENDOR_PLAN_CANCEL_POLICY_MASTER
(
POLICY_MASTER_SEQ bigint auto_increment
primary key,
ROOM_PLAN_SEQ bigint not null,
DEFAULT_YN varchar(1) not null,
NON_REFUNDABLE_YN varchar(1) not null,
USE_POLICY_OPTION_YN varchar(1) default 'N' not null,
APPLIED_FROM_DATE date not null,
APPLIED_TO_DATE date not null,
DATE_OPTION_YN varchar(1) default 'N' not null,
APPLIED_SUN_YN varchar(1) default 'N' not null,
APPLIED_MON_YN varchar(1) default 'N' not null,
APPLIED_TUE_YN varchar(1) default 'N' not null,
APPLIED_WED_YN varchar(1) default 'N' not null,
APPLIED_THU_YN varchar(1) default 'N' not null,
APPLIED_FRI_YN varchar(1) default 'N' not null,
APPLIED_SAT_YN varchar(1) default 'N' not null,
DELETE_YN varchar(1) default 'N' not null,
DISPLAY_ORDER smallint null,
FIRST_INSERT_UNO bigint default 10000 not null,
FIRST_INSERT_DATETIME datetime default CURRENT_TIMESTAMP not null,
LAST_UPDATE_UNO bigint default 10000 not null,
LAST_UPDATE_DATETIME datetime default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP,
DISPLAY_TITLE varchar(150) null
)
comment 'vendor room plan cancellation policy master' avg_row_length = 90;

create index XIF_VD_VENDOR_PLAN_CANCEL_POLICY_MASTER_01
on VD_VENDOR_PLAN_CANCEL_POLICY_MASTER (ROOM_PLAN_SEQ);

create index XIN_VD_VENDOR_PLAN_CANCEL_POLICY_MASTER_01
on VD_VENDOR_PLAN_CANCEL_POLICY_MASTER (ROOM_PLAN_SEQ, DEFAULT_YN);

create index XIN_VD_VENDOR_PLAN_CANCEL_POLICY_MASTER_02
on VD_VENDOR_PLAN_CANCEL_POLICY_MASTER (ROOM_PLAN_SEQ, DELETE_YN);

ALTER TABLE OMH_SUITE.VD_VENDOR_PLAN_CANCEL_POLICY
ADD COLUMN POLICY_MASTER_SEQ bigint NULL;