-- 租赁二期相关表
/**Begin E:\NextERP_PDM\PDM_UPGRADE\PDMS\branch_lastest\pdm\FA_table.sql **/
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_FA_LEASE_CONTRACT_NEW') 
 CREATE TABLE T_FA_LEASE_CONTRACT_NEW (
FID BIGINT DEFAULT 0 NOT NULL,
FNUMBER NVARCHAR(30) DEFAULT ' ' NOT NULL,
FNAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FSTATUS CHAR(1) DEFAULT ' ' NOT NULL,
FENABLE CHAR(1) DEFAULT '1' NOT NULL,
FMASTERID BIGINT DEFAULT 0 NOT NULL,
FASSETUNITID BIGINT DEFAULT 0 NOT NULL,
FORGID BIGINT DEFAULT 0 NOT NULL,
FLEASERID BIGINT DEFAULT 0 NOT NULL,
FCURRENCYID BIGINT DEFAULT 0 NOT NULL,
FASSETCATID BIGINT DEFAULT 0 NOT NULL,
FSTOREPLACEID BIGINT DEFAULT 0 NOT NULL,
FUNITID BIGINT DEFAULT 0 NOT NULL,
FASSETQTY DECIMAL(19,4) DEFAULT 0 NOT NULL,
FCONTRSIGNDATE DATETIME,
FLEASESTARTDATE DATETIME,
FLEASEENDDATE DATETIME,
FLEASEMONTHS INT DEFAULT 0 NOT NULL,
FFREELEASESTARTDATE DATETIME,
FINITCONFIRMDATE DATETIME,
FISEXEMPT CHAR(1) DEFAULT '0' NOT NULL,
FDISCOUNTRATE DECIMAL(23,10) DEFAULT 0 NOT NULL,
FDAILYDISCOUNTRATE DECIMAL(19,6) DEFAULT 0 NOT NULL,
FLEASELIAB DECIMAL(19,4) DEFAULT 0 NOT NULL,
FDEPREMONTHS INT DEFAULT 0 NOT NULL,
FAUDITORID BIGINT DEFAULT 0 NOT NULL,
FAUDITDATE DATETIME,
FLEASEASSETS DECIMAL(19,4) DEFAULT 0 NOT NULL,
FFREELEASEMONTHS INT DEFAULT 0 NOT NULL,
FCREATORID BIGINT DEFAULT 0 NOT NULL,
FMODIFIERID BIGINT DEFAULT 0 NOT NULL,
FCREATETIME DATETIME,
FMODIFYTIME DATETIME,
FASSETNAME NVARCHAR(300) DEFAULT ' ' NOT NULL,
FASSETQTYCREATE INT DEFAULT 0 NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_FA_LEASE_CONTRACT_NEW_L') 
 CREATE TABLE T_FA_LEASE_CONTRACT_NEW_L (
FPKID VARCHAR(18) DEFAULT '0' NOT NULL,
FID BIGINT DEFAULT 0 NOT NULL,
FLOCALEID VARCHAR(10) DEFAULT ' ' NOT NULL,
FNAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FASSETNAME NVARCHAR(300) DEFAULT ' ' NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_FA_LEASE_PAY_RULE') 
 CREATE TABLE T_FA_LEASE_PAY_RULE (
FENTRYID BIGINT DEFAULT 0 NOT NULL,
FID BIGINT DEFAULT 0 NOT NULL,
FSEQ INT DEFAULT 0 NOT NULL,
FPAYITEMID BIGINT DEFAULT 0 NOT NULL,
FFREQUENCY CHAR(1) DEFAULT ' ' NOT NULL,
FSTARTDATE DATETIME,
FENDDATE DATETIME,
FRELATIVEPAYDATE INT DEFAULT 0 NOT NULL,
FINVOICETYPE CHAR(1) DEFAULT ' ' NOT NULL,
FDEDUCTIBLE CHAR(1) DEFAULT '0' NOT NULL,
FTAXRATE DECIMAL(19,4) DEFAULT 0 NOT NULL,
FAMOUNT DECIMAL(19,4) DEFAULT 0 NOT NULL
);


 EXEC p_AlterColumn 'T_FA_LEASE_PAY_PLAN', 'FSEQ', 'INT', 'NOT NULL', '1111', '0';

 EXEC p_AlterColumn 'T_FA_LEASE_PAY_PLAN', 'FPLANPAYDATE', 'DATETIME', 'NULL', '0011', 'NULL';

IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_FA_LEASE_PAY_PLAN' AND KSQL_COL_NAME ='FPLANNUMBER') 
 ALTER TABLE T_FA_LEASE_PAY_PLAN ADD (FPLANNUMBER NVARCHAR(50) DEFAULT ' ' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_FA_LEASE_PAY_PLAN' AND KSQL_COL_NAME ='FPAYITEMID') 
 ALTER TABLE T_FA_LEASE_PAY_PLAN ADD (FPAYITEMID BIGINT DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_FA_LEASE_PAY_PLAN' AND KSQL_COL_NAME ='FSTARTDATE') 
 ALTER TABLE T_FA_LEASE_PAY_PLAN ADD (FSTARTDATE DATETIME );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_FA_LEASE_PAY_PLAN' AND KSQL_COL_NAME ='FENDDATE') 
 ALTER TABLE T_FA_LEASE_PAY_PLAN ADD (FENDDATE DATETIME );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_FA_LEASE_PAY_PLAN' AND KSQL_COL_NAME ='FINVOICETYPE') 
 ALTER TABLE T_FA_LEASE_PAY_PLAN ADD (FINVOICETYPE CHAR(1) DEFAULT ' ' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_FA_LEASE_PAY_PLAN' AND KSQL_COL_NAME ='FDEDUCTIBLE') 
 ALTER TABLE T_FA_LEASE_PAY_PLAN ADD (FDEDUCTIBLE CHAR(1) DEFAULT '0' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_FA_LEASE_PAY_PLAN' AND KSQL_COL_NAME ='FTAXRATE') 
 ALTER TABLE T_FA_LEASE_PAY_PLAN ADD (FTAXRATE DECIMAL(19,4) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_FA_LEASE_PAY_PLAN' AND KSQL_COL_NAME ='FDISCOUNTDAYS') 
 ALTER TABLE T_FA_LEASE_PAY_PLAN ADD (FDISCOUNTDAYS INT DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_FA_LEASE_PAY_PLAN' AND KSQL_COL_NAME ='FDISCOUNTFACTOR') 
 ALTER TABLE T_FA_LEASE_PAY_PLAN ADD (FDISCOUNTFACTOR DECIMAL(19,6) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_FA_LEASE_PAY_PLAN' AND KSQL_COL_NAME ='FPRESENTVALUE') 
 ALTER TABLE T_FA_LEASE_PAY_PLAN ADD (FPRESENTVALUE DECIMAL(19,4) DEFAULT 0 NOT NULL );

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