Completed Work Order deletes unused serial inventory

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Completed Work Order deletes unused serial inventory

rfthomas
We have a work order and associated BOM that includes on serial numbered item.  The work order call for 100 parts to be produced.  Only 83 were produced (received).  The serial numbers of the BOM item (issued to the work order) were consummed upon item receipt.  There were 17 serial number items remaining.  The work was closed and instead of returning the issued serial number items thay have disappeared.

There is no record in the Audit Trail showing any action for the part code or serial number when teh Work Order is closed.  There is nothing in the Serial Number History after the serial numbered item is issued to the work order.

When a work order is closed, the unused issued items should be returned to inventory, not just disappear.  

There are no reports available to readily trace what has happened.

Bob Thomas
Reply | Threaded
Open this post in threaded view
|

Re: Completed Work Order deletes unused serial inventory

rfthomas
To provide further detail:

Work Orders do not properly deal with serialized inventory

To demonstrate the problem create the following two inventory items:

TEST_SERIAL – purchased/controlled/serial number
TEST_SERIAL_M – manufactured/controlled/serial number

Create a BOM for TEST_SERIAL_M with one entity:

TEST_SERIAL QTY 1, Not Auto Issued

Add 7 of TEST_SERIAL to inventory, S/N 1, 2, …, 7

Create a Work Order to manufacture 5 of TEST_SERIAL_M.

Issue 5 of TEST_SERIAL to the newly created work order, using serial nos. 1, 2, 3, 4, and 5

Receive 2 of TEST_SERIAL_M where the received items have serial nos. 1 and 2

When one looks at inventory for TEST_SERIAL: quantity on hand = 2, quantity demand 3, available -1.  This is the first error in inventory: The demand is 0 since 5 parts have already been issued to the work order to manufacture TEST_SERIAL_M and there are 2 parts available.  When one examines serial numbers there are serial numbers 6 and 7.

Inventory movements show:
Stock Adjustment +7 New quantity 7
Work Order Issue -7 New quantity 2

The receive function does not force entry of the serial numbers of TEST_SERIAL when receiving TEST_SERIAL_M.  This is incorrect since the reason serial numbers as well as batch/lot numbers is to provide component traceability.  The receive function must properly allocated the material and make a proper move notification.  Such never occurs.

After the work order is closed, not having consumed all of the issued items, unused items are never returned to inventory.  Three unused serial number items are deleted from the system with no trace.

There should be 5 of TEST_SERIAL in inventory and 2 in TEST_SERIAL_M parts in inventory.  If the 2 TEST_SERIAL parts in TEST_SERIAL_M parts need to be tracked back, reports could be written to generate the migration of the part.

In the US, there are regulations that require traceability of components.  The FAA (Federal Aviation Administration), DOD (Department of Defense), FDA (Food and Drug Administration), among others require such.  An example are components going into a jet aircraft engine:  Each piece of raw material is tracked by Foundry/lot, then serialized when cut into blanks, and traced throughout manufacture to the completed rotor and engine.  One would expect that such is also required by other countries.
Any thoughts/suggestions on what can be done to meet these requirements and correct the inventory errors?
Reply | Threaded
Open this post in threaded view
|

Re: Completed Work Order deletes unused serial inventory

Andrew Galuski
Robert,
Just a few comments.
I assume when you said " Work Order Issue -7 New quantity 2" you meant ' Work Order Issue -5 New quantity 2" since previously you said "Issue 5"

Returning Unused items to inventory should be a manual step I believe (Reverse the issue).  You cannot assume the items are going back into inventory.  They may have been scrapped/damaged etc.  We have the possibility of loss (not 100% yield).
You should see this anomaly in the W/O Costing screen as a usage variance.  Used more than expected

It sounds like what you are looking for would be a new 'module' or method of consumption,  Require each raw material that is serialized or batched to be input for each Serial or Batch produced.  So the issue would actually be done during receipt instead of as 2 steps.  Or you could still do 2 separate steps and then 'allocate' the raws to the finished good (like you allocate cash to multiple invoices in A/R).  You would need a method to allow for loss (Product issued or damaged during manufacturing).  Theses issues would remain unallocated but be noted as not used (to force everything put in to be noted).  

