Hi Everyone,
It's me again, with another question related to reporting on POs in FNMS.
I added Purchase - Status to the report, but after running it only shows 2 groups of Status: Empty or New.
This is completely wrong, because we don't have a single PO without a status (is that even possible?)
I tried using Purchase Order - Status instead, but the same problem persists. In normal view from Procurement > All Purchases, I can see all 4 status being used.
Can anyone replicate this problem on their end? I hope it's not corruption on our end.
Kind regards,
Jan
‎May 31, 2019 02:52 AM
Hi Jan - I am pretty certain you have run into a defect.
I reproduced what you saw and profiled the query made by the report and it looks like it has a mistake in a join.
I’d suggest you submit a new ticket to Flexera support (feel free to make reference to this discussion).
For reference, the query that is faulty is based on the following subquery which doesn't return the right Status values (I expect the red items below should refer to the PO detail object not the PO one).
-- Custom view query for purchase order details
SELECT PurchaseOrderDetailInfo.PurchaseOrderDetailID AS [PurchaseOrderLineID],
PurchaseOrderDetailInfo.ItemDescription AS [Description],
PurchaseOrderStatusI18N.DefaultValue AS [PurchaseOrderStatus],
PurchaseOrderDetailInfo.PurchaseOrderID AS [PurchaseOrderID]
FROM PurchaseOrderDetailWithPONoSKUMatching AS PurchaseOrderDetailInfo
-- Additional joins
LEFT OUTER JOIN PurchaseOrderStatusI18N ON PurchaseOrderStatusI18N.PurchaseOrderStatusID = PurchaseOrderDetailInfo.PurchaseOrderStatusID
- Murray
‎May 31, 2019 03:45 AM - edited ‎May 31, 2019 04:07 AM
Hi Jan - I am pretty certain you have run into a defect.
I reproduced what you saw and profiled the query made by the report and it looks like it has a mistake in a join.
I’d suggest you submit a new ticket to Flexera support (feel free to make reference to this discussion).
For reference, the query that is faulty is based on the following subquery which doesn't return the right Status values (I expect the red items below should refer to the PO detail object not the PO one).
-- Custom view query for purchase order details
SELECT PurchaseOrderDetailInfo.PurchaseOrderDetailID AS [PurchaseOrderLineID],
PurchaseOrderDetailInfo.ItemDescription AS [Description],
PurchaseOrderStatusI18N.DefaultValue AS [PurchaseOrderStatus],
PurchaseOrderDetailInfo.PurchaseOrderID AS [PurchaseOrderID]
FROM PurchaseOrderDetailWithPONoSKUMatching AS PurchaseOrderDetailInfo
-- Additional joins
LEFT OUTER JOIN PurchaseOrderStatusI18N ON PurchaseOrderStatusI18N.PurchaseOrderStatusID = PurchaseOrderDetailInfo.PurchaseOrderStatusID
- Murray
‎May 31, 2019 03:45 AM - edited ‎May 31, 2019 04:07 AM
‎May 31, 2019 04:10 AM