9.05.700B2 - Putting a hard stop in place

I recently had to put together something that would do a similar thing. This prevents the final operation (the one that would allow the parts to be receipted or shipped) from reporting a quantity that is unsupported by the quantity of material issued. There are a couple elements that are specific to our situation and the whole thing is rigged together by generating a VB connection to the SQL database for a read. Maybe not the most ideal method but it works for us just fine. To add it, simply customize the end activity form and use the wizard to add a before field change to the labor qty field. Then drop the code below into it. You will need to tweak to your situation, but that is the gist of it. Good luck and do this in a TEST ENVIRONMENT of course.

Tom

Private Sub LaborDtl_BeforeFieldChange(ByVal sender As Object, ByVal args As DataColumnChangeEventArgs)
' ** Argument Properties and Uses **
' args.Row("FieldName")
' args.Column, args.ProposedValue, args.Row
' Add Event Handler Code
Select Case args.Column.ColumnName

Case "LaborQty"
Dim edvLabor as EpiDataView = CType(oTrans.EpiDataViews("End"), EpiDataview)
Dim NextOprSeq as Int32 = edvLabor.dataView(edvLabor.Row)("NextOprSeq")

'Is this the final operation?
if (NextOprSeq)=0 and edvLabor.dataView(edvLabor.Row)("LaborQty") <> 0 then
'messagebox.show("This is the final operation.")
'figure out the total quantity attempting to be receipted (previous + now)
Dim LaborQty as Int32 = edvLabor.dataView(edvLabor.Row)("LaborQty")
Dim PrevQty as Int32
Dim JobNum as string = edvLabor.dataView(edvLabor.Row)("JobNum").ToString
Dim CurOprSeq as string = edvLabor.dataView(edvLabor.Row)("OprSeq").ToString
Dim con As New SqlConnection
Dim cmd As New SqlCommand
Try
con.ConnectionString = "server=YOURSERVER;uid=USERID;pwd=YOURPASSWORD;database=YOURDB"
con.Open()
cmd.Connection = con
cmd.CommandText = "SELECT QtyCompleted FROM JobOper WHERE JobNum = '" & JobNum & "'"
cmd.CommandText += " AND OprSeq = '" & CurOprSeq & "'"
Dim myReader As SqlDataReader = cmd.ExecuteReader
If myReader.HasRows Then
While myReader.Read()
PrevQty = cint(myReader(0))
End While
End If
myReader.Close()
Catch ex As Exception
MessageBox.Show("Error while calculating previous qty completed. " & ex.Message, "Epicor Error")
Finally
con.Close()
End Try
'messagebox.show("The previous qty was: " & prevqty.tostring)
Dim TotalQty as Int32 = LaborQty + PrevQty
'Query for Quantity Problems
Dim ProblemString as String = ""
con = New SqlConnection
cmd = New SqlCommand
Try
con.ConnectionString = "server=epicordb;uid=sa;pwd=Mis2003;database=EpicorPilot905"
con.Open()
cmd.Connection = con
cmd.CommandText = "select 'Mtl:'+ Cast(JM.MtlSeq as nvarchar)+ ' PN:' +JM.PartNum + ' has only ' + CAST(cast(JM.IssuedQty as decimal(8,2))as nvarchar) + ' of ' + CAST(cast(JM.RequiredQty as decimal(8,2))as nvarchar) + JM.IUM+ ' issued. This does not support the entered completion qty.'"
cmd.CommandText += "from JobMtl JM join JobHead JH on JH.JobNum = JM.JobNum where JM.JobNum = '" & JobNum & "' and JM.RequiredQty > 0 and JH.ProdQty > 0 and JM.IUM not like '%AR%' and JM.IssuedQty/JM.RequiredQty < " & TotalQty & "/JH.ProdQty "
Dim myReader As SqlDataReader = cmd.ExecuteReader
If myReader.HasRows Then
Dim fNextResult as boolean = True
Do until not fNextResult
Do While myReader.Read()
ProblemString += myReader.GetString(0) & vbnewLine
loop
fNextResult = myReader.nextresult()
loop
End If
myReader.Close()
Catch ex As Exception
MessageBox.Show("Error while calculating previous qty completed. " & ex.Message, "Epicor Error")
Finally
con.Close()
End Try
if problemstring <> "" then
'messagebox.show(Problemstring)
'edvLabor.dataView(edvLabor.Row)("LaborQty") = 0
edvLabor.dataView(edvLabor.Row)("LaborNote") = problemstring

