--For the initial screen, that shows orders, that were placed during the current week,
--this query should be used:


SELECT TOP 100
    MasterListID, -- if you need this field for debugging, you can ask it.
                -- you don't need it to show to the user,
                -- hence you don't need it to ask in production.
                -- this field supposed to be unique for a particular storeId.
                -- so the combination of (MasterListID, StoreId) is unique.
    DisplayName,
    ItemNumber,
    Description,
    OrderQty
FROM OrderListView olv
WHERE olv.StoreId = ${action.storeId}
    AND MasterListID > 0
ORDER BY MasterListID

--(initially MasterListID > 0, after next scrolling MasterListID > 100 …)








--for the CombinedOrderList screen should be used another query.
--The unfiltered (and limited) one:

SELECT TOP 100
    v.DisplayName , ml.ItemNumber , ml.Description, jo.lastWeekQuantity, jo.thisWeekQuantity, ml.ItemInventoryCount,
    ml.Id AS MLID, jo.OrderId
FROM MasterList ml INNER JOIN Vendor v ON ml.VendorId = v.Id
                        LEFT JOIN JoinedOrders jo ON
                            ml.VendorId = jo.VendorId AND
                            ml.ItemNumber = jo.ItemNumber AND
                            ml.Description = jo.Description AND
                            jo.StoreId = ${action.storeId}
WHERE ml.Id > 0
ORDER BY ml.Id
--(initially ML.Id > 0, after next scrolling ML.Id > 100 …)



-- CombinedOrderList screen with example of applied filters:

SELECT TOP 100
    v.DisplayName , ml.ItemNumber , ml.Description, jo.lastWeekQuantity, jo.thisWeekQuantity, ml.ItemInventoryCount,
    ml.Id AS MLID, jo.OrderId
FROM MasterList ml INNER JOIN Vendor v ON ml.VendorId = v.Id
                        LEFT JOIN JoinedOrders jo ON
                            ml.VendorId = jo.VendorId AND
                            ml.ItemNumber = jo.ItemNumber AND
                            ml.Description = jo.Description AND
                            jo.StoreId = ${action.storeId}
WHERE ml.Id > 0
    AND V.DisplayName LIKE '%PD%'
    AND ML.ItemNumber LIKE '%1024%'
    AND ML.Description LIKE '%Glory%'

ORDER BY ml.Id
--(initially ML.Id > 0, after next scrolling ML.Id > 100 …)






-- batch update            ${action.storeId}     ${action.userId}
INSERT INTO OrderListUpdate
 ( OrderQty, MLID, OrderID,    StoreId,          ModifiedBy) VALUES
 (    20,      3,     NULL,       8,                      8)
,(    10,      4,       44,       8,                      8)
,(     0,      5,       45,       8,                      8)
;
-- to insert the order for MasterListID = 3 for 20
-- to update the order with OrderId = 44 for 10
-- to delete the order with OrderId = 45