Untitled

SELECT DISTINCT
  it.item_nr,
  it.catalog_sku,
  src_w.partner_warehouse_code AS source_warehouse,
  dst_w.partner_warehouse_code AS destination_darkstore,
  JSON_EXTRACT(dst_w.misc, '$.darkstore_name') AS destination_ds_name,
  DATE(it.target_shipped_at) AS target_ship_date,
  COALESCE(pj.zone_picklist_group, fwz.zone_code) AS zone_code,
  pjl.location_code,
  pjl.wms_barcode,
  pjl.pbarcode_canonical AS gtin,
  it.product_fulltype_code,
  JSON_EXTRACT(it.misc, '$.unfulfillable_reason') AS cancelation_reason,
  it.created_at
FROM noondwh.scgoms_goms.item it
LEFT JOIN noondwh.scgoms_goms.picking_job_line pjl USING (id_item)
LEFT JOIN noondwh.scgoms_goms.picking_job pj USING (id_picking_job)
LEFT JOIN noondwh.scgoms_goms.fbn_warehouse_zone fwz ON (pj.id_fbn_warehouse_zone = fwz.id_fbn_warehouse_zone)
LEFT JOIN noondwh.scgoms_goms.warehouse src_w ON (it.id_warehouse = src_w.id_warehouse)
LEFT JOIN noondwh.scgoms_goms.warehouse dst_w ON (CAST(JSON_EXTRACT_SCALAR(it.misc, '$.id_dst_warehouse') AS int64) = dst_w.id_warehouse)
WHERE it.id_mp = 9
    AND it.id_mp_status = 31
    AND it.target_shipped_at BETWEEN CAST('2024-05-01' AS TIMESTAMP) AND CAST(DATE_ADD('2024-05-31', INTERVAL 1 DAY) AS TIMESTAMP)
    AND src_w.id_country = 1