[Home] [Help]
PACKAGE BODY: APPS.IGI_DOS_FUNDS
Source
1 Package Body IGI_DOS_FUNDS AS
2 -- $Header: igidoseb.pls 120.8.12000000.7 2007/07/19 10:07:54 pshivara ship $
3
4 /* ============== FND LOG VARIABLES ================== */
5 l_debug_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL ;
6 l_state_level number := FND_LOG.LEVEL_STATEMENT ;
7 l_proc_level number := FND_LOG.LEVEL_PROCEDURE ;
8 l_event_level number := FND_LOG.LEVEL_EVENT ;
9 l_excep_level number := FND_LOG.LEVEL_EXCEPTION ;
10 l_error_level number := FND_LOG.LEVEL_ERROR ;
11 l_unexp_level number := FND_LOG.LEVEL_UNEXPECTED ;
12
13 /* =================== DEBUG_LOG_UNEXP_ERROR =================== */
14
15 Procedure Debug_log_unexp_error (P_module IN VARCHAR2,
16 P_error_type IN VARCHAR2)
17 IS
18
19 BEGIN
20
21 IF (l_unexp_level >= l_debug_level) THEN
22
23 IF (P_error_type = 'DEFAULT') THEN
24 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
25 FND_MESSAGE.SET_TOKEN('CODE',sqlcode);
26 FND_MESSAGE.SET_TOKEN('MSG',sqlerrm);
27 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igidoseb.' || P_module ,TRUE);
28 ELSIF (P_error_type = 'USER') THEN
29 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igidoseb.' || P_module ,TRUE);
30 END IF;
31
32 END IF;
33
34 END Debug_log_unexp_error;
35
36 /* =================== DEBUG_LOG_STRING =================== */
37
38 Procedure Debug_log_string (P_level IN NUMBER,
39 P_module IN VARCHAR2,
40 P_Message IN VARCHAR2)
41 IS
42
43 BEGIN
44
45 IF (P_level >= l_debug_level) THEN
46 FND_LOG.STRING(P_level, 'igi.plsql.igidoseb.' || P_module, P_message) ;
47 END IF;
48
49 END Debug_log_string;
50
51 /* Bug 6126275 start */
52
53 /* ================== INSERT_ROW ========================= */
54
55 PROCEDURE insert_row( P_packet_id NUMBER,
56 P_sob_id NUMBER,
57 P_code_combination_id NUMBER,
58 P_period_name VARCHAR2,
59 P_period_year number,
60 P_period_num number,
61 P_quarter_num number,
62 P_currency_code varchar2,
63 P_user_id varchar2,
64 P_source_amount number,
65 P_dossier_id number,
66 P_trx_id number,
67 P_encumbrance_type_id number,
68 P_budget_version_id number,
69 P_source_trx_id number ) AS
70
71 PRAGMA AUTONOMOUS_TRANSACTION;
72
73 --l_trx_id NUMBER;
74
75 l_session_id gl_bc_packets.session_id%type;
76 l_serial_id gl_bc_packets.serial_id%type;
77 l_application_id gl_bc_packets.application_id%type;
78
79
80 CURSOR get_dest (p_source_trx_id igi_dos_trx_dest.source_trx_id%TYPE)
81 IS
82 SELECT *
83 FROM igi_dos_trx_dest
84 WHERE trx_id = P_trx_id
85 AND source_trx_id = p_source_trx_id;
86
87 BEGIN
88
89
90 begin
91 select s.audsid, s.serial# into l_session_id, l_serial_id
92 from v$session s, v$process p
93 where s.paddr = p.addr
94 and s.audsid = USERENV('SESSIONID');
95 exception
96 when others then
97 raise;
98 end;
99
100
101
102 -- Bug 3627318
103 -- reversing the encumbrance for source.
104 INSERT INTO gl_bc_packets (status_code,
105 packet_id,
106 ledger_id, je_source_name,
107 je_category_name,
108 code_combination_id,
109 actual_flag,
110 period_name,
111 period_year,
112 period_num,
113 quarter_num,
114 currency_code,
115 last_update_date,
116 last_updated_by,
117 budget_version_id,
118 entered_dr,
119 entered_cr,
120 accounted_dr,
121 accounted_cr,
122 je_batch_name,
123 reference1,
124 reference2,
125 encumbrance_type_id,
126 session_id,serial_id,application_id )
127 VALUES
128 ('P',
129 P_packet_id,
130 P_sob_id,
131 'Transfer',
132 'Budget',
133 P_code_combination_id,
134 'E',
135 P_period_name,
136 P_period_year,
137 P_period_num,
138 P_quarter_num,
139 P_currency_code,
140 sysdate,
141 P_user_id,
142 null,
143 null,
144 P_source_amount,
145 null,
146 P_source_amount,
147 'Budget '||P_dossier_id,
148 P_dossier_id || '..' || /* Bug 3466463 */
149 P_trx_id, /* packet_rec.trx_id, */
150 P_source_trx_id,
151 P_encumbrance_type_id,
152 l_session_id,l_serial_id,101);
153
154 -- =============== START DEBUG LOG ================
155 Debug_log_string (l_proc_level, 'Approve.Msg27',
156 ' INSERT INTO gl_bc_packets --> ' || SQL%ROWCOUNT);
157 -- =============== END DEBUG LOG ==================
158
159 -- Bug 1635678 sekhar kappaga budget jounral for the source key ---
160 INSERT INTO gl_bc_packets
161 (status_code,
162 packet_id,
163 -- Set_of_books_id, /* Commented for bug 6126275 */
164 ledger_id, /* Added for bug 6126275 */
165 je_source_name,
166 je_category_name,
167 code_combination_id,
168 actual_flag,
169 period_name,
170 period_year,
171 period_num,
172 quarter_num,
173 currency_code,
174 last_update_date,
175 last_updated_by,
176 budget_version_id,
177 entered_dr,
178 entered_cr,
179 accounted_dr,
180 accounted_cr,
181 je_batch_name,
182 reference1,
183 reference2,
184 session_id,serial_id,application_id )
185 VALUES
186 ('P',
187 P_packet_id,
188 P_sob_id,
189 'Transfer',
190 'Budget',
191 P_code_combination_id,
192 'B',
193 P_period_name,
194 P_period_year,
195 P_period_num,
196 P_quarter_num,
197 P_currency_code,
198 sysdate,
199 P_user_id,
200 P_budget_version_id,
201 null,
202 P_source_amount,
203 null,
204 P_source_amount,
205 'Budget '||P_dossier_id,
206 P_dossier_id || '..' || /* Bug 3466463 */
207 P_trx_id, /* packet_rec.trx_id, */
208 P_source_trx_id,
209 l_session_id,l_serial_id,101);
210
211 -- =============== START DEBUG LOG ================
212 Debug_log_string (l_proc_level, 'Approve.Msg30',
213 ' INSERTED --> ' || SQL%ROWCOUNT);
214 Debug_log_string (l_proc_level, 'Approve.Msg31',
215 ' INSERTING INTO gl_bc_packets for budget');
216 Debug_log_string (l_proc_level, 'Approve.Msg32',
217 ' CREDIT SOURCE AMOUNT --> ' || P_source_amount);
218 -- =============== END DEBUG LOG ==================
219
220
221 -- Bug 1635678 sekhar kappaga budget jounral for the destination key start ---
222 FOR dest_rec IN get_dest(P_source_trx_id)
223 LOOP
224
225 -- =============== START DEBUG LOG ================
226 Debug_log_string (l_proc_level, 'Approve.Msg34',
227 ' In to dest_rec loop ');
228 Debug_log_string (l_proc_level, 'Approve.Msg35',
229 ' INSERTING INTO gl_bc_packets for budget');
230 Debug_log_string (l_proc_level, 'Approve.Msg36',
231 ' DEBIT DEST AMOUNT --> ' || dest_rec.budget_amount);
232 -- =============== END DEBUG LOG ==================
233
234 INSERT INTO gl_bc_packets
235 (status_code,
236 packet_id,
237 ledger_id, /* Added for bug 6126275 */
238 je_source_name,
239 je_category_name,
240 code_combination_id,
241 actual_flag,
242 period_name,
243 period_year,
244 period_num,
245 quarter_num,
246 currency_code,
247 last_update_date,
248 last_updated_by,
249 budget_version_id,
250 entered_dr,
251 entered_cr,
252 accounted_dr,
253 accounted_cr,
254 je_batch_name,
255 reference1,
256 reference2,
257 session_id,serial_id,application_id )
258 VALUES ('P',
259 P_packet_id,
260 dest_rec.sob_id,
261 'Transfer',
262 'Budget',
263 dest_rec.code_combination_id,
264 'B',
265 dest_rec.period_name,
266 dest_rec.period_year,
267 dest_rec.period_num,
268 dest_rec.quarter_num,
269 P_currency_code,
270 sysdate,
271 P_user_id,
272 dest_rec.budget_version_id,
273 dest_rec.budget_amount,
274 null,
275 dest_rec.budget_amount,
276 null,
277 'Dossier ' ||ltrim(to_char(dest_rec.dossier_id))|| ' ' || dest_rec.period_name,
278 to_char(dest_rec.dossier_id) || '..' || dest_rec.trx_id,
279 dest_rec.dest_trx_id,
280 l_session_id,l_serial_id,101);
281
282
283
284
285
286 -- =============== START DEBUG LOG ================
287 Debug_log_string (l_proc_level, 'Approve.Msg37',
288 ' INSERTED --> ' || SQL%ROWCOUNT);
289 -- =============== END DEBUG LOG ==================
290
291 END LOOP;
292
293 COMMIT;
294
295 END insert_row;
296
297 /* =================== REJECT_INSERT_ROW ======================= */
298
299 PROCEDURE reject_insert_row ( P_packet_id NUMBER,
300 P_sob_id NUMBER,
301 P_code_combination_id NUMBER,
302 P_period_name VARCHAR2,
303 P_period_year number,
304 P_period_num number,
305 P_quarter_num number,
306 P_currency_code varchar2,
307 P_user_id number,
308 P_source_amount number,
309 P_dossier_id number,
310 P_trx_id number,
311 P_encumbrance_type_id number,
312 P_source_trx_id number ) AS
313
314 PRAGMA AUTONOMOUS_TRANSACTION;
315
316 l_session_id gl_bc_packets.session_id%type;
317 l_serial_id gl_bc_packets.serial_id%type;
318 l_application_id gl_bc_packets.application_id%type;
319
320
321 BEGIN
322
323 begin
324 select s.audsid, s.serial# into l_session_id, l_serial_id
325 from v$session s, v$process p
326 where s.paddr = p.addr
327 and s.audsid = USERENV('SESSIONID');
328 exception
329 when others then
330 raise;
331 end;
332
333
334 -- Bug 3627318
335 -- reversing the encumbrance for source.
336 INSERT INTO gl_bc_packets (status_code,
337 packet_id,
338 ledger_id,
339 je_source_name,
340 je_category_name,
341 code_combination_id,
342 actual_flag,
343 period_name,
344 period_year,
345 period_num,
346 quarter_num,
347 currency_code,
348 last_update_date,
349 last_updated_by,
350 budget_version_id,
351 entered_dr,
352 entered_cr,
353 accounted_dr,
354 accounted_cr,
355 je_batch_name,
356 reference1,
357 reference2,
358 encumbrance_type_id,
359 session_id,serial_id,application_id)
360 VALUES ('P',
361 P_packet_id,
362 P_sob_id,
363 'Transfer',
364 'Budget',
365 P_code_combination_id,
366 'E',
367 P_period_name,
368 P_period_year,
369 P_period_num,
370 P_quarter_num,
371 P_currency_code,
372 sysdate,
373 P_user_id,
374 null,
375 null,
376 P_source_amount,
377 null,
378 P_source_amount,
379 'Budget '||P_dossier_id,
380 P_dossier_id || '..' || P_trx_id, P_source_trx_id,
381 P_encumbrance_type_id,
382 l_session_id,l_serial_id,101);
383
384 -- =============== START DEBUG LOG ================
385 Debug_log_string (l_proc_level, 'Reject.Msg31',
386 ' INSERT INTO gl_bc_packets --> ' || SQL%ROWCOUNT);
387 -- =============== END DEBUG LOG ==================
388
389 COMMIT;
390
391 END reject_insert_row;
392
393 /* end bug 6126275 */
394
395
396
397 /* =================== APPROVE =================== */
398
399
400 FUNCTION APPROVE ( p_trx_number IN VARCHAR2,
401 p_user_id IN VARCHAR2,
402 p_responsibility_id IN VARCHAR2,
403 p_sob_id IN VARCHAR2)
404 RETURN BOOLEAN
405 IS
406
407 user_transfer VARCHAR2(30);
408 user_budget VARCHAR2(30);
409 effective_date_rule_code VARCHAR2(2);
410 frozen_source_flag VARCHAR2(2);
411 approval_flag VARCHAR2(2);
412 l_packet_id NUMBER;
413 v_packet_id NUMBER;
414 l_return_code VARCHAR2(30);
415 v_source_amount NUMBER;
416 reversal_option_code VARCHAR2(2);
417 v_encumbrance_type_id NUMBER;
418
419 -- Bug 1635678 sekhar kappaga.
420 l_currency_code VARCHAR2(15);
421
422 l_trx_id NUMBER;
423 l_user_id VARCHAR2(30);
424 l_responsibility_id VARCHAR2(30);
425 l_resp_appl_id VARCHAR2(30);
426 temp VARCHAR2(30);
427 l_sob_id VARCHAR2(30);
428
429
430 CURSOR get_trx_id
431 IS
432 SELECT trx_id
433 FROM igi_dos_trx_headers
434 WHERE trx_number = P_TRX_NUMBER;
435
436 CURSOR get_packets
437 IS
438 SELECT *
439 FROM igi_dos_trx_sources
440 WHERE trx_id = l_trx_id;
441
442 -- Bug 1635678 sekhar kappaga
443 CURSOR get_dest (p_source_trx_id igi_dos_trx_dest.source_trx_id%TYPE)
444 IS
445 SELECT *
446 FROM igi_dos_trx_dest
447 WHERE trx_id = l_trx_id
448 AND source_trx_id = p_source_trx_id;
449
450 CURSOR get_currency_code
451 IS
452 SELECT currency_code
453 FROM gl_sets_of_books
454 WHERE set_of_books_id = l_sob_id;
455
456 BEGIN
457
458 -- =============== START DEBUG LOG ================
459 Debug_log_string (l_proc_level, 'Approve.Msg1',
460 ' ** BEGIN APPROVE ** ');
461 -- =============== END DEBUG LOG ==================
462
463 l_sob_id := p_sob_id;
464
465 -- =============== START DEBUG LOG ================
466 Debug_log_string (l_proc_level, 'Approve.Msg2.1',
467 ' l_sob_id --> ' || l_sob_id);
468 -- =============== END DEBUG LOG ==================
469
470
471 -- Bug 1635678 sekhar kappaga.
472 OPEN get_currency_code;
473 FETCH get_currency_code into l_currency_code;
474 CLOSE get_currency_code;
475
476 -- =============== START DEBUG LOG ================
477 Debug_log_string (l_proc_level, 'Approve.Msg2.2',
478 ' l_currency_code --> ' || l_currency_code);
479 -- =============== END DEBUG LOG ==================
480
481 OPEN get_trx_id;
482 FETCH get_trx_id into l_trx_id;
483 CLOSE get_trx_id;
484
485 -- =============== START DEBUG LOG ================
486 Debug_log_string (l_proc_level, 'Approve.Msg3',
487 ' l_trx_id --> ' || l_trx_id);
488 -- =============== END DEBUG LOG ==================
489
490 l_user_id := fnd_global.user_id;
491
492 -- =============== START DEBUG LOG ================
493 Debug_log_string (l_proc_level, 'Approve.Msg4',
494 ' l_user_id --> ' || l_user_id);
495 -- =============== END DEBUG LOG ==================
496
497 l_responsibility_id := fnd_global.resp_id;
498
499 -- =============== START DEBUG LOG ================
500 Debug_log_string (l_proc_level, 'Approve.Msg4.1',
501 ' l_responsibility_id --> ' || l_responsibility_id);
502 -- =============== END DEBUG LOG ==================
503
504 l_resp_appl_id := fnd_global.resp_appl_id;
505
506 -- =============== START DEBUG LOG ================
507 Debug_log_string (l_proc_level, 'Approve.Msg4.1',
508 ' l_resp_appl_id --> ' || l_resp_appl_id);
509 -- =============== END DEBUG LOG ==================
510
511 -- Get the translation of Transfer
512 temp := 'Transfer';
513
514 -- =============== START DEBUG LOG ================
515 Debug_log_string (l_proc_level, 'Approve.Msg5',
516 ' CALLING GL_JE_SOURCES_PKG.select_columns ');
517 Debug_log_string (l_proc_level, 'Approve.Msg6',
518 ' temp --> '|| temp);
519 Debug_log_string (l_proc_level, 'Approve.Msg7',
520 ' user_transfer --> ' || user_transfer);
521 Debug_log_string (l_proc_level, 'Approve.Msg8',
522 ' effective_date_rule_code --> ' || effective_date_rule_code);
523 Debug_log_string (l_proc_level, 'Approve.Msg9',
524 ' frozen_source_flag --> ' || frozen_source_flag);
525 Debug_log_string (l_proc_level, 'Approve.Msg10',
526 ' approval_flag --> ' || approval_flag);
527 -- =============== END DEBUG LOG ==================
528
529 GL_JE_SOURCES_PKG.select_columns( X_JE_SOURCE_NAME => temp,
530 X_USER_JE_SOURCE_NAME => user_transfer,
531 X_EFFECTIVE_DATE_RULE_CODE => effective_date_rule_code,
532 X_FROZEN_SOURCE_FLAG => frozen_source_flag,
533 X_JOURNAL_APPROVAL_FLAG => approval_flag);
534
535 -- =============== START DEBUG LOG ================
536 Debug_log_string (l_proc_level, 'Approve.Msg11',
537 ' AFTER GL_JE_SOURCES_PKG.select_columns ');
538 Debug_log_string (l_proc_level, 'Approve.Msg12',
539 ' temp --> '|| temp);
540 Debug_log_string (l_proc_level, 'Approve.Msg13',
541 ' user_transfer --> ' || user_transfer);
542 Debug_log_string (l_proc_level, 'Approve.Msg14',
543 ' effective_date_rule_code --> ' || effective_date_rule_code);
544 Debug_log_string (l_proc_level, 'Approve.Msg15',
545 ' frozen_source_flag --> ' || frozen_source_flag);
546 Debug_log_string (l_proc_level, 'Approve.Msg16',
547 ' approval_flag --> ' || approval_flag);
548 -- =============== END DEBUG LOG ==================
549
550 -- Get the translation of Budget
551 temp := 'Budget';
552
553 -- =============== START DEBUG LOG ================
554 Debug_log_string (l_proc_level, 'Approve.Msg17',
555 ' CALLING GL_JE_CATEGORIES_PKG.select_columns ');
556 Debug_log_string (l_proc_level, 'Approve.Msg18',
557 ' temp --> '|| temp);
558 Debug_log_string (l_proc_level, 'Approve.Msg19',
559 ' user_budget --> ' || user_budget);
560 -- =============== END DEBUG LOG ==================
561
562 GL_JE_CATEGORIES_PKG.select_columns ( X_JE_CATEGORY_NAME => temp,
563 X_USER_JE_CATEGORY_NAME => user_budget);
564
565
566 -- =============== START DEBUG LOG ================
567 Debug_log_string (l_proc_level, 'Approve.Msg20',
568 ' AFTER GL_JE_CATEGORIES_PKG.select_columns ');
569 Debug_log_string (l_proc_level, 'Approve.Msg21',
570 ' temp --> '|| temp);
571 Debug_log_string (l_proc_level, 'Approve.Msg23',
572 ' user_budget --> ' || user_budget);
573 -- =============== END DEBUG LOG ==================
574
575 --get the encumbrance id
576 SELECT encumbrance_type_id
577 INTO v_encumbrance_type_id
578 FROM gl_encumbrance_types
579 WHERE upper(encumbrance_type) = 'DOSSIER' ;
580
581 -- =============== START DEBUG LOG ================
582 Debug_log_string (l_proc_level, 'Approve.Msg24',
583 ' v_encumbrance_type_id --> ' || v_encumbrance_type_id);
584 -- =============== END DEBUG LOG ==================
585
586 FOR packet_rec IN get_packets
587 LOOP
588
589 l_packet_id :=packet_rec.group_id;
590
591 -- =============== START DEBUG LOG ================
592 Debug_log_string (l_proc_level, 'Approve.Msg25',
593 ' l_packet_id --> ' || l_packet_id);
594 -- =============== END DEBUG LOG ==================
595
596 SELECT gl_bc_packets_s.nextval
597 INTO v_packet_id
598 FROM dual;
599
600 -- =============== START DEBUG LOG ================
601 Debug_log_string (l_proc_level, 'Approve.Msg26',
602 ' v_packet_id --> ' || v_packet_id);
603 -- =============== END DEBUG LOG ==================
604
605 --get the total amount for the source budget journal
606 SELECT nvl(sum(dest.budget_amount),0)
607 INTO v_source_amount
608 FROM igi_dos_trx_dest dest
609 WHERE dest.source_trx_id = packet_rec.source_trx_id;
610
611 -- =============== START DEBUG LOG ================
612 Debug_log_string (l_proc_level, 'Approve.Msg35',
613 ' INSERTING INTO gl_bc_packets for source enc ');
614 Debug_log_string (l_proc_level, 'Approve.Msg36',
615 ' CREDIT SOURCE AMOUNT --> ' || v_source_amount );
616 -- =============== END DEBUG LOG ==================
617 /* Commented for bug 6126275 */
618 -- Bug 3627318
619 -- reversing the encumbrance for source.
620 /* INSERT INTO gl_bc_packets (status_code,
621 packet_id,
622 -- Set_of_books_id, -- Commented for bug 6126275
623 ledger_id, -- Added for bug 6126275
624 je_source_name,
625 je_category_name,
626 code_combination_id,
627 actual_flag,
628 period_name,
629 period_year,
630 period_num,
631 quarter_num,
632 currency_code,
633 last_update_date,
634 last_updated_by,
635 budget_version_id,
636 entered_dr,
637 entered_cr,
638 accounted_dr,
639 accounted_cr,
640 je_batch_name,
641 reference1,
642 reference2,
643 encumbrance_type_id )
644 VALUES
645 ('P',
646 v_packet_id,
647 packet_rec.sob_id,
648 'Transfer',
649 'Budget',
650 packet_rec.code_combination_id,
651 'E',
652 packet_rec.period_name,
653 packet_rec.period_year,
654 packet_rec.period_num,
655 packet_rec.quarter_num,
656 l_currency_code,
657 sysdate,
658 l_user_id,
659 null,
660 null,
661 v_source_amount,
662 null,
663 v_source_amount,
664 'Budget '||packet_rec.dossier_id,
665 packet_rec.dossier_id || '..' || -- Bug 3466463
666 packet_rec.trx_id, -- packet_rec.trx_id,
667 packet_rec.source_trx_id,
668 v_encumbrance_type_id);
669
670 -- =============== START DEBUG LOG ================
671 Debug_log_string (l_proc_level, 'Approve.Msg27',
672 ' INSERT INTO gl_bc_packets --> ' || SQL%ROWCOUNT);
673 -- =============== END DEBUG LOG ==================
674
675 -- Bug 1635678 sekhar kappaga budget jounral for the source key ---
676 INSERT INTO gl_bc_packets
677 (status_code,
678 packet_id,
679 -- Set_of_books_id, -- Commented for bug 6126275
680 ledger_id, -- Added for bug 6126275
681 je_source_name,
682 je_category_name,
683 code_combination_id,
684 actual_flag,
685 period_name,
686 period_year,
687 period_num,
688 quarter_num,
689 currency_code,
690 last_update_date,
691 last_updated_by,
692 budget_version_id,
693 entered_dr,
694 entered_cr,
695 accounted_dr,
696 accounted_cr,
697 je_batch_name,
698 reference1,
699 reference2 )
700 VALUES
701 ('P',
702 v_packet_id,
703 packet_rec.sob_id,
704 'Transfer',
705 'Budget',
706 packet_rec.code_combination_id,
707 'B',
708 packet_rec.period_name,
709 packet_rec.period_year,
710 packet_rec.period_num,
711 packet_rec.quarter_num,
712 l_currency_code,
713 sysdate,
714 l_user_id,
715 packet_rec.budget_version_id,
716 null,
717 v_source_amount,
718 null,
719 v_source_amount,
720 'Budget '||packet_rec.dossier_id,
721 packet_rec.dossier_id || '..' || -- Bug 3466463
722 packet_rec.trx_id, -- packet_rec.trx_id,
723 packet_rec.source_trx_id);
724
725 -- =============== START DEBUG LOG ================
726 Debug_log_string (l_proc_level, 'Approve.Msg30',
727 ' INSERTED --> ' || SQL%ROWCOUNT);
728 Debug_log_string (l_proc_level, 'Approve.Msg31',
729 ' INSERTING INTO gl_bc_packets for budget');
730 Debug_log_string (l_proc_level, 'Approve.Msg32',
731 ' CREDIT SOURCE AMOUNT --> ' || v_source_amount);
732 -- =============== END DEBUG LOG ==================
733
734
735 -- Bug 1635678 sekhar kappaga budget jounral for the destination key start ---
736 FOR dest_rec IN get_dest(packet_rec.source_trx_id)
737 LOOP
738
739 -- =============== START DEBUG LOG ================
740 Debug_log_string (l_proc_level, 'Approve.Msg34',
741 ' In to dest_rec loop ');
742 Debug_log_string (l_proc_level, 'Approve.Msg35',
743 ' INSERTING INTO gl_bc_packets for budget');
744 Debug_log_string (l_proc_level, 'Approve.Msg36',
745 ' DEBIT DEST AMOUNT --> ' || dest_rec.budget_amount);
746 -- =============== END DEBUG LOG ==================
747
748 INSERT INTO gl_bc_packets
749 (status_code,
750 packet_id,
751 -- Set_of_books_id, -- Commented for bug 6126275
752 ledger_id, -- Added for bug 6126275
753 je_source_name,
754 je_category_name,
755 code_combination_id,
756 actual_flag,
757 period_name,
758 period_year,
759 period_num,
760 quarter_num,
761 currency_code,
762 last_update_date,
763 last_updated_by,
764 budget_version_id,
765 entered_dr,
766 entered_cr,
767 accounted_dr,
768 accounted_cr,
769 je_batch_name,
770 reference1,
771 reference2 )
772 VALUES ('P',
773 v_packet_id,
774 dest_rec.sob_id,
775 'Transfer',
776 'Budget',
777 dest_rec.code_combination_id,
778 'B',
779 dest_rec.period_name,
780 dest_rec.period_year,
781 dest_rec.period_num,
782 dest_rec.quarter_num,
783 l_currency_code,
784 sysdate,
785 l_user_id,
786 dest_rec.budget_version_id,
787 dest_rec.budget_amount,
788 null,
789 dest_rec.budget_amount,
790 null,
791 'Dossier ' ||ltrim(to_char(dest_rec.dossier_id))
792 || ' ' || dest_rec.period_name,
793 to_char(dest_rec.dossier_id) || '..' || -- Bug 3466463
794 dest_rec.trx_id, -- ltrim(to_char(dest_rec.dossier_id)),
795 dest_rec.dest_trx_id); -- 'New Budget Tranfer Row' );
796
797
798
799
800
801 -- =============== START DEBUG LOG ================
802 Debug_log_string (l_proc_level, 'Approve.Msg37',
803 ' INSERTED --> ' || SQL%ROWCOUNT);
804 -- =============== END DEBUG LOG ==================
805
806 END LOOP; */
807
808
809 /* bug 6126175 start */
810 -- AUTONOMOUS procedure
811
812 insert_row (v_packet_id,
813 packet_rec.sob_id,
814 packet_rec.code_combination_id,
815 packet_rec.period_name,
816 packet_rec.period_year,
817 packet_rec.period_num,
818 packet_rec.quarter_num,
819 l_currency_code,
820 l_user_id,
821 v_source_amount,
822 packet_rec.dossier_id,
823 packet_rec.trx_id,
824 v_encumbrance_type_id,
825 packet_rec.budget_version_id,
826 packet_rec.source_trx_id );
827 /* end bug 6126275 */
828
829 -- =============== START DEBUG LOG ================
830 Debug_log_string (l_proc_level, 'Approve.Msg38',
831 ' CALLING GL_FUNDS_CHECKER_PKG.glxfck ');
832 Debug_log_string (l_proc_level, 'Approve.Msg39',
833 ' P_SOBID --> ' || l_sob_id);
834 Debug_log_string (l_proc_level, 'Approve.Msg40',
835 ' P_PACKETID --> ' || v_packet_id);
836 Debug_log_string (l_proc_level, 'Approve.Msg41',
837 ' P_MODE --> R');
838 Debug_log_string (l_proc_level, 'Approve.Msg42',
839 ' P_PARTIAL_RESV_FLAG --> N ');
840 Debug_log_string (l_proc_level, 'Approve.Msg43',
841 ' P_OVERRIDE --> Y ');
842 Debug_log_string (l_proc_level, 'Approve.Msg44',
843 ' P_CONC_FLAG --> N ');
844 Debug_log_string (l_proc_level, 'Approve.Msg45',
845 ' P_USER_ID --> ' || l_user_id);
846 Debug_log_string (l_proc_level, 'Approve.Msg46',
847 ' P_USER_RESP_ID --> ' || l_responsibility_id);
848 -- =============== END DEBUG LOG ==================
849
850 -- Bug 1635678 sekhar kappaga budget jounral for the destination key end ---
851 /* Commented GL_FUNDS_CHECKER code & added PSA_FUNDS_CHECKER_PKG for bug 6126275 */
852 /* IF GL_FUNDS_CHECKER_PKG.glxfck ( P_SOBID => l_sob_id ,
853 P_PACKETID => v_packet_id ,
854 P_MODE => 'R',
855 P_PARTIAL_RESV_FLAG => 'N',
856 P_OVERRIDE => 'Y',
857 P_CONC_FLAG => 'N',
858 P_USER_ID => l_user_id,
859 P_USER_RESP_ID => l_responsibility_id,
860 P_RETURN_CODE => l_return_code ) */
861
862 IF PSA_FUNDS_CHECKER_PKG.glxfck ( p_ledgerid => l_sob_id ,
863 P_PACKETID => v_packet_id ,
864 P_MODE => 'R',
865 P_OVERRIDE => 'Y',
866 P_CONC_FLAG => 'N',
867 P_USER_ID => l_user_id,
868 P_USER_RESP_ID => l_responsibility_id,
869 P_CALLING_PROG_FLAG => 'G',
870 P_RETURN_CODE => l_return_code )
871 THEN
872
873 NULL;
874 -- =============== START DEBUG LOG ================
875 Debug_log_string (l_proc_level, 'Approve.Msg48',
876 ' GL_FUNDS_CHECKER_PKG.glxfck --> TRUE');
877 -- =============== END DEBUG LOG ==================
878 ELSE
879 -- =============== START DEBUG LOG ================
880 Debug_log_string (l_proc_level, 'Approve.Msg49',
881 ' GL_FUNDS_CHECKER_PKG.glxfck --> FALSE');
882 -- =============== END DEBUG LOG ==================
883 END IF;
884
885 -- =============== START DEBUG LOG ================
886 Debug_log_string (l_proc_level, 'Approve.Msg50',
887 ' P_RETURN_CODE --> ' || l_return_code);
888 -- =============== END DEBUG LOG ==================
889
890 END LOOP;
891
892 -- =============== START DEBUG LOG ================
893 Debug_log_string (l_proc_level, 'Approve.Msg51',
894 ' RETURN TRUE ');
895 Debug_log_string (l_proc_level, 'Approve.Msg52',
896 ' ** END APPROVE ** ');
897 -- =============== END DEBUG LOG ==================
898
899 RETURN TRUE;
900
901 EXCEPTION
902 WHEN OTHERS THEN
903 -- =============== START DEBUG LOG ================
904 DEBUG_LOG_UNEXP_ERROR ('Approve.unexp1','DEFAULT');
905 -- =============== END DEBUG LOG ==================
906 RETURN FALSE;
907 END Approve;
908
909
910 /* ============================ REJECT ======================= */
911
912 FUNCTION Reject ( p_trx_number IN VARCHAR2,
913 p_user_id IN VARCHAR2,
914 p_responsibility_id IN VARCHAR2,
915 p_sob_id IN VARCHAR2)
916 RETURN BOOLEAN
917 IS
918
919 l_trx_id NUMBER;
920 l_packet_id NUMBER;
921 v_packet_id NUMBER;
922 l_return_code VARCHAR2(30);
923 l_user_id VARCHAR2(30) :=p_user_id;
924 l_responsibility_id VARCHAR2(30) :=p_responsibility_id ;
925 l_sob_id VARCHAR2(30) :=p_sob_id ;
926 v_source_amount NUMBER;
927 user_transfer VARCHAR2(30);
928 user_budget VARCHAR2(30);
929 temp VARCHAR2(30);
930 effective_date_rule_code VARCHAR2(2);
931 frozen_source_flag VARCHAR2(2);
932 reversal_option_code VARCHAR2(2);
933 approval_flag VARCHAR2(2);
934 v_user_id NUMBER(15);
935 v_encumbrance_type_id NUMBER;
936 l_currency_code VARCHAR2(15);
937
938 -- Bug 1635678 sekhar kappaga
939 CURSOR get_dest (p_source_trx_id igi_dos_trx_dest.source_trx_id%TYPE)
940 IS
941 SELECT *
942 FROM igi_dos_trx_dest
943 WHERE trx_id = l_trx_id
944 AND source_trx_id = p_source_trx_id;
945
946
947 CURSOR get_trx_id
948 IS
949 SELECT trx_id
950 FROM igi_dos_trx_headers
951 WHERE trx_number = p_trx_NUMBER;
952
953 CURSOR get_packets
954 IS
955 SELECT *
956 FROM igi_dos_trx_sources
957 WHERE trx_id = l_trx_id;
958
959 CURSOR get_currency_code
960 IS
961 SELECT currency_code
962 FROM gl_sets_of_books
963 WHERE set_of_books_id = l_sob_id;
964
965
966 BEGIN
967
968 -- =============== START DEBUG LOG ================
969 Debug_log_string (l_proc_level, 'Reject.Msg1',
970 ' ** BEGIN REJECT ** ');
971 -- =============== END DEBUG LOG ==================
972
973 OPEN get_currency_code;
974 FETCH get_currency_code into l_currency_code;
975 CLOSE get_currency_code;
976
977 -- =============== START DEBUG LOG ================
978 Debug_log_string (l_proc_level, 'Reject.Msg2',
979 ' l_currency_code --> ' || l_currency_code);
980 -- =============== END DEBUG LOG ==================
981
982 OPEN get_trx_id;
983 FETCH get_trx_id into l_trx_id;
984 CLOSE get_trx_id;
985
986 -- =============== START DEBUG LOG ================
987 Debug_log_string (l_proc_level, 'Reject.Msg3',
988 ' l_trx_id --> ' || l_trx_id);
989 -- =============== END DEBUG LOG ==================
990
991 v_user_id := fnd_global.user_id;
992
993 -- =============== START DEBUG LOG ================
994 Debug_log_string (l_proc_level, 'Reject.Msg4',
995 ' v_user_id --> ' || v_user_id);
996 -- =============== END DEBUG LOG ==================
997
998 l_responsibility_id := fnd_global.resp_id;
999
1000 -- =============== START DEBUG LOG ================
1001 Debug_log_string (l_proc_level, 'Reject.Msg4.1',
1002 ' l_responsibility_id --> ' || l_responsibility_id);
1003 -- =============== END DEBUG LOG ==================
1004
1005 -- Get the translation of Transfer
1006 temp := 'Transfer';
1007
1008 -- =============== START DEBUG LOG ================
1009 Debug_log_string (l_proc_level, 'Reject.Msg5',
1010 ' CALLING GL_JE_SOURCES_PKG.select_columns ');
1011 Debug_log_string (l_proc_level, 'Reject.Msg6',
1012 ' temp --> '|| temp);
1013 Debug_log_string (l_proc_level, 'Reject.Msg7',
1014 ' user_transfer --> ' || user_transfer);
1015 Debug_log_string (l_proc_level, 'Reject.Msg8',
1016 ' effective_date_rule_code --> ' || effective_date_rule_code);
1017 Debug_log_string (l_proc_level, 'Reject.Msg9',
1018 ' frozen_source_flag --> ' || frozen_source_flag);
1019 Debug_log_string (l_proc_level, 'Reject.Msg10',
1020 ' approval_flag --> ' || approval_flag);
1021 -- =============== END DEBUG LOG ==================
1022
1023 GL_JE_SOURCES_PKG.select_columns( X_JE_SOURCE_NAME => temp,
1024 X_USER_JE_SOURCE_NAME => user_transfer,
1025 X_EFFECTIVE_DATE_RULE_CODE => effective_date_rule_code,
1026 X_FROZEN_SOURCE_FLAG => frozen_source_flag,
1027 X_JOURNAL_APPROVAL_FLAG => approval_flag);
1028
1029 -- =============== START DEBUG LOG ================
1030 Debug_log_string (l_proc_level, 'Reject.Msg11',
1031 ' AFTER GL_JE_SOURCES_PKG.select_columns ');
1032 Debug_log_string (l_proc_level, 'Reject.Msg12',
1033 ' temp --> '|| temp);
1034 Debug_log_string (l_proc_level, 'Reject.Msg13',
1035 ' user_transfer --> ' || user_transfer);
1036 Debug_log_string (l_proc_level, 'Reject.Msg14',
1037 ' effective_date_rule_code --> ' || effective_date_rule_code);
1038 Debug_log_string (l_proc_level, 'Reject.Msg15',
1039 ' frozen_source_flag --> ' || frozen_source_flag);
1040 Debug_log_string (l_proc_level, 'Reject.Msg16',
1041 ' approval_flag --> ' || approval_flag);
1042 -- =============== END DEBUG LOG ==================
1043
1044 -- Get the translation of Budget
1045 temp := 'Budget';
1046
1047 -- =============== START DEBUG LOG ================
1048 Debug_log_string (l_proc_level, 'Reject.Msg17',
1049 ' CALLING GL_JE_CATEGORIES_PKG.select_columns ');
1050 Debug_log_string (l_proc_level, 'Reject.Msg18',
1051 ' temp --> '|| temp);
1052 Debug_log_string (l_proc_level, 'Reject.Msg19',
1053 ' user_budget --> ' || user_budget);
1054 -- =============== END DEBUG LOG ==================
1055
1056 GL_JE_CATEGORIES_PKG.select_columns ( X_JE_CATEGORY_NAME => temp,
1057 X_USER_JE_CATEGORY_NAME => user_budget);
1058
1059
1060 -- =============== START DEBUG LOG ================
1061 Debug_log_string (l_proc_level, 'Reject.Msg20',
1062 ' AFTER GL_JE_CATEGORIES_PKG.select_columns ');
1063 Debug_log_string (l_proc_level, 'Reject.Msg21',
1064 ' temp --> '|| temp);
1065 Debug_log_string (l_proc_level, 'Reject.Msg23',
1066 ' user_budget --> ' || user_budget);
1067 -- =============== END DEBUG LOG ==================
1068
1069 --get the encumbrance id
1070 SELECT encumbrance_type_id
1071 INTO v_encumbrance_type_id
1072 FROM gl_encumbrance_types
1073 WHERE upper(encumbrance_type) = 'DOSSIER' ;
1074
1075 -- =============== START DEBUG LOG ================
1076 Debug_log_string (l_proc_level, 'Reject.Msg24',
1077 ' v_encumbrance_type_id --> ' || v_encumbrance_type_id);
1078 -- =============== END DEBUG LOG ==================
1079
1080
1081 FOR packet_rec IN get_packets
1082 LOOP
1083
1084 -- =============== START DEBUG LOG ================
1085 Debug_log_string (l_proc_level, 'Reject.Msg25',
1086 ' INTO get packest cursor');
1087 -- =============== END DEBUG LOG ==================
1088
1089 l_packet_id := packet_rec.group_id;
1090
1091 -- =============== START DEBUG LOG ================
1092 Debug_log_string (l_proc_level, 'Reject.Msg26',
1093 ' l_packet_id --> '|| l_packet_id);
1094 -- =============== END DEBUG LOG ==================
1095
1096 SELECT gl_bc_packets_s.nextval
1097 INTO v_packet_id
1098 FROM dual;
1099
1100 -- =============== START DEBUG LOG ================
1101 Debug_log_string (l_proc_level, 'Reject.Msg27',
1102 ' v_packet_id --> '|| v_packet_id);
1103 -- =============== END DEBUG LOG ==================
1104
1105
1106 SELECT nvl(sum(dest.budget_amount),0)
1107 INTO v_source_amount
1108 FROM igi_dos_trx_dest dest
1109 WHERE dest.source_trx_id = packet_rec.source_trx_id;
1110
1111 -- =============== START DEBUG LOG ================
1112 Debug_log_string (l_proc_level, 'Reject.Msg28',
1113 ' v_source_amount --> ' || v_source_amount);
1114 Debug_log_string (l_proc_level, 'Reject.Msg29',
1115 ' INSERTING INTO gl_bc_packets ');
1116 Debug_log_string (l_proc_level, 'Reject.Msg30',
1117 ' CREDIT SOURCE AMOUNT --> ' || v_source_amount);
1118 -- =============== END DEBUG LOG ==================
1119 /* Commented for bug 6126275 */
1120 /*
1121 -- Bug 3627318
1122 -- reversing the encumbrance for source.
1123 INSERT INTO gl_bc_packets (status_code,
1124 packet_id,
1125 -- Set_of_books_id, -- Commented for bug 6126275
1126 ledger_id, --Added for bug 6126275
1127 je_source_name,
1128 je_category_name,
1129 code_combination_id,
1130 actual_flag,
1131 period_name,
1132 period_year,
1133 period_num,
1134 quarter_num,
1135 currency_code,
1136 last_update_date,
1137 last_updated_by,
1138 budget_version_id,
1139 entered_dr,
1140 entered_cr,
1141 accounted_dr,
1142 accounted_cr,
1143 je_batch_name,
1144 reference1,
1145 reference2,
1146 encumbrance_type_id)
1147 VALUES
1148 ('P',
1149 v_packet_id,
1150 packet_rec.sob_id,
1151 'Transfer',
1152 'Budget',
1153 packet_rec.code_combination_id,
1154 'E',
1155 packet_rec.period_name,
1156 packet_rec.period_year,
1157 packet_rec.period_num,
1158 packet_rec.quarter_num,
1159 l_currency_code,
1160 sysdate,
1161 v_user_id,
1162 null,
1163 null,
1164 v_source_amount,
1165 null,
1166 v_source_amount,
1167 'Budget '||packet_rec.dossier_id,
1168 packet_rec.dossier_id || '..' || -- Bug 3466463
1169 packet_rec.trx_id, -- packet_rec.trx_id,
1170 packet_rec.source_trx_id,
1171 v_encumbrance_type_id);
1172
1173 -- =============== START DEBUG LOG ================
1174 Debug_log_string (l_proc_level, 'Reject.Msg31',
1175 ' INSERT INTO gl_bc_packets --> ' || SQL%ROWCOUNT);
1176 -- =============== END DEBUG LOG ================== */
1177
1178 /* bug 6126275 start */
1179 reject_insert_row ( v_packet_id,
1180 packet_rec.sob_id,
1181 packet_rec.code_combination_id,
1182 packet_rec.period_name,
1183 packet_rec.period_year,
1184 packet_rec.period_num,
1185 packet_rec.quarter_num,
1186 l_currency_code,
1187 v_user_id,
1188 v_source_amount,
1189 packet_rec.dossier_id,
1190 packet_rec.trx_id,
1191 v_encumbrance_type_id,
1192 packet_rec.source_trx_id);
1193 /* end bug 6126275 */
1194
1195 -- =============== START DEBUG LOG ================
1196 Debug_log_string (l_proc_level, 'Reject.Msg32',
1197 ' CALLING GL_FUNDS_CHECKER_PKG.glxfck ');
1198 Debug_log_string (l_proc_level, 'Reject.Msg33',
1199 ' P_SOBID --> ' || l_sob_id);
1200 Debug_log_string (l_proc_level, 'Reject.Msg34',
1201 ' P_PACKETID --> ' || v_packet_id);
1202 Debug_log_string (l_proc_level, 'Reject.Msg35',
1203 ' P_MODE --> R');
1204 Debug_log_string (l_proc_level, 'Reject.Msg36',
1205 ' P_PARTIAL_RESV_FLAG --> N ');
1206 Debug_log_string (l_proc_level, 'Reject.Msg37',
1207 ' P_OVERRIDE --> Y ');
1208 Debug_log_string (l_proc_level, 'Reject.Msg38',
1209 ' P_CONC_FLAG --> N ');
1210 Debug_log_string (l_proc_level, 'Reject.Msg39',
1211 ' P_USER_ID --> ' || l_user_id);
1212 Debug_log_string (l_proc_level, 'Reject.Msg40',
1213 ' P_USER_RESP_ID --> ' || l_responsibility_id);
1214 -- =============== END DEBUG LOG ==================
1215
1216 -- Bug 1635678 sekhar kappaga budget jounral for the destination key end ---
1217 /* Commented GL_FUND_CHECKER_PKG & added PSA_FUNDS_CHECKER_PKG for R12 uptake of Dossier - bug 6126275 */
1218 /* IF GL_FUNDS_CHECKER_PKG.glxfck ( P_SOBID => l_sob_id ,
1219 P_PACKETID => v_packet_id ,
1220 P_MODE => 'R',
1221 P_PARTIAL_RESV_FLAG => 'N',
1222 P_OVERRIDE => 'Y',
1223 P_CONC_FLAG => 'N',
1224 P_USER_ID => l_user_id,
1225 P_USER_RESP_ID => l_responsibility_id,
1226 P_RETURN_CODE => l_return_code ) */
1227 IF PSA_FUNDS_CHECKER_PKG.glxfck ( p_ledgerid => l_sob_id ,
1228 P_PACKETID => v_packet_id ,
1229 P_MODE => 'R',
1230 P_OVERRIDE => 'Y',
1231 P_CONC_FLAG => 'N',
1232 P_USER_ID => l_user_id,
1233 P_USER_RESP_ID => l_responsibility_id,
1234 P_CALLING_PROG_FLAG => 'G',
1235 P_RETURN_CODE => l_return_code )
1236
1237 THEN
1238 NULL;
1239 -- =============== START DEBUG LOG ================
1240 Debug_log_string (l_proc_level, 'Reject.Msg41',
1241 ' GL_FUNDS_CHECKER_PKG.glxfck --> TRUE');
1242 -- =============== END DEBUG LOG ==================
1243 ELSE
1244 -- =============== START DEBUG LOG ================
1245 Debug_log_string (l_proc_level, 'Reject.Msg42',
1246 ' GL_FUNDS_CHECKER_PKG.glxfck --> FALSE');
1247 -- =============== END DEBUG LOG ==================
1248 END IF;
1249
1250 -- =============== START DEBUG LOG ================
1251 Debug_log_string (l_proc_level, 'Reject.Msg43',
1252 ' P_RETURN_CODE --> ' || l_return_code);
1253 -- =============== END DEBUG LOG ==================
1254
1255 END LOOP;
1256
1257
1258 -- =============== START DEBUG LOG ================
1259 Debug_log_string (l_proc_level, 'Reject.Msg44',
1260 ' RETURN TRUE ');
1261 Debug_log_string (l_proc_level, 'Reject.Msg45',
1262 ' ** END APPROVE ** ');
1263 -- =============== END DEBUG LOG ==================
1264
1265 RETURN TRUE;
1266
1267 EXCEPTION
1268 WHEN OTHERS THEN
1269 -- =============== START DEBUG LOG ================
1270 DEBUG_LOG_UNEXP_ERROR ('Reject.unexp1','DEFAULT');
1271 -- =============== END DEBUG LOG ==================
1272 RETURN FALSE;
1273
1274 END Reject;
1275
1276 END igi_dos_funds;
1277