[Home] [Help]
PACKAGE BODY: APPS.XLA_MAPPING_SETS_PKG
Source
1 PACKAGE BODY xla_mapping_sets_pkg AS
2 /* $Header: xlaamdms.pkb 120.7 2004/11/02 18:59:34 wychan ship $ */
3 /*======================================================================+
4 | Copyright (c) 1995-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +=======================================================================+
8 | PACKAGE NAME |
9 | xla_mapping_sets_pkg |
10 | |
11 | DESCRIPTION |
12 | XLA Mapping Sets Package |
13 | |
14 | HISTORY |
15 | 01-May-01 Dimple Shah Created |
16 | |
17 +======================================================================*/
18
19 /*======================================================================+
20 | |
21 | Public Procedure |
22 | |
23 | delete_mapping_set_details |
24 | |
25 | Deletes all details of the mapping set |
26 | |
27 +======================================================================*/
28
29 PROCEDURE delete_mapping_set_details
30 (p_mapping_set_code IN VARCHAR2
31 ,p_amb_context_code IN VARCHAR2)
32 IS
33
34 BEGIN
35
36 xla_utility_pkg.trace('> xla_mapping_sets_pkg.delete_mapping_set_details' , 10);
37
38 xla_utility_pkg.trace('mapping_set_code = '||p_mapping_set_code , 20);
39
40 DELETE
41 FROM xla_mapping_set_values
42 WHERE mapping_set_code = p_mapping_set_code
43 AND amb_context_code = p_amb_context_code;
44
45 xla_utility_pkg.trace('< xla_mapping_sets_pkg.delete_mapping_set_details' , 10);
46
47 EXCEPTION
48 WHEN xla_exceptions_pkg.application_exception THEN
49 RAISE;
50 WHEN OTHERS THEN
51 xla_exceptions_pkg.raise_message
52 (p_location => 'xla_mapping_sets_pkg.delete_seg_rule_details');
53
54 END delete_mapping_set_details;
55
56 /*======================================================================+
57 | |
58 | Public Function |
59 | |
60 | mapping_set_in_use |
61 | |
62 | Returns true if the mapping set is in use by an account |
63 | derivation rule |
64 | |
65 +======================================================================*/
66
67 FUNCTION mapping_set_in_use
68 (p_event IN VARCHAR2
69 ,p_mapping_set_code IN VARCHAR2
70 ,p_amb_context_code IN VARCHAR2
71 ,p_application_id IN OUT NOCOPY NUMBER
72 ,p_segment_rule_code IN OUT NOCOPY VARCHAR2
73 ,p_segment_rule_type_code IN OUT NOCOPY VARCHAR2)
74 RETURN BOOLEAN
75 IS
76
77 l_return BOOLEAN;
78
79 CURSOR c_assignment_exist
80 IS
81 SELECT application_id, amb_context_code, segment_rule_code, segment_rule_type_code
82 FROM xla_seg_rule_details
83 WHERE value_mapping_set_code = p_mapping_set_code
84 AND amb_context_code = p_amb_context_code
85 AND value_mapping_set_code is not null;
86
87 l_assignment_exist c_assignment_exist%rowtype;
88
89 BEGIN
90
91 xla_utility_pkg.trace('> xla_mapping_sets_pkg.mapping_set_in_use' , 10);
92
93 xla_utility_pkg.trace('event = '||p_event , 20);
94 xla_utility_pkg.trace('mapping_set_code = '||p_mapping_set_code , 20);
95
96 IF p_event in ('DELETE','UPDATE','DISABLE') THEN
97 OPEN c_assignment_exist;
98 FETCH c_assignment_exist
99 INTO l_assignment_exist;
100 IF c_assignment_exist%found then
101 p_application_id := l_assignment_exist.application_id;
102 p_segment_rule_code := l_assignment_exist.segment_rule_code;
103 p_segment_rule_type_code := l_assignment_exist.segment_rule_type_code;
104
105 l_return := TRUE;
106 ELSE
107 p_application_id := null;
108 p_segment_rule_code := null;
109 p_segment_rule_type_code := null;
110
111 l_return := FALSE;
112 END IF;
113 CLOSE c_assignment_exist;
114
115 ELSE
116 xla_exceptions_pkg.raise_message
117 ('XLA' ,'XLA_COMMON_ERROR'
118 ,'ERROR' ,'Invalid event passed'
119 ,'LOCATION' ,'xla_mapping_sets_pkg.mapping_set_in_use');
120
121 END IF;
122
123 xla_utility_pkg.trace('< xla_mapping_sets_pkg.mapping_set_in_use' , 10);
124
125 return l_return;
126
127 EXCEPTION
128 WHEN xla_exceptions_pkg.application_exception THEN
129 IF c_assignment_exist%ISOPEN THEN
130 CLOSE c_assignment_exist;
131 END IF;
132
133 RAISE;
134 WHEN OTHERS THEN
135 IF c_assignment_exist%ISOPEN THEN
136 CLOSE c_assignment_exist;
137 END IF;
138
139 xla_exceptions_pkg.raise_message
140 (p_location => 'xla_mapping_sets_pkg.mapping_set_in_use');
141
142 END mapping_set_in_use;
143
144 /*======================================================================+
145 | |
146 | Public Function |
147 | |
148 | mapping_set_is_locked |
149 | |
150 | Returns true if the mapping set is in use by a locked product rule |
151 | |
152 +======================================================================*/
153
154 FUNCTION mapping_set_is_locked
155 (p_mapping_set_code IN VARCHAR2
156 ,p_amb_context_code IN VARCHAR2)
157 RETURN BOOLEAN
158 IS
159
160 l_return BOOLEAN;
161 l_exist VARCHAR2(1);
162
163 CURSOR c_frozen_assignment_exist
164 IS
165 SELECT 'x'
166 FROM xla_seg_rule_details xsr
167 WHERE xsr.value_mapping_set_code = p_mapping_set_code
168 AND xsr.amb_context_code = p_amb_context_code
169 AND xsr.value_mapping_set_code is not null
170 AND exists (SELECT 'x'
171 FROM xla_line_defn_adr_assgns xld
172 , xla_aad_line_defn_assgns xal
173 , xla_prod_acct_headers xpa
174 WHERE xsr.application_id = xld.application_id
175 AND xsr.amb_context_code = xld.amb_context_code
176 AND xsr.segment_rule_type_code = xld.segment_rule_type_code
177 AND xsr.segment_rule_code = xld.segment_rule_code
178 AND xld.application_id = xal.application_id
179 AND xld.amb_context_code = xal.amb_context_code
180 AND xld.event_class_code = xal.event_class_code
181 AND xld.event_type_code = xal.event_type_code
182 AND xld.line_definition_owner_code = xal.line_definition_owner_code
183 AND xld.line_definition_code = xal.line_definition_code
184 AND xal.application_id = xpa.application_id
185 AND xal.amb_context_code = xpa.amb_context_code
186 AND xal.product_rule_type_code = xpa.product_rule_type_code
187 AND xal.product_rule_code = xpa.product_rule_code
188 AND xal.event_class_code = xpa.event_class_code
189 AND xal.event_type_code = xpa.event_type_code
190 AND xpa.locking_status_flag = 'Y');
191
192 CURSOR c_tab_assignment_exist
193 IS
194 SELECT 'x'
195 FROM xla_seg_rule_details d
196 WHERE d.value_mapping_set_code = p_mapping_set_code
197 AND d.amb_context_code = p_amb_context_code
198 AND d.value_mapping_set_code is not null
199 AND exists (SELECT 'x'
200 FROM xla_tab_acct_defs_b a, xla_tab_acct_def_details s
201 WHERE a.application_id = s.application_id
202 AND a.amb_context_code = s.amb_context_code
203 AND a.account_definition_type_code = s.account_definition_type_code
204 AND a.account_definition_code = s.account_definition_code
205 AND s.application_id = d.application_id
206 AND s.amb_context_code = d.amb_context_code
207 AND s.segment_rule_type_code = d.segment_rule_type_code
208 AND s.segment_rule_code = d.segment_rule_code
209 AND a.locking_status_flag = 'Y');
210
211 BEGIN
212
213 xla_utility_pkg.trace('> xla_mapping_sets_pkg.mapping_set_is_locked' , 10);
214
215 xla_utility_pkg.trace('mapping_set_code = '||p_mapping_set_code , 20);
216
217 OPEN c_frozen_assignment_exist;
218 FETCH c_frozen_assignment_exist
219 INTO l_exist;
220 IF c_frozen_assignment_exist%found then
221 l_return := TRUE;
222 ELSE
223 l_return := FALSE;
224 END IF;
225 CLOSE c_frozen_assignment_exist;
226
227 IF l_return = FALSE THEN
228 OPEN c_tab_assignment_exist;
229 FETCH c_tab_assignment_exist
230 INTO l_exist;
231 IF c_tab_assignment_exist%found then
232 l_return := TRUE;
233 ELSE
234 l_return := FALSE;
235 END IF;
236 CLOSE c_tab_assignment_exist;
237 END IF;
238
239 xla_utility_pkg.trace('< xla_mapping_sets_pkg.mapping_set_is_locked' , 10);
240
241 return l_return;
242
243 EXCEPTION
244 WHEN xla_exceptions_pkg.application_exception THEN
245 IF c_frozen_assignment_exist%ISOPEN THEN
246 CLOSE c_frozen_assignment_exist;
247 END IF;
248
249 RAISE;
250 WHEN OTHERS THEN
251 IF c_frozen_assignment_exist%ISOPEN THEN
252 CLOSE c_frozen_assignment_exist;
253 END IF;
254
255 xla_exceptions_pkg.raise_message
256 (p_location => 'xla_mapping_sets_pkg.mapping_set_is_locked');
257
258 END mapping_set_is_locked;
259
260 /*======================================================================+
261 | |
262 | Public Function |
263 | |
264 | uncompile_product_rule |
265 | |
266 | Wrapper for uncompile_definitions |
267 | Provided for backward-compatibility, to be obsoleted |
268 | |
269 +======================================================================*/
270 FUNCTION uncompile_product_rule
271 (p_mapping_set_code IN VARCHAR2
272 ,p_amb_context_code IN VARCHAR2
273 ,p_product_rule_name IN OUT NOCOPY VARCHAR2
274 ,p_product_rule_type IN OUT NOCOPY VARCHAR2)
275 RETURN BOOLEAN
276 IS
277
278 l_event_class_name varchar2(80) := null;
279 l_event_type_name varchar2(80) := null;
280 l_locking_status_flag varchar2(80) := null;
281
282 l_return BOOLEAN := TRUE;
283
284 BEGIN
285
286 xla_utility_pkg.trace('> xla_mapping_sets_pkg.uncompile_product_rule' , 10);
287
288 xla_utility_pkg.trace('mapping_set_code = '||p_mapping_set_code , 20);
289
290 l_return := uncompile_definitions
291 (p_mapping_set_code => p_mapping_set_code
292 ,p_amb_context_code => p_amb_context_code
293 ,x_product_rule_name => p_product_rule_name
294 ,x_product_rule_type => p_product_rule_type
295 ,x_event_class_name => l_event_class_name
296 ,x_event_type_name => l_event_type_name
297 ,x_locking_status_flag => l_locking_status_flag);
298
299 xla_utility_pkg.trace('< xla_mapping_sets_pkg.uncompile_product_rule' , 10);
300
301 return l_return;
302
303 EXCEPTION
304 WHEN xla_exceptions_pkg.application_exception THEN
305 RAISE;
306
307 WHEN OTHERS THEN
308 xla_exceptions_pkg.raise_message
309 (p_location => 'xla_mapping_sets_pkg.uncompile_product_rule');
310
311 END uncompile_product_rule;
312
313 /*======================================================================+
314 | |
315 | Public Procedure |
316 | |
317 | uncompile_definitions |
318 | |
319 | Sets status of assigned application accounting definitions and |
320 | journal lines definitions to uncompiled |
321 | |
322 +======================================================================*/
323 FUNCTION uncompile_definitions
324 (p_mapping_set_code IN VARCHAR2
325 ,p_amb_context_code IN VARCHAR2
326 ,x_product_rule_name IN OUT NOCOPY VARCHAR2
327 ,x_product_rule_type IN OUT NOCOPY VARCHAR2
328 ,x_event_class_name IN OUT NOCOPY VARCHAR2
329 ,x_event_type_name IN OUT NOCOPY VARCHAR2
330 ,x_locking_status_flag IN OUT NOCOPY VARCHAR2)
331 RETURN BOOLEAN
332 IS
333
334 l_return BOOLEAN := TRUE;
335 l_exist VARCHAR2(1);
336
337 l_application_name varchar2(240) := null;
338 l_product_rule_name varchar2(80) := null;
339 l_product_rule_type varchar2(80) := null;
340 l_event_class_name varchar2(80) := null;
341 l_event_type_name varchar2(80) := null;
342 l_locking_status_flag varchar2(80) := null;
343
344 CURSOR c_prod_rules
345 IS
346 SELECT application_id, amb_context_code, segment_rule_type_code, segment_rule_code
347 FROM xla_seg_rule_details d
348 WHERE d.value_mapping_set_code = p_mapping_set_code
349 AND d.value_mapping_set_code is not null;
350
351 l_prod_rule c_prod_rules%rowtype;
352
353 BEGIN
354
355 xla_utility_pkg.trace('> xla_mapping_sets_pkg.uncompile_definitions' , 10);
356
357 xla_utility_pkg.trace('mapping_set_code = '||p_mapping_set_code , 20);
358
359 OPEN c_prod_rules;
360 LOOP
361 FETCH c_prod_rules
362 INTO l_prod_rule;
363 EXIT WHEN c_prod_rules%NOTFOUND or l_return=FALSE;
364
365 IF xla_seg_rules_pkg.uncompile_definitions
366 (p_application_id => l_prod_rule.application_id
367 ,p_amb_context_code => l_prod_rule.amb_context_code
368 ,p_segment_rule_type_code => l_prod_rule.segment_rule_type_code
369 ,p_segment_rule_code => l_prod_rule.segment_rule_code
370 ,x_product_rule_name => l_product_rule_name
371 ,x_product_rule_type => l_product_rule_type
372 ,x_event_class_name => l_event_class_name
373 ,x_event_type_name => l_event_type_name
374 ,x_locking_status_flag => l_locking_status_flag) THEN
375
376 l_return := TRUE;
377 ELSE
378 l_return := FALSE;
379 END IF;
380 END LOOP;
381 CLOSE c_prod_rules;
382
383 x_product_rule_name := l_product_rule_name;
384 x_product_rule_type := l_product_rule_type;
385 x_event_class_name := l_event_class_name;
386 x_event_type_name := l_event_type_name;
387 x_locking_status_flag := l_locking_status_flag;
388
389 xla_utility_pkg.trace('< xla_mapping_sets_pkg.uncompile_definitions' , 10);
390
391 return l_return;
392
393 EXCEPTION
394 WHEN xla_exceptions_pkg.application_exception THEN
395 IF c_prod_rules%ISOPEN THEN
396 CLOSE c_prod_rules;
397 END IF;
398
399 RAISE;
400 WHEN OTHERS THEN
401 IF c_prod_rules%ISOPEN THEN
402 CLOSE c_prod_rules;
403 END IF;
404
405 xla_exceptions_pkg.raise_message
406 (p_location => 'xla_mapping_sets_pkg.uncompile_definitions');
407
408 END uncompile_definitions;
409
410 /*======================================================================+
411 | |
412 | Public Function |
413 | |
414 | uncompile_tran_acct_def |
415 | |
416 | |
417 | Returns true if all the tads using the mapping set are |
418 | uncompiled |
419 | |
420 +======================================================================*/
421
422 FUNCTION uncompile_tran_acct_def
423 (p_mapping_set_code IN VARCHAR2
424 ,p_amb_context_code IN VARCHAR2
425 ,p_trx_acct_def IN OUT NOCOPY VARCHAR2
426 ,p_trx_acct_def_type IN OUT NOCOPY VARCHAR2)
427 RETURN BOOLEAN
428 IS
429
430 l_return BOOLEAN := TRUE;
431 l_exist VARCHAR2(1);
432
433 l_application_name varchar2(240) := null;
434 l_trx_acct_def varchar2(80) := null;
435 l_trx_acct_def_type varchar2(80) := null;
436
437 CURSOR c_prod_rules
438 IS
439 SELECT application_id, amb_context_code,
440 segment_rule_type_code, segment_rule_code
441 FROM xla_seg_rule_details d
442 WHERE d.value_mapping_set_code = p_mapping_set_code
443 AND d.value_mapping_set_code is not null;
444
445
446 l_prod_rule c_prod_rules%rowtype;
447
448 BEGIN
449
450 xla_utility_pkg.trace('> xla_mapping_sets_pkg.uncompile_tran_acct_def' , 10);
451
452 xla_utility_pkg.trace('mapping_set_code = '||p_mapping_set_code , 20);
453
454 OPEN c_prod_rules;
455 LOOP
456 FETCH c_prod_rules
457 INTO l_prod_rule;
458 EXIT WHEN c_prod_rules%NOTFOUND or l_return=FALSE;
459 IF xla_seg_rules_pkg.uncompile_tran_acct_def
460 (p_application_id => l_prod_rule.application_id
461 ,p_amb_context_code => l_prod_rule.amb_context_code
462 ,p_segment_rule_type_code => l_prod_rule.segment_rule_type_code
463 ,p_segment_rule_code => l_prod_rule.segment_rule_code
464 ,p_application_name => l_application_name
465 ,p_trx_acct_def => l_trx_acct_def
466 ,p_trx_acct_def_type => l_trx_acct_def_type) THEN
467
468 l_return := TRUE;
469 ELSE
470 l_return := FALSE;
471 END IF;
472 END LOOP;
473 CLOSE c_prod_rules;
474
475 p_trx_acct_def := l_trx_acct_def;
476 p_trx_acct_def_type := l_trx_acct_def_type;
477
478 xla_utility_pkg.trace('< xla_mapping_sets_pkg.uncompile_tran_acct_def' , 10);
479
480 return l_return;
481
482 EXCEPTION
483 WHEN xla_exceptions_pkg.application_exception THEN
484 IF c_prod_rules%ISOPEN THEN
485 CLOSE c_prod_rules;
486 END IF;
487
488 RAISE;
489 WHEN OTHERS THEN
490 IF c_prod_rules%ISOPEN THEN
491 CLOSE c_prod_rules;
492 END IF;
493
494 xla_exceptions_pkg.raise_message
495 (p_location => 'xla_mapping_sets_pkg.uncompile_tran_acct_def');
496
497 END uncompile_tran_acct_def;
498
499 END xla_mapping_sets_pkg;