else
'messagebox.show("There is sufficient material to continue.")
end if

else
'messagebox.show("This is not the final operation.")
end if



'we need to look at all the jobmtl records for this job and confirm that the qty for each material is enough (percentage-wise) to support qty we are dealing with.

End Select
End Sub

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Rob Bucek
Sent: Tuesday, July 10, 2012 8:44 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] 9.05.700B2 - Putting a hard stop in place.



Keep in mind also depending on how you write it, you may or may not be covered from labor modifications or entries from time and expense.. this may or may not be an issue for you. We backflush our material but here is some code for covering over reporting against MES and Labor Entry. Youll take the same approach or style when looking out to the jobmtl records as the joboper records..

If you are not trying to cover this condition from time and expense disregard references to ttLaborDtl.EnableRequestMove.

If you need to declare any variables when calculating quantities per parent use the following format

Define variable [varname] as [character/integer/decimal/etc] no-undo.

/* Execute Code Below. This is for Stopping Over Reporting of Production Quantities */
For each ttLaborDtl where ttLaborDtl.RowMod = 'U' no-lock.
Find First JobOper Where JobOper.Company = ttLaborDtl.Company and JobOper.JobNum = ttLaborDtl.JobNum and JobOper.AssemblySeq = ttLaborDtl.AssemblySeq and JobOper.OprSeq = ttLaborDtl.OprSeq no-lock no-error.
If avail JobOper Then Do:
If JobOper.QtyCompleted + ttLaborDtl.LaborQty > JobOper.RunQty and ttLaborDtl.EnableRequestMove = yes Then DO:
{lib/PublishEx.i &ExMsg = "'There are already ' + String(JobOper.QtyCompleted) + ' parts claimed as complete. The operation required quantity is ' + String(JobOper.RunQty) + '. You entered a quantity of ' + String(ttLaborDtl.LaborQty) + ' this will result in over reported quantities.'"}.
assign ttLaborDtl.LaborQty = 0.
End.
End.
Else.
Find First labordtl where labordtl.company = ttlabordtl.company and labordtl.LaborDtlSeq = ttLaborDtl.LaborDtlSeq no-lock no-error.
if avail LaborDtl Then DO:
Define Variable oldLaborQty as integer no-undo.
assign oldLaborQty = LaborDtl.LaborQty.
If (JobOper.QtyCompleted - oldLaborQty) + ttLaborDtl.LaborQty > JobOper.RunQty and ttLaborDtl.EnableRequestMove = no Then DO:
{lib/PublishEx.i &ExMsg = "'There are already ' + String(JobOper.QtyCompleted) + ' parts claimed as complete. The operation required quantity is ' + String(JobOper.RunQty) + '. You entered a quantity of' + String(ttLaborDtl.LaborQty) + ' this will result in over reported quantities.'"}.
assign ttLaborDtl.LaborQty = 0.
End.
End.
End.

Rob Bucek
Production Control Manager
PH: (715) 284-5376 ext 311
Mobile: (715)896-0590
FAX: (715)284-4084

(Click the logo to view our site)

-----Original Message-----
From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>] On Behalf Of John Kreger
Sent: Tuesday, July 10, 2012 10:34 AM
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>
Subject: Re: [Vantage] 9.05.700B2 - Putting a hard stop in place.

Jose,
Would this, i assume would require me to right some ABL. Any idea where to get good examples of ABL code? I was a C# developer before moving to Epicor so should be an ok learning curve.

Thanks again for the suggestions and help. You guys really do make our lives easier.

Thanks,
--John Kreger

On Tue, Jul 10, 2012 at 10:25 AM, John Kreger <lilj8069@...<mailto:lilj8069%40gmail.com>> wrote:

