WMS/upcloud-base-wh-outbound/script/report-sql/innercheck_lcl_query.sql

SELECT
  innercheckD.GMT_MODIFIED  AS innecheck_red_data,
  bill.src_code,
  omsbill.CONSIGNORCODE ,
  omsbill.CONSIGNORNAME ,
  wws.WAREHOUSE_AREA_CODE ,
  billd.ITEM_CODE ,
  billd.item_name,
  wws.uom,
  billd.ITEM_SPECS ,
  billd.CASE_CONVERSION_RATE ,
  wws.MANUFACTURER_LOT_NUM ,
  wws.PRODUCTION_DATE ,
  wws.EXPIRED_DATE ,
  billd.MANUFACTURER ,
  innercheckD.RED_QTY,
 innercheckD.RED_REASON
FROM
  UPCLOUD_BIZ_OUTBOUND.WMS_PICK_TASK_D taskd
  left join UPCLOUD_BIZ_OUTBOUND.WMS_PICK_TASK_H taskh on taskh.PICK_TASK_ID = TASKD.PICK_TASK_ID
  left join UPCLOUD_BIZ_OUTBOUND.WMS_WAVE_STORAGE wws on wws.id = taskd.WAVE_STORAGE_ID
  left join UPCLOUD_BIZ_OUTBOUND.WMS_INNER_CHECK_D innerCheckD on innerCheckD.pick_task_d_id = taskd.pick_task_d_id
  LEFT JOIN UPCLOUD_BIZ_OUTBOUND.wms_wave_bill_d billd ON wws.BILL_D_ID = billd.id
  left join UPCLOUD_BIZ_OUTBOUND.WMS_WAVE_BILL bill on bill.id = billd.bill_id
  LEFT JOIN UPCLOUD_BIZ_OUTBOUND.OMS_DO_BILL_H omsbill ON OMSBILL.BILLID  = bill.src_id
where
  innercheckD.RED_QTY != 0 and bill.warehouse_id = #{name:warehouseId,value:""}
order by
  billd.item_code