Does anybody have a good way of controlling new part creation so that users don't forget to populate or change the necessary data, especially when creating new parts by copying from existing parts?
We use Workflow.
Does the workflow look to see if it’s a new part and trigger the user to clear fields and/or to reevaluate certain fields. Can you provide any details of the workflow?
More info: My biggest concern is users creating new parts from existing parts (opening a part, changing the part ID, and saving). I’m trying to get better control of product codes, safety stocks, and leadtimes.
In the past I tried out a macro or workflow that cleared out all the fields if the part was created that day, but then users would have to wait until the next day to populate anything.
Hi,
Four years ago, the quality of the data in Visual was so bad (we had safety stock in quarantine, setup & run time in a user defined field). People before created macro everywhere to control the human.
We change the approach. First, we start by giving trainings (every employee including the CEO assist the training) to explain each filed, the importance of the data with an ERP. The important message in the training was: Visual give you the level of quality that you input in it. So, if you said you trust Visual at 20%, that means you are 20% good to do your job because you input data in Visual.
Second, we create a Master Data role. The master data is responsible to look at all part and engineering master created, correct it and explain it.
Third, we also implement ECN, the implementation cycle contains minimum 5 peoples and they all look at the part maintenance and the eng. Master.
With these actions, the quality of the data in Visual are well control. Finance, planning & purchasing are confident to use the data in Visual only. Management looks at Visual to do the manpower plan for the year.
Nancy
We recently put the part number creation in the hands of the R & D department. They determine if the part number is required and then forward a form to each department for the correct input as far as product code, sales information, purchasing information. The R & D department is then responsible for the input of the information including any new engineering master or changes to existing masters. For us it is a much better system than allowing any department to create and copy new part numbers. We do not have a workflow in Visual, but use a pdf form that gets approved and forwarded to the next department. We have a formal Work Instruction for this.
Christy
I had to enforce a new part naming convention in Visual Manufacturing in 2004 when working for a previous company. To enforce the part naming convention, I modified an existing Visual Manufacturing database trigger in the Oracle database - that custom code must be re-added to the standard trigger on every Visual Manufacturing upgrade. In the VMIUG-TEC group in 2004 and later in 2018 I described the solution as follows: I suggest that you take a look at how Infor's/Lilly's triggers report validation errors back to the application. You can see such errors by creating a SQL statement similar to the following:
SELECT *FROM DBA_TRIGGERSWHERE OWNER='SYSADM' AND TABLE_NAME='PART';
One trigger of interest might be TBIUD_PART_1. In the Inserting portion of that trigger, I added the following lines (note: line indent spacing was lost when pasting the code below from Notepad):
IF :NEWDATA.ID LIKE '% %' THEN raise_application_error(-20101, 'VMFG-33006 - Spaces not permitted in Part IDs'); END IF; IF :NEWDATA.ID != UPPER(:NEWDATA.ID) THEN raise_application_error(-20105, 'VMFG-33010 - Part IDs must be entered in uppercase'); END IF; IF :NEWDATA.ID LIKE '%-SA_' THEN raise_application_error(-20110, 'VMFG-33007 - Dash should not preceed SA in Part IDs'); END IF; IF :NEWDATA.ID LIKE '%SW_' THEN raise_application_error(-20115, 'VMFG-33008 - Use SA# instead of SW# for sub-assembly Part IDs'); END IF; IF :NEWDATA.STOCK_UM != UPPER(:NEWDATA.STOCK_UM) THEN raise_application_error(-20120, 'Stock unit of measure must be entered in uppercase'); END IF; IF :NEWDATA.PRODUCT_CODE IS NULL AND :NEWDATA.DETAIL_ONLY='N' THEN raise_application_error(-20125, 'Cannot save without specifying a product code'); END IF; IF :NEWDATA.COMMODITY_CODE IS NULL AND :NEWDATA.DETAIL_ONLY='N' THEN raise_application_error(-20130, 'Cannot save without specifying a commodity code'); END IF; IF :NEWDATA.FABRICATED = 'Y' AND :NEWDATA.ENGINEERING_MSTR IS NULL AND :NEWDATA.DETAIL_ONLY='N' THEN raise_application_error(-20135, 'Must specify engineering ID 0 for fabricated parts'); END IF; IF :NEWDATA.FABRICATED = 'N' AND :NEWDATA.ENGINEERING_MSTR IS NOT NULL THEN raise_application_error(-20140, 'Must not specify engineering ID for non-fabricated parts'); END IF; IF :NEWDATA.PURCHASED = 'Y' AND NVL(:NEWDATA.PLANNING_LEADTIME,0) = 0 THEN raise_application_error(-20145, 'Must specify lead time for purchased Part IDs'); END IF; IF :NEWDATA.PRODUCT_CODE='FG' AND NVL(:NEWDATA.WEIGHT,0) = 0 THEN raise_application_error(-20150, 'Must specify weight of finished goods'); END IF; IF :NEWDATA.WEIGHT_UM IS NOT NULL AND :NEWDATA.WEIGHT_UM != UPPER(:NEWDATA.WEIGHT_UM) THEN raise_application_error(-20120, 'Stock unit of measure must be entered in uppercase'); END IF; IF :NEWDATA.WEIGHT_UM IS NULL AND :NEWDATA.PRODUCT_CODE='FG' THEN raise_application_error(-20120, 'Stock unit of measure must be entered in uppercase'); END IF; IF :NEWDATA.PRODUCT_CODE='FG' AND NVL(:NEWDATA.PLANNING_LEADTIME,0) = 0 THEN raise_application_error(-20155, 'Must specify lead time for finished goods'); END IF; IF :NEWDATA.ID LIKE '%' || CHR(39) || '%' THEN raise_application_error(-20999, 'VMFG-33011 - Single Quotes not permitted in Part IDs'); END IF;
Those negative error numbers are translated by Visual using the errors.sql file in the Centura Runtime folder to display a human readable error message on screen. There are a couple oddities when dealing with such trigger code. Visualdoes not report the actual error number, such as -20120, instead, it adds the error number reported by the trigger to 45536. Thus, the above error numbers would be reported as 25435, 25431, etc. In the errors.sql file on each computer I added the following (note tha if you use -20999 for the error number, the actual error message from the trigger is displayed rather than the value from the errors.sql file):
25381 539 001 Company Part Creation Rule: Must specify planning lead time of finished goods parts Reason: Required Remedy: Enter Correct Values25386 539 001 Company Part Creation Rule: Must enter weight of finished goods parts Reason: Required Remedy: Enter Correct Values25391 539 001 Company Part Creation Rule: Must specify planning lead time for purchased part IDs Reason: Required Remedy: Enter Correct Values25396 539 006 Company Part Creation Rule: Must not specify engineering ID for non-fabricated parts Reason: Required Remedy: Enter Correct Values25401 540 001 Company Part Creation Rule: Must specify engineering ID 0 for fabricated parts Reason: Required Remedy: Enter Correct Values25406 540 006 Company Part Creation Rule: Must specify new part commodity code to save. Reason: Product code and commodity code are required for all parts. Remedy: Enter an appropriate product code and commodity code.25411 541 011 Company Part Creation Rule: Must specify new part product code to save. Reason: Product code and commodity code are required for all parts. Remedy: Enter an appropriate product code and commodity code.25416 541 016 Company Part Creation Rule: Enter unit of measure in uppercase Reason: Required Remedy: Enter Correct Values25421 542 021 Company Part Creation Rule: Use SA# instead of SW# for subassembly part IDs Reason: Required Remedy: Enter Correct Values25426 542 026 Company Part Creation Rule: Dash should not prefix SA in part IDs Reason: Required Remedy: Enter Correct Values25431 543 031 Company Part Creation Rule: Part IDs must be entered in uppercase Reason: Required Remedy: Enter Correct Values25435 543 035 Company Part Creation Rule: Spaces not permitted in part IDs Reason: Required Remedy: Enter Correct Values
With the modified trigger, the engineers could not bypass the company's part naming convention by adding part IDs on the fly in the Manufacturing Window. This change caused a sharp decline in the number of parts being created erroneously, and where not all of the required information is specified, regardless of the interface used to create the part IDs in Visual. There are only a narrow range of acceptable error numbers, ranging from about -20000 to -20999.-----------------------------
For a macro presentation that I gave in 2009 at a Visual user group meeting, I translated the above trigger code into a Visual Manufacturing macro. Note that this code will NOT prevent an engineer from creating a new part ID on the fly in the Manufacturing Window. So, this example is more or less simply a demonstration of how to translate the Oracle trigger code to VBScript - you might then translate into the trigger syntax used by SQL Server, if that is the database back end that you use:
Dim strOut
If Instr(ID, " ") > 0 Then strOut = strOut & "Spaces not permitted in Part IDs" & vbCrLfEnd IfIf Instr(ID, "'") > 0 Then strOut = strOut & "Single quotes not permitted in Part IDs" & vbCrLfEnd IfIf ID <> Ucase(ID) Then strOut = strOut & "Part IDs must be entered in uppercase" & vbCrLfEnd IfIf Len(ID) > 4 Then If Instr(ID, "-SA") = Len(ID) - 3 Then strOut = strOut & "Dash should not preceed SA in Part IDs" & vbCrLf End IfEnd IfIf Len(ID) > 3 Then If Instr(ID, "SW") = Len(ID) - 2 Then strOut = strOut & "Use SA# instead of SW# for sub-assembly Part IDs" & vbCrLf End IfEnd IfIf STOCK_UM <> Ucase(STOCK_UM) > 0 Then strOut = strOut & "Stock unit of measure must be entered in uppercase" & vbCrLfEnd IfIf PRODUCT_CODE = "" And DETAIL_ONLY = False Then strOut = strOut & "Cannot save without specifying a product code" & vbCrLfEnd IfIf COMMODITY_CODE = "" And DETAIL_ONLY = False Then strOut = strOut & "Cannot save without specifying a commodity code" & vbCrLfEnd IfIf FABRICATED = True And ENGINEERING_MSTR = "" And DETAIL_ONLY = False Then strOut = strOut & "Must specify engineering ID 0 for fabricated parts" & vbCrLfEnd IfIf FABRICATED = False And ENGINEERING_MSTR <> "" Then strOut = strOut & "Must not specify engineering ID for non-fabricated parts" & vbCrLfEnd IfIf PURCHASED = True And (PLANNING_LEADTIME = 0 Or PLANNING_LEADTIME = "") Then strOut = strOut & "Must specify lead time for purchased Part IDs" & vbCrLfEnd IfIf PRODUCT_CODE = "FG" And (WEIGHT = 0 Or WEIGHT = "") Then strOut = strOut & "Must specify weight of finished goods" & vbCrLfEnd IfIf WEIGHT_UM <> "" And WEIGHT_UM <> Ucase(WEIGHT_UM) Then strOut = strOut & "Stock unit of measure must be entered in uppercase" & vbCrLfEnd IfIf WEIGHT_UM = "" And PRODUCT_CODE = "FG" Then strOut = strOut & "Must specify weight of finished goods" & vbCrLfEnd IfIf PRODUCT_CODE = "FG" And (PLANNING_LEADTIME = 0 Or PLANNING_LEADTIME = "") Then strOut = strOut & "Must specify lead time for finished goods" & vbCrLfEnd IfIf strOut <> "" Then strOut = left(strOut, Len(strOut) - 2) MACRO_MESSAGE = strOut MACRO_SUCCESS = FalseElse MACRO_SUCCESS = TrueEnd If
Charles HooperSenior Software EngineerMeadville Forging Company
Thanks Charles. I think I'm actually using one of your macros in part maintenance already, so thanks for that and others that you have provided throughout the years.
I don't see anything in the trigger that addresses the issue that I'm trying to solve. Am I missing it?
Think it would be possible to write a macro that clears out fields if the create date/time is with seconds of now()? Something like this (not sure it will respond to this. I'll have to do some experimenting. Also not sure if CREATE_DATE has a time stamp in it):
If DateDiff("s",CREATE_DATE, NOW()) < 2 THEN
PRODUCT_CODE = ""
End If
Hi Daniel,
At least through Visual Manufacturing 9.0.8 SP5, the CREATE_DATE value for a part is never made available to the macro code, even though there is a CREATE_DATE variable provided by Part Maintenance. To confirm that, open an existing part ID in Part Maintenance that has a create date. Create an OnSave macro with the following line:
Msgbox "Create Date: " & CREATE_DATE
When the save button is clicked in the Visual module, no date will be displayed.
If you do not want a person to create more than one part ID every 20 seconds, you could write to a file in the user's temp folder with the current date and time at the end of the OnSave macro, and at the start of the macro, read in the date and time from that temp file and abort the save if the person did not wait 20 seconds. A technique for doing that is shown in the below macro:
Dim strTempFileDim strLastSaveDim dtLastSaveDim objFSODim flLastPartSave
Set objFSO = CreateObject("Scripting.FileSystemObject")strTempFile = objFSO.GetSpecialFolder(2)If Mid(strTempFile, Len(strTempFile)) <> "" Then strTempFile = strTempFile & ""End IfstrTempFile = strTempFile & "Last Part Save.txt"
If objFSO.FileExists(strTempFile) = True Then Set flLastPartSave = objFSO.OpenTextFile(strTempFile) strLastSave = flLastPartSave.ReadLine flLastPartSave.Close
If IsDate(strLastSave) = True Then dtLastSave = CDate(strLastSave) Else 'IsDate(strLastSave) = True dtLastSave = DateAdd("d", -1, Date) End If 'IsDate(strLastSave) = TrueElse 'If objFSO.FileExists(strTempFile) = True dtLastSave = DateAdd("d", -1, Date)End If 'If objFSO.FileExists(strTempFile) = True
If DateDiff("s", dtLastSave, Now) < 20 Then MACRO_SUCCESS = False MACRO_MESSAGE = "Double-check your work!"Else Set flLastPartSave=objFSO.CreateTextFile(strTempFile, True) flLastPartSave.Write Now & vbCrLf flLastPartSave.CloseEnd If
Set objFSO = NothingSet flLastPartSave = Nothing
I noticed that about the create date and modify date fields in macros. Thanks for the input. I think I'm dialing in on what I'm looking for. I have an OnAfterSave Macro with a connection to the part table to pull the create date from there. If the create date is within 5 seconds of now, the OnAfterSave will launch a series of input boxes that asks the user to verify specified fields. Here's what I have so far, and it seems to be working as desired:
'DATABASE CONNECTION TO SQL SERVER Const VELIVE= "PROVIDER=SQLOLEDB;DATA SOURCE=SERVERVISUAL;UID=x;PWD=xx;DATABASE=DB" set vecon = CreateObject( "ADODB.Connection" ) vecon.Open DB 'Database Query query = "SELECT P.CREATE_DATE FROM PART P LEFT JOIN PART_SITE PS ON PS.PART_ID = P.ID WHERE P.ID='" & ID & "'" set rs = CreateObject("ADODB.Recordset") rs.Open query, vecon 'Macro If Not(rs.EOF) Then If DateDiff("s",rs("CREATE_DATE"), NOW()) < 5 THEN PRODUCT_CODE = "" msgbox "Looks like this part was just created. Please repopulate..." End If Else End If
I think I can take it from here.
Thanks for all the inputs and ideas! I will also be creating a query for new parts and having our mfg engineer check off on new parts as they get created. The macro should decrease the workload by making sure parts get set up correctly in the first place. Also plan on writing some more instructions for part creators. As somebody said: ERP system is only as good as the data getting put in to it.
