[Home] [Help]
PACKAGE BODY: APPS.FEM_INTG_CAL_RULE_ENG_PKG
Source
1 PACKAGE BODY FEM_INTG_CAL_RULE_ENG_PKG AS
2 /* $Header: fem_intg_cal_eng.plb 120.15 2008/04/01 06:59:28 rguerrer ship $ */
3
4 --
5 -- Package variables
6 --
7 pv_folder_id NUMBER;
8 pv_cal_rule_obj_id NUMBER;
9 pv_cal_rule_obj_def_id NUMBER;
10
11 pv_req_id NUMBER;
12 pv_user_id NUMBER;
13 pv_resp_id NUMBER;
14 pv_login_id NUMBER;
15 pv_pgm_id NUMBER;
16 pv_pgm_app_id NUMBER;
17
18 pv_effective_start_date DATE;
19 pv_effective_end_date DATE;
20
21 pv_period_set_name VARCHAR2(15);
22 pv_period_type VARCHAR2(15);
23 pv_period_type_name VARCHAR2(15);
24 pv_period_year NUMBER;
25
26 pv_effective_period_num_low NUMBER;
27 pv_effective_period_num_high NUMBER;
28
29 pv_ogl_period_type_code VARCHAR(50);
30 pv_ogl_period_type_name VARCHAR(50);
31 pv_ogl_period_type_desc VARCHAR(300);
32 pv_ogl_number_per_fiscal_year NUMBER;
33
34 pv_cal_per_hier_obj_id NUMBER;
35 pv_cal_per_hier_obj_def_id NUMBER;
36 pv_dimension_grp_id_period NUMBER;
37 pv_calendar_id NUMBER;
38 pv_effective_period_num_min NUMBER;
39 pv_effective_period_num_max NUMBER;
40
41 pv_new_periods_to_process VARCHAR2(1);
42
43 pv_ledger_dim_id NUMBER;
44 pv_calendar_dim_id NUMBER;
45 pv_cal_period_dim_id NUMBER;
46 pv_time_group_type_dim_id NUMBER;
47 pv_dimension_grp_id_quarter NUMBER;
48 pv_dimension_grp_id_year NUMBER;
49 pv_source_system_code NUMBER;
50
51 pv_row_count_tot NUMBER;
52
53 pv_completion_status VARCHAR2(15);
54 pv_cal_per_hier_name VARCHAR2(100);
55
56 --
57 -- Constants
58 --
59 pc_module_name CONSTANT VARCHAR2(100):='fem.plsql.' ||
60 'fem_intg_cal_rule_eng_pkg.';
61
62 pc_object_version_number CONSTANT NUMBER := 1;
63 pc_aw_snapshot_flag CONSTANT VARCHAR2(1) := 'N';
64 pc_weighting_pct CONSTANT NUMBER := NULL;
65 pc_period_date_past CONSTANT DATE:= TO_DATE('1000/01/01','YYYY/MM/DD');
66 pc_period_date_future CONSTANT DATE:= TO_DATE('3000/01/01','YYYY/MM/DD');
67 pc_effective_start_date CONSTANT DATE:= TO_DATE('1900/01/01','YYYY/MM/DD');
68 pc_effective_end_date CONSTANT DATE:= TO_DATE('2500/01/01','YYYY/MM/DD');
69
70 pc_adjustment_period_flag CONSTANT VARCHAR2(1) := 'Y';
71
72 pc_object_access_code CONSTANT VARCHAR2(30) := 'W';
73 pc_object_origin_code CONSTANT VARCHAR2(30) := 'USER';
74 pc_grp_seq_code CONSTANT VARCHAR2(30) := 'SEQUENCE_ENFORCED';
75 pc_multi_top_flg CONSTANT VARCHAR2(1) := 'Y';
76
77 pc_source_cd CONSTANT VARCHAR2(15) := 'OGL';
78 pc_ver_name CONSTANT VARCHAR2(15) := 'Default';
79 pc_ver_disp_cd CONSTANT VARCHAR2(15) := 'Default';
80
81 pc_log_level_statement CONSTANT NUMBER := FND_LOG.level_statement;
82 pc_log_level_procedure CONSTANT NUMBER := FND_LOG.level_procedure;
83 pc_log_level_event CONSTANT NUMBER := FND_LOG.level_event;
84 pc_log_level_exception CONSTANT NUMBER := FND_LOG.level_exception;
85 pc_log_level_error CONSTANT NUMBER := FND_LOG.level_error;
86 pc_log_level_unexpected CONSTANT NUMBER := FND_LOG.level_unexpected;
87
88 pc_success CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
89
90
91 -- ======================================================================
92 -- Procedure
93 -- Init
94 -- Purpose
95 -- Initializes package level variables
96 -- History
97 -- 02-02-05 Shintaro Okuda Created
98 -- Arguments
99 -- p_cal_rule_obj_def_id Calendar Rule Object Definition ID
100 -- p_period_set_name Period Set Name
101 -- p_period_type Period Type
102 -- p_period_year Period Year
103 -- ======================================================================
104 PROCEDURE Init(
105 p_cal_rule_obj_def_id IN NUMBER,
106 p_period_set_name IN VARCHAR2,
107 p_period_type IN VARCHAR2,
108 p_period_year IN NUMBER
109 ) IS
110 FEM_INTG_fatal_err EXCEPTION;
111
112 v_rowcount NUMBER;
113
114 v_nls_date_format VARCHAR2(50);
115 v_effective_start_char VARCHAR2(50);
116 v_effective_end_char VARCHAR2(50);
117
118 v_calendar_id NUMBER;
119 v_dimension_grp_id_period NUMBER;
120 v_cal_per_hier_obj_id NUMBER;
121
122 v_ogl_product_name VARCHAR2(100);
123 v_ogl_number_per_fiscal_year NUMBER;
124
125 v_effective_period_num_min NUMBER;
126 v_effective_period_num_max NUMBER;
127 v_period_num_min NUMBER;
128 v_period_num_max NUMBER;
129
130 v_gap_found VARCHAR2(1) := 'N';
131
132 BEGIN
133
134 FEM_ENGINES_PKG.Tech_Message(
135 p_severity => pc_log_level_procedure,
136 p_module => pc_module_name || 'init.begin',
137 p_app_name => 'FEM',
138 p_msg_name => 'FEM_GL_POST_201',
139 p_token1 => 'FUNC_NAME',
140 p_value1 => 'FEM_INTG_CAL_RULE_ENG_PKG.Init',
141 p_token2 => 'TIME',
142 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
143 );
144
145 --
146 -- Get a rule related information
147 --
148 SELECT
149 C.FOLDER_ID,
150 C.OBJECT_ID,
151 D.OBJECT_DEFINITION_ID
152 INTO
153 pv_folder_id,
154 pv_cal_rule_obj_id,
155 pv_cal_rule_obj_def_id
156 FROM
157 FEM_OBJECT_DEFINITION_B D,
158 FEM_OBJECT_CATALOG_B C
159 WHERE
160 D.OBJECT_DEFINITION_ID = p_cal_rule_obj_def_id AND
161 C.OBJECT_ID = D.OBJECT_ID AND
162 C.OBJECT_TYPE_CODE='OGL_INTG_CAL_RULE';
163
164 --
165 -- Initialize Concurrent Program related package variables
166 --
167 pv_req_id := NVL(FND_GLOBAL.CONC_REQUEST_ID,-1);
168 pv_user_id := NVL(FND_GLOBAL.USER_ID,'-1');
169 pv_resp_id := NVL(FND_GLOBAL.RESP_ID, '-1');
170 pv_login_id := NVL(FND_GLOBAL.CONC_LOGIN_ID, FND_GLOBAL.LOGIN_ID);
171 pv_pgm_id := NVL(FND_GLOBAL.CONC_PROGRAM_ID,-1);
172 pv_pgm_app_id := NVL(FND_GLOBAL.PROG_APPL_ID,-1);
173
174 --
175 -- Check user's folder assignment
176 --
177 -- Note that this check is not applicable for FEM.C
178 --
179 -- REMOVED CODE
180
181 --
182 -- Get effective dates from profile options
183 --
184 v_effective_start_char := FND_PROFILE.Value_Specific(
185 'FEM_EFFECTIVE_START_DATE',
186 pv_user_id,
187 pv_resp_id,
188 pv_pgm_app_id
189 );
190 v_effective_end_char := FND_PROFILE.Value_Specific(
191 'FEM_EFFECTIVE_END_DATE',
192 pv_user_id,
193 pv_resp_id,
194 pv_pgm_app_id
195 );
196
197 --
198 -- Initialize completion status
199 --
200 pv_completion_status := 'NORMAL';
201
202 /*
203 In FEM.C, date format validation is not performed in the Define
204 Profile Option value Screen. See bug4141148 for details.
205
206 In FEM.D, date format validation with a hard-coded 'DD-MON-YYYY'
207 is performed in the Define Profile Option Value screen.
208 See bug4141148 for details.
209
210 In FEM.E, the Effective Date profile options are planned to be
211 migrated to regular fields which are validated with ICX_DATE_FORMAT_MASK
212 profile option. See bug4378378 for details.
213
214 Effective Date profile options are now stored in the Canonical format.
215 See bug 5470448 for details.
216 */
217
218 BEGIN
219 pv_effective_start_date :=
220 FND_DATE.Canonical_To_Date(v_effective_start_char);
221 pv_effective_end_date :=
222 FND_DATE.Canonical_To_Date(v_effective_end_char);
223 EXCEPTION
224 WHEN OTHERS THEN
225
226 pv_completion_status := 'WARNING';
227
228 pv_effective_start_date := pc_effective_start_date;
229 pv_effective_end_date := pc_effective_end_date;
230
231 FEM_ENGINES_PKG.Tech_Message(
232 p_severity => pc_log_level_error,
233 p_module => pc_module_name || 'init.invalid_dt_fmt',
234 p_app_name => 'FEM',
235 p_msg_name => 'FEM_INTG_CAL_INVALID_DT_FMT'
236 );
237 FEM_ENGINES_PKG.User_Message(
238 p_app_name => 'FEM',
239 p_msg_name => 'FEM_INTG_CAL_INVALID_DT_FMT'
240 );
241 END;
242
243 --
244 -- Check if mandatory parameters are passed
245 --
246 IF p_cal_rule_obj_def_id IS NULL OR
247 p_period_set_name IS NULL OR
248 p_period_type IS NULL OR
249 p_period_year IS NULL THEN
250
251 FEM_ENGINES_PKG.Tech_Message(
252 p_severity => pc_log_level_error,
253 p_module => pc_module_name || 'init.cal_para_not_found',
254 p_app_name => 'FEM',
255 p_msg_name => 'FEM_INTG_CAL_PARA_MISSING'
256 );
257
258 FEM_ENGINES_PKG.User_Message(
259 p_app_name => 'FEM',
260 p_msg_name => 'FEM_INTG_CAL_PARA_MISSING'
261 );
262
263 RAISE FEM_INTG_fatal_err;
264
265 ELSE
266 pv_period_set_name := p_period_set_name;
267 pv_period_type := p_period_type;
268 pv_period_year := p_period_year;
269 END IF;
270
271 --
272 -- Store a year being processed in low and high effective period
273 -- range variable for later comparison.
274 --
275 SELECT MIN(P.PERIOD_NUM), MAX(P.PERIOD_NUM)
276 INTO v_period_num_min, v_period_num_max
277 FROM GL_PERIODS P
278 WHERE P.PERIOD_SET_NAME = pv_period_set_name
279 AND P.PERIOD_TYPE = pv_period_type
280 AND P.PERIOD_YEAR = pv_period_year;
281
282 pv_effective_period_num_low := pv_period_year * 10000 + v_period_num_min;
283 pv_effective_period_num_high := pv_period_year * 10000 + v_period_num_max;
284
285 --
286 -- Get Period Type attributes
287 --
288 v_ogl_product_name := FND_MESSAGE.get_string(
289 'FEM',
290 'FEM_INTG_CAL_OGL_PRODUCT_NAME'
291 );
292 SELECT
293 'OGL_' || T.PERIOD_TYPE,
294 v_ogl_product_name || ' ' || T.USER_PERIOD_TYPE,
295 v_ogl_product_name || ' ' || T.DESCRIPTION,
296 T.NUMBER_PER_FISCAL_YEAR,
297 T.USER_PERIOD_TYPE
298 INTO
299 pv_ogl_period_type_code,
300 pv_ogl_period_type_name,
301 pv_ogl_period_type_desc,
302 pv_ogl_number_per_fiscal_year,
303 pv_period_type_name
304 FROM
305 GL_PERIOD_TYPES T
306 WHERE
307 T.PERIOD_TYPE = pv_period_type;
308
309 --
310 -- Check if Period Year has the expected number of periods
311 --
312 SELECT COUNT(P.PERIOD_NUM) INTO v_ogl_number_per_fiscal_year
313 FROM GL_PERIODS P
314 WHERE P.PERIOD_SET_NAME = pv_period_set_name
315 AND P.PERIOD_TYPE = pv_period_type
316 AND P.PERIOD_YEAR = pv_period_year;
317
318 IF v_ogl_number_per_fiscal_year <> pv_ogl_number_per_fiscal_year THEN
319
320 FEM_ENGINES_PKG.Tech_Message(
321 p_severity => pc_log_level_error,
322 p_module => pc_module_name || 'init.no_new_periods',
323 p_app_name => 'FEM',
324 p_msg_name => 'FEM_INTG_CAL_INVALID_NUM_PER_Y',
325 p_token1 => 'FISCAL_YEAR',
326 p_value1 => pv_period_year
327 );
328
329 FEM_ENGINES_PKG.User_Message(
330 p_app_name => 'FEM',
331 p_msg_name => 'FEM_INTG_CAL_INVALID_NUM_PER_Y',
332 p_token1 => 'FISCAL_YEAR',
333 p_value1 => pv_period_year
334 );
335
336 RAISE FEM_INTG_fatal_err;
337
338 END IF;
339
340 --
341 -- Get a mapping record for a given Period Set Name and Period Type
342 --
343 BEGIN
344 SELECT
345 C.OBJECT_ID,
346 M.CAL_PER_HIER_OBJ_DEF_ID,
347 M.DIMENSION_GROUP_ID,
348 M.CALENDAR_ID,
349 M.EFFECTIVE_PERIOD_NUM_MIN,
350 M.EFFECTIVE_PERIOD_NUM_MAX
351 INTO
352 pv_cal_per_hier_obj_id,
353 pv_cal_per_hier_obj_def_id,
354 pv_dimension_grp_id_period,
355 pv_calendar_id,
356 pv_effective_period_num_min,
357 pv_effective_period_num_max
358 FROM
359 FEM_INTG_CALENDAR_MAP M,
360 FEM_OBJECT_DEFINITION_B D,
361 FEM_OBJECT_CATALOG_B C
362 WHERE
363 M.PERIOD_SET_NAME = p_period_set_name AND
364 M.PERIOD_TYPE = p_period_type AND
365 D.OBJECT_DEFINITION_ID = M.CAL_PER_HIER_OBJ_DEF_ID AND
366 C.OBJECT_ID = D.OBJECT_ID;
367 EXCEPTION
368 WHEN NO_DATA_FOUND THEN
369 pv_cal_per_hier_obj_id := NULL;
370 pv_cal_per_hier_obj_def_id := NULL;
371 pv_dimension_grp_id_period := NULL;
372 pv_calendar_id := NULL;
373 pv_effective_period_num_min := NULL;
374 pv_effective_period_num_max := NULL;
375 END;
376
377 --
378 -- If Calendar ID based on the mapping record turns out to be NULL,
379 -- then retrieve it from FEM_CALENDARS_B table based on Period Set Name
380 -- instead.
381 --
382 IF pv_calendar_id IS NULL THEN
383 BEGIN
384 SELECT CALENDAR_ID
385 INTO pv_calendar_id
386 FROM FEM_CALENDARS_B
387 WHERE CALENDAR_DISPLAY_CODE = p_period_set_name;
388 EXCEPTION
389 WHEN NO_DATA_FOUND THEN
390 pv_calendar_id := NULL;
391 END;
392 END IF;
393
394 --
395 -- If Time Dimension Group ID based on the mapping record turns out to be
396 -- NULL, then retrieve it from FEM_DIMENSION_GRPS_B table based on
397 -- Period Type instead.
398 --
399 IF pv_dimension_grp_id_period IS NULL THEN
400 BEGIN
401 SELECT DIMENSION_GROUP_ID
402 INTO pv_dimension_grp_id_period
403 FROM FEM_DIMENSION_GRPS_B
404 WHERE TIME_GROUP_TYPE_CODE = pv_ogl_period_type_code;
405 EXCEPTION
406 WHEN NO_DATA_FOUND THEN
407 pv_dimension_grp_id_period := NULL;
408 END;
409 END IF;
410
411 --
412 -- Check gapless periods
413 --
414 v_effective_period_num_min := NVL(pv_effective_period_num_min,0);
415 v_effective_period_num_max := NVL(pv_effective_period_num_max, 3000*10000);
416
417 IF v_effective_period_num_min - pv_effective_period_num_high > 1 THEN
418
419 IF TRUNC(v_effective_period_num_min/10000) -
420 TRUNC(pv_effective_period_num_high/10000) = 1 THEN
421
422 SELECT MIN(P.PERIOD_NUM)
423 INTO v_period_num_min
424 FROM GL_PERIODS P
425 WHERE P.PERIOD_SET_NAME = pv_period_set_name
426 AND P.PERIOD_TYPE = pv_period_type
427 AND P.PERIOD_YEAR = TRUNC(v_effective_period_num_min/10000);
428
429 SELECT MAX(P.PERIOD_NUM)
430 INTO v_period_num_max
431 FROM GL_PERIODS P
432 WHERE P.PERIOD_SET_NAME = pv_period_set_name
433 AND P.PERIOD_TYPE = pv_period_type
434 AND P.PERIOD_YEAR = TRUNC(pv_effective_period_num_high/10000);
435
436 IF v_period_num_min <> MOD(v_effective_period_num_min,10000) OR
437 v_period_num_max <> MOD(pv_effective_period_num_high,10000) THEN
438 v_gap_found := 'Y';
439 END IF;
440
441 ELSE
442 v_gap_found := 'Y';
443 END IF;
444 END IF;
445
446 IF pv_effective_period_num_low - v_effective_period_num_max > 1 THEN
447
448 IF TRUNC(pv_effective_period_num_low/10000) -
449 TRUNC(v_effective_period_num_max/10000) = 1 THEN
450
451 SELECT MIN(P.PERIOD_NUM)
452 INTO v_period_num_min
453 FROM GL_PERIODS P
454 WHERE P.PERIOD_SET_NAME = pv_period_set_name
455 AND P.PERIOD_TYPE = pv_period_type
456 AND P.PERIOD_YEAR = TRUNC(pv_effective_period_num_low/10000);
457
458 SELECT MAX(P.PERIOD_NUM)
459 INTO v_period_num_max
460 FROM GL_PERIODS P
461 WHERE P.PERIOD_SET_NAME = pv_period_set_name
462 AND P.PERIOD_TYPE = pv_period_type
463 AND P.PERIOD_YEAR = TRUNC(v_effective_period_num_max/10000);
464
465 IF v_period_num_min <> MOD(pv_effective_period_num_low,10000) OR
466 v_period_num_max <> MOD(v_effective_period_num_max,10000) THEN
467 v_gap_found := 'Y';
468 END IF;
469
470 ELSE
471 v_gap_found := 'Y';
472 END IF;
473 END IF;
474
475 IF v_gap_found = 'Y' THEN
476 FEM_ENGINES_PKG.Tech_Message(
477 p_severity => pc_log_level_error,
478 p_module => pc_module_name || 'init.period_gap_found',
479 p_app_name => 'FEM',
480 p_msg_name => 'FEM_INTG_CAL_PERIOD_GAP_FOUND',
481 p_token1 => 'FISCAL_YEAR',
482 p_value1 => pv_period_year,
483 p_token2 => 'PERIOD_SET_NAME',
484 p_value2 => pv_period_set_name,
485 p_token3 => 'PERIOD_TYPE',
486 p_value3 => pv_period_type_name
487 );
488
489 FEM_ENGINES_PKG.User_Message(
490 p_app_name => 'FEM',
491 p_msg_name => 'FEM_INTG_CAL_PERIOD_GAP_FOUND',
492 p_token1 => 'FISCAL_YEAR',
493 p_value1 => pv_period_year,
494 p_token2 => 'PERIOD_SET_NAME',
495 p_value2 => pv_period_set_name,
496 p_token3 => 'PERIOD_TYPE',
497 p_value3 => pv_period_type_name
498 );
499
500 RAISE FEM_INTG_fatal_err;
501
502 END IF;
503
504 --
505 -- Check if there are new periods to process
506 --
507 IF NVL(pv_effective_period_num_min, 3000*10000) -
508 pv_effective_period_num_low > 0 OR
509 pv_effective_period_num_high -
510 NVL(pv_effective_period_num_max, 0) > 0 THEN
511
512 pv_new_periods_to_process := 'Y';
513
514 ELSE
515
516 pv_new_periods_to_process := 'N';
517
518 FEM_ENGINES_PKG.Tech_Message(
519 p_severity => pc_log_level_error,
520 p_module => pc_module_name || 'init.no_new_periods',
521 p_app_name => 'FEM',
522 p_msg_name => 'FEM_INTG_CAL_NO_NEW_PERIODS'
523 );
524
525 FEM_ENGINES_PKG.User_Message(
526 p_app_name => 'FEM',
527 p_msg_name => 'FEM_INTG_CAL_NO_NEW_PERIODS'
528 );
529 END IF;
530
531 --
532 -- Check if a Calendar ID already exists for a given Period Set Name.
533 -- If Calendar ID is obtained from the mapping record, there is a chance
534 -- that it may not be a valid one.
535 --
536 BEGIN
537 SELECT CALENDAR_ID
538 INTO v_calendar_id
539 FROM FEM_CALENDARS_B
540 WHERE CALENDAR_DISPLAY_CODE = pv_period_set_name;
541 EXCEPTION
542 WHEN NO_DATA_FOUND THEN
543 v_calendar_id := NULL;
544 END;
545
546 --
547 -- Check if a Time Dimension Group ID exists for a given Period Type.
548 -- If Time Dimension Group ID is obtained from the mapping record,
549 -- there is a chance that it may not be a valid one.
550 --
551 BEGIN
552 SELECT G.DIMENSION_GROUP_ID
553 INTO v_dimension_grp_id_period
554 FROM
555 FEM_DIMENSION_GRPS_B G
556 WHERE
557 G.DIMENSION_GROUP_DISPLAY_CODE = pv_ogl_period_type_code;
558 EXCEPTION
559 WHEN NO_DATA_FOUND THEN
560 v_dimension_grp_id_period := NULL;
561 END;
562
563 --
564 -- Check if a Calendar Period Hierarchy ID already exists
565 --
566 --dedutta : Bug 4992900 : Changed query to introduce additional filtration
567 BEGIN
568 SELECT
569 h.HIERARCHY_OBJ_ID
570 INTO
571 v_cal_per_hier_obj_id
572 FROM
573 FEM_HIERARCHIES h,
574 fem_object_catalog_b o
575 WHERE
576 h.CALENDAR_ID = NVL(v_calendar_id, -1)
577 and h.PERIOD_TYPE = pv_period_type
578 and h.personal_flag = 'N'
579 and h.hierarchy_usage_code = 'STANDARD'
580 and h.HIERARCHY_OBJ_ID=o.OBJECT_ID
581 and o.folder_id=pv_folder_id ;
582 EXCEPTION
583 WHEN NO_DATA_FOUND THEN
584 v_cal_per_hier_obj_id := NULL;
585 END;
586
587 --
588 -- Check if the retrieved mapping record is valid,
589 -- i.e.,check if the corresponding Calendar, Time Dimension Group,
590 -- and hierarchy also exist. Note that unless the mapping record is
591 -- tampered, this check should not fail.
592 --
593 IF pv_cal_per_hier_obj_def_id IS NOT NULL AND (
594 NVL(v_calendar_id, -1) <> NVL(pv_calendar_id, -1) OR
595 NVL(v_dimension_grp_id_period,-1)<>NVL(pv_dimension_grp_id_period,-1) OR
596 NVL(v_cal_per_hier_obj_id, -1) <> NVL(pv_cal_per_hier_obj_id, -1)) THEN
597
598 FEM_ENGINES_PKG.Tech_Message(
599 p_severity => pc_log_level_error,
600 p_module => pc_module_name || 'init.invalid_cal_map',
601 p_app_name => 'FEM',
602 p_msg_name => 'FEM_INTG_CAL_INVALID_CAL_MAP',
603 p_token1 => 'PERIOD_SET_NAME',
604 p_value1 => pv_period_set_name,
605 p_token2 => 'PERIOD_TYPE',
606 p_value2 => pv_period_type_name
607 );
608
609 FEM_ENGINES_PKG.User_Message(
610 p_app_name => 'FEM',
611 p_msg_name => 'FEM_INTG_CAL_INVALID_CAL_MAP',
612 p_token1 => 'PERIOD_SET_NAME',
613 p_value1 => pv_period_set_name,
614 p_token2 => 'PERIOD_TYPE',
615 p_value2 => pv_period_type_name
616 );
617
618 --
619 -- Delete an invalid mapping record. The next run will create missing
620 -- Calendar, Time Dimension Group, and/or hierarchy and will create a
621 -- valid mapping record.
622 --
623 DELETE FROM FEM_INTG_CALENDAR_MAP
624 WHERE CAL_PER_HIER_OBJ_DEF_ID = pv_cal_per_hier_obj_def_id;
625
626 COMMIT;
627
628 RAISE FEM_INTG_fatal_err;
629
630 END IF;
631
632 --
633 -- Initialize other package level variables
634 --
635 SELECT DIMENSION_ID
636 INTO pv_ledger_dim_id
637 FROM FEM_DIMENSIONS_B
638 WHERE DIMENSION_VARCHAR_LABEL = 'LEDGER';
639
640 SELECT DIMENSION_ID
641 INTO pv_calendar_dim_id
642 FROM FEM_DIMENSIONS_B
643 WHERE DIMENSION_VARCHAR_LABEL = 'CALENDAR';
644
645 SELECT DIMENSION_ID
646 INTO pv_cal_period_dim_id
647 FROM FEM_DIMENSIONS_B
648 WHERE DIMENSION_VARCHAR_LABEL = 'CAL_PERIOD';
649
650 SELECT DIMENSION_ID
651 INTO pv_time_group_type_dim_id
652 FROM FEM_DIMENSIONS_B
653 WHERE DIMENSION_VARCHAR_LABEL = 'TIME_GROUP_TYPE';
654
655 SELECT DIMENSION_GROUP_ID
656 INTO pv_dimension_grp_id_year
657 FROM FEM_DIMENSION_GRPS_B
658 WHERE DIMENSION_GROUP_DISPLAY_CODE = 'Year';
659
660 SELECT DIMENSION_GROUP_ID
661 INTO pv_dimension_grp_id_quarter
662 FROM FEM_DIMENSION_GRPS_B
663 WHERE DIMENSION_GROUP_DISPLAY_CODE = 'Quarter';
664
665 SELECT SOURCE_SYSTEM_CODE
666 INTO pv_source_system_code
667 FROM FEM_SOURCE_SYSTEMS_B
668 WHERE SOURCE_SYSTEM_DISPLAY_CODE = 'OGL';
669
670 pv_row_count_tot := 0;
671
672 FEM_ENGINES_PKG.Tech_Message(
673 p_severity => pc_log_level_procedure,
674 p_module => pc_module_name || 'init.end',
675 p_app_name => 'FEM',
676 p_msg_name => 'FEM_GL_POST_202',
677 p_token1 => 'FUNC_NAME',
678 p_value1 => 'FEM_INTG_CAL_RULE_ENG_PKG.Init',
679 p_token2 => 'TIME',
680 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
681 );
682
683 EXCEPTION
684 WHEN FEM_INTG_fatal_err THEN
685 ROLLBACK;
686
687 FEM_ENGINES_PKG.Tech_Message(
688 p_severity => pc_log_level_exception,
689 p_module => pc_module_name || 'init.exception_fatal_err',
690 p_app_name => 'FEM',
691 p_msg_name => 'FEM_GL_POST_203',
692 p_token1 => 'FUNC_NAME',
693 p_value1 => 'FEM_INTG_CAL_RULE_ENG_PKG.Init',
694 p_token2 => 'TIME',
695 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
696 );
697
698 RAISE;
699
700 WHEN OTHERS THEN
701 ROLLBACK;
702
703 FEM_ENGINES_PKG.Tech_Message(
704 p_severity => pc_log_level_exception,
705 p_module => pc_module_name || 'init.exception_others' ,
706 p_app_name => 'FEM',
707 p_msg_name => 'FEM_GL_POST_215',
708 p_token1 => 'ERR_MSG',
709 p_value1 => SQLERRM
710 );
711
712 FEM_ENGINES_PKG.User_Message(
713 p_app_name => 'FEM',
714 p_msg_name => 'FEM_GL_POST_215',
715 p_token1 => 'ERR_MSG',
716 p_value1 => SQLERRM
717 );
718
719 FEM_ENGINES_PKG.Tech_Message(
720 p_severity => pc_log_level_exception,
721 p_module => pc_module_name || 'init.exception_others',
722 p_app_name => 'FEM',
723 p_msg_name => 'FEM_GL_POST_203',
724 p_token1 => 'FUNC_NAME',
725 p_value1 => 'FEM_INTG_CAL_RULE_ENG_PKG.Init',
726 p_token2 => 'TIME',
727 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
728 );
729
730 RAISE;
731
732 END Init;
733
734
735 -- ======================================================================
736 -- Procedure
737 -- New_Calendar
738 -- Purpose
739 -- Creates a new Calendar
740 -- History
741 -- 02-02-05 Shintaro Okuda Created
742 -- Arguments
743 -- None
744 -- ======================================================================
745 PROCEDURE New_Calendar
746 IS
747 FEM_INTG_fatal_err EXCEPTION;
748
749 v_return_status VARCHAR2(1);
750 v_msg_count NUMBER;
751 v_msg_data VARCHAR2(2000);
752 BEGIN
753
754 FEM_ENGINES_PKG.Tech_Message(
755 p_severity => pc_log_level_procedure,
756 p_module => pc_module_name || 'new_calendar.begin',
757 p_app_name => 'FEM',
758 p_msg_name => 'FEM_GL_POST_201',
759 p_token1 => 'FUNC_NAME',
760 p_value1 => 'FEM_INTG_CAL_RULE_ENG_PKG.New_Calendar',
761 p_token2 => 'TIME',
762 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
763 );
764
765 --
766 -- Note that pc_source_cd is used for FEM.C and
767 -- pv_source_system_code is used for FEM.D and above.
768 --
769 FEM_DIM_CAL_UTIL_PKG.New_Calendar (
770 x_return_status => v_return_status,
771 x_msg_count => v_msg_count,
772 x_msg_data => v_msg_data,
773 x_calendar_id => pv_calendar_id,
774 p_cal_disp_code => pv_period_set_name,
775 p_calendar_name => pv_period_set_name,
776 p_source_cd => pv_source_system_code,
777 p_period_set_name => pv_period_set_name,
778 p_ver_name => pc_ver_name,
779 p_ver_disp_cd => pc_ver_disp_cd,
780 p_calendar_desc => pv_period_set_name,
781 p_include_adj_per_flg => pc_adjustment_period_flag
782 );
783
784 IF v_return_status <> pc_success THEN
785
786 FOR i IN 1 .. v_msg_count LOOP
787 v_msg_data := FND_MSG_PUB.Get(
788 p_msg_index => i,
789 p_encoded => 'F'
790 );
791
792 FEM_ENGINES_PKG.Tech_Message(
793 p_severity => pc_log_level_error,
794 p_module => pc_module_name || 'new_calendar_error',
795 p_app_name => 'FEM',
796 p_msg_text => v_msg_data
797 );
798 END LOOP;
799
800 FEM_ENGINES_PKG.Tech_Message(
801 p_severity => pc_log_level_error,
802 p_module => pc_module_name || 'new_calendar_error',
803 p_app_name => 'FEM',
804 p_msg_name => 'FEM_INTG_CAL_NEW_CAL_ERROR',
805 p_token1 => 'CALENDAR_DIMENSION',
806 p_value1 => pv_period_set_name
807 );
808
809 FEM_ENGINES_PKG.User_Message(
810 p_app_name => 'FEM',
811 p_msg_name => 'FEM_INTG_CAL_NEW_CAL_ERROR',
812 p_token1 => 'CALENDAR_DIMENSION',
813 p_value1 => pv_period_set_name
814 );
815
816 RAISE FEM_INTG_fatal_err;
817
818 END IF;
819
820 FEM_ENGINES_PKG.Tech_Message(
821 p_severity => pc_log_level_procedure,
822 p_module => pc_module_name || 'new_calendar.end',
823 p_app_name => 'FEM',
824 p_msg_name => 'FEM_GL_POST_202',
825 p_token1 => 'FUNC_NAME',
826 p_value1 => 'FEM_INTG_CAL_RULE_ENG_PKG.New_Calendar',
827 p_token2 => 'TIME',
828 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
829 );
830
831 EXCEPTION
832 WHEN FEM_INTG_fatal_err THEN
833 ROLLBACK;
834
835 FEM_ENGINES_PKG.Tech_Message(
836 p_severity => pc_log_level_exception,
837 p_module => pc_module_name || 'new_calendar.exception_fatal_err',
838 p_app_name => 'FEM',
839 p_msg_name => 'FEM_GL_POST_203',
840 p_token1 => 'FUNC_NAME',
841 p_value1 => 'FEM_INTG_CAL_RULE_ENG_PKG.New_Calendar',
842 p_token2 => 'TIME',
843 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
844 );
845
846 RAISE;
847
848 WHEN OTHERS THEN
849 ROLLBACK;
850
851 FEM_ENGINES_PKG.Tech_Message(
852 p_severity => pc_log_level_exception,
853 p_module => pc_module_name || 'new_calendar.exception_others' ,
854 p_app_name => 'FEM',
855 p_msg_name => 'FEM_GL_POST_215',
856 p_token1 => 'ERR_MSG',
857 p_value1 => SQLERRM
858 );
859
860 FEM_ENGINES_PKG.User_Message(
861 p_app_name => 'FEM',
862 p_msg_name => 'FEM_GL_POST_215',
863 p_token1 => 'ERR_MSG',
864 p_value1 => SQLERRM
865 );
866
867 FEM_ENGINES_PKG.Tech_Message(
868 p_severity => pc_log_level_exception,
869 p_module => pc_module_name || 'new_calendar.exception_others',
870 p_app_name => 'FEM',
871 p_msg_name => 'FEM_GL_POST_203',
872 p_token1 => 'FUNC_NAME',
873 p_value1 => 'FEM_INTG_CAL_RULE_ENG_PKG.New_Calendar',
874 p_token2 => 'TIME',
875 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
876 );
877
878 RAISE;
879
880 END New_Calendar;
881
882
883 -- ======================================================================
884 -- Procedure
885 -- New_Time_Dimension_Group
886 -- Purpose
887 -- Creates a new Time Group Type and a new Time Dimension Group
888 -- History
889 -- 02-02-05 Shintaro Okuda Created
890 -- Arguments
891 -- None
892 -- ======================================================================
893 PROCEDURE New_Time_Dimension_Group
894 IS
895 FEM_INTG_fatal_err EXCEPTION;
896
897 v_return_status VARCHAR2(1);
898 v_msg_count NUMBER;
899 v_msg_data VARCHAR2(2000);
900
901 v_time_group_type_code VARCHAR2(30):= NULL;
902 BEGIN
903
904 FEM_ENGINES_PKG.Tech_Message(
905 p_severity => pc_log_level_procedure,
906 p_module => pc_module_name || 'new_time_dimension_group.begin',
907 p_app_name => 'FEM',
908 p_msg_name => 'FEM_GL_POST_201',
909 p_token1 => 'FUNC_NAME',
910 p_value1 => 'FEM_INTG_CAL_RULE_ENG_PKG.New_Time_Dimension_Group',
911 p_token2 => 'TIME',
912 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
913 );
914
915 --
916 -- Check the existence of Time Group Type Code
917 --
918 BEGIN
919 SELECT TIME_GROUP_TYPE_CODE
920 INTO v_time_group_type_code
921 FROM FEM_TIME_GROUP_TYPES_B
922 WHERE TIME_GROUP_TYPE_CODE = pv_ogl_period_type_code;
923 EXCEPTION
924 WHEN NO_DATA_FOUND THEN
925 FEM_ENGINES_PKG.Tech_Message(
926 p_severity => pc_log_level_statement,
927 p_module => pc_module_name || 'new_time_group_type_create_new',
928 p_app_name => 'FEM',
929 p_msg_text => 'Creating a new Time Group Type Code.'
930 );
931 END;
932
933 IF v_time_group_type_code IS NULL THEN
934
935 FEM_DIM_CAL_UTIL_PKG.New_Time_Group_Type(
936 x_return_status => v_return_status,
937 x_msg_count => v_msg_count,
938 x_msg_data => v_msg_data,
939 p_time_grp_type_code => pv_ogl_period_type_code,
940 p_time_grp_type_name => pv_ogl_period_type_name,
941 p_time_grp_type_desc => pv_ogl_period_type_desc,
942 p_periods_in_year => pv_ogl_number_per_fiscal_year,
943 p_ver_name => pc_ver_name,
944 p_ver_disp_cd => pc_ver_disp_cd,
945 p_read_only_flag => 'Y'
946 );
947
948 IF v_return_status <> pc_success THEN
949
950 FOR i IN 1 .. v_msg_count LOOP
951
952 v_msg_data := FND_MSG_PUB.Get(
953 p_msg_index => i,
954 p_encoded => 'F'
955 );
956 FEM_ENGINES_PKG.Tech_Message(
957 p_severity => pc_log_level_error,
958 p_module => pc_module_name || 'new_time_group_type_error',
959 p_app_name => 'FEM',
960 p_msg_text => v_msg_data
961 );
962
963 END LOOP;
964
965 FEM_ENGINES_PKG.Tech_Message(
966 p_severity => pc_log_level_error,
967 p_module => pc_module_name || 'new_time_group_type_error',
968 p_app_name => 'FEM',
969 p_msg_name => 'FEM_INTG_CAL_NEW_TGT_ERROR',
970 p_token1 => 'TIME_GROUP_TYPE',
971 p_value1 => pv_ogl_period_type_name
972 );
973
974 FEM_ENGINES_PKG.User_Message(
975 p_app_name => 'FEM',
976 p_msg_name => 'FEM_INTG_CAL_NEW_TGT_ERROR',
977 p_token1 => 'TIME_GROUP_TYPE',
978 p_value1 => pv_ogl_period_type_name
979 );
980
981 RAISE FEM_INTG_fatal_err;
982
983 END IF;
984
985 END IF;
986
987 IF pv_dimension_grp_id_period IS NULL THEN
988
989 FEM_ENGINES_PKG.Tech_Message(
990 p_severity => pc_log_level_statement,
991 p_module => pc_module_name || 'new_time_dimension_group_create_new',
992 p_app_name => 'FEM',
993 p_msg_text => 'Creating a new Time Dimension Group.'
994 );
995
996 FEM_DIM_CAL_UTIL_PKG.New_Time_Dimension_Group(
997 x_return_status => v_return_status,
998 x_msg_count => v_msg_count,
999 x_msg_data => v_msg_data,
1000 x_dim_grp_id => pv_dimension_grp_id_period,
1001 p_time_grp_type_code => pv_ogl_period_type_code,
1002 p_dim_grp_name => pv_ogl_period_type_name,
1003 p_dim_grp_disp_cd => pv_ogl_period_type_code,
1004 p_dim_grp_desc => pv_ogl_period_type_desc,
1005 p_read_only_flag => 'Y'
1006 );
1007
1008 IF v_return_status <> pc_success THEN
1009
1010 FOR i IN 1 .. v_msg_count LOOP
1011
1012 v_msg_data := FND_MSG_PUB.Get(
1013 p_msg_index => i,
1014 p_encoded => 'F'
1015 );
1016 FEM_ENGINES_PKG.Tech_Message(
1017 p_severity => pc_log_level_error,
1018 p_module => pc_module_name || 'new_time_dimension_group_error',
1019 p_app_name => 'FEM',
1020 p_msg_text => v_msg_data
1021 );
1022 END LOOP;
1023
1024
1025 FEM_ENGINES_PKG.Tech_Message(
1026 p_severity => pc_log_level_error,
1027 p_module => pc_module_name || 'new_time_dimension_group_error',
1028 p_app_name => 'FEM',
1029 p_msg_name => 'FEM_INTG_CAL_NEW_TDG_ERROR',
1030 p_token1 => 'TIME_DIMENSION_GROUP',
1031 p_value1 => pv_ogl_period_type_name
1032 );
1033
1034 FEM_ENGINES_PKG.User_Message(
1035 p_app_name => 'FEM',
1036 p_msg_name => 'FEM_INTG_CAL_NEW_TDG_ERROR',
1037 p_token1 => 'TIME_DIMENSION_GROUP',
1038 p_value1 => pv_ogl_period_type_name
1039 );
1040
1041 RAISE FEM_INTG_fatal_err;
1042
1043 END IF;
1044
1045 END IF;
1046
1047 FEM_ENGINES_PKG.Tech_Message(
1048 p_severity => pc_log_level_procedure,
1049 p_module => pc_module_name || 'new_time_dimension_group.end',
1050 p_app_name => 'FEM',
1051 p_msg_name => 'FEM_GL_POST_202',
1052 p_token1 => 'FUNC_NAME',
1053 p_value1 => 'FEM_INTG_CAL_RULE_ENG_PKG.New_Time_Dimension_Group',
1054 p_token2 => 'TIME',
1055 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
1056 );
1057
1058 EXCEPTION
1059 WHEN FEM_INTG_fatal_err THEN
1060 ROLLBACK;
1061
1062 FEM_ENGINES_PKG.Tech_Message(
1063 p_severity => pc_log_level_exception,
1064 p_module => pc_module_name || 'new_time_dimension_group.exception_fatal_err',
1065 p_app_name => 'FEM',
1066 p_msg_name => 'FEM_GL_POST_203',
1067 p_token1 => 'FUNC_NAME',
1068 p_value1 => 'FEM_INTG_CAL_RULE_ENG_PKG.New_Time_Dimension_Group',
1069 p_token2 => 'TIME',
1070 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
1071 );
1072
1073 RAISE;
1074
1075 WHEN OTHERS THEN
1076 ROLLBACK;
1077
1078 FEM_ENGINES_PKG.Tech_Message(
1079 p_severity => pc_log_level_exception,
1080 p_module => pc_module_name || 'new_time_dimension_group.exception_others' ,
1081 p_app_name => 'FEM',
1082 p_msg_name => 'FEM_GL_POST_215',
1083 p_token1 => 'ERR_MSG',
1084 p_value1 => SQLERRM
1085 );
1086
1087 FEM_ENGINES_PKG.User_Message(
1088 p_app_name => 'FEM',
1089 p_msg_name => 'FEM_GL_POST_215',
1090 p_token1 => 'ERR_MSG',
1091 p_value1 => SQLERRM
1092 );
1093
1094 FEM_ENGINES_PKG.Tech_Message(
1095 p_severity => pc_log_level_exception,
1096 p_module => pc_module_name || 'new_time_dimension_group.exception_others',
1097 p_app_name => 'FEM',
1098 p_msg_name => 'FEM_GL_POST_203',
1099 p_token1 => 'FUNC_NAME',
1100 p_value1 => 'FEM_INTG_CAL_RULE_ENG_PKG.New_Time_Dimension_Group',
1101 p_token2 => 'TIME',
1102 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
1103 );
1104
1105 RAISE;
1106
1107 End New_Time_Dimension_Group;
1108
1109
1110 -- ======================================================================
1111 -- Procedure
1112 -- New_GL_Cal_Period_Hier
1113 -- Purpose
1114 -- Create a new GL Calendar Period Hierarchy
1115 -- History
1116 -- 02-02-05 Shintaro Okuda Created
1117 -- Arguments
1118 -- None
1119 -- ======================================================================
1120 PROCEDURE New_GL_Cal_Period_Hier
1121 IS
1122 FEM_INTG_fatal_err EXCEPTION;
1123
1124 v_object_name VARCHAR2(200);
1125
1126 v_return_status VARCHAR2(1);
1127 v_msg_count NUMBER;
1128 v_msg_data VARCHAR2(2000);
1129 BEGIN
1130
1131 FEM_ENGINES_PKG.Tech_Message(
1132 p_severity => pc_log_level_procedure,
1133 p_module => pc_module_name || 'new_gl_cal_period_hier.begin',
1134 p_app_name => 'FEM',
1135 p_msg_name => 'FEM_GL_POST_201',
1136 p_token1 => 'FUNC_NAME',
1137 p_value1 => 'FEM_INTG_CAL_RULE_ENG_PKG.New_GL_Cal_Peroid_Hier',
1138 p_token2 => 'TIME',
1139 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
1140 );
1141
1142 --
1143 -- Define a new hierarchy
1144 --
1145 v_object_name := pv_period_set_name || '-' || pv_period_type_name;
1146
1147 FEM_DIM_HIER_UTIL_PKG.New_GL_Cal_Period_Hier(
1148 x_return_status => v_return_status,
1149 x_msg_count => v_msg_count,
1150 x_msg_data => v_msg_data,
1151 x_hier_obj_id => pv_cal_per_hier_obj_id,
1152 x_hier_obj_def_id => pv_cal_per_hier_obj_def_id,
1153 p_folder_id => pv_folder_id,
1154 p_object_access_code => pc_object_access_code,
1155 p_object_origin_code => pc_object_origin_code,
1156 p_object_name => v_object_name,
1157 p_description => v_object_name,
1158 p_effective_start_date => pv_effective_start_date,
1159 p_effective_end_date => pv_effective_end_date,
1160 p_obj_def_name => v_object_name,
1161 p_grp_seq_code => pc_grp_seq_code,
1162 p_multi_top_flg => pc_multi_top_flg,
1163 p_gl_period_type => pv_period_type,
1164 p_dim_grp_id => pv_dimension_grp_id_period,
1165 p_calendar_id => pv_calendar_id
1166 );
1167
1168 IF v_return_status <> pc_success THEN
1169
1170 FOR i IN 1 .. v_msg_count LOOP
1171 v_msg_data := FND_MSG_PUB.Get(
1172 p_msg_index => i,
1173 p_encoded => 'F'
1174 );
1175
1176 FEM_ENGINES_PKG.Tech_Message(
1177 p_severity => pc_log_level_error,
1178 p_module => pc_module_name || 'new_gl_cal_period_hier_error',
1179 p_app_name => 'FEM',
1180 p_msg_text => v_msg_data
1181 );
1182 END LOOP;
1183
1184 FEM_ENGINES_PKG.Tech_Message(
1185 p_severity => pc_log_level_error,
1186 p_module => pc_module_name || 'new_gl_cal_period_hier_error',
1187 p_app_name => 'FEM',
1188 p_msg_name => 'FEM_INTG_CAL_NEW_HIER_ERROR',
1189 p_token1 => 'TIME_DIMENSION_HIER_OBJ',
1190 p_value1 => v_object_name
1191 );
1192
1193 FEM_ENGINES_PKG.User_Message(
1194 p_app_name => 'FEM',
1195 p_msg_name => 'FEM_INTG_CAL_NEW_HIER_ERROR',
1196 p_token1 => 'TIME_DIMENSION_HIER_OBJ',
1197 p_value1 => v_object_name
1198 );
1199
1200 RAISE FEM_INTG_fatal_err;
1201 END IF;
1202
1203 --
1204 -- Create a mapping record
1205 --
1206 INSERT INTO FEM_INTG_CALENDAR_MAP(
1207 PERIOD_SET_NAME,
1208 PERIOD_TYPE,
1209 CAL_PER_HIER_OBJ_DEF_ID,
1210 DIMENSION_GROUP_ID,
1211 CALENDAR_ID,
1212 EFFECTIVE_PERIOD_NUM_MIN,
1213 EFFECTIVE_PERIOD_NUM_MAX,
1214 CREATION_DATE,
1215 CREATED_BY,
1216 LAST_UPDATE_DATE,
1217 LAST_UPDATED_BY,
1218 LAST_UPDATE_LOGIN
1219 ) VALUES (
1220 pv_period_set_name,
1221 pv_period_type,
1222 pv_cal_per_hier_obj_def_id,
1223 pv_dimension_grp_id_period,
1224 pv_calendar_id,
1225 NULL,
1226 NULL,
1227 SYSDATE,
1228 pv_user_id,
1229 SYSDATE,
1230 pv_user_id,
1231 pv_login_id
1232 );
1233
1234 pv_row_count_tot := pv_row_count_tot + SQL%ROWCOUNT;
1235
1236 --
1237 -- Update a list of Ledgers' attribute which share the same
1238 -- Period Set Name/Period Type
1239 --
1240 -- Note that the sub query returning LEDGER_ID for H.PARENT_ID
1241 -- can potentially be replaced with a WHERE clause.
1242 --
1243 UPDATE FEM_LEDGERS_ATTR
1244 SET DIM_ATTRIBUTE_NUMERIC_MEMBER = pv_cal_per_hier_obj_def_id,
1245 LAST_UPDATE_DATE = SYSDATE,
1246 LAST_UPDATED_BY = pv_user_id,
1247 LAST_UPDATE_LOGIN = pv_login_id
1248 WHERE
1249 LEDGER_ID IN (
1250 SELECT
1251 H.CHILD_ID
1252 FROM
1253 FEM_LEDGERS_HIER H,
1254 GL_LEDGERS LGR
1255 WHERE
1256 H.PARENT_ID = (
1257 SELECT LEDGER_ID
1258 FROM FEM_LEDGERS_B
1259 WHERE LEDGER_DISPLAY_CODE = 'OGL_SOURCE_LEDGER_GROUP'
1260 ) AND
1261 H.HIERARCHY_OBJ_DEF_ID = 1505 AND -- a hard-coded value is to be
1262 -- replaced with a future
1263 -- FEM API return value
1264 LGR.LEDGER_ID = H.CHILD_ID AND
1265 LGR.PERIOD_SET_NAME = pv_period_set_name AND
1266 LGR.ACCOUNTED_PERIOD_TYPE = pv_period_type
1267 ) AND
1268 (ATTRIBUTE_ID, VERSION_ID) = (
1269 SELECT
1270 V.ATTRIBUTE_ID,
1271 V.VERSION_ID
1272 FROM
1273 FEM_DIM_ATTRIBUTES_B A,
1274 FEM_DIM_ATTR_VERSIONS_B V
1275 WHERE
1276 A.DIMENSION_ID = pv_ledger_dim_id AND
1277 A.ATTRIBUTE_VARCHAR_LABEL = 'CAL_PERIOD_HIER_OBJ_DEF_ID' AND
1278 V.ATTRIBUTE_ID = A.ATTRIBUTE_ID AND
1279 V.DEFAULT_VERSION_FLAG = 'Y'
1280 ) AND
1281 DIM_ATTRIBUTE_NUMERIC_MEMBER = -1;
1282 /*
1283 Removed the code below and replaced with -1 as part of bug 5486636
1284 (
1285 SELECT
1286 DIM_ATTRIBUTE_NUMERIC_MEMBER
1287 FROM
1288 FEM_DIM_ATTRIBUTES_B A,
1289 FEM_DIM_ATTR_VERSIONS_B V,
1290 FEM_LEDGERS_B L,
1291 FEM_LEDGERS_ATTR LA
1292 WHERE
1293 A.DIMENSION_ID = pv_ledger_dim_id AND
1294 A.ATTRIBUTE_VARCHAR_LABEL = 'CAL_PERIOD_HIER_OBJ_DEF_ID' AND
1295 V.ATTRIBUTE_ID = A.ATTRIBUTE_ID AND
1296 V.DEFAULT_VERSION_FLAG = 'Y' AND
1297 L.LEDGER_DISPLAY_CODE = 'OGL_SOURCE_LEDGER_GROUP' AND
1298 LA.LEDGER_ID = L.LEDGER_ID AND
1299 LA.ATTRIBUTE_ID = V.ATTRIBUTE_ID AND
1300 LA.VERSION_ID = V.VERSION_ID
1301 );
1302 */
1303 pv_row_count_tot := pv_row_count_tot + SQL%ROWCOUNT;
1304
1305 FEM_ENGINES_PKG.Tech_Message(
1306 p_severity => pc_log_level_procedure,
1307 p_module => pc_module_name || 'new_gl_cal_period_hier.end',
1308 p_app_name => 'FEM',
1309 p_msg_name => 'FEM_GL_POST_202',
1310 p_token1 => 'FUNC_NAME',
1311 p_value1 => 'FEM_INTG_CAL_RULE_ENG_PKG.New_GL_Cal_Period_Hier',
1312 p_token2 => 'TIME',
1313 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
1314 );
1315
1316 EXCEPTION
1317 WHEN FEM_INTG_fatal_err THEN
1318 ROLLBACK;
1319
1320 FEM_ENGINES_PKG.Tech_Message(
1321 p_severity => pc_log_level_exception,
1322 p_module => pc_module_name || 'new_gl_cal_period_hier.exception_fatal_err',
1323 p_app_name => 'FEM',
1324 p_msg_name => 'FEM_GL_POST_203',
1325 p_token1 => 'FUNC_NAME',
1326 p_value1 => 'FEM_INTG_CAL_RULE_ENG_PKG.New_GL_Cal_Period_Hier',
1327 p_token2 => 'TIME',
1328 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
1329 );
1330
1331 RAISE;
1332
1333 WHEN OTHERS THEN
1334 ROLLBACK;
1335
1336 FEM_ENGINES_PKG.Tech_Message(
1337 p_severity => pc_log_level_exception,
1338 p_module => pc_module_name || 'new_gl_cal_period_hier.exception_others' ,
1339 p_app_name => 'FEM',
1340 p_msg_name => 'FEM_GL_POST_215',
1341 p_token1 => 'ERR_MSG',
1342 p_value1 => SQLERRM
1343 );
1344
1345 FEM_ENGINES_PKG.User_Message(
1346 p_app_name => 'FEM',
1347 p_msg_name => 'FEM_GL_POST_215',
1348 p_token1 => 'ERR_MSG',
1349 p_value1 => SQLERRM
1350 );
1351
1352 FEM_ENGINES_PKG.Tech_Message(
1353 p_severity => pc_log_level_exception,
1354 p_module => pc_module_name || 'new_gl_cal_period_hier.exception_others',
1355 p_app_name => 'FEM',
1356 p_msg_name => 'FEM_GL_POST_203',
1357 p_token1 => 'FUNC_NAME',
1358 p_value1 => 'FEM_INTG_CAL_RULE_ENG_PKG.New_GL_Cal_Period_Hier',
1359 p_token2 => 'TIME',
1360 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
1361 );
1362
1363 RAISE;
1364
1365 END New_GL_Cal_Period_Hier;
1366
1367
1368 -- ======================================================================
1369 -- Procedure
1370 -- Generate_Member_IDs
1371 -- Purpose
1372 -- Generates CAL_PERIOD_IDs using FEM_DIMENSION_UTIL_PKG.Generate_Member_ID
1373 -- and stores them in Global Temporary table
1374 -- History
1375 -- 02-02-05 Shintaro Okuda Created
1376 -- 08-02-05 Harikiran Bug 4523730 - Made code changes to the query
1377 -- which inserts values into the global temporary
1378 -- table FEM_INTG_CAL_PERIODS_GT so that cal_period_id
1379 -- and gl_period_num attribute are in sync
1380 -- Arguments
1381 -- None
1382 -- ======================================================================
1383 PROCEDURE Generate_Member_IDs
1384 IS
1385 v_err_code NUMBER;
1386 v_num_msg NUMBER;
1387 BEGIN
1388
1389 FEM_ENGINES_PKG.Tech_Message(
1390 p_severity => pc_log_level_procedure,
1391 p_module => pc_module_name || 'generate_member_ids.begin',
1392 p_app_name => 'FEM',
1393 p_msg_name => 'FEM_GL_POST_201',
1394 p_token1 => 'FUNC_NAME',
1395 p_value1 => 'FEM_INTG_CAL_RULE_ENG_PKG.Generate_Member_IDs',
1396 p_token2 => 'TIME',
1397 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
1398 );
1399
1400 INSERT INTO FEM_INTG_CAL_PERIODS_GT(
1401 CAL_PERIOD_ID,
1402 DIMENSION_GROUP_ID,
1403 PERIOD_NAME,
1404 ENTERED_PERIOD_NAME,
1405 ADJUSTMENT_PERIOD_FLAG,
1406 START_DATE,
1407 END_DATE,
1408 PERIOD_NUM,
1409 QUARTER_NUM,
1410 PERIOD_YEAR
1411 )
1412 SELECT -- Period members
1413 FEM_DIMENSION_UTIL_PKG.Generate_Member_ID(
1414 P.END_DATE,
1415 P.PERIOD_NUM,
1416 pv_calendar_id,
1417 G.DIMENSION_GROUP_ID
1418 ) CAL_PERIOD_ID,
1419 G.DIMENSION_GROUP_ID,
1420 P.PERIOD_NAME,
1421 P.ENTERED_PERIOD_NAME,
1422 P.ADJUSTMENT_PERIOD_FLAG,
1423 P.START_DATE,
1424 P.END_DATE,
1425 P.PERIOD_NUM,
1426 P.QUARTER_NUM,
1427 pv_period_year PERIOD_YEAR
1428 FROM
1429 GL_PERIODS P,
1430 FEM_DIMENSION_GRPS_B G
1431 WHERE
1432 P.PERIOD_SET_NAME = pv_period_set_name AND
1433 P.PERIOD_TYPE = pv_period_type AND
1434 P.PERIOD_YEAR = pv_period_year AND
1435 G.DIMENSION_GROUP_ID = pv_dimension_grp_id_period
1436 UNION ALL
1437 SELECT -- Quater members
1438 FEM_DIMENSION_UTIL_PKG.Generate_Member_ID(
1439 Q.END_DATE,
1440 Q.QUARTER_NUM,
1441 pv_calendar_id,
1442 pv_dimension_grp_id_quarter
1443 ) CAL_PERIOD_ID,
1444 pv_dimension_grp_id_quarter,
1445 'Q' || Q.QUARTER_NUM || '-' || SUBSTR(pv_period_year, length(pv_period_year)-1, 2),
1446 'Q' || Q.QUARTER_NUM || '-' || SUBSTR(pv_period_year, length(pv_period_year)-1, 2),
1447 --dedutta : removed decode for fixed value N : 4970174
1448 'N' as ADJUSTMENT_PERIOD_FLAG,
1449 Q.START_DATE,
1450 Q.END_DATE,
1451 Q.QUARTER_NUM, -- Bug 4523730 hkaniven --
1452 Q.QUARTER_NUM,
1453 Q.PERIOD_YEAR
1454 FROM
1455 (
1456 SELECT
1457 pv_period_year PERIOD_YEAR,
1458 P.QUARTER_NUM,
1459 --dedutta : removed AP decode 4970174
1460 MIN(P.START_DATE) START_DATE,
1461 MAX(P.END_DATE) END_DATE
1462 FROM
1463 GL_PERIODS P
1464 WHERE
1465 P.PERIOD_SET_NAME = pv_period_set_name AND
1466 P.PERIOD_TYPE = pv_period_type AND
1467 P.PERIOD_YEAR = pv_period_year
1468 GROUP BY P.QUARTER_NUM
1469 ) Q,
1470 GL_PERIOD_TYPES PT
1471 WHERE
1472 PT.PERIOD_TYPE = pv_period_type
1473 UNION ALL
1474 SELECT -- Year members
1475 FEM_DIMENSION_UTIL_PKG.Generate_Member_ID(
1476 Y.END_DATE,
1477 1, -- Bug 4523730 hkaniven --
1478 pv_calendar_id,
1479 pv_dimension_grp_id_year
1480 ) CAL_PERIOD_ID,
1481 pv_dimension_grp_id_year,
1482 to_char(pv_period_year),
1483 to_char(pv_period_year),
1484 --dedutta : removed decode for fixed value N : 4970174
1485 'N'as ADJUSTMENT_PERIOD_FLAG,
1486 Y.START_DATE,
1487 Y.END_DATE,
1488 1, -- Bug 4523730 hkaniven --
1489 Y.QUARTER_NUM,
1490 Y.PERIOD_YEAR
1491 FROM
1492 (
1493 SELECT
1494 pv_period_year PERIOD_YEAR,
1495 --dedutta : removed AP decode 4970174
1496 MIN(P.START_DATE) START_DATE,
1497 MAX(P.END_DATE) END_DATE,
1498 MIN(P.QUARTER_NUM) QUARTER_NUM
1499 FROM
1500 GL_PERIODS P
1501 WHERE
1502 P.PERIOD_SET_NAME = pv_period_set_name AND
1503 P.PERIOD_TYPE = pv_period_type AND
1504 P.PERIOD_YEAR = pv_period_year
1505 ) Y,
1506 GL_PERIOD_TYPES PT
1507 WHERE
1508 PT.PERIOD_TYPE = pv_period_type;
1509
1510 pv_row_count_tot := pv_row_count_tot + SQL%ROWCOUNT;
1511
1512 FEM_ENGINES_PKG.Tech_Message(
1513 p_severity => pc_log_level_procedure,
1514 p_module => pc_module_name || 'generate_member_ids.end',
1515 p_app_name => 'FEM',
1516 p_msg_name => 'FEM_GL_POST_202',
1517 p_token1 => 'FUNC_NAME',
1518 p_value1 => 'FEM_INTG_CAL_RULE_ENG_PKG.Generate_Member_IDs',
1519 p_token2 => 'TIME',
1520 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
1521 );
1522
1523 EXCEPTION
1524 WHEN OTHERS THEN
1525 ROLLBACK;
1526
1527 FEM_ENGINES_PKG.Tech_Message(
1528 p_severity => pc_log_level_exception,
1529 p_module => pc_module_name || 'generate_member_ids.exception_others' ,
1530 p_app_name => 'FEM',
1531 p_msg_name => 'FEM_GL_POST_215',
1532 p_token1 => 'ERR_MSG',
1533 p_value1 => SQLERRM
1534 );
1535
1536 FEM_ENGINES_PKG.User_Message(
1537 p_app_name => 'FEM',
1538 p_msg_name => 'FEM_GL_POST_215',
1539 p_token1 => 'ERR_MSG',
1540 p_value1 => SQLERRM
1541 );
1542
1543 FEM_ENGINES_PKG.Tech_Message(
1544 p_severity => pc_log_level_exception,
1545 p_module => pc_module_name || 'generate_member_ids.exception_others',
1546 p_app_name => 'FEM',
1547 p_msg_name => 'FEM_GL_POST_203',
1548 p_token1 => 'FUNC_NAME',
1549 p_value1 => 'FEM_INTG_CAL_RULE_ENG_PKG.Generate_Member_IDs',
1550 p_token2 => 'TIME',
1551 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
1552 );
1553
1554 RAISE;
1555
1556 END Generate_Member_IDs;
1557
1558
1559 -- ======================================================================
1560 -- Procedure
1561 -- Populate_Time_Dimension
1562 -- Purpose
1563 -- Populates Time Dimension member and attribute tables
1564 --
1565 -- The following Time Dimension attributes are populated:
1566 -- 'ACCOUNTING_YEAR',
1567 -- 'ADJ_PERIOD_FLAG',
1568 -- 'CAL_PERIOD_END_DATE',
1569 -- 'CAL_PERIOD_PREFIX',
1570 -- 'CAL_PERIOD_START_DATE',
1571 -- 'CUR_PERIOD_FLAG',
1572 -- 'GL_ORIGIN_FLAG',
1573 -- 'GL_PERIOD_NUM',
1574 -- 'SOURCE_SYSTEM_CODE'
1575 -- History
1576 -- 02-02-05 Shintaro Okuda Created
1577 -- 07-15-05 Harikiran Bug 4486878 - Populated Dimension_group_id
1578 -- 08-29-05 Harikiran Bug 4350620
1579 -- - Get the calendar period hierarchy name
1580 -- and prefix it while inserting the
1581 -- calendar period name and description into
1582 -- the fem_cal_periods_tl table
1583 -- Arguments
1584 -- None
1585 -- ======================================================================
1586 PROCEDURE Populate_Time_Dimension
1587 IS
1588 v_err_code NUMBER;
1589 v_num_msg NUMBER;
1590 BEGIN
1591
1592 FEM_ENGINES_PKG.Tech_Message(
1593 p_severity => pc_log_level_procedure,
1594 p_module => pc_module_name || 'populate_time_dimension.begin',
1595 p_app_name => 'FEM',
1596 p_msg_name => 'FEM_GL_POST_201',
1597 p_token1 => 'FUNC_NAME',
1598 p_value1 => 'FEM_INTG_CAL_RULE_ENG_PKG.Populate_Time_Dimension',
1599 p_token2 => 'TIME',
1600 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
1601 );
1602
1603 MERGE INTO FEM_CAL_PERIODS_B B
1604 USING (
1605 SELECT
1606 GT.CAL_PERIOD_ID CAL_PERIOD_ID,
1607 GT.DIMENSION_GROUP_ID DIMENSION_GROUP_ID,
1608 pv_calendar_id CALENDAR_ID,
1609 'Y' ENABLED_FLAG,
1610 'N' PERSONAL_FLAG,
1611 CASE
1612 WHEN GT.DIMENSION_GROUP_ID = pv_dimension_grp_id_period THEN 'Y'
1613 ELSE 'N'
1614 END READ_ONLY_FLAG,
1615 pc_object_version_number OBJECT_VERSION_NUMBER,
1616 SYSDATE CREATION_DATE,
1617 pv_user_id CREATED_BY,
1618 SYSDATE LAST_UPDATE_DATE,
1619 pv_user_id LAST_UPDATED_BY,
1620 pv_login_id LAST_UPDATE_LOGIN
1621 FROM
1622 FEM_INTG_CAL_PERIODS_GT GT
1623 ) S
1624 ON (
1625 B.CAL_PERIOD_ID = S.CAL_PERIOD_ID
1626 )
1627 WHEN MATCHED THEN UPDATE
1628 SET B.LAST_UPDATE_DATE = SYSDATE
1629 WHEN NOT MATCHED THEN INSERT(
1630 B.CAL_PERIOD_ID,
1631 B.DIMENSION_GROUP_ID,
1632 B.CALENDAR_ID,
1633 B.ENABLED_FLAG,
1634 B.PERSONAL_FLAG,
1635 B.READ_ONLY_FLAG,
1636 B.OBJECT_VERSION_NUMBER,
1637 B.CREATION_DATE,
1638 B.CREATED_BY,
1639 B.LAST_UPDATE_DATE,
1640 B.LAST_UPDATED_BY,
1641 B.LAST_UPDATE_LOGIN
1642 ) VALUES (
1643 S.CAL_PERIOD_ID,
1644 S.DIMENSION_GROUP_ID,
1645 S.CALENDAR_ID,
1646 S.ENABLED_FLAG,
1647 S.PERSONAL_FLAG,
1648 S.READ_ONLY_FLAG,
1649 S.OBJECT_VERSION_NUMBER,
1650 S.CREATION_DATE,
1651 S.CREATED_BY,
1652 S.LAST_UPDATE_DATE,
1653 S.LAST_UPDATED_BY,
1654 S.LAST_UPDATE_LOGIN
1655 );
1656
1657 pv_row_count_tot := pv_row_count_tot + SQL%ROWCOUNT;
1658
1659 --
1660 -- bug4335312: a source for DESCRIPTION column has been changed from
1661 -- GT.ENTERED_PERIOD_NAME to GT.PERIOD_NAME
1662 --
1663
1664 --
1665 -- Bug 4486878: Insert a not null value to Dimension_group_id
1666 --
1667
1668 --
1669 -- Bug 4350620 hkaniven --
1670 -- Prefix the calendar period hierarchy name to the period_name and description
1671 -- while inserting into the fem_cal_periods_tl table
1672 --
1673
1674
1675 MERGE INTO FEM_CAL_PERIODS_TL TL
1676 USING (
1677 SELECT
1678 GT.CAL_PERIOD_ID CAL_PERIOD_ID,
1679 DECODE(GT.DIMENSION_GROUP_ID,
1680 70, pv_cal_per_hier_name || '-' || GT.PERIOD_NAME,
1681 10, pv_cal_per_hier_name || '-' || GT.PERIOD_NAME,
1682 GT.PERIOD_NAME) CAL_PERIOD_NAME, -- Bug 4350620 hkaniven --
1683 GT.DIMENSION_GROUP_ID DIMENSION_GROUP_ID, -- Bug 4486878 --
1684 DECODE(GT.DIMENSION_GROUP_ID,
1685 70, pv_cal_per_hier_name || '-' || GT.PERIOD_NAME,
1686 10, pv_cal_per_hier_name || '-' || GT.PERIOD_NAME,
1687 GT.PERIOD_NAME) DESCRIPTION, -- Bug 4350620 hkaniven --
1688 pv_calendar_id CALENDAR_ID,
1689 L.LANGUAGE_CODE LANGUAGE,
1690 L.LANGUAGE_CODE SOURCE_LANG,
1691 SYSDATE CREATION_DATE,
1692 pv_user_id CREATED_BY,
1693 SYSDATE LAST_UPDATE_DATE,
1694 pv_user_id LAST_UPDATED_BY,
1695 pv_login_id LAST_UPDATE_LOGIN
1696 FROM
1697 FEM_INTG_CAL_PERIODS_GT GT,
1698 FND_LANGUAGES L
1699 WHERE
1700 L.INSTALLED_FLAG IN ('B','I')
1701 ) S
1702 ON (
1703 TL.CAL_PERIOD_ID = S.CAL_PERIOD_ID AND
1704 TL.LANGUAGE = S.LANGUAGE
1705 )
1706 WHEN MATCHED THEN UPDATE
1707 SET TL.LAST_UPDATE_DATE = SYSDATE
1708 WHEN NOT MATCHED THEN INSERT(
1709 TL.CAL_PERIOD_ID,
1710 TL.CAL_PERIOD_NAME,
1711 TL.DIMENSION_GROUP_ID, -- Bug 4486878 --
1712 TL.DESCRIPTION,
1713 TL.CALENDAR_ID,
1714 TL.LANGUAGE,
1715 TL.SOURCE_LANG,
1716 TL.CREATION_DATE,
1717 TL.CREATED_BY,
1718 TL.LAST_UPDATE_DATE,
1719 TL.LAST_UPDATED_BY,
1720 TL.LAST_UPDATE_LOGIN
1721 ) VALUES (
1722 S.CAL_PERIOD_ID,
1723 S.CAL_PERIOD_NAME,
1724 S.DIMENSION_GROUP_ID, -- Bug 4486878 --
1725 S.DESCRIPTION,
1726 S.CALENDAR_ID,
1727 S.LANGUAGE,
1728 S.SOURCE_LANG,
1729 S.CREATION_DATE,
1730 S.CREATED_BY,
1731 S.LAST_UPDATE_DATE,
1732 S.LAST_UPDATED_BY,
1733 S.LAST_UPDATE_LOGIN
1734 );
1735
1736 pv_row_count_tot := pv_row_count_tot + SQL%ROWCOUNT;
1737
1738 MERGE INTO FEM_CAL_PERIODS_ATTR ATTR
1739 USING (
1740 SELECT
1741 A.ATTRIBUTE_ID ATTRIBUTE_ID,
1742 V.VERSION_ID VERSION_ID,
1743 GT.CAL_PERIOD_ID CAL_PERIOD_ID,
1744 DECODE(
1745 A.ATTRIBUTE_VARCHAR_LABEL,
1746 'SOURCE_SYSTEM_CODE',NULL,
1747 'ADJ_PERIOD_FLAG', NULL,
1748 'GL_ORIGIN_FLAG', NULL,
1749 NULL
1750 ) DIM_ATTRIBUTE_VALUE_SET_ID,
1751 DECODE(
1752 A.ATTRIBUTE_VARCHAR_LABEL,
1753 'SOURCE_SYSTEM_CODE', pv_source_system_code,
1754 NULL
1755 ) DIM_ATTRIBUTE_NUMERIC_MEMBER,
1756 DECODE(
1757 A.ATTRIBUTE_VARCHAR_LABEL,
1758 'ADJ_PERIOD_FLAG', GT.ADJUSTMENT_PERIOD_FLAG,
1759 'CUR_PERIOD_FLAG', 'N',
1760 'GL_ORIGIN_FLAG', 'Y',
1761 NULL
1762 ) DIM_ATTRIBUTE_VARCHAR_MEMBER,
1763 DECODE(
1764 A.ATTRIBUTE_VARCHAR_LABEL,
1765 'ACCOUNTING_YEAR', GT.PERIOD_YEAR,
1766 'GL_PERIOD_NUM', GT.PERIOD_NUM,
1767 NULL
1768 ) NUMBER_ASSIGN_VALUE,
1769 DECODE(
1770 A.ATTRIBUTE_VARCHAR_LABEL,
1771 'CAL_PERIOD_PREFIX', GT.ENTERED_PERIOD_NAME,
1772 NULL
1773 ) VARCHAR_ASSIGN_VALUE,
1774 DECODE(
1775 A.ATTRIBUTE_VARCHAR_LABEL,
1776 'CAL_PERIOD_START_DATE', GT.START_DATE,
1777 'CAL_PERIOD_END_DATE', GT.END_DATE,
1778 NULL
1779 ) DATE_ASSIGN_VALUE,
1780 pc_object_version_number OBJECT_VERSION_NUMBER,
1781 pc_aw_snapshot_flag AW_SNAPSHOT_FLAG,
1782 SYSDATE CREATION_DATE,
1783 pv_user_id CREATED_BY,
1784 SYSDATE LAST_UPDATE_DATE,
1785 pv_user_id LAST_UPDATED_BY,
1786 pv_login_id LAST_UPDATE_LOGIN
1787 FROM
1788 FEM_INTG_CAL_PERIODS_GT GT,
1789 FEM_DIM_ATTRIBUTES_B A,
1790 FEM_DIM_ATTR_VERSIONS_B V
1791 WHERE
1792 A.DIMENSION_ID = pv_cal_period_dim_id AND
1793 V.ATTRIBUTE_ID = A.ATTRIBUTE_ID AND
1794 V.DEFAULT_VERSION_FLAG = 'Y' AND
1795 A.ATTRIBUTE_VARCHAR_LABEL IN (
1796 'ACCOUNTING_YEAR',
1797 'ADJ_PERIOD_FLAG',
1798 'CAL_PERIOD_END_DATE',
1799 'CAL_PERIOD_PREFIX',
1800 'CAL_PERIOD_START_DATE',
1801 'CUR_PERIOD_FLAG',
1802 'GL_ORIGIN_FLAG',
1803 'GL_PERIOD_NUM',
1804 'SOURCE_SYSTEM_CODE'
1805 )
1806 ) S
1807 ON (
1808 ATTR.ATTRIBUTE_ID = S.ATTRIBUTE_ID AND
1809 ATTR.VERSION_ID = S.VERSION_ID AND
1810 ATTR.CAL_PERIOD_ID = S.CAL_PERIOD_ID AND
1811 NVL(ATTR.DIM_ATTRIBUTE_NUMERIC_MEMBER, -1) =
1812 NVL(S.DIM_ATTRIBUTE_NUMERIC_MEMBER, -1) AND
1813 NVL(ATTR.DIM_ATTRIBUTE_VARCHAR_MEMBER, 'NULL') =
1814 NVL(S.DIM_ATTRIBUTE_VARCHAR_MEMBER, 'NULL')
1815 )
1816 WHEN MATCHED THEN UPDATE
1817 SET ATTR.LAST_UPDATE_DATE = SYSDATE
1818 WHEN NOT MATCHED THEN INSERT(
1819 ATTR.ATTRIBUTE_ID,
1820 ATTR.VERSION_ID,
1821 ATTR.CAL_PERIOD_ID,
1822 ATTR.DIM_ATTRIBUTE_NUMERIC_MEMBER,
1823 ATTR.DIM_ATTRIBUTE_VALUE_SET_ID,
1824 ATTR.DIM_ATTRIBUTE_VARCHAR_MEMBER,
1825 ATTR.NUMBER_ASSIGN_VALUE,
1826 ATTR.VARCHAR_ASSIGN_VALUE,
1827 ATTR.DATE_ASSIGN_VALUE,
1828 ATTR.OBJECT_VERSION_NUMBER,
1829 ATTR.AW_SNAPSHOT_FLAG,
1830 ATTR.CREATION_DATE,
1831 ATTR.CREATED_BY,
1832 ATTR.LAST_UPDATE_DATE,
1833 ATTR.LAST_UPDATED_BY,
1834 ATTR.LAST_UPDATE_LOGIN
1835 ) VALUES (
1836 S.ATTRIBUTE_ID,
1837 S.VERSION_ID,
1838 S.CAL_PERIOD_ID,
1839 S.DIM_ATTRIBUTE_NUMERIC_MEMBER,
1840 S.DIM_ATTRIBUTE_VALUE_SET_ID,
1841 S.DIM_ATTRIBUTE_VARCHAR_MEMBER,
1842 S.NUMBER_ASSIGN_VALUE,
1843 S.VARCHAR_ASSIGN_VALUE,
1844 S.DATE_ASSIGN_VALUE,
1845 S.OBJECT_VERSION_NUMBER,
1846 S.AW_SNAPSHOT_FLAG,
1847 S.CREATION_DATE,
1848 S.CREATED_BY,
1849 S.LAST_UPDATE_DATE,
1850 S.LAST_UPDATED_BY,
1851 S.LAST_UPDATE_LOGIN
1852 );
1853
1854 pv_row_count_tot := pv_row_count_tot + SQL%ROWCOUNT;
1855
1856 FEM_ENGINES_PKG.Tech_Message(
1857 p_severity => pc_log_level_procedure,
1858 p_module => pc_module_name || 'populate_time_dimension.end',
1859 p_app_name => 'FEM',
1860 p_msg_name => 'FEM_GL_POST_202',
1861 p_token1 => 'FUNC_NAME',
1862 p_value1 => 'FEM_INTG_CAL_RULE_ENG_PKG.Populate_Time_Dimension',
1863 p_token2 => 'TIME',
1864 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
1865 );
1866
1867 EXCEPTION
1868 WHEN OTHERS THEN
1869 ROLLBACK;
1870
1871 FEM_ENGINES_PKG.Tech_Message(
1872 p_severity => pc_log_level_exception,
1873 p_module => pc_module_name || 'populate_time_dimension.exception_others' ,
1874 p_app_name => 'FEM',
1875 p_msg_name => 'FEM_GL_POST_215',
1876 p_token1 => 'ERR_MSG',
1877 p_value1 => SQLERRM
1878 );
1879
1880 FEM_ENGINES_PKG.User_Message(
1881 p_app_name => 'FEM',
1882 p_msg_name => 'FEM_GL_POST_215',
1883 p_token1 => 'ERR_MSG',
1884 p_value1 => SQLERRM
1885 );
1886
1887 FEM_ENGINES_PKG.Tech_Message(
1888 p_severity => pc_log_level_exception,
1889 p_module => pc_module_name || 'populate_time_dimension.exception_others',
1890 p_app_name => 'FEM',
1891 p_msg_name => 'FEM_GL_POST_203',
1892 p_token1 => 'FUNC_NAME',
1893 p_value1 => 'FEM_INTG_CAL_RULE_ENG_PKG.Populate_Time_Dimension',
1894 p_token2 => 'TIME',
1895 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
1896 );
1897
1898 RAISE;
1899
1900 END Populate_Time_Dimension;
1901
1902
1903 -- ======================================================================
1904 -- Procedure
1905 -- Populate_Time_Hierarchy
1906 -- Purpose
1907 -- Populates Time Dimension Hierarchy
1908 --
1909 -- Note that if full table scan in accessing a global temporary table
1910 -- twice causes a performance issue, an alternative is to generate
1911 -- Calendar Period ID using the FEM API when populating the Time Hierarchy
1912 -- table.
1913 --
1914 -- History
1915 -- 02-02-05 Shintaro Okuda Created
1916 -- Arguments
1917 -- None
1918 -- ======================================================================
1919 PROCEDURE Populate_Time_Hierarchy
1920 IS
1921 v_err_code NUMBER;
1922 v_num_msg NUMBER;
1923 BEGIN
1924
1925 FEM_ENGINES_PKG.Tech_Message(
1926 p_severity => pc_log_level_procedure,
1927 p_module => pc_module_name || 'populate_time_hierarchy.begin',
1928 p_app_name => 'FEM',
1929 p_msg_name => 'FEM_GL_POST_201',
1930 p_token1 => 'FUNC_NAME',
1931 p_value1 => 'FEM_INTG_CAL_RULE_ENG_PKG.Populate_Time_Hierarchy',
1932 p_token2 => 'TIME',
1933 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
1934 );
1935
1936 --
1937 -- A record is created only if there is no existing relationship for a
1938 -- given child.
1939 --
1940 MERGE INTO FEM_CAL_PERIODS_HIER H
1941 USING (
1942 SELECT -- Termination points (Year)
1943 pv_cal_per_hier_obj_def_id HIERARCHY_OBJ_DEF_ID,
1944 1 PARENT_DEPTH_NUM,
1945 GT.CAL_PERIOD_ID PARENT_ID,
1946 1 CHILD_DEPTH_NUM,
1947 GT.CAL_PERIOD_ID CHILD_ID,
1948 'Y' SINGLE_DEPTH_FLAG,
1949 GT.PERIOD_YEAR DISPLAY_ORDER_NUM,
1950 pc_weighting_pct WEIGHTING_PCT,
1951 pc_object_version_number OBJECT_VERSION_NUMBER,
1952 'Y' READ_ONLY_FLAG,
1953 SYSDATE CREATION_DATE,
1954 pv_user_id CREATED_BY,
1955 SYSDATE LAST_UPDATE_DATE,
1956 pv_user_id LAST_UPDATED_BY,
1957 pv_login_id LAST_UPDATE_LOGIN
1958 FROM
1959 FEM_INTG_CAL_PERIODS_GT GT
1960 WHERE
1961 GT.DIMENSION_GROUP_ID = pv_dimension_grp_id_year
1962 UNION ALL
1963 SELECT -- Year and Quarter
1964 pv_cal_per_hier_obj_def_id HIERARCHY_OBJ_DEF_ID,
1965 1 PARENT_DEPTH_NUM,
1966 Y.CAL_PERIOD_ID PARENT_ID,
1967 2 CHILD_DEPTH_NUM,
1968 Q.CAL_PERIOD_ID CHILD_ID,
1969 'Y' SINGLE_DEPTH_FLAG,
1970 Q.QUARTER_NUM DISPLAY_ORDER_NUM,
1971 pc_weighting_pct WEIGHTING_PCT,
1972 pc_object_version_number OBJECT_VERSION_NUMBER,
1973 'Y' READ_ONLY_FLAG,
1974 SYSDATE CREATION_DATE,
1975 pv_user_id CREATED_BY,
1976 SYSDATE LAST_UPDATE_DATE,
1977 pv_user_id LAST_UPDATED_BY,
1978 pv_login_id LAST_UPDATE_LOGIN
1979 FROM
1980 FEM_INTG_CAL_PERIODS_GT Y,
1981 FEM_INTG_CAL_PERIODS_GT Q
1982 WHERE
1983 Y.DIMENSION_GROUP_ID = pv_dimension_grp_id_year AND
1984 Q.DIMENSION_GROUP_ID = pv_dimension_grp_id_quarter AND
1985 Y.PERIOD_YEAR = Q.PERIOD_YEAR
1986 ) S
1987 ON (
1988 H.HIERARCHY_OBJ_DEF_ID = S.HIERARCHY_OBJ_DEF_ID AND
1989 H.CHILD_ID = S.CHILD_ID
1990 )
1991 WHEN MATCHED THEN UPDATE
1992 SET H.LAST_UPDATE_DATE = SYSDATE
1993 WHEN NOT MATCHED THEN INSERT(
1994 H.HIERARCHY_OBJ_DEF_ID,
1995 H.PARENT_DEPTH_NUM,
1996 H.PARENT_ID,
1997 H.CHILD_DEPTH_NUM,
1998 H.CHILD_ID,
1999 H.SINGLE_DEPTH_FLAG,
2000 H.DISPLAY_ORDER_NUM,
2001 H.WEIGHTING_PCT,
2002 H.OBJECT_VERSION_NUMBER,
2003 H.READ_ONLY_FLAG,
2004 H.CREATION_DATE,
2005 H.CREATED_BY,
2006 H.LAST_UPDATE_DATE,
2007 H.LAST_UPDATED_BY,
2008 H.LAST_UPDATE_LOGIN
2009 ) VALUES (
2010 S.HIERARCHY_OBJ_DEF_ID,
2011 S.PARENT_DEPTH_NUM,
2012 S.PARENT_ID,
2013 S.CHILD_DEPTH_NUM,
2014 S.CHILD_ID,
2015 S.SINGLE_DEPTH_FLAG,
2016 S.DISPLAY_ORDER_NUM,
2017 S.WEIGHTING_PCT,
2018 S.OBJECT_VERSION_NUMBER,
2019 S.READ_ONLY_FLAG,
2020 S.CREATION_DATE,
2021 S.CREATED_BY,
2022 S.LAST_UPDATE_DATE,
2023 S.LAST_UPDATED_BY,
2024 S.LAST_UPDATE_LOGIN
2025 );
2026
2027 pv_row_count_tot := pv_row_count_tot + SQL%ROWCOUNT;
2028
2029 --
2030 -- A record is created only if there is no existing relationship for a
2031 -- given child.
2032 --
2033 MERGE INTO FEM_CAL_PERIODS_HIER H
2034 USING (
2035 SELECT
2036 pv_cal_per_hier_obj_def_id HIERARCHY_OBJ_DEF_ID,
2037 2 PARENT_DEPTH_NUM,
2038 Q.CAL_PERIOD_ID PARENT_ID,
2039 3 CHILD_DEPTH_NUM,
2040 P.CAL_PERIOD_ID CHILD_ID,
2041 'Y' SINGLE_DEPTH_FLAG,
2042 P.PERIOD_NUM DISPLAY_ORDER_NUM,
2043 pc_weighting_pct WEIGHTING_PCT,
2044 pc_object_version_number OBJECT_VERSION_NUMBER,
2045 'N' READ_ONLY_FLAG,
2046 SYSDATE CREATION_DATE,
2047 pv_user_id CREATED_BY,
2048 SYSDATE LAST_UPDATE_DATE,
2049 pv_user_id LAST_UPDATED_BY,
2050 pv_login_id LAST_UPDATE_LOGIN
2051 FROM
2052 FEM_INTG_CAL_PERIODS_GT P,
2053 FEM_INTG_CAL_PERIODS_GT Q
2054 WHERE
2055 P.DIMENSION_GROUP_ID = pv_dimension_grp_id_period AND
2056 Q.DIMENSION_GROUP_ID = pv_dimension_grp_id_quarter AND
2057 Q.QUARTER_NUM = P.QUARTER_NUM
2058 ) S
2059 ON (
2060 H.HIERARCHY_OBJ_DEF_ID = S.HIERARCHY_OBJ_DEF_ID AND
2061 H.CHILD_ID = S.CHILD_ID
2062 )
2063 WHEN MATCHED THEN UPDATE
2064 SET H.LAST_UPDATE_DATE = SYSDATE
2065 WHEN NOT MATCHED THEN INSERT(
2066 H.HIERARCHY_OBJ_DEF_ID,
2067 H.PARENT_DEPTH_NUM,
2068 H.PARENT_ID,
2069 H.CHILD_DEPTH_NUM,
2070 H.CHILD_ID,
2071 H.SINGLE_DEPTH_FLAG,
2072 H.DISPLAY_ORDER_NUM,
2073 H.WEIGHTING_PCT,
2074 H.OBJECT_VERSION_NUMBER,
2075 H.READ_ONLY_FLAG,
2076 H.CREATION_DATE,
2077 H.CREATED_BY,
2078 H.LAST_UPDATE_DATE,
2079 H.LAST_UPDATED_BY,
2080 H.LAST_UPDATE_LOGIN
2081 ) VALUES (
2082 S.HIERARCHY_OBJ_DEF_ID,
2083 S.PARENT_DEPTH_NUM,
2084 S.PARENT_ID,
2085 S.CHILD_DEPTH_NUM,
2086 S.CHILD_ID,
2087 S.SINGLE_DEPTH_FLAG,
2088 S.DISPLAY_ORDER_NUM,
2089 S.WEIGHTING_PCT,
2090 S.OBJECT_VERSION_NUMBER,
2091 S.READ_ONLY_FLAG,
2092 S.CREATION_DATE,
2093 S.CREATED_BY,
2094 S.LAST_UPDATE_DATE,
2095 S.LAST_UPDATED_BY,
2096 S.LAST_UPDATE_LOGIN
2097 );
2098
2099 pv_row_count_tot := pv_row_count_tot + SQL%ROWCOUNT;
2100
2101 MERGE INTO FEM_HIER_VALUE_SETS V
2102 USING DUAL
2103 ON (
2104 V.HIERARCHY_OBJ_ID = pv_cal_per_hier_obj_id AND
2105 V.VALUE_SET_ID = pv_calendar_id
2106 )
2107 WHEN MATCHED THEN UPDATE
2108 SET V.LAST_UPDATE_DATE = SYSDATE
2109 WHEN NOT MATCHED THEN INSERT (
2110 V.HIERARCHY_OBJ_ID,
2111 V.VALUE_SET_ID,
2112 V.OBJECT_VERSION_NUMBER,
2113 V.CREATION_DATE,
2114 V.CREATED_BY,
2115 V.LAST_UPDATE_DATE,
2116 V.LAST_UPDATED_BY,
2117 V.LAST_UPDATE_LOGIN
2118 ) VALUES (
2119 pv_cal_per_hier_obj_id,
2120 pv_calendar_id,
2121 pc_object_version_number,
2122 SYSDATE,
2123 pv_user_id,
2124 SYSDATE,
2125 pv_user_id,
2126 pv_login_id
2127 );
2128
2129 pv_row_count_tot := pv_row_count_tot + SQL%ROWCOUNT;
2130
2131 FEM_ENGINES_PKG.Tech_Message(
2132 p_severity => pc_log_level_procedure,
2133 p_module => pc_module_name || 'populate_time_hierarchy.end',
2134 p_app_name => 'FEM',
2135 p_msg_name => 'FEM_GL_POST_202',
2136 p_token1 => 'FUNC_NAME',
2137 p_value1 => 'FEM_INTG_CAL_RULE_ENG_PKG.Populate_Time_Hierarchy',
2138 p_token2 => 'TIME',
2139 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
2140 );
2141
2142 EXCEPTION
2143 WHEN OTHERS THEN
2144 ROLLBACK;
2145
2146 FEM_ENGINES_PKG.Tech_Message(
2147 p_severity => pc_log_level_exception,
2148 p_module => pc_module_name || 'populate_time_hierarchy.exception_others' ,
2149 p_app_name => 'FEM',
2150 p_msg_name => 'FEM_GL_POST_215',
2151 p_token1 => 'ERR_MSG',
2152 p_value1 => SQLERRM
2153 );
2154
2155 FEM_ENGINES_PKG.User_Message(
2156 p_app_name => 'FEM',
2157 p_msg_name => 'FEM_GL_POST_215',
2158 p_token1 => 'ERR_MSG',
2159 p_value1 => SQLERRM
2160 );
2161
2162 FEM_ENGINES_PKG.Tech_Message(
2163 p_severity => pc_log_level_exception,
2164 p_module => pc_module_name || 'populate_time_hierarchy.exception_others',
2165 p_app_name => 'FEM',
2166 p_msg_name => 'FEM_GL_POST_203',
2167 p_token1 => 'FUNC_NAME',
2168 p_value1 => 'FEM_INTG_CAL_RULE_ENG_PKG.Populate_Time_Hierarchy',
2169 p_token2 => 'TIME',
2170 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
2171 );
2172
2173 RAISE;
2174
2175 END Populate_Time_Hierarchy;
2176
2177
2178 -- ======================================================================
2179 -- Procedure
2180 -- Update_Calenar_Map
2181 -- Purpose
2182 -- Updates Calendar Map table
2183 -- History
2184 -- 02-02-05 Shintaro Okuda Created
2185 -- Arguments
2186 -- None
2187 -- ======================================================================
2188 PROCEDURE Update_Calendar_Map
2189 IS
2190 BEGIN
2191
2192 FEM_ENGINES_PKG.Tech_Message(
2193 p_severity => pc_log_level_procedure,
2194 p_module => pc_module_name || 'update_calendar_map.begin',
2195 p_app_name => 'FEM',
2196 p_msg_name => 'FEM_GL_POST_201',
2197 p_token1 => 'FUNC_NAME',
2198 p_value1 => 'FEM_INTG_CAL_RULE_ENG_PKG.Update_Calendar_Map',
2199 p_token2 => 'TIME',
2200 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
2201 );
2202
2203 UPDATE FEM_INTG_CALENDAR_MAP
2204 SET EFFECTIVE_PERIOD_NUM_MIN = pv_effective_period_num_low,
2205 LAST_UPDATE_DATE = SYSDATE,
2206 LAST_UPDATED_BY = pv_user_id,
2207 LAST_UPDATE_LOGIN = pv_login_id
2208 WHERE CAL_PER_HIER_OBJ_DEF_ID = pv_cal_per_hier_obj_def_id
2209 AND pv_effective_period_num_low < NVL(EFFECTIVE_PERIOD_NUM_MIN,3000*10000);
2210
2211 pv_row_count_tot := pv_row_count_tot + SQL%ROWCOUNT;
2212
2213 UPDATE FEM_INTG_CALENDAR_MAP
2214 SET EFFECTIVE_PERIOD_NUM_MAX = pv_effective_period_num_high,
2215 LAST_UPDATE_DATE = SYSDATE,
2216 LAST_UPDATED_BY = pv_user_id,
2217 LAST_UPDATE_LOGIN = pv_login_id
2218 WHERE CAL_PER_HIER_OBJ_DEF_ID = pv_cal_per_hier_obj_def_id
2219 AND pv_effective_period_num_high > NVL(EFFECTIVE_PERIOD_NUM_MAX, 0);
2220
2221 pv_row_count_tot := pv_row_count_tot + SQL%ROWCOUNT;
2222
2223 FEM_ENGINES_PKG.Tech_Message(
2224 p_severity => pc_log_level_procedure,
2225 p_module => pc_module_name || 'update_calendar_map.end',
2226 p_app_name => 'FEM',
2227 p_msg_name => 'FEM_GL_POST_202',
2228 p_token1 => 'FUNC_NAME',
2229 p_value1 => 'FEM_INTG_CAL_RULE_ENG_PKG.Update_Calendar_Map',
2230 p_token2 => 'TIME',
2231 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
2232 );
2233
2234 EXCEPTION
2235 WHEN OTHERS THEN
2236 ROLLBACK;
2237
2238 FEM_ENGINES_PKG.Tech_Message(
2239 p_severity => pc_log_level_exception,
2240 p_module => pc_module_name || 'update_calendar_map.exception_others' ,
2241 p_app_name => 'FEM',
2242 p_msg_name => 'FEM_GL_POST_215',
2243 p_token1 => 'ERR_MSG',
2244 p_value1 => SQLERRM
2245 );
2246
2247 FEM_ENGINES_PKG.User_Message(
2248 p_app_name => 'FEM',
2249 p_msg_name => 'FEM_GL_POST_215',
2250 p_token1 => 'ERR_MSG',
2251 p_value1 => SQLERRM
2252 );
2253
2254 FEM_ENGINES_PKG.Tech_Message(
2255 p_severity => pc_log_level_exception,
2256 p_module => pc_module_name || 'update_calendar_map.exception_others',
2257 p_app_name => 'FEM',
2258 p_msg_name => 'FEM_GL_POST_203',
2259 p_token1 => 'FUNC_NAME',
2260 p_value1 => 'FEM_INTG_CAL_RULE_ENG_PKG.Update_Calendar_Map',
2261 p_token2 => 'TIME',
2262 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
2263 );
2264
2265 RAISE;
2266
2267 END Update_Calendar_Map;
2268
2269
2270 -- ======================================================================
2271 -- Procedure
2272 -- Print_Package_Variables
2273 -- Purpose
2274 -- Prints package variables in debug message
2275 -- History
2276 -- 02-02-05 Shintaro Okuda Created
2277 -- Arguments
2278 -- None
2279 -- ======================================================================
2280 PROCEDURE Print_Package_Variables
2281 IS
2282 cr CONSTANT VARCHAR2(1) := '
2283 ';
2284 BEGIN
2285 FEM_ENGINES_PKG.Tech_Message(
2286 p_severity => pc_log_level_procedure,
2287 p_module => pc_module_name || 'print_pkg_variable_values',
2288 p_msg_text => cr ||
2289 'pv_folder_id=' || pv_folder_id || cr ||
2290 'pv_cal_rule_obj_id=' || pv_cal_rule_obj_id || cr ||
2291 'pv_cal_rule_obj_def_id=' || pv_cal_rule_obj_def_id || cr ||
2292 'pv_req_id=' || pv_req_id || cr ||
2293 'pv_user_id=' || pv_user_id || cr ||
2294 'pv_resp_id=' || pv_resp_id || cr ||
2295 'pv_login_id=' || pv_login_id || cr ||
2296 'pv_pgm_id=' || pv_pgm_id || cr ||
2297 'pv_pgm_app_id=' || pv_pgm_app_id || cr ||
2298 'pv_effective_start_date=' || pv_effective_start_date || cr ||
2299 'pv_effective_end_date=' || pv_effective_end_date || cr ||
2300 'pv_period_set_name=' || pv_period_set_name || cr ||
2301 'pv_period_type=' || pv_period_type || cr ||
2302 'pv_period_type_name=' || pv_period_type_name || cr ||
2303 'pv_period_year=' || pv_period_year || cr ||
2304 'pv_effective_period_num_low=' || pv_effective_period_num_low || cr ||
2305 'pv_effective_period_num_high=' || pv_effective_period_num_high || cr||
2306 'pv_ogl_period_type_code=' || pv_ogl_period_type_code || cr ||
2307 'pv_ogl_period_type_name=' || pv_ogl_period_type_name || cr ||
2308 'pv_ogl_period_type_desc=' || pv_ogl_period_type_desc || cr ||
2309 'pv_ogl_number_per_fiscal_year=' || pv_ogl_number_per_fiscal_year||cr||
2310 'pv_cal_per_hier_obj_id=' || pv_cal_per_hier_obj_id || cr ||
2311 'pv_cal_per_hier_obj_def_id=' || pv_cal_per_hier_obj_def_id || cr ||
2312 'pv_dimension_grp_id_period=' || pv_dimension_grp_id_period || cr ||
2313 'pv_calendar_id=' || pv_calendar_id || cr ||
2314 'pv_effective_period_num_min=' || pv_effective_period_num_min || cr ||
2315 'pv_effective_period_num_max=' || pv_effective_period_num_max || cr ||
2316 'pv_new_periods_to_process=' || pv_new_periods_to_process || cr ||
2317 'pv_ledger_dim_id=' || pv_ledger_dim_id || cr ||
2318 'pv_calendar_dim_id=' || pv_calendar_dim_id || cr ||
2319 'pv_cal_period_dim_id=' || pv_cal_period_dim_id || cr ||
2320 'pv_time_group_type_dim_id=' || pv_time_group_type_dim_id || cr ||
2321 'pv_dimension_grp_id_quarter=' || pv_dimension_grp_id_quarter || cr ||
2322 'pv_dimension_grp_id_year=' || pv_dimension_grp_id_year || cr ||
2323 'pv_source_system_code=' || pv_source_system_code || cr ||
2324 'pv_row_count_tot=' || pv_row_count_tot || cr ||
2325 'pv_completion_status=' || pv_completion_status
2326 );
2327
2328 END Print_Package_Variables;
2329
2330
2331 -- ======================================================================
2332 -- Procedure
2333 -- Main
2334 -- Purpose
2335 -- Executes the Calendar Engine
2336 -- History
2337 -- 02-02-05 Shintaro Okuda Created
2338 -- 08-29-05 Harikiran Bug 4350620
2339 -- Get the calendar period hierarchy name
2340 -- Arguments
2341 -- x_errbuf Standard Concurrent Program parameter
2342 -- x_retcode Standard Concurrent Program parameter
2343 -- p_cal_rule_obj_def_id Calendar Rule Object Definition ID
2344 -- p_period_set_name Period Set Name
2345 -- p_period_type Period Type
2346 -- p_period_year Period Year
2347 -- ======================================================================
2348 PROCEDURE Main(
2349 x_errbuf OUT NOCOPY VARCHAR2,
2350 x_retcode OUT NOCOPY VARCHAR2,
2351 p_cal_rule_obj_def_id IN NUMBER,
2352 p_period_set_name IN VARCHAR2,
2353 p_period_type IN VARCHAR2,
2354 p_period_year IN NUMBER
2355 ) IS
2356 FEM_INTG_fatal_err EXCEPTION;
2357
2358 v_completion_code NUMBER;
2359 v_ret_status BOOLEAN;
2360
2361 BEGIN
2362
2363 FEM_ENGINES_PKG.Tech_Message(
2364 p_severity => pc_log_level_procedure,
2365 p_module => pc_module_name || 'main.begin',
2366 p_app_name => 'FEM',
2367 p_msg_name => 'FEM_GL_POST_201',
2368 p_token1 => 'FUNC_NAME',
2369 p_value1 => 'FEM_INTG_CAL_RULE_ENG_PKG.Main',
2370 p_token2 => 'TIME',
2371 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
2372 );
2373
2374 --
2375 -- Initialize Variables
2376 --
2377 Init(
2378 p_cal_rule_obj_def_id => p_cal_rule_obj_def_id,
2379 p_period_set_name => p_period_set_name,
2380 p_period_type => p_period_type,
2381 p_period_year => p_period_year
2382 );
2383
2384 FEM_INTG_PL_PKG.Register_Process_Execution(
2385 p_obj_id => pv_cal_rule_obj_id,
2386 p_obj_def_id => pv_cal_rule_obj_def_id,
2387 p_req_id => pv_req_id,
2388 p_user_id => pv_user_id,
2389 p_login_id => pv_login_id,
2390 p_pgm_id => pv_pgm_id,
2391 p_pgm_app_id => pv_pgm_app_id,
2392 p_module_name => pc_module_name || 'register_process_execution',
2393 x_completion_code => v_completion_code
2394 );
2395
2396 IF v_completion_code = 2 THEN
2397 RAISE FEM_INTG_fatal_err;
2398 END IF;
2399
2400 IF pv_new_periods_to_process = 'Y' THEN
2401 --
2402 -- If hierarchy object definition ID is not defined yet,
2403 -- create a new Calendar and a new Time Dimension Group if not
2404 -- created yet before creating a new hierarchy.
2405 --
2406 IF pv_cal_per_hier_obj_def_id IS NULL THEN
2407 --
2408 -- Create Calendar in FEM if not exist yet
2409 --
2410 IF pv_calendar_id IS NULL THEN
2411 New_Calendar();
2412 END IF;
2413
2414 --
2415 -- Create Time Group Type and Time Dimension Group if not exists yet
2416 --
2417 IF pv_dimension_grp_id_period IS NULL THEN
2418 New_Time_Dimension_Group();
2419 END IF;
2420
2421 --
2422 -- Create a new Calendar Period hierarchy
2423 --
2424 New_GL_Cal_Period_Hier();
2425
2426 END IF;
2427
2428 --
2429 -- Bug 4350620 hkaniven --
2430 -- Get the calendar period hierarchy name which will then be added as a
2431 -- prefix to the period_name while inserting into the table
2432 -- fem_cal_periods_tl
2433 --
2434
2435 SELECT TRIM(display_name)
2436 INTO pv_cal_per_hier_name
2437 FROM fem_object_definition_vl
2438 WHERE object_id = pv_cal_per_hier_obj_id
2439 AND object_definition_id = pv_cal_per_hier_obj_def_id;
2440
2441 --
2442 -- Generate cal_period_id in GT table
2443 --
2444 Generate_Member_IDs();
2445
2446 --
2447 -- Populate Time Dimension Members and attributes
2448 --
2449 Populate_Time_Dimension();
2450
2451 --
2452 -- Populate Time Dimension Hierarchy
2453 --
2454 Populate_Time_Hierarchy();
2455
2456 --
2457 -- Update the mapping table for additionally processed periods by this
2458 -- run
2459 --
2460 Update_Calendar_Map();
2461
2462 COMMIT;
2463
2464 END IF;
2465
2466 v_completion_code := 0;
2467
2468 FEM_INTG_PL_PKG.Final_Process_Logging(
2469 p_obj_id => pv_cal_rule_obj_id,
2470 p_obj_def_id => pv_cal_rule_obj_def_id,
2471 p_req_id => pv_req_id,
2472 p_user_id => pv_user_id,
2473 p_login_id => pv_login_id,
2474 p_exec_status => 'SUCCESS',
2475 p_row_num_loaded => pv_row_count_tot,
2476 p_err_num_count => 0,
2477 p_final_msg_name => 'FEM_INTG_PROC_SUCCESS',
2478 p_module_name => pc_module_name || 'final_process_logging',
2479 x_completion_code => v_completion_code
2480 );
2481
2482 IF v_completion_code = 2 THEN
2483 RAISE FEM_INTG_fatal_err;
2484 END IF;
2485
2486 FEM_ENGINES_PKG.Tech_Message(
2487 p_severity => pc_log_level_procedure,
2488 p_module => pc_module_name || 'main.end' ,
2489 p_app_name => 'FEM',
2490 p_msg_name => 'FEM_GL_POST_202',
2491 p_token1 => 'FUNC_NAME',
2492 p_value1 => 'FEM_INTG_CAL_RULE_ENG_PKG.Main',
2493 p_token2 => 'TIME',
2494 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
2495 );
2496
2497 FEM_ENGINES_PKG.User_Message(
2498 p_app_name => 'FEM',
2499 p_msg_name => 'FEM_INTG_PROC_SUCCESS'
2500 );
2501
2502 Print_Package_Variables();
2503
2504 v_ret_status := FND_CONCURRENT.Set_Completion_Status(
2505 status => pv_completion_status,
2506 message => NULL
2507 );
2508
2509 EXCEPTION
2510 WHEN OTHERS THEN
2511 ROLLBACK;
2512
2513 FEM_INTG_PL_PKG.Final_Process_Logging(
2514 p_obj_id => pv_cal_rule_obj_id,
2515 p_obj_def_id => pv_cal_rule_obj_def_id,
2516 p_req_id => pv_req_id,
2517 p_user_id => pv_user_id,
2518 p_login_id => pv_login_id,
2519 p_exec_status => 'ERROR_RERUN',
2520 p_row_num_loaded => 0,
2521 p_err_num_count => pv_row_count_tot,
2522 p_final_msg_name => 'FEM_INTG_PROC_FAILURE',
2523 p_module_name => pc_module_name || 'final_process_logging',
2524 x_completion_code => v_completion_code
2525 );
2526
2527 FEM_ENGINES_PKG.Tech_Message(
2528 p_severity => pc_log_level_exception,
2529 p_module => pc_module_name || 'main.exception_others',
2530 p_app_name => 'FEM',
2531 p_msg_name => 'FEM_GL_POST_203',
2532 p_token1 => 'FUNC_NAME',
2533 p_value1 => 'FEM_INTG_CAL_RULE_ENG_PKG.Main',
2534 p_token2 => 'TIME',
2535 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
2536 );
2537
2538 FEM_ENGINES_PKG.User_Message(
2539 p_app_name => 'FEM',
2540 p_msg_name => 'FEM_INTG_PROC_FAILURE'
2541 );
2542
2543 Print_Package_Variables();
2544
2545 v_ret_status := FND_CONCURRENT.Set_Completion_Status(
2546 status => 'ERROR',
2547 message => NULL
2548 );
2549
2550 END Main;
2551
2552 END FEM_INTG_CAL_RULE_ENG_PKG;