<?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.outbound.dao.OutcomingRecordHMapper">

    <select id="getH" resultType="com.prolog.cs.biz.outbound.model.OutcomingRecordH">
	SELECT DISTINCT
	OMSBILL.BILLID,
	OMSBILL.BILLNO,
	OMSBILL.BILLTYPE,
	OMSBILL.BRANCHID,
	OMSBILL.BRANCHCODE,
	OMSBILL.BRANCHNAME,
	OMSBILL.CONSIGNORID,
	nvl(OMSBILL.CONSIGNORCODE,OMSBILL.CONSIGNORID) CONSIGNORCODE,
	OMSBILL.CONSIGNORNAME,
	'' CONSIGNOR_BILL_NO,
	OMSBILL.REF_BILLTYPE REFBILLTYPE,
	OMSBILL.REF_BILLID REFBILLID,
	OMSBILL.REF_BILLNO REFBILLNO,
	OMSBILL.SALESPERSON_ID salespersonId,
	OMSBILL.SALESPERSON_NAME salespersonName,
	'' PURORG_ID,
	'' PURORG_NAME,
	OMSBILL.BRANCHAREA,
	OMSBILL.BRANCH_ADDRESS branchAddress,
	'' businessNo,
	OMSBILL.SHIPPER_TYPE shipperType,
	REGEXP_REPLACE( OMSBILL.SHIPPERID, '(\w+)(-)+(\w+)', '\3' ) SHIPPERCODE,
	OMSBILL.SHIPPERNAME SHIPPERNAME,
	OMSBILL.PRIORITY_LEVEL priorityLevel,
	OMSBILL.ARRIVAL_MODE arrivalMode,
	OMSBILL.SHIP_LINE_ID shilineId,
	OMSBILL.CARRIER_ID carrierId,
	OMSBILL.CARRIER_NAME carrierName,
	'10' STATUS,
	coalesce( INNERCHECK.CHECK_TIME, BARCODE.CHECK_TIME, BILL.GMT_CREATE, BILL.GMT_CREATE ) billOutTime,
	NULL CONFIRMTIME, INNERCHECK.CHECKER_NAME OPERATOR,
	NULL CONFIRMER,
	XXX2.PCS totPcs,
	BILL.TOTAL_QTY totQty,
	COALESCE( X1.TOT_BOX_NUM, 0 ) totBoxNum,
	BILL.TOTAL_WEIGHT totWeight,
	BILL.TOTAL_VOLUME totVolume,
	BARCODE.CHECKER_NAME EXTERNALREVIEWER,
	BARCODE.CHECK_TIME CHECK_DATE,
	BILL.GMT_CREATE WMSSCHEDTIME,
	OMSBILL.SALE_BRANCHID,
	OMSBILL.SALE_BRANCHCODE,
	OMSBILL.SALE_BRANCHNAME,
	OMSBILL.CREATEBY,
	OMSBILL.CREATEDATE,
	OMSBILL.LASTMODIFYBY,
	OMSBILL.LASTMODIFYDATE,
	OMSBILL.P_ENTERPRISEID enterpriseid,
	OMSBILL.P_ENTERPRISENAME enterprisename,
	XXX1.STAGE_CODE_MIN,
	XXX1.STAGE_CODE_MAX,
	redFlag.red_flag attrStr8,
	'N' attrStr1
