-- 创建表
/* from  172.17.3.90/NextERP_PDM/PDM_UPGRADE/PDMS/branch_lastest/pdm/ER_table.sql*/
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_ER_ACCOUNTCHANGEBILL') 
 CREATE TABLE T_ER_ACCOUNTCHANGEBILL (
FID BIGINT DEFAULT 0 NOT NULL,
FBILLNO VARCHAR(80) DEFAULT ' ' NOT NULL,
FBILLSTATUS VARCHAR(1) DEFAULT ' ' NOT NULL,
FCREATORID BIGINT DEFAULT 0 NOT NULL,
FMODIFIERID BIGINT DEFAULT 0 NOT NULL,
FAUDITORID BIGINT DEFAULT 0 NOT NULL,
FAUDITDATE DATETIME,
FMODIFYTIME DATETIME,
FCREATETIME DATETIME,
FORGID BIGINT DEFAULT 0 NOT NULL,
FDESCRIPTION NVARCHAR(1000) DEFAULT ' ' NOT NULL,
FCOMPANYID BIGINT DEFAULT 0 NOT NULL,
FTEL VARCHAR(100) DEFAULT ' ' NOT NULL,
FCHANGEDATE DATETIME,
FNEXTAUDITOR VARCHAR(200) DEFAULT ' ' NOT NULL,
FAPPLIERPOSITION VARCHAR(100) DEFAULT ' ' NOT NULL,
FAPPLIERID BIGINT DEFAULT 0 NOT NULL
);

IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_ER_ACCOUNTCHANGEBILL_L') 
 CREATE TABLE T_ER_ACCOUNTCHANGEBILL_L (
FPKID VARCHAR(36) DEFAULT ' ' NOT NULL,
FID BIGINT DEFAULT 0 NOT NULL,
FLOCALEID VARCHAR(10) DEFAULT ' ' NOT NULL,
FAPPLIERPOSITION VARCHAR(100) DEFAULT ' ' NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_ER_ORIGINALENTRY') 
 CREATE TABLE T_ER_ORIGINALENTRY (
FID BIGINT DEFAULT 0 NOT NULL,
FENTRYID BIGINT DEFAULT 0 NOT NULL,
FSEQ INT DEFAULT 0 NOT NULL,
FSOURCEBILLNO VARCHAR(80) DEFAULT ' ' NOT NULL,
FSOURCEROW VARCHAR(50) DEFAULT ' ' NOT NULL,
FPAYERTYPE VARCHAR(50) DEFAULT ' ' NOT NULL,
FPAYERNAME VARCHAR(100) DEFAULT ' ' NOT NULL,
FPAYERACCOUNT VARCHAR(100) DEFAULT ' ' NOT NULL,
FPAYERACCOUNTNAME VARCHAR(100) DEFAULT ' ' NOT NULL,
FPAYERBANK BIGINT DEFAULT 0 NOT NULL,
FSUPPLIER BIGINT DEFAULT 0 NOT NULL,
FCUSTOMER BIGINT DEFAULT 0 NOT NULL,
FCASORG BIGINT DEFAULT 0 NOT NULL,
FPAYEERID BIGINT DEFAULT 0 NOT NULL,
FSOURCECURRENCY BIGINT DEFAULT 0 NOT NULL,
FSOURCEAMOUNT DECIMAL(23,10) DEFAULT 0 NOT NULL,
FSOURCEBILLID VARCHAR(50) DEFAULT 0 NOT NULL,
FSOURCEENTRYID VARCHAR(50) DEFAULT ' ' NOT NULL,
FSOURCEBILLTYPE VARCHAR(50) DEFAULT ' ' NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_ER_CHANGEENTRY') 
 CREATE TABLE T_ER_CHANGEENTRY (
FID BIGINT DEFAULT 0 NOT NULL,
FENTRYID BIGINT DEFAULT 0 NOT NULL,
FSEQ INT DEFAULT 0 NOT NULL,
FCHANGEBILLNO VARCHAR(80) DEFAULT ' ' NOT NULL,
FCHANGEROW VARCHAR(50) DEFAULT ' ' NOT NULL,
FCHANGEPAYERTYPE VARCHAR(50) DEFAULT ' ' NOT NULL,
FCHANGEPAYERNAME VARCHAR(100) DEFAULT ' ' NOT NULL,
FCHANGESUPPLIER BIGINT DEFAULT 0 NOT NULL,
FCHANGECUSTOMER BIGINT DEFAULT 0 NOT NULL,
FCHANGECASORG BIGINT DEFAULT 0 NOT NULL,
FCHANGEPAYEERID BIGINT DEFAULT 0 NOT NULL,
FCHANGEPAYERBANK BIGINT DEFAULT 0 NOT NULL,
FCHANGEPAYERACCOUNT VARCHAR(100) DEFAULT ' ' NOT NULL,
FCHANGEPAYERACCOUNTNAME VARCHAR(100) DEFAULT ' ' NOT NULL,
FCHANGECURRENCY BIGINT DEFAULT 0 NOT NULL,
FCHANGEAMOUNT DECIMAL(23,10) DEFAULT 0 NOT NULL,
FCHANGEBILLID VARCHAR(50) DEFAULT ' ' NOT NULL,
FCHANGEENTRYID VARCHAR(50) DEFAULT ' ' NOT NULL,
FCHANGEBILLTYPE VARCHAR(50) DEFAULT ' ' NOT NULL
);
-- 创建索引 
/*from 172.17.3.90/NextERP_PDM/PDM_UPGRADE/PDMS/branch_lastest/pdm/ER_constraint.sql*/

EXEC P_ALTERPK 'PK_ER_ACCOUNTCHANGEBILL', 'T_ER_ACCOUNTCHANGEBILL', 'FID', '1';
IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_TABNAME = 'T_ER_ACCOUNTCHANGEBILL' AND KSQL_INDNAME = 'IDX_ER_ACCCHANGE_FBILLNO') CREATE INDEX IDX_ER_ACCCHANGE_FBILLNO ON T_ER_ACCOUNTCHANGEBILL ( FBILLNO );
IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_TABNAME = 'T_ER_ACCOUNTCHANGEBILL' AND KSQL_INDNAME = 'IDX_ER_ACCCHANGE_FAPPLYERID') CREATE INDEX IDX_ER_ACCCHANGE_FAPPLYERID ON T_ER_ACCOUNTCHANGEBILL ( FAPPLIERID ,FMODIFYTIME );


EXEC P_ALTERPK 'PK_ER_ACCOUNTCHANGEBILL_L', 'T_ER_ACCOUNTCHANGEBILL_L', 'FPKID', '1';
IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_TABNAME = 'T_ER_ACCOUNTCHANGEBILL_L' AND KSQL_INDNAME = 'IDX_ACCHANGEBILL_L_ID') CREATE INDEX IDX_ACCHANGEBILL_L_ID ON T_ER_ACCOUNTCHANGEBILL_L ( FID ,FLOCALEID );


EXEC P_ALTERPK 'PK_ER_ORIGINALENTRY', 'T_ER_ORIGINALENTRY', 'FENTRYID', '1';
IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_TABNAME = 'T_ER_ORIGINALENTRY' AND KSQL_INDNAME = 'IDX_ER_ORIGIN_FID') CREATE INDEX IDX_ER_ORIGIN_FID ON T_ER_ORIGINALENTRY ( FID );

EXEC P_ALTERPK 'PK_ER_CHANGEENTRY', 'T_ER_CHANGEENTRY', 'FENTRYID', '1';
IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_TABNAME = 'T_ER_CHANGEENTRY' AND KSQL_INDNAME = 'IDX_ER_CHANGEENTRY_FID') CREATE INDEX IDX_ER_CHANGEENTRY_FID ON T_ER_CHANGEENTRY ( FID );