1 PACKAGE BODY GR_FIELD_NAME_PROPERTIES_PUB AS
2 /* $Header: GRPIFNPB.pls 120.1.12010000.2 2009/06/19 17:29:59 asatpute noship $
3 *****************************************************************
4 * *
5 * Package GR_FIELD_NAME_PROPERTIES_PUB *
6 * *
7 * Contents FIELD_NAME_PROPERTIES *
8 * *
9 * *
10 * Use This is the public layer for the FIELD_NAME_PROPERTIES*
11 * API *
12 * *
13 * History *
14 * Written by P A Lowe OPM Unlimited Dev *
15 * Peter Lowe 06/26/08 *
16 * *
17 * Updated By For *
18 * Peter Lowe 02/04/09 8208515 *
19 * *
20 *****************************************************************
21 */
22
23 -- Global variables
24
25 G_PKG_NAME CONSTANT VARCHAR2(30):='GR_FIELD_NAME_PROPERTIES_PUB';
26
27 --Forward declaration.
28 FUNCTION set_debug_flag RETURN VARCHAR2;
29 l_debug VARCHAR2(1) := set_debug_flag;
30
31 FUNCTION set_debug_flag RETURN VARCHAR2 IS
32 l_debug VARCHAR2(1):= 'N';
33 BEGIN
34 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
35 l_debug := 'Y';
36 END IF;
37 l_debug := 'Y';
38 RETURN l_debug;
39 END set_debug_flag;
40 PROCEDURE FIELD_NAME_PROPERTIES
41 ( p_api_version IN NUMBER
42 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
43 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
44 , p_action IN VARCHAR2
45 , p_object IN VARCHAR2
46 , p_property_id IN VARCHAR2
47 , p_property_type_indicator IN VARCHAR2
48 , p_length IN NUMBER
49 , p_precision IN NUMBER
50 , p_range_min IN NUMBER
51 , p_range_max IN NUMBER
52 , p_language IN VARCHAR2
53 , p_source_language IN VARCHAR2
54 , p_description IN VARCHAR2
55 , p_label_prop_values_tab IN GR_FIELD_NAME_PROPERTIES_PUB.gr_label_prop_values_tab_type
56 , x_return_status OUT NOCOPY VARCHAR2
57 , x_msg_count OUT NOCOPY NUMBER
58 , x_msg_data OUT NOCOPY VARCHAR2
59 )
60
61 IS
62 l_api_name CONSTANT VARCHAR2 (30) := 'FIELD_NAME_PROPERTIES';
63 l_api_version CONSTANT NUMBER := 1.0;
64 l_msg_count NUMBER :=0;
65 l_debug_flag VARCHAR2(1) := set_debug_flag;
66
67 l_property_id VARCHAR2(6);
68 l_language_code VARCHAR2(4);
69 l_missing_count NUMBER;
70
71 l_last_update_login NUMBER(15,0) := 0;
72 L_KEY_EXISTS VARCHAR2(1);
73
74 l_display_order NUMBER;
75 l_value VARCHAR2(30);
76 l_value_description VARCHAR2(240);
77
78 dummy NUMBER;
79 i NUMBER;
80 row_id VARCHAR2(18);
81 return_status VARCHAR2(1);
82 oracle_error NUMBER;
83 msg_data VARCHAR2(2000);
84
85 LBins_err EXCEPTION;
86 LCins_err EXCEPTION;
87 LTadd_err EXCEPTION;
88 LT_Exists_Error EXCEPTION;
89 ROW_MISSING_ERROR EXCEPTION;
90
91 -- Cursor Definitions
92
93 CURSOR c_get_language
94 IS
95 SELECT lng.language_code
96 FROM fnd_languages lng
97 WHERE lng.language_code = l_language_code;
98 LangRecord c_get_language%ROWTYPE;
99
100 CURSOR Cur_count_language IS
101 SELECT count (language_code)
102 FROM fnd_languages
103 WHERE installed_flag IN ('I', 'B')
104 AND language_code not in
105 (SELECT language
106 FROM GR_PROPERTIES_TL
107 WHERE PROPERTY_ID = p_PROPERTY_ID);
108
109 CURSOR c_get_property_id is
110 SELECT 1
111 FROM
112 GR_PROPERTIES_B B
113 where B.PROPERTY_ID = p_property_id;
114
115 CURSOR c_get_property_flag is
116 SELECT 1
117 FROM
118 GR_PROPERTIES_B B
119 where B.PROPERTY_ID = p_property_id
120 and property_type_indicator = 'F';
121
122 CURSOR c_get_gr_properties_tl
123 IS
124 SELECT 1
125 FROM gr_properties_tl prt
126 WHERE prt.property_id = p_property_id
127 AND prt.language = p_language;
128
129 L_MSG_TOKEN VARCHAR2(100);
130
131
132 BEGIN
133
134 -- Standard Start OF API savepoint
135
136 -- SAVEPOINT FIELD_NAME_PROPERTIES;
137
138 /* Standard call to check for call compatibility. */
139
140 IF NOT FND_API.Compatible_API_CALL
141 (l_api_version , p_api_version , l_api_name , G_PKG_NAME)
142 THEN
143 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
144 END IF;
145
146 /* Initialize message list if p_int_msg_list is set TRUE. */
147 IF FND_API.to_boolean(p_init_msg_list)
148 THEN
149 FND_MSG_PUB.Initialize;
150 END IF;
151
152 -- Initialize API return Parameters
153 gmd_debug.log_initialize('PAL euro trash');
154 x_return_status := FND_API.G_RET_STS_SUCCESS;
155
156 -- IF (l_debug = 'Y') THEN
157 -- gmd_debug.log_initialize('GR FIELD NAME CLASSES API');
158 -- END IF;
159
160
161
162 /* check mandatory inputs */
163
164 IF p_action is NULL or p_action not in ('I','U','D') then
165 --GMD_API_PUB.Log_Message('GR_INVALID_ACTION');
166 FND_MESSAGE.SET_NAME('GR',
167 'GR_INVALID_ACTION');
168 RAISE FND_API.G_EXC_ERROR;
169 END IF;
170
171 IF p_object is NULL or p_object not in ('C','L','V') then
172 --GMD_API_PUB.Log_Message('GR_INVALID_OBJECT');
173 FND_MESSAGE.SET_NAME('GR',
174 'GR_INVALID_OBJECT');
175 RAISE FND_API.G_EXC_ERROR;
176 END IF;
177
178
179 IF p_property_id is NULL then
180 -- GMD_API_PUB.Log_Message('SY_FIELDNAME');
181 FND_MESSAGE.SET_NAME('GMA',
182 'SY_FIELDNAME');
183 RAISE FND_API.G_EXC_ERROR;
184 END IF;
185
186 -- check Decimal precision is not > 6 if input -- 8208515
187 -- If an invalid value is passed in, an error message will be written to the log file.
188 IF p_precision is not NULL and p_precision > 6 then
189 GMD_API_PUB.Log_Message('GR_INVALID_PRECISION');
190 FND_MESSAGE.SET_NAME('GR',
191 'GR_INVALID_PRECISION');
192 RAISE FND_API.G_EXC_ERROR;
193 END IF;
194
195 -- end 8208515
196
197 l_property_id := p_property_id;
198 l_language_code := p_language;
199
200 IF p_action = 'I' then
201
202 IF p_object = 'C' then
203 -- Validate that the value of Property Id does not already exist in the table GR_PROPERTIES_B.
204 -- If it does, write an error to the log file.
205
206 dummy:= 0;
207 OPEN c_get_property_id;
208 FETCH c_get_property_id INTO dummy;
209 IF c_get_property_id%FOUND THEN
210
211 CLOSE c_get_property_id;
212 l_msg_token := p_property_id;
213 RAISE LT_Exists_Error;
214 END IF;
215
216 CLOSE c_get_property_id;
217 -- Property Type, Language, Source Language and Description values are required
218 -- and an error message will be written to the log file if any of the values are null.
219 IF p_property_type_indicator is NULL or p_source_language is NULL or p_language is NULL or p_description is null then
220 --GMD_API_PUB.Log_Message('SY_FIELDNAME');
221 FND_MESSAGE.SET_NAME('GMA',
222 'SY_FIELDNAME');
223
224 RAISE FND_API.G_EXC_ERROR;
225 END IF;
226 -- Validate that Property Type is set to either Flag, Numeric, Alphanumeric, Date, Risk Phrase or Safety Phrase.
227 -- If an invalid value is passed in, an error message will be written to the log file.
228 IF p_property_type_indicator not in ('F','N','A','D','R','S') then
229 GMD_API_PUB.Log_Message('SY_INVALID_TYPE');
230 FND_MESSAGE.SET_NAME('GMA',
231 'SY_INVALID_TYPE');
232
233 RAISE FND_API.G_EXC_ERROR;
234 END IF;
235
236 -- Decimal precision is only valid for a type of numeric
237 -- If an invalid value is passed in, an error message will be written to the log file.
238 IF p_property_type_indicator <> 'N' and p_precision is not NULL then
239 GMD_API_PUB.Log_Message('GR_INVALID_PRECISION');
240 FND_MESSAGE.SET_NAME('GR',
241 'GR_INVALID_PRECISION');
242 RAISE FND_API.G_EXC_ERROR;
243 END IF;
244
245 -- If minimum and maximum values are sent in, they must be validated against each other
246 -- (e.g. min can't be greater than max).
247 -- If an invalid value is passed in, an error message will be written to the log file.
248
249 IF p_range_min is not null and p_range_max is not null then
250 IF (p_range_min > p_range_max ) or ( p_range_max < p_range_min ) then
251 GMD_API_PUB.Log_Message('GR_INVALID_RANGE');
252 FND_MESSAGE.SET_NAME('GR',
253 'GR_INVALID_RANGE');
254 RAISE FND_API.G_EXC_ERROR;
255 END IF;
256 END IF; -- IF p_range_min is not null and p_range_max is not null then
257
258 -- The values for Property Id, Property Type, Length,
259 -- Decimal Precision, Minimum Value and Maximum Value will be written to the GR_PROPERTIES_B table.
260
261 GR_PROPERTIES_B_PKG.Insert_Row
262 (p_commit => 'F',
263 p_called_by_form => 'F',
264 p_property_id => p_property_id,
265 p_property_type_indicator => p_property_type_indicator,
266 p_length => p_length,
267 p_precision => p_precision,
268 p_range_min => p_range_min,
269 p_range_max => p_range_max,
270 p_created_by => FND_GLOBAL.USER_ID,
271 p_creation_date => SYSDATE,
272 p_last_updated_by => FND_GLOBAL.USER_ID,
273 p_last_update_date => SYSDATE,
274 p_last_update_login => l_last_update_login,
275 x_rowid => row_id,
276 x_return_status => return_status,
277 x_oracle_error => oracle_error,
278 x_msg_data => msg_data);
279
280 /*dbms_output.put_line('msg_data => ' || msg_data);
281 dbms_output.put_line('oracle_error => ' || oracle_error);
282 dbms_output.put_line(' return_status => ' || return_status); */
283
284 IF return_status <> 'S' THEN
285
286 IF (l_debug_flag = 'Y') THEN
287 gmd_debug.put_line('error');
288 END IF;
289 GMD_API_PUB.Log_Message(msg_data);
290 RAISE LBins_err;
291 END IF;
292
293
294
295 -- need to add base row for language for GR_PROPERTIES_TL
296
297 gr_properties_tl_pkg.insert_row(
298 p_commit => 'F',
299 p_called_by_form => 'F',
300 p_property_id => p_property_id,
301 p_language => p_language,
302 p_source_lang => p_source_language,
303 p_description => p_description,
304 p_created_by => fnd_global.user_id,
305 p_creation_date => sysdate,
306 p_last_updated_by => fnd_global.user_id,
307 p_last_update_date => sysdate,
308 p_last_update_login => l_last_update_login,
309 x_rowid => row_id,
310 x_return_status => return_status,
311 x_oracle_error => oracle_error,
312 x_msg_data => msg_data);
313
314
315 IF return_status <> 'S' THEN
316
317 IF (l_debug_flag = 'Y') THEN
318 gmd_debug.put_line('error');
319 END IF;
320 GMD_API_PUB.Log_Message(msg_data);
321 RAISE LCins_err;
322 END IF;
323
324
325 -- Insert a record into GR_PROPERTIES_TL for each installed language.
326
327 OPEN Cur_count_language;
328 FETCH Cur_count_language INTO l_missing_count;
329 CLOSE Cur_count_language;
330 IF l_missing_count > 0 THEN
331
332 gr_properties_tl_pkg.Add_Language
333 (p_commit => 'F',
334 p_called_by_form => 'F',
335 p_property_id => p_property_id,
336 p_language => p_language,
337 x_return_status => return_status,
338 x_oracle_error => oracle_error,
339 x_msg_data => msg_data);
340
341
342 /*dbms_output.put_line('msg_data => ' || msg_data);
343 dbms_output.put_line('oracle_error => ' || oracle_error);
344 dbms_output.put_line('return_status => ' || return_status); */
345
346 IF return_status <> 'S' THEN
347 GMD_API_PUB.Log_Message('GR_LABEL_CLASS_ADD_LANG_ERROR');
348 FND_MESSAGE.SET_NAME('GR',
349 'GR_LABEL_CLASS_ADD_LANG_ERROR');
350 FND_MSG_PUB.ADD;
351 RAISE LTadd_err;
352 END IF;
353
354 END IF; -- IF l_missing_count > 0 THEN
355
356 -- Insert all associated property values into the GR_PROPERTY_VALUES_TL table
357
358
359 FOR i IN 1 .. p_label_prop_values_tab.count LOOP
360
361 l_display_order := p_label_prop_values_tab(i).display_order;
362 l_value := p_label_prop_values_tab(i).value;
363 l_value_description := p_label_prop_values_tab(i).value_description;
364
365
366 IF l_display_order is NOT NULL or l_value is NOT NULL then
367
368 gr_property_values_tl_pkg.Insert_Row
369 (p_commit => 'F',
370 p_called_by_form => 'F',
371 p_property_id => p_property_id,
372 p_language => p_language,
373 p_value => l_value,
374 p_display_order=> l_display_order,
375 p_source_lang => p_source_language,
376 p_meaning => l_value_description,
377 p_created_by => fnd_global.user_id,
378 p_creation_date => sysdate,
379 p_last_updated_by => fnd_global.user_id,
380 p_last_update_date => sysdate,
381 p_last_update_login => l_last_update_login,
382 x_rowid => row_id,
383 x_return_status => return_status,
384 x_oracle_error => oracle_error,
385 x_msg_data => msg_data);
386
387
388
389 END IF; --IF l_display_order is NOT NULL or l_value is NOT NULL then
390
391 END LOOP; -- FOR i IN 1 ..p_label_prop_values_tab.count LOOP
392
393
394 ELSIF p_object = 'L' then
395
396 -- Validate that the value of Property Id exists in the table GR_PROPERTIES_B.
397 -- If it does not, write an error to the log file
398
399 dummy:= 0;
400 l_property_id := p_property_id;
401 OPEN c_get_property_id;
402 FETCH c_get_property_id INTO dummy;
403 IF c_get_property_id%NOTFOUND THEN
404
405 CLOSE c_get_property_id;
406 l_msg_token := l_property_id;
407 RAISE Row_Missing_Error;
408 END IF;
409
410 OPEN c_get_gr_properties_tl;
411 FETCH c_get_gr_properties_tl INTO dummy;
412 IF c_get_gr_properties_tl%FOUND THEN
413 x_return_status := 'E';
414 l_msg_token := p_property_id|| ' ' || p_language;
415 CLOSE c_get_gr_properties_tl;
416 RAISE LT_Exists_Error;
417 END IF;
418 CLOSE c_get_gr_properties_tl;
419
420
421 gr_properties_tl_pkg.insert_row(
422 p_commit => 'F',
423 p_called_by_form => 'F',
424 p_property_id => p_property_id,
425 p_language => p_language,
426 p_source_lang => p_source_language,
427 p_description => p_description,
428 p_created_by => fnd_global.user_id,
429 p_creation_date => sysdate,
430 p_last_updated_by => fnd_global.user_id,
431 p_last_update_date => sysdate,
432 p_last_update_login => l_last_update_login,
433 x_rowid => row_id,
434 x_return_status => return_status,
435 x_oracle_error => oracle_error,
436 x_msg_data => msg_data);
437
438
439 IF return_status <> 'S' THEN
440
441 IF (l_debug_flag = 'Y') THEN
442 gmd_debug.put_line('error');
443 END IF;
444 GMD_API_PUB.Log_Message(msg_data);
445 RAISE LCins_err;
446 END IF;
447
448
449 ELSE -- object = V value
450
451 --Validate that the value of Property Id exists in the table GR_PROPERTIES_B and that it is
452 -- of property type Flag If it does not, write an error to the log file.
453 dummy:= 0;
454 OPEN c_get_property_flag;
455 FETCH c_get_property_flag INTO dummy;
456 IF c_get_property_flag%NOTFOUND THEN
457
458 CLOSE c_get_property_flag;
459 l_msg_token := l_property_id|| ' F';
460 RAISE Row_Missing_Error;
461 END IF;
462
463
464 FOR i IN 1 .. p_label_prop_values_tab.count LOOP
465
466 l_display_order := p_label_prop_values_tab(i).display_order;
467 l_value := p_label_prop_values_tab(i).value;
468 l_value_description := p_label_prop_values_tab(i).value_description;
469
470 -- Validate that the value of Language for the specified property exists in the table GR_PROPERTY_VALUES_TL.
471 -- If it does , write an error to the log file
472
473 gr_property_values_tl_pkg.Check_Primary_Key
474 (p_property_id,
475 p_language,
476 l_value,
477 'F',
478 row_id,
479 l_key_exists);
480
481 IF FND_API.To_Boolean(l_key_exists) THEN
482 l_msg_token := p_property_id || ' ' || p_language || ' ' || l_value;
483 RAISE LT_Exists_Error;
484 END IF;
485
486
487 IF l_display_order is NOT NULL or l_value is NOT NULL then
488
489 gr_property_values_tl_pkg.Insert_Row
490 (p_commit => 'F',
491 p_called_by_form => 'F',
492 p_property_id => p_property_id,
493 p_language => p_language,
494 p_value => l_value,
495 p_display_order=> l_display_order,
496 p_source_lang => p_source_language,
497 p_meaning => l_value_description,
498 p_created_by => fnd_global.user_id,
499 p_creation_date => sysdate,
500 p_last_updated_by => fnd_global.user_id,
501 p_last_update_date => sysdate,
502 p_last_update_login => l_last_update_login,
503 x_rowid => row_id,
504 x_return_status => return_status,
505 x_oracle_error => oracle_error,
506 x_msg_data => msg_data);
507
508 END IF; --IF l_display_order is NOT NULL or l_value is NOT NULL then
509
510 END LOOP; -- FOR i IN 1 .. p_label_prop_values_tab.count LOOP
511
512 END IF; -- IF p_object = 'C' then
513
514 -- next is U action
515
516
517 /*************************************************************************************/
518
519 ELSIF p_action = 'U' then
520
521 -- Validate that the value of Property Id exists in the table GR_PROPERTIES_B.
522 -- If it does not, an error message will be written to the log file.
523
524 dummy:= 0;
525 OPEN c_get_property_id;
526 FETCH c_get_property_id INTO dummy;
527 IF c_get_property_id%NOTFOUND THEN
528 CLOSE c_get_property_id;
529 l_msg_token := l_property_id;
530 RAISE Row_Missing_Error;
531 END IF;
532 CLOSE c_get_property_id;
533
534
535 IF p_object = 'C' then
536
537 -- Any non-null, valid values passed in for property type,
538 -- length, decimal precision, minimum value and maximum value will be updated in the GR_PROPERTIES_B table.
539
540 UPDATE GR_PROPERTIES_B
541 SET length = nvl(p_length,length),
542 precision = nvl(p_precision,precision),
543 range_min = nvl(p_range_min,range_min),
544 range_max = nvl(p_range_max,range_max),
545 last_updated_by = FND_GLOBAL.USER_ID,
546 last_update_date = SYSDATE,
547 last_update_login = l_last_update_login
548 WHERE property_id = l_property_id;
549 IF SQL%NOTFOUND THEN
550 RAISE Row_Missing_Error;
551 END IF;
552
553 ELSIF p_object = 'L' then
554
555 -- If the value for Language is null or invalid, write an error to the log file.
556
557 IF p_language is NULL then
558 l_msg_token := l_language_code;
559 RAISE Row_Missing_Error;
560 END IF;
561
562 /* Check the language codes */
563
564 l_language_code := p_language;
565 OPEN c_get_language;
566 FETCH c_get_language INTO LangRecord;
567 IF c_get_language%NOTFOUND THEN
568 CLOSE c_get_language;
569 l_msg_token := l_language_code;
570 RAISE Row_Missing_Error;
571 END IF;
572 CLOSE c_get_language;
573
574 -- If the record for the specified phrase code and language
575 -- does not exist in the GR_PROPERTIES_TL table, an error will be written to the log file
576
577
578 gr_properties_tl_pkg.Check_Primary_Key(
579 p_property_id,
580 p_language,
581 'F',
582 row_id,
583 l_key_exists);
584
585 IF l_key_exists = 'N' THEN
586 l_msg_token := p_property_id|| ' ' || p_language;
587 RAISE Row_Missing_Error;
588 END IF;
589 -- The value for Property Description will be updated GR_PROPERTIES_TL table for the specified language.
590
591 UPDATE GR_PROPERTIES_TL
592 SET description = p_description,
593 last_updated_by = FND_GLOBAL.USER_ID,
594 last_update_date = SYSDATE,
595 last_update_login = l_last_update_login
596 WHERE property_id = p_property_id
597 and language = p_language;
598 IF SQL%NOTFOUND THEN
599 l_msg_token := p_property_id || ' ' || p_language;
600 RAISE Row_Missing_Error;
601 END IF;
602
603 ELSE -- object = V (Value)
604
605 -- Validate that the value of Property Id exists in the table GR_PROPERTIES_B
606 -- and that it is of property type Flag If it does not, write an error to the log file
607
608 dummy:= 0;
609 OPEN c_get_property_flag;
610 FETCH c_get_property_flag INTO dummy;
611 IF c_get_property_flag%NOTFOUND THEN
612
613 CLOSE c_get_property_flag;
614 l_msg_token := l_property_id|| ' F';
615 RAISE Row_Missing_Error;
616 END IF;
617
618 -- Validate that the value of Language for the specified property exists in the
619 -- table GR_PROPERTY_VALUES_TL. If it does, write an error to the log file.
620
621 gr_properties_tl_pkg.Check_Primary_Key
622 (p_property_id,
623 p_language,
624 'F',
625 row_id,
626 l_key_exists);
627
628 IF l_key_exists = 'N' THEN
629 l_msg_token := p_property_id || ' ' || p_language;
630 RAISE LT_Exists_Error;
631 END IF;
632
633 -- The values for Language, Value and Value Description will be written to the GR_PROPERTY_VALUES_TL table.
634 FOR i IN 1 .. p_label_prop_values_tab.count LOOP
635
636 l_display_order := p_label_prop_values_tab(i).display_order;
637 l_value := p_label_prop_values_tab(i).value;
638 l_value_description := p_label_prop_values_tab(i).value_description;
639
640
641 IF l_value is NOT NULL then
642
643 UPDATE GR_PROPERTY_values_TL
644 SET meaning = l_value_description,
645 last_updated_by = FND_GLOBAL.USER_ID,
646 last_update_date = SYSDATE,
647 last_update_login = l_last_update_login
648 WHERE property_id = p_property_id
649 and language = p_language
650 and value = l_value;
651 IF SQL%NOTFOUND THEN
652 l_msg_token := p_property_id || ' ' || p_language;
653 RAISE Row_Missing_Error;
654 END IF;
655
656 END IF; --IF l_display_order is NOT NULL or l_value is NOT NULL then
657
658 END LOOP; -- FOR i IN 1 .. p_label_prop_values_tab.count LOOP
659
660
661
662
663 END IF; -- IF p_object = 'C' then
664
665 ELSE -- action is D (delete)
666
667 -- Validate that the value of Property Id exists in the table GR_PROPERTIES_B.
668 -- If it does not, an error message will be written to the log file.
669
670 dummy:= 0;
671 OPEN c_get_property_id;
672 FETCH c_get_property_id INTO dummy;
673 IF c_get_property_id%NOTFOUND THEN
674 CLOSE c_get_property_id;
675 l_msg_token := p_property_id;
676 RAISE Row_Missing_Error;
677 END IF;
678 CLOSE c_get_property_id;
679
680
681 IF p_object = 'C' then
682
683 -- Delete all of the property related records in the GR_PROPERTIES_B, GR_PROPERTIES_TL and GR_PROPERTY_VALUES_TL tables.
684
685 delete from GR_PROPERTY_VALUES_TL T
686 where t.PROPERTY_ID = p_PROPERTY_ID;
687
688 delete from GR_PROPERTIES_TL T
689 where t.PROPERTY_ID = p_PROPERTY_ID;
690
691 DELETE FROM GR_PROPERTIES_B
692 where PROPERTY_ID = p_PROPERTY_ID;
693
694 IF SQL%NOTFOUND THEN
695 l_msg_token := p_PROPERTY_ID;
696 RAISE Row_Missing_Error;
697 END IF;
698
699 ELSIF p_object = 'L' then
700
701 -- If the value for Language is null or invalid, write an error to the log file.
702
703 IF p_language is NULL then
704 l_msg_token := l_language_code;
705 RAISE Row_Missing_Error;
706 END IF;
707
708 /* Check the language codes */
709
710 l_language_code := p_language;
711 OPEN c_get_language;
712 FETCH c_get_language INTO LangRecord;
713 IF c_get_language%NOTFOUND THEN
714 CLOSE c_get_language;
715 l_msg_token := l_language_code;
716 RAISE Row_Missing_Error;
717 END IF;
718 CLOSE c_get_language;
719
720
721 -- if the record for the property_id
722 --and language does not exist in the GR_PROPERTIES_TL table, an error will be written to the log file.
723
724 gr_properties_tl_pkg.Check_Primary_Key
725 (p_property_id,
726 p_language,
727 'F',
728 row_id,
729 l_key_exists);
730
731 IF l_key_exists = 'N' THEN
732 l_msg_token := p_property_id || ' ' || p_language;
733 RAISE LT_Exists_Error;
734 END IF;
735
736 -- Delete the record in GR_PROPERTIES_TL table for the specified language
737
738 delete from GR_PROPERTIES_TL
739 WHERE property_id = p_property_id
740 and language = p_language;
741 IF SQL%NOTFOUND THEN
742 l_msg_token := p_property_id || ' ' || p_language;
743 RAISE Row_Missing_Error;
744 END IF;
745
746
747 ELSE -- object = V(value)
748
749 -- Validate that the value of Property Id exists in the table GR_PROPERTIES_B
750 -- and that it is of property type Flag If it does not, write an error to the log file
751
752 dummy:= 0;
753 OPEN c_get_property_flag;
754 FETCH c_get_property_flag INTO dummy;
755 IF c_get_property_flag%NOTFOUND THEN
756
757 CLOSE c_get_property_flag;
758 l_msg_token := p_property_id|| ' F';
759 RAISE Row_Missing_Error;
760 END IF;
761
762
763
764 -- Loop through records in input table
765
766 FOR i IN 1 .. p_label_prop_values_tab.count LOOP
767
768 l_display_order := p_label_prop_values_tab(i).display_order;
769 l_value := p_label_prop_values_tab(i).value;
770 l_value_description := p_label_prop_values_tab(i).value_description;
771
772 -- Validate that the value of specified property and language and value does exists in the table GR_PROPERTY_VALUES_TL.
773 -- If it does not , write an error to the log file
774
775 gr_property_values_tl_pkg.Check_Primary_Key
776 (p_property_id,
777 p_language,
778 l_value,
779 'F',
780 row_id,
781 l_key_exists);
782
783 IF l_key_exists = 'N' THEN
784 l_msg_token := p_property_id|| ' ' || p_language || ' ' || l_value;
785 RAISE Row_Missing_Error;
786 END IF;
787
788 -- Delete the record in GR_PROPERTY_VALUES_TL table for the specified language. .
789
790 IF l_value is NOT NULL then
791
792
793 delete from GR_PROPERTY_values_TL
794 WHERE property_id = p_property_id
795 and language = p_language
796 and value = l_value;
797 IF SQL%NOTFOUND THEN
798 l_msg_token := p_property_id || ' ' || p_language;
799 RAISE Row_Missing_Error;
800 END IF;
801
802 END IF; --IF l_value is NOT NULL then
803
804 END LOOP; -- FOR i IN 1 .. p_label_prop_values_tab.count LOOP
805
806
807 END IF; -- IF p_object = 'C' then
808
809 END IF; -- IF p_action = 'I' then
810 IF x_return_status IN (FND_API.G_RET_STS_SUCCESS) AND (FND_API.To_Boolean( p_commit ) ) THEN
811 Commit;
812 END IF;
813
814 EXCEPTION
815 WHEN LBins_err THEN
816 x_return_status := FND_API.G_RET_STS_ERROR;
817 -- ROLLBACK TO SAVEPOINT FIELD_NAMES;
818 --x_msg_data := msg_data;
819 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
820 , p_count => x_msg_count
821 , p_data => x_msg_data);
822
823 WHEN LCins_err THEN
824
825 x_return_status := FND_API.G_RET_STS_ERROR;
826 -- ROLLBACK TO SAVEPOINT FIELD_NAMES;
827 --x_msg_data := msg_data;
828 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
829 , p_count => x_msg_count
830 , p_data => x_msg_data);
831
832 WHEN LTadd_err THEN
833 x_return_status := FND_API.G_RET_STS_ERROR;
834 --ROLLBACK TO SAVEPOINT FIELD_NAME_PROPERTIES;
835 FND_MSG_PUB.COUNT_AND_GET (P_count => x_msg_count,
836 P_data => x_msg_data);
837
838 WHEN Row_Missing_Error THEN
839 --GMD_API_PUB.Log_Message('GR_RECORD_NOT_FOUND');
840 --ROLLBACK TO SAVEPOINT FIELD_NAME_PROPERTIES;
841 x_return_status := 'E';
842 FND_MESSAGE.SET_NAME('GR',
843 'GR_RECORD_NOT_FOUND');
844 FND_MESSAGE.SET_TOKEN('CODE',
845 l_msg_token,
846 FALSE);
847 FND_MSG_PUB.ADD;
848 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
849 , p_count => x_msg_count
850 , p_data => x_msg_data);
851
852 WHEN LT_Exists_Error THEN
853 x_return_status := 'E';
854 oracle_error := APP_EXCEPTION.Get_Code;
855 FND_MESSAGE.SET_NAME('GR',
856 'GR_RECORD_EXISTS');
857 FND_MESSAGE.SET_TOKEN('CODE',
858 l_msg_token,
859 FALSE);
860 FND_MSG_PUB.ADD;
861 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
862 , p_count => x_msg_count
863 , p_data => x_msg_data);
864
865
866 WHEN FND_API.G_EXC_ERROR THEN
867 --ROLLBACK TO SAVEPOINT FIELD_NAME_PROPERTIES;
868 x_return_status := FND_API.G_RET_STS_ERROR;
869 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
870 , p_count => x_msg_count
871 , p_data => x_msg_data
872 );
873 x_msg_data := FND_MESSAGE.Get;
874
875 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
876 --ROLLBACK TO SAVEPOINT FIELD_NAME_PROPERTIES;
877 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
878 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
879 , p_count => x_msg_count
880 , p_data => x_msg_data
881 );
882
883 WHEN OTHERS THEN
884 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
885 --ROLLBACK TO SAVEPOINT FIELD_NAME_PROPERTIES;
886 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
887 , l_api_name
888 );
889
890 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
891 , p_count => x_msg_count
892 , p_data => x_msg_data
893 );
894
895 END FIELD_NAME_PROPERTIES;
896
897 END GR_FIELD_NAME_PROPERTIES_PUB;
[Home] [Help]