/**Begin D:\PDM_UPGRADE\PDM1\TM_table.sql **/
--生命周期记录表
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_TM_BIZRECORD')
 CREATE TABLE T_TM_BIZRECORD (
FID BIGINT DEFAULT 0 NOT NULL,
FCREATETIME DATETIME,
FBILLNO NVARCHAR(80) DEFAULT ' ' NOT NULL,
FCURRENCYID BIGINT DEFAULT 0 NOT NULL,
FRESTAMT DECIMAL(23,10) DEFAULT 0 NOT NULL,
FTRADEBILLID BIGINT DEFAULT 0 NOT NULL,
FEXRATE DECIMAL(23,10) DEFAULT 0 NOT NULL,
FBIZDATE DATETIME,
FLV INT DEFAULT 0 NOT NULL,
FSEQNO INT DEFAULT 0 NOT NULL,
FPID BIGINT DEFAULT 0 NOT NULL,
FENDDATE DATETIME,
FSRCBIZBILLID BIGINT DEFAULT 0 NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_TM_BIZRECORD_L')
 CREATE TABLE T_TM_BIZRECORD_L (
FPKID NVARCHAR(18) DEFAULT ' ' NOT NULL,
FID BIGINT DEFAULT 0 NOT NULL,
FLOCALEID NVARCHAR(10) DEFAULT ' ' NOT NULL,
FDESC NVARCHAR(255) DEFAULT ' ' NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_TM_BIZRECORD_ENTRY')
 CREATE TABLE T_TM_BIZRECORD_ENTRY (
FENTRYID BIGINT DEFAULT 0 NOT NULL,
FID BIGINT DEFAULT 0 NOT NULL,
FSEQ INT DEFAULT 0 NOT NULL,
FOPDATE DATETIME,
FBIZOP NVARCHAR(30) DEFAULT ' ' NOT NULL,
FAMOUNT DECIMAL(23,10) DEFAULT 0 NOT NULL,
FBIZBILLID BIGINT DEFAULT 0 NOT NULL
);

--业务处理单新增的字段
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_TM_BUSINESSBILL' AND KSQL_COL_NAME IN ('FEXRATEDEY'))
 ALTER TABLE T_TM_BUSINESSBILL ADD (FEXRATEDEY DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_TM_BUSINESSBILL' AND KSQL_COL_NAME IN ('FPIPS'))
 ALTER TABLE T_TM_BUSINESSBILL ADD (FPIPS DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_TM_BUSINESSBILL' AND KSQL_COL_NAME IN ('FRESTAMT1'))
 ALTER TABLE T_TM_BUSINESSBILL ADD (FRESTAMT1 DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_TM_BUSINESSBILL' AND KSQL_COL_NAME IN ('FBIZAMT1'))
 ALTER TABLE T_TM_BUSINESSBILL ADD (FBIZAMT1 DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_TM_BUSINESSBILL' AND KSQL_COL_NAME IN ('FRESTAMT2'))
 ALTER TABLE T_TM_BUSINESSBILL ADD (FRESTAMT2 DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_TM_BUSINESSBILL' AND KSQL_COL_NAME IN ('FBIZAMT2'))
 ALTER TABLE T_TM_BUSINESSBILL ADD (FBIZAMT2 DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_TM_BUSINESSBILL' AND KSQL_COL_NAME IN ('FEXRATECALMTD'))
 ALTER TABLE T_TM_BUSINESSBILL ADD (FEXRATECALMTD NVARCHAR(30) DEFAULT ' ' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_TM_BUSINESSBILL' AND KSQL_COL_NAME IN ('FPLCURRENCYID'))
 ALTER TABLE T_TM_BUSINESSBILL ADD (FPLCURRENCYID BIGINT DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_TM_BUSINESSBILL' AND KSQL_COL_NAME IN ('FPLAMT'))
 ALTER TABLE T_TM_BUSINESSBILL ADD (FPLAMT DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_TM_BUSINESSBILL' AND KSQL_COL_NAME IN ('FBASEFQ'))
 ALTER TABLE T_TM_BUSINESSBILL ADD (FBASEFQ NVARCHAR(80) DEFAULT ' ' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_TM_BUSINESSBILL' AND KSQL_COL_NAME IN ('FBASEEXRATE'))
 ALTER TABLE T_TM_BUSINESSBILL ADD (FBASEEXRATE DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_TM_BUSINESSBILL' AND KSQL_COL_NAME IN ('FBASEPLAMT'))
 ALTER TABLE T_TM_BUSINESSBILL ADD (FBASEPLAMT DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_TM_BUSINESSBILL' AND KSQL_COL_NAME IN ('FPLSETTLEDATE'))
 ALTER TABLE T_TM_BUSINESSBILL ADD (FPLSETTLEDATE DATETIME );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_TM_BUSINESSBILL' AND KSQL_COL_NAME IN ('FDELIVERYDATE'))
 ALTER TABLE T_TM_BUSINESSBILL ADD (FDELIVERYDATE DATETIME );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_TM_BUSINESSBILL' AND KSQL_COL_NAME IN ('FBIZRECORDID'))
 ALTER TABLE T_TM_BUSINESSBILL ADD (FBIZRECORDID BIGINT DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_TM_BUSINESSBILL' AND KSQL_COL_NAME IN ('FEXPIREDDATE'))
 ALTER TABLE T_TM_BUSINESSBILL ADD (FEXPIREDDATE DATETIME );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_TM_BUSINESSBILL' AND KSQL_COL_NAME IN ('FEXPIREDSTART'))
 ALTER TABLE T_TM_BUSINESSBILL ADD (FEXPIREDSTART DATETIME );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_TM_BUSINESSBILL' AND KSQL_COL_NAME IN ('FEXPIREDEND'))
 ALTER TABLE T_TM_BUSINESSBILL ADD (FEXPIREDEND DATETIME );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_TM_BUSINESSBILL' AND KSQL_COL_NAME IN ('FDEYDATESTART'))
 ALTER TABLE T_TM_BUSINESSBILL ADD (FDEYDATESTART DATETIME );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_TM_BUSINESSBILL' AND KSQL_COL_NAME IN ('FDEYDATEEND'))
 ALTER TABLE T_TM_BUSINESSBILL ADD (FDEYDATEEND DATETIME );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_TM_BUSINESSBILL' AND KSQL_COL_NAME IN ('FISDATERANGE'))
 ALTER TABLE T_TM_BUSINESSBILL ADD (FISDATERANGE CHAR(1) DEFAULT '0' NOT NULL );

--外汇扩展表
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_TM_TRADE_F' AND KSQL_COL_NAME IN ('FBIZTYPE'))
 ALTER TABLE T_TM_TRADE_F ADD (FBIZTYPE NVARCHAR(30) DEFAULT ' ' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_TM_TRADE_F' AND KSQL_COL_NAME IN ('FPAIR'))
 ALTER TABLE T_TM_TRADE_F ADD (FPAIR NVARCHAR(30) DEFAULT ' ' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_TM_TRADE_F' AND KSQL_COL_NAME IN ('FISDATERANGE'))
 ALTER TABLE T_TM_TRADE_F ADD (FISDATERANGE CHAR(1) DEFAULT '0' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_TM_TRADE_F' AND KSQL_COL_NAME IN ('FSETTLEDATESTART'))
 ALTER TABLE T_TM_TRADE_F ADD (FSETTLEDATESTART DATETIME );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_TM_TRADE_F' AND KSQL_COL_NAME IN ('FSETTLEDATEEND'))
 ALTER TABLE T_TM_TRADE_F ADD (FSETTLEDATEEND DATETIME );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_TM_TRADE_F' AND KSQL_COL_NAME IN ('FADJSETTLEDATESTART'))
 ALTER TABLE T_TM_TRADE_F ADD (FADJSETTLEDATESTART DATETIME );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_TM_TRADE_F' AND KSQL_COL_NAME IN ('FADJSETTLEDATEEND'))
 ALTER TABLE T_TM_TRADE_F ADD (FADJSETTLEDATEEND DATETIME );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_TM_TRADE_F' AND KSQL_COL_NAME IN ('FTERMEND'))
 ALTER TABLE T_TM_TRADE_F ADD (FTERMEND NVARCHAR(30) DEFAULT ' ' NOT NULL );

--现金流反写字段
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_TM_CASHFLOW' AND KSQL_COL_NAME IN ('FBIZRECORDID'))
 ALTER TABLE T_TM_CASHFLOW ADD (FBIZRECORDID BIGINT DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_TM_CASHFLOW' AND KSQL_COL_NAME IN ('FISHIS'))
 ALTER TABLE T_TM_CASHFLOW ADD (FISHIS CHAR(1) DEFAULT '0' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_TM_CASHFLOW_BAK' AND KSQL_COL_NAME IN ('FBIZRECORDID'))
 ALTER TABLE T_TM_CASHFLOW_BAK ADD (FBIZRECORDID BIGINT DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_TM_CASHFLOW_BAK' AND KSQL_COL_NAME IN ('FISHIS'))
 ALTER TABLE T_TM_CASHFLOW_BAK ADD (FISHIS CHAR(1) DEFAULT '0' NOT NULL );

--交易录入
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_TM_TRADE' AND KSQL_COL_NAME IN ('FBIZRESTAMT'))
 ALTER TABLE T_TM_TRADE ADD (FBIZRESTAMT DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_TM_TRADE' AND KSQL_COL_NAME IN ('FBIZOP'))
 ALTER TABLE T_TM_TRADE ADD (FBIZOP NVARCHAR(30) DEFAULT ' ' NOT NULL );
/**End D:\PDM_UPGRADE\PDM1\TM_table.sql **/
