Exhaustive Material Availability analysis for Capable-to-promise in Infor10 ERP Express (Visual)

As a production scheduler and MRP Admin, I would like to be able to do capable-to-promise based on an exhaustive analysis of all parts, all the way back to raw materials.

The Material Availability feature only looks at the parts on a Work Order's BOM. It does not look at linked engineering masters for subassemblies, sub-subs, etc.

Of course when MRP is run in Visual, it goes through it's multiple passes, figuring out the demand for the subassemblies.

But I need to be able to "peer down through the engineering masters" all the way to the end.

Also, I want to be able to find the Maximum Cumulative Leadtime--in other words, what is the longest string of leadtimes in the exhaustive BOM?

Is there any way to do this in Visual? I haven't found it yet.

Find more posts tagged with

Comments

d-catalano

Sounds like you might need to look into Advanced Material Planning (AMP) or EasyLean. As far as I know, the best way to do this without either of the above is probably to have all of the BOMs included in the engineering master as legs. Although there might be a way to do it that I don't know about.
Another option might be "Show Work Order Audit Detail" in the "Info" menu. You can select "show criticial material", but this will/should only show you material that is unavailable I believe.

Legacy Contributor

In the past I have done "family tree" reports - sort of a where used through all levels - both up and down. I used crystal but there are other reporting tools that would work. The creation of the demand_supply_link table a few rev's back really helped.

As to Maximum Cumulative Leadtime I would recommend some reading on DDMRP - I recently became one of 28 people in THE WORLD certified in this discipline. It WILL change you the way you look at planning and scheduling.

Legacy Contributor

Patricia, thanks for the response.

1, We are actively considering DDMRP. It looks like THE way to go.

2, I have used the demand_supply_link in Synoptix to do reports that combine info from Customer Orders and Work Orders, but I don't see how that would help looking down through mulitple layers of Engineering Masters.

3. How do you use CR to do multiple-level where-used? What is the linking structure in the tables that links an engineering master to the upper level assembly?

Legacy Contributor

Daniel

Thanks. AMP - does it actually enable multiple-level material requirements checks? Easy lean we don't want to use. DDMRP would be better., as Patricia mentioned. We just did a webinar with them, and I am uncertain as to whether Replenishment+ will actually check through multiple layers of engineering orders, or if you have to have all materials on the BOM for it to work. I don't see how having everything on the BOM works--unless you do all parts on Period Supply in order to clump them together into reasonable groupings. Still, how do you generate "clump" WO's if al the materials are on massive parent WO's? Doesn't make sense.

"Show Work Order Audit Detail" lists the way the Concurrent Scheduler handles the resource operations, so that doesn't help with materials. "Show Critical Material" just blanked the screen for us.

Legacy Contributor

Patric, Patrick, Padraig - NOT Patricia!

Anyway -
work order table -demand supply link - work order table - demand supply link - through all the layers. Either up or down.

Replenishment+ is a program, it's not the art behind DDMRP. With DDMRP you will want to determine if you've properly decoupled your BOMS anyway - remember a de-coupling point requires a win for BOTH the upper and lower assemblies.

d-catalano

I don't use AMP so I can't answer any questions about it. I just know that it's a bit more robust than standard.
Visual ManuWindow and MRP seems to be ONE dimensional. What you see is what you get. If you want to see all the materials required to build a specific part, I believe you have to include ALL the materials in one work order. I do this using legs. The leg header card is the BOM part number, and then you can add all of the materials to it. Yes, this means massive parent WOs.

What's DDMRP?

Legacy Contributor

Patric--
1.sorry :^
2. I thought the demand_supply_link only coupled SO demand to WO supply. So it couples WO demand to subassembly WO supply as well? How do you come up with a report that zig-zags back and forth to develop a top-to-bottom materials list? That is just beyond my level of knowledge.
3. What do you mean, "a de-coupling point requires a win for BOTH the upper and lower assemblies"? What's a win? I've been thinking I might be able to come close to DDMRP without the R+ by weekly reports that help us keep safety stock tied to real demand. The thing is, though, you can't use Visual to look top-to-bottom, see the longest chain lead time, then analyze where-used and demand, then make recommendations for strategic buffers.

Daniel,

