[Home] [Help]
PACKAGE BODY: APPS.JL_ZZ_GL_COPY_JE_PKG
Source
1 PACKAGE BODY jl_zz_gl_copy_je_pkg AS
2 /* $Header: jlzzgcjb.pls 120.8 2005/04/08 20:59:01 vsidhart ship $ */
3 TYPE je_source_tab IS TABLE OF VARCHAR2(30)
4 INDEX BY BINARY_INTEGER;
5
6 x_last_updated_by NUMBER(15);
7 x_last_update_login NUMBER(15);
8 x_request_id NUMBER(15);
9 x_program_application_id NUMBER(15);
10 x_program_id NUMBER(15);
11 x_sysdate DATE;
12 x_debug BOOLEAN := FALSE;
13 x_statement VARCHAR2(20);
14
15 detail_group_id NUMBER(15);
16 summary_group_id NUMBER(15);
17 x_group_id NUMBER(15);
18 x_entered_dr NUMBER;
19 x_entered_cr NUMBER;
20 x_accounted_dr NUMBER;
21 x_accounted_cr NUMBER;
22 create_summary_journals VARCHAR2(3);
23
24 PROCEDURE find_who_columns;
25 PROCEDURE get_import_group_id(p_summary IN OUT NOCOPY NUMBER,p_detail IN OUT NOCOPY NUMBER);
26 FUNCTION run_import_journal(p_counter NUMBER,
27 p_sob NUMBER,
28 p_je_source_tab je_source_tab,
29 p_group_id NUMBER,
30 p_summary VARCHAR2)
31 return boolean;
32
33 ----------------------------------------------------------------------------
34 -- PROCEDURE --
35 -- copy --
36 -- --
37 -- DESCRIPTION --
38 -- Use this procedure to copy journal entry from one --
39 -- sets of books and put them in gl_interface table to be imported --
40 -- for another sets of books. --
41 -- --
42 -- PURPOSE: --
43 -- Oracle Applications Rel 11.5 --
44 -- --
45 -- PARAMETERS: --
46 -- p_from_book --
47 -- p_to_book --
48 -- p_period --
49 -- --
50 -- --
51 -- HISTORY: --
52 -- 12/10/98 Sujit Dalai Created --
53 ----------------------------------------------------------------------------
54
55 PROCEDURE copy( ERRBUF OUT NOCOPY VARCHAR2,
56 RETCODE OUT NOCOPY VARCHAR2,
57 p_from_ledger NUMBER,
58 p_to_ledger NUMBER,
59 p_period VARCHAR2) IS
60
61 p_import VARCHAR2(2) := 'Y';
62 CURSOR c_ledgers IS
63 SELECT set_of_books_id,
64 name,
65 chart_of_accounts_id,
66 currency_code,
67 period_set_name
68 FROM gl_sets_of_books
69 WHERE set_of_books_id IN ( p_from_ledger , p_to_ledger );
70
71 /*
72 Bug 2564710 - Commented check on posted date since it is null for Move/Merge journals
73 and there will exist no journals with posted date for any other status
74 */
75
76 CURSOR c_batch IS
77 SELECT je_batch_id,
78 name,
79 description
80 FROM gl_je_batches
81 WHERE actual_flag = 'A'
82 --AND set_of_books_id = p_sob_id
83 --AND posted_date IS NOT NULL
84 AND status = 'P'
85 AND default_period_name = p_period;
86
87 /*
88 Bug 2564710 - Added one more check on global_attribute1 to confirm that they are not that
89 of reversed journals since for reversed journals the global attribute1 is populated with
90 the original journals header id as a result of which these journals are never selected
91 to be posted
92 */
93
94 /*
95 Bug 2606480 - accrual_rev_flag should be considered 'N' if accrual_rev_effective_date is null
96 */
97 CURSOR c_header(p_batch_id NUMBER,
98 p_ledger_id NUMBER) IS
99 SELECT h.je_header_id,
100 h.name header_name,
101 h.default_effective_date,
102 h.description header_description,
103 h.currency_code,
104 h.currency_conversion_rate,
105 d.user_conversion_type,
106 h.currency_conversion_date,
107 --h.accrual_rev_flag,
108 decode (h.accrual_rev_effective_date, null, 'N', 'Y') accrual_rev_flag,
109 h.accrual_rev_effective_date,
110 h.global_attribute1,
111 h.reversed_je_header_id,
112 s.user_je_source_name,
113 s.je_source_name,
114 s.journal_reference_flag,
115 c.user_je_category_name,
116 c.je_category_name
117 FROM gl_je_headers h,
118 gl_je_sources s,
119 gl_je_categories c,
120 gl_daily_conversion_types d
121 WHERE h.je_source = s.je_source_name
122 AND c.je_category_name = h.je_category
123 AND je_batch_id = p_batch_id
124 AND (NVL(global_attribute1, 0) = 0
125 OR NVL(global_attribute1,0) <> h.je_header_id)
126 AND status = 'P'
127 AND actual_flag = 'A'
128 AND je_category <> 'Revaluation'
129 AND s.je_source_name <> 'Assets'
130 AND h.currency_conversion_type = d.conversion_type
131 AND h.ledger_id = p_ledger_id
132 ORDER BY s.je_source_name
133 FOR UPDATE OF h.global_attribute1;
134
135 CURSOR c_line(p_header_id NUMBER) IS
136 SELECT rownum,
137 l.je_line_num,
138 l.entered_dr,
139 l.entered_cr,
140 l.accounted_dr,
141 l.accounted_cr,
142 l.description line_description,
143 l.reference_1,
144 l.reference_2,
145 l.reference_3,
146 l.reference_4,
147 l.reference_5,
148 l.reference_6,
149 l.reference_7,
150 l.reference_8,
151 l.reference_9,
152 l.reference_10,
153 l.code_combination_id,
154 l.stat_amount,
155 l.attribute1,
156 l.attribute2,
157 l.attribute3,
158 l.attribute4,
159 l.attribute5,
160 l.attribute6,
161 l.attribute7,
162 l.attribute8,
163 l.attribute9,
164 l.attribute10,
165 l.attribute11,
166 l.attribute12,
167 l.attribute13,
168 l.attribute14,
169 l.attribute15,
170 l.attribute16,
171 l.attribute17,
172 l.attribute18,
173 l.attribute19,
174 l.attribute20,
175 l.context,
176 l.context2,
177 l.context3,
178 l.invoice_date,
179 l.tax_code,
180 l.invoice_identifier,
181 l.invoice_amount,
182 l.ussgl_transaction_code--,
183 --l.jgzz_recon_ref
184 FROM gl_je_lines l
185 WHERE l.je_header_id = p_header_id;
186
187 CURSOR c_ref( p_header_id NUMBER,
188 p_line_num NUMBER) IS
189 SELECT reference_1,
190 reference_2,
191 reference_3,
192 reference_4,
193 reference_5,
194 reference_6,
195 reference_7,
196 reference_8,
197 reference_9,
198 reference_10
199 FROM gl_import_references
200 WHERE je_header_id = p_header_id
201 AND je_line_num = p_line_num;
202
203 x_je_source_tab_sum je_source_tab;
204 x_je_source_tab_det je_source_tab;
205 x_counter_sum NUMBER := 0;
206 x_counter_det NUMBER := 0;
207 x_je_source_name_sum VARCHAR2(30);
208 x_je_source_name_det VARCHAR2(30);
209
210 x_from_ledger c_ledgers%ROWTYPE;
211 x_to_ledger c_ledgers%ROWTYPE;
212 x_sequence_line NUMBER;
213 x_reference_1 VARCHAR2(240);
214 x_reference_2 VARCHAR2(240);
215 x_reference_3 VARCHAR2(240);
216 x_reference_4 VARCHAR2(240);
217 x_reference_5 VARCHAR2(240);
218 x_reference_6 VARCHAR2(240);
219 x_reference_7 VARCHAR2(240);
220 x_reference_8 VARCHAR2(240);
221 x_reference_9 VARCHAR2(240);
222 x_reference_10 VARCHAR2(240);
223
224
225 x_number NUMBER :=0;
226 err_num NUMBER;
227 x_result BOOLEAN;
228 err_msg VARCHAR2(2000);
229 DIFF_CHART_OF_ACCT_ID EXCEPTION;
230 DIFF_CURRENCY_CODE EXCEPTION;
231 DIFF_CALENDAR EXCEPTION;
232 UNABLE_TO_IMPORT EXCEPTION;
233
234 x_source_index BINARY_INTEGER;
235 x_source_row VARCHAR2(2) :='Y';
236
237 BEGIN
238
239 fnd_message.set_name('JL', 'JL_CO_FA_PARAMETER');
240 fnd_file.put_line( FND_FILE.LOG, fnd_message.get);
241 fnd_file.put_line(FND_FILE.LOG, '----------------------------------------');
242 fnd_message.set_name('JL', 'JL_ZZ_GL_SOB');
243 fnd_message.set_token('SOB', p_to_ledger );
244 fnd_file.put_line( 1, fnd_message.get);
245 fnd_message.set_name('JL', 'JL_ZZ_FA_PERIOD_NAME');
246 fnd_message.set_token('PERIOD_NAME', p_period);
247 fnd_file.put_line( 1, fnd_message.get);
248 fnd_file.put_line(FND_FILE.LOG, '----------------------------------------');
249
250 ---------------------------------------------------------
251 -- Find who columns --
252 ---------------------------------------------------------
253 find_who_columns;
254
255
256 ---------------------------------------------------------
257 -- Get set of books information --
258 ---------------------------------------------------------
259
260
261 x_statement := 'SOB_INFO';
262 FOR rec_sob IN c_ledgers LOOP
263 IF (rec_sob.set_of_books_id = p_from_ledger ) THEN
264 x_from_ledger .chart_of_accounts_id := rec_sob.chart_of_accounts_id;
265 x_from_ledger .currency_code := rec_sob.currency_code;
266 x_from_ledger .period_set_name := rec_sob.period_set_name;
267 x_from_ledger .set_of_books_id := rec_sob.set_of_books_id;
268 x_from_ledger .name := rec_sob.name;
269
270 ELSE
271 x_to_ledger .chart_of_accounts_id := rec_sob.chart_of_accounts_id;
272 x_to_ledger .currency_code := rec_sob.currency_code;
273 x_to_ledger .period_set_name := rec_sob.period_set_name;
274 x_to_ledger .set_of_books_id := rec_sob.set_of_books_id;
275 x_to_ledger .name := rec_sob.name;
276 END IF;
277 END LOOP;
278 ---------------------------------------------------------
279 -- Finish the program with error incase of following --
280 -- conditions. --
281 -- a) Chart of account id is not same for both books--
282 -- b) Currency code not same for both books --
283 -- c) calendar is not same for both books --
284 ---------------------------------------------------------
285 IF (x_from_ledger.chart_of_accounts_id <> x_to_ledger.chart_of_accounts_id) THEN
286 RAISE DIFF_CHART_OF_ACCT_ID;
287 END IF;
288
289 IF (x_from_ledger.currency_code <> x_to_ledger.currency_code) THEN
290 RAISE DIFF_CURRENCY_CODE;
291 END IF;
292
293 IF (x_from_ledger.period_set_name <> x_to_ledger.period_set_name) THEN
294 RAISE DIFF_CALENDAR;
295 END IF;
296
297 -------------------------------------------------------------
298 --Get Group Id's for GL_INTERFACE and GL_INTERFACE_CONTROL --
299 -------------------------------------------------------------
300
301 Get_Import_Group_Id(Summary_group_id,Detail_group_id);
302
303 ---------------------------------------------------------
304 -- Get information from gl_je_batches --
305 ---------------------------------------------------------
306 FOR rec_batch IN c_batch LOOP
307
308 ---------------------------------------------------------
309 -- Get information from gl_je_headers --
310 ---------------------------------------------------------
311 FOR rec_header IN c_header (rec_batch.je_batch_id,x_from_ledger.set_of_books_id) LOOP
312
313 ---------------------------------------------------------
314 -- Get information from gl_je_lines --
315 ---------------------------------------------------------
316 FOR rec_line IN c_line( rec_header.je_header_id) LOOP
317 ---------------------------------------------------------
318 -- Assiging the Line References as default references --
319 ---------------------------------------------------------
320
321 x_reference_1 := rec_line.reference_1;
322 x_reference_2 := rec_line.reference_2;
323 x_reference_3 := rec_line.reference_3;
324 x_reference_4 := rec_line.reference_4;
325 x_reference_5 := rec_line.reference_5;
326 x_reference_6 := rec_line.reference_6;
327 x_reference_7 := rec_line.reference_7;
328 x_reference_8 := rec_line.reference_8;
329 x_reference_9 := rec_line.reference_9;
330 x_reference_10 := rec_line.reference_10;
331
332 ---------------------------------------------------------
333 -- Assiging the Detail Group Id as default --
334 ---------------------------------------------------------
335
336
337 x_group_id := detail_group_id;
338
339 ---------------------------------------------------------
340 -- Check Journal's Source Import References Flag --
341 ---------------------------------------------------------
342
343
344 IF (rec_header.journal_reference_flag = 'Y'
345 AND rec_header.reversed_je_header_id IS NULL) THEN
346
347
348 x_entered_dr := rec_line.entered_dr;
349 x_entered_cr := rec_line.entered_cr;
350 x_accounted_dr := rec_line.accounted_dr;
351 x_accounted_cr := rec_line.accounted_cr;
352
353 ---------------------------------------------------------
354 -- Get information from table gl_import_references --
355 ---------------------------------------------------------
356
357 ---------------------------------------------------------------------------
358 -- Bug 2757223 --
359 -- Irrespective of whether records exist in gl_import_references --
360 -- we need to insert into gl_interface if the import journal reference --
361 -- for manual source is set. Moved the End Loop from after insert to --
362 -- Before insert insert into gl_interface --
363 ---------------------------------------------------------------------------
364
365 FOR rec_ref IN c_ref(rec_header.je_header_id, rec_line.je_line_num) LOOP
366
367
368 IF nvl(rec_line.reference_1,'X') = nvl(rec_ref.reference_1,'X') AND
369 nvl(rec_line.reference_2,'X') = nvl(rec_ref.reference_2,'X') AND
370 nvl(rec_line.reference_3,'X') = nvl(rec_ref.reference_3,'X') AND
371 nvl(rec_line.reference_4,'X') = nvl(rec_ref.reference_4,'X') AND
372 nvl(rec_line.reference_5,'X') = nvl(rec_ref.reference_5,'X') AND
373 nvl(rec_line.reference_6,'X') = nvl(rec_ref.reference_6,'X') AND
374 nvl(rec_line.reference_7,'X') = nvl(rec_ref.reference_7,'X') AND
375 nvl(rec_line.reference_8,'X') = nvl(rec_ref.reference_8,'X') AND
376 nvl(rec_line.reference_9,'X') = nvl(rec_ref.reference_9,'X') AND
377 nvl(rec_line.reference_10,'X')= nvl(rec_ref.reference_10,'X') THEN
378
379 -- Do Nothing --
380 Null;
381 ELSE
382 x_group_id := Summary_group_id;
383 x_reference_1 := rec_ref.reference_1;
384 x_reference_2 := rec_ref.reference_2;
385 x_reference_3 := rec_ref.reference_3;
386 x_reference_4 := rec_ref.reference_4;
387 x_reference_5 := rec_ref.reference_5;
388 x_reference_6 := rec_ref.reference_6;
389 x_reference_7 := rec_ref.reference_7;
390 x_reference_8 := rec_ref.reference_8;
391 x_reference_9 := rec_ref.reference_9;
392 x_reference_10 := rec_ref.reference_10;
393 END IF;
394
395 END LOOP;
396 x_number := x_number +1;
397 ---------------------------------------------------------
398 -- Insert row into gl_interface --
399 ---------------------------------------------------------
400 x_statement := 'INS_GLIF';
401
402 /*Bug 3934813 - if we have two journals with the firsts 50 positions same in reference1 and first 25 positions same in reference4 fields, these two
403 journals will copy in only one journal. This is the expected behavior for Journal Import. Journal Import only looks at the first 50 characters
404 of reference1 and the first 25 characters of reference4 while grouping. This logic has been changed in 11iX, but doing so in release 11i
405 would require a major rewrite of Journal Import, which is not feasible at this time.
406
407 Suggestion from GL team is to transfer the first 40 characters of the batch name into reference1, and follow them with the je_batch_id.
408 Then, transfer the first 15 characters of the journal name into reference4, and follow them with the je_header_id */
409
410 INSERT INTO gl_interface(
411 STATUS,
412 SET_OF_BOOKS_ID,
413 ACCOUNTING_DATE,
414 CURRENCY_CODE,
415 DATE_CREATED,
416 CREATED_BY,
417 ACTUAL_FLAG ,
418 USER_JE_CATEGORY_NAME,
419 USER_JE_SOURCE_NAME,
420 CURRENCY_CONVERSION_DATE,
421 USER_CURRENCY_CONVERSION_TYPE,
422 CURRENCY_CONVERSION_RATE,
423 ENTERED_DR,
424 ENTERED_CR ,
425 ACCOUNTED_DR,
426 ACCOUNTED_CR,
427 REFERENCE1,
428 REFERENCE2 ,
429 REFERENCE4,
430 REFERENCE5,
431 REFERENCE6,
432 REFERENCE7,
433 REFERENCE8,
434 REFERENCE10,
435 REFERENCE21,
436 REFERENCE22,
437 REFERENCE23,
438 REFERENCE24,
439 REFERENCE25,
440 REFERENCE26,
441 REFERENCE27,
442 REFERENCE28,
443 REFERENCE29,
444 REFERENCE30,
445 CODE_COMBINATION_ID,
446 STAT_AMOUNT,
447 GROUP_ID,
448 ATTRIBUTE1,
449 ATTRIBUTE2,
450 ATTRIBUTE3,
451 ATTRIBUTE4,
452 ATTRIBUTE5 ,
453 ATTRIBUTE6,
454 ATTRIBUTE7 ,
455 ATTRIBUTE8,
456 ATTRIBUTE9,
457 ATTRIBUTE10,
458 ATTRIBUTE11,
459 ATTRIBUTE12,
460 ATTRIBUTE13,
461 ATTRIBUTE14,
462 ATTRIBUTE15,
463 ATTRIBUTE16,
464 ATTRIBUTE17,
465 ATTRIBUTE18,
466 ATTRIBUTE19,
467 ATTRIBUTE20,
468 CONTEXT,
469 CONTEXT2,
470 INVOICE_DATE,
471 TAX_CODE,
472 INVOICE_IDENTIFIER,
473 INVOICE_AMOUNT,
474 CONTEXT3 ,
475 USSGL_TRANSACTION_CODE--,
476 --JGZZ_RECON_REF
477 )
478 VALUES( 'NEW',
479 x_to_ledger.set_of_books_id,
480 rec_header.default_effective_date,
481 rec_header.currency_code,
482 x_sysdate,
483 x_last_updated_by,
484 'A',
485 rec_header.user_je_category_name,
486 rec_header.user_je_source_name,
487 rec_header.currency_conversion_date,
488 rec_header.user_conversion_type,
489 rec_header.currency_conversion_rate,
490 x_entered_dr,
491 x_entered_cr,
492 x_accounted_dr,
493 x_accounted_cr,
494 substr(rec_batch.name,1,40)|| to_char(rec_header.je_header_id),
495 rec_batch.description,
496 substr(rec_header.header_name,1,15)||to_char(rec_header.je_header_id),
497 rec_header.header_description,
498 to_char(rec_header.je_header_id),
499 rec_header.accrual_rev_flag,
500 rec_header.accrual_rev_effective_date,
501 rec_line.line_description,
502 x_reference_1,
503 x_reference_2,
504 x_reference_3,
505 x_reference_4,
506 x_reference_5,
507 x_reference_6,
508 x_reference_7,
509 x_reference_8,
510 x_reference_9,
511 x_reference_10,
512 rec_line.code_combination_id,
513 rec_line.stat_amount,
514 x_group_id,
515 rec_line.attribute1,
516 rec_line.attribute2,
517 rec_line.attribute3,
518 rec_line.attribute4,
519 rec_line.attribute5,
520 rec_line.attribute6,
521 rec_line.attribute7,
522 rec_line.attribute8,
523 rec_line.attribute9,
524 rec_line.attribute10,
525 rec_line.attribute11,
526 rec_line.attribute12,
527 rec_line.attribute13,
528 rec_line.attribute14,
529 rec_line.attribute15,
530 rec_line.attribute16,
531 rec_line.attribute17,
532 rec_line.attribute18,
533 rec_line.attribute19,
534 rec_line.attribute20,
535 rec_line.context,
536 rec_line.context2,
537 rec_line.invoice_date,
538 rec_line.tax_code,
539 rec_line.invoice_identifier,
540 rec_line.invoice_amount,
541 rec_line.context3,
542 rec_line.ussgl_transaction_code--,
543 --rec_line.jgzz_recon_ref
544 );
545
546
547 If rec_line.entered_dr is Null Then
548 x_entered_dr := Null;
549 x_accounted_dr := Null;
550 x_entered_cr := 0;
551 x_accounted_cr := 0;
552 Else
553 x_entered_dr := 0;
554 x_accounted_dr := 0;
555 x_entered_cr := Null;
556 x_accounted_cr := Null;
557 End If;
558
559 --END LOOP; --bug 2757223
560
561 ELSE
562
563 x_number := x_number +1;
564 ---------------------------------------------------------
565 -- Insert row into gl_interface --
566 ---------------------------------------------------------
567 x_statement := 'INS_GLIF';
568 INSERT INTO gl_interface(
569 STATUS,
570 SET_OF_BOOKS_ID,
571 ACCOUNTING_DATE,
572 CURRENCY_CODE,
573 DATE_CREATED,
574 CREATED_BY,
575 ACTUAL_FLAG ,
576 USER_JE_CATEGORY_NAME,
577 USER_JE_SOURCE_NAME,
578 CURRENCY_CONVERSION_DATE,
579 USER_CURRENCY_CONVERSION_TYPE,
580 CURRENCY_CONVERSION_RATE,
581 ENTERED_DR,
582 ENTERED_CR ,
583 ACCOUNTED_DR,
584 ACCOUNTED_CR,
585 REFERENCE1,
586 REFERENCE2 ,
587 REFERENCE4,
588 REFERENCE5,
589 REFERENCE6,
590 REFERENCE7,
591 REFERENCE8,
592 REFERENCE10,
593 REFERENCE21,
594 REFERENCE22,
595 REFERENCE23,
596 REFERENCE24,
597 REFERENCE25,
598 REFERENCE26,
599 REFERENCE27,
600 REFERENCE28,
601 REFERENCE29,
602 REFERENCE30,
603 CODE_COMBINATION_ID,
604 STAT_AMOUNT,
605 GROUP_ID,
606 ATTRIBUTE1,
607 ATTRIBUTE2,
608 ATTRIBUTE3,
609 ATTRIBUTE4,
610 ATTRIBUTE5 ,
611 ATTRIBUTE6,
612 ATTRIBUTE7 ,
613 ATTRIBUTE8,
614 ATTRIBUTE9,
615 ATTRIBUTE10,
616 ATTRIBUTE11,
617 ATTRIBUTE12,
618 ATTRIBUTE13,
619 ATTRIBUTE14,
620 ATTRIBUTE15,
621 ATTRIBUTE16,
622 ATTRIBUTE17,
623 ATTRIBUTE18,
624 ATTRIBUTE19,
625 ATTRIBUTE20,
626 CONTEXT,
627 CONTEXT2,
628 INVOICE_DATE,
629 TAX_CODE,
630 INVOICE_IDENTIFIER,
631 INVOICE_AMOUNT,
632 CONTEXT3 ,
633 USSGL_TRANSACTION_CODE--,
634 --JGZZ_RECON_REF
635 )
636 VALUES( 'NEW',
637 x_to_ledger.set_of_books_id,
638 rec_header.default_effective_date,
639 rec_header.currency_code,
640 x_sysdate,
641 x_last_updated_by,
642 'A',
643 rec_header.user_je_category_name,
644 rec_header.user_je_source_name,
645 rec_header.currency_conversion_date,
646 rec_header.user_conversion_type,
647 rec_header.currency_conversion_rate,
648 rec_line.entered_dr,
649 rec_line.entered_cr,
650 rec_line.accounted_dr,
651 rec_line.accounted_cr,
652 substr(rec_batch.name,1,40)|| to_char(rec_header.je_header_id),
653 rec_batch.description,
654 substr(rec_header.header_name,1,15)||to_char(rec_header.je_header_id),
655 rec_header.header_description,
656 to_char(rec_header.je_header_id),
657 rec_header.accrual_rev_flag,
658 rec_header.accrual_rev_effective_date,
659 rec_line.line_description,
660 x_reference_1,
661 x_reference_2,
662 x_reference_3,
663 x_reference_4,
664 x_reference_5,
665 x_reference_6,
666 x_reference_7,
667 x_reference_8,
668 x_reference_9,
669 x_reference_10,
670 rec_line.code_combination_id,
671 rec_line.stat_amount,
672 x_group_id,
673 rec_line.attribute1,
674 rec_line.attribute2,
675 rec_line.attribute3,
676 rec_line.attribute4,
677 rec_line.attribute5,
678 rec_line.attribute6,
679 rec_line.attribute7,
680 rec_line.attribute8,
681 rec_line.attribute9,
682 rec_line.attribute10,
683 rec_line.attribute11,
684 rec_line.attribute12,
685 rec_line.attribute13,
686 rec_line.attribute14,
687 rec_line.attribute15,
688 rec_line.attribute16,
689 rec_line.attribute17,
690 rec_line.attribute18,
691 rec_line.attribute19,
692 rec_line.attribute20,
693 rec_line.context,
694 rec_line.context2,
695 rec_line.invoice_date,
696 rec_line.tax_code,
697 rec_line.invoice_identifier,
698 rec_line.invoice_amount,
699 rec_line.context3,
700 rec_line.ussgl_transaction_code--,
701 --rec_line.jgzz_recon_ref
702 );
703 END IF;
704
705 END LOOP;
706
707
708 IF ((x_group_id = summary_group_id) AND (NVL(x_je_source_name_sum, '0') <> rec_header.je_source_name)) THEN
709
710 x_source_row :='Y';
711
712 IF x_counter_sum > 0 THEN
713 x_source_index := x_je_source_tab_sum.FIRST;
714 LOOP
715 IF x_je_source_tab_sum(x_source_index) = rec_header.je_source_name THEN
716 x_source_row := 'N';
717 x_source_index := x_je_source_tab_sum.LAST;
718 END IF;
719 EXIT WHEN x_source_index = x_je_source_tab_sum.LAST;
720 x_source_index := x_je_source_tab_sum.NEXT(x_source_index);
721 END LOOP;
722 END IF;
723 IF x_source_row ='Y' THEN
724 x_je_source_name_sum := rec_header.je_source_name;
725 x_counter_sum := x_counter_sum +1;
726 x_je_source_tab_sum(x_counter_sum) := x_je_source_name_sum;
727 END IF;
728 ElSIF ((x_group_id = detail_group_id) AND (NVL(x_je_source_name_det, '0') <> rec_header.je_source_name)) THEN
729
730 x_source_row :='Y';
731
732 IF x_counter_det > 0 THEN
733 x_source_index := x_je_source_tab_det.FIRST;
734 LOOP
735 IF x_je_source_tab_det(x_source_index) = rec_header.je_source_name THEN
736 x_source_row := 'N';
737 x_source_index := x_je_source_tab_det.LAST;
738 END IF;
739 EXIT WHEN x_source_index = x_je_source_tab_det.LAST;
740 x_source_index := x_je_source_tab_det.NEXT(x_source_index);
741 END LOOP;
742 END IF;
743
744 IF x_source_row ='Y' THEN
745 x_je_source_name_det := rec_header.je_source_name;
746 x_counter_det := x_counter_det +1;
747 x_je_source_tab_det(x_counter_det) := x_je_source_name_det;
748 END IF;
749
750 END IF;
751 ---------------------------------------------------------
752 -- UPDATE gl_je_headers --
753 ---------------------------------------------------------
754
755 UPDATE gl_je_headers
756 SET global_attribute1 = to_char(rec_header.je_header_id)
757 WHERE CURRENT OF c_header;
758
759 END LOOP;
760
761 END LOOP;
762 fnd_message.set_name('JL', 'JL_ZZ_GL_JE_PROCESSED');
763 fnd_message.set_token('NUMBER', x_number);
764 fnd_file.put_line( 1, fnd_message.get);
765
766 fnd_message.set_name('JL', 'JL_ZZ_GL_JE_PROCESSED');
767 fnd_message.set_token('NUMBER', x_number);
768 fnd_file.put_line( FND_FILE.OUTPUT, fnd_message.get);
769
770 COMMIT;
771
772 IF (p_import = 'Y' AND x_counter_sum <> 0) THEN
773
774 x_statement := 'IMPORT JE';
775 create_summary_journals := 'Y';
776 IF NOT(run_import_journal(x_counter_sum,
777 p_to_ledger,
778 x_je_source_tab_sum,
779 Summary_group_id,create_summary_journals)) THEN
780
781 RAISE UNABLE_TO_IMPORT;
782 END IF;
783 END IF;
784
785 IF (p_import = 'Y' AND x_counter_det <> 0) THEN
786 create_summary_journals := 'N';
787 IF NOT(run_import_journal(x_counter_det,
788 p_to_ledger,
789 x_je_source_tab_det,
790 Detail_group_id,create_summary_journals)) THEN
791
792 RAISE UNABLE_TO_IMPORT;
793 END IF;
794 END IF;
795 EXCEPTION
796 WHEN DIFF_CHART_OF_ACCT_ID THEN
797
798 fnd_message.set_name('JL', 'JL_ZZ_GL_DIFF_CHAT_OF_ACCT_ID');
799 fnd_message.set_token('TO_BOOK', x_to_ledger.name);
800 fnd_message.set_token('FROM_BOOK', x_from_ledger.name);
801 err_msg := fnd_message.get;
802 app_exception.raise_exception (exception_type => 'APP',
803 exception_code =>
804 jl_zz_fa_utilities_pkg.get_app_errnum('JL', 'JL_ZZ_GL_DIFF_CHAT_OF_ACCT_ID'),
805 exception_text => err_msg);
806
807 WHEN DIFF_CURRENCY_CODE THEN
808
809 fnd_message.set_name('JL', 'JL_ZZ_GL_DIFF_CURRENCY_CODE');
810 fnd_message.set_token('TO_BOOK', x_to_ledger.name);
811 fnd_message.set_token('FROM_BOOK', x_from_ledger.name);
812 err_msg := fnd_message.get;
813 app_exception.raise_exception (exception_type => 'APP',
814 exception_code =>
815 jl_zz_fa_utilities_pkg.get_app_errnum('JL', 'JL_ZZ_GL_DIFF_CURRENCY_CODE'),
816 exception_text => err_msg);
817
818 WHEN DIFF_CALENDAR THEN
819
820 fnd_message.set_name('JL', 'JL_ZZ_GL_DIFF_CALENDAR');
821 fnd_message.set_token('TO_BOOK', x_to_ledger.name);
822 fnd_message.set_token('FROM_BOOK', x_from_ledger.name);
823 err_msg := fnd_message.get;
824 app_exception.raise_exception (exception_type => 'APP',
825 exception_code =>
826 jl_zz_fa_utilities_pkg.get_app_errnum('JL', 'JL_ZZ_GL_DIFF_CALENDAR'),
827 exception_text => err_msg);
828
829 WHEN UNABLE_TO_IMPORT THEN
830
831 ROLLBACK;
832 fnd_message.set_name('JL', 'JL_ZZ_GL_CANNT_RUN_JOURNAL_IMP');
833 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
834 x_result := fnd_concurrent.set_completion_status( status => 'WARNING',
835 message => '');
836
837 WHEN OTHERS THEN
838
839 IF x_statement = 'SOB_INFO' THEN
840 fnd_message.set_name('JL', 'JL_ZZ_FA_EXEC_FAILURE');
841 fnd_message.set_token('NUMBER', '1');
842 err_msg := fnd_message.get;
843 ROLLBACK;
844 app_exception.raise_exception (exception_type => 'APP',
845 exception_code =>
846 jl_zz_fa_utilities_pkg.get_app_errnum('JL', 'JL_ZZ_FA_EXEC_FAILURE'),
847 exception_text => err_msg);
848
849 ELSIF x_statement = 'SEQ_INFO' THEN
850 fnd_message.set_name('JL', 'JL_ZZ_FA_EXEC_FAILURE');
851 fnd_message.set_token('NUMBER', '2');
852 err_msg := fnd_message.get;
853 ROLLBACK;
854 app_exception.raise_exception (exception_type => 'APP',
855 exception_code =>
856 jl_zz_fa_utilities_pkg.get_app_errnum('JL', 'JL_ZZ_FA_EXEC_FAILURE'),
857 exception_text => err_msg);
858
859 ELSIF x_statement = 'INS_GLIF' THEN
860 fnd_message.set_name('JL', 'JL_ZZ_FA_EXEC_FAILURE');
861 fnd_message.set_token('NUMBER', '3');
862 err_msg := fnd_message.get;
863 ROLLBACK;
864 app_exception.raise_exception (exception_type => 'APP',
865 exception_code =>
866 jl_zz_fa_utilities_pkg.get_app_errnum('JL', 'JL_ZZ_FA_EXEC_FAILURE'),
867 exception_text => err_msg);
868
869 ELSE
870 fnd_message.set_name('JL', 'JL_CO_FA_GENERAL_ERROR');
871 fnd_file.put_line( fnd_file.log, fnd_message.get);
872 err_num := SQLCODE;
873 err_msg := substr(SQLERRM, 1, 200);
874 ROLLBACK;
875 RAISE_APPLICATION_ERROR( err_num, err_msg);
876 END IF;
877
878 END;
879 ----------------------------------------------------------------------------
880 -- PROCEDURE --
881 -- find_who_columns --
882 -- --
883 -- DESCRIPTION --
884 -- Use this procedure to find the values for WHO columns. --
885 -- --
886 -- PURPOSE: --
887 -- Oracle Applications Rel 11.5 --
888 -- --
889 -- PARAMETERS: --
890 -- HISTORY: --
891 -- 08/12/98 Sujit Dalai Created --
892 ----------------------------------------------------------------------------
893
894 PROCEDURE find_who_columns IS
895
896 BEGIN
897
898 x_last_updated_by := fnd_global.user_id;
899 x_last_update_login := fnd_global.login_id;
900 x_request_id := fnd_global.conc_request_id;
901 x_program_application_id := fnd_global.prog_appl_id;
902 x_program_id := fnd_global.conc_program_id;
903 x_sysdate := SYSDATE;
904
905 IF x_debug THEN
906 fnd_file.put_line( 1, 'last_update_login:'||to_char(x_last_update_login));
907 fnd_file.put_line( 1, 'last_updated_by:'||to_char(x_last_updated_by));
908 fnd_file.put_line( 1, 'last_request_id:'||to_char(x_request_id));
909 fnd_file.put_line( 1, 'x_program_application_id :'||to_char(x_program_application_id ));
910 fnd_file.put_line( 1, 'x_program_id :'||to_char(x_program_id ));
911 fnd_file.put_line( 1, 'x_sysdate :'||to_char(x_sysdate ));
912
913 END IF;
914
915 END find_who_columns;
916
917 ----------------------------------------------------------------------------
918 -- FUNCTION --
919 -- run_import_journal --
920 -- --
921 -- DESCRIPTION --
922 -- Use this function to import journal --
923 -- --
924 -- PURPOSE: --
925 -- Oracle Applications Rel 11.5 --
926 -- --
927 -- HISTORY: --
928 -- 12/01/98 Sujit Dalai Created --
929 ----------------------------------------------------------------------------
930
931 FUNCTION run_import_journal(p_counter NUMBER,
932 p_sob NUMBER,
933 p_je_source_tab je_source_tab,
934 p_group_id NUMBER,
935 p_summary varchar2)
936 return boolean IS
937 x_interface_run_id NUMBER;
938 x_request_id NUMBER;
939 BEGIN
940 SELECT gl_journal_import_s.nextval
941 INTO x_interface_run_id
942 FROM sys.DUAL;
943 FOR i IN 1..p_counter LOOP
944
945 INSERT INTO gl_interface_control( je_source_name,
946 status,
947 interface_run_id,
948 group_id,
949 set_of_books_id,
950 packet_id)
951 VALUES( p_je_source_tab(i),
952 'S',
953 x_interface_run_id,
954 p_group_id,
955 p_sob,
956 NULL);
957
958 END LOOP;
959
960 x_request_id := fnd_request.submit_request('SQLGL',
961 'GLLEZL',
962 '',
963 '',
964 FALSE,
965 TO_CHAR(x_interface_run_id),
966 TO_CHAR(p_sob),
967 'N',
968 '',
969 '',
970 p_summary,
971 'Y',
972 chr(0),
973 '', '', '', '', '', '', '', '', '', '',
974 '', '', '', '', '', '', '', '', '', '',
975 '', '', '', '', '', '', '', '', '', '',
976 '', '', '', '', '', '', '', '', '', '',
977 '', '', '', '', '', '', '', '', '', '',
978 '', '', '', '', '', '', '', '', '', '',
979 '', '', '', '', '', '', '', '', '', '',
980 '', '', '', '', '', '', '', '', '', '',
981 '', '', '', '', '', '', '', '', '', '',
982 '', '');
983
984 IF x_request_id = 0 THEN
985 ROLLBACK;
986 RETURN (FALSE);
987 ELSE
988 COMMIT;
989 RETURN (TRUE);
990 END IF;
991 EXCEPTION
992 WHEN OTHERS THEN
993 RETURN (FALSE);
994
995 END run_import_journal;
996
997
998 PROCEDURE get_import_group_id(p_summary IN OUT NOCOPY NUMBER,p_detail IN OUT NOCOPY NUMBER) IS
999 BEGIN
1000 -------------------------------------
1001 -- Get Summary Journals Group Id. --
1002 -------------------------------------
1003
1004 SELECT gl_interface_control_s.nextval
1005 INTO p_summary
1006 FROM sys.dual;
1007
1008 -------------------------------------
1009 -- Get Detail Journals Group Id. --
1010 -------------------------------------
1011
1012 SELECT gl_interface_control_s.nextval
1013 INTO p_detail
1014 FROM sys.dual;
1015
1016 END get_import_group_id;
1017
1018 END jl_zz_gl_copy_je_pkg;