[Home] [Help]
PACKAGE BODY: APPS.FND_MO_REPORTING_API
Source
1 PACKAGE BODY FND_MO_REPORTING_API AS
2 /* $Header: FNDMORPB.pls 120.4 2005/07/02 03:11:43 appldev noship $ */
3
4 g_max_num NUMBER DEFAULT 100;
5 g_pred VARCHAR2(2000) DEFAULT NULL;
6 g_reporting_level VARCHAR2(10) DEFAULT '3000';
7 g_reporting_entity_id NUMBER;
8
9
10
11 --
12 -- Generic_Error (Internal)
13 --
14 -- Set error message and raise exception for unexpected sql errors.
15 --
16 PROCEDURE Generic_Error
17 ( routine IN VARCHAR2
18 , errcode IN NUMBER
19 , errmsg IN VARCHAR2
20 )
21 IS
22 BEGIN
23 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
24 fnd_message.set_token('ROUTINE', routine);
25 fnd_message.set_token('ERRNO', errcode);
26 fnd_message.set_token('REASON', errmsg);
27 app_exception.raise_exception;
28
29 EXCEPTION
30 WHEN OTHERS THEN RAISE;
31 END;
32
33
34
35
36 --
37 -- App_Error (Internal)
38 --
39 PROCEDURE App_Error
40 ( error_name IN VARCHAR2
41 , token1 IN VARCHAR2 DEFAULT NULL
42 , value1 IN VARCHAR2 DEFAULT NULL
43 , token2 IN VARCHAR2 DEFAULT NULL
44 , value2 IN VARCHAR2 DEFAULT NULL
45 , token3 IN VARCHAR2 DEFAULT NULL
46 , value3 IN VARCHAR2 DEFAULT NULL
47 , token4 IN VARCHAR2 DEFAULT NULL
48 , value4 IN VARCHAR2 DEFAULT NULL
49 )
50 IS
51 BEGIN
52 fnd_message.set_name('FND',error_name);
53
54 IF (token1 IS NOT NULL AND value1 IS NOT NULL)
55 THEN
56 fnd_message.set_token(token1,value1);
57 END IF;
58
59 IF (token2 IS NOT NULL AND value2 IS NOT NULL)
60 THEN
61 fnd_message.set_token(token2,value2);
62 END IF;
63
64 IF (token3 IS NOT NULL AND value3 IS NOT NULL)
65 THEN
66 fnd_message.set_token(token3,value3);
67 END IF;
68
69 IF (token4 IS NOT NULL AND value4 IS NOT NULL)
70 THEN
71 fnd_message.set_token(token4,value4);
72 END IF;
73
74 app_exception.raise_exception;
75
76 EXCEPTION
77 WHEN OTHERS THEN
78 Generic_Error( 'FND_MO_REPORTING_API.App_Error'
79 , sqlcode
80 , sqlerrm);
81 END App_Error;
82
83
84
85
86 --
87 -- Initialize
88 --
89 PROCEDURE Initialize
90 ( p_reporting_level IN VARCHAR2
91 , p_reporting_entity_id IN NUMBER
92 , p_pred_type IN VARCHAR2 DEFAULT 'AUTO'
93 )
94 IS
95
96 l_count NUMBER := 0;
97 l_pred VARCHAR2(2000) DEFAULT NULL;
98 l_pred_type VARCHAR2(30) DEFAULT 'EXISTS';
99 l_multi_org_enabled fnd_product_groups.multi_org_flag%TYPE;
100
101
102 BEGIN
103
104 -- Validate_Reporting_Level( p_reporting_level );
105 -- Validate_Reporting_Entity( p_reporting_level, p_reporting_entity_id );
106
107 g_reporting_level := p_reporting_level;
108 g_reporting_entity_id := p_reporting_entity_id;
109
110 SELECT multi_org_flag
111 INTO l_multi_org_enabled
112 FROM fnd_product_groups
113 WHERE product_group_id = 1;
114
115 IF ( p_pred_type = 'AUTO'
116 OR p_pred_type = 'INSTR'
117 OR p_pred_type IS NULL
118 )
119 THEN
120
121 IF p_reporting_level = '1000' THEN
122 SELECT count(*)
123 INTO l_count
124 FROM hr_organization_information
125 WHERE org_information_context = 'Operating Unit Information'
126 AND TO_NUMBER(org_information3) = p_reporting_entity_id;
127 END IF;
128
129 IF ( l_count <= g_max_num )
130 THEN
131 l_pred_type := 'INSTR';
132 ELSE
133 l_pred_type := 'EXISTS';
134 END IF;
135
136 ELSIF ( p_pred_type = 'EXISTS'
137 OR p_pred_type = 'IN_SELECT'
138 OR p_pred_type = 'IN_LIST'
139 )
140 THEN
141
142 l_pred_type := p_pred_type;
143
144 END IF;
145
146
147 IF ( p_reporting_level = '3000' ) /* Operating Unit Level */
148 THEN
149 l_pred := ' AND NVL(/*ALIAS*/ORG_ID, :p_reporting_entity_id) = '
150 ||':p_reporting_entity_id ';
151
152 ELSIF (
153 p_reporting_level = '1000' /* Set of Books Level */
154 AND l_pred_type = 'IN_LIST'
155 )
156 THEN
157
158 l_pred := ' AND (NVL(/*ALIAS*/ORG_ID, -99) IN ( -99';
159
160 FOR l_org_rec IN
161 ( SELECT organization_id
162 FROM hr_organization_information
163 WHERE org_information_context = 'Operating Unit Information'
164 AND DECODE( p_reporting_level
165 , '1000' , TO_NUMBER(org_information3)
166 , '3000' , organization_id ) =
167 p_reporting_entity_id )
168 LOOP
169
170 l_pred := l_pred||', '||l_org_rec.organization_id;
171
172 END LOOP;
173
174 l_pred := l_pred||')) ';
175
176 ELSIF ( p_reporting_level = '1000' AND l_pred_type = 'INSTR'
177 )
178 THEN
179
180 l_pred := ' AND (/*ALIAS*/ORG_ID IS NULL OR INSTRB(''*';
181
182 FOR l_org_rec IN
183 ( SELECT organization_id
184 FROM hr_organization_information
185 WHERE org_information_context = 'Operating Unit Information'
186 AND DECODE( p_reporting_level
187 , '1000' , TO_NUMBER(org_information3)
188 , '3000' , organization_id ) =
189 p_reporting_entity_id )
190 LOOP
191
192 l_pred := l_pred||l_org_rec.organization_id||'*';
193
194 END LOOP;
195
196 l_pred := l_pred||''', ''*''||TO_CHAR(/*ALIAS*/ORG_ID)||''*'') > 0) ';
197
198 ELSIF ( p_reporting_level = '1000' AND l_pred_type = 'EXISTS'
199 )
200 THEN
201
202 l_pred :=
203 ' AND (/*ALIAS*/org_id IS NULL OR '
204 ||'EXISTS '
205 ||'( SELECT /*HINT*/ 1'
206 || ' FROM hr_organization_information org_info'
207 || ' WHERE /*ALIAS*/org_id = org_info.organization_id'
208 || ' AND org_info.org_information_context = ''Operating Unit Information''';
209
210 IF (p_reporting_level = '1000')
211 THEN
212 l_pred := l_pred||' AND TO_NUMBER(org_info.org_information3) = '
213 || ' :p_reporting_entity_id';
214
215 ELSIF (p_reporting_level = '2000')
216 THEN
217 l_pred := l_pred||' AND TO_NUMBER(org_info.org_information2) = '
218 || ' :p_reporting_entity_id';
219
220 END IF;
221
222 l_pred := l_pred||')) ';
223
224 ELSIF ( p_reporting_level = '1000' AND l_pred_type = 'IN_SELECT'
225 )
226 THEN
227
228 l_pred :=
229 ' AND (NVL(/*ALIAS*/org_id, -99) IN '
230 ||'( SELECT -99'
231 || ' FROM DUAL'
232 || ' UNION'
233 || ' SELECT /*HINT*/ organization_id'
234 || ' FROM hr_organization_information'
235 || ' WHERE org_information_context = ''Operating Unit Information''';
236
237 IF (p_reporting_level = '1000')
238 THEN
239 l_pred := l_pred||' AND TO_NUMBER(org_information3) = '
240 || ' :p_reporting_entity_id';
241
242 ELSIF (p_reporting_level = '2000')
243 THEN
244 l_pred := l_pred||' AND TO_NUMBER(org_information2) = '
245 || ' :p_reporting_entity_id';
246
247 END IF;
248
249 l_pred := l_pred||')) ';
250
251 END IF;
252
253 g_pred := l_pred;
254
255 l_pred := NULL;
256
257 EXCEPTION
258 WHEN OTHERS THEN
259 Generic_Error( 'FND_MO_REPORTING_API.Initialize'
260 , sqlcode
261 , sqlerrm);
262
263 END Initialize;
264
265
266 --
267 -- Get predicate
268 --
269 FUNCTION Get_Predicate
270 ( p_alias IN VARCHAR2 DEFAULT NULL
271 , p_hint IN VARCHAR2 DEFAULT NULL
272 , p_variable_override IN VARCHAR2 DEFAULT ' :p_reporting_entity_id '
273 )
274 RETURN VARCHAR2
275 IS
276
277 l_return_pred VARCHAR2(2000) DEFAULT NULL;
278
279 BEGIN
280
281 l_return_pred := replace( g_pred
282 , '/*ALIAS*/'
283 , p_alias||'.');
284
285 l_return_pred := replace( l_return_pred
286 , '/*HINT*/'
287 , '/* '||p_hint||' */');
288
289 l_return_pred := replace( l_return_pred
290 , ':p_reporting_entity_id'
291 , p_variable_override);
292
293 RETURN l_return_pred;
294
295 EXCEPTION
296 WHEN OTHERS THEN
297 Generic_Error( 'FND_MO_REPORTING_API.Get_Predicate'
298 , sqlcode
299 , sqlerrm);
300
301 END Get_Predicate;
302
303
304 --
305 -- Get the reporting level name
306 --
307 FUNCTION Get_Reporting_Level_Name
308 RETURN VARCHAR2
309 IS
310 l_reporting_level_name fnd_lookups.meaning%TYPE;
311
312 BEGIN
313 SELECT meaning
314 INTO l_reporting_level_name
315 FROM fnd_lookups
316 WHERE lookup_type = 'FND_MO_REPORTING_LEVEL'
317 AND lookup_code = g_reporting_level;
318
319 RETURN l_reporting_level_name;
320
321 EXCEPTION
322 WHEN OTHERS THEN
323 Generic_Error( 'FND_MO_REPORTING_API.Get_Reporting_Level_Name'
324 , sqlcode
325 , sqlerrm);
326
327 END Get_Reporting_Level_Name;
328
329
330
331
332 --
333 -- Get the reporting entity name
334 --
335 FUNCTION Get_Reporting_Entity_Name
336 RETURN VARCHAR2
337 IS
338 l_reporting_entity_name fnd_mo_reporting_entities_v
339 .entity_name%TYPE;
340
341 BEGIN
342
343 IF g_reporting_level = '1000'
344 THEN
345 SELECT name
346 INTO l_reporting_entity_name
347 FROM gl_sets_of_books
348 WHERE set_of_books_id = g_reporting_entity_id;
349 ELSE
350 SELECT name
351 INTO l_reporting_entity_name
352 FROM hr_all_organization_units
353 WHERE organization_id = g_reporting_entity_id;
354 END IF;
355
356 RETURN l_reporting_entity_name;
357
358 EXCEPTION
359 WHEN OTHERS THEN
360 Generic_Error( 'FND_MO_REPORTING_API.Get_Reporting_Entity_Name'
361 , sqlcode
362 , sqlerrm);
363
364 END Get_Reporting_Entity_Name;
365
366
367
368
369 --
370 -- Validate the reporting level
371 --
372 PROCEDURE Validate_Reporting_Level
373 ( p_reporting_level IN VARCHAR2 )
374 IS
375
376 l_top_reporting_level fnd_profile_option_values.profile_option_value%TYPE;
377
378 CURSOR l_check_reporting_level
379 ( x_reporting_level IN VARCHAR2
380 , x_top_reporting_level IN VARCHAR2
381 )
382 IS
383 SELECT 1
384 FROM fnd_lookups lp
385 WHERE lookup_type = 'FND_MO_REPORTING_LEVEL'
386 AND lookup_code = x_reporting_level
387 AND TO_NUMBER(lookup_code) >=
388 TO_NUMBER(x_top_reporting_level);
389
390 l_dummy NUMBER;
391
392 BEGIN
393
394 l_top_reporting_level := fnd_profile.value('FND_MO_TOP_REPORTING_LEVEL');
395
396 OPEN l_check_reporting_level( p_reporting_level, l_top_reporting_level);
397 FETCH l_check_reporting_level INTO l_dummy;
398
399 IF (l_check_reporting_level%NOTFOUND )
400 THEN
401 CLOSE l_check_reporting_level;
402 App_Error( 'FND_MO_RPTAPI_LEVEL'
403 , 'REPORTING_LEVEL', p_reporting_level
404 , 'TOP_REPORTING_LEVEL', l_top_reporting_level);
405
406 END IF;
407
408 CLOSE l_check_reporting_level;
409
410 EXCEPTION
411 WHEN OTHERS THEN
412 Generic_Error( 'FND_MO_REPORTING_API.Validate_Reporting_Level'
413 , sqlcode
414 , sqlerrm);
415
416 END Validate_Reporting_Level;
417
418
419
420
421 --
422 -- Validate the reporting entity
423 --
424 PROCEDURE Validate_Reporting_Entity
425 ( p_reporting_level IN VARCHAR2
426 , p_reporting_entity_id IN NUMBER
427 )
428 IS
429
430 CURSOR l_check_reporting_entity
431 ( x_reporting_level IN VARCHAR2
432 , x_reporting_entity_id IN NUMBER
433 )
434 IS
435 SELECT 1
436 FROM FND_MO_REPORTING_ENTITIES_V
437 WHERE reporting_level = p_reporting_level
438 AND DECODE( fnd_profile.value_wnps('FND_MO_TOP_REPORTING_LEVEL')
439 , '1000', ledger_id
440 , '3000', operating_unit_id) =
441 ( SELECT DECODE( fnd_profile.value_wnps('FND_MO_TOP_REPORTING_LEVEL')
442 , '1000', TO_NUMBER(org_information3)
443 , '3000', organization_id )
444 FROM hr_organization_information
445 WHERE organization_id = fnd_profile.value_wnps('ORG_ID')
446 AND org_information_context = 'Operating Unit Information'
447 )
448 AND entity_id = x_reporting_entity_id;
449
450 l_dummy NUMBER;
451
452 BEGIN
453
454 OPEN l_check_reporting_entity ( p_reporting_level, p_reporting_entity_id );
455 FETCH l_check_reporting_entity INTO l_dummy;
456
457 IF ( l_check_reporting_entity%NOTFOUND )
458 THEN
459 CLOSE l_check_reporting_entity;
460 App_Error('FND_MO_RPTAPI_ENTITY', 'REPORTING_ENTITY', p_reporting_entity_id);
461
462 END IF;
463
464 CLOSE l_check_reporting_entity;
465
466 EXCEPTION
467 WHEN OTHERS THEN
468 Generic_Error( 'FND_MO_REPORTING_API.Validate_Reporting_Entity'
469 , sqlcode
470 , sqlerrm);
471
472 END Validate_Reporting_Entity;
473
474
475
476
477 END FND_MO_REPORTING_API;