[Home] [Help]
PACKAGE BODY: APPS.GR_DISPATCH_HISTORIES_PKG
Source
1 PACKAGE BODY GR_DISPATCH_HISTORIES_PKG AS
2 /*$Header: GRHIDIHB.pls 115.5 2002/10/25 18:29:07 mgrosser ship $*/
3 PROCEDURE Insert_Row
4 (p_commit IN VARCHAR2,
5 p_called_by_form IN VARCHAR2,
6 p_document_code IN VARCHAR2,
7 p_document_text_id IN NUMBER,
8 p_item_code IN VARCHAR2,
9 p_recipient_code IN VARCHAR2,
10 p_date_msds_sent IN DATE,
11 p_dispatch_method_code IN VARCHAR2,
12 p_cover_letter_text_id IN VARCHAR2,
13 p_attribute_category IN VARCHAR2,
14 p_attribute1 IN VARCHAR2,
15 p_attribute2 IN VARCHAR2,
16 p_attribute3 IN VARCHAR2,
17 p_attribute4 IN VARCHAR2,
18 p_attribute5 IN VARCHAR2,
19 p_attribute6 IN VARCHAR2,
20 p_attribute7 IN VARCHAR2,
21 p_attribute8 IN VARCHAR2,
22 p_attribute9 IN VARCHAR2,
23 p_attribute10 IN VARCHAR2,
24 p_attribute11 IN VARCHAR2,
25 p_attribute12 IN VARCHAR2,
26 p_attribute13 IN VARCHAR2,
27 p_attribute14 IN VARCHAR2,
28 p_attribute15 IN VARCHAR2,
29 p_attribute16 IN VARCHAR2,
30 p_attribute17 IN VARCHAR2,
31 p_attribute18 IN VARCHAR2,
32 p_attribute19 IN VARCHAR2,
33 p_attribute20 IN VARCHAR2,
34 p_attribute21 IN VARCHAR2,
35 p_attribute22 IN VARCHAR2,
36 p_attribute23 IN VARCHAR2,
37 p_attribute24 IN VARCHAR2,
38 p_attribute25 IN VARCHAR2,
39 p_attribute26 IN VARCHAR2,
40 p_attribute27 IN VARCHAR2,
41 p_attribute28 IN VARCHAR2,
42 p_attribute29 IN VARCHAR2,
43 p_attribute30 IN VARCHAR2,
44 p_created_by IN NUMBER,
45 p_creation_date IN DATE,
46 p_last_updated_by IN NUMBER,
47 p_last_update_date IN DATE,
48 p_last_update_login IN NUMBER,
49 x_rowid OUT NOCOPY VARCHAR2,
50 x_return_status OUT NOCOPY VARCHAR2,
51 x_oracle_error OUT NOCOPY NUMBER,
52 x_msg_data OUT NOCOPY VARCHAR2)
53 IS
54 /* Alpha Variables */
55
56 L_RETURN_STATUS VARCHAR2(1) := 'S';
57 L_KEY_EXISTS VARCHAR2(1);
58 L_MSG_DATA VARCHAR2(2000);
59 L_ROWID VARCHAR2(18);
60
61 /* Number Variables */
62
63 L_ORACLE_ERROR NUMBER;
64
65 /* Exceptions */
66
67 FOREIGN_KEY_ERROR EXCEPTION;
68 LABEL_EXISTS_ERROR EXCEPTION;
69 ROW_MISSING_ERROR EXCEPTION;
70
71 /* Declare cursors */
72
73 BEGIN
74
75 /* Initialization Routine */
76
77 SAVEPOINT Insert_Row;
78 x_return_status := 'S';
79 x_oracle_error := 0;
80 x_msg_data := NULL;
81
82 /* Now call the check foreign key procedure */
83
84 Check_Foreign_Keys
85 (p_document_code,
86 p_document_text_id,
87 p_item_code,
88 p_recipient_code,
89 p_date_msds_sent,
90 p_dispatch_method_code,
91 p_cover_letter_text_id,
92 p_attribute_category,
93 p_attribute1,
94 p_attribute2,
95 p_attribute3,
96 p_attribute4,
97 p_attribute5,
98 p_attribute6,
99 p_attribute7,
100 p_attribute8,
101 p_attribute9,
102 p_attribute10,
103 p_attribute11,
104 p_attribute12,
105 p_attribute13,
106 p_attribute14,
107 p_attribute15,
108 p_attribute16,
109 p_attribute17,
110 p_attribute18,
111 p_attribute19,
112 p_attribute20,
113 p_attribute21,
114 p_attribute22,
115 p_attribute23,
116 p_attribute24,
117 p_attribute25,
118 p_attribute26,
119 p_attribute27,
120 p_attribute28,
121 p_attribute29,
122 p_attribute30,
123 l_return_status,
124 l_oracle_error,
125 l_msg_data);
126 IF l_return_status <> 'S' THEN
127 RAISE Foreign_Key_Error;
128 END IF;
129
130 /* Now check the primary key doesn't already exist */
131
132 Check_Primary_Key
133 (p_document_code,
134 p_document_text_id,
135 p_item_code,
136 p_recipient_code,
137 p_date_msds_sent,
138 'F',
139 l_rowid,
140 l_key_exists);
141
142 IF FND_API.To_Boolean(l_key_exists) THEN
143 RAISE Label_Exists_Error;
144 END IF;
145
146 INSERT INTO gr_dispatch_histories
147 (document_code,
148 document_text_id,
149 item_code,
150 recipient_code,
151 date_msds_sent,
152 dispatch_method_code,
153 cover_letter_text_id,
154 attribute_category,
155 attribute1,
156 attribute2,
157 attribute3,
158 attribute4,
159 attribute5,
160 attribute6,
161 attribute7,
162 attribute8,
163 attribute9,
164 attribute10,
165 attribute11,
166 attribute12,
167 attribute13,
168 attribute14,
169 attribute15,
170 attribute16,
171 attribute17,
172 attribute18,
173 attribute19,
174 attribute20,
175 attribute21,
176 attribute22,
177 attribute23,
178 attribute24,
179 attribute25,
180 attribute26,
181 attribute27,
182 attribute28,
183 attribute29,
184 attribute30,
185 created_by,
186 creation_date,
187 last_updated_by,
188 last_update_date,
189 last_update_login)
190 VALUES
191 (p_document_code,
192 p_document_text_id,
193 p_item_code,
194 p_recipient_code,
195 p_date_msds_sent,
196 p_dispatch_method_code,
197 p_cover_letter_text_id,
198 p_attribute_category,
199 p_attribute1,
200 p_attribute2,
201 p_attribute3,
202 p_attribute4,
203 p_attribute5,
204 p_attribute6,
205 p_attribute7,
206 p_attribute8,
207 p_attribute9,
208 p_attribute10,
209 p_attribute11,
210 p_attribute12,
211 p_attribute13,
212 p_attribute14,
213 p_attribute15,
214 p_attribute16,
215 p_attribute17,
216 p_attribute18,
217 p_attribute19,
218 p_attribute20,
219 p_attribute21,
220 p_attribute22,
221 p_attribute23,
222 p_attribute24,
223 p_attribute25,
224 p_attribute26,
225 p_attribute27,
226 p_attribute28,
227 p_attribute29,
228 p_attribute30,
229 p_created_by,
230 p_creation_date,
231 p_last_updated_by,
232 p_last_update_date,
233 p_last_update_login);
234
235 /* Now get the row id of the inserted record */
236
237 Check_Primary_Key
238 (p_document_code,
239 p_document_text_id,
240 p_item_code,
241 p_recipient_code,
242 p_date_msds_sent,
243 'F',
244 l_rowid,
245 l_key_exists);
246
247 IF FND_API.To_Boolean(l_key_exists) THEN
248 x_rowid := l_rowid;
249 ELSE
250 RAISE Row_Missing_Error;
251 END IF;
252
253 /* Check the commit flag and if set, then commit the work. */
254
255 IF FND_API.To_Boolean(p_commit) THEN
256 COMMIT WORK;
257 END IF;
258
259 EXCEPTION
260
261 WHEN Foreign_Key_Error THEN
262 ROLLBACK TO SAVEPOINT Insert_Row;
263 x_return_status := l_return_status;
264 x_oracle_error := l_oracle_error;
265 FND_MESSAGE.SET_NAME('GR',
266 'GR_FOREIGN_KEY_ERROR');
267 FND_MESSAGE.SET_TOKEN('TEXT',
268 l_msg_data,
269 FALSE);
270 IF FND_API.To_Boolean(p_called_by_form) THEN
271 APP_EXCEPTION.Raise_Exception;
272 ELSE
273 x_msg_data := FND_MESSAGE.Get;
274 END IF;
275
276 WHEN Label_Exists_Error THEN
277 ROLLBACK TO SAVEPOINT Insert_Row;
278 x_return_status := 'E';
279 x_oracle_error := APP_EXCEPTION.Get_Code;
280 FND_MESSAGE.SET_NAME('GR',
281 'GR_RECORD_EXISTS');
282 FND_MESSAGE.SET_TOKEN('CODE',
283 p_document_code,
284 FALSE);
285 IF FND_API.To_Boolean(p_called_by_form) THEN
286 APP_EXCEPTION.Raise_Exception;
287 ELSE
288 x_msg_data := FND_MESSAGE.Get;
289 END IF;
290
291 WHEN Row_Missing_Error THEN
292 ROLLBACK TO SAVEPOINT Insert_Row;
293 x_return_status := 'E';
294 x_oracle_error := APP_EXCEPTION.Get_Code;
295 FND_MESSAGE.SET_NAME('GR',
296 'GR_NO_RECORD_INSERTED');
297 FND_MESSAGE.SET_TOKEN('CODE',
298 p_document_code,
299 FALSE);
300 IF FND_API.To_Boolean(p_called_by_form) THEN
301 APP_EXCEPTION.Raise_Exception;
302 ELSE
303 x_msg_data := FND_MESSAGE.Get;
304 END IF;
305
306 WHEN OTHERS THEN
307 ROLLBACK TO SAVEPOINT Insert_Row;
308 x_return_status := 'U';
309 x_oracle_error := APP_EXCEPTION.Get_Code;
310 l_msg_data := APP_EXCEPTION.Get_Text;
311 FND_MESSAGE.SET_NAME('GR',
312 'GR_UNEXPECTED_ERROR');
313 FND_MESSAGE.SET_TOKEN('TEXT',
314 l_msg_data,
315 FALSE);
316 IF FND_API.To_Boolean(p_called_by_form) THEN
317 APP_EXCEPTION.Raise_Exception;
318 ELSE
319 x_msg_data := FND_MESSAGE.Get;
320 END IF;
321
322 END Insert_Row;
323
324 PROCEDURE Update_Row
325 (p_commit IN VARCHAR2,
326 p_called_by_form IN VARCHAR2,
327 p_rowid IN VARCHAR2,
328 p_document_code IN VARCHAR2,
329 p_document_text_id IN NUMBER,
330 p_item_code IN VARCHAR2,
331 p_recipient_code IN VARCHAR2,
332 p_date_msds_sent IN DATE,
333 p_dispatch_method_code IN VARCHAR2,
334 p_cover_letter_text_id IN VARCHAR2,
335 p_attribute_category IN VARCHAR2,
336 p_attribute1 IN VARCHAR2,
337 p_attribute2 IN VARCHAR2,
338 p_attribute3 IN VARCHAR2,
339 p_attribute4 IN VARCHAR2,
340 p_attribute5 IN VARCHAR2,
341 p_attribute6 IN VARCHAR2,
342 p_attribute7 IN VARCHAR2,
343 p_attribute8 IN VARCHAR2,
344 p_attribute9 IN VARCHAR2,
345 p_attribute10 IN VARCHAR2,
346 p_attribute11 IN VARCHAR2,
347 p_attribute12 IN VARCHAR2,
348 p_attribute13 IN VARCHAR2,
349 p_attribute14 IN VARCHAR2,
350 p_attribute15 IN VARCHAR2,
351 p_attribute16 IN VARCHAR2,
352 p_attribute17 IN VARCHAR2,
353 p_attribute18 IN VARCHAR2,
354 p_attribute19 IN VARCHAR2,
355 p_attribute20 IN VARCHAR2,
356 p_attribute21 IN VARCHAR2,
357 p_attribute22 IN VARCHAR2,
358 p_attribute23 IN VARCHAR2,
359 p_attribute24 IN VARCHAR2,
360 p_attribute25 IN VARCHAR2,
361 p_attribute26 IN VARCHAR2,
362 p_attribute27 IN VARCHAR2,
363 p_attribute28 IN VARCHAR2,
364 p_attribute29 IN VARCHAR2,
365 p_attribute30 IN VARCHAR2,
366 p_created_by IN NUMBER,
367 p_creation_date IN DATE,
368 p_last_updated_by IN NUMBER,
369 p_last_update_date IN DATE,
370 p_last_update_login IN NUMBER,
371 x_return_status OUT NOCOPY VARCHAR2,
372 x_oracle_error OUT NOCOPY NUMBER,
373 x_msg_data OUT NOCOPY VARCHAR2)
374 IS
375
376 /* Alpha Variables */
377
378 L_RETURN_STATUS VARCHAR2(1) := 'S';
379 L_MSG_DATA VARCHAR2(2000);
380
381 /* Number Variables */
382
383 L_ORACLE_ERROR NUMBER;
384
385 /* Exceptions */
386
387 FOREIGN_KEY_ERROR EXCEPTION;
388 ROW_MISSING_ERROR EXCEPTION;
389
390 BEGIN
391
392 /* Initialization Routine */
393
394 SAVEPOINT Update_Row;
395 x_return_status := 'S';
396 x_oracle_error := 0;
397 x_msg_data := NULL;
398
399 /* Now call the check foreign key procedure */
400
401 Check_Foreign_Keys
402 (p_document_code,
403 p_document_text_id,
404 p_item_code,
405 p_recipient_code,
406 p_date_msds_sent,
407 p_dispatch_method_code,
408 p_cover_letter_text_id,
409 p_attribute_category,
410 p_attribute1,
411 p_attribute2,
412 p_attribute3,
413 p_attribute4,
414 p_attribute5,
415 p_attribute6,
416 p_attribute7,
417 p_attribute8,
418 p_attribute9,
419 p_attribute10,
420 p_attribute11,
421 p_attribute12,
422 p_attribute13,
423 p_attribute14,
424 p_attribute15,
425 p_attribute16,
426 p_attribute17,
427 p_attribute18,
428 p_attribute19,
429 p_attribute20,
430 p_attribute21,
431 p_attribute22,
432 p_attribute23,
433 p_attribute24,
434 p_attribute25,
435 p_attribute26,
436 p_attribute27,
437 p_attribute28,
438 p_attribute29,
439 p_attribute30,
440 l_return_status,
441 l_oracle_error,
442 l_msg_data);
443
444 IF l_return_status <> 'S' THEN
445 RAISE Foreign_Key_Error;
446 ELSE
447 UPDATE gr_dispatch_histories
448 SET document_code = p_document_code,
449 document_text_id = p_document_text_id,
450 item_code = p_item_code,
451 recipient_code = p_recipient_code,
452 date_msds_sent = p_date_msds_sent,
453 dispatch_method_code = p_dispatch_method_code,
454 cover_letter_text_id = p_cover_letter_text_id,
455 attribute_category = p_attribute_category,
456 attribute1 = p_attribute1,
457 attribute2 = p_attribute2,
458 attribute3 = p_attribute3,
459 attribute4 = p_attribute4,
460 attribute5 = p_attribute5,
461 attribute6 = p_attribute6,
462 attribute7 = p_attribute7,
463 attribute8 = p_attribute8,
464 attribute9 = p_attribute9,
465 attribute10 = p_attribute10,
466 attribute11 = p_attribute11,
467 attribute12 = p_attribute12,
468 attribute13 = p_attribute13,
469 attribute14 = p_attribute14,
470 attribute15 = p_attribute15,
471 attribute16 = p_attribute16,
472 attribute17 = p_attribute17,
473 attribute18 = p_attribute18,
474 attribute19 = p_attribute19,
475 attribute20 = p_attribute20,
476 attribute21 = p_attribute11,
477 attribute22 = p_attribute22,
478 attribute23 = p_attribute23,
479 attribute24 = p_attribute24,
480 attribute25 = p_attribute25,
481 attribute26 = p_attribute26,
482 attribute27 = p_attribute27,
483 attribute28 = p_attribute28,
484 attribute29 = p_attribute29,
485 attribute30 = p_attribute30,
486 created_by = p_created_by,
487 creation_date = p_creation_date,
488 last_updated_by = p_last_updated_by,
489 last_update_date = p_last_update_date,
490 last_update_login = p_last_update_login
491 WHERE rowid = p_rowid;
492 IF SQL%NOTFOUND THEN
493 RAISE Row_Missing_Error;
494 END IF;
495 END IF;
496
497 /* Check the commit flag and if set, then commit the work. */
498
499 IF FND_API.To_Boolean(p_commit) THEN
500 COMMIT WORK;
501 END IF;
502
503 EXCEPTION
504
505 WHEN Foreign_Key_Error THEN
506 ROLLBACK TO SAVEPOINT Update_Row;
507 x_return_status := l_return_status;
508 x_oracle_error := l_oracle_error;
509 FND_MESSAGE.SET_NAME('GR',
510 'GR_FOREIGN_KEY_ERROR');
511 FND_MESSAGE.SET_TOKEN('TEXT',
512 l_msg_data,
513 FALSE);
514 IF FND_API.To_Boolean(p_called_by_form) THEN
515 APP_EXCEPTION.Raise_Exception;
516 ELSE
517 x_msg_data := FND_MESSAGE.Get;
518 END IF;
519
520 WHEN Row_Missing_Error THEN
521 ROLLBACK TO SAVEPOINT Update_Row;
522 x_return_status := 'E';
523 x_oracle_error := APP_EXCEPTION.Get_Code;
524 FND_MESSAGE.SET_NAME('GR',
525 'GR_NO_RECORD_INSERTED');
526 FND_MESSAGE.SET_TOKEN('CODE',
527 p_document_code,
528 FALSE);
529 IF FND_API.To_Boolean(p_called_by_form) THEN
530 APP_EXCEPTION.Raise_Exception;
531 ELSE
532 x_msg_data := FND_MESSAGE.Get;
533 END IF;
534
535 WHEN OTHERS THEN
536 ROLLBACK TO SAVEPOINT Update_Row;
537 x_return_status := 'U';
538 x_oracle_error := APP_EXCEPTION.Get_Code;
539 l_msg_data := APP_EXCEPTION.Get_Text;
540 FND_MESSAGE.SET_NAME('GR',
541 'GR_UNEXPECTED_ERROR');
542 FND_MESSAGE.SET_TOKEN('TEXT',
543 l_msg_data,
544 FALSE);
545 IF FND_API.To_Boolean(p_called_by_form) THEN
546 APP_EXCEPTION.Raise_Exception;
547 ELSE
548 x_msg_data := FND_MESSAGE.Get;
549 END IF;
550
551 END Update_Row;
552
553 PROCEDURE Lock_Row
554 (p_commit IN VARCHAR2,
555 p_called_by_form IN VARCHAR2,
556 p_rowid IN VARCHAR2,
557 p_document_code IN VARCHAR2,
558 p_document_text_id IN NUMBER,
559 p_item_code IN VARCHAR2,
560 p_recipient_code IN VARCHAR2,
561 p_date_msds_sent IN DATE,
562 p_dispatch_method_code IN VARCHAR2,
563 p_cover_letter_text_id IN VARCHAR2,
564 p_attribute_category IN VARCHAR2,
565 p_attribute1 IN VARCHAR2,
566 p_attribute2 IN VARCHAR2,
567 p_attribute3 IN VARCHAR2,
568 p_attribute4 IN VARCHAR2,
569 p_attribute5 IN VARCHAR2,
570 p_attribute6 IN VARCHAR2,
571 p_attribute7 IN VARCHAR2,
572 p_attribute8 IN VARCHAR2,
573 p_attribute9 IN VARCHAR2,
574 p_attribute10 IN VARCHAR2,
575 p_attribute11 IN VARCHAR2,
576 p_attribute12 IN VARCHAR2,
577 p_attribute13 IN VARCHAR2,
578 p_attribute14 IN VARCHAR2,
579 p_attribute15 IN VARCHAR2,
580 p_attribute16 IN VARCHAR2,
581 p_attribute17 IN VARCHAR2,
582 p_attribute18 IN VARCHAR2,
583 p_attribute19 IN VARCHAR2,
584 p_attribute20 IN VARCHAR2,
585 p_attribute21 IN VARCHAR2,
586 p_attribute22 IN VARCHAR2,
587 p_attribute23 IN VARCHAR2,
588 p_attribute24 IN VARCHAR2,
589 p_attribute25 IN VARCHAR2,
590 p_attribute26 IN VARCHAR2,
591 p_attribute27 IN VARCHAR2,
592 p_attribute28 IN VARCHAR2,
593 p_attribute29 IN VARCHAR2,
594 p_attribute30 IN VARCHAR2,
595 p_created_by IN NUMBER,
596 p_creation_date IN DATE,
597 p_last_updated_by IN NUMBER,
598 p_last_update_date IN DATE,
599 p_last_update_login IN NUMBER,
600 x_return_status OUT NOCOPY VARCHAR2,
601 x_oracle_error OUT NOCOPY NUMBER,
602 x_msg_data OUT NOCOPY VARCHAR2)
603 IS
604
605 /* Alpha Variables */
606
607 L_RETURN_STATUS VARCHAR2(1) := 'S';
608 L_MSG_DATA VARCHAR2(2000);
609
610 /* Number Variables */
611
612 L_ORACLE_ERROR NUMBER;
613
614 /* Exceptions */
615
616 NO_DATA_FOUND_ERROR EXCEPTION;
617 ROW_ALREADY_LOCKED_ERROR EXCEPTION;
618 PRAGMA EXCEPTION_INIT(ROW_ALREADY_LOCKED_ERROR,-54);
619
620 /* Define the cursors */
621
622 CURSOR c_lock_dispatch
623 IS
624 SELECT *
625 FROM gr_dispatch_histories
626 WHERE rowid = p_rowid
627 FOR UPDATE NOWAIT;
628 LockDispatchRcd c_lock_dispatch%ROWTYPE;
629
630 BEGIN
631
635 x_return_status := 'S';
632 /* Initialization Routine */
633
634 SAVEPOINT Lock_Row;
636 x_oracle_error := 0;
637 x_msg_data := NULL;
638
639 /* Now lock the record */
640
641 OPEN c_lock_dispatch;
642 FETCH c_lock_dispatch INTO LockDispatchRcd;
643 IF c_lock_dispatch%NOTFOUND THEN
644 CLOSE c_lock_dispatch;
645 RAISE No_Data_Found_Error;
646 END IF;
647 CLOSE c_lock_dispatch;
648
649 IF FND_API.To_Boolean(p_commit) THEN
650 COMMIT WORK;
651 END IF;
652
653 EXCEPTION
654
655 WHEN No_Data_Found_Error THEN
656 ROLLBACK TO SAVEPOINT Lock_Row;
657 x_return_status := 'E';
658 FND_MESSAGE.SET_NAME('GR',
659 'GR_RECORD_NOT_FOUND');
660 FND_MESSAGE.SET_TOKEN('CODE',
661 p_document_code,
662 FALSE);
663 IF FND_API.To_Boolean(p_called_by_form) THEN
664 APP_EXCEPTION.Raise_Exception;
665 ELSE
666 x_msg_data := FND_MESSAGE.Get;
667 END IF;
668
669 WHEN Row_Already_Locked_Error THEN
670 ROLLBACK TO SAVEPOINT Lock_Row;
671 x_return_status := 'E';
672 x_oracle_error := APP_EXCEPTION.Get_Code;
673 FND_MESSAGE.SET_NAME('GR',
674 'GR_ROW_IS_LOCKED');
675 IF FND_API.To_Boolean(p_called_by_form) THEN
676 APP_EXCEPTION.Raise_Exception;
677 ELSE
678 x_msg_data := FND_MESSAGE.Get;
679 END IF;
680
681 WHEN OTHERS THEN
682 ROLLBACK TO SAVEPOINT Lock_Row;
683 x_return_status := 'U';
684 x_oracle_error := APP_EXCEPTION.Get_Code;
685 l_msg_data := APP_EXCEPTION.Get_Text;
686 FND_MESSAGE.SET_NAME('GR',
687 'GR_UNEXPECTED_ERROR');
688 FND_MESSAGE.SET_TOKEN('TEXT',
689 l_msg_data,
690 FALSE);
691 IF FND_API.To_Boolean(p_called_by_form) THEN
692 APP_EXCEPTION.Raise_Exception;
693 ELSE
694 x_msg_data := FND_MESSAGE.Get;
695 END IF;
696
697 END Lock_Row;
698
699 PROCEDURE Delete_Row
700 (p_commit IN VARCHAR2,
701 p_called_by_form IN VARCHAR2,
702 p_rowid IN VARCHAR2,
703 p_document_code IN VARCHAR2,
704 p_document_text_id IN NUMBER,
705 p_item_code IN VARCHAR2,
706 p_recipient_code IN VARCHAR2,
707 p_date_msds_sent IN DATE,
708 p_dispatch_method_code IN VARCHAR2,
709 p_cover_letter_text_id IN VARCHAR2,
710 p_attribute_category IN VARCHAR2,
711 p_attribute1 IN VARCHAR2,
712 p_attribute2 IN VARCHAR2,
713 p_attribute3 IN VARCHAR2,
714 p_attribute4 IN VARCHAR2,
715 p_attribute5 IN VARCHAR2,
716 p_attribute6 IN VARCHAR2,
717 p_attribute7 IN VARCHAR2,
718 p_attribute8 IN VARCHAR2,
719 p_attribute9 IN VARCHAR2,
720 p_attribute10 IN VARCHAR2,
721 p_attribute11 IN VARCHAR2,
722 p_attribute12 IN VARCHAR2,
723 p_attribute13 IN VARCHAR2,
724 p_attribute14 IN VARCHAR2,
725 p_attribute15 IN VARCHAR2,
726 p_attribute16 IN VARCHAR2,
727 p_attribute17 IN VARCHAR2,
728 p_attribute18 IN VARCHAR2,
729 p_attribute19 IN VARCHAR2,
730 p_attribute20 IN VARCHAR2,
731 p_attribute21 IN VARCHAR2,
732 p_attribute22 IN VARCHAR2,
733 p_attribute23 IN VARCHAR2,
734 p_attribute24 IN VARCHAR2,
735 p_attribute25 IN VARCHAR2,
736 p_attribute26 IN VARCHAR2,
737 p_attribute27 IN VARCHAR2,
738 p_attribute28 IN VARCHAR2,
739 p_attribute29 IN VARCHAR2,
740 p_attribute30 IN VARCHAR2,
741 p_created_by IN NUMBER,
742 p_creation_date IN DATE,
743 p_last_updated_by IN NUMBER,
744 p_last_update_date IN DATE,
745 p_last_update_login IN NUMBER,
746 x_return_status OUT NOCOPY VARCHAR2,
747 x_oracle_error OUT NOCOPY NUMBER,
748 x_msg_data OUT NOCOPY VARCHAR2)
749 IS
750
751 /* Alpha Variables */
752
753 L_RETURN_STATUS VARCHAR2(1) := 'S';
754 L_MSG_DATA VARCHAR2(2000);
755 L_CALLED_BY_FORM VARCHAR2(1);
756
757 /* Number Variables */
758
759 L_ORACLE_ERROR NUMBER;
760
761 /* Exceptions */
762
763 CHECK_INTEGRITY_ERROR EXCEPTION;
764 ROW_MISSING_ERROR EXCEPTION;
765 PRAGMA EXCEPTION_INIT(Row_Missing_Error,100);
766
767 BEGIN
768
769 /* Initialization Routine */
770
771 SAVEPOINT Delete_Row;
772 x_return_status := 'S';
773 l_called_by_form := 'F';
774 x_oracle_error := 0;
775 x_msg_data := NULL;
776
777 /* Now call the check integrity procedure */
778
779 Check_Integrity
780 (l_called_by_form,
781 p_document_code,
782 p_document_text_id,
783 p_item_code,
784 p_recipient_code,
785 p_date_msds_sent,
786 p_dispatch_method_code,
787 p_cover_letter_text_id,
788 p_attribute_category,
789 p_attribute1,
790 p_attribute2,
791 p_attribute3,
792 p_attribute4,
793 p_attribute5,
794 p_attribute6,
795 p_attribute7,
796 p_attribute8,
797 p_attribute9,
798 p_attribute10,
799 p_attribute11,
800 p_attribute12,
801 p_attribute13,
802 p_attribute14,
803 p_attribute15,
804 p_attribute16,
805 p_attribute17,
806 p_attribute18,
810 p_attribute22,
807 p_attribute19,
808 p_attribute20,
809 p_attribute21,
811 p_attribute23,
812 p_attribute24,
813 p_attribute25,
814 p_attribute26,
815 p_attribute27,
816 p_attribute28,
817 p_attribute29,
818 p_attribute30,
819 l_return_status,
820 l_oracle_error,
821 l_msg_data);
822
823 IF l_return_status <> 'S' THEN
824 RAISE Check_Integrity_Error;
825 END IF;
826
827 DELETE FROM gr_dispatch_histories
828 WHERE rowid = p_rowid;
829
830 /* Check the commit flag and if set, then commit the work. */
831
832 IF FND_API.TO_Boolean(p_commit) THEN
833 COMMIT WORK;
834 END IF;
835
836 EXCEPTION
837
838 WHEN Check_Integrity_Error THEN
839 ROLLBACK TO SAVEPOINT Delete_Row;
840 x_return_status := l_return_status;
841 x_oracle_error := l_oracle_error;
842 IF FND_API.To_Boolean(p_called_by_form) THEN
843 APP_EXCEPTION.Raise_Exception;
844 ELSE
845 x_msg_data := FND_MESSAGE.Get;
846 END IF;
847
848 WHEN Row_Missing_Error THEN
849 ROLLBACK TO SAVEPOINT Delete_Row;
850 x_return_status := 'E';
851 x_oracle_error := APP_EXCEPTION.Get_Code;
852 FND_MESSAGE.SET_NAME('GR',
853 'GR_RECORD_NOT_FOUND');
854 FND_MESSAGE.SET_TOKEN('CODE',
855 p_document_code,
856 FALSE);
857 IF FND_API.To_Boolean(p_called_by_form) THEN
858 APP_EXCEPTION.Raise_Exception;
859 ELSE
860 x_msg_data := FND_MESSAGE.Get;
861 END IF;
862
863 WHEN OTHERS THEN
864 ROLLBACK TO SAVEPOINT Delete_Row;
865 x_return_status := 'U';
866 x_oracle_error := APP_EXCEPTION.Get_Code;
867 l_msg_data := APP_EXCEPTION.Get_Text;
868 l_msg_data := APP_EXCEPTION.Get_Text;
869 FND_MESSAGE.SET_NAME('GR',
870 'GR_UNEXPECTED_ERROR');
871 FND_MESSAGE.SET_TOKEN('TEXT',
872 l_msg_data,
873 FALSE);
874 IF FND_API.To_Boolean(p_called_by_form) THEN
875 APP_EXCEPTION.Raise_Exception;
876 ELSE
877 x_msg_data := FND_MESSAGE.Get;
878 END IF;
879
880 END Delete_Row;
881
882 PROCEDURE Delete_Rows
883 (p_commit IN VARCHAR2,
884 p_called_by_form IN VARCHAR2,
885 p_delete_option IN VARCHAR2,
886 p_document_code IN VARCHAR2,
887 p_item_code IN VARCHAR2,
888 p_recipient_code IN VARCHAR2,
889 x_return_status OUT NOCOPY VARCHAR2,
890 x_oracle_error OUT NOCOPY NUMBER,
891 x_msg_data OUT NOCOPY VARCHAR2)
892 IS
893
894 /* Alpha Variables */
895
896 L_RETURN_STATUS VARCHAR2(1) := 'S';
897 L_MSG_DATA VARCHAR2(2000);
898 L_MSG_TOKEN VARCHAR2(100);
899
900 /* Number Variables */
901
902 L_ORACLE_ERROR NUMBER;
903
904 /* Define the exceptions */
905 NULL_DELETE_OPTION_ERROR EXCEPTION;
906
907 /* Define the cursors */
908
909 BEGIN
910
911 /* Initialization Routine */
912
913 SAVEPOINT Delete_Rows;
914 x_return_status := 'S';
915 x_oracle_error := 0;
916 x_msg_data := NULL;
917 /*
918 ** p delete option has one of four values
919 ** 'I' - Delete all rows for the specified item.
920 ** 'D' - Delete all rows for the specified document.
921 ** 'R' - Delete all rows for the specified recipient.
922 ** 'C' - Delete all rows using the item document combination.
923 */
924 IF p_delete_option = 'I' THEN
925 IF p_item_code IS NULL THEN
926 l_msg_token := 'Item Code';
927 RAISE Null_Delete_Option_Error;
928 ELSE
929 l_msg_token := p_item_code;
930
931 DELETE FROM gr_dispatch_histories
932 WHERE item_code = p_item_code;
933 END IF;
934 ELSIF p_delete_option = 'D' THEN
935 IF p_document_code IS NULL THEN
936 l_msg_token := 'Document Code';
937 RAISE Null_Delete_Option_Error;
938 ELSE
939 l_msg_token := p_document_code;
940
941 DELETE FROM gr_dispatch_histories
942 WHERE document_code = p_document_code;
943 END IF;
944 ELSIF p_delete_option = 'R' THEN
945 IF p_recipient_code IS NULL THEN
946 l_msg_token := 'Recipient Code';
947 RAISE Null_Delete_Option_Error;
948 ELSE
949 l_msg_token := p_recipient_code;
950
951 DELETE FROM gr_dispatch_histories
952 WHERE recipient_code = p_recipient_code;
953 END IF;
954 ELSIF p_delete_option = 'C' THEN
955 IF p_item_code IS NULL OR
956 p_document_code IS NULL THEN
957 l_msg_token := 'Item / Document Code';
958 RAISE Null_Delete_Option_Error;
959 ELSE
960 l_msg_token := p_item_code || ' ' || p_document_code;
961
962 DELETE FROM gr_dispatch_histories
963 WHERE item_code = p_item_code
964 AND document_code = p_document_code;
965 END IF;
966 END IF;
967
968 IF FND_API.To_Boolean(p_commit) THEN
969 COMMIT WORK;
970 END IF;
971
972 EXCEPTION
973
974 WHEN Null_Delete_Option_Error THEN
975 x_return_status := 'E';
976 x_oracle_error := APP_EXCEPTION.Get_Code;
977 FND_MESSAGE.SET_NAME('GR',
978 'GR_NULL_VALUE');
979 FND_MESSAGE.SET_TOKEN('CODE',
980 l_msg_token,
981 FALSE);
982 IF FND_API.To_Boolean(p_called_by_form) THEN
983 APP_EXCEPTION.Raise_Exception;
984 ELSE
985 x_msg_data := FND_MESSAGE.Get;
986 END IF;
987
988 WHEN OTHERS THEN
989 ROLLBACK TO SAVEPOINT Delete_Rows;
990 x_return_status := 'U';
991 x_oracle_error := APP_EXCEPTION.Get_Code;
992 l_msg_data := APP_EXCEPTION.Get_Text;
993 FND_MESSAGE.SET_NAME('GR',
994 'GR_UNEXPECTED_ERROR');
995 FND_MESSAGE.SET_TOKEN('TEXT',
996 l_msg_token,
997 FALSE);
998 IF FND_API.To_Boolean(p_called_by_form) THEN
999 APP_EXCEPTION.Raise_Exception;
1000 ELSE
1001 x_msg_data := FND_MESSAGE.Get;
1002 END IF;
1003
1004 END Delete_Rows;
1005
1006 PROCEDURE Check_Foreign_Keys
1007 (p_document_code IN VARCHAR2,
1008 p_document_text_id IN NUMBER,
1009 p_item_code IN VARCHAR2,
1010 p_recipient_code IN VARCHAR2,
1011 p_date_msds_sent IN DATE,
1012 p_dispatch_method_code IN VARCHAR2,
1013 p_cover_letter_text_id IN VARCHAR2,
1014 p_attribute_category IN VARCHAR2,
1015 p_attribute1 IN VARCHAR2,
1016 p_attribute2 IN VARCHAR2,
1017 p_attribute3 IN VARCHAR2,
1018 p_attribute4 IN VARCHAR2,
1019 p_attribute5 IN VARCHAR2,
1020 p_attribute6 IN VARCHAR2,
1021 p_attribute7 IN VARCHAR2,
1022 p_attribute8 IN VARCHAR2,
1023 p_attribute9 IN VARCHAR2,
1024 p_attribute10 IN VARCHAR2,
1025 p_attribute11 IN VARCHAR2,
1026 p_attribute12 IN VARCHAR2,
1027 p_attribute13 IN VARCHAR2,
1028 p_attribute14 IN VARCHAR2,
1029 p_attribute15 IN VARCHAR2,
1030 p_attribute16 IN VARCHAR2,
1031 p_attribute17 IN VARCHAR2,
1032 p_attribute18 IN VARCHAR2,
1033 p_attribute19 IN VARCHAR2,
1034 p_attribute20 IN VARCHAR2,
1035 p_attribute21 IN VARCHAR2,
1036 p_attribute22 IN VARCHAR2,
1037 p_attribute23 IN VARCHAR2,
1038 p_attribute24 IN VARCHAR2,
1039 p_attribute25 IN VARCHAR2,
1040 p_attribute26 IN VARCHAR2,
1041 p_attribute27 IN VARCHAR2,
1042 p_attribute28 IN VARCHAR2,
1043 p_attribute29 IN VARCHAR2,
1044 p_attribute30 IN VARCHAR2,
1048 IS
1045 x_return_status OUT NOCOPY VARCHAR2,
1046 x_oracle_error OUT NOCOPY NUMBER,
1047 x_msg_data OUT NOCOPY VARCHAR2)
1049
1050 /* Alpha Variables */
1051
1052 L_RETURN_STATUS VARCHAR2(1) := 'S';
1053 L_MSG_DATA VARCHAR2(2000);
1054 L_ROWID VARCHAR2(18);
1055 L_KEY_EXISTS VARCHAR2(1);
1056 L_HEADING VARCHAR2(30);
1057
1058 /* Number Variables */
1059
1060 L_ORACLE_ERROR NUMBER;
1061
1062 /* Define the cursors */
1063
1064 BEGIN
1065
1066 /* Initialization Routine */
1067
1068 SAVEPOINT Check_Foreign_Keys;
1069 x_return_status := 'S';
1070 x_oracle_error := 0;
1071 x_msg_data := NULL;
1072
1073 /* Check the item code */
1074
1075 l_key_exists := 'T';
1076 GR_ITEM_GENERAL_PKG.Check_Primary_Key
1077 (p_item_code,
1078 'F',
1079 l_rowid,
1080 l_key_exists);
1081
1082 IF NOT FND_API.To_Boolean(l_key_exists) THEN
1083 x_return_status := 'E';
1084 FND_MESSAGE.SET_NAME('GR',
1085 'GR_RECORD_NOT_FOUND');
1086 FND_MESSAGE.SET_TOKEN('CODE',
1087 p_item_code,
1088 FALSE);
1089 l_msg_data := l_msg_data || FND_MESSAGE.Get || ' ';
1090 END IF;
1091
1092 /* Check the document code */
1093
1094 l_key_exists := 'T';
1095 GR_DOCUMENT_CODES_PKG.Check_Primary_Key
1096 (p_document_code,
1097 'F',
1098 l_rowid,
1099 l_key_exists);
1100
1101 IF NOT FND_API.To_Boolean(l_key_exists) THEN
1102 x_return_status := 'E';
1103 FND_MESSAGE.SET_NAME('GR',
1104 'GR_RECORD_NOT_FOUND');
1105 FND_MESSAGE.SET_TOKEN('CODE',
1106 p_document_code,
1107 FALSE);
1108 l_msg_data := l_msg_data || FND_MESSAGE.Get || ' ';
1109 END IF;
1110
1111 /* Check the recipient code */
1112
1113 l_key_exists := 'T';
1114 GR_RECIPIENT_INFO_PKG.Check_Primary_Key
1115 (p_recipient_code,
1116 'F',
1117 l_rowid,
1118 l_key_exists);
1119
1120 IF NOT FND_API.To_Boolean(l_key_exists) THEN
1121 x_return_status := 'E';
1122 FND_MESSAGE.SET_NAME('GR',
1123 'GR_RECORD_NOT_FOUND');
1124 FND_MESSAGE.SET_TOKEN('CODE',
1125 p_recipient_code,
1126 FALSE);
1127 l_msg_data := l_msg_data || FND_MESSAGE.Get || ' ';
1128 END IF;
1129
1130 IF x_return_status <> 'S' THEN
1131 x_msg_data := l_msg_data;
1132 END IF;
1133
1134 EXCEPTION
1135
1136 WHEN OTHERS THEN
1137 ROLLBACK TO SAVEPOINT Check_Foreign_Keys;
1138 x_return_status := 'U';
1139 x_oracle_error := APP_EXCEPTION.Get_Code;
1140 l_msg_data := APP_EXCEPTION.Get_Text;
1141 FND_MESSAGE.SET_NAME('GR',
1142 'GR_UNEXPECTED_ERROR');
1143 FND_MESSAGE.SET_TOKEN('TEXT',
1144 l_msg_data,
1145 FALSE);
1146 x_msg_data := FND_MESSAGE.Get;
1147
1148 END Check_Foreign_Keys;
1149
1150 PROCEDURE Check_Integrity
1151 (p_called_by_form IN VARCHAR2,
1152 p_document_code IN VARCHAR2,
1153 p_document_text_id IN NUMBER,
1154 p_item_code IN VARCHAR2,
1155 p_recipient_code IN VARCHAR2,
1156 p_date_msds_sent IN DATE,
1157 p_dispatch_method_code IN VARCHAR2,
1158 p_cover_letter_text_id IN VARCHAR2,
1159 p_attribute_category IN VARCHAR2,
1160 p_attribute1 IN VARCHAR2,
1161 p_attribute2 IN VARCHAR2,
1162 p_attribute3 IN VARCHAR2,
1163 p_attribute4 IN VARCHAR2,
1164 p_attribute5 IN VARCHAR2,
1165 p_attribute6 IN VARCHAR2,
1166 p_attribute7 IN VARCHAR2,
1167 p_attribute8 IN VARCHAR2,
1168 p_attribute9 IN VARCHAR2,
1169 p_attribute10 IN VARCHAR2,
1170 p_attribute11 IN VARCHAR2,
1171 p_attribute12 IN VARCHAR2,
1172 p_attribute13 IN VARCHAR2,
1173 p_attribute14 IN VARCHAR2,
1174 p_attribute15 IN VARCHAR2,
1175 p_attribute16 IN VARCHAR2,
1176 p_attribute17 IN VARCHAR2,
1177 p_attribute18 IN VARCHAR2,
1178 p_attribute19 IN VARCHAR2,
1179 p_attribute20 IN VARCHAR2,
1180 p_attribute21 IN VARCHAR2,
1181 p_attribute22 IN VARCHAR2,
1182 p_attribute23 IN VARCHAR2,
1183 p_attribute24 IN VARCHAR2,
1184 p_attribute25 IN VARCHAR2,
1185 p_attribute26 IN VARCHAR2,
1186 p_attribute27 IN VARCHAR2,
1187 p_attribute28 IN VARCHAR2,
1188 p_attribute29 IN VARCHAR2,
1189 p_attribute30 IN VARCHAR2,
1190 x_return_status OUT NOCOPY VARCHAR2,
1191 x_oracle_error OUT NOCOPY NUMBER,
1192 x_msg_data OUT NOCOPY VARCHAR2)
1193 IS
1194
1195 /* Alpha Variables */
1196
1197 L_RETURN_STATUS VARCHAR2(1) := 'S';
1198 L_MSG_DATA VARCHAR2(2000);
1199 L_CODE_BLOCK VARCHAR2(100);
1200
1201 /* Number Variables */
1202
1203 L_ORACLE_ERROR NUMBER;
1204
1205 /* Define the Cursors */
1206
1207 BEGIN
1208
1209 /* Initialization Routine */
1210
1211 SAVEPOINT Check_Integrity;
1212 x_return_status := 'S';
1213 x_oracle_error := 0;
1214 x_msg_data := NULL;
1215
1216
1217 /* No integrity checking is required. */
1218
1219 /* Now sort out the error messaging */
1220
1221 IF l_return_status <> 'S' THEN
1222 x_return_status := l_return_status;
1223 x_msg_data := l_msg_data;
1224 END IF;
1225
1226 EXCEPTION
1227
1231 x_oracle_error := APP_EXCEPTION.Get_Code;
1228 WHEN OTHERS THEN
1229 ROLLBACK TO SAVEPOINT Check_Integrity;
1230 x_return_status := 'U';
1232 l_msg_data := APP_EXCEPTION.Get_Text;
1233 FND_MESSAGE.SET_NAME('GR',
1234 'GR_UNEXPECTED_ERROR');
1235 FND_MESSAGE.SET_TOKEN('TEXT',
1236 l_msg_data,
1237 FALSE);
1238 IF FND_API.To_Boolean(p_called_by_form) THEN
1239 APP_EXCEPTION.Raise_Exception;
1240 ELSE
1241 x_msg_data := FND_MESSAGE.Get;
1242 END IF;
1243
1244 END Check_Integrity;
1245
1246 PROCEDURE Check_Primary_Key
1247 /* p_document_code is the document code to check.
1248 ** p_document_text_id is the document information
1249 ** p_item_code is the item code.
1250 ** p_recipient_code is the recipient of the dispatch.
1251 ** p_date_msds_sent is the dispatch date.
1252 ** p_called_by_form is 'T' if called by a form or 'F' if not.
1253 ** x_rowid is the row id of the record if found.
1254 ** x_key_exists is 'T' is the record is found, 'F' if not.
1255 */
1256 (p_document_code IN VARCHAR2,
1257 p_document_text_id IN NUMBER,
1258 p_item_code IN VARCHAR2,
1259 p_recipient_code IN VARCHAR2,
1260 p_date_msds_sent IN DATE,
1261 p_called_by_form IN VARCHAR2,
1262 x_rowid OUT NOCOPY VARCHAR2,
1263 x_key_exists OUT NOCOPY VARCHAR2)
1264 IS
1265 /* Alphanumeric variables */
1266
1267 L_MSG_DATA VARCHAR2(80);
1268
1269 /* Declare any variables and the cursor */
1270
1271
1272 CURSOR c_get_dispatch_rowid
1273 IS
1274 SELECT dih.rowid
1275 FROM gr_dispatch_histories dih
1276 WHERE dih.document_code = p_document_code
1277 AND dih.document_text_id = p_document_text_id
1278 AND dih.item_code = p_item_code
1279 AND dih.recipient_code = p_recipient_code
1280 AND dih.date_msds_sent = p_date_msds_sent;
1281 DispatchRecord c_get_dispatch_rowid%ROWTYPE;
1282
1283 BEGIN
1284
1285 x_key_exists := 'F';
1286 l_msg_data := p_document_code;
1287 OPEN c_get_dispatch_rowid;
1288 FETCH c_get_dispatch_rowid INTO DispatchRecord;
1289 IF c_get_dispatch_rowid%FOUND THEN
1290 x_key_exists := 'T';
1291 x_rowid := DispatchRecord.rowid;
1292 ELSE
1293 x_key_exists := 'F';
1294 END IF;
1295 CLOSE c_get_dispatch_rowid;
1296
1297 EXCEPTION
1298
1299 WHEN Others THEN
1300 l_msg_data := APP_EXCEPTION.Get_Text;
1301 FND_MESSAGE.SET_NAME('GR',
1302 'GR_UNEXPECTED_ERROR');
1303 FND_MESSAGE.SET_TOKEN('TEXT',
1304 l_msg_data,
1305 FALSE);
1306 IF FND_API.To_Boolean(p_called_by_form) THEN
1307 APP_EXCEPTION.Raise_Exception;
1308 END IF;
1309
1310 END Check_Primary_Key;
1311
1312 END GR_DISPATCH_HISTORIES_PKG;