1 PACKAGE BODY GR_FIELD_NAME_CLASSES_PUB AS
2 /* $Header: GRPIFNCB.pls 120.2.12010000.2 2009/06/19 16:12:48 plowe noship $
3 *****************************************************************
4 * *
5 * Package GR_FIELD_NAME_CLASSES_PUB *
6 * *
7 * Contents FIELD_NAME_CLASSES *
8 * *
9 * *
10 * Use This is the public layer for the FIELD NAME_CLASSES *
11 * API *
12 * *
13 * History *
14 * Written by P A Lowe OPM Unlimited Dev *
15 * Peter Lowe 06/20/08 *
16 * *
17 * Updated By For *
18 * *
19 * Peter Lowe 07/10/08 BUG 7249054 *
20 * Peter Lowe 08/01/08 BUG 7293765 *
21 * *
22 *****************************************************************
23 */
24
25 -- Global variables
26
27 G_PKG_NAME CONSTANT VARCHAR2(30):='GR_FIELD_NAME_CLASSES_PUB';
28
29 --Forward declaration.
30 FUNCTION set_debug_flag RETURN VARCHAR2;
31 l_debug VARCHAR2(1) := set_debug_flag;
32
33 FUNCTION set_debug_flag RETURN VARCHAR2 IS
34 l_debug VARCHAR2(1):= 'N';
35 BEGIN
36 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
37 l_debug := 'Y';
38 END IF;
39 l_debug := 'Y';
40 RETURN l_debug;
41 END set_debug_flag;
42
43 PROCEDURE FIELD_NAME_CLASSES
44 ( p_api_version IN NUMBER
45 , p_init_msg_list IN VARCHAR2
46 , p_commit IN VARCHAR2
47 , p_action IN VARCHAR2
48 , p_object IN VARCHAR2
49 , p_field_name_class IN VARCHAR2
50 , p_form_block IN VARCHAR2
51 , p_language IN VARCHAR2
52 , p_source_language IN VARCHAR2
53 , p_description IN VARCHAR2
54 , p_label_class_resp_tab IN GR_FIELD_NAME_CLASSES_PUB.gr_label_class_resp_tab_type
55 , x_return_status OUT NOCOPY VARCHAR2
56 , x_msg_count OUT NOCOPY NUMBER
57 , x_msg_data OUT NOCOPY VARCHAR2
58 )
59
60 IS
61 l_api_name CONSTANT VARCHAR2 (30) := 'FIELD_NAME_CLASSES';
62 l_api_version CONSTANT NUMBER := 1.0;
63 l_msg_count NUMBER :=0;
64 l_debug_flag VARCHAR2(1) := set_debug_flag;
65
66 l_language_code VARCHAR2(4);
67 l_missing_count NUMBER;
68 l_form_block VARCHAR2(14);
69 l_application_id NUMBER := 557;
70
71 l_last_update_login NUMBER(15,0) := 0;
72 L_KEY_EXISTS VARCHAR2(1);
73
74 l_responsibility_id NUMBER;
75 l_responsibility VARCHAR2(100);
76 l_display_sequence NUMBER;
77 l_allow_create_update VARCHAR2(1);
78
79
80 dummy NUMBER;
81 i NUMBER;
82
83 row_id VARCHAR2(18);
84 return_status VARCHAR2(1);
85 oracle_error NUMBER;
86 msg_data VARCHAR2(2000);
87
88 LBins_err EXCEPTION;
89 LCins_err EXCEPTION;
90 LTadd_err EXCEPTION;
91 LTL_del_err EXCEPTION;
92 LT_EXISTS_ERROR EXCEPTION;
93 ROW_MISSING_ERROR EXCEPTION;
94
95 -- Cursor Definitions
96
97 CURSOR c_get_language
98 IS
99 SELECT lng.language_code
100 FROM fnd_languages lng
101 WHERE lng.language_code = l_language_code;
102 LangRecord c_get_language%ROWTYPE;
103
104 CURSOR Cur_count_language IS
105 SELECT count (language_code)
106 FROM fnd_languages
107 WHERE installed_flag IN ('I', 'B')
108 AND language_code not in
109 (SELECT language
110 FROM GR_LABEL_CLASSES_TL
111 WHERE label_class_code = p_field_name_class);
112
113 /* Label Class Codes */
114
115 CURSOR c_get_label_class
116 IS
117 SELECT lcb.label_class_code, lcb.form_block
118 FROM gr_label_classes_b lcb
119 WHERE lcb.label_class_code = p_field_name_class;
120 LabelClsRcd c_get_label_class%ROWTYPE;
121
122 CURSOR c_get_label_class_tl
123 IS
124 SELECT 1
125 FROM GR_LABEL_CLASSES_TL
126 WHERE label_class_code = p_field_name_class
127 and language = p_language;
128
129 cursor resp_id_cur is
130 select 1
131 from fnd_responsibility
132 where responsibility_id = l_responsibility_id;
133
134 cursor resp_name_cur is
135 select responsibility_id
136 from fnd_responsibility_vl
137 where responsibility_name = l_responsibility;
138
139 cursor label_class_resp1 is
140 select 1
141 from GR_LABEL_CLASS_RESPS
142 where LABEL_CLASS_CODE = p_field_name_class;
143
144
145 cursor label_class_resp is
146 select 1
147 from GR_LABEL_CLASS_RESPS
148 where LABEL_CLASS_CODE = p_field_name_class
149 and APPLICATION_ID = 557
150 and RESPONSIBILITY_ID = l_responsibility_id;
151
152 cursor label_class_resp_ds is
153 select 1
154 from GR_LABEL_CLASS_RESPS
155 where LABEL_CLASS_CODE = p_field_name_class
156 and APPLICATION_ID = 557
157 and display_sequence = l_display_sequence;
158
159 L_MSG_TOKEN VARCHAR2(100);
160
161
162 BEGIN
163
164 -- Standard Start OF API savepoint
165
166 -- SAVEPOINT FIELD_NAME_CLASSES;
167
168 /* Standard call to check for call compatibility. */
169
170 IF NOT FND_API.Compatible_API_CALL
171 (l_api_version , p_api_version , l_api_name , G_PKG_NAME)
172 THEN
173 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
174 END IF;
175
176 /* Initialize message list if p_int_msg_list is set TRUE. */
177 IF FND_API.to_boolean(p_init_msg_list)
178 THEN
179 FND_MSG_PUB.Initialize;
180 END IF;
181
182 -- Initialize API return Parameters
183 gmd_debug.log_initialize('PAL euro trash');
184 x_return_status := FND_API.G_RET_STS_SUCCESS;
185
186 -- IF (l_debug = 'Y') THEN
187 -- gmd_debug.log_initialize('GR FIELD NAME CLASSES API');
188 -- END IF;
189
190
191
192 /* check mandatory inputs */
193
194 IF p_action is NULL or p_action not in ('I','U','D') then
195 FND_MESSAGE.SET_NAME('GR',
196 'GR_INVALID_ACTION');
197 RAISE FND_API.G_EXC_ERROR;
198 END IF;
199
200 IF p_object is NULL or p_object not in ('C','L','R') then
201 FND_MESSAGE.SET_NAME('GR',
202 'GR_INVALID_OBJECT');
203 RAISE FND_API.G_EXC_ERROR;
204 END IF;
205
206
207 IF p_field_name_class is NULL then
208 FND_MESSAGE.SET_NAME('GMA',
209 'SY_FIELDNAME');
210 RAISE FND_API.G_EXC_ERROR;
211 END IF;
212
213 IF p_action = 'I' then
214
215 IF p_object = 'C' then
216
217
218 -- Validate that the value of Field Name Class does not already exist in the table GR_LABEL_CLASSES_B.
219 -- If it does, write an error to the log file
220
221 OPEN c_get_label_class;
222 FETCH c_get_label_class INTO LabelClsRcd;
223 IF c_get_label_class%NOTFOUND THEN
224 null;
225 ELSE
226 x_return_status := 'E';
227 l_msg_token := p_field_name_class;
228 CLOSE c_get_label_class;
229 RAISE LT_Exists_Error;
230 END IF;
231 CLOSE c_get_label_class;
232
233 -- Language, Source Language and Description values are required
234 -- and an error message will be written to the log file if any of the values are null.
235 IF p_language is NULL or p_source_language is NULL or p_description is null or p_form_block is NULL then
236 FND_MESSAGE.SET_NAME('GMA',
237 'SY_FIELDNAME');
238 RAISE FND_API.G_EXC_ERROR;
239 END IF;
240
241 -- Validate that Form Block is set to either Properties, Risk Phrases, Safety Phrases or Names.
242 -- If an invalid value is passed in, an error message will be written to the log file.
243 IF p_form_block not in ('Properties','Risk Phrases','Safety Phrases','Names') then
244 FND_MESSAGE.SET_NAME('GMA',
245 'SY_INVALID_TYPE');
246 RAISE FND_API.G_EXC_ERROR;
247
248 END IF;
249
250 -- 7293765
251 IF p_form_block = 'Risk Phrases' then
252 l_form_block := 'RISK_PHRASES';
253 END IF;
254 IF p_form_block = 'Safety Phrases' then
255 l_form_block := 'SAFETY_PHRASES';
256 END IF;
257
258
259 -- insert a record for GR_LABEL_CLASSES_B
260 gr_label_classes_b_pkg.Insert_Row
261 (p_commit => 'T',
262 p_called_by_form => 'F',
263 p_label_class_code => p_field_name_class,
264 p_form_block => l_form_block,
265 p_rollup_type => NULL,
266 p_rollup_property => NULL,
267 p_rollup_label => NULL,
268 p_attribute_category => NULL,
269 p_attribute1 => NULL,
270 p_attribute2 => NULL,
271 p_attribute3 => NULL,
272 p_attribute4 => NULL,
273 p_attribute5 => NULL,
274 p_attribute6 => NULL,
275 p_attribute7 => NULL,
276 p_attribute8 => NULL,
277 p_attribute9 => NULL,
278 p_attribute10 => NULL,
279 p_attribute11 => NULL,
280 p_attribute12 => NULL,
281 p_attribute13 => NULL,
282 p_attribute14 => NULL,
283 p_attribute15 => NULL,
284 p_attribute16 => NULL,
285 p_attribute17 => NULL,
286 p_attribute18 => NULL,
287 p_attribute19 => NULL,
288 p_attribute20 => NULL,
289 p_attribute21 => NULL,
290 p_attribute22=> NULL,
291 p_attribute23 => NULL,
292 p_attribute24 => NULL,
293 p_attribute25 => NULL,
294 p_attribute26 => NULL,
295 p_attribute27 => NULL,
296 p_attribute28 => NULL,
297 p_attribute29 => NULL,
298 p_attribute30 => NULL,
299 p_created_by => FND_GLOBAL.USER_ID,
300 p_creation_date => SYSDATE,
301 p_last_updated_by => FND_GLOBAL.USER_ID,
302 p_last_update_date => SYSDATE,
303 p_last_update_login => l_last_update_login,
304 x_rowid => row_id,
305 x_return_status => return_status,
306 x_oracle_error => oracle_error,
307 x_msg_data => msg_data);
308
309 /*dbms_output.put_line('msg_data => ' || msg_data);
310 dbms_output.put_line('oracle_error => ' || oracle_error);
311 dbms_output.put_line(' return_status => ' || return_status); */
312
313 IF return_status <> 'S' THEN
314
315 IF (l_debug_flag = 'Y') THEN
316 gmd_debug.put_line('error');
317 END IF;
318 GMD_API_PUB.Log_Message(msg_data);
319 RAISE LBins_err;
320 END IF;
321
322 -- need to add base row for language for GR_LABEL_CLASSES_TL
323
324 gr_label_classes_tl_pkg.insert_row(
325 p_commit => 'T',
326 p_called_by_form => 'F',
327 p_label_class_code => p_field_name_class,
328 p_language => p_language,
329 p_label_class_description => p_description,
330 p_source_lang => p_source_language,
331 p_created_by => fnd_global.user_id,
332 p_creation_date => sysdate,
333 p_last_updated_by => fnd_global.user_id,
334 p_last_update_date => sysdate,
335 p_last_update_login => l_last_update_login,
336 x_rowid => row_id,
337 x_return_status => return_status,
338 x_oracle_error => oracle_error,
339 x_msg_data => msg_data);
340
341
342 IF return_status <> 'S' THEN
343
344 IF (l_debug_flag = 'Y') THEN
345 gmd_debug.put_line('error');
346 END IF;
347 GMD_API_PUB.Log_Message(msg_data);
348 RAISE LCins_err;
349 END IF;
350
351
352
353 -- Insert a record into GR_LABEL_CLASSES_TL for each installed language.
354
355 OPEN Cur_count_language;
356 FETCH Cur_count_language INTO l_missing_count;
357 CLOSE Cur_count_language;
358 IF l_missing_count > 0 THEN
359
360
361 GR_LABEL_CLASSES_TL_PKG.Add_Language
362 (p_commit => 'T',
363 p_called_by_form => 'F',
364 p_label_class_code => p_field_name_class,
365 p_language => p_language,
366 x_return_status => return_status,
367 x_oracle_error => oracle_error,
368 x_msg_data => msg_data);
369
370
371 /*dbms_output.put_line('msg_data => ' || msg_data);
372 dbms_output.put_line('oracle_error => ' || oracle_error);
373 dbms_output.put_line('return_status => ' || return_status); */
374
375 IF return_status <> 'S' THEN
376 GMD_API_PUB.Log_Message('GR_LABEL_CLASS_ADD_LANG_ERROR');
377 FND_MESSAGE.SET_NAME('GR',
378 'GR_LABEL_CLASS_ADD_LANG_ERROR');
379 FND_MESSAGE.SET_TOKEN('CODE',
380 l_msg_token,
381 FALSE);
382 FND_MSG_PUB.ADD;
383 RAISE LTadd_err;
384 END IF;
385
386 END IF; -- IF l_missing_count > 0 THEN
387
388 -- Insert a record into GR_LABEL_CLASS_RESPS for each valid record in the passed table.
389
390 FOR i IN 1 .. p_label_class_resp_tab.count LOOP
391
392 l_responsibility_id := p_label_class_resp_tab(i).responsibility_id;
393 l_responsibility := p_label_class_resp_tab(i).responsibility;
394 l_display_sequence := p_label_class_resp_tab(i).display_sequence;
395 l_allow_create_update := p_label_class_resp_tab(i).allow_create_update;
396
397 IF l_responsibility_id IS NOT NULL THEN
398 -- check if valid_id
399 dummy:= 0;
400 OPEN resp_id_cur;
401 FETCH resp_id_cur INTO dummy;
402 IF resp_id_cur%NOTFOUND THEN
403 CLOSE resp_id_cur;
404 l_msg_token := l_responsibility_id;
405 RAISE Row_Missing_Error;
406 END IF;
407 CLOSE resp_id_cur;
408 END IF;
409
410 IF l_responsibility IS NOT NULL THEN
411 -- check if valid resp
412
413 OPEN resp_name_cur;
414 FETCH resp_name_cur INTO l_responsibility_id;
415 IF resp_name_cur%NOTFOUND THEN
416 CLOSE resp_name_cur;
417 l_msg_token := l_responsibility;
418 RAISE Row_Missing_Error;
419 END IF;
420 CLOSE resp_name_cur;
421 END IF;
422 IF l_responsibility_id is NOT NULL or l_responsibility is NOT NULL then
423
424 -- Insert a record into GR_LABEL_CLASS_RESPS for each valid record in the passed table
425
426 insert into gr_label_class_resps (
427 label_class_code, application_id, responsibility_id, display_sequence,
428 allow_create_update, created_by, creation_date, last_updated_by,
429 last_update_date, last_update_login ) values
430 ( p_field_name_class,
431 l_application_id,
432 l_responsibility_id,
433 l_display_sequence,
434 l_allow_create_update,
435 fnd_global.user_id,
436 sysdate,
437 fnd_global.user_id,
438 sysdate,
439 l_last_update_login
440 );
441
442 END IF; --IF l_responsibility_id is NOT NULL or responsibility_id is NOT NULL then
443
444 END LOOP; -- FOR i IN 1 .. p_label_class_resp_tab.count LOOP
445
446
447 ELSIF p_object = 'L' then
448
449 -- Validate that the value of Field Name Class exists in the table GR_LABEL_CLASSES_B.
450 -- If it does not, write an error to the log file
451 OPEN c_get_label_class;
452 FETCH c_get_label_class INTO LabelClsRcd;
453 IF c_get_label_class%NOTFOUND THEN
454 x_return_status := 'E';
455 l_msg_token := p_field_name_class;
456 CLOSE c_get_label_class;
457 RAISE Row_Missing_Error;
458 END IF;
459 CLOSE c_get_label_class;
460
461 -- Validate that the value of Language for the specified property does not exist in the table table GR_LABEL_CLASSES_TL.
462 -- If it does, write an error to the log file.
463
464 OPEN c_get_label_class_tl;
465 FETCH c_get_label_class_tl INTO dummy;
466 IF c_get_label_class_tl%FOUND THEN
467 x_return_status := 'E';
468 l_msg_token := p_field_name_class|| ' ' || p_language;
469 CLOSE c_get_label_class_tl;
470 RAISE LT_Exists_Error;
471 END IF;
472 CLOSE c_get_label_class_tl;
473
474 -- The values for Source Language, Language and Description will be written to the GR_LABEL_CLASSES_TL table
475 gr_label_classes_tl_pkg.insert_row(
476 p_commit => 'T',
477 p_called_by_form => 'F',
478 p_label_class_code => p_field_name_class,
479 p_language => p_language,
480 p_label_class_description => p_description,
481 p_source_lang => p_source_language,
482 p_created_by => fnd_global.user_id,
483 p_creation_date => sysdate,
484 p_last_updated_by => fnd_global.user_id,
485 p_last_update_date => sysdate,
486 p_last_update_login => l_last_update_login,
487 x_rowid => row_id,
488 x_return_status => return_status,
489 x_oracle_error => oracle_error,
490 x_msg_data => msg_data);
491
492 IF return_status <> 'S' THEN
493
494 IF (l_debug_flag = 'Y') THEN
495 gmd_debug.put_line('error');
496 END IF;
497 GMD_API_PUB.Log_Message(msg_data);
498 RAISE LCins_err;
499 END IF;
500
501
502 ELSE -- object = R
503
504 -- Insert a record into GR_LABEL_CLASS_RESPS for each valid record in the passed table.
505
506 FOR i IN 1 .. p_label_class_resp_tab.count LOOP
507
508 l_responsibility_id := p_label_class_resp_tab(i).responsibility_id;
509 l_responsibility := p_label_class_resp_tab(i).responsibility;
510 l_display_sequence := p_label_class_resp_tab(i).display_sequence;
511 l_allow_create_update := p_label_class_resp_tab(i).allow_create_update;
512
513 -- It is required that either the responsibility id or the responsibility name is passed in.
514 --If neither is sent in, a 'Responsibility required' error message will be written to the log file.
515 IF l_responsibility_id is NULL and l_responsibility is NULL then
516 FND_MESSAGE.SET_NAME('GMA',
517 'SY_FIELDNAME');
518 RAISE FND_API.G_EXC_ERROR;
519 END IF;
520
521 -- If a responsibility name is sent in, the responsibility id will be retrieved from FND_RESPONSIBILITY_VL.
522 -- If a responsibility id is sent in, it is validated against the FND_RESPONSIBILITY table.
523 -- An error message will be written to the log file if the value is invalid.
524
525 IF l_responsibility_id IS NOT NULL THEN
526 -- check if valid_id
527 dummy:= 0;
528 OPEN resp_id_cur;
529 FETCH resp_id_cur INTO dummy;
530 IF resp_id_cur%NOTFOUND THEN
531 CLOSE resp_id_cur;
532 l_msg_token := l_responsibility_id;
533 RAISE Row_Missing_Error;
534 END IF;
535 CLOSE resp_id_cur;
536 END IF;
537
538 IF l_responsibility IS NOT NULL THEN
539 -- check if valid resp
540
541 OPEN resp_name_cur;
542 FETCH resp_name_cur INTO l_responsibility_id;
543 IF resp_name_cur%NOTFOUND THEN
544 CLOSE resp_name_cur;
545 l_msg_token := l_responsibility;
546 RAISE Row_Missing_Error;
547 END IF;
548 CLOSE resp_name_cur;
549 END IF;
550
551 -- Validate that the value of Responsibility Id for the specified field name class
552 -- does not exist in the table GR_LABEL_CLASS_RESPS.
553 -- If it does, write an error to the log file.
554
555 OPEN label_class_resp;
556 FETCH label_class_resp INTO dummy;
557 IF label_class_resp%NOTFOUND THEN
558 null;
559 ELSE
560 x_return_status := 'E';
561 l_msg_token := p_field_name_class || ' ' || l_responsibility_id;
562 CLOSE label_class_resp;
563 RAISE LT_Exists_Error;
564 END IF;
565 CLOSE label_class_resp;
566
567 -- Validate that the value of display sequence for the specified field name class does not exist
568 -- in the table GR_LABEL_CLASS_RESPS. If it does, write an error to the log file
569
570 OPEN label_class_resp_ds;
571 FETCH label_class_resp_ds INTO dummy;
572 IF label_class_resp_ds%NOTFOUND THEN
573 null;
574 ELSE
575 x_return_status := 'E';
576 l_msg_token := p_field_name_class || ' ' || l_display_sequence;
577 CLOSE label_class_resp_ds;
578 RAISE LT_Exists_Error;
579 END IF;
580 CLOSE label_class_resp_ds;
581
582 -- The values for field name class, responsibility id,
583 -- display_sequence and allow edit will be written to the GR_LABEL_CLASS_RESPS table.
584
585 IF l_responsibility_id is NOT NULL then
586
587 -- Insert a record into GR_LABEL_CLASS_RESPS for each valid record in the passed table
588
589 insert into gr_label_class_resps (
590 label_class_code, application_id, responsibility_id, display_sequence,
591 allow_create_update, created_by, creation_date, last_updated_by,
592 last_update_date, last_update_login ) values
593 ( p_field_name_class,
594 l_application_id,
595 l_responsibility_id,
596 l_display_sequence,
597 l_allow_create_update,
598 fnd_global.user_id,
599 sysdate,
600 fnd_global.user_id,
601 sysdate,
602 l_last_update_login
603 );
604
605
606 END IF; --IF l_responsibility_id is NOT NULL or responsibility_id is NOT NULL then
607
608 END LOOP; -- FOR i IN 1 .. p_label_class_resp_tab.count LOOP
609
610
611
612 END IF; -- IF p_object = 'C' then
613
614 -- next is U action
615
616 /*************************************************************************************/
617
618 ELSIF p_action = 'U' then
619
620 -- Validate that the value of Label Class exists in the table GR_LABEL_CLASSES_B.
621 -- If it does not, an error message will be written to the log file.
622
623 OPEN c_get_label_class;
624 FETCH c_get_label_class INTO LabelClsRcd;
625 IF c_get_label_class%NOTFOUND THEN
626 x_return_status := 'E';
627 l_msg_token := p_field_name_class;
628 CLOSE c_get_label_class;
629 RAISE Row_Missing_Error;
630 END IF;
631 CLOSE c_get_label_class;
632
633
634
635 IF p_object = 'C' then
636 -- bug 7249054 - need to validate p_form_block
637
638 -- Validate that Form Block is set to either Properties, Risk Phrases, Safety Phrases or Names.
639 -- If an invalid value is passed in, an error message will be written to the log file.
640 IF p_form_block not in ('Properties','Risk Phrases','Safety Phrases','Names') then
641 FND_MESSAGE.SET_NAME('GMA',
642 'SY_INVALID_TYPE');
643 RAISE FND_API.G_EXC_ERROR;
644
645 END IF;
646
647 -- 7293765
648 IF p_form_block = 'Risk Phrases' then
649 l_form_block := 'RISK_PHRASES';
650 END IF;
651 IF p_form_block = 'Safety Phrases' then
652 l_form_block := 'SAFETY_PHRASES';
653 END IF;
654
655 -- The value of Form Block will be updated in the GR_ GR_LABEL_CLASSES_B table.
656
657 UPDATE gr_label_classes_b
658 SET form_block = l_form_block,
659 last_updated_by = fnd_global.user_id,
660 last_update_date = sysdate,
661 last_update_login = l_last_update_login
662 WHERE label_class_code = p_field_name_class;
663 IF SQL%NOTFOUND THEN
664 RAISE Row_Missing_Error;
665 END IF;
666
667 ELSIF p_object = 'L' then
668
669 -- If the value for Language is null or invalid, write an error to the log file.
670
671 IF p_language is NULL then
672 l_msg_token := l_language_code;
673 RAISE Row_Missing_Error;
674 END IF;
675
676 /* Check the language codes */
677
678 l_language_code := p_language;
679 OPEN c_get_language;
680 FETCH c_get_language INTO LangRecord;
681 IF c_get_language%NOTFOUND THEN
682 CLOSE c_get_language;
683 l_msg_token := l_language_code;
684 RAISE Row_Missing_Error;
685 END IF;
686 CLOSE c_get_language;
687
688 -- If the record for the specified field name class and language does not exist in the GR_LABEL_CLASSES_TL table,
689 -- an error will be written to the log file.
690
691 gr_label_classes_tl_pkg.Check_Primary_Key(
692 p_field_name_class,
693 p_language,
694 'F',
695 row_id,
696 l_key_exists);
697
698 IF l_key_exists = 'N' THEN
699 l_msg_token := p_field_name_class|| ' ' || p_language;
700 RAISE Row_Missing_Error;
701 END IF;
702 -- The value for Description will be updated GR_LABEL_CLASSES_TL table for the specified language.
703
704 UPDATE GR_LABEL_CLASSES_TL
705 SET label_class_description = p_description,
706 source_lang = p_source_language,
707 last_updated_by = FND_GLOBAL.USER_ID,
708 last_update_date = SYSDATE,
709 last_update_login = l_last_update_login
710 WHERE label_class_code = p_field_name_class
711 and language = p_language;
712 IF SQL%NOTFOUND THEN
713 l_msg_token := p_field_name_class || ' ' || p_language;
714 RAISE Row_Missing_Error;
715 END IF;
716
717 ELSE -- object = R (responsibility)
718
719 FOR i IN 1 .. p_label_class_resp_tab.count LOOP
720
721 l_responsibility_id := p_label_class_resp_tab(i).responsibility_id;
722 l_responsibility := p_label_class_resp_tab(i).responsibility;
723 l_display_sequence := p_label_class_resp_tab(i).display_sequence;
724 l_allow_create_update := p_label_class_resp_tab(i).allow_create_update;
725
726 -- If a responsibility name is sent in, the responsibility id will be retrieved from FND_RESPONSIBILITY_VL.
727 -- If a responsibility id is sent in, it is validated against the FND_RESPONSIBILITY table.
728 -- An error message will be written to the log file if the value is invalid.
729
730 IF l_responsibility_id IS NOT NULL THEN
731 -- check if valid_id
732 dummy:= 0;
733 OPEN resp_id_cur;
734 FETCH resp_id_cur INTO dummy;
735 IF resp_id_cur%NOTFOUND THEN
736 CLOSE resp_id_cur;
737 l_msg_token := l_responsibility_id;
738 RAISE Row_Missing_Error;
739 END IF;
740 CLOSE resp_id_cur;
741 END IF;
742
743 IF l_responsibility IS NOT NULL THEN
744 -- check if valid resp
745 OPEN resp_name_cur;
746 FETCH resp_name_cur INTO l_responsibility_id;
747 IF resp_name_cur%NOTFOUND THEN
748 CLOSE resp_name_cur;
749 l_msg_token := l_responsibility;
750 RAISE Row_Missing_Error;
751 END IF;
752 CLOSE resp_name_cur;
753 END IF;
754
755 -- Validate that the value of Responsibility Id for the specified field name class exists in the table
756 -- GR_LABEL_CLASS_RESPS. If it does not, write an error to the log file.
757
758
759 OPEN label_class_resp;
760 FETCH label_class_resp INTO dummy;
761 IF label_class_resp%NOTFOUND THEN
762 x_return_status := 'E';
763 l_msg_token := p_field_name_class || ' ' || l_responsibility_id;
764 CLOSE label_class_resp;
765 RAISE LT_Exists_Error;
766 END IF;
767 CLOSE label_class_resp;
768
769 -- If sent in, validate that the value of display sequence for the specified Responsibility Id
770 -- and field name class does not exist in the table GR_LABEL_CLASS_RESPS.
771 -- If it does, write an error to the log file
772
773 IF l_display_sequence is NOT NULL then
774 OPEN label_class_resp_ds;
775 FETCH label_class_resp_ds INTO dummy;
776 IF label_class_resp_ds%NOTFOUND THEN
777 null;
778 ELSE
779 x_return_status := 'E';
780 l_msg_token := p_field_name_class || ' ' || l_display_sequence;
781 CLOSE label_class_resp_ds;
782 RAISE LT_Exists_Error;
783 END IF;
784 CLOSE label_class_resp_ds;
785 END IF; -- IF l_display_sequence is NOT NULL then
786
787 -- The non-null values for display sequence and allow edit will be updated in the GR_LABEL_CLASS_RESPS table.
788 IF l_responsibility_id is NOT NULL then
789 update gr_label_class_resps
790 set display_sequence = l_display_sequence,
791 allow_create_update = l_allow_create_update
792 where label_class_code = p_field_name_class
793 and application_id = l_application_id
794 and responsibility_id = l_responsibility_id;
795 END IF; --IF l_responsibility_id is NOT NULL then
796
797 END LOOP; -- FOR i IN 1 .. p_label_class_resp_tab.count LOOP
798
799 END IF; -- IF p_object = 'C' then
800
801 ELSE -- action is D (delete)
802
803 -- Validate that the value of Field Name class exists in the table GR_LABEL_CLASSES_B.
804 -- If it does not, write an error to the log file
805
806 OPEN c_get_label_class;
807 FETCH c_get_label_class INTO LabelClsRcd;
808 IF c_get_label_class%NOTFOUND THEN
809 x_return_status := 'E';
810 l_msg_token := p_field_name_class;
811 CLOSE c_get_label_class;
812 RAISE Row_Missing_Error;
813 END IF;
814 CLOSE c_get_label_class;
815
816 IF p_object = 'C' then
817
818 -- Delete all of the property related records in the
819 -- GR_LABEL_CLASSES_B, GR_LABEL_CLASSES_TL and GR_LABEL_CLASS_RESPS tables.
820
821 gr_label_classes_tl_pkg.delete_rows
822 (p_commit => 'T',
823 p_called_by_form => 'F',
824 p_label_class_code => p_field_name_class,
825 x_return_status => return_status,
826 x_oracle_error => oracle_error,
827 x_msg_data => msg_data);
828
829 IF return_status <> FND_API.g_ret_sts_success THEN
830 GMD_API_PUB.Log_Message('GR_LABEL_CLASS_DEL_LANG_ERROR');
831 FND_MESSAGE.SET_NAME('GR',
832 'GR_LABEL_CLASS_DEL_LANG_ERROR');
833 FND_MSG_PUB.ADD;
834 RAISE LTL_del_err;
835 END IF;
836
837 DELETE FROM gr_label_class_resps
838 WHERE label_class_code = p_field_name_class;
839
840 DELETE FROM gr_label_classes_b
841 WHERE label_class_code = p_field_name_class;
842
843 IF SQL%NOTFOUND THEN
844 l_msg_token := p_field_name_class;
845 RAISE Row_Missing_Error;
846 END IF;
847
848 ELSIF p_object = 'L' then
849
850 -- If the value for Language is null or invalid, write an error to the log file.
851
852 IF p_language is NULL then
853 l_msg_token := l_language_code;
854 RAISE Row_Missing_Error;
855 END IF;
856
857 /* Check the language codes */
858
859 l_language_code := p_language;
860 OPEN c_get_language;
861 FETCH c_get_language INTO LangRecord;
862 IF c_get_language%NOTFOUND THEN
863 CLOSE c_get_language;
864 l_msg_token := l_language_code;
865 RAISE Row_Missing_Error;
866 END IF;
867 CLOSE c_get_language;
868
869 -- If the record for the specified field name class and language does not exist
870 --in the GR_LABEL_CLASSES_TL table, an error will be written to the log file
871
872 gr_label_classes_tl_pkg.Check_Primary_Key(
873 p_field_name_class,
874 p_language,
875 'F',
876 row_id,
877 l_key_exists);
878
879 IF l_key_exists = 'N' THEN
880 l_msg_token := p_field_name_class|| ' ' || p_language;
881 RAISE Row_Missing_Error;
882 END IF;
883
884 -- Delete the record in GR_LABEL_CLASSES_TL table for the specified language
885
886 delete from GR_LABEL_CLASSES_TL
887 WHERE label_class_code = p_field_name_class
888 and language = p_language;
889 IF SQL%NOTFOUND THEN
890 l_msg_token := p_field_name_class || ' ' || p_language;
891 RAISE Row_Missing_Error;
892 END IF;
893
894
895 ELSE -- object = R -- up to here
896
897 -- Validate that the value of Field Name Class exists in the table GR_LABEL_CLASS_RESPS.
898 -- If it does not, write an error to the log file
899 OPEN label_class_resp1; -- lap
900 FETCH label_class_resp1 INTO dummy;
901 IF label_class_resp1%NOTFOUND THEN
902 x_return_status := 'E';
903 l_msg_token := p_field_name_class;
904 CLOSE label_class_resp1;
905 RAISE Row_Missing_Error;
906 END IF;
907 CLOSE label_class_resp1;
908
909 -- Loop through records in input table
910
911
912 FOR i IN 1 .. p_label_class_resp_tab.count LOOP
913
914 l_responsibility_id := p_label_class_resp_tab(i).responsibility_id;
915 l_responsibility := p_label_class_resp_tab(i).responsibility;
916 l_display_sequence := p_label_class_resp_tab(i).display_sequence;
917 l_allow_create_update := p_label_class_resp_tab(i).allow_create_update;
918
919 -- If a responsibility name is sent in, the responsibility id will be retrieved from FND_RESPONSIBILITY_VL.
920 -- If a responsibility id is sent in, it is validated against the FND_RESPONSIBILITY table.
921 -- An error message will be written to the log file if the value is invalid.
922
923 IF l_responsibility_id IS NOT NULL THEN
924 -- check if valid_id
925 dummy:= 0;
926 OPEN resp_id_cur;
927 FETCH resp_id_cur INTO dummy;
928 IF resp_id_cur%NOTFOUND THEN
929 CLOSE resp_id_cur;
930 l_msg_token := l_responsibility_id;
931 RAISE Row_Missing_Error;
932 END IF;
933 CLOSE resp_id_cur;
934 END IF;
935
936 IF l_responsibility IS NOT NULL THEN
937 -- check if valid resp
938 OPEN resp_name_cur;
939 FETCH resp_name_cur INTO l_responsibility_id;
940 IF resp_name_cur%NOTFOUND THEN
941 CLOSE resp_name_cur;
942 l_msg_token := l_responsibility;
943 RAISE Row_Missing_Error;
944 END IF;
945 CLOSE resp_name_cur;
946 END IF;
947
948 -- Validate that the value of Responsibility Id for the specified field name class exists in the
949 --table GR_LABEL_CLASS_RESPS. If it does not, write an error to the log file.
950
951 OPEN label_class_resp;
952 FETCH label_class_resp INTO dummy;
953 IF label_class_resp%NOTFOUND THEN
954 x_return_status := 'E';
955 l_msg_token := p_field_name_class || ' ' || l_responsibility_id;
956 CLOSE label_class_resp;
957 RAISE LT_Exists_Error;
958 END IF;
959 CLOSE label_class_resp;
960
961 -- Delete the record in GR_ GR_LABEL_CLASS_RESPS
962 -- table for the specified field name class and responsibility id.
963 IF l_responsibility_id is NOT NULL then
964 delete from gr_label_class_resps
965 where label_class_code = p_field_name_class
966 and application_id = l_application_id
967 and responsibility_id = l_responsibility_id ;
968 END IF; --IF l_responsibility_id is NOT NULL then
969
970 END LOOP; -- FOR i IN 1 .. p_label_class_resp_tab.count LOOP
971
972 END IF; -- IF p_object = 'C' then
973
974 END IF; -- IF p_action = 'I' then
975
976
977 EXCEPTION
978 WHEN LBins_err THEN
979 x_return_status := FND_API.G_RET_STS_ERROR;
980 -- ROLLBACK TO SAVEPOINT FIELD_NAMES;
981 --x_msg_data := msg_data;
982 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
983 , p_count => x_msg_count
984 , p_data => x_msg_data);
985
986 WHEN LCins_err THEN
987
988 x_return_status := FND_API.G_RET_STS_ERROR;
989 -- ROLLBACK TO SAVEPOINT FIELD_NAMES;
990 --x_msg_data := msg_data;
991 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
992 , p_count => x_msg_count
993 , p_data => x_msg_data);
994
995 WHEN LTadd_err THEN
996 x_return_status := FND_API.G_RET_STS_ERROR;
997 --ROLLBACK TO SAVEPOINT FIELD_NAME_CLASSES;
998 FND_MSG_PUB.COUNT_AND_GET (P_count => x_msg_count,
999 P_data => x_msg_data);
1000
1001 WHEN LTL_del_err THEN
1002 x_return_status := FND_API.G_RET_STS_ERROR;
1003 -- ROLLBACK TO SAVEPOINT FIELD_NAME_CLASSES;
1004 --x_msg_data := msg_data;
1005 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
1006 , p_count => x_msg_count
1007 , p_data => x_msg_data);
1008
1009 WHEN Row_Missing_Error THEN
1010 --GMD_API_PUB.Log_Message('GR_RECORD_NOT_FOUND');
1011 --ROLLBACK TO SAVEPOINT FIELD_NAME_CLASSES;
1012 x_return_status := 'E';
1013 FND_MESSAGE.SET_NAME('GR',
1014 'GR_RECORD_NOT_FOUND');
1015 FND_MESSAGE.SET_TOKEN('CODE',
1016 l_msg_token,
1017 FALSE);
1018 FND_MSG_PUB.ADD;
1019 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
1020 , p_count => x_msg_count
1021 , p_data => x_msg_data);
1022
1023 WHEN LT_Exists_Error THEN
1024
1025 x_return_status := 'E';
1026 oracle_error := APP_EXCEPTION.Get_Code;
1027 FND_MESSAGE.SET_NAME('GR',
1028 'GR_RECORD_EXISTS');
1029 FND_MESSAGE.SET_TOKEN('CODE',
1030 l_msg_token,
1031 FALSE);
1032 FND_MSG_PUB.ADD;
1033
1034 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
1035 , p_count => x_msg_count
1036 , p_data => x_msg_data);
1037
1038
1039 WHEN FND_API.G_EXC_ERROR THEN
1040 --ROLLBACK TO SAVEPOINT FIELD_NAME_CLASSES;
1041 x_return_status := FND_API.G_RET_STS_ERROR;
1042 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
1043 , p_count => x_msg_count
1044 , p_data => x_msg_data
1045 );
1046 x_msg_data := FND_MESSAGE.Get;
1047
1048 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1049 --ROLLBACK TO SAVEPOINT FIELD_NAME_CLASSES;
1050 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1051 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
1052 , p_count => x_msg_count
1053 , p_data => x_msg_data
1054 );
1055
1056 WHEN OTHERS THEN
1057 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1058 --ROLLBACK TO SAVEPOINT FIELD_NAME_CLASSES;
1059 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1060 , l_api_name
1061 );
1062
1063 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
1064 , p_count => x_msg_count
1065 , p_data => x_msg_data
1066 );
1067
1068 END FIELD_NAME_CLASSES;
1069
1070 END GR_FIELD_NAME_CLASSES_PUB;
[Home] [Help]