cancel
Showing results for 
Search instead for 
Did you mean: 

Query on subsequent PO after initial PO

Former Member
0 Kudos

Hi Experts,

Our client has a requirement wherein items tagged as Initial stocks (UDF in OITM) will be created with PO and they need to track or an alert if this items has no subsequent PO created 90 days after first PO doc date. I will attach the query as alert to a certain user when the condition is met.

Our version is 8.81 PL09.

Many thanks.

Don

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Try:

SELECT T0.ItemCode,Max(T1.DocDate) 'Last PO'

FROM POR1 T0

JOIN OPOR T1 ON T1.DocEntry=T0.DocEntry

JOIN OITM T2 ON T2.ItemCode=T0.ItemCode AND T2.U_STATUS='IS'

WHERE DateDiff(dd,T1.DocDate,GetDate())>90

Former Member
0 Kudos

Thanks Gordon!

Answers (3)

Answers (3)

Former Member
0 Kudos

Hi Don,

As per my understanding whenever the PO is created your program update IS in UDF for this type of item and you will need to send the reminder if no Purchase order created for the item after 90 days. You can simply join on OITL table for the item and use docdate and current system date to find out items for which purchase order is not created in last 90 days. Your select query will look like

Select a.Itemcode from OITM where a.u_status = 'IS' and a.itemcode NOT IN (Select a.ITEMCODE FROM OITM a TABLE WHERE INNER JOIN OITL b ON a.itemcode = b.itemcode where a.u_status = 'IS' AND b.doctype = 20 and DATEDIFF(DAY,DocDate,GETDATE()) <= 90)

The above query will gives the list of item having staus 'IS' and have not received within 90 days.

Hope this helps.


Thanks,

Nitin

Former Member
0 Kudos

Hi Nitin,

Thanks for the query but the result should give a list of items having status 'IS' and have not been created with another PO (order) 90 days from the first PO date.

Hope this makes it more clear.

Thanks,

Don

Former Member
0 Kudos

Hi Don,

This query should be a simple one. What is the exact UDF name?

Thanks,

Gordon

Former Member
0 Kudos

Hi Gordon,

UDF name is U_STATUS, should be tagged as 'IS' or Initial Stocks.

Thanks,

Don

Former Member
0 Kudos

'IS' here may not be exact word because it represent a object type. Check what is the exact value of your UDF.

Former Member
0 Kudos

Hi Gordon,

UDF is U_STATUS while valid values are Reg and IS.

Thanks again,

Don

former_member218051
Active Contributor
0 Kudos

Hi Don,

you can try this way in test DB.

Make a UDF in PO as a Next PO Date.

Through FMS auto populate the UDF.

Based on the UDF then it will be very easy to frame the query and alert the user for subsequent PO.

you can make use of following in the where clause

datediff(D,U_Nxt_PO_Date,GetDate()) = 0

Hope this helps you.

Thanking you

Malhaarr