CREATE TABLE "CRM_AGENT"
( "AGENT_NO" NUMBER,
"AGENT_NAME" VARCHAR2(100),
"AGENT_ADDRESS" VARCHAR2(100),
"CREATE_DT" DATE,
"UPDATE_DT" DATE,
"UPDATE_BY" VARCHAR2(100),
"CREATE_BY" VARCHAR2(100),
CONSTRAINT "AGENT_NO_PK" PRIMARY KEY ("AGENT_NO")
USING INDEX ENABLE
) NO INMEMORY ;
--------------------------------------------------------------------------------
CREATE TABLE "CRM_BASE"
( "CRM_BASE_ID" NUMBER,
"BASE_TYPE" VARCHAR2(100),
"BASE_NAME" VARCHAR2(100)
) NO INMEMORY ;
-------------------------------------------------
CREATE TABLE "CRM_CONTACT"
( "CONTACT_NO" NUMBER,
"CONTACT_ID" VARCHAR2(30),
"CONTACTS" VARCHAR2(300),
"CONTACTS_PERSON" VARCHAR2(300),
"BIRTH_DATE" DATE,
"CONTACT_WORK" VARCHAR2(16),
"CONTACT_HOME" VARCHAR2(16),
"CONTACT_MOBILE" VARCHAR2(16),
"CONTACT_OTHERS" VARCHAR2(16),
"CONTACT_ASSISTANT" VARCHAR2(16),
"EMAIL_OFFICE" VARCHAR2(80),
"EMAIL_SELF" VARCHAR2(80),
"SKYPE_NAME" VARCHAR2(80),
"IS_ASSIGNED_TO" VARCHAR2(80),
"CONTACT_CAT" VARCHAR2(80),
"LEAD_SOURCE" VARCHAR2(80),
"DEPT_NAME" VARCHAR2(80),
"PRIAMRY_ADDRESS" VARCHAR2(1000),
"OTHER_ADDRESS" VARCHAR2(1000),
"REAMRKS" VARCHAR2(3999),
"STATUS" NUMBER,
"APP_NO" VARCHAR2(100),
"PAGE_NO" VARCHAR2(100),
"COMPANY_NO" NUMBER,
"CREATED_BY" VARCHAR2(100),
"CREATED_DT" DATE,
"UPDATED_BY" VARCHAR2(100),
"UPDATED_DT" DATE,
"GENDER_NAME" VARCHAR2(100),
"LEAD_FLAG" NUMBER,
"OPTY_FLAG" NUMBER,
"CUST_FLAG" NUMBER,
"SOURCE_TYPE" NUMBER,
"USER_NO" VARCHAR2(100),
"LEAD_SOURCE_1" NUMBER
) NO INMEMORY ;
CREATE OR REPLACE EDITIONABLE TRIGGER "CRM_CONTACT_ID_GEN"
BEFORE INSERT
ON crm_contact
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
WHEN (new.CONTACT_NO IS NULL and new.CONTACT_ID is null) BEGIN
SELECT NVL (MAX (CONTACT_NO), 0) + 1,NVL (MAX (to_number(CONTACT_ID)), 0) + 1 INTO :new.CONTACT_NO,:new.CONTACT_ID FROM CRM_CONTACT;
END;
/
ALTER TRIGGER "CRM_CONTACT_ID_GEN" ENABLE;
---------------------------------------------------------------------------------
CREATE TABLE "CRM_CUSTOMERS"
( "CUST_NO" NUMBER,
"CONTACT_NO" NUMBER,
"CUSTOMER_NAME" VARCHAR2(100),
PRIMARY KEY ("CUST_NO")
USING INDEX ENABLE
) NO INMEMORY ;
------------------------------------------------------------------------------------------
CREATE TABLE "CRM_TICKETS"
( "TKT_NO" NUMBER,
"TKT_NAME" VARCHAR2(100),
"TKT_PIPELINE" VARCHAR2(100),
"TICKET_STATUS" VARCHAR2(100),
"TKT_DESCRIPTIONS" VARCHAR2(200),
"TICKET_OWNER" VARCHAR2(100),
"TICKET_OWNER_ID" NUMBER,
"PRIORITY" VARCHAR2(100),
"ASSOCIATE_WITH" VARCHAR2(100),
"CREATED_BY" VARCHAR2(100),
"CREATED_DATE" DATE,
"UPDATE_BY" VARCHAR2(100),
"UPDATE_DATE" DATE,
"ASSIGN_TO" NUMBER,
"USER_NO" NUMBER,
"COMPLETE_STATUS" CHAR(3),
"COMPLETE_NOTE" VARCHAR2(200),
"COMPETE_DATE" DATE,
PRIMARY KEY ("TKT_NO")
USING INDEX ENABLE
) NO INMEMORY ;
CREATE OR REPLACE EDITIONABLE TRIGGER "TKT_NO_GEN"
before insert on CRM_TICKETS
referencing old as old new as new
for each row
begin
select nvl(max(TKT_NO),0)+1 into :new.TKT_NO from CRM_TICKETS;
end;
/
ALTER TRIGGER "TKT_NO_GEN" ENABLE;
-------------------------------------------------------------
CREATE TABLE "CRM_TRNS_LOG"
( "CRM_TRNS_LG_NO" NUMBER,
"CONTACT_NO" NUMBER,
"CONTACT_ID" NUMBER,
"CALL_DATE" DATE,
"DESCRIPTIONS" VARCHAR2(100),
"LOG_TYPE" VARCHAR2(20),
"LOG_NO" NUMBER,
"APPOINT_WITH" VARCHAR2(100),
"APPOINT_SUBJECT" VARCHAR2(100),
"CALL_START_TIME" DATE,
"CALL_END_TIME" DATE,
"CALL_DUARATIONS" VARCHAR2(100),
"CALL_TYPE" VARCHAR2(50),
"CALL_AGENDA" VARCHAR2(50),
PRIMARY KEY ("CRM_TRNS_LG_NO")
USING INDEX ENABLE
) NO INMEMORY ;
CREATE OR REPLACE EDITIONABLE TRIGGER "CRM_TRNS_LG_NO_GEN"
BEFORE INSERT
ON crm_trns_log
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
WHEN (new.crm_trns_lg_no IS NULL) BEGIN
SELECT NVL (MAX (crm_trns_lg_no), 0) + 1 INTO :new.crm_trns_lg_no FROM crm_trns_log;
END;
/
ALTER TRIGGER "CRM_TRNS_LG_NO_GEN" ENABLE;
No comments:
Post a Comment