Hi community,
We have a small issue with the POP List. Sometimes, an order is fully received but hasn't been invoiced yet. In the system, it still appears as "Live." It's okay, but we need a better way to see what's going on. Each user as their specific filter set up to make sure they are on the top of the orders.
To solve this, we came up with an idea. We created a special Analysis code called 'Received.' When an order is fully received, users manually mark it as 'Full'.
The trouble is, this process is a bit repetitive and not very efficient. So, I've been experimenting and came up with the following SQL Script.
Feedback about the code would also be appreciated. Also, is there any other way of achieving the same, or improving the below code?
UPDATE PR SET PR.AnalysisCode7 = CASE WHEN ReceivedLineCount >= TotalLines THEN 'Full' ELSE '' END FROM POPOrderReturn AS PR INNER JOIN ( SELECT DocumentNo, COUNT(CASE WHEN ConfirmationIntentTypeID <> 2 THEN 1 ELSE 0 END) AS TotalLines FROM POPOrderReturn INNER JOIN POPOrderReturnLine ON POPOrderReturn.POPOrderReturnID = POPOrderReturnLine.POPOrderReturnID WHERE ConfirmationIntentTypeID <> 2 AND LineTypeID <> 2 GROUP BY DocumentNo ) AS TotalLines ON PR.DocumentNo = TotalLines.DocumentNo INNER JOIN ( SELECT DocumentNo, COUNT(CASE WHEN (ConfirmationIntentTypeID <> 2 AND ReceiptReturnQuantity >= LineQuantity) THEN POPOrderReturnLine.ItemCode END) AS ReceivedLineCount FROM POPOrderReturn INNER JOIN POPOrderReturnLine ON POPOrderReturn.POPOrderReturnID = POPOrderReturnLine.POPOrderReturnID WHERE ConfirmationIntentTypeID <> 2 AND LineTypeID <> 2 GROUP BY DocumentNo ) AS Received ON PR.DocumentNo = Received.DocumentNo;
Thanks for your help.
Kind regards,