/**Begin E:\NextERP_PDM\PDM_UPGRADE\PDMS\branch_lastest\pdm\BEI_table.sql **/
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_BEI_ELECTICDIRCONSET_L') 
 CREATE TABLE T_BEI_ELECTICDIRCONSET_L (
FPKID VARCHAR(18) DEFAULT ' ' NOT NULL,
FID BIGINT DEFAULT 0 NOT NULL,
FLOCALEID VARCHAR(10) DEFAULT ' ' NOT NULL,
FNAME NVARCHAR(100) DEFAULT ' ' NOT NULL,
FCOMMENT NVARCHAR(255) DEFAULT ' ' NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_BEI_RESPONSERULESET') 
 CREATE TABLE T_BEI_RESPONSERULESET (
FID BIGINT DEFAULT 0 NOT NULL,
FNUMBER VARCHAR(50) DEFAULT ' ' NOT NULL,
FSTATUS VARCHAR(30) DEFAULT 'C' NOT NULL,
FMASTERID BIGINT DEFAULT 0 NOT NULL,
FDISABLEDATE DATETIME,
FENABLEDATE DATETIME,
FDISABLERID BIGINT DEFAULT 0 NOT NULL,
FENABLERID BIGINT DEFAULT 0 NOT NULL,
FENABLE CHAR(1) DEFAULT ' ' NOT NULL,
FCREATORID BIGINT DEFAULT 0 NOT NULL,
FCREATETIME DATETIME,
FMODIFIERID BIGINT DEFAULT 0 NOT NULL,
FMODIFYTIME DATETIME,
FNAME NVARCHAR(100) DEFAULT ' ' NOT NULL,
FCOMMENT NVARCHAR(255) DEFAULT ' ' NOT NULL,
FINVOICE CHAR(1) DEFAULT '0' NOT NULL,
FRECITE CHAR(1) DEFAULT '0' NOT NULL,
FACCEPTANCE CHAR(1) DEFAULT '0' NOT NULL,
FPAYMENT CHAR(1) DEFAULT '0' NOT NULL,
FENSURE CHAR(1) DEFAULT '0' NOT NULL,
FPLEDGE CHAR(1) DEFAULT '0' NOT NULL,
FRELEASEOFPLEDGE CHAR(1) DEFAULT '0' NOT NULL,
FCONSENTPAYOFF CHAR(1) DEFAULT '0' NOT NULL,
FCONDITIONDESC NVARCHAR(1000) DEFAULT ' ' NOT NULL,
FDATAFILTER NVARCHAR(255) DEFAULT ' ' NOT NULL,
FDATAFILTER_TAG NCLOB
);


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


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_BEI_RESPRULESETENTRY') 
 CREATE TABLE T_BEI_RESPRULESETENTRY (
FID BIGINT DEFAULT 0 NOT NULL,
FENTRYID BIGINT DEFAULT 0 NOT NULL,
FSEQ INT DEFAULT 0 NOT NULL,
FORGID BIGINT DEFAULT 0 NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_BEI_ELECTICDIRCONSET') 
 CREATE TABLE T_BEI_ELECTICDIRCONSET (
FID BIGINT DEFAULT 0 NOT NULL,
FNUMBER VARCHAR(50) DEFAULT ' ' NOT NULL,
FSTATUS VARCHAR(30) DEFAULT 'C' NOT NULL,
FMASTERID BIGINT DEFAULT 0 NOT NULL,
FDISABLEDATE DATETIME,
FENABLEDATE DATETIME,
FDISABLERID BIGINT DEFAULT 0 NOT NULL,
FENABLERID BIGINT DEFAULT 0 NOT NULL,
FENABLE CHAR(1) DEFAULT '0' NOT NULL,
FCREATORID BIGINT DEFAULT 0 NOT NULL,
FCREATETIME DATETIME,
FMODIFIERID BIGINT DEFAULT 0 NOT NULL,
FMODIFYTIME DATETIME,
FORGID BIGINT DEFAULT 0 NOT NULL,
FDIRECTCONNCHANNELID BIGINT DEFAULT 0 NOT NULL,
FDEFAULTACCOUNT BIGINT DEFAULT 0 NOT NULL,
FBANKNAME VARCHAR(50) DEFAULT ' ' NOT NULL,
FCOMMENT NVARCHAR(255) DEFAULT ' ' NOT NULL,
FNAME NVARCHAR(100) DEFAULT ' ' NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_BEI_RECEIVABLEHANDLE') 
 CREATE TABLE T_BEI_RECEIVABLEHANDLE (
FID BIGINT DEFAULT 0 NOT NULL,
FBILLNO NVARCHAR(50) DEFAULT ' ' NOT NULL,
FBILLSTATUS NVARCHAR(30) 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,
FBIZDATE DATETIME,
FSOURCEBILLID BIGINT DEFAULT 0 NOT NULL,
FCURRENCYID BIGINT DEFAULT 0 NOT NULL,
FCURRENCY NVARCHAR(50) DEFAULT ' ' NOT NULL,
FAMOUNT DECIMAL(19,6) DEFAULT 0 NOT NULL,
FLOCAMT DECIMAL(19,6) DEFAULT 0 NOT NULL,
FEXCHANGERATE DECIMAL(23,10) DEFAULT 0 NOT NULL,
FISSUETICKETDATE DATETIME,
FEXCHANGEBILLEXPIREDATE DATETIME,
FTICKETSTATUS NVARCHAR(30) DEFAULT ' ' NOT NULL,
FTRADECONTRACTNO NVARCHAR(50) DEFAULT ' ' NOT NULL,
FISTRANSFER CHAR(1) DEFAULT '0' NOT NULL,
FCOMPANYID BIGINT DEFAULT 0 NOT NULL,
FCOLLECTIONER NVARCHAR(50) DEFAULT ' ' NOT NULL,
FCOLLECTIONACC NVARCHAR(50) DEFAULT ' ' NOT NULL,
FCOLLECTIONBANK NVARCHAR(50) DEFAULT ' ' NOT NULL,
FCOLLECTIONBANKNO NVARCHAR(50) DEFAULT ' ' NOT NULL,
FISSUETICKETER NVARCHAR(50) DEFAULT ' ' NOT NULL,
FISSUETICKETACC NVARCHAR(50) DEFAULT ' ' NOT NULL,
FISSUETICKETBANK NVARCHAR(50) DEFAULT ' ' NOT NULL,
FISSUETICKETBANKNO NVARCHAR(50) DEFAULT ' ' NOT NULL,
FPROMISER NVARCHAR(50) DEFAULT ' ' NOT NULL,
FPROMISEACC NVARCHAR(50) DEFAULT ' ' NOT NULL,
FPROMISEBANK NVARCHAR(50) DEFAULT ' ' NOT NULL,
FPROMISEBANKNO NVARCHAR(50) DEFAULT ' ' NOT NULL,
FISSUETICKETPROMISE NVARCHAR(255) DEFAULT ' ' NOT NULL,
FPROMISEINFO NVARCHAR(255) DEFAULT ' ' NOT NULL,
FPROMISEDATE DATETIME,
FPROMISEENSUREINFO NVARCHAR(255) DEFAULT ' ' NOT NULL,
FISSUETICKETGRADE NVARCHAR(255) DEFAULT ' ' NOT NULL,
FISSUETICKETCREDITLEVEL NVARCHAR(255) DEFAULT ' ' NOT NULL,
FISSUETICKETEXPIREDATE DATETIME,
FPROMISEGRADEBODY NVARCHAR(255) DEFAULT ' ' NOT NULL,
FPROMISECREDITLEVEL NVARCHAR(255) DEFAULT ' ' NOT NULL,
FPROMISEEXPIREDATE DATETIME,
FBATCHSEQID NVARCHAR(100) DEFAULT ' ' NOT NULL,
FDETAILSEQID NVARCHAR(100) DEFAULT ' ' NOT NULL,
FAPPLICANTNAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FAPPLICANTACC NVARCHAR(50) DEFAULT ' ' NOT NULL,
FAPPLICANTBANK NVARCHAR(50) DEFAULT ' ' NOT NULL,
FAPPLICANTBANKNO NVARCHAR(50) DEFAULT ' ' NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_BEI_RECEIVABLEHANDLE_L') 
 CREATE TABLE T_BEI_RECEIVABLEHANDLE_L (
FPKID NVARCHAR(30) DEFAULT ' ' NOT NULL,
FID BIGINT DEFAULT 0 NOT NULL,
FLOCALEID VARCHAR(10) DEFAULT ' ' NOT NULL,
FDESCRIPTION NVARCHAR(255) DEFAULT ' ' NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_BEI_RECEIVABLEHANDLEENT') 
 CREATE TABLE T_BEI_RECEIVABLEHANDLEENT (
FID BIGINT DEFAULT 0 NOT NULL,
FENTRYID BIGINT DEFAULT 0 NOT NULL,
FSEQ INT DEFAULT 0 NOT NULL,
FENDORSER NVARCHAR(50) DEFAULT ' ' NOT NULL,
FENDORSEE NVARCHAR(50) DEFAULT ' ' NOT NULL,
FISTRANSFER CHAR(1) DEFAULT '0' NOT NULL,
FENDORSEDATE DATETIME,
FNOTENO NVARCHAR(50) DEFAULT ' ' NOT NULL,
FDETAILSEQ NVARCHAR(50) DEFAULT ' ' NOT NULL,
FBUSINESSCODE NVARCHAR(50) DEFAULT ' ' NOT NULL,
FSUBTYPE NVARCHAR(50) DEFAULT ' ' NOT NULL,
FINITIATORORG NVARCHAR(50) DEFAULT ' ' NOT NULL,
FINITIATORNAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FINITIATORBANKCNAPS NVARCHAR(50) DEFAULT ' ' NOT NULL,
FINITIATORACNO NVARCHAR(50) DEFAULT ' ' NOT NULL,
FOPPONENTORG NVARCHAR(50) DEFAULT ' ' NOT NULL,
FOPPONENTNAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FOPPONENTBANKCNAPS NVARCHAR(50) DEFAULT ' ' NOT NULL,
FOPPONENTCL NVARCHAR(50) DEFAULT ' ' NOT NULL,
FOPPONENTLORG NVARCHAR(50) DEFAULT ' ' NOT NULL,
FOPPONENTDUEDATE NVARCHAR(50) DEFAULT ' ' NOT NULL,
FSIGNDATE NVARCHAR(50) DEFAULT ' ' NOT NULL,
FREPLYCODE NVARCHAR(50) DEFAULT ' ' NOT NULL,
FDEALNO NVARCHAR(50) DEFAULT ' ' NOT NULL,
FINVOICENO NVARCHAR(50) DEFAULT ' ' NOT NULL,
FCONTRACTNO NVARCHAR(50) DEFAULT ' ' NOT NULL,
FPAYMENTORDER NVARCHAR(50) DEFAULT ' ' NOT NULL,
FPAYMENTACCEPT NVARCHAR(50) DEFAULT ' ' NOT NULL,
FTRANSFERFLAG NVARCHAR(50) DEFAULT ' ' NOT NULL,
FOLCLEARINGFLAG NVARCHAR(50) DEFAULT ' ' NOT NULL,
FDISCOUNTTYPE NVARCHAR(50) DEFAULT ' ' NOT NULL,
FDISCOUNTRATE NVARCHAR(50) DEFAULT ' ' NOT NULL,
FDISCOUNTAMOUNT NVARCHAR(50) DEFAULT ' ' NOT NULL,
FREDEMPTIONSDATE NVARCHAR(50) DEFAULT ' ' NOT NULL,
FREDEMPTIONEDATE NVARCHAR(50) DEFAULT ' ' NOT NULL,
FDISREDRATE NVARCHAR(50) DEFAULT ' ' NOT NULL,
FDISREDAMOUNT NVARCHAR(50) DEFAULT ' ' NOT NULL,
FPRE4PAYDATE NVARCHAR(50) DEFAULT ' ' NOT NULL,
FCURRENCY NVARCHAR(50) DEFAULT ' ' NOT NULL,
FTRADEAMOUNT NVARCHAR(50) DEFAULT ' ' NOT NULL,
FDEFAULTFPCODE NVARCHAR(50) DEFAULT ' ' NOT NULL,
FREMARK NVARCHAR(50) DEFAULT ' ' NOT NULL,
FTRACKTYPE NVARCHAR(50) DEFAULT ' ' NOT NULL,
FTRACKDATE NVARCHAR(50) DEFAULT ' ' NOT NULL,
FCLEARDATE NVARCHAR(50) DEFAULT ' ' NOT NULL,
FCLEARAMOUNT NVARCHAR(50) DEFAULT ' ' NOT NULL,
FACOUNTBANKCNAPS NVARCHAR(50) DEFAULT ' ' NOT NULL,
FACCOUNTNO NVARCHAR(50) DEFAULT ' ' NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BEI_BANKBALANCE' AND KSQL_COL_NAME ='FSCORGID') 
 ALTER TABLE T_BEI_BANKBALANCE ADD (FSCORGID BIGINT DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_BEI_RECEIVABLEHANDLE_E') 
 CREATE TABLE T_BEI_RECEIVABLEHANDLE_E (
FID BIGINT DEFAULT 0 NOT NULL,
FDRAFTTYPE NVARCHAR(50) DEFAULT ' ' NOT NULL,
FBIZCODE NVARCHAR(50) DEFAULT ' ' NOT NULL,
FINVOICENUMBER NVARCHAR(50) DEFAULT ' ' NOT NULL,
FCONTRACTNUMBER NVARCHAR(50) DEFAULT ' ' NOT NULL,
FKEEPFLAG CHAR(1) DEFAULT '0' NOT NULL,
FAUTOACCEPT CHAR(1) DEFAULT '0' NOT NULL,
FAUTORECEIVE CHAR(1) DEFAULT '0' NOT NULL,
FEXPLAIN NVARCHAR(50) DEFAULT ' ' NOT NULL,
FREMARK NVARCHAR(50) DEFAULT ' ' NOT NULL,
FPRETYPEFLAG CHAR(1) DEFAULT '0' NOT NULL,
FINDORSENAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FPREHOLDERNAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FDISCOUNTRATE DECIMAL(23,10) DEFAULT 0 NOT NULL,
FLOANAMOUNT DECIMAL(19,6) DEFAULT 0 NOT NULL,
FBANKCONSULTNO NVARCHAR(50) DEFAULT ' ' NOT NULL,
FCUSTOMERCONSULTNO NVARCHAR(50) DEFAULT ' ' NOT NULL,
FNOTESTATE NVARCHAR(50) DEFAULT ' ' NOT NULL,
FHOLDERACCNO NVARCHAR(50) DEFAULT ' ' NOT NULL,
FHOLDERBANKNAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FHOLDERCNAPSCODE NVARCHAR(50) DEFAULT ' ' NOT NULL,
FOPERATIONCODE NVARCHAR(50) DEFAULT ' ' NOT NULL,
FDETAILBIZNO NVARCHAR(50) DEFAULT ' ' NOT NULL,
FACCEPTORBANKADDR NVARCHAR(50) DEFAULT ' ' NOT NULL,
FACCEPTORCOUNTRY NVARCHAR(50) DEFAULT ' ' NOT NULL,
FACCEPTORPROVINCE NVARCHAR(50) DEFAULT ' ' NOT NULL,
FACCEPTORCITY NVARCHAR(50) DEFAULT ' ' NOT NULL,
FOPPACCNO NVARCHAR(50) DEFAULT ' ' NOT NULL,
FOPPACCNAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FOPPCNAPSCODE NVARCHAR(50) DEFAULT ' ' NOT NULL,
FOPPBANKNAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FOPPCOUNTRY NVARCHAR(50) DEFAULT ' ' NOT NULL,
FOPPPROVINCE NVARCHAR(50) DEFAULT ' ' NOT NULL,
FOPPCITY NVARCHAR(50) DEFAULT ' ' NOT NULL,
FEBSEQID NVARCHAR(50) DEFAULT ' ' NOT NULL,
FEBSTATUS NVARCHAR(50) DEFAULT ' ' NOT NULL,
FEBSTATUSMSG NVARCHAR(50) DEFAULT ' ' NOT NULL,
FBANKSTATUS NVARCHAR(50) DEFAULT ' ' NOT NULL,
FBANKMSG NVARCHAR(50) DEFAULT ' ' NOT NULL,
FFLOWSERIALNO NVARCHAR(50) DEFAULT ' ' NOT NULL,
FRQSTSERIALNO NVARCHAR(50) DEFAULT ' ' NOT NULL,
FRSPSERIALNO NVARCHAR(50) DEFAULT ' ' NOT NULL,
FOPERATIONNAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FPAYAGENCY NVARCHAR(50) DEFAULT ' ' NOT NULL,
FDISCOUNTAMOUNT NVARCHAR(50) DEFAULT ' ' NOT NULL,
FREDEMPTIONSDATE NVARCHAR(50) DEFAULT ' ' NOT NULL,
FREDEMPTIONEDATE NVARCHAR(50) DEFAULT ' ' NOT NULL,
FDISREDRATE NVARCHAR(50) DEFAULT ' ' NOT NULL,
FINCREASERATE NVARCHAR(50) DEFAULT ' ' NOT NULL,
FADDDATE NVARCHAR(50) DEFAULT ' ' NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_BEI_RECEIVABLEHANDLE_F') 
 CREATE TABLE T_BEI_RECEIVABLEHANDLE_F (
FID BIGINT DEFAULT 0 NOT NULL,
FOTHERINFO NVARCHAR(50) DEFAULT ' ' NOT NULL,
FOPSTATUS NVARCHAR(50) DEFAULT ' ' NOT NULL,
FCUSTOMEROPSTATUS NVARCHAR(50) DEFAULT ' ' NOT NULL,
FOPPBANKADDR NVARCHAR(50) DEFAULT ' ' NOT NULL,
FDISCOUNTPAYTYPE NVARCHAR(50) DEFAULT ' ' NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BEI_TRANSDETAIL_E' AND KSQL_COL_NAME ='FSCORGID') 
 ALTER TABLE T_BEI_TRANSDETAIL_E ADD (FSCORGID BIGINT DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_BEI_TRANSDETAIL_E' AND KSQL_COL_NAME ='FRECEREDWAY') 
 ALTER TABLE T_BEI_TRANSDETAIL_E ADD (FRECEREDWAY NVARCHAR(30) DEFAULT ' ' NOT NULL );
 EXEC p_AlterColumn 'T_BEI_TRANSDETAIL', 'FSRCBILLTYPE', 'NVARCHAR(30)', 'NOT NULL', '1111', ''' ''';

 EXEC p_AlterColumn 'T_BEI_TRANSDETAIL', 'FRECEDBILLNUMBER', 'NVARCHAR(1024)', 'NOT NULL', '0100', ''' ''';


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