<?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.task.TaskInstockDetailMapper">
	<resultMap id="mainMap"
		type="com.prolog.cs.biz.inbound.dto.task.TaskInstockDetailDTO">
		<result column="task_inbound_id" property="taskInboundId" />
		<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="rcv_header_id" property="rcvHeaderId" />
		<result column="rcv_line_id" property="rcvLineId" />
		<result column="item_id" property="itemId" />
		<!-- <result column="qty" property="qty"/> -->
		<result column="uom" property="uom" />
		<!-- <result column="conversion_rate" property="conversionRate"/> -->
		<!-- <result column="piece_qty" property="pieceQty"/> -->
		<result column="batch_id" property="batchId" />
		<result column="batch_num" property="batchNum" />
		<result column="vendor_batch_num" property="vendorBatchNum" />
		<result column="active_flag" property="activeFlag" />
		<result column="remain_qty" property="remainQty" />
		<result column="comments" property="comments" />
		<result column="result" property="result" />
		<result column="reason" property="reason" />
		<result column="task_type" property="taskType" />
		<result column="manufacturer_lot_num"
			property="manufacturerLotNum" />
		<result column="data_src_type" property="dataSrcType" />
		<result column="executor" property="executor" />
		<result column="container_code" property="containerCode" />
		<result column="goods_case_code" property="goodsCaseCode" />
		<result column="status" property="status" />
		<result column="spilter_name" property="spilterName" />
		<result column="is_spilt" property="isSpilt" />
		<result column="wh_locator_id" property="whLocatorId" />
		<result column="data_version" property="dataVersion" />
		<result column="is_lock" property="isLock" />
		<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="rqc_line_id" property="rqcLineId" />
		<result column="bldg_id" property="bldgId" />
		<result column="inventory_status" property="inventoryStatus" />
		<result column="instock_label" property="instockLabel" />

		<!-- -->
		<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_NUM" property="warehouseCode" />
		<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="CATEGORY_ID" property="categoryId" />
		<result column="ITEM_BARCODE" property="itemBarcode" />
		<!-- <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"/> 
			<result column="enterprise_id" property="enterpriseId"/> -->
	</resultMap>

	<resultMap id="rcvMap"
		type="com.prolog.cs.biz.inbound.dto.rcv.RcvHDTO">
		<result column="rcv_header_id" property="rcvHeaderId" />
		<result column="auto_bill_num" property="autoBillNum" />
		<result column="custom_bill_num" property="customBillNum" />
		<result column="executor_user" property="executorUser" />

		<result column="rcv_date" property="rcvDate" />
		<result column="rcv_type" property="rcvType" />
		<result column="status" property="status" />
		<result column="case_qty" property="caseQty" />
		<result column="piece_qty" property="pieceQty" />
		<result column="access_level" property="accessLevel" />
		<result column="comments" property="comments" />
		<result column="mtl_owner_id" property="mtlOwnerId" />
		<result column="warehouse_id" property="warehouseId" />
		<result column="asn_header_id" property="asnHeaderId" />

		<result column="print_count" property="printCount" />
		<result column="print_date" property="printDate" />
		<result column="audit_date" property="auditDate" />
		<result column="confirm_date" property="confirmDate" />
		<result column="audited_name" property="auditedName" />
		<result column="printed_name" property="printedName" />
		<result column="confirmed_name" property="confirmedName" />
		<result column="vendor_id" property="vendorId" />
		<result column="src_bill_type" property="srcBillType" />
		<result column="src_header_id" property="srcHeaderId" />
		<!-- profile字段 -->
		<result column="ship_type" property="shipType" />
		<result column="arrival_mode" property="arrivalMode" />
		<result column="vendor_name" property="vendorName" />
		<result column="warehouse_name" property="warehouseName" />
		<result column="buyer_mane" property="buyerMane" />
		<result column="customer_name" property="customerName" />
		<result column="mtl_owner_name" property="mtlOwnerName" />

		<!-- <result column="asn_auto_bill_num" property="asnAutoBillNum"/> <result 
			column="buyer_id" property="buyerId"/> <result column="src_orgn_id" property="srcOrgnId"/> 
			<result column="src_warehouse_id" property="srcWarehouseId"/> <result column="src_mtl_owner_id" 
			property="srcMtlOwnerId"/> <result column="enterprise_id" property="enterpriseId"/> 
			<result column="src_auto_bill_num" property="srcAutoBillNum"/> <result column="audited_by" 
			property="auditedBy"/> <result column="confirmed_by" property="confirmedBy"/> 
			<result column="printed_by" property="printedBy"/> <result column="status_as" 
			property="statusAs"/> <result column="orgn_id" property="orgnId"/> <result 
			column="customer_id" property="customerId"/> <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.task_inbound_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.rcv_header_id,
		t1.rcv_line_id,
		t1.item_id,
		<!-- t1.qty, -->
		trunc(nvl(t1.piece_qty,0)/nvl(t1.conversion_rate,1),4) as qty,
		<!-- floor(nvl(t1.qty,0)) as caseQty, -->
		trunc(nvl(t1.piece_qty,0)/nvl(t1.conversion_rate,1),0) as caseQty,
		mod(nvl(t1.piece_qty,0),nvl(t1.conversion_rate,1)) as oddPieceQty,
		t1.uom,
		nvl(t1.conversion_rate,1) as conversionRate,
		nvl(t1.piece_qty,0) as pieceQty,
		t1.batch_id,
		t1.batch_num,
		t1.vendor_batch_num,
		t1.active_flag,
		t1.remain_qty,
		t1.comments,
		t1.result,
		t1.reason,
		t1.task_type,
		t1.manufacturer_lot_num,
		t1.data_src_type,
		t1.executor,
		t1.container_code,
		t1.goods_case_code,
		t1.status,
		t1.spilter_name,
		t1.is_spilt,
		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.rqc_line_id,
		t1.bldg_id,
		t1.inventory_status,
		t1.instock_label,

		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,
		t1.data_version,
		t1.is_lock,
		t1.CREATOR_NAME as rqcUser,
	</sql>

	<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,
		t2.batch_production_date as
		batchProductionDate,
		t2.batch_expired_date as batchExpiredDate,
		t2.ERP_BATCH_NUM as erpBatchNum,
		t2.PRODUCTION_PLACE as
		productionPlace,
		<!-- 批准文号 -->
		t3.ITEM_NUM,
		t3.ITEM_NAME,
		t3.CATEGORY_ID,
		t3.CATEGORY_NAME,
		t3.ITEM_BARCODE,
		t3.PIECE_PRICE_TI as piecePriceTi,
		t3.PIECE_PRICE_TE as
		piecePriceTe,
		TRUNC(
		decode(
		nvl( t3.PIECE_PRICE_TI, 1 ),
		0,
		0,
		nvl(
		t3.PIECE_PRICE_TE, 0 ) / nvl( t3.PIECE_PRICE_TI, 1 )
		),
		4
		) * 100 AS
		taxRate,
		<!-- TRUNC(nvl(t3.PIECE_PRICE_TE,0)/nvl(t3.PIECE_PRICE_TI,1), 4) * 100 
			as taxRate, -->
		t4.VENDOR_ID as vendorId,
		t4.ORGN_ID as orgId,
		t4.RCV_DATE as rcvDate,
		t5.WAREHOUSE_NUM,
		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,

		t3.MANUFACTURER_ID as
		manufacturerId,
		t3.MANUFACTURER_CODE as manufacturerCode,
		t3.MANUFACTURER_NAME as manufacturerName,
		t3.ITEM_HEALTH_REGISTER_NBR
		as itemHealthRegisterNbr,
		t3.ITEM_HEALTH_COMMON_NAME as
		itemHealthCommonName,
		t3.RETURN_INFO as returnInfo,
		t3.RETURN_COLD_QUALITY as returnColdQuality,
		t3.RETURN_EXPLAIN as
		returnExplain,
		t3.IS_LOCK as isLock,
		t3.IS_HOSTED as isHosted,
		t3.ITEM_IS_SPECIAL as itemIsSpecial,
		t3.IS_BACTERIA as isBacteria,
		t3.SHELF_LIFE_DAYS as shelfLifeDays,
		t3.SHELF_LIFE_UNIT as
		shelfLifeUnit,

		t2.bldg_name as bldgName,
		t2.bldg_code as bldgCode,

		t2.DELEGATION_PARTY_ID as delegationPartyId,
		t2.DELEGATION_PARTY_NAME
		as delegationPartyName,
		t2.is_delegate as isDelegate,
		t2.SHOW_PRODUCTION_DATE as
		showProductionDate,
		t2.SHOW_EXPIRED_DATE as
		showExpiredDate,
		t2.WAREHOUSE_ROOM_CODES as warehouseRoomCodes,
		t2.PIECE_CONVERSION_RATE as pieceConversionRate,
		t2.ASN_LINE_REMARK as
		asnLineRemark,
		t2.ITEM_ZJM as itemZjm,
		t5.MTL_OWNER_NUM as mtlOwnerNum,
		t5.MTL_OWNER_NAME as mtlOwnerName,
		t4.ASN_AUTO_BILL_NUM as asnBillNum,
		t4.AUTO_BILL_NUM as autoBillNum,
		t2.approval_number as approvalNumber,
		t2.RQC_REPORT_NO as rqcReportNo,
		t3.allow_decimal as allowDecimal,
		t3.STORE_TEMPERATURE as storeTemperature,

		t4.SALE_BILL_TYPE as
		saleBillType,
		t5.BUYER_MANE as bizUser,
		t5.ORGN_NAME as orgName,
		t2.WH_LOCATOR_ID as rcvWhLocatorId,
		t2.WH_LOCATOR_CODE as
		rcvWhLocatorCode,
		t2.WH_AREA_CODE as rcvWhAreaCode,
		t2.WH_AREA_CATEGORY_CODE as rcvWhAreaCategoryCode,

		t2.INVENTORY_OP_TYPE as inventoryOpType,
		t2.HOST_LOCATOR_ID as
		hostLocatorId,
		coalesce(t5.VENDOR_NAME,t5.CUSTOMER_NAME) as shipperName


		FROM
		wms_in_task_instock_detail 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
		LEFT JOIN
		wms_in_rcv_headers t4
		on
		t4.rcv_header_id = t1.rcv_header_id
		LEFT JOIN
		WMS_IN_RCV_HEADERS_PROFILES t5
		on t5.rcv_header_id = t1.rcv_header_id
		where
		t1.task_inbound_id = #{pkId}
	</select>
	<select id="findList" resultMap="mainMap">
		select
		distinct
		t1.task_inbound_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.rcv_header_id,
		t1.rcv_line_id,
		t1.item_id,
		<!-- t1.qty, -->
		trunc(nvl(t1.piece_qty,0)/nvl(t1.conversion_rate,1),4) as qty,
		<!-- floor(nvl(t1.qty,0)) as caseQty, -->
		trunc(nvl(t1.piece_qty,0)/nvl(t1.conversion_rate,1),0) as caseQty,
		mod(nvl(t1.piece_qty,0),nvl(t1.conversion_rate,1)) as oddPieceQty,
		t1.uom,
		nvl(t1.conversion_rate,1) as conversionRate,
		nvl(t1.piece_qty,0) as pieceQty,
		t1.batch_id,
		t1.batch_num,
		t1.vendor_batch_num,
		t1.active_flag,
		t1.remain_qty,
		t1.comments,
		t1.result,
		t1.reason,
		t1.task_type,
		t1.manufacturer_lot_num,
		t1.data_src_type,
		t1.executor,
		t1.container_code,
		t1.goods_case_code,
		t1.status,
		t1.spilter_name,
		t1.is_spilt,

		<!-- t1.wh_locator_id, t1.wh_locator_code, t1.wh_area_code, t1.wh_area_category_code, -->

		t1.wh_locator_name,
		t1.wh_area_category_name,

		<!-- t1.bldg_code, -->

		t1.wh_area_category_id,
		t1.rqc_line_id,
		t1.bldg_id,
		t1.inventory_status,
		t1.instock_label,

		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,
		t1.data_version,
		t1.is_lock,
		t1.CREATOR_NAME as rqcUser,

		coalesce(t1.wh_locator_id, t2.WH_LOCATOR_ID) as wh_locator_id,
		coalesce(t1.wh_locator_code,
		t2.WH_LOCATOR_CODE) as wh_locator_code,
		coalesce(t1.wh_area_code,
		t2.WH_AREA_CODE) as wh_area_code,
		coalesce(t1.wh_area_category_code,
		t2.WH_AREA_CATEGORY_CODE) as
		wh_area_category_code,

		t2.WAREHOUSE_AREA_CODES as warehouseAreaCodes,
		nvl(t2.ITEM_IS_PIECE_OR_CASE,0) as itemIsPieceOrCase,
		t2.WAREHOUSE_AREA_CATEGORYS as warehouseAreaCategorys,
		t2.ITEM_VOLUME,
		t2.ITEM_WEIGHT,
		t2.WAREHOUSE_NAME,
		t2.WAREHOUSE_AREA_NAME,
		t2.batch_production_date as batchProductionDate,
		t2.batch_expired_date
		as batchExpiredDate,
		t3.ITEM_NUM,
		t3.ITEM_NAME,
		t3.CATEGORY_NAME,
		t3.CATEGORY_ID,
		t3.ITEM_BARCODE,
		t3.PIECE_PRICE_TI as piecePriceTi,
		t3.PIECE_PRICE_TE as piecePriceTe,
		TRUNC(
		decode(
		nvl( t3.PIECE_PRICE_TI,
		1 ),
		0,
		0,
		nvl( t3.PIECE_PRICE_TE, 0 ) / nvl( t3.PIECE_PRICE_TI, 1 )
		),
		4
		)
		* 100 AS taxRate,
		<!-- TRUNC(nvl(t3.PIECE_PRICE_TE,0)/nvl(t3.PIECE_PRICE_TI,1), 4) * 100 
			as taxRate, -->
		t4.VENDOR_ID as vendorId,
		t4.ORGN_ID as orgId,
		t4.RCV_DATE as rcvDate,
		t5.WAREHOUSE_NUM,
		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,

		t3.MANUFACTURER_ID as
		manufacturerId,
		t3.MANUFACTURER_CODE as manufacturerCode,
		t3.MANUFACTURER_NAME as manufacturerName,
		t3.ITEM_HEALTH_REGISTER_NBR
		as itemHealthRegisterNbr,
		t3.ITEM_HEALTH_COMMON_NAME as
		itemHealthCommonName,
		t3.RETURN_INFO as returnInfo,
		t3.RETURN_COLD_QUALITY as returnColdQuality,
		t3.RETURN_EXPLAIN as
		returnExplain,
		t3.IS_LOCK as isLock,
		t3.IS_HOSTED as isHosted,
		t3.ITEM_IS_SPECIAL as itemIsSpecial,
		t3.IS_BACTERIA as isBacteria,
		t3.SHELF_LIFE_DAYS as shelfLifeDays,
		t3.SHELF_LIFE_UNIT as
		shelfLifeUnit,

		t2.bldg_name as bldgName,
		t2.bldg_code as bldgCode,

		t1.locator_floor_num as locatorFloorNum,
		t1.rqc_header_id as
		rqcHeaderId,

		t4.executor_user as rcvUser,

		t2.DELEGATION_PARTY_ID as
		delegationPartyId,
		t2.DELEGATION_PARTY_NAME as delegationPartyName,
		t2.is_delegate as isDelegate,
		t2.SHOW_PRODUCTION_DATE as
		showProductionDate,
		t2.SHOW_EXPIRED_DATE as showExpiredDate,
		t2.WAREHOUSE_ROOM_CODES as warehouseRoomCodes,
		t2.PIECE_CONVERSION_RATE as pieceConversionRate,
		t2.ASN_LINE_REMARK as
		asnLineRemark,
		t2.ITEM_ZJM as itemZjm,
		t4.rcv_type as rcvType,
		t5.MTL_OWNER_NUM as mtlOwnerNum,
		t5.MTL_OWNER_NAME as mtlOwnerName,
		t4.ASN_AUTO_BILL_NUM as asnBillNum,
		t4.AUTO_BILL_NUM as autoBillNum,
		t2.production_place as productionPlace,
		t2.approval_number as
		approvalNumber,
		t2.RQC_REPORT_NO as rqcReportNo,
		t3.allow_decimal as
		allowDecimal,
		t3.STORE_TEMPERATURE as storeTemperature,

		t4.SALE_BILL_TYPE as saleBillType,
		t5.BUYER_MANE as bizUser,
		t5.ORGN_NAME as orgName,

		t2.WH_LOCATOR_ID as rcvWhLocatorId,
		t2.WH_LOCATOR_CODE as rcvWhLocatorCode,
		t2.WH_AREA_CODE as
		rcvWhAreaCode,
		t2.WH_AREA_CATEGORY_CODE as
		rcvWhAreaCategoryCode,



		t2.INVENTORY_OP_TYPE as inventoryOpType,
		t2.HOST_LOCATOR_ID as
		hostLocatorId,
		coalesce(t5.VENDOR_NAME,t5.CUSTOMER_NAME) as shipperName

		FROM
		wms_in_task_instock_detail 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
		LEFT JOIN
		wms_in_rcv_headers t4
		on
		t4.rcv_header_id = t1.rcv_header_id
		LEFT JOIN
		WMS_IN_RCV_HEADERS_PROFILES t5
		on t5.rcv_header_id = t1.rcv_header_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="bldgName != null  and bldgName !=''">
			and t2.bldg_name = #{bldgName}
		</if>
		<if test="whAreaCode != null  and whAreaCode !=''">
			and t1.wh_area_code = #{whAreaCode}
		</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>
		<choose>
			<when test="incloudRefIds != null and incloudRefIds.size() >0">
				and t1.rqc_line_id in
				<foreach collection="incloudRefIds" item="item" open="("
					separator="," close=")">
					#{item}
				</foreach>
			</when>
			<otherwise>
				<if test="rqcHeaderId != null  and rqcHeaderId !=''">
					and (t1.rqc_header_id = #{rqcHeaderId} or t1.RQC_LINE_ID
					in(SELECT RQC_LINE_ID FROM WMS_IN_TASK_INSTOCK_DETAIL WHERE
					RQC_LINE_ID in (SELECT RQC_LINE_ID FROM WMS_IN_RQC_LINES WHERE
					RQC_HEADER_ID = #{rqcHeaderId})) )
				</if>
			</otherwise>
		</choose>
		<!-- <if test="rqcHeaderId != null and rqcHeaderId !=''"> and (t1.rqc_header_id 
			= #{rqcHeaderId} or exists(SELECT RQC_LINE_ID FROM WMS_IN_TASK_INSTOCK_DETAIL 
			WHERE RQC_LINE_ID in (SELECT RQC_LINE_ID FROM WMS_IN_RQC_LINES WHERE RQC_HEADER_ID 
			= #{rqcHeaderId})) ) </if> -->
		<!-- <if test="rqcHeaderId != null and rqcHeaderId !=''"> and t1.rqc_header_id 
			= #{rqcHeaderId} </if> <if test="incloudRefIds != null and incloudRefIds.size() 
			>0 "> and t1.rqc_line_id in <foreach collection="incloudRefIds" item="item" 
			open="(" separator="," close=")"> #{item} </foreach> </if> -->
		<if test="excloudRefIds != null and excloudRefIds.size() >0 ">
			and t1.rqc_line_id not in
			<foreach collection="includeIds" item="item" open="("
				separator="," close=")">
				#{item}
			</foreach>
		</if>

		<if test="incloudIds != null  and incloudIds.size() >0 ">
			and t1.task_inbound_id in
			<foreach collection="incloudIds" item="item" open="("
				separator="," close=")">
				#{item}
			</foreach>
		</if>

		<if test="itemId != null  and itemId !=''">
			and t1.item_id = #{itemId}
		</if>
		<if test="rcvLineId != null  and rcvLineId !=''">
			and t1.rcv_line_id = #{rcvLineId}
		</if>
		<if test="status != null  and status !=''">
			and t1.status = #{status}
		</if>
		<if test="itemCondition != null  and itemCondition !=''">
			and (UPPER(t2.ITEM_ZJM) like
			CONCAT(CONCAT('%',UPPER(#{itemCondition})),'%')
			or UPPER(t3.ITEM_NUM)
			like
			CONCAT(CONCAT('%',UPPER(#{itemCondition})),'%')
			or
			UPPER(t3.ITEM_NAME) like
			CONCAT(CONCAT('%',UPPER(#{itemCondition})),'%')
			or
			UPPER(t3.ITEM_BARCODE) like
			CONCAT(CONCAT('%',UPPER(#{itemCondition})),'%')
			)
		</if>
		<if test="containerCode != null  and containerCode !=''">
			AND UPPER(t1.CONTAINER_CODE) like
			CONCAT(CONCAT('%',UPPER(#{containerCode})),'%')
		</if>
		<if test="instockLabel != null  and instockLabel !=''">
			AND UPPER(t1.instock_label) like
			CONCAT(CONCAT('%',UPPER(#{instockLabel})),'%')
		</if>
		<if test="segment2 != null  and segment2 !=''">
			AND t1.segment2 = #{segment2}
		</if>

		and t1.piece_qty &lt;&gt; 0
		order by t1.gmt_modified
		desc
	</select>
	<select id="extractRcvHWithNativeSql" resultMap="rcvMap">
		SELECT
		t1.rcv_header_id,
		t1.auto_bill_num,
		t1.custom_bill_num,
		t1.executor_user,
		t1.rcv_date,
		t1.rcv_type,
		t1.status,
		t1.case_qty,
		t1.piece_qty,
		t1.access_level,
		t1.comments,
		t1.mtl_owner_id,
		t1.warehouse_id,
		t1.asn_header_id,
		t1.vendor_id,
		t1.src_header_id,
		t1.src_bill_type,
		t1.print_count,
		t1.print_date,
		t1.audit_date,
		t1.confirm_date,
		t1.audited_name,
		t1.printed_name,
		t1.confirmed_name,
		t2.mtl_owner_num as mtlOwnerNum,
		t2.ship_type,
		t2.arrival_mode,
		t2.vendor_name,
		t2.warehouse_name,
		t2.buyer_mane,
		t2.customer_name,
		t2.mtl_owner_name,
		nvl(t1.need_user,t2.BUYER_MANE) as needUser,
		nvl(t1.need_dept,t2.ORGN_NAME) as needDept,
		t2.ORGN_NAME as orgnName,
		t1.AGENT as agent,
		t1.ASN_REMARK as asnRemark,
		nvl(t2.vendor_num,
		t1.vendor_id)as vendorNum,
		t2.orgn_num as orgnNum,
		t1.shipper_zjm as
		shipperZjm,
		t1.ASN_AUTO_BILL_NUM as asnBillNum,
		t1.ASN_AUTO_BILL_NUM as
		asnAutoBillNum,
		t2.transportation_party as transportationParty,
		t2.carriername as carriername,
		t2.ship_from_date as shipFromDate,
		t2.ship_from_add as shipFromAdd,
		t2.on_passage_days as onPassageDays,
		t2.ship_arrive_tem_perature as shipArriveTemPerature,
		t2.ship_arrive_time as shipArriveTime,
		t2.ship_tem_perature as
		shipTemPerature,
		t2.ship_date as shipDate,
		t2.device_no as deviceNo,
		t2.tempcontrol_mode as tempcontrolMode,
		t2.tempcontrol_status as
		tempcontrolStatus,
		t1.EXECUTOR_USER as rcvUser,
		t1.auto_bill_num as
		rcvNo,
		t1.COMMENTS as rcvRemark,

		coalesce( t2.VENDOR_NUM,
		t2.CUSTOMER_NUM,t3.SHIPPERCODE) AS shipperCode,
		coalesce(
		t2.VENDOR_NAME, t2.CUSTOMER_NAME, t3.SHIPPERNAME) AS
		shipperName,
		(SELECT QC_OPCODE1 FROM(
		SELECT
		l1.rcv_header_id,l1.QC_OPCODE1,l1.QC_OPCODE2,l1.GMT_MODIFIED
		FROM
		WMS_IN_TASK_INSTOCK_DETAIL l2
		LEFT JOIN WMS_IN_RQC_LINES l1 ON
		l1.RQC_LINE_ID = l2.RQC_LINE_ID
		WHERE l2.STATUS = '1'
		ORDER BY
		l2.GMT_MODIFIED
		) WHERE ROWNUM = 1 AND RCV_HEADER_ID =
		t1.RCV_HEADER_ID) as rqcUser,
		(SELECT QC_OPCODE2 FROM(
		SELECT
		l1.rcv_header_id,l1.QC_OPCODE1,l1.QC_OPCODE2,l1.GMT_MODIFIED
		FROM
		WMS_IN_TASK_INSTOCK_DETAIL l2
		LEFT JOIN WMS_IN_RQC_LINES l1 ON
		l1.RQC_LINE_ID = l2.RQC_LINE_ID
		WHERE l2.STATUS = '1'
		ORDER BY
		l2.GMT_MODIFIED
		) WHERE ROWNUM = 1 AND RCV_HEADER_ID =
		t1.RCV_HEADER_ID) as rqcUser2,
		(SELECT GMT_MODIFIED FROM(
		SELECT
		l1.rcv_header_id,l1.QC_OPCODE1,l1.QC_OPCODE2,l1.GMT_MODIFIED
		FROM
		WMS_IN_TASK_INSTOCK_DETAIL l2
		LEFT JOIN WMS_IN_RQC_LINES l1 ON
		l1.RQC_LINE_ID = l2.RQC_LINE_ID
		WHERE l2.STATUS = '1'
		ORDER BY
		l2.GMT_MODIFIED
		) WHERE ROWNUM = 1 AND RCV_HEADER_ID =
		t1.RCV_HEADER_ID) as rqcDate
		<!-- 加入验收员 -->
		FROM
		wms_in_rcv_headers t1
		LEFT JOIN wms_in_rcv_headers_profiles t2 ON
		t1.RCV_HEADER_ID =
		t2.RCV_HEADER_ID
		LEFT JOIN OMS_ASN_BILL_H t3 ON
		t1.ASN_HEADER_ID = t3.BILLID
		WHERE
		<!-- t1.rcv_header_id = t2.RCV_HEADER_ID -->
		1=1
		and t1.rcv_header_id IN (
		<!-- 6.11新加 -->
		SELECT
		t1.RCV_HEADER_ID
		FROM
		wms_in_task_instock_detail t1
		LEFT JOIN
		WMS_IN_RCV_LINES t2 ON t1.RCV_LINE_ID = t2.RCV_LINE_ID
		WHERE
		1=1
		<if test="enterpriseId != null  and enterpriseId !=''">
			and t1.enterprise_id = #{enterpriseId}
		</if>
		<if test="itemZjm != null  and itemZjm !=''">
			and UPPER(t2.ITEM_ZJM) like
			CONCAT(CONCAT('%',UPPER(#{itemZjm})),'%')
		</if>
		<if test="containerCode != null  and containerCode !=''">
			AND UPPER(t1.CONTAINER_CODE) like
			CONCAT(CONCAT('%',UPPER(#{containerCode})),'%')
		</if>
		<if test="instockLabel != null  and instockLabel !=''">
			AND UPPER(t1.INSTOCK_LABEL) like
			CONCAT(CONCAT('%',UPPER(#{instockLabel})),'%')
		</if>
		<if test="bldgId != null  and bldgId !=''">
			AND t2.BLDG_ID = #{bldgId}
		</if>
		<if test="whAreaCategoryCode != null  and whAreaCategoryCode !=''">
			AND UPPER(t1.WH_AREA_CATEGORY_CODE) =
			UPPER(#{whAreaCategoryCode})
		</if>
		<if test="whAreaCode != null  and whAreaCode !=''">
			AND UPPER(t1.WH_AREA_CODE) like
			CONCAT(CONCAT('%',UPPER(#{whAreaCode})),'%')
		</if>
		AND(t1.status = '1' OR t1.status IS NULL)
		and t1.piece_qty &lt;&gt; 0
		GROUP BY
		t1.RCV_HEADER_ID
		<!-- 6.11新加 -->
		<!-- SELECT RCV_HEADER_ID FROM wms_in_task_instock_detail where status 
			= '1' or status is null GROUP BY RCV_HEADER_ID -->
		)
		<if test="rcvUser != null  and rcvUser !=''">
			and UPPER(t1.EXECUTOR_USER) like
			CONCAT(CONCAT('%',UPPER(#{rcvUser})),'%')
		</if>
		<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="rcvType != null  and rcvType !=''">
			and t1.rcv_type = #{rcvType}
		</if>
		<!-- 6.11新加 -->
		<if test="rcvNo != null  and rcvNo !=''">
			and UPPER(t1.auto_bill_num) like
			CONCAT(CONCAT('%',UPPER(#{rcvNo})),'%')
		</if>
		<if test="billNo != null  and billNo !=''">
			and UPPER(t1.ASN_AUTO_BILL_NUM) like
			CONCAT(CONCAT('%',UPPER(#{billNo})),'%')
		</if>
		<if test="shipperZjm != null  and shipperZjm !=''">
			and UPPER(t1.SHIPPER_ZJM) like
			CONCAT(CONCAT('%',UPPER(#{shipperZjm})),'%')
		</if>
		<if test="shipperCondition != null  and shipperCondition !=''">
			and(
			UPPER(t1.SHIPPER_ZJM) like
			CONCAT(CONCAT('%',UPPER(#{shipperCondition})),'%')
			or
			(UPPER(t2.VENDOR_NUM) like
			CONCAT(CONCAT('%',UPPER(#{shipperCondition})),'%') or
			UPPER(t2.CUSTOMER_NUM) like
			CONCAT(CONCAT('%',UPPER(#{shipperCondition})),'%'))
			or
			(UPPER(t2.VENDOR_NAME) like
			CONCAT(CONCAT('%',UPPER(#{shipperCondition})),'%') or
			UPPER(t2.CUSTOMER_NAME) like
			CONCAT(CONCAT('%',UPPER(#{shipperCondition})),'%'))
			)
		</if>

		<if test="startTime != null">
			and t1.rcv_date &gt;= #{startTime}
		</if>
		<if test="endTime != null">
			and t1.rcv_date &lt;= #{endTime}
		</if>
		<!-- 6.11新加 -->
		and t1.rcv_type not in ('5','12','13','14','15')
		order by t1.rcv_date
		desc,t1.ASN_AUTO_BILL_NUM desc
	</select>

	<select id="findListPad" 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.WAREHOUSE_NAME,
		t2.WAREHOUSE_AREA_NAME,
		t2.batch_production_date as batchProductionDate,
		t2.batch_expired_date
		as batchExpiredDate,
		t3.ITEM_NUM,
		t3.ITEM_NAME,
		t3.CATEGORY_NAME,
		t3.CATEGORY_ID,
		t3.PIECE_PRICE_TI as piecePriceTi,
		t3.PIECE_PRICE_TE as
		piecePriceTe,
		TRUNC(
		decode(
		nvl( t3.PIECE_PRICE_TI, 1 ),
		0,
		0,
		nvl(
		t3.PIECE_PRICE_TE, 0 ) / nvl( t3.PIECE_PRICE_TI, 1 )
		),
		4
		) * 100 AS
		taxRate,
		<!-- TRUNC(nvl(t3.PIECE_PRICE_TE,0)/nvl(t3.PIECE_PRICE_TI,1), 4) * 100 
			as taxRate, -->
		t4.VENDOR_ID as vendorId,
		t4.ORGN_ID as orgId,
		t4.RCV_DATE as rcvDate,
		t5.WAREHOUSE_NUM,

		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,

		t3.MANUFACTURER_ID as
		manufacturerId,
		t3.MANUFACTURER_CODE as manufacturerCode,
		t3.MANUFACTURER_NAME as manufacturerName,
		t3.ITEM_HEALTH_REGISTER_NBR
		as itemHealthRegisterNbr,
		t3.ITEM_HEALTH_COMMON_NAME as
		itemHealthCommonName,
		t3.RETURN_INFO as returnInfo,
		t3.RETURN_COLD_QUALITY as returnColdQuality,
		t3.RETURN_EXPLAIN as
		returnExplain,
		t3.IS_LOCK as isLock,
		t3.IS_HOSTED as isHosted,
		t3.ITEM_IS_SPECIAL as itemIsSpecial,
		t3.IS_BACTERIA as isBacteria,
		t3.SHELF_LIFE_DAYS as shelfLifeDays,
		t3.SHELF_LIFE_UNIT as
		shelfLifeUnit,
		<!-- t3.manufacturer_name as manufacturerName, t3.manufacturer_id as manufacturerId, -->

		t2.bldg_name as bldgName,
		t2.bldg_code as bldgCode,

		t1.locator_floor_num as locatorFloorNum,
		t1.rqc_header_id as
		rqcHeaderId,

		t4.executor_user as rcvUser,

		t2.DELEGATION_PARTY_ID as
		delegationPartyId,
		t2.DELEGATION_PARTY_NAME as delegationPartyName,
		t2.is_delegate as isDelegate,
		t2.SHOW_PRODUCTION_DATE as
		showProductionDate,
		t2.SHOW_EXPIRED_DATE as showExpiredDate,
		t2.WAREHOUSE_ROOM_CODES as warehouseRoomCodes,
		t2.PIECE_CONVERSION_RATE as pieceConversionRate,
		t2.ASN_LINE_REMARK as
		asnLineRemark,
		t2.ITEM_ZJM as itemZjm,
		t4.rcv_type as rcvType,
		t5.MTL_OWNER_NUM as mtlOwnerNum,
		t5.MTL_OWNER_NAME as mtlOwnerName,
		t4.ASN_AUTO_BILL_NUM as asnBillNum,
		t4.AUTO_BILL_NUM as autoBillNum,
		t2.production_place as productionPlace,
		t2.approval_number as
		approvalNumber,
		t2.RQC_REPORT_NO as rqcReportNo,
		t3.allow_decimal as
		allowDecimal,
		t3.STORE_TEMPERATURE as storeTemperature,

		t4.SALE_BILL_TYPE as saleBillType,
		t5.BUYER_MANE as bizUser,
		t5.ORGN_NAME as orgName,
		t2.WH_LOCATOR_ID as rcvWhLocatorId,
		t2.WH_LOCATOR_CODE as rcvWhLocatorCode,
		t2.WH_AREA_CODE as
		rcvWhAreaCode,
		t2.WH_AREA_CATEGORY_CODE as rcvWhAreaCategoryCode,

		t2.INVENTORY_OP_TYPE as inventoryOpType,
		t2.HOST_LOCATOR_ID as
		hostLocatorId,
		coalesce(t5.VENDOR_NAME,t5.CUSTOMER_NAME) as shipperName


		FROM
		wms_in_task_instock_detail 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
		LEFT JOIN
		wms_in_rcv_headers t4
		on
		t4.rcv_header_id = t1.rcv_header_id
		LEFT JOIN
		WMS_IN_RCV_HEADERS_PROFILES t5
		on t5.rcv_header_id = t1.rcv_header_id
		LEFT JOIN
		WMS_IN_RQC_LINES t6
		on t6.RQC_LINE_ID = t1.RQC_LINE_ID

		where
		1=1
		<if test="enterpriseId != null  and enterpriseId !=''">
			and t1.enterprise_id = #{enterpriseId}
		</if>
		<if test="itemBarCode != null  and itemBarCode !=''">
			and (t3.ITEM_BARCODE = #{itemBarCode}
			or UPPER(t2.ITEM_ZJM)
			like CONCAT(CONCAT('%',UPPER(#{itemBarCode})),'%')
			)
		</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="itemId != null  and itemId !=''">
			and t1.item_id = #{itemId}
		</if>
		<if test="rcvLineId != null  and rcvLineId !=''">
			and t1.rcv_line_id = #{rcvLineId}
		</if>
		<if test="status != null  and status !=''">
			and t1.status = #{status}
		</if>

		<if test="condition != null  and condition !=''">
			and (t3.ITEM_BARCODE = #{condition}
			or t1.wh_area_code = #{condition}
			or t1.container_code = #{condition}
			or t1.instock_label= #{condition}
			or t6.CONTAINER_CODE = #{condition}
			or t6.CONTAINER_CODE =
			#{condition}
			<!-- or UPPER(t2.ITEM_ZJM) like CONCAT(CONCAT('%',UPPER(#{condition})),'%') -->
			)
		</if>

		<if test="AreaCode != null  and AreaCode !=''">
			and t1.wh_area_code = #{AreaCode}
		</if>
		<if test="container != null  and container !=''">
			and t1.container_code = #{container}
		</if>
		<if test="label != null  and label !=''">
			and t1.instock_label = #{label}
		</if>
		and t4.rcv_type not in ('5','12','13','14','15')
		and t1.piece_qty
		&lt;&gt; 0
		order by t1.gmt_modified
		desc
	</select>
	<select id="findContainer" resultType="String">
		select
		distinct
		t1.container_code
		FROM
		wms_in_task_instock_detail t1
		where
		t1.enterprise_id = #{enterpriseId}
		and t1.warehouse_id = #{warehouseId}
		and t1.mtl_owner_id = #{mtlOwnerId}
		and t1.status = '1'
		and t1.container_code!='N/A'
		order by
		t1.container_code
		desc
	</select>
	
	<select id="findTaskListByContainerNo" resultMap="mainMap">
		select
		<include refid="mainClomuns"></include>
		FROM
		wms_in_task_instock_detail t1
		where
		<!-- t1.enterprise_id = #{enterpriseId}
		and t1.warehouse_id = #{warehouseId}
		and t1.mtl_owner_id =
		#{mtlOwnerId}
		and -->
		 t1.status = '1'
		and CONTAINER_CODE = #{containerNo}
	</select>
</mapper>
