Hello,
Is there a list of steps to do to remove closed work orders from the financials WIP report for month end closing? I know of some of the steps but we don't know which steps to do in what order.
Thank you,
Paul
Just to clarify, it's the WIP Balance report in the Post Manufacturing Journals window.
Hi,
When we scrap a part while the part is in process, we must ship it to not have the cost on the WIP. So we created a SCRAP customer order and we ship the part against this scrap CO.
Nancy
The real question (and we've had this for years) is how to correctly close work orders that don't ship. We create work orders for maintenance and, of course, make to stock that don't ship. I came on to ask just that question and will right after I send this but you might watch that thread as well.
Not sure I fully understand the question but are you referring to WO's that don't get received to inventory or are tied to a SO (sales order)? WO's that you are manually changing the status to Close?
If so, rather than "close" the WO you should "cancel" it. When you cancel it the costs of the WO will flow from WIP to the expense accounts of the Product code on the WO header.
Any work order that does not ship or even get received to inventory gets canceled. The costs just then go to the default GL account and it drops off of the WIP Balance Report on the next run of Costing Utilities. That's easy. My problem is after running costing utilities, there are closed work orders on the WIP balance report. In costing tools, I can set it as a posting candidate and the usually works. But I would like to know if there is a step by step process for closing the month and removing unwanted WIP Balance values.
Chris,
You mentioned this problem at the November 2019 OINK meeting. We were also having a similar problem, but were not using the official Visual maintenance module to create maintenance work orders. Kim Broom posted the following solution on 12/9/2019 in the VMIUG-TEC Google Group:"We handle removal of Work Orders for capturing internal equipment and/or facility maintenance through Customer Orders. We have jobs shipped out to our Company name with zero price/revenue. We have product codes that point to either an Expense or Capital Asset based on a dollar threshold.
When the job is complete, it ships out and leaves Wip, and hits either the Income Statement or the Balance Sheet based on the qualifications.
We handle Fixtures/Tools the same way. We build them and ship them out. Most are expensed when complete but the Big Ones will be Capitalized and be subjected to Depreciation."
I tested the solution, initially with poor results - below is what I posted at the time:"We hit a bizarre bug in Visual when testing in our test database to clear the maintenance work orders from WIP by associating those maintenance work orders with a customer order, and then shipping the customer order. We set up a customer order with a single line having a quantity of 20,000 pieces. I then created a SQL statement to associate 13,008 maintenance work orders with that one customer order line by creating 13,008 entries in the DEMAND_SUPPLY_LINK table. In Shipping Entry I specified the customer order ID, and then clicked the Ship All button. 6,992 pieces were shipped against the first linked work order (20,000 pieces - 13,008 work orders for Qty 1 each is exactly 6,992), the first work order is now showing 6,992 pieces complete, and two inventory transactions were created, the first with 6,992 pieces coming from the first linked work order, and 20,000 pieces shipping for the customer order."
I then tried another slightly modified approach, which we have been using successfully since December 2019. The method suggested by Kim (and Lynn) worked to take the old maintenance work orders off WIP by associating those work orders with a customer order and then shipping that customer order. We did NOT assign a part ID to the maintenance work orders. The solution requires one customer order line per work order lot. That meant that we needed to add 13,000+ customer order lines and 13,000+ demand supply link lines. We created one customer order per year (CLEARWIP2019, CLEARWIP2018, CLEARWIP2017, CLEARWIP2016) as it took Visual's Shipping Entry module several hours to ship a customer order with just the maintenance work orders for one year (and probably 5+ minutes just to open the customer order in Shipping Entry). In the future we will create a monthly customer order to clear the maintenance work orders from WIP. All of our maintenance work orders have a work order base ID that begins with EM (ex: EMUM for unplanned maintenance and EMPM for preventive maintenance) and ends with -YY (ex: -19, -18, -17, -16) to represent the year. Below is the process documentation that I created to document the process of automatically adding the customer order lines and demand supply link lines to a customer order for all closed maintenance work orders for a specific year (note that the SQL syntax is Oracle specific, and will need some adjustment for use with a SQL Server back end - I am not sure if ROW_NUMBER is a valid analytic statement in SQL Server). One problem that you will find is that the close dates of the maintenance work orders will change to the date of the shipment, so you will want to record the original close dates and then restore those dates.
1. Create a customer order with an Order ID of CLEARWIP20nn (ex: CLEARWIP2019) where the last two digits represent the two digit year, with no order lines.2. In the following SQL statements change 19 to match the last two digits of the year.3. Execute the SQL statements and the COMMIT; that follows.4. After executing the SQL statements, use Shipping Entry to ship all lines in the customer order.5. Run all steps in Costing Utilities.
SQL statements:INSERT INTO CUST_ORDER_LINE( CUST_ORDER_ID, LINE_NO, ORDER_QTY, USER_ORDER_QTY, UNIT_PRICE, TRADE_DISC_PERCENT, EST_FREIGHT, COMMISSION_PCT, TOTAL_ACT_FREIGHT, TOTAL_SHIPPED_QTY, TOTAL_USR_SHIP_QTY, TOTAL_AMT_SHIPPED, TOTAL_AMT_ORDERED, ALLOCATED_QTY, ACCEPT_EARLY, DAYS_EARLY, DD250_REQUIRED, STATUS_EFF_DATE, SITE_ID, LINE_STATUS, ENTERED_BY, EMAIL_NOTIFICATION, POSTING_CANDIDATE)SELECT N'CLEARWIP2019' CUST_ORDER_ID, 0+ (ROW_NUMBER() OVER (ORDER BY WO.BASE_ID, WO.LOT_ID)) LINE_NO, 1 ORDER_QTY, 1 USER_ORDER_QTY, 0 UNIT_PRICE, 0 TRADE_DISC_PERCENT, 0 EST_FREIGHT, 0 COMMISSION_PCT, 0 TOTAL_ACT_FREIGHT, 0 TOTAL_SHIPPED_QTY, 0 TOTAL_USR_SHIP_QTY, 0 TOTAL_AMT_SHIPPED, 0 TOTAL_AMT_ORDERED, 0 ALLOCATED_QTY, N'Y' ACCEPT_EARLY, 9999 DAYS_EARLY, N'N' DD250_REQUIRED, SYSDATE STATUS_EFF_DATE, N'MFC' SITE_ID, N'A' LINE_STATUS, N'SYSADM' ENTERED_BY, N'N' EMAIL_NOTIFICATION, N'N' POSTING_CANDIDATEFROM WORK_ORDER WOWHERE WO.TYPE='W' AND WO.BASE_ID LIKE N'EM%' AND WO.SPLIT_ID=N'0' AND WO.SUB_ID=N'0' AND WO.STATUS=N'C' AND WO.DESIRED_QTY>WO.RECEIVED_QTY AND WO.BASE_ID LIKE N'EM%-19'ORDER BY WO.BASE_ID, WO.LOT_ID;INSERT INTO DEMAND_SUPPLY_LINK( ID, DEMAND_TYPE, DEMAND_BASE_ID, DEMAND_SEQ_NO, SUPPLY_TYPE, SUPPLY_BASE_ID, SUPPLY_LOT_ID, SUPPLY_SPLIT_ID, SUPPLY_SUB_ID, ALLOCATED_QTY, RECEIVED_QTY, ISSUED_QTY, SUPPLY_MODE, CREATE_DATE, USER_ID, DEMAND_SITE_ID, SUPPLY_SITE_ID)SELECT DSL.ID+ (ROW_NUMBER() OVER (ORDER BY WO.BASE_ID, WO.LOT_ID)) ID, N'CO' DEMAND_TYPE, 'CLEARWIP2019' DEMAND_BASE_ID, 0+ (ROW_NUMBER() OVER (ORDER BY WO.BASE_ID, WO.LOT_ID)) DEMAND_SEQ_NO, N'WO' SUPPLY_TYPE, WO.BASE_ID SUPPLY_BASE_ID, WO.LOT_ID SUPPLY_LOT_ID, N'0' SUPPLY_SPLIT_ID, N'0' SUPPLY_SUB_ID, 1 ALLOCATED_QTY, 0 RECEIVED_QTY, 0 ISSUED_QTY, N'A' SUPPLY_MODE, SYSDATE CREATE_DATE, N'SYSADM' USER_ID, N'MFC' DEMAND_SITE_ID, N'MFC' SUPPLY_SITE_IDFROM (SELECT MAX(ID) ID FROM DEMAND_SUPPLY_LINK) DSL, WORK_ORDER WOWHERE WO.TYPE='W' AND WO.BASE_ID LIKE N'EM%' AND WO.SPLIT_ID=N'0' AND WO.SUB_ID=N'0' AND WO.STATUS=N'C' AND WO.DESIRED_QTY>WO.RECEIVED_QTY AND WO.BASE_ID LIKE N'EM%-19'ORDER BY WO.BASE_ID, WO.LOT_ID;COMMIT;
Charles HooperSenior Software EngineerMeadville Forging Company
Thanks for the shout out 2 years ago. I was referenced before I even joined this website!
