[Home] [Help]
PACKAGE BODY: APPS.XLA_ANALYTICAL_HDRS_PKG
Source
1 PACKAGE BODY xla_analytical_hdrs_pkg AS
2 /* $Header: xlaamanc.pkb 120.8 2005/04/28 18:42:31 masada ship $ */
3 /*======================================================================+
4 | Copyright (c) 1995-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +=======================================================================+
8 | PACKAGE NAME |
9 | xla_analytical_hdrs_pkg |
10 | |
11 | DESCRIPTION |
12 | XLA Analytical Criteria Package |
13 | |
14 | HISTORY |
15 | 01-May-01 Dimple Shah Created |
16 | 22-Oct-04 Wynne Chan Changes for Journal Lines Definition |
17 | |
18 +======================================================================*/
19
20 TYPE t_array_codes IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
21 TYPE t_array_type_codes IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
22 TYPE t_array_int IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
23
24
25 --=============================================================================
26 -- *********** Local Trace Routine **********
27 --=============================================================================
28 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
29 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
30 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
31 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
32 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
33 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
34
35 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
36 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_analytical_hdrs_pkg';
37
38 g_log_level NUMBER;
39 g_log_enabled BOOLEAN;
40
41 PROCEDURE trace
42 (p_msg IN VARCHAR2
43 ,p_module IN VARCHAR2
44 ,p_level IN NUMBER) IS
45 BEGIN
46 ----------------------------------------------------------------------------
47 -- Following is for FND log.
48 ----------------------------------------------------------------------------
49 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
50 fnd_log.message(p_level, p_module);
51 ELSIF p_level >= g_log_level THEN
52 fnd_log.string(p_level, p_module, p_msg);
53 END IF;
54
55 EXCEPTION
56 WHEN xla_exceptions_pkg.application_exception THEN
57 RAISE;
58
59 WHEN OTHERS THEN
60 xla_exceptions_pkg.raise_message
61 (p_location => 'xla_analytical_hdrs_pkg.trace');
62 END trace;
63
64
65 /*======================================================================+
66 | |
67 | Public Function |
68 | |
69 | uncompile_definitions |
70 | |
71 | Returns true if all the product rules are uncompiled for this |
72 | analytical criteria |
73 | |
74 +======================================================================*/
75
76 FUNCTION uncompile_definitions
77 (p_amb_context_code IN VARCHAR2
78 ,p_analytical_criterion_code IN VARCHAR2
79 ,p_anal_criterion_type_code IN VARCHAR2
80 ,x_product_rule_name IN OUT NOCOPY VARCHAR2
81 ,x_product_rule_type IN OUT NOCOPY VARCHAR2
82 ,x_event_class_name IN OUT NOCOPY VARCHAR2
83 ,x_event_type_name IN OUT NOCOPY VARCHAR2
84 ,x_locking_status_flag IN OUT NOCOPY VARCHAR2)
85 RETURN BOOLEAN
86 IS
87
88 l_return BOOLEAN := TRUE;
89
90 l_application_name varchar2(240) := null;
91 l_product_rule_name varchar2(80) := null;
92 l_product_rule_type varchar2(80) := null;
93 l_event_class_name varchar2(80) := null;
94 l_event_type_name varchar2(80) := null;
95 l_locking_status_flag varchar2(1) := null;
96
97 CURSOR c_lock_line_aads IS
98 SELECT xpa.application_id
99 , xpa.entity_code
100 , xpa.event_class_code
101 , xpa.event_type_code
102 , xpa.product_rule_type_code
103 , xpa.product_rule_code
104 , xpa.locking_status_flag
105 , xpa.validation_status_code
106 FROM xla_line_defn_ac_assgns xld
107 ,xla_aad_line_defn_assgns xal
108 ,xla_prod_acct_headers xpa
109 WHERE xpa.application_id = xal.application_id
110 AND xpa.amb_context_code = xal.amb_context_code
111 AND xpa.product_rule_type_code = xal.product_rule_type_code
112 AND xpa.product_rule_code = xal.product_rule_code
113 AND xpa.event_class_code = xal.event_class_code
114 AND xpa.event_type_code = xal.event_type_code
115 AND xal.application_id = xld.application_id
116 AND xal.amb_context_code = xld.amb_context_code
117 AND xal.event_class_code = xld.event_class_code
118 AND xal.event_type_code = xld.event_type_code
119 AND xal.line_definition_owner_code = xld.line_definition_owner_code
120 AND xal.line_definition_code = xld.line_definition_code
121 AND xld.amb_context_code = p_amb_context_code
122 AND xld.analytical_criterion_type_code = p_anal_criterion_type_code
123 AND xld.analytical_criterion_code = p_analytical_criterion_code
124 FOR UPDATE NOWAIT;
125
126 CURSOR c_lock_header_aads IS
127 SELECT xpa.application_id
128 , xpa.entity_code
129 , xpa.event_class_code
130 , xpa.event_type_code
131 , xpa.product_rule_type_code
132 , xpa.product_rule_code
133 , xpa.locking_status_flag
134 , xpa.validation_status_code
135 FROM xla_aad_header_ac_assgns xah
136 ,xla_prod_acct_headers xpa
137 WHERE xpa.application_id = xah.application_id
138 AND xpa.amb_context_code = xah.amb_context_code
139 AND xpa.product_rule_type_code = xah.product_rule_type_code
140 AND xpa.product_rule_code = xah.product_rule_code
141 AND xpa.event_class_code = xah.event_class_code
142 AND xpa.event_type_code = xah.event_type_code
143 AND xah.amb_context_code = p_amb_context_code
144 AND xah.analytical_criterion_type_code = p_anal_criterion_type_code
145 AND xah.analytical_criterion_code = p_analytical_criterion_code
146 FOR UPDATE NOWAIT;
147
148 CURSOR c_update_aads IS
149 SELECT xpa.application_id, xpa.event_class_code,
150 xpa.product_rule_type_code, xpa.product_rule_code
151 FROM xla_aad_header_ac_assgns xah
152 ,xla_prod_acct_headers xpa
153 WHERE xpa.application_id = xah.application_id
154 AND xpa.amb_context_code = xah.amb_context_code
155 AND xpa.product_rule_type_code = xah.product_rule_type_code
156 AND xpa.product_rule_code = xah.product_rule_code
157 AND xpa.event_class_code = xah.event_class_code
158 AND xpa.event_type_code = xah.event_type_code
159 AND xah.amb_context_code = p_amb_context_code
160 AND xah.analytical_criterion_type_code = p_anal_criterion_type_code
161 AND xah.analytical_criterion_code = p_analytical_criterion_code
162 UNION
163 SELECT xpa.application_id, xpa.event_class_code,
164 xpa.product_rule_type_code, xpa.product_rule_code
165 FROM xla_prod_acct_headers xpa
166 ,xla_aad_line_defn_assgns xal
167 ,xla_line_defn_ac_assgns xac
168 WHERE xpa.application_id = xal.application_id
169 AND xpa.amb_context_code = xal.amb_context_code
170 AND xpa.product_rule_type_code = xal.product_rule_type_code
171 AND xpa.product_rule_code = xal.product_rule_code
172 AND xpa.event_class_code = xal.event_class_code
173 AND xpa.event_type_code = xal.event_type_code
174 AND xal.application_id = xac.application_id
175 AND xal.amb_context_code = xac.amb_context_code
176 AND xal.event_class_code = xac.event_class_code
177 AND xal.event_type_code = xac.event_type_code
178 AND xal.line_definition_owner_code = xac.line_definition_owner_code
179 AND xal.line_definition_code = xac.line_definition_code
180 AND xac.amb_context_code = p_amb_context_code
181 AND xac.analytical_criterion_type_code = p_anal_criterion_type_code
182 AND xac.analytical_criterion_code = p_analytical_criterion_code;
183
184 l_locked_application_id INTEGER;
185 l_locked_entity_code VARCHAR2(30);
186 l_locked_event_class_code VARCHAR2(30);
187 l_locked_event_type_code VARCHAR2(30);
188 l_locked_aad_type_code VARCHAR2(30);
189 l_locked_aad_code VARCHAR2(30);
190
191 l_application_ids t_array_int;
192 l_event_class_codes t_array_codes;
193 l_product_rule_type_codes t_array_type_codes;
194 l_product_rule_codes t_array_codes;
195
196 l_log_module VARCHAR2(240);
197 BEGIN
198 IF g_log_enabled THEN
199 l_log_module := C_DEFAULT_MODULE||'.uncompile_definitions';
200 END IF;
201
202 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
203 trace(p_msg => 'BEGIN of procedure uncompile_definitions'
204 ,p_module => l_log_module
205 ,p_level => C_LEVEL_PROCEDURE);
206 END IF;
207
208 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
209 trace(p_msg => 'amb_context_code = '||p_amb_context_code||
210 ',analytical_criterion_type_code = '||p_anal_criterion_type_code||
211 ',analytical_criterion_code = '||p_analytical_criterion_code
212 ,p_module => l_log_module
213 ,p_level => C_LEVEL_STATEMENT);
214 END IF;
215
216 l_return := TRUE;
217
218 FOR l_lock_aad IN c_lock_header_aads LOOP
219 IF (l_lock_aad.validation_status_code NOT IN ('E', 'Y', 'N') OR
220 l_lock_aad.locking_status_flag = 'Y') THEN
221
222 l_locked_application_id := l_lock_aad.application_id;
223 l_locked_entity_code := l_lock_aad.entity_code;
224 l_locked_event_class_code := l_lock_aad.event_class_code;
225 l_locked_event_type_code := l_lock_aad.event_type_code;
226 l_locked_aad_type_code := l_lock_aad.product_rule_type_code;
227 l_locked_aad_code := l_lock_aad.product_rule_code;
228 l_locking_status_flag := l_lock_aad.locking_status_flag;
229
230 l_return := FALSE;
231 EXIT;
232 END IF;
233 END LOOP;
234
235 IF (l_return) THEN
236 FOR l_lock_aad IN c_lock_line_aads LOOP
237 IF (l_lock_aad.validation_status_code NOT IN ('E', 'Y', 'N') OR
238 l_lock_aad.locking_status_flag = 'Y') THEN
239
240 l_locked_application_id := l_lock_aad.application_id;
241 l_locked_entity_code := l_lock_aad.entity_code;
242 l_locked_event_class_code := l_lock_aad.event_class_code;
243 l_locked_event_type_code := l_lock_aad.event_type_code;
244 l_locked_aad_type_code := l_lock_aad.product_rule_type_code;
248 l_return := FALSE;
245 l_locked_aad_code := l_lock_aad.product_rule_code;
246 l_locking_status_flag := l_lock_aad.locking_status_flag;
247
249 EXIT;
250 END IF;
251 END LOOP;
252 END IF;
253
254 IF (NOT l_return) THEN
255
256 xla_validations_pkg.get_product_rule_info
257 (p_application_id => l_locked_application_id
258 ,p_amb_context_code => p_amb_context_code
259 ,p_product_rule_type_code => l_locked_aad_type_code
260 ,p_product_rule_code => l_locked_aad_code
261 ,p_application_name => l_application_name
262 ,p_product_rule_name => l_product_rule_name
263 ,p_product_rule_type => l_product_rule_type);
264
265 xla_validations_pkg.get_event_class_info
266 (p_application_id => l_locked_application_id
267 ,p_entity_code => l_locked_entity_code
268 ,p_event_class_code => l_locked_event_class_code
269 ,p_event_class_name => l_event_class_name);
270
271 xla_validations_pkg.get_event_type_info
272 (p_application_id => l_locked_application_id
273 ,p_entity_code => l_locked_entity_code
274 ,p_event_class_code => l_locked_event_class_code
275 ,p_event_type_code => l_locked_event_type_code
276 ,p_event_type_name => l_event_type_name);
277
278 ELSE
279
280 UPDATE xla_line_definitions_b xld
281 SET validation_status_code = 'N'
282 , last_update_date = sysdate
283 , last_updated_by = xla_environment_pkg.g_usr_id
284 , last_update_login = xla_environment_pkg.g_login_id
285 WHERE xld.amb_context_code = p_amb_context_code
286 AND xld.validation_status_code <> 'N'
287 AND EXISTS
288 (SELECT 'X'
289 FROM xla_line_defn_ac_assgns xac
290 WHERE xac.amb_context_code = p_amb_context_code
291 AND xac.analytical_criterion_type_code = p_anal_criterion_type_code
292 AND xac.analytical_criterion_code = p_analytical_criterion_code
293 AND xac.application_id = xld.application_id
294 AND xac.event_class_code = xld.event_class_code
295 AND xac.event_type_code = xld.event_type_code
296 AND xac.line_definition_owner_code = xld.line_definition_owner_code
297 AND xac.line_definition_code = xld.line_definition_code);
298
299 OPEN c_update_aads;
300 FETCH c_update_aads BULK COLLECT INTO l_application_ids
301 ,l_event_class_codes
302 ,l_product_rule_type_codes
303 ,l_product_rule_codes;
304 CLOSE c_update_aads;
305
306 IF (C_LEVEL_EVENT >= g_log_level) THEN
307 trace(p_msg => 'l_event_class_codes.count = '||l_event_class_codes.count,
308 p_module => l_log_module,
309 p_level => C_LEVEL_EVENT);
310 END IF;
311
312 IF (l_event_class_codes.count > 0) THEN
313
314 FORALL i IN 1..l_event_class_codes.LAST
315 UPDATE xla_product_rules_b
316 SET compile_status_code = 'N'
317 , updated_flag = 'Y'
318 , last_update_date = sysdate
319 , last_updated_by = xla_environment_pkg.g_usr_id
320 , last_update_login = xla_environment_pkg.g_login_id
321 WHERE application_id = l_application_ids(i)
322 AND amb_context_code = p_amb_context_code
323 AND product_rule_type_code = l_product_rule_type_codes(i)
324 AND product_rule_code = l_product_rule_codes(i)
325 AND (compile_status_code <> 'N' OR
326 updated_flag <> 'Y');
327
328 FORALL i IN 1..l_event_class_codes.LAST
329 UPDATE xla_prod_acct_headers
330 SET validation_status_code = 'N'
331 , last_update_date = sysdate
332 , last_updated_by = xla_environment_pkg.g_usr_id
333 , last_update_login = xla_environment_pkg.g_login_id
334 WHERE application_id = l_application_ids(i)
335 AND amb_context_code = p_amb_context_code
336 AND event_class_code = l_event_class_codes(i)
337 AND product_rule_type_code = l_product_rule_type_codes(i)
338 AND product_rule_code = l_product_rule_codes(i)
339 AND validation_status_code <> 'N';
340
341 FORALL i IN 1..l_application_ids.LAST
342 UPDATE xla_appli_amb_contexts
343 SET updated_flag = 'Y'
344 , last_update_date = sysdate
345 , last_updated_by = xla_environment_pkg.g_usr_id
346 , last_update_login = xla_environment_pkg.g_login_id
347 WHERE application_id = l_application_ids(i)
348 AND amb_context_code = p_amb_context_code
349 AND updated_flag <> 'Y';
350
351 END IF;
352 l_return := TRUE;
353 END IF;
354
355 x_product_rule_name := l_product_rule_name;
356 x_product_rule_type := l_product_rule_type;
357 x_event_class_name := l_event_class_name;
358 x_event_type_name := l_event_type_name;
359 x_locking_status_flag := l_locking_status_flag;
360
361 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
362 trace(p_msg => 'END of procedure uncompile_definitions'
363 ,p_module => l_log_module
364 ,p_level => C_LEVEL_PROCEDURE);
365 END IF;
366
367 RETURN l_return;
368
369 EXCEPTION
373 END IF;
370 WHEN xla_exceptions_pkg.application_exception THEN
371 IF c_update_aads%ISOPEN THEN
372 CLOSE c_update_aads;
374 IF c_lock_line_aads%ISOPEN THEN
375 CLOSE c_lock_line_aads;
376 END IF;
377 IF c_lock_header_aads%ISOPEN THEN
378 CLOSE c_lock_header_aads;
379 END IF;
380
381 RAISE;
382 WHEN OTHERS THEN
383 IF c_update_aads%ISOPEN THEN
384 CLOSE c_update_aads;
385 END IF;
386 IF c_lock_line_aads%ISOPEN THEN
387 CLOSE c_lock_line_aads;
388 END IF;
389 IF c_lock_header_aads%ISOPEN THEN
390 CLOSE c_lock_header_aads;
391 END IF;
392
393 xla_exceptions_pkg.raise_message
394 (p_location => 'xla_analytical_hdrs_pkg.uncompile_definitions');
395
396 END uncompile_definitions;
397
398 --=============================================================================
399 --
400 -- Following code is executed when the package body is referenced for the first
401 -- time
402 --
403 --=============================================================================
404 BEGIN
405 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
406 g_log_enabled := fnd_log.test
407 (log_level => g_log_level
408 ,module => C_DEFAULT_MODULE);
409
410 IF NOT g_log_enabled THEN
411 g_log_level := C_LEVEL_LOG_DISABLED;
412 END IF;
413
414 END xla_analytical_hdrs_pkg;