eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.XLA_RPT_SARDLR_PKG

Source


1 PACKAGE BODY XLA_RPT_SARDLR_PKG AS
2 /* $Header: xlasardlr.pkb 120.0.12010000.3 2009/09/14 10:51:29 kapkumar noship $ */
3 /*======================================================================+
4 |             Copyright (c) 2009-2010 Oracle Corporation                |
5 |                       Redwood Shores, CA, USA                         |
6 |                         All rights reserved.                          |
7 +=======================================================================+
8 | PACKAGE NAME                                                          |
9 |    xla_rpt_sardlr_pkg                                                     |
10 |                                                                       |
11 |                                                                       |
12 | DESCRIPTION                                                           |
13 |  Package for Subledger Accounting Rules Detail Listing Report         |
14 |  to retrieve line-level information for ADR details                   |
15 |                                                                       |
16 | HISTORY                                                               |
17 |    AUG-09  Kapil Kumar                          Created               |
18 |                                                                       |
19 +======================================================================*/
20 
21 
22 --=============================================================================
23 --               *********** Local Trace Routine **********
24 --=============================================================================
25 C_LEVEL_STATEMENT     CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
26 C_LEVEL_PROCEDURE     CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
27 C_LEVEL_EVENT         CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
28 C_LEVEL_EXCEPTION     CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
29 C_LEVEL_ERROR         CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
30 C_LEVEL_UNEXPECTED    CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
31 C_LEVEL_LOG_DISABLED  CONSTANT NUMBER := 99;
32 C_DEFAULT_MODULE      CONSTANT VARCHAR2(240):= 'xla.plsql.xla_rpt_sardlr_pkg';
33 
34 g_log_level           NUMBER;
35 g_log_enabled         BOOLEAN;
36 
37 
38 
39 PROCEDURE trace
40        (p_msg                        IN VARCHAR2
41        ,p_level                      IN NUMBER
42        ,p_module                     IN VARCHAR2) IS
43 BEGIN
44       IF (p_msg IS NULL AND p_level >= g_log_level) THEN
45          fnd_log.message(p_level, NVL(p_module,C_DEFAULT_MODULE));
46       ELSIF p_level >= g_log_level THEN
47          fnd_log.string(p_level, NVL(p_module,C_DEFAULT_MODULE), p_msg);
48       END IF;
49 
50 EXCEPTION
51       WHEN xla_exceptions_pkg.application_exception THEN
52          RAISE;
53       WHEN OTHERS THEN
54          xla_exceptions_pkg.raise_message
55             (p_location   => 'xla_rpt_sardlr_pkg.trace');
56 END trace;
57 
58 
59 
60 
61 
62 FUNCTION beforeReport  RETURN BOOLEAN IS
63 
64 l_log_module                    VARCHAR2(240);
65 
66 BEGIN
67 
68 
69 	IF g_log_enabled THEN
70 		l_log_module := C_DEFAULT_MODULE||'.beforeReport';
71 	END IF;
72 
73 	IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
74 		trace('Begin of beforeReport',C_LEVEL_PROCEDURE,l_log_module);
75 	END IF;
76 
77 	IF (C_LEVEL_STATEMENT >= g_log_level) THEN
78 
79 		trace('P_APPLICATION_ID = '|| to_char(P_APPLICATION_ID),
80 		       C_LEVEL_STATEMENT, l_log_module);
81 		trace('P_SLAM_CODE = '|| P_SLAM_CODE,
82 		       C_LEVEL_STATEMENT, l_log_module);
83 		trace('P_SLAM_TYPE_CODE = '|| P_SLAM_TYPE_CODE,
84 		       C_LEVEL_STATEMENT, l_log_module);
85 		trace('P_EVENT_CLASS_CODE = '|| P_EVENT_CLASS_CODE,
86 		       C_LEVEL_STATEMENT, l_log_module);
87 		trace('P_EVENT_TYPE_CODE = '|| P_EVENT_TYPE_CODE,
88 		       C_LEVEL_STATEMENT, l_log_module);
89 		trace('P_AMB_CONTEXT_CODE = '|| P_AMB_CONTEXT_CODE,
90 		       C_LEVEL_STATEMENT, l_log_module);
91 		trace('P_EVENT_CLASS_NAME = '|| P_EVENT_CLASS_NAME,
92 		       C_LEVEL_STATEMENT, l_log_module);
93 		trace('P_EVENT_TYPE_NAME = '|| P_EVENT_TYPE_NAME,
94 		       C_LEVEL_STATEMENT, l_log_module);
95 
96 
97 	END IF;
98 
99 	IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
100 		 trace('End of beforeReport'
101 		       ,C_LEVEL_PROCEDURE, l_log_module);
102 	END IF;
103 
104    RETURN TRUE;
105 
106 EXCEPTION
107    WHEN OTHERS THEN
108       xla_exceptions_pkg.raise_message
109          (p_location  => 'xla_rpt_sardlr_pkg.beforeReport');
110 END beforeReport;
111 
112 
113 
114 
115 
116 
117 
118 FUNCTION populate_fields
119     (value_type_code                IN  VARCHAR2,
120      value_source_application_id    IN  NUMBER,
121      value_source_type_code         IN  VARCHAR2,
122      value_source_code              IN  VARCHAR2,
123      value_mapping_set_code         IN  VARCHAR2,
124      value_code_combination_id      IN  NUMBER,
125      amb_context_code               IN  VARCHAR2,
126      value_segment_rule_appl_id     IN  NUMBER,
127      value_segment_rule_type_code   IN  VARCHAR2,
128      value_segment_rule_code        IN  VARCHAR2,
129      flexfield_assign_mode_code     IN  VARCHAR2,
130      value_constant                 IN  VARCHAR2,
131      flex_value_set_id              IN  NUMBER,
132      input_source_code              IN  VARCHAR2,
133      input_source_application_id    IN  NUMBER,
134      input_source_type_code         IN  VARCHAR2,
135      value_flexfield_segment_code   IN  VARCHAR2,
136      transaction_coa_id             IN  NUMBER,
137      accounting_coa_id              IN  NUMBER,
138      p_mode                         IN  NUMBER
139     )
140 RETURN VARCHAR2
141 
142 IS
143 
144 
145 --=============================================================================
146 --               *********** Function Variables **********
147 --=============================================================================
148 
149 input_source_view_appl_id       NUMBER;
150 input_source_lookup_type        VARCHAR2(250);
151 input_source_value_set_id       NUMBER;
152 input_source_flex_appl_id       NUMBER;
153 input_source_id_flex_code       VARCHAR2(250);
154 input_source_segment_code       VARCHAR2(250);
155 input_source_id_flex_num        NUMBER;
156 input_source_name               VARCHAR2(250);
157 
158 value_flexfield_segment_name    VARCHAR2(250);
159 value_dsp                       VARCHAR2(250);
160 value_source_flex_appl_id       NUMBER;
161 value_source_id_flex_code       VARCHAR2(250);
162 value_source_segment_code       VARCHAR2(250);
163 value_ms_view_application_id    NUMBER;
164 value_ms_lookup_type            VARCHAR2(250);
165 value_ms_value_set_id           NUMBER;
166 
167 l_log_module                    VARCHAR2(240);
168 --=============================================================================
169 --               *********** Cursors **********
170 --=============================================================================
171 
172 
173     CURSOR c_source_name
174       (p_source_application_id  IN  NUMBER
175       ,p_source_type_code       IN  VARCHAR2
176       ,p_source_code            IN  VARCHAR2)
177     IS
178     SELECT name
179       FROM xla_sources_vl
180      WHERE application_id   = p_source_application_id
181        AND source_type_code = p_source_type_code
182        AND source_code      = p_source_code;
183 
184     CURSOR c_value_sources
185       (p_source_application_id  IN  NUMBER
186       ,p_source_type_code       IN  VARCHAR2
187       ,p_source_code            IN  VARCHAR2)
188     IS
189     SELECT name, flexfield_application_id, id_flex_code, segment_code
190       FROM xla_sources_vl
191      WHERE application_id   = p_source_application_id
192        AND source_type_code = p_source_type_code
193        AND source_code      = p_source_code;
194 
195     CURSOR c_input_source_name
196       (p_source_application_id  IN  NUMBER
197       ,p_source_type_code       IN  VARCHAR2
198       ,p_source_code            IN  VARCHAR2)
199     IS
200     SELECT name, view_application_id, lookup_type, flex_value_set_id,
201            flexfield_application_id, id_flex_code, segment_code
202       FROM xla_sources_vl
203      WHERE application_id   = p_source_application_id
204        AND source_type_code = p_source_type_code
205        AND source_code      = p_source_code;
206 
207 
208     CURSOR c_mapping_set
209       (p_mapping_set_code  IN  VARCHAR2)
210     IS
211     SELECT name, view_application_id, lookup_type, value_set_id
212       FROM xla_mapping_sets_vl
213      WHERE mapping_set_code  = p_mapping_set_code;
214 
215     CURSOR c_adr
216       (p_amb_context_code             IN VARCHAR2
217       ,p_value_segment_rule_appl_id   IN VARCHAR2
218       ,p_value_segment_rule_type_code IN VARCHAR2
219       ,p_value_segment_rule_code      IN VARCHAR2)
220     IS
221     SELECT name
222       FROM xla_seg_rules_fvl
223      WHERE amb_context_code       = p_amb_context_code
224        AND application_id         = p_value_segment_rule_appl_id
225        AND segment_rule_type_code = p_value_segment_rule_type_code
226        AND segment_rule_code      = p_value_segment_rule_code;
227 
228 BEGIN
229 
230 
231 
232 
233 IF g_log_enabled THEN
234 	l_log_module := C_DEFAULT_MODULE||'.populate_fields';
235 END IF;
236 
237 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
238 	trace('Begin of populate_fields',C_LEVEL_PROCEDURE,l_log_module);
239 END IF;
240 
241 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
242 
243 	trace('value_source_application_id = '|| to_char(value_source_application_id),
244 		C_LEVEL_STATEMENT, l_log_module);
245 	trace('value_segment_rule_appl_id = '|| to_char(value_segment_rule_appl_id),
246 		C_LEVEL_STATEMENT, l_log_module);
247 	trace('flex_value_set_id = '|| to_char(flex_value_set_id),
248 		C_LEVEL_STATEMENT, l_log_module);
249 	trace('input_source_application_id = '|| to_char(input_source_application_id),
250 		C_LEVEL_STATEMENT, l_log_module);
251 	trace('transaction_coa_id = '|| to_char(transaction_coa_id),
252 		C_LEVEL_STATEMENT, l_log_module);
253 	trace('value_type_code = '|| value_type_code,
254 		C_LEVEL_STATEMENT, l_log_module);
255 	trace('value_source_type_code = '|| value_source_type_code,
256 		C_LEVEL_STATEMENT, l_log_module);
257 	trace('value_source_code = '|| value_source_code,
258 		C_LEVEL_STATEMENT, l_log_module);
259 	trace('value_mapping_set_code = '|| value_mapping_set_code,
260 		C_LEVEL_STATEMENT, l_log_module);
261 	trace('amb_context_code = '|| amb_context_code,
262 		C_LEVEL_STATEMENT, l_log_module);
263 	trace('value_segment_rule_type_code = '|| value_segment_rule_type_code,
264 		C_LEVEL_STATEMENT, l_log_module);
265 	trace('value_segment_rule_code = '|| value_segment_rule_code,
266 		C_LEVEL_STATEMENT, l_log_module);
267 	trace('flexfield_assign_mode_code = '|| flexfield_assign_mode_code,
268 		C_LEVEL_STATEMENT, l_log_module);
269 	trace('value_constant = '|| value_constant,
270 		C_LEVEL_STATEMENT, l_log_module);
271 	trace('input_source_code = '|| input_source_code,
272 		C_LEVEL_STATEMENT, l_log_module);
273 	trace('input_source_type_code = '|| input_source_type_code,
274 		C_LEVEL_STATEMENT, l_log_module);
275 	trace('value_flexfield_segment_code = '|| value_flexfield_segment_code,
276 		C_LEVEL_STATEMENT, l_log_module);
277 END IF;
278 
279 
280 
281 IF value_type_code = 'S' THEN
282 
283 	OPEN c_value_sources
284 	(value_source_application_id
285 	,value_source_type_code
286 	,value_source_code);
287 	FETCH c_value_sources
288 	INTO value_dsp, value_source_flex_appl_id,
289 	value_source_id_flex_code, value_source_segment_code;
290 	CLOSE c_value_sources;
291 
292 ELSIF value_type_code = 'M' THEN
293 
294 	OPEN c_mapping_set
295 	(value_mapping_set_code);
296 	FETCH c_mapping_set
297 	INTO value_dsp
298 	, value_ms_view_application_id
299 	, value_ms_lookup_type
300 	, value_ms_value_set_id;
301 	CLOSE c_mapping_set;
302 
303 ELSIF  value_type_code = 'A' THEN
304 
305 	OPEN c_adr
306 	(amb_context_code
307 	,value_segment_rule_appl_id
308 	,value_segment_rule_type_code
309 	,value_segment_rule_code);
310 	FETCH c_adr
311 	INTO value_dsp;
312 	CLOSE c_adr;
313 
314 
315 ELSIF value_type_code = 'C' THEN
316 
317 	IF flexfield_assign_mode_code = 'S' THEN
318 		value_dsp := value_constant;
319 	ELSIF  flexfield_assign_mode_code = 'V' THEN
320 		IF  value_constant IS NOT NULL THEN
321 			IF flex_value_set_id IS NOT NULL    THEN
322 				value_dsp := xla_flex_pkg.get_flex_value_meaning
323 							(p_flex_value_set_id => flex_value_set_id
324 							,p_flex_value        => value_constant);
325 			END IF;
326 		END IF;
327 	ELSIF  flexfield_assign_mode_code = 'A' THEN
328 	    value_dsp := FND_FLEX_EXT.GET_SEGS('SQLGL', 'GL#', accounting_coa_id, value_code_combination_id);
329 	END IF;
330 
331 ELSE value_dsp := NULL;
332 END IF;
333 
334 
335 IF p_mode = 1 THEN    --ADR_VALUE field
336 
337 	IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
338 		 trace('End of populate_fields (1) return' || value_dsp
339 		       ,C_LEVEL_PROCEDURE, l_log_module);
340 	END IF;
341 
342   RETURN value_dsp;
343 
344 END IF;
345 
346 
347 
348 input_source_name := NULL;   -- default null value for ADR_INPUT
349 value_flexfield_segment_name := NULL;   -- default null value for ADR_SEGMENT
350 
351 
352 IF input_source_code IS NOT NULL THEN
353 
354 	OPEN c_input_source_name
355 	( input_source_application_id
356 	, input_source_type_code
357 	, input_source_code);
358 	FETCH c_input_source_name
359 	INTO  input_source_name
360 	, input_source_view_appl_id
361 	, input_source_lookup_type
362 	, input_source_value_set_id
363 	, input_source_flex_appl_id
364 	, input_source_id_flex_code
365 	, input_source_segment_code;
366 	CLOSE c_input_source_name;
367 
368 	IF (input_source_flex_appl_id IS NOT NULL AND input_source_segment_code IS NULL) THEN
369 		IF (input_source_flex_appl_id = 101 AND input_source_id_flex_code = 'GL#') THEN
370 			input_source_id_flex_num := NULL;
371 		ELSE
372 			input_source_id_flex_num := xla_flex_pkg.get_flexfield_structure
373 								(p_application_id   => input_source_flex_appl_id
374 								,p_id_flex_code     => input_source_id_flex_code);
375 		END IF;
376 	END IF;
377 END IF;
378 
379 
380 IF P_MODE = 2 THEN   -- ADR_INPUT field
381 
382 	IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
383 		 trace('End of populate_fields (2) return' || input_source_name
384 		       ,C_LEVEL_PROCEDURE, l_log_module);
385 	END IF;
386 
387 	RETURN input_source_name;
388 END IF;
389 
390 
391 
392 
393 IF value_flexfield_segment_code IS NOT NULL THEN
394 	IF (value_source_flex_appl_id IS NOT NULL OR (input_source_flex_appl_id = 101 AND input_source_id_flex_code = 'GL#')) THEN
395 		IF transaction_coa_id IS NOT NULL THEN
396 
397 			value_flexfield_segment_name := xla_flex_pkg.get_flexfield_segment_name
398 								(p_application_id         => 101
399 								,p_flex_code              => 'GL#'
400 								,p_chart_of_accounts_id   => transaction_coa_id
401 								,p_flexfield_segment_code => value_flexfield_segment_code);
402 
403 		ELSE
404 			value_flexfield_segment_name := xla_flex_pkg.get_qualifier_name
405 								(p_application_id         => 101
406 								,p_id_flex_code              => 'GL#'
407 								,p_qualifier_segment      => value_flexfield_segment_code);
408 
409 		END IF;
410 	ELSE
411 		value_flexfield_segment_name := xla_flex_pkg.get_flexfield_segment_name
412 								(p_application_id         => input_source_flex_appl_id
413 								,p_flex_code              => input_source_id_flex_code
414 								,p_chart_of_accounts_id   => input_source_id_flex_num
415 								,p_flexfield_segment_code => value_flexfield_segment_code);
416 
417 	END IF;
418 END IF;
419 
420 
421 IF P_MODE = 3 THEN    --ADR_SEGMENT field
422 
423 	IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
424 		 trace('End of populate_fields (3) return' || value_flexfield_segment_name
425 		       ,C_LEVEL_PROCEDURE, l_log_module);
426 	END IF;
427 
428 
429 	RETURN value_flexfield_segment_name;
430 END IF;
431 
432 
433 
434 
435 EXCEPTION
436    WHEN OTHERS THEN
437       xla_exceptions_pkg.raise_message
438          (p_location  => 'xla_rpt_sardlr_pkg.beforeReport');
439 
440 END populate_fields;
441 
442 
443 
444 BEGIN
445    g_log_level      := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
446    g_log_enabled    := fnd_log.test
447                        (log_level  => g_log_level
448                        ,MODULE     => C_DEFAULT_MODULE);
449 
450    IF NOT g_log_enabled  THEN
451       g_log_level := C_LEVEL_LOG_DISABLED;
452    END IF;
453 
454 END xla_rpt_sardlr_pkg;