[Home] [Help]
PACKAGE BODY: APPS.GMI_XFER_MST_PKG
Source
1 PACKAGE BODY GMI_XFER_MST_PKG AS
2 /*$Header: GMIXFERB.pls 115.2 2002/10/25 14:51:37 jdiiorio noship $*/
3 /*
4 **
5 **
6 **
7 */
8 /*###############################################################
9 # NAME
10 # insert_row
11 # SYNOPSIS
12 # proc insert_row
13 # DESCRIPTION
14 # This particular procedure is used to insert the values into
15 # the ic_xfer_mst table.
16 #################################################################*/
17
18 PROCEDURE insert_row (p_commit IN VARCHAR2,
19 p_called_by_form IN VARCHAR2,
20 p_transfer_id IN NUMBER,
21 p_transfer_no IN VARCHAR2,
22 p_transfer_batch IN VARCHAR2,
23 p_orgn_code IN VARCHAR2,
24 p_transfer_status IN VARCHAR2,
25 p_item_id IN NUMBER,
26 p_lot_id IN NUMBER,
27 p_lot_status IN VARCHAR2,
28 p_release_reason_code IN VARCHAR2,
29 p_receive_reason_code IN VARCHAR2,
30 p_cancel_reason_code IN VARCHAR2,
31 p_from_warehouse IN VARCHAR2,
32 p_from_location IN VARCHAR2,
33 p_to_warehouse IN VARCHAR2,
34 p_to_location IN VARCHAR2,
35 p_release_quantity1 IN NUMBER,
36 p_release_quantity2 IN NUMBER,
37 p_release_uom1 IN VARCHAR2,
38 p_release_uom2 IN VARCHAR2,
39 p_receive_quantity1 IN NUMBER,
40 p_receive_quantity2 IN NUMBER,
41 p_scheduled_release_date IN DATE,
42 p_actual_release_date IN DATE,
43 p_scheduled_receive_date IN DATE,
44 p_actual_receive_date IN DATE,
45 p_cancel_date IN DATE,
46 p_delete_mark IN NUMBER,
47 p_received_by IN NUMBER,
48 p_released_by IN NUMBER,
49 p_canceled_by IN NUMBER,
50 p_text_code IN NUMBER,
51 p_comments IN VARCHAR2,
52 p_attribute_category IN VARCHAR2,
53 p_attribute1 IN VARCHAR2,
54 p_attribute2 IN VARCHAR2,
55 p_attribute3 IN VARCHAR2,
56 p_attribute4 IN VARCHAR2,
57 p_attribute5 IN VARCHAR2,
58 p_attribute6 IN VARCHAR2,
59 p_attribute7 IN VARCHAR2,
60 p_attribute8 IN VARCHAR2,
61 p_attribute9 IN VARCHAR2,
62 p_attribute10 IN VARCHAR2,
63 p_attribute11 IN VARCHAR2,
64 p_attribute12 IN VARCHAR2,
65 p_attribute13 IN VARCHAR2,
66 p_attribute14 IN VARCHAR2,
67 p_attribute15 IN VARCHAR2,
68 p_attribute16 IN VARCHAR2,
69 p_attribute17 IN VARCHAR2,
70 p_attribute18 IN VARCHAR2,
71 p_attribute19 IN VARCHAR2,
72 p_attribute20 IN VARCHAR2,
73 p_attribute21 IN VARCHAR2,
74 p_attribute22 IN VARCHAR2,
75 p_attribute23 IN VARCHAR2,
76 p_attribute24 IN VARCHAR2,
77 p_attribute25 IN VARCHAR2,
78 p_attribute26 IN VARCHAR2,
79 p_attribute27 IN VARCHAR2,
80 p_attribute28 IN VARCHAR2,
81 p_attribute29 IN VARCHAR2,
82 p_attribute30 IN VARCHAR2,
83 p_created_by IN NUMBER,
84 p_creation_date IN DATE,
85 p_last_updated_by IN NUMBER,
86 p_last_update_date IN DATE,
87 p_last_update_login IN NUMBER,
88 x_rowid OUT NOCOPY VARCHAR2,
89 x_return_status OUT NOCOPY VARCHAR2,
90 x_oracle_error OUT NOCOPY NUMBER,
91 x_msg_data OUT NOCOPY VARCHAR2)
92 IS
93
94 /* Local Variables */
95
96 l_return_status VARCHAR2(1) := 'S';
97 l_key_exists VARCHAR2(1);
98 l_msg_data VARCHAR2(2000);
99 l_rowid VARCHAR2(18);
100
101 l_oracle_error NUMBER;
102
103 /* Exceptions */
104
105 FOREIGN_KEY_ERROR EXCEPTION;
106 TRANSFER_EXISTS_ERROR EXCEPTION;
107 ROW_MISSING_ERROR EXCEPTION;
108
109 /* Declare cursors */
110
111 BEGIN
112
113 /* Initialization Routine */
114
115 SAVEPOINT Insert_Row;
116 x_return_status := 'S';
117 x_oracle_error := 0;
118 x_msg_data := NULL;
119
120 /* Now call the check foreign key procedure */
121
122 check_foreign_keys (p_orgn_code,
123 p_item_id,
124 p_lot_id,
125 p_lot_status,
126 p_release_reason_code,
127 p_receive_reason_code,
128 p_cancel_reason_code,
129 p_from_warehouse,
130 p_from_location,
131 p_to_warehouse,
132 p_to_location,
133 p_received_by,
134 p_released_by,
135 p_canceled_by,
136 l_return_status,
137 l_oracle_error,
138 l_msg_data);
139
140 IF l_return_status <> 'S' THEN
141 RAISE Foreign_Key_Error;
142 END IF;
143
144 /* Now check the primary key doesn't already exist */
145
146 Check_Primary_Key (p_orgn_code,
147 p_transfer_no,
148 'F',
149 l_rowid,
150 l_key_exists);
151
152 IF FND_API.To_Boolean(l_key_exists) THEN
153 RAISE Transfer_Exists_Error;
154 END IF;
155
156 INSERT INTO ic_xfer_mst
157 (transfer_id,
158 transfer_no,
159 transfer_batch,
160 orgn_code,
161 transfer_status,
162 item_id,
163 lot_id,
164 lot_status,
165 release_reason_code,
166 receive_reason_code,
167 cancel_reason_code,
168 from_warehouse,
169 from_location,
170 to_warehouse,
171 to_location,
172 release_quantity1,
173 release_quantity2,
174 release_uom1,
175 release_uom2,
176 receive_quantity1,
177 receive_quantity2,
178 scheduled_release_date,
179 actual_release_date,
180 scheduled_receive_date,
181 actual_receive_date,
182 cancel_date,
183 delete_mark,
184 received_by,
185 released_by,
186 canceled_by,
187 text_code,
188 comments,
189 attribute_category ,
190 attribute1 ,
191 attribute2 ,
192 attribute3 ,
193 attribute4 ,
194 attribute5 ,
195 attribute6 ,
196 attribute7 ,
197 attribute8 ,
198 attribute9 ,
199 attribute10 ,
200 attribute11 ,
201 attribute12 ,
202 attribute13 ,
203 attribute14 ,
204 attribute15 ,
205 attribute16 ,
206 attribute17 ,
207 attribute18 ,
208 attribute19 ,
209 attribute20 ,
210 attribute21 ,
211 attribute22 ,
212 attribute23 ,
213 attribute24 ,
214 attribute25 ,
215 attribute26 ,
216 attribute27 ,
217 attribute28 ,
218 attribute29 ,
219 attribute30 ,
220 created_by ,
221 creation_date ,
222 last_updated_by ,
223 last_update_date ,
224 last_update_login)
225 VALUES
226 (p_transfer_id ,
227 p_transfer_no ,
228 p_transfer_batch ,
229 p_orgn_code ,
230 p_transfer_status ,
231 p_item_id ,
232 p_lot_id ,
233 p_lot_status ,
234 p_release_reason_code ,
235 p_receive_reason_code ,
236 p_cancel_reason_code ,
237 p_from_warehouse ,
238 p_from_location ,
239 p_to_warehouse ,
240 p_to_location ,
241 p_release_quantity1 ,
242 p_release_quantity2 ,
243 p_release_uom1,
244 p_release_uom2,
245 p_receive_quantity1,
246 p_receive_quantity2,
247 p_scheduled_release_date ,
248 p_actual_release_date ,
249 p_scheduled_receive_date ,
250 p_actual_receive_date ,
251 p_cancel_date ,
252 p_delete_mark ,
253 p_received_by ,
254 p_released_by ,
255 p_canceled_by ,
256 p_text_code ,
257 p_comments ,
258 p_attribute_category ,
259 p_attribute1 ,
260 p_attribute2 ,
261 p_attribute3 ,
262 p_attribute4 ,
263 p_attribute5 ,
264 p_attribute6 ,
265 p_attribute7 ,
266 p_attribute8 ,
267 p_attribute9 ,
268 p_attribute10 ,
269 p_attribute11 ,
270 p_attribute12 ,
271 p_attribute13 ,
272 p_attribute14 ,
273 p_attribute15 ,
274 p_attribute16 ,
275 p_attribute17 ,
276 p_attribute18 ,
277 p_attribute19 ,
278 p_attribute20 ,
279 p_attribute21 ,
280 p_attribute22 ,
281 p_attribute23 ,
282 p_attribute24 ,
283 p_attribute25 ,
284 p_attribute26 ,
285 p_attribute27 ,
286 p_attribute28 ,
287 p_attribute29 ,
288 p_attribute30 ,
289 p_created_by ,
290 p_creation_date ,
291 p_last_updated_by ,
292 p_last_update_date ,
293 p_last_update_login);
294
295 /* Now get the row id of the inserted record */
296
297 Check_Primary_Key
298 (p_orgn_code,
299 p_transfer_no,
300 'F',
301 l_rowid,
302 l_key_exists);
303
304 IF FND_API.To_Boolean(l_key_exists) THEN
305 x_rowid := l_rowid;
306 ELSE
307 RAISE Row_Missing_Error;
308 END IF;
309
310 /* Check the commit flag and if set, then commit the work. */
311
312 IF FND_API.To_Boolean(p_commit) THEN
313 COMMIT WORK;
314 END IF;
315 EXCEPTION
316 WHEN Foreign_Key_Error THEN
317 ROLLBACK TO SAVEPOINT Insert_Row;
318 x_return_status := l_return_status;
319 x_oracle_error := l_oracle_error;
320 FND_MESSAGE.SET_NAME('GMI', 'IC_FOREIGN_KEY_ERROR');
321 FND_MESSAGE.SET_TOKEN('TEXT', l_msg_data);
322 IF FND_API.To_Boolean(p_called_by_form) THEN
323 APP_EXCEPTION.Raise_Exception;
324 ELSE
325 x_msg_data := FND_MESSAGE.Get;
326 END IF;
327 WHEN Transfer_Exists_Error THEN
328 ROLLBACK TO SAVEPOINT Insert_Row;
329 x_return_status := 'E';
330 x_oracle_error := l_oracle_error;
331 FND_MESSAGE.SET_NAME('GMA', 'SY_DUPKEYINSERT');
332 IF FND_API.To_Boolean(p_called_by_form) THEN
333 APP_EXCEPTION.Raise_Exception;
334 ELSE
335 x_msg_data := FND_MESSAGE.Get;
336 END IF;
337 WHEN Row_Missing_Error THEN
338 ROLLBACK TO SAVEPOINT Insert_Row;
339 x_return_status := 'E';
340 x_oracle_error := l_oracle_error;
341 FND_MESSAGE.SET_NAME('GMI', 'IC_NO_RECORD_INSERTED');
342 FND_MESSAGE.SET_TOKEN('CODE', p_orgn_code||' '||p_transfer_no);
343 IF FND_API.To_Boolean(p_called_by_form) THEN
344 APP_EXCEPTION.Raise_Exception;
345 ELSE
346 x_msg_data := FND_MESSAGE.Get;
347 END IF;
348 WHEN OTHERS THEN
349 ROLLBACK TO SAVEPOINT Insert_Row;
350 x_return_status := 'U';
351 x_oracle_error := l_oracle_error;
352 l_msg_data := sqlerrm;
353 FND_MESSAGE.SET_NAME('GMI', 'IC_UNEXPECTED_ERROR');
354 FND_MESSAGE.SET_TOKEN('TEXT', l_msg_data);
355 IF FND_API.To_Boolean(p_called_by_form) THEN
356 APP_EXCEPTION.Raise_Exception;
357 ELSE
358 x_msg_data := FND_MESSAGE.Get;
359 END IF;
360 END Insert_Row;
361
362 /*###############################################################
363 # NAME
364 # update_row
365 # SYNOPSIS
366 # proc update_row
367 # DESCRIPTION
368 # This particular procedure is used to update the values into
369 # the ic_xfer_mst table.
370 #################################################################*/
371
372 PROCEDURE update_row (p_commit IN VARCHAR2,
373 p_called_by_form IN VARCHAR2,
374 p_rowid IN VARCHAR2,
375 p_transfer_id IN NUMBER,
376 p_transfer_no IN VARCHAR2,
377 p_transfer_batch IN VARCHAR2,
378 p_orgn_code IN VARCHAR2,
379 p_transfer_status IN VARCHAR2,
380 p_item_id IN NUMBER,
381 p_lot_id IN NUMBER,
382 p_lot_status IN VARCHAR2,
383 p_release_reason_code IN VARCHAR2,
384 p_receive_reason_code IN VARCHAR2,
385 p_cancel_reason_code IN VARCHAR2,
386 p_from_warehouse IN VARCHAR2,
387 p_from_location IN VARCHAR2,
388 p_to_warehouse IN VARCHAR2,
389 p_to_location IN VARCHAR2,
390 p_release_quantity1 IN NUMBER,
391 p_release_quantity2 IN NUMBER,
392 p_release_uom1 IN VARCHAR2,
393 p_release_uom2 IN VARCHAR2,
394 p_receive_quantity1 IN NUMBER,
395 p_receive_quantity2 IN NUMBER,
396 p_scheduled_release_date IN DATE,
397 p_actual_release_date IN DATE,
398 p_scheduled_receive_date IN DATE,
399 p_actual_receive_date IN DATE,
400 p_cancel_date IN DATE,
401 p_delete_mark IN NUMBER,
402 p_received_by IN NUMBER,
403 p_released_by IN NUMBER,
404 p_canceled_by IN NUMBER,
405 p_text_code IN NUMBER,
406 p_comments IN VARCHAR2,
407 p_attribute_category IN VARCHAR2,
408 p_attribute1 IN VARCHAR2,
409 p_attribute2 IN VARCHAR2,
410 p_attribute3 IN VARCHAR2,
411 p_attribute4 IN VARCHAR2,
412 p_attribute5 IN VARCHAR2,
413 p_attribute6 IN VARCHAR2,
414 p_attribute7 IN VARCHAR2,
415 p_attribute8 IN VARCHAR2,
416 p_attribute9 IN VARCHAR2,
417 p_attribute10 IN VARCHAR2,
418 p_attribute11 IN VARCHAR2,
419 p_attribute12 IN VARCHAR2,
420 p_attribute13 IN VARCHAR2,
421 p_attribute14 IN VARCHAR2,
422 p_attribute15 IN VARCHAR2,
423 p_attribute16 IN VARCHAR2,
424 p_attribute17 IN VARCHAR2,
425 p_attribute18 IN VARCHAR2,
426 p_attribute19 IN VARCHAR2,
427 p_attribute20 IN VARCHAR2,
428 p_attribute21 IN VARCHAR2,
429 p_attribute22 IN VARCHAR2,
430 p_attribute23 IN VARCHAR2,
431 p_attribute24 IN VARCHAR2,
432 p_attribute25 IN VARCHAR2,
433 p_attribute26 IN VARCHAR2,
434 p_attribute27 IN VARCHAR2,
435 p_attribute28 IN VARCHAR2,
436 p_attribute29 IN VARCHAR2,
437 p_attribute30 IN VARCHAR2,
438 p_created_by IN NUMBER,
439 p_creation_date IN DATE,
440 p_last_updated_by IN NUMBER,
441 p_last_update_date IN DATE,
442 p_last_update_login IN NUMBER,
443 x_return_status OUT NOCOPY VARCHAR2,
444 x_oracle_error OUT NOCOPY NUMBER,
445 x_msg_data OUT NOCOPY VARCHAR2)
446 IS
447 /* Local Variables */
448
449 l_return_status VARCHAR2(1) := 'S';
450 l_msg_data VARCHAR2(2000);
451 l_oracle_error NUMBER;
452
453 /* Exceptions */
454
455 FOREIGN_KEY_ERROR EXCEPTION;
456 ROW_MISSING_ERROR EXCEPTION;
457
458 BEGIN
459
460 /* Initialization Routine */
461
462 SAVEPOINT Update_Row;
463 X_return_status := 'S';
464 X_oracle_error := 0;
465 X_msg_data := NULL;
466
467 /* Now call the check foreign key procedure */
468
469 check_foreign_keys (p_orgn_code,
470 p_item_id,
471 p_lot_id,
472 p_lot_status,
473 p_release_reason_code,
474 p_receive_reason_code,
475 p_cancel_reason_code,
476 p_from_warehouse,
477 p_from_location,
478 p_to_warehouse,
479 p_to_location,
480 p_received_by,
481 p_released_by,
482 p_canceled_by,
483 l_return_status,
484 l_oracle_error,
485 l_msg_data);
486
487 IF l_return_status <> 'S' THEN
488 RAISE Foreign_Key_Error;
489 END IF;
490
491 UPDATE ic_xfer_mst
492 SET transfer_status = p_transfer_status,
493 transfer_batch = p_transfer_batch,
494 item_id = p_item_id,
495 lot_id = p_lot_id,
496 lot_status = p_lot_status,
497 release_reason_code = p_release_reason_code,
498 receive_reason_code = p_receive_reason_code,
499 cancel_reason_code = p_cancel_reason_code,
500 from_warehouse = p_from_warehouse,
501 from_location = p_from_location,
502 to_warehouse = p_to_warehouse,
503 to_location = p_to_location,
504 release_quantity1 = p_release_quantity1,
505 release_quantity2 = p_release_quantity2,
506 release_uom1 = p_release_uom1,
507 release_uom2 = p_release_uom2,
508 receive_quantity1 = p_receive_quantity1,
509 receive_quantity2 = p_receive_quantity2,
510 scheduled_release_date = p_scheduled_release_date,
511 actual_release_date = p_actual_release_date,
512 scheduled_receive_date = p_scheduled_receive_date,
513 actual_receive_date = p_actual_receive_date,
514 cancel_date = p_cancel_date,
515 delete_mark = p_delete_mark,
516 received_by = p_received_by,
517 released_by = p_released_by,
518 canceled_by = p_canceled_by,
519 text_code = p_text_code,
520 comments = p_comments,
521 attribute_category = p_attribute_category,
522 attribute1 = p_attribute1,
523 attribute2 = p_attribute2,
524 attribute3 = p_attribute3,
525 attribute4 = p_attribute4,
526 attribute5 = p_attribute5,
527 attribute6 = p_attribute6,
528 attribute7 = p_attribute7,
529 attribute8 = p_attribute8,
530 attribute9 = p_attribute9,
531 attribute10 = p_attribute10,
532 attribute11 = p_attribute11,
533 attribute12 = p_attribute12,
534 attribute13 = p_attribute13,
535 attribute14 = p_attribute14,
536 attribute15 = p_attribute15,
537 attribute16 = p_attribute16,
538 attribute17 = p_attribute17,
539 attribute18 = p_attribute18,
540 attribute19 = p_attribute19,
541 attribute20 = p_attribute20,
542 attribute21 = p_attribute21,
543 attribute22 = p_attribute22,
544 attribute23 = p_attribute23,
545 attribute24 = p_attribute24,
546 attribute25 = p_attribute25,
547 attribute26 = p_attribute26,
548 attribute27 = p_attribute27,
549 attribute28 = p_attribute28,
550 attribute29 = p_attribute29,
551 attribute30 = p_attribute30,
552 created_by = p_created_by,
553 creation_date = p_creation_date,
554 last_updated_by = p_last_updated_by,
555 last_update_date = p_last_update_date,
556 last_update_login = p_last_update_login
557 WHERE rowid = p_rowid;
558 IF SQL%NOTFOUND THEN
559 RAISE Row_Missing_Error;
560 END IF;
561
562 /* Check the commit flag and if set, then commit the work. */
563 IF FND_API.To_Boolean(p_commit) THEN
564 COMMIT WORK;
565 END IF;
566
567 EXCEPTION
568
569 WHEN Foreign_Key_Error THEN
570 ROLLBACK TO SAVEPOINT Update_Row;
571 x_return_status := l_return_status;
572 x_oracle_error := l_oracle_error;
573 FND_MESSAGE.SET_NAME('GMI', 'IC_FOREIGN_KEY_ERROR');
574 FND_MESSAGE.SET_TOKEN('TEXT', l_msg_data);
575 IF FND_API.To_Boolean(p_called_by_form) THEN
576 APP_EXCEPTION.Raise_Exception;
577 ELSE
578 x_msg_data := FND_MESSAGE.Get;
579 END IF;
580 WHEN Row_Missing_Error THEN
581 ROLLBACK TO SAVEPOINT Update_Row;
582 x_return_status := 'E';
583 x_oracle_error := l_oracle_error;
584 FND_MESSAGE.SET_NAME('GMI',
585 'IC_RECORD_MISSING');
586 FND_MESSAGE.SET_TOKEN('CODE', p_transfer_no);
587 IF FND_API.To_Boolean(p_called_by_form) THEN
588 APP_EXCEPTION.Raise_Exception;
589 ELSE
590 x_msg_data := FND_MESSAGE.Get;
591 END IF;
592 WHEN OTHERS THEN
593 ROLLBACK TO SAVEPOINT Update_Row;
594 x_return_status := 'U';
595 x_oracle_error := l_oracle_error;
596 l_msg_data := sqlerrm;
597 FND_MESSAGE.SET_NAME('GMI', 'IC_UNEXPECTED_ERROR');
598 FND_MESSAGE.SET_TOKEN('TEXT', l_msg_data);
599 IF FND_API.To_Boolean(p_called_by_form) THEN
600 APP_EXCEPTION.Raise_Exception;
601 ELSE
602 x_msg_data := FND_MESSAGE.Get;
603 END IF;
604 END Update_Row;
605
606 /*###############################################################
607 # NAME
608 # lock_row
609 # SYNOPSIS
610 # proc lock_row
611 # DESCRIPTION
612 # This particular procedure is used to lock the corresponding
613 # row in the ic_xfer_mst table.
614 #################################################################*/
615
616 PROCEDURE Lock_Row (p_called_by_form IN VARCHAR2,
617 p_rowid IN VARCHAR2,
618 p_last_update_date IN DATE,
619 x_return_status OUT NOCOPY VARCHAR2,
620 x_oracle_error OUT NOCOPY NUMBER,
621 x_msg_data OUT NOCOPY VARCHAR2)
622 IS
623
624 /* Alpha Variables */
625
626 L_RETURN_STATUS VARCHAR2(1) := 'S';
627 L_MSG_DATA VARCHAR2(2000);
628
629 /* Number Variables */
630
631 L_ORACLE_ERROR NUMBER;
632
633 /* Exceptions */
634
635 NO_DATA_FOUND_ERROR EXCEPTION;
636 RECORD_CHANGED_ERROR EXCEPTION;
637
638 /* Define the cursors */
639
640 CURSOR c_lock_transfer IS
641 SELECT last_update_date
642 FROM ic_xfer_mst
643 WHERE rowid = p_rowid
644 FOR UPDATE OF last_update_date NOWAIT;
645
646 LockTransferRcd c_lock_transfer%ROWTYPE;
647
648 BEGIN
649
650 /* Initialization Routine */
651
652 SAVEPOINT Lock_Row;
653 X_return_status := 'S';
654 X_oracle_error := 0;
655 X_msg_data := NULL;
656
657 /* Now lock the record */
658
659 OPEN c_lock_transfer;
660 FETCH c_lock_transfer INTO LockTransferRcd;
661 IF c_lock_transfer%NOTFOUND THEN
662 CLOSE c_lock_transfer;
663 RAISE No_Data_Found_Error;
664 END IF;
665 CLOSE c_lock_transfer;
666
667 IF LockTransferRcd.last_update_date <> p_last_update_date THEN
668 RAISE RECORD_CHANGED_ERROR;
669 END IF;
670 EXCEPTION
671 WHEN No_Data_Found_Error THEN
672 ROLLBACK TO SAVEPOINT Lock_Row;
673 X_return_status := 'E';
674 FND_MESSAGE.SET_NAME('GMI', 'IC_RECORD_NOT_FOUND');
675 FND_MESSAGE.SET_TOKEN('CODE',p_rowid,FALSE);
676 IF FND_API.To_Boolean(p_called_by_form) THEN
677 APP_EXCEPTION.Raise_Exception;
678 ELSE
679 X_msg_data := FND_MESSAGE.Get;
680 END IF;
681 WHEN RECORD_CHANGED_ERROR THEN
682 ROLLBACK TO SAVEPOINT Lock_Row;
683 X_return_status := 'E';
684 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_CHANGED');
685 IF FND_API.To_Boolean(p_called_by_form) THEN
686 APP_EXCEPTION.Raise_Exception;
687 ELSE
688 X_msg_data := FND_MESSAGE.Get;
689 END IF;
690 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
691 ROLLBACK TO SAVEPOINT Lock_Row;
692 X_return_status := 'L';
693 X_oracle_error := APP_EXCEPTION.Get_Code;
694 IF NOT (FND_API.To_Boolean(p_called_by_form)) THEN
695 FND_MESSAGE.SET_NAME('GMI', 'IC_ROW_IS_LOCKED');
696 X_msg_data := FND_MESSAGE.Get;
697 END IF;
698 WHEN OTHERS THEN
699 ROLLBACK TO SAVEPOINT Lock_Row;
700 X_return_status := 'U';
701 X_oracle_error := APP_EXCEPTION.Get_Code;
702 l_msg_data := APP_EXCEPTION.Get_Text;
703 FND_MESSAGE.SET_NAME('GMI', 'IC_UNEXPECTED_ERROR');
704 FND_MESSAGE.SET_TOKEN('TEXT', l_msg_data);
705 IF FND_API.To_Boolean(p_called_by_form) THEN
706 APP_EXCEPTION.Raise_Exception;
707 ELSE
708 X_msg_data := FND_MESSAGE.Get;
709 END IF;
710 END Lock_Row;
711
712 /*###############################################################
713 # NAME
714 # check_foreign_keys
715 # SYNOPSIS
716 # proc check_foreign_keys
717 # DESCRIPTION
718 # This particular procedure is used to check the existense
719 # of the foreign keys in their parent table.
720 #################################################################*/
721
722 PROCEDURE check_foreign_keys
723 (p_orgn_code IN VARCHAR2,
724 p_item_id IN NUMBER,
725 p_lot_id IN NUMBER,
726 p_lot_status IN VARCHAR2,
727 p_release_reason_code IN VARCHAR2,
728 p_receive_reason_code IN VARCHAR2,
729 p_cancel_reason_code IN VARCHAR2,
730 p_from_warehouse IN VARCHAR2,
731 p_from_location IN VARCHAR2,
732 p_to_warehouse IN VARCHAR2,
733 p_to_location IN VARCHAR2,
734 p_received_by IN NUMBER,
735 p_released_by IN NUMBER,
736 p_canceled_by IN NUMBER,
737 x_return_status OUT NOCOPY VARCHAR2,
738 x_oracle_error OUT NOCOPY NUMBER,
739 x_msg_data OUT NOCOPY VARCHAR2)
740 IS
741
742 /* Local Variables */
743
744 l_return_status VARCHAR2(1) := 'S';
745 l_msg_data VARCHAR2(2000);
746 l_rowid VARCHAR2(18);
747 l_key_exists VARCHAR2(1);
748
749 l_oracle_error NUMBER;
750
751 /* Define the cursors */
752
753 CURSOR Cur_get_orgn_code IS
754 SELECT orgn_code
755 FROM sy_orgn_mst
756 WHERE orgn_code = p_orgn_code
757 AND delete_mark = 0;
758 OrgnRecord Cur_get_orgn_code%ROWTYPE;
759
760 BEGIN
761
762 /* Initialization Routine */
763
764 SAVEPOINT check_foreign_keys;
765 x_return_status := 'S';
766 x_oracle_error := 0;
767 x_msg_data := NULL;
768
769 /* Organization Code */
770
771 IF p_orgn_code IS NOT NULL THEN
772 OPEN Cur_get_orgn_code;
773 FETCH Cur_get_orgn_code INTO OrgnRecord;
774 IF Cur_get_orgn_code%NOTFOUND THEN
775 x_return_status := 'E';
776 FND_MESSAGE.SET_NAME('GMI', 'IC_RECORD_NOT_FOUND');
777 FND_MESSAGE.SET_TOKEN('CODE', p_orgn_code);
778 l_msg_data := l_msg_data || ' ' || FND_MESSAGE.Get;
779 END IF;
780 CLOSE Cur_get_orgn_code;
781 END IF;
782
783
784 IF x_return_status <> 'S' THEN
785 x_msg_data := l_msg_data;
786 END IF;
787 EXCEPTION
788 WHEN OTHERS THEN
789 ROLLBACK TO SAVEPOINT check_foreign_keys;
790 x_return_status := 'U';
791 l_msg_data := sqlerrm;
792 FND_MESSAGE.SET_NAME('GMI', 'IC_UNEXPECTED_ERROR');
793 FND_MESSAGE.SET_TOKEN('TEXT', l_msg_data);
794 x_msg_data := FND_MESSAGE.Get;
795 END check_foreign_keys;
796
797 /*###############################################################
798 # NAME
799 # check_primary_key
800 # SYNOPSIS
801 # proc check_primary_key
802 # DESCRIPTION
803 # This particular procedure is used to check the existense
804 # of the primary key in the table.
805 #################################################################*/
806
807 PROCEDURE check_primary_key
808 /* p_transfer_no is the transfer number to check.
809 ** p_called_by_form is 'T' if called by a form or 'F' if not.
810 ** x_rowid is the row id of the record if found.
811 ** x_key_exists is 'T' is the record is found, 'F' if not.
812 */
813 (p_orgn_code IN VARCHAR2,
814 p_transfer_no IN VARCHAR2,
815 p_called_by_form IN VARCHAR2,
816 x_rowid OUT NOCOPY VARCHAR2,
817 x_key_exists OUT NOCOPY VARCHAR2)
818 IS
819 /* Local variables */
820
821 l_msg_data VARCHAR2(80);
822
823 /* Declare any variables and the cursor */
824
825 CURSOR Cur_get_transfer_rowid IS
826 SELECT rowid
827 FROM ic_xfer_mst
828 WHERE transfer_no = p_transfer_no
829 AND orgn_code = p_orgn_code;
830
831 TransferRecord Cur_get_transfer_rowid%ROWTYPE;
832
833 BEGIN
834 x_key_exists := 'F';
835 l_msg_data := p_orgn_code||' '||p_transfer_no;
836
837 OPEN Cur_get_transfer_rowid;
838 FETCH Cur_get_transfer_rowid INTO TransferRecord;
839 IF Cur_get_transfer_rowid%FOUND THEN
840 x_key_exists := 'T';
841 x_rowid := TransferRecord.rowid;
842 ELSE
843 x_key_exists := 'F';
844 END IF;
845 CLOSE Cur_get_transfer_rowid;
846
847 EXCEPTION
848 WHEN Others THEN
849 l_msg_data := sqlerrm;
850 FND_MESSAGE.SET_NAME('GMI', 'IC_UNEXPECTED_ERROR');
851 FND_MESSAGE.SET_TOKEN('TEXT',l_msg_data);
852 IF FND_API.To_Boolean(p_called_by_form) THEN
853 APP_EXCEPTION.Raise_Exception;
854 END IF;
855 END Check_Primary_Key;
856
857 END GMI_XFER_MST_PKG;