JanPie
Intrepid explorer

Purchase Status - missing value on reports

Jump to solution

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

0 Kudos
1 Solution
MurrayPeters
Frequent contributor

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

 

View solution in original post

2 Replies
MurrayPeters
Frequent contributor

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

 

View solution in original post

Hi Murray,
Thank you for looking into this! It does indeed look like a defect in the query.

I opened new case and linked this discussion and your findings.
Hopefully it's an easy fix.
Kind regards,
Jan
0 Kudos