/**Begin D:\k3Cloud\PDM_UPGRADE\PDM1\MDS_table.sql **/
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_MDS_ASSIGNED') 
 CREATE TABLE T_MDS_ASSIGNED (
FID BIGINT DEFAULT 0 NOT NULL,
FNUMBER NVARCHAR(80) DEFAULT ' ' NOT NULL,
FSTATUS VARCHAR(5) DEFAULT ' ' NOT NULL,
FCREATORID BIGINT DEFAULT 0 NOT NULL,
FMODIFIERID BIGINT DEFAULT 0 NOT NULL,
FENABLE VARCHAR(5) DEFAULT ' ' NOT NULL,
FCREATETIME DATETIME,
FMODIFYTIME DATETIME,
FMASTERID BIGINT DEFAULT 0 NOT NULL,
FCREATEORGID BIGINT DEFAULT 0 NOT NULL,
FORGID BIGINT DEFAULT 0 NOT NULL,
FCTRLSTRATEGY NVARCHAR(50) DEFAULT ' ' NOT NULL
);


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


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_MDS_ASSIGNENTRY') 
 CREATE TABLE T_MDS_ASSIGNENTRY (
FID BIGINT DEFAULT 0 NOT NULL,
FENTRYID BIGINT DEFAULT 0 NOT NULL,
FSEQ BIGINT DEFAULT 0 NOT NULL,
FPERIOD NVARCHAR(50) DEFAULT ' ' NOT NULL,
FINTPERIOD BIGINT DEFAULT 0 NOT NULL,
FMOD DECIMAL(23,10) DEFAULT 0 NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_MDS_FNREPORT') 
 CREATE TABLE T_MDS_FNREPORT (
FID BIGINT DEFAULT 0 NOT NULL,
FBILLNO NVARCHAR(80) DEFAULT ' ' NOT NULL,
FBILLSTATUS VARCHAR(5) 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,
FSNO NVARCHAR(80) DEFAULT ' ' NOT NULL,
FBILLID BIGINT DEFAULT 0 NOT NULL,
FBILLENTRYID BIGINT DEFAULT 0 NOT NULL,
FMATERIALID BIGINT DEFAULT 0 NOT NULL,
FOKDATE DATETIME,
FFINISHDATE DATETIME,
FFORDATE DATETIME,
FSETOFFDATE DATETIME,
FQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FSETOFFQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FSETOFFVERID BIGINT DEFAULT 0 NOT NULL,
FUNITID BIGINT DEFAULT 0 NOT NULL,
FROWNO NVARCHAR(50) DEFAULT ' ' NOT NULL,
FFORECASTQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FREMAINQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FEPDDATE DATETIME,
FSALEORG BIGINT DEFAULT 0 NOT NULL,
FRPDDATE DATETIME,
FOUTSTOCKQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FORDERREMAINQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FBILLTYPE BIGINT DEFAULT 0 NOT NULL,
FSENDGOODSDATE DATETIME,
FTOOLID BIGINT DEFAULT 0 NOT NULL,
FSETID BIGINT DEFAULT 0 NOT NULL,
FORDERSTATUS VARCHAR(5) DEFAULT ' ' NOT NULL,
FPLANID BIGINT DEFAULT 0 NOT NULL,
FPLANENTRYID BIGINT DEFAULT 0 NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_MDS_PROVIDERRECORD') 
 CREATE TABLE T_MDS_PROVIDERRECORD (
FID BIGINT DEFAULT 0 NOT NULL,
FBILLNO NVARCHAR(80) DEFAULT ' ' NOT NULL,
FBILLSTATUS VARCHAR(5) 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,
FBILLROWNO BIGINT DEFAULT 0 NOT NULL,
FBSTATUS VARCHAR(5) DEFAULT ' ' NOT NULL,
FQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FMATERIEL BIGINT DEFAULT 0 NOT NULL,
FBILLUNIT BIGINT DEFAULT 0 NOT NULL,
FTRANSFERID BIGINT DEFAULT 0 NOT NULL,
FBILLENTRYID BIGINT DEFAULT 0 NOT NULL,
FBILLID BIGINT DEFAULT 0 NOT NULL,
FRECORDDATE DATETIME,
FSETID BIGINT DEFAULT 0 NOT NULL,
FTOOLID BIGINT DEFAULT 0 NOT NULL,
FSETOFFQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FBILLTAG VARCHAR(50) DEFAULT ' ' NOT NULL,
FBILLTYPENAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FBNO NVARCHAR(80) DEFAULT ' ' NOT NULL,
FBILLTYPE BIGINT DEFAULT 0 NOT NULL,
FBILLORG BIGINT DEFAULT 0 NOT NULL,
FFCVRNNUM BIGINT DEFAULT 0 NOT NULL,
FLOGID BIGINT DEFAULT 0 NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_MDS_REQUESTRECORD') 
 CREATE TABLE T_MDS_REQUESTRECORD (
FID BIGINT DEFAULT 0 NOT NULL,
FBILLNO NVARCHAR(80) DEFAULT ' ' NOT NULL,
FBILLSTATUS VARCHAR(5) 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,
FBILLROWNO BIGINT DEFAULT 0 NOT NULL,
FBSTATUS VARCHAR(5) DEFAULT ' ' NOT NULL,
FQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FMATERIEL BIGINT DEFAULT 0 NOT NULL,
FBILLUNIT BIGINT DEFAULT 0 NOT NULL,
FTRANSFERID BIGINT DEFAULT 0 NOT NULL,
FRECORDDATE DATETIME,
FBILLENTRYID BIGINT DEFAULT 0 NOT NULL,
FBILLID BIGINT DEFAULT 0 NOT NULL,
FBILLTAG VARCHAR(50) DEFAULT ' ' NOT NULL,
FBNO NVARCHAR(80) DEFAULT ' ' NOT NULL,
FSETID BIGINT DEFAULT 0 NOT NULL,
FTOOLID BIGINT DEFAULT 0 NOT NULL,
FSETOFFQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FBILLTYPE BIGINT DEFAULT 0 NOT NULL,
FBILLORG BIGINT DEFAULT 0 NOT NULL,
FRPD DATETIME,
FEPD DATETIME,
FDELEVERYDATE DATETIME,
FACCOUTQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FSALEORDEROUTID BIGINT DEFAULT 0 NOT NULL,
FBILLTYPENAME NVARCHAR(50) DEFAULT ' ' NOT NULL,
FLOGID BIGINT DEFAULT 0 NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_MDS_SETOFFLOG') 
 CREATE TABLE T_MDS_SETOFFLOG (
FID BIGINT DEFAULT 0 NOT NULL,
FNUMBER NVARCHAR(80) DEFAULT ' ' NOT NULL,
FSTATUS VARCHAR(5) DEFAULT ' ' NOT NULL,
FCREATORID BIGINT DEFAULT 0 NOT NULL,
FMODIFIERID BIGINT DEFAULT 0 NOT NULL,
FENABLE VARCHAR(5) DEFAULT ' ' NOT NULL,
FCREATETIME DATETIME,
FMODIFYTIME DATETIME,
FMASTERID BIGINT DEFAULT 0 NOT NULL,
FCREATEORGID BIGINT DEFAULT 0 NOT NULL,
FORGID BIGINT DEFAULT 0 NOT NULL,
FCTRLSTRATEGY NVARCHAR(50) DEFAULT ' ' NOT NULL,
FCALCULATEPRO DECIMAL(23,10) DEFAULT 0 NOT NULL,
FSTARTDATE DATETIME,
FENDDATE DATETIME,
FSUMMIN DECIMAL(23,10) DEFAULT 0 NOT NULL,
FPLANID NVARCHAR(100) DEFAULT ' ' NOT NULL,
FJOBID NVARCHAR(100) DEFAULT ' ' NOT NULL,
FEXECSTAT VARCHAR(5) DEFAULT ' ' NOT NULL
);


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


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_MDS_SETOFFLOGENTRY') 
 CREATE TABLE T_MDS_SETOFFLOGENTRY (
FID BIGINT DEFAULT 0 NOT NULL,
FENTRYID BIGINT DEFAULT 0 NOT NULL,
FSEQ BIGINT DEFAULT 0 NOT NULL,
FSTEPSEQ NVARCHAR(50) DEFAULT ' ' NOT NULL,
FSTEPNAME NVARCHAR(100) DEFAULT ' ' NOT NULL,
FPROCESSDATA BIGINT DEFAULT 0 NOT NULL,
FOPERATMIN DECIMAL(23,10) DEFAULT 0 NOT NULL,
FRESULT NVARCHAR(100) DEFAULT ' ' NOT NULL,
FDETAILMSG NVARCHAR(1000) DEFAULT ' ' NOT NULL
);


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


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_MDS_SETOFFORDER') 
 CREATE TABLE T_MDS_SETOFFORDER (
FID BIGINT DEFAULT 0 NOT NULL,
FNUMBER NVARCHAR(80) DEFAULT ' ' NOT NULL,
FSTATUS VARCHAR(5) DEFAULT ' ' NOT NULL,
FCREATORID BIGINT DEFAULT 0 NOT NULL,
FMODIFIERID BIGINT DEFAULT 0 NOT NULL,
FENABLE VARCHAR(5) DEFAULT ' ' NOT NULL,
FCREATETIME DATETIME,
FMODIFYTIME DATETIME,
FMASTERID BIGINT DEFAULT 0 NOT NULL,
FCREATEORGID BIGINT DEFAULT 0 NOT NULL,
FORGID BIGINT DEFAULT 0 NOT NULL,
FCTRLSTRATEGY NVARCHAR(50) DEFAULT ' ' NOT NULL,
FAHEADS BIGINT DEFAULT 0 NOT NULL,
FTAILS BIGINT DEFAULT 0 NOT NULL,
FORDERTYPE VARCHAR(5) DEFAULT ' ' NOT NULL
);


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


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


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


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


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_MDS_SETOFFSETTING') 
 CREATE TABLE T_MDS_SETOFFSETTING (
FID BIGINT DEFAULT 0 NOT NULL,
FNUMBER NVARCHAR(80) DEFAULT ' ' NOT NULL,
FSTATUS VARCHAR(5) DEFAULT ' ' NOT NULL,
FCREATORID BIGINT DEFAULT 0 NOT NULL,
FMODIFIERID BIGINT DEFAULT 0 NOT NULL,
FENABLE VARCHAR(5) DEFAULT ' ' NOT NULL,
FCREATETIME DATETIME,
FMODIFYTIME DATETIME,
FMASTERID BIGINT DEFAULT 0 NOT NULL,
FCREATEORGID BIGINT DEFAULT 0 NOT NULL,
FORGID BIGINT DEFAULT 0 NOT NULL,
FPLANID NVARCHAR(100) DEFAULT ' ' NOT NULL,
FJOBID NVARCHAR(100) DEFAULT ' ' NOT NULL,
FCTRLSTRATEGY NVARCHAR(50) DEFAULT ' ' NOT NULL,
FSETOFFORDERID BIGINT DEFAULT 0 NOT NULL,
FMOD BIGINT DEFAULT 0 NOT NULL,
FSFILTER NVARCHAR(255) DEFAULT ' ' NOT NULL,
FSFILTER_TAG NCLOB,
FPFILTER NVARCHAR(255) DEFAULT ' ' NOT NULL,
FPFILTER_TAG NCLOB
);


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


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_MDS_SETOFFTOOL') 
 CREATE TABLE T_MDS_SETOFFTOOL (
FID BIGINT DEFAULT 0 NOT NULL,
FNUMBER NVARCHAR(80) DEFAULT ' ' NOT NULL,
FSTATUS VARCHAR(5) DEFAULT ' ' NOT NULL,
FCREATORID BIGINT DEFAULT 0 NOT NULL,
FMODIFIERID BIGINT DEFAULT 0 NOT NULL,
FENABLE VARCHAR(5) DEFAULT ' ' NOT NULL,
FCREATETIME DATETIME,
FMODIFYTIME DATETIME,
FMASTERID BIGINT DEFAULT 0 NOT NULL,
FCREATEORGID BIGINT DEFAULT 0 NOT NULL,
FORGID BIGINT DEFAULT 0 NOT NULL,
FCTRLSTRATEGY NVARCHAR(50) DEFAULT ' ' NOT NULL,
FPLUGIN NVARCHAR(255) DEFAULT ' ' NOT NULL
);


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


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_MDS_SETOFFTOOLENTRY') 
 CREATE TABLE T_MDS_SETOFFTOOLENTRY (
FID BIGINT DEFAULT 0 NOT NULL,
FENTRYID BIGINT DEFAULT 0 NOT NULL,
FSEQ BIGINT DEFAULT 0 NOT NULL,
FSETID BIGINT DEFAULT 0 NOT NULL,
FISUSE CHAR(1) DEFAULT '1' NOT NULL
);

IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_MDS_SETOFFORDER' AND KSQL_COL_NAME ='FQTYPE') 
 ALTER TABLE T_MDS_SETOFFORDER ADD (FQTYPE VARCHAR(5) DEFAULT ' ' NOT NULL );
 
 IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_MDS_FNREPORT' AND KSQL_COL_NAME ='FKQDATE') 
 ALTER TABLE T_MDS_FNREPORT ADD (FKQDATE DATETIME );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_MDS_FNREPORT' AND KSQL_COL_NAME ='FEXECQTY') 
 ALTER TABLE T_MDS_FNREPORT ADD (FEXECQTY DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_MDS_SETOFFSETENT' AND KSQL_COL_NAME ='FMANUSTRATEGY') 
 ALTER TABLE T_MDS_SETOFFSETENT ADD (FMANUSTRATEGY NVARCHAR(500) DEFAULT ' ' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_MDS_SETOFFSETMANU') 
 CREATE TABLE T_MDS_SETOFFSETMANU (
FPKID BIGINT DEFAULT 0 NOT NULL,
FENTRYID BIGINT DEFAULT 0 NOT NULL,
FBASEDATAID BIGINT DEFAULT 0 NOT NULL
);


/**End D:\k3Cloud\PDM_UPGRADE\PDM1\MDS_table.sql **/
