IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_ER_EXPENSE_DETAIL_DATA')
 CREATE TABLE T_ER_EXPENSE_DETAIL_DATA (
FID BIGINT DEFAULT 0 NOT NULL,
FBILLNO VARCHAR(60) DEFAULT ' ' NOT NULL,
FBILLSTATUS VARCHAR(30) DEFAULT ' ' NOT NULL,
FCREATORID BIGINT DEFAULT 0 NOT NULL,
FCREATETIME DATETIME,
FMODIFIERID BIGINT DEFAULT 0 NOT NULL,
FMODIFYTIME DATETIME,
FAUDITORID BIGINT DEFAULT 0 NOT NULL,
FAUDITDATE DATETIME,

FORGID BIGINT DEFAULT 0 NOT NULL,
FBILLTYPE VARCHAR(50) DEFAULT ' ' NOT NULL,
FCOMPANY BIGINT DEFAULT 0 NOT NULL,
FBIZDATE DATETIME,
FAPPLIER BIGINT DEFAULT 0 NOT NULL,
FCOSTCOMPANY BIGINT DEFAULT 0 NOT NULL,
FCOSTDEPT BIGINT DEFAULT 0 NOT NULL,

FEXPENSEITEM BIGINT DEFAULT 0 NOT NULL,
FTRAVELITEM BIGINT DEFAULT 0 NOT NULL,
FSTDPROJECT BIGINT DEFAULT 0 NOT NULL,
FORIENTRYAMOUNT DECIMAL(23,10) DEFAULT 0 NOT NULL,
FENTRYAMOUNT DECIMAL(23,10) DEFAULT 0 NOT NULL,
FORIENTRYAPPAMOUNT DECIMAL(23,10) DEFAULT 0 NOT NULL,
FENTRYAPPAMOUNT DECIMAL(23,10) DEFAULT 0 NOT NULL,

FHAPPENDATE DATETIME,
FORICURRENCY BIGINT DEFAULT 0 NOT NULL,
FCURRENCY BIGINT DEFAULT 0 NOT NULL,
FBILLID BIGINT DEFAULT 0 NOT NULL,
FENCASHAMOUNT DECIMAL(23,10) DEFAULT 0 NOT NULL,

FYEAR BIGINT DEFAULT 0 NOT NULL,
FYEARMONTH BIGINT DEFAULT 0 NOT NULL,
FQUARTER BIGINT DEFAULT 0 NOT NULL
);

EXEC P_ALTERPK 'PK_ER_EXPENSE_DETAIL_DATA', 'T_ER_EXPENSE_DETAIL_DATA', 'FID', '1';
IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_ER_DETAIL_DATA_FBILLID')
CREATE INDEX IDX_ER_DETAIL_DATA_FBILLID ON T_ER_EXPENSE_DETAIL_DATA ( FBILLID );


/*报销人*/
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_ER_DETAILDATA_REIMBURSE')
 CREATE TABLE T_ER_DETAILDATA_REIMBURSE (
FPKID BIGINT DEFAULT 0 NOT NULL,
FID BIGINT DEFAULT 0 NOT NULL,
FBASEDATAID BIGINT DEFAULT 0 NOT NULL
);

EXEC P_ALTERPK 'PK_ER_DETAILDATA_REIMBURSE', 'T_ER_DETAILDATA_REIMBURSE', 'FPKID', '1';
IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_ER_DETAILDATA_REIM_FID')
CREATE INDEX IDX_ER_DETAILDATA_REIM_FID ON T_ER_DETAILDATA_REIMBURSE ( FID );


/*同步状态记录*/
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_ER_EXPENSEDATA_SYNCTIME')
 CREATE TABLE T_ER_EXPENSEDATA_SYNCTIME (
FID BIGINT DEFAULT 0 NOT NULL,
FBILLTYPE VARCHAR(50) DEFAULT ' ' NOT NULL,
FSTATUS VARCHAR(30) DEFAULT ' ' NOT NULL,
FLASTSYNCTIME DATETIME,
FTOTAL BIGINT DEFAULT 0 NOT NULL,
FSYNCING BIGINT DEFAULT 0 NOT NULL,
FCREATETIME DATETIME,
FMODIFYTIME DATETIME
);
EXEC P_ALTERPK 'PK_ER_EXPENSEDATA_SYNCTIME', 'T_ER_EXPENSEDATA_SYNCTIME', 'FID', '1';
IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_ER_DATASYNCTIME_FBILLTYPE')
CREATE INDEX IDX_ER_DATASYNCTIME_FBILLTYPE ON T_ER_EXPENSEDATA_SYNCTIME ( FBILLTYPE );

/*同步记录*/
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_ER_EXPDATA_SYNCRECORD')
 CREATE TABLE T_ER_EXPDATA_SYNCRECORD (
FID BIGINT DEFAULT 0 NOT NULL,
FBILLTYPE VARCHAR(50) DEFAULT ' ' NOT NULL,
FCREATETIME DATETIME,
FMODIFYTIME DATETIME,
FCOUNT BIGINT DEFAULT 0 NOT NULL,
FBEGINTIME DATETIME,
FENDTIME DATETIME,
FSYNCRANGE CHAR(1) DEFAULT '0' NOT NULL,
FMESSAGE NCLOB,
FMESSAGE_TAG NCLOB
);

EXEC P_ALTERPK 'PK_ER_EXPDATA_SYNCRECORD', 'T_ER_EXPDATA_SYNCRECORD', 'FID', '1';
IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_ER_SYNCRECORD_FBEGINTIME')
CREATE INDEX IDX_ER_SYNCRECORD_FBEGINTIME ON T_ER_EXPDATA_SYNCRECORD ( FBEGINTIME );

