
/*** 费用标准功能新增的单据字段 -- 费用申请单单头+分录 **/
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_ER_DAILYAPPLYDETAIL' AND KSQL_COL_NAME ='FTREATTYPE') 
 ALTER TABLE T_ER_DAILYAPPLYDETAIL ADD (FTREATTYPE NVARCHAR(10) DEFAULT ' ' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_ER_DAILYAPPLYDETAIL' AND KSQL_COL_NAME ='FTREATWAY') 
 ALTER TABLE T_ER_DAILYAPPLYDETAIL ADD (FTREATWAY NVARCHAR(10) DEFAULT ' ' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_ER_DAILYAPPLYDETAIL' AND KSQL_COL_NAME ='FMEETTYPE') 
 ALTER TABLE T_ER_DAILYAPPLYDETAIL ADD (FMEETTYPE NVARCHAR(10) DEFAULT ' ' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_ER_DAILYAPPLYDETAIL' AND KSQL_COL_NAME ='FMEETLEVEL') 
 ALTER TABLE T_ER_DAILYAPPLYDETAIL ADD (FMEETLEVEL NVARCHAR(10) DEFAULT ' ' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_ER_DAILYAPPLYDETAIL' AND KSQL_COL_NAME ='FCITYAREA') 
 ALTER TABLE T_ER_DAILYAPPLYDETAIL ADD (FCITYAREA BIGINT DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_ER_DAILYAPPLYDETAIL' AND KSQL_COL_NAME ='FREIMBURSELEVEL') 
 ALTER TABLE T_ER_DAILYAPPLYDETAIL ADD (FREIMBURSELEVEL BIGINT DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_ER_DAILYAPPLYDETAIL' AND KSQL_COL_NAME ='FFEESTANDARDAMT') 
 ALTER TABLE T_ER_DAILYAPPLYDETAIL ADD (FFEESTANDARDAMT DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_ER_DAILYAPPLYDETAIL' AND KSQL_COL_NAME ='FFEESTANDARDDESC') 
 ALTER TABLE T_ER_DAILYAPPLYDETAIL ADD (FFEESTANDARDDESC NVARCHAR(255) DEFAULT ' ' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_ER_DAILYAPPLYDETAIL' AND KSQL_COL_NAME ='FTREATNUM') 
 ALTER TABLE T_ER_DAILYAPPLYDETAIL ADD (FTREATNUM INT DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_ER_DAILYAPPLYDETAIL' AND KSQL_COL_NAME ='FESCORTNUM') 
 ALTER TABLE T_ER_DAILYAPPLYDETAIL ADD (FESCORTNUM INT DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_ER_DAILYAPPLYDETAIL' AND KSQL_COL_NAME ='FGOODSNUM') 
 ALTER TABLE T_ER_DAILYAPPLYDETAIL ADD (FGOODSNUM INT DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_ER_DAILYAPPLYDETAIL' AND KSQL_COL_NAME ='FDAYSNUM') 
 ALTER TABLE T_ER_DAILYAPPLYDETAIL ADD (FDAYSNUM INT DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_ER_DAILYAPPLYDETAIL' AND KSQL_COL_NAME ='FCONTROLAMT') 
 ALTER TABLE T_ER_DAILYAPPLYDETAIL ADD (FCONTROLAMT DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_ER_DAILYAPPLYDETAIL' AND KSQL_COL_NAME ='FISOVER') 
 ALTER TABLE T_ER_DAILYAPPLYDETAIL ADD (FISOVER CHAR(1) DEFAULT '0' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_ER_DAILYAPPLYDETAIL' AND KSQL_COL_NAME ='FOVERDESC') 
 ALTER TABLE T_ER_DAILYAPPLYDETAIL ADD (FOVERDESC NVARCHAR(255) DEFAULT ' ' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_ER_DAILYAPPLYBILL' AND KSQL_COL_NAME ='FSTDREIMBURSETYPE') 
 ALTER TABLE T_ER_DAILYAPPLYBILL ADD (FSTDREIMBURSETYPE VARCHAR(30) DEFAULT ' ' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_ER_DAILYAPPLYBILL' AND KSQL_COL_NAME ='FISOVER') 
 ALTER TABLE T_ER_DAILYAPPLYBILL ADD (FISOVER CHAR(1) DEFAULT '0' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_ER_DAILYAPPLYDETAIL' AND KSQL_COL_NAME ='FFEESTANDID') 
 ALTER TABLE T_ER_DAILYAPPLYDETAIL ADD (FFEESTANDID BIGINT DEFAULT 0 NOT NULL );



/*** 费用标准功能新增的单据字段 -- 对公报销单单头+分录 **/
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_ER_PUBREIMBILL' AND KSQL_COL_NAME ='FISOVER') 
 ALTER TABLE T_ER_PUBREIMBILL ADD (FISOVER CHAR(1) DEFAULT '0' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_ER_PUBREIMEXPDET_A') 
 CREATE TABLE T_ER_PUBREIMEXPDET_A (
FID BIGINT DEFAULT 0 NOT NULL,
FDETAILID BIGINT DEFAULT 0 NOT NULL,
FTREATTYPE NVARCHAR(10) DEFAULT ' ' NOT NULL,
FTREATWAY NVARCHAR(10) DEFAULT ' ' NOT NULL,
FMEETTYPE NVARCHAR(10) DEFAULT ' ' NOT NULL,
FMEETLEVEL NVARCHAR(10) DEFAULT ' ' NOT NULL,
FCITYAREA BIGINT DEFAULT 0 NOT NULL,
FREIMBURSELEVEL BIGINT DEFAULT 0 NOT NULL,
FFEESTANDARDAMT DECIMAL(23,10) DEFAULT 0 NOT NULL,
FFEESTANDARDDESC NVARCHAR(255) DEFAULT ' ' NOT NULL,
FTREATNUM INT DEFAULT 0 NOT NULL,
FESCORTNUM INT DEFAULT 0 NOT NULL,
FGOODSNUM INT DEFAULT 0 NOT NULL,
FDAYSNUM INT DEFAULT 0 NOT NULL,
FCONTROLAMT DECIMAL(23,10) DEFAULT 0 NOT NULL,
FISOVER CHAR(1) DEFAULT '0' NOT NULL,
FOVERDESC NVARCHAR(255) DEFAULT ' ' NOT NULL
);
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_ER_PUBREIMEXPDET_A' AND KSQL_COL_NAME ='FFEESTANDID') 
 ALTER TABLE T_ER_PUBREIMEXPDET_A ADD (FFEESTANDID BIGINT DEFAULT 0 NOT NULL );


 EXEC P_ALTERPK 'PK_T_ER_PUBREIMEXPDET_A', 'T_ER_PUBREIMEXPDET_A', 'FDETAILID', '1';

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



/**End D:\pdm\PDM_UPGRADE\PDM1\ER_table.sql **/
