<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.prolog.cs.biz.inbound.dao.rcv.RcvInstockLinesMapper">
<resultMap id="mainMap" type="com.prolog.cs.biz.inbound.dto.rcv.RcvInstockLinesDTO">
	<result column="in_bound_line_id" property="inBoundLineId"/>
	<result column="rcv_header_id" property="rcvHeaderId"/>
	<result column="rcv_line_id" property="rcvLineId"/>
	<result column="line_num" property="lineNum"/>
	<result column="wh_locator_id" property="whLocatorId"/>
	<result column="item_id" property="itemId"/>
	<result column="piece_qty" property="pieceQty"/>
	<result column="uom" property="uom"/>
	<result column="uom_type" property="uomType"/>
	<result column="conversion_rate" property="conversionRate"/>
	<result column="odd_case_qty" property="oddCaseQty"/>
	<result column="odd_piece_qty" property="oddPieceQty"/>
	<result column="batch_id" property="batchId"/>
	<result column="batch_num" property="batchNum"/>
	<result column="batch_production_date" property="batchProductionDate"/>
	<result column="batch_expired_date" property="batchExpiredDate"/>
	<result column="vendor_batch_num" property="vendorBatchNum"/>
	<result column="manufacturer_lot_num" property="manufacturerLotNum"/>
	<result column="container_code" property="containerCode"/>
	<result column="goods_case_code" property="goodsCaseCode"/>
	<result column="comments" property="comments"/>
	<result column="algorithm_type" property="algorithmType"/>
	<result column="executor_user" property="executorUser"/>
	<result column="stock_status" property="stockStatus"/>
	<result column="task_id" property="taskId"/>
	<result column="mtl_owner_id" property="mtlOwnerId"/>
	<result column="warehouse_id" property="warehouseId"/>
	<result column="src_bill_type" property="srcBillType"/>
	<result column="src_header_id" property="srcHeaderId"/>
	<result column="src_line_id" property="srcLineId"/>
	<result column="asn_header_id" property="asnHeaderId"/>
	<result column="asn_line_id" property="asnLineId"/>
	<result column="CATEGORY_ID" property="categoryId"/>
	<result column="wh_locator_code" property="whLocatorCode"/>
	<result column="wh_locator_name" property="whLocatorName"/>
	<result column="wh_area_category_code" property="whAreaCategoryCode"/>
	<result column="wh_area_category_name" property="whAreaCategoryName"/>
	<result column="wh_area_code" property="whAreaCode"/>
	<result column="bldg_code" property="bldgCode"/>
	
	<result column="wh_area_category_id" property="whAreaCategoryId"/>
	<result column="inventory_status" property="inventoryStatus"/>
	<result column="instock_label" property="instockLabel"/>
	<result column="bldg_id" property="bldgId"/>
	<result column="real_wh_locator_code" property="realWhLocatorCode"/>
	<result column="real_wh_locator_id" property="realWhLocatorId"/>
	<result column="real_wh_locator_name" property="realWhLocatorName"/>
	
	<result column="ITEM_VOLUME" property="itemVolume" />
	<result column="ITEM_WEIGHT" property="itemWeight" />
	<result column="ITEM_HEALTH_TWOVOTEFLAG"
		property="itemHealthTwovoflag" />
	<result column="WAREHOUSE_NAME" property="warehouseName" />
	<result column="WAREHOUSE_AREA_NAME"
		property="warehouseAreaName" />
	<result column="ITEM_NUM" property="itemNum" />
	<result column="ITEM_NAME" property="itemName" />
	<result column="CATEGORY_NAME" property="categoryName" />
	<result column="ITEM_BARCODE" property="itemBarcode" />
	<!-- <result column="enterprise_id" property="enterpriseId"/>
	<result column="gmt_create" property="gmtCreate"/>
	<result column="creator" property="creator"/>
	<result column="creator_name" property="creatorName"/>
	<result column="gmt_modified" property="gmtModified"/>
	<result column="modifier" property="modifier"/>
	<result column="modifier_name" property="modifierName"/> -->
