[Home] [Help]
PACKAGE BODY: APPS.FND_FLEX_DIAGNOSE
Source
1 PACKAGE BODY fnd_flex_diagnose AS
2 /* $Header: AFFFDGNB.pls 120.4.12010000.1 2008/07/25 14:13:53 appldev ship $ */
3
4 -- ==================================================
5 -- Constants and Types.
6 -- ==================================================
7 g_api_name VARCHAR2(10) := 'DGN.';
8 g_std_date_mask VARCHAR2(100) := 'YYYY/MM/DD HH24:MI:SS';
9 g_line_size NUMBER := 75;
10 g_text_size NUMBER := 1950;
11 g_newline VARCHAR2(10);
12
13 SUBTYPE app_type IS fnd_application%ROWTYPE;
14 SUBTYPE tbl_type IS fnd_tables%ROWTYPE;
15 SUBTYPE col_type IS fnd_columns%ROWTYPE;
16 SUBTYPE rsp_type IS fnd_responsibility%ROWTYPE;
17
18 SUBTYPE vst_set_type IS fnd_flex_value_sets%ROWTYPE;
19 SUBTYPE vst_tbl_type IS fnd_flex_validation_tables%ROWTYPE;
20 SUBTYPE vst_evt_type IS fnd_flex_validation_events%ROWTYPE;
21 SUBTYPE vst_scr_type IS fnd_flex_value_rules%ROWTYPE;
22 SUBTYPE vst_scl_type IS fnd_flex_value_rule_lines%ROWTYPE;
23 SUBTYPE vst_scu_type IS fnd_flex_value_rule_usages%ROWTYPE;
24 SUBTYPE vst_rlg_type IS fnd_flex_hierarchies%ROWTYPE;
25 SUBTYPE vst_val_type IS fnd_flex_values%ROWTYPE;
26 SUBTYPE vst_fvn_type IS fnd_flex_value_norm_hierarchy%ROWTYPE;
27 SUBTYPE vst_fvh_type IS fnd_flex_value_hierarchies%ROWTYPE;
28
29 SUBTYPE dff_flx_type IS fnd_descriptive_flexs%ROWTYPE;
30 SUBTYPE dff_ctx_type IS fnd_descr_flex_contexts%ROWTYPE;
31 SUBTYPE dff_seg_type IS fnd_descr_flex_column_usages%ROWTYPE;
32
33 SUBTYPE kff_flx_type IS fnd_id_flexs%ROWTYPE;
34 SUBTYPE kff_str_type IS fnd_id_flex_structures%ROWTYPE;
35 SUBTYPE kff_seg_type IS fnd_id_flex_segments%ROWTYPE;
36 SUBTYPE kff_flq_type IS fnd_segment_attribute_types%ROWTYPE;
37 SUBTYPE kff_qlv_type IS fnd_segment_attribute_values%ROWTYPE;
38 SUBTYPE kff_sgq_type IS fnd_value_attribute_types%ROWTYPE;
39 SUBTYPE kff_sha_type IS fnd_shorthand_flex_aliases%ROWTYPE;
40 SUBTYPE kff_cvr_type IS fnd_flex_validation_rules%ROWTYPE;
41 SUBTYPE kff_cvl_type IS fnd_flex_validation_rule_lines%ROWTYPE;
42 SUBTYPE kff_cvi_type IS fnd_flex_include_rule_lines%ROWTYPE;
43 SUBTYPE kff_cve_type IS fnd_flex_exclude_rule_lines%ROWTYPE;
44 SUBTYPE kff_fwp_type IS fnd_flex_workflow_processes%ROWTYPE;
45
46 -- ***************************************************************************
47 -- * Helper functions.
48 -- ***************************************************************************
49 -- ---------------------------------------------------------------------------
50 -- Checks the existance of a lookup code.
51 --
52 FUNCTION lookup_code_exists(p_lookup_type IN VARCHAR2,
53 p_lookup_code IN VARCHAR2)
54 RETURN BOOLEAN
55 IS
56 l_vc2 VARCHAR2(100);
57 BEGIN
58 SELECT NULL
59 INTO l_vc2
60 FROM fnd_lookups
61 WHERE lookup_type = p_lookup_type
62 AND lookup_code = p_lookup_code;
63 RETURN(TRUE);
64 EXCEPTION
65 WHEN OTHERS THEN
66 RETURN(FALSE);
67 END lookup_code_exists;
68
69 -- ---------------------------------------------------------------------------
70 -- Formats the string to fit into single line.
71 --
72 FUNCTION line_return(p_in VARCHAR2)
73 RETURN VARCHAR2
74 IS
75 BEGIN
76 RETURN(Substr(p_in, 1, g_line_size));
77 END line_return;
78
79 -- ---------------------------------------------------------------------------
80 -- Formats the string to fit into mutliple lines.
81 --
82 FUNCTION text_return(p_in VARCHAR2)
83 RETURN VARCHAR2
84 IS
85 BEGIN
86 RETURN(Substr(p_in, 1, g_text_size));
87 END text_return;
88
89 -- ---------------------------------------------------------------------------
90 -- Updates flexfield info in fnd_columns.
91 --
92 FUNCTION update_fnd_columns(p_col IN col_type,
93 p_flexfield_usage_code IN VARCHAR2,
94 p_flexfield_application_id IN NUMBER,
95 p_flexfield_name IN VARCHAR2,
96 x_message OUT nocopy VARCHAR2)
97 RETURN BOOLEAN
98 IS
99 BEGIN
100 UPDATE fnd_columns SET
101 flexfield_usage_code = p_flexfield_usage_code,
102 flexfield_application_id = p_flexfield_application_id,
103 flexfield_name = p_flexfield_name,
104 last_update_date = Sysdate,
105 last_updated_by = 1
106 WHERE application_id = p_col.application_id
107 AND table_id = p_col.table_id
108 AND column_id = p_col.column_id;
109 x_message := SQL%rowcount || ' row(s) updated.';
110 RETURN (TRUE);
111 EXCEPTION
112 WHEN OTHERS THEN
113 x_message := 'Unable to update FND_COLUMNS. ' || Sqlerrm;
114 RETURN(FALSE);
115 END update_fnd_columns;
116
117 -- ---------------------------------------------------------------------------
118 -- Returns 'Unable to select from...' error.
119 --
120 FUNCTION msg_uts(p_table_name IN VARCHAR2,
121 p_key1 IN VARCHAR2,
122 p_value1 IN VARCHAR2,
123 p_key2 IN VARCHAR2 DEFAULT NULL,
124 p_value2 IN VARCHAR2 DEFAULT NULL,
125 p_key3 IN VARCHAR2 DEFAULT NULL,
126 p_value3 IN VARCHAR2 DEFAULT NULL,
127 p_key4 IN VARCHAR2 DEFAULT NULL,
128 p_value4 IN VARCHAR2 DEFAULT NULL,
129 p_key5 IN VARCHAR2 DEFAULT NULL,
130 p_value5 IN VARCHAR2 DEFAULT NULL,
131 p_key6 IN VARCHAR2 DEFAULT NULL,
132 p_value6 IN VARCHAR2 DEFAULT NULL)
133 RETURN VARCHAR2
134 IS
135 l_message VARCHAR2(32000);
136 BEGIN
137 l_message := 'Unable to select from ' || p_table_name || g_newline ||
138 Rpad(Upper(p_key1),31,' ') || ':''' || p_value1 || '''';
139 IF (p_key2 IS NOT NULL) THEN
140 l_message := l_message || g_newline ||
141 Rpad(Upper(p_key2),31,' ') || ':''' || p_value2 || '''';
142 IF (p_key3 IS NOT NULL) THEN
143 l_message := l_message || g_newline ||
144 Rpad(Upper(p_key3),31,' ') || ':''' || p_value3 || '''';
145 IF (p_key4 IS NOT NULL) THEN
146 l_message := l_message || g_newline ||
147 Rpad(Upper(p_key4),31,' ') || ':''' || p_value4 || '''';
148 IF (p_key5 IS NOT NULL) THEN
149 l_message := l_message || g_newline ||
150 Rpad(Upper(p_key5),31,' ') || ':''' || p_value5 || '''';
151 IF (p_key6 IS NOT NULL) THEN
152 l_message := l_message || g_newline ||
153 Rpad(Upper(p_key6),31,' ') || ':''' || p_value6 || '''';
154 END IF;
155 END IF;
156 END IF;
157 END IF;
158 END IF;
159 l_message := l_message || g_newline || 'SQLERRM: ' || Sqlerrm;
160 RETURN(text_return(l_message));
161 EXCEPTION
162 WHEN OTHERS THEN
163 RETURN(text_return('Unable to select from ' || p_table_name ||
164 g_newline || 'SQLERRM: ' || Sqlerrm));
165 END msg_uts;
166
167 -- ***************************************************************************
168 -- * Common fetch_stg() functions.
169 -- ***************************************************************************
170 -- ---------------------------------------------------------------------------
171 FUNCTION fetch_tbl(p_application_id IN NUMBER,
172 p_table_name IN VARCHAR2,
173 x_tbl OUT nocopy tbl_type,
174 x_message OUT nocopy VARCHAR2)
175 RETURN BOOLEAN
176 IS
177 BEGIN
178 SELECT *
179 INTO x_tbl
180 FROM fnd_tables
181 WHERE application_id = p_application_id
182 AND table_name = p_table_name;
183 RETURN(TRUE);
184 EXCEPTION
185 WHEN OTHERS THEN
186 x_message := msg_uts('FND_TABLES',
187 'application_id', p_application_id,
188 'table_name', p_table_name);
189 RETURN(FALSE);
190 END fetch_tbl;
191 -- ---------------------------------------------------------------------------
192 FUNCTION fetch_col(p_tbl IN tbl_type,
193 p_column_name IN VARCHAR2,
194 x_col OUT nocopy col_type,
195 x_message OUT nocopy VARCHAR2)
196 RETURN BOOLEAN
197 IS
198 BEGIN
199 SELECT *
200 INTO x_col
201 FROM fnd_columns
202 WHERE application_id = p_tbl.application_id
203 AND table_id = p_tbl.table_id
204 AND column_name = p_column_name;
205 RETURN(TRUE);
206 EXCEPTION
207 WHEN OTHERS THEN
208 x_message := msg_uts('FND_COLUMNS',
209 'application_id', p_tbl.application_id,
210 'table_id', p_tbl.table_id,
211 'column_name', p_column_name);
212 RETURN(FALSE);
213 END fetch_col;
214
215 -- ***************************************************************************
216 -- * VST fetch_vst_stg() functions.
217 -- ***************************************************************************
218 -- ---------------------------------------------------------------------------
219 FUNCTION fetch_vst_set(p_flex_value_set_id IN NUMBER,
220 x_vst_set OUT nocopy vst_set_type,
221 x_message OUT nocopy VARCHAR2)
222 RETURN BOOLEAN
223 IS
224 BEGIN
225 SELECT *
226 INTO x_vst_set
227 FROM fnd_flex_value_sets
228 WHERE flex_value_set_id = p_flex_value_set_id;
229 RETURN(TRUE);
230 EXCEPTION
231 WHEN OTHERS THEN
232 x_message := msg_uts('FND_FLEX_VALUE_SETS',
233 'flex_value_set_id', p_flex_value_set_id);
234 RETURN(FALSE);
235 END fetch_vst_set;
236 -- ---------------------------------------------------------------------------
237 FUNCTION fetch_vst_tbl(p_vst_set IN vst_set_type,
238 x_vst_tbl OUT nocopy vst_tbl_type,
239 x_message OUT nocopy VARCHAR2)
240 RETURN BOOLEAN
241 IS
242 BEGIN
243 SELECT *
244 INTO x_vst_tbl
245 FROM fnd_flex_validation_tables
246 WHERE flex_value_set_id = p_vst_set.flex_value_set_id;
247 RETURN(TRUE);
248 EXCEPTION
249 WHEN OTHERS THEN
250 x_message := msg_uts('FND_FLEX_VALIDATION_TABLES',
251 'flex_value_set_id', p_vst_set.flex_value_set_id);
252 RETURN(FALSE);
253 END fetch_vst_tbl;
254 -- ---------------------------------------------------------------------------
255 FUNCTION fetch_vst_evt(p_vst_set IN vst_set_type,
256 p_event_code IN VARCHAR2,
257 x_vst_evt OUT nocopy vst_evt_type,
258 x_message OUT nocopy VARCHAR2)
259 RETURN BOOLEAN
260 IS
261 BEGIN
262 SELECT *
263 INTO x_vst_evt
264 FROM fnd_flex_validation_events
265 WHERE flex_value_set_id = p_vst_set.flex_value_set_id
266 AND event_code = p_event_code;
267 RETURN(TRUE);
268 EXCEPTION
269 WHEN OTHERS THEN
270 x_message := msg_uts('FND_FLEX_VALIDATION_EVENTS',
271 'flex_value_set_id', p_vst_set.flex_value_set_id,
272 'event_code', p_event_code);
273 RETURN(FALSE);
274 END fetch_vst_evt;
275 -- ---------------------------------------------------------------------------
276 FUNCTION fetch_vst_scr(p_vst_set IN vst_set_type,
277 p_flex_value_rule_id IN NUMBER,
278 x_vst_scr OUT nocopy vst_scr_type,
279 x_message OUT nocopy VARCHAR2)
280 RETURN BOOLEAN
281 IS
282 BEGIN
283 SELECT *
284 INTO x_vst_scr
285 FROM fnd_flex_value_rules
286 WHERE flex_value_set_id = p_vst_set.flex_value_set_id
287 AND flex_value_rule_id = p_flex_value_rule_id;
288 RETURN(TRUE);
289 EXCEPTION
290 WHEN OTHERS THEN
291 x_message := msg_uts('FND_FLEX_VALUE_RULES',
292 'flex_value_set_id', p_vst_set.flex_value_set_id,
293 'flex_value_rule_id', p_flex_value_rule_id);
294 RETURN(FALSE);
295 END fetch_vst_scr;
296 -- ---------------------------------------------------------------------------
297 FUNCTION fetch_vst_scl(p_vst_scr IN vst_scr_type,
298 p_include_exclude_indicator IN VARCHAR2,
299 p_flex_value_low IN VARCHAR2,
300 p_flex_value_high IN VARCHAR2,
301 x_vst_scl OUT nocopy vst_scl_type,
302 x_message OUT nocopy VARCHAR2)
303 RETURN BOOLEAN
304 IS
305 BEGIN
306 SELECT *
307 INTO x_vst_scl
308 FROM fnd_flex_value_rule_lines
309 WHERE flex_value_set_id = p_vst_scr.flex_value_set_id
310 AND flex_value_rule_id = p_vst_scr.flex_value_rule_id
311 AND include_exclude_indicator = p_include_exclude_indicator
312 AND Nvl(flex_value_low, '$FLEX$.NULL') =
313 Nvl(p_flex_value_low, '$FLEX$.NULL')
314 AND Nvl(flex_value_high, '$FLEX$.NULL') =
315 Nvl(p_flex_value_high, '$FLEX$.NULL');
316 RETURN(TRUE);
317 EXCEPTION
318 WHEN OTHERS THEN
319 x_message := msg_uts('FND_FLEX_VALUE_RULE_LINES',
320 'flex_value_set_id', p_vst_scr.flex_value_set_id,
321 'flex_value_rule_id', p_vst_scr.flex_value_rule_id,
322 'include_exclude_indicator', p_include_exclude_indicator,
323 'flex_value_low', p_flex_value_low,
324 'flex_value_high', p_flex_value_high);
325 RETURN(FALSE);
326 END fetch_vst_scl;
327 -- ---------------------------------------------------------------------------
328 FUNCTION fetch_vst_scu(p_vst_scr IN vst_scr_type,
329 p_application_id IN NUMBER,
330 p_responsibility_id IN NUMBER,
331 x_vst_scu OUT nocopy vst_scu_type,
332 x_message OUT nocopy VARCHAR2)
333 RETURN BOOLEAN
334 IS
335 BEGIN
336 SELECT *
337 INTO x_vst_scu
338 FROM fnd_flex_value_rule_usages
339 WHERE flex_value_set_id = p_vst_scr.flex_value_set_id
340 AND flex_value_rule_id = p_vst_scr.flex_value_rule_id
341 AND application_id = p_application_id
342 AND responsibility_id = p_responsibility_id;
343 RETURN(TRUE);
344 EXCEPTION
345 WHEN OTHERS THEN
346 x_message := msg_uts('FND_FLEX_VALUE_RULE_USAGES',
347 'flex_value_set_id', p_vst_scr.flex_value_set_id,
348 'flex_value_rule_id', p_vst_scr.flex_value_rule_id,
349 'application_id', p_application_id,
350 'responsibility_id', p_responsibility_id);
351 RETURN(FALSE);
352 END fetch_vst_scu;
353 -- ---------------------------------------------------------------------------
354 FUNCTION fetch_vst_rlg(p_vst_set IN vst_set_type,
355 p_hierarchy_id IN NUMBER,
356 x_vst_rlg OUT nocopy vst_rlg_type,
357 x_message OUT nocopy VARCHAR2)
358 RETURN BOOLEAN
359 IS
360 BEGIN
361 SELECT *
362 INTO x_vst_rlg
363 FROM fnd_flex_hierarchies
364 WHERE flex_value_set_id = p_vst_set.flex_value_set_id
365 AND hierarchy_id = p_hierarchy_id;
366 RETURN(TRUE);
367 EXCEPTION
368 WHEN OTHERS THEN
369 x_message := msg_uts('FND_FLEX_HIERARCHIES',
370 'flex_value_set_id', p_vst_set.flex_value_set_id,
371 'hierarchy_id', p_hierarchy_id);
372 RETURN(FALSE);
373 END fetch_vst_rlg;
374 -- ---------------------------------------------------------------------------
375 FUNCTION fetch_vst_val(p_vst_set IN vst_set_type,
376 p_flex_value_id IN NUMBER,
377 x_vst_val OUT nocopy vst_val_type,
378 x_message OUT nocopy VARCHAR2)
379 RETURN BOOLEAN
380 IS
381 BEGIN
382 SELECT *
383 INTO x_vst_val
384 FROM fnd_flex_values
385 WHERE flex_value_set_id = p_vst_set.flex_value_set_id
386 AND flex_value_id = p_flex_value_id;
387 RETURN(TRUE);
388 EXCEPTION
389 WHEN OTHERS THEN
390 x_message := msg_uts('FND_FLEX_VALUES',
391 'flex_value_set_id', p_vst_set.flex_value_set_id,
392 'flex_value_id', p_flex_value_id);
393 RETURN(FALSE);
394 END fetch_vst_val;
395
396 -- ***************************************************************************
397 -- * DFF fetch_dff_stg() functions.
398 -- ***************************************************************************
399 -- ---------------------------------------------------------------------------
400 FUNCTION fetch_dff_flx(p_application_id IN NUMBER,
401 p_descriptive_flexfield_name IN VARCHAR2,
402 x_dff_flx OUT nocopy dff_flx_type,
403 x_message OUT nocopy VARCHAR2)
404 RETURN BOOLEAN
405 IS
406 BEGIN
407 SELECT *
408 INTO x_dff_flx
409 FROM fnd_descriptive_flexs
410 WHERE application_id = p_application_id
411 AND descriptive_flexfield_name = p_descriptive_flexfield_name;
412 RETURN(TRUE);
413 EXCEPTION
414 WHEN OTHERS THEN
415 x_message := msg_uts('FND_DESCRIPTIVE_FLEXS',
416 'application_id', p_application_id,
417 'descriptive_flexfield_name', p_descriptive_flexfield_name);
418 RETURN(FALSE);
419 END fetch_dff_flx;
420 -- ---------------------------------------------------------------------------
421 FUNCTION fetch_dff_ctx(p_dff_flx IN dff_flx_type,
422 p_descriptive_flex_context_cod IN VARCHAR2,
423 x_dff_ctx OUT nocopy dff_ctx_type,
424 x_message OUT nocopy VARCHAR2)
425 RETURN BOOLEAN
426 IS
427 BEGIN
428 SELECT *
429 INTO x_dff_ctx
430 FROM fnd_descr_flex_contexts
431 WHERE application_id = p_dff_flx.application_id
432 AND descriptive_flexfield_name = p_dff_flx.descriptive_flexfield_name
433 AND descriptive_flex_context_code = p_descriptive_flex_context_cod;
434 RETURN(TRUE);
435 EXCEPTION
436 WHEN OTHERS THEN
437 x_message := msg_uts('FND_DESCR_FLEX_CONTEXTS',
438 'application_id', p_dff_flx.application_id,
439 'descriptive_flexfield_name', p_dff_flx.descriptive_flexfield_name,
440 'descriptive_flex_context_code', p_descriptive_flex_context_cod);
441 RETURN(FALSE);
442 END fetch_dff_ctx;
443 -- ---------------------------------------------------------------------------
444 FUNCTION fetch_dff_seg(p_dff_ctx IN dff_ctx_type,
445 p_application_column_name IN VARCHAR2,
446 x_dff_seg OUT nocopy dff_seg_type,
447 x_message OUT nocopy VARCHAR2)
448 RETURN BOOLEAN
449 IS
450 BEGIN
451 SELECT *
452 INTO x_dff_seg
453 FROM fnd_descr_flex_column_usages
454 WHERE application_id = p_dff_ctx.application_id
455 AND descriptive_flexfield_name = p_dff_ctx.descriptive_flexfield_name
456 AND descriptive_flex_context_code =p_dff_ctx.descriptive_flex_context_code
457 AND application_column_name = p_application_column_name;
458 RETURN(TRUE);
459 EXCEPTION
460 WHEN OTHERS THEN
461 x_message := msg_uts('FND_DESCR_FLEX_COLUMN_USAGES',
462 'application_id', p_dff_ctx.application_id,
463 'descriptive_flexfield_name', p_dff_ctx.descriptive_flexfield_name,
464 'descriptive_flex_context_code', p_dff_ctx.descriptive_flex_context_code,
465 'application_column_name', p_application_column_name);
466 RETURN(FALSE);
467 END fetch_dff_seg;
468
469 -- ***************************************************************************
470 -- * KFF fetch_kff_stg() functions.
471 -- ***************************************************************************
472 -- ---------------------------------------------------------------------------
473 FUNCTION fetch_kff_flx(p_application_id IN NUMBER,
474 p_id_flex_code IN VARCHAR2,
475 x_kff_flx OUT nocopy kff_flx_type,
476 x_message OUT nocopy VARCHAR2)
477 RETURN BOOLEAN
478 IS
479 BEGIN
480 SELECT *
481 INTO x_kff_flx
482 FROM fnd_id_flexs
483 WHERE application_id = p_application_id
484 AND id_flex_code = p_id_flex_code;
485 RETURN(TRUE);
486 EXCEPTION
487 WHEN OTHERS THEN
488 x_message := msg_uts('FND_ID_FLEXS',
489 'application_id', p_application_id,
490 'id_flex_code', p_id_flex_code);
491 RETURN(FALSE);
492 END fetch_kff_flx;
493 -- ---------------------------------------------------------------------------
494 FUNCTION fetch_kff_str(p_kff_flx IN kff_flx_type,
495 p_id_flex_num IN NUMBER,
496 x_kff_str OUT nocopy kff_str_type,
497 x_message OUT nocopy VARCHAR2)
498 RETURN BOOLEAN
499 IS
500 BEGIN
501 SELECT *
502 INTO x_kff_str
503 FROM fnd_id_flex_structures
504 WHERE application_id = p_kff_flx.application_id
505 AND id_flex_code = p_kff_flx.id_flex_code
506 AND id_flex_num = p_id_flex_num;
507 RETURN(TRUE);
508 EXCEPTION
509 WHEN OTHERS THEN
510 x_message := msg_uts('FND_ID_FLEX_STRUCTURES',
511 'application_id', p_kff_flx.application_id,
512 'id_flex_code', p_kff_flx.id_flex_code,
513 'id_flex_num', p_id_flex_num);
514 RETURN(FALSE);
515 END fetch_kff_str;
516 -- ---------------------------------------------------------------------------
517 FUNCTION fetch_kff_seg(p_kff_str IN kff_str_type,
518 p_application_column_name IN VARCHAR2,
519 x_kff_seg OUT nocopy kff_seg_type,
520 x_message OUT nocopy VARCHAR2)
521 RETURN BOOLEAN
522 IS
523 BEGIN
524 SELECT *
525 INTO x_kff_seg
526 FROM fnd_id_flex_segments
527 WHERE application_id = p_kff_str.application_id
528 AND id_flex_code = p_kff_str.id_flex_code
529 AND id_flex_num = p_kff_str.id_flex_num
530 AND application_column_name = p_application_column_name;
531 RETURN(TRUE);
532 EXCEPTION
533 WHEN OTHERS THEN
534 x_message := msg_uts('FND_ID_FLEX_SEGMENTS',
535 'application_id', p_kff_str.application_id,
536 'id_flex_code', p_kff_str.id_flex_code,
537 'id_flex_num', p_kff_str.id_flex_num,
538 'application_column_name', p_application_column_name);
539 RETURN(FALSE);
540 END fetch_kff_seg;
541 -- ---------------------------------------------------------------------------
542 FUNCTION fetch_kff_flq(p_kff_flx IN kff_flx_type,
543 p_segment_attribute_type IN VARCHAR2,
544 x_kff_flq OUT nocopy kff_flq_type,
545 x_message OUT nocopy VARCHAR2)
546 RETURN BOOLEAN
547 IS
548 BEGIN
549 SELECT *
550 INTO x_kff_flq
551 FROM fnd_segment_attribute_types
552 WHERE application_id = p_kff_flx.application_id
553 AND id_flex_code = p_kff_flx.id_flex_code
554 AND segment_attribute_type = p_segment_attribute_type;
555 RETURN(TRUE);
556 EXCEPTION
557 WHEN OTHERS THEN
558 x_message := msg_uts('FND_SEGMENT_ATTRIBUTE_TYPES',
559 'application_id', p_kff_flx.application_id,
560 'id_flex_code', p_kff_flx.id_flex_code,
561 'segment_attribute_type', p_segment_attribute_type);
562 RETURN(FALSE);
563 END fetch_kff_flq;
564 -- ---------------------------------------------------------------------------
565 FUNCTION fetch_kff_sgq(p_kff_flq IN kff_flq_type,
566 p_value_attribute_type IN VARCHAR2,
567 x_kff_sgq OUT nocopy kff_sgq_type,
568 x_message OUT nocopy VARCHAR2)
569 RETURN BOOLEAN
570 IS
571 BEGIN
572 SELECT *
573 INTO x_kff_sgq
574 FROM fnd_value_attribute_types
575 WHERE application_id = p_kff_flq.application_id
576 AND id_flex_code = p_kff_flq.id_flex_code
577 AND segment_attribute_type = p_kff_flq.segment_attribute_type
578 AND value_attribute_type = p_value_attribute_type;
579 RETURN(TRUE);
580 EXCEPTION
581 WHEN OTHERS THEN
582 x_message := msg_uts('FND_VALUE_ATTRIBUTE_TYPES',
583 'application_id', p_kff_flq.application_id,
584 'id_flex_code', p_kff_flq.id_flex_code,
585 'segment_attribute_type', p_kff_flq.segment_attribute_type,
586 'value_attribute_type', p_value_attribute_type);
587 RETURN(FALSE);
588 END fetch_kff_sgq;
589 -- ---------------------------------------------------------------------------
590 FUNCTION fetch_kff_qlv(p_kff_seg IN kff_seg_type,
591 p_kff_flq IN kff_flq_type,
592 x_kff_qlv OUT nocopy kff_qlv_type,
593 x_message OUT nocopy VARCHAR2)
594 RETURN BOOLEAN
595 IS
596 BEGIN
597 IF (p_kff_seg. application_id <> p_kff_flq.application_id OR
598 p_kff_seg.id_flex_code <> p_kff_flq.id_flex_code) THEN
599 x_message := 'KFF does not match in SEG and FLQ records.';
600 RETURN(FALSE);
601 END IF;
602
603 SELECT *
604 INTO x_kff_qlv
605 FROM fnd_segment_attribute_values
606 WHERE application_id = p_kff_seg.application_id
607 AND id_flex_code = p_kff_seg.id_flex_code
608 AND id_flex_num = p_kff_seg.id_flex_num
609 AND application_column_name = p_kff_seg.application_column_name
610 AND segment_attribute_type = p_kff_flq.segment_attribute_type;
611 RETURN(TRUE);
612 EXCEPTION
613 WHEN OTHERS THEN
614 x_message := msg_uts('FND_SEGMENT_ATTRIBUTE_VALUES',
615 'application_id', p_kff_seg.application_id,
616 'id_flex_code', p_kff_seg.id_flex_code,
617 'id_flex_num', p_kff_seg.id_flex_num,
618 'application_column_name', p_kff_seg.application_column_name,
619 'segment_attribute_type', p_kff_flq.segment_attribute_type);
620 RETURN(FALSE);
621 END fetch_kff_qlv;
622 -- ---------------------------------------------------------------------------
623 FUNCTION fetch_kff_sha(p_kff_str IN kff_str_type,
624 p_alias_name IN VARCHAR2,
625 x_kff_sha OUT nocopy kff_sha_type,
626 x_message OUT nocopy VARCHAR2)
627 RETURN BOOLEAN
628 IS
629 BEGIN
630 SELECT *
631 INTO x_kff_sha
632 FROM fnd_shorthand_flex_aliases
633 WHERE application_id = p_kff_str.application_id
634 AND id_flex_code = p_kff_str.id_flex_code
635 AND id_flex_num = p_kff_str.id_flex_num
636 AND alias_name = p_alias_name;
637 RETURN(TRUE);
638 EXCEPTION
639 WHEN OTHERS THEN
640 x_message := msg_uts('FND_SHORTHAND_FLEX_ALIASES',
641 'application_id', p_kff_str.application_id,
642 'id_flex_code', p_kff_str.id_flex_code,
643 'id_flex_num', p_kff_str.id_flex_num,
644 'alias_name', p_alias_name);
645 RETURN(FALSE);
646 END fetch_kff_sha;
647 -- ---------------------------------------------------------------------------
648 FUNCTION fetch_kff_cvr(p_kff_str IN kff_str_type,
649 p_flex_validation_rule_name IN VARCHAR2,
650 x_kff_cvr OUT nocopy kff_cvr_type,
651 x_message OUT nocopy VARCHAR2)
652 RETURN BOOLEAN
653 IS
654 BEGIN
655 SELECT *
656 INTO x_kff_cvr
657 FROM fnd_flex_validation_rules
658 WHERE application_id = p_kff_str.application_id
659 AND id_flex_code = p_kff_str.id_flex_code
660 AND id_flex_num = p_kff_str.id_flex_num
661 AND flex_validation_rule_name = p_flex_validation_rule_name;
662 RETURN(TRUE);
663 EXCEPTION
664 WHEN OTHERS THEN
665 x_message := msg_uts('FND_FLEX_VALIDATION_RULES',
666 'application_id', p_kff_str.application_id,
667 'id_flex_code', p_kff_str.id_flex_code,
668 'id_flex_num', p_kff_str.id_flex_num,
669 'flex_validation_rule_name', p_flex_validation_rule_name);
670 RETURN(FALSE);
671 END fetch_kff_cvr;
672 -- ---------------------------------------------------------------------------
673 FUNCTION fetch_kff_cvl(p_kff_cvr IN kff_cvr_type,
674 p_rule_line_id IN NUMBER,
675 x_kff_cvl OUT nocopy kff_cvl_type,
676 x_message OUT nocopy VARCHAR2)
677 RETURN BOOLEAN
678 IS
679 BEGIN
680 SELECT *
681 INTO x_kff_cvl
682 FROM fnd_flex_validation_rule_lines
683 WHERE application_id = p_kff_cvr.application_id
684 AND id_flex_code = p_kff_cvr.id_flex_code
685 AND id_flex_num = p_kff_cvr.id_flex_num
686 AND flex_validation_rule_name = p_kff_cvr.flex_validation_rule_name
687 AND rule_line_id = p_rule_line_id;
688 RETURN(TRUE);
689 EXCEPTION
690 WHEN OTHERS THEN
691 x_message := msg_uts('FND_FLEX_VALIDATION_RULE_LINES',
692 'application_id', p_kff_cvr.application_id,
693 'id_flex_code', p_kff_cvr.id_flex_code,
694 'id_flex_num', p_kff_cvr.id_flex_num,
695 'flex_validation_rule_name', p_kff_cvr.flex_validation_rule_name,
696 'rule_line_id', p_rule_line_id);
697 RETURN(FALSE);
698 END fetch_kff_cvl;
699 -- ---------------------------------------------------------------------------
700 FUNCTION fetch_kff_cvi(p_kff_cvr IN kff_cvr_type,
701 p_rule_line_id IN NUMBER,
702 x_kff_cvi OUT nocopy kff_cvi_type,
703 x_message OUT nocopy VARCHAR2)
704 RETURN BOOLEAN
705 IS
706 BEGIN
707 SELECT *
708 INTO x_kff_cvi
709 FROM fnd_flex_include_rule_lines
710 WHERE application_id = p_kff_cvr.application_id
711 AND id_flex_code = p_kff_cvr.id_flex_code
712 AND id_flex_num = p_kff_cvr.id_flex_num
713 AND flex_validation_rule_name = p_kff_cvr.flex_validation_rule_name
714 AND rule_line_id = p_rule_line_id;
715 RETURN(TRUE);
716 EXCEPTION
717 WHEN OTHERS THEN
718 x_message := msg_uts('FND_FLEX_INCLUDE_RULE_LINES',
719 'application_id', p_kff_cvr.application_id,
720 'id_flex_code', p_kff_cvr.id_flex_code,
721 'id_flex_num', p_kff_cvr.id_flex_num,
722 'flex_validation_rule_name', p_kff_cvr.flex_validation_rule_name,
723 'rule_line_id', p_rule_line_id);
724 RETURN(FALSE);
725 END fetch_kff_cvi;
726 -- ---------------------------------------------------------------------------
727 FUNCTION fetch_kff_cve(p_kff_cvr IN kff_cvr_type,
728 p_rule_line_id IN NUMBER,
729 x_kff_cve OUT nocopy kff_cve_type,
730 x_message OUT nocopy VARCHAR2)
731 RETURN BOOLEAN
732 IS
733 BEGIN
734 SELECT *
735 INTO x_kff_cve
736 FROM fnd_flex_exclude_rule_lines
737 WHERE application_id = p_kff_cvr.application_id
738 AND id_flex_code = p_kff_cvr.id_flex_code
739 AND id_flex_num = p_kff_cvr.id_flex_num
740 AND flex_validation_rule_name = p_kff_cvr.flex_validation_rule_name
741 AND rule_line_id = p_rule_line_id;
742 RETURN(TRUE);
743 EXCEPTION
744 WHEN OTHERS THEN
745 x_message := msg_uts('FND_FLEX_EXCLUDE_RULE_LINES',
746 'application_id', p_kff_cvr.application_id,
747 'id_flex_code', p_kff_cvr.id_flex_code,
748 'id_flex_num', p_kff_cvr.id_flex_num,
749 'flex_validation_rule_name', p_kff_cvr.flex_validation_rule_name,
750 'rule_line_id', p_rule_line_id);
751 RETURN(FALSE);
752 END fetch_kff_cve;
753 -- ---------------------------------------------------------------------------
754 FUNCTION fetch_kff_fwp(p_kff_str IN kff_str_type,
755 p_wf_item_type IN VARCHAR2,
756 x_kff_fwp OUT nocopy kff_fwp_type,
757 x_message OUT nocopy VARCHAR2)
758 RETURN BOOLEAN
759 IS
760 BEGIN
761 SELECT *
762 INTO x_kff_fwp
763 FROM fnd_flex_workflow_processes
764 WHERE application_id = p_kff_str.application_id
765 AND id_flex_code = p_kff_str.id_flex_code
766 AND id_flex_num = p_kff_str.id_flex_num
767 AND wf_item_type = p_wf_item_type;
768 RETURN(TRUE);
769 EXCEPTION
770 WHEN OTHERS THEN
771 x_message := msg_uts('FND_FLEX_WORKFLOW_PROCESSES',
772 'application_id', p_kff_str.application_id,
773 'id_flex_code', p_kff_str.id_flex_code,
774 'id_flex_num', p_kff_str.id_flex_num,
775 'wf_item_type', p_wf_item_type);
776 RETURN(FALSE);
777 END fetch_kff_fwp;
778
779
780 -- ***************************************************************************
781 -- * Common get_something() RETURN VARCHAR2; functions.
782 -- ***************************************************************************
783 -- ===========================================================================
784 FUNCTION get_db RETURN VARCHAR2
785 IS
786 l_return VARCHAR2(2000);
787 BEGIN
788 SELECT name
789 INTO l_return
790 FROM v$database
791 WHERE ROWNUM = 1;
792 RETURN(line_return(l_return));
793 EXCEPTION
794 WHEN OTHERS THEN
795 RETURN(line_return('Unknown DB.'));
796 END get_db;
797
798 -- ===========================================================================
799 FUNCTION get_rel RETURN VARCHAR2
800 IS
801 l_return VARCHAR2(2000);
802 BEGIN
803 SELECT release_name
804 INTO l_return
805 FROM fnd_product_groups
806 WHERE rownum = 1;
807 RETURN(line_return(l_return));
808 EXCEPTION
809 WHEN OTHERS THEN
810 RETURN(line_return('00'));
811 END get_rel;
812
813 -- ===========================================================================
814 FUNCTION get_who(p_table_name IN VARCHAR2,
815 p_rowid IN ROWID)
816 RETURN VARCHAR2
817 IS
818 l_sql VARCHAR2(2000);
819 l_creation_date DATE;
820 l_created_by NUMBER;
821 l_last_update_date DATE;
822 l_last_updated_by NUMBER;
823 l_last_update_login NUMBER;
824 BEGIN
825 l_sql := ('SELECT creation_date, created_by,' ||
826 ' last_update_date, last_updated_by, last_update_login' ||
827 ' FROM ' || p_table_name ||
828 ' WHERE ROWID = :B1');
829
830 --
831 -- Oracle 8.1
832 --
833 EXECUTE IMMEDIATE l_sql
834 INTO l_creation_date, l_created_by,
835 l_last_update_date, l_last_updated_by, l_last_update_login
836 USING p_rowid;
837
838
839 RETURN(line_return(get_who(l_creation_date, l_created_by,
840 l_last_update_date, l_last_updated_by,
841 l_last_update_login)));
842 EXCEPTION
843 WHEN no_data_found THEN
844 RETURN(line_return(p_table_name || p_rowid ||
845 '/get_who: no data found.'));
846 WHEN OTHERS THEN
847 RETURN(line_return(p_table_name || p_rowid ||
848 '/get_who SQLCODE:' || To_char(SQLCODE)));
849 END get_who;
850
851 -- ===========================================================================
852 FUNCTION get_who(p_creation_date IN DATE,
853 p_created_by IN NUMBER,
854 p_last_update_date IN DATE,
855 p_last_updated_by IN NUMBER,
856 p_last_update_login IN NUMBER)
857 RETURN VARCHAR2
858 IS
859 BEGIN
860 RETURN(line_return('CD:' || To_char(p_creation_date, 'YYYY/MM/DD') ||
861 ' CB:' || To_char(p_created_by) ||
862 ' LUD:' || To_char(p_last_update_date, 'YYYY/MM/DD') ||
863 ' LUB:' || To_char(p_last_updated_by) ||
864 ' LUL:' || To_char(p_last_update_login)));
865 EXCEPTION
866 WHEN OTHERS THEN
867 RETURN(line_return('/get_who SQLCODE:' || To_char(SQLCODE)));
868 END get_who;
869
870 -- ===========================================================================
871 FUNCTION get_app(p_application_id IN NUMBER)
872 RETURN VARCHAR2
873 IS
874 l_return VARCHAR2(2000);
875 BEGIN
876 SELECT To_char(application_id) || '/' ||
877 application_short_name || '/' ||
878 application_name
879 INTO l_return
880 FROM fnd_application_vl
881 WHERE application_id = p_application_id;
882 RETURN(line_return(l_return));
883 EXCEPTION
884 WHEN no_data_found THEN
885 RETURN(line_return(To_char(p_application_id) ||
886 '/get_app: no data found.'));
887 WHEN OTHERS THEN
888 RETURN(line_return(To_char(p_application_id) ||
889 '/get_app SQLCODE:' || To_char(SQLCODE)));
890 END get_app;
891
892 -- ===========================================================================
893 FUNCTION get_tbl(p_application_id IN NUMBER,
894 p_table_name IN VARCHAR2)
895 RETURN VARCHAR2
896 IS
897 l_return VARCHAR2(2000);
898 BEGIN
899 BEGIN
900 SELECT ft.table_name || '/' ||
901 To_char(table_id)
902 INTO l_return
903 FROM fnd_tables ft
904 WHERE ft.application_id = p_application_id
905 AND ft.table_name = p_table_name;
906 EXCEPTION
907 WHEN no_data_found THEN
908 RETURN(line_return(p_table_name ||
909 '/get_tbl(FND_TABLES): no data found.'));
910 END;
911 BEGIN
912 SELECT us.table_owner || '/' || l_return
913 INTO l_return
914 FROM user_synonyms us
915 WHERE us.synonym_name = p_table_name;
916 EXCEPTION
917 WHEN no_data_found THEN
918 RETURN(line_return(l_return ||
919 '/get_tbl(USER_SYNONYMS): no data found.'));
920 END;
921 RETURN(line_return(l_return));
922 EXCEPTION
923 WHEN no_data_found THEN
924 RETURN(line_return(p_table_name ||
925 '/get_tbl: no data found.'));
926 WHEN OTHERS THEN
927 RETURN(line_return(p_table_name ||
928 '/get_tbl SQLCODE:' || To_char(SQLCODE)));
929 END get_tbl;
930
931 -- ===========================================================================
932 FUNCTION get_col(p_application_id IN NUMBER,
933 p_table_name IN VARCHAR2,
934 p_column_name IN VARCHAR2)
935 RETURN VARCHAR2
936 IS
937 l_return VARCHAR2(2000);
938 BEGIN
939 SELECT column_name || '/' ||
940 To_char(column_id) || '/' ||
941 column_type || '/' ||
942 To_char(width) || '/' ||
943 flexfield_usage_code || '/' ||
944 Nvl(To_char(flexfield_application_id), '<NULL>') || '/' ||
945 Nvl(flexfield_name, '<NULL>')
946 INTO l_return
947 FROM fnd_columns
948 WHERE ((application_id, table_id) =
949 (SELECT application_id, table_id
950 FROM fnd_tables
951 WHERE application_id = p_application_id
952 AND table_name = p_table_name))
953 AND column_name = p_column_name;
954 RETURN(line_return(l_return));
955 EXCEPTION
956 WHEN no_data_found THEN
957 RETURN(line_return(p_column_name ||
958 '/get_col: no data found.'));
959 WHEN OTHERS THEN
960 RETURN(line_return(p_column_name ||
961 '/get_col SQLCODE:' || To_char(SQLCODE)));
962 END get_col;
963
964 -- ===========================================================================
965 FUNCTION get_lng(p_language_code IN VARCHAR2)
966 RETURN VARCHAR2
967 IS
968 l_return VARCHAR2(2000);
969 BEGIN
970 SELECT language_code || '/' ||
971 installed_flag || '/' ||
972 nls_language || '/' ||
973 nls_territory
974 INTO l_return
975 FROM fnd_languages
976 WHERE language_code = p_language_code;
977 RETURN(line_return(l_return));
978 EXCEPTION
979 WHEN no_data_found THEN
980 RETURN(line_return(p_language_code ||
981 '/get_lng: no data found.'));
982 WHEN OTHERS THEN
983 RETURN(line_return(p_language_code ||
984 '/get_lng SQLCODE:' || To_char(SQLCODE)));
985 END get_lng;
986
987 -- ===========================================================================
988 FUNCTION get_rsp(p_application_id IN NUMBER,
989 p_responsibility_id IN NUMBER)
990 RETURN VARCHAR2
991 IS
992 l_return VARCHAR2(2000);
993 BEGIN
994 SELECT To_char(responsibility_id) || '/' ||
995 responsibility_key || '/' ||
996 responsibility_name
997 INTO l_return
998 FROM fnd_responsibility_vl
999 WHERE application_id = p_application_id
1000 AND responsibility_id = p_responsibility_id;
1001 RETURN(line_return(l_return));
1002 EXCEPTION
1003 WHEN no_data_found THEN
1004 RETURN(line_return(To_char(p_responsibility_id) ||
1005 '/get_rsp: no data found.'));
1006 WHEN OTHERS THEN
1007 RETURN(line_return(To_char(p_responsibility_id) ||
1008 '/get_rsp SQLCODE:' || To_char(SQLCODE)));
1009 END get_rsp;
1010
1011 -- ***************************************************************************
1012 -- * FB get_fb_something() RETURN VARCHAR2; functions.
1013 -- ***************************************************************************
1014 -- ===========================================================================
1015 FUNCTION get_fb_func(p_application_id IN NUMBER,
1016 p_function_code IN VARCHAR2)
1017 RETURN VARCHAR2
1018 IS
1019 l_return VARCHAR2(2000);
1020 BEGIN
1021 SELECT function_code || '/' ||
1022 function_name || '/' ||
1023 description
1024 INTO l_return
1025 FROM fnd_flexbuilder_functions
1026 WHERE application_id = p_application_id
1027 AND function_code = p_function_code;
1028 RETURN(line_return(l_return));
1029 EXCEPTION
1030 WHEN no_data_found THEN
1031 RETURN(line_return(p_function_code ||
1032 '/get_fb_func: no data found.'));
1033 WHEN OTHERS THEN
1034 RETURN(line_return(p_function_code ||
1035 '/get_fb_func SQLCODE:' || To_char(SQLCODE)));
1036 END get_fb_func;
1037
1038 -- ===========================================================================
1039 FUNCTION get_fb_kapp(p_application_id IN NUMBER,
1040 p_function_code IN VARCHAR2)
1041 RETURN VARCHAR2
1042 IS
1043 l_kff_app_id NUMBER;
1044 BEGIN
1045 SELECT flexfield_application_id
1046 INTO l_kff_app_id
1047 FROM fnd_flexbuilder_functions
1048 WHERE application_id = p_application_id
1049 AND function_code = p_function_code;
1050
1051 RETURN(get_app(l_kff_app_id));
1052 EXCEPTION
1053 WHEN no_data_found THEN
1054 RETURN(line_return(p_function_code ||
1055 '/get_fb_kapp: no data found.'));
1056 WHEN OTHERS THEN
1057 RETURN(line_return(p_function_code ||
1058 '/get_fb_kapp SQLCODE:' || To_char(SQLCODE)));
1059 END get_fb_kapp;
1060
1061 -- ===========================================================================
1062 FUNCTION get_fb_kflx(p_application_id IN NUMBER,
1063 p_function_code IN VARCHAR2)
1064 RETURN VARCHAR2
1065 IS
1066 l_kff_app_id NUMBER;
1067 l_kff_code VARCHAR2(100);
1068 BEGIN
1069 SELECT flexfield_application_id, id_flex_code
1070 INTO l_kff_app_id, l_kff_code
1071 FROM fnd_flexbuilder_functions
1072 WHERE application_id = p_application_id
1073 AND function_code = p_function_code;
1074
1075 RETURN(get_kff_flx(l_kff_app_id, l_kff_code));
1076 EXCEPTION
1077 WHEN no_data_found THEN
1078 RETURN(line_return(p_function_code ||
1079 '/get_fb_kflx: no data found.'));
1080 WHEN OTHERS THEN
1081 RETURN(line_return(p_function_code ||
1082 '/get_fb_kflx SQLCODE:' || To_char(SQLCODE)));
1083 END get_fb_kflx;
1084
1085 -- ===========================================================================
1086 FUNCTION get_fb_kstr(p_application_id IN NUMBER,
1087 p_function_code IN VARCHAR2,
1088 p_id_flex_num IN NUMBER)
1089 RETURN VARCHAR2
1090 IS
1091 l_kff_app_id NUMBER;
1092 l_kff_code VARCHAR2(100);
1093 BEGIN
1094 SELECT flexfield_application_id, id_flex_code
1095 INTO l_kff_app_id, l_kff_code
1096 FROM fnd_flexbuilder_functions
1097 WHERE application_id = p_application_id
1098 AND function_code = p_function_code;
1099
1100 RETURN(get_kff_str(l_kff_app_id, l_kff_code, p_id_flex_num));
1101 EXCEPTION
1102 WHEN no_data_found THEN
1103 RETURN(line_return(p_function_code || '/' || p_id_flex_num ||
1104 '/get_fb_kstr: no data found.'));
1105 WHEN OTHERS THEN
1106 RETURN(line_return(p_function_code || '/' || p_id_flex_num ||
1107 '/get_fb_kstr SQLCODE:' || To_char(SQLCODE)));
1108 END get_fb_kstr;
1109
1110 -- ***************************************************************************
1111 -- * VST get_vst_something() RETURN VARCHAR2; functions.
1112 -- ***************************************************************************
1113 -- ===========================================================================
1114 FUNCTION get_vst_set(p_flex_value_set_id IN NUMBER)
1115 RETURN VARCHAR2
1116 IS
1117 l_return VARCHAR2(2000);
1118 BEGIN
1119 SELECT To_char(flex_value_set_id) || '/' ||
1120 flex_value_set_name || '/' ||
1121 validation_type || '/' ||
1122 format_type || '/' ||
1123 To_char(maximum_size) || '/' ||
1124 Nvl(description, '<NULL>')
1125 INTO l_return
1126 FROM fnd_flex_value_sets
1127 WHERE flex_value_set_id = p_flex_value_set_id;
1128 RETURN(line_return(l_return));
1129 EXCEPTION
1130 WHEN no_data_found THEN
1131 RETURN(line_return(To_char(p_flex_value_set_id) ||
1132 '/get_vst_set: no data found.'));
1133 WHEN OTHERS THEN
1134 RETURN(line_return(To_char(p_flex_value_set_id) ||
1135 '/get_vst_set SQLCODE:' || To_char(SQLCODE)));
1136 END get_vst_set;
1137
1138 -- ===========================================================================
1139 FUNCTION get_vst_tbl(p_flex_value_set_id IN NUMBER)
1140 RETURN VARCHAR2
1141 IS
1142 l_return VARCHAR2(2000);
1143 BEGIN
1144 SELECT application_table_name || '/' ||
1145 value_column_name || '/' ||
1146 value_column_type || '/' ||
1147 value_column_size || '/' ||
1148 Nvl(id_column_name,'<NULL>') || '/' ||
1149 Nvl(id_column_type,'<NULL>') || '/' ||
1150 Nvl(To_char(id_column_size),'<NULL>')
1151 INTO l_return
1152 FROM fnd_flex_validation_tables
1153 WHERE flex_value_set_id = p_flex_value_set_id;
1154 RETURN(line_return(l_return));
1155 EXCEPTION
1156 WHEN no_data_found THEN
1157 RETURN(line_return(To_char(p_flex_value_set_id) ||
1158 '/get_vst_tbl: no data found.'));
1159 WHEN OTHERS THEN
1160 RETURN(line_return(To_char(p_flex_value_set_id) ||
1161 '/get_vst_tbl SQLCODE:' || To_char(SQLCODE)));
1162 END get_vst_tbl;
1163
1164 -- ===========================================================================
1165 FUNCTION get_vst_evt(p_flex_value_set_id IN NUMBER,
1166 p_event_code IN VARCHAR2)
1167 RETURN VARCHAR2
1168 IS
1169 l_return VARCHAR2(2000);
1170 l_user_exit VARCHAR2(32000);
1171 BEGIN
1172 SELECT fve.event_code || '/' ||
1173 fl.meaning || '/',
1174 fve.user_exit
1175 INTO l_return, l_user_exit
1176 FROM fnd_flex_validation_events fve, fnd_lookups fl
1177 WHERE fl.lookup_type = 'FLEX_VALIDATION_EVENTS'
1178 AND fl.lookup_code = fve.event_code
1179 AND fve.flex_value_set_id = p_flex_value_set_id
1180 AND fve.event_code = p_event_code;
1181 l_return := l_return || Substr(l_user_exit,
1182 1, g_line_size - Length(l_return));
1183 l_return := REPLACE(l_return, g_newline, '\n');
1184 RETURN(line_return(l_return));
1185 EXCEPTION
1186 WHEN no_data_found THEN
1187 RETURN(line_return(p_event_code ||
1188 '/get_vst_evt: no data found.'));
1189 WHEN OTHERS THEN
1190 RETURN(line_return(p_event_code ||
1191 '/get_vst_evt SQLCODE:' || To_char(SQLCODE)));
1192 END get_vst_evt;
1193
1194 -- ===========================================================================
1195 FUNCTION get_vst_scr(p_flex_value_set_id IN NUMBER,
1196 p_flex_value_rule_id IN NUMBER)
1197 RETURN VARCHAR2
1198 IS
1199 l_return VARCHAR2(2000);
1200 BEGIN
1201 SELECT flex_value_rule_id || '/' ||
1202 flex_value_rule_name || '/' ||
1203 Nvl(parent_flex_value_low,'<NULL>') || '/' ||
1204 error_message
1205 INTO l_return
1206 FROM fnd_flex_value_rules_vl
1207 WHERE flex_value_set_id = p_flex_value_set_id
1208 AND flex_value_rule_id = p_flex_value_rule_id;
1209 RETURN(line_return(l_return));
1210 EXCEPTION
1211 WHEN no_data_found THEN
1212 RETURN(line_return(To_char(p_flex_value_rule_id) ||
1213 '/get_vst_scr: no data found.'));
1214 WHEN OTHERS THEN
1215 RETURN(line_return(To_char(p_flex_value_rule_id) ||
1216 '/get_vst_scr SQLCODE:' || To_char(SQLCODE)));
1217 END get_vst_scr;
1218
1219 -- ===========================================================================
1220 FUNCTION get_vst_scl(p_flex_value_set_id IN NUMBER,
1221 p_flex_value_rule_id IN NUMBER,
1222 p_include_exclude_indicator IN VARCHAR2,
1223 p_flex_value_low IN VARCHAR2,
1224 p_flex_value_high IN VARCHAR2)
1225 RETURN VARCHAR2
1226 IS
1227 l_return VARCHAR2(2000);
1228 BEGIN
1229 SELECT include_exclude_indicator || '/' ||
1230 Nvl(parent_flex_value_low,'<NULL>') || '/' ||
1231 Nvl(flex_value_low, '<NULL>') || '/' ||
1232 Nvl(flex_value_high, '<NULL>')
1233 INTO l_return
1234 FROM fnd_flex_value_rule_lines
1235 WHERE flex_value_set_id = p_flex_value_set_id
1236 AND flex_value_rule_id = p_flex_value_rule_id
1237 AND include_exclude_indicator = p_include_exclude_indicator
1238 AND Nvl(flex_value_low, '$FLEX$.NULL') =
1239 Nvl(p_flex_value_low, '$FLEX$.NULL')
1240 AND Nvl(flex_value_high, '$FLEX$.NULL') =
1241 Nvl(p_flex_value_high, '$FLEX$.NULL');
1242 RETURN(line_return(l_return));
1243 EXCEPTION
1244 WHEN no_data_found THEN
1245 RETURN(line_return(p_include_exclude_indicator || '/' ||
1246 p_flex_value_low || '/' ||
1247 p_flex_value_high ||
1248 '/get_vst_scl: no data found.'));
1249 WHEN OTHERS THEN
1250 RETURN(line_return(p_include_exclude_indicator || '/' ||
1251 p_flex_value_low || '/' ||
1252 p_flex_value_high ||
1253 '/get_vst_scl SQLCODE:' || To_char(SQLCODE)));
1254 END get_vst_scl;
1255
1256 -- ===========================================================================
1257 FUNCTION get_vst_scu(p_flex_value_set_id IN NUMBER,
1258 p_flex_value_rule_id IN NUMBER,
1259 p_application_id IN NUMBER,
1260 p_responsibility_id IN NUMBER)
1261 RETURN VARCHAR2
1262 IS
1263 l_return VARCHAR2(2000);
1264 BEGIN
1265 SELECT To_char(flex_value_rule_id) || '/' ||
1266 To_char(application_id) || '/' ||
1267 To_char(responsibility_id)
1268 INTO l_return
1269 FROM fnd_flex_value_rule_usages
1270 WHERE flex_value_set_id = p_flex_value_set_id
1271 AND flex_value_rule_id = p_flex_value_rule_id
1272 AND application_id = p_application_id
1273 AND responsibility_id = p_responsibility_id;
1274 RETURN(line_return(l_return));
1275 EXCEPTION
1276 WHEN no_data_found THEN
1277 RETURN(line_return(To_char(p_flex_value_rule_id) || '/' ||
1278 To_char(p_application_id) || '/' ||
1279 To_char(p_responsibility_id) ||
1280 '/get_vst_scu: no data found.'));
1281 WHEN OTHERS THEN
1282 RETURN(line_return(To_char(p_flex_value_rule_id) || '/' ||
1283 To_char(p_application_id) || '/' ||
1284 To_char(p_responsibility_id) ||
1285 '/get_vst_scu SQLCODE:' || To_char(SQLCODE)));
1286 END get_vst_scu;
1287
1288 -- ===========================================================================
1289 FUNCTION get_vst_val(p_flex_value_set_id IN NUMBER,
1290 p_flex_value_id IN NUMBER)
1291 RETURN VARCHAR2
1292 IS
1293 l_return VARCHAR2(2000);
1294 BEGIN
1295 SELECT To_char(flex_value_id) || '/' ||
1296 Nvl(parent_flex_value_low, '<NULL>') || '/' ||
1297 flex_value || '/' ||
1298 enabled_flag || '/' ||
1299 flex_value_meaning || '/' ||
1300 Nvl(description, '<NULL>')
1301 INTO l_return
1302 FROM fnd_flex_values_vl
1303 WHERE flex_value_set_id = p_flex_value_set_id
1304 AND flex_value_id = p_flex_value_id;
1305 RETURN(line_return(l_return));
1306 EXCEPTION
1307 WHEN no_data_found THEN
1308 RETURN(line_return(To_char(p_flex_value_id) ||
1309 '/get_vst_val: no data found.'));
1310 WHEN OTHERS THEN
1311 RETURN(line_return(To_char(p_flex_value_id) ||
1312 '/get_vst_val SQLCODE:' || To_char(SQLCODE)));
1313 END get_vst_val;
1314
1315 -- ===========================================================================
1316 FUNCTION get_vst_rlg(p_flex_value_set_id IN NUMBER,
1317 p_hierarchy_id IN NUMBER)
1318 RETURN VARCHAR2
1319 IS
1320 l_return VARCHAR2(2000);
1321 BEGIN
1322 SELECT To_char(hierarchy_id) || '/' ||
1323 hierarchy_name || '/' ||
1324 Nvl(description,'<NULL>')
1325 INTO l_return
1326 FROM fnd_flex_hierarchies_vl
1327 WHERE flex_value_set_id = p_flex_value_set_id
1328 AND hierarchy_id = p_hierarchy_id;
1329 RETURN(line_return(l_return));
1330 EXCEPTION
1331 WHEN no_data_found THEN
1332 RETURN(line_return(To_char(p_hierarchy_id) ||
1333 '/get_vst_rlg: no data found.'));
1334 WHEN OTHERS THEN
1335 RETURN(line_return(To_char(p_hierarchy_id) ||
1336 '/get_vst_rlg SQLCODE:' || To_char(SQLCODE)));
1337 END get_vst_rlg;
1338
1339 -- ===========================================================================
1340 FUNCTION get_vst_fvn(p_flex_value_set_id IN NUMBER,
1341 p_parent_flex_value IN VARCHAR2,
1342 p_range_attribute IN VARCHAR2,
1343 p_child_flex_value_low IN VARCHAR2,
1344 p_child_flex_value_high IN VARCHAR2)
1345 RETURN VARCHAR2
1346 IS
1347 l_return VARCHAR2(2000);
1348 BEGIN
1349 SELECT parent_flex_value || '/' ||
1350 range_attribute || '/' ||
1351 child_flex_value_low || '/' ||
1352 child_flex_value_high
1353 INTO l_return
1354 FROM fnd_flex_value_norm_hierarchy
1355 WHERE flex_value_set_id = p_flex_value_set_id
1356 AND parent_flex_value = p_parent_flex_value
1357 AND range_attribute = p_range_attribute
1358 AND child_flex_value_low = p_child_flex_value_low
1359 AND child_flex_value_high = p_child_flex_value_high;
1360 RETURN(line_return(l_return));
1361 EXCEPTION
1362 WHEN no_data_found THEN
1363 RETURN(line_return(p_parent_flex_value || '/' ||
1364 p_range_attribute || '/' ||
1365 p_child_flex_value_low || '/' ||
1366 p_child_flex_value_high ||
1367 '/get_vst_fvn: no data found.'));
1368 WHEN OTHERS THEN
1369 RETURN(line_return(p_parent_flex_value || '/' ||
1370 p_range_attribute || '/' ||
1371 p_child_flex_value_low || '/' ||
1372 p_child_flex_value_high ||
1373 '/get_vst_fvn SQLCODE:' || To_char(SQLCODE)));
1374 END get_vst_fvn;
1375
1376 -- ===========================================================================
1377 FUNCTION get_vst_fvh(p_flex_value_set_id IN NUMBER,
1378 p_parent_flex_value IN VARCHAR2,
1379 p_child_flex_value_low IN VARCHAR2,
1380 p_child_flex_value_high IN VARCHAR2)
1381 RETURN VARCHAR2
1382 IS
1383 l_return VARCHAR2(2000);
1384 BEGIN
1385 SELECT parent_flex_value || '/' ||
1386 child_flex_value_low || '/' ||
1387 child_flex_value_high
1388 INTO l_return
1389 FROM fnd_flex_value_hierarchies
1390 WHERE flex_value_set_id = p_flex_value_set_id
1391 AND parent_flex_value = p_parent_flex_value
1392 AND child_flex_value_low = p_child_flex_value_low
1393 AND child_flex_value_high = p_child_flex_value_high;
1394 RETURN(line_return(l_return));
1395 EXCEPTION
1396 WHEN no_data_found THEN
1397 RETURN(line_return(p_parent_flex_value || '/' ||
1398 p_child_flex_value_low || '/' ||
1399 p_child_flex_value_high ||
1400 '/get_vst_fvh: no data found.'));
1401 WHEN OTHERS THEN
1402 RETURN(line_return(p_parent_flex_value || '/' ||
1403 p_child_flex_value_low || '/' ||
1404 p_child_flex_value_high ||
1405 '/get_vst_fvh SQLCODE:' || To_char(SQLCODE)));
1406 END get_vst_fvh;
1407
1408 -- ===========================================================================
1409 FUNCTION get_vst_fix(p_rule IN VARCHAR2,
1410 p_pk1 IN VARCHAR2 DEFAULT NULL,
1411 p_pk2 IN VARCHAR2 DEFAULT NULL,
1412 p_pk3 IN VARCHAR2 DEFAULT NULL,
1413 p_pk4 IN VARCHAR2 DEFAULT NULL,
1414 p_pk5 IN VARCHAR2 DEFAULT NULL,
1415 p_pk6 IN VARCHAR2 DEFAULT NULL)
1416 RETURN VARCHAR2
1417 IS
1418 l_beg VARCHAR2(100);
1419 l_mid VARCHAR2(2000) := NULL;
1420 l_end VARCHAR2(100);
1421 l_cn VARCHAR2(100) := ',' || g_newline;
1422 BEGIN
1423 l_beg := (g_newline ||
1424 'variable msg VARCHAR2(2000);' || g_newline ||
1425 'BEGIN' || g_newline ||
1426 ' fnd_flex_diagnose.');
1427 IF (p_rule IN ('A.01', 'A.02', 'A.03', 'A.04', 'A.05', 'B.01', 'B.02')) THEN
1428 l_mid :=
1429 'fix_vst_set' || g_newline ||
1430 ' (p_rule => ''' || p_rule || '''' || l_cn ||
1431 ' p_flex_value_set_id => ' || p_pk1 || l_cn;
1432 END IF;
1433 IF (p_rule IN ('C.01', 'C.02')) THEN
1434 l_mid :=
1435 'fix_vst_evt' || g_newline ||
1436 ' (p_rule => ''' || p_rule || '''' || l_cn ||
1437 ' p_flex_value_set_id => ' || p_pk1 || l_cn ||
1438 ' p_event_code => ''' || p_pk2 || '''' || l_cn;
1439 END IF;
1440 IF (p_rule IN ('D.03')) THEN
1441 l_mid :=
1442 'fix_vst_scr' || g_newline ||
1443 ' (p_rule => ''' || p_rule || '''' || l_cn ||
1444 ' p_flex_value_set_id => ' || p_pk1 || l_cn ||
1445 ' p_flex_value_rule_id => ' || p_pk2 || l_cn;
1446 END IF;
1447 IF (p_rule IN ('E.01', 'E.02')) THEN
1448 l_mid :=
1449 'fix_vst_scl' || g_newline ||
1450 ' (p_rule => ''' || p_rule || '''' || l_cn ||
1451 ' p_flex_value_set_id => ' || p_pk1 || l_cn ||
1452 ' p_flex_value_rule_id => ' || p_pk2 || l_cn ||
1453 ' p_include_exclude_indicator => ''' || p_pk3 || '''' || l_cn ||
1454 ' p_flex_value_low => ''' || p_pk4 || '''' || l_cn ||
1455 ' p_flex_value_high => ''' || p_pk5 || '''' || l_cn;
1456 END IF;
1457 IF (p_rule IN ('F.01', 'F.02', 'F.03')) THEN
1458 l_mid :=
1459 'fix_vst_scu' || g_newline ||
1460 ' (p_rule => ''' || p_rule || '''' || l_cn ||
1461 ' p_flex_value_set_id => ' || p_pk1 || l_cn ||
1462 ' p_flex_value_rule_id => ' || p_pk2 || l_cn ||
1463 ' p_application_id => ' || p_pk3 || l_cn ||
1464 ' p_responsibility_id => ' || p_pk4 || l_cn;
1465 END IF;
1466 IF (p_rule IN ('G.03', 'G.04')) THEN
1467 l_mid :=
1468 'fix_vst_val' || g_newline ||
1469 ' (p_rule => ''' || p_rule || '''' || l_cn ||
1470 ' p_flex_value_set_id => ' || p_pk1 || l_cn ||
1471 ' p_flex_value_id => ' || p_pk2 || l_cn;
1472 END IF;
1473 IF (p_rule IN ('H.03')) THEN
1474 l_mid :=
1475 'fix_vst_rlg' || g_newline ||
1476 ' (p_rule => ''' || p_rule || '''' || l_cn ||
1477 ' p_flex_value_set_id => ' || p_pk1 || l_cn ||
1478 ' p_hierarchy_id => ' || p_pk2 || l_cn;
1479 END IF;
1480 IF (p_rule IN ('I.01')) THEN
1481 l_mid :=
1482 'fix_vst_fvn' || g_newline ||
1483 ' (p_rule => ''' || p_rule || '''' || l_cn ||
1484 ' p_flex_value_set_id => ' || p_pk1 || l_cn ||
1485 ' p_parent_flex_value => ''' || p_pk2 || '''' || l_cn ||
1486 ' p_range_attribute => ''' || p_pk3 || '''' || l_cn ||
1487 ' p_child_flex_value_low => ''' || p_pk4 || '''' || l_cn ||
1488 ' p_child_flex_value_high => ''' || p_pk5 || '''' || l_cn;
1489 END IF;
1490 IF (p_rule IN ('J.01')) THEN
1491 l_mid :=
1492 'fix_vst_fvh' || g_newline ||
1493 ' (p_rule => ''' || p_rule || '''' || l_cn ||
1494 ' p_flex_value_set_id => ' || p_pk1 || l_cn ||
1495 ' p_parent_flex_value => ''' || p_pk2 || '''' || l_cn ||
1496 ' p_child_flex_value_low => ''' || p_pk3 || '''' || l_cn ||
1497 ' p_child_flex_value_high => ''' || p_pk4 || '''' || l_cn;
1498 END IF;
1499 l_end := (' x_message => :msg);' || g_newline ||
1500 'END;' || g_newline ||
1501 '/' || g_newline ||
1502 'print msg;' || g_newline || g_newline);
1503
1504
1505 IF (l_mid IS NOT NULL) THEN
1506 RETURN(l_beg || l_mid || l_end);
1507 ELSE
1508 RETURN('VST fix is not available for rule : ' || p_rule);
1509 END IF;
1510 EXCEPTION
1511 WHEN OTHERS THEN
1512 RETURN('get_vst_fix : SQLERRM ' || Sqlerrm);
1513 END get_vst_fix;
1514
1515
1516 -- ***************************************************************************
1517 -- * VST validate_vst_something(); functions
1518 -- ***************************************************************************
1519 -- ===========================================================================
1520 -- Validates vset table definition
1521 -- ===========================================================================
1522 FUNCTION validate_vst_tbl(p_flex_value_set_id IN NUMBER)
1523 RETURN VARCHAR2
1524 IS
1525 l_vst_set vst_set_type;
1526 l_vst_tbl vst_tbl_type;
1527 l_result VARCHAR2(20);
1528 l_message VARCHAR2(2000);
1529 BEGIN
1530 IF (NOT fetch_vst_set(p_flex_value_set_id,
1531 l_vst_set,
1532 l_message)) THEN
1533 GOTO return_error;
1534 END IF;
1535
1536 IF (NOT fetch_vst_tbl(l_vst_set,
1537 l_vst_tbl,
1538 l_message)) THEN
1539 GOTO return_error;
1540 END IF;
1541
1542 fnd_flex_val_api.validate_table_vset
1543 (p_flex_value_set_name => l_vst_set.flex_value_set_name,
1544 p_id_column_name => l_vst_tbl.id_column_name,
1545 p_value_column_name => l_vst_tbl.value_column_name,
1546 p_meaning_column_name => l_vst_tbl.meaning_column_name,
1547 p_additional_quickpick_columns => l_vst_tbl.additional_quickpick_columns,
1548 p_application_table_name => l_vst_tbl.application_table_name,
1549 p_additional_where_clause => l_vst_tbl.additional_where_clause,
1550 x_result => l_result,
1551 x_message => l_message);
1552
1553 IF (l_result = 'Failure') THEN
1554 GOTO return_error;
1555 END IF;
1556
1557 <<return_success>>
1558 RETURN ('Success');
1559
1560 <<return_error>>
1561 RETURN (l_message);
1562
1563 EXCEPTION
1564 WHEN OTHERS THEN
1565 RETURN('validate_vst_tbl : SQLERRM ' || Sqlerrm);
1566 END validate_vst_tbl;
1567
1568 -- ***************************************************************************
1569 -- * VST fix_vst_something(); procedures.
1570 -- ***************************************************************************
1571 -- ===========================================================================
1572 PROCEDURE fix_vst_set(p_rule IN VARCHAR2,
1573 p_flex_value_set_id IN NUMBER,
1574 x_message OUT nocopy VARCHAR2)
1575 IS
1576 l_vst_set vst_set_type;
1577 l_dep_set vst_set_type;
1578 l_vst_tbl vst_tbl_type;
1579 l_count NUMBER;
1580 BEGIN
1581 IF (p_rule = 'B.01') THEN
1582 --
1583 -- Table without set.
1584 --
1585 BEGIN
1586 DELETE
1587 FROM fnd_flex_validation_tables fvt
1588 WHERE flex_value_set_id = p_flex_value_set_id
1589 AND NOT EXISTS
1590 (SELECT null
1591 FROM fnd_flex_value_sets fvs
1592 WHERE fvs.flex_value_set_id = fvt.flex_value_set_id
1593 AND fvs.validation_type = 'F');
1594 x_message := SQL%rowcount || ' row(s) deleted.';
1595 GOTO return_success;
1596 EXCEPTION
1597 WHEN OTHERS THEN
1598 x_message :=
1599 'Unable to delete from FND_FLEX_VALIDATION_TABLES. ' || Sqlerrm;
1600 GOTO return_error;
1601 END;
1602 END IF;
1603
1604 IF (NOT fetch_vst_set(p_flex_value_set_id,
1605 l_vst_set,
1606 x_message)) THEN
1607 GOTO return_error;
1608 END IF;
1609
1610 IF (p_rule = 'A.01') THEN
1611 --
1612 -- Dep vset with NULL parent_flex_value_set_id.
1613 --
1614 IF (l_vst_set.validation_type NOT IN ('D', 'Y')) THEN
1615 x_message := 'This is not a dependent value set. No need to fix.';
1616 GOTO return_error;
1617 END IF;
1618
1619 IF (l_vst_set.parent_flex_value_set_id IS NOT NULL) THEN
1620 x_message := 'Parent flex value set id is not null. No need to fix.';
1621 GOTO return_error;
1622 END IF;
1623
1624 BEGIN
1625 UPDATE fnd_flex_value_sets SET
1626 validation_type = 'N',
1627 last_update_date = Sysdate,
1628 last_updated_by = 1
1629 WHERE flex_value_set_id = l_vst_set.flex_value_set_id;
1630 x_message := SQL%rowcount || ' row(s) updated.';
1631 GOTO return_success;
1632 EXCEPTION
1633 WHEN OTHERS THEN
1634 x_message := 'Unable to update FND_FLEX_VALUE_SETS. ' || Sqlerrm;
1635 GOTO return_error;
1636 END;
1637 END IF;
1638
1639 IF (p_rule = 'A.02') THEN
1640 --
1641 -- Dep vset with non-existing parent_flex_value_set_id.
1642 --
1643 IF (l_vst_set.validation_type NOT IN ('D', 'Y')) THEN
1644 x_message := 'This is not a dependent value set. No need to fix.';
1645 GOTO return_error;
1646 END IF;
1647
1648 IF (fetch_vst_set(l_vst_set.parent_flex_value_set_id,
1649 l_dep_set,
1650 x_message)) THEN
1651 x_message := 'Parent value set exists. No need to fix.';
1652 GOTO return_error;
1653 END IF;
1654
1655 BEGIN
1656 UPDATE fnd_flex_value_sets SET
1657 validation_type = 'N',
1658 parent_flex_value_set_id = NULL,
1659 dependant_default_value = NULL,
1660 dependant_default_meaning = NULL,
1661 last_update_date = Sysdate,
1662 last_updated_by = 1
1663 WHERE flex_value_set_id = l_vst_set.flex_value_set_id;
1664 x_message := SQL%rowcount || ' row(s) updated.';
1665 GOTO return_success;
1666 EXCEPTION
1667 WHEN OTHERS THEN
1668 x_message := 'Unable to update FND_FLEX_VALUE_SETS. ' || Sqlerrm;
1669 GOTO return_error;
1670 END;
1671 END IF;
1672
1673 IF (p_rule = 'A.03') THEN
1674 --
1675 -- Table vsets without table info.
1676 --
1677 IF (l_vst_set.validation_type <> 'F') THEN
1678 x_message := 'This is not a table value set. No need to fix.';
1679 GOTO return_error;
1680 END IF;
1681
1682 IF (fetch_vst_tbl(l_vst_set,
1683 l_vst_tbl,
1684 x_message)) THEN
1685 x_message := 'Table info exists. No need to fix.';
1686 GOTO return_error;
1687 END IF;
1688
1689 BEGIN
1690 UPDATE fnd_flex_value_sets SET
1691 validation_type = 'N',
1692 last_update_date = Sysdate,
1693 last_updated_by = 1
1694 WHERE flex_value_set_id = l_vst_set.flex_value_set_id;
1695 x_message := SQL%rowcount || ' row(s) updated.';
1696 GOTO return_success;
1697 EXCEPTION
1698 WHEN OTHERS THEN
1699 x_message := 'Unable to update FND_FLEX_VALUE_SETS. ' || Sqlerrm;
1700 GOTO return_error;
1701 END;
1702 END IF;
1703
1704 IF (p_rule = 'A.04') THEN
1705 --
1706 -- Uexit vsets without any uexit.
1707 --
1708 IF (l_vst_set.validation_type NOT IN ('U', 'P')) THEN
1709 x_message := 'This is not a user exit value set. No need to fix.';
1710 GOTO return_error;
1711 END IF;
1712
1713 BEGIN
1714 SELECT COUNT(*) INTO l_count
1715 FROM fnd_flex_validation_events
1716 WHERE flex_value_set_id = l_vst_set.flex_value_set_id;
1717 EXCEPTION
1718 WHEN OTHERS THEN
1719 x_message :=
1720 'Unable to count FND_FLEX_VALIDATION_EVENTS. ' || Sqlerrm;
1721 GOTO return_error;
1722 END;
1723
1724 IF (l_count > 0) THEN
1725 x_message :=
1726 'There are ' || To_char(l_count) || ' user exits. No need to fix.';
1727 GOTO return_error;
1728 END IF;
1729
1730 BEGIN
1731 UPDATE fnd_flex_value_sets SET
1732 validation_type = 'N',
1733 last_update_date = Sysdate,
1734 last_updated_by = 1
1735 WHERE flex_value_set_id = l_vst_set.flex_value_set_id;
1736 x_message := SQL%rowcount || ' row(s) updated.';
1737 GOTO return_success;
1738 EXCEPTION
1739 WHEN OTHERS THEN
1740 x_message := 'Unable to update FND_FLEX_VALUE_SETS. ' || Sqlerrm;
1741 GOTO return_error;
1742 END;
1743 END IF;
1744
1745 IF (p_rule = 'A.05') THEN
1746 --
1747 -- Problems in flags.
1748 --
1749 IF (NOT lookup_code_exists('SEG_VAL_TYPES',
1750 l_vst_set.validation_type)) THEN
1751 l_vst_set.validation_type := 'N';
1752 END IF;
1753
1754 IF (NOT lookup_code_exists('FIELD_TYPE',
1755 l_vst_set.format_type)) THEN
1756 l_vst_set.format_type := 'C';
1757 END IF;
1758
1759 IF (NOT lookup_code_exists('YES_NO',
1760 l_vst_set.protected_flag)) THEN
1761 l_vst_set.protected_flag := 'N';
1762 END IF;
1763
1764 IF (NOT lookup_code_exists('FLEX_VALUESET_LONGLIST_FLAG',
1765 l_vst_set.longlist_flag)) THEN
1766 l_vst_set.longlist_flag := 'N';
1767 END IF;
1768
1769 IF (NOT lookup_code_exists('FLEX_VST_SECURITY_ENABLED_FLAG',
1770 l_vst_set.security_enabled_flag)) THEN
1771 l_vst_set.security_enabled_flag := 'N';
1772 END IF;
1773
1774 IF (NOT lookup_code_exists('YES_NO',
1775 l_vst_set.alphanumeric_allowed_flag)) THEN
1776 l_vst_set.alphanumeric_allowed_flag := 'N';
1777 END IF;
1778
1779 IF (NOT lookup_code_exists('YES_NO',
1780 l_vst_set.numeric_mode_enabled_flag)) THEN
1781 l_vst_set.numeric_mode_enabled_flag := 'N';
1782 END IF;
1783
1784 IF (NOT lookup_code_exists('YES_NO',
1785 l_vst_set.uppercase_only_flag)) THEN
1786 l_vst_set.uppercase_only_flag := 'N';
1787 END IF;
1788
1789 IF (l_vst_set.validation_type IN ('D', 'Y')) THEN
1790 IF (l_vst_set.dependant_default_value IS NULL) THEN
1791 l_vst_set.dependant_default_value := 'N/A';
1792 END IF;
1793 IF (l_vst_set.dependant_default_meaning IS NULL) THEN
1794 l_vst_set.dependant_default_meaning := 'N/A';
1795 END IF;
1796 END IF;
1797
1798 BEGIN
1799 UPDATE fnd_flex_value_sets SET
1800 validation_type = l_vst_set.validation_type,
1801 format_type = l_vst_set.format_type,
1802 protected_flag = l_vst_set.protected_flag,
1803 longlist_flag = l_vst_set.longlist_flag,
1804 security_enabled_flag = l_vst_set.security_enabled_flag,
1805 alphanumeric_allowed_flag = l_vst_set.alphanumeric_allowed_flag,
1806 numeric_mode_enabled_flag = l_vst_set.numeric_mode_enabled_flag,
1807 uppercase_only_flag = l_vst_set.uppercase_only_flag,
1808 dependant_default_value = l_vst_set.dependant_default_value,
1809 dependant_default_meaning = l_vst_set.dependant_default_meaning,
1810 last_update_date = Sysdate,
1811 last_updated_by = 1
1812 WHERE flex_value_set_id = l_vst_set.flex_value_set_id;
1813 x_message := SQL%rowcount || ' row(s) updated.';
1814 GOTO return_success;
1815 EXCEPTION
1816 WHEN OTHERS THEN
1817 x_message := 'Unable to update FND_FLEX_VALUE_SETS. ' || Sqlerrm;
1818 GOTO return_error;
1819 END;
1820 END IF;
1821
1822 IF (p_rule = 'B.02') THEN
1823 --
1824 -- Problems in flags.
1825 --
1826 IF (NOT fetch_vst_tbl(l_vst_set,
1827 l_vst_tbl,
1828 x_message)) THEN
1829 GOTO return_error;
1830 END IF;
1831
1832 IF ((l_vst_tbl.id_column_type IS NOT NULL) AND
1833 (NOT lookup_code_exists('COLUMN_TYPE',
1834 l_vst_tbl.id_column_type))) THEN
1835 l_vst_tbl.id_column_type := 'V';
1836 END IF;
1837
1838 IF (NOT lookup_code_exists('COLUMN_TYPE',
1839 l_vst_tbl.value_column_type)) THEN
1840 l_vst_tbl.value_column_type := 'V';
1841 END IF;
1842
1843 IF ((l_vst_tbl.meaning_column_type IS NOT NULL) AND
1844 (NOT lookup_code_exists('COLUMN_TYPE',
1845 l_vst_tbl.meaning_column_type))) THEN
1846 l_vst_tbl.meaning_column_type := 'V';
1847 END IF;
1848
1849 IF (NOT lookup_code_exists('YES_NO',
1850 l_vst_tbl.summary_allowed_flag)) THEN
1851 l_vst_tbl.summary_allowed_flag := 'N';
1852 END IF;
1853
1854 BEGIN
1855 UPDATE fnd_flex_validation_tables SET
1856 id_column_type = l_vst_tbl.id_column_type,
1857 value_column_type = l_vst_tbl.value_column_type,
1858 meaning_column_type = l_vst_tbl.meaning_column_type,
1859 summary_allowed_flag = l_vst_tbl.summary_allowed_flag,
1860 last_update_date = Sysdate,
1861 last_updated_by = 1
1862 WHERE flex_value_set_id = l_vst_tbl.flex_value_set_id;
1863 x_message := SQL%rowcount || ' row(s) updated.';
1864 GOTO return_success;
1865 EXCEPTION
1866 WHEN OTHERS THEN
1867 x_message := 'Unable to update FND_FLEX_VALIDATION_TABLES. ' ||
1868 Sqlerrm;
1869 GOTO return_error;
1870 END;
1871 END IF;
1872
1873 <<return_success>>
1874 <<return_error>>
1875 RETURN;
1876 EXCEPTION
1877 WHEN OTHERS THEN
1878 x_message := 'fix_vst_set: Top level error: ' || Sqlerrm;
1879 END fix_vst_set;
1880
1881 -- ===========================================================================
1882 PROCEDURE fix_vst_evt(p_rule IN VARCHAR2,
1883 p_flex_value_set_id IN NUMBER,
1884 p_event_code IN VARCHAR2,
1885 x_message OUT nocopy VARCHAR2)
1886 IS
1887 l_vst_set vst_set_type;
1888 l_vst_evt vst_evt_type;
1889 BEGIN
1890 IF (p_rule = 'C.01') THEN
1891 --
1892 -- Events without set.
1893 --
1894 BEGIN
1895 DELETE
1896 FROM fnd_flex_validation_events fve
1897 WHERE flex_value_set_id = p_flex_value_set_id
1898 AND event_code = p_event_code
1899 AND NOT EXISTS
1900 (SELECT null
1901 FROM fnd_flex_value_sets fvs
1902 WHERE fvs.flex_value_set_id = fve.flex_value_set_id
1903 AND fvs.validation_type IN ('U', 'P'));
1904 x_message := SQL%rowcount || ' row(s) deleted.';
1905 GOTO return_success;
1906 EXCEPTION
1907 WHEN OTHERS THEN
1908 x_message :=
1909 'Unable to delete from FND_FLEX_VALIDATION_EVENTS. ' || Sqlerrm;
1910 GOTO return_error;
1911 END;
1912 END IF;
1913
1914 IF (NOT fetch_vst_set(p_flex_value_set_id,
1915 l_vst_set,
1916 x_message)) THEN
1917 GOTO return_error;
1918 END IF;
1919
1920 IF (NOT fetch_vst_evt(l_vst_set,
1921 p_event_code,
1922 l_vst_evt,
1923 x_message)) THEN
1924 GOTO return_error;
1925 END IF;
1926
1927 IF (p_rule = 'C.02') THEN
1928 --
1929 -- Event code is unknown.
1930 --
1931 IF (NOT lookup_code_exists('FLEX_VALIDATION_EVENTS',
1932 l_vst_evt.event_code)) THEN
1933 BEGIN
1934 DELETE
1935 FROM fnd_flex_validation_events fve
1936 WHERE flex_value_set_id = l_vst_evt.flex_value_set_id
1937 AND event_code = l_vst_evt.event_code;
1938 x_message := SQL%rowcount || ' row(s) deleted.';
1939 GOTO return_success;
1940 EXCEPTION
1941 WHEN OTHERS THEN
1942 x_message :=
1943 'Unable to delete from FND_FLEX_VALIDATION_EVENTS. ' ||
1944 Sqlerrm;
1945 GOTO return_error;
1946 END;
1947 END IF;
1948 END IF;
1949
1950 <<return_success>>
1951 <<return_error>>
1952 RETURN;
1953 EXCEPTION
1954 WHEN OTHERS THEN
1955 x_message := 'fix_vst_evt: Top level error: ' || Sqlerrm;
1956 END fix_vst_evt;
1957
1958 -- ===========================================================================
1959 PROCEDURE fix_vst_scr(p_rule IN VARCHAR2,
1960 p_flex_value_set_id IN NUMBER,
1961 p_flex_value_rule_id IN NUMBER,
1962 x_message OUT nocopy VARCHAR2)
1963 IS
1964 BEGIN
1965 IF (p_rule = 'D.03') THEN
1966 --
1967 -- Security rules without set.
1968 --
1969 BEGIN
1970 DELETE
1971 FROM fnd_flex_value_rules fvr
1972 WHERE flex_value_set_id = p_flex_value_set_id
1973 AND flex_value_rule_id = p_flex_value_rule_id
1974 AND NOT EXISTS
1975 (SELECT null
1976 FROM fnd_flex_value_sets fvs
1977 WHERE fvs.flex_value_set_id = fvr.flex_value_set_id);
1978 x_message := SQL%rowcount || ' row(s) deleted.';
1979 GOTO return_success;
1980 EXCEPTION
1981 WHEN OTHERS THEN
1982 x_message :=
1983 'Unable to delete from FND_FLEX_VALUE_RULES. ' || Sqlerrm;
1984 GOTO return_error;
1985 END;
1986 END IF;
1987
1988 <<return_success>>
1989 <<return_error>>
1990 RETURN;
1991 EXCEPTION
1992 WHEN OTHERS THEN
1993 x_message := 'fix_vst_scr: Top level error: ' || Sqlerrm;
1994 END fix_vst_scr;
1995
1996 -- ===========================================================================
1997 PROCEDURE fix_vst_scl(p_rule IN VARCHAR2,
1998 p_flex_value_set_id IN NUMBER,
1999 p_flex_value_rule_id IN NUMBER,
2000 p_include_exclude_indicator IN VARCHAR2,
2001 p_flex_value_low IN VARCHAR2,
2002 p_flex_value_high IN VARCHAR2,
2003 x_message OUT nocopy VARCHAR2)
2004 IS
2005 l_vst_set vst_set_type;
2006 l_vst_scr vst_scr_type;
2007 l_vst_scl vst_scl_type;
2008 BEGIN
2009 IF (p_rule = 'E.01') THEN
2010 --
2011 -- Security lines without rules.
2012 --
2013 BEGIN
2014 DELETE
2015 FROM fnd_flex_value_rule_lines fvrl
2016 WHERE flex_value_set_id = p_flex_value_set_id
2017 AND flex_value_rule_id = p_flex_value_rule_id
2018 AND include_exclude_indicator = p_include_exclude_indicator
2019 AND flex_value_low = p_flex_value_low
2020 AND flex_value_high = p_flex_value_high
2021 AND NOT EXISTS
2022 (SELECT null
2023 FROM fnd_flex_value_rules fvr
2024 WHERE fvr.flex_value_set_id = fvrl.flex_value_set_id
2025 AND fvr.flex_value_rule_id = fvrl.flex_value_rule_id);
2026 x_message := SQL%rowcount || ' row(s) deleted.';
2027 GOTO return_success;
2028 EXCEPTION
2029 WHEN OTHERS THEN
2030 x_message :=
2031 'Unable to delete from FND_FLEX_VALUE_RULE_LINES. ' || Sqlerrm;
2032 GOTO return_error;
2033 END;
2034 END IF;
2035
2036 IF (NOT fetch_vst_set(p_flex_value_set_id,
2037 l_vst_set,
2038 x_message)) THEN
2039 GOTO return_error;
2040 END IF;
2041
2042 IF (NOT fetch_vst_scr(l_vst_set,
2043 p_flex_value_rule_id,
2044 l_vst_scr,
2045 x_message)) THEN
2046 GOTO return_error;
2047 END IF;
2048
2049 IF (NOT fetch_vst_scl(l_vst_scr,
2050 p_include_exclude_indicator,
2051 p_flex_value_low,
2052 p_flex_value_high,
2053 l_vst_scl,
2054 x_message)) THEN
2055 GOTO return_error;
2056 END IF;
2057
2058 IF (p_rule = 'E.02') THEN
2059 --
2060 -- I/E indicator is unknown.
2061 --
2062 IF (NOT lookup_code_exists('INCLUDE_EXCLUDE',
2063 l_vst_scl.include_exclude_indicator)) THEN
2064 BEGIN
2065 DELETE
2066 FROM fnd_flex_value_rule_lines fvrl
2067 WHERE flex_value_set_id = l_vst_scl.flex_value_set_id
2068 AND flex_value_rule_id = l_vst_scl.flex_value_rule_id
2069 AND include_exclude_indicator=l_vst_scl.include_exclude_indicator
2070 AND flex_value_low = l_vst_scl.flex_value_low
2071 AND flex_value_high = l_vst_scl.flex_value_high;
2072 x_message := SQL%rowcount || ' row(s) deleted.';
2073 GOTO return_success;
2074 EXCEPTION
2075 WHEN OTHERS THEN
2076 x_message :=
2077 'Unable to delete from FND_FLEX_VALUE_RULES. ' || Sqlerrm;
2078 GOTO return_error;
2079 END;
2080 END IF;
2081 END IF;
2082
2083 <<return_success>>
2084 <<return_error>>
2085 RETURN;
2086 EXCEPTION
2087 WHEN OTHERS THEN
2088 x_message := 'fix_vst_scl: Top level error: ' || Sqlerrm;
2089 END fix_vst_scl;
2090
2091 -- ===========================================================================
2092 PROCEDURE fix_vst_scu(p_rule IN VARCHAR2,
2093 p_flex_value_set_id IN NUMBER,
2094 p_flex_value_rule_id IN NUMBER,
2095 p_application_id IN NUMBER,
2096 p_responsibility_id IN NUMBER,
2097 x_message OUT nocopy VARCHAR2)
2098 IS
2099 l_vst_set vst_set_type;
2100 l_vst_scr vst_scr_type;
2101 l_vst_scu vst_scu_type;
2102 BEGIN
2103 IF (p_rule = 'F.01') THEN
2104 --
2105 -- Security usage without rule.
2106 --
2107 BEGIN
2108 DELETE
2109 FROM fnd_flex_value_rule_usages fvru
2110 WHERE flex_value_set_id = p_flex_value_set_id
2111 AND flex_value_rule_id = p_flex_value_rule_id
2112 AND application_id = p_application_id
2113 AND responsibility_id = p_responsibility_id
2114 AND NOT EXISTS
2115 (SELECT null
2116 FROM fnd_flex_value_rules fvr
2117 WHERE fvr.flex_value_set_id = fvru.flex_value_set_id
2118 AND fvr.flex_value_rule_id = fvru.flex_value_rule_id);
2119 x_message := SQL%rowcount || ' row(s) deleted.';
2120 GOTO return_success;
2121 EXCEPTION
2122 WHEN OTHERS THEN
2123 x_message :=
2124 'Unable to delete from FND_FLEX_VALUE_RULE_USAGES. ' || Sqlerrm;
2125 GOTO return_error;
2126 END;
2127 END IF;
2128
2129 IF (NOT fetch_vst_set(p_flex_value_set_id,
2130 l_vst_set,
2131 x_message)) THEN
2132 GOTO return_error;
2133 END IF;
2134
2135 IF (NOT fetch_vst_scr(l_vst_set,
2136 p_flex_value_rule_id,
2137 l_vst_scr,
2138 x_message)) THEN
2139 GOTO return_error;
2140 END IF;
2141
2142 IF (NOT fetch_vst_scu(l_vst_scr,
2143 p_application_id,
2144 p_responsibility_id,
2145 l_vst_scu,
2146 x_message)) THEN
2147 GOTO return_error;
2148 END IF;
2149
2150 IF (p_rule = 'F.02') THEN
2151 --
2152 -- Security usage with invalid application id.
2153 --
2154 BEGIN
2155 DELETE
2156 FROM fnd_flex_value_rule_usages fvru
2157 WHERE flex_value_set_id = l_vst_scu.flex_value_set_id
2158 AND flex_value_rule_id = l_vst_scu.flex_value_rule_id
2159 AND application_id = l_vst_scu.application_id
2160 AND responsibility_id = l_vst_scu.responsibility_id
2161 AND NOT EXISTS
2162 (SELECT null
2163 FROM fnd_application a
2164 WHERE a.application_id = fvru.application_id);
2165 x_message := SQL%rowcount || ' row(s) deleted.';
2166 GOTO return_success;
2167 EXCEPTION
2168 WHEN OTHERS THEN
2169 x_message :=
2170 'Unable to delete from FND_FLEX_VALUE_RULE_USAGES. ' || Sqlerrm;
2171 GOTO return_error;
2172 END;
2173 END IF;
2174
2175 IF (p_rule = 'F.03') THEN
2176 --
2177 -- Security usage with invalid responsibility id.
2178 --
2179 BEGIN
2180 DELETE
2181 FROM fnd_flex_value_rule_usages fvru
2182 WHERE flex_value_set_id = l_vst_scu.flex_value_set_id
2183 AND flex_value_rule_id = l_vst_scu.flex_value_rule_id
2184 AND application_id = l_vst_scu.application_id
2185 AND responsibility_id = l_vst_scu.responsibility_id
2186 AND NOT EXISTS
2187 (SELECT null
2188 FROM fnd_responsibility r
2189 WHERE r.application_id = fvru.application_id
2190 AND r.responsibility_id = fvru.responsibility_id);
2191 x_message := SQL%rowcount || ' row(s) deleted.';
2192 GOTO return_success;
2193 EXCEPTION
2194 WHEN OTHERS THEN
2195 x_message :=
2196 'Unable to delete from FND_FLEX_VALUE_RULE_USAGES. ' || Sqlerrm;
2197 GOTO return_error;
2198 END;
2199 END IF;
2200
2201 <<return_success>>
2202 <<return_error>>
2203 RETURN;
2204 EXCEPTION
2205 WHEN OTHERS THEN
2206 x_message := 'fix_vst_scu: Top level error: ' || Sqlerrm;
2207 END fix_vst_scu;
2208
2209 -- ===========================================================================
2210 PROCEDURE fix_vst_val(p_rule IN VARCHAR2,
2211 p_flex_value_set_id IN NUMBER,
2212 p_flex_value_id IN NUMBER,
2213 x_message OUT nocopy VARCHAR2)
2214 IS
2215 l_vst_set vst_set_type;
2216 l_vst_val vst_val_type;
2217 BEGIN
2218 IF (p_rule = 'G.03') THEN
2219 --
2220 -- Values without set.
2221 --
2222 BEGIN
2223 DELETE
2224 FROM fnd_flex_values fv
2225 WHERE flex_value_set_id = p_flex_value_set_id
2226 AND flex_value_id = p_flex_value_id
2227 AND NOT EXISTS
2228 (SELECT null
2229 FROM fnd_flex_value_sets fvs
2230 WHERE fvs.flex_value_set_id = fv.flex_value_set_id);
2231 x_message := SQL%rowcount || ' row(s) deleted.';
2232 GOTO return_success;
2233 EXCEPTION
2234 WHEN OTHERS THEN
2235 x_message :=
2236 'Unable to delete from FND_FLEX_VALUES. ' || Sqlerrm;
2237 GOTO return_error;
2238 END;
2239 END IF;
2240
2241 IF (NOT fetch_vst_set(p_flex_value_set_id,
2242 l_vst_set,
2243 x_message)) THEN
2244 GOTO return_error;
2245 END IF;
2246
2247 IF (NOT fetch_vst_val(l_vst_set,
2248 p_flex_value_id,
2249 l_vst_val,
2250 x_message)) THEN
2251 GOTO return_error;
2252 END IF;
2253
2254 IF (p_rule = 'G.04') THEN
2255 --
2256 -- Problems in flags.
2257 --
2258 IF ((l_vst_val.start_date_active IS NOT NULL) AND
2259 (l_vst_val.end_date_active IS NOT NULL) AND
2260 (l_vst_val.start_date_active > l_vst_val.end_date_active)) THEN
2261 l_vst_val.end_date_active := l_vst_val.start_date_active;
2262 END IF;
2263
2264 IF (NOT lookup_code_exists('YES_NO',
2265 l_vst_val.enabled_flag)) THEN
2266 l_vst_val.enabled_flag := 'Y';
2267 END IF;
2268
2269 IF (NOT lookup_code_exists('YES_NO',
2270 l_vst_val.summary_flag)) THEN
2271 l_vst_val.summary_flag := 'N';
2272 END IF;
2273
2274 BEGIN
2275 UPDATE fnd_flex_values fv SET
2276 start_date_active = l_vst_val.start_date_active,
2277 end_date_active = l_vst_val.end_date_active,
2278 enabled_flag = l_vst_val.enabled_flag,
2279 summary_flag = l_vst_val.summary_flag,
2280 last_update_date = Sysdate,
2281 last_updated_by = 1
2282 WHERE flex_value_set_id = l_vst_val.flex_value_set_id
2283 AND flex_value_id = l_vst_val.flex_value_id;
2284 x_message := SQL%rowcount || ' row(s) updated.';
2285 GOTO return_success;
2286 EXCEPTION
2287 WHEN OTHERS THEN
2288 x_message := 'Unable to update FND_FLEX_VALUES. ' || Sqlerrm;
2289 GOTO return_error;
2290 END;
2291 END IF;
2292
2293 <<return_success>>
2294 <<return_error>>
2295 RETURN;
2296 EXCEPTION
2297 WHEN OTHERS THEN
2298 x_message := 'fix_vst_val: Top level error: ' || Sqlerrm;
2299 END fix_vst_val;
2300
2301 -- ===========================================================================
2302 PROCEDURE fix_vst_rlg(p_rule IN VARCHAR2,
2303 p_flex_value_set_id IN NUMBER,
2304 p_hierarchy_id IN NUMBER,
2305 x_message OUT nocopy VARCHAR2)
2306 IS
2307 BEGIN
2308 IF (p_rule = 'H.03') THEN
2309 --
2310 -- Rollup group without set.
2311 --
2312 BEGIN
2313 DELETE
2314 FROM fnd_flex_hierarchies fh
2315 WHERE flex_value_set_id = p_flex_value_set_id
2316 AND hierarchy_id = p_hierarchy_id
2317 AND NOT EXISTS
2318 (SELECT null
2319 FROM fnd_flex_value_sets fvs
2320 WHERE fvs.flex_value_set_id = fh.flex_value_set_id);
2321 x_message := SQL%rowcount || ' row(s) deleted.';
2322 GOTO return_success;
2323 EXCEPTION
2324 WHEN OTHERS THEN
2325 x_message :=
2326 'Unable to delete from FND_FLEX_HIERARCHIES. ' || Sqlerrm;
2327 GOTO return_error;
2328 END;
2329 END IF;
2330
2331 <<return_success>>
2332 <<return_error>>
2333 RETURN;
2334 EXCEPTION
2335 WHEN OTHERS THEN
2336 x_message := 'fix_vst_rlg: Top level error: ' || Sqlerrm;
2337 END fix_vst_rlg;
2338
2339 -- ===========================================================================
2340 PROCEDURE fix_vst_fvn(p_rule IN VARCHAR2,
2341 p_flex_value_set_id IN NUMBER,
2342 p_parent_flex_value IN VARCHAR2,
2343 p_range_attribute IN VARCHAR2,
2344 p_child_flex_value_low IN VARCHAR2,
2345 p_child_flex_value_high IN VARCHAR2,
2346 x_message OUT nocopy VARCHAR2)
2347 IS
2348 BEGIN
2349 IF (p_rule = 'I.01') THEN
2350 --
2351 -- Norm hierarchy without value.
2352 --
2353 BEGIN
2354 DELETE
2355 FROM fnd_flex_value_norm_hierarchy fvnh
2356 WHERE flex_value_set_id = p_flex_value_set_id
2357 AND parent_flex_value = p_parent_flex_value
2358 AND range_attribute = p_range_attribute
2359 AND child_flex_value_low = p_child_flex_value_low
2360 AND child_flex_value_high = p_child_flex_value_high
2361 AND NOT EXISTS
2362 (SELECT null
2363 FROM fnd_flex_values fv
2364 WHERE fv.flex_value_set_id = fvnh.flex_value_set_id
2365 AND fv.flex_value = fvnh.parent_flex_value);
2366 x_message := SQL%rowcount || ' row(s) deleted.';
2367 GOTO return_success;
2368 EXCEPTION
2369 WHEN OTHERS THEN
2370 x_message :=
2371 'Unable to delete from FND_FLEX_VALUE_NORM_HIERARCHY. ' ||
2372 Sqlerrm;
2373 GOTO return_error;
2374 END;
2375 END IF;
2376
2377 <<return_success>>
2378 <<return_error>>
2379 RETURN;
2380 EXCEPTION
2381 WHEN OTHERS THEN
2382 x_message := 'fix_vst_fvn: Top level error: ' || Sqlerrm;
2383 END fix_vst_fvn;
2384
2385 -- ===========================================================================
2386 PROCEDURE fix_vst_fvh(p_rule IN VARCHAR2,
2387 p_flex_value_set_id IN NUMBER,
2388 p_parent_flex_value IN VARCHAR2,
2389 p_child_flex_value_low IN VARCHAR2,
2390 p_child_flex_value_high IN VARCHAR2,
2391 x_message OUT nocopy VARCHAR2)
2392 IS
2393 BEGIN
2394 IF (p_rule = 'J.01') THEN
2395 --
2396 -- Denorm Hierarchy without norm hierarchy.
2397 --
2398 BEGIN
2399 DELETE
2400 FROM fnd_flex_value_hierarchies fvh
2401 WHERE flex_value_set_id = p_flex_value_set_id
2402 AND parent_flex_value = p_parent_flex_value
2403 AND child_flex_value_low = p_child_flex_value_low
2404 AND child_flex_value_high = p_child_flex_value_high
2405 AND NOT EXISTS
2406 (SELECT null
2407 FROM fnd_flex_value_norm_hierarchy fvnh
2408 WHERE fvnh.flex_value_set_id = fvh.flex_value_set_id
2409 AND fvnh.parent_flex_value = fvh.parent_flex_value);
2410 x_message := SQL%rowcount || ' row(s) deleted.';
2411 GOTO return_success;
2412 EXCEPTION
2413 WHEN OTHERS THEN
2414 x_message :=
2415 'Unable to delete from FND_FLEX_VALUE_HIERARCHIES. ' ||
2416 Sqlerrm;
2417 GOTO return_error;
2418 END;
2419 END IF;
2420
2421 <<return_success>>
2422 <<return_error>>
2423 RETURN;
2424 EXCEPTION
2425 WHEN OTHERS THEN
2426 x_message := 'fix_vst_fvh: Top level error: ' || Sqlerrm;
2427 END fix_vst_fvh;
2428
2429 -- ***************************************************************************
2430 -- * DFF get_dff_something() RETURN VARCHAR2; functions.
2431 -- ***************************************************************************
2432 -- ===========================================================================
2433 FUNCTION get_dff_flx(p_application_id IN NUMBER,
2434 p_descriptive_flexfield_name IN VARCHAR2)
2435 RETURN VARCHAR2
2436 IS
2437 l_return VARCHAR2(2000);
2438 BEGIN
2439 SELECT descriptive_flexfield_name || '/' || title
2440 INTO l_return
2441 FROM fnd_descriptive_flexs_vl
2442 WHERE application_id = p_application_id
2443 AND descriptive_flexfield_name = p_descriptive_flexfield_name;
2444 RETURN(line_return(l_return));
2445 EXCEPTION
2446 WHEN no_data_found THEN
2447 RETURN(line_return(p_descriptive_flexfield_name ||
2448 '/get_dff_flx: no data found.'));
2449 WHEN OTHERS THEN
2450 RETURN(line_return(p_descriptive_flexfield_name ||
2451 '/get_dff_flx SQLCODE:' || To_char(SQLCODE)));
2452 END get_dff_flx;
2453
2454 -- ===========================================================================
2455 FUNCTION get_dff_ctx(p_application_id IN NUMBER,
2456 p_descriptive_flexfield_name IN VARCHAR2,
2457 p_descriptive_flex_context_cod IN VARCHAR2)
2458 RETURN VARCHAR2
2459 IS
2460 l_return VARCHAR2(2000);
2461 BEGIN
2462 SELECT descriptive_flex_context_code || '/' ||
2463 global_flag || '/' ||
2464 enabled_flag || '/' ||
2465 descriptive_flex_context_name || '/' ||
2466 Nvl(description, '<NULL>')
2467 INTO l_return
2468 FROM fnd_descr_flex_contexts_vl
2469 WHERE application_id = p_application_id
2470 AND descriptive_flexfield_name = p_descriptive_flexfield_name
2471 AND descriptive_flex_context_code = p_descriptive_flex_context_cod;
2472 RETURN(line_return(l_return));
2473 EXCEPTION
2474 WHEN no_data_found THEN
2475 RETURN(line_return(p_descriptive_flex_context_cod ||
2476 '/get_dff_ctx: no data found.'));
2477 WHEN OTHERS THEN
2478 RETURN(line_return(p_descriptive_flex_context_cod ||
2479 '/get_dff_ctx SQLCODE:' || To_char(SQLCODE)));
2480 END get_dff_ctx;
2481
2482 -- ===========================================================================
2483 FUNCTION get_dff_seg(p_application_id IN NUMBER,
2484 p_descriptive_flexfield_name IN VARCHAR2,
2485 p_descriptive_flex_context_cod IN VARCHAR2,
2486 p_application_column_name IN VARCHAR2)
2487 RETURN VARCHAR2
2488 IS
2489 l_return VARCHAR2(2000);
2490 BEGIN
2491 SELECT application_column_name || '/' ||
2492 enabled_flag || '/' ||
2493 display_flag || '/' ||
2494 end_user_column_name || '/' ||
2495 form_left_prompt || '/' ||
2496 Nvl(description, '<NULL>')
2497 INTO l_return
2498 FROM fnd_descr_flex_col_usage_vl
2499 WHERE application_id = p_application_id
2500 AND descriptive_flexfield_name = p_descriptive_flexfield_name
2501 AND descriptive_flex_context_code = p_descriptive_flex_context_cod
2502 AND application_column_name = p_application_column_name;
2503 RETURN(line_return(l_return));
2504 EXCEPTION
2505 WHEN no_data_found THEN
2506 RETURN(line_return(p_application_column_name ||
2507 '/get_dff_seg: no data found.'));
2508 WHEN OTHERS THEN
2509 RETURN(line_return(p_application_column_name ||
2510 '/get_dff_seg SQLCODE:' || To_char(SQLCODE)));
2511 END get_dff_seg;
2512
2513 -- ===========================================================================
2514 FUNCTION get_dff_tap(p_application_id IN NUMBER,
2515 p_descriptive_flexfield_name IN VARCHAR2)
2516 RETURN VARCHAR2
2517 IS
2518 l_return VARCHAR2(2000);
2519 BEGIN
2520 SELECT To_char(avl.application_id) || '/' ||
2521 avl.application_short_name || '/' ||
2522 avl.application_name
2523 INTO l_return
2524 FROM fnd_application_vl avl, fnd_descriptive_flexs df
2525 WHERE avl.application_id = df.table_application_id
2526 AND df.application_id = p_application_id
2527 AND df.descriptive_flexfield_name = p_descriptive_flexfield_name;
2528 RETURN(line_return(l_return));
2529 EXCEPTION
2530 WHEN no_data_found THEN
2531 RETURN(line_return('get_dff_tap: no data found.'));
2532 WHEN OTHERS THEN
2533 RETURN(line_return('get_dff_tap SQLCODE:' || To_char(SQLCODE)));
2534 END get_dff_tap;
2535
2536 -- ===========================================================================
2537 FUNCTION get_dff_tbl(p_application_id IN NUMBER,
2538 p_descriptive_flexfield_name IN VARCHAR2)
2539 RETURN VARCHAR2
2540 IS
2541 l_return VARCHAR2(2000);
2542 BEGIN
2543 SELECT us.table_owner || '/' ||
2544 ft.table_name || '/' ||
2545 To_char(table_id)
2546 INTO l_return
2547 FROM user_synonyms us, fnd_tables ft, fnd_descriptive_flexs df
2548 WHERE us.synonym_name = df.application_table_name
2549 AND ft.application_id = df.table_application_id
2550 AND ft.table_name = df.application_table_name
2551 AND df.application_id = p_application_id
2552 AND df.descriptive_flexfield_name = p_descriptive_flexfield_name;
2553 RETURN(line_return(l_return));
2554 EXCEPTION
2555 WHEN no_data_found THEN
2556 RETURN(line_return('get_dff_tbl: no data found.'));
2557 WHEN OTHERS THEN
2558 RETURN(line_return('get_dff_tbl SQLCODE:' || To_char(SQLCODE)));
2559 END get_dff_tbl;
2560
2561 -- ===========================================================================
2562 FUNCTION get_dff_col(p_application_id IN NUMBER,
2563 p_descriptive_flexfield_name IN VARCHAR2,
2564 p_application_column_name IN VARCHAR2)
2565 RETURN VARCHAR2
2566 IS
2567 l_return VARCHAR2(2000);
2568 BEGIN
2569 SELECT fc.column_name || '/' ||
2570 To_char(fc.column_id) || '/' ||
2571 fc.column_type || '/' ||
2572 To_char(fc.width) || '/' ||
2573 fc.flexfield_usage_code || '/' ||
2574 Nvl(To_char(fc.flexfield_application_id), '<NULL>') || '/' ||
2575 Nvl(fc.flexfield_name, '<NULL>')
2576 INTO l_return
2577 FROM fnd_columns fc, fnd_tables ft, fnd_descriptive_flexs df
2578 WHERE df.application_id = p_application_id
2579 AND df.descriptive_flexfield_name = p_descriptive_flexfield_name
2580 AND ft.application_id = df.table_application_id
2581 AND ft.table_name = df.application_table_name
2582 AND fc.application_id = ft.application_id
2583 AND fc.table_id = ft.table_id
2584 AND fc.column_name = p_application_column_name;
2585 RETURN(line_return(l_return));
2586 EXCEPTION
2587 WHEN no_data_found THEN
2588 RETURN(line_return('get_dff_col: no data found.'));
2589 WHEN OTHERS THEN
2590 RETURN(line_return('get_dff_col SQLCODE:' || To_char(SQLCODE)));
2591 END get_dff_col;
2592
2593 -- ===========================================================================
2594 FUNCTION get_dff_fix(p_rule IN VARCHAR2,
2595 p_pk1 IN VARCHAR2 DEFAULT NULL,
2596 p_pk2 IN VARCHAR2 DEFAULT NULL,
2597 p_pk3 IN VARCHAR2 DEFAULT NULL,
2598 p_pk4 IN VARCHAR2 DEFAULT NULL,
2599 p_pk5 IN VARCHAR2 DEFAULT NULL)
2600 RETURN VARCHAR2
2601 IS
2602 l_beg VARCHAR2(100);
2603 l_mid VARCHAR2(2000) := NULL;
2604 l_end VARCHAR2(100);
2605 l_cn VARCHAR2(100) := ',' || g_newline;
2606 BEGIN
2607 l_beg := (g_newline ||
2608 'variable msg VARCHAR2(2000);' || g_newline ||
2609 'BEGIN' || g_newline ||
2610 ' fnd_flex_diagnose.');
2611 IF (p_rule IN ('A.03', 'A.09', 'A.10', 'A.11', 'A.12', 'A.13', 'D.01')) THEN
2612 l_mid :=
2613 'fix_dff_flx' || g_newline ||
2614 ' (p_rule => ''' || p_rule || '''' || l_cn ||
2615 ' p_application_id => ' || p_pk1 || l_cn ||
2616 ' p_descriptive_flexfield_name => ''' || p_pk2 || '''' || l_cn;
2617 END IF;
2618 IF (p_rule IN ('B.01')) THEN
2619 l_mid :=
2620 'fix_dff_ref' || g_newline ||
2621 ' (p_rule => ''' || p_rule || '''' || l_cn ||
2622 ' p_application_id => ' || p_pk1 || l_cn ||
2623 ' p_descriptive_flexfield_name => ''' || p_pk2 || '''' || l_cn ||
2624 ' p_default_context_field_name => ''' || p_pk3 || '''' || l_cn;
2625 END IF;
2626 IF (p_rule IN ('C.03', 'C.04', 'C.05', 'C.06')) THEN
2627 l_mid :=
2628 'fix_dff_ctx' || g_newline ||
2629 ' (p_rule => ''' || p_rule || '''' || l_cn ||
2630 ' p_application_id => ' || p_pk1 || l_cn ||
2631 ' p_descriptive_flexfield_name => ''' || p_pk2 || '''' || l_cn ||
2632 ' p_descriptive_flex_context_cod => ''' || p_pk3 || '''' || l_cn;
2633 END IF;
2634 IF (p_rule IN ('E.03', 'E.06', 'E.07', 'E.08')) THEN
2635 l_mid :=
2636 'fix_dff_seg' || g_newline ||
2637 ' (p_rule => ''' || p_rule || '''' || l_cn ||
2638 ' p_application_id => ' || p_pk1 || l_cn ||
2639 ' p_descriptive_flexfield_name => ''' || p_pk2 || '''' || l_cn ||
2640 ' p_descriptive_flex_context_cod => ''' || p_pk3 || '''' || l_cn ||
2641 ' p_application_column_name => ''' || p_pk4 || '''' || l_cn;
2642 END IF;
2643 IF (p_rule IN ('F.01', 'F.02')) THEN
2644 l_mid :=
2645 'fix_dff_col' || g_newline ||
2646 ' (p_rule => ''' || p_rule || '''' || l_cn ||
2647 ' p_application_id => ' || p_pk1 || l_cn ||
2648 ' p_table_name => ''' || p_pk2 || '''' || l_cn ||
2649 ' p_column_name => ''' || p_pk3 || '''' || l_cn;
2650 END IF;
2651 l_end := (' x_message => :msg);' || g_newline ||
2652 'END;' || g_newline ||
2653 '/' || g_newline ||
2654 'print msg;' || g_newline || g_newline);
2655
2656 IF (l_mid IS NOT NULL) THEN
2657 RETURN(l_beg || l_mid || l_end);
2658 ELSE
2659 RETURN('DFF fix is not available for rule : ' || p_rule);
2660 END IF;
2661 EXCEPTION
2662 WHEN OTHERS THEN
2663 RETURN('get_dff_fix : SQLERRM ' || Sqlerrm);
2664 END get_dff_fix;
2665
2666
2667 -- ***************************************************************************
2668 -- * DFF fix_dff_something(); procedures.
2669 -- ***************************************************************************
2670 -- ===========================================================================
2671 PROCEDURE fix_dff_flx(p_rule IN VARCHAR2,
2672 p_application_id IN NUMBER,
2673 p_descriptive_flexfield_name IN VARCHAR2,
2674 x_message OUT nocopy VARCHAR2)
2675 IS
2676 l_tbl tbl_type;
2677 l_col col_type;
2678 l_dff_flx dff_flx_type;
2679 l_dff_ctx dff_ctx_type;
2680 l_vst_set vst_set_type;
2681 l_rowid VARCHAR2(100);
2682 l_count NUMBER;
2683 BEGIN
2684 IF (p_rule = 'A.03') THEN
2685 --
2686 -- DFF with invalid APPLICATION_ID.
2687 --
2688 BEGIN
2689 DELETE
2690 FROM fnd_descriptive_flexs df
2691 WHERE application_id = p_application_id
2692 AND descriptive_flexfield_name = p_descriptive_flexfield_name
2693 AND NOT EXISTS
2694 (SELECT null
2695 FROM fnd_application aa
2696 WHERE aa.application_id = df.application_id);
2697 x_message := SQL%rowcount || ' row(s) deleted.';
2698 GOTO return_success;
2699 EXCEPTION
2700 WHEN OTHERS THEN
2701 x_message :=
2702 'Unable to delete from FND_DESCRIPTIVE_FLEXS. ' || Sqlerrm;
2703 GOTO return_error;
2704 END;
2705 END IF;
2706
2707 IF (p_rule = 'D.01') THEN
2708 --
2709 -- Compiled definitions without DFF.
2710 --
2711 BEGIN
2712 DELETE
2713 FROM fnd_compiled_descriptive_flexs cdf
2714 WHERE application_id = p_application_id
2715 AND descriptive_flexfield_name = p_descriptive_flexfield_name
2716 AND NOT EXISTS
2717 (SELECT null
2718 FROM fnd_descriptive_flexs df
2719 WHERE df.application_id = cdf.application_id
2720 AND df.descriptive_flexfield_name =cdf.descriptive_flexfield_name);
2721 x_message := SQL%rowcount || ' row(s) deleted.';
2722 GOTO return_success;
2723 EXCEPTION
2724 WHEN OTHERS THEN
2725 x_message :=
2726 'Unable to delete from FND_COMPILED_DESCRIPTIVE_FLEXS. ' || Sqlerrm;
2727 GOTO return_error;
2728 END;
2729 END IF;
2730
2731 IF (NOT fetch_dff_flx(p_application_id,
2732 p_descriptive_flexfield_name,
2733 l_dff_flx,
2734 x_message)) THEN
2735 GOTO return_error;
2736 END IF ;
2737
2738 IF (p_rule = 'A.09') THEN
2739 --
2740 -- Context column is not registered properly.
2741 --
2742 IF (NOT fetch_tbl(l_dff_flx.table_application_id,
2743 l_dff_flx.application_table_name,
2744 l_tbl,
2745 x_message)) THEN
2746 GOTO return_error;
2747 END IF;
2748
2749 IF (NOT fetch_col(l_tbl,
2750 l_dff_flx.context_column_name,
2751 l_col,
2752 x_message)) THEN
2753 GOTO return_error;
2754 END IF;
2755
2756 IF (l_col.flexfield_application_id = l_dff_flx.application_id AND
2757 l_col.flexfield_name = l_dff_flx.descriptive_flexfield_name AND
2758 l_col.flexfield_usage_code = 'C') THEN
2759 x_message := 'Context column is properly registered. No need to fix.';
2760 GOTO return_error;
2761 END IF;
2762
2763 IF (update_fnd_columns(l_col,
2764 'C',
2765 l_dff_flx.application_id,
2766 l_dff_flx.descriptive_flexfield_name,
2767 x_message)) THEN
2768 GOTO return_success;
2769 ELSE
2770 GOTO return_error;
2771 END IF;
2772 END IF;
2773
2774 IF (p_rule = 'A.10') THEN
2775 --
2776 -- Global Context doesn't exist.
2777 --
2778 SELECT COUNT(*)
2779 INTO l_count
2780 FROM fnd_descr_flex_contexts
2781 WHERE application_id = l_dff_flx.application_id
2782 AND descriptive_flexfield_name = l_dff_flx.descriptive_flexfield_name
2783 AND global_flag = 'Y';
2784
2785 IF (l_count > 0) THEN
2786 x_message := ('There is at least one global context. You cannot ' ||
2787 'create another one.');
2788 GOTO return_error;
2789 END IF;
2790
2791 --
2792 -- At this point there are no global contexts.
2793 --
2794 IF (NOT fetch_dff_ctx(l_dff_flx,
2795 'Global Data Elements',
2796 l_dff_ctx,
2797 x_message)) THEN
2798 --
2799 -- GDE doesn't exist, insert it.
2800 --
2801 fnd_descr_flex_contexts_pkg.insert_row
2802 (x_rowid => l_rowid,
2803 x_application_id => l_dff_flx.application_id,
2804 x_descriptive_flexfield_name => l_dff_flx.descriptive_flexfield_name,
2805 x_descriptive_flex_context_cod => 'Global Data Elements',
2806 x_enabled_flag => 'Y',
2807 x_global_flag => 'Y',
2808 x_description => 'Global Data Elements Context',
2809 x_descriptive_flex_context_nam => 'Global Data Elements',
2810 x_creation_date => Sysdate,
2811 x_created_by => 1,
2812 x_last_update_date => Sysdate,
2813 x_last_updated_by => 1,
2814 x_last_update_login => 0);
2815 x_message := 'Global Data Elements Context is inserted.';
2816 GOTO return_success;
2817 ELSE
2818 --
2819 -- GDE exists but is not marked as global.
2820 --
2821 BEGIN
2822 UPDATE fnd_descr_flex_contexts SET
2823 global_flag = 'Y',
2824 last_update_date = Sysdate,
2825 last_updated_by = 1
2826 WHERE application_id = l_dff_flx.application_id
2827 AND descriptive_flexfield_name = l_dff_flx.descriptive_flexfield_name
2828 AND descriptive_flex_context_code = 'Global Data Elements';
2829 x_message := 'Global Data Elements context is marked as global.';
2830 GOTO return_success;
2831 EXCEPTION
2832 WHEN OTHERS THEN
2833 x_message :=
2834 'Unable to update FND_DESCR_FLEX_CONTEXTS. ' || Sqlerrm;
2835 GOTO return_error;
2836 END;
2837 END IF;
2838 END IF;
2839
2840 IF (p_rule = 'A.11') THEN
2841 --
2842 -- Default context value should exist and be enabled.
2843 --
2844 IF (l_dff_flx.default_context_value IS NULL) THEN
2845 x_message := 'Default context is already NULL. No need to fix.';
2846 GOTO return_error;
2847 END IF;
2848
2849 IF (fetch_dff_ctx(l_dff_flx,
2850 l_dff_flx.default_context_value,
2851 l_dff_ctx,
2852 x_message)) THEN
2853 IF (l_dff_ctx.enabled_flag = 'Y' AND
2854 l_dff_ctx.global_flag <> 'Y') THEN
2855 x_message := 'Non-global default context exists and is enabled. No need to fix.';
2856 GOTO return_error;
2857 END IF;
2858 END IF;
2859
2860 BEGIN
2861 UPDATE fnd_descriptive_flexs SET
2862 default_context_value = NULL,
2863 last_update_date = Sysdate,
2864 last_updated_by = 1
2865 WHERE application_id = l_dff_flx.application_id
2866 AND descriptive_flexfield_name = l_dff_flx.descriptive_flexfield_name;
2867 x_message := SQL%rowcount || ' row(s) updated.';
2868 GOTO return_success;
2869 EXCEPTION
2870 WHEN OTHERS THEN
2871 x_message := 'Unable to update FND_DESCRIPTIVE_FLEXS. ' || Sqlerrm;
2872 GOTO return_error;
2873 END;
2874 END IF;
2875
2876 IF (p_rule = 'A.12') THEN
2877 --
2878 -- Problems in flags.
2879 --
2880 IF (NOT lookup_code_exists('YES_NO',
2881 l_dff_flx.context_required_flag)) THEN
2882 l_dff_flx.context_required_flag := 'N';
2883 END IF;
2884
2885 IF (NOT lookup_code_exists('YES_NO',
2886 l_dff_flx.context_user_override_flag)) THEN
2887 l_dff_flx.context_user_override_flag := 'Y';
2888 END IF;
2889
2890 IF (NOT lookup_code_exists('YES_NO',
2891 l_dff_flx.freeze_flex_definition_flag)) THEN
2892 l_dff_flx.freeze_flex_definition_flag := 'Y';
2893 END IF;
2894
2895 IF (l_dff_flx.descriptive_flexfield_name LIKE '$SRS$.%') THEN
2896 l_dff_flx.protected_flag := 'S';
2897 ELSE
2898 IF (NOT lookup_code_exists('YES_NO',
2899 l_dff_flx.protected_flag)) THEN
2900 l_dff_flx.protected_flag := 'N';
2901 END IF;
2902 END IF;
2903
2904 IF ((Nvl(l_dff_flx.context_default_type, 'C')
2905 NOT IN ('C', 'F', 'P', 'S')) OR
2906 (l_dff_flx.context_default_type IS NOT NULL AND
2907 l_dff_flx.context_default_value IS NULL) OR
2908 (l_dff_flx.context_default_type IS NULL AND
2909 l_dff_flx.context_default_value IS NOT NULL)) THEN
2910 l_dff_flx.context_default_type := NULL;
2911 l_dff_flx.context_default_value := NULL;
2912 END IF;
2913
2914 BEGIN
2915 UPDATE fnd_descriptive_flexs SET
2916 context_required_flag = l_dff_flx.context_required_flag,
2917 context_user_override_flag = l_dff_flx.context_user_override_flag,
2918 freeze_flex_definition_flag = l_dff_flx.freeze_flex_definition_flag,
2919 protected_flag = l_dff_flx.protected_flag,
2920 context_default_type = l_dff_flx.context_default_type,
2921 context_default_value = l_dff_flx.context_default_value,
2922 last_update_date = Sysdate,
2923 last_updated_by = 1
2924 WHERE application_id = l_dff_flx.application_id
2925 AND descriptive_flexfield_name = l_dff_flx.descriptive_flexfield_name;
2926 x_message := SQL%rowcount || ' row(s) updated.';
2927 GOTO return_success;
2928 EXCEPTION
2929 WHEN OTHERS THEN
2930 x_message := 'Unable to update FND_DESCRIPTIVE_FLEXS. ' || Sqlerrm;
2931 GOTO return_error;
2932 END;
2933 END IF;
2934
2935 IF (p_rule = 'A.13') THEN
2936 --
2937 -- Problem with context override value set.
2938 --
2939 IF (l_dff_flx.context_override_value_set_id IS NULL) THEN
2940 x_message := 'No context override value set. No need to fix.';
2941 GOTO return_error;
2942 END IF;
2943
2944 IF (fetch_vst_set(l_dff_flx.context_override_value_set_id,
2945 l_vst_set,
2946 x_message)) THEN
2947 x_message := 'Override value set already exists. No need to fix.';
2948 GOTO return_error;
2949 END IF;
2950
2951 BEGIN
2952 UPDATE fnd_descriptive_flexs SET
2953 context_override_value_set_id = NULL,
2954 last_update_date = Sysdate,
2955 last_updated_by = 1
2956 WHERE application_id = l_dff_flx.application_id
2957 AND descriptive_flexfield_name = l_dff_flx.descriptive_flexfield_name;
2958 x_message := SQL%rowcount || ' row(s) updated.';
2959 GOTO return_success;
2960 EXCEPTION
2961 WHEN OTHERS THEN
2962 x_message := 'Unable to update FND_DESCRIPTIVE_FLEXS. ' || Sqlerrm;
2963 GOTO return_error;
2964 END;
2965 END IF;
2966
2967 <<return_success>>
2968 <<return_error>>
2969 RETURN;
2970 EXCEPTION
2971 WHEN OTHERS THEN
2972 x_message := 'fix_dff_flx: Top level error: ' || Sqlerrm;
2973 END fix_dff_flx;
2974 -- ===========================================================================
2975 PROCEDURE fix_dff_ref(p_rule IN VARCHAR2,
2976 p_application_id IN NUMBER,
2977 p_descriptive_flexfield_name IN VARCHAR2,
2978 p_default_context_field_name IN VARCHAR2,
2979 x_message OUT nocopy VARCHAR2)
2980 IS
2981 BEGIN
2982 IF (p_rule = 'B.01') THEN
2983 --
2984 -- Reference fields without DFF.
2985 --
2986 BEGIN
2987 DELETE
2988 FROM fnd_default_context_fields dcf
2989 WHERE application_id = p_application_id
2990 AND descriptive_flexfield_name = p_descriptive_flexfield_name
2991 AND default_context_field_name = p_default_context_field_name
2992 AND NOT EXISTS
2993 (SELECT null
2994 FROM fnd_descriptive_flexs df
2995 WHERE df.application_id = dcf.application_id
2996 AND df.descriptive_flexfield_name =dcf.descriptive_flexfield_name);
2997 x_message := SQL%rowcount || ' row(s) deleted.';
2998 GOTO return_success;
2999 EXCEPTION
3000 WHEN OTHERS THEN
3001 x_message :=
3002 'Unable to delete from FND_DEFAULT_CONTEXT_FIELDS. ' || Sqlerrm;
3003 GOTO return_error;
3004 END;
3005 END IF;
3006
3007 <<return_success>>
3008 <<return_error>>
3009 RETURN;
3010 EXCEPTION
3011 WHEN OTHERS THEN
3012 x_message := 'fix_dff_ref: Top level error: ' || Sqlerrm;
3013 END fix_dff_ref;
3014 -- ===========================================================================
3015 PROCEDURE fix_dff_ctx(p_rule IN VARCHAR2,
3016 p_application_id IN NUMBER,
3017 p_descriptive_flexfield_name IN VARCHAR2,
3018 p_descriptive_flex_context_cod IN VARCHAR2,
3019 x_message OUT nocopy VARCHAR2)
3020 IS
3021 l_dff_flx dff_flx_type;
3022 l_dff_ctx dff_ctx_type;
3023 l_count NUMBER;
3024 BEGIN
3025 IF (p_rule = 'C.03') THEN
3026 --
3027 -- Contexts without DFF.
3028 --
3029 BEGIN
3030 DELETE
3031 FROM fnd_descr_flex_contexts dfc
3032 WHERE application_id = p_application_id
3033 AND descriptive_flexfield_name = p_descriptive_flexfield_name
3034 AND descriptive_flex_context_code = p_descriptive_flex_context_cod
3035 AND NOT EXISTS
3036 (SELECT null
3037 FROM fnd_descriptive_flexs df
3038 WHERE df.application_id = dfc.application_id
3039 AND df.descriptive_flexfield_name =dfc.descriptive_flexfield_name);
3040 x_message := SQL%rowcount || ' row(s) deleted.';
3041 GOTO return_success;
3042 EXCEPTION
3043 WHEN OTHERS THEN
3044 x_message :=
3045 'Unable to delete from FND_DESCR_FLEX_CONTEXTS. ' || Sqlerrm;
3046 GOTO return_error;
3047 END;
3048 END IF;
3049
3050 IF (NOT fetch_dff_flx(p_application_id,
3051 p_descriptive_flexfield_name,
3052 l_dff_flx,
3053 x_message)) THEN
3054 GOTO return_error;
3055 END IF ;
3056
3057 IF (NOT fetch_dff_ctx(l_dff_flx,
3058 p_descriptive_flex_context_cod,
3059 l_dff_ctx,
3060 x_message)) THEN
3061 GOTO return_error;
3062 END IF ;
3063
3064 IF (p_rule = 'C.04') THEN
3065 --
3066 -- There are more than one global context.
3067 --
3068 SELECT COUNT(*)
3069 INTO l_count
3070 FROM fnd_descr_flex_contexts
3071 WHERE application_id = l_dff_ctx.application_id
3072 AND descriptive_flexfield_name = l_dff_ctx.descriptive_flexfield_name
3073 AND global_flag = 'Y';
3074
3075 IF (l_count <= 1) THEN
3076 x_message := 'There is only one global context. No need to fix.';
3077 GOTO return_error;
3078 END IF;
3079
3080 IF (l_dff_ctx.global_flag = 'N') THEN
3081 x_message := 'This is not a global context.';
3082 GOTO return_error;
3083 END IF;
3084
3085 BEGIN
3086 UPDATE fnd_descr_flex_contexts SET
3087 global_flag = 'N',
3088 last_update_date = Sysdate,
3089 last_updated_by = 1
3090 WHERE application_id = l_dff_ctx.application_id
3091 AND descriptive_flexfield_name = l_dff_ctx.descriptive_flexfield_name
3092 AND descriptive_flex_context_code = l_dff_ctx.descriptive_flex_context_code;
3093
3094 x_message := SQL%rowcount || ' row(s) updated.';
3095 GOTO return_success;
3096 EXCEPTION
3097 WHEN OTHERS THEN
3098 x_message :=
3099 'Unable to update FND_DESCR_FLEX_CONTEXTS. ' || Sqlerrm;
3100 GOTO return_error;
3101 END;
3102 END IF;
3103
3104 IF (p_rule = 'C.05') THEN
3105 --
3106 -- Global context should be enabled.
3107 --
3108 IF (l_dff_ctx.global_flag = 'N') THEN
3109 x_message := 'This is not a global context.';
3110 GOTO return_error;
3111 END IF;
3112
3113 IF (l_dff_ctx.enabled_flag = 'Y') THEN
3114 x_message := 'Global context is already enabled. No need to fix.';
3115 GOTO return_error;
3116 END IF;
3117
3118 BEGIN
3119 UPDATE fnd_descr_flex_contexts SET
3120 enabled_flag = 'Y',
3121 last_update_date = Sysdate,
3122 last_updated_by = 1
3123 WHERE application_id = l_dff_ctx.application_id
3124 AND descriptive_flexfield_name = l_dff_ctx.descriptive_flexfield_name
3125 AND descriptive_flex_context_code = l_dff_ctx.descriptive_flex_context_code;
3126
3127 x_message := SQL%rowcount || ' row(s) updated.';
3128 GOTO return_success;
3129 EXCEPTION
3130 WHEN OTHERS THEN
3131 x_message :=
3132 'Unable to update FND_DESCR_FLEX_CONTEXTS. ' || Sqlerrm;
3133 GOTO return_error;
3134 END;
3135 END IF;
3136
3137 IF (p_rule = 'C.06') THEN
3138 --
3139 -- Problem in flags.
3140 --
3141 IF (NOT lookup_code_exists('YES_NO',
3142 l_dff_ctx.global_flag)) THEN
3143 l_dff_ctx.global_flag := 'N';
3144 END IF;
3145
3146 IF (NOT lookup_code_exists('YES_NO',
3147 l_dff_ctx.enabled_flag)) THEN
3148 l_dff_ctx.enabled_flag := 'N';
3149 END IF;
3150
3151 BEGIN
3152 UPDATE fnd_descr_flex_contexts SET
3153 global_flag = l_dff_ctx.global_flag,
3154 enabled_flag = l_dff_ctx.enabled_flag,
3155 last_update_date = Sysdate,
3156 last_updated_by = 1
3157 WHERE application_id = l_dff_ctx.application_id
3158 AND descriptive_flexfield_name = l_dff_ctx.descriptive_flexfield_name
3159 AND descriptive_flex_context_code = l_dff_ctx.descriptive_flex_context_code;
3160 x_message := SQL%rowcount || ' row(s) updated.';
3161 GOTO return_success;
3162 EXCEPTION
3163 WHEN OTHERS THEN
3164 x_message :=
3165 'Unable to update FND_DESCR_FLEX_CONTEXTS. ' || Sqlerrm;
3166 GOTO return_error;
3167 END;
3168 END IF;
3169
3170 <<return_success>>
3171 <<return_error>>
3172 RETURN;
3173 EXCEPTION
3174 WHEN OTHERS THEN
3175 x_message := 'fix_dff_ctx: Top level error: ' || Sqlerrm;
3176 END fix_dff_ctx;
3177 -- ===========================================================================
3178 PROCEDURE fix_dff_seg(p_rule IN VARCHAR2,
3179 p_application_id IN NUMBER,
3180 p_descriptive_flexfield_name IN VARCHAR2,
3181 p_descriptive_flex_context_cod IN VARCHAR2,
3182 p_application_column_name IN VARCHAR2,
3183 x_message OUT nocopy VARCHAR2)
3184 IS
3185 l_tbl tbl_type;
3186 l_col col_type;
3187 l_vst_set vst_set_type;
3188 l_dff_flx dff_flx_type;
3189 l_dff_ctx dff_ctx_type;
3190 l_dff_seg dff_seg_type;
3191 BEGIN
3192 IF (p_rule = 'E.03') THEN
3193 --
3194 -- Segments without DFF Context.
3195 --
3196 BEGIN
3197 DELETE
3198 FROM fnd_descr_flex_column_usages dfcu
3199 WHERE application_id = p_application_id
3200 AND descriptive_flexfield_name = p_descriptive_flexfield_name
3201 AND descriptive_flex_context_code = p_descriptive_flex_context_cod
3202 AND application_column_name = p_application_column_name
3203 AND NOT EXISTS
3204 (SELECT null
3205 FROM fnd_descr_flex_contexts dfc
3206 WHERE dfc.application_id = dfcu.application_id
3207 AND dfc.descriptive_flexfield_name =dfcu.descriptive_flexfield_name
3208 AND dfc.descriptive_flex_context_code = dfcu.descriptive_flex_context_code);
3209 x_message := SQL%rowcount || ' row(s) deleted.';
3210 GOTO return_success;
3211 EXCEPTION
3212 WHEN OTHERS THEN
3213 x_message :=
3214 'Unable to delete from FND_DESCR_FLEX_COLUMN_USAGES. ' ||Sqlerrm;
3215 GOTO return_error;
3216 END;
3217 END IF;
3218
3219 IF (NOT fetch_dff_flx(p_application_id,
3220 p_descriptive_flexfield_name,
3221 l_dff_flx,
3222 x_message)) THEN
3223 GOTO return_error;
3224 END IF ;
3225
3226 IF (NOT fetch_dff_ctx(l_dff_flx,
3227 p_descriptive_flex_context_cod,
3228 l_dff_ctx,
3229 x_message)) THEN
3230 GOTO return_error;
3231 END IF ;
3232
3233 IF (NOT fetch_dff_seg(l_dff_ctx,
3234 p_application_column_name,
3235 l_dff_seg,
3236 x_message)) THEN
3237 GOTO return_error;
3238 END IF;
3239
3240 IF (p_rule = 'E.06') THEN
3241 --
3242 -- Segment column is not registered properly.
3243 --
3244 IF (NOT fetch_tbl(l_dff_flx.table_application_id,
3245 l_dff_flx.application_table_name,
3246 l_tbl,
3247 x_message)) THEN
3248 GOTO return_error;
3249 END IF;
3250
3251 IF (NOT fetch_col(l_tbl,
3252 l_dff_seg.application_column_name,
3253 l_col,
3254 x_message)) THEN
3255 GOTO return_error;
3256 END IF;
3257
3258 IF (l_col.flexfield_application_id = l_dff_flx.application_id AND
3259 l_col.flexfield_name = l_dff_flx.descriptive_flexfield_name AND
3260 l_col.flexfield_usage_code = 'D') THEN
3261 x_message := 'Segment column is registered properly. No need to fix.';
3262 GOTO return_error;
3263 END IF;
3264
3265 IF (update_fnd_columns(l_col,
3266 'D',
3267 l_dff_flx.application_id,
3268 l_dff_flx.descriptive_flexfield_name,
3269 x_message)) THEN
3270 GOTO return_success;
3271 ELSE
3272 GOTO return_error;
3273 END IF;
3274 END IF;
3275
3276 IF (p_rule = 'E.07') THEN
3277 --
3278 -- Non-existing value set is used.
3279 --
3280 IF (l_dff_seg.flex_value_set_id IS NULL) THEN
3281 x_message := 'No value set is used in this segment. No need to fix.';
3282 GOTO return_error;
3283 END IF;
3284
3285 IF (fetch_vst_set(l_dff_seg.flex_value_set_id,
3286 l_vst_set,
3287 x_message)) THEN
3288 x_message := 'Value set already exists. No need to fix.';
3289 GOTO return_error;
3290 END IF;
3291
3292 BEGIN
3293 UPDATE fnd_descr_flex_column_usages SET
3294 flex_value_set_id = NULL,
3295 last_update_date = Sysdate,
3296 last_updated_by = 1
3297 WHERE application_id = l_dff_seg.application_id
3298 AND descriptive_flexfield_name = l_dff_seg.descriptive_flexfield_name
3299 AND descriptive_flex_context_code = l_dff_seg.descriptive_flex_context_code
3300 AND application_column_name = l_dff_seg.application_column_name;
3301 x_message := SQL%rowcount || ' row(s) updated.';
3302 GOTO return_success;
3303 EXCEPTION
3304 WHEN OTHERS THEN
3305 x_message :=
3306 'Unable to update FND_DESCR_FLEX_COLUMN_USAGES. ' || Sqlerrm;
3307 GOTO return_error;
3308 END;
3309 END IF;
3310
3311 IF (p_rule = 'E.08') THEN
3312 --
3313 -- Problem in flags.
3314 --
3315 IF (NOT lookup_code_exists('FLEX_DEFAULT_TYPE',
3316 l_dff_seg.default_type)) THEN
3317 l_dff_seg.default_type := NULL;
3318 END IF;
3319
3320 IF (NOT lookup_code_exists('YES_NO',
3321 l_dff_seg.enabled_flag)) THEN
3322 l_dff_seg.enabled_flag := 'N';
3323 END IF;
3324
3325 IF (NOT lookup_code_exists('YES_NO',
3326 l_dff_seg.display_flag)) THEN
3327 l_dff_seg.display_flag := 'N';
3328 END IF;
3329
3330 IF (NOT lookup_code_exists('RANGE_CODES',
3331 l_dff_seg.range_code)) THEN
3332 l_dff_seg.range_code := NULL;
3333 END IF;
3334
3335 IF (NOT lookup_code_exists('YES_NO',
3336 l_dff_seg.required_flag)) THEN
3337 l_dff_seg.required_flag := 'N';
3338 END IF;
3339
3340 IF (NOT lookup_code_exists('YES_NO',
3341 l_dff_seg.security_enabled_flag)) THEN
3342 l_dff_seg.security_enabled_flag := 'N';
3343 END IF;
3344
3345 BEGIN
3346 UPDATE fnd_descr_flex_column_usages SET
3347 default_type = l_dff_seg.default_type,
3348 enabled_flag = l_dff_seg.enabled_flag,
3349 display_flag = l_dff_seg.display_flag,
3350 range_code = l_dff_seg.range_code,
3351 required_flag = l_dff_seg.required_flag,
3352 security_enabled_flag = l_dff_seg.security_enabled_flag,
3353 last_update_date = Sysdate,
3354 last_updated_by = 1
3355 WHERE application_id = l_dff_seg.application_id
3356 AND descriptive_flexfield_name = l_dff_seg.descriptive_flexfield_name
3357 AND descriptive_flex_context_code = l_dff_seg.descriptive_flex_context_code
3358 AND application_column_name = l_dff_seg.application_column_name;
3359 x_message := SQL%rowcount || ' row(s) updated.';
3360 GOTO return_success;
3361 EXCEPTION
3362 WHEN OTHERS THEN
3363 x_message :=
3364 'Unable to update FND_DESCR_FLEX_COLUMN_USAGES. ' || Sqlerrm;
3365 GOTO return_error;
3366 END;
3367 END IF;
3368
3369 <<return_success>>
3370 <<return_error>>
3371 RETURN;
3372 EXCEPTION
3373 WHEN OTHERS THEN
3374 x_message := 'fix_dff_seg: Top level error: ' || Sqlerrm;
3375 END fix_dff_seg;
3376 -- ===========================================================================
3377 PROCEDURE fix_dff_col(p_rule IN VARCHAR2,
3378 p_application_id IN NUMBER,
3379 p_table_name IN VARCHAR2,
3380 p_column_name IN VARCHAR2,
3381 x_message OUT nocopy VARCHAR2)
3382 IS
3383 l_tbl tbl_type;
3384 l_col col_type;
3385 l_dff_flx dff_flx_type;
3386 l_count NUMBER;
3387 BEGIN
3388 IF (NOT fetch_tbl(p_application_id,
3389 p_table_name,
3390 l_tbl,
3391 x_message)) THEN
3392 GOTO return_error;
3393 END IF;
3394
3395 IF (l_tbl.table_name = 'FND_SRS_MASTER') THEN
3396 x_message := 'No change for FND_SRS_MASTER table columns.';
3397 GOTO return_error;
3398 END IF;
3399
3400 IF (NOT fetch_col(l_tbl,
3401 p_column_name,
3402 l_col,
3403 x_message)) THEN
3404 GOTO return_error;
3405 END IF;
3406
3407 IF (p_rule = 'F.01') THEN
3408 --
3409 -- 'C' columns.
3410 --
3411 IF (l_col.flexfield_usage_code <> 'C') THEN
3412 x_message := 'This is not a ''C'' column.';
3413 GOTO return_error;
3414 END IF;
3415
3416 IF (fetch_dff_flx(l_col.flexfield_application_id,
3417 l_col.flexfield_name,
3418 l_dff_flx,
3419 x_message)) THEN
3420 IF (l_dff_flx.table_application_id = l_tbl.application_id AND
3421 l_dff_flx.application_table_name = l_tbl.table_name AND
3422 l_dff_flx.context_column_name = l_col.column_name) THEN
3423 x_message := ('This column is used by ' ||
3424 To_char(l_dff_flx.application_id) || '/' ||
3425 l_dff_flx.descriptive_flexfield_name ||
3426 '. No need to fix.');
3427 GOTO return_error;
3428 END IF;
3429 END IF;
3430
3431 IF (update_fnd_columns(l_col, 'N', NULL, NULL, x_message)) THEN
3432 GOTO return_success;
3433 ELSE
3434 GOTO return_error;
3435 END IF;
3436 END IF;
3437
3438 IF (p_rule = 'F.02') THEN
3439 --
3440 -- 'D' columns.
3441 --
3442 IF (l_col.flexfield_usage_code <> 'D') THEN
3443 x_message := 'This is not a ''D'' column.';
3444 GOTO return_error;
3445 END IF;
3446
3447 IF (fetch_dff_flx(l_col.flexfield_application_id,
3448 l_col.flexfield_name,
3449 l_dff_flx,
3450 x_message)) THEN
3451 IF (l_dff_flx.table_application_id = l_tbl.application_id AND
3452 l_dff_flx.application_table_name = l_tbl.table_name) THEN
3453 x_message := ('This column is possibly used by ' ||
3454 To_char(l_dff_flx.application_id) || '/' ||
3455 l_dff_flx.descriptive_flexfield_name ||
3456 '. No need to fix.');
3457 GOTO return_error;
3458 END IF;
3459 END IF;
3460
3461 IF (update_fnd_columns(l_col, 'N', NULL, NULL, x_message)) THEN
3462 GOTO return_success;
3463 ELSE
3464 GOTO return_error;
3465 END IF;
3466 END IF;
3467
3468 <<return_success>>
3469 <<return_error>>
3470 RETURN;
3471 EXCEPTION
3472 WHEN OTHERS THEN
3473 x_message := 'fix_dff_col: Top level error: ' || Sqlerrm;
3474 END fix_dff_col;
3475
3476 -- ***************************************************************************
3477 -- * KFF get_kff_something() RETURN VARCHAR2; functions.
3478 -- ***************************************************************************
3479 -- ===========================================================================
3480 FUNCTION get_kff_flx(p_application_id IN NUMBER,
3481 p_id_flex_code IN VARCHAR2)
3482 RETURN VARCHAR2
3483 IS
3484 l_return VARCHAR2(2000);
3485 BEGIN
3486 SELECT id_flex_code || '/' ||
3487 id_flex_name || '/' ||
3488 Nvl(description, '<NULL>')
3489 INTO l_return
3490 FROM fnd_id_flexs
3491 WHERE application_id = p_application_id
3492 AND id_flex_code = p_id_flex_code;
3493 RETURN(line_return(l_return));
3494 EXCEPTION
3495 WHEN no_data_found THEN
3496 RETURN(line_return(p_id_flex_code ||
3497 '/get_kff_flx: no data found.'));
3498 WHEN OTHERS THEN
3499 RETURN(line_return(p_id_flex_code ||
3500 '/get_kff_flx SQLCODE:' || To_char(SQLCODE)));
3501 END get_kff_flx;
3502 -- ===========================================================================
3503 FUNCTION get_kff_str(p_application_id IN NUMBER,
3504 p_id_flex_code IN VARCHAR2,
3505 p_id_flex_num IN NUMBER)
3506 RETURN VARCHAR2
3507 IS
3508 l_return VARCHAR2(2000);
3509 BEGIN
3510 SELECT id_flex_num || '/' ||
3511 enabled_flag || '/' ||
3512 freeze_flex_definition_flag || '/' ||
3513 concatenated_segment_delimiter || '/' ||
3514 id_flex_structure_name
3515 INTO l_return
3516 FROM fnd_id_flex_structures_vl
3517 WHERE application_id = p_application_id
3518 AND id_flex_code = p_id_flex_code
3519 AND id_flex_num = p_id_flex_num;
3520 RETURN(line_return(l_return));
3521 EXCEPTION
3522 WHEN no_data_found THEN
3523 RETURN(line_return(p_id_flex_num ||
3524 '/get_kff_str: no data found.'));
3525 WHEN OTHERS THEN
3526 RETURN(line_return(p_id_flex_num ||
3527 '/get_kff_str SQLCODE:' || To_char(SQLCODE)));
3528 END get_kff_str;
3529 -- ===========================================================================
3530 FUNCTION get_kff_seg(p_application_id IN NUMBER,
3531 p_id_flex_code IN VARCHAR2,
3532 p_id_flex_num IN NUMBER,
3533 p_application_column_name IN VARCHAR2)
3534 RETURN VARCHAR2
3535 IS
3536 l_return VARCHAR2(2000);
3537 BEGIN
3538 SELECT application_column_name || '/' ||
3539 enabled_flag || '/' ||
3540 display_flag || '/' ||
3541 segment_name || '/' ||
3542 form_left_prompt || '/' ||
3543 Nvl(description, '<NULL>')
3544 INTO l_return
3545 FROM fnd_id_flex_segments_vl
3546 WHERE application_id = p_application_id
3547 AND id_flex_code = p_id_flex_code
3548 AND id_flex_num = p_id_flex_num
3549 AND application_column_name = p_application_column_name;
3550 RETURN(line_return(l_return));
3551 EXCEPTION
3552 WHEN no_data_found THEN
3553 RETURN(line_return(p_application_column_name ||
3554 '/get_kff_seg: no data found.'));
3555 WHEN OTHERS THEN
3556 RETURN(line_return(p_application_column_name ||
3557 '/get_kff_seg SQLCODE:' || To_char(SQLCODE)));
3558 END get_kff_seg;
3559 -- ===========================================================================
3560 FUNCTION get_kff_sha(p_application_id IN NUMBER,
3561 p_id_flex_code IN VARCHAR2,
3562 p_id_flex_num IN NUMBER,
3563 p_alias_name IN VARCHAR2)
3564 RETURN VARCHAR2
3565 IS
3566 l_return VARCHAR2(32000);
3567 BEGIN
3568 SELECT alias_name || '/' || enabled_flag || '/' ||
3569 concatenated_segments || '/' || Nvl(description, '<NULL>')
3570 INTO l_return
3571 FROM fnd_shorthand_flex_aliases
3572 WHERE application_id = p_application_id
3573 AND id_flex_code = p_id_flex_code
3574 AND id_flex_num = p_id_flex_num
3575 AND alias_name = p_alias_name;
3576 RETURN(line_return(l_return));
3577 EXCEPTION
3578 WHEN no_data_found THEN
3579 RETURN(line_return(p_alias_name ||
3580 '/get_kff_sha: no data found.'));
3581 WHEN OTHERS THEN
3582 RETURN(line_return(p_alias_name ||
3583 '/get_kff_sha SQLCODE:' || To_char(SQLCODE)));
3584 END get_kff_sha;
3585 -- ===========================================================================
3586 FUNCTION get_kff_cvr(p_application_id IN NUMBER,
3587 p_id_flex_code IN VARCHAR2,
3588 p_id_flex_num IN NUMBER,
3589 p_flex_validation_rule_name IN VARCHAR2)
3590 RETURN VARCHAR2
3591 IS
3592 l_return VARCHAR2(32000);
3593 BEGIN
3594 SELECT flex_validation_rule_name || '/' || enabled_flag || '/' ||
3595 error_message_text || '/' || Nvl(description, '<NULL>')
3596 INTO l_return
3597 FROM fnd_flex_vdation_rules_vl
3598 WHERE application_id = p_application_id
3599 AND id_flex_code = p_id_flex_code
3600 AND id_flex_num = p_id_flex_num
3601 AND flex_validation_rule_name = p_flex_validation_rule_name;
3602 RETURN(line_return(l_return));
3603 EXCEPTION
3604 WHEN no_data_found THEN
3605 RETURN(line_return(p_flex_validation_rule_name ||
3606 '/get_kff_cvr: no data found.'));
3607 WHEN OTHERS THEN
3608 RETURN(line_return(p_flex_validation_rule_name ||
3609 '/get_kff_cvr SQLCODE:' || To_char(SQLCODE)));
3610 END get_kff_cvr;
3611 -- ===========================================================================
3612 FUNCTION get_kff_cvl(p_application_id IN NUMBER,
3613 p_id_flex_code IN VARCHAR2,
3614 p_id_flex_num IN NUMBER,
3615 p_flex_validation_rule_name IN VARCHAR2,
3616 p_rule_line_id IN NUMBER)
3617 RETURN VARCHAR2
3618 IS
3619 l_return VARCHAR2(32000);
3620 BEGIN
3621 SELECT rule_line_id || '/' || enabled_flag || '/' ||
3622 include_exclude_indicator || '/' ||
3623 concatenated_segments_low || '/' ||
3624 concatenated_segments_high
3625 INTO l_return
3626 FROM fnd_flex_validation_rule_lines
3627 WHERE application_id = p_application_id
3628 AND id_flex_code = p_id_flex_code
3629 AND id_flex_num = p_id_flex_num
3630 AND flex_validation_rule_name = p_flex_validation_rule_name
3631 AND rule_line_id = p_rule_line_id;
3632 RETURN(line_return(l_return));
3633 EXCEPTION
3634 WHEN no_data_found THEN
3635 RETURN(line_return(p_rule_line_id ||
3636 '/get_kff_cvl: no data found.'));
3637 WHEN OTHERS THEN
3638 RETURN(line_return(p_rule_line_id ||
3639 '/get_kff_cvl SQLCODE:' || To_char(SQLCODE)));
3640 END get_kff_cvl;
3641 -- ===========================================================================
3642 FUNCTION get_kff_flq(p_application_id IN NUMBER,
3643 p_id_flex_code IN VARCHAR2,
3644 p_segment_attribute_type IN VARCHAR2)
3645 RETURN VARCHAR2
3646 IS
3647 l_return VARCHAR2(32000);
3648 BEGIN
3649 SELECT segment_attribute_type || '/' ||
3650 global_flag || '/' || required_flag || '/' || unique_flag || '/' ||
3651 segment_prompt
3652 INTO l_return
3653 FROM fnd_segment_attribute_types
3654 WHERE application_id = p_application_id
3655 AND id_flex_code = p_id_flex_code
3656 AND segment_attribute_type = p_segment_attribute_type;
3657 RETURN(line_return(l_return));
3658 EXCEPTION
3659 WHEN no_data_found THEN
3660 RETURN(line_return(p_segment_attribute_type ||
3661 '/get_kff_flq: no data found.'));
3662 WHEN OTHERS THEN
3663 RETURN(line_return(p_segment_attribute_type ||
3664 '/get_kff_flq SQLCODE:' || To_char(SQLCODE)));
3665 END get_kff_flq;
3666 -- ===========================================================================
3667 FUNCTION get_kff_sgq(p_application_id IN NUMBER,
3668 p_id_flex_code IN VARCHAR2,
3669 p_segment_attribute_type IN VARCHAR2,
3670 p_value_attribute_type IN VARCHAR2)
3671 RETURN VARCHAR2
3672 IS
3673 l_return VARCHAR2(32000);
3674 BEGIN
3675 SELECT value_attribute_type || '/' || application_column_name || '/' ||
3676 lookup_type || '/' || default_value || '/' || prompt
3677 INTO l_return
3678 FROM fnd_val_attribute_types_vl
3679 WHERE application_id = p_application_id
3680 AND id_flex_code = p_id_flex_code
3681 AND segment_attribute_type = p_segment_attribute_type
3682 AND value_attribute_type = p_value_attribute_type;
3683 RETURN(line_return(l_return));
3684 EXCEPTION
3685 WHEN no_data_found THEN
3686 RETURN(line_return(p_value_attribute_type ||
3687 '/get_kff_sgq: no data found.'));
3688 WHEN OTHERS THEN
3689 RETURN(line_return(p_value_attribute_type ||
3690 '/get_kff_sgq SQLCODE:' || To_char(SQLCODE)));
3691 END get_kff_sgq;
3692 -- ===========================================================================
3693 FUNCTION get_kff_tap(p_application_id IN NUMBER,
3694 p_id_flex_code IN VARCHAR2)
3695 RETURN VARCHAR2
3696 IS
3697 l_return VARCHAR2(2000);
3698 BEGIN
3699 SELECT To_char(avl.application_id) || '/' ||
3700 avl.application_short_name || '/' ||
3701 avl.application_name
3702 INTO l_return
3703 FROM fnd_application_vl avl, fnd_id_flexs idf
3704 WHERE avl.application_id = idf.table_application_id
3705 AND idf.application_id = p_application_id
3706 AND idf.id_flex_code = p_id_flex_code;
3707 RETURN(line_return(l_return));
3708 EXCEPTION
3709 WHEN no_data_found THEN
3710 RETURN(line_return('get_kff_tap: no data found.'));
3711 WHEN OTHERS THEN
3712 RETURN(line_return('get_kff_tap SQLCODE:' || To_char(SQLCODE)));
3713 END get_kff_tap;
3714 -- ===========================================================================
3715 FUNCTION get_kff_tbl(p_application_id IN NUMBER,
3716 p_id_flex_code IN VARCHAR2)
3717 RETURN VARCHAR2
3718 IS
3719 l_return VARCHAR2(2000);
3720 BEGIN
3721 SELECT us.table_owner || '/' ||
3722 ft.table_name || '/' ||
3723 To_char(table_id)
3724 INTO l_return
3725 FROM user_synonyms us, fnd_tables ft, fnd_id_flexs idf
3726 WHERE us.synonym_name = idf.application_table_name
3727 AND ft.application_id = idf.table_application_id
3728 AND ft.table_name = idf.application_table_name
3729 AND idf.application_id = p_application_id
3730 AND idf.id_flex_code = p_id_flex_code;
3731 RETURN(line_return(l_return));
3732 EXCEPTION
3733 WHEN no_data_found THEN
3734 RETURN(line_return('get_kff_tbl: no data found.'));
3735 WHEN OTHERS THEN
3736 RETURN(line_return('get_kff_tbl SQLCODE:' || To_char(SQLCODE)));
3737 END get_kff_tbl;
3738 -- ===========================================================================
3739 FUNCTION get_kff_col(p_application_id IN NUMBER,
3740 p_id_flex_code IN VARCHAR2,
3741 p_application_column_name IN VARCHAR2)
3742 RETURN VARCHAR2
3743 IS
3744 l_return VARCHAR2(2000);
3745 BEGIN
3746 SELECT fc.column_name || '/' ||
3747 To_char(fc.column_id) || '/' ||
3748 fc.column_type || '/' ||
3749 To_char(fc.width) || '/' ||
3750 fc.flexfield_usage_code || '/' ||
3751 Nvl(To_char(fc.flexfield_application_id), '<NULL>') || '/' ||
3752 Nvl(fc.flexfield_name, '<NULL>')
3753 INTO l_return
3754 FROM fnd_columns fc, fnd_tables ft, fnd_id_flexs idf
3755 WHERE idf.application_id = p_application_id
3756 AND idf.id_flex_code = p_id_flex_code
3757 AND ft.application_id = idf.table_application_id
3758 AND ft.table_name = idf.application_table_name
3759 AND fc.application_id = ft.application_id
3760 AND fc.table_id = ft.table_id
3761 AND fc.column_name = p_application_column_name;
3762 RETURN(line_return(l_return));
3763 EXCEPTION
3764 WHEN no_data_found THEN
3765 RETURN(line_return('get_kff_col: no data found.'));
3766 WHEN OTHERS THEN
3767 RETURN(line_return('get_kff_col SQLCODE:' || To_char(SQLCODE)));
3768 END get_kff_col;
3769 -- ===========================================================================
3770 FUNCTION get_kff_fwp(p_application_id IN NUMBER,
3771 p_id_flex_code IN VARCHAR2,
3772 p_id_flex_num IN NUMBER,
3773 p_wf_item_type IN VARCHAR2)
3774 RETURN VARCHAR2
3775 IS
3776 l_return VARCHAR2(2000);
3777 BEGIN
3778 SELECT wf_item_type || '/' || wf_process_name
3779 INTO l_return
3780 FROM fnd_flex_workflow_processes fwp
3781 WHERE application_id = p_application_id
3782 AND id_flex_code = p_id_flex_code
3783 AND id_flex_num = p_id_flex_num
3784 AND wf_item_type = p_wf_item_type;
3785 RETURN(line_return(l_return));
3786 EXCEPTION
3787 WHEN no_data_found THEN
3788 RETURN(line_return(p_wf_item_type ||
3789 '/get_kff_fwp: no data found.'));
3790 WHEN OTHERS THEN
3791 RETURN(line_return(p_wf_item_type ||
3792 '/get_kff_fwp SQLCODE:' || To_char(SQLCODE)));
3793 END get_kff_fwp;
3794 -- ===========================================================================
3795 FUNCTION get_kff_fix(p_rule IN VARCHAR2,
3796 p_pk1 IN VARCHAR2 DEFAULT NULL,
3797 p_pk2 IN VARCHAR2 DEFAULT NULL,
3798 p_pk3 IN VARCHAR2 DEFAULT NULL,
3799 p_pk4 IN VARCHAR2 DEFAULT NULL,
3800 p_pk5 IN VARCHAR2 DEFAULT NULL,
3801 p_pk6 IN VARCHAR2 DEFAULT NULL)
3802 RETURN VARCHAR2
3803 IS
3804 l_beg VARCHAR2(100);
3805 l_mid VARCHAR2(2000) := NULL;
3806 l_end VARCHAR2(100);
3807 l_cn VARCHAR2(100) := ',' || g_newline;
3808 BEGIN
3809 l_beg := (g_newline ||
3810 'variable msg VARCHAR2(2000);' || g_newline ||
3811 'BEGIN' || g_newline ||
3812 ' fnd_flex_diagnose.');
3813 IF (p_rule IN ('A.01', 'A.07', 'A.10', 'A.11', 'A.12', 'D.01')) THEN
3814 l_mid :=
3815 'fix_kff_flx' || g_newline ||
3816 ' (p_rule => ''' || p_rule || '''' || l_cn ||
3817 ' p_application_id => ' || p_pk1 || l_cn ||
3818 ' p_id_flex_code => ''' || p_pk2 || '''' || l_cn;
3819 END IF;
3820 IF (p_rule IN ('B.03', 'B.04', 'E.01', 'E.02', 'K.01', 'K.02')) THEN
3821 l_mid :=
3822 'fix_kff_str' || g_newline ||
3823 ' (p_rule => ''' || p_rule || '''' || l_cn ||
3824 ' p_application_id => ' || p_pk1 || l_cn ||
3825 ' p_id_flex_code => ''' || p_pk2 || '''' || l_cn ||
3826 ' p_id_flex_num => ' || p_pk3 || l_cn;
3827 END IF;
3828 IF (p_rule IN ('C.03', 'C.06', 'C.07', 'C.08')) THEN
3829 l_mid :=
3830 'fix_kff_seg' || g_newline ||
3831 ' (p_rule => ''' || p_rule || '''' || l_cn ||
3832 ' p_application_id => ' || p_pk1 || l_cn ||
3833 ' p_id_flex_code => ''' || p_pk2 || '''' || l_cn ||
3834 ' p_id_flex_num => ' || p_pk3 || l_cn ||
3835 ' p_application_column_name => ''' || p_pk4 || '''' || l_cn;
3836 END IF;
3837 IF (p_rule IN ('F.01', 'F.02')) THEN
3838 l_mid :=
3839 'fix_kff_sha' || g_newline ||
3840 ' (p_rule => ''' || p_rule || '''' || l_cn ||
3841 ' p_application_id => ' || p_pk1 || l_cn ||
3842 ' p_id_flex_code => ''' || p_pk2 || '''' || l_cn ||
3843 ' p_id_flex_num => ' || p_pk3 || l_cn ||
3844 ' p_alias_name => ''' || p_pk4 || '''' || l_cn;
3845 END IF;
3846 IF (p_rule IN ('G.03', 'G.04', 'G.05', 'G.07')) THEN
3847 l_mid :=
3848 'fix_kff_cvr' || g_newline ||
3849 ' (p_rule => ''' || p_rule || '''' || l_cn ||
3850 ' p_application_id => ' || p_pk1 || l_cn ||
3851 ' p_id_flex_code => ''' || p_pk2 || '''' || l_cn ||
3852 ' p_id_flex_num => ' || p_pk3 || l_cn ||
3853 ' p_flex_validation_rule_name => ''' || p_pk4 || '''' || l_cn;
3854 END IF;
3855 IF (p_rule IN ('H.01', 'H.02', 'I.01', 'J.01')) THEN
3856 l_mid :=
3857 'fix_kff_cvl' || g_newline ||
3858 ' (p_rule => ''' || p_rule || '''' || l_cn ||
3859 ' p_application_id => ' || p_pk1 || l_cn ||
3860 ' p_id_flex_code => ''' || p_pk2 || '''' || l_cn ||
3861 ' p_id_flex_num => ' || p_pk3 || l_cn ||
3862 ' p_flex_validation_rule_name => ''' || p_pk4 || '''' || l_cn ||
3863 ' p_rule_line_id => ' || p_pk5 || l_cn;
3864 END IF;
3865 IF (p_rule IN ('G.06', 'H.03')) THEN
3866 l_mid :=
3867 'fix_kff_cvrls' || g_newline ||
3868 ' (p_rule => ''' || p_rule || '''' || l_cn;
3869 END IF;
3870 IF (p_rule IN ('L.01', 'L.02')) THEN
3871 l_mid :=
3872 'fix_kff_flq' || g_newline ||
3873 ' (p_rule => ''' || p_rule || '''' || l_cn ||
3874 ' p_application_id => ' || p_pk1 || l_cn ||
3875 ' p_id_flex_code => ''' || p_pk2 || '''' || l_cn ||
3876 ' p_segment_attribute_type => ''' || p_pk3 || '''' || l_cn;
3877 END IF;
3878 IF (p_rule IN ('M.01', 'M.02', 'M.03', 'M.04')) THEN
3879 l_mid :=
3880 'fix_kff_qlv' || g_newline ||
3881 ' (p_rule => ''' || p_rule || '''' || l_cn ||
3882 ' p_application_id => ' || p_pk1 || l_cn ||
3883 ' p_id_flex_code => ''' || p_pk2 || '''' || l_cn ||
3884 ' p_id_flex_num => ' || p_pk3 || l_cn ||
3885 ' p_application_column_name => ''' || p_pk4 || '''' || l_cn ||
3886 ' p_segment_attribute_type => ''' || p_pk5 || '''' || l_cn;
3887 END IF;
3888 IF (p_rule IN ('N.03', 'N.06', 'N.07')) THEN
3889 l_mid :=
3890 'fix_kff_sgq' || g_newline ||
3891 ' (p_rule => ''' || p_rule || '''' || l_cn ||
3892 ' p_application_id => ' || p_pk1 || l_cn ||
3893 ' p_id_flex_code => ''' || p_pk2 || '''' || l_cn ||
3894 ' p_segment_attribute_type => ''' || p_pk3 || '''' || l_cn ||
3895 ' p_value_attribute_type => ''' || p_pk4 || '''' || l_cn;
3896 END IF;
3897 IF (p_rule IN ('O.01', 'O.02')) THEN
3898 l_mid :=
3899 'fix_kff_fvq' || g_newline ||
3900 ' (p_rule => ''' || p_rule || '''' || l_cn ||
3901 ' p_id_flex_application_id => ' || p_pk1 || l_cn ||
3902 ' p_id_flex_code => ''' || p_pk2 || '''' || l_cn ||
3903 ' p_segment_attribute_type => ''' || p_pk3 || '''' || l_cn ||
3904 ' p_value_attribute_type => ''' || p_pk4 || '''' || l_cn ||
3905 ' p_flex_value_set_id => ' || p_pk5 || l_cn;
3906 END IF;
3907 IF (p_rule IN ('P.01', 'P.02', 'P.03', 'P.04')) THEN
3908 l_mid :=
3909 'fix_kff_col' || g_newline ||
3910 ' (p_rule => ''' || p_rule || '''' || l_cn ||
3911 ' p_application_id => ' || p_pk1 || l_cn ||
3912 ' p_table_name => ''' || p_pk2 || '''' || l_cn ||
3913 ' p_column_name => ''' || p_pk3 || '''' || l_cn;
3914 END IF;
3915 IF (p_rule IN ('R.01', 'R.02')) THEN
3916 l_mid :=
3917 'fix_kff_fwp' || g_newline ||
3918 ' (p_rule => ''' || p_rule || '''' || l_cn ||
3919 ' p_application_id => ' || p_pk1 || l_cn ||
3920 ' p_id_flex_code => ''' || p_pk2 || '''' || l_cn ||
3921 ' p_id_flex_num => ' || p_pk3 || l_cn ||
3922 ' p_wf_item_type => ''' || p_pk4 || '''' || l_cn;
3923 END IF;
3924 l_end := (' x_message => :msg);' || g_newline ||
3925 'END;' || g_newline ||
3926 '/' || g_newline ||
3927 'print msg;' || g_newline || g_newline);
3928
3929 IF (l_mid IS NOT NULL) THEN
3930 RETURN(l_beg || l_mid || l_end);
3931 ELSE
3932 RETURN('KFF fix is not available for rule : ' || p_rule);
3933 END IF;
3934 EXCEPTION
3935 WHEN OTHERS THEN
3936 RETURN('get_kff_fix : SQLERRM ' || Sqlerrm);
3937 END get_kff_fix;
3938
3939
3940 -- ***************************************************************************
3941 -- * KFF fix_kff_something(); procedures.
3942 -- ***************************************************************************
3943 -- ---------------------------------------------------------------------------
3944 FUNCTION populate_kff_cvrls(p_kff_str IN kff_str_type,
3945 x_message OUT nocopy VARCHAR2)
3946 RETURN BOOLEAN
3947 IS
3948 l_r_count NUMBER;
3949 l_l_count NUMBER;
3950 l_i_count NUMBER;
3951 l_e_count NUMBER;
3952
3953 CURSOR kff_cvl_cur(p_kff_str kff_str_type) IS
3954 SELECT *
3955 FROM fnd_flex_validation_rule_lines
3956 WHERE application_id = p_kff_str.application_id
3957 AND id_flex_code = p_kff_str.id_flex_code
3958 AND id_flex_num = p_kff_str.id_flex_num
3959 ORDER BY flex_validation_rule_name;
3960 BEGIN
3961 SAVEPOINT sp_populate_kff_cvrls;
3962
3963 BEGIN
3964 SELECT COUNT(*)
3965 INTO l_r_count
3966 FROM fnd_flex_validation_rules
3967 WHERE application_id = p_kff_str.application_id
3968 AND id_flex_code = p_kff_str.id_flex_code
3969 AND id_flex_num = p_kff_str.id_flex_num;
3970 EXCEPTION
3971 WHEN OTHERS THEN
3972 x_message :=
3973 'Unable to count FND_FLEX_VALIDATION_RULES. ' || Sqlerrm;
3974 GOTO return_error;
3975 END;
3976
3977 BEGIN
3978 SELECT COUNT(*)
3979 INTO l_l_count
3980 FROM fnd_flex_validation_rule_lines
3981 WHERE application_id = p_kff_str.application_id
3982 AND id_flex_code = p_kff_str.id_flex_code
3983 AND id_flex_num = p_kff_str.id_flex_num;
3984 EXCEPTION
3985 WHEN OTHERS THEN
3986 x_message :=
3987 'Unable to count FND_FLEX_VALIDATION_RULE_LINES. ' || Sqlerrm;
3988 GOTO return_error;
3989 END;
3990
3991 BEGIN
3992 DELETE
3993 FROM fnd_flex_include_rule_lines
3994 WHERE application_id = p_kff_str.application_id
3995 AND id_flex_code = p_kff_str.id_flex_code
3996 AND id_flex_num = p_kff_str.id_flex_num;
3997 EXCEPTION
3998 WHEN OTHERS THEN
3999 x_message :=
4000 'Unable to delete from FND_FLEX_INCLUDE_RULE_LINES. ' ||
4001 Sqlerrm;
4002 GOTO return_error;
4003 END;
4004
4005 BEGIN
4006 DELETE
4007 FROM fnd_flex_exclude_rule_lines
4008 WHERE application_id = p_kff_str.application_id
4009 AND id_flex_code = p_kff_str.id_flex_code
4010 AND id_flex_num = p_kff_str.id_flex_num;
4011 EXCEPTION
4012 WHEN OTHERS THEN
4013 x_message :=
4014 'Unable to delete from FND_FLEX_EXCLUDE_RULE_LINES. ' ||
4015 Sqlerrm;
4016 GOTO return_error;
4017 END;
4018
4019 BEGIN
4020 DELETE
4021 FROM fnd_flex_validation_rule_stats
4022 WHERE application_id = p_kff_str.application_id
4023 AND id_flex_code = p_kff_str.id_flex_code
4024 AND id_flex_num = p_kff_str.id_flex_num;
4025 EXCEPTION
4026 WHEN OTHERS THEN
4027 x_message :=
4028 'Unable to delete from FND_FLEX_VALIDATION_RULE_STATS. ' ||
4029 Sqlerrm;
4030 GOTO return_error;
4031 END;
4032
4033 IF (l_l_count > 0) THEN
4034 BEGIN
4035 INSERT
4036 INTO fnd_flex_validation_rule_stats
4037 (application_id,
4038 id_flex_code,
4039 id_flex_num,
4040 creation_date, created_by,
4041 last_update_date, last_updated_by, last_update_login,
4042 rule_count, include_line_count, exclude_line_count)
4043 VALUES
4044 (p_kff_str.application_id,
4045 p_kff_str.id_flex_code,
4046 p_kff_str.id_flex_num,
4047 Sysdate, -1,
4048 Sysdate, -1, -1,
4049 0, 0, 0);
4050 EXCEPTION
4051 WHEN OTHERS THEN
4052 x_message :=
4053 'Unable to insert into FND_FLEX_VALIDATION_RULE_STATS. ' ||
4054 Sqlerrm;
4055 GOTO return_error;
4056 END;
4057 END IF;
4058
4059 BEGIN
4060 FOR kff_cvl_rec IN kff_cvl_cur(p_kff_str) LOOP
4061 IF (fnd_flex_trigger.insert_rule_line
4062 (kff_cvl_rec.rule_line_id,
4063 kff_cvl_rec.application_id,
4064 kff_cvl_rec.id_flex_code,
4065 kff_cvl_rec.id_flex_num,
4066 kff_cvl_rec.flex_validation_rule_name,
4067 kff_cvl_rec.include_exclude_indicator,
4068 kff_cvl_rec.enabled_flag,
4069 kff_cvl_rec.created_by,
4070 kff_cvl_rec.creation_date,
4071 kff_cvl_rec.last_update_date,
4072 kff_cvl_rec.last_updated_by,
4073 kff_cvl_rec.last_update_login,
4074 kff_cvl_rec.concatenated_segments_low,
4075 kff_cvl_rec.concatenated_segments_high) = FALSE) THEN
4076 x_message := fnd_message.get;
4077 GOTO return_error;
4078 END IF;
4079 END LOOP;
4080 EXCEPTION
4081 WHEN OTHERS THEN
4082 x_message := 'Unable to populate I/E tables. ' || Sqlerrm;
4083 GOTO return_error;
4084 END;
4085
4086 BEGIN
4087 SELECT COUNT(*)
4088 INTO l_i_count
4089 FROM fnd_flex_include_rule_lines
4090 WHERE application_id = p_kff_str.application_id
4091 AND id_flex_code = p_kff_str.id_flex_code
4092 AND id_flex_num = p_kff_str.id_flex_num;
4093 EXCEPTION
4094 WHEN OTHERS THEN
4095 x_message :=
4096 'Unable to count FND_FLEX_INCLUDE_RULE_LINES. ' || Sqlerrm;
4097 GOTO return_error;
4098 END;
4099
4100 BEGIN
4101 SELECT COUNT(*)
4102 INTO l_e_count
4103 FROM fnd_flex_exclude_rule_lines
4104 WHERE application_id = p_kff_str.application_id
4105 AND id_flex_code = p_kff_str.id_flex_code
4106 AND id_flex_num = p_kff_str.id_flex_num;
4107 EXCEPTION
4108 WHEN OTHERS THEN
4109 x_message :=
4110 'Unable to count FND_FLEX_EXCLUDE_RULE_LINES. ' || Sqlerrm;
4111 GOTO return_error;
4112 END;
4113
4114 IF (l_i_count + l_e_count <> l_l_count) THEN
4115 x_message := 'I/E count sum is not equal to total line count. '||
4116 'Please open a bug and assign it to AOL/FLEXFIELDS team. ';
4117 GOTO return_error;
4118 END IF;
4119
4120 BEGIN
4121 UPDATE fnd_flex_validation_rule_stats fvrs SET
4122 last_update_date = Sysdate,
4123 last_updated_by = 1,
4124 rule_count = l_r_count,
4125 include_line_count = l_i_count,
4126 exclude_line_count = l_e_count
4127 WHERE application_id = p_kff_str.application_id
4128 AND id_flex_code = p_kff_str.id_flex_code
4129 AND id_flex_num = p_kff_str.id_flex_num;
4130 EXCEPTION
4131 WHEN OTHERS THEN
4132 x_message := 'Unable to update FND_FLEX_VALIDATION_RULE_STATS. ' ||
4133 Sqlerrm;
4134 GOTO return_error;
4135 END;
4136
4137 <<return_success>>
4138 RETURN(TRUE);
4139
4140 <<return_error>>
4141 ROLLBACK TO sp_populate_kff_cvrls;
4142 RETURN(FALSE);
4143 EXCEPTION
4144 WHEN OTHERS THEN
4145 x_message := 'populate_kff_cvrls: Top level error: ' || Sqlerrm;
4146 ROLLBACK TO sp_populate_kff_cvrls;
4147 RETURN(FALSE);
4148 END populate_kff_cvrls;
4149
4150 -- ===========================================================================
4151 PROCEDURE fix_kff_flx(p_rule IN VARCHAR2,
4152 p_application_id IN NUMBER,
4153 p_id_flex_code IN VARCHAR2,
4154 x_message OUT nocopy VARCHAR2)
4155 IS
4156 l_tbl tbl_type;
4157 l_col col_type;
4158 l_kff_flx kff_flx_type;
4159 l_kff_str kff_str_type;
4160 l_rowid VARCHAR2(100);
4161 l_count NUMBER;
4162 BEGIN
4163 IF (p_rule = 'A.01') THEN
4164 --
4165 -- KFF with invalid APPLICATION_ID.
4166 --
4167 BEGIN
4168 DELETE
4169 FROM fnd_id_flexs idf
4170 WHERE application_id = p_application_id
4171 AND id_flex_code = p_id_flex_code
4172 AND NOT EXISTS
4173 (SELECT null
4174 FROM fnd_application aa
4175 WHERE aa.application_id = idf.application_id);
4176 x_message := SQL%rowcount || ' row(s) deleted.';
4177 GOTO return_success;
4178 EXCEPTION
4179 WHEN OTHERS THEN
4180 x_message :=
4181 'Unable to delete from FND_ID_FLEXS. ' || Sqlerrm;
4182 GOTO return_error;
4183 END;
4184 END IF;
4185
4186 IF (p_rule = 'D.01') THEN
4187 --
4188 -- Compiled definitions without KFF.
4189 --
4190 BEGIN
4191 DELETE
4192 FROM fnd_compiled_id_flexs cif
4193 WHERE application_id = p_application_id
4194 AND id_flex_code = p_id_flex_code
4195 AND NOT EXISTS
4196 (SELECT null
4197 FROM fnd_id_flexs idf
4198 WHERE idf.application_id = cif.application_id
4199 AND idf.id_flex_code = cif.id_flex_code);
4200 x_message := SQL%rowcount || ' row(s) deleted.';
4201 GOTO return_success;
4202 EXCEPTION
4203 WHEN OTHERS THEN
4204 x_message :=
4205 'Unable to delete from FND_COMPILED_ID_FLEXS. ' || Sqlerrm;
4206 GOTO return_error;
4207 END;
4208 END IF;
4209
4210 IF (NOT fetch_kff_flx(p_application_id,
4211 p_id_flex_code,
4212 l_kff_flx,
4213 x_message)) THEN
4214 GOTO return_error;
4215 END IF;
4216
4217 IF (p_rule = 'A.07') THEN
4218 --
4219 -- CCID column is not registered properly.
4220 --
4221 IF (NOT fetch_tbl(l_kff_flx.table_application_id,
4222 l_kff_flx.application_table_name,
4223 l_tbl,
4224 x_message)) THEN
4225 GOTO return_error;
4226 END IF;
4227
4228 IF (NOT fetch_col(l_tbl,
4229 l_kff_flx.unique_id_column_name,
4230 l_col,
4231 x_message)) THEN
4232 GOTO return_error;
4233 END IF;
4234
4235 IF (l_col.flexfield_usage_code = 'I') THEN
4236 x_message := 'CCID column is properly registered. No need to fix.';
4237 GOTO return_error;
4238 END IF;
4239
4240 IF (update_fnd_columns(l_col,
4241 'I',
4242 NULL, -- l_kff_flx.application_id,
4243 NULL, -- l_kff_flx.id_flex_code,
4244 x_message)) THEN
4245 GOTO return_success;
4246 ELSE
4247 GOTO return_error;
4248 END IF;
4249 END IF;
4250
4251 IF (p_rule = 'A.10') THEN
4252 --
4253 -- Structure column is not registered properly.
4254 --
4255 IF (l_kff_flx.set_defining_column_name IS NULL) THEN
4256 x_message := 'This KFF has no structure column. No need to fix. ';
4257 GOTO return_error;
4258 END IF;
4259
4260 IF (NOT fetch_tbl(l_kff_flx.table_application_id,
4261 l_kff_flx.application_table_name,
4262 l_tbl,
4263 x_message)) THEN
4264 GOTO return_error;
4265 END IF;
4266
4267 IF (NOT fetch_col(l_tbl,
4268 l_kff_flx.set_defining_column_name,
4269 l_col,
4270 x_message)) THEN
4271 GOTO return_error;
4272 END IF;
4273
4274 IF (l_col.flexfield_usage_code = 'S') THEN
4275 x_message := 'Structure column is properly registered. No need to fix.';
4276 GOTO return_error;
4277 END IF;
4278
4279 IF (update_fnd_columns(l_col,
4280 'S',
4281 NULL, -- l_kff_flx.application_id,
4282 NULL, -- l_kff_flx.id_flex_code,
4283 x_message)) THEN
4284 GOTO return_success;
4285 ELSE
4286 GOTO return_error;
4287 END IF;
4288 END IF;
4289
4290 IF (p_rule = 'A.11') THEN
4291 --
4292 -- No structures.
4293 --
4294 SELECT COUNT(*)
4295 INTO l_count
4296 FROM fnd_id_flex_structures
4297 WHERE application_id = l_kff_flx.application_id
4298 AND id_flex_code = l_kff_flx.id_flex_code;
4299
4300 IF (l_count > 0) THEN
4301 x_message := 'There is at least one structure. No need to fix.';
4302 GOTO return_error;
4303 END IF;
4304
4305 --
4306 -- At this point there are no structures.
4307 --
4308 IF (NOT fetch_kff_str(l_kff_flx,
4309 101,
4310 l_kff_str,
4311 x_message)) THEN
4312 --
4313 -- 101 structure doesn't exist, insert it.
4314 --
4315 fnd_id_flex_structures_pkg.insert_row
4316 (x_rowid => l_rowid,
4317 x_application_id => l_kff_flx.application_id,
4318 x_id_flex_code => l_kff_flx.id_flex_code,
4319 x_id_flex_num => 101,
4320 x_id_flex_structure_code => Upper(l_kff_flx.id_flex_name),
4321 x_concatenated_segment_delimit => '.',
4322 x_cross_segment_validation_fla => 'N',
4323 x_dynamic_inserts_allowed_flag => 'N',
4324 x_enabled_flag => 'Y',
4325 x_freeze_flex_definition_flag => 'N',
4326 x_freeze_structured_hier_flag => 'N',
4327 x_shorthand_enabled_flag => 'N',
4328 x_shorthand_length => NULL,
4329 x_structure_view_name => NULL,
4330 x_id_flex_structure_name => l_kff_flx.id_flex_name,
4331 x_description => NULL,
4332 x_shorthand_prompt => NULL,
4333 x_creation_date => Sysdate,
4334 x_created_by => 1,
4335 x_last_update_date => Sysdate,
4336 x_last_updated_by => 1,
4337 x_last_update_login => 0);
4338 x_message := '101 Structure is inserted.';
4339 GOTO return_success;
4340 END IF;
4341 END IF;
4342
4343 IF (p_rule = 'A.12') THEN
4344 --
4345 -- Problems in flags.
4346 --
4347 IF (NOT lookup_code_exists('KEY_FLEXFIELD_TABLE_TYPE',
4348 l_kff_flx.application_table_type)) THEN
4349 l_kff_flx.application_table_type := NULL;
4350 END IF;
4351
4352 IF (NOT lookup_code_exists('YES_NO',
4353 l_kff_flx.allow_id_valuesets)) THEN
4354 l_kff_flx.allow_id_valuesets := 'Y';
4355 END IF;
4356
4357 IF (NOT lookup_code_exists('YES_NO',
4358 l_kff_flx.dynamic_inserts_feasible_flag)) THEN
4359 l_kff_flx.dynamic_inserts_feasible_flag := 'N';
4360 END IF;
4361
4362 IF (NOT lookup_code_exists('YES_NO',
4363 l_kff_flx.index_flag)) THEN
4364 l_kff_flx.index_flag := 'N';
4365 END IF;
4366
4367 BEGIN
4368 UPDATE fnd_id_flexs SET
4369 application_table_type = l_kff_flx.application_table_type,
4370 allow_id_valuesets = l_kff_flx.allow_id_valuesets,
4371 dynamic_inserts_feasible_flag = l_kff_flx.dynamic_inserts_feasible_flag,
4372 index_flag = l_kff_flx.index_flag,
4373 last_update_date = Sysdate,
4374 last_updated_by = 1
4375 WHERE application_id = l_kff_flx.application_id
4376 AND id_flex_code = l_kff_flx.id_flex_code;
4377 x_message := SQL%rowcount || ' row(s) updated.';
4378 GOTO return_success;
4379 EXCEPTION
4380 WHEN OTHERS THEN
4381 x_message := 'Unable to update FND_ID_FLEXS. ' || Sqlerrm;
4382 GOTO return_error;
4383 END;
4384 END IF;
4385
4386 <<return_success>>
4387 <<return_error>>
4388 RETURN;
4389 EXCEPTION
4390 WHEN OTHERS THEN
4391 x_message := 'fix_kff_flx: Top level error: ' || Sqlerrm;
4392 END fix_kff_flx;
4393
4394 -- ===========================================================================
4395 PROCEDURE fix_kff_str(p_rule IN VARCHAR2,
4396 p_application_id IN NUMBER,
4397 p_id_flex_code IN VARCHAR2,
4398 p_id_flex_num IN NUMBER,
4399 x_message OUT nocopy VARCHAR2)
4400 IS
4401 l_kff_flx kff_flx_type;
4402 l_kff_str kff_str_type;
4403 l_count NUMBER;
4404 BEGIN
4405 IF (p_rule = 'B.03') THEN
4406 --
4407 -- Structure without KFF.
4408 --
4409 BEGIN
4410 DELETE
4411 FROM fnd_id_flex_structures ifst
4412 WHERE application_id = p_application_id
4413 AND id_flex_code = p_id_flex_code
4414 AND id_flex_num = p_id_flex_num
4415 AND NOT EXISTS
4416 (SELECT null
4417 FROM fnd_id_flexs idf
4418 WHERE idf.application_id = ifst.application_id
4419 AND idf.id_flex_code = ifst.id_flex_code);
4420 x_message := SQL%rowcount || ' row(s) deleted.';
4421 GOTO return_success;
4422 EXCEPTION
4423 WHEN OTHERS THEN
4424 x_message :=
4425 'Unable to delete from FND_ID_FLEX_STRUCTURES. ' || Sqlerrm;
4426 GOTO return_error;
4427 END;
4428 END IF;
4429
4430 IF (p_rule = 'E.01') THEN
4431 --
4432 -- Compiled definitions without structure.
4433 --
4434 BEGIN
4435 DELETE
4436 FROM fnd_compiled_id_flex_structs cifs
4437 WHERE application_id = p_application_id
4438 AND id_flex_code = p_id_flex_code
4439 AND id_flex_num = p_id_flex_num
4440 AND NOT EXISTS
4441 (SELECT null
4442 FROM fnd_id_flex_structures ifst
4443 WHERE ifst.application_id = cifs.application_id
4444 AND ifst.id_flex_code = cifs.id_flex_code
4445 AND ifst.id_flex_num = cifs.id_flex_num);
4446 x_message := SQL%rowcount || ' row(s) deleted.';
4447 GOTO return_success;
4448 EXCEPTION
4449 WHEN OTHERS THEN
4450 x_message :=
4451 'Unable to delete from FND_COMPILED_ID_FLEX_STRUCTS. ' ||
4452 Sqlerrm;
4453 GOTO return_error;
4454 END;
4455 END IF;
4456
4457 IF (p_rule = 'E.02') THEN
4458 --
4459 -- Compiled Structures without compiled KFF.
4460 --
4461 BEGIN
4462 DELETE
4463 FROM fnd_compiled_id_flex_structs cifs
4464 WHERE application_id = p_application_id
4465 AND id_flex_code = p_id_flex_code
4466 AND id_flex_num = p_id_flex_num
4467 AND NOT EXISTS
4468 (SELECT null
4469 FROM fnd_compiled_id_flexs cif
4470 WHERE cif.application_id = cifs.application_id
4471 AND cif.id_flex_code = cifs.id_flex_code);
4472 x_message := SQL%rowcount || ' row(s) deleted.';
4473 GOTO return_success;
4474 EXCEPTION
4475 WHEN OTHERS THEN
4476 x_message :=
4477 'Unable to delete from FND_COMPILED_ID_FLEX_STRUCTS. ' ||
4478 Sqlerrm;
4479 GOTO return_error;
4480 END;
4481 END IF;
4482
4483 IF (NOT fetch_kff_flx(p_application_id,
4484 p_id_flex_code,
4485 l_kff_flx,
4486 x_message)) THEN
4487 GOTO return_error;
4488 END IF;
4489
4490 IF (NOT fetch_kff_str(l_kff_flx,
4491 p_id_flex_num,
4492 l_kff_str,
4493 x_message)) THEN
4494 GOTO return_error;
4495 END IF;
4496
4497 IF (p_rule = 'B.04') THEN
4498 --
4499 -- Problems in flags.
4500 --
4501 IF (NOT lookup_code_exists('YES_NO',
4502 l_kff_str.cross_segment_validation_flag)) THEN
4503 l_kff_str.cross_segment_validation_flag := 'N';
4504 END IF;
4505
4506 IF (NOT lookup_code_exists('YES_NO',
4507 l_kff_str.dynamic_inserts_allowed_flag)) THEN
4508 l_kff_str.dynamic_inserts_allowed_flag := 'N';
4509 END IF;
4510
4511 IF (NOT lookup_code_exists('YES_NO',
4512 l_kff_str.enabled_flag)) THEN
4513 l_kff_str.enabled_flag := 'N';
4514 END IF;
4515
4516 IF (NOT lookup_code_exists('YES_NO',
4517 l_kff_str.freeze_flex_definition_flag)) THEN
4518 l_kff_str.freeze_flex_definition_flag := 'N';
4519 END IF;
4520
4521 IF (NOT lookup_code_exists('YES_NO',
4522 l_kff_str.freeze_structured_hier_flag)) THEN
4523 l_kff_str.freeze_structured_hier_flag := 'N';
4524 END IF;
4525
4526 IF (NOT lookup_code_exists('YES_NO',
4527 l_kff_str.shorthand_enabled_flag)) THEN
4528 l_kff_str.shorthand_enabled_flag := 'N';
4529 END IF;
4530
4531 BEGIN
4532 UPDATE fnd_id_flex_structures SET
4533 cross_segment_validation_flag = l_kff_str.cross_segment_validation_flag,
4534 dynamic_inserts_allowed_flag = l_kff_str.dynamic_inserts_allowed_flag,
4535 enabled_flag = l_kff_str.enabled_flag,
4536 freeze_flex_definition_flag = l_kff_str.freeze_flex_definition_flag,
4537 freeze_structured_hier_flag = l_kff_str.freeze_structured_hier_flag,
4538 shorthand_enabled_flag = l_kff_str.shorthand_enabled_flag,
4539 last_update_date = Sysdate,
4540 last_updated_by = 1
4541 WHERE application_id = l_kff_str.application_id
4542 AND id_flex_code = l_kff_str.id_flex_code
4543 AND id_flex_num = l_kff_str.id_flex_num;
4544 x_message := SQL%rowcount || ' row(s) updated.';
4545 GOTO return_success;
4546 EXCEPTION
4547 WHEN OTHERS THEN
4548 x_message :=
4549 'Unable to update FND_ID_FLEX_STRUCTURES. ' || Sqlerrm;
4550 GOTO return_error;
4551 END;
4552 END IF;
4553
4554 IF (p_rule IN ('K.01', 'K.02')) THEN
4555 --
4556 -- Problems in CVR stats table.
4557 --
4558 IF (NOT populate_kff_cvrls(l_kff_str,
4559 x_message)) THEN
4560 GOTO return_error;
4561 END IF;
4562 END IF;
4563
4564 <<return_success>>
4565 <<return_error>>
4566 RETURN;
4567 EXCEPTION
4568 WHEN OTHERS THEN
4569 x_message := 'fix_kff_str: Top level error: ' || Sqlerrm;
4570 END fix_kff_str;
4571
4572 -- ===========================================================================
4573 PROCEDURE fix_kff_seg(p_rule IN VARCHAR2,
4574 p_application_id IN NUMBER,
4575 p_id_flex_code IN VARCHAR2,
4576 p_id_flex_num IN NUMBER,
4577 p_application_column_name IN VARCHAR2,
4578 x_message OUT nocopy VARCHAR2)
4579 IS
4580 l_tbl tbl_type;
4581 l_col col_type;
4582 l_vst_set vst_set_type;
4583 l_kff_flx kff_flx_type;
4584 l_kff_str kff_str_type;
4585 l_kff_seg kff_seg_type;
4586 BEGIN
4587 IF (p_rule = 'C.03') THEN
4588 --
4589 -- Segments without Structure.
4590 --
4591 BEGIN
4592 DELETE
4593 FROM fnd_id_flex_segments ifsg
4594 WHERE application_id = p_application_id
4595 AND id_flex_code = p_id_flex_code
4596 AND id_flex_num = p_id_flex_num
4597 AND application_column_name = p_application_column_name
4598 AND NOT EXISTS
4599 (SELECT null
4600 FROM fnd_id_flex_structures ifst
4601 WHERE ifst.application_id = ifsg.application_id
4602 AND ifst.id_flex_code = ifsg.id_flex_code
4603 AND ifst.id_flex_num = ifsg.id_flex_num);
4604 x_message := SQL%rowcount || ' row(s) deleted.';
4605 GOTO return_success;
4606 EXCEPTION
4607 WHEN OTHERS THEN
4608 x_message :=
4609 'Unable to delete from FND_ID_FLEX_SEGMENTS. ' || Sqlerrm;
4610 GOTO return_error;
4611 END;
4612 END IF;
4613
4614 IF (NOT fetch_kff_flx(p_application_id,
4615 p_id_flex_code,
4616 l_kff_flx,
4617 x_message)) THEN
4618 GOTO return_error;
4619 END IF;
4620
4621 IF (NOT fetch_kff_str(l_kff_flx,
4622 p_id_flex_num,
4623 l_kff_str,
4624 x_message)) THEN
4625 GOTO return_error;
4626 END IF;
4627
4628 IF (NOT fetch_kff_seg(l_kff_str,
4629 p_application_column_name,
4630 l_kff_seg,
4631 x_message)) THEN
4632 GOTO return_error;
4633 END IF;
4634
4635 IF (p_rule = 'C.06') THEN
4636 --
4637 -- Segment column is not registered properly.
4638 --
4639 IF (NOT fetch_tbl(l_kff_flx.table_application_id,
4640 l_kff_flx.application_table_name,
4641 l_tbl,
4642 x_message)) THEN
4643 GOTO return_error;
4644 END IF;
4645
4646 IF (NOT fetch_col(l_tbl,
4647 l_kff_seg.application_column_name,
4648 l_col,
4649 x_message)) THEN
4650 GOTO return_error;
4651 END IF;
4652
4653 IF (l_col.flexfield_usage_code = 'K') THEN
4654 x_message := 'Segment column is properly registered. No need to fix.';
4655 GOTO return_error;
4656 END IF;
4657
4658 IF (update_fnd_columns(l_col,
4659 'K',
4660 NULL, -- l_kff_flx.application_id,
4661 NULL, -- l_kff_flx.id_flex_code,
4662 x_message)) THEN
4663 GOTO return_success;
4664 ELSE
4665 GOTO return_error;
4666 END IF;
4667 END IF;
4668
4669 IF (p_rule = 'C.07') THEN
4670 --
4671 -- Non-existing value set is used.
4672 --
4673 IF (l_kff_seg.flex_value_set_id IS NULL) THEN
4674 x_message := 'No value set is used in this segment. No need to fix.';
4675 GOTO return_error;
4676 END IF;
4677
4678 IF (fetch_vst_set(l_kff_seg.flex_value_set_id,
4679 l_vst_set,
4680 x_message)) THEN
4681 x_message := 'Value set already exists. No need to fix.';
4682 GOTO return_error;
4683 END IF;
4684
4685 BEGIN
4686 UPDATE fnd_id_flex_segments SET
4687 flex_value_set_id = NULL,
4688 last_update_date = Sysdate,
4689 last_updated_by = 1
4690 WHERE application_id = l_kff_seg.application_id
4691 AND id_flex_code = l_kff_seg.id_flex_code
4692 AND id_flex_num = l_kff_seg.id_flex_num
4693 AND application_column_name = l_kff_seg.application_column_name;
4694 x_message := SQL%rowcount || ' row(s) updated.';
4695 GOTO return_success;
4696 EXCEPTION
4697 WHEN OTHERS THEN
4698 x_message :=
4699 'Unable to update FND_ID_FLEX_SEGMENTS. ' || Sqlerrm;
4700 GOTO return_error;
4701 END;
4702 END IF;
4703
4704 IF (p_rule = 'C.08') THEN
4705 --
4706 -- Problem in flags.
4707 --
4708 IF (NOT lookup_code_exists('FLEX_DEFAULT_TYPE',
4709 l_kff_seg.default_type)) THEN
4710 l_kff_seg.default_type := NULL;
4711 END IF;
4712
4713 IF (NOT lookup_code_exists('RANGE_CODES',
4714 l_kff_seg.range_code)) THEN
4715 l_kff_seg.range_code := NULL;
4716 END IF;
4717
4718 IF (NOT lookup_code_exists('YES_NO',
4719 l_kff_seg.application_column_index_flag)) THEN
4720 l_kff_seg.application_column_index_flag := 'N';
4721 END IF;
4722
4723 IF (NOT lookup_code_exists('YES_NO',
4724 l_kff_seg.enabled_flag)) THEN
4725 l_kff_seg.enabled_flag := 'N';
4726 END IF;
4727
4728 IF (NOT lookup_code_exists('YES_NO',
4729 l_kff_seg.required_flag)) THEN
4730 l_kff_seg.required_flag := 'N';
4731 END IF;
4732
4733 IF (NOT lookup_code_exists('YES_NO',
4734 l_kff_seg.display_flag)) THEN
4735 l_kff_seg.display_flag := 'N';
4736 END IF;
4737
4738 IF (NOT lookup_code_exists('YES_NO',
4739 l_kff_seg.security_enabled_flag)) THEN
4740 l_kff_seg.security_enabled_flag := 'N';
4741 END IF;
4742
4743 BEGIN
4744 UPDATE fnd_id_flex_segments SET
4745 default_type = l_kff_seg.default_type,
4746 range_code = l_kff_seg.range_code,
4747 application_column_index_flag = l_kff_seg.application_column_index_flag,
4748 enabled_flag = l_kff_seg.enabled_flag,
4749 required_flag = l_kff_seg.required_flag,
4750 display_flag = l_kff_seg.display_flag,
4751 security_enabled_flag = l_kff_seg.security_enabled_flag,
4752 last_update_date = Sysdate,
4753 last_updated_by = 1
4754 WHERE application_id = l_kff_seg.application_id
4755 AND id_flex_code = l_kff_seg.id_flex_code
4756 AND id_flex_num = l_kff_seg.id_flex_num
4757 AND application_column_name = l_kff_seg.application_column_name;
4758 x_message := SQL%rowcount || ' row(s) updated.';
4759 GOTO return_success;
4760 EXCEPTION
4761 WHEN OTHERS THEN
4762 x_message :=
4763 'Unable to update FND_ID_FLEX_SEGMENTS. ' || Sqlerrm;
4764 GOTO return_error;
4765 END;
4766 END IF;
4767
4768 <<return_success>>
4769 <<return_error>>
4770 RETURN;
4771 EXCEPTION
4772 WHEN OTHERS THEN
4773 x_message := 'fix_kff_seg: Top level error: ' || Sqlerrm;
4774 END fix_kff_seg;
4775
4776 -- ===========================================================================
4777 PROCEDURE fix_kff_sha(p_rule IN VARCHAR2,
4778 p_application_id IN NUMBER,
4779 p_id_flex_code IN VARCHAR2,
4780 p_id_flex_num IN NUMBER,
4781 p_alias_name IN VARCHAR2,
4782 x_message OUT nocopy VARCHAR2)
4783 IS
4784 l_kff_flx kff_flx_type;
4785 l_kff_str kff_str_type;
4786 l_kff_sha kff_sha_type;
4787 BEGIN
4788 IF (p_rule = 'F.01') THEN
4789 --
4790 -- SHAs without Structure.
4791 --
4792 BEGIN
4793 DELETE
4794 FROM fnd_shorthand_flex_aliases sfa
4795 WHERE application_id = p_application_id
4796 AND id_flex_code = p_id_flex_code
4797 AND id_flex_num = p_id_flex_num
4798 AND alias_name = p_alias_name
4799 AND NOT EXISTS
4800 (SELECT null
4801 FROM fnd_id_flex_structures ifst
4802 WHERE ifst.application_id = sfa.application_id
4803 AND ifst.id_flex_code = sfa.id_flex_code
4804 AND ifst.id_flex_num = sfa.id_flex_num);
4805 x_message := SQL%rowcount || ' row(s) deleted.';
4806 GOTO return_success;
4807 EXCEPTION
4808 WHEN OTHERS THEN
4809 x_message :=
4810 'Unable to delete from FND_SHORTHAND_FLEX_ALIASES. ' || Sqlerrm;
4811 GOTO return_error;
4812 END;
4813 END IF;
4814
4815 IF (NOT fetch_kff_flx(p_application_id,
4816 p_id_flex_code,
4817 l_kff_flx,
4818 x_message)) THEN
4819 GOTO return_error;
4820 END IF;
4821
4822 IF (NOT fetch_kff_str(l_kff_flx,
4823 p_id_flex_num,
4824 l_kff_str,
4825 x_message)) THEN
4826 GOTO return_error;
4827 END IF;
4828
4829 IF (NOT fetch_kff_sha(l_kff_str,
4830 p_alias_name,
4831 l_kff_sha,
4832 x_message)) THEN
4833 GOTO return_error;
4834 END IF;
4835
4836 IF (p_rule = 'F.02') THEN
4837 --
4838 -- Problems in flags.
4839 --
4840 IF (NOT lookup_code_exists('YES_NO',
4841 l_kff_sha.enabled_flag)) THEN
4842 l_kff_sha.enabled_flag := 'N';
4843 END IF;
4844
4845 IF ((l_kff_sha.start_date_active IS NOT NULL) AND
4846 (l_kff_sha.end_date_active IS NOT NULL) AND
4847 (l_kff_sha.start_date_active > l_kff_sha.end_date_active)) THEN
4848 l_kff_sha.end_date_active := l_kff_sha.start_date_active;
4849 END IF;
4850
4851 BEGIN
4852 UPDATE fnd_shorthand_flex_aliases SET
4853 enabled_flag = l_kff_sha.enabled_flag,
4854 start_date_active = l_kff_sha.start_date_active,
4855 end_date_active = l_kff_sha.end_date_active,
4856 last_update_date = Sysdate,
4857 last_updated_by = 1
4858 WHERE application_id = l_kff_sha.application_id
4859 AND id_flex_code = l_kff_sha.id_flex_code
4860 AND id_flex_num = l_kff_sha.id_flex_num
4861 AND alias_name = l_kff_sha.alias_name;
4862 x_message := SQL%rowcount || ' row(s) updated.';
4863 GOTO return_success;
4864 EXCEPTION
4865 WHEN OTHERS THEN
4866 x_message :=
4867 'Unable to update FND_SHORTHAND_FLEX_ALIASES. ' || Sqlerrm;
4868 GOTO return_error;
4869 END;
4870 END IF;
4871
4872 <<return_success>>
4873 <<return_error>>
4874 RETURN;
4875 EXCEPTION
4876 WHEN OTHERS THEN
4877 x_message := 'fix_kff_sha: Top level error: ' || Sqlerrm;
4878 END fix_kff_sha;
4879
4880 -- ===========================================================================
4881 PROCEDURE fix_kff_cvr(p_rule IN VARCHAR2,
4882 p_application_id IN NUMBER,
4883 p_id_flex_code IN VARCHAR2,
4884 p_id_flex_num IN NUMBER,
4885 p_flex_validation_rule_name IN VARCHAR2,
4886 x_message OUT nocopy VARCHAR2)
4887 IS
4888 l_kff_flx kff_flx_type;
4889 l_kff_str kff_str_type;
4890 l_kff_cvr kff_cvr_type;
4891 l_kff_seg kff_seg_type;
4892 l_count NUMBER;
4893 BEGIN
4894 IF (p_rule = 'G.03') THEN
4895 --
4896 -- CVRs without Structure.
4897 --
4898 BEGIN
4899 DELETE
4900 FROM fnd_flex_validation_rules fvr
4901 WHERE application_id = p_application_id
4902 AND id_flex_code = p_id_flex_code
4903 AND id_flex_num = p_id_flex_num
4904 AND flex_validation_rule_name = p_flex_validation_rule_name
4905 AND NOT EXISTS
4906 (SELECT null
4907 FROM fnd_id_flex_structures ifst
4908 WHERE ifst.application_id = fvr.application_id
4909 AND ifst.id_flex_code = fvr.id_flex_code
4910 AND ifst.id_flex_num = fvr.id_flex_num);
4911 x_message := SQL%rowcount || ' row(s) deleted.';
4912 GOTO return_success;
4913 EXCEPTION
4914 WHEN OTHERS THEN
4915 x_message :=
4916 'Unable to delete from FND_FLEX_VALIDATION_RULES. ' || Sqlerrm;
4917 GOTO return_error;
4918 END;
4919 END IF;
4920
4921 IF (NOT fetch_kff_flx(p_application_id,
4922 p_id_flex_code,
4923 l_kff_flx,
4924 x_message)) THEN
4925 GOTO return_error;
4926 END IF;
4927
4928 IF (NOT fetch_kff_str(l_kff_flx,
4929 p_id_flex_num,
4930 l_kff_str,
4931 x_message)) THEN
4932 GOTO return_error;
4933 END IF;
4934
4935 IF (NOT fetch_kff_cvr(l_kff_str,
4936 p_flex_validation_rule_name,
4937 l_kff_cvr,
4938 x_message)) THEN
4939 GOTO return_error;
4940 END IF;
4941
4942 IF (p_rule = 'G.04') THEN
4943 --
4944 -- Non-existing error segment.
4945 --
4946 IF (l_kff_cvr.error_segment_column_name IS NULL) THEN
4947 x_message := 'No error segment is defined. No need to fix.';
4948 GOTO return_error;
4949 END IF;
4950
4951 IF (fetch_kff_seg(l_kff_str,
4952 l_kff_cvr.error_segment_column_name,
4953 l_kff_seg,
4954 x_message)) THEN
4955 x_message := 'Error segment exists. No need to fix.';
4956 GOTO return_error;
4957 END IF;
4958
4959 BEGIN
4960 UPDATE fnd_flex_validation_rules SET
4961 error_segment_column_name = NULL,
4962 last_update_date = Sysdate,
4963 last_updated_by = 1
4964 WHERE application_id = p_application_id
4965 AND id_flex_code = p_id_flex_code
4966 AND id_flex_num = p_id_flex_num
4967 AND flex_validation_rule_name = p_flex_validation_rule_name;
4968 x_message := SQL%rowcount || ' row(s) updated.';
4969 GOTO return_success;
4970 EXCEPTION
4971 WHEN OTHERS THEN
4972 x_message := 'Unable to update FND_FLEX_VALIDATION_RULES. ' ||
4973 Sqlerrm;
4974 GOTO return_error;
4975 END;
4976 END IF;
4977
4978 IF (p_rule = 'G.05') THEN
4979 --
4980 -- Problem in flags.
4981 --
4982 IF (NOT lookup_code_exists('YES_NO',
4983 l_kff_cvr.enabled_flag)) THEN
4984 l_kff_cvr.enabled_flag := 'N';
4985 END IF;
4986
4987 IF ((l_kff_cvr.start_date_active IS NOT NULL) AND
4988 (l_kff_cvr.end_date_active IS NOT NULL) AND
4989 (l_kff_cvr.start_date_active > l_kff_cvr.end_date_active)) THEN
4990 l_kff_cvr.end_date_active := l_kff_cvr.start_date_active;
4991 END IF;
4992
4993 BEGIN
4994 UPDATE fnd_flex_validation_rules SET
4995 enabled_flag = l_kff_cvr.enabled_flag,
4996 start_date_active = l_kff_cvr.start_date_active,
4997 end_date_active = l_kff_cvr.end_date_active,
4998 last_update_date = Sysdate,
4999 last_updated_by = 1
5000 WHERE application_id = l_kff_cvr.application_id
5001 AND id_flex_code = l_kff_cvr.id_flex_code
5002 AND id_flex_num = l_kff_cvr.id_flex_num
5003 AND flex_validation_rule_name = l_kff_cvr.flex_validation_rule_name;
5004 x_message := SQL%rowcount || ' row(s) updated.';
5005 GOTO return_success;
5006 EXCEPTION
5007 WHEN OTHERS THEN
5008 x_message :=
5009 'Unable to update FND_FLEX_VALIDATION_RULES. ' || Sqlerrm;
5010 GOTO return_error;
5011 END;
5012 END IF;
5013
5014 IF (p_rule = 'G.07') THEN
5015 --
5016 -- Rules without lines.
5017 --
5018 SELECT COUNT(*)
5019 INTO l_count
5020 FROM fnd_flex_validation_rule_lines
5021 WHERE application_id = l_kff_cvr.application_id
5022 AND id_flex_code = l_kff_cvr.id_flex_code
5023 AND id_flex_num = l_kff_cvr.id_flex_num
5024 AND flex_validation_rule_name = l_kff_cvr.flex_validation_rule_name;
5025
5026 IF (l_count > 0) THEN
5027 x_message := 'There are ' || l_count || ' lines for this rule. ' ||
5028 'No need to fix.';
5029 GOTO return_error;
5030 END IF;
5031
5032 IF (l_kff_cvr.enabled_flag = 'N') THEN
5033 x_message := 'This rule is already disabled. No need to fix.';
5034 GOTO return_error;
5035 END IF;
5036
5037
5038 BEGIN
5039 UPDATE fnd_flex_validation_rules SET
5040 enabled_flag = 'N',
5041 last_update_date = Sysdate,
5042 last_updated_by = 1
5043 WHERE application_id = l_kff_cvr.application_id
5044 AND id_flex_code = l_kff_cvr.id_flex_code
5045 AND id_flex_num = l_kff_cvr.id_flex_num
5046 AND flex_validation_rule_name = l_kff_cvr.flex_validation_rule_name;
5047 x_message := SQL%rowcount || ' row(s) deleted.';
5048 GOTO return_success;
5049 EXCEPTION
5050 WHEN OTHERS THEN
5051 x_message :=
5052 'Unable to delete from FND_FLEX_VALIDATION_RULES. ' || Sqlerrm;
5053 GOTO return_error;
5054 END;
5055 END IF;
5056
5057 <<return_success>>
5058 <<return_error>>
5059 RETURN;
5060 EXCEPTION
5061 WHEN OTHERS THEN
5062 x_message := 'fix_kff_cvr: Top level error: ' || Sqlerrm;
5063 END fix_kff_cvr;
5064 -- ===========================================================================
5065 PROCEDURE fix_kff_cvl(p_rule IN VARCHAR2,
5066 p_application_id IN NUMBER,
5067 p_id_flex_code IN VARCHAR2,
5068 p_id_flex_num IN NUMBER,
5069 p_flex_validation_rule_name IN VARCHAR2,
5070 p_rule_line_id IN NUMBER,
5071 x_message OUT nocopy VARCHAR2)
5072 IS
5073 l_kff_flx kff_flx_type;
5074 l_kff_str kff_str_type;
5075 l_kff_cvr kff_cvr_type;
5076 l_kff_cvl kff_cvl_type;
5077 l_kff_cvi kff_cvi_type;
5078 l_kff_cve kff_cve_type;
5079 BEGIN
5080 IF (p_rule = 'H.01') THEN
5081 --
5082 -- Lines without Rule.
5083 --
5084 BEGIN
5085 DELETE
5086 FROM fnd_flex_validation_rule_lines fvrl
5087 WHERE application_id = p_application_id
5088 AND id_flex_code = p_id_flex_code
5089 AND id_flex_num = p_id_flex_num
5090 AND flex_validation_rule_name = p_flex_validation_rule_name
5091 AND rule_line_id = p_rule_line_id
5092 AND NOT EXISTS
5093 (SELECT null
5094 FROM fnd_flex_validation_rules fvr
5095 WHERE fvr.application_id = fvrl.application_id
5096 AND fvr.id_flex_code = fvrl.id_flex_code
5097 AND fvr.id_flex_num = fvrl.id_flex_num
5098 AND fvr.flex_validation_rule_name =fvrl.flex_validation_rule_name);
5099 x_message := SQL%rowcount || ' row(s) deleted.';
5100 GOTO return_success;
5101 EXCEPTION
5102 WHEN OTHERS THEN
5103 x_message :=
5104 'Unable to delete from FND_FLEX_VALIDATION_RULE_LINES. ' ||
5105 Sqlerrm;
5106 GOTO return_error;
5107 END;
5108 END IF;
5109
5110 IF (NOT fetch_kff_flx(p_application_id,
5111 p_id_flex_code,
5112 l_kff_flx,
5113 x_message)) THEN
5114 GOTO return_error;
5115 END IF;
5116
5117 IF (NOT fetch_kff_str(l_kff_flx,
5118 p_id_flex_num,
5119 l_kff_str,
5120 x_message)) THEN
5121 GOTO return_error;
5122 END IF;
5123
5124 IF (NOT fetch_kff_cvr(l_kff_str,
5125 p_flex_validation_rule_name,
5126 l_kff_cvr,
5127 x_message)) THEN
5128 GOTO return_error;
5129 END IF;
5130
5131 IF (p_rule LIKE 'H.%') THEN
5132 IF (NOT fetch_kff_cvl(l_kff_cvr,
5133 p_rule_line_id,
5134 l_kff_cvl,
5135 x_message)) THEN
5136 GOTO return_error;
5137 END IF;
5138 ELSIF (p_rule LIKE 'I.%') THEN
5139 IF (NOT fetch_kff_cvi(l_kff_cvr,
5140 p_rule_line_id,
5141 l_kff_cvi,
5142 x_message)) THEN
5143 GOTO return_error;
5144 END IF;
5145 ELSIF (p_rule LIKE 'J.%') THEN
5146 IF (NOT fetch_kff_cve(l_kff_cvr,
5147 p_rule_line_id,
5148 l_kff_cve,
5149 x_message)) THEN
5150 GOTO return_error;
5151 END IF;
5152 END IF;
5153
5154 IF (p_rule = 'H.02') THEN
5155 --
5156 -- Problems in flags.
5157 --
5158 IF (NOT lookup_code_exists('YES_NO',
5159 l_kff_cvl.enabled_flag)) THEN
5160 l_kff_cvl.enabled_flag := 'Y';
5161 END IF;
5162
5163 IF (NOT lookup_code_exists('INCLUDE_EXCLUDE',
5164 l_kff_cvl.include_exclude_indicator)) THEN
5165 l_kff_cvl.include_exclude_indicator := 'I';
5166 END IF;
5167
5168 BEGIN
5169 UPDATE fnd_flex_validation_rule_lines SET
5170 enabled_flag = l_kff_cvl.enabled_flag,
5171 include_exclude_indicator = l_kff_cvl.include_exclude_indicator,
5172 last_update_date = Sysdate,
5173 last_updated_by = 1
5174 WHERE application_id = l_kff_cvl.application_id
5175 AND id_flex_code = l_kff_cvl.id_flex_code
5176 AND id_flex_num = l_kff_cvl.id_flex_num
5177 AND flex_validation_rule_name = l_kff_cvl.flex_validation_rule_name
5178 AND rule_line_id = l_kff_cvl.rule_line_id;
5179 x_message := SQL%rowcount || ' row(s) updated.';
5180 GOTO return_success;
5181 EXCEPTION
5182 WHEN OTHERS THEN
5183 x_message :=
5184 'Unable to update FND_FLEX_VALIDATION_RULE_LINES. ' || Sqlerrm;
5185 GOTO return_error;
5186 END;
5187 END IF;
5188
5189 IF (p_rule = 'I.01') THEN
5190 --
5191 -- Include Lines without actual Line.
5192 --
5193 BEGIN
5194 DELETE FROM fnd_flex_include_rule_lines firl
5195 WHERE application_id = p_application_id
5196 AND id_flex_code = p_id_flex_code
5197 AND id_flex_num = p_id_flex_num
5198 AND flex_validation_rule_name = p_flex_validation_rule_name
5199 AND rule_line_id = p_rule_line_id
5200 AND NOT EXISTS
5201 (SELECT null
5202 FROM fnd_flex_validation_rule_lines fvrl
5203 WHERE firl.rule_line_id = fvrl.rule_line_id
5204 AND firl.enabled_flag = fvrl.enabled_flag
5205 AND fvrl.include_exclude_indicator = 'I');
5206 x_message := SQL%rowcount || ' row(s) deleted.';
5207 GOTO return_success;
5208 EXCEPTION
5209 WHEN OTHERS THEN
5210 x_message :=
5211 'Unable to delete from FND_FLEX_INCLUDE_RULE_LINES. ' ||
5212 Sqlerrm;
5213 GOTO return_error;
5214 END;
5215 END IF;
5216
5217 IF (p_rule = 'J.01') THEN
5218 --
5219 -- Exclude Lines without actual Line.
5220 --
5221 BEGIN
5222 DELETE FROM fnd_flex_exclude_rule_lines ferl
5223 WHERE application_id = p_application_id
5224 AND id_flex_code = p_id_flex_code
5225 AND id_flex_num = p_id_flex_num
5226 AND flex_validation_rule_name = p_flex_validation_rule_name
5227 AND rule_line_id = p_rule_line_id
5228 AND NOT EXISTS
5229 (SELECT null
5230 FROM fnd_flex_validation_rule_lines fvrl
5231 WHERE ferl.rule_line_id = fvrl.rule_line_id
5232 AND ferl.enabled_flag = fvrl.enabled_flag
5233 AND fvrl.include_exclude_indicator = 'E');
5234 x_message := SQL%rowcount || ' row(s) deleted.';
5235 GOTO return_success;
5236 EXCEPTION
5237 WHEN OTHERS THEN
5238 x_message :=
5239 'Unable to delete from FND_FLEX_EXCLUDE_RULE_LINES. ' ||
5240 Sqlerrm;
5241 GOTO return_error;
5242 END;
5243 END IF;
5244
5245 <<return_success>>
5246 <<return_error>>
5247 RETURN;
5248 EXCEPTION
5249 WHEN OTHERS THEN
5250 x_message := 'fix_kff_cvl: Top level error: ' || Sqlerrm;
5251 END fix_kff_cvl;
5252
5253 -- ===========================================================================
5254 PROCEDURE fix_kff_cvrls(p_rule IN VARCHAR2,
5255 x_message OUT nocopy VARCHAR2)
5256 IS
5257 l_kff_flx kff_flx_type;
5258 l_kff_str kff_str_type;
5259
5260 CURSOR kff_str_cur IS
5261 SELECT *
5262 FROM fnd_id_flex_structures
5263 ORDER BY application_id, id_flex_code, id_flex_num;
5264 BEGIN
5265
5266 IF (p_rule IN ('G.06', 'H.03')) THEN
5267 --
5268 -- CVR fix. Run after triggers are enabled.
5269 -- See $FND_TOP/sql/afffcvr1.sql and afffcvr2.sql
5270 --
5271 FOR kff_str_rec IN kff_str_cur LOOP
5272
5273 IF (NOT fetch_kff_flx(kff_str_rec.application_id,
5274 kff_str_rec.id_flex_code,
5275 l_kff_flx,
5276 x_message)) THEN
5277 GOTO return_error;
5278 END IF;
5279
5280 IF (NOT fetch_kff_str(l_kff_flx,
5281 kff_str_rec.id_flex_num,
5282 l_kff_str,
5283 x_message)) THEN
5284 GOTO return_error;
5285 END IF;
5286
5287 IF (NOT populate_kff_cvrls(l_kff_str,
5288 x_message)) THEN
5289 GOTO return_error;
5290 END IF;
5291 END LOOP;
5292 END IF;
5293
5294 <<return_success>>
5295 <<return_error>>
5296 RETURN;
5297 EXCEPTION
5298 WHEN OTHERS THEN
5299 x_message := 'fix_kff_cvrls: Top level error: ' || Sqlerrm;
5300 END fix_kff_cvrls;
5301
5302 -- ===========================================================================
5303 PROCEDURE fix_kff_flq(p_rule IN VARCHAR2,
5304 p_application_id IN NUMBER,
5305 p_id_flex_code IN VARCHAR2,
5306 p_segment_attribute_type IN VARCHAR2,
5307 x_message OUT nocopy VARCHAR2)
5308 IS
5309 l_kff_flx kff_flx_type;
5310 l_kff_flq kff_flq_type;
5311 BEGIN
5312 IF (p_rule = 'L.01') THEN
5313 --
5314 -- Flex Qualfiers without KFF.
5315 --
5316 BEGIN
5317 DELETE
5318 FROM fnd_segment_attribute_types sat
5319 WHERE application_id = p_application_id
5320 AND id_flex_code = p_id_flex_code
5321 AND segment_attribute_type = p_segment_attribute_type
5322 AND NOT EXISTS
5323 (SELECT null
5324 FROM fnd_id_flexs idf
5325 WHERE idf.application_id = sat.application_id
5326 AND idf.id_flex_code = sat.id_flex_code);
5327 x_message := SQL%rowcount || ' row(s) deleted.';
5328 GOTO return_success;
5329 EXCEPTION
5330 WHEN OTHERS THEN
5331 x_message :=
5332 'Unable to delete from FND_SEGMENT_ATTRIBUTE_TYPES. ' || Sqlerrm;
5333 GOTO return_error;
5334 END;
5335 END IF;
5336
5337 IF (NOT fetch_kff_flx(p_application_id,
5338 p_id_flex_code,
5339 l_kff_flx,
5340 x_message)) THEN
5341 GOTO return_error;
5342 END IF;
5343
5344 IF (NOT fetch_kff_flq(l_kff_flx,
5345 p_segment_attribute_type,
5346 l_kff_flq,
5347 x_message)) THEN
5348 GOTO return_error;
5349 END IF;
5350
5351 IF (p_rule = 'L.02') THEN
5352 --
5353 -- Problems in flags.
5354 --
5355 IF (NOT lookup_code_exists('YES_NO',
5356 l_kff_flq.global_flag)) THEN
5357 l_kff_flq.global_flag := 'N';
5358 END IF;
5359
5360 IF (NOT lookup_code_exists('YES_NO',
5361 l_kff_flq.required_flag)) THEN
5362 l_kff_flq.required_flag := 'N';
5363 END IF;
5364
5365 IF (NOT lookup_code_exists('YES_NO',
5366 l_kff_flq.unique_flag)) THEN
5367 l_kff_flq.unique_flag := 'N';
5368 END IF;
5369
5370 BEGIN
5371 UPDATE fnd_segment_attribute_types SET
5372 global_flag = l_kff_flq.global_flag,
5373 required_flag = l_kff_flq.required_flag,
5374 unique_flag = l_kff_flq.unique_flag,
5375 last_update_date = Sysdate,
5376 last_updated_by = 1
5377 WHERE application_id = l_kff_flq.application_id
5378 AND id_flex_code = l_kff_flq.id_flex_code
5379 AND segment_attribute_type = l_kff_flq.segment_attribute_type;
5380 x_message := SQL%rowcount || ' row(s) updated.';
5381 GOTO return_success;
5382 EXCEPTION
5383 WHEN OTHERS THEN
5384 x_message :=
5385 'Unable to update FND_SEGMENT_ATTRIBUTE_TYPES. ' || Sqlerrm;
5386 GOTO return_error;
5387 END;
5388 END IF;
5389
5390 <<return_success>>
5391 <<return_error>>
5392 RETURN;
5393 EXCEPTION
5394 WHEN OTHERS THEN
5395 x_message := 'fix_kff_flq: Top level error: ' || Sqlerrm;
5396 END fix_kff_flq;
5397
5398 -- ===========================================================================
5399 PROCEDURE fix_kff_qlv(p_rule IN VARCHAR2,
5400 p_application_id IN NUMBER,
5401 p_id_flex_code IN VARCHAR2,
5402 p_id_flex_num IN NUMBER,
5403 p_application_column_name IN VARCHAR2,
5404 p_segment_attribute_type IN VARCHAR2,
5405 x_message OUT nocopy VARCHAR2)
5406 IS
5407 l_kff_flx kff_flx_type;
5408 l_kff_str kff_str_type;
5409 l_kff_seg kff_seg_type;
5410 l_kff_flq kff_flq_type;
5411 l_kff_qlv kff_qlv_type;
5412 BEGIN
5413 IF (p_rule = 'M.01') THEN
5414 --
5415 -- Qualifier assignments without segments.
5416 --
5417 BEGIN
5418 DELETE
5419 FROM fnd_segment_attribute_values sav
5420 WHERE application_id = p_application_id
5421 AND id_flex_code = p_id_flex_code
5422 AND id_flex_num = p_id_flex_num
5423 AND application_column_name = p_application_column_name
5424 AND segment_attribute_type = p_segment_attribute_type
5425 AND NOT EXISTS
5426 (SELECT null
5427 FROM fnd_id_flex_segments ifsg
5428 WHERE ifsg.application_id = sav.application_id
5429 AND ifsg.id_flex_code = sav.id_flex_code
5430 AND ifsg.id_flex_num = sav.id_flex_num
5431 AND ifsg.application_column_name = sav.application_column_name);
5432 x_message := SQL%rowcount || ' row(s) deleted.';
5433 GOTO return_success;
5434 EXCEPTION
5435 WHEN OTHERS THEN
5436 x_message :=
5437 'Unable to delete from FND_SEGMENT_ATTRIBUTE_VALUES. ' ||Sqlerrm;
5438 GOTO return_error;
5439 END;
5440 END IF;
5441
5442 IF (p_rule = 'M.02') THEN
5443 --
5444 -- Qualifier assignments without qualifiers.
5445 --
5446 BEGIN
5447 DELETE
5448 FROM fnd_segment_attribute_values sav
5449 WHERE application_id = p_application_id
5450 AND id_flex_code = p_id_flex_code
5451 AND id_flex_num = p_id_flex_num
5452 AND application_column_name = p_application_column_name
5453 AND segment_attribute_type = p_segment_attribute_type
5454 AND NOT EXISTS
5455 (SELECT null
5456 FROM fnd_segment_attribute_types sat
5457 WHERE sat.application_id = sav.application_id
5458 AND sat.id_flex_code = sav.id_flex_code
5459 AND sat.segment_attribute_type = sav.segment_attribute_type);
5460 x_message := SQL%rowcount || ' row(s) deleted.';
5461 GOTO return_success;
5462 EXCEPTION
5463 WHEN OTHERS THEN
5464 x_message :=
5465 'Unable to delete from FND_SEGMENT_ATTRIBUTE_VALUES. ' ||Sqlerrm;
5466 GOTO return_error;
5467 END;
5468 END IF;
5469
5470 IF (NOT fetch_kff_flx(p_application_id,
5471 p_id_flex_code,
5472 l_kff_flx,
5473 x_message)) THEN
5474 GOTO return_error;
5475 END IF;
5476
5477 IF (NOT fetch_kff_str(l_kff_flx,
5478 p_id_flex_num,
5479 l_kff_str,
5480 x_message)) THEN
5481 GOTO return_error;
5482 END IF;
5483
5484 IF (NOT fetch_kff_seg(l_kff_str,
5485 p_application_column_name,
5486 l_kff_seg,
5487 x_message)) THEN
5488 GOTO return_error;
5489 END IF;
5490
5491 IF (NOT fetch_kff_flq(l_kff_flx,
5492 p_segment_attribute_type,
5493 l_kff_flq,
5494 x_message)) THEN
5495 GOTO return_error;
5496 END IF;
5497
5498
5499 IF (p_rule = 'M.03') THEN
5500 --
5501 -- No assignments between Segments and Qualifiers.
5502 --
5503 IF (fetch_kff_qlv(l_kff_seg,
5504 l_kff_flq,
5505 l_kff_qlv,
5506 x_message)) THEN
5507 x_message := 'Assignment exists. No need to fix.';
5508 GOTO return_error;
5509 END IF;
5510
5511 BEGIN
5512 INSERT INTO fnd_segment_attribute_values
5513 (
5514 application_id,
5515 id_flex_code,
5516 id_flex_num,
5517 application_column_name,
5518 segment_attribute_type,
5519 attribute_value,
5520
5521 created_by,
5522 creation_date,
5523 last_updated_by,
5524 last_update_date,
5525 last_update_login
5526 )
5527 VALUES
5528 (
5529 l_kff_seg.application_id,
5530 l_kff_seg.id_flex_code,
5531 l_kff_seg.id_flex_num,
5532 l_kff_seg.application_column_name,
5533 l_kff_flq.segment_attribute_type,
5534 l_kff_flq.global_flag,
5535
5536 1,
5537 Sysdate,
5538 1,
5539 Sysdate,
5540 0);
5541 x_message := SQL%rowcount || ' row(s) inserted.';
5542 GOTO return_success;
5543 EXCEPTION
5544 WHEN OTHERS THEN
5545 x_message :=
5546 'Unable to insert into FND_SEGMENT_ATTRIBUTE_VALUES. ' ||Sqlerrm;
5547 GOTO return_error;
5548 END;
5549 END IF;
5550
5551 IF (NOT fetch_kff_qlv(l_kff_seg,
5552 l_kff_flq,
5553 l_kff_qlv,
5554 x_message)) THEN
5555 GOTO return_error;
5556 END IF;
5557
5558 IF (p_rule = 'M.04') THEN
5559 --
5560 -- Problems in flags.
5561 --
5562 IF (NOT lookup_code_exists('YES_NO',
5563 l_kff_qlv.attribute_value)) THEN
5564 l_kff_qlv.attribute_value := l_kff_flq.global_flag;
5565 END IF;
5566
5567 BEGIN
5568 UPDATE fnd_segment_attribute_values SET
5569 attribute_value = l_kff_qlv.attribute_value,
5570 last_update_date = Sysdate,
5571 last_updated_by = 1
5572 WHERE application_id = l_kff_qlv.application_id
5573 AND id_flex_code = l_kff_qlv.id_flex_code
5574 AND id_flex_num = l_kff_qlv.id_flex_num
5575 AND application_column_name = l_kff_qlv.application_column_name
5576 AND segment_attribute_type = l_kff_qlv.segment_attribute_type;
5577 x_message := SQL%rowcount || ' row(s) updated.';
5578 GOTO return_success;
5579 EXCEPTION
5580 WHEN OTHERS THEN
5581 x_message :=
5582 'Unable to update FND_SEGMENT_ATTRIBUTE_VALUES. ' || Sqlerrm;
5583 GOTO return_error;
5584 END;
5585 END IF;
5586
5587 <<return_success>>
5588 <<return_error>>
5589 RETURN;
5590 EXCEPTION
5591 WHEN OTHERS THEN
5592 x_message := 'fix_kff_qlv: Top level error: ' || Sqlerrm;
5593 END fix_kff_qlv;
5594 -- ===========================================================================
5595 PROCEDURE fix_kff_sgq(p_rule IN VARCHAR2,
5596 p_application_id IN NUMBER,
5597 p_id_flex_code IN VARCHAR2,
5598 p_segment_attribute_type IN VARCHAR2,
5599 p_value_attribute_type IN VARCHAR2,
5600 x_message OUT nocopy VARCHAR2)
5601 IS
5602 l_tbl tbl_type;
5603 l_col col_type;
5604 l_kff_flx kff_flx_type;
5605 l_kff_flq kff_flq_type;
5606 l_kff_sgq kff_sgq_type;
5607 l_dummy VARCHAR2(100);
5608 BEGIN
5609 IF (p_rule = 'N.03') THEN
5610 --
5611 -- Segment Qualifiers without Flexfield Qualifier.
5612 --
5613 BEGIN
5614 DELETE
5615 FROM fnd_value_attribute_types vat
5616 WHERE application_id = p_application_id
5617 AND id_flex_code = p_id_flex_code
5618 AND segment_attribute_type = p_segment_attribute_type
5619 AND value_attribute_type = p_value_attribute_type
5620 AND NOT EXISTS
5621 (SELECT null
5622 FROM fnd_segment_attribute_types sat
5623 WHERE sat.application_id = vat.application_id
5624 AND sat.id_flex_code = vat.id_flex_code
5625 AND sat.segment_attribute_type = vat.segment_attribute_type);
5626 x_message := SQL%rowcount || ' row(s) deleted.';
5627 GOTO return_success;
5628 EXCEPTION
5629 WHEN OTHERS THEN
5630 x_message :=
5631 'Unable to delete from FND_VALUE_ATTRIBUTE_TYPES. ' || Sqlerrm;
5632 GOTO return_error;
5633 END;
5634 END IF;
5635
5636 IF (NOT fetch_kff_flx(p_application_id,
5637 p_id_flex_code,
5638 l_kff_flx,
5639 x_message)) THEN
5640 GOTO return_error;
5641 END IF;
5642
5643 IF (NOT fetch_kff_flq(l_kff_flx,
5644 p_segment_attribute_type,
5645 l_kff_flq,
5646 x_message)) THEN
5647 GOTO return_error;
5648 END IF;
5649
5650 IF (NOT fetch_kff_sgq(l_kff_flq,
5651 p_value_attribute_type,
5652 l_kff_sgq,
5653 x_message)) THEN
5654 GOTO return_error;
5655 END IF;
5656
5657 IF (p_rule = 'N.06') THEN
5658 --
5659 -- Qualifier column is not registered properly.
5660 --
5661 IF (NOT fetch_tbl(l_kff_flx.table_application_id,
5662 l_kff_flx.application_table_name,
5663 l_tbl,
5664 x_message)) THEN
5665 GOTO return_error;
5666 END IF;
5667
5668 IF (NOT fetch_col(l_tbl,
5669 l_kff_sgq.application_column_name,
5670 l_col,
5671 x_message)) THEN
5672 GOTO return_error;
5673 END IF;
5674
5675 IF (l_col.flexfield_usage_code = 'Q') THEN
5676 x_message := 'Qualifier column is properly registered. No need to fix.';
5677 GOTO return_error;
5678 END IF;
5679
5680 IF (update_fnd_columns(l_col,
5681 'Q',
5682 NULL, -- l_kff_flx.application_id,
5683 NULL, -- l_kff_flx.id_flex_code,
5684 x_message)) THEN
5685 GOTO return_success;
5686 ELSE
5687 GOTO return_error;
5688 END IF;
5689 END IF;
5690
5691 IF (p_rule = 'N.07') THEN
5692 --
5693 -- Problem in flags.
5694 --
5695 IF (NOT lookup_code_exists('YES_NO',
5696 l_kff_sgq.required_flag)) THEN
5697 l_kff_sgq.required_flag := 'Y';
5698 END IF;
5699
5700 BEGIN
5701 UPDATE fnd_value_attribute_types SET
5702 required_flag = l_kff_sgq.required_flag,
5703 last_update_date = Sysdate,
5704 last_updated_by = 1
5705 WHERE application_id = l_kff_sgq.application_id
5706 AND id_flex_code = l_kff_sgq.id_flex_code
5707 AND segment_attribute_type = l_kff_sgq.segment_attribute_type
5708 AND value_attribute_type = l_kff_sgq.value_attribute_type;
5709 x_message := SQL%rowcount || ' row(s) updated.';
5710 GOTO return_success;
5711 EXCEPTION
5712 WHEN OTHERS THEN
5713 x_message :=
5714 'Unable to update FND_VALUE_ATTRIBUTE_TYPES. ' || Sqlerrm;
5715 GOTO return_error;
5716 END;
5717 END IF;
5718
5719 <<return_success>>
5720 <<return_error>>
5721 RETURN;
5722 EXCEPTION
5723 WHEN OTHERS THEN
5724 x_message := 'fix_kff_sgq: Top level error: ' || Sqlerrm;
5725 END fix_kff_sgq;
5726
5727 -- ===========================================================================
5728 PROCEDURE fix_kff_fvq(p_rule IN VARCHAR2,
5729 p_id_flex_application_id IN NUMBER,
5730 p_id_flex_code IN VARCHAR2,
5731 p_segment_attribute_type IN VARCHAR2,
5732 p_value_attribute_type IN VARCHAR2,
5733 p_flex_value_set_id IN NUMBER,
5734 x_message OUT nocopy VARCHAR2)
5735 IS
5736 BEGIN
5737 IF (p_rule = 'O.01') THEN
5738 --
5739 -- Validation Qualifiers without Segment qualifier.
5740 --
5741 BEGIN
5742 DELETE
5743 FROM fnd_flex_validation_qualifiers fvq
5744 WHERE id_flex_application_id = p_id_flex_application_id
5745 AND id_flex_code = p_id_flex_code
5746 AND segment_attribute_type = p_segment_attribute_type
5747 AND value_attribute_type = p_value_attribute_type
5748 AND flex_value_set_id = p_flex_value_set_id
5749 AND NOT EXISTS
5750 (SELECT null
5751 FROM fnd_value_attribute_types vat
5752 WHERE vat.application_id = fvq.id_flex_application_id
5753 AND vat.id_flex_code = fvq.id_flex_code
5754 AND vat.segment_attribute_type = fvq.segment_attribute_type
5755 AND vat.value_attribute_type = fvq.value_attribute_type);
5756 x_message := SQL%rowcount || ' row(s) deleted.';
5757 GOTO return_success;
5758 EXCEPTION
5759 WHEN OTHERS THEN
5760 x_message :=
5761 'Unable to delete from FND_FLEX_VALIDATION_QUALIFIERS. ' ||
5762 Sqlerrm;
5763 GOTO return_error;
5764 END;
5765 END IF;
5766
5767 IF (p_rule = 'O.02') THEN
5768 --
5769 -- Validation Qualifiers without Value sets.
5770 --
5771 BEGIN
5772 DELETE
5773 FROM fnd_flex_validation_qualifiers fvq
5774 WHERE id_flex_application_id = p_id_flex_application_id
5775 AND id_flex_code = p_id_flex_code
5776 AND segment_attribute_type = p_segment_attribute_type
5777 AND value_attribute_type = p_value_attribute_type
5778 AND flex_value_set_id = p_flex_value_set_id
5779 AND NOT EXISTS
5780 (SELECT null
5781 FROM fnd_flex_value_sets fvs
5782 WHERE fvs.flex_value_set_id = fvq.flex_value_set_id);
5783 x_message := SQL%rowcount || ' row(s) deleted.';
5784 GOTO return_success;
5785 EXCEPTION
5786 WHEN OTHERS THEN
5787 x_message :=
5788 'Unable to delete from FND_FLEX_VALIDATION_QUALIFIERS. ' ||
5789 Sqlerrm;
5790 GOTO return_error;
5791 END;
5792 END IF;
5793
5794 <<return_success>>
5795 <<return_error>>
5796 RETURN;
5797 EXCEPTION
5798 WHEN OTHERS THEN
5799 x_message := 'fix_kff_fvq: Top level error: ' || Sqlerrm;
5800 END fix_kff_fvq;
5801
5802 -- ===========================================================================
5803 PROCEDURE fix_kff_col(p_rule IN VARCHAR2,
5804 p_application_id IN NUMBER,
5805 p_table_name IN VARCHAR2,
5806 p_column_name IN VARCHAR2,
5807 x_message OUT nocopy VARCHAR2)
5808 IS
5809 l_tbl tbl_type;
5810 l_col col_type;
5811 l_kff_flx kff_flx_type;
5812 l_kff_sgq kff_sgq_type;
5813 BEGIN
5814 IF (NOT fetch_tbl(p_application_id,
5815 p_table_name,
5816 l_tbl,
5817 x_message)) THEN
5818 GOTO return_error;
5819 END IF;
5820
5821 IF (NOT fetch_col(l_tbl,
5822 p_column_name,
5823 l_col,
5824 x_message)) THEN
5825 GOTO return_error;
5826 END IF;
5827
5828 IF (p_rule = 'P.01') THEN
5829 --
5830 -- 'I' columns.
5831 --
5832 IF (l_col.flexfield_usage_code <> 'I') THEN
5833 x_message := 'This is not a ''I'' column.';
5834 GOTO return_error;
5835 END IF;
5836
5837 BEGIN
5838 SELECT *
5839 INTO l_kff_flx
5840 FROM fnd_id_flexs
5841 WHERE table_application_id = l_tbl.application_id
5842 AND application_table_name = l_tbl.table_name
5843 AND unique_id_column_name = l_col.column_name;
5844 x_message := ('This column is used by ' ||
5845 To_char(l_kff_flx.application_id) || '/' ||
5846 l_kff_flx.id_flex_code ||
5847 '. No need to fix.');
5848 GOTO return_error;
5849 EXCEPTION
5850 WHEN no_data_found THEN
5851 NULL;
5852 WHEN OTHERS THEN
5853 x_message := 'Unable to select from FND_ID_FLEXS. ' || Sqlerrm;
5854 GOTO return_error;
5855 END;
5856
5857 IF (update_fnd_columns(l_col, 'N', NULL, NULL, x_message)) THEN
5858 GOTO return_success;
5859 ELSE
5860 GOTO return_error;
5861 END IF;
5862 END IF;
5863
5864 IF (p_rule = 'P.02') THEN
5865 --
5866 -- 'S' columns.
5867 --
5868 IF (l_col.flexfield_usage_code <> 'S') THEN
5869 x_message := 'This is not a ''S'' column.';
5870 GOTO return_error;
5871 END IF;
5872
5873 BEGIN
5874 SELECT *
5875 INTO l_kff_flx
5876 FROM fnd_id_flexs
5877 WHERE table_application_id = l_tbl.application_id
5878 AND application_table_name = l_tbl.table_name
5879 AND set_defining_column_name = l_col.column_name;
5880 x_message := ('This column is used by ' ||
5881 To_char(l_kff_flx.application_id) || '/' ||
5882 l_kff_flx.id_flex_code ||
5883 '. No need to fix.');
5884 GOTO return_error;
5885 EXCEPTION
5886 WHEN no_data_found THEN
5887 NULL;
5888 WHEN OTHERS THEN
5889 x_message := 'Unable to select from FND_ID_FLEXS. ' || Sqlerrm;
5890 GOTO return_error;
5891 END;
5892
5893 IF (update_fnd_columns(l_col, 'N', NULL, NULL, x_message)) THEN
5894 GOTO return_success;
5895 ELSE
5896 GOTO return_error;
5897 END IF;
5898 END IF;
5899
5900 IF (p_rule = 'P.03') THEN
5901 --
5902 -- 'K' columns.
5903 --
5904 IF (l_col.flexfield_usage_code <> 'K') THEN
5905 x_message := 'This is not a ''K'' column.';
5906 GOTO return_error;
5907 END IF;
5908
5909 BEGIN
5910 SELECT *
5911 INTO l_kff_flx
5912 FROM fnd_id_flexs
5913 WHERE table_application_id = l_tbl.application_id
5914 AND application_table_name = l_tbl.table_name;
5915 x_message := ('This column is possibly used by ' ||
5916 To_char(l_kff_flx.application_id) || '/' ||
5917 l_kff_flx.id_flex_code ||
5918 '. No need to fix.');
5919 GOTO return_error;
5920 EXCEPTION
5921 WHEN no_data_found THEN
5922 NULL;
5923 WHEN OTHERS THEN
5924 x_message := 'Unable to select from FND_ID_FLEXS. ' || Sqlerrm;
5925 GOTO return_error;
5926 END;
5927
5928 IF (update_fnd_columns(l_col, 'N', NULL, NULL, x_message)) THEN
5929 GOTO return_success;
5930 ELSE
5931 GOTO return_error;
5932 END IF;
5933 END IF;
5934
5935 IF (p_rule = 'P.04') THEN
5936 --
5937 -- 'Q' columns.
5938 --
5939 IF (l_col.flexfield_usage_code <> 'Q') THEN
5940 x_message := 'This is not a ''Q'' column.';
5941 GOTO return_error;
5942 END IF;
5943
5944 BEGIN
5945 SELECT *
5946 INTO l_kff_flx
5947 FROM fnd_id_flexs
5948 WHERE table_application_id = l_tbl.application_id
5949 AND application_table_name = l_tbl.table_name;
5950 BEGIN
5951 SELECT *
5952 INTO l_kff_sgq
5953 FROM fnd_value_attribute_types
5954 WHERE application_id = l_kff_flx.application_id
5955 AND id_flex_code = l_kff_flx.id_flex_code
5956 AND application_column_name = l_col.column_name;
5957 x_message := ('This column is used by ' ||
5958 To_char(l_kff_flx.application_id) || '/' ||
5959 l_kff_flx.id_flex_code || '/' ||
5960 l_kff_sgq.segment_attribute_type || '/' ||
5961 l_kff_sgq.value_attribute_type ||
5962 '. No need to fix.');
5963 GOTO return_error;
5964 EXCEPTION
5965 WHEN no_data_found THEN
5966 NULL;
5967 WHEN OTHERS THEN
5968 x_message :=
5969 'Unable to select from FND_VALUE_ATTRIBUTE_TYPES. ' ||
5970 Sqlerrm;
5971 GOTO return_error;
5972 END;
5973 EXCEPTION
5974 WHEN no_data_found THEN
5975 NULL;
5976 WHEN OTHERS THEN
5977 x_message := 'Unable to select from FND_ID_FLEXS. ' || Sqlerrm;
5978 GOTO return_error;
5979 END;
5980
5981 IF (update_fnd_columns(l_col, 'N', NULL, NULL, x_message)) THEN
5982 GOTO return_success;
5983 ELSE
5984 GOTO return_error;
5985 END IF;
5986 END IF;
5987
5988 <<return_success>>
5989 <<return_error>>
5990 RETURN;
5991 EXCEPTION
5992 WHEN OTHERS THEN
5993 x_message := 'fix_kff_col: Top level error: ' || Sqlerrm;
5994 END fix_kff_col;
5995
5996 -- ===========================================================================
5997 PROCEDURE fix_kff_fwp(p_rule IN VARCHAR2,
5998 p_application_id IN NUMBER,
5999 p_id_flex_code IN VARCHAR2,
6000 p_id_flex_num IN NUMBER,
6001 p_wf_item_type IN VARCHAR2,
6002 x_message OUT nocopy VARCHAR2)
6003 IS
6004 BEGIN
6005 IF (p_rule = 'R.01') THEN
6006 --
6007 -- FWPs without Structure.
6008 --
6009 BEGIN
6010 DELETE
6011 FROM fnd_flex_workflow_processes fwp
6012 WHERE application_id = p_application_id
6013 AND id_flex_code = p_id_flex_code
6014 AND id_flex_num = p_id_flex_num
6015 AND wf_item_type = p_wf_item_type
6016 AND NOT EXISTS
6017 (SELECT null
6018 FROM fnd_id_flex_structures ifst
6019 WHERE ifst.application_id = fwp.application_id
6020 AND ifst.id_flex_code = fwp.id_flex_code
6021 AND ifst.id_flex_num = fwp.id_flex_num);
6022 x_message := SQL%rowcount || ' row(s) deleted.';
6023 GOTO return_success;
6024 EXCEPTION
6025 WHEN OTHERS THEN
6026 x_message :=
6027 'Unable to delete from FND_FLEX_WORKFLOW_PROCESSES. ' || Sqlerrm;
6028 GOTO return_error;
6029 END;
6030 END IF;
6031
6032 IF (p_rule = 'R.02') THEN
6033 --
6034 -- FWPs without WF Items.
6035 --
6036 BEGIN
6037 DELETE
6038 FROM fnd_flex_workflow_processes fwp
6039 WHERE application_id = p_application_id
6040 AND id_flex_code = p_id_flex_code
6041 AND id_flex_num = p_id_flex_num
6042 AND wf_item_type = p_wf_item_type
6043 AND NOT exists
6044 (SELECT null
6045 FROM wf_item_types wit
6046 WHERE wit.name = fwp.wf_item_type);
6047 x_message := SQL%rowcount || ' row(s) deleted.';
6048 GOTO return_success;
6049 EXCEPTION
6050 WHEN OTHERS THEN
6051 x_message :=
6052 'Unable to delete from FND_FLEX_WORKFLOW_PROCESSES. ' || Sqlerrm;
6053 GOTO return_error;
6054 END;
6055 END IF;
6056
6057 <<return_success>>
6058 <<return_error>>
6059 RETURN;
6060 EXCEPTION
6061 WHEN OTHERS THEN
6062 x_message := 'fix_kff_fwp: Top level error: ' || Sqlerrm;
6063 END fix_kff_fwp;
6064
6065 BEGIN
6066 g_newline := fnd_global.newline;
6067 END fnd_flex_diagnose;