[Home] [Help]
PACKAGE BODY: APPS.GCS_HIERARCHIES_PKG
Source
1 PACKAGE BODY GCS_HIERARCHIES_PKG AS
2 /* $Header: gcshierb.pls 120.3 2006/05/22 12:36:19 smatam noship $ */
3 --
4 -- Package
5 -- gcs_hierarchies_pkg
6 -- Purpose
7 -- Package procedures for Consolidation Hierarchies
8 -- History
9 -- 28-JUN-04 M Ward Created
10 --
11 --
12 -- Private Global Variables
13 --
14 -- The API name
15 g_api CONSTANT VARCHAR2(40) := 'gcs.plsql.GCS_HIERARCHIES_PKG';
16 -- Action types for writing module information to the log file. Used for
17 -- the procedure log_file_module_write.
18 g_module_enter CONSTANT VARCHAR2(2) := '>>';
19 g_module_success CONSTANT VARCHAR2(2) := '<<';
20 g_module_failure CONSTANT VARCHAR2(2) := '<x';
21 -- A newline character. Included for convenience when writing long strings.
22 g_nl CONSTANT VARCHAR2(1) := '
23 ';
24 -- Create an associative array (hashtable) to hold the entities that have
25 -- already been traversed in a hierarchy. This is to prevent infinite
26 -- looping in the case of mutual ownerships
27 TYPE EntitiesTableType IS TABLE OF BINARY_INTEGER INDEX BY BINARY_INTEGER;
28
29 --
30 -- Procedure
31 -- Insert_Row
32 -- Purpose
33 -- Inserts a row into the gcs_lex_map_structs table.
34 -- Arguments
35 -- row_id
36 -- hierarchy_id
37 -- top_entity_id
38 -- start_date
39 -- calendar_id
40 -- dimension_group_id
41 -- ie_by_org_code
42 -- balance_by_org_flag
43 -- enabled_flag
44 -- threshold_amount
45 -- threshold_currency
46 -- fem_ledger_id
47 -- column_name
48 -- object_version_number
49 -- hierarchy_name
50 -- description
51 -- last_update_date
52 -- last_updated_by
53 -- last_update_login
54 -- creation_date
55 -- created_by
56 -- Example
57 -- GCS_HIERARCHIES_PKG.Insert_Row(...);
58 -- Notes
59 --
60 PROCEDURE Insert_Row(row_id IN OUT NOCOPY VARCHAR2,
61 hierarchy_id VARCHAR2,
62 top_entity_id NUMBER,
63 start_date VARCHAR2,
64 calendar_id NUMBER,
65 dimension_group_id NUMBER,
66 ie_by_org_code VARCHAR2,
67 balance_by_org_flag VARCHAR2,
68 enabled_flag VARCHAR2,
69 threshold_amount NUMBER,
70 threshold_currency VARCHAR2,
71 fem_ledger_id NUMBER,
72 column_name VARCHAR2,
73 object_version_number NUMBER,
74 hierarchy_name VARCHAR2,
75 description VARCHAR2,
76 last_update_date DATE,
77 last_updated_by NUMBER,
78 last_update_login NUMBER,
79 creation_date DATE,
80 created_by NUMBER) IS
81 CURSOR hier_row IS
82 SELECT rowid
83 FROM gcs_hierarchies_b hb
84 WHERE hb.hierarchy_id = insert_row.hierarchy_id;
85 BEGIN
86 IF hierarchy_id IS NULL THEN
87 raise no_data_found;
88 END IF;
89 INSERT INTO gcs_hierarchies_b
90 (hierarchy_id,
91 top_entity_id,
92 start_date,
93 calendar_id,
94 dimension_group_id,
95 ie_by_org_code,
96 balance_by_org_flag,
97 enabled_flag,
98 threshold_amount,
99 threshold_currency,
100 fem_ledger_id,
101 column_name,
102 object_version_number,
103 last_update_date,
104 last_updated_by,
105 last_update_login,
106 creation_date,
107 created_by)
108 SELECT hierarchy_id,
109 top_entity_id,
110 start_date,
111 calendar_id,
112 dimension_group_id,
113 ie_by_org_code,
114 balance_by_org_flag,
115 enabled_flag,
116 threshold_amount,
117 threshold_currency,
118 fem_ledger_id,
119 column_name,
120 object_version_number,
121 last_update_date,
122 last_updated_by,
123 last_update_login,
124 creation_date,
125 created_by
126 FROM dual
127 WHERE NOT EXISTS
128 (SELECT 1
129 FROM gcs_hierarchies_b hb
130 WHERE hb.hierarchy_id = insert_row.hierarchy_id);
131 INSERT INTO gcs_hierarchies_tl
132 (hierarchy_id,
133 language,
134 source_lang,
135 hierarchy_name,
136 description,
137 last_update_date,
138 last_updated_by,
139 last_update_login,
140 creation_date,
141 created_by)
142 SELECT hierarchy_id,
143 userenv('LANG'),
144 userenv('LANG'),
145 hierarchy_name,
146 description,
147 last_update_date,
148 last_updated_by,
149 last_update_login,
150 creation_date,
151 created_by
152 FROM dual
153 WHERE NOT EXISTS (SELECT 1
154 FROM gcs_hierarchies_tl htl
155 WHERE htl.hierarchy_id = insert_row.hierarchy_id
156 AND htl.language = userenv('LANG'));
157 OPEN hier_row;
158 FETCH hier_row
159 INTO row_id;
160 IF hier_row%NOTFOUND THEN
161 CLOSE hier_row;
162 raise no_data_found;
163 END IF;
164 CLOSE hier_row;
165 END Insert_Row;
166 --
167 -- Procedure
168 -- Update_Row
169 -- Purpose
170 -- Updates a row in the gcs_lex_map_structs table.
171 -- Arguments
172 -- hierarchy_id
173 -- top_entity_id
174 -- start_date
175 -- calendar_id
176 -- dimension_group_id
177 -- ie_by_org_code
178 -- balance_by_org_flag
179 -- enabled_flag
180 -- threshold_amount
181 -- threshold_currency
182 -- fem_ledger_id
183 -- column_name
184 -- object_version_number
185 -- hierarchy_name
186 -- description
187 -- last_update_date
188 -- last_udpated_by
189 -- last_update_login
190 -- Example
191 -- GCS_HIERARCHIES_PKG.Update_Row(...);
192 -- Notes
193 --
194 PROCEDURE Update_Row(hierarchy_id VARCHAR2,
195 top_entity_id NUMBER,
196 start_date VARCHAR2,
197 calendar_id NUMBER,
198 dimension_group_id NUMBER,
199 ie_by_org_code VARCHAR2,
200 balance_by_org_flag VARCHAR2,
201 enabled_flag VARCHAR2,
202 threshold_amount NUMBER,
203 threshold_currency VARCHAR2,
204 fem_ledger_id NUMBER,
205 column_name VARCHAR2,
206 object_version_number NUMBER,
207 hierarchy_name VARCHAR2,
208 description VARCHAR2,
209 last_update_date DATE,
210 last_updated_by NUMBER,
211 last_update_login NUMBER,
212 creation_date DATE,
213 created_by NUMBER) IS
214 BEGIN
215 UPDATE gcs_hierarchies_b hb
216 SET top_entity_id = update_row.top_entity_id,
217 start_date = update_row.start_date,
218 calendar_id = update_row.calendar_id,
219 dimension_group_id = update_row.dimension_group_id,
220 ie_by_org_code = update_row.ie_by_org_code,
221 balance_by_org_flag = update_row.balance_by_org_flag,
222 enabled_flag = update_row.enabled_flag,
223 threshold_amount = update_row.threshold_amount,
224 threshold_currency = update_row.threshold_currency,
225 fem_ledger_id = update_row.fem_ledger_id,
226 column_name = update_row.column_name,
227 object_version_number = update_row.object_version_number,
228 last_update_date = update_row.last_update_date,
229 last_updated_by = update_row.last_updated_by,
230 last_update_login = update_row.last_update_login
231 WHERE hb.hierarchy_id = update_row.hierarchy_id;
232 IF SQL%NOTFOUND THEN
233 raise no_data_found;
234 END IF;
235 INSERT INTO gcs_hierarchies_tl
236 (hierarchy_id,
237 language,
238 source_lang,
239 hierarchy_name,
240 description,
241 last_update_date,
242 last_updated_by,
243 last_update_login,
244 creation_date,
245 created_by)
246 SELECT hierarchy_id,
247 userenv('LANG'),
248 userenv('LANG'),
249 hierarchy_name,
250 description,
251 last_update_date,
252 last_updated_by,
253 last_update_login,
254 creation_date,
255 created_by
256 FROM dual
257 WHERE NOT EXISTS (SELECT 1
258 FROM gcs_hierarchies_tl htl
259 WHERE htl.hierarchy_id = update_row.hierarchy_id
260 AND htl.language = userenv('LANG'));
261 UPDATE gcs_hierarchies_tl ht
262 SET hierarchy_name = update_row.hierarchy_name,
263 description = update_row.description,
264 last_update_date = update_row.last_update_date,
265 last_updated_by = update_row.last_updated_by,
266 last_update_login = update_row.last_update_login
267 WHERE ht.hierarchy_id = update_row.hierarchy_id
268 AND ht.language = userenv('LANG');
269 IF SQL%NOTFOUND THEN
270 raise no_data_found;
271 END IF;
272 END Update_Row;
273 --
274 -- Procedure
275 -- Load_Row
276 -- Purpose
277 -- Loads a row into the gcs_lex_map_structs table.
278 -- Arguments
279 -- hierarchy_id
280 -- owner
281 -- last_update_date
282 -- custom_mode
283 -- top_entity_id
284 -- start_date
285 -- calendar_id
286 -- dimension_group_id
287 -- ie_by_org_code
288 -- balance_by_org_flag
289 -- enabled_flag
290 -- threshold_amount
291 -- threshold_currency
292 -- fem_ledger_id
293 -- column_name
294 -- object_version_number
295 -- hierarchy_name
296 -- description
297 -- Example
298 -- GCS_HIERARCHIES_PKG.Load_Row(...);
299 -- Notes
300 --
301 PROCEDURE Load_Row(hierarchy_id VARCHAR2,
302 owner VARCHAR2,
303 last_update_date VARCHAR2,
304 custom_mode VARCHAR2,
305 top_entity_id NUMBER,
306 start_date VARCHAR2,
307 calendar_id NUMBER,
308 dimension_group_id NUMBER,
309 ie_by_org_code VARCHAR2,
310 balance_by_org_flag VARCHAR2,
311 enabled_flag VARCHAR2,
312 threshold_amount NUMBER,
313 threshold_currency VARCHAR2,
314 fem_ledger_id NUMBER,
315 column_name VARCHAR2,
316 object_version_number NUMBER,
317 hierarchy_name VARCHAR2,
318 description VARCHAR2) IS
319 row_id VARCHAR2(64);
320 f_luby NUMBER; -- entity owner in file
321 f_ludate DATE; -- entity update date in file
322 db_luby NUMBER; -- entity owner in db
323 db_ludate DATE; -- entity update date in db
324 f_start_date DATE; -- start date in file
325 BEGIN
326 -- Get last updated information from the loader data file
327 f_luby := fnd_load_util.owner_id(owner);
328 f_ludate := nvl(to_date(last_update_date, 'YYYY/MM/DD'), sysdate);
329 f_start_date := nvl(to_date(start_date, 'YYYY/MM/DD'), sysdate);
330 BEGIN
331 SELECT hb.last_updated_by, hb.last_update_date
332 INTO db_luby, db_ludate
333 FROM GCS_HIERARCHIES_B hb
334 WHERE hb.hierarchy_id = load_row.hierarchy_id;
335 -- Test for customization information
336 IF fnd_load_util.upload_test(f_luby,
337 f_ludate,
338 db_luby,
339 db_ludate,
340 custom_mode) THEN
341 update_row(hierarchy_id => HIERARCHY_ID,
342 top_entity_id => TOP_ENTITY_ID,
343 start_date => F_START_DATE,
344 calendar_id => CALENDAR_ID,
345 dimension_group_id => DIMENSION_GROUP_ID,
346 ie_by_org_code => IE_BY_ORG_CODE,
347 balance_by_org_flag => BALANCE_BY_ORG_FLAG,
348 enabled_flag => ENABLED_FLAG,
349 threshold_amount => THRESHOLD_AMOUNT,
350 threshold_currency => THRESHOLD_CURRENCY,
351 fem_ledger_id => FEM_LEDGER_ID,
352 column_name => COLUMN_NAME,
353 object_version_number => OBJECT_VERSION_NUMBER,
354 hierarchy_name => HIERARCHY_NAME,
355 description => DESCRIPTION,
356 last_update_date => f_ludate,
357 last_updated_by => f_luby,
358 last_update_login => 0,
359 creation_date => f_ludate,
360 created_by => f_luby);
361 END IF;
362 EXCEPTION
363 WHEN NO_DATA_FOUND THEN
364 insert_row(row_id => row_id,
365 hierarchy_id => HIERARCHY_ID,
366 top_entity_id => TOP_ENTITY_ID,
367 start_date => F_START_DATE,
368 calendar_id => CALENDAR_ID,
369 dimension_group_id => DIMENSION_GROUP_ID,
370 ie_by_org_code => IE_BY_ORG_CODE,
371 balance_by_org_flag => BALANCE_BY_ORG_FLAG,
372 enabled_flag => ENABLED_FLAG,
373 threshold_amount => THRESHOLD_AMOUNT,
374 threshold_currency => THRESHOLD_CURRENCY,
375 fem_ledger_id => FEM_LEDGER_ID,
376 column_name => COLUMN_NAME,
377 object_version_number => OBJECT_VERSION_NUMBER,
378 hierarchy_name => HIERARCHY_NAME,
379 description => DESCRIPTION,
380 last_update_date => f_ludate,
381 last_updated_by => f_luby,
382 last_update_login => 0,
383 creation_date => f_ludate,
384 created_by => f_luby);
385 END;
386 END Load_Row;
387 --
388 -- Procedure
389 -- Translate_Row
390 -- Purpose
391 -- Updates translated infromation for a row in the
392 -- gcs_hierarchies_tl table.
393 -- Arguments
394 -- hierarchy_id
395 -- owner
396 -- last_update_date
397 -- custom_mode
398 -- hierarchy_name
399 -- description
400 -- Example
401 -- GCS_HIERARCHIES_PKG.Translate_Row(...);
402 -- Notes
403 --
404 PROCEDURE Translate_Row(hierarchy_id NUMBER,
405 owner VARCHAR2,
406 last_update_date VARCHAR2,
407 custom_mode VARCHAR2,
408 hierarchy_name VARCHAR2,
409 description VARCHAR2) IS
410 f_luby NUMBER; -- entity owner in file
411 f_ludate DATE; -- entity update date in file
412 db_luby NUMBER; -- entity owner in db
413 db_ludate DATE; -- entity update date in db
414 BEGIN
415 -- Get last updated information from the loader data file
416 f_luby := fnd_load_util.owner_id(owner);
417 f_ludate := nvl(to_date(last_update_date, 'YYYY/MM/DD'), sysdate);
418 BEGIN
419 SELECT htl.last_updated_by, htl.last_update_date
420 INTO db_luby, db_ludate
421 FROM GCS_HIERARCHIES_TL htl
422 WHERE htl.hierarchy_id = translate_row.hierarchy_id
423 AND htl.language = userenv('LANG');
424 -- Test for customization information
425 IF fnd_load_util.upload_test(f_luby,
426 f_ludate,
427 db_luby,
428 db_ludate,
429 custom_mode) THEN
430 UPDATE gcs_hierarchies_tl htl
431 SET hierarchy_name = translate_row.hierarchy_name,
432 description = translate_row.description,
433 source_lang = userenv('LANG'),
434 last_update_date = f_ludate,
435 last_updated_by = f_luby,
436 last_update_login = 0
437 WHERE htl.hierarchy_id = translate_row.hierarchy_id
438 AND userenv('LANG') IN (htl.language, htl.source_lang);
439 END IF;
440 EXCEPTION
441 WHEN NO_DATA_FOUND THEN
442 null;
443 END;
444 END Translate_Row;
445 --
446 -- Procedure
447 -- ADD_LANGUAGE
448 -- Purpose
449 --
450 -- Arguments
451 --
452 -- GCS_HIERARCHIES_PKG.ADD_LANGUAGE();
453 -- Notes
454 --
455 procedure ADD_LANGUAGE is
456 begin
457 insert /*+ append parallel(tt) */
458 into GCS_HIERARCHIES_TL tt
459 (HIERARCHY_ID,
460 LANGUAGE,
461 SOURCE_LANG,
462 HIERARCHY_NAME,
463 CREATION_DATE,
464 CREATED_BY,
465 LAST_UPDATE_DATE,
466 LAST_UPDATED_BY,
467 LAST_UPDATE_LOGIN,
468 DESCRIPTION)
469 select /*+ parallel(v) parallel(t) use_nl(t) */
470 v.*
471 from (SELECT /*+ no_merge ordered parellel(b) */
472 B.HIERARCHY_ID,
473 L.LANGUAGE_CODE,
474 B.SOURCE_LANG,
475 B.HIERARCHY_NAME,
476 B.CREATION_DATE,
477 B.CREATED_BY,
478 B.LAST_UPDATE_DATE,
479 B.LAST_UPDATED_BY,
480 B.LAST_UPDATE_LOGIN,
481 B.DESCRIPTION
482 from GCS_HIERARCHIES_TL B, FND_LANGUAGES L
483 where L.INSTALLED_FLAG in ('I', 'B')
484 and B.LANGUAGE = userenv('LANG')) v,
485 GCS_HIERARCHIES_TL t
486 where T.HIERARCHY_ID(+) = v.HIERARCHY_ID
487 and T.LANGUAGE(+) = v.LANGUAGE_CODE
488 and t.HIERARCHY_ID IS NULL;
489 end ADD_LANGUAGE;
490 --
491 -- Private Procedures and Functions for Multiple Parents
492 --
493 --
494 -- Procedure
495 -- Module_Log_Write
496 -- Purpose
497 -- Write the procedure or function entered or exited, and the time that
498 -- this happened. Write it to the log repository.
499 -- Arguments
500 -- p_module Name of the module
501 -- p_action_type Entered, Exited Successfully, or Exited with Failure
502 -- Example
503 -- GCS_HIERARCHIES_PKG.Module_Log_Write
504 -- Notes
505 --
506 PROCEDURE Module_Log_Write(p_module VARCHAR2, p_action_type VARCHAR2) IS
507 BEGIN
508 -- Only print if the log level is set at the appropriate level
509 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE THEN
510 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
511 g_api || '.' || p_module,
512 p_action_type || ' ' || p_module || '() ' ||
513 to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
514 END IF;
515 FND_FILE.PUT_LINE(FND_FILE.LOG,
516 p_action_type || ' ' || p_module || '() ' ||
517 to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
518 END Module_Log_Write;
519 --
520 -- Procedure
521 -- Write_To_Log
522 -- Purpose
523 -- Write the text given to the log in 3500 character increments
524 -- this happened. Write it to the log repository.
525 -- Arguments
526 -- p_module Name of the module
527 -- p_level Logging level
528 -- p_text Text to write
529 -- Example
530 -- GCS_HIERARCHIES_PKG.Write_To_Log
531 -- Notes
532 --
533 PROCEDURE Write_To_Log(p_module VARCHAR2,
534 p_level NUMBER,
535 p_text VARCHAR2) IS
536 api_module_concat VARCHAR2(200);
537 text_with_date VARCHAR2(32767);
538 text_with_date_len NUMBER;
539 curr_index NUMBER;
540 BEGIN
541 -- Only print if the log level is set at the appropriate level
542 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= p_level THEN
543 api_module_concat := g_api || '.' || p_module;
544 text_with_date := to_char(sysdate, 'DD-MON-YYYY HH:MI:SS') || g_nl ||
545 p_text;
546 text_with_date_len := length(text_with_date);
547 curr_index := 1;
548 WHILE curr_index <= text_with_date_len LOOP
549 fnd_log.string(p_level,
550 api_module_concat,
551 substr(text_with_date, curr_index, 3500));
552 curr_index := curr_index + 3500;
553 END LOOP;
554 END IF;
555 END Write_To_Log;
556 --
557 -- Function
558 -- Entity_Exists
559 -- Purpose
560 -- Gets an entry from the hashtable with the given key. If no entry exists
561 -- this will simply return NULL.
562 -- Arguments
563 -- p_entities_hashtable Hashtable of entities
564 -- p_entity_id The key
565 -- Return Value
566 -- The value in the hashtable, of NULL if no value exists
567 -- Example
568 -- GCS_HIERARCHIES_PKG.Entity_In_Hashtable(...);
569 -- Notes
570 --
571 FUNCTION Entity_Exists(p_entities_hashtable IN OUT NOCOPY EntitiesTableType,
572 p_entity_id NUMBER) RETURN NUMBER IS
573 BEGIN
574 RETURN p_entities_hashtable(p_entity_id);
575 EXCEPTION
576 WHEN OTHERS THEN
577 RETURN NULL;
578 END Entity_Exists;
579 --
580 -- Procedure
581 -- Calc_Delta_To_Single_Parent
582 -- Purpose
583 -- Calculate the delta ownership to a single parent from the original
584 -- entity. The current entity given is the one we have gotten to in the
585 -- recursive traversal. We continue until we get to the parent or there are
586 -- no more parents to traverse.
587 -- Arguments
588 -- p_hierarchy_id Hierarchy for which the logic must be performed
589 -- p_original_entity_id Original child entity for which the logic must
590 -- be performed
591 -- p_parent_entity_id The parent for which the delta ownership must
592 -- be found
593 -- p_current_entity_id The entity we have gotten to in the traversal
594 -- p_effective_ownership The effective ownership of the original entity
595 -- by the current entity
596 -- p_start_date Date range for performing the logic
597 -- p_end_date Date range for performing the logic
598 -- p_traversed_entities List of entities we have already traversed to
599 -- get to the current entity
600 -- p_calc_parent_entities List of entities for which we have already run
601 -- delta calculations
602 -- Example
603 -- GCS_HIERARCHIES_PKG.Calc_Delta_To_Single_Parent(...);
604 -- Notes
605 --
606 PROCEDURE Calc_Delta_To_Single_Parent(p_hierarchy_id NUMBER,
607 p_original_entity_id NUMBER,
608 p_parent_entity_id NUMBER,
609 p_current_entity_id NUMBER,
610 p_effective_ownership NUMBER,
611 p_start_date DATE,
612 p_end_date DATE,
613 p_traversed_entities IN OUT NOCOPY EntitiesTableType,
614 p_calc_parent_entities IN OUT NOCOPY EntitiesTableType) IS
615 -- Used to find the list of existing delta ownership rows that must be
616 -- taken into consideration when creating and updating delta ownership rows
617 CURSOR get_existing_delta_rows_c IS
618 SELECT r.cons_relationship_id,
619 r.start_date,
620 r.end_date,
621 r.delta_owned
622 FROM gcs_cons_relationships r
623 WHERE r.hierarchy_id = p_hierarchy_id
624 AND r.parent_entity_id = p_parent_entity_id
625 AND r.child_entity_id = p_original_entity_id
626 AND r.actual_ownership_flag = 'N'
627 AND r.start_date <= nvl(r.end_date, r.start_date)
628 AND r.start_date <= nvl(p_end_date, r.start_date)
629 AND nvl(r.end_date, p_start_date) >= p_start_date
630 ORDER BY r.start_date;
631 -- The end date of the last existing delta relationship that we found. This
632 -- is used to create "filler" delta relationships between existing delta
633 -- relationships so that we do not have unwanted gaps, and we only have one
634 -- delta relationship for a particular date.
635 last_end_date DATE;
636 -- Used to get the consolidated parents for the current entity. The decodes
637 -- exist to pick the most restrictive date range possible.
638 CURSOR get_curr_entity_parent_c IS
639 SELECT r.parent_entity_id,
640 r.ownership_percent,
641 decode(sign(r.start_date - p_start_date),
642 1,
643 r.start_date,
644 p_start_date) start_date,
645 decode(r.end_date,
646 null,
647 p_end_date,
648 decode(p_end_date,
649 null,
650 r.end_date,
651 decode(sign(r.end_date - p_end_date),
652 1,
653 p_end_date,
654 r.end_date))) end_date
655 FROM gcs_cons_relationships r
656 WHERE r.hierarchy_id = p_hierarchy_id
657 AND r.child_entity_id = p_current_entity_id
658 AND r.actual_ownership_flag = 'Y'
659 AND r.start_date <= nvl(r.end_date, r.start_date)
660 AND p_start_date <= nvl(r.end_date, p_start_date)
661 AND nvl(p_end_date, r.start_date) >= r.start_date;
662 fnd_user_id NUMBER;
663 fnd_login_id NUMBER;
664 v_module VARCHAR2(30);
665 BEGIN
666 v_module := 'Calc_Delta_To_Single_Parent';
667 module_log_write(v_module, g_module_enter);
668 -- If we have already come up this path or we have calculated deltas for
669 -- this parent entity, then return
670 IF entity_exists(p_traversed_entities, p_current_entity_id) =
671 p_current_entity_id OR
672 entity_exists(p_calc_parent_entities, p_current_entity_id) =
673 p_current_entity_id THEN
674 module_log_write(v_module, g_module_success);
675 RETURN;
676 END IF;
677 -- If we have reached the desired parent entity, create the necessary rows.
678 -- Otherwise keep going up the hierarchy.
679 IF p_current_entity_id = p_parent_entity_id THEN
680 fnd_user_id := fnd_global.user_id;
681 fnd_login_id := fnd_global.login_id;
682 -- Initialize the previous end date to be the day prior to the start date
683 -- of the new range
684 last_end_date := p_start_date - 1;
685 -- Go through each of the delta rows, and perform the necessary actions
686 -- to update the delta ownership
687 FOR delta_row IN get_existing_delta_rows_c LOOP
688 -- If this relationship straddles the start date, split it in two
689 IF delta_row.start_date < p_start_date THEN
690 INSERT INTO gcs_cons_relationships
691 (cons_relationship_id,
692 hierarchy_id,
693 parent_entity_id,
694 child_entity_id,
695 ownership_percent,
696 start_date,
697 treatment_id,
698 curr_treatment_id,
699 object_version_number,
700 creation_date,
701 created_by,
702 last_update_date,
703 last_updated_by,
704 last_update_login,
705 end_date,
706 delta_owned,
707 dominant_parent_flag,
708 actual_ownership_flag)
709 VALUES
710 (gcs_cons_relationships_s.nextval,
711 p_hierarchy_id,
712 p_parent_entity_id,
713 p_original_entity_id,
714 0,
715 delta_row.start_date,
716 null,
717 null,
718 1,
719 sysdate,
720 fnd_user_id,
721 sysdate,
722 fnd_user_id,
723 fnd_login_id,
724 p_start_date - 1,
725 delta_row.delta_owned,
726 'N',
727 'N');
728 UPDATE gcs_cons_relationships r
729 SET start_date = p_start_date
730 WHERE r.cons_relationship_id = delta_row.cons_relationship_id;
731 END IF;
732 -- If this relationship straddles the end date, split it in two
733 IF p_end_date IS NOT NULL AND
734 nvl(delta_row.end_date, p_end_date + 1) > p_end_date THEN
735 INSERT INTO gcs_cons_relationships
736 (cons_relationship_id,
737 hierarchy_id,
738 parent_entity_id,
739 child_entity_id,
740 ownership_percent,
741 start_date,
742 treatment_id,
743 curr_treatment_id,
744 object_version_number,
745 creation_date,
746 created_by,
747 last_update_date,
748 last_updated_by,
749 last_update_login,
750 end_date,
751 delta_owned,
752 dominant_parent_flag,
753 actual_ownership_flag)
754 VALUES
755 (gcs_cons_relationships_s.nextval,
756 p_hierarchy_id,
757 p_parent_entity_id,
758 p_original_entity_id,
759 0,
760 p_end_date + 1,
761 null,
762 null,
763 1,
764 sysdate,
765 fnd_user_id,
766 sysdate,
767 fnd_user_id,
768 fnd_login_id,
769 delta_row.end_date,
770 delta_row.delta_owned,
771 'N',
772 'N');
773 UPDATE gcs_cons_relationships r
774 SET end_date = p_end_date
775 WHERE r.cons_relationship_id = delta_row.cons_relationship_id;
776 END IF;
777 -- If this relationship's start date is not the day after the prior
778 -- relationship's end date, then create a "filler" relationship
779 IF delta_row.start_date >
780 nvl(last_end_date + 1, delta_row.start_date) THEN
781 INSERT INTO gcs_cons_relationships
782 (cons_relationship_id,
783 hierarchy_id,
784 parent_entity_id,
785 child_entity_id,
786 ownership_percent,
787 start_date,
788 treatment_id,
789 curr_treatment_id,
790 object_version_number,
791 creation_date,
792 created_by,
793 last_update_date,
794 last_updated_by,
795 last_update_login,
796 end_date,
797 delta_owned,
798 dominant_parent_flag,
799 actual_ownership_flag)
800 VALUES
801 (gcs_cons_relationships_s.nextval,
802 p_hierarchy_id,
803 p_parent_entity_id,
804 p_original_entity_id,
805 0,
806 last_end_date + 1,
807 null,
808 null,
809 1,
810 sysdate,
811 fnd_user_id,
812 sysdate,
813 fnd_user_id,
814 fnd_login_id,
815 delta_row.start_date - 1,
816 p_effective_ownership,
817 'N',
818 'N');
819 END IF;
820 -- Now update the relationship with the new effective ownership
821 UPDATE gcs_cons_relationships r
822 SET r.delta_owned = r.delta_owned + p_effective_ownership
823 WHERE r.cons_relationship_id = delta_row.cons_relationship_id;
824 -- Finally, update last_end_date to the end date of this relationship
825 last_end_date := delta_row.end_date;
826 END LOOP;
827 -- Create the trailing "filler" relationship if necessary
828 IF last_end_date IS NOT NULL AND
829 nvl(p_end_date, last_end_date + 1) > last_end_date THEN
830 INSERT INTO gcs_cons_relationships
831 (cons_relationship_id,
832 hierarchy_id,
833 parent_entity_id,
834 child_entity_id,
835 ownership_percent,
836 start_date,
837 treatment_id,
838 curr_treatment_id,
839 object_version_number,
840 creation_date,
841 created_by,
842 last_update_date,
843 last_updated_by,
844 last_update_login,
845 end_date,
846 delta_owned,
847 dominant_parent_flag,
848 actual_ownership_flag)
849 VALUES
850 (gcs_cons_relationships_s.nextval,
851 p_hierarchy_id,
852 p_parent_entity_id,
853 p_original_entity_id,
854 0,
855 last_end_date + 1,
856 null,
857 null,
858 1,
859 sysdate,
860 fnd_user_id,
861 sysdate,
862 fnd_user_id,
863 fnd_login_id,
864 p_end_date,
865 p_effective_ownership,
866 'N',
867 'N');
868 END IF;
869 ELSE
870 -- Ensure that an infinite loop does not occur
871 p_traversed_entities(p_current_entity_id) := p_current_entity_id;
872 -- Get the list of parents for the current entity, and go up looking for
873 -- the parent entity
874 FOR parent_entity IN get_curr_entity_parent_c LOOP
875 calc_delta_to_single_parent(p_hierarchy_id => p_hierarchy_id,
876 p_original_entity_id => p_original_entity_id,
877 p_parent_entity_id => p_parent_entity_id,
878 p_current_entity_id => parent_entity.parent_entity_id,
879 p_effective_ownership => parent_entity.ownership_percent *
880 p_effective_ownership / 100,
881 p_start_date => parent_entity.start_date,
882 p_end_date => parent_entity.end_date,
883 p_traversed_entities => p_traversed_entities,
884 p_calc_parent_entities => p_calc_parent_entities);
885 END LOOP;
886 -- Now, clear out the entry so that we can traverse this entity again
887 p_traversed_entities(p_current_entity_id) := NULL;
888 END IF;
889 module_log_write(v_module, g_module_success);
890 EXCEPTION
891 WHEN OTHERS THEN
892 write_to_log(v_module, FND_LOG.LEVEL_UNEXPECTED, SQLERRM);
893 module_log_write(v_module, g_module_failure);
894 RAISE;
895 END Calc_Delta_To_Single_Parent;
896 --
897 -- Procedure
898 -- Calc_Delta_To_All_Parents
899 -- Purpose
900 -- Calculate the delta ownership to all parents from the original entity.
901 -- The parent entity given is the one for which we are currently performing
902 -- the calculation. We will recursively go up and perform the calculations
903 -- as necessary.
904 -- Arguments
905 -- p_hierarchy_id Hierarchy for which the logic must be performed
906 -- p_original_entity_id Original child entity for which the logic must
907 -- be performed
908 -- p_parent_entity_id The parent for which the delta ownership must
909 -- be found
910 -- p_start_date Date range for performing the logic
911 -- p_end_date Date range for performing the logic
912 -- p_calc_parent_entities List of entities we have already gone through
913 -- to get to this parent
914 -- Example
915 -- GCS_HIERARCHIES_PKG.Calc_Delta_To_All_Parents(...);
916 -- Notes
917 --
918 PROCEDURE Calc_Delta_To_All_Parents(p_hierarchy_id NUMBER,
919 p_original_entity_id NUMBER,
920 p_parent_entity_id NUMBER,
921 p_start_date DATE,
922 p_end_date DATE,
923 p_calc_parent_entities IN OUT NOCOPY EntitiesTableType) IS
924 -- Used to get the unconsolidated parents for the original child. The
925 -- decodes exist to pick the most restrictive date range possible.
926 CURSOR get_uncons_parent_c IS
927 SELECT r.parent_entity_id,
928 r.ownership_percent,
929 decode(sign(r.start_date - p_start_date),
930 1,
931 r.start_date,
932 p_start_date) start_date,
933 decode(r.end_date,
934 null,
935 p_end_date,
936 decode(p_end_date,
937 null,
938 r.end_date,
939 decode(sign(r.end_date - p_end_date),
940 1,
941 p_end_date,
942 r.end_date))) end_date
943 FROM gcs_cons_relationships r, gcs_treatments_b tb
944 WHERE r.hierarchy_id = p_hierarchy_id
945 AND r.child_entity_id = p_original_entity_id
946 AND r.actual_ownership_flag = 'Y'
947 AND r.start_date <= nvl(r.end_date, r.start_date)
948 AND p_start_date <= nvl(r.end_date, p_start_date)
949 AND nvl(p_end_date, r.start_date) >= r.start_date
950 AND tb.treatment_id = r.treatment_id
951 AND tb.consolidation_type_code = 'NONE';
952 -- A list of entities that I have visited on my way to get to the parent.
953 -- This is used to prevent infinite looping
954 v_traversed_entities EntitiesTableType;
955 -- Used to get the full consolidated parent for this child. The decodes
956 -- exist to pick the most restrictive date range possible.
957 CURSOR get_cons_parent_c IS
958 SELECT r.parent_entity_id,
959 decode(sign(r.start_date - p_start_date),
960 1,
961 r.start_date,
962 p_start_date) start_date,
963 decode(r.end_date,
964 null,
965 p_end_date,
966 decode(p_end_date,
967 null,
968 r.end_date,
969 decode(sign(r.end_date - p_end_date),
970 1,
971 p_end_date,
972 r.end_date))) end_date
973 FROM gcs_cons_relationships r, gcs_treatments_b tb
974 WHERE r.hierarchy_id = p_hierarchy_id
975 AND r.child_entity_id = p_parent_entity_id
976 AND r.actual_ownership_flag = 'Y'
977 AND r.start_date <= nvl(r.end_date, r.start_date)
978 AND p_start_date <= nvl(r.end_date, p_start_date)
979 AND nvl(p_end_date, r.start_date) >= r.start_date
980 AND tb.treatment_id = r.treatment_id
981 AND tb.consolidation_type_code = 'FULL';
982 v_module VARCHAR2(30);
983 BEGIN
984 v_module := 'Calc_Delta_To_All_Parents';
985 module_log_write(v_module, g_module_enter);
986 -- If we have already calculated deltas for this entity, return
987 IF entity_exists(p_calc_parent_entities, p_parent_entity_id) =
988 p_parent_entity_id THEN
989 module_log_write(v_module, g_module_success);
990 RETURN;
991 END IF;
992 -- Add the first child entity to the list of already-traversed entities
993 -- to prevent looping
994 v_traversed_entities(p_original_entity_id) := p_original_entity_id;
995 -- Get the immediate unconsolidated parents of the original entity, and
996 -- go up until you no longer have parents or you reach the parent entity,
997 -- and then create the appropriate delta ownership rows
998 FOR uncons_parent IN get_uncons_parent_c LOOP
999 Calc_Delta_To_Single_Parent(p_hierarchy_id => p_hierarchy_id,
1000 p_original_entity_id => p_original_entity_id,
1001 p_parent_entity_id => p_parent_entity_id,
1002 p_current_entity_id => uncons_parent.parent_entity_id,
1003 p_effective_ownership => uncons_parent.ownership_percent,
1004 p_start_date => uncons_parent.start_date,
1005 p_end_date => uncons_parent.end_date,
1006 p_traversed_entities => v_traversed_entities,
1007 p_calc_parent_entities => p_calc_parent_entities);
1008 END LOOP;
1009 -- Add this entity to the list of entities that have already had deltas
1010 -- calculated for them
1011 p_calc_parent_entities(p_parent_entity_id) := p_parent_entity_id;
1012 -- Get the immediate consolidated parent of this entity if there is one,
1013 -- and recursively go up the hierarchy, creating delta ownership rows
1014 FOR cons_parent IN get_cons_parent_c LOOP
1015 -- Calculate the delta ownership to the parent entity specified here,
1016 -- along with all its ancestors. Work within the date range given, and
1017 -- do not go up branches that go to parents that are in the list of
1018 -- already-traversed entities to prevent infinite looping.
1019 Calc_Delta_To_All_Parents(p_hierarchy_id => p_hierarchy_id,
1020 p_original_entity_id => p_original_entity_id,
1021 p_parent_entity_id => cons_parent.parent_entity_id,
1022 p_start_date => cons_parent.start_date,
1023 p_end_date => cons_parent.end_date,
1024 p_calc_parent_entities => p_calc_parent_entities);
1025 END LOOP;
1026 -- Clear out this parent entity, since we could be calculating a delta
1027 -- ownership for this for a future date range
1028 p_calc_parent_entities(p_parent_entity_id) := NULL;
1029 module_log_write(v_module, g_module_success);
1030 EXCEPTION
1031 WHEN OTHERS THEN
1032 write_to_log(v_module, FND_LOG.LEVEL_UNEXPECTED, SQLERRM);
1033 module_log_write(v_module, g_module_failure);
1034 RAISE;
1035 END Calc_Delta_To_All_Parents;
1036 --
1037 -- Procedure
1038 -- Calculate_Delta_Internal
1039 -- Purpose
1040 -- This does the actual work of calculating the delta amounts. It has the
1041 -- added parameter of a list of entities for which we have already
1042 -- performed the delta calculation logic.
1043 -- Arguments
1044 -- p_hierarchy_id Hierarchy for which the logic must be performed
1045 -- p_child_entity_id Entity for which the logic must be performed
1046 -- p_effective_date Date range for performing the logic
1047 -- p_calc_child_entities Entities for which the calculation has already
1048 -- been performed
1049 -- Example
1050 -- GCS_HIERARCHIES_PKG.Calculate_Delta_Internal(...);
1051 -- Notes
1052 --
1053 PROCEDURE Calculate_Delta_Internal(p_hierarchy_id NUMBER,
1054 p_child_entity_id NUMBER,
1055 p_effective_date DATE,
1056 p_calc_child_entities IN OUT NOCOPY EntitiesTableType) IS
1057 -- Used to get the full consolidated parent for this child. The decode
1058 -- exists to pick the most restrictive date range possible.
1059 CURSOR get_cons_parent_c IS
1060 SELECT r.parent_entity_id,
1061 decode(sign(r.start_date - p_effective_date),
1062 1,
1063 r.start_date,
1064 p_effective_date) start_date,
1065 r.end_date
1066 FROM gcs_cons_relationships r, gcs_treatments_b tb
1067 WHERE r.hierarchy_id = p_hierarchy_id
1068 AND r.child_entity_id = p_child_entity_id
1069 AND r.actual_ownership_flag = 'Y'
1070 AND r.start_date <= nvl(r.end_date, r.start_date)
1071 AND p_effective_date <= nvl(r.end_date, p_effective_date)
1072 AND tb.treatment_id = r.treatment_id
1073 AND tb.consolidation_type_code = 'FULL';
1074 v_calc_parent_entities EntitiesTableType;
1075 -- Get a list of this entity's children, if any exist
1076 CURSOR get_child_entities_c IS
1077 SELECT r.child_entity_id
1078 FROM gcs_cons_relationships r
1079 WHERE r.hierarchy_id = p_hierarchy_id
1080 AND r.parent_entity_id = p_child_entity_id
1081 AND r.actual_ownership_flag = 'Y'
1082 AND r.start_date <= nvl(r.end_date, r.start_date)
1083 AND p_effective_date <= nvl(r.end_date, p_effective_date)
1084 AND r.treatment_id IS NOT NULL;
1085 v_module VARCHAR2(30);
1086 BEGIN
1087 v_module := 'Calculate_Delta_Internal';
1088 module_log_write(v_module, g_module_enter);
1089 -- If we've already calculated the deltas for this entity, return
1090 IF entity_exists(p_calc_child_entities, p_child_entity_id) =
1091 p_child_entity_id THEN
1092 module_log_write(v_module, g_module_success);
1093 RETURN;
1094 END IF;
1095 -- First, we clean things up by end-dating all delta relationships
1096 -- that end after this day. We'll be recalculating these amounts later.
1097 UPDATE gcs_cons_relationships
1098 SET end_date = p_effective_date - 1
1099 WHERE hierarchy_id = p_hierarchy_id
1100 AND child_entity_id = p_child_entity_id
1101 AND actual_ownership_flag = 'N'
1102 AND nvl(end_date, p_effective_date) >= p_effective_date;
1103 -- Add this entity to the list of entities that have already had deltas
1104 -- calculated for them
1105 v_calc_parent_entities(p_child_entity_id) := p_child_entity_id;
1106 -- Get the immediate consolidated parent of this child if there is one,
1107 -- and recursively go up the hierarchy, creating delta ownership rows
1108 FOR cons_parent IN get_cons_parent_c LOOP
1109 -- Calculate the delta ownership to the parent entity specified here,
1110 -- along with all its ancestors. Work within the date range given, and
1111 -- do not go up branches that go to parents that are in the list of
1112 -- already-traversed entities to prevent infinite looping.
1113 Calc_Delta_To_All_Parents(p_hierarchy_id => p_hierarchy_id,
1114 p_original_entity_id => p_child_entity_id,
1115 p_parent_entity_id => cons_parent.parent_entity_id,
1116 p_start_date => cons_parent.start_date,
1117 p_end_date => cons_parent.end_date,
1118 p_calc_parent_entities => v_calc_parent_entities);
1119 END LOOP;
1120 -- List this entity so that we can prevent infinite loops
1121 p_calc_child_entities(p_child_entity_id) := p_child_entity_id;
1122 -- Now go through all this entity's children and perform the same logic
1123 FOR child_entity_row IN get_child_entities_c LOOP
1124 Calculate_Delta_Internal(p_hierarchy_id => p_hierarchy_id,
1125 p_child_entity_id => child_entity_row.child_entity_id,
1126 p_effective_date => p_effective_date,
1127 p_calc_child_entities => p_calc_child_entities);
1128 END LOOP;
1129 module_log_write(v_module, g_module_success);
1130 EXCEPTION
1131 WHEN OTHERS THEN
1132 write_to_log(v_module, FND_LOG.LEVEL_UNEXPECTED, SQLERRM);
1133 module_log_write(v_module, g_module_failure);
1134 RAISE;
1135 END Calculate_Delta_Internal;
1136 --
1137 -- Public Procedures and Functions for Multiple Parents
1138 --
1139 --
1140 -- Procedure
1141 -- Calculate_Delta
1142 -- Purpose
1143 -- Calculates the delta ownership amounts for an entity and its children,
1144 -- and updates or creates the necessary gcs_cons_relationships row.
1145 -- Arguments
1146 -- p_hierarchy_id Hierarchy for which the logic must be performed
1147 -- p_child_entity_id Entity for which the logic must be performed
1148 -- p_effective_date Start date for performing the logic
1149 -- Example
1150 -- GCS_HIERARCHIES_PKG.Calculate_Delta(...);
1151 -- Notes
1152 --
1153 PROCEDURE Calculate_Delta(p_hierarchy_id NUMBER,
1154 p_child_entity_id NUMBER,
1155 p_effective_date DATE) IS
1156 -- For holding the entities that have already had their deltas calculated
1157 v_calc_child_entities EntitiesTableType;
1158 v_module VARCHAR2(30);
1159 BEGIN
1160 v_module := 'Calculate_Delta';
1161 module_log_write(v_module, g_module_enter);
1162 -- In case of an error, we will roll back to this point in time.
1163 SAVEPOINT gcs_calc_delta_single_entity;
1164 Calculate_Delta_Internal(p_hierarchy_id => p_hierarchy_id,
1165 p_child_entity_id => p_child_entity_id,
1166 p_effective_date => p_effective_date,
1167 p_calc_child_entities => v_calc_child_entities);
1168 module_log_write(v_module, g_module_success);
1169 EXCEPTION
1170 WHEN OTHERS THEN
1171 ROLLBACK TO gcs_calc_delta_single_entity;
1172 write_to_log(v_module, FND_LOG.LEVEL_UNEXPECTED, SQLERRM);
1173 module_log_write(v_module, g_module_failure);
1174 RAISE;
1175 END Calculate_Delta;
1176 --
1177 -- Procedure
1178 -- Reciprocal_Exists
1179 -- Purpose
1180 -- See whether or not a cycle exists in the hierarchy. Search recursively
1181 -- for the child entity id, starting from the parent entity id, within the
1182 -- dates specified.
1183 -- Arguments
1184 -- p_hierarchy_id Hierarchy for which the logic must be performed
1185 -- p_child_id Entity we are searching for
1186 -- p_parent_id Entity to start the search from
1187 -- p_start_date Effective date range
1188 -- p_end_date Effective date range
1189 -- Example
1190 -- GCS_HIERARCHIES_PKG.Reciprocal_Exists(...);
1191 -- Notes
1192 --
1193 FUNCTION Reciprocal_Exists(p_hierarchy_id NUMBER,
1194 p_child_id NUMBER,
1195 p_parent_id NUMBER,
1196 p_start_date DATE,
1197 p_end_date DATE) RETURN VARCHAR2 IS
1198 CURSOR parents_c IS
1199 SELECT r.parent_entity_id,
1200 decode(sign(r.start_date - p_start_date),
1201 1,
1202 r.start_date,
1203 p_start_date) start_date,
1204 decode(r.end_date,
1205 null,
1206 p_end_date,
1207 decode(p_end_date,
1208 null,
1209 r.end_date,
1210 decode(sign(r.end_date - p_end_date),
1211 1,
1212 p_end_date,
1213 r.end_date))) end_date
1214 FROM gcs_cons_relationships r
1215 WHERE r.hierarchy_id = p_hierarchy_id
1216 AND r.child_entity_id = p_parent_id
1217 AND r.actual_ownership_flag = 'Y'
1218 AND r.start_date <= nvl(r.end_date, r.start_date)
1219 AND p_start_date <= nvl(r.end_date, p_start_date)
1220 AND nvl(p_end_date, r.start_date) >= r.start_date;
1221 BEGIN
1222 IF p_parent_id = p_child_id THEN
1223 return 'Y';
1224 END IF;
1225 FOR parent_info in parents_c LOOP
1226 IF reciprocal_exists(p_hierarchy_id,
1227 p_child_id,
1228 parent_info.parent_entity_id,
1229 parent_info.start_date,
1230 parent_info.end_date) = 'Y' THEN
1231 return 'Y';
1232 END IF;
1233 END LOOP;
1234 return 'N';
1235 END Reciprocal_Exists;
1236 --
1237 -- Procedure
1238 -- Get_Ccy_Treat
1239 -- Purpose
1240 -- Get the currency treatment for a given relationship
1241 -- Arguments
1242 -- p_hierarchy_id Hierarchy for which the logic must be performed
1243 -- p_parent_id Parent entity
1244 -- p_parent_id Child entity
1245 -- p_date Date
1246 -- Example
1247 -- GCS_HIERARCHIES_PKG.Calc_Delta_To_All_Parents(...);
1248 -- Notes
1249 --
1250 FUNCTION get_Ccy_Treat(p_hierarchy_id NUMBER,
1251 p_parent_id NUMBER,
1252 p_child_id NUMBER,
1253 p_date DATE) RETURN NUMBER IS
1254 to_ccy VARCHAR2(100);
1255 from_ccy VARCHAR2(100);
1256 return_val NUMBER;
1257 CURSOR locked_ccy_treat_c IS
1258 SELECT cr.curr_treatment_id
1259 FROM gcs_cons_eng_runs r,
1260 gcs_cons_eng_run_dtls rd,
1261 fem_cal_periods_attr cpa,
1262 gcs_cons_relationships cr,
1263 fem_dim_attributes_b fdab,
1264 fem_dim_attr_versions_b fdavb
1265 WHERE r.hierarchy_id = p_hierarchy_id
1266 AND r.run_entity_id = p_parent_id
1267 AND rd.run_name = r.run_name
1268 AND rd.consolidation_entity_id = p_parent_id
1269 AND rd.child_entity_id = p_child_id
1270 AND fdab.attribute_varchar_label = 'CAL_PERIOD_END_DATE'
1271 AND fdavb.attribute_id = fdab.attribute_id
1272 AND fdavb.default_version_flag = 'Y'
1273 AND cpa.cal_period_id = r.cal_period_id
1274 AND cpa.attribute_id = fdab.attribute_id
1275 AND cpa.version_id = fdavb.version_id
1276 AND cpa.date_assign_value < p_date
1277 AND cr.cons_relationship_id = rd.cons_relationship_id
1278 order by cpa.date_assign_value, rd.last_update_date desc;
1279 CURSOR def_ccy_treat_c IS
1280 SELECT ctb.curr_treatment_id
1281 FROM gcs_curr_treatments_b ctb
1282 WHERE ctb.enabled_flag = 'Y'
1283 ORDER BY decode(ctb.default_flag, 'Y', 0, 1), ctb.curr_treatment_id;
1284 BEGIN
1285 SELECT currency_code
1286 INTO to_ccy
1287 FROM gcs_entity_cons_attrs
1288 WHERE hierarchy_id = p_hierarchy_id
1289 AND entity_id = p_parent_id;
1290 SELECT currency_code
1291 INTO from_ccy
1292 FROM gcs_entity_cons_attrs
1293 WHERE hierarchy_id = p_hierarchy_id
1294 AND entity_id = p_child_id;
1295 IF to_ccy = from_ccy THEN
1296 return null;
1297 END IF;
1298 OPEN locked_ccy_treat_c;
1299 FETCH locked_ccy_treat_c
1300 INTO return_val;
1301 IF locked_ccy_treat_c%FOUND THEN
1302 CLOSE locked_ccy_treat_c;
1303 return return_val;
1304 END IF;
1305 CLOSE locked_ccy_treat_c;
1306 OPEN def_ccy_treat_c;
1307 FETCH def_ccy_treat_c
1308 INTO return_val;
1309 IF def_ccy_treat_c%FOUND THEN
1310 CLOSE def_ccy_treat_c;
1311 return return_val;
1312 END IF;
1313 CLOSE def_ccy_treat_c;
1314 return null;
1315 END Get_Ccy_Treat;
1316 --
1317 -- Procedure
1318 -- Set_Dominance
1319 -- Purpose
1320 -- Set the dominant parent flag for a relationship after an add or update
1321 -- entity in the update flow.
1322 -- Arguments
1323 -- p_rel_id New relationship identifier
1324 -- Example
1325 -- GCS_HIERARCHIES_PKG.Set_Dominance(123, 'ADD');
1326 -- Notes
1327 --
1328 PROCEDURE Set_Dominance(p_rel_id NUMBER) IS
1329 l_hierarchy_id NUMBER;
1330 l_parent_id NUMBER;
1331 l_child_id NUMBER;
1332 l_ownership NUMBER;
1333 l_start_date DATE;
1334 l_treat_id NUMBER;
1335 l_ccy_treat_id NUMBER;
1336 l_dominant_flag VARCHAR2(1);
1337 l_treat_type VARCHAR2(30);
1338 l_from_ccy VARCHAR2(30);
1339 l_to_ccy VARCHAR2(30);
1340 CURSOR other_dominant_c IS
1341 SELECT r.*
1342 FROM gcs_cons_relationships r
1343 WHERE r.hierarchy_id = l_hierarchy_id
1344 AND r.child_entity_id = l_child_id
1345 AND r.parent_entity_id <> l_parent_id
1346 AND r.start_date <= nvl(r.end_date, r.start_date)
1347 AND l_start_date <= nvl(r.end_date, l_start_date)
1348 AND r.actual_ownership_flag = 'Y'
1349 AND r.dominant_parent_flag = 'Y';
1350 CURSOR future_full_c IS
1351 SELECT r.*
1352 FROM gcs_cons_relationships r, gcs_treatments_b tb
1353 WHERE r.hierarchy_id = l_hierarchy_id
1354 AND r.child_entity_id = l_child_id
1355 AND r.parent_entity_id <> l_parent_id
1356 AND r.start_date <= nvl(r.end_date, r.start_date)
1357 AND l_start_date <= r.start_date
1358 AND r.actual_ownership_flag = 'Y'
1359 AND tb.treatment_id = r.treatment_id
1360 AND tb.consolidation_type_code = 'FULL'
1361 ORDER BY r.start_date;
1362 CURSOR non_full_dominant_straddle_c IS
1363 SELECT r.cons_relationship_id
1364 FROM gcs_cons_relationships r, gcs_treatments_b tb
1365 WHERE r.hierarchy_id = l_hierarchy_id
1366 AND r.child_entity_id = l_child_id
1367 AND r.parent_entity_id <> l_parent_id
1368 AND r.start_date <= nvl(r.end_date, r.start_date)
1369 AND r.start_date < l_start_date
1370 AND l_start_date <= nvl(r.end_date, l_start_date)
1371 AND r.actual_ownership_flag = 'Y'
1372 AND r.dominant_parent_flag = 'Y'
1373 AND tb.treatment_id = r.treatment_id
1374 AND tb.consolidation_type_code = 'NONE';
1375 l_temp_rel_id NUMBER;
1376 CURSOR future_rel_c IS
1377 SELECT r.*
1378 FROM gcs_cons_relationships r
1379 WHERE r.hierarchy_id = l_hierarchy_id
1380 AND r.child_entity_id = l_child_id
1381 AND r.parent_entity_id <> l_parent_id
1382 AND r.start_date <= nvl(r.end_date, r.start_date)
1383 AND l_start_date <= nvl(r.end_date, l_start_date)
1384 AND r.actual_ownership_flag = 'Y'
1385 ORDER BY r.start_date;
1386 l_last_end_date DATE;
1387 CURSOR future_dominant_rel_c IS
1388 SELECT r.*
1389 FROM gcs_cons_relationships r
1390 WHERE r.hierarchy_id = l_hierarchy_id
1391 AND r.child_entity_id = l_child_id
1392 AND r.start_date <= nvl(r.end_date, r.start_date)
1393 AND l_start_date <= nvl(r.end_date, l_start_date)
1394 AND r.actual_ownership_flag = 'Y'
1395 AND r.dominant_parent_flag = 'Y'
1396 ORDER BY r.start_date;
1397 l_temp_parent_id NUMBER;
1398 l_temp_ccy_treat_id NUMBER;
1399 BEGIN
1400 SELECT r.hierarchy_id,
1401 r.parent_entity_id,
1402 r.child_entity_id,
1403 r.ownership_percent,
1404 r.start_date,
1405 r.treatment_id,
1406 r.curr_treatment_id,
1407 r.dominant_parent_flag,
1408 tb.consolidation_type_code
1409 INTO l_hierarchy_id,
1410 l_parent_id,
1411 l_child_id,
1412 l_ownership,
1413 l_start_date,
1414 l_treat_id,
1415 l_ccy_treat_id,
1416 l_dominant_flag,
1417 l_treat_type
1418 FROM gcs_cons_relationships r, gcs_treatments_b tb
1419 WHERE r.cons_relationship_id = p_rel_id
1420 AND tb.treatment_id = r.treatment_id;
1421 IF l_treat_type = 'FULL' THEN
1422 UPDATE gcs_cons_relationships r
1423 SET dominant_parent_flag = 'Y'
1424 WHERE r.cons_relationship_id = p_rel_id;
1425 FOR other_row IN other_dominant_c LOOP
1426 -- Split a row if necessary
1427 IF other_row.start_date < l_start_date THEN
1428 INSERT INTO gcs_cons_relationships
1429 (CONS_RELATIONSHIP_ID,
1430 HIERARCHY_ID,
1431 PARENT_ENTITY_ID,
1432 CHILD_ENTITY_ID,
1433 OWNERSHIP_PERCENT,
1434 START_DATE,
1435 TREATMENT_ID,
1436 CURR_TREATMENT_ID,
1437 OBJECT_VERSION_NUMBER,
1438 CREATION_DATE,
1439 CREATED_BY,
1440 LAST_UPDATE_DATE,
1441 LAST_UPDATED_BY,
1442 LAST_UPDATE_LOGIN,
1443 END_DATE,
1444 DOMINANT_PARENT_FLAG,
1445 ACTUAL_OWNERSHIP_FLAG)
1446 VALUES
1447 (gcs_cons_relationships_s.nextval,
1448 other_row.hierarchy_id,
1449 other_row.parent_entity_id,
1450 other_row.child_entity_id,
1451 other_row.ownership_percent,
1452 l_start_date,
1453 other_row.treatment_id,
1454 null,
1455 1,
1456 sysdate,
1457 FND_GLOBAL.USER_ID,
1458 sysdate,
1459 FND_GLOBAL.USER_ID,
1460 FND_GLOBAL.LOGIN_ID,
1461 other_row.end_date,
1462 'N',
1463 other_row.actual_ownership_flag);
1464 UPDATE gcs_cons_relationships r
1465 SET end_date = l_start_date - 1,
1466 last_update_date = sysdate,
1467 last_updated_by = FND_GLOBAL.USER_ID,
1468 last_update_login = FND_GLOBAL.LOGIN_ID
1469 WHERE r.cons_relationship_id = other_row.cons_relationship_id;
1470 ELSE
1471 UPDATE gcs_cons_relationships r
1472 SET dominant_parent_flag = 'N',
1473 curr_treatment_id = null,
1474 last_update_date = sysdate,
1475 last_updated_by = FND_GLOBAL.USER_ID,
1476 last_update_login = FND_GLOBAL.LOGIN_ID
1477 WHERE r.cons_relationship_id = other_row.cons_relationship_id;
1478 END IF;
1479 END LOOP;
1480 ELSIF l_treat_type = 'NONE' THEN
1481 IF l_dominant_flag = 'Y' THEN
1482 -- Split so that for any future full relationships, the relationship's
1483 -- dominant parent flag = 'N' and currency treatment is null
1484 FOR future_full_row IN future_full_c LOOP
1485 UPDATE gcs_cons_relationships r
1486 SET end_date = future_full_row.start_date - 1
1487 WHERE r.hierarchy_id = l_hierarchy_id
1488 AND r.parent_entity_id = l_parent_id
1489 AND r.child_entity_id = l_child_id
1490 AND r.end_date IS NULL
1491 AND r.actual_ownership_flag = 'Y';
1492 INSERT INTO gcs_cons_relationships
1493 (CONS_RELATIONSHIP_ID,
1494 HIERARCHY_ID,
1495 PARENT_ENTITY_ID,
1496 CHILD_ENTITY_ID,
1497 OWNERSHIP_PERCENT,
1498 START_DATE,
1499 TREATMENT_ID,
1500 CURR_TREATMENT_ID,
1501 OBJECT_VERSION_NUMBER,
1502 CREATION_DATE,
1503 CREATED_BY,
1504 LAST_UPDATE_DATE,
1505 LAST_UPDATED_BY,
1506 LAST_UPDATE_LOGIN,
1507 END_DATE,
1508 DOMINANT_PARENT_FLAG,
1509 ACTUAL_OWNERSHIP_FLAG)
1510 VALUES
1511 (gcs_cons_relationships_s.nextval,
1512 l_hierarchy_id,
1513 l_parent_id,
1514 l_child_id,
1515 l_ownership,
1516 future_full_row.start_date,
1517 l_treat_id,
1518 null,
1519 1,
1520 sysdate,
1521 FND_GLOBAL.USER_ID,
1522 sysdate,
1523 FND_GLOBAL.USER_ID,
1524 FND_GLOBAL.LOGIN_ID,
1525 future_full_row.end_date,
1526 'N',
1527 'Y');
1528 IF future_full_row.end_date IS NOT NULL THEN
1529 INSERT INTO gcs_cons_relationships
1530 (CONS_RELATIONSHIP_ID,
1531 HIERARCHY_ID,
1532 PARENT_ENTITY_ID,
1533 CHILD_ENTITY_ID,
1534 OWNERSHIP_PERCENT,
1535 START_DATE,
1536 TREATMENT_ID,
1537 CURR_TREATMENT_ID,
1538 OBJECT_VERSION_NUMBER,
1539 CREATION_DATE,
1540 CREATED_BY,
1541 LAST_UPDATE_DATE,
1542 LAST_UPDATED_BY,
1543 LAST_UPDATE_LOGIN,
1544 END_DATE,
1545 DOMINANT_PARENT_FLAG,
1546 ACTUAL_OWNERSHIP_FLAG)
1547 VALUES
1548 (gcs_cons_relationships_s.nextval,
1549 l_hierarchy_id,
1550 l_parent_id,
1551 l_child_id,
1552 l_ownership,
1553 future_full_row.end_date + 1,
1554 l_treat_id,
1555 l_ccy_treat_id,
1556 1,
1557 sysdate,
1558 FND_GLOBAL.USER_ID,
1559 sysdate,
1560 FND_GLOBAL.USER_ID,
1561 FND_GLOBAL.LOGIN_ID,
1562 null,
1563 'Y',
1564 'Y');
1565 END IF;
1566 END LOOP;
1567 -- Split a dominant relationship if it straddles the start date
1568 OPEN non_full_dominant_straddle_c;
1569 FETCH non_full_dominant_straddle_c
1570 INTO l_temp_rel_id;
1571 IF non_full_dominant_straddle_c%FOUND THEN
1572 CLOSE non_full_dominant_straddle_c;
1573 INSERT INTO gcs_cons_relationships
1574 (CONS_RELATIONSHIP_ID,
1575 HIERARCHY_ID,
1576 PARENT_ENTITY_ID,
1577 CHILD_ENTITY_ID,
1578 OWNERSHIP_PERCENT,
1579 START_DATE,
1580 TREATMENT_ID,
1581 CURR_TREATMENT_ID,
1582 OBJECT_VERSION_NUMBER,
1583 CREATION_DATE,
1584 CREATED_BY,
1585 LAST_UPDATE_DATE,
1586 LAST_UPDATED_BY,
1587 LAST_UPDATE_LOGIN,
1588 END_DATE,
1589 DOMINANT_PARENT_FLAG,
1590 ACTUAL_OWNERSHIP_FLAG)
1591 SELECT gcs_cons_relationships_s.nextval,
1592 r.hierarchy_id,
1593 r.parent_entity_id,
1594 r.child_entity_id,
1595 r.ownership_percent,
1596 l_start_date,
1597 r.treatment_id,
1598 null,
1599 1,
1600 sysdate,
1601 FND_GLOBAL.USER_ID,
1602 sysdate,
1603 FND_GLOBAL.USER_ID,
1604 FND_GLOBAL.LOGIN_ID,
1605 r.end_date,
1606 'N',
1607 'Y'
1608 FROM gcs_cons_relationships r
1609 WHERE r.cons_relationship_id = l_temp_rel_id;
1610 UPDATE gcs_cons_relationships r
1611 SET end_date = l_start_date - 1
1612 WHERE r.cons_relationship_id = l_temp_rel_id;
1613 ELSE
1614 CLOSE non_full_dominant_straddle_c;
1615 END IF;
1616 -- Update all relationships that are on or after this date, with a
1617 -- different parent, that are not full relationships, to be not
1618 -- dominant parents
1619 UPDATE gcs_cons_relationships r
1620 SET dominant_parent_flag = 'N', curr_treatment_id = null
1621 WHERE r.hierarchy_id = l_hierarchy_id
1622 AND r.child_entity_id = l_child_id
1623 AND r.parent_entity_id <> l_parent_id
1624 AND r.start_date <= nvl(r.end_date, r.start_date)
1625 AND r.start_date >= l_start_date
1626 AND r.actual_ownership_flag = 'Y'
1627 AND r.dominant_parent_flag = 'Y'
1628 AND EXISTS
1629 (SELECT 1
1630 FROM gcs_treatments_b tb
1631 WHERE tb.treatment_id = r.treatment_id
1632 AND tb.consolidation_type_code = 'NONE');
1633 ELSE
1634 -- dominant flag = 'N'
1635 SELECT currency_code
1636 INTO l_from_ccy
1637 FROM gcs_entity_cons_attrs
1638 WHERE hierarchy_id = l_hierarchy_id
1639 AND entity_id = l_child_id;
1640 SELECT currency_code
1641 INTO l_to_ccy
1642 FROM gcs_entity_cons_attrs
1643 WHERE hierarchy_id = l_hierarchy_id
1644 AND entity_id = l_parent_id;
1645 -- Set the currency treatment that should be used when required
1646 IF l_from_ccy = l_to_ccy THEN
1647 l_ccy_treat_id := null;
1648 END IF;
1649 l_last_end_date := l_start_date - 1;
1650 FOR future_rel_row IN future_rel_c LOOP
1651 -- If there is a gap, create a relationship
1652 IF l_last_end_date IS NOT NULL AND
1653 l_last_end_date < future_rel_row.start_date - 1 THEN
1654 UPDATE gcs_cons_relationships r
1655 SET end_date = l_last_end_date
1656 WHERE r.hierarchy_id = l_hierarchy_id
1657 AND r.parent_entity_id = l_parent_id
1658 AND r.child_entity_id = l_child_id
1659 AND r.end_date IS NULL
1660 AND r.actual_ownership_flag = 'Y';
1661 INSERT INTO gcs_cons_relationships
1662 (CONS_RELATIONSHIP_ID,
1663 HIERARCHY_ID,
1664 PARENT_ENTITY_ID,
1665 CHILD_ENTITY_ID,
1666 OWNERSHIP_PERCENT,
1667 START_DATE,
1668 TREATMENT_ID,
1669 CURR_TREATMENT_ID,
1670 OBJECT_VERSION_NUMBER,
1671 CREATION_DATE,
1672 CREATED_BY,
1673 LAST_UPDATE_DATE,
1674 LAST_UPDATED_BY,
1675 LAST_UPDATE_LOGIN,
1676 END_DATE,
1677 DOMINANT_PARENT_FLAG,
1678 ACTUAL_OWNERSHIP_FLAG)
1679 VALUES
1680 (gcs_cons_relationships_s.nextval,
1681 l_hierarchy_id,
1682 l_parent_id,
1683 l_child_id,
1684 l_ownership,
1685 l_last_end_date + 1,
1686 l_treat_id,
1687 l_ccy_treat_id,
1688 1,
1689 sysdate,
1690 FND_GLOBAL.USER_ID,
1691 sysdate,
1692 FND_GLOBAL.USER_ID,
1693 FND_GLOBAL.LOGIN_ID,
1694 future_rel_row.start_date - 1,
1695 'Y',
1696 'Y');
1697 INSERT INTO gcs_cons_relationships
1698 (CONS_RELATIONSHIP_ID,
1699 HIERARCHY_ID,
1700 PARENT_ENTITY_ID,
1701 CHILD_ENTITY_ID,
1702 OWNERSHIP_PERCENT,
1703 START_DATE,
1704 TREATMENT_ID,
1705 CURR_TREATMENT_ID,
1706 OBJECT_VERSION_NUMBER,
1707 CREATION_DATE,
1708 CREATED_BY,
1709 LAST_UPDATE_DATE,
1710 LAST_UPDATED_BY,
1711 LAST_UPDATE_LOGIN,
1712 END_DATE,
1713 DOMINANT_PARENT_FLAG,
1714 ACTUAL_OWNERSHIP_FLAG)
1715 VALUES
1716 (gcs_cons_relationships_s.nextval,
1717 l_hierarchy_id,
1718 l_parent_id,
1719 l_child_id,
1720 l_ownership,
1721 future_rel_row.start_date,
1722 l_treat_id,
1723 null,
1724 1,
1725 sysdate,
1726 FND_GLOBAL.USER_ID,
1727 sysdate,
1728 FND_GLOBAL.USER_ID,
1729 FND_GLOBAL.LOGIN_ID,
1730 null,
1731 'N',
1732 'Y');
1733 END IF;
1734 -- Update the future relationship end date appropriately
1735 IF l_last_end_date IS NOT NULL AND
1736 (future_rel_row.end_date IS NULL OR
1737 l_last_end_date < future_rel_row.end_date) THEN
1738 l_last_end_date := future_rel_row.end_date;
1739 END IF;
1740 END LOOP;
1741 -- If the last relationship does not go to null, insert a row
1742 IF l_last_end_date IS NOT NULL THEN
1743 UPDATE gcs_cons_relationships r
1744 SET end_date = l_last_end_date
1745 WHERE r.hierarchy_id = l_hierarchy_id
1746 AND r.parent_entity_id = l_parent_id
1747 AND r.child_entity_id = l_child_id
1748 AND r.end_date IS NULL
1749 and r.actual_ownership_flag = 'Y';
1750 INSERT INTO gcs_cons_relationships
1751 (CONS_RELATIONSHIP_ID,
1752 HIERARCHY_ID,
1753 PARENT_ENTITY_ID,
1754 CHILD_ENTITY_ID,
1755 OWNERSHIP_PERCENT,
1756 START_DATE,
1757 TREATMENT_ID,
1758 CURR_TREATMENT_ID,
1759 OBJECT_VERSION_NUMBER,
1760 CREATION_DATE,
1761 CREATED_BY,
1762 LAST_UPDATE_DATE,
1763 LAST_UPDATED_BY,
1764 LAST_UPDATE_LOGIN,
1765 END_DATE,
1766 DOMINANT_PARENT_FLAG,
1767 ACTUAL_OWNERSHIP_FLAG)
1768 VALUES
1769 (gcs_cons_relationships_s.nextval,
1770 l_hierarchy_id,
1771 l_parent_id,
1772 l_child_id,
1773 l_ownership,
1774 l_last_end_date + 1,
1775 l_treat_id,
1776 l_ccy_treat_id,
1777 1,
1778 sysdate,
1779 FND_GLOBAL.USER_ID,
1780 sysdate,
1781 FND_GLOBAL.USER_ID,
1782 FND_GLOBAL.LOGIN_ID,
1783 null,
1784 'Y',
1785 'Y');
1786 END IF;
1787 UPDATE gcs_cons_relationships r
1788 SET curr_treatment_id = NULL
1789 WHERE r.hierarchy_id = l_hierarchy_id
1790 AND r.parent_entity_id = l_parent_id
1791 AND r.child_entity_id = l_child_id
1792 AND r.start_date <= nvl(r.end_date, r.start_date)
1793 AND r.start_date >= l_start_date
1794 AND r.dominant_parent_flag = 'N'
1795 AND r.actual_ownership_flag = 'Y'
1796 AND r.curr_treatment_id IS NOT NULL;
1797 l_last_end_date := l_start_date - 1;
1798 -- if there are any dominance gaps, fill them here
1799 FOR future_dominant_rel_row IN future_dominant_rel_c LOOP
1800 IF future_dominant_rel_row.start_date > l_last_end_date + 1 THEN
1801 begin
1802 SELECT r.cons_relationship_id, r.parent_entity_id
1803 INTO l_temp_rel_id, l_temp_parent_id
1804 FROM gcs_cons_relationships r
1805 WHERE r.hierarchy_id = l_hierarchy_id
1806 AND r.child_entity_id = l_child_id
1807 AND r.start_date <= nvl(r.end_date, r.start_date)
1808 AND r.start_date = l_last_end_date + 1
1809 AND r.end_date = future_dominant_rel_row.start_date - 1
1810 AND r.actual_ownership_flag = 'Y'
1811 AND r.dominant_parent_flag = 'N'
1812 AND rownum = 1;
1813 l_temp_ccy_treat_id := get_ccy_treat(l_hierarchy_id,
1814 l_temp_parent_id,
1815 l_child_id,
1816 l_last_end_date + 1);
1817 UPDATE gcs_cons_relationships r
1818 SET dominant_parent_flag = 'Y',
1819 curr_treatment_id = l_temp_ccy_treat_id
1820 WHERE r.cons_relationship_id = l_temp_rel_id;
1821 exception
1822 when others then
1823 null;
1824 end;
1825 END IF;
1826 IF l_last_end_date IS NOT NULL AND
1827 (future_dominant_rel_row.end_date IS NULL OR
1828 l_last_end_date < future_dominant_rel_row.end_date) THEN
1829 l_last_end_date := future_dominant_rel_row.end_date;
1830 END IF;
1831 END LOOP;
1832 IF l_last_end_date IS NOT NULL THEN
1833 begin
1834 SELECT r.cons_relationship_id, r.parent_entity_id
1835 INTO l_temp_rel_id, l_temp_parent_id
1836 FROM gcs_cons_relationships r
1837 WHERE r.hierarchy_id = l_hierarchy_id
1838 AND r.child_entity_id = l_child_id
1839 AND r.start_date <= nvl(r.end_date, r.start_date)
1840 AND r.start_date = l_last_end_date + 1
1841 AND r.end_date IS NULL
1842 AND r.actual_ownership_flag = 'Y'
1843 AND r.dominant_parent_flag = 'N'
1844 AND rownum = 1;
1845 l_temp_ccy_treat_id := get_ccy_treat(l_hierarchy_id,
1846 l_temp_parent_id,
1847 l_child_id,
1848 l_last_end_date + 1);
1849 UPDATE gcs_cons_relationships r
1850 SET dominant_parent_flag = 'Y',
1851 curr_treatment_id = l_temp_ccy_treat_id
1852 WHERE r.cons_relationship_id = l_temp_rel_id;
1853 exception
1854 when others then
1855 null;
1856 end;
1857 END IF;
1858 END IF;
1859 END IF;
1860 END Set_Dominance;
1861 PROCEDURE Handle_Remove_Internal(p_hier_id NUMBER,
1862 p_parent_id NUMBER,
1863 p_child_id NUMBER,
1864 p_start_date DATE,
1865 p_end_date DATE,
1866 p_removal_date DATE,
1867 p_ownership NUMBER,
1868 p_treat_id NUMBER,
1869 p_dom_flag VARCHAR2) IS
1870 CURSOR all_dominant_rel_c IS
1871 SELECT r.*
1872 FROM gcs_cons_relationships r
1873 WHERE r.hierarchy_id = p_hier_id
1874 AND r.child_entity_id = p_child_id
1875 AND r.start_date <= nvl(r.end_date, r.start_date)
1876 AND p_start_date <= nvl(r.end_date, p_start_date)
1877 AND r.start_date <= nvl(p_end_date, r.start_date)
1878 AND r.actual_ownership_flag = 'Y'
1879 AND r.dominant_parent_flag = 'Y'
1880 ORDER BY r.start_date;
1881 l_last_end_date DATE;
1882 l_ccy_treat_id NUMBER;
1883 l_temp_end_date DATE;
1884 l_temp_date DATE;
1885 CURSOR all_child_rels_c IS
1886 SELECT r.*
1887 FROM gcs_cons_relationships r
1888 WHERE r.hierarchy_id = p_hier_id
1889 AND r.parent_entity_id = p_child_id
1890 AND r.start_date <= nvl(r.end_date, r.start_date)
1891 AND l_temp_date <= nvl(r.end_date, l_temp_date)
1892 AND r.actual_ownership_flag = 'Y';
1893 BEGIN
1894 IF p_removal_date > p_start_date THEN
1895 l_temp_date := p_removal_date;
1896 ELSE
1897 l_temp_date := p_start_date;
1898 END IF;
1899 l_last_end_date := l_temp_date - 1;
1900 IF p_dom_flag = 'N' THEN
1901 FOR dominant_rel_row IN all_dominant_rel_c LOOP
1902 IF dominant_rel_row.start_date > l_last_end_date + 1 THEN
1903 UPDATE gcs_cons_relationships r
1904 SET end_date = l_last_end_date
1905 WHERE r.hierarchy_id = p_hier_id
1906 AND r.parent_entity_id = p_parent_id
1907 AND r.child_entity_id = p_child_id
1908 AND r.start_date <= nvl(r.end_date, r.start_date)
1909 AND r.actual_ownership_flag = 'Y'
1910 AND r.dominant_parent_flag = 'N'
1911 AND ((r.end_date IS NULL AND p_end_date IS NULL) OR
1912 (r.end_date = p_end_date));
1913 l_ccy_treat_id := get_ccy_treat(p_hier_id,
1914 p_parent_id,
1915 p_child_id,
1916 l_last_end_date + 1);
1917 INSERT INTO gcs_cons_relationships
1918 (CONS_RELATIONSHIP_ID,
1919 HIERARCHY_ID,
1920 PARENT_ENTITY_ID,
1921 CHILD_ENTITY_ID,
1922 OWNERSHIP_PERCENT,
1923 START_DATE,
1924 TREATMENT_ID,
1925 CURR_TREATMENT_ID,
1926 OBJECT_VERSION_NUMBER,
1927 CREATION_DATE,
1928 CREATED_BY,
1929 LAST_UPDATE_DATE,
1930 LAST_UPDATED_BY,
1931 LAST_UPDATE_LOGIN,
1932 END_DATE,
1933 DOMINANT_PARENT_FLAG,
1934 ACTUAL_OWNERSHIP_FLAG)
1935 VALUES
1936 (gcs_cons_relationships_s.nextval,
1937 p_hier_id,
1938 p_parent_id,
1939 p_child_id,
1940 p_ownership,
1941 l_last_end_date + 1,
1942 p_treat_id,
1943 l_ccy_treat_id,
1944 1,
1945 sysdate,
1946 FND_GLOBAL.USER_ID,
1947 sysdate,
1948 FND_GLOBAL.USER_ID,
1949 FND_GLOBAL.LOGIN_ID,
1950 dominant_rel_row.start_date - 1,
1951 'Y',
1952 'Y');
1953 IF dominant_rel_row.end_date IS NULL OR
1954 dominant_rel_row.end_date > p_end_date THEN
1955 l_temp_end_date := p_end_date;
1956 ELSE
1957 l_temp_end_date := dominant_rel_row.end_date;
1958 END IF;
1959 INSERT INTO gcs_cons_relationships
1960 (CONS_RELATIONSHIP_ID,
1961 HIERARCHY_ID,
1962 PARENT_ENTITY_ID,
1963 CHILD_ENTITY_ID,
1964 OWNERSHIP_PERCENT,
1965 START_DATE,
1966 TREATMENT_ID,
1967 CURR_TREATMENT_ID,
1968 OBJECT_VERSION_NUMBER,
1969 CREATION_DATE,
1970 CREATED_BY,
1971 LAST_UPDATE_DATE,
1972 LAST_UPDATED_BY,
1973 LAST_UPDATE_LOGIN,
1974 END_DATE,
1975 DOMINANT_PARENT_FLAG,
1976 ACTUAL_OWNERSHIP_FLAG)
1977 VALUES
1978 (gcs_cons_relationships_s.nextval,
1979 p_hier_id,
1980 p_parent_id,
1981 p_child_id,
1982 p_ownership,
1983 dominant_rel_row.start_date,
1984 p_treat_id,
1985 null,
1986 1,
1987 sysdate,
1988 FND_GLOBAL.USER_ID,
1989 sysdate,
1990 FND_GLOBAL.USER_ID,
1991 FND_GLOBAL.LOGIN_ID,
1992 l_temp_end_date,
1993 'N',
1994 'Y');
1995 END IF;
1996 IF l_last_end_date IS NOT NULL AND
1997 (dominant_rel_row.end_date IS NULL OR
1998 l_last_end_date < dominant_rel_row.end_date) THEN
1999 l_last_end_date := dominant_rel_row.end_date;
2000 END IF;
2001 END LOOP;
2002 IF l_last_end_date IS NOT NULL AND
2003 (p_end_date IS NULL OR l_last_end_date < p_end_date) THEN
2004 UPDATE gcs_cons_relationships r
2005 SET end_date = l_last_end_date
2006 WHERE r.hierarchy_id = p_hier_id
2007 AND r.parent_entity_id = p_parent_id
2008 AND r.child_entity_id = p_child_id
2009 AND r.start_date <= nvl(r.end_date, r.start_date)
2010 AND r.actual_ownership_flag = 'Y'
2011 AND r.dominant_parent_flag = 'N'
2012 AND ((r.end_date IS NULL AND p_end_date IS NULL) OR
2013 (r.end_date = p_end_date));
2014 l_ccy_treat_id := get_ccy_treat(p_hier_id,
2015 p_parent_id,
2016 p_child_id,
2017 l_last_end_date + 1);
2018 INSERT INTO gcs_cons_relationships
2019 (CONS_RELATIONSHIP_ID,
2020 HIERARCHY_ID,
2021 PARENT_ENTITY_ID,
2022 CHILD_ENTITY_ID,
2023 OWNERSHIP_PERCENT,
2024 START_DATE,
2025 TREATMENT_ID,
2026 CURR_TREATMENT_ID,
2027 OBJECT_VERSION_NUMBER,
2028 CREATION_DATE,
2029 CREATED_BY,
2030 LAST_UPDATE_DATE,
2031 LAST_UPDATED_BY,
2032 LAST_UPDATE_LOGIN,
2033 END_DATE,
2034 DOMINANT_PARENT_FLAG,
2035 ACTUAL_OWNERSHIP_FLAG)
2036 VALUES
2037 (gcs_cons_relationships_s.nextval,
2038 p_hier_id,
2039 p_parent_id,
2040 p_child_id,
2041 p_ownership,
2042 l_last_end_date + 1,
2043 p_treat_id,
2044 l_ccy_treat_id,
2045 1,
2046 sysdate,
2047 FND_GLOBAL.USER_ID,
2048 sysdate,
2049 FND_GLOBAL.USER_ID,
2050 FND_GLOBAL.LOGIN_ID,
2051 p_end_date,
2052 'Y',
2053 'Y');
2054 END IF;
2055 END IF;
2056 FOR all_child_rel_row IN all_child_rels_c LOOP
2057 handle_remove_internal(p_hier_id,
2058 all_child_rel_row.parent_entity_id,
2059 all_child_rel_row.child_entity_id,
2060 all_child_rel_row.start_date,
2061 all_child_rel_row.end_date,
2062 l_temp_date,
2063 all_child_rel_row.ownership_percent,
2064 all_child_rel_row.treatment_id,
2065 all_child_rel_row.dominant_parent_flag);
2066 END LOOP;
2067 END Handle_Remove_Internal;
2068 --
2069 -- Procedure
2070 -- Handle_Remove
2071 -- Purpose
2072 -- Handle removal of an entity in the update flow.
2073 -- Arguments
2074 -- p_hier_id Hierarchy identifier
2075 -- p_removal_date Date of the removal
2076 -- Example
2077 -- GCS_HIERARCHIES_PKG.Set_Dominance(123, 'ADD');
2078 -- Notes
2079 --
2080 PROCEDURE Handle_Remove(p_hier_id NUMBER, p_removal_date DATE) IS
2081 CURSOR all_child_rels_c IS
2082 SELECT r.*
2083 FROM gcs_cons_relationships r
2084 WHERE r.hierarchy_id = p_hier_id
2085 AND r.parent_entity_id =
2086 (SELECT hb.top_entity_id
2087 FROM gcs_hierarchies_b hb
2088 WHERE hb.hierarchy_id = p_hier_id)
2089 AND r.start_date <= nvl(r.end_date, r.start_date)
2090 AND p_removal_date <= nvl(r.end_date, p_removal_date)
2091 AND r.actual_ownership_flag = 'Y';
2092 CURSOR cons_entity_no_assoc_c IS
2093 SELECT r.child_entity_id, r.start_date, r.end_date
2094 FROM gcs_cons_relationships r,
2095 fem_entities_attr fea_type,
2096 fem_dim_attributes_b fdab_type,
2097 fem_dim_attr_versions_b fdavb_type
2098 WHERE r.hierarchy_id = p_hier_id
2099 AND r.start_date > p_removal_date
2100 AND fea_type.entity_id = r.child_entity_id
2101 AND fea_type.attribute_id = fdab_type.attribute_id
2102 AND fea_type.version_id = fdavb_type.version_id
2103 AND fdab_type.attribute_varchar_label = 'ENTITY_TYPE_CODE'
2104 AND fdavb_type.attribute_id = fdab_type.attribute_id
2105 AND fdavb_type.default_version_flag = 'Y'
2106 AND fea_type.dim_attribute_varchar_member = 'C'
2107 AND NOT EXISTS
2108 (SELECT 1
2109 FROM gcs_cons_relationships rassoc
2110 WHERE rassoc.hierarchy_id = p_hier_id
2111 AND rassoc.parent_entity_id = r.child_entity_id
2112 AND rassoc.start_date <= r.start_date
2113 AND (rassoc.end_date IS NULL OR
2114 (r.end_date IS NOT NULL AND
2115 rassoc.end_date >= r.end_date))
2116 AND rassoc.actual_ownership_flag = 'Y'
2117 AND rassoc.treatment_id IS NULL)
2118 ORDER BY r.child_entity_id, r.start_date;
2119 l_child_id NUMBER;
2120 l_start_date DATE;
2121 l_end_date DATE;
2122 BEGIN
2123 FOR all_child_rel_row IN all_child_rels_c LOOP
2124 handle_remove_internal(p_hier_id,
2125 all_child_rel_row.parent_entity_id,
2126 all_child_rel_row.child_entity_id,
2127 all_child_rel_row.start_date,
2128 all_child_rel_row.end_date,
2129 p_removal_date,
2130 all_child_rel_row.ownership_percent,
2131 all_child_rel_row.treatment_id,
2132 all_child_rel_row.dominant_parent_flag);
2133 END LOOP;
2134 OPEN cons_entity_no_assoc_c;
2135 FETCH cons_entity_no_assoc_c
2136 INTO l_child_id, l_start_date, l_end_date;
2137 WHILE cons_entity_no_assoc_c%FOUND LOOP
2138 CLOSE cons_entity_no_assoc_c;
2139 INSERT INTO gcs_cons_relationships
2140 (CONS_RELATIONSHIP_ID,
2141 HIERARCHY_ID,
2142 PARENT_ENTITY_ID,
2143 CHILD_ENTITY_ID,
2144 OWNERSHIP_PERCENT,
2145 START_DATE,
2146 TREATMENT_ID,
2147 CURR_TREATMENT_ID,
2148 OBJECT_VERSION_NUMBER,
2149 CREATION_DATE,
2150 CREATED_BY,
2151 LAST_UPDATE_DATE,
2152 LAST_UPDATED_BY,
2153 LAST_UPDATE_LOGIN,
2154 END_DATE,
2155 DOMINANT_PARENT_FLAG,
2156 ACTUAL_OWNERSHIP_FLAG)
2157 SELECT gcs_cons_relationships_s.nextval,
2158 p_hier_id,
2159 l_child_id,
2160 fea.DIM_ATTRIBUTE_NUMERIC_MEMBER,
2161 100,
2162 l_start_date,
2163 null,
2164 null,
2165 1,
2166 sysdate,
2167 FND_GLOBAL.USER_ID,
2168 sysdate,
2169 FND_GLOBAL.USER_ID,
2170 FND_GLOBAL.LOGIN_ID,
2171 l_end_date,
2172 'Y',
2173 'Y'
2174 FROM fem_entities_attr fea,
2175 fem_dim_attributes_b fdab,
2176 fem_dim_attr_versions_b fdavb
2177 WHERE fea.entity_id = l_child_id
2178 AND fea.attribute_id = fdab.attribute_id
2179 AND fea.version_id = fdavb.version_id
2180 AND fdab.attribute_varchar_label IN
2181 ('OPERATING_ENTITY', 'ELIMINATION_ENTITY')
2182 AND fdavb.attribute_id = fdab.attribute_id
2183 AND fdavb.default_version_flag = 'Y';
2184 OPEN cons_entity_no_assoc_c;
2185 FETCH cons_entity_no_assoc_c
2186 INTO l_child_id, l_start_date, l_end_date;
2187 END LOOP;
2188 CLOSE cons_entity_no_assoc_c;
2189 END Handle_Remove;
2190
2191 --
2192 -- Procedure
2193 -- Update_Hierarchies_Datatype
2194 -- Purpose
2195 -- .
2196 -- Arguments
2197 -- p_data_type_code Data Type Code identifier
2198 -- Example
2199 -- GCS_HIERARCHIES_PKG.Update_Hierarchies_Datatype('TEST' );
2200 -- Notes
2201 --
2202 PROCEDURE Update_Hierarchies_Datatype(p_data_type_code VARCHAR2) IS
2203 TYPE hier_info_rec_type IS RECORD(
2204 hier_id NUMBER,
2205 hier_name VARCHAR2(150));
2206 TYPE t_heir_info IS TABLE OF hier_info_rec_type;
2207 l_hier_info t_heir_info;
2208 l_data_type_name VARCHAR2(30) := NULL;
2209 l_fem_balance_type VARCHAR2(30) := NULL;
2210 l_dataset_code NUMBER;
2211 l_budget_id NUMBER := NULL;
2212 l_encumbrance_type_id NUMBER := NULL;
2213 l_base_balance_type VARCHAR2(30);
2214 l_analyze_balance_type VARCHAR2(30);
2215 l_base_display_code VARCHAR2(1000);
2216 l_analyze_display_code VARCHAR2(1000);
2217 l_base_description VARCHAR2(1000);
2218 l_analyze_description VARCHAR2(1000);
2219 l_base_ds_code NUMBER;
2220 l_analyze_ds_code NUMBER;
2221 l_base_err_code NUMBER;
2222 l_base_num_msg NUMBER;
2223 l_analyze_err_code NUMBER;
2224 l_analyze_num_msg NUMBER;
2225 l_counter NUMBER;
2226 BEGIN
2227 -- Get Source Data Set's FEM Balance type code for the given data type code.
2228 SELECT gdtctl.data_type_name,
2229 gdtcb.source_dataset_code,
2230 fda.dim_attribute_varchar_member
2231 INTO l_data_type_name, l_dataset_code, l_fem_balance_type
2232 FROM gcs_data_type_codes_b gdtcb,
2233 gcs_data_type_codes_tl gdtctl,
2234 fem_dim_attributes_b fdab,
2235 fem_dim_attr_versions_b fdavb,
2236 fem_datasets_attr fda
2237 WHERE gdtcb.source_dataset_code = fda.dataset_code
2238 AND gdtcb.data_type_id = gdtctl.data_type_id
2239 AND gdtctl.language = userenv('LANG')
2240 AND fda.attribute_id = fdab.attribute_id
2241 AND fdab.attribute_id = fdavb.attribute_id
2242 AND fda.version_id = fdavb.version_id
2243 AND fdavb.default_version_flag = 'Y'
2244 AND fda.attribute_id = gcs_utility_pkg.get_dimension_attribute('DATASET_CODE-DATASET_BALANCE_TYPE_CODE')
2245 AND gdtcb.data_type_code = p_data_type_code;
2246
2247 -- If the source dataset is a budget or encumbrance, get the budget or encumbrance type id of the source dataset
2248 IF l_fem_balance_type = 'BUDGET' THEN
2249 SELECT fda.dim_attribute_numeric_member
2250 INTO l_budget_id
2251 FROM fem_datasets_attr fda,
2252 fem_dim_attributes_b fdab,
2253 fem_dim_attr_versions_b fdavb
2254 WHERE fda.attribute_id = fdab.attribute_id
2255 AND fdab.attribute_id = fdavb.attribute_id
2256 AND fda.version_id = fdavb.version_id
2257 AND fdavb.default_version_flag = 'Y'
2258 AND fda.attribute_id = gcs_utility_pkg.get_dimension_attribute('DATASET_CODE-BUDGET_ID')
2259 AND fda.dataset_code = l_dataset_code;
2260 END IF;
2261
2262 IF l_fem_balance_type = 'ENCUMBRANCE' THEN
2263 SELECT fda.dim_attribute_numeric_member
2264 INTO l_encumbrance_type_id
2265 FROM fem_datasets_attr fda,
2266 fem_dim_attributes_b fdab,
2267 fem_dim_attr_versions_b fdavb
2268 WHERE fda.attribute_id = fdab.attribute_id
2269 AND fdab.attribute_id = fdavb.attribute_id
2270 AND fda.version_id = fdavb.version_id
2271 AND fdavb.default_version_flag = 'Y'
2272 AND fda.attribute_id = gcs_utility_pkg.get_dimension_attribute('DATASET_CODE-ENCUMBRANCE_TYPE_ID')
2273 AND fda.dataset_code = l_dataset_code;
2274 END IF;
2275
2276 l_base_balance_type := p_data_type_code;
2277 l_analyze_balance_type := 'ANALYZE_' || p_data_type_code;
2278
2279 -- Get the hierarchies' information
2280 SELECT hierarchy_id, hierarchy_name BULK COLLECT
2281 INTO l_hier_info
2282 FROM gcs_hierarchies_tl
2283 WHERE language = userenv('LANG');
2284
2285
2286 IF l_hier_info.FIRST IS NOT NULL AND l_hier_info.LAST IS NOT NULL THEN
2287 FOR l_counter IN l_hier_info.FIRST .. l_hier_info.LAST LOOP
2288
2289 l_base_display_code := TO_CHAR(l_hier_info(l_counter).hier_id) || ': ' || l_data_type_name;
2290 l_analyze_display_code := l_hier_info(l_counter).hier_name || ': ' || l_data_type_name;
2291
2292 fnd_message.set_name('GCS', 'GCS_HIER_NEW_DATASET_DESC');
2293 fnd_message.set_token('HIER_NAME', l_hier_info(l_counter).hier_name);
2294 fnd_message.set_token('BAL_TYPE', l_base_balance_type);
2295 l_base_description := fnd_message.get;
2296 fnd_message.set_name('GCS', 'GCS_HIER_NEW_DATASET_DESC');
2297 fnd_message.set_token('HIER_NAME', l_hier_info(l_counter).hier_name);
2298 fnd_message.set_token('BAL_TYPE', l_analyze_balance_type);
2299 l_analyze_description := fnd_message.get;
2300
2301 -- Create a dataset for the base balance type
2302 FEM_DIMENSION_UTIL_PKG.new_dataset(x_err_code => l_base_err_code,
2303 x_num_msg => l_base_num_msg,
2304 p_display_code => l_base_display_code,
2305 p_dataset_name => l_base_display_code,
2306 p_bal_type_cd => l_fem_balance_type,
2307 p_source_cd => 70,
2308 p_budget_id => l_budget_id,
2309 p_enc_type_id => l_encumbrance_type_id,
2310 p_ver_name => 'Default',
2311 p_ver_disp_cd => 'Default',
2312 p_dataset_desc => l_base_description);
2313 SELECT dataset_code
2314 INTO l_base_ds_code
2315 FROM fem_datasets_tl
2316 WHERE language = userenv('LANG')
2317 AND dataset_name = l_base_display_code;
2318
2319 -- Create a row in gcs_dataset_codes for the hierarchy, balance type, and new
2320 -- dataset for the base balance types and datasets
2321 INSERT INTO gcs_dataset_codes
2322 (hierarchy_id,
2323 balance_type_code,
2324 dataset_code,
2325 creation_date,
2326 created_by,
2327 last_update_date,
2328 last_updated_by,
2329 last_update_login)
2330 VALUES
2331 (l_hier_info(l_counter).hier_id,
2332 l_base_balance_type,
2333 l_base_ds_code,
2334 sysdate,
2335 fnd_global.user_id,
2336 sysdate,
2337 fnd_global.user_id,
2338 fnd_global.login_id);
2339
2340 -- Create a dataset for the analyze balance type
2341 FEM_DIMENSION_UTIL_PKG.new_dataset(x_err_code => l_analyze_err_code,
2342 x_num_msg => l_analyze_num_msg,
2343 p_display_code => l_analyze_display_code,
2344 p_dataset_name => l_analyze_display_code,
2345 p_bal_type_cd => l_fem_balance_type,
2346 p_source_cd => 70,
2347 p_budget_id => l_budget_id,
2348 p_enc_type_id => l_encumbrance_type_id,
2349 p_ver_name => 'Default',
2350 p_ver_disp_cd => 'Default',
2351 p_dataset_desc => l_analyze_description);
2352 SELECT dataset_code
2353 INTO l_analyze_ds_code
2354 FROM fem_datasets_tl
2355 WHERE language = userenv('LANG')
2356 AND dataset_name = l_analyze_display_code;
2357
2358 -- Create a row in gcs_dataset_codes for the hierarchy, balance type, and new
2359 -- dataset for analyze balance types and datasets
2360 INSERT INTO gcs_dataset_codes
2361 (hierarchy_id,
2362 balance_type_code,
2363 dataset_code,
2364 creation_date,
2365 created_by,
2366 last_update_date,
2367 last_updated_by,
2368 last_update_login)
2369 VALUES
2370 (l_hier_info(l_counter).hier_id,
2371 l_analyze_balance_type,
2372 l_analyze_ds_code,
2373 sysdate,
2374 fnd_global.user_id,
2375 sysdate,
2376 fnd_global.user_id,
2377 fnd_global.login_id);
2378 END LOOP;
2379 END IF;
2380 END Update_Hierarchies_Datatype;
2381
2382 --
2383 -- Procedure
2384 -- Handle_Datatypes
2385 -- Purpose
2386 -- .
2387 -- Arguments
2388 -- p_hier_id Hierarchy identifier
2389 -- Example
2390 -- GCS_HIERARCHIES_PKG.Handle_Datatypes(123 );
2391 -- Notes
2392 --
2393 PROCEDURE Handle_Datatypes(p_hierarchy_id NUMBER) IS
2394 TYPE data_type_info_rec_type IS RECORD(
2395 data_type_code VARCHAR2(30),
2396 data_type_name VARCHAR2(30),
2397 source_dataset_code NUMBER,
2398 fem_balance_type VARCHAR2(30));
2399 TYPE t_data_type_info IS TABLE OF data_type_info_rec_type;
2400 l_data_type_info t_data_type_info;
2401 l_data_type_name VARCHAR2(30);
2402 l_fem_balance_type VARCHAR2(30);
2403 l_dataset_code NUMBER;
2404 l_budget_id NUMBER := NULL;
2405 l_encumbrance_type_id NUMBER := NULL;
2406 l_base_balance_type VARCHAR2(30);
2407 l_analyze_balance_type VARCHAR2(30);
2408 l_base_display_code VARCHAR2(1000);
2409 l_analyze_display_code VARCHAR2(1000);
2410 l_base_description VARCHAR2(1000);
2411 l_analyze_description VARCHAR2(1000);
2412 l_base_ds_code NUMBER;
2413 l_analyze_ds_code NUMBER;
2414 l_base_err_code NUMBER;
2415 l_base_num_msg NUMBER;
2416 l_analyze_err_code NUMBER;
2417 l_analyze_num_msg NUMBER;
2418 l_counter NUMBER;
2419 l_hierarchy_name VARCHAR2(150);
2420 BEGIN
2421
2422 SELECT hierarchy_name
2423 INTO l_hierarchy_name
2424 FROM gcs_hierarchies_tl
2425 WHERE language = userenv('LANG')
2426 AND hierarchy_id = p_hierarchy_id;
2427
2428 -- Get Source Data Set's FEM Balance type code for the given data type code.
2429 SELECT gdtcb.data_type_code,
2430 gdtctl.data_type_name,
2431 gdtcb.source_dataset_code,
2432 fda.dim_attribute_varchar_member BULK COLLECT
2433 INTO l_data_type_info
2434 FROM gcs_data_type_codes_b gdtcb,
2435 gcs_data_type_codes_tl gdtctl,
2436 fem_dim_attributes_b fdab,
2437 fem_dim_attr_versions_b fdavb,
2438 fem_datasets_attr fda
2439 WHERE gdtcb.source_dataset_code = fda.dataset_code
2440 AND gdtcb.data_type_id = gdtctl.data_type_id
2441 AND gdtctl.language = userenv('LANG')
2442 AND fda.attribute_id = fdab.attribute_id
2443 AND fdab.attribute_id = fdavb.attribute_id
2444 AND fda.version_id = fdavb.version_id
2445 AND fdavb.default_version_flag = 'Y'
2446 AND fda.attribute_id = gcs_utility_pkg.get_dimension_attribute('DATASET_CODE-DATASET_BALANCE_TYPE_CODE');
2447
2448 IF l_data_type_info.FIRST IS NOT NULL AND
2449 l_data_type_info.LAST IS NOT NULL THEN
2450 FOR l_counter IN l_data_type_info.FIRST .. l_data_type_info.LAST LOOP
2451 l_budget_id := NULL;
2452 l_encumbrance_type_id := NULL;
2453 l_data_type_name := l_data_type_info(l_counter).data_type_name;
2454 l_fem_balance_type := l_data_type_info(l_counter).fem_balance_type;
2455 l_dataset_code := l_data_type_info(l_counter).source_dataset_code;
2456 l_base_balance_type := l_data_type_info(l_counter).data_type_code;
2457 l_analyze_balance_type := 'ANALYZE_' || l_base_balance_type;
2458
2459 l_base_display_code := TO_CHAR(p_hierarchy_id) || ': ' || l_data_type_name;
2460 l_analyze_display_code := l_hierarchy_name || ': ' || l_data_type_name;
2461
2462 -- If the source dataset is a budget or encumbrance, get the budget or encumbrance type id of the source dataset
2463 IF l_fem_balance_type = 'BUDGET' THEN
2464 SELECT fda.dim_attribute_numeric_member
2465 INTO l_budget_id
2466 FROM fem_datasets_attr fda,
2467 fem_dim_attributes_b fdab,
2468 fem_dim_attr_versions_b fdavb
2469 WHERE fda.attribute_id = fdab.attribute_id
2470 AND fdab.attribute_id = fdavb.attribute_id
2471 AND fda.version_id = fdavb.version_id
2472 AND fdavb.default_version_flag = 'Y'
2473 AND fda.attribute_id = gcs_utility_pkg.get_dimension_attribute('DATASET_CODE-BUDGET_ID')
2474 AND fda.dataset_code = l_dataset_code;
2475 END IF;
2476
2477 IF l_fem_balance_type = 'ENCUMBRANCE' THEN
2478 SELECT fda.dim_attribute_numeric_member
2479 INTO l_encumbrance_type_id
2480 FROM fem_datasets_attr fda,
2481 fem_dim_attributes_b fdab,
2482 fem_dim_attr_versions_b fdavb
2483 WHERE fda.attribute_id = fdab.attribute_id
2484 AND fdab.attribute_id = fdavb.attribute_id
2485 AND fda.version_id = fdavb.version_id
2486 AND fdavb.default_version_flag = 'Y'
2487 AND fda.attribute_id = gcs_utility_pkg.get_dimension_attribute('DATASET_CODE-ENCUMBRANCE_TYPE_ID')
2488 AND fda.dataset_code = l_dataset_code;
2489 END IF;
2490
2491 fnd_message.set_name('GCS', 'GCS_HIER_NEW_DATASET_DESC');
2492 fnd_message.set_token('HIER_NAME', l_hierarchy_name);
2493 fnd_message.set_token('BAL_TYPE', l_base_balance_type);
2494 l_base_description := fnd_message.get;
2495 fnd_message.set_name('GCS', 'GCS_HIER_NEW_DATASET_DESC');
2496 fnd_message.set_token('HIER_NAME', l_hierarchy_name);
2497 fnd_message.set_token('BAL_TYPE', l_analyze_balance_type);
2498 l_analyze_description := fnd_message.get;
2499
2500 -- Create a dataset for the base balance type
2501 FEM_DIMENSION_UTIL_PKG.new_dataset(x_err_code => l_base_err_code,
2502 x_num_msg => l_base_num_msg,
2503 p_display_code => l_base_display_code,
2504 p_dataset_name => l_base_display_code,
2505 p_bal_type_cd => l_fem_balance_type,
2506 p_source_cd => 70,
2507 p_budget_id => l_budget_id,
2508 p_enc_type_id => l_encumbrance_type_id,
2509 p_ver_name => 'Default',
2510 p_ver_disp_cd => 'Default',
2511 p_dataset_desc => l_base_description);
2512 SELECT dataset_code
2513 INTO l_base_ds_code
2514 FROM fem_datasets_tl
2515 WHERE language = userenv('LANG')
2516 AND dataset_name = l_base_display_code;
2517
2518 -- Create a row in gcs_dataset_codes for the hierarchy, balance type, and new
2519 -- dataset for the base balance types and datasets
2520 INSERT INTO gcs_dataset_codes
2521 (hierarchy_id,
2522 balance_type_code,
2523 dataset_code,
2524 creation_date,
2525 created_by,
2526 last_update_date,
2527 last_updated_by,
2528 last_update_login)
2529 VALUES
2530 (p_hierarchy_id,
2531 l_base_balance_type,
2532 l_base_ds_code,
2533 sysdate,
2534 fnd_global.user_id,
2535 sysdate,
2536 fnd_global.user_id,
2537 fnd_global.login_id);
2538
2539 -- Create a dataset for the analyze balance type
2540 FEM_DIMENSION_UTIL_PKG.new_dataset(x_err_code => l_analyze_err_code,
2541 x_num_msg => l_analyze_num_msg,
2542 p_display_code => l_analyze_display_code,
2543 p_dataset_name => l_analyze_display_code,
2544 p_bal_type_cd => l_fem_balance_type,
2545 p_source_cd => 70,
2546 p_budget_id => l_budget_id,
2547 p_enc_type_id => l_encumbrance_type_id,
2548 p_ver_name => 'Default',
2549 p_ver_disp_cd => 'Default',
2550 p_dataset_desc => l_analyze_description);
2551 SELECT dataset_code
2552 INTO l_analyze_ds_code
2553 FROM fem_datasets_tl
2554 WHERE language = userenv('LANG')
2555 AND dataset_name = l_analyze_display_code;
2556
2557 -- Create a row in gcs_dataset_codes for the hierarchy, balance type, and new
2558 -- dataset for analyze balance types and datasets
2559 INSERT INTO gcs_dataset_codes
2560 (hierarchy_id,
2561 balance_type_code,
2562 dataset_code,
2563 creation_date,
2564 created_by,
2565 last_update_date,
2566 last_updated_by,
2567 last_update_login)
2568 VALUES
2569 (p_hierarchy_id,
2570 l_analyze_balance_type,
2571 l_analyze_ds_code,
2572 sysdate,
2573 fnd_global.user_id,
2574 sysdate,
2575 fnd_global.user_id,
2576 fnd_global.login_id);
2577 END LOOP;
2578 END IF;
2579 END Handle_Datatypes;
2580
2581 --
2582 -- Procedure
2583 -- Handle_Datasets_Ledger
2584 -- Purpose
2585 -- Updates the Dataset name/desc and Ledger Name/Desc when Hierarchy Name is changed.
2586 -- Arguments
2587 -- p_hier_id Hierarchy identifier
2588 -- Example
2589 -- GCS_HIERARCHIES_PKG.Handle_Datasets_Ledger(hierarchyId );
2590 -- Notes
2591 --
2592 PROCEDURE Handle_Datasets_Ledger(p_hierarchy_id NUMBER) IS
2593 TYPE dataset_info_rec_type IS RECORD(balance_type_code VARCHAR2(30),
2594 dataset_code NUMBER,
2595 data_type_name VARCHAR2(30));
2596 TYPE t_dataset_info IS TABLE OF dataset_info_rec_type;
2597 l_dataset_info t_dataset_info;
2598 l_dataset_code NUMBER;
2599 l_balance_type VARCHAR2(150);
2600 l_data_type_name VARCHAR2(150);
2601 l_display_code VARCHAR2(150);
2602 l_description VARCHAR2(255);
2603 l_counter NUMBER;
2604 l_hierarchy_name VARCHAR2(150);
2605 l_ledger_id NUMBER;
2606 l_ledger_name VARCHAR2(150);
2607 l_ledger_desc VARCHAR2(255);
2608 BEGIN
2609
2610 SELECT tl.hierarchy_name,
2611 b.fem_ledger_id
2612 INTO l_hierarchy_name,l_ledger_id
2613 FROM gcs_hierarchies_b b,
2614 gcs_hierarchies_tl tl
2615 WHERE b.hierarchy_id = tl.hierarchy_id
2616 AND tl.hierarchy_id = p_hierarchy_id
2617 AND tl.language = userenv('LANG');
2618
2619
2620 SELECT gdc.balance_type_code,
2621 gdc.dataset_code,
2622 gtl.data_type_name BULK COLLECT
2623 INTO l_dataset_info
2624 FROM gcs_dataset_codes gdc,
2625 gcs_data_type_codes_b gtb,
2626 gcs_data_type_codes_tl gtl
2627 WHERE gdc.hierarchy_id = p_hierarchy_id
2628 AND INSTR(gdc.balance_type_code,gtb.data_type_code) > 0
2629 AND gtb.data_type_id = gtl.data_type_id
2630 AND gtl.language = userenv('LANG');
2631
2632 -- Update fem_datasets_b/tl tables accordingly by setting proper Dataset Name and Description
2633 IF l_dataset_info.FIRST IS NOT NULL AND l_dataset_info.LAST IS NOT NULL THEN
2634 FOR l_counter IN l_dataset_info.FIRST .. l_dataset_info.LAST LOOP
2635
2636 l_balance_type := l_dataset_info(l_counter).balance_type_code;
2637 l_dataset_code := l_dataset_info(l_counter).dataset_code;
2638 l_data_type_name := l_dataset_info(l_counter).data_type_name;
2639
2640 IF INSTR(l_balance_type, 'ANALYZE_') = 1 THEN
2641 l_display_code := l_hierarchy_name || ': ' || l_data_type_name;
2642 ELSE
2643 l_display_code := TO_CHAR(p_hierarchy_id) || ': ' || l_data_type_name;
2644 END IF;
2645
2646 fnd_message.set_name('GCS', 'GCS_HIER_NEW_DATASET_DESC');
2647 fnd_message.set_token('HIER_NAME', l_hierarchy_name);
2648 fnd_message.set_token('BAL_TYPE', l_balance_type);
2649 l_description := fnd_message.get;
2650
2651 UPDATE fem_datasets_b
2652 SET dataset_display_code = l_display_code
2653 WHERE dataset_code = l_dataset_code;
2654
2655 UPDATE fem_datasets_tl
2656 SET dataset_name = l_display_code,
2657 description = l_description
2658 WHERE dataset_code = l_dataset_code
2659 AND language = userenv('LANG');
2660 END LOOP;
2661 END IF;
2662
2663 -- Update the fem_ledgers_b/tl tables accordingly by setting proper Ledger Name and Description
2664
2665 fnd_message.set_name('GCS', 'GCS_HIER_NEW_LEDGER_NAME');
2666 fnd_message.set_token('HIER_NAME', l_hierarchy_name);
2667 l_ledger_name := fnd_message.get;
2668
2669 fnd_message.set_name('GCS', 'GCS_HIER_NEW_LEDGER_DESC');
2670 fnd_message.set_token('HIER_NAME', l_hierarchy_name);
2671 l_ledger_desc := fnd_message.get;
2672
2673 UPDATE fem_ledgers_b
2674 SET ledger_display_code = l_ledger_name
2675 WHERE ledger_id = l_ledger_id;
2676
2677 UPDATE fem_ledgers_tl
2678 SET ledger_name = l_ledger_name,
2679 description = l_ledger_desc
2680 WHERE ledger_id = l_ledger_id
2681 AND language = userenv('LANG');
2682
2683 END Handle_Datasets_Ledger;
2684
2685 END GCS_HIERARCHIES_PKG;