SQLize | PHPize | SQLtest

-- Create table create table ORDERS ( ord_id NUMBER(12) not null, ord_ord_id NUMBER(12), ord_carl_index VARCHAR2(20 CHAR) not null, ord_adef_id NUMBER(12) not null, ord_orst_index VARCHAR2(20 CHAR) not null, ord_sect_index VARCHAR2(16 CHAR), ord_plnt_index VARCHAR2(4 CHAR), ord_cl_index VARCHAR2(3 CHAR) not null, ord_woce_index VARCHAR2(38 CHAR), ord_nr VARCHAR2(30 CHAR), ord_qtyplanned NUMBER(9), ord_qtyreleased NUMBER(9), ord_qtyproduced NUMBER(9), ord_qtyscrapped NUMBER(9), ord_qtyadded NUMBER(9), ord_targetstartdate TIMESTAMP(6), ord_grpnr VARCHAR2(8 CHAR), ord_productnr VARCHAR2(22 CHAR), ord_lotnr VARCHAR2(2 CHAR), ord_serialnr NUMBER(6), ord_ppsident VARCHAR2(30 CHAR), ord_planninggrpnr VARCHAR2(12 CHAR), ord_grpseqnr VARCHAR2(3 CHAR), ord_targettime FLOAT, ord_setuptime FLOAT, ord_sublotsize NUMBER(9), ord_customer VARCHAR2(7 CHAR), ord_steeringtype VARCHAR2(2 CHAR), ord_sublotnr VARCHAR2(3 CHAR), ord_lastmoveoutdate TIMESTAMP(6), ord_statusdate TIMESTAMP(6), ord_releasedate TIMESTAMP(6), ord_releasesequence NUMBER(12), ord_productionarea VARCHAR2(1 CHAR), ord_cuttingplant VARCHAR2(3 CHAR), ord_orderlock VARCHAR2(1 CHAR), ord_lockreason VARCHAR2(100 CHAR), ord_articlelock VARCHAR2(1 CHAR), ord_nrofarticleslocked NUMBER(3), ord_plannedsector VARCHAR2(16 CHAR), ord_batchnr VARCHAR2(20 CHAR), ord_lotsize NUMBER(9), ord_plannedsequence NUMBER(12), ord_priority NUMBER(2), ord_dtype VARCHAR2(31 CHAR) not null, ord_alteredgrpnr VARCHAR2(8 CHAR), ord_orst_index_max VARCHAR2(20 CHAR), ord_adef_adef_id NUMBER(12), ord_cableavailable VARCHAR2(1 CHAR), ord_materialavailable VARCHAR2(1 CHAR), ord_isrebuildorder VARCHAR2(1 CHAR), ord_originproductnr VARCHAR2(22 CHAR), ord_ord_id_origin NUMBER(12), ord_ord_id_mainorder NUMBER(12), ord_creationdate TIMESTAMP(6) default systimestamp not null, ord_archivedate TIMESTAMP(6), ord_rebuildcomment VARCHAR2(100 CHAR), ord_originbatchnr VARCHAR2(20 CHAR), ord_preassignmentcomment VARCHAR2(100 CHAR), ord_ipstorderid NUMBER(12), ord_linenr NUMBER(3), ord_lastworkcenter VARCHAR2(38 CHAR), ord_stgr_index VARCHAR2(22 CHAR), ord_dpg VARCHAR2(18 CHAR), ord_cablenr VARCHAR2(18 CHAR), ord_barcodecablecuttingarea VARCHAR2(18 CHAR), ord_runningdate TIMESTAMP(6), ord_cableavailabledate TIMESTAMP(6), ord_buildindex VARCHAR2(3 CHAR), ord_shippingnote VARCHAR2(11 CHAR), ord_bookingnr VARCHAR2(10 CHAR), ord_qualitylock VARCHAR2(1 CHAR) default 'N' not null, ord_goodsreceiptdate TIMESTAMP(6), ord_bestbefore TIMESTAMP(6), ord_producedonsite VARCHAR2(1 CHAR) default 'N' not null, ord_customerbatchnr VARCHAR2(200 CHAR), ord_vety_index VARCHAR2(4 CHAR), ord_linepositionnr NUMBER(3), ord_completeddate TIMESTAMP(6), ord_carl_index_jis VARCHAR2(20 CHAR), ord_orpr_ordertype VARCHAR2(2 CHAR), ord_orpr_callofftype VARCHAR2(6 CHAR), ord_producttype VARCHAR2(1 CHAR), ord_changelevel NUMBER(2), ord_exemplarnr NUMBER(2), ord_productionflag VARCHAR2(1 CHAR), ord_productionstep NUMBER(2), ord_productionway NUMBER(4), ord_valid VARCHAR2(1 CHAR) default 'Y' not null, ord_date1 TIMESTAMP(6), ord_date2 TIMESTAMP(6), ord_date3 TIMESTAMP(6), ord_date4 TIMESTAMP(6), ord_date5 TIMESTAMP(6), ord_date6 TIMESTAMP(6), ord_ppscreationmonth VARCHAR2(6 CHAR), ord_ppsderivate VARCHAR2(20 CHAR), ord_variantnr NUMBER(9), ord_comment VARCHAR2(100 CHAR), ord_customerordernr VARCHAR2(20 CHAR), ord_customercalloffnr VARCHAR2(10 CHAR), ord_deliverysequence NUMBER(12), ord_cyclenr NUMBER(9), ord_shiftnr VARCHAR2(12 CHAR), ord_productionyear VARCHAR2(2 CHAR), ord_modelyear VARCHAR2(4 CHAR), ord_vehicleidentification VARCHAR2(40 CHAR), ord_use_index VARCHAR2(8 CHAR), ord_unloadingplant VARCHAR2(3 CHAR), ord_unloadingpoint VARCHAR2(14 CHAR), ord_printident NUMBER(6), ord_assemblyplant VARCHAR2(3 CHAR), ord_wftv_wfty_id NUMBER(12), ord_wftv_version NUMBER(3), ord_preassemblyrequired VARCHAR2(1 CHAR), ord_dede_id NUMBER(12), ord_reorderid NUMBER(12), ord_labelsequence NUMBER(12), ord_wsdename VARCHAR2(20 CHAR), ord_cuttingmachine VARCHAR2(8 CHAR), ord_cuttingdate TIMESTAMP(6), ord_wc_id NUMBER(12), ord_wstpsequence NUMBER(3), ord_woexresult VARCHAR2(40 CHAR), ord_reservedfor VARCHAR2(20 CHAR), ord_scrapcode VARCHAR2(8 CHAR), ord_wcty_index_target VARCHAR2(30 CHAR), ord_qtyreordered NUMBER(9), ord_med_id NUMBER(12), ord_linenractual NUMBER(3), ord_pin_id_used NUMBER(12), ord_targetenddate TIMESTAMP(6), ord_otyp_index VARCHAR2(4 CHAR), ord_sapprodshorttext VARCHAR2(40 CHAR), ord_persontime FLOAT, ord_machinetime FLOAT, ord_personsetuptime FLOAT, ord_machinesetuptime FLOAT, ord_medident VARCHAR2(30 CHAR), ord_sapwarehousenr VARCHAR2(3 CHAR), ord_overproductionfinished VARCHAR2(1 CHAR), ord_dailycountersequence NUMBER(9), ord_dailycounterdate TIMESTAMP(6), ord_workplanversion NUMBER(4), ord_wcp_id NUMBER(12), ord_lastwoexid NUMBER(12), ord_alternativeident VARCHAR2(200 CHAR), ord_fazit VARCHAR2(23 CHAR), ord_comp_med_id NUMBER(12), ord_comp_nr NUMBER(5), ord_ppsorderlogic VARCHAR2(3 CHAR), ord_targetlocation VARCHAR2(20 CHAR), ord_salesordernr VARCHAR2(10 CHAR), ord_lastpersonnel VARCHAR2(40 CHAR), ord_tasklistgroup VARCHAR2(8 CHAR), ord_groupcounter VARCHAR2(2 CHAR), ord_bomid VARCHAR2(8 CHAR), ord_bomusage VARCHAR2(1 CHAR), ord_bomalternative VARCHAR2(2 CHAR), ord_plannergroup VARCHAR2(3 CHAR), ord_superordinateordnr VARCHAR2(12 CHAR), ord_leadingordnr VARCHAR2(12 CHAR) ) tablespace MES_DAT pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); -- Add comments to the columns comment on column ORDERS.ord_id is 'UID of the order'; comment on column ORDERS.ord_ord_id is 'UID of the order'; comment on column ORDERS.ord_carl_index is 'UID of the carline'; comment on column ORDERS.ord_adef_id is 'The UID of articledefinition'; comment on column ORDERS.ord_orst_index is 'UID of the order status. e.g. ''CREATED'', ''COMPLETED'' or ''CANCELED'' etc.'; comment on column ORDERS.ord_sect_index is 'UID of sector'; comment on column ORDERS.ord_plnt_index is 'UID of the plant e.g. C9= Balti/Moldavia'; comment on column ORDERS.ord_cl_index is 'Index of Client'; comment on column ORDERS.ord_woce_index is 'UID of the workcenter'; comment on column ORDERS.ord_nr is 'Number of the order for identification'; comment on column ORDERS.ord_qtyplanned is 'Planned quantity. It is determined by the leading ERP-system (Avas, Host)'; comment on column ORDERS.ord_qtyreleased is 'Released quantity. It is increased when a part of the order gets released'; comment on column ORDERS.ord_qtyproduced is 'Produced quantity. It is increased if all worksteps of a part of the order are executed'; comment on column ORDERS.ord_qtyscrapped is 'Scrapped quantity. If a part of the order gets damaged, then this counter is increased.'; comment on column ORDERS.ord_qtyadded is 'Added quantity. It is increased for overproduction or scrap'; comment on column ORDERS.ord_targetstartdate is 'The date when the production of the order shall be started'; comment on column ORDERS.ord_grpnr is 'Group number. It is only relevant at series production and is determined by the leading ERP-system (Host).'; comment on column ORDERS.ord_productnr is 'The complete drx number of this Product'; comment on column ORDERS.ord_lotnr is 'Lot number of the order at series production'; comment on column ORDERS.ord_serialnr is 'Serial number of the order at series production'; comment on column ORDERS.ord_ppsident is 'Unique identification number of the order at the leading PPS-System'; comment on column ORDERS.ord_planninggrpnr is 'Planned Group Nummer'; comment on column ORDERS.ord_grpseqnr is 'The sequence nummer within the group'; comment on column ORDERS.ord_targettime is 'The target time per piece in 1/100 minutes.'; comment on column ORDERS.ord_setuptime is 'The setup time for tools and machines in 1/100 minutes.'; comment on column ORDERS.ord_sublotsize is 'The size of a Sublot'; comment on column ORDERS.ord_customer is 'The customer number'; comment on column ORDERS.ord_steeringtype is 'The steering type of the order: RL, LL or AL'; comment on column ORDERS.ord_sublotnr is 'The number of the sublot e.g. 01A'; comment on column ORDERS.ord_lastmoveoutdate is 'The time when the last moveout take place'; comment on column ORDERS.ord_statusdate is 'The time the of the last change of the orderstatus'; comment on column ORDERS.ord_releasedate is 'The time when the order was released'; comment on column ORDERS.ord_releasesequence is 'Contains the overall sequence of release'; comment on column ORDERS.ord_productionarea is 'The area of the production e.g. 0, 1, 2'; comment on column ORDERS.ord_cuttingplant is 'The number of the cutting plant'; comment on column ORDERS.ord_orderlock is 'Indicates if this order is locked'; comment on column ORDERS.ord_lockreason is 'The reason for the order lock'; comment on column ORDERS.ord_articlelock is 'Indicates if this order is locked due to its articles'; comment on column ORDERS.ord_nrofarticleslocked is 'Tee number of articles belonnging to this order that are locked'; comment on column ORDERS.ord_plannedsector is 'The planned sector of the PPS system'; comment on column ORDERS.ord_batchnr is 'Lot number of the order at series production'; comment on column ORDERS.ord_lotsize is 'The size of the lot'; comment on column ORDERS.ord_plannedsequence is 'The planned sequence for the production of the order'; comment on column ORDERS.ord_priority is 'The priority of the order for the production'; comment on column ORDERS.ord_dtype is 'Discriminator attribute'; comment on column ORDERS.ord_alteredgrpnr is 'If this value is set, then it is valid as working productiongroup-nr'; comment on column ORDERS.ord_orst_index_max is 'UID of the order status. e.g. ''CREATED'', ''COMPLETED'' or ''CANCELED'' etc.'; comment on column ORDERS.ord_adef_adef_id is 'The UID of articledefinition'; comment on column ORDERS.ord_cableavailable is 'Y/N-Flag that indicates if the cables of the order are available in the staging area.'; comment on column ORDERS.ord_materialavailable is 'Y/N-Flag that indicates if the materials of the order are available in the staging area.'; comment on column ORDERS.ord_isrebuildorder is 'Y/N-Flag that indicates if this order is a rebuild order (9th digit of the FPNr="U")'; comment on column ORDERS.ord_originproductnr is 'Contains the old product nr if the order was modified by a rebuild order'; comment on column ORDERS.ord_ord_id_origin is 'UID of the origin order if this is a rebuild order'; comment on column ORDERS.ord_ord_id_mainorder is 'UID of the order'; comment on column ORDERS.ord_creationdate is 'The time when the entry was created'; comment on column ORDERS.ord_archivedate is 'The time when the order was archived'; comment on column ORDERS.ord_rebuildcomment is 'A comment about the rebuild'; comment on column ORDERS.ord_originbatchnr is 'Contains the old batch nr if the order was rebuilt'; comment on column ORDERS.ord_preassignmentcomment is 'A comment for the pre assignment of the order'; comment on column ORDERS.ord_ipstorderid is 'The reference OrdId of IPST-STO or IPST-MTO-I'; comment on column ORDERS.ord_linenr is 'The assembly line where the workorder is intended to be produced.'; comment on column ORDERS.ord_lastworkcenter is 'The last workcenter that made a booking on this order'; comment on column ORDERS.ord_stgr_index is 'The UID of structuralgroup'; comment on column ORDERS.ord_runningdate is 'Timestamp when the Orderstatus was changed from RELEASED to RUNNING'; comment on column ORDERS.ord_cableavailabledate is 'Timestamp when the CABLEAVAILABLE-Flag has been last changed'; comment on column ORDERS.ord_buildindex is 'The Build Index (Bauteil-Index)'; comment on column ORDERS.ord_shippingnote is 'The Shipping Note (Lieferscheinnummer)'; comment on column ORDERS.ord_bookingnr is 'The Bookung Nr (Buchungsnummer)'; comment on column ORDERS.ord_qualitylock is 'The Quality Lock Flag'; comment on column ORDERS.ord_goodsreceiptdate is 'The Goods Receipt Date (Wareneingangsdatum)'; comment on column ORDERS.ord_bestbefore is 'The Best Before (Mindesthaltbarkeitsdatum)'; comment on column ORDERS.ord_producedonsite is 'Indicates if the part was produced on-site or booked in as booking order from PPS-System (HOST)'; comment on column ORDERS.ord_customerbatchnr is 'The customer batchnr'; comment on column ORDERS.ord_vety_index is 'UID of vehicletypes'; comment on column ORDERS.ord_linepositionnr is 'The position number on the assembly line'; comment on column ORDERS.ord_completeddate is 'The time when the entry was completed'; comment on column ORDERS.ord_carl_index_jis is 'UID of the carline'; comment on column ORDERS.ord_orpr_ordertype is 'The Ordertype (e.g. K, N, V, C, E, D, ...)'; comment on column ORDERS.ord_orpr_callofftype is 'Calltype of the order: PROD, NOTF'; comment on column ORDERS.ord_producttype is 'The product type (e.g. K for KSK, T for TVKL, M for MIKO) '; comment on column ORDERS.ord_changelevel is 'Change level of the AVAS order. It is determined by the leading erp-system (AVAS, HOST)'; comment on column ORDERS.ord_exemplarnr is 'Changelevel of the workorder'; comment on column ORDERS.ord_productionflag is 'The production flag for this workorder (e.g. for STO: P/N)'; comment on column ORDERS.ord_productionstep is 'The production step'; comment on column ORDERS.ord_productionway is 'The production way'; comment on column ORDERS.ord_valid is 'Valid-flag (Y/N)'; comment on column ORDERS.ord_date1 is 'Date column 1'; comment on column ORDERS.ord_date2 is 'Date column 2'; comment on column ORDERS.ord_date3 is 'Date column 3'; comment on column ORDERS.ord_date4 is 'Date column 4'; comment on column ORDERS.ord_date5 is 'Date column 5'; comment on column ORDERS.ord_date6 is 'Date column 6'; comment on column ORDERS.ord_ppscreationmonth is 'Creation month of the order on PPS-System'; comment on column ORDERS.ord_ppsderivate is 'PPS derivate of an order, e.g. W205AMG'; comment on column ORDERS.ord_variantnr is 'Variantnr'; comment on column ORDERS.ord_comment is 'Comment about this order'; comment on column ORDERS.ord_customerordernr is 'Full ordernr as it is used by the customer'; comment on column ORDERS.ord_customercalloffnr is 'CallOffNr. from the customer (ger. "Sendungsabrufnummer" for B10 Label)'; comment on column ORDERS.ord_deliverysequence is 'The non-cycle deliverysequence built by the PPS-System'; comment on column ORDERS.ord_cyclenr is 'The cycle number of this order'; comment on column ORDERS.ord_shiftnr is 'Original Shiftnr. from the customer.'; comment on column ORDERS.ord_productionyear is 'The production year from the customer'; comment on column ORDERS.ord_modelyear is 'The model year'; comment on column ORDERS.ord_vehicleidentification is 'Vehicleidentification (VIN-Nr.)'; comment on column ORDERS.ord_use_index is 'Uselocation'; comment on column ORDERS.ord_unloadingplant is 'Unloading Plant'; comment on column ORDERS.ord_unloadingpoint is 'Unloading Point'; comment on column ORDERS.ord_printident is 'Identification (number) for printed reports'; comment on column ORDERS.ord_assemblyplant is 'The number of the assembly plant'; comment on column ORDERS.ord_wftv_wfty_id is 'UID of Workflowtype'; comment on column ORDERS.ord_wftv_version is 'UID of Workflowtypeversion'; comment on column ORDERS.ord_preassemblyrequired is 'Indicates if preassembly is required for the order'; comment on column ORDERS.ord_dede_id is 'UID of derivativedefinition'; comment on column ORDERS.ord_reorderid is 'Unique Id from the Reordering system'; comment on column ORDERS.ord_labelsequence is 'The (rolling) Sequence that is printed on the Label'; comment on column ORDERS.ord_wsdename is 'Name of the last successful executed Workstep'; comment on column ORDERS.ord_cuttingmachine is 'The name of the KSInfo Cutting Machine'; comment on column ORDERS.ord_cuttingdate is 'The date when the cable was cutted'; comment on column ORDERS.ord_wc_id is 'UID of Workflowcontrol'; comment on column ORDERS.ord_wstpsequence is 'Contains the last Wstp_Sequence of last OK-Executed Workstep'; comment on column ORDERS.ord_woexresult is 'The Woex_Result value of the Workstepexecution of the last MoveOut'; comment on column ORDERS.ord_reservedfor is 'Identification of the person/workstation the order is reserved for'; comment on column ORDERS.ord_scrapcode is 'The scrapcode'; comment on column ORDERS.ord_wcty_index_target is 'UID of workcentertype'; comment on column ORDERS.ord_qtyreordered is 'Reordered quantity'; comment on column ORDERS.ord_med_id is 'Id of Medium'; comment on column ORDERS.ord_linenractual is 'The assembly line where the workorder is actually produced.'; comment on column ORDERS.ord_pin_id_used is 'UID of Packinginstructions'; comment on column ORDERS.ord_targetenddate is 'The date when the production of the order shall be finished'; comment on column ORDERS.ord_otyp_index is 'UID of Ordertypes'; comment on column ORDERS.ord_sapprodshorttext is 'Classification of the Order(-article)'; comment on column ORDERS.ord_persontime is 'The person time for the complete order quantity in minutes.'; comment on column ORDERS.ord_machinetime is 'The machine time for this detailworkstep in 1/100 minutes.'; comment on column ORDERS.ord_personsetuptime is 'The person setup time for this order in minutes.'; comment on column ORDERS.ord_machinesetuptime is 'The machine setup time for this order in minutes.'; comment on column ORDERS.ord_medident is 'Identification of this medium (1:1 copy of MED_IDENT)'; comment on column ORDERS.ord_sapwarehousenr is 'The destination Warehouse Nr on the SAP System'; comment on column ORDERS.ord_overproductionfinished is 'Y/N-Flag that indicates if the overproduction is finished.'; comment on column ORDERS.ord_dailycountersequence is 'The sequence Number of a Daily Counter for Orders'; comment on column ORDERS.ord_dailycounterdate is 'The Date of a Daily Counter for Orders'; comment on column ORDERS.ord_workplanversion is 'The vesrion nr of the workplan'; comment on column ORDERS.ord_wcp_id is 'UID if Workflowcontrolpos'; comment on column ORDERS.ord_lastwoexid is 'Last workstepexicution of this order'; comment on column ORDERS.ord_alternativeident is 'Alternative Identifier of Order (e.g. filled by GB/T-Code, Singlepartbarcode, etc.) - if used it should be unique to be possible to identiyfy Order by this attribute'; comment on column ORDERS.ord_fazit is 'Fazitstring of Order'; comment on column ORDERS.ord_comp_med_id is 'Id of Medium'; comment on column ORDERS.ord_comp_nr is 'nr of the compartment in the medium'; comment on column ORDERS.ord_ppsorderlogic is 'The type or the logic of the order in the PPS. e.g. MTO=makte-to-order or MTS=make-to-stock'; comment on column ORDERS.ord_targetlocation is 'The target location of a reorder'; comment on column ORDERS.ord_salesordernr is 'JIS Production Sales Order Nr. in SAP'; comment on column ORDERS.ord_lastpersonnel is 'The last personnel who made a booking on this order'; comment on column ORDERS.ord_tasklistgroup is 'Task List Group'; comment on column ORDERS.ord_groupcounter is 'Group Counter'; comment on column ORDERS.ord_bomid is 'BOM ID'; comment on column ORDERS.ord_bomusage is 'BOM Usage'; comment on column ORDERS.ord_bomalternative is 'BOM Alternative'; comment on column ORDERS.ord_plannergroup is 'Responsible plannergroup/former productionarea'; comment on column ORDERS.ord_superordinateordnr is '(SOBSL52) the ordernr of the superordinate order'; comment on column ORDERS.ord_leadingordnr is '(SOBSL52) the ordernr of the leading order'; -- Create/Recreate indexes create index ORD_ADEF_ADEF_FK_I on ORDERS (ORD_ADEF_ADEF_ID) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 64K minextents 1 maxextents unlimited ) compress; create index ORD_ADEF_FK_I on ORDERS (ORD_ADEF_ID) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 64K minextents 1 maxextents unlimited ) compress; create index ORD_ASSIGNMENT_I on ORDERS (ORD_WOCE_INDEX, ORD_ORST_INDEX, ORD_DTYPE) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 64K minextents 1 maxextents unlimited ) compress; create index ORD_BARCODECABLECUTTINGAREA_I on ORDERS (ORD_BARCODECABLECUTTINGAREA) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 64K minextents 1 maxextents unlimited ); create index ORD_BATCHNR_I on ORDERS (ORD_BATCHNR) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 64K minextents 1 maxextents unlimited ) compress; create index ORD_CARL_FK_I on ORDERS (ORD_CARL_INDEX) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 64K minextents 1 maxextents unlimited ) compress; create index ORD_CARL_JIS_FK_I on ORDERS (ORD_CARL_INDEX_JIS) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 64K minextents 1 maxextents unlimited ); create index ORD_CL_FK_I on ORDERS (ORD_CL_INDEX) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 64K minextents 1 maxextents unlimited ) compress; create index ORD_COMP_FK_I on ORDERS (ORD_COMP_MED_ID, ORD_COMP_NR) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); create index ORD_CUSTOMERBATCHNR_I on ORDERS (ORD_CUSTOMERBATCHNR) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); create index ORD_DEDE_FK_I on ORDERS (ORD_DEDE_ID) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 64K minextents 1 maxextents unlimited ) compress; create index ORD_DTYPE_I on ORDERS (ORD_DTYPE) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 64K minextents 1 maxextents unlimited ) compress; create index ORD_IDENT_I on ORDERS (ORD_CL_INDEX, ORD_DTYPE, ORD_GRPNR, ORD_SUBLOTNR, ORD_DPG, ORD_CABLENR, ORD_BATCHNR) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ) compress; create index ORD_LASTWOEXID_I on ORDERS (ORD_LASTWOEXID) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); create index ORD_LORE_FK_I on ORDERS (ORD_LOCKREASON) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 64K minextents 1 maxextents unlimited ) compress; create index ORD_LOTNR_I on ORDERS (ORD_LOTNR) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 64K minextents 1 maxextents unlimited ) compress; create index ORD_MED_FK_I on ORDERS (ORD_MED_ID) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); create index ORD_MTOE_I on ORDERS (ORD_DTYPE, ORD_GRPNR, ORD_PRODUCTNR, ORD_BATCHNR, ORD_SERIALNR) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 64K minextents 1 maxextents unlimited ) compress 4; create index ORD_NR_I on ORDERS (ORD_NR) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 64K minextents 1 maxextents unlimited ) compress; create index ORD_ORD_FK_I on ORDERS (ORD_ORD_ID) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 64K minextents 1 maxextents unlimited ) compress; create index ORD_ORD_MAINORDER_FK_I on ORDERS (ORD_ORD_ID_MAINORDER) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 64K minextents 1 maxextents unlimited ) compress; create index ORD_ORD_ORIGIN_FK_I on ORDERS (ORD_ORD_ID_ORIGIN) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 64K minextents 1 maxextents unlimited ); create index ORD_ORPR_FK_I on ORDERS (ORD_ORPR_ORDERTYPE, ORD_ORPR_CALLOFFTYPE) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 64K minextents 1 maxextents unlimited ); create index ORD_ORST_FK_I on ORDERS (ORD_ORST_INDEX) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 64K minextents 1 maxextents unlimited ) compress; create index ORD_ORST_MAX_FK_I on ORDERS (ORD_ORST_INDEX_MAX) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 64K minextents 1 maxextents unlimited ) compress; create index ORD_OTYP_FK_I on ORDERS (ORD_OTYP_INDEX) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); create index ORD_PIN_USED_FK_I on ORDERS (ORD_PIN_ID_USED) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); create index ORD_PLNT_FK_I on ORDERS (ORD_PLNT_INDEX) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 64K minextents 1 maxextents unlimited ) compress; create index ORD_PPSIDENT_I on ORDERS (ORD_PPSIDENT) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 64K minextents 1 maxextents unlimited ) compress; create index ORD_PRGR_FK_I on ORDERS (ORD_GRPNR) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 64K minextents 1 maxextents unlimited ) compress; create index ORD_PRODUCTNR_I on ORDERS (ORD_PRODUCTNR) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 64K minextents 1 maxextents unlimited ) compress; create index ORD_SECT_FK_I on ORDERS (ORD_SECT_INDEX) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 64K minextents 1 maxextents unlimited ) compress; create index ORD_SECT_PLANNED_FK_I on ORDERS (ORD_PLANNEDSECTOR) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 64K minextents 1 maxextents unlimited ) compress; create unique index ORD_SINGLEPART_UK on ORDERS (CASE WHEN (ORD_NR IS NOT NULL AND ORD_SERIALNR IS NOT NULL) THEN ORD_NR END, CASE WHEN (ORD_NR IS NOT NULL AND ORD_SERIALNR IS NOT NULL) THEN ORD_PRODUCTNR END, CASE WHEN (ORD_NR IS NOT NULL AND ORD_SERIALNR IS NOT NULL) THEN ORD_BATCHNR END, CASE WHEN (ORD_NR IS NOT NULL AND ORD_SERIALNR IS NOT NULL) THEN ORD_SERIALNR END) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); create index ORD_STATUS_SINGLEPART_I on ORDERS (ORD_ORST_INDEX, ORD_ORD_ID) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ) compress; create index ORD_STGR_FK_I on ORDERS (ORD_STGR_INDEX) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 64K minextents 1 maxextents unlimited ); create index ORD_SUBLOTNR_I on ORDERS (ORD_SUBLOTNR) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 64K minextents 1 maxextents unlimited ) compress; create index ORD_USE_FK_I on ORDERS (ORD_USE_INDEX) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 64K minextents 1 maxextents unlimited ); create index ORD_VETY_FK_I on ORDERS (ORD_VETY_INDEX) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 64K minextents 1 maxextents unlimited ) compress; create index ORD_WCP_FK_I on ORDERS (ORD_WCP_ID) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); create index ORD_WCTY_TARGET_FK_I on ORDERS (ORD_WCTY_INDEX_TARGET) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); create index ORD_WC_FK_I on ORDERS (ORD_WC_ID) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 64K minextents 1 maxextents unlimited ); create index ORD_WFTV_FK_I on ORDERS (ORD_WFTV_WFTY_ID, ORD_WFTV_VERSION) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 64K minextents 1 maxextents unlimited ) compress; create index ORD_WOCE_FK_I on ORDERS (ORD_WOCE_INDEX) tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 64K minextents 1 maxextents unlimited ) compress; -- Create/Recreate primary, unique and foreign key constraints alter table ORDERS add constraint ORD_PK primary key (ORD_ID) using index tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 64K minextents 1 maxextents unlimited ); alter table ORDERS add constraint ORD_IPSTORDERID_UK unique (ORD_IPSTORDERID) using index tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 64K minextents 1 maxextents unlimited ); alter table ORDERS add constraint ORD_ORD2_UK unique (ORD_ALTERNATIVEIDENT) using index tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); alter table ORDERS add constraint ORD_ORD3_UK unique (ORD_FAZIT) using index tablespace MES_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); alter table ORDERS add constraint ORD_ADEF_ADEF_FK foreign key (ORD_ADEF_ADEF_ID) references ARTICLEDEFINITIONS (ADEF_ID) deferrable; alter table ORDERS add constraint ORD_ADEF_FK foreign key (ORD_ADEF_ID) references ARTICLEDEFINITIONS (ADEF_ID) deferrable; alter table ORDERS add constraint ORD_CARL_FK foreign key (ORD_CARL_INDEX) references CARLINES (CARL_INDEX) deferrable; alter table ORDERS add constraint ORD_CARL_JIS_FK foreign key (ORD_CARL_INDEX_JIS) references CARLINES (CARL_INDEX) deferrable; alter table ORDERS add constraint ORD_CL_FK foreign key (ORD_CL_INDEX) references CLIENTS (CL_INDEX) deferrable; alter table ORDERS add constraint ORD_COMP_FK foreign key (ORD_COMP_MED_ID, ORD_COMP_NR) references COMPARTMENTS (COMP_MED_ID, COMP_NR) on delete set null deferrable; alter table ORDERS add constraint ORD_DEDE_FK foreign key (ORD_DEDE_ID) references DERIVATIVEDEFINITIONS (DEDE_ID) deferrable; alter table ORDERS add constraint ORD_MED_FK foreign key (ORD_MED_ID) references MEDIA (MED_ID) deferrable; alter table ORDERS add constraint ORD_ORD_FK foreign key (ORD_ORD_ID) references ORDERS (ORD_ID) deferrable; alter table ORDERS add constraint ORD_ORD_MAINORDER_FK foreign key (ORD_ORD_ID_MAINORDER) references ORDERS (ORD_ID) deferrable; alter table ORDERS add constraint ORD_ORPR_FK foreign key (ORD_ORPR_ORDERTYPE, ORD_ORPR_CALLOFFTYPE) references ORDERPRIORITIES (ORPR_ORDERTYPE, ORPR_CALLOFFTYPE) deferrable; alter table ORDERS add constraint ORD_ORST_FK foreign key (ORD_ORST_INDEX) references ORDERSTATUS (ORST_INDEX) deferrable; alter table ORDERS add constraint ORD_ORST_MAX_FK foreign key (ORD_ORST_INDEX_MAX) references ORDERSTATUS (ORST_INDEX) deferrable; alter table ORDERS add constraint ORD_OTYP_FK foreign key (ORD_OTYP_INDEX) references ORDERTYPES (OTYP_INDEX) deferrable; alter table ORDERS add constraint ORD_PIN_USED_FK foreign key (ORD_PIN_ID_USED) references PACKINGINSTRUCTIONS (PIN_ID) deferrable; alter table ORDERS add constraint ORD_PLNT_FK foreign key (ORD_PLNT_INDEX) references PLANTS (PLNT_INDEX) deferrable; alter table ORDERS add constraint ORD_PRGR_FK foreign key (ORD_GRPNR) references PRODUCTIONGROUPS (PRGR_INDEX) deferrable; alter table ORDERS add constraint ORD_SECT_FK foreign key (ORD_SECT_INDEX) references SECTORS (SECT_INDEX) deferrable; alter table ORDERS add constraint ORD_SECT_PLANNED_FK foreign key (ORD_PLANNEDSECTOR) references SECTORS (SECT_INDEX) deferrable; alter table ORDERS add constraint ORD_STGR_FK foreign key (ORD_STGR_INDEX) references STRUCTURALGROUPS (STGR_INDEX) deferrable; alter table ORDERS add constraint ORD_USE_FK foreign key (ORD_USE_INDEX) references USELOCATIONS (USE_INDEX) deferrable; alter table ORDERS add constraint ORD_VETY_FK foreign key (ORD_VETY_INDEX) references VEHICLETYPES (VETY_INDEX) deferrable; alter table ORDERS add constraint ORD_WCP_FK foreign key (ORD_WCP_ID) references WORKFLOWCONTROLPOS (WCP_ID) deferrable; alter table ORDERS add constraint ORD_WCTY_TARGET_FK foreign key (ORD_WCTY_INDEX_TARGET) references WORKCENTERTYPES (WCTY_INDEX) deferrable; alter table ORDERS add constraint ORD_WC_FK foreign key (ORD_WC_ID) references WORKFLOWCONTROL (WC_ID) on delete set null deferrable; alter table ORDERS add constraint ORD_WFTV_FK foreign key (ORD_WFTV_WFTY_ID, ORD_WFTV_VERSION) references WORKFLOWTYPEVERSIONS (WFTV_WFTY_ID, WFTV_VERSION) deferrable; alter table ORDERS add constraint ORD_WOCE_FK foreign key (ORD_WOCE_INDEX) references WORKCENTERS (WOCE_INDEX) deferrable; -- Create/Recreate check constraints alter table ORDERS add constraint ORD_ARTICLELOCK_CC check (ORD_ARTICLELOCK IN ('Y', 'N')); alter table ORDERS add constraint ORD_CABLEAVAILABLE_CC check (ORD_CABLEAVAILABLE IN ('Y', 'N')); alter table ORDERS add constraint ORD_ISREBUILDORDER_CC check (ORD_ISREBUILDORDER IN ('Y', 'N')); alter table ORDERS add constraint ORD_MATERIALAVAILABLE_CC check (ORD_MATERIALAVAILABLE IN ('Y', 'N')); alter table ORDERS add constraint ORD_ORDERLOCK_CC check (ORD_ORDERLOCK IN ('Y', 'N')); alter table ORDERS add constraint ORD_OVERPRODUCTIONFINISHED_CC check (ORD_OVERPRODUCTIONFINISHED IN ('N', 'Y')); alter table ORDERS add constraint ORD_PREASSEMBLYREQUIRED_CC check (ORD_PREASSEMBLYREQUIRED IN ('N', 'Y')); alter table ORDERS add constraint ORD_PRODUCEDONSITE_CC check (ORD_PRODUCEDONSITE IN ('N', 'Y')); alter table ORDERS add constraint ORD_QUALITYLOCK_CC check (ORD_QUALITYLOCK IN ('N', 'Y')); alter table ORDERS add constraint ORD_VALID_CC check (ORD_VALID IN ('N', 'Y')); -- Grant/Revoke object privileges grant select on ORDERS to DRSADMIN; grant read on ORDERS to MES_PROD_027_READ;