Fishbowl Query: ASN 856 EDI Shipments
Use this query to export the custom report of new new shipments from Fishbowl.
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'
/*
Stable per-shipment accounting trigger.
Later changes to postso.datePosted or dateLastModified
will not cause the shipment to be exported again.
*/
INNER JOIN (
SELECT
si.shipId,
MIN(pso.dateCreated) AS PostSOTriggerTime
FROM postso pso
INNER JOIN postsoitem psoi
ON psoi.postSOId = pso.id
INNER JOIN shipitem si
ON si.id = psoi.shipItemId
WHERE psoi.shipItemId IS NOT NULL
GROUP BY
si.shipId
) psoShipAgg
ON psoShipAgg.shipId = sh.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 psoShipAgg.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
si.shipId,
MIN(pso.dateCreated) AS PostSOTriggerTime
FROM postso pso
INNER JOIN postsoitem psoi
ON psoi.postSOId = pso.id
INNER JOIN shipitem si
ON si.id = psoi.shipItemId
WHERE psoi.shipItemId IS NOT NULL
GROUP BY
si.shipId
) psoShipAgg
ON psoShipAgg.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
Updated 16 days ago