As for your "Demand" 0 instead of 3 you might try to replace the code in SelectProduct.php that looks like this (might be slightly different based on version)
$sql = "SELECT SUM(qtypu*(woitems.qtyreqd - woitems.qtyrecd)) AS woqtydemo
                FROM woitems INNER JOIN worequirements
                ON woitems.stockid=worequirements.parentstockid
                INNER JOIN workorders
                ON woitems.wo=workorders.wo
                AND woitems.wo=worequirements.wo
                INNER JOIN locationusers ON locationusers.loccode=workorders.loccode AND locationusers.userid='" .  $_SESSION['UserID'] . "' AND locationusers.canview=1
                WHERE  worequirements.stockid='" . $StockID . "'
                AND workorders.closed=0";
$ErrMsg = _('The workorder component demand for this product cannot be retrieved because');
$DemandResult = DB_query($sql, $db, $ErrMsg);
if (DB_num_rows($DemandResult) == 1) {
        $DemandRow = DB_fetch_row($DemandResult);
        $Demand+= $DemandRow[0];
}

With something like this. (Not tested)
$sql="SELECT SUM(qtypu * (woitems.qtyreqd - woitems.qtyrecd)) as reqqty,
                        SUM( - qty ) as issqty
                FROM worequirements INNER JOIN woitems
                ON woitems.wo=worequirements.wo
                AND woitems.stockid = worequirements.parentstockid
                INNER JOIN workorders on workorders.wo=woitems.wo
                LEFT OUTER JOIN stockmoves ON stockmoves.type =28
                AND reference=woitems.wo
                AND stockmoves.stockid='" . $StockID."'
                WHERE workorders.closed=0
                AND worequirements.stockid='" . $StockID."'";

$ErrMsg = _('The order details for this product cannot be retrieved because');
$RequirmentsResult = DB_query($sql,$db,$ErrMsg);
$WoResult = DB_query($sql,$db,$ErrMsg);
$WoRow = DB_fetch_row($WoResult);
if ($WoRow[0]!=null){
        $ReqQty =  floatval($WoRow[0]);
} else {
        $ReqQty = 0;
}
if ($WoRow[1]!=null){
        $IssQty =  floatval($WoRow[1]);
} else {
        $IssQty = 0;
}
$Demand+= ($ReqQty - $IssQty);


Best Regards,
Andrew Galuski


-----Original Message-----
From: rfthomas [mailto:[hidden email]]
Sent: Thursday, March 10, 2016 11:30 AM
To: [hidden email]
Subject: Re: [WebERP-developers] Completed Work Order deletes unused serial inventory

To provide further detail:

Work Orders do not properly deal with serialized inventory

To demonstrate the problem create the following two inventory items:

TEST_SERIAL – purchased/controlled/serial number TEST_SERIAL_M – manufactured/controlled/serial number

Create a BOM for TEST_SERIAL_M with one entity:

TEST_SERIAL QTY 1, Not Auto Issued

Add 7 of TEST_SERIAL to inventory, S/N 1, 2, …, 7

Create a Work Order to manufacture 5 of TEST_SERIAL_M.

Issue 5 of TEST_SERIAL to the newly created work order, using serial nos. 1, 2, 3, 4, and 5

Receive 2 of TEST_SERIAL_M where the received items have serial nos. 1 and 2

When one looks at inventory for TEST_SERIAL: quantity on hand = 2, quantity demand 3, available -1.  This is the first error in inventory: The demand is
0 since 5 parts have already been issued to the work order to manufacture TEST_SERIAL_M and there are 2 parts available.  When one examines serial numbers there are serial numbers 6 and 7.

Inventory movements show:
Stock Adjustment +7 New quantity 7
Work Order Issue -7 New quantity 2

The receive function does not force entry of the serial numbers of TEST_SERIAL when receiving TEST_SERIAL_M.  This is incorrect since the reason serial numbers as well as batch/lot numbers is to provide component traceability.  The receive function must properly allocated the material and make a proper move notification.  Such never occurs.

After the work order is closed, not having consumed all of the issued items, unused items are never returned to inventory.  Three unused serial number items are deleted from the system with no trace.

There should be 5 of TEST_SERIAL in inventory and 2 in TEST_SERIAL_M parts in inventory.  If the 2 TEST_SERIAL parts in TEST_SERIAL_M parts need to be tracked back, reports could be written to generate the migration of the part.

In the US, there are regulations that require traceability of components.
The FAA (Federal Aviation Administration), DOD (Department of Defense), FDA (Food and Drug Administration), among others require such.  An example are components going into a jet aircraft engine:  Each piece of raw material is tracked by Foundry/lot, then serialized when cut into blanks, and traced throughout manufacture to the completed rotor and engine.  One would expect that such is also required by other countries.
Any thoughts/suggestions on what can be done to meet these requirements and correct the inventory errors?




