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