[Home] [Help]
PACKAGE BODY: APPS.FEM_DIM_CAL_UTIL_PKG
Source
1 PACKAGE BODY FEM_DIM_CAL_UTIL_PKG AS
2 --$Header: fem_dimcal_pkb.plb 120.0 2005/06/06 20:46:42 appldev noship $
3 /*==========================================================================+
4 | Copyright (c) 1997 Oracle Corporation, Redwood Shores, CA, USA |
5 | All rights reserved. |
6 +==========================================================================+
7 | FILENAME
8 |
9 | fem_dimcal_pkb.plb
10 |
11 | NAME
12 |
13 | FEM_DIM_CAL_UTIL_PKG
14 |
15 | DESCRIPTION
16 |
17 | Package Body for FEM_DIM_CAL_UTIL_PKG
18 |
19 | HISTORY
20 |
21 | 17-JAN-05 tmoore Bug 4106880 - added following APIs:
22 | New_Calendar
23 | New_Time_Group_Type_Code
24 | New_Time_Dimension_Group
25 |
26 +=========================================================================*/
27
28 -----------------------
29 -- Package Constants --
30 -----------------------
31 c_resp_app_id CONSTANT NUMBER := FND_GLOBAL.RESP_APPL_ID;
32
33 c_user_id CONSTANT NUMBER := FND_GLOBAL.USER_ID;
34 c_login_id NUMBER := FND_GLOBAL.Login_Id;
35
36 c_module_pkg CONSTANT VARCHAR2(80) := 'fem.plsql.fem_dimension_util_pkg';
37
38 f_set_status BOOLEAN;
39
40 c_log_level_1 CONSTANT NUMBER := fnd_log.level_statement;
41 c_log_level_2 CONSTANT NUMBER := fnd_log.level_procedure;
42 c_log_level_3 CONSTANT NUMBER := fnd_log.level_event;
43 c_log_level_4 CONSTANT NUMBER := fnd_log.level_exception;
44 c_log_level_5 CONSTANT NUMBER := fnd_log.level_error;
45 c_log_level_6 CONSTANT NUMBER := fnd_log.level_unexpected;
46
47 -----------------------
48 -- Package Variables --
49 -----------------------
50 v_module_log VARCHAR2(255);
51
52 v_session_ledger_id NUMBER := NULL;
53
54 v_varchar VARCHAR2(255);
55
56 v_token_value VARCHAR2(150);
57 v_token_trans VARCHAR2(1);
58
59 v_msg_text VARCHAR2(4000);
60
61 gv_prg_msg VARCHAR2(2000);
62 gv_callstack VARCHAR2(2000);
63
64 ------------------------
65 -- Package Exceptions --
66 ------------------------
67 e_bad_param_value EXCEPTION;
68 e_null_param_value EXCEPTION;
69 e_no_value_found EXCEPTION;
70 e_many_values_found EXCEPTION;
71 e_no_version_name EXCEPTION;
72 e_bad_dim_id EXCEPTION;
73 e_dup_mem_id EXCEPTION;
74 e_user_exception EXCEPTION;
75 e_dup_display_code EXCEPTION;
76 e_req_attr_assign EXCEPTION;
77 e_FEM_XDIM_UTIL_ATTR_NODEFAULT EXCEPTION;
78
79 /*************************************************************************
80
81 New_Calendar
82
83 *************************************************************************/
84
85 PROCEDURE New_Calendar (
86 p_api_version IN NUMBER DEFAULT c_api_version,
87 p_init_msg_list IN VARCHAR2 DEFAULT c_false,
88 p_commit IN VARCHAR2 DEFAULT c_false,
89 p_encoded IN VARCHAR2 DEFAULT c_true,
90 x_return_status OUT NOCOPY VARCHAR2,
91 x_msg_count OUT NOCOPY NUMBER,
92 x_msg_data OUT NOCOPY VARCHAR2,
93 x_calendar_id OUT NOCOPY NUMBER,
94 p_cal_disp_code IN VARCHAR2,
95 p_calendar_name IN VARCHAR2,
96 p_source_cd IN NUMBER,
97 p_period_set_name IN VARCHAR2,
98 p_ver_name IN VARCHAR2,
99 p_ver_disp_cd IN VARCHAR2,
100 p_calendar_desc IN VARCHAR2,
101 p_include_adj_per_flg IN VARCHAR2,
102 p_default_cal_per IN NUMBER DEFAULT NULL,
103 p_default_member IN NUMBER DEFAULT NULL,
104 p_default_load_member IN NUMBER DEFAULT NULL,
105 p_default_hier IN NUMBER DEFAULT NULL
106 )
107 IS
108
109 c_module_prg CONSTANT VARCHAR2(160) := c_module_pkg||'.new_calendar';
110
111 c_dim_label CONSTANT VARCHAR2(30) := 'CALENDAR';
112 c_enbld_flg CONSTANT VARCHAR2(1) := 'Y';
113 c_ro_flg CONSTANT VARCHAR2(1) := 'N';
114 c_pers_flg CONSTANT VARCHAR2(1) := 'N';
115 c_obj_ver_no CONSTANT NUMBER := 1;
116 c_aw_flg CONSTANT VARCHAR2(1) := 'N';
117
118 v_row_id VARCHAR2(20) := '';
119
120 v_dim_id NUMBER;
121 v_cal_id NUMBER;
122 v_ver_id NUMBER;
123 v_attr_id NUMBER;
124 v_xdim_id NUMBER;
125 v_xdim_tab VARCHAR2(30);
126 v_xdim_col VARCHAR2(30);
127 v_xdim_cd_col VARCHAR2(30);
128 v_attr_col VARCHAR2(30);
129 v_attr_label VARCHAR2(30);
130 v_attr_value VARCHAR2(1000);
131 v_reqd_flg VARCHAR2(1);
132 v_attr_num NUMBER;
133 v_attr_vch VARCHAR2(30);
134 v_attr_date DATE;
135
136 v_sql_cmd VARCHAR2(32767);
137
138 CURSOR cv_dim_attr IS
139 SELECT attribute_id,
140 attribute_varchar_label,
141 attribute_dimension_id,
142 attribute_value_column_name,
143 attribute_required_flag
144 FROM fem_dim_attributes_b
145 WHERE dimension_id =
146 (SELECT dimension_id
147 FROM fem_dimensions_b
148 WHERE dimension_varchar_label = c_dim_label);
149
150 TYPE cv_curs_type IS REF CURSOR;
151 cv_attr_dim cv_curs_type;
152
153 BEGIN
154
155 x_return_status := c_success;
156 x_calendar_id := -1;
157
158 FEM_Dimension_Util_Pkg.Validate_OA_Params (
159 p_api_version => p_api_version,
160 p_init_msg_list => p_init_msg_list,
161 p_commit => p_commit,
162 p_encoded => p_encoded,
163 x_return_status => x_return_status);
164
165 IF (x_return_status <> c_success)
166 THEN
167 FND_MSG_PUB.Count_and_Get(
168 p_encoded => c_false,
169 p_count => x_msg_count,
170 p_data => x_msg_data);
171 RETURN;
172 END IF;
173
174 ------------------------
175 -- Get New Calendar ID --
176 ------------------------
177 SELECT dimension_id
178 INTO v_dim_id
179 FROM fem_dimensions_b
180 WHERE dimension_varchar_label = c_dim_label;
181
182 v_cal_id := FEM_Dimension_Util_Pkg.Generate_Member_ID(
183 p_api_version => p_api_version,
184 p_init_msg_list => c_false,
185 p_commit => c_false,
186 p_encoded => p_encoded,
187 x_return_status => x_return_status,
188 x_msg_count => x_msg_count,
189 x_msg_data => x_msg_data,
190 p_dim_id => v_dim_id);
191
192 IF (x_return_status <> c_success)
193 THEN
194 RETURN;
195 END IF;
196
197 -------------------------------
198 -- Insert New Calendar Member --
199 -------------------------------
200 BEGIN
201 FEM_CALENDARS_PKG.INSERT_ROW(
202 x_rowid => v_row_id,
203 x_calendar_id => v_cal_id,
204 x_enabled_flag => c_enbld_flg,
205 x_calendar_display_code => p_cal_disp_code,
206 x_read_only_flag => c_ro_flg,
207 x_personal_flag => c_pers_flg,
208 x_object_version_number => c_obj_ver_no,
209 x_calendar_name => p_calendar_name,
210 x_description => p_calendar_desc,
211 x_creation_date => sysdate,
212 x_created_by => c_user_id,
213 x_last_update_date => sysdate,
214 x_last_updated_by => c_user_id,
215 x_last_update_login => null);
216 EXCEPTION
217 WHEN dup_val_on_index THEN
218 RAISE e_dup_display_code;
219 END;
220
221 -----------------------------------
222 -- Insert New Calendar Attributes --
223 -----------------------------------
224 FOR r_dim_attr IN cv_dim_attr
225 LOOP
226 v_attr_id := r_dim_attr.attribute_id;
227 v_attr_label := r_dim_attr.attribute_varchar_label;
228 v_xdim_id := r_dim_attr.attribute_dimension_id;
229 v_attr_col := r_dim_attr.attribute_value_column_name;
230 v_reqd_flg := r_dim_attr.attribute_required_flag;
231
232 -------------------------------
233 -- Check Attribute's Version --
234 -------------------------------
235 SELECT MIN(version_id)
236 INTO v_ver_id
237 FROM fem_dim_attr_versions_b
238 WHERE attribute_id = v_attr_id
239 AND default_version_flag = 'Y';
240
241 IF (v_ver_id IS NULL)
242 THEN
243 IF (p_ver_name IS NULL)
244 THEN
245 RAISE e_no_version_name;
246 ELSIF (p_ver_disp_cd IS NULL)
247 THEN
248 RAISE e_no_version_name;
249 END IF;
250
251 SELECT fem_dim_attr_versions_b_s.NEXTVAL
252 INTO v_ver_id FROM dual;
253
254 FEM_DIM_ATTR_VERSIONS_PKG.INSERT_ROW(
255 x_rowid => v_row_id,
256 x_version_id => v_ver_id,
257 x_aw_snapshot_flag => c_aw_flg,
258 x_version_display_code => p_ver_disp_cd,
259 x_object_version_number => c_obj_ver_no,
260 x_default_version_flag => 'Y',
261 x_personal_flag => c_pers_flg,
262 x_attribute_id => v_attr_id,
263 x_version_name => p_ver_name,
264 x_description => null,
265 x_creation_date => sysdate,
266 x_created_by => c_user_id,
267 x_last_update_date => sysdate,
268 x_last_updated_by => c_user_id,
269 x_last_update_login => null);
270 END IF;
271
272 -----------------------------
273 -- Get Attribute Parameter --
274 -----------------------------
275 CASE v_attr_label
276 WHEN 'DEFAULT_CAL_PERIOD' THEN
277 v_attr_value := p_default_cal_per;
278 WHEN 'DEFAULT_MEMBER' THEN
279 v_attr_value := p_default_member;
280 WHEN 'DEFAULT_LOAD_MEMBER' THEN
281 v_attr_value := p_default_load_member;
282 WHEN 'DEFAULT_HIERARCHY' THEN
283 v_attr_value := p_default_hier;
284 WHEN 'INCLUDE_ADJ_PERIOD_FLAG' THEN
285 v_attr_value := p_include_adj_per_flg;
286 WHEN 'SOURCE_SYSTEM_CODE' THEN
287 v_attr_value := p_source_cd;
288 WHEN 'PERIOD_SET_NAME' THEN
289 v_attr_value := p_period_set_name;
290 ELSE
291 v_attr_value := null;
292 FEM_ENGINES_PKG.Tech_Message(
293 p_severity => c_log_level_1,
294 p_module => c_module_pkg||'.New_Calendar.bad_attr_list',
295 p_msg_text => 'The Calendar attribute '||v_attr_label||
296 ' is in FEM_DIM_ATTRIBUTES_B but not in'||
297 ' the API''s list of attribute labels');
298
299 FEM_ENGINES_PKG.Put_Message(
300 p_app_name => 'FEM',
301 p_msg_name => 'FEM_BAD_ATTR_LIST_WARN',
302 p_token1 => 'ATTR',
303 p_value1 => v_attr_label);
304 END CASE;
305
306 IF (v_attr_value IS NULL)
307 THEN
308 CASE v_reqd_flg
309 WHEN 'Y' THEN
310 RAISE e_null_param_value;
311 ELSE null;
312 END CASE;
313 ELSE
314 IF (v_attr_col = 'DIM_ATTRIBUTE_NUMERIC_MEMBER' OR
315 v_attr_col = 'DIM_ATTRIBUTE_VARCHAR_MEMBER')
316 THEN
317 -------------------------------------
318 -- Attribute is a Dimension Attribute
319 -- which needs to be validated
320 -------------------------------------
321 SELECT member_b_table_name,
322 member_col
323 INTO v_xdim_tab,
324 v_xdim_col
325 FROM fem_xdim_dimensions
326 WHERE dimension_id = v_xdim_id;
327
328 v_sql_cmd :=
329 'SELECT '||v_xdim_col||
330 ' FROM '||v_xdim_tab||
331 ' WHERE '||v_xdim_col||' = :b_attr_value';
332
333 IF (v_attr_col = 'DIM_ATTRIBUTE_NUMERIC_MEMBER')
334 THEN
335 BEGIN
336 EXECUTE IMMEDIATE v_sql_cmd
337 INTO v_attr_num
338 USING v_attr_value;
339 EXCEPTION
340 WHEN no_data_found THEN
341 RAISE e_bad_param_value;
342 END;
343 v_attr_vch := '';
344 ELSIF (v_attr_col = 'DIM_ATTRIBUTE_VARCHAR_MEMBER')
345 THEN
346 BEGIN
347 EXECUTE IMMEDIATE v_sql_cmd
348 INTO v_attr_vch
349 USING v_attr_value;
350 EXCEPTION
351 WHEN no_data_found THEN
352 RAISE e_bad_param_value;
353 END;
354 v_attr_num := '';
355 END IF;
356
357 INSERT INTO fem_calendars_attr(
358 attribute_id,
359 version_id,
360 calendar_id,
361 dim_attribute_numeric_member,
362 dim_attribute_varchar_member,
363 number_assign_value,
364 varchar_assign_value,
365 date_assign_value,
366 creation_date,
367 created_by,
368 last_updated_by,
369 last_update_date,
370 last_update_login,
371 aw_snapshot_flag,
372 object_version_number)
373 VALUES(
374 v_attr_id,
375 v_ver_id,
376 v_cal_id,
377 v_attr_num,
378 v_attr_vch,
379 null,
380 null,
381 null,
382 sysdate,
383 c_user_id,
384 c_user_id,
385 sysdate,
386 null,
387 c_aw_flg,
388 c_obj_ver_no);
389
390 ELSIF (v_attr_col = 'NUMBER_ASSIGN_VALUE')
391 THEN
392 ----------------------------------------
393 -- Attribute is an assigned number value
394 ----------------------------------------
395 INSERT INTO fem_calendars_attr(
396 attribute_id,
397 version_id,
398 calendar_id,
399 dim_attribute_numeric_member,
400 dim_attribute_varchar_member,
401 number_assign_value,
402 varchar_assign_value,
403 date_assign_value,
404 creation_date,
405 created_by,
406 last_updated_by,
407 last_update_date,
408 last_update_login,
409 aw_snapshot_flag,
410 object_version_number)
411 VALUES(
412 v_attr_id,
413 v_ver_id,
414 v_cal_id,
415 null,
416 null,
417 v_attr_value,
418 null,
419 null,
420 sysdate,
421 c_user_id,
422 c_user_id,
423 sysdate,
424 null,
425 c_aw_flg,
426 c_obj_ver_no);
427
428 ELSIF (v_attr_col = 'VARCHAR_ASSIGN_VALUE')
429 THEN
430 -----------------------------------------
431 -- Attribute is an assigned varchar value
432 -----------------------------------------
433 INSERT INTO fem_calendars_attr(
434 attribute_id,
435 version_id,
436 calendar_id,
437 dim_attribute_numeric_member,
438 dim_attribute_varchar_member,
439 number_assign_value,
440 varchar_assign_value,
441 date_assign_value,
442 creation_date,
443 created_by,
444 last_updated_by,
445 last_update_date,
446 last_update_login,
447 aw_snapshot_flag,
448 object_version_number)
449 VALUES(
450 v_attr_id,
451 v_ver_id,
452 v_cal_id,
453 null,
454 null,
455 null,
456 v_attr_value,
457 null,
458 sysdate,
459 c_user_id,
460 c_user_id,
461 sysdate,
462 null,
463 c_aw_flg,
464 c_obj_ver_no);
465
466 ELSIF (v_attr_col = 'DATE_ASSIGN_VALUE')
467 THEN
468 --------------------------------------
469 -- Attribute is an assigned date value
470 --------------------------------------
471 INSERT INTO fem_calendars_attr(
472 attribute_id,
473 version_id,
474 calendar_id,
475 dim_attribute_numeric_member,
476 dim_attribute_varchar_member,
477 number_assign_value,
478 varchar_assign_value,
479 date_assign_value,
480 creation_date,
481 created_by,
482 last_updated_by,
483 last_update_date,
484 last_update_login,
485 aw_snapshot_flag,
486 object_version_number)
487 VALUES(
488 v_attr_id,
489 v_ver_id,
490 v_cal_id,
491 null,
492 null,
493 null,
494 null,
495 v_attr_date,
496 sysdate,
497 c_user_id,
498 c_user_id,
499 sysdate,
500 null,
501 c_aw_flg,
502 c_obj_ver_no);
503 END IF;
504
505 END IF;
506
507 END LOOP;
508
509 x_calendar_id := v_cal_id;
510
511 IF (p_commit = c_true)
512 THEN
513 COMMIT;
514 END IF;
515
516 FND_MSG_PUB.Count_and_Get(
517 p_encoded => p_encoded,
518 p_count => x_msg_count,
519 p_data => x_msg_data);
520
521 EXCEPTION
522 WHEN e_bad_param_value THEN
523 ROLLBACK;
524 FEM_ENGINES_PKG.Put_Message(
525 p_app_name => 'FEM',
526 p_msg_name => 'FEM_BAD_PARAM_VALUE_ERR',
527 p_token1 => 'PARAM',
528 p_value1 => FEM_Dimension_Util_Pkg.Get_Dim_Attr_Name(
529 p_attr_id => v_attr_id),
530 p_token2 => 'VALUE',
531 p_value2 => v_attr_value);
532 FND_MSG_PUB.Count_and_Get(
533 p_encoded => p_encoded,
534 p_count => x_msg_count,
535 p_data => x_msg_data);
536 x_return_status := c_error;
537
538 WHEN e_null_param_value THEN
539 ROLLBACK;
540 FEM_ENGINES_PKG.Put_Message(
541 p_app_name => 'FEM',
542 p_msg_name => 'FEM_NULL_PARAM_VALUE_ERR',
543 p_token1 => 'PARAM',
544 p_value1 => FEM_Dimension_Util_Pkg.Get_Dim_Attr_Name(
545 p_attr_id => v_attr_id));
546 FND_MSG_PUB.Count_and_Get(
547 p_encoded => p_encoded,
548 p_count => x_msg_count,
549 p_data => x_msg_data);
550 x_return_status := c_error;
551
552 WHEN e_no_version_name THEN
553 ROLLBACK;
554 FEM_ENGINES_PKG.Put_Message(
555 p_app_name => 'FEM',
556 p_msg_name => 'FEM_NO_VERSION_NAME_ERR',
557 p_token1 => 'ENTITY',
558 p_value1 => FEM_Dimension_Util_Pkg.Get_Dim_Attr_Name(
559 p_attr_id => v_attr_id));
560 FND_MSG_PUB.Count_and_Get(
561 p_encoded => p_encoded,
562 p_count => x_msg_count,
563 p_data => x_msg_data);
564 x_return_status := c_error;
565
566 WHEN e_dup_display_code THEN
567 ROLLBACK;
568 FEM_ENGINES_PKG.Put_Message(
569 p_app_name => 'FEM',
570 p_msg_name => 'FEM_DUP_DISPLAY_CODE_ERR',
571 p_token1 => 'VALUE',
572 p_value1 => p_cal_disp_code);
573 FND_MSG_PUB.Count_and_Get(
574 p_encoded => p_encoded,
575 p_count => x_msg_count,
576 p_data => x_msg_data);
577 x_return_status := c_error;
578
579 END New_Calendar;
580
581 /*************************************************************************
582
583 New_Time_Group_Type
584
585 *************************************************************************/
586
587 PROCEDURE New_Time_Group_Type (
588 p_api_version IN NUMBER DEFAULT c_api_version,
589 p_init_msg_list IN VARCHAR2 DEFAULT c_false,
590 p_commit IN VARCHAR2 DEFAULT c_false,
591 p_encoded IN VARCHAR2 DEFAULT c_true,
592 x_return_status OUT NOCOPY VARCHAR2,
593 x_msg_count OUT NOCOPY NUMBER,
594 x_msg_data OUT NOCOPY VARCHAR2,
595 p_time_grp_type_code IN VARCHAR2,
596 p_time_grp_type_name IN VARCHAR2,
597 p_time_grp_type_desc IN VARCHAR2 DEFAULT NULL,
598 p_periods_in_year IN NUMBER,
599 p_ver_name IN VARCHAR2,
600 p_ver_disp_cd IN VARCHAR2,
601 p_read_only_flag IN VARCHAR2 DEFAULT 'N'
602 )
603 IS
604 c_enbld_flg CONSTANT VARCHAR2(1) := 'Y';
605 c_pers_flg CONSTANT VARCHAR2(1) := 'N';
606 c_obj_ver_no CONSTANT NUMBER := 1;
607 c_aw_flg CONSTANT VARCHAR2(1) := 'N';
608
609 v_row_id VARCHAR2(20) := '';
610
611 v_ver_id NUMBER;
612 v_attr_id NUMBER;
613 v_attr_col VARCHAR2(30);
614 v_attr_label VARCHAR2(30);
615
616 v_xdim_id NUMBER;
617 v_xdim_tab VARCHAR2(30);
618 v_xdim_col VARCHAR2(30);
619 v_xdim_cd_col VARCHAR2(30);
620
621 BEGIN
622
623 x_return_status := c_success;
624
625 FEM_Dimension_Util_Pkg.Validate_OA_Params (
626 p_api_version => p_api_version,
627 p_init_msg_list => p_init_msg_list,
628 p_commit => p_commit,
629 p_encoded => p_encoded,
630 x_return_status => x_return_status);
631
632 IF (x_return_status <> c_success)
633 THEN
634 FND_MSG_PUB.Count_and_Get(
635 p_encoded => c_false,
636 p_count => x_msg_count,
637 p_data => x_msg_data);
638 RETURN;
639 END IF;
640
641 ---------------------------------------
642 -- Insert New Time Group Type Member --
643 ---------------------------------------
644 BEGIN
645 FEM_TIME_GROUP_TYPES_PKG.INSERT_ROW(
646 x_rowid => v_row_id,
647 x_time_group_type_code => p_time_grp_type_code,
648 x_enabled_flag => c_enbld_flg,
649 x_personal_flag => c_pers_flg,
650 x_object_version_number => c_obj_ver_no,
651 x_read_only_flag => p_read_only_flag,
652 x_time_group_type_name => p_time_grp_type_name,
653 x_description => p_time_grp_type_desc,
654 x_creation_date => sysdate,
655 x_created_by => c_user_id,
656 x_last_update_date => sysdate,
657 x_last_updated_by => c_user_id,
658 x_last_update_login => null);
659 EXCEPTION
660 WHEN dup_val_on_index THEN
661 RAISE e_dup_display_code;
662 END;
663
664 -------------------------------------------
665 -- Insert New Time Group Type Attributes --
666 -------------------------------------------
667 SELECT attribute_id,
668 attribute_varchar_label,
669 attribute_dimension_id,
670 attribute_value_column_name
671 INTO v_attr_id,
672 v_attr_label,
673 v_xdim_id,
674 v_attr_col
675 FROM fem_dim_attributes_b
676 WHERE dimension_id =
677 (SELECT dimension_id
678 FROM fem_dimensions_b
679 WHERE dimension_varchar_label = 'TIME_GROUP_TYPE')
680 AND attribute_varchar_label = 'PERIODS_IN_YEAR';
681
682 -------------------------------
683 -- Check Attribute's Version --
684 -------------------------------
685 SELECT MIN(version_id)
686 INTO v_ver_id
687 FROM fem_dim_attr_versions_b
688 WHERE attribute_id = v_attr_id
689 AND default_version_flag = 'Y';
690
691 IF (v_ver_id IS NULL)
692 THEN
693 IF (p_ver_name IS NULL)
694 THEN
695 RAISE e_no_version_name;
696 ELSIF (p_ver_disp_cd IS NULL)
697 THEN
698 RAISE e_no_version_name;
699 END IF;
700
701 SELECT fem_dim_attr_versions_b_s.NEXTVAL
702 INTO v_ver_id FROM dual;
703
704 FEM_DIM_ATTR_VERSIONS_PKG.INSERT_ROW(
705 x_rowid => v_row_id,
706 x_version_id => v_ver_id,
707 x_aw_snapshot_flag => c_aw_flg,
708 x_version_display_code => p_ver_disp_cd,
709 x_object_version_number => c_obj_ver_no,
710 x_default_version_flag => 'Y',
711 x_personal_flag => c_pers_flg,
712 x_attribute_id => v_attr_id,
713 x_version_name => p_ver_name,
714 x_description => null,
715 x_creation_date => sysdate,
716 x_created_by => c_user_id,
717 x_last_update_date => sysdate,
718 x_last_updated_by => c_user_id,
719 x_last_update_login => null);
720 END IF;
721
722 -----------------------------
723 -- Insert Attribute Values --
724 -----------------------------
725 INSERT INTO fem_time_grp_types_attr(
726 attribute_id,
727 version_id,
728 time_group_type_code,
729 dim_attribute_numeric_member,
730 dim_attribute_value_set_id,
731 dim_attribute_varchar_member,
732 number_assign_value,
733 varchar_assign_value,
734 date_assign_value,
735 creation_date,
736 created_by,
737 last_updated_by,
738 last_update_date,
739 last_update_login,
740 aw_snapshot_flag,
741 object_version_number)
742 VALUES(
743 v_attr_id,
744 v_ver_id,
745 p_time_grp_type_code,
746 null,
747 null,
748 null,
749 p_periods_in_year,
750 null,
751 null,
752 sysdate,
753 c_user_id,
754 c_user_id,
755 sysdate,
756 null,
757 c_aw_flg,
758 c_obj_ver_no);
759
760 IF (p_commit = c_true)
761 THEN
762 COMMIT;
763 END IF;
764
765 EXCEPTION
766 WHEN e_dup_display_code THEN
767 ROLLBACK;
768 FEM_ENGINES_PKG.Put_Message(
769 p_app_name => 'FEM',
770 p_msg_name => 'FEM_DUP_DISPLAY_CODE_ERR',
771 p_token1 => 'VALUE',
772 p_value1 => p_time_grp_type_code);
773 FND_MSG_PUB.Count_and_Get(
774 p_encoded => p_encoded,
775 p_count => x_msg_count,
776 p_data => x_msg_data);
777 x_return_status := c_error;
778
779 WHEN e_no_version_name THEN
780 ROLLBACK;
781 FEM_ENGINES_PKG.Put_Message(
782 p_app_name => 'FEM',
783 p_msg_name => 'FEM_NO_VERSION_NAME_ERR',
784 p_token1 => 'ENTITY',
785 p_value1 => FEM_Dimension_Util_Pkg.Get_Dim_Attr_Name(
786 p_attr_id => v_attr_id));
787 FND_MSG_PUB.Count_and_Get(
788 p_encoded => p_encoded,
789 p_count => x_msg_count,
790 p_data => x_msg_data);
791 x_return_status := c_error;
792
793 END New_Time_Group_Type;
794
795 /*************************************************************************
796
797 New_Time_Dimension_Group
798
799 *************************************************************************/
800
801 PROCEDURE New_Time_Dimension_Group (
802 p_api_version IN NUMBER DEFAULT c_api_version,
803 p_init_msg_list IN VARCHAR2 DEFAULT c_false,
804 p_commit IN VARCHAR2 DEFAULT c_false,
805 p_encoded IN VARCHAR2 DEFAULT c_true,
806 x_return_status OUT NOCOPY VARCHAR2,
807 x_msg_count OUT NOCOPY NUMBER,
808 x_msg_data OUT NOCOPY VARCHAR2,
809 x_dim_grp_id OUT NOCOPY NUMBER,
810 p_time_grp_type_code IN VARCHAR2,
811 p_dim_grp_name IN VARCHAR2,
812 p_dim_grp_disp_cd IN VARCHAR2,
813 p_dim_grp_desc IN VARCHAR2 DEFAULT NULL,
814 p_read_only_flag IN VARCHAR2 DEFAULT 'N'
815 )
816 IS
817 c_enbld_flg CONSTANT VARCHAR2(1) := 'Y';
818 c_pers_flg CONSTANT VARCHAR2(1) := 'N';
819 c_obj_ver_no CONSTANT NUMBER := 1;
820
821 v_row_id VARCHAR2(20) := '';
822
823 v_time_grp_type_code VARCHAR2(30);
824 v_dim_grp_id NUMBER;
825 v_dim_grp_key NUMBER;
826 v_cal_per_dim_id NUMBER;
827 v_dim_grp_seq NUMBER;
828
829 BEGIN
830
831 x_return_status := c_success;
832 x_dim_grp_id := -1;
833
834 FEM_Dimension_Util_Pkg.Validate_OA_Params (
835 p_api_version => p_api_version,
836 p_init_msg_list => p_init_msg_list,
837 p_commit => p_commit,
838 p_encoded => p_encoded,
839 x_return_status => x_return_status);
840
841 IF (x_return_status <> c_success)
842 THEN
843 FND_MSG_PUB.Count_and_Get(
844 p_encoded => c_false,
845 p_count => x_msg_count,
846 p_data => x_msg_data);
847 RETURN;
848 END IF;
849
850 ---------------------------------
851 -- Verify Time_Group_Type_Code --
852 ---------------------------------
853 BEGIN
854 SELECT time_group_type_code
855 INTO v_time_grp_type_code
856 FROM fem_time_group_types_b
857 WHERE time_group_type_code = p_time_grp_type_code;
858 EXCEPTION
859 WHEN no_data_found THEN
860 FEM_ENGINES_PKG.Put_Message(
861 p_app_name => 'FEM',
862 p_msg_name => 'FEM_NEW_TIME_GRP_BAD_GRP_TYPE',
863 p_token1 => 'GRP_TYPE',
864 p_value1 => NVL(p_time_grp_type_code,'NULL'));
865 FND_MSG_PUB.Count_and_Get(
866 p_encoded => p_encoded,
867 p_count => x_msg_count,
868 p_data => x_msg_data);
869 x_return_status := c_error;
870 RETURN;
871 END;
872
873 ---------------------------------
874 -- Get Cal Period Dimension ID --
875 ---------------------------------
876 SELECT dimension_id
877 INTO v_cal_per_dim_id
878 FROM fem_dimensions_b
879 where dimension_varchar_label = 'CAL_PERIOD';
880
881 --------------------------------
882 -- Get New Dimension Group ID --
883 --------------------------------
884 SELECT fem_dimension_grps_b_s.NEXTVAL
885 INTO v_dim_grp_id
886 FROM dual;
887
888 ---------------------------------
889 -- Get New Dimension Group Key --
890 ---------------------------------
891 SELECT fem_time_dimension_group_key_s.NEXTVAL
892 INTO v_dim_grp_key
893 FROM dual;
894
895 ---------------------------------
896 -- Set New Dimension Group Seq --
897 ---------------------------------
898 SELECT MAX(dimension_group_seq)+1
899 INTO v_dim_grp_seq
900 FROM fem_dimension_grps_b;
901
902 ---------------------------------------
903 -- Insert New Dimension Group Member --
904 ---------------------------------------
905 BEGIN
906 FEM_DIMENSION_GRPS_PKG.INSERT_ROW(
907 x_rowid => v_row_id,
908 x_dimension_group_id => v_dim_grp_id,
909 x_time_dimension_group_key => v_dim_grp_key,
910 x_dimension_id => v_cal_per_dim_id,
911 x_dimension_group_seq => v_dim_grp_seq,
912 x_time_group_type_code => p_time_grp_type_code,
913 x_read_only_flag => p_read_only_flag,
914 x_object_version_number => c_obj_ver_no,
915 x_personal_flag => c_pers_flg,
916 x_enabled_flag => c_enbld_flg,
917 x_dimension_group_display_code => p_dim_grp_disp_cd,
918 x_dimension_group_name => p_dim_grp_name,
919 x_description => p_dim_grp_desc,
920 x_creation_date => sysdate,
921 x_created_by => c_user_id,
922 x_last_update_date => sysdate,
923 x_last_updated_by => c_user_id,
924 x_last_update_login => null);
925 EXCEPTION
926 WHEN dup_val_on_index THEN
927 FEM_ENGINES_PKG.Put_Message(
928 p_app_name => 'FEM',
929 p_msg_name => 'FEM_DUP_DISPLAY_CODE_ERR',
930 p_token1 => 'VALUE',
931 p_value1 => p_dim_grp_disp_cd);
932 FND_MSG_PUB.Count_and_Get(
933 p_encoded => p_encoded,
934 p_count => x_msg_count,
935 p_data => x_msg_data);
936 x_return_status := c_error;
937 RETURN;
938 END;
939
940 IF (p_commit = c_true)
941 THEN
942 COMMIT;
943 END IF;
944
945 x_dim_grp_id := v_dim_grp_id;
946
947 END New_Time_Dimension_Group;
948
949 ---------------------------------------------
950
951 END FEM_DIM_CAL_UTIL_PKG;