[Home] [Help]
PACKAGE BODY: APPS.PSA_UTILS
Source
1 PACKAGE BODY PSA_UTILS as
2 /* $Header: PSAUTILB.pls 120.20 2010/12/10 18:32:37 sasukuma ship $ */
3
4 g_state_level NUMBER;
5 g_proc_level NUMBER;
6 g_event_level NUMBER;
7 g_excep_level NUMBER;
8 g_error_level NUMBER;
9 g_unexp_level NUMBER;
10 g_debug_level NUMBER;
11 g_path_name VARCHAR2(100);
12 g_FAILURE NUMBER;
13 g_SUCCESS NUMBER;
14 g_WARNING NUMBER;
15 g_request_id NUMBER;
16 g_user_id NUMBER;
17 g_login_id NUMBER;
18 g_resp_id NUMBER;
19 g_audsid NUMBER;
20
21 TYPE events_rec IS RECORD
22 (
23 entity_id xla_transaction_entities.entity_id%TYPE,
24 event_id xla_events.event_id%TYPE,
25 application_id xla_transaction_entities.application_id%TYPE,
26 event_type_code xla_events.event_type_code%TYPE,
27 entity_code xla_transaction_entities.entity_code%TYPE,
28 budgetary_flag xla_events.budgetary_control_flag%TYPE,
29 event_status_code xla_events.event_status_code%TYPE,
30 gl_transfer_flag xla_ae_headers.gl_transfer_status_code%TYPE,
31 gl_date DATE,
32 ae_header_id xla_ae_headers.ae_header_id%TYPE,
33 ledger_id xla_transaction_entities.ledger_id%TYPE,
34 transaction_number xla_transaction_entities.transaction_number%TYPE,
35 source_id_int_1 xla_transaction_entities.source_id_int_1%TYPE,
36 source_id_int_2 xla_transaction_entities.source_id_int_2%TYPE,
37 source_id_int_3 xla_transaction_entities.source_id_int_3%TYPE,
38 source_id_int_4 xla_transaction_entities.source_id_int_4%TYPE,
39 source_id_char_1 xla_transaction_entities.source_id_char_1%TYPE,
40 source_id_char_2 xla_transaction_entities.source_id_char_2%TYPE,
41 source_id_char_3 xla_transaction_entities.source_id_char_3%TYPE,
42 source_id_char_4 xla_transaction_entities.source_id_char_4%TYPE,
43 legal_entity_id xla_transaction_entities.legal_entity_id%TYPE
44 );
45
46 TYPE events_tab IS TABLE OF events_rec INDEX BY BINARY_INTEGER;
47
48 -- This procedure is stubbed out because this has been replaced with fnd logging procedures
49 -- The signature is still kept because some AR packages are accessing it
50
51 PROCEDURE init
52 IS
53 l_path_name VARCHAR2(500);
54 l_file_info VARCHAR2(2000);
55 BEGIN
56 g_path_name := 'psa.plsql.psautilb.psa_utils';
57 g_debug_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
58 g_state_level := FND_LOG.LEVEL_STATEMENT;
59 g_proc_level := FND_LOG.LEVEL_PROCEDURE;
60 g_event_level := FND_LOG.LEVEL_EVENT;
61 g_excep_level := FND_LOG.LEVEL_EXCEPTION;
62 g_error_level := FND_LOG.LEVEL_ERROR;
63 g_unexp_level := FND_LOG.LEVEL_UNEXPECTED;
64 l_path_name := g_path_name || '.init';
65 g_FAILURE := -1;
66 g_SUCCESS := 0;
67 g_WARNING := -2;
68 g_request_id := fnd_global.conc_request_id;
69 g_user_id := fnd_global.user_id;
70 g_login_id := fnd_global.login_id;
71 g_resp_id := fnd_global.resp_id;
72 g_audsid := userenv ('SESSIONID');
73
74 l_file_info :=
75 '$Header: PSAUTILB.pls 120.20 2010/12/10 18:32:37 sasukuma ship $';
76 debug_other_string(g_state_level,l_path_name, 'PSA_UTILS version = '||l_file_info);
77 END;
78
79 PROCEDURE log
80 (
81 p_path_name IN VARCHAR2,
82 p_message IN VARCHAR2
83 )
84 IS
85 BEGIN
86 debug_other_string(g_state_level,p_path_name, p_message);
87 fnd_file.put_line (fnd_file.log, p_message);
88 END;
89
90 PROCEDURE output
91 (
92 p_message IN VARCHAR2
93 )
94 IS
95 l_path_name VARCHAR2(500) := g_path_name || '.OUTPUT';
96 BEGIN
97 IF (g_request_id = -1) THEN
98 debug_other_string(g_state_level,l_path_name, p_message);
99 ELSE
100 fnd_file.put_line (fnd_file.output, p_message);
101 END IF;
102 END;
103
104 PROCEDURE error
105 (
106 p_message IN VARCHAR2
107 )
108 IS
109 l_path_name VARCHAR2(500) := g_path_name || '.output';
110 BEGIN
111 debug_other_string(g_state_level,l_path_name, p_message);
112 IF (g_request_id = -1) THEN
113 dbms_output.put_line (p_message);
114 ELSE
115 fnd_file.put_line (fnd_file.output, p_message);
116 END IF;
117 END;
118
119 PROCEDURE undo_error
120 (
121 p_message IN VARCHAR2
122 )
123 IS
124 l_buffer VARCHAR2(2000);
125 BEGIN
126 l_buffer := '********** ERROR: '||p_message||' **********';
127 error (RPAD('*', length (l_buffer), '*'));
128 error (l_buffer);
129 error (RPAD('*', length (l_buffer), '*'));
130 END;
131
132 PROCEDURE debug_mesg ( p_msg IN VARCHAR2 )
133 IS
134 BEGIN
135 null;
136 END debug_mesg;
137
138 /* ====================== DEBUG_UNEXPECTED_MSG ===================== */
139
140 PROCEDURE debug_unexpected_msg
141 (
142 p_full_path IN VARCHAR2
143 )
144 IS
145 BEGIN
146 fnd_message.set_name('PSA','PSA_LOGGING_USER_ERROR');
147 fnd_file.put_line(FND_FILE.LOG,FND_MESSAGE.GET);
148 IF (g_unexp_level >= g_debug_level) THEN
149 fnd_message.set_name('PSA','PSA_LOGGING_UNEXP_ERROR');
150 fnd_message.set_token('CODE',SQLCODE);
151 fnd_message.set_token('MSG', SQLERRM);
152 fnd_log.message (g_unexp_level,p_full_path, TRUE);
153 END IF;
154 END debug_unexpected_msg;
155
156 /* ===================== DEBUG_OTHER_MSG ============================ */
157
158 PROCEDURE debug_other_msg
159 (
160 p_level IN NUMBER,
161 p_full_path IN VARCHAR2,
162 p_remove_from_stack IN BOOLEAN
163 ) IS
164 BEGIN
165 IF (p_level >= g_debug_level) THEN
166 fnd_log.message(p_level,p_full_path,p_remove_from_stack);
167 END IF;
168 END debug_other_msg;
169
170 /* ==================== DEBUG_OTHER_STRING ========================= */
171
172 PROCEDURE debug_other_string
173 (
174 p_level IN NUMBER,
175 p_full_path IN VARCHAR2,
176 p_string IN VARCHAR2
177 )
178 IS
179 BEGIN
180 IF (p_level >= g_debug_level) THEN
181 fnd_log.string(p_level,p_full_path,p_string);
182 END IF;
183 END debug_other_string;
184
185 -- procedure to fetch RFE ccid for passed ccid
186 Procedure PSA_Get_RFE_ccid
187 (
188 p_ccid IN NUMBER,
189 rfe_ccid OUT NOCOPY NUMBER
190 )
191 AS
192 l_num number;
193 l_segarray_rfe fnd_flex_ext.segmentarray;
194 l_segarray_ccid fnd_flex_ext.segmentarray;
195 l_seg_rfe varchar2(2000);
196 l_seg_ccid varchar2(2000);
197 l_path_name VARCHAR2(500);
198 begin
199 l_path_name := g_path_name || '.PSA_Get_RFE_ccid';
200
201 select concatenated_segments
202 into l_seg_rfe
203 from gl_code_combinations_kfv
204 where code_combination_id = 1002;
205
206 debug_other_string(g_state_level,l_path_name, 'GET_RFE_CCID: RFE seg ' || l_seg_rfe);
207
208 l_num := fnd_flex_ext.breakup_segments
209 (
210 l_seg_rfe
211 ,'.'
212 ,l_segarray_rfe
213 );
214
215 debug_other_string(g_state_level,l_path_name, 'GET_RFE_CCID: Parameter received: ' || p_ccid);
216
217 select concatenated_segments
218 into l_seg_ccid
219 from gl_code_combinations_kfv
220 where code_combination_id = p_ccid; --24350;
221
222 debug_other_string(g_state_level,l_path_name, 'GET_RFE_CCID: CCID seg ' || l_seg_ccid);
223
224 l_num := fnd_flex_ext.breakup_segments
225 (
226 l_seg_ccid
227 ,'.'
228 ,l_segarray_ccid
229 );
230
231 l_segarray_rfe(1) := l_segarray_ccid(1);
232
233 l_num := fnd_flex_ext.breakup_segments
234 (
235 l_seg_ccid
236 ,'.'
237 ,l_segarray_ccid
238 );
239
240
241 select code_combination_id
242 into rfe_ccid
243 from gl_code_combinations_kfv
244 where segment1 = l_segarray_rfe(1)
245 and segment2 = l_segarray_rfe(2)
246 and segment3 = l_segarray_rfe(3)
247 and segment4 = l_segarray_rfe(4)
248 and segment5 = l_segarray_rfe(5)
249 and segment6 = l_segarray_rfe(6)
250 and segment7 = l_segarray_rfe(7);
251
252 debug_other_string(g_state_level,l_path_name, 'GET_RFE_CCID:Returning RFE ccid ' || rfe_ccid);
253
254 END psa_get_rfe_ccid;
255
256 PROCEDURE backup_tables
257 (
258 p_errbuf OUT NOCOPY VARCHAR2,
259 p_retcode OUT NOCOPY VARCHAR2,
260 p_audit_id IN NUMBER,
261 p_entity_rec IN xla_transaction_entities%ROWTYPE
262 )
263 IS
264 l_path_name VARCHAR2(500);
265 l_string VARCHAR2(2000);
266 l_backup_string VARCHAR2(50);
267 BEGIN
268 p_retcode := g_SUCCESS;
269 p_errbuf := NULL;
270 l_path_name := g_path_name || '.end_xla_audit';
271
272 l_backup_string := LPAD(TO_CHAR (p_audit_id), 10, '0');
273 IF (p_entity_rec.application_id = 200 AND p_entity_rec.entity_code = 'AP_INVOICES') THEN
274 IF (p_retcode = g_SUCCESS) THEN
275 BEGIN
276 l_string := 'CREATE TABLE PSA_BKP_AP_INV_HDRS_'||l_backup_string||' AS
277 SELECT * FROM AP_INVOICES_ALL WHERE INVOICE_ID = '||p_entity_rec.source_id_int_1;
278 EXECUTE IMMEDIATE l_string;
279 EXCEPTION
280 WHEN OTHERS THEN
281 log(l_path_name, 'EXCEPTION: '|| SQLERRM(sqlcode));
282 p_retcode := g_FAILURE;
283 END;
284 END IF;
285
286 IF (p_retcode = g_SUCCESS) THEN
287 BEGIN
288 l_string := 'CREATE TABLE PSA_BKP_AP_INV_DIST_'||l_backup_string||' AS
289 SELECT * FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE INVOICE_ID = '||p_entity_rec.source_id_int_1;
290 EXECUTE IMMEDIATE l_string;
291 EXCEPTION
292 WHEN OTHERS THEN
293 log(l_path_name, 'EXCEPTION: '|| SQLERRM(sqlcode));
294 p_retcode := g_FAILURE;
295 END;
296 END IF;
297
298 IF (p_retcode = g_SUCCESS) THEN
299 BEGIN
300 l_string := 'CREATE TABLE PSA_BKP_GMS_DISTS_'||l_backup_string||' AS
301 SELECT * FROM GMS_AWARD_DISTRIBUTIONS WHERE INVOICE_DISTRIBUTION_ID IN (
302 SELECT INVOICE_DISTRIBUTION_ID
303 FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE INVOICE_ID = '||p_entity_rec.source_id_int_1||
304 ')';
305 EXECUTE IMMEDIATE l_string;
306 EXCEPTION
307 WHEN OTHERS THEN
308 log(l_path_name, 'EXCEPTION: '|| SQLERRM(sqlcode));
309 p_retcode := g_FAILURE;
310 END;
311 END IF;
312
313 END IF;
314
315 IF (p_retcode = g_SUCCESS) THEN
316 BEGIN
317 l_string := 'CREATE TABLE PSA_BKP_XLA_EVENTS_'||l_backup_string||' AS
318 SELECT * FROM XLA_EVENTS WHERE ENTITY_ID = '||p_entity_rec.entity_id;
319 EXECUTE IMMEDIATE l_string;
320 EXCEPTION
321 WHEN OTHERS THEN
322 log(l_path_name, 'EXCEPTION: '|| SQLERRM(sqlcode));
323 p_retcode := g_FAILURE;
324 END;
325 END IF;
326
327 IF (p_retcode = g_SUCCESS) THEN
328 BEGIN
329 l_string := 'CREATE TABLE PSA_BKP_XLA_HDRS_'||l_backup_string||' AS
330 SELECT * FROM XLA_AE_HEADERS WHERE EVENT_ID IN (
331 SELECT EVENT_ID FROM XLA_EVENTS WHERE ENTITY_ID = '||p_entity_rec.entity_id||
332 ')';
333 EXECUTE IMMEDIATE l_string;
334 EXCEPTION
335 WHEN OTHERS THEN
336 log(l_path_name, 'EXCEPTION: '|| SQLERRM(sqlcode));
337 p_retcode := g_FAILURE;
338 END;
339 END IF;
340
341 IF (p_retcode = g_SUCCESS) THEN
342 BEGIN
343 l_string := 'CREATE TABLE PSA_BKP_XLA_LINES_'||l_backup_string||' AS
344 SELECT * FROM XLA_AE_LINES WHERE AE_HEADER_ID IN (
345 SELECT AE_HEADER_ID FROM XLA_AE_HEADERS WHERE EVENT_ID IN (
346 SELECT EVENT_ID FROM XLA_EVENTS WHERE ENTITY_ID = '||p_entity_rec.entity_id||
347 '))';
348 EXECUTE IMMEDIATE l_string;
349 EXCEPTION
350 WHEN OTHERS THEN
351 log(l_path_name, 'EXCEPTION: '|| SQLERRM(sqlcode));
352 p_retcode := g_FAILURE;
353 END;
354 END IF;
355
356 IF (p_retcode = g_SUCCESS) THEN
357 BEGIN
358 l_string := 'CREATE TABLE PSA_BKP_XLA_DIST_'||l_backup_string||' AS
359 SELECT * FROM XLA_DISTRIBUTION_LINKS WHERE AE_HEADER_ID IN (
360 SELECT AE_HEADER_ID FROM XLA_AE_HEADERS WHERE EVENT_ID IN (
361 SELECT EVENT_ID FROM XLA_EVENTS WHERE ENTITY_ID = '||p_entity_rec.entity_id||
362 '))';
363 EXECUTE IMMEDIATE l_string;
364 EXCEPTION
365 WHEN OTHERS THEN
366 log(l_path_name, 'EXCEPTION: '|| SQLERRM(sqlcode));
367 p_retcode := g_FAILURE;
368 END;
369 END IF;
370
371 IF (p_retcode = g_SUCCESS) THEN
372 BEGIN
373 l_string := 'CREATE TABLE PSA_BKP_XLA_TR_BAL_'||l_backup_string||' AS
374 SELECT * FROM XLA_TRIAL_BALANCES WHERE AE_HEADER_ID IN (
375 SELECT AE_HEADER_ID FROM XLA_AE_HEADERS WHERE EVENT_ID IN (
376 SELECT EVENT_ID FROM XLA_EVENTS WHERE ENTITY_ID = '||p_entity_rec.entity_id||
377 '))';
378 EXECUTE IMMEDIATE l_string;
379 EXCEPTION
380 WHEN OTHERS THEN
381 log(l_path_name, 'EXCEPTION: '|| SQLERRM(sqlcode));
382 p_retcode := g_FAILURE;
383 END;
384 END IF;
385
386 EXCEPTION
387 WHEN OTHERS THEN
388 log(l_path_name, 'EXCEPTION: '|| SQLERRM(sqlcode));
389 p_retcode := g_FAILURE;
390 p_errbuf := SQLERRM;
391 END;
392
393 PROCEDURE start_xla_audit
394 (
395 p_errbuf OUT NOCOPY VARCHAR2,
396 p_retcode OUT NOCOPY VARCHAR2,
397 p_audit_id OUT NOCOPY NUMBER,
398 p_mode IN VARCHAR2,
399 p_entity_rec IN xla_transaction_entities%ROWTYPE,
400 p_gl_date IN VARCHAR2,
401 p_tar_number IN VARCHAR2,
402 p_bug_number IN VARCHAR2
403 )
404 IS
405 PRAGMA AUTONOMOUS_TRANSACTION;
406 l_path_name VARCHAR2(500);
407 BEGIN
408 p_retcode := g_SUCCESS;
409 p_errbuf := NULL;
410 l_path_name := g_path_name || '.start_xla_audit';
411
412 INSERT INTO psa_xla_undo_acct_audit
413 (
414 audit_id,
415 user_id,
416 resp_id,
417 login_id,
418 request_id,
419 start_date,
420 end_date,
421 application_id,
422 entity_code,
423 source_id_int_1,
424 source_id_int_2,
425 source_id_int_3,
426 source_id_int_4,
427 source_id_char_1,
428 source_id_char_2,
429 source_id_char_3,
430 source_id_char_4,
431 document_num,
432 gl_date,
433 program_status,
434 program_mesg,
435 tar_number,
436 bug_number,
437 program_mode
438 )
439 VALUES
440 (
441 psa_xla_undo_acct_audit_s.nextval,
442 g_user_id,
443 g_resp_id,
444 g_login_id,
445 g_request_id,
446 SYSDATE,
447 NULL,
448 p_entity_rec.application_id,
449 p_entity_rec.entity_code,
450 p_entity_rec.source_id_int_1,
451 p_entity_rec.source_id_int_2,
452 p_entity_rec.source_id_int_3,
453 p_entity_rec.source_id_int_4,
454 p_entity_rec.source_id_char_1,
455 p_entity_rec.source_id_char_2,
456 p_entity_rec.source_id_char_3,
457 p_entity_rec.source_id_char_4,
458 p_entity_rec.transaction_number,
459 p_gl_date,
460 NULL,
461 NULL,
462 p_tar_number,
463 p_bug_number,
464 p_mode
465 ) RETURNING audit_id INTO p_audit_id;
466
467 IF (p_retcode = g_SUCCESS) THEN
468 backup_tables
469 (
470 p_errbuf => p_errbuf,
471 p_retcode => p_retcode,
472 p_audit_id => p_audit_id,
473 p_entity_rec => p_entity_rec
474 );
475 END IF;
476 COMMIT;
477 EXCEPTION
478 WHEN OTHERS THEN
479 log(l_path_name, 'EXCEPTION: '|| SQLERRM(sqlcode));
480 p_retcode := g_FAILURE;
481 p_errbuf := SQLERRM;
482 END;
483
484 PROCEDURE end_xla_audit
485 (
486 p_errbuf OUT NOCOPY VARCHAR2,
487 p_retcode OUT NOCOPY VARCHAR2,
488 p_audit_id IN NUMBER,
489 p_program_status IN VARCHAR2,
490 p_program_mesg IN VARCHAR2
491 )
492 IS
493 PRAGMA AUTONOMOUS_TRANSACTION;
494 l_path_name VARCHAR2(500);
495 BEGIN
496 p_retcode := g_SUCCESS;
497 p_errbuf := NULL;
498 l_path_name := g_path_name || '.end_xla_audit';
499
500 UPDATE psa_xla_undo_acct_audit
501 SET end_date = SYSDATE,
502 program_status = p_program_status,
503 program_mesg = p_program_mesg
504 WHERE audit_id = p_audit_id;
505 COMMIT;
506 EXCEPTION
507 WHEN OTHERS THEN
508 log(l_path_name, 'EXCEPTION: '|| SQLERRM(sqlcode));
509 p_retcode := g_FAILURE;
510 p_errbuf := SQLERRM;
511 END;
512
513 PROCEDURE redo_xla_accounting
514 (
515 p_errbuf OUT NOCOPY VARCHAR2,
516 p_retcode OUT NOCOPY VARCHAR2,
517 p_application_id IN xla_transaction_entities.application_id%TYPE,
518 p_events_tab IN OUT NOCOPY events_tab,
519 p_tar_number IN VARCHAR2,
520 p_bug_number IN VARCHAR2,
521 p_org_id IN NUMBER,
522 p_ledger_id IN NUMBER
523 )
524 IS
525 l_path_name VARCHAR2(500);
526 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
527 l_msg_count NUMBER;
528 l_msg_data VARCHAR2(2000) ;
529 l_bc_status VARCHAR2(2000);
530 l_packet_id NUMBER;
531 l_audit_id NUMBER;
532 l_entity_rec xla_transaction_entities%ROWTYPE;
533 l_program_status psa_xla_undo_acct_audit.program_status%TYPE;
534 l_program_mesg psa_xla_undo_acct_audit.program_mesg%TYPE;
535 l_tmp_errbuf VARCHAR2(4000);
536 l_tmp_retcode NUMBER;
537
538 BEGIN
539 p_retcode := g_SUCCESS;
540 p_errbuf := NULL;
541 l_path_name := g_path_name || '.redo_xla_accounting';
542 debug_other_string(g_state_level,l_path_name, 'BEGIN of procedure redo_xla_accounting ' );
543 debug_other_string(g_state_level,l_path_name, 'PARAMETERS PASSED' );
544 debug_other_string(g_state_level,l_path_name, '=================' );
545
546 IF (p_retcode = g_SUCCESS) THEN
547 BEGIN
548 SELECT *
549 INTO l_entity_rec
550 FROM xla_transaction_entities
551 WHERE entity_id = p_events_tab(1).entity_id;
552 EXCEPTION
553 WHEN NO_DATA_FOUND THEN
554 error('No Entity Found for entity id='||p_events_tab(1).entity_id);
555
556 p_retcode := g_FAILURE;
557 WHEN OTHERS THEN
558 error('System Error when selecting entity:'||SQLERRM);
559 p_retcode := g_FAILURE;
560 END;
561 END IF;
562
563 IF (p_retcode = g_SUCCESS) THEN
564 start_xla_audit
565 (
566 p_errbuf => p_errbuf,
567 p_retcode => p_retcode,
568 p_audit_id => l_audit_id,
569 p_mode => 'REDO',
570 p_entity_rec => l_entity_rec,
571 p_gl_date => NULL,
572 p_tar_number => p_tar_number,
573 p_bug_number => p_bug_number
574 );
575 END IF;
576
577 IF (p_retcode = g_SUCCESS) THEN
578 DELETE from psa_bc_xla_events_gt;
579 debug_other_string(g_state_level,l_path_name,'Number of rows deleted of psa_bc_xla_events_gt: ' || SQL%ROWCOUNT);
580 DELETE from xla_acct_prog_events_gt;
581 debug_other_string(g_state_level,l_path_name,'Number of rows deleted of xla_acct_prog_events_gt: ' || SQL%ROWCOUNT);
582 DELETE from xla_ae_headers_gt;
583 debug_other_string(g_state_level,l_path_name,'Number of rows deleted of xla_ae_headers_gt: ' || SQL%ROWCOUNT);
584 DELETE from xla_ae_lines_gt;
585 debug_other_string(g_state_level,l_path_name,'Number of rows deleted of xla_ae_lines_gt: ' || SQL%ROWCOUNT);
586 DELETE from xla_validation_lines_gt;
587 debug_other_string(g_state_level,l_path_name,'Number of rows deleted of xla_validation_lines_gt: ' || SQL%ROWCOUNT);
588 END IF;
589
590 IF (p_retcode = g_SUCCESS) THEN
591 FOR i IN 1..p_events_tab.COUNT LOOP
592 INSERT INTO psa_bc_xla_events_gt
593 (
594 event_id,
595 result_code
596 )
597 VALUES
598 (
599 p_events_tab(i).event_id,
600 'XLA_UNPROCESSED'
601 );
602 END LOOP;
603 END IF;
604
605 IF (p_retcode = g_SUCCESS) THEN
606 psa_bc_xla_pub.budgetary_control
607 (
608 p_api_version => 1.0,
609 p_init_msg_list => FND_API.G_FALSE,
610 x_return_status => l_return_status,
611 x_msg_count => l_msg_count,
612 x_msg_data => l_msg_data,
613 p_application_id => p_application_id,
614 p_bc_mode => 'F', /* Force Mode */
615 p_override_flag => NULL,
616 p_user_id => g_user_id,
617 p_user_resp_id => g_resp_id,
618 x_status_code => l_bc_status,
619 x_packet_id => l_packet_id
620 );
621 END IF;
622 debug_other_string(g_state_level,l_path_name,'l_return_status: ' ||l_return_status);
623 debug_other_string(g_state_level,l_path_name,'l_msg_count: ' ||l_msg_count);
624 debug_other_string(g_state_level,l_path_name,'l_msg_data: ' ||l_msg_data);
625 debug_other_string(g_state_level,l_path_name,'l_bc_status: ' ||l_bc_status);
626 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
627 p_retcode := g_FAILURE;
628 IF (l_msg_count = 1) THEN
629 error('ERROR: '||l_msg_data);
630 ELSE
631 FOR i IN 1..l_msg_count LOOP
632 l_msg_data :=fnd_msg_pub.get (p_encoded => 'F');
633 error('ERROR: '||l_msg_data);
634 END LOOP;
635 END IF;
636 END IF;
637 IF (l_bc_status in ('FATAL', 'FAIL', 'PARTIAL',
638 'XLA_ERROR','XLA_NO_JOURNAL' )) THEN
639 p_retcode := g_FAILURE;
640 END IF;
641
642
643 IF (p_retcode = g_SUCCESS) THEN
644 l_program_status := 'SUCCESS';
645 l_program_mesg := NULL;
646 ELSE
647 l_program_status := 'ERROR';
648 l_program_mesg := p_errbuf;
649 END IF;
650
651 end_xla_audit
652 (
653 p_errbuf => l_tmp_errbuf,
654 p_retcode => l_tmp_retcode,
655 p_audit_id => l_audit_id,
656 p_program_status => l_program_status,
657 p_program_mesg => l_program_mesg
658 );
659
660 IF (p_retcode = g_SUCCESS AND l_tmp_retcode = g_FAILURE) THEN
661 p_retcode := l_tmp_retcode;
662 p_errbuf := l_tmp_errbuf;
663 END IF;
664
665 IF p_retcode = g_SUCCESS THEN
666 COMMIT;
667 ELSE
668 ROLLBACK;
669 END IF;
670 debug_other_string(g_state_level,l_path_name, 'END of procedure redo_xla_accounting ' );
671 EXCEPTION
672 WHEN OTHERS THEN
673 log(l_path_name, 'EXCEPTION: '|| SQLERRM(sqlcode));
674 p_retcode := g_FAILURE;
675 p_errbuf := SQLERRM;
676 l_program_status := 'ERROR';
677 l_program_mesg := p_errbuf;
678 end_xla_audit
679 (
680 p_errbuf => l_tmp_errbuf,
681 p_retcode => l_tmp_retcode,
682 p_audit_id => l_audit_id,
683 p_program_status => l_program_status,
684 p_program_mesg => l_program_mesg
685 );
686 error (p_errbuf);
687 ROLLBACK;
688 END;
689
690 PROCEDURE undo_xla_accounting
691 (
692 p_errbuf OUT NOCOPY VARCHAR2,
693 p_retcode OUT NOCOPY VARCHAR2,
694 p_events_tab IN OUT NOCOPY events_tab,
695 p_gl_date IN VARCHAR2,
696 p_application_id IN xla_transaction_entities.application_id%TYPE,
697 p_entity_code IN xla_transaction_entities.entity_code%TYPE,
698 p_source_id_int_1 IN NUMBER DEFAULT NULL,
699 p_source_id_int_2 IN NUMBER DEFAULT NULL,
700 p_source_id_int_3 IN NUMBER DEFAULT NULL,
701 p_source_id_int_4 IN NUMBER DEFAULT NULL,
702 p_source_id_char_1 IN VARCHAR2 DEFAULT NULL,
703 p_source_id_char_2 IN VARCHAR2 DEFAULT NULL,
704 p_source_id_char_3 IN VARCHAR2 DEFAULT NULL,
705 p_source_id_char_4 IN VARCHAR2 DEFAULT NULL,
706 p_tar_number IN VARCHAR2,
707 p_bug_number IN VARCHAR2,
708 p_org_id IN NUMBER,
709 p_ledger_id IN NUMBER,
710 p_mode in varchar2
711 )
712 IS
713 l_path_name VARCHAR2(500);
714 l_gl_period_status gl_period_statuses.closing_status%TYPE;
715 l_gl_period_name gl_period_statuses.period_name%TYPE;
716 l_event_source_info xla_events_pub_pkg.t_event_source_info;
717 l_security_context xla_events_pub_pkg.t_security;
718 l_counter NUMBER;
719 l_old_event_id NUMBER;
720 l_api_version NUMBER := 1.0;
721 l_init_msg_list VARCHAR2(300) := fnd_api.g_true;
722 l_reversal_method VARCHAR2(300) := 'SIDE';
723 l_post_to_gl_flag VARCHAR2(1) := 'N';
724 l_return_status VARCHAR2(300);
725 l_msg_count NUMBER;
726 l_msg_data VARCHAR2(4000);
727 l_rev_ae_header_id INTEGER;
728 l_rev_event_id INTEGER;
729 l_rev_entity_id INTEGER;
730 l_new_event_id INTEGER;
731 l_new_entity_id INTEGER;
732 l_valuation_method VARCHAR2(30);
733 l_audit_id NUMBER;
734 l_entity_rec xla_transaction_entities%ROWTYPE;
735 l_tmp_errbuf VARCHAR2(4000);
736 l_tmp_retcode NUMBER;
737 l_program_status psa_xla_undo_acct_audit.program_status%TYPE;
738 l_program_mesg psa_xla_undo_acct_audit.program_mesg%TYPE;
739
740 BEGIN
741 p_retcode := g_SUCCESS;
742 p_errbuf := NULL;
743 l_path_name := g_path_name || '.undo_xla_accounting';
744 debug_other_string(g_state_level,l_path_name, 'BEGIN of procedure undo_xla_accounting **** NEW CODE ****' );
745 debug_other_string(g_state_level,l_path_name, 'PARAMETERS PASSED' );
746 debug_other_string(g_state_level,l_path_name, '=================' );
747 debug_other_string(g_state_level,l_path_name, 'p_application_id : '||p_application_id );
748 debug_other_string(g_state_level,l_path_name, 'p_entity_code : '||p_entity_code );
749 debug_other_string(g_state_level,l_path_name, 'p_source_id_int_1 : '||p_source_id_int_1 );
750 debug_other_string(g_state_level,l_path_name, 'p_source_id_int_2 : '||p_source_id_int_2 );
751 debug_other_string(g_state_level,l_path_name, 'p_source_id_int_3 : '||p_source_id_int_3 );
752 debug_other_string(g_state_level,l_path_name, 'p_source_id_int_4 : '||p_source_id_int_4 );
753 debug_other_string(g_state_level,l_path_name, 'p_source_id_char_1 : '||p_source_id_char_1 );
754 debug_other_string(g_state_level,l_path_name, 'p_source_id_char_2 : '||p_source_id_char_2 );
755 debug_other_string(g_state_level,l_path_name, 'p_source_id_char_3 : '||p_source_id_char_3 );
756 debug_other_string(g_state_level,l_path_name, 'p_source_id_char_4 : '||p_source_id_char_4 );
757 debug_other_string(g_state_level,l_path_name, 'p_gl_date : '||p_gl_date );
758 debug_other_string(g_state_level,l_path_name, 'p_MODE : '||p_mode);
759
760 xla_security_pkg.set_security_context(602);
761
762 IF (p_retcode = g_SUCCESS) THEN
763 BEGIN
764 SELECT *
765 INTO l_entity_rec
766 FROM xla_transaction_entities t
767 WHERE application_id = p_application_id
768 AND entity_code = p_entity_code
769 AND NVL(source_id_int_1, -1) = NVL(p_source_id_int_1, -1)
770 AND NVL(source_id_int_2, -1) = NVL(p_source_id_int_2, -1)
771 AND NVL(source_id_int_3, -1) = NVL(p_source_id_int_3, -1)
772 AND NVL(source_id_int_4, -1) = NVL(p_source_id_int_4, -1)
773 AND NVL(source_id_char_1, ' ') = NVL(p_source_id_char_1, ' ')
774 AND NVL(source_id_char_2, ' ') = NVL(p_source_id_char_2, ' ')
775 AND NVL(source_id_char_3, ' ') = NVL(p_source_id_char_3, ' ')
776 AND NVL(source_id_char_4, ' ') = NVL(p_source_id_char_4, ' ')
777 AND EXISTS (SELECT 1
778 FROM xla_events e
779 WHERE t.entity_id = e.entity_id);
780 EXCEPTION
781 WHEN NO_DATA_FOUND THEN
782 error('No Entity Found for application id='||p_application_id ||
783 ', and entity_code='||p_entity_code||
784 ', and source_id_int_1='||p_source_id_int_1);
785
786 p_retcode := g_FAILURE;
787 WHEN OTHERS THEN
788 error('System Error when selecting entity:'||SQLERRM);
789 p_retcode := g_FAILURE;
790 END;
791 END IF;
792
793
794 IF (p_retcode = g_SUCCESS) THEN
795 log(l_path_name, 'After start_xla_audit');
796
797 start_xla_audit
798 (
799 p_errbuf => p_errbuf,
800 p_retcode => p_retcode,
801 p_audit_id => l_audit_id,
802 p_mode => 'UNDO',
803 p_entity_rec => l_entity_rec,
804 p_gl_date => p_gl_date,
805 p_tar_number => p_tar_number,
806 p_bug_number => p_bug_number
807 );
808 END IF;
809
810
811 IF (p_retcode = g_SUCCESS) THEN
812 BEGIN
813 log(l_path_name, 'Calling validate_gl_date');
814 validate_gl_date (p_gl_date, p_ledger_id);
815 EXCEPTION
816 WHEN OTHERS THEN
817 log(l_path_name, 'validate_gl_date failure');
818 error (SQLERRM);
819 p_retcode := g_FAILURE;
820 END;
821 END IF;
822
823
824 /* Gather all the events that are to be deleted */
825
826 IF (p_retcode = g_SUCCESS) THEN
827
828 log(l_path_name, 'Gathering Events that are to be processed');
829 l_counter := 0;
830 l_old_event_id := -1;
831 FOR events_rec IN (SELECT e.event_id,
832 e.budgetary_control_flag,
833 e.event_status_code,
834 nvl(h.gl_transfer_status_code,'N') gl_xfer_flag,
835 h.accounting_date,
836 h.ae_header_id,
837 e.event_type_code,
838 ent.legal_entity_id,
839 ent.ledger_id,
840 ent.transaction_number
841 FROM xla_events e,
842 xla_ae_headers h,
843 xla_transaction_entities ent
844 WHERE ent.source_id_int_1 = p_source_id_int_1
845 and ent.application_id = p_application_id
846 AND ent.entity_code = p_entity_code
847 and e.entity_id = ent.entity_id
848 and e.budgetary_control_flag = decode(p_mode , 'B' , 'Y', e.budgetary_control_flag)
849 AND e.event_id = h.event_id(+)
850 ORDER BY e.event_id )
851 LOOP
852 log(l_path_name, 'Event id = '||events_rec.event_id);
853
854
855 IF (l_old_event_id <> events_rec.event_id) THEN
856 l_counter := l_counter + 1;
857 p_events_tab(l_counter).event_id := events_rec.event_id;
858 p_events_tab(l_counter).budgetary_flag := events_rec.budgetary_control_flag;
859 p_events_tab(l_counter).event_status_code := events_rec.event_status_code;
860 p_events_tab(l_counter).ae_header_id := events_rec.ae_header_id;
861 p_events_tab(l_counter).transaction_number := events_rec.transaction_number;
862 p_events_tab(l_counter).legal_entity_id := events_rec.legal_entity_id;
863 p_events_tab(l_counter).source_id_int_1 := p_source_id_int_1;
864 p_events_tab(l_counter).application_id := p_application_id;
865 p_events_tab(l_counter).event_type_code := events_rec.event_type_code;
866 p_events_tab(l_counter).entity_code := p_entity_code;
867 p_events_tab(l_counter).ledger_id := events_rec.ledger_id;
868 p_events_tab(l_counter).gl_date := p_gl_date;
869 p_events_tab(l_counter).gl_transfer_flag := events_rec.gl_xfer_flag;
870 log(l_path_name, 'gl_transfer_flag='||events_rec.gl_xfer_flag);
871
872 IF (events_rec.accounting_date IS NOT NULL) THEN
873 BEGIN
874 SELECT p.closing_status
875 INTO l_gl_period_status
876 FROM gl_period_statuses p
877 WHERE p.application_id = 101
878 AND p.set_of_books_id = l_entity_rec.ledger_id
879 AND p.adjustment_period_flag = 'N'
880 AND events_rec.accounting_date BETWEEN p.start_date AND p.end_date;
881 IF (l_gl_period_status <> 'O') THEN
882 log(l_path_name, 'Period '||l_gl_period_name||' is not open');
883 log(l_path_name, 'So Using Entered GL Date '||p_gl_date);
884 p_events_tab(l_counter).gl_date := p_gl_date;
885 ELSE
886 p_events_tab(l_counter).gl_date := events_rec.accounting_date;
887 END IF;
888 EXCEPTION
889 WHEN NO_DATA_FOUND THEN
890 log(l_path_name, 'No Period found for Accounting Date '||events_rec.accounting_date);
891 log(l_path_name, 'So Using Entered GL Date '||p_gl_date);
892 p_events_tab(l_counter).gl_date := events_rec.accounting_date;
893 WHEN OTHERS THEN
894 error('Error While selecting GL Period Information For Accouting Date: '||SQLERRM);
895 p_retcode := g_FAILURE;
896 END;
897 END IF;
898 END IF;
899 l_old_event_id := events_rec.event_id;
900 IF (p_retcode = g_FAILURE) THEN
901 EXIT;
902 END IF;
903 END LOOP;
904 log(l_path_name, 'Finished gathering Events that are to be processed');
905 END IF;
906
907 /* Start Deleting the Events */
908 IF (p_retcode = g_SUCCESS) THEN
909 log(l_path_name, 'Start Deleting Events');
910
911 FOR l_counter IN 1..p_events_tab.COUNT LOOP
912 log(l_path_name, 'Now Deleting Event: '||p_events_tab(l_counter).event_id);
913
914 IF (p_events_tab(l_counter).event_status_code = 'U') THEN
915 l_event_source_info.source_application_id := NULL;
916 l_event_source_info.application_id := p_events_tab(l_counter).application_id;
917 l_event_source_info.entity_type_code := p_events_tab(l_counter).entity_code;
918 l_event_source_info.ledger_id := p_events_tab(l_counter).ledger_id;
919 l_event_source_info.legal_entity_id := p_events_tab(l_counter).legal_entity_id;
920 l_event_source_info.transaction_number := p_events_tab(l_counter).transaction_number;
921 l_event_source_info.source_id_int_1 := p_events_tab(l_counter).source_id_int_1;
922 l_security_context.security_id_int_1 := p_org_id;
923 log(l_path_name, 'Calling xla_events_pub_pkg.delete_event ');
924
925 xla_events_pub_pkg.delete_event
926 (
927 p_event_source_info => l_event_source_info,
928 p_event_id => p_events_tab(l_counter).event_id,
929 p_valuation_method => l_valuation_method,
930 p_security_context => l_security_context
931 );
932 IF (p_application_id IN (200)) THEN
933 IF (p_events_tab(l_counter).budgetary_flag = 'Y') THEN
934 log(l_path_name, 'Deleting BC XLA Events');
935 DELETE xla_events
936 WHERE event_id = p_events_tab(l_counter).event_id;
937 log(l_path_name, 'Deleted '||SQL%ROWCOUNT||' rows from xla_events');
938 END IF;
939 END IF;
940 ELSE
941 IF (NVL(p_events_tab(l_counter).gl_transfer_flag, 'N') <> 'Y') THEN
942 log(l_path_name, 'Calling xla_datafixes_pub.delete_journal_entries');
943 xla_datafixes_pub.delete_journal_entries
944 (
945 p_api_version => l_api_version,
946 p_init_msg_list => l_init_msg_list,
947 p_application_id => p_application_id,
948 p_event_id => p_events_tab(l_counter).event_id,
949 x_return_status => l_return_status,
950 x_msg_count => l_msg_count,
951 x_msg_data => l_msg_data
952 );
953 ELSE
954 log(l_path_name, 'Calling xla_datafixes_pub.reverse_journal_entries');
955 xla_datafixes_pub.reverse_journal_entries
956 (
957 p_api_version => l_api_version,
958 p_init_msg_list => l_init_msg_list,
959 p_application_id => p_application_id,
960 p_event_id => p_events_tab(l_counter).event_id,
961 p_reversal_method => l_reversal_method,
962 p_gl_date => p_events_tab(l_counter).gl_date,
963 p_post_to_gl_flag => l_post_to_gl_flag,
964 x_return_status => l_return_status,
965 x_msg_count => l_msg_count,
966 x_msg_data => l_msg_data ,
967 x_rev_ae_header_id => l_rev_ae_header_id,
968 x_rev_event_id => l_rev_event_id,
969 x_rev_entity_id => l_rev_entity_id,
970 x_new_event_id => l_new_event_id,
971 x_new_entity_id => l_new_entity_id
972 );
973 END IF;
974
975 log(l_path_name, 'l_return_status ='||l_return_status);
976
977 IF (l_return_status <> 'S') THEN
978 p_retcode := g_FAILURE;
979 IF (l_msg_count = 1) THEN
980 error('ERROR: '||l_msg_data);
981 ELSE
982 FOR i IN 1..l_msg_count LOOP
983 l_msg_data :=fnd_msg_pub.get (p_encoded => 'F');
984 error('ERROR: '||l_msg_data);
985 END LOOP;
986 END IF;
987 ELSE
988 IF (p_events_tab(l_counter).budgetary_flag = 'Y') THEN
989 log(l_path_name, 'Deleting GL BC Packets Data');
990 DELETE gl_bc_packets
991 WHERE event_id = p_events_tab(l_counter).event_id;
992 log(l_path_name, 'Deleted '||SQL%ROWCOUNT||' rows from GL BC Packets');
993 End if;
994 IF (p_application_id IN (200)) THEN
995 IF (p_events_tab(l_counter).budgetary_flag = 'Y') THEN
996 log(l_path_name, 'Deleting BC XLA Events');
997 DELETE xla_events
998 WHERE event_id = p_events_tab(l_counter).event_id;
999 log(l_path_name, 'Deleted '||SQL%ROWCOUNT||' rows from xla_events');
1000 ELSE
1001 log(l_path_name, 'Updating AP XLA Events');
1002 UPDATE xla_events
1003 SET event_date = p_events_tab(l_counter).gl_date
1004 WHERE event_id = p_events_tab(l_counter).event_id;
1005 log(l_path_name, 'Updated '||SQL%ROWCOUNT||' rows in xla_events');
1006 END IF;
1007 ELSE
1008 log(l_path_name, 'Updating AP XLA Events');
1009 UPDATE xla_events
1010 SET event_date = p_events_tab(l_counter).gl_date
1011 WHERE event_id = p_events_tab(l_counter).event_id;
1012 log(l_path_name, 'Updated '||SQL%ROWCOUNT||' rows in xla_events');
1013 END IF;
1014 END IF;
1015 END IF; -- status
1016
1017 IF (p_retcode = g_FAILURE) THEN
1018 EXIT;
1019 END IF;
1020
1021 END LOOP;
1022
1023 END IF;
1024
1025
1026 /* Recheck to see if there are still any events left out */
1027 IF (p_retcode = g_SUCCESS) THEN
1028 l_program_status := 'SUCCESS';
1029 l_program_mesg := NULL;
1030 ELSE
1031 l_program_status := 'ERROR';
1032 l_program_mesg := p_errbuf;
1033 END IF;
1034
1035 end_xla_audit
1036 (
1037 p_errbuf => l_tmp_errbuf,
1038 p_retcode => l_tmp_retcode,
1039 p_audit_id => l_audit_id,
1040 p_program_status => l_program_status,
1041 p_program_mesg => l_program_mesg
1042 );
1043
1044 IF (p_retcode = g_SUCCESS AND l_tmp_retcode = g_FAILURE) THEN
1045 p_retcode := l_tmp_retcode;
1046 p_errbuf := l_tmp_errbuf;
1047 END IF;
1048
1049 /*
1050 IF p_retcode = g_SUCCESS THEN
1051 COMMIT;
1052 ELSE
1053 ROLLBACK;
1054 END IF;
1055 */
1056
1057 debug_other_string(g_state_level,l_path_name, 'END of procedure undo_xla_accounting ' );
1058 EXCEPTION
1059 WHEN OTHERS THEN
1060 log(l_path_name, 'EXCEPTION: '|| SQLERRM(sqlcode));
1061 p_retcode := g_FAILURE;
1062 p_errbuf := SQLERRM;
1063 l_program_status := 'ERROR';
1064 l_program_mesg := p_errbuf;
1065 end_xla_audit
1066 (
1067 p_errbuf => l_tmp_errbuf,
1068 p_retcode => l_tmp_retcode,
1069 p_audit_id => l_audit_id,
1070 p_program_status => l_program_status,
1071 p_program_mesg => l_program_mesg
1072 );
1073 error (p_errbuf);
1074 ROLLBACK;
1075 END;
1076
1077
1078 PROCEDURE undo_xla_accounting_for_inv
1079 (
1080 p_errbuf OUT NOCOPY VARCHAR2,
1081 p_retcode OUT NOCOPY VARCHAR2,
1082 p_invoice_id IN NUMBER,
1083 p_ledger_id IN NUMBER,
1084 p_org_id IN NUMBER,
1085 p_gl_date IN DATE,
1086 p_tar_number IN VARCHAR2,
1087 p_bug_number IN VARCHAR2,
1088 p_mode in varchar2 default 'B'
1089 )
1090 IS
1091 l_path_name VARCHAR2(500);
1092 l_application_id xla_transaction_entities.application_id%TYPE;
1093 l_entity_code xla_transaction_entities.entity_code%TYPE;
1094 l_events_tab events_tab;
1095 l_event_check number(15);
1096 BEGIN
1097 p_retcode := g_SUCCESS;
1098 p_errbuf := NULL;
1099 l_path_name := g_path_name || '.undo_xla_accounting_for_invoice';
1100 debug_other_string(g_state_level,l_path_name, 'BEGIN of procedure undo_xla_accounting_for_invoice ' );
1101 log(l_path_name, 'PARAMETERS PASSED' );
1102 log(l_path_name, '=================' );
1103 log(l_path_name, 'p_invoice_id : '||p_invoice_id );
1104 log(l_path_name, 'p_mode : '||p_mode);
1105 log(l_path_name, 'p_gl_date : '||p_gl_date );
1106 log(l_path_name, 'g_request_id : '||g_request_id );
1107
1108 IF (p_retcode = g_SUCCESS) THEN
1109 mo_global.set_policy_context('S',p_org_id);
1110 l_application_id := 200;
1111 l_entity_code := 'AP_INVOICES';
1112 debug_other_string(g_state_level,l_path_name, 'Calling Generic undo_xla_accounting' );
1113 undo_xla_accounting
1114 (
1115 p_errbuf => p_errbuf,
1116 p_retcode => p_retcode,
1117 p_events_tab => l_events_tab,
1118 p_gl_date => p_gl_date,
1119 p_application_id => l_application_id,
1120 p_entity_code => l_entity_code,
1121 p_source_id_int_1 => p_invoice_id,
1122 p_tar_number => p_tar_number,
1123 p_bug_number => p_bug_number,
1124 p_org_id => p_org_id,
1125 p_ledger_id => p_ledger_id,
1126 p_mode => p_mode
1127 );
1128 debug_other_string(g_state_level,l_path_name, 'undo_xla_accounting Returned' );
1129 debug_other_string(g_state_level,l_path_name, 'p_errbuf='||p_errbuf );
1130 debug_other_string(g_state_level,l_path_name, 'p_retcode='||p_retcode );
1131 END IF;
1132
1133
1134 IF p_retcode = g_SUCCESS THEN
1135 --COMMIT;
1136 /*
1137 debug_other_string(g_state_level,l_path_name, 'Checking if all events cleaned up' );
1138 select count(*) into l_event_check
1139 from xla_events e,
1140 xla_transaction_entities ent
1141 WHERE ent.source_id_int_1 = p_invoice_id
1142 and ent.application_id = l_application_id
1143 AND ent.entity_code = l_entity_code
1144 and e.entity_id = ent.entity_id
1145 and e.budgetary_control_flag = decode(p_mode , 'B', 'Y' , e.budgetary_control_flag);
1146
1147 if l_event_check > 0 then
1148 p_retcode := G_failure;
1149 debug_other_string(g_state_level,l_path_name, 'Error : No of events left uncleaned up = '||l_event_check );
1150 else
1151 debug_other_string(g_state_level,l_path_name, 'Success : All events Cleaned up ');
1152 End if;
1153 */
1154 debug_other_string(g_state_level,l_path_name, 'Checking if all Header/links cleaned up' );
1155 select count(*) into l_event_check
1156 from xla_events e,
1157 xla_transaction_entities ent,
1158 xla_ae_headers h
1159 WHERE ent.source_id_int_1 = p_invoice_id
1160 and ent.application_id = l_application_id
1161 AND ent.entity_code = l_entity_code
1162 and e.entity_id = ent.entity_id
1163 and h.application_id = l_application_id
1164 and h.event_id = e.event_id
1165 and e.budgetary_control_flag = decode(p_mode , 'B', 'Y' , e.budgetary_control_flag)
1166 and e.event_status_code in ('U', 'I')
1167 and e.process_status_code <> 'P';
1168
1169
1170 if l_event_check > 0 then
1171 p_retcode := G_failure;
1172 debug_other_string(g_state_level,l_path_name, 'Error : No of Headers left uncleaned up = '||l_event_check );
1173 else
1174 debug_other_string(g_state_level,l_path_name, 'Success : All Headers/Links Cleaned up ');
1175 End if;
1176
1177
1178 End if;
1179
1180 /* Now Reset the Documents */
1181 IF (p_retcode = g_SUCCESS) THEN
1182 FOR l_counter IN 1..l_events_tab.COUNT LOOP
1183 IF (l_events_tab(l_counter).budgetary_flag = 'N') THEN
1184 debug_other_string(g_state_level,l_path_name, 'Updating period_name for accounting events' );
1185 UPDATE ap_invoice_distributions_all aid
1186 SET period_name = (SELECT DISTINCT gps.period_name
1187 FROM gl_period_statuses gps
1188 WHERE gps.application_id = 200
1189 AND gps.set_of_books_id = l_events_tab(l_counter).ledger_id
1190 AND nvl(gps.adjustment_period_flag, 'N') = 'N'
1191 AND l_events_tab(l_counter).gl_date BETWEEN TRUNC(gps.start_date) AND TRUNC(gps.end_date))
1192 WHERE invoice_id = p_invoice_id
1193 AND accounting_event_id = l_events_tab(l_counter).event_id;
1194 END IF;
1195 END LOOP;
1196
1197 debug_other_string(g_state_level,l_path_name, 'Deleting ap_holds_all' );
1198 DELETE ap_holds_all
1199 WHERE invoice_id = p_invoice_id
1200 AND hold_lookup_code = 'CANT FUNDS CHECK'
1201 AND release_lookup_code IS NULL;
1202 debug_other_string(g_state_level,l_path_name, 'Deleted '||SQL%ROWCOUNT||' rows.' );
1203
1204 debug_other_string(g_state_level,l_path_name, 'Updating ap_invoice_distributions_all for BC flags(1)' );
1205 UPDATE ap_invoice_distributions_all
1206 SET encumbered_flag = 'N',
1207 match_status_flag = 'N',
1208 bc_event_id = NULL,
1209 posted_flag = 'N'
1210 WHERE invoice_id = p_invoice_id
1211 AND NVL(encumbered_flag,'X') <> 'R';
1212 debug_other_string(g_state_level,l_path_name, 'Updated '||SQL%ROWCOUNT||' rows.' );
1213
1214
1215 debug_other_string(g_state_level,l_path_name, 'Updating gms_award_distributions' );
1216
1217 update gms_award_distributions
1218 set fc_status = 'N'
1219 where invoice_distribution_id in (select invoice_distribution_id
1220 from ap_invoice_distributions_all
1221 where invoice_id = p_invoice_id
1222 and award_id is not null)
1223 and fc_status ='A';
1224
1225 debug_other_string(g_state_level,l_path_name, 'Updated '||SQL%ROWCOUNT||' rows.' );
1226
1227 debug_other_string(g_state_level,l_path_name, 'Updating ap_self_assessed_tax_dist_all');
1228
1229 UPDATE ap_self_assessed_tax_dist_all sad
1230 SET bc_event_id = null,
1231 accounting_event_id = decode(p_mode , 'A' , NULL,accounting_event_id)
1232 WHERE sad.invoice_id = p_invoice_id
1233 AND sad.invoice_distribution_id in (select invoice_distribution_id
1234 from ap_invoice_distributions_all
1235 where invoice_id = p_invoice_id);
1236
1237 debug_other_string(g_state_level,l_path_name, 'Updated '||SQL%ROWCOUNT||' rows.' );
1238
1239 debug_other_string(g_state_level,l_path_name, 'Updating ap_invoice_distributions_all for Actual events' );
1240 UPDATE ap_invoice_distributions_all
1241 SET accounting_event_id = decode(p_mode , 'A' , NULL, accounting_event_id)
1242 WHERE invoice_id = p_invoice_id;
1243 debug_other_string(g_state_level,l_path_name, 'Updated '||SQL%ROWCOUNT||' rows.' );
1244 End if;
1245
1246 debug_other_string(g_state_level,l_path_name, 'Updating ap_prepay_history_all' );
1247 UPDATE ap_prepay_history_all h
1248 SET h.bc_event_id = NULL,
1249 h.accounting_event_id = decode(p_mode , 'A' , NULL,h.accounting_event_id)
1250 WHERE h.invoice_id = p_invoice_id;
1251 debug_other_string(g_state_level,l_path_name, 'Updated '||SQL%ROWCOUNT||' rows.' );
1252
1253 debug_other_string(g_state_level,l_path_name, 'Updating ap_prepay_app_dists' );
1254 UPDATE ap_prepay_app_dists d
1255 SET d.bc_event_id = NULL,
1256 d.accounting_event_id = decode(p_mode , 'A' , NULL,d.accounting_event_id)
1257 WHERE d.invoice_distribution_id IN (SELECT invoice_distribution_id
1258 FROM ap_invoice_distributions
1259 WHERE invoice_id = p_invoice_id);
1260
1261 debug_other_string(g_state_level,l_path_name, 'Updated '||SQL%ROWCOUNT||' rows.' );
1262
1263
1264
1265 IF (p_retcode = g_SUCCESS and p_mode = 'A') THEN
1266 FOR l_counter IN 1..l_events_tab.COUNT LOOP
1267 IF (l_events_tab(l_counter).budgetary_flag = 'N') THEN
1268 debug_other_string(g_state_level,l_path_name, 'Resetting flags on AID for accounting events' );
1269 UPDATE ap_invoice_distributions_all aid
1270 SET accrual_posted_flag = 'N',
1271 cash_posted_flag = 'N',
1272 posted_flag = 'N',
1273 accounting_date = l_events_tab(l_counter).gl_date
1274 WHERE invoice_id = p_invoice_id;
1275
1276 UPDATE ap_prepay_history_all
1277 SET posted_flag = 'N'
1278 WHERE invoice_id = p_invoice_id;
1279
1280
1281
1282 END IF;
1283 END LOOP;
1284 END IF;
1285
1286 debug_other_string(g_state_level,l_path_name, 'END of procedure undo_xla_accounting_for_invoice ' );
1287 return;
1288 EXCEPTION
1289 WHEN OTHERS THEN
1290 log(l_path_name, 'EXCEPTION: '|| SQLERRM(sqlcode));
1291 p_retcode := g_FAILURE;
1292 p_errbuf := SQLERRM;
1293 END;
1294
1295 PROCEDURE undo_xla_accounting_for_inv
1296 (
1297 p_invoice_id IN NUMBER,
1298 p_user_id IN fnd_user.user_id%TYPE,
1299 p_resp_id IN fnd_responsibility.responsibility_id%TYPE,
1300 p_gl_date IN DATE,
1301 p_tar_number IN VARCHAR2,
1302 p_bug_number IN VARCHAR2,
1303 p_mode in varchar2 default 'B',
1304 p_start_deb_id OUT NOCOPY NUMBER,
1305 p_end_deb_id OUT NOCOPY NUMBER
1306 )
1307 IS
1308 l_path_name VARCHAR2(500);
1309 l_retcode NUMBER;
1310 l_errbuf VARCHAR2(2000);
1311 l_user_name fnd_user.user_name%TYPE;
1312 l_start_log_id NUMBER;
1313 l_end_log_id NUMBER;
1314 l_org_id NUMBER;
1315 l_ledger_id NUMBER;
1316 l_invoice_num ap_invoices_all.invoice_num%TYPE;
1317
1318 BEGIN
1319 l_retcode := g_SUCCESS;
1320
1321 validate_user_id (p_user_id);
1322 validate_resp_id (p_user_id, p_resp_id, 200);
1323 validate_invoice_id (p_user_id, p_resp_id, p_invoice_id, l_invoice_num, l_org_id, l_ledger_id);
1324 validate_gl_date (p_gl_date, l_ledger_id);
1325 validate_tar_number (p_tar_number);
1326 validate_bug_number (p_bug_number);
1327
1328 /*ENABLE DEBUGGIN AUTOMATICALLY */
1329 IF (l_retcode = g_SUCCESS) THEN
1330 fnd_profile.put ('AFLOG_ENABLED', 'Y');
1331 fnd_profile.put ('AFLOG_LEVEL', '1');
1332 fnd_profile.put ('AFLOG_MODULE', '%');
1333 g_debug_level := 1;
1334 END IF;
1335
1336 SELECT fnd_log_messages_s.nextval
1337 INTO l_start_log_id
1338 FROM DUAL;
1339
1340 IF (l_retcode = g_SUCCESS) THEN
1341 g_user_id := p_user_id;
1342 g_resp_id := p_resp_id;
1343 output ('User Id:'||p_user_id);
1344 output ('Resp Id:'||p_resp_id);
1345 output ('Invoice Id:'||p_invoice_id);
1346 output ('TAR Number:'||p_tar_number);
1347 output ('BUG Number:'||p_bug_number);
1348 fnd_global.apps_initialize(g_user_id, g_resp_id, 200);
1349 mo_global.init('SQLAP');
1350
1351 debug_other_string(g_state_level,l_path_name, 'Calling undo_xla_accounting_for_inv ' );
1352 undo_xla_accounting_for_inv
1353 (
1354 p_errbuf => l_errbuf,
1355 p_retcode => l_retcode,
1356 p_invoice_id => p_invoice_id,
1357 p_ledger_id => l_ledger_id,
1358 p_org_id => l_org_id,
1359 p_gl_date => p_gl_date,
1360 p_tar_number => p_tar_number,
1361 p_bug_number => p_bug_number,
1362 p_mode => p_mode
1363 );
1364 debug_other_string(g_state_level,l_path_name, 'undo_xla_accounting_for_inv returned' );
1365 debug_other_string(g_state_level,l_path_name, 'l_errbuf='||l_errbuf );
1366 debug_other_string(g_state_level,l_path_name, 'l_retcode='||l_retcode );
1367 END IF;
1368
1369 SELECT fnd_log_messages_s.nextval
1370 INTO l_end_log_id
1371 FROM DUAL;
1372
1373 error (' ');
1374 error (' ');
1375 IF (l_retcode <> g_SUCCESS) THEN
1376 error ('ERROR: There was an error trying to Undo the accouting for the invoice Id '||p_invoice_id);
1377 ROLLBACK;
1378 ELSE
1379 error ('Undo accounting was successful for Invoice Id: '||p_invoice_id);
1380 COMMIT;
1381 END IF;
1382
1383 p_start_deb_id := l_start_log_id;
1384 p_end_deb_id := l_end_log_id;
1385
1386 EXCEPTION
1387 WHEN OTHERS THEN
1388 log(l_path_name, 'EXCEPTION: '|| SQLERRM(sqlcode));
1389 l_retcode := g_FAILURE;
1390 l_errbuf := SQLERRM;
1391 error (l_errbuf);
1392 END;
1393
1394 PROCEDURE undo_xla_accounting_for_po
1395 (
1396 p_errbuf OUT NOCOPY VARCHAR2,
1397 p_retcode OUT NOCOPY VARCHAR2,
1398 p_po_header_id IN NUMBER,
1399 p_ledger_id IN NUMBER,
1400 p_org_id IN NUMBER,
1401 p_gl_date IN DATE,
1402 p_tar_number IN VARCHAR2,
1403 p_bug_number IN VARCHAR2
1404 )
1405 IS
1406 l_path_name VARCHAR2(500);
1407 l_application_id xla_transaction_entities.application_id%TYPE;
1408 l_entity_code xla_transaction_entities.entity_code%TYPE;
1409 l_events_tab events_tab;
1410 BEGIN
1411 p_retcode := g_SUCCESS;
1412 p_errbuf := NULL;
1413 l_path_name := g_path_name || '.undo_xla_accounting_for_po';
1414 debug_other_string(g_state_level,l_path_name, 'BEGIN of procedure undo_xla_accounting_for_po ' );
1415 log(l_path_name, 'PARAMETERS PASSED' );
1416 log(l_path_name, '=================' );
1417 log(l_path_name, 'p_po_header_id : '||p_po_header_id );
1418 log(l_path_name, 'p_gl_date : '||p_gl_date );
1419 log(l_path_name, 'g_request_id : '||g_request_id );
1420
1421 IF (p_retcode = g_SUCCESS) THEN
1422 mo_global.set_policy_context('S',p_org_id);
1423 l_application_id := 201;
1424 l_entity_code := 'PURCHASE_ORDER';
1425 debug_other_string(g_state_level,l_path_name, 'Calling Generic undo_xla_accounting' );
1426 undo_xla_accounting
1427 (
1428 p_errbuf => p_errbuf,
1429 p_retcode => p_retcode,
1430 p_events_tab => l_events_tab,
1431 p_gl_date => p_gl_date,
1432 p_application_id => l_application_id,
1433 p_entity_code => l_entity_code,
1434 p_source_id_int_1 => p_po_header_id,
1435 p_tar_number => p_tar_number,
1436 p_bug_number => p_bug_number,
1437 p_org_id => p_org_id,
1438 p_ledger_id => p_ledger_id,
1439 p_mode => 'B'
1440 );
1441 debug_other_string(g_state_level,l_path_name, 'undo_xla_accounting Returned' );
1442 debug_other_string(g_state_level,l_path_name, 'p_errbuf='||p_errbuf );
1443 debug_other_string(g_state_level,l_path_name, 'p_retcode='||p_retcode );
1444 END IF;
1445
1446 /* Now Reset the Documents */
1447 --IF (p_retcode = g_SUCCESS) THEN
1448 -- NULL;
1449 --END IF;
1450
1451 debug_other_string(g_state_level,l_path_name, 'END of procedure undo_xla_accounting_for_po ' );
1452 EXCEPTION
1453 WHEN OTHERS THEN
1454 log(l_path_name, 'EXCEPTION: '|| SQLERRM(sqlcode));
1455 p_retcode := g_FAILURE;
1456 p_errbuf := SQLERRM;
1457 END;
1458
1459 PROCEDURE undo_xla_accounting_for_po
1460 (
1461 p_po_header_id IN NUMBER,
1462 p_user_id IN fnd_user.user_id%TYPE,
1463 p_resp_id IN fnd_responsibility.responsibility_id%TYPE,
1464 p_gl_date IN DATE,
1465 p_tar_number IN VARCHAR2,
1466 p_bug_number IN VARCHAR2,
1467 p_start_deb_id OUT NOCOPY NUMBER,
1468 p_end_deb_id OUT NOCOPY NUMBER
1469 )
1470 IS
1471 l_path_name VARCHAR2(500);
1472 l_retcode NUMBER;
1473 l_errbuf VARCHAR2(2000);
1474 l_user_name fnd_user.user_name%TYPE;
1475 l_start_log_id NUMBER;
1476 l_end_log_id NUMBER;
1477 l_org_id NUMBER;
1478 l_ledger_id NUMBER;
1479 l_po_num po_headers_all.segment1%TYPE;
1480
1481 BEGIN
1482 l_retcode := g_SUCCESS;
1483
1484 validate_user_id (p_user_id);
1485 validate_resp_id (p_user_id, p_resp_id, 201);
1486 validate_po_header_id (p_user_id, p_resp_id, p_po_header_id, l_po_num, l_org_id, l_ledger_id);
1487 validate_gl_date (p_gl_date, l_ledger_id);
1488 validate_tar_number (p_tar_number);
1489 validate_bug_number (p_bug_number);
1490
1491 /*ENABLE DEBUGGIN AUTOMATICALLY */
1492 IF (l_retcode = g_SUCCESS) THEN
1493 fnd_profile.put ('AFLOG_ENABLED', 'Y');
1494 fnd_profile.put ('AFLOG_LEVEL', '1');
1495 fnd_profile.put ('AFLOG_MODULE', '%');
1496 g_debug_level := 1;
1497 END IF;
1498
1499 SELECT fnd_log_messages_s.nextval
1500 INTO l_start_log_id
1501 FROM DUAL;
1502
1503 IF (l_retcode = g_SUCCESS) THEN
1504 g_user_id := p_user_id;
1505 g_resp_id := p_resp_id;
1506 output ('User Id:'||p_user_id);
1507 output ('Resp Id:'||p_resp_id);
1508 output ('Po Header Id:'||p_po_header_id);
1509 output ('TAR Number:'||p_tar_number);
1510 output ('BUG Number:'||p_bug_number);
1511 fnd_global.apps_initialize(g_user_id, g_resp_id, 201);
1512 mo_global.init('PO');
1513
1514 debug_other_string(g_state_level,l_path_name, 'Calling undo_xla_accounting_for_po ' );
1515 undo_xla_accounting_for_po
1516 (
1517 p_errbuf => l_errbuf,
1518 p_retcode => l_retcode,
1519 p_po_header_id => p_po_header_id,
1520 p_ledger_id => l_ledger_id,
1521 p_org_id => l_org_id,
1522 p_gl_date => p_gl_date,
1523 p_tar_number => p_tar_number,
1524 p_bug_number => p_bug_number
1525 );
1526 debug_other_string(g_state_level,l_path_name, 'undo_xla_accounting_for_po returned' );
1527 debug_other_string(g_state_level,l_path_name, 'l_errbuf='||l_errbuf );
1528 debug_other_string(g_state_level,l_path_name, 'l_retcode='||l_retcode );
1529 END IF;
1530
1531 SELECT fnd_log_messages_s.nextval
1532 INTO l_end_log_id
1533 FROM DUAL;
1534
1535 error (' ');
1536 error (' ');
1537 IF (l_retcode <> g_SUCCESS) THEN
1538 error ('ERROR: There was an error trying to Undo the accouting for the Po Header Id '||p_po_header_id);
1539 ELSE
1540 error ('Undo accounting was successful for Po Header Id: '||p_po_header_id);
1541 END IF;
1542
1543 /*
1544 error (' ');
1545 error (' ');
1546 error ('For debugging purpose use the following SQL');
1547 error ('select * from fnd_log_messages ');
1548 error ('where log_sequence between '||l_start_log_id ||' and '||l_end_log_id);
1549 error ('and audsid = '||g_audsid);
1550 error ('order by log_sequence');
1551 error (' ');
1552 error (' ');
1553 */
1554 p_start_deb_id := l_start_log_id;
1555 p_end_deb_id := l_end_log_id;
1556
1557 EXCEPTION
1558 WHEN OTHERS THEN
1559 log(l_path_name, 'EXCEPTION: '|| SQLERRM(sqlcode));
1560 l_retcode := g_FAILURE;
1561 l_errbuf := SQLERRM;
1562 error (l_errbuf);
1563 END;
1564
1565 PROCEDURE redo_xla_accounting_for_po
1566 (
1567 p_po_header_id IN NUMBER,
1568 p_user_id IN fnd_user.user_id%TYPE,
1569 p_resp_id IN fnd_responsibility.responsibility_id%TYPE,
1570 p_tar_number IN VARCHAR2,
1571 p_bug_number IN VARCHAR2,
1572 p_start_deb_id OUT NOCOPY NUMBER,
1573 p_end_deb_id OUT NOCOPY NUMBER
1574 )
1575 IS
1576 l_path_name VARCHAR2(500);
1577 l_retcode NUMBER;
1578 l_errbuf VARCHAR2(2000);
1579 l_user_name fnd_user.user_name%TYPE;
1580 l_start_log_id NUMBER;
1581 l_end_log_id NUMBER;
1582 l_org_id NUMBER;
1583 l_ledger_id NUMBER;
1584 l_po_num po_headers_all.segment1%TYPE;
1585 l_events_tab events_tab;
1586 l_application_id NUMBER := 201;
1587 l_count NUMBER;
1588
1589 BEGIN
1590 l_retcode := g_SUCCESS;
1591
1592 validate_user_id (p_user_id);
1593 validate_resp_id (p_user_id, p_resp_id, l_application_id);
1594 validate_po_header_id (p_user_id, p_resp_id, p_po_header_id, l_po_num, l_org_id, l_ledger_id);
1595 validate_tar_number (p_tar_number);
1596 validate_bug_number (p_bug_number);
1597
1598 /*ENABLE DEBUGGIN AUTOMATICALLY */
1599 IF (l_retcode = g_SUCCESS) THEN
1600 fnd_profile.put ('AFLOG_ENABLED', 'Y');
1601 fnd_profile.put ('AFLOG_LEVEL', '1');
1602 fnd_profile.put ('AFLOG_MODULE', '%');
1603 g_debug_level := 1;
1604 END IF;
1605
1606 SELECT fnd_log_messages_s.nextval
1607 INTO l_start_log_id
1608 FROM DUAL;
1609
1610 IF (l_retcode = g_SUCCESS) THEN
1611 g_user_id := p_user_id;
1612 g_resp_id := p_resp_id;
1613 output ('User Id:'||p_user_id);
1614 output ('Resp Id:'||p_resp_id);
1615 output ('Po Header Id:'||p_po_header_id);
1616 output ('TAR Number:'||p_tar_number);
1617 output ('BUG Number:'||p_bug_number);
1618 fnd_global.apps_initialize(g_user_id, g_resp_id, l_application_id);
1619 mo_global.init('PO');
1620
1621 l_count := 0;
1622 FOR xla_rec IN (SELECT e.event_id,
1623 e.entity_id
1624 FROM xla_transaction_entities t,
1625 xla_events e
1626 WHERE t.application_id = l_application_id
1627 AND t.entity_code = 'PURCHASE_ORDER'
1628 AND t.source_id_int_1 = p_po_header_id
1629 AND t.entity_id = e.entity_id
1630 AND e.event_status_code = 'U') LOOP
1631 l_count := l_count + 1;
1632 l_events_tab(l_count).event_id := xla_rec.event_id;
1633 l_events_tab(l_count).entity_id := xla_rec.entity_id;
1634 END LOOP;
1635
1636 END IF;
1637
1638 IF (l_retcode = g_SUCCESS AND l_count <> 0) THEN
1639 debug_other_string(g_state_level,l_path_name, 'Calling redo_xla_accounting ' );
1640
1641 redo_xla_accounting
1642 (
1643 p_errbuf => l_errbuf,
1644 p_retcode => l_retcode,
1645 p_application_id => 201,
1646 p_events_tab => l_events_tab,
1647 p_tar_number => p_tar_number,
1648 p_bug_number => p_bug_number,
1649 p_org_id => l_org_id,
1650 p_ledger_id => l_ledger_id
1651 );
1652
1653 debug_other_string(g_state_level,l_path_name, 'redo_xla_accounting returned' );
1654 debug_other_string(g_state_level,l_path_name, 'l_errbuf='||l_errbuf );
1655 debug_other_string(g_state_level,l_path_name, 'l_retcode='||l_retcode );
1656 END IF;
1657
1658 SELECT fnd_log_messages_s.nextval
1659 INTO l_end_log_id
1660 FROM DUAL;
1661
1662 error (' ');
1663 error (' ');
1664 IF (l_retcode <> g_SUCCESS) THEN
1665 error ('ERROR: There was an error trying to Redo the accouting for the Po Header Id '||p_po_header_id);
1666 ELSE
1667 error ('Redo accounting was successful for Po Header Id: '||p_po_header_id);
1668 END IF;
1669
1670 /*
1671 error (' ');
1672 error (' ');
1673 error ('For debugging purpose use the following SQL');
1674 error ('select * from fnd_log_messages ');
1675 error ('where log_sequence between '||l_start_log_id ||' and '||l_end_log_id);
1676 error ('and audsid = '||g_audsid);
1677 error ('order by log_sequence');
1678 error (' ');
1679 error (' ');
1680 */
1681 p_start_deb_id := l_start_log_id;
1682 p_end_deb_id := l_end_log_id;
1683
1684 EXCEPTION
1685 WHEN OTHERS THEN
1686 log(l_path_name, 'EXCEPTION: '|| SQLERRM(sqlcode));
1687 l_retcode := g_FAILURE;
1688 l_errbuf := SQLERRM;
1689 error (l_errbuf);
1690 END;
1691
1692 PROCEDURE validate_user_name
1693 (
1694 p_user_name IN VARCHAR2,
1695 p_user_id OUT NOCOPY NUMBER
1696 )
1697 IS
1698 l_start_date DATE;
1699 l_end_date DATE;
1700 BEGIN
1701 IF p_user_name IS NULL THEN
1702 undo_error ('User Name not entered');
1703 app_exception.raise_exception;
1704 END IF;
1705
1706 SELECT user_id,
1707 start_date,
1708 end_date
1709 INTO p_user_id,
1710 l_start_date,
1711 l_end_date
1712 FROM fnd_user
1713 WHERE user_name = UPPER(p_user_name);
1714 IF (SYSDATE between l_start_date and NVL(l_end_date, SYSDATE)) THEN
1715 NULL;
1716 ELSE
1717 undo_error ('This is an expired user: '||p_user_name);
1718 error ('Please use a valid user.');
1719 app_exception.raise_exception;
1720 END IF;
1721 EXCEPTION
1722 WHEN NO_DATA_FOUND THEN
1723 undo_error ('No such user found: '||p_user_name);
1724 RAISE;
1725 WHEN OTHERS THEN
1726 undo_error (SQLERRM||' when trying to get user: '||p_user_name);
1727 RAISE;
1728 END;
1729
1730 PROCEDURE validate_user_id
1731 (
1732 p_user_id IN NUMBER
1733 )
1734 IS
1735 l_start_date DATE;
1736 l_end_date DATE;
1737 BEGIN
1738 IF p_user_id IS NULL THEN
1739 undo_error ('User Id not entered');
1740 app_exception.raise_exception;
1741 END IF;
1742
1743 SELECT start_date,
1744 end_date
1745 INTO l_start_date,
1746 l_end_date
1747 FROM fnd_user
1748 WHERE user_id = p_user_id;
1749 IF (SYSDATE between l_start_date and NVL(l_end_date, SYSDATE)) THEN
1750 NULL;
1751 ELSE
1752 undo_error ('This is an expired user id: '||p_user_id);
1753 error ('Please use a valid user.');
1754 app_exception.raise_exception;
1755 END IF;
1756 EXCEPTION
1757 WHEN NO_DATA_FOUND THEN
1758 undo_error ('No such user id found: '||p_user_id);
1759 RAISE;
1760 WHEN OTHERS THEN
1761 undo_error (SQLERRM||' when trying to get user id: '||p_user_id);
1762 RAISE;
1763 END;
1764
1765 PROCEDURE validate_resp_id
1766 (
1767 p_user_id IN NUMBER,
1768 p_resp_id IN NUMBER,
1769 p_appl_id IN NUMBER
1770 )
1771 IS
1772 l_resp_appl_id NUMBER;
1773 l_start_date DATE;
1774 l_end_date DATE;
1775 l_appl_name fnd_application_vl.application_name%TYPE;
1776
1777 BEGIN
1778 IF p_resp_id IS NULL THEN
1779 undo_error ('Resp Id not entered');
1780 app_exception.raise_exception;
1781 END IF;
1782
1783 BEGIN
1784 SELECT application_name
1785 INTO l_appl_name
1786 FROM fnd_application_vl
1787 WHERE application_id = p_appl_id;
1788 END;
1789
1790 BEGIN
1791 SELECT b.start_date,
1792 b.end_date
1793 INTO l_start_date,
1794 l_end_date
1795 FROM fnd_responsibility b
1796 WHERE b.responsibility_id = p_resp_id;
1797
1798 IF (SYSDATE BETWEEN l_start_date AND NVL(l_end_date, SYSDATE)) THEN
1799 NULL;
1800 ELSE
1801 undo_error ('This is an expired Responsibility '||p_resp_id);
1802 app_exception.raise_exception;
1803 END IF;
1804 EXCEPTION
1805 WHEN NO_DATA_FOUND THEN
1806 undo_error ('No such Repsonsibility exists: '||p_resp_id);
1807 RAISE;
1808 WHEN OTHERS THEN
1809 undo_error ('Getting fnd_responsibility: '||SQLERRM);
1810 RAISE;
1811 END;
1812
1813 BEGIN
1814 SELECT a.start_date,
1815 a.end_date,
1816 a.responsibility_application_id
1817 INTO l_start_date,
1818 l_end_date,
1819 l_resp_appl_id
1820 FROM fnd_user_resp_groups_direct a
1821 WHERE a.responsibility_id = p_resp_id
1822 AND a.user_id = p_user_id;
1823
1824 IF (SYSDATE BETWEEN l_start_date AND NVL(l_end_date, SYSDATE)) THEN
1825 IF (l_resp_appl_id <> p_appl_id) THEN
1826 undo_error ('The Repsonsibility is not '||l_appl_name);
1827 app_exception.raise_exception;
1828 END IF;
1829 ELSE
1830 undo_error ('User does not have access to the Repsonsibility');
1831 app_exception.raise_exception;
1832 END IF;
1833
1834 EXCEPTION
1835 WHEN NO_DATA_FOUND THEN
1836 undo_error ('User does not have access to the Repsonsibility');
1837 RAISE;
1838 WHEN OTHERS THEN
1839 undo_error('Getting fnd_user_resp_groups_direct:'||SQLERRM);
1840 RAISE;
1841 END;
1842
1843 EXCEPTION
1844 WHEN OTHERS THEN
1845 error (SQLERRM);
1846 RAISE;
1847 END;
1848
1849 PROCEDURE validate_invoice_id
1850 (
1851 p_user_id IN NUMBER,
1852 p_resp_id IN NUMBER,
1853 p_invoice_id IN NUMBER,
1854 p_invoice_num OUT NOCOPY ap_invoices_all.invoice_num%TYPE,
1855 p_org_id OUT NOCOPY NUMBER,
1856 p_ledger_id OUT NOCOPY NUMBER
1857 )
1858 IS
1859 l_ledger_name gl_ledgers.name%TYPE;
1860 BEGIN
1861 IF p_invoice_id IS NULL THEN
1862 undo_error ('Invoice Id not entered');
1863 app_exception.raise_exception;
1864 END IF;
1865
1866 fnd_global.apps_initialize(p_user_id, p_resp_id, 200);
1867 mo_global.init('SQLAP');
1868
1869 SELECT org_id,
1870 invoice_num
1871 INTO p_org_id,
1872 p_invoice_num
1873 FROM ap_invoices
1874 WHERE invoice_id = p_invoice_id;
1875
1876 mo_utils.get_ledger_info(p_org_id, p_ledger_id, l_ledger_name);
1877
1878 EXCEPTION
1879 WHEN NO_DATA_FOUND THEN
1880 undo_error ('Invalid Invoice Id or User does not have access to Invoice Id: '||p_invoice_id);
1881 RAISE;
1882 WHEN OTHERS THEN
1883 undo_error ('Selecting ap_invoices: '||SQLERRM);
1884 RAISE;
1885 END;
1886
1887 PROCEDURE validate_po_header_id
1888 (
1889 p_user_id IN NUMBER,
1890 p_resp_id IN NUMBER,
1891 p_po_header_id IN NUMBER,
1892 p_po_num OUT NOCOPY ap_invoices_all.invoice_num%TYPE,
1893 p_org_id OUT NOCOPY NUMBER,
1894 p_ledger_id OUT NOCOPY NUMBER
1895 )
1896 IS
1897 l_ledger_name gl_ledgers.name%TYPE;
1898 BEGIN
1899 IF p_po_header_id IS NULL THEN
1900 undo_error ('PO Header Id not entered');
1901 app_exception.raise_exception;
1902 END IF;
1903
1904 fnd_global.apps_initialize(p_user_id, p_resp_id, 200);
1905 mo_global.init('SQLAP');
1906
1907 SELECT org_id,
1908 segment1
1909 INTO p_org_id,
1910 p_po_num
1911 FROM po_headers_all
1912 WHERE po_header_id = p_po_header_id;
1913
1914 mo_utils.get_ledger_info(p_org_id, p_ledger_id, l_ledger_name);
1915
1916 EXCEPTION
1917 WHEN NO_DATA_FOUND THEN
1918 undo_error ('Invalid PO Header Id or User does not have access to PO Header Id: '||p_po_header_id);
1919 RAISE;
1920 WHEN OTHERS THEN
1921 undo_error ('Selecting ap_invoices: '||SQLERRM);
1922 RAISE;
1923 END;
1924
1925 PROCEDURE validate_gl_date
1926 (
1927 p_gl_date IN DATE,
1928 p_ledger_id IN NUMBER
1929 )
1930 IS
1931 l_gl_period_status gl_period_statuses.closing_status%TYPE;
1932 l_gl_period_name gl_period_statuses.period_name%TYPE;
1933 BEGIN
1934 IF p_gl_date IS NULL THEN
1935 undo_error ('GL Date is not entered');
1936 app_exception.raise_exception;
1937 END IF;
1938
1939 SELECT p.closing_status,
1940 p.period_name
1941 INTO l_gl_period_status,
1942 l_gl_period_name
1943 FROM gl_period_statuses p
1944 WHERE p.application_id = 101
1945 AND p.set_of_books_id = p_ledger_id
1946 AND p_gl_date BETWEEN p.start_date AND p.end_date
1947 AND p.adjustment_period_flag = 'N';
1948
1949 IF (l_gl_period_status <> 'O') THEN
1950 undo_error('Period '||l_gl_period_name||' is not open');
1951 error('Please Open the period or provide another GL Date');
1952 app_exception.raise_exception;
1953 END IF;
1954 EXCEPTION
1955 WHEN NO_DATA_FOUND THEN
1956 undo_error ('Could not find GL Date '||p_gl_date||' in any of the periods');
1957 RAISE;
1958 WHEN OTHERS THEN
1959 undo_error ('Selecting GL Period Information: '||SQLERRM);
1960 RAISE;
1961 END;
1962
1963 PROCEDURE validate_tar_number
1964 (
1965 p_tar_number IN VARCHAR2
1966 )
1967 IS
1968 BEGIN
1969 IF p_tar_number IS NULL THEN
1970 undo_error ('TAR Number is not Entered');
1971 app_exception.raise_exception;
1972 END IF;
1973 END;
1974
1975
1976 PROCEDURE validate_mode
1977 (
1978 p_mode IN VARCHAR2
1979 )
1980 IS
1981 BEGIN
1982 IF p_mode IS NULL or p_mode not in ( 'A', 'B') THEN
1983 undo_error ('Invalid Undo mode , Please enter A or B');
1984 app_exception.raise_exception;
1985 END IF;
1986 END;
1987
1988
1989 PROCEDURE validate_bug_number
1990 (
1991 p_bug_number IN VARCHAR2
1992 )
1993 IS
1994 BEGIN
1995 IF p_bug_number IS NULL THEN
1996 undo_error ('Bug Number is not Entered');
1997 app_exception.raise_exception;
1998 END IF;
1999 END;
2000
2001 BEGIN
2002 init;
2003 END ;