[Home] [Help]
PACKAGE BODY: APPS.GCS_DRILLDOWN_UTIL_PKG
Source
1 PACKAGE BODY GCS_DRILLDOWN_UTIL_PKG AS
2 /* $Header: gcs_drill_utilb.pls 120.3 2007/04/18 01:30:19 mikeward ship $ */
3
4 new_line VARCHAR2(4) := '
5 ';
6 g_api VARCHAR2(80) := 'gcs.plsql.GCS_DRILLDOWN_UTIL_PKG';
7
8
9 --
10 -- Private Global Variables
11 --
12
13 g_entity_ledger_attr_id NUMBER := gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-LEDGER_ID').attribute_id;
14 g_entity_ledger_v_id NUMBER := gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-LEDGER_ID').version_id;
15
16 g_entity_srcsys_attr_id NUMBER := gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-SOURCE_SYSTEM_CODE').attribute_id;
17 g_entity_srcsys_v_id NUMBER := gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-SOURCE_SYSTEM_CODE').version_id;
18
19 g_cp_num_attr_id NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-GL_PERIOD_NUM').attribute_id;
20 g_cp_num_v_id NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-GL_PERIOD_NUM').version_id;
21
22 g_cp_year_attr_id NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-ACCOUNTING_YEAR').attribute_id;
23 g_cp_year_v_id NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-ACCOUNTING_YEAR').version_id;
24
25 g_cp_enddate_attr_id NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE').attribute_id;
26 g_cp_enddate_v_id NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE').version_id;
27
28 --
29 -- Private Procedures
30 --
31
32
33
34 --
35 -- Public Procedures
36 --
37
38 FUNCTION get_currency_code
39 (p_hierarchy_id NUMBER,
40 p_entity_id NUMBER)
41 RETURN VARCHAR2 IS
42 l_ccy_code VARCHAR2(30);
43
44 l_module VARCHAR2(30);
45 BEGIN
46 l_module := 'get_currency_code';
47
48 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
49 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
50 g_api || '.' || l_module || '.begin',
51 '<<Enter>>');
52 END IF;
53
54 SELECT eca.currency_code
55 INTO l_ccy_code
56 FROM gcs_entity_cons_attrs eca
57 WHERE eca.hierarchy_id = p_hierarchy_id
58 AND eca.entity_id = p_entity_id;
59
60 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
61 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
62 g_api || '.' || l_module || '.end',
63 '<<Exit>>');
64 END IF;
65
66 RETURN l_ccy_code;
67
68 EXCEPTION
69 WHEN OTHERS THEN
70 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
71 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
72 g_api || '.' || l_module || '.error',
73 SQLERRM);
74 END IF;
75 RAISE;
76 END get_currency_code;
77
78
79 FUNCTION get_ledger_id
80 (p_entity_id NUMBER)
81 RETURN NUMBER IS
82 l_ledger_id NUMBER;
83
84 l_module VARCHAR2(30);
85 BEGIN
86 l_module := 'get_ledger_id';
87
88 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
89 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
90 g_api || '.' || l_module || '.begin',
91 '<<Enter>>');
92 END IF;
93
94 SELECT fea.dim_attribute_numeric_member
95 INTO l_ledger_id
96 FROM fem_entities_attr fea
97 WHERE fea.entity_id = p_entity_id
98 AND fea.attribute_id = g_entity_ledger_attr_id
99 AND fea.version_id = g_entity_ledger_v_id;
100
101 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
102 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
103 g_api || '.' || l_module || '.end',
104 '<<Exit>>');
105 END IF;
106
107 RETURN l_ledger_id;
108
109 EXCEPTION
110 WHEN OTHERS THEN
111 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
112 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
113 g_api || '.' || l_module || '.error',
114 SQLERRM);
115 END IF;
116 RAISE;
117 END get_ledger_id;
118
119
120 FUNCTION get_ledger_id
121 (p_entity_id NUMBER,
122 p_cal_period_id_str VARCHAR2)
123 RETURN NUMBER IS
124 l_ledger_id NUMBER;
125
126 l_module VARCHAR2(30);
127 BEGIN
128 l_module := 'get_ledger_id_2param';
129
130 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
131 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
132 g_api || '.' || l_module || '.begin',
133 '<<Enter>>');
134 END IF;
135
136 SELECT gea.ledger_id
137 INTO l_ledger_id
138 FROM gcs_entities_attr gea,
139 fem_cal_periods_attr fcpa_end_date
140 WHERE gea.entity_id = p_entity_id
141 AND gea.data_type_code = 'ACTUAL'
142 AND fcpa_end_date.cal_period_id = to_number(p_cal_period_id_str)
143 AND fcpa_end_date.attribute_id = g_cp_enddate_attr_id
144 AND fcpa_end_date.version_id = g_cp_enddate_v_id
145 AND fcpa_end_date.date_assign_value
146 BETWEEN gea.effective_start_date
147 AND nvl(gea.effective_end_date, fcpa_end_date.date_assign_value);
148
149 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
150 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
151 g_api || '.' || l_module || '.end',
152 '<<Exit>>');
153 END IF;
154
155 RETURN l_ledger_id;
156
157 EXCEPTION
158 WHEN OTHERS THEN
159 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
160 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
161 g_api || '.' || l_module || '.error',
162 SQLERRM);
163 END IF;
164 RAISE;
165 END get_ledger_id;
166
167
168
169 FUNCTION get_src_sys_code
170 (p_entity_id NUMBER)
171 RETURN NUMBER IS
172 l_src_sys_code NUMBER;
173
174 l_module VARCHAR2(30);
175 BEGIN
176 l_module := 'get_src_sys_code';
177
178 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
179 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
180 g_api || '.' || l_module || '.begin',
181 '<<Enter>>');
182 END IF;
183
184 SELECT fea.dim_attribute_numeric_member
185 INTO l_src_sys_code
186 FROM fem_entities_attr fea
187 WHERE fea.entity_id = p_entity_id
188 AND fea.attribute_id = g_entity_srcsys_attr_id
189 AND fea.version_id = g_entity_srcsys_v_id;
190
191 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
192 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
193 g_api || '.' || l_module || '.end',
194 '<<Exit>>');
195 END IF;
196
197 RETURN l_src_sys_code;
198
199 EXCEPTION
200 WHEN OTHERS THEN
201 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
202 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
203 g_api || '.' || l_module || '.error',
204 SQLERRM);
205 END IF;
206 RAISE;
207 END get_src_sys_code;
208
209
210 FUNCTION get_src_sys_code
211 (p_entity_id NUMBER,
212 p_cal_period_id_str NUMBER)
213 RETURN NUMBER IS
214 l_src_sys_code NUMBER;
215
216 l_module VARCHAR2(30);
217 BEGIN
218 l_module := 'get_src_sys_code_2param';
219
220 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
221 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
222 g_api || '.' || l_module || '.begin',
223 '<<Enter>>');
224 END IF;
225
226 SELECT gea.source_system_code
227 INTO l_src_sys_code
228 FROM gcs_entities_attr gea,
229 fem_cal_periods_attr fcpa_end_date
230 WHERE gea.entity_id = p_entity_id
231 AND gea.data_type_code = 'ACTUAL'
232 AND fcpa_end_date.cal_period_id = to_number(p_cal_period_id_str)
233 AND fcpa_end_date.attribute_id = g_cp_enddate_attr_id
234 AND fcpa_end_date.version_id = g_cp_enddate_v_id
235 AND fcpa_end_date.date_assign_value
236 BETWEEN gea.effective_start_date
237 AND nvl(gea.effective_end_date, fcpa_end_date.date_assign_value);
238
239 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
240 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
241 g_api || '.' || l_module || '.end',
242 '<<Exit>>');
243 END IF;
244
245 RETURN l_src_sys_code;
246
247 EXCEPTION
248 WHEN OTHERS THEN
249 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
250 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
251 g_api || '.' || l_module || '.error',
252 SQLERRM);
253 END IF;
254 RAISE;
255 END get_src_sys_code;
256
257
258
259
260 FUNCTION get_dataset_code
261 (p_entity_id NUMBER,
262 p_pristine_cal_period_id_str VARCHAR2)
263 RETURN NUMBER IS
264 l_dataset_code NUMBER;
265 l_ledger_id NUMBER;
266 l_src_sys_code NUMBER;
267
268 l_module VARCHAR2(30);
269 BEGIN
270 l_module := 'get_dataset_code';
271
272 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
273 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
274 g_api || '.' || l_module || '.begin',
275 '<<Enter>>');
276 END IF;
277
278 l_ledger_id := get_ledger_id(p_entity_id, p_pristine_cal_period_id_str);
279 l_src_sys_code := get_src_sys_code(p_entity_id, p_pristine_cal_period_id_str);
280
281 SELECT fdl.dataset_code
282 INTO l_dataset_code
283 FROM fem_data_locations fdl
284 WHERE fdl.ledger_id = l_ledger_id
285 AND fdl.cal_period_id = to_number(p_pristine_cal_period_id_str)
286 AND fdl.source_system_code = l_src_sys_code
287 AND fdl.table_name = 'FEM_BALANCES'
288 AND fdl.balance_type_code = 'ACTUAL';
289
290 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
291 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
292 g_api || '.' || l_module || '.end',
293 '<<Exit>>');
294 END IF;
295
296 RETURN l_dataset_code;
297
298 EXCEPTION
299 WHEN OTHERS THEN
300 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
301 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
302 g_api || '.' || l_module || '.error',
303 SQLERRM);
304 END IF;
305 RAISE;
306 END get_dataset_code;
307
308
309 FUNCTION get_pristine_cal_period_id
310 (p_entity_id NUMBER,
311 p_fch_cal_period_id_str VARCHAR2)
312 RETURN VARCHAR2 IS
313 l_pristine_cal_period_id NUMBER;
314
315 -- Cursor to check whether reverse mapping is needed
316 CURSOR same_calendar_check_c IS
317 SELECT 1
318 FROM gcs_data_sub_dtls gdsd
319 WHERE gdsd.entity_id = p_entity_id
320 AND gdsd.cal_period_id = to_number(p_fch_cal_period_id_str);
321
322 dummy NUMBER;
323
324 -- Cursor to handle the reverse mapping if necessary
325 CURSOR reverse_map_period_c IS
326 SELECT fcpb_source.cal_period_id source_cal_period_id
327 FROM fem_cal_periods_b fcpb_source,
328 fem_cal_periods_b fcpb_target,
329 gcs_cal_period_maps gcpm,
330 fem_cal_periods_attr fcpb_source_num,
331 fem_cal_periods_attr fcpb_source_year,
332 fem_cal_periods_attr fcpb_target_num,
333 fem_cal_periods_attr fcpb_target_year,
334 gcs_cal_period_map_dtls gcpmd
335 WHERE fcpb_target.cal_period_id = to_number(p_fch_cal_period_id_str)
336 AND fcpb_target.calendar_id = gcpm.target_calendar_id
337 AND fcpb_target.dimension_group_id = gcpm.target_dimension_group_id
338 AND fcpb_target_num.cal_period_id = to_number(p_fch_cal_period_id_str)
339 AND fcpb_target_year.cal_period_id = to_number(p_fch_cal_period_id_str)
340 AND fcpb_target_num.attribute_id = g_cp_num_attr_id
341 AND fcpb_target_num.version_id = g_cp_num_v_id
342 AND fcpb_target_year.attribute_id = g_cp_year_attr_id
343 AND fcpb_target_year.version_id = g_cp_year_v_id
344 AND gcpm.cal_period_map_id = gcpmd.cal_period_map_id
345 AND gcpmd.target_period_number = fcpb_target_num.number_assign_value
346 AND fcpb_source_num.attribute_id = g_cp_num_attr_id
347 AND fcpb_source_num.version_id = g_cp_num_v_id
348 AND fcpb_source_year.attribute_id = g_cp_year_attr_id
349 AND fcpb_source_year.version_id = g_cp_year_v_id
350 AND fcpb_source.cal_period_id = fcpb_source_num.cal_period_id
351 AND fcpb_source.cal_period_id = fcpb_source_year.cal_period_id
352 AND fcpb_source.calendar_id = gcpm.source_calendar_id
353 AND fcpb_source.dimension_group_id = gcpm.source_dimension_group_id
354 AND fcpb_source_num.number_assign_value = gcpmd.source_period_number
355 AND fcpb_source_year.number_assign_value =
356 DECODE(gcpmd.target_relative_year_code,
357 'CURRENT', fcpb_target_year.number_assign_value,
358 'PRIOR', fcpb_target_year.number_assign_value + 1,
359 'FOLLOWING', fcpb_target_year.number_assign_value - 1)
360 AND fcpb_source.cal_period_id IN
361 (SELECT gdsd.cal_period_id
362 FROM gcs_data_sub_dtls gdsd
363 WHERE gdsd.entity_id = p_entity_id)
364 ORDER BY fcpb_source_year.number_assign_value desc,
365 fcpb_source_num.number_assign_value desc;
366
367
368 l_module VARCHAR2(30);
369 BEGIN
370 l_module := 'get_pristine_cal_period_id';
371
372 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
373 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
374 g_api || '.' || l_module || '.begin',
375 '<<Enter>>');
376 END IF;
377
378 OPEN same_calendar_check_c;
379 FETCH same_calendar_check_c INTO dummy;
380 IF same_calendar_check_c%FOUND THEN
381 CLOSE same_calendar_check_c;
382
383 l_pristine_cal_period_id := to_number(p_fch_cal_period_id_str);
384 ELSE
385 CLOSE same_calendar_check_c;
386
387 OPEN reverse_map_period_c;
388 FETCH reverse_map_period_c INTO l_pristine_cal_period_id;
389 CLOSE reverse_map_period_c;
390
391 IF l_pristine_cal_period_id IS NULL THEN
392 RAISE NO_DATA_FOUND;
393 END IF;
394 END IF;
395
396 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
397 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
398 g_api || '.' || l_module || '.end',
399 '<<Exit>>');
400 END IF;
401
402 RETURN to_char(l_pristine_cal_period_id);
403
404 EXCEPTION
405 WHEN OTHERS THEN
406 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
407 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
408 g_api || '.' || l_module || '.error',
409 SQLERRM);
410 END IF;
411 RAISE;
412 END get_pristine_cal_period_id;
413
414
415 FUNCTION url_encode
416 (p_string_to_encode VARCHAR2)
417 RETURN VARCHAR2 IS
418 BEGIN
419 RETURN utl_url.escape(p_string_to_encode, TRUE, null);
420 END url_encode;
421
422
423 END GCS_DRILLDOWN_UTIL_PKG;