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,
