MRP - finding parts to order - fast

Thought this might be useful to someone:

Just figured out how to find all parts needing attention for PO's and/or WO's, and cycle through them--FAST.

Material Planning WindowSearch (use the magnifying glass, NOT the binoculars)

Search windowDataSQL

Use this to find all parts whose inventory level will--at any time--go below 0 for parts with no safety stock, and below the safety stock level for parts with safety stock:

QTY_ON_HAND + QTY_ON_ORDER
AND FABRICATED = 'Y'
AND PURCHASED = 'N'
AND QTY_IN_DEMAND > 0
OR
QTY_ON_HAND + QTY_ON_ORDER
AND FABRICATED = 'Y'
AND PURCHASED = 'N' AND QTY_IN_DEMAND > 0
AND QTY_ON_HAND

Use this to find all parts whose actual inventory level--at any point--will go below 0.

Normal 0 false false false EN-US X-NONE X-NONE
/* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman","serif";}
QTY_ON_HAND + QTY_ON_ORDER 0 and qty_on_order >=0

The above are for Fabricated parts. For purchased, just switch the 'Y' and 'N'.

One caveat: Qty In Demand includes On Hold orders, so sometimes that can be puzzling if you run the MRP window not showing On Hold Orders, like I do.

Find more posts tagged with

Comments

Legacy Contributor

This is great. Just showed our Planner this and I think I made his month. Thanks Tom!

Legacy Contributor

Well, glory to God! Glad it helped.

There's another even faster way I've found. This brings up a list of all planned orders, regardless:

1. Material Planning Window
2. Binoculars Search
3. Check only "Planned orders rls w/in [put # of days in, I use 800]
4. Click Search
5. This brings up a browse window. It is a list of all parts that have a Planned Order (PL) on them.
6. Double-click the first item in the Browse window
7. Do whatever is needed (make a WO from a PL, delete the PL, increase/decrease the Qty on a supply WO, etc)
8. Press CTRL + F11 to scroll down the search to the next part. CTRL + F10 will scroll back up the search if you want to see a previous part.

If you only want to see, say, parts with demand over the next month, just put 30 in the "Planned order rls w/in" box. 5 for just the next week. etc.

Legacy Contributor

BTW, here's the series of SQL statements I developed:

Sql code for doing mrp through the material planning window search
SQL CODE FOR FABRICATED (For purchased parts, just insert "Purchased" for "Fabricated". On my general search ("all shortages", I don't use "AND PURCHASED = N" because I want to look at the parts that are both purchased and fabricated.)

0. all shortages

QTY_ON_HAND + QTY_ON_ORDER
AND QTY_IN_DEMAND > 0
AND FABRICATED = 'Y'
OR
QTY_ON_HAND + QTY_ON_ORDER
AND QTY_IN_DEMAND > 0
AND QTY_ON_HAND
AND FABRICATED = 'Y'
or
QTY_ON_HAND + QTY_ON_ORDER
AND QTY_IN_DEMAND > 0
and qty_on_order >=0
AND FABRICATED = 'Y'
OR
QTY_ON_HAND + QTY_ON_ORDER
AND QTY_IN_DEMAND > 0
AND FABRICATED = 'Y'
OR
QTY_ON_HAND + QTY_ON_ORDER
AND FABRICATED = 'Y'

1. absolute shortages ONLY

QTY_ON_HAND + QTY_ON_ORDER
AND QTY_IN_DEMAND > 0
AND FABRICATED = 'Y'
AND PURCHASED = 'N'

2. Deduct safety stock shortages

QTY_ON_HAND + QTY_ON_ORDER
AND FABRICATED = 'Y'
AND PURCHASED = 'N'
OR
QTY_ON_HAND + QTY_ON_ORDER
AND QTY_IN_DEMAND > 0
AND FABRICATED = 'Y'
AND PURCHASED = 'N'
AND QTY_IN_DEMAND > 0
AND QTY_ON_HAND

3. safety stock shortages ONLY

QTY_ON_HAND + QTY_ON_ORDER
AND FABRICATED = 'Y'
AND PURCHASED = 'N'

[Updated on 10/2/2014 1:42 PM]

Legacy Contributor

Thanks again! This is great.

Would you say that you run through your exceptions through the material planning search window rather than the binoculars view?

Adam

Legacy Contributor

Actually you can run SQL through either.

In the Magnifying Glass Search in Material Planning: run the search, click DataSql...

If you use the Binoculars Search, to use SQL:

1. Run a search--any search, doesn't matter.
2. See the Sql... at the top-left? Click it, and now you have an SQL window you can use. Pop in your SQL and you have the best of both worlds...use of customer SQL search, with the auto-scroll using CTRL + F11 as well.

I like the Binoculars search because of the CTRL-F11 advance feature. And being able to go back, using CTRL-F10.

Legacy Contributor

This is magical.

2011-10-27_CH-Stammtisch_2.pdf

Legacy Contributor

Tom,

Our planner would like to see the query for "QTY IN DEMAND" based on current work order demand, rather than customer order demand. Any way to tweak it to show that? I'll write a report if not, but it would be great to see it in the Material Planning Window.

Thanks!

Adam

[Updated on 10/7/2014 4:16 PM]

edit: grammar

Legacy Contributor

That's over my head, I don't know how to do that. I'd go to Infor Support and ask which table is providing the "Peg to" column, and use that in a report. But why exclude customer orders? If the part is on a customer order line, its real demand. If the customer order line has a linked WO for the part, then only the CO shows in MRP, so there is no duplication.

ztrue

You could do this with a custom report and a query that looks at all on-hold or unreleased work orders, totaling the qty by part ID.

The query itself would be something like this:
select sum(DESIRED_QTY) as DEMAND_QTY, PART_ID from WORK_ORDER where type = 'W' and Status in ('h', 'u') group by part_ID

Legacy Contributor

Yeah, I agree. It has to do with the way we are using the system. More or less we have our work orders linked to customer orders via user defined fields. It is a remnant that persists...

If I understand it correctly, we will issue material for work orders that we do not have material for into the future and bring material in to fill them. So basically what he wants to see is the raw material requirements for the work orders created in the future. Of course, we have a user defined field in part maintenance listing raw material component, so it is a column that I am able to make visible in the search window.

Don't ask about MRP. I understand it ought to be filling this role.

Legacy Contributor

Using the UDF instead of material requirements on WO operations is short-circuiting your planning. Visual is built to handle multi-level BOM's and you are able to track the entire demand structure through the BOM Report, Explode Estimated Costs, or Synergy's Trial Kit application. That way your purchaser can actually use the MRP search to pull up parts with demand, and it is WAY faster!

Legacy Contributor

Sigh...hopefully someday soon, Tom. You can be sure I'm working on it!

Legacy Contributor

Zach,

I ended up doing something similar to what you recommended. Basically selected all Unreleased and On Hold work orders.

Thanks again!

Adam

Copyright © 2025 Infor. All rights reserved.