/**新差旅报销单 **/

/* 单头T_ER_ReimburseBill表新增的字段*/
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_ER_REIMBURSEBILL' AND KSQL_COL_NAME ='FSTDBILLTYPE') 
 ALTER TABLE T_ER_REIMBURSEBILL ADD (FSTDBILLTYPE BIGINT DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_ER_REIMBURSEBILL' AND KSQL_COL_NAME ='FHEADEXPENSEITEM') 
 ALTER TABLE T_ER_REIMBURSEBILL ADD (FHEADEXPENSEITEM BIGINT DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_ER_REIMBURSEBILL' AND KSQL_COL_NAME ='FHEADPROJECT') 
 ALTER TABLE T_ER_REIMBURSEBILL ADD (FHEADPROJECT BIGINT DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_ER_REIMBURSEBILL' AND KSQL_COL_NAME ='FBILLKIND') 
 ALTER TABLE T_ER_REIMBURSEBILL ADD (FBILLKIND CHAR(1) DEFAULT '0' NOT NULL );

/**行程二级明细添加出发日期, 到达日期字段*/
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_ER_REIMBURSEENTRY' AND KSQL_COL_NAME ='FTRIP2STARTDATE') 
 ALTER TABLE T_ER_REIMBURSEENTRY ADD (FTRIP2STARTDATE DATETIME);

IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_ER_REIMBURSEENTRY' AND KSQL_COL_NAME ='FTRIP2ENDDATE') 
 ALTER TABLE T_ER_REIMBURSEENTRY ADD (FTRIP2ENDDATE DATETIME);


/*差旅二级明细分录分表1 T_ER_REIMBURSEENTRY_A*/
/*建表*/
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_ER_REIMBURSEENTRY_A') 
 CREATE TABLE T_ER_REIMBURSEENTRY_A (
    FDETAILID              BIGINT               DEFAULT 0   NOT NULL,
    FENTRYID               BIGINT               DEFAULT 0   NOT NULL,
    FTRIP2FROM             BIGINT               DEFAULT 0   NOT NULL,
    FTRIP2TO               BIGINT               DEFAULT 0   NOT NULL,
    FTRIP2TRIPDAY          DECIMAL(23,10)       DEFAULT 0   NOT NULL,
    FTRIP2TRAVELERSCOUNT   INT                  DEFAULT 0   NOT NULL,
    FTRIP2ORISHAREDAMOUNT  DECIMAL(23,10)       DEFAULT 0   NOT NULL,
    FTRIP2SHAREDAMOUNT     DECIMAL(23,10)       DEFAULT 0   NOT NULL,
    FISOVER                CHAR(1)              DEFAULT '0' NOT NULL
);
/*主键*/
EXEC P_ALTERPK 'PK_ER_REIMBURSEENTRY_A', 'T_ER_REIMBURSEENTRY_A', 'FDETAILID', '1';
/*索引*/
IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_T_ER_REIMBURSEENTRY_A_FSEQ') CREATE INDEX IDX_T_ER_REIMBURSEENTRY_A_FSEQ ON T_ER_REIMBURSEENTRY_A ( FENTRYID );


/*差旅二级明细分录出差人多选基础资料表 T_ER_TRIP2REIMPARTNER*/ 
/*建表*/
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_ER_TRIP2REIMPARTNER') 
 CREATE TABLE T_ER_TRIP2REIMPARTNER (
FPKID BIGINT DEFAULT 0 NOT NULL,
FDETAILID BIGINT DEFAULT 0 NOT NULL,
FBASEDATAID BIGINT DEFAULT 0 NOT NULL
);
/*主键*/
EXEC P_ALTERPK 'PK_ER_TRIP2REIMPARTNER', 'T_ER_TRIP2REIMPARTNER', 'FPKID', '1';
/*索引*/
IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_ER_TRIP2REIMPARTNER') CREATE INDEX IDX_ER_TRIP2REIMPARTNER ON T_ER_TRIP2REIMPARTNER ( FDETAILID );


/*差旅二级明细分录多选途径地表基础资料表 T_ER_TRIP2MULWAYTO*/
/*建表*/
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_ER_TRIP2MULWAYTO') 
 CREATE TABLE T_ER_TRIP2MULWAYTO (
FPKID BIGINT DEFAULT 0 NOT NULL,
FDETAILID BIGINT DEFAULT 0 NOT NULL,
FBASEDATAID BIGINT DEFAULT 0 NOT NULL
);
/*主键*/
EXEC P_ALTERPK 'PK_ER_TRIP2MULWAYTO', 'T_ER_TRIP2MULWAYTO', 'FPKID', '1';
/*索引*/
IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_ER_TRIP2MULWAYTO_ENTRYID') CREATE INDEX IDX_ER_TRIP2MULWAYTO_ENTRYID ON T_ER_TRIP2MULWAYTO ( FDETAILID );

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