/**Begin E:\NextERP_PDM\PDM_UPGRADE\PDMS\branch_lastest\pdm\CAS_table.sql **/
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_CAS_BANKVCCHECK' AND KSQL_COL_NAME ='FADJUSTSTATUS') 
 ALTER TABLE T_CAS_BANKVCCHECK ADD (FADJUSTSTATUS CHAR(1) DEFAULT ' ' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_CAS_BANKVCCHECK' AND KSQL_COL_NAME ='FBALADJUST') 
 ALTER TABLE T_CAS_BANKVCCHECK ADD (FBALADJUST NVARCHAR(80) DEFAULT ' ' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_CAS_BANKVCCHECK' AND KSQL_COL_NAME ='FJOURNALAMT') 
 ALTER TABLE T_CAS_BANKVCCHECK ADD (FJOURNALAMT DECIMAL(19,6) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_CAS_BANKVCCHECK' AND KSQL_COL_NAME ='FSTMTAMT') 
 ALTER TABLE T_CAS_BANKVCCHECK ADD (FSTMTAMT DECIMAL(19,6) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_CAS_AUTOCALRESULT') 
 CREATE TABLE T_CAS_AUTOCALRESULT (
FID BIGINT DEFAULT 0 NOT NULL,
FMODIFIERID BIGINT DEFAULT 0 NOT NULL,
FCREATETIME DATETIME,
FMODIFYTIME DATETIME,
FNUMBER NVARCHAR(30) DEFAULT ' ' NOT NULL,
FNAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FSTATUS VARCHAR(30) DEFAULT ' ' NOT NULL,
FCREATORID BIGINT DEFAULT 0 NOT NULL,
FENABLE CHAR(1) DEFAULT '0' NOT NULL,
FMASTERID BIGINT DEFAULT 0 NOT NULL,
FMATCHSTATUS VARCHAR(30) DEFAULT ' ' NOT NULL,
FSMARTMATCHID BIGINT DEFAULT 0 NOT NULL,
FCURRENCYID BIGINT DEFAULT 0 NOT NULL
);


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


IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_CAS_AUTOMATCHRESULT_E' AND KSQL_COL_NAME ='FORGID') 
 ALTER TABLE T_CAS_AUTOMATCHRESULT_E ADD (FORGID BIGINT DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_CAS_AUTOMATCHRESULT_E' AND KSQL_COL_NAME ='FBANKACCTID') 
 ALTER TABLE T_CAS_AUTOMATCHRESULT_E ADD (FBANKACCTID BIGINT DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_CAS_AUTOMATCHRESULT_E' AND KSQL_COL_NAME ='FRECTYPEID') 
 ALTER TABLE T_CAS_AUTOMATCHRESULT_E ADD (FRECTYPEID BIGINT DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_CAS_AUTOMATCHRESULT_E' AND KSQL_COL_NAME ='FSETTLESTYLEID') 
 ALTER TABLE T_CAS_AUTOMATCHRESULT_E ADD (FSETTLESTYLEID BIGINT DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_CAS_AUTOMATCHRESULT_E' AND KSQL_COL_NAME ='FPAYTYPEID') 
 ALTER TABLE T_CAS_AUTOMATCHRESULT_E ADD (FPAYTYPEID BIGINT DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_CAS_AUTOMATCHRESULT_E' AND KSQL_COL_NAME ='FPAYAMOUNT') 
 ALTER TABLE T_CAS_AUTOMATCHRESULT_E ADD (FPAYAMOUNT DECIMAL(19,6) DEFAULT 0 NOT NULL );
 EXEC p_AlterColumn 'T_CAS_AUTOMATCHRESULT', 'FENABLE', 'VARCHAR(5)', 'NOT NULL', '1111', ''' ''';

IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_CAS_AUTOMATCHRESULT' AND KSQL_COL_NAME ='FSMARTMATCHID') 
 ALTER TABLE T_CAS_AUTOMATCHRESULT ADD (FSMARTMATCHID BIGINT DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_CAS_AUTOMATCHRESULT' AND KSQL_COL_NAME ='FCURRENCYID') 
 ALTER TABLE T_CAS_AUTOMATCHRESULT ADD (FCURRENCYID BIGINT DEFAULT 0 NOT NULL );
 EXEC p_AlterColumn 'T_CAS_SMARTMATCH', 'FENABLE', 'VARCHAR(5)', 'NOT NULL', '1111', ''' ''';

 EXEC p_AlterColumn 'T_CAS_SMARTMATCH', 'FCTRLSTRATEGY', 'VARCHAR(30)', 'NOT NULL', '1111', ''' ''';

IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_CAS_SMARTMATCH' AND KSQL_COL_NAME ='FCREATEORGID') 
 ALTER TABLE T_CAS_SMARTMATCH ADD (FCREATEORGID BIGINT DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_CAS_RECONCILIATION_E') 
 CREATE TABLE T_CAS_RECONCILIATION_E (
FENTRYID BIGINT DEFAULT 0 NOT NULL,
FID BIGINT DEFAULT 0 NOT NULL,
FRULESNAME NVARCHAR(100) DEFAULT ' ' NOT NULL,
FDATAFILTERDESC NVARCHAR(1024) DEFAULT ' ' NOT NULL,
FDATAFILTERDESC_REAL NCLOB,
FDATAFILTERDESC_REAL_TAG NCLOB
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_CAS_RECONCILIATIONRULE') 
 CREATE TABLE T_CAS_RECONCILIATIONRULE (
FID BIGINT DEFAULT 0 NOT NULL,
FCREATETIME DATETIME,
FMODIFYTIME DATETIME,
FMODIFIERID BIGINT DEFAULT 0 NOT NULL,
FNUMBER NVARCHAR(30) DEFAULT ' ' NOT NULL,
FNAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FSTATUS VARCHAR(30) DEFAULT ' ' NOT NULL,
FCREATORID BIGINT DEFAULT 0 NOT NULL,
FENABLE INT DEFAULT 0 NOT NULL,
FMASTERID BIGINT DEFAULT 0 NOT NULL,
FORGFIELD BIGINT DEFAULT 0 NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_CAS_SMARTMATCH_E' AND KSQL_COL_NAME ='FSEQ') 
 ALTER TABLE T_CAS_SMARTMATCH_E ADD (FSEQ INT DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_CAS_AUTOCALRESULT_E') 
 CREATE TABLE T_CAS_AUTOCALRESULT_E (
FENTRYID BIGINT DEFAULT 0 NOT NULL,
FID BIGINT DEFAULT 0 NOT NULL,
FSEQ INT DEFAULT 0 NOT NULL,
FRECORD VARCHAR(30) DEFAULT ' ' NOT NULL,
FORGID BIGINT DEFAULT 0 NOT NULL,
FBIZDATE DATETIME,
FBANKACCTID BIGINT DEFAULT 0 NOT NULL,
FACCTNAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FACCT NVARCHAR(50) DEFAULT ' ' NOT NULL,
FAMOUNT DECIMAL(19,6) DEFAULT 0 NOT NULL,
FNUM NVARCHAR(80) DEFAULT ' ' NOT NULL,
FBILLSTATUS NVARCHAR(30) DEFAULT ' ' NOT NULL,
FRECTYPEID BIGINT DEFAULT 0 NOT NULL,
FSETTLESTYLEID BIGINT DEFAULT 0 NOT NULL,
FSETTLENUMBER NVARCHAR(50) DEFAULT ' ' NOT NULL,
FBILLID VARCHAR(30) DEFAULT ' ' NOT NULL,
FDESCRIPTION NVARCHAR(255) DEFAULT ' ' NOT NULL,
FPAYTYPEID BIGINT DEFAULT 0 NOT NULL,
FPAYAMOUNT DECIMAL(19,6) DEFAULT 0 NOT NULL
);


 EXEC p_AlterColumn 'T_CAS_PAYMENTBILL_C', 'FRECEMAIL', 'NVARCHAR(100)', 'NOT NULL', '1111', ''' ''';

 EXEC p_AlterColumn 'T_CAS_PAYMENTBILL_C', 'FRECBANKADDRESS', 'NVARCHAR(400)', 'NOT NULL', '1111', ''' ''';

 EXEC p_AlterColumn 'T_CAS_PAYMENTBILL_C', 'FCONTRACTNO', 'NVARCHAR(20)', 'NOT NULL', '1111', ''' ''';

 EXEC p_AlterColumn 'T_CAS_PAYMENTBILL_C', 'FAUDITPARAM', 'NVARCHAR(200)', 'NOT NULL', '1111', ''' ''';

 EXEC p_AlterColumn 'T_CAS_PAYMENTBILL_C', 'FPAYMETHOD', 'NVARCHAR(30)', 'NOT NULL', '1111', ''' ''';

 EXEC p_AlterColumn 'T_CAS_PAYMENTBILL_C', 'FSERLEVEL', 'NVARCHAR(30)', 'NOT NULL', '1111', ''' ''';

 EXEC p_AlterColumn 'T_CAS_PAYMENTBILL_C', 'FCHECKTYPE', 'NVARCHAR(30)', 'NOT NULL', '1111', ''' ''';

 EXEC p_AlterColumn 'T_CAS_PAYMENTBILL_C', 'FSENDWAY', 'NVARCHAR(30)', 'NOT NULL', '1111', ''' ''';

 EXEC p_AlterColumn 'T_CAS_PAYMENTBILL_C', 'FCHECKUSE', 'NVARCHAR(30)', 'NOT NULL', '1111', ''' ''';

 EXEC p_AlterColumn 'T_CAS_PAYMENTBILL_C', 'FRECSWIFTCODE', 'NVARCHAR(100)', 'NOT NULL', '1111', ''' ''';

 EXEC p_AlterColumn 'T_CAS_PAYMENTBILL_C', 'FRECROUTINGNUM', 'NVARCHAR(100)', 'NOT NULL', '1111', ''' ''';

 EXEC p_AlterColumn 'T_CAS_PAYMENTBILL_C', 'FRECOTHERCODE', 'NVARCHAR(100)', 'NOT NULL', '1111', ''' ''';

 EXEC p_AlterColumn 'T_CAS_PAYMENTBILL_C', 'FFEEPAYER', 'NVARCHAR(30)', 'NOT NULL', '1111', ''' ''';

 EXEC p_AlterColumn 'T_CAS_PAYMENTBILL_C', 'FINSTRUCTMSG', 'NVARCHAR(30)', 'NOT NULL', '1111', ''' ''';

 EXEC p_AlterColumn 'T_CAS_PAYMENTBILL_C', 'FRECADDRESS', 'NVARCHAR(400)', 'NOT NULL', '1111', ''' ''';

 EXEC p_AlterColumn 'T_CAS_AGENTPAYBILL_E', 'FCONTRACTNO', 'NVARCHAR(20)', 'NOT NULL', '1111', ''' ''';

 EXEC p_AlterColumn 'T_CAS_AGENTPAYBILL_E', 'FFEEPAYER', 'NVARCHAR(30)', 'NOT NULL', '1111', ''' ''';

 EXEC p_AlterColumn 'T_CAS_AGENTPAYBILL_E', 'FSERLEVEL', 'NVARCHAR(30)', 'NOT NULL', '1111', ''' ''';

 EXEC p_AlterColumn 'T_CAS_AGENTPAYBILL_E', 'FINSTRUCTMSG', 'NVARCHAR(30)', 'NOT NULL', '1111', ''' ''';

 EXEC p_AlterColumn 'T_CAS_AGENTPAYBILLENTRY', 'FRECEMAIL', 'NVARCHAR(100)', 'NOT NULL', '1111', ''' ''';

 EXEC p_AlterColumn 'T_CAS_AGENTPAYBILLENTRY', 'FRECBANKADDRESS', 'NVARCHAR(400)', 'NOT NULL', '1111', ''' ''';

 EXEC p_AlterColumn 'T_CAS_AGENTPAYBILLENTRY', 'FRECSWIFTCODE', 'NVARCHAR(100)', 'NOT NULL', '1111', ''' ''';

 EXEC p_AlterColumn 'T_CAS_AGENTPAYBILLENTRY', 'FRECROUTINGNUM', 'NVARCHAR(100)', 'NOT NULL', '1111', ''' ''';

 EXEC p_AlterColumn 'T_CAS_AGENTPAYBILLENTRY', 'FRECOTHERCODE', 'NVARCHAR(100)', 'NOT NULL', '1111', ''' ''';

 EXEC p_AlterColumn 'T_CAS_AGENTPAYBILLENTRY', 'FPAYMETHOD', 'NVARCHAR(30)', 'NOT NULL', '1111', ''' ''';

 EXEC p_AlterColumn 'T_CAS_AGENTPAYBILLENTRY', 'FAUDITPARAM', 'NVARCHAR(200)', 'NOT NULL', '1111', ''' ''';

 EXEC p_AlterColumn 'T_CAS_AGENTPAYBILLENTRY', 'FCHECKTYPE', 'NVARCHAR(30)', 'NOT NULL', '1111', ''' ''';

 EXEC p_AlterColumn 'T_CAS_AGENTPAYBILLENTRY', 'FSENDWAY', 'NVARCHAR(30)', 'NOT NULL', '1111', ''' ''';

 EXEC p_AlterColumn 'T_CAS_AGENTPAYBILLENTRY', 'FCHECKUSE', 'NVARCHAR(30)', 'NOT NULL', '1111', ''' ''';

IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_CAS_AGENTPAYBILLENTRY' AND KSQL_COL_NAME ='FBANKCHECKFLAG') 
 ALTER TABLE T_CAS_AGENTPAYBILLENTRY ADD (FBANKCHECKFLAG VARCHAR(50) DEFAULT ' ' NOT NULL );
 EXEC p_AlterColumn 'T_CAS_AGENTPAYBILLENTRY_E', 'FRECADDRESS', 'NVARCHAR(400)', 'NOT NULL', '1111', ''' ''';

IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_CAS_AGENTPAYBILLENTRY_E' AND KSQL_COL_NAME ='FEDPAMT') 
 ALTER TABLE T_CAS_AGENTPAYBILLENTRY_E ADD (FEDPAMT DECIMAL(19,6) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_CAS_AGENTPAYBILLENTRY_E' AND KSQL_COL_NAME ='FEDPLOCALAMT') 
 ALTER TABLE T_CAS_AGENTPAYBILLENTRY_E ADD (FEDPLOCALAMT DECIMAL(19,6) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_CAS_CHECKEDRESULTENTRY' AND KSQL_COL_NAME ='FSETTLEMENTNUMBER') 
 ALTER TABLE T_CAS_CHECKEDRESULTENTRY ADD (FSETTLEMENTNUMBER NVARCHAR(50) DEFAULT ' ' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_CAS_CHECKEDRESULT' AND KSQL_COL_NAME ='FBATCHNO') 
 ALTER TABLE T_CAS_CHECKEDRESULT ADD (FBATCHNO NVARCHAR(5) DEFAULT ' ' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_CAS_CHECKEDRESULT' AND KSQL_COL_NAME ='FMATCHRULEID') 
 ALTER TABLE T_CAS_CHECKEDRESULT ADD (FMATCHRULEID BIGINT DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_CAS_CHECKEDRESULT' AND KSQL_COL_NAME ='FMATCHRULE') 
 ALTER TABLE T_CAS_CHECKEDRESULT ADD (FMATCHRULE NCLOB );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_CAS_CHECKEDRESULT' AND KSQL_COL_NAME ='FMATCHRULE_TAG') 
 ALTER TABLE T_CAS_CHECKEDRESULT ADD (FMATCHRULE_TAG NCLOB );

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