[Home] [Help]
PACKAGE BODY: APPS.GCS_TRANSLATION_PKG
Source
1 PACKAGE BODY GCS_TRANSLATION_PKG as
2 /* $Header: gcsxlatb.pls 120.7 2007/06/28 12:27:36 vkosuri noship $ */
3
4 --
5 -- PRIVATE TYPES
6 --
7 TYPE RefCursor IS REF CURSOR;
8
9 --
10 -- PRIVATE GLOBAL VARIABLES
11 --
12 -- The API name
13 g_api CONSTANT VARCHAR2(40) := 'gcs.plsql.GCS_TRANSLATION_PKG';
14
15 -- The dimension information table
16 g_dims gcs_utility_pkg.t_hash_gcs_dimension_info;
17
18 -- Action types for writing module information to the log file. Used for
19 -- the procedure log_file_module_write.
20 g_module_enter CONSTANT VARCHAR2(2) := '>>';
21 g_module_success CONSTANT VARCHAR2(2) := '<<';
22 g_module_failure CONSTANT VARCHAR2(2) := '<x';
23
24 -- A newline character. Included for convenience when writing long strings.
25 g_nl CONSTANT VARCHAR2(1) := '
26 ';
27
28 --
29 -- PRIVATE EXCEPTIONS
30 --
31
32 GCS_CCY_INVALID_ENTRY EXCEPTION;
33 GCS_CCY_INVALID_REL EXCEPTION;
34 GCS_CCY_INVALID_CCY_TREAT EXCEPTION;
35 GCS_CCY_INVALID_CHILD EXCEPTION;
36 GCS_CCY_INVALID_PARENT EXCEPTION;
37 GCS_CCY_INVALID_HIERARCHY EXCEPTION;
38 GCS_CCY_INVALID_PERIOD EXCEPTION;
39 GCS_CCY_INVALID_TARGET_CCY EXCEPTION;
40 GCS_CCY_INVALID_LEDGER EXCEPTION;
41 GCS_CCY_INVALID_DATASET EXCEPTION;
42 GCS_CCY_INVALID_ORG_TRACKING EXCEPTION;
43 GCS_CCY_INVALID_SEC_TRACKING EXCEPTION;
44 GCS_CCY_NO_END_DATE EXCEPTION;
45 GCS_CCY_NO_RE_TEMPLATE EXCEPTION;
46 GCS_CCY_NO_DATA EXCEPTION;
47 GCS_CCY_NO_RE_ACCT_CREATED EXCEPTION;
48 GCS_CCY_NO_RE_ACCT_UPDATED EXCEPTION;
49 GCS_CCY_NO_RATE_CREATED EXCEPTION;
50 GCS_CCY_NO_RATE_UPDATED EXCEPTION;
51 GCS_CCY_NO_END_RATE EXCEPTION;
52 GCS_CCY_NO_AVG_RATE EXCEPTION;
53 GCS_CCY_NO_DATASET EXCEPTION;
54 GCS_CCY_AVG_RATE_NOT_FOUND EXCEPTION;
55 GCS_CCY_END_RATE_NOT_FOUND EXCEPTION;
56 GCS_CCY_NO_SS_CODE EXCEPTION;
57 GCS_CCY_NO_ATTR_VERSION EXCEPTION;
58 GCS_CCY_NO_SPECIFIC_INTERCO EXCEPTION;
59
60 --
61 -- PRIVATE PROCEDURES/FUNCTIONS
62 --
63
64 --
65 -- Procedure
66 -- Module_Log_Write
67 -- Purpose
68 -- Write the procedure or function entered or exited, and the time that
69 -- this happened. Write it to the log repository.
70 -- Arguments
71 -- p_module Name of the module
72 -- p_action_type Entered, Exited Successfully, or Exited with Failure
73 -- Example
74 -- GCS_TRANSLATION_PKG.Module_Log_Write
75 -- Notes
76 --
77 PROCEDURE Module_Log_Write
78 (p_module VARCHAR2,
79 p_action_type VARCHAR2) IS
80 BEGIN
81 -- Only print if the log level is set at the appropriate level
82 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE THEN
83 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, g_api || '.' || p_module,
84 p_action_type || ' ' || p_module || '() ' ||
85 to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
86 END IF;
87 -- FND_FILE.PUT_LINE(FND_FILE.LOG, p_action_type || ' ' || p_module ||
88 -- '() ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
89 END Module_Log_Write;
90
91 --
92 -- Procedure
93 -- Write_To_Log
94 -- Purpose
95 -- Write the text given to the log in 3500 character increments
96 -- this happened. Write it to the log repository.
97 -- Arguments
98 -- p_module Name of the module
99 -- p_level Logging level
100 -- p_text Text to write
101 -- Example
102 -- GCS_TRANSLATION_PKG.Write_To_Log
103 -- Notes
104 --
105 PROCEDURE Write_To_Log
106 (p_module VARCHAR2,
107 p_level NUMBER,
108 p_text VARCHAR2)
109 IS
110 api_module_concat VARCHAR2(200);
111 text_with_date VARCHAR2(32767);
112 text_with_date_len NUMBER;
113 curr_index NUMBER;
114 BEGIN
115 -- Only print if the log level is set at the appropriate level
116 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= p_level THEN
117 api_module_concat := g_api || '.' || p_module;
118 text_with_date := to_char(sysdate,'DD-MON-YYYY HH:MI:SS')||g_nl||p_text;
119 text_with_date_len := length(text_with_date);
120 curr_index := 1;
121 WHILE curr_index <= text_with_date_len LOOP
122 fnd_log.string(p_level, api_module_concat,
123 substr(text_with_date, curr_index, 3500));
124 curr_index := curr_index + 3500;
125 END LOOP;
126 END IF;
127 END Write_To_Log;
128
129 --
130 -- Procedure
131 -- Set_Globals
132 -- Purpose
133 -- Sets all the global variables necessary for GCS Translation.
134 -- Example
135 -- GCS_TRANSLATION_PKG.Set_Globals;
136 -- Notes
137 --
138 PROCEDURE Set_Globals IS
139 module VARCHAR2(30);
140 BEGIN
141 module := 'SET_GLOBALS';
142 module_log_write(module, g_module_enter);
143
144 g_fnd_user_id := fnd_global.user_id;
145 g_fnd_login_id := fnd_global.login_id;
146
147 -- Initialize the dimension attribute and dimension information.
148 GCS_UTILITY_PKG.init_dimension_attr_info;
149 GCS_UTILITY_PKG.init_dimension_info;
150
151 g_cp_end_date_attr_id := GCS_UTILITY_PKG.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE').attribute_id;
152
153 g_li_acct_type_attr_id := GCS_UTILITY_PKG.g_dimension_attr_info('LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE').attribute_id;
154
155 g_xat_sign_attr_id := GCS_UTILITY_PKG.g_dimension_attr_info('EXT_ACCOUNT_TYPE_CODE-SIGN').attribute_id;
156
157 g_xat_basic_acct_type_attr_id := GCS_UTILITY_PKG.g_dimension_attr_info('EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE').attribute_id;
158
159 g_cp_end_date_v_id := GCS_UTILITY_PKG.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE').version_id;
160
161 g_li_acct_type_v_id := GCS_UTILITY_PKG.g_dimension_attr_info('LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE').version_id;
162
163 g_xat_sign_v_id := GCS_UTILITY_PKG.g_dimension_attr_info('EXT_ACCOUNT_TYPE_CODE-SIGN').version_id;
164
165 g_xat_basic_acct_type_v_id := GCS_UTILITY_PKG.g_dimension_attr_info('EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE').version_id;
166
167 g_dims := gcs_utility_pkg.g_gcs_dimension_info;
168
169 module_log_write(module, g_module_success);
170 EXCEPTION
171 WHEN GCS_CCY_SUBPROGRAM_RAISED THEN
172 module_log_write(module, g_module_failure);
173 raise GCS_CCY_SUBPROGRAM_RAISED;
174 WHEN OTHERS THEN
175 FND_MESSAGE.set_name('GCS', 'GCS_CCY_GLOBALS_UNEXP_ERR');
176 g_error_text := FND_MESSAGE.get;
177 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
178 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, SQLERRM);
179 module_log_write(module, g_module_failure);
180 raise GCS_CCY_SUBPROGRAM_RAISED;
181 END Set_Globals;
182
183 --
184 -- Procedure
185 -- Get_Setup_Data
186 -- Purpose
187 -- Get all the data necessary to perform this translation, such as the
188 -- translation mode, source and target currencies, etc.
189 -- Arguments
190 -- p_cal_period_id Period that is to be translated.
191 -- p_cons_relationship_id The relationship for which this translation
192 -- is taking place.
193 -- p_balance_type_code 'ACTUAL', 'ADB', or 'BUDGET' translation.
194 -- x_dataset_code Dataset for which this translation is being
195 -- performed.
196 -- x_cal_period_info Information regarding this period.
197 -- x_curr_treatment_id Currency Treatment.
198 -- x_eq_translation_mode YTD or PTD mode equity translation.
199 -- x_is_translation_mode YTD or PTD mode income statement translation.
200 -- x_per_rate_type_name Period End Rate Type Name.
201 -- x_per_avg_rate_type_name Period Average Rate Type Name.
202 -- x_hierarchy_id Consolidation Hierarchy.
203 -- x_child_entity_id Entity on which translation is being performed.
204 -- x_parent_entity_id Parent entity for consolidation relationship.
205 -- x_ledger_id Ledger of the child entity.
206 -- x_source_currency From currency.
207 -- x_target_currency To currency.
208 -- x_period_end_date Last day of this period.
209 -- x_period_end_rate Period End Rate.
210 -- x_period_avg_rate Period Average Rate.
211 -- x_ccy_round_factor The minimum accountable unit or precision for
212 -- the parent currency.
213 -- x_first_ever_period Y/N whether this is the first period ever
214 -- translated for the relationship.
215 -- x_re_tmp Retained Earnings template.
216 -- x_cta_tmp CTA account template.
217 -- x_org_tracking_flag 'Y' (balance by org) or 'N' (don't)
218 -- x_secondary_dim_col Secondary tracking dimension column name.
219 -- x_source_system_code Source System Code for GCS.
220 -- x_specific_interco_id Specific intercomany id if applicable.
221 -- x_group_by_flag Whether or not a group by is needed in the
222 -- SQL statements for translation.
223 -- x_hier_li_id Retained earnings' line item id.
224 -- Example
225 -- GCS_TRANSLATION_PKG.Get_Setup_Data;
226 -- Notes
227 --
228 PROCEDURE Get_Setup_Data
229 (p_cal_period_id NUMBER,
230 p_cons_relationship_id NUMBER,
231 p_balance_type_code VARCHAR2,
232 p_hier_dataset_code NUMBER,
233 x_cal_period_info OUT NOCOPY GCS_UTILITY_PKG.r_cal_period_info,
234 x_curr_treatment_id OUT NOCOPY NUMBER,
235 x_eq_translation_mode OUT NOCOPY VARCHAR2,
236 x_is_translation_mode OUT NOCOPY VARCHAR2,
237 x_per_rate_type_name OUT NOCOPY VARCHAR2,
238 x_per_avg_rate_type_name OUT NOCOPY VARCHAR2,
239 x_hierarchy_id OUT NOCOPY NUMBER,
240 x_child_entity_id OUT NOCOPY NUMBER,
241 x_parent_entity_id OUT NOCOPY NUMBER,
242 x_ledger_id OUT NOCOPY NUMBER,
243 x_source_currency OUT NOCOPY VARCHAR2,
244 x_target_currency OUT NOCOPY VARCHAR2,
245 x_period_end_date OUT NOCOPY DATE,
246 x_period_end_rate OUT NOCOPY NUMBER,
247 x_period_avg_rate OUT NOCOPY NUMBER,
248 x_ccy_round_factor OUT NOCOPY NUMBER,
249 x_first_ever_period OUT NOCOPY VARCHAR2,
250 x_re_tmp OUT NOCOPY GCS_TEMPLATES_PKG.TemplateRecord,
251 x_cta_tmp OUT NOCOPY GCS_TEMPLATES_PKG.TemplateRecord,
252 x_org_tracking_flag OUT NOCOPY VARCHAR2,
253 x_secondary_dim_col OUT NOCOPY VARCHAR2,
254 x_source_system_code OUT NOCOPY NUMBER,
255 x_specific_interco_id OUT NOCOPY NUMBER,
256 x_group_by_flag OUT NOCOPY VARCHAR2,
257 x_hier_li_id OUT NOCOPY NUMBER) IS
258
259 my_lang VARCHAR2(30);
260
261 -- Used to obtain the period name given the cal_period_id
262 CURSOR period_name_c IS
263 SELECT cal_period_name
264 FROM fem_cal_periods_tl
265 WHERE cal_period_id = p_cal_period_id
266 AND language = my_lang;
267
268 -- Used to obtain the entity name given the entity_id
269 CURSOR entity_name_c(c_entity_id NUMBER) IS
270 SELECT entity_name
271 FROM fem_entities_tl
272 WHERE entity_id = c_entity_id
273 AND language = my_lang;
274
275 -- Used to obtain relationship information.
276 CURSOR relationship_c IS
277 SELECT rel.hierarchy_id,
278 rel.child_entity_id,
279 rel.parent_entity_id,
280 rel.curr_treatment_id
281 FROM gcs_cons_relationships rel
282 WHERE rel.cons_relationship_id = p_cons_relationship_id;
283
284 -- Used to obtain currency treatment information including cta template.
285 CURSOR curr_treatment_c(c_curr_treatment_id NUMBER) IS
286 SELECT equity_mode_code,
287 inc_stmt_mode_code,
288 ending_rate_type,
289 average_rate_type,
290 financial_elem_id, product_id,
291 natural_account_id, channel_id, line_item_id, project_id,
292 customer_id, task_id, cta_user_dim1_id, cta_user_dim2_id,
293 cta_user_dim3_id, cta_user_dim4_id, cta_user_dim5_id,
294 cta_user_dim6_id, cta_user_dim7_id, cta_user_dim8_id,
295 cta_user_dim9_id, cta_user_dim10_id
296 FROM gcs_curr_treatments_b ctb
297 WHERE curr_treatment_id = c_curr_treatment_id;
298
299 -- Used to obtain entity information for the parent
300 CURSOR entity_c(c_entity_id NUMBER, c_hierarchy_id NUMBER) IS
301 SELECT eca.currency_code
302 FROM gcs_entity_cons_attrs eca
303 WHERE eca.entity_id = c_entity_id
304 AND eca.hierarchy_id = c_hierarchy_id;
305
306 -- Used to obtain hierarchy information
307 CURSOR hierarchy_c(c_hierarchy_id NUMBER) IS
308 SELECT balance_by_org_flag,
309 column_name,
310 fem_ledger_id
311 FROM gcs_hierarchies_b
312 WHERE hierarchy_id = c_hierarchy_id;
313
314 -- Used to get the specific intercompany information
315 CURSOR category_c IS
316 SELECT specific_intercompany_id
317 FROM gcs_categories_b
318 WHERE category_code = 'INTRACOMPANY';
319
320 -- Used to determine if translation is being run for the first time for
321 -- this relationship and dataset.
322 CURSOR is_earliest_period_c( c_hierarchy_id NUMBER,
323 c_entity_id NUMBER,
324 c_currency_code VARCHAR2) IS
325 SELECT decode(earliest_ever_period_id, p_cal_period_id, 'Y', 'N')
326 FROM gcs_translation_track_h
327 WHERE hierarchy_id = c_hierarchy_id
328 AND entity_id = c_entity_id
329 AND currency_code = c_currency_code
330 AND dataset_code = p_hier_dataset_code;
331
332 -- Used to get the end date of the period for which translation is
333 -- being run.
334 CURSOR end_date_c IS
335 SELECT fcpa_period_end_date.date_assign_value
336 FROM fem_cal_periods_attr fcpa_period_end_date
337 WHERE fcpa_period_end_date.cal_period_id = p_cal_period_id
338 AND fcpa_period_end_date.attribute_id = g_cp_end_date_attr_id
339 AND fcpa_period_end_date.version_id = g_cp_end_date_v_id;
340
341 -- Used to get the retained earnings template.
342 CURSOR re_template_c(c_hierarchy_id NUMBER) IS
343 SELECT financial_elem_id, product_id, natural_account_id, channel_id,
344 line_item_id, project_id, customer_id, task_id, user_dim1_id,
345 user_dim2_id, user_dim3_id, user_dim4_id, user_dim5_id,
346 user_dim6_id, user_dim7_id, user_dim8_id, user_dim9_id,
347 user_dim10_id
348 FROM GCS_DIMENSION_TEMPLATES
349 WHERE hierarchy_id = c_hierarchy_id
350 AND template_code = 'RE';
351
352 -- Used to get the minimum accountable unit for the currency given.
353 CURSOR ccy_mau_c(c_ccy VARCHAR2) IS
354 SELECT nvl(minimum_accountable_unit, power(10, -precision))
355 FROM fnd_currencies
356 WHERE currency_code = c_ccy;
357
358 -- Get the source system code for GCS
359 CURSOR source_system_code_c IS
360 SELECT source_system_code
361 FROM fem_source_systems_b
362 WHERE source_system_display_code = 'GCS';
363
364 -- Used to get the end and average rate type names
365 CURSOR rate_type_name_c(c_rate_type VARCHAR2) IS
366 SELECT user_conversion_type
367 FROM gl_daily_conversion_types
368 WHERE conversion_type = c_rate_type;
369
370 -- Used to get the entity type
371 CURSOR entity_type_c(c_entity_id NUMBER) IS
372 SELECT fea.dim_attribute_varchar_member
373 FROM fem_entities_attr fea,
374 fem_dim_attributes_b fdab,
375 fem_dim_attr_versions_b fdavb
376 WHERE fea.entity_id = c_entity_id
377 AND fea.attribute_id = fdab.attribute_id
378 AND fea.version_id = fdavb.version_id
379 AND fdab.attribute_varchar_label = 'ENTITY_TYPE_CODE'
380 AND fdavb.attribute_id = fdab.attribute_id
381 AND fdavb.default_version_flag = 'Y';
382
383 -- See whether there are any non-DataPrep rows in an operating entity
384 CURSOR source_balance_c(
385 c_cal_period_id NUMBER,
386 c_source_system_code NUMBER,
387 c_currency_code VARCHAR2,
388 c_ledger_id NUMBER,
389 c_entity_id NUMBER) IS
390 SELECT 1
391 FROM fem_balances fb,
392 gcs_categories_b cb
393 WHERE fb.dataset_code = p_hier_dataset_code
394 AND fb.cal_period_id = c_cal_period_id
395 AND fb.source_system_code = c_source_system_code
396 AND fb.currency_code = c_currency_code
397 AND fb.ledger_id = c_ledger_id
398 AND fb.entity_id = c_entity_id
399 AND fb.created_by_object_id <> cb.associated_object_id
400 AND cb.category_code = 'DATAPREPARATION';
401
402
403 -- Bugfix 5707630: Cursor returns the line item id slected for retained earnings on hierarchy page.
404 CURSOR re_line_item_cur (p_hier_id NUMBER) IS
405 SELECT line_item_id
406 FROM gcs_dimension_templates
407 WHERE hierarchy_id = p_hier_id
408 AND template_code = 'RE';
409
410
411 dummy NUMBER;
412
413 -- entity type of the child
414 entity_type VARCHAR2(30);
415
416 -- Used for error logging
417 period_name VARCHAR2(150);
418
419 per_rate_type VARCHAR2(30);
420 per_avg_rate_type VARCHAR2(30);
421
422 module VARCHAR2(30);
423 BEGIN
424 module := 'GET_SETUP_DATA';
425 my_lang := userenv('LANG');
426 module_log_write(module, g_module_enter);
427
428 write_to_log(module, FND_LOG.LEVEL_STATEMENT,
429 'SELECT rel.hierarchy_id, rel.child_entity_id, ' ||
430 'rel.parent_entity_id, rel.curr_treatment_id' || g_nl ||
431 'FROM gcs_cons_relationships rel' || g_nl ||
432 'WHERE rel.cons_relationship_id = ' || p_cons_relationship_id);
433
434 -- Get relationship information.
435 OPEN relationship_c;
436 FETCH relationship_c INTO x_hierarchy_id, x_child_entity_id,
437 x_parent_entity_id, x_curr_treatment_id;
438 IF relationship_c%NOTFOUND THEN
439 CLOSE relationship_c;
440 raise GCS_CCY_INVALID_REL;
441 END IF;
442 CLOSE relationship_c;
443
444 begin
445 -- Get current and previous period information.
446 GCS_UTILITY_PKG.get_cal_period_details(p_cal_period_id, x_cal_period_info);
447 exception
448 when others then
449 raise GCS_CCY_INVALID_PERIOD;
450 end;
451
452 write_to_log(module, FND_LOG.LEVEL_STATEMENT,
453 'SELECT cal_period_name' || g_nl ||
454 'FROM fem_cal_periods_tl' || g_nl ||
455 'WHERE cal_period_id = ' || p_cal_period_id || g_nl ||
456 'AND language = ''' || my_lang || '''');
457
458 -- Get the period name
459 OPEN period_name_c;
460 FETCH period_name_c INTO period_name;
461 IF period_name_c%NOTFOUND THEN
462 CLOSE period_name_c;
463 raise GCS_CCY_INVALID_PERIOD;
464 END IF;
465 CLOSE period_name_c;
466
467
468 write_to_log(module, FND_LOG.LEVEL_STATEMENT,
469 'SELECT balance_by_org_flag, column_name, fem_ledger_id' || g_nl||
470 'FROM gcs_hierarchies_b' || g_nl ||
471 'WHERE hierarchy_id = ' || x_hierarchy_id);
472
473 -- Get org tracking and secondary tracking information.
474 OPEN hierarchy_c(x_hierarchy_id);
475 FETCH hierarchy_c INTO x_org_tracking_flag, x_secondary_dim_col, x_ledger_id;
476 IF hierarchy_c%NOTFOUND THEN
477 CLOSE hierarchy_c;
478 raise GCS_CCY_INVALID_HIERARCHY;
479 END IF;
480 CLOSE hierarchy_c;
481
482
483 write_to_log(module, FND_LOG.LEVEL_STATEMENT,
484 'SELECT specific_intercompany_id' || g_nl||
485 'FROM gcs_categories_b' || g_nl ||
486 'WHERE category_code = ''INTRACOMPANY''');
487
488 -- Get the intercompany information
489 OPEN category_c;
490 FETCH category_c INTO x_specific_interco_id;
491 IF category_c%NOTFOUND THEN
492 CLOSE category_c;
493 raise GCS_CCY_NO_SPECIFIC_INTERCO;
494 END IF;
495 CLOSE category_c;
496
497
498 write_to_log(module, FND_LOG.LEVEL_STATEMENT,
499 'SELECT equity_mode_code, inc_stmt_mode_code, ending_rate_type, ' ||
500 'average_rate_type, financial_elem_id, ' ||
501 'product_id, natural_account_id, channel_id, line_item_id, project_id, ' ||
502 'customer_id, task_id, cta_user_dim1_id, cta_user_dim2_id, ' ||
503 'cta_user_dim3_id, cta_user_dim4_id, cta_user_dim5_id, cta_user_dim6_id, ' ||
504 'cta_user_dim7_id, cta_user_dim8_id, cta_user_dim9_id, ' ||
505 'cta_user_dim10_id' || g_nl ||
506 'FROM gcs_curr_treatments_b ctb' || g_nl ||
507 'WHERE curr_treatment_id = ' || x_curr_treatment_id);
508
509 -- Get information on the currency_treatment.
510 OPEN curr_treatment_c(x_curr_treatment_id);
511 FETCH curr_treatment_c INTO
512 x_eq_translation_mode, x_is_translation_mode, per_rate_type,
513 per_avg_rate_type,
514 x_cta_tmp.financial_elem_id, x_cta_tmp.product_id,
515 x_cta_tmp.natural_account_id, x_cta_tmp.channel_id,
516 x_cta_tmp.line_item_id, x_cta_tmp.project_id, x_cta_tmp.customer_id,
517 x_cta_tmp.task_id, x_cta_tmp.user_dim1_id, x_cta_tmp.user_dim2_id,
518 x_cta_tmp.user_dim3_id, x_cta_tmp.user_dim4_id, x_cta_tmp.user_dim5_id,
519 x_cta_tmp.user_dim6_id, x_cta_tmp.user_dim7_id, x_cta_tmp.user_dim8_id,
520 x_cta_tmp.user_dim9_id, x_cta_tmp.user_dim10_id;
521 IF curr_treatment_c%NOTFOUND THEN
522 CLOSE curr_treatment_c;
523 raise GCS_CCY_INVALID_CCY_TREAT;
524 END IF;
525 CLOSE curr_treatment_c;
526
527 write_to_log(module, FND_LOG.LEVEL_STATEMENT,
528 'SELECT user_conversion_type' || g_nl ||
529 'FROM gl_daily_conversion_types' || g_nl ||
530 'WHERE conversion_type = ''' || per_rate_type || '''');
531
532 OPEN rate_type_name_c(per_rate_type);
533 FETCH rate_type_name_c INTO x_per_rate_type_name;
534 IF rate_type_name_c%NOTFOUND THEN
535 CLOSE rate_type_name_c;
536 raise GCS_CCY_END_RATE_NOT_FOUND;
537 END IF;
538 CLOSE rate_type_name_c;
539
540 write_to_log(module, FND_LOG.LEVEL_STATEMENT,
541 'SELECT user_conversion_type' || g_nl ||
542 'FROM gl_daily_conversion_types' || g_nl ||
543 'WHERE conversion_type = ''' || per_avg_rate_type || '''');
544
545 OPEN rate_type_name_c(per_avg_rate_type);
546 FETCH rate_type_name_c INTO x_per_avg_rate_type_name;
547 IF rate_type_name_c%NOTFOUND THEN
548 CLOSE rate_type_name_c;
549 raise GCS_CCY_AVG_RATE_NOT_FOUND;
550 END IF;
551 CLOSE rate_type_name_c;
552
553
554
555 write_to_log(module, FND_LOG.LEVEL_STATEMENT,
556 'SELECT eca.currency_code' || g_nl ||
557 'FROM gcs_entity_cons_attrs eca' || g_nl ||
558 'WHERE eca.entity_id = ' || x_child_entity_id || g_nl ||
559 'AND eca.hierarchy_id = ' || x_hierarchy_id);
560
561 -- Get information on the parent entity.
562 OPEN entity_c(x_child_entity_id, x_hierarchy_id);
563 FETCH entity_c INTO x_source_currency;
564 IF entity_c%NOTFOUND THEN
565 CLOSE entity_c;
566 raise GCS_CCY_INVALID_CHILD;
567 END IF;
568 CLOSE entity_c;
569
570 write_to_log(module, FND_LOG.LEVEL_STATEMENT,
571 'SELECT eca.currency_code' || g_nl ||
572 'FROM gcs_entity_cons_attrs eca' || g_nl ||
573 'WHERE eca.entity_id = ' || x_parent_entity_id || g_nl ||
574 'AND eca.hierarchy_id = ' || x_hierarchy_id);
575
576 -- Get information on the parent entity.
577 OPEN entity_c(x_parent_entity_id, x_hierarchy_id);
578 FETCH entity_c INTO x_target_currency;
579 IF entity_c%NOTFOUND THEN
580 CLOSE entity_c;
581 raise GCS_CCY_INVALID_PARENT;
582 END IF;
583 CLOSE entity_c;
584
585 write_to_log(module, FND_LOG.LEVEL_STATEMENT,
586 'SELECT fcpa_period_end_date.date_assign_value' || g_nl ||
587 'FROM fem_cal_periods_attr fcpa_period_end_date' || g_nl ||
588 'WHERE fcpa_period_end_date.cal_period_id = ' || p_cal_period_id || g_nl ||
589 'AND fcpa_period_end_date.attribute_id = ' || g_cp_end_date_attr_id || g_nl ||
590 'AND fcpa_period_end_date.version_id = ' || g_cp_end_date_v_id);
591
592 -- Get the end date for this particular period
593 OPEN end_date_c;
594 FETCH end_date_c INTO x_period_end_date;
595 IF end_date_c%NOTFOUND THEN
596 CLOSE end_date_c;
597 raise GCS_CCY_NO_END_DATE;
598 END IF;
599 CLOSE end_date_c;
600
601 -- Get the end rate, and make sure to check for errors.
602 begin
603 x_period_end_rate := GL_CURRENCY_API.get_rate
604 (x_source_currency, x_target_currency,
605 x_period_end_date, per_rate_type);
606 IF x_period_end_rate IS NULL THEN
607 raise GCS_CCY_NO_END_RATE;
608 END IF;
609 exception
610 when others then
611 raise GCS_CCY_NO_END_RATE;
612 end;
613
614 -- Get the average rate, and make sure to check for errors.
615 begin
616 x_period_avg_rate := GL_CURRENCY_API.get_rate
617 (x_source_currency, x_target_currency,
618 x_period_end_date, per_avg_rate_type);
619 IF x_period_avg_rate IS NULL THEN
620 raise GCS_CCY_NO_AVG_RATE;
621 END IF;
622 exception
623 when others then
624 raise GCS_CCY_NO_AVG_RATE;
625 end;
626
627 write_to_log(module, FND_LOG.LEVEL_STATEMENT,
628 'SELECT financial_elem_id, product_id, ' ||
629 'natural_account_id, channel_id, line_item_id, project_id, customer_id, ' ||
630 'task_id, user_dim1_id, user_dim2_id, user_dim3_id, user_dim4_id, ' ||
631 'user_dim5_id, user_dim6_id, user_dim7_id, user_dim8_id, user_dim9_id, ' ||
632 'user_dim10_id' || g_nl ||
633 'FROM GCS_DIMENSION_TEMPLATES' || g_nl ||
634 'WHERE hierarchy_id = ' || x_hierarchy_id || g_nl ||
635 'AND template_code = ''RE''');
636
637 -- Get the retained earnings template.
638 OPEN re_template_c(x_hierarchy_id);
639 FETCH re_template_c INTO x_re_tmp;
640 IF re_template_c%NOTFOUND THEN
641 CLOSE re_template_c;
642 raise GCS_CCY_NO_RE_TEMPLATE;
643 END IF;
644 CLOSE re_template_c;
645
646 write_to_log(module, FND_LOG.LEVEL_STATEMENT,
647 'SELECT nvl(minimum_accountable_unit, power(10, -precision))' || g_nl ||
648 'FROM fnd_currencies' || g_nl ||
649 'WHERE currency_code = ''' || x_target_currency || '''');
650
651 -- Get the minimum accountable unit of the target currency.
652 OPEN ccy_mau_c(x_target_currency);
653 FETCH ccy_mau_c INTO x_ccy_round_factor;
654 IF ccy_mau_c%NOTFOUND THEN
655 CLOSE ccy_mau_c;
656 raise GCS_CCY_INVALID_TARGET_CCY;
657 END IF;
658 CLOSE ccy_mau_c;
659
660 write_to_log(module, FND_LOG.LEVEL_STATEMENT,
661 'SELECT decode(earliest_ever_period_id, ' || p_cal_period_id ||
662 ', ''Y'', ''N'')' || g_nl ||
663 'FROM gcs_translation_track_h' || g_nl ||
664 'WHERE cons_relationship_id = ' || p_cons_relationship_id || g_nl ||
665 'AND dataset_code = ' || p_hier_dataset_code);
666
667 -- Check if this is the first ever translated period. If so, this program
668 -- will use the first ever translated period rule. If not, it will use
669 -- the rule for non-first ever translated periods.
670 OPEN is_earliest_period_c( x_hierarchy_id, x_child_entity_id,
671 x_target_currency);
672 FETCH is_earliest_period_c INTO x_first_ever_period;
673 IF is_earliest_period_c%NOTFOUND THEN
674 x_first_ever_period := 'Y';
675 END IF;
676 CLOSE is_earliest_period_c;
677
678 write_to_log(module, FND_LOG.LEVEL_STATEMENT,
679 'SELECT source_system_code' || g_nl ||
680 'FROM fem_source_systems_b' || g_nl ||
681 'WHERE source_system_display_code = ''GCS''');
682
683 -- Get the source system code for GCS.
684 OPEN source_system_code_c;
685 FETCH source_system_code_c INTO x_source_system_code;
686 IF source_system_code_c%NOTFOUND THEN
687 CLOSE source_system_code_c;
688 raise GCS_CCY_NO_SS_CODE;
689 END IF;
690 CLOSE source_system_code_c;
691
692 -- Set the financial_elem_id of the templates appropriately if the
693 -- balance type is 'ADB'
694 IF p_balance_type_code = 'ADB' THEN
695 x_re_tmp.financial_elem_id := gcs_utility_pkg.g_avg_fin_elem;
696 x_cta_tmp.financial_elem_id := gcs_utility_pkg.g_avg_fin_elem;
697 END IF;
698
699 -- Now figure out whether a group by is needed or not
700 OPEN entity_type_c(x_child_entity_id);
701 FETCH entity_type_c INTO entity_type;
702 IF entity_type_c%NOTFOUND THEN
703 CLOSE entity_type_c;
704 raise GCS_CCY_INVALID_CHILD;
705 END IF;
706 CLOSE entity_type_c;
707
708 IF entity_type = 'O' THEN
709 OPEN source_balance_c(x_cal_period_info.cal_period_id, x_source_system_code, x_source_currency, x_ledger_id, x_child_entity_id);
710 FETCH source_balance_c INTO dummy;
711 IF source_balance_c%NOTFOUND THEN
712 CLOSE source_balance_c;
713 x_group_by_flag := 'N';
714 ELSE
715 CLOSE source_balance_c;
716 x_group_by_flag := 'Y';
717 END IF;
718 ELSE
719 x_group_by_flag := 'N';
720 END IF;
721
722 -- Bugfix 5707630: Fectch the line item id from the cursor.
723 OPEN re_line_item_cur(x_hierarchy_id);
724 FETCH re_line_item_cur INTO x_hier_li_id;
725 CLOSE re_line_item_cur;
726
727
728
729 module_log_write(module, g_module_success);
730 EXCEPTION
731 WHEN GCS_CCY_SUBPROGRAM_RAISED THEN
732 module_log_write(module, g_module_failure);
733 raise GCS_CCY_SUBPROGRAM_RAISED;
734 WHEN GCS_CCY_NO_SS_CODE THEN
735 FND_MESSAGE.set_name('GCS', 'GCS_CCY_NO_SS_CODE_ERR');
736 g_error_text := FND_MESSAGE.get;
737 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
738 module_log_write(module, g_module_failure);
739 raise GCS_CCY_SUBPROGRAM_RAISED;
740 WHEN GCS_CCY_NO_SPECIFIC_INTERCO THEN
741 FND_MESSAGE.set_name('GCS', 'GCS_CCY_NO_INTERCO_ERR');
742 g_error_text := FND_MESSAGE.get;
743 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
744 module_log_write(module, g_module_failure);
745 raise GCS_CCY_SUBPROGRAM_RAISED;
746 WHEN GCS_CCY_INVALID_PERIOD THEN
747 FND_MESSAGE.set_name('GCS', 'GCS_CCY_INVALID_PERIOD_ERR');
748 FND_MESSAGE.set_token('CAL_PERIOD_ID', p_cal_period_id);
749 g_error_text := FND_MESSAGE.get;
750 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
751 module_log_write(module, g_module_failure);
752 raise GCS_CCY_SUBPROGRAM_RAISED;
753 WHEN GCS_CCY_INVALID_REL THEN
754 FND_MESSAGE.set_name('GCS', 'GCS_CCY_INVALID_REL_ERR');
755 FND_MESSAGE.set_token('RELATIONSHIP_ID', p_cons_relationship_id);
756 g_error_text := FND_MESSAGE.get;
757 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
758 module_log_write(module, g_module_failure);
759 raise GCS_CCY_SUBPROGRAM_RAISED;
760 WHEN GCS_CCY_INVALID_CCY_TREAT THEN
761 FND_MESSAGE.set_name('GCS', 'GCS_CCY_INVALID_CCY_TREAT_ERR');
762 FND_MESSAGE.set_token('RELATIONSHIP_ID', p_cons_relationship_id);
763 FND_MESSAGE.set_token('TREATMENT_ID', x_curr_treatment_id);
764 g_error_text := FND_MESSAGE.get;
765 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
766 module_log_write(module, g_module_failure);
767 raise GCS_CCY_SUBPROGRAM_RAISED;
768 WHEN GCS_CCY_INVALID_CHILD THEN
769 FND_MESSAGE.set_name('GCS', 'GCS_CCY_NO_CHILD_ENTITY_ERR');
770 FND_MESSAGE.set_token('ENTITY_ID', x_child_entity_id);
771 g_error_text := FND_MESSAGE.get;
772 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
773 module_log_write(module, g_module_failure);
774 raise GCS_CCY_SUBPROGRAM_RAISED;
775 WHEN GCS_CCY_INVALID_PARENT THEN
776 FND_MESSAGE.set_name('GCS', 'GCS_CCY_NO_PARENT_ENTITY_ERR');
777 FND_MESSAGE.set_token('ENTITY_ID', x_parent_entity_id);
778 g_error_text := FND_MESSAGE.get;
779 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
780 module_log_write(module, g_module_failure);
781 raise GCS_CCY_SUBPROGRAM_RAISED;
782 WHEN GCS_CCY_INVALID_HIERARCHY THEN
783 FND_MESSAGE.set_name('GCS', 'GCS_CCY_INVALID_HIERARCHY_ERR');
784 FND_MESSAGE.set_token('HIERARCHY_ID', x_hierarchy_id);
785 g_error_text := FND_MESSAGE.get;
786 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
787 module_log_write(module, g_module_failure);
788 raise GCS_CCY_SUBPROGRAM_RAISED;
789 WHEN GCS_CCY_NO_DATASET THEN
790 FND_MESSAGE.set_name('GCS', 'GCS_CCY_NO_DATASET_ERR');
791 FND_MESSAGE.set_token('BAL_TYPE', p_balance_type_code);
792 FND_MESSAGE.set_token('HIERARCHY_ID', x_hierarchy_id);
793 g_error_text := FND_MESSAGE.get;
794 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
795 module_log_write(module, g_module_failure);
796 raise GCS_CCY_SUBPROGRAM_RAISED;
797 WHEN GCS_CCY_NO_END_DATE THEN
798 FND_MESSAGE.set_name('GCS', 'GCS_CCY_NO_END_DATE_ERR');
799 FND_MESSAGE.set_token('PERIOD_NAME', period_name);
800 g_error_text := FND_MESSAGE.get;
801 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
802 module_log_write(module, g_module_failure);
803 raise GCS_CCY_SUBPROGRAM_RAISED;
804 WHEN GCS_CCY_AVG_RATE_NOT_FOUND THEN
805 FND_MESSAGE.set_name('GCS', 'GCS_CCY_RATE_NOT_FOUND_ERR');
806 FND_MESSAGE.set_token('RATE_TYPE', per_avg_rate_type);
807 g_error_text := FND_MESSAGE.get;
808 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
809 module_log_write(module, g_module_failure);
810 raise GCS_CCY_SUBPROGRAM_RAISED;
811 WHEN GCS_CCY_END_RATE_NOT_FOUND THEN
812 FND_MESSAGE.set_name('GCS', 'GCS_CCY_RATE_NOT_FOUND_ERR');
813 FND_MESSAGE.set_token('RATE_TYPE', per_rate_type);
814 g_error_text := FND_MESSAGE.get;
815 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
816 module_log_write(module, g_module_failure);
817 raise GCS_CCY_SUBPROGRAM_RAISED;
818 WHEN GCS_CCY_NO_END_RATE THEN
819 FND_MESSAGE.set_name('GCS', 'GCS_CCY_NO_END_RATE_ERR');
820 FND_MESSAGE.set_token('PERIOD_NAME', period_name);
821 FND_MESSAGE.set_token('RATE_TYPE', x_per_rate_type_name);
822 g_error_text := FND_MESSAGE.get;
823 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
824 module_log_write(module, g_module_failure);
825 raise GCS_CCY_SUBPROGRAM_RAISED;
826 WHEN GCS_CCY_NO_AVG_RATE THEN
827 FND_MESSAGE.set_name('GCS', 'GCS_CCY_NO_AVG_RATE_ERR');
828 FND_MESSAGE.set_token('PERIOD_NAME', period_name);
829 FND_MESSAGE.set_token('RATE_TYPE', x_per_avg_rate_type_name);
830 g_error_text := FND_MESSAGE.get;
831 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
832 module_log_write(module, g_module_failure);
833 raise GCS_CCY_SUBPROGRAM_RAISED;
834 WHEN GCS_CCY_NO_RE_TEMPLATE THEN
835 FND_MESSAGE.set_name('GCS', 'GCS_CCY_NO_RE_TEMPLATE_ERR');
836 FND_MESSAGE.set_token('HIERARCHY_ID', x_hierarchy_id);
837 g_error_text := FND_MESSAGE.get;
838 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
839 module_log_write(module, g_module_failure);
840 raise GCS_CCY_SUBPROGRAM_RAISED;
841 WHEN GCS_CCY_INVALID_TARGET_CCY THEN
842 FND_MESSAGE.set_name('GCS', 'GCS_CCY_INFO_NOT_FOUND_ERR');
843 FND_MESSAGE.set_token('CURRENCY', x_target_currency);
844 g_error_text := FND_MESSAGE.get;
845 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
846 module_log_write(module, g_module_failure);
847 raise GCS_CCY_SUBPROGRAM_RAISED;
848 WHEN OTHERS THEN
849 FND_MESSAGE.set_name('GCS', 'GCS_CCY_SETUP_UNEXPECTED_ERR');
850 g_error_text := FND_MESSAGE.get;
851 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
852 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, SQLERRM);
853 module_log_write(module, g_module_failure);
854 raise GCS_CCY_SUBPROGRAM_RAISED;
855 END Get_Setup_Data;
856
857 --
858 -- Procedure
859 -- Create_RE_Sec_Tracking_Text
860 -- Purpose
861 -- Create part of the dynamic sql for seconday tracking in the equity
862 -- calculation.
863 -- Arguments
864 -- p_re_acct Retained earnings account existence check cursor.
865 -- p_ins_re Retained earnings account creation statement.
866 -- p_ins_join Retained earnings account creation statement join.
867 -- p_upd_re Retained earnings account update statement.
868 -- p_sec_dim Secondary tracking dimension column.
869 -- p_curr_dim Current dimension column being looked at.
870 -- p_template_id Template value to use.
871 -- Example
872 -- GCS_TRANSLATION_PKG.Create_RE_Sec_Tracking_Text;
873 -- Notes
874 --
875 PROCEDURE Create_RE_Sec_Tracking_Text
876 (p_re_acct IN OUT NOCOPY VARCHAR2,
877 p_ins_re IN OUT NOCOPY VARCHAR2,
878 p_ins_join IN OUT NOCOPY VARCHAR2,
879 p_upd_re IN OUT NOCOPY VARCHAR2,
880 p_sec_dim VARCHAR2,
881 p_curr_dim VARCHAR2,
882 p_template_id NUMBER) IS
883 module VARCHAR2(30);
884 BEGIN
885 module := 'CREATE_RE_SEC_TRACKING_TEXT';
886 module_log_write(module, g_module_enter);
887
888 IF gcs_utility_pkg.get_dimension_required(p_curr_dim) = 'Y' THEN
889 p_re_acct := p_re_acct || 'AND ' || p_curr_dim || ' = ';
890 p_ins_join := p_ins_join || 'AND fb.' || p_curr_dim || ' = ';
891 p_upd_re := p_upd_re || 'AND ' || p_curr_dim || ' = ';
892 IF p_sec_dim = p_curr_dim THEN
893 p_re_acct := p_re_acct || ':sec_dim_val_id' || g_nl;
894 p_ins_re := p_ins_re || ':sec_dim_val_id, ';
895 p_ins_join := p_ins_join || ':sec_dim_val_id' || g_nl;
896 p_upd_re := p_upd_re || ':sec_dim_val_id' || g_nl;
897 ELSE
898 p_re_acct := p_re_acct || p_template_id || g_nl;
899 p_ins_re := p_ins_re || p_template_id || ', ';
900 p_ins_join := p_ins_join || p_template_id || g_nl;
901 p_upd_re := p_upd_re || p_template_id || g_nl;
902 END IF;
903 ELSE
904 p_ins_re := p_ins_re || 'null, ';
905 END IF;
906
907 module_log_write(module, g_module_success);
908 EXCEPTION
909 WHEN GCS_CCY_SUBPROGRAM_RAISED THEN
910 module_log_write(module, g_module_failure);
911 raise GCS_CCY_SUBPROGRAM_RAISED;
912 WHEN OTHERS THEN
913 FND_MESSAGE.set_name('GCS', 'GCS_CCY_RE_SEC_TRK_UNEXP_ERR');
914 g_error_text := FND_MESSAGE.get;
915 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
916 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, SQLERRM);
917 module_log_write(module, g_module_failure);
918 raise GCS_CCY_SUBPROGRAM_RAISED;
919 END Create_RE_Sec_Tracking_Text;
920
921 --
922 -- Procedure
923 -- Create_RE_Rate_Sec_Track_Text
924 -- Purpose
925 -- Create part of the dynamic sql for seconday tracking in the equity
926 -- calculation.
927 -- Arguments
928 -- p_amt_text Retained earnings rate amount calculation text.
929 -- p_amt_join Retained earnings rate amount calculation join.
930 -- p_re_rate Retained earnings rate existence check cursor.
931 -- p_ins_rate Retained earnings rate creation statement.
932 -- p_upd_rate Retained earnings rate update statement.
933 -- p_sec_dim Secondary tracking dimension column.
934 -- p_curr_dim Current dimension column being looked at.
935 -- p_template_id Template value to use.
936 -- Example
937 -- GCS_TRANSLATION_PKG.Create_RE_Rate_Sec_Track_Text
938 -- Notes
939 --
940 PROCEDURE Create_RE_Rate_Sec_Track_Text
941 (p_amt_text IN OUT NOCOPY VARCHAR2,
942 p_amt_join IN OUT NOCOPY VARCHAR2,
943 p_re_rate IN OUT NOCOPY VARCHAR2,
944 p_ins_rate IN OUT NOCOPY VARCHAR2,
945 p_upd_rate IN OUT NOCOPY VARCHAR2,
946 p_sec_dim VARCHAR2,
947 p_curr_dim VARCHAR2,
948 p_template_id NUMBER) IS
949 module VARCHAR2(30);
950 BEGIN
951 module := 'CREATE_RE_RATE_SEC_TRACK_TEXT';
952 module_log_write(module, g_module_enter);
953
954 IF gcs_utility_pkg.get_dimension_required(p_curr_dim) = 'Y' THEN
955 p_amt_text := p_amt_text || ' AND fb.' || p_curr_dim || ' = ';
956 p_amt_join := p_amt_join || ' AND el.' || p_curr_dim || ' = ';
957 p_re_rate := p_re_rate || 'AND ' || p_curr_dim || ' = ';
958 p_upd_rate := p_upd_rate || 'AND ' || p_curr_dim || ' = ';
959 IF p_sec_dim = p_curr_dim THEN
960 p_amt_text := p_amt_text || ':sec_dim_val_id' || g_nl;
961 p_amt_join := p_amt_join || ':sec_dim_val_id' || g_nl;
962 p_re_rate := p_re_rate || ':sec_dim_val_id' || g_nl;
963 p_ins_rate := p_ins_rate || ':sec_dim_val_id, ';
964 p_upd_rate := p_upd_rate || ':sec_dim_val_id' || g_nl;
965 ELSE
966 p_amt_text := p_amt_text || p_template_id || g_nl;
967 p_amt_join := p_amt_join || p_template_id || g_nl;
968 p_re_rate := p_re_rate || p_template_id || g_nl;
969 p_ins_rate := p_ins_rate || p_template_id || ', ';
970 p_upd_rate := p_upd_rate || p_template_id || g_nl;
971 END IF;
972 ELSE
973 p_ins_rate := p_ins_rate || 'null, ';
974 END IF;
975
976 module_log_write(module, g_module_success);
977 EXCEPTION
978 WHEN GCS_CCY_SUBPROGRAM_RAISED THEN
979 module_log_write(module, g_module_failure);
980 raise GCS_CCY_SUBPROGRAM_RAISED;
981 WHEN OTHERS THEN
982 FND_MESSAGE.set_name('GCS', 'GCS_CCY_RE_SEC_TRK_UNEXP_ERR');
983 g_error_text := FND_MESSAGE.get;
984 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
985 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, SQLERRM);
986 module_log_write(module, g_module_failure);
987 raise GCS_CCY_SUBPROGRAM_RAISED;
988 END Create_RE_Rate_Sec_Track_Text;
989
990 --
991 -- Procedure
992 -- Create_All_RE_Tracking_Text
993 -- Purpose
994 -- Create all of the dynamic sql for tracking in the retained earnings
995 -- calculation.
996 -- Arguments
997 -- p_re_acct Retained earnings account existence check cursor.
998 -- p_ins_re Retained earnings account creation statement.
999 -- p_ins_join Retained earnings account creation statement join.
1000 -- p_upd_re Retained earnings account update statement.
1001 -- p_org_track_flag Whether or not org tracking is enabled.
1002 -- p_sec_dim_col Secondary tracking dimension column.
1003 -- p_hierarchy_id Hierarchy on which the translation is being run.
1004 -- p_entity_id Entity on which the translation is being run.
1005 -- p_relationship_id Relationship on which the translation is being run.
1006 -- p_specific_interco_id Specific intercomany id if applicable.
1007 -- p_re_template Retained earnings template to use.
1008 -- Example
1009 -- GCS_TRANSLATION_PKG.Create_All_RE_Tracking_Text;
1010 -- Notes
1011 --
1012 PROCEDURE Create_All_RE_Tracking_Text
1013 (p_re_acct IN OUT NOCOPY VARCHAR2,
1014 p_ins_re IN OUT NOCOPY VARCHAR2,
1015 p_ins_join IN OUT NOCOPY VARCHAR2,
1016 p_upd_re IN OUT NOCOPY VARCHAR2,
1017 p_org_track_flag VARCHAR2,
1018 p_sec_dim_col VARCHAR2,
1019 p_hierarchy_id NUMBER,
1020 p_entity_id NUMBER,
1021 p_relationship_id NUMBER,
1022 p_specific_interco_id NUMBER,
1023 p_re_template GCS_TEMPLATES_PKG.TemplateRecord) IS
1024
1025 re_org_id NUMBER; -- retained earnings org_id, if applicable
1026 re_interco_id NUMBER; -- retained earnings interco_id, if applicable
1027 interco_text VARCHAR2(100); -- intercompany matching text
1028
1029 module VARCHAR2(30);
1030 BEGIN
1031 module := 'CREATE_ALL_RE_TRACKING_TEXT';
1032 module_log_write(module, g_module_enter);
1033
1034 -- First add the org tracking joins
1035 IF p_org_track_flag = 'Y' THEN
1036 IF p_specific_interco_id IS NOT NULL THEN
1037 interco_text := p_specific_interco_id;
1038 ELSE
1039 interco_text := ':org_id';
1040 END IF;
1041
1042 p_re_acct := p_re_acct ||
1043 'WHERE company_cost_center_org_id = :org_id' || g_nl ||
1044 'AND intercompany_id = ' || interco_text || g_nl;
1045 p_ins_re := p_ins_re || ':org_id, ' || interco_text || ', ';
1046 p_ins_join :=
1047 'AND fb.company_cost_center_org_id = :org_id' || g_nl ||
1048 'AND fb.intercompany_id = ' || interco_text || g_nl;
1049 p_upd_re := p_upd_re ||
1050 'WHERE company_cost_center_org_id = :org_id' || g_nl ||
1051 'AND intercompany_id = ' || interco_text || g_nl;
1052 ELSE
1053 re_org_id := gcs_utility_pkg.get_org_id(p_entity_id, p_hierarchy_id);
1054
1055 -- If a specific intercompany id is to be used, use it
1056 IF p_specific_interco_id IS NOT NULL THEN
1057 re_interco_id := p_specific_interco_id;
1058 ELSE
1059 re_interco_id := re_org_id;
1060 END IF;
1061
1062 p_re_acct := p_re_acct ||
1063 'WHERE company_cost_center_org_id = ' || re_org_id || g_nl ||
1064 'AND intercompany_id = ' || re_interco_id || g_nl;
1065 p_ins_re := p_ins_re || re_org_id || ', ' || re_interco_id || ', ';
1066 p_ins_join :=
1067 'AND fb.company_cost_center_org_id = ' || re_org_id || g_nl ||
1068 'AND fb.intercompany_id = ' || re_interco_id || g_nl;
1069 p_upd_re := p_upd_re ||
1070 'WHERE company_cost_center_org_id = ' || re_org_id || g_nl ||
1071 'AND intercompany_id = ' || re_interco_id || g_nl;
1072 END IF;
1073
1074 -- Now add the secondary tracking joins
1075 create_re_sec_tracking_text(p_re_acct, p_ins_re, p_ins_join, p_upd_re, p_sec_dim_col, 'FINANCIAL_ELEM_ID', p_re_template.financial_elem_id);
1076 create_re_sec_tracking_text(p_re_acct, p_ins_re, p_ins_join, p_upd_re, p_sec_dim_col, 'PRODUCT_ID', p_re_template.product_id);
1077 create_re_sec_tracking_text(p_re_acct, p_ins_re, p_ins_join, p_upd_re, p_sec_dim_col, 'NATURAL_ACCOUNT_ID', p_re_template.natural_account_id);
1078 create_re_sec_tracking_text(p_re_acct, p_ins_re, p_ins_join, p_upd_re, p_sec_dim_col, 'CHANNEL_ID', p_re_template.channel_id);
1079 create_re_sec_tracking_text(p_re_acct, p_ins_re, p_ins_join, p_upd_re, p_sec_dim_col, 'LINE_ITEM_ID', p_re_template.line_item_id);
1080 create_re_sec_tracking_text(p_re_acct, p_ins_re, p_ins_join, p_upd_re, p_sec_dim_col, 'PROJECT_ID', p_re_template.project_id);
1081 create_re_sec_tracking_text(p_re_acct, p_ins_re, p_ins_join, p_upd_re, p_sec_dim_col, 'CUSTOMER_ID', p_re_template.customer_id);
1082 create_re_sec_tracking_text(p_re_acct, p_ins_re, p_ins_join, p_upd_re, p_sec_dim_col, 'TASK_ID', p_re_template.task_id);
1083 create_re_sec_tracking_text(p_re_acct, p_ins_re, p_ins_join, p_upd_re, p_sec_dim_col, 'USER_DIM1_ID', p_re_template.user_dim1_id);
1084 create_re_sec_tracking_text(p_re_acct, p_ins_re, p_ins_join, p_upd_re, p_sec_dim_col, 'USER_DIM2_ID', p_re_template.user_dim2_id);
1085 create_re_sec_tracking_text(p_re_acct, p_ins_re, p_ins_join, p_upd_re, p_sec_dim_col, 'USER_DIM3_ID', p_re_template.user_dim3_id);
1086 create_re_sec_tracking_text(p_re_acct, p_ins_re, p_ins_join, p_upd_re, p_sec_dim_col, 'USER_DIM4_ID', p_re_template.user_dim4_id);
1087 create_re_sec_tracking_text(p_re_acct, p_ins_re, p_ins_join, p_upd_re, p_sec_dim_col, 'USER_DIM5_ID', p_re_template.user_dim5_id);
1088 create_re_sec_tracking_text(p_re_acct, p_ins_re, p_ins_join, p_upd_re, p_sec_dim_col, 'USER_DIM6_ID', p_re_template.user_dim6_id);
1089 create_re_sec_tracking_text(p_re_acct, p_ins_re, p_ins_join, p_upd_re, p_sec_dim_col, 'USER_DIM7_ID', p_re_template.user_dim7_id);
1090 create_re_sec_tracking_text(p_re_acct, p_ins_re, p_ins_join, p_upd_re, p_sec_dim_col, 'USER_DIM8_ID', p_re_template.user_dim8_id);
1091 create_re_sec_tracking_text(p_re_acct, p_ins_re, p_ins_join, p_upd_re, p_sec_dim_col, 'USER_DIM9_ID', p_re_template.user_dim9_id);
1092 create_re_sec_tracking_text(p_re_acct, p_ins_re, p_ins_join, p_upd_re, p_sec_dim_col, 'USER_DIM10_ID', p_re_template.user_dim10_id);
1093
1094 module_log_write(module, g_module_success);
1095 EXCEPTION
1096 WHEN GCS_CCY_SUBPROGRAM_RAISED THEN
1097 module_log_write(module, g_module_failure);
1098 raise GCS_CCY_SUBPROGRAM_RAISED;
1099 WHEN OTHERS THEN
1100 FND_MESSAGE.set_name('GCS', 'GCS_CCY_ALL_RE_TRK_UNEXP_ERR');
1101 g_error_text := FND_MESSAGE.get;
1102 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
1103 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, SQLERRM);
1104 module_log_write(module, g_module_failure);
1105 raise GCS_CCY_SUBPROGRAM_RAISED;
1106 END Create_All_RE_Tracking_Text;
1107
1108 --
1109 -- Procedure
1110 -- Create_All_RE_Rate_Track_Text
1111 -- Purpose
1112 -- Create all of the dynamic sql for tracking in the retained earnings
1113 -- calculation.
1114 -- Arguments
1115 -- p_amt_text Retained earnings rate amount calculation text.
1116 -- p_amt_join Retained earnings rate amount calculation join.
1117 -- p_re_rate Retained earnings rate existence check cursor.
1118 -- p_ins_rate Retained earnings rate creation statement.
1119 -- p_upd_rate Retained earnings rate update statement.
1120 -- p_org_track_flag Whether or not org tracking is enabled.
1121 -- p_sec_dim_col Secondary tracking dimension column.
1122 -- p_hierarchy_id Hierarchy on which the translation is being run.
1123 -- p_entity_id Entity on which the translation is being run.
1124 -- p_relationship_id Relationship on which the translation is being run.
1125 -- p_specific_interco_id Specific intercomany id if applicable.
1126 -- p_re_template Retained earnings template to use.
1127 -- Example
1128 -- GCS_TRANSLATION_PKG.Create_All_RE_Rate_Track_Text;
1129 -- Notes
1130 --
1131 PROCEDURE Create_All_RE_Rate_Track_Text
1132 (p_amt_text IN OUT NOCOPY VARCHAR2,
1133 p_amt_join IN OUT NOCOPY VARCHAR2,
1134 p_re_rate IN OUT NOCOPY VARCHAR2,
1135 p_ins_rate IN OUT NOCOPY VARCHAR2,
1136 p_upd_rate IN OUT NOCOPY VARCHAR2,
1137 p_org_track_flag VARCHAR2,
1138 p_sec_dim_col VARCHAR2,
1139 p_hierarchy_id NUMBER,
1140 p_entity_id NUMBER,
1141 p_relationship_id NUMBER,
1142 p_specific_interco_id NUMBER,
1143 p_re_template GCS_TEMPLATES_PKG.TemplateRecord) IS
1144
1145 re_org_id NUMBER; -- retained earnings org_id, if applicable
1146 re_interco_id NUMBER; -- retained earnings interco_id, if applicable
1147 interco_text VARCHAR2(100); -- intercompany matching text
1148
1149 module VARCHAR2(30);
1150 BEGIN
1151 module := 'CREATE_ALL_RE_RATE_TRACK_TEXT';
1152 module_log_write(module, g_module_enter);
1153
1154 -- First add the org tracking joins
1155 IF p_org_track_flag = 'Y' THEN
1156 IF p_specific_interco_id IS NOT NULL THEN
1157 interco_text := p_specific_interco_id;
1158 ELSE
1159 interco_text := ':org_id';
1160 END IF;
1161
1162 p_amt_text := p_amt_text ||
1163 ' fb.company_cost_center_org_id = :org_id' || g_nl ||
1164 ' AND fb.intercompany_id = ' || interco_text || g_nl;
1165 p_amt_join :=
1166 ' el.company_cost_center_org_id = :org_id' || g_nl ||
1167 ' AND el.intercompany_id = ' || interco_text || g_nl;
1168 p_re_rate := p_re_rate ||
1169 'AND company_cost_center_org_id = :org_id' || g_nl ||
1170 'AND intercompany_id = ' || interco_text || g_nl;
1171 p_ins_rate := p_ins_rate || ':org_id, ' || interco_text || ', ';
1172 p_upd_rate := p_upd_rate ||
1173 'AND company_cost_center_org_id = :org_id' || g_nl ||
1174 'AND intercompany_id = ' || interco_text || g_nl;
1175 ELSE
1176 re_org_id := gcs_utility_pkg.get_org_id(p_entity_id, p_hierarchy_id);
1177
1178 -- If a specific intercompany id is to be used, use it
1179 IF p_specific_interco_id IS NOT NULL THEN
1180 re_interco_id := p_specific_interco_id;
1181 ELSE
1182 re_interco_id := re_org_id;
1183 END IF;
1184
1185 p_amt_text := p_amt_text ||
1186 ' fb.company_cost_center_org_id = ' || re_org_id || g_nl ||
1187 ' AND fb.intercompany_id = ' || re_interco_id || g_nl;
1188 p_amt_join :=
1189 ' el.company_cost_center_org_id = ' || re_org_id || g_nl ||
1190 ' AND el.intercompany_id = ' || re_interco_id || g_nl;
1191 p_re_rate := p_re_rate ||
1192 'AND company_cost_center_org_id = ' || re_org_id || g_nl ||
1193 'AND intercompany_id = ' || re_interco_id || g_nl;
1194 p_ins_rate := p_ins_rate || re_org_id || ', ' || re_interco_id || ', ';
1195 p_upd_rate := p_upd_rate ||
1196 'AND company_cost_center_org_id = ' || re_org_id || g_nl ||
1197 'AND intercompany_id = ' || re_interco_id || g_nl;
1198 END IF;
1199
1200 -- Now add the secondary tracking joins
1201 create_re_rate_sec_track_text(p_amt_text, p_amt_join, p_re_rate, p_ins_rate, p_upd_rate, p_sec_dim_col, 'FINANCIAL_ELEM_ID', p_re_template.financial_elem_id);
1202 create_re_rate_sec_track_text(p_amt_text, p_amt_join, p_re_rate, p_ins_rate, p_upd_rate, p_sec_dim_col, 'PRODUCT_ID', p_re_template.product_id);
1203 create_re_rate_sec_track_text(p_amt_text, p_amt_join, p_re_rate, p_ins_rate, p_upd_rate, p_sec_dim_col, 'NATURAL_ACCOUNT_ID', p_re_template.natural_account_id);
1204 create_re_rate_sec_track_text(p_amt_text, p_amt_join, p_re_rate, p_ins_rate, p_upd_rate, p_sec_dim_col, 'CHANNEL_ID', p_re_template.channel_id);
1205 create_re_rate_sec_track_text(p_amt_text, p_amt_join, p_re_rate, p_ins_rate, p_upd_rate, p_sec_dim_col, 'LINE_ITEM_ID', p_re_template.line_item_id);
1206 create_re_rate_sec_track_text(p_amt_text, p_amt_join, p_re_rate, p_ins_rate, p_upd_rate, p_sec_dim_col, 'PROJECT_ID', p_re_template.project_id);
1207 create_re_rate_sec_track_text(p_amt_text, p_amt_join, p_re_rate, p_ins_rate, p_upd_rate, p_sec_dim_col, 'CUSTOMER_ID', p_re_template.customer_id);
1208 create_re_rate_sec_track_text(p_amt_text, p_amt_join, p_re_rate, p_ins_rate, p_upd_rate, p_sec_dim_col, 'TASK_ID', p_re_template.task_id);
1209 create_re_rate_sec_track_text(p_amt_text, p_amt_join, p_re_rate, p_ins_rate, p_upd_rate, p_sec_dim_col, 'USER_DIM1_ID', p_re_template.user_dim1_id);
1210 create_re_rate_sec_track_text(p_amt_text, p_amt_join, p_re_rate, p_ins_rate, p_upd_rate, p_sec_dim_col, 'USER_DIM2_ID', p_re_template.user_dim2_id);
1211 create_re_rate_sec_track_text(p_amt_text, p_amt_join, p_re_rate, p_ins_rate, p_upd_rate, p_sec_dim_col, 'USER_DIM3_ID', p_re_template.user_dim3_id);
1212 create_re_rate_sec_track_text(p_amt_text, p_amt_join, p_re_rate, p_ins_rate, p_upd_rate, p_sec_dim_col, 'USER_DIM4_ID', p_re_template.user_dim4_id);
1213 create_re_rate_sec_track_text(p_amt_text, p_amt_join, p_re_rate, p_ins_rate, p_upd_rate, p_sec_dim_col, 'USER_DIM5_ID', p_re_template.user_dim5_id);
1214 create_re_rate_sec_track_text(p_amt_text, p_amt_join, p_re_rate, p_ins_rate, p_upd_rate, p_sec_dim_col, 'USER_DIM6_ID', p_re_template.user_dim6_id);
1215 create_re_rate_sec_track_text(p_amt_text, p_amt_join, p_re_rate, p_ins_rate, p_upd_rate, p_sec_dim_col, 'USER_DIM7_ID', p_re_template.user_dim7_id);
1216 create_re_rate_sec_track_text(p_amt_text, p_amt_join, p_re_rate, p_ins_rate, p_upd_rate, p_sec_dim_col, 'USER_DIM8_ID', p_re_template.user_dim8_id);
1217 create_re_rate_sec_track_text(p_amt_text, p_amt_join, p_re_rate, p_ins_rate, p_upd_rate, p_sec_dim_col, 'USER_DIM9_ID', p_re_template.user_dim9_id);
1218 create_re_rate_sec_track_text(p_amt_text, p_amt_join, p_re_rate, p_ins_rate, p_upd_rate, p_sec_dim_col, 'USER_DIM10_ID', p_re_template.user_dim10_id);
1219
1220 module_log_write(module, g_module_success);
1221 EXCEPTION
1222 WHEN GCS_CCY_SUBPROGRAM_RAISED THEN
1223 module_log_write(module, g_module_failure);
1224 raise GCS_CCY_SUBPROGRAM_RAISED;
1225 WHEN OTHERS THEN
1226 FND_MESSAGE.set_name('GCS', 'GCS_CCY_ALL_RE_TRK_UNEXP_ERR');
1227 g_error_text := FND_MESSAGE.get;
1228 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
1229 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, SQLERRM);
1230 module_log_write(module, g_module_failure);
1231 raise GCS_CCY_SUBPROGRAM_RAISED;
1232 END Create_All_RE_Rate_Track_Text;
1233
1234 --
1235 -- Procedure
1236 -- Calculate_RE_Amt
1237 -- Purpose
1238 -- Calculate balances for the retained earnings account.
1239 -- Arguments
1240 -- p_relationship_id The relationship for which translation is being run.
1241 -- p_cal_period_id The period being translated.
1242 -- p_prev_period_id The period prior to the translation period.
1243 -- p_entity_id Entity on which the translation is being performed.
1244 -- p_hierarchy_id Hierarchy in which the entity resides.
1245 -- p_ledger_id Ledger of the entity to be translated.
1246 -- p_from_ccy From currency code.
1247 -- p_to_ccy To currency code.
1248 -- p_eq_xlate_mode Equity translation mode (YTD or PTD).
1249 -- p_dataset_code Actual or budget translation.
1250 -- p_org_track_flag Whether org tracking is enabled.
1251 -- p_sec_dim_col Secondary tracking dimension column.
1252 -- p_re_template Retained earnings template.
1253 -- p_source_system_code Source System Code for GCS.
1254 -- p_specific_interco_id Specific intercomany id if applicable.
1255 -- Example
1256 -- GCS_TRANSLATION_PKG.Calculate_RE_Amt;
1257 -- Notes
1258 --
1259 PROCEDURE Calculate_RE_Amt
1260 (p_relationship_id NUMBER,
1261 p_cal_period_id NUMBER,
1262 p_prev_period_id NUMBER,
1263 p_entity_id NUMBER,
1264 p_hierarchy_id NUMBER,
1265 p_ledger_id NUMBER,
1266 p_from_ccy VARCHAR2,
1267 p_to_ccy VARCHAR2,
1268 p_eq_xlate_mode VARCHAR2,
1269 p_hier_dataset_code NUMBER,
1270 p_org_track_flag VARCHAR2,
1271 p_sec_dim_col VARCHAR2,
1272 p_re_template GCS_TEMPLATES_PKG.TemplateRecord,
1273 p_source_system_code NUMBER,
1274 p_specific_interco_id NUMBER) IS
1275
1276 -- Holds a dynamically created cursor for the secondary dimension
1277 -- only or a combination of the secondary and org dimensions
1278 dims_cv refcursor;
1279 dims_cv_text VARCHAR2(32767);
1280
1281 -- Lists all orgs for GCS
1282 CURSOR all_orgs IS
1283 SELECT DISTINCT company_cost_center_org_id org_id
1284 FROM gcs_translation_gt;
1285
1286 org_id NUMBER;
1287 sec_dim_val_id NUMBER;
1288
1289 -- Get the object id for TRANSLATION
1290 CURSOR get_object_id IS
1291 SELECT cb.associated_object_id
1292 FROM gcs_categories_b cb
1293 WHERE cb.category_code = 'TRANSLATION';
1294
1295 fb_object_id NUMBER;
1296
1297 -- Used when secondary tracking is enabled.
1298 re_acct_exists_cv refcursor;
1299 re_acct_text VARCHAR2(32767);
1300
1301 -- Whether or not the retained earning account already exists in the
1302 -- interim table
1303 re_account_exists VARCHAR2(1);
1304
1305 -- Used for getting the aggregate revenues minus expenses for the
1306 -- previous year (P/L)
1307 pl_text VARCHAR2(32767);
1308
1309 -- Used for inserting and updating the retained earnings account
1310 ins_re_text VARCHAR2(32767);
1311 ins_join VARCHAR2(8000);
1312 upd_re_text VARCHAR2(32767);
1313
1314 -- The total P/L for the previous year, to be rolled into this year's
1315 -- retained earnings account
1316 re_delta_dr NUMBER;
1317 re_delta_cr NUMBER;
1318
1319 module VARCHAR2(30);
1320 BEGIN
1321 module := 'CALCULATE_RE_AMT';
1322 module_log_write(module, g_module_enter);
1323
1324 OPEN get_object_id;
1325 FETCH get_object_id INTO fb_object_id;
1326 CLOSE get_object_id;
1327
1328 re_acct_text :=
1329 'SELECT ''Y''' || g_nl ||
1330 'FROM GCS_TRANSLATION_GT' || g_nl;
1331
1332 pl_text :=
1333 'SELECT nvl(sum(nvl(fb.ytd_debit_balance_e, 0)),0), ' ||
1334 'nvl(sum(nvl(fb.ytd_credit_balance_e, 0)),0)' || g_nl ||
1335 'FROM FEM_BALANCES fb,' || g_nl ||
1336 ' FEM_LN_ITEMS_ATTR li,' || g_nl ||
1337 ' FEM_EXT_ACCT_TYPES_ATTR fxata' || g_nl ||
1338 'WHERE fb.dataset_code = ' || p_hier_dataset_code || g_nl ||
1339 'AND fb.created_by_object_id = ' || fb_object_id || g_nl ||
1340 'AND fb.cal_period_id = ' || p_prev_period_id || g_nl ||
1341 'AND fb.source_system_code = ' || p_source_system_code || g_nl ||
1342 'AND fb.currency_code = ''' || p_to_ccy || '''' || g_nl ||
1343 'AND fb.ledger_id = ' || p_ledger_id || g_nl ||
1344 'AND fb.entity_id = ' || p_entity_id || g_nl ||
1345 'AND li.line_item_id = fb.line_item_id' || g_nl ||
1346 'AND li.attribute_id = ' || g_li_acct_type_attr_id || g_nl ||
1347 'AND li.version_id = ' || g_li_acct_type_v_id || g_nl ||
1348 'AND fxata.ext_account_type_code = li.dim_attribute_varchar_member' || g_nl ||
1349 'AND fxata.attribute_id = ' || g_xat_basic_acct_type_attr_id || g_nl ||
1350 'AND fxata.version_id = ' || g_xat_basic_acct_type_v_id || g_nl ||
1351 'AND fxata.dim_attribute_varchar_member IN (''REVENUE'',''EXPENSE'')' || g_nl;
1352
1353 IF p_org_track_flag = 'Y' THEN
1354 pl_text := pl_text || 'AND fb.company_cost_center_org_id = :org_id' || g_nl;
1355 END IF;
1356 IF p_sec_dim_col IS NOT NULL THEN
1357 pl_text := pl_text || 'AND fb.' || p_sec_dim_col || ' = :sec_dim_val_id' || g_nl;
1358 END IF;
1359
1360 ins_re_text :=
1361 'INSERT INTO gcs_translation_gt(translate_rule_code, account_type_code, ' ||
1362 'company_cost_center_org_id, intercompany_id, ' ||
1363 'financial_elem_id, product_id, natural_account_id, channel_id, ' ||
1364 'line_item_id, project_id, customer_id, task_id, user_dim1_id, ' ||
1365 'user_dim2_id, user_dim3_id, user_dim4_id, user_dim5_id, user_dim6_id, ' ||
1366 'user_dim7_id, user_dim8_id, user_dim9_id, user_dim10_id, t_amount_dr, ' ||
1367 't_amount_cr, begin_ytd_dr, begin_ytd_cr, xlate_ptd_dr, xlate_ptd_cr, ' ||
1368 'xlate_ytd_dr, xlate_ytd_cr)' || g_nl ||
1369 'SELECT ''' || p_eq_xlate_mode || ''', ''EQUITY'', ';
1370
1371 upd_re_text :=
1372 'UPDATE GCS_TRANSLATION_GT' || g_nl ||
1373 'SET begin_ytd_dr = nvl(begin_ytd_dr, 0) + :re_delta_dr,' || g_nl ||
1374 ' begin_ytd_cr = nvl(begin_ytd_cr, 0) + :re_delta_cr' || g_nl;
1375
1376 -- Create all the text involving secondary and org tracking, including
1377 -- select portions of statements and joins.
1378 create_all_re_tracking_text(
1379 re_acct_text, ins_re_text, ins_join, upd_re_text, p_org_track_flag,
1380 p_sec_dim_col, p_hierarchy_id, p_entity_id, p_relationship_id,
1381 p_specific_interco_id, p_re_template);
1382
1383 ins_re_text := ins_re_text ||
1384 'decode(''' || p_eq_xlate_mode || ''', ''YTD'', nvl(min(ytd_debit_balance_e),0) + :re_delta_dr, 0), ' ||
1385 'decode(''' || p_eq_xlate_mode || ''', ''YTD'', nvl(min(ytd_credit_balance_e),0) + :re_delta_cr, 0), ' ||
1386 'nvl(min(ytd_debit_balance_e),0) + :re_delta_dr, ' ||
1387 'nvl(min(ytd_credit_balance_e),0) + :re_delta_cr, 0, 0, 0, 0' || g_nl ||
1388 'FROM FEM_BALANCES fb' || g_nl ||
1389 'WHERE fb.dataset_code = ' || p_hier_dataset_code || g_nl ||
1390 'AND fb.created_by_object_id = ' || fb_object_id || g_nl ||
1391 'AND fb.cal_period_id = ' || p_prev_period_id || g_nl ||
1392 'AND fb.source_system_code = ' || p_source_system_code || g_nl ||
1393 'AND fb.currency_code = ''' || p_to_ccy || '''' || g_nl ||
1394 'AND fb.ledger_id = ' || p_ledger_id || g_nl ||
1395 'AND fb.entity_id = ' || p_entity_id || g_nl ||
1396 ins_join;
1397
1398 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
1399 write_to_log(module, FND_LOG.LEVEL_STATEMENT, re_acct_text);
1400 write_to_log(module, FND_LOG.LEVEL_STATEMENT, pl_text);
1401 write_to_log(module, FND_LOG.LEVEL_STATEMENT, ins_re_text);
1402 write_to_log(module, FND_LOG.LEVEL_STATEMENT, upd_re_text);
1403 END IF;
1404
1405
1406 IF p_sec_dim_col IS NOT NULL AND p_org_track_flag = 'Y' THEN
1407 dims_cv_text :=
1408 'SELECT DISTINCT ' || p_sec_dim_col ||
1409 ', COMPANY_COST_CENTER_ORG_ID FROM gcs_translation_gt';
1410
1411 write_to_log(module, FND_LOG.LEVEL_STATEMENT, dims_cv_text);
1412
1413 OPEN dims_cv FOR dims_cv_text;
1414 FETCH dims_cv INTO sec_dim_val_id, org_id;
1415 WHILE dims_cv%FOUND LOOP
1416 -- Check if the given account is already in the interim table.
1417 IF p_specific_interco_id IS NOT NULL THEN
1418 OPEN re_acct_exists_cv FOR re_acct_text
1419 USING org_id, sec_dim_val_id;
1420 ELSE
1421 OPEN re_acct_exists_cv FOR re_acct_text
1422 USING org_id, org_id, sec_dim_val_id;
1423 END IF;
1424 FETCH re_acct_exists_cv INTO re_account_exists;
1425 IF re_acct_exists_cv%NOTFOUND THEN
1426 re_account_exists := 'N';
1427 END IF;
1428 CLOSE re_acct_exists_cv;
1429
1430 EXECUTE IMMEDIATE pl_text
1431 INTO re_delta_dr, re_delta_cr
1432 USING org_id, sec_dim_val_id;
1433
1434 -- Insert or update the retained earnings account in the temporary
1435 -- table.
1436 IF re_account_exists = 'N' THEN
1437 IF p_specific_interco_id IS NOT NULL THEN
1438 EXECUTE IMMEDIATE ins_re_text
1439 USING org_id, sec_dim_val_id, re_delta_dr, re_delta_cr, re_delta_dr, re_delta_cr,
1440 org_id, sec_dim_val_id;
1441 ELSE
1442 EXECUTE IMMEDIATE ins_re_text
1443 USING org_id, org_id, sec_dim_val_id, re_delta_dr, re_delta_cr, re_delta_dr, re_delta_cr,
1444 org_id, org_id, sec_dim_val_id;
1445 END IF;
1446
1447 -- Retained Earnings account was not created successfully.
1448 IF SQL%ROWCOUNT = 0 THEN
1449 CLOSE dims_cv;
1450 raise GCS_CCY_NO_RE_ACCT_CREATED;
1451 END IF;
1452 ELSE
1453 IF p_specific_interco_id IS NOT NULL THEN
1454 EXECUTE IMMEDIATE upd_re_text
1455 USING re_delta_dr, re_delta_cr, org_id, sec_dim_val_id;
1456 ELSE
1457 EXECUTE IMMEDIATE upd_re_text
1458 USING re_delta_dr, re_delta_cr, org_id, org_id, sec_dim_val_id;
1459 END IF;
1460
1461 -- Retained earnings account was not updated successfully.
1462 IF SQL%ROWCOUNT = 0 THEN
1463 CLOSE dims_cv;
1464 raise GCS_CCY_NO_RE_ACCT_UPDATED;
1465 END IF;
1466 END IF;
1467
1468 FETCH dims_cv INTO sec_dim_val_id, org_id;
1469 END LOOP;
1470 CLOSE dims_cv;
1471 ELSIF p_sec_dim_col IS NOT NULL THEN
1472 dims_cv_text := 'SELECT DISTINCT ' || p_sec_dim_col ||
1473 ' FROM gcs_translation_gt';
1474
1475 write_to_log(module, FND_LOG.LEVEL_STATEMENT, dims_cv_text);
1476
1477 OPEN dims_cv FOR dims_cv_text;
1478 FETCH dims_cv INTO sec_dim_val_id;
1479 WHILE dims_cv%FOUND LOOP
1480 -- Check if the given account is already in the interim table.
1481 OPEN re_acct_exists_cv FOR re_acct_text USING sec_dim_val_id;
1482 FETCH re_acct_exists_cv INTO re_account_exists;
1483 IF re_acct_exists_cv%NOTFOUND THEN
1484 re_account_exists := 'N';
1485 END IF;
1486 CLOSE re_acct_exists_cv;
1487
1488 EXECUTE IMMEDIATE pl_text
1489 INTO re_delta_dr, re_delta_cr
1490 USING sec_dim_val_id;
1491
1492 -- Insert or update the retained earnings account in the temporary
1493 -- table.
1494 IF re_account_exists = 'N' THEN
1495 EXECUTE IMMEDIATE ins_re_text
1496 USING sec_dim_val_id, re_delta_dr, re_delta_cr, re_delta_dr, re_delta_cr, sec_dim_val_id;
1497 -- Retained Earnings account was not created successfully.
1498 IF SQL%ROWCOUNT = 0 THEN
1499 CLOSE dims_cv;
1500 raise GCS_CCY_NO_RE_ACCT_CREATED;
1501 END IF;
1502 ELSE
1503 EXECUTE IMMEDIATE upd_re_text
1504 USING re_delta_dr, re_delta_cr, sec_dim_val_id;
1505 -- Retained earnings account was not updated successfully.
1506 IF SQL%ROWCOUNT = 0 THEN
1507 CLOSE dims_cv;
1508 raise GCS_CCY_NO_RE_ACCT_UPDATED;
1509 END IF;
1510 END IF;
1511
1512 FETCH dims_cv INTO sec_dim_val_id;
1513 END LOOP;
1514 CLOSE dims_cv;
1515 ELSIF p_org_track_flag = 'Y' THEN
1516 write_to_log(module, FND_LOG.LEVEL_STATEMENT,
1517 'SELECT DISTINCT company_cost_center_org_id org_id FROM gcs_translation_gt');
1518
1519 FOR org IN all_orgs LOOP
1520 org_id := org.org_id;
1521
1522 -- Check if the given account is already in the interim table.
1523 IF p_specific_interco_id IS NOT NULL THEN
1524 OPEN re_acct_exists_cv FOR re_acct_text USING org_id;
1525 ELSE
1526 OPEN re_acct_exists_cv FOR re_acct_text USING org_id, org_id;
1527 END IF;
1528 FETCH re_acct_exists_cv INTO re_account_exists;
1529 IF re_acct_exists_cv%NOTFOUND THEN
1530 re_account_exists := 'N';
1531 END IF;
1532 CLOSE re_acct_exists_cv;
1533
1534 EXECUTE IMMEDIATE pl_text
1535 INTO re_delta_dr, re_delta_cr
1536 USING org_id;
1537
1538 -- Insert or update the retained earnings account in the temporary
1539 -- table.
1540 IF re_account_exists = 'N' THEN
1541 IF p_specific_interco_id IS NOT NULL THEN
1542 EXECUTE IMMEDIATE ins_re_text
1543 USING org_id, re_delta_dr, re_delta_cr, re_delta_dr, re_delta_cr, org_id;
1544 ELSE
1545 EXECUTE IMMEDIATE ins_re_text
1546 USING org_id, org_id, re_delta_dr, re_delta_cr, re_delta_dr, re_delta_cr, org_id, org_id;
1547 END IF;
1548
1549 -- Retained Earnings account was not created successfully.
1550 IF SQL%ROWCOUNT = 0 THEN
1551 raise GCS_CCY_NO_RE_ACCT_CREATED;
1552 END IF;
1553 ELSE
1554 IF p_specific_interco_id IS NOT NULL THEN
1555 EXECUTE IMMEDIATE upd_re_text
1556 USING re_delta_dr, re_delta_cr, org_id;
1557 ELSE
1558 EXECUTE IMMEDIATE upd_re_text
1559 USING re_delta_dr, re_delta_cr, org_id, org_id;
1560 END IF;
1561 -- Retained earnings account was not updated successfully.
1562 IF SQL%ROWCOUNT = 0 THEN
1563 raise GCS_CCY_NO_RE_ACCT_UPDATED;
1564 END IF;
1565 END IF;
1566
1567 END LOOP;
1568 ELSE
1569 -- Check if the given account is already in the interim table.
1570 OPEN re_acct_exists_cv FOR re_acct_text;
1571 FETCH re_acct_exists_cv INTO re_account_exists;
1572 IF re_acct_exists_cv%NOTFOUND THEN
1573 re_account_exists := 'N';
1574 END IF;
1575 CLOSE re_acct_exists_cv;
1576
1577 EXECUTE IMMEDIATE pl_text
1578 INTO re_delta_dr, re_delta_cr;
1579
1580 -- Insert or update the retained earnings account in the temporary
1581 -- table.
1582 IF re_account_exists = 'N' THEN
1583 EXECUTE IMMEDIATE ins_re_text USING re_delta_dr, re_delta_cr, re_delta_dr, re_delta_cr;
1584 -- Retained Earnings account was not created successfully.
1585 IF SQL%ROWCOUNT = 0 THEN
1586 raise GCS_CCY_NO_RE_ACCT_CREATED;
1587 END IF;
1588 ELSE
1589 EXECUTE IMMEDIATE upd_re_text USING re_delta_dr, re_delta_cr;
1590 -- Retained earnings account was not updated successfully.
1591 IF SQL%ROWCOUNT = 0 THEN
1592 raise GCS_CCY_NO_RE_ACCT_UPDATED;
1593 END IF;
1594 END IF;
1595 END IF;
1596
1597 write_to_log(module, FND_LOG.LEVEL_STATEMENT,
1598 'UPDATE GCS_TRANSLATION_GT tg' || g_nl ||
1599 'SET begin_ytd_dr = 0,' || g_nl ||
1600 ' begin_ytd_cr = 0' || g_nl ||
1601 'WHERE EXISTS' || g_nl ||
1602 '(SELECT 1' || g_nl ||
1603 ' FROM FEM_LN_ITEMS_ATTR li,' || g_nl ||
1604 ' FEM_EXT_ACCT_TYPES_ATTR fxata' || g_nl ||
1605 ' WHERE li.line_item_id = tg.line_item_id' || g_nl ||
1606 ' AND li.attribute_id = ' || g_li_acct_type_attr_id || g_nl ||
1607 ' AND li.version_id = ' || g_li_acct_type_v_id || g_nl ||
1608 ' AND fxata.ext_account_type_code = li.dim_attribute_varchar_member' || g_nl ||
1609 ' AND fxata.attribute_id = ' || g_xat_basic_acct_type_attr_id || g_nl ||
1610 ' AND fxata.version_id = ' || g_xat_basic_acct_type_v_id || g_nl ||
1611 ' AND fxata.dim_attribute_varchar_member IN (''REVENUE'',''EXPENSE''))');
1612
1613 -- Zero out the Income Statement begin balances, since we moved
1614 -- those balances over to the retained earnings account.
1615 UPDATE GCS_TRANSLATION_GT tg
1616 SET begin_ytd_dr = 0,
1617 begin_ytd_cr = 0
1618 WHERE EXISTS
1619 (SELECT 1
1620 FROM FEM_LN_ITEMS_ATTR li,
1621 FEM_EXT_ACCT_TYPES_ATTR fxata
1622 WHERE li.line_item_id = tg.line_item_id
1623 AND li.attribute_id = g_li_acct_type_attr_id
1624 AND li.version_id = g_li_acct_type_v_id
1625 AND fxata.ext_account_type_code = li.dim_attribute_varchar_member
1626 AND fxata.attribute_id = g_xat_basic_acct_type_attr_id
1627 AND fxata.version_id = g_xat_basic_acct_type_v_id
1628 AND fxata.dim_attribute_varchar_member IN ('REVENUE','EXPENSE'));
1629
1630 module_log_write(module, g_module_success);
1631 EXCEPTION
1632 WHEN GCS_CCY_SUBPROGRAM_RAISED THEN
1633 module_log_write(module, g_module_failure);
1634 raise GCS_CCY_SUBPROGRAM_RAISED;
1635 WHEN GCS_CCY_NO_RE_ACCT_CREATED THEN
1636 FND_MESSAGE.set_name('GCS', 'GCS_CCY_NO_RE_CREATED_ERR');
1637 g_error_text := FND_MESSAGE.get;
1638 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
1639 module_log_write(module, g_module_failure);
1640 raise GCS_CCY_SUBPROGRAM_RAISED;
1641 WHEN GCS_CCY_NO_RE_ACCT_UPDATED THEN
1642 FND_MESSAGE.set_name('GCS', 'GCS_CCY_NO_RE_UPDATED_ERR');
1643 g_error_text := FND_MESSAGE.get;
1644 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
1645 module_log_write(module, g_module_failure);
1646 raise GCS_CCY_SUBPROGRAM_RAISED;
1647 WHEN OTHERS THEN
1648 FND_MESSAGE.set_name('GCS', 'GCS_CCY_RE_UNEXPECTED_ERR');
1649 g_error_text := FND_MESSAGE.get;
1650 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
1651 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, SQLERRM);
1652 module_log_write(module, g_module_failure);
1653 raise GCS_CCY_SUBPROGRAM_RAISED;
1654 END Calculate_RE_Amt;
1655
1656 --
1657 -- Procedure
1658 -- Calculate_RE_Rate
1659 -- Purpose
1660 -- Calculate historical rates for the retained earnings account.
1661 -- Arguments
1662 -- p_relationship_id The relationship for which translation is being run.
1663 -- p_cal_period_id The period being translated.
1664 -- p_next_period_id The next period, which is the first period of the year.
1665 -- p_entity_id Entity on which the translation is being performed.
1666 -- p_hierarchy_id Hierarchy in which the entity resides.
1667 -- p_ledger_id Ledger of the entity to be translated.
1668 -- p_from_ccy From currency code.
1669 -- p_to_ccy To currency code.
1670 -- p_dataset_code Actual or budget translation.
1671 -- p_org_track_flag Whether org tracking is enabled.
1672 -- p_sec_dim_col Secondary tracking dimension column.
1673 -- p_re_template Retained earnings template.
1674 -- p_source_system_code Source System Code for GCS.
1675 -- p_specific_interco_id Specific intercomany id if applicable.
1676 -- p_entry_id The translation entry created.
1677 -- Example
1678 -- GCS_TRANSLATION_PKG.Calculate_RE_Rate;
1679 -- Notes
1680 --
1681 PROCEDURE Calculate_RE_Rate
1682 (p_relationship_id NUMBER,
1683 p_cal_period_id NUMBER,
1684 p_next_period_id NUMBER,
1685 p_entity_id NUMBER,
1686 p_hierarchy_id NUMBER,
1687 p_ledger_id NUMBER,
1688 p_from_ccy VARCHAR2,
1689 p_to_ccy VARCHAR2,
1690 p_hier_dataset_code NUMBER,
1691 p_org_track_flag VARCHAR2,
1692 p_sec_dim_col VARCHAR2,
1693 p_re_template GCS_TEMPLATES_PKG.TemplateRecord,
1694 p_source_system_code NUMBER,
1695 p_specific_interco_id NUMBER,
1696 p_entry_id NUMBER) IS
1697
1698 -- Holds a dynamically created cursor for the secondary dimension
1699 -- only or a combination of the secondary and org dimensions
1700 dims_cv refcursor;
1701 dims_cv_text VARCHAR2(32767);
1702
1703 -- Lists all orgs for GCS
1704 CURSOR all_orgs IS
1705 SELECT DISTINCT company_cost_center_org_id org_id
1706 FROM gcs_entry_lines
1707 WHERE entry_id = p_entry_id;
1708
1709 org_id NUMBER;
1710 sec_dim_val_id NUMBER;
1711
1712 -- Used for getting the calculated_historical rate
1713 amounts_text VARCHAR2(32767);
1714 amounts_join VARCHAR2(8000);
1715
1716 new_rate NUMBER;
1717
1718 -- Used to check whether a historical rate is defined, and returns the
1719 -- rate type if such a rate is defined.
1720 re_rate_exists_cv refcursor;
1721 re_rate_text VARCHAR2(32767);
1722
1723 -- SQL to insert or update the new retained earnings historical rate.
1724 re_ins_rate VARCHAR2(32767);
1725 re_upd_rate VARCHAR2(32767);
1726
1727 -- The historical rate type for the retained earnings account
1728 re_rate_type VARCHAR2(30);
1729
1730 module VARCHAR2(30);
1731 BEGIN
1732 module := 'CALCULATE_RE_RATE';
1733 module_log_write(module, g_module_enter);
1734
1735 amounts_text :=
1736 'SELECT decode(func.ytd_debit - func.ytd_credit,' || g_nl ||
1737 ' 0, decode(func.ytd_debit,' || g_nl ||
1738 ' 0, 0,' || g_nl ||
1739 ' xlat.ytd_debit/func.ytd_debit),' || g_nl ||
1740 ' (xlat.ytd_debit - xlat.ytd_credit)/(func.ytd_debit - func.ytd_credit))' || g_nl ||
1741 'FROM' || g_nl ||
1742 ' (SELECT nvl(sum(nvl(fb.ytd_debit_balance_e, 0)),0) ytd_debit, ' ||
1743 'nvl(sum(nvl(fb.ytd_credit_balance_e, 0)),0) ytd_credit' || g_nl ||
1744 ' FROM FEM_BALANCES fb,' || g_nl ||
1745 ' FEM_LN_ITEMS_ATTR li,' || g_nl ||
1746 ' FEM_EXT_ACCT_TYPES_ATTR fxata' || g_nl ||
1747 ' WHERE fb.dataset_code = ' || p_hier_dataset_code || g_nl ||
1748 ' AND fb.cal_period_id = ' || p_cal_period_id || g_nl ||
1749 ' AND fb.source_system_code = ' || p_source_system_code || g_nl ||
1750 ' AND fb.currency_code = ''' || p_from_ccy || '''' || g_nl ||
1751 ' AND fb.ledger_id = ' || p_ledger_id || g_nl ||
1752 ' AND fb.entity_id = ' || p_entity_id || g_nl ||
1753 ' AND li.line_item_id = fb.line_item_id' || g_nl ||
1754 ' AND li.attribute_id = ' || g_li_acct_type_attr_id || g_nl ||
1755 ' AND li.version_id = ' || g_li_acct_type_v_id || g_nl ||
1756 ' AND fxata.ext_account_type_code = li.dim_attribute_varchar_member' || g_nl ||
1757 ' AND fxata.attribute_id = ' || g_xat_basic_acct_type_attr_id || g_nl ||
1758 ' AND fxata.version_id = ' || g_xat_basic_acct_type_v_id || g_nl ||
1759 ' AND ((fxata.dim_attribute_varchar_member IN (''REVENUE'',''EXPENSE'')' || g_nl;
1760
1761 IF p_org_track_flag = 'Y' THEN
1762 amounts_text := amounts_text || ' AND fb.company_cost_center_org_id = :org_id' || g_nl;
1763 END IF;
1764 IF p_sec_dim_col IS NOT NULL THEN
1765 amounts_text := amounts_text || ' AND fb.' || p_sec_dim_col || ' = :sec_dim_val_id' || g_nl;
1766 END IF;
1767
1768 amounts_text := amounts_text ||
1769 ' ) OR ' || g_nl ||
1770 ' (' || g_nl;
1771
1772 re_rate_text :=
1773 'SELECT rate_type_code' || g_nl ||
1774 'FROM GCS_HISTORICAL_RATES' || g_nl ||
1775 'WHERE entity_id = ' || p_entity_id || g_nl ||
1776 'AND hierarchy_id = ' || p_hierarchy_id || g_nl ||
1777 'AND cal_period_id = ' || p_next_period_id || g_nl ||
1778 'AND from_currency = ''' ||p_from_ccy || '''' || g_nl ||
1779 'AND to_currency = ''' || p_to_ccy || '''' || g_nl;
1780
1781 re_ins_rate :=
1782 'INSERT INTO gcs_historical_rates(standard_re_rate_flag, entity_id, hierarchy_id, ' ||
1783 'cal_period_id, from_currency, to_currency, company_cost_center_org_id, ' ||
1784 'intercompany_id, financial_elem_id, ' ||
1785 'product_id, natural_account_id, channel_id, line_item_id, project_id, ' ||
1786 'customer_id, task_id, user_dim1_id, user_dim2_id, user_dim3_id, ' ||
1787 'user_dim4_id, user_dim5_id, user_dim6_id, user_dim7_id, user_dim8_id, ' ||
1788 'user_dim9_id, user_dim10_id, translated_rate, translated_amount, ' ||
1789 'rate_type_code, update_flag, account_type_code, stop_rollforward_flag, ' ||
1790 'last_update_date, last_updated_by, last_update_login, creation_date, ' ||
1791 'created_by)' || g_nl ||
1792 'VALUES(''Y'', ' || p_entity_id || ', ' || p_hierarchy_id ||
1793 ', ' || p_next_period_id || ', ''' || p_from_ccy || ''', ''' || p_to_ccy ||
1794 ''', ';
1795
1796 re_upd_rate :=
1797 'UPDATE GCS_HISTORICAL_RATES' || g_nl ||
1798 'SET translated_rate = :re_rate,' || g_nl ||
1799 ' translated_amount = null,' || g_nl ||
1800 ' rate_type_code = ''C'',' || g_nl ||
1801 ' last_update_date = sysdate,' || g_nl ||
1802 ' last_updated_by = ' || g_fnd_user_id || ',' || g_nl ||
1803 ' last_update_login = ' || g_fnd_login_id || g_nl ||
1804 'WHERE entity_id = ' || p_entity_id || g_nl ||
1805 'AND hierarchy_id = ' || p_hierarchy_id || g_nl ||
1806 'AND from_currency = ''' || p_from_ccy || '''' || g_nl ||
1807 'AND to_currency = ''' || p_to_ccy || '''' || g_nl ||
1808 'AND cal_period_id = ' || p_next_period_id || g_nl;
1809
1810 -- Create all the text involving secondary and org tracking, including
1811 -- select portions of statements and joins.
1812 create_all_re_rate_track_text(
1813 amounts_text, amounts_join, re_rate_text, re_ins_rate, re_upd_rate,
1814 p_org_track_flag, p_sec_dim_col, p_hierarchy_id, p_entity_id,
1815 p_relationship_id, p_specific_interco_id, p_re_template);
1816
1817
1818 amounts_text := amounts_text ||
1819 ' ))' || g_nl ||
1820 ' ) func,' || g_nl ||
1821 ' (SELECT nvl(sum(nvl(el.ytd_debit_balance_e, 0)), 0) ytd_debit, ' ||
1822 'nvl(sum(nvl(el.ytd_credit_balance_e, 0)), 0) ytd_credit' || g_nl ||
1823 ' FROM GCS_ENTRY_LINES el,' || g_nl ||
1824 ' FEM_LN_ITEMS_ATTR lia,' || g_nl ||
1825 ' FEM_EXT_ACCT_TYPES_ATTR xata' || g_nl ||
1826 ' WHERE el.entry_id = ' || p_entry_id || g_nl ||
1827 ' AND lia.line_item_id = el.line_item_id' || g_nl ||
1828 ' AND lia.attribute_id = ' || g_li_acct_type_attr_id || g_nl ||
1829 ' AND lia.version_id = ' || g_li_acct_type_v_id || g_nl ||
1830 ' AND xata.ext_account_type_code = lia.dim_attribute_varchar_member' || g_nl ||
1831 ' AND xata.attribute_id = ' || g_xat_basic_acct_type_attr_id || g_nl ||
1832 ' AND xata.version_id = ' || g_xat_basic_acct_type_v_id || g_nl ||
1833 ' AND ((xata.dim_attribute_varchar_member IN (''REVENUE'', ''EXPENSE'')' || g_nl;
1834
1835 IF p_org_track_flag = 'Y' THEN
1836 amounts_text := amounts_text || ' AND el.company_cost_center_org_id = :org_id' || g_nl;
1837 END IF;
1838 IF p_sec_dim_col IS NOT NULL THEN
1839 amounts_text := amounts_text || ' AND el.' || p_sec_dim_col || ' = :sec_dim_val_id' || g_nl;
1840 END IF;
1841
1842 amounts_text := amounts_text ||
1843 ' ) OR' || g_nl ||
1844 ' (' || g_nl ||
1845 amounts_join ||
1846 ' ))' || g_nl ||
1847 ' ) xlat';
1848
1849
1850 re_ins_rate := re_ins_rate ||
1851 ':re_rate, null, ''C'', ''N'', ''EQUITY'', ''N'', sysdate, ' || g_fnd_user_id ||
1852 ', ' || g_fnd_login_id || ', sysdate, ' || g_fnd_user_id || ')';
1853
1854 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
1855 write_to_log(module, FND_LOG.LEVEL_STATEMENT, amounts_text);
1856 write_to_log(module, FND_LOG.LEVEL_STATEMENT, re_rate_text);
1857 write_to_log(module, FND_LOG.LEVEL_STATEMENT, re_ins_rate);
1858 write_to_log(module, FND_LOG.LEVEL_STATEMENT, re_upd_rate);
1859 END IF;
1860
1861
1862 IF p_sec_dim_col IS NOT NULL AND p_org_track_flag = 'Y' THEN
1863 dims_cv_text :=
1864 'SELECT DISTINCT ' || p_sec_dim_col ||
1865 ', COMPANY_COST_CENTER_ORG_ID FROM gcs_entry_lines where entry_id = ' || p_entry_id;
1866
1867 write_to_log(module, FND_LOG.LEVEL_STATEMENT, dims_cv_text);
1868
1869 OPEN dims_cv FOR dims_cv_text;
1870 FETCH dims_cv INTO sec_dim_val_id, org_id;
1871 WHILE dims_cv%FOUND LOOP
1872
1873 IF p_specific_interco_id IS NOT NULL THEN
1874 EXECUTE IMMEDIATE amounts_text
1875 INTO new_rate
1876 USING org_id, sec_dim_val_id, org_id, sec_dim_val_id,
1877 org_id, sec_dim_val_id, org_id, sec_dim_val_id;
1878
1879 OPEN re_rate_exists_cv FOR re_rate_text
1880 USING org_id, sec_dim_val_id;
1881 ELSE
1882 EXECUTE IMMEDIATE amounts_text
1883 INTO new_rate
1884 USING org_id, sec_dim_val_id, org_id, org_id, sec_dim_val_id,
1885 org_id, sec_dim_val_id, org_id, org_id, sec_dim_val_id;
1886
1887 OPEN re_rate_exists_cv FOR re_rate_text
1888 USING org_id, org_id, sec_dim_val_id;
1889 END IF;
1890 FETCH re_rate_exists_cv INTO re_rate_type;
1891 IF re_rate_exists_cv%NOTFOUND THEN
1892 CLOSE re_rate_exists_cv;
1893 IF p_specific_interco_id IS NOT NULL THEN
1894 EXECUTE IMMEDIATE re_ins_rate
1895 USING org_id, sec_dim_val_id, new_rate;
1896 ELSE
1897 EXECUTE IMMEDIATE re_ins_rate
1898 USING org_id, org_id, sec_dim_val_id, new_rate;
1899 END IF;
1900
1901 -- Retained earnings historical rate not created successfully.
1902 IF SQL%ROWCOUNT = 0 THEN
1903 raise GCS_CCY_NO_RATE_CREATED;
1904 END IF;
1905 ELSE
1906 CLOSE re_rate_exists_cv;
1907 IF nvl(re_rate_type, 'X') <> 'H' THEN
1908 IF p_specific_interco_id IS NOT NULL THEN
1909 EXECUTE IMMEDIATE re_upd_rate
1910 USING new_rate, org_id, sec_dim_val_id;
1911 ELSE
1912 EXECUTE IMMEDIATE re_upd_rate
1913 USING new_rate, org_id, org_id, sec_dim_val_id;
1914 END IF;
1915
1916 -- Retained earnings historical rate not updated successfully.
1917 IF SQL%ROWCOUNT = 0 THEN
1918 raise GCS_CCY_NO_RATE_UPDATED;
1919 END IF;
1920 END IF;
1921 END IF;
1922
1923 FETCH dims_cv INTO sec_dim_val_id, org_id;
1924 END LOOP;
1925 CLOSE dims_cv;
1926 ELSIF p_sec_dim_col IS NOT NULL THEN
1927 dims_cv_text := 'SELECT DISTINCT ' || p_sec_dim_col ||
1928 ' FROM gcs_entry_lines WHERE entry_id = ' || p_entry_id;
1929
1930 write_to_log(module, FND_LOG.LEVEL_STATEMENT, dims_cv_text);
1931
1932 OPEN dims_cv FOR dims_cv_text;
1933 FETCH dims_cv INTO sec_dim_val_id;
1934 WHILE dims_cv%FOUND LOOP
1935
1936 EXECUTE IMMEDIATE amounts_text
1937 INTO new_rate
1938 USING sec_dim_val_id, sec_dim_val_id,
1939 sec_dim_val_id, sec_dim_val_id;
1940
1941 OPEN re_rate_exists_cv FOR re_rate_text USING sec_dim_val_id;
1942 FETCH re_rate_exists_cv INTO re_rate_type;
1943 IF re_rate_exists_cv%NOTFOUND THEN
1944 CLOSE re_rate_exists_cv;
1945 EXECUTE IMMEDIATE re_ins_rate USING sec_dim_val_id, new_rate;
1946
1947 -- Retained earnings historical rate not created successfully.
1948 IF SQL%ROWCOUNT = 0 THEN
1949 raise GCS_CCY_NO_RATE_CREATED;
1950 END IF;
1951 ELSE
1952 CLOSE re_rate_exists_cv;
1953 IF nvl(re_rate_type, 'X') <> 'H' THEN
1954 EXECUTE IMMEDIATE re_upd_rate
1955 USING new_rate, sec_dim_val_id;
1956
1957 -- Retained earnings historical rate not updated successfully.
1958 IF SQL%ROWCOUNT = 0 THEN
1959 raise GCS_CCY_NO_RATE_UPDATED;
1960 END IF;
1961 END IF;
1962 END IF;
1963
1964 FETCH dims_cv INTO sec_dim_val_id;
1965 END LOOP;
1966 CLOSE dims_cv;
1967 ELSIF p_org_track_flag = 'Y' THEN
1968 write_to_log(module, FND_LOG.LEVEL_STATEMENT,
1969 'SELECT DISTINCT company_cost_center_org_id org_id FROM gcs_entry_lines WHERE entry_id = ' || p_entry_id);
1970
1971 FOR org IN all_orgs LOOP
1972 org_id := org.org_id;
1973
1974 IF p_specific_interco_id IS NOT NULL THEN
1975 EXECUTE IMMEDIATE amounts_text
1976 INTO new_rate
1977 USING org_id, org_id, org_id, org_id;
1978
1979 OPEN re_rate_exists_cv FOR re_rate_text USING org_id;
1980 ELSE
1981 EXECUTE IMMEDIATE amounts_text
1982 INTO new_rate
1983 USING org_id, org_id, org_id, org_id, org_id, org_id;
1984
1985 OPEN re_rate_exists_cv FOR re_rate_text USING org_id, org_id;
1986 END IF;
1987 FETCH re_rate_exists_cv INTO re_rate_type;
1988 IF re_rate_exists_cv%NOTFOUND THEN
1989 CLOSE re_rate_exists_cv;
1990 IF p_specific_interco_id IS NOT NULL THEN
1991 EXECUTE IMMEDIATE re_ins_rate
1992 USING org_id, new_rate;
1993 ELSE
1994 EXECUTE IMMEDIATE re_ins_rate
1995 USING org_id, org_id, new_rate;
1996 END IF;
1997
1998 -- Retained earnings historical rate not created successfully.
1999 IF SQL%ROWCOUNT = 0 THEN
2000 raise GCS_CCY_NO_RATE_CREATED;
2001 END IF;
2002 ELSE
2003 CLOSE re_rate_exists_cv;
2004 IF nvl(re_rate_type, 'X') <> 'H' THEN
2005 IF p_specific_interco_id IS NOT NULL THEN
2006 EXECUTE IMMEDIATE re_upd_rate
2007 USING new_rate, org_id;
2008 ELSE
2009 EXECUTE IMMEDIATE re_upd_rate
2010 USING new_rate, org_id, org_id;
2011 END IF;
2012
2013 -- Retained earnings historical rate not updated successfully.
2014 IF SQL%ROWCOUNT = 0 THEN
2015 raise GCS_CCY_NO_RATE_UPDATED;
2016 END IF;
2017 END IF;
2018 END IF;
2019
2020 END LOOP;
2021 ELSE
2022
2023 EXECUTE IMMEDIATE amounts_text
2024 INTO new_rate;
2025
2026 OPEN re_rate_exists_cv FOR re_rate_text;
2027 FETCH re_rate_exists_cv INTO re_rate_type;
2028 IF re_rate_exists_cv%NOTFOUND THEN
2029 CLOSE re_rate_exists_cv;
2030 EXECUTE IMMEDIATE re_ins_rate USING new_rate;
2031
2032 -- Retained earnings historical rate not created successfully.
2033 IF SQL%ROWCOUNT = 0 THEN
2034 raise GCS_CCY_NO_RATE_CREATED;
2035 END IF;
2036 ELSE
2037 CLOSE re_rate_exists_cv;
2038 IF nvl(re_rate_type, 'X') <> 'H' THEN
2039 EXECUTE IMMEDIATE re_upd_rate USING new_rate;
2040
2041 -- Retained earnings historical rate not updated successfully.
2042 IF SQL%ROWCOUNT = 0 THEN
2043 raise GCS_CCY_NO_RATE_UPDATED;
2044 END IF;
2045 END IF;
2046 END IF;
2047 END IF;
2048
2049 module_log_write(module, g_module_success);
2050 EXCEPTION
2051 WHEN GCS_CCY_SUBPROGRAM_RAISED THEN
2052 module_log_write(module, g_module_failure);
2053 raise GCS_CCY_SUBPROGRAM_RAISED;
2054 WHEN GCS_CCY_NO_RATE_CREATED THEN
2055 FND_MESSAGE.set_name('GCS', 'GCS_CCY_NO_RE_RT_CREATED_ERR');
2056 g_error_text := FND_MESSAGE.get;
2057 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
2058 module_log_write(module, g_module_failure);
2059 raise GCS_CCY_SUBPROGRAM_RAISED;
2060 WHEN GCS_CCY_NO_RATE_UPDATED THEN
2061 FND_MESSAGE.set_name('GCS', 'GCS_CCY_NO_RE_RT_UPDATED_ERR');
2062 g_error_text := FND_MESSAGE.get;
2063 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
2064 module_log_write(module, g_module_failure);
2065 raise GCS_CCY_SUBPROGRAM_RAISED;
2066 WHEN OTHERS THEN
2067 FND_MESSAGE.set_name('GCS', 'GCS_CCY_RE_UNEXPECTED_ERR');
2068 g_error_text := FND_MESSAGE.get;
2069 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
2070 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, SQLERRM);
2071 module_log_write(module, g_module_failure);
2072 raise GCS_CCY_SUBPROGRAM_RAISED;
2073 END Calculate_RE_Rate;
2074
2075 --
2076 -- Procedure
2077 -- Calculate_Xlated_PTD_YTD
2078 -- Purpose
2079 -- Calculate the PTD or YTD balances for YTD or PTD translations,
2080 -- respectively.
2081 -- Example
2082 -- GCS_TRANSLATION_PKG.Calculate_Xlated_PTD_YTD;
2083 -- Notes
2084 --
2085 PROCEDURE Calculate_Xlated_PTD_YTD IS
2086 module VARCHAR2(30);
2087 BEGIN
2088 module := 'CALCULATE_XLATED_PTD_YTD';
2089 module_log_write(module, g_module_enter);
2090
2091 write_to_log(module, FND_LOG.LEVEL_STATEMENT,
2092 'UPDATE gcs_translation_gt' || g_nl ||
2093 'SET xlate_ptd_dr = t_amount_dr,' || g_nl ||
2094 ' xlate_ptd_cr = t_amount_cr,' || g_nl ||
2095 ' xlate_ytd_dr = t_amount_dr + begin_ytd_dr,' || g_nl ||
2096 ' xlate_ytd_cr = t_amount_cr + begin_ytd_cr' || g_nl ||
2097 'WHERE translate_rule_code = ''PTD''');
2098
2099 -- Calculate YTD balances for PTD translation
2100 UPDATE gcs_translation_gt
2101 SET xlate_ptd_dr = t_amount_dr,
2102 xlate_ptd_cr = t_amount_cr,
2103 xlate_ytd_dr = t_amount_dr + begin_ytd_dr,
2104 xlate_ytd_cr = t_amount_cr + begin_ytd_cr
2105 WHERE translate_rule_code = 'PTD';
2106
2107 write_to_log(module, FND_LOG.LEVEL_STATEMENT,
2108 'UPDATE gcs_translation_gt' || g_nl ||
2109 'SET xlate_ptd_dr = t_amount_dr - begin_ytd_dr,' || g_nl ||
2110 ' xlate_ptd_cr = t_amount_cr - begin_ytd_cr,' || g_nl ||
2111 ' xlate_ytd_dr = t_amount_dr,' || g_nl ||
2112 ' xlate_ytd_cr = t_amount_cr' || g_nl ||
2113 'WHERE translate_rule_code = ''YTD''');
2114
2115 -- Calculate PTD balances for YTD translation
2116 UPDATE gcs_translation_gt
2117 SET xlate_ptd_dr = t_amount_dr - begin_ytd_dr,
2118 xlate_ptd_cr = t_amount_cr - begin_ytd_cr,
2119 xlate_ytd_dr = t_amount_dr,
2120 xlate_ytd_cr = t_amount_cr
2121 WHERE translate_rule_code = 'YTD';
2122
2123 module_log_write(module, g_module_success);
2124 EXCEPTION
2125 WHEN GCS_CCY_SUBPROGRAM_RAISED THEN
2126 module_log_write(module, g_module_failure);
2127 raise GCS_CCY_SUBPROGRAM_RAISED;
2128 WHEN OTHERS THEN
2129 FND_MESSAGE.set_name('GCS', 'GCS_CCY_PTDYTD_UNEXPECTED_ERR');
2130 g_error_text := FND_MESSAGE.get;
2131 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
2132 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, SQLERRM);
2133 module_log_write(module, g_module_failure);
2134 raise GCS_CCY_SUBPROGRAM_RAISED;
2135 END Calculate_Xlated_PTD_YTD;
2136
2137
2138
2139 PROCEDURE Update_Status_Tracking(
2140 p_hierarchy_id IN NUMBER,
2141 p_entity_id IN NUMBER,
2142 p_currency_code IN VARCHAR2,
2143 p_cal_period_id IN NUMBER,
2144 p_hier_dataset_code IN NUMBER,
2145 p_cal_period_number IN NUMBER,
2146 p_cal_period_year IN NUMBER,
2147 p_next_cal_period_id IN NUMBER) IS
2148 module VARCHAR2(30);
2149
2150 -- Check the status of a translation
2151 CURSOR status_check_c(c_cal_period_id NUMBER) IS
2152 SELECT status_code
2153 FROM gcs_translation_statuses
2154 WHERE hierarchy_id = p_hierarchy_id
2155 AND entity_id = p_entity_id
2156 AND currency_code = p_currency_code
2157 AND cal_period_id = c_cal_period_id
2158 AND dataset_code = p_hier_dataset_code;
2159
2160 status VARCHAR2(30);
2161
2162 -- Check the translation tracking information for an entity
2163 CURSOR tracking_check_c IS
2164 SELECT earliest_ever_period_id,
2165 earliest_never_period_id
2166 FROM gcs_translation_track_h
2167 WHERE hierarchy_id = p_hierarchy_id
2168 AND entity_id = p_entity_id
2169 AND currency_code = p_currency_code
2170 AND dataset_code = p_hier_dataset_code;
2171
2172 earliest_period_id NUMBER;
2173 never_period_id NUMBER;
2174 earliest_period_info GCS_UTILITY_PKG.r_cal_period_info;
2175 never_period_info GCS_UTILITY_PKG.r_cal_period_info;
2176
2177 next_period_id NUMBER;
2178 next_period_info GCS_UTILITY_PKG.r_cal_period_info;
2179
2180 BEGIN
2181 module := 'UPDATE_STATUS_TRACKING';
2182 module_log_write(module, g_module_enter);
2183
2184 -- If a status row does not exist, create one. If it does exist, make sure
2185 -- it is set to the 'Current' status
2186 OPEN status_check_c(p_cal_period_id);
2187 FETCH status_check_c INTO status;
2188 IF status_check_c%NOTFOUND THEN
2189 CLOSE status_check_c;
2190 INSERT INTO gcs_translation_statuses(
2191 hierarchy_id, entity_id, currency_code, cal_period_id, dataset_code,
2192 status_code, request_id, creation_date, created_by, last_update_date,
2193 last_updated_by, last_update_login)
2194 VALUES(
2195 p_hierarchy_id, p_entity_id, p_currency_code, p_cal_period_id,
2196 p_hier_dataset_code, 'C', '', sysdate, g_fnd_user_id, sysdate,
2197 g_fnd_user_id, g_fnd_login_id);
2198 ELSE
2199 CLOSE status_check_c;
2200 IF status <> 'C' THEN
2201 UPDATE gcs_translation_statuses
2202 SET status_code = 'C'
2203 WHERE hierarchy_id = p_hierarchy_id
2204 AND entity_id = p_entity_id
2205 AND currency_code = p_currency_code
2206 AND cal_period_id = p_cal_period_id
2207 AND dataset_code = p_hier_dataset_code;
2208 END IF;
2209 END IF;
2210
2211 -- If a tracking row does not exist, create one. If it does exist, make
2212 -- sure the latest translated period is included in the range
2213 OPEN tracking_check_c;
2214 FETCH tracking_check_c INTO earliest_period_id, never_period_id;
2215 IF tracking_check_c%NOTFOUND THEN
2216 CLOSE tracking_check_c;
2217 INSERT INTO gcs_translation_track_h(
2218 hierarchy_id, entity_id, currency_code, dataset_code,
2219 earliest_ever_period_id, earliest_never_period_id, created_by,
2220 creation_date, last_updated_by, last_update_date, last_update_login)
2221 VALUES(
2222 p_hierarchy_id, p_entity_id, p_currency_code, p_hier_dataset_code,
2223 p_cal_period_id, p_cal_period_id, g_fnd_user_id, sysdate,
2224 g_fnd_user_id, sysdate, g_fnd_login_id);
2225 ELSE
2226 CLOSE tracking_check_c;
2227 GCS_UTILITY_PKG.get_cal_period_details
2228 (earliest_period_id, earliest_period_info);
2229 GCS_UTILITY_PKG.get_cal_period_details
2230 (never_period_id, never_period_info);
2231
2232 -- If this is prior to the first ever translated period, update the
2233 -- tracking table accordingly. Otherwise, move the earliest_never_period
2234 -- forward to the appropriate spot.
2235 IF p_cal_period_year < earliest_period_info.cal_period_year OR
2236 (p_cal_period_year = earliest_period_info.cal_period_year AND
2237 p_cal_period_number < earliest_period_info.cal_period_number) THEN
2238 UPDATE gcs_translation_track_h
2239 SET earliest_ever_period_id = p_cal_period_id
2240 WHERE hierarchy_id = p_hierarchy_id
2241 AND entity_id = p_entity_id
2242 AND currency_code = p_currency_code
2243 AND dataset_code = p_hier_dataset_code;
2244
2245 -- Now, if the period after the period just translated is not the
2246 -- previous first-ever translated period,then update the earliest-
2247 -- never period accordingly
2248 IF p_next_cal_period_id <> earliest_period_info.cal_period_id THEN
2249 UPDATE gcs_translation_track_h
2250 SET earliest_never_period_id = p_next_cal_period_id
2251 WHERE hierarchy_id = p_hierarchy_id
2252 AND entity_id = p_entity_id
2253 AND currency_code = p_currency_code
2254 AND dataset_code = p_hier_dataset_code;
2255 END IF;
2256 ELSIF p_cal_period_id = never_period_info.cal_period_id THEN
2257 next_period_id := p_next_cal_period_id;
2258 OPEN status_check_c(next_period_id);
2259 FETCH status_check_c INTO status;
2260 WHILE status_check_c%FOUND LOOP
2261 CLOSE status_check_c;
2262 GCS_UTILITY_PKG.get_cal_period_details
2263 (next_period_id, next_period_info);
2264 next_period_id := next_period_info.next_cal_period_id;
2265 OPEN status_check_c(next_period_id);
2266 FETCH status_check_c INTO status;
2267 END LOOP;
2268 CLOSE status_check_c;
2269
2270 IF next_period_id <> -1 THEN
2271 -- Now we have found the next period that has not been translated
2272 UPDATE gcs_translation_track_h
2273 SET earliest_never_period_id = next_period_id
2274 WHERE hierarchy_id = p_hierarchy_id
2275 AND entity_id = p_entity_id
2276 AND currency_code = p_currency_code
2277 AND dataset_code = p_hier_dataset_code;
2278 END IF;
2279 END IF;
2280 END IF;
2281
2282 module_log_write(module, g_module_success);
2283 EXCEPTION
2284 WHEN GCS_CCY_SUBPROGRAM_RAISED THEN
2285 module_log_write(module, g_module_failure);
2286 raise GCS_CCY_SUBPROGRAM_RAISED;
2287 WHEN OTHERS THEN
2288 FND_MESSAGE.set_name('GCS', 'GCS_CCY_TRACK_UNEXPECTED_ERR');
2289 g_error_text := FND_MESSAGE.get;
2290 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
2291 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, SQLERRM);
2292 module_log_write(module, g_module_failure);
2293 raise GCS_CCY_SUBPROGRAM_RAISED;
2294 END Update_Status_Tracking;
2295
2296
2297 --
2298 -- PUBLIC PROCEDURES
2299 --
2300
2301 PROCEDURE Translate(
2302 x_errbuf OUT NOCOPY VARCHAR2,
2303 x_retcode OUT NOCOPY VARCHAR2,
2304 p_cal_period_id NUMBER,
2305 p_cons_relationship_id NUMBER,
2306 p_balance_type_code VARCHAR2,
2307 p_hier_dataset_code NUMBER,
2308 p_new_entry_id NUMBER) IS
2309
2310 -- information about this period and the previous period
2311 cal_period_info GCS_UTILITY_PKG.r_cal_period_info;
2312
2313 source_currency VARCHAR2(15);
2314 target_currency VARCHAR2(15);
2315 curr_treatment_id NUMBER;
2316 hierarchy_id NUMBER;
2317 child_entity_id NUMBER;
2318 parent_entity_id NUMBER;
2319 ledger_id NUMBER;
2320 eq_translation_mode VARCHAR2(30);
2321 is_translation_mode VARCHAR2(30);
2322 per_rate_type_name VARCHAR2(30);
2323 per_avg_rate_type_name VARCHAR2(30);
2324 secondary_dimension_column VARCHAR2(30);
2325 org_tracking_flag VARCHAR2(1);
2326 source_system_code NUMBER;
2327 specific_interco_id NUMBER;
2328 group_by_flag VARCHAR2(1);
2329
2330 ccy_round_factor NUMBER; -- minimum accountable amount for target ccy
2331
2332 period_end_rate NUMBER;
2333 period_avg_rate NUMBER;
2334
2335 period_end_date DATE;
2336
2337 re_template GCS_TEMPLATES_PKG.TemplateRecord;
2338 cta_template GCS_TEMPLATES_PKG.TemplateRecord;
2339
2340 first_ever_period VARCHAR2(1); -- First period ever for translation (Y/N)
2341
2342 -- Bugfix 5707630: Holds the lin item id selected on the hierarchy page for
2343 -- retained earnings.
2344 hier_li_id NUMBER;
2345
2346 module VARCHAR2(30);
2347 BEGIN
2348 module := 'TRANSLATE';
2349 module_log_write(module, g_module_enter);
2350
2351 -- In case of an error, we will roll back to this point in time.
2352 SAVEPOINT gcs_ccy_translation_start;
2353
2354 -- Set all global variables as necessary
2355 Set_Globals;
2356
2357 -- Get various information regarding the relationship passed in.
2358 Get_Setup_Data(
2359 p_cal_period_id => p_cal_period_id,
2360 p_cons_relationship_id => p_cons_relationship_id,
2361 p_balance_type_code => p_balance_type_code,
2362 p_hier_dataset_code => p_hier_dataset_code,
2363 x_cal_period_info => cal_period_info,
2364 x_curr_treatment_id => curr_treatment_id,
2365 x_eq_translation_mode => eq_translation_mode,
2366 x_is_translation_mode => is_translation_mode,
2367 x_per_rate_type_name => per_rate_type_name,
2368 x_per_avg_rate_type_name=> per_avg_rate_type_name,
2369 x_hierarchy_id => hierarchy_id,
2370 x_child_entity_id => child_entity_id,
2371 x_parent_entity_id => parent_entity_id,
2372 x_ledger_id => ledger_id,
2373 x_source_currency => source_currency,
2374 x_target_currency => target_currency,
2375 x_period_end_date => period_end_date,
2376 x_period_end_rate => period_end_rate,
2377 x_period_avg_rate => period_avg_rate,
2378 x_ccy_round_factor => ccy_round_factor,
2379 x_first_ever_period => first_ever_period,
2380 x_re_tmp => re_template,
2381 x_cta_tmp => cta_template,
2382 x_org_tracking_flag => org_tracking_flag,
2383 x_secondary_dim_col => secondary_dimension_column,
2384 x_source_system_code => source_system_code,
2385 x_specific_interco_id => specific_interco_id,
2386 x_group_by_flag => group_by_flag,
2387 x_hier_li_id => hier_li_id);
2388
2389 -- Bugfix 5725759
2390 GCS_TRANS_DYNAMIC_PKG.Initialize_Data_Load_Status (
2391 p_hier_dataset_code => p_hier_dataset_code,
2392 p_cal_period_id => p_cal_period_id,
2393 p_source_system_code => source_system_code,
2394 p_from_ccy => source_currency,
2395 p_ledger_id => ledger_id,
2396 p_entity_id => child_entity_id,
2397 p_line_item_id => hier_li_id);
2398
2399 -- Roll forward the historical rates applicable for this translation.
2400 GCS_TRANS_DYNAMIC_PKG.Roll_Forward_Rates(
2401 p_hier_dataset_code => p_hier_dataset_code,
2402 p_source_system_code => source_system_code,
2403 p_ledger_id => ledger_id,
2404 p_cal_period_id => p_cal_period_id,
2405 p_prev_period_id => cal_period_info.prev_cal_period_id,
2406 p_entity_id => child_entity_id,
2407 p_hierarchy_id => hierarchy_id,
2408 p_from_ccy => source_currency,
2409 p_to_ccy => target_currency,
2410 p_eq_xlate_mode => eq_translation_mode,
2411 p_hier_li_id => hier_li_id);
2412
2413 -- Populate GCS_TRANSLATION_GT based on whether this is the first ever
2414 -- translated period or not.
2415 IF first_ever_period = 'Y' THEN
2416 GCS_TRANS_DYNAMIC_PKG.Translate_First_Ever_Period(
2417 p_hier_dataset_code => p_hier_dataset_code,
2418 p_source_system_code => source_system_code,
2419 p_ledger_id => ledger_id,
2420 p_cal_period_id => p_cal_period_id,
2421 p_entity_id => child_entity_id,
2422 p_hierarchy_id => hierarchy_id,
2423 p_from_ccy => source_currency,
2424 p_to_ccy => target_currency,
2425 p_eq_xlate_mode => eq_translation_mode,
2426 p_is_xlate_mode => is_translation_mode,
2427 p_avg_rate => period_avg_rate,
2428 p_end_rate => period_end_rate,
2429 p_group_by_flag => group_by_flag,
2430 p_round_factor => ccy_round_factor,
2431 p_hier_li_id => hier_li_id);
2432 ELSE
2433 GCS_TRANS_DYNAMIC_PKG.Translate_Subsequent_Period(
2434 p_hier_dataset_code => p_hier_dataset_code,
2435 p_cal_period_id => p_cal_period_id,
2436 p_prev_period_id => cal_period_info.prev_cal_period_id,
2437 p_entity_id => child_entity_id,
2438 p_hierarchy_id => hierarchy_id,
2439 p_ledger_id => ledger_id,
2440 p_from_ccy => source_currency,
2441 p_to_ccy => target_currency,
2442 p_eq_xlate_mode => eq_translation_mode,
2443 p_is_xlate_mode => is_translation_mode,
2444 p_avg_rate => period_avg_rate,
2445 p_end_rate => period_end_rate,
2446 p_group_by_flag => group_by_flag,
2447 p_round_factor => ccy_round_factor,
2448 p_source_system_code => source_system_code,
2449 p_hier_li_id => hier_li_id);
2450
2451 -- If this is the first period of a fiscal year, perform retained
2452 -- earnings account maintenance as well.
2453 IF cal_period_info.cal_period_number = 1 AND
2454 p_balance_type_code <> 'BUDGET' THEN
2455 Calculate_RE_Amt(
2456 p_relationship_id => p_cons_relationship_id,
2457 p_cal_period_id => p_cal_period_id,
2458 p_prev_period_id => cal_period_info.prev_cal_period_id,
2459 p_entity_id => child_entity_id,
2460 p_hierarchy_id => hierarchy_id,
2461 p_ledger_id => ledger_id,
2462 p_from_ccy => source_currency,
2463 p_to_ccy => target_currency,
2464 p_eq_xlate_mode => eq_translation_mode,
2465 p_hier_dataset_code => p_hier_dataset_code,
2466 p_org_track_flag => org_tracking_flag,
2467 p_sec_dim_col => secondary_dimension_column,
2468 p_re_template => re_template,
2469 p_source_system_code => source_system_code,
2470 p_specific_interco_id => specific_interco_id);
2471 END IF;
2472
2473 -- Calculate the PTD or YTD translated balances for YTD or PTD mode
2474 -- translation, respectively.
2475 Calculate_Xlated_PTD_YTD;
2476 END IF;
2477
2478 -- Create a new entry in the gcs_entry_headers table, and associated lines.
2479 GCS_TRANS_DYNAMIC_PKG.Create_New_Entry(
2480 p_new_entry_id => p_new_entry_id,
2481 p_hierarchy_id => hierarchy_id,
2482 p_entity_id => child_entity_id,
2483 p_cal_period_id => p_cal_period_id,
2484 p_balance_type_code => p_balance_type_code,
2485 p_to_ccy => target_currency);
2486
2487
2488 IF p_balance_type_code <> 'BUDGET' THEN
2489 --Calculate the CTA account balances
2490 GCS_TEMPLATES_DYNAMIC_PKG.balance(p_new_entry_id, cta_template,
2491 p_balance_type_code, hierarchy_id,
2492 child_entity_id);
2493 END IF;
2494
2495 -- If this is the last period of a fiscal year, calculate
2496 -- the retained earnings historical rate
2497 IF cal_period_info.next_cal_period_number = 1 AND
2498 p_balance_type_code <> 'BUDGET' AND
2499 eq_translation_mode = 'YTD' THEN
2500 Calculate_RE_Rate(
2501 p_relationship_id => p_cons_relationship_id,
2502 p_cal_period_id => p_cal_period_id,
2503 p_next_period_id => cal_period_info.next_cal_period_id,
2504 p_entity_id => child_entity_id,
2505 p_hierarchy_id => hierarchy_id,
2506 p_ledger_id => ledger_id,
2507 p_from_ccy => source_currency,
2508 p_to_ccy => target_currency,
2509 p_hier_dataset_code => p_hier_dataset_code,
2510 p_org_track_flag => org_tracking_flag,
2511 p_sec_dim_col => secondary_dimension_column,
2512 p_re_template => re_template,
2513 p_source_system_code => source_system_code,
2514 p_specific_interco_id => specific_interco_id,
2515 p_entry_id => p_new_entry_id);
2516 END IF;
2517
2518 -- Create or update rows in the gcs_translation_track_h and
2519 -- gcs_translation_statuses tables
2520 update_status_tracking(
2521 p_hierarchy_id => hierarchy_id,
2522 p_entity_id => child_entity_id,
2523 p_currency_code => target_currency,
2524 p_cal_period_id => p_cal_period_id,
2525 p_hier_dataset_code => p_hier_dataset_code,
2526 p_cal_period_number => cal_period_info.cal_period_number,
2527 p_cal_period_year => cal_period_info.cal_period_year,
2528 p_next_cal_period_id => cal_period_info.next_cal_period_id);
2529
2530 commit;
2531
2532 -- Write the appropriate information to the execution report
2533 module_log_write(module, g_module_success);
2534 EXCEPTION
2535 WHEN GCS_TEMPLATES_PKG.GCS_TMP_BALANCING_FAILED THEN
2536 ROLLBACK TO gcs_ccy_translation_start;
2537 x_errbuf := FND_MESSAGE.get;
2538 x_retcode := '2';
2539 module_log_write(module, g_module_failure);
2540 WHEN GCS_CCY_SUBPROGRAM_RAISED THEN
2541 ROLLBACK TO gcs_ccy_translation_start;
2542 x_errbuf := g_error_text;
2543 x_retcode := '2';
2544 module_log_write(module, g_module_failure);
2545 WHEN OTHERS THEN
2546 ROLLBACK TO gcs_ccy_translation_start;
2547 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, SQLERRM);
2548 x_errbuf := SQLERRM;
2549 x_retcode := '2';
2550 module_log_write(module, g_module_failure);
2551 END Translate;
2552
2553 END GCS_TRANSLATION_PKG;