Untitled
unknown
plain_text
a year ago
2.8 kB
13
Indexable
SELECT it.item_nr, it.order_nr, it.catalog_sku AS sku, it.product_fulltype_code, src_w.partner_warehouse_code AS source_warehouse, dst_w.partner_warehouse_code AS destination_warehouse, dst_w.misc ->> '$.darkstore_name' as destination_ds_name, DATE_FORMAT(it.created_at, '%Y-%m-%d') AS order_placed_at, DATE_FORMAT(it.picking_eligibility_at, '%Y-%m-%d'), DATE_FORMAT(it.target_shipped_at, '%Y-%m-%d'), CASE WHEN it.id_status = 1 AND it.id_mp_status = 30 AND COALESCE(it.misc->>'$.is_stock_available', 1) = 0 THEN 'stock_unavailable' WHEN it.id_status = 1 AND it.id_mp_status = 30 AND COALESCE(it.misc->>'$.is_stock_available', 1) = 1 THEN 'pending_job_gen' WHEN it.id_status = 44 AND it.id_mp_status = 30 THEN 'pending_picking' WHEN it.id_status IN (36, 17) AND it.id_mp_status = 30 AND (htl.id_status = 1 OR htl.id_hub_transfer_line IS NULL) THEN 'pending_staging' WHEN it.id_status = 17 AND it.id_mp_status = 30 AND htl.id_status = 57 THEN 'pending_release_staging' WHEN it.id_status = 17 AND it.id_mp_status = 30 AND htl.id_status = 36 AND ht.id_status = 26 THEN 'pending_dispatching' WHEN it.id_status = 17 AND it.id_mp_status = 30 AND htl.id_status = 36 AND ht.id_status = 13 THEN 'pending_receiving' WHEN it.id_status = 17 AND it.id_mp_status = 30 AND htl.id_status = 69 THEN 'received_at_darkstore' WHEN it.id_mp_status = 31 THEN 'cancelled' ELSE 'unexpected_status' END AS status, sa.code AS staging_location, sh.awb_nr AS shipmentBarcode, sl.pbarcode_canonical as gtin, sl.wms_barcode, ht.canter_code AS pallet_barcode, IF(COALESCE(sh.misc ->> "$.is_shipment_urgent", 0) = 0 AND sh.id_status != 58, TRUE, FALSE) AS enable_mark_shipment_urgent FROM item it LEFT JOIN warehouse src_w USING (id_warehouse) LEFT JOIN warehouse dst_w ON (it.misc ->> '$.id_dst_warehouse' = dst_w.id_warehouse) LEFT JOIN shipment_line sl ON (sl.id_item, sl.is_active) = (it.id_item, 1) LEFT JOIN shipment sh USING (id_shipment) LEFT JOIN hub_transfer_line htl ON (htl.type, htl.line_type, htl.line_id) = ('forward_nim', 'shipment', sh.id_shipment) LEFT JOIN hub_transfer_group htg USING (id_hub_transfer_group) LEFT JOIN hub_transfer ht USING (id_hub_transfer) LEFT JOIN staging_area sa ON (sa.id_staging_area = htg.id_staging_area) WHERE it.id_mp = 9 AND it.target_shipped_at > DATE_SUB(NOW(), INTERVAL 1 DAY) AND src_w.id_country = 1 GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17 HAVING status = 'received_at_darkstore' ORDER BY target_shipped_at DESC, order_nr
Editor is loading...
Leave a Comment