As I live and breathe

Whoever wrote this should have their programmer license revoked. Oh, wait, licenses aren't required to be programmers. Well, maybe we should start requiring licenses. The first question on that test should be "when is it OK to include a 'SELECT *' in production code, and if the answer is anything other than 'NEVER', the programmer should be shot.

Actual production SQL code follows:

SELECT P.WMS_SHIP_DT, P.DC_LOCATION_ID, P.TO_LOCATION_ID, P.PRODUCT_ID, P.PO_NBR, Z.DEMAND_DT
, NVL(Z.SOURCE_VENDOR_ID, 1802) AS SOURCE_VENDOR_ID, Z.VENDOR_ID
, CASE WHEN NVL(Z.DPR_PROJ_ORDER_QTY,0) > NVL(P.WMS_ORIG_ORD_QTY,0) THEN NVL(Z.DPR_PROJ_ORDER_QTY,0)
ELSE NVL(P.WMS_ORIG_ORD_QTY,0) END AS DEMAND_QTY
, NVL(Z.DPR_PROJ_ORDER_QTY,0) AS DPR_PROJ_ORDER_QTY, NVL(P.PO_ORIG_ORDER_QTY,0) AS PO_ORIG_ORDER_QTY
, NVL(P.PO_ORDER_QTY,0) AS PO_ORDER_QTY, NVL(P.WMS_UNIT_PRICE,0) AS WMS_UNIT_PRICE
, NVL(P.WMS_ORIG_ORD_QTY,0) AS WMS_ORIG_ORD_QTY, NVL(P.WMS_SHIPPED_QTY,0) AS WMS_SHIPPED_QTY
, NVL(P.WMS_CANCEL_QTY,0) AS WMS_CANCEL_QTY, NVL(P.OUT_OF_STOCK_QTY,0) AS OUT_OF_STOCK_QTY
FROM (SELECT O.WMS_SHIP_DT, O.DC_LOCATION_ID, O.TO_LOCATION_ID, O.PO_NBR, O.PRODUCT_ID,
MAX(T.PO_DOC_DT) AS PO_DOC_DT, MAX(T.PO_ORIG_ORDER_QTY) AS PO_ORIG_ORDER_QTY,
MAX(T.PO_ORDER_QTY) AS PO_ORDER_QTY, MAX(NVL(O.WMS_UNIT_PRICE,0)) AS WMS_UNIT_PRICE,
SUM(O.WMS_ORIG_ORD_QTY) AS WMS_ORIG_ORD_QTY, SUM(O.WMS_SHIPPED_QTY) AS WMS_SHIPPED_QTY,
SUM(O.WMS_CANCEL_QTY) AS WMS_CANCEL_QTY,
CASE WHEN SUM(O.WMS_ORIG_ORD_QTY - O.WMS_SHIPPED_QTY - O.WMS_CANCEL_QTY) >= 0
THEN SUM(O.WMS_ORIG_ORD_QTY - O.WMS_SHIPPED_QTY - O.WMS_CANCEL_QTY)
ELSE 0 END AS OUT_OF_STOCK_QTY
FROM EDW_STANDBY..WMS_OUTBOUND O
LEFT OUTER JOIN (SELECT PH.PO_DOC_DT, PH.PO_NBR, PL.PRODUCT_ID, PL.LOCATION_ID
,PL.PO_ORIG_ORDER_QTY, PL.PO_ORDER_QTY
FROM EDW_STANDBY..PO_HDR_FACT PH, EDW_STANDBY..PO_LN_FACT PL
WHERE PH.PO_MSTR_DUE_DT BETWEEN CURRENT_DATE - 75 AND CURRENT_DATE + 30
AND PH.PO_TYPE_ID = 'UB'
AND PH.PO_GID = PL.PO_GID
AND PH.PO_MSTR_DUE_DT = PL.PO_MSTR_DUE_DT) T
ON O.TO_LOCATION_ID = T.LOCATION_ID AND O.PO_NBR = T.PO_NBR AND O.PRODUCT_ID = T.PRODUCT_ID
WHERE O.WMS_SHIP_DT > CURRENT_DATE - 35
AND O.PO_NBR IS NOT NULL
GROUP BY O.WMS_SHIP_DT, O.DC_LOCATION_ID, O.TO_LOCATION_ID, O.PO_NBR, O.PRODUCT_ID) P
LEFT OUTER JOIN (SELECT *
FROM WMS_DEMAND_STORE_DAY
WHERE DEMAND_DT > CURRENT_DATE - 60
AND SHIP_SEQ_NBR = 1) Z
ON NVL(P.PO_DOC_DT, TO_DATE('12/31/9999', 'MM/DD/YYYY')) = Z.DEMAND_DT
AND P.TO_LOCATION_ID = Z.TO_LOCATION_ID
AND P.DC_LOCATION_ID = Z.DC_LOCATION_ID
AND P.PRODUCT_ID = Z.PRODUCT_ID