1. With your way of having all parts on the BOM's, what do you do if 5 WO's have the same part, and the part has a long setup time so you should run the parts together on a machine?
2. DDMRP combines Lean, DBR, MRP, and MSP (forecast), and then adds the ability to dynamically adjust key stock levels. Its in Orlickey's Material Requirements Planning, 3rd Ed. Learn more here:
www.beyondmrp.com/.../
www.demanddrivenmrp.com/index.php
www.replenishmentplus.com/Default.aspx

Legacy Contributor

I don't know synoptix - we looked at it and as soon as we saw it restricted somewhat the joing of information (like gupta) we decided to stick with Crystal - plus we had the report launcher and a trained workforce.

Anyway to go DOWN the chain - the demand part is your finished good - it is made up of supply parts - some of them or in the demand supply link - others are sub assemblies - or pre-assemblies -

so from the work order link to the supply part - through the demand supply link back to the work order table - (in crystal it would be work_order_1 becuse it is a copy of the table) - and more layers equals more links.

One client had fourteen (14!) layers like this - it was tough but it worked!

3. a win for both sides - both sides of the de-coupling point - on the supply side it aggregates demand, decreases supply variability - on the demand side they increase output reliability and availability. Remember safety stock is different than a buffer - you've go to wrap your head around that and then you realize that de-coupling point are strategic decisions in time and dollars that can kep a company agile in today's envionment.

You can apply a lot of DDMRP without replenishment plus - some of the alerts, and the reports - where they shine is the BOM matrix.

Legacy Contributor

Patric--thanks! One way or another, I need visiblity top-to-bottom of our material requirements.

Legacy Contributor

