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