Filtering by production spec causes Cadence badges to not be seen

[bradleyreynolds-idi]

Summary

The "pending production digital lines" query looks at an order, takes the designs, takes the line items, and returns a row for every combination of the two, disregarding what's actually selected on the production specification.

After a support ticket with Shopworks, they gave us access to query the production specification, and I merged #67 to accurately reflect that. I then promptly received a ticket from production saying that they couldn't see any badges. I've reverted #67 in #71 for now.

Why?

The Impress badges have a single line item, with production applied, so the query was able to see the instructions on the single production line.

The Cadence badges use an "assembly BoM", which has the instructions on the parent line, but the design attached to a component line. Because the instructions are on a different line, the line that the query actually looks at gets discarded as a "non-production line".

Possible solutions

Unclear at this time. A new query will have to be designed to find the ApplyLocation and OrderInstructions on their respective lines.

Keep in mind

Both query formats need to be supported until badges can be entered the same way.

[bradleyreynolds-idi]

SELECT OrderDes.id_Design          AS design_id,
       LinesOE.cn_LineQuantity_Req AS quantity,
       LinesOE.OrderInstructions   AS instructions
FROM Orders
         JOIN OrdDesLoc ON Orders.ID_Order = OrdDesLoc.ID_Order
         JOIN OrderDes ON OrderDes.ID_OrderDesign = OrdDesLoc.id_OrderDesign
         JOIN LinesCompletion ON OrdDesLoc.ID_OrderDesLoc = LinesCompletion.id_OrderDesLoc
         JOIN LinesOE ON LinesCompletion.id_LineOE = LinesOE.ID_LineOE
WHERE Orders.sts_Produced IN (0, 0.5)
  AND Orders.id_OrderType = 63
  AND LinesOE.OrderInstructions IS NOT NULL
UNION
SELECT OrderDes.id_Design          AS design_id,
       LinesOE.cn_LineQuantity_Req AS quantity,
       LinesOE.OrderInstructions   AS instructions
FROM LinesOE
         JOIN LinesCompletion ON LinesOE.ID_LineOE = LinesCompletion.id_LineOE -- never matches, because the design is not on the parent
         JOIN OrderDes ON LinesCompletion.id_OrderDesign = OrderDes.ID_OrderDesign
WHERE LinesOE.ID_LineOE IN (SELECT DISTINCT LinesOE.id_LineOE_AssbParent -- parent grabbed here
                            FROM LinesOE
                            WHERE LinesOE.ID_LineOE IN (SELECT LinesCompletion.id_LineOE
                                                        FROM LinesCompletion
                                                        WHERE LinesCompletion.id_order IN (SELECT Orders.ID_Order
                                                                                           FROM Orders
                                                                                           WHERE Orders.sts_Produced IN (0, 0.5)
                                                                                             AND Orders.id_OrderType = 63)
                                                          AND LinesCompletion.sts_ApplyLocation = 1)
                              AND LinesOE.id_LineOE_AssbParent <> 0);