[Home] [Help]
PACKAGE BODY: APPS.GR_DOCUMENT_CODES_PKG
Source
1 PACKAGE BODY GR_DOCUMENT_CODES_PKG AS
2 /*$Header: GRHIDCB.pls 115.13 2002/10/28 19:12:23 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_user_id IN NUMBER,
8 p_document_version IN NUMBER,
9 p_document_description IN VARCHAR2,
10 p_document_history_flag IN VARCHAR2,
11 p_allow_user_override IN VARCHAR2,
12 p_document_date_format IN VARCHAR2,
13 p_print_ingredients_flag IN VARCHAR2,
14 p_ingredient_header_label IN VARCHAR2,
15 p_ingredient_conc_ind IN VARCHAR2,
16 p_cas_number_seq IN NUMBER,
17 p_msds_name_seq IN NUMBER,
18 p_concentration_seq IN NUMBER,
19 p_hmis_code_seq IN NUMBER,
20 p_nfpa_code_seq IN NUMBER,
21 p_user_code_seq IN NUMBER,
22 p_eec_number_seq IN NUMBER,
23 p_hazard_symbol_seq IN NUMBER,
24 p_risk_phrase_seq IN NUMBER,
25 p_safety_phrase_seq IN NUMBER,
26 p_print_toxic_info_flag IN VARCHAR2,
27 p_toxic_header_label IN VARCHAR2,
28 p_toxic_cas_number_seq IN NUMBER,
29 p_toxic_msds_name_seq IN NUMBER,
30 p_toxic_route_seq IN NUMBER,
31 p_toxic_species_seq IN NUMBER,
32 p_toxic_exposure_seq IN NUMBER,
33 p_toxic_dose_seq IN NUMBER,
34 p_toxic_note_seq IN NUMBER,
35 p_print_exposure_flag IN VARCHAR2,
36 p_exposure_header_label IN VARCHAR2,
37 p_exposure_cas_number_seq IN NUMBER,
38 p_exposure_msds_name_seq IN NUMBER,
39 p_exposure_authority_seq IN NUMBER,
40 p_exposure_type_seq IN NUMBER,
41 p_exposure_dose_seq IN NUMBER,
42 p_exposure_note_seq IN NUMBER,
43 p_include_qc_data_flag IN VARCHAR2,
44 p_prop_65_conc_ind IN VARCHAR2,
45 p_sara_312_conc_ind IN VARCHAR2,
46 p_print_rtk_on_document IN VARCHAR2,
47 p_rtk_header IN VARCHAR2,
48 p_print_all_state_indicator IN VARCHAR2,
49 p_approval_process_flag IN VARCHAR2,
50 p_attribute_category IN VARCHAR2,
51 p_attribute1 IN VARCHAR2,
52 p_attribute2 IN VARCHAR2,
53 p_attribute3 IN VARCHAR2,
54 p_attribute4 IN VARCHAR2,
55 p_attribute5 IN VARCHAR2,
56 p_attribute6 IN VARCHAR2,
57 p_attribute7 IN VARCHAR2,
58 p_attribute8 IN VARCHAR2,
59 p_attribute9 IN VARCHAR2,
60 p_attribute10 IN VARCHAR2,
61 p_attribute11 IN VARCHAR2,
62 p_attribute12 IN VARCHAR2,
63 p_attribute13 IN VARCHAR2,
64 p_attribute14 IN VARCHAR2,
65 p_attribute15 IN VARCHAR2,
66 p_attribute16 IN VARCHAR2,
67 p_attribute17 IN VARCHAR2,
68 p_attribute18 IN VARCHAR2,
69 p_attribute19 IN VARCHAR2,
70 p_attribute20 IN VARCHAR2,
71 p_attribute21 IN VARCHAR2,
72 p_attribute22 IN VARCHAR2,
73 p_attribute23 IN VARCHAR2,
74 p_attribute24 IN VARCHAR2,
75 p_attribute25 IN VARCHAR2,
76 p_attribute26 IN VARCHAR2,
77 p_attribute27 IN VARCHAR2,
78 p_attribute28 IN VARCHAR2,
79 p_attribute29 IN VARCHAR2,
80 p_attribute30 IN VARCHAR2,
81 p_created_by IN NUMBER,
82 p_creation_date IN DATE,
83 p_last_updated_by IN NUMBER,
84 p_last_update_date IN DATE,
85 p_last_update_login IN NUMBER,
86 x_rowid OUT NOCOPY VARCHAR2,
87 x_return_status OUT NOCOPY VARCHAR2,
88 x_oracle_error OUT NOCOPY NUMBER,
89 x_msg_data OUT NOCOPY VARCHAR2)
90 IS
91 /* Alpha Variables */
92
93 L_RETURN_STATUS VARCHAR2(1) := 'S';
94 L_KEY_EXISTS VARCHAR2(1);
95 L_MSG_DATA VARCHAR2(2000);
96 L_ROWID VARCHAR2(18);
97
98 /* Number Variables */
99
100 L_ORACLE_ERROR NUMBER;
101
102 /* Exceptions */
103
104 FOREIGN_KEY_ERROR EXCEPTION;
105 LABEL_EXISTS_ERROR EXCEPTION;
106 ROW_MISSING_ERROR EXCEPTION;
107
108 /* Declare cursors */
109
110 BEGIN
111
112 /* Initialization Routine */
113
114 SAVEPOINT Insert_Row;
115 x_return_status := 'S';
116 x_oracle_error := 0;
117 x_msg_data := NULL;
118
119 /* Now call the check foreign key procedure */
120
121 Check_Foreign_Keys
122 (p_document_code,
123 p_user_id,
124 p_document_version,
125 p_document_description,
126 p_document_history_flag,
127 p_allow_user_override,
128 p_document_date_format,
129 p_print_ingredients_flag,
130 p_ingredient_header_label,
131 p_ingredient_conc_ind,
132 p_cas_number_seq,
133 p_msds_name_seq,
134 p_concentration_seq,
135 p_hmis_code_seq,
136 p_nfpa_code_seq,
137 p_user_code_seq,
138 p_eec_number_seq,
139 p_hazard_symbol_seq,
140 p_risk_phrase_seq,
141 p_safety_phrase_seq,
142 p_print_toxic_info_flag,
143 p_toxic_header_label,
144 p_toxic_cas_number_seq,
145 p_toxic_msds_name_seq,
146 p_toxic_route_seq,
147 p_toxic_species_seq,
148 p_toxic_exposure_seq,
149 p_toxic_dose_seq,
150 p_toxic_note_seq,
151 p_print_exposure_flag,
152 p_exposure_header_label,
153 p_exposure_cas_number_seq,
154 p_exposure_msds_name_seq,
155 p_exposure_authority_seq,
156 p_exposure_type_seq,
157 p_exposure_dose_seq,
158 p_exposure_note_seq,
159 p_include_qc_data_flag,
160 p_prop_65_conc_ind,
161 p_sara_312_conc_ind,
162 p_print_rtk_on_document,
163 p_rtk_header,
164 p_print_all_state_indicator,
165 p_approval_process_flag,
166 p_attribute_category,
167 p_attribute1,
168 p_attribute2,
169 p_attribute3,
170 p_attribute4,
171 p_attribute5,
172 p_attribute6,
173 p_attribute7,
174 p_attribute8,
175 p_attribute9,
176 p_attribute10,
177 p_attribute11,
178 p_attribute12,
179 p_attribute13,
180 p_attribute14,
181 p_attribute15,
182 p_attribute16,
183 p_attribute17,
184 p_attribute18,
185 p_attribute19,
186 p_attribute20,
187 p_attribute21,
188 p_attribute22,
189 p_attribute23,
190 p_attribute24,
191 p_attribute25,
192 p_attribute26,
193 p_attribute27,
194 p_attribute28,
195 p_attribute29,
196 p_attribute30,
197 l_return_status,
198 l_oracle_error,
199 l_msg_data);
200 IF l_return_status <> 'S' THEN
201 RAISE Foreign_Key_Error;
202 END IF;
203
204 /* Now check the primary key doesn't already exist */
205
206 Check_Primary_Key
207 (p_document_code,
208 'F',
209 l_rowid,
210 l_key_exists);
211
212 IF FND_API.To_Boolean(l_key_exists) THEN
213 RAISE Label_Exists_Error;
214 END IF;
215
216 INSERT INTO gr_document_codes
217 (document_code,
218 user_id,
219 document_version,
220 document_description,
221 document_history_flag,
222 allow_user_override,
223 document_date_format,
224 print_ingredients_flag,
225 ingredient_header_label,
226 ingredient_conc_ind,
227 cas_number_seq,
228 msds_name_seq,
229 concentration_seq,
230 hmis_code_seq,
231 nfpa_code_seq,
232 user_code_seq,
233 eec_number_seq,
234 hazard_symbol_seq,
235 risk_phrase_seq,
236 safety_phrase_seq,
237 print_toxic_info_flag,
238 toxic_header_label,
239 toxic_cas_number_seq,
240 toxic_msds_name_seq,
241 toxic_route_seq,
242 toxic_species_seq,
243 toxic_exposure_seq,
244 toxic_dose_seq,
245 toxic_note_seq,
246 print_exposure_flag,
247 exposure_header_label,
248 exposure_cas_number_seq,
249 exposure_msds_name_seq,
250 exposure_authority_seq,
251 exposure_type_seq,
252 exposure_dose_seq,
253 exposure_note_seq,
254 include_qc_data_flag,
255 prop_65_conc_ind,
256 sara_312_conc_ind,
257 print_rtk_on_document,
258 rtk_header,
259 print_all_state_indicator,
260 approval_process_flag,
261 attribute_category,
262 attribute1,
263 attribute2,
264 attribute3,
265 attribute4,
266 attribute5,
267 attribute6,
268 attribute7,
269 attribute8,
270 attribute9,
271 attribute10,
272 attribute11,
273 attribute12,
274 attribute13,
275 attribute14,
276 attribute15,
277 attribute16,
278 attribute17,
279 attribute18,
280 attribute19,
281 attribute20,
282 attribute21,
283 attribute22,
284 attribute23,
285 attribute24,
286 attribute25,
287 attribute26,
288 attribute27,
289 attribute28,
290 attribute29,
291 attribute30,
292 created_by,
293 creation_date,
294 last_updated_by,
295 last_update_date,
296 last_update_login)
297 VALUES
298 (p_document_code,
299 p_user_id,
300 p_document_version,
301 p_document_description,
302 p_document_history_flag,
303 p_allow_user_override,
304 p_document_date_format,
305 p_print_ingredients_flag,
306 p_ingredient_header_label,
307 p_ingredient_conc_ind,
308 p_cas_number_seq,
309 p_msds_name_seq,
310 p_concentration_seq,
311 p_hmis_code_seq,
312 p_nfpa_code_seq,
313 p_user_code_seq,
314 p_eec_number_seq,
315 p_hazard_symbol_seq,
316 p_risk_phrase_seq,
317 p_safety_phrase_seq,
318 p_print_toxic_info_flag,
319 p_toxic_header_label,
320 p_toxic_cas_number_seq,
321 p_toxic_msds_name_seq,
322 p_toxic_route_seq,
323 p_toxic_species_seq,
324 p_toxic_exposure_seq,
325 p_toxic_dose_seq,
326 p_toxic_note_seq,
327 p_print_exposure_flag,
328 p_exposure_header_label,
329 p_exposure_cas_number_seq,
330 p_exposure_msds_name_seq,
331 p_exposure_authority_seq,
332 p_exposure_type_seq,
333 p_exposure_dose_seq,
334 p_exposure_note_seq,
335 p_include_qc_data_flag,
336 p_prop_65_conc_ind,
337 p_sara_312_conc_ind,
338 p_print_rtk_on_document,
339 p_rtk_header,
340 p_print_all_state_indicator,
341 p_approval_process_flag,
342 p_attribute_category,
343 p_attribute1,
344 p_attribute2,
345 p_attribute3,
346 p_attribute4,
347 p_attribute5,
348 p_attribute6,
349 p_attribute7,
350 p_attribute8,
351 p_attribute9,
352 p_attribute10,
353 p_attribute11,
354 p_attribute12,
355 p_attribute13,
356 p_attribute14,
357 p_attribute15,
358 p_attribute16,
359 p_attribute17,
360 p_attribute18,
361 p_attribute19,
362 p_attribute20,
363 p_attribute21,
364 p_attribute22,
365 p_attribute23,
366 p_attribute24,
367 p_attribute25,
368 p_attribute26,
369 p_attribute27,
370 p_attribute28,
371 p_attribute29,
372 p_attribute30,
373 p_created_by,
374 p_creation_date,
375 p_last_updated_by,
376 p_last_update_date,
377 p_last_update_login);
378
379 /* Now get the row id of the inserted record */
380
381 Check_Primary_Key
382 (p_document_code,
383 'F',
384 l_rowid,
385 l_key_exists);
386
387 IF FND_API.To_Boolean(l_key_exists) THEN
388 x_rowid := l_rowid;
389 ELSE
390 RAISE Row_Missing_Error;
391 END IF;
392
393 /* Check the commit flag and if set, then commit the work. */
394
395 IF FND_API.To_Boolean(p_commit) THEN
396 COMMIT WORK;
397 END IF;
398
399 EXCEPTION
400
401 WHEN Foreign_Key_Error THEN
402 ROLLBACK TO SAVEPOINT Insert_Row;
403 x_return_status := l_return_status;
404 x_oracle_error := l_oracle_error;
405 FND_MESSAGE.SET_NAME('GR',
406 'GR_FOREIGN_KEY_ERROR');
407 FND_MESSAGE.SET_TOKEN('TEXT',
408 l_msg_data,
409 FALSE);
410 IF FND_API.To_Boolean(p_called_by_form) THEN
411 APP_EXCEPTION.Raise_Exception;
412 ELSE
413 x_msg_data := FND_MESSAGE.Get;
414 END IF;
415
416 WHEN Label_Exists_Error THEN
417 ROLLBACK TO SAVEPOINT Insert_Row;
418 x_return_status := 'E';
419 x_oracle_error := APP_EXCEPTION.Get_Code;
420 FND_MESSAGE.SET_NAME('GR',
421 'GR_RECORD_EXISTS');
422 FND_MESSAGE.SET_TOKEN('CODE',
423 p_document_code,
424 FALSE);
425 IF FND_API.To_Boolean(p_called_by_form) THEN
426 APP_EXCEPTION.Raise_Exception;
427 ELSE
428 x_msg_data := FND_MESSAGE.Get;
429 END IF;
430
431 WHEN Row_Missing_Error THEN
432 ROLLBACK TO SAVEPOINT Insert_Row;
433 x_return_status := 'E';
434 x_oracle_error := APP_EXCEPTION.Get_Code;
435 FND_MESSAGE.SET_NAME('GR',
436 'GR_NO_RECORD_INSERTED');
437 FND_MESSAGE.SET_TOKEN('CODE',
438 p_document_code,
439 FALSE);
440 IF FND_API.To_Boolean(p_called_by_form) THEN
441 APP_EXCEPTION.Raise_Exception;
442 ELSE
443 x_msg_data := FND_MESSAGE.Get;
444 END IF;
445
446 WHEN OTHERS THEN
447 ROLLBACK TO SAVEPOINT Insert_Row;
448 x_return_status := 'U';
449 x_oracle_error := SQLCODE;
450 l_msg_data := SUBSTR(SQLERRM, 1, 200);
451 FND_MESSAGE.SET_NAME('GR',
452 'GR_UNEXPECTED_ERROR');
453 FND_MESSAGE.SET_TOKEN('TEXT',
454 l_msg_data,
455 FALSE);
456 IF FND_API.To_Boolean(p_called_by_form) THEN
457 APP_EXCEPTION.Raise_Exception;
458 ELSE
459 x_msg_data := FND_MESSAGE.Get;
460 END IF;
461
462 END Insert_Row;
463
464 PROCEDURE Update_Row
465 (p_commit IN VARCHAR2,
466 p_called_by_form IN VARCHAR2,
467 p_rowid IN VARCHAR2,
468 p_document_code IN VARCHAR2,
469 p_user_id IN NUMBER,
470 p_document_version IN NUMBER,
471 p_document_description IN VARCHAR2,
472 p_document_history_flag IN VARCHAR2,
473 p_allow_user_override IN VARCHAR2,
474 p_document_date_format IN VARCHAR2,
475 p_print_ingredients_flag IN VARCHAR2,
476 p_ingredient_header_label IN VARCHAR2,
477 p_ingredient_conc_ind IN VARCHAR2,
478 p_cas_number_seq IN NUMBER,
479 p_msds_name_seq IN NUMBER,
480 p_concentration_seq IN NUMBER,
481 p_hmis_code_seq IN NUMBER,
482 p_nfpa_code_seq IN NUMBER,
483 p_user_code_seq IN NUMBER,
484 p_eec_number_seq IN NUMBER,
485 p_hazard_symbol_seq IN NUMBER,
486 p_risk_phrase_seq IN NUMBER,
487 p_safety_phrase_seq IN NUMBER,
488 p_print_toxic_info_flag IN VARCHAR2,
489 p_toxic_header_label IN VARCHAR2,
490 p_toxic_cas_number_seq IN NUMBER,
491 p_toxic_msds_name_seq IN NUMBER,
492 p_toxic_route_seq IN NUMBER,
493 p_toxic_species_seq IN NUMBER,
494 p_toxic_exposure_seq IN NUMBER,
495 p_toxic_dose_seq IN NUMBER,
496 p_toxic_note_seq IN NUMBER,
497 p_print_exposure_flag IN VARCHAR2,
498 p_exposure_header_label IN VARCHAR2,
499 p_exposure_cas_number_seq IN NUMBER,
500 p_exposure_msds_name_seq IN NUMBER,
501 p_exposure_authority_seq IN NUMBER,
502 p_exposure_type_seq IN NUMBER,
503 p_exposure_dose_seq IN NUMBER,
504 p_exposure_note_seq IN NUMBER,
505 p_include_qc_data_flag IN VARCHAR2,
506 p_prop_65_conc_ind IN VARCHAR2,
507 p_sara_312_conc_ind IN VARCHAR2,
508 p_print_rtk_on_document IN VARCHAR2,
509 p_rtk_header IN VARCHAR2,
510 p_print_all_state_indicator IN VARCHAR2,
511 p_approval_process_flag IN VARCHAR2,
512 p_attribute_category IN VARCHAR2,
513 p_attribute1 IN VARCHAR2,
514 p_attribute2 IN VARCHAR2,
515 p_attribute3 IN VARCHAR2,
516 p_attribute4 IN VARCHAR2,
517 p_attribute5 IN VARCHAR2,
518 p_attribute6 IN VARCHAR2,
519 p_attribute7 IN VARCHAR2,
520 p_attribute8 IN VARCHAR2,
521 p_attribute9 IN VARCHAR2,
522 p_attribute10 IN VARCHAR2,
523 p_attribute11 IN VARCHAR2,
524 p_attribute12 IN VARCHAR2,
525 p_attribute13 IN VARCHAR2,
526 p_attribute14 IN VARCHAR2,
527 p_attribute15 IN VARCHAR2,
528 p_attribute16 IN VARCHAR2,
529 p_attribute17 IN VARCHAR2,
530 p_attribute18 IN VARCHAR2,
531 p_attribute19 IN VARCHAR2,
532 p_attribute20 IN VARCHAR2,
533 p_attribute21 IN VARCHAR2,
534 p_attribute22 IN VARCHAR2,
535 p_attribute23 IN VARCHAR2,
536 p_attribute24 IN VARCHAR2,
537 p_attribute25 IN VARCHAR2,
538 p_attribute26 IN VARCHAR2,
539 p_attribute27 IN VARCHAR2,
540 p_attribute28 IN VARCHAR2,
541 p_attribute29 IN VARCHAR2,
542 p_attribute30 IN VARCHAR2,
543 p_created_by IN NUMBER,
544 p_creation_date IN DATE,
545 p_last_updated_by IN NUMBER,
546 p_last_update_date IN DATE,
547 p_last_update_login IN NUMBER,
548 x_return_status OUT NOCOPY VARCHAR2,
549 x_oracle_error OUT NOCOPY NUMBER,
550 x_msg_data OUT NOCOPY VARCHAR2)
551 IS
552
553 /* Alpha Variables */
554
555 L_RETURN_STATUS VARCHAR2(1) := 'S';
556 L_MSG_DATA VARCHAR2(2000);
557
558 /* Number Variables */
559
560 L_ORACLE_ERROR NUMBER;
561
562 /* Exceptions */
563
564 FOREIGN_KEY_ERROR EXCEPTION;
565 ROW_MISSING_ERROR EXCEPTION;
566
567 BEGIN
568
569 /* Initialization Routine */
570
571 SAVEPOINT Update_Row;
572 x_return_status := 'S';
573 x_oracle_error := 0;
574 x_msg_data := NULL;
575
576 /* Now call the check foreign key procedure */
577
578 Check_Foreign_Keys
579 (p_document_code,
580 p_user_id,
581 p_document_version,
582 p_document_description,
583 p_document_history_flag,
584 p_allow_user_override,
585 p_document_date_format,
586 p_print_ingredients_flag,
587 p_ingredient_header_label,
588 p_ingredient_conc_ind,
589 p_cas_number_seq,
590 p_msds_name_seq,
591 p_concentration_seq,
592 p_hmis_code_seq,
593 p_nfpa_code_seq,
594 p_user_code_seq,
595 p_eec_number_seq,
596 p_hazard_symbol_seq,
597 p_risk_phrase_seq,
598 p_safety_phrase_seq,
599 p_print_toxic_info_flag,
600 p_toxic_header_label,
601 p_toxic_cas_number_seq,
602 p_toxic_msds_name_seq,
603 p_toxic_route_seq,
604 p_toxic_species_seq,
605 p_toxic_exposure_seq,
606 p_toxic_dose_seq,
607 p_toxic_note_seq,
608 p_print_exposure_flag,
609 p_exposure_header_label,
610 p_exposure_cas_number_seq,
611 p_exposure_msds_name_seq,
612 p_exposure_authority_seq,
613 p_exposure_type_seq,
614 p_exposure_dose_seq,
615 p_exposure_note_seq,
616 p_include_qc_data_flag,
617 p_prop_65_conc_ind,
618 p_sara_312_conc_ind,
619 p_print_rtk_on_document,
620 p_rtk_header,
621 p_print_all_state_indicator,
622 p_approval_process_flag,
623 p_attribute_category,
624 p_attribute1,
625 p_attribute2,
626 p_attribute3,
627 p_attribute4,
628 p_attribute5,
629 p_attribute6,
630 p_attribute7,
631 p_attribute8,
632 p_attribute9,
633 p_attribute10,
634 p_attribute11,
635 p_attribute12,
636 p_attribute13,
637 p_attribute14,
638 p_attribute15,
639 p_attribute16,
640 p_attribute17,
641 p_attribute18,
642 p_attribute19,
643 p_attribute20,
644 p_attribute21,
645 p_attribute22,
646 p_attribute23,
647 p_attribute24,
648 p_attribute25,
649 p_attribute26,
650 p_attribute27,
651 p_attribute28,
652 p_attribute29,
653 p_attribute30,
654 l_return_status,
655 l_oracle_error,
656 l_msg_data);
657
658 IF l_return_status <> 'S' THEN
659 RAISE Foreign_Key_Error;
660 ELSE
661 UPDATE gr_document_codes
662 SET document_code = p_document_code,
663 user_id = p_user_id,
664 document_version = p_document_version,
665 document_description = p_document_description,
666 document_history_flag = p_document_history_flag,
667 allow_user_override = p_allow_user_override,
668 document_date_format = p_document_date_format,
669 print_ingredients_flag = p_print_ingredients_flag,
670 ingredient_header_label = p_ingredient_header_label,
671 ingredient_conc_ind = p_ingredient_conc_ind,
672 cas_number_seq = p_cas_number_seq,
673 msds_name_seq = p_msds_name_seq,
674 concentration_seq = p_concentration_seq,
675 hmis_code_seq = p_hmis_code_seq,
676 nfpa_code_seq = p_nfpa_code_seq,
677 user_code_seq = p_user_code_seq,
678 eec_number_seq = p_eec_number_seq,
679 hazard_symbol_seq = p_hazard_symbol_seq,
680 risk_phrase_seq = p_risk_phrase_seq,
681 safety_phrase_seq = p_safety_phrase_seq,
682 print_toxic_info_flag = p_print_toxic_info_flag,
683 toxic_header_label = p_toxic_header_label,
684 toxic_cas_number_seq = p_toxic_cas_number_seq,
685 toxic_msds_name_seq = p_toxic_msds_name_seq,
686 toxic_route_seq = p_toxic_route_seq,
687 toxic_species_seq = p_toxic_species_seq,
688 toxic_exposure_seq = p_toxic_exposure_seq,
689 toxic_dose_seq = p_toxic_dose_seq,
690 toxic_note_seq = p_toxic_note_seq,
691 print_exposure_flag = p_print_exposure_flag,
692 exposure_header_label = p_exposure_header_label,
693 exposure_cas_number_seq = p_exposure_cas_number_seq,
694 exposure_msds_name_seq = p_exposure_msds_name_seq,
695 exposure_authority_seq = p_exposure_authority_seq,
696 exposure_type_seq = p_exposure_type_seq,
697 exposure_dose_seq = p_exposure_dose_seq,
698 exposure_note_seq = p_exposure_note_seq,
699 include_qc_data_flag = p_include_qc_data_flag,
700 prop_65_conc_ind = p_prop_65_conc_ind,
701 sara_312_conc_ind = p_sara_312_conc_ind,
702 print_rtk_on_document = p_print_rtk_on_document,
703 rtk_header = p_rtk_header,
704 print_all_state_indicator = p_print_all_state_indicator,
705 approval_process_flag = p_approval_process_flag,
706 attribute_category = p_attribute_category,
707 attribute1 = p_attribute1,
708 attribute2 = p_attribute2,
709 attribute3 = p_attribute3,
710 attribute4 = p_attribute4,
711 attribute5 = p_attribute5,
712 attribute6 = p_attribute6,
713 attribute7 = p_attribute7,
714 attribute8 = p_attribute8,
715 attribute9 = p_attribute9,
716 attribute10 = p_attribute10,
717 attribute11 = p_attribute11,
718 attribute12 = p_attribute12,
719 attribute13 = p_attribute13,
720 attribute14 = p_attribute14,
721 attribute15 = p_attribute15,
722 attribute16 = p_attribute16,
723 attribute17 = p_attribute17,
724 attribute18 = p_attribute18,
725 attribute19 = p_attribute19,
726 attribute20 = p_attribute20,
727 attribute21 = p_attribute11,
728 attribute22 = p_attribute22,
729 attribute23 = p_attribute23,
730 attribute24 = p_attribute24,
731 attribute25 = p_attribute25,
732 attribute26 = p_attribute26,
733 attribute27 = p_attribute27,
734 attribute28 = p_attribute28,
735 attribute29 = p_attribute29,
736 attribute30 = p_attribute30,
737 created_by = p_created_by,
738 creation_date = p_creation_date,
739 last_updated_by = p_last_updated_by,
740 last_update_date = p_last_update_date,
741 last_update_login = p_last_update_login
742 WHERE rowid = p_rowid;
743 IF SQL%NOTFOUND THEN
744 RAISE Row_Missing_Error;
745 END IF;
746 END IF;
747
748 /* Check the commit flag and if set, then commit the work. */
749
750 IF FND_API.To_Boolean(p_commit) THEN
751 COMMIT WORK;
752 END IF;
753
754 EXCEPTION
755
756 WHEN Foreign_Key_Error THEN
757 ROLLBACK TO SAVEPOINT Update_Row;
758 x_return_status := l_return_status;
759 x_oracle_error := l_oracle_error;
760 FND_MESSAGE.SET_NAME('GR',
761 'GR_FOREIGN_KEY_ERROR');
762 FND_MESSAGE.SET_TOKEN('TEXT',
763 l_msg_data,
764 FALSE);
765 IF FND_API.To_Boolean(p_called_by_form) THEN
766 APP_EXCEPTION.Raise_Exception;
767 ELSE
768 x_msg_data := FND_MESSAGE.Get;
769 END IF;
770
771 WHEN Row_Missing_Error THEN
772 ROLLBACK TO SAVEPOINT Update_Row;
773 x_return_status := 'E';
774 x_oracle_error := APP_EXCEPTION.Get_Code;
775 FND_MESSAGE.SET_NAME('GR',
776 'GR_NO_RECORD_INSERTED');
777 FND_MESSAGE.SET_TOKEN('CODE',
778 p_document_code,
779 FALSE);
780 IF FND_API.To_Boolean(p_called_by_form) THEN
781 APP_EXCEPTION.Raise_Exception;
782 ELSE
783 x_msg_data := FND_MESSAGE.Get;
784 END IF;
785
786 WHEN OTHERS THEN
787 ROLLBACK TO SAVEPOINT Update_Row;
788 x_return_status := 'U';
789 x_oracle_error := SQLCODE;
790 l_msg_data := SUBSTR(SQLERRM, 1, 200);
791 FND_MESSAGE.SET_NAME('GR',
792 'GR_UNEXPECTED_ERROR');
793 FND_MESSAGE.SET_TOKEN('TEXT',
794 l_msg_data,
795 FALSE);
796 IF FND_API.To_Boolean(p_called_by_form) THEN
797 APP_EXCEPTION.Raise_Exception;
798 ELSE
799 x_msg_data := FND_MESSAGE.Get;
800 END IF;
801
802 END Update_Row;
803
804 PROCEDURE Lock_Row
805 (p_commit IN VARCHAR2,
806 p_called_by_form IN VARCHAR2,
807 p_rowid IN VARCHAR2,
808 p_document_code IN VARCHAR2,
809 p_user_id IN NUMBER,
810 p_document_version IN NUMBER,
811 p_document_description IN VARCHAR2,
812 p_document_history_flag IN VARCHAR2,
813 p_allow_user_override IN VARCHAR2,
814 p_document_date_format IN VARCHAR2,
815 p_print_ingredients_flag IN VARCHAR2,
816 p_ingredient_header_label IN VARCHAR2,
817 p_ingredient_conc_ind IN VARCHAR2,
818 p_cas_number_seq IN NUMBER,
819 p_msds_name_seq IN NUMBER,
820 p_concentration_seq IN NUMBER,
821 p_hmis_code_seq IN NUMBER,
822 p_nfpa_code_seq IN NUMBER,
823 p_user_code_seq IN NUMBER,
824 p_eec_number_seq IN NUMBER,
825 p_hazard_symbol_seq IN NUMBER,
826 p_risk_phrase_seq IN NUMBER,
827 p_safety_phrase_seq IN NUMBER,
828 p_print_toxic_info_flag IN VARCHAR2,
829 p_toxic_header_label IN VARCHAR2,
830 p_toxic_cas_number_seq IN NUMBER,
831 p_toxic_msds_name_seq IN NUMBER,
832 p_toxic_route_seq IN NUMBER,
833 p_toxic_species_seq IN NUMBER,
834 p_toxic_exposure_seq IN NUMBER,
835 p_toxic_dose_seq IN NUMBER,
836 p_toxic_note_seq IN NUMBER,
837 p_print_exposure_flag IN VARCHAR2,
838 p_exposure_header_label IN VARCHAR2,
839 p_exposure_cas_number_seq IN NUMBER,
840 p_exposure_msds_name_seq IN NUMBER,
841 p_exposure_authority_seq IN NUMBER,
842 p_exposure_type_seq IN NUMBER,
843 p_exposure_dose_seq IN NUMBER,
844 p_exposure_note_seq IN NUMBER,
845 p_include_qc_data_flag IN VARCHAR2,
846 p_prop_65_conc_ind IN VARCHAR2,
847 p_sara_312_conc_ind IN VARCHAR2,
848 p_print_rtk_on_document IN VARCHAR2,
849 p_rtk_header IN VARCHAR2,
850 p_print_all_state_indicator IN VARCHAR2,
851 p_approval_process_flag IN VARCHAR2,
852 p_attribute_category IN VARCHAR2,
853 p_attribute1 IN VARCHAR2,
854 p_attribute2 IN VARCHAR2,
855 p_attribute3 IN VARCHAR2,
856 p_attribute4 IN VARCHAR2,
857 p_attribute5 IN VARCHAR2,
858 p_attribute6 IN VARCHAR2,
859 p_attribute7 IN VARCHAR2,
860 p_attribute8 IN VARCHAR2,
861 p_attribute9 IN VARCHAR2,
862 p_attribute10 IN VARCHAR2,
863 p_attribute11 IN VARCHAR2,
864 p_attribute12 IN VARCHAR2,
865 p_attribute13 IN VARCHAR2,
866 p_attribute14 IN VARCHAR2,
867 p_attribute15 IN VARCHAR2,
868 p_attribute16 IN VARCHAR2,
869 p_attribute17 IN VARCHAR2,
870 p_attribute18 IN VARCHAR2,
871 p_attribute19 IN VARCHAR2,
872 p_attribute20 IN VARCHAR2,
873 p_attribute21 IN VARCHAR2,
874 p_attribute22 IN VARCHAR2,
875 p_attribute23 IN VARCHAR2,
876 p_attribute24 IN VARCHAR2,
877 p_attribute25 IN VARCHAR2,
878 p_attribute26 IN VARCHAR2,
879 p_attribute27 IN VARCHAR2,
880 p_attribute28 IN VARCHAR2,
881 p_attribute29 IN VARCHAR2,
882 p_attribute30 IN VARCHAR2,
883 p_created_by IN NUMBER,
884 p_creation_date IN DATE,
885 p_last_updated_by IN NUMBER,
886 p_last_update_date IN DATE,
887 p_last_update_login IN NUMBER,
888 x_return_status OUT NOCOPY VARCHAR2,
889 x_oracle_error OUT NOCOPY NUMBER,
890 x_msg_data OUT NOCOPY VARCHAR2)
891 IS
892
893 /* Alpha Variables */
894
895 L_RETURN_STATUS VARCHAR2(1) := 'S';
896 L_MSG_DATA VARCHAR2(2000);
897
898 /* Number Variables */
899
900 L_ORACLE_ERROR NUMBER;
901
902 /* Exceptions */
903
904 NO_DATA_FOUND_ERROR EXCEPTION;
905 ROW_ALREADY_LOCKED_ERROR EXCEPTION;
906 PRAGMA EXCEPTION_INIT(ROW_ALREADY_LOCKED_ERROR,-54);
907
908 /* Define the cursors */
909
910 CURSOR c_lock_document
911 IS
912 SELECT *
913 FROM gr_document_codes
914 WHERE rowid = p_rowid
915 FOR UPDATE NOWAIT;
916 LockDocumentRcd c_lock_document%ROWTYPE;
917
918 BEGIN
919
920 /* Initialization Routine */
921
922 SAVEPOINT Lock_Row;
923 x_return_status := 'S';
924 x_oracle_error := 0;
925 x_msg_data := NULL;
926
927 /* Now lock the record */
928
929 OPEN c_lock_document;
930 FETCH c_lock_document INTO LockDocumentRcd;
931 IF c_lock_document%NOTFOUND THEN
932 CLOSE c_lock_document;
933 RAISE No_Data_Found_Error;
934 END IF;
935 CLOSE c_lock_document;
936
937 IF FND_API.To_Boolean(p_commit) THEN
938 COMMIT WORK;
939 END IF;
940
941 EXCEPTION
942
943 WHEN No_Data_Found_Error THEN
944 ROLLBACK TO SAVEPOINT Lock_Row;
945 x_return_status := 'E';
946 FND_MESSAGE.SET_NAME('GR',
947 'GR_RECORD_NOT_FOUND');
948 FND_MESSAGE.SET_TOKEN('CODE',
949 p_document_code,
950 FALSE);
951 IF FND_API.To_Boolean(p_called_by_form) THEN
952 APP_EXCEPTION.Raise_Exception;
953 ELSE
954 x_msg_data := FND_MESSAGE.Get;
955 END IF;
956
957 WHEN Row_Already_Locked_Error THEN
958 ROLLBACK TO SAVEPOINT Lock_Row;
959 x_return_status := 'E';
960 x_oracle_error := APP_EXCEPTION.Get_Code;
961 FND_MESSAGE.SET_NAME('GR',
962 'GR_ROW_IS_LOCKED');
963 IF FND_API.To_Boolean(p_called_by_form) THEN
964 APP_EXCEPTION.Raise_Exception;
965 ELSE
966 x_msg_data := FND_MESSAGE.Get;
967 END IF;
968
969 WHEN OTHERS THEN
970 ROLLBACK TO SAVEPOINT Lock_Row;
971 x_return_status := 'U';
972 x_oracle_error := SQLCODE;
973 l_msg_data := SUBSTR(SQLERRM, 1, 200);
974 FND_MESSAGE.SET_NAME('GR',
975 'GR_UNEXPECTED_ERROR');
976 FND_MESSAGE.SET_TOKEN('TEXT',
977 l_msg_data,
978 FALSE);
979 IF FND_API.To_Boolean(p_called_by_form) THEN
980 APP_EXCEPTION.Raise_Exception;
981 ELSE
982 x_msg_data := FND_MESSAGE.Get;
983 END IF;
984
985 END Lock_Row;
986
987 PROCEDURE Delete_Row
988 (p_commit IN VARCHAR2,
989 p_called_by_form IN VARCHAR2,
990 p_rowid IN VARCHAR2,
991 p_document_code IN VARCHAR2,
992 p_user_id IN NUMBER,
993 p_document_version IN NUMBER,
994 p_document_description IN VARCHAR2,
995 p_document_history_flag IN VARCHAR2,
996 p_allow_user_override IN VARCHAR2,
997 p_document_date_format IN VARCHAR2,
998 p_print_ingredients_flag IN VARCHAR2,
999 p_ingredient_header_label IN VARCHAR2,
1000 p_ingredient_conc_ind IN VARCHAR2,
1001 p_cas_number_seq IN NUMBER,
1002 p_msds_name_seq IN NUMBER,
1003 p_concentration_seq IN NUMBER,
1004 p_hmis_code_seq IN NUMBER,
1005 p_nfpa_code_seq IN NUMBER,
1006 p_user_code_seq IN NUMBER,
1007 p_eec_number_seq IN NUMBER,
1008 p_hazard_symbol_seq IN NUMBER,
1009 p_risk_phrase_seq IN NUMBER,
1010 p_safety_phrase_seq IN NUMBER,
1011 p_print_toxic_info_flag IN VARCHAR2,
1012 p_toxic_header_label IN VARCHAR2,
1013 p_toxic_cas_number_seq IN NUMBER,
1014 p_toxic_msds_name_seq IN NUMBER,
1015 p_toxic_route_seq IN NUMBER,
1016 p_toxic_species_seq IN NUMBER,
1017 p_toxic_exposure_seq IN NUMBER,
1018 p_toxic_dose_seq IN NUMBER,
1019 p_toxic_note_seq IN NUMBER,
1020 p_print_exposure_flag IN VARCHAR2,
1021 p_exposure_header_label IN VARCHAR2,
1022 p_exposure_cas_number_seq IN NUMBER,
1023 p_exposure_msds_name_seq IN NUMBER,
1024 p_exposure_authority_seq IN NUMBER,
1025 p_exposure_type_seq IN NUMBER,
1026 p_exposure_dose_seq IN NUMBER,
1027 p_exposure_note_seq IN NUMBER,
1028 p_include_qc_data_flag IN VARCHAR2,
1029 p_prop_65_conc_ind IN VARCHAR2,
1030 p_sara_312_conc_ind IN VARCHAR2,
1031 p_print_rtk_on_document IN VARCHAR2,
1032 p_rtk_header IN VARCHAR2,
1033 p_print_all_state_indicator IN VARCHAR2,
1034 p_approval_process_flag IN VARCHAR2,
1035 p_attribute_category IN VARCHAR2,
1036 p_attribute1 IN VARCHAR2,
1037 p_attribute2 IN VARCHAR2,
1038 p_attribute3 IN VARCHAR2,
1039 p_attribute4 IN VARCHAR2,
1040 p_attribute5 IN VARCHAR2,
1041 p_attribute6 IN VARCHAR2,
1042 p_attribute7 IN VARCHAR2,
1043 p_attribute8 IN VARCHAR2,
1044 p_attribute9 IN VARCHAR2,
1045 p_attribute10 IN VARCHAR2,
1046 p_attribute11 IN VARCHAR2,
1047 p_attribute12 IN VARCHAR2,
1048 p_attribute13 IN VARCHAR2,
1049 p_attribute14 IN VARCHAR2,
1050 p_attribute15 IN VARCHAR2,
1051 p_attribute16 IN VARCHAR2,
1052 p_attribute17 IN VARCHAR2,
1053 p_attribute18 IN VARCHAR2,
1054 p_attribute19 IN VARCHAR2,
1055 p_attribute20 IN VARCHAR2,
1056 p_attribute21 IN VARCHAR2,
1057 p_attribute22 IN VARCHAR2,
1058 p_attribute23 IN VARCHAR2,
1059 p_attribute24 IN VARCHAR2,
1060 p_attribute25 IN VARCHAR2,
1061 p_attribute26 IN VARCHAR2,
1062 p_attribute27 IN VARCHAR2,
1063 p_attribute28 IN VARCHAR2,
1064 p_attribute29 IN VARCHAR2,
1065 p_attribute30 IN VARCHAR2,
1066 p_created_by IN NUMBER,
1067 p_creation_date IN DATE,
1068 p_last_updated_by IN NUMBER,
1069 p_last_update_date IN DATE,
1070 p_last_update_login IN NUMBER,
1071 x_return_status OUT NOCOPY VARCHAR2,
1072 x_oracle_error OUT NOCOPY NUMBER,
1073 x_msg_data OUT NOCOPY VARCHAR2)
1074 IS
1075
1076 /* Alpha Variables */
1077
1078 L_RETURN_STATUS VARCHAR2(1) := 'S';
1079 L_MSG_DATA VARCHAR2(2000);
1080 L_CALLED_BY_FORM VARCHAR2(1);
1081
1082 /* Number Variables */
1083
1084 L_ORACLE_ERROR NUMBER;
1085
1086 /* Exceptions */
1087
1088 CHECK_INTEGRITY_ERROR EXCEPTION;
1089 ROW_MISSING_ERROR EXCEPTION;
1090 PRAGMA EXCEPTION_INIT(Row_Missing_Error,100);
1091
1092 BEGIN
1093
1094 /* Initialization Routine */
1095
1096 SAVEPOINT Delete_Row;
1097 x_return_status := 'S';
1098 l_called_by_form := 'F';
1099 x_oracle_error := 0;
1100 x_msg_data := NULL;
1101
1102 /* Now call the check integrity procedure */
1103
1104 Check_Integrity
1105 (l_called_by_form,
1106 p_document_code,
1107 p_user_id,
1108 p_document_version,
1109 p_document_description,
1110 p_document_history_flag,
1111 p_allow_user_override,
1112 p_document_date_format,
1113 p_print_ingredients_flag,
1114 p_ingredient_header_label,
1115 p_ingredient_conc_ind,
1116 p_cas_number_seq,
1117 p_msds_name_seq,
1118 p_concentration_seq,
1119 p_hmis_code_seq,
1120 p_nfpa_code_seq,
1121 p_user_code_seq,
1122 p_eec_number_seq,
1123 p_hazard_symbol_seq,
1124 p_risk_phrase_seq,
1125 p_safety_phrase_seq,
1126 p_print_toxic_info_flag,
1127 p_toxic_header_label,
1128 p_toxic_cas_number_seq,
1129 p_toxic_msds_name_seq,
1130 p_toxic_route_seq,
1131 p_toxic_species_seq,
1132 p_toxic_exposure_seq,
1133 p_toxic_dose_seq,
1134 p_toxic_note_seq,
1135 p_print_exposure_flag,
1136 p_exposure_header_label,
1137 p_exposure_cas_number_seq,
1138 p_exposure_msds_name_seq,
1139 p_exposure_authority_seq,
1140 p_exposure_type_seq,
1141 p_exposure_dose_seq,
1142 p_exposure_note_seq,
1143 p_include_qc_data_flag,
1144 p_prop_65_conc_ind,
1145 p_sara_312_conc_ind,
1146 p_print_rtk_on_document,
1147 p_rtk_header,
1148 p_print_all_state_indicator,
1149 p_approval_process_flag,
1150 p_attribute_category,
1151 p_attribute1,
1152 p_attribute2,
1153 p_attribute3,
1154 p_attribute4,
1155 p_attribute5,
1156 p_attribute6,
1157 p_attribute7,
1158 p_attribute8,
1159 p_attribute9,
1160 p_attribute10,
1161 p_attribute11,
1162 p_attribute12,
1163 p_attribute13,
1164 p_attribute14,
1165 p_attribute15,
1166 p_attribute16,
1167 p_attribute17,
1168 p_attribute18,
1169 p_attribute19,
1170 p_attribute20,
1171 p_attribute21,
1172 p_attribute22,
1173 p_attribute23,
1174 p_attribute24,
1175 p_attribute25,
1176 p_attribute26,
1177 p_attribute27,
1178 p_attribute28,
1179 p_attribute29,
1180 p_attribute30,
1181 l_return_status,
1182 l_oracle_error,
1183 l_msg_data);
1184
1185 IF l_return_status <> 'S' THEN
1186 RAISE Check_Integrity_Error;
1187 END IF;
1188
1189 DELETE FROM gr_document_codes
1190 WHERE rowid = p_rowid;
1191
1192 /* Check the commit flag and if set, then commit the work. */
1193
1194 IF FND_API.TO_Boolean(p_commit) THEN
1195 COMMIT WORK;
1196 END IF;
1197
1198 EXCEPTION
1199
1200 WHEN Check_Integrity_Error THEN
1201 ROLLBACK TO SAVEPOINT Delete_Row;
1202 x_return_status := l_return_status;
1203 x_oracle_error := l_oracle_error;
1204 IF FND_API.To_Boolean(p_called_by_form) THEN
1205 APP_EXCEPTION.Raise_Exception;
1206 ELSE
1207 x_msg_data := FND_MESSAGE.Get;
1208 END IF;
1209
1210 WHEN Row_Missing_Error THEN
1211 ROLLBACK TO SAVEPOINT Delete_Row;
1212 x_return_status := 'E';
1213 x_oracle_error := APP_EXCEPTION.Get_Code;
1214 FND_MESSAGE.SET_NAME('GR',
1215 'GR_RECORD_NOT_FOUND');
1216 FND_MESSAGE.SET_TOKEN('CODE',
1217 p_document_code,
1218 FALSE);
1219 IF FND_API.To_Boolean(p_called_by_form) THEN
1220 APP_EXCEPTION.Raise_Exception;
1221 ELSE
1222 x_msg_data := FND_MESSAGE.Get;
1223 END IF;
1224
1225 WHEN OTHERS THEN
1226 ROLLBACK TO SAVEPOINT Delete_Row;
1227 x_return_status := 'U';
1228 x_oracle_error := SQLCODE;
1229 l_msg_data := SUBSTR(SQLERRM, 1, 200);
1230 FND_MESSAGE.SET_NAME('GR',
1231 'GR_UNEXPECTED_ERROR');
1232 FND_MESSAGE.SET_TOKEN('TEXT',
1233 l_msg_data,
1234 FALSE);
1235 IF FND_API.To_Boolean(p_called_by_form) THEN
1236 APP_EXCEPTION.Raise_Exception;
1237 ELSE
1238 x_msg_data := FND_MESSAGE.Get;
1239 END IF;
1240
1241 END Delete_Row;
1242
1243 PROCEDURE Check_Foreign_Keys
1244 (p_document_code IN VARCHAR2,
1245 p_user_id IN NUMBER,
1246 p_document_version IN NUMBER,
1247 p_document_description IN VARCHAR2,
1248 p_document_history_flag IN VARCHAR2,
1249 p_allow_user_override IN VARCHAR2,
1250 p_document_date_format IN VARCHAR2,
1251 p_print_ingredients_flag IN VARCHAR2,
1252 p_ingredient_header_label IN VARCHAR2,
1253 p_ingredient_conc_ind IN VARCHAR2,
1254 p_cas_number_seq IN NUMBER,
1255 p_msds_name_seq IN NUMBER,
1256 p_concentration_seq IN NUMBER,
1257 p_hmis_code_seq IN NUMBER,
1258 p_nfpa_code_seq IN NUMBER,
1259 p_user_code_seq IN NUMBER,
1260 p_eec_number_seq IN NUMBER,
1261 p_hazard_symbol_seq IN NUMBER,
1262 p_risk_phrase_seq IN NUMBER,
1263 p_safety_phrase_seq IN NUMBER,
1264 p_print_toxic_info_flag IN VARCHAR2,
1265 p_toxic_header_label IN VARCHAR2,
1266 p_toxic_cas_number_seq IN NUMBER,
1267 p_toxic_msds_name_seq IN NUMBER,
1268 p_toxic_route_seq IN NUMBER,
1269 p_toxic_species_seq IN NUMBER,
1270 p_toxic_exposure_seq IN NUMBER,
1271 p_toxic_dose_seq IN NUMBER,
1272 p_toxic_note_seq IN NUMBER,
1273 p_print_exposure_flag IN VARCHAR2,
1274 p_exposure_header_label IN VARCHAR2,
1275 p_exposure_cas_number_seq IN NUMBER,
1276 p_exposure_msds_name_seq IN NUMBER,
1277 p_exposure_authority_seq IN NUMBER,
1278 p_exposure_type_seq IN NUMBER,
1279 p_exposure_dose_seq IN NUMBER,
1280 p_exposure_note_seq IN NUMBER,
1281 p_include_qc_data_flag IN VARCHAR2,
1282 p_prop_65_conc_ind IN VARCHAR2,
1283 p_sara_312_conc_ind IN VARCHAR2,
1284 p_print_rtk_on_document IN VARCHAR2,
1285 p_rtk_header IN VARCHAR2,
1286 p_print_all_state_indicator IN VARCHAR2,
1287 p_approval_process_flag IN VARCHAR2,
1288 p_attribute_category IN VARCHAR2,
1289 p_attribute1 IN VARCHAR2,
1290 p_attribute2 IN VARCHAR2,
1291 p_attribute3 IN VARCHAR2,
1292 p_attribute4 IN VARCHAR2,
1293 p_attribute5 IN VARCHAR2,
1294 p_attribute6 IN VARCHAR2,
1295 p_attribute7 IN VARCHAR2,
1296 p_attribute8 IN VARCHAR2,
1297 p_attribute9 IN VARCHAR2,
1298 p_attribute10 IN VARCHAR2,
1299 p_attribute11 IN VARCHAR2,
1300 p_attribute12 IN VARCHAR2,
1301 p_attribute13 IN VARCHAR2,
1302 p_attribute14 IN VARCHAR2,
1303 p_attribute15 IN VARCHAR2,
1304 p_attribute16 IN VARCHAR2,
1305 p_attribute17 IN VARCHAR2,
1306 p_attribute18 IN VARCHAR2,
1307 p_attribute19 IN VARCHAR2,
1308 p_attribute20 IN VARCHAR2,
1309 p_attribute21 IN VARCHAR2,
1310 p_attribute22 IN VARCHAR2,
1311 p_attribute23 IN VARCHAR2,
1312 p_attribute24 IN VARCHAR2,
1313 p_attribute25 IN VARCHAR2,
1314 p_attribute26 IN VARCHAR2,
1315 p_attribute27 IN VARCHAR2,
1316 p_attribute28 IN VARCHAR2,
1317 p_attribute29 IN VARCHAR2,
1318 p_attribute30 IN VARCHAR2,
1319 x_return_status OUT NOCOPY VARCHAR2,
1320 x_oracle_error OUT NOCOPY NUMBER,
1321 x_msg_data OUT NOCOPY VARCHAR2)
1322 IS
1323
1324 /* Alpha Variables */
1325
1326 L_RETURN_STATUS VARCHAR2(1) := 'S';
1327 L_MSG_DATA VARCHAR2(2000);
1328 L_ROWID VARCHAR2(18);
1329 L_KEY_EXISTS VARCHAR2(1);
1330 L_HEADING VARCHAR2(30);
1331
1332 /* Number Variables */
1333
1334 L_ORACLE_ERROR NUMBER;
1335
1336 /* Define the cursors */
1337 /* Main Headings */
1338
1339 CURSOR c_get_main_heading
1340 IS
1341 SELECT mh.main_heading_code
1342 FROM gr_main_headings_b mh
1343 WHERE mh.main_heading_code = l_heading;
1344 MainHdgRcd c_get_main_heading%ROWTYPE;
1345
1346 /* User ID */
1347
1348 CURSOR c_get_user_id
1349 IS
1350 SELECT fnu.user_id
1351 FROM fnd_user fnu
1352 WHERE fnu.user_id = p_user_id;
1353 UserRcd c_get_user_id%ROWTYPE;
1354
1355 BEGIN
1356
1357 /* Initialization Routine */
1358
1359 SAVEPOINT Check_Foreign_Keys;
1360 x_return_status := 'S';
1361 x_oracle_error := 0;
1362 x_msg_data := NULL;
1363
1364 /* Check the main heading code for ingredient print */
1365
1366 IF p_ingredient_header_label IS NOT NULL THEN
1367 l_heading := p_ingredient_header_label;
1368 OPEN c_get_main_heading;
1369 FETCH c_get_main_heading INTO MainHdgRcd;
1370 IF c_get_main_heading%NOTFOUND THEN
1371 x_return_status := 'E';
1372 FND_MESSAGE.SET_NAME('GR',
1373 'GR_RECORD_NOT_FOUND');
1374 FND_MESSAGE.SET_TOKEN('CODE',
1375 l_heading,
1376 FALSE);
1377 l_msg_data := l_msg_data || FND_MESSAGE.Get || ' ';
1378 END IF;
1379 CLOSE c_get_main_heading;
1380 END IF;
1381
1382 /* Check the main heading code for toxic print */
1383
1384 IF p_toxic_header_label IS NOT NULL THEN
1385 l_heading := p_toxic_header_label;
1386 OPEN c_get_main_heading;
1387 FETCH c_get_main_heading INTO MainHdgRcd;
1388 IF c_get_main_heading%NOTFOUND THEN
1389 x_return_status := 'E';
1390 FND_MESSAGE.SET_NAME('GR',
1391 'GR_RECORD_NOT_FOUND');
1392 FND_MESSAGE.SET_TOKEN('CODE',
1393 l_heading,
1394 FALSE);
1395 l_msg_data := l_msg_data || FND_MESSAGE.Get || ' ';
1396 END IF;
1397 CLOSE c_get_main_heading;
1398 END IF;
1399
1400 /* Check the main heading code for exposure print */
1401
1402 IF p_exposure_header_label IS NOT NULL THEN
1403 l_heading := p_exposure_header_label;
1404 OPEN c_get_main_heading;
1405 FETCH c_get_main_heading INTO MainHdgRcd;
1406 IF c_get_main_heading%NOTFOUND THEN
1407 x_return_status := 'E';
1408 FND_MESSAGE.SET_NAME('GR',
1409 'GR_RECORD_NOT_FOUND');
1410 FND_MESSAGE.SET_TOKEN('CODE',
1411 l_heading,
1412 FALSE);
1413 l_msg_data := l_msg_data || FND_MESSAGE.Get || ' ';
1414 END IF;
1415 CLOSE c_get_main_heading;
1416 END IF;
1417
1418 /* Check the user id */
1419
1420 OPEN c_get_user_id;
1421 FETCH c_get_user_id INTO UserRcd;
1422 IF c_get_user_id%NOTFOUND THEN
1423 x_return_status := 'E';
1424 FND_MESSAGE.SET_NAME('GR',
1425 'GR_RECORD_NOT_FOUND');
1426 FND_MESSAGE.SET_TOKEN('CODE',
1427 p_user_id,
1428 FALSE);
1429 l_msg_data := l_msg_data || FND_MESSAGE.Get || ' ';
1430 END IF;
1431 CLOSE c_get_user_id;
1432
1433 IF x_return_status <> 'S' THEN
1434 x_msg_data := l_msg_data;
1435 END IF;
1436
1437 EXCEPTION
1438
1439 WHEN OTHERS THEN
1440 ROLLBACK TO SAVEPOINT Check_Foreign_Keys;
1441 x_return_status := 'U';
1442 x_oracle_error := SQLCODE;
1443 l_msg_data := SUBSTR(SQLERRM, 1, 200);
1444 FND_MESSAGE.SET_NAME('GR',
1445 'GR_UNEXPECTED_ERROR');
1446 FND_MESSAGE.SET_TOKEN('TEXT',
1447 l_msg_data,
1448 FALSE);
1449 x_msg_data := FND_MESSAGE.Get;
1450
1451 END Check_Foreign_Keys;
1452
1453 PROCEDURE Check_Integrity
1454 (p_called_by_form IN VARCHAR2,
1455 p_document_code IN VARCHAR2,
1456 p_user_id IN NUMBER,
1457 p_document_version IN NUMBER,
1458 p_document_description IN VARCHAR2,
1459 p_document_history_flag IN VARCHAR2,
1460 p_allow_user_override IN VARCHAR2,
1461 p_document_date_format IN VARCHAR2,
1462 p_print_ingredients_flag IN VARCHAR2,
1463 p_ingredient_header_label IN VARCHAR2,
1464 p_ingredient_conc_ind IN VARCHAR2,
1465 p_cas_number_seq IN NUMBER,
1466 p_msds_name_seq IN NUMBER,
1467 p_concentration_seq IN NUMBER,
1468 p_hmis_code_seq IN NUMBER,
1469 p_nfpa_code_seq IN NUMBER,
1470 p_user_code_seq IN NUMBER,
1471 p_eec_number_seq IN NUMBER,
1472 p_hazard_symbol_seq IN NUMBER,
1473 p_risk_phrase_seq IN NUMBER,
1474 p_safety_phrase_seq IN NUMBER,
1475 p_print_toxic_info_flag IN VARCHAR2,
1476 p_toxic_header_label IN VARCHAR2,
1477 p_toxic_cas_number_seq IN NUMBER,
1478 p_toxic_msds_name_seq IN NUMBER,
1479 p_toxic_route_seq IN NUMBER,
1480 p_toxic_species_seq IN NUMBER,
1481 p_toxic_exposure_seq IN NUMBER,
1482 p_toxic_dose_seq IN NUMBER,
1483 p_toxic_note_seq IN NUMBER,
1484 p_print_exposure_flag IN VARCHAR2,
1485 p_exposure_header_label IN VARCHAR2,
1486 p_exposure_cas_number_seq IN NUMBER,
1487 p_exposure_msds_name_seq IN NUMBER,
1488 p_exposure_authority_seq IN NUMBER,
1489 p_exposure_type_seq IN NUMBER,
1490 p_exposure_dose_seq IN NUMBER,
1491 p_exposure_note_seq IN NUMBER,
1492 p_include_qc_data_flag IN VARCHAR2,
1493 p_prop_65_conc_ind IN VARCHAR2,
1494 p_sara_312_conc_ind IN VARCHAR2,
1495 p_print_rtk_on_document IN VARCHAR2,
1496 p_rtk_header IN VARCHAR2,
1497 p_print_all_state_indicator IN VARCHAR2,
1498 p_approval_process_flag IN VARCHAR2,
1499 p_attribute_category IN VARCHAR2,
1500 p_attribute1 IN VARCHAR2,
1501 p_attribute2 IN VARCHAR2,
1502 p_attribute3 IN VARCHAR2,
1503 p_attribute4 IN VARCHAR2,
1504 p_attribute5 IN VARCHAR2,
1505 p_attribute6 IN VARCHAR2,
1506 p_attribute7 IN VARCHAR2,
1507 p_attribute8 IN VARCHAR2,
1508 p_attribute9 IN VARCHAR2,
1509 p_attribute10 IN VARCHAR2,
1510 p_attribute11 IN VARCHAR2,
1511 p_attribute12 IN VARCHAR2,
1512 p_attribute13 IN VARCHAR2,
1513 p_attribute14 IN VARCHAR2,
1514 p_attribute15 IN VARCHAR2,
1515 p_attribute16 IN VARCHAR2,
1516 p_attribute17 IN VARCHAR2,
1517 p_attribute18 IN VARCHAR2,
1518 p_attribute19 IN VARCHAR2,
1519 p_attribute20 IN VARCHAR2,
1520 p_attribute21 IN VARCHAR2,
1521 p_attribute22 IN VARCHAR2,
1522 p_attribute23 IN VARCHAR2,
1523 p_attribute24 IN VARCHAR2,
1524 p_attribute25 IN VARCHAR2,
1525 p_attribute26 IN VARCHAR2,
1526 p_attribute27 IN VARCHAR2,
1527 p_attribute28 IN VARCHAR2,
1528 p_attribute29 IN VARCHAR2,
1529 p_attribute30 IN VARCHAR2,
1530 x_return_status OUT NOCOPY VARCHAR2,
1531 x_oracle_error OUT NOCOPY NUMBER,
1532 x_msg_data OUT NOCOPY VARCHAR2)
1533 IS
1534
1535 /* Alpha Variables */
1536
1537 L_RETURN_STATUS VARCHAR2(1) := 'S';
1538 L_MSG_DATA VARCHAR2(2000);
1539 L_CODE_BLOCK VARCHAR2(100);
1540
1541 /* Number Variables */
1542
1543 L_ORACLE_ERROR NUMBER;
1544 L_RECORD_COUNT NUMBER;
1545
1546 /* Define the Cursors */
1547 /* Dispatch Histories */
1548
1549 CURSOR c_get_disp_history
1550 IS
1551 SELECT COUNT(*)
1552 FROM gr_dispatch_histories
1553 WHERE document_code = p_document_code;
1554
1555 /* Document Print */
1556
1557 CURSOR c_get_doc_print
1558 IS
1559 SELECT COUNT(*)
1560 FROM gr_document_print
1561 WHERE document_code = p_document_code;
1562
1563 /* Item Document Details */
1564
1565 CURSOR c_get_item_doc_dtls
1566 IS
1567 SELECT COUNT(*)
1568 FROM gr_item_document_dtls
1569 WHERE document_code = p_document_code;
1570
1571 /* Item Document Statuses */
1572
1573 CURSOR c_get_item_doc_statuses
1574 IS
1575 SELECT COUNT(*)
1576 FROM gr_item_doc_statuses
1577 WHERE document_code = p_document_code;
1578
1579 /* Recipient Documents */
1580
1581 CURSOR c_get_recipient_docs
1582 IS
1583 SELECT COUNT(*)
1584 FROM gr_recipient_documents
1585 WHERE document_code = p_document_code;
1586
1587 /* Recipient Information */
1588
1589 CURSOR c_get_recipient_info
1590 IS
1591 SELECT COUNT(*)
1592 FROM gr_recipient_info
1593 WHERE document_code = p_document_code;
1594
1595 BEGIN
1596
1597 /* Initialization Routine */
1598
1599 SAVEPOINT Check_Integrity;
1600 x_return_status := 'S';
1601 x_oracle_error := 0;
1602 x_msg_data := NULL;
1603
1604 FND_MESSAGE.SET_NAME('GR',
1605 'GR_INTEGRITY_HEADER');
1606 FND_MESSAGE.SET_TOKEN('CODE',
1607 p_document_code,
1608 FALSE);
1609 l_msg_data := FND_MESSAGE.Get;
1610
1611 /* Now read the cursors to make sure the item code isn't used. */
1612 /* Dispatch Histories */
1613
1614 l_record_count := 0;
1615 l_code_block := 'c_get_disp_history';
1616 OPEN c_get_disp_history;
1617 FETCH c_get_disp_history INTO l_record_count;
1618 IF l_record_count <> 0 THEN
1619 l_return_status := 'E';
1620 l_msg_data := l_msg_data || 'gr_dispatch_history, ';
1621 END IF;
1622 CLOSE c_get_disp_history;
1623
1624 /* Document Print */
1625
1626 l_record_count := 0;
1627 l_code_block := 'c_get_doc_print';
1628 OPEN c_get_doc_print;
1629 FETCH c_get_doc_print INTO l_record_count;
1630 IF l_record_count <> 0 THEN
1631 l_return_status := 'E';
1632 l_msg_data := l_msg_data || 'gr_document_print, ';
1633 END IF;
1634 CLOSE c_get_doc_print;
1635
1636 /* Item Document Details */
1637
1638 l_record_count := 0;
1639 l_code_block := 'c_get_item_doc_dtls';
1640 OPEN c_get_item_doc_dtls;
1641 FETCH c_get_item_doc_dtls INTO l_record_count;
1642 IF l_record_count <> 0 THEN
1643 l_return_status := 'E';
1644 l_msg_data := l_msg_data || 'gr_item_document_details, ';
1645 END IF;
1646 CLOSE c_get_item_doc_dtls;
1647
1648 /* Item Document Statuses */
1649
1650 l_record_count := 0;
1651 l_code_block := 'c_get_item_doc_statuses';
1652 OPEN c_get_item_doc_statuses;
1653 FETCH c_get_item_doc_statuses INTO l_record_count;
1654 IF l_record_count <> 0 THEN
1655 l_return_status := 'E';
1656 l_msg_data := l_msg_data || 'gr_item_doc_statuses, ';
1657 END IF;
1658 CLOSE c_get_item_doc_statuses;
1659
1660 /* Recipient Documents */
1661
1662 l_record_count := 0;
1663 l_code_block := 'c_get_recipient_docs';
1664 OPEN c_get_recipient_docs;
1665 FETCH c_get_recipient_docs INTO l_record_count;
1666 IF l_record_count <> 0 THEN
1667 l_return_status := 'E';
1668 l_msg_data := l_msg_data || 'gr_recipient_docs, ';
1669 END IF;
1670 CLOSE c_get_recipient_docs;
1671
1672 /* Recipient Info */
1673
1674 l_record_count := 0;
1675 l_code_block := 'c_get_recipient_info';
1676 OPEN c_get_recipient_info;
1677 FETCH c_get_recipient_info INTO l_record_count;
1678 IF l_record_count <> 0 THEN
1679 l_return_status := 'E';
1680 l_msg_data := l_msg_data || 'gr_recipient_info, ';
1681 END IF;
1682 CLOSE c_get_recipient_info;
1683
1684 /* Now sort out the error messaging */
1685
1686 IF l_return_status <> 'S' THEN
1687 x_return_status := l_return_status;
1688 x_msg_data := l_msg_data;
1689 END IF;
1690
1691 EXCEPTION
1692
1693 WHEN OTHERS THEN
1694 ROLLBACK TO SAVEPOINT Check_Integrity;
1695 x_return_status := 'U';
1696 x_oracle_error := SQLCODE;
1697 l_msg_data := SUBSTR(SQLERRM, 1, 200);
1698 FND_MESSAGE.SET_NAME('GR',
1699 'GR_UNEXPECTED_ERROR');
1700 FND_MESSAGE.SET_TOKEN('TEXT',
1701 l_msg_data,
1702 FALSE);
1703 IF FND_API.To_Boolean(p_called_by_form) THEN
1704 APP_EXCEPTION.Raise_Exception;
1705 ELSE
1706 x_msg_data := FND_MESSAGE.Get;
1707 END IF;
1708
1709 END Check_Integrity;
1710
1711 PROCEDURE Check_Primary_Key
1712 /* p_document_code is the document code to check.
1713 ** p_called_by_form is 'T' if called by a form or 'F' if not.
1714 ** x_rowid is the row id of the record if found.
1715 ** x_key_exists is 'T' is the record is found, 'F' if not.
1716 */
1717 (p_document_code IN VARCHAR2,
1718 p_called_by_form IN VARCHAR2,
1719 x_rowid OUT NOCOPY VARCHAR2,
1720 x_key_exists OUT NOCOPY VARCHAR2)
1721 IS
1722 /* Alphanumeric variables */
1723
1724 L_MSG_DATA VARCHAR2(80);
1725
1726 /* Declare any variables and the cursor */
1727
1728
1729 CURSOR c_get_document_rowid
1730 IS
1731 SELECT dc.rowid
1732 FROM gr_document_codes dc
1733 WHERE dc.document_code = p_document_code;
1734 DocumentRecord c_get_document_rowid%ROWTYPE;
1735
1736 BEGIN
1737
1738 x_key_exists := 'F';
1739 l_msg_data := p_document_code;
1740 OPEN c_get_document_rowid;
1741 FETCH c_get_document_rowid INTO DocumentRecord;
1742 IF c_get_document_rowid%FOUND THEN
1743 x_key_exists := 'T';
1744 x_rowid := DocumentRecord.rowid;
1745 ELSE
1746 x_key_exists := 'F';
1747 END IF;
1748 CLOSE c_get_document_rowid;
1749
1750 EXCEPTION
1751
1752 WHEN Others THEN
1753 l_msg_data := SUBSTR(SQLERRM, 1, 200);
1754 FND_MESSAGE.SET_NAME('GR',
1755 'GR_UNEXPECTED_ERROR');
1756 FND_MESSAGE.SET_TOKEN('TEXT',
1757 l_msg_data,
1758 FALSE);
1759 IF FND_API.To_Boolean(p_called_by_form) THEN
1760 APP_EXCEPTION.Raise_Exception;
1761 END IF;
1762
1763 END Check_Primary_Key;
1764
1765 END GR_DOCUMENT_CODES_PKG;