Customize Mfg Orders
 		Previous Topic  Next Topic 

The Customize Mfg Orders module enables advanced SQL users to make modifications to the SQL query in Manufacturing Orders Job Grid.  This feature should only be used to attach data from a user created table from within the DMS database to a job.  It must not be used to try and customize the SQL query as unpredictable results can occur.


Access to Customize Mfg Orders is not permitted when new tables are created for a DMS database.  The default is to block users from accessing this module.  It is recommended that all users, with the exception of IT or an administrator, be blocked from having access to this module.


Warning:  Customizing SQL is a very advanced technical feature.  If setup incorrectly, Manufacturing Orders Job Grid may not function correctly and/or could cause damage or loss of data.  It is strongly recommended that if you wish to use this feature you contact Dynafact for support before attempting to customize the SQL.  It is also recommended that you use a test environment and thoroughly test any SQL changes before applying the changes to a live system.


To access Customize Mfg Orders, select Tables | Customize Mfg Orders.  The following screen will appear:



The example below adds two new fields to the SQL statement (SpecialWorkOrders.MONumber as SpecialWorkOrdersMONumber  and SpecialWorkOrders.DueDate as SpecialWorkOrdersDueDate).  A left outer join is used to attach the data to the DMS data.  The final two lines define the new fields to DMS:



The following is an example of why a company may want to customize the Mfg Orders query.  Let’s say that a third party application ERP system produces certain manufacturing order (MO) information with a MO due date.  In this fictional ERP application there is not a one to one relationship between the DMS order numbers and the ERP MOs.  However, DMS users would like to see the MO number and MO due date information in DMS when they view the order numbers. 


The company decides to create a new table in the DMS database called MOWorkOrders.  The MOWorkOrders table is populated by the ERP application with the required data on a daily basis.


To relate the MO information to the DMS Work Orders, a user field was setup for the job called “MO Job Reference” with a map reference of 2 (see User Field Definitions in DMS to setup a map reference).  The MO number that relates to the DMS Order Number is entered into the user field. 


The company can use the Customize Mfg Orders to link the newly created table to the DMS order number (see the example below after the Technical Section on how to link the data).


Technical Information

The customized SQL code is kept in the table MfgOrdersSearch.  The internal key to the field uses the name:


       ~CUSTOMIZEDMSSQL


The Customize Mfg Orders module is used to add changes to the Mfg Orders SQL query.  The customized code must be in the format:


SQLCustomSelect= SQL code~

SQLCustomJoin= SQL code~

Field= SQL field 1 name, field type, Column Name, field size (integer) ~

Field= SQL field 2 name, field type, Column Name, field size (integer) ~

…

Field= SQL field n name, field type, Column Name, field size (integer)


Notes:


  1. The SQLCustomSelect SQL code is attached to any existing SELECT code in Manufacturing Orders Job Grid.  It must start with a comma and include the names of the new fields you wish to add to the Job Grid.
  2. The SQLCustomJoin SQL code is the code that tells DMS how you want to join the new table to DMS.  You can join by a user defined field as in the example given below.
  3. The SQL Field Name is the name of the fields in the SQL table
  4. Valid field type:
    1. String
    2. DateTime
    3. Memo
    4. Integer
    5. LargeInteger
    6. Float
    7. Currency
    8. Boolean
  5. The Column Name is the name that will appear to the user and is also the name that will be applied to the column on the job grid.
  6. Field Size is the length of the string field.  It can be 0 for other field types.
  7. Each line must end with a ~ except the last line.  The last line must not end with a ~.


In the example below a table called MOWorkOrders was created in the DMS database by an external application.  It has the fields MONumber and MODate.  A user field exists using Map Reference 2 that links the job to MOWorkOrders.  Note that each line ends with a ~ except the last line.  Also note that the SQLCustomSelect line starts with a comma.  In the example below a blank line was left between the sections to make it easier to read:


SQLCustomSelect=, MOWorkOrders.MONumber as MOWorkOrdersMONumber, MOWorkOrders.DueDate as MOWorkOrdersDueDate~


SQLCustomJoin=left outer join JobUF as MO1 on Job.OrderID = MO1.OrderId and MO1.UFRequiredID in (select UFRequired.UFRequiredID from UFRequired where MapRef=2) left outer join MOWorkOrders on MO1.Text = MOWorkOrders.MONumber~


Field=MOWorkOrdersMONumber,string,MO Number,40~

Field=MOWorkOrdersDueDate,datetime,MO Date,12