Are you tired of dealing with inventory discrepancies that result in wasted space, money, and time? If you’re a business owner or inventory manager, you know how critical it is to keep track of your inventory levels and purchase orders. Fortunately, Elastic Windows offers a solution to help you stay on top of your inventory and prevent duplicate Purchase Orders.
Let’s take the example of Buttercup Cosmetics. This company created a purchase order for 2,000 Keychain-attachable lip gloss bottles. They planned to buy the keychains at a later time. However, they later forgot about this order and ordered 1,950 keychains and 2,000 new bottles. This resulted in a surplus of 2,000 bottles in their inventory and less money in their bank account.
With Elastic Windows, this situation could have been avoided. By using a simple SQL code, an Elastic Window would have popped up with an alert indicating that there was already an open purchase order for the same item. The window would have provided information about the open purchase order, such as the purchase order number and the line status:
This simple alert would have saved Buttercup Cosmetics a significant amount of money and prevented them from wasting inventory space. They would have been able to allocate their resources more efficiently and avoid unnecessary expenses. Here is the SQL code for the Elastic Window:
CREATE VIEW [dbo].[adv_PurchaseOrderEntry_Reviews]
AS
SELECT A.ITEMNMBR,
CASE
WHEN A.QTYORDER - P.QTYSHIpped > 1 THEN -1
ELSE 0
END AS ALERT_1,
CASE
WHEN A.QTYORDER - P.QTYSHIpped > 1 THEN
' PLEASE NOTE:' + Char(13) + Char(13)
+ ' Item found in another PO: '
ELSE ''
END AS MESSAGE_1,
A.PONUMBER [Found In],
Rtrim(Convert(Varchar(50), Coalesce(POLNESTA, 0)))
+ '-' + CASE POLNESTA WHEN 1 THEN 'New' WHEN 2 THEN 'Released' WHEN 3
THEN
'Change Order' WHEN 4 THEN 'Received' WHEN 5 THEN 'Closed' WHEN 6 THEN
'Canceled' ELSE '(n/a)' END As [Line Status]
--,RTRIM( A.ITEMNMBR) ItemNo
,
Rtrim(A.ITEMDESC) [Item Desc],
Rtrim(A.VNDITNUM) [Vendor ItemNo],
Rtrim(A.VNDITDSC) [Vendor Desc],
Rtrim(V.VENDORID) [Vendor ID],
Rtrim(V.VENDNAME) [Vendor Name],
DOCDATE PODate,
Convert(decimal(19, 2), A.QTYORDER) QtyOrdered,
Convert(decimal(19, 2), P.QtyShipped) QtyShipped,
Convert(decimal(19, 2), P.QtyInvoiced) QtyInvoiced,
Convert(decimal(19, 2), A.QTYORDER - P.QTYSHIpped) QtyRemaining
FROM POP10100 V
JOIN POP10110 A
ON V.PONUMBER = A.PONUMBER
JOIN (SELECT PONUMBER,
POLNENUM,
Sum(QTYSHPPD) QtyShipped,
Sum(QTYINVCD) QtyInvoiced
FROM POP10500
GROUP BY PONUMBER,
POLNENUM) P
ON A.PONUMBER = P.PONUMBER
AND A.oRD = P.POLNENUM
WHERE POLNESTA < 5
AND A.QTYORDER - P.QTYSHIpped > 1
GO
GRANT SELECT ON [adv_PurchaseOrderEntry_Reviews] TO DYNGRP
In conclusion, Elastic Windows can help businesses of all sizes manage their inventory more effectively. By setting up alerts for open purchase orders, businesses can avoid costly mistakes and prevent duplicate purchase orders. So, whether you’re a small business owner or a large corporation, Elastic Windows can be a game-changer for your inventory management system.