[Home] [Help]
PACKAGE BODY: APPS.GCS_TRANS_RE_DYNAMIC_PKG
Source
1 PACKAGE BODY GCS_TRANS_RE_DYNAMIC_PKG AS
2
3 -- The API name
4 g_api VARCHAR2(50) := 'gcs.plsql.GCS_TRANS_RE_DYNAMIC_PKG';
5
6 -- Action types for writing module information to the log file. Used for
7 -- the procedure log_file_module_write.
8 g_module_enter VARCHAR2(2) := '>>';
9 g_module_success VARCHAR2(2) := '<<';
10 g_module_failure VARCHAR2(2) := '<x';
11
12 -- A newline character. Included for convenience when writing long strings.
13 g_nl VARCHAR2(1) := '
14 ';
15
16 --
17 -- PRIVATE EXCEPTIONS
18 --
19 GCS_CCY_NO_DATA EXCEPTION;
20 GCS_CCY_ENTRY_CREATE_FAILED EXCEPTION;
21
22 --
23 -- PRIVATE PROCEDURES/FUNCTIONS
24 --
25
26 --
27 -- Procedure
28 -- Module_Log_Write
29 -- Purpose
30 -- Write the procedure or function entered or exited, and the time that
31 -- this happened. Write it to the log repository.
32 -- Arguments
33 -- p_module Name of the module
34 -- p_action_type Entered, Exited Successfully, or Exited with Failure
35 -- Example
36 -- GCS_TRANS_RE_DYNAMIC_PKG.Module_Log_Write
37 -- Notes
38 --
39 PROCEDURE Module_Log_Write
40 (p_module VARCHAR2,
41 p_action_type VARCHAR2) IS
42 BEGIN
43 -- Only print if the log level is set at the appropriate level
44 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE THEN
45 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, g_api || '.' || p_module,
46 p_action_type || ' ' || p_module || '() ' ||
47 to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
48 END IF;
49 FND_FILE.PUT_LINE(FND_FILE.LOG, p_action_type || ' ' || p_module ||
50 '() ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
51 END Module_Log_Write;
52
53 --
54 -- Procedure
55 -- Write_To_Log
56 -- Purpose
57 -- Write the text given to the log in 3500 character increments
58 -- this happened. Write it to the log repository.
59 -- Arguments
60 -- p_module Name of the module
61 -- p_level Logging level
62 -- p_text Text to write
63 -- Example
64 -- GCS_TRANS_RE_DYNAMICPKG.Write_To_Log
65 -- Notes
66 --
67 PROCEDURE Write_To_Log
68 (p_module VARCHAR2,
69 p_level NUMBER,
70 p_text VARCHAR2)
71 IS
72 api_module_concat VARCHAR2(200);
73 text_with_date VARCHAR2(32767);
74 text_with_date_len NUMBER;
75 curr_index NUMBER;
76 BEGIN
77 -- Only print if the log level is set at the appropriate level
78 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= p_level THEN
79 api_module_concat := g_api || '.' || p_module;
80 text_with_date := to_char(sysdate,'DD-MON-YYYY HH:MI:SS')||g_nl||p_text;
81 text_with_date_len := length(text_with_date);
82 curr_index := 1;
83 WHILE curr_index <= text_with_date_len LOOP
84 fnd_log.string(p_level, api_module_concat,
85 substr(text_with_date, curr_index, 3500));
86 curr_index := curr_index + 3500;
87 END LOOP;
88 END IF;
89 END Write_To_Log;
90
91
92 --
93 -- Public procedures
94
95
96 --
97 PROCEDURE Roll_Forward_Retained_Earnings
98 (p_hier_dataset_code NUMBER,
99 p_source_system_code NUMBER,
100 p_ledger_id NUMBER,
101 p_cal_period_id NUMBER,
102 p_prev_period_id NUMBER,
103 p_entity_id NUMBER,
104 p_hierarchy_id NUMBER,
105 p_from_ccy VARCHAR2,
106 p_to_ccy VARCHAR2,
107 p_eq_xlate_mode VARCHAR2,
108 p_hier_li_id NUMBER) IS
109
110 module VARCHAR2(50) := 'ROLL_FORWARD_RETAINED_EARNINGS';
111 BEGIN
112 write_to_log(module, FND_LOG.LEVEL_PROCEDURE,g_module_enter);
113
114 --Bugfix 6111815: Only Roll Forward Rates Where Standard RE Rate Flag is Y
115 write_to_log(module, FND_LOG.LEVEL_STATEMENT,
116 'UPDATE gcs_historical_rates ghr' || g_nl ||
117 'SET (translated_rate, translated_amount, rate_type_code, ' ||
118 'last_update_date, last_updated_by, last_update_login) =' || g_nl ||
119 ' (SELECT ghr1.translated_rate, ghr1.translated_amount, ''P'', ' ||
120 'sysdate, ' || gcs_translation_pkg.g_fnd_user_id || ', ' ||
121 gcs_translation_pkg.g_fnd_login_id || g_nl ||
122 ' FROM gcs_historical_rates ghr1' || g_nl ||
123 ' WHERE ghr1.entity_id = ghr.entity_id' || g_nl ||
124 ' AND ghr1.hierarchy_id = ghr.hierarchy_id' || g_nl ||
125 ' AND ghr1.from_currency = ghr.from_currency' || g_nl ||
126 ' AND ghr1.to_currency = ghr.to_currency' || g_nl ||
127 ' AND ghr1.line_item_id = ghr.line_item_id' || g_nl ||
128 ' AND ghr1.standard_re_rate_flag = ''Y'' ' || g_nl ||
129 ' AND ghr1.line_item_id = p_hier_li_id ' || g_nl ||' AND ghr1.cal_period_id = ' || p_prev_period_id || ')' || g_nl ||
130 'WHERE ghr.rowid IN ( ' || g_nl ||
131 ' SELECT ghr3.rowid' || g_nl ||
132 ' FROM GCS_HISTORICAL_RATES ghr2, ' || g_nl ||
133 ' GCS_HISTORICAL_RATES ghr3' || g_nl ||
134 ' WHERE ghr2.entity_id = ' || p_entity_id || g_nl ||
135 ' AND ghr2.hierarchy_id = ' || p_hierarchy_id || g_nl ||
136 ' AND ghr2.from_currency = ''' || p_from_ccy || '''' || g_nl ||
137 ' AND ghr2.to_currency = ''' || p_to_ccy || '''' || g_nl ||
138 ' AND ghr2.rate_type_code in (''H'',''P'',''C'')' || g_nl ||
139 ' AND ghr2.account_type_code IN (''ASSET'',''LIABILITY'',decode(''' || p_eq_xlate_mode || ''', ''YTD'', ''EQUITY'', NULL))' || g_nl ||
140 ' AND ghr2.stop_rollforward_flag = ''N''' || g_nl ||
141 ' AND ghr3.entity_id = ghr2.entity_id' || g_nl ||
142 ' AND ghr3.hierarchy_id = ghr2.hierarchy_id' || g_nl ||
143 ' AND ghr2.cal_period_id = ' || p_prev_period_id || g_nl ||
144 ' AND ghr3.cal_period_id = ' || p_cal_period_id || g_nl ||
145 ' AND ghr3.line_item_id = ghr2.line_item_id' || g_nl ||
146 ' AND ghr3.standard_re_rate_flag = ''Y'' ' || g_nl ||
147 ' AND ghr3.line_item_id = p_hier_li_id' || g_nl ||' AND ghr3.from_currency = ghr2.from_currency' || g_nl ||
148 ' AND ghr3.to_currency = ghr2.to_currency ' || g_nl ||
149 ' AND ghr3.rate_type_code IN (''P'', ''E'')' || g_nl ||
150 ' AND (nvl(to_char(ghr2.translated_rate), ''X'') <>' || g_nl ||
151 ' nvl(to_char(ghr3.translated_rate), ''X'')' || g_nl ||
152 ' OR' || g_nl ||
153 ' nvl(to_char(ghr2.translated_amount), ''X'') <>' || g_nl ||
154 ' nvl(to_char(ghr3.translated_amount), ''X'')))');
155
156 -- First, update historical rates for balance sheet accounts if:
157 -- 1. A historical rate exists in the current period and the rate type
158 -- is not historical.
159 -- 2. A historical rate exists in the previous period and the rate type
160 -- is Prior or Historical.
161 -- 3. The historical rates of current and previous periods are different.
162 -- 4. The historical rate is not marked with stop rolling forward.
163 --Bugfix 6111815: Only Roll Forward Rates Where Standard RE Rate Flag is Y
164 UPDATE gcs_historical_rates ghr
165 SET (translated_rate, translated_amount, rate_type_code,
166 last_update_date, last_updated_by, last_update_login) =
167 (SELECT ghr1.translated_rate, ghr1.translated_amount, 'P', sysdate,
168 gcs_translation_pkg.g_fnd_user_id, gcs_translation_pkg.g_fnd_login_id
169 FROM gcs_historical_rates ghr1
170 WHERE ghr1.entity_id = ghr.entity_id
171 AND ghr1.hierarchy_id = ghr.hierarchy_id
172 AND ghr1.from_currency = ghr.from_currency
173 AND ghr1.to_currency = ghr.to_currency
174 AND ghr1.line_item_id = ghr.line_item_id
175 AND ghr1.standard_re_rate_flag = 'Y'
176 AND ghr1.line_item_id = p_hier_li_id AND ghr1.cal_period_id = p_prev_period_id)
177 WHERE ghr.rowid IN (
178 SELECT ghr3.rowid
179 FROM GCS_HISTORICAL_RATES ghr2,
180 GCS_HISTORICAL_RATES ghr3
181 WHERE ghr2.entity_id = p_entity_id
182 AND ghr2.hierarchy_id = p_hierarchy_id
183 AND ghr2.from_currency = p_from_ccy
184 AND ghr2.to_currency = p_to_ccy
185 AND ghr2.rate_type_code in ('H','P','C')
186 AND ghr2.account_type_code IN ('ASSET','LIABILITY',decode(p_eq_xlate_mode, 'YTD', 'EQUITY', NULL))
187 AND ghr2.stop_rollforward_flag = 'N'
188 AND ghr3.entity_id = ghr2.entity_id
189 AND ghr3.hierarchy_id = ghr2.hierarchy_id
190 AND ghr2.cal_period_id = p_prev_period_id
191 AND ghr3.cal_period_id = p_cal_period_id
192 AND ghr3.line_item_id = ghr2.line_item_id
193 ANd ghr3.standard_re_rate_flag = 'Y'
194 AND ghr3.line_item_id = p_hier_li_id AND ghr3.from_currency = ghr2.from_currency
195 AND ghr3.to_currency = ghr2.to_currency
196 AND ghr3.rate_type_code IN ('P', 'E')
197 AND (nvl(to_char(ghr2.translated_rate), 'X') <>
198 nvl(to_char(ghr3.translated_rate), 'X')
199 OR
200 nvl(to_char(ghr2.translated_amount), 'X') <>
201 nvl(to_char(ghr3.translated_amount), 'X')));
202
203 --Bugfix 6111815: Only Roll Forward Rates Where Standard RE Rate Flag is Y
204 write_to_log(module, FND_LOG.LEVEL_STATEMENT,
205 'DELETE FROM gcs_historical_rates ghr' || g_nl ||
206 'WHERE (rowid, ''E'') IN (' || g_nl ||
207 ' SELECT ghr3.rowid, nvl(ghr2.rate_type_code, ''E'')' || g_nl ||
208 ' FROM GCS_HISTORICAL_RATES ghr3, ' || g_nl ||
209 ' GCS_HISTORICAL_RATES ghr2' || g_nl ||
210 ' WHERE ghr3.entity_id = ' || p_entity_id || g_nl ||
211 ' AND ghr3.hierarchy_id = ' || p_hierarchy_id || g_nl ||
212 ' AND ghr3.rate_type_code = ''P''' || g_nl ||
213 ' AND ghr3.account_type_code IN (''ASSET'',''LIABILITY'',decode(''' || p_eq_xlate_mode || ''', ''YTD'', ''EQUITY'', NULL))' || g_nl ||
214 ' AND ghr3.cal_period_id = ' || p_cal_period_id || g_nl ||
215 ' AND ghr3.from_currency = ''' || p_from_ccy || '''' || g_nl ||
216 ' AND ghr3.to_currency = ''' || p_to_ccy || '''' || g_nl ||
217 ' AND ghr2.cal_period_id (+)= ' || p_prev_period_id || g_nl ||
218 ' AND ghr2.entity_id (+)= ' || p_entity_id || g_nl ||
219 ' AND ghr2.hierarchy_id (+)= ' || p_hierarchy_id || g_nl ||
220 ' AND ghr2.from_currency (+)= ''' || p_from_ccy || '''' || g_nl ||
221 ' AND ghr2.to_currency (+)= ''' || p_to_ccy || '''' || g_nl ||
222 ' AND ghr2.stop_rollforward_flag (+)= ''N''' || g_nl ||
223 ' AND ghr2.line_item_id (+)= ghr3.line_item_id' || g_nl ||
224 ' AND ghr3.standard_re_rate_flag = ''Y'' ' || g_nl ||
225 ' AND ghr3.line_item_id = p_hier_li_id ' || g_nl ||' )');
226
227 -- Next, delete historical rates for balance sheet accounts if:
228 -- 1. A historical rate exists in the current period and the rate
229 -- type is Prior.
230 -- 2. There is no historical rate in the previous period or a historical
231 -- rate exists in the previous period with the rate type Period.
232 --Bugfix 6111815: Only Roll Forward Rates Where Standard RE Rate Flag is Y
233 DELETE FROM gcs_historical_rates ghr
234 WHERE (rowid, 'E') IN (
235 SELECT ghr3.rowid, nvl(ghr2.rate_type_code, 'E')
236 FROM GCS_HISTORICAL_RATES ghr3,
237 GCS_HISTORICAL_RATES ghr2
238 WHERE ghr3.entity_id = p_entity_id
239 AND ghr3.hierarchy_id = p_hierarchy_id
240 AND ghr3.rate_type_code = 'P'
241 AND ghr3.account_type_code IN ('ASSET','LIABILITY',decode(p_eq_xlate_mode, 'YTD', 'EQUITY', NULL))
242 AND ghr3.cal_period_id = p_cal_period_id
243 AND ghr3.from_currency = p_from_ccy
244 AND ghr3.to_currency = p_to_ccy
245 AND ghr2.cal_period_id (+)= p_prev_period_id
246 AND ghr2.entity_id (+)= p_entity_id
247 AND ghr2.hierarchy_id (+)= p_hierarchy_id
248 AND ghr2.from_currency (+)= p_from_ccy
249 AND ghr2.to_currency (+)= p_to_ccy
250 AND ghr2.stop_rollforward_flag (+)= 'N'
251 AND ghr2.line_item_id (+)= ghr3.line_item_id
252 AND ghr3.standard_re_rate_flag = 'Y'
253 AND ghr3.line_item_id = p_hier_li_id );
254
255 write_to_log(module, FND_LOG.LEVEL_STATEMENT,
256 'INSERT /*+ parallel (gcs_historical_rates) */ INTO gcs_historical_rates(standard_re_rate_flag, entity_id, hierarchy_id, ' ||
257 'cal_period_id, from_currency, to_currency, line_item_id, ' ||
258 'company_cost_center_org_id, intercompany_id, financial_elem_id, ' ||
259 'product_id, natural_account_id, channel_id, project_id, customer_id, task_id, ' ||
260 'user_dim1_id, user_dim2_id, user_dim3_id, user_dim4_id, user_dim5_id, ' ||
261 'user_dim6_id, user_dim7_id, user_dim8_id, user_dim9_id, user_dim10_id, ' ||
262 'translated_rate, translated_amount, rate_type_code, update_flag, ' ||
263 'account_type_code, stop_rollforward_flag, last_update_date, last_updated_by, ' ||
264 'last_update_login, creation_date, created_by)' || g_nl ||
265 'SELECT ' || g_nl ||
266 ' ''Y'', ghr.entity_id, ghr.hierarchy_id, ' || p_cal_period_id || ', '||
267 'ghr.from_currency, ghr.to_currency, ghr.line_item_id, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'ghr.translated_rate, ghr.translated_amount, ''P'', ''N'', ' ||
268 'ghr.account_type_code, ''N'', sysdate, ' ||
269 gcs_translation_pkg.g_fnd_user_id || ', ' || gcs_translation_pkg.g_fnd_login_id ||
270 ', sysdate, ' || gcs_translation_pkg.g_fnd_user_id || g_nl ||
271 'FROM gcs_historical_rates ghr' || g_nl ||
272 'WHERE ghr.entity_id = ' || p_entity_id || g_nl ||
273 'AND ghr.hierarchy_id = ' || p_hierarchy_id || g_nl ||
274 'AND ghr.to_currency = ''' || p_to_ccy || '''' || g_nl ||
275 'AND ghr.from_currency = ''' || p_from_ccy || '''' || g_nl ||
276 'AND ghr.rate_type_code in (''H'', ''P'', ''C'')' || g_nl ||
277 'AND ghr.cal_period_id = ' || p_prev_period_id || g_nl ||
278 'AND ghr.account_type_code IN (''ASSET'',''LIABILITY'',decode(''' || p_eq_xlate_mode || ''', ''YTD'', ''EQUITY'', NULL))' || g_nl ||
279 'AND ghr.stop_rollforward_flag = ''N''' || g_nl ||
280 'AND ghr.line_item_id = ' || p_hier_li_id || g_nl ||
281 'AND ghr.standard_re_rate_flag = ''Y'' ' || g_nl ||
282 ' AND NOT EXISTS (' || g_nl ||
283 ' SELECT 1 FROM gcs_historical_rates ghr1' || g_nl ||
284 ' WHERE ghr1.entity_id = p_entity_id' || g_nl ||
285 ' AND ghr1.hierarchy_id = p_hierarchy_id' || g_nl ||
286 ' AND ghr1.cal_period_id = p_cal_period_id' || g_nl ||
287 ' AND ghr1.line_item_id = ghr.line_item_id' || g_nl ||' AND ghr1.update_flag = ''N''' || g_nl ||
288 ' AND ghr1.from_currency = ghr.from_currency' || g_nl ||
289 ' AND ghr1.to_currency = ghr.to_currency);' || g_nl );
290
291 -- Next, insert historical rates for balance sheet accounts if:
292 -- 1. No historical rate exists for the current period.
293 -- 2. A historical rate is defined for the previous period with Prior or
294 -- Historical rate type and the stop roll forward flag is not checked.
295 -- Bugfix 6111815: Adeded Standard RE Rate Flag as part of insert
296 INSERT /*+ parallel (gcs_historical_rates) */ INTO gcs_historical_rates(
297 standard_re_rate_flag, entity_id, hierarchy_id, cal_period_id, from_currency,
298 to_currency, line_item_id, company_cost_center_org_id, intercompany_id,
299 financial_elem_id, product_id, natural_account_id,
300 channel_id, project_id, customer_id, task_id, user_dim1_id, user_dim2_id,
301 user_dim3_id, user_dim4_id, user_dim5_id, user_dim6_id, user_dim7_id,
302 user_dim8_id, user_dim9_id, user_dim10_id, translated_rate,
303 translated_amount, rate_type_code, update_flag, account_type_code,
304 stop_rollforward_flag, last_update_date, last_updated_by,
308 p_cal_period_id, ghr.from_currency, ghr.to_currency,
305 last_update_login, creation_date, created_by)
306 SELECT
307 'Y', ghr.entity_id, ghr.hierarchy_id,
309 ghr.line_item_id, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ghr.translated_rate, ghr.translated_amount, 'P', 'N',
310 ghr.account_type_code, 'N', sysdate, gcs_translation_pkg.g_fnd_user_id,
311 gcs_translation_pkg.g_fnd_login_id, sysdate,
312 gcs_translation_pkg.g_fnd_user_id
313 FROM gcs_historical_rates ghr
314 WHERE ghr.entity_id = p_entity_id
315 AND ghr.hierarchy_id = p_hierarchy_id
316 AND ghr.to_currency = p_to_ccy
317 AND ghr.from_currency = p_from_ccy
318 AND ghr.rate_type_code in ('H', 'P', 'C')
319 AND ghr.cal_period_id = p_prev_period_id
320 AND ghr.account_type_code IN ('ASSET','LIABILITY',decode(p_eq_xlate_mode, 'YTD', 'EQUITY', NULL))
321 AND ghr.stop_rollforward_flag = 'N'
322 AND ghr.line_item_id = p_hier_li_id
323 AND ghr.standard_re_rate_flag = 'Y'
324 AND NOT EXISTS (
325 SELECT 1 FROM gcs_historical_rates ghr1
326 WHERE ghr1.entity_id = p_entity_id
327 AND ghr1.hierarchy_id = p_hierarchy_id
328 AND ghr1.cal_period_id = p_cal_period_id
329 AND ghr1.line_item_id = ghr.line_item_id AND ghr1.update_flag = 'N'
330 AND ghr1.from_currency = ghr.from_currency
331 AND ghr1.to_currency = ghr.to_currency);
332
333 write_to_log(module, FND_LOG.LEVEL_PROCEDURE,g_module_success);
334 EXCEPTION
335 WHEN OTHERS THEN
336 FND_MESSAGE.set_name('GCS', 'GCS_CCY_RF_UNEXPECTED_ERR');
337 GCS_TRANSLATION_PKG.g_error_text := FND_MESSAGE.get;
338 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, GCS_TRANSLATION_PKG.g_error_text);
339 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, SQLERRM);
340 module_log_write(module, g_module_failure);
341 raise GCS_TRANSLATION_PKG.GCS_CCY_SUBPROGRAM_RAISED;
342 END Roll_Forward_Retained_Earnings;
343
344
345 --
346 PROCEDURE Trans_RE_First_Per
347 (p_hier_dataset_code NUMBER,
348 p_source_system_code NUMBER,
349 p_ledger_id NUMBER,
350 p_cal_period_id NUMBER,
351 p_entity_id NUMBER,
352 p_hierarchy_id NUMBER,
353 p_from_ccy VARCHAR2,
354 p_to_ccy VARCHAR2,
355 p_eq_xlate_mode VARCHAR2,
356 p_is_xlate_mode VARCHAR2,
357 p_avg_rate NUMBER,
358 p_end_rate NUMBER,
359 p_group_by_flag VARCHAR2,
360 p_round_factor NUMBER,
361 p_hier_li_id NUMBER) IS
362
363 -- Rate to use for equity accounts, income statement accounts if there
364 -- are no historical rates defined.
365 eq_rate NUMBER;
366 is_rate NUMBER;
367
368 module VARCHAR2(50) := 'TRANS_RE_FIRST_PER';
369 BEGIN
370 write_to_log(module, FND_LOG.LEVEL_PROCEDURE,g_module_enter);
371
372 IF p_eq_xlate_mode = 'YTD' THEN
373 eq_rate := p_end_rate;
374 ELSE
375 eq_rate := p_avg_rate;
376 END IF;
377
378 IF p_is_xlate_mode = 'YTD' THEN
379 is_rate := p_end_rate;
380 ELSE
381 is_rate := p_avg_rate;
382 END IF;
383
384 IF p_group_by_flag = 'Y' THEN
385 write_to_log(module, FND_LOG.LEVEL_STATEMENT,
386 'INSERT /*+ parallel (GCS_TRANSLATION_GT) */ INTO GCS_TRANSLATION_GT(translate_rule_code, account_type_code, ' ||
387 'line_item_id, company_cost_center_org_id, intercompany_id, financial_elem_id, ' ||
388 'product_id, natural_account_id, channel_id, ' ||
389 'project_id, customer_id, task_id, user_dim1_id, user_dim2_id, user_dim3_id, ' ||
390 'user_dim4_id, user_dim5_id, user_dim6_id, user_dim7_id, user_dim8_id, ' ||
391 'user_dim9_id, user_dim10_id, t_amount_dr, t_amount_cr, begin_ytd_dr, ' ||
392 'begin_ytd_cr, xlate_ptd_dr, xlate_ptd_cr, xlate_ytd_dr, xlate_ytd_cr)' || g_nl ||
393 'SELECT' || g_nl ||
394 'decode(fxata.dim_attribute_varchar_member,' || g_nl ||
395 ' ''REVENUE'', ''' || p_is_xlate_mode || ''',' || g_nl ||
396 ' ''EXPENSE'', ''' || p_is_xlate_mode || ''',' || g_nl ||
397 ' ''EQUITY'', ''' || p_eq_xlate_mode || ''',' || g_nl ||
398 ' ''YTD''),' || g_nl ||
399 'fxata.dim_attribute_varchar_member, fb.line_item_id, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'0, 0, 0, 0,' || g_nl ||
400 'round(nvl(ghr.translated_amount * 0,' || g_nl ||
401 ' nvl(fb.sum_ytd_debit_balance_e, 0) *' || g_nl ||
402 ' nvl(ghr.translated_rate,' || g_nl ||
403 ' decode(fxata.dim_attribute_varchar_member,' || g_nl ||
404 ' ''REVENUE'', ' || is_rate || ',' || g_nl ||
405 ' ''EXPENSE'', ' || is_rate || ',' || g_nl ||
406 ' ''EQUITY'', ' || eq_rate || ',' || g_nl ||
407 ' ' || p_end_rate || '))) /' || g_nl ||
408 ' ' || p_round_factor || ') *' || g_nl ||
409 p_round_factor || ',' || g_nl ||
410 'round(nvl(ghr.translated_amount,' || g_nl ||
411 ' nvl(fb.sum_ytd_credit_balance_e, 0) *' || g_nl ||
412 ' nvl(ghr.translated_rate,' || g_nl ||
413 ' decode(fxata.dim_attribute_varchar_member,' || g_nl ||
414 ' ''REVENUE'', ' || is_rate || ',' || g_nl ||
418 ' ' || p_round_factor || ') *' || g_nl ||
415 ' ''EXPENSE'', ' || is_rate || ',' || g_nl ||
416 ' ''EQUITY'', ' || eq_rate || ',' || g_nl ||
417 ' ' || p_end_rate || '))) /' || g_nl ||
419 p_round_factor || ',' || g_nl ||
420 'round(nvl(ghr.translated_amount * 0,' || g_nl ||
421 ' nvl(fb.sum_ytd_debit_balance_e, 0) *' || g_nl ||
422 ' nvl(ghr.translated_rate,' || g_nl ||
423 ' decode(fxata.dim_attribute_varchar_member,' || g_nl ||
424 ' ''REVENUE'', ' || is_rate || ',' || g_nl ||
425 ' ''EXPENSE'', ' || is_rate || ',' || g_nl ||
426 ' ''EQUITY'', ' || eq_rate || ',' || g_nl ||
427 ' ' || p_end_rate || '))) /' || g_nl ||
428 ' ' || p_round_factor || ') *' || g_nl ||
429 p_round_factor || ',' || g_nl ||
430 'round(nvl(ghr.translated_amount,' || g_nl ||
431 ' nvl(fb.sum_ytd_credit_balance_e, 0) *' || g_nl ||
432 ' nvl(ghr.translated_rate,' || g_nl ||
433 ' decode(fxata.dim_attribute_varchar_member,' || g_nl ||
434 ' ''REVENUE'', ' || is_rate || ',' || g_nl ||
435 ' ''EXPENSE'', ' || is_rate || ',' || g_nl ||
436 ' ''EQUITY'', ' || eq_rate || ',' || g_nl ||
437 ' ' || p_end_rate || '))) /' || g_nl ||
438 ' ' || p_round_factor || ') *' || g_nl ||
439 p_round_factor || g_nl ||
440 'FROM (SELECT' || g_nl ||
441 ' fb_in.line_item_id,' || g_nl ||' SUM(ytd_debit_balance_e) sum_ytd_debit_balance_e,' || g_nl ||
442 ' SUM(ytd_credit_balance_e) sum_ytd_credit_balance_e' || g_nl ||
443 ' FROM FEM_BALANCES fb_in' || g_nl ||
444 ' WHERE fb_in.dataset_code = ' || p_hier_dataset_code || g_nl ||
445 ' AND fb_in.cal_period_id = ' || p_cal_period_id || g_nl ||
446 ' AND fb_in.source_system_code = ' || p_source_system_code || g_nl ||
447 ' AND fb_in.currency_code = ''' || p_from_ccy || '''' || g_nl ||
448 ' AND fb_in.ledger_id = ' || p_ledger_id || g_nl ||
449 ' AND fb_in.entity_id = ' || p_entity_id || g_nl ||
450 ' GROUP BY ' || g_nl ||' fb_in.line_item_id) fb,' || g_nl ||
451 ' FEM_LN_ITEMS_ATTR li,' || g_nl ||
452 ' FEM_EXT_ACCT_TYPES_ATTR fxata,' || g_nl ||
453 ' GCS_HISTORICAL_RATES ghr' || g_nl ||
454 'WHERE li.line_item_id = fb.line_item_id' || g_nl ||
455 'AND li.attribute_id = ' || gcs_translation_pkg.g_li_acct_type_attr_id || g_nl ||
456 'AND li.version_id = ' || gcs_translation_pkg.g_li_acct_type_v_id || g_nl ||
457 'AND fxata.ext_account_type_code = li.dim_attribute_varchar_member' || g_nl ||
458 'AND fxata.attribute_id = ' || gcs_translation_pkg.g_xat_basic_acct_type_attr_id || g_nl ||
459 'AND fxata.version_id = ' || gcs_translation_pkg.g_xat_basic_acct_type_v_id || g_nl ||
460 'AND ghr.entity_id = ' || p_entity_id || g_nl ||
461 'AND ghr.hierarchy_id = ' || p_hierarchy_id || g_nl ||
462 'AND ghr.from_currency = ''' || p_from_ccy || '''' || g_nl ||
463 'AND ghr.to_currency = ''' || p_to_ccy || '''' || g_nl ||
464 'AND ghr.cal_period_id = ' || p_cal_period_id || g_nl ||
465 'AND ghr.line_item_id = fb.line_item_id' || g_nl ||
466 'AND ghr.update_flag = ''N'' ' || g_nl ||
467 'AND ghr.standard_re_rate_flag = ''Y'' ' || g_nl ||
468 'AND fb.line_item_id = p_hier_li_id ' || g_nl ||'');
469
470 INSERT /*+ parallel (GCS_TRANSLATION_GT) */ INTO GCS_TRANSLATION_GT(
471 translate_rule_code, account_type_code, line_item_id,
472 company_cost_center_org_id, intercompany_id, financial_elem_id,
473 product_id, natural_account_id, channel_id, project_id,
474 customer_id, task_id, user_dim1_id, user_dim2_id, user_dim3_id,
475 user_dim4_id, user_dim5_id, user_dim6_id, user_dim7_id, user_dim8_id,
476 user_dim9_id, user_dim10_id, t_amount_dr, t_amount_cr, begin_ytd_dr,
477 begin_ytd_cr, xlate_ptd_dr, xlate_ptd_cr, xlate_ytd_dr, xlate_ytd_cr)
478 SELECT /*+ ordered */
479 decode(fxata.dim_attribute_varchar_member,
480 'REVENUE', p_is_xlate_mode,
481 'EXPENSE', p_is_xlate_mode,
482 'EQUITY', p_eq_xlate_mode,
483 'YTD'),
484 fxata.dim_attribute_varchar_member, fb.line_item_id, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 0, 0, 0,
485 round(nvl(ghr.translated_amount * 0,
486 nvl(fb.sum_ytd_debit_balance_e, 0) *
487 nvl(ghr.translated_rate,
488 decode(fxata.dim_attribute_varchar_member,
489 'REVENUE', is_rate,
490 'EXPENSE', is_rate,
491 'EQUITY', eq_rate,
492 p_end_rate))) /
493 p_round_factor) *
494 p_round_factor,
495 round(nvl(ghr.translated_amount,
496 nvl(fb.sum_ytd_credit_balance_e, 0) *
497 nvl(ghr.translated_rate,
498 decode(fxata.dim_attribute_varchar_member,
499 'REVENUE', is_rate,
500 'EXPENSE', is_rate,
501 'EQUITY', eq_rate,
502 p_end_rate))) /
503 p_round_factor) *
504 p_round_factor,
505 round(nvl(ghr.translated_amount * 0,
506 nvl(fb.sum_ytd_debit_balance_e, 0) *
507 nvl(ghr.translated_rate,
511 'EQUITY', eq_rate,
508 decode(fxata.dim_attribute_varchar_member,
509 'REVENUE', is_rate,
510 'EXPENSE', is_rate,
512 p_end_rate))) /
513 p_round_factor) *
514 p_round_factor,
515 round(nvl(ghr.translated_amount,
516 nvl(fb.sum_ytd_credit_balance_e, 0) *
517 nvl(ghr.translated_rate,
518 decode(fxata.dim_attribute_varchar_member,
519 'REVENUE', is_rate,
520 'EXPENSE', is_rate,
521 'EQUITY', eq_rate,
522 p_end_rate))) /
523 p_round_factor) *
524 p_round_factor
525 FROM (SELECT
526 fb_in.line_item_id, SUM(ytd_debit_balance_e) sum_ytd_debit_balance_e,
527 SUM(ytd_credit_balance_e) sum_ytd_credit_balance_e
528 FROM FEM_BALANCES fb_in
529 WHERE fb_in.dataset_code = p_hier_dataset_code
530 AND fb_in.cal_period_id = p_cal_period_id
531 AND fb_in.source_system_code = p_source_system_code
532 AND fb_in.currency_code = p_from_ccy
533 AND fb_in.ledger_id = p_ledger_id
534 AND fb_in.entity_id = p_entity_id
535 GROUP BY fb_in.line_item_id) fb,
536 FEM_LN_ITEMS_ATTR li,
537 FEM_EXT_ACCT_TYPES_ATTR fxata,
538 GCS_HISTORICAL_RATES ghr
539 WHERE li.line_item_id = fb.line_item_id
540 AND li.attribute_id = gcs_translation_pkg.g_li_acct_type_attr_id
541 AND li.version_id = gcs_translation_pkg.g_li_acct_type_v_id
542 AND fxata.ext_account_type_code = li.dim_attribute_varchar_member
543 AND fxata.attribute_id = gcs_translation_pkg.g_xat_basic_acct_type_attr_id
544 AND fxata.version_id = gcs_translation_pkg.g_xat_basic_acct_type_v_id
545 AND ghr.entity_id = p_entity_id
546 AND ghr.hierarchy_id = p_hierarchy_id
547 AND ghr.from_currency = p_from_ccy
548 AND ghr.to_currency = p_to_ccy
549 AND ghr.cal_period_id = p_cal_period_id
550 AND ghr.line_item_id = fb.line_item_id
551 AND ghr.update_flag = 'N'
552 AND fb.line_item_id = p_hier_li_id
553 AND ghr.standard_re_rate_flag = 'Y' ;
554
555 ELSE
556 write_to_log(module, FND_LOG.LEVEL_STATEMENT,
557 'INSERT /*+ parallel (GCS_TRANSLATION_GT) */ INTO GCS_TRANSLATION_GT(translate_rule_code, account_type_code, ' ||
558 'line_item_id, company_cost_center_org_id, intercompany_id, financial_elem_id, ' ||
559 'product_id, natural_account_id, channel_id, ' ||
560 'project_id, customer_id, task_id, user_dim1_id, user_dim2_id, user_dim3_id, ' ||
561 'user_dim4_id, user_dim5_id, user_dim6_id, user_dim7_id, user_dim8_id, ' ||
562 'user_dim9_id, user_dim10_id, t_amount_dr, t_amount_cr, begin_ytd_dr, ' ||
563 'begin_ytd_cr, xlate_ptd_dr, xlate_ptd_cr, xlate_ytd_dr, xlate_ytd_cr)' || g_nl ||
564 'SELECT' || g_nl ||
565 'decode(fxata.dim_attribute_varchar_member,' || g_nl ||
566 ' ''REVENUE'', ''' || p_is_xlate_mode || ''',' || g_nl ||
567 ' ''EXPENSE'', ''' || p_is_xlate_mode || ''',' || g_nl ||
568 ' ''EQUITY'', ''' || p_eq_xlate_mode || ''',' || g_nl ||
569 ' ''YTD''),' || g_nl ||
570 'fxata.dim_attribute_varchar_member, fb.line_item_id, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'0, 0, 0, 0,' || g_nl ||
571 'round(nvl(ghr.translated_amount * 0,' || g_nl ||
572 ' nvl(fb.sum_ytd_debit_balance_e, 0) *' || g_nl ||
573 ' nvl(ghr.translated_rate,' || g_nl ||
574 ' decode(fxata.dim_attribute_varchar_member,' || g_nl ||
575 ' ''REVENUE'', ' || is_rate || ',' || g_nl ||
576 ' ''EXPENSE'', ' || is_rate || ',' || g_nl ||
577 ' ''EQUITY'', ' || eq_rate || ',' || g_nl ||
578 ' ' || p_end_rate || '))) /' || g_nl ||
579 ' ' || p_round_factor || ') *' || g_nl ||
580 p_round_factor || ',' || g_nl ||
581 'round(nvl(ghr.translated_amount,' || g_nl ||
582 ' nvl(fb.sum_ytd_credit_balance_e, 0) *' || g_nl ||
583 ' nvl(ghr.translated_rate,' || g_nl ||
584 ' decode(fxata.dim_attribute_varchar_member,' || g_nl ||
585 ' ''REVENUE'', ' || is_rate || ',' || g_nl ||
586 ' ''EXPENSE'', ' || is_rate || ',' || g_nl ||
587 ' ''EQUITY'', ' || eq_rate || ',' || g_nl ||
588 ' ' || p_end_rate || '))) /' || g_nl ||
589 ' ' || p_round_factor || ') *' || g_nl ||
590 p_round_factor || ',' || g_nl ||
591 'round(nvl(ghr.translated_amount * 0,' || g_nl ||
592 ' nvl(fb.sum_ytd_debit_balance_e, 0) *' || g_nl ||
593 ' nvl(ghr.translated_rate,' || g_nl ||
594 ' decode(fxata.dim_attribute_varchar_member,' || g_nl ||
595 ' ''REVENUE'', ' || is_rate || ',' || g_nl ||
596 ' ''EXPENSE'', ' || is_rate || ',' || g_nl ||
597 ' ''EQUITY'', ' || eq_rate || ',' || g_nl ||
598 ' ' || p_end_rate || '))) /' || g_nl ||
599 ' ' || p_round_factor || ') *' || g_nl ||
600 p_round_factor || ',' || g_nl ||
601 'round(nvl(ghr.translated_amount,' || g_nl ||
602 ' nvl(fb.sum_ytd_credit_balance_e, 0) *' || g_nl ||
603 ' nvl(ghr.translated_rate,' || g_nl ||
607 ' ''EQUITY'', ' || eq_rate || ',' || g_nl ||
604 ' decode(fxata.dim_attribute_varchar_member,' || g_nl ||
605 ' ''REVENUE'', ' || is_rate || ',' || g_nl ||
606 ' ''EXPENSE'', ' || is_rate || ',' || g_nl ||
608 ' ' || p_end_rate || '))) /' || g_nl ||
609 ' ' || p_round_factor || ') *' || g_nl ||
610 p_round_factor || g_nl ||
611 'FROM (SELECT' || g_nl ||
612 ' fb_in.line_item_id,' || g_nl ||' ytd_debit_balance_e sum_ytd_debit_balance_e,' || g_nl ||
613 ' ytd_credit_balance_e sum_ytd_credit_balance_e' || g_nl ||
614 ' FROM FEM_BALANCES fb_in' || g_nl ||
615 ' WHERE fb_in.dataset_code = ' || p_hier_dataset_code || g_nl ||
616 ' AND fb_in.cal_period_id = ' || p_cal_period_id || g_nl ||
617 ' AND fb_in.source_system_code = ' || p_source_system_code || g_nl ||
618 ' AND fb_in.currency_code = ''' || p_from_ccy || '''' || g_nl ||
619 ' AND fb_in.ledger_id = ' || p_ledger_id || g_nl ||
620 ' AND fb_in.entity_id = ' || p_entity_id || ') fb,' || g_nl ||
621 ' FEM_LN_ITEMS_ATTR li,' || g_nl ||
622 ' FEM_EXT_ACCT_TYPES_ATTR fxata,' || g_nl ||
623 ' GCS_HISTORICAL_RATES ghr' || g_nl ||
624 'WHERE li.line_item_id = fb.line_item_id' || g_nl ||
625 'AND li.attribute_id = ' || gcs_translation_pkg.g_li_acct_type_attr_id || g_nl ||
626 'AND li.version_id = ' || gcs_translation_pkg.g_li_acct_type_v_id || g_nl ||
627 'AND fxata.ext_account_type_code = li.dim_attribute_varchar_member' || g_nl ||
628 'AND fxata.attribute_id = ' || gcs_translation_pkg.g_xat_basic_acct_type_attr_id || g_nl ||
629 'AND fxata.version_id = ' || gcs_translation_pkg.g_xat_basic_acct_type_v_id || g_nl ||
630 'AND ghr.entity_id = ' || p_entity_id || g_nl ||
631 'AND ghr.hierarchy_id = ' || p_hierarchy_id || g_nl ||
632 'AND ghr.from_currency = ''' || p_from_ccy || '''' || g_nl ||
633 'AND ghr.to_currency = ''' || p_to_ccy || '''' || g_nl ||
634 'AND ghr.cal_period_id = ' || p_cal_period_id || g_nl ||
635 'AND ghr.line_item_id = fb.line_item_id' || g_nl ||
636 'AND ghr.update_flag = ''N'' ' || g_nl ||
637 'AND ghr.standard_re_rate_flag = ''Y'' ' || g_nl ||
638 'AND fb.line_item_id = p_hier_li_id ' || g_nl ||'');
639
640 INSERT /*+ parallel (GCS_TRANSLATION_GT) */ INTO GCS_TRANSLATION_GT(
641 translate_rule_code, account_type_code, line_item_id,
642 company_cost_center_org_id, intercompany_id, financial_elem_id,
643 product_id, natural_account_id, channel_id, project_id,
644 customer_id, task_id, user_dim1_id, user_dim2_id, user_dim3_id,
645 user_dim4_id, user_dim5_id, user_dim6_id, user_dim7_id, user_dim8_id,
646 user_dim9_id, user_dim10_id, t_amount_dr, t_amount_cr, begin_ytd_dr,
647 begin_ytd_cr, xlate_ptd_dr, xlate_ptd_cr, xlate_ytd_dr, xlate_ytd_cr)
648 SELECT /*+ ordered */
649 decode(fxata.dim_attribute_varchar_member,
650 'REVENUE', p_is_xlate_mode,
651 'EXPENSE', p_is_xlate_mode,
652 'EQUITY', p_eq_xlate_mode,
653 'YTD'),
654 fxata.dim_attribute_varchar_member, fb.line_item_id, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 0, 0, 0,
655 round(nvl(ghr.translated_amount * 0,
656 nvl(fb.sum_ytd_debit_balance_e, 0) *
657 nvl(ghr.translated_rate,
658 decode(fxata.dim_attribute_varchar_member,
659 'REVENUE', is_rate,
660 'EXPENSE', is_rate,
661 'EQUITY', eq_rate,
662 p_end_rate))) /
663 p_round_factor) *
664 p_round_factor,
665 round(nvl(ghr.translated_amount,
666 nvl(fb.sum_ytd_credit_balance_e, 0) *
667 nvl(ghr.translated_rate,
668 decode(fxata.dim_attribute_varchar_member,
669 'REVENUE', is_rate,
670 'EXPENSE', is_rate,
671 'EQUITY', eq_rate,
672 p_end_rate))) /
673 p_round_factor) *
674 p_round_factor,
675 round(nvl(ghr.translated_amount * 0,
676 nvl(fb.sum_ytd_debit_balance_e, 0) *
677 nvl(ghr.translated_rate,
678 decode(fxata.dim_attribute_varchar_member,
679 'REVENUE', is_rate,
680 'EXPENSE', is_rate,
681 'EQUITY', eq_rate,
682 p_end_rate))) /
683 p_round_factor) *
684 p_round_factor,
685 round(nvl(ghr.translated_amount,
686 nvl(fb.sum_ytd_credit_balance_e, 0) *
687 nvl(ghr.translated_rate,
688 decode(fxata.dim_attribute_varchar_member,
689 'REVENUE', is_rate,
690 'EXPENSE', is_rate,
691 'EQUITY', eq_rate,
692 p_end_rate))) /
693 p_round_factor) *
694 p_round_factor
695 FROM (SELECT
696 fb_in.line_item_id, ytd_debit_balance_e sum_ytd_debit_balance_e,
697 ytd_credit_balance_e sum_ytd_credit_balance_e
698 FROM FEM_BALANCES fb_in
699 WHERE fb_in.dataset_code = p_hier_dataset_code
703 AND fb_in.ledger_id = p_ledger_id
700 AND fb_in.cal_period_id = p_cal_period_id
701 AND fb_in.source_system_code = p_source_system_code
702 AND fb_in.currency_code = p_from_ccy
704 AND fb_in.entity_id = p_entity_id) fb,
705 FEM_LN_ITEMS_ATTR li,
706 FEM_EXT_ACCT_TYPES_ATTR fxata,
707 GCS_HISTORICAL_RATES ghr
708 WHERE li.line_item_id = fb.line_item_id
709 AND li.attribute_id = gcs_translation_pkg.g_li_acct_type_attr_id
710 AND li.version_id = gcs_translation_pkg.g_li_acct_type_v_id
711 AND fxata.ext_account_type_code = li.dim_attribute_varchar_member
712 AND fxata.attribute_id = gcs_translation_pkg.g_xat_basic_acct_type_attr_id
713 AND fxata.version_id = gcs_translation_pkg.g_xat_basic_acct_type_v_id
714 AND ghr.entity_id = p_entity_id
715 AND ghr.hierarchy_id = p_hierarchy_id
716 AND ghr.from_currency = p_from_ccy
717 AND ghr.to_currency = p_to_ccy
718 AND ghr.cal_period_id = p_cal_period_id
719 AND ghr.line_item_id = fb.line_item_id
720 AND ghr.update_flag = 'N'
721 AND ghr.standard_re_rate_flag = 'Y'
722 AND fb.line_item_id = p_hier_li_id ;
723 END IF;
724
725 --Bugfix 6111815: Do not check if any data was translated
726 /*
727 -- No data was found to translate.
728 IF SQL%ROWCOUNT = 0 THEN
729 raise GCS_CCY_NO_DATA;
730 END IF;
731 */
732 write_to_log(module, FND_LOG.LEVEL_PROCEDURE,g_module_success);
733 EXCEPTION
734 WHEN GCS_CCY_NO_DATA THEN
735 FND_MESSAGE.set_name('GCS', 'GCS_CCY_NO_TRANSLATE_DATA_ERR');
736 GCS_TRANSLATION_PKG.g_error_text := FND_MESSAGE.get;
737 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, GCS_TRANSLATION_PKG.g_error_text);
738 module_log_write(module, g_module_failure);
739 raise GCS_TRANSLATION_PKG.GCS_CCY_SUBPROGRAM_RAISED;
740 WHEN OTHERS THEN
741 FND_MESSAGE.set_name('GCS', 'GCS_CCY_FIRST_UNEXPECTED_ERR');
742 GCS_TRANSLATION_PKG.g_error_text := FND_MESSAGE.get;
743 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, GCS_TRANSLATION_PKG.g_error_text);
744 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, SQLERRM);
745 module_log_write(module, g_module_failure);
746 raise GCS_TRANSLATION_PKG.GCS_CCY_SUBPROGRAM_RAISED;
747 END Trans_RE_First_Per;
748
749
750 --
751 PROCEDURE Trans_RE_Subseq_Per
752 (p_hier_dataset_code NUMBER,
753 p_cal_period_id NUMBER,
754 p_prev_period_id NUMBER,
755 p_entity_id NUMBER,
756 p_hierarchy_id NUMBER,
757 p_ledger_id NUMBER,
758 p_from_ccy VARCHAR2,
759 p_to_ccy VARCHAR2,
760 p_eq_xlate_mode VARCHAR2,
761 p_is_xlate_mode VARCHAR2,
762 p_avg_rate NUMBER,
763 p_end_rate NUMBER,
764 p_group_by_flag VARCHAR2,
765 p_round_factor NUMBER,
766 p_source_system_code NUMBER,
767 p_hier_li_id NUMBER) IS
768
769 -- Rate to use for equity accounts, income statement accounts if there
770 -- are no historical rates defined.
771 eq_rate NUMBER;
772 is_rate NUMBER;
773
774 fb_object_id NUMBER;
775 CURSOR get_object_id IS
776 SELECT cb.associated_object_id
777 FROM gcs_categories_b cb
778 WHERE cb.category_code = 'TRANSLATION';
779
780 module VARCHAR2(50) := 'TRANS_RE_SUBSEQ_PER';
781 BEGIN
782 module_log_write(module, g_module_enter);
783
784 IF p_eq_xlate_mode = 'YTD' THEN
785 eq_rate := p_end_rate;
786 ELSE
787 eq_rate := p_avg_rate;
788 END IF;
789
790 IF p_is_xlate_mode = 'YTD' THEN
791 is_rate := p_end_rate;
792 ELSE
793 is_rate := p_avg_rate;
794 END IF;
795
796 OPEN get_object_id;
797 FETCH get_object_id INTO fb_object_id;
798 CLOSE get_object_id;
799
800 IF p_group_by_flag = 'Y' THEN
801 write_to_log(module, FND_LOG.LEVEL_STATEMENT,
802 'INSERT /*+ parallel (GCS_TRANSLATION_GT) */ INTO GCS_TRANSLATION_GT(translate_rule_code, account_type_code, ' ||
803 'line_item_id, company_cost_center_org_id, ' ||
804 'intercompany_id, financial_elem_id, product_id, ' ||
805 'natural_account_id, channel_id, project_id, customer_id, task_id, ' ||
806 'user_dim1_id, user_dim2_id, user_dim3_id, user_dim4_id, user_dim5_id, ' ||
807 'user_dim6_id, user_dim7_id, user_dim8_id, user_dim9_id, user_dim10_id, ' ||
808 't_amount_dr, t_amount_cr, begin_ytd_dr, begin_ytd_cr, xlate_ptd_dr, ' ||
809 'xlate_ptd_cr, xlate_ytd_dr,xlate_ytd_cr)' || g_nl ||
810 'SELECT' || g_nl ||
811 'decode(fxata.dim_attribute_varchar_member,' || g_nl ||
812 ' ''REVENUE'', ''' || p_is_xlate_mode || ''',' || g_nl ||
813 ' ''EXPENSE'', ''' || p_is_xlate_mode || ''',' || g_nl ||
814 ' ''EQUITY'', ''' || p_eq_xlate_mode || ''',' || g_nl ||
815 ' ''YTD''),' || g_nl ||
816 'fxata.dim_attribute_varchar_member, fb.line_item_id, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||g_nl ||
817 'round(nvl(ghr.translated_amount * 0,' || g_nl ||
818 ' nvl(decode(fxata.dim_attribute_varchar_member,' || g_nl ||
822 ' ''EXPENSE'', decode(''' || p_is_xlate_mode || ''',' || g_nl ||
819 ' ''REVENUE'', decode(''' || p_is_xlate_mode || ''',' || g_nl ||
820 ' ''YTD'', fb.sum_ytd_debit_balance_e,' || g_nl ||
821 ' fb.sum_ptd_debit_balance_e),' || g_nl ||
823 ' ''YTD'', fb.sum_ytd_debit_balance_e,' || g_nl ||
824 ' fb.sum_ptd_debit_balance_e),' || g_nl ||
825 ' ''EQUITY'', decode(''' || p_eq_xlate_mode || ''',' || g_nl ||
826 ' ''YTD'', fb.sum_ytd_debit_balance_e,' || g_nl ||
827 ' fb.sum_ptd_debit_balance_e),' || g_nl ||
828 ' fb.sum_ytd_debit_balance_e),' || g_nl ||
829 ' 0) *' || g_nl ||
830 ' nvl(ghr.translated_rate,' || g_nl ||
831 ' decode(fxata.dim_attribute_varchar_member,' || g_nl ||
832 ' ''REVENUE'', ' || is_rate || ',' || g_nl ||
833 ' ''EXPENSE'', ' || is_rate || ',' || g_nl ||
834 ' ''EQUITY'', ' || eq_rate || ',' || g_nl ||
835 ' ' || p_end_rate || '))) /' || g_nl ||
836 ' ' || p_round_factor || ') *' || g_nl ||
837 p_round_factor || ',' || g_nl ||
838 'round(nvl(ghr.translated_amount,' || g_nl ||
839 ' nvl(decode(fxata.dim_attribute_varchar_member,' || g_nl ||
840 ' ''REVENUE'', decode(''' || p_is_xlate_mode || ''',' || g_nl ||
841 ' ''YTD'', fb.sum_ytd_credit_balance_e,' || g_nl ||
842 ' fb.sum_ptd_credit_balance_e),' || g_nl ||
843 ' ''EXPENSE'', decode(''' || p_is_xlate_mode || ''',' || g_nl ||
844 ' ''YTD'', fb.sum_ytd_credit_balance_e,' || g_nl ||
845 ' fb.sum_ptd_credit_balance_e),' || g_nl ||
846 ' ''EQUITY'', decode(''' || p_eq_xlate_mode || ''',' || g_nl ||
847 ' ''YTD'', fb.sum_ytd_credit_balance_e,' || g_nl ||
848 ' fb.sum_ptd_credit_balance_e),' || g_nl ||
849 ' fb.sum_ytd_credit_balance_e),' || g_nl ||
850 ' 0) *' || g_nl ||
851 ' nvl(ghr.translated_rate,' || g_nl ||
852 ' decode(fxata.dim_attribute_varchar_member,' || g_nl ||
853 ' ''REVENUE'', ' || is_rate || ',' || g_nl ||
854 ' ''EXPENSE'', ' || is_rate || ',' || g_nl ||
855 ' ''EQUITY'', ' || eq_rate || ',' || g_nl ||
856 ' ' || p_end_rate || '))) /' || g_nl ||
857 ' ' || p_round_factor || ') *' || g_nl ||
858 p_round_factor || ',' || g_nl ||
859 'nvl(fbp.ytd_debit_balance_e,0),' || g_nl ||
860 'nvl(fbp.ytd_credit_balance_e,0), 0, 0, 0, 0' || g_nl ||
861 'FROM (SELECT' || g_nl ||
862 ' fb_in.line_item_id,' || g_nl ||' SUM(ptd_debit_balance_e) sum_ptd_debit_balance_e,' || g_nl ||
863 ' SUM(ptd_credit_balance_e) sum_ptd_credit_balance_e,' || g_nl ||
864 ' SUM(ytd_debit_balance_e) sum_ytd_debit_balance_e,' || g_nl ||
865 ' SUM(ytd_credit_balance_e) sum_ytd_credit_balance_e' || g_nl ||
866 ' FROM FEM_BALANCES fb_in' || g_nl ||
867 ' WHERE fb_in.dataset_code = ' || p_hier_dataset_code || g_nl ||
868 ' AND fb_in.cal_period_id = ' || p_cal_period_id || g_nl ||
869 ' AND fb_in.source_system_code = ' || p_source_system_code || g_nl ||
870 ' AND fb_in.currency_code = ''' || p_from_ccy || '''' || g_nl ||
871 ' AND fb_in.ledger_id = ' || p_ledger_id || g_nl ||
872 ' AND fb_in.entity_id = ' || p_entity_id || g_nl ||
873 ' GROUP BY ' || g_nl ||' fb_in.line_item_id) fb,' || g_nl ||
874 ' FEM_BALANCES fbp,' || g_nl ||
875 ' GCS_HISTORICAL_RATES ghr,' || g_nl ||
876 ' FEM_LN_ITEMS_ATTR li,' || g_nl ||
877 ' FEM_EXT_ACCT_TYPES_ATTR fxata' || g_nl ||
878 'WHERE fbp.created_by_object_id (+)= ' || fb_object_id || g_nl ||
879 'AND li.line_item_id = fb.line_item_id' || g_nl ||
880 'AND li.attribute_id = ' || gcs_translation_pkg.g_li_acct_type_attr_id || g_nl ||
881 'AND li.version_id = ' || gcs_translation_pkg.g_li_acct_type_v_id || g_nl ||
882 'AND fxata.ext_account_type_code = li.dim_attribute_varchar_member' || g_nl ||
883 'AND fxata.attribute_id = ' || gcs_translation_pkg.g_xat_basic_acct_type_attr_id || g_nl ||
884 'AND fxata.version_id = ' || gcs_translation_pkg.g_xat_basic_acct_type_v_id || g_nl ||
885 'AND fbp.dataset_code (+)= ' || p_hier_dataset_code || g_nl ||
886 'AND fbp.cal_period_id (+)= ' || p_prev_period_id || g_nl ||
887 'AND fbp.source_system_code (+)= ' || p_source_system_code || g_nl ||
888 'AND fbp.currency_code (+)= ''' || p_to_ccy || '''' || g_nl ||
889 'AND fbp.ledger_id (+)= ' || p_ledger_id || g_nl ||
890 'AND fbp.entity_id (+)= ' || p_entity_id || g_nl ||
891 'AND fbp.line_item_id (+)= fb.line_item_id' || g_nl ||
892 'AND fb.line_item_id = p_hier_li_id ' || g_nl ||'AND ghr.entity_id = ' || p_entity_id || g_nl ||
893 'AND ghr.hierarchy_id = ' || p_hierarchy_id || g_nl ||
894 'AND ghr.from_currency = ''' || p_from_ccy || '''' || g_nl ||
895 'AND ghr.to_currency = ''' || p_to_ccy || '''' || g_nl ||
896 'AND ghr.cal_period_id = ' || p_cal_period_id || g_nl ||
897 'AND ghr.line_item_id = fb.line_item_id' || g_nl ||
898 'AND ghr.standard_re_rate_flag = ''Y'' ' || g_nl ||
899 'AND ghr.update_flag = ''N''' || g_nl ||'');
900
901 INSERT /*+ parallel (GCS_TRANSLATION_GT) */ INTO GCS_TRANSLATION_GT(
902 translate_rule_code, account_type_code, line_item_id,
903 company_cost_center_org_id, intercompany_id, financial_elem_id,
907 user_dim9_id, user_dim10_id, t_amount_dr, t_amount_cr, begin_ytd_dr,
904 product_id, natural_account_id, channel_id, project_id,
905 customer_id, task_id, user_dim1_id, user_dim2_id, user_dim3_id,
906 user_dim4_id, user_dim5_id, user_dim6_id, user_dim7_id, user_dim8_id,
908 begin_ytd_cr, xlate_ptd_dr, xlate_ptd_cr, xlate_ytd_dr,xlate_ytd_cr)
909 SELECT
910 decode(fxata.dim_attribute_varchar_member,
911 'REVENUE', p_is_xlate_mode,
912 'EXPENSE', p_is_xlate_mode,
913 'EQUITY', p_eq_xlate_mode,
914 'YTD'),
915 fxata.dim_attribute_varchar_member, fb.line_item_id, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, round(nvl(ghr.translated_amount * 0,
916 nvl(decode(fxata.dim_attribute_varchar_member,
917 'REVENUE', decode(p_is_xlate_mode,
918 'YTD', fb.sum_ytd_debit_balance_e,
919 fb.sum_ptd_debit_balance_e),
920 'EXPENSE', decode(p_is_xlate_mode,
921 'YTD', fb.sum_ytd_debit_balance_e,
922 fb.sum_ptd_debit_balance_e),
923 'EQUITY', decode(p_eq_xlate_mode,
924 'YTD', fb.sum_ytd_debit_balance_e,
925 fb.sum_ptd_debit_balance_e),
926 fb.sum_ytd_debit_balance_e),
927 0) *
928 nvl(ghr.translated_rate,
929 decode(fxata.dim_attribute_varchar_member,
930 'REVENUE', is_rate,
931 'EXPENSE', is_rate,
932 'EQUITY', eq_rate,
933 p_end_rate))) /
934 p_round_factor) *
935 p_round_factor,
936 round(nvl(ghr.translated_amount,
937 nvl(decode(fxata.dim_attribute_varchar_member,
938 'REVENUE', decode(p_is_xlate_mode,
939 'YTD', fb.sum_ytd_credit_balance_e,
940 fb.sum_ptd_credit_balance_e),
941 'EXPENSE', decode(p_is_xlate_mode,
942 'YTD', fb.sum_ytd_credit_balance_e,
943 fb.sum_ptd_credit_balance_e),
944 'EQUITY', decode(p_eq_xlate_mode,
945 'YTD', fb.sum_ytd_credit_balance_e,
946 fb.sum_ptd_credit_balance_e),
947 fb.sum_ytd_credit_balance_e),
948 0) *
949 nvl(ghr.translated_rate,
950 decode(fxata.dim_attribute_varchar_member,
951 'REVENUE', is_rate,
952 'EXPENSE', is_rate,
953 'EQUITY', eq_rate,
954 p_end_rate))) /
955 p_round_factor) *
956 p_round_factor,
957 nvl(fbp.ytd_debit_balance_e,0),
958 nvl(fbp.ytd_credit_balance_e,0), 0,0,0,0
959 FROM (SELECT
960 fb_in.line_item_id, SUM(ptd_debit_balance_e) sum_ptd_debit_balance_e,
961 SUM(ptd_credit_balance_e) sum_ptd_credit_balance_e,
962 SUM(ytd_debit_balance_e) sum_ytd_debit_balance_e,
963 SUM(ytd_credit_balance_e) sum_ytd_credit_balance_e
964 FROM FEM_BALANCES fb_in
965 WHERE fb_in.dataset_code = p_hier_dataset_code
966 AND fb_in.cal_period_id = p_cal_period_id
967 AND fb_in.source_system_code = p_source_system_code
968 AND fb_in.currency_code = p_from_ccy
969 AND fb_in.ledger_id = p_ledger_id
970 AND fb_in.entity_id = p_entity_id
971 GROUP BY fb_in.line_item_id) fb,
972 FEM_BALANCES fbp,
973 GCS_HISTORICAL_RATES ghr,
974 FEM_LN_ITEMS_ATTR li,
975 FEM_EXT_ACCT_TYPES_ATTR fxata
976 WHERE fbp.created_by_object_id (+)= fb_object_id
977 AND li.line_item_id = fb.line_item_id
978 AND li.attribute_id = gcs_translation_pkg.g_li_acct_type_attr_id
979 AND li.version_id = gcs_translation_pkg.g_li_acct_type_v_id
980 AND fxata.ext_account_type_code = li.dim_attribute_varchar_member
981 AND fxata.attribute_id = gcs_translation_pkg.g_xat_basic_acct_type_attr_id
982 AND fxata.version_id = gcs_translation_pkg.g_xat_basic_acct_type_v_id
983 AND fbp.dataset_code (+)= p_hier_dataset_code
984 AND fbp.cal_period_id (+)= p_prev_period_id
985 AND fbp.source_system_code (+)= p_source_system_code
986 AND fbp.currency_code (+)= p_to_ccy
987 AND fbp.ledger_id (+)= p_ledger_id
988 AND fbp.entity_id (+)= p_entity_id
989 AND fbp.line_item_id (+)= fb.line_item_id
990 AND fb.line_item_id = p_hier_li_id AND ghr.entity_id = p_entity_id
991 AND ghr.hierarchy_id = p_hierarchy_id
992 AND ghr.from_currency = p_from_ccy
993 AND ghr.to_currency = p_to_ccy
994 AND ghr.cal_period_id = p_cal_period_id
995 AND ghr.line_item_id = fb.line_item_id
996 AND ghr.standard_re_rate_flag = 'Y'
1000 write_to_log(module, FND_LOG.LEVEL_STATEMENT,
997 AND ghr.update_flag = 'N' ;
998
999 ELSE
1001 'INSERT /*+ parallel (GCS_TRANSLATION_GT) */ INTO GCS_TRANSLATION_GT(translate_rule_code, account_type_code, ' ||
1002 'line_item_id, company_cost_center_org_id, ' ||
1003 'intercompany_id, financial_elem_id, product_id, ' ||
1004 'natural_account_id, channel_id, project_id, customer_id, task_id, ' ||
1005 'user_dim1_id, user_dim2_id, user_dim3_id, user_dim4_id, user_dim5_id, ' ||
1006 'user_dim6_id, user_dim7_id, user_dim8_id, user_dim9_id, user_dim10_id, ' ||
1007 't_amount_dr, t_amount_cr, begin_ytd_dr, begin_ytd_cr, xlate_ptd_dr, ' ||
1008 'xlate_ptd_cr, xlate_ytd_dr,xlate_ytd_cr)' || g_nl ||
1009 'SELECT' || g_nl ||
1010 'decode(fxata.dim_attribute_varchar_member,' || g_nl ||
1011 ' ''REVENUE'', ''' || p_is_xlate_mode || ''',' || g_nl ||
1012 ' ''EXPENSE'', ''' || p_is_xlate_mode || ''',' || g_nl ||
1013 ' ''EQUITY'', ''' || p_eq_xlate_mode || ''',' || g_nl ||
1014 ' ''YTD''),' || g_nl ||
1015 'fxata.dim_attribute_varchar_member, fb.line_item_id, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||'NULL, ' ||g_nl ||
1016 'round(nvl(ghr.translated_amount * 0,' || g_nl ||
1017 ' nvl(decode(fxata.dim_attribute_varchar_member,' || g_nl ||
1018 ' ''REVENUE'', decode(''' || p_is_xlate_mode || ''',' || g_nl ||
1019 ' ''YTD'', fb.sum_ytd_debit_balance_e,' || g_nl ||
1020 ' fb.sum_ptd_debit_balance_e),' || g_nl ||
1021 ' ''EXPENSE'', decode(''' || p_is_xlate_mode || ''',' || g_nl ||
1022 ' ''YTD'', fb.sum_ytd_debit_balance_e,' || g_nl ||
1023 ' fb.sum_ptd_debit_balance_e),' || g_nl ||
1024 ' ''EQUITY'', decode(''' || p_eq_xlate_mode || ''',' || g_nl ||
1025 ' ''YTD'', fb.sum_ytd_debit_balance_e,' || g_nl ||
1026 ' fb.sum_ptd_debit_balance_e),' || g_nl ||
1027 ' fb.sum_ytd_debit_balance_e),' || g_nl ||
1028 ' 0) *' || g_nl ||
1029 ' nvl(ghr.translated_rate,' || g_nl ||
1030 ' decode(fxata.dim_attribute_varchar_member,' || g_nl ||
1031 ' ''REVENUE'', ' || is_rate || ',' || g_nl ||
1032 ' ''EXPENSE'', ' || is_rate || ',' || g_nl ||
1033 ' ''EQUITY'', ' || eq_rate || ',' || g_nl ||
1034 ' ' || p_end_rate || '))) /' || g_nl ||
1035 ' ' || p_round_factor || ') *' || g_nl ||
1036 p_round_factor || ',' || g_nl ||
1037 'round(nvl(ghr.translated_amount,' || g_nl ||
1038 ' nvl(decode(fxata.dim_attribute_varchar_member,' || g_nl ||
1039 ' ''REVENUE'', decode(''' || p_is_xlate_mode || ''',' || g_nl ||
1040 ' ''YTD'', fb.sum_ytd_credit_balance_e,' || g_nl ||
1041 ' fb.sum_ptd_credit_balance_e),' || g_nl ||
1042 ' ''EXPENSE'', decode(''' || p_is_xlate_mode || ''',' || g_nl ||
1043 ' ''YTD'', fb.sum_ytd_credit_balance_e,' || g_nl ||
1044 ' fb.sum_ptd_credit_balance_e),' || g_nl ||
1045 ' ''EQUITY'', decode(''' || p_eq_xlate_mode || ''',' || g_nl ||
1046 ' ''YTD'', fb.sum_ytd_credit_balance_e,' || g_nl ||
1047 ' fb.sum_ptd_credit_balance_e),' || g_nl ||
1048 ' fb.sum_ytd_credit_balance_e),' || g_nl ||
1049 ' 0) *' || g_nl ||
1050 ' nvl(ghr.translated_rate,' || g_nl ||
1051 ' decode(fxata.dim_attribute_varchar_member,' || g_nl ||
1052 ' ''REVENUE'', ' || is_rate || ',' || g_nl ||
1053 ' ''EXPENSE'', ' || is_rate || ',' || g_nl ||
1054 ' ''EQUITY'', ' || eq_rate || ',' || g_nl ||
1055 ' ' || p_end_rate || '))) /' || g_nl ||
1056 ' ' || p_round_factor || ') *' || g_nl ||
1057 p_round_factor || ',' || g_nl ||
1058 'nvl(fbp.ytd_debit_balance_e,0),' || g_nl ||
1059 'nvl(fbp.ytd_credit_balance_e,0), 0, 0, 0, 0' || g_nl ||
1060 'FROM (SELECT' || g_nl ||
1061 ' fb_in.line_item_id,' || g_nl ||' ptd_debit_balance_e sum_ptd_debit_balance_e,' || g_nl ||
1062 ' ptd_credit_balance_e sum_ptd_credit_balance_e,' || g_nl ||
1063 ' ytd_debit_balance_e sum_ytd_debit_balance_e,' || g_nl ||
1064 ' ytd_credit_balance_e sum_ytd_credit_balance_e' || g_nl ||
1065 ' FROM FEM_BALANCES fb_in' || g_nl ||
1066 ' WHERE fb_in.dataset_code = ' || p_hier_dataset_code || g_nl ||
1067 ' AND fb_in.cal_period_id = ' || p_cal_period_id || g_nl ||
1068 ' AND fb_in.source_system_code = ' || p_source_system_code || g_nl ||
1069 ' AND fb_in.currency_code = ''' || p_from_ccy || '''' || g_nl ||
1070 ' AND fb_in.ledger_id = ' || p_ledger_id || g_nl ||
1071 ' AND fb_in.entity_id = ' || p_entity_id || ') fb,' || g_nl ||
1072 ' FEM_BALANCES fbp,' || g_nl ||
1073 ' GCS_HISTORICAL_RATES ghr,' || g_nl ||
1074 ' FEM_LN_ITEMS_ATTR li,' || g_nl ||
1075 ' FEM_EXT_ACCT_TYPES_ATTR fxata' || g_nl ||
1076 'WHERE fbp.created_by_object_id (+)= ' || fb_object_id || g_nl ||
1077 'AND li.line_item_id = fb.line_item_id' || g_nl ||
1078 'AND li.attribute_id = ' || gcs_translation_pkg.g_li_acct_type_attr_id || g_nl ||
1079 'AND li.version_id = ' || gcs_translation_pkg.g_li_acct_type_v_id || g_nl ||
1080 'AND fxata.ext_account_type_code = li.dim_attribute_varchar_member' || g_nl ||
1081 'AND fxata.attribute_id = ' || gcs_translation_pkg.g_xat_basic_acct_type_attr_id || g_nl ||
1082 'AND fxata.version_id = ' || gcs_translation_pkg.g_xat_basic_acct_type_v_id || g_nl ||
1083 'AND fbp.dataset_code (+)= ' || p_hier_dataset_code || g_nl ||
1084 'AND fbp.cal_period_id (+)= ' || p_prev_period_id || g_nl ||
1085 'AND fbp.source_system_code (+)= ' || p_source_system_code || g_nl ||
1086 'AND fbp.currency_code (+)= ''' || p_to_ccy || '''' || g_nl ||
1087 'AND fbp.ledger_id (+)= ' || p_ledger_id || g_nl ||
1088 'AND fbp.entity_id (+)= ' || p_entity_id || g_nl ||
1089 'AND fbp.line_item_id (+)= fb.line_item_id' || g_nl ||
1090 'AND fb.line_item_id = p_hier_li_id ' || g_nl ||'AND ghr.entity_id = ' || p_entity_id || g_nl ||
1091 'AND ghr.hierarchy_id = ' || p_hierarchy_id || g_nl ||
1092 'AND ghr.from_currency = ''' || p_from_ccy || '''' || g_nl ||
1093 'AND ghr.to_currency = ''' || p_to_ccy || '''' || g_nl ||
1094 'AND ghr.cal_period_id = ' || p_cal_period_id || g_nl ||
1095 'AND ghr.line_item_id = fb.line_item_id' || g_nl ||
1096 'AND ghr.standard_re_rate_flag = ''Y'' ' || g_nl ||
1097 'AND ghr.update_flag = ''N''' || g_nl ||'');
1098
1099 INSERT /*+ parallel (GCS_TRANSLATION_GT) */ INTO GCS_TRANSLATION_GT(
1100 translate_rule_code, account_type_code, line_item_id,
1101 company_cost_center_org_id, intercompany_id, financial_elem_id,
1102 product_id, natural_account_id, channel_id, project_id,
1103 customer_id, task_id, user_dim1_id, user_dim2_id, user_dim3_id,
1104 user_dim4_id, user_dim5_id, user_dim6_id, user_dim7_id, user_dim8_id,
1105 user_dim9_id, user_dim10_id, t_amount_dr, t_amount_cr, begin_ytd_dr,
1106 begin_ytd_cr, xlate_ptd_dr, xlate_ptd_cr, xlate_ytd_dr,xlate_ytd_cr)
1107 SELECT
1108 decode(fxata.dim_attribute_varchar_member,
1109 'REVENUE', p_is_xlate_mode,
1110 'EXPENSE', p_is_xlate_mode,
1111 'EQUITY', p_eq_xlate_mode,
1112 'YTD'),
1113 fxata.dim_attribute_varchar_member, fb.line_item_id, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, round(nvl(ghr.translated_amount * 0,
1114 nvl(decode(fxata.dim_attribute_varchar_member,
1115 'REVENUE', decode(p_is_xlate_mode,
1116 'YTD', fb.sum_ytd_debit_balance_e,
1117 fb.sum_ptd_debit_balance_e),
1118 'EXPENSE', decode(p_is_xlate_mode,
1119 'YTD', fb.sum_ytd_debit_balance_e,
1120 fb.sum_ptd_debit_balance_e),
1121 'EQUITY', decode(p_eq_xlate_mode,
1122 'YTD', fb.sum_ytd_debit_balance_e,
1123 fb.sum_ptd_debit_balance_e),
1124 fb.sum_ytd_debit_balance_e),
1125 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(decode(fxata.dim_attribute_varchar_member,
1136 'REVENUE', decode(p_is_xlate_mode,
1137 'YTD', fb.sum_ytd_credit_balance_e,
1138 fb.sum_ptd_credit_balance_e),
1139 'EXPENSE', decode(p_is_xlate_mode,
1140 'YTD', fb.sum_ytd_credit_balance_e,
1141 fb.sum_ptd_credit_balance_e),
1142 'EQUITY', decode(p_eq_xlate_mode,
1143 'YTD', fb.sum_ytd_credit_balance_e,
1144 fb.sum_ptd_credit_balance_e),
1145 fb.sum_ytd_credit_balance_e),
1146 0) *
1147 nvl(ghr.translated_rate,
1148 decode(fxata.dim_attribute_varchar_member,
1149 'REVENUE', is_rate,
1150 'EXPENSE', is_rate,
1151 'EQUITY', eq_rate,
1152 p_end_rate))) /
1153 p_round_factor) *
1154 p_round_factor,
1155 nvl(fbp.ytd_debit_balance_e,0),
1156 nvl(fbp.ytd_credit_balance_e,0), 0,0,0,0
1157 FROM (SELECT
1158 fb_in.line_item_id, ptd_debit_balance_e sum_ptd_debit_balance_e,
1159 ptd_credit_balance_e sum_ptd_credit_balance_e,
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
1163 WHERE fb_in.dataset_code = p_hier_dataset_code
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_BALANCES fbp,
1170 GCS_HISTORICAL_RATES ghr,
1171 FEM_LN_ITEMS_ATTR li,
1172 FEM_EXT_ACCT_TYPES_ATTR fxata
1173 WHERE fbp.created_by_object_id (+)= fb_object_id
1174 AND li.line_item_id = fb.line_item_id
1175 AND li.attribute_id = gcs_translation_pkg.g_li_acct_type_attr_id
1176 AND li.version_id = gcs_translation_pkg.g_li_acct_type_v_id
1177 AND fxata.ext_account_type_code = li.dim_attribute_varchar_member
1178 AND fxata.attribute_id = gcs_translation_pkg.g_xat_basic_acct_type_attr_id
1179 AND fxata.version_id = gcs_translation_pkg.g_xat_basic_acct_type_v_id
1180 AND fbp.dataset_code (+)= p_hier_dataset_code
1181 AND fbp.cal_period_id (+)= p_prev_period_id
1182 AND fbp.source_system_code (+)= p_source_system_code
1183 AND fbp.currency_code (+)= p_to_ccy
1184 AND fbp.ledger_id (+)= p_ledger_id
1185 AND fbp.entity_id (+)= p_entity_id
1186 AND fbp.line_item_id (+)= fb.line_item_id
1187 AND fb.line_item_id = p_hier_li_id AND ghr.entity_id = p_entity_id
1188 AND ghr.hierarchy_id = p_hierarchy_id
1189 AND ghr.from_currency = p_from_ccy
1190 AND ghr.to_currency = p_to_ccy
1191 AND ghr.cal_period_id = p_cal_period_id
1192 AND ghr.line_item_id = fb.line_item_id
1193 AND ghr.standard_re_rate_flag = 'Y'
1194 AND ghr.update_flag = 'N' ;
1195
1196 END IF;
1197
1198 --Bugfix 6111815: Do not Raise No Data Found anymore
1199 /*
1200 -- No data was found to translate.
1201 IF SQL%ROWCOUNT = 0 THEN
1202 raise GCS_CCY_NO_DATA;
1203 END IF;
1204 */
1205 write_to_log(module, FND_LOG.LEVEL_PROCEDURE,g_module_success);
1206 EXCEPTION
1207 WHEN GCS_CCY_NO_DATA THEN
1208 FND_MESSAGE.set_name('GCS', 'GCS_CCY_NO_TRANSLATE_DATA_ERR');
1209 GCS_TRANSLATION_PKG.g_error_text := FND_MESSAGE.get;
1210 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, GCS_TRANSLATION_PKG.g_error_text);
1211 module_log_write(module, g_module_failure);
1212 raise GCS_TRANSLATION_PKG.GCS_CCY_SUBPROGRAM_RAISED;
1213 WHEN OTHERS THEN
1214 FND_MESSAGE.set_name('GCS', 'GCS_CCY_SUBSQ_UNEXPECTED_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 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, SQLERRM);
1218 module_log_write(module, g_module_failure);
1219 raise GCS_TRANSLATION_PKG.GCS_CCY_SUBPROGRAM_RAISED;
1220 END Trans_RE_Subseq_Per;
1221
1222 END GCS_TRANS_RE_DYNAMIC_PKG;