[Home] [Help]
PACKAGE BODY: APPS.FEM_DEFCALP_UTIL_PKG
Source
1 PACKAGE BODY FEM_DEFCALP_UTIL_PKG AS
2 -- $Header: fem_defcalp_utl.plb 120.6 2006/07/11 20:27:27 rflippo ship $
3 /*=======================================================================+
4 Copyright (c) 1995 Oracle Corporation Redwood Shores, California, USA|
5 | All rights reserved. |
6 +=======================================================================+
7 | FILENAME
8 | fem_defcalp_utl.sql
9 |
10 | DESCRIPTION
11 | Create a default Calendar Period member based on the current sysdate and
12 | create a default calendar period hierarchy using this member
13 |
14 | MODIFICATION HISTORY
15 | Rob Flippo 04/26/2004 Created
16 | Rob Flippo 06/22/2004 Modified select on dimension_group_id
17 | to include dimension_id since multiple
18 | groups can be named "Year" in the db
19 | Rob Flippo 08/02/2004 Add month and quarter levels to
20 | the seeded cal period hier
21 | Rob Flippo 08/20/2004 Change responsibility since all
22 | FEM responsibilities are stubbed out
23 | Gordon Cheng 10/11/2004 Defaults profile option FEM_LEDGER
24 | at the site level to the default ledger.
25 | Rob Flippo 01/18/2005 Bug#4107563 FEM_DEFCALP.SQL NOT PROPERLY
26 | RECOGNIZING WHEN IT HAS ALREADY BEEN RUN
27 | Rob Flippo 02/14/2005 Bug#4167929 Qtr and Month group seq
28 | not correct for seeded cal per hier
29 | Rob Flippo 02/15/2005 Bug#4188679 Folder security means
30 | need privs on fem_user_folders
31 | for sysadmin user
32 | Gordon Cheng 02/28/2005 Bug 3695254. Defaults another ledger:
33 | OGL_SOURCE_LEDGER_GROUP.
34 | Rob Flippo 04/27/2005 Bug#4288332 Signature change for
35 | new_ledger API
36 | Rob Flippo 05/06/2005 bug#4344994 converted
37 | fem_defcalp.sql to a package
38 | so that it can be easily called
39 | by the Refresh engine
40 | Rob Flippo 05/16/2005 Added exception logic so that the
41 | procedure returns an OUT variable
42 | designating success or error;
43 | Error condition is when the
44 | procedure does not create one of
45 | the required pieces, such as default
46 | ledger, hierarchy, cal period, etc.
47 | Rob Flippo 05/17/2005 Modified for FEM.D compatability
48 | by using source_sys_code in
49 | new_ledger api rather than
50 | source sys display code
51 | Rob Flippo 08/19/2005 Bug#4547880 Update default attr
52 | info for budget_first_period and
53 | budget_last_period using the
54 | created cal period id
55 | Gordon Cheng 11/17/2005 Bug#4540353. Makes sure default ledger
56 | v115.5 and cal period are assigned to the
57 | Budget attributes, if the default
58 | assignment is null.
59 | Rob Flippo 05/15/2006 Bug#5201184 Make sure default
60 | Bus Rel code assigned to the
61 | Business Rel attribute on the
62 | Customer dimension if it is null
63 | Rob Flippo 07/11/2006 Bug#5237422 Comment out code to create
64 | OGL_SOURCE_LEDGER_GROUP because it is now
65 | created via fem_srcledgers.lct/ldt
66 *=======================================================================*/
67
68
69 PROCEDURE main (x_status OUT NOCOPY VARCHAR2) IS
70 v_dup_cal_period_flag VARCHAR2(1);
71 -- Initialization variables
72
73 gv_apps_user_id CONSTANT NUMBER := FND_GLOBAL.User_Id;
74 c_fem CONSTANT VARCHAR2(3) := 'FEM';
75
76 v_user_id NUMBER;
77 v_app_id NUMBER;
78 v_resp_id NUMBER;
79
80 -- Cal Period variables
81 v_rowid ROWID;
82 c_cal_period_number CONSTANT NUMBER :=1;
83 c_time_dim_group_key CONSTANT NUMBER := 10;
84 v_calendar_id NUMBER;
85 v_start_date DATE;
86 v_end_date DATE;
87 v_cal_period_id NUMBER;
88 v_dimension_group_id NUMBER; -- group for Year
89 v_month_dimgrp_id NUMBER; -- group for Month
90 v_qtr_dimgrp_id NUMBER; -- group for Quarter
91 v_accounting_year NUMBER;
92 v_source_system_dc VARCHAR2(150);
93 v_source_system_code NUMBER;
94 v_ofa_source_sys_code NUMBER;
95 v_ofa_source_sys_dc VARCHAR2(150);
96 v_num_msg NUMBER;
97
98 v_cal_period_count NUMBER; -- identifies if 1 cal period exists in db
99
100 -- Hier Object ID variables
101 v_object_id NUMBER(9);
102 v_object_definition_id NUMBER(9);
103
104 -- Ledger variables
105 v_ledger_id FEM_LEDGERS_B.ledger_id%TYPE;
106 v_boolean BOOLEAN;
107 v_verify_ledger_id FEM_LEDGERS_B.ledger_id%TYPE;
108 v_cal_hier_attribute_id FEM_LEDGERS_ATTR.attribute_id%TYPE;
109 v_cal_hier_version_id FEM_LEDGERS_ATTR.version_id%TYPE;
110
111 -- Business Relationship variables
112 v_bus_rel_mbr_id NUMBER;
113 v_bus_rel_dim_id NUMBER;
114 v_bus_rel_attr_id NUMBER;
115 v_customer_dim_id NUMBER;
116
117 -- Other variables
118 v_dim_id FEM_DIMENSIONS_B.dimension_id%TYPE;
119 v_count NUMBER;
120
121 -- Message Variables
122 v_msg_count NUMBER;
123 v_msg_data VARCHAR2(4000);
124 v_return_status VARCHAR2(30);
125 v_msg_out NUMBER;
126
127
128 -- Exceptions
129 e_no_def_ledger exception;
130 e_bus_rel_error exception;
131
132
133 BEGIN
134
135 v_dup_cal_period_flag := 'N';
136 -- Initialization variables
137 v_user_id :=gv_apps_user_id;
138
139
140 BEGIN -- Create default Cal Period and Ledger
141
142 select count(*)
143 into v_cal_period_count
144 from fem_cal_periods_b;
145
146 -- If one cal_period exists in the db, then we don't
147 -- want to create a default cal_period, nor do we want
148 -- to create a cal_period hier or default ledger
149
150 IF v_cal_period_count = 0 THEN
151
152 -- Insert privs in fem_user_folders for SYSADMIN user
153 BEGIN
154 insert into fem_user_folders
155 (FOLDER_ID
156 ,USER_ID
157 ,WRITE_FLAG
158 ,CREATED_BY
159 ,CREATION_DATE
160 ,LAST_UPDATED_BY
161 ,LAST_UPDATE_DATE
162 ,LAST_UPDATE_LOGIN
163 ,OBJECT_VERSION_NUMBER)
164 select 1100
165 ,v_user_id
166 ,'Y'
167 ,v_user_id
168 ,sysdate
169 ,v_user_id
170 ,sysdate
171 ,null
172 ,1
173 from dual;
174
175 EXCEPTION
176 when dup_val_on_index then null;
177 END;
178
179 -- Create the Cal Period for the Hierarchy
180 select calendar_id
181 into v_calendar_id
182 from fem_calendars_b
183 where calendar_display_code='Default';
184
185 select trunc(sysdate,'YYYY')
186 into v_start_date
187 from dual;
188
189 v_end_date := LAST_DAY(ADD_MONTHS(v_start_date,11));
190
191
192 select LPAD(to_char(to_number(to_char(v_end_date,'j'))),7,'0')||
193 LPAD(TO_CHAR(c_cal_period_number),15,'0')||
194 LPAD(to_char(v_calendar_id),5,'0')||
195 LPAD(to_char(c_time_dim_group_key),5,'0')
196 into v_cal_period_id
197 from dual;
198
199
200 select dimension_group_id
201 into v_dimension_group_id
202 from fem_dimension_grps_b
203 where dimension_group_display_code = 'Year'
204 and dimension_id=1;
205
206 select dimension_group_id
207 into v_month_dimgrp_id
208 from fem_dimension_grps_b
209 where dimension_group_display_code = 'Month'
210 and dimension_id=1;
211
212 select dimension_group_id
213 into v_qtr_dimgrp_id
214 from fem_dimension_grps_b
215 where dimension_group_display_code = 'Quarter'
216 and dimension_id=1;
217
218 select to_number(to_char(sysdate,'YYYY'))
219 into v_accounting_year
220 from dual;
221
222 select source_system_code, source_system_display_code
223 into v_source_system_code, v_source_system_dc
224 from fem_source_systems_b
225 where source_system_display_code = 'XGL1';
226
227 begin
228 fem_cal_periods_pkg.insert_row(
229 X_ROWID => v_rowid
230 ,X_CAL_PERIOD_ID => v_cal_period_id
231 ,X_OBJECT_VERSION_NUMBER => 1
232 ,X_READ_ONLY_FLAG => 'N'
233 ,X_DIMENSION_GROUP_ID => v_dimension_group_id
234 ,X_CALENDAR_ID => v_calendar_id
235 ,X_ENABLED_FLAG => 'Y'
236 ,X_PERSONAL_FLAG => 'N'
237 ,X_CAL_PERIOD_NAME => to_char(v_end_date,'YYYY/MM/DD')||' Year'
238 ,X_DESCRIPTION => 'Created by Installation'
239 ,X_CREATION_DATE => sysdate
240 ,X_CREATED_BY => v_user_id
241 ,X_LAST_UPDATE_DATE => sysdate
242 ,X_LAST_UPDATED_BY => v_user_id
243 ,X_LAST_UPDATE_LOGIN => null);
244 exception
245 when dup_val_on_index then
246 v_dup_cal_period_flag := 'Y';
247 end;
248
249 insert into fem_cal_periods_attr (
250 ATTRIBUTE_ID
251 ,VERSION_ID
252 ,CAL_PERIOD_ID
253 ,DIM_ATTRIBUTE_NUMERIC_MEMBER
254 ,DIM_ATTRIBUTE_VALUE_SET_ID
255 ,DIM_ATTRIBUTE_VARCHAR_MEMBER
256 ,NUMBER_ASSIGN_VALUE
257 ,VARCHAR_ASSIGN_VALUE
258 ,DATE_ASSIGN_VALUE
259 ,CREATION_DATE
260 ,CREATED_BY
261 ,LAST_UPDATED_BY
262 ,LAST_UPDATE_DATE
263 ,LAST_UPDATE_LOGIN
264 ,OBJECT_VERSION_NUMBER
265 ,AW_SNAPSHOT_FLAG)
266 select A.attribute_id
267 ,V.version_id
268 ,v_cal_period_id
269 ,null
270 ,null
271 ,'N'
272 ,null
273 ,null
274 ,null
275 ,sysdate
276 ,v_user_id
277 ,v_user_id
278 ,sysdate
279 ,null
280 ,1
281 ,'N'
282 from fem_dim_attributes_b A, fem_dim_attr_versions_b V
283 where A.attribute_Varchar_label = 'RECON_LEAF_NODE_FLAG'
284 and A.attribute_id = V.attribute_id
285 and A.dimension_id = 1
286 and V.default_version_flag = 'Y';
287
288
289 insert into fem_cal_periods_attr (
290 ATTRIBUTE_ID
291 ,VERSION_ID
292 ,CAL_PERIOD_ID
293 ,DIM_ATTRIBUTE_NUMERIC_MEMBER
294 ,DIM_ATTRIBUTE_VALUE_SET_ID
295 ,DIM_ATTRIBUTE_VARCHAR_MEMBER
296 ,NUMBER_ASSIGN_VALUE
297 ,VARCHAR_ASSIGN_VALUE
298 ,DATE_ASSIGN_VALUE
299 ,CREATION_DATE
300 ,CREATED_BY
301 ,LAST_UPDATED_BY
302 ,LAST_UPDATE_DATE
303 ,LAST_UPDATE_LOGIN
304 ,OBJECT_VERSION_NUMBER
305 ,AW_SNAPSHOT_FLAG)
306 select A.attribute_id
307 ,V.version_id
308 ,v_cal_period_id
309 ,null
310 ,null
311 ,'N'
312 ,null
313 ,null
314 ,null
315 ,sysdate
316 ,v_user_id
317 ,v_user_id
318 ,sysdate
319 ,null
320 ,1
321 ,'N'
322 from fem_dim_attributes_b A, fem_dim_attr_versions_b V
323 where A.attribute_Varchar_label = 'ADJ_PERIOD_FLAG'
324 and A.attribute_id = V.attribute_id
325 and A.dimension_id = 1
326 and V.default_version_flag = 'Y';
327
328 insert into fem_cal_periods_attr (
329 ATTRIBUTE_ID
330 ,VERSION_ID
331 ,CAL_PERIOD_ID
332 ,DIM_ATTRIBUTE_NUMERIC_MEMBER
333 ,DIM_ATTRIBUTE_VALUE_SET_ID
334 ,DIM_ATTRIBUTE_VARCHAR_MEMBER
335 ,NUMBER_ASSIGN_VALUE
336 ,VARCHAR_ASSIGN_VALUE
337 ,DATE_ASSIGN_VALUE
338 ,CREATION_DATE
339 ,CREATED_BY
340 ,LAST_UPDATED_BY
341 ,LAST_UPDATE_DATE
342 ,LAST_UPDATE_LOGIN
343 ,OBJECT_VERSION_NUMBER
344 ,AW_SNAPSHOT_FLAG)
345 select A.attribute_id
346 ,V.version_id
347 ,v_cal_period_id
348 ,null
349 ,null
350 ,'N'
351 ,null
352 ,null
353 ,null
354 ,sysdate
355 ,v_user_id
356 ,v_user_id
357 ,sysdate
358 ,null
359 ,1
360 ,'N'
361 from fem_dim_attributes_b A, fem_dim_attr_versions_b V
362 where A.attribute_Varchar_label = 'GL_ORIGIN_FLAG'
363 and A.attribute_id = V.attribute_id
364 and A.dimension_id = 1
365 and V.default_version_flag = 'Y';
366
367 insert into fem_cal_periods_attr (
368 ATTRIBUTE_ID
369 ,VERSION_ID
370 ,CAL_PERIOD_ID
371 ,DIM_ATTRIBUTE_NUMERIC_MEMBER
372 ,DIM_ATTRIBUTE_VALUE_SET_ID
373 ,DIM_ATTRIBUTE_VARCHAR_MEMBER
374 ,NUMBER_ASSIGN_VALUE
375 ,VARCHAR_ASSIGN_VALUE
376 ,DATE_ASSIGN_VALUE
377 ,CREATION_DATE
378 ,CREATED_BY
379 ,LAST_UPDATED_BY
380 ,LAST_UPDATE_DATE
381 ,LAST_UPDATE_LOGIN
382 ,OBJECT_VERSION_NUMBER
383 ,AW_SNAPSHOT_FLAG)
384 select A.attribute_id
385 ,V.version_id
386 ,v_cal_period_id
387 ,null
388 ,null
389 ,'N'
390 ,null
391 ,null
392 ,null
393 ,sysdate
394 ,v_user_id
395 ,v_user_id
396 ,sysdate
397 ,null
398 ,1
399 ,'N'
400 from fem_dim_attributes_b A, fem_dim_attr_versions_b V
401 where A.attribute_Varchar_label = 'CUR_PERIOD_FLAG'
402 and A.attribute_id = V.attribute_id
403 and A.dimension_id = 1
404 and V.default_version_flag = 'Y';
405
406 insert into fem_cal_periods_attr (
407 ATTRIBUTE_ID
408 ,VERSION_ID
409 ,CAL_PERIOD_ID
410 ,DIM_ATTRIBUTE_NUMERIC_MEMBER
411 ,DIM_ATTRIBUTE_VALUE_SET_ID
412 ,DIM_ATTRIBUTE_VARCHAR_MEMBER
413 ,NUMBER_ASSIGN_VALUE
414 ,VARCHAR_ASSIGN_VALUE
415 ,DATE_ASSIGN_VALUE
416 ,CREATION_DATE
417 ,CREATED_BY
418 ,LAST_UPDATED_BY
419 ,LAST_UPDATE_DATE
420 ,LAST_UPDATE_LOGIN
421 ,OBJECT_VERSION_NUMBER
422 ,AW_SNAPSHOT_FLAG)
423 select A.attribute_id
424 ,V.version_id
425 ,v_cal_period_id
426 ,null
427 ,null
428 ,null
429 ,v_accounting_year
430 ,null
431 ,null
432 ,sysdate
433 ,v_user_id
434 ,v_user_id
435 ,sysdate
436 ,null
437 ,1
438 ,'N'
439 from fem_dim_attributes_b A, fem_dim_attr_versions_b V
440 where A.attribute_Varchar_label = 'ACCOUNTING_YEAR'
441 and A.attribute_id = V.attribute_id
442 and A.dimension_id = 1
443 and V.default_version_flag = 'Y';
444
445 insert into fem_cal_periods_attr (
446 ATTRIBUTE_ID
447 ,VERSION_ID
448 ,CAL_PERIOD_ID
449 ,DIM_ATTRIBUTE_NUMERIC_MEMBER
450 ,DIM_ATTRIBUTE_VALUE_SET_ID
451 ,DIM_ATTRIBUTE_VARCHAR_MEMBER
452 ,NUMBER_ASSIGN_VALUE
453 ,VARCHAR_ASSIGN_VALUE
454 ,DATE_ASSIGN_VALUE
455 ,CREATION_DATE
456 ,CREATED_BY
457 ,LAST_UPDATED_BY
458 ,LAST_UPDATE_DATE
459 ,LAST_UPDATE_LOGIN
460 ,OBJECT_VERSION_NUMBER
461 ,AW_SNAPSHOT_FLAG)
462 select A.attribute_id
463 ,V.version_id
464 ,v_cal_period_id
465 ,null
466 ,null
467 ,null
468 ,null
469 ,null
470 ,v_start_date
471 ,sysdate
472 ,v_user_id
473 ,v_user_id
474 ,sysdate
475 ,null
476 ,1
477 ,'N'
478 from fem_dim_attributes_b A, fem_dim_attr_versions_b V
479 where A.attribute_Varchar_label = 'CAL_PERIOD_START_DATE'
480 and A.attribute_id = V.attribute_id
481 and A.dimension_id = 1
482 and V.default_version_flag = 'Y';
483
484 insert into fem_cal_periods_attr (
485 ATTRIBUTE_ID
486 ,VERSION_ID
487 ,CAL_PERIOD_ID
488 ,DIM_ATTRIBUTE_NUMERIC_MEMBER
489 ,DIM_ATTRIBUTE_VALUE_SET_ID
490 ,DIM_ATTRIBUTE_VARCHAR_MEMBER
491 ,NUMBER_ASSIGN_VALUE
492 ,VARCHAR_ASSIGN_VALUE
493 ,DATE_ASSIGN_VALUE
494 ,CREATION_DATE
495 ,CREATED_BY
496 ,LAST_UPDATED_BY
497 ,LAST_UPDATE_DATE
498 ,LAST_UPDATE_LOGIN
499 ,OBJECT_VERSION_NUMBER
500 ,AW_SNAPSHOT_FLAG)
501 select A.attribute_id
502 ,V.version_id
503 ,v_cal_period_id
504 ,null
505 ,null
506 ,null
507 ,null
508 ,null
509 ,v_end_date
510 ,sysdate
511 ,v_user_id
512 ,v_user_id
513 ,sysdate
514 ,null
515 ,1
516 ,'N'
517 from fem_dim_attributes_b A, fem_dim_attr_versions_b V
518 where A.attribute_Varchar_label = 'CAL_PERIOD_END_DATE'
519 and A.attribute_id = V.attribute_id
520 and A.dimension_id = 1
521 and V.default_version_flag = 'Y';
522
523 insert into fem_cal_periods_attr (
524 ATTRIBUTE_ID
525 ,VERSION_ID
526 ,CAL_PERIOD_ID
527 ,DIM_ATTRIBUTE_NUMERIC_MEMBER
528 ,DIM_ATTRIBUTE_VALUE_SET_ID
529 ,DIM_ATTRIBUTE_VARCHAR_MEMBER
530 ,NUMBER_ASSIGN_VALUE
531 ,VARCHAR_ASSIGN_VALUE
532 ,DATE_ASSIGN_VALUE
533 ,CREATION_DATE
534 ,CREATED_BY
535 ,LAST_UPDATED_BY
536 ,LAST_UPDATE_DATE
537 ,LAST_UPDATE_LOGIN
538 ,OBJECT_VERSION_NUMBER
539 ,AW_SNAPSHOT_FLAG)
540 select A.attribute_id
541 ,V.version_id
542 ,v_cal_period_id
543 ,null
544 ,null
545 ,null
546 ,c_cal_period_number
547 ,null
548 ,null
549 ,sysdate
550 ,v_user_id
551 ,v_user_id
552 ,sysdate
553 ,null
554 ,1
555 ,'N'
556 from fem_dim_attributes_b A, fem_dim_attr_versions_b V
557 where A.attribute_Varchar_label = 'GL_PERIOD_NUM'
558 and A.attribute_id = V.attribute_id
559 and A.dimension_id = 1
560 and V.default_version_flag = 'Y';
561
562 insert into fem_cal_periods_attr (
563 ATTRIBUTE_ID
564 ,VERSION_ID
565 ,CAL_PERIOD_ID
566 ,DIM_ATTRIBUTE_NUMERIC_MEMBER
567 ,DIM_ATTRIBUTE_VALUE_SET_ID
568 ,DIM_ATTRIBUTE_VARCHAR_MEMBER
569 ,NUMBER_ASSIGN_VALUE
570 ,VARCHAR_ASSIGN_VALUE
571 ,DATE_ASSIGN_VALUE
572 ,CREATION_DATE
573 ,CREATED_BY
574 ,LAST_UPDATED_BY
575 ,LAST_UPDATE_DATE
576 ,LAST_UPDATE_LOGIN
577 ,OBJECT_VERSION_NUMBER
578 ,AW_SNAPSHOT_FLAG)
579 select A.attribute_id
580 ,V.version_id
581 ,v_cal_period_id
582 ,v_source_system_code
583 ,null
584 ,null
585 ,null
586 ,null
587 ,null
588 ,sysdate
589 ,v_user_id
590 ,v_user_id
591 ,sysdate
592 ,null
593 ,1
594 ,'N'
595 from fem_dim_attributes_b A, fem_dim_attr_versions_b V
596 where A.attribute_Varchar_label = 'SOURCE_SYSTEM_CODE'
597 and A.attribute_id = V.attribute_id
598 and A.dimension_id = 1
599 and V.default_version_flag = 'Y';
600
601 insert into fem_cal_periods_attr (
602 ATTRIBUTE_ID
603 ,VERSION_ID
604 ,CAL_PERIOD_ID
605 ,DIM_ATTRIBUTE_NUMERIC_MEMBER
606 ,DIM_ATTRIBUTE_VALUE_SET_ID
607 ,DIM_ATTRIBUTE_VARCHAR_MEMBER
608 ,NUMBER_ASSIGN_VALUE
609 ,VARCHAR_ASSIGN_VALUE
610 ,DATE_ASSIGN_VALUE
611 ,CREATION_DATE
612 ,CREATED_BY
613 ,LAST_UPDATED_BY
614 ,LAST_UPDATE_DATE
615 ,LAST_UPDATE_LOGIN
616 ,OBJECT_VERSION_NUMBER
617 ,AW_SNAPSHOT_FLAG)
618 select A.attribute_id
619 ,V.version_id
620 ,v_cal_period_id
621 ,null
622 ,null
623 ,null
624 ,null
625 ,'Year'
626 ,null
627 ,sysdate
628 ,v_user_id
629 ,v_user_id
630 ,sysdate
631 ,null
632 ,1
633 ,'N'
634 from fem_dim_attributes_b A, fem_dim_attr_versions_b V
635 where A.attribute_Varchar_label = 'CAL_PERIOD_PREFIX'
636 and A.attribute_id = V.attribute_id
637 and A.dimension_id = 1
638 and V.default_version_flag = 'Y';
639
640
641 -- Create the Hierarchy Object
642 fem_object_catalog_util_pkg.create_object (x_object_id => v_object_id,
643 x_object_definition_id => v_object_definition_id,
644 x_msg_count => v_msg_count,
645 x_msg_data => v_msg_data,
646 x_return_status => v_return_status,
647 p_api_version => 1,
648 p_commit => FND_API.G_FALSE,
649 p_object_type_code => 'HIERARCHY',
650 p_folder_id => 1100,
651 p_local_vs_combo_id => null,
652 p_object_access_code => 'W',
653 p_object_origin_code => 'SEEDED',
654 p_object_name => 'Seeded Calendar Period Hierarchy',
655 p_description => 'Created by Installation',
656 p_obj_def_name => 'Seeded Calendar Period Hierarchy');
657
658 insert into fem_hierarchies (
659 HIERARCHY_OBJ_ID
660 ,DIMENSION_ID
661 ,HIERARCHY_TYPE_CODE
662 ,GROUP_SEQUENCE_ENFORCED_CODE
663 ,MULTI_TOP_FLAG
664 ,FINANCIAL_CATEGORY_FLAG
665 ,VALUE_SET_ID
666 ,CALENDAR_ID
667 ,PERIOD_TYPE
668 ,PERSONAL_FLAG
669 ,FLATTENED_ROWS_FLAG
670 ,CREATION_DATE
671 ,CREATED_BY
672 ,LAST_UPDATED_BY
673 ,LAST_UPDATE_DATE
674 ,LAST_UPDATE_LOGIN
675 ,HIERARCHY_USAGE_CODE
676 ,MULTI_VALUE_SET_FLAG
677 ,OBJECT_VERSION_NUMBER)
678 values (v_object_id
679 ,1
680 ,'OPEN'
681 ,'SEQUENCE_ENFORCED'
682 ,'Y'
683 ,'N'
684 ,null
685 ,v_calendar_id
686 ,null
687 ,'N'
688 ,'N'
689 ,sysdate
690 ,v_user_id
691 ,v_user_id
692 ,sysdate
693 ,null
694 ,'STANDARD'
695 ,'N'
696 ,1);
697
698 insert into fem_hier_value_sets
699 ( HIERARCHY_OBJ_ID,
700 VALUE_SET_ID,
701 CREATION_DATE,
702 CREATED_BY,
703 LAST_UPDATED_BY,
704 LAST_UPDATE_DATE,
705 LAST_UPDATE_LOGIN,
706 OBJECT_VERSION_NUMBER)
707 values (v_object_id, v_calendar_id,sysdate,v_user_id,v_user_id,sysdate,null,1);
708
709 insert into fem_hier_definitions (
710 HIERARCHY_OBJ_DEF_ID
711 ,CREATION_DATE
712 ,CREATED_BY
713 ,LAST_UPDATED_BY
714 ,LAST_UPDATE_DATE
715 ,LAST_UPDATE_LOGIN
716 ,OBJECT_VERSION_NUMBER
717 ,FLATTENED_ROWS_COMPLETION_CODE )
718 values (v_object_definition_id
719 ,sysdate
720 ,v_user_id
721 ,v_user_id
722 ,sysdate
723 ,null
724 ,1
725 ,'COMPLETED');
726
727
728
729 insert into fem_hier_dimension_grps(
730 DIMENSION_GROUP_ID
731 ,HIERARCHY_OBJ_ID
732 ,RELATIVE_DIMENSION_GROUP_SEQ
733 ,CREATION_DATE
734 ,CREATED_BY
735 ,LAST_UPDATED_BY
736 ,LAST_UPDATE_DATE
737 ,LAST_UPDATE_LOGIN
738 ,OBJECT_VERSION_NUMBER)
739 values (v_dimension_group_id
740 ,v_object_id
741 ,1
742 ,sysdate
743 ,v_user_id
744 ,v_user_id
745 ,sysdate
746 ,null
747 ,1);
748
749 insert into fem_hier_dimension_grps(
750 DIMENSION_GROUP_ID
751 ,HIERARCHY_OBJ_ID
752 ,RELATIVE_DIMENSION_GROUP_SEQ
753 ,CREATION_DATE
754 ,CREATED_BY
755 ,LAST_UPDATED_BY
756 ,LAST_UPDATE_DATE
757 ,LAST_UPDATE_LOGIN
758 ,OBJECT_VERSION_NUMBER)
759 values (v_month_dimgrp_id
760 ,v_object_id
761 ,40
762 ,sysdate
763 ,v_user_id
764 ,v_user_id
765 ,sysdate
766 ,null
767 ,1);
768
769 insert into fem_hier_dimension_grps(
770 DIMENSION_GROUP_ID
771 ,HIERARCHY_OBJ_ID
772 ,RELATIVE_DIMENSION_GROUP_SEQ
773 ,CREATION_DATE
774 ,CREATED_BY
775 ,LAST_UPDATED_BY
776 ,LAST_UPDATE_DATE
777 ,LAST_UPDATE_LOGIN
778 ,OBJECT_VERSION_NUMBER)
779 values (v_qtr_dimgrp_id
780 ,v_object_id
781 ,20
782 ,sysdate
783 ,v_user_id
784 ,v_user_id
785 ,sysdate
786 ,null
787 ,1);
788
789 insert into fem_cal_periods_hier (
790 HIERARCHY_OBJ_DEF_ID
791 ,PARENT_DEPTH_NUM
792 ,PARENT_ID
793 ,CHILD_DEPTH_NUM
794 ,CHILD_ID
795 ,SINGLE_DEPTH_FLAG
796 ,DISPLAY_ORDER_NUM
797 ,WEIGHTING_PCT
798 ,CREATION_DATE
799 ,CREATED_BY
800 ,LAST_UPDATED_BY
801 ,LAST_UPDATE_DATE
802 ,LAST_UPDATE_LOGIN
803 ,OBJECT_VERSION_NUMBER)
804 values (v_object_definition_id
805 ,1
806 ,v_cal_period_id
807 ,1
808 ,v_cal_period_id
809 ,'Y'
810 ,1
811 ,null
812 ,sysdate
813 ,v_user_id
814 ,v_user_id
815 ,sysdate
816 ,null
817 ,1);
818
819 fem_dimension_util_pkg.new_ledger (
820 X_RETURN_STATUS => v_return_status
821 ,X_MSG_COUNT => v_msg_count
822 ,X_MSG_DATA => v_msg_data
823 ,P_DISPLAY_CODE => 'DEFAULT_LEDGER'
824 ,P_LEDGER_NAME => 'Default Ledger'
825 ,P_FUNC_CURR_CD => 'USD'
826 ,P_SOURCE_CD => v_source_system_code
827 ,P_CAL_PER_HID => v_object_definition_id
828 ,P_GLOBAL_VS_ID => 1
829 ,P_EPB_DEF_LG_FLG => 'Y'
830 ,P_ENT_CURR_FLG => 'Y'
831 ,P_AVG_BAL_FLG => 'Y'
832 ,P_CHAN_FLG => 'Y'
833 ,P_CCTR_FLG => 'Y'
834 ,P_CUST_FLG => 'Y'
835 ,P_GEOG_FLG => 'Y'
836 ,P_LN_ITEM_FLG => 'Y'
837 ,P_NAT_ACCT_FLG => 'Y'
838 ,P_PROD_FLG => 'Y'
839 ,P_PROJ_FLG => 'Y'
840 ,P_USER1_FLG => 'Y'
841 ,P_USER2_FLG => 'Y'
842 ,P_USER3_FLG => 'Y'
843 ,P_USER4_FLG => 'Y'
844 ,P_USER5_FLG => 'Y'
845 ,P_USER6_FLG => 'Y'
846 ,P_USER7_FLG => 'Y'
847 ,P_USER8_FLG => 'Y'
848 ,P_USER9_FLG => 'Y'
849 ,P_USER10_FLG => 'Y'
850 ,P_ENTITY_FLG => 'Y'
851 ,P_VER_NAME => 'Default'
852 ,P_VER_DISP_CD => 'Default'
853 ,P_LEDGER_DESC => 'Created by Installation');
854
855 BEGIN
856 select ledger_id
857 into v_verify_ledger_id
858 from fem_ledgers_b
859 where ledger_display_code='DEFAULT_LEDGER';
860
861 EXCEPTION
862 when no_data_found then raise e_no_def_ledger;
863 END;
864
865 commit;
866
867 END IF;
868
869 EXCEPTION
870 when dup_val_on_index then
871 null;
872 END; -- Create default Cal Period and Ledger
873
874
875 BEGIN
876
877 -- Bug 4740353. Set all missing default budget attribute assignments.
878 -- Only process if at least one of the following attributes is null:
879 -- BUDGET_LEDGER, BUDGET_FIRST_PERIOD, BUDGET_LAST_PERIOD,
880 -- BUDGET_LATEST_OPEN_YEAR
881
882 -- Get BUDGET dimension id
883 SELECT dimension_id
884 INTO v_dim_id
885 FROM fem_dimensions_b
886 WHERE dimension_varchar_label = 'BUDGET';
887
888 SELECT count(*)
889 INTO v_count
890 FROM fem_dim_attributes_b
891 WHERE dimension_id = v_dim_id
892 AND attribute_varchar_label IN ('BUDGET_LEDGER','BUDGET_LEDGER',
893 'BUDGET_FIRST_PERIOD','BUDGET_LAST_PERIOD','BUDGET_LATEST_OPEN_YEAR')
894 AND default_assignment IS NULL;
895
896 IF v_count > 0 THEN
897 -- Assign newly created 'DEFAULT_LEDGER' as the default assignment for the
898 -- BUDGET_LEDGER attribute. If this is an update, find the earliest
899 -- created ledger with calendar periods created associated with it.
900 IF v_ledger_id IS NULL THEN
901 -- First find attrib and version id for CAL_PERIOD_HIER_OBJ_DEF_ID attrib
902 SELECT a.attribute_id, v.version_id
903 INTO v_cal_hier_attribute_id, v_cal_hier_version_id
904 FROM fem_dim_attributes_b a, fem_dim_attr_versions_b v
905 WHERE a.attribute_varchar_label = 'CAL_PERIOD_HIER_OBJ_DEF_ID'
906 AND v.attribute_id = a.attribute_id
907 AND v.default_version_flag = 'Y'
908 AND a.dimension_id =
909 (SELECT dimension_id
910 FROM fem_dimensions_b
911 WHERE dimension_varchar_label = 'LEDGER');
912
913 -- Then find the ledger
914 SELECT min(ledger_id)
915 INTO v_ledger_id
916 FROM fem_ledgers_b
917 WHERE enabled_flag = 'Y'
918 AND ledger_id IN
919 (SELECT ledger_id
920 FROM fem_ledgers_attr
921 WHERE dim_attribute_numeric_member IN
922 (SELECT object_definition_id
923 FROM fem_object_definition_b
924 WHERE object_id IN
925 (SELECT hierarchy_obj_id
926 FROM fem_hierarchies
927 WHERE calendar_id IN
928 (SELECT calendar_id
929 FROM fem_cal_periods_b
930 WHERE enabled_flag = 'Y')))
931 AND attribute_id = v_cal_hier_attribute_id
932 AND version_id = v_cal_hier_version_id);
933 END IF;
934
935 UPDATE fem_dim_attributes_b
936 SET default_assignment = to_char(v_ledger_id)
937 WHERE dimension_id = v_dim_id
938 AND attribute_varchar_label = 'BUDGET_LEDGER';
939
940 -- If the default calendar period does not exist, find the earliest
941 -- created calendar period that belongs to the same calendar as the
942 -- BUDGET_LEDGER.
943 IF v_cal_period_id IS NULL THEN
944 SELECT min(cal_period_id) KEEP (DENSE_RANK FIRST ORDER BY creation_date)
945 INTO v_cal_period_id
946 FROM fem_cal_periods_b
947 WHERE enabled_flag = 'Y'
948 AND calendar_id IN
949 (SELECT calendar_id
950 FROM fem_hierarchies
951 WHERE hierarchy_obj_id IN
952 (SELECT object_id
953 FROM fem_object_definition_b
954 WHERE object_definition_id IN
955 (SELECT dim_attribute_numeric_member
956 FROM fem_ledgers_attr
957 WHERE ledger_id = v_ledger_id
958 AND attribute_id = v_cal_hier_attribute_id
959 AND version_id = v_cal_hier_version_id)));
960 END IF;
961
962 UPDATE fem_dim_attributes_b
963 SET default_assignment = to_char(v_cal_period_id)
964 WHERE dimension_id = v_dim_id
965 AND attribute_varchar_label IN ('BUDGET_FIRST_PERIOD','BUDGET_LAST_PERIOD');
966 -- Update default assignment for the BUDGET_LATEST_OPEN_YEAR as the
967 -- accounting year of the BUDGET_LAST_PERIOD.
968 IF v_accounting_year IS NULL THEN
969 SELECT C.number_assign_value
970 INTO v_accounting_year
971 FROM fem_cal_periods_attr C, fem_dim_attributes_b A, fem_dim_attr_versions_b V
972 WHERE C.cal_period_id = v_cal_period_id
973 AND A.attribute_varchar_label = 'ACCOUNTING_YEAR'
974 AND C.attribute_id = V.attribute_id
975 AND C.version_id = V.version_id
976 AND A.attribute_id = V.attribute_id
977 AND V.default_version_flag = 'Y'
978 AND A.dimension_id IN
979 (SELECT dimension_id
980 FROM fem_dimensions_b
981 WHERE dimension_varchar_label = 'CAL_PERIOD');
982 END IF;
983
984 UPDATE fem_dim_attributes_b
985 SET default_assignment = to_char(v_accounting_year)
986 WHERE dimension_id = v_dim_id
987 AND attribute_varchar_label = 'BUDGET_LATEST_OPEN_YEAR';
988 END IF;
989
990 EXCEPTION WHEN others THEN null;
991 END;
992
993
994 -- Default FEM_LEDGER profile option at the site level to 'DEFAULT_LEDGER'
995 BEGIN
996 SELECT ledger_id
997 INTO v_ledger_id
998 FROM fem_ledgers_b
999 WHERE ledger_display_code = 'DEFAULT_LEDGER';
1000
1001 IF v_ledger_id IS NOT NULL AND FND_PROFILE.value('FEM_LEDGER') IS NULL THEN
1002 v_boolean := FND_PROFILE.save('FEM_LEDGER', v_ledger_id, 'SITE');
1003
1004 COMMIT;
1005 END IF;
1006 EXCEPTION WHEN others THEN null;
1007 END;
1008
1009 /*********************************************************************
1010 Bug#5237422 - Commenting out because we now create the
1011 OGL_SOURCE_LEDGER_GROUP using fem_srcledgers.lct and fem_srcledgers.ldt
1012
1013 -- Bug 3695254: Create OGL_SOURCE_LEDGER_GROUP
1014 BEGIN
1015 v_ledger_id := null;
1016
1017 -- If OGL_SOURCE_LEDGER_GROUP is not found, we need to create it.
1018 BEGIN
1019 SELECT ledger_id
1020 INTO v_ledger_id
1021 FROM fem_ledgers_b
1022 WHERE ledger_display_code = 'OGL_SOURCE_LEDGER_GROUP';
1023 EXCEPTION
1024 WHEN no_data_found THEN
1025
1026 -- Get the first Cal Period hierarchy created, which
1027 -- presumably would be the default one we seeded.
1028 SELECT MIN(h.hierarchy_obj_id)
1029 INTO v_object_id
1030 FROM fem_hierarchies h, fem_dimensions_b d
1031 WHERE d.dimension_varchar_label = 'CAL_PERIOD'
1032 AND h.dimension_id = d.dimension_id;
1033
1034 SELECT MIN(object_definition_id)
1035 INTO v_object_definition_id
1036 FROM fem_object_definition_b
1037 WHERE object_id = v_object_id;
1038
1039 select source_system_code,source_system_display_code
1040 into v_ofa_source_sys_code,v_ofa_source_sys_dc
1041 from fem_source_systems_b
1042 where source_system_display_code = 'OFA';
1043
1044 fem_dimension_util_pkg.new_ledger (
1045 X_RETURN_STATUS => v_return_status
1046 ,X_MSG_COUNT => v_msg_count
1047 ,X_MSG_DATA => v_msg_data
1048 ,P_DISPLAY_CODE => 'OGL_SOURCE_LEDGER_GROUP'
1049 ,P_LEDGER_NAME => 'Oracle General Ledger Source Ledger Group'
1050 ,P_FUNC_CURR_CD => 'USD'
1051 ,P_SOURCE_CD => v_ofa_source_sys_code
1052 ,P_CAL_PER_HID => v_object_definition_id
1053 ,P_GLOBAL_VS_ID => 1
1054 ,P_EPB_DEF_LG_FLG => 'N'
1055 ,P_ENT_CURR_FLG => 'Y'
1056 ,P_AVG_BAL_FLG => 'Y'
1057 ,P_CHAN_FLG => 'Y'
1058 ,P_CCTR_FLG => 'Y'
1059 ,P_CUST_FLG => 'Y'
1060 ,P_GEOG_FLG => 'Y'
1061 ,P_LN_ITEM_FLG => 'Y'
1062 ,P_NAT_ACCT_FLG => 'Y'
1063 ,P_PROD_FLG => 'Y'
1064 ,P_PROJ_FLG => 'Y'
1065 ,P_USER1_FLG => 'Y'
1066 ,P_USER2_FLG => 'Y'
1067 ,P_USER3_FLG => 'Y'
1068 ,P_USER4_FLG => 'Y'
1069 ,P_USER5_FLG => 'Y'
1070 ,P_USER6_FLG => 'Y'
1071 ,P_USER7_FLG => 'Y'
1072 ,P_USER8_FLG => 'Y'
1073 ,P_USER9_FLG => 'Y'
1074 ,P_USER10_FLG => 'Y'
1075 ,P_ENTITY_FLG => 'Y'
1076 ,P_VER_NAME => 'Default'
1077 ,P_VER_DISP_CD => 'Default'
1078 ,P_LEDGER_DESC => 'Oracle General Ledger Source Ledger Group');
1079
1080 -- The New_Ledger API creates ledgers as enabled and not read only.
1081 -- SLG should not be enabled and should be read only.
1082 UPDATE fem_ledgers_b
1083 SET ENABLED_FLAG = 'N', READ_ONLY_FLAG = 'Y'
1084 WHERE ledger_display_code = 'OGL_SOURCE_LEDGER_GROUP';
1085
1086 -- Get Source Ledger Group ledger ID to populate FEM_LEDGER_HIER
1087 SELECT ledger_id
1088 INTO v_ledger_id
1089 FROM fem_ledgers_b
1090 WHERE ledger_display_code = 'OGL_SOURCE_LEDGER_GROUP';
1091
1092
1093 -- Just make sure it exists as it should have been seeded
1094 -- by fem_objects.ldt in an earlier phase of the install.
1095 SELECT object_definition_id
1096 INTO v_object_definition_id
1097 FROM fem_object_definition_b
1098 WHERE object_definition_id = 1505;
1099
1100 INSERT INTO fem_ledgers_hier
1101 (HIERARCHY_OBJ_DEF_ID,
1102 PARENT_DEPTH_NUM,
1103 PARENT_ID,
1104 CHILD_DEPTH_NUM,
1105 CHILD_ID,
1106 SINGLE_DEPTH_FLAG,
1107 DISPLAY_ORDER_NUM,
1108 WEIGHTING_PCT,
1109 CREATION_DATE,
1110 CREATED_BY,
1111 LAST_UPDATED_BY,
1112 LAST_UPDATE_DATE,
1113 LAST_UPDATE_LOGIN,
1114 OBJECT_VERSION_NUMBER)
1115 VALUES
1116 (v_object_definition_id,
1117 1,
1118 v_ledger_id,
1119 1,
1120 v_ledger_id,
1121 'Y',
1122 1,
1123 NULL,
1124 SYSDATE,
1125 1,
1126 1,
1127 SYSDATE,
1128 0,
1129 1);
1130
1131 COMMIT;
1132 END; -- query if OGL_SOURCE_LEDGER_GROUP exists
1133
1134 EXCEPTION
1135 when no_data_found then raise e_no_def_ledger;
1136
1137 END; -- Bug 3695254
1138 ******************************************************/
1139
1140
1141 -- Bug#5201184 Update fem_dim_attributes_b.default_assignment
1142 -- For the BUSINESS_RELATIONSHIP attribute
1143 BEGIN
1144
1145 SELECT bus_rel_id
1146 INTO v_bus_rel_mbr_id
1147 FROM fem_bus_rel_b
1148 WHERE bus_rel_display_code = 'INDIVIDUAL_CUSTOMER';
1149
1150 SELECT dimension_id
1151 INTO v_customer_dim_id
1152 FROM fem_dimensions_b
1153 WHERE dimension_varchar_label = 'CUSTOMER';
1154
1155 SELECT attribute_id
1156 INTO v_bus_rel_attr_id
1157 FROM fem_dim_attributes_b
1158 WHERE dimension_id = v_customer_dim_id
1159 AND attribute_varchar_label = 'BUSINESS_RELATIONSHIP';
1160
1161 UPDATE fem_dim_attributes_b
1162 SET default_assignment = v_bus_rel_mbr_id
1163 WHERE attribute_id = v_bus_rel_attr_id
1164 AND default_assignment IS NULL;
1165
1166
1167 EXCEPTION
1168 WHEN OTHERS THEN RAISE e_bus_rel_error;
1169
1170 END; -- bug#5201184
1171
1172 x_status := 'SUCCESS';
1173
1174 EXCEPTION
1175 WHEN e_no_def_ledger THEN x_status := 'ERROR';
1176 FEM_ENGINES_PKG.USER_MESSAGE
1177 (P_APP_NAME => c_fem
1178 ,P_MSG_NAME => 'FEM_GDFT_NO_DEFLEDGER');
1179
1180
1181 WHEN e_bus_rel_error THEN x_status := 'ERROR';
1182 FEM_ENGINES_PKG.USER_MESSAGE
1183 (P_APP_NAME => c_fem
1184 ,P_MSG_NAME => 'FEM_GDFT_BUS_REL_ERROR');
1185
1186
1187
1188 END main;
1189
1190 END fem_defcalp_util_pkg;