[Home] [Help]
PACKAGE BODY: APPS.FND_FLEX_SERVER2
Source
1 PACKAGE BODY fnd_flex_server2 AS
2 /* $Header: AFFFSV2B.pls 120.2.12010000.2 2010/02/25 09:46:36 nareshku ship $ */
3
4 g_line_size NUMBER := 240; /* Maximum line size. */
5 g_indent NUMBER := 1; /* Indentation in Log File. */
6 g_numof_errors NUMBER := 0; /* Number of errors. */
7 chr_newline VARCHAR2(8) := fnd_global.newline;
8
9 ---------------
10 -- NOTES
11 --
12
13 --------
14 -- PRIVATE TYPES
15 --
16
17 ------------
18 -- PRIVATE CONSTANTS
19 --
20
21 -- Cross-validation rule caching and optimization
22 --
23 CACHE_DELIMITER VARCHAR2(10); -- := fnd_global.local_chr(0);
24
25 -- This should be moved back to key validation engine when it is broken
26 -- up. For now it is a duplicate of the definition in FND_FLEX_SERVER.
27 --
28 MAX_NSEGS CONSTANT NUMBER := 30;
29
30 cvrule_clause_begin CONSTANT VARCHAR2(2000) :=
31 'select R.FLEX_VALIDATION_RULE_NAME ' ||
32 ' from FND_FLEX_VALIDATION_RULES R ';
33
34 cvrule_clause_exclude_begin CONSTANT VARCHAR2(2000) :=
35 ', FND_FLEX_EXCLUDE_RULE_LINES L ';
36
37 cvrule_clause_where CONSTANT VARCHAR2(2000) :=
38 ' where R.ENABLED_FLAG = ''Y'' ' ||
39 ' and ( (:VDATE is null) ' ||
40 ' or ( ( R.START_DATE_ACTIVE is null ' ||
41 ' or R.START_DATE_ACTIVE <= :VDATE) ' ||
42 ' and ( R.END_DATE_ACTIVE is null ' ||
43 ' or R.END_DATE_ACTIVE >= :VDATE))) ' ||
44 ' and R.APPLICATION_ID = :APID ' ||
45 ' and R.ID_FLEX_CODE = :CODE ' ||
46 ' and R.ID_FLEX_NUM = :NUM ';
47
48 cvrule_clause_exclude_mid CONSTANT VARCHAR2(2000) :=
49 'and R.FLEX_VALIDATION_RULE_NAME = L.FLEX_VALIDATION_RULE_NAME ';
50
51 cvrule_clause_include_mid CONSTANT VARCHAR2(2000) :=
52 'MINUS select L.FLEX_VALIDATION_RULE_NAME ' ||
53 ' from FND_FLEX_INCLUDE_RULE_LINES L ' ||
54 ' where 1 = 1 ';
55
56 cvrule_clause_end CONSTANT VARCHAR2(2000) :=
57 'and L.APPLICATION_ID = :APID ' ||
58 'and L.ID_FLEX_CODE = :CODE ' ||
59 'and L.ID_FLEX_NUM = :NUM ' ||
60 'and L.ENABLED_FLAG = ''Y'' ';
61
62 -------------
63 -- EXCEPTIONS
64 --
65
66
67 /* -------------------------------------------------------------------- */
68 /* Private global variables */
69 /* -------------------------------------------------------------------- */
70 -- ==================================================
71 -- CACHING
72 -- ==================================================
73
74 g_cache_return_code VARCHAR2(30);
75 g_cache_key VARCHAR2(2000);
76 g_cache_value fnd_plsql_cache.generic_cache_value_type;
77 g_cache_values fnd_plsql_cache.generic_cache_values_type;
78 g_cache_numof_values NUMBER;
79
80 -- --------------------------------------------------
81 -- gks : Get KeyStruct Cache.
82 -- --------------------------------------------------
83 gks_cache_controller fnd_plsql_cache.cache_1to1_controller_type;
84 gks_cache_storage fnd_plsql_cache.generic_cache_values_type;
85
86 -- --------------------------------------------------
87 -- gds : Get DescStruct Cache.
88 -- --------------------------------------------------
89 gds_cache_controller fnd_plsql_cache.cache_1to1_controller_type;
90 gds_cache_storage fnd_plsql_cache.generic_cache_values_type;
91
92 -- --------------------------------------------------
93 -- xvc : Cross validated combinations
94 -- --------------------------------------------------
95 xvc_cache_controller fnd_plsql_cache.cache_1to1_controller_type;
96 xvc_cache_storage fnd_plsql_cache.generic_cache_values_type;
97
98 -- --------------------------------------------------
99 -- coc : Columns cache
100 -- --------------------------------------------------
101 coc_cache_controller fnd_plsql_cache.cache_1tom_controller_type;
102 coc_cache_storage fnd_plsql_cache.generic_cache_values_type;
103
104 -- --------------------------------------------------
105 -- gas : Get All SegQuals Cache
106 -- --------------------------------------------------
107 gas_cache_controller fnd_plsql_cache.cache_1tom_controller_type;
108 gas_cache_storage fnd_plsql_cache.generic_cache_values_type;
109
110 -- --------------------------------------------------
111 -- gqs : Get QualSegs Cache
112 -- --------------------------------------------------
113 gqs_cache_controller fnd_plsql_cache.cache_1tom_controller_type;
114 gqs_cache_storage fnd_plsql_cache.generic_cache_values_type;
115
116 /* -------------------------------------------------------------------- */
117 /* Private definitions */
118 /* -------------------------------------------------------------------- */
119
120 FUNCTION cv_rule_violated(nsegs IN NUMBER,
121 segs IN FND_FLEX_SERVER1.StringArray,
122 segfmt IN FND_FLEX_SERVER1.SegFormats,
123 fstruct IN FND_FLEX_SERVER1.FlexStructId,
124 inex IN VARCHAR2,
125 v_date IN DATE,
126 rule_name OUT nocopy VARCHAR2) RETURN BOOLEAN;
127
128 FUNCTION x_cv_rule_select(fstruct IN FND_FLEX_SERVER1.FlexStructId,
129 v_date IN DATE,
130 bad_rule OUT nocopy VARCHAR2) RETURN NUMBER;
131
132 FUNCTION x_xvc_check_cache(fstruct IN FND_FLEX_SERVER1.FlexStructId,
133 v_date IN DATE,
134 p_cat_segs IN VARCHAR2,
135 in_cache OUT nocopy BOOLEAN,
136 is_violated OUT nocopy BOOLEAN,
137 rule_name OUT nocopy VARCHAR2) RETURN BOOLEAN;
138
139 FUNCTION x_xvc_update_cache(fstruct IN FND_FLEX_SERVER1.FlexStructId,
140 v_date IN DATE,
141 p_cat_segs IN VARCHAR2,
142 is_violated IN BOOLEAN,
143 rule_name IN VARCHAR2) RETURN BOOLEAN;
144
145 /* ----------------------------------------------------------------------- */
146 /* Private Functions */
147 /* ----------------------------------------------------------------------- */
148
149 /* ----------------------------------------------------------------------- */
150 /* Gets flexfield and structure header information for key flexfieds. */
151 /* Returns FALSE and sets error message on error. */
152 /* ----------------------------------------------------------------------- */
153 FUNCTION get_keystruct(appl_sname IN VARCHAR2,
154 flex_code IN VARCHAR2,
155 select_comb_from_view IN VARCHAR2,
156 flex_num IN NUMBER,
157 flex_struct OUT nocopy FND_FLEX_SERVER1.FlexStructId,
158 struct_info OUT nocopy FND_FLEX_SERVER1.FlexStructInfo,
159 cctbl_info OUT nocopy FND_FLEX_SERVER1.CombTblInfo)
160 RETURN BOOLEAN
161 IS
162 BEGIN
163 -- Get all required info about the desired flexfield structure.
164 -- Note exceptions handle the case that the structure not found or not unique.
165 --
166 g_cache_key := (appl_sname || '.' || flex_code || '.' ||
167 flex_num || '.' || select_comb_from_view);
168 fnd_plsql_cache.generic_1to1_get_value(gks_cache_controller,
169 gks_cache_storage,
170 g_cache_key,
171 g_cache_value,
172 g_cache_return_code);
173 IF (g_cache_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
174 NULL;
175 ELSE
176 SELECT
177 f.application_id, f.table_application_id,
178 t.table_id, f.application_table_name,
179 Nvl(select_comb_from_view, f.application_table_name),
180 f.application_table_type, f.unique_id_column_name,
181 f.set_defining_column_name, f.dynamic_inserts_feasible_flag,
182 f.maximum_concatenation_len, f.concatenation_len_warning
183 INTO
184 g_cache_value.number_1, g_cache_value.number_2,
185 g_cache_value.number_3, g_cache_value.varchar2_1,
186 g_cache_value.varchar2_2,
187 g_cache_value.varchar2_3, g_cache_value.varchar2_4,
188 g_cache_value.varchar2_5, g_cache_value.varchar2_6,
189 g_cache_value.number_4, g_cache_value.varchar2_7
190 FROM fnd_id_flexs f, fnd_tables t, fnd_application a
191 WHERE f.id_flex_code = flex_code
192 AND f.application_id = a.application_id
193 AND a.application_short_name = appl_sname
194 AND t.application_id = f.table_application_id
195 AND t.table_name = f.application_table_name;
196
197 -- NOTE: Should select from structures _VL table if selecting on
198 -- structure name.
199 SELECT
200 enabled_flag, concatenated_segment_delimiter,
201 cross_segment_validation_flag, dynamic_inserts_allowed_flag
202 INTO
203 g_cache_value.varchar2_8,
204 g_cache_value.varchar2_9,
205 g_cache_value.varchar2_10,
206 g_cache_value.varchar2_11
207 FROM fnd_id_flex_structures
208 WHERE application_id = g_cache_value.number_1
209 AND id_flex_code = flex_code
210 AND id_flex_num = flex_num;
211
212 fnd_plsql_cache.generic_1to1_put_value(gks_cache_controller,
213 gks_cache_storage,
214 g_cache_key,
215 g_cache_value);
216 END IF;
217
218 flex_struct.isa_key_flexfield := TRUE;
219 flex_struct.application_id := g_cache_value.number_1;
220 flex_struct.id_flex_code := flex_code;
221 flex_struct.id_flex_num := flex_num;
222
223 cctbl_info.table_application_id := g_cache_value.number_2;
224 cctbl_info.combination_table_id := g_cache_value.number_3;
225 cctbl_info.application_table_name := g_cache_value.varchar2_1;
226 cctbl_info.select_comb_from := g_cache_value.varchar2_2;
227 cctbl_info.application_table_type := g_cache_value.varchar2_3;
228 cctbl_info.unique_id_column_name := g_cache_value.varchar2_4;
229 cctbl_info.set_defining_column_name := g_cache_value.varchar2_5;
230
231 struct_info.dynamic_inserts_feasible_flag := g_cache_value.varchar2_6;
232 struct_info.maximum_concatenation_len := g_cache_value.number_4;
233 struct_info.concatenation_len_warning := g_cache_value.varchar2_7;
234
235 struct_info.enabled_flag := g_cache_value.varchar2_8;
236 struct_info.concatenated_segment_delimiter := g_cache_value.varchar2_9;
237 struct_info.cross_segment_validation_flag := g_cache_value.varchar2_10;
238 struct_info.dynamic_inserts_allowed_flag := g_cache_value.varchar2_11;
239
240 return(TRUE);
241
242 EXCEPTION
243 WHEN NO_DATA_FOUND then
244 FND_MESSAGE.set_name('FND', 'FLEX-CANNOT FIND STRUCT DEF');
245 FND_MESSAGE.set_token('ROUTINE', 'SV2.GET_KEYSTRUCT');
246 FND_MESSAGE.set_token('APPL', appl_sname);
247 FND_MESSAGE.set_token('CODE', flex_code);
248 FND_MESSAGE.set_token('NUM', to_char(flex_num));
249 return(FALSE);
250 WHEN TOO_MANY_ROWS then
251 FND_MESSAGE.set_name('FND', 'FLEX-DUPLICATE STRUCT DEF');
252 FND_MESSAGE.set_token('ROUTINE', 'SV2.GET_KEYSTRUCT');
253 FND_MESSAGE.set_token('APPL', appl_sname);
254 FND_MESSAGE.set_token('CODE', flex_code);
255 FND_MESSAGE.set_token('NUM', to_char(flex_num));
256 return(FALSE);
257 WHEN OTHERS then
258 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
259 FND_MESSAGE.set_token('MSG', 'SV2.get_keystruct() exception: ' || SQLERRM);
260 return(FALSE);
261
262 END get_keystruct;
263
264 /* ----------------------------------------------------------------------- */
265 /* Function to get descriptive flexfield information. */
266 /* Returns TRUE and DescFlexInfo on success or FALSE and sets */
267 /* FND_MESSAGE to error name if not found or error. */
268 /* ----------------------------------------------------------------------- */
269
270 FUNCTION get_descstruct(flex_app_sname IN VARCHAR2,
271 desc_flex_name IN VARCHAR2,
272 dfinfo OUT nocopy FND_FLEX_SERVER1.DescFlexInfo)
273 RETURN BOOLEAN
274 IS
275 BEGIN
276 g_cache_key := flex_app_sname || '.' || desc_flex_name;
277 fnd_plsql_cache.generic_1to1_get_value(gds_cache_controller,
278 gds_cache_storage,
279 g_cache_key,
280 g_cache_value,
281 g_cache_return_code);
282
283 IF (g_cache_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
284 NULL;
285 ELSE
286 SELECT
287 df.application_id, df.descriptive_flexfield_name,
288 df.description, df.table_application_id,
289 df.application_table_name, t.table_id,
290 df.context_required_flag, df.context_column_name,
291 df.context_user_override_flag, df.concatenated_segment_delimiter,
292 df.protected_flag, df.default_context_value,
293 df.default_context_field_name, df.context_override_value_set_id,
294 df.context_default_type, df.context_default_value,
295 df.context_runtime_property_funct
296 INTO
297 g_cache_value.number_1, g_cache_value.varchar2_1,
298 g_cache_value.varchar2_2, g_cache_value.number_2,
299 g_cache_value.varchar2_3, g_cache_value.number_3,
300 g_cache_value.varchar2_4, g_cache_value.varchar2_5,
301 g_cache_value.varchar2_6, g_cache_value.varchar2_7,
302 g_cache_value.varchar2_8, g_cache_value.varchar2_9,
303 g_cache_value.varchar2_10, g_cache_value.number_4,
304 g_cache_value.varchar2_11, g_cache_value.varchar2_12,
305 g_cache_value.varchar2_13
306 FROM fnd_tables t, fnd_descriptive_flexs_vl df, fnd_application a
307 WHERE a.application_short_name = flex_app_sname
308 AND df.application_id = a.application_id
309 AND df.descriptive_flexfield_name = desc_flex_name
310 AND t.application_id = df.table_application_id
311 AND t.table_name = df.application_table_name;
312
313 fnd_plsql_cache.generic_1to1_put_value(gds_cache_controller,
314 gds_cache_storage,
315 g_cache_key,
316 g_cache_value);
317 END IF;
318
319 dfinfo.application_id := g_cache_value.number_1;
320 dfinfo.name := g_cache_value.varchar2_1;
321 dfinfo.description := g_cache_value.varchar2_2;
322 dfinfo.table_appl_id := g_cache_value.number_2;
323 dfinfo.table_name := g_cache_value.varchar2_3;
324 dfinfo.table_id := g_cache_value.number_3;
325 dfinfo.context_required := g_cache_value.varchar2_4;
326 dfinfo.context_column := g_cache_value.varchar2_5;
327 dfinfo.context_override := g_cache_value.varchar2_6;
328 dfinfo.segment_delimiter := g_cache_value.varchar2_7;
329 dfinfo.protected_flag := g_cache_value.varchar2_8;
330 dfinfo.default_context := g_cache_value.varchar2_9;
331 dfinfo.reference_field := g_cache_value.varchar2_10;
332 dfinfo.context_override_value_set_id := g_cache_value.number_4;
333 dfinfo.context_default_type := g_cache_value.varchar2_11;
334 dfinfo.context_default_value := g_cache_value.varchar2_12;
335 dfinfo.context_runtime_property_funct := g_cache_value.varchar2_13;
336
337 return(TRUE);
338
339 EXCEPTION
340 WHEN NO_DATA_FOUND then
341 FND_MESSAGE.set_name('FND', 'FLEX-DESC DEF NOT FOUND');
342 FND_MESSAGE.set_token('APPID', flex_app_sname);
343 FND_MESSAGE.set_token('DESCR_FLEX_NAME', desc_flex_name);
344 return(FALSE);
345 WHEN OTHERS then
346 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
347 FND_MESSAGE.set_token('MSG','SV2.get_descstruct() exception: ' || SQLERRM);
348 return(FALSE);
349
350 END get_descstruct;
351
352 /* ----------------------------------------------------------------------- */
353 /* Gets column names, column types, segment value set formats and */
354 /* maximum sizes for all enabled segments of the given key or */
355 /* descriptive flexfield structure. Error if no key segments found. */
356 /* Returns FALSE and sets error message on error. */
357 /* ----------------------------------------------------------------------- */
358 FUNCTION get_struct_cols(fstruct IN FND_FLEX_SERVER1.FlexStructId,
359 table_apid IN NUMBER,
360 table_id IN NUMBER,
361 n_columns OUT nocopy NUMBER,
362 cols OUT nocopy FND_FLEX_SERVER1.TabColArray,
363 coltypes OUT nocopy FND_FLEX_SERVER1.CharArray,
364 seg_formats OUT nocopy FND_FLEX_SERVER1.SegFormats)
365 RETURN BOOLEAN
366 IS
367 CURSOR kff_column_cursor(p_application_id IN NUMBER,
368 p_id_flex_code IN VARCHAR2,
369 p_id_flex_num IN NUMBER,
370 p_table_application_id IN NUMBER,
371 p_table_id IN NUMBER)
372 IS
373 SELECT /*+ LEADING (G) USE_NL (G C S) */
374 g.application_column_name application_column_name,
375 c.column_type application_column_type,
376 Nvl(s.format_type, 'C') value_set_format_type,
377 Nvl(s.maximum_size, c.width) value_set_maximum_size
378 FROM fnd_flex_value_sets s, fnd_columns c, fnd_id_flex_segments g
379 WHERE g.application_id = p_application_id
380 AND g.id_flex_code = p_id_flex_code
381 AND g.id_flex_num = p_id_flex_num
382 AND g.enabled_flag = 'Y'
383 AND s.flex_value_set_id(+) = g.flex_value_set_id
384 AND c.application_id = p_table_application_id
385 AND c.table_id = p_table_id
386 AND c.column_name = g.application_column_name
387 ORDER BY g.segment_num;
388
389 CURSOR dff_column_cursor(p_application_id IN NUMBER,
390 p_descriptive_flexfield_name IN VARCHAR2,
391 p_descriptive_flex_context_co IN VARCHAR2,
392 p_table_application_id IN NUMBER,
393 p_table_id IN NUMBER)
394 IS
395 SELECT
396 g.application_column_name application_column_name,
397 c.column_type application_column_type,
398 Nvl(s.format_type, 'C') value_set_format_type,
399 Nvl(s.maximum_size, c.width) value_set_maximum_size
400 FROM fnd_flex_value_sets s, fnd_columns c, fnd_descr_flex_column_usages g
401 WHERE g.application_id = p_application_id
402 AND g.descriptive_flexfield_name = p_descriptive_flexfield_name
403 AND g.descriptive_flex_context_code = p_descriptive_flex_context_co
404 AND g.enabled_flag = 'Y'
405 AND s.flex_value_set_id(+) = g.flex_value_set_id
406 AND c.application_id = p_table_application_id
407 AND c.table_id = p_table_id
408 AND c.column_name = g.application_column_name
409 ORDER BY g.column_seq_num;
410
411 i NUMBER;
412 BEGIN
413 IF (fstruct.isa_key_flexfield) THEN
414 g_cache_key := ('KFF.' ||
415 fstruct.application_id || '.' ||
416 fstruct.id_flex_code || '.' ||
417 fstruct.id_flex_num || '.' ||
418 table_apid || '.' ||
419 table_id);
420 fnd_plsql_cache.generic_1tom_get_values(coc_cache_controller,
421 coc_cache_storage,
422 g_cache_key,
423 g_cache_numof_values,
424 g_cache_values,
425 g_cache_return_code);
426
427 IF (g_cache_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
428 NULL;
429 ELSE
430 i := 0;
431 FOR col_rec IN kff_column_cursor(fstruct.application_id,
432 fstruct.id_flex_code,
433 fstruct.id_flex_num,
434 table_apid,
435 table_id) LOOP
436 i := i + 1;
437 fnd_plsql_cache.generic_cache_new_value
438 (x_value => g_cache_value,
439 p_varchar2_1 => col_rec.application_column_name,
440 p_varchar2_2 => col_rec.application_column_type,
441 p_varchar2_3 => col_rec.value_set_format_type,
442 p_varchar2_4 => col_rec.value_set_maximum_size);
443 g_cache_values(i) := g_cache_value;
444 END LOOP;
445 g_cache_numof_values := i;
446 fnd_plsql_cache.generic_1tom_put_values(coc_cache_controller,
447 coc_cache_storage,
448 g_cache_key,
449 g_cache_numof_values,
450 g_cache_values);
451 END IF;
452
453 IF (g_cache_numof_values < 1) THEN
454 FND_MESSAGE.set_name('FND', 'FLEX-CANT FIND SEGMENTS');
455 FND_MESSAGE.set_token('ROUTINE', 'Get Comb Table Column Names');
456 FND_MESSAGE.set_token('APPID', to_char(fstruct.application_id));
457 FND_MESSAGE.set_token('CODE', fstruct.id_flex_code);
458 FND_MESSAGE.set_token('NUM', to_char(fstruct.id_flex_num));
459 return(FALSE);
460 END IF;
461 ELSE
462 g_cache_key := ('DFF.' ||
463 fstruct.application_id || '.' ||
464 fstruct.desc_flex_name || '.' ||
465 fstruct.desc_flex_context || '.' ||
466 table_apid || '.' ||
467 table_id);
468 fnd_plsql_cache.generic_1tom_get_values(coc_cache_controller,
469 coc_cache_storage,
470 g_cache_key,
471 g_cache_numof_values,
472 g_cache_values,
473 g_cache_return_code);
474
475
476 IF (g_cache_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
477 NULL;
478 ELSE
479 i := 0;
480 FOR col_rec IN dff_column_cursor(fstruct.application_id,
481 fstruct.desc_flex_name,
482 fstruct.desc_flex_context,
483 table_apid,
484 table_id) LOOP
485 i := i + 1;
486 fnd_plsql_cache.generic_cache_new_value
487 (x_value => g_cache_value,
488 p_varchar2_1 => col_rec.application_column_name,
489 p_varchar2_2 => col_rec.application_column_type,
490 p_varchar2_3 => col_rec.value_set_format_type,
491 p_varchar2_4 => col_rec.value_set_maximum_size);
492 g_cache_values(i) := g_cache_value;
493 END LOOP;
494 g_cache_numof_values := i;
495 fnd_plsql_cache.generic_1tom_put_values(coc_cache_controller,
496 coc_cache_storage,
497 g_cache_key,
498 g_cache_numof_values,
499 g_cache_values);
500 END IF;
501 END IF;
502
503 n_columns := g_cache_numof_values;
504 seg_formats.nsegs := g_cache_numof_values;
505 FOR i IN 1..g_cache_numof_values LOOP
506 cols(i) := g_cache_values(i).varchar2_1;
507 coltypes(i) := g_cache_values(i).varchar2_2;
508 seg_formats.vs_format(i) := g_cache_values(i).varchar2_3;
509 seg_formats.vs_maxsize(i) := g_cache_values(i).varchar2_4;
510 END LOOP;
511
512 RETURN(TRUE);
513
514 EXCEPTION
515 WHEN OTHERS then
516 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
517 FND_MESSAGE.set_token('MSG', 'SV2.get_struct_cols() exception: '||SQLERRM);
518 return(FALSE);
519 END get_struct_cols;
520
521 /* ----------------------------------------------------------------------- */
522 /* Gets flexfield qualifier name, segment qualifier name, */
523 /* combination table column name and default value, for all segment */
524 /* qualifiers associated with a given flexfield. */
525 /* No qualifiers associated with descriptive flexfields. */
526 /* Returns TRUE on success or FALSE and sets error message if error. */
527 /* ----------------------------------------------------------------------- */
528 FUNCTION get_all_segquals(fstruct IN FND_FLEX_SERVER1.FlexStructId,
529 seg_quals OUT nocopy FND_FLEX_SERVER1.Qualifiers)
530 RETURN BOOLEAN IS
531
532 i NUMBER;
533
534 CURSOR all_qual_cursor(keystruct in FND_FLEX_SERVER1.FlexStructId) IS
535 SELECT segment_attribute_type fq_name, value_attribute_type sq_name,
536 application_column_name drv_colname, default_value dflt_val
537 FROM fnd_value_attribute_types
538 WHERE application_id = keystruct.application_id
539 AND id_flex_code = keystruct.id_flex_code;
540
541 BEGIN
542 seg_quals.nquals := 0;
543
544 if(fstruct.isa_key_flexfield) THEN
545 g_cache_key := fstruct.application_id || '.' || fstruct.id_flex_code;
546
547 fnd_plsql_cache.generic_1tom_get_values(gas_cache_controller,
548 gas_cache_storage,
549 g_cache_key,
550 g_cache_numof_values,
551 g_cache_values,
552 g_cache_return_code);
553
554 IF (g_cache_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
555 NULL;
556 ELSE
557 i := 0;
558 for squal in all_qual_cursor(fstruct) LOOP
559 i := i + 1;
560 fnd_plsql_cache.generic_cache_new_value
561 (x_value => g_cache_value,
562 p_varchar2_1 => squal.fq_name,
563 p_varchar2_2 => squal.sq_name,
564 p_varchar2_3 => squal.dflt_val,
565 p_varchar2_4 => squal.drv_colname);
566 g_cache_values(i) := g_cache_value;
567 END LOOP;
568 g_cache_numof_values := i;
569 fnd_plsql_cache.generic_1tom_put_values(gas_cache_controller,
570 gas_cache_storage,
571 g_cache_key,
572 g_cache_numof_values,
573 g_cache_values);
574 END IF;
575
576 FOR i IN 1..g_cache_numof_values LOOP
577 seg_quals.fq_names(i) := g_cache_values(i).varchar2_1;
578 seg_quals.sq_names(i) := g_cache_values(i).varchar2_2;
579 seg_quals.sq_values(i) := g_cache_values(i).varchar2_3;
580 seg_quals.derived_cols(i) := g_cache_values(i).varchar2_4;
581 END LOOP;
582 seg_quals.nquals := g_cache_numof_values;
583 end if;
584
585 return(TRUE);
586
587 EXCEPTION
588 WHEN OTHERS then
589 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
590 FND_MESSAGE.set_token('MSG', 'SV2.get_all_segquals() exception: ' ||SQLERRM);
591 return(FALSE);
592
593 END get_all_segquals;
594
595 /* ------------------------------------------------------------------------- */
596 /* Returns a table of flexfield qualifiers for each enabled segment */
597 /* for this flexfield, and a count of enabled segments and a map of */
598 /* which segments are required and displayed at the segment level. */
599 /* Does an outer join to get all segments in their display order and */
600 /* all flexfield qualifiers associated with each segment using a single */
601 /* select. For descriptive flexfields there are no qualifiers. In */
602 /* this case return just the segment info for the given context. */
603 /* For key flexfields it's an error if no enabled segments found. */
604 /* Returns FALSE and sets error message if error, or returns TRUE of OK */
605 /* ------------------------------------------------------------------------- */
606
607 FUNCTION get_qualsegs(fstruct IN FND_FLEX_SERVER1.FlexStructId,
608 nsegs OUT nocopy NUMBER,
609 segdisp OUT nocopy FND_FLEX_SERVER1.CharArray,
610 segrqd OUT nocopy FND_FLEX_SERVER1.CharArray,
611 fqtab OUT nocopy FND_FLEX_SERVER1.FlexQualTable)
612 RETURN BOOLEAN IS
613
614 n_segs NUMBER;
615 n_fqual NUMBER;
616 segnums FND_FLEX_SERVER1.NumberArray;
617
618 CURSOR KeyFQCursor(kff_struct IN FND_FLEX_SERVER1.FlexStructId) IS
619 SELECT s.segment_num segnum,
620 s.display_flag displayed,
621 s.required_flag required,
622 sav.segment_attribute_type fqname
623 FROM fnd_id_flex_segments s, fnd_segment_attribute_values sav
624 WHERE s.application_id = kff_struct.application_id
625 AND s.id_flex_code = kff_struct.id_flex_code
626 AND s.id_flex_num = kff_struct.id_flex_num
627 AND s.enabled_flag = 'Y'
628 AND sav.application_column_name(+) = s.application_column_name
629 AND sav.application_id(+) = kff_struct.application_id
630 AND sav.id_flex_code(+) = kff_struct.id_flex_code
631 AND sav.id_flex_num(+) = kff_struct.id_flex_num
632 AND sav.attribute_value(+) = 'Y'
633 ORDER BY s.segment_num;
634
635 CURSOR DescFQCursor(dff_struct IN FND_FLEX_SERVER1.FlexStructId) IS
636 SELECT column_seq_num segnum, display_flag displayed,
637 required_flag required
638 FROM fnd_descr_flex_column_usages
639 WHERE application_id = dff_struct.application_id
640 AND descriptive_flexfield_name = dff_struct.desc_flex_name
641 AND descriptive_flex_context_code = dff_struct.desc_flex_context
642 AND enabled_flag = 'Y'
643 ORDER BY column_seq_num;
644
645 kflexqual keyfqcursor%ROWTYPE;
646 dflexqual descfqcursor%ROWTYPE;
647
648 i NUMBER;
649 BEGIN
650 IF (fnd_flex_server1.g_debug_level > 0) THEN
651 fnd_flex_server1.add_debug('BEGIN SV2.get_qualsegs()');
652 END IF;
653
654 -- Outer join on segments and flexfield qualifiers tables.
655 -- Fill segdisp, segrqd with the values only for the distict segments
656 -- which are separated by their segment numbers. n_segs is the number
657 -- of distinct segments. seg_indexes is order of distinct segments.
658 -- FlexQualTable maps qualifiers to seg_indexes.
659
660 IF (fnd_flex_server1.g_debug_level > 0) THEN
661 FND_FLEX_SERVER1.add_debug('Segments+quals: ');
662 END IF;
663
664 n_segs := 0;
665 n_fqual := 0;
666 if(fstruct.isa_key_flexfield) THEN
667
668 g_cache_key:= ('KFF' || '.' ||
669 fstruct.application_id || '.' ||
670 fstruct.id_flex_code || '.' ||
671 fstruct.id_flex_num);
672
673 fnd_plsql_cache.generic_1tom_get_values(gqs_cache_controller,
674 gqs_cache_storage,
675 g_cache_key,
676 g_cache_numof_values,
677 g_cache_values,
678 g_cache_return_code);
679
680 IF (g_cache_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
681 NULL;
682 ELSE
683 i := 0;
684 FOR kflexqual IN keyfqcursor(fstruct) LOOP
685 i := i + 1;
686 fnd_plsql_cache.generic_cache_new_value
687 (x_value => g_cache_value,
688 p_number_1 => kflexqual.segnum,
689 p_varchar2_1 => kflexqual.displayed,
690 p_varchar2_2 => kflexqual.required,
691 p_varchar2_3 => kflexqual.fqname);
692 g_cache_values(i) := g_cache_value;
693 END LOOP;
694 g_cache_numof_values := i;
695 fnd_plsql_cache.generic_1tom_put_values(gqs_cache_controller,
696 gqs_cache_storage,
697 g_cache_key,
698 g_cache_numof_values,
699 g_cache_values);
700 END IF;
701
702 FOR i IN 1..g_cache_numof_values LOOP
703 kflexqual.segnum := g_cache_values(i).number_1;
704 kflexqual.displayed := g_cache_values(i).varchar2_1;
705 kflexqual.required := g_cache_values(i).varchar2_2;
706 kflexqual.fqname := g_cache_values(i).varchar2_3;
707
708 -- for flexqual in KeyFQCursor(fstruct) loop
709 n_fqual := n_fqual + 1;
710 segnums(n_fqual) := kflexqual.segnum;
711 fqtab.fq_names(n_fqual) := kflexqual.fqname;
712 if((n_fqual = 1) or (segnums(n_fqual) <> segnums(n_fqual - 1))) then
713 -- This is a new distinct segment.
714 n_segs := n_segs + 1;
715 segdisp(n_segs) := kflexqual.displayed;
716 segrqd(n_segs) := kflexqual.required;
717 end if;
718 fqtab.seg_indexes(n_fqual) := n_segs;
719 IF (fnd_flex_server1.g_debug_level > 0) THEN
720 FND_FLEX_SERVER1.add_debug('(' || to_char(kflexqual.segnum) || ', ' ||
721 kflexqual.fqname || ') ');
722 END IF;
723 end loop;
724 -- Key flexfield must have enabled segments
725 if(n_fqual <= 0) then
726 FND_MESSAGE.set_name('FND', 'FLEX-CANT FIND SEGMENTS');
727 FND_MESSAGE.set_token('ROUTINE', 'FND_FLEX_SERVER2.get_qualsegs()');
728 FND_MESSAGE.set_token('APPID', to_char(fstruct.application_id));
729 FND_MESSAGE.set_token('CODE', fstruct.id_flex_code);
730 FND_MESSAGE.set_token('NUM', to_char(fstruct.id_flex_num));
731 return(FALSE);
732 end if;
733 else
734 -- Descriptive flexfield segments
735
736 g_cache_key:= ('DFF' || '.' ||
737 fstruct.application_id || '.' ||
738 fstruct.desc_flex_name || '.' ||
739 fstruct.desc_flex_context);
740
741 fnd_plsql_cache.generic_1tom_get_values(gqs_cache_controller,
742 gqs_cache_storage,
743 g_cache_key,
744 g_cache_numof_values,
745 g_cache_values,
746 g_cache_return_code);
747
748 IF (g_cache_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
749 NULL;
750 ELSE
751 i := 0;
752 FOR dflexqual in descfqcursor(fstruct) LOOP
753 i := i + 1;
754 fnd_plsql_cache.generic_cache_new_value
755 (x_value => g_cache_value,
756 p_number_1 => dflexqual.segnum,
757 p_varchar2_1=> dflexqual.displayed,
758 p_varchar2_2=> dflexqual.required);
759 g_cache_values(i) := g_cache_value;
760 END LOOP;
761 g_cache_numof_values := i;
762 fnd_plsql_cache.generic_1tom_put_values(gqs_cache_controller,
763 gqs_cache_storage,
764 g_cache_key,
765 g_cache_numof_values,
766 g_cache_values);
767 END IF;
768
769 FOR i IN 1..g_cache_numof_values LOOP
770 dflexqual.segnum := g_cache_values(i).number_1;
771 dflexqual.displayed := g_cache_values(i).varchar2_1;
772 dflexqual.required := g_cache_values(i).varchar2_2;
773
774
775 -- for flexqual in DescFQCursor(fstruct) loop
776 n_segs := n_segs + 1;
777 segdisp(n_segs) := dflexqual.displayed;
778 segrqd(n_segs) := dflexqual.required;
779 fqtab.fq_names(n_segs) := NULL;
780 fqtab.seg_indexes(n_segs) := n_segs;
781 IF (fnd_flex_server1.g_debug_level > 0) THEN
782 FND_FLEX_SERVER1.add_debug('(' || to_char(dflexqual.segnum) || ') ');
783 END IF;
784 end loop;
785 n_fqual := n_segs;
786 end if;
787
788 fqtab.nentries := n_fqual;
789 nsegs := n_segs;
790 IF (fnd_flex_server1.g_debug_level > 0) THEN
791 fnd_flex_server1.add_debug('END SV2.get_qualsegs()');
792 END IF;
793
794 return(TRUE);
795
796 EXCEPTION
797 WHEN OTHERS THEN
798 IF (fnd_flex_server1.g_debug_level > 0) THEN
799 fnd_flex_server1.add_debug('EXCEPTION others SV2.get_qualsegs()');
800 END IF;
801 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
802 FND_MESSAGE.set_token('MSG','SV2.get_qualsegs() exception: ' || SQLERRM);
803 return(FALSE);
804
805 END get_qualsegs;
806
807 /* ----------------------------------------------------------------------- */
808 /* Performes cross-validation against validation rules. */
809 /* Returns VV_VALID if combination valid, VV_CROSSVAL if */
810 /* rule violated or VV_ERROR if error. */
811 /* Returns error_column_name and sets error message in */
812 /* FND_MESSAGE if not valid. */
813 /* Segment array is 1-based containing entries for 1 <= i <= nsegs */
814 /* ----------------------------------------------------------------------- */
815
816 FUNCTION cross_validate(nsegs IN NUMBER,
817 segs IN FND_FLEX_SERVER1.ValueArray,
818 segfmt IN FND_FLEX_SERVER1.SegFormats,
819 vdt IN DATE,
820 fstruct IN FND_FLEX_SERVER1.FlexStructId,
821 errcol OUT nocopy VARCHAR2)
822 RETURN NUMBER
823 IS
824 isviolated BOOLEAN;
825 incache BOOLEAN;
826 rulemsg VARCHAR2(240);
827 rulename VARCHAR2(15);
828 segments FND_FLEX_SERVER1.StringArray;
829 l_cat_segs VARCHAR2(32000);
830 BEGIN
831
832 -- Convert value array to string array for subsequent processing.
833 --
834 for i in 1..nsegs loop
835 segments(i) := segs(i);
836 end loop;
837
838 -- Concatenate segments
839 --
840 l_cat_segs := FND_FLEX_SERVER1.from_stringarray2(nsegs, segments,
841 CACHE_DELIMITER);
842
843 -- Next see if cross-validation result in cache. If not, check the
844 -- cross validation rules and cache the result. Implement our own
845 -- cost-based optimization to check the rules more efficiently
846 --
847 if(x_xvc_check_cache(fstruct, vdt,
848 l_cat_segs,
849 incache, isviolated, rulename) = FALSE) then
850 return(FND_FLEX_SERVER1.VV_ERROR);
851 end if;
852
853 IF (NOT incache) then
854
855 -- Check to see if rules violated.
856 --
857 isviolated := (cv_rule_violated(nsegs, segments, segfmt, fstruct, 'E',
858 vdt, rulename) OR
859 cv_rule_violated(nsegs, segments, segfmt, fstruct, 'I',
860 vdt, rulename));
861 -- Save the result in cache
862 --
863 if(x_xvc_update_cache(fstruct, vdt,
864 l_cat_segs,
865 isviolated, rulename) = FALSE) then
866 return(FND_FLEX_SERVER1.VV_ERROR);
867 end if;
868
869 end if;
870
871 IF (isviolated and fstruct.isa_key_flexfield) then
872 IF (rulename is not null) then
873 select error_message_text, error_segment_column_name
874 into rulemsg, errcol
875 from fnd_flex_vdation_rules_vl
876 where application_id = fstruct.application_id
877 and id_flex_code = fstruct.id_flex_code
878 and id_flex_num = fstruct.id_flex_num
879 and flex_validation_rule_name = rulename;
880 FND_MESSAGE.set_name('FND', 'FLEX-EXCLUDED BY XVAL RULE');
881 FND_MESSAGE.set_token('MESSAGE', rulemsg);
882 return(FND_FLEX_SERVER1.VV_CROSSVAL);
883 end if;
884 return(FND_FLEX_SERVER1.VV_ERROR);
885 end if;
886 return(FND_FLEX_SERVER1.VV_VALID);
887
888 EXCEPTION
889 WHEN NO_DATA_FOUND then
890 FND_MESSAGE.set_name('FND', 'FLEX-XVAL RULE MSG NOT FOUND');
891 FND_MESSAGE.set_token('RULENAME', rulename);
892 return(FND_FLEX_SERVER1.VV_ERROR);
893 WHEN TOO_MANY_ROWS then
894 FND_MESSAGE.set_name('FND', 'FLEX-XVAL RULE MSG NOT UNIQUE');
895 FND_MESSAGE.set_token('RULENAME', rulename);
896 return(FND_FLEX_SERVER1.VV_ERROR);
897 WHEN OTHERS then
898 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
899 FND_MESSAGE.set_token('MSG','SV2.cross_validate() exception: '||SQLERRM);
900 return(FND_FLEX_SERVER1.VV_ERROR);
901 END cross_validate;
902
903 /* ----------------------------------------------------------------------- */
904 /* Determines if any cross-validation include (inex = 'I') or */
905 /* exclude (inex = 'E') rule is violated. Returns name of first */
906 /* violated rule and TRUE if so, otherwise returns FALSE. */
907 /* ----------------------------------------------------------------------- */
908 FUNCTION cv_rule_violated(nsegs IN NUMBER,
909 segs IN FND_FLEX_SERVER1.StringArray,
910 segfmt IN FND_FLEX_SERVER1.SegFormats,
911 fstruct IN FND_FLEX_SERVER1.FlexStructId,
912 inex IN VARCHAR2,
913 v_date IN DATE,
914 rule_name OUT nocopy VARCHAR2) RETURN BOOLEAN
915 IS
916 sqls VARCHAR2(32000);
917 nfound NUMBER;
918 datatype VARCHAR2(1);
919 locol VARCHAR2(100);
920 hicol VARCHAR2(100);
921 violated_rule VARCHAR2(15);
922 isviolated BOOLEAN;
923
924 BEGIN
925
926 -- VERY IMPORTANT!!!
927 -- Must put the portions of SQL statement that select the specific flex
928 -- structure LAST in the SQL statement. This is so they will be evaluated
929 -- FIRST. If they are not evaluated first, then SQL may attempt to do
930 -- format conversions on the segments before checking what structure we
931 -- have and this will cause a data format type error.
932
933 -- First build the select statement appropriate for the include or
934 -- exclude rule search.
935 --
936
937 fnd_dsql.init;
938
939 sqls := cvrule_clause_begin;
940
941 IF (inex = 'E') THEN
942 sqls := sqls || cvrule_clause_exclude_begin;
943 end if;
944
945 sqls := sqls || cvrule_clause_where;
946
947 if(inex = 'I') then
948 sqls := sqls || cvrule_clause_include_mid;
949 else
950 sqls := sqls || cvrule_clause_exclude_mid;
951 end if;
952
953 fnd_dsql.add_text(sqls);
954
955 -- Build column select statements for each column.
956 --
957 for i in reverse 1..nsegs loop
958 datatype := segfmt.vs_format(i);
959 locol := 'L.SEGMENT' || to_char(i) || '_LOW';
960 hicol := 'L.SEGMENT' || to_char(i) || '_HIGH';
961 fnd_flex_server1.x_inrange_clause(segs(i), datatype,locol, hicol);
962 end loop;
963
964 fnd_dsql.add_text(cvrule_clause_end);
965
966 -- Stop at the first row if exclude rule violated
967 --
968 if(inex = 'E') then
969 fnd_dsql.add_text('and ROWNUM = 1 ');
970 end if;
971
972 -- Now do the select using dynamic sql
973 --
974 nfound := x_cv_rule_select(fstruct, v_date, violated_rule);
975
976 if(nfound = 0) then
977 isviolated := FALSE;
978 rule_name := NULL;
979 elsif(nfound > 0) then
980 isviolated := TRUE;
981 rule_name := violated_rule;
982 else
983 isviolated := TRUE;
984 rule_name := NULL;
985 end if;
986 return(isviolated);
987
988 EXCEPTION
989 WHEN OTHERS then
990 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
991 FND_MESSAGE.set_token('MSG','SV2.cv_rule_violated() exception: ' || SQLERRM);
992 rule_name := NULL;
993 return(TRUE);
994 END cv_rule_violated;
995
996 /* ----------------------------------------------------------------------- */
997 /* Uses dynamic SQL to select violated cross-validation rule. */
998 /* Returns 1 and first violated rule name if any rule violated. */
999 /* Returns 0 if no violated rules, or < 0 and sets message if error. */
1000 /* Returns 0 for descritive flexfields because they have no cv rules. */
1001 /* ----------------------------------------------------------------------- */
1002 FUNCTION x_cv_rule_select(fstruct IN FND_FLEX_SERVER1.FlexStructId,
1003 v_date IN DATE,
1004 bad_rule OUT nocopy VARCHAR2) RETURN NUMBER IS
1005
1006 num_returned NUMBER;
1007 selected_rule VARCHAR2(15);
1008 cursornum INTEGER;
1009 sql_str VARCHAR2(32000);
1010
1011 BEGIN
1012
1013 -- No cv rules for descriptive flexfields
1014 --
1015 if(not fstruct.isa_key_flexfield) then
1016 return(0);
1017 end if;
1018
1019 -- Save the SQL string in the debug string
1020 --
1021 FND_FLEX_SERVER1.add_sql_string(fnd_dsql.get_text(p_with_debug => TRUE));
1022
1023 cursornum := dbms_sql.open_cursor;
1024 fnd_dsql.set_cursor(cursornum);
1025
1026 sql_str := fnd_dsql.get_text(p_with_debug => FALSE);
1027 dbms_sql.parse(cursornum, sql_str, dbms_sql.v7);
1028
1029 fnd_dsql.do_binds;
1030 dbms_sql.bind_variable(cursornum, ':VDATE', v_date);
1031 dbms_sql.bind_variable(cursornum, ':APID', fstruct.application_id);
1032 dbms_sql.bind_variable(cursornum, ':CODE', fstruct.id_flex_code);
1033 dbms_sql.bind_variable(cursornum, ':NUM', fstruct.id_flex_num);
1034 dbms_sql.define_column(cursornum, 1, selected_rule, 15);
1035 num_returned := dbms_sql.execute_and_fetch(cursornum, FALSE);
1036 if(num_returned = 1) then
1037 dbms_sql.column_value(cursornum, 1, selected_rule);
1038 IF (fnd_flex_server1.g_debug_level > 0) THEN
1039 FND_FLEX_SERVER1.add_debug('(DSQL returned ' || selected_rule || ')');
1040 END IF;
1041 bad_rule := selected_rule;
1042 else
1043 num_returned := 0;
1044 IF (fnd_flex_server1.g_debug_level > 0) THEN
1045 FND_FLEX_SERVER1.add_debug('(DSQL returned: NULL)');
1046 END IF;
1047 end if;
1048 dbms_sql.close_cursor(cursornum);
1049 return(num_returned);
1050
1051 EXCEPTION
1052 WHEN OTHERS then
1053 if(dbms_sql.is_open(cursornum)) then
1054 dbms_sql.close_cursor(cursornum);
1055 end if;
1056 FND_MESSAGE.set_name('FND', 'FLEX-DSQL EXCEPTION');
1057 FND_MESSAGE.set_token('MSG', SQLERRM);
1058 FND_MESSAGE.set_token('SQLSTR', SUBSTRB(sql_str, 1, 1000));
1059 return(-1);
1060 END x_cv_rule_select;
1061
1062 /* ----------------------------------------------------------------------- */
1063 /* Checks to see if the segments already have been cross-validated. */
1064 /* If so, sets in_cache flag = TRUE and returns the isviolated flag */
1065 /* and the rule name of the violated rule if any. */
1066 /* Returns TRUE on success or FALSE and sets error message if error. */
1067 /* Combination must have been validated for the same vdate day. */
1068 /* Combination will be cleared after inserting in VALID(). */
1069 /* Cached is limited in size. */
1070 /* Returns in_cache = TRUE and is_violated = FALSE for descriptive */
1071 /* flexfields since there are no cv rules for descriptive flexfields. */
1072 /* ----------------------------------------------------------------------- */
1073 FUNCTION x_xvc_check_cache(fstruct IN FND_FLEX_SERVER1.FlexStructId,
1074 v_date IN DATE,
1075 p_cat_segs IN VARCHAR2,
1076 in_cache OUT nocopy BOOLEAN,
1077 is_violated OUT nocopy BOOLEAN,
1078 rule_name OUT nocopy VARCHAR2)
1079 RETURN BOOLEAN
1080 IS
1081 l_v_day DATE;
1082 BEGIN
1083 IF (fnd_flex_server1.g_debug_level > 0) THEN
1084 fnd_flex_server1.add_debug('BEGIN SV2.x_xvc_check_cache()');
1085 END IF;
1086
1087 in_cache := FALSE;
1088
1089 -- No cv rules for descriptive flexfields.
1090 --
1091 IF (not fstruct.isa_key_flexfield) then
1092 in_cache := TRUE;
1093 is_violated := FALSE;
1094 return(TRUE);
1095 end if;
1096
1097 g_cache_key := (fstruct.application_id ||
1098 fstruct.id_flex_code ||
1099 fstruct.id_flex_num || '.' ||
1100 p_cat_segs);
1101
1102 fnd_plsql_cache.generic_1to1_get_value(xvc_cache_controller,
1103 xvc_cache_storage,
1104 g_cache_key,
1105 g_cache_value,
1106 g_cache_return_code);
1107
1108 IF (g_cache_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
1109 -- Convert v_date to the day, so that if user calls validation
1110 -- with SYSDATE as the v_date, the value cached a few seconds earlier
1111 -- will still have the same v_date. Client code does not even consier
1112 -- seconds when checking v_date so this should cause no problems.
1113 -- note v_date may be null.
1114 --
1115 l_v_day := Trunc(v_date);
1116
1117 IF ((l_v_day IS NULL AND g_cache_value.date_1 IS NULL) OR
1118 (l_v_day IS NOT NULL AND l_v_day = g_cache_value.date_1)) THEN
1119 is_violated := g_cache_value.boolean_1;
1120 rule_name := g_cache_value.varchar2_1;
1121 in_cache := TRUE;
1122 END IF;
1123
1124 END IF;
1125
1126 return(TRUE);
1127 EXCEPTION
1128 WHEN OTHERS then
1129 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
1130 FND_MESSAGE.set_token('MSG','SV2.x_xvc_check_cache() exception: ' || SQLERRM);
1131 return(FALSE);
1132 END x_xvc_check_cache;
1133
1134 /* ----------------------------------------------------------------------- */
1135 /* Saves the results of validating a combination to cache. */
1136 /* Returns TRUE on success or FALSE and sets error message if error. */
1137 /* ----------------------------------------------------------------------- */
1138 FUNCTION x_xvc_update_cache(fstruct IN FND_FLEX_SERVER1.FlexStructId,
1139 v_date IN DATE,
1140 p_cat_segs IN VARCHAR2,
1141 is_violated IN BOOLEAN,
1142 rule_name IN VARCHAR2)
1143 RETURN BOOLEAN
1144 IS
1145 l_v_day DATE;
1146 l_in_cache BOOLEAN;
1147 BEGIN
1148 IF (fnd_flex_server1.g_debug_level > 0) THEN
1149 fnd_flex_server1.add_debug('BEGIN SV2.x_xvc_update_cache()');
1150 END IF;
1151
1152 -- No cv rules for descriptive flexfields.
1153 --
1154 if(not fstruct.isa_key_flexfield) then
1155 return(TRUE);
1156 end if;
1157
1158 g_cache_key := (fstruct.application_id ||
1159 fstruct.id_flex_code ||
1160 fstruct.id_flex_num || '.' ||
1161 p_cat_segs);
1162
1163 fnd_plsql_cache.generic_1to1_get_value(xvc_cache_controller,
1164 xvc_cache_storage,
1165 g_cache_key,
1166 g_cache_value,
1167 g_cache_return_code);
1168
1169
1170
1171 IF (g_cache_return_code = fnd_plsql_cache.CACHE_NOTFOUND) THEN
1172 l_in_cache := FALSE;
1173 ELSE
1174 l_v_day := Trunc(v_date);
1175
1176 IF ((l_v_day IS NULL AND g_cache_value.date_1 IS NULL) OR
1177 (l_v_day IS NOT NULL AND l_v_day = g_cache_value.date_1)) THEN
1178
1179 l_in_cache := TRUE;
1180
1181 fnd_plsql_cache.generic_cache_new_value(x_value => g_cache_value,
1182 p_boolean_1 => is_violated,
1183 p_varchar2_1 => rule_name,
1184 p_date_1 => l_v_day);
1185 END IF;
1186 END IF;
1187
1188
1189 IF (NOT l_in_cache) THEN
1190 fnd_plsql_cache.generic_cache_new_value(x_value => g_cache_value,
1191 p_boolean_1 => is_violated,
1192 p_varchar2_1 => rule_name,
1193 p_date_1 => l_v_day);
1194 END IF;
1195
1196 fnd_plsql_cache.generic_1to1_put_value(xvc_cache_controller,
1197 xvc_cache_storage,
1198 g_cache_key,
1199 g_cache_value);
1200
1201 IF (fnd_flex_server1.g_debug_level > 0) THEN
1202 FND_FLEX_SERVER1.add_debug('Added to xvc_cache key : ' ||
1203 REPLACE(g_cache_key, CACHE_DELIMITER, '.'));
1204
1205 fnd_flex_server1.add_debug('is_violated :');
1206 if(is_violated) then
1207 FND_FLEX_SERVER1.add_debug('Y ');
1208 else
1209 FND_FLEX_SERVER1.add_debug('N ');
1210 end if;
1211 FND_FLEX_SERVER1.add_debug('rule_name :' || rule_name);
1212 END IF;
1213 return(TRUE);
1214
1215 EXCEPTION
1216 WHEN OTHERS then
1217 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
1218 FND_MESSAGE.set_token('MSG','SV2.x_xvc_cache_result() exception: ' || SQLERRM);
1219 return(FALSE);
1220
1221 END x_xvc_update_cache;
1222
1223 /* ----------------------------------------------------------------------- */
1224 /* Deletes the specified combination from the cross-validation cache. */
1225 /* ----------------------------------------------------------------------- */
1226 FUNCTION x_drop_cached_cv_result(fstruct IN FND_FLEX_SERVER1.FlexStructId,
1227 n_segs IN NUMBER,
1228 segs IN FND_FLEX_SERVER1.ValueArray)
1229 RETURN BOOLEAN IS
1230 l_count NUMBER;
1231 cat_segs VARCHAR2(2000);
1232 segments FND_FLEX_SERVER1.StringArray;
1233
1234 BEGIN
1235 IF (fnd_flex_server1.g_debug_level > 0) THEN
1236 fnd_flex_server1.add_debug('BEGIN SV2.x_drop_cached_cv_result()');
1237 END IF;
1238
1239 -- No cv rules for descriptive flexfields.
1240 --
1241 if(not fstruct.isa_key_flexfield) then
1242 return(TRUE);
1243 end if;
1244
1245 -- Concatenate segments
1246 --
1247 for i in 1..n_segs loop
1248 segments(i) := segs(i);
1249 end loop;
1250 cat_segs := FND_FLEX_SERVER1.from_stringarray2(n_segs, segments,
1251 CACHE_DELIMITER);
1252
1253 g_cache_key := (fstruct.application_id ||
1254 fstruct.id_flex_code ||
1255 fstruct.id_flex_num || '.' ||
1256 cat_segs);
1257
1258 fnd_plsql_cache.generic_1to1_get_value(xvc_cache_controller,
1259 xvc_cache_storage,
1260 g_cache_key,
1261 g_cache_value,
1262 g_cache_return_code);
1263
1264 IF (g_cache_return_code = fnd_plsql_cache.CACHE_NOTFOUND) THEN
1265 l_count := 0;
1266 ELSE
1267 l_count := 1;
1268
1269 fnd_plsql_cache.generic_1to1_remove_key(xvc_cache_controller,
1270 g_cache_key);
1271 END IF;
1272
1273 IF (fnd_flex_server1.g_debug_level > 0) THEN
1274 FND_FLEX_SERVER1.add_debug('Cleared ' || to_char(l_count) ||
1275 ' cached cv results. ');
1276 END IF;
1277 return(TRUE);
1278
1279 EXCEPTION
1280 WHEN OTHERS then
1281 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
1282 FND_MESSAGE.set_token('MSG','SV2.x_drop_cached_cv_result() exception: '
1283 || SQLERRM);
1284 return(FALSE);
1285
1286 END x_drop_cached_cv_result;
1287
1288 /* ----------------------------------------------------------------------- */
1289 /* Clears the cross-validation rule cache altogether. */
1290 /* ----------------------------------------------------------------------- */
1291 PROCEDURE x_clear_cv_cache IS
1292 BEGIN
1293 IF (fnd_flex_server1.g_debug_level > 0) THEN
1294 FND_FLEX_SERVER1.add_debug('Zeroing xvr_comb cache');
1295 END IF;
1296 fnd_plsql_cache.generic_1to1_clear(xvc_cache_controller,
1297 xvc_cache_storage);
1298 END x_clear_cv_cache;
1299
1300 /* ----------------------------------------------------------------------- */
1301 /* Breaks up concatenated values or ids into a StringArray. */
1302 /* Also checks that there are not too many segments. */
1303 /* If segment delimiter in a segment it is assumed to have been */
1304 /* replaced by a carriage return UNLESS there is only one segment */
1305 /* in which case the delimiter is not substituted. */
1306 /* The parsed displayed segments token is input to count the number */
1307 /* of segments expected. */
1308 /* Returns TRUE and nsegs_out > 0 if all ok, or FALSE, nsegs_out = 0, */
1309 /* and sets error using FND_MESSAGE on error. */
1310 /* ----------------------------------------------------------------------- */
1311
1312 FUNCTION breakup_catsegs(catsegs IN VARCHAR2,
1313 delim IN VARCHAR2,
1314 vals_not_ids IN BOOLEAN,
1315 displayed_segs IN FND_FLEX_SERVER1.DisplayedSegs,
1316 nsegs_out OUT nocopy NUMBER,
1317 segs_out IN OUT nocopy FND_FLEX_SERVER1.StringArray)
1318 RETURN BOOLEAN IS
1319 cat_nsegs NUMBER;
1320 disp_nsegs NUMBER;
1321 j NUMBER;
1322 l_min NUMBER;
1323
1324 BEGIN
1325
1326 disp_nsegs := 0;
1327 cat_nsegs := 0;
1328 nsegs_out := 0;
1329
1330 -- Count the number of segments to expect. Only displayed ones if values.
1331 --
1332 if(vals_not_ids) then
1333 for i in 1..displayed_segs.n_segflags loop
1334 if(displayed_segs.segflags(i)) then
1335 disp_nsegs := disp_nsegs + 1;
1336 end if;
1337 end loop;
1338 else
1339 disp_nsegs := displayed_segs.n_segflags;
1340 end if;
1341
1342 -- If only expecting one segment return it immediately without
1343 -- calling to_stringarray() which might replace the delimiter.
1344 -- Otherwise call to_stringarray to break up the segments.
1345 --
1346 if(disp_nsegs = 1) then
1347 segs_out(1) := catsegs;
1348 cat_nsegs := 1;
1349 else
1350 cat_nsegs := FND_FLEX_SERVER1.to_stringarray(catsegs, delim, segs_out);
1351 end if;
1352
1353
1354 /*************************************************************************
1355 Bug 2050531 The correct format (In V mode) is to pass only displayed
1356 segments to fnd_flex_keyval.validate_segs(concat_segs). If for some
1357 reason the calling program passes in the concatenated displayed and
1358 non-displayed segments, then we need to parse out the concatednated
1359 segments removing the non-displayed segments. The validation code
1360 does not expect to receive non-diplayed segments and will error out
1361 if it does. Non-displayed segments are handeled differently because
1362 the are automatically defaulted and validated at that time. How will
1363 the we know if the concat segs passed in include non displayed
1364 segments ? We will assume that if the concat segment count is
1365 greater than the number of displayed segment count, then we have
1366 non-disp segments and they need to be parsed and removed out of
1367 the concatenation.
1368 *************************************************************************/
1369
1370 if((cat_nsegs > disp_nsegs) AND vals_not_ids) then
1371
1372 IF (cat_nsegs < displayed_segs.n_segflags) THEN
1373 l_min := cat_nsegs;
1374 ELSE
1375 -- Concat string has more values than the number of segments.
1376 l_min := displayed_segs.n_segflags;
1377 END IF;
1378
1379 -- Shift displayed values in the array.
1380 j := 1;
1381 for i in 1..l_min loop
1382 if(displayed_segs.segflags(i)) then
1383 segs_out(j) := segs_out(i);
1384 j := j + 1;
1385 end if;
1386 end loop;
1387
1388 -- Re-set array size.
1389 disp_nsegs := j - 1;
1390
1391 -- Nullify the rest of the array.
1392 FOR i IN j..cat_nsegs LOOP
1393 segs_out(i) := NULL;
1394 END LOOP;
1395
1396 end if;
1397
1398
1399 -- Check to make sure there are not too many segments.
1400 --
1401 if(disp_nsegs > MAX_NSEGS) then
1402 FND_MESSAGE.set_name('FND', 'FLEX-TOO MANY SEGS');
1403 FND_MESSAGE.set_token('NSEGS', MAX_NSEGS);
1404 return(FALSE);
1405 end if;
1406
1407 -- Return the segment count
1408 nsegs_out := disp_nsegs;
1409 return(TRUE);
1410
1411 EXCEPTION
1412 WHEN OTHERS then
1413 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
1414 FND_MESSAGE.set_token('MSG','SV2.breakup_catsegs() exception: '||SQLERRM);
1415 return(FALSE);
1416
1417 END breakup_catsegs;
1418
1419
1420 /* ----------------------------------------------------------------------- */
1421
1422 -- ======================================================================
1423 PROCEDURE cp_debug(p_debug IN VARCHAR2)
1424 IS
1425 l_debug VARCHAR2(32000) := p_debug;
1426 l_len NUMBER := Nvl(Length(l_debug),0);
1427 l_pos NUMBER;
1428 BEGIN
1429 IF (p_debug LIKE 'ERROR%') THEN
1430 g_numof_errors := g_numof_errors + 1;
1431 END IF;
1432
1433 WHILE l_len > 0 LOOP
1434 l_pos := Instr(l_debug, chr_newline, 1, 1);
1435 IF ((l_pos + g_indent > g_line_size) OR (l_pos = 0)) THEN
1436 l_pos := g_line_size - g_indent;
1437 fnd_file.put_line(FND_FILE.LOG,
1438 Lpad(' ',g_indent-1,' ') ||
1439 Substr(l_debug, 1, l_pos));
1440 ELSE
1441 fnd_file.put(FND_FILE.LOG,
1442 Lpad(' ',g_indent-1,' ') ||
1443 Substr(l_debug, 1, l_pos));
1444 END IF;
1445
1446 l_debug := Substr(l_debug, l_pos + 1);
1447 l_len := Nvl(Length(l_debug),0);
1448 END LOOP;
1449 EXCEPTION
1450 WHEN OTHERS THEN
1451 NULL;
1452 END cp_debug;
1453
1454 -- --------------------------------------------------
1455
1456 -- ======================================================================
1457 -- This procedure submits parallel processes for FNDFFRXR which
1458 -- generates the Cross Validatoin rule violation report.
1459 -- ======================================================================
1460 PROCEDURE submit_rxr_report(errbuf OUT nocopy VARCHAR2,
1461 retcode OUT nocopy VARCHAR2,
1462 p_application_id IN VARCHAR2,
1463 p_id_flex_code IN VARCHAR2,
1464 p_id_flex_num IN VARCHAR2,
1465 p_update_flag IN VARCHAR2,
1466 p_num_workers IN NUMBER,
1467 p_debug_flag IN VARCHAR2,
1468 p_enddate_flag IN VARCHAR2 DEFAULT NULL,
1469 p_cvr_name_low IN VARCHAR2 DEFAULT NULL,
1470 p_cvr_name_high IN VARCHAR2 DEFAULT NULL)
1471 IS
1472 ----------------------
1473 -- Local definitions -
1474 ----------------------
1475 l_request_id NUMBER;
1476 l_sub_request_id NUMBER;
1477 l_request_count NUMBER;
1478 i NUMBER;
1479 l_min_ccid NUMBER;
1480 l_max_ccid NUMBER;
1481 l_total_ccid NUMBER;
1482 l_min_ccid_range NUMBER;
1483 l_max_ccid_range NUMBER;
1484 l_batch_size NUMBER;
1485 l_num_workers NUMBER;
1486 l_normal_count NUMBER := 0;
1487 l_warning_count NUMBER := 0;
1488 l_error_count NUMBER := 0;
1489 l_request_data VARCHAR2(100);
1490 l_action_message VARCHAR2(200);
1491 l_sub_program VARCHAR2(8);
1492 l_sub_description VARCHAR2(100);
1493 l_ccid_partition_sql VARCHAR2(1000);
1494 l_max_ccid_range_sql VARCHAR2(1000);
1495 l_set_def_col_name VARCHAR2(30);
1496 l_unique_id_col_name VARCHAR2(30);
1497 l_app_table_name VARCHAR2(30);
1498 l_cp_appl_name VARCHAR2(30);
1499 l_sub_requests fnd_concurrent.requests_tab_type;
1500 TYPE cursor_type IS REF CURSOR;
1501 l_ccid_partition_cur cursor_type;
1502 l_max_ccid_range_cur cursor_type;
1503
1504
1505 BEGIN
1506
1507 ------------------------------------------------
1508 -- Defining values for the sumbit request call -
1509 ------------------------------------------------
1510 l_cp_appl_name := 'FND';
1511 l_sub_program := 'FNDRXR';
1512 l_sub_description := 'Cross-Validation Rule Violation Report';
1513
1514 ------------------------------------------------------------
1515 -- Select the information needed to partition the CC table -
1516 ------------------------------------------------------------
1517 SELECT
1518 application_table_name,
1519 set_defining_column_name,
1520 unique_id_column_name
1521 INTO
1522 l_app_table_name,
1523 l_set_def_col_name,
1524 l_unique_id_col_name
1525 FROM
1526 fnd_id_flexs
1527 WHERE
1528 application_id = p_application_id and
1529 id_flex_code = p_id_flex_code;
1530
1531
1532 /************************************************************
1533 select count(*) total_ccid,
1534 min(code_combination_id) min_ccid,
1535 max(code_combination_id) max_ccid
1536 from gl_code_combinations
1537 where chart_of_accounts_id = 101;
1538 ************************************************************/
1539 l_ccid_partition_sql :=
1540 ('SELECT /* $Header: AFFFSV2B.pls 120.2.12010000.2 2010/02/25 09:46:36 nareshku ship $ */ ' ||
1541 ' COUNT(*), ' ||
1542 ' MIN(' || l_unique_id_col_name || '), ' ||
1543 ' MAX(' || l_unique_id_col_name || ')' ||
1544 ' FROM ' || l_app_table_name ||
1545 ' WHERE ' || l_set_def_col_name || '= :b_id_flex_num' ||
1546 ' AND enabled_flag = ''Y'' ');
1547
1548 l_request_id := fnd_global.conc_request_id;
1549 l_request_data := fnd_conc_global.request_data;
1550
1551 cp_debug('DEBUG: Request Id : ' || l_request_id);
1552 cp_debug('DEBUG: Request Data : ' || l_request_data);
1553 cp_debug(' ');
1554
1555 IF (l_request_data IS NULL) THEN
1556 --
1557 -- Print the header.
1558 --
1559 cp_debug(Lpad('Request ID', 10) || ' ' ||
1560 Rpad('Cross-Validation Rule Violation Report', 60));
1561 cp_debug(Lpad('-',10, '-') || ' ' ||
1562 Rpad('-',60, '-'));
1563
1564 BEGIN
1565
1566 OPEN l_ccid_partition_cur FOR l_ccid_partition_sql USING p_id_flex_num;
1567 l_request_count := 0;
1568
1569 FETCH l_ccid_partition_cur INTO l_total_ccid, l_min_ccid, l_max_ccid;
1570
1571 CLOSE l_ccid_partition_cur;
1572
1573
1574 --------------------------------------
1575 -- Initialize partitioning variables -
1576 --------------------------------------
1577 i := 0;
1578 l_num_workers := p_num_workers;
1579 -- If more workers than ccid's, then set
1580 -- number of workers to number of ccid's
1581 if(l_total_ccid < l_num_workers) then
1582 l_num_workers := l_total_ccid;
1583 end if;
1584
1585 -- Set l_num_workers to 1 if user enters 0 or less
1586 IF (l_num_workers <= 0) THEN
1587 l_num_workers :=1;
1588
1589 END IF;
1590
1591 l_batch_size := (trunc((l_total_ccid)/l_num_workers ));
1592 l_min_ccid_range := l_min_ccid;
1593
1594 /************************************************************
1595 SELECT
1596 MAX(code_combination_id)
1597 FROM
1598 (SELECT code_combination_id
1599 FROM gl_code_combinations
1600 WHERE chart_of_accounts_id = 101
1601 AND enabled_flag = 'Y'
1602 AND code_combination_id >= l_min_ccid_range
1603 ORDER BY code_combination_id)
1604 where rownum <= BATCH_SIZE
1605 ************************************************************/
1606 l_max_ccid_range_sql :=
1607 ('SELECT /* $Header: AFFFSV2B.pls 120.2.12010000.2 2010/02/25 09:46:36 nareshku ship $ */ ' ||
1608 ' MAX(' || l_unique_id_col_name || ')' ||
1609 ' FROM' ||
1610 ' (SELECT ' || l_unique_id_col_name ||
1611 ' FROM ' || l_app_table_name ||
1612 ' WHERE ' || l_set_def_col_name || '= :b_id_flex_num' ||
1613 ' AND enabled_flag = ''Y'' ' ||
1614 ' AND ' || l_unique_id_col_name || '>= :b_l_min_ccid_range' ||
1615 ' ORDER BY ' || l_unique_id_col_name || ')' ||
1616 ' WHERE rownum <= :b_batch_size');
1617
1618 l_request_count := 0;
1619
1620 FOR i in 1..l_num_workers LOOP
1621
1622 -- Last worker should get the max ccid
1623 IF (i = l_num_workers) THEN
1624 l_max_ccid_range := l_max_ccid;
1625 ELSE
1626 OPEN l_max_ccid_range_cur FOR l_max_ccid_range_sql
1627 USING p_id_flex_num, l_min_ccid_range, l_batch_size;
1628 FETCH l_max_ccid_range_cur INTO l_max_ccid_range;
1629 CLOSE l_max_ccid_range_cur;
1630 END IF;
1631
1632 l_request_count := l_request_count + 1;
1633
1634 l_sub_request_id := fnd_request.submit_request
1635 (application => l_cp_appl_name,
1636 program => l_sub_program,
1637 description => l_sub_description,
1638 start_time => NULL,
1639 sub_request => TRUE,
1640 argument1 => p_application_id,
1641 argument2 => p_id_flex_code,
1642 argument3 => p_id_flex_num,
1643 argument4 => p_update_flag,
1644 argument5 => p_enddate_flag,
1645 argument6 => p_cvr_name_low,
1646 argument7 => p_cvr_name_high,
1647 argument8 => l_min_ccid_range,
1648 argument9 => l_max_ccid_range,
1649 argument10 => p_debug_flag);
1650
1651 l_min_ccid_range := l_max_ccid_range + 1;
1652
1653 cp_debug(Lpad(l_sub_request_id, 10) || ' ' ||
1654 Rpad(l_sub_program, 60));
1655
1656 IF (l_sub_request_id = 0) THEN
1657 null;
1658 cp_debug('ERROR : Unable to submit sub request.');
1659 cp_debug('MESSAGE : ' || fnd_message.get);
1660 END IF;
1661 END LOOP;
1662
1663 END;
1664
1665 l_request_count := Nvl(l_request_count, 0);
1666
1667 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
1668 request_data => To_char(l_request_count));
1669
1670 errbuf := l_request_count || ' sub request(s) submitted.';
1671 cp_debug(' ');
1672 cp_debug(errbuf);
1673 cp_debug(' ');
1674 retcode := 0;
1675 RETURN;
1676 ELSE
1677 l_request_count := To_number(l_request_data);
1678
1679 cp_debug(l_request_count || ' sub request(s) completed.');
1680 --
1681 -- Print the header.
1682 --
1683 cp_debug(' ');
1684 cp_debug('Status Report');
1685 cp_debug(Rpad('-',72,'-'));
1686 cp_debug(Lpad('Request ID', 10) || ' ' ||
1687 Rpad('Status', 10) || ' ' ||
1688 Rpad('Action', 50));
1689 cp_debug(Lpad('-',10, '-') || ' ' ||
1690 Lpad('-',10, '-') || ' ' ||
1691 Lpad('-',50, '-'));
1692
1693 l_sub_requests := fnd_concurrent.get_sub_requests(l_request_id);
1694 i := l_sub_requests.first;
1695 WHILE i IS NOT NULL LOOP
1696 IF (l_sub_requests(i).dev_status = 'NORMAL') THEN
1697 l_normal_count := l_normal_count + 1;
1698 l_action_message := 'Completed successfully.';
1699 ELSIF (l_sub_requests(i).dev_status = 'WARNING') THEN
1700 l_warning_count := l_warning_count + 1;
1701 l_action_message := 'Warnings reported, please see the sub-request log file.';
1702 ELSIF (l_sub_requests(i).dev_status = 'ERROR') THEN
1703 l_error_count := l_error_count + 1;
1704 l_action_message := 'Errors reported, please see the sub-request log file.';
1705 ELSE
1706 l_error_count := l_error_count + 1;
1707 l_action_message := 'Unknown status reported, please see the sub-request log file.';
1708 END IF;
1709 cp_debug(Lpad(l_sub_requests(i).request_id, 10) || ' ' ||
1710 Rpad(l_sub_requests(i).dev_status, 10) || ' ' ||
1711 l_action_message);
1712 i := l_sub_requests.next(i);
1713 END LOOP;
1714
1715 cp_debug(' ');
1716 cp_debug('Summary Report');
1717 cp_debug(Rpad('-',72,'-'));
1718 cp_debug(Rpad('Status', 20) || ' ' ||
1719 Rpad('Count', 10));
1720 cp_debug(Rpad('-', 20, '-') || ' ' ||
1721 Rpad('-', 10, '-'));
1722 cp_debug(Rpad('Normal', 20) || ' ' ||
1723 Rpad(l_normal_count, 10));
1724 cp_debug(Rpad('Warning', 20) || ' ' ||
1725 Rpad(l_warning_count, 10));
1726 cp_debug(Rpad('Error', 20) || ' ' ||
1727 Rpad(l_error_count, 10));
1728 cp_debug(Rpad('-', 20, '-') || ' ' ||
1729 Rpad('-', 10, '-'));
1730 cp_debug(Rpad('Total', 20) || ' ' ||
1731 Rpad(l_sub_requests.COUNT, 10));
1732 cp_debug(' ');
1733 errbuf := l_sub_requests.COUNT || ' sub request(s) completed.';
1734 IF (l_error_count > 0) THEN
1735 retcode := 2;
1736 ELSIF (l_warning_count > 0) THEN
1737 retcode := 1;
1738 ELSE
1739 retcode := 0;
1740 END IF;
1741 RETURN;
1742 END IF;
1743 EXCEPTION
1744 WHEN OTHERS THEN
1745 retcode := 2;
1746 errbuf := Substr('submit_rxr_report:SQLERRM: ' || Sqlerrm, 1, 240);
1747 END submit_rxr_report;
1748
1749 /* ----------------------------------------------------------------------- */
1750
1751 BEGIN
1752 CACHE_DELIMITER := fnd_global.local_chr(0);
1753
1754 fnd_plsql_cache.generic_1to1_init('SV2.GKS',
1755 gks_cache_controller,
1756 gks_cache_storage);
1757
1758 fnd_plsql_cache.generic_1to1_init('SV2.GDS',
1759 gds_cache_controller,
1760 gds_cache_storage);
1761
1762 fnd_plsql_cache.generic_1to1_init('SV2.XVC',
1763 xvc_cache_controller,
1764 xvc_cache_storage);
1765
1766 fnd_plsql_cache.generic_1tom_init('SV2.COC',
1767 coc_cache_controller,
1768 coc_cache_storage);
1769
1770 fnd_plsql_cache.generic_1tom_init('SV2.GAS',
1771 gas_cache_controller,
1772 gas_cache_storage);
1773
1774 fnd_plsql_cache.generic_1tom_init('SV2.GQS',
1775 gqs_cache_controller,
1776 gqs_cache_storage);
1777
1778 END fnd_flex_server2;