--
View this message in context: http://weberp-accounting.1478800.n4.nabble.com/Completed-Work-Order-deletes-unused-serial-inventory-tp4658530p4658553.html
Sent from the web-ERP-developers mailing list archive at Nabble.com.

------------------------------------------------------------------------------
Transform Data into Opportunity.
Accelerate data analysis in your applications with Intel Data Analytics Acceleration Library.
Click to learn more.
http://pubads.g.doubleclick.net/gampad/clk?id=278785111&iu=/4140
_______________________________________________
Web-erp-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/web-erp-developers
------------------------------------------------------------------------------
Transform Data into Opportunity.
Accelerate data analysis in your applications with
Intel Data Analytics Acceleration Library.
Click to learn more.
http://pubads.g.doubleclick.net/gampad/clk?id=278785111&iu=/4140
_______________________________________________
Web-erp-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/web-erp-developers
Reply | Threaded
Open this post in threaded view
|

RE: Completed Work Order deletes unused serial inventory

rfthomas

Andrew,

 

The number issued should have been 5, my error.

 

For all non-controlled items we utilize auto issue.  We then manually adjust inventory for losses.

 

We see no clean mechanism to return unused issued items to inventory.  The only means involves making a manual inventory adjustment, i.e. returning the item to inventory.  We understand that items could be consumed (used/damaged) in manufacture and not be returned to inventory.

 

Another problem that is created is the costing for the work order is wrong due to the all items issued are assumed to be consumed by the work order creating incorrect costs of manufacture.

 

We could probably get around this limitation by only issuing items one at a time and only receiving completed items one at a time, i.e. issue one serial number, receive one finished item, so on and so forth until the desired quantity has been produced.  The way the system is currently designed this is very labor intensive and would slow down production appreciably.  Another method would be to create an individual work order for each piece.  Again the extra data entry becomes an issue when there is no automation for creating such.

 

Handling scrap and MRB is another area of challenge.

 

We will look at making the script changes that you have outlined.

 

Sincerely,

Robert F. Thomas

cid:image001.gif@01C71ABB.312C1E90

 44 Industrial Way