</resultMap>
<sql id="mainClomuns">
		t1.in_bound_line_id,
		t1.rcv_header_id,
		t1.rcv_line_id,
		t1.line_num,
		t1.wh_locator_id,
		
		t1.wh_locator_code,
		t1.wh_locator_name,
		t1.wh_area_category_code,
		t1.wh_area_category_name,
		t1.wh_area_code,
		t1.bldg_code,
		
		t1.wh_area_category_id,
		t1.inventory_status,
		t1.instock_label,
		t1.bldg_id,
		t1.real_wh_locator_code,
		t1.real_wh_locator_id,
		t1.real_wh_locator_name,
		
		t1.item_id,
		t1.piece_qty,
		t1.uom,
		t1.uom_type,
		t1.conversion_rate,
		t1.odd_case_qty,
		t1.odd_piece_qty,
		t1.batch_id,
		t1.batch_num,
		t1.batch_production_date,
		t1.batch_expired_date,
		t1.vendor_batch_num,
		t1.manufacturer_lot_num,
		t1.container_code,
		t1.goods_case_code,
		t1.comments,
		t1.algorithm_type,
		t1.executor_user,
		t1.stock_status,
		t1.task_id,
		t1.mtl_owner_id,
		t1.warehouse_id,
		t1.src_bill_type,
		t1.src_header_id,
		t1.src_line_id,
		t1.asn_header_id,
		t1.asn_line_id,
		t1.ENTERPRISE_ID as enterpriseId,
		t1.GMT_CREATE as gmtCreate,
		t1.CREATOR,
		t1.CREATOR_NAME as creatorName,
		t1.GMT_MODIFIED as gmtModified,
		t1.MODIFIER,
		t1.MODIFIER_NAME as modifierName,
	</sql>
	<select id="findList" resultMap="mainMap">
		select
		distinct
		<include refid="mainClomuns"></include>
		t2.WAREHOUSE_AREA_CODES as warehouseAreaCodes,
		t2.ITEM_IS_PIECE_OR_CASE as itemIsPieceOrCase,
		t2.WAREHOUSE_AREA_CATEGORYS as warehouseAreaCategorys,
		t2.ITEM_VOLUME,
		t2.ITEM_WEIGHT,
		t2.ITEM_HEALTH_TWOVOTEFLAG,
		t2.WAREHOUSE_NAME,
		t2.WAREHOUSE_AREA_NAME,
		t3.ITEM_NUM,
		t3.ITEM_NAME,
		t3.CATEGORY_ID,
		t3.CATEGORY_NAME,
		t3.ITEM_BARCODE,
		t3.specification as specification,
		t3.ITEM_HEALTH_LISTED_LICENSOR as itemHealthListedLicensor,
		t3.ITEM_HEALTH_PRODUCE_LICENSE as itemHealthProduceLicense,
		t3.ITEM_HEALTH_STORE_CATEGORY as itemHealthStoreCategory,
		t3.ITEM_IS_RECHECK as itemIsRecheck,
		t3.IS_INSTRUMENT as isInstrument,
		t3.ITEM_IS_IMPORTED as itemIsImported
		FROM
		wms_in_rcv_instock_lines t1
		LEFT JOIN
		WMS_IN_RCV_LINES t2
		on t1.rcv_line_id = t2.RCV_LINE_ID
		LEFT JOIN
		WMS_IN_RCV_LINES_PROFILES t3
		on t1.rcv_line_id = t3.RCV_LINE_ID
		where
		1=1
		<if test="enterpriseId != null  and enterpriseId !=''">
			and t1.enterprise_id = #{enterpriseId}
		</if>
		<if test="warehouseId != null  and warehouseId !=''">
			and t1.warehouse_id = #{warehouseId}
		</if>
		<if test="mtlOwnerId != null  and mtlOwnerId !=''">
			and t1.mtl_owner_id = #{mtlOwnerId}
		</if>
		<if test="rcvHeaderId != null  and rcvHeaderId !=''">
			and t1.rcv_header_id = #{rcvHeaderId}
		</if>
		<if test="rcvLineId != null  and rcvLineId !=''">
			and t1.rcv_line_id = #{rcvLineId}
		</if>
		
		<if test="itemId != null  and itemId !=''">
			and t1.item_id = #{itemId}
		</if>
		<if test="stockStatus != null  and stockStatus !=''">
			and t1.stock_status = #{stockStatus}
		</if>
		<if test="includeIds != null and includeIds.size() >0 ">
            and t1.in_bound_line_id in
            <foreach collection="includeIds" item="item" open="("
                     separator="," close=")">
                #{item}
            </foreach>
        </if>
		<if test="excludeIds != null and excludeIds.size() >0 ">
            and t1.in_bound_line_id not in
            <foreach collection="excludeIds" item="item" open="("
                     separator="," close=")">
                #{item}
            </foreach>
        </if>
		order by t1.gmt_modified
		desc
	</select>
	
	<select id="findByPkId" resultMap="mainMap" parameterType="String">
		select
		<include refid="mainClomuns"></include>
		t2.WAREHOUSE_AREA_CODES as warehouseAreaCodes,
		t2.ITEM_IS_PIECE_OR_CASE as itemIsPieceOrCase,
		t2.WAREHOUSE_AREA_CATEGORYS as warehouseAreaCategorys,
		t2.ITEM_VOLUME,
		t2.ITEM_WEIGHT,
		t2.ITEM_HEALTH_TWOVOTEFLAG,
		t2.WAREHOUSE_NAME,
		t2.WAREHOUSE_AREA_NAME,
		t3.ITEM_NUM,
		t3.CATEGORY_ID,
		t3.ITEM_NAME,
		t3.CATEGORY_NAME,
		t3.ITEM_BARCODE,
		t3.specification as specification,
		t3.ITEM_HEALTH_LISTED_LICENSOR as itemHealthListedLicensor,
		t3.ITEM_HEALTH_PRODUCE_LICENSE as itemHealthProduceLicense,
		t3.ITEM_HEALTH_STORE_CATEGORY as itemHealthStoreCategory,
		t3.ITEM_IS_RECHECK as itemIsRecheck,
		t3.IS_INSTRUMENT as isInstrument,
		t3.ITEM_IS_IMPORTED as itemIsImported
		FROM
		wms_in_rcv_instock_lines t1
		LEFT JOIN
		WMS_IN_RCV_LINES t2
		on t1.rcv_line_id = t2.RCV_LINE_ID
		LEFT JOIN
		WMS_IN_RCV_LINES_PROFILES t3
		on t1.rcv_line_id = t3.RCV_LINE_ID
		where
		t1.in_bound_line_id = #{pkId}
	</select>
	<select id="getReport" resultMap="mainMap">
		select
		distinct
		<include refid="mainClomuns"></include>
		t2.WAREHOUSE_AREA_CODES as warehouseAreaCodes,
		t2.ITEM_IS_PIECE_OR_CASE as itemIsPieceOrCase,
		t2.WAREHOUSE_AREA_CATEGORYS as warehouseAreaCategorys,
		t2.ITEM_VOLUME,
		t2.ITEM_WEIGHT,
		t2.ITEM_HEALTH_TWOVOTEFLAG,
		t2.WAREHOUSE_NAME,
		t2.WAREHOUSE_AREA_NAME,
		t3.ITEM_NUM,
		t3.ITEM_NAME,
		t3.CATEGORY_ID,
		t3.CATEGORY_NAME,
		t3.ITEM_BARCODE,
		t3.specification as specification,
		t3.ITEM_HEALTH_LISTED_LICENSOR as itemHealthListedLicensor,
		t3.ITEM_HEALTH_PRODUCE_LICENSE as itemHealthProduceLicense,
		t3.ITEM_HEALTH_STORE_CATEGORY as itemHealthStoreCategory,
		t3.ITEM_IS_RECHECK as itemIsRecheck,
		t3.IS_INSTRUMENT as isInstrument,
		t3.ITEM_IS_IMPORTED as itemIsImported,
		
		t2.show_production_date as showProductionDate,	
		t2.show_expired_date as showExpiredDate,
		t2.item_zjm as itemZjm,
		t2.approval_number as approvalNumber,
		t2.production_place as productionPlace,
		t3.ITEM_HEALTH_REGISTER_NBR as itemHealthRegisterNbr,
		t3.manufacturer_name as manufacturerName
		
		FROM
		wms_in_rcv_instock_lines t1
		LEFT JOIN
		WMS_IN_RCV_LINES t2
		on t1.rcv_line_id = t2.RCV_LINE_ID
		LEFT JOIN
		WMS_IN_RCV_LINES_PROFILES t3
		on t1.rcv_line_id = t3.RCV_LINE_ID
		where
		1=1
		<if test="rcvHeaderId != null  and rcvHeaderId !=''">
			and t1.rcv_header_id = #{rcvHeaderId}
		</if>
		<if test="itemId != null  and itemId !=''">
			and t1.item_id = #{itemId}
		</if>
		<if test="itemCode != null  and itemCode !=''">
			and UPPER(t3.item_num) like concat(CONCAT('%',UPPER(#{itemCode})),'%')
		</if>
		<if test="itemName != null  and itemName !=''">
			and UPPER(t3.item_name) like concat(concat('%',UPPER(#{itemName})),'%')
		</if>
		<if test="itemZjm != null  and itemZjm !=''">
		    and UPPER(t2.ITEM_ZJM) like CONCAT(CONCAT('%',UPPER(#{itemZjm})),'%') 
		</if>
		<if test="manufacturerLotNum != null  and manufacturerLotNum !=''">
			and UPPER(t1.manufacturer_lot_num) like CONCAT(concat('%',UPPER(#{manufacturerLotNum})),,'%')
		</if>
		order by t1.gmt_modified
		desc
	</select>
	<select id="getMaxLineNum" parameterType="String" resultType="java.lang.Long">
		SELECT NVL(MAX(NVL(LINE_NUM, 1)), 1) FROM WMS_IN_RCV_INSTOCK_LINES WHERE RCV_HEADER_ID = #{rcvHeaderId}
	</select>
</mapper>
