[Home] [Help]
PACKAGE BODY: APPS.FUN_GL_ASF_EVENTS_PKG
Source
1 PACKAGE BODY FUN_GL_ASF_EVENTS_PKG AS
2 /* $Header: funglasfevntb.pls 120.0 2006/01/13 09:23:10 bsilveir noship $ */
3
4 g_debug_level NUMBER;
5 g_module CONSTANT VARCHAR2(80) := 'fun.plsql.fun_gl_asf_events_pkg';
6
7 -- This procedure is invoked from the GL Accounting Setup Flow page
8 -- when a Balancing Segment Value is removed from the Ledger
9 -- Event Name = oracle.apps.gl.Setup.Ledger.BalancingSegmentValueRemove
10 --
11 FUNCTION ledger_bsv_remove(p_subscription_guid IN RAW
12 ,p_event IN OUT NOCOPY wf_event_t
13 ) RETURN VARCHAR2
14 IS
15
16 l_routine VARCHAR2(80) := 'ledger_bsv_remove';
17 l_ledger_id NUMBER;
18 l_bsv VARCHAR2(100);
19 l_ret_mode VARCHAR2(20) := 'SUCCESS';
20
21 BEGIN
22
23 SAVEPOINT fun_ledger_bsv_remove;
24
25 -- variable p_validation_level is not used .
26 g_debug_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
27
28 l_ledger_id := wf_event.getvalueforparameter ('LEDGER_ID',p_event.parameter_list);
29 l_bsv := wf_event.getvalueforparameter ('BAL_SEGMENT_VALUE',p_event.parameter_list);
30
31 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level)
32 THEN
33 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
34 'fun.plsql.fun_gl_asf_events_pkg.ledger_bsv_remove',
35 'Begin Ledger Id ' || l_ledger_id || ', BSV ' || l_bsv);
36 END IF;
37
38 IF l_ledger_id IS NULL OR l_bsv IS NULL
39 THEN
40 wf_core.context (g_module,
41 l_routine,
42 p_event.event_name,
43 p_subscription_guid
44 );
45 wf_event.seterrorinfo (p_event, 'WARNING');
46 l_ret_mode := 'WARNING';
47
48 ELSE
49 DELETE fun_balance_accounts
50 WHERE (dr_bsv = l_bsv OR cr_bsv = l_bsv)
51 AND template_id IN (SELECT template_id
52 FROM fun_balance_options
53 WHERE ledger_id = l_ledger_id);
54
55 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level)
56 THEN
57 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
58 'fun.plsql.fun_gl_asf_events_pkg.ledger_bsv_remove',
59 ' No. of rows deleted from fun_balance_accounts is ' ||
60 SQL%ROWCOUNT);
61 END IF;
62
63
64 END IF; -- Ledger and BSV passed
65
66 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level)
67 THEN
68 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
69 'fun.plsql.fun_gl_asf_events_pkg.ledger_bsv_remove',
70 'completed');
71 END IF;
72
73 RETURN l_ret_mode;
74
75 EXCEPTION
76 WHEN OTHERS THEN
77 ROLLBACK TO fun_ledger_bsv_remove;
78 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level)
79 THEN
80 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,
81 'fun.plsql.fun_gl_asf_events_pkg.ledger_bsv_remove',
82 'Error encountered ' || SQLERRM);
83 END IF;
84 wf_core.context (g_module,
85 l_routine,
86 p_event.event_name,
87 p_subscription_guid
88 );
89 wf_event.seterrorinfo (p_event, 'ERROR');
90 RETURN 'ERROR';
91
92 END ledger_bsv_remove;
93
94 -- This procedure is invoked from the GL Accounting Setup Flow page
95 -- when a Balancing Segment Value is removed from the Legal Entity
96 -- Event Name = oracle.apps.gl.Setup.LegalEntity.BalancingSegmentValueRemove
97 --
98 FUNCTION le_bsv_remove(p_subscription_guid IN RAW
99 ,p_event IN OUT NOCOPY wf_event_t
100 ) RETURN VARCHAR2
101 IS
102
103 l_routine VARCHAR2(80) := 'le_bsv_remove';
104 l_le_id NUMBER;
105 l_bsv VARCHAR2(100);
106 l_ret_mode VARCHAR2(20) := 'SUCCESS';
107
108 BEGIN
109 SAVEPOINT fun_le_bsv_remove;
110
111 -- variable p_validation_level is not used .
112 g_debug_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
113
114 l_le_id := wf_event.getvalueforparameter ('LEGAL_ENTITY_ID',p_event.parameter_list);
115 l_bsv := wf_event.getvalueforparameter ('BAL_SEGMENT_VALUE',p_event.parameter_list);
116
117 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level)
118 THEN
119 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
120 'fun.plsql.fun_gl_asf_events_pkg.le_bsv_remove',
121 'Begin LE Id ' || l_le_id || ', BSV ' || l_bsv);
122 END IF;
123
124 IF l_le_id IS NULL OR l_bsv IS NULL
125 THEN
126 wf_core.context (g_module,
127 l_routine,
128 p_event.event_name,
129 p_subscription_guid
130 );
131 wf_event.seterrorinfo (p_event, 'WARNING');
132 l_ret_mode := 'WARNING';
133
134 ELSE
135 DELETE fun_balance_accounts
136 WHERE (dr_bsv = l_bsv OR cr_bsv = l_bsv)
137 AND template_id IN (SELECT template_id
138 FROM fun_balance_options
139 WHERE le_id = l_le_id);
140
141 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level)
142 THEN
143 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
144 'fun.plsql.fun_gl_asf_events_pkg.le_bsv_remove',
145 ' No. of rows deleted from fun_balance_accounts is ' ||
146 SQL%ROWCOUNT);
147 END IF;
148
149
150 UPDATE fun_inter_accounts
151 SET end_date = SYSDATE,
152 last_update_date = SYSDATE,
153 last_update_login = FND_GLOBAL.LOGIN_ID,
154 last_updated_by = FND_GLOBAL.USER_ID
155 WHERE (trans_bsv = l_bsv AND from_le_id = l_le_id)
156 OR (tp_bsv = l_bsv AND to_le_id = l_le_id);
157
158 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level)
159 THEN
160 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
161 'fun.plsql.fun_gl_asf_events_pkg.le_bsv_remove',
162 ' No. of rows terminated in fun_inter_accounts is ' ||
163 SQL%ROWCOUNT);
164 END IF;
165
166
167 END IF; -- Ledger and BSV passed
168
169 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level)
170 THEN
171 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
172 'fun.plsql.fun_gl_asf_events_pkg.le_bsv_remove',
173 'completed');
174 END IF;
175
176 RETURN l_ret_mode;
177
178 EXCEPTION
179 WHEN OTHERS THEN
180 ROLLBACK TO fun_le_bsv_remove;
181 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level)
182 THEN
183 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,
184 'fun.plsql.fun_gl_asf_events_pkg.le_bsv_remove',
185 'Error encountered ' || SQLERRM);
186 END IF;
187 wf_core.context (g_module,
188 l_routine,
189 p_event.event_name,
190 p_subscription_guid
191 );
192 wf_event.seterrorinfo (p_event, 'ERROR');
193 RETURN 'ERROR';
194
195
196 END le_bsv_remove;
197
198
199 -- This procedure is invoked from the GL Accounting Setup Flow page
200 -- when a Legal Entity is removed from the Ledger
201 -- Event Name = oracle.apps.gl.Setup.Ledger.LegalEntityRemove
202 --
203 FUNCTION ledger_le_remove(p_subscription_guid IN RAW
204 ,p_event IN OUT NOCOPY wf_event_t
205 ) RETURN VARCHAR2
206 IS
207
208 l_routine VARCHAR2(80) := 'ledger_le_remove';
209 l_le_id NUMBER;
210 l_ledger_id NUMBER;
211 l_ret_mode VARCHAR2(20) := 'SUCCESS';
212
213 BEGIN
214 SAVEPOINT fun_ledger_le_remove;
215
216 -- variable p_validation_level is not used .
217 g_debug_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
218
219 l_le_id := wf_event.getvalueforparameter ('LEGAL_ENTITY_ID',p_event.parameter_list);
220 l_ledger_id := wf_event.getvalueforparameter ('PRIMARY_LEDGER_ID',p_event.parameter_list);
221
222 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level)
223 THEN
224 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
225 'fun.plsql.fun_gl_asf_events_pkg.ledger_le_remove',
226 'Begin LE Id ' || l_le_id || ', Ledger Id ' || l_ledger_id);
227 END IF;
228
229 IF l_le_id IS NULL OR l_ledger_id IS NULL
230 THEN
231 wf_core.context (g_module,
232 l_routine,
233 p_event.event_name,
234 p_subscription_guid
235 );
236 wf_event.seterrorinfo (p_event, 'WARNING');
237 l_ret_mode := 'WARNING';
238
239 ELSE
240 UPDATE fun_balance_options
241 SET status_flag = 'N',
242 last_update_date = SYSDATE,
243 last_update_login = FND_GLOBAL.LOGIN_ID,
244 last_updated_by = FND_GLOBAL.USER_ID
245 WHERE le_id = l_le_id
246 AND ledger_id = l_ledger_id;
247
248 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level)
249 THEN
250 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
251 'fun.plsql.fun_gl_asf_events_pkg.ledger_le_remove',
252 ' No. of rows disabled in fun_balance_options is ' ||
253 SQL%ROWCOUNT);
254 END IF;
255
256 UPDATE fun_inter_accounts
257 SET end_date = SYSDATE,
258 last_update_date = SYSDATE,
259 last_update_login = FND_GLOBAL.LOGIN_ID,
260 last_updated_by = FND_GLOBAL.USER_ID
261 WHERE from_le_id = l_le_id
262 AND ledger_id = l_ledger_id;
263
264 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level)
265 THEN
266 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
267 'fun.plsql.fun_gl_asf_events_pkg.ledger_le_remove',
268 ' No. of rows terminated in fun_inter_accounts is ' ||
269 SQL%ROWCOUNT);
270 END IF;
271
272 END IF; -- Ledger and LE passed
273
274 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level)
275 THEN
276 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
277 'fun.plsql.fun_gl_asf_events_pkg.ledger_le_remove',
278 'completed');
279 END IF;
280
281 RETURN l_ret_mode;
282
283 EXCEPTION
284 WHEN OTHERS THEN
285 ROLLBACK TO fun_ledger_le_remove;
286 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level)
287 THEN
288 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,
289 'fun.plsql.fun_gl_asf_events_pkg.ledger_le_remove',
290 'Error encountered ' || SQLERRM);
291 END IF;
292 wf_core.context (g_module,
293 l_routine,
294 p_event.event_name,
295 p_subscription_guid
296 );
297 wf_event.seterrorinfo (p_event, 'ERROR');
298 RETURN 'ERROR';
299
300 END ledger_le_remove;
301
302
303 -- This procedure is invoked from the GL Accounting Setup Flow page
304 -- when a Secondary ledger is deleted from the ledger
305 -- Event Name = oracle.apps.gl.Setup.SecondaryLedger.Delete
306 --
310 IS
307 FUNCTION secondary_ledger_delete(p_subscription_guid IN RAW
308 ,p_event IN OUT NOCOPY wf_event_t
309 ) RETURN VARCHAR2
311
312 l_routine VARCHAR2(80) := 'secondary_ledger_delete';
313 l_sec_ledger_id NUMBER;
314 l_ret_mode VARCHAR2(20) := 'SUCCESS';
315
316 BEGIN
317 SAVEPOINT fun_secondary_ledger_delete;
318
319 -- variable p_validation_level is not used .
320 g_debug_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
321
322 l_sec_ledger_id := wf_event.getvalueforparameter ('SECONDARY_LEDGER_ID',p_event.parameter_list);
323
324 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level)
325 THEN
326 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
327 'fun.plsql.fun_gl_asf_events_pkg.secondary_ledger_delete',
328 'Begin Secondary Ledger Id ' || l_sec_ledger_id );
329 END IF;
330
331 IF l_sec_ledger_id IS NULL
332 THEN
333 wf_core.context (g_module,
334 l_routine,
335 p_event.event_name,
336 p_subscription_guid
337 );
338 wf_event.seterrorinfo (p_event, 'WARNING');
339 l_ret_mode := 'WARNING';
340
341 ELSE
342 DELETE FROM fun_balance_accounts
343 WHERE template_id in (SELECT template_id
344 FROM fun_balance_options
345 WHERE ledger_id = l_sec_ledger_id);
346
347 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level)
348 THEN
349 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
350 'fun.plsql.fun_gl_asf_events_pkg.secondary_ledger_delete',
351 ' No. of rows deleted in fun_balance_accounts is ' ||
352 SQL%ROWCOUNT);
353 END IF;
354
355 DELETE FROM fun_balance_options
356 WHERE ledger_id = l_sec_ledger_id;
357
358 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level)
359 THEN
360 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
361 'fun.plsql.fun_gl_asf_events_pkg.secondary_ledger_delete',
362 ' No. of rows deleted in fun_balance_options is ' ||
363 SQL%ROWCOUNT);
364 END IF;
365
366 DELETE FROM fun_inter_accounts
367 WHERE ledger_id = l_sec_ledger_id;
368
369 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level)
370 THEN
371 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
372 'fun.plsql.fun_gl_asf_events_pkg.secondary_ledger_delete',
373 ' No. of rows deleted in fun_inter_accounts is ' ||
374 SQL%ROWCOUNT);
375 END IF;
376
377
378 END IF; -- Ledger passed
379
380 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level)
381 THEN
382 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
383 'fun.plsql.fun_gl_asf_events_pkg.secondary_ledger_delete',
384 'completed');
385 END IF;
386
387 RETURN l_ret_mode;
388
389 EXCEPTION
390 WHEN OTHERS THEN
391 ROLLBACK TO fun_secondary_ledger_delete;
392 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level)
393 THEN
394 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,
395 'fun.plsql.fun_gl_asf_events_pkg.secondary_ledger_delete',
396 'Error encountered ' || SQLERRM);
397 END IF;
398 wf_core.context (g_module,
399 l_routine,
400 p_event.event_name,
401 p_subscription_guid
402 );
403 wf_event.seterrorinfo (p_event, 'ERROR');
404 RETURN 'ERROR';
405
406 END secondary_ledger_delete;
407
408
409 -- This procedure is invoked from the GL Accounting Setup Flow page
410 -- when a Reporting Ledger is deleted from the Ledger
411 -- Event Name = oracle.apps.gl.Setup.ReportingLedger.Delete
412 --
413 FUNCTION reporting_ledger_delete(p_subscription_guid IN RAW
414 ,p_event IN OUT NOCOPY wf_event_t
415 ) RETURN VARCHAR2
416 IS
417
418 l_routine VARCHAR2(80) := 'reporting_ledger_delete';
419 l_rep_ledger_id NUMBER;
420 l_ret_mode VARCHAR2(20) := 'SUCCESS';
421
422 BEGIN
423 SAVEPOINT fun_reporting_ledger_delete;
424
425 -- variable p_validation_level is not used .
426 g_debug_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
427
428 l_rep_ledger_id := wf_event.getvalueforparameter ('TARGET_LEDGER_ID',p_event.parameter_list);
429
430 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level)
431 THEN
432 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
433 'fun.plsql.fun_gl_asf_events_pkg.reporting_ledger_delete',
434 'Begin Reporting Ledger Id ' || l_rep_ledger_id );
435 END IF;
436
437 IF l_rep_ledger_id IS NULL
438 THEN
439 wf_core.context (g_module,
440 l_routine,
441 p_event.event_name,
442 p_subscription_guid
443 );
444 wf_event.seterrorinfo (p_event, 'WARNING');
445 l_ret_mode := 'WARNING';
446
447 ELSE
448 DELETE FROM fun_balance_accounts
449 WHERE template_id in (SELECT template_id
450 FROM fun_balance_options
451 WHERE ledger_id = l_rep_ledger_id);
452
453 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level)
454 THEN
455 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
456 'fun.plsql.fun_gl_asf_events_pkg.reporting_ledger_delete',
457 ' No. of rows deleted in fun_balance_accounts is ' ||
458 SQL%ROWCOUNT);
459 END IF;
460
461 DELETE FROM fun_balance_options
462 WHERE ledger_id = l_rep_ledger_id;
463
464 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level)
465 THEN
466 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
467 'fun.plsql.fun_gl_asf_events_pkg.reporting_ledger_delete',
468 ' No. of rows deleted in fun_balance_options is ' ||
469 SQL%ROWCOUNT);
470 END IF;
471
472 DELETE FROM fun_inter_accounts
473 WHERE ledger_id = l_rep_ledger_id;
474
475 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level)
476 THEN
477 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
478 'fun.plsql.fun_gl_asf_events_pkg.reporting_ledger_delete',
479 ' No. of rows deleted in fun_inter_accounts is ' ||
480 SQL%ROWCOUNT);
481 END IF;
482
483
484 END IF; -- Ledger passed
485
486 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level)
487 THEN
488 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
489 'fun.plsql.fun_gl_asf_events_pkg.reporting_ledger_delete',
490 'completed');
491 END IF;
492
493 RETURN l_ret_mode;
494
495 EXCEPTION
496 WHEN OTHERS THEN
497 ROLLBACK TO fun_reporting_ledger_delete;
498 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level)
499 THEN
500 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,
501 'fun.plsql.fun_gl_asf_events_pkg.reporting_ledger_delete',
502 'Error encountered ' || SQLERRM);
503 END IF;
504 wf_core.context (g_module,
505 l_routine,
506 p_event.event_name,
507 p_subscription_guid
508 );
509 wf_event.seterrorinfo (p_event, 'ERROR');
510 RETURN 'ERROR';
511
512 END reporting_ledger_delete;
513 END;
514