[Home] [Help]
PACKAGE BODY: APPS.GCS_CONS_ENGINE_PKG
Source
1 PACKAGE BODY GCS_CONS_ENGINE_PKG as
2 /* $Header: gcs_eng_wfb.pls 120.12 2007/12/13 11:50:10 cdesouza noship $ */
3
4 -- Declaration of package body global variables
5
6 g_api VARCHAR2(200) := 'gcs.plsql.GCS_CONS_ENGINE_PKG';
7 g_cons_item_type VARCHAR2(200) := 'GCSENGNE';
8 g_cons_entity_process VARCHAR2(200) := 'CONS_ENTITY_PROCESS';
9 g_oper_item_type VARCHAR2(200) := 'GCSOPRWF';
10 g_oper_entity_process VARCHAR2(200) := 'OPER_ENTITY_PROCESS';
11 g_entity_type_attr NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-ENTITY_TYPE_CODE').attribute_id;
12 g_entity_type_version NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-ENTITY_TYPE_CODE').version_id;
13 g_source_system_attr NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-SOURCE_SYSTEM_CODE').attribute_id;
14 g_end_date_attr NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE').attribute_id;
15
16 -- End of package body global variables
17
18
19 -- bugfix 5569522: p_analysis_cycle_id will also be passed from raise_completion_event
20 -- procedure.
21 PROCEDURE submit_epb_data_transfer( p_hierarchy_id IN NUMBER,
22 p_balance_type_code IN VARCHAR2,
23 p_cal_period_id IN NUMBER,
24 p_analysis_cycle_id IN NUMBER )
25 IS PRAGMA AUTONOMOUS_TRANSACTION;
26
27
28 l_request_id NUMBER(15);
29 -- Bugfix 5187689: Only call data transfer if Analytical Reporting Step is complete
30 l_table_name VARCHAR2(30);
31 l_conc_request_status BOOLEAN;
32
33 BEGIN
34 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
35 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.SUBMIT_EPB_DATA_TRANSFER.begin', '<<Enter>>');
36 END IF;
37
38 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
39 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.SUBMIT_EPB_DATA_TRANSFER', 'Hierarchy : ' || p_hierarchy_id);
40 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.SUBMIT_EPB_DATA_TRANSFER', 'Consoliation Entity : ' || p_cal_period_id);
41 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.SUBMIT_EPB_DATA_TRANSFER', 'Analysis Cycle ID: ' || p_analysis_cycle_id);
42 END IF;
43
44 -- Bugfix 5187689: Make sure the table has been selected before submitting the program
45 SELECT epb_table_name
46 INTO l_table_name
47 FROM gcs_system_options;
48
49 IF (l_table_name IS NOT NULL) THEN
50 -- bugfix 5569522: Added the analysis_cycle_id as an argument to the concurrent
51 -- request.
52 l_request_id := fnd_request.submit_request(
53 application => 'GCS',
54 program => 'FCH_DATA_TRANSFER',
55 sub_request => FALSE,
56 argument1 => p_hierarchy_id,
57 argument2 => p_balance_type_code,
58 argument3 => p_cal_period_id,
59 argument4 => p_analysis_cycle_id);
60
61 END IF;
62
63 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
64 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.SUBMIT_EPB_DATA_TRANSFER.end', '<<Exit>>');
65 END IF;
66
67 COMMIT;
68 EXCEPTION
69 WHEN OTHERS THEN
70 NULL;
71 END;
72
73 PROCEDURE prepare_immediate_children( itemtype IN VARCHAR2,
74 itemkey IN VARCHAR2,
75 actid IN NUMBER,
76 funcmode IN varchar2,
77 result IN OUT NOCOPY varchar2)
78 IS
79
80 l_entities_to_process VARCHAR2(30) := 'FALSE';
81 cons_entity_wf_info gcs_cons_eng_utility_pkg.r_cons_entity_wf_info;
82 l_xlate_entry_id NUMBER(15);
83 l_run_detail_id NUMBER(15);
84 l_xlate_request_error_code VARCHAR2(30);
85 l_bp_xlate_request_error_code VARCHAR2(30);
86
87 CURSOR c_immediate_children (p_hierarchy_id IN NUMBER,
88 p_entity_id IN NUMBER,
89 p_cal_period_end_date IN DATE) IS
90 SELECT gcr.child_entity_id,
91 gcr.cons_relationship_id,
92 geca_child.currency_code child_currency_code,
93 geca_parent.currency_code parent_currency_code,
94 fea.dim_attribute_varchar_member entity_type_code
95 FROM gcs_cons_relationships gcr,
96 fem_entities_attr fea,
97 gcs_entity_cons_attrs geca_child,
98 gcs_entity_cons_attrs geca_parent
99 WHERE gcr.hierarchy_id = p_hierarchy_id
100 AND gcr.parent_entity_id = p_entity_id
101 AND geca_child.hierarchy_id = p_hierarchy_id
102 AND geca_child.entity_id = gcr.child_entity_id
103 AND gcr.dominant_parent_flag = 'Y'
104 AND geca_parent.hierarchy_id = p_hierarchy_id
105 AND geca_parent.entity_id = gcr.parent_entity_id
106 AND p_cal_period_end_date BETWEEN gcr.start_date and NVL(end_date,to_date(p_cal_period_end_date,'DD-MM-RR'))
107 AND gcr.child_entity_id = fea.entity_id
108 AND fea.attribute_id = g_entity_type_attr
109 AND fea.version_id = g_entity_type_version
110 AND fea.dim_attribute_varchar_member IN ('O','X','C');
111
112 CURSOR c_oper_entity_child (p_hierarchy_id IN NUMBER,
113 p_entity_id IN NUMBER,
114 p_cal_period_end_date IN DATE) IS
115 SELECT gcr.child_entity_id,
116 gcr.cons_relationship_id,
117 geca_child.currency_code child_currency_code,
118 geca_parent.currency_code parent_currency_code
119 FROM gcs_cons_relationships gcr,
120 gcs_entity_cons_attrs geca_child,
121 gcs_entity_cons_attrs geca_parent,
122 fem_entities_attr fea
123 WHERE gcr.hierarchy_id = p_hierarchy_id
124 AND gcr.parent_entity_id = p_entity_id
125 AND geca_child.hierarchy_id = p_hierarchy_id
126 AND geca_child.entity_id = gcr.child_entity_id
127 AND gcr.dominant_parent_flag = 'Y'
128 AND geca_parent.hierarchy_id = p_hierarchy_id
129 AND geca_parent.entity_id = gcr.parent_entity_id
130 AND gcr.child_entity_id = fea.entity_id
131 AND fea.attribute_id = g_entity_type_attr
132 AND fea.version_id = g_entity_type_version
133 AND fea.dim_attribute_varchar_member IN ('O','X')
134 AND p_cal_period_end_date BETWEEN gcr.start_date and NVL(end_date,to_date(p_cal_period_end_date,'DD-MM-RR'));
135
136 BEGIN
137
138 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
139 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.PREPARE_IMMEDIATE_CHILDREN.begin', '<<Enter for item key : ' || itemkey || '>>');
140 END IF;
141
142 gcs_cons_eng_utility_pkg.get_cons_entity_wf_info(itemtype, itemkey, cons_entity_wf_info);
143
144 FOR v_immediate_children IN c_immediate_children(cons_entity_wf_info.consolidation_hierarchy,
145 cons_entity_wf_info.consolidation_entity,
146 cons_entity_wf_info.cal_period_end_date)
147 LOOP
148 l_xlate_request_error_code := 'NOT_STARTED';
149
150 IF (v_immediate_children.parent_currency_code = v_immediate_children.child_currency_code) THEN
151 l_xlate_request_error_code := 'NOT_APPLICABLE';
152 END IF;
153
154 gcs_cons_eng_run_dtls_pkg.insert_row(p_run_name => cons_entity_wf_info.run_identifier,
155 p_consolidation_entity_id => cons_entity_wf_info.consolidation_entity,
156 p_category_code => 'DATAPREPARATION',
157 p_child_entity_id => v_immediate_children.child_entity_id,
158 p_entry_id => NULL,
159 p_stat_entry_id => null,
160 p_cons_relationship_id => v_immediate_children.cons_relationship_id,
161 p_run_detail_id => l_run_detail_id,
162 p_request_error_code => 'NOT_STARTED',
163 p_bp_request_error_code => 'NOT_STARTED');
164
165 IF (l_xlate_request_error_code = 'NOT_STARTED') THEN
166 gcs_cons_eng_run_dtls_pkg.insert_row(p_run_name => cons_entity_wf_info.run_identifier,
167 p_consolidation_entity_id => cons_entity_wf_info.consolidation_entity,
168 p_category_code => 'TRANSLATION',
169 p_child_entity_id => v_immediate_children.child_entity_id,
170 p_entry_id => NULL,
171 p_stat_entry_id => null,
172 p_cons_relationship_id => v_immediate_children.cons_relationship_id,
173 p_run_detail_id => l_run_detail_id,
174 p_request_error_code => l_xlate_request_error_code,
175 p_bp_request_error_code => l_xlate_request_error_code);
176 END IF;
177
178 IF (v_immediate_children.entity_type_code IN ('O', 'X')) THEN
179 -- Bugfix 4122843 : Support for Operating Entities Owning Other Operating Entities
180 FOR v_oper_entity IN c_oper_entity_child (cons_entity_wf_info.consolidation_hierarchy,
181 v_immediate_children.child_entity_id,
182 cons_entity_wf_info.cal_period_end_date)
183 LOOP
184
185 l_xlate_request_error_code := 'NOT_STARTED';
186
187 IF (v_oper_entity.child_currency_code = v_immediate_children.child_currency_code) THEN
188 l_xlate_request_error_code := 'NOT_APPLICABLE';
189 END IF;
190
191 gcs_cons_eng_run_dtls_pkg.insert_row(p_run_name => cons_entity_wf_info.run_identifier,
192 p_consolidation_entity_id => cons_entity_wf_info.consolidation_entity,
193 p_category_code => 'DATAPREPARATION',
194 p_child_entity_id => v_oper_entity.child_entity_id,
195 p_cons_relationship_id => v_oper_entity.cons_relationship_id,
196 p_run_detail_id => l_run_detail_id,
197 p_request_error_code => 'NOT_STARTED',
198 p_bp_request_error_code => 'NOT_STARTED');
199
200 IF (l_xlate_request_error_code = 'NOT_STARTED') THEN
201 gcs_cons_eng_run_dtls_pkg.insert_row(p_run_name => cons_entity_wf_info.run_identifier,
202 p_consolidation_entity_id => cons_entity_wf_info.consolidation_entity,
203 p_category_code => 'TRANSLATION',
204 p_child_entity_id => v_oper_entity.child_entity_id,
205 p_cons_relationship_id => v_oper_entity.cons_relationship_id,
206 p_run_detail_id => l_run_detail_id,
207 p_request_error_code => l_xlate_request_error_code,
208 p_bp_request_error_code => l_xlate_request_error_code);
209 END IF;
210 END LOOP;
211 END IF;
212
213 END LOOP;
214
215 result := 'COMPLETE';
216
217 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
218 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.PREPARE_IMMEDIATE_CHILDREN.end', '<<Exit for item key : ' || itemkey || '>>');
219 END IF;
220
221 END prepare_immediate_children;
222
223 PROCEDURE spawn_oper_entity_process( itemtype IN VARCHAR2,
224 itemkey IN VARCHAR2,
225 actid IN NUMBER,
226 funcmode IN varchar2,
227 result IN OUT NOCOPY varchar2)
228
229 IS
230
231 TYPE t_childkey_list IS TABLE OF VARCHAR2(200);
232
233 l_childkey_list t_childkey_list := t_childkey_list(null);
234 l_child_key VARCHAR2(200);
235 counter NUMBER(15) := 0;
236 l_entities_to_process BOOLEAN := FALSE;
237 cons_entity_wf_info gcs_cons_eng_utility_pkg.r_cons_entity_wf_info;
238 l_workflow_item_key VARCHAR2(200);
239 l_translated_required VARCHAR2(1);
240
241 CURSOR c_operating_entities (p_run_name IN VARCHAR2,
242 p_cons_entity_id IN NUMBER) IS
243 SELECT gcerd.child_entity_id,
244 gcerd.cons_relationship_id,
245 gcerd.run_detail_id,
246 fev.entity_name,
247 DECODE(geca_parent.currency_code, geca_child.currency_code, 'N', 'Y') translation_required
248 FROM gcs_cons_eng_run_dtls gcerd,
249 fem_entities_attr fea,
250 fem_entities_vl fev,
251 gcs_entity_cons_attrs geca_parent,
252 gcs_entity_cons_attrs geca_child,
253 gcs_cons_eng_runs gcer
254 WHERE gcerd.run_name = p_run_name
255 AND gcerd.consolidation_entity_id = p_cons_entity_id
256 AND gcerd.entry_id IS NULL
257 AND gcerd.category_code = 'DATAPREPARATION'
258 AND gcerd.child_entity_id = fev.entity_id
259 AND gcerd.child_entity_id = fea.entity_id
260 AND fea.attribute_id = g_entity_type_attr
261 AND fea.version_id = g_entity_type_version
262 AND fea.dim_attribute_varchar_member IN ('O','X')
263 AND geca_parent.entity_id = p_cons_entity_id
264 AND geca_child.entity_id = fev.entity_id
265 AND geca_parent.hierarchy_id = geca_child.hierarchy_id
266 AND gcer.hierarchy_id = geca_child.hierarchy_id
267 AND gcer.run_name = gcerd.run_name
268 ANd gcer.run_entity_id = gcerd.consolidation_entity_id;
269
270 BEGIN
271 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
272 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.SPAWN_OPER_ENTITY_PROCESS', '<<Enter for item key : ' || itemkey || '>>');
273 END IF;
274
275 gcs_cons_eng_utility_pkg.get_cons_entity_wf_info(itemtype, itemkey, cons_entity_wf_info);
276
277 FOR v_operating_entity IN c_operating_entities(cons_entity_wf_info.run_identifier, cons_entity_wf_info.consolidation_entity)
278 LOOP
279
280 counter := counter + 1;
281 l_child_key := v_operating_entity.entity_name || ' (' || cons_entity_wf_info.request_id || ')';
282
283 l_childkey_list.extend(1);
284 l_childkey_list(counter) := l_child_key;
285
286 --Bugfix 5197891: Assign appropriate user rather than null value
287 WF_ENGINE.CreateProcess(g_oper_item_type, l_childkey_list(counter), g_oper_entity_process, l_childkey_list(counter), FND_GLOBAL.USER_NAME);
288 WF_ENGINE.SetItemAttrNumber(g_oper_item_type, l_childkey_list(counter), 'OPER_ENTITY', v_operating_entity.child_entity_id);
289 WF_ENGINE.SetItemAttrNumber(g_oper_item_type, l_childkey_list(counter), 'RUN_DETAIL_ID', v_operating_entity.run_detail_id);
290 WF_ENGINE.SetItemAttrNumber(g_oper_item_type, l_childkey_list(counter), 'CONS_RELATIONSHIP_ID', v_operating_entity.cons_relationship_id);
291 WF_ENGINE.SetItemAttrText(g_oper_item_type, l_childkey_list(counter), 'PARENT_WORKFLOW_KEY', itemkey);
292 WF_ENGINE.SetItemAttrText(g_oper_item_type, l_childkey_list(counter), 'TRANSLATION_REQUIRED', v_operating_entity.translation_required);
296 WHILE (counter > 0)
293 WF_ENGINE.Set_Item_Parent(g_oper_item_type, l_childkey_list(counter), itemtype, itemkey,'WAITFORFLOW');
294 END LOOP;
295
297 LOOP
298 l_entities_to_process := TRUE;
299 WF_ENGINE.StartProcess(g_oper_item_type, l_childkey_list(counter));
300
301 counter := counter - 1;
302
303 END LOOP;
304
305 IF (l_entities_to_process) THEN
306 result := 'COMPLETE:T';
307 ELSE
308 result := 'COMPLETE:F';
309 END IF;
310
311 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
312 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.SPAWN_OPER_ENTITY_PROCESS.end', '<<Exit for item key : ' || itemkey || '>>');
313 END IF;
314
315 END spawn_oper_entity_process;
316
317 PROCEDURE execute_data_preparation( itemtype IN VARCHAR2,
318 itemkey IN VARCHAR2,
319 actid IN NUMBER,
320 funcmode IN varchar2,
321 result IN OUT NOCOPY varchar2)
322 IS
323
324 x_errbuf VARCHAR2(2000);
325 x_retcode VARCHAR2(2000);
326 l_execution_mode VARCHAR2(30) := 'FULL';
327 cons_entity_wf_info gcs_cons_eng_utility_pkg.r_cons_entity_wf_info;
328 oper_entity_wf_info gcs_cons_eng_utility_pkg.r_oper_entity_wf_info;
329 l_data_exists_flag VARCHAR2(1) := 'N';
330 l_entry_id NUMBER(15);
331 l_stat_entry_id NUMBER(15);
332 l_parameter_list gcs_cons_eng_utility_pkg.r_module_parameters;
333 l_request_error_code VARCHAR2(2000);
334
335 BEGIN
336
337 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
338 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.EXECUTE_DATA_PREPARATION', '<<Enter for item key : ' || itemkey || '>>');
339 END IF;
340
341
342 l_execution_mode := WF_ENGINE.GetActivityAttrText(itemtype, itemkey, actid, 'EXECUTION_MODE', FALSE);
343
344
345 gcs_cons_eng_utility_pkg.get_oper_entity_wf_info (itemtype,
346 itemkey,
347 cons_entity_wf_info,
348 oper_entity_wf_info);
349
350 IF (l_execution_mode = 'FULL') THEN
351
352 -- Bugfix 3750740 : Remove Check to See if Data Exists to Support Calendar Mapping (Dataprep already does this check)
353
354 l_parameter_list.hierarchy_id := cons_entity_wf_info.consolidation_hierarchy;
355 l_parameter_list.child_entity_id := oper_entity_wf_info.operating_entity;
356 l_parameter_list.cal_period_id := cons_entity_wf_info.cal_period_id;
357 l_parameter_list.run_detail_id := oper_entity_wf_info.run_detail_id;
358 l_parameter_list.cons_relationship_id := oper_entity_wf_info.cons_relationship_id;
359 l_parameter_list.balance_type_code := cons_entity_wf_info.balance_type_code;
360 --Bugfix 5017120: Added support for additional data types
361 l_parameter_list.source_dataset_code := cons_entity_wf_info.source_dataset_code;
362
363 gcs_cons_eng_utility_pkg.execute_module ( module_code => 'DATAPREPARATION',
364 p_parameter_list => l_parameter_list,
365 p_item_key => itemkey);
366
367
368 SELECT entry_id, stat_entry_id
369 INTO l_entry_id, l_stat_entry_id
370 FROM gcs_cons_eng_run_dtls
371 WHERE run_detail_id = oper_entity_wf_info.run_detail_id;
372
373 --Bugfix 3666700: Added code to insert into intercompany temporary table
374 gcs_interco_dynamic_pkg.insert_interco_trx( p_entry_id => l_entry_id,
375 p_stat_entry_id => NVL(l_stat_entry_id, -1),
376 p_hierarchy_id => l_parameter_list.hierarchy_id,
377 p_period_end_date => cons_entity_wf_info.cal_period_end_date,
378 x_errbuf => l_parameter_list.errbuf,
379 x_retcode => l_parameter_list.retcode);
380 IF (l_parameter_list.errbuf IS NULL) THEN
381 result := 'COMPLETE:T';
382 --Bugfix 4874306: Eliminate calls to XML Generation in order to leverage data templates
383 --gcs_cons_eng_utility_pkg.submit_xml_ntf_program( p_run_name => cons_entity_wf_info.run_identifier,
384 -- p_cons_entity_id => cons_entity_wf_info.consolidation_entity,
385 -- p_category_code => 'DATAPREPARATION',
386 -- p_run_detail_id => oper_entity_wf_info.run_detail_id);
387 ELSE
388 result := 'COMPLETE:F';
389 END IF;
390
391 ELSE -- Execution Mode is Incremental
392
393 BEGIN
394 SELECT gcia.entry_id,
395 gcia.stat_entry_id
396 INTO l_entry_id,
397 l_stat_entry_id
398 FROM gcs_cons_impact_analyses gcia,
399 gcs_entry_headers geh,
400 gcs_data_sub_dtls gdsd
401 WHERE gcia.run_name = cons_entity_wf_info.prior_run_identifier
402 AND gcia.consolidation_entity_id = cons_entity_wf_info.consolidation_entity
403 AND gcia.child_entity_id = oper_entity_wf_info.operating_entity
404 AND gdsd.load_id = gcia.load_id
405 AND gdsd.most_recent_flag = 'Y'
406 AND gcia.message_name IN ('GCS_PRISTINE_DATA_INC_LOAD','GCS_PRISTINE_DATA_FULL_LOAD')
407 AND gcia.entry_id = geh.entry_id
408 AND geh.disabled_flag = 'N';
409
410
411 --Bugfix 3666700: Added code to insert into intercompany temporary table
415 p_period_end_date => cons_entity_wf_info.cal_period_end_date,
412 gcs_interco_dynamic_pkg.insert_interco_trx( p_entry_id => l_entry_id,
413 p_stat_entry_id => NVL(l_stat_entry_id, -1),
414 p_hierarchy_id => l_parameter_list.hierarchy_id,
416 x_errbuf => l_parameter_list.errbuf,
417 x_retcode => l_parameter_list.retcode);
418
419 gcs_cons_eng_run_dtls_pkg.update_entry_headers_async( p_run_detail_id => oper_entity_wf_info.run_detail_id,
420 p_entry_id => l_entry_id,
421 p_stat_entry_id => l_stat_entry_id,
422 p_request_error_code => 'COMPLETED',
423 p_bp_request_error_code => 'COMPLETED');
424
425 result := 'COMPLETE:T';
426
427 EXCEPTION
428 WHEN NO_DATA_FOUND THEN
429 result := 'COMPLETE:F';
430
431 END;
432
433 END IF;
434
435 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
436 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.EXECUTE_DATA_PREPARATION.end', '<<Exit for item key : ' || itemkey || '>>');
437 END IF;
438
439 END execute_data_preparation;
440
441 PROCEDURE init_oper_entity_process( itemtype IN VARCHAR2,
442 itemkey IN VARCHAR2,
443 actid IN NUMBER,
444 funcmode IN varchar2,
445 result IN OUT NOCOPY varchar2)
446
447 IS
448
449 x_errbuf VARCHAR2(2000);
450 x_retcode VARCHAR2(2000);
451 l_process_code VARCHAR2(30);
452 l_prior_dataprep_exists VARCHAR2(1) := 'X';
453 cons_entity_wf_info gcs_cons_eng_utility_pkg.r_cons_entity_wf_info;
454 oper_entity_wf_info gcs_cons_eng_utility_pkg.r_oper_entity_wf_info;
455 l_entry_id NUMBER(15);
456 l_stat_entry_id NUMBER(15);
457 l_request_error_code VARCHAR2(200);
458
459 BEGIN
460
461 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
462 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.INIT_OPER_ENTITY_PROCESS.end', '<<Exit for item key : ' || itemkey || '>>');
463 END IF;
464
465 gcs_cons_eng_utility_pkg.get_oper_entity_wf_info (itemtype,
466 itemkey,
467 cons_entity_wf_info,
468 oper_entity_wf_info);
469
470 IF (cons_entity_wf_info.prior_run_identifier <> 'NO_PRIOR_RUN') THEN
471 -- Prior run exists
472 BEGIN
473 SELECT DECODE(gcerd.entry_id, NULL, 'COMPLETE:FULL', -1, 'COMPLETE:FULL', 'COMPLETE:INCREMENTAL'),
474 gcerd.entry_id,
475 gcerd.stat_entry_id,
476 gcerd.request_error_code
477 INTO result,
478 l_entry_id,
479 l_stat_entry_id,
480 l_request_error_code
481 FROM gcs_cons_eng_run_dtls gcerd,
482 gcs_cons_eng_runs gcer
483 WHERE gcer.run_entity_id = cons_entity_wf_info.consolidation_entity
484 AND gcerd.category_code = 'DATAPREPARATION'
485 AND gcer.run_name = gcerd.run_name
486 AND gcerd.request_error_code IN ('COMPLETED', 'WARNING')
487 AND gcer.run_name = cons_entity_wf_info.prior_run_identifier
488 AND gcer.balance_type_code = cons_entity_wf_info.balance_type_code
489 AND gcer.run_entity_id = gcerd.consolidation_entity_id
490 AND gcerd.child_entity_id = oper_entity_wf_info.operating_entity;
491 EXCEPTION
492 WHEN OTHERS THEN
493 result := 'COMPLETE:FULL';
494 END;
495
496 BEGIN
497 IF (result = 'COMPLETE:INCREMENTAL') THEN
498 SELECT 'COMPLETE:FULL'
499 INTO result
500 FROM gcs_cons_impact_analyses
501 WHERE run_name = cons_entity_wf_info.prior_run_identifier
502 AND consolidation_entity_id = cons_entity_wf_info.consolidation_entity
503 AND child_entity_id = oper_entity_wf_info.operating_entity
504 --Bugfix 4665921: Added GCS_VS_MAP_UPDATED check to support impact for value set assignments
505 AND message_name IN ('GCS_VS_MAP_UPDATED', 'GCS_PRISTINE_DATA_INC_LOAD', 'GCS_PRISTINE_DATA_FULL_LOAD')
506 AND ROWNUM < 2;
507 END IF;
508 EXCEPTION
509 WHEN NO_DATA_FOUND THEN
510 result := 'COMPLETE:NONE';
511
512 gcs_cons_eng_run_dtls_pkg.update_entry_headers_async( p_run_detail_id => oper_entity_wf_info.run_detail_id,
513 p_entry_id => l_entry_id,
514 p_stat_entry_id => l_stat_entry_id,
515 p_request_error_code => l_request_error_code,
516 p_bp_request_error_code => l_request_error_code
517 );
518 END;
519 ELSE
520 result := 'COMPLETE:FULL';
521 END IF;
522
523 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
527 END init_oper_entity_process;
524 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.INIT_OPER_ENTITY_PROCESS', '<<Exit for item key : ' || itemkey || '>>');
525 END IF;
526
528
529 PROCEDURE check_aggregation_required( itemtype IN VARCHAR2,
530 itemkey IN VARCHAR2,
531 actid IN NUMBER,
532 funcmode IN varchar2,
533 result IN OUT NOCOPY varchar2)
534
535 IS
536
537 l_aggregation_required VARCHAR2(1) := 'N';
538 l_run_identifier VARCHAR2(200);
539 l_entity_id NUMBER(15);
540 l_entry_count NUMBER(15);
541
542 cons_entity_wf_info gcs_cons_eng_utility_pkg.r_cons_entity_wf_info;
543 l_run_detail_id NUMBER(15);
544
545 BEGIN
546
547 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
548 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.CHECK_AGGREGATION_REQUIRED', '<<Enter for item key : ' || itemkey || '>>');
549 END IF;
550
551 l_entity_id := WF_ENGINE.GetItemAttrNumber(itemtype, itemkey, 'CONS_ENTITY', FALSE);
552 l_run_identifier := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'RUN_IDENTIFIER', FALSE);
553
554 gcs_cons_eng_utility_pkg.get_cons_entity_wf_info(itemtype, itemkey, cons_entity_wf_info);
555
556 SELECT COUNT(entry_id)
557 INTO l_entry_count
558 FROM gcs_cons_eng_run_dtls
559 WHERE run_name = l_run_identifier
560 AND NVL(entry_id,-1) > 0
561 AND consolidation_entity_id = l_entity_id
562 AND category_code <> 'AGGREGATION';
563
564 IF (l_entry_count > 0) THEN
565 result := 'COMPLETE:T';
566 ELSE
567 result := 'COMPLETE:F';
568
569 --Bugfix 5288100: If aggregation is not required then insert not applicable into gcs_cons_eng_run_dtls
570 GCS_CONS_ENG_RUN_DTLS_PKG.insert_row(p_run_name => cons_entity_wf_info.run_identifier,
571 p_consolidation_entity_id => cons_entity_wf_info.consolidation_entity,
572 p_category_code => 'AGGREGATION',
573 p_child_entity_id => cons_entity_wf_info.consolidation_entity,
574 p_cons_relationship_id => -1,
575 p_request_error_code => 'NOT_APPLICABLE',
576 p_run_detail_id => l_run_detail_id);
577
578 WF_ENGINE.SetItemAttrNumber(itemtype, itemkey, 'RUN_DETAIL_ID', l_run_detail_id);
579
580 END IF;
581
582 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
583 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.CHECK_AGGREGATION_REQUIRED', '<<Exit for item key : ' || itemkey || '>>');
584 END IF;
585
586 END check_aggregation_required;
587
588 PROCEDURE execute_aggregation( itemtype IN VARCHAR2,
589 itemkey IN VARCHAR2,
590 actid IN NUMBER,
591 funcmode IN varchar2,
592 result IN OUT NOCOPY varchar2)
593
594 IS
595
596 l_aggregation_required VARCHAR2(1) := 'N';
597 l_cons_relationship_id NUMBER(15);
598 l_run_detail_id NUMBER(15);
599 x_errbuf VARCHAR2(200);
600 x_retcode VARCHAR2(200);
601
602 cons_entity_wf_info gcs_cons_eng_utility_pkg.r_cons_entity_wf_info;
603 l_parameter_list gcs_cons_eng_utility_pkg.r_module_parameters;
604
605 BEGIN
606
607 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
608 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.EXECUTE_AGGREGATION', '<<Enter for item key : ' || itemkey || '>>');
609 END IF;
610
611
612 gcs_cons_eng_utility_pkg.get_cons_entity_wf_info(itemtype, itemkey, cons_entity_wf_info);
613
614 BEGIN
615 SELECT cons_relationship_id
616 INTO l_cons_relationship_id
617 FROM gcs_cons_relationships
618 WHERE child_entity_id = cons_entity_wf_info.consolidation_entity
619 AND dominant_parent_flag = 'Y'
620 AND hierarchy_id = cons_entity_wf_info.consolidation_hierarchy
621 AND cons_entity_wf_info.cal_period_end_date BETWEEN start_date AND NVL(end_date, cons_entity_wf_info.cal_period_end_date)
622 AND ROWNUM < 2;
623 EXCEPTION
624 WHEN OTHERS THEN
625 l_cons_relationship_id := -1;
626 END;
627
628
629 GCS_CONS_ENG_RUN_DTLS_PKG.insert_row(p_run_name => cons_entity_wf_info.run_identifier,
630 p_consolidation_entity_id => cons_entity_wf_info.consolidation_entity,
631 p_category_code => 'AGGREGATION',
632 p_child_entity_id => cons_entity_wf_info.consolidation_entity,
633 p_cons_relationship_id => l_cons_relationship_id,
634 p_request_error_code => 'IN_PROGRESS',
635 p_run_detail_id => l_run_detail_id);
636
637
638 SELECT DECODE(COUNT(*), 0, 'N', 'Y')
639 INTO l_parameter_list.stat_required
640 FROM gcs_cons_eng_run_dtls
641 WHERE run_name = cons_entity_wf_info.run_identifier
642 AND consolidation_entity_id = cons_entity_wf_info.consolidation_entity
643 AND NVL(stat_entry_id,0) > 0;
644
645 l_parameter_list.run_detail_id := l_run_detail_id;
649 l_parameter_list.cal_period_id := cons_entity_wf_info.cal_period_id;
646 l_parameter_list.hierarchy_id := cons_entity_wf_info.consolidation_hierarchy;
647 l_parameter_list.cons_relationship_id := l_cons_relationship_id;
648 l_parameter_list.cons_entity_id := cons_entity_wf_info.consolidation_entity;
650 l_parameter_list.period_end_date := cons_entity_wf_info.cal_period_end_date;
651 l_parameter_list.balance_type_code := cons_entity_wf_info.balance_type_code;
652 --Bugfix 5017120: Added support for additional data types
653 l_parameter_list.hierarchy_dataset_code := cons_entity_wf_info.hierarchy_dataset_code;
654
655 gcs_cons_eng_utility_pkg.execute_module('AGGREGATION', l_parameter_list, itemkey);
656
657 WF_ENGINE.SetItemAttrNumber(itemtype, itemkey, 'RUN_DETAIL_ID', l_run_detail_id);
658
659 --Bugfix 4874306: Eliminate calls to XML Generation in order to leverage data templates
660 --gcs_cons_eng_utility_pkg.submit_xml_ntf_program(
661 -- p_run_name => cons_entity_wf_info.run_identifier,
662 -- p_cons_entity_id => cons_entity_wf_info.consolidation_entity,
663 -- p_category_code => 'AGGREGATION',
664 -- p_run_detail_id => l_run_detail_id);
665
666 result := 'COMPLETE';
667
668 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
669 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.EXECUTE_AGGREGATION', '<<Exit for item key : ' || itemkey || '>>');
670 END IF;
671
672 END execute_aggregation;
673
674 PROCEDURE delete_flattened_relns(cons_entity_wf_info IN gcs_cons_eng_utility_pkg.r_cons_entity_wf_info)
675
676 IS PRAGMA AUTONOMOUS_TRANSACTION;
677
678 BEGIN
679
680 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
681 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.DELETE_FLATTENED_RELNS', '<<Enter>>');
682 END IF;
683
684 --Bugfix 4928211: For performance purposes deleting from gcs_flattened relations when parent node is fully complete with consolidation
685 DELETE FROM gcs_flattened_relns
686 WHERE run_name = cons_entity_wf_info.run_identifier;
687
688 COMMIT;
689
690 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
691 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.DELETE_FLATTENED_RELNS', '<<Exit>>');
692 END IF;
693
694 END;
695
696 PROCEDURE raise_completion_event( itemtype IN VARCHAR2,
697 itemkey IN VARCHAR2,
698 actid IN NUMBER,
699 funcmode IN varchar2,
700 result IN OUT NOCOPY varchar2)
701
702 IS
703
704 l_run_identifier VARCHAR2(200);
705 l_entity_id NUMBER(15);
706 l_event_name VARCHAR2(200) := 'oracle.apps.gcs.consolidation.engine.finishconsentitywf';
707 l_event_key VARCHAR2(2000);
708 l_parameter_list wf_parameter_list_t;
709 l_parent_entity_id NUMBER(15);
710 l_dependent_count NUMBER(15);
711 l_entry_id NUMBER(15);
712 l_stat_entry_id NUMBER(15);
713 cons_entity_wf_info gcs_cons_eng_utility_pkg.r_cons_entity_wf_info;
714 l_warning_exists VARCHAR2(1) := 'N';
715 l_status_code VARCHAR2(30);
716 l_run_detail_id NUMBER(15);
717 l_top_entity_id NUMBER;
718
719 BEGIN
720 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
721 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.RAISE_COMPLETION_EVENT', '<<Enter for item key : ' || itemkey || '>>');
722 END IF;
723
724 gcs_cons_eng_utility_pkg.get_cons_entity_wf_info(itemtype, itemkey, cons_entity_wf_info);
725
726
727 BEGIN
728 SELECT 'Y'
729 INTO l_warning_exists
730 FROM gcs_cons_eng_run_dtls
731 WHERE run_name = cons_entity_wf_info.run_identifier
732 AND consolidation_entity_id = cons_entity_wf_info.consolidation_entity
733 AND child_entity_id IS NULL
734 AND request_error_code NOT IN ('COMPLETED', 'NOT_APPLICABLE')
735 --STK: Added Condition for Excluding Category Code of Aggregation 5/6/03
736 AND category_code <> 'AGGREGATION'
737 AND ROWNUM < 2;
738 EXCEPTION
739 WHEN NO_DATA_FOUND THEN
740 l_warning_exists := 'N';
741 END;
742
743 IF (l_warning_exists = 'Y') THEN
744 l_status_code := 'WARNING';
745 ELSE
746 l_status_code := 'COMPLETED';
747 END IF;
748
749 gcs_cons_eng_runs_pkg.update_status(
750 p_run_name => cons_entity_wf_info.run_identifier,
751 p_most_recent_flag => 'Y',
752 p_status_code => l_status_code,
753 p_run_entity_id => cons_entity_wf_info.consolidation_entity,
754 p_end_time => sysdate);
755
756 SELECT top_entity_id
757 INTO l_top_entity_id
758 FROM gcs_hierarchies_b
759 WHERE hierarchy_id = cons_entity_wf_info.consolidation_hierarchy;
760
761
762 BEGIN
763 gcs_cons_eng_run_dtls_pkg.update_category_status(
767 p_status => l_status_code);
764 p_run_name => cons_entity_wf_info.run_identifier,
765 p_consolidation_entity_id => cons_entity_wf_info.consolidation_entity,
766 p_category_code => 'AGGREGATION',
768
769 gcs_eng_cp_utility_pkg.submit_xml_ntf_program(
770 p_execution_type => 'NTF_ONLY',
771 p_run_name => cons_entity_wf_info.run_identifier,
772 p_cons_entity_id => cons_entity_wf_info.consolidation_entity,
773 p_category_code => 'AGGREGATION',
774 p_run_detail_id => cons_entity_wf_info.run_detail_id);
775
776 IF (l_top_entity_id = cons_entity_wf_info.consolidation_entity) THEN
777 -- bugfix 5569522: pass p_analysis_cycle_id for launching business process.
778 submit_epb_data_transfer( p_hierarchy_id => cons_entity_wf_info.consolidation_hierarchy,
779 p_balance_type_code => cons_entity_wf_info.balance_type_code,
780 p_cal_period_id => cons_entity_wf_info.cal_period_id,
781 p_analysis_cycle_id => cons_entity_wf_info.analysis_cycle_id);
782 END IF;
783
784 --Bugfix 4928211: If this is the topmost parent for the specifc run then delete data from gcs_flattened_relns
785 SELECT parent_entity_id
786 INTO l_top_entity_id
787 FROM gcs_cons_eng_runs
788 WHERE run_name = cons_entity_wf_info.run_identifier
789 AND run_entity_id = cons_entity_wf_info.consolidation_entity;
790
791 IF (l_top_entity_id = -1) THEN
792 delete_flattened_relns(cons_entity_wf_info);
793 END IF;
794
795 SELECT entry_id,
796 stat_entry_id
797 INTO l_entry_id,
798 l_stat_entry_id
799 FROM gcs_cons_eng_run_dtls
800 WHERE run_detail_id = cons_entity_wf_info.run_detail_id;
801
802 EXCEPTION
803 WHEN NO_DATA_FOUND THEN
804 l_entry_id := -1;
805 l_stat_entry_id := -1;
806 END;
807
808 gcs_cons_impact_analysis_pkg.consolidation_completed( p_run_name => cons_entity_wf_info.run_identifier,
809 p_run_entity_id => cons_entity_wf_info.consolidation_entity,
810 p_cal_period_id => cons_entity_wf_info.cal_period_id,
811 p_cal_period_end_date => cons_entity_wf_info.cal_period_end_date,
812 p_hierarchy_id => cons_entity_wf_info.consolidation_hierarchy,
813 p_balance_type_code => cons_entity_wf_info.balance_type_code);
814
815 SELECT gcerd.run_detail_id
816 INTO l_run_detail_id
817 FROM gcs_cons_eng_run_dtls gcerd
818 WHERE gcerd.category_code = 'DATAPREPARATION'
819 AND gcerd.child_entity_id = cons_entity_wf_info.consolidation_entity
820 AND gcerd.run_name = cons_entity_wf_info.run_identifier;
821
822 gcs_cons_eng_run_dtls_pkg.update_entry_headers_async( p_run_detail_id => l_run_detail_id,
823 p_entry_id => l_entry_id,
824 p_stat_entry_id => l_stat_entry_id,
825 p_request_error_code => l_status_code,
826 p_bp_request_error_code => l_status_code);
827
828 result := 'COMPLETE';
829
830 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
831 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.RAISE_COMPLETION_EVENT', '<<Exit for item key : ' || itemkey || '>>');
832 END IF;
833
834 EXCEPTION
835 WHEN OTHERS THEN
836 result := 'COMPLETE';
837 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
838 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.RAISE_COMPLETION_EVENt', SQLERRM);
839 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.RAISE_COMPLETION_EVENT', '<<Exit for item key : ' || itemkey || '>>');
840 END IF;
841
842 END raise_completion_event;
843
844 PROCEDURE create_flattened_relns(cons_entity_wf_info IN gcs_cons_eng_utility_pkg.r_cons_entity_wf_info,
845 cons_entity_id IN NUMBER)
846
847 IS PRAGMA AUTONOMOUS_TRANSACTION;
848
849 BEGIN
850
851 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
852 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.CREATE_FLATTENED_RELNS', '<<Enter>>');
853 END IF;
854
855 --Bugfix 4928211: For performance benefit store all children of consolidation hierarchy into gcs_flattened_relns
856
857 INSERT INTO gcs_flattened_relns
858 (run_name,
859 parent_entity_id,
860 child_entity_id,
861 creation_date,
862 created_by,
863 last_update_date,
864 last_updated_by,
865 last_update_login,
866 object_version_number,
867 --Bugfix 5091093: Added consolidation type code
868 consolidation_type_code
869 )
870 SELECT cons_entity_wf_info.run_identifier,
871 cons_entity_id,
872 gcr.child_entity_id,
873 sysdate,
877 fnd_global.login_id,
874 fnd_global.user_id,
875 sysdate,
876 fnd_global.user_id,
878 1,
879 --Bugfix 5091093: Added consolidation type code
880 gtb.consolidation_type_code
881 FROM gcs_cons_relationships gcr,
882 gcs_treatments_b gtb
883 WHERE gtb.treatment_id(+) = gcr.treatment_id
884 START WITH gcr.parent_entity_id = cons_entity_id
885 AND gcr.hierarchy_id = cons_entity_wf_info.consolidation_hierarchy
886 --Bugfix 5192720: Added dominant parent flag join condition
887 AND gcr.dominant_parent_flag = 'Y'
888 AND cons_entity_wf_info.cal_period_end_date
889 BETWEEN gcr.start_date AND NVL(gcr.end_date, cons_entity_wf_info.cal_period_end_date)
890 CONNECT BY PRIOR gcr.child_entity_id = gcr.parent_entity_id
891 AND gcr.hierarchy_id = cons_entity_wf_info.consolidation_hierarchy
892 AND gcr.dominant_parent_flag = 'Y'
893 AND cons_entity_wf_info.cal_period_end_date
894 BETWEEN gcr.start_date AND NVL(gcr.end_date, cons_entity_wf_info.cal_period_end_date);
895
896 COMMIT;
897
898 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
899 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.CREATE_FLATTENED_RELNS', '<<Exit>>');
900 END IF;
901
902 END;
903
904 PROCEDURE spawn_cons_entity_process( itemtype IN VARCHAR2,
905 itemkey IN VARCHAR2,
906 actid IN NUMBER,
907 funcmode IN varchar2,
908 result IN OUT NOCOPY varchar2)
909
910 IS
911
912 TYPE t_childkey_list IS TABLE OF VARCHAR2(200);
913 l_childkey_list t_childkey_list := t_childkey_list(NULL);
914 l_child_key VARCHAR2(200);
915 counter NUMBER(15) := 0;
916 l_entities_to_process BOOLEAN := FALSE;
917 cons_entity_wf_info gcs_cons_eng_utility_pkg.r_cons_entity_wf_info;
918
919 CURSOR c_cons_entities (p_run_name IN VARCHAR2,
920 p_cons_entity_id IN NUMBER) IS
921 SELECT gcerd.child_entity_id,
922 gcerd.cons_relationship_id,
923 gcerd.run_detail_id,
924 fev.entity_name,
925 DECODE(geca_parent.currency_code, geca_child.currency_code, 'N', 'Y') translation_required
926 FROM gcs_cons_eng_run_dtls gcerd,
927 fem_entities_attr fea,
928 fem_entities_vl fev,
929 gcs_entity_cons_attrs geca_parent,
930 gcs_entity_cons_attrs geca_child,
931 gcs_cons_eng_runs gcer
932 WHERE gcerd.run_name = p_run_name
933 AND gcerd.consolidation_entity_id = p_cons_entity_id
934 AND gcerd.entry_id IS NULL
935 AND category_code = 'DATAPREPARATION'
936 AND gcerd.child_entity_id = fev.entity_id
937 AND gcerd.child_entity_id = fea.entity_id
938 AND fea.attribute_id = g_entity_type_attr
939 AND fea.version_id = g_entity_type_version
940 AND fea.dim_attribute_varchar_member IN ('C')
941 AND geca_parent.entity_id = p_cons_entity_id
942 AND geca_child.entity_id = fev.entity_id
943 AND geca_parent.hierarchy_id = geca_child.hierarchy_id
944 AND gcer.run_name = gcerd.run_name
945 AND gcer.run_entity_id = gcerd.consolidation_entity_id
946 AND gcer.hierarchy_id = geca_child.hierarchy_id;
947
948
949 BEGIN
950
951 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
952 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.SPAWN_CONS_ENTITY_PROCESS', '<<Exit for item key : ' || itemkey || '>>');
953 END IF;
954
955
956 gcs_cons_eng_utility_pkg.get_cons_entity_wf_info(itemtype, itemkey, cons_entity_wf_info);
957
958 FOR v_cons_entity IN c_cons_entities(cons_entity_wf_info.run_identifier, cons_entity_wf_info.consolidation_entity)
959 LOOP
960
961 counter := counter + 1;
962 SELECT to_char(sysdate,'DD-MM-RR HH:MI:SS')
963 INTO l_child_key
964 FROM dual;
965
966 l_child_key := v_cons_entity.entity_name || ' (' || cons_entity_wf_info.request_id || ')';
967
968 l_childkey_list.extend(1);
969 l_childkey_list(counter) := l_child_key;
970
971 GCS_CONS_ENG_RUNS_PKG.insert_row
972 (
973 p_run_name => cons_entity_wf_info.run_identifier,
974 p_hierarchy_id => cons_entity_wf_info.consolidation_hierarchy,
975 p_process_method_code => cons_entity_wf_info.process_method,
976 p_run_entity_id => v_cons_entity.child_entity_id,
977 p_cal_period_id => cons_entity_wf_info.cal_period_id,
978 p_balance_type_code => cons_entity_wf_info.balance_type_code,
979 p_parent_entity_id => cons_entity_wf_info.consolidation_entity,
980 p_item_key => l_child_key,
981 p_request_id => cons_entity_wf_info.request_id
982 );
983
984 --Bugfix 5197891: Assign appropriate user rather than null value
985 WF_ENGINE.CreateProcess(g_cons_item_type, l_childkey_list(counter), g_cons_entity_process, l_childkey_list(counter), FND_GLOBAL.USER_NAME);
986 WF_ENGINE.SetItemAttrNumber(g_cons_item_type, l_childkey_list(counter), 'CONS_HIERARCHY', cons_entity_wf_info.consolidation_hierarchy);
987 WF_ENGINE.SetItemAttrNumber(g_cons_item_type, l_childkey_list(counter), 'CONS_ENTITY', v_cons_entity.child_entity_id);
988 WF_ENGINE.SetItemAttrText(g_cons_item_type, l_childkey_list(counter), 'RUN_IDENTIFIER', cons_entity_wf_info.run_identifier);
992 WF_ENGINE.SetItemAttrText(g_cons_item_type, l_childkey_list(counter), 'BALANCE_TYPE_CODE', cons_entity_wf_info.balance_type_code);
989 WF_ENGINE.SetItemAttrText(g_cons_item_type, l_childkey_list(counter), 'CAL_PERIOD', cons_entity_wf_info.cal_period_id);
990 WF_ENGINE.SetItemAttrText(g_cons_item_type, l_childkey_list(counter), 'PROCESS_METHOD', cons_entity_wf_info.process_method);
991 WF_ENGINE.SetItemAttrText(g_cons_item_type, l_childkey_list(counter), 'CAL_PERIOD_END_DATE', cons_entity_wf_info.cal_period_end_date);
993 WF_ENGINE.SetItemAttrText(g_cons_item_type, l_childkey_list(counter), 'TRANSLATION_REQUIRED', v_cons_entity.translation_required);
994 WF_ENGINE.SetItemAttrNumber(g_cons_item_type, l_childkey_list(counter), 'CONC_REQUEST_ID', cons_entity_wf_info.request_id);
995 --Bugfix 5017120: Added support for additional data types
996 WF_ENGINE.SetItemAttrNumber(g_cons_item_type, l_childkey_list(counter), 'SOURCE_DATASET_CODE', cons_entity_wf_info.source_dataset_code);
997 WF_ENGINE.SetItemAttrNumber(g_cons_item_type, l_childkey_list(counter), 'HIERARCHY_DATASET_CODE', cons_entity_wf_info.hierarchy_dataset_code);
998 WF_ENGINE.Set_Item_Parent(g_cons_item_type, l_childkey_list(counter), itemtype, itemkey,'WAITFORFLOW-1');
999
1000 l_entities_to_process := TRUE;
1001
1002 --Bugfix 4928211: For performance benefit store all children of consolidation hierarchy into gcs_flattened_relns
1003 create_flattened_relns(cons_entity_wf_info,
1004 v_cons_entity.child_entity_id);
1005
1006 END LOOP;
1007
1008 WHILE (counter > 0)
1009 LOOP
1010 l_entities_to_process := TRUE;
1011 WF_ENGINE.StartProcess(g_cons_item_type, l_childkey_list(counter));
1012 counter := counter - 1;
1013 END LOOP;
1014
1015 IF (l_entities_to_process) THEN
1016 result := 'COMPLETE:T';
1017 ELSE
1018 result := 'COMPLETE:F';
1019 END IF;
1020
1021 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1022 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.SPAWN_CONS_ENTITY_PROCESS', '<<Exit for item key : ' || itemkey || '>>');
1023 END IF;
1024
1025 END spawn_cons_entity_process;
1026
1027 PROCEDURE initialize_cons_process( itemtype IN VARCHAR2,
1028 itemkey IN VARCHAR2,
1029 actid IN NUMBER,
1030 funcmode IN varchar2,
1031 result IN OUT NOCOPY varchar2)
1032
1033 IS
1034 cons_entity_wf_info gcs_cons_eng_utility_pkg.r_cons_entity_wf_info;
1035 l_run_detail_id NUMBER(15);
1036
1037 CURSOR c_categories_to_process IS
1038 SELECT cons_entity_wf_info.run_identifier,
1039 cons_entity_wf_info.consolidation_entity,
1040 DECODE(category_code, 'DATAPREPARATION', 'IN_PROGRESS', 'NOT_STARTED') request_error_code,
1041 category_code
1042 FROM gcs_categories_b
1043 WHERE category_number > 0
1044 AND enabled_flag = 'Y';
1045
1046 BEGIN
1047
1048 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1049 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.INITIALIZE_CONS_PROCESS', '<<Enter for item key : ' || itemkey || '>>');
1050 END IF;
1051
1052 gcs_cons_eng_utility_pkg.get_cons_entity_wf_info(itemtype, itemkey, cons_entity_wf_info);
1053
1054 FOR v_categories_to_process IN c_categories_to_process LOOP
1055 gcs_cons_eng_run_dtls_pkg.insert_row(
1056 p_run_name => cons_entity_wf_info.run_identifier,
1057 p_consolidation_entity_id => cons_entity_wf_info.consolidation_entity,
1058 p_category_code => v_categories_to_process.category_code,
1059 p_request_error_code => v_categories_to_process.request_error_code,
1060 p_run_detail_id => l_run_detail_id);
1061 END LOOP;
1062
1063 result := 'COMPLETE';
1064
1065 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1066 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.INITIALIZE_CONS_PROCESS', '<<Exit for item key : ' || itemkey || '>>');
1067 END IF;
1068
1069 END initialize_cons_process;
1070
1071 PROCEDURE check_cons_entity_status( itemtype IN VARCHAR2,
1072 itemkey IN VARCHAR2,
1073 actid IN NUMBER,
1074 funcmode IN varchar2,
1075 result IN OUT NOCOPY varchar2)
1076
1077 IS
1078
1079 cons_entity_wf_info gcs_cons_eng_utility_pkg.r_cons_entity_wf_info;
1080 l_prior_run_name VARCHAR2(240);
1081 l_status_code VARCHAR2(30);
1082 l_locked_flag VARCHAR2(1);
1083 l_impacted_flag VARCHAr2(1);
1084
1085 BEGIN
1086
1087 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1088 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.CHECK_CONS_ENTITY_STATUS', '<<Enter for item key : ' || itemkey || '>>');
1089 END IF;
1090
1091 gcs_cons_eng_utility_pkg.get_cons_entity_wf_info(itemtype, itemkey, cons_entity_wf_info);
1092
1093 SELECT run_name,
1094 status_code,
1095 locked_flag,
1096 impacted_flag
1097 INTO l_prior_run_name,
1098 l_status_code,
1099 l_locked_flag,
1100 l_impacted_flag
1101 FROM gcs_cons_eng_runs
1102 WHERE run_entity_id = cons_entity_wf_info.consolidation_entity
1103 -- Bugfix 3659810 : Added condition for hierarchy_id
1104 AND hierarchy_id = cons_entity_wf_info.consolidation_hierarchy
1108 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'PRIOR_RUN_NAME', l_prior_run_name);
1105 AND most_recent_flag = 'X'
1106 AND cal_period_id = cons_entity_wf_info.cal_period_id;
1107
1109 result := 'COMPLETE:F';
1110
1111 --Bugfix 3750740 : Update Prior Run To MOST_RECENT_FLAG = 'N'
1112 gcs_cons_eng_runs_pkg.update_status ( p_run_name => l_prior_run_name,
1113 p_most_recent_flag => 'N',
1114 p_status_code => NULL,
1115 p_run_entity_id => cons_entity_wf_info.consolidation_entity);
1116
1117 gcs_cons_eng_runs_pkg.update_status ( p_run_name => cons_entity_wf_info.run_identifier,
1118 p_most_recent_flag => 'Y',
1119 p_status_code => 'IN_PROGRESS',
1120 p_run_entity_id => cons_entity_wf_info.consolidation_entity);
1121
1122
1123 IF (l_locked_flag = 'Y' OR (cons_entity_wf_info.process_method = 'INCREMENTAL' AND l_impacted_flag = 'N')) THEN
1124 result := 'COMPLETE:T';
1125 END IF;
1126
1127
1128 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1129 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.CHECK_CONS_ENTITY_STATUS', '<<Exit for item key : ' || itemkey || '>>');
1130 END IF;
1131
1132 EXCEPTION
1133 WHEN NO_DATA_FOUND THEN
1134 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'PRIOR_RUN_NAME', 'NO_PRIOR_RUN');
1135
1136 gcs_cons_eng_runs_pkg.update_status ( p_run_name => cons_entity_wf_info.run_identifier,
1137 p_most_recent_flag => 'Y',
1138 p_status_code => 'IN_PROGRESS',
1139 p_run_entity_id => cons_entity_wf_info.consolidation_entity);
1140 result := 'COMPLETE:F';
1141 END check_cons_entity_status;
1142
1143 PROCEDURE update_run_information( cons_entity_wf_info IN gcs_cons_eng_utility_pkg.r_cons_entity_wf_info,
1144 p_run_detail_id IN OUT NOCOPY NUMBER)
1145
1146 IS PRAGMA AUTONOMOUS_TRANSACTION;
1147
1148 l_status_code VARCHAR2(30);
1149 l_impacted_flag VARCHAR2(1);
1150 l_locked_flag VARCHAR2(1);
1151
1152 BEGIN
1153
1154 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1155 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.UPDATE_RUN_INFORMATION.begin', '<<Enter>>');
1156 END IF;
1157
1158 UPDATE gcs_cons_eng_runs
1159 SET most_recent_flag = 'Y'
1160 WHERE run_name = cons_entity_wf_info.prior_run_identifier
1161 AND run_entity_id = cons_entity_wf_info.consolidation_entity
1162 RETURNING status_code, impacted_flag, locked_flag INTO l_status_code, l_impacted_flag, l_locked_flag;
1163
1164 UPDATE gcs_cons_eng_runs
1165 SET most_recent_flag = 'N',
1166 associated_run_name = cons_entity_wf_info.prior_run_identifier,
1167 status_code = l_status_code,
1168 impacted_flag = l_impacted_flag,
1169 locked_flag = l_locked_flag,
1170 end_time = sysdate,
1171 last_updated_by = FND_GLOBAL.USER_ID,
1172 last_update_date = sysdate,
1173 last_update_login = FND_GLOBAL.LOGIN_ID
1174 WHERE run_name = cons_entity_wf_info.run_identifier
1175 AND run_entity_id = cons_entity_wf_info.consolidation_entity;
1176
1177 UPDATE gcs_cons_eng_runs gcer
1178 SET gcer.most_recent_flag = 'Y'
1179 WHERE gcer.most_recent_flag = 'X'
1180 AND gcer.hierarchy_id = cons_entity_wf_info.consolidation_hierarchy
1181 AND gcer.cal_period_id = cons_entity_wf_info.cal_period_id
1182 AND gcer.balance_type_code = cons_entity_wf_info.balance_type_code
1183 AND EXISTS ( SELECT 'X'
1184 FROM gcs_cons_relationships gcr
1185 WHERE gcr.child_entity_id = gcer.run_entity_id
1186 START WITH gcr.parent_entity_id = cons_entity_wf_info.consolidation_entity
1187 AND gcr.hierarchy_id = cons_entity_wf_info.consolidation_hierarchy
1188 AND cons_entity_wf_info.cal_period_end_date
1189 BETWEEN gcr.start_date AND NVL(gcr.end_date,
1190 cons_entity_wf_info.cal_period_end_date)
1191 CONNECT BY PRIOR gcr.child_entity_id = gcr.parent_entity_id
1192 AND gcr.hierarchy_id = cons_entity_wf_info.consolidation_hierarchy
1193 AND gcr.dominant_parent_flag = 'Y'
1194 AND cons_entity_wf_info.cal_period_end_date
1195 BETWEEN gcr.start_date AND NVL(gcr.end_date,
1196 cons_entity_wf_info.cal_period_end_date));
1197
1198
1199 UPDATE gcs_cons_eng_run_dtls gcerd
1200 SET (entry_id,
1201 stat_entry_id,
1202 request_error_code,
1203 bp_request_error_code
1204 ) =
1205 (SELECT gcerd_inner.entry_id,
1206 gcerd_inner.stat_entry_id,
1207 gcerd_inner.request_error_code,
1208 gcerd_inner.bp_request_error_code
1209 FROM gcs_cons_eng_run_dtls gcerd_inner
1210 WHERE gcerd_inner.run_name = cons_entity_wf_info.prior_run_identifier
1211 AND gcerd_inner.consolidation_entity_id = cons_entity_wf_info.consolidation_entity
1212 AND gcerd_inner.category_code = 'AGGREGATION'
1213 AND gcerd_inner.child_entity_id = cons_entity_wf_info.consolidation_entity)
1214 WHERE gcerd.run_name = cons_entity_wf_info.run_identifier
1215 AND gcerd.category_code = 'DATAPREPARATION'
1216 AND gcerd.child_entity_id = cons_entity_wf_info.consolidation_entity
1217 RETURN run_detail_id INTO p_run_detail_id;
1218
1219 COMMIT;
1220
1221 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1222 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.UPDATE_RUN_INFORMATION.end', '<<Exit>>');
1223 END IF;
1224 END;
1225
1226 PROCEDURE retrieve_prior_runs( itemtype IN VARCHAR2,
1227 itemkey IN VARCHAR2,
1228 actid IN NUMBER,
1229 funcmode IN varchar2,
1230 result IN OUT NOCOPY varchar2)
1231
1232 IS
1233
1234 cons_entity_wf_info gcs_cons_eng_utility_pkg.r_cons_entity_wf_info;
1235 l_prior_run_name VARCHAR2(200);
1236 l_status_code VARCHAR2(30);
1237 l_run_detail_id NUMBER(15);
1238
1239 BEGIN
1240
1241 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1242 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.RETRIEVE_PRIOR_RUNS', '<<Enter for item key : ' || itemkey || '>>');
1243 END IF;
1244
1245
1246 gcs_cons_eng_utility_pkg.get_cons_entity_wf_info(itemtype, itemkey, cons_entity_wf_info);
1247
1248 update_run_information(cons_entity_wf_info,
1249 l_run_detail_id);
1250
1251 WF_ENGINE.SetItemAttrNumber(itemtype, itemkey, 'RUN_DETAIL_ID', l_run_detail_id);
1252
1253 result := 'COMPLETE';
1254
1255 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1256 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.RETRIEVE_PRIOR_RUNS', '<<Exit for item key : ' || itemkey || '>>');
1257 END IF;
1258
1259 END retrieve_prior_runs;
1260
1261
1262 END GCS_CONS_ENGINE_PKG;
1263