1 PACKAGE BODY WSH_SC_PLD_PKG as
2 /* $Header: WSHSCPDB.pls 115.5 99/07/16 08:20:47 porting shi $ */
3
4 --
5 -- Package
6 -- WSH_SC_PLD_PKG
7 --
8 -- Purpose
9 -- This package is used by the confirm delivery form to update
10 -- serial number information (details) entered on a delivery line
11 -- and do the explosion of the serial numbers if necessary.
12 -- History
13 -- Version 1.0 03/01/97 RMANJUNA
14 --
15 --
16 -- Name
17 -- Close_Details
18 -- Purpose
19 -- To update picking line details with information entered in the
20 -- serial number window.
21 --
22 --
23 -- Arguments
24 --
25 --
26 --
27 -- Notes
28
29 PROCEDURE Close_Details(X_Delivery_Id IN NUMBER)
30 IS
31 x_picking_line_id NUMBER;
32 x_picking_line_detail_id NUMBER;
33 x_split_detail_id NUMBER;
34 x_trx_qty NUMBER;
35 x_req_qty NUMBER;
36 x_new_req_qty NUMBER;
37 x_new_trx_qty NUMBER;
38 x_explode_flag NUMBER;
39 x_serial_number varchar2(30);
40 --get the picking line details for this Delivery
41 CURSOR picking_line_details_cursor IS
42 SELECT picking_line_detail_id, requested_quantity, shipped_quantity,
43 serial_number
44 FROM so_picking_line_details
45 WHERE delivery_id = X_Delivery_Id
46 AND transaction_temp_id IS NOT NULL;
47
48 -- to check if the item is under serial number control
49 CURSOR check_serial(X_Pk_Line_Detail_Id NUMBER) is
50 SELECT msi.serial_number_control_code
51 FROM mtl_system_items msi,
52 so_picking_lines_all pl,
53 so_picking_line_details pld
54 WHERE pl.inventory_item_id = msi.inventory_item_id
55 AND pl.warehouse_id = msi.organization_id
56 AND msi.serial_number_control_code in (2,5,6)
57 AND pl.picking_line_id = pld.picking_line_id
58 AND pld.picking_line_detail_id = X_Pk_Line_Detail_Id;
59 BEGIN
60
61 -- X_Error := 0;
62
63 OPEN picking_line_details_cursor;
64 LOOP
65 FETCH picking_line_details_cursor
66 INTO x_picking_line_detail_id, x_req_qty,
67 x_trx_qty, x_serial_number ;
68 EXIT WHEN picking_line_details_cursor%NOTFOUND;
69 IF (x_serial_number is NULL) then
70 IF x_trx_qty < x_req_qty THEN
71 -- Create remainder detail
72 Create_Remainders(x_picking_line_detail_id, (x_req_qty - x_trx_qty));
73 END IF;
74
75 x_explode_flag := -1; -- initialize
76
77 OPEN check_serial(x_picking_line_detail_id);
78
79 FETCH check_serial INTO x_explode_flag;
80
81 IF x_explode_flag > 0 THEN -- basically if x_explode flag is 2, 5 or 6
82 -- Serial Number Explosion
83 Explode_Lines(x_picking_line_detail_id);
84 END IF;
85
86 CLOSE check_serial;
87 end if;
88
89 END LOOP;
90
91 CLOSE picking_line_details_cursor;
92
93 -- Now go ahead and Delete the records from MSNT
94 Delete_From_Msnt(X_Delivery_Id);
95
96 /* donno if we need to do this
97 IF (X_Error = 0) THEN
98 SHP_SC_SERIAL_VALIDATION_PKG.Check_Duplicate_Serial(X_Entity_Id, X_Error, X_Error_Lines);
99 END IF;
100 */
101
102 EXCEPTION
103 WHEN OTHERS THEN
104 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
105 FND_MESSAGE.Set_Token('PACKAGE','SHP_SC_PLD_PKG.Close_Details');
106 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
107 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
108 APP_EXCEPTION.Raise_Exception;
109
110 END Close_Details;
111
112 --
113 -- Name
114 -- Update_Details
115 -- Purpose
116 -- To update picking line details with information entered into the
117 -- transaction block.
118 -- Arguments
119 -- X_Trx_Src_Line_Id IN NUMBER
120 -- X_Requested_Qty IN NUMBER
121 -- X_Shipped_Qty IN NUMBER
122 -- X_Serial IN VARCHAR2
123 --
124 --
125 -- Notes
126 --
127
128
129 PROCEDURE Update_Details( X_Trx_Src_Line_Id IN NUMBER,
130 X_Requested_Qty IN NUMBER,
131 X_Shipped_Qty IN NUMBER,
132 X_Serial IN VARCHAR2) IS
133 BEGIN
134
135 UPDATE SO_PICKING_LINE_DETAILS PLD
136 SET
137 (LAST_UPDATE_DATE,
138 LAST_UPDATED_BY,
139 LAST_UPDATE_LOGIN,
140 REQUESTED_QUANTITY,
141 SHIPPED_QUANTITY,
142 SERIAL_NUMBER ) =
143 (SELECT
144 SYSDATE,
145 FND_GLOBAL.USER_ID,
146 FND_GLOBAL.USER_ID,
147 X_Requested_Qty,
148 X_Shipped_Qty,
149 X_Serial
150 FROM DUAL)
151 WHERE PICKING_LINE_DETAIL_ID = X_Trx_Src_Line_Id;
152
153 EXCEPTION
154 WHEN NO_DATA_FOUND THEN
155 -- Replace this with real error? How to reflect in form?
156 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
157 FND_MESSAGE.Set_Token('PACKAGE','WSH_SC_PLD_PKG.Update_Details');
158 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
159 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
160 APP_EXCEPTION.Raise_Exception;
161 WHEN OTHERS THEN
162 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
163 FND_MESSAGE.Set_Token('PACKAGE','WSH_SC_PLD_PKG.Update_Details');
164 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
165 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
166 APP_EXCEPTION.Raise_Exception;
167
168 END Update_Details;
169
170 --
171 -- Purpose
172 -- This the used for split delivery line function to create a new
173 -- picking line details
174 --
175 FUNCTION Insert_Splitted_Details( X_Parent_Detail_Id IN NUMBER,
176 X_Req_Qty IN NUMBER,
177 X_detail_type_code IN VARCHAR2)
178 RETURN NUMBER IS
179 X_new_detail_id NUMBER;
180 BEGIN
181 SELECT SO_PICKING_LINE_DETAILS_S.NEXTVAL
182 INTO X_new_detail_id
183 FROM DUAL;
184
185 Insert_Details( x_new_detail_id,
186 X_Parent_Detail_Id,
187 NULL,
188 X_Req_Qty,
189 NULL,
190 'SPLIT',
191 X_detail_type_code);
192
193 return X_new_detail_id;
194 END Insert_Splitted_Details;
195
196 --
197 -- Name
198 -- Insert_Details
199 -- Purpose
200 -- To insert new picking line details for details split on transaction
201 -- block or entered in the serial number window.
202 -- Arguments
203 -- X_New_Detail_Id IN NUMBER
204 -- X_Parent_Detail_Id IN NUMBER
205 -- X_Trx_Qty IN NUMBER
206 -- X_Req_Qty IN NUMBER
207 -- X_Serial IN VARCHAR2
208 -- X_Mode IN VARCHAR2
209 --
210 --
211 --
212 -- Notes
213 --
214
215 PROCEDURE Insert_Details( X_New_Detail_Id IN NUMBER,
216 X_Parent_Detail_Id IN NUMBER,
217 X_Trx_Qty IN NUMBER,
218 X_Req_Qty IN NUMBER,
219 X_Serial IN VARCHAR2,
220 X_Mode IN VARCHAR2,
221 X_detail_type_code IN VARCHAR2 DEFAULT 'NA')
222 IS
223 X_Created_Detail_Id NUMBER;
224 BEGIN
225
226
227 IF (X_New_Detail_Id is NULL) THEN
228 SELECT SO_PICKING_LINE_DETAILS_S.NEXTVAL
229 INTO X_Created_Detail_Id
230 FROM DUAL;
231 END IF;
232
233 INSERT INTO SO_PICKING_LINE_DETAILS (
234 PICKING_LINE_DETAIL_ID
235 ,LAST_UPDATE_DATE
236 ,LAST_UPDATED_BY
237 ,CREATED_BY
238 ,CREATION_DATE
239 ,LAST_UPDATE_LOGIN
240 ,PROGRAM_APPLICATION_ID
241 ,PROGRAM_ID
242 ,PROGRAM_UPDATE_DATE
243 ,REQUEST_ID
244 ,PICKING_LINE_ID
245 ,WAREHOUSE_ID
246 ,REQUESTED_QUANTITY
247 ,SHIPPED_QUANTITY
248 ,SERIAL_NUMBER
249 ,LOT_NUMBER
250 ,CUSTOMER_REQUESTED_LOT_FLAG
251 ,REVISION
252 ,SUBINVENTORY
253 ,INVENTORY_LOCATION_ID
254 ,SEGMENT1
255 ,SEGMENT2
256 ,SEGMENT3
257 ,SEGMENT4
258 ,SEGMENT5
259 ,SEGMENT6
260 ,SEGMENT7
261 ,SEGMENT8
262 ,SEGMENT9
263 ,SEGMENT10
264 ,SEGMENT11
265 ,SEGMENT12
266 ,SEGMENT13
267 ,SEGMENT14
268 ,SEGMENT15
269 ,SEGMENT16
270 ,SEGMENT17
271 ,SEGMENT18
272 ,SEGMENT19
273 ,SEGMENT20
274 ,INVENTORY_LOCATION_SEGMENTS
275 ,DETAIL_TYPE_CODE
276 ,CONTEXT
277 ,ATTRIBUTE1
278 ,ATTRIBUTE2
279 ,ATTRIBUTE3
280 ,ATTRIBUTE4
281 ,ATTRIBUTE5
282 ,ATTRIBUTE6
283 ,ATTRIBUTE7
284 ,ATTRIBUTE8
285 ,ATTRIBUTE9
286 ,ATTRIBUTE10
287 ,ATTRIBUTE11
288 ,ATTRIBUTE12
289 ,ATTRIBUTE13
290 ,ATTRIBUTE14
291 ,ATTRIBUTE15
292 ,RELEASED_FLAG
293 ,SCHEDULE_DATE
294 ,SCHEDULE_LEVEL
295 ,SCHEDULE_STATUS_CODE
296 ,DEMAND_ID
297 ,AUTOSCHEDULED_FLAG
298 ,DELIVERY
299 ,WIP_RESERVED_QUANTITY
300 ,WIP_COMPLETED_QUANTITY
301 ,SUPPLY_SOURCE_TYPE
302 ,SUPPLY_SOURCE_HEADER_ID
303 ,UPDATE_FLAG
304 ,DEMAND_CLASS_CODE
305 ,RESERVABLE_FLAG
306 ,TRANSACTABLE_FLAG
307 ,LATEST_ACCEPTABLE_DATE
308 ,DPW_ASSIGNED_FLAG
309 ,DELIVERY_ID
310 ,DEPARTURE_ID
311 ,LOAD_SEQ_NUMBER
312 ,MASTER_CONTAINER_ITEM_ID
313 ,DETAIL_CONTAINER_ITEM_ID
314 ,TRANSACTION_TEMP_ID
315 ,PICK_SLIP_NUMBER
316 ,CONTAINER_ID
317 ,MVT_STAT_STATUS)
318
319 SELECT
320 Nvl(X_New_Detail_id, X_Created_Detail_id)
321 ,SYSDATE
322 ,FND_GLOBAL.USER_ID
323 ,FND_GLOBAL.USER_ID
324 ,SYSDATE
325 ,FND_GLOBAL.LOGIN_ID
326 ,PROGRAM_APPLICATION_ID
327 ,PROGRAM_ID
328 ,PROGRAM_UPDATE_DATE
329 ,REQUEST_ID
330 ,PICKING_LINE_ID
331 ,WAREHOUSE_ID
332 ,X_Req_Qty
333 ,X_Trx_Qty
334 ,X_Serial
335 ,LOT_NUMBER
336 ,CUSTOMER_REQUESTED_LOT_FLAG
337 ,REVISION
338 ,SUBINVENTORY
339 ,INVENTORY_LOCATION_ID
340 ,SEGMENT1
341 ,SEGMENT2
342 ,SEGMENT3
343 ,SEGMENT4
344 ,SEGMENT5
345 ,SEGMENT6
346 ,SEGMENT7
347 ,SEGMENT8
348 ,SEGMENT9
349 ,SEGMENT10
350 ,SEGMENT11
351 ,SEGMENT12
352 ,SEGMENT13
353 ,SEGMENT14
354 ,SEGMENT15
355 ,SEGMENT16
356 ,SEGMENT17
357 ,SEGMENT18
358 ,SEGMENT19
359 ,SEGMENT20
360 ,INVENTORY_LOCATION_SEGMENTS
361 ,Decode( X_detail_type_code, 'NA', DETAIL_TYPE_CODE,
362 X_detail_type_code)
363 ,CONTEXT
364 ,ATTRIBUTE1
365 ,ATTRIBUTE2
366 ,ATTRIBUTE3
367 ,ATTRIBUTE4
368 ,ATTRIBUTE5
369 ,ATTRIBUTE6
370 ,ATTRIBUTE7
371 ,ATTRIBUTE8
372 ,ATTRIBUTE9
373 ,ATTRIBUTE10
374 ,ATTRIBUTE11
375 ,ATTRIBUTE12
376 ,ATTRIBUTE13
377 ,ATTRIBUTE14
378 ,ATTRIBUTE15
379 ,RELEASED_FLAG
380 ,SCHEDULE_DATE
381 ,SCHEDULE_LEVEL
382 ,SCHEDULE_STATUS_CODE
383 ,DEMAND_ID
384 ,AUTOSCHEDULED_FLAG
385 ,DELIVERY
386 ,WIP_RESERVED_QUANTITY
387 ,WIP_COMPLETED_QUANTITY
388 ,SUPPLY_SOURCE_TYPE
389 ,SUPPLY_SOURCE_HEADER_ID
390 ,UPDATE_FLAG
391 ,DEMAND_CLASS_CODE
392 ,RESERVABLE_FLAG
393 ,TRANSACTABLE_FLAG
394 ,LATEST_ACCEPTABLE_DATE
395 ,DPW_ASSIGNED_FLAG
396 ,DELIVERY_ID
397 ,DEPARTURE_ID
398 ,LOAD_SEQ_NUMBER
399 ,MASTER_CONTAINER_ITEM_ID
400 ,DETAIL_CONTAINER_ITEM_ID
401 ,decode(X_Mode,'REMAINDER', NULL,TRANSACTION_TEMP_ID)
402 ,PICK_SLIP_NUMBER
403 ,decode(X_Mode,'REMAINDER', NULL,CONTAINER_ID)
404 ,MVT_STAT_STATUS
405 FROM SO_PICKING_LINE_DETAILS WHERE
406 PICKING_LINE_DETAIL_ID = X_Parent_Detail_Id ;
407
408 EXCEPTION
409 WHEN NO_DATA_FOUND THEN
410 -- Replace this with real error? How to reflect in form?
411 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
412 FND_MESSAGE.Set_Token('PACKAGE','WSH_SC_PLD_PKG.Insert_Details');
413 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
414 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
415 APP_EXCEPTION.Raise_Exception;
416 WHEN OTHERS THEN
417 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
418 FND_MESSAGE.Set_Token('PACKAGE','WSH_SC_PLD_PKG.Insert_Details');
419 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
420 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
421 APP_EXCEPTION.Raise_Exception;
422
423 END;
424
425
426 --
427 -- Name
428 -- Create_Remainders
429 -- Purpose
430 -- To create a new detail for the remaining quantity when a partial quantity
431 -- has been shipped for a reserved picking line.
432 --
433 -- Arguments
434 -- X_Pick_Line_Detail_Id IN NUMBER
435 -- X_New_Requested IN NUMBER
436 --
437 -- Notes
438 --
439
440 PROCEDURE Create_Remainders(X_Picking_Line_Detail_Id NUMBER,
441 X_New_Requested NUMBER ) IS
442 BEGIN
443
444 -- insert the remainder detail in so_picking_line_details
445 -- shipped quantity will be 0 since this remainder detail
446 -- is for the unshipped quantity
447 Insert_Details( NULL,
448 X_Picking_Line_Detail_Id,
449 0,
450 X_New_Requested,
451 NULL ,
452 'REMAINDER') ;
453
454 --now update the requested quantity on the original picking_line_detail
455 UPDATE so_picking_line_details pld
456 SET pld.requested_quantity = pld.shipped_quantity
457 WHERE pld.picking_line_detail_id = X_Picking_Line_Detail_Id;
458
459 EXCEPTION
460 WHEN NO_DATA_FOUND THEN
461 RETURN;
462 WHEN OTHERS THEN
463 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
464 FND_MESSAGE.Set_Token('PACKAGE','WSH_SC_PLD_PKG.Create_Remainders');
465 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
466 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
467 APP_EXCEPTION.Raise_Exception;
468 END;
469
470
471 --
472 -- Name
473 -- Explode_Lines
474 -- Purpose
475 -- Takes individual lines from MTL_SERIAL_NUMBERS_TEMP that
476 -- are under serial number control and explodes them into multiple
477 -- lines based on the serial numbers entered.
478 -- Arguments
479 -- X_Picking_Line_Detail_Id for which is under Serial number
480 -- control and hence must do the explosion
481 --
482 -- Notes
483 -- Assumptions: The package assumes that it will be called when
484 -- there is one line in SO_PICKING_LINE_DETAILS
485 -- for every line in MTL_SERIAL_NUMBERS_TEMP.
486 --
487
488
489 PROCEDURE Explode_Lines( X_Picking_Line_Detail_Id IN NUMBER)
490 IS
491 -- Cursor to get the serial line detail information that can be
492 -- extracted from MSNT to be stored in SOPLD
493
494 CURSOR get_picking_line_detail(pk_line_detail_id NUMBER) IS
495 SELECT msnt.transaction_temp_id,
496 pld.picking_line_detail_id,
497 msnt.vendor_serial_number,
498 msnt.fm_serial_number,
499 msnt.to_serial_number,
500 msnt.serial_prefix
501 FROM mtl_serial_numbers_temp msnt,
502 so_picking_line_details pld
503 WHERE pld.picking_line_detail_id = pk_line_detail_id
504 AND pld.transaction_temp_id = msnt.transaction_temp_id(+)
505 ORDER BY msnt.transaction_temp_id;
506
507 I NUMBER;
508 X_Serial_Control_Code NUMBER;
509 X_transaction_temp_id NUMBER;
510 X_transaction_temp_id_old NUMBER;
511 X_trx_source_line_id NUMBER;
512 X_serial_number VARCHAR2(30);
513 X_fm_serial_number VARCHAR2(30);
514 X_to_serial_number VARCHAR2(30);
515 X_serial_qty VARCHAR2(30);
516 X_Serial_Current VARCHAR2(30);
517 X_Serial_Prefix VARCHAR2(30);
518 X_Serial_Numeric_Temp VARCHAR2(30);
519 X_Serial_Numeric NUMBER;
520 X_Serial_Numeric_Len NUMBER;
521 X_can_update NUMBER;
522 X_loop_end NUMBER;
523
524 BEGIN
525 X_Transaction_temp_id_old := -1;
526 -- Retrieve line detail info from MSNT using picking line_detail
527 OPEN get_picking_line_detail(X_Picking_Line_Detail_Id);
528 LOOP
529 FETCH get_picking_line_detail INTO
530 X_transaction_temp_id,
531 X_trx_source_line_id,
532 X_serial_number,
533 X_fm_serial_number,
534 X_to_serial_number,
535 X_serial_qty;
536 EXIT WHEN get_picking_line_detail%NOTFOUND;
537 IF (X_serial_number IS NULL) THEN
538 IF (X_transaction_temp_id <> X_transaction_temp_id_old) THEN
539 X_can_update := 1;
540 X_transaction_temp_id_old := X_transaction_temp_id;
541 END IF;
542
543 -- Determine the serial number prefix
544 X_Serial_Prefix := rtrim(X_fm_serial_number, '0123456789');
545 -- Determine the base numeric portion
549 -- Determine length of numeric portion
546 X_Serial_Numeric := to_number(substr(X_fm_serial_number,
547 nvl(length(X_Serial_Prefix),0) + 1));
548
550 X_Serial_Numeric_Len := length(substr(X_fm_serial_number,
551 nvl(length(X_Serial_Prefix),0) + 1));
552
553 -- Generate serial numbers to be inserted
554 -- Get the first serial number
555 X_Serial_Current := X_fm_serial_number;
556 X_Serial_Numeric := X_Serial_Numeric - 1;
557 -- Update first picking line detail
558 X_loop_end := to_number(X_serial_qty);
559 IF (X_can_update = 1) THEN
560 Update_Details(X_trx_source_line_id,
561 1,
562 1,
563 X_Serial_Current);
564 X_loop_end := to_number(X_serial_qty) - 1;
565 X_can_update := 0;
566 X_Serial_Numeric := X_Serial_Numeric + 1;
567 END IF;
568 -- Insert the rest of the line details
569 FOR I IN 1..X_loop_end LOOP
570 -- Determine next serial number
571 X_Serial_Current := Next_Serial(X_Serial_Prefix,
572 X_Serial_Numeric_Len,
573 X_Serial_Numeric);
574 X_Serial_Numeric := X_Serial_Numeric + 1;
575
576 Insert_Details(NULL,
577 X_trx_source_line_id,
578 1,
579 1,
580 X_Serial_Current,
581 'NEW');
582
583 END LOOP;
584 END IF;
585 END LOOP;
586 CLOSE get_picking_line_detail;
587
588 EXCEPTION
589 WHEN OTHERS THEN
590 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
591 FND_MESSAGE.Set_Token('PACKAGE','WSH_SC_PDB_PKG.Explode_lines');
592 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
593 APP_EXCEPTION.Raise_Exception;
594
595 END Explode_Lines;
596
597 --
598 -- Name
599 -- Next_Serial
600 -- Purpose
601 -- Takes a serial prefix, the length of the numeric portion of a serial
602 -- number and the current value of the numeric portion and returns the
603 -- next serial number.
604 -- Arguments
605 -- s_prefix is the serial number prefix
606 -- s_num_length is the size of the numeric portion of the serial number
607 -- s_num_current is the current vlaue of the numeric portion
608 --
609 -- Notes
610 -- Uses the following algorithm:
611 -- X_Serial_Current := X_Serial_Prefix || lpad('000000000000000000000000000000',
612 -- X_Serial_Numeric_Len -
613 -- length(to_char(X_Serial_Numeric + I))) ||
614 -- to_char(X_Serial_Numeric + I)
615 --
616 --
617
618
619 FUNCTION Next_Serial (s_prefix IN VARCHAR2,
620 s_num_length IN NUMBER,
621 s_num_current IN NUMBER
622 )
623 RETURN VARCHAR2 IS
624 X_new_serial VARCHAR2(30);
625 BEGIN
626 select s_prefix || lpad('000000000000000000000000000000',
627 s_num_length - length(to_char(s_num_current + 1)))
628 || to_char(s_num_current + 1)
629 into X_new_serial
630 from dual;
631
632 return(X_new_serial);
633
634 END Next_Serial;
635
636 --
637 -- Name
638 -- Delete_From_Msnt
639 -- Purpose
640 -- To Delete the temporary records created in MSNT by the Serial Number Entry Form
641 --
642 -- Arguments
643 -- X_Delivery_Id IN NUMBER
644 --
645 -- Notes
646 --
647
648 PROCEDURE Delete_From_Msnt(X_Delivery_Id NUMBER ) IS
649
650 BEGIN
651
652 DELETE FROM mtl_serial_numbers_temp
653 WHERE transaction_temp_id in
654 (SELECT transaction_temp_id
655 FROM so_picking_line_details
656 WHERE delivery_id = X_Delivery_Id );
657
658 EXCEPTION
659 WHEN NO_DATA_FOUND THEN
660 RETURN;
661 WHEN OTHERS THEN
662 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
663 FND_MESSAGE.Set_Token('PACKAGE','WSH_SC_PLD_PKG.Delete_From_Msnt');
664 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
665 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
666 APP_EXCEPTION.Raise_Exception;
667 END;
668 END WSH_SC_PLD_PKG;
[Home] [Help]