1 PACKAGE BODY XTR_ORACLE_FIN_INTERFACES_P as
2 /* $Header: xtrdistb.pls 120.4.12010000.3 2008/08/23 14:27:22 srsampat ship $ */
3
4 ------------------------------------------------------------------------------------------------------
5 PROCEDURE UPDATE_DR(in_company_code IN VARCHAR2,
6 in_batch_id IN NUMBER,
7 in_jnl_date IN DATE,
8 in_ccid IN NUMBER,
9 in_gl_link_id IN NUMBER,
10 in_ccy IN VARCHAR2,
11 in_alt_jrnl_date IN DATE) is
12
13 Begin
14
15 /* Private procedure to update debit journal rows within the given batch id. */
16 /* Updated columns are the transferred date and the link id used by the drilldown */
17 /* feature in GL which XTR does not have currently. */
18
19 Update XTR_JOURNALS
20 Set TRANSFER_TO_EXTERNAL_GL = trunc(sysdate),
21 gl_sl_link_id = in_gl_link_id,
22 alt_journal_date = in_alt_jrnl_date
23 where batch_id = in_batch_id
24 and journal_date = in_jnl_date
25 and code_combination_id = in_ccid
26 and currency = in_ccy
27 and (nvl(debit_amount,0) <> 0 or nvl(accounted_dr,0) <> 0);
28
29 End UPDATE_DR;
30 --------------------------------------------------------------------------------------------------------
31 PROCEDURE UPDATE_CR(in_company_code IN VARCHAR2,
32 in_batch_id IN NUMBER,
33 in_jnl_date IN DATE,
34 in_ccid IN NUMBER,
35 in_gl_link_id IN NUMBER,
36 in_ccy IN VARCHAR2,
37 in_alt_jrnl_date IN VARCHAR2) is
38 Begin
39
40 /* Private procedure to update credit journal rows within the given batch id. */
41
42 Update XTR_JOURNALS
43 Set TRANSFER_TO_EXTERNAL_GL = trunc(sysdate),
44 gl_sl_link_id = in_gl_link_id,
45 alt_journal_date = in_alt_jrnl_date
46 where batch_id = in_batch_id
47 and JOURNAL_DATE = in_jnl_date
48 and code_combination_id = in_ccid
49 and currency = in_ccy
50 and (nvl(credit_amount,0) <> 0 or nvl(accounted_cr,0) <> 0);
51
52 End UPDATE_CR;
53
54 /* ---------------------------------------------------------------------
55 | PRIVATE FUNCTION |
56 | GET_NEXT_OPEN_START_DATE
57 |
58 |
59 | Bug 4504734
60 | |
61 | DESCRIPTION |
62 | Function which will return the date of the next open period |
63 | provided for the given set of books ID.
64 |
65 | |
66 | CALLED BY |
67 | Procedure TRANSFER_JNLS |
68 | |
69 | PARAMETERS |
70 | in_sob_id sob id (in, required) |
71 | in_company_name company name (in, optional for msg use only) |
72 | in_jrnl_date date (in, required) |
73 | |
74 | HISTORY |
75 | 7/12/2006 eggarwa Created. |
76 |----------------------------------------------------------------------*/
77
78 FUNCTION GET_NEXT_OPEN_START_DATE (in_sob_id in number,in_company
79 varchar2,in_jrnl_date IN DATE) RETURN DATE IS
80
81 l_next_start DATE := to_date(null);
82
83 Begin
84
85 Select min(start_date) into l_next_start
86 from gl_period_statuses
87 where application_id = 101
88 and set_of_books_id = in_sob_id
89 and closing_status in ('O','F')
90 and adjustment_period_flag = 'N'
91 and start_date >= in_jrnl_date;
92
93
94 Return (l_next_start);
95 Exception
96
97 When others then
98 FND_MESSAGE.Set_Name ('XTR','XTR_NO_NEXT_OPEN_PERIOD');
99 FND_MESSAGE.Set_Token ('COMPANY_CODE', in_company);
100 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
101 Return to_date(null);
102
103 End GET_NEXT_OPEN_START_DATE;
104
105
106
107 /* ---------------------------------------------------------------------
108 | PRIVATE FUNCTION |
109 | In_Open_Period |
110 | |
111 | DESCRIPTION |
112 | Function which will return T/F value after determining if date |
113 | provided falls within an OPEN or FUTURE ENTRY journal period |
114 | for the given set of books ID. |
115 | |
116 | CALLED BY |
117 | Procedure TRANSFER_JNLS |
118 | |
119 | PARAMETERS |
120 | in_sob_id sob id (in, required) |
121 | in_sob_name sob name (in, optional for msg use only) |
122 | in_jrnl_date date (in, required) |
123 | |
124 | HISTORY |
125 | 6/26/2002 eklau Created. |
126 |----------------------------------------------------------------------*/
127
128 FUNCTION IN_OPEN_PERIOD (in_sob_id IN NUMBER,
129 in_sob_name IN VARCHAR2,
130 in_jrnl_date IN DATE) RETURN BOOLEAN IS
131 l_temp number;
132
133 Begin
134 Select 1 into l_temp
135 from gl_period_statuses
136 where application_id = 101
137 and set_of_books_id = in_sob_id
138 and adjustment_period_flag = 'N'
139 and closing_status in ('O','F')
140 and in_jrnl_date between start_date and end_date;
141
142 If (SQL%FOUND) then
143 return TRUE;
144 Else
145 return FALSE;
146 End If;
147
148 Exception
149 When NO_DATA_FOUND then
150 return FALSE;
151 When OTHERS then
152 FND_MESSAGE.Set_Name ('XTR','XTR_CHK_JRNL_DATE_ERROR');
153 FND_MESSAGE.Set_Token ('JNL_DATE', to_char(in_jrnl_date));
154 FND_MESSAGE.Set_Token ('SOB_NAME', in_sob_name);
155 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
156 return FALSE;
157 End;
158
159
160 /* ---------------------------------------------------------------------
161 | PUBLIC FUNCTION |
162 | Balance_Batch |
163 | |
164 | DESCRIPTION |
165 | Function which will return T/F value after determining if |
166 | batch is balanced, based on the sob cr/dr amounts. |
167 | T = Balanced batch. |
168 | F = Unbalanced batch. |
169 | CALLED BY |
170 | Procedure TRANSFER_JNLS |
171 | Form XTRACJNL |
172 | |
173 | PARAMETERS |
174 | in_batch_id batch id (in, required) |
175 | |
176 | HISTORY |
177 | 6/26/2002 eklau Created. |
178 |----------------------------------------------------------------------*/
179
180 FUNCTION BALANCE_BATCH (in_batch_id IN NUMBER) RETURN BOOLEAN IS
181
182 l_dummy varchar2(1) := null;
183
184 /* Bug 7233054 . Fixing rounding differences if any by adjusting the credit or debit
185 amounts . If Allow Unbalanced Journals is set to No , and if there is rounding difference
186 of .01 , then adjust credit/debit amounts to make it balanced */
187
188 l_journaldate Date := null;
189 l_diff number := null;
190 l_rowid VARCHAR2(2000) := null;
191 l_allow_unbal_jrnl varchar2(1) := null ;
192
193 Cursor CHK_ALLOW_UNBAL_JRNL is
194 Select
195 PARAMETER_VALUE_CODE
196 FROM XTR_COMPANY_PARAMETERS WHERE
197 COMPANY_CODE in (Select distinct company_code from xtr_journals
198 where batch_id = in_batch_id)
199 and parameter_code = 'ACCNT_UNBAL';
200
201 Cursor UPD_CREDIT(l_jrnl_date date) is
202 Select rowid from xtr_journals
203 Where batch_id = in_batch_id
204 and journal_date = l_jrnl_date and nvl(accounted_cr,0) <> 0
205 and accounted_cr = (Select min(accounted_cr) from xtr_journals
206 Where batch_id = in_batch_id
207 and journal_date = l_jrnl_date and nvl(accounted_cr,0) <> 0 );
208
209
210 Cursor UPD_DEBIT(l_jrnl_date date) is
211 Select rowid from xtr_journals
212 Where batch_id = in_batch_id
213 and journal_date = l_jrnl_date and nvl(accounted_dr,0) <> 0
214 and accounted_dr = (Select min(accounted_dr) from xtr_journals
215 Where batch_id = in_batch_id
216 and journal_date = l_jrnl_date and nvl(accounted_dr,0) <> 0 );
217
218
219 Cursor CHK_DIFF is
220 Select journal_date,
221 sum(nvl(accounted_dr,0)) - sum(nvl(accounted_cr,0)) difference
222 From XTR_JOURNALS
223 Where batch_id = in_batch_id
224 Group By journal_date
225 having sum(nvl(debit_amount,0)) = sum(nvl(credit_amount,0));
226
227 -- Bug 7233054 end
228
229 Cursor BALANCE is
230 Select null
231 From DUAL
232 Where exists
233 (Select null
234 From XTR_JOURNALS
235 Where batch_id = in_batch_id
236 Group By journal_date
237 Having sum(nvl(accounted_dr,0)) <> sum(nvl(accounted_cr,0)));
238
239 Begin
240 -- Bug 7233054 start
241 Open CHK_ALLOW_UNBAL_JRNL ;
242 Fetch CHK_ALLOW_UNBAL_JRNL into l_allow_unbal_jrnl ;
243 Close CHK_ALLOW_UNBAL_JRNL;
244 If(l_allow_unbal_jrnl = 'N') then
245 Open CHK_DIFF ;
246 loop
247 Fetch CHK_DIFF into l_journaldate,l_diff;
248 exit when CHK_DIFF%notfound;
249 if (abs(l_diff) = .01 ) then
250 if (l_diff < 0 ) then
251 Open UPD_DEBIT(l_journaldate);
252 fetch UPD_DEBIT into l_rowid;
253 close UPD_DEBIT;
254 update xtr_journals set accounted_dr=accounted_dr+.01
255 where rowid = l_rowid;
256 commit;
257 else
258 Open UPD_CREDIT(l_journaldate);
259 fetch UPD_CREDIT into l_rowid;
260 close UPD_CREDIT;
261 update xtr_journals set accounted_cr=accounted_cr+.01
262 where rowid = l_rowid;
263 commit;
264 end if;
265 end if;
266 end loop;
267 close CHK_DIFF;
268 end if;
269 -- Bug 7233054 End
270 Open BALANCE;
271 Fetch BALANCE into l_dummy;
272
273 If (BALANCE%NOTFOUND) then
274 Close BALANCE;
275 return TRUE;
276 Else
277 Close BALANCE;
278 return FALSE;
279 End If;
280
281 Exception
282 When OTHERS then
283 If (BALANCE%ISOPEN) then
284 Close BALANCE;
285 End If;
286 return FALSE;
287 End;
288
289
290 /* ---------------------------------------------------------------------
291 | PUBLIC FUNCTION |
292 | Get_Unbalance_Param |
293 | |
294 | DESCRIPTION |
295 | Function which will return the setting of the company parameter |
296 | 'Accounting - Allow Unbalanced Journal Transfer.' |
297 | Y = Allow transfer. |
298 | N = Do not allow transfer. |
299 | null = default as applicable by calling routine. |
300 | CALLED BY |
301 | Procedure TRANSFER_JNLS |
302 | Form XTRACJNL |
303 | |
304 | PARAMETERS |
305 | in_company company_code (in, required) |
306 | |
307 | HISTORY |
308 | 6/26/2002 eklau Created. |
309 |----------------------------------------------------------------------*/
310
311 FUNCTION GET_UNBALANCE_PARAM (in_company IN VARCHAR2) RETURN VARCHAR2 IS
312
313 l_param_code XTR_COMPANY_PARAMETERS.parameter_value_code%TYPE := 'N';
314
315 Cursor UNBAL_PARAM is
316 Select parameter_value_code
317 from XTR_COMPANY_PARAMETERS
318 where company_code = in_company
319 and parameter_code = 'ACCNT_UNBAL';
320 --
321
322 Begin
323 Open UNBAL_PARAM;
324 Fetch UNBAL_PARAM into l_param_code;
325 Close UNBAL_PARAM;
326
327 return (l_param_code);
328
329 Exception
330 When OTHERS then
331 return (l_param_code);
332 End;
333
334
335 /* ---------------------------------------------------------------------
336 | PUBLIC PROCEDURE |
337 | Transfer_Jnls |
338 | |
339 | DESCRIPTION |
340 | Procedure which will transfer/re-transfer the given batch id |
341 | from the XTR_JOURNALS table to the GL_INTERFACE table. |
342 | |
343 | CALLED BY |
344 | Procedure XTR_JOURNAL_PROCESS_P.Journals. |
345 | |
346 | PARAMETERS |
347 | in_company_code company code (in, required) |
348 | in_batch_id batch id (in, required) |
349 | in_closed_periods closed per param (in, optional) |
350 | CLOSED - No change, post to closed period. |
351 | NXTOPEN - Change journal date to start date of next open GL |
352 | period. |
353 | null --- Unable to locate the company param setting. |
354 | If journals in closed periods are found, transfer |
355 | transfer of batch will be aborted. |
356 | |
357 | HISTORY
358 | Bug 4504734 Removed parameter in_next_open_start |
359 |----------------------------------------------------------------------*/
360
361 PROCEDURE TRANSFER_JNLS(
362 errbuff OUT NOCOPY VARCHAR2,
363 retcode OUT NOCOPY NUMBER,
364 in_company_code IN VARCHAR2,
365 in_batch_id IN NUMBER,
366 in_closed_periods IN VARCHAR2) IS
367 --
368 -- Public procedure which will create Journal Records in the
369 -- Oracle Financials GL_INTERFACE Table
370 --
371
372 l_set_of_books gl_sets_of_books.set_of_books_id%TYPE := to_number(NULL);
373 l_trx_param xtr_company_parameters.parameter_value_code%TYPE;
374 l_source_name gl_je_sources.user_je_source_name%TYPE;
375 l_category_name gl_je_categories.user_je_category_name%TYPE;
376 l_xchange_type gl_daily_conversion_types.user_conversion_type%TYPE;
377 l_gl_link_id XTR_JOURNALS.gl_sl_link_id%TYPE;
378 l_gl_group_id GL_INTERFACE.group_id%TYPE; -- add to record group id when tranfer
379 p_company_code XTR_PARTY_INFO.party_code%TYPE;
380 l_rowid VARCHAR2(30);
381
382 l_bal_flag XTR_COMPANY_PARAMETERS.parameter_value_code%TYPE := NULL;
383 l_ok_to_xfer BOOLEAN := FALSE;
384 l_sob_name GL_SETS_OF_BOOKS.name%TYPE := NULL;
385 l_journal_date DATE := to_date(null);
386 l_next_open_start DATE := to_date(null);
387
388 --
389 cursor SOB_ID is
390 select PTY.set_of_books_id, SOB.name
391 from XTR_PARTIES_V PTY,
392 GL_SETS_OF_BOOKS SOB
393 where PTY.set_of_books_id = SOB.set_of_books_id
394 and PTY.party_code = p_company_code;
395 --
396 cursor TRX_PARAM is
397 select parameter_value_code
398 from XTR_COMPANY_PARAMETERS
399 where company_code = p_company_code
400 and parameter_code = 'ACCNT_JNTRM';-- determine the transfer method: SUMMARY or DETAIL
401
402 cursor JNL_SUMMARY is
403 select null row_id,
404 batch_id batch_id,
405 journal_date journal_date,
406 code_combination_id ccid,
407 currency currency,
408 sum(nvl(debit_amount,0)) debit,
409 0 credit,
410 sum(nvl(accounted_dr,0)) acct_dr,
411 0 acct_cr,
412 to_number(null) trans_number,
413 null date_type,
414 to_number(null) deal_number,
415 null amount_type,
416 null action_code,
417 null deal_type,
418 null deal_subtype,
419 null product_type,
420 null portfolio_code
421 from XTR_JOURNALS
422 where batch_id = G_batch_id
423 and (nvl(debit_amount,0) <> 0 or nvl(accounted_dr,0) <> 0)
424 group by batch_id, journal_date, code_combination_id, currency
425 UNION
426 select null row_id,
427 batch_id batch_id,
428 journal_date journal_date,
429 code_combination_id ccid,
430 currency currency,
431 0 debit,
432 sum(nvl(credit_amount,0)) credit,
433 0 acct_dr,
434 sum(nvl(accounted_cr,0)) acct_cr,
435 to_number(null) trans_number,
436 null date_type,
440 null deal_type,
437 to_number(null) deal_number,
438 null amount_type,
439 null action_code,
441 null deal_subtype,
442 null product_type,
443 null portfolio_code
444 from XTR_JOURNALS
445 where batch_id = G_batch_id
446 and (nvl(credit_amount,0) <> 0 or nvl(accounted_cr,0) <> 0)
447 group by batch_id, journal_date, code_combination_id, currency;
448 --
449 cursor JNL_DETAIL is
450 select rowid row_id,
451 batch_id batch_id,
452 journal_date journal_date,
453 code_combination_id ccid,
454 currency currency,
455 (nvl(debit_amount,0)) debit,
456 nvl(credit_amount,0) credit,
457 (nvl(accounted_dr,0)) acct_dr,
458 nvl(accounted_cr,0) acct_cr,
459 transaction_number trans_number,
460 date_type date_type,
461 deal_number deal_number,
462 amount_type amount_type,
463 action_code action_code,
464 deal_type deal_type,
465 deal_subtype deal_subtype,
466 product_type product_type,
467 portfolio_code portfolio_code
468 from XTR_JOURNALS
469 where batch_id = G_batch_id
470 and (nvl(debit_amount,0) <> 0 or nvl(accounted_dr,0) <> 0 or nvl(credit_amount,0) <> 0 or nvl(accounted_cr,0) <> 0);
471 --
472 JNL_REC JNL_DETAIL%ROWTYPE;
473 --
474 cursor SOURCE_NAME is
475 SELECT user_je_source_name
476 FROM gl_je_sources
477 WHERE je_source_name = 'Treasury';
478 --
479 cursor CATEGORY_NAME is
480 select user_je_category_name
481 from gl_je_categories
482 where je_category_name = 'Treasury';
483 --
484 cursor EXCHANGE_TYPE is
485 select user_conversion_type
486 from gl_daily_conversion_types
487 where conversion_type = (select conversion_type
488 from xtr_parties_v
489 where party_code = p_company_code);
490 --
491 cursor GL_GROUP_ID is
492 select gl_interface_control_s.nextval
493 from dual;
494 --
495
496 BEGIN
497
498 SAVEPOINT sp_transfer;
499
500 -- return code: 0 - success, 1 - warning, 2 - error.
501 retcode := 0;
502
503 p_company_code := Upper(in_company_code);
504 G_batch_id := in_batch_id;
505
506 FND_MESSAGE.Set_Name ('XTR','XTR_START_XFER_JRNL_BATCH');
507 FND_MESSAGE.Set_Token ('COMPANY_CODE', p_company_code);
508 FND_MESSAGE.Set_Token ('BID', to_char(in_batch_id));
509 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
510
511 --
512 Begin
513 Open SOB_ID;
514 Fetch SOB_ID INTO l_set_of_books, l_sob_name;
515 Close SOB_ID;
516 Exception
517 When Others then
518 FND_MESSAGE.Set_Name ('XTR','XTR_NO_SOB');
519 FND_MESSAGE.Set_Token ('COMPANY_CODE', p_company_code);
520 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
521 retcode := greatest(retcode,2);
522 l_ok_to_xfer := FALSE;
523 End;
524
525 -- Obtain unbalanced journal batches transfer handling from company params.
526 -- If no parameter is found, then any unbalanced batches will NOT be transferred.
527
528 l_bal_flag := XTR_ORACLE_FIN_INTERFACES_P.Get_Unbalance_Param(p_company_code);
529 If (l_bal_flag is null) then
530 FND_MESSAGE.Set_Name ('XTR','XTR_NO_UNBAL_JRNL_PARAM');
531 FND_MESSAGE.Set_Token ('COMPANY_CODE', p_company_code);
532 FND_MESSAGE.Set_Token ('BID', in_batch_id);
533 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
534 retcode := greatest(retcode,1);
535 l_bal_flag := 'N';
536 End if;
537
538 -- If company is setup to not transfer unbalanced batches, check balance.
539 -- Y = Yes, allow transfer.
540 -- N = No, do not allow transfer.
541
542 If (nvl(l_bal_flag, 'N') = 'N') then
543 If (XTR_ORACLE_FIN_INTERFACES_P.Balance_Batch(in_batch_id)) then
544 l_ok_to_xfer := TRUE;
545 Else
546 FND_MESSAGE.Set_Name ('XTR','XTR_UNBAL_JRNL_ERROR');
547 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
548 retcode := greatest(retcode,2);
549 l_ok_to_xfer := FALSE;
550 End If;
551 Else
552 l_ok_to_xfer := TRUE;
553 End If;
554
555 -- Journal batch is either in balance or unbalanced transfer is allowed.
556
557 If (l_ok_to_xfer) then
558
559 Open SOURCE_NAME;
560 Fetch SOURCE_NAME INTO l_source_name;
561 Close SOURCE_NAME;
562
563 Open CATEGORY_NAME;
564 Fetch CATEGORY_NAME INTO l_category_name;
565 Close CATEGORY_NAME;
566
567 Open EXCHANGE_TYPE;
568 Fetch EXCHANGE_TYPE INTO l_xchange_type;
569 Close EXCHANGE_TYPE;
570
571 Open GL_GROUP_ID;
572 Fetch GL_GROUP_ID into l_gl_group_id;
573 Close GL_GROUP_ID;
574
575 Open TRX_PARAM;
576 Fetch TRX_PARAM into l_trx_param;
577 Close TRX_PARAM;
578
579 If l_trx_param = 'DETAIL' then
583 End If;
580 Open JNL_DETAIL;
581 Else
582 Open JNL_SUMMARY;
584
585 LOOP
586 If l_trx_param = 'DETAIL' then
587 Fetch JNL_DETAIL INTO JNL_REC;
588
589 EXIT WHEN JNL_DETAIL%NOTFOUND;
590 Else
591 Fetch JNL_SUMMARY INTO JNL_REC;
592 EXIT WHEN JNL_SUMMARY%NOTFOUND;
593 End if;
594
595 Select XTR_AE_LINK_ID_S.nextval
596 into l_gl_link_id
597 from dual;
598
599 --
600 -- Three possible values for the parameter in_closed_periods:
601 -- CLOSED - No change, post to closed period.
602 -- NXTOPEN - Change journal date to start date of next open GL period.
603 -- null --- Unable to locate the company parameter setting. If
604 -- journals in closed periods are found, transfer of batch
605 -- be aborted.
606 --
607
608 l_journal_date := JNL_REC.journal_date;
609
610 If (nvl(in_closed_periods, 'NXTOPEN') = 'NXTOPEN') then
611 If (NOT IN_OPEN_PERIOD (l_set_of_books, l_sob_name, JNL_REC.journal_date)) then
612
613 -- Two possible values for the parameter in_next_open_start.
614 -- Valid Date - Use this date as the journal date.
615 -- null ------- N/A if in_closed_periods is not 'NXTOPEN', but transfer
616 -- of batch will be aborted if in_closed_periods is 'NXTOPEN'.
617
618 -- bug 4504734
619 l_next_open_start := GET_NEXT_OPEN_START_DATE (l_set_of_books,in_company_code, jnl_rec.journal_date);
620
621 If (in_closed_periods is null) then
622 FND_MESSAGE.Set_Name ('XTR','XTR_XFER_NO_CLOSE_PER_PARAM');
623 FND_MESSAGE.Set_Token ('JNL_DATE', to_char(JNL_REC.journal_date,'DD-MON-YYYY'));
624 FND_MESSAGE.Set_Token ('COMPANY_CODE', p_company_code);
625 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
626 retcode := greatest(retcode,2);
627 l_ok_to_xfer := FALSE;
628
629 Elsif (l_next_open_start is null) then
630 FND_MESSAGE.Set_Name ('XTR','XTR_XFER_NO_NEXT_PERIOD');
631 FND_MESSAGE.Set_Token ('JNL_DATE', to_char(JNL_REC.journal_date,'DD-MON-YYYY'));
632 FND_MESSAGE.Set_Token ('SOB_NAME', l_sob_name);
633 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
634 retcode := greatest(retcode,2);
635 l_ok_to_xfer := FALSE;
636
637 Elsif (l_next_open_start is not null) then
638
639 -- Set the journal date to be the period start date of the next open GL period.
640 -- NOTE: Although the journal date is being adjusted, the exchange rate date
641 -- does not change and thereby the debit/credit amounts in SOB currency
642 -- will not change. They will be based on the original transaction date.
643 -- This is in accordance with how AP handles "sweeping" of closed period
644 -- journals into the next open period.
645
646 l_journal_date := l_next_open_start;
647 End If; -- [in_closed_period...]
648 End If; -- [not in_open_period...]
649 End If; -- [in_closed_period...]
650
651 If (l_ok_to_xfer) then
652
653 -- Continue to transfer info into the GL_Interface table.
654
655 Insert into GL_INTERFACE(
656 status,
657 set_of_books_id,
658 code_combination_id,
659 user_je_source_name,
660 user_je_category_name,
661 accounting_date,
662 currency_code,
663 date_created,
664 created_by,
665 actual_flag,
666 entered_dr,
667 entered_cr,
668 currency_conversion_date,
669 user_currency_conversion_type,
670 accounted_dr,
671 accounted_cr,
672 gl_sl_link_id,
673 group_id,
674 reference21,
675 reference22,
676 reference23,
677 reference24,
678 reference25,
679 reference26,
680 reference27,
681 reference28,
682 reference29,
683 reference30)
684 Values ('NEW',
685 l_set_of_books,
686 JNL_REC.ccid,
687 l_source_name,
688 l_category_name,
689 l_journal_date,
690 JNL_REC.currency,
691 trunc(sysdate),
692 nvl(fnd_global.user_id,-1),
693 'A',
694 JNL_REC.debit,
695 JNL_REC.credit,
696 JNL_REC.journal_date,
697 l_xchange_type,
698 JNL_REC.acct_dr,
699 JNL_REC.acct_cr,
700 l_gl_link_id,
701 l_gl_group_id,
702 JNL_REC.batch_id,
703 JNL_REC.trans_number,
704 JNL_REC.date_type,
705 JNL_REC.deal_number,
706 JNL_REC.amount_type,
707 JNL_REC.action_code,
708 JNL_REC.deal_type,
709 JNL_REC.deal_subtype,
710 JNL_REC.product_type,
711 JNL_REC.portfolio_code);
712
713 -- Update XTR_JOURNALS table. Mark transferred rows.
714
715 If l_trx_param = 'DETAIL' then
716 Update XTR_JOURNALS
717 Set TRANSFER_TO_EXTERNAL_GL = trunc(sysdate),
718 gl_sl_link_id = l_gl_link_id,
719 alt_journal_date = l_journal_date -- bug 3461138
720 where rowid = JNL_REC.row_id;
721 Else
722 If (l_journal_date = JNL_REC.journal_date) then
723 l_journal_date := to_date(null);
724 End If;
725
726 If (JNL_REC.debit <> 0 or JNL_REC.acct_dr <> 0) then
727 UPDATE_DR (in_company_code,
728 JNL_REC.batch_id,
729 JNL_REC.journal_date,
730 JNL_REC.ccid,
731 l_gl_link_id,
732 JNL_REC.currency,
733 l_journal_date);
734 Else
735 UPDATE_CR (in_company_code,
736 JNL_REC.batch_id,
737 JNL_REC.journal_date,
738 JNL_REC.ccid,
739 l_gl_link_id,
740 JNL_REC.currency,
741 l_journal_date);
742 End If; -- [debit/credit <> 0 ...]
743 End if; -- [l_trx_param ...]
744 End If; -- [l_ok_to_xfer ... after closed period handling check]
745 END LOOP; -- [loop processing of xtr_journals recrods ...]
746
747 If l_trx_param = 'DETAIL' then
748 Close JNL_DETAIL;
749 Else
750 Close JNL_SUMMARY;
751 End if;
752
753 -- Update XTR_BATCHES table to put the GL_GROUP_ID in corresponding BATCH_ID
754 -- if batch has been successfully transferred.
755
756 If (l_ok_to_xfer) then
757 Update XTR_BATCHES
758 Set GL_GROUP_ID = l_gl_group_id
759 Where BATCH_ID = in_batch_id;
760
761 -- Update XTR_BATCH_EVENTS table.Set authorized information once journal is transferred
762
763 Update XTR_BATCH_EVENTS
764 Set AUTHORIZED = 'Y',
765 AUTHORIZED_BY = fnd_global.user_id,
766 AUTHORIZED_ON = trunc(sysdate)
767 Where batch_id = in_batch_id
768 And event_code = 'JRNLGN';
769
770 Commit;
771 End If; -- [l_ok_to_xfer... update batch tables after successful xfer]
772 End If; -- [l_ok_to_xfer... after validating "balanceness" of batch]
773
774 If (NOT l_ok_to_xfer) then
775 ROLLBACK TO SAVEPOINT sp_transfer;
776 End If;
777
778 FND_MESSAGE.Set_Name ('XTR','XTR_END_XFER_JRNL_BATCH');
779 FND_MESSAGE.Set_Token ('COMPANY_CODE', p_company_code);
780 FND_MESSAGE.Set_Token ('BID', to_char(in_batch_id));
781 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
782
783 Exception
784 When Others then
785 FND_MESSAGE.Set_Name ('XTR','XTR_XFER_UNHANDLED_ERROR');
786 FND_MESSAGE.Set_Token ('COMPANY_CODE', p_company_code);
787 FND_MESSAGE.Set_Token ('BID', to_char(in_batch_id));
788 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
789
790 ROLLBACK TO SAVEPOINT sp_transfer;
791 retcode := 2;
792 END TRANSFER_JNLS;
793 --------------------------------------------------------------------------------------------------------------
794
795 end XTR_ORACLE_FIN_INTERFACES_P;
[Home] [Help]