/**Begin E:\NextERP_PDM\PDM_UPGRADE\PDMS\branch_lastest\pdm\BCM_table.sql **/
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BCM_CHECKRECORD' AND KSQL_COL_NAME IN ('FDATASORTID')) 
 ALTER TABLE T_BCM_CHECKRECORD ADD (FDATASORTID BIGINT DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BCM_CHECKRECORDENTRY' AND KSQL_COL_NAME IN ('FENTRYDATASORT')) 
 ALTER TABLE T_BCM_CHECKRECORDENTRY ADD (FENTRYDATASORT BIGINT DEFAULT 0 NOT NULL );


IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BCM_CHKREPORT' AND KSQL_COL_NAME IN ('FMODIFYTIME')) 
 ALTER TABLE T_BCM_CHKREPORT ADD (FMODIFYTIME DATETIME );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BCM_CHKREPORT' AND KSQL_COL_NAME IN ('FMODIFIERID')) 
 ALTER TABLE T_BCM_CHKREPORT ADD (FMODIFIERID BIGINT DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_BCM_ISBASEENTLIST') 
 CREATE TABLE T_BCM_ISBASEENTLIST (
FID BIGINT DEFAULT 0 NOT NULL,
FNUMBER VARCHAR(50) DEFAULT ' ' NOT NULL,
FENTITYNUMBER VARCHAR(50) DEFAULT ' ' NOT NULL,
FSCHEMEID 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_BCM_ISBASEENTLIST_L') 
 CREATE TABLE T_BCM_ISBASEENTLIST_L (
FNAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FID BIGINT DEFAULT 0 NOT NULL,
FPKID BIGINT DEFAULT 0 NOT NULL,
FLOCALEID VARCHAR(36) DEFAULT ' ' NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_BCM_ISDIMMAP') 
 CREATE TABLE T_BCM_ISDIMMAP (
FID BIGINT DEFAULT 0 NOT NULL,
FSEQ INT DEFAULT 0 NOT NULL,
FNUMBER VARCHAR(50) DEFAULT ' ' NOT NULL,
FMAPPEDTYPE CHAR(1) DEFAULT '1' NOT NULL,
FSCHEMEID 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_BCM_ISDIMMAP_L') 
 CREATE TABLE T_BCM_ISDIMMAP_L (
FNAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FID BIGINT DEFAULT 0 NOT NULL,
FPKID BIGINT DEFAULT 0 NOT NULL,
FLOCALEID VARCHAR(36) DEFAULT ' ' NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_BCM_ISDIMMAPSRCDEFVAL_L') 
 CREATE TABLE T_BCM_ISDIMMAPSRCDEFVAL_L (
FSRCMEMNAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FID BIGINT DEFAULT 0 NOT NULL,
FPKID BIGINT DEFAULT 0 NOT NULL,
FLOCALEID VARCHAR(36) DEFAULT ' ' NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_BCM_ISDIMMAPTARGDEFVAL_L') 
 CREATE TABLE T_BCM_ISDIMMAPTARGDEFVAL_L (
FTAGMEMNAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FID BIGINT DEFAULT 0 NOT NULL,
FPKID BIGINT DEFAULT 0 NOT NULL,
FLOCALEID VARCHAR(36) DEFAULT ' ' NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_BCM_ISPRODUCT') 
 CREATE TABLE T_BCM_ISPRODUCT (
FID BIGINT DEFAULT 0 NOT NULL,
FNUMBER VARCHAR(50) DEFAULT ' ' 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_BCM_ISSCHEME') 
 CREATE TABLE T_BCM_ISSCHEME (
FID BIGINT DEFAULT 0 NOT NULL,
FNUMBER VARCHAR(50) DEFAULT ' ' NOT NULL,
FISSRCID BIGINT DEFAULT 0 NOT NULL,
FISTARGID BIGINT DEFAULT 0 NOT NULL,
FISSCHEMEID BIGINT DEFAULT 0 NOT NULL,
FSCHEMECATALOGID BIGINT DEFAULT 0 NOT NULL,
FMODELID BIGINT DEFAULT 0 NOT NULL,
FDESCRIPTION NVARCHAR(200) DEFAULT ' ' 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_BCM_ISSCHEME_L') 
 CREATE TABLE T_BCM_ISSCHEME_L (
FNAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FID BIGINT DEFAULT 0 NOT NULL,
FPKID BIGINT DEFAULT 0 NOT NULL,
FLOCALEID VARCHAR(36) DEFAULT ' ' NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BCM_DIMCOMBMAPPING' AND KSQL_COL_NAME IN ('FISAGE')) 
 ALTER TABLE T_BCM_DIMCOMBMAPPING ADD (FISAGE CHAR(1) DEFAULT '0' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_BCM_ISDIMMAPSRCDEFVAL') 
 CREATE TABLE T_BCM_ISDIMMAPSRCDEFVAL (
FDETAILID BIGINT DEFAULT 0 NOT NULL,
FENTRYID BIGINT DEFAULT 0 NOT NULL,
FSEQ INT DEFAULT 0 NOT NULL,
FMEMBID BIGINT DEFAULT 0 NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_BCM_ISDIMMAPSRCENTRY') 
 CREATE TABLE T_BCM_ISDIMMAPSRCENTRY (
FID BIGINT DEFAULT 0 NOT NULL,
FENTRYID BIGINT DEFAULT 0 NOT NULL,
FSEQ INT DEFAULT 0 NOT NULL,
FDIMTYPE NVARCHAR(30) DEFAULT ' ' NOT NULL,
FDIMENSIONID BIGINT DEFAULT 0 NOT NULL,
FBYFROM CHAR(1) DEFAULT '1' NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_BCM_ISDIMMAPTARGDEFVAL') 
 CREATE TABLE T_BCM_ISDIMMAPTARGDEFVAL (
FDETAILID BIGINT DEFAULT 0 NOT NULL,
FENTRYID BIGINT DEFAULT 0 NOT NULL,
FSEQ INT DEFAULT 0 NOT NULL,
FMEMBID BIGINT DEFAULT 0 NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_BCM_ISDIMMAPTARGENTRY') 
 CREATE TABLE T_BCM_ISDIMMAPTARGENTRY (
FID BIGINT DEFAULT 0 NOT NULL,
FENTRYID BIGINT DEFAULT 0 NOT NULL,
FSEQ INT DEFAULT 0 NOT NULL,
FDIMTYPE NVARCHAR(30) DEFAULT ' ' NOT NULL,
FDIMENSIONID BIGINT DEFAULT 0 NOT NULL,
FBYFROM CHAR(1) DEFAULT '1' NOT NULL
);


 EXEC p_AlterColumn 'T_BCM_DIMMEMBCOMBENTRY', 'FFLOATITEM', 'BIGINT', 'NOT NULL', '1111', '0';

IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BCM_DIMMEMBCOMBENTRY' AND KSQL_COL_NAME IN ('FACCESSFORMULA')) 
 ALTER TABLE T_BCM_DIMMEMBCOMBENTRY ADD (FACCESSFORMULA NCLOB );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BCM_DIMMEMBCOMBENTRY' AND KSQL_COL_NAME IN ('FTEXTSPREAD')) 
 ALTER TABLE T_BCM_DIMMEMBCOMBENTRY ADD (FTEXTSPREAD BIGINT DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_BCM_SCENECURRENCYSCALE') 
 CREATE TABLE T_BCM_SCENECURRENCYSCALE (
FID BIGINT DEFAULT 0 NOT NULL,
FENTRYID BIGINT DEFAULT 0 NOT NULL,
FSEQ INT DEFAULT 0 NOT NULL,
FCURRENCYID BIGINT DEFAULT 0 NOT NULL,
FSCALE INT DEFAULT 0 NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BCM_SCHEMEEXECUTEREC' AND KSQL_COL_NAME IN ('FCOPYEXEID')) 
 ALTER TABLE T_BCM_SCHEMEEXECUTEREC ADD (FCOPYEXEID BIGINT DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BCM_SCHEMEEXECUTEREC' AND KSQL_COL_NAME IN ('FPARAMJSON')) 
 ALTER TABLE T_BCM_SCHEMEEXECUTEREC ADD (FPARAMJSON NCLOB );

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