2 Replies Latest reply on Oct 5, 2009 1:41 PM by ninja

    When a record can play dual roles



      When a record can play dual roles




      Found myself in an interesting situation:


      Context - Inventory management


      Current setup (the pertinent tables best I can figure):


              Inventory prediction and allocation table = "MatManagement"

              Inventory summary table                      = "MatInv"  includes summary of all related weight sheets

              Weight sheets per lot                          = "weight sheets"  Includes Qty of material left per lot

              Transactions (Line Items)                     = "WSLI" 


      MatManagement             MatInv           WeightSheets           WSLI

        MaterialID ----------- MaterialID        WSID---------------<WSID




      Now a vendor sent material in that meets the specs for two different raw materials.  Didn't see that one coming (shame on me).


      When I enter an order, the MatManagement looks through the formula, calculates that I need 20Kg of GlassA, compares to the quantity of GlassA in stock (MatInv),  and flags whether or not the order should begin manufacture, and if so creates a material request which allocates the material and lowers the available stock level (MatInv).  That works fine.  But I have 300Kg of material that meets spec for GlassA and also GlassA_milled.


      What suggestions are there for how to count the 300Kg of glass toward both GlassA and GlassA_milled without creating erroneous flags saying that I can use 600Kg of glass?  I could stock 150Kg in each type, but that would allow a stock outage and reorder when it wasn't really out...how to create a functional alias without a full redesign of structure (which would not be tolerated by the users at this point)?


      Thanks for whatever thoughts you can offer.

        • 1. Re: When a record can play dual roles

          In one project, we handled this problem by setting up Bills of Materials that listed groups of materials where the manufactured product needed one material selected from each group. Thus, alternate but acceptible materials were listed in the BOM but only one specific material from each such group was selected for a given manufacturing run.


          an Example BOM might look like this:


          AltFlag MaterialID   MaterialDesc        UsageQty          EffDate1       EffDate2

                     C1234       BLk-3001 metal    15lbs/Case         9/1/09         9/7/09

          x          C2345       BLK-3000 metal    15lbs/Case        10/1/09        10/7/09

                     C4598       Trifoil Foam liner    1lbs/Case         1/1/07          12/31/50



          The second item on the list would be a different but acceptable material to use in place of the first item. The effective date fields were then used to control which materials were used for a given run.


          In your case, I'd give the new material it's own ID and use a similar method to use it as a "subsitute" material.

          • 2. Re: When a record can play dual roles

            Thanks Phil,


            That's a good idea that we are considering, and I'm hoping for a better one...


            The reason to keep looking is that it may only be this one lot of GlassA that meets the GlassA_milled spec.  The past lots have not, and I have no certainty that the next one will.

            If I change the BOM to allow the use of GlassA_milled, and the next lot doesn't meet the GlassA_milled spec, then I run the risk of making out of spec final product.  We do have other products that can tolerate either one, and the BOM for those already cross-references as you suggest.  That works well when either Glass can make in-spec finished goods.


            Another stress-riser is that we have about 175 BOM's that use GlassA, and another 130 BOM's that use GlassA_milled.  Its a lot of changing to do (that system is still on paper with 6 approvals required per changed BOM...I haven't gotten the go ahead to pull that one onto the computer yet.)  That said, reason#1 is still my biggest pause...a batch error costs wayyyy too much to increase the risk.


            Please continue with ideas as available...I'll be hashing this out with the stockroom controller for a while until we find a low risk, 'relatively' low labor way to work it through.  In the mean time, it's Flagged on the electronic weight sheet to do a manual reallocation for this lot only from one weight sheet to the corresponding material's weight sheet as needed.  This keeps the batching risk low, and still allows for the manual double and triple checks by the floor technicians, but the MatManagement function is reporting errant info.  Any additional thoughts and ideas are very much appreciated.