> Our desire is not to backflush this raw material because it is lot
> tracked in most cases. It mostly seems to be due to laziness or lack
> of time on the part of our material handlers.
>
>
>
> On Tue, Jul 10, 2012 at 10:20 AM, Rob Bucek <rbucek@...<mailto:rbucek%40dsmfg.com>> wrote:
>
>> **
>>
>>
>> How is your material being issued..it sounds like backflush correct?
>>
>> Rob Bucek
>> Production Control Manager
>> PH: (715) 284-5376 ext 311
>> Mobile: (715)896-0590
>> FAX: (715)284-4084
>> [Description: cid:1.234354861@...<mailto:1.234354861%40web65412.mail.ac4.yahoo.com>]<
>> http://www.dsmfg.com/>
>> (Click the logo to view our site)<http://www.dsmfg.com/>
>>
>> From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>] On
>> Behalf Of lilj8069
>> Sent: Tuesday, July 10, 2012 10:16 AM
>> To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>
>> Subject: [Vantage] 9.05.700B2 - Putting a hard stop in place.
>>
>>
>> We're having issues with material being issued to jobs well after the
>> parts are being received to stock. This is creating massive
>> production variances and causing nightmares for our accounting
>> people. Does anyone have a good solution for putting a hard stop in
>> place in MES that will prevent someone from starting a production
>> transaction before material has been issued to the job. An ideal
>> state would be not letting the operator report a quantity greater
>> than the material that has been issued to this point. This seems like
>> a pretty in depth process to me but maybe someone has a simple solution.
>>
>> Thanks,
>> --John Kreger
>>
>> [Non-text portions of this message have been removed]
>>
>>
>>
>
>

[Non-text portions of this message have been removed]

------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.<http://groups.yahoo.com/group/vantage/files/>
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links



[Non-text portions of this message have been removed]
We're having issues with material being issued to jobs well after the parts are being received to stock. This is creating massive production variances and causing nightmares for our accounting people. Does anyone have a good solution for putting a hard stop in place in MES that will prevent someone from starting a production transaction before material has been issued to the job. An ideal state would be not letting the operator report a quantity greater than the material that has been issued to this point. This seems like a pretty in depth process to me but maybe someone has a simple solution.

Thanks,
--John Kreger
I've done this a thousand times for different people, basically but a BPM
in place which checks completed quantity against reported if it doesn't
match raise an exception

On the same BPM you can check that the Reported Qty is not greater than
that which can be made from the issued materials, you'll have to figure out
the formula for that but it shouldn't be too hard, you can use the Qty /
Per Parent to know how many you need for a given Part

You can also put a BPM on Start Avty which checks to make sure you have
issued the material

*Jose C Gomez*
*Software Engineer*
*
*
*
*T: 904.469.1524 mobile
E: jose@...
http://www.josecgomez.com
<http://www.linkedin.com/in/josecgomez> <http://www.facebook.com/josegomez>
<http://www.google.com/profiles/jose.gomez> <http://www.twitter.com/joc85>
<http://www.josecgomez.com/professional-resume/>
<http://www.josecgomez.com/feed/>
<http://www.usdoingstuff.com>

*Quis custodiet ipsos custodes?*



On Tue, Jul 10, 2012 at 11:15 AM, lilj8069 <lilj8069@...> wrote:

> **
>
>
> We're having issues with material being issued to jobs well after the
> parts are being received to stock. This is creating massive production
> variances and causing nightmares for our accounting people. Does anyone
> have a good solution for putting a hard stop in place in MES that will
> prevent someone from starting a production transaction before material has
> been issued to the job. An ideal state would be not letting the operator
> report a quantity greater than the material that has been issued to this
> point. This seems like a pretty in depth process to me but maybe someone
> has a simple solution.
>
> Thanks,
> --John Kreger
>
>
>


[Non-text portions of this message have been removed]
How is your material being issued..it sounds like backflush correct?

Rob Bucek
Production Control Manager
PH: (715) 284-5376 ext 311
Mobile: (715)896-0590
FAX: (715)284-4084
[Description: cid:1.234354861@...]<http://www.dsmfg.com/>
(Click the logo to view our site)<http://www.dsmfg.com/>

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of lilj8069
Sent: Tuesday, July 10, 2012 10:16 AM
To: vantage@yahoogroups.com
Subject: [Vantage] 9.05.700B2 - Putting a hard stop in place.



