WMS/upcloud-base-wh-outbound/script/outboundMoveTaskQuery.presto

<script>

SELECT
  move.MOVE_TASK_ID,
  move.warehouse_id,
  obill.CONSIGNORNAME ,
  bill.CONTACT_NAME,
  bill.CONSIGNOR_ID ,
  bill.SRC_CODE ,
  bill.wave_id,
  item.ITEM_NAME,
  item.CASE_UOM  uom,
  item.PIECE_UOM ,
  move.ITEM_ID,
  move.SRC_WAREHOUSE_LOCATOR_NUM as SRC_LOCATOR_CODE,
  move.SRC_WAREHOUSE_AREA_NUM as SRC_WAREHOUSE_AREA_CODE,
  move.DEST_WAREHOUSE_AREA_NUM as dest_WAREHOUSE_AREA_CODE,
  move.DEST_WAREHOUSE_LOCATOR_NUM AS DEST_LOCATOR_CODE,
  move.MANUFACTURER_LOT_NUM manufacturer_Lot_num,
  SRCBLDG.BLDG_CODE src_bldg_code,
  destbldg.BLDG_CODE dest_bldg_code,
  move.QTY piece_qty,
  move.QTY /move.CONVERSION_RATE  caseQty,
  item.SPECIFICATION,
  move.CONVERSION_RATE,
  move.OUTSTORE_WORKER_NAME,
  destbldg.BLDG_CODE,
  move.status,
  move.move_type,
  move.gmt_create
FROM
  upcloud_base_wh_outbound.upcloud_biz_outbound.wms_move_task move
  LEFT JOIN upcloud_base_wh_outbound.upcloud_biz_outbound.wms_wave_bill bill ON move.bill_id = bill.id
  LEFT JOIN upcloud_base_wh_outbound.upcloud_biz_outbound.OMS_DO_BILL_H  obill ON obill.BILLNO =bill.SRC_CODE
  LEFT JOIN upcloud_base_item.UPCLOUD_BASE_ITEM.BASE_MTL_ITEM_MST item ON MOVE.ITEM_ID = ITEM.ITEM_ID
  LEFT JOIN upcloud_base_wh.UPCLOUD_BASE_WAREHOUSE.BASE_WMS_WH_LOCATOR srcLOCATOR ON srcLOCATOR.WAREHOUSE_LOCATOR_ID  = move.SRC_WAREHOUSE_LOCATOR_ID
  LEFT JOIN upcloud_base_wh.UPCLOUD_BASE_WAREHOUSE.BASE_WMS_WH_BLDG srcbldg ON srcbldg.BLDG_ID  = srcLOCATOR.BLDG_ID
  LEFT JOIN upcloud_base_wh.UPCLOUD_BASE_WAREHOUSE.BASE_WMS_WH_LOCATOR destLocator ON destLocator.WAREHOUSE_LOCATOR_ID  = move.dest_WAREHOUSE_LOCATOR_ID
  LEFT JOIN upcloud_base_wh.UPCLOUD_BASE_WAREHOUSE.BASE_WMS_WH_BLDG destbldg ON destbldg.BLDG_ID  = destLocator.BLDG_ID
where 1 = 1
<if test="params.waveId != null and params.waveId != ''">
  and bill.wave_id = #{waveId}
</if>
<if test="params.warehouseId != null and params.warehouseId != ''">
  and bill.warehouse_id = #{warehouseId}
</if>
<if test="params.itemCode != null and params.itemCode != ''">
  and item.item_code = #{itemCode}
</if>
<if test="params.consignorId != null and params.consignorId != ''">
  and bill.CONSIGNOR_ID = #{consignorId}
</if>
<if test="params.billCode != null and params.billCode != ''">
  and bill.code = #{billCode}
</if>
<if test="params.srcBldgCode != null and params.srcBldgCode != ''">
  and SRCBLDG.BLDG_CODE = #{srcBldgCode}
</if>
<if test="params.destBldgCode != null and params.destBldgCode != ''">
  and destbldg.BLDG_CODE = #{destBldgCode}
</if>
<if test="params.workerNo != null and params.workerNo != ''">
  and move.OUTSTORE_WORKER_NAME  = #{workerNo}
</if>
<if test="params.status != null and params.status != ''">
  and move.status  = #{status}
</if>
order by move.gmt_create desc


</script>