rWrTeA9m

1USE [ILS]
2GO
3/****** Object:  StoredProcedure [dbo].[KC_PalletBuilding]    Script Date: 09/11/18 14:56:47 ******/
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     DECLARE @sar_insert NUMERIC (9,0)                          
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
430       IF @@ROWCOUNT = 1 AND 
431	                   (
432					    (SELECT WEIGHT FROM @SD_NUM WHERE INTERNAL_SHIP_ALLOC_NUM = @currentSAR) > @maxweight 
433					   OR
434					    (SELECT VOLUME FROM @SD_NUM WHERE INTERNAL_SHIP_ALLOC_NUM = @currentSAR) > @maxvolume 
435					   )
436	BEGIN
437	INSERT INTO 
438	SHIPMENT_ALLOC_REQUEST
439	SELECT [STATUS_FLOW_NAME]
440           ,SAR.[FROM_WHS]
441           ,SAR.[INTERNAL_SHIPMENT_LINE_NUM]
442           ,SAR.[INTERNAL_SHIPMENT_NUM]
443           ,SAR.[SHIPMENT_ID]
444           ,SAR.[ORDER_TYPE]
445           ,SAR.[ERP_ORDER]
446           ,SAR.[ERP_ORDER_LINE_NUM]
447           ,SAR.[INTERNAL_ORDER_NUM]
448           ,SAR.[PICK_LIST_ID]
449           ,SAR.[PRIORITY]
450           ,SAR.[ITEM]
451           ,SAR.[COMPANY]
452           ,SAR.[ITEM_DESC]
453           , CASE WHEN (sdnum.volume - @maxvolume) / (sar.ITEM_HEIGHT * sar.ITEM_LENGTH * sar.ITEM_WIDTH) 
454					 > (sdnum.weight - @maxweight) / ( sar.item_weight )
455		   THEN (sdnum.volume - @maxvolume) / (sar.ITEM_HEIGHT * sar.ITEM_LENGTH * sar.ITEM_WIDTH) 
456		   ELSE (sdnum.weight - @maxweight) / ( sar.item_weight ) 
457		   END 'ALLOCATED_QTY'
458           ,SAR.[QUANTITY_UM]
459           ,SAR.[LOT]
460           ,SAR.[CUSTOMER]
461           ,SAR.[SHIP_TO]
462           ,SAR.[MARK_FOR]
463           ,SAR.[CARRIER]
464           ,SAR.[CARRIER_SERVICE]
465           ,SAR.[CARRIER_TYPE]
466           ,SAR.[ORDER_DATE]
467           ,SAR.[REQUESTED_DELIVERY_DATE]
468           ,SAR.[REQUESTED_DELIVERY_TYPE]
469           ,SAR.[PLANNED_SHIP_DATE]
470           ,SAR.[ALLOCATION_ZONE]
471           ,SAR.[ITEM_WEIGHT]
472           ,SAR.[WEIGHT_UM]
473           ,SAR.[ITEM_LENGTH]
474           ,SAR.[ITEM_WIDTH]
475           ,SAR.[ITEM_HEIGHT]
476           ,SAR.[ITEM_DIMENSION_UM]
477           ,SAR.[VOLUME_UM]
478           ,SAR.[ITEM_DIVISION]
479           ,SAR.[ITEM_DEPARTMENT]
480           ,SAR.[ITEM_LIST_PRICE]
481           ,SAR.[ITEM_NET_PRICE]
482           ,SAR.[VALUE]
483           ,SAR.[ITEM_SIZE]
484           ,SAR.[ITEM_COLOR]
485           ,SAR.[ITEM_STYLE]
486           ,SAR.[LAUNCH_NUM]
487           ,SAR.[CUSTOMER_PO]
488           ,SAR.[INVOICE]
489           ,SAR.[PACKING_CATEGORY]
490           ,SAR.[HAZARDOUS_CODE]
491           ,SAR.[NMFC_CODE]
492           ,SAR.[USER_DEF1]
493           ,SAR.[USER_DEF2]
494           ,SAR.[USER_DEF3]
495           ,SAR.[USER_DEF4]
496           ,SAR.[USER_DEF5]
497           ,SAR.[USER_DEF6]
498           ,SAR.[USER_DEF7]
499           ,SAR.[USER_DEF8]
500           ,SAR.[USER_STAMP]
501           ,SAR.[PROCESS_STAMP]
502           ,SAR.[DATE_TIME_STAMP]
503           ,SAR.[TO_WHS]
504           ,SAR.[INTERFACED_DATE]
505           ,SAR.[PICKING_SEQ]
506           ,SAR.[PUTAWAY_SEQ]
507           ,SAR.[CONVERTED_ALLOC_QTY]
508           ,SAR.[CONVERTED_QTY_UM]
509           ,SAR.[INVENTORY_TRACKING]
510           ,SAR.[FROM_LOC]
511           ,SAR.[TO_LOC]
512           ,SAR.[FROM_WORK_ZONE]
513           ,SAR.[TO_WORK_ZONE]
514           ,SAR.[FROM_TEMPL_FIELD1]
515           ,SAR.[FROM_TEMPL_FIELD2]
516           ,SAR.[FROM_TEMPL_FIELD3]
517           ,SAR.[FROM_TEMPL_FIELD4]
518           ,SAR.[FROM_TEMPL_FIELD5]
519           ,SAR.[TO_TEMPL_FIELD1]
520           ,SAR.[TO_TEMPL_FIELD2]
521           ,SAR.[TO_TEMPL_FIELD3]
522           ,SAR.[TO_TEMPL_FIELD4]
523           ,SAR.[TO_TEMPL_FIELD5]
524           ,SAR.[WORK_CREATED]
525           ,SAR.[PACKING_CLASS]
526           ,SAR.[REQUESTED_QTY]
527           ,SAR.[CONTAINER_WEIGHT]
528           ,SAR.[CONTAINER_WIDTH]
529           ,SAR.[CONTAINER_LENGTH]
530           ,SAR.[CONTAINER_HEIGHT]
531           ,SAR.[CONTAINER_DIMENSION_UM]
532           ,SAR.[CONTAINER_WEIGHT_UM]
533           ,SAR.[CONT_CREATION_INNERPACK_QTY]
534           ,SAR.[TREAT_AS_LOOSE]
535           ,SAR.[LOGISTICS_UNIT]
536           ,SAR.[PARENT_LOGISTICS_UNIT]
537           ,SAR.[FROM_LOC_INV_ATTRIBUTES_ID]
538           ,SAR.[TO_LOC_INV_ATTRIBUTES_ID]
539           ,SAR.[PALLET_UM_CONVERSION_PARENT_UM]
540           ,SAR.[PALLET_UM_CONVERSION_IDENTIFIER] 
541		   FROM [dbo].[SHIPMENT_ALLOC_REQUEST] SAR
542		   JOIN @SD_NUM sdnum ON sdnum.INTERNAL_SHIP_ALLOC_NUM = SAR.INTERNAL_SHIP_ALLOC_NUM
543		   WHERE SAR.INTERNAL_SHIP_ALLOC_NUM = @currentSAR
544		   SET @currentSAR = (SELECT SCOPE_IDENTITY ())
545	UPDATE SAR
546	SET SAR.ALLOCATED_QTY = SAR.ALLOCATED_QTY - SAR1.ALLOCATED_QTY
547	FROM dbo.SHIPMENT_ALLOC_REQUEST SAR
548	JOIN dbo.SHIPMENT_ALLOC_REQUEST SAR1  ON SAR1.item = sar.item
549	WHERE sar1.INTERNAL_SHIP_ALLOC_NUM = @sar_insert 
550	  AND sar.INTERNAL_SHIP_ALLOC_NUM = @currentSAR
551
552	INSERT INTO @SD_NUM
553	SELECT INTERNAL_SHIPMENT_LINE_NUM 
554     , DIFF_ITEM_QTY_SHID ,GROUP_ID , PICKING_SEQ ,
555      PICKING_ZONE , FROM_LOC , item , VOLUME , @sar_insert, WEIGHT ,
556                    FROM_TEMPL_FIELD4 , ROW_UP , DIFF_LOT_QTY_PAL , LOT 
557                    , DIFF_ITEM_QTY_PAL , DIFF_ITEM_QTY_SHID_FP , SORTING_TYPE , QUANTITY_UM
558                    , FROM_SHIPMENT_ID   ,CUSTOMER, SHIP_TO , RNK , INTERNAL_SHIPMENT_NUM 
559                    ,DIFF_ITEM_QTY_CUST , DIFF_ITEM_QTY_ADRS , NUM_OLD , ORDER_CLASS , STOP_SEQ
560                    ,MAXHEIGHT , MAXWEIGHT , CTYPE 
561	 FROM @SD_NUM WHERE INTERNAL_SHIP_ALLOC_NUM = @currentSAR
562	END
563
564    IF NOT EXISTS (SELECT 1 FROM @SD_NUM WHERE GROUP_ID IS NULL) BREAK
565    ELSE SET @id += 1 CONTINUE
566 
567    END
568 
569    UPDATE @SD_NUM SET GROUP_ID = @id +1
570    WHERE  QUANTITY_UM = N'GR'
571 
572    ;
573    --SELECT getdate(),* FROM @SD_NUM
574   
575    --DROP TABLE #SAR_RESTRICTION
576    --UPDATE sar
577    --SET SAR.PACKING_CLASS = SAR_TMP.PCL_NEW   --PackingClass
578    --  ,SAR.ITEM_SIZE = SAR_TMP.CIIPL      --Консолидация по товару на паллете
579    --  ,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) --Хрупкость
580    --FROM dbo.SHIPMENT_ALLOC_REQUEST SAR
581    --JOIN (
582    --  SELECT SAR_TMP.CIIPL, SAR_TMP.SORTING_TYPE, SAR_TMP.INTERNAL_SHIP_ALLOC_NUM
583    --      , 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
584    --      , CONCAT(IIF(SAR_TMP.PICKING_ZONE = N'Y', SAR_TMP.CTYPE, SAR_TMP.MTYPE)
585    --          , N'.' + CAST(DENSE_RANK() OVER (ORDER BY SAR_TMP.PCL, SDN.NUM_OLD, SDN.CONS_SHIPTO_CUSTOMER) AS nvarchar(25))) AS PCL_NEW
586    --  FROM @SAR SAR_TMP
587    --  JOIN #SD_NUM SDN
588    --      ON SAR_TMP.INTERNAL_SHIP_ALLOC_NUM = SDN.INTERNAL_SHIP_ALLOC_NUM
589    --) SAR_TMP
590    --  ON SAR_TMP.INTERNAL_SHIP_ALLOC_NUM = SAR.INTERNAL_SHIP_ALLOC_NUM
591    --;
592 
593   
594 
595 
596BEGIN
597WITH FULL_RESERV AS
598        (
599            SELECT FROM_LOC
600            FROM dbo.SHIPMENT_ALLOC_REQUEST
601            WHERE LAUNCH_NUM = @LAUNCH_NUM AND QUANTITY_UM = N'GR'
602            GROUP BY FROM_LOC
603            HAVING SUM(ALLOCATED_QTY) = (
604                SELECT SUM(ON_HAND_QTY + IN_TRANSIT_QTY) QTY
605                FROM dbo.LOCATION_INVENTORY
606                WHERE FROM_LOC = LOCATION
607                    AND EXISTS (SELECT * FROM KC_LPN WHERE LPN = LOGISTICS_UNIT AND FULL_PALLET = N'Y')
608            )
609        )
610        UPDATE SAR
611        SET SAR.PACKING_CLASS = SAR_TMP.PCL_NEW         --PackingClass
612            ,SAR.ITEM_SIZE = SAR_TMP.CIIPL              --Консолидация по товару на паллете
613            ,SAR.PICKING_SEQ =  SAR_TMP.PICKING_SEQ_NEW
614        --SELECT PICKING_SEQ_NEW, PCL_NEW, CIIPL, *
615        FROM dbo.SHIPMENT_ALLOC_REQUEST SAR
616        JOIN (
617            SELECT SAR.CIIPL, SAR.SORTING_TYPE, SAR.INTERNAL_SHIP_ALLOC_NUM
618            , ROW_NUMBER() OVER (ORDER BY SDN.GROUP_ID, SAR.PCL, IIF(SAR.PICKING_ZONE = N'Y', 1, 0),PICKING_SEQ) AS PICKING_SEQ_NEW
619                , CONCAT(IIF(SAR.PICKING_ZONE = N'Y' AND SAR.FROM_LOC NOT LIKE N'WW%', SAR.CTYPE, SAR.MTYPE)
620                    , N'.' + CAST(DENSE_RANK() OVER (
621                        ORDER BY IIF(SDN.FROM_LOC IN (SELECT FROM_LOC FROM FULL_RESERV), SDN.FROM_LOC, NULL)
622                            , SAR.PCL, SDN.GROUP_ID) AS nvarchar(25))) AS PCL_NEW
623            FROM @SAR SAR
624            JOIN (SELECT INTERNAL_SHIP_ALLOC_NUM, GROUP_ID, FROM_LOC FROM @SD_NUM) SDN
625                ON SAR.INTERNAL_SHIP_ALLOC_NUM = SDN.INTERNAL_SHIP_ALLOC_NUM
626        ) SAR_TMP
627            ON SAR_TMP.INTERNAL_SHIP_ALLOC_NUM = SAR.INTERNAL_SHIP_ALLOC_NUM
628        WHERE SAR_TMP.SORTING_TYPE = @sortType
629 
630    END
631    ;
632    --SELECT * FROM #SD_NUM order by group_id, picking_seq
633    ----drop table #sar_restriction
634    --SELECT * FROM @SAR
635    ;
636   
637    --  WW. Весовой товар - преобразование ЕИ в CS
638    UPDATE SAR
639    SET SAR.CONVERTED_ALLOC_QTY = CEILING(SAR.ALLOCATED_QTY / (IT.AVGWEIGHT * 1000))
640      , SAR.CONTAINER_WEIGHT = SAR.ALLOCATED_QTY / (CEILING(SAR.ALLOCATED_QTY / (IT.AVGWEIGHT)))
641    --, SAR.CONVERTED_QTY_UM = N'CS'
642    --, SAR.TREAT_AS_LOOSE = N'N'
643    FROM dbo.SHIPMENT_ALLOC_REQUEST SAR
644    CROSS APPLY (
645        SELECT IT1.COST AS AVGWEIGHT
646        FROM dbo.ITEM IT1
647        WHERE SAR.ITEM = IT1.ITEM
648            AND SAR.COMPANY = IT1.COMPANY
649    ) AS IT
650    WHERE SAR.LAUNCH_NUM = @LAUNCH_NUM
651        AND SAR.ALLOCATED_QTY > 0.0
652        AND IT.AVGWEIGHT > 0.0
653    ;
654 
655    --  PL. Для зоны хранения невесового товара - увеличение ВГХ паллеты до размеров контейнера
656    /*WITH SAR_FROM_LU AS
657    (
658        SELECT SAR1.INTERNAL_SHIP_ALLOC_NUM, SAR1.LOGISTICS_UNIT
659        FROM dbo.SHIPMENT_ALLOC_REQUEST SAR1
660        WHERE SAR1.LAUNCH_NUM = @LAUNCH_NUM
661            AND SAR1.ALLOCATED_QTY > 0.0
662    )
663    UPDATE SAR
664    SET SAR.CONTAINER_LENGTH = CT.LENGTH
665      , SAR.CONTAINER_WIDTH = CT.WIDTH
666      , SAR.CONTAINER_HEIGHT = (CT.HEIGHT / SAR_COUNT.NUM)
667      , SAR.CONVERTED_ALLOC_QTY = 1.0
668    FROM dbo.SHIPMENT_ALLOC_REQUEST SAR
669    JOIN SHIPMENT_DETAIL AS SD ON SAR.INTERNAL_SHIPMENT_LINE_NUM = SD.INTERNAL_SHIPMENT_LINE_NUM
670    JOIN (SELECT PACKING_CLASS, CONTAINER_GROUP FROM dbo.PACKING_CLASS) PC
671        ON SAR.PACKING_CLASS = PC.PACKING_CLASS
672    JOIN (SELECT CONTAINER_TYPE, LENGTH, WIDTH, HEIGHT FROM dbo.CONTAINER_TYPE) CT
673        ON PC.CONTAINER_GROUP = CT.CONTAINER_TYPE
674    CROSS APPLY
675    (
676        SELECT COUNT(*) AS NUM
677        FROM SAR_FROM_LU
678        WHERE SAR.LOGISTICS_UNIT = SAR_FROM_LU.LOGISTICS_UNIT
679    ) AS SAR_COUNT
680    CROSS APPLY (
681        SELECT IT1.COST AS AVGWEIGHT
682        FROM dbo.ITEM IT1
683        WHERE SAR.ITEM = IT1.ITEM
684            AND SAR.COMPANY = IT1.COMPANY
685    ) AS IT
686    WHERE SAR.LAUNCH_NUM = @LAUNCH_NUM
687        AND SAR.FROM_WORK_ZONE NOT IN (SELECT ZONE FROM ZONE WHERE USER_DEF1 = N'Y')
688        AND (@topUp != N'1' OR SAR.ITEM IN (SELECT ITEM FROM dbo.ITEM WHERE ITEM_CATEGORY4 = N'N'))
689        AND SD.ITEM_CATEGORY1 != N'ZEMT'
690        AND IT.AVGWEIGHT = 0.0
691        AND SAR.USER_DEF1 NOT LIKE N'TopUp%'
692    ;
693    */
694 
695    --  CS. Для коробочного отбора - округление высоты короба для получения целого количества слоев
696    --WITH K AS
697    --(
698    --  SELECT 10 AS TOLERANCE
699    --)
700    --UPDATE SAR
701    --SET SAR.CONTAINER_HEIGHT = CT.VOLUME
702    --  / (ISNULL(
703    --      --  по количеству укладок
704    --      IIF(LAYOUTT.ISFULL = 1, LAYOUTT.QTY, NULL)
705    --      --  по целому числу упаковок в
706    --      , FLOOR(CT.HEIGHT / SAR.ITEM_HEIGHT)
707    --          * IIF(LAYOUTT.ISFULL = 0
708    --              , LAYOUTT.LAYER_QTY
709    --              , FLOOR(CT.LENGTH * CT.WIDTH / (SAR.ITEM_LENGTH * SAR.ITEM_WIDTH)))
710    --  ) * SAR.ITEM_WIDTH * SAR.ITEM_LENGTH)
711    --FROM dbo.SHIPMENT_ALLOC_REQUEST SAR
712    --JOIN SHIPMENT_DETAIL AS SD ON SAR.INTERNAL_SHIPMENT_LINE_NUM = SD.INTERNAL_SHIPMENT_LINE_NUM
713    --JOIN (SELECT PACKING_CLASS, CONTAINER_GROUP FROM dbo.PACKING_CLASS) PC
714    --  ON SAR.PACKING_CLASS = PC.PACKING_CLASS
715    --JOIN (SELECT CONTAINER_TYPE, LENGTH, WIDTH, HEIGHT
716    --  --  Объем типа контейнера за вычетом допуска (1)
717    --  , LENGTH * WIDTH * HEIGHT - 1 AS VOLUME
718    --  FROM dbo.CONTAINER_TYPE
719    --) AS CT
720    --  ON PC.CONTAINER_GROUP = CT.CONTAINER_TYPE
721    --OUTER APPLY
722    --(
723    --  SELECT TOP 1 GCD.IDENTIFIER
724    --      , GCD.USER1VALUE AS PALLET_TYPE
725    --      , GCD.USER3VALUE AS QTY
726    --      , IIF(GCD.USER5VALUE >= GCD.USER6VALUE, GCD.USER5VALUE, GCD.USER6VALUE) AS LAYER_QTY
727    --      , IIF(CT.HEIGHT >= CAST(GCD.USER4VALUE AS numeric(19,5)), 1, 0) AS ISFULL
728    --  FROM dbo.GENERIC_CONFIG_DETAIL GCD
729    --  JOIN (
730    --      SELECT USER1VALUE AS ITEM, USER2VALUE AS LAYOUT
731    --      FROM dbo.GENERIC_CONFIG_DETAIL
732    --      WHERE RECORD_TYPE = N'KC_ITEM_LAYOUT'
733    --          AND SAR.ITEM = USER1VALUE
734    --  ) AS ILAYOUT
735    --      ON ILAYOUT.LAYOUT = GCD.IDENTIFIER
736    --  WHERE RECORD_TYPE = N'KC_PALLET_LAYOUT'
737    --      AND (CT.CONTAINER_TYPE LIKE GCD.USER1VALUE + N'%')
738    --  ORDER BY IIF(CT.HEIGHT >= CAST(GCD.USER4VALUE AS numeric(19,5)), 3, 4) DESC
739    --) AS LAYOUTT
740    --CROSS APPLY (
741    --  SELECT IT1.COST AS AVGWEIGHT
742    --  FROM dbo.ITEM IT1
743    --  WHERE SAR.ITEM = IT1.ITEM
744    --      AND SAR.COMPANY = IT1.COMPANY
745    --) AS IT
746    --WHERE SAR.LAUNCH_NUM = @LAUNCH_NUM
747    --  AND SAR.FROM_WORK_ZONE IN (SELECT ZONE FROM ZONE WHERE USER_DEF1 = N'Y')
748    --  AND (@topUp != N'1' OR SAR.ITEM IN (SELECT ITEM FROM dbo.ITEM WHERE ITEM_CATEGORY4 = N'N'))
749    --  --AND (SAR.ALLOCATED_QTY >= ISNULL(LAYOUTT.QTY, FLOOR(CT.HEIGHT / SAR.ITEM_HEIGHT) * FLOOR(CT.LENGTH * CT.WIDTH / (SAR.ITEM_LENGTH * SAR.ITEM_WIDTH))))
750    --  AND SD.ITEM_CATEGORY1 != N'ZEMT'
751    --  AND IT.AVGWEIGHT = 0.0
752    --  AND SAR.INTERNAL_SHIP_ALLOC_NUM NOT IN (
753    --      SELECT RW.INTERNAL_SHIP_ALLOC_NUM FROM (
754    --          SELECT ROW_NUMBER() OVER (PARTITION BY _sd.GROUP_ID ORDER BY _sd.INTERNAL_SHIP_ALLOC_NUM) RWNM
755    --              , _sd.INTERNAL_SHIP_ALLOC_NUM
756    --          FROM @SD_NUM _sd
757    --          WHERE _sd.GROUP_ID IS NOT NULL
758    --      ) RW
759    --      GROUP BY RW.INTERNAL_SHIP_ALLOC_NUM
760    --      HAVING MAX(RWNM) = 1
761    --  )
762    --;
763 
764    DROP TABLE #SAR_RESTRICTION
765 
766END
767 
768 
769      --DROP TABLE #SAR_RESTRICTION DROP TABLE #SD_NUM