We're having issues with material being issued to jobs well after the parts are being received to stock. This is creating massive production variances and causing nightmares for our accounting people. Does anyone have a good solution for putting a hard stop in place in MES that will prevent someone from starting a production transaction before material has been issued to the job. An ideal state would be not letting the operator report a quantity greater than the material that has been issued to this point. This seems like a pretty in depth process to me but maybe someone has a simple solution.

Thanks,
--John Kreger



[Non-text portions of this message have been removed]
Our desire is not to backflush this raw material because it is lot tracked
in most cases. It mostly seems to be due to laziness or lack of time on the
part of our material handlers.



On Tue, Jul 10, 2012 at 10:20 AM, Rob Bucek <rbucek@...> wrote:

> **
>
>
> How is your material being issued..it sounds like backflush correct?
>
> Rob Bucek
> Production Control Manager
> PH: (715) 284-5376 ext 311
> Mobile: (715)896-0590
> FAX: (715)284-4084
> [Description: cid:1.234354861@...]<
> http://www.dsmfg.com/>
> (Click the logo to view our site)<http://www.dsmfg.com/>
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
> Of lilj8069
> Sent: Tuesday, July 10, 2012 10:16 AM
> To: vantage@yahoogroups.com
> Subject: [Vantage] 9.05.700B2 - Putting a hard stop in place.
>
>
> We're having issues with material being issued to jobs well after the
> parts are being received to stock. This is creating massive production
> variances and causing nightmares for our accounting people. Does anyone
> have a good solution for putting a hard stop in place in MES that will
> prevent someone from starting a production transaction before material has
> been issued to the job. An ideal state would be not letting the operator
> report a quantity greater than the material that has been issued to this
> point. This seems like a pretty in depth process to me but maybe someone
> has a simple solution.
>
> Thanks,
> --John Kreger
>
> [Non-text portions of this message have been removed]
>
>
>


[Non-text portions of this message have been removed]
Jose,
Would this, i assume would require me to right some ABL. Any idea where to
get good examples of ABL code? I was a C# developer before moving to Epicor
so should be an ok learning curve.

Thanks again for the suggestions and help. You guys really do make our
lives easier.

Thanks,
--John Kreger


On Tue, Jul 10, 2012 at 10:25 AM, John Kreger <lilj8069@...> wrote:

> Our desire is not to backflush this raw material because it is lot tracked
> in most cases. It mostly seems to be due to laziness or lack of time on the
> part of our material handlers.
>
>
>
> On Tue, Jul 10, 2012 at 10:20 AM, Rob Bucek <rbucek@...> wrote:
>
>> **
>>
>>
>> How is your material being issued..it sounds like backflush correct?
>>
>> Rob Bucek
>> Production Control Manager
>> PH: (715) 284-5376 ext 311
>> Mobile: (715)896-0590
>> FAX: (715)284-4084
>> [Description: cid:1.234354861@...]<
>> http://www.dsmfg.com/>
>> (Click the logo to view our site)<http://www.dsmfg.com/>
>>
>> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
>> Of lilj8069
>> Sent: Tuesday, July 10, 2012 10:16 AM
>> To: vantage@yahoogroups.com
>> Subject: [Vantage] 9.05.700B2 - Putting a hard stop in place.
>>
>>
>> We're having issues with material being issued to jobs well after the
>> parts are being received to stock. This is creating massive production
>> variances and causing nightmares for our accounting people. Does anyone
>> have a good solution for putting a hard stop in place in MES that will
>> prevent someone from starting a production transaction before material has
>> been issued to the job. An ideal state would be not letting the operator
>> report a quantity greater than the material that has been issued to this
>> point. This seems like a pretty in depth process to me but maybe someone
>> has a simple solution.
>>
>> Thanks,
>> --John Kreger
>>
>> [Non-text portions of this message have been removed]
>>
>>
>>
>
>


[Non-text portions of this message have been removed]
Keep in mind also depending on how you write it, you may or may not be covered from labor modifications or entries from time and expense.. this may or may not be an issue for you. We backflush our material but here is some code for covering over reporting against MES and Labor Entry. Youll take the same approach or style when looking out to the jobmtl records as the joboper records..

If you are not trying to cover this condition from time and expense disregard references to ttLaborDtl.EnableRequestMove.

If you need to declare any variables when calculating quantities per parent use the following format

Define variable [varname] as [character/integer/decimal/etc] no-undo.


