[Home] [Help]
PACKAGE BODY: APPS.GCS_UTILITY_PKG
Source
1 PACKAGE BODY GCS_UTILITY_PKG as
2 /* $Header: gcsutilb.pls 120.5 2007/06/28 12:23:18 vkosuri noship $ */
3
4 --
5 -- Procedure
6 -- init_dimension_attr_info
7 -- Purpose
8 -- Caches all critical info stored in dim_attributes_b within g_dimension_attr_info
9 --
10 -- Arguments
11 --
12 -- Example
13 --
14 -- Notes
15 --
16
17 PROCEDURE init_dimension_attr_info IS
18
19 TYPE t_index_dimension_attr_info IS TABLE OF r_dimension_attr_info;
20
21 l_index_dimension_attr_info t_index_dimension_attr_info;
22 l_hashkey VARCHAR2(200);
23
24 BEGIN
25
26 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
27 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'gcs.plsql.gcs_utility_pkg.init_dimension_attr_info.begin', null);
28 END IF;
29
30 SELECT ftcb.dimension_id,
31 fda.attribute_id,
32 ftcb.column_name,
33 fda.attribute_varchar_label,
34 fda.attribute_value_column_name,
35 -- STK 3/29/04 Added Default Version ID for performance purposes
36 fdavb.version_id
37 BULK COLLECT INTO l_index_dimension_attr_info
38 FROM fem_tab_columns_b ftcb,
39 fem_dim_attributes_b fda,
40 fem_tab_column_prop ftcp,
41 fem_dim_attr_versions_b fdavb
42 WHERE ftcb.table_name = 'FEM_BALANCES'
43 AND ftcb.dimension_id = fda.dimension_id
44 AND ftcp.table_name = 'FEM_BALANCES'
45 AND ftcp.column_property_code = 'PROCESSING_KEY'
46 AND fdavb.attribute_id = fda.attribute_id
47 AND fdavb.default_version_flag = 'Y'
48 AND ftcp.column_name = ftcb.column_name;
49
50 FOR l_counter IN
51 l_index_dimension_attr_info.FIRST..l_index_dimension_attr_info.LAST
52 LOOP
53
54 -- The hashkey will be COLUMN NAME - DIMENSION ATTRIBUTE LABEL in order to resolve issue with attributes sharing names between
55 -- dimensions and/or columns (Issue Found by Mike Ward)
56
57 l_hashkey := l_index_dimension_attr_info(l_counter).column_name || '-' || l_index_dimension_attr_info(l_counter).attribute_varchar_label;
58
59 g_dimension_attr_info(l_hashkey) :=
60 l_index_dimension_attr_info(l_counter);
61 END LOOP;
62
63 SELECT fdb.dimension_id,
64 fda.attribute_id,
65 DECODE(fdb.dimension_varchar_label,
66 'COST_CENTER', 'COST_CENTER','EXT_ACCOUNT_TYPE_CODE'),
67 fda.attribute_varchar_label,
68 fda.attribute_value_column_name,
69 -- STK 3/29/04 Added Default Version ID for performance purposes
70 fdavb.version_id
71 BULK COLLECT INTO l_index_dimension_attr_info
72 FROM fem_dim_attributes_b fda,
73 fem_dimensions_b fdb,
74 fem_dim_attr_versions_b fdavb
75 WHERE fdb.dimension_id = fda.dimension_id
76 AND fdavb.attribute_id = fda.attribute_id
77 AND fdavb.default_version_flag = 'Y'
78 AND fdb.dimension_varchar_label IN ('EXTENDED_ACCOUNT_TYPE','COST_CENTER');
79
80 FOR l_counter IN
81 l_index_dimension_attr_info.FIRST..l_index_dimension_attr_info.LAST
82 LOOP
83
84 -- The hashkey will be COLUMN NAME - DIMENSION ATTRIBUTE LABEL in order to resolve issue with attributes sharing names between
85 -- dimensions and/or columns (Issue Found by Mike Ward)
86
87 l_hashkey := l_index_dimension_attr_info(l_counter).column_name || '-' || l_index_dimension_attr_info(l_counter).attribute_varchar_label;
88
89 g_dimension_attr_info(l_hashkey) :=
90 l_index_dimension_attr_info(l_counter);
91 END LOOP;
92
93
94 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
95 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'gcs.plsql.gcs_utility_pkg.init_dimension_attr_info.end', null);
96 END IF;
97
98 END init_dimension_attr_info;
99
100 -- END init_dimension_attr_info
101
102
103 --
104 -- Procedure
105 -- init_dimension_info
106 -- Purpose
107 -- Caches the active dimension for GCS II, in addition to the value sets associated with each dimension.
108 --
109 -- Arguments
110 --
111 -- Example
112 --
113 -- Notes
114 --
115
116 PROCEDURE init_dimension_info IS
117
118 TYPE t_index_gcs_dimension_info IS TABLE OF r_gcs_dimension_info;
119
120 l_index_gcs_dimension_info t_index_gcs_dimension_info;
121 l_application_group_id NUMBER(15);
122 l_err_code NUMBER;
123 l_err_msg VARCHAR2(2000);
124 l_required_for_gcs VARCHAR2(1) := 'N';
125
126 -- Bugfix 5707630: Type added to hold historical rates dimension.
127 TYPE t_index_hrate_dim_info IS TABLE OF r_hrate_dim_info;
128 l_index_hrate_dim_info t_index_hrate_dim_info;
129
130 inv_application_exception EXCEPTION;
131 inv_global_combo_exception EXCEPTION;
132
133 BEGIN
134
135 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
136 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'gcs.plsql.gcs_utility_pkg.init_dimension_info.begin', null);
137 END IF;
138
139 -- Start bugfix 5707630: Select the dimensions enabled for historical rates on the
140 -- processing keys page and store it into the record for use during translation
141 SELECT gedm.gcs_column,
142 nvl(gedm.hrate_enabled_flag, 'Y')
143 BULK COLLECT INTO l_index_hrate_dim_info
144 FROM gcs_epb_dim_maps gedm;
145
146
147 IF l_index_hrate_dim_info.COUNT > 0 THEN
148 FOR l_counter IN l_index_hrate_dim_info.FIRST..l_index_hrate_dim_info.LAST
149 LOOP
150 g_hrate_dim_info(l_index_hrate_dim_info(l_counter).column_name) :=
151 l_index_hrate_dim_info(l_counter);
152 END LOOP;
153 END IF;
154 -- End bugfix 5707630
155
156 /* Code for resolving application group id, and global value set combo will remain commented until reconvening in early Jan 2004 */
157 -- Bugfix 4267992 : Storing Global Value Set Combo on gcs_system_options
158 l_application_group_id := 266;
159
160 SELECT ftcb.column_name,
161 fvsb.dimension_id,
162 fvsb.value_set_id,
163 'N' gcs_required,
164 'Y' fem_required,
165 fvsb.default_member_id,
166 -1,
167 fxd.member_b_table_name,
168 fxd.member_vl_object_name,
169 fxd.member_col,
170 fxd.member_display_code_col
171 BULK COLLECT INTO l_index_gcs_dimension_info
172 FROM fem_value_sets_b fvsb,
173 fem_global_vs_combo_defs fgvcd,
174 fem_tab_columns_b ftcb,
175 fem_tab_column_prop ftcp,
176 fem_xdim_dimensions fxd,
177 gcs_system_options gso
178 WHERE fvsb.dimension_id = DECODE(fgvcd.dimension_id, 17, 8, fgvcd.dimension_id)
179 AND fvsb.value_set_id = fgvcd.value_set_id
180 AND fgvcd.global_vs_combo_id = gso.fch_global_vs_combo_id
181 AND ftcb.dimension_id = fgvcd.dimension_id
182 AND ftcb.table_name = 'FEM_BALANCES'
183 AND ftcp.table_name = 'FEM_BALANCES'
184 AND ftcp.column_property_code = 'PROCESSING_KEY'
185 AND ftcb.dimension_id = fxd.dimension_id
186 AND ftcp.column_name = ftcb.column_name;
187
188
189 IF l_index_gcs_dimension_info.FIRST IS NOT NULL AND
190 l_index_gcs_dimension_info.LAST IS NOT NULL THEN
191 FOR l_counter IN
192 l_index_gcs_dimension_info.FIRST..l_index_gcs_dimension_info.LAST
193 LOOP
194
195 BEGIN
196 SELECT 'N'
197 INTO l_required_for_gcs
198 FROM DUAL
199 WHERE EXISTS
200 (SELECT 'X'
201 FROM fem_app_grp_dim_exclsns fagde
202 WHERE fagde.application_group_id = l_application_group_id
203 AND fagde.dimension_id = l_index_gcs_dimension_info(l_counter).dimension_id)
204 OR EXISTS
205 (SELECT 'X'
206 FROM fem_app_grp_col_exclsns fagce
207 WHERE fagce.application_group_id = l_application_group_id
208 AND fagce.column_name = l_index_gcs_dimension_info(l_counter).column_name);
209 EXCEPTION
210 WHEN OTHERS THEN
211 l_index_gcs_dimension_info(l_counter).required_for_gcs := 'Y';
212 END;
213
214 g_gcs_dimension_info(l_index_gcs_dimension_info(l_counter).column_name) :=
215 l_index_gcs_dimension_info(l_counter);
216 END LOOP;
217 END IF;
218
219 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
220 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'gcs.plsql.gcs_utility_pkg.init_dimension_info.end', null);
221 END IF;
222
223 EXCEPTION
224 WHEN inv_application_exception THEN
225 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
226 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, 'gcs.plsql.gcs_utility_pkg.init_dimension_info', 'Invalid Applications Context');
227 END IF;
228 FND_MESSAGE.SET_NAME('GCS','Invalid_App_Group');
229 app_exception.raise_exception;
230 WHEN inv_global_combo_exception THEN
231 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
232 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, 'gcs.plsql.gcs_utility_pkg.init_dimension_info', 'Invalid Global VS Combo');
233 END IF;
234 FND_MESSAGE.SET_NAME('GCS','Invalid_Global_Como');
235 app_exception.raise_exception;
236 WHEN NO_DATA_FOUND THEN
237 NULL;
238 END init_dimension_info;
239
240
241 --
242 -- Procedure
243 -- get_cal_period_details
244 -- Purpose
245 -- Returns the attribute information for the current period, and the prior period.
246 -- Needs to be leveraged by Dataprep and Translation
247 --
248 -- Arguments
249 --
250 -- p_cal_period_id Calendar Period Identifier
251 -- p_cal_period_record Records containing attribute values for prior and specified period.
252 -- Example
253 --
254 -- Notes
255 --
256
257 PROCEDURE get_cal_period_details(p_cal_period_id NUMBER,
258 p_cal_period_record IN OUT NOCOPY r_cal_period_info) IS
259
260 l_curr_cal_period_number NUMBER(15);
261 l_curr_cal_period_year NUMBER(15);
262 l_periods_per_year NUMBER(15);
263 l_calendar_id NUMBER(15);
264 l_calendar_group_id NUMBER(15);
265 l_prev_cal_period_id NUMBER;
266 l_prev_cal_period_number NUMBER(15);
267 l_prev_cal_period_year NUMBER(15);
268 l_next_cal_period_id NUMBER;
269 l_next_cal_period_number NUMBER(15);
270 l_next_cal_period_year NUMBER(15);
271
272 BEGIN
273
274 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
275 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'gcs.plsql.gcs_utility_pkg.get_cal_period_details.begin', null);
276 END IF;
277
278 SELECT fcpb.calendar_id, fcpb.dimension_group_id,
279 fcpa_number.number_assign_value, fcpa_year.number_assign_value,
280 ftgta.number_assign_value
281 INTO l_calendar_id, l_calendar_group_id,
282 l_curr_cal_period_number, l_curr_cal_period_year,
283 l_periods_per_year
284 FROM fem_cal_periods_b fcpb,
285 fem_cal_periods_attr fcpa_number,
286 fem_cal_periods_attr fcpa_year,
287 fem_dimension_grps_b fdgb,
288 fem_time_grp_types_attr ftgta,
289 fem_dim_attributes_b feab,
290 fem_dim_attr_versions_b fdavb
291 WHERE fcpb.cal_period_id = p_cal_period_id
292 AND fcpb.dimension_group_id = fdgb.dimension_group_id
293 AND fdgb.time_group_type_code = ftgta.time_group_type_code
294 AND ftgta.attribute_id = feab.attribute_id
295 AND ftgta.version_id = fdavb.version_id
296 AND feab.attribute_varchar_label = 'PERIODS_IN_YEAR'
297 AND feab.attribute_id = fdavb.attribute_id
298 AND fdavb.default_version_flag = 'Y'
299 AND fcpa_number.cal_period_id = fcpb.cal_period_id
300 AND fcpa_number.attribute_id =
301 g_dimension_attr_info ('CAL_PERIOD_ID-GL_PERIOD_NUM').attribute_id
302 AND fcpa_number.version_id =
303 g_dimension_attr_info ('CAL_PERIOD_ID-GL_PERIOD_NUM').version_id
304 AND fcpa_year.attribute_id =
305 g_dimension_attr_info ('CAL_PERIOD_ID-ACCOUNTING_YEAR').attribute_id
306 AND fcpa_year.version_id =
307 g_dimension_attr_info ('CAL_PERIOD_ID-ACCOUNTING_YEAR').version_id
308 AND fcpa_year.cal_period_id = fcpb.cal_period_id;
309
310 IF (l_curr_cal_period_number = 1) THEN
311 l_prev_cal_period_number := l_periods_per_year;
312 l_prev_cal_period_year := l_curr_cal_period_year - 1;
313 ELSE
314 l_prev_cal_period_number := l_curr_cal_period_number - 1;
315 l_prev_cal_period_year := l_curr_cal_period_year;
316 END IF;
317
318 BEGIN
319
320 SELECT fcpb.cal_period_id
321 INTO l_prev_cal_period_id
322 FROM fem_cal_periods_b fcpb,
323 fem_cal_periods_attr fcpa_number,
324 fem_cal_periods_attr fcpa_year
325 WHERE fcpb.calendar_id = l_calendar_id
326 AND fcpb.dimension_group_id = l_calendar_group_id
327 AND fcpb.cal_period_id = fcpa_number.cal_period_id
328 AND fcpb.cal_period_id = fcpa_year.cal_period_id
329 AND fcpa_number.attribute_id = g_dimension_attr_info('CAL_PERIOD_ID-GL_PERIOD_NUM').attribute_id
330 AND fcpa_number.version_id = g_dimension_attr_info('CAL_PERIOD_ID-GL_PERIOD_NUM').version_id
331 AND fcpa_number.number_assign_value = l_prev_cal_period_number
332 AND fcpa_year.attribute_id = g_dimension_attr_info('CAL_PERIOD_ID-ACCOUNTING_YEAR').attribute_id
333 AND fcpa_year.version_id = g_dimension_attr_info('CAL_PERIOD_ID-ACCOUNTING_YEAR').version_id
334 AND fcpa_year.number_assign_value = l_prev_cal_period_year;
335
336 EXCEPTION
337 WHEN NO_DATA_FOUND THEN
338 l_prev_cal_period_id := -1;
339 l_prev_cal_period_year := -1;
340 l_prev_cal_period_number := -1;
341 END;
342
343 -- next get the information for the subsequent period
344 IF (l_curr_cal_period_number = l_periods_per_year) THEN
345 l_next_cal_period_number := 1;
346 l_next_cal_period_year := l_curr_cal_period_year + 1;
347 ELSE
348 l_next_cal_period_number := l_curr_cal_period_number + 1;
349 l_next_cal_period_year := l_curr_cal_period_year;
350 END IF;
351
352 BEGIN
353
354 SELECT fcpb.cal_period_id
355 INTO l_next_cal_period_id
356 FROM fem_cal_periods_b fcpb,
357 fem_cal_periods_attr fcpa_number,
358 fem_cal_periods_attr fcpa_year
359 WHERE fcpb.calendar_id = l_calendar_id
360 AND fcpb.dimension_group_id = l_calendar_group_id
361 AND fcpb.cal_period_id = fcpa_number.cal_period_id
362 AND fcpb.cal_period_id = fcpa_year.cal_period_id
363 AND fcpa_number.attribute_id = g_dimension_attr_info('CAL_PERIOD_ID-GL_PERIOD_NUM').attribute_id
364 AND fcpa_number.version_id = g_dimension_attr_info('CAL_PERIOD_ID-GL_PERIOD_NUM').version_id
365 AND fcpa_number.number_assign_value = l_next_cal_period_number
366 AND fcpa_year.attribute_id = g_dimension_attr_info('CAL_PERIOD_ID-ACCOUNTING_YEAR').attribute_id
367 AND fcpa_year.version_id = g_dimension_attr_info('CAL_PERIOD_ID-ACCOUNTING_YEAR').version_id
368 AND fcpa_year.number_assign_value = l_next_cal_period_year;
369
370 EXCEPTION
371 WHEN NO_DATA_FOUND THEN
372 l_next_cal_period_id := -1;
373 l_next_cal_period_year := -1;
374 l_next_cal_period_number := -1;
375 END;
376
377
378 p_cal_period_record.cal_period_id := p_cal_period_id;
379 p_cal_period_record.cal_period_number := l_curr_cal_period_number;
380 p_cal_period_record.cal_period_year := l_curr_cal_period_year;
381 p_cal_period_record.prev_cal_period_id := l_prev_cal_period_id;
382 p_cal_period_record.prev_cal_period_number := l_prev_cal_period_number;
383 p_cal_period_record.prev_cal_period_year := l_prev_cal_period_year;
384 p_cal_period_record.next_cal_period_id := l_next_cal_period_id;
385 p_cal_period_record.next_cal_period_number := l_next_cal_period_number;
386 p_cal_period_record.next_cal_period_year := l_next_cal_period_year;
387 p_cal_period_record.cal_periods_per_year := l_periods_per_year;
388
389
390 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
391 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'gcs.plsql.gcs_utility_pkg.get_cal_period_details.end', null);
392 END IF;
393
394 END get_cal_period_details;
395
396 -- STK 1/12/04
397 -- Procedure
398 -- get_entry_header()
399 -- Purpose
400 -- generates a unique name, and appropriate description for all automated GCS II processes
401 -- Arguments
402 -- p_process_type_code Automated Process Values: Data Prep, Translation, Aggregation,Acquisitions and Disposals,
403 -- Pre-Intercompany, Intercompany, Post-Intercompany,
404 -- Minority Interest, Post-Minority Interest
405 -- p_entry_id Entry ID
406 -- p_entity_id Entity ID associated with process (parent entity in case of rules)
407 -- p_currency_code Currency Code of Entry
408 -- p_rule_id Required Only for Automated Rules
409 -- Notes
410 --
411 PROCEDURE get_entry_header( p_process_type_code VARCHAR2,
412 p_entry_id NUMBER,
413 p_entity_id NUMBER,
414 p_currency_code VARCHAR2,
415 p_rule_id NUMBER DEFAULT NULL,
416 p_entry_header IN OUT NOCOPY r_entry_header) IS
417
418 l_entity_name VARCHAR2(80);
419 l_rule_name VARCHAR2(80);
420 l_entry_header VARCHAR2(240);
421 l_entry_description VARCHAR2(240);
422 l_temp VARCHAR2(1);
423
424 BEGIN
425
426 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
427 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'gcs.plsql.gcs_utility_pkg.get_entry_header.begin', null);
428 END IF;
429
430 SELECT entity_name
431 INTO l_entity_name
432 FROM fem_entities_tl
433 WHERE entity_id = p_entity_id
434 AND language = USERENV('LANG');
435
436 IF (p_rule_id IS NOT NULL) THEN
437 SELECT rule_name
438 INTO l_rule_name
439 FROM gcs_elim_rules_tl
440 WHERE rule_id = p_rule_id
441 AND language = USERENV('LANG');
442 END IF;
443
444 IF (p_process_type_code = 'Data Prep') THEN
445 l_entry_header := l_entity_name || '(' || p_currency_code || ')';
446 l_entry_description := 'Data Preparation of ' || l_entity_name;
447 ELSIF (p_process_type_code = 'Translation') THEN
448 l_entry_header := l_entity_name || '(' || p_currency_code || ')';
449 l_entry_description := 'Translation of ' || l_entity_name || ' to ' || p_currency_code;
450 ELSE
451 l_entry_header := l_rule_name || ' for ' || l_entity_name;
452 l_entry_description := l_rule_name || ' Executed For ' || l_entity_name;
453 END IF;
454
455 BEGIN
456
457 SELECT 'X'
458 INTO l_temp
459 FROM gcs_entry_headers
460 WHERE entry_name = l_entry_header;
461 EXCEPTION
462 WHEN NO_DATA_FOUND THEN
463 null;
464 END;
465
466 IF (l_temp = 'X') THEN
467 l_entry_header := l_entry_header || ' ID #' || p_entry_id;
468 END IF;
469
470 p_entry_header.name := l_entry_header;
471 p_entry_header.description := l_entry_description;
472
473 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
474 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'gcs.plsql.gcs_utility_pkg.get_entry_header.end', null);
475 END IF;
476
477 END get_entry_header;
478
479
480 --
481 -- Function
482 -- Get_Dimension_Required
483 -- Purpose
484 -- Get whether or not this dimension is required. Return 'Y' or 'N'.
485 -- Arguments
486 -- p_dim_col Dimension column name
487 -- Example
488 -- GCS_UTILITY_PKG.Get_Dimension_Required
489 -- Notes
490 --
491 FUNCTION Get_Dimension_Required(p_dim_col VARCHAR2) RETURN VARCHAR2 IS
492 BEGIN
493 return g_gcs_dimension_info(p_dim_col).required_for_gcs;
494 EXCEPTION
495 WHEN NO_DATA_FOUND THEN
496 return 'N';
497 END Get_Dimension_Required;
498
499 --
500 -- Function
501 -- Get_Fem_Dim_Required
502 -- Purpose
503 -- Get whether or not this dimension is required for FEM. Return 'Y' or 'N'.
504 -- Arguments
505 -- p_dim_col Dimension column name
506 -- Example
507 -- GCS_UTILITY_PKG.Get_Fem_Dim_Required
508 -- Notes
509 --
510 FUNCTION Get_Fem_Dim_Required(p_dim_col VARCHAR2) RETURN VARCHAR2 IS
511 BEGIN
512 return g_gcs_dimension_info(p_dim_col).required_for_fem;
513 EXCEPTION
514 WHEN NO_DATA_FOUND THEN
515 return 'N';
516 END Get_Fem_Dim_Required;
517
518 -- Bug fix 5707630
519 -- Function
520 -- Get_Hrate_Dim_Required
521 -- Purpose
522 -- Get whether or not this dimension is required for historical rates
523 -- Arguments
524 -- p_dim_col Dimension column name
525 -- Example
526 -- GCS_UTILITY_PKG.Get_Hrate_Dim_Required
527 -- Notes
528 --
529 FUNCTION Get_Hrate_Dim_Required(p_dim_col VARCHAR2) RETURN VARCHAR2 IS
530 BEGIN
531 return g_hrate_dim_info(p_dim_col).required_for_hrate;
532 EXCEPTION
533 WHEN NO_DATA_FOUND THEN
534 RETURN 'N';
535 END Get_Hrate_Dim_Required;
536
537
538
539 --
540 -- Function
541 -- Get_Default_Value
542 -- Purpose
543 -- Get default value for the dimension
544 -- Arguments
545 -- p_dim_col Dimension column name
546 -- Example
547 -- GCS_UTILITY_PKG.Get_Default_Value
548 -- Notes
549 --
550 FUNCTION Get_Default_Value(p_dim_col VARCHAR2) RETURN NUMBER IS
551 BEGIN
552 return g_gcs_dimension_info(p_dim_col).default_value;
553 EXCEPTION
554 WHEN NO_DATA_FOUND THEN
555 return -1;
556 END Get_Default_Value;
557
558 --
559 -- Function
560 -- Get_Dimension_Attribute
561 -- Purpose
562 -- Get attribute_id for the dimension-attribute
563 -- Arguments
564 -- p_dim_attr Dimension attribute
565 -- Example
566 -- GCS_UTILITY_PKG.Get_Dimension_Attribute('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
567 -- Notes
568 --
569 FUNCTION Get_Dimension_Attribute(p_dim_attr VARCHAR2) RETURN NUMBER IS
570 BEGIN
571 return g_dimension_attr_info(p_dim_attr).attribute_id;
572 EXCEPTION
573 WHEN NO_DATA_FOUND THEN
574 return -1;
575 END Get_Dimension_Attribute;
576
577
578 -- *********************************************************************
579 -- Function (PRIVATE)
580 -- Get_Base_Org_Id(p_entity_id NUMBER) RETURN NUMBER
581 -- Purpose
582 -- Get org_id for the given entity.
583 -- Arguments
584 -- p_entity_id Entity Id
585 -- Example
586 -- GCS_UTILITY_PKG.Get_Org_Id
587 -- Notes
588 -- This is a private function, only called by the public Get_Org_Id().
589 -- History
590 -- 23-Jun-2004 J Huang BaseOrg enhancement. (Bug 3711204)
591 --
592
593 FUNCTION Get_Base_Org_Id(p_entity_id NUMBER) RETURN NUMBER IS
594 cursor getOrgId (cEntityId NUMBER) is
595 SELECT DIM_ATTRIBUTE_NUMERIC_MEMBER
596 FROM FEM_ENTITIES_ATTR
597 WHERE ENTITY_ID = cEntityId
598 AND VERSION_ID = g_dimension_attr_info('ENTITY_ID-BASE_ORGANIZATION').version_id
599 AND ATTRIBUTE_ID = g_dimension_attr_info('ENTITY_ID-BASE_ORGANIZATION').attribute_id;
600
601 org_id NUMBER;
602 BEGIN
603
604 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
605 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
606 'gcs.plsql.GCS_UTILITY_PKG.GET_BASE_ORG_ID.begin',
607 TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
608 END IF;
609
610 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
611 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
612 'gcs.plsql.GCS_UTILITY_PKG.GET_BASE_ORG_ID.bind',
613 'ENTITY_ID: ' || p_entity_id);
614 END IF;
615
616 OPEN getOrgId( p_entity_id);
617 FETCH getOrgId INTO org_id;
618 IF getOrgId%NOTFOUND THEN
619 --Bugfix 4302199: Remove closing of this cursor
620 org_id :=-1;
621 END IF;
622 CLOSE getOrgId;
623
624 IF (org_id IS NULL) THEN
625 org_id :=-1;
626 END IF;
627
628 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
629 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
630 'gcs.plsql.GCS_UTILITY_PKG.GET_BASE_ORG_ID',
631 'BASE_ORG_ID: ' || org_id);
632 END IF;
633
634 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
635 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
636 'gcs.plsql.GCS_UTILITY_PKG.GET_BASE_ORG_ID.end',
637 TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
638 END IF;
639
640 RETURN org_id;
641 END Get_Base_Org_Id;
642
643 --
644 -- Function
645 -- Get_Org_Id(p_entity_id NUMBER, p_hierarchy_id NUMBER, p_relationship_id NUMBER) RETURN NUMBER
646 -- Purpose
647 -- Get org_id for the given entity. If no org_id is found, then look
648 -- for org_id for the associated operating entity for this consolidation
649 -- entity. If no org_id is found, then look for org_id for any one of the
650 -- child entities
651 -- Get org_id for the given entity. If the given entity is an operating
652 -- entity, then org_id is just base_org_id assigned to the entity. If
653 -- the entity is a consolidation entity, then get the base_org_id of the
654 -- asso
655 -- Arguments
656 -- p_entity_id Entity Id
657 -- p_hierarchy_id Hierarchy Id
658 -- p_relationship_id Relationship Id
659 -- Example
660 -- GCS_UTILITY_PKG.Get_Org_Id
661 -- Notes
662 --
663 -- History
664 -- 23-Jun-2004 J Huang BaseOrg enhancement. (Bug 3711204)
665 -- 15-Jul-2004 J Huang Bug 3761865
666 --
667
668 FUNCTION Get_Org_Id(p_entity_id NUMBER, p_hierarchy_id NUMBER)RETURN NUMBER IS
669 l_org_id NUMBER;
670 l_entity_id NUMBER; --entity_id;
671 c_entity_id NUMBER; --consolidation entity_id, when applicable;
672 l_hierarchy_id NUMBER;
673 l_entity_type VARCHAR2(2);
674
675 --Gets the ENTITY_ID for the associated operating entity for this consolidation entity.
676 cursor getOpEntityId (cEntityId NUMBER) IS
677 SELECT DIM_ATTRIBUTE_NUMERIC_MEMBER
678 FROM FEM_ENTITIES_ATTR
679 WHERE ENTITY_ID = cEntityId
680 AND VERSION_ID = g_dimension_attr_info('ENTITY_ID-OPERATING_ENTITY').version_id
681 AND ATTRIBUTE_ID = g_dimension_attr_info('ENTITY_ID-OPERATING_ENTITY').attribute_id;
682
683
684 --Gets all other operationg entity ENTITY_ID this consolidation entity.
685 cursor getAllChildEntityId(cEntityId NUMBER, cHierarchyId NUMBER)IS
686 SELECT r.child_entity_id
687 FROM GCS_CONS_RELATIONSHIPS r
688 START WITH r.parent_entity_id = cEntityId
689 AND r.hierarchy_id = cHierarchyId
690
691 AND ( sysdate BETWEEN r.start_date
692 AND NVL(r.end_date, sysdate))
693 AND r.actual_ownership_flag='Y'
694 CONNECT BY prior r.child_entity_id = r.parent_entity_id
695 AND r.hierarchy_id = cHierarchyId
696 AND ( sysdate BETWEEN r.start_date
697 AND NVL(r.end_date, sysdate))
698 AND r.actual_ownership_flag='Y';
699
700 BEGIN
701 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
702 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
703 'gcs.plsql.GCS_UTILITY_PKG.GET_ORG_ID. begin',
704 TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
705 END IF;
706
707 l_entity_id:=p_entity_id;
708 c_entity_id:=p_entity_id;
709 l_hierarchy_id := p_hierarchy_id;
710 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
711 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
712 'gcs.plsql.GCS_UTILITY_PKG.GET_ORG_ID.bind',
713 'ENTITY_ID: '||l_entity_id
714 ||'; HIERARCHY_ID: '|| l_hierarchy_id);
715 END IF;
716
717 --Get entity_type
718 SELECT dim_attribute_varchar_member
719 INTO l_entity_type
720 FROM FEM_ENTITIES_ATTR
721 WHERE entity_id = l_entity_id
722 AND attribute_id = g_dimension_attr_info('ENTITY_ID-ENTITY_TYPE_CODE').attribute_id
723 AND version_id = g_dimension_attr_info('ENTITY_ID-ENTITY_TYPE_CODE').version_id
724 AND value_set_id = g_gcs_dimension_info('ENTITY_ID').associated_value_set_id;
725
726 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
727 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
728 'gcs.plsql.GCS_UTILITY_PKG.GET_ORG_ID.get_entity_type',
729 'ATTRIBUTE_ID: '|| g_dimension_attr_info('ENTITY_ID-ENTITY_TYPE_CODE').attribute_id
730 ||'; VERSION_ID: '|| g_dimension_attr_info('ENTITY_ID-ENTITY_TYPE_CODE').version_id
731 ||'; VALUE_SET_ID: '|| g_gcs_dimension_info('ENTITY_ID').associated_value_set_id
732 ||'; ENTITY_TYPE: '|| l_entity_type);
733 END IF;
734
735 IF (l_entity_type= 'O') THEN
736 l_org_id := Get_Base_Org_Id(l_entity_id);
737
738 ELSE
739 --If entity is an elim entity, then get the consolidated entity
740 IF (l_entity_type = 'E') THEN
741 --Bugfix 5256700: Need to modify this code to handle case where child is owned multiple times. Will determine consolidation entity by looking
742 --at fem_entities_attr
743
744 /* Commenting out original query
745 SELECT R.PARENT_ENTITY_ID
746 INTO c_entity_id
747 FROM GCS_CONS_RELATIONSHIPS R
748 WHERE R.CHILD_ENTITY_ID = l_entity_id
749 AND R.actual_ownership_flag='Y'
750 AND R.HIERARCHY_ID = l_hierarchy_id;
751 */
752
753 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
754 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
755 'gcs.plsql.GCS_UTILITY_PKG.GET_ORG_ID.get_consolidation_entity',
756 'ATTRIBUTE_ID: '|| g_dimension_attr_info('ENTITY_ID-ELIMINATION_ENTITY').attribute_id
757 ||'; VERSION_ID: '|| g_dimension_attr_info('ENTITY_ID-ELIMINATION_ENTITY').version_id
758 ||'; ENTITY_ID: '|| l_entity_id);
759 END IF;
760
761 SELECT fea.entity_id
762 INTO c_entity_id
763 FROM fem_entities_attr fea
764 WHERE fea.attribute_id = g_dimension_attr_info('ENTITY_ID-ELIMINATION_ENTITY').attribute_id
765 AND fea.version_id = g_dimension_attr_info('ENTITY_ID-ELIMINATION_ENTITY').version_id
766 AND fea.dim_attribute_numeric_member = l_entity_id;
767
768 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
769 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
770 'gcs.plsql.GCS_UTILITY_PKG.GET_ORG_ID.get_consolidation_entity',
771 'CONSOLIDATION ENTITY: '|| c_entity_id);
772 END IF;
773
774 END IF; --IF entity_type = 'E'
775
776 --Get the assoicated operating entity id for the consolidated entity.
777 OPEN getOpEntityId(c_entity_id);
778 FETCH getOpEntityId INTO l_entity_id;
779
780 --If no associated operating entity is found, get base_org_id of any child operating entity
781 IF getOpEntityId%ROWCOUNT <1 THEN
782 FOR childRec IN getAllChildEntityId(c_entity_id, l_hierarchy_id ) LOOP
783 IF childRec.child_entity_id <> l_entity_id then
784 l_org_id :=Get_Base_Org_Id(childRec.child_entity_id);
785 END IF;
786 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
787 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
788 'gcs.plsql.GCS_UTILITY_PKG.GET_ORG_ID.bind',
789 'Consolidation entity ENTITY_ID: ' || c_entity_id
790 ||'; Operating entity ENTITY_ID: ' || l_entity_id);
791 END IF;
792
793 EXIT WHEN l_org_id > -1;
794 END LOOP;
795 ELSE
796 l_org_id := Get_Base_Org_Id(l_entity_id);
797
798 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
799 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
800 'gcs.plsql.GCS_UTILITY_PKG.GET_ORG_ID.bind',
801 'Consolidation entity ENTITY_ID: ' || c_entity_id
802 ||'; Associated Operating entity ENTITY_ID: ' || l_entity_id);
803 END IF;
804 END IF; --If getOpEntityId%ROWCOUNT<1
805 CLOSE getOpEntityId;
806
807 END IF; --If entity_type ='O'
808
809 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
810 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
811 'gcs.plsql.GCS_UTILITY_PKG.GET_ORG_ID.end',
812 TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
813 END IF;
814
815 RETURN l_org_id;
816
817 END Get_Org_Id;
818
819
820 ---*************************************************************-----
821 -- Procedure
822 -- Get_Conversion_Rate
823 -- Purpose
824 -- Get the conversion rate.
825
826 -- The API do the following:
827 -- 1) Get the last date for the given cal_period_id
828 -- 2) Check for a default currency treatment and if one is found
829 -- do the following else proceed to step (3) :
830 -- a) For the ending rate type see if a rate exists
831 -- for the currency, and date combination.
832 -- Return the rate if found, else return 1 plus a
833 -- 'NO_RATE_ERROR' code.
834 --
835 -- 3) Check if a rate exists for the 'Corporate' rate type,
836 -- currency and date combination. Return the rate if found,
837 -- else return 1 plus a 'NO_RATE_ERROR' code.
838 --
839 --
840 -- Arguments
841 -- p_source_currency Source Currency
842 -- p_target_currency Target Currency
843 -- p_cal_period_id Cal Period Id
844 -- P_errbuf Error Buffer
845 -- P_errcode Error Code
846 -- Example
847 -- GCS_UTILITY_PKG.Get_Conversion_Rate('EUR', 'USD',
848 -- 24528210000000000000061000200140,
849 -- l_errbuf,
850 -- l_errcode);
851 -- Notes
852 --
853
854 PROCEDURE get_conversion_rate (P_Source_Currency IN VARCHAR2,
855 P_Target_Currency IN VARCHAR2,
856 p_cal_period_Id IN NUMBER,
857 p_conversion_rate IN OUT NOCOPY NUMBER,
858 P_errbuf IN OUT NOCOPY VARCHAR2,
859 p_errcode IN OUT NOCOPY NUMBER) IS
860
861 l_period_end_date DATE;
862 l_rate_type VARCHAR2(90);
863 l_conv_rate NUMBER;
864
865 BEGIN
866
867
868 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
869 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
870 'gcs.plsql.gcs_utility_pkg.get_conversion_rate. begin',
871 null);
872 END IF;
873 p_errbuf := 'SUCCESS';
874 -- Get the period end date.
875
876 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
877 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
878 'gcs.plsql.gcs_utility_pkg.get_conversion_rate.Get the period end date.'
879 , null);
880 END IF;
881
882 SELECT DATE_ASSIGN_VALUE
883 INTO l_period_end_date
884 FROM fem_cal_periods_attr fcpa
885 WHERE fcpa.cal_period_id = p_cal_period_id
886 AND fcpa.attribute_id =
887 gcs_utility_pkg.g_dimension_attr_info ('CAL_PERIOD_ID-CAL_PERIOD_END_DATE').attribute_id
888 AND fcpa.version_id =
889 gcs_utility_pkg.g_dimension_attr_info ('CAL_PERIOD_ID-CAL_PERIOD_END_DATE').version_id;
890
891
892 BEGIN
893 -- Get the default currency treatment.
894
895 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
896 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
897 'gcs.plsql.gcs_utility_pkg.'
898 ||'get_conversion_rate'
899 ||'Source Currency: '||P_source_currency
900 ||' Target Currency: '||P_Target_currency
901 ||' End Date: '||l_period_end_date,
902 null);
903 END IF;
904
905 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
906 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
907 'gcs.plsql.gcs_utility_pkg.'
908 ||'get_conversion_rate.Get the default rate type',
909 null);
910 END IF;
911
912
913 SELECT Ending_Rate_Type
914 INTO l_rate_type
915 FROM GCS_CURR_TREATMENTS_B
916 WHERE ENABLED_FLAG = 'Y'
917 AND DEFAULT_FLAG = 'Y';
918
919
920 BEGIN
921 -- Get the conversion rate for the above ending_rate_type and
922 -- period end date combination.
923
924 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
925 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
926 'gcs.plsql.gcs_utility_pkg.get_conversion_rate.'
927 ||'Get the rate for default rate type and period '
928 ||'end date combination', null);
929 END IF;
930
931
932 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
933 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
934 'gcs.plsql.gcs_utility_pkg.'
935 ||'get_conversion_rate'
936 ||' Default Conversion Type: '||l_rate_type,
937 null);
938 END IF;
939
940
941 SELECT conversion_rate
942 INTO l_conv_rate
943 FROM GL_DAILY_RATES
944 WHERE From_Currency = p_Source_Currency
945 AND TO_Currency = P_Target_Currency
946 AND Conversion_type = l_rate_type
947 AND Conversion_date = l_period_end_date;
948
949 EXCEPTION
950
951 WHEN OTHERS THEN
952
953 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
954 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'gcs.plsql.gcs_utility_pkg.'
955 ||'get_conversion_rate'|| SUBSTR(SQLERRM, 1, 255), null);
956 END IF;
957
958 l_conv_rate := 1;
959 p_errbuf := 'NO_RATE_ERROR';
960 p_errcode := 2;
961
962 END;
963
964 EXCEPTION
965 WHEN OTHERS THEN
966 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
967 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'gcs.plsql.gcs_utility_pkg.'
968 ||'get_conversion_rate'|| SUBSTR(SQLERRM, 1, 255), null);
969 END IF;
970
971 -- Get the conversion Rate for 'Corporate' rate type and period
972 -- end date combination.
973
974 BEGIN
975
976 -- Get the conversion Rate for 'Corporate' rate type and period
977 -- end date combination.
978
979 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
980 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
981 'gcs.plsql.gcs_utility_pkg.get_conversion_rate.'
982 ||'Get the rate for ''Corporate''rate type and period '
983 ||'end date combination', null);
984 END IF;
985
986 SELECT conversion_rate
987 INTO l_conv_rate
988 FROM GL_DAILY_RATES
989 WHERE From_Currency = p_Source_Currency
990 AND TO_Currency = P_Target_Currency
991 AND Conversion_type = 'Corporate'
992 AND Conversion_date = l_period_end_date;
993
994 EXCEPTION
995
996 WHEN OTHERS THEN
997 l_conv_rate := 1;
998 p_errbuf := 'NO_RATE_ERROR';
999 p_errcode :=2;
1000
1001 END; -- Corporate Type Block Ends
1002
1003 END; -- Default Rate Type Block End;
1004
1005 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1006 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1007 gcs_utility_pkg.g_module_success
1008 || ' '
1009 || 'gcs.plsql.gcs_utility_pkg.'
1010 ||'get_conversion_rate'
1011 || '() '
1012 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS'),
1013 null);
1014
1015 END IF;
1016
1017 p_conversion_rate := l_conv_rate;
1018 p_errbuf := p_errbuf;
1019 p_errcode := 1;
1020
1021 EXCEPTION
1022 WHEN OTHERS THEN
1023
1024 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1025 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1026 gcs_utility_pkg.g_module_success
1027 || ' '
1028 || 'gcs.plsql.gcs_utility_pkg.'
1029 ||'get_conversion_rate'
1030 || '() '
1031 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS'),
1032 null);
1033 END IF;
1034 p_conversion_rate := 1;
1035 p_errbuf := 'NO_RATE_ERROR';
1036 p_errcode :=2;
1037
1038
1039 END get_conversion_rate;
1040
1041 --
1042 -- Function
1043 -- populate_calendar_map_details
1044 -- Purpose
1045 -- Takes the source calendar period and maps it to the target calendar period
1046 -- Arguments
1047 -- p_source_cal_period_id Calendar Period
1048 -- Example
1049 -- Notes
1050 --
1051
1052 PROCEDURE populate_calendar_map_details(p_cal_period_id IN NUMBER,
1053 p_source_period_flag IN VARCHAR2,
1054 p_greater_than_flag IN VARCHAR2)
1055
1056 IS
1057
1058 l_data_exists NUMBER(15);
1059 l_cal_period_record gcs_utility_pkg.r_cal_period_info;
1060 l_period_num_attr NUMBER :=
1061 gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-GL_PERIOD_NUM').attribute_id;
1062 l_period_num_version NUMBER :=
1063 gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-GL_PERIOD_NUM').version_id;
1064 l_period_year_attr NUMBER :=
1065 gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-ACCOUNTING_YEAR').attribute_id;
1066 l_period_year_version NUMBER :=
1067 gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-ACCOUNTING_YEAR').version_id;
1068
1069 BEGIN
1070 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1071 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'gcs.plsql.gcs_utility_pkg.populate_calendar_map_details.begin', '<<Enter>>');
1072 END IF;
1073
1074 delete from gcs_cal_period_maps_gt;
1075
1076 gcs_utility_pkg.get_cal_period_details( p_cal_period_id,
1077 l_cal_period_record);
1078
1079 --Insert one row where source and target are the same
1080 INSERT INTO gcs_cal_period_maps_gt
1081 ( source_cal_period_id,
1082 target_cal_period_id)
1083 VALUES
1084 ( p_cal_period_id,
1085 p_cal_period_id);
1086
1087
1088 IF (p_source_period_flag = 'N') THEN
1089
1090 IF (p_greater_than_flag = 'N') THEN
1091
1092 --Insert all mapping options
1093 INSERT INTO gcs_cal_period_maps_gt
1094 ( source_cal_period_id,
1095 target_cal_period_id)
1096 SELECT fcpb_source.cal_period_id,
1097 p_cal_period_id
1098 FROM fem_cal_periods_b fcpb_source,
1099 fem_cal_periods_b fcpb_target,
1100 gcs_cal_period_maps gcpm,
1101 fem_cal_periods_attr fcpb_source_num,
1102 fem_cal_periods_attr fcpb_source_year,
1103 gcs_cal_period_map_dtls gcpmd
1104 WHERE fcpb_target.cal_period_id = p_cal_period_id
1105 AND fcpb_target.calendar_id = gcpm.target_calendar_id
1106 AND fcpb_target.dimension_group_id = gcpm.target_dimension_group_id
1107 AND gcpm.cal_period_map_id = gcpmd.cal_period_map_id
1108 AND gcpmd.target_period_number = l_cal_period_record.cal_period_number
1109 AND fcpb_source_num.attribute_id = l_period_num_attr
1110 AND fcpb_source_num.version_id = l_period_num_version
1111 AND fcpb_source_year.attribute_id = l_period_year_attr
1112 AND fcpb_source_year.version_id = l_period_year_version
1113 AND fcpb_source.cal_period_id = fcpb_source_num.cal_period_id
1114 AND fcpb_source.cal_period_id = fcpb_source_year.cal_period_id
1115 AND fcpb_source.calendar_id = gcpm.source_calendar_id
1116 AND fcpb_source.dimension_group_id = gcpm.source_dimension_group_id
1117 AND fcpb_source_num.number_assign_value = gcpmd.source_period_number
1118 AND fcpb_source_year.number_assign_value = DECODE(gcpmd.target_relative_year_code,
1119 'CURRENT', l_cal_period_record.cal_period_year,
1120 'PRIOR', l_cal_period_record.cal_period_year + 1,
1121 'FOLLOWING', l_cal_period_record.cal_period_year - 1);
1122 ELSE
1123
1124 --Insert all mapping options
1125 INSERT INTO gcs_cal_period_maps_gt
1126 ( source_cal_period_id,
1127 target_cal_period_id)
1128 SELECT fcpb_source.cal_period_id,
1129 fcpb_target.cal_period_id
1130 FROM fem_cal_periods_b fcpb_source,
1131 fem_cal_periods_b fcpb_target,
1132 gcs_cal_period_maps gcpm,
1133 fem_cal_periods_attr fcpb_source_num,
1134 fem_cal_periods_attr fcpb_source_year,
1135 gcs_cal_period_map_dtls gcpmd
1136 WHERE fcpb_target.cal_period_id >= p_cal_period_id
1137 AND fcpb_target.calendar_id = gcpm.target_calendar_id
1138 AND fcpb_target.dimension_group_id = gcpm.target_dimension_group_id
1139 AND gcpm.cal_period_map_id = gcpmd.cal_period_map_id
1140 AND gcpmd.target_period_number = l_cal_period_record.cal_period_number
1141 AND fcpb_source_num.attribute_id = l_period_num_attr
1142 AND fcpb_source_num.version_id = l_period_num_version
1143 AND fcpb_source_year.attribute_id = l_period_year_attr
1144 AND fcpb_source_year.version_id = l_period_year_version
1145 AND fcpb_source.cal_period_id = fcpb_source_num.cal_period_id
1146 AND fcpb_source.cal_period_id = fcpb_source_year.cal_period_id
1147 AND fcpb_source.calendar_id = gcpm.source_calendar_id
1148 AND fcpb_source.dimension_group_id = gcpm.source_dimension_group_id
1149 AND fcpb_source_num.number_assign_value = gcpmd.source_period_number
1150 AND fcpb_source_year.number_assign_value = DECODE(gcpmd.target_relative_year_code,
1151 'CURRENT', l_cal_period_record.cal_period_year,
1152 'PRIOR', l_cal_period_record.cal_period_year + 1,
1153 'FOLLOWING', l_cal_period_record.cal_period_year - 1);
1154 INSERT INTO gcs_cal_period_maps_gt
1155 ( source_cal_period_id,
1156 target_cal_period_id)
1157 SELECT distinct target_cal_period_id,
1158 target_cal_period_id
1159 FROM gcs_cal_period_maps_gt
1160 WHERE target_cal_period_id <> p_cal_period_id;
1161
1162 END IF;
1163
1164 ELSE
1165 --Insert all mapping options
1166 INSERT INTO gcs_cal_period_maps_gt
1167 ( source_cal_period_id,
1168 target_cal_period_id)
1169 SELECT p_cal_period_id,
1170 fcpb_target.cal_period_id
1171 FROM fem_cal_periods_b fcpb_source,
1172 fem_cal_periods_b fcpb_target,
1173 gcs_cal_period_maps gcpm,
1174 fem_cal_periods_attr fcpb_target_num,
1175 fem_cal_periods_attr fcpb_target_year,
1176 gcs_cal_period_map_dtls gcpmd
1177 WHERE fcpb_source.cal_period_id = p_cal_period_id
1178 AND fcpb_source.calendar_id = gcpm.source_calendar_id
1179 AND fcpb_source.dimension_group_id = gcpm.source_dimension_group_id
1180 AND gcpm.cal_period_map_id = gcpmd.cal_period_map_id
1181 AND gcpmd.source_period_number = l_cal_period_record.cal_period_number
1182 AND fcpb_target_num.attribute_id = l_period_num_attr
1183 AND fcpb_target_num.version_id = l_period_num_version
1184 AND fcpb_target_year.attribute_id = l_period_year_attr
1185 AND fcpb_target_year.version_id = l_period_year_version
1186 AND fcpb_target.cal_period_id = fcpb_target_num.cal_period_id
1187 AND fcpb_target.cal_period_id = fcpb_target_year.cal_period_id
1188 AND fcpb_target.calendar_id = gcpm.target_calendar_id
1189 AND fcpb_target.dimension_group_id = gcpm.target_dimension_group_id
1190 AND fcpb_target_num.number_assign_value = gcpmd.target_period_number
1191 AND fcpb_target_year.number_assign_value = DECODE(gcpmd.target_relative_year_code,
1192 'CURRENT', l_cal_period_record.cal_period_year,
1193 'PRIOR', l_cal_period_record.cal_period_year - 1,
1194 'FOLLOWING', l_cal_period_record.cal_period_year + 1);
1195 END IF;
1196
1197 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1198 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'gcs.plsql.gcs_utility_pkg.init_dimension_attr_info.end', '<<Exit>>');
1199 END IF;
1200
1201 END;
1202
1203
1204
1205 ---*************************************************************-----
1206 --
1207 -- Function
1208 -- Get_Associated_Value_Set_Id
1209 -- Purpose
1210 -- Get associated_value_set_id for the dimension
1211 -- Arguments
1212 -- p_dim_col Dimension
1213 -- Example
1214 -- GCS_UTILITY_PKG.Get_Associated_Value_Set_Id('LINE_ITEM_ID')
1215 -- Notes
1216 --
1217
1218 FUNCTION Get_Associated_Value_Set_Id(p_dim_col VARCHAR2) RETURN NUMBER IS
1219 BEGIN
1220 return g_gcs_dimension_info(p_dim_col).associated_value_set_id;
1221 EXCEPTION
1222 WHEN NO_DATA_FOUND THEN
1223 return -1;
1224 END Get_Associated_Value_Set_Id;
1225
1226 ---*************************************************************-----
1227
1228 BEGIN
1229
1230 init_dimension_attr_info();
1231 init_dimension_info();
1232
1233 BEGIN
1234 SELECT fch_global_vs_combo_id
1235 INTO g_fch_global_vs_combo_id
1236 FROM gcs_system_options;
1237 EXCEPTION
1238 WHEN OTHERS THEN
1239 NULL;
1240 END;
1241
1242 END GCS_UTILITY_PKG;
1243
1244