· 4 years ago · Mar 09, 2021, 10:02 AM
1CREATE TABLE if not exists TRPM_BILLG_RULE_L (
2 BILLG_RULE_ID int NOT NULL,
3 BILLG_RULE_NM char(100) NOT NULL,
4 BILLG_RULE_TYP_ID char(1) NOT NULL, -- <{TRPM_BILLG_RULE_TYP }>,
5 BILLG_RULE_BILLG_TYP_ID int NOT NULL, -- <{TRPM_BILLG_RULE_BILLG_TYP }>,
6 REGN_CD char(2) NOT NULL,
7 CTRY_CD decimal(3) NOT NULL,
8 CURR_CD decimal(3) NOT NULL,
9 EFF_STRT_DT date NOT NULL,
10 EFF_END_DT date NOT NULL,
11 SIF_ENTY_TYPE int NOT NULL, -- <{TRPM_BILLG_RULE_SIF_ENTY_TYP }>,
12 DFLT_ISO_BIN_NUM decimal(8),
13 DFLT_BUS_ID int,
14 GRC_PRD_CALCN_ID int NOT NULL, -- <{TRPM_BILLG_RULE_GRC_PRD_CALCN }>,
15 PROD_TYP_ID int,
16 SNDR_BUS_ID int,
17 SUB_SNDR_ID int,
18 SNDR_SUB_BUS_ID int,
19
20 BILLG_MNTH_JAN char(1) NOT NULL,
21 BILLG_MNTH_FEB char(1) NOT NULL,
22 BILLG_MNTH_MAR char(1) NOT NULL,
23 BILLG_MNTH_APR char(1) NOT NULL,
24 BILLG_MNTH_MAY char(1) NOT NULL,
25 BILLG_MNTH_JUN char(1) NOT NULL,
26 BILLG_MNTH_JUL char(1) NOT NULL,
27 BILLG_MNTH_AUG char(1) NOT NULL,
28 BILLG_MNTH_SEP char(1) NOT NULL,
29 BILLG_MNTH_OCT char(1) NOT NULL,
30 BILLG_MNTH_NOV char(1) NOT NULL,
31 BILLG_MNTH_DEC char(1) NOT NULL,
32 SIF_MNTH_JAN char(1) NOT NULL,
33 SIF_MNTH_FEB char(1) NOT NULL,
34 SIF_MNTH_MAR char(1) NOT NULL,
35 SIF_MNTH_APR char(1) NOT NULL,
36 SIF_MNTH_MAY char(1) NOT NULL,
37 SIF_MNTH_JUN char(1) NOT NULL,
38 SIF_MNTH_JUL char(1) NOT NULL,
39 SIF_MNTH_AUG char(1) NOT NULL,
40 SIF_MNTH_SEP char(1) NOT NULL,
41 SIF_MNTH_OCT char(1) NOT NULL,
42 SIF_MNTH_NOV char(1) NOT NULL,
43 SIF_MNTH_DEC char(1) NOT NULL,
44
45 BILLG_RBATE_MNTH_JAN char(1) NOT NULL,
46 BILLG_RBATE_MNTH_FEB char(1) NOT NULL,
47 BILLG_RBATE_MNTH_MAR char(1) NOT NULL,
48 BILLG_RBATE_MNTH_APR char(1) NOT NULL,
49 BILLG_RBATE_MNTH_MAY char(1) NOT NULL,
50 BILLG_RBATE_MNTH_JUN char(1) NOT NULL,
51 BILLG_RBATE_MNTH_JUL char(1) NOT NULL,
52 BILLG_RBATE_MNTH_AUG char(1) NOT NULL,
53 BILLG_RBATE_MNTH_SEP char(1) NOT NULL,
54 BILLG_RBATE_MNTH_OCT char(1) NOT NULL,
55 BILLG_RBATE_MNTH_NOV char(1) NOT NULL,
56 BILLG_RBATE_MNTH_DEC char(1) NOT NULL,
57 SIF_RBATE_MNTH_JAN char(1) NOT NULL,
58 SIF_RBATE_MNTH_FEB char(1) NOT NULL,
59 SIF_RBATE_MNTH_MAR char(1) NOT NULL,
60 SIF_RBATE_MNTH_APR char(1) NOT NULL,
61 SIF_RBATE_MNTH_MAY char(1) NOT NULL,
62 SIF_RBATE_MNTH_JUN char(1) NOT NULL,
63 SIF_RBATE_MNTH_JUL char(1) NOT NULL,
64 SIF_RBATE_MNTH_AUG char(1) NOT NULL,
65 SIF_RBATE_MNTH_SEP char(1) NOT NULL,
66 SIF_RBATE_MNTH_OCT char(1) NOT NULL,
67 SIF_RBATE_MNTH_NOV char(1) NOT NULL,
68 SIF_RBATE_MNTH_DEC char(1) NOT NULL,
69
70 DFLT_BILLG_GRC_PRD int,
71 CRIT_RPIN char(1) DEFAULT 'N' NOT NULL,
72 CRIT_ISSR_REFNC_NUM char(1) DEFAULT 'N' NOT NULL,
73 CRIT_BIN char(1) DEFAULT 'N' NOT NULL,
74 CRIT_AR char(1) DEFAULT 'N' NOT NULL,
75 CRIT_ENTY char(1) DEFAULT 'N' NOT NULL,
76
77 GRC_PRD_BILLG_LN_CD varchar(20),
78 GRC_PRD_RBATE_LN_CD varchar(20),
79 BILLG_FREQ int,
80 RBATE_FREQ int,
81
82 COMMENTS text,
83 BILLG_RULE_CONFIG_ID int,
84 BILLG_RULE_MGRTD char(1) DEFAULT 'N' NOT NULL,
85 STA_CD int NOT NULL,
86 ACTV_IND char(1) DEFAULT 'Y' NOT NULL,
87 LAST_UPD_USR_ID int NOT NULL,
88 LAST_UPD_TS timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
89 VER_NUM int NOT NULL,
90 PRIMARY KEY (BILLG_RULE_ID)
91);
92
93ALTER TABLE TRPM_BILLG_RULE_L
94 ADD CONSTRAINT FK_TRPM_BILLG_RULE_L_CTRY_CD FOREIGN KEY (CTRY_CD)
95 REFERENCES TRPM_COUNTRY (CTRY_CD);
96
97ALTER TABLE TRPM_BILLG_RULE_L
98 ADD CONSTRAINT FK_TRPM_BILLG_RULE_L_STA_CD FOREIGN KEY (STA_CD)
99 REFERENCES TRPM_STATUS_CD (STA_CD);
100
101ALTER TABLE TRPM_BILLG_RULE_L
102 ADD CONSTRAINT FK_TRPM_BILLG_RULE_L_SNDR_BUS_ID FOREIGN KEY (SNDR_BUS_ID)
103 REFERENCES TRPM_SENDER (SNDR_BUS_ID);
104
105ALTER TABLE TRPM_BILLG_RULE_L
106 ADD CONSTRAINT FK_TRPM_BILLG_RULE_L_SUB_SNDR_ID FOREIGN KEY (SUB_SNDR_ID)
107 REFERENCES TRPM_SNDR_SUB_BID (SUB_SNDR_ID);
108
109ALTER TABLE TRPM_BILLG_RULE_L
110 ADD CONSTRAINT `FK_TRPM_BILLG_RULE_L_PROD_TYP_ID` FOREIGN KEY (`PROD_TYP_ID`) REFERENCES `TRPM_PROD_TYP` (`PROD_TYP_ID`);
111ALTER TABLE TRPM_BILLG_RULE_L
112 ADD CONSTRAINT `FK_TRPM_BILLG_RULE_L_LAST_UPD_USR_ID` FOREIGN KEY (`LAST_UPD_USR_ID`) REFERENCES `TRPM_USER` (`USR_ID`);
113ALTER TABLE TRPM_BILLG_RULE_L
114 ADD CONSTRAINT `FK_TRPM_BILLG_RULE_L_BILLG_RULE_TYP_ID` FOREIGN KEY (`BILLG_RULE_TYP_ID`) REFERENCES `TRPM_BILLG_RULE_TYP` (`BILLG_RULE_TYP_ID`);
115ALTER TABLE TRPM_BILLG_RULE_L
116 ADD CONSTRAINT `FK_TRPM_BILLG_RULE_L_BILLG_RULE_BILLG_TYP_ID` FOREIGN KEY (`BILLG_RULE_BILLG_TYP_ID`) REFERENCES `TRPM_BILLG_RULE_BILLG_TYP` (`BILLG_RULE_BILLG_TYP_ID`);
117
118ALTER TABLE TRPM_BILLG_RULE_L
119 ADD CONSTRAINT `FK_TRPM_BILLG_RULE_L_SIF_ENTY_TYPE` FOREIGN KEY (`SIF_ENTY_TYPE`) REFERENCES `TRPM_BILLG_RULE_SIF_ENTY_TYP` (`SIF_ENTY_TYP_ID`);
120
121ALTER TABLE TRPM_BILLG_RULE_L
122 ADD CONSTRAINT `FK_TRPM_BILLG_RULE_L_GRC_PRD_CALCN_ID` FOREIGN KEY (`GRC_PRD_CALCN_ID`) REFERENCES `TRPM_BILLG_RULE_GRC_PRD_CALCN` (`GRC_PRD_CALCN_ID`);
123
124
125ALTER TABLE TRPM_BILLG_RULE_L
126 ADD CONSTRAINT `FK_TRPM_BILLG_RULE_L_BILLG_RULE_CONFIG_ID` FOREIGN KEY (`BILLG_RULE_CONFIG_ID`) REFERENCES `TRPM_BILLG_RULE_CONFIG` (`BILLG_RULE_CONFIG_ID`);
127
128
129ALTER TABLE TRPM_BILLG_RULE_L
130 ADD CONSTRAINT `FK_TRPM_BILLG_RULE_L_BILLG_FREQ` FOREIGN KEY (`BILLG_FREQ`) REFERENCES `TRPM_BILLG_RULE_BILLG_FREQ` (`BILLG_FREQ`);
131
132
133CREATE INDEX FK_TRPM_BILLG_RULE_L_SNDR_BUS_ID_idx ON TRPM_BILLG_RULE_L (SNDR_BUS_ID);
134CREATE INDEX FK_TRPM_BILLG_RULE_L_SUB_SNDR_ID_idx ON TRPM_BILLG_RULE_L (SUB_SNDR_ID);
135CREATE INDEX IX_TRPM_BILLG_RULE_L_SNDR_SUB_BUS_ID ON TRPM_BILLG_RULE_L (SNDR_SUB_BUS_ID);
136CREATE INDEX FK_TRPM_BILLG_RULE_L_CTRY_CD_idx ON TRPM_BILLG_RULE_L (CTRY_CD);
137CREATE INDEX FK_TRPM_BILLG_RULE_L_STA_CD_idx ON TRPM_BILLG_RULE_L (STA_CD);
138CREATE INDEX IX_TRPM_BILLG_RULE_L_BILLG_RULE_NM ON TRPM_BILLG_RULE_L (BILLG_RULE_NM);