WITH
-- Unreceived lines are qualified by QtyShipped = 0 and QtyOnHold > 0
-- Make sure there is no "|" in productcode, this means that line has been received
-- Exclude dropship orders with '%DS%'
-- Only orders with a status of "processing" will have unreceived lines
UnreceivedLines AS (
	SELECT
	o.OrderId,
	o.OrderStatus,
    o.OrderDate,
    o.ShipDate,
	o.ShippingMethodID,
	o.ShipFirstName,
	o.ShipLastName,
	o.ShipCompanyName,
	o.ShipAddress1,
	o.ShipAddress2,
	o.ShipCity,
	o.ShipState,
	o.ShipPostalCode,
	o.ShipCountry,
	o.Order_Comments,
	od.OrderDetailID,
	od.Productcode,
	REGEXP_REPLACE(REPLACE(REPLACE(od.ProductNote, ' NO ETA', ''), ' SPC', ''), ' ETA [0-9]{8}', '') AS ProductNote,
	od.QtyOnHold,
	od.QtyShipped,
	od.Quantity,
	p.PIN,
	p.Parent,
        p.Hidden,
	vpw.Location

	FROM custopayted.orderdetails od
	JOIN custopayted.orders o ON o.OrderId = od.OrderId
	LEFT JOIN products.pins p ON p.Productcode = od.Productcode
	LEFT JOIN products._VPW vpw ON vpw.MFG_SKU = p.PIN
	WHERE o.orderstatus = 'Processing'
	AND od.QtyShipped = 0
	AND od.QtyonHold > 0
	AND od.ProductNote NOT LIKE '%|%'
	AND od.ProductNote NOT LIKE '% DS%'
    AND od.ProductNote NOT LIKE '% MDS%'
AND od.ProductNote NOT LIKE 'Processing %'
AND od.ProductNote NOT LIKE 'PreProcessing At%'
AND od.ProductNote NOT LIKE 'NOO %'
AND od.ProductNote != ''
AND od.ProductNote != 'FNLA'
AND od.ProductNote != 'LTBO'
),

-- Get the line count by grouping by orderIDs
LineCount AS (
	SELECT orderid, COUNT(OrderDetailID) AS NumLines
    FROM UnreceivedLines
    GROUP BY orderid
)

SELECT
ul.OrderId,
ul.OrderDetailId,
CONCAT(ul.ShipFirstName, ' ', ul.ShipLastName) AS ShipName,
ul.Productcode,
p.Name,
ul.QtyOnHold,
IFNULL(ul.ProductNote, '') AS ProductNote,
CASE WHEN ul.ShippingMethodID IN ('142', '950', '951', '1058') THEN 'F' WHEN ul.ShippingMethodID >= 400 AND ul.ShippingMethodID <= 418 THEN 'I' ELSE '' END AS ShippingIdentifier, -- Get the FedEx Onerate qualifier
ul.OrderDate,
ul.PIN,
REPLACE(ul.Parent, 'FNLA', '') AS SubbedTo,
IFNULL(GROUP_CONCAT(vm.VendorCode), '') AS Vendors,
IFNULL(GROUP_CONCAT(vm.MFG_SKU), '') AS VendorSKUs,
CASE WHEN p.HasPhoto > 0 THEN 'Y' ELSE '' END AS Photo,
CASE WHEN lc.NumLines > 1 THEN 'Y' ELSE '' END AS Multi,
IFNULL(vpw.Location, '') AS Location,
IFNULL(vpw.Stock, 0) AS VPWStock,
p.Popularity,
p.PricingType,
p.Discontinued,
ul.Hidden


FROM UnreceivedLines ul
LEFT JOIN products.pins p ON p.ProductCode = ul.ProductCode
LEFT JOIN LineCount lc ON lc.orderid = ul.orderid
LEFT JOIN products._VPW vpw ON vpw.MFG_SKU = ul.PIN
LEFT JOIN products.vendor_map vm ON vm.ProductCode = ul.ProductCode AND vm.VendorCode != 'VPW'

GROUP BY
ul.OrderId,
ul.OrderDetailId,
ShippingIdentifier, -- Get the FedEx Onerate qualifier
ul.OrderDate,
ShipName,
ul.PIN,
ul.Productcode,
SubbedTo,
p.Name,
Photo,
ul.QtyOnHold,
ul.ProductNote,
Multi,
vpw.Location,
VPWStock,
p.Popularity,
p.PricingType,
p.Discontinued,
Hidden

ORDER BY ul.ProductNote ASC;