· 8 years ago · Jan 11, 2018, 04:12 PM
1SET QUOTED_IDENTIFIER ON
2SET ANSI_NULLS ON
3GO
4-- =============================================
5-- Autor: hector.gonzalez
6-- Fecha de Creacion: 2017-01-31 @ Team ERGON - Sprint ERGON II
7-- Description: Sp que crea las tareas para demanda de despacho de sap
8
9-- Modificación: pablo.aguilar
10-- Fecha de Creacion: 2017-04-12 Team ERGON - Sprint ERGON EPONA
11-- Description: Se quita cursor y se reserva unicamente el inventario necesario para suplir la tarea
12
13
14-- Modificación: pablo.aguilar
15-- Fecha de Modificaci[on: 2017-05-17 ErgonTeam@Sheik
16-- Description: Se agrega parámetro de @IS_CONSOLIDATED y se asigna el tasksubtype dependiendo de esta variable
17
18-- Modificacion 7/13/2017 @ NEXUS-Team Sprint AgeOfEmpires
19-- rodrigo.gomez
20-- Se agrega la explocion de masterpack a un nivel si no hay inventario suficiente
21
22-- Modificacion 8/29/2017 @ NEXUS-Team Sprint CommandAndConquer
23-- rodrigo.gomez
24-- Se agregan los parametros @SOURCE_TYPE y @TRANSFER_REQUEST_ID
25
26-- Modificacion 04-Oct-17 @ Nexus Team Sprint enave
27-- pablo.aguilar
28-- Se agrega parámetro de maneja linea de picking
29
30-- Modificacion 11-Dec-17 @ Nexus Team Sprint HeyYouPikachu!
31 -- pablo.aguilar
32 -- Se quita validación de masterpack en linea de picking
33
34-- Modificacion 22-Dec-17 @ Nexus Team Sprint @IceAge
35 -- pablo.aguilar
36 -- Se agregan nueva función de tabla para obtener las licencias a pickear basado en algoritmos de ascedente y descedente
37
38/*
39-- Ejemplo de Ejecucion:
40 EXEC [alsersa].[OP_WMS_SP_INSERT_TASKS_GENERAL_PICKING_DEMAND]
41 @TASK_OWNER = 'ADMIN', -- varchar(25)
42 @TASK_ASSIGNEDTO = 'ACAMACHO', -- varchar(25)
43 @QUANTITY_ASSIGNED = 10, -- numeric
44 @CODIGO_POLIZA_TARGET = NULL, -- varchar(25)
45 @MATERIAL_ID = 'alsersa/SKUPRUEBA', -- varchar(25)
46 @BARCODE_ID = 'alsersa/SKUPRUEBA', -- varchar(50)
47 @ALTERNATE_BARCODE = 'alsersa/SKUPRUEBA', -- varchar(50)
48 @MATERIAL_NAME = 'Prueba', -- varchar(200)
49 @CLIENT_OWNER = 'alsersa', -- varchar(25)
50 @CLIENT_NAME = 'alsersa Guatemala', -- varchar(150)
51 @IS_FROM_SONDA = 0, -- int
52 @CODE_WAREHOUSE = 'BODEGA_01', -- varchar(50)
53 @IS_FROM_ERP = 0, -- int
54 @WAVE_PICKING_ID = 0, -- numeric
55 @DOC_ID_TARGET = 0, -- int
56 @LOCATION_SPOT_TARGET = 'B01-R01-C01-NA', -- varchar(25)
57 @IS_CONSOLIDATED = 0, -- int,
58 @SOURCE_TYPE = NULL,
59 @TRANSFER_REQUEST_ID = 0
60
61*/
62
63-- =============================================
64ALTER PROCEDURE [alsersa].[OP_WMS_SP_INSERT_TASKS_GENERAL_PICKING_DEMAND] (
65 @TASK_OWNER VARCHAR(25)
66 ,@TASK_ASSIGNEDTO VARCHAR(25)
67 ,@QUANTITY_ASSIGNED NUMERIC(18, 3)
68 ,@CODIGO_POLIZA_TARGET VARCHAR(25)
69 ,@MATERIAL_ID VARCHAR(50)
70 ,@BARCODE_ID VARCHAR(50)
71 ,@ALTERNATE_BARCODE VARCHAR(50) = ''
72 ,@MATERIAL_NAME VARCHAR(200)
73 ,@CLIENT_OWNER VARCHAR(25)
74 ,@CLIENT_NAME VARCHAR(150)
75 ,@IS_FROM_SONDA INT = 0
76 ,@CODE_WAREHOUSE VARCHAR(50)
77 ,@IS_FROM_ERP INT
78 ,@WAVE_PICKING_ID NUMERIC(18, 0)
79 ,@DOC_ID_TARGET INT
80 ,@LOCATION_SPOT_TARGET VARCHAR(25)
81 ,@IS_CONSOLIDATED INT = 0
82 ,@FROM_MASTERPACK INT = 0
83 ,@SOURCE_TYPE VARCHAR(50) = NULL
84 ,@TRANSFER_REQUEST_ID INT = 0
85 ,@TONE VARCHAR(20) = NULL
86 ,@CALIBER VARCHAR(20) = NULL
87 ,@IN_PICKING_LINE INT = 0
88 ,@IS_FOR_DELIVERY_IMMEDIATE INT
89 )
90AS
91BEGIN
92 SET NOCOUNT ON;
93 --
94
95 DECLARE
96 @WPI NUMERIC(18, 0)
97 ,@ASSIGNED_DATE DATETIME
98 ,@CURRENT_LOCATION VARCHAR(25)
99 ,@CURRENT_WAREHOUSE VARCHAR(25)
100 ,@LICENSE_ID NUMERIC(18, 0)
101 ,@TASK_COMMENTS VARCHAR(150)
102 ,@PRESULT VARCHAR(150)
103 ,@CODIGO_POLIZA_SOURCE VARCHAR(25)
104 ,@TASK_TYPE VARCHAR(25)
105 ,@TASK_SUBTYPE VARCHAR(25)
106 ,@QUANTITY_PENDING NUMERIC(18, 4)
107 ,@HAVBATCH NUMERIC(18) = 0
108 ,@vCURRENT_ASSIGNED NUMERIC(18, 2)
109 ,@IS_MASTER_PACK INT = 0
110 ,@ASSEMBLY_QTY INT = 0
111 ,@AVAILABLE_QTY INT = 0
112 ,@ASSEMBLED_QTY INT = 0
113 ,@QUERY NVARCHAR(MAX)
114 ,@IN_PICKING_LINE_ORIGINAL INT = @IN_PICKING_LINE
115 ,@TASK_ASSIGNEDTO_ORIGINAL VARCHAR(25) = @TASK_ASSIGNEDTO;
116
117 BEGIN TRY
118
119 SELECT
120 @ASSIGNED_DATE = GETDATE()
121 ,@TASK_TYPE = 'TAREA_PICKING'
122 ,@TASK_SUBTYPE = CASE WHEN @IS_CONSOLIDATED = 1
123 THEN 'DESPACHO_CONSOLIDADO'
124 ELSE 'DESPACHO_GENERAL'
125 END
126 ,@QUANTITY_PENDING = @QUANTITY_ASSIGNED;
127 --
128 SELECT
129 @TASK_SUBTYPE = CASE WHEN @SOURCE_TYPE = 'WT - SWIFT'
130 OR @SOURCE_TYPE = 'WT - ERP'
131 THEN 'DESPACHO_WT'
132 ELSE @TASK_SUBTYPE
133 END;
134 SELECT TOP 1
135 @TASK_ASSIGNEDTO = CASE WHEN [USE_PICKING_LINE] = 0
136 THEN ''
137 ELSE @TASK_ASSIGNEDTO
138 END
139 FROM
140 [alsersa].[OP_WMS_MATERIALS]
141 WHERE
142 [MATERIAL_ID] = @MATERIAL_ID;
143 ---------------------------------------------------------------------------------
144 -- Valida Ola de picking
145 ---------------------------------------------------------------------------------
146 IF @WAVE_PICKING_ID = 0
147 BEGIN
148 SELECT
149 @WAVE_PICKING_ID = ISNULL(MAX([WAVE_PICKING_ID]),
150 0) + 1
151 FROM
152 [alsersa].[OP_WMS_TASK_LIST]
153 WHERE
154 [SERIAL_NUMBER] > 0;
155 END;
156 IF @WPI = NULL
157 BEGIN
158 SET @WPI = 1;
159 END;
160
161 IF @WAVE_PICKING_ID = 0
162 BEGIN
163 SET @WAVE_PICKING_ID = @WPI;
164 END;
165 ELSE
166 BEGIN
167 SET @WPI = @WAVE_PICKING_ID;
168 END;
169
170 -- ------------------------------------------------------------------------------------
171 -- Obtiene el inventario disponible para el material, luego verifica que sea suficiente para el picking.
172 -- Si no lo es verifica que sea un masterpack y no venga de una explosion de masterpack, de ser asi obtiene la cantidad disponible a armar y
173 -- establece una nueva cantidad asignada para solo armar los MPs necesarios.
174 -- ------------------------------------------------------------------------------------
175
176 SELECT TOP 1
177 @IS_MASTER_PACK = [IS_MASTER_PACK]
178 --Validar que si está en linea de picking el material tambien este en linea de picking
179 ,@IN_PICKING_LINE = CASE WHEN @IN_PICKING_LINE = 1
180 AND [USE_PICKING_LINE] = 1
181 THEN 1
182 ELSE 0
183 END
184 FROM
185 [alsersa].[OP_WMS_MATERIALS]
186 WHERE
187 [MATERIAL_ID] = @MATERIAL_ID
188 AND [CLIENT_OWNER] = @CLIENT_OWNER;
189
190 ---------------------------------------------------------------------------------
191 -- Valida inventario
192 ---------------------------------------------------------------------------------
193 SELECT
194 @AVAILABLE_QTY = ISNULL(SUM([AVAILABLE_QTY]), 0)
195 FROM
196 [alsersa].[OP_WMS_VIEW_INVENTORY_FOR_PICKING]
197 WHERE
198 [MATERIAL_ID] = @MATERIAL_ID
199 AND [CURRENT_WAREHOUSE] = @CODE_WAREHOUSE;
200
201 IF (@QUANTITY_ASSIGNED > @AVAILABLE_QTY)
202 BEGIN
203 IF @IS_MASTER_PACK = 1
204 AND @FROM_MASTERPACK = 0
205 BEGIN
206 SELECT
207 @ASSEMBLY_QTY = [alsersa].[OP_WMS_FN_GET_AVAILABLE_INVENTORY_FOR_MASTERPACK_ON_DISPATCH_DEMAND](@MATERIAL_ID,
208 @CODE_WAREHOUSE);
209
210 IF @ASSEMBLY_QTY > 0
211 BEGIN
212 SELECT
213 @QUANTITY_ASSIGNED = @QUANTITY_ASSIGNED
214 - @AVAILABLE_QTY;
215 GOTO EXPLOTAR_MASTERPACK;
216 END;
217 END;
218
219 SELECT
220 @PRESULT = 'ERROR, No cuenta con inventario suficiente para continuar con la operación del cliente: '
221 + @CLIENT_NAME + ' SKU: ' + @MATERIAL_ID;
222 RAISERROR (@PRESULT, 16, 1);
223
224 END;
225
226
227 PROCESAR_CON_INVENTARIO:
228 ---------------------------------------------------------------------------------
229 -- Valida si maneja lote
230 ---------------------------------------------------------------------------------
231 SELECT TOP 1
232 @HAVBATCH = ISNULL([BATCH_REQUESTED], 0)
233 FROM
234 [alsersa].[OP_WMS_MATERIALS]
235 WHERE
236 [CLIENT_OWNER] = @CLIENT_OWNER
237 AND [MATERIAL_ID] = @MATERIAL_ID;
238
239 SELECT
240 @TASK_COMMENTS = 'OLA DE PICKING #'
241 + CAST(@WPI AS VARCHAR);
242
243 CREATE TABLE [#LICENCIAS] (
244 [CURRENT_LOCATION] VARCHAR(25)
245 ,[CURRENT_WAREHOUSE] VARCHAR(25)
246 ,[LICENSE_ID] NUMERIC
247 ,[CODIGO_POLIZA] VARCHAR(25)
248 ,[QTY] NUMERIC(19, 4)
249 ,[DATE_BASE] DATETIME
250 ,[ROW] INT
251 );
252
253 CREATE TABLE [#LICENCIAS_TEMP] (
254 [CURRENT_LOCATION] VARCHAR(25)
255 ,[CURRENT_WAREHOUSE] VARCHAR(25)
256 ,[LICENSE_ID] NUMERIC
257 ,[CODIGO_POLIZA] VARCHAR(25)
258 ,[QTY] NUMERIC(19, 4)
259 ,[DATE_BASE] DATETIME
260 ,[ROW] INT
261 );
262
263 PRINT '@HAVBATCH' + CAST(@HAVBATCH AS VARCHAR);
264 PRINT '@WAVE_PICKING_ID'
265 + CAST(@WAVE_PICKING_ID AS VARCHAR);
266
267
268 INSERT INTO [#LICENCIAS]
269 (
270 [CURRENT_LOCATION]
271 ,[CURRENT_WAREHOUSE]
272 ,[LICENSE_ID]
273 ,[CODIGO_POLIZA]
274 ,[QTY]
275 ,[DATE_BASE]
276 ,[ROW]
277 )
278 SELECT
279 [CURRENT_LOCATION]
280 ,[CURRENT_WAREHOUSE]
281 ,[LICENSE_ID]
282 ,[CODIGO_POLIZA]
283 ,[QTY]
284 ,[FECHA_DOCUMENTO]
285 ,[ORDER]
286 FROM
287 [alsersa].[OP_WMS_FN_GET_LICENSE_TO_PICK](@MATERIAL_ID,
288 @CODE_WAREHOUSE,
289 @QUANTITY_ASSIGNED,
290 @HAVBATCH, NULL);
291
292 DECLARE
293 @LICENCIA_ELEGIDA NUMERIC = NULL
294 ,@LICENCIA_ACTUAL NUMERIC
295 ,@FILA_LICENCIA INT
296 ,@CANTIDAD_LICENCIAS INT
297 ,@QTY_LICENCIA INT
298 ,@FILA INT = 1;
299
300
301 IF @TONE IS NOT NULL
302 OR @CALIBER IS NOT NULL
303 BEGIN
304 INSERT INTO [#LICENCIAS_TEMP]
305 SELECT
306 [L].[CURRENT_LOCATION]
307 ,[L].[CURRENT_WAREHOUSE]
308 ,[L].[LICENSE_ID]
309 ,[L].[CODIGO_POLIZA]
310 ,[L].[QTY]
311 ,[L].[DATE_BASE]
312 ,ROW_NUMBER() OVER (ORDER BY [L].[QTY], [L].[DATE_BASE])
313 FROM
314 [#LICENCIAS] [L]
315 INNER JOIN [alsersa].[OP_WMS_INV_X_LICENSE] [IL] ON (
316 [IL].[LICENSE_ID] = [L].[LICENSE_ID]
317 AND [IL].[MATERIAL_ID] = @MATERIAL_ID
318 )
319 INNER JOIN [alsersa].[OP_WMS_TONE_AND_CALIBER_BY_MATERIAL] [TCM] ON ([TCM].[TONE_AND_CALIBER_ID] = [IL].[TONE_AND_CALIBER_ID])
320 WHERE
321 (
322 @TONE IS NULL
323 OR [TCM].[TONE] = @TONE
324 )
325 AND (
326 @CALIBER IS NULL
327 OR [TCM].[CALIBER] = @CALIBER
328 );
329
330 DELETE
331 [#LICENCIAS];
332 INSERT INTO [#LICENCIAS]
333 SELECT
334 *
335 FROM
336 [#LICENCIAS_TEMP];
337 DELETE
338 [#LICENCIAS_TEMP];
339 END;
340
341 WHILE (EXISTS ( SELECT TOP 1
342 1
343 FROM
344 [#LICENCIAS] ))
345 BEGIN
346
347 ---------------------------------------------------------------------------------
348 -- Se obtiene la cantidad total de licencias
349 ---------------------------------------------------------------------------------
350 DECLARE @QTY_LICENSE_TONO NUMERIC(19, 4);
351 SET @FILA = 1;
352 SELECT
353 @CANTIDAD_LICENCIAS = COUNT([LICENSE_ID])
354 ,@QTY_LICENSE_TONO = SUM([QTY])
355 FROM
356 [#LICENCIAS];
357
358 ---------------------------------------------------------------------------------
359 -- Se verifica si es un picking de tono o calibre para sacar el inventario por lote
360 ---------------------------------------------------------------------------------
361 IF @TONE IS NOT NULL
362 OR @CALIBER IS NOT NULL
363 BEGIN
364
365 ---------------------------------------------------------------------------------
366 -- Se obtiene la cantidad total de licencias
367 ---------------------------------------------------------------------------------
368
369 SET @FILA = 1;
370
371 IF @QUANTITY_PENDING <= @QTY_LICENSE_TONO
372 BEGIN
373
374 WHILE (@FILA <= @CANTIDAD_LICENCIAS)
375 BEGIN
376 SELECT
377 @LICENCIA_ACTUAL = [L].[LICENSE_ID]
378 ,@FILA_LICENCIA = [L].[ROW]
379 ,@QTY_LICENCIA = [L].[QTY]
380 FROM
381 [#LICENCIAS] [L]
382 INNER JOIN [alsersa].[OP_WMS_INV_X_LICENSE] [IL] ON (
383 [IL].[LICENSE_ID] = [L].[LICENSE_ID]
384 AND [IL].[MATERIAL_ID] = @MATERIAL_ID
385 )
386 INNER JOIN [alsersa].[OP_WMS_TONE_AND_CALIBER_BY_MATERIAL] [TCM] ON ([TCM].[TONE_AND_CALIBER_ID] = [IL].[TONE_AND_CALIBER_ID])
387 WHERE
388 (
389 @TONE IS NULL
390 OR [TCM].[TONE] = @TONE
391 )
392 AND (
393 @CALIBER IS NULL
394 OR [TCM].[CALIBER] = @CALIBER
395 )
396 AND [L].[ROW] = @FILA
397 ORDER BY
398 [L].[QTY] ASC
399 ,[L].[DATE_BASE] ASC;
400 ---------------------------------------------------------------------------------
401 -- Si la cantidad pendiente
402 ---------------------------------------------------------------------------------
403 IF @QUANTITY_PENDING <= @QTY_LICENCIA
404 OR @FILA_LICENCIA = @CANTIDAD_LICENCIAS
405 BEGIN
406 SELECT
407 @LICENCIA_ELEGIDA = @LICENCIA_ACTUAL;
408 BREAK;
409 END;
410 ELSE
411 BEGIN
412 SET @FILA = @FILA + 1;
413 END;
414 END;
415 END;
416 ELSE
417 BEGIN
418 SELECT
419 @PRESULT = 'ERROR, No cuenta con inventario suficiente para continuar con la operación del cliente: '
420 + @CLIENT_NAME + ' SKU: '
421 + @MATERIAL_ID;
422 RAISERROR (@PRESULT, 16, 1);
423 END;
424
425 SELECT
426 @CANTIDAD_LICENCIAS = COUNT([LICENSE_ID])
427 FROM
428 [#LICENCIAS];
429
430 END;
431
432 ---------------------------------------------------------------------------------
433 -- Recorrer por licencias el material
434 ---------------------------------------------------------------------------------
435
436 SELECT TOP 1
437 @CURRENT_LOCATION = [L].[CURRENT_LOCATION]
438 ,@CURRENT_WAREHOUSE = [L].[CURRENT_WAREHOUSE]
439 ,@LICENSE_ID = [L].[LICENSE_ID]
440 ,@CODIGO_POLIZA_SOURCE = [L].[CODIGO_POLIZA]
441 ,@vCURRENT_ASSIGNED = CASE WHEN @QUANTITY_PENDING >= [L].[QTY]
442 THEN [L].[QTY]
443 ELSE @QUANTITY_PENDING
444 END
445 FROM
446 [#LICENCIAS] [L]
447 WHERE
448 (
449 @LICENCIA_ELEGIDA IS NULL
450 OR [L].[LICENSE_ID] = @LICENCIA_ELEGIDA
451 )
452 ORDER BY
453 [L].[ROW];
454
455
456 PRINT CAST(@LICENSE_ID AS VARCHAR);
457
458 IF EXISTS ( SELECT TOP 1
459 1
460 FROM
461 [alsersa].[OP_WMS_TASK_LIST]
462 WHERE
463 [WAVE_PICKING_ID] = @WAVE_PICKING_ID
464 AND [LICENSE_ID_SOURCE] = @LICENSE_ID
465 AND [MATERIAL_ID] = @MATERIAL_ID )
466 BEGIN
467 ---------------------------------------------------------------------------------
468 -- Existe el material en la misma ola y hay disponible de la licencia se actualiza el registro.
469 ---------------------------------------------------------------------------------
470 PRINT CAST('Modifica' AS VARCHAR);
471 UPDATE
472 [alsersa].[OP_WMS_TASK_LIST]
473 SET
474 [QUANTITY_PENDING] = [QUANTITY_PENDING]
475 + @vCURRENT_ASSIGNED
476 ,[QUANTITY_ASSIGNED] = [QUANTITY_ASSIGNED]
477 + @vCURRENT_ASSIGNED
478 WHERE
479 [WAVE_PICKING_ID] = @WAVE_PICKING_ID
480 AND [LICENSE_ID_SOURCE] = @LICENSE_ID
481 AND [MATERIAL_ID] = @MATERIAL_ID;
482 END;
483 ELSE
484 BEGIN
485 PRINT 'Insert';
486 INSERT INTO [alsersa].[OP_WMS_TASK_LIST]
487 (
488 [WAVE_PICKING_ID]
489 ,[TASK_TYPE]
490 ,[TASK_SUBTYPE]
491 ,[TASK_OWNER]
492 ,[TASK_ASSIGNEDTO]
493 ,[ASSIGNED_DATE]
494 ,[QUANTITY_PENDING]
495 ,[QUANTITY_ASSIGNED]
496 ,[CODIGO_POLIZA_SOURCE]
497 ,[CODIGO_POLIZA_TARGET]
498 ,[LICENSE_ID_SOURCE]
499 ,[REGIMEN]
500 ,[IS_DISCRETIONAL]
501 ,[MATERIAL_ID]
502 ,[BARCODE_ID]
503 ,[ALTERNATE_BARCODE]
504 ,[MATERIAL_NAME]
505 ,[WAREHOUSE_SOURCE]
506 ,[LOCATION_SPOT_SOURCE]
507 ,[CLIENT_OWNER]
508 ,[CLIENT_NAME]
509 ,[TASK_COMMENTS]
510 ,[TRANS_OWNER]
511 ,[IS_COMPLETED]
512 ,[MATERIAL_SHORT_NAME]
513 ,[IS_FROM_SONDA]
514 ,[IS_FROM_ERP]
515 ,[DOC_ID_TARGET]
516 ,[LOCATION_SPOT_TARGET]
517 ,[SOURCE_TYPE]
518 ,[TRANSFER_REQUEST_ID]
519 ,[TONE]
520 ,[CALIBER]
521 ,[IN_PICKING_LINE]
522 ,[IS_FOR_DELIVERY_IMMEDIATE]
523 )
524 VALUES
525 (
526 @WAVE_PICKING_ID
527 ,@TASK_TYPE
528 ,@TASK_SUBTYPE
529 ,@TASK_OWNER
530 ,@TASK_ASSIGNEDTO
531 ,@ASSIGNED_DATE
532 ,@vCURRENT_ASSIGNED
533 ,@vCURRENT_ASSIGNED
534 ,@CODIGO_POLIZA_SOURCE
535 ,@CODIGO_POLIZA_TARGET
536 ,@LICENSE_ID
537 ,'GENERAL'
538 ,1
539 ,@MATERIAL_ID
540 ,@BARCODE_ID
541 ,@ALTERNATE_BARCODE
542 ,@MATERIAL_NAME
543 ,@CURRENT_WAREHOUSE
544 ,@CURRENT_LOCATION
545 ,@CLIENT_OWNER
546 ,@CLIENT_NAME
547 ,@TASK_COMMENTS
548 ,0
549 ,0
550 ,@MATERIAL_NAME
551 ,@IS_FROM_SONDA
552 ,@IS_FROM_ERP
553 ,@DOC_ID_TARGET
554 ,@LOCATION_SPOT_TARGET
555 ,@SOURCE_TYPE
556 ,@TRANSFER_REQUEST_ID
557 ,@TONE
558 ,@CALIBER
559 ,@IN_PICKING_LINE
560 ,@IS_FOR_DELIVERY_IMMEDIATE
561 );
562 END;
563
564 SELECT
565 @QUANTITY_PENDING = @QUANTITY_PENDING
566 - @vCURRENT_ASSIGNED;
567
568 IF @QUANTITY_PENDING <= 0
569 BEGIN
570 DELETE
571 [#LICENCIAS];
572 END;
573 ELSE
574 BEGIN
575 DELETE
576 [#LICENCIAS]
577 WHERE
578 [LICENSE_ID] = @LICENSE_ID;
579 END;
580 END;
581 IF @FROM_MASTERPACK = 0
582 BEGIN
583 GOTO FIN;
584 END;
585 ELSE
586 BEGIN
587 GOTO FIN_COMPONENTE;
588 END;
589
590 EXPLOTAR_MASTERPACK:
591
592 IF @FROM_MASTERPACK = 0
593 BEGIN
594 PRINT 'MASTERPACK EXPLOTADO';
595 SELECT
596 [MASTER_PACK_COMPONENT_ID]
597 ,[MASTER_PACK_CODE]
598 ,[COMPONENT_MATERIAL]
599 ,([QTY] * @QUANTITY_ASSIGNED) [QTY]
600 INTO
601 [#COMPONENTES]
602 FROM
603 [alsersa].[OP_WMS_COMPONENTS_BY_MASTER_PACK]
604 WHERE
605 [MASTER_PACK_CODE] = @MATERIAL_ID;
606
607 WHILE EXISTS ( SELECT TOP 1
608 1
609 FROM
610 [#COMPONENTES] )
611 BEGIN
612 DECLARE @QTY_COMP INT = 0;
613 DECLARE @MATERIAL_ID_COMP VARCHAR(25) = '';
614 DECLARE @BARCODE_ID_COMP VARCHAR(50) = '';
615 DECLARE @ALTERNATE_BARCODE_COMP VARCHAR(50) = '';
616 DECLARE @MATERIAL_NAME_COMP VARCHAR(200) = '';
617
618 SELECT
619 @MATERIAL_ID_COMP = [COMPONENT_MATERIAL]
620 ,@QTY_COMP = [QTY]
621 ,@BARCODE_ID_COMP = [M].[BARCODE_ID]
622 ,@ALTERNATE_BARCODE_COMP = [M].[ALTERNATE_BARCODE]
623 ,@MATERIAL_NAME_COMP = [M].[MATERIAL_NAME]
624 FROM
625 [#COMPONENTES] [C]
626 INNER JOIN [alsersa].[OP_WMS_MATERIALS] [M] ON [C].[COMPONENT_MATERIAL] = [M].[MATERIAL_ID];
627
628 EXEC [alsersa].[OP_WMS_SP_INSERT_TASKS_GENERAL_PICKING_DEMAND] @TASK_OWNER = @TASK_OWNER, -- varchar(25)
629 @TASK_ASSIGNEDTO = @TASK_ASSIGNEDTO_ORIGINAL, -- varchar(25)
630 @QUANTITY_ASSIGNED = @QTY_COMP, -- numeric
631 @CODIGO_POLIZA_TARGET = @CODIGO_POLIZA_TARGET, -- varchar(25)
632 @MATERIAL_ID = @MATERIAL_ID_COMP, -- varchar(25)
633 @BARCODE_ID = @BARCODE_ID_COMP, -- varchar(50)
634 @ALTERNATE_BARCODE = @ALTERNATE_BARCODE_COMP, -- varchar(50)
635 @MATERIAL_NAME = @MATERIAL_NAME_COMP, -- varchar(200)
636 @CLIENT_OWNER = @CLIENT_OWNER, -- varchar(25)
637 @CLIENT_NAME = @CLIENT_NAME, -- varchar(150)
638 @IS_FROM_SONDA = @IS_FROM_SONDA, -- int
639 @CODE_WAREHOUSE = @CODE_WAREHOUSE, -- varchar(50)
640 @IS_FROM_ERP = @IS_FROM_ERP, -- int
641 @WAVE_PICKING_ID = @WAVE_PICKING_ID, -- numeric
642 @DOC_ID_TARGET = @DOC_ID_TARGET, -- int
643 @LOCATION_SPOT_TARGET = @LOCATION_SPOT_TARGET, -- varchar(25)
644 @IS_CONSOLIDATED = @IS_CONSOLIDATED, -- int
645 @FROM_MASTERPACK = 1,
646 @SOURCE_TYPE = @SOURCE_TYPE,
647 @TRANSFER_REQUEST_ID = @TRANSFER_REQUEST_ID,
648 @IN_PICKING_LINE = @IN_PICKING_LINE_ORIGINAL;
649
650 DELETE FROM
651 [#COMPONENTES]
652 WHERE
653 @MATERIAL_ID_COMP = [COMPONENT_MATERIAL];
654 END;
655
656 SELECT
657 @ASSEMBLED_QTY = @QUANTITY_ASSIGNED;
658 -- ------------------------------------------------------------------------------------
659 -- Si la cantidad disponible en el inventario es mayor a 0 establece el valor de @QUANTITY_ASSIGNED a lo que esta disponible y lo envia a procesar esa cantidad normalmente.
660 -- ------------------------------------------------------------------------------------
661 IF @AVAILABLE_QTY > 0
662 BEGIN
663 SELECT
664 @QUANTITY_ASSIGNED = @AVAILABLE_QTY;
665 GOTO PROCESAR_CON_INVENTARIO;
666 END;
667 END;
668
669 FIN:
670 SELECT
671 1 AS [Resultado]
672 ,'Proceso Exitoso' [Mensaje]
673 ,0 [Codigo]
674 ,CAST(@WAVE_PICKING_ID AS VARCHAR) + '|'
675 + CAST(@ASSEMBLED_QTY AS VARCHAR) [DbData];
676 FIN_COMPONENTE:
677 END TRY
678
679 BEGIN CATCH
680 SELECT
681 -1 AS [Resultado]
682 ,ERROR_MESSAGE() [Mensaje]
683 ,@@ERROR [Codigo]
684 ,'' [DbData];
685 END CATCH;
686END;
687GO