Do you have an IT developer on staff? If so talk to them. I made some functions that does this but from the requirement table minus the scheduling portion a while back and it’s not hard if you know SQL and some type of development language (C#, VB, etc..) maybe a couple hours or a days’ worth of work… not much. Visual is great but sometimes it just can’t handle what you want to look at without them charging some ridiculous “consulting” price or "you have to buy this module"

Legacy Contributor

Would you mind sharing some code that would get us started in the right direction? I have no clue where to start on this one. I don't think we have anyone on board that has the skills to tackle this from scratch. I'd understand if you prefer not to.

Legacy Contributor

no problem I'll post it on Monday when I'm back in the office

Legacy Contributor

This code will generate a list from the top level part and its operations along with the sub component operations and the sub component of the sub component and so forth. All you should have to do is put the list in a collection and find the scheduling stat you’re looking for. I have removed a lot of the code that you don’t need, also this isn’t the best forum to post code so hopefully you can read it as I can’t post any attachments.

A few notes about this code: The functions getComponents() and getSubComponents() are the same, I just found it easier to debug because of how many differnt levels can go into a part. The point of the spacing loops is that when used correctly it will add spaces to the part id so that it looks likes

Part ID
Sub Operations
Sub Operations
Material
Sub Operations
Material
Material

Also the whole idea behind this is to follow the steps

1. Call SQL stored procedure that gives you the operations for the current part id.
2. Call SQL stored procedure that gives you the requirements for the currently part id
3. Add to collection
4. Take each part in Step 2 and run it from Step 1.

this will give you the top down list you are looking for. Now for the scheduling info I would just make that part of the getResources() function and add it do the collection. If you do this, once all items are in the collection just transpose the collection to a gridview or literal or however else you want to display the data and you should be good to go. If you have any questions please let me know.

------------------------- SQL ----------------------------
CREATE PROCEDURE [dbo].[workflow_newjob_getresources_rev2]
-- Add the parameters for the stored procedure here
@engmaster varchar(30),
@lotid varchar(3),
@type char(1)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT DISTINCT SEQUENCE_NO, RESOURCE_ID, WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_TYPE, WORKORDER_SUB_ID
FROM dbo.OPERATION
WHERE (WORKORDER_BASE_ID = @engmaster) AND (WORKORDER_LOT_ID = @lotid) AND (WORKORDER_TYPE = @type)
ORDER BY WORKORDER_SUB_ID, SEQUENCE_NO

CREATE PROCEDURE [dbo].[workflow_getlatestlotid]
-- Add the parameters for the stored procedure here
@engmaster varchar(30)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT TYPE, BASE_ID AS Part, CREATE_DATE AS LatestDate, LOT_ID
FROM dbo.WORK_ORDER AS a
WHERE (BASE_ID = @engmaster) AND (TYPE = 'M') AND (CREATE_DATE =
(SELECT MAX(CREATE_DATE) AS Latest
FROM dbo.WORK_ORDER AS b
WHERE (BASE_ID = a.BASE_ID)))
END

------------- end sql ----------
------------------------ c# functions code --------------------
private void getResources(string strPartID, string strLotID, int intSpace, int intWorkflowID, string strSub = "", string strOp = "")
{
try
{
StringBuilder strSpace = new StringBuilder();
if (optStartTypePart.Checked)
{
for (int intX = 0; intX
{
strSpace.Append(" ");
}
}
bool blnFound = false;
SqlParameter[] arParams = new SqlParameter[3];
arParams[0] = new SqlParameter("@engmaster", SqlDbType.VarChar, 30);
arParams[1] = new SqlParameter("@lotid", SqlDbType.VarChar, 3);
arParams[2] = new SqlParameter("@type", SqlDbType.Char, 1);
arParams[0].Value = strPartID;
arParams[1].Value = strLotID;
if (optStartTypeWO.Checked == true) { arParams[2].Value = "W"; } else { arParams[2].Value = "M"; }
string strIndent = "";
string strLastIndet = "0";
int intSubID = 0;
string strSUB = "";

SqlDataReader dr = SqlHelper.ExecuteReader(myConnection.getVisualConnectionString, CommandType.StoredProcedure, "dbo.[workflow_newjob_getresources_rev2]", arParams);
while (dr.Read())
{

SqlParameter[] arParamsC = new SqlParameter[5];
arParamsC[0] = new SqlParameter("@workflowid", SqlDbType.Int);
arParamsC[1] = new SqlParameter("@partid", SqlDbType.NVarChar);
arParamsC[2] = new SqlParameter("@resource", SqlDbType.NVarChar);
arParamsC[3] = new SqlParameter("@operation", SqlDbType.NVarChar);
arParamsC[4] = new SqlParameter("@subid", SqlDbType.NVarChar);
arParamsC[0].Value = intWorkflowID;
arParamsC[1].Value = dr["WORKORDER_BASE_ID"].ToString();
arParamsC[2].Value = dr["RESOURCE_ID"].ToString();
arParamsC[3].Value = dr["SEQUENCE_NO"].ToString();
arParamsC[4].Value = dr["WORKORDER_SUB_ID"].ToString();

//spacing for the main resources on a work order
strIndent = dr["WORKORDER_SUB_ID"].ToString();
strSUB = strIndent;
if (strIndent != strLastIndet)
{
if (optStartTypeWO.Checked)
{
int intIndent = Convert.ToInt32(strIndent);
intSubID = intIndent;
intIndent = intIndent + 1;
intIndent = intIndent * 5;
for (int intX = 0; intX
{
strSpace.Append(" ");
}
strLastIndet = strIndent;
}
}

newproject_week2 strCurrentInfo_week2 = new newproject_week2(strSpace.ToString() + dr["WORKORDER_BASE_ID"].ToString(), dr["SEQUENCE_NO"].ToString(), dr["RESOURCE_ID"].ToString(), strSUB, strW2_Q1, strW2_Q2, strW2_Q3, blnW2_Q1_Approved, blnW2_Q2_Approved, blnW2_Q3_Approved, dteW2_Q1_timestamp, dteW2_Q2_timestamp, dteW2_Q3_timestamp, strW2_Q1_ApprovedBy, strW2_Q2_ApprovedBy, strW2_Q3_ApprovedBy, strW2_FileUpload, strW2_FileUploadLoc, strW2_Q1_Notes, strW2_Q2_Notes, strW2_Q3_Notes, strW2_Q1_DueDate, strW2_Q2_DueDate, strW2_Q3_DueDate);
myWorkflow_week2.Add(intCount, strCurrentInfo_week2);

++intCount;
//get custom fields info according to the current item
blnFound = true;
}
dr.Close();
//if blnfound == false then that means that we are dealing with a material and no resource or opperation will be associated with it
//we should still contact the server to get any assignments but do not expect any "resource" information
string strReqOpp = "";
if (!blnFound)
{
SqlParameter[] arParamsC = new SqlParameter[5];
arParamsC[0] = new SqlParameter("@workflowid", SqlDbType.Int);
arParamsC[1] = new SqlParameter("@partid", SqlDbType.NVarChar);
arParamsC[2] = new SqlParameter("@resource", SqlDbType.NVarChar);
arParamsC[3] = new SqlParameter("@operation", SqlDbType.NVarChar);
arParamsC[4] = new SqlParameter("@subid", SqlDbType.NVarChar);
arParamsC[0].Value = intWorkflowID;
arParamsC[1].Value = strPartID;
arParamsC[2].Value = "";

arParamsC[3].Value = strOp;
arParamsC[4].Value = strSub;
//spacing for a requirement on a work order
if (optStartTypeWO.Checked)
{
var requirement = getSUBID(txtPartID.Text.ToString(), txtLotID.Text.ToString(), strPartID);
int intIndent = requirement.Item1;
strReqOpp = requirement.Item2;
strSUB = intIndent.ToString();

intIndent = intIndent + 1;
intIndent = intIndent * 10;
for (int intX = 0; intX
{
strSpace.Append(" ");
}
}

newproject_week2 strCurrentInfo_week2 = new newproject_week2(strSpace.ToString() + strPartID, strOp, "", strSub, strW2_Q1, strW2_Q2, strW2_Q3, blnW2_Q1_Approved, blnW2_Q2_Approved, blnW2_Q3_Approved, dteW2_Q1_timestamp, dteW2_Q2_timestamp, dteW2_Q3_timestamp, strW2_Q1_ApprovedBy, strW2_Q2_ApprovedBy, strW2_Q3_ApprovedBy, strW2_FileUpload, strW2_FileUploadLoc, strW2_Q1_Notes, strW2_Q2_Notes, strW2_Q3_Notes, strW2_Q1_DueDate, strW2_Q2_DueDate, strW2_Q3_DueDate);
myWorkflow_week2.Add(intCount, strCurrentInfo_week2);

++intCount;
}

lblError.Text = text.ToString();

}
catch (Exception ex)
{
lblError.Text = ex.Message.ToString();
}
}
private void getComponents(string strPart, string strLot, int intSpacing)
{
try
{

SqlParameter[] arParams = new SqlParameter[3];
arParams[0] = new SqlParameter("@partid", SqlDbType.VarChar, 30);
arParams[1] = new SqlParameter("@lotid", SqlDbType.VarChar, 3);
arParams[2] = new SqlParameter("@type", SqlDbType.Char, 1);
arParams[0].Value = strPart;
arParams[1].Value = strLot;
if (optStartTypeWO.Checked == true) { arParams[2].Value = "W"; } else { arParams[2].Value = "M"; }
intCountTemp = intCount;
SqlDataReader dr = SqlHelper.ExecuteReader(myConnection.getVisualConnectionString, CommandType.StoredProcedure, "dbo.[workflow_getsubcomponents_rev2]", arParams);
while (dr.Read())
{
string strCurrentPartID = dr["PART_ID"].ToString();
string strCurrentSUBID = dr["WORKORDER_SUB_ID"].ToString();
string strCurrentOp = dr["OPERATION_SEQ_NO"].ToString();
getResources(strCurrentPartID, getLatestLotID(strCurrentPartID), intSpacing, Convert.ToInt32(hidWorkflowID.Value), strCurrentSUBID, strCurrentOp );
getSubComponents(strCurrentPartID, getLatestLotID(strCurrentPartID), intSpacing + 10);

}
dr.Close();

}
catch (Exception ex)
{
lblError.Text = ex.Message.ToString();
}
}
private void getSubComponents(string strPart, string strLot, int intSpacing)
{
try
{

SqlParameter[] arParams = new SqlParameter[3];
arParams[0] = new SqlParameter("@partid", SqlDbType.VarChar, 30);
arParams[1] = new SqlParameter("@lotid", SqlDbType.VarChar, 3);
arParams[2] = new SqlParameter("@type", SqlDbType.Char, 1);
arParams[0].Value = strPart;
arParams[1].Value = strLot;
if (optStartTypeWO.Checked == true) { arParams[2].Value = "W"; } else { arParams[2].Value = "M"; }
SqlDataReader dr = SqlHelper.ExecuteReader(myConnection.getVisualConnectionString, CommandType.StoredProcedure, "dbo.[workflow_getsubcomponents_rev2]", arParams);
while (dr.Read())
{
string strCurrentPartID = dr["PART_ID"].ToString();
string strCurrentSUBID = dr["WORKORDER_SUB_ID"].ToString();
string strCurrentOp = dr["OPERATION_SEQ_NO"].ToString();
getResources(strCurrentPartID, "0", intSpacing, Convert.ToInt32(hidWorkflowID.Value), strCurrentSUBID, strCurrentOp);
getSubComponents(strCurrentPartID, getLatestLotID(strCurrentPartID), intSpacing + 10);
}
dr.Close();

}
catch (Exception ex)
{
lblError.Text = ex.Message.ToString();
}
}
private string getLatestLotID(string strPart)
{
//used to get the latest lot(rev) off a master
try
{

string strLotID = "0";
SqlParameter[] arParams = new SqlParameter[1];
arParams[0] = new SqlParameter("@engmaster", SqlDbType.VarChar, 30);
arParams[0].Value = strPart;

SqlDataReader dr = SqlHelper.ExecuteReader(myConnection.getVisualConnectionString, CommandType.StoredProcedure, "dbo.[workflow_getlatestlotid]", arParams);
while (dr.Read())
{
strLotID = dr["LOT_ID"].ToString();
}
dr.Close();
//return the latest lot # found or the default of 0
return strLotID;

}
catch (Exception ex)
{
return "0";
}
}

private Tuple getSUBID(string strWO, string strLot, string strPartID)
{
//since this is a requirement on a work order and not an eng master the requirements have no resources therefore we need to recontact the db for the requirements for the wo and then grab the sub id off of it instead of the resource sp
try
{

SqlParameter[] arParams = new SqlParameter[3];
arParams[0] = new SqlParameter("@partid", SqlDbType.VarChar, 30);
arParams[1] = new SqlParameter("@lotid", SqlDbType.VarChar, 3);
arParams[2] = new SqlParameter("@type", SqlDbType.Char, 1);
arParams[0].Value = strWO;
arParams[1].Value = strLot;
arParams[2].Value = "W";
intCountTemp = intCount;
string strSUBID = "0";
string strOperation = "";
SqlDataReader dr = SqlHelper.ExecuteReader(myConnection.getVisualConnectionString, CommandType.StoredProcedure, "dbo.[workflow_getsubcomponents_rev2]", arParams);
while (dr.Read())
{
string strCurrentPartID = dr["PART_ID"].ToString();
if (strCurrentPartID == strPartID)
{
strSUBID = dr["WORKORDER_SUB_ID"].ToString();
strOperation = dr["OPERATION_SEQ_NO"].ToString();
}
}
dr.Close();
int intID = Convert.ToInt32(strSUBID);
return new Tuple(intID, strOperation);
}
catch (Exception)
{
return new Tuple(0, "");
}
}
----------------- end c# functions ------------------

----------- how to call the c# functions ----------------
//start off getting the top level parts info
getResources(strPartID, strLotID, 0, Convert.ToInt32(hidWorkflowID.Value));
//now get all of the sub comonpents of the toplevel
getComponents(strPartID, strLotID, 10);

Legacy Contributor

darn the fourm ripped out my spacing, the

Part ID
Sub Operations
Sub Operations
Material
Sub Operations
Material
Material

Should be displayed as multi level tier(s)

Legacy Contributor

AChronister:

Thanks for the code! Infor should implement this as an option in Material Availability--have you submitted it as an enhancement request?

I assume you meant the multi level to be something like this:

Part ID
--Sub Operations
----Sub Operations
------Material
--------Sub Operations
----------Material
------------Material

[Updated on 8/13/2012 9:35 AM]

Legacy Contributor

it wouldve been more like

Part ID
Operation
---------Sub Components
---------Operations
------------------Sub Components
------------------Operations
------------------Material
---------Sub Component
---------Etc
---------Material
Operations
Material

It can go to unlimited amount of levels. it will just keep going until it doesnt return anything. Also in getresources() you can remove the part if(!blnFound) { ..... } This part of the code I grab some custom info but didnt realize I pasted it... you dont need it.

Important Links

Community Hubs
Discussion Forums
Groups
Community News

Copyright © 2025 Infor. All rights reserved.