Untitled

SELECT
        it.item_nr,
        it.order_nr,
        it.catalog_sku AS sku,
        src_w.partner_warehouse_code AS source_warehouse,
        dst_w.partner_warehouse_code AS destination_warehouse,
        JSON_EXTRACT(dst_w.misc, '$.darkstore_name') as destination_ds_name,
        it.created_at AS order_placed_at,
        it.picking_eligibility_at,
        it.target_shipped_at,
        CASE
            WHEN it.id_status = 1 AND it.id_mp_status = 30 AND COALESCE(JSON_EXTRACT(it.misc,'$.is_stock_available'), '1') = '0' THEN 'stock_unavailable'
            WHEN it.id_status = 1 AND it.id_mp_status = 30 AND COALESCE(JSON_EXTRACT(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 btl_putaway.qty > 0 THEN 'ds_putaway_completed'
            WHEN it.id_status = 17 AND it.id_mp_status = 30 AND htl.id_status = 69 THEN 'pending_ds_putaway'
            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
    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(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.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.hub_transfer ht USING (id_hub_transfer)
    LEFT JOIN noondwh.scgoms_goms.staging_area sa ON (sa.id_staging_area = htg.id_staging_area)
    LEFT JOIN noondwh.scfbnplatform_platform.box_transaction_line btl_putaway ON (btl_putaway.pick_location, btl_putaway.barcode) = (sh.awb_nr, sl.wms_barcode)
    WHERE it.id_mp = 9
            AND it.target_shipped_at BETWEEN DATE_SUB(CAST('2024-05-01' AS TIMESTAMP) , INTERVAL 1 DAY) AND DATE_ADD(CAST('2024-05-20' AS TIMESTAMP) , INTERVAL 1 DAY)  



            AND dst_w.partner_warehouse_code = 'DXBDS09'


          AND src_w.id_country = 1
  
        
    GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15

    HAVING status = 'pending_ds_putaway'

    ORDER BY target_shipped_at DESC, order_nr