/**Begin E:\NextERP_PDM\PDM_UPGRADE\PDMS\branch_lastest\pdm\EB_table.sql **/

IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_EB_INTEGRATION') 
 CREATE TABLE T_EB_INTEGRATION (
FID BIGINT DEFAULT 0 NOT NULL,
FTYPE NVARCHAR(30) DEFAULT ' ' NOT NULL,
FNUMBER NVARCHAR(50) DEFAULT ' ' NOT NULL,
FNAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FSTATUS CHAR(1) DEFAULT '0' NOT NULL,
FMODELID BIGINT DEFAULT 0 NOT NULL,
FDATASETID BIGINT DEFAULT 0 NOT NULL,
FSOURCEOBJID BIGINT DEFAULT 0 NOT NULL,
FSOURCESYSID BIGINT DEFAULT 0 NOT NULL,
FTARGETSYSID BIGINT DEFAULT 0 NOT NULL,
FTRIGGERID BIGINT DEFAULT 0 NOT NULL,
FTABLENAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FMODIFIERFIELD BIGINT DEFAULT 0 NOT NULL,
FMODIFYDATEFIELD DATETIME
);

IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_EB_INTEGRATION_MAPVAL') 
 CREATE TABLE T_EB_INTEGRATION_MAPVAL (
FID BIGINT DEFAULT 0 NOT NULL,
FENTRYID BIGINT DEFAULT 0 NOT NULL,
FSEQ INT DEFAULT 0 NOT NULL,
FSOURCENUMBER NVARCHAR(50) DEFAULT ' ' NOT NULL,
FSOURCENAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FAIMNUMBER NVARCHAR(50) DEFAULT ' ' NOT NULL,
FAIMNAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FSTATE CHAR(1) DEFAULT '1' NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_EB_INTEGRATION_MAP') 
 CREATE TABLE T_EB_INTEGRATION_MAP (
FID BIGINT DEFAULT 0 NOT NULL,
FNAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FTYPE NVARCHAR(30) DEFAULT ' ' NOT NULL,
FDIMSOURCE NVARCHAR(50) DEFAULT ' ' NOT NULL,
FDIMTARGET NVARCHAR(50) DEFAULT ' ' NOT NULL,
FMODELID BIGINT DEFAULT 0 NOT NULL,
FMAPCAT BIGINT DEFAULT 0 NOT NULL,
FCREATORID BIGINT DEFAULT 0 NOT NULL,
FCREATETIME DATETIME,
FMODIFIERID BIGINT DEFAULT 0 NOT NULL,
FMODIFYTIME DATETIME
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_EB_INTEGRATION_MAPCAT') 
 CREATE TABLE T_EB_INTEGRATION_MAPCAT (
FID BIGINT DEFAULT 0 NOT NULL,
FMODELID BIGINT DEFAULT 0 NOT NULL,
FNUMBER NVARCHAR(50) DEFAULT ' ' NOT NULL,
FNAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FPARENTID BIGINT DEFAULT 0 NOT NULL,
FCREATORID BIGINT DEFAULT 0 NOT NULL,
FCREATETIME DATETIME,
FMODIFIERID BIGINT DEFAULT 0 NOT NULL,
FMODIFYTIME DATETIME
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_EB_INTEGRATION_MAPM') 
 CREATE TABLE T_EB_INTEGRATION_MAPM (
FID BIGINT DEFAULT 0 NOT NULL,
FENTRYID BIGINT DEFAULT 0 NOT NULL,
FSEQ INT DEFAULT 0 NOT NULL,
FMETRICSOURCE NVARCHAR(50) DEFAULT ' ' NOT NULL,
FMETRICSOURCENAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FMETRICTARGET NVARCHAR(50) DEFAULT ' ' NOT NULL,
FMETRICTARGETNAME NVARCHAR(50) DEFAULT ' ' NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_EB_INTEGRATION_MAPDIM') 
 CREATE TABLE T_EB_INTEGRATION_MAPDIM (
FID BIGINT DEFAULT 0 NOT NULL,
FENTRYID BIGINT DEFAULT 0 NOT NULL,
FSEQ INT DEFAULT 0 NOT NULL,
FDIMSOURCE NVARCHAR(50) DEFAULT ' ' NOT NULL,
FDIMSOURCENAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FDIMTARGETID BIGINT DEFAULT 0 NOT NULL,
FDIMTARGET NVARCHAR(50) DEFAULT ' ' NOT NULL,
FDIMTARGETNAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FDIMMEMBMAPID BIGINT DEFAULT 0 NOT NULL,

FDIMMEMBER NVARCHAR(50) DEFAULT ' ' NOT NULL,
FDIMMEMBNUM NVARCHAR(50) DEFAULT ' ' NOT NULL
);


 EXEC p_AlterColumn 'T_EB_STRUCTOFMETRIC', 'FUSE', 'CHAR(1)', 'NOT NULL', '0010', ''' ''';

 EXEC p_AlterColumn 'T_EB_STRUCTOFMETRIC', 'FCOMPAREMETRIC', 'NVARCHAR(30)', 'NOT NULL', '1111', ''' ''';

IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_EB_INTEGRATION_ROUTER') 
 CREATE TABLE T_EB_INTEGRATION_ROUTER (
FID BIGINT DEFAULT 0 NOT NULL,
FSOURCE NVARCHAR(50) DEFAULT ' ' NOT NULL,
FTABLE NVARCHAR(50) DEFAULT ' ' NOT NULL,
FMODELID BIGINT DEFAULT 0 NOT NULL
);





IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_EB_STRUCTOFACCOUNT' AND KSQL_COL_NAME ='FCTVIEW') 
 ALTER TABLE T_EB_STRUCTOFACCOUNT ADD (FCTVIEW BIGINT DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_EB_STRUCTOFACCOUNT' AND KSQL_COL_NAME ='FCTMEMBERID') 
 ALTER TABLE T_EB_STRUCTOFACCOUNT ADD (FCTMEMBERID NVARCHAR(50) DEFAULT ' ' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_EB_BIZRULESET_ENTRY') 
 CREATE TABLE T_EB_BIZRULESET_ENTRY (
FID BIGINT DEFAULT 0 NOT NULL,
FENTRYID BIGINT DEFAULT 0 NOT NULL,
FREFBIZRULE BIGINT DEFAULT 0 NOT NULL,
FSEQ INT DEFAULT 0 NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_EB_BROADCASE_L') 
 CREATE TABLE T_EB_BROADCASE_L (
FID BIGINT DEFAULT 0 NOT NULL,
FPKID NVARCHAR(30) DEFAULT ' ' NOT NULL,
FLOCALEID NVARCHAR(10) DEFAULT ' ' NOT NULL,
FNAME NVARCHAR(50) DEFAULT ' ' NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_EB_BROADCASTMAPPING') 
 CREATE TABLE T_EB_BROADCASTMAPPING (
FID BIGINT DEFAULT 0 NOT NULL,
FENTRYID BIGINT DEFAULT 0 NOT NULL,
FSEQ INT DEFAULT 0 NOT NULL,
FSOURCERANGE NVARCHAR(10) DEFAULT ' ' NOT NULL,
FSOURCEMEMBER BIGINT DEFAULT 0 NOT NULL,
FSOURCELONGNUMBER NVARCHAR(200) DEFAULT ' ' NOT NULL,
FSOURCENUMBER NVARCHAR(50) DEFAULT ' ' NOT NULL,
FSOURCENAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FTARGETMEMBER BIGINT DEFAULT 0 NOT NULL,
FTARGETLONGNUMBER NVARCHAR(200) DEFAULT ' ' NOT NULL,
FTARGETNUMBER NVARCHAR(50) DEFAULT ' ' NOT NULL,
FTARGETNAME NVARCHAR(50) DEFAULT ' ' NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_EB_BIZRULESET' AND KSQL_COL_NAME ='FBROADFLAG') 
 ALTER TABLE T_EB_BIZRULESET ADD (FBROADFLAG CHAR(1) DEFAULT '0' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_EB_BIZRULESET' AND KSQL_COL_NAME ='FBROADCASE') 
 ALTER TABLE T_EB_BIZRULESET ADD (FBROADCASE BIGINT DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_EB_BROADCASE') 
 CREATE TABLE T_EB_BROADCASE (
FID BIGINT DEFAULT 0 NOT NULL,
FNUMBER NVARCHAR(50) DEFAULT ' ' NOT NULL,
FNAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FMODELID BIGINT DEFAULT 0 NOT NULL,
FBIZMODEL BIGINT DEFAULT 0 NOT NULL,
FCONTROLDIMENSION BIGINT DEFAULT 0 NOT NULL,
FCREATERFIELD BIGINT DEFAULT 0 NOT NULL,
FMODIFIERID BIGINT DEFAULT 0 NOT NULL,
FCREATETIME DATETIME,
FMODIFYTIME DATETIME
);

 IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_EB_STRUCTOFCHANGETYPE' AND KSQL_COL_NAME IN ('FFORMULA')) 
 ALTER TABLE T_EB_STRUCTOFCHANGETYPE ADD (FFORMULA NCLOB );
  IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_EB_STRUCTOFCHANGETYPE' AND KSQL_COL_NAME IN ('FFORMULANAME')) 
 ALTER TABLE T_EB_STRUCTOFCHANGETYPE ADD (FFORMULANAME NCLOB );


/**End E:\NextERP_PDM\PDM_UPGRADE\PDMS\branch_lastest\pdm\EB_table.sql **/
