/**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_EXCHANGERATE' AND KSQL_COL_NAME IN ('FUSERDEFINE20')) 
 ALTER TABLE T_BCM_EXCHANGERATE ADD (FUSERDEFINE20 DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BCM_EXCHANGERATE' AND KSQL_COL_NAME IN ('FUSERDEFINE19')) 
 ALTER TABLE T_BCM_EXCHANGERATE ADD (FUSERDEFINE19 DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BCM_EXCHANGERATE' AND KSQL_COL_NAME IN ('FUSERDEFINE18')) 
 ALTER TABLE T_BCM_EXCHANGERATE ADD (FUSERDEFINE18 DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BCM_EXCHANGERATE' AND KSQL_COL_NAME IN ('FUSERDEFINE17')) 
 ALTER TABLE T_BCM_EXCHANGERATE ADD (FUSERDEFINE17 DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BCM_EXCHANGERATE' AND KSQL_COL_NAME IN ('FUSERDEFINE16')) 
 ALTER TABLE T_BCM_EXCHANGERATE ADD (FUSERDEFINE16 DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BCM_EXCHANGERATE' AND KSQL_COL_NAME IN ('FUSERDEFINE15')) 
 ALTER TABLE T_BCM_EXCHANGERATE ADD (FUSERDEFINE15 DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BCM_EXCHANGERATE' AND KSQL_COL_NAME IN ('FUSERDEFINE14')) 
 ALTER TABLE T_BCM_EXCHANGERATE ADD (FUSERDEFINE14 DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BCM_EXCHANGERATE' AND KSQL_COL_NAME IN ('FUSERDEFINE13')) 
 ALTER TABLE T_BCM_EXCHANGERATE ADD (FUSERDEFINE13 DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BCM_EXCHANGERATE' AND KSQL_COL_NAME IN ('FUSERDEFINE12')) 
 ALTER TABLE T_BCM_EXCHANGERATE ADD (FUSERDEFINE12 DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BCM_EXCHANGERATE' AND KSQL_COL_NAME IN ('FUSERDEFINE11')) 
 ALTER TABLE T_BCM_EXCHANGERATE ADD (FUSERDEFINE11 DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BCM_EXCHANGERATE' AND KSQL_COL_NAME IN ('FUSERDEFINE10')) 
 ALTER TABLE T_BCM_EXCHANGERATE ADD (FUSERDEFINE10 DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BCM_EXCHANGERATE' AND KSQL_COL_NAME IN ('FUSERDEFINE9')) 
 ALTER TABLE T_BCM_EXCHANGERATE ADD (FUSERDEFINE9 DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BCM_EXCHANGERATE' AND KSQL_COL_NAME IN ('FUSERDEFINE8')) 
 ALTER TABLE T_BCM_EXCHANGERATE ADD (FUSERDEFINE8 DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BCM_EXCHANGERATE' AND KSQL_COL_NAME IN ('FUSERDEFINE7')) 
 ALTER TABLE T_BCM_EXCHANGERATE ADD (FUSERDEFINE7 DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BCM_EXCHANGERATE' AND KSQL_COL_NAME IN ('FUSERDEFINE6')) 
 ALTER TABLE T_BCM_EXCHANGERATE ADD (FUSERDEFINE6 DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BCM_EXCHANGERATE' AND KSQL_COL_NAME IN ('FUSERDEFINE5')) 
 ALTER TABLE T_BCM_EXCHANGERATE ADD (FUSERDEFINE5 DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BCM_EXCHANGERATE' AND KSQL_COL_NAME IN ('FUSERDEFINE4')) 
 ALTER TABLE T_BCM_EXCHANGERATE ADD (FUSERDEFINE4 DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BCM_EXCHANGERATE' AND KSQL_COL_NAME IN ('FUSERDEFINE3')) 
 ALTER TABLE T_BCM_EXCHANGERATE ADD (FUSERDEFINE3 DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BCM_EXCHANGERATE' AND KSQL_COL_NAME IN ('FUSERDEFINE2')) 
 ALTER TABLE T_BCM_EXCHANGERATE ADD (FUSERDEFINE2 DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BCM_EXCHANGERATE' AND KSQL_COL_NAME IN ('FUSERDEFINE1')) 
 ALTER TABLE T_BCM_EXCHANGERATE ADD (FUSERDEFINE1 DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BCM_CHECKTMPLENTRY' AND KSQL_COL_NAME IN ('FDATASORTID')) 
 ALTER TABLE T_BCM_CHECKTMPLENTRY ADD (FDATASORTID BIGINT DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_BCM_ACCOUNTDIMENSION') 
 CREATE TABLE T_BCM_ACCOUNTDIMENSION (
FID BIGINT DEFAULT 0 NOT NULL,
FENTRYID BIGINT DEFAULT 0 NOT NULL,
FDEFINDIMSENSION BIGINT DEFAULT 0 NOT NULL,
FSEQ INT DEFAULT 0 NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_BCM_ACCOUNTMETRIC') 
 CREATE TABLE T_BCM_ACCOUNTMETRIC (
FID BIGINT DEFAULT 0 NOT NULL,
FENTRYID BIGINT DEFAULT 0 NOT NULL,
FMETRIC BIGINT DEFAULT 0 NOT NULL,
FISTOPOLY CHAR(1) DEFAULT '0' NOT NULL,
FDATABYTIME NVARCHAR(30) DEFAULT ' ' NOT NULL,
FSKIP NVARCHAR(30) DEFAULT ' ' NOT NULL,
FMETRICDESCRIPTION NVARCHAR(510) DEFAULT ' ' NOT NULL,
FSEQ INT DEFAULT 0 NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_BCM_DATETYPEEDITCYCLE') 
 CREATE TABLE T_BCM_DATETYPEEDITCYCLE (
FID BIGINT DEFAULT 0 NOT NULL,
FENTRYID BIGINT DEFAULT 0 NOT NULL,
FSEQ INT DEFAULT 0 NOT NULL,
FSTARTDATE DATETIME,
FENDDATE DATETIME,
FSTATUS CHAR(1) DEFAULT '0' NOT NULL,
FMODELID BIGINT DEFAULT 0 NOT NULL,
FDATATYPEMEMID BIGINT DEFAULT 0 NOT NULL
);


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


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_BCM_MEMBOFDATTTYPE') 
 CREATE TABLE T_BCM_MEMBOFDATTTYPE (
FID BIGINT DEFAULT 0 NOT NULL,
FNAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FNUMBER NVARCHAR(50) DEFAULT ' ' NOT NULL,
FSIMPLENAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FDIMENSIONID BIGINT DEFAULT 0 NOT NULL,
FMODELID BIGINT DEFAULT 0 NOT NULL,
FSTATUS VARCHAR(5) DEFAULT ' ' NOT NULL,
FENABLE VARCHAR(5) DEFAULT ' ' NOT NULL,
FDISABLERID BIGINT DEFAULT 0 NOT NULL,
FDISABLEDATE DATETIME,
FCREATORID BIGINT DEFAULT 0 NOT NULL,
FCREATETIME DATETIME,
FMODIFIERID BIGINT DEFAULT 0 NOT NULL,
FMODIFYTIME DATETIME,
FORGID BIGINT DEFAULT 0 NOT NULL,
FMASTERID BIGINT DEFAULT 0 NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_BCM_STRUCTOFDATETYPE') 
 CREATE TABLE T_BCM_STRUCTOFDATETYPE (
FID BIGINT DEFAULT 0 NOT NULL,
FLONGNUMBER NVARCHAR(200) DEFAULT ' ' NOT NULL,
FMEMBERID BIGINT DEFAULT 0 NOT NULL,
FPARENTID BIGINT DEFAULT 0 NOT NULL,
FISLEAF CHAR(1) DEFAULT ' ' NOT NULL,
FLEVEL INT DEFAULT 0 NOT NULL,
FSEQ INT DEFAULT 0 NOT NULL,
FSTORAGETYPE CHAR(1) DEFAULT ' ' NOT NULL,
FAGGOPRT CHAR(1) DEFAULT ' ' NOT NULL,
FSTATUS VARCHAR(5) DEFAULT ' ' NOT NULL,
FENABLE VARCHAR(5) DEFAULT ' ' NOT NULL,
FDISABLERID BIGINT DEFAULT 0 NOT NULL,
FDISABLEDATE DATETIME,
FCREATORID BIGINT DEFAULT 0 NOT NULL,
FCREATETIME DATETIME,
FMODIFIERID BIGINT DEFAULT 0 NOT NULL,
FMODIFYTIME DATETIME,
FDIMENSIONID BIGINT DEFAULT 0 NOT NULL,
FMODELID BIGINT DEFAULT 0 NOT NULL,
FNUMBER NVARCHAR(50) DEFAULT ' ' NOT NULL,
FSHAREALL INT DEFAULT 0 NOT NULL,
FISSYSMEMBER INT DEFAULT 0 NOT NULL,
FCOPYFROM BIGINT DEFAULT 0 NOT NULL,
FMASTERID BIGINT DEFAULT 0 NOT NULL,
FREFERID BIGINT DEFAULT 0 NOT NULL,
FVERSIONTYPE NVARCHAR(30) DEFAULT ' ' NOT NULL,
FEDITTYPE NVARCHAR(30) DEFAULT ' ' NOT NULL,
FSTARTDATE DATETIME,
FENDDATE DATETIME,
FDIMENSIONRANG NVARCHAR(100) DEFAULT ' ' NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_BCM_STRUCTOFDATETYPE_L') 
 CREATE TABLE T_BCM_STRUCTOFDATETYPE_L (
FPKID VARCHAR(18) DEFAULT ' ' NOT NULL,
FID BIGINT DEFAULT 0 NOT NULL,
FLOCALEID VARCHAR(10) DEFAULT ' ' NOT NULL,
FDESCRIPTION NVARCHAR(255) DEFAULT ' ' NOT NULL,
FNAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FSIMPLENAME NVARCHAR(50) DEFAULT ' ' NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BCM_DATETYPETODIM' AND KSQL_COL_NAME IN ('FDIMNAME')) 
 ALTER TABLE T_BCM_DATETYPETODIM ADD (FDIMNAME NVARCHAR(100) DEFAULT '0' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BCM_TEMPLATE' AND KSQL_COL_NAME IN ('FISOLDTEMPLATE')) 
 ALTER TABLE T_BCM_TEMPLATE ADD (FISOLDTEMPLATE CHAR(1) DEFAULT '1' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_BCM_METRIC') 
 CREATE TABLE T_BCM_METRIC (
FID BIGINT DEFAULT 0 NOT NULL,
FCODE NVARCHAR(50) DEFAULT ' ' NOT NULL,
FNAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FENUM NVARCHAR(30) DEFAULT ' ' NOT NULL,
FTYPE NVARCHAR(30) DEFAULT ' ' NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_BCM_LINKAGE') 
 CREATE TABLE T_BCM_LINKAGE (
FID BIGINT DEFAULT 0 NOT NULL,
FMODELID BIGINT DEFAULT 0 NOT NULL,
FSCENARIOID BIGINT DEFAULT 0 NOT NULL,
FNUMBER NVARCHAR(50) DEFAULT ' ' NOT NULL,
FNAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FDESCRIPTION VARCHAR(500) DEFAULT ' ' NOT NULL,
FMODIFIERID BIGINT DEFAULT 0 NOT NULL,
FMODIFYTIME DATETIME,
FSTATUS CHAR(1) DEFAULT '1' NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_BCM_RIGHTINFO') 
 CREATE TABLE T_BCM_RIGHTINFO (
FID BIGINT DEFAULT 0 NOT NULL,
FORG BIGINT DEFAULT 0 NOT NULL,
FCURRENCY BIGINT DEFAULT 0 NOT NULL,
FRIGHTACCOUNT BIGINT DEFAULT 0 NOT NULL,
FCHANGETYPE BIGINT DEFAULT 0 NOT NULL,
FCHANGETIME DATETIME,
FECSUM DECIMAL(23,10) DEFAULT 0 NOT NULL,
FMODELID BIGINT DEFAULT 0 NOT NULL,
FICENTITY BIGINT DEFAULT 0 NOT NULL,
FRULE BIGINT DEFAULT 0 NOT NULL,
FDATASORT BIGINT DEFAULT 0 NOT NULL,
FDIM1 BIGINT DEFAULT 0 NOT NULL,
FDIM2 BIGINT DEFAULT 0 NOT NULL,
FDIM3 BIGINT DEFAULT 0 NOT NULL,
FDIM4 BIGINT DEFAULT 0 NOT NULL,
FDIM5 BIGINT DEFAULT 0 NOT NULL,
FDIM6 BIGINT DEFAULT 0 NOT NULL,
FSCENE BIGINT DEFAULT 0 NOT NULL,
FPROCESS BIGINT DEFAULT 0 NOT NULL,
FAUDITTRIAL BIGINT DEFAULT 0 NOT NULL,
FMODIFIER BIGINT DEFAULT 0 NOT NULL,
FMODIFYDATE DATETIME
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_BCM_RIGHTINFOENTRY') 
 CREATE TABLE T_BCM_RIGHTINFOENTRY (
FENTRYID BIGINT DEFAULT 0 NOT NULL,
FID BIGINT DEFAULT 0 NOT NULL,
FPC BIGINT DEFAULT 0 NOT NULL,
FEXCHAGERATE DECIMAL(23,10) DEFAULT 0 NOT NULL,
FPCSUM DECIMAL(23,10) DEFAULT 0 NOT NULL,
FSEQ INT DEFAULT 0 NOT NULL
);


 EXEC p_AlterColumn 'T_BCM_CHKNOTEINFO', 'FDIMSTR', 'VARCHAR(500)', 'NOT NULL', '0100', ''' ''';

IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BCM_DIMMEMBCOMBENTRY' AND KSQL_COL_NAME IN ('FGROUP')) 
 ALTER TABLE T_BCM_DIMMEMBCOMBENTRY ADD (FGROUP INT DEFAULT 0 NOT NULL );
 EXEC p_AlterColumn 'T_BCM_DIMMAPINGENTRY', 'FSRCTOCOMBINE', 'INT', 'NOT NULL', '1111', '0';

 EXEC p_AlterColumn 'T_BCM_DIMMAPINGENTRY', 'FTAGTOCOMBINE', 'INT', 'NOT NULL', '1111', '0';

IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BCM_RPTADJUST' AND KSQL_COL_NAME IN ('FMODIFYSTATUS')) 
 ALTER TABLE T_BCM_RPTADJUST ADD (FMODIFYSTATUS CHAR(1) DEFAULT '0' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BCM_RPTADJUSTDATA' AND KSQL_COL_NAME IN ('FMAPPINGENTRYID')) 
 ALTER TABLE T_BCM_RPTADJUSTDATA ADD (FMAPPINGENTRYID BIGINT DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_BCM_LINKAGEENTRY') 
 CREATE TABLE T_BCM_LINKAGEENTRY (
FID BIGINT DEFAULT 0 NOT NULL,
FENTRYID BIGINT DEFAULT 0 NOT NULL,
FSEQ INT DEFAULT 0 NOT NULL,
FGROUPNUM BIGINT DEFAULT 0 NOT NULL,
FGROUPNAME NVARCHAR(30) DEFAULT ' ' NOT NULL,
FOPERATOR CHAR(1) DEFAULT '1' NOT NULL,
FACCOUNTID BIGINT DEFAULT 0 NOT NULL,
FLINKAGETYPE CHAR(1) DEFAULT '1' NOT NULL,
FWEIGHT DECIMAL(23,10) DEFAULT 0 NOT NULL,
FTEMPLATEID BIGINT DEFAULT 0 NOT NULL,
FCHANGETYPEID BIGINT DEFAULT 0 NOT NULL,
FINTERCOMPANYID BIGINT DEFAULT 0 NOT NULL,
FUSERDEFINED1ID BIGINT DEFAULT 0 NOT NULL,
FUSERDEFINED2ID BIGINT DEFAULT 0 NOT NULL,
FUSERDEFINED3ID BIGINT DEFAULT 0 NOT NULL,
FUSERDEFINED4ID BIGINT DEFAULT 0 NOT NULL,
FUSERDEFINED5ID BIGINT DEFAULT 0 NOT NULL,
FUSERDEFINED6ID BIGINT DEFAULT 0 NOT NULL,
FDIMSHOW NVARCHAR(500) DEFAULT ' ' NOT NULL,
FACCOUNTRANGE INT DEFAULT 0 NOT NULL,
FCHANGETYPERANGE INT DEFAULT 0 NOT NULL,
FINTERCOMPANYRANGE INT DEFAULT 0 NOT NULL,
FUSERDEFINEDRANGE1 INT DEFAULT 0 NOT NULL,
FUSERDEFINEDRANGE2 INT DEFAULT 0 NOT NULL,
FUSERDEFINEDRANGE3 INT DEFAULT 0 NOT NULL,
FUSERDEFINEDRANGE4 INT DEFAULT 0 NOT NULL,
FUSERDEFINEDRANGE5 INT DEFAULT 0 NOT NULL,
FUSERDEFINEDRANGE6 INT DEFAULT 0 NOT NULL
);


 EXEC p_AlterColumn 'T_BCM_CHKATTDETAIL', 'FATTTYPE', 'VARCHAR(200)', 'NOT NULL', '1111', ''' ''';


/**End E:\NextERP_PDM\PDM_UPGRADE\PDMS\branch_lastest(平台_业务)\pdm\BCM_table.sql **/
