[Home] [Help]
PACKAGE BODY: APPS.GCS_DATASUB_WF_PKG
Source
1 PACKAGE BODY GCS_DATASUB_WF_PKG as
2 /* $Header: gcs_datasub_wfb.pls 120.29 2007/09/25 13:29:45 akeesara noship $ */
3
4 g_api VARCHAR2(80) := 'gcs.plsql.GCS_DATASUB_WF_PKG';
5
6 -- Dimension Attribute Information
7 g_entity_ledger_attr NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-LEDGER_ID')
8 .attribute_id;
9 g_entity_ledger_version NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-LEDGER_ID')
10 .version_id;
11 g_ledger_curr_attr NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-LEDGER_FUNCTIONAL_CRNCY_CODE')
12 .attribute_id;
13 g_ledger_curr_version NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-LEDGER_FUNCTIONAL_CRNCY_CODE')
14 .version_id;
15 g_ledger_system_attr NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-SOURCE_SYSTEM_CODE')
16 .attribute_id;
17 g_ledger_system_version NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-SOURCE_SYSTEM_CODE')
18 .version_id;
19 g_ledger_vs_combo_attr NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-GLOBAL_VS_COMBO')
20 .attribute_id;
21 g_ledger_vs_combo_version NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-GLOBAL_VS_COMBO')
22 .version_id;
23
24 -- Beginning of Private Procedures
25
26 -- Bugfix 4969879: Removed get_reference_data_info
27
28 PROCEDURE get_datasub_dtls(p_load_id IN NUMBER,
29 p_datasub_info IN OUT NOCOPY r_datasub_info) IS
30
31 -- Bugfix 5066041: Added support for additional data types
32 l_balance_type_attr NUMBER := gcs_utility_pkg.g_dimension_attr_info('DATASET_CODE-DATASET_BALANCE_TYPE_CODE')
33 .attribute_id;
34 l_balance_type_version NUMBER := gcs_utility_pkg.g_dimension_attr_info('DATASET_CODE-DATASET_BALANCE_TYPE_CODE')
35 .version_id;
36 l_budget_attr NUMBER := gcs_utility_pkg.g_dimension_attr_info('DATASET_CODE-BUDGET_ID')
37 .attribute_id;
38 l_budget_version NUMBER := gcs_utility_pkg.g_dimension_attr_info('DATASET_CODE-BUDGET_ID')
39 .version_id;
40 l_encumbrance_attr NUMBER := gcs_utility_pkg.g_dimension_attr_info('DATASET_CODE-ENCUMBRANCE_TYPE_ID')
41 .attribute_id;
42 l_encumbrance_version NUMBER := gcs_utility_pkg.g_dimension_attr_info('DATASET_CODE-ENCUMBRANCE_TYPE_ID')
43 .version_id;
44
45 -- Bug Fix: 5843592, Get the attribute id and version id of the CAL_PERIOD_END_DATE of calendar period
46 l_period_end_date_attr NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
47 .attribute_id;
48 l_period_end_date_version NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
49 .version_id;
50
51 BEGIN
52 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
53 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
54 g_api || '.GET_DATASUB_DTLS.begin',
55 '<<Enter>>');
56 END IF;
57
58 --Bugfix 4969879: Added support for data types data model.
59 --Bugfix 5843592: Query modified to get the date effective source data details from gcs_entities_attr
60
61 SELECT gdsd.load_id,
62 gdsd.load_name,
63 gdsd.entity_id,
64 gdsd.cal_period_id,
65 gdsd.currency_code,
66 gdsd.balance_type_code,
67 gdsd.load_method_code,
68 gdsd.currency_type_code,
69 gdsd.amount_type_code,
70 gdsd.measure_type_code,
71 gdsd.notify_options_code,
72 gea.ledger_id,
73 feb.entity_display_code,
74 flb.ledger_display_code,
75 gea.transform_rule_set_id,
76 gea.validation_rule_set_id,
77 gea.balances_rule_id,
78 gea.source_system_code,
79 gdtcb.source_dataset_code,
80 fda.dim_attribute_varchar_member
81 INTO p_datasub_info.load_id,
82 p_datasub_info.load_name,
83 p_datasub_info.entity_id,
84 p_datasub_info.cal_period_id,
85 p_datasub_info.currency_code,
86 p_datasub_info.balance_type_code,
87 p_datasub_info.load_method_code,
88 p_datasub_info.currency_type_code,
89 p_datasub_info.amount_type_code,
90 p_datasub_info.measure_type_code,
91 p_datasub_info.notify_options_code,
92 p_datasub_info.ledger_id,
93 p_datasub_info.entity_display_code,
94 p_datasub_info.ledger_display_code,
95 p_datasub_info.transform_rule_set_id,
96 p_datasub_info.validation_rule_set_id,
97 p_datasub_info.balances_rule_id,
98 p_datasub_info.source_system_code,
99 p_datasub_info.dataset_code,
100 p_datasub_info.ds_balance_type_code
101 FROM gcs_data_sub_dtls gdsd,
102 fem_entities_b feb,
103 fem_ledgers_b flb,
104 gcs_entities_attr gea,
105 gcs_data_type_codes_b gdtcb,
106 fem_datasets_attr fda,
107 fem_cal_periods_attr fcpa
108 WHERE gdsd.load_id = p_load_id
109 AND gdsd.entity_id = feb.entity_id
110 AND feb.entity_id = gea.entity_id
111 AND gea.data_type_code = gdsd.balance_type_code
112 AND gdsd.balance_type_code = gdtcb.data_type_code
113 AND flb.ledger_id = gea.ledger_id
114 AND fda.dataset_code = gdtcb.source_dataset_code
115 AND fda.attribute_id = l_balance_type_attr
116 AND fda.version_id = l_balance_type_version
117 AND fcpa.cal_period_id = gdsd.cal_period_id
118 AND fcpa.attribute_id = l_period_end_date_attr
119 AND fcpa.version_id = l_period_end_date_version
120 AND fcpa.date_assign_value BETWEEN gea.effective_start_date
121 AND NVL(gea.effective_end_date, fcpa.date_assign_value ) ;
122
123 --Bugfix 5066041: Check the encumbrance type id or budget id
124 IF (p_datasub_info.ds_balance_type_code = 'BUDGET') THEN
125 SELECT fb.budget_id,
126 fb.budget_display_code
127 INTO p_datasub_info.budget_id,
128 p_datasub_info.budget_display_code
129 FROM fem_datasets_attr fda,
130 fem_budgets_b fb
131 WHERE fda.dataset_code = p_datasub_info.dataset_code
132 AND fda.attribute_id = l_budget_attr
133 AND fda.version_id = l_budget_version
134 AND fb.budget_id = fda.dim_attribute_numeric_member;
135
136 ELSIF (p_datasub_info.ds_balance_type_code = 'ENCUMBRANCE') THEN
137 SELECT fetb.encumbrance_type_id,
138 fetb.encumbrance_type_code
139 INTO p_datasub_info.encumbrance_type_id,
140 p_datasub_info.encumbrance_type_code
141 FROM fem_datasets_attr fda,
142 fem_encumbrance_types_b fetb
143 WHERE fda.dataset_code = p_datasub_info.dataset_code
144 AND fda.attribute_id = l_encumbrance_attr
145 AND fda.version_id = l_encumbrance_version
146 AND fetb.encumbrance_type_id = fda.dim_attribute_numeric_member;
147
148 END IF;
149
150 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
151 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
152 g_api || '.GET_DATASUB_DTLS.end',
153 '<<Exit>>');
154 END IF;
155 END get_datasub_dtls;
156
157 PROCEDURE process_external_entity(p_load_id NUMBER,
158 p_datasub_info r_datasub_info)
159
160 IS
161 l_execution_mode VARCHAR2(1);
162 l_errbuf VARCHAR2(200);
163 l_retcode VARCHAR2(200);
164 l_status_code VARCHAR2(30);
165 l_ret_status_code BOOLEAN;
166 l_event_key VARCHAR2(200);
167 l_dataset_code NUMBER;
168 l_source_system_code NUMBER;
169 l_request_id NUMBER(15);
170 BEGIN
171
172 fnd_file.put_line(fnd_file.log, 'Processing an External Entity');
173
174 --Bugfix 4969879: Remove call to get_reference_data_info as this is already stored on p_datasub_info
175
176 l_event_key := 'Load Identifier : ' || p_load_id;
177
178 -- Launch the Workflow
179 fnd_file.put_line(fnd_file.log, 'Launching Workflow');
180 --Bugfix 5197891: Pass the corrrect owner rather than null value for workflow
181 WF_ENGINE.CreateProcess('DATASUB',
182 l_event_key,
183 'GCSDATASUB',
184 l_event_key,
185 FND_GLOBAL.USER_NAME);
186 WF_ENGINE.SetItemAttrNumber('DATASUB',
187 l_event_key,
188 'LOAD_ID',
189 p_load_id);
190 WF_ENGINE.StartProcess('DATASUB', l_event_key);
191
192 SELECT status_code
193 INTO l_status_code
194 FROM gcs_data_sub_dtls
195 WHERE load_id = p_load_id;
196
197 fnd_file.put_line(fnd_file.log, 'Completed Workflow');
198
199 IF (l_status_code = 'IN_PROGRESS') THEN
200 -- Submit the Engine
201 BEGIN
202 SELECT 'I'
203 INTO l_execution_mode
204 FROM fem_data_locations fdl,
205 fem_ledgers_attr fla
206 WHERE fdl.ledger_id = p_datasub_info.ledger_id
207 AND fdl.cal_period_id = p_datasub_info.cal_period_id
208 AND fdl.dataset_code = p_datasub_info.dataset_code
209 AND fdl.source_system_code = p_datasub_info.source_system_code
210 AND ROWNUM < 2;
211 EXCEPTION
212 WHEN NO_DATA_FOUND THEN
213 l_execution_mode := 'S';
214 END;
215
216 fnd_file.put_line(fnd_file.log,
217 'Executing External Ledger Integration');
218
219 FEM_XGL_POST_ENGINE_PKG.Main(x_errbuf => l_errbuf,
220 x_retcode => l_retcode,
221 p_execution_mode => l_execution_mode,
222 p_ledger_id => p_datasub_info.ledger_id,
223 p_cal_period_id => p_datasub_info.cal_period_id,
224 p_budget_id => p_datasub_info.budget_id,
225 p_enc_type_id => p_datasub_info.encumbrance_type_id,
226 p_dataset_code => p_datasub_info.dataset_code,
227 p_xgl_int_obj_def_id => 1000,
228 p_qtd_ytd_code => 'YTD');
229
230 COMMIT;
231
232 fnd_file.put_line(fnd_file.log,
233 'Completed External Ledger Integration');
234
235 fnd_file.put_line(fnd_file.log, 'Raising Pristine Data Event');
236
237 raise_impact_analysis_event(p_load_id => p_load_id,
238 p_ledger_id => p_datasub_info.ledger_id);
239
240 fnd_file.put_line(fnd_file.log, 'Updating Process Status');
241
242 update_status(p_load_id => p_load_id);
243
244 fnd_file.put_line(fnd_file.log, 'Updating Data Status');
245
246 -- Bugfix 5676634: Submit request for data status update instead of API call
247 -- issuing a commit prior to request submission to ensure information is going
248 --to be available to the concurrent program which will run in different context/session
249
250 --gcs_cons_monitor_pkg.update_data_status(p_load_id => p_load_id,
251 -- p_cons_rel_id => null,
252 -- p_hierarchy_id => null,
253 -- p_transaction_type => null);
254 COMMIT;
255 l_request_id := fnd_request.submit_request(application => 'GCS',
256 program => 'FCH_UPDATE_DATA_STATUS',
257 sub_request => FALSE,
258 argument1 => p_load_id,
259 argument2 => NULL,
260 argument3 => NULL,
261 argument4 => NULL);
262
263 --gcs_xml_gen_pkg.generate_ds_xml(p_load_id => p_load_id);
264 l_request_id := fnd_request.submit_request(application => 'GCS',
265 program => 'FCH_XML_WRITER',
266 sub_request => FALSE,
267 argument1 => 'DATASUBMISSION',
268 argument2 => NULL,
269 argument3 => NULL,
270 argument4 => p_load_id);
271
272 fnd_file.put_line(fnd_file.log,
273 'Submitted XML Generation Request Id: ' ||
274 l_request_id);
275
276 ELSE
277 -- Bug Fix : 5234796
278 fnd_file.put_line(fnd_file.log, '<<<<< Beginning of Error >>>>>');
279 IF (l_status_code = 'VALIDATION_MEMBERS_FAILED') THEN
280 fnd_file.put_line(fnd_file.log,
281 'Validations on dimension members failed. Please review the error_message_code column or data loaded report to see which members are invalid.');
282 ELSIF (l_status_code = 'VALIDATION_FAILED') THEN
283 fnd_file.put_line(fnd_file.log,
284 'Validations on data failed. Please review the error_message_code column or data loaded report to see which trial balance rows are invalid.');
285 --Bugfix 5261560: Added new validations on whether the transfer processed correctly or not
286 ELSIF (l_status_code = 'INVALID_FEM_INDEX') THEN
287 fnd_file.put_line(fnd_file.log,
288 'The index defined on FEM_BAL_INTERFACE_T does not contain all columns that are part of the processing key. Please review the index definition.');
289 ELSIF (l_status_code = 'TRANSFER_ERROR') THEN
290 fnd_file.put_line(fnd_file.log,
291 'The transfer of data from GCS_BAL_INTERFACE_T to FEM_BAL_INTERFACE_T failed. Please review the information in FND_LOG_MESSAGES for more details.');
292 ELSE
293 fnd_file.put_line(fnd_file.log, 'Transformation on data failed.');
294 END IF;
295 fnd_file.put_line(fnd_file.log, '<<<<< End of Error >>>>>');
296
297 -- Transformation or Validation Failed
298 UPDATE gcs_data_sub_dtls
299 SET status_code = 'ERROR',
300 end_time = sysdate,
301 last_updated_by = FND_GLOBAL.USER_ID,
302 last_update_login = FND_GLOBAL.LOGIN_ID,
303 last_update_date = sysdate
304 WHERE load_id = p_load_id;
305
306 --gcs_xml_gen_pkg.generate_ds_xml(p_load_id => p_load_id);
307 -- There is no need to launch the XML Generator if the data submission errored. This skep may be skipped. The code is being deleted.
308 l_ret_status_code := fnd_concurrent.set_completion_status(status => 'ERROR',
309 message => NULL);
310 END IF;
311
312 END process_external_entity;
313
314 PROCEDURE process_internal_entity(p_load_id NUMBER,
315 p_datasub_info r_datasub_info)
316
317 IS
318 l_errbuf VARCHAR2(200);
319 l_retcode VARCHAR2(200);
320 l_chart_of_accounts_id NUMBER(15);
321 l_enable_avg_bal_flag VARCHAR2(1);
322 l_company_value_low VARCHAR2(150);
323 l_company_value_high VARCHAR2(150);
324 l_currency_code VARCHAR2(30);
325 l_currency_option_code VARCHAR2(30);
326 l_xlated_bal_option_code VARCHAR2(30);
327 l_bal_rule_obj_def_id NUMBER;
328 l_cal_period_end_date DATE;
329
330
331 l_period_end_date_attr NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
332 .attribute_id;
333 l_period_end_date_version NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
334 .version_id;
335 l_balances_rule_attr NUMBER := gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-BALANCES_RULE_ID')
336 .attribute_id;
337 l_balances_rule_version NUMBER := gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-BALANCES_RULE_ID')
338 .version_id;
339 l_global_vs_combo_attr NUMBER := gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-GLOBAL_VS_COMBO')
340 .attribute_id;
341 l_global_vs_combo_version NUMBER := gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-GLOBAL_VS_COMBO')
342 .version_id;
343 l_company_attr NUMBER := gcs_utility_pkg.g_dimension_attr_info('COMPANY_COST_CENTER_ORG_ID-COMPANY')
344 .attribute_id;
345 l_company_version NUMBER := gcs_utility_pkg.g_dimension_attr_info('COMPANY_COST_CENTER_ORG_ID-COMPANY')
346 .version_id;
347 l_global_vs_combo_id NUMBER(9);
348 l_status_code VARCHAR2(1);
349 l_hier_obj_definition_id NUMBER(9);
350 l_hier_obj_id NUMBER(9);
351 l_company_vs_id NUMBER;
352 l_fch_company_vs_id NUMBER;
353 l_org_vs_id NUMBER;
354 l_fch_org_vs_id NUMBER;
355 l_cal_period_name VARCHAR2(150);
356 l_error_occurred BOOLEAN := FALSE;
357 l_code_point VARCHAR2(200);
358 l_request_id NUMBER(15);
359
360 CURSOR c_generated_loads IS
361 SELECT gdsd.load_id
362 FROM gcs_data_sub_dtls gdsd
363 WHERE gdsd.associated_request_id = FND_GLOBAL.CONC_REQUEST_ID;
364
365 --Bugfix 4507953: Rely on translated balances select, rather than REQUEST_ID select
366 CURSOR c_translated_balances(p_request_id NUMBER, p_object_id NUMBER, p_ledger_id NUMBER, p_cal_period_id NUMBER) IS
367
368 SELECT DISTINCT translated_currency
369 FROM fem_dl_trans_curr
370 WHERE request_id >= p_request_id
371 AND object_id = p_object_id
372 AND ledger_id = p_ledger_id
373 AND cal_period_id = p_cal_period_id;
374
375 BEGIN
376
377 fnd_file.put_line(fnd_file.log, 'Processing an Internal Entity');
378
379 fnd_file.put_line(fnd_file.log,
380 'Retrieving Chart of Accounts Information');
381
382 SELECT gsob.chart_of_accounts_id,
383 gsob.currency_code
384 INTO l_chart_of_accounts_id,
385 l_currency_code
386 FROM gl_sets_of_books gsob
387 WHERE gsob.set_of_books_id = p_datasub_info.ledger_id;
388
389 fnd_file.put_line(fnd_file.log, 'Retrieving Balances Rule Information');
390
391 SELECT fibrd.bal_rule_obj_def_id,
392 fibrd.currency_option_code,
393 fibrd.xlated_bal_option_code,
394 fibr.include_avg_bal_flag,
395 fcpa.date_assign_value,
396 fcpv.cal_period_name
397 INTO l_bal_rule_obj_def_id,
398 l_currency_option_code,
399 l_xlated_bal_option_code,
400 l_enable_avg_bal_flag,
401 l_cal_period_end_date,
402 l_cal_period_name
403 FROM fem_intg_bal_rule_defs fibrd,
404 fem_intg_bal_rules fibr,
405 fem_object_definition_b fodb,
406 fem_cal_periods_attr fcpa,
407 fem_cal_periods_vl fcpv
408 WHERE fibrd.bal_rule_obj_def_id = fodb.object_definition_id
409 AND fibr.bal_rule_obj_id = fodb.object_id
410 AND fodb.object_id = p_datasub_info.balances_rule_id
411 AND fcpa.cal_period_id = p_datasub_info.cal_period_id
412 AND fcpa.cal_period_id = fcpv.cal_period_id
413 AND fcpa.attribute_id = l_period_end_date_attr
414 AND fcpa.version_id = l_period_end_date_version
415 AND fcpa.date_assign_value BETWEEN fodb.effective_start_date AND
416 fodb.effective_end_date;
417
418 UPDATE gcs_data_sub_dtls
419 SET currency_code = l_currency_code
420 WHERE load_id = p_datasub_info.load_id;
421
422 --Check if ADB enabled
423 fnd_file.put_line(fnd_file.log,
424 'Checking if Average Balances is Enabled');
425
426 IF (l_enable_avg_bal_flag = 'Y') THEN
427 -- Bugfix 5630225: Added balances_rule_id to the insert statement
428 INSERT INTO gcs_data_sub_dtls
429 (load_id,
430 load_name,
431 entity_id,
432 cal_period_id,
433 currency_code,
434 balance_type_code,
435 load_method_code,
436 currency_type_code,
437 amount_type_code,
438 measure_type_code,
439 notify_options_code,
440 notification_text,
441 creation_date,
442 created_by,
443 last_update_date,
444 last_updated_by,
445 last_update_login,
446 object_version_number,
447 start_time,
448 locked_flag,
449 most_recent_flag,
450 associated_request_id,
451 status_code,
452 balances_rule_id)
453 SELECT gcs_data_sub_dtls_s.nextval,
454 gcs_data_sub_dtls_s.nextval,
455 gdsd.entity_id,
456 gdsd.cal_period_id,
457 gdsd.currency_code,
458 'ADB',
459 gdsd.load_method_code,
460 gdsd.currency_type_code,
461 gdsd.amount_type_code,
462 gdsd.measure_type_code,
463 gdsd.notify_options_code,
464 gdsd.notification_text,
465 sysdate,
466 fnd_global.user_id,
467 sysdate,
468 fnd_global.user_id,
469 fnd_global.login_id,
470 1,
471 gdsd.start_time,
472 gdsd.locked_flag,
473 gdsd.most_recent_flag,
474 gdsd.associated_request_id,
475 gdsd.status_code,
476 gdsd.balances_rule_id
477 FROM gcs_data_sub_dtls gdsd
478 WHERE gdsd.load_id = p_datasub_info.load_id;
479 END IF;
480
481 IF (p_datasub_info.load_method_code = 'SNAPSHOT') THEN
482 fnd_file.put_line(fnd_file.log, 'Performing a snapshot load');
483 -- Submit Data for All Entities Associated to Balances Rules
484 -- Bugfix 5630225: Added balances_rule_id to the insert statement
485 -- BugFix 5843592 : Use gcs_entities_attr instead of fem_entities_attr
486 INSERT INTO gcs_data_sub_dtls
487 (load_id,
488 load_name,
489 entity_id,
490 cal_period_id,
491 currency_code,
492 balance_type_code,
493 load_method_code,
494 currency_type_code,
495 amount_type_code,
496 measure_type_code,
497 notify_options_code,
498 notification_text,
499 creation_date,
500 created_by,
501 last_update_date,
502 last_updated_by,
503 last_update_login,
504 object_version_number,
505 start_time,
506 locked_flag,
507 most_recent_flag,
508 associated_request_id,
509 status_code,
510 balances_rule_id)
511 SELECT gcs_data_sub_dtls_s.nextval,
512 gcs_data_sub_dtls_s.nextval,
513 gea.entity_id,
514 gdsd.cal_period_id,
515 gdsd.currency_code,
516 gdsd.balance_type_code,
517 gdsd.load_method_code,
518 gdsd.currency_type_code,
519 gdsd.amount_type_code,
520 gdsd.measure_type_code,
521 gdsd.notify_options_code,
522 gdsd.notification_text,
523 SYSDATE,
524 fnd_global.user_id,
525 SYSDATE,
526 fnd_global.user_id,
527 fnd_global.login_id,
528 1,
529 gdsd.start_time,
530 gdsd.locked_flag,
531 gdsd.most_recent_flag,
532 gdsd.associated_request_id,
533 gdsd.status_code,
534 gdsd.balances_rule_id
535 FROM gcs_data_sub_dtls gdsd,
536 gcs_entities_attr gea,
537 fem_cal_periods_attr fcpa
538 WHERE gdsd.associated_request_id = FND_GLOBAL.CONC_REQUEST_ID
539 AND gea.entity_id <> p_datasub_info.entity_id
540 AND gea.balances_rule_id = gdsd.balances_rule_id
541 AND gea.data_type_code = gdsd.balance_type_code
542 AND fcpa.cal_period_id = gdsd.cal_period_id
543 AND fcpa.attribute_id = l_period_end_date_attr
544 AND fcpa.version_id = l_period_end_date_version
545 AND fcpa.date_assign_value BETWEEN gea.effective_start_date
546 AND NVL(gea.effective_end_date, fcpa.date_assign_value ) ;
547
548
549 fnd_file.put_line(fnd_file.log,
550 'Submitting the balances integration');
551
552 BEGIN
553 --Submit the concurrent program
554 FEM_INTG_BAL_RULE_ENG_PKG.Main(x_errbuf => l_errbuf,
555 x_retcode => l_retcode,
556 p_bal_rule_obj_def_id => l_bal_rule_obj_def_id,
557 p_coa_id => l_chart_of_accounts_id,
558 p_from_period => l_cal_period_name,
559 p_to_period => l_cal_period_name,
560 p_effective_date => NULL,
561 p_bsv_range_low => NULL,
562 p_bsv_range_high => NULL);
563 EXCEPTION
564 WHEN OTHERS THEN
565 l_error_occurred := TRUE;
566 fnd_file.put_line(fnd_file.log,
567 'An error occurred while synchronizing balances.');
568 fnd_file.put_line(fnd_file.log,
569 'Please review the output of the request for more details.');
570 END;
571 ELSE
572 BEGIN
573 fnd_file.put_line(fnd_file.log, 'Submitting an incremental load');
574
575 fnd_file.put_line(fnd_file.log,
576 'Checking if chart of accounts mapping is required');
577 --Check if chart of accounts mapping is required
578
579 l_code_point := 'RETRIEVE_LOCAL_VALUE_SETS';
580
581 SELECT fla.dim_attribute_numeric_member,
582 fgvcd_local_company.value_set_id,
583 fgvcd_local_org.value_set_id
584 INTO l_global_vs_combo_id,
585 l_company_vs_id,
586 l_org_vs_id
587 FROM fem_ledgers_attr fla,
588 fem_global_vs_combo_defs fgvcd_local_company,
589 fem_global_vs_combo_defs fgvcd_local_org
590 WHERE fla.ledger_id = p_datasub_info.ledger_id
591 AND fla.attribute_id = l_global_vs_combo_attr
592 AND fla.version_id = l_global_vs_combo_version
593 AND fla.dim_attribute_numeric_member =
594 fgvcd_local_company.global_vs_combo_id
595 AND fgvcd_local_company.dimension_id = 112
596 AND fla.dim_attribute_numeric_member =
597 fgvcd_local_org.global_vs_combo_id
598 AND fgvcd_local_org.dimension_id = 8;
599
600 l_code_point := 'RETRIEVE_CONSOLIDATION_VALUE_SETS';
601
602 SELECT fgvcd_fch_company.value_set_id,
603 fgvcd_fch_org.value_set_id
604 INTO l_fch_company_vs_id,
605 l_fch_org_vs_id
606 FROM fem_global_vs_combo_defs fgvcd_fch_company,
607 fem_global_vs_combo_defs fgvcd_fch_org
608 WHERE fgvcd_fch_company.global_vs_combo_id =
609 gcs_utility_pkg.g_fch_global_vs_combo_id
610 AND fgvcd_fch_org.global_vs_combo_id =
611 fgvcd_fch_company.global_vs_combo_id
612 AND fgvcd_fch_org.dimension_id = 8
613 AND fgvcd_fch_company.dimension_id = 112;
614
615 IF ((l_fch_company_vs_id <> l_company_vs_id) AND
616 (l_fch_org_vs_id <> l_org_vs_id)) THEN
617
618 fnd_file.put_line(fnd_file.log,
619 'Chart of Accounts mapping is reuqired');
620
621 l_code_point := 'RETRIEVE_DEFAULT_HIERARCHY';
622
623 SELECT fxd.default_mvs_hierarchy_obj_id,
624 fodb.object_definition_id
625 INTO l_hier_obj_id,
626 l_hier_obj_definition_id
627 FROM fem_xdim_dimensions fxd,
628 fem_object_definition_b fodb
629 WHERE fxd.dimension_id = 8
630 AND fxd.default_mvs_hierarchy_obj_id = fodb.object_id
631 AND l_cal_period_end_date BETWEEN fodb.effective_start_date AND
632 fodb.effective_end_date;
633
634 l_code_point := 'RETRIEVING_COMPANY_VALUE_RANGES';
635
636 SELECT min(fcmin.company_display_code),
637 min(fcmax.company_display_code)
638 INTO l_company_value_low,
639 l_company_value_high
640 FROM fem_companies_b fcmin,
641 fem_companies_b fcmax,
642 fem_cctr_orgs_hier fcoh,
643 fem_cctr_orgs_attr fcoa
644 WHERE fcoh.hierarchy_obj_def_id = l_hier_obj_definition_id
645 AND fcoh.parent_value_set_id = l_fch_org_vs_id
646 AND fcoh.child_value_set_id = l_org_vs_id
647 AND fcoh.child_id = fcoa.company_cost_center_org_id
648 AND fcoh.child_value_set_id = fcoa.value_set_id
649 AND fcoa.attribute_id = l_company_attr
650 AND fcoa.version_id = l_company_version
651 AND fcoa.dim_attribute_numeric_member = fcmin.company_id
652 AND fcoa.dim_attribute_numeric_member = fcmax.company_id
653 AND fcmin.value_set_id = l_fch_company_vs_id
654 AND fcmax.value_set_id = l_fch_company_vs_id;
655 ELSE
656 fnd_file.put_line(fnd_file.log,
657 'Chart of accounts mapping is not required');
658
659 l_code_point := 'RETRIEVING_COMPANY_VALUE_RANGES';
660
661 SELECT min(fcmin.company_display_code),
662 min(fcmax.company_display_code)
663 INTO l_company_value_low,
664 l_company_value_high
665 FROM fem_companies_b fcmin,
666 fem_companies_b fcmax,
667 gcs_entity_organizations geo
668 WHERE geo.entity_id = p_datasub_info.entity_id
669 AND geo.company_cost_center_org_id = fcmin.company_id
670 AND geo.company_cost_center_org_id = fcmax.company_id;
671 END IF;
672
673 fnd_file.put_line(fnd_file.log, 'Loading the balances data');
674
675 l_code_point := 'SUBMITTING_INTEGRATION';
676
677 --Submit the concurrent program
678 FEM_INTG_BAL_RULE_ENG_PKG.Main(x_errbuf => l_errbuf,
679 x_retcode => l_retcode,
680 p_bal_rule_obj_def_id => l_bal_rule_obj_def_id,
681 p_coa_id => l_chart_of_accounts_id,
682 p_from_period => l_cal_period_name,
683 p_to_period => l_cal_period_name,
684 p_effective_date => NULL,
685 p_bsv_range_low => l_company_value_low,
686 p_bsv_range_high => l_company_value_high);
687 EXCEPTION
688 WHEN OTHERS THEN
689 l_error_occurred := TRUE;
690 IF (l_code_point = 'SUBMITING_INTEGRATION') THEN
691 fnd_file.put_line(fnd_file.log,
692 'Error occurred while synchronizing balances.');
693 fnd_file.put_line(fnd_file.log,
694 'Please review the output file for more details.');
695 ELSIF (l_code_point = 'RETRIEVING_COMPANY_VALUE_RANGES') THEN
696 fnd_file.put_line(fnd_file.log,
697 'Error while retrieving company values.');
698 ELSIF (l_code_point = 'RETRIEVE_DEFAULT_HIERARCHY') THEN
699 fnd_file.put_line(fnd_file.log,
700 'The default value set map is not available on the Org Hierarchy');
701 ELSIF (l_code_point = 'RETRIEVE_CONSOLIDATION_VALUE_SETS') THEN
702 fnd_file.put_line(fnd_file.log,
703 ' Error retrieving the consolidation value sets for the Org and Company dimension.');
704 ELSIF (l_code_point = 'RETRIEVE_LOCAL_VALUE_SETS') THEN
705 fnd_file.put_line(fnd_file.log,
706 'Error retrieving the local value sets for the Org and Company dimension.');
707 END IF;
708 END;
709 END IF;
710
711 IF (NOT l_error_occurred) THEN
712
713 fnd_file.put_line(fnd_file.log, 'Reviewing translation balances');
714
715 FOR v_translated_balances IN c_translated_balances(FND_GLOBAL.CONC_REQUEST_ID,
716 p_datasub_info.balances_rule_id,
717 p_datasub_info.ledger_id,
718 p_datasub_info.cal_period_id) LOOP
719 INSERT INTO gcs_data_sub_dtls
720 (load_id,
721 load_name,
722 entity_id,
723 cal_period_id,
724 currency_code,
725 balance_type_code,
726 load_method_code,
727 currency_type_code,
728 amount_type_code,
729 measure_type_code,
730 notify_options_code,
731 notification_text,
732 creation_date,
733 created_by,
734 last_update_date,
735 last_updated_by,
736 last_update_login,
737 object_version_number,
738 start_time,
739 locked_flag,
740 most_recent_flag,
741 associated_request_id,
742 status_code,
743 balances_rule_id)
744 SELECT gcs_data_sub_dtls_s.nextval,
745 gcs_data_sub_dtls_s.nextval,
746 gdsd.entity_id,
747 gdsd.cal_period_id,
748 v_translated_balances.translated_currency,
749 gdsd.balance_type_code,
750 gdsd.load_method_code,
751 gdsd.currency_type_code,
752 gdsd.amount_type_code,
753 gdsd.measure_type_code,
754 gdsd.notify_options_code,
755 gdsd.notification_text,
756 SYSDATE,
757 fnd_global.user_id,
758 SYSDATE,
759 fnd_global.user_id,
760 fnd_global.login_id,
761 1,
762 gdsd.start_time,
763 gdsd.locked_flag,
764 gdsd.most_recent_flag,
765 gdsd.associated_request_id,
766 'IN_PROGRESS',
767 gdsd.balances_rule_id
768 FROM gcs_data_sub_dtls gdsd
769 WHERE gdsd.associated_request_id = FND_GLOBAL.CONC_REQUEST_ID;
770
771 END LOOP;
772
773 fnd_file.put_line(fnd_file.log,
774 'Setting the concurrent request status');
775
776 SELECT status_code
777 INTO l_status_code
778 FROM fnd_concurrent_requests
779 WHERE request_id = FND_GLOBAL.conc_request_id;
780
781 UPDATE gcs_data_sub_dtls
782 SET status_code = DECODE(l_status_code,
783 'C',
784 'COMPLETED',
785 'E',
786 'ERROR',
787 'W',
788 'WARNING',
789 'WARNING'),
790 end_time = SYSDATE,
791 last_updated_by = FND_GLOBAL.USER_ID,
792 last_update_login = FND_GLOBAL.LOGIN_ID,
793 last_update_date = SYSDATE
794 WHERE associated_request_id = FND_GLOBAL.conc_request_id;
795
796 -- Bugfix 5347804: Need to update the most recent flag to 'N' for prior loads
797 UPDATE gcs_data_sub_dtls prev_runs
798 SET most_recent_flag = 'N'
799 WHERE EXISTS (SELECT 'X'
800 FROM gcs_data_sub_dtls curr_run
801 WHERE curr_run.associated_request_id =
802 FND_GLOBAL.conc_request_id
803 AND curr_run.entity_id = prev_runs.entity_id
804 AND curr_run.cal_period_id = prev_runs.cal_period_id
805 AND curr_run.currency_code = prev_runs.currency_code
806 AND curr_run.balance_type_code =
807 prev_runs.balance_type_code
808 AND prev_runs.load_id < curr_run.load_id);
809
810 COMMIT;
811
812 FOR v_generated_loads IN c_generated_loads LOOP
813
814 -- Bugfix 5347804: Raising the impact analysis and updating the data status should only be done if the request completed successfully
815
816 -- Fixed bug 5632567, Added l_retcode <> 1 condition, since incase of incremental load
817 -- if no new balances are processed by the balances rule engine
818 -- it will set retcode = 1 and request status set to normal completion
819 -- So if retcode is 1 then we need not do below processing since nothing has changed since last load
820 IF (l_status_code = 'C') THEN
821 -- Bugfix 5569620: l_retcode will be null in case load was successful
822 -- so raise pristine data event for this case to sync data status
823 IF (l_retcode IS NULL OR l_retcode <> 1) THEN
824 fnd_file.put_line(fnd_file.log, 'Raising Pristine Data Event');
825 raise_impact_analysis_event(p_load_id => v_generated_loads.load_id,
826 p_ledger_id => p_datasub_info.ledger_id);
827
828 -- Bugfix 5347804: Do not need to call update status as it is done in prior call
829 --
830 --fnd_file.put_line(fnd_file.log, 'Updating Process Status');
831 --update_status
832 -- (p_load_id => v_generated_loads.load_id);
833
834 fnd_file.put_line(fnd_file.log, 'Updating Data Status');
835
836 -- Bugfix 5676634: Submit request for data status update instead of API call
837
838 --gcs_cons_monitor_pkg.update_data_status(p_load_id => v_generated_loads.load_id,
839 -- p_cons_rel_id => null,
840 -- p_hierarchy_id => null,
841 -- p_transaction_type => null);
842 l_request_id := fnd_request.submit_request(application => 'GCS',
843 program => 'FCH_UPDATE_DATA_STATUS',
844 sub_request => FALSE,
845 argument1 => v_generated_loads.load_id,
846 argument2 => NULL,
847 argument3 => NULL,
848 argument4 => NULL);
849 END IF;
850 --gcs_xml_gen_pkg.generate_ds_xml(p_load_id => v_generated_loads.load_id);
851 l_request_id := fnd_request.submit_request(application => 'GCS',
852 program => 'FCH_XML_WRITER',
853 sub_request => FALSE,
854 argument1 => 'DATASUBMISSION',
855 argument2 => NULL,
856 argument3 => NULL,
857 argument4 => v_generated_loads.load_id);
858
859 --Bugfix 5347804: Commenting out println statement
860 --fnd_file.put_line(fnd_file.log, 'Submitted XML Generation Request Id: '||l_request_id);
861 END IF;
862
863 END LOOP;
864
865 ELSE
866 UPDATE gcs_data_sub_dtls
867 SET status_code = DECODE(l_status_code,
868 'C',
869 'COMPLETED',
870 'E',
871 'ERROR',
872 'W',
873 'WARNING',
874 'WARNING'),
875 end_time = sysdate,
876 last_updated_by = FND_GLOBAL.USER_ID,
877 last_update_login = FND_GLOBAL.LOGIN_ID,
878 last_update_date = sysdate
879 WHERE associated_request_id = FND_GLOBAL.conc_request_id;
880
881 --gcs_xml_gen_pkg.generate_ds_xml(p_load_id => p_load_id);
882 --Bugfix 5347804: Commenting out call to XML Generation if the process ends in error
883 -- l_request_id := fnd_request.submit_request(
884 -- application => 'GCS',
885 -- program => 'FCH_XML_WRITER',
886 -- sub_request => FALSE,
887 -- argument1 => 'DATASUBMISSION',
888 -- argument2 => NULL,
889 -- argument3 => NULL,
890 -- argument4 => p_load_id);
891
892 -- fnd_file.put_line(fnd_file.log, 'Submitted XML Generation Request Id: '||l_request_id);
893
894 l_error_occurred := fnd_concurrent.set_completion_status(status => 'ERROR',
895 message => NULL);
896 END IF;
897
898 COMMIT;
899
900 EXCEPTION
901 WHEN OTHERS THEN
902 fnd_file.put_line(fnd_file.log, 'An unexpected error occurred');
903 fnd_file.put_line(fnd_file.log,
904 'The following SQL Error happened : ' || SQLERRM);
905
906 UPDATE gcs_data_sub_dtls
907 SET status_code = DECODE(l_status_code,
908 'C',
909 'COMPLETED',
910 'E',
911 'ERROR',
912 'W',
913 'WARNING',
914 'WARNING'),
915 end_time = SYSDATE,
916 last_updated_by = FND_GLOBAL.USER_ID,
917 last_update_login = FND_GLOBAL.LOGIN_ID,
918 last_update_date = SYSDATE
919 WHERE associated_request_id = FND_GLOBAL.conc_request_id;
920
921 -- Bugfix 5347804: Need to update the most recent flag to 'N' for prior loads
922 UPDATE gcs_data_sub_dtls prev_runs
923 SET most_recent_flag = 'N'
924 WHERE EXISTS (SELECT 'X'
925 FROM gcs_data_sub_dtls curr_run
926 WHERE curr_run.associated_request_id =
927 FND_GLOBAL.conc_request_id
928 AND curr_run.entity_id = prev_runs.entity_id
929 AND curr_run.cal_period_id = prev_runs.cal_period_id
930 AND curr_run.currency_code = prev_runs.currency_code
931 AND curr_run.balance_type_code = prev_runs.balance_type_code
932 AND prev_runs.load_id < curr_run.load_id);
933
934 l_error_occurred := fnd_concurrent.set_completion_status(status => 'ERROR',
935 message => NULL);
936 END process_internal_entity;
937
938 -- End of private procedures
939
940 PROCEDURE submit_datasub(x_errbuf OUT NOCOPY VARCHAR2,
941 x_retcode OUT NOCOPY VARCHAR2,
942 p_load_id IN NUMBER)
943
944 IS
945
946 l_datasub_info r_datasub_info;
947 l_locked_flag VARCHAR2(1);
948
949 BEGIN
950 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
951 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
952 g_api || '.SUBMIT_DATASUB.begin',
953 '<<Enter>>');
954 END IF;
955
956 fnd_file.put_line(fnd_file.log, 'Beginning Data Submission Execution');
957
958 get_datasub_dtls(p_load_id => p_load_id,
959 p_datasub_info => l_datasub_info);
960
961 --Bugfix 6016288: Getting the most recent lock status for load entity, and preventing the data submission if
962 --locked flag is 'Y'
963
964 BEGIN
965 SELECT gdsd.locked_flag
966 INTO l_locked_flag
967 FROM gcs_data_sub_dtls gdsd
968 WHERE gdsd.entity_id = l_datasub_info.entity_id
969 AND gdsd.cal_period_id = l_datasub_info.cal_period_id
970 AND gdsd.balance_type_code = l_datasub_info.balance_type_code
971 AND NVL(gdsd.currency_code, 'X') = l_datasub_info.currency_code
972 AND gdsd.most_recent_flag = 'Y';
973
974 EXCEPTION
975 WHEN NO_DATA_FOUND THEN
976 l_locked_flag :='N';
977 END;
978
979 IF (l_locked_flag = 'N') THEN
980
981 UPDATE gcs_data_sub_dtls
982 SET most_recent_flag = 'N'
983 WHERE entity_id = l_datasub_info.entity_id
984 AND cal_period_id = l_datasub_info.cal_period_id
985 AND NVL(currency_code, 'X') = l_datasub_info.currency_code
986 AND balance_type_code = l_datasub_info.balance_type_code;
987
988 UPDATE gcs_data_sub_dtls
989 SET most_recent_flag = 'Y',
990 associated_request_id = FND_GLOBAL.conc_request_id
991 WHERE load_id = p_load_id;
992
993 COMMIT;
994
995 fnd_file.put_line(fnd_file.log,
996 'Checking Entity Type either External versus Oracle');
997
998 --Bugfix 4969879: Remove call to check source system, as it has moved to get_datasub_info
999 fnd_file.put_line(fnd_file.log,
1000 'Source System Code is : ' ||
1001 l_datasub_info.source_system_code);
1002
1003 --Bugfix 5112626: Need to use l_datasub_info.source_system_code rather than l_ledger_source_system_code
1004
1005 IF (l_datasub_info.source_system_code = 10) THEN
1006 process_internal_entity(p_load_id => p_load_id,
1007 p_datasub_info => l_datasub_info);
1008 ELSE
1009 process_external_entity(p_load_id => p_load_id,
1010 p_datasub_info => l_datasub_info);
1011 END IF;
1012
1013 --Bugfix 6016288: If Locked, Put the message into log file, set the request status to warning and delete the
1014 --record from gcs_data_sub_dtls which is failed to submit.
1015 ElSE
1016 fnd_file.put_line(fnd_file.log,
1017 'Recent Submission is locked, Unlock it and Resubmit');
1018
1019 DELETE gcs_data_sub_dtls
1020 WHERE load_id = l_datasub_info.load_id;
1021
1022 x_retcode := 1;
1023
1024 END IF;
1025
1026 COMMIT;
1027
1028 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1029 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1030 g_api || '.SUBMIT_DATASUB.end',
1031 '<<Exit>>');
1032 END IF;
1033 EXCEPTION
1034 WHEN OTHERS THEN
1035 fnd_file.put_line(fnd_file.log, SQLERRM);
1036 END submit_datasub;
1037
1038 PROCEDURE update_amounts_autonomous(p_datasub_info IN r_datasub_info,
1039 p_first_ever_loaded IN VARCHAR2,
1040 p_currency_type_code IN VARCHAR2) IS
1041 PRAGMA AUTONOMOUS_TRANSACTION;
1042
1043 l_datasub_info r_datasub_info := p_datasub_info;
1044 l_first_ever_loaded VARCHAR2(1) := p_first_ever_loaded;
1045 l_currency_type_code VARCHAR2(30) := p_currency_type_code;
1046
1047 BEGIN
1048 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1049 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1050 g_api || '.UPDATE_AMOUNTS_AUTONOMOUS.begin',
1051 '<<Enter>>');
1052 END IF;
1053
1054 IF (l_currency_type_code = 'TRANSLATED') THEN
1055 UPDATE gcs_bal_interface_t
1056 SET ytd_debit_balance_e = DECODE(l_datasub_info.measure_type_code,
1057 'BALANCE',
1058 DECODE(SIGN(ytd_balance_e),
1059 1,
1060 ytd_balance_e,
1061 0),
1062 ytd_debit_balance_e),
1063 ytd_credit_balance_e = DECODE(l_datasub_info.measure_type_code,
1064 'BALANCE',
1065 DECODE(SIGN(ytd_balance_e),
1066 -1,
1067 ABS(ytd_balance_e),
1068 0),
1069 ytd_credit_balance_e),
1070 ytd_balance_e = DECODE(l_datasub_info.measure_type_code,
1071 'DEBIT_CREDIT',
1072 NVL(ytd_debit_balance_e, 0) -
1073 NVL(ytd_credit_balance_e, 0),
1074 ytd_balance_e),
1075 ptd_debit_balance_e = DECODE(l_first_ever_loaded,
1076 'Y',
1077 DECODE(l_datasub_info.measure_type_code,
1078 'BALANCE',
1079 DECODE(SIGN(ytd_balance_e),
1080 1,
1081 ytd_balance_e,
1082 0),
1083 ytd_debit_balance_e),
1084 0),
1085 ptd_credit_balance_e = DECODE(l_first_ever_loaded,
1086 'Y',
1087 DECODE(l_datasub_info.measure_type_code,
1088 'BALANCE',
1089 DECODE(SIGN(ytd_balance_e),
1090 -1,
1091 ABS(ytd_balance_e),
1092 0),
1093 ytd_credit_balance_e),
1094 0),
1095 ptd_balance_e = DECODE(l_first_ever_loaded,
1096 'Y',
1097 DECODE(l_datasub_info.measure_type_code,
1098 'DEBIT_CREDIT',
1099 NVL(ytd_debit_balance_e, 0) -
1100 NVL(ytd_credit_balance_e, 0),
1101 ytd_balance_e),
1102 0),
1103 currency_code = DECODE(financial_elem_display_code,
1104 '10000',
1105 'STAT',
1106 l_datasub_info.currency_code)
1107 WHERE load_id = l_datasub_info.load_id;
1108
1109 ELSIF (l_datasub_info.currency_type_code = 'BASE_CURRENCY') THEN
1110 UPDATE gcs_bal_interface_t
1111 SET ytd_debit_balance_e = DECODE(l_datasub_info.measure_type_code,
1112 'BALANCE',
1113 DECODE(SIGN(ytd_balance_e),
1114 1,
1115 ytd_balance_e,
1116 0),
1117 ytd_debit_balance_e),
1118 ytd_credit_balance_e = DECODE(l_datasub_info.measure_type_code,
1119 'BALANCE',
1120 DECODE(SIGN(ytd_balance_e),
1121 -1,
1122 ABS(ytd_balance_e),
1123 0),
1124 ytd_credit_balance_e),
1125 ytd_balance_e = DECODE(l_datasub_info.measure_type_code,
1126 'DEBIT_CREDIT',
1127 NVL(ytd_debit_balance_e, 0) -
1128 NVL(ytd_credit_balance_e, 0),
1129 ytd_balance_e),
1130 ytd_debit_balance_f = DECODE(l_datasub_info.measure_type_code,
1131 'BALANCE',
1132 DECODE(SIGN(ytd_balance_e),
1133 1,
1134 ytd_balance_e,
1135 0),
1136 ytd_debit_balance_e),
1137 ytd_credit_balance_f = DECODE(l_datasub_info.measure_type_code,
1138 'BALANCE',
1139 DECODE(SIGN(ytd_balance_e),
1140 -1,
1141 ABS(ytd_balance_e),
1142 0),
1143 ytd_credit_balance_e),
1144 ytd_balance_f = DECODE(l_datasub_info.measure_type_code,
1145 'DEBIT_CREDIT',
1146 NVL(ytd_debit_balance_e, 0) -
1147 NVL(ytd_credit_balance_e, 0),
1148 ytd_balance_e),
1149 ptd_debit_balance_e = DECODE(l_datasub_info.measure_type_code,
1150 'BALANCE',
1151 DECODE(SIGN(ytd_balance_e),
1152 1,
1153 ytd_balance_e,
1154 0),
1155 ytd_debit_balance_e),
1156 ptd_credit_balance_e = DECODE(l_datasub_info.measure_type_code,
1157 'BALANCE',
1158 DECODE(SIGN(ytd_balance_e),
1159 -1,
1160 ABS(ytd_balance_e),
1161 0),
1162 ytd_credit_balance_e),
1163 ptd_balance_e = DECODE(l_datasub_info.measure_type_code,
1164 'DEBIT_CREDIT',
1165 NVL(ytd_debit_balance_e, 0) -
1166 NVL(ytd_credit_balance_e, 0),
1167 ytd_balance_e),
1168 ptd_debit_balance_f = DECODE(l_datasub_info.measure_type_code,
1169 'BALANCE',
1170 DECODE(SIGN(ytd_balance_e),
1171 1,
1172 ytd_balance_e,
1173 0),
1174 ytd_debit_balance_e),
1175 ptd_credit_balance_f = DECODE(l_datasub_info.measure_type_code,
1176 'BALANCE',
1177 DECODE(SIGN(ytd_balance_e),
1178 -1,
1179 ABS(ytd_balance_e),
1180 0),
1181 ytd_credit_balance_e),
1182 ptd_balance_f = DECODE(l_datasub_info.measure_type_code,
1183 'DEBIT_CREDIT',
1184 NVL(ytd_debit_balance_e, 0) -
1185 NVL(ytd_credit_balance_e, 0),
1186 ytd_balance_e),
1187 currency_code = DECODE(financial_elem_display_code,
1188 '10000',
1189 'STAT',
1190 l_datasub_info.currency_code)
1191 WHERE load_id = l_datasub_info.load_id;
1192
1193 ELSE
1194 UPDATE gcs_bal_interface_t
1195 SET ytd_debit_balance_e = DECODE(l_datasub_info.measure_type_code,
1196 'BALANCE',
1197 DECODE(SIGN(ytd_balance_e),
1198 1,
1199 ytd_balance_e,
1200 0),
1201 ytd_debit_balance_e),
1202 ytd_credit_balance_e = DECODE(l_datasub_info.measure_type_code,
1203 'BALANCE',
1204 DECODE(SIGN(ytd_balance_e),
1205 -1,
1206 ABS(ytd_balance_e),
1207 0),
1208 ytd_credit_balance_e),
1209 ytd_balance_e = DECODE(l_datasub_info.measure_type_code,
1210 'DEBIT_CREDIT',
1211 NVL(ytd_debit_balance_e, 0) -
1212 NVL(ytd_credit_balance_e, 0),
1213 ytd_balance_e),
1214 ytd_debit_balance_f = DECODE(l_datasub_info.measure_type_code,
1215 'BALANCE',
1216 DECODE(SIGN(ytd_balance_f),
1217 1,
1218 ytd_balance_f,
1219 0),
1220 ytd_debit_balance_f),
1221 ytd_credit_balance_f = DECODE(l_datasub_info.measure_type_code,
1222 'BALANCE',
1223 DECODE(SIGN(ytd_balance_f),
1224 -1,
1225 ABS(ytd_balance_f),
1226 0),
1227 ytd_credit_balance_f),
1228 ytd_balance_f = DECODE(l_datasub_info.measure_type_code,
1229 'DEBIT_CREDIT',
1230 NVL(ytd_debit_balance_f, 0) -
1231 NVL(ytd_credit_balance_f, 0),
1232 ytd_balance_f),
1233 ptd_debit_balance_e = DECODE(l_datasub_info.measure_type_code,
1234 'BALANCE',
1235 DECODE(SIGN(ytd_balance_e),
1236 1,
1237 ytd_balance_e,
1238 0),
1239 ytd_debit_balance_e),
1240 ptd_credit_balance_e = DECODE(l_datasub_info.measure_type_code,
1241 'BALANCE',
1242 DECODE(SIGN(ytd_balance_e),
1243 -1,
1244 ABS(ytd_balance_e),
1245 0),
1246 ytd_credit_balance_e),
1247 ptd_balance_e = DECODE(l_datasub_info.measure_type_code,
1248 'DEBIT_CREDIT',
1249 NVL(ytd_debit_balance_e, 0) -
1250 NVL(ytd_credit_balance_e, 0),
1251 ytd_balance_e),
1252 ptd_debit_balance_f = DECODE(l_datasub_info.measure_type_code,
1253 'BALANCE',
1254 DECODE(SIGN(ytd_balance_f),
1255 1,
1256 ytd_balance_f,
1257 0),
1258 ytd_debit_balance_f),
1259 ptd_credit_balance_f = DECODE(l_datasub_info.measure_type_code,
1260 'BALANCE',
1261 DECODE(SIGN(ytd_balance_f),
1262 -1,
1263 ABS(ytd_balance_f),
1264 0),
1265 ytd_credit_balance_f),
1266 ptd_balance_f = DECODE(l_datasub_info.measure_type_code,
1267 'DEBIT_CREDIT',
1268 NVL(ytd_debit_balance_f, 0) -
1269 NVL(ytd_credit_balance_f, 0),
1270 ytd_balance_f),
1271 financial_elem_display_code = DECODE(currency_code,
1272 'STAT',
1273 '10000',
1274 financial_elem_display_code),
1275 currency_code = DECODE(financial_elem_display_code,
1276 '10000',
1277 'STAT',
1278 currency_code)
1279 WHERE load_id = l_datasub_info.load_id;
1280
1281 END IF;
1282
1283 COMMIT;
1284
1285 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1286 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1287 g_api || '.UPDATE_AMOUNTS_AUTONOMOUS.end',
1288 '<<Exit>>');
1289 END IF;
1290
1291 END update_amounts_autonomous;
1292
1293 PROCEDURE transfer_data_autonomous(p_ledger_disp_code IN VARCHAR2,
1294 p_source_system_disp_code IN VARCHAR2,
1295 p_dim_grp_disp_code IN VARCHAR2,
1296 p_cal_period_number IN NUMBER,
1297 p_cal_period_end_date IN DATE,
1298 p_load_method_code IN VARCHAR2,
1299 p_bal_post_type_code IN VARCHAR2,
1300 p_currency_type_code IN VARCHAR2,
1301 p_entity_display_code IN VARCHAR2,
1302 p_load_id IN NUMBER,
1303 p_line_item_vs_id IN NUMBER,
1304 p_ds_balance_type_code IN VARCHAR2,
1305 --Bugfix 5066041: Added support for additional data types
1306 p_budget_display_code IN VARCHAR2,
1307 p_encumbrance_type_code IN VARCHAR2,
1308 --Bugfix 5261560: Added variable to track if transfer was successful
1309 p_transfer_status OUT NOCOPY VARCHAR2)
1310
1311 IS
1312 PRAGMA AUTONOMOUS_TRANSACTION;
1313
1314 l_line_item_type_attr NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info('LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE')
1315 .attribute_id;
1316 l_line_item_type_version NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info('LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE')
1317 .version_id;
1318 l_acct_type_attr NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info('EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE')
1319 .attribute_id;
1320 l_acct_type_version NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info('EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE')
1321 .version_id;
1322
1323 BEGIN
1324
1325 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1326 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1327 g_api || '.TRANSFER_DATA_AUTONOMOUS.begin',
1328 '<<Enter>>');
1329 END IF;
1330
1331 INSERT INTO fem_bal_interface_t
1332 (load_set_id,
1333 load_method_code,
1334 bal_post_type_code,
1335 cal_per_dim_grp_display_code,
1336 cal_period_number,
1337 cal_period_end_date,
1338 cctr_org_display_code,
1339 currency_code,
1340 currency_type_code,
1341 ds_balance_type_code,
1342 source_system_display_code,
1343 ledger_display_code,
1344 financial_elem_display_code,
1345 product_display_code,
1346 natural_account_display_code,
1347 channel_display_code,
1348 line_item_display_code,
1349 project_display_code,
1350 customer_display_code,
1351 entity_display_code,
1352 intercompany_display_code,
1353 task_display_code,
1354 user_dim1_display_code,
1355 user_dim2_display_code,
1356 user_dim3_display_code,
1357 user_dim4_display_code,
1358 user_dim5_display_code,
1359 user_dim6_display_code,
1360 user_dim7_display_code,
1361 user_dim8_display_code,
1362 user_dim9_display_code,
1363 user_dim10_display_code,
1364 xtd_balance_e,
1365 xtd_balance_f,
1366 ytd_balance_e,
1367 ytd_balance_f,
1368 ptd_debit_balance_e,
1369 ptd_credit_balance_e,
1370 ytd_debit_balance_e,
1371 ytd_credit_balance_e,
1372 --Bugfix 5066041: Added additional columns to support additional data types
1373 budget_display_code,
1374 encumbrance_type_code)
1375 SELECT p_load_id,
1376 p_load_method_code,
1377 p_bal_post_type_code,
1378 p_dim_grp_disp_code,
1379 p_cal_period_number,
1380 p_cal_period_end_date,
1381 gbit.cctr_org_display_code,
1382 gbit.currency_code,
1383 p_currency_type_code,
1384 p_ds_balance_type_code,
1385 p_source_system_disp_code,
1386 p_ledger_disp_code,
1387 gbit.financial_elem_display_code,
1388 gbit.product_display_code,
1389 gbit.natural_account_display_code,
1390 gbit.channel_display_code,
1391 gbit.line_item_display_code,
1392 gbit.project_display_code,
1393 gbit.customer_display_code,
1394 p_entity_display_code,
1395 gbit.intercompany_display_code,
1396 gbit.task_display_code,
1397 gbit.user_dim1_display_code,
1398 gbit.user_dim2_display_code,
1399 gbit.user_dim3_display_code,
1400 gbit.user_dim4_display_code,
1401 gbit.user_dim5_display_code,
1402 gbit.user_dim6_display_code,
1403 gbit.user_dim7_display_code,
1404 gbit.user_dim8_display_code,
1405 gbit.user_dim9_display_code,
1406 gbit.user_dim10_display_code,
1407 DECODE(feata.dim_attribute_varchar_member,
1408 'REVENUE',
1409 NVL(gbit.ptd_balance_e, gbit.ytd_balance_e),
1410 'EXPENSE',
1411 NVL(gbit.ptd_balance_e, gbit.ytd_balance_e),
1412 NVL(gbit.ytd_balance_e, gbit.ptd_balance_e)),
1413 DECODE(feata.dim_attribute_varchar_member,
1414 'REVENUE',
1415 NVL(gbit.ptd_balance_f, gbit.ytd_balance_f),
1416 'EXPENSE',
1417 NVL(gbit.ptd_balance_f, gbit.ytd_balance_f),
1418 NVL(gbit.ytd_balance_f, gbit.ptd_balance_f)),
1419 NVL(gbit.ytd_balance_e, gbit.ptd_balance_e),
1420 NVL(gbit.ytd_balance_f, gbit.ptd_balance_f),
1421 DECODE(feata.ext_account_type_code,
1422 'RETAINED_EARNINGS',
1423 0,
1424 NVL(gbit.ptd_debit_balance_e,
1425 NVL(gbit.ytd_debit_balance_e, 0))),
1426 DECODE(feata.ext_account_type_code,
1427 'RETAINED_EARNINGS',
1428 0,
1429 NVL(gbit.ptd_credit_balance_e,
1430 NVL(gbit.ytd_credit_balance_e, 0))),
1431 NVL(gbit.ytd_debit_balance_e, NVL(gbit.ptd_debit_balance_e, 0)),
1432 NVL(gbit.ytd_credit_balance_e,
1433 NVL(gbit.ptd_credit_balance_e, 0)),
1434 --Bugfix 5066041: Added additional columns to support new data types
1435 p_budget_display_code,
1436 p_encumbrance_type_code
1437 FROM gcs_bal_interface_t gbit,
1438 fem_ln_items_b flb,
1439 fem_ln_items_attr flia,
1440 fem_ext_acct_types_attr feata
1441 WHERE gbit.load_id = p_load_id
1442 AND gbit.line_item_display_code = flb.line_item_display_code
1443 AND flb.line_item_id = flia.line_item_id
1444 AND flb.value_set_id = p_line_item_vs_id
1445 -- Attribute for Extended Account Type
1446 -- Bugfix 4644576: Removed assigning the attributes using hardcoded literals
1447 AND flia.attribute_id = l_line_item_type_attr
1448 AND flia.version_id = l_line_item_type_version
1449 AND flia.value_set_id = flb.value_set_id
1450 AND flia.dim_attribute_varchar_member =
1451 feata.ext_account_type_code
1452 AND feata.attribute_id = l_acct_type_attr
1453 AND feata.version_id = l_acct_type_version;
1454
1455 COMMIT;
1456
1457 --Bugfix 5261560: Setting the transfer status to OK
1458 p_transfer_status := 'OK';
1459
1460 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1461 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1462 g_api || '.TRANSFER_DATA_AUTONOMOUS.end',
1463 '<<Exit>>');
1464 END IF;
1465
1466 --Bugfix 5261560: Trap errors on transfer to inform the user on errors
1467 EXCEPTION
1468 WHEN DUP_VAL_ON_INDEX THEN
1469 UPDATE gcs_data_sub_dtls
1470 SET status_code = 'INVALID_FEM_INDEX'
1471 WHERE load_id = p_load_id;
1472
1473 p_transfer_status := 'ERROR';
1474
1475 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
1476 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
1477 g_api || '.TRANSFER_DATA_AUTONOMOUS',
1478 '<<Beginning of Error>>');
1479 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
1480 g_api || '.TRANSFER_DATA_AUTONOMOUS',
1481 SQLERRM);
1482 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
1483 g_api || '.TRANSFER_DATA_AUTONOMOUS',
1484 '<<End of Error>>');
1485 END IF;
1486 COMMIT;
1487
1488 WHEN OTHERS THEN
1489 UPDATE gcs_data_sub_dtls
1490 SET status_code = 'TRANSFER_ERROR'
1491 WHERE load_id = p_load_id;
1492
1493 p_transfer_status := 'ERROR';
1494
1495 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
1496 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
1497 g_api || '.TRANSFER_DATA_AUTONOMOUS',
1498 '<<Beginning of Error>>');
1499 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
1500 g_api || '.TRANSFER_DATA_AUTONOMOUS',
1501 SQLERRM);
1502 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
1503 g_api || '.TRANSFER_DATA_AUTONOMOUS',
1504 '<<End of Error>>');
1505 END IF;
1506 COMMIT;
1507
1508 END transfer_data_autonomous;
1509
1510 PROCEDURE execute_autonomous(p_logic_type IN VARCHAR2,
1511 p_set_id IN NUMBER,
1512 p_load_id IN NUMBER,
1513 p_return_status IN OUT NOCOPY VARCHAR2) IS
1514
1515 PRAGMA AUTONOMOUS_TRANSACTION;
1516
1517 l_msg_count NUMBER(15);
1518 l_msg_data VARCHAR2(2000);
1519
1520 BEGIN
1521
1522 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1523 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1524 g_api || '.EXECUTE_AUTONOMOUS.begin',
1525 '<<Exit>>');
1526 END IF;
1527
1528 IF (p_logic_type = 'TRANSFORMATION') THEN
1529
1530 gcs_lex_map_api_pkg.apply_map(p_api_version => 1.0,
1531 p_init_msg_list => FND_API.G_FALSE,
1532 p_commit => FND_API.G_TRUE,
1533 p_validation_level => NULL,
1534 x_return_status => p_return_status,
1535 x_msg_count => l_msg_count,
1536 x_msg_data => l_msg_data,
1537 p_rule_set_id => p_set_id,
1538 p_staging_table_name => 'GCS_BAL_INTERFACE_T',
1539 p_debug_mode => NULL,
1540 p_filter_column_name1 => 'LOAD_ID',
1541 p_filter_column_value1 => p_load_id);
1542
1543 ELSIF (p_logic_type = 'VALIDATION') THEN
1544
1545 gcs_lex_map_api_pkg.apply_validation(p_api_version => 1.0,
1546 p_init_msg_list => FND_API.G_FALSE,
1547 p_commit => FND_API.G_TRUE,
1548 p_validation_level => NULL,
1549 x_return_status => p_return_status,
1550 x_msg_count => l_msg_count,
1551 x_msg_data => l_msg_data,
1552 p_rule_set_id => p_set_id,
1553 p_staging_table_name => 'GCS_BAL_INTERFACE_T',
1554 p_debug_mode => NULL,
1555 p_filter_column_name1 => 'LOAD_ID',
1556 p_filter_column_value1 => p_load_id);
1557
1558 END IF;
1559
1560 COMMIT;
1561 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1562 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1563 g_api || '.EXECUTE_AUTONOMOUS.end',
1564 '<<Exit>>');
1565 END IF;
1566
1567 EXCEPTION
1568 WHEN OTHERS THEN
1569 ROLLBACK;
1570 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
1571 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
1572 g_api || '.EXECUTE_AUTONOMOUS',
1573 SQLERRM);
1574 END IF;
1575 p_return_status := FND_API.G_RET_STS_ERROR;
1576 END;
1577
1578 -- End of Private Procedures
1579
1580 PROCEDURE check_idt_required(p_itemtype IN VARCHAR2,
1581 p_itemkey IN VARCHAR2,
1582 p_actid IN NUMBER,
1583 p_funcmode IN VARCHAR2,
1584 p_result IN OUT NOCOPY VARCHAR2) IS
1585
1586 l_datasub_info r_datasub_info;
1587
1588 BEGIN
1589
1590 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1591 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1592 g_api || '.CHECK_IDT_REQUIRED.begin',
1593 '<<Enter for itemkey : ' || p_itemkey || ' >>');
1594 END IF;
1595
1596 get_datasub_dtls(p_load_id => WF_ENGINE.GetItemAttrNumber(p_itemtype,
1597 p_itemkey,
1598 'LOAD_ID',
1599 FALSE),
1600 p_datasub_info => l_datasub_info);
1601
1602 IF (l_datasub_info.transform_rule_set_id IS NOT NULL) THEN
1603 p_result := 'COMPLETE:T';
1604 ELSE
1605 p_result := 'COMPLETE:F';
1606 END IF;
1607
1608 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1609 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1610 g_api || '.CHECK_IDT_REQUIRED.end',
1611 '<<Exit for itemkey : ' || p_itemkey || ' >>');
1612 END IF;
1613
1614 END check_idt_required;
1615
1616 PROCEDURE execute_idt(p_itemtype IN VARCHAR2,
1617 p_itemkey IN VARCHAR2,
1618 p_actid IN NUMBER,
1619 p_funcmode IN VARCHAR2,
1620 p_result IN OUT NOCOPY VARCHAR2) IS
1621
1622 l_datasub_info r_datasub_info;
1623 l_return_status VARCHAR2(1);
1624
1625 BEGIN
1626
1627 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1628 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1629 g_api || '.EXECUTE_IDT.begin',
1630 '<<Enter for itemkey : ' || p_itemkey || ' >>');
1631 END IF;
1632
1633 get_datasub_dtls(p_load_id => WF_ENGINE.GetItemAttrNumber(p_itemtype,
1634 p_itemkey,
1635 'LOAD_ID',
1636 FALSE),
1637 p_datasub_info => l_datasub_info);
1638
1639 execute_autonomous(p_logic_type => 'TRANSFORMATION',
1640 p_set_id => l_datasub_info.transform_rule_set_id,
1641 p_load_id => l_datasub_info.load_id,
1642 p_return_status => l_return_status);
1643
1644 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1645 p_result := 'COMPLETE:T';
1646 ELSE
1647 p_result := 'COMPLETE:F';
1648 UPDATE gcs_data_sub_dtls
1649 SET status_code = 'TRANSFORMATION_FAILED'
1650 WHERE load_id = l_datasub_info.load_id;
1651 END IF;
1652
1653 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1654 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1655 g_api || '.EXECUTE_IDT.end',
1656 '<<Exit for itemkey : ' || p_itemkey || ' >>');
1657 END IF;
1658
1659 END execute_idt;
1660
1661 PROCEDURE check_validation_required(p_itemtype IN VARCHAR2,
1662 p_itemkey IN VARCHAR2,
1663 p_actid IN NUMBER,
1664 p_funcmode IN VARCHAR2,
1665 p_result IN OUT NOCOPY VARCHAR2) IS
1666
1667 l_datasub_info r_datasub_info;
1668
1669 BEGIN
1670
1671 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1672 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1673 g_api || '.CHECK_VALIDATION_REQUIRED.begin',
1674 '<<Enter for itemkey : ' || p_itemkey || ' >>');
1675 END IF;
1676
1677 get_datasub_dtls(p_load_id => WF_ENGINE.GetItemAttrNumber(p_itemtype,
1678 p_itemkey,
1679 'LOAD_ID',
1680 FALSE),
1681 p_datasub_info => l_datasub_info);
1682
1683 IF (l_datasub_info.validation_rule_set_id IS NOT NULL) THEN
1684 p_result := 'COMPLETE:T';
1685 ELSE
1686 p_result := 'COMPLETE:F';
1687 END IF;
1688
1689 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1690 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1691 g_api || '.CHECK_VALIDATION_REQUIRED.end',
1692 '<<Exit for itemkey : ' || p_itemkey || ' >>');
1693 END IF;
1694
1695 END check_validation_required;
1696
1697 PROCEDURE execute_validation(p_itemtype IN VARCHAR2,
1698 p_itemkey IN VARCHAR2,
1699 p_actid IN NUMBER,
1700 p_funcmode IN VARCHAR2,
1701 p_result IN OUT NOCOPY VARCHAR2) IS
1702
1703 l_datasub_info r_datasub_info;
1704 l_return_status VARCHAR2(1);
1705
1706 BEGIN
1707
1708 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1709 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1710 g_api || '.EXECUTE_VALIDATION.begin',
1711 '<<Enter for itemkey : ' || p_itemkey || ' >>');
1712 END IF;
1713
1714 get_datasub_dtls(p_load_id => WF_ENGINE.GetItemAttrNumber(p_itemtype,
1715 p_itemkey,
1716 'LOAD_ID',
1717 FALSE),
1718 p_datasub_info => l_datasub_info);
1719
1720 execute_autonomous(p_logic_type => 'VALIDATION',
1721 p_set_id => l_datasub_info.validation_rule_set_id,
1722 p_load_id => l_datasub_info.load_id,
1723 p_return_status => l_return_status);
1724
1725 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1726 p_result := 'COMPLETE:T';
1727 ELSE
1728 p_result := 'COMPLETE:F';
1729 UPDATE gcs_data_sub_dtls
1730 SET status_code = 'VALIDATION_FAILED'
1731 WHERE load_id = l_datasub_info.load_id;
1732 END IF;
1733
1734 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1735 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1736 g_api || '.EXECUTE_VALIDATION.end',
1737 '<<Exit for itemkey : ' || p_itemkey || ' >>');
1738 END IF;
1739
1740 END execute_validation;
1741
1742 PROCEDURE init_datasub_process(p_itemtype IN VARCHAR2,
1743 p_itemkey IN VARCHAR2,
1744 p_actid IN NUMBER,
1745 p_funcmode IN VARCHAR2,
1746 p_result IN OUT NOCOPY VARCHAR2) IS
1747
1748 l_datasub_info r_datasub_info;
1749 l_currency_type_code VARCHAR2(30);
1750 l_first_ever_loaded VARCHAR2(1);
1751 l_func_crncy_code VARCHAR2(30);
1752 l_execution_mode VARCHAR2(1);
1753
1754 BEGIN
1755
1756 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1757 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1758 g_api || '.INIT_DATASUB_PROCESS.begin',
1759 '<<Enter for itemkey : ' || p_itemkey || ' >>');
1760 END IF;
1761
1762 get_datasub_dtls(p_load_id => WF_ENGINE.GetItemAttrNumber(p_itemtype,
1763 p_itemkey,
1764 'LOAD_ID',
1765 FALSE),
1766 p_datasub_info => l_datasub_info);
1767
1768 --Bugfix 4969879: Remove call to get_reference_data_info
1769
1770 SELECT fla.dim_attribute_varchar_member
1771 INTO l_func_crncy_code
1772 FROM fem_ledgers_attr fla
1773 WHERE fla.ledger_id = l_datasub_info.ledger_id
1774 AND fla.attribute_id = g_ledger_curr_attr
1775 AND fla.version_id = g_ledger_curr_version;
1776
1777 IF (l_datasub_info.currency_type_code = 'BASE_CURRENCY') THEN
1778 IF (l_datasub_info.currency_code <> l_func_crncy_code) THEN
1779 l_currency_type_code := 'TRANSLATED';
1780 ELSE
1781 l_currency_type_code := 'ENTERED';
1782 END IF;
1783 ELSE
1784 l_currency_type_code := 'ENTERED';
1785 END IF;
1786
1787 -- Check if this is first ever period
1788 IF (l_datasub_info.load_method_code IN
1789 ('INITIAL_LOAD', 'UNDO_AND_REPLACE', 'REPLACE')) THEN
1790 BEGIN
1791 SELECT 'N'
1792 INTO l_first_ever_loaded
1793 FROM gcs_data_sub_dtls gdsd
1794 WHERE gdsd.entity_id = l_datasub_info.entity_id
1795 AND gdsd.balance_type_code = l_datasub_info.balance_type_code
1796 AND gdsd.cal_period_id < l_datasub_info.cal_period_id
1797 AND NVL(gdsd.currency_code, l_func_crncy_code) =
1798 NVL(l_datasub_info.currency_code, l_func_crncy_code)
1799 AND ROWNUM < 2;
1800 EXCEPTION
1801 WHEN NO_DATA_FOUND THEN
1802 l_first_ever_loaded := 'Y';
1803 END;
1804 ELSE
1805 l_first_ever_loaded := 'N';
1806 END IF;
1807
1808 IF (l_first_ever_loaded = 'N') AND
1809 (l_datasub_info.load_method_code = 'INCREMENTAL') THEN
1810 l_first_ever_loaded := 'Y';
1811 END IF;
1812
1813 --Check to see if it is first load for the period
1814 BEGIN
1815 SELECT 'I'
1816 INTO l_execution_mode
1817 FROM fem_data_locations fdl
1818 WHERE fdl.ledger_id = l_datasub_info.ledger_id
1819 AND fdl.cal_period_id = l_datasub_info.cal_period_id
1820 AND fdl.dataset_code = l_datasub_info.dataset_code
1821 AND fdl.source_system_code = l_datasub_info.source_system_code
1822 AND rownum < 2;
1823 EXCEPTION
1824 WHEN NO_DATA_FOUND THEN
1825 l_execution_mode := 'S';
1826 END;
1827
1828 WF_ENGINE.SetItemAttrText(p_itemtype,
1829 p_itemkey,
1830 'CURRENCYTYPE',
1831 l_currency_type_code);
1832 WF_ENGINE.SetItemAttrText(p_itemtype,
1833 p_itemkey,
1834 'FIRSTEVERPERIOD',
1835 l_first_ever_loaded);
1836 WF_ENGINE.SetItemAttrText(p_itemtype,
1837 p_itemkey,
1838 'FUNCCURRENCYCODE',
1839 l_func_crncy_code);
1840 WF_ENGINE.SetItemAttrNumber(p_itemtype,
1841 p_itemkey,
1842 'LEDGERID',
1843 l_datasub_info.ledger_id);
1844 WF_ENGINE.SetItemAttrText(p_itemtype,
1845 p_itemkey,
1846 'CALPERIODID',
1847 l_datasub_info.cal_period_id);
1848 WF_ENGINE.SetItemAttrText(p_itemtype,
1849 p_itemkey,
1850 'EXECUTIONMODE',
1851 l_execution_mode);
1852
1853 p_result := 'COMPLETE';
1854
1855 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1856 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1857 g_api || '.INIT_DATASUB_PROCESS.end',
1858 '<<Exit for itemkey : ' || p_itemkey || ' >>');
1859 END IF;
1860
1861 END init_datasub_process;
1862
1863 PROCEDURE update_amounts(p_itemtype IN VARCHAR2,
1864 p_itemkey IN VARCHAR2,
1865 p_actid IN NUMBER,
1866 p_funcmode IN VARCHAR2,
1867 p_result IN OUT NOCOPY VARCHAR2) IS
1868
1869 l_datasub_info r_datasub_info;
1870 l_currency_type_code VARCHAR2(30);
1871 l_first_ever_loaded VARCHAR2(1);
1872 l_cal_period_info gcs_utility_pkg.r_cal_period_info;
1873
1874 BEGIN
1875
1876 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1877 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1878 g_api || '.UPDATE_AMOUNTS.begin',
1879 '<<Enter for itemkey : ' || p_itemkey || ' >>');
1880 END IF;
1881
1882 l_currency_type_code := WF_ENGINE.GetItemAttrText(p_itemtype,
1883 p_itemkey,
1884 'CURRENCYTYPE',
1885 FALSE);
1886 l_first_ever_loaded := WF_ENGINE.GetItemAttrText(p_itemtype,
1887 p_itemkey,
1888 'FIRSTEVERPERIOD',
1889 FALSE);
1890
1891 get_datasub_dtls(p_load_id => WF_ENGINE.GetItemAttrNumber(p_itemtype,
1892 p_itemkey,
1893 'LOAD_ID',
1894 FALSE),
1895 p_datasub_info => l_datasub_info);
1896
1897 update_amounts_autonomous(p_datasub_info => l_datasub_info,
1898 p_currency_type_code => l_currency_type_code,
1899 p_first_ever_loaded => l_first_ever_loaded);
1900
1901 IF (l_first_ever_loaded = 'N') THEN
1902 gcs_utility_pkg.get_cal_period_details(p_cal_period_id => l_datasub_info.cal_period_id,
1903 p_cal_period_record => l_cal_period_info);
1904
1905 --Bugfix 4969879: Remove call to get_reference_data_info
1906
1907 IF (l_datasub_info.amount_type_code = 'PERIOD_ACTIVITY') THEN
1908 -- Calculate the YTD Amounts Based off of Beginning Balances
1909 gcs_datasub_utility_pkg.update_ytd_balances(p_load_id => l_datasub_info.load_id,
1910 p_source_system_code => l_datasub_info.source_system_code,
1911 p_dataset_code => l_datasub_info.dataset_code,
1912 p_cal_period_id => l_cal_period_info.prev_cal_period_id,
1913 p_ledger_id => l_datasub_info.ledger_id,
1914 p_currency_type => l_currency_type_code,
1915 p_currency_code => l_datasub_info.currency_code);
1916 ELSE
1917 -- Calculate the PTD Amount Based off of Difference of Ending Balances
1918
1919 IF (l_cal_period_info.cal_period_number <> 1) THEN
1920
1921 gcs_datasub_utility_pkg.update_ptd_balances(p_load_id => l_datasub_info.load_id,
1922 p_source_system_code => l_datasub_info.source_system_code,
1923 p_dataset_code => l_datasub_info.dataset_code,
1924 p_cal_period_id => l_cal_period_info.prev_cal_period_id,
1925 p_ledger_id => l_datasub_info.ledger_id,
1926 p_currency_type => l_currency_type_code,
1927 p_currency_code => l_datasub_info.currency_code);
1928 ELSE
1929
1930 gcs_datasub_utility_pkg.update_ptd_balance_sheet(p_load_id => l_datasub_info.load_id,
1931 p_source_system_code => l_datasub_info.source_system_code,
1932 p_dataset_code => l_datasub_info.dataset_code,
1933 p_cal_period_id => l_cal_period_info.prev_cal_period_id,
1934 p_ledger_id => l_datasub_info.ledger_id,
1935 p_currency_type => l_currency_type_code,
1936 p_currency_code => l_datasub_info.currency_code);
1937 END IF;
1938
1939 END IF;
1940
1941 END IF;
1942
1943 p_result := 'COMPLETE';
1944
1945 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1946 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1947 g_api || '.UPDATE_AMOUNTS.end',
1948 '<<Exit for itemkey : ' || p_itemkey || ' >>');
1949 END IF;
1950 END update_amounts;
1951
1952 PROCEDURE transfer_data_to_interface(p_itemtype IN VARCHAR2,
1953 p_itemkey IN VARCHAR2,
1954 p_actid IN NUMBER,
1955 p_funcmode IN VARCHAR2,
1956 p_result IN OUT NOCOPY VARCHAR2) IS
1957
1958 l_datasub_info r_datasub_info;
1959 l_currency_type_code VARCHAR2(30);
1960 l_first_ever_loaded VARCHAR2(1);
1961 l_cal_period_info gcs_utility_pkg.r_cal_period_info;
1962 l_period_end_date_attr NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
1963 .attribute_id;
1964 l_period_end_date_version NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
1965 .version_id;
1966 l_period_num_attr NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-GL_PERIOD_NUM')
1967 .attribute_id;
1968 l_period_num_version NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-GL_PERIOD_NUM')
1969 .version_id;
1970 l_period_end_date DATE;
1971 l_period_num NUMBER(15);
1972 l_period_dim_grp_disp_code VARCHAR2(50);
1973 l_source_system_disp_code VARCHAR2(50);
1974 l_load_method_code VARCHAR2(1);
1975 l_bal_post_type_code VARCHAR2(1);
1976 l_line_item_vs_id NUMBER;
1977 l_transfer_status VARCHAR2(30);
1978
1979 BEGIN
1980
1981 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1982 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1983 g_api || '.TRANSFER_DATA_TO_INTERFACE.begin',
1984 '<<Enter for itemkey : ' || p_itemkey || ' >>');
1985 END IF;
1986
1987 l_currency_type_code := WF_ENGINE.GetItemAttrText(p_itemtype,
1988 p_itemkey,
1989 'CURRENCYTYPE',
1990 FALSE);
1991
1992 get_datasub_dtls(p_load_id => WF_ENGINE.GetItemAttrNumber(p_itemtype,
1993 p_itemkey,
1994 'LOAD_ID',
1995 FALSE),
1996 p_datasub_info => l_datasub_info);
1997
1998 --Bugfix 4969879: Remove calls to get_reference_data_info
1999
2000 --Initialize Workflow Attributes for Dataset Code and Object Id
2001 WF_ENGINE.SetItemAttrText(p_itemtype,
2002 p_itemkey,
2003 'DATASETCODE',
2004 l_datasub_info.dataset_code);
2005 WF_ENGINE.SetItemAttrText(p_itemtype, p_itemkey, 'XGLOBJECTID', '1000');
2006
2007 SELECT fdgb.dimension_group_display_code,
2008 fcpa_end_date.date_assign_value,
2009 fcpa_period_num.number_assign_value
2010 INTO l_period_dim_grp_disp_code,
2011 l_period_end_date,
2012 l_period_num
2013 FROM fem_cal_periods_b fcpb,
2014 fem_dimension_grps_b fdgb,
2015 fem_cal_periods_attr fcpa_end_date,
2016 fem_cal_periods_attr fcpa_period_num
2017 WHERE fcpb.cal_period_id = l_datasub_info.cal_period_id
2018 AND fcpb.dimension_group_id = fdgb.dimension_group_id
2019 AND fcpb.cal_period_id = fcpa_end_date.cal_period_id
2020 AND fcpa_end_date.attribute_id = l_period_end_date_attr
2021 AND fcpa_end_date.version_id = l_period_end_date_version
2022 AND fcpb.cal_period_id = fcpa_period_num.cal_period_id
2023 AND fcpa_period_num.attribute_id = l_period_num_attr
2024 AND fcpa_period_num.version_id = l_period_num_version;
2025
2026 SELECT source_system_display_code
2027 INTO l_source_system_disp_code
2028 FROM fem_source_systems_b
2029 WHERE source_system_code = l_datasub_info.source_system_code;
2030
2031 BEGIN
2032 SELECT 'I'
2033 INTO l_load_method_code
2034 FROM fem_data_locations fdl,
2035 fem_ledgers_attr fla
2036 WHERE fdl.ledger_id = l_datasub_info.ledger_id
2037 AND fdl.cal_period_id = l_datasub_info.cal_period_id
2038 AND fdl.dataset_code = l_datasub_info.dataset_code
2039 AND fdl.source_system_code = l_datasub_info.source_system_code
2040 AND rownum < 2;
2041 EXCEPTION
2042 WHEN NO_DATA_FOUND THEN
2043 l_load_method_code := 'S';
2044 END;
2045
2046 IF (l_datasub_info.load_method_code = 'INITIAL_LOAD') THEN
2047 l_bal_post_type_code := 'R';
2048 ELSE
2049 IF (l_datasub_info.load_method_code = 'INCREMENTAL') THEN
2050 l_bal_post_type_code := 'A';
2051 ELSE
2052 l_bal_post_type_code := 'R';
2053 END IF;
2054 END IF;
2055
2056 SELECT fgvcd.value_set_id
2057 INTO l_line_item_vs_id
2058 FROM fem_ledgers_attr fla,
2059 fem_global_vs_combo_defs fgvcd
2060 WHERE fla.ledger_id = l_datasub_info.ledger_id
2061 AND fgvcd.global_vs_combo_id = fla.dim_attribute_numeric_member
2062 AND fla.attribute_id = g_ledger_vs_combo_attr
2063 AND fla.version_id = g_ledger_vs_combo_version
2064 AND fgvcd.dimension_id = 14;
2065
2066 transfer_data_autonomous(p_ledger_disp_code => l_datasub_info.ledger_display_code,
2067 p_source_system_disp_code => l_source_system_disp_code,
2068 p_dim_grp_disp_code => l_period_dim_grp_disp_code,
2069 p_cal_period_number => l_period_num,
2070 p_cal_period_end_date => l_period_end_date,
2071 p_load_method_code => l_load_method_code,
2072 p_bal_post_type_code => l_bal_post_type_code,
2073 p_currency_type_code => l_currency_type_code,
2074 p_entity_display_code => l_datasub_info.entity_display_code,
2075 p_load_id => l_datasub_info.load_id,
2076 p_line_item_vs_id => l_line_item_vs_id,
2077 p_ds_balance_type_code => l_datasub_info.ds_balance_type_code,
2078 p_budget_display_code => l_datasub_info.budget_display_code,
2079 p_encumbrance_type_code => l_datasub_info.encumbrance_type_code,
2080 -- Bugfix 5261560: Added p_transfer_status
2081 p_transfer_status => l_transfer_status);
2082
2083 --Bugfix 5261560: Determine whether to continue with the rest of the submission depending on the state of the workflow
2084 IF (l_transfer_status = 'OK') THEN
2085 p_result := 'COMPLETE:T';
2086 ELSE
2087 p_result := 'COMPLETE:F';
2088 END IF;
2089
2090 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2091 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2092 g_api || '.TRANSFER_DATA_TO_INTERFACE.end',
2093 '<<Exit for itemkey : ' || p_itemkey || ' >>');
2094 END IF;
2095
2096 END transfer_data_to_interface;
2097
2098 PROCEDURE update_status(p_load_id IN NUMBER)
2099
2100 IS
2101
2102 l_datasub_info r_datasub_info;
2103 l_status_code VARCHAR2(1);
2104
2105 BEGIN
2106
2107 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2108 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2109 g_api || '.UPDATE_STATUS.begin',
2110 '<<Enter>>');
2111 END IF;
2112
2113 get_datasub_dtls(p_load_id => p_load_id,
2114 p_datasub_info => l_datasub_info);
2115
2116 SELECT status_code
2117 INTO l_status_code
2118 FROM fnd_concurrent_requests
2119 WHERE request_id = FND_GLOBAL.conc_request_id;
2120
2121 UPDATE gcs_data_sub_dtls
2122 SET status_code = DECODE(l_status_code,
2123 'C',
2124 'COMPLETED',
2125 'E',
2126 'ERROR',
2127 'W',
2128 'WARNING',
2129 'WARNING'),
2130 end_time = sysdate,
2131 last_updated_by = FND_GLOBAL.USER_ID,
2132 last_update_login = FND_GLOBAL.LOGIN_ID,
2133 last_update_date = sysdate
2134 WHERE load_id = l_datasub_info.load_id;
2135
2136 IF (l_status_code <> 'C') THEN
2137 IF (l_status_code = 'E') THEN
2138 -- Update the entire set of rows with the same error message
2139 UPDATE gcs_bal_interface_t
2140 SET error_message_code = 'Please refer to concurrent request : ' ||
2141 FND_GLOBAL.conc_request_id ||
2142 ' for more details.'
2143 WHERE load_id = l_datasub_info.load_id;
2144 ELSE
2145 -- Update the rows to check FEM_BAL_INTERFACE_T for remaining issues
2146 UPDATE gcs_bal_interface_t
2147 SET error_message_code = 'Please refer to FEM_BAL_INTERFACE_T to see if any rows failed.'
2148 WHERE load_id = l_datasub_info.load_id;
2149 END IF;
2150 END IF;
2151
2152 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2153 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2154 g_api || '.UPDATE_STATUS.end',
2155 '<<Exit>>');
2156 END IF;
2157
2158 END update_status;
2159
2160 PROCEDURE raise_impact_analysis_event(p_load_id IN NUMBER,
2161 p_ledger_id IN NUMBER)
2162
2163 IS
2164
2165 l_event_name VARCHAR2(100) := 'oracle.apps.gcs.pristinedata.altered';
2166 l_parameter_list wf_parameter_list_t;
2167
2168 BEGIN
2169
2170 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2171 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2172 g_api || '.RAISE_IMPACT_ANALYSIS_EVENT.begin',
2173 '<<Enter>>');
2174 END IF;
2175
2176 wf_event.addparametertolist(p_name => 'LOAD_ID',
2177 p_value => p_load_id,
2178 p_parameterlist => l_parameter_list);
2179
2180 wf_event.addparametertolist(p_name => 'LEDGER_ID',
2181 p_value => p_ledger_id,
2182 p_parameterlist => l_parameter_list);
2183
2184 wf_event.raise(p_event_name => l_event_name,
2185 p_event_key => p_load_id,
2186 p_parameters => l_parameter_list);
2187
2188 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2189 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2190 g_api || '.RAISE_IMPACT_ANALYSIS_EVENT.end',
2191 '<<Exit>>');
2192 END IF;
2193
2194 END raise_impact_analysis_event;
2195
2196 PROCEDURE submit_ogl_datasub(p_load_id IN NUMBER,
2197 p_request_id OUT NOCOPY NUMBER) IS
2198
2199 BEGIN
2200
2201 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2202 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2203 g_api || '.SUBMIT_OGL_DATASUB.begin',
2204 '<<Enter>>');
2205 END IF;
2206
2207 p_request_id := fnd_request.submit_request(application => 'GCS',
2208 program => 'FCH_DATA_SUBMISSION',
2209 sub_request => FALSE,
2210 argument1 => p_load_id);
2211
2212 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2213 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2214 g_api || '.SUBMIT_OGL_DATASUB.end',
2215 '<<Exit>>');
2216 END IF;
2217
2218 END;
2219
2220 -- Bug Fix : 5234796, Start
2221 PROCEDURE validate_member_values(p_itemtype IN VARCHAR2,
2222 p_itemkey IN VARCHAR2,
2223 p_actid IN NUMBER,
2224 p_funcmode IN VARCHAR2,
2225 p_result IN OUT NOCOPY VARCHAR2) IS
2226
2227 l_datasub_info r_datasub_info;
2228
2229 TYPE msg_info_rec_type IS RECORD(
2230 error_msg CLOB);
2231 TYPE t_msg_info IS TABLE OF msg_info_rec_type;
2232 l_msg_info t_msg_info;
2233
2234 BEGIN
2235
2236 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2237 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2238 g_api || '.VALIDATE_MEMBER_VALUES.begin',
2239 '<<Enter for itemkey : ' || p_itemkey || ' >>');
2240 END IF;
2241
2242 get_datasub_dtls(p_load_id => WF_ENGINE.GetItemAttrNumber(p_itemtype,
2243 p_itemkey,
2244 'LOAD_ID',
2245 FALSE),
2246 p_datasub_info => l_datasub_info);
2247
2248 gcs_datasub_utility_pkg.validate_dimension_members(p_load_id => l_datasub_info.load_id);
2249
2250 SELECT error_message_code BULK COLLECT
2251 INTO l_msg_info
2252 FROM gcs_bal_interface_t
2253 WHERE load_id = l_datasub_info.load_id;
2254
2255 p_result := 'COMPLETE:T';
2256
2257 IF l_msg_info.FIRST IS NOT NULL THEN
2258 FOR l_counter in l_msg_info.FIRST .. l_msg_info.LAST LOOP
2259 IF l_msg_info(l_counter).error_msg IS NOT NULL THEN
2260
2261 FND_FILE.PUT_LINE(FND_FILE.LOG, '<<<< Beginning of Error >>>>');
2262 FND_FILE.NEW_LINE(FND_FILE.LOG);
2263 FND_FILE.PUT_LINE(FND_FILE.LOG,
2264 'One or more of the dimension members are invalid.Please refer View Data Loaded Report');
2265 FND_FILE.NEW_LINE(FND_FILE.LOG);
2266 FND_FILE.PUT_LINE(FND_FILE.LOG, '<<<< End of Error >>>>');
2267 FND_FILE.NEW_LINE(FND_FILE.LOG);
2268
2269 UPDATE gcs_data_sub_dtls
2270 SET status_code = 'VALIDATION_MEMBERS_FAILED'
2271 WHERE load_id = l_datasub_info.load_id;
2272
2273 p_result := 'COMPLETE:F';
2274 EXIT;
2275 END IF;
2276 END LOOP;
2277 END IF;
2278
2279 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2280 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2281 g_api || '.VALIDATE_MEMBER_VALUES.end',
2282 '<<Exit for itemkey : ' || p_itemkey || ' >>');
2283 END IF;
2284
2285 COMMIT;
2286
2287 END validate_member_values;
2288 -- Bug Fix : 5234796, End
2289
2290 --
2291 -- function
2292 -- populate_ogl_datasub_dtls
2293 -- Purpose
2294 -- An API to populate the gcs_dats_sub_dtls.
2295 -- This API has subscription with the business event "oracle.apps.fem.oglintg.balrule.execute"
2296 -- Arguments
2297 -- p_subscription_guid - This subscription GUID is passed when the event is raised
2298 -- p_event - wf_event_t param
2299 -- Notes
2300 --
2301
2302 FUNCTION populate_ogl_datasub_dtls(p_subscription_guid in raw,
2303 p_event in out nocopy wf_event_t)
2304 RETURN VARCHAR2 IS
2305 l_parameter_list wf_parameter_list_t;
2306 l_bal_rule_version_id NUMBER;
2307 l_bal_rule_id NUMBER;
2308 l_ledger_id NUMBER;
2309 l_ds_bal_type_code VARCHAR2(30);
2310 l_cal_period_id NUMBER;
2311 l_request_id NUMBER;
2312 l_base_request_id NUMBER;
2313 l_load_method_code VARCHAR2(1);
2314 l_load_method VARCHAR2(30);
2315 l_start_date DATE;
2316 l_status_code VARCHAR2(30);
2317 l_status VARCHAR2(30);
2318 l_user_id NUMBER;
2319 l_login_id NUMBER;
2320 l_bsv_low VARCHAR2(150);
2321 l_bsv_high VARCHAR2(150);
2322 l_data_sub_exists_via_ui VARCHAR2(1);
2323 l_curr_code VARCHAR2(30);
2324 l_avg_bal_flag VARCHAR2(30);
2325 l_cal_period_end_date DATE;
2326
2327 l_global_vs_combo_id NUMBER(9);
2328 l_company_vs_id NUMBER;
2329 l_org_vs_id NUMBER;
2330 l_fch_company_vs_id NUMBER;
2331 l_fch_org_vs_id NUMBER;
2332 l_hier_obj_definition_id NUMBER(9);
2333
2334 l_entity_list DBMS_SQL.NUMBER_TABLE;
2335 l_xlated_curr_list DBMS_SQL.VARCHAR2_TABLE;
2336 l_generated_load_list DBMS_SQL.NUMBER_TABLE;
2337
2338 l_global_vs_combo_attr NUMBER := gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-GLOBAL_VS_COMBO')
2339 .attribute_id;
2340 l_global_vs_combo_version NUMBER := gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-GLOBAL_VS_COMBO')
2341 .version_id;
2342 l_company_attr NUMBER := gcs_utility_pkg.g_dimension_attr_info('COMPANY_COST_CENTER_ORG_ID-COMPANY')
2343 .attribute_id;
2344 l_company_version NUMBER := gcs_utility_pkg.g_dimension_attr_info('COMPANY_COST_CENTER_ORG_ID-COMPANY')
2345 .version_id;
2346 l_period_end_date_attr NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
2347 .attribute_id;
2348 l_period_end_date_version NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
2349 .version_id;
2350
2351 BEGIN
2352
2353 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2354 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2355 g_api || '.POPULATE_OGL_DATASUB_DTLS.begin',
2356 '<< Enter >>');
2357 END IF;
2358
2359 l_parameter_list := p_event.getParameterList();
2360 l_bal_rule_version_id := TO_NUMBER(WF_EVENT.getValueForParameter('BAL_RULE_OBJ_DEF_ID',
2361 l_parameter_list));
2362 l_cal_period_id := TO_NUMBER(WF_EVENT.getValueForParameter('CAL_PERIOD_ID',
2363 l_parameter_list));
2364 l_request_id := TO_NUMBER(WF_EVENT.getValueForParameter('REQUEST_ID',
2365 l_parameter_list));
2366 l_base_request_id := TO_NUMBER(WF_EVENT.getValueForParameter('BASE_REQUEST_ID',
2367 l_parameter_list));
2368 l_load_method_code := WF_EVENT.getValueForParameter('LOAD_METHOD_CODE',
2369 l_parameter_list);
2370 l_bsv_low := WF_EVENT.getValueForParameter('BSV_RANGE_LOW',
2371 l_parameter_list);
2372 l_bsv_high := WF_EVENT.getValueForParameter('BSV_RANGE_HIGH',
2373 l_parameter_list);
2374 l_status_code := WF_EVENT.getValueForParameter('STATUS_CODE',
2375 l_parameter_list);
2376 l_user_id := FND_GLOBAL.user_id;
2377 l_login_id := FND_GLOBAL.login_id;
2378 l_data_sub_exists_via_ui := 'N';
2379
2380 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2381 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2382 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2383 '<< Parameters on event : Start >>');
2384 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2385 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2386 'Balance Rule Version Id : ' || l_bal_rule_version_id);
2387 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2388 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2389 'Cal Period Id : ' || l_cal_period_id);
2390 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2391 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2392 'Request Id : ' || l_request_id);
2393 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2394 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2395 'Base Request Id : ' || l_base_request_id);
2396 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2397 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2398 'Load Method Code : ' || l_load_method_code);
2399 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2400 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2401 'BSV low : ' || l_bsv_low);
2402 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2403 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2404 'BSV high : ' || l_bsv_high);
2405 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2406 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2407 'Status Code : ' || l_status_code);
2408 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2409 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2410 '<< Parameters on event : End >>');
2411 END IF;
2412
2413 -- If the data submission is done via FCH Data Submission UI then, no need to process further.
2414 BEGIN
2415
2416 SELECT 'Y'
2417 INTO l_data_sub_exists_via_ui
2418 FROM gcs_data_sub_dtls
2419 WHERE associated_request_id = l_base_request_id
2420 AND cal_period_id = l_cal_period_id
2421 AND ROWNUM < 2;
2422
2423 IF l_data_sub_exists_via_ui = 'Y' THEN
2424
2425 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2426 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2427 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2428 '<< Data Load Submitted via UI >>');
2429 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2430 g_api || '.POPULATE_OGL_DATASUB_DTLS.end',
2431 '<< Exit >>');
2432 END IF;
2433
2434 RETURN 'SUCCESS';
2435 END IF;
2436
2437 EXCEPTION
2438 WHEN NO_DATA_FOUND THEN
2439
2440 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2441 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2442 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2443 '<< Data Load Submitted via Balances Rule Engine >>');
2444 END IF;
2445
2446 NULL;
2447 END;
2448
2449 --Since we are interested in only "ACTUAL" loads, we dont need to process further for other balance_types
2450 SELECT fodb.object_id balances_rule_id,
2451 fibr.ledger_id,
2452 fibr.ds_bal_type_code,
2453 gsob.currency_code,
2454 fibr.include_avg_bal_flag,
2455 fcpa.date_assign_value
2456 INTO l_bal_rule_id,
2457 l_ledger_id,
2458 l_ds_bal_type_code,
2459 l_curr_code,
2460 l_avg_bal_flag,
2461 l_cal_period_end_date
2462 FROM fem_intg_bal_rule_defs fibrd,
2463 fem_intg_bal_rules fibr,
2464 fem_object_definition_b fodb,
2465 gl_sets_of_books gsob,
2466 fem_cal_periods_attr fcpa
2467 WHERE gsob.set_of_books_id = fibr.ledger_id
2468 AND fibrd.bal_rule_obj_def_id = fodb.object_definition_id
2469 AND fibr.bal_rule_obj_id = fodb.object_id
2470 AND fibrd.bal_rule_obj_def_id = l_bal_rule_version_id
2471 AND fcpa.cal_period_id = l_cal_period_id
2472 AND fcpa.attribute_id = l_period_end_date_attr
2473 AND fcpa.version_id = l_period_end_date_version
2474 AND fcpa.date_assign_value BETWEEN fodb.effective_start_date AND
2475 fodb.effective_end_date;
2476
2477 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2478 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2479 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2480 'Balances Rule Id : ' || l_bal_rule_id);
2481 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2482 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2483 'Ledger Id : ' || l_ledger_id);
2484 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2485 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2486 'Balance Type Code : ' || l_ds_bal_type_code);
2487 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2488 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2489 'Currency Code : ' || l_curr_code);
2490 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2491 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2492 'Cal Period End Date : ' || l_cal_period_end_date);
2493 END IF;
2494
2495 IF l_ds_bal_type_code <> 'ACTUAL' THEN
2496
2497 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2498 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2499 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2500 '<< Data Load Submitted for other than actuals >>');
2501 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2502 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2503 '<< Exit >>');
2504 END IF;
2505
2506 RETURN 'SUCCESS';
2507
2508 END IF;
2509
2510 IF l_load_method_code = 'I' THEN
2511 l_load_method := 'INCREMENTAL';
2512 ELSE
2513 l_load_method := 'SNAPSHOT';
2514 END IF;
2515
2516 IF l_status_code = 'NORMAL' THEN
2517 l_status := 'COMPLETED';
2518 ELSIF l_status_code = 'WARNING' THEN
2519 l_status := 'WARNING';
2520 ELSE
2521 l_status := 'ERROR';
2522 END IF;
2523
2524 SELECT requested_start_date
2525 INTO l_start_date
2526 FROM fnd_concurrent_requests
2527 WHERE request_id = l_base_request_id;
2528
2529 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2530 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2531 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2532 'Requested Status Date : ' || l_start_date);
2533 END IF;
2534
2535 IF (l_load_method_code = 'S' OR
2536 (l_load_method_code = 'I' AND l_bsv_low IS NULL AND
2537 l_bsv_high IS NULL)) THEN
2538
2539 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2540 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2541 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2542 ' Snapshot or Incremtal with no bsv range : << Enter >>');
2543 END IF;
2544
2545 -- Bugfix: 5843592, select entity ids
2546
2547 SELECT gea.entity_id BULK COLLECT
2548 INTO l_entity_list
2549 FROM gcs_entities_attr gea,
2550 fem_entities_b feb,
2551 fem_cal_periods_attr fcpa
2552 WHERE gea.balances_rule_id = l_bal_rule_id
2553 AND gea.source_system_code = 10
2554 AND gea.data_type_code = 'ACTUAL'
2555 AND gea.entity_id = feb.entity_id
2556 AND feb.enabled_flag = 'Y'
2557 AND fcpa.cal_period_id = l_cal_period_id
2558 AND fcpa.attribute_id = l_period_end_date_attr
2559 AND fcpa.version_id = l_period_end_date_version
2560 AND fcpa.date_assign_value BETWEEN gea.effective_start_date
2561 AND NVL(gea.effective_end_date, fcpa.date_assign_value);
2562
2563 IF l_entity_list.FIRST IS NOT NULL AND l_entity_list.LAST IS NOT NULL THEN
2564
2565 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2566 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2567 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2568 ' Updating gcs_data_sub_dtls for ACTUAL');
2569 END IF;
2570
2571 FORALL i IN l_entity_list.FIRST .. l_entity_list.LAST
2572 UPDATE gcs_data_sub_dtls
2573 SET most_recent_flag = 'N'
2574 WHERE most_recent_flag = 'Y'
2575 AND cal_period_id = l_cal_period_id
2576 AND balance_type_code = 'ACTUAL'
2577 AND entity_id = l_entity_list(i);
2578
2579 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2580 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2581 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2582 ' Inserting into gcs_data_sub_dtls for ACTUAL');
2583 END IF;
2584
2585 FORALL i IN l_entity_list.FIRST .. l_entity_list.LAST
2586 INSERT INTO gcs_data_sub_dtls
2587 (load_id,
2588 load_name,
2589 entity_id,
2590 cal_period_id,
2591 currency_code,
2592 balance_type_code,
2593 load_method_code,
2594 currency_type_code,
2595 amount_type_code,
2596 measure_type_code,
2597 notify_options_code,
2598 notification_text,
2599 creation_date,
2600 created_by,
2601 last_update_date,
2602 last_updated_by,
2603 last_update_login,
2604 object_version_number,
2605 start_time,
2606 end_time,
2607 locked_flag,
2608 most_recent_flag,
2609 associated_request_id,
2610 status_code,
2611 balances_rule_id)
2612 VALUES
2613 (gcs_data_sub_dtls_s.nextval,
2614 gcs_data_sub_dtls_s.nextval,
2615 l_entity_list(i),
2616 l_cal_period_id,
2617 l_curr_code,
2618 'ACTUAL',
2619 l_load_method,
2620 'BASE_CURRENCY',
2621 'ENDING_BALANCE',
2622 'BALANCE',
2623 'N',
2624 NULL,
2625 sysdate,
2626 l_user_id,
2627 sysdate,
2628 l_user_id,
2629 l_login_id,
2630 1,
2631 l_start_date,
2632 sysdate,
2633 'N',
2634 'Y',
2635 l_base_request_id,
2636 l_status,
2637 l_bal_rule_id);
2638 ELSE
2639
2640 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2641 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2642 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2643 '<< No Entities found for update/insert >>');
2644 END IF;
2645
2646 END IF;
2647
2648 IF l_avg_bal_flag = 'Y' THEN
2649 IF l_entity_list.FIRST IS NOT NULL AND
2650 l_entity_list.LAST IS NOT NULL THEN
2651
2652 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2653 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2654 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2655 ' Updating gcs_data_sub_dtls for ADB');
2656 END IF;
2657
2658 FORALL i IN l_entity_list.FIRST .. l_entity_list.LAST
2659 UPDATE gcs_data_sub_dtls
2660 SET most_recent_flag = 'N'
2661 WHERE most_recent_flag = 'Y'
2662 AND cal_period_id = l_cal_period_id
2663 AND balance_type_code = 'ADB'
2664 AND entity_id = l_entity_list(i);
2665
2666 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2667 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2668 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2669 ' Inserting into gcs_data_sub_dtls for ADB');
2670 END IF;
2671
2672 FORALL i IN l_entity_list.FIRST .. l_entity_list.LAST
2673 INSERT INTO gcs_data_sub_dtls
2674 (load_id,
2675 load_name,
2676 entity_id,
2677 cal_period_id,
2678 currency_code,
2679 balance_type_code,
2680 load_method_code,
2681 currency_type_code,
2682 amount_type_code,
2683 measure_type_code,
2684 notify_options_code,
2685 notification_text,
2686 creation_date,
2687 created_by,
2688 last_update_date,
2689 last_updated_by,
2690 last_update_login,
2691 object_version_number,
2692 start_time,
2693 end_time,
2694 locked_flag,
2695 most_recent_flag,
2696 associated_request_id,
2697 status_code,
2698 balances_rule_id)
2699 VALUES
2700 (gcs_data_sub_dtls_s.nextval,
2701 gcs_data_sub_dtls_s.nextval,
2702 l_entity_list(i),
2703 l_cal_period_id,
2704 l_curr_code,
2705 'ADB',
2706 l_load_method,
2707 'BASE_CURRENCY',
2708 'ENDING_BALANCE',
2709 'BALANCE',
2710 'N',
2711 NULL,
2712 sysdate,
2713 l_user_id,
2714 sysdate,
2715 l_user_id,
2716 l_login_id,
2717 1,
2718 l_start_date,
2719 sysdate,
2720 'N',
2721 'Y',
2722 l_base_request_id,
2723 l_status,
2724 l_bal_rule_id);
2725 ELSE
2726
2727 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2728 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2729 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2730 '<< No Entities found for update/insert >>');
2731 END IF;
2732
2733 END IF;
2734 END IF;
2735
2736 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2737 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2738 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2739 ' Snapshot or Incremtal with no bsv range : << Exit >>');
2740 END IF;
2741
2742 ELSE
2743
2744 --Incremental case code goes here
2745 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2746 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2747 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2748 ' Incremtal with bsv range : << Enter >>');
2749 END IF;
2750
2751 --Check if chart of accounts mapping is required
2752 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2753 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2754 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2755 ' Check if chart of accounts mapping is required');
2756 END IF;
2757
2758 BEGIN
2759 SELECT fla.dim_attribute_numeric_member,
2760 fgvcd_local_company.value_set_id,
2761 fgvcd_local_org.value_set_id
2762 INTO l_global_vs_combo_id,
2763 l_company_vs_id,
2764 l_org_vs_id
2765 FROM fem_ledgers_attr fla,
2766 fem_global_vs_combo_defs fgvcd_local_company,
2767 fem_global_vs_combo_defs fgvcd_local_org
2768 WHERE fla.ledger_id = l_ledger_id
2769 AND fla.attribute_id = l_global_vs_combo_attr
2770 AND fla.version_id = l_global_vs_combo_version
2771 AND fla.dim_attribute_numeric_member =
2772 fgvcd_local_company.global_vs_combo_id
2773 AND fgvcd_local_company.dimension_id = 112
2774 AND fla.dim_attribute_numeric_member =
2775 fgvcd_local_org.global_vs_combo_id
2776 AND fgvcd_local_org.dimension_id = 8;
2777
2778 SELECT fgvcd_fch_company.value_set_id,
2779 fgvcd_fch_org.value_set_id
2780 INTO l_fch_company_vs_id,
2781 l_fch_org_vs_id
2782 FROM fem_global_vs_combo_defs fgvcd_fch_company,
2783 fem_global_vs_combo_defs fgvcd_fch_org,
2784 gcs_system_options gso
2785 WHERE fgvcd_fch_company.global_vs_combo_id =
2786 gso.fch_global_vs_combo_id
2787 AND fgvcd_fch_org.global_vs_combo_id =
2788 fgvcd_fch_company.global_vs_combo_id
2789 AND fgvcd_fch_org.dimension_id = 8
2790 AND fgvcd_fch_company.dimension_id = 112;
2791
2792 EXCEPTION
2793 WHEN NO_DATA_FOUND THEN
2794 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2795 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2796 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2797 '<< No Data Found while finding out Master and Local value sets >>');
2798 END IF;
2799 NULL;
2800 END;
2801
2802 IF (l_fch_org_vs_id <> l_org_vs_id) THEN
2803
2804 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2805 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2806 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2807 ' Chart of Accounts mapping is reuqired');
2808 END IF;
2809
2810 BEGIN
2811 SELECT fodb.object_definition_id
2812 INTO l_hier_obj_definition_id
2813 FROM fem_xdim_dimensions fxd,
2814 fem_object_definition_b fodb
2815 WHERE fxd.dimension_id = 8
2816 AND fxd.default_mvs_hierarchy_obj_id = fodb.object_id
2817 AND l_cal_period_end_date BETWEEN fodb.effective_start_date AND
2818 fodb.effective_end_date;
2819 EXCEPTION
2820 WHEN NO_DATA_FOUND THEN
2821 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2822 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2823 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2824 '<< No Data Found while finding out hierarchy object def Id In case of COA map required >>');
2825 END IF;
2826 NULL;
2827 END;
2828
2829 -- Bugfix 5843592
2830
2831 SELECT DISTINCT geco.entity_id BULK COLLECT
2832 INTO l_entity_list
2833 FROM fem_companies_b f,
2834 fem_cctr_orgs_hier fcoh,
2835 fem_cctr_orgs_attr fcoa,
2836 gcs_entity_cctr_orgs geco,
2837 gcs_entities_attr gea,
2838 fem_entities_b feb,
2839 fem_cal_periods_attr fcpa
2840 WHERE feb.entity_id = gea.entity_id
2841 AND geco.entity_id = gea.entity_id
2842 AND geco.company_cost_center_org_id = fcoa.company_cost_center_org_id
2843 AND fcoh.hierarchy_obj_def_id = l_hier_obj_definition_id
2844 AND fcoh.parent_value_set_id = l_fch_company_vs_id
2845 AND fcoh.child_value_set_id = l_company_vs_id
2846 AND fcoh.child_id = fcoa.company_cost_center_org_id
2847 AND fcoh.child_value_set_id = fcoa.value_set_id
2848 AND fcoa.attribute_id = l_company_attr
2849 AND fcoa.version_id = l_company_version
2850 AND fcoa.dim_attribute_numeric_member = f.company_id
2851 AND fcoa.value_set_id = f.value_set_id
2852 AND gea.balances_rule_id = l_bal_rule_id
2853 AND gea.source_system_code = 10
2854 AND gea.data_type_code = 'ACTUAL'
2855 AND fcpa.cal_period_id = l_cal_period_id
2856 AND fcpa.attribute_id = l_period_end_date_attr
2857 AND fcpa.version_id = l_period_end_date_version
2858 AND fcpa.date_assign_value BETWEEN gea.effective_start_date
2859 AND NVL(gea.effective_end_date, fcpa.date_assign_value)
2860 AND feb.enabled_flag = 'Y'
2861 AND f.company_display_code BETWEEN l_bsv_low AND l_bsv_high;
2862
2863 ELSE
2864
2865 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2866 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2867 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2868 ' Chart of Accounts mapping is not reuqired');
2869 END IF;
2870
2871 SELECT DISTINCT geo.entity_id BULK COLLECT
2872 INTO l_entity_list
2873 FROM fem_companies_b fcb,
2874 gcs_entity_organizations geo,
2875 gcs_entities_attr gea,
2876 fem_entities_b feb
2877 WHERE feb.entity_id = gea.entity_id
2878 AND geo.entity_id = gea.entity_id
2879 AND feb.enabled_flag = 'Y'
2880 AND gea.balances_rule_id = l_bal_rule_id
2881 AND gea.source_system_code = 10
2882 AND gea.data_type_code = 'ACTUAL'
2883 AND geo.company_cost_center_org_id = fcb.company_id
2884 AND fcb.value_set_id = l_company_vs_id
2885 AND fcb.company_display_code BETWEEN l_bsv_low AND l_bsv_high;
2886
2887 END IF;
2888
2889 IF l_entity_list.FIRST IS NOT NULL AND l_entity_list.LAST IS NOT NULL THEN
2890
2891 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2892 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2893 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2894 ' Updating gcs_data_sub_dtls for ACTUAL');
2895 END IF;
2896
2897 FORALL i IN l_entity_list.FIRST .. l_entity_list.LAST
2898 UPDATE gcs_data_sub_dtls
2899 SET most_recent_flag = 'N'
2900 WHERE most_recent_flag = 'Y'
2901 AND cal_period_id = l_cal_period_id
2902 AND balance_type_code = 'ACTUAL'
2903 AND entity_id = l_entity_list(i);
2904
2905 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2906 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2907 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2908 ' Inserting gcs_data_sub_dtls for ACTUAL');
2909 END IF;
2910
2911 FORALL i IN l_entity_list.FIRST .. l_entity_list.LAST
2912 INSERT INTO gcs_data_sub_dtls
2913 (load_id,
2914 load_name,
2915 entity_id,
2916 cal_period_id,
2917 currency_code,
2918 balance_type_code,
2919 load_method_code,
2920 currency_type_code,
2921 amount_type_code,
2922 measure_type_code,
2923 notify_options_code,
2924 notification_text,
2925 creation_date,
2926 created_by,
2927 last_update_date,
2928 last_updated_by,
2929 last_update_login,
2930 object_version_number,
2931 start_time,
2932 end_time,
2933 locked_flag,
2934 most_recent_flag,
2935 associated_request_id,
2936 status_code,
2937 balances_rule_id)
2938 VALUES
2939 (gcs_data_sub_dtls_s.nextval,
2940 gcs_data_sub_dtls_s.nextval,
2941 l_entity_list(i),
2942 l_cal_period_id,
2943 l_curr_code,
2944 'ACTUAL',
2945 l_load_method,
2946 'BASE_CURRENCY',
2947 'ENDING_BALANCE',
2948 'BALANCE',
2949 'N',
2950 NULL,
2951 sysdate,
2952 l_user_id,
2953 sysdate,
2954 l_user_id,
2955 l_login_id,
2956 1,
2957 l_start_date,
2958 sysdate,
2959 'N',
2960 'Y',
2961 l_base_request_id,
2962 l_status,
2963 l_bal_rule_id);
2964 ELSE
2965
2966 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2967 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2968 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2969 '<< No entities found for update/insert >>');
2970 END IF;
2971
2972 END IF;
2973
2974 IF l_avg_bal_flag = 'Y' THEN
2975 IF l_entity_list.FIRST IS NOT NULL AND
2976 l_entity_list.LAST IS NOT NULL THEN
2977
2978 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2979 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2980 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2981 ' Updating gcs_data_sub_dtls for ADB');
2982 END IF;
2983
2984 FORALL i IN l_entity_list.FIRST .. l_entity_list.LAST
2985 UPDATE gcs_data_sub_dtls
2986 SET most_recent_flag = 'N'
2987 WHERE most_recent_flag = 'Y'
2988 AND cal_period_id = l_cal_period_id
2989 AND balance_type_code = 'ADB'
2990 AND entity_id = l_entity_list(i);
2991
2992 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2993 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2994 g_api || '.POPULATE_OGL_DATASUB_DTLS',
2995 ' Inserting into gcs_data_sub_dtls for ADB');
2996 END IF;
2997
2998 FORALL i IN l_entity_list.FIRST .. l_entity_list.LAST
2999 INSERT INTO gcs_data_sub_dtls
3000 (load_id,
3001 load_name,
3002 entity_id,
3003 cal_period_id,
3004 currency_code,
3005 balance_type_code,
3006 load_method_code,
3007 currency_type_code,
3008 amount_type_code,
3009 measure_type_code,
3010 notify_options_code,
3011 notification_text,
3012 creation_date,
3013 created_by,
3014 last_update_date,
3015 last_updated_by,
3016 last_update_login,
3017 object_version_number,
3018 start_time,
3019 end_time,
3020 locked_flag,
3021 most_recent_flag,
3022 associated_request_id,
3023 status_code,
3024 balances_rule_id)
3025 VALUES
3026 (gcs_data_sub_dtls_s.nextval,
3027 gcs_data_sub_dtls_s.nextval,
3028 l_entity_list(i),
3029 l_cal_period_id,
3030 l_curr_code,
3031 'ADB',
3032 l_load_method,
3033 'BASE_CURRENCY',
3034 'ENDING_BALANCE',
3035 'BALANCE',
3036 'N',
3037 NULL,
3038 sysdate,
3039 l_user_id,
3040 sysdate,
3041 l_user_id,
3042 l_login_id,
3043 1,
3044 l_start_date,
3045 sysdate,
3046 'N',
3047 'Y',
3048 l_base_request_id,
3049 l_status,
3050 l_bal_rule_id);
3051 ELSE
3052
3053 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3054 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3055 g_api || '.POPULATE_OGL_DATASUB_DTLS',
3056 '<< No entities found for update/insert >>');
3057 END IF;
3058
3059 END IF;
3060
3061 END IF;
3062
3063 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3064 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3065 g_api || '.POPULATE_OGL_DATASUB_DTLS',
3066 ' Incremtal with bsv range : << Exit >>');
3067 END IF;
3068
3069 END IF;
3070
3071 COMMIT;
3072
3073 --Handle Translated balances
3074 SELECT DISTINCT translated_currency BULK COLLECT
3075 INTO l_xlated_curr_list
3076 FROM fem_dl_trans_curr
3077 WHERE request_id >= l_base_request_id
3078 AND object_id = l_bal_rule_id
3079 AND ledger_id = l_ledger_id
3080 AND cal_period_id = l_cal_period_id;
3081
3082 IF l_xlated_curr_list.FIRST IS NOT NULL AND
3083 l_xlated_curr_list.LAST IS NOT NULL THEN
3084
3085 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3086 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3087 g_api || '.POPULATE_OGL_DATASUB_DTLS',
3088 '<< Processing Translated Currencies >>');
3089 END IF;
3090
3091 FORALL i IN l_xlated_curr_list.FIRST .. l_xlated_curr_list.LAST
3092 INSERT INTO gcs_data_sub_dtls
3093 (load_id,
3094 load_name,
3095 entity_id,
3096 cal_period_id,
3097 currency_code,
3098 balance_type_code,
3099 load_method_code,
3100 currency_type_code,
3101 amount_type_code,
3102 measure_type_code,
3103 notify_options_code,
3104 notification_text,
3105 creation_date,
3106 created_by,
3107 last_update_date,
3108 last_updated_by,
3109 last_update_login,
3110 object_version_number,
3111 start_time,
3112 end_time,
3113 locked_flag,
3114 most_recent_flag,
3115 associated_request_id,
3116 status_code,
3117 balances_rule_id)
3118 SELECT gcs_data_sub_dtls_s.nextval,
3119 gcs_data_sub_dtls_s.nextval,
3120 gdsd.entity_id,
3121 gdsd.cal_period_id,
3122 l_xlated_curr_list(i),
3123 gdsd.balance_type_code,
3124 gdsd.load_method_code,
3125 gdsd.currency_type_code,
3126 gdsd.amount_type_code,
3127 gdsd.measure_type_code,
3128 gdsd.notify_options_code,
3129 gdsd.notification_text,
3130 sysdate,
3131 l_user_id,
3132 sysdate,
3133 l_user_id,
3134 l_login_id,
3135 1,
3136 gdsd.start_time,
3137 sysdate,
3138 gdsd.locked_flag,
3139 gdsd.most_recent_flag,
3140 gdsd.associated_request_id,
3141 gdsd.status_code,
3142 gdsd.balances_rule_id
3143 FROM gcs_data_sub_dtls gdsd
3144 WHERE gdsd.associated_request_id = l_base_request_id
3145 AND gdsd.cal_period_id = l_cal_period_id;
3146 ELSE
3147
3148 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3149 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3150 g_api || '.POPULATE_OGL_DATASUB_DTLS',
3151 '<< No Translated Currencies found >>');
3152 END IF;
3153
3154 END IF;
3155
3156 COMMIT;
3157
3158 SELECT gdsd.load_id BULK COLLECT
3159 INTO l_generated_load_list
3160 FROM gcs_data_sub_dtls gdsd
3161 WHERE gdsd.associated_request_id = l_base_request_id
3162 AND gdsd.cal_period_id = l_cal_period_id;
3163
3164 IF l_generated_load_list.FIRST IS NOT NULL AND
3165 l_generated_load_list.LAST IS NOT NULL THEN
3166
3167 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3168 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3169 g_api || '.POPULATE_OGL_DATASUB_DTLS',
3170 '<< Raising IA Event, Updating the status, XML WRiter program >>');
3171 END IF;
3172
3173 FOR k IN l_generated_load_list.FIRST .. l_generated_load_list.LAST LOOP
3174
3175 -- Raising the impact analysis and updating the data status should only be done if the request completed successfully
3176
3177 IF (l_status = 'COMPLETED') THEN
3178 raise_impact_analysis_event(p_load_id => l_generated_load_list(k),
3179 p_ledger_id => l_ledger_id);
3180
3181 -- Bugfix 5676634: Submit request for data status update instead of API call
3182
3183 --gcs_cons_monitor_pkg.update_data_status(p_load_id => l_generated_load_list(k),
3184 -- p_cons_rel_id => null,
3185 -- p_hierarchy_id => null,
3186 -- p_transaction_type => null);
3187 l_request_id := fnd_request.submit_request(application => 'GCS',
3188 program => 'FCH_UPDATE_DATA_STATUS',
3189 sub_request => FALSE,
3190 argument1 => l_generated_load_list(k),
3191 argument2 => NULL,
3192 argument3 => NULL,
3193 argument4 => NULL);
3194
3195 l_request_id := fnd_request.submit_request(application => 'GCS',
3196 program => 'FCH_XML_WRITER',
3197 sub_request => FALSE,
3198 argument1 => 'DATASUBMISSION',
3199 argument2 => NULL,
3200 argument3 => NULL,
3201 argument4 => l_generated_load_list(k));
3202 END IF;
3203
3204 END LOOP;
3205
3206 ELSE
3207
3208 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3209 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3210 g_api || '.POPULATE_OGL_DATASUB_DTLS',
3211 '<< No generated loads found >>');
3212 END IF;
3213
3214 END IF;
3215
3216 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3217 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3218 g_api || '.POPULATE_OGL_DATASUB_DTLS.end',
3219 '<< Exit >>');
3220 END IF;
3221 RETURN 'SUCCESS';
3222
3223 EXCEPTION
3224 WHEN NO_DATA_FOUND THEN
3225
3226 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3227 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3228 g_api || '.POPULATE_OGL_DATASUB_DTLS',
3229 '<< No Data Found >>');
3230 END IF;
3231
3232 RETURN 'ERROR';
3233
3234 WHEN OTHERS THEN
3235 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3236 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
3237 g_api || '.POPULATE_OGL_DATASUB_DTLS',
3238 SQLERRM);
3239 END IF;
3240
3241 RETURN 'ERROR';
3242 END populate_ogl_datasub_dtls;
3243
3244 --
3245 -- function
3246 -- handle_undo_event
3247 -- Purpose
3248 -- An API to handle the UNDO Event submitted via EPF.
3249 -- This API has subscription with the business event "oracle.apps.fem.ud.complete"
3250 -- Arguments
3251 -- p_subscription_guid - This subscription GUID is passed when the event is raised
3252 -- p_event - wf_event_t param
3253 -- Notes
3254 -- Bug Fix : 5647099
3255 FUNCTION handle_undo_event(p_subscription_guid IN RAW,
3256 p_event IN OUT NOCOPY wf_event_t)
3257 RETURN VARCHAR2 IS
3258 l_parameter_list wf_parameter_list_t;
3259 l_undo_request_id NUMBER;
3260 l_dataset_code NUMBER;
3261 l_cal_period_id NUMBER;
3262 l_ledger_id NUMBER;
3263 l_srcsys_code NUMBER;
3264 l_status_code VARCHAR2(30);
3265 l_data_type_code VARCHAR2(30);
3266 l_actual_ds_code VARCHAR2(30);
3267 -- Bugfix 5676634
3268 l_request_id NUMBER;
3269
3270 -- Bugfix 5664023 :Start
3271 --l_load_list DBMS_SQL.NUMBER_TABLE;
3272
3273 TYPE load_rec_type IS RECORD(load_id NUMBER,
3274 most_recent_flag VARCHAR2(1));
3275 TYPE t_load_info IS TABLE OF load_rec_type;
3276 l_load_list t_load_info;
3277 -- Bugfix 5664023 :End
3278
3279 -- Bug Fix: 5843592, Get the attribute id and version id of the CAL_PERIOD_END_DATE of calendar period
3280
3281 l_period_end_date_attr NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
3282 .attribute_id;
3283 l_period_end_date_version NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
3284 .version_id;
3285
3286
3287 BEGIN
3288
3289 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3290 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3291 g_api || '.HANDLE_UNDO_EVENT.begin',
3292 '<< Enter >>');
3293 END IF;
3294
3295 l_parameter_list := p_event.getParameterList();
3296 l_undo_request_id := TO_NUMBER(WF_EVENT.getValueForParameter('UNDO_REQUEST_ID',
3297 l_parameter_list));
3298 l_dataset_code := TO_NUMBER(WF_EVENT.getValueForParameter('DATASET_CODE',
3299 l_parameter_list));
3300 l_cal_period_id := TO_NUMBER(WF_EVENT.getValueForParameter('CAL_PERIOD_ID',
3301 l_parameter_list));
3302 l_ledger_id := TO_NUMBER(WF_EVENT.getValueForParameter('LEDGER_ID',
3303 l_parameter_list));
3304 l_status_code := WF_EVENT.getValueForParameter('STATUS',
3305 l_parameter_list);
3306
3307 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3308 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3309 g_api || '.HANDLE_UNDO_EVENT',
3310 '<< Parameters on event : Start >>');
3311 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3312 g_api || '.HANDLE_UNDO_EVENT',
3313 'Undo Request Id : ' || l_undo_request_id);
3314 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3315 g_api || '.HANDLE_UNDO_EVENT',
3316 'Dataset Code : ' || l_dataset_code);
3317 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3318 g_api || '.HANDLE_UNDO_EVENT',
3319 'Cal Period Id : ' || l_cal_period_id);
3320 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3321 g_api || '.HANDLE_UNDO_EVENT',
3322 'Ledger Id : ' || l_ledger_id);
3323 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3324 g_api || '.HANDLE_UNDO_EVENT',
3325 'Source System Code : ' || l_srcsys_code);
3326 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3327 g_api || '.HANDLE_UNDO_EVENT',
3328 'Status Code : ' || l_status_code);
3329 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3330 g_api || '.HANDLE_UNDO_EVENT',
3331 '<< Parameters on event : End >>');
3332 END IF;
3333
3334 IF (l_status_code = 'S') THEN
3335
3336 -- first check whether there exists rows in gcs_data_type_codes_b with given dataset
3337 -- because if it is not then we do not care.
3338
3339 BEGIN
3340 SELECT data_type_code
3341 INTO l_data_type_code
3342 FROM gcs_data_type_codes_b
3343 WHERE source_dataset_code = l_dataset_code
3344 AND rownum < 2;
3345 EXCEPTION
3346 WHEN NO_DATA_FOUND THEN
3347 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3348 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3349 g_api || '.HANDLE_UNDO_EVENT',
3350 '<< No Data Type exists with the provided data set code >>');
3351 END IF;
3352 RETURN 'SUCCESS';
3353 END;
3354
3355 -- Check if the datasetcode is same as the source_dataset_code of ACTUAL/ADB
3356 -- then we need to UNDONE both the ACTUAL/ADB loads for the given ledger/calPeriod
3357 -- else UNDO only the loads with ledger/calPeriod/dataTypeCode
3358
3359 BEGIN
3360 SELECT source_dataset_code
3361 INTO l_actual_ds_code
3362 FROM gcs_data_type_codes_b
3363 WHERE data_type_code = 'ACTUAL';
3364
3365 -- Bugfix 5843592, Get the correct entity, depending upon the calendar period
3366
3367 IF (l_actual_ds_code = l_dataset_code) THEN
3368
3369 SELECT gdsd.load_id,
3370 gdsd.most_recent_flag
3371 BULK COLLECT INTO l_load_list
3372 FROM gcs_entities_attr gea,
3373 gcs_data_sub_dtls gdsd,
3374 fem_cal_periods_attr fcpa
3375 WHERE gea.ledger_id = l_ledger_id
3376 AND gdsd.cal_period_id = l_cal_period_id
3377 AND gdsd.balance_type_code IN ('ACTUAL', 'ADB')
3378 AND gdsd.balance_type_code = gea.data_type_code
3379 AND gdsd.entity_id = gea.entity_id
3380 AND fcpa.cal_period_id = gdsd.cal_period_id
3381 AND fcpa.attribute_id = l_period_end_date_attr
3382 AND fcpa.version_id = l_period_end_date_version
3383 AND fcpa.date_assign_value BETWEEN gea.effective_start_date
3384 AND NVL(gea.effective_end_date, fcpa.date_assign_value ) ;
3385
3386 ELSE
3387
3388 SELECT gdsd.load_id,
3389 gdsd.most_recent_flag
3390 BULK COLLECT INTO l_load_list
3391 FROM gcs_entities_attr gea,
3392 gcs_data_sub_dtls gdsd,
3393 fem_cal_periods_attr fcpa
3394 WHERE gea.ledger_id = l_ledger_id
3395 AND gdsd.cal_period_id = l_cal_period_id
3396 AND gdsd.balance_type_code = l_data_type_code
3397 AND gdsd.balance_type_code = gea.data_type_code
3398 AND gdsd.entity_id = gea.entity_id
3399 AND fcpa.cal_period_id = gdsd.cal_period_id
3400 AND fcpa.attribute_id = l_period_end_date_attr
3401 AND fcpa.version_id = l_period_end_date_version
3402 AND fcpa.date_assign_value BETWEEN gea.effective_start_date
3403 AND NVL(gea.effective_end_date, fcpa.date_assign_value ) ;
3404
3405
3406 END IF;
3407
3408 EXCEPTION
3409 WHEN NO_DATA_FOUND THEN
3410 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3411 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3412 g_api || '.HANDLE_UNDO_EVENT',
3413 '<< Source_dataset_code for ACTUAL is NULL in gcs_data_type_codes_b >>');
3414 END IF;
3415 RETURN 'SUCCESS';
3416 END;
3417
3418
3419 IF l_load_list.FIRST IS NOT NULL AND l_load_list.LAST IS NOT NULL THEN
3420
3421 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3422 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3423 g_api || '.HANDLE_UNDO_EVENT',
3424 '<< Update gcs_data_sub_dtls status and >>');
3425 END IF;
3426 -- Bugfix 5664023 :Start
3427 FOR k IN l_load_list.FIRST .. l_load_list.LAST LOOP
3428
3429 UPDATE gcs_data_sub_dtls
3430 SET status_code = 'UNDONE'
3431 WHERE load_id = l_load_list(k).load_id;
3432
3433 IF (l_load_list(k).most_recent_flag = 'Y') THEN
3434 -- Raising the impact analysis and updating the data status should be done.
3435 raise_impact_analysis_event(p_load_id => l_load_list(k).load_id,
3436 p_ledger_id => l_ledger_id);
3437
3438 -- Bugfix 5676634: Submit request for data status update instead of API call
3439 -- issuing a commit prior to request submission to ensure information is going
3440 --to be available to the concurrent program which will run in different context/session
3441
3442 --gcs_cons_monitor_pkg.update_data_status(p_load_id => l_load_list(k).load_id,
3443 -- p_cons_rel_id => null,
3444 -- p_hierarchy_id => null,
3445 -- p_transaction_type => null);
3446 COMMIT;
3447 l_request_id := fnd_request.submit_request(application => 'GCS',
3448 program => 'FCH_UPDATE_DATA_STATUS',
3449 sub_request => FALSE,
3450 argument1 => l_load_list(k).load_id,
3451 argument2 => NULL,
3452 argument3 => NULL,
3453 argument4 => NULL);
3454 END IF;
3455 END LOOP;
3456 -- Bugfix 5664023 :End
3457
3458 ELSE
3459
3460 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3461 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3462 g_api || '.HANDLE_UNDO_EVENT',
3463 '<< No loads found >>');
3464 END IF;
3465
3466 END IF;
3467
3468 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3469 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3470 g_api || '.HANDLE_UNDO_EVENT.end',
3471 '<< Exit >>');
3472 END IF;
3473 RETURN 'SUCCESS';
3474
3475 ELSE
3476
3477 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3478 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3479 g_api || '.HANDLE_UNDO_EVENT',
3480 '<< UNDO event is not successful >>');
3481 END IF;
3482 RETURN 'SUCCESS';
3483
3484 END IF;
3485
3486 EXCEPTION
3487 WHEN NO_DATA_FOUND THEN
3488
3489 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3490 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3491 g_api || '.HANDLE_UNDO_EVENT',
3492 '<< No Data Found >>');
3493 END IF;
3494
3495 RETURN 'ERROR';
3496
3497 WHEN OTHERS THEN
3498 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3499 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
3500 g_api || '.HANDLE_UNDO_EVENT',
3501 SQLERRM);
3502 END IF;
3503
3504 RETURN 'ERROR';
3505
3506 END handle_undo_event;
3507 END gcs_datasub_wf_pkg;