[Home] [Help]
PACKAGE BODY: APPS.GR_ITEM_DOC_STATUSES_PKG
Source
1 PACKAGE BODY GR_ITEM_DOC_STATUSES_PKG AS
2 /*$Header: GRHIIDSB.pls 115.4 2002/10/25 20:37:50 methomas ship $*/
3 PROCEDURE Insert_Row
4 (p_commit IN VARCHAR2,
5 p_called_by_form IN VARCHAR2,
6 p_item_code IN VARCHAR2,
7 p_document_code IN VARCHAR2,
8 p_item_document_status IN VARCHAR2,
9 p_item_document_version IN NUMBER,
10 p_rebuild_item_doc_flag IN VARCHAR2,
11 p_last_approval_user IN NUMBER,
12 p_last_doc_author_lock IN NUMBER,
13 p_last_doc_update_date IN DATE,
14 p_created_by IN NUMBER,
15 p_creation_date IN DATE,
16 p_last_updated_by IN NUMBER,
17 p_last_update_date IN DATE,
18 p_last_update_login IN NUMBER,
19 x_rowid OUT NOCOPY VARCHAR2,
20 x_return_status OUT NOCOPY VARCHAR2,
21 x_oracle_error OUT NOCOPY NUMBER,
22 x_msg_data OUT NOCOPY VARCHAR2)
23 IS
24 /* Alpha Variables */
25
26 L_RETURN_STATUS VARCHAR2(1) := 'S';
27 L_KEY_EXISTS VARCHAR2(1);
28 L_MSG_DATA VARCHAR2(2000);
29 L_ROWID VARCHAR2(18);
30 L_MSG_TOKEN VARCHAR2(100);
31
32 /* Number Variables */
33
34 L_ORACLE_ERROR NUMBER;
35 /* Exceptions */
36
37 FOREIGN_KEY_ERROR EXCEPTION;
38 ITEM_EXISTS_ERROR EXCEPTION;
39 ROW_MISSING_ERROR EXCEPTION;
40
41 /* Declare cursors */
42
43
44 BEGIN
45
46 /* Initialization Routine */
47
48 SAVEPOINT Insert_Row;
49 x_return_status := 'S';
50 x_oracle_error := 0;
51 x_msg_data := NULL;
52
53 /* Now call the check foreign key procedure */
54
55 Check_Foreign_Keys
56 (p_item_code,
57 p_document_code,
58 p_item_document_status,
59 p_item_document_version,
60 p_rebuild_item_doc_flag,
61 p_last_approval_user,
62 p_last_doc_author_lock,
63 p_last_doc_update_date,
64 l_return_status,
65 l_oracle_error,
66 l_msg_data);
67 IF l_return_status <> 'S' THEN
68 RAISE Foreign_Key_Error;
69 END IF;
70
71 /* Now check the primary key doesn't already exist */
72
73 Check_Primary_Key
74 (p_item_code,
75 p_document_code,
76 'F',
77 l_rowid,
78 l_key_exists);
79
80 IF FND_API.To_Boolean(l_key_exists) THEN
81 RAISE Item_Exists_Error;
82 END IF;
83
84 INSERT INTO gr_item_doc_statuses
85 (item_code,
86 document_code,
87 item_document_status,
88 item_document_version,
89 rebuild_item_doc_flag,
90 last_approval_user,
91 last_doc_author_lock,
92 last_doc_update_date,
93 created_by,
94 creation_date,
95 last_updated_by,
96 last_update_date,
97 last_update_login)
98 VALUES
99 (p_item_code,
100 p_document_code,
101 p_item_document_status,
102 p_item_document_version,
103 p_rebuild_item_doc_flag,
104 p_last_approval_user,
105 p_last_doc_author_lock,
106 p_last_doc_update_date,
107 p_created_by,
108 p_creation_date,
109 p_last_updated_by,
110 p_last_update_date,
111 p_last_update_login);
112
113 /* Now get the row id of the inserted record */
114
115 Check_Primary_Key
116 (p_item_code,
117 p_document_code,
118 'F',
119 l_rowid,
120 l_key_exists);
121
122 IF FND_API.To_Boolean(l_key_exists) THEN
123 x_rowid := l_rowid;
124 ELSE
125 RAISE Row_Missing_Error;
126 END IF;
127
128 /* Check the commit flag and if set, then commit the work. */
129
130 IF FND_API.To_Boolean(p_commit) THEN
131 COMMIT WORK;
132 END IF;
133
134 EXCEPTION
135
136 WHEN Foreign_Key_Error THEN
137 ROLLBACK TO SAVEPOINT Insert_Row;
138 x_return_status := l_return_status;
139 x_oracle_error := l_oracle_error;
140 FND_MESSAGE.SET_NAME('GR',
141 'GR_FOREIGN_KEY_ERROR');
142 FND_MESSAGE.SET_TOKEN('TEXT',
143 l_msg_data,
144 FALSE);
145 IF FND_API.To_Boolean(p_called_by_form) THEN
146 APP_EXCEPTION.Raise_Exception;
147 ELSE
148 x_msg_data := FND_MESSAGE.Get;
149 END IF;
150
151 WHEN Item_Exists_Error THEN
152 ROLLBACK TO SAVEPOINT Insert_Row;
153 l_msg_token := p_item_code || ' ' || p_document_code;
154 x_return_status := 'E';
155 x_oracle_error := APP_EXCEPTION.Get_Code;
156 FND_MESSAGE.SET_NAME('GR',
157 'GR_RECORD_THERE');
158 FND_MESSAGE.SET_TOKEN('CODE',
159 l_msg_token,
160 FALSE);
161 IF FND_API.To_Boolean(p_called_by_form) THEN
162 APP_EXCEPTION.Raise_Exception;
163 ELSE
164 x_msg_data := FND_MESSAGE.Get;
165 END IF;
166
167 WHEN Row_Missing_Error THEN
168 ROLLBACK TO SAVEPOINT Insert_Row;
169 l_msg_token := p_item_code || ' ' || p_document_code;
170 x_return_status := 'E';
171 x_oracle_error := APP_EXCEPTION.Get_Code;
172 FND_MESSAGE.SET_NAME('GR',
173 'GR_NO_RECORD_INSERTED');
174 FND_MESSAGE.SET_TOKEN('CODE',
175 l_msg_token,
176 FALSE);
177 IF FND_API.To_Boolean(p_called_by_form) THEN
178 APP_EXCEPTION.Raise_Exception;
179 ELSE
180 x_msg_data := FND_MESSAGE.Get;
181 END IF;
182
183 WHEN OTHERS THEN
184 ROLLBACK TO SAVEPOINT Insert_Row;
185 l_msg_token := p_item_code || ' ' || p_document_code;
186 x_return_status := 'U';
187 x_oracle_error := APP_EXCEPTION.Get_Code;
188 l_msg_data := APP_EXCEPTION.Get_Text;
189 FND_MESSAGE.SET_NAME('GR',
190 'GR_UNEXPECTED_ERROR');
191 FND_MESSAGE.SET_TOKEN('TEXT',
192 l_msg_token,
193 FALSE);
194 IF FND_API.To_Boolean(p_called_by_form) THEN
195 APP_EXCEPTION.Raise_Exception;
196 ELSE
197 x_msg_data := FND_MESSAGE.Get;
198 END IF;
199
200 END Insert_Row;
201
202 PROCEDURE Update_Row
203 (p_commit IN VARCHAR2,
204 p_called_by_form IN VARCHAR2,
205 p_rowid IN VARCHAR2,
206 p_item_code IN VARCHAR2,
207 p_document_code IN VARCHAR2,
208 p_item_document_status IN VARCHAR2,
209 p_item_document_version IN NUMBER,
210 p_rebuild_item_doc_flag IN VARCHAR2,
211 p_last_approval_user IN NUMBER,
212 p_last_doc_author_lock IN NUMBER,
213 p_last_doc_update_date IN DATE,
214 p_created_by IN NUMBER,
215 p_creation_date IN DATE,
216 p_last_updated_by IN NUMBER,
217 p_last_update_date IN DATE,
218 p_last_update_login IN NUMBER,
219 x_return_status OUT NOCOPY VARCHAR2,
220 x_oracle_error OUT NOCOPY NUMBER,
221 x_msg_data OUT NOCOPY VARCHAR2)
222 IS
223
224 /* Alpha Variables */
225
226 L_RETURN_STATUS VARCHAR2(1) := 'S';
227 L_MSG_DATA VARCHAR2(2000);
228 L_MSG_TOKEN VARCHAR2(100);
229
230 /* Number Variables */
231
232 L_ORACLE_ERROR NUMBER;
233
234 /* Exceptions */
235
236 FOREIGN_KEY_ERROR EXCEPTION;
237 ROW_MISSING_ERROR EXCEPTION;
238
239 BEGIN
240
241 /* Initialization Routine */
242
243 SAVEPOINT Update_Row;
244 x_return_status := 'S';
245 x_oracle_error := 0;
246 x_msg_data := NULL;
247 l_msg_token := p_item_code || ' ' || p_document_code;
248
249 /* Now call the check foreign key procedure */
250
251 Check_Foreign_Keys
252 (p_item_code,
253 p_document_code,
254 p_item_document_status,
255 p_item_document_version,
256 p_rebuild_item_doc_flag,
257 p_last_approval_user,
258 p_last_doc_author_lock,
259 p_last_doc_update_date,
260 l_return_status,
261 l_oracle_error,
262 l_msg_data);
263
264 IF l_return_status <> 'S' THEN
265 RAISE Foreign_Key_Error;
266 ELSE
267 UPDATE gr_item_doc_statuses
268 SET item_code = p_item_code,
269 document_code = p_document_code,
270 item_document_status = p_item_document_status,
271 item_document_version = p_item_document_version,
272 rebuild_item_doc_flag = p_rebuild_item_doc_flag,
273 last_approval_user = p_last_approval_user,
274 last_doc_author_lock = p_last_doc_author_lock,
275 last_doc_update_date = p_last_doc_update_date,
276 created_by = p_created_by,
277 creation_date = p_creation_date,
278 last_updated_by = p_last_updated_by,
279 last_update_date = p_last_update_date,
280 last_update_login = p_last_update_login
281 WHERE rowid = p_rowid;
282 IF SQL%NOTFOUND THEN
283 RAISE Row_Missing_Error;
284 END IF;
285 END IF;
286
287 /* Check the commit flag and if set, then commit the work. */
288
289 IF FND_API.To_Boolean(p_commit) THEN
290 COMMIT WORK;
291 END IF;
292
293 EXCEPTION
294
295 WHEN Foreign_Key_Error THEN
296 ROLLBACK TO SAVEPOINT Update_Row;
297 x_return_status := l_return_status;
298 x_oracle_error := l_oracle_error;
299 FND_MESSAGE.SET_NAME('GR',
300 'GR_FOREIGN_KEY_ERROR');
301 FND_MESSAGE.SET_TOKEN('TEXT',
302 l_msg_data,
303 FALSE);
304 IF FND_API.To_Boolean(p_called_by_form) THEN
305 APP_EXCEPTION.Raise_Exception;
306 ELSE
307 x_msg_data := FND_MESSAGE.Get;
308 END IF;
309
310 WHEN Row_Missing_Error THEN
311 ROLLBACK TO SAVEPOINT Update_Row;
312 x_return_status := 'E';
313 x_oracle_error := APP_EXCEPTION.Get_Code;
314 FND_MESSAGE.SET_NAME('GR',
315 'GR_NO_RECORD_INSERTED');
316 FND_MESSAGE.SET_TOKEN('CODE',
317 l_msg_token,
318 FALSE);
319 IF FND_API.To_Boolean(p_called_by_form) THEN
320 APP_EXCEPTION.Raise_Exception;
321 ELSE
322 x_msg_data := FND_MESSAGE.Get;
323 END IF;
324
325 WHEN OTHERS THEN
326 ROLLBACK TO SAVEPOINT Update_Row;
327 x_return_status := 'U';
328 x_oracle_error := APP_EXCEPTION.Get_Code;
329 l_msg_data := APP_EXCEPTION.Get_Text;
330 FND_MESSAGE.SET_NAME('GR',
331 'GR_UNEXPECTED_ERROR');
332 FND_MESSAGE.SET_TOKEN('TEXT',
333 l_msg_token,
334 FALSE);
335 IF FND_API.To_Boolean(p_called_by_form) THEN
336 APP_EXCEPTION.Raise_Exception;
337 ELSE
338 x_msg_data := FND_MESSAGE.Get;
339 END IF;
340
341 END Update_Row;
342
343 PROCEDURE Lock_Row
344 (p_commit IN VARCHAR2,
345 p_called_by_form IN VARCHAR2,
346 p_rowid IN VARCHAR2,
347 p_item_code IN VARCHAR2,
348 p_document_code IN VARCHAR2,
349 p_item_document_status IN VARCHAR2,
350 p_item_document_version IN NUMBER,
351 p_rebuild_item_doc_flag IN VARCHAR2,
352 p_last_approval_user IN NUMBER,
353 p_last_doc_author_lock IN NUMBER,
354 p_last_doc_update_date IN DATE,
355 p_created_by IN NUMBER,
356 p_creation_date IN DATE,
357 p_last_updated_by IN NUMBER,
358 p_last_update_date IN DATE,
359 p_last_update_login IN NUMBER,
360 x_return_status OUT NOCOPY VARCHAR2,
361 x_oracle_error OUT NOCOPY NUMBER,
362 x_msg_data OUT NOCOPY VARCHAR2)
363 IS
364
365 /* Alpha Variables */
366
367 L_RETURN_STATUS VARCHAR2(1) := 'S';
368 L_MSG_DATA VARCHAR2(2000);
369 L_MSG_TOKEN VARCHAR2(100);
370
371 /* Number Variables */
372
373 L_ORACLE_ERROR NUMBER;
374
375 /* Exceptions */
376
377 NO_DATA_FOUND_ERROR EXCEPTION;
378 ROW_ALREADY_LOCKED_ERROR EXCEPTION;
379 PRAGMA EXCEPTION_INIT(ROW_ALREADY_LOCKED_ERROR,-54);
380
381 /* Define the cursors */
382
383 CURSOR c_lock_item_doc_statuses
384 IS
385 SELECT *
386 FROM gr_item_doc_statuses
387 WHERE rowid = p_rowid
388 FOR UPDATE NOWAIT;
389 LockItemDocStatRcd c_lock_item_doc_statuses%ROWTYPE;
390
391 BEGIN
392
393 /* Initialization Routine */
394
395 SAVEPOINT Lock_Row;
396 x_return_status := 'S';
397 x_oracle_error := 0;
398 x_msg_data := NULL;
399 l_msg_token := p_item_code || ' ' || p_document_code;
400
401 /* Now lock the record */
402
403 OPEN c_lock_item_doc_statuses;
404 FETCH c_lock_item_doc_statuses INTO LockItemDocStatRcd;
405 IF c_lock_item_doc_statuses%NOTFOUND THEN
406 CLOSE c_lock_item_doc_statuses;
407 RAISE No_Data_Found_Error;
408 END IF;
409 CLOSE c_lock_item_doc_statuses;
410
411 IF FND_API.To_Boolean(p_commit) THEN
412 COMMIT WORK;
413 END IF;
414
415 EXCEPTION
416
417 WHEN No_Data_Found_Error THEN
418 ROLLBACK TO SAVEPOINT Lock_Row;
419 x_return_status := 'E';
420 FND_MESSAGE.SET_NAME('GR',
421 'GR_RECORD_NOT_FOUND');
422 FND_MESSAGE.SET_TOKEN('CODE',
426 APP_EXCEPTION.Raise_Exception;
423 l_msg_token,
424 FALSE);
425 IF FND_API.To_Boolean(p_called_by_form) THEN
427 ELSE
428 x_msg_data := FND_MESSAGE.Get;
429 END IF;
430
431 WHEN Row_Already_Locked_Error THEN
432 ROLLBACK TO SAVEPOINT Lock_Row;
433 x_return_status := 'E';
434 x_oracle_error := APP_EXCEPTION.Get_Code;
435 FND_MESSAGE.SET_NAME('GR',
436 'GR_ROW_IS_LOCKED');
437 IF FND_API.To_Boolean(p_called_by_form) THEN
438 APP_EXCEPTION.Raise_Exception;
439 ELSE
440 x_msg_data := FND_MESSAGE.Get;
441 END IF;
442
443 WHEN OTHERS THEN
444 ROLLBACK TO SAVEPOINT Lock_Row;
445 x_return_status := 'U';
446 x_oracle_error := APP_EXCEPTION.Get_Code;
447 l_msg_data := APP_EXCEPTION.Get_Text;
448 FND_MESSAGE.SET_NAME('GR',
449 'GR_UNEXPECTED_ERROR');
450 FND_MESSAGE.SET_TOKEN('TEXT',
451 l_msg_token,
452 FALSE);
453 IF FND_API.To_Boolean(p_called_by_form) THEN
454 APP_EXCEPTION.Raise_Exception;
455 ELSE
456 x_msg_data := FND_MESSAGE.Get;
457 END IF;
458
459 END Lock_Row;
460
461 PROCEDURE Delete_Row
462 (p_commit IN VARCHAR2,
463 p_called_by_form IN VARCHAR2,
464 p_rowid IN VARCHAR2,
465 p_item_code IN VARCHAR2,
466 p_document_code IN VARCHAR2,
467 p_item_document_status IN VARCHAR2,
468 p_item_document_version IN NUMBER,
469 p_rebuild_item_doc_flag IN VARCHAR2,
470 p_last_approval_user IN NUMBER,
471 p_last_doc_author_lock IN NUMBER,
472 p_last_doc_update_date IN DATE,
473 p_created_by IN NUMBER,
474 p_creation_date IN DATE,
475 p_last_updated_by IN NUMBER,
476 p_last_update_date IN DATE,
477 p_last_update_login IN NUMBER,
478 x_return_status OUT NOCOPY VARCHAR2,
479 x_oracle_error OUT NOCOPY NUMBER,
480 x_msg_data OUT NOCOPY VARCHAR2)
481 IS
482
483 /* Alpha Variables */
484
485 L_RETURN_STATUS VARCHAR2(1) := 'S';
486 L_MSG_DATA VARCHAR2(2000);
487 L_MSG_TOKEN VARCHAR2(100);
488 L_CALLED_BY_FORM VARCHAR2(1);
489
490 /* Number Variables */
491
492 L_ORACLE_ERROR NUMBER;
493
494 /* Exceptions */
495
496 CHECK_INTEGRITY_ERROR EXCEPTION;
497 ROW_MISSING_ERROR EXCEPTION;
498 PRAGMA EXCEPTION_INIT(Row_Missing_Error,100);
499
500 BEGIN
501
502 /* Initialization Routine */
503
504 SAVEPOINT Delete_Row;
505 x_return_status := 'S';
506 l_called_by_form := 'F';
507 x_oracle_error := 0;
508 x_msg_data := NULL;
509 l_msg_token := p_item_code || ' ' || p_document_code;
510
511 /* Now call the check integrity procedure */
512
513 Check_Integrity
514 (l_called_by_form,
515 p_item_code,
516 p_document_code,
517 p_item_document_status,
518 p_item_document_version,
519 p_rebuild_item_doc_flag,
520 p_last_approval_user,
521 p_last_doc_author_lock,
522 p_last_doc_update_date,
523 l_return_status,
524 l_oracle_error,
525 l_msg_data);
526
527 IF l_return_status <> 'S' THEN
528 RAISE Check_Integrity_Error;
529 END IF;
530
531 DELETE FROM gr_item_doc_statuses
532 WHERE rowid = p_rowid;
533
534 /* Check the commit flag and if set, then commit the work. */
535
536 IF FND_API.TO_Boolean(p_commit) THEN
537 COMMIT WORK;
538 END IF;
539
540 EXCEPTION
541
542 WHEN Check_Integrity_Error THEN
543 ROLLBACK TO SAVEPOINT Delete_Row;
544 x_return_status := l_return_status;
545 x_oracle_error := l_oracle_error;
546 IF FND_API.To_Boolean(p_called_by_form) THEN
547 APP_EXCEPTION.Raise_Exception;
548 ELSE
549 x_msg_data := FND_MESSAGE.Get;
550 END IF;
551
552 WHEN Row_Missing_Error THEN
553 ROLLBACK TO SAVEPOINT Delete_Row;
554 x_return_status := 'E';
555 x_oracle_error := APP_EXCEPTION.Get_Code;
556 FND_MESSAGE.SET_NAME('GR',
557 'GR_RECORD_NOT_FOUND');
558 FND_MESSAGE.SET_TOKEN('CODE',
559 l_msg_token,
560 FALSE);
561 IF FND_API.To_Boolean(p_called_by_form) THEN
562 APP_EXCEPTION.Raise_Exception;
563 ELSE
564 x_msg_data := FND_MESSAGE.Get;
565 END IF;
566
567 WHEN OTHERS THEN
568 ROLLBACK TO SAVEPOINT Delete_Row;
569 x_return_status := 'U';
570 x_oracle_error := APP_EXCEPTION.Get_Code;
571 l_msg_data := APP_EXCEPTION.Get_Text;
572 l_msg_data := APP_EXCEPTION.Get_Text;
573 FND_MESSAGE.SET_NAME('GR',
574 'GR_UNEXPECTED_ERROR');
575 FND_MESSAGE.SET_TOKEN('TEXT',
576 l_msg_token,
577 FALSE);
578 IF FND_API.To_Boolean(p_called_by_form) THEN
579 APP_EXCEPTION.Raise_Exception;
580 ELSE
581 x_msg_data := FND_MESSAGE.Get;
582 END IF;
583
584 END Delete_Row;
585
586 PROCEDURE Delete_Rows
587 (p_commit IN VARCHAR2,
588 p_called_by_form IN VARCHAR2,
589 p_delete_option IN VARCHAR2,
590 p_item_code IN VARCHAR2,
591 p_document_code IN VARCHAR2,
592 x_return_status OUT NOCOPY VARCHAR2,
593 x_oracle_error OUT NOCOPY NUMBER,
594 x_msg_data OUT NOCOPY VARCHAR2)
595 IS
596
597 /* Alpha Variables */
598
599 L_RETURN_STATUS VARCHAR2(1) := 'S';
600 L_MSG_DATA VARCHAR2(2000);
601 L_MSG_TOKEN VARCHAR2(100);
602
603 /* Number Variables */
604
605 L_ORACLE_ERROR NUMBER;
606
607 /* Define the exceptions */
608 NULL_DELETE_OPTION_ERROR EXCEPTION;
609
610 /* Define the cursors */
611
612 BEGIN
613
614 /* Initialization Routine */
615
616 SAVEPOINT Delete_Rows;
617 x_return_status := 'S';
618 x_oracle_error := 0;
619 x_msg_data := NULL;
620
621 /*
622 ** p delete option has one of two values
623 ** 'I' - Delete all rows for the specified item.
624 ** 'D' - Delete all rows for the specified document.
625 */
626 IF p_delete_option = 'I' THEN
627 IF p_item_code IS NULL THEN
628 l_msg_token := 'Item Code';
629 RAISE Null_Delete_Option_Error;
630 ELSE
631 l_msg_token := p_item_code;
632
633 DELETE FROM gr_item_doc_statuses
634 WHERE item_code = p_item_code;
635 END IF;
636 ELSIF p_delete_option = 'D' THEN
637 IF p_document_code IS NULL THEN
638 l_msg_token := 'Document Code';
639 RAISE Null_Delete_Option_Error;
640 ELSE
641 l_msg_token := p_document_code;
642
643 DELETE FROM gr_item_doc_statuses
644 WHERE document_code = p_document_code;
645 END IF;
646 END IF;
647
648 IF FND_API.To_Boolean(p_commit) THEN
649 COMMIT WORK;
650 END IF;
651
652 EXCEPTION
653
654 WHEN Null_Delete_Option_Error THEN
655 x_return_status := 'E';
656 x_oracle_error := APP_EXCEPTION.Get_Code;
657 FND_MESSAGE.SET_NAME('GR',
658 'GR_NULL_VALUE');
659 FND_MESSAGE.SET_TOKEN('CODE',
660 l_msg_token,
661 FALSE);
662 IF FND_API.To_Boolean(p_called_by_form) THEN
663 APP_EXCEPTION.Raise_Exception;
664 ELSE
665 x_msg_data := FND_MESSAGE.Get;
666 END IF;
667
668 WHEN OTHERS THEN
669 ROLLBACK TO SAVEPOINT Delete_Rows;
670 x_return_status := 'U';
671 x_oracle_error := APP_EXCEPTION.Get_Code;
672 l_msg_data := APP_EXCEPTION.Get_Text;
673 FND_MESSAGE.SET_NAME('GR',
674 'GR_UNEXPECTED_ERROR');
675 FND_MESSAGE.SET_TOKEN('TEXT',
676 l_msg_token,
677 FALSE);
678 IF FND_API.To_Boolean(p_called_by_form) THEN
679 APP_EXCEPTION.Raise_Exception;
680 ELSE
681 x_msg_data := FND_MESSAGE.Get;
682 END IF;
683
684 END Delete_Rows;
685
686 PROCEDURE Check_Foreign_Keys
687 (p_item_code IN VARCHAR2,
688 p_document_code IN VARCHAR2,
689 p_item_document_status IN VARCHAR2,
690 p_item_document_version IN NUMBER,
691 p_rebuild_item_doc_flag IN VARCHAR2,
692 p_last_approval_user IN NUMBER,
693 p_last_doc_author_lock IN NUMBER,
694 p_last_doc_update_date IN DATE,
695 x_return_status OUT NOCOPY VARCHAR2,
696 x_oracle_error OUT NOCOPY NUMBER,
697 x_msg_data OUT NOCOPY VARCHAR2)
698 IS
699
700 /* Alpha Variables */
701
702 L_RETURN_STATUS VARCHAR2(1) := 'S';
703 L_MSG_DATA VARCHAR2(2000);
704 L_MSG_TOKEN VARCHAR2(100);
705 L_ROWID VARCHAR2(18);
706 L_KEY_EXISTS VARCHAR2(1);
707
708 /* Number Variables */
709
710 L_ORACLE_ERROR NUMBER;
711
712 /* Error Definitions */
713
714 ROW_MISSING_ERROR EXCEPTION;
715
716 BEGIN
717
718 /* Initialization Routine */
719
720 l_return_status := 'S';
721 x_oracle_error := 0;
722 x_msg_data := NULL;
723 l_msg_token := NULL;
724
725 /* Check the item code */
726
727 GR_ITEM_GENERAL_PKG.Check_Primary_Key
728 (p_item_code,
729 'F',
730 l_rowid,
731 l_key_exists);
732
733 IF NOT FND_API.To_Boolean(l_key_exists) THEN
734 l_return_status := 'E';
735 l_msg_token := l_msg_token || ' ' || p_item_code;
736 END IF;
737
738 /* Check the document code */
739
740 GR_DOCUMENT_CODES_PKG.Check_Primary_Key
741 (p_document_code,
742 'F',
743 l_rowid,
744 l_key_exists);
745
746 IF NOT FND_API.To_Boolean(l_key_exists) THEN
747 l_return_status := 'E';
748 l_msg_token := l_msg_token || ' ' || p_document_code;
749 END IF;
750
751 IF l_return_status <> 'S' THEN
752 RAISE Row_Missing_Error;
753 ELSE
754 x_return_status := 'S';
755 END IF;
756
757 EXCEPTION
758
759 WHEN Row_Missing_Error THEN
760 x_return_status := 'E';
761 x_oracle_error := APP_EXCEPTION.Get_Code;
762 FND_MESSAGE.SET_NAME('GR',
763 'GR_RECORD_NOT_FOUND');
764 FND_MESSAGE.SET_TOKEN('CODE',
765 l_msg_token,
766 FALSE);
767 x_msg_data := FND_MESSAGE.Get;
768
769 WHEN OTHERS THEN
770 x_return_status := 'U';
771 x_oracle_error := APP_EXCEPTION.Get_Code;
772 l_msg_data := APP_EXCEPTION.Get_Text;
773 FND_MESSAGE.SET_NAME('GR',
774 'GR_UNEXPECTED_ERROR');
775 FND_MESSAGE.SET_TOKEN('TEXT',
776 l_msg_token,
777 FALSE);
778 x_msg_data := FND_MESSAGE.Get;
779
780 END Check_Foreign_Keys;
781
782 PROCEDURE Check_Integrity
783 (p_called_by_form IN VARCHAR2,
784 p_item_code IN VARCHAR2,
785 p_document_code IN VARCHAR2,
786 p_item_document_status IN VARCHAR2,
787 p_item_document_version IN NUMBER,
788 p_rebuild_item_doc_flag IN VARCHAR2,
789 p_last_approval_user IN NUMBER,
790 p_last_doc_author_lock IN NUMBER,
791 p_last_doc_update_date IN DATE,
792 x_return_status OUT NOCOPY VARCHAR2,
793 x_oracle_error OUT NOCOPY NUMBER,
794 x_msg_data OUT NOCOPY VARCHAR2)
795 IS
796
797 /* Alpha Variables */
798
799 L_RETURN_STATUS VARCHAR2(1) := 'S';
800 L_MSG_DATA VARCHAR2(2000);
801 L_CODE_BLOCK VARCHAR2(100);
802
803 /* Number Variables */
804
805 L_ORACLE_ERROR NUMBER;
806 L_RECORD_COUNT NUMBER;
807
808 /* Define the Cursors */
809
810 BEGIN
811
812 /* Initialization Routine */
813
814 SAVEPOINT Check_Integrity;
815 x_return_status := 'S';
816 x_oracle_error := 0;
817 x_msg_data := NULL;
818
819 /* No integrity checking is needed */
820
821
822 EXCEPTION
823
824 WHEN OTHERS THEN
825 ROLLBACK TO SAVEPOINT Check_Integrity;
826 x_return_status := 'U';
827 x_oracle_error := APP_EXCEPTION.Get_Code;
828 l_msg_data := APP_EXCEPTION.Get_Text;
829 FND_MESSAGE.SET_NAME('GR',
830 'GR_UNEXPECTED_ERROR');
831 FND_MESSAGE.SET_TOKEN('TEXT',
832 l_msg_data,
833 FALSE);
834 IF FND_API.To_Boolean(p_called_by_form) THEN
835 APP_EXCEPTION.Raise_Exception;
836 ELSE
837 x_msg_data := FND_MESSAGE.Get;
838 END IF;
839
840 END Check_Integrity;
841
842 PROCEDURE Check_Primary_Key
843 /* p_item_code is the item code
844 ** p_document_code is the document code
845 ** p_called_by_form is 'T' if called by a form or 'F' if not.
846 ** x_rowid is the row id of the record if found.
847 ** x_key_exists is 'T' is the record is found, 'F' if not.
848 */
849 (p_item_code IN VARCHAR2,
850 p_document_code IN VARCHAR2,
851 p_called_by_form IN VARCHAR2,
852 x_rowid OUT NOCOPY VARCHAR2,
853 x_key_exists OUT NOCOPY VARCHAR2)
854 IS
855 /* Alphanumeric variables */
856
857 L_MSG_DATA VARCHAR2(100);
858
859 /* Declare any variables and the cursor */
860
861
862 CURSOR c_get_item_doc_stat_rowid
863 IS
864 SELECT ids.rowid
865 FROM gr_item_doc_statuses ids
866 WHERE ids.item_code = p_item_code
867 AND ids.document_code = p_document_code;
868 ItemDocStatRecord c_get_item_doc_stat_rowid%ROWTYPE;
869
870 BEGIN
871
872 l_msg_data := p_item_code || ' ' || p_document_code;
873
874 x_key_exists := 'F';
875 OPEN c_get_item_doc_stat_rowid;
876 FETCH c_get_item_doc_stat_rowid INTO ItemDocStatRecord;
877 IF c_get_item_doc_stat_rowid%FOUND THEN
878 x_key_exists := 'T';
879 x_rowid := ItemDocStatRecord.rowid;
880 ELSE
881 x_key_exists := 'F';
882 END IF;
883 CLOSE c_get_item_doc_stat_rowid;
884
885 EXCEPTION
886
887 WHEN Others THEN
888 l_msg_data := APP_EXCEPTION.Get_Text;
889 FND_MESSAGE.SET_NAME('GR',
890 'GR_UNEXPECTED_ERROR');
891 FND_MESSAGE.SET_TOKEN('TEXT',
892 l_msg_data,
893 FALSE);
894 IF FND_API.To_Boolean(p_called_by_form) THEN
895 APP_EXCEPTION.Raise_Exception;
896 END IF;
897
898 END Check_Primary_Key;
899
900 END GR_ITEM_DOC_STATUSES_PKG;