[Home] [Help]
PACKAGE BODY: APPS.GCS_CONS_IMPACT_ANALYSIS_PKG
Source
1 PACKAGE BODY GCS_CONS_IMPACT_ANALYSIS_PKG as
2 /* $Header: gcs_cons_impactb.pls 120.6 2007/12/06 10:08:57 smatam noship $ */
3
4 g_api VARCHAR2(80) := 'gcs.plsql.GCS_CONS_IMPACT_ANALYSIS_PKG';
5
6 PROCEDURE rollup_impact(p_consolidation_entity_id IN NUMBER,
7 p_hierarchy_id IN NUMBER,
8 p_cal_period_id IN NUMBER)
9
10 IS
11
12 CURSOR c_parent_entity(p_child_entity_id NUMBER, p_hierarchy_id NUMBER, p_end_date DATE, p_cal_period_id NUMBER) IS
13
14 SELECT gcr.parent_entity_id
15 FROM gcs_cons_relationships gcr, gcs_cons_eng_runs gcer
16 WHERE gcr.hierarchy_id = p_hierarchy_id
17 AND gcr.child_entity_id = p_child_entity_id
18 AND gcr.dominant_parent_flag = 'Y'
19 AND gcr.hierarchy_id = gcer.hierarchy_id
20 AND gcr.parent_entity_id = gcer.run_entity_id
21 AND gcer.cal_period_id = p_cal_period_id
22 AND gcer.most_recent_flag = 'Y'
23 AND gcer.impacted_flag = 'N'
24 AND p_end_date BETWEEN gcr.start_date AND
25 NVL(gcr.end_date, p_end_date);
26
27 l_rows_updated NUMBER(1);
28 l_child_entity_id NUMBER(15);
29 l_end_date_attribute_id NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
30 .attribute_id;
31 l_end_date_version_id NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
32 .version_id;
33 l_period_num_attribute_id NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-GL_PERIOD_NUM')
34 .attribute_id;
35 l_period_num_version_id NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-GL_PERIOD_NUM')
36 .version_id;
37 l_acct_year_attribute_id NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-ACCOUNTING_YEAR')
38 .attribute_id;
39 l_acct_year_version_id NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-ACCOUNTING_YEAR')
40 .version_id;
41 l_end_date DATE;
42 l_cal_period_id NUMBER;
43 l_target_cal_period_id NUMBER;
44 l_period_mapping_required VARCHAR2(1) := 'Y';
45 l_src_calendar_id NUMBER;
46 l_src_dimension_group_id NUMBER;
47 l_tgt_calendar_id NUMBER;
48 l_tgt_dimension_group_id NUMBER;
49 l_cal_period_map_id NUMBER;
50 l_cal_period_record gcs_utility_pkg.r_cal_period_info;
51
52 BEGIN
53 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
54 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
55 g_api || '.ROLLUP_IMPACT.begin',
56 '<<Enter>>');
57 END IF;
58
59 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
60 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
61 g_api || '.ROLLUP_IMPACT',
62 'Consolidation Entity : ' ||
63 p_consolidation_entity_id);
64 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
65 g_api || '.ROLLUP_IMPACT',
66 'Hierarchy : ' || p_hierarchy_id);
67 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
68 g_api || '.ROLLUP_IMPACT',
69 'Calendar Period Id : ' || p_cal_period_id);
70 END IF;
71
72 l_child_entity_id := p_consolidation_entity_id;
73
74 -- Start Bug fix : 6029020
75 BEGIN
76 SELECT gcpm.cal_period_map_id,
77 gcpm.SOURCE_CALENDAR_ID,
78 gcpm.SOURCE_DIMENSION_GROUP_ID,
79 gcpm.TARGET_CALENDAR_ID,
80 gcpm.TARGET_DIMENSION_GROUP_ID
81 INTO l_cal_period_map_id,
82 l_src_calendar_id,
83 l_src_dimension_group_id,
84 l_tgt_calendar_id,
85 l_tgt_dimension_group_id
86 FROM GCS_CAL_PERIOD_MAPS gcpm,
87 gcs_hierarchies_b ghb,
88 fem_cal_periods_b fcpb
89 WHERE gcpm.SOURCE_CALENDAR_ID = fcpb.CALENDAR_ID
90 AND gcpm.SOURCE_DIMENSION_GROUP_ID = fcpb.DIMENSION_GROUP_ID
91 AND gcpm.TARGET_CALENDAR_ID = ghb.CALENDAR_ID
92 AND gcpm.TARGET_DIMENSION_GROUP_ID = ghb.DIMENSION_GROUP_ID
93 AND ghb.HIERARCHY_ID = p_hierarchy_id
94 AND fcpb.cal_period_id = p_cal_period_id;
95 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
96 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
97 g_api || '.ROLLUP_IMPACT',
98 'Calendar Period Map Id = ' || l_cal_period_map_id);
99 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
100 g_api || '.ROLLUP_IMPACT',
101 ' Source Calendar Id = ' || l_src_calendar_id);
102 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
103 g_api || '.ROLLUP_IMPACT',
104 'Source Dimension Group Id = ' ||
105 l_src_dimension_group_id);
106 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
107 g_api || '.ROLLUP_IMPACT',
108 ' Target Calendar Id = ' || l_tgt_calendar_id);
109 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
110 g_api || '.ROLLUP_IMPACT',
111 'Target Dimension Group Id = ' ||
112 l_tgt_dimension_group_id);
113 END IF;
114 EXCEPTION
115 WHEN OTHERS THEN
116 l_period_mapping_required := 'N';
117 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
118 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
119 g_api || '.ROLLUP_IMPACT.exception',
120 'NO DATA FOUND ');
121 END IF;
122 END;
123 IF (l_period_mapping_required = 'Y') THEN
124 --period_mapping_required
125 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
126 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
127 g_api || '.ROLLUP_IMPACT',
128 '<< Calendar Period Mapping required >>');
129 END IF;
130
131 gcs_utility_pkg.get_cal_period_details(p_cal_period_id,
132 l_cal_period_record);
133 SELECT fcpb.cal_period_id
134 INTO l_target_cal_period_id
135 FROM fem_cal_periods_b fcpb,
136 fem_cal_periods_attr fcpa_number,
137 fem_cal_periods_attr fcpa_year,
138 gcs_cal_period_map_dtls gcpmd
139 WHERE gcpmd.cal_period_map_id = l_cal_period_map_id
140 AND fcpb.calendar_id = l_tgt_calendar_id
141 AND fcpb.dimension_group_id = l_tgt_dimension_group_id
142 AND fcpb.cal_period_id = fcpa_number.cal_period_id
143 AND fcpb.cal_period_id = fcpa_year.cal_period_id
144 AND fcpa_number.attribute_id = l_period_num_attribute_id
145 AND fcpa_year.attribute_id = l_acct_year_attribute_id
146 AND fcpa_number.version_id = l_period_num_version_id
147 AND fcpa_year.version_id = l_acct_year_version_id
148 AND fcpa_number.number_assign_value = gcpmd.target_period_number
149 AND gcpmd.source_period_number =
150 l_cal_period_record.cal_period_number
151 AND fcpa_year.number_assign_value =
152 DECODE(gcpmd.target_relative_year_code,
153 'CURRENT',
154 l_cal_period_record.cal_period_year,
155 'PRIOR',
156 l_cal_period_record.cal_period_year - 1,
157 'FOLLOWING',
158 l_cal_period_record.cal_period_year + 1);
159 l_cal_period_id := l_target_cal_period_id;
160 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
161 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
162 g_api || '.ROLLUP_IMPACT',
163 'Target Calendar Period Id = ' || l_cal_period_id);
164 END IF;
165 ELSE
166 --l_period_mapping_not_required
167 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
168 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
169 g_api || '.ROLLUP_IMPACT',
170 '<< Calendar Period Mapping is NOT required >>');
171 END IF;
172 l_cal_period_id := p_cal_period_id;
173 END IF;
174
175 SELECT date_assign_value
176 INTO l_end_date
177 FROM fem_cal_periods_attr
178 WHERE cal_period_id = l_cal_period_id
179 AND attribute_id = l_end_date_attribute_id
180 AND version_id = l_end_date_version_id;
181
182 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
183 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
184 g_api || '.ROLLUP_IMPACT',
185 ' Calendar Period End Date = ' || l_end_date);
186 END IF;
187 WHILE (1 = 1) LOOP
188 l_rows_updated := 0;
189 FOR v_parent_entity IN c_parent_entity(l_child_entity_id,
190 p_hierarchy_id,
191 l_end_date,
192 l_cal_period_id) LOOP
193 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
194 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
195 g_api || '.ROLLUP_IMPACT',
196 ' l_child_entity_id = ' || l_child_entity_id);
197 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
198 g_api || '.ROLLUP_IMPACT',
199 ' Updating the impacted status of Entity with Entity_Id = ' ||
200 v_parent_entity.parent_entity_id);
201 END IF;
202
203 UPDATE gcs_cons_eng_runs
204 SET impacted_flag = 'Y'
205 WHERE hierarchy_id = p_hierarchy_id
206 AND run_entity_id = v_parent_entity.parent_entity_id
207 AND cal_period_id = l_cal_period_id
208 AND most_recent_flag = 'Y';
209
210 IF (SQL%ROWCOUNT = 0) THEN
211 l_rows_updated := 0;
212 ELSE
213 l_rows_updated := 1;
214 END IF;
215
216 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
217 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
218 g_api || '.ROLLUP_IMPACT',
219 ' Done');
220 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
221 g_api || '.ROLLUP_IMPACT',
222 'SQL%ROWCOUNT = ' || l_rows_updated);
223 END IF;
224 l_child_entity_id := v_parent_entity.parent_entity_id;
225
226 END LOOP;
227
228 IF (l_rows_updated = 0) THEN
229 EXIT;
230 END IF;
231 END LOOP;
232
233 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
234 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
235 g_api || '.ROLLUP_IMPACT.end',
236 '<<Exit>>');
237 END IF;
238 EXCEPTION
239 WHEN OTHERS THEN
240 l_period_mapping_required := 'N';
241 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
242 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
243 g_api || '.ROLLUP_IMPACT.exception',
244 SUBSTR(SQLERRM, 1, 100));
245 END IF;
246 -- End Bug fix : 6029020
247 END rollup_impact;
248
249 PROCEDURE insert_impact_analysis(p_run_name IN VARCHAR2,
250 p_consolidation_entity_id IN NUMBER,
251 p_child_entity_id IN NUMBER,
252 p_message_name IN VARCHAR2,
253 p_pre_relationship_id IN NUMBER DEFAULT NULL,
254 p_post_relationship_id IN NUMBER DEFAULT NULL,
255 p_date_token IN DATE DEFAULT NULL,
256 p_stat_entry_id IN NUMBER DEFAULT NULL,
257 p_entry_id IN NUMBER DEFAULT NULL,
258 p_orig_entry_id IN NUMBER DEFAULT NULL,
259 p_pre_prop_entry_id IN NUMBER DEFAULT NULL,
260 p_pre_prop_stat_entry_id IN NUMBER DEFAULT NULL,
261 p_load_id IN NUMBER DEFAULT NULL)
262
263 IS
264
265 BEGIN
266 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
267 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
268 g_api || '.INSERT_IMPACT_ANALYSIS.begin',
269 '<<Enter Module>>');
270 END IF;
271
272 INSERT INTO gcs_cons_impact_analyses
273 (impact_analysis_id,
274 run_name,
275 consolidation_entity_id,
276 child_entity_id,
277 message_name,
278 pre_relationship_id,
279 post_relationship_id,
280 date_token,
281 stat_entry_id,
282 entry_id,
283 original_entry_id,
284 pre_prop_entry_id,
285 pre_prop_stat_entry_id,
286 creation_date,
287 created_by,
288 last_updated_by,
289 last_update_date,
290 last_update_login,
291 object_version_number,
292 load_id)
293 VALUES
294 (gcs_cons_impact_analyses_s.nextval,
295 p_run_name,
296 p_consolidation_entity_id,
297 p_child_entity_id,
298 p_message_name,
299 p_pre_relationship_id,
300 p_post_relationship_id,
301 p_date_token,
302 p_stat_entry_id,
303 p_entry_id,
304 p_orig_entry_id,
305 p_pre_prop_entry_id,
306 p_pre_prop_stat_entry_id,
307 SYSDATE,
308 FND_GLOBAL.USER_ID,
309 FND_GLOBAL.USER_ID,
310 SYSDATE,
311 FND_GLOBAL.LOGIN_ID,
312 1, -- Bugfix 3718098 : Added OBJECT_VERSION_NUMBER per Release Standards,
313 p_load_id);
314
315 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
316 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
317 g_api || '.INSERT_IMPACT_ANALYSIS.end',
318 '<<Exit Module>>');
319 END IF;
320
321 END insert_impact_analysis;
322
323 -- Definition of Public Procedures
324
325 FUNCTION hierarchy_altered(p_pre_cons_relationship_id IN NUMBER,
326 p_post_cons_relationship_id IN NUMBER,
327 p_trx_type_code IN VARCHAR2,
328 p_trx_date_day IN NUMBER,
329 p_trx_date_month IN NUMBER,
330 p_trx_date_year IN NUMBER,
331 p_hidden_flag IN VARCHAR2,
332 p_intermediate_trtmnt_id IN NUMBER,
333 p_intermediate_pct_owned IN NUMBER)
334 RETURN VARCHAR2
335
336 IS
337 l_parameter_list wf_parameter_list_t;
338 l_pre_cons_relationship_id NUMBER(15);
339 l_post_cons_relationship_id NUMBER(15);
340 l_trx_type_code VARCHAR2(30);
341 l_trx_date DATE;
342 l_intermediate_trtmnt_id NUMBER(15);
343 l_intermediate_pct_owned NUMBER;
344 l_enabled_flag VARCHAR2(1);
345 l_param_counter NUMBER(15);
346 l_orig_treatment_id NUMBER(15);
347 l_new_treatment_id NUMBER(15);
348 l_orig_pct_owned NUMBER;
349 l_new_pct_owned NUMBER;
350 l_orig_curr_trtmnt_id NUMBER;
351 l_new_curr_trtmnt_id NUMBER;
352 l_hierarchy_id NUMBER;
353 l_cal_period_id NUMBER;
354 l_parent_entity_id NUMBER;
355 l_child_entity_id NUMBER;
356 l_run_name VARCHAR2(80);
360 l_cp_end_date_version_id NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
357 l_impact_occurred BOOLEAN := FALSE;
358 l_cp_end_date_attr_id NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
359 .attribute_id;
361 .version_id;
362 l_cp_start_date_attr_id NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_START_DATE')
363 .attribute_id;
364 l_cp_start_date_version_id NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_START_DATE')
365 .version_id;
366 l_parent_entity_name VARCHAR2(80);
367 l_child_entity_name VARCHAR2(80);
368 l_email VARCHAR2(200);
369 l_orig_treatment VARCHAR2(100);
370 l_new_treatment VARCHAR2(100);
371
372 CURSOR c_select_hier_info(p_pre_cons_relationship_id IN NUMBER, p_post_cons_relationship_id IN NUMBER) IS
373 SELECT gcr.hierarchy_id,
374 gcr.parent_entity_id,
375 gcr.child_entity_id,
376 gcr.cons_relationship_id,
377 gcr.curr_treatment_id,
378 gcr.treatment_id,
379 gcr.ownership_percent
380 FROM gcs_cons_relationships gcr
381 WHERE gcr.cons_relationship_id IN
382 (p_post_cons_relationship_id, p_pre_cons_relationship_id);
383
384 BEGIN
385
386 l_pre_cons_relationship_id := p_pre_cons_relationship_id;
387 l_post_cons_relationship_id := p_post_cons_relationship_id;
388 l_trx_type_code := p_trx_type_code;
389 l_trx_date := TO_DATE(p_trx_date_day || '-' ||
390 p_trx_date_month || '-' ||
391 p_trx_date_year,
392 'DD-MM-YYYY');
393 IF (p_hidden_flag = 'Y') THEN
394 l_enabled_flag := 'N';
395 ELSE
396 l_enabled_flag := 'Y';
397 END IF;
398 l_intermediate_trtmnt_id := p_intermediate_trtmnt_id;
399 l_intermediate_pct_owned := p_intermediate_pct_owned;
400
401 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
402 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
403 g_api || '.HIERARCHY_ALTERED.begin',
404 '<<Enter>>');
405 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
406 g_api || '.HIERARCHY_ALTERED',
407 'Pre-Cons Relationship Id : ' ||
408 p_pre_cons_relationship_id);
409 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
410 g_api || '.HIERARCHY_ALTERED',
411 'Post-Cons Relationship Id : ' ||
412 p_post_cons_relationship_id);
413 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
414 g_api || '.HIERARCHY_ALTERED',
415 'Trx Date : ' || l_trx_date);
416 END IF;
417
418 -- Initialize Critical Variables
419 FOR v_select_hier_info IN c_select_hier_info(l_pre_cons_relationship_id,
420 l_post_cons_relationship_id) LOOP
421 IF (l_hierarchy_id IS NULL) THEN
422 l_hierarchy_id := v_select_hier_info.hierarchy_id;
423 l_parent_entity_id := v_select_hier_info.parent_entity_id;
424 l_child_entity_id := v_select_hier_info.child_entity_id;
425 END IF;
426
427 IF (v_select_hier_info.cons_relationship_id =
428 l_pre_cons_relationship_id) THEN
429 l_orig_treatment_id := v_select_hier_info.treatment_id;
430 l_orig_pct_owned := v_select_hier_info.ownership_percent;
431 l_orig_curr_trtmnt_id := v_select_hier_info.curr_treatment_id;
432 ELSE
433 l_new_treatment_id := v_select_hier_info.treatment_id;
434 l_new_pct_owned := v_select_hier_info.ownership_percent;
438 IF (l_pre_cons_relationship_id = -1) THEN
435 l_new_curr_trtmnt_id := v_select_hier_info.curr_treatment_id;
436 END IF;
437
439 l_orig_treatment_id := l_intermediate_trtmnt_id;
440 l_orig_pct_owned := l_intermediate_pct_owned;
441 ELSIF (l_post_cons_relationship_id = -1) THEN
442 l_new_treatment_id := l_intermediate_trtmnt_id;
443 l_new_pct_owned := l_intermediate_pct_owned;
444 END IF;
445 END LOOP;
446
447 --Change Data Status : Bugfix 4179351
448 IF (l_pre_cons_relationship_id = -1) THEN
449 gcs_cons_monitor_pkg.update_data_status(p_load_id => NULL,
450 p_cons_rel_id => l_post_cons_relationship_id,
451 p_hierarchy_id => l_hierarchy_id,
452 p_transaction_type => 'ACQ');
453 ELSIF (l_post_cons_relationship_id = -1) THEN
454 gcs_cons_monitor_pkg.update_data_status(p_load_id => NULL,
455 p_cons_rel_id => l_pre_cons_relationship_id,
456 p_hierarchy_id => l_hierarchy_id,
457 p_transaction_type => 'DIS');
458 END IF;
459
460 --Extract Calendar Period Information
461 SELECT fcb.cal_period_id
462 INTO l_cal_period_id
463 FROM fem_cal_periods_b fcb,
464 gcs_hierarchies_b fhb,
465 fem_cal_periods_attr fcpa_start_date,
466 fem_cal_periods_attr fcpa_end_date
467 WHERE fhb.hierarchy_id = l_hierarchy_id
468 AND fcb.calendar_id = fhb.calendar_id
469 AND fcb.dimension_group_id = fhb.dimension_group_id
470 AND fcb.cal_period_id = fcpa_start_date.cal_period_id
471 AND fcb.cal_period_id = fcpa_end_date.cal_period_id
472 AND fcpa_start_date.attribute_id = l_cp_start_date_attr_id
473 AND fcpa_start_date.version_id = l_cp_start_date_version_id
474 AND fcpa_end_date.attribute_id = l_cp_end_date_attr_id
475 AND fcpa_end_date.version_id = l_cp_end_date_version_id
476 AND l_trx_date BETWEEN fcpa_start_date.date_assign_value AND
477 fcpa_end_date.date_assign_value;
478
479 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
480 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
481 g_api || '.HIERARCHY_ALTERED',
482 'Calendar Period : ' || l_cal_period_id);
483 END IF;
484
485 UPDATE gcs_ad_transactions gat
486 SET gat.enabled_flag = 'N', gat.hidden_flag = 'N'
487 WHERE gat.transaction_date >= l_trx_date
488 AND (EXISTS
489 (SELECT 'X'
490 FROM gcs_cons_relationships pre
491 WHERE pre.hierarchy_id = l_hierarchy_id
492 AND pre.cons_relationship_id = gat.pre_cons_relationship_id
493 AND pre.parent_entity_id = l_parent_entity_id
494 AND pre.child_entity_id = l_child_entity_id) OR EXISTS
495 (SELECT 'X'
496 FROM gcs_cons_relationships post
497 WHERE post.hierarchy_id = l_hierarchy_id
498 AND post.cons_relationship_id = gat.post_cons_relationship_id
499 AND post.parent_entity_id = l_parent_entity_id
500 AND post.child_entity_id = l_child_entity_id));
501
502 -- Insert records into GCS_AD_TRANSACTIONS
503 INSERT INTO GCS_AD_TRANSACTIONS
504 (ad_transaction_id,
505 pre_cons_relationship_id,
506 post_cons_relationship_id,
507 transaction_type_code,
508 hidden_flag,
509 enabled_flag,
510 last_update_date,
511 last_updated_by,
512 creation_date,
513 created_by,
514 last_update_login,
515 intermediate_treatment_id,
516 intermediate_percent_owned,
517 cal_period_id,
518 transaction_date)
519 VALUES
520 (GCS_AD_TRANSACTIONS_S.NEXTVAL,
521 DECODE(l_pre_cons_relationship_id,
522 -1,
523 NULL,
524 l_pre_cons_relationship_id),
525 DECODE(l_post_cons_relationship_id,
526 -1,
527 NULL,
528 l_post_cons_relationship_id),
529 l_trx_type_code,
530 'N',
531 l_enabled_flag,
532 SYSDATE,
533 FND_GLOBAL.USER_ID,
534 SYSDATE,
535 FND_GLOBAL.USER_ID,
536 FND_GLOBAL.LOGIN_ID,
537 l_intermediate_trtmnt_id,
538 l_intermediate_pct_owned,
539 l_cal_period_id,
540 l_trx_date);
541
542 -- Bugfix 4309316: Need to synchronize new additions with EPF
543 IF (l_pre_cons_relationship_id = -1) THEN
547
544 gcs_fem_hier_sync_pkg.entity_added(p_hierarchy_id => l_hierarchy_id,
545 p_cons_relationship_id => l_post_cons_relationship_id);
546 END IF;
548 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
549 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
550 g_api || '.HIERARCHY_ALTERED',
551 'Checking for Impact Analysis');
552 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
553 g_api || '.HIERARCHY_ALTERED',
554 'Parent Entity ID : ' || l_parent_entity_id);
555 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
556 g_api || '.HIERARCHY_ALTERED',
557 'Hierarchy ID : ' || l_hierarchy_id);
558 END IF;
559
560 --Check for Impact Analysis
561 BEGIN
562
563 BEGIN
564 SELECT run_name
565 INTO l_run_name
566 FROM gcs_cons_eng_runs
567 WHERE run_entity_id = l_parent_entity_id
568 AND hierarchy_id = l_hierarchy_id
569 AND most_recent_flag = 'Y'
570 AND cal_period_id = l_cal_period_id;
571 EXCEPTION
572 WHEN NO_DATA_FOUND THEN
573 --Check if change happened in earlier period
574 SELECT gcer.run_name, gcer.cal_period_id
575 INTO l_run_name, l_cal_period_id
576 FROM gcs_cons_eng_runs gcer
577 WHERE gcer.run_entity_id = l_parent_entity_id
578 AND gcer.hierarchy_id = l_hierarchy_id
579 AND most_recent_flag = 'Y'
580 AND cal_period_id =
581 (SELECT min(gcer_inner.cal_period_id)
582 FROM gcs_cons_eng_runs gcer_inner
583 WHERE gcer_inner.run_entity_id = l_parent_entity_id
584 AND gcer_inner.hierarchy_id = l_hierarchy_id
585 AND gcer_inner.most_recent_flag = 'Y'
586 AND gcer_inner.cal_period_id > l_cal_period_id);
587 END;
588
589 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
590 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
591 g_api || '.HIERARCHY_ALTERED',
592 'Run Name :');
593 END IF;
594
595 SELECT fev_parent.entity_name, fev_child.entity_name
596 INTO l_parent_entity_name, l_child_entity_name
597 FROM fem_entities_vl fev_parent, fem_entities_vl fev_child
598 WHERE fev_parent.entity_id = l_parent_entity_id
599 AND fev_child.entity_id = l_child_entity_id;
600
601 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
602 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
603 g_api || '.HIERARCHY_ALTERED',
604 'Pre Cons Relatonship Id :' ||
605 l_pre_cons_relationship_id);
606 END IF;
607
608 IF (l_pre_cons_relationship_id = -1) THEN
609 l_impact_occurred := TRUE;
610 insert_impact_analysis(p_run_name => l_run_name,
611 p_consolidation_entity_id => l_parent_entity_id,
612 p_child_entity_id => l_child_entity_id,
613 p_message_name => 'GCS_ENTITY_ACQUIRED',
614 p_post_relationship_id => l_post_cons_relationship_id,
618 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
615 p_date_token => l_trx_date);
616
617 ELSIF (l_post_cons_relationship_id = -1) THEN
619 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
620 g_api || '.HIERARCHY_ALTERED',
621 'Post Cons Relatonship Id :' ||
622 l_pre_cons_relationship_id);
623 END IF;
624
625 l_impact_occurred := TRUE;
626 insert_impact_analysis(p_run_name => l_run_name,
627 p_consolidation_entity_id => l_parent_entity_id,
628 p_child_entity_id => l_child_entity_id,
629 p_message_name => 'GCS_ENTITY_DISPOSED',
630 p_pre_relationship_id => l_pre_cons_relationship_id,
631 p_date_token => l_trx_date);
632
633 ELSE
634
635 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
636 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
637 g_api || '.hierarchy_altered',
638 'Original Treatment Id : ' || l_orig_treatment_id);
639 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
640 g_api || '.hierarchy_altered',
641 'New Treatment Id : ' || l_new_treatment_id);
642 END IF;
643
644 IF (l_orig_treatment_id <> l_new_treatment_id) THEN
645 l_impact_occurred := TRUE;
646 insert_impact_analysis(p_run_name => l_run_name,
647 p_consolidation_entity_id => l_parent_entity_id,
648 p_child_entity_id => l_child_entity_id,
649 p_message_name => 'GCS_CONS_TREATMENT_ALTERED',
650 p_pre_relationship_id => l_pre_cons_relationship_id,
651 p_post_relationship_id => l_post_cons_relationship_id,
652 p_date_token => l_trx_date);
653
654 SELECT gcs_orig.treatment_name, gcs_new.treatment_name
655 INTO l_orig_treatment, l_new_treatment
656 FROM gcs_treatments_tl gcs_orig, gcs_treatments_tl gcs_new
657 WHERE gcs_orig.treatment_id = l_orig_treatment_id
658 AND gcs_new.treatment_id = l_new_treatment_id
659 AND gcs_orig.language = USERENV('LANG')
660 AND gcs_new.language = USERENV('LANG');
661
662 END IF;
663
664 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
665 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
666 g_api || '.HIERARCHY_ALTERED',
667 'Original Pct Owned : ' || l_new_pct_owned);
668 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
669 g_api || '.HIERARCHY_ALTERED',
670 'New Pct Owned : ' || l_orig_pct_owned);
671 END IF;
672
673 IF (l_orig_pct_owned <> l_new_pct_owned) THEN
674 l_impact_occurred := TRUE;
675 insert_impact_analysis(p_run_name => l_run_name,
676 p_consolidation_entity_id => l_parent_entity_id,
677 p_child_entity_id => l_child_entity_id,
678 p_message_name => 'GCS_PCT_OWNERSHIP_ALTERED',
679 p_pre_relationship_id => l_pre_cons_relationship_id,
680 p_post_relationship_id => l_post_cons_relationship_id,
681 p_date_token => l_trx_date);
682
683 END IF;
684
685 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
686 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
687 g_api || '.HIERARCHY_ALTERED',
688 'Original Curr Trtmnt : ' || l_orig_curr_trtmnt_id);
689 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
690 g_api || '.HIERARCHY_ALTERED',
691 'New Curr Trtmnt : ' || l_new_curr_trtmnt_id);
692 END IF;
693
694 IF (l_orig_curr_trtmnt_id <> l_new_curr_trtmnt_id) THEN
695 l_impact_occurred := TRUE;
696 insert_impact_analysis(p_run_name => l_run_name,
697 p_consolidation_entity_id => l_parent_entity_id,
698 p_child_entity_id => l_child_entity_id,
699 p_message_name => 'GCS_CURR_TREATMENT_ALTERED',
700 p_pre_relationship_id => l_pre_cons_relationship_id,
701 p_post_relationship_id => l_post_cons_relationship_id,
702 p_date_token => l_trx_date);
703
704 SELECT gcs_orig.curr_treatment_name, gcs_new.curr_treatment_name
705 INTO l_orig_treatment, l_new_treatment
706 FROM gcs_curr_treatments_tl gcs_orig,
707 gcs_curr_treatments_tl gcs_new
708 WHERE gcs_orig.curr_treatment_id = l_orig_curr_trtmnt_id
709 AND gcs_new.curr_treatment_id = l_new_curr_trtmnt_id
710 AND gcs_orig.language = USERENV('LANG')
711 AND gcs_new.language = USERENV('LANG');
712
713 END IF;
714
718 --Update IMPACTED_FLAG rather than status code
715 END IF;
716
717 IF (l_impact_occurred) THEN
719 UPDATE gcs_cons_eng_runs
720 SET impacted_flag = 'Y'
721 WHERE run_name = l_run_name
722 AND run_entity_id = l_parent_entity_id
723 AND most_recent_flag = 'Y';
724
725 rollup_impact(p_hierarchy_id => l_hierarchy_id,
726 p_consolidation_entity_id => l_parent_entity_id,
727 --Bugfix 3848844 : Added Cal Period ID as a parameter
728 p_cal_period_id => l_cal_period_id);
729
730 --Bugfix 4179379 : Send notifications via workflow
731 gcs_eng_cp_utility_pkg.submit_xml_ntf_program(p_execution_type => 'IMPACT_ENGINE',
732 p_run_name => l_run_name,
733 p_cons_entity_id => l_parent_entity_id,
734 p_category_code => 'NOT_APPLICABLE');
735
736 END IF;
737
738 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
739 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
740 g_api || '.HIERARCHY_ALTERED',
741 '<<Exit>>');
742 END IF;
743 EXCEPTION
744 WHEN NO_DATA_FOUND THEN
745 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
746 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
747 g_api || '.HIERARCHY_ALTERED',
748 'No Data Found');
749 END IF;
750 RETURN 'SUCCESS'; -- NO_IMPACT_OCCURRED
751 END;
752 RETURN 'SUCCESS';
753 EXCEPTION
754 WHEN OTHERS THEN
755 RETURN 'SUCCESS';
756 END hierarchy_altered;
757
758 FUNCTION data_sub_load_executed(p_subscription_guid in raw,
759 p_event in out nocopy wf_event_t)
760 RETURN VARCHAR2
761
762 IS
763 l_parameter_list wf_parameter_list_t;
764 l_data_sub_info gcs_data_sub_dtls%ROWTYPE;
765 l_load_id NUMBER(15);
766 l_message_name VARCHAR2(30);
767 l_entry_id NUMBER(15);
768 l_stat_entry_id NUMBER(15);
769 l_prop_entry_id NUMBER(15);
770 l_stat_prop_entry_id NUMBER(15);
771 l_currency_type_code VARCHAR2(30);
772 l_ledger_id NUMBER(15);
773 l_errbuf VARCHAR2(200);
774 l_retcode VARCHAR2(200);
775
776 l_cal_period_end_date_attr NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
777 .attribute_id;
778 l_cal_period_end_date_ver NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
779 .version_id;
780 l_func_currency_attr NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-LEDGER_FUNCTIONAL_CRNCY_CODE')
781 .attribute_id;
782 l_func_currency_ver NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-LEDGER_FUNCTIONAL_CRNCY_CODE')
783 .version_id;
784
785 CURSOR c_impacted_runs(p_cal_period_id IN NUMBER, p_currency_code IN VARCHAR2, p_entity_id IN NUMBER, p_balance_type_code IN VARCHAR2) IS
786
787 SELECT gcer.run_name,
788 gcer.run_entity_id,
789 gcer.hierarchy_id,
790 gcr.cons_relationship_id
791 FROM gcs_cons_eng_runs gcer,
792 gcs_cons_relationships gcr,
793 fem_cal_periods_attr fcpa,
797 gcs_hierarchies_b ghb
794 gcs_entity_cons_attrs geca,
795 gcs_cal_period_maps_gt gcpmt,
796 fem_cal_periods_b fcpb,
798 WHERE gcer.most_recent_flag = 'Y'
799 AND gcer.balance_type_code = p_balance_type_code
800 AND gcer.cal_period_id = gcpmt.target_cal_period_id
801 AND gcer.hierarchy_id = gcr.hierarchy_id
802 AND gcer.run_entity_id = gcr.parent_entity_id
803 AND gcr.child_entity_id = p_entity_id
804 AND gcr.dominant_parent_flag = 'Y'
805 AND geca.hierarchy_id = gcr.hierarchy_id
806 AND gcer.hierarchy_id = ghb.hierarchy_id
807 AND gcpmt.source_cal_period_id = p_cal_period_id
808 AND gcpmt.target_cal_period_id = fcpb.cal_period_id
809 AND ghb.calendar_id = fcpb.calendar_id
810 AND ghb.dimension_group_id = fcpb.dimension_group_id
811 AND geca.entity_id = gcr.child_entity_id
812 AND geca.currency_code = p_currency_code
813 AND fcpa.cal_period_id = fcpb.cal_period_id
814 AND fcpa.attribute_id = l_cal_period_end_date_attr
815 AND fcpa.version_id = l_cal_period_end_date_ver
816 AND fcpa.date_assign_value BETWEEN gcr.start_date AND
817 NVL(gcr.end_date, fcpa.date_assign_value);
818
819 BEGIN
820
821 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
822 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
823 g_api || '.DATA_SUB_LOAD_EXECUTED.begin',
824 '<<Enter>>');
825 END IF;
826
827 l_parameter_list := p_event.getParameterList();
828 l_load_id := TO_NUMBER(WF_EVENT.getValueForParameter('LOAD_ID',
829 l_parameter_list));
830 l_ledger_id := TO_NUMBER(WF_EVENT.getValueForParameter('LEDGER_ID',
831 l_parameter_list));
832
833 SELECT gdsd.entity_id,
834 gdsd.cal_period_id,
835 NVL(gdsd.currency_code, fla.dim_attribute_varchar_member),
836 gdsd.balance_type_code,
837 gdsd.load_method_code,
838 gdsd.currency_type_code,
839 gdsd.load_id,
840 DECODE(status_code,
841 -- Bug Fix: 5647099
842 'UNDONE',
843 'GCS_PRISTINE_DATA_UNDO_LOAD',
844 DECODE(load_method_code,
845 'INITIAL_LOAD',
846 'GCS_PRISTINE_DATA_FULL_LOAD',
847 'GCS_PRISTINE_DATA_INC_LOAD')),
848 DECODE(gdsd.currency_code,
849 NULL,
850 'ENTERED',
851 fla.dim_attribute_varchar_member,
852 'ENTERED',
853 'TRANSLATED')
854 INTO l_data_sub_info.entity_id,
855 l_data_sub_info.cal_period_id,
856 l_data_sub_info.currency_code,
857 l_data_sub_info.balance_type_code,
858 l_data_sub_info.load_method_code,
859 l_data_sub_info.currency_type_code,
860 l_data_sub_info.load_id,
861 l_message_name,
862 l_currency_type_code
863 FROM gcs_data_sub_dtls gdsd, fem_ledgers_attr fla
864 WHERE gdsd.load_id = l_load_id
865 AND fla.ledger_id = l_ledger_id
866 AND fla.attribute_id = l_func_currency_attr
867 AND fla.version_id = l_func_currency_ver;
868
869 --Explode into calendar period maps table gcs_cal_period_maps_gt
870 gcs_utility_pkg.populate_calendar_map_details(l_data_sub_info.cal_period_id,
871 'Y',
872 'N');
873
874 FOR v_impacted_runs IN c_impacted_runs(l_data_sub_info.cal_period_id,
875 l_data_sub_info.currency_code,
876 l_data_sub_info.entity_id,
877 l_data_sub_info.balance_type_code) LOOP
878
879 insert_impact_analysis(p_run_name => v_impacted_runs.run_name,
880 p_consolidation_entity_id => v_impacted_runs.run_entity_id,
881 p_child_entity_id => l_data_sub_info.entity_id,
882 p_message_name => l_message_name,
883 p_date_token => sysdate,
884 p_load_id => l_data_sub_info.load_id);
885
886 -- Bugfix 4322320: Removing call to incremental data prep. Will add back after controlled release
887 -- gcs_data_prep_pkg.gcs_incremental_data_prep(
888 -- x_errbuf => l_errbuf,
889 -- x_retcode => l_retcode,
890 -- x_entry_id => l_entry_id,
891 -- x_stat_entry_id => l_stat_entry_id,
892 -- x_prop_entry_id => l_prop_entry_id,
893 -- x_stat_prop_entry_id => l_stat_prop_entry_id,
894 -- p_source_cal_period_id => l_data_sub_info.cal_period_id,
895 -- p_balance_type_code => l_data_sub_info.balance_type_code,
896 -- p_ledger_id => l_ledger_id,
897 -- p_currency_code => l_data_sub_info.currency_code,
898 -- p_dataset_code => l_dataset_code,
899 -- p_run_name => v_impacted_runs.run_name,
903 -- UPDATE gcs_cons_impact_analyses
900 -- p_cons_relationship_id => v_impacted_runs.cons_relationship_id,
901 -- p_currency_type_code => l_currency_type_code);
902
904 -- SET entry_id = l_entry_id,
905 -- stat_entry_id = l_stat_entry_id,
906 -- pre_prop_entry_id = l_prop_entry_id,
907 -- pre_prop_stat_entry_id = l_stat_prop_entry_id
908 -- WHERE load_id = l_data_sub_info.load_id;
909 --
910 -- IF (l_entry_id IS NOT NULL) THEN
911 -- -- Call Entries XML generation API
912 -- gcs_xml_gen_pkg.generate_entry_xml( p_entry_id => l_entry_id,
913 -- p_category_code => 'DATAPREPARATION',
914 -- p_cons_rule_flag =>'N');
915 -- END IF;
916
917 -- IF (l_stat_entry_id IS NOT NULL) THEN
918 -- -- Call Entries XML generation API
919 -- gcs_xml_gen_pkg.generate_entry_xml( p_entry_id => l_stat_entry_id,
920 -- p_category_code => 'DATAPREPARATION',
921 -- p_cons_rule_flag =>'N');
922 -- END IF;
923
924 UPDATE gcs_cons_eng_runs
925 SET impacted_flag = 'Y'
926 WHERE run_name = v_impacted_runs.run_name
927 AND run_entity_id = v_impacted_runs.run_entity_id
928 AND most_recent_flag = 'Y';
929
930 rollup_impact(p_hierarchy_id => v_impacted_runs.hierarchy_id,
931 p_consolidation_entity_id => v_impacted_runs.run_entity_id,
932 p_cal_period_id => l_data_sub_info.cal_period_id);
933
934 --Bugfix 4179379 : Send notifications via workflow
935 gcs_eng_cp_utility_pkg.submit_xml_ntf_program(p_execution_type => 'IMPACT_ENGINE',
936 p_run_name => v_impacted_runs.run_name,
937 p_cons_entity_id => v_impacted_runs.run_entity_id,
938 p_category_code => 'NOT_APPLICABLE',
939 p_load_id => l_load_id);
940
941 END LOOP;
942
943 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
944 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
945 g_api || '.DATA_SUB_LOAD_EXECUTED.end',
946 '<<Exit>>');
947 END IF;
948
949 RETURN 'SUCCESS';
950 EXCEPTION
951 WHEN OTHERS THEN
952 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
953 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
954 g_api || '.DATA_SUB_LOAD_EXECUTED',
955 SQLERRM);
956 END IF;
957 RETURN 'SUCCESS';
958 END data_sub_load_executed;
959
960 FUNCTION acqdisp_altered(p_subscription_guid in raw,
961 p_event in out nocopy wf_event_t)
962 RETURN VARCHAR2
963
964 IS
965
966 l_entry_id NUMBER(15);
967 l_orig_entry_id NUMBER(15);
968 l_start_cal_period_id NUMBER;
969 l_end_cal_period_id NUMBER;
970 l_hierarchy_id NUMBER;
971 l_entity_id NUMBER;
972 l_bal_type_code VARCHAR2(30);
973 l_change_type_code VARCHAR2(30);
977 l_end_cp_end_date DATE;
974 l_parameter_list wf_parameter_list_t;
975 l_run_name VARCHAR2(80);
976 l_start_cp_end_date DATE;
978 l_entity_type_code VARCHAR2(1);
979 l_cons_entity_id NUMBER;
980 l_cal_period_attr NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
981 .attribute_id;
982 l_cal_period_version NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
983 .version_id;
984
985 BEGIN
986
987 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
988 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
989 g_api || '.ACQDISP_ALTERED.begin',
990 '<<Enter>>');
991 END IF;
992
993 l_parameter_list := p_event.getParameterList();
994 l_change_type_code := WF_EVENT.getValueForParameter('CHANGE_TYPE_CODE',
995 l_parameter_list);
996 l_entry_id := TO_NUMBER(WF_EVENT.getValueForParameter('ENTRY_ID',
997 l_parameter_list));
998 l_orig_entry_id := TO_NUMBER(WF_EVENT.getValueForParameter('ORIG_ENTRY_ID',
999 l_parameter_list));
1000
1001 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1002 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1003 g_api || '.ACQDISP_ALTERED',
1004 'Change Type Code : ' || l_change_type_code);
1005 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1006 g_api || '.ACQDISP_ALTERED',
1007 'Entry ID : ' || l_entry_id);
1008 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1009 g_api || '.ACQDISP_ALTERED',
1010 'Original Entry ID : ' || l_orig_entry_id);
1011 END IF;
1012
1013 SELECT geh.start_cal_period_id,
1014 geh.end_cal_period_id,
1015 geh.hierarchy_id,
1016 geh.entity_id,
1017 geh.balance_type_code,
1018 fcpa_start.date_assign_value
1019 INTO l_start_cal_period_id,
1020 l_end_cal_period_id,
1021 l_hierarchy_id,
1022 l_entity_id,
1023 l_bal_type_code,
1024 l_start_cp_end_date
1025 FROM gcs_entry_headers geh, fem_cal_periods_attr fcpa_start
1026 WHERE geh.entry_id = l_entry_id
1027 AND geh.start_cal_period_id = fcpa_start.cal_period_id
1028 AND fcpa_start.attribute_id = l_cal_period_attr
1029 AND fcpa_start.version_id = l_cal_period_version;
1030
1031 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1032 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1033 g_api || '.ACQDISP_ALTERED',
1034 'End Date Value : ' || l_start_cp_end_date);
1035 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1036 g_api || '.ACQDISP_ALTERED',
1037 'Entity ID : ' || l_entity_id);
1038 END IF;
1039
1040 SELECT parent_entity_id
1041 INTO l_cons_entity_id
1042 FROM gcs_cons_relationships
1043 WHERE hierarchy_id = l_hierarchy_id
1044 AND child_entity_id = l_entity_id
1045 AND dominant_parent_flag = 'Y'
1046 AND l_start_cp_end_date BETWEEN start_date AND
1047 NVL(end_date, l_start_cp_end_date);
1048
1049 -- Bugfix 4332123 : Resolve issue with the calendar period
1050
1051 BEGIN
1052 SELECT run_name
1053 INTO l_run_name
1054 FROM gcs_cons_eng_runs
1055 WHERE most_recent_flag = 'Y'
1056 AND hierarchy_id = l_hierarchy_id
1057 AND balance_type_code = l_bal_type_code
1058 AND cal_period_id = l_start_cal_period_id
1059 AND run_entity_id = l_cons_entity_id;
1060 EXCEPTION
1061 WHEN NO_DATA_FOUND THEN
1062 --Check if change happened in earlier period
1063 SELECT gcer.run_name, gcer.cal_period_id
1064 INTO l_run_name, l_start_cal_period_id
1065 FROM gcs_cons_eng_runs gcer
1066 WHERE gcer.run_entity_id = l_cons_entity_id
1067 AND gcer.hierarchy_id = l_hierarchy_id
1068 AND gcer.most_recent_flag = 'Y'
1069 AND gcer.balance_type_code = l_bal_type_code
1070 AND gcer.cal_period_id =
1071 (SELECT min(gcer_inner.cal_period_id)
1072 FROM gcs_cons_eng_runs gcer_inner
1073 WHERE gcer_inner.run_entity_id = l_cons_entity_id
1074 AND gcer_inner.hierarchy_id = l_hierarchy_id
1075 AND gcer_inner.most_recent_flag = 'Y'
1079
1076 AND gcer_inner.balance_type_code = l_bal_type_code
1077 AND gcer_inner.cal_period_id > l_start_cal_period_id);
1078 END;
1080 IF (l_change_type_code = 'NEW_ACQDISP') THEN
1081 insert_impact_analysis(p_run_name => l_run_name,
1082 p_consolidation_entity_id => l_cons_entity_id,
1083 p_child_entity_id => l_entity_id,
1084 p_message_name => 'GCS_ACQDISP_CREATED',
1085 p_pre_relationship_id => null,
1086 p_post_relationship_id => null,
1087 p_date_token => sysdate,
1088 p_entry_id => l_entry_id);
1089 ELSIF (l_change_type_code = 'ACQDISP_MODIFIED') THEN
1090 insert_impact_analysis(p_run_name => l_run_name,
1091 p_consolidation_entity_id => l_cons_entity_id,
1092 p_child_entity_id => l_entity_id,
1093 p_message_name => 'GCS_ACQDISP_MODIFIED',
1094 p_pre_relationship_id => null,
1095 p_post_relationship_id => null,
1096 p_date_token => sysdate,
1097 p_entry_id => l_entry_id,
1098 p_orig_entry_id => l_orig_entry_id);
1099 ELSIF (l_change_type_code = 'ACQDISP_UNDONE') THEN
1100 insert_impact_analysis(p_run_name => l_run_name,
1101 p_consolidation_entity_id => l_cons_entity_id,
1102 p_child_entity_id => l_entity_id,
1103 p_message_name => 'GCS_ACQDISP_UNDO',
1104 p_pre_relationship_id => null,
1105 p_post_relationship_id => null,
1106 p_date_token => sysdate,
1107 p_entry_id => l_entry_id,
1108 p_orig_entry_id => l_orig_entry_id);
1109 END IF;
1110
1111 -- Bugfix 3848844 : Added update for impact of the gcs_cons_eng_runs
1112
1113 UPDATE gcs_cons_eng_runs
1114 SET impacted_flag = 'Y'
1115 WHERE run_name = l_run_name
1116 AND run_entity_id = l_cons_entity_id
1117 AND most_recent_flag = 'Y';
1118
1119 rollup_impact(p_hierarchy_id => l_hierarchy_id,
1120 p_consolidation_entity_id => l_cons_entity_id,
1121 p_cal_period_id => l_start_cal_period_id);
1122
1123 --Bugfix 4179379 : Send notifications via workflow
1124 gcs_eng_cp_utility_pkg.submit_xml_ntf_program(p_execution_type => 'IMPACT_ENGINE',
1125 p_run_name => l_run_name,
1126 p_cons_entity_id => l_cons_entity_id,
1127 p_category_code => 'NOT_APPLICABLE');
1128
1129 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1130 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1131 g_api || '.ACQDISP_ALTERED.end',
1132 '<<Exit>>');
1133 END IF;
1134
1135 RETURN 'SUCCESS';
1136
1137 EXCEPTION
1138 WHEN NO_DATA_FOUND THEN
1139 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
1140 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
1141 g_api || '.ACQDISP_ALTERED',
1142 'Error occurred : ' || SQLERRM);
1143 END IF;
1144 RETURN 'SUCCESS';
1145 END acqdisp_altered;
1146
1147 FUNCTION adjustment_altered(p_subscription_guid in raw,
1148 p_event in out nocopy wf_event_t)
1149 RETURN VARCHAR2
1150
1151 IS
1152 l_entry_id NUMBER(15);
1153 l_orig_entry_id NUMBER(15);
1154 l_start_cal_period_id NUMBER;
1155 l_end_cal_period_id NUMBER;
1156 l_hierarchy_id NUMBER;
1157 l_entity_id NUMBER;
1158 l_bal_type_code VARCHAR2(30);
1159 l_parameter_list wf_parameter_list_t;
1160 l_run_name VARCHAR2(80);
1161 l_start_cp_end_date DATE;
1162 l_end_cp_end_date DATE;
1163 l_entity_type_code VARCHAR2(1);
1164 l_cons_entity_id NUMBER;
1165 l_email VARCHAR2(200);
1166
1167 BEGIN
1168
1169 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1170 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1171 g_api || '.ADJUSTMENT_ALTERED.begin',
1172 '<<Enter>>');
1173 END IF;
1174
1175 l_parameter_list := p_event.getParameterList();
1176
1177 l_entry_id := TO_NUMBER(WF_EVENT.getValueForParameter('ENTRY_ID',
1178 l_parameter_list));
1179 l_orig_entry_id := TO_NUMBER(WF_EVENT.getValueForParameter('ORIG_ENTRY_ID',
1180 l_parameter_list));
1181
1182 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1183 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1184 g_api || '.ADJUSTMENT_ALTERED',
1188 'Original Entry ID : ' || l_orig_entry_id);
1185 'Entry ID : ' || l_entry_id);
1186 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1187 g_api || '.ADJUSTMENT_ALTERED',
1189 END IF;
1190
1191 SELECT geh.start_cal_period_id,
1192 geh.end_cal_period_id,
1193 geh.hierarchy_id,
1194 geh.entity_id,
1195 geh.balance_type_code,
1196 fcpa_start.date_assign_value
1197 INTO l_start_cal_period_id,
1198 l_end_cal_period_id,
1199 l_hierarchy_id,
1200 l_entity_id,
1201 l_bal_type_code,
1202 l_start_cp_end_date
1203 FROM gcs_entry_headers geh, fem_cal_periods_attr fcpa_start
1204 WHERE geh.entry_id = l_entry_id
1205 AND geh.start_cal_period_id = fcpa_start.cal_period_id
1206 AND fcpa_start.attribute_id =
1207 gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
1208 .attribute_id
1209 AND fcpa_start.version_id =
1210 gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
1211 .version_id;
1212
1213 SELECT parent_entity_id
1214 INTO l_cons_entity_id
1215 FROM gcs_cons_relationships
1216 WHERE hierarchy_id = l_hierarchy_id
1217 AND child_entity_id = l_entity_id
1218 AND dominant_parent_flag = 'Y'
1219 AND l_start_cp_end_date BETWEEN start_date AND
1220 NVL(end_date, l_start_cp_end_date);
1221
1222 SELECT run_name
1223 INTO l_run_name
1224 FROM gcs_cons_eng_runs
1225 WHERE most_recent_flag = 'Y'
1226 AND hierarchy_id = l_hierarchy_id
1227 AND cal_period_id = l_start_cal_period_id
1228 AND balance_type_code = l_bal_type_code
1229 AND run_entity_id = l_cons_entity_id;
1230
1231 IF (l_orig_entry_id IS NULL) THEN
1232
1233 insert_impact_analysis(p_run_name => l_run_name,
1234 p_consolidation_entity_id => l_cons_entity_id,
1235 p_child_entity_id => l_entity_id,
1236 p_message_name => 'GCS_ADJUSTMENT_CREATED',
1237 p_pre_relationship_id => null,
1238 p_post_relationship_id => null,
1239 p_date_token => sysdate,
1240 p_entry_id => l_entry_id);
1241
1242 ELSIF (l_entry_id <> l_orig_entry_id) THEN
1243
1244 insert_impact_analysis(p_run_name => l_run_name,
1245 p_consolidation_entity_id => l_cons_entity_id,
1246 p_child_entity_id => l_entity_id,
1247 p_message_name => 'GCS_ADJUSTMENT_MODIFIED',
1248 p_pre_relationship_id => null,
1249 p_post_relationship_id => null,
1250 p_date_token => sysdate,
1251 p_entry_id => l_entry_id,
1252 p_orig_entry_id => l_orig_entry_id);
1253
1254 END IF;
1255
1256 UPDATE gcs_cons_eng_runs
1257 SET impacted_flag = 'Y'
1258 WHERE run_name = l_run_name
1259 AND run_entity_id = l_cons_entity_id
1260 AND most_recent_flag = 'Y';
1261
1262 rollup_impact(p_hierarchy_id => l_hierarchy_id,
1263 p_consolidation_entity_id => l_cons_entity_id,
1264 p_cal_period_id => l_start_cal_period_id);
1265
1266 --Bugfix 4179379 : Send notifications via workflow
1267 gcs_eng_cp_utility_pkg.submit_xml_ntf_program(p_execution_type => 'IMPACT_ENGINE',
1268 p_run_name => l_run_name,
1269 p_cons_entity_id => l_cons_entity_id,
1270 p_category_code => 'NOT_APPLICABLE',
1271 p_entry_id => l_entry_id);
1272
1273 RETURN 'SUCCESS';
1274
1278 END adjustment_altered;
1275 EXCEPTION
1276 WHEN NO_DATA_FOUND THEN
1277 RETURN 'SUCCESS';
1279
1280 FUNCTION daily_rates_altered(p_subscription_guid in raw,
1281 p_event in out nocopy wf_event_t)
1282 RETURN VARCHAR2
1283
1284 IS
1285 l_parameter_list wf_parameter_list_t;
1286
1287 l_from_currency VARCHAR2(30);
1288 l_to_currency VARCHAR2(30);
1289 l_from_conversion_date DATE;
1290 l_to_conversion_date DATE;
1291 l_conversion_type VARCHAR2(30);
1292
1293 l_cp_end_date_attr_id NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
1294 .attribute_id;
1295 l_cp_end_date_version_id NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
1296 .version_id;
1297
1298 CURSOR c_impacted_runs(p_from_currency IN VARCHAR2, p_to_currency IN VARCHAR2, p_from_conv_date IN DATE, p_to_conv_date IN DATE, p_conversion_type IN VARCHAR2) IS
1299 SELECT gcer.run_name,
1300 gcer.run_entity_id,
1301 gcerd.child_entity_id,
1302 gcer.hierarchy_id,
1303 gcer.cal_period_id
1304 FROM gcs_cons_eng_runs gcer,
1305 gcs_cons_eng_run_dtls gcerd,
1306 gcs_curr_treatments_b gctb,
1307 fem_cal_periods_b fcpb,
1308 fem_cal_periods_attr fcpa_end,
1309 gcs_cons_relationships gcr,
1310 gcs_entity_cons_attrs geca_parent,
1311 gcs_entity_cons_attrs geca_child
1312 WHERE gcer.cal_period_id = fcpb.cal_period_id
1313 AND fcpb.cal_period_id = fcpa_end.cal_period_id
1317 p_to_conv_date
1314 AND fcpa_end.attribute_id = l_cp_end_date_attr_id
1315 AND fcpa_end.version_id = l_cp_end_date_version_id
1316 AND fcpa_end.date_assign_value BETWEEN p_from_conv_date AND
1318 AND gcer.most_recent_flag = 'Y'
1319 AND gcer.run_name = gcerd.run_name
1320 AND gcer.run_entity_id = gcerd.consolidation_entity_id
1321 AND gcerd.category_code = 'TRANSLATION'
1322 AND gcerd.cons_relationship_id = gcr.cons_relationship_id
1323 AND gcr.curr_treatment_id = gctb.curr_treatment_id
1324 AND p_conversion_type IN
1325 (gctb.ending_rate_type, gctb.average_rate_type)
1326 AND gcr.parent_entity_id = geca_parent.entity_id
1327 AND gcr.hierarchy_id = geca_parent.hierarchy_id
1328 AND gcr.dominant_parent_flag = 'Y'
1329 AND gcr.child_entity_id = geca_child.entity_id
1330 AND gcr.hierarchy_id = geca_child.hierarchy_id
1331 AND geca_parent.currency_code = p_to_currency
1332 AND geca_child.currency_code = p_from_currency;
1333
1334 BEGIN
1335
1336 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1337 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1338 g_api || '.DAILY_RATES_ALTERED.begin',
1339 '<<Enter>>');
1340 END IF;
1341
1342 l_parameter_list := p_event.getParameterList();
1343
1344 l_from_currency := WF_EVENT.getValueForParameter('FROM_CURRENCY',
1345 l_parameter_list);
1346 l_to_currency := WF_EVENT.getValueForParameter('TO_CURRENCY',
1347 l_parameter_list);
1348 l_from_conversion_date := TO_DATE(WF_EVENT.getValueForParameter('FROM_CONVERSION_DATE',
1349 l_parameter_list),
1350 'YYYY/MM/DD');
1351 l_to_conversion_date := TO_DATE(NVL(WF_EVENT.getValueForParameter('TO_CONVERSION_DATE',
1352 l_parameter_list),
1353 TO_CHAR(l_from_conversion_date,
1354 'YYYY/MM/DD')),
1355 'YYYY/MM/DD');
1356 l_conversion_type := WF_EVENT.getValueForParameter('CONVERSION_TYPE',
1357 l_parameter_list);
1358
1359 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1360 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1361 g_api || '.DAILY_RATES_ALTERED',
1362 'From Currency : ' || l_from_currency);
1363 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1364 g_api || '.DAILY_RATES_ALTERED',
1365 'To Currency : ' || l_to_currency);
1366 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1367 g_api || '.DAILY_RATES_ALTERED',
1368 'From Date : ' || l_from_conversion_date);
1369 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1370 g_api || '.DAILY_RATES_ALTERED',
1371 'To Date : ' || l_to_conversion_date);
1372 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1373 g_api || '.DAILY_RATES_ALTERED',
1374 'Conversion Type : ' || l_conversion_type);
1375 END IF;
1376
1377 FOR v_impacted_runs IN c_impacted_runs(l_from_currency,
1378 l_to_currency,
1379 l_from_conversion_date,
1380 l_to_conversion_date,
1381 l_conversion_type) LOOP
1382
1383 insert_impact_analysis(p_run_name => v_impacted_runs.run_name,
1384 p_consolidation_entity_id => v_impacted_runs.run_entity_id,
1385 p_child_entity_id => v_impacted_runs.child_entity_id,
1386 p_message_name => 'GCS_TRANSLATION_RATES_ALTERED',
1387 p_date_token => sysdate);
1388
1389 UPDATE gcs_cons_eng_runs
1390 SET impacted_flag = 'Y'
1391 WHERE run_name = v_impacted_runs.run_name
1392 ANd run_entity_id = v_impacted_runs.run_entity_id;
1393
1394 rollup_impact(p_hierarchy_id => v_impacted_runs.hierarchy_id,
1395 p_consolidation_entity_id => v_impacted_runs.run_entity_id,
1396 p_cal_period_id => v_impacted_runs.cal_period_id);
1397
1398 --Bugfix 4179379 : Send notifications via workflow
1399 gcs_eng_cp_utility_pkg.submit_xml_ntf_program(p_execution_type => 'IMPACT_ENGINE',
1400 p_run_name => v_impacted_runs.run_name,
1401 p_cons_entity_id => v_impacted_runs.run_entity_id,
1402 p_category_code => 'NOT_APPLICABLE');
1403
1404 END LOOP;
1405
1406 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1407 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1408 g_api || '.DAILY_RATES_ALTERED.end',
1409 '<<Exit>>');
1410 END IF;
1411
1412 RETURN 'SUCCESS';
1413 EXCEPTION
1414 WHEN OTHERS THEN
1415 RETURN 'SUCCESS';
1416 END daily_rates_altered;
1417
1418 FUNCTION historical_rates_altered(p_subscription_guid in raw,
1419 p_event in out nocopy wf_event_t)
1420 RETURN VARCHAR2
1421
1422 IS
1423
1424 l_parameter_list wf_parameter_list_t;
1425
1426 l_cal_period_id NUMBER;
1427 l_entity_id NUMBER(15);
1428 l_hierarchy_id NUMBER(15);
1432 BEGIN
1429 l_run_name VARCHAR2(240);
1430 l_run_entity_id NUMBER(15);
1431
1433
1434 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1435 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1436 g_api || '.HISTORICAL_RATES_ALTERED.begin',
1437 '<<Enter>>');
1438 END IF;
1439
1440 l_parameter_list := p_event.getParameterList();
1441
1442 l_cal_period_id := TO_NUMBER(WF_EVENT.getValueForParameter('PERIOD_ID',
1443 l_parameter_list));
1444 l_entity_id := TO_NUMBER(WF_EVENT.getValueForParameter('ENTITY_ID',
1445 l_parameter_list));
1446 l_hierarchy_id := TO_NUMBER(WF_EVENT.getValueForParameter('HIERARCHY_ID',
1447 l_parameter_list));
1448
1449 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1450 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1451 g_api || '.HISTORICAL_RATES_ALTERED',
1452 'Calendar Period : ' || l_cal_period_id);
1453 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1454 g_api || '.HISTORICAL_RATES_ALTERED',
1455 'Entity : ' || l_entity_id);
1456 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1457 g_api || '.HISTORICAL_RATES_ALTERED',
1458 'Hierarchy : ' || l_hierarchy_id);
1459 END IF;
1460
1461 SELECT gcer.run_name, gcer.run_entity_id
1462 INTO l_run_name, l_run_entity_id
1463 FROM gcs_cons_eng_runs gcer, gcs_cons_eng_run_dtls gcerd
1464 WHERE gcer.hierarchy_id = l_hierarchy_id
1465 AND gcer.cal_period_id = l_cal_period_id
1466 AND gcer.most_recent_flag = 'Y'
1467 AND gcer.run_entity_id = gcerd.consolidation_entity_id
1468 AND gcer.run_name = gcerd.run_name
1469 AND gcerd.category_code = 'TRANSLATION'
1470 AND gcerd.child_entity_id = l_entity_id;
1471
1472 insert_impact_analysis(p_run_name => l_run_name,
1473 p_consolidation_entity_id => l_run_entity_id,
1474 p_child_entity_id => l_entity_id,
1475 p_message_name => 'GCS_HISTORICAL_RATES_ALTERED',
1476 p_date_token => sysdate);
1477
1478 UPDATE gcs_cons_eng_runs
1479 SET impacted_flag = 'Y'
1480 WHERE run_name = l_run_name
1481 ANd run_entity_id = l_run_entity_id;
1482
1483 rollup_impact(p_hierarchy_id => l_hierarchy_id,
1484 p_consolidation_entity_id => l_run_entity_id,
1485 p_cal_period_id => l_cal_period_id);
1486
1487 --Bugfix 4179379 : Send notifications via workflow
1488 gcs_eng_cp_utility_pkg.submit_xml_ntf_program(p_execution_type => 'IMPACT_ENGINE',
1489 p_run_name => l_run_name,
1490 p_cons_entity_id => l_run_entity_id,
1491 p_category_code => 'NOT_APPLICABLE');
1492
1493 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1494 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1495 g_api || '.HISTORICAL_RATES_ALTERED.end',
1496 '<<Exit>>');
1497 END IF;
1498
1499 RETURN 'SUCCESS';
1500
1501 EXCEPTION
1502 WHEN OTHERS THEN
1503 RETURN 'SUCCESS';
1504 END historical_rates_altered;
1505
1506 PROCEDURE consolidation_completed(p_run_name IN VARCHAR2,
1507 p_run_entity_id IN NUMBER,
1508 p_cal_period_id IN NUMBER,
1509 p_cal_period_end_date IN DATE,
1510 p_hierarchy_id IN NUMBER,
1511 p_balance_type_code IN VARCHAR2)
1512
1513 IS
1514 PRAGMA AUTONOMOUS_TRANSACTION;
1515
1516 l_parent_entity_id NUMBER;
1517 l_run_parent_entity_id NUMBER;
1518 l_run_name VARCHAR2(240);
1519 l_cal_period_info gcs_utility_pkg.r_cal_period_info;
1520
1521 BEGIN
1522
1523 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1524 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1525 g_api || '.CONSOLIDATION_COMPLETED.begin',
1526 '<<Enter>>');
1527 END IF;
1528
1529 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1530 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1531 g_api || '.CONSOLIDATION_COMPLETED',
1532 'Run Name : ' || p_run_name);
1533 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1534 g_api || '.CONSOLIDATION_COMPLETED',
1535 'Run Entity : ' || p_run_entity_id);
1536 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1537 g_api || '.CONSOLIDATION_COMPLETED',
1538 'Cal Period : ' || p_cal_period_id);
1539 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1540 g_api || '.CONSOLIDATION_COMPLETED',
1541 'Period End Date : ' || p_cal_period_end_date);
1542 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1543 g_api || '.CONSOLIDATION_COMPLETED',
1544 'Hierarchy : ' || p_hierarchy_id);
1545 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1546 g_api || '.CONSOLIDATION_COMPLETED',
1547 'Balance Type : ' || p_balance_type_code);
1548 END IF;
1549
1550 --Check if top parent needs to be re-consolidated
1551 SELECT parent_entity_id
1552 INTO l_run_parent_entity_id
1556
1553 FROM gcs_cons_eng_runs
1554 WHERE run_name = p_run_name
1555 AND run_entity_id = p_run_entity_id;
1557 IF (l_run_parent_entity_id = -1) THEN
1558 BEGIN
1559 SELECT parent_entity_id
1560 INTO l_parent_entity_id
1561 FROM gcs_cons_relationships
1562 WHERE hierarchy_id = p_hierarchy_id
1563 AND child_entity_id = p_run_entity_id
1564 AND dominant_parent_flag = 'Y'
1565 AND p_cal_period_end_date BETWEEN start_date AND
1566 NVL(end_date, p_cal_period_end_date);
1567
1568 SELECT run_name
1569 INTO l_run_name
1570 FROM gcs_cons_eng_runs
1571 WHERE run_entity_id = l_parent_entity_id
1572 AND cal_period_id = p_cal_period_id
1573 AND hierarchy_id = p_hierarchy_id
1574 AND balance_type_code = p_balance_type_code
1575 AND most_recent_flag = 'Y';
1576 EXCEPTION
1577 WHEN OTHERS THEN
1578 l_parent_entity_id := -1;
1579 END;
1580
1581 IF ((l_parent_entity_id <> -1) AND (l_run_name IS NOT NULL)) THEN
1582 --Impact has occurred for parent level entity
1583 insert_impact_analysis(p_run_name => l_run_name,
1584 p_consolidation_entity_id => l_parent_entity_id,
1585 p_child_entity_id => p_run_entity_id,
1586 p_message_name => 'GCS_SUB_RECONSOLIDATED',
1587 p_date_token => sysdate);
1588
1589 UPDATE gcs_cons_eng_runs
1590 SET impacted_flag = 'Y'
1591 WHERE run_name = l_run_name
1592 AND run_entity_id = l_parent_entity_id;
1593
1594 rollup_impact(p_hierarchy_id => p_hierarchy_id,
1595 p_consolidation_entity_id => l_parent_entity_id,
1596 p_cal_period_id => p_cal_period_id);
1597
1598 gcs_eng_cp_utility_pkg.submit_xml_ntf_program(p_execution_type => 'IMPACT_ENGINE',
1599 p_run_name => l_run_name,
1600 p_cons_entity_id => l_parent_entity_id,
1601 p_category_code => 'NOT_APPLICABLE');
1602
1603 END IF;
1604
1605 END IF;
1606
1607 BEGIN
1608 -- Check to see if subsequent period impacted
1609 gcs_utility_pkg.get_cal_period_details(p_cal_period_id => p_cal_period_id,
1610 p_cal_period_record => l_cal_period_info);
1611
1612 SELECT run_name
1613 INTO l_run_name
1614 FROM gcs_cons_eng_runs
1615 WHERE hierarchy_id = p_hierarchy_id
1616 AND run_entity_id = p_run_entity_id
1617 AND balance_type_code = p_balance_type_code
1618 AND cal_period_id = l_cal_period_info.next_cal_period_id
1619 AND most_recent_flag = 'Y';
1620
1621 insert_impact_analysis(p_run_name => l_run_name,
1622 p_consolidation_entity_id => p_run_entity_id,
1623 p_child_entity_id => p_run_entity_id,
1624 p_message_name => 'GCS_PRIOR_PD_RECONSOLIDATED',
1625 p_date_token => sysdate);
1626
1627 UPDATE gcs_cons_eng_runs
1628 SET impacted_flag = 'Y'
1629 WHERE run_name = l_run_name
1630 AND run_entity_id = p_run_entity_id;
1631
1632 rollup_impact(p_hierarchy_id => p_hierarchy_id,
1633 p_consolidation_entity_id => p_run_entity_id,
1634 p_cal_period_id => l_cal_period_info.next_cal_period_id);
1635
1636 gcs_eng_cp_utility_pkg.submit_xml_ntf_program(p_execution_type => 'IMPACT_ENGINE',
1637 p_run_name => l_run_name,
1638 p_cons_entity_id => p_run_entity_id,
1639 p_category_code => 'NOT_APPLICABLE');
1640
1641 EXCEPTION
1642 WHEN OTHERS THEN
1643 NULL;
1644 -- No impact has occurred
1645 END;
1646
1647 COMMIT;
1648 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1649 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1650 g_api || '.CONSOLIDATION_COMPLETED.end',
1651 '<<Exit>>');
1652 END IF;
1653
1654 END;
1655
1656 PROCEDURE value_set_map_updated(p_dimension_id IN NUMBER,
1657 p_eff_start_date IN DATE,
1658 p_eff_end_date IN DATE,
1659 p_consolidation_vs_id IN NUMBER) IS
1660
1661 TYPE r_run_entity_info IS RECORD(
1662 run_name VARCHAR2(240),
1663 run_entity_id NUMBER,
1664 cal_period_id NUMBER,
1665 hierarchy_id NUMBER(15));
1666 TYPE t_run_entity_info IS TABLE OF r_run_entity_info INDEX BY VARCHAR2(256);
1667 l_hash_key VARCHAR2(255);
1668 l_tab_run_entity_info t_run_entity_info;
1669 l_run_entity_info r_run_entity_info;
1670 l_character_index VARCHAR2(255);
1671
1672 l_cp_end_date_attr_id NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
1673 .attribute_id;
1674 l_cp_end_date_version_id NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
1675 .version_id;
1676 l_cp_start_date_attr_id NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_START_DATE')
1677 .attribute_id;
1681 .attribute_id;
1678 l_cp_start_date_version_id NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_START_DATE')
1679 .version_id;
1680 l_ledger_attr_id NUMBER := gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-LEDGER_ID')
1682 l_ledger_version_id NUMBER := gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-LEDGER_ID')
1683 .version_id;
1684 l_gvsc_attr_id NUMBER := gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-GLOBAL_VS_COMBO')
1685 .attribute_id;
1686 l_gvsc_version_id NUMBER := gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-GLOBAL_VS_COMBO')
1687 .version_id;
1688
1689 CURSOR c_impacted_hierarchies IS
1690 SELECT min(gcer.cal_period_id) cal_period_id,
1691 gcer.hierarchy_id hierarchy_id,
1692 min(fcpa_end.date_assign_value) end_date
1693 FROM gcs_cons_eng_runs gcer,
1694 fem_cal_periods_attr fcpa_start,
1695 fem_cal_periods_attr fcpa_end
1696 WHERE gcer.cal_period_id = fcpa_start.cal_period_id
1697 AND gcer.cal_period_id = fcpa_end.cal_period_id
1698 AND fcpa_start.date_assign_value >= p_eff_start_date
1699 AND fcpa_end.date_assign_value <= p_eff_end_date
1700 AND fcpa_start.attribute_id = l_cp_start_date_attr_id
1701 AND fcpa_start.version_id = l_cp_start_date_version_id
1702 AND fcpa_end.attribute_id = l_cp_end_date_attr_id
1703 AND fcpa_end.version_id = l_cp_end_date_version_id
1704 GROUP BY hierarchy_id;
1705
1706 -- Bugfix 5843592, Get the correct source ledger Id, depending upon the calendar period
1707
1708 CURSOR c_impacted_entities(p_hierarchy_id NUMBER, p_cal_period_id NUMBER, p_cal_period_end_date DATE) IS
1709 SELECT gcr.parent_entity_id, gcr.child_entity_id, gcer.run_name
1710 FROM gcs_cons_relationships gcr,
1711 gcs_cons_eng_runs gcer,
1712 fem_global_vs_combo_defs fgvcd,
1713 gcs_entities_attr gea,
1714 fem_ledgers_attr fla
1715 WHERE gcr.hierarchy_id = p_hierarchy_id
1716 AND gcr.hierarchy_id = gcer.hierarchy_id
1717 AND gcer.most_recent_flag = 'Y'
1718 AND gcer.cal_period_id = p_cal_period_id
1719 AND gcr.parent_entity_id = gcer.run_entity_id
1720 AND gcr.dominant_parent_flag = 'Y'
1721 AND p_cal_period_end_date BETWEEN gcr.start_date AND
1722 NVL(gcr.end_date, p_cal_period_end_date)
1723 AND gcr.child_entity_id = gea.entity_id
1724 AND gea.data_type_code = gcer.balance_type_code
1725 AND p_cal_period_end_date BETWEEN gea.effective_start_date AND
1726 NVL(gea.effective_end_date, p_cal_period_end_date)
1727 AND gea.ledger_id = fla.ledger_id
1728 AND fla.attribute_id = l_gvsc_attr_id
1729 AND fla.version_id = l_gvsc_version_id
1730 AND fla.dim_attribute_numeric_member = fgvcd.global_vs_combo_id
1731 AND fgvcd.dimension_id = p_dimension_id
1732 AND fgvcd.value_set_id <> p_consolidation_vs_id;
1733
1734 BEGIN
1735
1736 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1737 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1738 g_api || '.VALUE_SET_MAP_UPDATED.begin',
1739 '<<Enter>>');
1740 END IF;
1741
1742 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1743 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1744 g_api || '.VALUE_SET_MAP_UPDATED',
1745 'Dimension Id : ' || p_dimension_id);
1746 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1747 g_api || '.VALUE_SET_MAP_UPDATED',
1748 'Effective Start Date : ' || p_eff_start_date);
1749 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1750 g_api || '.VALUE_SET_MAP_UPDATED',
1751 'Effective End Date : ' || p_eff_end_date);
1752 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1753 g_api || '.VALUE_SET_MAP_UPDATED',
1754 'Consolidation VS Id : ' || p_consolidation_vs_id);
1755 END IF;
1756
1757 FOR v_impacted_hierarchies IN c_impacted_hierarchies LOOP
1758
1759 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1760 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1761 g_api || '.VALUE_SET_MAP_UPDATED',
1762 'Hierarchy Id : ' ||
1763 v_impacted_hierarchies.hierarchy_id);
1764 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1765 g_api || '.VALUE_SET_MAP_UPDATED',
1766 'Cal Period Id : ' ||
1767 v_impacted_hierarchies.cal_period_id);
1768 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1769 g_api || '.VALUE_SET_MAP_UPDATED',
1770 'Cal Period End Date : ' ||
1771 v_impacted_hierarchies.end_date);
1772 END IF;
1773
1774 FOR v_impacted_entities IN c_impacted_entities(v_impacted_hierarchies.hierarchy_id,
1775 v_impacted_hierarchies.cal_period_id,
1776 v_impacted_hierarchies.end_date) LOOP
1777
1778 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1779 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1780 g_api || '.VALUE_SET_MAP_UPDATED',
1781 'Parent Entity Id : ' ||
1782 v_impacted_entities.parent_entity_id);
1783 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1784 g_api || '.VALUE_SET_MAP_UPDATED',
1785 'Child Entity Id : ' ||
1789 'Run Name : ' || v_impacted_entities.run_name);
1786 v_impacted_entities.child_entity_id);
1787 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1788 g_api || '.VALUE_SET_MAP_UPDATED',
1790 END IF;
1791
1792 insert_impact_analysis(p_run_name => v_impacted_entities.run_name,
1793 p_consolidation_entity_id => v_impacted_entities.parent_entity_id,
1794 p_child_entity_id => v_impacted_entities.child_entity_id,
1795 p_message_name => 'GCS_VS_MAP_UPDATED',
1796 p_date_token => sysdate);
1797
1798 l_hash_key := v_impacted_entities.run_name ||
1799 ' - ' ||
1800 v_impacted_entities.parent_entity_id;
1801 l_run_entity_info.run_name := v_impacted_entities.run_name;
1802 l_run_entity_info.run_entity_id := v_impacted_entities.parent_entity_id;
1803 l_run_entity_info.hierarchy_id := v_impacted_hierarchies.hierarchy_id;
1804 l_run_entity_info.cal_period_id := v_impacted_hierarchies.cal_period_id;
1805
1806 --Capture all the entity information in a hashtable so we don't perform the same update over and over
1807 l_tab_run_entity_info(l_hash_key) := l_run_entity_info;
1808 END LOOP;
1809
1810 END LOOP;
1811
1812 l_character_index := l_tab_run_entity_info.FIRST;
1813
1814 WHILE (l_character_index IS NOT NULL) LOOP
1815
1816 UPDATE gcs_cons_eng_runs
1817 SET impacted_flag = 'Y'
1818 WHERE run_name = l_tab_run_entity_info(l_character_index)
1819 .run_name
1820 AND run_entity_id = l_tab_run_entity_info(l_character_index)
1821 .run_entity_id;
1822
1823 rollup_impact(p_hierarchy_id => l_tab_run_entity_info(l_character_index)
1824 .hierarchy_id,
1825 p_consolidation_entity_id => l_tab_run_entity_info(l_character_index)
1826 .run_entity_id,
1827 p_cal_period_id => l_tab_run_entity_info(l_character_index)
1828 .cal_period_id);
1829
1830 gcs_eng_cp_utility_pkg.submit_xml_ntf_program(p_execution_type => 'IMPACT_ENGINE',
1831 p_run_name => l_tab_run_entity_info(l_character_index)
1832 .run_name,
1833 p_cons_entity_id => l_tab_run_entity_info(l_character_index)
1834 .run_entity_id,
1835 p_category_code => 'NOT_APPLICABLE');
1836 l_character_index := l_tab_run_entity_info.NEXT(l_character_index);
1837 END LOOP;
1838
1839 COMMIT;
1840
1841 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1842 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1843 g_api || '.VALUE_SET_MAP_UPDATED.end',
1844 '<<Exit>>');
1845 END IF;
1846 END;
1847 --
1848 -- Function
1849 -- adjustment_disabled()
1850 -- Purpose
1851 -- Tracks disabling adjustments
1852 -- Arguments
1853 -- p_subscription_guid Standard Business Event Parameter
1854 -- p_event Standard Business Event Parameter
1855 -- Notes
1856 -- Bugfix 5613302
1857 FUNCTION adjustment_disabled(p_subscription_guid in raw,
1858 p_event in out nocopy wf_event_t)
1859 RETURN VARCHAR2
1860
1861 IS
1862 l_entry_id NUMBER(15);
1863 l_start_cal_period_id NUMBER;
1864 l_end_cal_period_id NUMBER;
1865 l_hierarchy_id NUMBER;
1866 l_entity_id NUMBER;
1867 l_bal_type_code VARCHAR2(30);
1868 l_parameter_list wf_parameter_list_t;
1869 l_run_name VARCHAR2(80);
1870 l_start_cp_end_date DATE;
1871 l_end_cp_end_date DATE;
1872 l_entity_type_code VARCHAR2(1);
1873 l_cons_entity_id NUMBER;
1874 l_email VARCHAR2(200);
1875
1876 BEGIN
1877
1878 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1879 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1880 g_api || '.ADJUSTMENT_DISABLED.begin',
1881 '<<Enter>>');
1882 END IF;
1883
1884 l_parameter_list := p_event.getParameterList();
1885
1886 l_entry_id := TO_NUMBER(WF_EVENT.getValueForParameter('ENTRY_ID',
1887 l_parameter_list));
1888
1889 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1890 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1891 g_api || '.ADJUSTMENT_DISABLED',
1892 'Entry ID : ' || l_entry_id);
1893
1894 END IF;
1895
1896 SELECT geh.start_cal_period_id,
1897 geh.end_cal_period_id,
1898 geh.hierarchy_id,
1899 geh.entity_id,
1900 geh.balance_type_code,
1901 fcpa_start.date_assign_value
1902 INTO l_start_cal_period_id,
1903 l_end_cal_period_id,
1904 l_hierarchy_id,
1905 l_entity_id,
1906 l_bal_type_code,
1907 l_start_cp_end_date
1908 FROM gcs_entry_headers geh, fem_cal_periods_attr fcpa_start
1909 WHERE geh.entry_id = l_entry_id
1910 AND geh.start_cal_period_id = fcpa_start.cal_period_id
1911 AND fcpa_start.attribute_id =
1912 gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
1913 .attribute_id
1914 AND fcpa_start.version_id =
1915 gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
1916 .version_id;
1917
1918 SELECT parent_entity_id
1919 INTO l_cons_entity_id
1920 FROM gcs_cons_relationships
1921 WHERE hierarchy_id = l_hierarchy_id
1922 AND child_entity_id = l_entity_id
1923 AND dominant_parent_flag = 'Y'
1924 AND l_start_cp_end_date BETWEEN start_date AND
1925 NVL(end_date, l_start_cp_end_date);
1926
1927 SELECT run_name
1928 INTO l_run_name
1929 FROM gcs_cons_eng_runs
1930 WHERE most_recent_flag = 'Y'
1931 AND hierarchy_id = l_hierarchy_id
1932 AND cal_period_id = l_start_cal_period_id
1933 AND balance_type_code = l_bal_type_code
1934 AND run_entity_id = l_cons_entity_id;
1935
1936 insert_impact_analysis(p_run_name => l_run_name,
1937 p_consolidation_entity_id => l_cons_entity_id,
1938 p_child_entity_id => l_entity_id,
1939 p_message_name => 'GCS_ADJUSTMENT_DISABLED',
1940 p_pre_relationship_id => null,
1941 p_post_relationship_id => null,
1942 p_date_token => sysdate,
1943 p_entry_id => l_entry_id);
1944
1945 UPDATE gcs_cons_eng_runs
1946 SET impacted_flag = 'Y'
1947 WHERE run_name = l_run_name
1948 AND run_entity_id = l_cons_entity_id
1949 AND most_recent_flag = 'Y';
1950
1951 rollup_impact(p_hierarchy_id => l_hierarchy_id,
1952 p_consolidation_entity_id => l_cons_entity_id,
1953 p_cal_period_id => l_start_cal_period_id);
1954
1955 --Bugfix 4179379 : Send notifications via workflow
1956 gcs_eng_cp_utility_pkg.submit_xml_ntf_program(p_execution_type => 'IMPACT_ENGINE',
1957 p_run_name => l_run_name,
1958 p_cons_entity_id => l_cons_entity_id,
1959 p_category_code => 'NOT_APPLICABLE',
1960 p_entry_id => l_entry_id);
1961
1962 RETURN 'SUCCESS';
1963
1964 EXCEPTION
1965 WHEN NO_DATA_FOUND THEN
1966 RETURN 'SUCCESS';
1967 END adjustment_disabled;
1968
1969 END GCS_CONS_IMPACT_ANALYSIS_PKG;