1 PACKAGE BODY GL_CHART_OF_ACCOUNTS_API_PKG AS
2 /* $Header: gluvcoab.pls 120.2 2005/03/31 13:39:29 knag ship $ */
3 --
4 -- Package
5 -- GL_CHART_OF_ACCOUNTS_API_PKG
6 -- Purpose
7 -- This package is used to validate the chart of accounts information
8 -- imported with iSpeed.
9 -- History
10 -- 10.09.2000 O Monnier Created.
11
12 --
13 -- PRIVATE FUNCTIONS
14 --
15
16 --
17 -- Function
18 -- has_loop
19 -- Purpose
20 -- "Borrowed" this from the JAHE project...test to see
21 -- if any of test_value's ancestors are parent_value
22 --
23 -- History
24 -- 02.03.2001 M Marra Created.
25 --
26 FUNCTION has_loop ( source IN VARCHAR2,
27 target IN VARCHAR2,
28 value_set_id IN NUMBER
29 ) RETURN BOOLEAN IS
30 CURSOR find_parent_cursor IS
31 SELECT parent_flex_value, range_attribute
32 FROM fnd_flex_value_norm_hierarchy
33 WHERE flex_value_set_id = value_set_id
34 AND target BETWEEN child_flex_value_low
35 AND child_flex_value_high;
36 parent find_parent_cursor%ROWTYPE;
37 BEGIN
38 OPEN find_parent_cursor;
39 LOOP
40 FETCH find_parent_cursor INTO parent;
41 IF ( find_parent_cursor%NOTFOUND ) THEN
42 CLOSE find_parent_cursor;
43 RETURN(FALSE);
44 ELSIF ( parent.parent_flex_value = source ) THEN
45 CLOSE find_parent_cursor;
46 RETURN(TRUE);
47 ELSIF ( has_loop(source, parent.parent_flex_value, value_set_id) ) THEN
48 CLOSE find_parent_cursor;
49 RETURN(TRUE);
50 END IF;
51 END LOOP;
52 CLOSE find_parent_cursor;
53 RETURN(FALSE);
54 END has_loop;
55
56
57 --
58 -- Function
59 -- is_flexfield_supported
60 -- Purpose
61 -- Check if the flexfield is supported by the API
62 -- Currently, only the GL Accounting flexfield is supported, but we may
63 -- add some supports for other flexfields later.
64 -- History
65 -- 10.09.2000 O Monnier Created.
66 --
67 FUNCTION is_flexfield_supported(v_application_id IN NUMBER,
68 v_id_flex_code IN VARCHAR2) RETURN BOOLEAN
69 IS
70 BEGIN
71 --
72 -- Only the GL Accounting is supported by the API now.
73 --
74 IF (v_application_id = 101 AND v_id_flex_code = 'GL#') THEN
75 RETURN TRUE;
76 ELSE
77 RETURN FALSE;
78 END IF;
79 END;
80
81 --
82 -- PUBLIC FUNCTIONS
83 --
84
85 --
86 -- Procedure
87 -- validate_structure
88 -- Purpose
89 -- Do the validation for the structure
90 -- (FND_ID_FLEX_STRUCTURES table)
91 -- History
92 -- 10.09.2000 O Monnier Created.
93 --
94 PROCEDURE validate_structure(v_application_id IN NUMBER,
95 v_id_flex_code IN VARCHAR2,
96 v_id_flex_num IN NUMBER,
97 v_dynamic_inserts_allowed_f IN VARCHAR2,
98 v_operation IN VARCHAR2 DEFAULT 'DML_INSERT')
99 IS
100 /* NOT NEEDED FOR THE CHART OF ACCOUNTS FLEXFIELD
101 ONLY NEEDED FOR OTHER FLEXFIELDS
102 v_dynamic_inserts_feasible_f VARCHAR2(1);
103 v_set_defining_column_name VARCHAR2(30);
104
105 -- Retrieve the flexfield information required for the validation from the structure.
106 CURSOR c_flex IS
107 SELECT dynamic_inserts_feasible_flag,
108 set_defining_column_name
109 FROM FND_ID_FLEXS
110 WHERE application_id = v_application_id
111 AND id_flex_code = v_id_flex_code;*/
112
113 BEGIN
114 --
115 -- Check the mode.
116 --
117 IF (v_operation<> 'DML_INSERT' AND v_operation<> 'DML_UPDATE') THEN
118 RAISE invalid_dml_mode;
119 END IF;
120
121 --
122 -- Check if the flexfield is supported by the API
123 --
124 IF (NOT is_flexfield_supported(v_application_id => v_application_id,
125 v_id_flex_code => v_id_flex_code))
126 THEN
127 RAISE flexfield_not_supported;
128 END IF;
129
130 /* NOT NEEDED FOR THE CHART OF ACCOUNTS FLEXFIELD
131 ONLY NEEDED FOR OTHER FLEXFIELDS
132 --
133 -- Retrieve the useful information for the validation logic from the flexfield.
134 --
135 OPEN c_flex;
136 FETCH c_flex
137 INTO v_dynamic_inserts_feasible_f,
138 v_set_defining_column_name;
139
140 --
141 -- Check that the flexfield exists
142 --
143 IF c_flex%FOUND THEN
144 CLOSE c_flex;
145 ELSE
146 CLOSE c_flex;
147 RAISE flexfield_must_exist;
148 END IF;
149
150 --
151 -- Check to see multiflex is allowed for this
152 -- flexfield. Multiflex is not allowed for flexfields
153 -- without a structure defining column and for
154 -- flexfields using the set feature (hard coded here)
155 -- Flag error if a new record is being created for
156 -- these flexfields. The default 101 structure will be
157 -- created on registering the flexfield.
158 --
159 IF (v_operation = 'DML_INSERT' AND ((v_set_defining_column_name IS NULL) OR
160 (v_id_flex_code IN ('MSTK', 'MTLL', 'MICG', 'MDSP')))) THEN
161 RAISE multiflex_not_allowed;
162 END IF;
163
164 --
165 -- Check to see dynamic insert is allowed for this flexfield.
166 --
167 IF (v_dynamic_inserts_feasible_f = 'N' AND v_dynamic_inserts_allowed_f = 'Y') THEN
168 RAISE dynamic_inserts_not_allowed;
169 END IF;*/
170
171 --
172 -- **GL Accounting Flexfield specific validation**
173 -- After inserting a new structure for the "GL Accounting Flexfield",
174 -- we also need to insert some default rows into the FND_FLEX_WORKFLOW_PROCESSES table.
175 --
176 IF (v_application_id = 101 AND v_id_flex_code = 'GL#'
177 AND v_id_flex_num <> 101 AND v_operation = 'DML_INSERT') THEN
178 INSERT INTO FND_FLEX_WORKFLOW_PROCESSES(APPLICATION_ID,
179 ID_FLEX_CODE,
180 ID_FLEX_NUM,
181 WF_ITEM_TYPE,
182 WF_PROCESS_NAME,
183 LAST_UPDATE_DATE,
184 LAST_UPDATED_BY,
185 CREATION_DATE,
186 CREATED_BY,
187 LAST_UPDATE_LOGIN)
188 SELECT FS.APPLICATION_ID,
189 FS.ID_FLEX_CODE,
190 FS.ID_FLEX_NUM,
191 FWP.WF_ITEM_TYPE,
192 'DEFAULT_ACCOUNT_GENERATION',
193 SYSDATE,
194 FS.LAST_UPDATED_BY,
195 SYSDATE,
196 FS.CREATED_BY,
197 FS.LAST_UPDATE_LOGIN
198 FROM FND_FLEX_WORKFLOW_PROCESSES FWP,
199 FND_ID_FLEX_STRUCTURES FS
200 WHERE FWP.APPLICATION_ID = v_application_id
201 AND FWP.ID_FLEX_CODE = v_id_flex_code
202 AND FWP.ID_FLEX_NUM = 101
203 AND FS.APPLICATION_ID = v_application_id
204 AND FS.ID_FLEX_CODE = v_id_flex_code
205 AND FS.ID_FLEX_NUM = v_id_flex_num
206 AND NOT EXISTS (SELECT 'Row already exists'
207 FROM FND_FLEX_WORKFLOW_PROCESSES FWP2
208 WHERE FWP2.APPLICATION_ID = v_application_id
209 AND FWP2.ID_FLEX_CODE = v_id_flex_code
210 AND FWP2.ID_FLEX_NUM = v_id_flex_num
211 AND FWP2.WF_ITEM_TYPE = FWP.WF_ITEM_TYPE);
212 END IF;
213
214 --
215 -- The structure has been validated successfully.
216 --
217 return;
218
219 EXCEPTION
220 WHEN invalid_dml_mode THEN
221 fnd_message.set_name('SQLGL','GL_UNHANDLED_EXCEPTION');
222 fnd_message.set_token('PROCEDURE','GL_CHART_OF_ACCOUNTS_API_PKG.validate_structure');
223 fnd_message.set_token('EVENT','INVALID_DML_MODE');
224 app_exception.raise_exception;
225
226 WHEN flexfield_not_supported THEN
227 fnd_message.set_name('SQLGL','GL_API_COA_FLEX_NOT_SUPPORTED');
228 app_exception.raise_exception;
229
230 /* NOT NEEDED FOR THE CHART OF ACCOUNTS FLEXFIELD
231 ONLY NEEDED FOR OTHER FLEXFIELDS
232 WHEN flexfield_must_exist THEN
233 fnd_message.set_name('SQLGL','GL_UNHANDLED_EXCEPTION');
234 fnd_message.set_token('PROCEDURE','GL_CHART_OF_ACCOUNTS_API_PKG.validate_structure');
235 fnd_message.set_token('EVENT','flexfield_must_exist');
236 app_exception.raise_exception;
237
238 WHEN multiflex_not_allowed THEN
239 fnd_message.set_name('SQLGL','GL_UNHANDLED_EXCEPTION');
240 fnd_message.set_token('PROCEDURE','GL_CHART_OF_ACCOUNTS_API_PKG.validate_structure');
241 fnd_message.set_token('EVENT','multiflex_not_allowed');
242 app_exception.raise_exception;
243
244 WHEN dynamic_inserts_not_allowed THEN
245 fnd_message.set_name('SQLGL','GL_UNHANDLED_EXCEPTION');
246 fnd_message.set_token('PROCEDURE','GL_CHART_OF_ACCOUNTS_API_PKG.validate_structure');
247 fnd_message.set_token('EVENT','dynamic_inserts_not_allowed');
248 app_exception.raise_exception;*/
249
250 WHEN OTHERS THEN
251 fnd_message.set_name('SQLGL','GL_UNHANDLED_EXCEPTION');
252 fnd_message.set_token('PROCEDURE','GL_CHART_OF_ACCOUNTS_API_PKG.validate_structure');
253 fnd_message.set_token('EVENT','OTHERS');
254 app_exception.raise_exception;
255
256 END validate_structure;
257
258
259 --
260 -- Procedure
261 -- validate_structure_tl
262 -- Purpose
263 -- Do the validation for the translated structure
264 -- (FND_ID_FLEX_STRUCTURES_TL table)
265 -- History
266 -- 10.09.2000 O Monnier Created.
267 --
268 PROCEDURE validate_structure_tl(v_application_id IN NUMBER,
269 v_id_flex_code IN VARCHAR2,
270 v_id_flex_num IN NUMBER,
271 v_language IN VARCHAR2,
272 v_id_flex_structure_name IN VARCHAR2,
273 v_userenvlang IN VARCHAR2)
274 IS
275 v_count NUMBER;
276
277 BEGIN
278 --
279 -- Check if the flexfield is supported by the API
280 --
281 IF (NOT is_flexfield_supported(v_application_id => v_application_id,
282 v_id_flex_code => v_id_flex_code))
283 THEN
284 RAISE flexfield_not_supported;
285 END IF;
286
287 --
288 -- Check that IdFlexStructureName is unique for a particular key flexfield if the language
289 -- is userenv('LANG').
290 --
291 IF (v_userenvlang = v_language) THEN
292 SELECT count(*)
293 INTO v_count
294 FROM FND_ID_FLEX_STRUCTURES_VL
295 WHERE application_id = v_application_id
296 AND id_flex_code = v_id_flex_code
297 AND id_flex_structure_name = v_id_flex_structure_name;
298
299 IF (v_count > 1) THEN
300 RAISE structure_name_not_unique;
301 END IF;
302 END IF;
303
304 --
305 -- The structure tl has been validated successfully.
306 --
307 return;
308
309 EXCEPTION
310 WHEN flexfield_not_supported THEN
311 fnd_message.set_name('SQLGL','GL_API_COA_FLEX_NOT_SUPPORTED');
312 app_exception.raise_exception;
313
314 WHEN structure_name_not_unique THEN
315 fnd_message.set_name('FND','FLEX-DUPLICATE STRUCTURE NAME');
316 app_exception.raise_exception;
317
318 WHEN OTHERS THEN
319 fnd_message.set_name('SQLGL','GL_UNHANDLED_EXCEPTION');
320 fnd_message.set_token('PROCEDURE','GL_CHART_OF_ACCOUNTS_API_PKG.validate_structure_tl');
321 fnd_message.set_token('EVENT','OTHERS');
322 app_exception.raise_exception;
323
324 END validate_structure_tl;
325
326
327 --
328 -- Procedure
329 -- validate_segment
330 -- Purpose
331 -- Do the validation for one particular segment of a structure
332 -- (FND_ID_FLEX_SEGMENTS table)
333 -- History
334 -- 10.09.2000 O Monnier Created.
335 --
336 PROCEDURE validate_segment(v_application_id IN NUMBER,
337 v_id_flex_code IN VARCHAR2,
338 v_id_flex_num IN NUMBER,
339 v_application_column_name IN VARCHAR2,
340 v_segment_name IN VARCHAR2,
341 v_segment_num IN NUMBER,
342 v_enabled_flag IN VARCHAR2,
343 v_required_flag IN VARCHAR2,
344 v_display_flag IN VARCHAR2,
345 v_display_size IN NUMBER,
346 v_security_enabled_flag IN VARCHAR2,
347 v_flex_value_set_id IN NUMBER,
348 v_default_type IN VARCHAR2,
349 v_default_value IN VARCHAR2)
350 IS
351 v_count NUMBER;
352 v_industry_type VARCHAR2(1);
353 v_flex_value_set_name VARCHAR2(60) := '';
354 v_validation_type VARCHAR2(1) := '';
355 v_vset_security_enabled_flag VARCHAR2(1) := '';
356 v_format_type VARCHAR2(1) := '';
357 v_maximum_size NUMBER(3) := '';
358 v_number_precision NUMBER(3) := '';
359 v_alphanumeric_allowed_flag VARCHAR2(1) := '';
360 v_uppercase_only_flag VARCHAR2(1) := '';
361 v_numeric_mode_enabled_flag VARCHAR2(1) := '';
362 v_minimum_value VARCHAR2(150) := '';
363 v_maximum_value VARCHAR2(150) := '';
364 v_storage_value VARCHAR2(2000) := '';
365 v_display_value VARCHAR2(2000) := '';
366 v_width NUMBER(15);
367 v_column_type VARCHAR2(1);
368 v_defined BOOLEAN;
369 -- v_check_format_type VARCHAR2(1);
370
371 -- Retrieve the value set information required for the validation from the value set id.
372 CURSOR c_flex_value_set IS
373 SELECT flex_value_set_name,
374 validation_type,
375 security_enabled_flag,
376 format_type,
377 maximum_size,
378 number_precision,
379 alphanumeric_allowed_flag,
380 uppercase_only_flag,
381 numeric_mode_enabled_flag,
382 minimum_value,
383 maximum_value
384 FROM FND_FLEX_VALUE_SETS
385 WHERE flex_value_set_id = v_flex_value_set_id;
386
387 -- Validate the application_column_name
388 CURSOR c_check_column_name IS
389 SELECT c.width, c.column_type
390 FROM FND_COLUMNS c,
391 FND_TABLES t,
392 FND_ID_FLEXS f
393 WHERE c.application_id = t.application_id
394 AND c.table_id = t.table_id
395 AND c.column_name = v_application_column_name
396 AND c.flexfield_usage_code = 'K'
397 AND t.application_id = f.table_application_id
398 AND t.table_name = f.application_table_name
399 AND f.application_id = v_application_id
400 AND f.id_flex_code = v_id_flex_code
401 AND ( (v_industry_type = 'G'
402 AND v_id_flex_code = 'GLAT'
403 OR ((v_id_flex_code <> 'GLAT') and (c.column_name not like 'SEGMENT_ATTRIBUTE%')))
404 OR NVL(v_industry_type, 'C') <> 'G');
405
406 BEGIN
407 --
408 -- Check if the flexfield is supported by the API
409 --
410 IF (NOT is_flexfield_supported(v_application_id => v_application_id,
411 v_id_flex_code => v_id_flex_code))
412 THEN
413 RAISE flexfield_not_supported;
414 END IF;
415
416 --
417 -- Retrieve the industry type for the validation of the application_column_name
418 --
419 FND_PROFILE.GET_SPECIFIC(name_z => 'INDUSTRY',
420 val_z => v_industry_type,
421 defined_z => v_defined);
422
423 IF NOT v_defined THEN
424
425 BEGIN
426 SELECT fpi.industry
427 INTO v_industry_type
428 FROM fnd_product_installations fpi
429 WHERE fpi.application_id = v_application_id;
430 EXCEPTION
431 WHEN OTHERS THEN
432 v_industry_type := 'N';
433 END;
434
435 END IF;
436
437 --
438 -- Retrieve the width of the application_column_name.
439 --
440 OPEN c_check_column_name;
441 FETCH c_check_column_name
442 INTO v_width, v_column_type;
443
444 --
445 -- Check that the application_column_name exists
446 --
447 IF c_check_column_name%FOUND THEN
448 CLOSE c_check_column_name;
449 ELSE
450 CLOSE c_check_column_name;
451 RAISE invalid_app_column_name;
452 END IF;
453
454 --
455 -- Check that SegmentNum is unique for a particular structure
456 --
457 SELECT count(*)
458 INTO v_count
459 FROM FND_ID_FLEX_SEGMENTS
460 WHERE application_id = v_application_id
461 AND id_flex_code = v_id_flex_code
462 AND id_flex_num = v_id_flex_num
463 AND segment_num = v_segment_num;
464
465 IF (v_count > 1) THEN
466 RAISE segment_num_not_unique;
467 END IF;
468
469 --
470 -- **GL Accounting Flexfield specific validation**
471 -- The required check box must be checked for each segment
472 --
473 IF (v_application_id = 101 AND v_id_flex_code = 'GL#' AND v_required_flag <> 'Y') THEN
474 RAISE gl_segment_must_be_required;
475 END IF;
476
477 --
478 -- **GL Accounting Flexfield specific validation**
479 -- The display check box must be checked for each segment
480 --
481 IF (v_application_id = 101 AND v_id_flex_code = 'GL#' AND v_display_flag <> 'Y') THEN
482 RAISE gl_segment_must_be_displayed;
483 END IF;
484
485
486 IF (v_flex_value_set_id IS NOT NULL) THEN
487 --
488 -- VALIDATION CODE FOR WHEN A VALUE SET HAS BEEN ASSIGNED TO THE SEGMENT
489 --
490
491 --
492 -- Retrieve the useful information for the validation logic from the assigned value set.
493 --
494 OPEN c_flex_value_set;
495 FETCH c_flex_value_set
496 INTO v_flex_value_set_name,
497 v_validation_type,
498 v_vset_security_enabled_flag,
499 v_format_type,
500 v_maximum_size,
501 v_number_precision,
502 v_alphanumeric_allowed_flag,
503 v_uppercase_only_flag,
504 v_numeric_mode_enabled_flag,
505 v_minimum_value,
506 v_maximum_value;
507
508 --
509 -- If a value set has been assigned to a segment, check that the value set exists
510 -- and retrieve the value set information
511 --
512 IF c_flex_value_set%FOUND THEN
513 CLOSE c_flex_value_set;
514 ELSE
515 CLOSE c_flex_value_set;
516 RAISE value_set_must_exist;
517 END IF;
518
519 --
520 -- If security is not enabled for the chosen value set, security cannot be enabled for
521 -- the segment.
522 --
523 IF (v_vset_security_enabled_flag = 'N' AND v_security_enabled_flag = 'Y') THEN
524 RAISE vset_security_not_enabled;
525 END IF;
526
527 --
528 -- **PHASE I - Only independent value set are supported for phase I**
529 -- The validation type of the value set must be "independent".
530 --
531 IF (v_validation_type <> 'I') THEN
532 RAISE invalid_value_set;
533 END IF;
534
535 --
536 -- **GL Accounting Flexfield specific validation**
537 -- The format type of the value set must be "character".
538 --
539 IF (v_application_id = 101 AND v_id_flex_code = 'GL#' AND v_format_type <> 'C') THEN
540 RAISE gl_format_must_be_char;
541 END IF;
542
543 --
544 -- The default type of the default value can be 'D' only if the value set has a format
545 -- type of 'D', 'X', or 'Y' or a format type of 'C' with a maximum size greater than 9
546 --
547 IF (v_default_type = 'D'
548 AND v_format_type NOT IN ('D','X','Y')
549 AND (v_format_type <> 'C' OR v_maximum_size < 9))
550 THEN
551 RAISE invalid_date_default_type;
552 END IF;
553
554 --
555 -- The default type of the default value can be 'T' only if the value set has a format
556 -- type of 'T', 'I', 'Y', or 'Z' or a format type of 'C' with a maximum size greater than 5
557 --
558 IF (v_default_type = 'T'
559 AND v_format_type NOT IN ('T','I','Y','Z')
560 AND (v_format_type <>'C' OR v_maximum_size < 5))
561 THEN
562 RAISE invalid_time_default_type;
563 END IF;
564
565 --
566 -- The display size cannot exceeds the value set maximum size.
567 --
568 IF (v_maximum_size < v_display_size) THEN
569 RAISE display_size_too_large;
570 END IF;
571
572 --
573 -- The maximum size of the value set cannot be greater than the size of the segment column
574 --
575 IF (v_width < v_maximum_size) THEN
576 RAISE maximum_size_too_large;
577 END IF;
578
579 --
580 -- If the default type of the default value is "constant", the default value
581 -- should be validated.
582 --
583 IF (v_default_type = 'C') THEN
584 IF (NOT FND_FLEX_VAL_UTIL.IS_VALUE_VALID
585 (p_value => v_default_value,
586 p_is_displayed => TRUE,
587 p_vset_name => nvl(v_flex_value_set_name, 'FORM:FNDFFMIS'),
588 p_vset_format => nvl(v_format_type,'C'),
589 p_max_length => nvl(v_maximum_size, 150),
590 p_precision => nvl(v_number_precision, 0),
591 p_alpha_allowed => nvl(v_alphanumeric_allowed_flag, 'Y'),
592 p_uppercase_only => nvl(v_uppercase_only_flag, 'N'),
593 p_zero_fill => nvl(v_numeric_mode_enabled_flag, 'N'),
594 p_min_value => v_minimum_value,
595 p_max_value => v_maximum_value,
596 x_storage_value => v_storage_value,
597 x_display_value => v_display_value)) THEN
598 RAISE invalid_default_value;
599 END IF;
600
601 IF (v_default_value <> v_storage_value) THEN
602 UPDATE FND_ID_FLEX_SEGMENTS
603 SET default_value = v_storage_value
604 WHERE application_id = v_application_id
605 AND id_flex_code = v_id_flex_code
606 AND id_flex_num = v_id_flex_num
607 AND application_column_name = v_application_column_name;
608 END IF;
609 END IF;
610
611 ELSE
612 --
613 -- VALIDATION CODE FOR WHEN NO VALUE SET HAS BEEN ASSIGNED TO THE SEGMENT
614 --
615
616 --
617 -- **GL Accounting Flexfield specific validation**
618 -- You must enter a value set in the value set field for each segment of
619 -- the accounting flexfield.
620 --
621 IF (v_application_id = 101 AND v_id_flex_code = 'GL#') THEN
622 RAISE gl_value_set_must_exist;
623 END IF;
624
625 /* NOT NEEDED FOR THE CHART OF ACCOUNTS FLEXFIELD
626 ONLY NEEDED FOR OTHER FLEXFIELDS
627 --
628 -- If the default type of the default value is "constant", the default value
629 -- should be validated.
630 --
631 IF (v_default_type = 'C') THEN
632
633 --
634 -- Validate for the application column
635 --
636 IF (v_column_type = 'V') THEN
637 v_format_type := 'C';
638 ELSE
639 v_format_type := v_column_type;
640 END IF;
641 v_maximum_size := v_width;
642
643 IF (NOT FND_FLEX_VAL_UTIL.IS_VALUE_VALID
644 (p_value => v_default_value,
645 p_is_displayed => TRUE,
646 p_vset_name => nvl(v_flex_value_set_name, 'FORM:FNDFFMIS'),
647 p_vset_format => nvl(v_format_type,'C'),
648 p_max_length => nvl(v_maximum_size, 150),
649 p_precision => nvl(v_number_precision, 0),
650 p_alpha_allowed => nvl(v_alphanumeric_allowed_flag, 'Y'),
651 p_uppercase_only => nvl(v_uppercase_only_flag, 'N'),
652 p_zero_fill => nvl(v_numeric_mode_enabled_flag, 'N'),
653 p_min_value => v_minimum_value,
654 p_max_value => v_maximum_value,
655 x_storage_value => v_storage_value,
656 x_display_value => v_display_value)) THEN
657 RAISE invalid_default_value;
658 END IF;
659
660 v_format_type := null;
661 v_maximum_size := null;
662 END IF;
663
664 --
665 -- When no value set has been assigned to the segment,
666 -- the display size cannot exceeds the application column size.
667 --
668 IF (v_width < v_display_size) THEN
669 RAISE display_size_too_large;
670 END IF;*/
671
672 END IF;
673
674 --
675 -- The segment has been validated successfully.
676 --
677 return;
678
679 EXCEPTION
680 WHEN flexfield_not_supported THEN
681 fnd_message.set_name('SQLGL','GL_API_COA_FLEX_NOT_SUPPORTED');
682 app_exception.raise_exception;
683
684 WHEN invalid_app_column_name THEN
685 fnd_message.set_name('SQLGL','GL_API_COA_APP_COL_NAME_INV');
686 fnd_message.set_token('APPCOLNAME',v_application_column_name);
687 app_exception.raise_exception;
688
689 WHEN segment_num_not_unique THEN
690 fnd_message.set_name('SQLGL','GL_API_COA_SEG_NUM_NOT_UNIQUE');
691 app_exception.raise_exception;
692
693 WHEN value_set_must_exist THEN
694 fnd_message.set_name('FND','FLEX-VALUE SET NOT FOUND');
695 fnd_message.set_token('SEGMENT',v_segment_name);
696 app_exception.raise_exception;
697
698 WHEN gl_segment_must_be_required THEN
699 fnd_message.set_name('SQLGL','GL_API_COA_SEG_MUST_BE_REQ');
700 app_exception.raise_exception;
701
702 WHEN gl_segment_must_be_displayed THEN
703 fnd_message.set_name('SQLGL','GL_API_COA_SEG_MUST_BE_DIS');
704 app_exception.raise_exception;
705
706 WHEN invalid_value_set THEN
707 fnd_message.set_name('SQLGL','GL_API_COA_VSET_NOT_SUPPORTED');
708 app_exception.raise_exception;
709
710 WHEN gl_format_must_be_char THEN
711 fnd_message.set_name('SQLGL','GL_API_COA_MUST_BE_CHAR');
712 app_exception.raise_exception;
713
714 WHEN display_size_too_large THEN
715 fnd_message.set_name('SQLGL','GL_API_COA_DIS_SIZE_TOO_LARGE');
716 fnd_message.set_token('MAXSIZE',v_maximum_size);
717 app_exception.raise_exception;
718
719 WHEN maximum_size_too_large THEN
720 fnd_message.set_name('SQLGL','GL_API_COA_MAX_SIZE_TOO_LARGE');
721 fnd_message.set_token('VSETNAME',v_flex_value_set_name);
722 fnd_message.set_token('COLSIZE',v_width);
723 app_exception.raise_exception;
724
725 WHEN invalid_default_value THEN
726 fnd_message.set_name('SQLGL','GL_API_COA_INVALID_DEF_VAL');
727 fnd_message.set_token('DEFVAL',v_default_value);
728 app_exception.raise_exception;
729
730 WHEN vset_security_not_enabled THEN
731 fnd_message.set_name('SQLGL','GL_API_COA_NO_VSET_SECURITY');
732 fnd_message.set_token('VSETNAME',v_flex_value_set_name);
733 app_exception.raise_exception;
734
735 WHEN invalid_date_default_type THEN
736 fnd_message.set_name('SQLGL','GL_API_COA_INV_DATE_DEF_TYPE');
737 fnd_message.set_token('VSETNAME',v_flex_value_set_name);
738 app_exception.raise_exception;
739
740 WHEN invalid_time_default_type THEN
741 fnd_message.set_name('SQLGL','GL_API_COA_INV_TIME_DEF_TYPE');
742 fnd_message.set_token('VSETNAME',v_flex_value_set_name);
743 app_exception.raise_exception;
744
745 WHEN gl_value_set_must_exist THEN
746 fnd_message.set_name('SQLGL','GL_API_COA_VAL_SET_REQUIRED');
747 app_exception.raise_exception;
748
749 WHEN OTHERS THEN
750 fnd_message.set_name('SQLGL','GL_UNHANDLED_EXCEPTION');
751 fnd_message.set_token('PROCEDURE','GL_CHART_OF_ACCOUNTS_API_PKG.validate_segment');
752 fnd_message.set_token('EVENT','OTHERS');
753 app_exception.raise_exception;
754
755 END validate_segment;
756
757
758 --
759 -- Procedure
760 -- validate_segment_tl
761 -- Purpose
762 -- Do the validation for one particular translated segment of a structure
763 -- (FND_ID_FLEX_SEGMENTS_TL table)
764 -- NO VALIDATION CODE IS NEEDED NOW.
765 -- History
766 -- 10.09.2000 O Monnier Created.
767 --
768 PROCEDURE validate_segment_tl(v_application_id IN NUMBER,
769 v_id_flex_code IN VARCHAR2,
770 v_id_flex_num IN NUMBER,
771 v_application_column_name IN VARCHAR2,
772 v_language IN VARCHAR2)
773 IS
774
775 BEGIN
776 --
777 -- Check if the flexfield is supported by the API
778 --
779 IF (NOT is_flexfield_supported(v_application_id => v_application_id,
780 v_id_flex_code => v_id_flex_code))
781 THEN
782 RAISE flexfield_not_supported;
783 END IF;
784
785 --
786 -- The segment tl has been validated successfully.
787 --
788 return;
789
790 EXCEPTION
791 WHEN flexfield_not_supported THEN
792 fnd_message.set_name('SQLGL','GL_API_COA_FLEX_NOT_SUPPORTED');
793 app_exception.raise_exception;
794
795 WHEN OTHERS THEN
796 fnd_message.set_name('SQLGL','GL_UNHANDLED_EXCEPTION');
797 fnd_message.set_token('PROCEDURE','GL_CHART_OF_ACCOUNTS_API_PKG.validate_segment_tl');
798 fnd_message.set_token('EVENT','OTHERS');
799 app_exception.raise_exception;
800
801 END validate_segment_tl;
802
803
804 --
805 -- Procedure
806 -- validate_seg_attribute_value
807 -- Purpose
808 -- Do the validation for one particular segment attribute
809 -- (FND_SEGMENT_ATTRIBUTE_VALUES table)
810 -- History
811 -- 10.09.2000 O Monnier Created.
812 --
813 PROCEDURE validate_seg_attribute_value(v_application_id IN NUMBER,
814 v_id_flex_code IN VARCHAR2,
815 v_id_flex_num IN NUMBER,
816 v_application_column_name IN VARCHAR2,
817 v_segment_attribute_type IN VARCHAR2,
818 v_attribute_value IN VARCHAR2)
819 IS
820 v_global_flag VARCHAR2(1);
821 v_unique_flag VARCHAR2(1);
822 v_segment_prompt VARCHAR2(50);
823 v_count NUMBER;
824
825 -- Retrieve the attribute type information required for the validation.
826 CURSOR c_check_seg_attribute_type IS
827 SELECT global_flag,
828 unique_flag,
829 segment_prompt
830 FROM FND_SEGMENT_ATTRIBUTE_TYPES
831 WHERE application_id = v_application_id
832 AND id_flex_code = v_id_flex_code
833 AND segment_attribute_type = v_segment_attribute_type;
834
835 BEGIN
836 --
837 -- Check if the flexfield is supported by the API
838 --
839 IF (NOT is_flexfield_supported(v_application_id => v_application_id,
840 v_id_flex_code => v_id_flex_code))
841 THEN
842 RAISE flexfield_not_supported;
843 END IF;
844
845 --
846 -- The segment attribute type must exist in the attribute type table
847 --
848 OPEN c_check_seg_attribute_type;
849 FETCH c_check_seg_attribute_type
850 INTO v_global_flag,
851 v_unique_flag,
852 v_segment_prompt;
853
854 IF c_check_seg_attribute_type%FOUND THEN
855 CLOSE c_check_seg_attribute_type;
856 ELSE
857 CLOSE c_check_seg_attribute_type;
858 RAISE invalid_seg_attribute_type;
859 END IF;
860
861 --
862 -- The global qualifiers should apply to all segments
863 --
864 IF (v_attribute_value = 'N' AND v_global_flag = 'Y') THEN
865 RAISE global_qualifier_error;
866 END IF;
867
868 --
869 -- A unique qualifier should not be assigned to more than one segment in the structure
870 --
871 IF (v_attribute_value = 'Y' AND v_unique_flag = 'Y') THEN
872 SELECT count(*)
873 INTO v_count
874 FROM FND_SEGMENT_ATTRIBUTE_VALUES
875 WHERE application_id = v_application_id
876 AND id_flex_code = v_id_flex_code
877 AND id_flex_num = v_id_flex_num
878 AND segment_attribute_type = v_segment_attribute_type
879 AND attribute_value = 'Y';
880
881 IF (v_count > 1) THEN
882 RAISE qualifier_not_unique;
883 END IF;
884
885 END IF;
886
887 --
888 -- The segment attribute value has been validated successfully.
889 --
890 return;
891
892 EXCEPTION
893 WHEN flexfield_not_supported THEN
894 fnd_message.set_name('SQLGL','GL_API_COA_FLEX_NOT_SUPPORTED');
895 app_exception.raise_exception;
896
897 WHEN invalid_seg_attribute_type THEN
898 fnd_message.set_name('SQLGL','GL_API_COA_INVALID_ATTR_TYPE');
899 app_exception.raise_exception;
900
901 WHEN global_qualifier_error THEN
902 fnd_message.set_name('SQLGL','GL_API_COA_GLOB_QUAL_NOT_YES');
903 fnd_message.set_token('QUAL',v_segment_prompt);
904 app_exception.raise_exception;
905
906 WHEN qualifier_not_unique THEN
907 fnd_message.set_name('SQLGL','GL_API_COA_QUAL_NOT_UNIQUE');
908 fnd_message.set_token('QUAL',v_segment_prompt);
909 app_exception.raise_exception;
910
911 WHEN OTHERS THEN
912 fnd_message.set_name('SQLGL','GL_UNHANDLED_EXCEPTION');
913 fnd_message.set_token('PROCEDURE','GL_CHART_OF_ACCOUNTS_API_PKG.validate_seg_attribute_value');
914 fnd_message.set_token('EVENT','OTHERS');
915 app_exception.raise_exception;
916
917 END validate_seg_attribute_value;
918
919 --
920 -- Procedure
921 -- validate_value_set
922 -- (FND_FLEX_VALUE_SETS table)
923 -- Purpose
924 -- Do the validation for one particular value set
925 -- History
926 -- 10.09.2000 O Monnier Created.
927 --
928 PROCEDURE validate_value_set(v_flex_value_set_id IN NUMBER,
929 v_flex_value_set_name IN VARCHAR2,
930 v_format_type IN VARCHAR2,
931 v_maximum_size IN NUMBER,
932 v_alphanumeric_allowed_flag IN VARCHAR2,
933 v_uppercase_only_flag IN VARCHAR2,
934 v_numeric_mode_enabled_flag IN VARCHAR2,
935 v_dependant_default_value IN VARCHAR2,
936 v_minimum_value IN VARCHAR2,
937 v_maximum_value IN VARCHAR2,
938 v_number_precision IN NUMBER)
939 IS
940 v_storage_value VARCHAR2(2000) := '';
941 v_display_value VARCHAR2(2000) := '';
942 v_is_minimum_value_valid BOOLEAN := TRUE;
943 v_is_maximum_value_valid BOOLEAN := TRUE;
944
945 BEGIN
946
947 --
948 -- The value set name cannot start with '$FLEX$.'
949 --
950 IF (v_flex_value_set_name like '$FLEX$.%') THEN
951 RAISE invalid_value_set_name;
952 END IF;
953
954 --
955 -- The minimum value must be less than the maximum value.
956 --
957 IF (v_minimum_value > v_maximum_value) THEN
958 RAISE invalid_minimum_maximum;
959 END IF;
960
961 --
962 -- If they are defined, the Minimum Value, the Maximum Value, and the
963 -- Dependant Default Value must be validated by calling
964 -- FND_FLEX_VAL_UTIL.is_value_valid.
965 --
966 IF (v_dependant_default_value IS NOT NULL) THEN
967 IF (NOT FND_FLEX_VAL_UTIL.IS_VALUE_VALID
968 (p_value => v_dependant_default_value,
969 p_is_displayed => TRUE,
970 p_vset_name => v_flex_value_set_name,
971 p_vset_format => v_format_type,
972 p_max_length => v_maximum_size,
973 p_precision => v_number_precision,
974 p_alpha_allowed => v_alphanumeric_allowed_flag,
975 p_uppercase_only => v_uppercase_only_flag,
976 p_zero_fill => v_numeric_mode_enabled_flag,
977 p_min_value => v_minimum_value,
978 p_max_value => v_maximum_value,
979 x_storage_value => v_storage_value,
980 x_display_value => v_display_value)) THEN
981 RAISE invalid_dependant_value;
982 END IF;
983
984 IF (v_dependant_default_value <> v_storage_value) THEN
985 UPDATE FND_FLEX_VALUE_SETS
986 SET dependant_default_value = v_storage_value
987 WHERE flex_value_set_id = v_flex_value_set_id;
988 END IF;
989 END IF;
990
991 IF (v_minimum_value IS NOT NULL) THEN
992 IF (NOT FND_FLEX_VAL_UTIL.IS_VALUE_VALID
993 (p_value => v_minimum_value,
994 p_is_displayed => TRUE,
995 p_vset_name => v_flex_value_set_name,
996 p_vset_format => v_format_type,
997 p_max_length => v_maximum_size,
998 p_precision => v_number_precision,
999 p_alpha_allowed => v_alphanumeric_allowed_flag,
1000 p_uppercase_only => v_uppercase_only_flag,
1001 p_zero_fill => v_numeric_mode_enabled_flag,
1002 p_min_value => v_minimum_value,
1003 p_max_value => v_maximum_value,
1004 x_storage_value => v_storage_value,
1005 x_display_value => v_display_value)) THEN
1006 v_is_minimum_value_valid := FALSE;
1007 END IF;
1008
1009 IF (v_minimum_value <> v_storage_value) THEN
1010 UPDATE FND_FLEX_VALUE_SETS
1011 SET minimum_value = v_storage_value
1012 WHERE flex_value_set_id = v_flex_value_set_id;
1013 END IF;
1014 END IF;
1015
1016 IF (v_maximum_value IS NOT NULL) THEN
1017 IF (NOT FND_FLEX_VAL_UTIL.IS_VALUE_VALID
1018 (p_value => v_maximum_value,
1019 p_is_displayed => TRUE,
1020 p_vset_name => v_flex_value_set_name,
1021 p_vset_format => v_format_type,
1022 p_max_length => v_maximum_size,
1023 p_precision => v_number_precision,
1024 p_alpha_allowed => v_alphanumeric_allowed_flag,
1025 p_uppercase_only => v_uppercase_only_flag,
1026 p_zero_fill => v_numeric_mode_enabled_flag,
1027 p_min_value => v_minimum_value,
1028 p_max_value => v_maximum_value,
1029 x_storage_value => v_storage_value,
1030 x_display_value => v_display_value)) THEN
1031 v_is_maximum_value_valid := FALSE;
1032 END IF;
1033
1034 IF (v_maximum_value <> v_storage_value) THEN
1035 UPDATE FND_FLEX_VALUE_SETS
1036 SET maximum_value = v_storage_value
1037 WHERE flex_value_set_id = v_flex_value_set_id;
1038 END IF;
1039 END IF;
1040
1041 IF (NOT v_is_minimum_value_valid) THEN
1042 IF (NOT v_is_maximum_value_valid) THEN
1043 -- In this case, we do not know whether minimum value is invalid,
1044 -- maximum value is invalid, or both.
1045 -- (For example, if maximum value is invalid, then in some cases, the validation of
1046 -- minimum value (FND_FLEX_VAL_UTIL.IS_VALUE_VALID) is going to failed since we are
1047 -- passing the maximum value argument. In this case, we should not display mimimum value
1048 -- invalid
1049 RAISE invalid_minormax_value;
1050 ELSE
1051 -- We know that the minimum value is invalid.
1052 RAISE invalid_minimum_value;
1053 END IF;
1054 END IF;
1055
1056 IF (NOT v_is_maximum_value_valid) THEN
1057 -- We know that the maximum value is invalid.
1058 RAISE invalid_maximum_value;
1059 END IF;
1060
1061 --
1062 -- The value set has been validated successfully.
1063 --
1064 return;
1065
1066 EXCEPTION
1067 WHEN invalid_value_set_name THEN
1068 fnd_message.set_name('FND','FLEX-VALUE SET RESERVED WORD');
1069 app_exception.raise_exception;
1070
1071 WHEN invalid_minimum_maximum THEN
1072 fnd_message.set_name('SQLGL','GL_API_COA_MIN_GREATER_MAX');
1073 app_exception.raise_exception;
1074
1075 WHEN invalid_dependant_value THEN
1076 fnd_message.set_name('SQLGL','GL_API_COA_INVALID_DEP_DEF_VAL');
1077 fnd_message.set_token('DEFVAL',v_dependant_default_value);
1078 app_exception.raise_exception;
1079
1080 WHEN invalid_minimum_value THEN
1081 fnd_message.set_name('SQLGL','GL_API_COA_INVALID_MIN_VAL');
1082 fnd_message.set_token('MINVAL',v_minimum_value);
1083 app_exception.raise_exception;
1084
1085 WHEN invalid_maximum_value THEN
1086 fnd_message.set_name('SQLGL','GL_API_COA_INVALID_MAX_VAL');
1087 fnd_message.set_token('MAXVAL',v_maximum_value);
1088 app_exception.raise_exception;
1089
1090 WHEN invalid_minormax_value THEN
1091 fnd_message.set_name('SQLGL','GL_API_COA_INV_MINORMAX_VAL');
1092 fnd_message.set_token('MINVAL',v_minimum_value);
1093 fnd_message.set_token('MAXVAL',v_maximum_value);
1094 app_exception.raise_exception;
1095
1096 WHEN OTHERS THEN
1097 fnd_message.set_name('SQLGL','GL_UNHANDLED_EXCEPTION');
1098 fnd_message.set_token('PROCEDURE','GL_CHART_OF_ACCOUNTS_API_PKG.validate_value_set');
1099 fnd_message.set_token('EVENT','OTHERS');
1100 app_exception.raise_exception;
1101
1102 END validate_value_set;
1103
1104
1105 --
1106 -- Procedure
1107 -- validate_validation_qualifier
1108 -- Purpose
1109 -- Do the validation for one particular validation qualifier
1110 -- (FND_FLEX_VALIDATION_QUALIFIERS table)
1111 -- History
1112 -- 10.09.2000 O Monnier Created.
1113 --
1114 PROCEDURE validate_validation_qualifier(v_id_flex_application_id IN NUMBER,
1115 v_id_flex_code IN VARCHAR2,
1116 v_flex_value_set_id IN NUMBER,
1117 v_segment_attribute_type IN VARCHAR2,
1118 v_value_attribute_type IN VARCHAR2)
1119 IS
1120 v_check_val_attribute_type VARCHAR2(10);
1121 v_value_attribute_type_err VARCHAR2(30);
1122 v_assignment_date DATE;
1123 v_assignment_date_err DATE;
1124
1125 -- Cursor used to check that the value attribute type exist in the value attribute type
1126 -- table.
1127 CURSOR c_check_val_attribute_type IS
1128 SELECT 'Exists'
1129 FROM FND_VALUE_ATTRIBUTE_TYPES
1130 WHERE application_id = v_id_flex_application_id
1131 AND id_flex_code = v_id_flex_code
1132 AND segment_attribute_type = v_segment_attribute_type
1133 AND value_attribute_type = v_value_attribute_type;
1134
1135 -- Cursor to check if the assignment dates for the GL Accounting
1136 -- Flexfield qualifiers are in the right order.
1137 CURSOR c_check_date_detail_budgeting IS
1138 SELECT value_attribute_type, assignment_date
1139 FROM FND_FLEX_VALIDATION_QUALIFIERS
1140 WHERE id_flex_application_id = v_id_flex_application_id
1141 AND id_flex_code = v_id_flex_code
1142 AND flex_value_set_id = v_flex_value_set_id
1143 AND value_attribute_type IN ('DETAIL_POSTING_ALLOWED',
1144 'GL_ACCOUNT_TYPE',
1145 'GL_CONTROL_ACCOUNT',
1146 'RECONCILIATION FLAG')
1147 AND assignment_date < v_assignment_date;
1148
1149 CURSOR c_check_date_detail_posting IS
1150 SELECT value_attribute_type, assignment_date
1151 FROM FND_FLEX_VALIDATION_QUALIFIERS
1152 WHERE id_flex_application_id = v_id_flex_application_id
1153 AND id_flex_code = v_id_flex_code
1154 AND flex_value_set_id = v_flex_value_set_id
1155 AND ((value_attribute_type = 'DETAIL_BUDGETING_ALLOWED'
1156 AND assignment_date > v_assignment_date)
1157 OR
1158 (value_attribute_type IN ('GL_ACCOUNT_TYPE',
1159 'GL_CONTROL_ACCOUNT',
1160 'RECONCILIATION FLAG')
1161 AND assignment_date < v_assignment_date));
1162
1163 CURSOR c_check_date_account IS
1164 SELECT value_attribute_type, assignment_date
1165 FROM FND_FLEX_VALIDATION_QUALIFIERS
1166 WHERE id_flex_application_id = v_id_flex_application_id
1167 AND id_flex_code = v_id_flex_code
1168 AND flex_value_set_id = v_flex_value_set_id
1169 AND ((value_attribute_type IN ('DETAIL_BUDGETING_ALLOWED',
1170 'DETAIL_POSTING_ALLOWED')
1171 AND assignment_date > v_assignment_date)
1172 OR
1173 (value_attribute_type IN ('GL_CONTROL_ACCOUNT',
1174 'RECONCILIATION FLAG')
1175 AND assignment_date < v_assignment_date));
1176
1177 CURSOR c_check_date_control IS
1178 SELECT value_attribute_type, assignment_date
1179 FROM FND_FLEX_VALIDATION_QUALIFIERS
1180 WHERE id_flex_application_id = v_id_flex_application_id
1181 AND id_flex_code = v_id_flex_code
1182 AND flex_value_set_id = v_flex_value_set_id
1183 AND value_attribute_type IN ('DETAIL_BUDGETING_ALLOWED',
1184 'DETAIL_POSTING_ALLOWED',
1185 'GL_ACCOUNT_TYPE')
1186 AND assignment_date > v_assignment_date;
1187
1188
1189 CURSOR c_check_date_reconcilition IS
1190 SELECT value_attribute_type, assignment_date
1191 FROM FND_FLEX_VALIDATION_QUALIFIERS
1192 WHERE id_flex_application_id = v_id_flex_application_id
1193 AND id_flex_code = v_id_flex_code
1194 AND flex_value_set_id = v_flex_value_set_id
1195 AND value_attribute_type IN ('DETAIL_BUDGETING_ALLOWED',
1196 'DETAIL_POSTING_ALLOWED',
1197 'GL_ACCOUNT_TYPE')
1198 AND assignment_date > v_assignment_date;
1199
1200 BEGIN
1201 --
1202 -- Check if the flexfield is supported by the API
1203 --
1204 IF (NOT is_flexfield_supported(v_application_id => v_id_flex_application_id,
1205 v_id_flex_code => v_id_flex_code))
1206 THEN
1207 RAISE flexfield_not_supported;
1208 END IF;
1209
1210 --
1211 -- The value attribute type must exist in the attribute type table.
1212 --
1213 OPEN c_check_val_attribute_type;
1214 FETCH c_check_val_attribute_type
1215 INTO v_check_val_attribute_type;
1216
1217 IF c_check_val_attribute_type%FOUND THEN
1218 CLOSE c_check_val_attribute_type;
1219 ELSE
1220 CLOSE c_check_val_attribute_type;
1221 RAISE invalid_val_attribute_type;
1222 END IF;
1223
1224 --
1225 -- **GL Accounting Flexfield specific validation**
1226 -- The flexfield qualifier assignment dates must be in the following chronological
1227 -- order(if they exists):
1228 -- DETAIL_BUDGETING_ALLOWED, DETAIL_POSTING_ALLOWED, and GL_ACCOUNT_TYPE
1229 -- GL_CONTROL_ACCOUNT and RECONCILIATION FLAG after.
1230 SELECT assignment_date
1231 INTO v_assignment_date
1232 FROM FND_FLEX_VALIDATION_QUALIFIERS
1233 WHERE id_flex_application_id = v_id_flex_application_id
1234 AND id_flex_code = v_id_flex_code
1235 AND flex_value_set_id = v_flex_value_set_id
1236 AND segment_attribute_type = v_segment_attribute_type
1237 AND value_attribute_type = v_value_attribute_type;
1238
1239 IF (v_value_attribute_type = 'DETAIL_BUDGETING_ALLOWED') THEN
1240 OPEN c_check_date_detail_budgeting;
1241 FETCH c_check_date_detail_budgeting
1242 INTO v_value_attribute_type_err, v_assignment_date_err;
1243
1244 IF c_check_date_detail_budgeting%NOTFOUND THEN
1245 CLOSE c_check_date_detail_budgeting;
1246 ELSE
1247 CLOSE c_check_date_detail_budgeting;
1248 RAISE invalid_assignment_date_order;
1249 END IF;
1250 END IF;
1251
1252 IF (v_value_attribute_type = 'DETAIL_POSTING_ALLOWED') THEN
1253 OPEN c_check_date_detail_posting;
1254 FETCH c_check_date_detail_posting
1255 INTO v_value_attribute_type_err, v_assignment_date_err;
1256
1257 IF c_check_date_detail_posting%NOTFOUND THEN
1258 CLOSE c_check_date_detail_posting;
1259 ELSE
1260 CLOSE c_check_date_detail_posting;
1261 RAISE invalid_assignment_date_order;
1262 END IF;
1263 END IF;
1264
1265 IF (v_value_attribute_type = 'GL_ACCOUNT_TYPE') THEN
1266 OPEN c_check_date_account;
1267 FETCH c_check_date_account
1268 INTO v_value_attribute_type_err, v_assignment_date_err;
1269
1270 IF c_check_date_account%NOTFOUND THEN
1271 CLOSE c_check_date_account;
1272 ELSE
1273 CLOSE c_check_date_account;
1274 RAISE invalid_assignment_date_order;
1275 END IF;
1276 END IF;
1277
1278 IF (v_value_attribute_type = 'GL_CONTROL_ACCOUNT') THEN
1279 OPEN c_check_date_control;
1280 FETCH c_check_date_control
1281 INTO v_value_attribute_type_err, v_assignment_date_err;
1282
1283 IF c_check_date_control%NOTFOUND THEN
1284 CLOSE c_check_date_control;
1285 ELSE
1286 CLOSE c_check_date_control;
1287 RAISE invalid_assignment_date_order;
1288 END IF;
1289 END IF;
1290
1291 IF (v_value_attribute_type = 'RECONCILIATION FLAG') THEN
1292 OPEN c_check_date_reconcilition;
1293 FETCH c_check_date_reconcilition
1294 INTO v_value_attribute_type_err, v_assignment_date_err;
1295
1296 IF c_check_date_reconcilition%NOTFOUND THEN
1297 CLOSE c_check_date_reconcilition;
1298 ELSE
1299 CLOSE c_check_date_reconcilition;
1300 RAISE invalid_assignment_date_order;
1301 END IF;
1302 END IF;
1303
1304 --
1305 -- The validation qualifier has been validated successfully.
1306 --
1307 return;
1308
1309 EXCEPTION
1310 WHEN flexfield_not_supported THEN
1311 fnd_message.set_name('SQLGL','GL_API_COA_FLEX_NOT_SUPPORTED');
1312 app_exception.raise_exception;
1313
1314 WHEN invalid_val_attribute_type THEN
1315 fnd_message.set_name('SQLGL','GL_API_COA_INV_VAL_ATTR_TYPE');
1316 app_exception.raise_exception;
1317
1318 WHEN invalid_assignment_date_order THEN
1319 fnd_message.set_name('SQLGL','GL_API_COA_INVALID_DATE_ORDER');
1320
1321 IF (v_assignment_date_err < v_assignment_date) THEN
1322 fnd_message.set_token('VALATTR1',v_value_attribute_type);
1323 fnd_message.set_token('VALATTR2',v_value_attribute_type_err);
1324 ELSE
1325 fnd_message.set_token('VALATTR1',v_value_attribute_type_err);
1326 fnd_message.set_token('VALATTR2',v_value_attribute_type);
1327 END IF;
1328
1329 app_exception.raise_exception;
1330
1331 WHEN OTHERS THEN
1332 fnd_message.set_name('SQLGL','GL_UNHANDLED_EXCEPTION');
1333 fnd_message.set_token('PROCEDURE','GL_CHART_OF_ACCOUNTS_API_PKG.validate_validation_qualifier');
1334 fnd_message.set_token('EVENT','OTHERS');
1335 app_exception.raise_exception;
1336
1337 END validate_validation_qualifier;
1338
1339
1340 --
1341 -- Procedure
1342 -- validate_value
1343 -- Purpose
1344 -- Do the validation for one particular value of a value set.
1345 -- (FND_ID_FLEX_VALUES table)
1346 -- History
1347 -- 10.09.2000 O Monnier Created.
1348 --
1349 PROCEDURE validate_value(v_flex_value_id IN NUMBER,
1350 v_flex_value_set_id IN NUMBER,
1351 v_flex_value IN VARCHAR2,
1352 v_compiled_value_attributes IN VARCHAR2)
1353 IS
1354 v_flex_value_set_name VARCHAR2(60);
1355 v_validation_type VARCHAR2(1);
1356 v_vset_security_enabled_flag VARCHAR2(1);
1357 v_format_type VARCHAR2(1);
1358 v_maximum_size NUMBER(3);
1359 v_number_precision NUMBER(2);
1360 v_alphanumeric_allowed_flag VARCHAR2(1);
1361 v_uppercase_only_flag VARCHAR2(1);
1362 v_numeric_mode_enabled_flag VARCHAR2(1);
1363 v_minimum_value VARCHAR2(150);
1364 v_maximum_value VARCHAR2(150);
1365 v_storage_value VARCHAR2(2000) := '';
1366 v_display_value VARCHAR2(2000) := '';
1367 v_count NUMBER;
1368 v_required_flag VARCHAR2(1);
1369 v_lookup_type VARCHAR2(30);
1370 v_value_attribute_type VARCHAR2(30);
1371 v_compiled_value_attribute_c VARCHAR2(30);
1372 v_compiled_value_attribute_l NUMBER;
1373 i NUMBER := 0;
1374 j NUMBER;
1375 v_compiled_value_attribute_s VARCHAR2(2000);
1376 v_count_value_attribute_types NUMBER;
1377
1378 -- Retrieve the value set information required for the validation from the value set id.
1379 CURSOR c_flex_value_set IS
1380 SELECT flex_value_set_name,
1381 validation_type,
1382 security_enabled_flag,
1383 format_type,
1384 maximum_size,
1385 number_precision,
1386 alphanumeric_allowed_flag,
1387 uppercase_only_flag,
1388 numeric_mode_enabled_flag,
1389 minimum_value,
1390 maximum_value
1391 FROM FND_FLEX_VALUE_SETS
1392 WHERE flex_value_set_id = v_flex_value_set_id;
1393
1394 -- Retrieve the value attribute type information required for the validation.
1395 CURSOR c_value_attribute_type IS
1396 SELECT vat.required_flag AS required_flag,
1397 vat.lookup_type AS lookup_type,
1398 vat.value_attribute_type AS value_attribute_type
1399 FROM FND_VALUE_ATTRIBUTE_TYPES vat,
1400 FND_FLEX_VALIDATION_QUALIFIERS fvq
1401 WHERE fvq.flex_value_set_id = v_flex_value_set_id
1402 AND vat.id_flex_code = fvq.id_flex_code
1403 AND vat.application_id = fvq.id_flex_application_id
1404 AND vat.segment_attribute_type = fvq.segment_attribute_type
1405 AND vat.value_attribute_type = fvq.value_attribute_type
1406 ORDER BY fvq.assignment_date, fvq.value_attribute_type;
1407
1408 -- Hold the cursor values
1409 v_cursor_attribute_type c_value_attribute_type%ROWTYPE;
1410
1411 -- Validate the value attribute.
1412 CURSOR c_check_value_attribute(v_character VARCHAR2,
1413 v_required_flag VARCHAR2,
1414 v_lookup_type VARCHAR2) IS
1415 SELECT v_character
1416 FROM dual
1417 WHERE v_character IN (SELECT lookup_code
1418 FROM FND_LOOKUPS
1419 WHERE lookup_type = v_lookup_type)
1420 OR (v_character = ' ' AND v_required_flag = 'N' );
1421
1422 BEGIN
1423 --
1424 -- The flexfield value must be formatted properly.
1425 --
1426 OPEN c_flex_value_set;
1427 FETCH c_flex_value_set
1428 INTO v_flex_value_set_name,
1429 v_validation_type,
1430 v_vset_security_enabled_flag,
1431 v_format_type,
1432 v_maximum_size,
1433 v_number_precision,
1434 v_alphanumeric_allowed_flag,
1435 v_uppercase_only_flag,
1436 v_numeric_mode_enabled_flag,
1437 v_minimum_value,
1438 v_maximum_value;
1439
1440 IF c_flex_value_set%FOUND THEN
1441 CLOSE c_flex_value_set;
1442 ELSE
1443 CLOSE c_flex_value_set;
1444 RAISE value_set_must_exist;
1445 END IF;
1446
1447 --
1448 -- The value should be validated.
1449 --
1450 IF (NOT FND_FLEX_VAL_UTIL.IS_VALUE_VALID
1451 (p_value => v_flex_value,
1452 p_is_displayed => TRUE,
1453 p_vset_name => v_flex_value_set_name,
1454 p_vset_format => v_format_type,
1455 p_max_length => v_maximum_size,
1456 p_precision => v_number_precision,
1457 p_alpha_allowed => v_alphanumeric_allowed_flag,
1458 p_uppercase_only => v_uppercase_only_flag,
1459 p_zero_fill => v_numeric_mode_enabled_flag,
1460 p_min_value => v_minimum_value,
1461 p_max_value => v_maximum_value,
1462 x_storage_value => v_storage_value,
1463 x_display_value => v_display_value)) THEN
1464 RAISE invalid_value;
1465 END IF;
1466
1467 IF (v_flex_value <> v_storage_value) THEN
1468 UPDATE FND_FLEX_VALUES
1469 SET flex_value = v_storage_value
1470 WHERE flex_value_id = v_flex_value_id;
1471 END IF;
1472
1473 --
1474 -- The compiled value attributes must be formatted properly:
1475 --
1476 -- Added by ABHJOSHI on 03/31/05
1477 -- Retrieve the count of the value attribute types required for the validation.
1478
1479 SELECT COUNT(*) INTO v_count_value_attribute_types
1480 FROM FND_VALUE_ATTRIBUTE_TYPES vat,
1481 FND_FLEX_VALIDATION_QUALIFIERS fvq
1482 WHERE fvq.flex_value_set_id = v_flex_value_set_id
1483 AND vat.id_flex_code = fvq.id_flex_code
1484 AND vat.application_id = fvq.id_flex_application_id
1485 AND vat.segment_attribute_type = fvq.segment_attribute_type
1486 AND vat.value_attribute_type = fvq.value_attribute_type;
1487
1488 --
1489 -- 1. Each individual attribute value must be in the lookup table
1490 -- or can be ' ' if it is not a required attribute.
1491 -- Each individual attribute value can also be null (Added on 03/05/01).
1492 --
1493 -- Added by ABHJOSHI on 03/31/05
1494 -- Handling of the multi-character individual attribute value.
1495 --
1496
1497 v_compiled_value_attribute_s
1498 := v_compiled_value_attributes||FND_GLOBAL.newline;
1499
1500 FOR v_cursor_attribute_type IN c_value_attribute_type LOOP
1501
1502 -- Count the number of attributes
1503 i := i + 1;
1504
1505 -- Retrieve the attribute type information for the attribute
1506 v_required_flag := v_cursor_attribute_type.required_flag;
1507 v_lookup_type := v_cursor_attribute_type.lookup_type;
1508 v_value_attribute_type := v_cursor_attribute_type.value_attribute_type;
1509
1510 -- Retrieve the individual attribute value from the compiled attribute value
1511 -- Each individual attribute value can be null.
1512 -- Consequtive <Enter> character (ASCII value 10) means a NULL value.
1513 -- Added by ABHJOSHI on 03/31/05
1514 --
1515 IF (substrb(v_compiled_value_attribute_s,1,1) = FND_GLOBAL.newline) THEN
1516 v_compiled_value_attribute_c := NULL;
1517 ELSE
1518 v_compiled_value_attribute_c
1519 := substrb(v_compiled_value_attribute_s
1520 ,1
1521 ,instrb(v_compiled_value_attribute_s,FND_GLOBAL.newline,1)-1);
1522 END IF;
1523
1524 -- Each individual attribute value can be null.
1525 IF (v_compiled_value_attribute_c IS NOT NULL) THEN
1526
1527 -- Check if the individual attribute value is valid
1528 OPEN c_check_value_attribute(v_compiled_value_attribute_c,
1529 v_required_flag,
1530 v_lookup_type);
1531 FETCH c_check_value_attribute
1532 INTO v_compiled_value_attribute_c;
1533
1534 IF c_check_value_attribute%FOUND THEN
1535 CLOSE c_check_value_attribute;
1536 ELSE
1537 CLOSE c_check_value_attribute;
1538 RAISE invalid_compiled_value_attr1;
1539 END IF;
1540
1541 END IF;
1542
1543 -- Individual attribute value should not contain
1544 -- <Enter> character (ASCII value 10)
1545 -- Otherwise will be treated as separate values
1546 v_compiled_value_attribute_s
1547 := substrb(v_compiled_value_attribute_s
1548 ,instrb(v_compiled_value_attribute_s,FND_GLOBAL.newline,1)+1);
1549
1550 END LOOP;
1551
1552 --
1553 -- 2. Each individual attribute must be separated from other
1554 -- attributes with an <Enter> character (ASCII value 10).
1555 -- Each individual attribute value can also be null (Added on 03/05/01).
1556 --
1557 -- Added by ABHJOSHI on 03/31/05
1558 -- This check has been modified alongwith the handling of the
1559 -- multi-character individual attribute value (code section 1),
1560 -- hence this code section has been removed and exception
1561 -- invalid_compiled_value_attr2 is no longer used.
1562
1563 --
1564 -- 3. Check that there is not more attribute values
1565 -- than needed (Modified by ABHJOSHI on 03/31/05).
1566 --
1567 IF (v_compiled_value_attribute_s IS NOT NULL) OR
1568 (v_count_value_attribute_types > i) THEN
1569
1570 RAISE invalid_compiled_value_attr3;
1571 END IF;
1572
1573 --
1574 -- The value has been validated successfully.
1575 --
1576 return;
1577
1578 EXCEPTION
1579 WHEN value_set_must_exist THEN
1580 fnd_message.set_name('SQLGL','GL_API_COA_NO_VSET_FOUND');
1581 fnd_message.set_token('VSET',v_flex_value_set_name);
1582 app_exception.raise_exception;
1583
1584 WHEN invalid_value THEN
1585 fnd_message.set_name('SQLGL','GL_API_COA_INVALID_VAL');
1586 app_exception.raise_exception;
1587
1588 WHEN invalid_compiled_value_attr1 THEN
1589 fnd_message.set_name('SQLGL','GL_API_COA_INVALID_COMP_ATTR1');
1590 fnd_message.set_token('NUM',i);
1591 fnd_message.set_token('VALATTRTYPE',v_value_attribute_type);
1592 app_exception.raise_exception;
1593
1594 WHEN invalid_compiled_value_attr3 THEN
1595 fnd_message.set_name('SQLGL','GL_API_COA_INVALID_COMP_ATTR3');
1596 app_exception.raise_exception;
1597
1598 WHEN OTHERS THEN
1599 fnd_message.set_name('SQLGL','GL_UNHANDLED_EXCEPTION');
1600 fnd_message.set_token('PROCEDURE','GL_CHART_OF_ACCOUNTS_API_PKG.validate_value');
1601 fnd_message.set_token('EVENT','OTHERS');
1602 app_exception.raise_exception;
1603
1604 END validate_value;
1605
1606
1607 --
1608 -- Procedure
1609 -- validate_value_tl
1610 -- Purpose
1611 -- Do the validation for one particular translated value of a value set.
1612 -- (FND_ID_FLEX_VALUES_TL table)
1613 -- History
1614 -- 10.09.2000 O Monnier Created.
1615 --
1616 PROCEDURE validate_value_tl(v_flex_value_id IN NUMBER,
1617 v_language IN VARCHAR2,
1618 v_flex_value_meaning IN VARCHAR2,
1619 v_userenvlang IN VARCHAR2)
1620 IS
1621 v_flex_value_set_id NUMBER(15);
1622 v_flex_value_set_name VARCHAR2(60);
1623 v_validation_type VARCHAR2(1);
1624 v_vset_security_enabled_flag VARCHAR2(1);
1625 v_format_type VARCHAR2(1);
1626 v_maximum_size NUMBER(3);
1627 v_number_precision NUMBER(2);
1628 v_alphanumeric_allowed_flag VARCHAR2(1);
1629 v_uppercase_only_flag VARCHAR2(1);
1630 v_numeric_mode_enabled_flag VARCHAR2(1);
1631 v_minimum_value VARCHAR2(150);
1632 v_maximum_value VARCHAR2(150);
1633 v_storage_value VARCHAR2(2000);
1634 v_display_value VARCHAR2(2000);
1635 v_parent_flex_value_low VARCHAR2(60);
1636 v_count NUMBER;
1637
1638 -- Retrieve the value set information required for the validation from the value set id.
1639 CURSOR c_flex_value_set IS
1640 SELECT vs.flex_value_set_id,
1641 vs.flex_value_set_name,
1642 vs.validation_type,
1643 vs.security_enabled_flag,
1644 vs.format_type,
1645 vs.maximum_size,
1646 vs.number_precision,
1647 vs.alphanumeric_allowed_flag,
1648 vs.uppercase_only_flag,
1649 vs.numeric_mode_enabled_flag,
1650 vs.minimum_value,
1651 vs.maximum_value
1652 FROM FND_FLEX_VALUE_SETS vs,
1653 FND_FLEX_VALUES v
1654 WHERE v.flex_value_id = v_flex_value_id
1655 AND v.flex_value_set_id = vs.flex_value_set_id;
1656
1657 BEGIN
1658 --
1659 -- Retrieve the environment language.
1660 --
1661 IF (v_userenvlang = v_language) THEN
1662 --
1663 -- Retrieve the value set information
1664 --
1665 OPEN c_flex_value_set;
1666 FETCH c_flex_value_set
1667 INTO v_flex_value_set_id,
1668 v_flex_value_set_name,
1669 v_validation_type,
1670 v_vset_security_enabled_flag,
1671 v_format_type,
1672 v_maximum_size,
1673 v_number_precision,
1674 v_alphanumeric_allowed_flag,
1675 v_uppercase_only_flag,
1676 v_numeric_mode_enabled_flag,
1677 v_minimum_value,
1678 v_maximum_value;
1679
1680 IF c_flex_value_set%FOUND THEN
1681 CLOSE c_flex_value_set;
1682 ELSE
1683 CLOSE c_flex_value_set;
1684 RAISE value_set_must_exist;
1685 END IF;
1686
1687 --
1688 -- Check that flexfield value meaning is unique for the value set if the language
1689 -- is userenv('LANG').
1690 --
1691 SELECT parent_flex_value_low
1692 INTO v_parent_flex_value_low
1693 FROM FND_FLEX_VALUES
1694 WHERE flex_value_id = v_flex_value_id;
1695
1696 SELECT count(*)
1697 INTO v_count
1698 FROM FND_FLEX_VALUES_VL
1699 WHERE flex_value_set_id = v_flex_value_set_id
1700 AND flex_value_meaning = v_flex_value_meaning
1701 AND ((v_parent_flex_value_low IS null) OR
1702 (parent_flex_value_low =
1703 v_parent_flex_value_low));
1704
1705 IF (v_count > 1) THEN
1706 RAISE value_meaning_not_unique;
1707 END IF;
1708
1709 --
1710 -- The flexfield value meaning must be formatted properly for the value set
1711 -- if the language is userenv('LANG').
1712 --
1713 IF (NOT FND_FLEX_VAL_UTIL.IS_VALUE_VALID
1714 (p_value => v_flex_value_meaning,
1715 p_is_displayed => TRUE,
1716 p_vset_name => v_flex_value_set_name,
1717 p_vset_format => v_format_type,
1718 p_max_length => v_maximum_size,
1719 p_precision => v_number_precision,
1720 p_alpha_allowed => v_alphanumeric_allowed_flag,
1721 p_uppercase_only => v_uppercase_only_flag,
1722 p_zero_fill => v_numeric_mode_enabled_flag,
1723 p_min_value => v_minimum_value,
1724 p_max_value => v_maximum_value,
1725 x_storage_value => v_storage_value,
1726 x_display_value => v_display_value)) THEN
1727 RAISE invalid_value_meaning;
1728 END IF;
1729
1730 IF (v_flex_value_meaning <> v_storage_value) THEN
1731 UPDATE FND_FLEX_VALUES_TL
1732 SET flex_value_meaning = v_storage_value
1733 WHERE flex_value_id = v_flex_value_id
1734 AND language = v_language;
1735 END IF;
1736
1737 END IF;
1738
1739 --
1740 -- The value tl has been validated successfully.
1741 --
1742 return;
1743
1744 EXCEPTION
1745 WHEN value_meaning_not_unique THEN
1746 fnd_message.set_name('SQLGL','GL_API_COA_VALUE_TL_NOT_UNIQ');
1747 app_exception.raise_exception;
1748
1749 WHEN value_set_must_exist THEN
1750 fnd_message.set_name('SQLGL','GL_API_COA_NO_VSET_FOUND');
1751 fnd_message.set_token('VSET',v_flex_value_set_name);
1752 app_exception.raise_exception;
1753
1754 WHEN invalid_value_meaning THEN
1755 fnd_message.set_name('SQLGL','GL_API_COA_INVALID_VAL_TL');
1756 app_exception.raise_exception;
1757
1758 WHEN OTHERS THEN
1759 fnd_message.set_name('SQLGL','GL_UNHANDLED_EXCEPTION');
1760 fnd_message.set_token('PROCEDURE','GL_CHART_OF_ACCOUNTS_API_PKG.validate_value_tl');
1761 fnd_message.set_token('EVENT','OTHERS');
1762 app_exception.raise_exception;
1763
1764 END validate_value_tl;
1765
1766
1767 --
1768 -- Procedure
1769 -- validate_final_structure
1770 -- Purpose
1771 -- Do the final validation for one particular structure:
1772 -- Check that the structure has all the global and required qualifiers.
1773 -- Check the range code Low and High.
1774 -- Check that the total of value set maximum sizes + the number of segment separators
1775 -- does not add up to more than 240
1776 -- **GL Accounting Flexfield specific validation**
1777 -- The accounting flexfield requires consecutive segment numbers beginning with 1.
1778 -- There must be two different segments for the balancing segment and the
1779 -- natural account segment.
1780 --
1781 -- History
1782 -- 10.09.2000 O Monnier Created.
1783 --
1784 PROCEDURE validate_final_structure(v_application_id IN NUMBER,
1785 v_id_flex_code IN VARCHAR2,
1786 v_id_flex_num IN NUMBER)
1787 IS
1788 v_freeze_flex_definition_flag VARCHAR2(1);
1789 v_segment_prompt VARCHAR2(50);
1790 v_segment_num NUMBER(4);
1791 v_range_code VARCHAR2(1);
1792 v_range_code_min_high_seg_num NUMBER(4);
1793 v_range_code_min_low_seg_num NUMBER(4);
1794 v_range_code_max_high_seg_num NUMBER(4);
1795 v_range_code_max_low_seg_num NUMBER(4);
1796 v_max NUMBER(3);
1797 v_count NUMBER(3);
1798
1799 -- Retrieve the flexfield structure information required.
1800 CURSOR c_id_flex_structure IS
1801 SELECT freeze_flex_definition_flag
1802 FROM FND_ID_FLEX_STRUCTURES
1803 WHERE application_id = v_application_id
1804 AND id_flex_code = v_id_flex_code
1805 AND id_flex_num = v_id_flex_num;
1806
1807 -- Check if any segment qualifiers is missing.
1808 CURSOR c_check_qualifiers
1809 IS
1810 SELECT sat.segment_prompt,
1811 fs.segment_num
1812 FROM FND_ID_FLEX_SEGMENTS fs,
1813 FND_SEGMENT_ATTRIBUTE_TYPES sat
1814 WHERE fs.application_id = v_application_id
1815 AND fs.id_flex_code = v_id_flex_code
1816 AND fs.id_flex_num = v_id_flex_num
1817 AND sat.application_id = v_application_id
1818 AND sat.id_flex_code = v_id_flex_code
1819 AND NOT EXISTS (SELECT 'Exist'
1820 FROM FND_SEGMENT_ATTRIBUTE_VALUES sav
1821 WHERE sav.application_id = v_application_id
1822 AND sav.id_flex_code = v_id_flex_code
1823 AND sav.id_flex_num = v_id_flex_num
1824 AND sav.application_column_name = fs.application_column_name
1825 AND sav.segment_attribute_type = sat.segment_attribute_type);
1826
1827 -- Check if all required segment qualifiers are defined.
1828 CURSOR c_check_required_qualifiers
1829 IS
1830 SELECT sat.segment_prompt
1831 FROM FND_ID_FLEX_STRUCTURES ft,
1832 FND_SEGMENT_ATTRIBUTE_TYPES sat
1833 WHERE ft.application_id = v_application_id
1834 AND ft.id_flex_code = v_id_flex_code
1835 AND ft.id_flex_num = v_id_flex_num
1836 AND sat.application_id = v_application_id
1837 AND sat.id_flex_code = v_id_flex_code
1838 AND sat.required_flag = 'Y'
1839 AND NOT EXISTS (SELECT 'Exist'
1840 FROM FND_SEGMENT_ATTRIBUTE_VALUES sav,
1841 FND_ID_FLEX_SEGMENTS fs
1842 WHERE sav.application_id = v_application_id
1843 AND sav.id_flex_code = v_id_flex_code
1844 AND sav.id_flex_num = v_id_flex_num
1845 AND sav.segment_attribute_type = sat.segment_attribute_type
1846 AND sav.attribute_value = 'Y'
1847 AND fs.application_id = v_application_id
1848 AND fs.id_flex_code = v_id_flex_code
1849 AND fs.id_flex_num = v_id_flex_num
1850 AND fs.application_column_name = sav.application_column_name
1851 AND fs.enabled_flag = 'Y');
1852
1853 -- Retrieve the range code information
1854 CURSOR c_check_range_code
1855 IS
1856 SELECT fs.segment_num, fs.range_code
1857 FROM FND_ID_FLEX_SEGMENTS fs
1858 WHERE fs.application_id = v_application_id
1859 AND fs.id_flex_code = v_id_flex_code
1860 AND fs.id_flex_num = v_id_flex_num
1861 AND fs.range_code IN ('L','H')
1862 AND fs.enabled_flag = 'Y';
1863
1864 -- Variable to loop on c_check_range_code
1865 v_check_range_code c_check_range_code%ROWTYPE;
1866
1867 -- Check if the balancing segment and the natural account segment are the same.
1868 CURSOR c_check_balancing_and_account
1869 IS
1870 SELECT fs.segment_num
1871 FROM FND_ID_FLEX_SEGMENTS fs
1872 WHERE fs.application_id = v_application_id
1873 AND fs.id_flex_code = v_id_flex_code
1874 AND fs.id_flex_num = v_id_flex_num
1875 AND fs.application_column_name IN (SELECT sav1.application_column_name
1876 FROM FND_SEGMENT_ATTRIBUTE_VALUES sav1,
1877 FND_SEGMENT_ATTRIBUTE_VALUES sav2
1878 WHERE sav1.application_id = v_application_id
1879 AND sav1.id_flex_code = v_id_flex_code
1880 AND sav1.id_flex_num = v_id_flex_num
1881 AND sav1.segment_attribute_type = 'GL_ACCOUNT'
1882 AND sav1.attribute_value = 'Y'
1883 AND sav1.application_column_name = sav2.application_column_name
1884 AND sav2.application_id = v_application_id
1885 AND sav2.id_flex_code = v_id_flex_code
1886 AND sav2.id_flex_num = v_id_flex_num
1887 AND sav2.segment_attribute_type = 'GL_BALANCING'
1888 AND sav2.attribute_value = 'Y');
1889
1890 BEGIN
1891 --
1892 -- Retrieve the flexfield structure information.
1893 --
1894 OPEN c_id_flex_structure;
1895 FETCH c_id_flex_structure
1896 INTO v_freeze_flex_definition_flag;
1897
1898 --
1899 -- Check that the flexfield structure exists
1900 --
1901 IF c_id_flex_structure%FOUND THEN
1902 CLOSE c_id_flex_structure;
1903 ELSE
1904 CLOSE c_id_flex_structure;
1905 return;
1906 END IF;
1907
1908 --
1909 -- Check if the flexfield is supported by the API
1910 --
1911 IF (NOT is_flexfield_supported(v_application_id => v_application_id,
1912 v_id_flex_code => v_id_flex_code))
1913 THEN
1914 RAISE flexfield_not_supported;
1915 END IF;
1916
1917 --
1918 -- The total of value set maximum sizes + the number of segment separators
1919 -- should not add up to more than 240.
1920 --
1921 SELECT NVL((sum(fv.maximum_size) + count(fs.application_column_name) - 1),0)
1922 INTO v_count
1923 FROM FND_ID_FLEX_SEGMENTS fs,
1924 FND_FLEX_VALUE_SETS fv
1925 WHERE fs.application_id = v_application_id
1926 AND fs.id_flex_code = v_id_flex_code
1927 AND fs.id_flex_num = v_id_flex_num
1928 AND fv.flex_value_set_id (+) = fs.flex_value_set_id;
1929
1930 IF (v_count > 240) THEN
1931 RAISE sum_maximum_size_too_large;
1932 END IF;
1933
1934 --
1935 -- Check if there is a row for each segment for each qualifier.
1936 --
1937 OPEN c_check_qualifiers;
1938 FETCH c_check_qualifiers
1939 INTO v_segment_prompt, v_segment_num;
1940
1941 IF c_check_qualifiers%NOTFOUND THEN
1942 CLOSE c_check_qualifiers;
1943 ELSE
1944 CLOSE c_check_qualifiers;
1945 RAISE attribute_must_exist;
1946 END IF;
1947
1948 --
1949 -- **GL Accounting Flexfield specific validation**
1950 --
1951 IF (v_application_id = 101 AND v_id_flex_code = 'GL#') THEN
1952
1953 --
1954 -- **GL Accounting Flexfield specific validation**
1955 -- The accounting flexfield requires consecutive segment numbers beginning with 1.
1956 --
1957 SELECT max(segment_num),count(segment_num)
1958 INTO v_max,v_count
1959 FROM FND_ID_FLEX_SEGMENTS
1960 WHERE application_id = v_application_id
1961 AND id_flex_code = v_id_flex_code
1962 AND id_flex_num = v_id_flex_num;
1963
1964 IF (v_max <> v_count) THEN
1965 RAISE gl_segment_not_consecutive;
1966 END IF;
1967
1968 --
1969 -- **GL Accounting Flexfield specific validation**
1970 -- There must be two different segments for the balancing segment and the
1971 -- natural account segment.
1972 --
1973 OPEN c_check_balancing_and_account;
1974 FETCH c_check_balancing_and_account
1975 INTO v_segment_num;
1976
1977 IF c_check_balancing_and_account%NOTFOUND THEN
1978 CLOSE c_check_balancing_and_account;
1979 ELSE
1980 CLOSE c_check_balancing_and_account;
1981 RAISE gl_same_bal_acct_segment;
1982 END IF;
1983
1984 END IF;
1985
1986 --
1987 -- ** Frozen structure validation **
1988 --
1989 IF (v_freeze_flex_definition_flag = 'Y') THEN
1990
1991 --
1992 -- ** Frozen structure validation **
1993 -- Check for the required qualifiers
1994 -- We do not consider disabled segments.
1995 --
1996 OPEN c_check_required_qualifiers;
1997 FETCH c_check_required_qualifiers
1998 INTO v_segment_prompt;
1999
2000 IF c_check_required_qualifiers%NOTFOUND THEN
2001 CLOSE c_check_required_qualifiers;
2002 ELSE
2003 CLOSE c_check_required_qualifiers;
2004 RAISE required_attr_must_exist;
2005 END IF;
2006
2007 --
2008 -- ** Frozen structure validation **
2009 -- If you choose Low for one segment, you must also choose High for another segment in
2010 -- that structure (and vice versa). Furthermore, segments with a range type of Low must
2011 -- appear before segments with a range type of High.
2012 --
2013 FOR v_check_range_code IN c_check_range_code LOOP
2014 v_segment_num := v_check_range_code.segment_num;
2015 v_range_code := v_check_range_code.range_code;
2016
2017 IF (v_range_code = 'L') THEN
2018 SELECT NVL(min(segment_num),-1000)
2019 INTO v_range_code_min_high_seg_num
2020 FROM FND_ID_FLEX_SEGMENTS fs
2021 WHERE fs.application_id = v_application_id
2022 AND fs.id_flex_code = v_id_flex_code
2023 AND fs.id_flex_num = v_id_flex_num
2024 AND fs.segment_num > v_segment_num
2025 AND fs.enabled_flag = 'Y'
2026 AND fs.range_code = 'H';
2027
2028 SELECT NVL(min(segment_num),1000)
2029 INTO v_range_code_min_low_seg_num
2030 FROM FND_ID_FLEX_SEGMENTS fs
2031 WHERE fs.application_id = v_application_id
2032 AND fs.id_flex_code = v_id_flex_code
2033 AND fs.id_flex_num = v_id_flex_num
2034 AND fs.segment_num > v_segment_num
2035 AND fs.enabled_flag = 'Y'
2036 AND fs.range_code = 'L';
2037
2038 IF (v_range_code_min_high_seg_num < v_segment_num
2039 OR v_range_code_min_low_seg_num < v_range_code_min_high_seg_num) THEN
2040 RAISE invalid_low_high_range_code;
2041 END IF;
2042
2043 ELSE IF (v_range_code = 'H') THEN
2044 SELECT NVL(max(segment_num),1000)
2045 INTO v_range_code_max_low_seg_num
2046 FROM FND_ID_FLEX_SEGMENTS fs
2047 WHERE fs.application_id = v_application_id
2048 AND fs.id_flex_code = v_id_flex_code
2049 AND fs.id_flex_num = v_id_flex_num
2050 AND fs.segment_num < v_segment_num
2051 AND fs.enabled_flag = 'Y'
2052 AND fs.range_code = 'L';
2053
2054 SELECT NVL(max(segment_num),-1000)
2055 INTO v_range_code_max_high_seg_num
2056 FROM FND_ID_FLEX_SEGMENTS fs
2057 WHERE fs.application_id = v_application_id
2058 AND fs.id_flex_code = v_id_flex_code
2059 AND fs.id_flex_num = v_id_flex_num
2060 AND fs.segment_num < v_segment_num
2061 AND fs.enabled_flag = 'Y'
2062 AND fs.range_code = 'H';
2063
2064 IF (v_range_code_max_low_seg_num > v_segment_num
2065 OR v_range_code_max_high_seg_num > v_range_code_max_low_seg_num) THEN
2066 RAISE invalid_low_high_range_code;
2067 END IF;
2068
2069 END IF;
2070 END IF;
2071 END LOOP;
2072 END IF;
2073
2074 --
2075 -- After inserting or updating a new segment for the flexfield,
2076 -- we also need to insert into the FND_FLEX_VALIDATION_QUALIFIERS table,
2077 -- if this has not already been done.
2078 --
2079 INSERT INTO fnd_flex_validation_qualifiers( flex_value_set_id,
2080 id_flex_application_id,
2081 id_flex_code,
2082 segment_attribute_type,
2083 value_attribute_type,
2084 assignment_date )
2085 SELECT seg.flex_value_set_id,
2086 v_application_id,
2087 v_id_flex_code,
2088 sav.segment_attribute_type,
2089 vat.value_attribute_type,
2090 sysdate
2091 FROM fnd_segment_attribute_values sav,
2092 fnd_value_attribute_types vat,
2093 fnd_id_flex_segments seg
2094 WHERE seg.application_id = v_application_id
2095 AND seg.id_flex_code = v_id_flex_code
2096 AND seg.id_flex_num = v_id_flex_num
2097 AND seg.flex_value_set_id IS NOT NULL
2098 AND seg.enabled_flag = 'Y'
2099 AND sav.application_id = v_application_id
2100 AND sav.id_flex_code = v_id_flex_code
2101 AND sav.id_flex_num = v_id_flex_num
2102 AND sav.application_column_name = seg.application_column_name
2103 AND sav.attribute_value = 'Y'
2104 AND sav.application_id = vat.application_id
2105 AND sav.id_flex_code = vat.id_flex_code
2106 AND sav.segment_attribute_type = vat.segment_attribute_type
2107 AND NOT EXISTS
2108 (SELECT NULL
2109 FROM fnd_flex_validation_qualifiers q
2110 WHERE q.flex_value_set_id = seg.flex_value_set_id
2111 AND q.id_flex_application_id = v_application_id
2112 AND q.id_flex_code = v_id_flex_code
2113 AND q.segment_attribute_type = sav.segment_attribute_type
2114 AND q.value_attribute_type = vat.value_attribute_type);
2115
2116 --
2117 -- The final validation for the structure is successful.
2118 --
2119 return;
2120
2121 EXCEPTION
2122 WHEN flexfield_not_supported THEN
2123 fnd_message.set_name('SQLGL','GL_API_COA_FLEX_NOT_SUPPORTED');
2124 app_exception.raise_exception;
2125
2126 WHEN attribute_must_exist THEN
2127 fnd_message.set_name('SQLGL','GL_API_COA_QUAL_MUST_EXIST');
2128 fnd_message.set_token('QUAL',v_segment_prompt);
2129 fnd_message.set_token('SEG',v_segment_num);
2130 app_exception.raise_exception;
2131
2132 WHEN sum_maximum_size_too_large THEN
2133 fnd_message.set_name('SQLGL','GL_API_COA_SUM_MAXSIZE_TOO_LAR');
2134 fnd_message.set_token('SUM',v_count);
2135 app_exception.raise_exception;
2136
2137 WHEN invalid_low_high_range_code THEN
2138 fnd_message.set_name('SQLGL','GL_API_COA_INVALID_RANGE_CODE');
2139 app_exception.raise_exception;
2140
2141 WHEN gl_segment_not_consecutive THEN
2142 fnd_message.set_name('SQLGL','GL_API_COA_SEG_NOT_CONS');
2143 app_exception.raise_exception;
2144
2145 WHEN gl_same_bal_acct_segment THEN
2146 fnd_message.set_name('SQLGL','GL_API_COA_SAME_BAL_ACCT_SEG');
2147 app_exception.raise_exception;
2148
2149 WHEN required_attr_must_exist THEN
2150 fnd_message.set_name('SQLGL','GL_API_COA_UNIQUE_QUAL_ERR');
2151 fnd_message.set_token('QUAL',v_segment_prompt);
2152 app_exception.raise_exception;
2153
2154 WHEN OTHERS THEN
2155 fnd_message.set_name('SQLGL','GL_UNHANDLED_EXCEPTION');
2156 fnd_message.set_token('PROCEDURE','GL_CHART_OF_ACCOUNTS_API_PKG.validate_final_structure');
2157 fnd_message.set_token('EVENT','OTHERS');
2158 app_exception.raise_exception;
2159
2160 END validate_final_structure;
2161
2162
2163 --
2164 -- Procedure
2165 -- compile_key_flexfield
2166 -- Purpose
2167 -- Compile the key flexfield.
2168 -- History
2169 -- 10.09.2000 O Monnier Created.
2170 --
2171 FUNCTION compile_key_flexfield(v_application_id IN NUMBER,
2172 v_id_flex_code IN VARCHAR2,
2173 v_id_flex_structure_code IN VARCHAR2) RETURN VARCHAR2
2174 IS
2175 v_application_short_name VARCHAR2(50);
2176 v_id_flex_num NUMBER(15);
2177 v_structure_view_name VARCHAR2(30);
2178 v_count NUMBER;
2179 v_set_flag VARCHAR(1) := 'N';
2180 ret VARCHAR2(500) := '';
2181 request_id NUMBER := -1;
2182 request_id2 NUMBER := -1;
2183 request_id3 NUMBER := -1;
2184
2185 -- Retrieve the application short name.
2186 CURSOR c_application_short_name IS
2187 SELECT application_short_name
2188 FROM FND_APPLICATION
2189 WHERE application_id = v_application_id;
2190
2191 -- Retrieve the flexfield structure information required.
2192 CURSOR c_id_flex_structure IS
2193 SELECT id_flex_num,
2194 structure_view_name
2195 FROM FND_ID_FLEX_STRUCTURES
2196 WHERE application_id = v_application_id
2197 AND id_flex_code = v_id_flex_code
2198 AND id_flex_structure_code = v_id_flex_structure_code;
2199
2200 BEGIN
2201 --
2202 -- Retrieve the flexfield structure information.
2203 --
2204 OPEN c_id_flex_structure;
2205 FETCH c_id_flex_structure
2206 INTO v_id_flex_num,
2207 v_structure_view_name;
2208
2209 --
2210 -- Check that the flexfield structure exists
2211 --
2212 IF c_id_flex_structure%FOUND THEN
2213 CLOSE c_id_flex_structure;
2214 ELSE
2215 CLOSE c_id_flex_structure;
2216 RETURN ('The structure does not exist');
2217 END IF;
2218
2219 --
2220 -- Retrieve the application_short_name.
2221 --
2222 OPEN c_application_short_name;
2223 FETCH c_application_short_name
2224 INTO v_application_short_name;
2225
2226 --
2227 -- Check that the application_short_name exists.
2228 --
2229 IF c_application_short_name%FOUND THEN
2230 CLOSE c_application_short_name;
2231 ELSE
2232 CLOSE c_application_short_name;
2233 RAISE invalid_application_id;
2234 END IF;
2235
2236 --
2237 -- Compile the Flexfield
2238 --
2239 request_id := FND_REQUEST.SUBMIT_REQUEST(
2240 'FND',
2241 'FDFCMPK',
2242 '',
2243 '',
2244 FALSE,
2245 'K',
2246 v_application_short_name,
2247 v_id_flex_code,
2248 v_id_flex_num);
2249
2250 IF (request_id = 0) THEN
2251 RAISE request_failed;
2252 END IF;
2253
2254 ret := ret||'**Request id 1**'||to_char(request_id);
2255
2256 --
2257 -- Generate the view only if the application owning the
2258 -- flexfield is installed at the site.
2259 --
2260 SELECT count(*)
2261 INTO v_count
2262 FROM fnd_product_installations
2263 WHERE application_id = v_application_id;
2264
2265 IF (v_count = 0) THEN
2266 return (ret);
2267 END IF;
2268
2269 /* NOT NEEDED FOR THE CHART OF ACCOUNTS FLEXFIELD
2270 ONLY NEEDED FOR OTHER FLEXFIELDS
2271 --
2272 -- Check to see if this flexfield uses set numbers
2273 -- (hardcoded), if so set the set_flag to 'Y'.
2274 --
2275 IF (v_id_flex_code IN ('MSTK', 'MTLL','MICG', 'MDSP')) THEN
2276 v_set_flag := 'Y';
2277 END IF;*/
2278
2279 --
2280 -- Create the structure view only if a structure
2281 -- view name has been given.
2282 --
2283 IF (v_structure_view_name IS NOT NULL) THEN
2284 request_id2 := FND_REQUEST.SUBMIT_REQUEST(
2285 'FND',
2286 'FDFVGN',
2287 '',
2288 '',
2289 FALSE,
2290 '1',
2291 to_char(v_application_id),
2292 v_id_flex_code,
2293 to_char(v_id_flex_num),
2294 v_structure_view_name,
2295 v_set_flag,
2296 chr(0),
2297 '', '', '',
2298 '', '', '', '', '', '', '', '', '', '',
2299 '', '', '', '', '', '', '', '', '', '',
2300 '', '', '', '', '', '', '', '', '', '',
2301 '', '', '', '', '', '', '', '', '', '',
2302 '', '', '', '', '', '', '', '', '', '',
2303 '', '', '', '', '', '', '', '', '', '',
2304 '', '', '', '', '', '', '', '', '', '',
2305 '', '', '', '', '', '', '', '', '', '',
2306 '', '', '', '', '', '', '', '', '', '');
2307
2308 IF (request_id2 = 0) THEN
2309 RAISE request_failed;
2310 END IF;
2311
2312 ret := ret||'**Request id 2**'||to_char(request_id2);
2313
2314 END IF;
2315
2316 --
2317 -- Submit request for code combination view.
2318 --
2319 request_id3 := FND_REQUEST.SUBMIT_REQUEST(
2320 'FND',
2321 'FDFVGN',
2322 '',
2323 '',
2324 FALSE,
2325 '2',
2326 to_char(v_application_id),
2327 v_id_flex_code,
2328 '',
2329 v_set_flag,
2330 '', '', '', '', '',
2331 '', '', '', '', '', '', '', '', '', '',
2332 '', '', '', '', '', '', '', '', '', '',
2333 '', '', '', '', '', '', '', '', '', '',
2334 '', '', '', '', '', '', '', '', '', '',
2335 '', '', '', '', '', '', '', '', '', '',
2336 '', '', '', '', '', '', '', '', '', '',
2337 '', '', '', '', '', '', '', '', '', '',
2338 '', '', '', '', '', '', '', '', '', '',
2339 '', '', '', '', '', '', '', '', '', '');
2340
2341 IF (request_id3 = 0) THEN
2342 RAISE request_failed;
2343 END IF;
2344
2345 ret := ret||'**Request id 3**'||to_char(request_id3);
2346
2347 --
2348 -- The compilation of the flexfield is successful.
2349 --
2350 RETURN(ret);
2351
2352 EXCEPTION
2353 WHEN invalid_application_id THEN
2354 fnd_message.set_name('SQLGL','GL_UNHANDLED_EXCEPTION');
2355 fnd_message.set_token('PROCEDURE','GL_CHART_OF_ACCOUNTS_API_PKG.compile_key_flexfield');
2356 fnd_message.set_token('EVENT','INVALID_APPLICATION_ID');
2357 app_exception.raise_exception;
2358
2359 WHEN request_failed THEN
2360 fnd_message.set_name('SQLGL','GL_API_COA_FLEX_COMPILE_ERR');
2361 fnd_message.set_token('STRUCTURECODE',v_id_flex_structure_code);
2362 app_exception.raise_exception;
2363
2364 WHEN OTHERS THEN
2365 fnd_message.set_name('SQLGL','GL_UNHANDLED_EXCEPTION');
2366 fnd_message.set_token('PROCEDURE','GL_CHART_OF_ACCOUNTS_API_PKG.compile_key_flexfield');
2367 fnd_message.set_token('EVENT','OTHERS');
2368 app_exception.raise_exception;
2369
2370 END compile_key_flexfield;
2371
2372
2373 --
2374 -- Procedure
2375 -- validate_hierarchy
2376 -- Purpose
2377 -- Detect hierarchy loop in rows to be added to
2378 -- fnd_flex_value_norm_hierarchy
2379 -- History
2380 -- 02.03.2001 M Marra Created.
2381 -- 03.08.2001 M Marra Modified to work row by row rather than
2382 -- validating the entire hierarchy at once.
2383 -- This reflects the repositioning of
2384 -- FndFlexValueNormHierarchyEO as a composite
2385 -- child of FndFlexValueEO.
2386 --
2387 procedure validate_hierarchy (
2388 p_parent IN varchar2,
2389 p_child_low IN varchar2,
2390 p_child_high IN varchar2,
2391 p_value_set_id IN number
2392 ) is
2393
2394 --is the p_parent a parent value?
2395 cursor parent_value_cursor is
2396 select 'x'
2397 from fnd_flex_values
2398 where flex_value_set_id = p_value_set_id
2399 and flex_value = p_parent
2400 and summary_flag = 'Y';
2401
2402 --find all flex values in a given range
2403 cursor child_values_cursor is
2404 SELECT flex_value
2405 FROM fnd_flex_values
2406 WHERE flex_value_set_id = p_value_set_id
2407 AND flex_value BETWEEN p_child_low AND p_child_high
2408 ORDER by flex_value;
2409
2410 dum Varchar2(1);
2411
2412 BEGIN
2413
2414 -- **************************************************************
2415 -- We check if the parent falls into the child range before
2416 -- this procedure is called, so here we go straight to the
2417 -- verification of the parent value and child range values
2418 -- **************************************************************
2419
2420 -- The parent
2421 Open parent_value_cursor;
2422 Fetch parent_value_cursor Into dum;
2423 if parent_value_cursor%NOTFOUND then
2424 Close parent_value_cursor;
2425 raise invalid_parent;
2426 end if;
2427 Close parent_value_cursor;
2428
2429 -- The child range
2430 For c in child_values_cursor Loop
2431
2432 if has_loop( c.flex_value, p_parent, p_value_set_id ) then
2433 RAISE hierarchy_loop;
2434 end if;
2435
2436 End Loop;
2437
2438 EXCEPTION
2439 WHEN invalid_parent then
2440 fnd_message.set_name('SQLGL','GL_API_COA_INVALID_HIER_PARENT');
2441 fnd_message.set_token('PARENT_VALUE', p_parent);
2442 app_exception.raise_exception;
2443
2444 WHEN hierarchy_loop then
2445 fnd_message.set_name('SQLGL','GL_API_COA_HIERARCHY_LOOP');
2446 fnd_message.set_token('PARENT_VALUE', p_parent);
2447 fnd_message.set_token('CHILD_LOW', p_child_low);
2448 fnd_message.set_token('CHILD_HIGH', p_child_high);
2449 app_exception.raise_exception;
2450
2451 WHEN OTHERS THEN
2452 fnd_message.set_name('SQLGL','GL_UNHANDLED_EXCEPTION');
2453 fnd_message.set_token('PROCEDURE','GL_CHART_OF_ACCOUNTS_API_PKG.validate_hierarchy');
2454 fnd_message.set_token('EVENT','OTHERS');
2455 app_exception.raise_exception;
2456 END validate_hierarchy;
2457
2458 --
2459 -- Procedure
2460 -- compile_hierarchy
2461 -- Purpose
2462 -- Compile hierarchy data from fnd_flex_value_norm_hierarchy.
2463 -- History
2464 -- 02.03.2001 MMarra Created.
2465 --
2466 FUNCTION compile_hierarchy (
2467 p_flex_value_set_id IN NUMBER
2468 ) RETURN VARCHAR2 IS
2469
2470 request_id NUMBER := -1;
2471
2472 -- In case we need the value set name
2473 cursor vset_name_cursor is
2474 select flex_value_set_name
2475 from fnd_flex_value_sets
2476 where flex_value_set_id = p_flex_value_set_id;
2477 vset_name varchar2(100);
2478
2479 BEGIN
2480
2481 --
2482 -- FOR TESTING PURPOSE ( finspeed, SYSADMIN - System Administrator)
2483 -- NEEDS TO BE REMOVED
2484 --
2485 FND_PROFILE.put('USER_ID', 0 );
2486 FND_PROFILE.put('RESP_ID', 20420);
2487 FND_PROFILE.put('RESP_APPL_ID', 1);
2488
2489 --
2490 -- Compile
2491 --
2492 request_id := FND_REQUEST.SUBMIT_REQUEST(
2493 'FND',
2494 'FDFCHY',
2495 '',
2496 '',
2497 FALSE,
2498 to_char(p_flex_value_set_id),
2499 chr(0), '', '', '', '', '', '', '', '',
2500 '', '', '', '', '', '', '', '', '', '',
2501 '', '', '', '', '', '', '', '', '', '',
2502 '', '', '', '', '', '', '', '', '', '',
2503 '', '', '', '', '', '', '', '', '', '',
2504 '', '', '', '', '', '', '', '', '', '',
2505 '', '', '', '', '', '', '', '', '', '',
2506 '', '', '', '', '', '', '', '', '', '',
2507 '', '', '', '', '', '', '', '', '', '',
2508 '', '', '', '', '', '', '', '', '', '');
2509
2510 IF (request_id = 0) THEN
2511 Open vset_name_cursor;
2512 Fetch vset_name_cursor Into vset_name;
2513 Close vset_name_cursor;
2514 RAISE request_failed;
2515 END IF;
2516
2517 --
2518 -- The compilation is successful.
2519 --
2520 RETURN('**Request id** '||to_char(request_id));
2521
2522 EXCEPTION
2523 WHEN request_failed THEN
2524 /* There was a mismatch in error message called here (GL_API_COA_HIER_COMPILE_ERR) and error message defined (GL_API_COA_HIER_COMPILE_ERROR). Same corrected */
2525 fnd_message.set_name('SQLGL','GL_API_COA_HIER_COMPILE_ERROR');
2526 fnd_message.set_token('VALUE_SET_NAME',vset_name);
2527 app_exception.raise_exception;
2528
2529 WHEN OTHERS THEN
2530 fnd_message.set_name('SQLGL','GL_UNHANDLED_EXCEPTION');
2531 fnd_message.set_token('PROCEDURE','GL_CHART_OF_ACCOUNTS_API_PKG.compile_hierarchy');
2532 fnd_message.set_token('EVENT','OTHERS');
2533 app_exception.raise_exception;
2534
2535 END compile_hierarchy;
2536
2537 END GL_CHART_OF_ACCOUNTS_API_PKG;
[Home] [Help]