Untitled

    SELECT
        sh.awb_nr AS awbNr,
        sh.tote_code,
        it.order_nr,
        it.target_shipped_at,
        src_w.partner_warehouse_code AS source_warehouse,
        dst_w.partner_warehouse_code AS destination_warehouse,
        JSON_EXTRACT_SCALAR(dst_w.misc, '$.darkstore_name') as destination_ds_name,
        force_dst_w.partner_warehouse_code AS force_receive_destination_darkstore,
        JSON_EXTRACT_SCALAR(force_dst_w.misc, '$.darkstore_name') as force_receive_destination_ds_name,
        CASE
            WHEN htl.id_status = 1 THEN 'picked'
            WHEN htl.id_status = 57 THEN 'staged'
            WHEN htl.id_status = 36 AND ht.id_status = 26 THEN 'released'
            WHEN htl.id_status = 36 AND ht.id_status = 13 AND dtl.id_status IS NULL THEN 'dispatched'
            WHEN htl.id_status = 36 AND ht.id_status = 13 AND dtl.id_status = 1 THEN 'picked by DA'
            WHEN htl.id_status = 69 THEN 'received'
        END AS status,
        CASE
            WHEN htl.id_status = 1 AND sa.id_staging_area IS NULL THEN 'Stage in Empty Staging Location'
            WHEN htl.id_status = 1 AND sa.id_staging_area IS NOT NULL THEN CONCAT('Stage in ', sa.code, ' Location')
            WHEN htl.id_status = 57 AND ht.id_hub_transfer IS NULL THEN 'Pending Release From Staging'
            WHEN htl.id_status = 36 AND ht.id_status = 26 THEN 'Pending Dispatching'
            WHEN htl.id_status = 36 AND ht.id_status = 13 THEN 'Pending Receive in Darkstore'
            WHEN htl.id_status = 69 THEN 'Received in Darkstore'
        END AS instruction,
        COUNT(DISTINCT it.id_item) AS items_count,
        COALESCE(fz.zone_code, pj.zone_picklist_group) as picked_zone,
        sa.code AS staging_location,
        ht.canter_code AS palletBarcode,
        sh.created_at AS picked_at,
        al1.created_at AS staged_at,
        u1.username as staged_user,
        al2.created_at AS released_at,
        u2.username as released_user,
        al4.created_at AS dispatched_at,
        u4.username as dispatched_user,
        al3.created_at AS received_at,
        u3.username as received_user
    FROM noondwh.scgoms_goms.item it
    LEFT JOIN noondwh.scgoms_goms.warehouse src_w USING (id_warehouse)
    LEFT JOIN noondwh.scgoms_goms.warehouse dst_w ON JSON_EXTRACT_SCALAR(it.misc, '$.id_dst_warehouse') = CAST(dst_w.id_warehouse AS STRING)
    LEFT JOIN noondwh.scgoms_goms.shipment_line sl ON (sl.id_item, sl.is_active) = (it.id_item, 1)
    LEFT JOIN noondwh.scgoms_goms.shipment sh USING (id_shipment)
    LEFT JOIN noondwh.scgoms_goms.warehouse force_dst_w ON (JSON_EXTRACT_SCALAR(sh.misc, '$.force_received_warehouse_code') = force_dst_w.code)
    LEFT JOIN noondwh.scgoms_goms.hub_transfer_line htl ON (htl.type, htl.line_type, htl.line_id) = ('forward_nim', 'shipment', sh.id_shipment)
    LEFT JOIN noondwh.scgoms_goms.hub_transfer_group htg ON (htg.id_hub_transfer_group = htl.id_hub_transfer_group)
    LEFT JOIN noondwh.scgoms_goms.staging_area sa ON (htg.id_staging_area = sa.id_staging_area)
    LEFT JOIN noondwh.scgoms_goms.hub_transfer ht ON (htl.id_hub_transfer = ht.id_hub_transfer)
    LEFT JOIN noondwh.scgoms_goms.driver_task_line dtl ON (dtl.entity_id, dtl.entity_type) = (ht.id_hub_transfer, 'pallet')
    LEFT JOIN noondwh.scgoms_goms.audit_log al1 ON (al1.id_audit_action, al1.action_key, JSON_EXTRACT_SCALAR(al1.misc, '$.to')) = (12, htl.id_hub_transfer_line, 'staged')
    LEFT JOIN noondwh.scgoms_goms.user u1 ON u1.id_user = al1.id_user
    LEFT JOIN noondwh.scgoms_goms.audit_log al2 ON (al2.id_audit_action, al2.action_key, JSON_EXTRACT_SCALAR(al2.misc, '$.to')) = (12, htl.id_hub_transfer_line, 'picked')
    LEFT JOIN noondwh.scgoms_goms.user u2 ON u2.id_user = al2.id_user
    LEFT JOIN noondwh.scgoms_goms.audit_log al3 ON (al3.id_audit_action, al3.action_key, JSON_EXTRACT_SCALAR(al3.misc, '$.to')) = (12, htl.id_hub_transfer_line, 'transferred')
    LEFT JOIN noondwh.scgoms_goms.user u3 ON u3.id_user = al3.id_user
    LEFT JOIN noondwh.scgoms_goms.audit_log al4 ON (al4.id_audit_action, al4.action_key, JSON_EXTRACT_SCALAR(al4.misc, '$.to')) = (11, ht.id_hub_transfer, 'sealed')
    LEFT JOIN noondwh.scgoms_goms.user u4 ON u4.id_user = al4.id_user
    LEFT JOIN noondwh.scgoms_goms.picking_container pc ON COALESCE(pc.box_barcode, pc.tote_version_code) = sh.awb_nr
    LEFT JOIN noondwh.scgoms_goms.picking_job pj ON pj.id_picking_job = pc.id_picking_job
    LEFT JOIN noondwh.scgoms_goms.fbn_warehouse_zone fz ON fz.id_fbn_warehouse_zone = pj.id_fbn_warehouse_zone
    WHERE it.id_mp = 9
        AND it.id_status = 17
        AND sh.id_shipment IS NOT NULL

            AND it.target_shipped_at > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 DAY)
   
    GROUP BY 1,2,3,4,5,6,7,8,9,10,11,13,14,15,16,17,18,19,20,21,22,23,24
    ORDER BY target_shipped_at DESC, picked_at DESC