[Home] [Help]
PACKAGE BODY: APPS.IGI_ITR
Source
1 PACKAGE BODY IGI_ITR AS
2 -- $Header: igiitrgb.pls 120.3.12000000.1 2007/09/12 10:31:04 mbremkum ship $
3 --
4 PROCEDURE action (p_action NUMBER)
5 AS
6 BEGIN
7 l_Action := l_Action + p_action;
8 END action;
9 --
10 --
11 PROCEDURE set_batches (p_batch_id NUMBER)
12 AS
13 BEGIN
14 l_TableRow := l_TableRow + 1;
15 l_BatchIdTable(l_TableRow) := p_batch_id;
16 END set_batches;
17 --
18 --
19 PROCEDURE process_batches
20 AS
21 l_Var binary_integer := 0;
22 BEGIN
23 IF l_Action <> 0
24 THEN
25 FOR I in 1..l_TableRow
26 LOOP
27 l_Var := l_Var + 1;
28 IGI_itr.STATUS(l_BatchIdTable(l_Var));
29 END LOOP;
30 END IF;
31 END process_batches;
32 --
33 --
34 PROCEDURE control ( p_interface_run_id NUMBER
35 , p_group_id NUMBER
36 , p_set_of_books_id NUMBER
37 , p_status_code IN OUT NOCOPY NUMBER
38 , p_status_message IN OUT NOCOPY VARCHAR2
39 )
40 AS
41 import_request_id number;
42
43 BEGIN
44 INSERT INTO gl_interface_control ( je_source_name
45 , status
46 , interface_run_id
47 , group_id
48 , set_of_books_id
49 )
50 VALUES ( 'Internal Trading'
51 , 'S'
52 , p_interface_run_id
53 , p_group_id
54 , p_set_of_books_id
55 );
56
57 import_request_id := FND_REQUEST.SUBMIT_REQUEST
58 ('SQLGL'
59 , 'GLLEZL'
60 , NULL
61 , NULL
62 , FALSE
63 , p_interface_run_id
64 , p_set_of_books_id
65 , 'N'
66 , null
67 , null
68 , 'N'
69 , 'N');
70 p_status_code := import_request_id;
71 if p_status_code > 0 then
72 -- p_status_message := 'The Import Request Id is '||to_char(import_request_id);
73 -- Modified by Prerak Vora for NLS Fixing.
74 p_status_message := IGI_GEN.GET_LOOKUP_MEANING('IGI_IMPORT_REQUEST_ID');
75 p_status_message := p_status_message ||to_char(import_request_id);
76 -- Modification ends.
77 commit;
78 elsif p_status_code = 0 then
79 -- Modified by Prerak Vora for NLS Fixing.
80 p_status_message := IGI_GEN.GET_LOOKUP_MEANING('IGI_IMPORT_REQUEST_ID_FAIL');
81 -- p_status_message := 'The Import Request Failed';
82 rollback;
83 end if;
84 exception
85 when others then
86 p_status_code := -1;
87 p_status_message := substr(sqlerrm,1,70);
88 rollback;
89 END control;
90
91
92 PROCEDURE SUBMIT
93 (P_IT_HEADER_ID number,
94 P_STATUS_FLAG IN OUT NOCOPY number, P_STATUS_MESSAGE IN OUT NOCOPY VARCHAR2)
95 AS
96
97 R_igi_itr_charge_headers IGI_itr_charge_headers%ROWTYPE;
98 R_igi_itr_charge_lines IGI_itr_charge_lines%ROWTYPE;
99
100 m_user_je_category_name varchar2(25);
101 m_user_je_source_name varchar2(25);
102 interface_sequence_num number;
103 interface_group_id number;
104 import_request_id number;
105 m_submit_flag char;
106 dr_total number := 0;
107 cr_total number := 0;
108 flag number := 0;
109
110 CURSOR c1 is
111 SELECT *
112 FROM IGI_itr_charge_lines
113 WHERE it_header_id = p_it_header_id
114 AND status_flag is null
115 AND posting_flag is null;
116
117
118 BEGIN
119 SELECT submit_flag into m_submit_flag
120 FROM IGI_itr_charge_headers
121 WHERE it_header_id = p_it_header_id;
122
123 SELECT * into R_igi_itr_charge_headers
124 FROM IGI_itr_charge_headers
125 WHERE it_header_id = p_it_header_id;
126
127 SELECT user_je_source_name into m_user_je_source_name
128 FROM gl_je_sources
129 WHERE je_source_name = R_igi_itr_charge_headers.it_source;
130
131 SELECT user_je_category_name into m_user_je_category_name
132 FROM gl_je_categories
133 WHERE je_category_name = R_igi_itr_charge_headers.it_category;
134
135 IF (m_submit_flag = 'S' or m_submit_flag = 'R') THEN
136 SELECT gl_journal_import_s.nextval
137 INTO interface_group_id
138 FROM dual;
139 INSERT INTO gl_interface
140 (status
141 ,entered_dr
142 ,entered_cr
143 ,set_of_books_id
144 ,user_je_source_name
145 ,user_je_category_name
146 ,accounting_date
147 ,currency_code
148 ,date_created
149 ,created_by
150 ,actual_flag
151 ,encumbrance_type_id
152 ,code_combination_id
153 ,reference1
154 ,reference4
155 ,reference5
156 ,reference10
157 ,reference21
158 ,reference22
159 ,group_id
160 )
161 VALUES ('NEW'
162 ,R_igi_itr_charge_headers.entered_dr
163 ,R_igi_itr_charge_headers.entered_cr
164 ,R_igi_itr_charge_headers.set_of_books_id
165 ,m_user_je_source_name
166 ,m_user_je_category_name
167 ,R_igi_itr_charge_headers.gl_date
168 ,R_igi_itr_charge_headers.currency_code
169 ,R_igi_itr_charge_headers.creation_date
170 ,R_igi_itr_charge_headers.created_by
171 ,'E'
172 ,R_igi_itr_charge_headers.encumbrance_type_id
173 ,R_igi_itr_charge_headers.code_combination_id
174 ,R_igi_itr_charge_headers.name
175 ,R_igi_itr_charge_headers.name
176 ,R_igi_itr_charge_headers.description
177 ,R_igi_itr_charge_headers.description
178 ,R_igi_itr_charge_headers.it_header_id
179 ,NULL
180 ,interface_group_id
181 );
182 OPEN c1;
183 LOOP
184 FETCH c1 INTO R_igi_itr_charge_lines;
185 EXIT WHEN c1%NOTFOUND;
186
187 INSERT INTO gl_interface
188 (status
189 ,entered_dr
190 ,entered_cr
191 ,set_of_books_id
192 ,user_je_source_name
193 ,user_je_category_name
194 ,accounting_date
195 ,currency_code
196 ,date_created
197 ,created_by
198 ,actual_flag
199 ,encumbrance_type_id
200 ,code_combination_id
201 ,reference1
202 ,reference4
203 ,reference5
204 ,reference10
205 ,reference21
206 ,reference22
207 ,group_id
208 )
209 VALUES ('NEW'
210 ,R_igi_itr_charge_lines.entered_dr
211 ,R_igi_itr_charge_lines.entered_cr
212 ,R_igi_itr_charge_lines.set_of_books_id
213 ,m_user_je_source_name
214 ,m_user_je_category_name
215 ,R_igi_itr_charge_headers.gl_date
216 ,R_igi_itr_charge_headers.currency_code
217 ,R_igi_itr_charge_lines.creation_date
218 ,R_igi_itr_charge_lines.created_by
219 ,'E'
220 ,R_igi_itr_charge_headers.encumbrance_type_id
221 ,R_igi_itr_charge_lines.code_combination_id
222 ,R_igi_itr_charge_headers.name
223 ,R_igi_itr_charge_headers.name
224 ,R_igi_itr_charge_headers.description
225 ,R_igi_itr_charge_lines.description
226 ,R_igi_itr_charge_headers.it_header_id
227 ,R_igi_itr_charge_lines.it_line_num
228 ,interface_group_id
229 );
230 END LOOP;
231 CLOSE c1;
232 END IF;
233
234 SELECT gl_journal_import_s.nextval
235 INTO interface_sequence_num
236 FROM dual;
237
238 INSERT INTO gl_interface_control
239 (je_source_name
240 ,status
241 ,interface_run_id
242 ,group_id
243 ,set_of_books_id)
244 VALUES
245 ('Internal Trading'
246 ,'S'
247 ,interface_sequence_num
248 ,interface_group_id
249 ,R_igi_itr_charge_headers.set_of_books_id
250 );
251
252 import_request_id := FND_REQUEST.SUBMIT_REQUEST
253 ('SQLGL'
254 , 'GLLEZL'
255 , NULL
256 , NULL
257 , FALSE
258 , interface_sequence_num
259 ,R_igi_itr_charge_headers.set_of_books_id
260 , 'N'
261 , null
262 , null
263 , 'N'
264 , 'N');
265 p_status_flag := import_request_id;
266 IF p_status_flag > 0 THEN
267 p_status_message := 'The Import Request Id is : '||to_char(import_request_id);
268 COMMIT;
269 ELSIF p_status_flag = 0 THEN
270 p_status_message := 'The Import Request Failed';
271 ROLLBACK;
272 END IF;
273 EXCEPTION
274 WHEN others THEN
275 P_STATUS_FLAG := -1;
276 P_STATUS_MESSAGE := substr(sqlerrm,1,70);
277 END SUBMIT;
278
279 /*Procedure ACCEPT */
280 procedure ACCEPT
281 (p_it_header_id number
282 ,p_it_line_num number
283 ,p_igig_itr_encumbrance_allowed char
284 ,p_group_id number
285 ,p_status_code in out NOCOPY number
286 ,p_status_message in out NOCOPY char
287 ) as
288
289
290 m_user_je_category_name varchar2(25);
291 m_user_je_source_name varchar2(25);
292
293 R_header igi_itr_charge_headers%rowtype;
294 R_line igi_itr_charge_lines%rowtype;
295
296 PROCEDURE ACTUAL is
297 begin
298 insert into gl_interface
299 (status
300 ,entered_dr
301 ,entered_cr
302 ,set_of_books_id
303 ,user_je_source_name
304 ,user_je_category_name
305 ,accounting_date
306 ,currency_code
307 ,date_created
308 ,created_by
309 ,actual_flag
310 ,encumbrance_type_id
311 ,code_combination_id
312 ,reference1
313 ,reference4
314 ,reference5
315 ,reference10
316 ,reference21
317 ,reference22
318 ,group_id
319 )
320 values ('NEW'
321 ,R_line.entered_dr
322 ,R_line.entered_cr
323 ,R_line.set_of_books_id
324 ,m_user_je_source_name
325 ,m_user_je_category_name
326 ,R_header.gl_date
327 ,R_header.currency_code
328 ,R_line.creation_date
329 ,R_line.created_by
330 ,'A'
331 ,null
332 ,R_line.code_combination_id
333 ,R_header.name
334 ,R_header.name
335 ,R_header.description
336 ,R_line.description
337 ,p_it_header_id
338 ,p_it_line_num
339 ,p_group_id
340 );
341 insert into gl_interface
342 (status
343 ,entered_dr
344 ,entered_cr
345 ,set_of_books_id
346 ,user_je_source_name
347 ,user_je_category_name
348 ,accounting_date
349 ,currency_code
350 ,date_created
351 ,created_by
352 ,actual_flag
353 ,encumbrance_type_id
354 ,code_combination_id
355 ,reference1
356 ,reference4
357 ,reference5
358 ,reference10
359 ,reference21
360 ,reference22
361 ,group_id
362 )
363 values ('NEW'
364 ,R_line.entered_cr
365 ,R_line.entered_dr
366 ,R_header.set_of_books_id
367 ,m_user_je_source_name
368 ,m_user_je_category_name
369 ,R_header.gl_date
370 ,R_header.currency_code
371 ,R_header.creation_date
372 ,R_header.created_by
373 ,'A'
374 ,null
375 ,R_header.code_combination_id
376 ,R_header.name
377 ,R_header.name
378 ,R_header.description
379 ,R_header.description
380 ,p_it_header_id
381 ,p_it_line_num
382 ,p_group_id
383 );
384 end;
385 begin
386 select * into R_header
387 from IGI_itr_charge_headers
388 where it_header_id = p_it_header_id;
389
390 select * into R_line
391 from IGI_itr_charge_lines
392 where it_header_id = p_it_header_id
393 and it_line_num = p_it_line_num;
394
395 select user_je_source_name into m_user_je_source_name
396 from gl_je_sources
397 where je_source_name = R_header.it_source;
398
399 select user_je_category_name into m_user_je_category_name
400 from gl_je_categories
401 where je_category_name = R_header.it_category;
402
403 if (p_igig_itr_encumbrance_allowed = 'Y') then
404
405 IGI_ITR.REJECT(R_header.it_header_id
406 ,R_line.it_line_num
407 ,p_group_id
408 ,p_status_code
409 ,p_status_message
410 );
411 ACTUAL;
412 elsif (p_igig_itr_encumbrance_allowed = 'N') then
413 ACTUAL;
414 end if;
415 EXCEPTION
416 when others then
417 p_status_code := sqlcode;
418 p_status_message := substr(sqlerrm,1,70);
419 end ACCEPT;
420
421 /* Procedure REJECT */
422 procedure REJECT
423 (p_it_header_id number
424 ,p_it_line_num number
425 ,p_group_id number
426 ,p_status_code in out NOCOPY number
427 ,p_status_message in out NOCOPY char
428 ) as
429
430
431 m_user_je_category_name varchar2(25);
432 m_user_je_source_name varchar2(25);
433
434 R_header IGI_itr_charge_headers%rowtype;
435 R_line IGI_itr_charge_lines%rowtype;
436
437 begin
438 select * into R_header
439 from IGI_itr_charge_headers
440 where it_header_id = p_it_header_id;
441
442 select * into R_line
443 from IGI_itr_charge_lines
444 where it_header_id = p_it_header_id
445 and it_line_num = p_it_line_num;
446
447 select user_je_source_name into m_user_je_source_name
448 from gl_je_sources
449 where je_source_name = R_header.it_source;
450
451 select user_je_category_name into m_user_je_category_name
452 from gl_je_categories
453 where je_category_name = R_header.it_category;
454
455 insert into gl_interface
456 (status
457 ,entered_dr
458 ,entered_cr
459 ,set_of_books_id
460 ,user_je_source_name
461 ,user_je_category_name
462 ,accounting_date
463 ,currency_code
464 ,date_created
465 ,created_by
466 ,actual_flag
467 ,encumbrance_type_id
468 ,code_combination_id
469 ,reference1
470 ,reference4
471 ,reference5
472 ,reference10
473 ,reference21
474 ,reference22
475 ,group_id
476 )
477 values ('NEW'
478 ,R_line.entered_cr
479 ,R_line.entered_dr
480 ,R_line.set_of_books_id
481 ,m_user_je_source_name
482 ,m_user_je_category_name
483 ,R_header.gl_date
484 ,R_header.currency_code
485 ,R_line.creation_date
486 ,R_line.created_by
487 ,'E'
488 ,R_header.encumbrance_type_id
489 ,R_line.code_combination_id
490 ,R_header.name
491 ,R_header.name
492 ,R_header.description
493 ,R_line.description
494 ,p_it_header_id
495 ,p_it_line_num
496 ,p_group_id
497 );
498 insert into gl_interface
499 (status
500 ,entered_dr
501 ,entered_cr
502 ,set_of_books_id
503 ,user_je_source_name
504 ,user_je_category_name
505 ,accounting_date
506 ,currency_code
507 ,date_created
508 ,created_by
509 ,actual_flag
510 ,encumbrance_type_id
511 ,code_combination_id
512 ,reference1
513 ,reference4
514 ,reference5
515 ,reference10
516 ,reference21
517 ,reference22
518 ,group_id
519 )
520 values ('NEW'
521 ,R_line.entered_dr
522 ,R_line.entered_cr
523 ,R_header.set_of_books_id
524 ,m_user_je_source_name
525 ,m_user_je_category_name
526 ,R_header.gl_date
527 ,R_header.currency_code
528 ,R_header.creation_date
529 ,R_header.created_by
530 ,'E'
531 ,R_header.encumbrance_type_id
532 ,R_header.code_combination_id
533 ,R_header.name
534 ,R_header.name
535 ,R_header.description
536 ,R_header.description
537 ,p_it_header_id
538 ,p_it_line_num
539 ,p_group_id
540 );
541 EXCEPTION
542 when others then
543 p_status_code := sqlcode;
544 p_status_message := substr(sqlerrm,1,70);
545 end REJECT;
546
547 /*
548 * STATUS
549 */
550 procedure STATUS( p_batch_id number) as
551
552 b_budgetary_control_status gl_je_batches.budgetary_control_status%TYPE;
553 b_status_flag gl_je_batches.status%TYPE;
554 l_status_flag varchar2(1);
555 l_posting_flag varchar2(1);
556 b_actual_flag varchar2(1);
557 a number;
558
559 cursor c_header(c_batch_id number) is
560 select je_header_id
561 from gl_je_headers
562 where je_batch_id = c_batch_id
563 and je_source = 'Internal Trading';
564
565 cursor c_line(c_header_id number)
566 is select reference_1,reference_2
567 from gl_je_lines
568 where je_header_id = c_header_id;
569
570 begin
571 select budgetary_control_status, status,actual_flag into
572 b_budgetary_control_status, b_status_flag , b_actual_flag
573 from gl_je_batches
574 where je_batch_id = p_batch_id;
575
576 for current_header in c_header(p_batch_id) loop
577
578 for current_line in c_line(current_header.je_header_id) loop
579 begin
580 if (current_line.reference_1 is not null) and
581 (current_line.reference_2 is not null) then
582 select status_flag, posting_flag into l_status_flag, l_posting_flag
583 from IGI_itr_charge_lines
584 where it_header_id = current_line.reference_1 and
585 it_line_num = current_line.reference_2;
586 else
587 select 1 into a from IGI_itr_charge_headers
588 where it_header_id = current_line.reference_1;
589 l_status_flag := NULL;
590 end if;
591 EXCEPTION
592 when no_data_found then
593 exit;
594 end;
595
596 if b_status_flag NOT IN('P','S','U','I' )
597 then
598 if b_budgetary_control_status = 'F'
599 then
600 if l_status_flag IS NULL
601 then
602 UPDATE IGI_itr_charge_headers
603 SET submit_flag = 'F'
604 WHERE it_header_id = current_line.reference_1;
605 else
606 UPDATE IGI_itr_charge_lines
607 SET posting_flag = 'F'
608 WHERE it_header_id = current_line.reference_1
609 AND it_line_num = current_line.reference_2;
610 end if;
611
612 DELETE gl_je_lines where je_header_id IN
613 ( SELECT je_header_id
614 FROM gl_je_headers
615 WHERE je_batch_id = p_batch_id
616 );
617 DELETE gl_je_headers WHERE je_batch_id = p_batch_id;
618 DELETE gl_je_batches WHERE je_batch_id = p_batch_id;
619
620
621 elsif b_budgetary_control_status = 'R'
622 and b_status_flag in ('D','Z')
623
624 then
625 if l_status_flag IS NULL
626 then
627 UPDATE IGI_itr_charge_headers
628 SET submit_flag = 'D'
629 WHERE it_header_id = current_line.reference_1;
630 else
631 UPDATE IGI_itr_charge_lines
632 SET posting_flag = 'D'
633 WHERE it_header_id = current_line.reference_1
634 AND it_line_num = current_line.reference_2;
635 end if;
636
637 DELETE gl_je_lines where je_header_id IN
638 (SELECT je_header_id
639 FROM gl_je_headers
640 WHERE je_batch_id = p_batch_id);
641 delete gl_je_headers where je_batch_id = p_batch_id;
642 delete gl_je_batches where je_batch_id = p_batch_id;
643
644
645 elsif b_budgetary_control_status NOT IN ('R')
646 and b_status_flag in ('D','Z')
647
648 then
649 if l_status_flag IS NULL
650 then
651 UPDATE IGI_itr_charge_headers
652 SET submit_flag = 'O'
653 WHERE it_header_id = current_line.reference_1;
654 else
655 UPDATE IGI_itr_charge_lines
656 SET posting_flag = 'O'
657 WHERE it_header_id = current_line.reference_1
658 AND it_line_num = current_line.reference_2;
659 end if;
660
661 /* DELETE gl_je_lines where je_header_id IN
662 ( SELECT je_header_id
663 FROM gl_je_headers
664 WHERE je_batch_id = p_batch_id
665 );
666 DELETE gl_je_headers WHERE je_batch_id = p_batch_id;
667 DELETE gl_je_batches WHERE je_batch_id = p_batch_id;
668 */
669 elsif b_budgetary_control_status = 'N'
670 then
671 if l_status_flag IS NULL
672 then
673 UPDATE IGI_itr_charge_headers
674 SET submit_flag = 'D'
675 WHERE it_header_id = current_line.reference_1;
676 else
677 UPDATE IGI_itr_charge_lines
678 SET posting_flag = 'D'
679 WHERE it_header_id = current_line.reference_1
680 AND it_line_num = current_line.reference_2;
681 end if;
682
683 DELETE gl_je_lines where je_header_id IN
684 ( SELECT je_header_id
685 FROM gl_je_headers
686 WHERE je_batch_id = p_batch_id
687 );
688 DELETE gl_je_headers WHERE je_batch_id = p_batch_id;
689 DELETE gl_je_batches WHERE je_batch_id = p_batch_id;
690
691 elsif b_budgetary_control_status = 'P'
692 then
693 if l_status_flag IS NULL
694 then
695 UPDATE IGI_itr_charge_headers
696 SET submit_flag = 'O'
697 WHERE it_header_id = current_line.reference_1;
698 else
699 UPDATE IGI_itr_charge_lines
700 SET posting_flag = 'O'
701 WHERE it_header_id = current_line.reference_1
702 AND it_line_num = current_line.reference_2;
703 end if;
704 else
705 NULL;
706 end if;
707
708 elsif b_status_flag = 'P'
709 then
710 if l_status_flag IS NULL
711 then
712 UPDATE IGI_itr_charge_headers
713 SET submit_flag = 'P'
714 WHERE it_header_id = current_line.reference_1;
715 else
716 if (l_posting_flag = 'S' OR l_posting_flag = 'P')
717 then
718 UPDATE IGI_itr_charge_lines
719 SET posting_flag = 'P'
720 WHERE it_header_id = current_line.reference_1
721 AND it_line_num = current_line.reference_2;
722
723 UPDATE IGI_itr_charge_headers
724 set submit_flag = 'C' where
725 it_header_id = current_line.reference_1
726 and ('A','P') =all (select status_flag,posting_flag
727 from IGI_itr_charge_lines
728 where it_header_id = current_line.reference_1)
729 AND b_actual_flag = 'A';
730 end if;
731 end if;
732 else
733 NULL;
734 end if;
735
736
737 end loop;
738 end loop;
739 EXCEPTION
740 WHEN NO_DATA_FOUND THEN
741 NULL;
742 end STATUS;
743
744 end IGI_ITR;