/* Execute Code Below. This is for Stopping Over Reporting of Production Quantities */
For each ttLaborDtl where ttLaborDtl.RowMod = 'U' no-lock.
Find First JobOper Where JobOper.Company = ttLaborDtl.Company and JobOper.JobNum = ttLaborDtl.JobNum and JobOper.AssemblySeq = ttLaborDtl.AssemblySeq and JobOper.OprSeq = ttLaborDtl.OprSeq no-lock no-error.
If avail JobOper Then Do:
If JobOper.QtyCompleted + ttLaborDtl.LaborQty > JobOper.RunQty and ttLaborDtl.EnableRequestMove = yes Then DO:
{lib/PublishEx.i &ExMsg = "'There are already ' + String(JobOper.QtyCompleted) + ' parts claimed as complete. The operation required quantity is ' + String(JobOper.RunQty) + '. You entered a quantity of ' + String(ttLaborDtl.LaborQty) + ' this will result in over reported quantities.'"}.
assign ttLaborDtl.LaborQty = 0.
End.
End.
Else.
Find First labordtl where labordtl.company = ttlabordtl.company and labordtl.LaborDtlSeq = ttLaborDtl.LaborDtlSeq no-lock no-error.
if avail LaborDtl Then DO:
Define Variable oldLaborQty as integer no-undo.
assign oldLaborQty = LaborDtl.LaborQty.
If (JobOper.QtyCompleted - oldLaborQty) + ttLaborDtl.LaborQty > JobOper.RunQty and ttLaborDtl.EnableRequestMove = no Then DO:
{lib/PublishEx.i &ExMsg = "'There are already ' + String(JobOper.QtyCompleted) + ' parts claimed as complete. The operation required quantity is ' + String(JobOper.RunQty) + '. You entered a quantity of' + String(ttLaborDtl.LaborQty) + ' this will result in over reported quantities.'"}.
assign ttLaborDtl.LaborQty = 0.
End.
End.
End.

Rob Bucek
Production Control Manager
PH: (715) 284-5376 ext 311
Mobile: (715)896-0590
FAX: (715)284-4084

(Click the logo to view our site)


-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of John Kreger
Sent: Tuesday, July 10, 2012 10:34 AM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] 9.05.700B2 - Putting a hard stop in place.

Jose,
Would this, i assume would require me to right some ABL. Any idea where to get good examples of ABL code? I was a C# developer before moving to Epicor so should be an ok learning curve.

Thanks again for the suggestions and help. You guys really do make our lives easier.

Thanks,
--John Kreger


On Tue, Jul 10, 2012 at 10:25 AM, John Kreger <lilj8069@...> wrote:

> Our desire is not to backflush this raw material because it is lot
> tracked in most cases. It mostly seems to be due to laziness or lack
> of time on the part of our material handlers.
>
>
>
> On Tue, Jul 10, 2012 at 10:20 AM, Rob Bucek <rbucek@...> wrote:
>
>> **
>>
>>
>> How is your material being issued..it sounds like backflush correct?
>>
>> Rob Bucek
>> Production Control Manager
>> PH: (715) 284-5376 ext 311
>> Mobile: (715)896-0590
>> FAX: (715)284-4084
>> [Description: cid:1.234354861@...]<
>> http://www.dsmfg.com/>
>> (Click the logo to view our site)<http://www.dsmfg.com/>
>>
>> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On
>> Behalf Of lilj8069
>> Sent: Tuesday, July 10, 2012 10:16 AM
>> To: vantage@yahoogroups.com
>> Subject: [Vantage] 9.05.700B2 - Putting a hard stop in place.
>>
>>
>> We're having issues with material being issued to jobs well after the
>> parts are being received to stock. This is creating massive
>> production variances and causing nightmares for our accounting
>> people. Does anyone have a good solution for putting a hard stop in
>> place in MES that will prevent someone from starting a production
>> transaction before material has been issued to the job. An ideal
>> state would be not letting the operator report a quantity greater
>> than the material that has been issued to this point. This seems like
>> a pretty in depth process to me but maybe someone has a simple solution.
>>
>> Thanks,
>> --John Kreger
>>
>> [Non-text portions of this message have been removed]
>>
>>
>>
>
>


[Non-text portions of this message have been removed]



------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links