[Home] [Help]
PACKAGE BODY: APPS.GCS_CONS_MONITOR_PKG
Source
1 PACKAGE BODY GCS_CONS_MONITOR_PKG AS
2 /* $Header: gcscmb.pls 120.10 2007/03/22 12:45:55 vkosuri noship $ */
3
4 --
5 -- PRIVATE GLOBAL VARIABLES
6 --
7
8 -- The API name
9 g_pkg_name CONSTANT VARCHAR2(30) := 'gcs.plsql.GCS_CONS_MONITOR_PKG';
10
11 g_cal_period_end_date_attr NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
12 .attribute_id;
13 g_cal_period_end_date_version NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
14 .version_id;
15 g_entity_ledger_attr NUMBER := gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-LEDGER_ID')
16 .attribute_id;
17 g_entity_ledger_version NUMBER := gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-LEDGER_ID')
18 .version_id;
19 g_ledger_currency_attr NUMBER := gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-LEDGER_FUNCTIONAL_CRNCY_CODE')
20 .attribute_id;
21 g_ledger_currency_version NUMBER := gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-LEDGER_FUNCTIONAL_CRNCY_CODE')
22 .version_id;
23 g_entity_type_attr NUMBER := gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-ENTITY_TYPE_CODE')
24 .attribute_id;
25 g_entity_type_version NUMBER := gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-ENTITY_TYPE_CODE')
26 .version_id;
27
28 --
29 -- PRIVATE PROCEDURES
30 --
31
32 CURSOR c_parent_entities(p_end_date DATE, p_hierarchy_id NUMBER, p_entity_id NUMBER) IS
33 SELECT gcr.parent_entity_id
34 FROM gcs_cons_relationships gcr
35 START WITH child_entity_id = p_entity_id
36 AND dominant_parent_flag = 'Y'
37 AND gcr.hierarchy_id = p_hierarchy_id
38 AND p_end_date BETWEEN gcr.start_date AND
39 NVL(gcr.end_date, p_end_date)
40 CONNECT BY PRIOR parent_entity_id = child_entity_id
41 AND dominant_parent_flag = 'Y'
42 AND gcr.hierarchy_id = p_hierarchy_id
43 AND p_end_date BETWEEN gcr.start_date AND
44 NVL(gcr.end_date, p_end_date);
45
46 PROCEDURE load_entity(p_entity_id IN NUMBER,
47 p_hierarchy_id IN NUMBER,
48 p_cal_period_id IN NUMBER,
49 p_balance_type_code IN VARCHAR2,
50 p_end_date IN DATE) IS
51
52 -- Bugfix 5843592, Get the correct entity, depending upon the calendar period
53
54 CURSOR c_child_count IS
55 SELECT count(1)
56 FROM gcs_cons_relationships gcr,
57 fem_entities_attr fea,
58 gcs_entities_attr gea
59 WHERE gcr.hierarchy_id = p_hierarchy_id
60 AND gcr.parent_entity_id = p_entity_id
61 AND gcr.dominant_parent_flag = 'Y'
62 AND gcr.child_entity_id = fea.entity_id
63 AND fea.attribute_id = g_entity_type_attr
64 AND fea.version_id = g_entity_type_version
65 AND fea.dim_attribute_varchar_member <> 'E'
66 AND fea.entity_id = gea.entity_id
67 AND gea.data_type_code = p_balance_type_code
68 AND p_end_date BETWEEN gea.effective_start_date
69 AND NVL(gea.effective_end_date, p_end_date )
70 AND p_end_date BETWEEN gcr.start_date AND
71 nvl(gcr.end_date, p_end_date);
72
73 -- bug fix 4554149
74
75 -- Bugfix 5843592, Get the correct source ledger Id, depending upon the calendar period
76
77 CURSOR c_op_entities IS
78 SELECT status_code
79 FROM gcs_data_sub_dtls gdsd,
80 fem_entities_attr fea,
81 gcs_cons_relationships gcr,
82 gcs_cal_period_maps_gt gcpmt,
83 fem_ledgers_attr fla,
84 gcs_entity_cons_attrs geca,
85 gcs_entities_attr gea
86 WHERE gcr.child_entity_id = gdsd.entity_id
87 AND p_cal_period_id = gcpmt.target_cal_period_id
88 AND gdsd.cal_period_id = gcpmt.source_cal_period_id
89 AND gdsd.balance_type_code = p_balance_type_code
90 AND gdsd.most_recent_flag = 'Y'
91 AND NVL(gdsd.currency_code, fla.dim_attribute_varchar_member) =
92 geca.currency_code
93 AND gea.entity_id = gdsd.entity_id
94 AND gea.data_type_code = gdsd.balance_type_code
95 AND p_end_date BETWEEN gea.effective_start_date
96 AND NVL(gea.effective_end_date, p_end_date )
97 AND fla.ledger_id = gea.ledger_id
98 AND fla.attribute_id = g_ledger_currency_attr
99 AND fla.version_id = g_ledger_currency_version
100 AND geca.hierarchy_id = gcr.hierarchy_id
101 AND geca.entity_id = gdsd.entity_id
102 AND gcr.child_entity_id = fea.entity_id
103 AND gcr.hierarchy_id = p_hierarchy_id
104 AND gcr.parent_entity_id = p_entity_id
105 AND gcr.dominant_parent_flag = 'Y'
106 AND fea.attribute_id = g_entity_type_attr
107 AND fea.version_id = g_entity_type_version
108 AND fea.dim_attribute_varchar_member = 'O'
109 AND p_end_date BETWEEN gcr.start_date AND NVL(gcr.end_date, p_end_date);
110 -- end of bug fix 4554149
111
112 CURSOR c_cons_entities IS
113 SELECT gcds.status_code
114 FROM gcs_cons_data_statuses gcds,
115 fem_entities_attr fea,
116 gcs_cons_relationships gcr
117 WHERE gcr.child_entity_id = gcds.consolidation_entity_id
118 AND gcds.cal_period_id = p_cal_period_id
119 AND gcds.balance_type_code = p_balance_type_code
120 AND gcds.hierarchy_id = p_hierarchy_id
121 AND gcr.child_entity_id = fea.entity_id
122 AND gcr.hierarchy_id = p_hierarchy_id
123 AND gcr.parent_entity_id = p_entity_id
124 AND gcr.dominant_parent_flag = 'Y'
125 AND fea.attribute_id = g_entity_type_attr
126 AND fea.version_id = g_entity_type_version
127 AND fea.dim_attribute_varchar_member = 'C'
128 AND p_end_date between gcr.start_date AND
129 NVL(gcr.end_date, p_end_date);
130
131 l_op_status VARCHAR2(30);
132 l_cons_status VARCHAR2(30);
133 l_status_code VARCHAR2(30) := 'NOT_STARTED';
134 l_total_cnt NUMBER(15) := 0;
135 l_oper_cnt NUMBER(15) := 0;
136 l_undo_cnt NUMBER(15) := 0;
137 l_child_cnt NUMBER(15) := 0;
138 l_api_name VARCHAR2(30) := 'load_entity';
139 BEGIN
140 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure THEN
141 fnd_log.STRING(fnd_log.level_procedure,
142 g_pkg_name || '.' || l_api_name,
143 gcs_utility_pkg.g_module_enter || ' p_entity_id = ' ||
144 p_entity_id || ' p_hierarchy_id = ' || p_hierarchy_id ||
145 ' p_cal_period_id = ' || p_cal_period_id ||
146 ' p_balance_type_code = ' || p_balance_type_code ||
147 ' p_end_date = ' || p_end_date || ' ' ||
148 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
149 END IF;
150 gcs_utility_pkg.populate_calendar_map_details(p_cal_period_id,
151 'N',
152 'N');
153
154 -- count how many non-elim children this entity has
155 OPEN c_child_count;
156 FETCH c_child_count
157 INTO l_child_cnt;
158 CLOSE c_child_count;
159
160
161 OPEN c_op_entities;
162 LOOP
163 FETCH c_op_entities
164 INTO l_op_status;
165 EXIT WHEN(c_op_entities%NOTFOUND);
166 IF (l_op_status <> 'NOT_STARTED' ) THEN
167 l_total_cnt := l_total_cnt + 1;
168 l_oper_cnt := l_oper_cnt + 1;
169 IF (l_op_status = 'ERROR') THEN
170 l_status_code := 'ERROR';
171 ELSIF (l_op_status = 'WARNING' AND l_status_code <> 'ERROR') THEN
172 l_status_code := 'WARNING';
173 --Start Bugfix 5569620
174 ELSIF (l_op_status = 'IMPACTED' AND l_status_code <> 'ERROR' AND l_status_code <> 'WARNING') THEN
175 l_status_code := 'IMPACTED';
176 --End Bugfix 5569620
177 ELSIF (l_op_status = 'COMPLETED' AND l_status_code <> 'ERROR' AND
178 l_status_code <> 'WARNING' AND l_status_code <> 'IMPACTED') THEN
179 l_status_code := 'COMPLETED';
180 --Start BugFix: 5647099
181 ELSIF (l_op_status = 'UNDONE') THEN
182 l_total_cnt := l_total_cnt - 1;
183 l_undo_cnt := l_undo_cnt + 1;
184 --End BugFix: 5647099
185 END IF;
186 END IF;
187 END LOOP;
188 CLOSE c_op_entities;
189
190 OPEN c_cons_entities;
191 LOOP
192 FETCH c_cons_entities
193 INTO l_cons_status;
194 EXIT WHEN(c_cons_entities%NOTFOUND);
195 IF (l_cons_status <> 'NOT_STARTED') THEN
196 l_total_cnt := l_total_cnt + 1;
197 IF (l_cons_status = 'ERROR') THEN
198 l_status_code := 'ERROR';
199 ELSIF (l_cons_status = 'WARNING' AND l_status_code <> 'ERROR') THEN
200 l_status_code := 'WARNING';
201 --Start Bugfix 5569620
202 ELSIF (l_cons_status = 'IMPACTED' AND l_status_code <> 'ERROR'
203 AND l_status_code <> 'WARNING') THEN
204 l_status_code := 'IMPACTED';
205 --End Bugfix 5569620
206 --Start Bugfix 5668981
207 ELSIF (l_cons_status = 'IN_PROGRESS' AND l_status_code <> 'ERROR'
208 AND l_status_code <> 'WARNING' AND l_status_code <> 'IMPACTED') THEN
209 l_status_code := 'IN_PROGRESS';
210 ELSIF (l_cons_status = 'COMPLETED' AND l_status_code <> 'ERROR'
211 AND l_status_code <> 'WARNING' AND l_status_code <> 'IMPACTED'
212 AND l_status_code <> 'IN_PROGRESS') THEN
213 --End Bugfix 5668981
214 l_status_code := 'COMPLETED';
215 END IF;
216 END IF;
217 END LOOP;
218 CLOSE c_cons_entities;
219
220 -- Start Bug fix : 5647099
221 IF (l_oper_cnt = l_undo_cnt AND l_status_code <> 'NOT_STARTED'
222 --Bug fix : 5668981
223 AND l_oper_cnt <> 0) THEN
224 l_status_code := 'NOT_STARTED';
225 END IF;
226 -- End Bug fix : 5647099
227
228
229 IF (l_total_cnt < l_child_cnt AND l_status_code <> 'NOT_STARTED') THEN
230 l_status_code := 'IN_PROGRESS';
231 END IF;
232
233 MERGE INTO gcs_cons_data_statuses gcds
234 USING (SELECT l_status_code status_code FROM dual) src
235 ON (gcds.hierarchy_id = p_hierarchy_id AND
236 gcds.consolidation_entity_id = p_entity_id AND
237 gcds.cal_period_id = p_cal_period_id AND
238 gcds.balance_type_code = p_balance_type_code)
239 WHEN MATCHED THEN
240 UPDATE
241 SET gcds.status_code = src.status_code,
242 gcds.last_update_date = sysdate,
243 gcds.last_updated_by = fnd_global.user_id
244 WHEN NOT MATCHED THEN
245 INSERT
246 (gcds.hierarchy_id,
247 gcds.consolidation_entity_id,
248 gcds.cal_period_id,
249 gcds.balance_type_code,
250 gcds.status_code,
251 gcds.created_by,
252 gcds.creation_date,
253 gcds.last_updated_by,
254 gcds.last_update_date)
255 VALUES
256 (p_hierarchy_id,
257 p_entity_id,
258 p_cal_period_id,
259 p_balance_type_code,
260 src.status_code,
261 fnd_global.user_id,
262 sysdate,
263 fnd_global.user_id,
264 sysdate);
265 END load_entity;
266
267 -- update the gcs_cons_data_statuses table for a newly added entity in the specified hierarchy
268 -- from the start date
269 -- it first insert into the gcs_cons_data_statuses table
270 -- then update gcs_cons_data_statuses for the cal_period_id and balance_type_code not loaded with
271 -- this entity
272 PROCEDURE add_entity(p_entity_id IN NUMBER,
273 p_hierarchy_id IN NUMBER,
274 p_start_date IN DATE) IS
275
276
277
278 -- bug fix 4554149
279 CURSOR c_entity_period IS
280 SELECT fcpa.cal_period_id,
281 gdsd.balance_type_code
282 FROM gcs_data_sub_dtls gdsd,
283 fem_cal_periods_attr fcpa,
284 fem_ledgers_attr fla,
285 gcs_entity_cons_attrs geca,
286 fem_entities_attr fea_cur
287 WHERE gdsd.entity_id = p_entity_id
288 AND gdsd.cal_period_id = fcpa.cal_period_id
289 AND gdsd.most_recent_flag = 'Y'
290 AND fcpa.attribute_id = g_cal_period_end_date_attr
291 AND fcpa.version_id = g_cal_period_end_date_version
292 AND fcpa.date_assign_value >= p_start_date
293 AND NVL(gdsd.currency_code, fla.dim_attribute_varchar_member) =
294 geca.currency_code
295 AND fea_cur.entity_id = gdsd.entity_id
296 AND fea_cur.attribute_id = g_entity_ledger_attr
297 AND fea_cur.version_id = g_entity_ledger_version
298 AND fla.ledger_id = fea_cur.dim_attribute_numeric_member
299 AND fla.attribute_id = g_ledger_currency_attr
300 AND fla.version_id = g_ledger_currency_version
301 AND geca.hierarchy_id = p_hierarchy_id
302 AND geca.entity_id = gdsd.entity_id;
303 -- end of bug fix 4554149
304
305 cursor c_target_cal_period is
306 select target_cal_period_id,
307 fcpa.date_assign_value
308 from gcs_cal_period_maps_gt gcpmt,
309 fem_cal_periods_attr fcpa
310 where gcpmt.target_cal_period_id = fcpa.cal_period_id
311 and fcpa.attribute_id = g_cal_period_end_date_attr
312 and fcpa.version_id = g_cal_period_end_date_version;
313
314 l_date date;
315 l_cal_period_id number;
316 l_bal_type_code varchar2(30);
317 l_parent_id number;
318 l_api_name VARCHAR2(30) := 'add_entity';
319
320 BEGIN
321 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure THEN
322 fnd_log.STRING(fnd_log.level_procedure,
323 g_pkg_name || '.' || l_api_name,
324 gcs_utility_pkg.g_module_enter || ' p_entity_id = ' ||
325 p_entity_id || ' p_hierarchy_id = ' || p_hierarchy_id || ' ' ||
326 ' p_start_date = ' || p_start_date || ' ' ||
327 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
328 END IF;
329 UPDATE gcs_cons_data_statuses gcds
330 SET status_code = 'IN_PROGRESS',
331 last_update_date = sysdate,
332 last_updated_by = fnd_global.user_id
333 WHERE hierarchy_id = p_hierarchy_id
334 AND status_code in ('COMPLETED', 'WARNING', 'ERROR')
335 AND consolidation_entity_id in
336 (SELECT gcr.parent_entity_id
337 FROM gcs_cons_relationships gcr
338 START WITH child_entity_id = p_entity_id
339 AND hierarchy_id = p_hierarchy_id
340 AND dominant_parent_flag = 'Y'
341 CONNECT BY prior parent_entity_id = child_entity_id
342 AND hierarchy_id = p_hierarchy_id
343 AND dominant_parent_flag = 'Y')
344 AND EXISTS
345 (SELECT 1
346 FROM fem_cal_periods_attr fcpa
347 WHERE fcpa.cal_period_id = gcds.cal_period_id
348 AND fcpa.attribute_id = g_cal_period_end_date_attr
349 AND fcpa.version_id = g_cal_period_end_date_version
350 AND fcpa.date_assign_value >= p_start_date);
351
352 OPEN c_entity_period;
353 LOOP
354 FETCH c_entity_period
355 INTO l_cal_period_id, l_bal_type_code;
356 EXIT WHEN(c_entity_period%NOTFOUND);
357
358 gcs_utility_pkg.populate_calendar_map_details(l_cal_period_id,
359 'Y',
360 'N');
361
362 OPEN c_parent_entities(p_start_date, p_hierarchy_id, p_entity_id);
363 LOOP
364 FETCH c_parent_entities
365 INTO l_parent_id;
366 EXIT WHEN(c_parent_entities % NOTFOUND);
367
368 OPEN c_target_cal_period;
369 LOOP
370 FETCH c_target_cal_period
371 INTO l_cal_period_id, l_date;
372 EXIT WHEN(c_target_cal_period%NOTFOUND);
373 load_entity(l_parent_id,
374 p_hierarchy_id,
375 l_cal_period_id,
376 l_bal_type_code,
377 l_date);
378 END LOOP;
379 CLOSE c_target_cal_period;
380 END LOOP;
381 CLOSE c_parent_entities;
382 END LOOP;
383 CLOSE c_entity_period;
384
385 END add_entity;
386
387 --
388 -- PUBLIC PROCEDURES
389 --
390
391 --
392 -- Procedure
393 -- lock_results
394 -- Purpose
395 -- lock/unlock consolidation results
396 -- called from consolidation monitor UI
397 -- Arguments
398 -- p_runname Consolidation run identifier
399 -- p_entity_id Consolidation entity identifier
400 -- p_lock_flag Y for lock and N for unlock
401 --
402 PROCEDURE lock_results(p_runname IN VARCHAR2,
403 p_entity_id IN NUMBER,
404 p_lock_flag IN VARCHAR2,
405 x_errbuf OUT NOCOPY VARCHAR2,
406 x_retcode OUT NOCOPY VARCHAR2) IS
407 CURSOR c_entities IS
408 SELECT run_entity_id, status_code
409 FROM gcs_cons_eng_runs
410 WHERE NVL(associated_run_name, run_name) = p_runname
411 START WITH run_entity_id = p_entity_id
412 AND NVL(associated_run_name, run_name) = p_runname
413 CONNECT BY PRIOR run_entity_id = parent_entity_id;
414
415 gcs_cons_eng_invalid_status EXCEPTION;
416 l_api_name VARCHAR2(30) := 'LOCK_RESULTS';
417 BEGIN
418 SAVEPOINT gcs_lock_results_start;
419
420 FOR entity IN c_entities LOOP
421 IF (entity.status_code <> 'COMPLETED') THEN
422 RAISE gcs_cons_eng_invalid_status;
423 END IF;
424
425 UPDATE gcs_cons_eng_runs
426 SET locked_flag = decode(p_lock_flag, 'Y', 'N', 'Y')
427 WHERE NVL(associated_run_name, run_name) = p_runname
428 AND run_entity_id = entity.run_entity_id;
429
430 END LOOP;
431 EXCEPTION
432 WHEN gcs_cons_eng_invalid_status THEN
433 ROLLBACK TO gcs_lock_results_start;
434 fnd_message.set_name('GCS', 'GCS_CM_INVALID_STATUS');
435 x_errbuf := fnd_message.get;
436 x_retcode := fnd_api.g_ret_sts_error;
437
438 -- Write the appropriate information to the execution report
439 IF fnd_log.g_current_runtime_level <= fnd_log.level_error THEN
440 fnd_log.STRING(fnd_log.level_error,
441 g_pkg_name || '.' || l_api_name,
442 gcs_utility_pkg.g_module_failure || ' ' || x_errbuf ||
443 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
444 END IF;
445 WHEN OTHERS THEN
446 ROLLBACK TO gcs_lock_results_start;
447 x_errbuf := SQLCODE || SQLERRM;
448 x_retcode := fnd_api.g_ret_sts_unexp_error;
449
450 -- Write the appropriate information to the execution report
451 IF fnd_log.g_current_runtime_level <= fnd_log.level_error THEN
452 fnd_log.STRING(fnd_log.level_error,
453 g_pkg_name || '.' || l_api_name,
454 gcs_utility_pkg.g_module_failure || ' ' || x_errbuf ||
455 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
456 END IF;
457 END lock_results;
458
459 --
460 -- Procedure
461 -- update_data_status
462 -- Purpose
463 -- Update the gcs_cons_data_statuses when a new hierarchy is created,
464 -- or an entity is added/deleted, or new data submitted
465 -- Arguments
466 -- p_load_id Data submission identifier
467 -- p_cons_rel_id Consolidation relationship identifier
468 -- p_hierarchy_id Hierarchy for which the logic must be performed
469 -- p_transaction_type NEW, ACQ, or DIS
470 --
471 PROCEDURE update_data_status(p_load_id IN NUMBER DEFAULT NULL,
472 p_cons_rel_id IN NUMBER DEFAULT NULL,
473 p_hierarchy_id IN NUMBER DEFAULT NULL,
474 p_transaction_type IN VARCHAR2 DEFAULT NULL) IS
475 l_load_id NUMBER := p_load_id;
476 l_entity_id NUMBER;
477 l_hierarchy_id NUMBER := p_hierarchy_id;
478 l_transaction_type VARCHAR2(10) := p_transaction_type;
479 l_start_date DATE;
480 l_end_date DATE;
481 l_date DATE;
482 l_cal_period_id NUMBER;
483 l_bal_type_code VARCHAR2(30);
484 l_child_id NUMBER(15);
485 l_parent_id_list DBMS_SQL.number_table;
486 l_parent_id NUMBER;
487 l_api_name VARCHAR2(80) := 'update_data_status';
488
489 -- bug fix 4554149
490 -- Bugfix 5843592, Get the correct source ledger Id, depending upon the calendar period
491 -- and use gcs_entities_attr instead of fem_entities_attr
492
493 CURSOR c_load_data(p_load_id NUMBER) IS
494 SELECT DISTINCT ghb.hierarchy_id,
495 gdsd.entity_id,
496 fcpb.cal_period_id,
497 gdsd.balance_type_code,
498 fcpa.date_assign_value
499 FROM gcs_data_sub_dtls gdsd,
500 fem_cal_periods_attr fcpa,
501 gcs_cal_period_maps_gt gcpmt,
502 gcs_hierarchies_b ghb,
503 fem_cal_periods_b fcpb,
504 fem_ledgers_attr fla,
505 gcs_entity_cons_attrs geca,
506 gcs_entities_attr gea,
507 fem_cal_periods_attr fcpa_curr
508 WHERE gdsd.cal_period_id = gcpmt.source_cal_period_id
509 AND fcpb.cal_period_id = fcpa.cal_period_id
510 AND fcpb.cal_period_id = gcpmt.target_cal_period_id
511 AND fcpb.calendar_id = ghb.calendar_id
512 AND fcpb.dimension_group_id = ghb.dimension_group_id
513 AND fcpa.attribute_id = g_cal_period_end_date_attr
514 AND fcpa.version_id = g_cal_period_end_date_version
515 AND gdsd.load_id = p_load_id
516 AND NVL(gdsd.currency_code, fla.dim_attribute_varchar_member) =
517 geca.currency_code
518 AND gea.entity_id = gdsd.entity_id
519 AND gea.data_type_code = gdsd.balance_type_code
520 AND fcpa_curr.cal_period_id = gdsd.cal_period_id
521 AND fcpa_curr.attribute_id = g_cal_period_end_date_attr
522 AND fcpa_curr.version_id = g_cal_period_end_date_version
523 AND fcpa_curr.date_assign_value BETWEEN gea.effective_start_date
524 AND NVL(gea.effective_end_date, fcpa_curr.date_assign_value )
525 AND gea.ledger_id = fla.ledger_id
526 AND fla.attribute_id = g_ledger_currency_attr
527 AND fla.version_id = g_ledger_currency_version
528 AND geca.hierarchy_id = ghb.hierarchy_id
529 AND geca.entity_id = gdsd.entity_id;
530
531 -- end of bug fix 4554149
532
533 CURSOR c_cons_rel_data(p_cons_rel_id NUMBER) IS
534 SELECT gcr.child_entity_id,
535 gcr.hierarchy_id,
536 gcr.start_date,
537 gcr.end_date
538 FROM gcs_cons_relationships gcr
539 WHERE cons_relationship_id = p_cons_rel_id;
540
541 CURSOR c_child_id(p_hierarchy_id NUMBER) IS
542 SELECT gcr.child_entity_id,
543 gcr.start_date
544 FROM gcs_cons_relationships gcr,
545 fem_entities_attr fea
546 WHERE gcr.hierarchy_id = p_hierarchy_id
547 AND gcr.dominant_parent_flag = 'Y'
548 AND gcr.child_entity_id = fea.entity_id
549 AND fea.attribute_id = g_entity_type_attr
550 AND fea.version_id = g_entity_type_version
551 AND fea.dim_attribute_varchar_member = 'O';
552
553 CURSOR c_parent_info(p_end_date DATE, p_hierarchy_id NUMBER, p_entity_id NUMBER) IS
554 SELECT gcds.cal_period_id,
555 gcds.balance_type_code,
556 fcpa.date_assign_value
557 FROM gcs_cons_data_statuses gcds,
558 fem_cal_periods_attr fcpa
559 WHERE hierarchy_id = p_hierarchy_id
560 AND consolidation_entity_id = p_entity_id
561 AND gcds.cal_period_id = fcpa.cal_period_id
562 AND fcpa.attribute_id = g_cal_period_end_date_attr
563 AND fcpa.version_id = g_cal_period_end_date_version
564 AND fcpa.date_assign_value > p_end_date;
565
566 BEGIN
567 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure THEN
568 fnd_log.STRING(fnd_log.level_procedure,
569 g_pkg_name || '.' || l_api_name,
570 gcs_utility_pkg.g_module_enter || ' p_load_id = ' ||
571 p_load_id || ', p_cons_rel_id = ' || p_cons_rel_id ||
572 ', p_hierarchy_id = ' || p_hierarchy_id ||
573 ', p_transaction_type = ' || p_transaction_type ||
574 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
575 END IF;
576
577 -- event raised from data submission
578 -- loop through each parent of the newly loaded entity and scan its children's status
579 IF (l_load_id IS NOT NULL) THEN
580
581 --Explode into calendar period maps table gcs_cal_period_maps_gt
582 SELECT cal_period_id
583 INTO l_cal_period_id
584 FROM gcs_data_sub_dtls
585 WHERE load_id = l_load_id;
586
587 gcs_utility_pkg.populate_calendar_map_details(l_cal_period_id,
588 'Y',
589 'N');
590
591 OPEN c_load_data(l_load_id);
592 LOOP
593 FETCH c_load_data
594 INTO l_hierarchy_id,
595 l_child_id,
596 l_cal_period_id,
597 l_bal_type_code,
598 l_date;
599 EXIT WHEN(c_load_data%NOTFOUND);
600
601 OPEN c_parent_entities(l_date, l_hierarchy_id, l_child_id);
602 LOOP
603 FETCH c_parent_entities
604 INTO l_entity_id;
605
606 EXIT WHEN(c_parent_entities % NOTFOUND);
607
608 load_entity(l_entity_id,
609 l_hierarchy_id,
610 l_cal_period_id,
611 l_bal_type_code,
612 l_date);
613 END LOOP;
614 CLOSE c_parent_entities;
615 END LOOP;
616 CLOSE c_load_data;
617
618 -- event raised from hierarchy change
619 ELSE
620 -- a newly create hierarchy
621 -- loop through each leaf node in this hierarchy and update its recursive parents' status
622 IF (l_transaction_type = 'NEW') THEN
623 OPEN c_child_id(l_hierarchy_id);
624 LOOP
625 FETCH c_child_id
626 INTO l_child_id,
627 l_date;
628 EXIT WHEN(c_child_id%NOTFOUND);
629 add_entity(l_child_id, l_hierarchy_id, l_date);
630 END LOOP;
631 CLOSE c_child_id;
632
633 -- add a new entity
634 -- update its recursive parents' status
635 ELSIF (l_transaction_type = 'ACQ') THEN
636 open c_cons_rel_data(p_cons_rel_id);
637 fetch c_cons_rel_data
638 INTO l_entity_id,
639 l_hierarchy_id,
640 l_start_date,
641 l_end_date;
642
643 IF (c_cons_rel_data%FOUND) THEN
644 add_entity(l_entity_id, l_hierarchy_id, l_start_date);
645 END IF;
646 close c_cons_rel_data;
647
648 -- delete an entity
649 -- loop through each parent of this entity and scan its children's status
650 ELSIF (l_transaction_type = 'DIS') THEN
651 open c_cons_rel_data(p_cons_rel_id);
652 fetch c_cons_rel_data
653 INTO l_child_id,
654 l_hierarchy_id,
655 l_start_date,
656 l_end_date;
657
658 IF (c_cons_rel_data%FOUND) THEN
659 OPEN c_parent_entities(l_end_date, l_hierarchy_id, l_child_id);
660 LOOP
661 FETCH c_parent_entities
662 INTO l_parent_id;
663 EXIT WHEN(c_parent_entities % NOTFOUND);
664
665 OPEN c_parent_info(l_end_date, l_hierarchy_id, l_parent_id);
666 LOOP
667 FETCH c_parent_info
668 INTO l_cal_period_id,
669 l_bal_type_code,
670 l_date;
671 EXIT WHEN(c_parent_info % NOTFOUND);
672 load_entity(l_parent_id,
673 l_hierarchy_id,
674 l_cal_period_id,
675 l_bal_type_code,
676 l_date);
677 END LOOP;
678 CLOSE c_parent_info;
679 END LOOP;
680 CLOSE c_parent_entities;
681 END IF;
682 CLOSE c_cons_rel_data;
683 END IF;
684 END IF;
685
686 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure THEN
687 fnd_log.STRING(fnd_log.level_procedure,
688 g_pkg_name || '.' || l_api_name,
689 gcs_utility_pkg.g_module_success || ' ' ||
690 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
691 END IF;
692
693 EXCEPTION
694 WHEN NO_DATA_FOUND THEN
695 NULL;
696 WHEN OTHERS THEN
697 IF fnd_log.g_current_runtime_level <= fnd_log.level_error THEN
698 fnd_log.STRING(fnd_log.level_error,
699 g_pkg_name || '.' || l_api_name,
700 gcs_utility_pkg.g_module_failure || ' ' || SQLERRM ||
701 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
702 END IF;
703
704 RAISE;
705
706 END update_data_status;
707
708 --
709 -- Procedure
710 -- hierarchy_init
711 -- Purpose
712 -- Update the gcs_cons_data_status when a new hierarchy is created
713 -- Arguments
714 -- p_hierarchy_id Hierarchy for which the logic must be performed
715 --
716 PROCEDURE hierarchy_init(x_errbuf OUT NOCOPY VARCHAR2,
717 x_retcode OUT NOCOPY VARCHAR2,
718 p_hierarchy_id NUMBER) IS
719
720 CURSOR c_child_id IS
721 SELECT gcr.child_entity_id,
722 gcr.start_date
723 FROM gcs_cons_relationships gcr,
724 fem_entities_attr fea
725 WHERE gcr.hierarchy_id = p_hierarchy_id
726 AND gcr.dominant_parent_flag = 'Y'
727 AND gcr.child_entity_id = fea.entity_id
728 AND fea.attribute_id = g_entity_type_attr
729 AND fea.version_id = g_entity_type_version
730 AND fea.dim_attribute_varchar_member = 'O';
731
732 l_date DATE;
733 l_child_id NUMBER;
734 l_api_name VARCHAR2(80) := 'hierarchy_init';
735
736 BEGIN
737 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
738 g_pkg_name || '.' || l_api_name || ' ENTER : ' ||
739 ', p_hierarchy_id = ' || p_hierarchy_id ||
740 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
741
742 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
743
744 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure THEN
745 fnd_log.STRING(fnd_log.level_procedure,
746 g_pkg_name || '.' || l_api_name,
747 gcs_utility_pkg.g_module_enter ||
748 ', p_hierarchy_id = ' || p_hierarchy_id ||
749 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
750 END IF;
751
752 OPEN c_child_id;
753 LOOP
754 FETCH c_child_id
755 INTO l_child_id, l_date;
756
757 EXIT WHEN(c_child_id%NOTFOUND);
758 add_entity(l_child_id, p_hierarchy_id, l_date);
759 END LOOP;
760 CLOSE c_child_id;
761
762 x_retcode := fnd_api.g_ret_sts_success;
763
764 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
765 g_pkg_name || '.' || l_api_name || ' EXIT');
766 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
767
768 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure THEN
769 fnd_log.STRING(fnd_log.level_procedure,
770 g_pkg_name || '.' || l_api_name,
771 gcs_utility_pkg.g_module_success || ' ' ||
772 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
773 END IF;
774
775 GCS_FEM_HIER_SYNC_PKG.synchronize_hierarchy(p_hierarchy_id => p_hierarchy_id,
776 x_errbuf => x_errbuf,
777 x_retcode => x_retcode);
778
779 EXCEPTION
780 WHEN NO_DATA_FOUND THEN
781 NULL;
782 WHEN OTHERS THEN
783 x_errbuf := SQLERRM;
784 x_retcode := '2';
785
786 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
787 g_pkg_name || '.' || l_api_name || ' ERROR : ' ||
788 x_errbuf);
789 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
790
791 IF fnd_log.g_current_runtime_level <= fnd_log.level_error THEN
792 fnd_log.STRING(fnd_log.level_error,
793 g_pkg_name || '.' || l_api_name,
794 gcs_utility_pkg.g_module_failure || ' ' || SQLERRM ||
795 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
796 END IF;
797
798 END hierarchy_init;
799
800 --
801 -- Procedure
802 -- submit_update_data_status
803 -- Purpose
804 -- Submits update gcs_cons_data_statuses when a new hierarchy is created,
805 -- or an entity is added/deleted, or new data submitted
806 -- Arguments
807 -- p_load_id Data submission identifier
808 -- p_cons_rel_id Consolidation relationship identifier
809 -- p_hierarchy_id Hierarchy for which the logic must be performed
810 -- p_transaction_type NEW, ACQ, or DIS
811 PROCEDURE submit_update_data_status(x_errbuf OUT NOCOPY VARCHAR2,
812 x_retcode OUT NOCOPY VARCHAR2,
813 p_load_id IN NUMBER DEFAULT NULL,
814 p_cons_rel_id IN NUMBER DEFAULT NULL,
815 p_hierarchy_id IN NUMBER DEFAULT NULL,
816 p_transaction_type IN VARCHAR2 DEFAULT NULL) IS
817
818 l_api_name VARCHAR2(80) := 'submit_update_data_status';
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_pkg_name || '.' || l_api_name,
824 gcs_utility_pkg.g_module_enter || ' p_load_id = ' ||
825 p_load_id || ', p_cons_rel_id = ' || p_cons_rel_id ||
826 ', p_hierarchy_id = ' || p_hierarchy_id ||
827 ', p_transaction_type = ' || p_transaction_type ||
828 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
829 END IF;
830 FND_FILE.PUT_LINE(FND_FILE.LOG,
831 g_pkg_name || '.' || l_api_name || ' ENTER : ' ||
832 ' p_load_id = ' || p_load_id || ', p_cons_rel_id = ' || p_cons_rel_id ||
833 ', p_hierarchy_id = ' || p_hierarchy_id ||
834 ', p_transaction_type = ' || p_transaction_type ||
835 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
836
837 FND_FILE.NEW_LINE(FND_FILE.LOG);
838
839 update_data_status(p_load_id => p_load_id,
840 p_cons_rel_id => p_cons_rel_id,
841 p_hierarchy_id => p_hierarchy_id,
842 p_transaction_type => p_transaction_type);
843 COMMIT;
844 FND_FILE.PUT_LINE(FND_FILE.LOG,
845 g_pkg_name || '.' || l_api_name || ' EXIT : ' ||
846 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
847
848 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure THEN
849 fnd_log.STRING(fnd_log.level_procedure,
850 g_pkg_name || '.' || l_api_name,
851 gcs_utility_pkg.g_module_success || ' ' ||
852 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
853 END IF;
854
855 EXCEPTION
856 WHEN NO_DATA_FOUND THEN
857 NULL;
858 WHEN OTHERS THEN
859 IF fnd_log.g_current_runtime_level <= fnd_log.level_error THEN
860 fnd_log.STRING(fnd_log.level_error,
861 g_pkg_name || '.' || l_api_name,
862 gcs_utility_pkg.g_module_failure || ' ' || SQLERRM ||
863 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
864 END IF;
865
866 RAISE;
867
868 END submit_update_data_status;
869
870 END GCS_CONS_MONITOR_PKG;