[Home] [Help]
PACKAGE BODY: APPS.GCS_TRANS_DYNAMIC_PKG
Source
1 PACKAGE BODY GCS_TRANS_DYNAMIC_PKG AS
2
3 -- The API name
4 g_api VARCHAR2(50) := 'gcs.plsql.GCS_TRANS_DYNAMIC_PKG';
5
6 -- Action types for writing module information to the log file. Used for
7 -- the procedure log_file_module_write.
8 g_module_enter VARCHAR2(2) := '>>';
9 g_module_success VARCHAR2(2) := '<<';
10 g_module_failure VARCHAR2(2) := '<x';
11
12 -- A newline character. Included for convenience when writing long strings.
13 g_nl VARCHAR2(1) := '
14 ';
15
16 --
17 -- PRIVATE EXCEPTIONS
18 --
19 GCS_CCY_NO_DATA EXCEPTION;
20 GCS_CCY_ENTRY_CREATE_FAILED EXCEPTION;
21
22 --
23 -- PRIVATE PROCEDURES/FUNCTIONS
24 --
25
26 --
27 -- Procedure
28 -- Module_Log_Write
29 -- Purpose
30 -- Write the procedure or function entered or exited, and the time that
31 -- this happened. Write it to the log repository.
32 -- Arguments
33 -- p_module Name of the module
34 -- p_action_type Entered, Exited Successfully, or Exited with Failure
35 -- Example
36 -- GCS_TRANSLATION_PKG.Module_Log_Write
37 -- Notes
38 --
39 PROCEDURE Module_Log_Write
40 (p_module VARCHAR2,
41 p_action_type VARCHAR2) IS
42 BEGIN
43 -- Only print if the log level is set at the appropriate level
44 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE THEN
45 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, g_api || '.' || p_module,
46 p_action_type || ' ' || p_module || '() ' ||
47 to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
48 END IF;
49 FND_FILE.PUT_LINE(FND_FILE.LOG, p_action_type || ' ' || p_module ||
50 '() ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
51 END Module_Log_Write;
52
53 --
54 -- Procedure
55 -- Write_To_Log
56 -- Purpose
57 -- Write the text given to the log in 3500 character increments
58 -- this happened. Write it to the log repository.
59 -- Arguments
60 -- p_module Name of the module
61 -- p_level Logging level
62 -- p_text Text to write
63 -- Example
64 -- GCS_TRANSLATION_PKG.Write_To_Log
65 -- Notes
66 --
67 PROCEDURE Write_To_Log
68 (p_module VARCHAR2,
69 p_level NUMBER,
70 p_text VARCHAR2)
71 IS
72 api_module_concat VARCHAR2(200);
73 text_with_date VARCHAR2(32767);
74 text_with_date_len NUMBER;
75 curr_index NUMBER;
76 BEGIN
77 -- Only print if the log level is set at the appropriate level
78 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= p_level THEN
79 api_module_concat := g_api || '.' || p_module;
80 text_with_date := to_char(sysdate,'DD-MON-YYYY HH:MI:SS')||g_nl||p_text;
81 text_with_date_len := length(text_with_date);
82 curr_index := 1;
83 WHILE curr_index <= text_with_date_len LOOP
84 fnd_log.string(p_level, api_module_concat,
85 substr(text_with_date, curr_index, 3500));
86 curr_index := curr_index + 3500;
87 END LOOP;
88 END IF;
89 END Write_To_Log;
90
91
92
93
94 --
95 -- Function
96 -- Get_RE_Data_Exists
97 -- Purpose
98 -- Determines whether the data was loaded for the given combination or not.
99 -- Arguments
100 -- p_hier_dataset_code The dataset code in FEM_BALANCES.
101 -- p_cal_period_id The current period's cal_period_id.
102 -- p_source_system_code GCS source system code.
103 -- p_from_ccy From currency code.
104 -- p_ledger_id The ledger in FEM_BALANCES.
105 -- p_entity_id Entity on which the translation is being performed.
106 -- p_line_item_id Line Item Id of retained earnings selected for the hierarchy.
107 -- Example
108 -- GCS_TRANSLATION_PKG.Get_RE_Data_Exists
109 -- Notes
110 --
111
112 FUNCTION Get_RE_Data_Exists(
113 p_hier_dataset_code NUMBER,
114 p_cal_period_id NUMBER,
115 p_source_system_code NUMBER,
116 p_from_ccy VARCHAR2,
117 p_ledger_id NUMBER,
118 p_entity_id NUMBER,
119 p_line_item_id NUMBER) RETURN VARCHAR2 IS
120
121 l_re_data_flag VARCHAR2(10);
122 CURSOR re_data_cur (
123 p_hier_dataset_code NUMBER,
124 p_cal_period_id NUMBER,
125 p_source_system_code NUMBER,
126 p_from_ccy VARCHAR2,
127 p_ledger_id NUMBER,
128 p_entity_id NUMBER,
129 p_line_item_id NUMBER) IS
130 SELECT 'X'
131 FROM FEM_BALANCES fb
132 WHERE fb.dataset_code = p_hier_dataset_code
133 AND fb.cal_period_id = p_cal_period_id
134 AND fb.source_system_code = p_source_system_code
135 AND fb.currency_code = p_from_ccy
136 AND fb.ledger_id = p_ledger_id
137 AND fb.entity_id = p_entity_id
138 AND fb.line_item_id = p_line_item_id;
139
140 BEGIN
141 OPEN re_data_cur (
142 p_hier_dataset_code,
143 p_cal_period_id,
144 p_source_system_code,
145 p_from_ccy,
146 p_ledger_id,
147 p_entity_id,
148 p_line_item_id);
149 FETCH re_data_cur INTO l_re_data_flag;
150 CLOSE re_data_cur;
151
152 IF l_re_data_flag IS NOT NULL THEN
153 l_re_data_flag := 'Y';
154 ELSE
155 l_re_data_flag := 'N';
156 END IF;
157
158 RETURN l_re_data_flag;
159
160 END Get_RE_Data_Exists;
161
162
163 --
164 -- Public procedures
165 --
166
167
168 PROCEDURE Initialize_Data_Load_Status (
169 p_hier_dataset_code NUMBER,
170 p_cal_period_id NUMBER,
171 p_source_system_code NUMBER,
172 p_from_ccy VARCHAR2,
173 p_ledger_id NUMBER,
174 p_entity_id NUMBER,
175 p_line_item_id NUMBER) IS
176 BEGIN
177 re_data_loaded_flag :=
178 Get_RE_Data_Exists (
179 p_hier_dataset_code,
180 p_cal_period_id,
181 p_source_system_code,
182 p_from_ccy,
183 p_ledger_id,
184 p_entity_id,
185 p_line_item_id);
186 END;
187
188
189 -- Start bugfix 5707630: Added public procedure for Roll_Forward_Rates,
190 -- Translate_First_Ever_Period, Translate_Subsequent_Period and
191 -- Create_New_Entry procedures.This public procedures will call theier respective
192 -- private procedures (one for historical rates and the other for retained earnings).
193 --
194 PROCEDURE Roll_Forward_Rates
195 (p_hier_dataset_code NUMBER,
196 p_source_system_code NUMBER,
197 p_ledger_id NUMBER,
198 p_cal_period_id NUMBER,
199 p_prev_period_id NUMBER,
200 p_entity_id NUMBER,
201 p_hierarchy_id NUMBER,
202 p_from_ccy VARCHAR2,
203 p_to_ccy VARCHAR2,
204 p_eq_xlate_mode VARCHAR2,
205 p_hier_li_id NUMBER) IS
206 module VARCHAR2(30) := 'ROLL_FORWARD_RATES:PUBLIC';
207 BEGIN
208 write_to_log(module, FND_LOG.LEVEL_PROCEDURE,g_module_enter);
209
210 GCS_TRANS_HRATES_DYNAMIC_PKG.Roll_Forward_Historical_Rates
211 (p_hier_dataset_code,
212 p_source_system_code,
213 p_ledger_id,
214 p_cal_period_id,
215 p_prev_period_id,
216 p_entity_id,
217 p_hierarchy_id,
218 p_from_ccy,
219 p_to_ccy,
220 p_eq_xlate_mode,
221 p_hier_li_id);
222
223 GCS_TRANS_RE_DYNAMIC_PKG.Roll_Forward_Retained_Earnings
224 (p_hier_dataset_code,
225 p_source_system_code,
226 p_ledger_id,
227 p_cal_period_id,
228 p_prev_period_id,
229 p_entity_id,
230 p_hierarchy_id,
231 p_from_ccy,
232 p_to_ccy,
233 p_eq_xlate_mode,
234 p_hier_li_id);
235
236 write_to_log(module, FND_LOG.LEVEL_PROCEDURE,g_module_success);
237 END Roll_Forward_Rates;
238
239
240 --
241 PROCEDURE Translate_First_Ever_Period
242 (p_hier_dataset_code NUMBER,
243 p_source_system_code NUMBER,
244 p_ledger_id NUMBER,
245 p_cal_period_id NUMBER,
246 p_entity_id NUMBER,
247 p_hierarchy_id NUMBER,
248 p_from_ccy VARCHAR2,
249 p_to_ccy VARCHAR2,
250 p_eq_xlate_mode VARCHAR2,
251 p_is_xlate_mode VARCHAR2,
252 p_avg_rate NUMBER,
253 p_end_rate NUMBER,
254 p_group_by_flag VARCHAR2,
255 p_round_factor NUMBER,
256 p_hier_li_id NUMBER) IS
257 module VARCHAR2(50) := 'TRANSLATE_FIRST_EVER_PERIOD:PUBLIC';
258 BEGIN
259 write_to_log(module, FND_LOG.LEVEL_PROCEDURE,g_module_enter);
260
261 GCS_TRANS_HRATES_DYNAMIC_PKG.Trans_HRates_First_Per
262 (p_hier_dataset_code,
263 p_source_system_code,
264 p_ledger_id,
265 p_cal_period_id,
266 p_entity_id,
267 p_hierarchy_id,
268 p_from_ccy,
269 p_to_ccy,
270 p_eq_xlate_mode,
271 p_is_xlate_mode,
272 p_avg_rate,
273 p_end_rate,
274 p_group_by_flag,
275 p_round_factor,
276 p_hier_li_id);
277
278 IF re_data_loaded_flag = 'Y' THEN
279 GCS_TRANS_RE_DYNAMIC_PKG.Trans_RE_First_Per
280 (p_hier_dataset_code,
281 p_source_system_code,
282 p_ledger_id,
283 p_cal_period_id,
284 p_entity_id,
285 p_hierarchy_id,
286 p_from_ccy,
287 p_to_ccy,
288 p_eq_xlate_mode,
289 p_is_xlate_mode,
290 p_avg_rate,
291 p_end_rate,
292 p_group_by_flag,
293 p_round_factor,
294 p_hier_li_id);
295 END IF;
296
297 write_to_log(module, FND_LOG.LEVEL_PROCEDURE,g_module_success);
298 END Translate_First_Ever_Period;
299
300
301 --
302 PROCEDURE Translate_Subsequent_Period
303 (p_hier_dataset_code NUMBER,
304 p_cal_period_id NUMBER,
305 p_prev_period_id NUMBER,
306 p_entity_id NUMBER,
307 p_hierarchy_id NUMBER,
308 p_ledger_id NUMBER,
309 p_from_ccy VARCHAR2,
310 p_to_ccy VARCHAR2,
311 p_eq_xlate_mode VARCHAR2,
312 p_is_xlate_mode VARCHAR2,
313 p_avg_rate NUMBER,
314 p_end_rate NUMBER,
315 p_group_by_flag VARCHAR2,
316 p_round_factor NUMBER,
317 p_source_system_code NUMBER,
318 p_hier_li_id NUMBER) IS
319 module VARCHAR2(50) := 'TRANSLATE_SUBSEQUENT_PERIOD:PUBLIC';
320 BEGIN
321 write_to_log(module, FND_LOG.LEVEL_PROCEDURE,g_module_enter);
322
323 GCS_TRANS_HRATES_DYNAMIC_PKG.Trans_HRates_Subseq_Per
324 (p_hier_dataset_code,
325 p_cal_period_id,
326 p_prev_period_id,
327 p_entity_id,
328 p_hierarchy_id,
329 p_ledger_id,
330 p_from_ccy,
331 p_to_ccy,
332 p_eq_xlate_mode,
333 p_is_xlate_mode,
334 p_avg_rate,
335 p_end_rate,
336 p_group_by_flag,
337 p_round_factor,
338 p_source_system_code,
339 p_hier_li_id);
340
341 IF re_data_loaded_flag = 'Y' THEN
342 GCS_TRANS_RE_DYNAMIC_PKG.Trans_RE_Subseq_Per
343 (p_hier_dataset_code,
344 p_cal_period_id,
345 p_prev_period_id,
346 p_entity_id,
347 p_hierarchy_id,
348 p_ledger_id,
349 p_from_ccy,
350 p_to_ccy,
351 p_eq_xlate_mode,
352 p_is_xlate_mode,
353 p_avg_rate,
354 p_end_rate,
355 p_group_by_flag,
356 p_round_factor,
357 p_source_system_code,
358 p_hier_li_id);
359 END IF;
360
361 write_to_log(module, FND_LOG.LEVEL_PROCEDURE,g_module_success);
362 END Translate_Subsequent_Period;
363
364
365 -- Create_New_Entry will not split as it does not use gcs_historical_rates table.
366 --
367 PROCEDURE Create_New_Entry
368 (p_new_entry_id NUMBER,
369 p_hierarchy_id NUMBER,
370 p_entity_id NUMBER,
371 p_cal_period_id NUMBER,
372 p_balance_type_code VARCHAR2,
373 p_to_ccy VARCHAR2) IS
374 module VARCHAR2(50) := 'CREATE_NEW_ENTRY:PUBLIC';
375 -- Used to keep information for gcs_entry_pkg.create_entry_header.
376 errbuf VARCHAR2(2000);
377 retcode VARCHAR2(2000);
378
379 -- Used because we need an IN OUT parameter
380 new_entry_id NUMBER := p_new_entry_id;
381
382 BEGIN
383 write_to_log(module, FND_LOG.LEVEL_PROCEDURE,g_module_enter);
384
385
386 -- Create the entry
387 GCS_ENTRY_PKG.create_entry_header(
388 x_errbuf => errbuf,
389 x_retcode => retcode,
390 p_entry_id => new_entry_id,
391 p_hierarchy_id => p_hierarchy_id,
392 p_entity_id => p_entity_id,
393 p_start_cal_period_id => p_cal_period_id,
394 p_end_cal_period_id => p_cal_period_id,
395 p_entry_type_code => 'AUTOMATIC',
396 p_balance_type_code => p_balance_type_code,
397 p_currency_code => p_to_ccy,
398 p_process_code => 'SINGLE_RUN_FOR_PERIOD',
399 p_category_code => 'TRANSLATION',
400 p_xlate_flag => 'Y',
401 p_period_init_entry_flag => 'N');
402
403 IF retcode IN (fnd_api.g_ret_sts_error, fnd_api.g_ret_sts_unexp_error) THEN
404 raise GCS_CCY_ENTRY_CREATE_FAILED;
405 END IF;
406
407 write_to_log(module, FND_LOG.LEVEL_STATEMENT,
408 'INSERT /*+ parallel (gcs_entry_lines) */ INTO gcs_entry_lines(entry_id, ' ||
409 'line_item_id, company_cost_center_org_id, ' ||
410 'intercompany_id, financial_elem_id, product_id, ' ||
411 'natural_account_id, channel_id, project_id, customer_id, task_id, ' ||
412 'user_dim1_id, user_dim2_id, user_dim3_id, user_dim4_id, user_dim5_id, ' ||
413 'user_dim6_id, user_dim7_id, user_dim8_id, user_dim9_id, user_dim10_id, ' ||
414 'xtd_balance_e, ytd_balance_e, ptd_debit_balance_e, ptd_credit_balance_e, ' ||
415 'ytd_debit_balance_e, ytd_credit_balance_e, creation_date, created_by, ' ||
416 'last_update_date, last_updated_by, last_update_login)' || g_nl ||
417 'SELECT ' || p_new_entry_id || ', ' ||
418 'tgt.line_item_id, ' ||
419 'NULL, ' ||
420 'NULL, ' ||
421 'NULL, ' ||
422 'NULL, ' ||
423 'NULL, ' ||
424 'NULL, ' ||
425 'NULL, ' ||
426 'NULL, ' ||
427 'NULL, ' ||
428 'NULL, ' ||
429 'NULL, ' ||
430 'NULL, ' ||
431 'NULL, ' ||
432 'NULL, ' ||
433 'NULL, ' ||
434 'NULL, ' ||
435 'NULL, ' ||
436 'NULL, ' ||
437 'NULL, ' ||
438 g_nl ||
439 'fxata.number_assign_value *' || g_nl ||
440 'decode(tgt.account_type_code,' || g_nl ||
441 ' ''REVENUE'', tgt.xlate_ptd_dr - tgt.xlate_ptd_cr,' || g_nl ||
442 ' ''EXPENSE'', tgt.xlate_ptd_dr - tgt.xlate_ptd_cr,' || g_nl ||
443 ' tgt.xlate_ytd_dr - tgt.xlate_ytd_cr),' || g_nl ||
444 'fxata.number_assign_value * (tgt.xlate_ytd_dr - tgt.xlate_ytd_cr),' || g_nl ||
445 'tgt.xlate_ptd_dr, tgt.xlate_ptd_cr, tgt.xlate_ytd_dr, tgt.xlate_ytd_cr, sysdate, ' ||
446 gcs_translation_pkg.g_fnd_user_id || ', sysdate, ' ||
447 gcs_translation_pkg.g_fnd_user_id || ', ' ||
448 gcs_translation_pkg.g_fnd_login_id || g_nl ||
449 'FROM gcs_translation_gt, tgt,' || g_nl ||
450 ' fem_ln_items_attr li,' || g_nl ||
451 ' fem_ext_acct_types_attr fxata' || g_nl ||
452 'WHERE li.line_item_id = tgt.line_item_id' || g_nl ||
453 'AND li.attribute_id = ' || gcs_translation_pkg.g_li_acct_type_attr_id || g_nl ||
454 'AND li.version_id = ' || gcs_translation_pkg.g_li_acct_type_v_id || g_nl ||
455 'AND fxata.ext_account_type_code = li.dim_attribute_varchar_label' || g_nl ||
456 'AND fxata.attribute_id = ' || gcs_translation_pkg.g_xat_sign_attr_id || g_nl ||
457 'AND fxata.version_id = ' || gcs_translation_pkg.g_xat_sign_v_id);
458
459 INSERT /*+ parallel (gcs_entry_lines) */ INTO gcs_entry_lines(
460 entry_id, line_item_id, company_cost_center_org_id,
461 intercompany_id, financial_elem_id,
462 product_id, natural_account_id, channel_id, project_id, customer_id,
463 task_id, user_dim1_id, user_dim2_id, user_dim3_id, user_dim4_id,
464 user_dim5_id, user_dim6_id, user_dim7_id, user_dim8_id, user_dim9_id,
465 user_dim10_id, xtd_balance_e, ytd_balance_e, ptd_debit_balance_e,
466 ptd_credit_balance_e, ytd_debit_balance_e, ytd_credit_balance_e,
467 creation_date, created_by, last_update_date, last_updated_by,
468 last_update_login)
469 SELECT
470 p_new_entry_id,
471 tgt.line_item_id,
472
473 NULL,
474 NULL,
475 NULL,
476 NULL,
477 NULL,
478 NULL,
479 NULL,
480 NULL,
481 NULL,
482 NULL,
483 NULL,
484 NULL,
485 NULL,
486 NULL,
487 NULL,
488 NULL,
489 NULL,
490 NULL,
491 NULL,
492 fxata.number_assign_value *
493 decode(tgt.account_type_code,
494 'REVENUE', tgt.xlate_ptd_dr - tgt.xlate_ptd_cr,
495 'EXPENSE', tgt.xlate_ptd_dr - tgt.xlate_ptd_cr,
496 tgt.xlate_ytd_dr - tgt.xlate_ytd_cr),
497 fxata.number_assign_value * (tgt.xlate_ytd_dr - tgt.xlate_ytd_cr),
498 tgt.xlate_ptd_dr, tgt.xlate_ptd_cr, tgt.xlate_ytd_dr, tgt.xlate_ytd_cr,
499 sysdate, gcs_translation_pkg.g_fnd_user_id, sysdate,
500 gcs_translation_pkg.g_fnd_user_id, gcs_translation_pkg.g_fnd_login_id
501 FROM gcs_translation_gt tgt,
502 fem_ln_items_attr li,
503 fem_ext_acct_types_attr fxata
504 WHERE li.line_item_id = tgt.line_item_id
505 AND li.attribute_id = gcs_translation_pkg.g_li_acct_type_attr_id
506 AND li.version_id = gcs_translation_pkg.g_li_acct_type_v_id
507 AND fxata.ext_account_type_code = li.dim_attribute_varchar_member
508 AND fxata.attribute_id = gcs_translation_pkg.g_xat_sign_attr_id
509 AND fxata.version_id = gcs_translation_pkg.g_xat_sign_v_id;
510
511 write_to_log(module, FND_LOG.LEVEL_PROCEDURE,g_module_success);
512 EXCEPTION
513 WHEN GCS_CCY_ENTRY_CREATE_FAILED THEN
514 module_log_write(module, g_module_failure);
515 raise GCS_TRANSLATION_PKG.GCS_CCY_SUBPROGRAM_RAISED;
516 WHEN OTHERS THEN
517 FND_MESSAGE.set_name('GCS', 'GCS_CCY_NEW_ENTRY_UNEXP_ERR');
518 GCS_TRANSLATION_PKG.g_error_text := FND_MESSAGE.get;
519 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, GCS_TRANSLATION_PKG.g_error_text);
520 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, SQLERRM);
521 module_log_write(module, g_module_failure);
522 raise GCS_TRANSLATION_PKG.GCS_CCY_SUBPROGRAM_RAISED;
523
524
525 write_to_log(module, FND_LOG.LEVEL_PROCEDURE,g_module_success);
526 END Create_New_Entry;
527
528
529 END GCS_TRANS_DYNAMIC_PKG;