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