[Home] [Help]
PACKAGE BODY: APPS.GCS_TRANS_RE_DYN_BUILD_PKG
Source
1 PACKAGE BODY GCS_TRANS_RE_DYN_BUILD_PKG AS
2 /* $Header: gcsxltredb.pls 120.3 2007/06/28 12:31:47 vkosuri noship $ */
3
4 --
5 -- Private Exceptions
6 --
7 GCS_CCY_APPLSYS_NOT_FOUND EXCEPTION;
8 GCS_CCY_DYN_PKG_BUILD_ERR EXCEPTION;
9
10 --
11 -- Private Global Variables
12 --
13 -- The API name
14 g_api CONSTANT VARCHAR2(50) := 'gcs.plsql.GCS_TRANS_RE_DYN_BUILD_PKG';
15
16
17 -- Action types for writing module information to the log file. Used for
18 -- the procedure log_file_module_write.
19 g_module_enter CONSTANT VARCHAR2(2) := '>>';
20 g_module_success CONSTANT VARCHAR2(2) := '<<';
21 g_module_failure CONSTANT VARCHAR2(2) := '<x';
22
23 -- For holding error text
24 g_error_text VARCHAR2(32767);
25
26 -- Newline character
27 g_nl CONSTANT VARCHAR2(1) := '
28 ';
29
30 g_line_size NUMBER := 250;
31
32
33 --
34 -- Procedure
35 -- Module_Log_Write
36 -- Purpose
37 -- Write the procedure or function entered or exited, and the time that
38 -- this happened. Write it to the log repository.
39 -- Arguments
40 -- p_module Name of the module
41 -- p_action_type Entered, Exited Successfully, or Exited with Failure
42 -- Example
43 -- GCS_TRANS_RE_DYN_BUILD_PKG.Module_Log_Write
44 -- Notes
45 --
46 PROCEDURE Module_Log_Write
47 (p_module VARCHAR2,
48 p_action_type VARCHAR2) IS
49 BEGIN
50 -- Only print if the log level is set at the appropriate level
51 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE THEN
52 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, g_api || '.' || p_module,
53 p_action_type || ' ' || p_module || '() ' ||
54 to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
55 END IF;
56 FND_FILE.PUT_LINE(FND_FILE.LOG, p_action_type || ' ' || p_module ||
57 '() ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
58 END Module_Log_Write;
59
60 --
61 -- Procedure
62 -- Write_To_Log
63 -- Purpose
64 -- Write the text given to the log in 3500 character increments
65 -- this happened. Write it to the log repository.
66 -- Arguments
67 -- p_module Name of the module
68 -- p_level Logging level
69 -- p_text Text to write
70 -- Example
71 -- GCS_TRANS_RE_DYN_BUILD_PKG.Write_To_Log
72 -- Notes
73 --
74 PROCEDURE Write_To_Log
75 (p_module VARCHAR2,
76 p_level NUMBER,
77 p_text VARCHAR2)
78 IS
79 api_module_concat VARCHAR2(200);
80 text_with_date VARCHAR2(32767);
81 text_with_date_len NUMBER;
82 curr_index NUMBER;
83 BEGIN
84 -- Only print if the log level is set at the appropriate level
85 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= p_level THEN
86 api_module_concat := g_api || '.' || p_module;
87 text_with_date := to_char(sysdate,'DD-MON-YYYY HH:MI:SS')||g_nl||p_text;
88 text_with_date_len := length(text_with_date);
89 curr_index := 1;
90 WHILE curr_index <= text_with_date_len LOOP
91 fnd_log.string(p_level, api_module_concat,
92 substr(text_with_date, curr_index, 3500));
93 curr_index := curr_index + 3500;
94 END LOOP;
95 END IF;
96 END Write_To_Log;
97
98
99 --
100 -- Public procedures
101 --
102 PROCEDURE Create_Package(
103 x_errbuf OUT NOCOPY VARCHAR2,
104 x_retcode OUT NOCOPY VARCHAR2) IS
105
106 err VARCHAR2(2000);
107 status VARCHAR2(1);
108 industry VARCHAR2(1);
109 appl VARCHAR2(30);
110 module VARCHAR2(30);
111
112 -- control each line to < 80 chars and put in <= 50 lines each time
113 body_block VARCHAR2(20000);
114 body_len NUMBER;
115 curr_pos NUMBER;
116 line_num NUMBER := 1;
117
118 BEGIN
119 module := 'CREATE_PACKAGE';
120 module_log_write(module, g_module_enter);
121
122 -- Get APPLSYS information. Needed for ad_ddl
123 IF NOT fnd_installation.get_app_info('FND', status, industry, appl) THEN
124 raise gcs_ccy_applsys_not_found;
125 END IF;
126
127 -- Bugfix 5725759: Initialize the variables that determines the required dimensions.
128 GCS_TRANS_DYN_BUILD_PKG.Initialize_Dimensions;
129
130 -- Create the package body
131 body_block :=
132 'CREATE OR REPLACE PACKAGE BODY GCS_TRANS_RE_DYNAMIC_PKG AS
133
134 -- The API name
135 g_api VARCHAR2(50) := ''gcs.plsql.GCS_TRANS_RE_DYNAMIC_PKG'';
136
137 -- Action types for writing module information to the log file. Used for
138 -- the procedure log_file_module_write.
139 g_module_enter VARCHAR2(2) := ''>>'';
140 g_module_success VARCHAR2(2) := ''<<'';
141 g_module_failure VARCHAR2(2) := ''<x'';
142
143 -- A newline character. Included for convenience when writing long strings.
144 g_nl VARCHAR2(1) := ''
145 '';
146
147 --
148 -- PRIVATE EXCEPTIONS
149 --
150 GCS_CCY_NO_DATA EXCEPTION;
151 GCS_CCY_ENTRY_CREATE_FAILED EXCEPTION;
152
153 --
154 -- PRIVATE PROCEDURES/FUNCTIONS
155 --
156
157 --
158 -- Procedure
159 -- Module_Log_Write
160 -- Purpose
161 -- Write the procedure or function entered or exited, and the time that
162 -- this happened. Write it to the log repository.
163 -- Arguments
164 -- p_module Name of the module
165 -- p_action_type Entered, Exited Successfully, or Exited with Failure
166 -- Example
167 -- GCS_TRANS_RE_DYNAMIC_PKG.Module_Log_Write
168 -- Notes
169 --
170 PROCEDURE Module_Log_Write
171 (p_module VARCHAR2,
172 p_action_type VARCHAR2) IS
173 BEGIN
174 -- Only print if the log level is set at the appropriate level
175 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE THEN
176 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, g_api || ''.'' || p_module,
177 p_action_type || '' '' || p_module || ''() '' ||
178 to_char(sysdate, ''DD-MON-YYYY HH:MI:SS''));
179 END IF;
180 FND_FILE.PUT_LINE(FND_FILE.LOG, p_action_type || '' '' || p_module ||
181 ''() '' || to_char(sysdate, ''DD-MON-YYYY HH:MI:SS''));
182 END Module_Log_Write;
183
184 --
185 -- Procedure
186 -- Write_To_Log
187 -- Purpose
188 -- Write the text given to the log in 3500 character increments
189 -- this happened. Write it to the log repository.
190 -- Arguments
191 -- p_module Name of the module
192 -- p_level Logging level
193 -- p_text Text to write
194 -- Example
195 -- GCS_TRANS_RE_DYNAMICPKG.Write_To_Log
196 -- Notes
197 --
198 PROCEDURE Write_To_Log
199 (p_module VARCHAR2,
200 p_level NUMBER,
201 p_text VARCHAR2)
202 IS
203 api_module_concat VARCHAR2(200);
204 text_with_date VARCHAR2(32767);
205 text_with_date_len NUMBER;
206 curr_index NUMBER;
207 BEGIN
208 -- Only print if the log level is set at the appropriate level
209 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= p_level THEN
210 api_module_concat := g_api || ''.'' || p_module;
211 text_with_date := to_char(sysdate,''DD-MON-YYYY HH:MI:SS'')||g_nl||p_text;
212 text_with_date_len := length(text_with_date);
213 curr_index := 1;
214 WHILE curr_index <= text_with_date_len LOOP
215 fnd_log.string(p_level, api_module_concat,
216 substr(text_with_date, curr_index, 3500));
217 curr_index := curr_index + 3500;
218 END LOOP;
219 END IF;
220 END Write_To_Log;
221
222
223 --
224 -- Public procedures
225
226
227 --
228 PROCEDURE Roll_Forward_Retained_Earnings
229 (p_hier_dataset_code NUMBER,
230 p_source_system_code NUMBER,
231 p_ledger_id NUMBER,
232 p_cal_period_id NUMBER,
233 p_prev_period_id NUMBER,
234 p_entity_id NUMBER,
235 p_hierarchy_id NUMBER,
236 p_from_ccy VARCHAR2,
237 p_to_ccy VARCHAR2,
238 p_eq_xlate_mode VARCHAR2,
239 p_hier_li_id NUMBER) IS
240
241 module VARCHAR2(50) := ''ROLL_FORWARD_RETAINED_EARNINGS'';
242 BEGIN
243 write_to_log(module, FND_LOG.LEVEL_PROCEDURE,g_module_enter);
244
245 --Bugfix 6111815: Only Roll Forward Rates Where Standard RE Rate Flag is Y
246 write_to_log(module, FND_LOG.LEVEL_STATEMENT,
247 ''UPDATE gcs_historical_rates ghr'' || g_nl ||
248 ''SET (translated_rate, translated_amount, rate_type_code, '' ||
249 ''last_update_date, last_updated_by, last_update_login) ='' || g_nl ||
250 '' (SELECT ghr1.translated_rate, ghr1.translated_amount, ''''P'''', '' ||
251 ''sysdate, '' || gcs_translation_pkg.g_fnd_user_id || '', '' ||
252 gcs_translation_pkg.g_fnd_login_id || g_nl ||
253 '' FROM gcs_historical_rates ghr1'' || g_nl ||
254 '' WHERE ghr1.entity_id = ghr.entity_id'' || g_nl ||
255 '' AND ghr1.hierarchy_id = ghr.hierarchy_id'' || g_nl ||
256 '' AND ghr1.from_currency = ghr.from_currency'' || g_nl ||
257 '' AND ghr1.to_currency = ghr.to_currency'' || g_nl ||
258 '' AND ghr1.line_item_id = ghr.line_item_id'' || g_nl ||
259 '' AND ghr1.standard_re_rate_flag = ''''Y'''' '' || g_nl ||
260 '' AND ghr1.line_item_id = p_hier_li_id '' || g_nl ||';
261
262 curr_pos := 1;
263 body_len := LENGTH(body_block);
264 WHILE curr_pos <= body_len LOOP
265 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
266 line_num);
267 curr_pos := curr_pos + g_line_size;
268 line_num := line_num + 1;
269 END LOOP;
270
271 line_num := GCS_TRANS_DYN_BUILD_PKG.build_join_list(''' AND ghr1.', ' = ghr.', ''' || g_nl ||', line_num, 'F');
272
273 --Bugfix 6111815: Only Roll Forward Rates Where Standard RE Rate Flag is Y
274 body_block :=
275 ''' AND ghr1.cal_period_id = '' || p_prev_period_id || '')'' || g_nl ||
276 ''WHERE ghr.rowid IN ( '' || g_nl ||
277 '' SELECT ghr3.rowid'' || g_nl ||
278 '' FROM GCS_HISTORICAL_RATES ghr2, '' || g_nl ||
279 '' GCS_HISTORICAL_RATES ghr3'' || g_nl ||
280 '' WHERE ghr2.entity_id = '' || p_entity_id || g_nl ||
281 '' AND ghr2.hierarchy_id = '' || p_hierarchy_id || g_nl ||
282 '' AND ghr2.from_currency = '''''' || p_from_ccy || '''''''' || g_nl ||
283 '' AND ghr2.to_currency = '''''' || p_to_ccy || '''''''' || g_nl ||
284 '' AND ghr2.rate_type_code in (''''H'''',''''P'''',''''C'''')'' || g_nl ||
285 '' AND ghr2.account_type_code IN (''''ASSET'''',''''LIABILITY'''',decode('''''' || p_eq_xlate_mode || '''''', ''''YTD'''', ''''EQUITY'''', NULL))'' || g_nl ||
286 '' AND ghr2.stop_rollforward_flag = ''''N'''''' || g_nl ||
287 '' AND ghr3.entity_id = ghr2.entity_id'' || g_nl ||
288 '' AND ghr3.hierarchy_id = ghr2.hierarchy_id'' || g_nl ||
289 '' AND ghr2.cal_period_id = '' || p_prev_period_id || g_nl ||
290 '' AND ghr3.cal_period_id = '' || p_cal_period_id || g_nl ||
291 '' AND ghr3.line_item_id = ghr2.line_item_id'' || g_nl ||
292 '' AND ghr3.standard_re_rate_flag = ''''Y'''' '' || g_nl ||
293 '' AND ghr3.line_item_id = p_hier_li_id'' || g_nl ||';
294
295 curr_pos := 1;
296 body_len := LENGTH(body_block);
297 WHILE curr_pos <= body_len LOOP
298 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
299 line_num);
300 curr_pos := curr_pos + g_line_size;
301 line_num := line_num + 1;
302 END LOOP;
303
304 line_num := GCS_TRANS_DYN_BUILD_PKG.build_join_list(''' AND ghr3.', ' = ghr2.', ''' || g_nl ||', line_num, 'F');
305
306 body_block :=
307 ''' AND ghr3.from_currency = ghr2.from_currency'' || g_nl ||
308 '' AND ghr3.to_currency = ghr2.to_currency '' || g_nl ||
309 '' AND ghr3.rate_type_code IN (''''P'''', ''''E'''')'' || g_nl ||
310 '' AND (nvl(to_char(ghr2.translated_rate), ''''X'''') <>'' || g_nl ||
311 '' nvl(to_char(ghr3.translated_rate), ''''X'''')'' || g_nl ||
312 '' OR'' || g_nl ||
313 '' nvl(to_char(ghr2.translated_amount), ''''X'''') <>'' || g_nl ||
314 '' nvl(to_char(ghr3.translated_amount), ''''X'''')))'');
315
316 -- First, update historical rates for balance sheet accounts if:
317 -- 1. A historical rate exists in the current period and the rate type
318 -- is not historical.
319 -- 2. A historical rate exists in the previous period and the rate type
320 -- is Prior or Historical.
321 -- 3. The historical rates of current and previous periods are different.
322 -- 4. The historical rate is not marked with stop rolling forward.
323 --Bugfix 6111815: Only Roll Forward Rates Where Standard RE Rate Flag is Y
324 UPDATE gcs_historical_rates ghr
325 SET (translated_rate, translated_amount, rate_type_code,
326 last_update_date, last_updated_by, last_update_login) =
327 (SELECT ghr1.translated_rate, ghr1.translated_amount, ''P'', sysdate,
328 gcs_translation_pkg.g_fnd_user_id, gcs_translation_pkg.g_fnd_login_id
329 FROM gcs_historical_rates ghr1
330 WHERE ghr1.entity_id = ghr.entity_id
331 AND ghr1.hierarchy_id = ghr.hierarchy_id
332 AND ghr1.from_currency = ghr.from_currency
333 AND ghr1.to_currency = ghr.to_currency
334 AND ghr1.line_item_id = ghr.line_item_id
335 AND ghr1.standard_re_rate_flag = ''Y''
336 AND ghr1.line_item_id = p_hier_li_id';
337
338 curr_pos := 1;
339 body_len := LENGTH(body_block);
340 WHILE curr_pos <= body_len LOOP
341 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
342 line_num);
343 curr_pos := curr_pos + g_line_size;
344 line_num := line_num + 1;
345 END LOOP;
346
347 line_num := GCS_TRANS_DYN_BUILD_PKG.build_join_list(' AND ghr1.', ' = ghr.', '', line_num, 'F');
348
349 --Bugfix 6111815: Only Roll Forward Rates Where Standard RE Rate Flag is Y
350 body_block :=
351 ' AND ghr1.cal_period_id = p_prev_period_id)
352 WHERE ghr.rowid IN (
353 SELECT ghr3.rowid
354 FROM GCS_HISTORICAL_RATES ghr2,
355 GCS_HISTORICAL_RATES ghr3
356 WHERE ghr2.entity_id = p_entity_id
357 AND ghr2.hierarchy_id = p_hierarchy_id
358 AND ghr2.from_currency = p_from_ccy
359 AND ghr2.to_currency = p_to_ccy
360 AND ghr2.rate_type_code in (''H'',''P'',''C'')
361 AND ghr2.account_type_code IN (''ASSET'',''LIABILITY'',decode(p_eq_xlate_mode, ''YTD'', ''EQUITY'', NULL))
362 AND ghr2.stop_rollforward_flag = ''N''
363 AND ghr3.entity_id = ghr2.entity_id
364 AND ghr3.hierarchy_id = ghr2.hierarchy_id
365 AND ghr2.cal_period_id = p_prev_period_id
366 AND ghr3.cal_period_id = p_cal_period_id
367 AND ghr3.line_item_id = ghr2.line_item_id
368 ANd ghr3.standard_re_rate_flag = ''Y''
369 AND ghr3.line_item_id = p_hier_li_id';
370
371 curr_pos := 1;
372 body_len := LENGTH(body_block);
373 WHILE curr_pos <= body_len LOOP
374 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
375 line_num);
376 curr_pos := curr_pos + g_line_size;
377 line_num := line_num + 1;
378 END LOOP;
379
380 line_num := GCS_TRANS_DYN_BUILD_PKG.build_join_list(' AND ghr3.', ' = ghr2.', '', line_num, 'F');
381
382 body_block :=
383 ' AND ghr3.from_currency = ghr2.from_currency
384 AND ghr3.to_currency = ghr2.to_currency
385 AND ghr3.rate_type_code IN (''P'', ''E'')
386 AND (nvl(to_char(ghr2.translated_rate), ''X'') <>
387 nvl(to_char(ghr3.translated_rate), ''X'')
388 OR
389 nvl(to_char(ghr2.translated_amount), ''X'') <>
390 nvl(to_char(ghr3.translated_amount), ''X'')));
391
392 --Bugfix 6111815: Only Roll Forward Rates Where Standard RE Rate Flag is Y
393 write_to_log(module, FND_LOG.LEVEL_STATEMENT,
394 ''DELETE FROM gcs_historical_rates ghr'' || g_nl ||
395 ''WHERE (rowid, ''''E'''') IN ('' || g_nl ||
396 '' SELECT ghr3.rowid, nvl(ghr2.rate_type_code, ''''E'''')'' || g_nl ||
397 '' FROM GCS_HISTORICAL_RATES ghr3, '' || g_nl ||
398 '' GCS_HISTORICAL_RATES ghr2'' || g_nl ||
399 '' WHERE ghr3.entity_id = '' || p_entity_id || g_nl ||
400 '' AND ghr3.hierarchy_id = '' || p_hierarchy_id || g_nl ||
401 '' AND ghr3.rate_type_code = ''''P'''''' || g_nl ||
402 '' AND ghr3.account_type_code IN (''''ASSET'''',''''LIABILITY'''',decode('''''' || p_eq_xlate_mode || '''''', ''''YTD'''', ''''EQUITY'''', NULL))'' || g_nl ||
403 '' AND ghr3.cal_period_id = '' || p_cal_period_id || g_nl ||
404 '' AND ghr3.from_currency = '''''' || p_from_ccy || '''''''' || g_nl ||
405 '' AND ghr3.to_currency = '''''' || p_to_ccy || '''''''' || g_nl ||
406 '' AND ghr2.cal_period_id (+)= '' || p_prev_period_id || g_nl ||
407 '' AND ghr2.entity_id (+)= '' || p_entity_id || g_nl ||
408 '' AND ghr2.hierarchy_id (+)= '' || p_hierarchy_id || g_nl ||
409 '' AND ghr2.from_currency (+)= '''''' || p_from_ccy || '''''''' || g_nl ||
410 '' AND ghr2.to_currency (+)= '''''' || p_to_ccy || '''''''' || g_nl ||
411 '' AND ghr2.stop_rollforward_flag (+)= ''''N'''''' || g_nl ||
412 '' AND ghr2.line_item_id (+)= ghr3.line_item_id'' || g_nl ||
413 '' AND ghr3.standard_re_rate_flag = ''''Y'''' '' || g_nl ||
414 '' AND ghr3.line_item_id = p_hier_li_id '' || g_nl ||';
415
416 curr_pos := 1;
417 body_len := LENGTH(body_block);
418 WHILE curr_pos <= body_len LOOP
419 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
420 line_num);
421 curr_pos := curr_pos + g_line_size;
422 line_num := line_num + 1;
423 END LOOP;
424
425 line_num := GCS_TRANS_DYN_BUILD_PKG.build_join_list(''' AND ghr2.', ' (+)= ghr3.', ''' || g_nl ||', line_num, 'F');
426
427 body_block :=
428 ''' )'');
429
430 -- Next, delete historical rates for balance sheet accounts if:
431 -- 1. A historical rate exists in the current period and the rate
432 -- type is Prior.
433 -- 2. There is no historical rate in the previous period or a historical
434 -- rate exists in the previous period with the rate type Period.
435 --Bugfix 6111815: Only Roll Forward Rates Where Standard RE Rate Flag is Y
436 DELETE FROM gcs_historical_rates ghr
437 WHERE (rowid, ''E'') IN (
438 SELECT ghr3.rowid, nvl(ghr2.rate_type_code, ''E'')
439 FROM GCS_HISTORICAL_RATES ghr3,
440 GCS_HISTORICAL_RATES ghr2
441 WHERE ghr3.entity_id = p_entity_id
442 AND ghr3.hierarchy_id = p_hierarchy_id
443 AND ghr3.rate_type_code = ''P''
444 AND ghr3.account_type_code IN (''ASSET'',''LIABILITY'',decode(p_eq_xlate_mode, ''YTD'', ''EQUITY'', NULL))
445 AND ghr3.cal_period_id = p_cal_period_id
446 AND ghr3.from_currency = p_from_ccy
447 AND ghr3.to_currency = p_to_ccy
448 AND ghr2.cal_period_id (+)= p_prev_period_id
449 AND ghr2.entity_id (+)= p_entity_id
450 AND ghr2.hierarchy_id (+)= p_hierarchy_id
451 AND ghr2.from_currency (+)= p_from_ccy
452 AND ghr2.to_currency (+)= p_to_ccy
453 AND ghr2.stop_rollforward_flag (+)= ''N''
454 AND ghr2.line_item_id (+)= ghr3.line_item_id
455 AND ghr3.standard_re_rate_flag = ''Y''
456 AND ghr3.line_item_id = p_hier_li_id';
457
458 curr_pos := 1;
459 body_len := LENGTH(body_block);
460 WHILE curr_pos <= body_len LOOP
461 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
462 line_num);
463 curr_pos := curr_pos + g_line_size;
464 line_num := line_num + 1;
465 END LOOP;
466
467 line_num := GCS_TRANS_DYN_BUILD_PKG.build_join_list(' AND ghr2.', ' (+)= ghr3.', '', line_num, 'F');
468
469 --Bugfix 6111815: Only Roll Forward Rates Where Standard RE Rate Flag is Y
470 body_block :=
471 ' );
472
473 write_to_log(module, FND_LOG.LEVEL_STATEMENT,
474 ''INSERT /*+ parallel (gcs_historical_rates) */ INTO gcs_historical_rates(standard_re_rate_flag, entity_id, hierarchy_id, '' ||
475 ''cal_period_id, from_currency, to_currency, line_item_id, '' ||
476 ''company_cost_center_org_id, intercompany_id, financial_elem_id, '' ||
477 ''product_id, natural_account_id, channel_id, project_id, customer_id, task_id, '' ||
478 ''user_dim1_id, user_dim2_id, user_dim3_id, user_dim4_id, user_dim5_id, '' ||
479 ''user_dim6_id, user_dim7_id, user_dim8_id, user_dim9_id, user_dim10_id, '' ||
480 ''translated_rate, translated_amount, rate_type_code, update_flag, '' ||
481 ''account_type_code, stop_rollforward_flag, last_update_date, last_updated_by, '' ||
482 ''last_update_login, creation_date, created_by)'' || g_nl ||
483 ''SELECT '' || g_nl ||
484 '' ''''Y'''', ghr.entity_id, ghr.hierarchy_id, '' || p_cal_period_id || '', ''||
485 ''ghr.from_currency, ghr.to_currency, ghr.line_item_id, '' ||';
486
487 curr_pos := 1;
488 body_len := LENGTH(body_block);
489 WHILE curr_pos <= body_len LOOP
490 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
491 line_num);
492 curr_pos := curr_pos + g_line_size;
493 line_num := line_num + 1;
494 END LOOP;
495
496 line_num := GCS_TRANS_DYN_BUILD_PKG.build_comma_list('''ghr.', ' '' ||', '''NULL, '' ||', line_num, 'F');
497
498 body_block :=
499 '''ghr.translated_rate, ghr.translated_amount, ''''P'''', ''''N'''', '' ||
500 ''ghr.account_type_code, ''''N'''', sysdate, '' ||
501 gcs_translation_pkg.g_fnd_user_id || '', '' || gcs_translation_pkg.g_fnd_login_id ||
502 '', sysdate, '' || gcs_translation_pkg.g_fnd_user_id || g_nl ||
503 ''FROM gcs_historical_rates ghr'' || g_nl ||
504 ''WHERE ghr.entity_id = '' || p_entity_id || g_nl ||
505 ''AND ghr.hierarchy_id = '' || p_hierarchy_id || g_nl ||
506 ''AND ghr.to_currency = '''''' || p_to_ccy || '''''''' || g_nl ||
507 ''AND ghr.from_currency = '''''' || p_from_ccy || '''''''' || g_nl ||
508 ''AND ghr.rate_type_code in (''''H'''', ''''P'''', ''''C'''')'' || g_nl ||
509 ''AND ghr.cal_period_id = '' || p_prev_period_id || g_nl ||
510 ''AND ghr.account_type_code IN (''''ASSET'''',''''LIABILITY'''',decode('''''' || p_eq_xlate_mode || '''''', ''''YTD'''', ''''EQUITY'''', NULL))'' || g_nl ||
511 ''AND ghr.stop_rollforward_flag = ''''N'''''' || g_nl ||
512 ''AND ghr.line_item_id = '' || p_hier_li_id || g_nl ||
513 ''AND ghr.standard_re_rate_flag = ''''Y'''' '' || g_nl ||
514 '' AND NOT EXISTS ('' || g_nl ||
515 '' SELECT 1 FROM gcs_historical_rates ghr1'' || g_nl ||
516 '' WHERE ghr1.entity_id = p_entity_id'' || g_nl ||
517 '' AND ghr1.hierarchy_id = p_hierarchy_id'' || g_nl ||
518 '' AND ghr1.cal_period_id = p_cal_period_id'' || g_nl ||
519 '' AND ghr1.line_item_id = ghr.line_item_id'' || g_nl ||';
520
521 curr_pos := 1;
522 body_len := LENGTH(body_block);
523 WHILE curr_pos <= body_len LOOP
524 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
525 line_num);
526 curr_pos := curr_pos + g_line_size;
527 line_num := line_num + 1;
528 END LOOP;
529
530 line_num := GCS_TRANS_DYN_BUILD_PKG.build_join_list('''AND ghr1.', ' = ghr.', ''' || g_nl ||', line_num, 'F');
531
532 body_block :=
533 ''' AND ghr1.update_flag = ''''N'''''' || g_nl ||
534 '' AND ghr1.from_currency = ghr.from_currency'' || g_nl ||
535 '' AND ghr1.to_currency = ghr.to_currency);'' || g_nl );
536
537 -- Next, insert historical rates for balance sheet accounts if:
538 -- 1. No historical rate exists for the current period.
539 -- 2. A historical rate is defined for the previous period with Prior or
540 -- Historical rate type and the stop roll forward flag is not checked.
541 -- Bugfix 6111815: Adeded Standard RE Rate Flag as part of insert
542 INSERT /*+ parallel (gcs_historical_rates) */ INTO gcs_historical_rates(
543 standard_re_rate_flag, entity_id, hierarchy_id, cal_period_id, from_currency,
544 to_currency, line_item_id, company_cost_center_org_id, intercompany_id,
545 financial_elem_id, product_id, natural_account_id,
546 channel_id, project_id, customer_id, task_id, user_dim1_id, user_dim2_id,
547 user_dim3_id, user_dim4_id, user_dim5_id, user_dim6_id, user_dim7_id,
548 user_dim8_id, user_dim9_id, user_dim10_id, translated_rate,
549 translated_amount, rate_type_code, update_flag, account_type_code,
550 stop_rollforward_flag, last_update_date, last_updated_by,
551 last_update_login, creation_date, created_by)
552 SELECT
553 ''Y'', ghr.entity_id, ghr.hierarchy_id,
554 p_cal_period_id, ghr.from_currency, ghr.to_currency,
555 ghr.line_item_id,';
556
557 curr_pos := 1;
558 body_len := LENGTH(body_block);
559 WHILE curr_pos <= body_len LOOP
560 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
561 line_num);
562 curr_pos := curr_pos + g_line_size;
563 line_num := line_num + 1;
564 END LOOP;
565
566 line_num := GCS_TRANS_DYN_BUILD_PKG.build_comma_list(' ghr.', '', ' NULL,', line_num, 'F');
567
568 --Bugfix 6111815: Only Roll Forward Rates Where Standard RE Rate Flag is Y
569 body_block :=
570 ' ghr.translated_rate, ghr.translated_amount, ''P'', ''N'',
571 ghr.account_type_code, ''N'', sysdate, gcs_translation_pkg.g_fnd_user_id,
572 gcs_translation_pkg.g_fnd_login_id, sysdate,
573 gcs_translation_pkg.g_fnd_user_id
574 FROM gcs_historical_rates ghr
575 WHERE ghr.entity_id = p_entity_id
576 AND ghr.hierarchy_id = p_hierarchy_id
577 AND ghr.to_currency = p_to_ccy
578 AND ghr.from_currency = p_from_ccy
579 AND ghr.rate_type_code in (''H'', ''P'', ''C'')
580 AND ghr.cal_period_id = p_prev_period_id
581 AND ghr.account_type_code IN (''ASSET'',''LIABILITY'',decode(p_eq_xlate_mode, ''YTD'', ''EQUITY'', NULL))
582 AND ghr.stop_rollforward_flag = ''N''
583 AND ghr.line_item_id = p_hier_li_id
584 AND ghr.standard_re_rate_flag = ''Y''
585 AND NOT EXISTS (
586 SELECT 1 FROM gcs_historical_rates ghr1
587 WHERE ghr1.entity_id = p_entity_id
588 AND ghr1.hierarchy_id = p_hierarchy_id
589 AND ghr1.cal_period_id = p_cal_period_id
590 AND ghr1.line_item_id = ghr.line_item_id';
591
592 curr_pos := 1;
593 body_len := LENGTH(body_block);
594 WHILE curr_pos <= body_len LOOP
595 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
596 line_num);
597 curr_pos := curr_pos + g_line_size;
598 line_num := line_num + 1;
599 END LOOP;
600
601 line_num := GCS_TRANS_DYN_BUILD_PKG.build_join_list(' AND ghr1.', ' = ghr.', '', line_num, 'F');
602
603 body_block :=
604 ' AND ghr1.update_flag = ''N''
605 AND ghr1.from_currency = ghr.from_currency
606 AND ghr1.to_currency = ghr.to_currency);
607
608 write_to_log(module, FND_LOG.LEVEL_PROCEDURE,g_module_success);
609 EXCEPTION
610 WHEN OTHERS THEN
611 FND_MESSAGE.set_name(''GCS'', ''GCS_CCY_RF_UNEXPECTED_ERR'');
612 GCS_TRANSLATION_PKG.g_error_text := FND_MESSAGE.get;
613 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, GCS_TRANSLATION_PKG.g_error_text);
614 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, SQLERRM);
615 module_log_write(module, g_module_failure);
616 raise GCS_TRANSLATION_PKG.GCS_CCY_SUBPROGRAM_RAISED;
617 END Roll_Forward_Retained_Earnings;
618
619
620 --
621 PROCEDURE Trans_RE_First_Per
622 (p_hier_dataset_code NUMBER,
623 p_source_system_code NUMBER,
624 p_ledger_id NUMBER,
625 p_cal_period_id NUMBER,
626 p_entity_id NUMBER,
627 p_hierarchy_id NUMBER,
628 p_from_ccy VARCHAR2,
629 p_to_ccy VARCHAR2,
630 p_eq_xlate_mode VARCHAR2,
631 p_is_xlate_mode VARCHAR2,
632 p_avg_rate NUMBER,
633 p_end_rate NUMBER,
634 p_group_by_flag VARCHAR2,
635 p_round_factor NUMBER,
636 p_hier_li_id NUMBER) IS
637
638 -- Rate to use for equity accounts, income statement accounts if there
639 -- are no historical rates defined.
640 eq_rate NUMBER;
641 is_rate NUMBER;
642
643 module VARCHAR2(50) := ''TRANS_RE_FIRST_PER'';
644 BEGIN
645 write_to_log(module, FND_LOG.LEVEL_PROCEDURE,g_module_enter);
646
647 IF p_eq_xlate_mode = ''YTD'' THEN
648 eq_rate := p_end_rate;
649 ELSE
650 eq_rate := p_avg_rate;
651 END IF;
652
653 IF p_is_xlate_mode = ''YTD'' THEN
654 is_rate := p_end_rate;
655 ELSE
656 is_rate := p_avg_rate;
657 END IF;
658
659 IF p_group_by_flag = ''Y'' THEN
660 write_to_log(module, FND_LOG.LEVEL_STATEMENT,
661 ''INSERT /*+ parallel (GCS_TRANSLATION_GT) */ INTO GCS_TRANSLATION_GT(translate_rule_code, account_type_code, '' ||
662 ''line_item_id, company_cost_center_org_id, intercompany_id, financial_elem_id, '' ||
663 ''product_id, natural_account_id, channel_id, '' ||
664 ''project_id, customer_id, task_id, user_dim1_id, user_dim2_id, user_dim3_id, '' ||
665 ''user_dim4_id, user_dim5_id, user_dim6_id, user_dim7_id, user_dim8_id, '' ||
666 ''user_dim9_id, user_dim10_id, t_amount_dr, t_amount_cr, begin_ytd_dr, '' ||
667 ''begin_ytd_cr, xlate_ptd_dr, xlate_ptd_cr, xlate_ytd_dr, xlate_ytd_cr)'' || g_nl ||
668 ''SELECT'' || g_nl ||
669 ''decode(fxata.dim_attribute_varchar_member,'' || g_nl ||
670 '' ''''REVENUE'''', '''''' || p_is_xlate_mode || '''''','' || g_nl ||
671 '' ''''EXPENSE'''', '''''' || p_is_xlate_mode || '''''','' || g_nl ||
672 '' ''''EQUITY'''', '''''' || p_eq_xlate_mode || '''''','' || g_nl ||
673 '' ''''YTD''''),'' || g_nl ||
674 ''fxata.dim_attribute_varchar_member, fb.line_item_id, '' ||';
675
676 curr_pos := 1;
677 body_len := LENGTH(body_block);
678 WHILE curr_pos <= body_len LOOP
679 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
680 line_num);
681 curr_pos := curr_pos + g_line_size;
682 line_num := line_num + 1;
683 END LOOP;
684
685 line_num := GCS_TRANS_DYN_BUILD_PKG.build_comma_list('''fb.', ' '' ||', '''NULL, '' ||', line_num, 'F');
686
687 body_block :=
688 '''0, 0, 0, 0,'' || g_nl ||
689 ''round(nvl(ghr.translated_amount * 0,'' || g_nl ||
690 '' nvl(fb.sum_ytd_debit_balance_e, 0) *'' || g_nl ||
691 '' nvl(ghr.translated_rate,'' || g_nl ||
692 '' decode(fxata.dim_attribute_varchar_member,'' || g_nl ||
693 '' ''''REVENUE'''', '' || is_rate || '','' || g_nl ||
694 '' ''''EXPENSE'''', '' || is_rate || '','' || g_nl ||
695 '' ''''EQUITY'''', '' || eq_rate || '','' || g_nl ||
696 '' '' || p_end_rate || ''))) /'' || g_nl ||
697 '' '' || p_round_factor || '') *'' || g_nl ||
698 p_round_factor || '','' || g_nl ||
699 ''round(nvl(ghr.translated_amount,'' || g_nl ||
700 '' nvl(fb.sum_ytd_credit_balance_e, 0) *'' || g_nl ||
701 '' nvl(ghr.translated_rate,'' || g_nl ||
702 '' decode(fxata.dim_attribute_varchar_member,'' || g_nl ||
703 '' ''''REVENUE'''', '' || is_rate || '','' || g_nl ||
704 '' ''''EXPENSE'''', '' || is_rate || '','' || g_nl ||
705 '' ''''EQUITY'''', '' || eq_rate || '','' || g_nl ||
706 '' '' || p_end_rate || ''))) /'' || g_nl ||
707 '' '' || p_round_factor || '') *'' || g_nl ||
708 p_round_factor || '','' || g_nl ||
709 ''round(nvl(ghr.translated_amount * 0,'' || g_nl ||
710 '' nvl(fb.sum_ytd_debit_balance_e, 0) *'' || g_nl ||
711 '' nvl(ghr.translated_rate,'' || g_nl ||
712 '' decode(fxata.dim_attribute_varchar_member,'' || g_nl ||
713 '' ''''REVENUE'''', '' || is_rate || '','' || g_nl ||
714 '' ''''EXPENSE'''', '' || is_rate || '','' || g_nl ||
715 '' ''''EQUITY'''', '' || eq_rate || '','' || g_nl ||
716 '' '' || p_end_rate || ''))) /'' || g_nl ||
717 '' '' || p_round_factor || '') *'' || g_nl ||
718 p_round_factor || '','' || g_nl ||
719 ''round(nvl(ghr.translated_amount,'' || g_nl ||
720 '' nvl(fb.sum_ytd_credit_balance_e, 0) *'' || g_nl ||
721 '' nvl(ghr.translated_rate,'' || g_nl ||
722 '' decode(fxata.dim_attribute_varchar_member,'' || g_nl ||
723 '' ''''REVENUE'''', '' || is_rate || '','' || g_nl ||
724 '' ''''EXPENSE'''', '' || is_rate || '','' || g_nl ||
725 '' ''''EQUITY'''', '' || eq_rate || '','' || g_nl ||
726 '' '' || p_end_rate || ''))) /'' || g_nl ||
727 '' '' || p_round_factor || '') *'' || g_nl ||
728 p_round_factor || g_nl ||
729 ''FROM (SELECT'' || g_nl ||
730 '' fb_in.line_item_id,'' || g_nl ||';
731
732 curr_pos := 1;
733 body_len := LENGTH(body_block);
734 WHILE curr_pos <= body_len LOOP
735 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
736 line_num);
737 curr_pos := curr_pos + g_line_size;
738 line_num := line_num + 1;
739 END LOOP;
740
741 line_num := GCS_TRANS_DYN_BUILD_PKG.build_comma_list(''' fb_in.', ' '' ||', '', line_num, 'F');
742
743 body_block :=
744 ''' SUM(ytd_debit_balance_e) sum_ytd_debit_balance_e,'' || g_nl ||
745 '' SUM(ytd_credit_balance_e) sum_ytd_credit_balance_e'' || g_nl ||
746 '' FROM FEM_BALANCES fb_in'' || g_nl ||
747 '' WHERE fb_in.dataset_code = '' || p_hier_dataset_code || g_nl ||
748 '' AND fb_in.cal_period_id = '' || p_cal_period_id || g_nl ||
749 '' AND fb_in.source_system_code = '' || p_source_system_code || g_nl ||
750 '' AND fb_in.currency_code = '''''' || p_from_ccy || '''''''' || g_nl ||
751 '' AND fb_in.ledger_id = '' || p_ledger_id || g_nl ||
752 '' AND fb_in.entity_id = '' || p_entity_id || g_nl ||
753 '' GROUP BY '' || g_nl ||';
754
755 curr_pos := 1;
756 body_len := LENGTH(body_block);
757 WHILE curr_pos <= body_len LOOP
758 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
759 line_num);
760 curr_pos := curr_pos + g_line_size;
761 line_num := line_num + 1;
762 END LOOP;
763
764 line_num := GCS_TRANS_DYN_BUILD_PKG.build_comma_list(''' fb_in.', ' '' ||', '', line_num, 'F');
765
766 --Bugfix 6111815: Added Standard RE Rate Flag
767 body_block :=
768 ''' fb_in.line_item_id) fb,'' || g_nl ||
769 '' FEM_LN_ITEMS_ATTR li,'' || g_nl ||
770 '' FEM_EXT_ACCT_TYPES_ATTR fxata,'' || g_nl ||
771 '' GCS_HISTORICAL_RATES ghr'' || g_nl ||
772 ''WHERE li.line_item_id = fb.line_item_id'' || g_nl ||
773 ''AND li.attribute_id = '' || gcs_translation_pkg.g_li_acct_type_attr_id || g_nl ||
774 ''AND li.version_id = '' || gcs_translation_pkg.g_li_acct_type_v_id || g_nl ||
775 ''AND fxata.ext_account_type_code = li.dim_attribute_varchar_member'' || g_nl ||
779 ''AND ghr.hierarchy_id = '' || p_hierarchy_id || g_nl ||
776 ''AND fxata.attribute_id = '' || gcs_translation_pkg.g_xat_basic_acct_type_attr_id || g_nl ||
777 ''AND fxata.version_id = '' || gcs_translation_pkg.g_xat_basic_acct_type_v_id || g_nl ||
778 ''AND ghr.entity_id = '' || p_entity_id || g_nl ||
780 ''AND ghr.from_currency = '''''' || p_from_ccy || '''''''' || g_nl ||
781 ''AND ghr.to_currency = '''''' || p_to_ccy || '''''''' || g_nl ||
782 ''AND ghr.cal_period_id = '' || p_cal_period_id || g_nl ||
783 ''AND ghr.line_item_id = fb.line_item_id'' || g_nl ||
784 ''AND ghr.update_flag = ''''N'''' '' || g_nl ||
785 ''AND ghr.standard_re_rate_flag = ''''Y'''' '' || g_nl ||
786 ''AND fb.line_item_id = p_hier_li_id '' || g_nl ||';
787
788 curr_pos := 1;
789 body_len := LENGTH(body_block);
790 WHILE curr_pos <= body_len LOOP
791 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
792 line_num);
793 curr_pos := curr_pos + g_line_size;
794 line_num := line_num + 1;
795 END LOOP;
796
797 --Bugfix 6111815: Remove the outer join
798 line_num := GCS_TRANS_DYN_BUILD_PKG.build_join_list('''AND ghr.', ' = fb.', ''' || g_nl ||', line_num, 'F');
799
800 body_block :=
801 ''''');
802
803 INSERT /*+ parallel (GCS_TRANSLATION_GT) */ INTO GCS_TRANSLATION_GT(
804 translate_rule_code, account_type_code, line_item_id,
805 company_cost_center_org_id, intercompany_id, financial_elem_id,
806 product_id, natural_account_id, channel_id, project_id,
807 customer_id, task_id, user_dim1_id, user_dim2_id, user_dim3_id,
808 user_dim4_id, user_dim5_id, user_dim6_id, user_dim7_id, user_dim8_id,
809 user_dim9_id, user_dim10_id, t_amount_dr, t_amount_cr, begin_ytd_dr,
810 begin_ytd_cr, xlate_ptd_dr, xlate_ptd_cr, xlate_ytd_dr, xlate_ytd_cr)
811 SELECT /*+ ordered */
812 decode(fxata.dim_attribute_varchar_member,
813 ''REVENUE'', p_is_xlate_mode,
814 ''EXPENSE'', p_is_xlate_mode,
815 ''EQUITY'', p_eq_xlate_mode,
816 ''YTD''),
817 fxata.dim_attribute_varchar_member, fb.line_item_id,';
818
819 curr_pos := 1;
820 body_len := LENGTH(body_block);
821 WHILE curr_pos <= body_len LOOP
822 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
823 line_num);
824 curr_pos := curr_pos + g_line_size;
825 line_num := line_num + 1;
826 END LOOP;
827
828 line_num := GCS_TRANS_DYN_BUILD_PKG.build_comma_list(' fb.', '', ' NULL,', line_num, 'F');
829
830 body_block :=
831 ' 0, 0, 0, 0,
832 round(nvl(ghr.translated_amount * 0,
833 nvl(fb.sum_ytd_debit_balance_e, 0) *
834 nvl(ghr.translated_rate,
835 decode(fxata.dim_attribute_varchar_member,
836 ''REVENUE'', is_rate,
837 ''EXPENSE'', is_rate,
838 ''EQUITY'', eq_rate,
839 p_end_rate))) /
840 p_round_factor) *
841 p_round_factor,
842 round(nvl(ghr.translated_amount,
843 nvl(fb.sum_ytd_credit_balance_e, 0) *
844 nvl(ghr.translated_rate,
845 decode(fxata.dim_attribute_varchar_member,
846 ''REVENUE'', is_rate,
847 ''EXPENSE'', is_rate,
848 ''EQUITY'', eq_rate,
849 p_end_rate))) /
850 p_round_factor) *
851 p_round_factor,
852 round(nvl(ghr.translated_amount * 0,
853 nvl(fb.sum_ytd_debit_balance_e, 0) *
854 nvl(ghr.translated_rate,
855 decode(fxata.dim_attribute_varchar_member,
856 ''REVENUE'', is_rate,
857 ''EXPENSE'', is_rate,
858 ''EQUITY'', eq_rate,
859 p_end_rate))) /
860 p_round_factor) *
861 p_round_factor,
862 round(nvl(ghr.translated_amount,
863 nvl(fb.sum_ytd_credit_balance_e, 0) *
864 nvl(ghr.translated_rate,
865 decode(fxata.dim_attribute_varchar_member,
866 ''REVENUE'', is_rate,
867 ''EXPENSE'', is_rate,
868 ''EQUITY'', eq_rate,
869 p_end_rate))) /
870 p_round_factor) *
871 p_round_factor
872 FROM (SELECT
873 fb_in.line_item_id,';
874
875 curr_pos := 1;
876 body_len := LENGTH(body_block);
877 WHILE curr_pos <= body_len LOOP
878 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
879 line_num);
880 curr_pos := curr_pos + g_line_size;
881 line_num := line_num + 1;
882 END LOOP;
883
884 line_num := GCS_TRANS_DYN_BUILD_PKG.build_comma_list(' fb_in.', '', '', line_num, 'F');
885
886 body_block :=
887 ' SUM(ytd_debit_balance_e) sum_ytd_debit_balance_e,
888 SUM(ytd_credit_balance_e) sum_ytd_credit_balance_e
889 FROM FEM_BALANCES fb_in
890 WHERE fb_in.dataset_code = p_hier_dataset_code
891 AND fb_in.cal_period_id = p_cal_period_id
892 AND fb_in.source_system_code = p_source_system_code
893 AND fb_in.currency_code = p_from_ccy
894 AND fb_in.ledger_id = p_ledger_id
895 AND fb_in.entity_id = p_entity_id
896 GROUP BY';
897
898 curr_pos := 1;
899 body_len := LENGTH(body_block);
900 WHILE curr_pos <= body_len LOOP
901 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
902 line_num);
903 curr_pos := curr_pos + g_line_size;
904 line_num := line_num + 1;
905 END LOOP;
906
907 line_num := GCS_TRANS_DYN_BUILD_PKG.build_comma_list(' fb_in.', '', '', line_num, 'F');
908
909 --Bugfix 6111815: Added Standard RE Rate Flag
910 body_block :=
911 ' fb_in.line_item_id) fb,
912 FEM_LN_ITEMS_ATTR li,
913 FEM_EXT_ACCT_TYPES_ATTR fxata,
914 GCS_HISTORICAL_RATES ghr
915 WHERE li.line_item_id = fb.line_item_id
916 AND li.attribute_id = gcs_translation_pkg.g_li_acct_type_attr_id
917 AND li.version_id = gcs_translation_pkg.g_li_acct_type_v_id
918 AND fxata.ext_account_type_code = li.dim_attribute_varchar_member
919 AND fxata.attribute_id = gcs_translation_pkg.g_xat_basic_acct_type_attr_id
920 AND fxata.version_id = gcs_translation_pkg.g_xat_basic_acct_type_v_id
921 AND ghr.entity_id = p_entity_id
922 AND ghr.hierarchy_id = p_hierarchy_id
923 AND ghr.from_currency = p_from_ccy
924 AND ghr.to_currency = p_to_ccy
925 AND ghr.cal_period_id = p_cal_period_id
926 AND ghr.line_item_id = fb.line_item_id
927 AND ghr.update_flag = ''N''
928 AND fb.line_item_id = p_hier_li_id
929 AND ghr.standard_re_rate_flag = ''Y'' ';
930
931 curr_pos := 1;
932 body_len := LENGTH(body_block);
933 WHILE curr_pos <= body_len LOOP
934 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
935 line_num);
936 curr_pos := curr_pos + g_line_size;
937 line_num := line_num + 1;
938 END LOOP;
939
940 --Bugfix 6111815: Removed Outer Join
941 line_num := GCS_TRANS_DYN_BUILD_PKG.build_join_list(' AND ghr.', ' = fb.', '', line_num, 'F');
942
943 body_block :=
944 ' ;
945
946 ELSE
947 write_to_log(module, FND_LOG.LEVEL_STATEMENT,
948 ''INSERT /*+ parallel (GCS_TRANSLATION_GT) */ INTO GCS_TRANSLATION_GT(translate_rule_code, account_type_code, '' ||
949 ''line_item_id, company_cost_center_org_id, intercompany_id, financial_elem_id, '' ||
950 ''product_id, natural_account_id, channel_id, '' ||
951 ''project_id, customer_id, task_id, user_dim1_id, user_dim2_id, user_dim3_id, '' ||
952 ''user_dim4_id, user_dim5_id, user_dim6_id, user_dim7_id, user_dim8_id, '' ||
953 ''user_dim9_id, user_dim10_id, t_amount_dr, t_amount_cr, begin_ytd_dr, '' ||
954 ''begin_ytd_cr, xlate_ptd_dr, xlate_ptd_cr, xlate_ytd_dr, xlate_ytd_cr)'' || g_nl ||
955 ''SELECT'' || g_nl ||
956 ''decode(fxata.dim_attribute_varchar_member,'' || g_nl ||
957 '' ''''REVENUE'''', '''''' || p_is_xlate_mode || '''''','' || g_nl ||
958 '' ''''EXPENSE'''', '''''' || p_is_xlate_mode || '''''','' || g_nl ||
959 '' ''''EQUITY'''', '''''' || p_eq_xlate_mode || '''''','' || g_nl ||
960 '' ''''YTD''''),'' || g_nl ||
961 ''fxata.dim_attribute_varchar_member, fb.line_item_id, '' ||';
962
963 curr_pos := 1;
964 body_len := LENGTH(body_block);
965 WHILE curr_pos <= body_len LOOP
966 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
967 line_num);
968 curr_pos := curr_pos + g_line_size;
969 line_num := line_num + 1;
970 END LOOP;
971
972 line_num := GCS_TRANS_DYN_BUILD_PKG.build_comma_list('''fb.', ' '' ||', '''NULL, '' ||', line_num, 'F');
973
974 body_block :=
975 '''0, 0, 0, 0,'' || g_nl ||
976 ''round(nvl(ghr.translated_amount * 0,'' || g_nl ||
977 '' nvl(fb.sum_ytd_debit_balance_e, 0) *'' || g_nl ||
978 '' nvl(ghr.translated_rate,'' || g_nl ||
979 '' decode(fxata.dim_attribute_varchar_member,'' || g_nl ||
980 '' ''''REVENUE'''', '' || is_rate || '','' || g_nl ||
981 '' ''''EXPENSE'''', '' || is_rate || '','' || g_nl ||
982 '' ''''EQUITY'''', '' || eq_rate || '','' || g_nl ||
983 '' '' || p_end_rate || ''))) /'' || g_nl ||
984 '' '' || p_round_factor || '') *'' || g_nl ||
985 p_round_factor || '','' || g_nl ||
986 ''round(nvl(ghr.translated_amount,'' || g_nl ||
987 '' nvl(fb.sum_ytd_credit_balance_e, 0) *'' || g_nl ||
988 '' nvl(ghr.translated_rate,'' || g_nl ||
992 '' ''''EQUITY'''', '' || eq_rate || '','' || g_nl ||
989 '' decode(fxata.dim_attribute_varchar_member,'' || g_nl ||
990 '' ''''REVENUE'''', '' || is_rate || '','' || g_nl ||
991 '' ''''EXPENSE'''', '' || is_rate || '','' || g_nl ||
993 '' '' || p_end_rate || ''))) /'' || g_nl ||
994 '' '' || p_round_factor || '') *'' || g_nl ||
995 p_round_factor || '','' || g_nl ||
996 ''round(nvl(ghr.translated_amount * 0,'' || g_nl ||
997 '' nvl(fb.sum_ytd_debit_balance_e, 0) *'' || g_nl ||
998 '' nvl(ghr.translated_rate,'' || g_nl ||
999 '' decode(fxata.dim_attribute_varchar_member,'' || g_nl ||
1000 '' ''''REVENUE'''', '' || is_rate || '','' || g_nl ||
1001 '' ''''EXPENSE'''', '' || is_rate || '','' || g_nl ||
1002 '' ''''EQUITY'''', '' || eq_rate || '','' || g_nl ||
1003 '' '' || p_end_rate || ''))) /'' || g_nl ||
1004 '' '' || p_round_factor || '') *'' || g_nl ||
1005 p_round_factor || '','' || g_nl ||
1006 ''round(nvl(ghr.translated_amount,'' || g_nl ||
1007 '' nvl(fb.sum_ytd_credit_balance_e, 0) *'' || g_nl ||
1008 '' nvl(ghr.translated_rate,'' || g_nl ||
1009 '' decode(fxata.dim_attribute_varchar_member,'' || g_nl ||
1010 '' ''''REVENUE'''', '' || is_rate || '','' || g_nl ||
1011 '' ''''EXPENSE'''', '' || is_rate || '','' || g_nl ||
1012 '' ''''EQUITY'''', '' || eq_rate || '','' || g_nl ||
1013 '' '' || p_end_rate || ''))) /'' || g_nl ||
1014 '' '' || p_round_factor || '') *'' || g_nl ||
1015 p_round_factor || g_nl ||
1016 ''FROM (SELECT'' || g_nl ||
1017 '' fb_in.line_item_id,'' || g_nl ||';
1018
1019 curr_pos := 1;
1020 body_len := LENGTH(body_block);
1021 WHILE curr_pos <= body_len LOOP
1022 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1023 line_num);
1024 curr_pos := curr_pos + g_line_size;
1025 line_num := line_num + 1;
1026 END LOOP;
1027
1028 line_num := GCS_TRANS_DYN_BUILD_PKG.build_comma_list(''' fb_in.', ' '' ||', '', line_num, 'F');
1029
1030 --Bugfix 6111815: Added Standard RE Rate Flag
1031 body_block :=
1032 ''' ytd_debit_balance_e sum_ytd_debit_balance_e,'' || g_nl ||
1033 '' ytd_credit_balance_e sum_ytd_credit_balance_e'' || g_nl ||
1034 '' FROM FEM_BALANCES fb_in'' || g_nl ||
1035 '' WHERE fb_in.dataset_code = '' || p_hier_dataset_code || g_nl ||
1036 '' AND fb_in.cal_period_id = '' || p_cal_period_id || g_nl ||
1037 '' AND fb_in.source_system_code = '' || p_source_system_code || g_nl ||
1038 '' AND fb_in.currency_code = '''''' || p_from_ccy || '''''''' || g_nl ||
1039 '' AND fb_in.ledger_id = '' || p_ledger_id || g_nl ||
1040 '' AND fb_in.entity_id = '' || p_entity_id || '') fb,'' || g_nl ||
1041 '' FEM_LN_ITEMS_ATTR li,'' || g_nl ||
1042 '' FEM_EXT_ACCT_TYPES_ATTR fxata,'' || g_nl ||
1043 '' GCS_HISTORICAL_RATES ghr'' || g_nl ||
1044 ''WHERE li.line_item_id = fb.line_item_id'' || g_nl ||
1045 ''AND li.attribute_id = '' || gcs_translation_pkg.g_li_acct_type_attr_id || g_nl ||
1046 ''AND li.version_id = '' || gcs_translation_pkg.g_li_acct_type_v_id || g_nl ||
1047 ''AND fxata.ext_account_type_code = li.dim_attribute_varchar_member'' || g_nl ||
1048 ''AND fxata.attribute_id = '' || gcs_translation_pkg.g_xat_basic_acct_type_attr_id || g_nl ||
1049 ''AND fxata.version_id = '' || gcs_translation_pkg.g_xat_basic_acct_type_v_id || g_nl ||
1050 ''AND ghr.entity_id = '' || p_entity_id || g_nl ||
1051 ''AND ghr.hierarchy_id = '' || p_hierarchy_id || g_nl ||
1052 ''AND ghr.from_currency = '''''' || p_from_ccy || '''''''' || g_nl ||
1053 ''AND ghr.to_currency = '''''' || p_to_ccy || '''''''' || g_nl ||
1054 ''AND ghr.cal_period_id = '' || p_cal_period_id || g_nl ||
1055 ''AND ghr.line_item_id = fb.line_item_id'' || g_nl ||
1056 ''AND ghr.update_flag = ''''N'''' '' || g_nl ||
1057 ''AND ghr.standard_re_rate_flag = ''''Y'''' '' || g_nl ||
1058 ''AND fb.line_item_id = p_hier_li_id '' || g_nl ||';
1059
1060 curr_pos := 1;
1061 body_len := LENGTH(body_block);
1062 WHILE curr_pos <= body_len LOOP
1063 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1064 line_num);
1065 curr_pos := curr_pos + g_line_size;
1066 line_num := line_num + 1;
1067 END LOOP;
1068
1069 --Bugfix 6111815: Removed Outer Join
1070 line_num := GCS_TRANS_DYN_BUILD_PKG.build_join_list('''AND ghr.', ' = fb.', ''' || g_nl ||', line_num, 'F');
1071
1072 body_block :=
1073 ''''');
1074
1075 INSERT /*+ parallel (GCS_TRANSLATION_GT) */ INTO GCS_TRANSLATION_GT(
1076 translate_rule_code, account_type_code, line_item_id,
1077 company_cost_center_org_id, intercompany_id, financial_elem_id,
1078 product_id, natural_account_id, channel_id, project_id,
1079 customer_id, task_id, user_dim1_id, user_dim2_id, user_dim3_id,
1080 user_dim4_id, user_dim5_id, user_dim6_id, user_dim7_id, user_dim8_id,
1081 user_dim9_id, user_dim10_id, t_amount_dr, t_amount_cr, begin_ytd_dr,
1082 begin_ytd_cr, xlate_ptd_dr, xlate_ptd_cr, xlate_ytd_dr, xlate_ytd_cr)
1083 SELECT /*+ ordered */
1084 decode(fxata.dim_attribute_varchar_member,
1085 ''REVENUE'', p_is_xlate_mode,
1086 ''EXPENSE'', p_is_xlate_mode,
1087 ''EQUITY'', p_eq_xlate_mode,
1088 ''YTD''),
1089 fxata.dim_attribute_varchar_member, fb.line_item_id,';
1090
1091 curr_pos := 1;
1092 body_len := LENGTH(body_block);
1093 WHILE curr_pos <= body_len LOOP
1094 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1095 line_num);
1096 curr_pos := curr_pos + g_line_size;
1097 line_num := line_num + 1;
1098 END LOOP;
1099
1100 line_num := GCS_TRANS_DYN_BUILD_PKG.build_comma_list(' fb.', '', ' NULL,', line_num, 'F');
1101
1102 body_block :=
1103 ' 0, 0, 0, 0,
1104 round(nvl(ghr.translated_amount * 0,
1105 nvl(fb.sum_ytd_debit_balance_e, 0) *
1106 nvl(ghr.translated_rate,
1107 decode(fxata.dim_attribute_varchar_member,
1108 ''REVENUE'', is_rate,
1109 ''EXPENSE'', is_rate,
1110 ''EQUITY'', eq_rate,
1111 p_end_rate))) /
1112 p_round_factor) *
1113 p_round_factor,
1114 round(nvl(ghr.translated_amount,
1115 nvl(fb.sum_ytd_credit_balance_e, 0) *
1116 nvl(ghr.translated_rate,
1117 decode(fxata.dim_attribute_varchar_member,
1118 ''REVENUE'', is_rate,
1119 ''EXPENSE'', is_rate,
1120 ''EQUITY'', eq_rate,
1121 p_end_rate))) /
1122 p_round_factor) *
1123 p_round_factor,
1124 round(nvl(ghr.translated_amount * 0,
1125 nvl(fb.sum_ytd_debit_balance_e, 0) *
1126 nvl(ghr.translated_rate,
1127 decode(fxata.dim_attribute_varchar_member,
1128 ''REVENUE'', is_rate,
1129 ''EXPENSE'', is_rate,
1130 ''EQUITY'', eq_rate,
1131 p_end_rate))) /
1132 p_round_factor) *
1133 p_round_factor,
1134 round(nvl(ghr.translated_amount,
1135 nvl(fb.sum_ytd_credit_balance_e, 0) *
1136 nvl(ghr.translated_rate,
1137 decode(fxata.dim_attribute_varchar_member,
1138 ''REVENUE'', is_rate,
1139 ''EXPENSE'', is_rate,
1140 ''EQUITY'', eq_rate,
1141 p_end_rate))) /
1142 p_round_factor) *
1143 p_round_factor
1144 FROM (SELECT
1145 fb_in.line_item_id,';
1146
1147 curr_pos := 1;
1148 body_len := LENGTH(body_block);
1149 WHILE curr_pos <= body_len LOOP
1150 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1151 line_num);
1152 curr_pos := curr_pos + g_line_size;
1153 line_num := line_num + 1;
1154 END LOOP;
1155
1156 line_num := GCS_TRANS_DYN_BUILD_PKG.build_comma_list(' fb_in.', '', '', line_num, 'F');
1157
1158 --Bugfix 6111815: Removed Added Standard RE Rate Flag
1159 body_block :=
1163 WHERE fb_in.dataset_code = p_hier_dataset_code
1160 ' ytd_debit_balance_e sum_ytd_debit_balance_e,
1161 ytd_credit_balance_e sum_ytd_credit_balance_e
1162 FROM FEM_BALANCES fb_in
1164 AND fb_in.cal_period_id = p_cal_period_id
1165 AND fb_in.source_system_code = p_source_system_code
1166 AND fb_in.currency_code = p_from_ccy
1167 AND fb_in.ledger_id = p_ledger_id
1168 AND fb_in.entity_id = p_entity_id) fb,
1169 FEM_LN_ITEMS_ATTR li,
1170 FEM_EXT_ACCT_TYPES_ATTR fxata,
1171 GCS_HISTORICAL_RATES ghr
1172 WHERE li.line_item_id = fb.line_item_id
1173 AND li.attribute_id = gcs_translation_pkg.g_li_acct_type_attr_id
1174 AND li.version_id = gcs_translation_pkg.g_li_acct_type_v_id
1175 AND fxata.ext_account_type_code = li.dim_attribute_varchar_member
1176 AND fxata.attribute_id = gcs_translation_pkg.g_xat_basic_acct_type_attr_id
1177 AND fxata.version_id = gcs_translation_pkg.g_xat_basic_acct_type_v_id
1178 AND ghr.entity_id = p_entity_id
1179 AND ghr.hierarchy_id = p_hierarchy_id
1180 AND ghr.from_currency = p_from_ccy
1181 AND ghr.to_currency = p_to_ccy
1182 AND ghr.cal_period_id = p_cal_period_id
1183 AND ghr.line_item_id = fb.line_item_id
1184 AND ghr.update_flag = ''N''
1185 AND ghr.standard_re_rate_flag = ''Y''
1186 AND fb.line_item_id = p_hier_li_id';
1187
1188 curr_pos := 1;
1189 body_len := LENGTH(body_block);
1190 WHILE curr_pos <= body_len LOOP
1191 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1192 line_num);
1193 curr_pos := curr_pos + g_line_size;
1194 line_num := line_num + 1;
1195 END LOOP;
1196
1197 --Bugfix 6111815: Removed Outer Join
1198 line_num := GCS_TRANS_DYN_BUILD_PKG.build_join_list(' AND ghr.', ' = fb.', '', line_num, 'F');
1199
1200 body_block :=
1201 ' ;
1202 END IF;
1203
1204 --Bugfix 6111815: Do not check if any data was translated
1205 /*
1206 -- No data was found to translate.
1207 IF SQL%ROWCOUNT = 0 THEN
1208 raise GCS_CCY_NO_DATA;
1209 END IF;
1210 */
1211 write_to_log(module, FND_LOG.LEVEL_PROCEDURE,g_module_success);
1212 EXCEPTION
1213 WHEN GCS_CCY_NO_DATA THEN
1214 FND_MESSAGE.set_name(''GCS'', ''GCS_CCY_NO_TRANSLATE_DATA_ERR'');
1215 GCS_TRANSLATION_PKG.g_error_text := FND_MESSAGE.get;
1216 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, GCS_TRANSLATION_PKG.g_error_text);
1217 module_log_write(module, g_module_failure);
1218 raise GCS_TRANSLATION_PKG.GCS_CCY_SUBPROGRAM_RAISED;
1219 WHEN OTHERS THEN
1220 FND_MESSAGE.set_name(''GCS'', ''GCS_CCY_FIRST_UNEXPECTED_ERR'');
1221 GCS_TRANSLATION_PKG.g_error_text := FND_MESSAGE.get;
1222 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, GCS_TRANSLATION_PKG.g_error_text);
1223 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, SQLERRM);
1224 module_log_write(module, g_module_failure);
1225 raise GCS_TRANSLATION_PKG.GCS_CCY_SUBPROGRAM_RAISED;
1226 END Trans_RE_First_Per;
1227
1228
1229 --
1230 PROCEDURE Trans_RE_Subseq_Per
1231 (p_hier_dataset_code NUMBER,
1232 p_cal_period_id NUMBER,
1233 p_prev_period_id NUMBER,
1234 p_entity_id NUMBER,
1235 p_hierarchy_id NUMBER,
1236 p_ledger_id NUMBER,
1237 p_from_ccy VARCHAR2,
1238 p_to_ccy VARCHAR2,
1239 p_eq_xlate_mode VARCHAR2,
1240 p_is_xlate_mode VARCHAR2,
1241 p_avg_rate NUMBER,
1242 p_end_rate NUMBER,
1243 p_group_by_flag VARCHAR2,
1244 p_round_factor NUMBER,
1245 p_source_system_code NUMBER,
1246 p_hier_li_id NUMBER) IS
1247
1248 -- Rate to use for equity accounts, income statement accounts if there
1249 -- are no historical rates defined.
1250 eq_rate NUMBER;
1251 is_rate NUMBER;
1252
1253 fb_object_id NUMBER;
1254 CURSOR get_object_id IS
1255 SELECT cb.associated_object_id
1256 FROM gcs_categories_b cb
1257 WHERE cb.category_code = ''TRANSLATION'';
1258
1259 module VARCHAR2(50) := ''TRANS_RE_SUBSEQ_PER'';
1260 BEGIN
1261 module_log_write(module, g_module_enter);
1262
1263 IF p_eq_xlate_mode = ''YTD'' THEN
1264 eq_rate := p_end_rate;
1265 ELSE
1266 eq_rate := p_avg_rate;
1267 END IF;
1268
1269 IF p_is_xlate_mode = ''YTD'' THEN
1270 is_rate := p_end_rate;
1271 ELSE
1272 is_rate := p_avg_rate;
1273 END IF;
1274
1275 OPEN get_object_id;
1276 FETCH get_object_id INTO fb_object_id;
1277 CLOSE get_object_id;
1278
1279 IF p_group_by_flag = ''Y'' THEN
1280 write_to_log(module, FND_LOG.LEVEL_STATEMENT,
1281 ''INSERT /*+ parallel (GCS_TRANSLATION_GT) */ INTO GCS_TRANSLATION_GT(translate_rule_code, account_type_code, '' ||
1282 ''line_item_id, company_cost_center_org_id, '' ||
1283 ''intercompany_id, financial_elem_id, product_id, '' ||
1284 ''natural_account_id, channel_id, project_id, customer_id, task_id, '' ||
1285 ''user_dim1_id, user_dim2_id, user_dim3_id, user_dim4_id, user_dim5_id, '' ||
1286 ''user_dim6_id, user_dim7_id, user_dim8_id, user_dim9_id, user_dim10_id, '' ||
1290 ''decode(fxata.dim_attribute_varchar_member,'' || g_nl ||
1287 ''t_amount_dr, t_amount_cr, begin_ytd_dr, begin_ytd_cr, xlate_ptd_dr, '' ||
1288 ''xlate_ptd_cr, xlate_ytd_dr,xlate_ytd_cr)'' || g_nl ||
1289 ''SELECT'' || g_nl ||
1291 '' ''''REVENUE'''', '''''' || p_is_xlate_mode || '''''','' || g_nl ||
1292 '' ''''EXPENSE'''', '''''' || p_is_xlate_mode || '''''','' || g_nl ||
1293 '' ''''EQUITY'''', '''''' || p_eq_xlate_mode || '''''','' || g_nl ||
1294 '' ''''YTD''''),'' || g_nl ||
1295 ''fxata.dim_attribute_varchar_member, fb.line_item_id, '' ||';
1296
1297 curr_pos := 1;
1298 body_len := LENGTH(body_block);
1299 WHILE curr_pos <= body_len LOOP
1300 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1301 line_num);
1302 curr_pos := curr_pos + g_line_size;
1303 line_num := line_num + 1;
1304 END LOOP;
1305
1306 line_num := GCS_TRANS_DYN_BUILD_PKG.build_comma_list('''fb.', ' '' ||', '''NULL, '' ||', line_num, 'F');
1307
1308 body_block :=
1309 'g_nl ||
1310 ''round(nvl(ghr.translated_amount * 0,'' || g_nl ||
1311 '' nvl(decode(fxata.dim_attribute_varchar_member,'' || g_nl ||
1312 '' ''''REVENUE'''', decode('''''' || p_is_xlate_mode || '''''','' || g_nl ||
1313 '' ''''YTD'''', fb.sum_ytd_debit_balance_e,'' || g_nl ||
1314 '' fb.sum_ptd_debit_balance_e),'' || g_nl ||
1315 '' ''''EXPENSE'''', decode('''''' || p_is_xlate_mode || '''''','' || g_nl ||
1316 '' ''''YTD'''', fb.sum_ytd_debit_balance_e,'' || g_nl ||
1317 '' fb.sum_ptd_debit_balance_e),'' || g_nl ||
1318 '' ''''EQUITY'''', decode('''''' || p_eq_xlate_mode || '''''','' || g_nl ||
1319 '' ''''YTD'''', fb.sum_ytd_debit_balance_e,'' || g_nl ||
1320 '' fb.sum_ptd_debit_balance_e),'' || g_nl ||
1321 '' fb.sum_ytd_debit_balance_e),'' || g_nl ||
1322 '' 0) *'' || g_nl ||
1323 '' nvl(ghr.translated_rate,'' || g_nl ||
1324 '' decode(fxata.dim_attribute_varchar_member,'' || g_nl ||
1325 '' ''''REVENUE'''', '' || is_rate || '','' || g_nl ||
1326 '' ''''EXPENSE'''', '' || is_rate || '','' || g_nl ||
1327 '' ''''EQUITY'''', '' || eq_rate || '','' || g_nl ||
1328 '' '' || p_end_rate || ''))) /'' || g_nl ||
1329 '' '' || p_round_factor || '') *'' || g_nl ||
1330 p_round_factor || '','' || g_nl ||
1331 ''round(nvl(ghr.translated_amount,'' || g_nl ||
1332 '' nvl(decode(fxata.dim_attribute_varchar_member,'' || g_nl ||
1333 '' ''''REVENUE'''', decode('''''' || p_is_xlate_mode || '''''','' || g_nl ||
1334 '' ''''YTD'''', fb.sum_ytd_credit_balance_e,'' || g_nl ||
1335 '' fb.sum_ptd_credit_balance_e),'' || g_nl ||
1336 '' ''''EXPENSE'''', decode('''''' || p_is_xlate_mode || '''''','' || g_nl ||
1337 '' ''''YTD'''', fb.sum_ytd_credit_balance_e,'' || g_nl ||
1338 '' fb.sum_ptd_credit_balance_e),'' || g_nl ||
1339 '' ''''EQUITY'''', decode('''''' || p_eq_xlate_mode || '''''','' || g_nl ||
1340 '' ''''YTD'''', fb.sum_ytd_credit_balance_e,'' || g_nl ||
1341 '' fb.sum_ptd_credit_balance_e),'' || g_nl ||
1342 '' fb.sum_ytd_credit_balance_e),'' || g_nl ||
1343 '' 0) *'' || g_nl ||
1344 '' nvl(ghr.translated_rate,'' || g_nl ||
1345 '' decode(fxata.dim_attribute_varchar_member,'' || g_nl ||
1346 '' ''''REVENUE'''', '' || is_rate || '','' || g_nl ||
1347 '' ''''EXPENSE'''', '' || is_rate || '','' || g_nl ||
1348 '' ''''EQUITY'''', '' || eq_rate || '','' || g_nl ||
1349 '' '' || p_end_rate || ''))) /'' || g_nl ||
1350 '' '' || p_round_factor || '') *'' || g_nl ||
1351 p_round_factor || '','' || g_nl ||
1352 ''nvl(fbp.ytd_debit_balance_e,0),'' || g_nl ||
1353 ''nvl(fbp.ytd_credit_balance_e,0), 0, 0, 0, 0'' || g_nl ||
1354 ''FROM (SELECT'' || g_nl ||
1355 '' fb_in.line_item_id,'' || g_nl ||';
1356
1357 curr_pos := 1;
1358 body_len := LENGTH(body_block);
1359 WHILE curr_pos <= body_len LOOP
1360 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1361 line_num);
1362 curr_pos := curr_pos + g_line_size;
1363 line_num := line_num + 1;
1364 END LOOP;
1365
1366 line_num := GCS_TRANS_DYN_BUILD_PKG.build_comma_list(''' fb_in.', ' '' ||', '', line_num, 'F');
1367
1368 body_block :=
1369 ''' SUM(ptd_debit_balance_e) sum_ptd_debit_balance_e,'' || g_nl ||
1370 '' SUM(ptd_credit_balance_e) sum_ptd_credit_balance_e,'' || g_nl ||
1371 '' SUM(ytd_debit_balance_e) sum_ytd_debit_balance_e,'' || g_nl ||
1372 '' SUM(ytd_credit_balance_e) sum_ytd_credit_balance_e'' || g_nl ||
1373 '' FROM FEM_BALANCES fb_in'' || g_nl ||
1374 '' WHERE fb_in.dataset_code = '' || p_hier_dataset_code || g_nl ||
1375 '' AND fb_in.cal_period_id = '' || p_cal_period_id || g_nl ||
1376 '' AND fb_in.source_system_code = '' || p_source_system_code || g_nl ||
1377 '' AND fb_in.currency_code = '''''' || p_from_ccy || '''''''' || g_nl ||
1378 '' AND fb_in.ledger_id = '' || p_ledger_id || g_nl ||
1379 '' AND fb_in.entity_id = '' || p_entity_id || g_nl ||
1380 '' GROUP BY '' || g_nl ||';
1381
1382 curr_pos := 1;
1383 body_len := LENGTH(body_block);
1387 curr_pos := curr_pos + g_line_size;
1384 WHILE curr_pos <= body_len LOOP
1385 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1386 line_num);
1388 line_num := line_num + 1;
1389 END LOOP;
1390
1391 line_num := GCS_TRANS_DYN_BUILD_PKG.build_comma_list(''' fb_in.', ' '' ||', '', line_num, 'F');
1392
1393 body_block :=
1394 ''' fb_in.line_item_id) fb,'' || g_nl ||
1395 '' FEM_BALANCES fbp,'' || g_nl ||
1396 '' GCS_HISTORICAL_RATES ghr,'' || g_nl ||
1397 '' FEM_LN_ITEMS_ATTR li,'' || g_nl ||
1398 '' FEM_EXT_ACCT_TYPES_ATTR fxata'' || g_nl ||
1399 ''WHERE fbp.created_by_object_id (+)= '' || fb_object_id || g_nl ||
1400 ''AND li.line_item_id = fb.line_item_id'' || g_nl ||
1401 ''AND li.attribute_id = '' || gcs_translation_pkg.g_li_acct_type_attr_id || g_nl ||
1402 ''AND li.version_id = '' || gcs_translation_pkg.g_li_acct_type_v_id || g_nl ||
1403 ''AND fxata.ext_account_type_code = li.dim_attribute_varchar_member'' || g_nl ||
1404 ''AND fxata.attribute_id = '' || gcs_translation_pkg.g_xat_basic_acct_type_attr_id || g_nl ||
1405 ''AND fxata.version_id = '' || gcs_translation_pkg.g_xat_basic_acct_type_v_id || g_nl ||
1406 ''AND fbp.dataset_code (+)= '' || p_hier_dataset_code || g_nl ||
1407 ''AND fbp.cal_period_id (+)= '' || p_prev_period_id || g_nl ||
1408 ''AND fbp.source_system_code (+)= '' || p_source_system_code || g_nl ||
1409 ''AND fbp.currency_code (+)= '''''' || p_to_ccy || '''''''' || g_nl ||
1410 ''AND fbp.ledger_id (+)= '' || p_ledger_id || g_nl ||
1411 ''AND fbp.entity_id (+)= '' || p_entity_id || g_nl ||
1412 ''AND fbp.line_item_id (+)= fb.line_item_id'' || g_nl ||
1413 ''AND fb.line_item_id = p_hier_li_id '' || g_nl ||';
1414
1415 curr_pos := 1;
1416 body_len := LENGTH(body_block);
1417 WHILE curr_pos <= body_len LOOP
1418 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1419 line_num);
1420 curr_pos := curr_pos + g_line_size;
1421 line_num := line_num + 1;
1422 END LOOP;
1423
1424 line_num := GCS_TRANS_DYN_BUILD_PKG.build_join_list('''AND fbp.', ' (+)= fb.', ''' || g_nl ||', line_num, 'F');
1425
1426 --Bugfix 6111815: Added Standard RE Rate Flag
1427 body_block :=
1428 '''AND ghr.entity_id = '' || p_entity_id || g_nl ||
1429 ''AND ghr.hierarchy_id = '' || p_hierarchy_id || g_nl ||
1430 ''AND ghr.from_currency = '''''' || p_from_ccy || '''''''' || g_nl ||
1431 ''AND ghr.to_currency = '''''' || p_to_ccy || '''''''' || g_nl ||
1432 ''AND ghr.cal_period_id = '' || p_cal_period_id || g_nl ||
1433 ''AND ghr.line_item_id = fb.line_item_id'' || g_nl ||
1434 ''AND ghr.standard_re_rate_flag = ''''Y'''' '' || g_nl ||
1435 ''AND ghr.update_flag = ''''N'''''' || g_nl ||';
1436
1437 curr_pos := 1;
1438 body_len := LENGTH(body_block);
1439 WHILE curr_pos <= body_len LOOP
1440 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1441 line_num);
1442 curr_pos := curr_pos + g_line_size;
1443 line_num := line_num + 1;
1444 END LOOP;
1445
1446 line_num := GCS_TRANS_DYN_BUILD_PKG.build_join_list('''AND ghr.', ' = fb.', ''' || g_nl ||', line_num, 'F');
1447
1448 body_block :=
1449 ''''');
1450
1451 INSERT /*+ parallel (GCS_TRANSLATION_GT) */ INTO GCS_TRANSLATION_GT(
1452 translate_rule_code, account_type_code, line_item_id,
1453 company_cost_center_org_id, intercompany_id, financial_elem_id,
1454 product_id, natural_account_id, channel_id, project_id,
1455 customer_id, task_id, user_dim1_id, user_dim2_id, user_dim3_id,
1456 user_dim4_id, user_dim5_id, user_dim6_id, user_dim7_id, user_dim8_id,
1457 user_dim9_id, user_dim10_id, t_amount_dr, t_amount_cr, begin_ytd_dr,
1458 begin_ytd_cr, xlate_ptd_dr, xlate_ptd_cr, xlate_ytd_dr,xlate_ytd_cr)
1459 SELECT
1460 decode(fxata.dim_attribute_varchar_member,
1461 ''REVENUE'', p_is_xlate_mode,
1462 ''EXPENSE'', p_is_xlate_mode,
1463 ''EQUITY'', p_eq_xlate_mode,
1464 ''YTD''),
1465 fxata.dim_attribute_varchar_member, fb.line_item_id,';
1466
1467 curr_pos := 1;
1468 body_len := LENGTH(body_block);
1469 WHILE curr_pos <= body_len LOOP
1470 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1471 line_num);
1472 curr_pos := curr_pos + g_line_size;
1473 line_num := line_num + 1;
1474 END LOOP;
1475
1476 line_num := GCS_TRANS_DYN_BUILD_PKG.build_comma_list(' fb.', '', ' NULL,', line_num, 'F');
1477
1478 body_block :=
1479 ' round(nvl(ghr.translated_amount * 0,
1480 nvl(decode(fxata.dim_attribute_varchar_member,
1481 ''REVENUE'', decode(p_is_xlate_mode,
1482 ''YTD'', fb.sum_ytd_debit_balance_e,
1483 fb.sum_ptd_debit_balance_e),
1484 ''EXPENSE'', decode(p_is_xlate_mode,
1485 ''YTD'', fb.sum_ytd_debit_balance_e,
1486 fb.sum_ptd_debit_balance_e),
1487 ''EQUITY'', decode(p_eq_xlate_mode,
1488 ''YTD'', fb.sum_ytd_debit_balance_e,
1489 fb.sum_ptd_debit_balance_e),
1490 fb.sum_ytd_debit_balance_e),
1491 0) *
1492 nvl(ghr.translated_rate,
1493 decode(fxata.dim_attribute_varchar_member,
1497 p_end_rate))) /
1494 ''REVENUE'', is_rate,
1495 ''EXPENSE'', is_rate,
1496 ''EQUITY'', eq_rate,
1498 p_round_factor) *
1499 p_round_factor,
1500 round(nvl(ghr.translated_amount,
1501 nvl(decode(fxata.dim_attribute_varchar_member,
1502 ''REVENUE'', decode(p_is_xlate_mode,
1503 ''YTD'', fb.sum_ytd_credit_balance_e,
1504 fb.sum_ptd_credit_balance_e),
1505 ''EXPENSE'', decode(p_is_xlate_mode,
1506 ''YTD'', fb.sum_ytd_credit_balance_e,
1507 fb.sum_ptd_credit_balance_e),
1508 ''EQUITY'', decode(p_eq_xlate_mode,
1509 ''YTD'', fb.sum_ytd_credit_balance_e,
1510 fb.sum_ptd_credit_balance_e),
1511 fb.sum_ytd_credit_balance_e),
1512 0) *
1513 nvl(ghr.translated_rate,
1514 decode(fxata.dim_attribute_varchar_member,
1515 ''REVENUE'', is_rate,
1516 ''EXPENSE'', is_rate,
1517 ''EQUITY'', eq_rate,
1518 p_end_rate))) /
1519 p_round_factor) *
1520 p_round_factor,
1521 nvl(fbp.ytd_debit_balance_e,0),
1522 nvl(fbp.ytd_credit_balance_e,0), 0,0,0,0
1523 FROM (SELECT
1524 fb_in.line_item_id,';
1525
1526 curr_pos := 1;
1527 body_len := LENGTH(body_block);
1528 WHILE curr_pos <= body_len LOOP
1529 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1530 line_num);
1531 curr_pos := curr_pos + g_line_size;
1532 line_num := line_num + 1;
1533 END LOOP;
1534
1535 line_num := GCS_TRANS_DYN_BUILD_PKG.build_comma_list(' fb_in.', '', '', line_num, 'F');
1536
1537 body_block :=
1538 ' SUM(ptd_debit_balance_e) sum_ptd_debit_balance_e,
1539 SUM(ptd_credit_balance_e) sum_ptd_credit_balance_e,
1540 SUM(ytd_debit_balance_e) sum_ytd_debit_balance_e,
1541 SUM(ytd_credit_balance_e) sum_ytd_credit_balance_e
1542 FROM FEM_BALANCES fb_in
1543 WHERE fb_in.dataset_code = p_hier_dataset_code
1544 AND fb_in.cal_period_id = p_cal_period_id
1545 AND fb_in.source_system_code = p_source_system_code
1546 AND fb_in.currency_code = p_from_ccy
1547 AND fb_in.ledger_id = p_ledger_id
1548 AND fb_in.entity_id = p_entity_id
1549 GROUP BY';
1550
1551 curr_pos := 1;
1552 body_len := LENGTH(body_block);
1553 WHILE curr_pos <= body_len LOOP
1554 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1555 line_num);
1556 curr_pos := curr_pos + g_line_size;
1557 line_num := line_num + 1;
1558 END LOOP;
1559
1560 line_num := GCS_TRANS_DYN_BUILD_PKG.build_comma_list(' fb_in.', '', '', line_num, 'F');
1561
1562 body_block :=
1563 ' fb_in.line_item_id) fb,
1564 FEM_BALANCES fbp,
1565 GCS_HISTORICAL_RATES ghr,
1566 FEM_LN_ITEMS_ATTR li,
1567 FEM_EXT_ACCT_TYPES_ATTR fxata
1568 WHERE fbp.created_by_object_id (+)= fb_object_id
1569 AND li.line_item_id = fb.line_item_id
1570 AND li.attribute_id = gcs_translation_pkg.g_li_acct_type_attr_id
1571 AND li.version_id = gcs_translation_pkg.g_li_acct_type_v_id
1572 AND fxata.ext_account_type_code = li.dim_attribute_varchar_member
1573 AND fxata.attribute_id = gcs_translation_pkg.g_xat_basic_acct_type_attr_id
1574 AND fxata.version_id = gcs_translation_pkg.g_xat_basic_acct_type_v_id
1575 AND fbp.dataset_code (+)= p_hier_dataset_code
1576 AND fbp.cal_period_id (+)= p_prev_period_id
1577 AND fbp.source_system_code (+)= p_source_system_code
1578 AND fbp.currency_code (+)= p_to_ccy
1579 AND fbp.ledger_id (+)= p_ledger_id
1580 AND fbp.entity_id (+)= p_entity_id
1581 AND fbp.line_item_id (+)= fb.line_item_id
1582 AND fb.line_item_id = p_hier_li_id';
1583
1584 curr_pos := 1;
1585 body_len := LENGTH(body_block);
1586 WHILE curr_pos <= body_len LOOP
1587 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1588 line_num);
1589 curr_pos := curr_pos + g_line_size;
1590 line_num := line_num + 1;
1591 END LOOP;
1592
1593 line_num := GCS_TRANS_DYN_BUILD_PKG.build_join_list(' AND fbp.', ' (+)= fb.', '', line_num, 'F');
1594
1595 --Bugfix 6111815: Added Standard RE Rate Flag
1596 body_block :=
1597 ' AND ghr.entity_id = p_entity_id
1598 AND ghr.hierarchy_id = p_hierarchy_id
1599 AND ghr.from_currency = p_from_ccy
1600 AND ghr.to_currency = p_to_ccy
1601 AND ghr.cal_period_id = p_cal_period_id
1602 AND ghr.line_item_id = fb.line_item_id
1603 AND ghr.standard_re_rate_flag = ''Y''
1604 AND ghr.update_flag = ''N''';
1605
1606 curr_pos := 1;
1607 body_len := LENGTH(body_block);
1608 WHILE curr_pos <= body_len LOOP
1609 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1613 END LOOP;
1610 line_num);
1611 curr_pos := curr_pos + g_line_size;
1612 line_num := line_num + 1;
1614
1615 line_num := GCS_TRANS_DYN_BUILD_PKG.build_join_list(' AND ghr.', ' = fb.', '', line_num, 'F');
1616
1617 body_block :=
1618 ' ;
1619
1620 ELSE
1621 write_to_log(module, FND_LOG.LEVEL_STATEMENT,
1622 ''INSERT /*+ parallel (GCS_TRANSLATION_GT) */ INTO GCS_TRANSLATION_GT(translate_rule_code, account_type_code, '' ||
1623 ''line_item_id, company_cost_center_org_id, '' ||
1624 ''intercompany_id, financial_elem_id, product_id, '' ||
1625 ''natural_account_id, channel_id, project_id, customer_id, task_id, '' ||
1626 ''user_dim1_id, user_dim2_id, user_dim3_id, user_dim4_id, user_dim5_id, '' ||
1627 ''user_dim6_id, user_dim7_id, user_dim8_id, user_dim9_id, user_dim10_id, '' ||
1628 ''t_amount_dr, t_amount_cr, begin_ytd_dr, begin_ytd_cr, xlate_ptd_dr, '' ||
1629 ''xlate_ptd_cr, xlate_ytd_dr,xlate_ytd_cr)'' || g_nl ||
1630 ''SELECT'' || g_nl ||
1631 ''decode(fxata.dim_attribute_varchar_member,'' || g_nl ||
1632 '' ''''REVENUE'''', '''''' || p_is_xlate_mode || '''''','' || g_nl ||
1633 '' ''''EXPENSE'''', '''''' || p_is_xlate_mode || '''''','' || g_nl ||
1634 '' ''''EQUITY'''', '''''' || p_eq_xlate_mode || '''''','' || g_nl ||
1635 '' ''''YTD''''),'' || g_nl ||
1636 ''fxata.dim_attribute_varchar_member, fb.line_item_id, '' ||';
1637
1638 curr_pos := 1;
1639 body_len := LENGTH(body_block);
1640 WHILE curr_pos <= body_len LOOP
1641 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1642 line_num);
1643 curr_pos := curr_pos + g_line_size;
1644 line_num := line_num + 1;
1645 END LOOP;
1646
1647 line_num := GCS_TRANS_DYN_BUILD_PKG.build_comma_list('''fb.', ' '' ||', '''NULL, '' ||', line_num, 'F');
1648
1649 body_block :=
1650 'g_nl ||
1651 ''round(nvl(ghr.translated_amount * 0,'' || g_nl ||
1652 '' nvl(decode(fxata.dim_attribute_varchar_member,'' || g_nl ||
1653 '' ''''REVENUE'''', decode('''''' || p_is_xlate_mode || '''''','' || g_nl ||
1654 '' ''''YTD'''', fb.sum_ytd_debit_balance_e,'' || g_nl ||
1655 '' fb.sum_ptd_debit_balance_e),'' || g_nl ||
1656 '' ''''EXPENSE'''', decode('''''' || p_is_xlate_mode || '''''','' || g_nl ||
1657 '' ''''YTD'''', fb.sum_ytd_debit_balance_e,'' || g_nl ||
1658 '' fb.sum_ptd_debit_balance_e),'' || g_nl ||
1659 '' ''''EQUITY'''', decode('''''' || p_eq_xlate_mode || '''''','' || g_nl ||
1660 '' ''''YTD'''', fb.sum_ytd_debit_balance_e,'' || g_nl ||
1661 '' fb.sum_ptd_debit_balance_e),'' || g_nl ||
1662 '' fb.sum_ytd_debit_balance_e),'' || g_nl ||
1663 '' 0) *'' || g_nl ||
1664 '' nvl(ghr.translated_rate,'' || g_nl ||
1665 '' decode(fxata.dim_attribute_varchar_member,'' || g_nl ||
1666 '' ''''REVENUE'''', '' || is_rate || '','' || g_nl ||
1667 '' ''''EXPENSE'''', '' || is_rate || '','' || g_nl ||
1668 '' ''''EQUITY'''', '' || eq_rate || '','' || g_nl ||
1669 '' '' || p_end_rate || ''))) /'' || g_nl ||
1670 '' '' || p_round_factor || '') *'' || g_nl ||
1671 p_round_factor || '','' || g_nl ||
1672 ''round(nvl(ghr.translated_amount,'' || g_nl ||
1673 '' nvl(decode(fxata.dim_attribute_varchar_member,'' || g_nl ||
1674 '' ''''REVENUE'''', decode('''''' || p_is_xlate_mode || '''''','' || g_nl ||
1675 '' ''''YTD'''', fb.sum_ytd_credit_balance_e,'' || g_nl ||
1676 '' fb.sum_ptd_credit_balance_e),'' || g_nl ||
1677 '' ''''EXPENSE'''', decode('''''' || p_is_xlate_mode || '''''','' || g_nl ||
1678 '' ''''YTD'''', fb.sum_ytd_credit_balance_e,'' || g_nl ||
1679 '' fb.sum_ptd_credit_balance_e),'' || g_nl ||
1680 '' ''''EQUITY'''', decode('''''' || p_eq_xlate_mode || '''''','' || g_nl ||
1681 '' ''''YTD'''', fb.sum_ytd_credit_balance_e,'' || g_nl ||
1682 '' fb.sum_ptd_credit_balance_e),'' || g_nl ||
1683 '' fb.sum_ytd_credit_balance_e),'' || g_nl ||
1684 '' 0) *'' || g_nl ||
1685 '' nvl(ghr.translated_rate,'' || g_nl ||
1686 '' decode(fxata.dim_attribute_varchar_member,'' || g_nl ||
1687 '' ''''REVENUE'''', '' || is_rate || '','' || g_nl ||
1688 '' ''''EXPENSE'''', '' || is_rate || '','' || g_nl ||
1689 '' ''''EQUITY'''', '' || eq_rate || '','' || g_nl ||
1690 '' '' || p_end_rate || ''))) /'' || g_nl ||
1691 '' '' || p_round_factor || '') *'' || g_nl ||
1692 p_round_factor || '','' || g_nl ||
1693 ''nvl(fbp.ytd_debit_balance_e,0),'' || g_nl ||
1694 ''nvl(fbp.ytd_credit_balance_e,0), 0, 0, 0, 0'' || g_nl ||
1695 ''FROM (SELECT'' || g_nl ||
1696 '' fb_in.line_item_id,'' || g_nl ||';
1697
1698 curr_pos := 1;
1699 body_len := LENGTH(body_block);
1700 WHILE curr_pos <= body_len LOOP
1701 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1702 line_num);
1703 curr_pos := curr_pos + g_line_size;
1704 line_num := line_num + 1;
1705 END LOOP;
1706
1707 line_num := GCS_TRANS_DYN_BUILD_PKG.build_comma_list(''' fb_in.', ' '' ||', '', line_num, 'F');
1708
1709 body_block :=
1710 ''' ptd_debit_balance_e sum_ptd_debit_balance_e,'' || g_nl ||
1714 '' FROM FEM_BALANCES fb_in'' || g_nl ||
1711 '' ptd_credit_balance_e sum_ptd_credit_balance_e,'' || g_nl ||
1712 '' ytd_debit_balance_e sum_ytd_debit_balance_e,'' || g_nl ||
1713 '' ytd_credit_balance_e sum_ytd_credit_balance_e'' || g_nl ||
1715 '' WHERE fb_in.dataset_code = '' || p_hier_dataset_code || g_nl ||
1716 '' AND fb_in.cal_period_id = '' || p_cal_period_id || g_nl ||
1717 '' AND fb_in.source_system_code = '' || p_source_system_code || g_nl ||
1718 '' AND fb_in.currency_code = '''''' || p_from_ccy || '''''''' || g_nl ||
1719 '' AND fb_in.ledger_id = '' || p_ledger_id || g_nl ||
1720 '' AND fb_in.entity_id = '' || p_entity_id || '') fb,'' || g_nl ||
1721 '' FEM_BALANCES fbp,'' || g_nl ||
1722 '' GCS_HISTORICAL_RATES ghr,'' || g_nl ||
1723 '' FEM_LN_ITEMS_ATTR li,'' || g_nl ||
1724 '' FEM_EXT_ACCT_TYPES_ATTR fxata'' || g_nl ||
1725 ''WHERE fbp.created_by_object_id (+)= '' || fb_object_id || g_nl ||
1726 ''AND li.line_item_id = fb.line_item_id'' || g_nl ||
1727 ''AND li.attribute_id = '' || gcs_translation_pkg.g_li_acct_type_attr_id || g_nl ||
1728 ''AND li.version_id = '' || gcs_translation_pkg.g_li_acct_type_v_id || g_nl ||
1729 ''AND fxata.ext_account_type_code = li.dim_attribute_varchar_member'' || g_nl ||
1730 ''AND fxata.attribute_id = '' || gcs_translation_pkg.g_xat_basic_acct_type_attr_id || g_nl ||
1731 ''AND fxata.version_id = '' || gcs_translation_pkg.g_xat_basic_acct_type_v_id || g_nl ||
1732 ''AND fbp.dataset_code (+)= '' || p_hier_dataset_code || g_nl ||
1733 ''AND fbp.cal_period_id (+)= '' || p_prev_period_id || g_nl ||
1734 ''AND fbp.source_system_code (+)= '' || p_source_system_code || g_nl ||
1735 ''AND fbp.currency_code (+)= '''''' || p_to_ccy || '''''''' || g_nl ||
1736 ''AND fbp.ledger_id (+)= '' || p_ledger_id || g_nl ||
1737 ''AND fbp.entity_id (+)= '' || p_entity_id || g_nl ||
1738 ''AND fbp.line_item_id (+)= fb.line_item_id'' || g_nl ||
1739 ''AND fb.line_item_id = p_hier_li_id '' || g_nl ||';
1740
1741 curr_pos := 1;
1742 body_len := LENGTH(body_block);
1743 WHILE curr_pos <= body_len LOOP
1744 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1745 line_num);
1746 curr_pos := curr_pos + g_line_size;
1747 line_num := line_num + 1;
1748 END LOOP;
1749
1750 line_num := GCS_TRANS_DYN_BUILD_PKG.build_join_list('''AND fbp.', ' (+)= fb.', ''' || g_nl ||', line_num, 'F');
1751
1752 --Bugfix 6111815: Added Standard RE Rate Flag
1753 body_block :=
1754 '''AND ghr.entity_id = '' || p_entity_id || g_nl ||
1755 ''AND ghr.hierarchy_id = '' || p_hierarchy_id || g_nl ||
1756 ''AND ghr.from_currency = '''''' || p_from_ccy || '''''''' || g_nl ||
1757 ''AND ghr.to_currency = '''''' || p_to_ccy || '''''''' || g_nl ||
1758 ''AND ghr.cal_period_id = '' || p_cal_period_id || g_nl ||
1759 ''AND ghr.line_item_id = fb.line_item_id'' || g_nl ||
1760 ''AND ghr.standard_re_rate_flag = ''''Y'''' '' || g_nl ||
1761 ''AND ghr.update_flag = ''''N'''''' || g_nl ||';
1762
1763 curr_pos := 1;
1764 body_len := LENGTH(body_block);
1765 WHILE curr_pos <= body_len LOOP
1766 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1767 line_num);
1768 curr_pos := curr_pos + g_line_size;
1769 line_num := line_num + 1;
1770 END LOOP;
1771
1772 --Bugfix 6111815: Removing Outer Join Condition
1773 line_num := GCS_TRANS_DYN_BUILD_PKG.build_join_list('''AND ghr.', ' = fb.', ''' || g_nl ||', line_num, 'F');
1774
1775 body_block :=
1776 ''''');
1777
1778 INSERT /*+ parallel (GCS_TRANSLATION_GT) */ INTO GCS_TRANSLATION_GT(
1779 translate_rule_code, account_type_code, line_item_id,
1780 company_cost_center_org_id, intercompany_id, financial_elem_id,
1781 product_id, natural_account_id, channel_id, project_id,
1782 customer_id, task_id, user_dim1_id, user_dim2_id, user_dim3_id,
1783 user_dim4_id, user_dim5_id, user_dim6_id, user_dim7_id, user_dim8_id,
1784 user_dim9_id, user_dim10_id, t_amount_dr, t_amount_cr, begin_ytd_dr,
1785 begin_ytd_cr, xlate_ptd_dr, xlate_ptd_cr, xlate_ytd_dr,xlate_ytd_cr)
1786 SELECT
1787 decode(fxata.dim_attribute_varchar_member,
1788 ''REVENUE'', p_is_xlate_mode,
1789 ''EXPENSE'', p_is_xlate_mode,
1790 ''EQUITY'', p_eq_xlate_mode,
1791 ''YTD''),
1792 fxata.dim_attribute_varchar_member, fb.line_item_id,';
1793
1794 curr_pos := 1;
1795 body_len := LENGTH(body_block);
1796 WHILE curr_pos <= body_len LOOP
1797 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1798 line_num);
1799 curr_pos := curr_pos + g_line_size;
1800 line_num := line_num + 1;
1801 END LOOP;
1802
1803 line_num := GCS_TRANS_DYN_BUILD_PKG.build_comma_list(' fb.', '', ' NULL,', line_num, 'F');
1804
1805 body_block :=
1806 ' round(nvl(ghr.translated_amount * 0,
1807 nvl(decode(fxata.dim_attribute_varchar_member,
1808 ''REVENUE'', decode(p_is_xlate_mode,
1809 ''YTD'', fb.sum_ytd_debit_balance_e,
1810 fb.sum_ptd_debit_balance_e),
1811 ''EXPENSE'', decode(p_is_xlate_mode,
1812 ''YTD'', fb.sum_ytd_debit_balance_e,
1813 fb.sum_ptd_debit_balance_e),
1814 ''EQUITY'', decode(p_eq_xlate_mode,
1815 ''YTD'', fb.sum_ytd_debit_balance_e,
1819 nvl(ghr.translated_rate,
1816 fb.sum_ptd_debit_balance_e),
1817 fb.sum_ytd_debit_balance_e),
1818 0) *
1820 decode(fxata.dim_attribute_varchar_member,
1821 ''REVENUE'', is_rate,
1822 ''EXPENSE'', is_rate,
1823 ''EQUITY'', eq_rate,
1824 p_end_rate))) /
1825 p_round_factor) *
1826 p_round_factor,
1827 round(nvl(ghr.translated_amount,
1828 nvl(decode(fxata.dim_attribute_varchar_member,
1829 ''REVENUE'', decode(p_is_xlate_mode,
1830 ''YTD'', fb.sum_ytd_credit_balance_e,
1831 fb.sum_ptd_credit_balance_e),
1832 ''EXPENSE'', decode(p_is_xlate_mode,
1833 ''YTD'', fb.sum_ytd_credit_balance_e,
1834 fb.sum_ptd_credit_balance_e),
1835 ''EQUITY'', decode(p_eq_xlate_mode,
1836 ''YTD'', fb.sum_ytd_credit_balance_e,
1837 fb.sum_ptd_credit_balance_e),
1838 fb.sum_ytd_credit_balance_e),
1839 0) *
1840 nvl(ghr.translated_rate,
1841 decode(fxata.dim_attribute_varchar_member,
1842 ''REVENUE'', is_rate,
1843 ''EXPENSE'', is_rate,
1844 ''EQUITY'', eq_rate,
1845 p_end_rate))) /
1846 p_round_factor) *
1847 p_round_factor,
1848 nvl(fbp.ytd_debit_balance_e,0),
1849 nvl(fbp.ytd_credit_balance_e,0), 0,0,0,0
1850 FROM (SELECT
1851 fb_in.line_item_id,';
1852
1853 curr_pos := 1;
1854 body_len := LENGTH(body_block);
1855 WHILE curr_pos <= body_len LOOP
1856 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1857 line_num);
1858 curr_pos := curr_pos + g_line_size;
1859 line_num := line_num + 1;
1860 END LOOP;
1861
1862 line_num := GCS_TRANS_DYN_BUILD_PKG.build_comma_list(' fb_in.', '', '', line_num, 'F');
1863
1864 body_block :=
1865 ' ptd_debit_balance_e sum_ptd_debit_balance_e,
1866 ptd_credit_balance_e sum_ptd_credit_balance_e,
1867 ytd_debit_balance_e sum_ytd_debit_balance_e,
1868 ytd_credit_balance_e sum_ytd_credit_balance_e
1869 FROM FEM_BALANCES fb_in
1870 WHERE fb_in.dataset_code = p_hier_dataset_code
1871 AND fb_in.cal_period_id = p_cal_period_id
1872 AND fb_in.source_system_code = p_source_system_code
1873 AND fb_in.currency_code = p_from_ccy
1874 AND fb_in.ledger_id = p_ledger_id
1875 AND fb_in.entity_id = p_entity_id) fb,
1876 FEM_BALANCES fbp,
1877 GCS_HISTORICAL_RATES ghr,
1878 FEM_LN_ITEMS_ATTR li,
1879 FEM_EXT_ACCT_TYPES_ATTR fxata
1880 WHERE fbp.created_by_object_id (+)= fb_object_id
1881 AND li.line_item_id = fb.line_item_id
1882 AND li.attribute_id = gcs_translation_pkg.g_li_acct_type_attr_id
1883 AND li.version_id = gcs_translation_pkg.g_li_acct_type_v_id
1884 AND fxata.ext_account_type_code = li.dim_attribute_varchar_member
1885 AND fxata.attribute_id = gcs_translation_pkg.g_xat_basic_acct_type_attr_id
1886 AND fxata.version_id = gcs_translation_pkg.g_xat_basic_acct_type_v_id
1887 AND fbp.dataset_code (+)= p_hier_dataset_code
1888 AND fbp.cal_period_id (+)= p_prev_period_id
1889 AND fbp.source_system_code (+)= p_source_system_code
1890 AND fbp.currency_code (+)= p_to_ccy
1891 AND fbp.ledger_id (+)= p_ledger_id
1892 AND fbp.entity_id (+)= p_entity_id
1893 AND fbp.line_item_id (+)= fb.line_item_id
1894 AND fb.line_item_id = p_hier_li_id';
1895
1896 curr_pos := 1;
1897 body_len := LENGTH(body_block);
1898 WHILE curr_pos <= body_len LOOP
1899 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1900 line_num);
1901 curr_pos := curr_pos + g_line_size;
1902 line_num := line_num + 1;
1903 END LOOP;
1904
1905 line_num := GCS_TRANS_DYN_BUILD_PKG.build_join_list(' AND fbp.', ' (+)= fb.', '', line_num, 'F');
1906
1907 --Bugfix 6111815: Removed Outer Joins and Added Standard RE Rate Flag Condition
1908 body_block :=
1909 ' AND ghr.entity_id = p_entity_id
1910 AND ghr.hierarchy_id = p_hierarchy_id
1911 AND ghr.from_currency = p_from_ccy
1912 AND ghr.to_currency = p_to_ccy
1913 AND ghr.cal_period_id = p_cal_period_id
1914 AND ghr.line_item_id = fb.line_item_id
1915 AND ghr.standard_re_rate_flag = ''Y''
1916 AND ghr.update_flag = ''N''';
1917
1918 curr_pos := 1;
1919 body_len := LENGTH(body_block);
1920 WHILE curr_pos <= body_len LOOP
1921 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1922 line_num);
1923 curr_pos := curr_pos + g_line_size;
1924 line_num := line_num + 1;
1925 END LOOP;
1926
1927 line_num := GCS_TRANS_DYN_BUILD_PKG.build_join_list(' AND ghr.', ' = fb.', '', line_num, 'F');
1928
1929 body_block :=
1930 ' ;
1931
1932 END IF;
1933
1934 --Bugfix 6111815: Do not Raise No Data Found anymore
1935 /*
1936 -- No data was found to translate.
1937 IF SQL%ROWCOUNT = 0 THEN
1938 raise GCS_CCY_NO_DATA;
1939 END IF;
1940 */
1941 write_to_log(module, FND_LOG.LEVEL_PROCEDURE,g_module_success);
1942 EXCEPTION
1943 WHEN GCS_CCY_NO_DATA THEN
1944 FND_MESSAGE.set_name(''GCS'', ''GCS_CCY_NO_TRANSLATE_DATA_ERR'');
1945 GCS_TRANSLATION_PKG.g_error_text := FND_MESSAGE.get;
1946 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, GCS_TRANSLATION_PKG.g_error_text);
1947 module_log_write(module, g_module_failure);
1948 raise GCS_TRANSLATION_PKG.GCS_CCY_SUBPROGRAM_RAISED;
1949 WHEN OTHERS THEN
1950 FND_MESSAGE.set_name(''GCS'', ''GCS_CCY_SUBSQ_UNEXPECTED_ERR'');
1951 GCS_TRANSLATION_PKG.g_error_text := FND_MESSAGE.get;
1952 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, GCS_TRANSLATION_PKG.g_error_text);
1953 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, SQLERRM);
1954 module_log_write(module, g_module_failure);
1955 raise GCS_TRANSLATION_PKG.GCS_CCY_SUBPROGRAM_RAISED;
1956 END Trans_RE_Subseq_Per;
1957
1958 END GCS_TRANS_RE_DYNAMIC_PKG;';
1959
1960 curr_pos := 1;
1961 body_len := LENGTH(body_block);
1962 WHILE curr_pos <= body_len LOOP
1963 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1964 line_num);
1965 curr_pos := curr_pos + g_line_size;
1966 line_num := line_num + 1;
1967 END LOOP;
1968
1969 ad_ddl.create_plsql_object(appl, 'APPS', 'GCS_TRANS_RE_DYNAMIC_PKG', 1, line_num - 1, 'FALSE', err);
1970
1971 IF err = 'TRUE' THEN
1972 raise GCS_CCY_DYN_PKG_BUILD_ERR;
1973 END IF;
1974
1975 module_log_write(module, g_module_success);
1976 EXCEPTION
1977 WHEN GCS_CCY_APPLSYS_NOT_FOUND THEN
1978 FND_MESSAGE.SET_NAME('GCS', 'GCS_APPLSYS_NOT_FOUND');
1979 g_error_text := FND_MESSAGE.get;
1980 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
1981 x_errbuf := g_error_text;
1982 x_retcode := '2';
1983 module_log_write(module, g_module_failure);
1984 WHEN GCS_CCY_DYN_PKG_BUILD_ERR THEN
1985 FND_MESSAGE.set_name('GCS', 'GCS_CCY_DYN_PKG_BUILD_ERR');
1986 g_error_text := FND_MESSAGE.get;
1987 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
1988 x_errbuf := g_error_text;
1989 x_retcode := '2';
1990 module_log_write(module, g_module_failure);
1991 WHEN OTHERS THEN
1992 FND_MESSAGE.set_name('GCS', 'GCS_CCY_DYN_PKG_ERR');
1993 g_error_text := FND_MESSAGE.get;
1994 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
1995 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, SQLERRM);
1996 x_errbuf := g_error_text;
1997 x_retcode := '2';
1998 module_log_write(module, g_module_failure);
1999 END Create_Package;
2000
2001 END GCS_TRANS_RE_DYN_BUILD_PKG;
2002