CREATE TABLE COTS_TAB_BENE_LOOKUP_CODE(
BRANCH VARCHAR2(3 BYTE) NOT NULL,
MSGTYPE VARCHAR2(50 BYTE) NOT NULL,
PARAM VARCHAR2(30 BYTE) NOT NULL,
VALUE VARCHAR2(70 BYTE) ,
ACTIVE_IND VARCHAR2(1 BYTE) NOT NULL,
COTS_UPD_DATE DATE ,
COTS_UPD_BY VARCHAR2(18 BYTE)
--REMARK VARCHAR2(30 BYTE) --(E FOR EMAIL,F FOR FAX)
)
--INDEX
CREATE UNIQUE INDEX COTS_PK_BENE_LOOKUP_CODE ON COTS_TAB_BENE_LOOKUP_CODE(BRANCH,MSGTYPE,PARAM);
--PRIMARY KEY
ALTER TABLE COTS_TAB_BENE_LOOKUP_CODE ADD (
CONSTRAINT COTS_PK_BENE_LOOKUP_CODE
PRIMARY KEY(BRANCH, MSGTYPE, PARAM)
)
CREATE PUBLIC SYNONYM COTS_TAB_BENE_LOOKUP_CODE FOR COTS_TAB_BENE_LOOKUP_CODE;
GRANT DELETE, INSERT, SELECT, UPDATE ON COTS_TAB_BENE_LOOKUP_CODE TO RCOT_APPS_ROLE;
GRANT SELECT ON COTS_TAB_BENE_LOOKUP_CODE TO RCOT_SELECT_ROLE;
--- Insert data to table,FOR EPO_FTBENE
INSERT INTO COTS_TAB_BENE_LOOKUP_CODE VALUES(712,'FTBNADV','SOURCESYSTEM','FFTI','A','','')
INSERT INTO COTS_TAB_BENE_LOOKUP_CODE VALUES(712,'FTBNADV','COTSUPDBY','BENEADVISING','A','','')
INSERT INTO COTS_TAB_BENE_LOOKUP_CODE VALUES(712,'FTBNADV','DIR','/ucotreg1/wrk/output/712/','A','','')
INSERT INTO COTS_TAB_BENE_LOOKUP_CODE VALUES(712,'FTBNADV','DELIVERYMEDIA','FAX','A','','')
INSERT INTO COTS_TAB_BENE_LOOKUP_CODE VALUES(712,'FTBNADV','DELIVERYSTATUS','GENERATED','A','','')
INSERT INTO COTS_TAB_BENE_LOOKUP_CODE VALUES(712,'FTBNADV','FILENAME_EMAILMSG','','A','','')
SELECT * FROM COTS_TAB_BENE_LOOKUP_CODE WHERE BRANCH=712 AND MSGTYPE='FTBNADV'
--- INSERT DATA TO TABLE,FOR EMAIL BENE ADV,HONGYI WILL UPDATE....