Norwood, MA USA 02062
(  Office Phone - (781) 329-9200
* mail to: [hidden email]

 

 

From: Andrew Galuski [via webERP accounting] [mailto:[hidden email]]
Sent: Thursday, March 10, 2016 2:59 PM
To: rfthomas <[hidden email]>
Subject: Re: Completed Work Order deletes unused serial inventory

 

Robert,
Just a few comments.
I assume when you said " Work Order Issue -7 New quantity 2" you meant ' Work Order Issue -5 New quantity 2" since previously you said "Issue 5"

Returning Unused items to inventory should be a manual step I believe (Reverse the issue).  You cannot assume the items are going back into inventory.  They may have been scrapped/damaged etc.  We have the possibility of loss (not 100% yield).
You should see this anomaly in the W/O Costing screen as a usage variance.  Used more than expected

It sounds like what you are looking for would be a new 'module' or method of consumption,  Require each raw material that is serialized or batched to be input for each Serial or Batch produced.  So the issue would actually be done during receipt instead of as 2 steps.  Or you could still do 2 separate steps and then 'allocate' the raws to the finished good (like you allocate cash to multiple invoices in A/R).  You would need a method to allow for loss (Product issued or damaged during manufacturing).  Theses issues would remain unallocated but be noted as not used (to force everything put in to be noted).  

As for your "Demand" 0 instead of 3 you might try to replace the code in SelectProduct.php that looks like this (might be slightly different based on version)
$sql = "SELECT SUM(qtypu*(woitems.qtyreqd - woitems.qtyrecd)) AS woqtydemo
                FROM woitems INNER JOIN worequirements
                ON woitems.stockid=worequirements.parentstockid
                INNER JOIN workorders
                ON woitems.wo=workorders.wo
                AND woitems.wo=worequirements.wo
                INNER JOIN locationusers ON locationusers.loccode=workorders.loccode AND locationusers.userid='" .  $_SESSION['UserID'] . "' AND locationusers.canview=1
                WHERE  worequirements.stockid='" . $StockID . "'
                AND workorders.closed=0";
$ErrMsg = _('The workorder component demand for this product cannot be retrieved because');
$DemandResult = DB_query($sql, $db, $ErrMsg);
if (DB_num_rows($DemandResult) == 1) {
        $DemandRow = DB_fetch_row($DemandResult);
        $Demand+= $DemandRow[0];
}

With something like this. (Not tested)
$sql="SELECT SUM(qtypu * (woitems.qtyreqd - woitems.qtyrecd)) as reqqty,
                        SUM( - qty ) as issqty
                FROM worequirements INNER JOIN woitems
                ON woitems.wo=worequirements.wo
                AND woitems.stockid = worequirements.parentstockid
                INNER JOIN workorders on workorders.wo=woitems.wo
                LEFT OUTER JOIN stockmoves ON stockmoves.type =28
                AND reference=woitems.wo
                AND stockmoves.stockid='" . $StockID."'
                WHERE workorders.closed=0
                AND worequirements.stockid='" . $StockID."'";

$ErrMsg = _('The order details for this product cannot be retrieved because');
$RequirmentsResult = DB_query($sql,$db,$ErrMsg);
$WoResult = DB_query($sql,$db,$ErrMsg);
$WoRow = DB_fetch_row($WoResult);
if ($WoRow[0]!=null){
        $ReqQty =  floatval($WoRow[0]);
} else {
        $ReqQty = 0;
}
if ($WoRow[1]!=null){
        $IssQty =  floatval($WoRow[1]);
} else {
        $IssQty = 0;
}
$Demand+= ($ReqQty - $IssQty);


Best Regards,
Andrew Galuski


-----Original Message-----
From: rfthomas [mailto:[hidden email]]
Sent: Thursday, March 10, 2016 11:30 AM
To: [hidden email]
Subject: Re: [WebERP-developers] Completed Work Order deletes unused serial inventory

To provide further detail:

Work Orders do not properly deal with serialized inventory

To demonstrate the problem create the following two inventory items:

TEST_SERIAL – purchased/controlled/serial number TEST_SERIAL_M – manufactured/controlled/serial number

Create a BOM for TEST_SERIAL_M with one entity:

TEST_SERIAL QTY 1, Not Auto Issued

Add 7 of TEST_SERIAL to inventory, S/N 1, 2, …, 7

Create a Work Order to manufacture 5 of TEST_SERIAL_M.

Issue 5 of TEST_SERIAL to the newly created work order, using serial nos. 1, 2, 3, 4, and 5

Receive 2 of TEST_SERIAL_M where the received items have serial nos. 1 and 2

When one looks at inventory for TEST_SERIAL: quantity on hand = 2, quantity demand 3, available -1.  This is the first error in inventory: The demand is
0 since 5 parts have already been issued to the work order to manufacture TEST_SERIAL_M and there are 2 parts available.  When one examines serial numbers there are serial numbers 6 and 7.

Inventory movements show:
Stock Adjustment +7 New quantity 7
Work Order Issue -7 New quantity 2

The receive function does not force entry of the serial numbers of TEST_SERIAL when receiving TEST_SERIAL_M.  This is incorrect since the reason serial numbers as well as batch/lot numbers is to provide component traceability.  The receive function must properly allocated the material and make a proper move notification.  Such never occurs.

After the work order is closed, not having consumed all of the issued items, unused items are never returned to inventory.  Three unused serial number items are deleted from the system with no trace.

There should be 5 of TEST_SERIAL in inventory and 2 in TEST_SERIAL_M parts in inventory.  If the 2 TEST_SERIAL parts in TEST_SERIAL_M parts need to be tracked back, reports could be written to generate the migration of the part.

In the US, there are regulations that require traceability of components.
The FAA (Federal Aviation Administration), DOD (Department of Defense), FDA (Food and Drug Administration), among others require such.  An example are components going into a jet aircraft engine:  Each piece of raw material is tracked by Foundry/lot, then serialized when cut into blanks, and traced throughout manufacture to the completed rotor and engine.  One would expect that such is also required by other countries.
Any thoughts/suggestions on what can be done to meet these requirements and correct the inventory errors?




--
View this message in context: http://weberp-accounting.1478800.n4.nabble.com/Completed-Work-Order-deletes-unused-serial-inventory-tp4658530p4658553.html
Sent from the web-ERP-developers mailing list archive at Nabble.com.

------------------------------------------------------------------------------
Transform Data into Opportunity.
Accelerate data analysis in your applications with Intel Data Analytics Acceleration Library.
Click to learn more.
http://pubads.g.doubleclick.net/gampad/clk?id=278785111&iu=/4140
_______________________________________________
Web-erp-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/web-erp-developers
------------------------------------------------------------------------------
Transform Data into Opportunity.
Accelerate data analysis in your applications with
Intel Data Analytics Acceleration Library.
Click to learn more.
http://pubads.g.doubleclick.net/gampad/clk?id=278785111&iu=/4140
_______________________________________________
Web-erp-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/web-erp-developers


To unsubscribe from Completed Work Order deletes unused serial inventory, click here.
NAML