[Home] [Help]
PACKAGE BODY: APPS.GR_SAFETY_CATEGORIES_B_PKG
Source
1 PACKAGE BODY GR_SAFETY_CATEGORIES_B_PKG AS
2 /*$Header: GRHICABB.pls 115.2 2002/10/28 16:50:51 gkelly ship $*/
3 PROCEDURE Insert_Row
4 (p_commit IN VARCHAR2,
5 p_called_by_form IN VARCHAR2,
6 p_safety_category_code IN VARCHAR2,
7 p_last_updated_by IN NUMBER,
8 p_last_update_login IN NUMBER,
9 p_last_update_date IN DATE,
10 p_created_by IN NUMBER,
11 p_creation_date IN DATE,
12 x_rowid OUT NOCOPY VARCHAR2,
13 x_return_status OUT NOCOPY VARCHAR2,
14 x_oracle_error OUT NOCOPY NUMBER,
15 x_msg_data OUT NOCOPY VARCHAR2)
16 IS
17 /* Alpha Variables */
18
19 L_RETURN_STATUS VARCHAR2(1) := 'S';
20 L_KEY_EXISTS VARCHAR2(1);
21 L_MSG_DATA VARCHAR2(2000);
22 L_ROWID VARCHAR2(18);
23 L_MSG_TOKEN VARCHAR2(100);
24
25 /* Number Variables */
26
27 L_ORACLE_ERROR NUMBER;
28 /* Exceptions */
29
30 FOREIGN_KEY_ERROR EXCEPTION;
31 ITEM_EXISTS_ERROR EXCEPTION;
32 ROW_MISSING_ERROR EXCEPTION;
33
34 /* Declare cursors */
35
36
37 BEGIN
38
39 /* Initialization Routine */
40
41 SAVEPOINT Insert_Row;
42 x_return_status := 'S';
43 x_oracle_error := 0;
44 x_msg_data := NULL;
45
46 /* Now call the check foreign key procedure */
47
48 Check_Foreign_Keys
49 (p_safety_category_code,
50 l_return_status,
51 l_oracle_error,
52 l_msg_data);
53 IF l_return_status <> 'S' THEN
54 RAISE Foreign_Key_Error;
55 END IF;
56
57 /* Now check the primary key doesn't already exist */
58
59 Check_Primary_Key
60 (p_safety_category_code,
61 'F',
62 l_rowid,
63 l_key_exists);
64
65 IF FND_API.To_Boolean(l_key_exists) THEN
66 RAISE Item_Exists_Error;
67 END IF;
68
69 INSERT INTO gr_safety_categories_b
70 (safety_category_code,
71 last_updated_by,
72 last_update_login,
73 last_update_date,
74 created_by,
75 creation_date)
76 VALUES
77 (p_safety_category_code,
78 p_last_updated_by,
79 p_last_update_login,
80 p_last_update_date,
81 p_created_by,
82 p_creation_date);
83
84 /* Now get the row id of the inserted record */
85
86 Check_Primary_Key
87 (p_safety_category_code,
88 'F',
89 l_rowid,
90 l_key_exists);
91
92 IF FND_API.To_Boolean(l_key_exists) THEN
93 x_rowid := l_rowid;
94 ELSE
95 RAISE Row_Missing_Error;
96 END IF;
97
98 /* Check the commit flag and if set, then commit the work. */
99
100 IF FND_API.To_Boolean(p_commit) THEN
101 COMMIT WORK;
102 END IF;
103
104 EXCEPTION
105
106 WHEN Foreign_Key_Error THEN
107 ROLLBACK TO SAVEPOINT Insert_Row;
108 x_return_status := l_return_status;
109 x_oracle_error := l_oracle_error;
110 FND_MESSAGE.SET_NAME('GR',
111 'GR_FOREIGN_KEY_ERROR');
112 FND_MESSAGE.SET_TOKEN('TEXT',
113 l_msg_data,
114 FALSE);
115 IF FND_API.To_Boolean(p_called_by_form) THEN
116 APP_EXCEPTION.Raise_Exception;
117 ELSE
118 x_msg_data := FND_MESSAGE.Get;
119 END IF;
120
121 WHEN Item_Exists_Error THEN
122 ROLLBACK TO SAVEPOINT Insert_Row;
123 l_msg_token := p_safety_category_code;
124 x_return_status := 'E';
125 x_oracle_error := APP_EXCEPTION.Get_Code;
126 FND_MESSAGE.SET_NAME('GR',
127 'GR_RECORD_EXISTS');
128 FND_MESSAGE.SET_TOKEN('CODE',
129 l_msg_token,
130 FALSE);
131 IF FND_API.To_Boolean(p_called_by_form) THEN
132 APP_EXCEPTION.Raise_Exception;
133 ELSE
134 x_msg_data := FND_MESSAGE.Get;
135 END IF;
136
137 WHEN Row_Missing_Error THEN
138 ROLLBACK TO SAVEPOINT Insert_Row;
139 l_msg_token := p_safety_category_code;
140 x_return_status := 'E';
141 x_oracle_error := APP_EXCEPTION.Get_Code;
142 FND_MESSAGE.SET_NAME('GR',
143 'GR_NO_RECORD_INSERTED');
144 FND_MESSAGE.SET_TOKEN('CODE',
145 l_msg_token,
146 FALSE);
147 IF FND_API.To_Boolean(p_called_by_form) THEN
148 APP_EXCEPTION.Raise_Exception;
149 ELSE
150 x_msg_data := FND_MESSAGE.Get;
151 END IF;
152
153 WHEN OTHERS THEN
154 ROLLBACK TO SAVEPOINT Insert_Row;
155 l_msg_token := p_safety_category_code;
156 x_return_status := 'U';
157 x_oracle_error := APP_EXCEPTION.Get_Code;
158 l_msg_data := APP_EXCEPTION.Get_Text;
159 FND_MESSAGE.SET_NAME('GR',
160 'GR_UNEXPECTED_ERROR');
161 FND_MESSAGE.SET_TOKEN('TEXT',
162 l_msg_token,
163 FALSE);
164 IF FND_API.To_Boolean(p_called_by_form) THEN
165 APP_EXCEPTION.Raise_Exception;
166 ELSE
167 x_msg_data := FND_MESSAGE.Get;
168 END IF;
169
170 END Insert_Row;
171
172 PROCEDURE Update_Row
173 (p_commit IN VARCHAR2,
174 p_called_by_form IN VARCHAR2,
175 p_rowid IN VARCHAR2,
176 p_safety_category_code IN VARCHAR2,
177 p_last_updated_by IN NUMBER,
178 p_last_update_login IN NUMBER,
179 p_last_update_date IN DATE,
180 p_created_by IN NUMBER,
181 p_creation_date IN DATE,
182 x_return_status OUT NOCOPY VARCHAR2,
183 x_oracle_error OUT NOCOPY NUMBER,
184 x_msg_data OUT NOCOPY VARCHAR2)
185 IS
186
187 /* Alpha Variables */
188
189 L_RETURN_STATUS VARCHAR2(1) := 'S';
190 L_MSG_DATA VARCHAR2(2000);
191 L_MSG_TOKEN VARCHAR2(100);
192
193 /* Number Variables */
194
195 L_ORACLE_ERROR NUMBER;
196
197 /* Exceptions */
198
199 FOREIGN_KEY_ERROR EXCEPTION;
200 ROW_MISSING_ERROR EXCEPTION;
201
202 BEGIN
203
204 /* Initialization Routine */
205
206 SAVEPOINT Update_Row;
207 x_return_status := 'S';
208 x_oracle_error := 0;
209 x_msg_data := NULL;
210 l_msg_token := p_safety_category_code;
211
212 /* Now call the check foreign key procedure */
213
214 Check_Foreign_Keys
215 (p_safety_category_code,
216 l_return_status,
217 l_oracle_error,
218 l_msg_data);
219
220 IF l_return_status <> 'S' THEN
221 RAISE Foreign_Key_Error;
222 ELSE
223 UPDATE gr_safety_categories_b
224 SET safety_category_code = p_safety_category_code,
225 last_updated_by = p_last_updated_by,
226 last_update_login = p_last_update_login,
227 last_update_date = p_last_update_date,
228 created_by = p_created_by,
229 creation_date = p_creation_date
230 WHERE rowid = p_rowid;
231 IF SQL%NOTFOUND THEN
232 RAISE Row_Missing_Error;
233 END IF;
234 END IF;
235
236 /* Check the commit flag and if set, then commit the work. */
237
238 IF FND_API.To_Boolean(p_commit) THEN
239 COMMIT WORK;
240 END IF;
241
242 EXCEPTION
243
244 WHEN Foreign_Key_Error THEN
245 ROLLBACK TO SAVEPOINT Update_Row;
246 x_return_status := l_return_status;
247 x_oracle_error := l_oracle_error;
248 FND_MESSAGE.SET_NAME('GR',
249 'GR_FOREIGN_KEY_ERROR');
250 FND_MESSAGE.SET_TOKEN('TEXT',
251 l_msg_data,
252 FALSE);
253 IF FND_API.To_Boolean(p_called_by_form) THEN
254 APP_EXCEPTION.Raise_Exception;
255 ELSE
256 x_msg_data := FND_MESSAGE.Get;
257 END IF;
258
259 WHEN Row_Missing_Error THEN
260 ROLLBACK TO SAVEPOINT Update_Row;
261 x_return_status := 'E';
262 x_oracle_error := APP_EXCEPTION.Get_Code;
263 FND_MESSAGE.SET_NAME('GR',
264 'GR_NO_RECORD_INSERTED');
265 FND_MESSAGE.SET_TOKEN('CODE',
266 l_msg_token,
267 FALSE);
268 IF FND_API.To_Boolean(p_called_by_form) THEN
269 APP_EXCEPTION.Raise_Exception;
270 ELSE
271 x_msg_data := FND_MESSAGE.Get;
272 END IF;
273
274 WHEN OTHERS THEN
275 ROLLBACK TO SAVEPOINT Update_Row;
276 x_return_status := 'U';
277 x_oracle_error := APP_EXCEPTION.Get_Code;
278 l_msg_data := APP_EXCEPTION.Get_Text;
279 FND_MESSAGE.SET_NAME('GR',
280 'GR_UNEXPECTED_ERROR');
281 FND_MESSAGE.SET_TOKEN('TEXT',
282 l_msg_token,
283 FALSE);
284 IF FND_API.To_Boolean(p_called_by_form) THEN
285 APP_EXCEPTION.Raise_Exception;
286 ELSE
287 x_msg_data := FND_MESSAGE.Get;
288 END IF;
289
290 END Update_Row;
291 /*
292 **
293 **
294 **
295 */
296 PROCEDURE Lock_Row
297 (p_commit IN VARCHAR2,
298 p_called_by_form IN VARCHAR2,
299 p_rowid IN VARCHAR2,
300 p_safety_category_code IN VARCHAR2,
301 p_last_updated_by IN NUMBER,
302 p_last_update_login IN NUMBER,
303 p_last_update_date IN DATE,
304 p_created_by IN NUMBER,
305 p_creation_date IN DATE,
306 x_return_status OUT NOCOPY VARCHAR2,
307 x_oracle_error OUT NOCOPY NUMBER,
308 x_msg_data OUT NOCOPY VARCHAR2)
309 IS
310
311 /* Alpha Variables */
312
313 L_RETURN_STATUS VARCHAR2(1) := 'S';
314 L_MSG_DATA VARCHAR2(2000);
315 L_MSG_TOKEN VARCHAR2(100);
316
317 /* Number Variables */
318
319 L_ORACLE_ERROR NUMBER;
320
321 /* Exceptions */
322
323 NO_DATA_FOUND_ERROR EXCEPTION;
324 ROW_ALREADY_LOCKED_ERROR EXCEPTION;
325 PRAGMA EXCEPTION_INIT(ROW_ALREADY_LOCKED_ERROR,-54);
326
327 /* Define the cursors */
328
329 CURSOR c_lock_safety_cat_b
330 IS
331 SELECT *
332 FROM gr_safety_categories_b
333 WHERE rowid = p_rowid
334 FOR UPDATE NOWAIT;
335 LockCategoryRcd c_lock_safety_cat_b%ROWTYPE;
336
337 BEGIN
338
339 /* Initialization Routine */
340
341 SAVEPOINT Lock_Row;
342 x_return_status := 'S';
343 x_oracle_error := 0;
344 x_msg_data := NULL;
345 l_msg_token := p_safety_category_code;
346
347 /* Now lock the record */
348
349 OPEN c_lock_safety_cat_b;
350 FETCH c_lock_safety_cat_b INTO LockCategoryRcd;
351 IF c_lock_safety_cat_b%NOTFOUND THEN
352 CLOSE c_lock_safety_cat_b;
353 RAISE No_Data_Found_Error;
354 END IF;
355 CLOSE c_lock_safety_cat_b;
356
357 IF FND_API.To_Boolean(p_commit) THEN
358 COMMIT WORK;
359 END IF;
360
361 EXCEPTION
362
363 WHEN No_Data_Found_Error THEN
364 ROLLBACK TO SAVEPOINT Lock_Row;
365 x_return_status := 'E';
366 FND_MESSAGE.SET_NAME('GR',
367 'GR_RECORD_NOT_FOUND');
368 FND_MESSAGE.SET_TOKEN('CODE',
369 l_msg_token,
370 FALSE);
371 IF FND_API.To_Boolean(p_called_by_form) THEN
372 APP_EXCEPTION.Raise_Exception;
373 ELSE
374 x_msg_data := FND_MESSAGE.Get;
375 END IF;
376
377 WHEN Row_Already_Locked_Error THEN
378 ROLLBACK TO SAVEPOINT Lock_Row;
379 x_return_status := 'E';
380 x_oracle_error := APP_EXCEPTION.Get_Code;
381 FND_MESSAGE.SET_NAME('GR',
382 'GR_ROW_IS_LOCKED');
383 IF FND_API.To_Boolean(p_called_by_form) THEN
384 APP_EXCEPTION.Raise_Exception;
385 ELSE
386 x_msg_data := FND_MESSAGE.Get;
387 END IF;
388
389 WHEN OTHERS THEN
390 ROLLBACK TO SAVEPOINT Lock_Row;
391 x_return_status := 'U';
392 x_oracle_error := APP_EXCEPTION.Get_Code;
393 l_msg_data := APP_EXCEPTION.Get_Text;
394 FND_MESSAGE.SET_NAME('GR',
395 'GR_UNEXPECTED_ERROR');
396 FND_MESSAGE.SET_TOKEN('TEXT',
397 l_msg_token,
398 FALSE);
399 IF FND_API.To_Boolean(p_called_by_form) THEN
400 APP_EXCEPTION.Raise_Exception;
401 ELSE
402 x_msg_data := FND_MESSAGE.Get;
406
403 END IF;
404
405 END Lock_Row;
407 PROCEDURE Delete_Row
408 (p_commit IN VARCHAR2,
409 p_called_by_form IN VARCHAR2,
410 p_rowid IN VARCHAR2,
411 p_safety_category_code IN VARCHAR2,
412 p_last_updated_by IN NUMBER,
413 p_last_update_login IN NUMBER,
414 p_last_update_date IN DATE,
415 p_created_by IN NUMBER,
416 p_creation_date IN DATE,
417 x_return_status OUT NOCOPY VARCHAR2,
418 x_oracle_error OUT NOCOPY NUMBER,
419 x_msg_data OUT NOCOPY VARCHAR2)
420 IS
421
422 /* Alpha Variables */
423
424 L_RETURN_STATUS VARCHAR2(1) := 'S';
425 L_MSG_DATA VARCHAR2(2000);
426 L_MSG_TOKEN VARCHAR2(100);
427 L_CALLED_BY_FORM VARCHAR2(1);
428
429 /* Number Variables */
430
431 L_ORACLE_ERROR NUMBER;
432
433 /* Exceptions */
434
435 CHECK_INTEGRITY_ERROR EXCEPTION;
436 ROW_MISSING_ERROR EXCEPTION;
437 PRAGMA EXCEPTION_INIT(Row_Missing_Error,100);
438
439 /* Define the cursor */
440
441 BEGIN
442
443 /* Initialization Routine */
444
445 SAVEPOINT Delete_Row;
446 x_return_status := 'S';
447 l_called_by_form := 'F';
448 x_oracle_error := 0;
449 x_msg_data := NULL;
450 l_msg_token := p_safety_category_code;
451
452 /* Now call the check integrity procedure */
453
454 Check_Integrity
455 (l_called_by_form,
456 p_safety_category_code,
457 l_return_status,
458 l_oracle_error,
459 l_msg_data);
460
461 IF l_return_status <> 'S' THEN
462 RAISE Check_Integrity_Error;
463 END IF;
464
465 DELETE FROM gr_safety_categories_b
466 WHERE rowid = p_rowid;
467
468 /* Check the commit flag and if set, then commit the work. */
469
470 IF FND_API.TO_Boolean(p_commit) THEN
471 COMMIT WORK;
472 END IF;
473
474 EXCEPTION
475
476 WHEN Check_Integrity_Error THEN
477 ROLLBACK TO SAVEPOINT Delete_Row;
478 x_return_status := l_return_status;
479 x_oracle_error := l_oracle_error;
480 IF FND_API.To_Boolean(p_called_by_form) THEN
481 APP_EXCEPTION.Raise_Exception;
482 ELSE
483 x_msg_data := FND_MESSAGE.Get;
484 END IF;
485
486 WHEN Row_Missing_Error THEN
487 ROLLBACK TO SAVEPOINT Delete_Row;
488 x_return_status := 'E';
489 x_oracle_error := APP_EXCEPTION.Get_Code;
490 FND_MESSAGE.SET_NAME('GR',
491 'GR_RECORD_NOT_FOUND');
492 FND_MESSAGE.SET_TOKEN('CODE',
493 l_msg_token,
494 FALSE);
495 IF FND_API.To_Boolean(p_called_by_form) THEN
496 APP_EXCEPTION.Raise_Exception;
497 ELSE
498 x_msg_data := FND_MESSAGE.Get;
499 END IF;
500
501 WHEN OTHERS THEN
502 ROLLBACK TO SAVEPOINT Delete_Row;
503 x_return_status := 'U';
504 x_oracle_error := APP_EXCEPTION.Get_Code;
505 l_msg_data := APP_EXCEPTION.Get_Text;
506 FND_MESSAGE.SET_NAME('GR',
507 'GR_UNEXPECTED_ERROR');
508 FND_MESSAGE.SET_TOKEN('TEXT',
509 l_msg_data,
510 FALSE);
511 IF FND_API.To_Boolean(p_called_by_form) THEN
512 APP_EXCEPTION.Raise_Exception;
513 ELSE
514 x_msg_data := FND_MESSAGE.Get;
515 END IF;
516
517 END Delete_Row;
518
519 PROCEDURE Check_Foreign_Keys
520 (p_safety_category_code IN VARCHAR2,
521 x_return_status OUT NOCOPY VARCHAR2,
522 x_oracle_error OUT NOCOPY NUMBER,
523 x_msg_data OUT NOCOPY VARCHAR2)
524 IS
525
526 /* Alpha Variables */
527
528 L_RETURN_STATUS VARCHAR2(1) := 'S';
529 L_MSG_DATA VARCHAR2(2000);
530 L_MSG_TOKEN VARCHAR2(100);
531
532 /* Number Variables */
533
534 L_ORACLE_ERROR NUMBER;
535
536 /* Error Definitions */
537
538 ROW_MISSING_ERROR EXCEPTION;
539
540 /*
541 ** Define the cursors
542 */
543
544 BEGIN
545
546 /* Initialization Routine */
547
548 SAVEPOINT Check_Foreign_Keys;
549 x_return_status := 'S';
550 x_oracle_error := 0;
551 x_msg_data := NULL;
552 l_msg_token := p_safety_category_code;
553
554 /*
555 ** No foreign key references
556 */
557
558 EXCEPTION
559
560 WHEN Row_Missing_Error THEN
561 ROLLBACK TO SAVEPOINT Delete_Row;
562 x_return_status := 'E';
563 x_oracle_error := APP_EXCEPTION.Get_Code;
564 FND_MESSAGE.SET_NAME('GR',
565 'GR_RECORD_NOT_FOUND');
566 FND_MESSAGE.SET_TOKEN('CODE',
567 p_safety_category_code,
568 FALSE);
569 x_msg_data := FND_MESSAGE.Get;
570
571 WHEN OTHERS THEN
572 ROLLBACK TO SAVEPOINT Check_Foreign_Keys;
573 x_return_status := 'U';
574 x_oracle_error := APP_EXCEPTION.Get_Code;
575 l_msg_data := APP_EXCEPTION.Get_Text;
576 FND_MESSAGE.SET_NAME('GR',
577 'GR_UNEXPECTED_ERROR');
578 FND_MESSAGE.SET_TOKEN('TEXT',
579 l_msg_token,
580 FALSE);
581 x_msg_data := FND_MESSAGE.Get;
582
583 END Check_Foreign_Keys;
584
585 PROCEDURE Check_Integrity
586 (p_called_by_form IN VARCHAR2,
587 p_safety_category_code IN VARCHAR2,
588 x_return_status OUT NOCOPY VARCHAR2,
589 x_oracle_error OUT NOCOPY NUMBER,
590 x_msg_data OUT NOCOPY VARCHAR2)
591 IS
592
593 /* Alpha Variables */
594
595 L_RETURN_STATUS VARCHAR2(1) := 'S';
596 L_MSG_DATA VARCHAR2(2000);
597 L_CODE_BLOCK VARCHAR2(100);
598
599 /* Number Variables */
600
601 L_ORACLE_ERROR NUMBER;
602 L_RECORD_COUNT NUMBER;
603
604 /* Exceptions */
605 INTEGRITY_ERROR EXCEPTION;
606
607 /*
608 ** Define the Cursors
609 **
610 ** Document Headings
611 */
612 CURSOR c_get_safety_tl
613 IS
614 SELECT COUNT(*)
615 FROM gr_safety_categories_tl sct
616 WHERE sct.safety_category_code = p_safety_category_code;
617
618 /*
619 ** Label Codes
620 */
621 CURSOR c_get_labels
622 IS
623 SELECT COUNT(*)
624 FROM gr_labels_b lab
625 WHERE lab.safety_category_code = p_safety_category_code;
626
627 BEGIN
628
629 /* Initialization Routine */
630
631 SAVEPOINT Check_Integrity;
632 x_return_status := 'S';
633 x_oracle_error := 0;
634 x_msg_data := NULL;
635 l_record_count := 0;
636
637 /*
638 ** Check safety_tl
639 */
640 OPEN c_get_safety_tl;
641 FETCH c_get_safety_tl INTO l_record_count;
642 IF l_record_count <> 0 THEN
643 l_return_status := 'E';
644 l_msg_data := l_msg_data || ' gr_safety_categories_tl,';
645 END IF;
646 CLOSE c_get_safety_tl;
647 /*
648 ** Check labels
649 */
650 l_record_count := 0;
651 OPEN c_get_labels;
652 FETCH c_get_labels INTO l_record_count;
653 IF l_record_count <> 0 THEN
654 l_return_status := 'E';
655 l_msg_data := l_msg_data || ' gr_labels_b,';
656 END IF;
657 CLOSE c_get_labels;
658
659 IF l_return_status <> 'S' THEN
660 RAISE INTEGRITY_ERROR;
661 END IF;
662
663 EXCEPTION
664
665 WHEN INTEGRITY_ERROR THEN
666 x_return_status := 'E';
667 FND_MESSAGE.SET_NAME('GR',
668 'GR_INTEGRITY_HEADER');
669 FND_MESSAGE.SET_TOKEN('CODE',
670 p_safety_category_code,
671 FALSE);
672 FND_MESSAGE.SET_TOKEN('TABLES',
673 SUBSTR(l_msg_data,1,LENGTH(l_msg_data)-1),
674 FALSE);
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 WHEN OTHERS THEN
681 ROLLBACK TO SAVEPOINT Check_Integrity;
682 x_return_status := 'U';
683 x_oracle_error := APP_EXCEPTION.Get_Code;
684 l_msg_data := APP_EXCEPTION.Get_Text;
685 FND_MESSAGE.SET_NAME('GR',
686 'GR_UNEXPECTED_ERROR');
687 FND_MESSAGE.SET_TOKEN('TEXT',
688 l_msg_data,
689 FALSE);
690 IF FND_API.To_Boolean(p_called_by_form) THEN
691 APP_EXCEPTION.Raise_Exception;
692 ELSE
693 x_msg_data := FND_MESSAGE.Get;
694 END IF;
695
696 END Check_Integrity;
697
698 PROCEDURE Check_Primary_Key
699 /* p_safety_category_code is the code to check.
700 ** p_called_by_form is 'T' if called by a form or 'F' if not.
701 ** x_rowid is the row id of the record if found.
702 ** x_key_exists is 'T' is the record is found, 'F' if not.
703 */
704 (p_safety_category_code IN VARCHAR2,
705 p_called_by_form IN VARCHAR2,
706 x_rowid OUT NOCOPY VARCHAR2,
707 x_key_exists OUT NOCOPY VARCHAR2)
708 IS
709 /* Alphanumeric variables */
710
711 L_MSG_DATA VARCHAR2(80);
712
713 /* Declare any variables and the cursor */
714
715
716 CURSOR c_get_category_b_rowid
717 IS
718 SELECT scb.rowid
719 FROM gr_safety_categories_b scb
720 WHERE scb.safety_category_code = p_safety_category_code;
721 CategoryBRecord c_get_category_b_rowid%ROWTYPE;
722
723 BEGIN
724
725 l_msg_data := p_safety_category_code;
726
727 x_key_exists := 'F';
728 OPEN c_get_category_b_rowid;
729 FETCH c_get_category_b_rowid INTO CategoryBRecord;
730 IF c_get_category_b_rowid%FOUND THEN
731 x_key_exists := 'T';
732 x_rowid := CategoryBRecord.rowid;
733 ELSE
734 x_key_exists := 'F';
735 END IF;
736 CLOSE c_get_category_b_rowid;
737
738 EXCEPTION
739
740 WHEN Others THEN
741 l_msg_data := APP_EXCEPTION.Get_Text;
742 FND_MESSAGE.SET_NAME('GR',
743 'GR_UNEXPECTED_ERROR');
744 FND_MESSAGE.SET_TOKEN('TEXT',
745 l_msg_data,
746 FALSE);
747 IF FND_API.To_Boolean(p_called_by_form) THEN
748 APP_EXCEPTION.Raise_Exception;
749 END IF;
750
751 END Check_Primary_Key;
752
753 END GR_SAFETY_CATEGORIES_B_PKG;