[Home] [Help]
PACKAGE BODY: APPS.GCS_RAISE_EVENT_PKG
Source
1 PACKAGE BODY GCS_RAISE_EVENT_PKG as
2 /* $Header: gcs_raise_eventb.pls 120.11 2007/12/05 14:38:11 rthati noship $ */
3
4 g_api VARCHAR2(200) := 'gcs.plsql.GCS_RAISE_EVENT_PKG';
5
6 PROCEDURE raise_hierarchy_alt_event (p_pre_cons_relationship_id IN NUMBER,
7 p_post_cons_relationship_id IN NUMBER,
8 p_trx_type_code IN VARCHAR2,
9 p_trx_date_day IN NUMBER,
10 p_trx_date_month IN NUMBER,
11 p_trx_date_year IN NUMBER,
12 p_hidden_flag IN VARCHAR2,
13 p_intermediate_trtmnt_id IN NUMBER,
14 p_intermediate_pct_owned IN NUMBER)
15
16 IS
17
18 l_success VARCHAR2(30);
19
20 BEGIN
21
22 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
23 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.RAISE_HIERARCHY_ALT_EVENT.begin', '<<Enter>>');
24 END IF;
25
26 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
27 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.RAISE_HIERARCHY_ALT_EVENT', 'Pre-Cons Relationship ID : ' || p_pre_cons_relationship_id);
28 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.RAISE_HIERARCHY_ALT_EVENT', 'Post-Cons Relationship ID : ' || p_post_cons_relationship_id);
29 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.RAISE_HIERARCHY_ALT_EVENT', 'Transaction Type Code : ' || p_trx_type_code);
30 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.RAISE_HIERARCHY_ALT_EVENT', 'Transaction Date Day : ' || p_trx_date_day);
31 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.RAISE_HIERARCHY_ALT_EVENT', 'Transaction Date Month : ' || p_trx_date_month);
32 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.RAISE_HIERARCHY_ALT_EVENT', 'Transaction Date Year : ' || p_trx_date_year);
33 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.RAISE_HIERARCHY_ALT_EVENT', 'Hidden Flag : ' || p_hidden_flag);
34 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.RAISE_HIERARCHY_ALT_EVENT', 'Intermediate Trtmnt Id : ' || p_intermediate_trtmnt_id);
35 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.RAISE_HIERARCHY_ALT_EVENT', 'Intermeidate Pct Owned : ' || p_intermediate_pct_owned);
36 END IF;
37
38 l_success := gcs_cons_impact_analysis_pkg.hierarchy_altered
39 (p_pre_cons_relationship_id => p_pre_cons_relationship_id,
40 p_post_cons_relationship_id => p_post_cons_relationship_id,
41 p_trx_type_code => p_trx_type_code,
42 p_trx_date_day => p_trx_date_day,
43 p_trx_date_month => p_trx_date_month,
44 p_trx_date_year => p_trx_date_year,
45 p_hidden_flag => p_hidden_flag,
46 p_intermediate_trtmnt_id => p_intermediate_trtmnt_id,
47 p_intermediate_pct_owned => p_intermediate_pct_owned);
48
49 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
50 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.RAISE_HIERARCHY_ALT_EVENT.begin', '<<Exit>>');
51 END IF;
52
53 END;
54
55 -- bugfix 5569522: Added parameter p_analysis_cycle_id that will be passed
56 -- to the execute_consolidation cocurrent program.
57 PROCEDURE raise_execute_eng_event (p_consolidation_hierarchy IN NUMBER,
58 p_consolidation_entity IN NUMBER,
59 p_run_identifier IN VARCHAR2,
60 p_cal_period_id IN VARCHAR2,
61 p_balance_type_code IN VARCHAR2,
62 p_process_method IN VARCHAR2,
63 p_request_id OUT NOCOPY NUMBER,
64 p_analysis_cycle_id IN NUMBER)
65 IS
66 l_cal_period_end_date VARCHAR2(30);
67 l_end_date_attribute_id NUMBER :=
68 gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE').attribute_id;
69 l_end_date_version_id NUMBER :=
70 gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE').version_id;
71 BEGIN
72
73 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
74 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.RAISE_EXECUTE_ENG_EVENT.begin', '<<Enter>>');
75 END IF;
76
77 SELECT to_char(date_assign_value, 'DD-MM-RR')
78 INTO l_cal_period_end_date
79 FROM fem_cal_periods_attr
80 WHERE attribute_id = l_end_date_attribute_id
81 AND version_id = l_end_date_version_id
82 AND cal_period_id = p_cal_period_id;
83
84 -- Bugfix : Change All Icons to Not Started
85
86 UPDATE gcs_cons_eng_runs
87 SET most_recent_flag = 'X'
88 WHERE most_recent_flag = 'Y'
89 AND run_entity_id = p_consolidation_entity
90 AND hierarchy_id = p_consolidation_hierarchy
91 AND cal_period_id = p_cal_period_id
92 AND balance_type_code = p_balance_type_code;
93
94 UPDATE gcs_cons_eng_runs gcer
95 SET gcer.most_recent_flag = 'X'
96 WHERE gcer.most_recent_flag = 'Y'
97 AND gcer.hierarchy_id = p_consolidation_hierarchy
98 AND gcer.cal_period_id = p_cal_period_id
99 AND gcer.balance_type_code = p_balance_type_code
100 AND EXISTS (SELECT 'X'
101 FROM gcs_cons_relationships gcr
102 WHERE gcr.child_entity_id = gcer.run_entity_id
103 START WITH gcr.parent_entity_id = p_consolidation_entity
104 AND gcr.hierarchy_id = p_consolidation_hierarchy
105 AND TO_DATE(l_cal_period_end_date, 'DD-MM-RR')
106 BETWEEN gcr.start_date AND NVL(gcr.end_date,
107 TO_DATE(l_cal_period_end_date, 'DD-MM-RR'))
108 CONNECT BY PRIOR gcr.child_entity_id = gcr.parent_entity_id
109 AND gcr.hierarchy_id = p_consolidation_hierarchy
110 -- Bugfix 4122843: Added dominant parent flag condition
111 AND gcr.dominant_parent_flag = 'Y'
112 AND TO_DATE(l_cal_period_end_date, 'DD-MM-RR')
113 BETWEEN gcr.start_date AND NVL(gcr.end_date,
114 TO_DATE(l_cal_period_end_date, 'DD-MM-RR')));
115
116 -- bugfix 5569522: pass p_analysis_cycle_id as argument to the concurrent request.
117 p_request_id := fnd_request.submit_request(
118 application => 'GCS',
119 program => 'FCH_SUBMIT_CONSOLIDATION',
120 sub_request => FALSE,
121 argument1 => p_run_identifier,
122 argument2 => p_consolidation_hierarchy,
123 argument3 => p_consolidation_entity,
124 argument4 => p_cal_period_id,
125 argument5 => p_balance_type_code,
126 argument6 => NVL(p_process_method,'FULL'),
127 argument7 => 'N',
128 argument8 => p_analysis_cycle_id);
129
130 COMMIT;
131
132 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
133 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.RAISE_EXECUTE_ENG_EVENT.end', '<<Exit>>');
134 END IF;
135
136 END;
137
138
139
140 PROCEDURE raise_execute_eng_event (p_consolidation_hierarchy IN NUMBER,
141 p_consolidation_entity IN NUMBER,
142 p_run_identifier IN VARCHAR2,
143 p_cal_period_id IN VARCHAR2,
144 p_balance_type_code IN VARCHAR2,
145 p_process_method IN VARCHAR2)
146
147 IS
148 l_event_key VARCHAR2(200);
149 l_cal_period_end_date VARCHAR2(30);
150 l_threshold NUMBER;
151 l_end_date_attribute_id NUMBER :=
152 gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE').attribute_id;
153 l_end_date_version_id NUMBER :=
154 gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE').version_id;
155 BEGIN
156
157 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
158 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.RAISE_EXECUTE_ENG_EVENT.begin', '<<Enter>>');
159 END IF;
160
161 SELECT entity_name || ' on ' || to_char(sysdate, 'DD-MM-RR HH24:MI:SS')
162 INTO l_event_key
163 FROM fem_entities_vl
164 WHERE entity_id = p_consolidation_entity;
165
166 SELECT to_char(date_assign_value, 'DD-MM-RR')
167 INTO l_cal_period_end_date
168 FROM fem_cal_periods_attr
169 WHERE attribute_id = l_end_date_attribute_id
170 AND version_id = l_end_date_version_id
171 AND cal_period_id = p_cal_period_id;
172
173 GCS_CONS_ENG_RUNS_PKG.insert_row
174 (
175 p_run_name => p_run_identifier,
176 p_hierarchy_id => p_consolidation_hierarchy,
177 p_process_method_code => NVL(p_process_method, 'FULL'),
178 p_run_entity_id => p_consolidation_entity,
179 p_cal_period_id => p_cal_period_id,
180 p_balance_type_code => p_balance_type_code,
181 p_parent_entity_id => -1,
182 p_item_key => l_event_key,
183 p_request_id => -1
184 );
185
186 COMMIT;
187
188 -- Bugfix : Change All Icons to Not Started
189
190 UPDATE gcs_cons_eng_runs
191 SET most_recent_flag = 'X'
192 WHERE most_recent_flag = 'Y'
193 AND run_entity_id = p_consolidation_entity
194 AND hierarchy_id = p_consolidation_hierarchy
195 AND cal_period_id = p_cal_period_id
196 AND balance_type_code = p_balance_type_code;
197
198 UPDATE gcs_cons_eng_runs gcer
199 SET gcer.most_recent_flag = 'X'
200 WHERE gcer.most_recent_flag = 'Y'
201 AND gcer.hierarchy_id = p_consolidation_hierarchy
202 AND gcer.cal_period_id = p_cal_period_id
203 AND gcer.balance_type_code = p_balance_type_code
204 AND EXISTS (SELECT 'X'
205 FROM gcs_cons_relationships gcr
206 WHERE gcr.child_entity_id = gcer.run_entity_id
207 START WITH gcr.parent_entity_id = p_consolidation_entity
208 AND gcr.hierarchy_id = p_consolidation_hierarchy
209 AND TO_DATE(l_cal_period_end_date, 'DD-MM-RR')
210 BETWEEN gcr.start_date AND NVL(gcr.end_date,
211 TO_DATE(l_cal_period_end_date, 'DD-MM-RR'))
212 CONNECT BY PRIOR gcr.child_entity_id = gcr.parent_entity_id
213 -- Bugfix 4122843: Added dominant parent flag
214 AND gcr.dominant_parent_flag = 'Y'
215 AND gcr.hierarchy_id = p_consolidation_hierarchy
216 AND TO_DATE(l_cal_period_end_date, 'DD-MM-RR')
217 BETWEEN gcr.start_date AND NVL(gcr.end_date,
218 TO_DATE(l_cal_period_end_date, 'DD-MM-RR')));
219
220 -- Bugfix 3629541 : Set the Workflow Threshold to -1 to Defer the Process to the Background Engine. Remove
221 -- all thresholds from the Workflow Definition
222
223 l_threshold := WF_ENGINE.THRESHOLD;
224 WF_ENGINE.THRESHOLD := -1;
225
226 --Bugfix 5197891: Assign the correct owner rather than null
227 WF_ENGINE.CreateProcess('GCSENGNE', l_event_key, 'CONS_ENTITY_PROCESS', l_event_key, FND_GLOBAL.USER_NAME);
228 WF_ENGINE.SetItemAttrNumber('GCSENGNE', l_event_key, 'CONS_HIERARCHY', p_consolidation_hierarchy);
229 WF_ENGINE.SetItemAttrNumber('GCSENGNE', l_event_key, 'CONS_ENTITY', p_consolidation_entity);
230 WF_ENGINE.SetItemAttrText('GCSENGNE', l_event_key, 'RUN_IDENTIFIER', p_run_identifier);
231 WF_ENGINE.SetItemAttrText('GCSENGNE', l_event_key, 'CAL_PERIOD', p_cal_period_id);
232 WF_ENGINE.SetItemAttrText('GCSENGNE', l_event_key, 'PROCESS_METHOD', NVL(p_process_method, 'FULL'));
233 WF_ENGINE.SetItemAttrText('GCSENGNE', l_event_key, 'CAL_PERIOD_END_DATE', l_cal_period_end_date);
234 WF_ENGINE.SetItemAttrText('GCSENGNE', l_event_key, 'BALANCE_TYPE_CODE', p_balance_type_code);
235 WF_ENGINE.StartProcess('GCSENGNE', l_event_key);
236
237 COMMIT;
238
239 WF_ENGINE.THRESHOLD := l_threshold;
240
241 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
242 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.RAISE_EXECUTE_ENG_EVENT.end', '<<Exit>>');
243 END IF;
244
245 END;
246
247
248 -- bugfix 5569522: added parameter p_analysis_cycle_id that will be passed to
249 -- the consolidation workflow to launch the business process.
250 PROCEDURE execute_consolidation (x_retcode OUT NOCOPY VARCHAR2,
251 x_errbuf OUT NOCOPY VARCHAR2,
252 p_run_identifier IN OUT NOCOPY VARCHAR2,
253 p_consolidation_hierarchy IN NUMBER,
254 p_consolidation_entity IN NUMBER,
255 p_cal_period_id IN VARCHAR2,
256 p_balance_type_code IN VARCHAR2,
257 p_process_method IN VARCHAR2,
258 p_called_via_srs IN VARCHAR2,
259 p_analysis_cycle_id IN NUMBER)
260 IS
261
262 l_event_key VARCHAR2(200);
263 l_cal_period_end_date VARCHAR2(30);
264 l_threshold NUMBER;
265 l_end_date_attribute_id NUMBER :=
266 gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE').attribute_id;
267 l_end_date_version_id NUMBER :=
268 gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE').version_id;
269 l_source_dataset_code NUMBER;
270 l_hierarchy_dataset_code NUMBER;
271
272 --Bugfix 5505707: Add variables to store information required for default member setup
273 l_dimension_info gcs_utility_pkg.t_hash_gcs_dimension_info;
274 l_column_name VARCHAR2(30);
275 l_value_set_tokens VARCHAR2(4000);
276 l_value_set_name VARCHAR2(150);
277 l_error_message VARCHAR2(32767);
278 l_ret_status BOOLEAN;
279 l_process_identifier_exists NUMBER:=0;
280
281 BEGIN
282
283 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
284 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.EXECUTE_CONSOLIDATION.begin', '<<Enter>>');
285 END IF;
286 --Bugfix 6195807: To check if the process_identifier is already exists in gcs_cons_eng_runs.
287 --This fix is for running REQUEST SET.
288 BEGIN
289 SELECT 1
290 INTO l_process_identifier_exists
291 FROM gcs_cons_eng_runs
292 WHERE run_name=p_run_identifier;
293
294 IF l_process_identifier_exists=1 THEN
295 SELECT gcs_cons_eng_run_dtls_s.NEXTVAL
296 INTO p_run_identifier
297 FROM dual;
298 END IF;
299 EXCEPTION
300 WHEN NO_DATA_FOUND THEN
301 NULL;
302 END;
303 --Bugfix 6195807: To check if the process_identifier is already exists in gcs_cons_eng_runs.
304 fnd_file.put_line(fnd_file.log, 'Beginning Consolidation Submission Execution');
305
306 fnd_file.put_line(fnd_file.log, '<<Parameter Listings>>');
307 fnd_file.put_line(fnd_file.log, 'Consolidation Hierarchy : ' || p_consolidation_hierarchy);
308 fnd_file.put_line(fnd_file.log, 'Consolidation Entity : ' || p_consolidation_entity);
309 fnd_file.put_line(fnd_file.log, 'Process Identifier : ' || p_run_identifier);
310 fnd_file.put_line(fnd_file.log, 'Calendar Period : ' || p_cal_period_id);
311 fnd_file.put_line(fnd_file.log, 'Balance Type : ' || p_balance_type_code);
312 fnd_file.put_line(fnd_file.log, 'Process Method : ' || p_process_method);
313 fnd_file.put_line(fnd_file.log, 'Called via SRS : ' || p_called_via_srs);
314 fnd_file.put_line(fnd_file.log, 'Analysis Cycle Id : ' || p_analysis_cycle_id);
315 fnd_file.put_line(fnd_file.log, '<<End of Parameter Listings>>');
316
317 --Bugfix 5505707: Validating Default Member Setup
318 fnd_file.put_line(fnd_file.log, '<<Beginning Validation of Default Member Setup>>');
319 l_dimension_info := gcs_utility_pkg.g_gcs_dimension_info;
320 l_column_name := l_dimension_info.FIRST;
321
322 WHILE (l_column_name <= l_dimension_info.LAST) LOOP
323
324 IF ((l_dimension_info(l_column_name).required_for_gcs = 'N') AND
325 (l_dimension_info(l_column_name).required_for_fem = 'Y') AND
326 (l_dimension_info(l_column_name).default_value IS NULL)) THEN
327
328 SELECT value_set_name
329 INTO l_value_set_name
330 FROM fem_value_sets_vl
331 WHERE value_set_id = l_dimension_info(l_column_name).associated_value_set_id;
332
333 IF (l_value_set_tokens IS NULL) THEN
334 l_value_set_tokens := l_value_set_name;
335 ELSE
336 l_value_set_tokens := l_value_set_tokens || ', ' || l_value_set_name;
337 END IF;
338 END IF;
339
340 l_column_name := l_dimension_info.NEXT(l_column_name);
341 END LOOP;
342
343 IF (LENGTH(l_value_set_tokens) <> 0) THEN
344 fnd_message.set_name('GCS', 'GCS_CONS_PROC_INV_VALUESETS');
345 fnd_message.set_token('VALUESETS', l_value_set_tokens);
346 l_error_message := fnd_message.get;
347
348 fnd_file.put_line(fnd_file.log, '<<<<Beginning of Validation Error>>>>>');
349 fnd_file.put_line(fnd_file.log, l_error_message);
350 fnd_file.put_line(fnd_file.log, '<<<<End of Validation Error>>>>>');
351
352 l_ret_status := fnd_concurrent.set_completion_status(status => 'ERROR',
353 message => l_error_message);
354 GOTO endofprogram;
355 END IF;
356 --End of Bugfix 5505707: Validating Default Member Setup
357
358 fnd_file.put_line(fnd_file.log, '<<End Validation of Default Member Setup>>');
359
360 --Bugfix 5017120: Added support for additional data types
361 fnd_file.put_line(fnd_file.log, '<<Data Type Specific Parameters>>');
362
363 --Bugfix 5505707: Added validation for source dataset and hierarchy dataset
364 BEGIN
365 SELECT source_dataset_code
366 INTO l_source_dataset_code
367 FROM gcs_data_type_codes_b
368 WHERE data_type_code = p_balance_type_code;
369
370 SELECT dataset_code
371 INTO l_hierarchy_dataset_code
372 FROM gcs_dataset_codes
373 WHERE hierarchy_id = p_consolidation_hierarchy
374 AND balance_type_code = p_balance_type_code;
375
376 fnd_file.put_line(fnd_file.log, 'Source Dataset : ' || l_source_dataset_code);
377 fnd_file.put_line(fnd_file.log,' Hierarchy Dataset : ' || l_hierarchy_dataset_code);
378 EXCEPTION
379 WHEN OTHERS THEN
380 IF (l_source_dataset_code IS NULL) THEN
381 fnd_message.set_name('GCS', 'GCS_CONS_PROC_INV_SRC_DATASET');
382 l_error_message := fnd_message.get;
383 ELSIF (l_hierarchy_dataset_code IS NULL) THEN
384 fnd_message.set_name('GCS', 'GCS_CONS_PROC_INV_HIER_DATASET');
385 l_error_message := fnd_message.get;
386 END IF;
387
388 fnd_file.put_line(fnd_file.log, '<<<<Beginning of Data Types Error>>>>>');
389 fnd_file.put_line(fnd_file.log, l_error_message);
390 fnd_file.put_line(fnd_file.log, '<<<<End of Data Types Error>>>>>');
391
392 l_ret_status := fnd_concurrent.set_completion_status(status => 'ERROR',
393 message => l_error_message);
394 GOTO endofprogram;
395 END;
396 --End of Bugfix 5505707
397
398 fnd_file.put_line(fnd_file.log, '<<End of Data Type Specific Parameters>>');
399
400 SELECT to_char(date_assign_value, 'DD-MM-RR')
401 INTO l_cal_period_end_date
402 FROM fem_cal_periods_attr
403 WHERE attribute_id = l_end_date_attribute_id
404 AND version_id = l_end_date_version_id
405 AND cal_period_id = p_cal_period_id;
406
407 IF (p_called_via_srs = 'Y') THEN
408 fnd_file.put_line(fnd_file.log, 'Resetting consolidation status to IN_PROGRESS');
409
410 fnd_file.put_line(fnd_file.log, 'End Date : ' || l_cal_period_end_date);
411 UPDATE gcs_cons_eng_runs
412 SET most_recent_flag = 'X'
413 WHERE most_recent_flag = 'Y'
414 AND run_entity_id = p_consolidation_entity
415 AND hierarchy_id = p_consolidation_hierarchy
416 AND cal_period_id = p_cal_period_id
417 AND balance_type_code = p_balance_type_code;
418
419 UPDATE gcs_cons_eng_runs gcer
420 SET gcer.most_recent_flag = 'X'
421 WHERE gcer.most_recent_flag = 'Y'
422 AND gcer.hierarchy_id = p_consolidation_hierarchy
423 AND gcer.cal_period_id = p_cal_period_id
424 AND gcer.balance_type_code = p_balance_type_code
425 AND EXISTS (SELECT 'X'
426 FROM gcs_cons_relationships gcr
427 WHERE gcr.child_entity_id = gcer.run_entity_id
428 START WITH gcr.parent_entity_id = p_consolidation_entity
429 AND gcr.hierarchy_id = p_consolidation_hierarchy
430 AND TO_DATE(l_cal_period_end_date, 'DD-MM-RR')
431 BETWEEN gcr.start_date AND NVL(gcr.end_date,
432 TO_DATE(l_cal_period_end_date, 'DD-MM-RR'))
433 CONNECT BY PRIOR gcr.child_entity_id = gcr.parent_entity_id
434 --Bugfix 4122843: Added dominant parent flag condition
435 AND gcr.dominant_parent_flag = 'Y'
436 AND gcr.hierarchy_id = p_consolidation_hierarchy
437 AND TO_DATE(l_cal_period_end_date, 'DD-MM-RR')
438 BETWEEN gcr.start_date AND NVL(gcr.end_date,
439 TO_DATE(l_cal_period_end_date, 'DD-MM-RR')));
440 fnd_file.put_line(fnd_file.log, 'End of resetting consolidation status to IN_PROGRESS');
441 END IF;
442
443
444 SELECT entity_name || ' (' || FND_GLOBAL.CONC_REQUEST_ID || ')'
445 INTO l_event_key
446 FROM fem_entities_vl
447 WHERE entity_id = p_consolidation_entity;
448
449 fnd_file.put_line(fnd_file.log, 'The event key is : ' || l_event_key);
450
451 GCS_CONS_ENG_RUNS_PKG.insert_row
452 (
453 p_run_name => p_run_identifier,
454 p_hierarchy_id => p_consolidation_hierarchy,
455 p_process_method_code => NVL(p_process_method, 'FULL'),
456 p_run_entity_id => p_consolidation_entity,
457 p_cal_period_id => p_cal_period_id,
458 p_balance_type_code => p_balance_type_code,
459 p_parent_entity_id => -1,
460 p_item_key => l_event_key,
461 p_request_id => FND_GLOBAL.CONC_REQUEST_ID
462 );
463
464 --Bugfix 4928211: Inserting data into gcs_flattend_relns for performance purposes
465 INSERT INTO gcs_flattened_relns
466 (run_name,
467 parent_entity_id,
468 child_entity_id,
469 creation_date,
470 created_by,
471 last_update_date,
472 last_updated_by,
473 last_update_login,
474 object_version_number,
475 --Bugfix 5091093: Added consolidation type code
476 consolidation_type_code
477 )
478 SELECT p_run_identifier,
479 p_consolidation_entity,
480 gcr.child_entity_id,
481 sysdate,
482 fnd_global.user_id,
483 sysdate,
484 fnd_global.user_id,
485 fnd_global.login_id,
486 1,
487 gtb.consolidation_type_code
488 FROM gcs_cons_relationships gcr,
489 --Bugfix 5091093: Added join for consolidation type code
490 gcs_treatments_b gtb
491 WHERE gtb.treatment_id(+) = gcr.treatment_id
492 START WITH gcr.parent_entity_id = p_consolidation_entity
493 AND gcr.hierarchy_id = p_consolidation_hierarchy
494 --Bugfix 5192720: Added dominant parent flag join condition
495 AND gcr.dominant_parent_flag = 'Y'
496 AND TO_DATE(l_cal_period_end_date, 'DD-MM-RR')
497 BETWEEN gcr.start_date AND NVL(gcr.end_date, TO_DATE(l_cal_period_end_date, 'DD-MM-RR'))
498 CONNECT BY PRIOR gcr.child_entity_id = gcr.parent_entity_id
499 AND gcr.hierarchy_id = p_consolidation_hierarchy
500 AND gcr.dominant_parent_flag = 'Y'
501 AND TO_DATE(l_cal_period_end_date, 'DD-MM-RR')
502 BETWEEN gcr.start_date AND NVL(gcr.end_date, TO_DATE(l_cal_period_end_date, 'DD-MM-RR'));
503
504 COMMIT;
505
506 fnd_file.put_line(fnd_file.log, 'Submitting Workflow');
507
508 --Bugfix 5197891: Assign the correct user rather than putting a null value
509 WF_ENGINE.CreateProcess('GCSENGNE', l_event_key, 'CONS_ENTITY_PROCESS', l_event_key, FND_GLOBAL.USER_NAME);
510 WF_ENGINE.SetItemAttrNumber('GCSENGNE', l_event_key, 'CONS_HIERARCHY', p_consolidation_hierarchy);
511 WF_ENGINE.SetItemAttrNumber('GCSENGNE', l_event_key, 'CONS_ENTITY', p_consolidation_entity);
512 WF_ENGINE.SetItemAttrText('GCSENGNE', l_event_key, 'RUN_IDENTIFIER', p_run_identifier);
513 WF_ENGINE.SetItemAttrText('GCSENGNE', l_event_key, 'CAL_PERIOD', p_cal_period_id);
514 WF_ENGINE.SetItemAttrText('GCSENGNE', l_event_key, 'PROCESS_METHOD', NVL(p_process_method, 'FULL'));
515 WF_ENGINE.SetItemAttrText('GCSENGNE', l_event_key, 'CAL_PERIOD_END_DATE', l_cal_period_end_date);
516 WF_ENGINE.SetItemAttrText('GCSENGNE', l_event_key, 'BALANCE_TYPE_CODE', p_balance_type_code);
517 WF_ENGINE.SetItemAttrText('GCSENGNE', l_event_key, 'CONC_REQUEST_ID', FND_GLOBAL.CONC_REQUEST_ID);
518 --Bugfix 5017120: Added support for multiple data types
519 WF_ENGINE.SetItemAttrNumber('GCSENGNE', l_event_key, 'SOURCE_DATASET_CODE', l_source_dataset_code);
520 WF_ENGINE.SetItemAttrNumber('GCSENGNE', l_event_key, 'HIERARCHY_DATASET_CODE', l_hierarchy_dataset_code);
521 -- Bugfix 5569522: Added Analysis ID as an attribute to the consolidation workflow.
522 -- set the value passed from the Submit Consolidation UI.
523 WF_ENGINE.SetItemAttrNumber('GCSENGNE', l_event_key, 'ANALYSIS_CYCLE_ID', p_analysis_cycle_id);
524 WF_ENGINE.StartProcess('GCSENGNE', l_event_key);
525
526 fnd_file.put_line(fnd_file.log, 'End of Workflow');
527
528 COMMIT;
529
530 --Bugfix 5505707: Added label to end at and reset status of UI if consolidation kicked off from the self service interface
531 <<ENDOFPROGRAM>>
532
533 IF (l_error_message IS NOT NULL) AND (p_called_via_srs <> 'Y') THEN
534
535 UPDATE gcs_cons_eng_runs
536 SET most_recent_flag = 'Y'
537 WHERE most_recent_flag = 'X'
538 AND run_entity_id = p_consolidation_entity
539 AND hierarchy_id = p_consolidation_hierarchy
540 AND cal_period_id = p_cal_period_id
541 AND balance_type_code = p_balance_type_code;
542
543 UPDATE gcs_cons_eng_runs gcer
544 SET gcer.most_recent_flag = 'Y'
545 WHERE gcer.most_recent_flag = 'X'
546 AND gcer.hierarchy_id = p_consolidation_hierarchy
547 AND gcer.cal_period_id = p_cal_period_id
548 AND gcer.balance_type_code = p_balance_type_code
549 AND EXISTS (SELECT 'X'
550 FROM gcs_cons_relationships gcr
551 WHERE gcr.child_entity_id = gcer.run_entity_id
552 START WITH gcr.parent_entity_id = p_consolidation_entity
553 AND gcr.hierarchy_id = p_consolidation_hierarchy
554 AND TO_DATE(l_cal_period_end_date, 'DD-MM-RR')
555 BETWEEN gcr.start_date AND NVL(gcr.end_date,
556 TO_DATE(l_cal_period_end_date, 'DD-MM-RR'))
557 CONNECT BY PRIOR gcr.child_entity_id = gcr.parent_entity_id
558 --Bugfix 4122843: Added dominant parent flag condition
559 AND gcr.dominant_parent_flag = 'Y'
560 AND gcr.hierarchy_id = p_consolidation_hierarchy
561 AND TO_DATE(l_cal_period_end_date, 'DD-MM-RR')
562 BETWEEN gcr.start_date AND NVL(gcr.end_date,
563 TO_DATE(l_cal_period_end_date, 'DD-MM-RR')));
564 END IF;
565
566 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
567 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.EXECUTE_CONSOLIDATION.end', '<<Exit>>');
568 END IF;
569
570 EXCEPTION
571 WHEN OTHERS THEN
572 fnd_file.put_line(fnd_file.log, 'Error message is ' || SQLERRM);
573 x_retcode := '2';
574 END;
575
576 END;