/**Begin D:\tools\PDM\pdm_upgrade\pdm1\ER_table.sql **/
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_ER_STANDARD_DIMENS')
CREATE TABLE T_ER_STANDARD_DIMENS (
                                      FID BIGINT DEFAULT 0 NOT NULL,
                                      FNUMBER VARCHAR(30) DEFAULT ' ' NOT NULL,
                                      FNAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
                                      FOPTIONTYPE VARCHAR(200) DEFAULT ' ' NOT NULL,
                                      FOPTIONRANGE VARCHAR(255) DEFAULT ' ' NOT NULL,
                                      FVALUESOURCE VARCHAR(36) DEFAULT ' ' NOT NULL,
                                      FSTATUS VARCHAR(10) DEFAULT ' ' NOT NULL,
                                      FCREATORID BIGINT DEFAULT 0 NOT NULL,
                                      FMODIFIERID BIGINT DEFAULT 0 NOT NULL,
                                      FENABLE VARCHAR(20) DEFAULT ' ' NOT NULL,
                                      FCREATETIME DATETIME,
                                      FMODIFYTIME DATETIME,
                                      FMASTERID BIGINT DEFAULT 0 NOT NULL,
                                      FCREATEORGID BIGINT DEFAULT 0 NOT NULL,
                                      FORGID BIGINT DEFAULT 0 NOT NULL,
                                      FCTRLSTRATEGY VARCHAR(50) DEFAULT ' ' NOT NULL,
                                      FSOURCEDATAID BIGINT DEFAULT 0 NOT NULL,
                                      FBITINDEX BIGINT DEFAULT 0 NOT NULL,
                                      FSOURCEBITINDEX BIGINT DEFAULT 0 NOT NULL
);


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


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_ER_STANDARD_SELECT_DIM')
CREATE TABLE T_ER_STANDARD_SELECT_DIM (
                                          FPKID BIGINT DEFAULT 0 NOT NULL,
                                          FID BIGINT DEFAULT 0 NOT NULL,
                                          FBASEDATAID BIGINT DEFAULT 0 NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_ER_STANDARD_TYPE')
CREATE TABLE T_ER_STANDARD_TYPE (
                                    FID BIGINT DEFAULT 0 NOT NULL,
                                    FNUMBER VARCHAR(100) DEFAULT ' ' NOT NULL,
                                    FNAME NVARCHAR(100) DEFAULT ' ' NOT NULL,
                                    FSTATUS VARCHAR(10) DEFAULT ' ' NOT NULL,
                                    FCREATORID BIGINT DEFAULT 0 NOT NULL,
                                    FMODIFIERID BIGINT DEFAULT 0 NOT NULL,
                                    FENABLE VARCHAR(10) DEFAULT ' ' NOT NULL,
                                    FCREATETIME DATETIME,
                                    FMODIFYTIME DATETIME,
                                    FMASTERID BIGINT DEFAULT 0 NOT NULL,
                                    FCREATEORGID BIGINT DEFAULT 0 NOT NULL,
                                    FORGID BIGINT DEFAULT 0 NOT NULL,
                                    FCTRLSTRATEGY VARCHAR(20) DEFAULT ' ' NOT NULL,
                                    FSOURCEDATAID BIGINT DEFAULT 0 NOT NULL,
                                    FBITINDEX BIGINT DEFAULT 0 NOT NULL,
                                    FSOURCEBITINDEX BIGINT DEFAULT 0 NOT NULL,
                                    FDESCRIBE VARCHAR(255) DEFAULT ' ' NOT NULL,
                                    FBILLTYPE VARCHAR(255) DEFAULT ' ' NOT NULL
);


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


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_ER_FEE_STANDARD')
CREATE TABLE T_ER_FEE_STANDARD (
                                   FID BIGINT DEFAULT 0 NOT NULL,
                                   FNUMBER VARCHAR(100) DEFAULT ' ' NOT NULL,
                                   FNAME NVARCHAR(100) DEFAULT ' ' NOT NULL,
                                   FSTATUS VARCHAR(10) DEFAULT ' ' NOT NULL,
                                   FCREATORID BIGINT DEFAULT 0 NOT NULL,
                                   FMODIFIERID BIGINT DEFAULT 0 NOT NULL,
                                   FENABLE VARCHAR(10) DEFAULT ' ' NOT NULL,
                                   FCREATETIME DATETIME,
                                   FMODIFYTIME DATETIME,
                                   FMASTERID BIGINT DEFAULT 0 NOT NULL,
                                   FCREATEORGID BIGINT DEFAULT 0 NOT NULL,
                                   FORGID BIGINT DEFAULT 0 NOT NULL,
                                   FCTRLSTRATEGY VARCHAR(50) DEFAULT ' ' NOT NULL,
                                   FSOURCEDATAID BIGINT DEFAULT 0 NOT NULL,
                                   FBITINDEX BIGINT DEFAULT 0 NOT NULL,
                                   FSOURCEBITINDEX BIGINT DEFAULT 0 NOT NULL,
                                   FBASEDATAFIELD BIGINT DEFAULT 0 NOT NULL,
                                   FEXPENSEITEM BIGINT DEFAULT 0 NOT NULL,
                                   FREIMBURSELEVEL BIGINT DEFAULT 0 NOT NULL,
                                   FCURRENCYID BIGINT DEFAULT 0 NOT NULL,
                                   FSTANDARDAMOUNT DECIMAL(23,10) DEFAULT 0 NOT NULL,
                                   FCTLPERIOD VARCHAR(10) DEFAULT ' ' NOT NULL,
                                   FCTLNUMBER INT DEFAULT 0 NOT NULL,
                                   FTREATTYPE VARCHAR(10) DEFAULT ' ' NOT NULL,
                                   FTREATGRADE VARCHAR(20) DEFAULT ' ' NOT NULL,
                                   FCITY VARCHAR(36) DEFAULT ' ' NOT NULL,
                                   FMEETGRADE VARCHAR(10) DEFAULT ' ' NOT NULL,
                                   FSTARDARDTYPE BIGINT DEFAULT 0 NOT NULL,
                                   FCOSTCOMPANY BIGINT DEFAULT 0 NOT NULL
);
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_ER_FEE_STANDARD' AND KSQL_COL_NAME ='FMEETTYPE') 
 ALTER TABLE T_ER_FEE_STANDARD ADD (FMEETTYPE NVARCHAR(10) DEFAULT ' ' NOT NULL );


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

 IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 't_er_standard_dimens' AND KSQL_COL_NAME ='fispreset')
 ALTER TABLE t_er_standard_dimens ADD (fispreset  CHAR(1) DEFAULT '0' NOT NULL );


IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 't_er_standard_type' AND KSQL_COL_NAME ='fispreset')
ALTER TABLE t_er_standard_type ADD (fispreset  CHAR(1) DEFAULT '0' NOT NULL );

/**  业务维度与标准类型的列表增加 系统预置 字段 **/
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_ER_FEE_STANDARD' AND KSQL_COL_NAME IN ('FDIMENSION'))
ALTER TABLE T_ER_FEE_STANDARD ADD (FDIMENSION NVARCHAR(512) DEFAULT ' ' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_ER_FEE_STANDARD' AND KSQL_COL_NAME IN ('FCALCEXPR'))
ALTER TABLE T_ER_FEE_STANDARD ADD (FCALCEXPR NVARCHAR(512) DEFAULT ' ' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_ER_FEE_STANDARD' AND KSQL_COL_NAME IN ('FTREATWAY'))
ALTER TABLE T_ER_FEE_STANDARD ADD (FTREATWAY NVARCHAR(10) DEFAULT ' ' NOT NULL );


/**Begin D:\tools\PDM\pdm_upgrade\pdm1\ER_constraint.sql **/
EXEC P_ALTERPK 'PK_T_ER_STANDARD_DIMENS', 'T_ER_STANDARD_DIMENS', 'FID', '1';

 IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'FNUMBER_INDEX') CREATE INDEX FNUMBER_INDEX ON T_ER_STANDARD_DIMENS ( FNUMBER );


EXEC P_ALTERPK 'PK_T_ER_STANDARD_DIMENS_L', 'T_ER_STANDARD_DIMENS_L', 'FPKID', '1';

 IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_ER_STANDARD_DIMENS_L_0') CREATE INDEX IDX_ER_STANDARD_DIMENS_L_0 ON T_ER_STANDARD_DIMENS_L ( FID ,FLOCALEID );


EXEC P_ALTERPK 'PK_T_ER_STANDARD_SELECT_DIM', 'T_ER_STANDARD_SELECT_DIM', 'FPKID', '1';

 IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_ER_STANDARD_SELECT_DIM_FK') CREATE INDEX IDX_ER_STANDARD_SELECT_DIM_FK ON T_ER_STANDARD_SELECT_DIM ( FID );


EXEC P_ALTERPK 'PK_T_ER_STANDARD_TYPE', 'T_ER_STANDARD_TYPE', 'FID', '1';

 IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_STANDARD_TYPE_CREATEORG') CREATE INDEX IDX_STANDARD_TYPE_CREATEORG ON T_ER_STANDARD_TYPE ( FCREATEORGID );

 IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_T_ER_STANDARD_TYPE_MASTER') CREATE INDEX IDX_T_ER_STANDARD_TYPE_MASTER ON T_ER_STANDARD_TYPE ( FMASTERID );


EXEC P_ALTERPK 'PK_T_ER_STANDARD_TYPE_L', 'T_ER_STANDARD_TYPE_L', 'FPKID', '1';

 IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_ER_STANDARD_TYPE_L_0') CREATE INDEX IDX_ER_STANDARD_TYPE_L_0 ON T_ER_STANDARD_TYPE_L ( FID ,FLOCALEID );


EXEC P_ALTERPK 'PK_T_ER_FEE_STANDARD', 'T_ER_FEE_STANDARD', 'FID', '1';

 IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_FEE_STANDARD_CREATEORG') CREATE INDEX IDX_FEE_STANDARD_CREATEORG ON T_ER_FEE_STANDARD ( FCREATEORGID );

 IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_T_ER_FEE_STANDARD_MASTER') CREATE INDEX IDX_T_ER_FEE_STANDARD_MASTER ON T_ER_FEE_STANDARD ( FMASTERID );


EXEC P_ALTERPK 'PK_T_ER_FEE_STANDARD_L', 'T_ER_FEE_STANDARD_L', 'FPKID', '1';

 IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_ER_FEE_STANDARD_L_0') CREATE INDEX IDX_ER_FEE_STANDARD_L_0 ON T_ER_FEE_STANDARD_L ( FID ,FLOCALEID );


