-- *** TABLES *** CREATE TABLE CMIS.ACCLEV ( ACCESSLEVEL CHAR(2) NOT NULL, U_VERSION CHAR(1), FN1 CHAR(1), FN2 CHAR(1), FN3 CHAR(1), FN4 CHAR(1), FN5 CHAR(1), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (ACCESSLEVEL) ); CREATE TABLE CMIS.ACCTCAL ( ACCTYEAR INTEGER NOT NULL, ACCTMTH INTEGER NOT NULL, U_VERSION CHAR(1), YEARLABEL CHAR(20), MTHLABEL CHAR(20), DATEFROM DATE NOT NULL, DATETO DATE NOT NULL, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, ACTIVEFLAG CHAR(20), PRIMARY KEY (ACCTYEAR,ACCTMTH) ); CREATE TABLE CMIS.ACCTPR ( TABLENAME CHAR(8) NOT NULL, U_VERSION CHAR(1), ABBRCODE CHAR(3), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (TABLENAME)); CREATE UNIQUE INDEX ACCTPR_IDX1 ON CMIS.ACCTPR (ABBRCODE); CREATE TABLE CMIS.APPRBODY ( APPRBODYCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), APPRBODYDES CHAR(30), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (APPRBODYCODE) ); CREATE TABLE CMIS.ASCOND ( ASSETSYS DOUBLE PRECISION(15,0) NOT NULL, CONDID DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), JOBCODE DOUBLE PRECISION(15,0), CONDCODE CHAR(20), CONDNOTE CHAR(120), INSTPLANTSYS DOUBLE PRECISION(15,0), ESTCOST DOUBLE PRECISION(15,0), ESTCOSTNOTE CHAR(120), PENCODE CHAR(20), PENNOTE CHAR(120), PRIORITY INTEGER, EFFECTCODE CHAR(20), REQCODE DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (ASSETSYS,CONDID) ); CREATE TABLE CMIS.ASDEPR ( ASSETSYS DOUBLE PRECISION(15,0) NOT NULL, DEPRCATG CHAR(20) NOT NULL, U_VERSION CHAR(1), EXPDEPT DOUBLE PRECISION(15,0), EXPACCT CHAR(30), RESERVEDEPT DOUBLE PRECISION(15,0), RESERVEACCT CHAR(30), REVALDEPT DOUBLE PRECISION(15,0), REVALACCT CHAR(30), CURDEPRMTD CHAR(1) NOT NULL, CURSTARTVALUE DOUBLE PRECISION(15,0) NOT NULL, CURSTARTDATE DATE NOT NULL, CURSTARTRATE DOUBLE PRECISION(15,0), CURESTLIFE DOUBLE PRECISION(15,0), CURENDDATE DATE NOT NULL, CURRESVALUE DOUBLE PRECISION(15,0) NOT NULL, CURWDV DOUBLE PRECISION(15,0), CURASSETDEPRID DOUBLE PRECISION(15,0), ACCMDEPR DOUBLE PRECISION(15,0), REMAINLIFE DOUBLE PRECISION(15,0), DATELASTDEPR DATE, ACCTYEAR INTEGER, YEARDEPR DOUBLE PRECISION(15,0), LASTCLAIMDEPR DOUBLE PRECISION(15,0), GAINLOSS DOUBLE PRECISION(15,0), OPSTMT CHAR(30), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (ASSETSYS,DEPRCATG) ); CREATE TABLE CMIS.ASDRLOG ( ASSETSYS DOUBLE PRECISION(15,0) NOT NULL, DEPRCATG CHAR(20) NOT NULL, DEPRID DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), DEPRMTD CHAR(1) NOT NULL, STARTVALUE DOUBLE PRECISION(15,0) NOT NULL, STARTDATE DATE NOT NULL, STARTRATE DOUBLE PRECISION(15,0), ESTLIFE DOUBLE PRECISION(15,0), ENDDATE DATE NOT NULL, RESVALUE DOUBLE PRECISION(15,0), ACCMDEPR DOUBLE PRECISION(15,0), WDV DOUBLE PRECISION(15,0), REMAINLIFE DOUBLE PRECISION(15,0), REVALUEDIFF DOUBLE PRECISION(15,0), BATCHNO DOUBLE PRECISION(15,0), ACTIONFLAG CHAR(1), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPAREDATE1 DATE, SPAREDATE2 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (ASSETSYS,DEPRCATG,DEPRID) ); CREATE TABLE CMIS.ASLOC ( ASSETSYS DOUBLE PRECISION(15,0) NOT NULL, ASLOCID DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), ISSUECODE CHAR(20) NOT NULL, ISSUETO CHAR(20) NOT NULL, ISSUEDATE DATE NOT NULL, ISSUECOMMENT CHAR(30), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (ASSETSYS,ASLOCID) ); CREATE TABLE CMIS.ASPARA ( HIDKEY CHAR(1) NOT NULL, U_VERSION CHAR(1), NODIGIT INTEGER, CREATEIP CHAR(1), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPARECHAR3 CHAR(20), SPARECHAR4 CHAR(20), SPARENUM1 CHAR(32), SPARENUM2 CHAR(32), SPARENUM3 CHAR(32), SPARENUM4 CHAR(32), SPAREDATE1 DATE, SPAREDATE2 DATE, SPAREDATE3 DATE, SPAREDATE4 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (HIDKEY) ); CREATE TABLE CMIS.ASPLANT ( ASSETSYS DOUBLE PRECISION(15,0) NOT NULL, INSTPLANTSYS DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (ASSETSYS,INSTPLANTSYS) ); CREATE TABLE CMIS.ASREVAL ( BATCHNO DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), DEPRCATG CHAR(20), STARTREVALDATE DATE, STARTVALINDEX DOUBLE PRECISION(15,0), STARTRATE DOUBLE PRECISION(15,0), ESTLIFE DOUBLE PRECISION(15,0), RESVALUE DOUBLE PRECISION(15,0), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPAREDATE1 DATE, SPAREDATE2 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (BATCHNO) ); CREATE TABLE CMIS.ASSET ( ASSETSYS DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), ASSETNO CHAR(20) NOT NULL, ASSETALIAS CHAR(30), ASSETDES CHAR(100), DEPRFLAG CHAR(1) NOT NULL, ASSETSTATUS CHAR(20) NOT NULL, ASSETOWNER CHAR(20), ASSETCLASS CHAR(20), ASSETTYPE CHAR(20), ASSETPROJ CHAR(20), ASSETCATG CHAR(20), ASSETDEPTSYS DOUBLE PRECISION(15,0), ASSETACCT CHAR(30), DISPDATE DATE, DISPBY CHAR(20), DISPTYPE CHAR(20), SALEPRICE DOUBLE PRECISION(15,0), DISPREF CHAR(30), DISPCOMMENT CHAR(40), CURISSUECODE CHAR(20), CURISSUETO CHAR(20), CURISSUEDATE DATE, QTY DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPAREDATE1 DATE, SPAREDATE2 DATE, PRIMARY KEY (ASSETSYS)); CREATE UNIQUE INDEX ASSET_IDX1 ON CMIS.ASSET (ASSETNO); CREATE TABLE CMIS.ASSTK ( ASSETSYS DOUBLE PRECISION(15,0) NOT NULL, STKDATE DATE NOT NULL, U_VERSION CHAR(1), STKSTATUS CHAR(20) NOT NULL, LASTSIGHT DATE NOT NULL, STKBY CHAR(20) NOT NULL, STKCOMMENT CHAR(30), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (ASSETSYS,STKDATE) ); CREATE TABLE CMIS.ASTEMP ( ASSETSYS DOUBLE PRECISION(15,0) NOT NULL, DEPRCATG CHAR(20) NOT NULL, U_VERSION CHAR(1), DEPRID DOUBLE PRECISION(15,0), DEPRMTD CHAR(1), STARTVALUE DOUBLE PRECISION(15,0), STARTDATE DATE, STARTRATE DOUBLE PRECISION(15,0), ESTLIFE DOUBLE PRECISION(15,0), RESVALUE DOUBLE PRECISION(15,0), ACCMDEPR DOUBLE PRECISION(15,0), WDV DOUBLE PRECISION(15,0), REMAINLIFE DOUBLE PRECISION(15,0), REVALUEDIFF DOUBLE PRECISION(15,0), DATELASTDEPR DATE, EXPDEPT DOUBLE PRECISION(15,0), EXPACCT CHAR(30), RESERVEDEPT DOUBLE PRECISION(15,0), RESERVEACCT CHAR(30), DEPRVALUE DOUBLE PRECISION(15,0), ACCTYEAR INTEGER, ACCTMTH INTEGER, YEARDEPR DOUBLE PRECISION(15,0), ACTIONFLAG CHAR(1), OPSTMT CHAR(30), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (ASSETSYS,DEPRCATG) ); CREATE TABLE CMIS.ASTPL ( FLDNAME CHAR(30) NOT NULL, FLDVALUE CHAR(30) NOT NULL, U_VERSION CHAR(1), DEPRFLAG CHAR(1), ASSETSTATUS CHAR(20), ASSETOWNER CHAR(20), ASSETCLASS CHAR(20), ASSETTYPE CHAR(20), ASSETPROJ CHAR(20), ASSETCATG CHAR(20), ASSETDEPTSYS DOUBLE PRECISION(15,0), ASSETACCT CHAR(30), DEPRCATG CHAR(20), CURDEPRMTD CHAR(1), CURSTARTDATE DATE, CURSTARTVALUE DOUBLE PRECISION(15,0), CURSTARTRATE DOUBLE PRECISION(15,0), CURESTLIFE DOUBLE PRECISION(15,0), CURRESVALUE DOUBLE PRECISION(15,0), CURENDDATE DATE, EXPDEPT DOUBLE PRECISION(15,0), EXPACCT CHAR(30), RESERVEDEPT DOUBLE PRECISION(15,0), RESERVEACCT CHAR(30), REVALDEPT DOUBLE PRECISION(15,0), REVALACCT CHAR(30), OPTIMIZEFACTOR DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (FLDNAME,FLDVALUE) ); CREATE TABLE CMIS.ASTRAN ( TRANID DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), TRANTYPE CHAR(20), TRANDATE DATE, ASSETSYS DOUBLE PRECISION(15,0), DEPRCATG CHAR(20), DEPRDATE DATE, EXPDEPT DOUBLE PRECISION(15,0), EXPACCT CHAR(30), RESERVEDEPT DOUBLE PRECISION(15,0), RESERVEACCT CHAR(30), DEPRVALUE DOUBLE PRECISION(15,0), CURSTARTVALUE DOUBLE PRECISION(15,0), CURWDV DOUBLE PRECISION(15,0), ACCTYEAR INTEGER, ACCTMTH INTEGER, BATCHNO DOUBLE PRECISION(15,0), OPSTMT CHAR(30), ACTIONFLAG CHAR(1), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPAREDATE1 DATE, SPAREDATE2 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (TRANID) ); CREATE TABLE CMIS.BUDGET ( ACCTCODE CHAR(30) NOT NULL, ACCTYEAR INTEGER NOT NULL, U_VERSION CHAR(1), BUDSOURCE CHAR(3), AMT DOUBLE PRECISION(15,0), CURCODE CHAR(12), CURRATE DOUBLE PRECISION(15,0), BUDLOCK CHAR(1), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (ACCTCODE,ACCTYEAR) ); CREATE TABLE CMIS.BUDLMO ( ACCTCODE CHAR(30) NOT NULL, ACCTYEAR INTEGER NOT NULL, SOURCEID CHAR(12) NOT NULL, U_VERSION CHAR(1), AMT DOUBLE PRECISION(15,0), AUTHBY CHAR(30), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (ACCTCODE,ACCTYEAR,SOURCEID) ); CREATE TABLE CMIS.BUDMTH ( ACCTCODE CHAR(30) NOT NULL, ACCTYEAR INTEGER NOT NULL, SOURCEID CHAR(12) NOT NULL, ACCTMTH INTEGER NOT NULL, U_VERSION CHAR(1), RATIO DOUBLE PRECISION(15,0), AMT DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (ACCTCODE,ACCTYEAR,SOURCEID,ACCTMTH) ); CREATE TABLE CMIS.CALD ( INSTPLANTCODE CHAR(20) NOT NULL, STATUSDATE DATE NOT NULL, U_VERSION CHAR(1), STATUSTIME TIME, NOHOUR DOUBLE PRECISION(15,0), REMARK CHAR(60), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (INSTPLANTCODE,STATUSDATE) ); CREATE TABLE CMIS.CATG ( CATGCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), CATGDES CHAR(100), SYSACCTCODE CHAR(30), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (CATGCODE) ); CREATE TABLE CMIS.CATITEM ( CATITEMSYS DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), CATITEMCODE CHAR(30) NOT NULL, CATITEMNAME CHAR(20), CATITEMDES CHAR(100), EXTCATITEMDES CHAR(200), TYPECODE CHAR(12) NOT NULL, CLASSCODE CHAR(20), STDREFNO CHAR(16), QTYISSUE DOUBLE PRECISION(15,0), UOMCODE CHAR(20), QTYPURCH DOUBLE PRECISION(15,0), PKGUOMCODE CHAR(20), ROTABLE CHAR(1), CONVRATE INTEGER, SCRAPRATE DOUBLE PRECISION(15,0), CATITEMSTATUS CHAR(20), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPAREDATE1 DATE, SPAREDATE2 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, TXTCATITEMSYS CHAR(30), PRIMARY KEY (CATITEMSYS)); CREATE UNIQUE INDEX CATITEM_IDX1 ON CMIS.CATITEM (CATITEMCODE); CREATE TABLE CMIS.CATPLANT ( CATPLANTCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), CATPLANTDES CHAR(100), MODELNO CHAR(30), STDCOST DOUBLE PRECISION(15,0), UOMCODE CHAR(20), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPAREDATE1 DATE, SPAREDATE2 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (CATPLANTCODE) ); CREATE TABLE CMIS.CHKTAB ( CHKCODE CHAR(30) NOT NULL, U_VERSION CHAR(1), CHKDES CHAR(40), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (CHKCODE) ); CREATE TABLE CMIS.CHRTACCT ( ACCTCODE CHAR(30) NOT NULL, U_VERSION CHAR(1), ACCTALIAS CHAR(30), ACCTDES CHAR(30), ACCTTYPE CHAR(12), ACCTUSE CHAR(12), ACCTFLAG CHAR(1), DOUENTRY CHAR(30), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, SPARE1 CHAR(254), SPARE2 CHAR(254), PRIMARY KEY (ACCTCODE) ); CREATE TABLE CMIS.CIABC ( CATITEMSYS DOUBLE PRECISION(15,0) NOT NULL, WAREHSECODE CHAR(20) NOT NULL, U_VERSION CHAR(1), RANKNO DOUBLE PRECISION(15,0), COSTUSAGE DOUBLE PRECISION(15,0), COSTPERCENT DOUBLE PRECISION(15,0), ACCUSAGE DOUBLE PRECISION(15,0), ACCPERCENT DOUBLE PRECISION(15,0), ABCCODE CHAR(1), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (CATITEMSYS,WAREHSECODE) ); CREATE TABLE CMIS.CIALLOC ( WAREHSECODE CHAR(20) NOT NULL, CATITEMSYS DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), QTYALLOC DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (WAREHSECODE,CATITEMSYS) ); CREATE TABLE CMIS.CICATG ( CATITEMSYS DOUBLE PRECISION(15,0) NOT NULL, INVCATGCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (CATITEMSYS,INVCATGCODE) ); CREATE TABLE CMIS.CILOG ( CATITEMSYS DOUBLE PRECISION(15,0) NOT NULL, WAREHSECODE CHAR(20) NOT NULL, LOGID DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), STDCOST DOUBLE PRECISION(15,0), AVGCOST DOUBLE PRECISION(15,0), ACTUALCOST DOUBLE PRECISION(15,0), PLANCOST DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (CATITEMSYS,WAREHSECODE,LOGID) ); CREATE TABLE CMIS.CIMANUF ( CATITEMSYS DOUBLE PRECISION(15,0) NOT NULL, MANUFSYS DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), PRIORITY INTEGER, MANUFCATITEM CHAR(20), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (CATITEMSYS,MANUFSYS) ); CREATE TABLE CMIS.CIMFITEM ( CATITEMSYS DOUBLE PRECISION(15,0) NOT NULL, MANUFSYS DOUBLE PRECISION(15,0) NOT NULL, LOGID DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), MANUFCATITEM CHAR(20), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (CATITEMSYS,MANUFSYS,LOGID) ); CREATE TABLE CMIS.CIPART ( CATITEMSYS DOUBLE PRECISION(15,0) NOT NULL, SUBCATITEM DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (CATITEMSYS,SUBCATITEM) ); CREATE TABLE CMIS.CIRECOM ( CATITEMSYS DOUBLE PRECISION(15,0) NOT NULL, WAREHSECODE CHAR(20) NOT NULL, DATERECOM DATE NOT NULL, U_VERSION CHAR(1), ORDERQTY DOUBLE PRECISION(15,0), PKGUOMCODE CHAR(20), UNITCOST DOUBLE PRECISION(15,0), TOTALCOST DOUBLE PRECISION(15,0), PREFSUPPSYS DOUBLE PRECISION(15,0), POSYS DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (CATITEMSYS,WAREHSECODE,DATERECOM) ); CREATE TABLE CMIS.CISTATUS ( CATITEMSTATUS CHAR(20) NOT NULL, U_VERSION CHAR(1), CATITEMSTATUSDES CHAR(100), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (CATITEMSTATUS) ); CREATE TABLE CMIS.CISUITEM ( CATITEMSYS DOUBLE PRECISION(15,0) NOT NULL, SUPPSYS DOUBLE PRECISION(15,0) NOT NULL, LOGID DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), SUPPCATITEM CHAR(20), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (CATITEMSYS,SUPPSYS,LOGID) ); CREATE TABLE CMIS.CISUPP ( CATITEMSYS DOUBLE PRECISION(15,0) NOT NULL, SUPPSYS DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), PRIORITY INTEGER, LEADTIME INTEGER, SUPPCATITEM CHAR(20), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (CATITEMSYS,SUPPSYS) ); CREATE TABLE CMIS.CITEXT ( CATITEMSYS DOUBLE PRECISION(15,0) NOT NULL, TEXTCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), SEQ5 INTEGER, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (CATITEMSYS,TEXTCODE) ); CREATE TABLE CMIS.CIWH ( CATITEMSYS DOUBLE PRECISION(15,0) NOT NULL, WAREHSECODE CHAR(20) NOT NULL, U_VERSION CHAR(1), STDCOST DOUBLE PRECISION(15,0), AVGCOST DOUBLE PRECISION(15,0), ACTUALCOST DOUBLE PRECISION(15,0), PLANCOST DOUBLE PRECISION(15,0), QTYONHAND DOUBLE PRECISION(15,0), QTYONORDER DOUBLE PRECISION(15,0), QTYALLOC DOUBLE PRECISION(15,0), MAXSTOCK DOUBLE PRECISION(15,0), REORDERPOINT DOUBLE PRECISION(15,0), REORDERQTY DOUBLE PRECISION(15,0), REORDERPERIOD INTEGER, DATELASTORDER DATE, ANNUALUSAGE DOUBLE PRECISION(15,0), ACCTCODE CHAR(30), ORDERMTDCODE CHAR(12) NOT NULL, ABCCODE CHAR(1), PREFSUPPSYS DOUBLE PRECISION(15,0), STKMTDCODE CHAR(20), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (CATITEMSYS,WAREHSECODE) ); CREATE TABLE CMIS.CIWHBIN ( CATITEMSYS DOUBLE PRECISION(15,0) NOT NULL, WAREHSECODE CHAR(20) NOT NULL, BINCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), QTYONHAND DOUBLE PRECISION(15,0), STKDATE DATE, QTYBOOK DOUBLE PRECISION(15,0), QTYIR DOUBLE PRECISION(15,0), QTYCNT DOUBLE PRECISION(15,0), QTYADJ DOUBLE PRECISION(15,0), STKFLAG CHAR(1), RESPEMPCODE CHAR(20), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (CATITEMSYS,WAREHSECODE,BINCODE) ); CREATE TABLE CMIS.CONTTYPE ( FROMTABLE CHAR(8) NOT NULL, CONTACTTYPE CHAR(20) NOT NULL, U_VERSION CHAR(1), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (FROMTABLE,CONTACTTYPE) ); CREATE TABLE CMIS.CPALARM ( CATPLANTCODE CHAR(20) NOT NULL, TASKCODE CHAR(20) NOT NULL, UOMCODE CHAR(20) NOT NULL, ALARMVALUE DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), PRIORITY INTEGER, POSTFLAG CHAR(1), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (CATPLANTCODE,TASKCODE,UOMCODE,ALARMVALUE) ); CREATE TABLE CMIS.CPATTR ( CATPLANTCODE CHAR(20) NOT NULL, PLANTTYPE CHAR(20) NOT NULL, ATTRCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), SEQ5 INTEGER, ATTRVALUE CHAR(20), REMARK CHAR(30), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, TEMPORARY VARCHAR(20), PRIMARY KEY (CATPLANTCODE,PLANTTYPE,ATTRCODE) ); CREATE TABLE CMIS.CPCAL ( CATPLANTCODE CHAR(20) NOT NULL, CMPNAME CHAR(50) NOT NULL, U_VERSION CHAR(1), UOMCODE CHAR(20), STDVALUE DOUBLE PRECISION(15,0), TOLTREND CHAR(3), TOLVALUE DOUBLE PRECISION(15,0), UPPERLIMIT DOUBLE PRECISION(15,0), LOWERLIMIT DOUBLE PRECISION(15,0), TOLPLANTSTATUS CHAR(20), LIMITPLANTSTATUS CHAR(20), VARPERCENT DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (CATPLANTCODE,CMPNAME) ); CREATE TABLE CMIS.CPCITEM ( CATPLANTCODE CHAR(20) NOT NULL, INVFLAG CHAR(1) NOT NULL, CATITEMCODE CHAR(30) NOT NULL, U_VERSION CHAR(1), CATITEMDES CHAR(100), QTYREQD DOUBLE PRECISION(15,0), SEQ5 INTEGER, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (CATPLANTCODE,INVFLAG,CATITEMCODE) ); CREATE TABLE CMIS.CPFAULT ( CATPLANTCODE CHAR(20) NOT NULL, FAULTCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), CRITCODE CHAR(20), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (CATPLANTCODE,FAULTCODE) ); CREATE TABLE CMIS.CPLOC ( CATPLANTCODE CHAR(20) NOT NULL, LOCCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (CATPLANTCODE,LOCCODE) ); CREATE TABLE CMIS.CPMANUF ( CATPLANTCODE CHAR(20) NOT NULL, MANUFSYS DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), PRIORITY INTEGER, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (CATPLANTCODE,MANUFSYS) ); CREATE TABLE CMIS.CPSUPP ( CATPLANTCODE CHAR(20) NOT NULL, SUPPSYS DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), PRIORITY INTEGER, LEADTIME INTEGER, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (CATPLANTCODE,SUPPSYS) ); CREATE INDEX CPSUPP_IDX1 ON CMIS.CPSUPP (SUPPSYS); CREATE TABLE CMIS.CPTASK ( CATPLANTCODE CHAR(20) NOT NULL, TASKCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), TASKDES CHAR(100), DURATION DOUBLE PRECISION(15,0), DURUOMCODE CHAR(20), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (CATPLANTCODE,TASKCODE) ); CREATE TABLE CMIS.CPTEXT ( CATPLANTCODE CHAR(20) NOT NULL, TEXTCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), SEQ5 INTEGER, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (CATPLANTCODE,TEXTCODE) ); CREATE TABLE CMIS.CRITTAB ( CRITCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), CRITDES CHAR(100), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (CRITCODE) ); CREATE TABLE CMIS.CTCOND ( CONTRACTSYS DOUBLE PRECISION(15,0) NOT NULL, CONDID DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), PROJTYPE CHAR(1), PROJCODE CHAR(30), CONDCODE CHAR(1), UOMCODE CHAR(20), UOMVALUE DOUBLE PRECISION(15,0), FACTORC CHAR(20), FACTORN DOUBLE PRECISION(15,0), FACTORD DATE, PAYAMT DOUBLE PRECISION(15,0), PAYRATE DOUBLE PRECISION(15,0), LASTINVDATE DATE, LASTUOMVALUE DOUBLE PRECISION(15,0), LASTFACTORC CHAR(20), LASTFACTORN DOUBLE PRECISION(15,0), LASTFACTORD DATE, REMARK VARCHAR(1000), NUMJOBCODE DOUBLE PRECISION(15,0), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPARENUM1 DOUBLE PRECISION(15,0), SAPRENUM2 DOUBLE PRECISION(15,0), SPAREDATE1 DATE, SPAREDATE2 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (CONTRACTSYS,CONDID) ); CREATE INDEX CTCOND_IDX1 ON CMIS.CTCOND (CONTRACTSYS); CREATE TABLE CMIS.CTINV ( TRANID DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), CONTRACTSYS DOUBLE PRECISION(15,0) NOT NULL, CONDID DOUBLE PRECISION(15,0) NOT NULL, INVDATE DATE, INVAMT DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (TRANID) ); CREATE TABLE CMIS.CTLOG ( CONTRACTSYS DOUBLE PRECISION(15,0) NOT NULL, LOGID DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), LOGDATE DATE, CTSTATUSCODE CHAR(20), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (CONTRACTSYS,LOGID) ); CREATE TABLE CMIS.CTRACT ( CONTRACTSYS DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), CONTRACTNO CHAR(30) NOT NULL, CONTRACTDES CHAR(100), DATECT DATE, CUSTSYS CHAR(12) NOT NULL, SCHSTARTDATE DATE, SCHFINISHDATE DATE, ESTPRICE DOUBLE PRECISION(15,0), LOGDATE DATE, CTSTATUSCODE CHAR(20), AUTHORBY CHAR(30), RESPCODE CHAR(30), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPAREDATE1 DATE, SPAREDATE2 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, NOTE VARBINARY(7791), PRIMARY KEY (CONTRACTSYS)); CREATE UNIQUE INDEX CTRACT_IDX1 ON CMIS.CTRACT (CONTRACTNO); CREATE TABLE CMIS.OCTRACT ( CONTRACTSYS DOUBLE PRECISION(15,0) NOT NULL, SEGM CHAR(4) NOT NULL, DATA VARBINARY(8180), PRIMARY KEY (CONTRACTSYS,SEGM) ); CREATE TABLE CMIS.CTSITE ( CONTRACTSYS CHAR(12) NOT NULL, SITESYS DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (CONTRACTSYS) ); CREATE TABLE CMIS.CURRATE ( RECID INTEGER NOT NULL, CURCODE CHAR(12) NOT NULL, U_VERSION CHAR(1), FROMDATE DATE, TODATE DATE, EXCHANGERATE DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (RECID,CURCODE) ); CREATE TABLE CMIS.CURTAB ( CURCODE CHAR(12) NOT NULL, U_VERSION CHAR(1), CURDESC CHAR(100), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (CURCODE) ); CREATE TABLE CMIS.CUSTAB ( CUSTSYS DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), CUSTCODE CHAR(20) NOT NULL, CUSTDES CHAR(100), CUSTALIAS CHAR(30), CUSTNAME CHAR(30), CONTACTPERSON CHAR(30), SYSACCTCODE CHAR(30), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPAREDATE1 DATE, SPAREDATE2 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (CUSTSYS)); CREATE UNIQUE INDEX CUSTAB_IDX1 ON CMIS.CUSTAB (CUSTCODE); CREATE TABLE CMIS.CUSTAD ( CUSTSYS DOUBLE PRECISION(15,0) NOT NULL, CONTACTTYPE CHAR(20) NOT NULL, U_VERSION CHAR(1), ADDR1 CHAR(40), ADDR2 CHAR(40), ADDR3 CHAR(40), ADDR4 CHAR(40), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (CUSTSYS,CONTACTTYPE) ); CREATE TABLE CMIS.CUSTTE ( CUSTSYS DOUBLE PRECISION(15,0) NOT NULL, CONTACTTYPE CHAR(20) NOT NULL, U_VERSION CHAR(1), CONTACTNO CHAR(20), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (CUSTSYS,CONTACTTYPE) ); CREATE TABLE CMIS.DEPT ( DEPTSYS DOUBLE PRECISION(15,0) NOT NULL, SITESYS DOUBLE PRECISION(15,0), U_VERSION CHAR(1), DEPTCODE CHAR(20) NOT NULL, DEPTDES CHAR(100), SYSACCTCODE CHAR(30), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (DEPTSYS)); CREATE UNIQUE INDEX DEPT_IDX1 ON CMIS.DEPT (DEPTCODE); CREATE TABLE CMIS.DEPTRT ( ROOTCODE DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (ROOTCODE) ); CREATE TABLE CMIS.DEPTSTR ( ROOTCODE DOUBLE PRECISION(15,0) NOT NULL, PARENTCODE DOUBLE PRECISION(15,0) NOT NULL, CHILDCODE DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), SEQ5 INTEGER, LEVELNO INTEGER NOT NULL, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (ROOTCODE,PARENTCODE,CHILDCODE) ); CREATE TABLE CMIS.DISCNTS ( DISCCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), DISCDES CHAR(100), DISCRATE DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (DISCCODE) ); CREATE TABLE CMIS.FAILTAB ( FAILCODE CHAR(30) NOT NULL, U_VERSION CHAR(1), FAILDES CHAR(200), FAILCAUSE CHAR(200), FAILEFFECT CHAR(200), FAILACTION CHAR(200), NOTE VARBINARY(7361), PRIMARY KEY (FAILCODE) ); CREATE TABLE CMIS.OFAILTAB ( FAILCODE CHAR(30) NOT NULL, SEGM CHAR(4) NOT NULL, DATA VARBINARY(8158), PRIMARY KEY (FAILCODE,SEGM) ); CREATE TABLE CMIS.FAULT ( FAULTCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), FAULTDES CHAR(200), CRITCODE CHAR(20), SYSACCTCODE CHAR(30), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (FAULTCODE) ); CREATE TABLE CMIS.FAULTCHK ( FAULTCODE CHAR(20) NOT NULL, CHKCODE CHAR(30) NOT NULL, U_VERSION CHAR(1), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (FAULTCODE,CHKCODE) ); CREATE TABLE CMIS.FCTEMP ( TASKCODE CHAR(20) NOT NULL, UOMCODE CHAR(20) NOT NULL, ALARMVALUE DOUBLE PRECISION(15,0) NOT NULL, SCHDATE DATE NOT NULL, U_VERSION CHAR(1), PRIORITY INTEGER, FORECASTSTATUS CHAR(20), JOBCODE DOUBLE PRECISION(15,0), REFNO CHAR(30), PERIOD DOUBLE PRECISION(15,0), INSTPLANTSYS DOUBLE PRECISION(15,0), DELFLAG CHAR(32), PRIMARY KEY (TASKCODE,UOMCODE,ALARMVALUE,SCHDATE) ); CREATE TABLE CMIS.FIMATRAN ( TRANID DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), BATCHNO CHAR(20), RECID CHAR(1), RECBODY VARCHAR(550), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (TRANID) ); CREATE TABLE CMIS.FIXER ( FAULTCODE CHAR(20) NOT NULL, SKILLCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (FAULTCODE,SKILLCODE) ); CREATE TABLE CMIS.FLDHELP ( ENTNAME CHAR(16) NOT NULL, FLDNAME CHAR(16) NOT NULL, U_VERSION CHAR(1), FLDMSG VARBINARY(8159), PRIMARY KEY (ENTNAME,FLDNAME) ); CREATE TABLE CMIS.OFLDHELP ( ENTNAME CHAR(16) NOT NULL, FLDNAME CHAR(16) NOT NULL, SEGM CHAR(4) NOT NULL, DATA VARBINARY(8156), PRIMARY KEY (ENTNAME,FLDNAME,SEGM) ); CREATE TABLE CMIS.FM ( INSTPLANTSYS DOUBLE PRECISION(15,0) NOT NULL, UOMCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (INSTPLANTSYS,UOMCODE) ); CREATE TABLE CMIS.FNDONE ( FNID DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), MODNAME CHAR(20), FNNAME CHAR(20), FACTOR1 CHAR(40), FACTOR2 CHAR(40), FACTOR3 CHAR(40), DATE1 DATE, DATE2 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (FNID) ); CREATE TABLE CMIS.FORECAST ( TASKCODE CHAR(20) NOT NULL, UOMCODE CHAR(20) NOT NULL, ALARMVALUE DOUBLE PRECISION(15,0) NOT NULL, SCHDATE DATE NOT NULL, U_VERSION CHAR(1), PRIORITY INTEGER, FORECASTSTATUS CHAR(20), JOBCODE DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (TASKCODE,UOMCODE,ALARMVALUE,SCHDATE) ); CREATE TABLE CMIS.FOREMEA ( INSTPLANTSYS DOUBLE PRECISION(15,0) NOT NULL, UOMCODE CHAR(20) NOT NULL, FOREID DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), DATEFROM DATE NOT NULL, DATETO DATE NOT NULL, DAILYVALUE DOUBLE PRECISION(15,0) NOT NULL, POSTFLAG CHAR(1), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (INSTPLANTSYS,UOMCODE,FOREID) ); CREATE TABLE CMIS.FORMHELP ( FORMNAME CHAR(12) NOT NULL, U_VERSION CHAR(1), FORMMSG VARBINARY(8179), PRIMARY KEY (FORMNAME) ); CREATE TABLE CMIS.OFORMHELP ( FORMNAME CHAR(12) NOT NULL, SEGM CHAR(4) NOT NULL, DATA VARBINARY(8176), PRIMARY KEY (FORMNAME,SEGM) ); CREATE TABLE CMIS.GENASTRN ( HIDKEY CHAR(1) NOT NULL, U_VERSION CHAR(1), TRANID DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (HIDKEY) ); CREATE TABLE CMIS.GENCMP ( HIDKEY CHAR(1) NOT NULL, U_VERSION CHAR(1), JOBCMPSYS DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (HIDKEY) ); CREATE TABLE CMIS.GENCTR ( HIDKEY CHAR(1) NOT NULL, U_VERSION CHAR(1), CONTRACTSYS CHAR(12) NOT NULL, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (HIDKEY) ); CREATE TABLE CMIS.GENCUST ( HIDKEY CHAR(1) NOT NULL, U_VERSION CHAR(1), CUSTSYS DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (HIDKEY) ); CREATE TABLE CMIS.GENFIBAT ( HIDKEY CHAR(1) NOT NULL, U_VERSION CHAR(1), BATCHNO CHAR(20), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (HIDKEY) ); CREATE TABLE CMIS.GENFIMA ( HIDKEY CHAR(1) NOT NULL, U_VERSION CHAR(1), TRANID DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (HIDKEY) ); CREATE TABLE CMIS.GENGL ( HIDKEY CHAR(1) NOT NULL, U_VERSION CHAR(1), BATCHNO DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (HIDKEY) ); CREATE TABLE CMIS.GENINTS ( HIDKEY CHAR(1) NOT NULL, U_VERSION CHAR(1), INTENTSYS CHAR(32) NOT NULL, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (HIDKEY) ); CREATE TABLE CMIS.GENIR ( HIDKEY CHAR(1) NOT NULL, U_VERSION CHAR(1), IRTRANID DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (HIDKEY) ); CREATE TABLE CMIS.GENIREC ( HIDKEY CHAR(1) NOT NULL, U_VERSION CHAR(1), TRANLID DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (HIDKEY) ); CREATE TABLE CMIS.GENLDG ( ACCTYEAR INTEGER NOT NULL, ACCTMTH INTEGER NOT NULL, ACCTCODE CHAR(30) NOT NULL, U_VERSION CHAR(1), ACTAMT DOUBLE PRECISION(15,0), BUDAMT DOUBLE PRECISION(15,0), COMMITAMT DOUBLE PRECISION(15,0), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPARENUM1 CHAR(32), SPARENUM2 CHAR(32), SPAREDATE1 DATE, SPAREDATE2 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (ACCTYEAR,ACCTMTH,ACCTCODE) ); CREATE TABLE CMIS.GENMAT ( HIDKEY CHAR(1) NOT NULL, U_VERSION CHAR(1), MATLTRANID DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (HIDKEY) ); CREATE TABLE CMIS.GENNO ( HIDKEY CHAR(1) NOT NULL, U_VERSION CHAR(1), ASSETSYS DOUBLE PRECISION(15,0), CATITEMSYS DOUBLE PRECISION(15,0), DEPTSYS DOUBLE PRECISION(15,0), INSTPLANTSYS DOUBLE PRECISION(15,0), JOBCODE DOUBLE PRECISION(15,0), REQCODE DOUBLE PRECISION(15,0), LABTRANID DOUBLE PRECISION(15,0), INVTRANID DOUBLE PRECISION(15,0), RESGRPSYS DOUBLE PRECISION(15,0), TRANID DOUBLE PRECISION(15,0), METERLOGID DOUBLE PRECISION(15,0), URDEFSYS DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (HIDKEY) ); CREATE TABLE CMIS.GENPO ( HIDKEY CHAR(1) NOT NULL, U_VERSION CHAR(1), POSYS DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (HIDKEY) ); CREATE TABLE CMIS.GENPREC ( HIDKEY CHAR(1) NOT NULL, U_VERSION CHAR(1), TRANLID DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (HIDKEY) ); CREATE TABLE CMIS.GENREVAL ( HIDKEY CHAR(1) NOT NULL, U_VERSION CHAR(1), BATCHNO DOUBLE PRECISION(15,0) NOT NULL, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (HIDKEY) ); CREATE TABLE CMIS.GENSTK ( HIDKEY CHAR(1) NOT NULL, U_VERSION CHAR(1), STKADJID DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (HIDKEY) ); CREATE TABLE CMIS.GENSUNGL ( HIDKEY CHAR(1) NOT NULL, U_VERSION CHAR(1), SUNGLNO DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (HIDKEY) ); CREATE TABLE CMIS.GENTRAL ( HIDKEY CHAR(1) NOT NULL, U_VERSION CHAR(1), TRANLID DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (HIDKEY) ); CREATE TABLE CMIS.INTENT ( INTENTSYS CHAR(32) NOT NULL, U_VERSION CHAR(1), LEGALNAME CHAR(50), INTENTDES CHAR(100), INTENTCODE CHAR(20), INTENTALIAS CHAR(30), CONTACTPERSON CHAR(30), SYSACCTCODE CHAR(30), CRSTCODE CHAR(20), REF1 CHAR(20), REF2 CHAR(20), REF3 CHAR(20), UNALLOCAMT DOUBLE PRECISION(15,0), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPAREDATE1 DATE, SPAREDATE2 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (INTENTSYS)); CREATE UNIQUE INDEX INTENT_IDX1 ON CMIS.INTENT (INTENTCODE); CREATE TABLE CMIS.INTENTAD ( INTENTSYS CHAR(32) NOT NULL, CONTACTTYPE CHAR(20) NOT NULL, U_VERSION CHAR(1), ADDR1 CHAR(40), ADDR2 CHAR(40), ADDR3 CHAR(40), ADDR4 CHAR(40), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPAREDATE1 DATE, SPAREDATE2 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (INTENTSYS,CONTACTTYPE) ); CREATE TABLE CMIS.INTENTTE ( INTENTSYS CHAR(32) NOT NULL, CONTACTTYPE CHAR(20) NOT NULL, U_VERSION CHAR(1), PHONENO CHAR(20), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (INTENTSYS,CONTACTTYPE) ); CREATE TABLE CMIS.INVPARA ( HIDKEY CHAR(1) NOT NULL, U_VERSION CHAR(1), PERSONLU CHAR(1), POSTCOST CHAR(1), MULTWAREHSE CHAR(1), ATHRESHOLD DOUBLE PRECISION(15,0), BTHRESHOLD DOUBLE PRECISION(15,0), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPARECHAR3 CHAR(20), SPARECHAR4 CHAR(20), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPARENUM3 DOUBLE PRECISION(15,0), SPARENUM4 DOUBLE PRECISION(15,0), SPAREDATE1 DATE, SPAREDATE2 DATE, SPAREDATE3 DATE, SPAREDATE4 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (HIDKEY) ); CREATE TABLE CMIS.INVTRAN ( INVTRANID DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), JOBCODE DOUBLE PRECISION(15,0) NOT NULL, RESCODE CHAR(20) NOT NULL, RESTYPE CHAR(8) NOT NULL, RESGRPSYS DOUBLE PRECISION(15,0) NOT NULL, WORKGRPCODE CHAR(20), INVNO CHAR(20) NOT NULL, INVDATE DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (INVTRANID) ); CREATE INDEX INVTRAN_IDX1 ON CMIS.INVTRAN (JOBCODE,RESCODE); CREATE TABLE CMIS.INVTRAND ( INVTRANID DOUBLE PRECISION(15,0) NOT NULL, LNUM CHAR(12) NOT NULL, U_VERSION CHAR(1), TRANDATE DATE NOT NULL, FAULTCODE CHAR(20), ACCTCAT CHAR(12) NOT NULL, INVCOST DOUBLE PRECISION(15,0) NOT NULL, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, LABHR DOUBLE PRECISION(15,0), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPARENUM1 CHAR(32), SPARENUM2 CHAR(32), SPAREDATE1 DATE, SPAREDATE2 DATE, PRIMARY KEY (INVTRANID,LNUM) ); CREATE TABLE CMIS.IPATTR ( INSTPLANTSYS DOUBLE PRECISION(15,0) NOT NULL, PLANTTYPE CHAR(20) NOT NULL, ATTRCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), SEQ5 INTEGER, ATTRVALUE CHAR(20), REMARK CHAR(30), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (INSTPLANTSYS,PLANTTYPE,ATTRCODE) ); CREATE TABLE CMIS.IPCATG ( INSTPLANTSYS DOUBLE PRECISION(15,0) NOT NULL, CATGCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (INSTPLANTSYS,CATGCODE) ); CREATE TABLE CMIS.IPCITEM ( INSTPLANTSYS DOUBLE PRECISION(15,0) NOT NULL, INVFLAG CHAR(1) NOT NULL, CATITEMCODE CHAR(30) NOT NULL, U_VERSION CHAR(1), CATITEMDES CHAR(100), QTYREQD DOUBLE PRECISION(15,0), SEQ5 INTEGER, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (INSTPLANTSYS,INVFLAG,CATITEMCODE) ); CREATE TABLE CMIS.IPCUST ( INSTPLANTSYS DOUBLE PRECISION(15,0) NOT NULL, CUSTSYS DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (INSTPLANTSYS,CUSTSYS) ); CREATE TABLE CMIS.IPDEPT ( INSTPLANTSYS DOUBLE PRECISION(15,0) NOT NULL, MOVEID DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), MOVEDATE DATE, DEPTSYS DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (INSTPLANTSYS,MOVEID) ); CREATE TABLE CMIS.IPFAULT ( INSTPLANTSYS DOUBLE PRECISION(15,0) NOT NULL, FAULTCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), CRITCODE CHAR(20), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (INSTPLANTSYS,FAULTCODE) ); CREATE TABLE CMIS.IPLOC ( INSTPLANTSYS DOUBLE PRECISION(15,0) NOT NULL, LOCID DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), LOCDATE DATE, LOCCODE CHAR(20) NOT NULL, UOMCODE CHAR(20), ACTUALREADING DOUBLE PRECISION(15,0), TOTALCOST DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (INSTPLANTSYS,LOCID) ); CREATE TABLE CMIS.IPOPR ( INSTPLANTSYS DOUBLE PRECISION(15,0) NOT NULL, OPRDATE DATE NOT NULL, OPRTIME TIME NOT NULL, U_VERSION CHAR(1), TOOPRTIME TIME, PLANTSTATUSCODE CHAR(20), JOBCODE DOUBLE PRECISION(15,0), REMARK CHAR(60), POSTFLAG CHAR(1), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (INSTPLANTSYS,OPRDATE,OPRTIME) ); CREATE TABLE CMIS.IPTEXT ( INSTPLANTSYS DOUBLE PRECISION(15,0) NOT NULL, TEXTCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), SEQ5 INTEGER, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (INSTPLANTSYS,TEXTCODE) ); CREATE TABLE CMIS.IRTRAN ( IRTRANID DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), TRANTYPE CHAR(1), DATETRAN DATE, ACCTYEAR INTEGER, ACCTMTH INTEGER, CATITEMSYS DOUBLE PRECISION(15,0), WAREHSECODE CHAR(20), BINCODE CHAR(20), QTYISSUE DOUBLE PRECISION(15,0), UNITCOST DOUBLE PRECISION(15,0), COSTISSUE DOUBLE PRECISION(15,0), JOBCODE DOUBLE PRECISION(15,0), FAULTCODE CHAR(20), INSTPLANTSYS DOUBLE PRECISION(15,0), RESCODE CHAR(20), ACCTCODE CHAR(30), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (IRTRANID) ); CREATE TABLE CMIS.JACTION ( JOBCODE DOUBLE PRECISION(15,0) NOT NULL, FAULTCODE CHAR(20) NOT NULL, FAULTID DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), CAUSECODE CHAR(20), EFFECTCODE CHAR(20), ACTIONCODE CHAR(20), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (JOBCODE,FAULTCODE,FAULTID) ); CREATE TABLE CMIS.JBCAL ( INSTPLANTSYS DOUBLE PRECISION(15,0) NOT NULL, JOBCODE DOUBLE PRECISION(15,0) NOT NULL, LOGID DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), CMPNAME CHAR(50), CALDATE DATE, CALVALUE DOUBLE PRECISION(15,0), UOMCODE CHAR(20), TOLFLAG CHAR(1), LIMITFLAG CHAR(1), PLANTSTATUS CHAR(20), CALPERIOD DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (INSTPLANTSYS,JOBCODE,LOGID) ); CREATE TABLE CMIS.JCFAULT ( JOBCMPSYS DOUBLE PRECISION(15,0) NOT NULL, LNUM INTEGER NOT NULL, U_VERSION CHAR(1), CHKCODE CHAR(30), RESPONSECODE CHAR(40), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (JOBCMPSYS,LNUM) ); CREATE TABLE CMIS.JCITEM ( JOBCODE DOUBLE PRECISION(15,0) NOT NULL, FAULTCODE CHAR(20) NOT NULL, INVFLAG CHAR(1) NOT NULL, CATITEMCODE CHAR(30) NOT NULL, U_VERSION CHAR(1), CATITEMDES CHAR(100), WAREHSECODE CHAR(20), QTYREQD DOUBLE PRECISION(15,0), QTYISSUE DOUBLE PRECISION(15,0), QTYRETURN DOUBLE PRECISION(15,0), QTYUSED DOUBLE PRECISION(15,0), SEQ5 INTEGER, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (JOBCODE,FAULTCODE,INVFLAG,CATITEMCODE) ); CREATE TABLE CMIS.JFAULT ( JOBCODE DOUBLE PRECISION(15,0) NOT NULL, FAULTCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), INSTPLANTSYS DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (JOBCODE,FAULTCODE) ); CREATE TABLE CMIS.JFIXER ( JOBCODE DOUBLE PRECISION(15,0) NOT NULL, FAULTCODE CHAR(20) NOT NULL, SKILLCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), ESTHR DOUBLE PRECISION(15,0), ACTHR DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (JOBCODE,FAULTCODE,SKILLCODE) ); CREATE TABLE CMIS.JOBCMP ( JOBCMPSYS DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), JOBCMPDES CHAR(40), RESTYPE CHAR(8), RESGRPSYS DOUBLE PRECISION(15,0), SUPPSYS DOUBLE PRECISION(15,0), SITESYS DOUBLE PRECISION(15,0), CMPDATE DATE, CMPTIME TIME, CMPBY CHAR(30), INSTPLANTSYS DOUBLE PRECISION(15,0), FAULTCODE CHAR(20), MAINTTYPECODE CHAR(20), TKCGCODE CHAR(20), PRIORITY INTEGER, CMPSTATUS CHAR(12), REQMTD CHAR(20), ACKREQ CHAR(20), JOBCODE DOUBLE PRECISION(15,0), ESTCOMPLTDATE DATE, ESTPRICE DOUBLE PRECISION(15,0), ACCTCODE CHAR(30), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPAREDATE1 DATE, SPAREDATE2 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, NOTE VARBINARY(7757), PRIMARY KEY (JOBCMPSYS) ); CREATE TABLE CMIS.OJOBCMP ( JOBCMPSYS DOUBLE PRECISION(15,0) NOT NULL, SEGM CHAR(4) NOT NULL, DATA VARBINARY(8180), PRIMARY KEY (JOBCMPSYS,SEGM) ); CREATE TABLE CMIS.JOBCRT ( CONTRACTNO CHAR(30) NOT NULL, U_VERSION CHAR(1), CONTRACTDES CHAR(100), DATECRT DATE, JOBCODE DOUBLE PRECISION(15,0), JOBCMPSYS DOUBLE PRECISION(15,0), SUPPSYS DOUBLE PRECISION(15,0), ESTPRICE DOUBLE PRECISION(15,0), ESTCPTDATE DATE, REVCPTDATE DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, NOTE VARBINARY(7947), PRIMARY KEY (CONTRACTNO) ); CREATE TABLE CMIS.OJOBCRT ( CONTRACTNO CHAR(30) NOT NULL, SEGM CHAR(4) NOT NULL, DATA VARBINARY(8158), PRIMARY KEY (CONTRACTNO,SEGM) ); CREATE TABLE CMIS.JOBREQ ( REQCODE DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), JOBCODE DOUBLE PRECISION(15,0), REQDES CHAR(100), INSTPLANTSYS DOUBLE PRECISION(15,0) NOT NULL, FAULTCODE CHAR(20), EFFECTCODE CHAR(20), PRIORITY INTEGER, REQSTATUSCODE CHAR(12) NOT NULL, MAINTTYPECODE CHAR(20), RESTYPE CHAR(8), RESGRPSYS DOUBLE PRECISION(15,0), ENTERBY CHAR(30), DATERAISE DATE, SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPAREDATE1 DATE, SPAREDATE2 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (REQCODE) ); CREATE TABLE CMIS.JOBTMP ( JOBCODE DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), JOBDES CHAR(100), EXTJOBDES CHAR(200), TASKCODE CHAR(20), INSTPLANTSYS DOUBLE PRECISION(15,0), ALUOMCODE CHAR(20), ALARMVALUE DOUBLE PRECISION(15,0), MAINTTYPECODE CHAR(20), SCHDATE DATE, PRIORITY INTEGER, DURUOMCODE CHAR(20), DURATION DOUBLE PRECISION(15,0), ACCTCODE CHAR(30), PLANNER CHAR(30), RESPEMPCODE CHAR(20), LOGDATE DATE, JSTATUSCODE CHAR(20), PREFWAREHSECODE CHAR(20), SYSACCTCODE CHAR(30), WORKDONE VARCHAR(2000), TXTJOBCODE CHAR(30), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPAREDATE1 DATE, SPAREDATE2 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, NOTE VARBINARY(5449), PRIMARY KEY (JOBCODE) ); CREATE INDEX JOBTMP_IDX1 ON CMIS.JOBTMP (TASKCODE); CREATE INDEX JOBTMP_IDX2 ON CMIS.JOBTMP (INSTPLANTSYS); CREATE TABLE CMIS.OJOBTMP ( JOBCODE DOUBLE PRECISION(15,0) NOT NULL, SEGM CHAR(4) NOT NULL, DATA VARBINARY(8180), PRIMARY KEY (JOBCODE,SEGM) ); CREATE TABLE CMIS.JSTATUS ( JSTATUSCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), JSTATUSDES CHAR(100), JSTATUSNO INTEGER NOT NULL, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (JSTATUSCODE) ); CREATE TABLE CMIS.JTEXT ( JOBCODE DOUBLE PRECISION(15,0) NOT NULL, TEXTCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), SEQ5 INTEGER, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (JOBCODE,TEXTCODE) ); CREATE TABLE CMIS.JTKCG ( JOBCODE DOUBLE PRECISION(15,0) NOT NULL, TKCGCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (JOBCODE,TKCGCODE) ); CREATE TABLE CMIS.JWORK ( JOBCODE DOUBLE PRECISION(15,0) NOT NULL, LOGID DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), SEQ5 INTEGER, SHORTNOTE CHAR(100), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, WORKDES VARBINARY(8013), PRIMARY KEY (JOBCODE,LOGID) ); CREATE TABLE CMIS.OJWORK ( JOBCODE DOUBLE PRECISION(15,0) NOT NULL, LOGID DOUBLE PRECISION(15,0) NOT NULL, SEGM CHAR(4) NOT NULL, DATA VARBINARY(8172), PRIMARY KEY (JOBCODE,LOGID,SEGM) ); CREATE TABLE CMIS.JWORKCONV ( JOBCODE DOUBLE PRECISION(15,0) NOT NULL, SHORTNOTE CHAR(100), WORKDES CHAR(255), PRIMARY KEY (JOBCODE) ); CREATE TABLE CMIS.JWORKOLD ( JOBCODE DOUBLE PRECISION(15,0) NOT NULL, LOGID DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), SEQ5 INTEGER, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, WORKDES VARBINARY(8113), PRIMARY KEY (JOBCODE,LOGID) ); CREATE TABLE CMIS.OJWORKOLD ( JOBCODE DOUBLE PRECISION(15,0) NOT NULL, LOGID DOUBLE PRECISION(15,0) NOT NULL, SEGM CHAR(4) NOT NULL, DATA VARBINARY(8172), PRIMARY KEY (JOBCODE,LOGID,SEGM) ); CREATE TABLE CMIS.LABTRAN ( LABTRANID DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), JOBCODE DOUBLE PRECISION(15,0) NOT NULL, RESCODE CHAR(20) NOT NULL, RESTYPE CHAR(8), RESGRPSYS DOUBLE PRECISION(15,0), WORKGRPCODE CHAR(20), TRANDATE DATE NOT NULL, FAULTCODE CHAR(20), SKILLCODE CHAR(20), CAUSECODE CHAR(20), EFFECTCODE CHAR(20), ACTIONCODE CHAR(20), STARTTIME TIME, FINISHTIME TIME, LABHR DOUBLE PRECISION(15,0), PAYCODE CHAR(20) NOT NULL, LABCOST DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (LABTRANID) ); CREATE INDEX LABTRAN_IDX1 ON CMIS.LABTRAN (JOBCODE,RESCODE); CREATE TABLE CMIS.LDGTRAN ( TRANID DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), ACCTYEAR INTEGER NOT NULL, ACCTMTH INTEGER NOT NULL, ACCTCODE CHAR(30) NOT NULL, TRANDATE DATE NOT NULL, REFTYPE CHAR(20) NOT NULL, REFID CHAR(20), SOURCEID CHAR(20), TRANAMT DOUBLE PRECISION(15,0) NOT NULL, REMARK CHAR(60), BATCHNO CHAR(20) NOT NULL, JNLNO CHAR(20), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPARENUM1 CHAR(32), SPARENUM2 CHAR(32), SPAREDATE1 DATE, SPAREDATE2 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (TRANID,ACCTYEAR,ACCTMTH,REFTYPE,BATCHNO) ); CREATE TABLE CMIS.LOC ( LOCCODE CHAR(20) NOT NULL, SITESYS DOUBLE PRECISION(15,0), LOCDES CHAR(100) NOT NULL, CRITCODE CHAR(20), LOCCLASS CHAR(20), SYSACCTCODE CHAR(30), U_VERSION CHAR(1), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (LOCCODE) ); CREATE TABLE CMIS.LOVASSET ( FROMTABLE CHAR(8) NOT NULL, CODE CHAR(20) NOT NULL, U_VERSION CHAR(1), DES CHAR(50), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (FROMTABLE,CODE) ); CREATE TABLE CMIS.LOVINV ( FROMTABLE CHAR(8) NOT NULL, CODE CHAR(20) NOT NULL, U_VERSION CHAR(1), DES CHAR(50), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (FROMTABLE,CODE) ); CREATE TABLE CMIS.LOVTAB ( FROMTABLE CHAR(8) NOT NULL, CODE CHAR(20) NOT NULL, U_VERSION CHAR(1), DES CHAR(50), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (FROMTABLE,CODE) ); CREATE TABLE CMIS.MAINTYPE ( MAINTTYPECODE CHAR(20) NOT NULL, U_VERSION CHAR(1), MAINTTYPEDES CHAR(100), SYSACCTCODE CHAR(30), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (MAINTTYPECODE) ); CREATE TABLE CMIS.MATLTRAN ( MATLTRANID DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), JOBCODE DOUBLE PRECISION(15,0), RESCODE CHAR(20), RESTYPE CHAR(8), RESGRPSYS DOUBLE PRECISION(15,0), WORKGRPCODE CHAR(20), TRANDATE DATE, FAULTCODE CHAR(20), MATLCOST DOUBLE PRECISION(15,0), REMARK CHAR(40), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (MATLTRANID) ); CREATE TABLE CMIS.METERLOG ( INSTPLANTSYS DOUBLE PRECISION(15,0) NOT NULL, UOMCODE CHAR(20) NOT NULL, METERLOGID DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), DATEMEASURE DATE NOT NULL, RESETFLAG CHAR(1), METERREADING DOUBLE PRECISION(15,0) NOT NULL, ACTUALREADING DOUBLE PRECISION(15,0), METERID DOUBLE PRECISION(15,0), JOBCODE DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (INSTPLANTSYS,UOMCODE,METERLOGID) ); CREATE TABLE CMIS.METERS ( INSTPLANTSYS DOUBLE PRECISION(15,0) NOT NULL, UOMCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (INSTPLANTSYS,UOMCODE) ); CREATE TABLE CMIS.MODTAB ( MODNAME CHAR(20) NOT NULL, U_VERSION CHAR(1), MODFLAG CHAR(1), MAXUSER INTEGER, NOUSER INTEGER, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (MODNAME) ); CREATE TABLE CMIS.NONINV ( NONINVCODE CHAR(30) NOT NULL, U_VERSION CHAR(1), NONINVDES CHAR(100), STDCOST DOUBLE PRECISION(15,0), UOMCODE CHAR(20), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (NONINVCODE) ); CREATE TABLE CMIS.NOTECONV ( FROMTABLE CHAR(2) NOT NULL, KEYNUM DOUBLE PRECISION(15,0) NOT NULL, NOTE CHAR(255), PRIMARY KEY (FROMTABLE,KEYNUM) ); CREATE TABLE CMIS.NOTETAB ( FROMTABLE CHAR(2) NOT NULL, KEYCHAR CHAR(30) NOT NULL, KEYNUM DOUBLE PRECISION(15,0), U_VERSION CHAR(1), SHORTNOTE CHAR(100), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, NOTE VARBINARY(7993), PRIMARY KEY (FROMTABLE,KEYCHAR) ); CREATE INDEX NOTETAB_DX2 ON CMIS.NOTETAB (FROMTABLE,KEYNUM); CREATE TABLE CMIS.ONOTETAB ( FROMTABLE CHAR(2) NOT NULL, KEYCHAR CHAR(30) NOT NULL, SEGM CHAR(4) NOT NULL, DATA VARBINARY(8156), PRIMARY KEY (FROMTABLE,KEYCHAR,SEGM) ); CREATE TABLE CMIS.NURDEF ( URDEFSYS DOUBLE PRECISION(15,0) NOT NULL, URDEFYEAR CHAR(2) NOT NULL, SIGNALNO INTEGER NOT NULL, U_VERSION CHAR(1), DTG CHAR(20), FROMSHIP CHAR(40), TODEST1 CHAR(40), TODEST2 CHAR(40), TODEST3 CHAR(40), INFO1 CHAR(200), INFO2 CHAR(200), INFO3 CHAR(200), INFO4 CHAR(200), INFO5 CHAR(200), INFO6 CHAR(200), SICACTSUPP CHAR(12), SICACTTECH CHAR(12), SICACTOPR CHAR(12), SUBJ CHAR(200), INSTPLANTSYS DOUBLE PRECISION(15,0), INSTPLANTDES CHAR(100), PARENTPLANT DOUBLE PRECISION(15,0), JOBCODE DOUBLE PRECISION(15,0), JOBDES CHAR(100), PRIORITY INTEGER, SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPARCHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPAREDATE1 DATE, SPAREDATE2 DATE, DEFSKILL CHAR(200), EFFSHIP CHAR(200), SPECEQUIP CHAR(200), AVAILREPAIR CHAR(200), QUALITYCONTROL VARCHAR(400), APLNO CHAR(200), APLDES CHAR(200), NEXTPARENT CHAR(200), PDNN CHAR(200), TM200 CHAR(12), ORDERNO CHAR(15), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, NOTE VARBINARY(4176), PRIMARY KEY (URDEFSYS,URDEFYEAR,SIGNALNO) ); CREATE TABLE CMIS.ONURDEF ( URDEFSYS DOUBLE PRECISION(15,0) NOT NULL, URDEFYEAR CHAR(2) NOT NULL, SIGNALNO INTEGER NOT NULL, SEGM CHAR(4) NOT NULL, DATA VARBINARY(8174), PRIMARY KEY (URDEFSYS,URDEFYEAR,SIGNALNO,SEGM) ); CREATE TABLE CMIS.PARAGS ( URDEFSYS DOUBLE PRECISION(15,0) NOT NULL, URDEFYEAR CHAR(2) NOT NULL, SIGNALNO INTEGER NOT NULL, CATITEMSYS DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), STOCKNO CHAR(30), PUBLICREF CHAR(50), CATITEMDES CHAR(100), QTYREQD DOUBLE PRECISION(15,0), QTYUSED DOUBLE PRECISION(15,0), MANAGECODE CHAR(50), QTYALLOW DOUBLE PRECISION(15,0), SEERCODE CHAR(1), DEMANDREF CHAR(50), ACCEPTALT CHAR(50), UOMCODE CHAR(20), SHIPDETAIL CHAR(50), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (URDEFSYS,URDEFYEAR,SIGNALNO,CATITEMSYS) ); CREATE TABLE CMIS.PAY ( PAYCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), PAYDES CHAR(100), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (PAYCODE) ); CREATE TABLE CMIS.PCODETAB ( INTCODE CHAR(20) NOT NULL, CODE CHAR(20) NOT NULL, U_VERSION CHAR(1), DES CHAR(50), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (INTCODE,CODE) ); CREATE TABLE CMIS.PERTAB ( PERCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), PERNAME CHAR(50), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPARECHAR3 CHAR(20), SPARECHAR4 CHAR(20), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPARENUM3 DOUBLE PRECISION(15,0), SPARENUM4 CHAR(32), SPAREDATE1 DATE, SPAREDATE2 DATE, SPAREDATE3 DATE, SPAREDATE4 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (PERCODE) ); CREATE TABLE CMIS.PLANTRT ( ROOTCODE DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (ROOTCODE) ); CREATE TABLE CMIS.PLANTSTATUS ( PLANTSTATUSCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), PLANTSTATUSDES CHAR(30), PLANTSTATUSNO INTEGER, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (PLANTSTATUSCODE) ); CREATE TABLE CMIS.PLANTSTR ( ROOTCODE DOUBLE PRECISION(15,0) NOT NULL, PARENTCODE DOUBLE PRECISION(15,0) NOT NULL, CHILDCODE DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), SEQ5 INTEGER, LEVELNO INTEGER NOT NULL, LRATIO DOUBLE PRECISION(15,0), NLRATIO DOUBLE PRECISION(15,0), BUDRATIO DOUBLE PRECISION(15,0), METERPOSTFLAG CHAR(1), EFFSTARTDATE DATE, EFFENDDATE DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (ROOTCODE,PARENTCODE,CHILDCODE) ); CREATE TABLE CMIS.PNTATTR ( PLANTTYPE CHAR(20) NOT NULL, U_VERSION CHAR(1), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (PLANTTYPE) ); CREATE TABLE CMIS.PNTTYPE ( PLANTTYPE CHAR(20) NOT NULL, ATTRCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), SEQ5 INTEGER, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (PLANTTYPE,ATTRCODE) ); CREATE TABLE CMIS.POCILOG ( POSYS DOUBLE PRECISION(15,0) NOT NULL, LNUM INTEGER NOT NULL, LOGID INTEGER NOT NULL, U_VERSION CHAR(1), WAREHSECODE CHAR(20), CATITEMCODE CHAR(30), PKGUOMCODE CHAR(20), RECEIPTSTATUS CHAR(20), INVOICESTATUS CHAR(20), JOBCODE CHAR(12), DISCOUNTCODE CHAR(20), TAXCODE CHAR(20), INVFLAG CHAR(20), CATITEMDES CHAR(100), QTYORDER DOUBLE PRECISION(15,0), QTYOUTSTANDING DOUBLE PRECISION(15,0), QTYRECEIPT DOUBLE PRECISION(15,0), QTYACCEPT DOUBLE PRECISION(15,0), QTYREJECT DOUBLE PRECISION(15,0), QTYINVOICE DOUBLE PRECISION(15,0), UNITPRICE DOUBLE PRECISION(15,0), SUPPITEMNO CHAR(20), DATEDUE DATE, ACCTCODE CHAR(30), PRCODE CHAR(20), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPARECHAR3 CHAR(20), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPARENUM3 DOUBLE PRECISION(15,0), SPAREDATE1 DATE, SPAREDATE2 DATE, SPAREDATE3 DATE, LOGSTATUS CHAR(20), TAXAMT DOUBLE PRECISION(15,0), TAXRATE DOUBLE PRECISION(15,0), DISCRATE DOUBLE PRECISION(15,0), DELIVERTO CHAR(30), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (POSYS,LNUM,LOGID) ); CREATE TABLE CMIS.POINV ( TRANID DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), POSYS DOUBLE PRECISION(15,0) NOT NULL, LNUM INTEGER NOT NULL, LOGID INTEGER, CATITEMCODE CHAR(30), TRANDATE DATE, UNITPRICE DOUBLE PRECISION(15,0), TOTALPRICE DOUBLE PRECISION(15,0), CURCODE CHAR(12), EXCHANGERATE DOUBLE PRECISION(15,0), GOODSRECNO CHAR(20), SUPPREF CHAR(20), COMMENT CHAR(60), INVOICESTATUS CHAR(20) NOT NULL, DONEBY CHAR(30), QTYINVOICE DOUBLE PRECISION(15,0), ACCTCODE CHAR(30), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (TRANID) ); CREATE TABLE CMIS.POITEM ( POSYS DOUBLE PRECISION(15,0) NOT NULL, LNUM INTEGER NOT NULL, U_VERSION CHAR(1), WAREHSECODE CHAR(20), CATITEMCODE CHAR(30) NOT NULL, PKGUOMCODE CHAR(20), RECEIPTSTATUS CHAR(20), INVOICESTATUS CHAR(20), JOBCODE DOUBLE PRECISION(15,0), DISCOUNTCODE CHAR(20), TAXCODE CHAR(20), ITEMSTATUS CHAR(20), INVFLAG CHAR(20), CATITEMDES CHAR(100), QTYORDER DOUBLE PRECISION(15,0), QTYOUTSTANDING DOUBLE PRECISION(15,0), QTYRECEIPT DOUBLE PRECISION(15,0), QTYACCEPT DOUBLE PRECISION(15,0), QTYREJECT DOUBLE PRECISION(15,0), QTYINVOICE DOUBLE PRECISION(15,0), UNITPRICE DOUBLE PRECISION(15,0), SUPPITEMNO CHAR(20), DATEDUE DATE, ACCTCODE CHAR(30), PRCODE CHAR(20), TAXAMT DOUBLE PRECISION(15,0), TAXRATE DOUBLE PRECISION(15,0), DISCRATE DOUBLE PRECISION(15,0), DELIVERTO CHAR(30), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPARECHAR3 CHAR(20), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPARENUM3 DOUBLE PRECISION(15,0), SPAREDATE1 DATE, SPAREDATE2 DATE, SPAREDATE3 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (POSYS,LNUM) ); CREATE TABLE CMIS.POMAS ( POSYS DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), SUPPSYS DOUBLE PRECISION(15,0) NOT NULL, CURCODE CHAR(12) NOT NULL, EXCHANGERATE DOUBLE PRECISION(15,0), DISCOUNTCODE CHAR(20), TAXCODE CHAR(20), CODEA CHAR(20), CODEB CHAR(20), CODEC CHAR(20), CODED CHAR(20), CODEE CHAR(20), CODEF CHAR(20), CODEG CHAR(20), CODEH CHAR(20), CODEI CHAR(20), CODEJ CHAR(20), POCODE CHAR(20), POALIAS CHAR(30), ORDERDATE DATE, PRCODE CHAR(20), DATEDUE DATE, RATETYPE CHAR(20) NOT NULL, ORDERSTATUS CHAR(20) NOT NULL, RECEIPTSTATUS CHAR(20), INVOICESTATUS CHAR(20), DELIVERTO CHAR(30), DISCRATE DOUBLE PRECISION(15,0), TAXRATE DOUBLE PRECISION(15,0), TAXAMT DOUBLE PRECISION(15,0), PRINTEDFLAG CHAR(20), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPARECHAR3 CHAR(20), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPARENUM3 DOUBLE PRECISION(15,0), SPAREDATE1 DATE, SPAREDATE2 DATE, SPAREDATE3 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (POSYS) ); CREATE TABLE CMIS.POREPT ( TRANID DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), POSYS DOUBLE PRECISION(15,0) NOT NULL, LNUM INTEGER NOT NULL, LOGID INTEGER, TRANDATE DATE, QTYRECEIPT DOUBLE PRECISION(15,0), QTYACCEPT DOUBLE PRECISION(15,0), QTYREJECT DOUBLE PRECISION(15,0), CATITEMCODE CHAR(30), WAREHSECODE CHAR(20), BINCODE CHAR(20), SUPPREF CHAR(20), COMMENT CHAR(60), DONEBY CHAR(30), INVFLAG CHAR(20), GOODSRECNO CHAR(20), RECEIPTSTATUS CHAR(20) NOT NULL, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (TRANID) ); CREATE TABLE CMIS.PURPARA ( HIDKEY CHAR(1) NOT NULL, U_VERSION CHAR(1), PERSONLU CHAR(1), POSTMATL CHAR(20), USECONTRACT CHAR(20), VARPERCENT DOUBLE PRECISION(15,0), DEFCURCODE CHAR(12), TOLPERCENT DOUBLE PRECISION(15,0), TOLCOSTTYPE CHAR(1), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPARECHAR3 CHAR(20), SPARECHAR4 CHAR(20), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPARENUM3 DOUBLE PRECISION(15,0), SPARENUM4 DOUBLE PRECISION(15,0), SPAREDATE1 DATE, SPAREDATE2 DATE, SPAREDATE3 DATE, SPAREDATE4 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (HIDKEY) ); CREATE TABLE CMIS.RESGRP ( RESGRPSYS DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), RESTYPE CHAR(8) NOT NULL, RESGRPCODE CHAR(20) NOT NULL, RESGRPDES CHAR(100), RESGRPALIAS CHAR(30), RESGRPNAME CHAR(30), CONTACTPERSON CHAR(30), COSTRATE DOUBLE PRECISION(15,0), INTREFNO CHAR(12), PREFFLAG CHAR(12), CREDITLIMIT DOUBLE PRECISION(15,0), CURCODE CHAR(12), SYSACCTCODE CHAR(30), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPAREDATE1 DATE, SPAREDATE2 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (RESGRPSYS)); CREATE UNIQUE INDEX RESGRP_IDX1 ON CMIS.RESGRP (RESTYPE,RESGRPCODE); CREATE INDEX RESGRP_IDX2 ON CMIS.RESGRP (RESGRPCODE); CREATE TABLE CMIS.RESGRPAD ( RESGRPSYS DOUBLE PRECISION(15,0) NOT NULL, CONTACTTYPE CHAR(20) NOT NULL, U_VERSION CHAR(1), ADDR1 CHAR(40), ADDR2 CHAR(40), ADDR3 CHAR(40), ADDR4 CHAR(40), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (RESGRPSYS,CONTACTTYPE) ); CREATE TABLE CMIS.RESGRPTE ( RESGRPSYS DOUBLE PRECISION(15,0) NOT NULL, CONTACTTYPE CHAR(20) NOT NULL, U_VERSION CHAR(1), CONTACTNO CHAR(20), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (RESGRPSYS,CONTACTTYPE) ); CREATE TABLE CMIS.RESOPR ( RESCODE CHAR(20) NOT NULL, OPRDATE DATE NOT NULL, OPRTIME TIME NOT NULL, U_VERSION CHAR(1), TOOPRTIME TIME NOT NULL, RSSTATUSCODE CHAR(20) NOT NULL, REMARK CHAR(30), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (RESCODE,OPRDATE,OPRTIME) ); CREATE TABLE CMIS.RESPAY ( RESCODE CHAR(20) NOT NULL, PAYCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), PAYRATE DOUBLE PRECISION(15,0), PAYAMT DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (RESCODE,PAYCODE) ); CREATE TABLE CMIS.RESPTAB ( RESPCODE CHAR(30) NOT NULL, U_VERSION CHAR(1), RESPNAME CHAR(50), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (RESPCODE) ); CREATE TABLE CMIS.RESRC ( RESCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), SURNAME CHAR(30), FIRSTNAME CHAR(30), RESTYPE CHAR(8) NOT NULL, RESGRPSYS DOUBLE PRECISION(15,0) NOT NULL, POSITION CHAR(30), WORKGRPCODE CHAR(20), STARTDATE DATE, ENDDATE DATE, BASICPAY DOUBLE PRECISION(15,0), RESALIAS CHAR(30), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPAREDATE1 DATE, SPAREDATE2 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (RESCODE) ); CREATE TABLE CMIS.RESRCADR ( RESCODE CHAR(20) NOT NULL, CONTACTTYPE CHAR(20) NOT NULL, U_VERSION CHAR(1), ADDR1 CHAR(40), ADDR2 CHAR(40), ADDR3 CHAR(40), ADDR4 CHAR(40), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (RESCODE,CONTACTTYPE) ); CREATE TABLE CMIS.RESRCTEL ( RESCODE CHAR(20) NOT NULL, CONTACTTYPE CHAR(20) NOT NULL, U_VERSION CHAR(1), CONTACTNO CHAR(20), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (RESCODE,CONTACTTYPE) ); CREATE TABLE CMIS.RESSKILL ( RESCODE CHAR(20) NOT NULL, SKILLCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), EFFSTARTDATE DATE, EFFENDDATE DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (RESCODE,SKILLCODE) ); CREATE TABLE CMIS.REVBDLOG ( ACCTCODE CHAR(30) NOT NULL, ACCTYEAR INTEGER NOT NULL, ACCTMTH INTEGER NOT NULL, U_VERSION CHAR(1), AMT DOUBLE PRECISION(15,0), AUTHBY CHAR(30), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (ACCTCODE,ACCTYEAR,ACCTMTH) ); CREATE TABLE CMIS.REVBUDGT ( ACCTCODE CHAR(30) NOT NULL, ACCTYEAR INTEGER NOT NULL, U_VERSION CHAR(1), BUDSOURCE CHAR(3), CURCODE CHAR(12), CURRATE DOUBLE PRECISION(15,0), BUDLOCK CHAR(1), TOTAMT DOUBLE PRECISION(15,0), DEBTOR DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (ACCTCODE,ACCTYEAR) ); CREATE TABLE CMIS.RSTATUS ( RSSTATUSCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), RSSTATUSDES CHAR(100), RSSTATUSNO INTEGER NOT NULL, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (RSSTATUSCODE) ); CREATE TABLE CMIS.SECUSTR ( ROOTCODE CHAR(12) NOT NULL, PARENTCODE CHAR(12) NOT NULL, CHILDCODE CHAR(12) NOT NULL, U_VERSION CHAR(1), CHILDTYPE CHAR(1) NOT NULL, SEQ5 INTEGER, LEVELNO INTEGER NOT NULL, CHILDALIAS CHAR(100), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (ROOTCODE,PARENTCODE,CHILDCODE) ); CREATE TABLE CMIS.SHARETXT ( TEXTCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), TEXTTYPE CHAR(20), TEXTDES CHAR(100), HOSTID CHAR(50) NOT NULL, SOFTCODE CHAR(40) NOT NULL, TEXTPATH CHAR(150) NOT NULL, SHARECODE CHAR(20), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (TEXTCODE) ); CREATE TABLE CMIS.SICACT ( SICCODE CHAR(12) NOT NULL, U_VERSION CHAR(1), SICDES CHAR(30), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (SICCODE) ); CREATE TABLE CMIS.SITEAD ( SITESYS DOUBLE PRECISION(15,0) NOT NULL, CONTACTTYPE CHAR(20) NOT NULL, U_VERSION CHAR(1), ADDR1 CHAR(40), ADDR2 CHAR(40), ADDR3 CHAR(40), ADDR4 CHAR(40), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (SITESYS,CONTACTTYPE) ); CREATE TABLE CMIS.SITELOC ( SITENO CHAR(30) NOT NULL, LOCCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (SITENO,LOCCODE) ); CREATE TABLE CMIS.SITESUPP ( SITESYS DOUBLE PRECISION(15,0) NOT NULL, SUPPSYS DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (SITESYS,SUPPSYS) ); CREATE TABLE CMIS.SITETAB ( SITESYS DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), SITENO CHAR(20) NOT NULL, SITENAME CHAR(50), SITETYPE CHAR(20), RESPONPERSON CHAR(20), LEASECODE CHAR(20), LEASEDATE DATE, LEASEEXPDATE DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (SITESYS) ); CREATE TABLE CMIS.SITETE ( SITESYS DOUBLE PRECISION(15,0) NOT NULL, CONTACTTYPE CHAR(20) NOT NULL, U_VERSION CHAR(1), CONTACTNO CHAR(20), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (SITESYS,CONTACTTYPE) ); CREATE TABLE CMIS.SKILL ( SKILLCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), SKILLDES CHAR(100), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (SKILLCODE) ); CREATE TABLE CMIS.SOFTWARE ( SOFTCODE CHAR(40) NOT NULL, U_VERSION CHAR(1), SOFTDES CHAR(30), HOSTID CHAR(50) NOT NULL, SOFTPATH CHAR(150) NOT NULL, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (SOFTCODE) ); CREATE TABLE CMIS.SRCTAB ( SOURCEID CHAR(12) NOT NULL, U_VERSION CHAR(1), SOURCEDES CHAR(30), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (SOURCEID) ); CREATE TABLE CMIS.STDTAB ( HIDKEY CHAR(1) NOT NULL, U_VERSION CHAR(1), STDVALUE DOUBLE PRECISION(15,0), PRIMARY KEY (HIDKEY) ); CREATE TABLE CMIS.STKADJ ( STKADJID DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), WAREHSECODE CHAR(20), BINCODE CHAR(20), CATITEMSYS DOUBLE PRECISION(15,0), TRANDATE DATE, QTYADJ DOUBLE PRECISION(15,0), AVGCOST DOUBLE PRECISION(15,0), COSTADJ DOUBLE PRECISION(15,0), RESPEMPCODE CHAR(20), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (STKADJID) ); CREATE TABLE CMIS.STKMTD ( STKMTDCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), STKMTDDES CHAR(30), NOMONTH INTEGER, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (STKMTDCODE) ); CREATE TABLE CMIS.SUNGL ( BATCHNO DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), BATCHDATE TIMESTAMP, ACCTYEAR INTEGER, ACCTMTH INTEGER, DEPRCATG CHAR(20), TRANTYPE CHAR(20), USERNAME CHAR(30), FILENAME CHAR(100), BATCHDR DOUBLE PRECISION(15,0), BATCHCR DOUBLE PRECISION(15,0), BATCHTX CHAR(32), BATCHSTATUS INTEGER, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (BATCHNO) ); CREATE TABLE CMIS.SUNGLTX ( BATCHNO DOUBLE PRECISION(15,0) NOT NULL, LNUM INTEGER NOT NULL, U_VERSION CHAR(1), JNLTYPE CHAR(20), ACCTCODE CHAR(30), ACCTYEAR INTEGER, ACCTMTH INTEGER, TRANDATE DATE, DRCR CHAR(1), TRANAMT DOUBLE PRECISION(15,0), DESCRIPTION CHAR(25), COSTCENTRE DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (BATCHNO,LNUM) ); CREATE TABLE CMIS.SYSPARA ( HIDKEY CHAR(1) NOT NULL, LOGPERIOD DOUBLE PRECISION(15,0) NOT NULL, ESCALATION CHAR(1) NOT NULL, SYSNAME CHAR(30), ACN CHAR(20), U_VERSION CHAR(1), PERSONLU CHAR(1), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPARECHAR3 CHAR(20), SPARECHAR4 CHAR(20), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPARENUM3 DOUBLE PRECISION(15,0), SPARENUM4 DOUBLE PRECISION(15,0), SPAREDATE1 DATE, SPAREDATE2 DATE, SPAREDATE3 DATE, SPAREDATE4 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (HIDKEY) ); CREATE TABLE CMIS.TALARM ( TASKCODE CHAR(20) NOT NULL, UOMCODE CHAR(20) NOT NULL, ALARMVALUE DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), PRIORITY INTEGER, POSTFLAG CHAR(1) NOT NULL, LASTDONEDATE DATE NOT NULL, INITREADING INTEGER, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (TASKCODE,UOMCODE,ALARMVALUE) ); CREATE TABLE CMIS.TAXES ( TAXCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), TAXDES CHAR(100), TAXRATE DOUBLE PRECISION(15,0), TAXAMT DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (TAXCODE) ); CREATE TABLE CMIS.TCITEM ( TASKCODE CHAR(20) NOT NULL, FAULTCODE CHAR(20) NOT NULL, INVFLAG CHAR(1) NOT NULL, CATITEMCODE CHAR(30) NOT NULL, U_VERSION CHAR(1), CATITEMDES CHAR(100), QTYREQD DOUBLE PRECISION(15,0), SEQ5 INTEGER, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (TASKCODE,FAULTCODE,INVFLAG,CATITEMCODE) ); CREATE TABLE CMIS.TFAULT ( TASKCODE CHAR(20) NOT NULL, FAULTCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (TASKCODE,FAULTCODE) ); CREATE TABLE CMIS.TFIXER ( TASKCODE CHAR(20) NOT NULL, FAULTCODE CHAR(20) NOT NULL, SKILLCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), ESTHR DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (TASKCODE,FAULTCODE,SKILLCODE) ); CREATE TABLE CMIS.TKCG ( TKCGCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), TKCGDES CHAR(100), SYSACCTCODE CHAR(30), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (TKCGCODE) ); CREATE TABLE CMIS.TMPASSTK ( ASSETNO CHAR(20) NOT NULL, LOCCODE CHAR(20), STKDATE DATE, CONDCODE CHAR(1), PRIMARY KEY (ASSETNO) ); CREATE TABLE CMIS.TMPCLL ( LOGID CHAR(1) NOT NULL, U_VERSION CHAR(1), ACCTYEAR INTEGER, ASSETSTATUS CHAR(20), INSTDATE DATE, UOMCODE CHAR(20), TASKFAULTCODE CHAR(20), WARESHECODE CHAR(20), JOBFAULTCODE CHAR(20), RESCODE CHAR(20), PAYCODE CHAR(20), TRADECODE CHAR(20), PRIMARY KEY (LOGID) ); CREATE TABLE CMIS.TMPJOB ( JOBCODE DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), JOBDES CHAR(100), EXTJOBDES CHAR(200), TASKCODE CHAR(20), INSTPLANTSYS DOUBLE PRECISION(15,0) NOT NULL, ALUOMCODE CHAR(20), ALARMVALUE DOUBLE PRECISION(15,0), MAINTTYPECODE CHAR(20) NOT NULL, SCHDATE DATE, PRIORITY INTEGER, DURUOMCODE CHAR(20), DURATION DOUBLE PRECISION(15,0), ACCTCODE CHAR(30), PLANNER CHAR(30), RESPEMPCODE CHAR(20), LOGDATE DATE, JSTATUSCODE CHAR(20), PREFWAREHSECODE CHAR(20), SYSACCTCODE CHAR(30), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPAREDATE1 DATE, SPAREDATE2 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, TRANNOTE VARBINARY(7479), PRIMARY KEY (JOBCODE) ); CREATE INDEX TMPJOB_IDX1 ON CMIS.TMPJOB (TASKCODE); CREATE INDEX TMPJOB_IDX2 ON CMIS.TMPJOB (INSTPLANTSYS); CREATE TABLE CMIS.OTMPJOB ( JOBCODE DOUBLE PRECISION(15,0) NOT NULL, SEGM CHAR(4) NOT NULL, DATA VARBINARY(8180), PRIMARY KEY (JOBCODE,SEGM) ); CREATE TABLE CMIS.TMPTAB ( FNNAME CHAR(20) NOT NULL, U_VERSION CHAR(1), MODNAME CHAR(20), WAREHSECODE CHAR(20), HORIDATE DATE, BATCHCMD CHAR(200), TXT1 CHAR(40), TXT2 CHAR(40), TXT3 CHAR(40), NUM1 DOUBLE PRECISION(15,0), NUM2 DOUBLE PRECISION(15,0), NUM3 DOUBLE PRECISION(15,0), DATE1 DATE, DATE2 DATE, DATE3 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (FNNAME) ); CREATE TABLE CMIS.TRANTAB ( RECID DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), MODNAME CHAR(20), FUNCTNAME CHAR(20), INTCODE CHAR(20), TRANSCODE CHAR(20), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (RECID) ); CREATE TABLE CMIS.TTEXT ( TASKCODE CHAR(20) NOT NULL, TEXTCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), SEQ5 INTEGER, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (TASKCODE,TEXTCODE) ); CREATE TABLE CMIS.TTKCG ( TASKCODE CHAR(20) NOT NULL, TKCGCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (TASKCODE,TKCGCODE) ); CREATE TABLE CMIS.TXTTYPE ( TEXTTYPE CHAR(20) NOT NULL, U_VERSION CHAR(1), TEXTTYPEDES CHAR(30), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (TEXTTYPE) ); CREATE TABLE CMIS.UOM ( UOMCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), UOMTRAN CHAR(20), UOMDES CHAR(30), FOREMTDCODE INTEGER, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (UOMCODE) ); CREATE TABLE CMIS.USERGRP ( GROUPID CHAR(20) NOT NULL, U_VERSION CHAR(1), GROUPDES CHAR(30), ACCESSLEVEL CHAR(2) NOT NULL, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, COSTLIMIT DOUBLE PRECISION(15,0), JOBCOSTLIMIT DOUBLE PRECISION(15,0), PRIMARY KEY (GROUPID) ); CREATE TABLE CMIS.USERRT ( USERTYPE CHAR(1) NOT NULL, USERID CHAR(12) NOT NULL, ROOTCODE CHAR(12) NOT NULL, U_VERSION CHAR(1), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (USERTYPE,USERID,ROOTCODE) ); CREATE TABLE CMIS.USERSECU ( USERTYPE CHAR(1) NOT NULL, USERID CHAR(12) NOT NULL, ROOTCODE CHAR(12) NOT NULL, PARENTCODE CHAR(12) NOT NULL, CHILDCODE CHAR(12) NOT NULL, U_VERSION CHAR(1), ACCESSLEVEL CHAR(2) NOT NULL, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (USERTYPE,USERID,ROOTCODE,PARENTCODE,CHILDCODE) ); CREATE TABLE CMIS.USERTAB ( USERID CHAR(12) NOT NULL, U_VERSION CHAR(1), USERNAME CHAR(40) NOT NULL, MPPASSWD CHAR(20), GROUPID CHAR(20), STARTDATE DATE, FINISHDATE DATE, STARTTIME TIME, FINISHTIME TIME, DEFAULTWH CHAR(20), COSTLIMIT DOUBLE PRECISION(15,0), JOBCOSTLIMIT DOUBLE PRECISION(15,0), RETRDEF CHAR(1), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (USERID) ); CREATE TABLE CMIS.USERWH ( USERID CHAR(12) NOT NULL, WAREHSECODE CHAR(20) NOT NULL, U_VERSION CHAR(1), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (USERID,WAREHSECODE) ); CREATE TABLE CMIS.WAREHSE ( WAREHSECODE CHAR(20) NOT NULL, U_VERSION CHAR(1), WAREHSEDES CHAR(30), RESPONPERSON CHAR(20), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (WAREHSECODE) ); CREATE TABLE CMIS.WCHARACC ( RECTYPE CHAR(9) NOT NULL, WACCTDAT VARCHAR(8183), PRIMARY KEY (RECTYPE) ); CREATE TABLE CMIS.OWCHARACC ( RECTYPE CHAR(9) NOT NULL, SEGM CHAR(4) NOT NULL, DATA VARCHAR(8179), PRIMARY KEY (RECTYPE,SEGM) ); CREATE TABLE CMIS.WHAD ( WAREHSECODE CHAR(20) NOT NULL, CONTACTTYPE CHAR(20) NOT NULL, U_VERSION CHAR(1), ADDR1 CHAR(40), ADDR2 CHAR(40), ADDR3 CHAR(40), ADDR4 CHAR(40), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (WAREHSECODE,CONTACTTYPE) ); CREATE TABLE CMIS.WHBIN ( WAREHSECODE CHAR(20) NOT NULL, BINCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), CLASSCODE CHAR(20), BINCAPACITY DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (WAREHSECODE,BINCODE) ); CREATE TABLE CMIS.WHTE ( WAREHSECODE CHAR(20) NOT NULL, CONTACTTYPE CHAR(20) NOT NULL, U_VERSION CHAR(1), CONTACTNO CHAR(20), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (WAREHSECODE,CONTACTTYPE) ); CREATE TABLE CMIS.WOLOG ( WORKORDERCODE CHAR(30) NOT NULL, LOGID DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), LOGDATE DATE, WOSTATUSCODE CHAR(20), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (WORKORDERCODE,LOGID) ); CREATE TABLE CMIS.WORKGRP ( WORKGRPCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), WORKGRPDES CHAR(30), SYSACCTCODE CHAR(30), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (WORKGRPCODE) ); CREATE TABLE CMIS.WORKORD ( WORKORDERCODE CHAR(30) NOT NULL, U_VERSION CHAR(1), WORKORDERDES CHAR(100), STARTDATE DATE, FINISHDATE DATE, FUNDPGM CHAR(30), AUTHORBY CHAR(30), RESPBY CHAR(30), SYSACCTCODE CHAR(30), BUDAMT DOUBLE PRECISION(15,0), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPAREDATE1 DATE, SPAREDATE2 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (WORKORDERCODE) ); CREATE TABLE CMIS.WORT ( ROOTCODE CHAR(30) NOT NULL, U_VERSION CHAR(1), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (ROOTCODE) ); CREATE TABLE CMIS.WOSTR ( ROOTCODE CHAR(30) NOT NULL, PARENTCODE CHAR(30) NOT NULL, CHILDCODE CHAR(30) NOT NULL, CHILDTYPE CHAR(1) NOT NULL, U_VERSION CHAR(1), SEQ5 INTEGER, LEVELNO INTEGER NOT NULL, RANKNO DOUBLE PRECISION(15,0), RANKBY CHAR(30), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (ROOTCODE,PARENTCODE,CHILDCODE,CHILDTYPE) ); CREATE TABLE CMIS.WOTEXT ( WORKORDERCODE CHAR(30) NOT NULL, TEXTCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), SEQ5 INTEGER, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (WORKORDERCODE,TEXTCODE) ); CREATE TABLE CMIS.CALEQUIP ( INSTPLANTSYS DOUBLE PRECISION(15,0) NOT NULL, JOBCODE DOUBLE PRECISION(15,0) NOT NULL, SEQ5 CHAR(32) NOT NULL, CALNUM CHAR(15), REFPLANT CHAR(20), CALDUEDATE DATE, NOTE CHAR(200), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), PRIMARY KEY (INSTPLANTSYS,SEQ5,JOBCODE) ); CREATE TABLE CMIS.CALIBTAB ( FROMTABLE CHAR(8) NOT NULL, CODE CHAR(20) NOT NULL, U_VERSION CHAR(1), DES CHAR(50), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (FROMTABLE,CODE) ); CREATE TABLE CMIS.CALLOG ( INSTPLANTSYS DOUBLE PRECISION(15,0) NOT NULL, JOBCODE DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), CUSTODCODE CHAR(20), CALNUM CHAR(15), RECVDATE DATE, RETURNDATE DATE, TOCALDATE DATE, FMCALDATE DATE, REPAIRHOUR DOUBLE PRECISION(15,0), CALHOUR DOUBLE PRECISION(15,0), RECVCODE INTEGER, FAULTCODE CHAR(6), NEWDUEDATE DATE, ALARMVALUE DOUBLE PRECISION(15,0), REQDATE DATE, APPDATE DATE, REPAIRDATE DATE, REPAIRCOST DOUBLE PRECISION(15,0), SURVEYCOST DOUBLE PRECISION(15,0), WSSEQ INTEGER, WSPO CHAR(8), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPAREDATE1 DATE, SPAREDATE2 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (INSTPLANTSYS,JOBCODE) ); CREATE TABLE CMIS.CALLOGEXT ( INSTPLANTSYS DOUBLE PRECISION(15,0) NOT NULL, JOBCODE DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), CALNUM CHAR(15), TEMP CHAR(40), CALPROCUSED CHAR(20), HUMIDITY CHAR(40), TECHNICALNAME CHAR(20), QANAME CHAR(20), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), PRIMARY KEY (INSTPLANTSYS,JOBCODE) ); CREATE TABLE CMIS.CONAGPRC ( CATPLANTCODE CHAR(20) NOT NULL, SUPPSYS DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), STARTDATE DATE, STDPRICE DOUBLE PRECISION(15,0), NATAPRICE DOUBLE PRECISION(15,0), TEXTCODE CHAR(20), FROMDATE DATE, TODATE DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, NOTE VARBINARY(8045), PRIMARY KEY (CATPLANTCODE,SUPPSYS) ); CREATE TABLE CMIS.OCONAGPRC ( CATPLANTCODE CHAR(20) NOT NULL, SUPPSYS DOUBLE PRECISION(15,0) NOT NULL, SEGM CHAR(4) NOT NULL, DATA VARBINARY(8160), PRIMARY KEY (CATPLANTCODE,SUPPSYS,SEGM) ); CREATE TABLE CMIS.CPIMPORT ( CPCODE CHAR(20) NOT NULL, CPDES CHAR(30), CPMODNO CHAR(20), CPSCAT CHAR(20), CPTYPE CHAR(11), CPTRS CHAR(20), CPMREFC CHAR(20), PRIMARY KEY (CPCODE) ); CREATE TABLE CMIS.CUIMPORT ( CUCUSTO CHAR(20) NOT NULL, CUCUSNM CHAR(40), CUGEOGC CHAR(20), CUUDC CHAR(11), CUSECT CHAR(20), CUDIVN CHAR(20), CUCOCODE CHAR(20), CUSERVICE CHAR(20), CUREFNO CHAR(12), PRIMARY KEY (CUCUSTO) ); CREATE TABLE CMIS.CUSTODIAN ( CUSTODCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), CUSTODNAME CHAR(50), SUPPSYS DOUBLE PRECISION(15,0), SERVICE CHAR(20), COMMAND CHAR(20), DIVISION CHAR(20), SECTION CHAR(20), GEOGLOC CHAR(20), UDC INTEGER, NEXTREF INTEGER, UNITCODE CHAR(30), ADDRESS1 CHAR(30), ADDRESS2 CHAR(30), ADDRESS3 CHAR(30), CONTACTNO CHAR(30), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (CUSTODCODE) ); CREATE TABLE CMIS.FSNIMPORT ( MODELNUM CHAR(20) NOT NULL, FSN CHAR(30), PRIMARY KEY (MODELNUM) ); CREATE TABLE CMIS.GENDPNO ( HIDKEY CHAR(1) NOT NULL, U_VERSION CHAR(1), DISPATCHNO DOUBLE PRECISION(15,0), DISPATCHYR INTEGER, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (HIDKEY) ); CREATE TABLE CMIS.GENLOAN ( HIDKEY CHAR(1) NOT NULL, U_VERSION CHAR(1), LOANSYS DOUBLE PRECISION(15,0), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (HIDKEY) ); CREATE TABLE CMIS.GENPCAL ( HIDKEY CHAR(1) NOT NULL, U_VERSION CHAR(1), GENPALID DOUBLE PRECISION(15,0) NOT NULL, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (HIDKEY) ); CREATE TABLE CMIS.IPIMPORT ( IPDEVICENO CHAR(20) NOT NULL, IPBARCODE CHAR(20), IPNOMEN CHAR(30), IPREFNO CHAR(20), IPSERNUM CHAR(20), IPCFLAG CHAR(11), IPCUSTO CHAR(20), IPCULOC CHAR(20), IPCALNXD CHAR(11), PRIMARY KEY (IPDEVICENO) ); CREATE TABLE CMIS.JOBATTR ( JOBCODE DOUBLE PRECISION(15,0) NOT NULL, ATTRNO CHAR(32) NOT NULL, U_VERSION CHAR(1), ATTRCODE CHAR(40), ATTRVALUE CHAR(20), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (JOBCODE,ATTRNO) ); CREATE TABLE CMIS.LNLOGDT ( LOANSYS CHAR(32) NOT NULL, U_VERSION CHAR(1), DISPATCHNO CHAR(20), INSTPLANTSYS DOUBLE PRECISION(15,0), CUSTODOWNER CHAR(20), LOANER CHAR(20), COMMENT CHAR(50), RETURNDATE DATE, JOBCODE DOUBLE PRECISION(15,0), STATUSFLAG CHAR(1), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (LOANSYS) ); CREATE INDEX LNLOGDT_DX2 ON CMIS.LNLOGDT (DISPATCHNO); CREATE INDEX LNLOGDT_DX3 ON CMIS.LNLOGDT (INSTPLANTSYS); CREATE INDEX LNLOGDT_IDX2 ON CMIS.LNLOGDT (DISPATCHNO); CREATE TABLE CMIS.LNLOGHD ( DISPATCHNO CHAR(20) NOT NULL, U_VERSION CHAR(1), CUSTODLOC CHAR(20), SERVNUM CHAR(20) NOT NULL, SERVICE CHAR(20) NOT NULL, TRANTYPE CHAR(20) NOT NULL, PRINTFLAG CHAR(1), RETURNDATE DATE, MOVEDATE DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, NOTE VARBINARY(8016), PRIMARY KEY (DISPATCHNO) ); CREATE TABLE CMIS.OLNLOGHD ( DISPATCHNO CHAR(20) NOT NULL, SEGM CHAR(4) NOT NULL, DATA VARBINARY(8168), PRIMARY KEY (DISPATCHNO,SEGM) ); CREATE TABLE CMIS.MCIMPORT ( MODELNUM CHAR(20) NOT NULL, SUPPLIER CHAR(20) NOT NULL, STDCOST CHAR(22), NATACOST CHAR(22), PRIMARY KEY (MODELNUM,SUPPLIER) ); CREATE TABLE CMIS.MDIMPORT ( MODELNUM CHAR(20) NOT NULL, MODELDOC CHAR(20), PRIMARY KEY (MODELNUM) ); CREATE TABLE CMIS.MEAATTR ( PLANTTYPECODE CHAR(20) NOT NULL, ATTRNO CHAR(32) NOT NULL, U_VERSION CHAR(1), ATTRCODE CHAR(40), DATATYPE CHAR(1), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (PLANTTYPECODE,ATTRNO) ); CREATE TABLE CMIS.MFIMPORT ( MREFC CHAR(20) NOT NULL, MFRNAM CHAR(40), FSCM CHAR(20), PRIMARY KEY (MREFC) ); CREATE TABLE CMIS.MTIMPORT ( MTMODNUM CHAR(20) NOT NULL, MTCIPR CHAR(11), MTEXPRI CHAR(22), PRIMARY KEY (MTMODNUM) ); CREATE TABLE CMIS.PLANTCAL ( INSTPLANTSYS DOUBLE PRECISION(15,0) NOT NULL, LOGID DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), RETURNDATE DATE, REQDDATE DATE, SUPPSYS DOUBLE PRECISION(15,0), AGREEPRICE DOUBLE PRECISION(15,0), STATUSFLAG CHAR(1), JOBCODE DOUBLE PRECISION(15,0), JSTATUSCODE CHAR(20), CUSTODLOC CHAR(20), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (INSTPLANTSYS,LOGID) ); CREATE TABLE CMIS.REPORT ( CUSTODCODE CHAR(20) NOT NULL, SECTION CHAR(20), JANUARY DOUBLE PRECISION(15,0), FEBRUARY DOUBLE PRECISION(15,0), MARCH DOUBLE PRECISION(15,0), APRIL DOUBLE PRECISION(15,0), MAY DOUBLE PRECISION(15,0), JUNE DOUBLE PRECISION(15,0), JULY DOUBLE PRECISION(15,0), AUGUST DOUBLE PRECISION(15,0), SEPTEMBER DOUBLE PRECISION(15,0), OCTOBER DOUBLE PRECISION(15,0), NOVEMBER DOUBLE PRECISION(15,0), DECEMBER DOUBLE PRECISION(15,0), PRIMARY KEY (CUSTODCODE) ); CREATE TABLE CMIS.SERVPER ( SERVNUM CHAR(20) NOT NULL, SERVICE CHAR(20) NOT NULL, U_VERSION CHAR(1), FIRSTNAME CHAR(30), SURNAME CHAR(30), RANK CHAR(20), CONTACTNO CHAR(20), CUSTODCODE CHAR(20), PERTYPE CHAR(20), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPAREDATE1 DATE, SPAREDATE2 DATE, PRIMARY KEY (SERVNUM,SERVICE) ); CREATE TABLE CMIS.SYSTOCAT ( DEVICENO CHAR(11) NOT NULL, MODNUM CHAR(20), PRIMARY KEY (DEVICENO) ); CREATE TABLE CMIS.LNLGHD ( DISPATCHNO CHAR(20) NOT NULL, U_VERSION CHAR(1), CUSTODLOC CHAR(20), SERVNUM CHAR(20) NOT NULL, SERVICE CHAR(20) NOT NULL, TRANTYPE CHAR(20) NOT NULL, PRINTFLAG CHAR(1), RETURNDATE DATE, MOVEDATE DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, NOTE VARBINARY(8016), PRIMARY KEY (DISPATCHNO) ); CREATE TABLE CMIS.OLNLGHD ( DISPATCHNO CHAR(20) NOT NULL, SEGM CHAR(4) NOT NULL, DATA VARBINARY(8168), PRIMARY KEY (DISPATCHNO,SEGM) ); CREATE TABLE CMIS.JOB ( JOBCODE DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), JOBDES CHAR(100), EXTJOBDES CHAR(200), TASKCODE CHAR(20), INSTPLANTSYS DOUBLE PRECISION(15,0) NOT NULL, ALUOMCODE CHAR(20), ALARMVALUE DOUBLE PRECISION(15,0), MAINTTYPECODE CHAR(20) NOT NULL, SCHDATE DATE, PRIORITY INTEGER, DURUOMCODE CHAR(20), DURATION DOUBLE PRECISION(15,0), ACCTCODE CHAR(30), PLANNER CHAR(30), RESPEMPCODE CHAR(20), LOGDATE DATE, JSTATUSCODE CHAR(20), PREFWAREHSECODE CHAR(20), SYSACCTCODE CHAR(30), TXTJOBCODE CHAR(30), EXTJOBCODE CHAR(30), ESTTOTCOST DOUBLE PRECISION(15,0), ESTLABCOST DOUBLE PRECISION(15,0), ESTMATCOST DOUBLE PRECISION(15,0), ESTOTHCOST DOUBLE PRECISION(15,0), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPAREDATE1 DATE, SPAREDATE2 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (JOBCODE) ); CREATE INDEX JOB_IDX1 ON CMIS.JOB (TASKCODE); CREATE INDEX JOB_IDX2 ON CMIS.JOB (INSTPLANTSYS); CREATE TABLE CMIS.JLOG ( JOBCODE DOUBLE PRECISION(15,0) NOT NULL, LOGID DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), LOGDATE DATE NOT NULL, LOGTIME TIME, JSTATUSCODE CHAR(20) NOT NULL, REMARK60 CHAR(60), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (JOBCODE,LOGID) ); CREATE TABLE CMIS.PLANT ( INSTPLANTSYS DOUBLE PRECISION(15,0) NOT NULL, SITESYS DOUBLE PRECISION(15,0), U_VERSION CHAR(1), INSTPLANTCODE CHAR(20) NOT NULL, INSTPLANTDES CHAR(100), ROTABLE CHAR(1), CATPLANTCODE CHAR(20), SUPPSYS DOUBLE PRECISION(15,0), APPRBODYCODE CHAR(20), APPREXPDATE DATE, APPRNO CHAR(15), SERIALNO CHAR(30), ORDERNO CHAR(15), PURCHCOST DOUBLE PRECISION(15,0), INSTCOST DOUBLE PRECISION(15,0), INSTDATE DATE NOT NULL, WARDATE DATE, TARGETMTBF DOUBLE PRECISION(15,0), CURLOCCODE CHAR(20), CURLOCDATE DATE, CURDEPTSYS DOUBLE PRECISION(15,0), CURMOVEDATE DATE, SYSACCTCODE CHAR(30), INSTPLANTALIAS CHAR(30), REPLACECOST DOUBLE PRECISION(15,0), POSTLOCCODE CHAR(20), PLANTSTATUS CHAR(20), MKTCOST DOUBLE PRECISION(15,0), TOTALCOST DOUBLE PRECISION(15,0), EXPRETIREDATE DATE, WARUOMCODE CHAR(20), WARVALUE DOUBLE PRECISION(15,0), SERUOMCODE CHAR(20), SERVALUE DOUBLE PRECISION(15,0), CALDUEDATE DATE, BILLID CHAR(32), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPAREDATE1 DATE, SPAREDATE2 DATE, MODELNO CHAR(30), SPARECHAR3 CHAR(20), SPARECHAR4 CHAR(20), SPARENUM3 CHAR(32), SPARENUM4 CHAR(32), SPAREDATE3 DATE, SPAREDATE4 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (INSTPLANTSYS)); CREATE UNIQUE INDEX PLANT_IDX1 ON CMIS.PLANT (INSTPLANTCODE); CREATE INDEX PLANT_DX5 ON CMIS.PLANT (SPARECHAR1); CREATE INDEX PLANT_IDX2 ON CMIS.PLANT (CATPLANTCODE); CREATE INDEX PLANT_IDX3 ON CMIS.PLANT (SERIALNO); CREATE TABLE CMIS.TASK ( TASKCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), TASKDES CHAR(100), INSTPLANTSYS DOUBLE PRECISION(15,0) NOT NULL, WORKORDERCODE CHAR(30), TOLERANCE INTEGER, DURATION DOUBLE PRECISION(15,0), UOMCODE CHAR(20), ACCTCODE CHAR(30), TASKSTATUSCODE CHAR(20), SYSACCTCODE CHAR(30), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPAREDATE1 DATE, SPAREDATE2 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (TASKCODE) ); CREATE INDEX TASK_IDX1 ON CMIS.TASK (INSTPLANTSYS); CREATE TABLE CMIS.EQUIPMENT ( INSTPLANTSYS DOUBLE PRECISION(15,0) NOT NULL, JOBCODE DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), SEQ5 INTEGER, INSTPLANTCODE CHAR(20), INSTPLANTDES CHAR(100), MODELNO CHAR(30), CALNUM CHAR(15), REFPLANTSYS DOUBLE PRECISION(15,0), CALDUEDATE DATE, PRIMARY KEY (INSTPLANTSYS,JOBCODE) ); CREATE TABLE CMIS.ASSETLOG ( INSTPLANTCODE CHAR(20) NOT NULL, INSTPLANTSYS DOUBLE PRECISION(15,0) NOT NULL, SEQ5 INTEGER NOT NULL, U_VERSION CHAR(40), DATECREATE DATE, CREATEDBY CHAR(40), SHORTNOTE CHAR(200), NOTE VARBINARY(7872), PRIMARY KEY (INSTPLANTSYS,SEQ5) ); CREATE TABLE CMIS.TMPREGISTER ( TYPE CHAR(10) NOT NULL, TMC CHAR(40) NOT NULL, CATPLANTCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPAREDATE1 DATE, SPAREDATE2 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (TYPE,TMC,CATPLANTCODE) ); CREATE TABLE CMIS.TMP ( TYPE CHAR(10) NOT NULL, TMC CHAR(40) NOT NULL, U_VERSION CHAR(1), TMP.INTERVAL INTEGER, PERIODTYPE CHAR(20), FULLTMC CHAR(40), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPAREDATE1 DATE, SPAREDATE2 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (TYPE,TMC) ); CREATE TABLE CMIS.IPTMPREGISTER ( INSTPLANTSYS CHAR(12) NOT NULL, TYPE CHAR(10) NOT NULL, TMC CHAR(40) NOT NULL, U_VERSION CHAR(1), FULLTMP CHAR(40), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPAREDATE1 DATE, SPAREDATE2 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (INSTPLANTSYS,TYPE,TMC) ); CREATE TABLE CMIS.EE400 ( EE400SYS DOUBLE PRECISION(15,0) NOT NULL, CATPLANTCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), ORIGFROM CHAR(40), ORIGSERIALNO CHAR(30), FILEREF CHAR(40), SENDTO CHAR(40), AAP CHAR(40), TITLE CHAR(100), ISSUEDATE DATE, ALNO INTEGER, SPONSORNO CHAR(20), TMPSYSNO CHAR(20), ENGNAME CHAR(40), REASON VARCHAR(500), DRAFTNAME CHAR(60), DRAFTRANK CHAR(10), DRAFTPHONE CHAR(20), DRAFTDATE DATE, DELNAME CHAR(60), DELRANK CHAR(10), DELPHONE CHAR(20), DELDATE DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (EE400SYS,CATPLANTCODE) ); CREATE TABLE CMIS.EE400EXT ( EE400SYS DOUBLE PRECISION(15,0) NOT NULL, CATPLANTCODE CHAR(20) NOT NULL, FIELDNAME CHAR(40) NOT NULL, U_VERSION CHAR(1), AMENDMENT CHAR(100), PRIMARY KEY (EE400SYS,CATPLANTCODE,FIELDNAME) ); CREATE TABLE CMIS.STIREGISTER ( STIBATCHREF DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), STIREFNO CHAR(30), STIDESCRIPTION CHAR(100), STIDATE DATE, STIFLAG CHAR(1), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPARENUM1 CHAR(32), SPARENUM2 CHAR(32), SPAREDATE1 DATE, SPAREDATE2 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (STIBATCHREF) ); CREATE TABLE CMIS.STIBATCH ( INSTPLANTSYS DOUBLE PRECISION(15,0) NOT NULL, STIBATCHREF DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), INSTPLANTCODE CHAR(40), SERIALNO CHAR(40), CATPLANTCODE CHAR(40), STIREFNO CHAR(30), STIDESCRIPTION CHAR(100), STATUSFLAG CHAR(1), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPARENUM1 CHAR(32), SPARENUM2 CHAR(32), SPAREDATE1 DATE, SPAREDATE2 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (INSTPLANTSYS,STIBATCHREF) ); CREATE TABLE CMIS.NSN ( NSNNO CHAR(40) NOT NULL, U_VERSION CHAR(1), SPONSOR CHAR(100), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPAREDATE1 DATE, SPAREDATE2 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (NSNNO) ); CREATE TABLE CMIS.CPNSNREGISTER ( NSNNO CHAR(40) NOT NULL, CATPLANTCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPAREDATE1 DATE, SPAREDATE2 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (NSNNO,CATPLANTCODE) ); CREATE TABLE CMIS.IPNSNREGISTER ( INSTPLANTSYS DOUBLE PRECISION(15,0) NOT NULL, NSNNO CHAR(40) NOT NULL, U_VERSION CHAR(1), SPONSOR CHAR(100), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPAREDATE1 DATE, SPAREDATE2 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (INSTPLANTSYS,NSNNO) ); CREATE TABLE CMIS.CALPROC ( AAPREF CHAR(60) NOT NULL, CALPROCREF CHAR(30) NOT NULL, U_VERSION CHAR(1), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPAREDATE1 DATE, SPAREDATE2 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (AAPREF,CALPROCREF) ); CREATE TABLE CMIS.CPCALPROCREGISTER ( AAPREF CHAR(60) NOT NULL, CALPROCREF CHAR(30) NOT NULL, CATPLANTCODE CHAR(20) NOT NULL, U_VERSION CHAR(1), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPAREDATE1 DATE, SPAREDATE2 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (AAPREF,CALPROCREF,CATPLANTCODE) ); CREATE TABLE CMIS.IPCALPROCREGISTER ( AAPREF CHAR(60) NOT NULL, CALPROCREF CHAR(30) NOT NULL, INSTPLANTSYS DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), SPARECHAR1 CHAR(20), SPARECHAR2 CHAR(20), SPARENUM1 DOUBLE PRECISION(15,0), SPARENUM2 DOUBLE PRECISION(15,0), SPAREDATE1 DATE, SPAREDATE2 DATE, USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (AAPREF,CALPROCREF,INSTPLANTSYS) ); CREATE TABLE CMIS.TASKALL ( SERVNUM CHAR(20) NOT NULL, SERVICE CHAR(20) NOT NULL, SEQ5 DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), TASKDATE DATE, DUEDATE DATE, URGENCY CHAR(20), SUBJECT CHAR(200), WHOSERVNUM CHAR(20), WHOSERVICE CHAR(20), WHOFIRSTNAME CHAR(30), WHOSURNAME CHAR(30), TASKSTATUS CHAR(20), NOTE VARBINARY(7787), PRIMARY KEY (SERVNUM,SERVICE,SEQ5) ); CREATE TABLE CMIS.EMAIL ( SERVNUM CHAR(20) NOT NULL, SERVICE CHAR(20) NOT NULL, SEQ5 DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), MAILDATE DATE, SUBJECT CHAR(200), WHOSERVNUM CHAR(20), WHOSERVICE CHAR(20), WHOFIRSTNAME CHAR(30), WHOSURNAME CHAR(30), READSTATUS CHAR(1), MESSAGE VARBINARY(7834), PRIMARY KEY (SERVNUM,SERVICE,SEQ5) ); CREATE TABLE CMIS.TASKBAR ( INSTPLANTSYS DOUBLE PRECISION(15,0) NOT NULL, JOBCODE CHAR(12) NOT NULL, U_VERSION CHAR(1), TASKCREATEDATE DATE, TASKDUEDATE DATE, URGENCY CHAR(20), TASKSTATUS CHAR(20), CRSERVNUM CHAR(20), CRSERVICE CHAR(20), ALLSERVNUM CHAR(20), ALLSERVICE CHAR(20), ALLSTATUS CHAR(1), PRIMARY KEY (INSTPLANTSYS,JOBCODE) ); CREATE TABLE CMIS.OEMAIL ( SERVNUM CHAR(20) NOT NULL, SERVICE CHAR(20) NOT NULL, SEQ5 DOUBLE PRECISION(15,0) NOT NULL, SEGM CHAR(4) NOT NULL, DATA VARBINARY(8140), PRIMARY KEY (SERVNUM,SERVICE,SEQ5,SEGM) ); CREATE TABLE CMIS.DISCUSSHD ( SEQ5 DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), FIRSTNAME CHAR(30), LASTNAME CHAR(30), TOPIC VARCHAR(300), USERCREATE CHAR(12), DATECREATE TIMESTAMP, USERCHANGE CHAR(12), DATECHANGE TIMESTAMP, PRIMARY KEY (SEQ5) ); CREATE TABLE CMIS.DISCUSSFT ( SEQ5 DOUBLE PRECISION(15,0) NOT NULL, FOOTSEQ5 DOUBLE PRECISION(15,0) NOT NULL, U_VERSION CHAR(1), FIRSTNAME CHAR(20), LASTNAME CHAR(40), READSTATUS CHAR(1), NOTE VARBINARY(8114), PRIMARY KEY (SEQ5,FOOTSEQ5) ); -- *** VIEWS *** CREATE VIEW JMAXLOGVW (JOBCODE,MAXLOGID) AS SELECT JLOG.JOBCODE, MAX(LOGID) FROM JLOG,JSTATUS WHERE JSTATUS.JSTATUSCODE = JLOG.JSTATUSCODE AND JSTATUS.JSTATUSNO > 1 GROUP BY JLOG.JOBCODE; CREATE VIEW ASPLANTVW AS SELECT ASSET.ASSETSYS, ASPLANT.INSTPLANTSYS, ASSET.ASSETNO, ASSET.DEPRFLAG, ASSET.ASSETSTATUS, PLANT.INSTPLANTCODE FROM ASSET,ASPLANT,PLANT WHERE ASSET.ASSETSYS = ASPLANT.ASSETSYS AND ASPLANT.INSTPLANTSYS = PLANT.INSTPLANTSYS; CREATE VIEW CPCITEMVW (CATPLANTCODE,INVFLAG,CATITEMCODE,U_VERSION,CATITEMDES,QTYREQD, SEQ5,USERCREATE,DATECREATE,USERCHANGE,DATECHANGE,INVCATITEMCODE) AS SELECT CATPLANTCODE, INVFLAG, CPCITEM.CATITEMCODE, CPCITEM.U_VERSION, CPCITEM.CATITEMDES, QTYREQD, SEQ5, CPCITEM.USERCREATE, CPCITEM.DATECREATE, CPCITEM.USERCHANGE, CPCITEM.DATECHANGE, CATITEM.CATITEMCODE FROM CPCITEM,CATITEM WHERE CPCITEM.CATITEMCODE = CATITEM.TXTCATITEMSYS AND CPCITEM.INVFLAG = 'Y'; CREATE VIEW CPMANUFVW (CATPLANTCODE,MANUFSYS,MANUFPRIORITY,MANUFCODE) AS SELECT CPMANUF.CATPLANTCODE, CPMANUF.MANUFSYS, CPMANUF.PRIORITY, RESGRP.RESGRPCODE FROM CPMANUF,RESGRP WHERE CPMANUF.MANUFSYS = RESGRP.RESGRPSYS; CREATE VIEW CPSUPPVW (CATPLANTCODE,SUPPSYS,SUPPPRIORITY,LEADTIME,SUPPCODE) AS SELECT CPSUPP.CATPLANTCODE, CPSUPP.SUPPSYS, CPSUPP.PRIORITY, CPSUPP.LEADTIME, RESGRP.RESGRPCODE FROM CPSUPP,RESGRP WHERE CPSUPP.SUPPSYS = RESGRP.RESGRPSYS; CREATE VIEW IPATTRVW AS SELECT IPATTR.INSTPLANTSYS, IPATTR.PLANTTYPE, IPATTR.ATTRCODE, IPATTR.ATTRVALUE, IPATTR.SEQ5, IPATTR.REMARK, PLANT.INSTPLANTCODE FROM IPATTR,PLANT WHERE IPATTR.INSTPLANTSYS = PLANT.INSTPLANTSYS; CREATE VIEW IPCATGVW AS SELECT IPCATG.INSTPLANTSYS, IPCATG.CATGCODE, PLANT.INSTPLANTCODE FROM IPCATG,PLANT WHERE IPCATG.INSTPLANTSYS = PLANT.INSTPLANTSYS; CREATE VIEW IPFAULTVW AS SELECT IPFAULT.INSTPLANTSYS, IPFAULT.FAULTCODE, PLANT.INSTPLANTCODE FROM IPFAULT,PLANT WHERE IPFAULT.INSTPLANTSYS = PLANT.INSTPLANTSYS; CREATE VIEW IPOPRVW AS SELECT IPOPR.INSTPLANTSYS, IPOPR.OPRDATE, IPOPR.OPRTIME, IPOPR.TOOPRTIME, IPOPR.PLANTSTATUSCODE, IPOPR.JOBCODE, IPOPR.REMARK, PLANT.INSTPLANTCODE FROM IPOPR,PLANT WHERE IPOPR.INSTPLANTSYS = PLANT.INSTPLANTSYS; CREATE VIEW JCITEMVW AS SELECT JCITEM.JOBCODE, JCITEM.FAULTCODE, JCITEM.INVFLAG, JCITEM.CATITEMCODE, JCITEM.U_VERSION, JCITEM.CATITEMDES, JCITEM.WAREHSECODE, JCITEM.QTYREQD, JCITEM.QTYISSUE, JCITEM.QTYRETURN, JCITEM.QTYUSED, JCITEM.SEQ5, JCITEM.USERCREATE, JCITEM.DATECREATE, JCITEM.USERCHANGE, JCITEM.DATECHANGE, CATITEM.CATITEMCODE AS INVCATITEMCODE FROM JCITEM,CATITEM WHERE JCITEM.CATITEMCODE = CATITEM.TXTCATITEMSYS AND JCITEM.INVFLAG = 'Y'; CREATE VIEW TCITEMVW AS SELECT TASKCODE, FAULTCODE, INVFLAG, TCITEM.CATITEMCODE, TCITEM.U_VERSION, TCITEM.CATITEMDES, QTYREQD, SEQ5, TCITEM.USERCREATE, TCITEM.DATECREATE, TCITEM.USERCHANGE, TCITEM.DATECHANGE, CATITEM.CATITEMCODE AS INVCATITEMCODE FROM TCITEM,CATITEM WHERE TCITEM.CATITEMCODE = CATITEM.TXTCATITEMSYS AND TCITEM.INVFLAG = 'Y'; CREATE VIEW RESSKILLVW AS SELECT RESSKILL.RESCODE, SKILLCODE, RESSKILL.U_VERSION, EFFSTARTDATE, EFFENDDATE, RESSKILL.USERCREATE, RESSKILL.DATECREATE, RESSKILL.USERCHANGE, RESSKILL.DATECHANGE, RESRC.RESTYPE, RESRC.RESGRPSYS FROM RESSKILL,RESRC WHERE RESSKILL.RESCODE = RESRC.RESCODE; CREATE VIEW SITESUPPVW AS SELECT SITESUPP.SITESYS, SITESUPP.SUPPSYS, RESGRP.RESGRPCODE, RESGRP.RESGRPDES, RESSKILLVW.SKILLCODE FROM SITESUPP,JOBCRT,RESGRP,RESSKILLVW WHERE SITESUPP.SUPPSYS = RESGRP.RESGRPSYS AND SITESUPP.SUPPSYS = JOBCRT.SUPPSYS AND JOBCRT.DATECRT >= CURDATE() AND SITESUPP.SUPPSYS = RESSKILLVW.RESGRPSYS; CREATE VIEW PLANTRTVW AS SELECT ROOTCODE, INSTPLANTCODE AS INSTPLANTROOT, INSTPLANTDES AS ROOTDES FROM PLANTRT,PLANT WHERE PLANTRT.ROOTCODE = PLANT.INSTPLANTSYS; CREATE VIEW DEPTRTVW AS SELECT ROOTCODE, DEPTCODE DEPTCODEROOT, DEPTDES ROOTDES FROM DEPTRT,DEPT WHERE DEPTRT.ROOTCODE = DEPT.DEPTSYS; CREATE VIEW WORTVW AS SELECT ROOTCODE, WORKORDERDES ROOTDES FROM WORT,WORKORD WHERE WORT.ROOTCODE = WORKORD.WORKORDERCODE; CREATE VIEW JBDEPVW AS SELECT DEPT.DEPTCODE, PLANT.INSTPLANTSYS, JOB.TASKCODE, JOB.JOBCODE, DEPT.DEPTDES, JOB.JOBDES FROM DEPT,PLANT,JOB WHERE DEPT.DEPTSYS = PLANT.CURDEPTSYS AND PLANT.INSTPLANTSYS = JOB.INSTPLANTSYS; CREATE VIEW LBTRANVW AS SELECT DISTINCT JOBCODE,RESCODE FROM LABTRAN; CREATE VIEW MATRANVW AS SELECT DISTINCT JOBCODE,RESCODE FROM MATLTRAN; CREATE VIEW IVTRANVW AS SELECT DISTINCT JOBCODE,RESCODE FROM INVTRAN; CREATE VIEW INVJOBVW AS SELECT INVTRAN.INVTRANID, INVTRAN.INVNO, INVTRAN.JOBCODE, INVTRAN.RESCODE, INVTRAN.RESTYPE, INVTRAN.WORKGRPCODE, INVTRAN.RESGRPSYS, INVTRAN.INVDATE, INVTRAND.INVCOST FROM INVTRAN,INVTRAND WHERE INVTRAN.INVTRANID = INVTRAND.INVTRANID; CREATE VIEW FORCASTVW AS SELECT FORECAST.TASKCODE, FORECAST.UOMCODE, FORECAST.SCHDATE, FORECAST.FORECASTSTATUS, FORECAST.JOBCODE, TFIXER.SKILLCODE, TFIXER.ESTHR, TASK.DURATION, TASK.UOMCODE DURUOMCODE, PLANT.INSTPLANTCODE, DEPT.DEPTCODE, DEPT.DEPTDES FROM FORECAST,TFIXER,TASK,PLANT LEFT OUTER JOIN DEPT ON DEPT.DEPTSYS = PLANT.CURDEPTSYS WHERE FORECAST.TASKCODE = TFIXER.TASKCODE AND FORECAST.FORECASTSTATUS IS NULL AND TFIXER.TASKCODE = TASK.TASKCODE AND PLANT.INSTPLANTSYS = TASK.INSTPLANTSYS; CREATE VIEW JFIXERVW AS SELECT JFIXER.JOBCODE, FAULTCODE, SKILLCODE, JFIXER.U_VERSION, ESTHR, ACTHR, JFIXER.USERCREATE, JFIXER.DATECREATE, JFIXER.USERCHANGE, JFIXER.DATECHANGE, JOB.SCHDATE, JOB.JSTATUSCODE, JOB.LOGDATE, JSTATUS.JSTATUSNO, JOB.DURATION, JOB.DURUOMCODE, PLANT.INSTPLANTCODE, DEPT.DEPTCODE, DEPT.DEPTDES FROM JFIXER,JOB,JSTATUS,PLANT LEFT OUTER JOIN DEPT ON DEPT.DEPTSYS = PLANT.CURDEPTSYS WHERE JFIXER.JOBCODE = JOB.JOBCODE AND JOB.JSTATUSCODE = JSTATUS.JSTATUSCODE AND JSTATUS.JSTATUSNO IN (1,2) AND PLANT.INSTPLANTSYS = JOB.INSTPLANTSYS; CREATE VIEW INVTRANVW AS SELECT INVTRAN.INVTRANID, INVTRAN.JOBCODE, INVTRAN.RESCODE, INVTRAN.RESTYPE, INVTRAN.RESGRPSYS, INVTRAN.WORKGRPCODE, INVTRAN.INVDATE, INVTRAN.INVNO, INVTRAND.FAULTCODE, INVTRAND.TRANDATE, INVTRAND.ACCTCAT, INVTRAND.INVCOST FROM INVTRAN,INVTRAND WHERE INVTRAN.INVTRANID = INVTRAND.INVTRANID; CREATE VIEW PLANTLCRTVW AS SELECT ROOTCODE, INSTPLANTCODE INSTPLANTROOT, INSTPLANTDES ROOTDES , CURLOCCODE FROM PLANTRT,PLANT WHERE PLANTRT.ROOTCODE = PLANT.INSTPLANTSYS; CREATE VIEW PLANTNOASSETVW AS SELECT INSTPLANTSYS, INSTPLANTCODE, INSTPLANTDES FROM PLANT WHERE PLANT.INSTPLANTSYS NOT IN (SELECT INSTPLANTSYS FROM ASPLANT); CREATE VIEW PLANTNOTINSTRVW AS SELECT PLANT.INSTPLANTSYS, PLANT.INSTPLANTCODE, PLANT.INSTPLANTDES FROM PLANT WHERE PLANT.INSTPLANTSYS NOT IN (SELECT CHILDCODE AS INSTPLANTSYS FROM PLANTSTR UNION SELECT ROOTCODE AS INSTPLANTSYS FROM PLANTRT); CREATE VIEW DEPTNOTINSTRVW AS SELECT DEPT.DEPTSYS, DEPT.DEPTCODE, DEPT.DEPTDES FROM DEPT WHERE DEPT.DEPTSYS NOT IN (SELECT CHILDCODE AS DEPTSYS FROM DEPTSTR UNION SELECT ROOTCODE AS DEPTSYS FROM DEPTRT); CREATE VIEW WONOTINSTRVW AS SELECT WORKORD.WORKORDERCODE, WORKORD.WORKORDERDES FROM WORKORD WHERE WORKORD.WORKORDERCODE NOT IN (SELECT CHILDCODE AS WORKORDERCODE FROM WOSTR WHERE CHILDTYPE = 'W' UNION SELECT ROOTCODE AS WORKORDERCODE FROM WORT); CREATE VIEW TASKNOTINSTRVW AS SELECT TASK.TASKCODE, TASK.TASKDES FROM TASK WHERE TASK.TASKCODE NOT IN (SELECT CHILDCODE AS TASKCODE FROM WOSTR WHERE CHILDTYPE = 'T'); CREATE VIEW JOBNOTINSTRVW AS SELECT JOB.JOBCODE, JOB.JOBDES FROM JOB WHERE JOB.TXTJOBCODE NOT IN (SELECT CHILDCODE AS JOBCODE FROM WOSTR WHERE CHILDTYPE = 'J'); CREATE VIEW CPSUVW (CATPLANTCODE,RESGRPSYS,SUPPCODE,CATPLANTDES, SUPPDES) AS SELECT CATPLANT.CATPLANTCODE, RESGRP.RESGRPSYS, RESGRP.RESGRPCODE, CATPLANT.CATPLANTDES, RESGRP.RESGRPDES FROM CATPLANT,CPSUPP,RESGRP WHERE CPSUPP.CATPLANTCODE = CATPLANT.CATPLANTCODE AND RESGRP.RESGRPSYS = CPSUPP.SUPPSYS; CREATE VIEW CPFDVW AS SELECT CATPLANT.CATPLANTCODE, FAULT.FAULTCODE, CPFAULT.CRITCODE, CATPLANT.CATPLANTDES, FAULT.FAULTDES FROM CATPLANT,CPFAULT,FAULT WHERE CPFAULT.CATPLANTCODE = CATPLANT.CATPLANTCODE AND FAULT.FAULTCODE = CPFAULT.FAULTCODE; CREATE VIEW CTIVVW AS SELECT CTINV.TRANID, CTINV.CONTRACTSYS, CTINV.CONDID, CTRACT.CONTRACTNO, CTRACT.CONTRACTDES, CTINV.INVDATE, CTINV.INVAMT FROM CTINV,CTRACT WHERE CTRACT.CONTRACTSYS = CTINV.CONTRACTSYS; CREATE VIEW DEPTSTRVW AS SELECT DEPTRT.ROOTCODE, DEPTSTR.PARENTCODE, DEPTSTR.CHILDCODE, DEPTSTR.SEQ5, DEPTSTR.LEVELNO, ROOTDEPT.DEPTCODE AS DEPTCODEROOT, ROOTDEPT.DEPTDES AS ROOTDES, PARENTDEPT.DEPTCODE AS DEPTCODEPARENT, PARENTDEPT.DEPTDES AS PARENTDES, CHILDDEPT.DEPTCODE AS DEPTCODECHILD, CHILDDEPT.DEPTDES AS CHILDDES FROM DEPTRT,DEPTSTR,DEPT ROOTDEPT,DEPT PARENTDEPT,DEPT CHILDDEPT WHERE DEPTSTR.ROOTCODE = DEPTRT.ROOTCODE AND DEPTRT.ROOTCODE = ROOTDEPT.DEPTSYS AND PARENTDEPT.DEPTSYS = DEPTSTR.PARENTCODE AND CHILDDEPT.DEPTSYS = DEPTSTR.CHILDCODE; CREATE VIEW FIXERVW AS SELECT FAULT.FAULTCODE, FIXER.SKILLCODE, FAULT.U_VERSION, FAULT.FAULTDES, FAULT.CRITCODE, FAULT.SYSACCTCODE, FAULT.USERCREATE, FAULT.DATECREATE, FAULT.USERCHANGE, FAULT.DATECHANGE FROM FAULT LEFT OUTER JOIN FIXER ON FIXER.FAULTCODE = FAULT.FAULTCODE; CREATE VIEW PLANTSTRVW AS SELECT PLANTRT.ROOTCODE, PLANTSTR.PARENTCODE, PLANTSTR.CHILDCODE, PLANTSTR.SEQ5, PLANTSTR.LEVELNO, LRATIO,NLRATIO,BUDRATIO,METERPOSTFLAG, EFFSTARTDATE,EFFENDDATE, ROOTPLANT.INSTPLANTCODE AS INSTPLANTROOT, ROOTPLANT.INSTPLANTDES AS ROOTDES, PARENTPLANT.INSTPLANTCODE AS INSTPLANTPARENT, PARENTPLANT.INSTPLANTDES AS PARENTDES, CHILDPLANT.INSTPLANTCODE AS INSTPLANTCHILD, CHILDPLANT.INSTPLANTDES AS CHILDDES FROM PLANTRT,PLANTSTR,PLANT ROOTPLANT,PLANT PARENTPLANT,PLANT CHILDPLANT WHERE PLANTSTR.ROOTCODE = PLANTRT.ROOTCODE AND PLANTRT.ROOTCODE = ROOTPLANT.INSTPLANTSYS AND PARENTPLANT.INSTPLANTSYS = PLANTSTR.PARENTCODE AND CHILDPLANT.INSTPLANTSYS = PLANTSTR.CHILDCODE; CREATE VIEW JTKCGVW AS SELECT JTKCG.JOBCODE, JTKCG.TKCGCODE, JOB.JOBDES, JOB.JSTATUSCODE, JOB.LOGDATE, JOB.DURUOMCODE, JOB.DURATION, PLANT.INSTPLANTCODE, PLANT.INSTPLANTDES, TKCG.TKCGDES, JSTATUS.JSTATUSNO, JSTATUS.JSTATUSDES FROM JTKCG,PLANT,TKCG,JOB LEFT OUTER JOIN JSTATUS ON JOB.JSTATUSCODE = JSTATUS.JSTATUSCODE WHERE JOB.JOBCODE = JTKCG.JOBCODE AND JOB.INSTPLANTSYS = PLANT.INSTPLANTSYS AND JTKCG.TKCGCODE = TKCG.TKCGCODE; CREATE VIEW JCITEMALCVW AS SELECT JCITEM.JOBCODE, JCITEM.FAULTCODE, JCITEM.INVFLAG, JCITEM.CATITEMCODE, JCITEM.U_VERSION, JCITEM.CATITEMDES, JCITEM.WAREHSECODE, JCITEM.QTYREQD, JCITEM.QTYISSUE, JCITEM.QTYRETURN, JCITEM.QTYUSED, JCITEM.SEQ5, JCITEM.USERCREATE, JCITEM.DATECREATE, JCITEM.USERCHANGE, JCITEM.DATECHANGE, JOB.JOBDES, JOB.EXTJOBDES, JOB.TASKCODE, JOB.INSTPLANTSYS, JOB.ALUOMCODE, JOB.ALARMVALUE, JOB.MAINTTYPECODE, JOB.SCHDATE, JOB.PRIORITY, JOB.DURUOMCODE, JOB.DURATION, JOB.ACCTCODE, JOB.PLANNER, JOB.RESPEMPCODE, JOB.LOGDATE, JOB.JSTATUSCODE, JOB.PREFWAREHSECODE, JSTATUS.JSTATUSDES, JSTATUS.JSTATUSNO FROM JOB LEFT OUTER JOIN JSTATUS ON JOB.JSTATUSCODE = JSTATUS.JSTATUSCODE JOIN JCITEM WHERE JOB.JOBCODE = JCITEM.JOBCODE AND JCITEM.INVFLAG = 'Y' AND JCITEM.QTYREQD > JCITEM.QTYUSED AND (JSTATUS.JSTATUSNO < 3 OR JOB.JSTATUSCODE IS NULL); CREATE VIEW JOBREQVW AS SELECT JOBREQ.REQCODE, JOBREQ.JOBCODE, JOBREQ.REQDES, JOBREQ.INSTPLANTSYS, JOBREQ.FAULTCODE, JOBREQ.EFFECTCODE, JOBREQ.PRIORITY, JOBREQ.REQSTATUSCODE, JOBREQ.MAINTTYPECODE, JOBREQ.RESTYPE, JOBREQ.RESGRPSYS, PLANT.INSTPLANTCODE, RESGRP.RESGRPCODE, DEPT.DEPTCODE, JOBREQ.ENTERBY, JOBREQ.DATERAISE, PLANT.CURLOCCODE, SKILL.SKILLCODE, IPFAULT.CRITCODE FROM JOBREQ LEFT OUTER JOIN RESGRP ON JOBREQ.RESGRPSYS = RESGRP.RESGRPSYS LEFT OUTER JOIN (PLANT LEFT OUTER JOIN DEPT ON PLANT.CURDEPTSYS = DEPT.DEPTSYS) ON JOBREQ.INSTPLANTSYS = PLANT.INSTPLANTSYS LEFT OUTER JOIN SKILL ON JOBREQ.SPARECHAR1 = SKILL.SKILLCODE LEFT OUTER JOIN IPFAULT ON JOBREQ.INSTPLANTSYS = IPFAULT.INSTPLANTSYS AND JOBREQ.FAULTCODE = IPFAULT.FAULTCODE; CREATE VIEW PNTATTRVW AS SELECT PNTATTR.PLANTTYPE, PNTTYPE.ATTRCODE, PNTTYPE.U_VERSION, PNTTYPE.USERCREATE, PNTTYPE.DATECREATE, PNTTYPE.USERCHANGE, PNTTYPE.DATECHANGE, PNTTYPE.SEQ5 FROM PNTATTR,PNTTYPE WHERE PNTTYPE.PLANTTYPE = PNTATTR.PLANTTYPE; CREATE VIEW WOSTRVW AS SELECT WORT.ROOTCODE, WOSTR.PARENTCODE, WOSTR.CHILDCODE, WOSTR.CHILDTYPE, WOSTR.SEQ5, WOSTR.LEVELNO, ROOTWO.WORKORDERDES AS ROOTDES FROM WORT,WOSTR,WORKORD ROOTWO WHERE WOSTR.ROOTCODE = WORT.ROOTCODE AND WORT.ROOTCODE = ROOTWO.WORKORDERCODE; CREATE VIEW PLANTVW AS SELECT PLANT.INSTPLANTSYS, PLANT.INSTPLANTCODE, PLANT.INSTPLANTDES, PLANT.ROTABLE, PLANT.CATPLANTCODE, PLANT.SUPPSYS, PLANT.APPRBODYCODE, PLANT.APPREXPDATE, PLANT.APPRNO, PLANT.SERIALNO, PLANT.ORDERNO, PLANT.INSTCOST, PLANT.INSTDATE, PLANT.WARDATE, PLANT.TARGETMTBF, PLANT.CURLOCCODE, PLANT.CURLOCDATE, PLANT.CURDEPTSYS, PLANT.CURMOVEDATE, PLANT.SYSACCTCODE, PLANT.INSTPLANTALIAS, PLANT.REPLACECOST, PLANT.POSTLOCCODE, PLANT.PLANTSTATUS, PLANT.MKTCOST, PLANT.TOTALCOST, EXPRETIREDATE, WARUOMCODE, WARVALUE, SERUOMCODE, SERVALUE, CALDUEDATE, BILLID, PLANT.SPARENUM1, PLANT.SPARENUM2, PLANT.SPARECHAR1, PLANT.SPARECHAR2, PLANT.SPAREDATE1, PLANT.SPAREDATE2, DEPT.DEPTCODE, RESGRP.RESGRPCODE AS SUPPCODE FROM PLANT LEFT JOIN RESGRP ON RESGRP.RESGRPSYS = PLANT.SUPPSYS LEFT JOIN DEPT ON DEPT.DEPTSYS = PLANT.CURDEPTSYS; CREATE VIEW JOBCMPIPVW AS SELECT PLANT.INSTPLANTSYS, PLANT.INSTPLANTCODE, PLANT.INSTPLANTDES, PLANT.CURLOCCODE, PLANTSTR.ROOTCODE, IPCATG.CATGCODE FROM PLANT LEFT JOIN PLANTSTR ON PLANTSTR.CHILDCODE = PLANT.INSTPLANTSYS LEFT JOIN IPCATG ON IPCATG.INSTPLANTSYS = PLANT.INSTPLANTSYS; CREATE VIEW FORECASTVW AS SELECT FORECAST.TASKCODE, FORECAST.UOMCODE, FORECAST.ALARMVALUE, FORECAST.SCHDATE, FORECAST.PRIORITY, FORECAST.FORECASTSTATUS, FORECAST.JOBCODE, TASK.INSTPLANTSYS, TASK.TASKDES, TASK.SPARECHAR1, PLANT.INSTPLANTCODE, PLANT.INSTPLANTDES, PLANT.CURLOCCODE, DEPT.DEPTCODE FROM FORECAST,TASK, PLANT LEFT OUTER JOIN DEPT ON PLANT.CURDEPTSYS = DEPT.DEPTSYS WHERE TASK.INSTPLANTSYS = PLANT.INSTPLANTSYS AND FORECAST.TASKCODE = TASK.TASKCODE; CREATE VIEW TASKVW AS SELECT TASK.TASKCODE, TASK.U_VERSION, TASK.TASKDES, TASK.INSTPLANTSYS, TASK.WORKORDERCODE, TASK.TOLERANCE, TASK.DURATION, TASK.UOMCODE, TASK.ACCTCODE, TASK.SYSACCTCODE, TASK.SPARENUM1, TASK.SPARENUM2, TASK.SPARECHAR1, TASK.SPARECHAR2, TASK.SPAREDATE1, TASK.SPAREDATE2, TASK.USERCREATE, TASK.DATECREATE, TASK.USERCHANGE, TASK.DATECHANGE, PLANT.INSTPLANTCODE, PLANT.CURLOCCODE, DEPT.DEPTCODE, CHRTACCT.ACCTDES, CHRTACCT.ACCTALIAS FROM TASK LEFT OUTER JOIN CHRTACCT ON TASK.ACCTCODE = CHRTACCT.ACCTCODE JOIN (PLANT LEFT OUTER JOIN DEPT ON PLANT.CURDEPTSYS = DEPT.DEPTSYS) WHERE TASK.INSTPLANTSYS = PLANT.INSTPLANTSYS; CREATE VIEW JOBCVTVW AS SELECT JOB.JOBCODE, JOB.TASKCODE, PLANT.INSTPLANTCODE, JOB.SCHDATE, JOB.PRIORITY, JOB.JSTATUSCODE, JOB.RESPEMPCODE, JSTATUS.JSTATUSNO, JSTATUS.JSTATUSDES, PLANT.CURLOCCODE, DEPT.DEPTCODE, JOB.PLANNER, WOSTR.PARENTCODE AS PROJECTCODE FROM JOB LEFT OUTER JOIN JSTATUS ON JOB.JSTATUSCODE = JSTATUS.JSTATUSCODE LEFT OUTER JOIN WOSTR ON JOB.TXTJOBCODE = WOSTR.CHILDCODE AND WOSTR.CHILDTYPE = 'J' JOIN (PLANT LEFT OUTER JOIN DEPT ON PLANT.CURDEPTSYS = DEPT.DEPTSYS) WHERE JOB.INSTPLANTSYS = PLANT.INSTPLANTSYS AND (JSTATUS.JSTATUSNO < 2 OR JSTATUS.JSTATUSNO IS NULL); CREATE VIEW RESRCVW AS SELECT RESRC.RESCODE, RESRC.U_VERSION, RESRC.SURNAME, RESRC.FIRSTNAME, RESRC.RESTYPE, RESRC.RESGRPSYS, RESRC.POSITION, RESRC.WORKGRPCODE, RESRC.STARTDATE, RESRC.ENDDATE, RESRC.BASICPAY, RESRC.RESALIAS, RESRC.SPARENUM1, RESRC.SPARENUM2, RESRC.SPARECHAR1, RESRC.SPARECHAR2, RESRC.SPAREDATE1, RESRC.SPAREDATE2, RESRC.USERCREATE, RESRC.DATECREATE, RESRC.USERCHANGE, RESRC.DATECHANGE, RESGRP.RESGRPCODE FROM RESRC, RESGRP WHERE RESGRP.RESGRPSYS = RESRC.RESGRPSYS; CREATE VIEW NURDEFALLVW AS SELECT NURDEF.URDEFSYS, NURDEF.URDEFYEAR, NURDEF.SIGNALNO, NURDEF.U_VERSION, NURDEF.DTG, NURDEF.FROMSHIP, NURDEF.TODEST1, NURDEF.TODEST2, NURDEF.TODEST3, NURDEF.INFO1, NURDEF.INFO2, NURDEF.INFO3, NURDEF.INFO4, NURDEF.INFO5, NURDEF.INFO6, NURDEF.SICACTSUPP, NURDEF.SICACTTECH, NURDEF.SICACTOPR, NURDEF.SUBJ, NURDEF.INSTPLANTSYS, NURDEF.INSTPLANTDES, NURDEF.PARENTPLANT, NURDEF.JOBCODE, NURDEF.JOBDES, NURDEF.PRIORITY, NURDEF.SPARENUM1, NURDEF.SPARENUM2, NURDEF.SPARCHAR1, NURDEF.SPARECHAR2, NURDEF.SPAREDATE1, NURDEF.SPAREDATE2, NURDEF.DEFSKILL, NURDEF.EFFSHIP, NURDEF.SPECEQUIP, NURDEF.AVAILREPAIR, NURDEF.QUALITYCONTROL, NURDEF.APLNO, NURDEF.APLDES, NURDEF.NEXTPARENT, NURDEF.PDNN, PARAGS.CATITEMSYS, PARAGS.STOCKNO, PARAGS.PUBLICREF, PARAGS.CATITEMDES, PARAGS.QTYREQD, PARAGS.QTYUSED, PARAGS.MANAGECODE, PARAGS.QTYALLOW, PARAGS.SEERCODE, PARAGS.DEMANDREF, PARAGS.ACCEPTALT, PARAGS.SHIPDETAIL, PLANT.INSTPLANTCODE, CATITEM.CATITEMCODE FROM NURDEF LEFT JOIN (PARAGS LEFT JOIN CATITEM ON CATITEM.CATITEMSYS = PARAGS.CATITEMSYS) ON PARAGS.URDEFSYS = NURDEF.URDEFSYS AND PARAGS.URDEFYEAR = NURDEF.URDEFYEAR AND PARAGS.SIGNALNO = NURDEF.SIGNALNO LEFT JOIN PLANT ON PLANT.INSTPLANTSYS = NURDEF.INSTPLANTSYS; CREATE VIEW JBMDVW AS SELECT JOB.JOBCODE, JMAXLOGVW.MAXLOGID, JLOG.LOGDATE, JSTATUS.JSTATUSNO, PLANT.INSTPLANTCODE, PLANT.INSTPLANTDES, MAINTYPE.MAINTTYPEDES FROM JOB,JMAXLOGVW,JLOG,JSTATUS,PLANT,MAINTYPE WHERE JOB.JOBCODE = JMAXLOGVW.JOBCODE AND JMAXLOGVW.MAXLOGID = JLOG.LOGID AND JMAXLOGVW.JOBCODE = JLOG.JOBCODE AND JSTATUS.JSTATUSCODE = JLOG.JSTATUSCODE AND JOB.INSTPLANTSYS = PLANT.INSTPLANTSYS AND JOB.MAINTTYPECODE = MAINTYPE.MAINTTYPECODE; CREATE VIEW WORKTOVW AS SELECT JOB.JOBCODE, FAULTCODE, SKILLCODE, JFIXER.U_VERSION, ESTHR, ACTHR, JFIXER.USERCREATE, JFIXER.DATECREATE, JFIXER.USERCHANGE, JFIXER.DATECHANGE, JOB.SCHDATE, JOB.MAINTTYPECODE, JOB.JOBDES, JOB.JSTATUSCODE, JOB.PRIORITY, JSTATUS.JSTATUSNO, PLANT.INSTPLANTCODE, PLANT.INSTPLANTDES, DEPT.DEPTCODE FROM JFIXER , JOB, JSTATUS, (PLANT LEFT OUTER JOIN DEPT ON PLANT.CURDEPTSYS = DEPT.DEPTSYS) WHERE JFIXER.JOBCODE = JOB.JOBCODE AND JOB.JSTATUSCODE = JSTATUS.JSTATUSCODE AND JSTATUS.JSTATUSNO IN (1,2) AND JOB.INSTPLANTSYS = PLANT.INSTPLANTSYS; CREATE VIEW JOBVW AS SELECT JOB.JOBCODE, JOB.JOBDES, JOB.EXTJOBDES, JOB.TASKCODE, JOB.INSTPLANTSYS, JOB.ALUOMCODE, JOB.ALARMVALUE, JOB.MAINTTYPECODE, JOB.SCHDATE, JOB.PRIORITY, JOB.DURUOMCODE, JOB.DURATION, JOB.ACCTCODE, JOB.PLANNER, JOB.RESPEMPCODE, JOB.LOGDATE, JOB.JSTATUSCODE, JOB.PREFWAREHSECODE, JOB.SPARENUM1, JOB.SPARENUM2, JOB.SPARECHAR1, JOB.SPARECHAR2, JOB.SPAREDATE1, JOB.SPAREDATE2, PLANT.INSTPLANTCODE, PLANT.CURLOCCODE, DEPT.DEPTCODE, WOSTR.PARENTCODE AS WORKORDERCODE, JOB.EXTJOBCODE, JSTATUS.JSTATUSNO FROM JOB LEFT OUTER JOIN WOSTR ON JOB.TXTJOBCODE = WOSTR.CHILDCODE AND WOSTR.CHILDTYPE = 'J' LEFT OUTER JOIN JSTATUS ON JSTATUS.JSTATUSCODE = JOB.JSTATUSCODE JOIN PLANT LEFT OUTER JOIN DEPT ON PLANT.CURDEPTSYS = DEPT.DEPTSYS WHERE JOB.INSTPLANTSYS = PLANT.INSTPLANTSYS; CREATE VIEW TTDEPVW AS SELECT DEPT.DEPTCODE, PLANT.INSTPLANTSYS, TASK.TASKCODE, TALARM.UOMCODE, DEPT.DEPTDES, TASK.TASKDES FROM TASK LEFT OUTER JOIN TALARM ON TASK.TASKCODE = TALARM.TASKCODE JOIN PLANT LEFT OUTER JOIN DEPT ON PLANT.CURDEPTSYS = DEPT.DEPTSYS WHERE TASK.INSTPLANTSYS = PLANT.INSTPLANTSYS; CREATE VIEW AS2PLANTVW AS SELECT ASSET.ASSETSYS, ASPLANT.INSTPLANTSYS, ASSET.ASSETNO, ASSET.DEPRFLAG, ASSET.ASSETSTATUS, PLANT.INSTPLANTCODE FROM ASSET LEFT JOIN (ASPLANT LEFT OUTER JOIN PLANT ON PLANT.INSTPLANTSYS = ASPLANT.INSTPLANTSYS) ON ASPLANT.ASSETSYS = ASSET.ASSETSYS; CREATE VIEW MLVW AS SELECT METERS.INSTPLANTSYS, METERS.UOMCODE, PLANT.INSTPLANTCODE, PLANT.CURLOCCODE, DEPT.DEPTCODE, IPCATG.CATGCODE, UOM.UOMDES, PLANT.INSTPLANTDES FROM METERS, UOM JOIN PLANT LEFT OUTER JOIN IPCATG ON PLANT.INSTPLANTSYS = IPCATG.INSTPLANTSYS LEFT OUTER JOIN DEPT ON PLANT.CURDEPTSYS= DEPT.DEPTSYS WHERE METERS.INSTPLANTSYS = PLANT.INSTPLANTSYS AND METERS.UOMCODE = UOM.UOMCODE; CREATE VIEW FMVW AS SELECT FM.INSTPLANTSYS, FM.UOMCODE, PLANT.INSTPLANTCODE, PLANT.CURLOCCODE, DEPT.DEPTCODE, IPCATG.CATGCODE, UOM.UOMDES, PLANT.INSTPLANTDES FROM UOM,FM JOIN (PLANT LEFT OUTER JOIN DEPT ON PLANT.CURDEPTSYS = DEPT.DEPTSYS LEFT OUTER JOIN IPCATG ON PLANT.INSTPLANTSYS = IPCATG.INSTPLANTSYS) WHERE FM.INSTPLANTSYS = PLANT.INSTPLANTSYS AND FM.UOMCODE = UOM.UOMCODE; CREATE VIEW LDGTRNVW AS SELECT LDGTRAN.TRANID, LDGTRAN.TRANAMT, LDGTRAN.TRANDATE, LDGTRAN.ACCTCODE, LDGTRAN.ACCTYEAR, LDGTRAN.ACCTMTH, LDGTRAN.REFID, LDGTRAN.REFTYPE, LDGTRAN.SOURCEID, CHRTACCT.ACCTDES, CHRTACCT.ACCTALIAS FROM LDGTRAN,CHRTACCT WHERE LDGTRAN.ACCTCODE = CHRTACCT.ACCTCODE; CREATE VIEW NUDVW (URDEFSYS,URDEFYEAR,SIGNALNO,U_VERSION,DTG, FROMSHIP,TODEST1,TODEST2,TODEST3, INFO1,INFO2,INFO3,INFO4,INFO5,INFO6, SICACTSUPP,SICACTTECH,SICACTOPR,SUBJ, INSTPLANTSYS,INSTPLANTDES,PARENTPLANT,JOBCODE, JOBDES,PRIORITY,SPARENUM1,SPARENUM2,SPARECHAR1,SPARECHAR2, SPAREDATE1,SPAREDATE2,DEFSKILL,EFFSHIP, SPECEQUIP,AVAILREPAIR,QUALITYCONTROL,APLNO, APLDES,NEXTPARENT,PDNN,INSTPLANTCODE,IPPARENTCODE) AS SELECT NURDEF.URDEFSYS, NURDEF.URDEFYEAR, NURDEF.SIGNALNO, NURDEF.U_VERSION, NURDEF.DTG, NURDEF.FROMSHIP, NURDEF.TODEST1, NURDEF.TODEST2, NURDEF.TODEST3, NURDEF.INFO1, NURDEF.INFO2, NURDEF.INFO3, NURDEF.INFO4, NURDEF.INFO5, NURDEF.INFO6, NURDEF.SICACTSUPP, NURDEF.SICACTTECH, NURDEF.SICACTOPR, NURDEF.SUBJ, NURDEF.INSTPLANTSYS, NURDEF.INSTPLANTDES, NURDEF.PARENTPLANT, NURDEF.JOBCODE, NURDEF.JOBDES, NURDEF.PRIORITY, NURDEF.SPARENUM1, NURDEF.SPARENUM2, NURDEF.SPARCHAR1, NURDEF.SPARECHAR2, NURDEF.SPAREDATE1, NURDEF.SPAREDATE2, NURDEF.DEFSKILL, NURDEF.EFFSHIP, NURDEF.SPECEQUIP, NURDEF.AVAILREPAIR, NURDEF.QUALITYCONTROL, NURDEF.APLNO, NURDEF.APLDES, NURDEF.NEXTPARENT, NURDEF.PDNN, PLANT1.INSTPLANTCODE, PLANT2.INSTPLANTCODE IPPARENTCODE FROM NURDEF LEFT OUTER JOIN PLANT PLANT1 ON PLANT1.INSTPLANTSYS = NURDEF.INSTPLANTSYS LEFT OUTER JOIN PLANT PLANT2 ON NURDEF.PARENTPLANT = PLANT2.INSTPLANTSYS; CREATE VIEW NTM200VW AS SELECT JOB.JOBCODE, JFIXER.SKILLCODE, JFIXER.FAULTCODE, PLANT.INSTPLANTCODE, DEPT.DEPTCODE, JOB.JOBDES, JOB.SCHDATE, JOB.JSTATUSCODE, JOB.PRIORITY, JOB.PLANNER, JOB.RESPEMPCODE, JOB.DURATION, JOB.DURUOMCODE, JOB.EXTJOBCODE TM200NO, JOB.TASKCODE, JOB.ACCTCODE, JOB.MAINTTYPECODE, PLANT.INSTPLANTDES, NURDEF.URDEFSYS, NURDEF.URDEFYEAR, JFIXER.ACTHR, JFIXER.ESTHR, WOSTR.PARENTCODE PROJECTCODE FROM JOB LEFT OUTER JOIN JFIXER ON JOB.JOBCODE = JFIXER.JOBCODE LEFT OUTER JOIN NURDEF ON JOB.JOBCODE = NURDEF.JOBCODE LEFT OUTER JOIN WOSTR ON (JOB.TXTJOBCODE = WOSTR.CHILDCODE AND WOSTR.CHILDTYPE = 'J') JOIN PLANT LEFT OUTER JOIN DEPT ON PLANT.CURDEPTSYS = DEPT.DEPTSYS WHERE PLANT.INSTPLANTSYS = JOB.INSTPLANTSYS ; CREATE VIEW JBSEVW AS SELECT JOB.JOBCODE, JFIXER.SKILLCODE, JFIXER.FAULTCODE, PLANT.INSTPLANTCODE, DEPT.DEPTCODE, JOB.JOBDES, JOB.SCHDATE, JOB.JSTATUSCODE, JOB.PRIORITY, JOB.MAINTTYPECODE, JOB.PLANNER, JOB.RESPEMPCODE, JOB.DURATION, JOB.DURUOMCODE, PLANT.INSTPLANTDES, JFIXER.ACTHR, JFIXER.ESTHR, JTKCG.TKCGCODE, WOSTR.PARENTCODE FROM JOB LEFT OUTER JOIN JFIXER ON JOB.JOBCODE = JFIXER.JOBCODE LEFT OUTER JOIN JTKCG ON JOB.JOBCODE = JTKCG.JOBCODE LEFT OUTER JOIN WOSTR ON (JOB.TXTJOBCODE = WOSTR.CHILDCODE AND WOSTR.CHILDTYPE = 'J') JOIN PLANT LEFT OUTER JOIN DEPT ON PLANT.CURDEPTSYS = DEPT.DEPTSYS WHERE PLANT.INSTPLANTSYS = JOB.INSTPLANTSYS ; CREATE VIEW JBCMVW AS SELECT JOB.JOBCODE, JFIXER.SKILLCODE, JFIXER.FAULTCODE, PLANT.INSTPLANTCODE, DEPT.DEPTCODE, JOB.JOBDES, JOB.SCHDATE, JOB.JSTATUSCODE, JOB.MAINTTYPECODE, JOB.PLANNER, JOB.RESPEMPCODE, JOB.TASKCODE, PLANT.INSTPLANTDES, JFIXER.ACTHR, JFIXER.ESTHR, WOSTR.PARENTCODE PROJECTCODE FROM JOB LEFT OUTER JOIN JFIXER ON JOB.JOBCODE = JFIXER.JOBCODE LEFT OUTER JOIN WOSTR ON (JOB.TXTJOBCODE = WOSTR.CHILDCODE AND WOSTR.CHILDTYPE = 'J') JOIN PLANT LEFT OUTER JOIN DEPT ON PLANT.CURDEPTSYS = DEPT.DEPTSYS WHERE PLANT.INSTPLANTSYS = JOB.INSTPLANTSYS; CREATE VIEW JOBPLANTVW AS SELECT JOB.JOBCODE, JOB.JOBDES, JOB.TASKCODE, JOB.INSTPLANTSYS, PLANT.INSTPLANTCODE, PLANT.INSTPLANTDES, JOB.SCHDATE, JOB.PRIORITY, JOB.DURUOMCODE, JOB.DURATION, JOB.ALUOMCODE, JOB.ALARMVALUE, JOB.PLANNER, JOB.RESPEMPCODE, JOB.LOGDATE, JOB.JSTATUSCODE, JSTATUS.JSTATUSNO, JSTATUS.JSTATUSDES FROM JOB LEFT OUTER JOIN JSTATUS ON JSTATUS.JSTATUSCODE = JOB.JSTATUSCODE JOIN PLANT WHERE JOB.INSTPLANTSYS = PLANT.INSTPLANTSYS AND (JSTATUSNO < 3 OR JSTATUSNO IS NULL); CREATE VIEW RSSKVW AS SELECT RESRC.RESCODE, RESRC.SURNAME, RESRC.FIRSTNAME, RESRC.RESTYPE, RESGRP.RESGRPCODE, RESRC.WORKGRPCODE, RESRC.STARTDATE, RESRC.ENDDATE, RESRC.BASICPAY, RESSKILL.SKILLCODE, RESSKILL.EFFSTARTDATE, RESSKILL.EFFENDDATE, RESOPR.OPRDATE, RESOPR.OPRTIME, RESOPR.TOOPRTIME, DATE_SUB(RESOPR.TOOPRTIME, INTERVAL DAY( RESOPR.OPRTIME ) DAY ) AS OPRHR, RESOPR.RSSTATUSCODE, RSTATUS.RSSTATUSNO FROM RESSKILL,RESRC,RESOPR,RESGRP,RSTATUS WHERE RESOPR.RSSTATUSCODE = RSTATUS.RSSTATUSCODE AND RSTATUS.RSSTATUSNO = 0 AND RESOPR.RESCODE = RESSKILL.RESCODE AND RESSKILL.RESCODE = RESRC.RESCODE AND RESRC.RESGRPSYS = RESGRP.RESGRPSYS AND RESRC.RESTYPE = 'TRADE'; CREATE VIEW JOBTDVW AS SELECT JOB.JOBCODE, JFIXER.SKILLCODE, JFIXER.FAULTCODE, PLANT.INSTPLANTCODE, JOB.TASKCODE, JOB.JOBDES, JOB.EXTJOBDES, JOB.SCHDATE, JOB.JSTATUSCODE, JOB.RESPEMPCODE, JOB.MAINTTYPECODE, JOB.DURATION, JOB.DURUOMCODE, JOB.PRIORITY, PLANT.INSTPLANTDES, JFIXER.ACTHR, JFIXER.ESTHR, JSTATUS.JSTATUSDES, DEPT.DEPTCODE, DEPT.DEPTDES FROM JOB LEFT OUTER JOIN JFIXER ON JFIXER.JOBCODE = JOB.JOBCODE JOIN PLANT LEFT OUTER JOIN DEPT ON DEPT.DEPTSYS = PLANT.CURDEPTSYS JOIN JSTATUS WHERE PLANT.INSTPLANTSYS = JOB.INSTPLANTSYS AND JSTATUS.JSTATUSCODE = JOB.JSTATUSCODE; CREATE VIEW PARAGVW AS SELECT PARAGS.URDEFSYS, PARAGS.URDEFYEAR, PARAGS.SIGNALNO, PARAGS.CATITEMSYS, PARAGS.U_VERSION, PARAGS.STOCKNO, PARAGS.PUBLICREF, PARAGS.CATITEMDES, PARAGS.QTYREQD, PARAGS.QTYUSED, PARAGS.MANAGECODE, PARAGS.QTYALLOW, PARAGS.SEERCODE, PARAGS.DEMANDREF, PARAGS.ACCEPTALT, PARAGS.UOMCODE, PARAGS.SHIPDETAIL, CATITEM.CATITEMCODE FROM PARAGS,CATITEM WHERE CATITEM.CATITEMSYS = PARAGS.CATITEMSYS; CREATE VIEW JBPROVW AS SELECT JOB.JOBCODE, JOB.JOBDES, JOB.TASKCODE, JOB.SCHDATE, JOB.JSTATUSCODE, JOB.DURUOMCODE, JOB.DURATION, PLANT.INSTPLANTCODE, WORKORD.WORKORDERCODE, WORKORD.WORKORDERDES FROM JOB,PLANT,WOSTR,WORKORD WHERE PLANT.INSTPLANTSYS = JOB.INSTPLANTSYS AND WOSTR.CHILDTYPE = 'J' AND WOSTR.CHILDCODE = JOB.TXTJOBCODE AND WORKORD.WORKORDERCODE = WOSTR.PARENTCODE; CREATE VIEW SYPEVW AS SELECT FCTEMP.TASKCODE, FCTEMP.UOMCODE, FCTEMP.ALARMVALUE, FCTEMP.SCHDATE, FCTEMP.PRIORITY, FCTEMP.FORECASTSTATUS, FCTEMP.JOBCODE, FCTEMP.REFNO, FCTEMP.PERIOD, FCTEMP.INSTPLANTSYS, FCTEMP.DELFLAG, PLANT.INSTPLANTCODE FROM FCTEMP,PLANT WHERE PLANT.INSTPLANTSYS = FCTEMP.INSTPLANTSYS; CREATE VIEW SYPOVW AS SELECT FORECAST.TASKCODE, FORECAST.UOMCODE, FORECAST.ALARMVALUE, FORECAST.SCHDATE, FORECAST.PRIORITY, FORECAST.FORECASTSTATUS, FORECAST.JOBCODE, TASK.WORKORDERCODE, TASK.INSTPLANTSYS, PLANT.INSTPLANTCODE FROM FORECAST,TASK,PLANT WHERE TASK.TASKCODE = FORECAST.TASKCODE AND PLANT.INSTPLANTSYS = TASK.INSTPLANTSYS; CREATE VIEW GENLDGVW AS SELECT GENLDG.ACCTYEAR, GENLDG.ACCTMTH, GENLDG.ACCTCODE, GENLDG.BUDAMT, GENLDG.ACTAMT, CHRTACCT.ACCTDES, CHRTACCT.ACCTALIAS, CHRTACCT.ACCTFLAG, CHRTACCT.ACCTTYPE FROM GENLDG,CHRTACCT WHERE GENLDG.ACCTCODE = CHRTACCT.ACCTCODE; CREATE VIEW IPCITEMVW AS SELECT IPCITEM.INSTPLANTSYS, INVFLAG, IPCITEM.CATITEMCODE, IPCITEM.CATITEMDES, QTYREQD, SEQ5, PLANT.INSTPLANTCODE, CATITEM.CATITEMCODE INVCATITEMCODE FROM IPCITEM,CATITEM,PLANT WHERE IPCITEM.INSTPLANTSYS = PLANT.INSTPLANTSYS AND IPCITEM.CATITEMCODE = CATITEM.TXTCATITEMSYS AND IPCITEM.INVFLAG = 'Y'; CREATE VIEW JBFDVW AS SELECT FAULT.FAULTCODE, JOB.JOBCODE, FAULT.FAULTDES, JOB.JOBDES, JOB.JSTATUSCODE, JOB.LOGDATE, PLANT.INSTPLANTCODE, PLANT.INSTPLANTDES, DEPT.DEPTCODE, DEPT.DEPTDES FROM FAULT,JFAULT,JOB,PLANT LEFT OUTER JOIN DEPT ON PLANT.CURDEPTSYS = DEPT.DEPTSYS WHERE JFAULT.FAULTCODE = FAULT.FAULTCODE AND JOB.JOBCODE = JFAULT.JOBCODE AND PLANT.INSTPLANTSYS = JOB.INSTPLANTSYS; CREATE VIEW JBFMDVW AS SELECT FAULT.FAULTCODE, JOB.JOBCODE, FAULT.FAULTDES, JOB.JOBDES, JOB.DURUOMCODE, JOB.DURATION, JOB.LOGDATE, PLANT.INSTPLANTCODE, PLANT.INSTPLANTDES, DEPT.DEPTCODE, DEPT.DEPTDES FROM FAULT,JFAULT,JOB,PLANT,DEPT WHERE JFAULT.FAULTCODE = FAULT.FAULTCODE AND JOB.JOBCODE = JFAULT.JOBCODE AND JOB.JSTATUSCODE = 'X' AND (JOB.LOGDATE <= CURDATE() AND JOB.LOGDATE >= DATE_ADD(CURDATE(),INTERVAL -90 DAY) ) AND PLANT.INSTPLANTSYS = JOB.INSTPLANTSYS AND DEPT.DEPTSYS = PLANT.CURDEPTSYS; CREATE VIEW JBDCALVW AS SELECT JFAULT.FAULTCODE, JFAULT.JOBCODE, JOB.DURATION AS TOTDUR FROM JFAULT,JOB WHERE JOB.JOBCODE = JFAULT.JOBCODE AND JOB.DURUOMCODE = 'D' AND JOB.JSTATUSCODE = 'X' AND (JOB.LOGDATE <= CURDATE() AND JOB.LOGDATE >= DATE_ADD(CURDATE(),INTERVAL -90 DAY) ) UNION SELECT JFAULT.FAULTCODE, JFAULT.JOBCODE, JOB.DURATION * 7 AS TOTDUR FROM JFAULT,JOB WHERE JOB.JOBCODE = JFAULT.JOBCODE AND JOB.DURUOMCODE = 'W' AND JOB.JSTATUSCODE = 'X' AND (JOB.LOGDATE <= CURDATE() AND JOB.LOGDATE >= DATE_ADD(CURDATE(),INTERVAL -90 DAY) ) UNION SELECT JFAULT.FAULTCODE, JFAULT.JOBCODE, JOB.DURATION * 28 AS TOTDUR FROM JFAULT,JOB WHERE JOB.JOBCODE = JFAULT.JOBCODE AND JOB.DURUOMCODE = 'M' AND JOB.JSTATUSCODE = 'X' AND (JOB.LOGDATE <= CURDATE() AND JOB.LOGDATE >= DATE_ADD(CURDATE(),INTERVAL -90 DAY) ) UNION SELECT JFAULT.FAULTCODE, JFAULT.JOBCODE, JOB.DURATION * 365 AS TOTDUR FROM JFAULT,JOB,JLOG WHERE JOB.JOBCODE = JFAULT.JOBCODE AND JOB.DURUOMCODE = 'Y' AND JOB.JSTATUSCODE = 'X' AND (JOB.LOGDATE <= CURDATE() AND JOB.LOGDATE >= DATE_ADD(CURDATE(),INTERVAL -90 DAY) ) UNION SELECT JFAULT.FAULTCODE, JFAULT.JOBCODE, JOB.DURATION / 24 AS TOTDUR FROM JFAULT,JOB WHERE JOB.JOBCODE = JFAULT.JOBCODE AND JOB.DURUOMCODE = 'H' AND JOB.JSTATUSCODE = 'X' AND (JOB.LOGDATE <= CURDATE() AND JOB.LOGDATE >= DATE_ADD(CURDATE(),INTERVAL -90 DAY) ) ; CREATE VIEW JBLABVW AS SELECT JFAULT.FAULTCODE, JFAULT.JOBCODE, SUM(LABTRAN.LABHR) AS LABHOUR FROM JFAULT,JOB,LABTRAN WHERE JOB.JOBCODE = JFAULT.JOBCODE AND JOB.JSTATUSCODE = 'X' AND (JOB.LOGDATE <= CURDATE() AND JOB.LOGDATE >= DATE_ADD(CURDATE(),INTERVAL -90 DAY) ) AND LABTRAN.JOBCODE = JFAULT.JOBCODE AND LABTRAN.FAULTCODE = JFAULT.FAULTCODE GROUP BY JFAULT.FAULTCODE,JFAULT.JOBCODE; CREATE VIEW JBLCALVW AS SELECT JFAULT.FAULTCODE, LABTRAN.LABHR FROM JFAULT,LABTRAN,JOB WHERE LABTRAN.FAULTCODE = JFAULT.FAULTCODE AND LABTRAN.JOBCODE = JFAULT.JOBCODE AND JOB.JOBCODE = JFAULT.JOBCODE AND JOB.JSTATUSCODE = 'X' AND (JOB.LOGDATE <= CURDATE() AND JOB.LOGDATE >= DATE_ADD(CURDATE(),INTERVAL -90 DAY) ); CREATE VIEW JBLTOTVW AS SELECT DISTINCT(JBLCALVW.FAULTCODE), SUM(JBLCALVW.LABHR) AS TOTFMLHR FROM JBLCALVW GROUP BY FAULTCODE; CREATE VIEW JBFMLVW AS SELECT JFAULT.FAULTCODE, JFAULT.JOBCODE, FAULT.FAULTDES, JOB.JOBDES, JOB.LOGDATE, PLANT.INSTPLANTCODE, PLANT.INSTPLANTDES, DEPT.DEPTCODE, DEPT.DEPTDES, JBLABVW.LABHOUR FROM FAULT,JOB,PLANT,DEPT, JFAULT LEFT OUTER JOIN JBLABVW ON (JBLABVW.FAULTCODE = JFAULT.FAULTCODE AND JBLABVW.JOBCODE = JFAULT.JOBCODE) WHERE FAULT.FAULTCODE = JFAULT.FAULTCODE AND JOB.JOBCODE = JFAULT.JOBCODE AND JOB.JSTATUSCODE = 'X' AND (JOB.LOGDATE <= CURDATE() AND JOB.LOGDATE >= DATE_ADD(CURDATE(),INTERVAL -90 DAY) ) AND PLANT.INSTPLANTSYS = JOB.INSTPLANTSYS AND DEPT.DEPTSYS = PLANT.CURDEPTSYS; CREATE VIEW JBCCALVW AS SELECT JFAULT.FAULTCODE, LABTRAN.LABCOST AS TOTCOST FROM JFAULT,LABTRAN,JOB WHERE LABTRAN.FAULTCODE = JFAULT.FAULTCODE AND LABTRAN.JOBCODE = JFAULT.JOBCODE AND JOB.JOBCODE = JFAULT.JOBCODE AND JOB.JSTATUSCODE = 'X' AND (JOB.LOGDATE <= CURDATE() AND JOB.LOGDATE >= DATE_ADD(CURDATE(),INTERVAL -90 DAY ) ) UNION SELECT JFAULT.FAULTCODE, MATLTRAN.MATLCOST AS TOTCOST FROM JFAULT,MATLTRAN,JOB WHERE MATLTRAN.FAULTCODE = JFAULT.FAULTCODE AND MATLTRAN.JOBCODE = JFAULT.JOBCODE AND JOB.JOBCODE = JFAULT.JOBCODE AND JOB.JSTATUSCODE = 'X' AND (JOB.LOGDATE <= CURDATE() AND JOB.LOGDATE >= DATE_ADD(CURDATE(),INTERVAL -90 DAY ) ) UNION SELECT JFAULT.FAULTCODE, IRTRAN.COSTISSUE AS TOTCOST FROM JFAULT,IRTRAN,JOB WHERE IRTRAN.FAULTCODE = JFAULT.FAULTCODE AND IRTRAN.JOBCODE = JFAULT.JOBCODE AND JOB.JOBCODE = JFAULT.JOBCODE AND JOB.JSTATUSCODE = 'X' AND (JOB.LOGDATE <= CURDATE() AND JOB.LOGDATE >= DATE_ADD(CURDATE(),INTERVAL -90 DAY)); CREATE VIEW RESRATEVW AS SELECT RESRC.RESCODE, SKILLCODE, SURNAME, FIRSTNAME, RESRC.RESTYPE, RESRC.RESGRPSYS, WORKGRPCODE, STARTDATE, ENDDATE, BASICPAY, EFFSTARTDATE, EFFENDDATE, COSTRATE FROM RESRC,RESSKILL,RESGRP WHERE RESRC.RESCODE = RESSKILL.RESCODE AND RESRC.RESTYPE = RESGRP.RESTYPE AND RESRC.RESGRPSYS = RESGRP.RESGRPSYS; CREATE VIEW JINVVW AS SELECT JOBCODE, CATITEMCODE, SUM(QTYREQD - QTYUSED) AS OUTSTDQTY FROM JCITEM WHERE INVFLAG = 'Y' GROUP BY JOBCODE,CATITEMCODE; CREATE VIEW JINVWHVW AS SELECT JOBCODE, CATITEM.CATITEMCODE, CATITEM.CATITEMDES, WAREHSECODE, OUTSTDQTY, QTYONHAND FROM JINVVW,CATITEM,CIWH WHERE JINVVW.CATITEMCODE = CATITEM.TXTCATITEMSYS AND CATITEM.CATITEMSYS = CIWH.CATITEMSYS; CREATE VIEW JPOVW AS SELECT DISTINCT POSYS,JOBCODE FROM POITEM; CREATE VIEW TALARMVW AS SELECT TALARM.TASKCODE, TALARM.UOMCODE, ALARMVALUE, PRIORITY, LASTDONEDATE, TASKDES, INSTPLANTSYS, UOMDES FROM TALARM,TASK,UOM WHERE TALARM.TASKCODE = TASK.TASKCODE AND TALARM.UOMCODE = UOM.UOMCODE; CREATE VIEW TFIXERVW AS SELECT FORECAST.TASKCODE, FORECAST.UOMCODE, FORECAST.ALARMVALUE, FORECAST.SCHDATE, FORECAST.FORECASTSTATUS, TFIXER.SKILLCODE, TFIXER.FAULTCODE, TFIXER.ESTHR, TASK.INSTPLANTSYS, PLANT.INSTPLANTCODE, DEPT.DEPTCODE, TTKCG.TKCGCODE FROM FORECAST,TFIXER,TASK LEFT OUTER JOIN TTKCG ON TASK.TASKCODE = TTKCG.TASKCODE JOIN (PLANT LEFT OUTER JOIN DEPT ON PLANT.CURDEPTSYS = DEPT.DEPTSYS) ON PLANT.INSTPLANTSYS = TASK.INSTPLANTSYS WHERE FORECAST.TASKCODE = TFIXER.TASKCODE AND TASK.TASKCODE = FORECAST.TASKCODE; CREATE VIEW TCIQTYVW AS SELECT DISTINCT TASKCODE, CATITEMCODE, INVFLAG, CATITEMDES, SUM(QTYREQD) AS ESTQTY FROM TCITEM GROUP BY TASKCODE,CATITEMCODE,INVFLAG,CATITEMDES; CREATE VIEW FNPLVW AS SELECT FORECAST.TASKCODE, FORECAST.UOMCODE, FORECAST.ALARMVALUE, FORECAST.SCHDATE, FORECAST.PRIORITY, TFIXER.SKILLCODE, TFIXER.ESTHR, PLANT.INSTPLANTCODE, DEPT.DEPTCODE, PLANT.INSTPLANTDES, TTKCG.TKCGCODE FROM FORECAST,TFIXER,PLANT LEFT OUTER JOIN DEPT ON DEPT.DEPTSYS = PLANT.CURDEPTSYS, TASK LEFT OUTER JOIN TTKCG ON TTKCG.TASKCODE = TASK.TASKCODE WHERE FORECAST.TASKCODE = TFIXER.TASKCODE AND TASK.TASKCODE = FORECAST.TASKCODE AND PLANT.INSTPLANTSYS = TASK.INSTPLANTSYS; CREATE VIEW JBPLVW AS SELECT JOB.JOBCODE, PLANT.INSTPLANTCODE, DEPT.DEPTCODE, JOB.JOBDES, JOB.SCHDATE, JOB.JSTATUSCODE, JOB.PRIORITY, JOB.MAINTTYPECODE, JOB.PLANNER, JOB.RESPEMPCODE, JOB.EXTJOBCODE, PLANT.INSTPLANTDES, NURDEF.URDEFSYS, WOSTR.RANKNO, WOSTR.PARENTCODE FROM JOB LEFT OUTER JOIN NURDEF ON NURDEF.JOBCODE = JOB.JOBCODE LEFT OUTER JOIN WOSTR ON JOB.TXTJOBCODE = WOSTR.CHILDCODE AND WOSTR.CHILDTYPE = 'J' JOIN (PLANT LEFT OUTER JOIN DEPT ON PLANT.CURDEPTSYS = DEPT.DEPTSYS) ON JOB.INSTPLANTSYS = PLANT.INSTPLANTSYS; CREATE VIEW JCIQTYVW AS SELECT DISTINCT JOBCODE, CATITEMCODE, CATITEMDES, INVFLAG, SUM(QTYREQD) AS ESTQTY FROM JCITEM GROUP BY JOBCODE,CATITEMCODE,CATITEMDES,INVFLAG; CREATE VIEW JBSPLVW AS SELECT JOB.JOBCODE, CATITEM.CATITEMCODE, JCIQTYVW.INVFLAG, PLANT.INSTPLANTCODE, DEPT.DEPTCODE, WOSTR.PARENTCODE, JOB.JOBDES, JOB.PRIORITY, JOB.SCHDATE, JOB.JSTATUSCODE, JOB.PLANNER, JOB.RESPEMPCODE, JOB.MAINTTYPECODE, JOB.TASKCODE, JCIQTYVW.CATITEMDES, JCIQTYVW.ESTQTY, PLANT.INSTPLANTDES, JTKCG.TKCGCODE FROM CATITEM,JCIQTYVW, JOB LEFT OUTER JOIN JTKCG ON JTKCG.JOBCODE = JOB.JOBCODE LEFT OUTER JOIN WOSTR ON JOB.TXTJOBCODE = WOSTR.CHILDCODE AND WOSTR.CHILDTYPE = 'J', PLANT LEFT OUTER JOIN DEPT ON PLANT.CURDEPTSYS = DEPT.DEPTSYS WHERE JOB.JOBCODE = JCIQTYVW.JOBCODE AND JCIQTYVW.CATITEMCODE = CATITEM.TXTCATITEMSYS AND JCIQTYVW.INVFLAG = 'Y' AND PLANT.INSTPLANTSYS = JOB.INSTPLANTSYS UNION SELECT JOB.JOBCODE, JCIQTYVW.CATITEMCODE, JCIQTYVW.INVFLAG, PLANT.INSTPLANTCODE, DEPT.DEPTCODE, WOSTR.PARENTCODE, JOB.JOBDES, JOB.PRIORITY, JOB.SCHDATE, JOB.JSTATUSCODE, JOB.PLANNER, JOB.RESPEMPCODE, JOB.MAINTTYPECODE, JOB.TASKCODE, JCIQTYVW.CATITEMDES, JCIQTYVW.ESTQTY, PLANT.INSTPLANTDES, JTKCG.TKCGCODE FROM JCIQTYVW, JOB LEFT OUTER JOIN JTKCG ON JTKCG.JOBCODE = JOB.JOBCODE LEFT OUTER JOIN WOSTR ON JOB.TXTJOBCODE = WOSTR.CHILDCODE AND WOSTR.CHILDTYPE = 'J', PLANT LEFT OUTER JOIN DEPT ON PLANT.CURDEPTSYS = DEPT.DEPTSYS WHERE JOB.JOBCODE = JCIQTYVW.JOBCODE AND JCIQTYVW.INVFLAG != 'Y' AND PLANT.INSTPLANTSYS = JOB.INSTPLANTSYS; CREATE VIEW MTRLOGVW AS SELECT METERLOG.INSTPLANTSYS, METERLOG.UOMCODE, PLANT.INSTPLANTCODE, PLANT.INSTPLANTDES, METERLOG.METERLOGID, METERLOG.DATEMEASURE, METERLOG.RESETFLAG, METERLOG.METERREADING, METERLOG.ACTUALREADING, METERLOG.JOBCODE, METERLOG.METERID FROM METERLOG,PLANT WHERE PLANT.INSTPLANTSYS = METERLOG.INSTPLANTSYS; CREATE VIEW FTCIVW AS SELECT FORECAST.TASKCODE, FORECAST.UOMCODE, FORECAST.ALARMVALUE, FORECAST.SCHDATE, CATITEM.CATITEMCODE, TCIQTYVW.INVFLAG, PLANT.INSTPLANTCODE, IPFAULT.FAULTCODE, FORECAST.PRIORITY, TASK.TASKDES, TCIQTYVW.CATITEMDES, TCIQTYVW.ESTQTY, TTKCG.TKCGCODE, PLANT.INSTPLANTDES, IPFAULT.CRITCODE, DEPT.DEPTCODE FROM FORECAST,CATITEM,TCIQTYVW,TFAULT,IPFAULT, PLANT LEFT OUTER JOIN DEPT ON DEPT.DEPTSYS = PLANT.CURDEPTSYS, TASK LEFT OUTER JOIN TTKCG ON TTKCG.TASKCODE = TASK.TASKCODE WHERE FORECAST.TASKCODE = TCIQTYVW.TASKCODE AND TASK.TASKCODE = FORECAST.TASKCODE AND FORECAST.FORECASTSTATUS IS NULL AND TCIQTYVW.CATITEMCODE = CATITEM.TXTCATITEMSYS AND TCIQTYVW.INVFLAG = 'Y' AND PLANT.INSTPLANTSYS = TASK.INSTPLANTSYS AND TFAULT.TASKCODE = TASK.TASKCODE AND IPFAULT.INSTPLANTSYS = PLANT.INSTPLANTSYS AND IPFAULT.FAULTCODE = TFAULT.FAULTCODE UNION SELECT FORECAST.TASKCODE, FORECAST.UOMCODE, FORECAST.ALARMVALUE, FORECAST.SCHDATE, TCIQTYVW.CATITEMCODE, TCIQTYVW.INVFLAG, PLANT.INSTPLANTCODE, IPFAULT.FAULTCODE, FORECAST.PRIORITY, TASK.TASKDES, TCIQTYVW.CATITEMDES, TCIQTYVW.ESTQTY, TTKCG.TKCGCODE, PLANT.INSTPLANTDES, IPFAULT.CRITCODE, DEPT.DEPTCODE FROM FORECAST,TCIQTYVW,TFAULT,IPFAULT, PLANT LEFT OUTER JOIN DEPT ON DEPT.DEPTSYS = PLANT.CURDEPTSYS, TASK LEFT OUTER JOIN TTKCG ON TTKCG.TASKCODE = TASK.TASKCODE WHERE FORECAST.TASKCODE = TCIQTYVW.TASKCODE AND TASK.TASKCODE = FORECAST.TASKCODE AND FORECAST.FORECASTSTATUS IS NULL AND TCIQTYVW.INVFLAG != 'Y' AND PLANT.INSTPLANTSYS = TASK.INSTPLANTSYS AND TFAULT.TASKCODE = TASK.TASKCODE AND IPFAULT.INSTPLANTSYS = PLANT.INSTPLANTSYS AND IPFAULT.FAULTCODE = TFAULT.FAULTCODE; CREATE VIEW JOBAWAITVW AS SELECT JOB.JOBCODE, JOB.SCHDATE, JOB.PRIORITY, JCITEM.WAREHSECODE, JCITEM.QTYREQD, CATITEM.CATITEMCODE, CIWH.QTYONHAND, CIWH.QTYALLOC FROM JOB,JSTATUS,JCITEM,CATITEM,CIWH WHERE JOB.JOBCODE = JCITEM.JOBCODE AND JOB.JSTATUSCODE = JSTATUS.JSTATUSCODE AND JSTATUS.JSTATUSNO < 3 AND JCITEM.INVFLAG = 'Y' AND JCITEM.CATITEMCODE = CATITEM.TXTCATITEMSYS AND CATITEM.CATITEMSYS = CIWH.CATITEMSYS AND JCITEM.WAREHSECODE = CIWH.WAREHSECODE; CREATE VIEW FOREDETVW AS SELECT FORECAST.TASKCODE, FORECAST.UOMCODE, FORECAST.ALARMVALUE, FORECAST.SCHDATE, FORECAST.PRIORITY, FORECAST.FORECASTSTATUS, FORECAST.JOBCODE, TASK.INSTPLANTSYS, TASK.TASKDES, PLANT.INSTPLANTCODE, PLANT.INSTPLANTDES, CURLOCCODE, DEPTCODE FROM FORECAST,TASK,PLANT LEFT OUTER JOIN DEPT ON PLANT.CURDEPTSYS = DEPT.DEPTSYS WHERE FORECAST.TASKCODE = TASK.TASKCODE AND TASK.INSTPLANTSYS = PLANT.INSTPLANTSYS; CREATE VIEW TCITEMALLVW AS SELECT TASKCODE, FAULTCODE, INVFLAG, TCITEM.CATITEMCODE, TCITEM.U_VERSION, TCITEM.CATITEMDES, QTYREQD, SEQ5, TCITEM.USERCREATE, TCITEM.DATECREATE, TCITEM.USERCHANGE, TCITEM.DATECHANGE, CATITEM.CATITEMCODE INVCATITEMCODE FROM TCITEM,CATITEM WHERE TCITEM.CATITEMCODE = CATITEM.TXTCATITEMSYS AND TCITEM.INVFLAG = 'Y' UNION SELECT TASKCODE, FAULTCODE, INVFLAG, TCITEM.CATITEMCODE, TCITEM.U_VERSION, TCITEM.CATITEMDES, QTYREQD, SEQ5, TCITEM.USERCREATE, TCITEM.DATECREATE, TCITEM.USERCHANGE, TCITEM.DATECHANGE, TCITEM.CATITEMCODE INVCATITEMCODE FROM TCITEM WHERE INVFLAG != 'Y'; CREATE VIEW IRTRANVW AS SELECT IRTRANID, IRTRAN.U_VERSION, TRANTYPE, DATETRAN, ACCTYEAR, ACCTMTH, CATITEMCODE, WAREHSECODE, BINCODE, IRTRAN.QTYISSUE, UNITCOST, COSTISSUE, JOBCODE, FAULTCODE, INSTPLANTCODE, RESCODE, ACCTCODE, IRTRAN.USERCREATE, IRTRAN.DATECREATE, IRTRAN.USERCHANGE, IRTRAN.DATECHANGE FROM IRTRAN,CATITEM,PLANT WHERE IRTRAN.CATITEMSYS = CATITEM.CATITEMSYS AND IRTRAN.INSTPLANTSYS = PLANT.INSTPLANTSYS; CREATE VIEW INSTVW AS SELECT PLANT.INSTPLANTSYS, PLANT.INSTPLANTCODE, PLANT.INSTPLANTDES, PLANT.SERIALNO, PLANT.CALDUEDATE, PLANT.INSTDATE, PLANT.SPARECHAR1 CUSTODIAN, PLANT.CURLOCCODE CUSTODLOC, IPATTR.ATTRVALUE REFNO, CATPLANT.CATPLANTCODE, CATPLANT.MODELNO, RESGRP.RESGRPCODE, RESGRP.RESGRPDES, CUSTODIAN.COMMAND, CUSTODIAN.SERVICE, CUSTODIAN.GEOGLOC, CUSTODIAN.DIVISION, CUSTODIAN.SECTION FROM PLANT LEFT OUTER JOIN IPATTR ON (IPATTR.INSTPLANTSYS = PLANT.INSTPLANTSYS AND IPATTR.SEQ5 = 1) JOIN CATPLANT LEFT OUTER JOIN CPMANUF ON CATPLANT.CATPLANTCODE = CPMANUF.CATPLANTCODE LEFT OUTER JOIN RESGRP ON CPMANUF.MANUFSYS = RESGRP.RESGRPSYS JOIN CUSTODIAN WHERE PLANT.CATPLANTCODE = CATPLANT.CATPLANTCODE; CREATE VIEW EQVW AS SELECT PLANT.INSTPLANTSYS, PLANT.INSTPLANTCODE, PLANT.SERIALNO, PLANT.CURLOCCODE, PLANT.SPARECHAR1, PLANT.CALDUEDATE, PLANT.PLANTSTATUS, CATPLANT.CATPLANTCODE, CATPLANT.CATPLANTDES, CATPLANT.MODELNO, RESGRP.RESGRPCODE, IPATTR1.ATTRVALUE REFNO, IPATTR2.ATTRVALUE BINLOC FROM PLANT LEFT OUTER JOIN IPATTR IPATTR1 ON IPATTR1.INSTPLANTSYS = PLANT.INSTPLANTSYS AND IPATTR1.SEQ5 = 1 AND IPATTR1.PLANTTYPE = 'ALL' LEFT OUTER JOIN IPATTR IPATTR2 ON IPATTR2.INSTPLANTSYS = PLANT.INSTPLANTSYS AND IPATTR2.SEQ5 = 2 AND IPATTR2.PLANTTYPE = 'ALL' JOIN CATPLANT ON PLANT.CATPLANTCODE = CATPLANT.CATPLANTCODE LEFT OUTER JOIN CPMANUF ON CATPLANT.CATPLANTCODE = CPMANUF.CATPLANTCODE LEFT OUTER JOIN RESGRP ON CPMANUF.MANUFSYS = RESGRP.RESGRPSYS; CREATE VIEW MAXRTNVW AS SELECT INSTPLANTSYS, MAX(SCHDATE) MAXSCHDATE FROM FORECAST,TASK WHERE FORECAST.TASKCODE = TASK.TASKCODE AND FORECASTSTATUS IS NULL GROUP BY INSTPLANTSYS; CREATE VIEW RETSELVW AS SELECT MAXRTNVW.INSTPLANTSYS, MAXRTNVW.MAXSCHDATE, PLANT.CURLOCCODE, PLANT.SPARECHAR1, CUSTODOWNER.CUSTODNAME OWNER, CUSTODLOC.CUSTODNAME LOCATION FROM MAXRTNVW,PLANT,CUSTODIAN CUSTODOWNER,CUSTODIAN CUSTODLOC WHERE PLANT.INSTPLANTSYS = MAXRTNVW.INSTPLANTSYS AND CUSTODOWNER.CUSTODCODE = PLANT.SPARECHAR1 AND CUSTODLOC.CUSTODCODE = PLANT.CURLOCCODE; CREATE VIEW RETLSTVW AS SELECT TASK.TASKCODE, TASK.INSTPLANTSYS, FORECAST.SCHDATE, FORECAST.UOMCODE, FORECAST.ALARMVALUE, PLANT.INSTPLANTCODE, PLANT.INSTPLANTDES, PLANT.SERIALNO, PLANT.SPARECHAR2, PLANT.CALDUEDATE, PLANT.SPAREDATE2, CATPLANT.MODELNO, CPMANUF.MANUFSYS, RESGRP.RESGRPCODE, RESGRP.RESGRPDES, FORECAST.FORECASTSTATUS, FORECAST.JOBCODE, CUSTODOWNER.CUSTODCODE OWNERCODE, CUSTODOWNER.CUSTODNAME OWNERNAME, CUSTODOWNER.COMMAND, CUSTODOWNER.DIVISION, CUSTODOWNER.GEOGLOC, CUSTODOWNER.SECTION, CUSTODOWNER.SERVICE, CUSTODLOC.CUSTODCODE LOCCODE, CUSTODLOC.CUSTODNAME LOCNAME FROM TASK JOIN FORECAST ON TASK.TASKCODE = FORECAST.TASKCODE JOIN PLANT ON TASK.INSTPLANTSYS = PLANT.INSTPLANTSYS JOIN CATPLANT ON PLANT.CATPLANTCODE = CATPLANT.CATPLANTCODE LEFT OUTER JOIN CPMANUF ON CATPLANT.CATPLANTCODE = CPMANUF.CATPLANTCODE LEFT OUTER JOIN RESGRP ON CPMANUF.MANUFSYS = RESGRP.RESGRPSYS JOIN CUSTODIAN CUSTODOWNER ON PLANT.SPARECHAR1 = CUSTODOWNER.CUSTODCODE JOIN CUSTODIAN CUSTODLOC ON PLANT.CURLOCCODE = CUSTODLOC.CUSTODCODE; CREATE VIEW PCSELVW AS SELECT PLANTCAL.INSTPLANTSYS, PLANTCAL.LOGID, PLANTCAL.RETURNDATE, PLANTCAL.REQDDATE, PLANTCAL.SUPPSYS, PLANTCAL.AGREEPRICE, PLANTCAL.STATUSFLAG, PLANT.INSTPLANTCODE, PLANT.INSTPLANTDES, PLANT.CATPLANTCODE, PLANT.CURLOCCODE, PLANT.SPARECHAR1, PLANT.SERIALNO, RESGRP.RESGRPDES, CUSTODOWNER.CUSTODCODE OWNERCODE, CUSTODOWNER.CUSTODNAME OWNERNAME, CUSTODLOC.CUSTODCODE LOCCODE, CUSTODLOC.CUSTODNAME LOCNAME FROM PLANTCAL,PLANT,CPMANUF,RESGRP,CUSTODIAN CUSTODOWNER,CUSTODIAN CUSTODLOC WHERE PLANT.INSTPLANTSYS = PLANTCAL.INSTPLANTSYS AND CPMANUF.CATPLANTCODE = PLANT.CATPLANTCODE AND RESGRP.RESGRPSYS = CPMANUF.MANUFSYS AND CUSTODOWNER.CUSTODCODE = PLANT.SPARECHAR1 AND CUSTODLOC.CUSTODCODE = PLANT.CURLOCCODE; CREATE VIEW FORSCHVW AS SELECT FORECAST.TASKCODE, FORECAST.UOMCODE, FORECAST.ALARMVALUE, FORECAST.SCHDATE, FORECAST.PRIORITY, FORECAST.FORECASTSTATUS, FORECAST.JOBCODE, TASK.INSTPLANTSYS, TASK.TASKDES, PLANT.INSTPLANTCODE, PLANT.INSTPLANTDES FROM FORECAST,TASK,PLANT WHERE FORECAST.TASKCODE = TASK.TASKCODE AND TASK.INSTPLANTSYS = PLANT.INSTPLANTSYS AND FORECASTSTATUS IS NULL; CREATE VIEW RIPVW AS SELECT PLANT.INSTPLANTSYS, PLANT.INSTPLANTCODE, PLANT.INSTPLANTDES, IPATTR.ATTRVALUE REFNO FROM PLANT,IPATTR WHERE IPATTR.INSTPLANTSYS = PLANT.INSTPLANTSYS AND IPATTR.ATTRCODE = 'REFNO' AND IPATTR.SEQ5 = 1; CREATE VIEW MODELVW AS SELECT PLANT.INSTPLANTSYS, PLANT.INSTPLANTCODE, PLANT.INSTPLANTDES, PLANT.SERIALNO, PLANT.INSTDATE, PLANT.CALDUEDATE, IPATTR.ATTRVALUE REFNO, CATPLANT.CATPLANTCODE, CATPLANT.CATPLANTDES, CATPLANT.MODELNO, RESGRP.RESGRPCODE, RESGRP.RESGRPDES, TASK.TASKCODE, TASK.TASKDES, TALARM.UOMCODE, TALARM.ALARMVALUE, TALARM.POSTFLAG, TALARM.LASTDONEDATE FROM PLANT LEFT OUTER JOIN IPATTR ON PLANT.INSTPLANTSYS = IPATTR.INSTPLANTSYS AND IPATTR.PLANTTYPE = 'ALL' AND IPATTR.SEQ5 = 1 LEFT OUTER JOIN TASK ON PLANT.INSTPLANTSYS = TASK.INSTPLANTSYS LEFT OUTER JOIN TALARM ON TASK.TASKCODE = TALARM.TASKCODE JOIN CATPLANT ON PLANT.CATPLANTCODE = CATPLANT.CATPLANTCODE LEFT OUTER JOIN CPMANUF ON CATPLANT.CATPLANTCODE = CPMANUF.CATPLANTCODE LEFT OUTER JOIN RESGRP ON CPMANUF.MANUFSYS = RESGRP.RESGRPSYS; CREATE VIEW LNLOGVW AS SELECT LNLOGHD.DISPATCHNO, LNLOGDT.LOANSYS, LNLOGHD.CUSTODLOC, LNLOGHD.TRANTYPE, LNLOGHD.SERVNUM, LNLOGHD.PRINTFLAG, LNLOGDT.RETURNDATE, LNLOGHD.MOVEDATE, LNLOGDT.INSTPLANTSYS, LNLOGDT.LOANER, LNLOGDT.DATECREATE, PLANT.INSTPLANTCODE FROM LNLOGHD,LNLOGDT,PLANT WHERE LNLOGHD.DISPATCHNO = LNLOGDT.DISPATCHNO AND LNLOGDT.INSTPLANTSYS = PLANT.INSTPLANTSYS; CREATE VIEW MODELTVW AS SELECT PLANT.INSTPLANTSYS, PLANT.INSTPLANTCODE, PLANT.INSTPLANTDES, PLANT.SERIALNO, PLANT.INSTDATE, PLANT.CALDUEDATE, CATPLANT.CATPLANTCODE, CATPLANT.CATPLANTDES, CATPLANT.MODELNO, RESGRP.RESGRPCODE, RESGRP.RESGRPDES, TASK.TASKCODE, TASK.TASKDES, TALARM.UOMCODE, TALARM.ALARMVALUE, TALARM.POSTFLAG, TALARM.LASTDONEDATE FROM PLANT JOIN CATPLANT ON PLANT.CATPLANTCODE = CATPLANT.CATPLANTCODE LEFT OUTER JOIN CPMANUF ON CATPLANT.CATPLANTCODE = CPMANUF.CATPLANTCODE LEFT OUTER JOIN RESGRP ON CPMANUF.MANUFSYS = RESGRP.RESGRPSYS AND RESGRP.RESTYPE = 'MANUF' LEFT OUTER JOIN TASK ON PLANT.INSTPLANTSYS = TASK.INSTPLANTSYS LEFT OUTER JOIN TALARM ON TASK.TASKCODE = TALARM.TASKCODE; CREATE VIEW CALFCVW AS SELECT PLANT.INSTPLANTSYS, PLANT.INSTPLANTCODE, PLANT.INSTPLANTDES, PLANT.SERIALNO, PLANT.CALDUEDATE, PLANT.SPAREDATE2 RECFORCAL, PLANT.CURLOCCODE, CATPLANT.CATPLANTCODE, MANUF.RESGRPCODE MANUFCODE, MANUF.RESGRPDES MANUFDESC, CALCEN.RESGRPCODE CALCODE, CALCEN.RESGRPDES CALDESC FROM PLANT,CATPLANT,CPMANUF,RESGRP MANUF,PLANTCAL,RESGRP CALCEN WHERE CATPLANT.CATPLANTCODE = PLANT.CATPLANTCODE AND CPMANUF.CATPLANTCODE = CATPLANT.CATPLANTCODE AND MANUF.RESGRPSYS = CPMANUF.MANUFSYS AND PLANTCAL.INSTPLANTSYS = PLANT.INSTPLANTSYS AND PLANTCAL.STATUSFLAG IS NULL AND PLANTCAL.JOBCODE IS NULL AND CALCEN.RESGRPSYS = PLANTCAL.SUPPSYS; CREATE VIEW MODELDVW AS SELECT CATPLANT.CATPLANTCODE, CATPLANT.CATPLANTDES, RESGRP.RESGRPDES MANUF, NSN.ATTRVALUE NSN, CPER.ATTRVALUE CALPERIOD, CPTEXT.TEXTCODE CALPROC, SCAT.ATTRVALUE SCAT FROM CATPLANT LEFT OUTER JOIN CPMANUF ON CATPLANT.CATPLANTCODE = CPMANUF.CATPLANTCODE LEFT OUTER JOIN RESGRP ON CPMANUF.MANUFSYS = RESGRP.RESGRPSYS LEFT OUTER JOIN CPATTR NSN ON CATPLANT.CATPLANTCODE = NSN.CATPLANTCODE AND NSN.PLANTTYPE = 'ALL' AND NSN.ATTRCODE = 'FSN' LEFT OUTER JOIN CPATTR CPER ON CATPLANT.CATPLANTCODE = CPER.CATPLANTCODE AND CPER.PLANTTYPE = 'ALL' AND CPER.ATTRCODE = 'CAL PERIOD' LEFT OUTER JOIN CPATTR SCAT ON CATPLANT.CATPLANTCODE = SCAT.CATPLANTCODE AND SCAT.PLANTTYPE = 'ALL' AND SCAT.ATTRCODE = 'SCAT' LEFT OUTER JOIN CPTEXT ON CATPLANT.CATPLANTCODE = CPTEXT.CATPLANTCODE AND CPTEXT.SEQ5 = 1; CREATE VIEW TOTOUTVW AS SELECT PLANT.CURLOCCODE, COUNT(INSTPLANTSYS) TOTOUTCAL FROM PLANT WHERE (PLANT.PLANTSTATUS = 'PCR' OR PLANT.PLANTSTATUS = 'LUS' OR PLANT.PLANTSTATUS = 'OLC') AND (PLANT.CALDUEDATE IS NOT NULL AND PLANT.CALDUEDATE < CURDATE()) GROUP BY PLANT.CURLOCCODE; CREATE VIEW CALSUMVW AS SELECT CUSTODIAN.CUSTODCODE, CUSTODIAN.DIVISION, CUSTODIAN.GEOGLOC, CUSTODIAN.SECTION, CUSTODIAN.SERVICE, CUSTODIAN.COMMAND, TOTOUTVW.TOTOUTCAL FROM CUSTODIAN LEFT OUTER JOIN TOTOUTVW ON TOTOUTVW.CURLOCCODE = CUSTODIAN.CUSTODCODE; CREATE VIEW TEINVVW AS SELECT PLANT.INSTPLANTSYS, PLANT.INSTPLANTCODE, PLANT.INSTPLANTDES, PLANT.SERIALNO, PLANT.SPARECHAR2, PLANT.CALDUEDATE, PLANT.SPAREDATE2, CATPLANT.MODELNO, CPMANUF.MANUFSYS, RESGRP.RESGRPCODE, RESGRP.RESGRPDES, CUSTODOWNER.CUSTODCODE OWNERCODE, CUSTODOWNER.CUSTODNAME OWNERNAME, CUSTODOWNER.COMMAND, CUSTODOWNER.DIVISION, CUSTODOWNER.GEOGLOC, CUSTODOWNER.SECTION, CUSTODOWNER.SERVICE, CUSTODLOC.CUSTODCODE LOCCODE, CUSTODLOC.CUSTODNAME LOCNAME, JSTATUS.JSTATUSDES STATUS FROM PLANT LEFT OUTER JOIN CUSTODIAN CUSTODOWNER ON PLANT.SPARECHAR1 = CUSTODOWNER.CUSTODCODE LEFT OUTER JOIN CUSTODIAN CUSTODLOC ON PLANT.CURLOCCODE = CUSTODLOC.CUSTODCODE JOIN CATPLANT ON PLANT.CATPLANTCODE = CATPLANT.CATPLANTCODE LEFT OUTER JOIN CPMANUF ON CATPLANT.CATPLANTCODE = CPMANUF.CATPLANTCODE LEFT OUTER JOIN RESGRP ON CPMANUF.MANUFSYS = RESGRP.RESGRPSYS LEFT OUTER JOIN PLANTCAL ON PLANT.INSTPLANTSYS = PLANTCAL.INSTPLANTSYS AND PLANTCAL.STATUSFLAG IS NULL LEFT OUTER JOIN JSTATUS ON PLANTCAL.JSTATUSCODE = JSTATUS.JSTATUSCODE; -- SET SCHEMA USER; -- *** SEQUENCES *** -- *** EVENTS *** -- *** PROCEDURES *** COMMIT WORK;