[Home] [Help]
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 ||
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 ||
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 ||
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,
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
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 ||
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.
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,
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, ' ||
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, ' ||
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 ||
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
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 );
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 );
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 );
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 );