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