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