/** 新差旅业务改造表相关改动语句 **/
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_ER_STANDARD_TYPE' AND KSQL_COL_NAME ='FISTRIP')
ALTER TABLE T_ER_STANDARD_TYPE ADD (FISTRIP CHAR(1) DEFAULT '0' NOT NULL);

IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_ER_TRIP_DIMENSION')
CREATE TABLE T_ER_TRIP_DIMENSION (
                                     FID BIGINT DEFAULT 0 NOT NULL,
                                     FENTRYID BIGINT DEFAULT 0 NOT NULL,
                                     FSEQ BIGINT DEFAULT 0 NOT NULL,
                                     FTRIPATTRIBUTE NVARCHAR(50) DEFAULT ' ' NOT NULL
);

IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_ER_STANDARD_TRIP_DIM')
CREATE TABLE T_ER_STANDARD_TRIP_DIM (
                                        FPKID BIGINT DEFAULT 0 NOT NULL,
                                        FENTRYID 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_TRIPITEM')
CREATE TABLE T_ER_STANDARD_TRIPITEM (
                                        FID BIGINT DEFAULT 0 NOT NULL,
                                        FENTRYID BIGINT DEFAULT 0 NOT NULL,
                                        FSEQ BIGINT DEFAULT 0 NOT NULL,
                                        FTRIPEXPENSEITEM BIGINT DEFAULT 0 NOT NULL
);

/**主键及索引相关改动**/
EXEC P_ALTERPK 'PK_T_ER_TRIP_DIMENSION', 'T_ER_TRIP_DIMENSION', 'FENTRYID', '1';

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

EXEC P_ALTERPK 'PK_ER_STANDARD_TRIP_DIM', 'T_ER_STANDARD_TRIP_DIM', 'FPKID', '1';

IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_ER_TRIP_DIM_ID') CREATE INDEX IDX_ER_TRIP_DIM_ID ON T_ER_STANDARD_TRIP_DIM ( FENTRYID );

EXEC P_ALTERPK 'PK_ER_STANDARD_TRIPITEM', 'T_ER_STANDARD_TRIPITEM', 'FENTRYID', '1';

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

/** 差旅标准增加语句 **/
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_ER_TRIP_STANDARD')
CREATE TABLE T_ER_TRIP_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,
    FSRCCREATEORGID   BIGINT          DEFAULT 0   NOT NULL,
    FSTARDARDTYPE     BIGINT          DEFAULT 0   NOT NULL,
    FTRIPEXPENSEITEM  BIGINT          DEFAULT 0   NOT NULL,
    FCURRENCY         BIGINT          DEFAULT 0   NOT NULL,
    FSTANDARDAMOUNT   DECIMAL(23, 10) DEFAULT 0   NOT NULL,
    FDIMENSION        NVARCHAR(2000)  DEFAULT ' ' NOT NULL,
    FCALCEXPR         NVARCHAR(50)    DEFAULT ' ' NOT NULL,
    FPEAKSEASONAMOUNT DECIMAL(23, 10) DEFAULT 0   NOT NULL,
    FTRIPAREA         BIGINT          DEFAULT 0   NOT NULL,
    FISLADDERSUBSIDY  CHAR(1)         DEFAULT '0' NOT NULL,
    FACTUALREIMTRIP   CHAR(1)         DEFAULT '0' NOT NULL,
    FEFFECTIVEDATE    DATETIME
);

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

IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_ER_STEP_STANDARD')
CREATE TABLE T_ER_STEP_STANDARD(
    FID                 BIGINT          DEFAULT 0   NOT NULL,
    FSEQ                BIGINT          DEFAULT 0   NOT NULL,
    FENTRYID            BIGINT          DEFAULT 0   NOT NULL,
    FSTARTDAY           DECIMAL(23, 10) DEFAULT 0   NOT NULL,
    FENDDAY             DECIMAL(23, 10) DEFAULT 0   NOT NULL,
    FSTARTMILEAGE       DECIMAL(23, 10) DEFAULT 0   NOT NULL,
    FENDMILEAGE         DECIMAL(23, 10) DEFAULT 0   NOT NULL,
    FSTEPSTANDARDAMOUNT DECIMAL(23, 10) DEFAULT 0   NOT NULL,
    FENTRYREMARK        NVARCHAR(100)   DEFAULT ' ' NOT NULL,
    FENTRYKEY           NVARCHAR(50)    DEFAULT ' ' NOT NULL
);

IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_ER_STEP_STANDARD_L')
CREATE TABLE T_ER_STEP_STANDARD_L(
    FENTRYID     BIGINT        DEFAULT 0   NOT NULL,
    FPKID        NVARCHAR(50)  DEFAULT ' ' NOT NULL,
    FENTRYREMARK NVARCHAR(200) DEFAULT ' ' NOT NULL,
    FLOCALEID    NVARCHAR(50)  DEFAULT ' ' NOT NULL
);

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

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

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

/**主键及索引相关改动**/
IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_ER_TRIP_STANDARD_NUMBER')
CREATE INDEX IDX_ER_TRIP_STANDARD_NUMBER ON T_ER_TRIP_STANDARD (FNUMBER);

EXEC P_ALTERPK 'PK_T_ER_TRIP_STANDARD_ID', 'T_ER_TRIP_STANDARD', 'FID', '1';

EXEC P_ALTERPK 'PK_T_ER_TRIP_STANDARD_L_ID', 'T_ER_TRIP_STANDARD_L', 'FPKID', '1';
IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_ER_TRIP_STANDARD_L')
CREATE INDEX IDX_ER_TRIP_STANDARD_L ON T_ER_TRIP_STANDARD_L (FID);

EXEC P_ALTERPK 'PK_T_ER_SELECT_TRIPTYPE', 'T_ER_SELECT_TRIPTYPE', 'FPKID', '1';
IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_ER_SELECT_TRIPTYPE')
CREATE INDEX IDX_ER_SELECT_TRIPTYPE ON T_ER_SELECT_TRIPTYPE (FID);

EXEC P_ALTERPK 'PK_T_ER_REIMBURSELEVE', 'T_ER_SELECT_REIMBURSELEVE', 'FPKID', '1';
IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_ER_SELECT_REIMBURSELEVE')
CREATE INDEX IDX_ER_SELECT_REIMBURSELEVE ON T_ER_SELECT_REIMBURSELEVE (FID);

EXEC P_ALTERPK 'PK_T_ER_INCLUDEPERSON', 'T_ER_INCLUDEPERSON', 'FPKID', '1';
IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_ER_INCLUDEPERSON')
CREATE INDEX IDX_ER_INCLUDEPERSON ON T_ER_INCLUDEPERSON (FID);

EXEC P_ALTERPK 'PK_ER_STEP_STANDARD', 'T_ER_STEP_STANDARD', 'FENTRYID', '1';
IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_ER_STEP_STANDARD')
CREATE INDEX IDX_ER_STEP_STANDARD ON T_ER_STEP_STANDARD (FID);

EXEC P_ALTERPK 'PK_ER_STEP_STANDARD_L', 'T_ER_STEP_STANDARD_L', 'FPKID', '1';
IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_ER_STEP_STANDARD_L')
CREATE INDEX IDX_ER_STEP_STANDARD_L ON T_ER_STEP_STANDARD_L (FENTRYID);




