SELECT
Pos01, Pos02, Pos03, Pos04, Pos05, Pos06, Pos07, Pos08, Pos09, Pos10,
Pos11, Pos12, Pos13, Pos14, Pos15, Pos16, Pos17, Pos18, Pos19, Pos20,
Pos21, Pos22, Pos23, Pos24, Pos25, Pos26, Pos27, Pos28, Pos29, Pos30,
Pos31, Pos32, Pos33, Pos34, Pos35, Pos36, Pos37, Pos38, Pos39, Pos40,
Pos41, Pos42
FROM (
/* =========================
S ROW - Shipment
========================= */
SELECT
sh.id AS SortShipment,
1 AS SortRow,
0 AS SortCarton,
0 AS SortLine,
'S' AS Pos01,
'856SOPI' AS Pos02,
COALESCE(NULLIF(TRIM(cust.accountingId), ''), '') AS Pos03,
COALESCE(
NULLIF(TRIM(sh.shipmentIdentificationNumber), ''),
NULLIF(TRIM(sh.num), ''),
CAST(sh.id AS CHAR),
''
) AS Pos04,
COALESCE(NULLIF(TRIM(car.scac), ''), '') AS Pos05,
'' AS Pos06,
COALESCE(NULLIF(TRIM(sh.billOfLading), ''), '') AS Pos07,
COALESCE(DATE_FORMAT(orderAgg.ScheduledDeliveryDate, '%Y%m%d'), '') AS Pos08,
COALESCE(DATE_FORMAT(sh.dateShipped, '%Y%m%d'), '') AS Pos09,
COALESCE(NULLIF(TRIM(sh.shipToName), ''), '') AS Pos10,
COALESCE(
NULLIF(TRIM(SUBSTRING_INDEX(REPLACE(sh.shipToAddress, '\r', ''), '\n', 1)), ''),
''
) AS Pos11,
COALESCE(
NULLIF(
TRIM(
CASE
WHEN LOCATE('\n', REPLACE(sh.shipToAddress, '\r', '')) > 0 THEN
SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(sh.shipToAddress, '\r', ''), '\n', 2), '\n', -1)
ELSE ''
END
),
''
),
''
) AS Pos12,
COALESCE(NULLIF(TRIM(sh.shipToCity), ''), '') AS Pos13,
COALESCE(NULLIF(TRIM(shipState.code), ''), '') AS Pos14,
COALESCE(NULLIF(TRIM(sh.shipToZip), ''), '') AS Pos15,
COALESCE(
NULLIF(CAST(sh.shipToId AS CHAR), ''),
NULLIF(TRIM(cust.number), ''),
''
) AS Pos16,
'' AS Pos17,
'' AS Pos18,
'' AS Pos19,
CAST(COALESCE(cartonAgg.GrossWeight, 0) AS CHAR) AS Pos20,
CASE
WHEN LOWER(COALESCE(cartonAgg.WeightUOM, '')) IN ('lb', 'lbs', 'pound', 'pounds') THEN 'Pound'
WHEN LOWER(COALESCE(cartonAgg.WeightUOM, '')) IN ('kg', 'kgs', 'kilogram', 'kilograms') THEN 'Kilogram'
ELSE COALESCE(NULLIF(TRIM(cartonAgg.WeightUOM), ''), '')
END AS Pos21,
CAST(COALESCE(NULLIF(sh.cartonCount, 0), cartonAgg.CartonCount, 0) AS CHAR) AS Pos22,
'' AS Pos23,
'' AS Pos24,
COALESCE(
NULLIF(TRIM(shipFrom.addressName), ''),
NULLIF(TRIM(shipFrom.name), ''),
NULLIF(TRIM(lg.name), ''),
NULLIF(TRIM(comp.name), ''),
''
) AS Pos25,
COALESCE(
NULLIF(TRIM(SUBSTRING_INDEX(REPLACE(shipFrom.address, '\r', ''), '\n', 1)), ''),
''
) AS Pos26,
COALESCE(
NULLIF(
TRIM(
CASE
WHEN LOCATE('\n', REPLACE(shipFrom.address, '\r', '')) > 0 THEN
SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(shipFrom.address, '\r', ''), '\n', 2), '\n', -1)
ELSE ''
END
),
''
),
''
) AS Pos27,
COALESCE(NULLIF(TRIM(shipFrom.city), ''), '') AS Pos28,
COALESCE(NULLIF(TRIM(shipFromState.code), ''), '') AS Pos29,
COALESCE(NULLIF(TRIM(shipFrom.zip), ''), '') AS Pos30,
'' AS Pos31,
'' AS Pos32,
'' AS Pos33,
'' AS Pos34,
'' AS Pos35,
'' AS Pos36,
'' AS Pos37,
'' AS Pos38,
CASE
WHEN COALESCE(shipThroughAgg.TotalShippedThroughShipment, 0) >= COALESCE(orderAgg.TotalOrderedOnOrder, 0)
THEN 'CC'
ELSE 'PR'
END AS Pos39,
'' AS Pos40,
COALESCE(DATE_FORMAT(sh.dateShipped, '%H:%i'), '') AS Pos41,
COALESCE(NULLIF(TRIM(car.name), ''), '') AS Pos42
FROM ship sh
INNER JOIN so ON so.id = sh.soId
INNER JOIN qbclass soClass ON soClass.id = so.qbClassId
AND TRIM(soClass.name) = 'Zoey'
INNER JOIN (
SELECT
pso.soId,
MAX(
GREATEST(
COALESCE(pso.datePosted, '1900-01-01 00:00:00'),
COALESCE(pso.dateCreated, '1900-01-01 00:00:00'),
COALESCE(pso.dateLastModified, '1900-01-01 00:00:00')
)
) AS PostSOTriggerTime
FROM postso pso
GROUP BY pso.soId
) psoAgg ON psoAgg.soId = so.id
LEFT JOIN customer cust ON cust.id = so.customerId
LEFT JOIN carrier car ON car.id = COALESCE(sh.carrierId, so.carrierId)
LEFT JOIN stateconst shipState ON shipState.id = sh.shipToStateId
LEFT JOIN locationgroup lg ON lg.id = sh.locationGroupId
LEFT JOIN company comp ON comp.id = 1
CROSS JOIN (
SELECT
COALESCE(
MAX(h.lastRunTime),
'1900-01-01 00:00:00'
) AS LastSuccessfulRun
FROM fbschedulehistory h
INNER JOIN fbschedule s ON s.id = h.scheduleId
WHERE TRIM(s.name) = 'Shipments to Zoey'
AND (
h.error IS NULL
OR TRIM(h.error) = ''
OR LOWER(TRIM(h.error)) LIKE 'success%'
)
AND LOWER(COALESCE(h.error, '')) NOT LIKE '%fail%'
AND LOWER(COALESCE(h.error, '')) NOT LIKE '%error%'
) lastRun
LEFT JOIN address shipFrom ON shipFrom.id = (
SELECT a.id
FROM address a
WHERE a.locationGroupId = sh.locationGroupId
ORDER BY a.defaultFlag DESC, a.id
LIMIT 1
)
LEFT JOIN stateconst shipFromState ON shipFromState.id = shipFrom.stateId
LEFT JOIN (
SELECT
sc.shipId,
COUNT(sc.id) AS CartonCount,
SUM(COALESCE(sc.freightWeight, 0)) AS GrossWeight,
MAX(NULLIF(TRIM(sc.weightUOM), '')) AS WeightUOM
FROM shipcarton sc
GROUP BY sc.shipId
) cartonAgg ON cartonAgg.shipId = sh.id
LEFT JOIN (
SELECT
soi.soId,
SUM(COALESCE(soi.qtyOrdered, 0)) AS TotalOrderedOnOrder,
MIN(soi.dateScheduledFulfillment) AS ScheduledDeliveryDate
FROM soitem soi
WHERE soi.productId IS NOT NULL
GROUP BY soi.soId
) orderAgg ON orderAgg.soId = so.id
LEFT JOIN (
SELECT
shBase.id AS ShipId,
SUM(COALESCE(si2.qtyShipped, 0)) AS TotalShippedThroughShipment
FROM ship shBase
INNER JOIN ship sh2 ON sh2.soId = shBase.soId
AND sh2.dateShipped IS NOT NULL
AND (
sh2.dateShipped < shBase.dateShipped
OR (
sh2.dateShipped = shBase.dateShipped
AND sh2.id <= shBase.id
)
)
INNER JOIN shipitem si2 ON si2.shipId = sh2.id
GROUP BY shBase.id
) shipThroughAgg ON shipThroughAgg.ShipId = sh.id
WHERE sh.soId IS NOT NULL
AND sh.dateShipped IS NOT NULL
AND psoAgg.PostSOTriggerTime > lastRun.LastSuccessfulRun
UNION ALL
/* =========================
O ROW - Order
========================= */
SELECT
sh.id AS SortShipment,
2 AS SortRow,
0 AS SortCarton,
0 AS SortLine,
'O' AS Pos01,
COALESCE(NULLIF(TRIM(so.customerPO), ''), '') AS Pos02,
COALESCE(DATE_FORMAT(COALESCE(so.dateIssued, so.dateCreated), '%Y%m%d'), '') AS Pos03,
'' AS Pos04,
CAST(COALESCE(cartonAgg.GrossWeight, 0) AS CHAR) AS Pos05,
'' AS Pos06,
'' AS Pos07,
CAST(COALESCE(NULLIF(sh.cartonCount, 0), cartonAgg.CartonCount, 0) AS CHAR) AS Pos08,
'' AS Pos09,
'' AS Pos10,
'' AS Pos11,
'' AS Pos12,
'' AS Pos13,
'' AS Pos14,
'' AS Pos15,
'' AS Pos16,
'' AS Pos17,
'' AS Pos18,
'' AS Pos19,
'' AS Pos20,
'' AS Pos21,
'' AS Pos22,
'' AS Pos23,
'' AS Pos24,
'' AS Pos25,
'' AS Pos26,
'' AS Pos27,
'' AS Pos28,
'' AS Pos29,
'' AS Pos30,
'' AS Pos31,
'' AS Pos32,
'' AS Pos33,
'' AS Pos34,
'' AS Pos35,
'' AS Pos36,
'' AS Pos37,
'' AS Pos38,
'' AS Pos39,
'' AS Pos40,
'' AS Pos41,
'' AS Pos42
FROM ship sh
INNER JOIN so ON so.id = sh.soId
INNER JOIN qbclass soClass ON soClass.id = so.qbClassId
AND TRIM(soClass.name) = 'Zoey'
INNER JOIN (
SELECT
pso.soId,
MAX(
GREATEST(
COALESCE(pso.datePosted, '1900-01-01 00:00:00'),
COALESCE(pso.dateCreated, '1900-01-01 00:00:00'),
COALESCE(pso.dateLastModified, '1900-01-01 00:00:00')
)
) AS PostSOTriggerTime
FROM postso pso
GROUP BY pso.soId
) psoAgg ON psoAgg.soId = so.id
CROSS JOIN (
SELECT
COALESCE(
MAX(h.lastRunTime),
'1900-01-01 00:00:00'
) AS LastSuccessfulRun
FROM fbschedulehistory h
INNER JOIN fbschedule s ON s.id = h.scheduleId
WHERE TRIM(s.name) = 'Shipments to Zoey'
AND (
h.error IS NULL
OR TRIM(h.error) = ''
OR LOWER(TRIM(h.error)) LIKE 'success%'
)
AND LOWER(COALESCE(h.error, '')) NOT LIKE '%fail%'
AND LOWER(COALESCE(h.error, '')) NOT LIKE '%error%'
) lastRun
LEFT JOIN (
SELECT
sc.shipId,
COUNT(sc.id) AS CartonCount,
SUM(COALESCE(sc.freightWeight, 0)) AS GrossWeight
FROM shipcarton sc
GROUP BY sc.shipId
) cartonAgg ON cartonAgg.shipId = sh.id
WHERE sh.soId IS NOT NULL
AND sh.dateShipped IS NOT NULL
AND psoAgg.PostSOTriggerTime > lastRun.LastSuccessfulRun
UNION ALL
/* =========================
P ROW - Pack / Carton
========================= */
SELECT
sh.id AS SortShipment,
3 AS SortRow,
COALESCE(sc.cartonNum, sc.id) AS SortCarton,
0 AS SortLine,
'P' AS Pos01,
COALESCE(NULLIF(TRIM(sc.sscc), ''), '') AS Pos02,
CAST(COALESCE(packAgg.PackSize, 0) AS CHAR) AS Pos03,
'' AS Pos04,
'' AS Pos05,
'' AS Pos06,
'' AS Pos07,
'' AS Pos08,
'' AS Pos09,
'' AS Pos10,
'' AS Pos11,
'' AS Pos12,
COALESCE(NULLIF(TRIM(sc.trackingNum), ''), '') AS Pos13,
'' AS Pos14,
'' AS Pos15,
'' AS Pos16,
'' AS Pos17,
'' AS Pos18,
'' AS Pos19,
'' AS Pos20,
'' AS Pos21,
'' AS Pos22,
'' AS Pos23,
'' AS Pos24,
'' AS Pos25,
'' AS Pos26,
'' AS Pos27,
'' AS Pos28,
'' AS Pos29,
'' AS Pos30,
'' AS Pos31,
'' AS Pos32,
'' AS Pos33,
'' AS Pos34,
'' AS Pos35,
'' AS Pos36,
'' AS Pos37,
'' AS Pos38,
'' AS Pos39,
'' AS Pos40,
'' AS Pos41,
'' AS Pos42
FROM ship sh
INNER JOIN so ON so.id = sh.soId
INNER JOIN qbclass soClass ON soClass.id = so.qbClassId
AND TRIM(soClass.name) = 'Zoey'
INNER JOIN (
SELECT
pso.soId,
MAX(
GREATEST(
COALESCE(pso.datePosted, '1900-01-01 00:00:00'),
COALESCE(pso.dateCreated, '1900-01-01 00:00:00'),
COALESCE(pso.dateLastModified, '1900-01-01 00:00:00')
)
) AS PostSOTriggerTime
FROM postso pso
GROUP BY pso.soId
) psoAgg ON psoAgg.soId = so.id
INNER JOIN shipcarton sc ON sc.shipId = sh.id
CROSS JOIN (
SELECT
COALESCE(
MAX(h.lastRunTime),
'1900-01-01 00:00:00'
) AS LastSuccessfulRun
FROM fbschedulehistory h
INNER JOIN fbschedule s ON s.id = h.scheduleId
WHERE TRIM(s.name) = 'Shipments to Zoey'
AND (
h.error IS NULL
OR TRIM(h.error) = ''
OR LOWER(TRIM(h.error)) LIKE 'success%'
)
AND LOWER(COALESCE(h.error, '')) NOT LIKE '%fail%'
AND LOWER(COALESCE(h.error, '')) NOT LIKE '%error%'
) lastRun
LEFT JOIN (
SELECT
si.shipCartonId,
SUM(COALESCE(si.qtyShipped, 0)) AS PackSize
FROM shipitem si
GROUP BY si.shipCartonId
) packAgg ON packAgg.shipCartonId = sc.id
WHERE sh.soId IS NOT NULL
AND sh.dateShipped IS NOT NULL
AND COALESCE(packAgg.PackSize, 0) <> 0
AND psoAgg.PostSOTriggerTime > lastRun.LastSuccessfulRun
UNION ALL
/* =========================
I ROW - Item
========================= */
SELECT
sh.id AS SortShipment,
4 AS SortRow,
COALESCE(sc.cartonNum, sc.id, 0) AS SortCarton,
COALESCE(soi.soLineItem, si.id) AS SortLine,
'I' AS Pos01,
COALESCE(
NULLIF(TRIM(soi.customerPartNum), ''),
NULLIF(TRIM(product.num), ''),
NULLIF(TRIM(soi.productNum), ''),
''
) AS Pos02,
'' AS Pos03,
COALESCE(NULLIF(TRIM(product.upc), ''), '') AS Pos04,
COALESCE(
NULLIF(TRIM(soi.description), ''),
NULLIF(TRIM(product.description), ''),
''
) AS Pos05,
CAST(COALESCE(soi.qtyOrdered, 0) AS CHAR) AS Pos06,
COALESCE(
NULLIF(TRIM(shipUom.code), ''),
NULLIF(TRIM(soUom.code), ''),
''
) AS Pos07,
CAST(COALESCE(si.qtyShipped, 0) AS CHAR) AS Pos08,
'' AS Pos09,
'' AS Pos10,
'' AS Pos11,
COALESCE(NULLIF(CAST(soi.soLineItem AS CHAR), ''), '') AS Pos12,
'' AS Pos13,
'' AS Pos14,
'' AS Pos15,
'' AS Pos16,
'' AS Pos17,
'' AS Pos18,
'' AS Pos19,
'' AS Pos20,
'' AS Pos21,
'' AS Pos22,
'' AS Pos23,
'' AS Pos24,
'' AS Pos25,
'' AS Pos26,
'' AS Pos27,
'' AS Pos28,
'' AS Pos29,
'' AS Pos30,
'' AS Pos31,
'' AS Pos32,
'' AS Pos33,
'' AS Pos34,
'' AS Pos35,
'' AS Pos36,
'' AS Pos37,
'' AS Pos38,
'' AS Pos39,
'' AS Pos40,
'' AS Pos41,
'' AS Pos42
FROM ship sh
INNER JOIN so ON so.id = sh.soId
INNER JOIN qbclass soClass ON soClass.id = so.qbClassId
AND TRIM(soClass.name) = 'Zoey'
INNER JOIN (
SELECT
pso.soId,
MAX(
GREATEST(
COALESCE(pso.datePosted, '1900-01-01 00:00:00'),
COALESCE(pso.dateCreated, '1900-01-01 00:00:00'),
COALESCE(pso.dateLastModified, '1900-01-01 00:00:00')
)
) AS PostSOTriggerTime
FROM postso pso
GROUP BY pso.soId
) psoAgg ON psoAgg.soId = so.id
INNER JOIN shipitem si ON si.shipId = sh.id
LEFT JOIN shipcarton sc ON sc.id = si.shipCartonId
LEFT JOIN soitem soi ON soi.id = si.soItemId
LEFT JOIN product ON product.id = soi.productId
LEFT JOIN uom shipUom ON shipUom.id = si.uomId
LEFT JOIN uom soUom ON soUom.id = soi.uomId
CROSS JOIN (
SELECT
COALESCE(
MAX(h.lastRunTime),
'1900-01-01 00:00:00'
) AS LastSuccessfulRun
FROM fbschedulehistory h
INNER JOIN fbschedule s ON s.id = h.scheduleId
WHERE TRIM(s.name) = 'Shipments to Zoey'
AND (
h.error IS NULL
OR TRIM(h.error) = ''
OR LOWER(TRIM(h.error)) LIKE 'success%'
)
AND LOWER(COALESCE(h.error, '')) NOT LIKE '%fail%'
AND LOWER(COALESCE(h.error, '')) NOT LIKE '%error%'
) lastRun
WHERE sh.soId IS NOT NULL
AND sh.dateShipped IS NOT NULL
AND COALESCE(si.qtyShipped, 0) <> 0
AND psoAgg.PostSOTriggerTime > lastRun.LastSuccessfulRun
) SOPI
ORDER BY
SortShipment,
CASE
WHEN Pos01 = 'S' THEN 1
WHEN Pos01 = 'O' THEN 2
WHEN Pos01 IN ('P', 'I') THEN 3
ELSE 9
END,
SortCarton,
CASE
WHEN Pos01 = 'P' THEN 1
WHEN Pos01 = 'I' THEN 2
ELSE 0
END,
SortLine;
