UrY3MJ5g

1USE [ILS]
2GO
3/****** Object:  StoredProcedure [dbo].[KC_PalletBuilding]    Script Date: 09.11.2018 8:55:33 ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8
9/*================================================
10Create date: 	MK	2017
11Modify date: 	SL	09/08/2018
12				YR	04/09/2018	Начало обработки ВГХ весовых товаров
13				YR	14/09/2018	PO1 fixes implementation
14				YR	15/10/2018	Докладка к целой паллете. Обработка WW. Обработка CS
15				MF	29/10/2018	Переработка разбиения по группам (не)смешивания
16				YR	30/10/2018	Максимальные высота и вес контейнера из условий отгрузки
17				YR	31/10/2018	Обработка вариантов сортировки. Типизирована таблица @SAR
18				YR	02/11/2018	Замена расчетов NUM_OLD на GROUP_ID
19				YR	06/11/2018	Исправление Join KC_ORIG_SD на TOP 1
20				MF	07/11/2018	Добавление Stop Seq, перенос Pick Seq в начало и сборка по нему
21				MF	09/11/2018	Весовым паллетам назначается тип целой паллеты, исправления в цикле CS
22Description:	Присвоение класса упаковки в SAR
23================================================*/
24
25ALTER PROCEDURE [dbo].[KC_PalletBuilding] @LAUNCH_NUM NUMERIC(9,0) 
26AS
27BEGIN
28
29SET NOCOUNT ON
30;
31	--
32	
33	--DROP TABLE #SAR_RESTRICTION
34	--DECLARE @LAUNCH_NUM NUMERIC(9,0) = 1968
35							  
36	DECLARE @topUp nchar(1)
37	SELECT @topUp =  SYS1VALUE
38	FROM dbo.GENERIC_CONFIG_DETAIL
39	WHERE RECORD_TYPE = N'KC_SHIPPING_VALUES'
40		AND IDENTIFIER = N'90'
41	;
42	--1. Собираем требования по волне
43	SELECT * INTO #SAR_RESTRICTION FROM dbo.KC_SH_SD_SAR_RESTRICTION(@LAUNCH_NUM, NULL, NULL, NULL)
44	;
45
46	--31/10/2018
47	DECLARE @SORT_TYPES TABLE (ST nvarchar(25), EXPR nvarchar(250))
48	;
49	DECLARE @expr nvarchar(250)
50		, @sql nvarchar(max)
51		, @sortType nvarchar(25)
52	;
53	WITH S AS (
54		SELECT DISTINCT SORTING_TYPE FROM #SAR_RESTRICTION
55	)
56	INSERT INTO @SORT_TYPES
57	SELECT SORTING_TYPE, USER1VALUE
58	FROM S
59	LEFT JOIN (SELECT IDENTIFIER, USER1VALUE FROM dbo.GENERIC_CONFIG_DETAIL WHERE RECORD_TYPE = N'KC_SORT_TYPE') ST
60		ON S.SORTING_TYPE = ST.IDENTIFIER
61	;
62
63	--SELECT*FROM @SORT_TYPES
64
65	WHILE EXISTS (SELECT * FROM @SORT_TYPES)
66	BEGIN
67		SELECT TOP 1 @sortType = ST, @expr = EXPR FROM @SORT_TYPES
68
69		SET @sql = N'
70		WITH tmp AS (
71			SELECT INTERNAL_SHIP_ALLOC_NUM AS ISN
72			, ROW_NUMBER() OVER (ORDER BY ' + ISNULL(@expr, N'PICKING_SEQ') + N') AS PICK_SEQ
73			FROM SHIPMENT_ALLOC_REQUEST
74			WHERE INTERNAL_SHIP_ALLOC_NUM IN (
75				SELECT INTERNAL_SHIP_ALLOC_NUM
76				FROM #SAR_RESTRICTION
77				WHERE SORTING_TYPE = @sortType
78			)
79		)
80		UPDATE SAR
81		SET SAR.PICKING_SEQ = tmp.PICK_SEQ
82		FROM dbo.SHIPMENT_ALLOC_REQUEST SAR
83		JOIN tmp ON SAR.INTERNAL_SHIP_ALLOC_NUM = tmp.ISN
84		WHERE SAR.LAUNCH_NUM = @LAUNCH_NUM
85			AND SAR.ALLOCATED_QTY > 0.0'
86		;
87		--SELECT @sortType, @expr, @sql
88		EXEC sp_executesql @sql
89			, N'@sortType nvarchar(25), @LAUNCH_NUM int'
90			, @sortType = @sortType, @LAUNCH_NUM = @LAUNCH_NUM
91		;
92		DELETE FROM @SORT_TYPES WHERE ST = @sortType
93	END
94	;
95
96
97	/*	Пересоздать тип (добавить/убрать поля)
98	IF EXISTS (select * from sys.types where name = N'SARDATA')
99		DROP TYPE SARDATA
100	CREATE TYPE SARDATA AS TABLE
101	(	CTYPE NVARCHAR(25), MTYPE NVARCHAR(25), PCL NVARCHAR(25),CIIPL NVARCHAR(50), SORTING_TYPE NVARCHAR(25)
102		, INTERNAL_SHIP_ALLOC_NUM NUMERIC(9,0), INTERNAL_SHIPMENT_NUM NUMERIC(9,0)
103		, PICKING_ZONE NCHAR(1),FROM_LOC NVARCHAR (25), PICKING_SEQ INT, DIFF_ITEM_QTY_SHID_FP NUMERIC(9,0), ROW_UP NUMERIC(9,0)
104		, GROUP_ID NUMERIC (9,0), FROM_TEMPL_FIELD4 NVARCHAR (25), VOLUME NUMERIC (19,0), WEIGHT NUMERIC (19,0) , LOT NVARCHAR(25)
105		, MAXHEIGHT NUMERIC(9,0), MAXWEIGHT NUMERIC(9,0), CRASHABILITY NVARCHAR(25), CATEGORY NVARCHAR(25), ORDER_CLASS NVARCHAR(25) , STOP_SEQ NUMERIC(9,0)	)
106	GO
107	*/ 
108
109	DECLARE @SAR AS SARDATA
110	;
111	--2. Собираем приоритет хрупкости над обходом FRAGILE, проставляем признак консолидации по товару ITEM_SIZE, проставляем packing_class, если нужно по lot
112	WITH SAR AS (
113		SELECT IIF(FROM_WORK_ZONE LIKE N'WEIGHT_ITEM%' OR (FROM_TEMPL_FIELD1 = N'CB' AND FROM_TEMPL_FIELD4 = N'1'), N'Y', N'N') AS PICKING_ZONE
114			,* FROM dbo.SHIPMENT_ALLOC_REQUEST
115		WHERE LAUNCH_NUM = @LAUNCH_NUM
116	)
117	INSERT INTO @SAR (CTYPE, MTYPE, PCL,CIIPL, SORTING_TYPE,INTERNAL_SHIP_ALLOC_NUM, INTERNAL_SHIPMENT_NUM, PICKING_ZONE, FROM_LOC, PICKING_SEQ
118	, DIFF_ITEM_QTY_SHID_FP, FROM_TEMPL_FIELD4, VOLUME, WEIGHT, LOT, MAXHEIGHT, MAXWEIGHT, CRASHABILITY, CATEGORY, ORDER_CLASS, STOP_SEQ)
119	SELECT --IIF(SAR.FROM_WORK_ZONE IN (SELECT ZONE FROM ZONE WHERE USER_DEF1 = N'Y'), 
120		TMP.PALLET_TYPE, TMP.PALLET_TYPE_MONO
121	, CASE
122        WHEN TMP.PALLET_TYPE IS NOT NULL AND (TMP.DIFF_ITEM_QTY_PAL = 0 OR TMP.DIFF_ITEM_QTY_PAL > 1) AND (TMP.DIFF_LOT_QTY_PAL = 0 OR TMP.DIFF_LOT_QTY_PAL > 1)
123			AND (SAR.PICKING_ZONE = N'Y' OR (@topUp = N'1' AND SAR.ITEM NOT IN (SELECT ITEM FROM ITEM WHERE ITEM_CATEGORY4 = N'N')))
124			AND (SAR.PICKING_ZONE = N'Y' OR (SAR.FROM_WORK_ZONE NOT IN (SELECT ZONE FROM ZONE WHERE USER_DEF1 = N'Y') AND TMP.DIFF_ITEM_QTY_SHID_FP != 1)) --MF
125		THEN TMP.PALLET_TYPE
126
127		WHEN TMP.PALLET_TYPE IS NOT NULL AND (TMP.DIFF_ITEM_QTY_PAL = 0 OR TMP.DIFF_ITEM_QTY_PAL > 1) AND TMP.DIFF_LOT_QTY_PAL = 1
128			AND (SAR.PICKING_ZONE = N'Y' OR (@topUp = N'1' AND SAR.ITEM NOT IN (SELECT ITEM FROM ITEM WHERE ITEM_CATEGORY4 = N'N')))
129		THEN TMP.PALLET_TYPE+'.'+CAST(DENSE_RANK() OVER (PARTITION BY SAR.LAUNCH_NUM, SAR.ITEM ORDER BY SAR.LOT) AS nvarchar(3))
130
131		WHEN TMP.PALLET_TYPE IS NOT NULL AND TMP.DIFF_ITEM_QTY_PAL = 1 AND (TMP.DIFF_LOT_QTY_PAL = 0 OR TMP.DIFF_LOT_QTY_PAL > 1)
132			AND (SAR.PICKING_ZONE = N'Y' OR (@topUp = N'1' AND SAR.ITEM NOT IN (SELECT ITEM FROM ITEM WHERE ITEM_CATEGORY4 = N'N')))
133		THEN TMP.PALLET_TYPE+'.'+CAST(DENSE_RANK() OVER (PARTITION BY SAR.LAUNCH_NUM, SAR.ITEM ORDER BY SAR.LOGISTICS_UNIT) AS nvarchar(3))
134
135		WHEN TMP.PALLET_TYPE IS NOT NULL AND (TMP.DIFF_ITEM_QTY_PAL = 1 AND TMP.DIFF_LOT_QTY_PAL = 1) 
136		THEN TMP.PALLET_TYPE+'.'+CAST(DENSE_RANK() OVER (PARTITION BY SAR.LAUNCH_NUM, SAR.ITEM ORDER BY SAR.LOGISTICS_UNIT, SAR.LOT) AS nvarchar(3))
137		
138		WHEN TMP.PALLET_TYPE_MONO IS NOT NULL
139			AND (SAR.PICKING_ZONE = N'N' OR (@topUp = N'1' AND SAR.ITEM NOT IN (SELECT ITEM FROM ITEM WHERE ITEM_CATEGORY4 = N'N')))
140		THEN TMP.PALLET_TYPE_MONO+'.'+CAST(DENSE_RANK() OVER (PARTITION BY SAR.LAUNCH_NUM ORDER BY SAR.ITEM, SAR.LOGISTICS_UNIT) AS nvarchar(3))
141		
142		WHEN TMP.PALLET_TYPE IS NULL AND TMP.PALLET_TYPE_MONO IS NULL
143			THEN (SELECT TOP 1 CONTAINER_TYPE FROM dbo.CONTAINER_TYPE WHERE USER_DEF1 = N'Y')
144				+'.'+CAST(DENSE_RANK() OVER (PARTITION BY SAR.LAUNCH_NUM, SAR.ITEM, SAR.LOGISTICS_UNIT ORDER BY sar.LOGISTICS_UNIT) AS nvarchar(3)) 
145		ELSE (SELECT TOP 1 CONTAINER_TYPE FROM dbo.CONTAINER_TYPE WHERE USER_DEF1 = N'Y') 
146	END AS PCL,
147	CASE WHEN CONS_ALLOW = 'Y'
148		THEN TMP.ITEM
149		ELSE NULL
150	END AS CIIPL,
151	SORTING_TYPE,
152	SAR.INTERNAL_SHIP_ALLOC_NUM,
153	TMP.INTERNAL_SHIPMENT_NUM
154	,SAR.PICKING_ZONE
155	,SAR.FROM_LOC
156	,SAR.PICKING_SEQ
157	,tmp.DIFF_ITEM_QTY_SHID_FP
158	,SAR.FROM_TEMPL_FIELD4
159	,SAR.ALLOCATED_QTY * SAR.ITEM_HEIGHT * SAR.ITEM_WIDTH * SAR.ITEM_LENGTH AS VOLUME
160	,SAR.ALLOCATED_QTY * SAR.ITEM_WEIGHT AS WEIGHT
161	,SAR.LOT
162	,TMP.MAX_PAL_HEIGHT
163	,TMP.MAX_PAL_WEIGHT
164	,SD.ITEM_CATEGORY3	--Хрупкость
165	,SD.ITEM_CATEGORY7 --Товарная категория
166	,SD.ITEM_CLASS	 ----Класс отгрузки
167	,TMP.STOP_SEQ
168	FROM SAR
169		JOIN SHIPMENT_DETAIL AS SD ON SAR.INTERNAL_SHIPMENT_LINE_NUM = SD.INTERNAL_SHIPMENT_LINE_NUM
170		JOIN #SAR_RESTRICTION TMP
171			ON SAR.INTERNAL_SHIP_ALLOC_NUM = TMP.INTERNAL_SHIP_ALLOC_NUM
172	WHERE SD.ITEM_CATEGORY1 != N'ZEMT'
173	 AND sar.ALLOCATED_QTY > 0
174
175	 --select * from @SAR drop table #SAR_RESTRICTION
176	 --SELECT SORTING_TYPE, FROM_LOC FROM @SAR 
177	 UPDATE r SET r.ROW_UP =  _r.rwnm, r.GROUP_ID = _r.rwnm
178	 FROM @SAR r
179	 JOIN
180	 (
181	 SELECT INTERNAL_SHIP_ALLOC_NUM
182	 , IIF (PICKING_ZONE = N'N', CAST(CEILING(ROW_NUMBER()OVER(ORDER BY PICKING_ZONE, SORTING_TYPE, FROM_LOC )) AS nvarchar(5)), 0) rwnm
183	 FROM @SAR WHERE PICKING_ZONE = N'N' --FROM_TEMPL_FIELD4 > 1
184
185	 ) _r ON _r.INTERNAL_SHIP_ALLOC_NUM = r.INTERNAL_SHIP_ALLOC_NUM
186	 
187	 /*
188	 IF EXISTS (select * from sys.types where name = N'SDNUM')
189		DROP TYPE SDNUM
190	 CREATE TYPE SDNUM AS TABLE (INTERNAL_SHIPMENT_LINE_NUM NUMERIC (9,0)
191	 , DIFF_ITEM_QTY_SHID NVARCHAR (25),GROUP_ID NUMERIC (9,0), PICKING_SEQ NVARCHAR (25),
192	  PICKING_ZONE NVARCHAR (25), FROM_LOC NVARCHAR (50), item NVARCHAR (50), VOLUME NUMERIC (19,5), INTERNAL_SHIP_ALLOC_NUM NUMERIC (9,0), WEIGHT NUMERIC (19,5),
193					FROM_TEMPL_FIELD4 NVARCHAR (25), ROW_UP NUMERIC (9,0), DIFF_LOT_QTY_PAL NUMERIC (9,0), LOT NVARCHAR (25)
194					, DIFF_ITEM_QTY_PAL NUMERIC (9,0), DIFF_ITEM_QTY_SHID_FP NUMERIC (9,0), SORTING_TYPE NVARCHAR (25), QUANTITY_UM NVARCHAR (25)
195					, FROM_SHIPMENT_ID NVARCHAR (25)  ,CUSTOMER NVARCHAR (25), SHIP_TO NVARCHAR (25), RNK NVARCHAR (25), INTERNAL_SHIPMENT_NUM NUMERIC (9,0)
196					,DIFF_ITEM_QTY_CUST NUMERIC (9,0), DIFF_ITEM_QTY_ADRS NUMERIC (9,0), NUM_OLD NVARCHAR (25), ORDER_CLASS NVARCHAR (25), STOP_SEQ NUMERIC(9,0)
197					,MAXHEIGHT NUMERIC(9,0), MAXWEIGHT NUMERIC(9,0), CTYPE NVARCHAR(25)
198					)
199	GO
200	*/
201	;
202	DECLARE @SD_NUM AS SDNUM
203	;
204	INSERT INTO @SD_NUM
205	SELECT	SD.INTERNAL_SHIPMENT_LINE_NUM,
206	DIFF_ITEM_QTY_SHID AS DIFF_ITEM_QTY_SHID	
207	,GROUP_ID, PICKING_SEQ, PICKING_ZONE, FROM_LOC, item, VOLUME, INTERNAL_SHIP_ALLOC_NUM, WEIGHT,
208	FROM_TEMPL_FIELD4, ROW_UP, NUM.DIFF_LOT_QTY_PAL, NUM.LOT, DIFF_ITEM_QTY_PAL, DIFF_ITEM_QTY_SHID_FP, SORTING_TYPE
209	, QUANTITY_UM,FROM_SHIPMENT_ID
210	,sd.CUSTOMER, sd.SHIP_TO
211	, RNK, 	INTERNAL_SHIPMENT_NUM	,DIFF_ITEM_QTY_CUST		,DIFF_ITEM_QTY_ADRS	, NULL AS NUM_OLD,ORDER_CLASS, STOP_SEQ , MAXHEIGHT, MAXWEIGHT, CTYPE
212	FROM dbo.SHIPMENT_DETAIL SD 
213	
214		JOIN
215		(
216			SELECT 	INTERNAL_SHIPMENT_LINE_NUM, DIFF_ITEM_QTY_PAL, DIFF_LOT_QTY_PAL, DIFF_ITEM_QTY_SHID, DIFF_ITEM_QTY_CUST, ITEM_STYLE, ITEM_COLOR, DIFF_ITEM_QTY_ADRS
217						,GROUP_ID, PICKING_SEQ, PICKING_ZONE, from_loc, VOLUME, INTERNAL_SHIP_ALLOC_NUM, WEIGHT, FROM_TEMPL_FIELD4, ROW_UP,LOT
218						,DIFF_ITEM_QTY_SHID_FP, sh.SORTING_TYPE,FROM_SHIPMENT_ID, RNK, ORDER_CLASS,STOP_SEQ, MAXHEIGHT, MAXWEIGHT, CTYPE
219						  
220			FROM
221				(SELECT _sar.INTERNAL_SHIPMENT_NUM, DENSE_RANK () OVER (ORDER BY RT) AS RNK, _sar.PICKING_SEQ,
222					_sar.ITEM,
223					 sar.LOT,
224					_sar.RT,
225					_sar.SHIPMENT_ID,
226					_sar.CUSTOMER,
227					_sar.INTERNAL_SHIPMENT_LINE_NUM,
228					DIFF_ITEM_QTY_PAL,
229					DIFF_LOT_QTY_PAL,
230					DIFF_ITEM_QTY_ADRS,
231					DIFF_ITEM_QTY_CUST,
232					DIFF_ITEM_QTY_SHID,
233					_sar.SHIP_TO,
234					SHIPPING_LOAD_NUM,
235					_sar.ITEM_STYLE,
236					_sar.ITEM_COLOR,
237					sar.GROUP_ID
238					,sar.DIFF_ITEM_QTY_SHID_FP
239					,sar.PICKING_ZONE
240					,sar.FROM_LOC
241					,sar.VOLUME  AS VOLUME
242					,sar.INTERNAL_SHIP_ALLOC_NUM
243					,sar.WEIGHT
244					, FROM_TEMPL_FIELD4
245					, sar.ROW_UP
246					, sar.SORTING_TYPE
247					, ISNULL(OSD.FROM_SHIPMENT_ID, _sar.SHIPMENT_ID) AS FROM_SHIPMENT_ID
248					, ISNULL(sar.ORDER_CLASS, N'S') AS ORDER_CLASS
249					, sar.STOP_SEQ
250					, sar.MAXHEIGHT
251					, sar.MAXWEIGHT
252					, sar.CTYPE
253					FROM #SAR_RESTRICTION _sar
254					JOIN @SAR sar ON sar.INTERNAL_SHIP_ALLOC_NUM = _sar.INTERNAL_SHIP_ALLOC_NUM 
255					OUTER APPLY (
256						SELECT TOP 1 FROM_SHIPMENT_ID
257						FROM KC_ORIG_SHIPMENT_DETAIL OSD
258						WHERE OSD.TO_SHIPMENT_ID = _sar.SHIPMENT_ID
259							AND _sar.ERP_ORDER_LINE_NUM = OSD.TO_ERP_ORDER_LINE_NUM
260						ORDER BY OSD.DATE_TIME_STAMP DESC
261					) AS OSD
262				) AS SH
263		) AS NUM
264			ON SD.INTERNAL_SHIPMENT_LINE_NUM = NUM.INTERNAL_SHIPMENT_LINE_NUM
265
266	WHERE SD.LAUNCH_NUM = @LAUNCH_NUM AND SD.ITEM_CATEGORY1 != N'ZEMT'
267	
268	--SELECT * FROM @SD_NUM
269	--select getdate(),*from @sd_num order by GROUP_ID
270	
271	
272	DECLARE @id INT = 1
273	DECLARE @maxvolume NUMERIC (19,0) ,
274		@maxweight NUMERIC(19,0),
275		@volume NUMERIC (19,0) ,
276		@weight NUMERIC(19,0),
277		@orderclass nvarchar(25)
278
279	WHILE EXISTS (SELECT 1 FROM @SAR WHERE isnull(ROW_UP,0) > 0)
280	BEGIN
281
282	--DECLARE @t NVARCHAR (25) = (SELECT  DOC + CONS_SHIPTO_CUSTOMER FROM @SD_NUM  WHERE GROUP_ID = @id)
283
284	SELECT @maxvolume = LENGTH * WIDTH * IIF(SAR.MAXHEIGHT = 0, CT.HEIGHT, SAR.MAXHEIGHT)
285		, @maxweight = IIF(SAR.MAXWEIGHT = 0, CT.MAXIMUM_WEIGHT, SAR.MAXWEIGHT)
286		, @volume = SAR.VOLUME
287		, @weight = SAR.WEIGHT
288		, @sortType = SAR.SORTING_TYPE
289		, @orderclass = ISNULL(SAR.ORDER_CLASS, N'S')
290	FROM CONTAINER_TYPE CT
291	JOIN @SAR SAR ON CT.CONTAINER_TYPE = SAR.CTYPE
292		AND SAR.GROUP_ID = @id
293	 ;
294	 
295	;
296	WITH MY_ID AS
297	(
298		SELECT ITEM, LOT, FROM_SHIPMENT_ID, CUSTOMER, SHIP_TO FROM @SD_NUM WHERE ROW_UP = @id
299	)
300
301	UPDATE sar SET ROW_UP = 0, GROUP_ID = @id
302	 FROM @SD_NUM sar
303	JOIN
304	(
305	 SELECT SUM(VOLUME) OVER ( ORDER BY PICKING_ZONE, PICKING_SEQ, VOLUME, INTERNAL_SHIP_ALLOC_NUM ROWS UNBOUNDED PRECEDING) AS SUM_VOLUME
306		  , SUM(WEIGHT) OVER (ORDER BY PICKING_ZONE,  PICKING_SEQ, VOLUME, INTERNAL_SHIP_ALLOC_NUM ROWS UNBOUNDED PRECEDING) AS SUM_WEIGHT
307		 , r.INTERNAL_SHIP_ALLOC_NUM
308		 FROM @SD_NUM r
309		 WHERE r.INTERNAL_SHIP_ALLOC_NUM IN
310		 (SELECT INTERNAL_SHIP_ALLOC_NUM FROM 
311		 (
312	SELECT 
313				 INTERNAL_SHIP_ALLOC_NUM, 
314			     DENSE_RANK () OVER ( ORDER BY RNK, INTERNAL_SHIPMENT_NUM, STOP_SEQ, IIF(SDN.ITEM = MY_ID.ITEM, '0', SDN.ITEM)		) rwitem,
315				 DENSE_RANK () OVER ( ORDER BY RNK, INTERNAL_SHIPMENT_NUM, STOP_SEQ, IIF(SDN.LOT = MY_ID.LOT, '0', SDN.LOT)		) rwlot,
316				 DENSE_RANK () OVER ( ORDER BY RNK, INTERNAL_SHIPMENT_NUM, STOP_SEQ, IIF(SDN.FROM_SHIPMENT_ID = MY_ID.FROM_SHIPMENT_ID, '0', SDN.FROM_SHIPMENT_ID)	) rwshipid,
317				 DENSE_RANK () OVER ( ORDER BY RNK, INTERNAL_SHIPMENT_NUM, STOP_SEQ, IIF(SDN.CUSTOMER = MY_ID.CUSTOMER, '0', SDN.CUSTOMER)		) rwcust,
318				 DENSE_RANK () OVER ( ORDER BY RNK, INTERNAL_SHIPMENT_NUM, STOP_SEQ, IIF(SDN.SHIP_TO = MY_ID.SHIP_TO, '0', SDN.SHIP_TO)	) rwshipto, ---1
319				 DIFF_LOT_QTY_PAL
320				FROM @SD_NUM SDN
321					JOIN MY_ID ON 1 = 1
322				WHERE 
323				(
324					--@t = (DOC+CONS_SHIPTO_CUSTOMER  ) AND 
325					( PICKING_ZONE = N'Y' AND ISNULL(ROW_UP, 10000) = 10000)
326					AND SORTING_TYPE = @sortType
327					AND VOLUME + @volume <= @maxvolume
328					AND WEIGHT + @weight <= @maxweight
329					AND QUANTITY_UM != N'GR'
330					AND ORDER_CLASS = @orderclass
331				)
332				OR ROW_UP = @id
333				
334		)_data 
335		  WHERE 
336		  rwitem <= IIF(ISNULL(NULLIF(DIFF_ITEM_QTY_PAL,0),10000)
337						    >= 
338				    ISNULL(NULLIF(DIFF_ITEM_QTY_SHID_FP,0),10000), ISNULL(NULLIF(DIFF_ITEM_QTY_SHID_FP,0),10000), ISNULL(NULLIF(DIFF_ITEM_QTY_PAL,0),10000) )
339		  AND rwlot <= ISNULL(NULLIF(DIFF_LOT_QTY_PAL,0),10000)
340		  AND rwcust <= ISNULL(NULLIF(DIFF_ITEM_QTY_CUST,0), rwcust)
341		  AND rwshipid <= ISNULL(NULLIF(DIFF_ITEM_QTY_SHID,0), rwshipid)
342		  AND rwshipto <= ISNULL(NULLIF(DIFF_ITEM_QTY_ADRS,0), rwshipid)
343		 )
344	)data		
345	ON data.INTERNAL_SHIP_ALLOC_NUM = sar.INTERNAL_SHIP_ALLOC_NUM
346	WHERE (data.SUM_VOLUME <= @maxvolume AND data.SUM_WEIGHT <= @maxweight) OR sar.PICKING_ZONE = N'N'
347	--DROP TABLE #SAR_RESTRICTION
348
349	--IF @@ROWCOUNT > 1
350	--UPDATE @SAR SET MTYPE = CTYPE WHERE GROUP_ID = @id
351
352	SET @id += 1
353	IF NOT EXISTS (SELECT 1 FROM @SD_NUM WHERE ROW_UP = @id)
354	BREAK
355	ELSE
356	CONTINUE
357	END
358	
359	SET @id += 1
360	
361	--DROP TABLE #SAR_RESTRICTION
362	DECLARE @currentSAR int
363
364	WHILE EXISTS (SELECT 1 FROM @SD_NUM WHERE GROUP_ID IS NULL)
365	BEGIN
366
367	SELECT @maxvolume = LENGTH * WIDTH * IIF(SAR.MAXHEIGHT = 0, CT.HEIGHT, SAR.MAXHEIGHT)
368		, @maxweight = IIF(SAR.MAXWEIGHT = 0, CT.MAXIMUM_WEIGHT, SAR.MAXWEIGHT)
369		, @volume = SAR.VOLUME
370		, @weight = SAR.WEIGHT
371		, @sortType = SAR.SORTING_TYPE
372		, @orderclass = ISNULL(SAR.ORDER_CLASS, N'S')
373		, @currentSAR = SAR.INTERNAL_SHIP_ALLOC_NUM
374	FROM CONTAINER_TYPE CT
375	JOIN
376	(	SELECT TOP 1 SORTING_TYPE, WEIGHT, VOLUME, MAXWEIGHT, MAXHEIGHT, CTYPE, ORDER_CLASS, INTERNAL_SHIP_ALLOC_NUM
377		FROM @SD_NUM WHERE GROUP_ID IS NULL
378	) 
379	 SAR ON CT.CONTAINER_TYPE = SAR.CTYPE
380
381	--SELECT @id ID, 'q' AS q, * FROM @SD_NUM
382	UPDATE sar SET ROW_UP = 0, GROUP_ID = @id
383	--select*
384	FROM @SD_NUM sar
385	JOIN
386	(
387	 SELECT SUM(VOLUME) OVER (ORDER BY PICKING_ZONE, PICKING_SEQ, VOLUME, INTERNAL_SHIP_ALLOC_NUM ROWS UNBOUNDED PRECEDING) AS SUM_VOLUME
388		  , SUM(WEIGHT) OVER (ORDER BY PICKING_ZONE, PICKING_SEQ, VOLUME, INTERNAL_SHIP_ALLOC_NUM ROWS UNBOUNDED PRECEDING) AS SUM_WEIGHT
389		 , r.INTERNAL_SHIP_ALLOC_NUM
390		 FROM @SD_NUM r
391		 WHERE r.INTERNAL_SHIP_ALLOC_NUM IN
392		 (SELECT INTERNAL_SHIP_ALLOC_NUM FROM 
393		 (
394	SELECT 
395				 INTERNAL_SHIP_ALLOC_NUM, 
396			     DENSE_RANK () OVER ( ORDER BY IIF(INTERNAL_SHIP_ALLOC_NUM = @currentSAR, 0, 1), RNK, INTERNAL_SHIPMENT_NUM,STOP_SEQ, SDN.ITEM	) rwitem,
397				 DENSE_RANK () OVER ( ORDER BY IIF(INTERNAL_SHIP_ALLOC_NUM = @currentSAR, 0, 1), RNK, INTERNAL_SHIPMENT_NUM,STOP_SEQ, SDN.LOT   ) rwlot,
398				 DENSE_RANK () OVER ( ORDER BY IIF(INTERNAL_SHIP_ALLOC_NUM = @currentSAR, 0, 1), RNK, INTERNAL_SHIPMENT_NUM,STOP_SEQ, SDN.FROM_SHIPMENT_ID) rwshipid,
399				 DENSE_RANK () OVER ( ORDER BY IIF(INTERNAL_SHIP_ALLOC_NUM = @currentSAR, 0, 1), RNK, INTERNAL_SHIPMENT_NUM,STOP_SEQ, SDN.CUSTOMER) rwcust,
400				 DENSE_RANK () OVER ( ORDER BY IIF(INTERNAL_SHIP_ALLOC_NUM = @currentSAR, 0, 1), RNK, INTERNAL_SHIPMENT_NUM,STOP_SEQ, SDN.SHIP_TO) rwshipto, ---1
401				 DIFF_LOT_QTY_PAL
402				FROM @SD_NUM SDN
403				WHERE 
404				(
405					--@t = (DOC+CONS_SHIPTO_CUSTOMER  ) AND 
406					PICKING_ZONE = N'Y' 
407					AND SORTING_TYPE = @sortType
408					AND VOLUME + @volume <= @maxvolume
409					AND WEIGHT + @weight <= @maxweight
410					AND QUANTITY_UM != N'GR'
411					AND GROUP_ID IS NULL
412					AND ORDER_CLASS = @orderclass
413					--AND INTERNAL_SHIP_ALLOC_NUM != @currentSAR
414				) OR INTERNAL_SHIP_ALLOC_NUM = @currentSAR
415		)_data 
416		  WHERE 
417		  rwitem <= IIF(ISNULL(NULLIF(DIFF_ITEM_QTY_PAL,0),10000)
418						    >= 
419				    ISNULL(NULLIF(DIFF_ITEM_QTY_SHID_FP,0),10000), ISNULL(NULLIF(DIFF_ITEM_QTY_SHID_FP,0),10000), ISNULL(NULLIF(DIFF_ITEM_QTY_PAL,0),10000) )
420		  AND rwlot <= ISNULL(NULLIF(DIFF_LOT_QTY_PAL,0),10000)
421		  AND rwcust <= ISNULL(NULLIF(DIFF_ITEM_QTY_CUST,0), rwcust)
422		  AND rwshipid <= ISNULL(NULLIF(DIFF_ITEM_QTY_SHID,0), rwshipid)
423		  AND rwshipto <= ISNULL(NULLIF(DIFF_ITEM_QTY_ADRS,0), rwshipid)
424		 )
425	)data		
426	ON data.INTERNAL_SHIP_ALLOC_NUM = sar.INTERNAL_SHIP_ALLOC_NUM
427	WHERE (data.SUM_VOLUME <= @maxvolume AND data.SUM_WEIGHT <= @maxweight) OR sar.INTERNAL_SHIP_ALLOC_NUM = @currentSAR
428	
429	IF @@ROWCOUNT = 0 BREAK
430	ELSE SET @id += 1 CONTINUE
431
432	END
433
434	UPDATE @SD_NUM SET GROUP_ID = @id +1
435	WHERE  QUANTITY_UM = N'GR'
436
437	; 
438	--SELECT getdate(),* FROM @SD_NUM
439	
440	--DROP TABLE #SAR_RESTRICTION 
441	--UPDATE sar
442	--SET SAR.PACKING_CLASS = SAR_TMP.PCL_NEW	--PackingClass
443	--	,SAR.ITEM_SIZE = SAR_TMP.CIIPL		--Консолидация по товару на паллете
444	--	,SAR.PICKING_SEQ = 	SAR_TMP.PICKING_SEQ_NEW	--IIF(SAR.FROM_WORK_ZONE IN (SELECT ZONE FROM ZONE WHERE USER_DEF1 = N'Y'), SAR.PICKING_SEQ, 0)	--Хрупкость
445	--FROM dbo.SHIPMENT_ALLOC_REQUEST SAR
446	--JOIN (
447	--	SELECT SAR_TMP.CIIPL, SAR_TMP.SORTING_TYPE, SAR_TMP.INTERNAL_SHIP_ALLOC_NUM
448	--		, ROW_NUMBER() OVER (ORDER BY SDN.NUM_OLD, SAR_TMP.PCL, IIF(SAR_TMP.PICKING_ZONE = N'Y', 1, 0), SAR_TMP.PICKING_SEQ) AS PICKING_SEQ_NEW
449	--		, CONCAT(IIF(SAR_TMP.PICKING_ZONE = N'Y', SAR_TMP.CTYPE, SAR_TMP.MTYPE)
450	--			, N'.' + CAST(DENSE_RANK() OVER (ORDER BY SAR_TMP.PCL, SDN.NUM_OLD, SDN.CONS_SHIPTO_CUSTOMER) AS nvarchar(25))) AS PCL_NEW
451	--	FROM @SAR SAR_TMP
452	--	JOIN #SD_NUM SDN
453	--		ON SAR_TMP.INTERNAL_SHIP_ALLOC_NUM = SDN.INTERNAL_SHIP_ALLOC_NUM
454	--) SAR_TMP
455	--	ON SAR_TMP.INTERNAL_SHIP_ALLOC_NUM = SAR.INTERNAL_SHIP_ALLOC_NUM
456	--;
457
458	
459
460
461BEGIN
462WITH FULL_RESERV AS
463		(
464			SELECT FROM_LOC
465			FROM dbo.SHIPMENT_ALLOC_REQUEST
466			WHERE LAUNCH_NUM = @LAUNCH_NUM AND QUANTITY_UM = N'GR'
467			GROUP BY FROM_LOC
468			HAVING SUM(ALLOCATED_QTY) = (
469				SELECT SUM(ON_HAND_QTY + IN_TRANSIT_QTY) QTY
470				FROM dbo.LOCATION_INVENTORY
471				WHERE FROM_LOC = LOCATION
472					AND EXISTS (SELECT * FROM KC_LPN WHERE LPN = LOGISTICS_UNIT AND FULL_PALLET = N'Y')
473			)
474		)
475		UPDATE SAR
476		SET SAR.PACKING_CLASS = SAR_TMP.PCL_NEW			--PackingClass
477			,SAR.ITEM_SIZE = SAR_TMP.CIIPL				--Консолидация по товару на паллете
478			,SAR.PICKING_SEQ = 	SAR_TMP.PICKING_SEQ_NEW
479		--SELECT PICKING_SEQ_NEW, PCL_NEW, CIIPL, *
480		FROM dbo.SHIPMENT_ALLOC_REQUEST SAR
481		JOIN (
482			SELECT SAR.CIIPL, SAR.SORTING_TYPE, SAR.INTERNAL_SHIP_ALLOC_NUM
483			, ROW_NUMBER() OVER (ORDER BY SDN.GROUP_ID, SAR.PCL, IIF(SAR.PICKING_ZONE = N'Y', 1, 0),PICKING_SEQ) AS PICKING_SEQ_NEW
484				, CONCAT(IIF(SAR.PICKING_ZONE = N'Y' AND SAR.FROM_LOC NOT LIKE N'WW%', SAR.CTYPE, SAR.MTYPE)
485					, N'.' + CAST(DENSE_RANK() OVER (
486						ORDER BY IIF(SDN.FROM_LOC IN (SELECT FROM_LOC FROM FULL_RESERV), SDN.FROM_LOC, NULL)
487							, SAR.PCL, SDN.GROUP_ID) AS nvarchar(25))) AS PCL_NEW
488			FROM @SAR SAR
489			JOIN (SELECT INTERNAL_SHIP_ALLOC_NUM, GROUP_ID, FROM_LOC FROM @SD_NUM) SDN
490				ON SAR.INTERNAL_SHIP_ALLOC_NUM = SDN.INTERNAL_SHIP_ALLOC_NUM
491		) SAR_TMP
492			ON SAR_TMP.INTERNAL_SHIP_ALLOC_NUM = SAR.INTERNAL_SHIP_ALLOC_NUM
493		WHERE SAR_TMP.SORTING_TYPE = @sortType
494
495	END
496	;
497	--SELECT * FROM #SD_NUM order by group_id, picking_seq
498	----drop table #sar_restriction
499	--SELECT * FROM @SAR
500	;
501	
502	--	WW. Весовой товар - преобразование ЕИ в CS
503	UPDATE SAR
504	SET SAR.CONVERTED_ALLOC_QTY = CEILING(SAR.ALLOCATED_QTY / (IT.AVGWEIGHT * 1000))
505	  , SAR.CONTAINER_WEIGHT = SAR.ALLOCATED_QTY / (CEILING(SAR.ALLOCATED_QTY / (IT.AVGWEIGHT)))
506	--,	SAR.CONVERTED_QTY_UM = N'CS'
507	--,	SAR.TREAT_AS_LOOSE = N'N'
508	FROM dbo.SHIPMENT_ALLOC_REQUEST SAR
509	CROSS APPLY (
510		SELECT IT1.COST AS AVGWEIGHT
511		FROM dbo.ITEM IT1
512		WHERE SAR.ITEM = IT1.ITEM
513			AND SAR.COMPANY = IT1.COMPANY
514	) AS IT
515	WHERE SAR.LAUNCH_NUM = @LAUNCH_NUM
516		AND SAR.ALLOCATED_QTY > 0.0
517		AND IT.AVGWEIGHT > 0.0
518	;
519
520	--	PL. Для зоны хранения невесового товара - увеличение ВГХ паллеты до размеров контейнера
521	/*WITH SAR_FROM_LU AS
522	(
523		SELECT SAR1.INTERNAL_SHIP_ALLOC_NUM, SAR1.LOGISTICS_UNIT
524		FROM dbo.SHIPMENT_ALLOC_REQUEST SAR1
525		WHERE SAR1.LAUNCH_NUM = @LAUNCH_NUM
526			AND SAR1.ALLOCATED_QTY > 0.0
527	)
528	UPDATE SAR
529	SET SAR.CONTAINER_LENGTH = CT.LENGTH
530	  , SAR.CONTAINER_WIDTH = CT.WIDTH
531	  , SAR.CONTAINER_HEIGHT = (CT.HEIGHT / SAR_COUNT.NUM)
532	  , SAR.CONVERTED_ALLOC_QTY = 1.0
533	FROM dbo.SHIPMENT_ALLOC_REQUEST SAR
534	JOIN SHIPMENT_DETAIL AS SD ON SAR.INTERNAL_SHIPMENT_LINE_NUM = SD.INTERNAL_SHIPMENT_LINE_NUM
535	JOIN (SELECT PACKING_CLASS, CONTAINER_GROUP FROM dbo.PACKING_CLASS) PC
536		ON SAR.PACKING_CLASS = PC.PACKING_CLASS
537	JOIN (SELECT CONTAINER_TYPE, LENGTH, WIDTH, HEIGHT FROM dbo.CONTAINER_TYPE) CT
538		ON PC.CONTAINER_GROUP = CT.CONTAINER_TYPE
539	CROSS APPLY
540	(
541		SELECT COUNT(*) AS NUM
542		FROM SAR_FROM_LU
543		WHERE SAR.LOGISTICS_UNIT = SAR_FROM_LU.LOGISTICS_UNIT
544	) AS SAR_COUNT
545	CROSS APPLY (
546		SELECT IT1.COST AS AVGWEIGHT
547		FROM dbo.ITEM IT1
548		WHERE SAR.ITEM = IT1.ITEM
549			AND SAR.COMPANY = IT1.COMPANY
550	) AS IT
551	WHERE SAR.LAUNCH_NUM = @LAUNCH_NUM
552		AND SAR.FROM_WORK_ZONE NOT IN (SELECT ZONE FROM ZONE WHERE USER_DEF1 = N'Y')
553		AND (@topUp != N'1' OR SAR.ITEM IN (SELECT ITEM FROM dbo.ITEM WHERE ITEM_CATEGORY4 = N'N'))
554		AND SD.ITEM_CATEGORY1 != N'ZEMT'
555		AND IT.AVGWEIGHT = 0.0
556		AND SAR.USER_DEF1 NOT LIKE N'TopUp%'
557	;
558	*/
559
560	--	CS. Для коробочного отбора - округление высоты короба для получения целого количества слоев
561	--WITH K AS
562	--(
563	--	SELECT 10 AS TOLERANCE
564	--)
565	--UPDATE SAR
566	--SET SAR.CONTAINER_HEIGHT = CT.VOLUME
567	--	/ (ISNULL(
568	--		--	по количеству укладок
569	--		IIF(LAYOUTT.ISFULL = 1, LAYOUTT.QTY, NULL)
570	--		--	по целому числу упаковок в 
571	--		, FLOOR(CT.HEIGHT / SAR.ITEM_HEIGHT)
572	--			* IIF(LAYOUTT.ISFULL = 0
573	--				, LAYOUTT.LAYER_QTY
574	--				, FLOOR(CT.LENGTH * CT.WIDTH / (SAR.ITEM_LENGTH * SAR.ITEM_WIDTH)))
575	--	) * SAR.ITEM_WIDTH * SAR.ITEM_LENGTH)
576	--FROM dbo.SHIPMENT_ALLOC_REQUEST SAR
577	--JOIN SHIPMENT_DETAIL AS SD ON SAR.INTERNAL_SHIPMENT_LINE_NUM = SD.INTERNAL_SHIPMENT_LINE_NUM
578	--JOIN (SELECT PACKING_CLASS, CONTAINER_GROUP FROM dbo.PACKING_CLASS) PC
579	--	ON SAR.PACKING_CLASS = PC.PACKING_CLASS
580	--JOIN (SELECT CONTAINER_TYPE, LENGTH, WIDTH, HEIGHT
581	--	--	Объем типа контейнера за вычетом допуска (1)
582	--	, LENGTH * WIDTH * HEIGHT - 1 AS VOLUME
583	--	FROM dbo.CONTAINER_TYPE
584	--) AS CT
585	--	ON PC.CONTAINER_GROUP = CT.CONTAINER_TYPE
586	--OUTER APPLY
587	--(
588	--	SELECT TOP 1 GCD.IDENTIFIER
589	--		, GCD.USER1VALUE AS PALLET_TYPE
590	--		, GCD.USER3VALUE AS QTY
591	--		, IIF(GCD.USER5VALUE >= GCD.USER6VALUE, GCD.USER5VALUE, GCD.USER6VALUE) AS LAYER_QTY
592	--		, IIF(CT.HEIGHT >= CAST(GCD.USER4VALUE AS numeric(19,5)), 1, 0) AS ISFULL
593	--	FROM dbo.GENERIC_CONFIG_DETAIL GCD
594	--	JOIN (
595	--		SELECT USER1VALUE AS ITEM, USER2VALUE AS LAYOUT
596	--		FROM dbo.GENERIC_CONFIG_DETAIL
597	--		WHERE RECORD_TYPE = N'KC_ITEM_LAYOUT'
598	--			AND SAR.ITEM = USER1VALUE
599	--	) AS ILAYOUT
600	--		ON ILAYOUT.LAYOUT = GCD.IDENTIFIER
601	--	WHERE RECORD_TYPE = N'KC_PALLET_LAYOUT'
602	--		AND (CT.CONTAINER_TYPE LIKE GCD.USER1VALUE + N'%')
603	--	ORDER BY IIF(CT.HEIGHT >= CAST(GCD.USER4VALUE AS numeric(19,5)), 3, 4) DESC
604	--) AS LAYOUTT
605	--CROSS APPLY (
606	--	SELECT IT1.COST AS AVGWEIGHT
607	--	FROM dbo.ITEM IT1
608	--	WHERE SAR.ITEM = IT1.ITEM
609	--		AND SAR.COMPANY = IT1.COMPANY
610	--) AS IT
611	--WHERE SAR.LAUNCH_NUM = @LAUNCH_NUM
612	--	AND SAR.FROM_WORK_ZONE IN (SELECT ZONE FROM ZONE WHERE USER_DEF1 = N'Y')
613	--	AND (@topUp != N'1' OR SAR.ITEM IN (SELECT ITEM FROM dbo.ITEM WHERE ITEM_CATEGORY4 = N'N'))
614	--	--AND (SAR.ALLOCATED_QTY >= ISNULL(LAYOUTT.QTY, FLOOR(CT.HEIGHT / SAR.ITEM_HEIGHT) * FLOOR(CT.LENGTH * CT.WIDTH / (SAR.ITEM_LENGTH * SAR.ITEM_WIDTH))))
615	--	AND SD.ITEM_CATEGORY1 != N'ZEMT'
616	--	AND IT.AVGWEIGHT = 0.0
617	--	AND SAR.INTERNAL_SHIP_ALLOC_NUM NOT IN (
618	--		SELECT RW.INTERNAL_SHIP_ALLOC_NUM FROM (
619	--			SELECT ROW_NUMBER() OVER (PARTITION BY _sd.GROUP_ID ORDER BY _sd.INTERNAL_SHIP_ALLOC_NUM) RWNM
620	--				, _sd.INTERNAL_SHIP_ALLOC_NUM
621	--			FROM @SD_NUM _sd
622	--			WHERE _sd.GROUP_ID IS NOT NULL
623	--		) RW
624	--		GROUP BY RW.INTERNAL_SHIP_ALLOC_NUM
625	--		HAVING MAX(RWNM) = 1
626	--	)
627	--;
628
629	DROP TABLE #SAR_RESTRICTION
630
631END
632
633
634	  --DROP TABLE #SAR_RESTRICTION DROP TABLE #SD_NUM