FROM
	OMS_DO_BILL_H OMSBILL
	LEFT JOIN WMS_WAVE_BILL BILL ON BILL.SRC_ID = OMSBILL.BILLID
	LEFT JOIN (
	SELECT
		*
	FROM
		(
		SELECT ROWNUM
			row_flag,
			taskd.CHECK_TIME,
			taskd.CHECKER_NAME,
			taskd.CHECKER_NAME_2
		FROM
			wms_pick_task_h taskh
			LEFT JOIN wms_pick_task_d taskd ON taskd.Pick_task_id = taskh.pick_task_id
			LEFT JOIN WMS_WAVE_BILL bill ON bill.BILL_GROUP_ID = taskh.BILL_GROUP_ID
		WHERE
			bill.src_id = #{omsBillId}
		) a
	WHERE
		row_flag = 1
	) INNERCHECK ON 1 = 1
	LEFT JOIN (
	SELECT
		*
	FROM
		(
		SELECT ROWNUM
			ROWFLAG,
			BARCODE.CHECKER_NAME,
			BARCODE.CHECK_TIME
		FROM
			WMS_BARCODE_H barcode
			LEFT JOIN WMS_WAVE_BILL bill ON bill.BILL_GROUP_ID = barcode.BILL_GROUP_ID
		WHERE
			bill.src_id = #{omsBillId}
			AND barcode.CHECK_TIME IS NOT NULL
		) b
	WHERE
		ROWFLAG = 1
	) BARCODE ON 1 = 1
	LEFT JOIN (
	SELECT
		count( * ) TOT_BOX_NUM
	FROM
		WMS_BARCODE_H barcodeH
		LEFT JOIN WMS_WAVE_BILL bill ON bill.BILL_GROUP_ID = barcodeH.BILL_GROUP_ID
	WHERE
		bill.src_id = #{omsBillId}
		AND barcodeH.TYPE = '0'
		AND NOT EXISTS ( SELECT 1 FROM WMS_BARCODE_D g WHERE barcodeH.id = g.BARCODE_ID AND g.IS_PIECE = 0 )
	) X1 ON 1 = 1
	LEFT JOIN (
	SELECT
		billid,
		min(stage_code) stage_code_min,
		max(stage_code) stage_code_max
	FROM
		(
		SELECT
			OMSBILL.billid,
			COALESCE( x.stage_code, '机动' ) stage_code
		FROM
			OMS_DO_BILL_H OMSBILL
			LEFT JOIN WMS_WORK_STAGE_RUNTIME x ON OMSBILL.BILLID = x.bill_group_id
		WHERE
			OMSBILL.billid = #{omsBillId}
		) X
		GROUP BY x.billid
	) XXX1 ON 1 = 1
	LEFT JOIN (
	SELECT
		sum( pcs ) pcs
	FROM
		(
		SELECT
			floor( sum( qty ) / case_conversion_rate ) pcs,
			SRC_ID
		FROM
			(
			SELECT DISTINCT
				g.id,
				g.qty,
				m.case_conversion_rate,
				n.SRC_ID
			FROM
				WMS_BARCODE_D g
				LEFT JOIN wms_pick_task_d d ON d.PICK_TASK_D_ID = g.WMS_PICK_TASK_D_ID
				LEFT JOIN wms_wave_storage s ON d.WAVE_STORAGE_ID = s.ID
				LEFT JOIN WMS_WAVE_BILL_D m ON m.ID = s.BILL_D_ID
				LEFT JOIN WMS_BARCODE_H f ON f.id = g.BARCODE_ID
				LEFT JOIN WMS_WAVE_BILL n ON n.BILL_GROUP_ID = f.BILL_GROUP_ID
			WHERE
				n.SRC_ID =#{omsBillId}
				AND g.qty > 0
				AND g.IS_PIECE = 0
			) c
		GROUP BY
			SRC_ID,
			case_conversion_rate
		) z
	) XXX2 ON 1 = 1
	LEFT JOIN (
		SELECT
			CASE WHEN
			sum(  b.item_qty - COALESCE ( x3.RUSHREDNUMB, 0 ) ) &lt;= 0 THEN
				'Y' ELSE 'N'
			END red_flag
		FROM
			oms_do_bill_d b
			LEFT JOIN (SELECT SRC_LINE_ID, sum( t.qty ) RUSHREDNUMB FROM wms_sale_red_record t WHERE t.src_id = #{omsBillId} GROUP BY SRC_LINE_ID) X3 ON 1 = 1
			AND b.LINEID = x3.SRC_LINE_ID
		WHERE
			b.billid = #{omsBillId}
	) redFlag ON 1 = 1
WHERE
	1 = 1
	AND OMSBILL.BILLID = #{omsBillId}
    </select>


	<select id="getD" resultType="com.prolog.cs.biz.outbound.model.OutcomingRecordD">
WITH R AS (
SELECT BILLNO,MERGENUMBER,max(REVIEWER) REVIEWER,max(TWOREVIEWER) TWOREVIEWER,max(REVIEWSTAGENO) REVIEWSTAGENO,max(SORTER) SORTER,max(PICKER_NO) PICKER_NO,STAGAREADESCRIP,RT_REASON,RETURNGOODSTATUS,WMSSCHEDTIME,DISPATCHER,EXTERREVIEWER,EXTERREVIEWER2 from(
	SELECT DISTINCT
		OMSBILL.BILLNO,
		barcodeH.LCL_NO MERGENUMBER,
		(
		SELECT
			to_char( wm_concat ( DISTINCT d.CHECKER_NAME ) )
		FROM
			WMS_PICK_TASK_D d
		WHERE
			d.PICK_TASK_ID = PICKTASKH.PICK_TASK_ID
			AND d.CHECKER_NAME IS NOT NULL
			GROUP BY d.PICK_TASK_ID
		) REVIEWER,
		(
		SELECT
			to_char( wm_concat ( DISTINCT d.CHECKER_NAME_2 ) )
		FROM
			WMS_PICK_TASK_D d
		WHERE
			d.PICK_TASK_ID = PICKTASKH.PICK_TASK_ID
			AND CHECKER_NAME_2 IS NOT NULL
			GROUP BY PICKTASKH.PICK_TASK_ID
		) TWOREVIEWER,
		PICKTASKH.STATION_CODE REVIEWSTAGENO,
		o.OPERATOR_NAME SORTER,
		o.OPERATOR_CODE PICKER_NO,
		WWSR.STAGE_CODE STAGAREADESCRIP,
		'-' RT_REASON,
		'-' RETURNGOODSTATUS,
		BILL.GMT_CREATE WMSSCHEDTIME,
		BILL.CREATOR_NAME DISPATCHER,
		BARCODEH.CHECKER_NAME EXTERREVIEWER,
		BARCODEH.CHECKER_NAME_2 EXTERREVIEWER2
	FROM
		OMS_DO_BILL_H OMSBILL
		LEFT JOIN WMS_WAVE_BILL BILL ON BILL.SRC_ID = OMSBILL.BILLID
		LEFT JOIN WMS_PICK_TASK_H PICKTASKH ON PICKTASKH.BILL_GROUP_ID = BILL.BILL_GROUP_ID
		LEFT JOIN WMS_PICK_ORDER o ON o.PICK_ORDER_ID = PICKTASKH.PICK_ORDER_ID
		LEFT JOIN WMS_BARCODE_H BARCODEH ON BARCODEH.BILL_GROUP_ID = BILL.BILL_GROUP_ID
		LEFT JOIN WMS_WORK_STAGE_RUNTIME WWSR ON WWSR.bill_group_id = BILL.BILL_GROUP_ID
	WHERE
		1 = 1
		AND OMSBILL.BILLID = #{omsBillId} and PICKTASKH.type='0'
		) l GROUP BY BILLNO,MERGENUMBER,STAGAREADESCRIP,RT_REASON,RETURNGOODSTATUS,WMSSCHEDTIME,DISPATCHER,EXTERREVIEWER,EXTERREVIEWER2
	),
	A AS ( SELECT * FROM ( SELECT T.*, ROWNUM ROW_FLG FROM R T WHERE 1 = 1 ) X WHERE 1 = 1 AND ROW_FLG = 1 ),
	B AS (
	SELECT
		SRC_LINE_ID,
		x.reason RUSHREDREASON
	FROM
		(
		SELECT
			T.SRC_LINE_ID,
			t.reason,
			ROW_NUMBER ( ) OVER ( PARTITION BY coalesce( t.SRC_LINE_ID, '-1' ) ORDER BY T.GMT_CREATE DESC ) AS ROW_FLG
		FROM
			wms_sale_red_record T
		WHERE
			1 = 1
			AND SRC_ID = #{omsBillId}
		) X
	WHERE
		1 = 1
		AND ROW_FLG = 1
	),
	C AS (
	SELECT
		count( * ) TOT_BOX_NUM_X
	FROM
		WMS_BARCODE_H barcodeH
		LEFT JOIN WMS_WAVE_BILL bill ON bill.BILL_GROUP_ID = barcodeH.BILL_GROUP_ID
	WHERE
		bill.src_id = #{omsBillId}
		AND barcodeH.TYPE = '0'
	)

	SELECT
		X.*, ROWNUM refSeqno
	FROM (
			SELECT DISTINCT
			OMSBILLD.LINEID lineId,
			OMSBILL.BILLID BILLID,
			OMSBILL.BILLNO BILLNO,
			OMSBILLD.SEQNO SEQNO,
			OMSBILL.REF_BILLTYPE,
			OMSBILL.REF_BILLID,
			OMSBILL.REF_BILLNO,
			OMSBILL.CONSIGNORID,
			coalesce(OMSBILL.CONSIGNORCODE,OMSBILL.CONSIGNORID) CONSIGNORCODE,
			OMSBILL.CONSIGNORNAME,
			OMSBILLD.ITEMID,
			OMSBILLD.ITEMCODE,
			OMSBILLD.ITEMNAME,
			OMSBILLD.PARERNT_ITEMID parerntItemid,
			OMSBILLD.TYPE_ID categoryId,
			OMSBILLD.PACK_SIZE packSize,
			OMSBILLD.PACK_UNITS packUnits,
			OMSBILLD.MACH_TYPE machType,
			s.MANUFACTURER_LOT_NUM lotNo,
			s.BATCH_NUM batchNo,
			to_char(s.PRODUCTION_DATE,'yyyyMMdd') pdateFrom,
			to_char(s.EXPIRED_DATE,'yyyyMMdd') validUntil,
			OMSBILLD.MANUFACTURER,
			coalesce( floor((OMSBILLD.ITEM_QTY  - coalesce(x3.RUSHREDNUMB, 0) ) / BILLD.CASE_CONVERSION_RATE) , OMSBILLD.ITEM_PCS) affirmedPcs,
			coalesce( MOD(( OMSBILLD.ITEM_QTY  - coalesce(x3.RUSHREDNUMB, 0) ) ,BILLD.CASE_CONVERSION_RATE),OMSBILLD.ITEM_ODD) affirmedOdd,
			coalesce(OMSBILLD.FREE_QTY,0) affirmedFreeqty,
			coalesce(OMSBILLD.ITEM_QTY - coalesce(x3.RUSHREDNUMB, 0),OMSBILLD.ITEM_QTY) affirmedQty,
			COALESCE( BILLD.TOTAL_WEIGHT, 0 ) affirmedWeight,
			COALESCE( BILLD.TOTAL_VOLUME, 0 ) affirmedVolume,
			coalesce(OMSBILLD.PRICE,0) PRICE,
			OMSBILLD.TNI_PRICE tniPrice,
			coalesce(OMSBILLD.PRICE,0) * BILLD.PIECE_QTY AMT,
			OMSBILLD.TNI_PRICE * BILLD.PIECE_QTY TNI_AMT,
			OMSBILLD.TNI_PRICE PIECE_COST_TE,
			coalesce(OMSBILLD.PRICE,0) PIECE_COST_TI,
			CASE WHEN ( coalesce( OMSBILLD.price / OMSBILLD.tni_price, 0 ) ) = 0 THEN
					13 ELSE floor( ( ( coalesce( OMSBILLD.price / OMSBILLD.tni_price, 0 ) ) - 1 ) * 100 )
			END TAX_RATE,
			X2.TOT_BOX_NUM_X MERGENUMBER,
			A.REVIEWER,
			A.TWOREVIEWER,
			A.REVIEWSTAGENO,
			A.SORTER,
			A.PICKER_NO,
			coalesce(x3.RUSHREDNUMB,0) RUSHREDNUMB,
			x1.RUSHREDREASON RUSHREDREASON,
			A.STAGAREADESCRIP,
			A.RT_REASON rtReason,
			A.RETURNGOODSTATUS,
			A.WMSSCHEDTIME,
			A.DISPATCHER,
			A.EXTERREVIEWER,
			A.EXTERREVIEWER2,
			OMSBILLD.REMARK,
			OMSBILLD.CREATEBY,
			OMSBILLD.CREATEDATE,
			OMSBILLD.LASTMODIFYBY,
			OMSBILLD.LASTMODIFYDATE,
			OMSBILL.P_ENTERPRISEID enterpriseid,
			OMSBILL.P_ENTERPRISENAME enterprisename,
			X2.TOT_BOX_NUM_X SUMBOXNUM
		FROM
			OMS_DO_BILL_D OMSBILLD
			LEFT JOIN OMS_DO_BILL_H OMSBILL ON OMSBILL.BILLID = OMSBILLD.BILLID
			LEFT JOIN WMS_WAVE_BILL BILL ON BILL.SRC_ID = OMSBILL.BILLID
			LEFT JOIN WMS_WAVE_BILL_D BILLD ON BILL.ID = BILLD.BILL_ID
			AND OMSBILLD.ITEMID = BILLD.ITEM_ID
			AND BILLD.Src_Line_Id = OMSBILLD.LINEID
			LEFT JOIN WMS_WAVE_STORAGE s on BILLD.ID = s.BILL_D_ID
			LEFT JOIN A ON 1 = 1
			LEFT JOIN B X1 ON 1 = 1
			AND OMSBILLD.LINEID = x1.SRC_LINE_ID
			LEFT JOIN C X2 ON 1 = 1
			LEFT JOIN (SELECT SRC_LINE_ID, sum( t.qty ) RUSHREDNUMB FROM wms_sale_red_record t WHERE t.src_id = #{omsBillId} GROUP BY SRC_LINE_ID) X3 ON 1 = 1
			AND OMSBILLD.LINEID = x3.SRC_LINE_ID
		WHERE
			OMSBILLD.BILLID = #{omsBillId}
	) X
	</select>

	<select id="getOmsPackingData" resultType="com.prolog.cs.biz.outbound.model.OmsPackingData">
		SELECT DISTINCT g.id,obd.seqno,obd.seqno refSeqno,obh.billid,obh.billno,s.item_id itemid,s.warehouse_id branchid,obh.consignorid,obh.ref_billid refBillid,
		obh.ref_billno refBillno,obh.shipperid,obh.shippercode,obh.shippername,
		case when f.type = 0 then 0
		WHEN f.type = '0' THEN '2'
		WHEN f.type = '1' THEN '1'
		end as  boxmode,f.lcl_no mergenumber,
		n.bill_group_id unionbillcode,f.check_time_2 createdate,f.check_time_2 lastmodifydate,f.checker_name createby,
		f.checker_name lastmodifyby,obh.p_enterpriseid,obh.p_enterprisename,'0' tms_flag,f.check_time_2 confirmdate,f.barcode boxcode,
		s.WEIGHT totweight,s.VOLUME totvolume,g.qty totqty,
		case when g.IS_PIECE = 0 then 1
		WHEN g.IS_PIECE = 1 THEN 0
		WHEN g.IS_PIECE = 1 THEN 1
		end as  wholenum,
		case when g.IS_PIECE = 0 then 0
		WHEN g.IS_PIECE = 1 THEN 1
		WHEN g.IS_PIECE = 2 THEN 0
		end as  totboxnum,0 isgreenroad,1 totpcs,x.stage_code stagecode
		FROM WMS_BARCODE_D g
		LEFT JOIN wms_pick_task_d d ON d.PICK_TASK_D_ID = g.WMS_PICK_TASK_D_ID
		LEFT JOIN wms_wave_storage s ON d.WAVE_STORAGE_ID = s.ID
		left join WMS_WAVE_BILL_D m on m.ID = s.BILL_D_ID
		left join OMS_DO_BILL_D obd on m.Src_Line_Id = obd.LINEID
		LEFT JOIN WMS_BARCODE_H f ON f.id = g.BARCODE_ID
		LEFT JOIN WMS_WAVE_BILL n ON n.BILL_GROUP_ID = f.BILL_GROUP_ID
		LEFT JOIN OMS_DO_BILL_H obh ON obh.billid = n.SRC_ID
		LEFT JOIN (
			SELECT min(STAGE_CODE) STAGE_CODE,BILL_GROUP_ID from WMS_WORK_STAGE_RUNTIME where BILL_GROUP_ID = #{billGroupId} GROUP BY BILL_GROUP_ID
		) x on x.BILL_GROUP_ID = obh.billno
		WHERE  1=1
		AND n.BILL_GROUP_ID = #{billGroupId}
	</select>

</mapper>