Hello Gurus,
I am assigned the responsibility of collecting data regarding the date of generation for a purchase order (PO) suggestion and the date it was officially created or converted into a PO. The objective is to calculate the difference in days between the suggestion’s creation date and the date it was approved as a PO.
Any idea or suggestions ?
There is a table called SugPoDtl. There is a field in there called CreatedOn. Compare this date to the OrderDate in the POHeader table. I think that should get you pretty close.
Hi @NateS, thank you for your reply.
Issue is I don’t have this date in SugPoDtl table. All I see is Order by date and due date.
In my table, it is field #87, the last field in the table.
Since we haven’t run PO suggestions yet, we don’t have any data in our table to compare to so I can’t show you the exact setup.
Probably a version thing, that field doesn’t exist in 10.2.400 either.
Also, when a suggestion becomes a purchase order (PO), it no longer shows up in the SugPoDtl table. Even if I have the creation date (CreatedOn), I’ll need to figure out how to compare it. My SQL skills are limited, but I can write it to UD tables as a secondary step, I suppose.
SuggPO detail is not a history table, it gets cleared and recreated every time you run suggestions/MRP. Also, they get removed when the PO’s are created, or they can be deleted by the users.
It’s going to be a challenge to verify when the suggestion was created.
@itsme You might be able to make this work from PartDtl. PartDtl is what gets read to create the suggestions and then when the PO is created it will also create a PartDtl record. If you have a CreatedDate field you can set when null in a data directive. You might even get into hours from suggestion to PO generation.
It would potentially be a one to many since suggestions use days of supply and aggregate all demand for a suggestion/PO, so you would have to check all of the requirements created dates against the PO created date.
Finally, after months, I found a solution that works for me!
SQL Part:
I had a custom SQL database in which created a table called dbo.Suggestions. Every hour, a job runs that inserts fields from the Erp.SugPoDtl table into this table. The new DateCreated field, available after the upgrade, came in handy.
Kinetic Part:
CallContext was a game changer! I created a pre-processing method directive at Erp.POSugg.CheckGenerate and set CallContextBpmData.Number01 to SugPODtl.SugNum. Then, at the POHeader data directive (In-trans), I set custom field called Calc_SugNum_c to `CallContextBpmData.Number01’ based on some condition.
SQL Integration:
I queried the POHeader, POHeader_UD, and SugPoDtl tables to get the needed data, added some calculated fields, and sent it all to PowerBI.
It was a bit of work, but learned a lot finding solution to this. Thanks to everyone who helped!
