[Home] [Help]
PACKAGE BODY: APPS.XTR_JOURNAL_PROCESS_P
Source
1 PACKAGE BODY XTR_JOURNAL_PROCESS_P as
2 /* $Header: xtrjrnlb.pls 120.30.12020000.2 2013/03/25 09:57:34 nipant ship $ */
3 ----------------------------------------------------------------------------------------------------------------
4 /* Impotant Notes
5 The insert of the revaluation row (In procedure CALC_REVAL_PROCESS) is
6 not being currently called from NEW_TRANSFER_TO_GL until we clear up
7 how / what we are revaluing and if we should be creating Journals for each G/L contra
8 agst REVAL G/L (all flagged ready for reversal) - should we only be reval g/ls after closeout
9 to the Balance Sheet (ie only Asset / Liab A/c's should be revalued)
10 */
11
12 --------------------------------------------------------------------------------------------------------------------------
13
14 debug_flag varchar2(1) := 'F';
15
16 /* ---------------------------------------------------------------------
17 | PRIVATE FUNCTION |
18 | GET_IG_CCID |
19 | |
20 | DESCRIPTION |
21 | Private function to obtain principal or interest ccid for a IG |
22 | transaction from the xtr_ig_journal_structures table. |
23 | |
24 | CALLED BY |
25 | Gen_Journals |
26 | |
27 | PARAMETERS |
28 | in_prin_flag get principal ccid flag. |
29 | in_int_flag get interest ccid flag. |
30 | in_company_code company code. |
31 | in_cpty counter party code. |
32 | in_curr deal and cpty's bank account currency. |
33 | in_bank_acct_no counter party's bank account number. |
34 | |
35 | HISTORY |
36 | 02/19/02 eklau Created. |
37 --------------------------------------------------------------------- */
38
39
40
41 FUNCTION GET_IG_CCID (
42 in_prin_flag in varchar2,
43 in_int_flag in varchar2,
44 in_company_code in varchar2,
45 in_cpty in varchar2,
46 in_curr in varchar2,
47 in_bank_acct_no in varchar2) RETURN NUMBER IS
48
49 l_ccid number(15) := null;
50
51 Begin
52 Select decode(nvl(in_prin_flag,'N'),
53 'Y', principal_gl,
54 (decode(nvl(in_int_flag,'N'), 'Y', interest_gl, NULL)))
55 into l_ccid
56 From xtr_ig_journal_structures
57 Where company_code = in_company_code
58 and cparty_code = in_cpty
59 and cp_currency = in_curr
60 and cp_acct_no = in_bank_acct_no;
61
62 Return (l_ccid);
63 /* exception added by Ilavenil to fix bug # 2293339 issue 9 */
64 Exception
65 When others then
66 Return null;
67 End GET_IG_CCID;
68
69
70 /* -----------------------------------------------------------------------------
71 | PUBLIC FUNCTION |
72 | GET_CLOSED_PERIOD_PARAM |
73 | |
74 | DESCRIPTION |
75 | This function will return the company parameter setting for posting |
76 | journal batches to closed GL periods. |
77 | CALLED BY |
78 | Procedure Journals. |
79 | Form XTRACJNL. |
80 | PARAMETERS |
81 | in_company_ company code. (input, required) |
82 | HISTORY |
83 | 06/21/2002 eklau Created |
84 ----------------------------------------------------------------------------- */
85
86 FUNCTION GET_CLOSED_PERIOD_PARAM (in_company in varchar2) RETURN VARCHAR2 IS
87
88 l_param_value XTR_COMPANY_PARAMETERS.parameter_value_code%TYPE := NULL;
89
90 Begin
91 Select parameter_value_code into l_param_value
92 from xtr_company_parameters
93 where company_code = in_company
94 and parameter_code = 'ACCNT_CLPER';
95
96 Return (l_param_value);
97 Exception
98 When others then
99 FND_MESSAGE.Set_Name ('XTR','XTR_NO_CLOSE_PER_PARAM');
100 FND_MESSAGE.Set_Token ('COMPANY_CODE', in_company);
101 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
102 Return null;
103 End GET_CLOSED_PERIOD_PARAM;
104
105
106
107 /* --------------------------------------------------------------------- --------
108 | PUBLIC PROCEDURE |
109 | Do_Journal_Process |
110 | |
111 | DESCRIPTION |
112 | This procedure generate the G/L entries for a single or all |
113 | companies the user has access to for transactions up to the |
114 | cutoff date. |
115 | CALLED BY |
116 | Concurrent program submission manager. |
117 | PARAMETERS |
118 | errbuf (out) |
119 | retcode (out) |
120 | p_source_option NRA/RA related batch (in, optional) |
121 | 'J' - Non-Reval/Non-Accrual related |
122 | null - Reval/Accrual related |
123 | p_company_code company code. (in, required) |
124 | p_batch_id_from batch id low value (in, dependant) |
125 | p_batch_id_to batch id high value (in, dependant) |
126 | p_cutoff_date date. (in, dependant) |
127 |
128 | HISTORY |
129 | 12/16/98 eklau Created |
130 | 01/21/99 eklau (1) Change source of suspense GL acct |
131 | from gl_reference to party_info table. |
132 | (2) Added a 'suspense_gl' column to the |
133 | journals table and populate it with 'Y' |
134 | if the company's suspense account was |
135 | used to create the journal entry. |
136 | 04/29/99 eklau (1) Removed acct period from parameters. |
137 | Generation process will now work off a |
138 | cutoff date, only DDA rows marked as not |
139 | having jrnls generated (journal_created |
140 | = 'N') on or before the cutoff date |
141 | will be processed. |
142 | (2) Removed procedure REVERSE_JOURNALS |
143 | from pkg. Journals are now updated on |
144 | a real-time basis from DB trigger call |
145 | to the procedure UPDATE_JOURNALS, when |
146 | a deal is cancelled. |
147 | 05/24/99 eklau (1) Added p_dummy_date parameter for |
148 gen_journals| use by concurrent prg validation only. |
149 | 03/12/01 jhung (1) Add p_period_end,p_batch_id_from, |
150 | p_batch_id_to, remove p_cutoff_date |
151 | parameter for introduction of batch_id |
152 | concept. |
153 ----------------------------------------------------------------------------- */
154 PROCEDURE Do_Journal_Process
155 (errbuf OUT NOCOPY VARCHAR2,
156 retcode OUT NOCOPY NUMBER,
157 p_source_option IN VARCHAR2,
158 p_company_code IN VARCHAR2,
159 p_batch_id_from IN NUMBER,
160 p_batch_id_to IN NUMBER,
161 p_cutoff_date IN DATE) IS
162 --
163 p_curr_batch_id XTR_BATCHES.BATCH_ID%TYPE;
164 p_upgrade_batch XTR_BATCHES.UPGRADE_BATCH%TYPE;
165 p_period_start DATE;
166 p_period_end DATE;
167
168
169 CURSOR BATCH_SEQ is
170 Select batch_id
171 From XTR_BATCHES
172 Where company_code = p_company_code
173 and batch_type is null
174 and batch_id between nvl(p_batch_id_from, batch_id) and nvl(p_batch_id_to, batch_id)
175 Order by batch_id asc;
176
177 CURSOR BATCH_PERIOD is
178 Select period_start, period_end, upgrade_batch
179 From XTR_BATCHES
180 Where batch_id = p_curr_batch_id;
181
182 CURSOR FIND_USER (fnd_user_id in number) is
183 select dealer_code
184 from xtr_dealer_codes_v
185 where user_id = fnd_user_id;
186 --
187 CURSOR GET_SOB_INFO is
188 select set_of_books_id
189 from xtr_parties_v
190 where party_type = 'C'
191 and party_code = p_company_code;
192 --
193 CURSOR GET_SUSPENSE_CCID is
194 select parameter_value_code -- replaced a.suspense_ccid
195 from xtr_parties_v a,
196 xtr_company_parameters b
197 where a.party_code = p_company_code
198 and a.party_code = b.company_code
199 and b.parameter_code = 'ACCNT_SUSAC';
200 --
201 CURSOR FIND_PRE_BATCH is -- Find the previous Non-Reval/Non-Accrual related Batch ID
202 select pre.batch_id
203 from XTR_BATCHES PRE,
204 XTR_BATCHES CUR
205 where cur.company_code = p_company_code
206 and cur.batch_id = p_curr_batch_id
207 and pre.company_code = cur.company_code
208 and cur.period_start = (pre.period_end + 1)
209 and pre.upgrade_batch <> 'Y'
210 and pre.batch_type is null;
211
212 l_pre_batch_id XTR_BATCHES.BATCH_ID%TYPE;
213
214 CURSOR CHK_EARLY_BATCH is -- chck if the early batch id been generated journal
215 select 1
216 from XTR_BATCH_EVENTS
217 where batch_id = l_pre_batch_id
218 and EVENT_CODE = 'JRNLGN';
219 --
220 CURSOR CHK_ACCRLS_AUTH is -- check if the batch id has been authorized from accruals
221 select 1
222 from XTR_BATCH_EVENTS
223 where batch_id = p_curr_batch_id
224 and event_code = 'ACCRUAL'
225 and authorized = 'Y';
226 --
227 CURSOR CHK_JOURNAL_GEN is -- check if the batch id has been generated in journals
228 select 1
229 from XTR_BATCH_EVENTS
230 where batch_id = p_curr_batch_id
231 and event_code = 'JRNLGN';
232
233 fnd_user_id number;
234 pg_fp utl_file.file_type;
235 l_temp NUMBER;
236 ex_early_batch EXCEPTION;
237 ex_accrls_auth EXCEPTION;
238 ex_journal_gen EXCEPTION;
239
240 -- Added for flex journals. 2404342. Creation of a new journal only batch.
241
242 Cursor GEN_BATCH is
243 Select XTR_BATCHES_S.NEXTVAL
244 from DUAL;
245
246 l_retcode NUMBER := 0;
247 l_warn_flag BOOLEAN;
248 l_sub_retcode NUMBER := 0; -- Added for 1336492.
249
250 BEGIN
251
252 -- xtr_debug_pkg.enable_file_debug; -- RV 2293339 issue# 8
253 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
254 xtr_debug_pkg.debug ('Do_Journal_Process: ' || '>> Do Journal Process.');
255 END IF;
256
257 If (p_source_option is null) then
258
259 -- Process Reval/Accrual related batches. Range of bid should be provided.
260 -- If no range of batch id, all authorized accrual batches are to be processed.
261
262 Open BATCH_SEQ; -- Generate Journal. Start from smallest ID.
263 Fetch BATCH_SEQ into p_curr_batch_id;
264 While BATCH_SEQ%FOUND LOOP
265
266 -- Added for flex journals. 2404342. ekl
267 -- Issue info message at beginning of each batch being processed.
268
269 FND_MESSAGE.Set_Name ('XTR','XTR_START_GEN_BID');
270 FND_MESSAGE.Set_Token ('BID', p_curr_batch_id);
271 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
272
273 -- End addition for 2404342.
274
275 Open BATCH_PERIOD;
276 Fetch BATCH_PERIOD into p_period_start, p_period_end, p_upgrade_batch;
277 Close BATCH_PERIOD;
278
279 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
280 xtr_debug_pkg.debug('Do_Journal_Process: ' || 'p_company_code = ' || p_company_code);
281 xtr_debug_pkg.debug('Do_Journal_Process: ' || 'p_curr_batch_id = ' || p_curr_batch_id);
282 xtr_debug_pkg.debug('Do_Journal_Process: ' || 'p_period_start = ' || p_period_start);
283 xtr_debug_pkg.debug('Do_Journal_Process: ' || 'p_period_end = ' || p_period_end);
284 END IF;
285
286 -- Issue warning if journals have already been generated for the current batch id.
287 -- Proceed to next batch id in range.
288
289 Open CHK_JOURNAL_GEN;
290 Fetch CHK_JOURNAL_GEN into l_temp;
291 If CHK_JOURNAL_GEN%FOUND then
292 Close CHK_JOURNAL_GEN;
293 FND_MESSAGE.Set_Name('XTR', 'XTR_JOURNAL_GEN');
294 FND_MESSAGE.Set_Token('BATCH', p_curr_batch_id);
295 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
296 l_retcode := greatest(l_retcode,1);
297 l_warn_flag := TRUE;
298 Else
299 Close CHK_JOURNAL_GEN;
300 l_warn_flag := FALSE;
301 End if;
302
303 -- Added condition for flex jrnls. 2404342. ekl
304 -- With this logic, if a previously processed RA batch was encountered,
305 -- a warning would be issued and processing can continue with the next
306 -- batch ID in the given range.
307
308 If (NOT l_warn_flag) then
309
310 -- Raise error if the previous batch id has not generated journal
311 Open FIND_PRE_BATCH;
312 Fetch FIND_PRE_BATCH into l_pre_batch_id;
313 If FIND_PRE_BATCH%FOUND then
314 Open CHK_EARLY_BATCH;
315 Fetch CHK_EARLY_BATCH into l_temp;
316 If CHK_EARLY_BATCH%NOTFOUND then
317 Close CHK_EARLY_BATCH;
318 Raise ex_early_batch;
319 Else
320 Close CHK_EARLY_BATCH;
321 End if;
322 End If;
323 Close FIND_PRE_BATCH;
324
325 -- Raise error if the current batch is not authorized from Accruals
326 Open CHK_ACCRLS_AUTH;
327 Fetch CHK_ACCRLS_AUTH into l_temp;
328 If CHK_ACCRLS_AUTH%NOTFOUND then
329 CLOSE CHK_ACCRLS_AUTH;
330 Raise ex_accrls_auth;
331 Else
332 CLOSE CHK_ACCRLS_AUTH;
333 End if;
334
335 G_company_code := p_company_code;
336 G_batch_id := p_curr_batch_id;
337 G_period_end := p_period_end;
338 --
339 -- Set the dealer code
340 --
341 fnd_user_id := FND_GLOBAL.USER_ID;
342 Open FIND_USER(fnd_user_id);
343 Fetch FIND_USER into G_user;
344 Close FIND_USER;
345
346 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
347 xtr_debug_pkg.debug ('Do_Journal_Process: ' || 'FND_GLOBAL.user_id = ' || to_char(fnd_user_id));
348 END IF;
349 --
350 -- Set sob info.
351 --
352 Open GET_SOB_INFO;
353 Fetch GET_SOB_INFO into G_set_of_books_id;
354 Close GET_SOB_INFO;
355
356 Open GET_SUSPENSE_CCID;
357 Fetch GET_SUSPENSE_CCID into G_suspense_ccid;
358 Close GET_SUSPENSE_CCID;
359
360 -- Begin 1336492 additions.
361 -- Modified returned value type and meaning of Procedure Gen_Journals
362 -- in order to handle warning status in addition to successful and error.
363
364 l_sub_retcode := Gen_Journals(p_source_option, G_company_code, G_batch_id, G_period_end, p_upgrade_batch);
365 l_retcode := greatest(l_retcode,l_sub_retcode);
366
367 -- End 1336492 additions.
368
369 /* replaced by code above for 1336492.
370 If (NOT Gen_Journals(p_source_option, G_company_code, G_batch_id, G_period_end, p_upgrade_batch)) then
371 l_retcode := greatest(l_retcode,2);
372 --bug 2804548
373 else
374 l_retcode := greatest(l_retcode,g_gen_journal_retcode);
375 End If;
376 */
377 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
378 xtr_debug_pkg.debug ('Do_Journal_Process: ' || '<< Do Journal Process.');
379 END IF;
380 -- xtr_debug_pkg.disable_file_debug; -- RV 2293339 issue# 8
381
382 End If; -- (NOT l_warn_flag)
383
384 -- Added for flex journals. 2404342. ekl
385 -- Issue info message at end of each batch being processed.
386
387 FND_MESSAGE.Set_Name ('XTR','XTR_END_GEN_BID');
388 FND_MESSAGE.Set_Token ('BID', p_curr_batch_id);
389 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
390
391 -- End addition for 2404342.
392
393 Fetch BATCH_SEQ into p_curr_batch_id;
394 END LOOP;
395 Close BATCH_SEQ;
396
397 Elsif (p_source_option = 'J') then
398
399 -- Process Non-Reval/Non-Accrual related journals.
400 -- Will need to create a new journal only batch.
401
402 Open GEN_BATCH;
403 Fetch GEN_BATCH into p_curr_batch_id;
404 Close GEN_BATCH;
405
406 -- Insert new row to XTR_BATCH when new batch process staring from accrual
407
408 Insert into XTR_BATCHES(batch_id, company_code, period_start, period_end,
409 gl_group_id, upgrade_batch, created_by, creation_date,
410 last_updated_by, last_update_date, last_update_login, batch_type)
411 values (p_curr_batch_id, p_company_code, p_cutoff_date, p_cutoff_date,
412 null, 'N', fnd_global.user_id, sysdate,
413 fnd_global.user_id, sysdate, fnd_global.login_id, 'J');
414
415 G_company_code := p_company_code;
416 G_batch_id := p_curr_batch_id;
417 G_period_end := p_cutoff_date;
418
419 --
420 -- Set the dealer code
421 --
422 fnd_user_id := FND_GLOBAL.USER_ID;
423 Open FIND_USER(fnd_user_id);
424 Fetch FIND_USER into G_user;
425 Close FIND_USER;
426
427 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
428 xtr_debug_pkg.debug ('Do_Journal_Process: ' || 'FND_GLOBAL.user_id = ' || to_char(fnd_user_id));
429 END IF;
430 --
431 -- Set sob info.
432 --
433 Open GET_SOB_INFO;
434 Fetch GET_SOB_INFO into G_set_of_books_id;
435 Close GET_SOB_INFO;
436
437 Open GET_SUSPENSE_CCID;
438 Fetch GET_SUSPENSE_CCID into G_suspense_ccid;
439 Close GET_SUSPENSE_CCID;
440
441 -- Begin 1336492 additions.
442 -- Modified returned value type and meaning of Procedure Gen_Journals
443 -- in order to handle warning status in addition to successful and error.
444
445 l_sub_retcode := Gen_Journals(p_source_option, G_company_code, G_batch_id, G_period_end, p_upgrade_batch);
446 l_retcode := greatest(l_retcode,l_sub_retcode);
447
448 -- End 1336492 additions.
449
450 /* replaced by code above for 1336492.
451
452 If (NOT Gen_Journals(p_source_option, G_company_code, G_batch_id, G_period_end, 'N')) then
453 l_retcode := greatest(l_retcode,2);
454 --bug 2804548
455 else
456 l_retcode := greatest(l_retcode,g_gen_journal_retcode);
457 End If;
458 */
459 End If;
460
461 retcode := l_retcode;
462
463 EXCEPTION
464 when ex_journal_gen then
465 FND_MESSAGE.Set_Name('XTR', 'XTR_JOURNAL_GEN');
466 FND_MESSAGE.Set_Token('BATCH', p_curr_batch_id);
467 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
468 retcode := greatest(l_retcode,2);
469 -- APP_EXCEPTION.Raise_exception; -- Removed for 2404342. Interferring with new wrapper procedure JOURNALS' put_line.
470 when ex_accrls_auth then
471 FND_MESSAGE.Set_Name('XTR', 'XTR_ACCRLS_AUTH');
472 FND_MESSAGE.Set_Token('BATCH', p_curr_batch_id);
473 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
474 retcode := greatest(l_retcode,2);
475 -- APP_EXCEPTION.Raise_exception; -- Removed for 2404342. Interferring with new wrapper procedure JOURNALS' put_line.
476 when ex_early_batch then
477 FND_MESSAGE.Set_Name('XTR', 'XTR_EARLY_BATCH');
478 FND_MESSAGE.Set_Token('PRE_BATCH', l_pre_batch_id);
479 FND_MESSAGE.Set_Token('CUR_BATCH', p_curr_batch_id);
480 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
481 retcode := greatest(l_retcode,2);
482 -- APP_EXCEPTION.Raise_exception; -- Removed for 2404342. Interferring with new wrapper procedure JOURNALS' put_line.
483 END Do_Journal_Process;
484
485 ------------------------------------------------------------------------------------
486
487 /* ---------------------------------------------------------------------
488 | PUBLIC FUNCTION |
489 | Gen_Journals |
490 | |
491 | DESCRIPTION |
492 | Function which generates journal entries for the given batch id.|
493 | The batch can either be Reval/Accrual related or Non-Reval/ |
494 | Accrual related, as specified by the parameter in_source_option.|
495 | NOTE: An upgrade batch can only be a Reval/Accrual related |
496 | batch. |
497 | CALLED BY |
498 | Procedure DO_JOURNAL_PROCESS. |
499 | PARAMETERS |
500 | in_source_option NRA/RA related batch (in, optional) |
501 | 'J' - Non-Reval/Non-Accrual related |
502 | null - Reval/Accrual related |
503 | in_company company code. (required) |
504 | in_batch_id batch id. (required) |
505 | in_period_end date. (required) |
506 | in_upgrade_batch Y/N flag. (required) |
507 | HISTORY |
508 | 04/29/99 eklau (1) Changed update of JOURNAL_CREATED |
509 | in DDA from end of processing for the |
510 | amount date to upon change in deal_type |
511 | deal_number, transaction_number, |
512 | date_type, and amount_date for non 'EXP'|
513 | deals. For 'EXP' deals, it's upon chg |
514 | of deal number. |
515 | (2) Consolidated cursors Q2 and Q4. Q4 |
516 | originally processed 'CA' and 'IG' deals|
517 | and Q2 all deals except the above and |
518 | 'EXP'. |
519 | (3) Changed Q3 - EXP to be driven off |
520 | of the DDA table instead of the exposure|
521 | transactions table. |
522 | (4) Q5 removed due to change in how we |
523 | post INTSET to DDA. Should now be |
524 | handled by Q2. |
525 | 05/26/99 eklau (1) Added logic to calculate accounted |
526 | amounts. |
527 | 08/13/99 eklau (1) Added cursors Q4 and Q5 to handle |
528 | accruals and revaluations, respectively.|
529 | 02/19/02 eklau Changed procedure to accomodate new |
530 | xtr_ig_journal_structure table for |
531 | re-arch of IG journaling in order to |
532 | achieve the new company to company IG |
533 | feature for patchset G. |
534 --------------------------------------------------------------------- */
535
536 FUNCTION GEN_JOURNALS
537 (in_source_option IN VARCHAR2,
538 in_company IN VARCHAR2,
539 in_batch_id IN NUMBER,
540 in_period_end IN DATE,
541 in_upgrade_batch IN VARCHAR2) RETURN NUMBER is
542 --
543 l_accounted_cr NUMBER;
544 l_accounted_dr NUMBER;
545 l_action_code VARCHAR2(7);
546 l_amount_date DATE;
547 l_amount_type VARCHAR2(7);
548 l_batch_id NUMBER;
549 l_bkr_client VARCHAR2(1);
550 l_bnk_ccid NUMBER(15);
551 l_ccid NUMBER(15);
552 l_ccy VARCHAR2(15);
553 l_trans_ccy VARCHAR2(15);
554 l_company VARCHAR2(7);
555 l_company1 VARCHAR2(7);
556 l_credit_amt NUMBER;
557 l_date_type VARCHAR2(7);
558 l_deal_nos NUMBER;
559 l_deal_subtype VARCHAR2(7);
560 l_deal_type VARCHAR2(7);
561 l_debit_amt NUMBER;
562 l_dr_or_cr VARCHAR2(2);
563 -- l_error_flag VARCHAR2(1) := 'N'; -- removed for 1336492.
564 l_event_id NUMBER;
565 l_portfolio VARCHAR2(7);
566 l_prev_amount_date DATE;
567 l_prev_date_type VARCHAR2(7);
568 l_prev_deal_nbr NUMBER;
569 l_prev_deal_type VARCHAR2(7);
570 l_prev_rowid ROWID;
571 l_prev_transaction_nbr NUMBER;
572 l_prod_ty VARCHAR2(10);
573 l_pty_convert_type VARCHAR2(30);
574 l_pty_user_convert_type VARCHAR2(30);
575 l_reval_amt NUMBER;
576 l_row_id rowid;
577 l_sob_currency VARCHAR2(15);
578 l_suspense_gl VARCHAR2(1);
579 l_sysdate DATE :=trunc(sysdate);
580 l_tmp_amt NUMBER;
581 l_transaction_nos NUMBER;
582 l_upgrade_reval VARCHAR2(1) := 'N';
583 l_q6_deal_no NUMBER;
584 l_UNREAL BOOLEAN;
585 l_CCYUNRL BOOLEAN;
586 l_REAL BOOLEAN;
587 l_CCYREAL BOOLEAN;
588 l_EFF_EXIST BOOLEAN;
589 l_create_journal VARCHAR2(1);
590
591
592 -- 4641750
593 l_debit_amount number;
594 l_credit_amount number;
595 l_curr varchar2(7);
596 l_curr_sell varchar2(7);
597 l_curr_buy varchar2(7);
598
599 -- 4641750
600
601
602 -- Bug 3805480 begin
603 l_empty number := 0;
604
605 Cursor Entries (bid number) is
606 Select 1
607 from dual
608 where exists (select null
609 from xtr_journals
610 where batch_id = bid);
611 -- Bug 3805480 end
612
613 Cursor EVENT_ID is
614 Select XTR_BATCH_EVENTS_S.NEXTVAL
615 From DUAL;
616
617 --------------------------------------------------------------------------------
618 -- NOTE: Q2 will select all deal types except 'EXP' for journal generation.
619 -- Tables e (DDA) and f (JEA) are the driving tables, with table a (JEA)
620 -- linking back to itself on the same row as table f (JEA).
621 -- Deals
622 --------------------------------------------------------------------------------
623 Cursor Q2 is
624 select
625 e.company_code company_code
626 ,c.deal_number deal_number
627 ,c.currency currency
628 ,a.deal_type deal_type
629 ,a.deal_subtype deal_subtype
630 ,nvl(a.product_type,'NOT APPLIC') product_type
631 ,a.portfolio_code portfolio_code
632 ,c.amount_type amount_type
633 ,decode(a.credit_or_debit,'DR',nvl(c.amount,0),0) dr_amount
634 ,decode(a.credit_or_debit,'CR',nvl(c.amount,0),0) cr_amount
635 ,c.transaction_number transaction_number
636 ,decode(a.get_gl_from_deal,
637 'Y', g.code_combination_id,
638 a.code_combination_id) ccid
639 ,c.client_broker_clracct client_broker_clracct
640 ,e.date_type date_type
641 ,e.amount_date amount_date -- Reverted Back to amount_date Bug 5235988
642 ,c.settlement_number settlement_number
643 ,a.get_prin_ccid_from_deal use_prin_ccid_flag
644 ,a.get_int_ccid_from_deal use_int_ccid_flag
645 ,c.cparty_account_no cparty_account_no
646 ,e.cparty_code cparty_code
647 ,a.action_code action_code
648 ,decode(a.deal_type,'CA','-1',c.dual_authorisation_by) validated_by -- Added for 1336492.
649 ,c.account_no co_account_no -- Added for 1336492.
650 from XTR_JOURNAL_ENTRY_ACTIONS a,
651 XTR_DEAL_DATE_AMOUNTS c,
652 XTR_DEAL_DATE_AMOUNTS e, -- To generate Journal for Bank Acct
653 XTR_JOURNAL_ENTRY_ACTIONS f, -- " " " " " "
654 XTR_BANK_ACCOUNTS g
655 where e.company_code = in_company
656 and e.batch_id is null
657 and e.amount_date <= in_period_end -- Reverted Back to amount_date Bug 5235988
658 and e.deal_type NOT IN ('EXP')
659 and ((e.deal_type = 'BOND' and e.status_code not in ('CLOSED','CANCELLED')) or
660 (e.deal_type <> 'BOND' and e.status_code <> 'CANCELLED'))
661 and f.company_code = e.company_code
662 and f.deal_type = e.deal_type
663 and f.deal_subtype = e.deal_subtype
664 and f.product_type = nvl(e.product_type,'NOT APPLIC')
665 and f.portfolio_code = nvl(e.portfolio_code,'NOTAPPL')
666 and f.date_type = e.date_type
667 and e.amount_date between nvl(f.effective_from, ADD_MONTHS(sysdate,-240)) and nvl(f.effective_to, sysdate) -- flex journals-- Reverted Back to amount_date Bug 5235988
668 and e.date_type <> 'REVAL' -- jhung 3/14
669 and c.company_code = e.company_code
670 and c.deal_number = e.deal_number
671 and (e.deal_type = 'RTMM' and e.amount_date = c.amount_date) --Bug 16503310
672 and c.transaction_number = e.transaction_number
673 and c.amount <> 0.00 -- 1336492
674 and ((e.deal_type = 'BOND' and c.status_code not in ('CLOSED','CANCELLED')) or
675 (e.deal_type <> 'BOND' and c.status_code <> 'CANCELLED')) -- Bug 3359347.
676 and a.company_code = c.company_code
677 and a.deal_type = c.deal_type
678 and a.deal_subtype = c.deal_subtype
679 and a.product_type = nvl(c.product_type,'NOT APPLIC')
680 and a.portfolio_code = nvl(c.portfolio_code,'NOTAPPL')
681 and a.amount_type = c.amount_type
682 and nvl(a.action_code,'x') = nvl(c.action_code,'x')
683 and a.rowid = f.rowid
684 and g.party_code(+) = c.company_code
685 and g.account_number(+) = c.account_no
686 and g.currency(+) = c.currency
687 and e.company_code IN (SELECT xca.party_code
688 FROM XTR_COMPANY_AUTHORITIES xca
689 WHERE xca.DEALER_CODE = xtr_user_access.dealer_code
690 AND xca.COMPANY_AUTHORISED_FOR_INPUT = 'Y' ) -- bug 5605716
691 order by e.deal_type, e.deal_number, e.transaction_number, e.date_type, e.amount_date;
692
693 Q2_REC Q2%ROWTYPE;
694
695 ----------------------------------------------------------------------------------------------
696 -- NOTE: Q3 are for Exposure transactions. EXP actions are not entered into JEA.
697 -- They are entered into XTR_EXPOSURE_TYPES table by company/exposure type.
698 -- However, DDA does get updated for Exposure transactions. Two journal entries
699 -- should be created for EXP deals, one for the exposure side and the other
700 -- for the bank account side. Journals should be generated only after the
701 -- transaction has been settled, when no further changes to the transaction
702 -- are allowed.
703 -- Product type is currently not applicable for 'EXP' and will be denoted as
704 -- 'NOT APPLIC' in DDA and JOUNRNALS tables.
705 -- Only a transaction number is assigned to exposures. In DDA, this trans nbr will
706 -- be used as the deal number and 0 will be used for the transaction number. Therefore,
707 -- there is the possibility of having the same deal number for two different deal types
708 -- in DDA. To be consistant, journal entries will also follow this format.
709 ----------------------------------------------------------------------------------------------
710 Cursor Q3 is
711 select
712 dda.company_code company_code
713 ,dda.deal_number deal_number
714 ,dda.currency currency
715 ,dda.deal_type deal_type
716 ,dda.deal_subtype deal_subtype
717 ,dda.product_type product_type
718 ,dda.portfolio_code portfolio_code
719 ,dda.amount_type amount_type
720 ,decode(dda.action_code,'PAY',nvl(dda.amount,0),0) debit_amount
721 ,decode(dda.action_code,'REC',nvl(dda.amount,0),0) credit_amount
722 ,dda.transaction_number trans_number
723 ,dda.amount_date amount_date --Reverted Back to amount_date Bug 5235988
724 ,typ.code_combination_id type_ccid
725 ,bnk.code_combination_id bank_ccid
726 ,dda.settlement_number settlement_number
727 ,dda.date_type date_type
728 ,dda.action_code action_code
729 ,dda.dual_authorisation_by validated_by -- Added for 1336492.
730 from XTR_DEAL_DATE_AMOUNTS_V dda,
731 XTR_EXPOSURE_TRANSACTIONS exp,
732 XTR_EXPOSURE_TYPES typ,
733 XTR_BANK_ACCOUNTS bnk
734 where dda.company_code = in_company
735 and dda.amount_date <= in_period_end
736 and dda.batch_id is null
737 and dda.settle = 'Y'
738 and dda.deal_type = 'EXP'
739 and dda.deal_subtype = 'FIRM'
740 and dda.company_code = exp.company_code
741 and dda.transaction_number = exp.transaction_number
742 and dda.amount <> 0.00 -- 1336492.
743 and exp.company_code = typ.company_code
744 and exp.exposure_type = typ.exposure_type
745 and bnk.party_code (+) = dda.company_code
746 and bnk.account_number (+) = dda.account_no
747 order by exp.transaction_number -- 1336492.
748 ;
749
750 Q3_REC Q3%ROWTYPE;
751
752 ----------------------------------------------------------------------------------------------
753 -- NOTE: Q4 will process accruals from XTR_JOURNAL_ENTRY_ACTIONS
754 -- (where date type = 'ACCRUAL') and join it to XTR_ACCRLS_AMORT
755 -- to create journal entries.
756 ----------------------------------------------------------------------------------------------
757 Cursor Q4 is
758 Select
759 acc.batch_id batch_id
760 ,acc.company_code company_code
761 ,acc.deal_no deal_number
762 ,acc.currency currency
763 ,acc.deal_type deal_type
764 ,acc.deal_subtype deal_subtype
765 ,acc.product_type product_type
766 ,acc.portfolio_code portfolio_code
767 ,acc.amount_type amount_type
768 ,acc.accrls_amount accrls_amount
769 ,acc.trans_no trans_number
770 ,acc.period_to amount_date
771 ,jea.credit_or_debit credit_or_debit
772 ,jea.code_combination_id ccid
773 ,acc.rowid row_id
774 ,jea.date_type date_type
775 ,jea.action_code action_code
776 from xtr_accrls_amort acc,
777 xtr_journal_entry_actions jea
778 where acc.batch_id = in_batch_id
779 and jea.company_code = in_company
780 and jea.date_type = 'ACCRUAL'
781 and jea.company_code = acc.company_code
782 and jea.deal_type = acc.deal_type
783 and jea.deal_subtype = acc.deal_subtype
784 and jea.product_type = acc.product_type
785 and jea.portfolio_code = acc.portfolio_code
786 and jea.amount_type = acc.amount_type
787 and jea.action_code = acc.action_code
788 and acc.period_to between nvl(jea.effective_from, ADD_MONTHS(sysdate,-240)) and NVL(JEA.effective_to, sysdate) -- flex journals
789 and acc.period_to <= in_period_end
790 and nvl(in_upgrade_batch,'N') <> 'I' -- Do not generate accruals for inaugural batch
791 order by acc.rowid
792 ;
793
794 Q4_REC Q4%ROWTYPE;
795
796 ----------------------------------------------------------------------------------------------
797 -- NOTE: Q5 will process revaluations from XTR_REVALUATION_DETAILS.
798 -- There is no amount_type column in the XTR_REVALUATION_DETAILS table.
799 -- In the JEA setup, the amount type of 'UNREAL' and 'REAL' is intended
800 -- to allow the user to post unrealized/realized gains/losses to different
801 -- GL accounts. In addition, any unrealized gains/losses will be flagged
802 -- as requiring a reversal entry in the subsequent period when transferring
803 -- XTR journals to the GL interface table.
804 -- Instead, there are separate columns for REALIZED_PL and UNREALIZED_PL
805 -- amounts in the XTR_REVALUATION_DETAILS table. If the row is for a realized
806 -- profit/loss, then there will be an amount in the REALIZED_PL column, etc.
807 ----------------------------------------------------------------------------------------------
808 Cursor Q5 is
809 Select
810 dnm.rowid row_id
811 ,dnm.batch_id batch_id
812 ,dnm.company_code company_code
813 ,dnm.deal_number deal_number
814 ,dnm.currency reval_currency
815 ,dea.currency deal_currency
816 ,dea.deal_type deal_type
817 ,dea.deal_subtype deal_subtype
818 ,dea.product_type product_type
819 ,dea.portfolio_code portfolio_code
820 -- ,decode(jea.amount_type,'REAL',nvl(dnm.amount,0),0)
821 -- ,decode(jea.amount_type,'UNREAL',nvl(dnm.amount,0),0)
822 ,dnm.amount amount
823 ,dnm.transaction_number trans_number
824 ,dnm.journal_date journal_date -- Bug 1967109 replacing dnm.amount_date
825 ,jea.credit_or_debit credit_or_debit
826 ,jea.code_combination_id ccid
827 ,jea.amount_type amount_type
828 ,jea.action_code action_code
829 ,jea.date_type date_type
830 from xtr_gain_loss_dnm dnm,
831 xtr_journal_entry_actions jea,
832 xtr_deals dea
833 where dnm.batch_id = in_batch_id
834 and jea.company_code = in_company
835 and jea.date_type = dnm.date_type
836 and jea.amount_type = dnm.amount_type
837 and jea.action_code = dnm.action
838 and jea.company_code = dnm.company_code
839 and jea.deal_type ||'' = dea.deal_type
840 and ((jea.deal_type in ('FXO','BOND','NI','FRA','FX') and
841 nvl(in_upgrade_batch,'N') = 'I' and l_upgrade_reval = 'Y') or
842 ((nvl(in_upgrade_batch,'N')<> 'I') and
843 not(jea.deal_type = 'FX' and jea.deal_subtype = 'FORWARD')))
844 and jea.deal_subtype = dea.deal_subtype
845 and jea.product_type = dea.product_type
846 and jea.portfolio_code = dea.portfolio_code
847 and dnm.journal_date between nvl(jea.effective_from, ADD_MONTHS(sysdate,-240)) and nvl(jea.effective_to, sysdate) -- flex journals
848 and dnm.deal_number = dea.deal_no
849 -- and dnm.amount_date <= in_period_end
850 -- and dnm.rate_error = 'N' -- what do we substitute with this column?
851 union
852 ----------
853 -- ONC --
854 ----------
855 Select
856 dnm.rowid row_id
857 ,dnm.batch_id batch_id
858 ,dnm.company_code company_code
859 ,dnm.deal_number deal_number
860 ,dnm.currency reval_currency
861 ,ro.currency deal_currency
862 ,ro.deal_type deal_type
863 ,ro.deal_subtype deal_subtype
864 ,ro.product_type product_type
865 ,ro.portfolio_code portfolio_code
866 ,dnm.amount amount
867 ,dnm.transaction_number trans_number
868 ,dnm.journal_date journal_date -- Bug 1967109 replacing dnm.amount_date
869 ,jea.credit_or_debit credit_or_debit
870 ,jea.code_combination_id ccid
871 ,jea.amount_type amount_type
872 ,jea.action_code action_code
873 ,jea.date_type date_type
874 from xtr_gain_loss_dnm dnm,
875 xtr_journal_entry_actions jea,
876 xtr_rollover_transactions ro
877 where dnm.batch_id = in_batch_id
878 and jea.company_code = in_company
879 and jea.date_type = dnm.date_type
880 and jea.amount_type = dnm.amount_type
881 and jea.action_code = dnm.action
882 and jea.company_code = dnm.company_code
883 and jea.deal_type ||'' = ro.deal_type
884 and ro.deal_type = 'ONC'
885 and ((nvl(in_upgrade_batch,'N') = 'I' and l_upgrade_reval = 'Y') or
886 (nvl(in_upgrade_batch,'N')<> 'I'))
887 and jea.deal_subtype = ro.deal_subtype
888 and jea.product_type = ro.product_type
889 and jea.portfolio_code = ro.portfolio_code
890 and dnm.journal_date between nvl(jea.effective_from, ADD_MONTHS(sysdate,-240)) and nvl(jea.effective_to, sysdate) -- flex journals
891 and dnm.deal_number = ro.deal_number
892 and dnm.transaction_number = ro.transaction_number
893 union
894 --------
895 -- IG --
896 --------
897 Select
898 dnm.rowid row_id
899 ,dnm.batch_id batch_id
900 ,dnm.company_code company_code
901 ,dnm.deal_number deal_number
902 ,dnm.currency reval_currency
903 ,ig.currency deal_currency
904 ,'IG' deal_type
905 ,decode(sign(ig.balance_out),-1,'FUND','INVEST') deal_subtype
906 ,ig.product_type product_type
907 ,ig.portfolio portfolio_code
908 ,dnm.amount amount
909 ,dnm.transaction_number trans_number
910 ,dnm.journal_date journal_date -- Bug 1967109 replacing dnm.amount_date
911 ,jea.credit_or_debit credit_or_debit
912 ,jea.code_combination_id ccid
913 ,jea.amount_type amount_type
914 ,jea.action_code action_code
915 ,jea.date_type date_type
916 from xtr_gain_loss_dnm dnm,
917 xtr_journal_entry_actions jea,
918 xtr_intergroup_transfers ig
919 where dnm.batch_id = in_batch_id
920 and jea.company_code = in_company
921 and jea.date_type = dnm.date_type
922 and jea.amount_type = dnm.amount_type
923 and jea.action_code = dnm.action
924 and jea.company_code = dnm.company_code
925 and jea.deal_type = ig.deal_type
926 and ig.deal_type = 'IG'
927 and ((nvl(in_upgrade_batch,'N') = 'I' and l_upgrade_reval = 'Y') or
928 (nvl(in_upgrade_batch,'N')<> 'I'))
929 and jea.deal_subtype = decode(sign(ig.balance_out),-1,'FUND','INVEST')
930 and jea.product_type = ig.product_type
931 and jea.portfolio_code = ig.portfolio
932 and dnm.journal_date between nvl(jea.effective_from, ADD_MONTHS(sysdate,-240)) and nvl(jea.effective_to, sysdate) -- flex journals
933 and dnm.deal_number = ig.deal_number
934 and dnm.transaction_number = ig.transaction_number
935 union
936 --------
937 -- CA --
938 --------
939 Select
940 dnm.rowid row_id
941 ,dnm.batch_id batch_id
942 ,dnm.company_code company_code
943 ,dnm.deal_number deal_number
944 ,dnm.currency reval_currency
945 ,ba.currency deal_currency
946 ,'CA' deal_type
947 ,decode(sign(bb.balance_cflow),-1,'FUND','INVEST') deal_subtype
948 ,'NOT APPLIC' product_type
949 ,ba.portfolio_code portfolio_code
950 ,dnm.amount amount
951 ,dnm.transaction_number trans_number
952 ,dnm.journal_date journal_date -- Bug 1967109 replacing dnm.amount_date
953 ,jea.credit_or_debit credit_or_debit
954 ,jea.code_combination_id ccid
955 ,jea.amount_type amount_type
956 ,jea.action_code action_code
957 ,jea.date_type date_type
958 from xtr_gain_loss_dnm dnm,
959 xtr_journal_entry_actions jea,
960 xtr_revaluation_details rd,
961 xtr_bank_accounts ba,
962 xtr_bank_balances bb
963 where dnm.batch_id = in_batch_id
964 and jea.company_code = in_company -- JEA
965 and jea.date_type = dnm.date_type -- DNM
966 and jea.amount_type = dnm.amount_type -- DNM
967 and jea.action_code = dnm.action -- DNM
968 and jea.company_code = dnm.company_code -- DNM
969 and jea.deal_type = rd.deal_type -- JEA
970 and dnm.journal_date between nvl(jea.effective_from, ADD_MONTHS(sysdate,-240)) and nvl(jea.effective_to, sysdate) -- flex journals
971 and rd.deal_type = 'CA' -- Reval Details
972 and rd.deal_no = dnm.deal_number -- Reval Details
973 and rd.batch_id = dnm.batch_id -- Reval Details
974 and rd.period_to = dnm.journal_date -- Reval Details
975 and rd.realized_flag = 'Y' -- Reval Details
976 and ba.party_code = bb.company_code -- Bank Balances
977 and ba.account_number = bb.account_number -- Bank Balances
978 and bb.balance_date = dnm.journal_date -- Bank Balances
979 and jea.deal_subtype = decode(sign(bb.balance_cflow),
980 -1,'FUND','INVEST') -- JEA
981 and jea.product_type = 'NOT APPLIC' -- JEA
982 and jea.portfolio_code = ba.portfolio_code -- JEA
983 and ba.party_code = rd.company_code -- Bank Account
984 and ba.account_number = rd.account_no -- Bank Account
985 and ba.currency = rd.currencya -- Bank Account
986 and ((nvl(in_upgrade_batch,'N') = 'I' and l_upgrade_reval = 'Y') or
987 (nvl(in_upgrade_batch,'N')<> 'I'))
988 order by 1;
989
990
991 Q5_REC Q5%ROWTYPE;
992
993 ------------------------------------------------------------------------------------------
994 -- NOTE: Q6 will process FX Forward deals for Revaluations and
995 -- Effectiveness testing journal entries only. This cursor will include
996 -- the normal FX deals and hedge associated FX Forward deals. This
997 -- cursor will fetch all FX deals from XTR_GAIN_LOSS_DNM table with
998 -- Amount_type/Date_type/Action combination defined in JEA set up, and
999 -- process the reval_eff_flag equal to 'T' (Efffective testing results) first.
1000 -- If any of the flag 'T' is found, then the flag 'R' for the same deal
1001 -- number /amout type will not be journalized.
1002 -------------------------- ------------------------------------------------------------
1003 Cursor Q6 is Select
1004 dnm.rowid row_id,
1005 dnm.batch_id batch_id,
1006 dnm.company_code company_code,
1007 dnm.deal_number deal_number,
1008 dnm.currency reval_currency,
1009 dnm.currency deal_currency,
1010 dea.deal_type deal_type,
1011 dea.deal_subtype deal_subtype,
1012 dea.product_type product_type,
1013 dea.portfolio_code portfolio_code,
1014 dnm.amount amount,
1015 dnm.transaction_number trans_number,
1016 dnm.journal_date journal_date,
1017 jea.credit_or_debit credit_or_debit,
1018 jea.code_combination_id ccid,
1019 jea.amount_type amount_type,
1020 jea.action_code action_code,
1021 jea.date_type date_type,
1022 dnm.reval_eff_flag reval_eff_flag
1023 from XTR_GAIN_LOSS_DNM dnm,
1024 XTR_DEALS dea,
1025 XTR_JOURNAL_ENTRY_ACTIONS jea
1026 Where dnm.batch_id = in_batch_id
1027 And jea.company_code = in_company
1028 And jea.company_code = dnm.company_code
1029 And jea.amount_type = dnm.amount_type
1030 And jea.action_code = dnm.action
1031 And jea.deal_type = dea.deal_type
1032 And jea.deal_subtype = dea.deal_subtype
1033 And jea.product_type = dea.product_type
1034 And jea.portfolio_code = dea.portfolio_code
1035 And dea.deal_no = dnm.deal_number
1036 And jea.date_type = dnm.date_type
1037 And dnm.journal_date between nvl(jea.effective_from,
1038 ADD_MONTHS(sysdate,-240)) and nvl(jea.effective_to, sysdate)
1039 And jea.deal_type = 'FX'
1040 And jea.deal_subtype = 'FORWARD'
1041 Order by dnm.deal_number, dnm.reval_eff_flag desc;
1042
1043
1044 Q6_REC Q6%ROWTYPE;
1045
1046 ------------------------------------------------------------------------------------------
1047 -- NOTE: Q7 will process Hedge Items Revaluations process the CCYUNRL and UNREAL
1048 -- amount types based on Hedge Item accounting settings.
1049 -------------------------- ------------------------------------------------------------
1050 Cursor Q7 is Select
1051 dnm.rowid row_id,
1052 dnm.batch_id batch_id,
1053 dnm.company_code company_code,
1054 dnm.deal_number deal_number,
1055 dnm.currency reval_currency,
1056 att.hedge_currency deal_currency,
1057 'HEDGE' deal_type,
1058 str.hedge_type deal_subtype,
1059 str.hedge_approach product_type,
1060 att.strategy_code portfolio_code,
1061 dnm.amount amount,
1062 dnm.transaction_number trans_number,
1063 dnm.journal_date journal_date,
1064 hac.credit_or_debit credit_or_debit,
1065 hac.code_combination_id ccid,
1066 hac.amount_type amount_type,
1067 hac.action_code action_code,
1068 hac.date_type date_type
1069 from XTR_GAIN_LOSS_DNM dnm,
1070 XTR_HEDGE_JOURNAL_ACTIONS hac,
1071 XTR_HEDGE_ATTRIBUTES att,
1072 XTR_HEDGE_STRATEGIES str
1073 Where dnm.batch_id = in_batch_id
1074 And hac.company_code = in_company
1075 And hac.company_code = dnm.company_code
1076 And hac.amount_type = dnm.amount_type
1077 And hac.action_code = dnm.action
1078 And hac.date_type = dnm.date_type
1079 And str.strategy_code = att.strategy_code
1080 And att.hedge_attribute_id = dnm.deal_number
1081 And hac.code_combination_id is NOT NULL
1082 And str.hedge_approach = 'FIRMCOM';
1083
1084 Q7_REC Q7%ROWTYPE;
1085
1086 ------------------------------------------------------------
1087 -- Obtain company's SOB currency and conversion rate type.
1088 ------------------------------------------------------------
1089 cursor COMPANY_INFO is
1090 select substr(sob.currency_code,1,3),
1091 cp.parameter_value_code, -- replaced pty.conversion_type,
1092 dct.user_conversion_type
1093 from xtr_parties_v pty,
1094 xtr_company_parameters cp,
1095 gl_sets_of_books sob,
1096 gl_daily_conversion_types dct
1097 where pty.party_code = in_company
1098 and cp.company_code = pty.party_code
1099 and cp.parameter_code = 'ACCNT_EXRTP'
1100 and pty.set_of_books_id = sob.set_of_books_id
1101 and cp.parameter_value_code = dct.conversion_type (+);
1102 --and pty.conversion_type = dct.conversion_type (+);
1103
1104 -----------------------------------
1105 -- Find any upgrade reval details
1106 -----------------------------------
1107 cursor UPGRADE_REVAL is
1108 select b.upgrade_batch
1109 from XTR_REVALUATION_DETAILS a,
1110 XTR_BATCHES b
1111 where a.company_code = in_company
1112 and a.batch_id = b.batch_id
1113 and nvl(b.upgrade_batch,'N') = 'Y';
1114 --
1115 --bug 2804548
1116 v_ChkCpnRateReset_out xtr_mm_covers.ChkCpnRateReset_out_rec;
1117 v_ChkCpnRateReset_in xtr_mm_covers.ChkCpnRateReset_in_rec;
1118
1119 -- Bug 4634182
1120
1121 CURSOR C_NRA_PERIOD_FROM IS
1122 SELECT min(journal_date)
1123 FROM XTR_JOURNALS
1124 WHERE batch_id = in_batch_id;
1125
1126 l_nra_period_from DATE;
1127
1128 -- End of Bug 4634182
1129
1130 -- Begin 1336492 additions.
1131
1132 Cursor VALREQ(p_param_name varchar2) is
1133 Select param_value
1134 from xtr_pro_param
1135 where param_name = p_param_name;
1136
1137 l_valreq XTR_PRO_PARAM.param_value%TYPE := NULL;
1138 l_val_flag varchar2(1) := 'N';
1139 l_ret_value number := 0;
1140 l_updt_flag VARCHAR2(1) := 'Y';
1141 l_iac_valreq xtr_pro_param.param_value%type;
1142
1143 FUNCTION VALIDATED
1144 (l_deal_type in varchar2,
1145 l_deal_no in number,
1146 l_trans_no in number,
1147 l_val_flag in varchar2,
1148 l_co_account_no in varchar2) RETURN BOOLEAN IS
1149
1150 Cursor DEAL_VAL_BY is
1151 Select dual_authorisation_by
1152 from xtr_deals
1153 where deal_no = l_deal_no;
1154
1155 Cursor GET_USER_DEAL_TYPE is
1156 Select user_deal_type
1157 from xtr_deal_types
1158 where deal_type = l_deal_type;
1159
1160
1161
1162
1163 l_deal_val_by XTR_DEALS.dual_authorisation_by%TYPE := NULL;
1164 l_ret_value boolean;
1165 l_user_deal_type XTR_DEAL_TYPES.user_deal_type%TYPE := NULL;
1166 l_trans_no_1 number := l_trans_no;
1167
1168
1169 Begin
1170 If (l_val_flag is not null) then
1171 If (l_val_flag = 'Y') then
1172 l_ret_value := TRUE;
1173 Else
1174 l_ret_value := FALSE;
1175 End If;
1176 Else
1177 If (l_deal_type IN ('TMM','RTMM','BDO','FRA','IRO','SWPTN')) then
1178 l_trans_no_1 := null;
1179 Open DEAL_VAL_BY;
1180 Fetch DEAL_VAL_BY into l_deal_val_by;
1181 Close DEAL_VAL_BY;
1182
1183 If (l_deal_val_by is not null) then
1184 l_ret_value := TRUE;
1185 Else
1186 l_ret_value := FALSE;
1187 End If;
1188 Elsif (l_deal_type = 'CA') then
1189 l_ret_value := TRUE;
1190 Else
1191 l_ret_value := FALSE;
1192 End If;
1193 End If;
1194
1195 If (l_deal_type in ('BOND','NI','FX','IRS','FXO')) then
1196 l_trans_no_1 := null;
1197 End If;
1198
1199 If (NOT l_ret_value) then
1200 Open GET_USER_DEAL_TYPE;
1201 Fetch GET_USER_DEAL_TYPE into l_user_deal_type;
1202 Close GET_USER_DEAL_TYPE;
1203
1204 If (l_deal_type = 'CA') then
1205 l_user_deal_type := nvl(l_user_deal_type,l_deal_type) || ' : ' || l_co_account_no;
1206 Else
1207 l_user_deal_type := nvl(l_user_deal_type,l_deal_type);
1208 End If;
1209
1210 Begin
1211 Insert into xtr_unval_deals_gt
1212 (deal_type,
1213 deal_number,
1214 trans_number)
1215 values (l_user_deal_type, l_deal_no, l_trans_no_1);
1216 Exception
1217 when DUP_VAL_ON_INDEX then
1218 NULL;
1219 when OTHERS then
1220 FND_MESSAGE.Set_Name ('XTR','XTR_UNHANDLED_EXCEPTION');
1221 FND_MESSAGE.Set_Token ('PROCEDURE','GEN_JOURNALS:VALIDATED');
1222 FND_MESSAGE.Set_Token ('EVENT','INSERT_INTO_TEMP_TABLE');
1223 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
1224 End;
1225 End If;
1226 return l_ret_value;
1227
1228 Exception
1229 when others then
1230 If (DEAL_VAL_BY%ISOPEN) then
1231 Close DEAL_VAL_BY;
1232 End If;
1233
1234 Begin
1235 Insert into xtr_unval_deals_gt
1236 (deal_type,
1237 deal_number,
1238 trans_number)
1239 values (l_user_deal_type, l_deal_no, l_trans_no_1);
1240 Exception
1241 when DUP_VAL_ON_INDEX then
1242 NULL;
1243 when OTHERS then
1244 FND_MESSAGE.Set_Name ('XTR','XTR_UNHANDLED_EXCEPTION');
1245 FND_MESSAGE.Set_Token ('PROCEDURE','GEN_JOURNALS:VALIDATED');
1246 FND_MESSAGE.Set_Token ('EVENT','Inserting non-validated deal/transaction number into temp table.');
1247 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
1248 End;
1249 return FALSE;
1250 End;
1251
1252 PROCEDURE PRT_UNVAL_DEALS is
1253
1254 Cursor UNVAL_DEALS is
1255 Select *
1256 from xtr_unval_deals_gt;
1257
1258 unval_deals_rec unval_deals%ROWTYPE;
1259 Begin
1260 Open UNVAL_DEALS;
1261 Fetch UNVAL_DEALS into unval_deals_rec;
1262 If (UNVAL_DEALS%FOUND) then
1263 FND_FILE.Put_Line (FND_FILE.Log, null);
1264 FND_MESSAGE.Set_Name ('XTR','XTR_UNVAL_DEALS_LIST');
1265 FND_FILE.Put_Line (FND_FILE.Log, FND_MESSAGE.Get);
1266 FND_FILE.Put_Line (FND_FILE.Log, null);
1267 While (UNVAL_DEALS%FOUND)
1268 Loop
1269 FND_MESSAGE.Set_Name ('XTR','XTR_UNVAL_DEALS');
1270 FND_MESSAGE.Set_Token ('DEAL_TYPE', unval_deals_rec.deal_type);
1271 FND_MESSAGE.Set_Token ('DEAL_NO', rtrim(to_char(unval_deals_rec.deal_number)));
1272 FND_MESSAGE.Set_Token ('TRANS_NO', rtrim(to_char(unval_deals_rec.trans_number)));
1273 FND_FILE.Put_Line (FND_FILE.Log, FND_MESSAGE.Get);
1274 Fetch UNVAL_DEALS into unval_deals_rec;
1275 End Loop;
1276 End If;
1277 Close UNVAL_DEALS;
1278 End;
1279
1280 -- End 1336492 additions.
1281
1282 begin
1283 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1284 xtr_debug_pkg.debug('GEN_JOURNALS: ' || '>> Gen Journals.');
1285 END IF;
1286
1287 -- Begin 1336492 additions.
1288
1289 Open VALREQ('VAL_REQD_FOR_ACCT');
1290 Fetch VALREQ into l_valreq;
1291 Close VALREQ;
1292 l_valreq := nvl(l_valreq,'N');
1293
1294
1295 -- Bug 3800146 IAC redesign
1296 Open VALREQ('DUAL_AUTHORISE_IAC');
1297 Fetch VALREQ into l_iac_valreq;
1298 Close VALREQ;
1299 l_iac_valreq := nvl(l_iac_valreq,'N');
1300
1301
1302
1303 Delete from xtr_unval_deals_gt;
1304
1305 -- End 1336492 addition.
1306
1307 ----------------------------------------------------------
1308 -- Obtain company's SOB currency and conversion rate type.
1309 ----------------------------------------------------------
1310 Open COMPANY_INFO;
1311 Fetch COMPANY_INFO into l_sob_currency, l_pty_convert_type, l_pty_user_convert_type;
1312 Close COMPANY_INFO;
1313
1314 ----------------------------------------------------------
1315 -- Start processing Q2 for all deal types except 'EXP'.
1316 ----------------------------------------------------------
1317 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1318 xtr_debug_pkg.debug('GEN_JOURNALS: ' || 'Opening Q2');
1319 END IF;
1320
1321 Open Q2;
1322 Fetch Q2 INTO Q2_REC;
1323
1324 l_batch_id := in_batch_id;
1325 l_prev_deal_type := Q2_REC.deal_type;
1326 l_prev_deal_nbr := Q2_REC.deal_number;
1327 l_prev_transaction_nbr := Q2_REC.transaction_number;
1328 l_prev_date_type := Q2_REC.date_type;
1329 l_prev_amount_date := Q2_REC.amount_date;
1330
1331 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1332 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Entering Q2 loop.');
1333 END IF;
1334
1335 WHILE Q2%FOUND LOOP
1336
1337 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1338 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Company Code = ' || Q2_REC.company_code);
1339 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Deal Type = ' || Q2_REC.deal_type);
1340 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Deal Number = ' || to_char(Q2_REC.deal_number));
1341 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Transaction Nbr = ' || to_char(Q2_REC.transaction_number));
1342 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Amount Date = ' || to_char(Q2_REC.amount_date,'MM/DD/RRRR'));
1343 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Currency = ' || Q2_REC.currency);
1344 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Deal Subtype = ' || Q2_REC.deal_subtype);
1345 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Product Type = ' || Q2_REC.product_type);
1346 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Portfolio = ' || Q2_REC.portfolio_code);
1347 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Date Type = ' || Q2_REC.date_type);
1348 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Amount Type = ' || Q2_REC.amount_type);
1349 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Debit Amount = ' || to_char(Q2_REC.dr_amount));
1350 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Credit Amount = ' || to_char(Q2_REC.cr_amount));
1351 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'CCID = ' || to_char(Q2_REC.ccid));
1352 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Client Clr Acct = ' || Q2_REC.client_broker_clracct);
1353 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Settlement Number = ' || to_char(Q2_REC.settlement_number));
1354 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Use Prin CCID flag = ' || Q2_REC.use_prin_ccid_flag);
1355 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Use Int CCID flag = ' || Q2_REC.use_int_ccid_flag);
1356 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Cpty Bank Acct Nbr = ' || Q2_REC.cparty_account_no);
1357 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Cpty Code = ' || Q2_REC.cparty_code);
1358 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Action Code = ' || Q2_REC.action_code);
1359 END IF;
1360
1361 -- Begin bug 1336492 additions.
1362 -- Added condition to prevent cashflow entry creation when deal has not been validated and
1363 -- system parameter is set to require validation prior to journaling.
1364
1365 l_updt_flag := 'Y';
1366
1367 If (Q2_REC.deal_type in ('TMM','RTMM','BDO','FRA','IRO','SWPTN')) then
1368 l_val_flag := null;
1369
1370 elsif Q2_REC.deal_type = 'IAC' then -- 3800146 IAC Redesign Added Lines
1371
1372 if l_iac_valreq = 'N' then
1373 l_val_flag := 'Y';
1374 else
1375 If (Q2_REC.validated_by is NULL) then
1376 l_val_flag := 'N';
1377 Else
1378 l_val_flag := 'Y';
1379 End If;
1380 end if; -- 3800146 IAC Redesign Ended Lines
1381
1382 Else
1383 If (Q2_REC.validated_by is NULL) then
1384 l_val_flag := 'N';
1385 Else
1386 l_val_flag := 'Y';
1387 End If;
1388 End If;
1389
1390 /* bug 4236929 -- added a new variable l_create_journal for creation of journals
1391 * and removed the if condition added during IAC Project */
1392
1393 if l_valreq = 'N' then
1394 l_create_journal := 'Y';
1395 else
1396 if Q2_REC.deal_type <> 'IAC' then
1397 if VALIDATED(Q2_REC.deal_type, Q2_REC.deal_number,Q2_REC.transaction_number, l_val_flag, Q2_REC.co_account_no) then
1398 l_create_journal := 'Y';
1399 else
1400 l_create_journal := 'N';
1401 end if;
1402 else
1403 if l_iac_valreq = 'Y' then
1404 if VALIDATED(Q2_REC.deal_type, Q2_REC.deal_number,Q2_REC.transaction_number, l_val_flag, Q2_REC.co_account_no) then
1405 l_create_journal := 'Y';
1406 else
1407 l_create_journal := 'N';
1408 end if;
1409 else
1410 l_create_journal := 'Y';
1411 end if; -- l_iac_valreq
1412 end if; -- q2_rec.deal_type
1413 end if; -- l_valreq
1414
1415
1416
1417
1418
1419 if l_create_journal = 'Y' then
1420
1421 --start bug 2804548
1422 --FND_FILE.Put_Line (FND_FILE.LOG, 'Q2 '||to_char(q2_rec.transaction_number));
1423 if q2_rec.deal_type in ('BOND','EXP') and
1424 q2_rec.transaction_number is not null then
1425 v_ChkCpnRateReset_in.deal_type:=q2_rec.deal_type;
1426 v_ChkCpnRateReset_in.transaction_no:=q2_rec.transaction_number;
1427 v_ChkCpnRateReset_in.deal_no:=q2_rec.deal_number;
1428 xtr_mm_covers.check_coupon_rate_reset(v_ChkCpnRateReset_in,
1429 v_ChkCpnRateReset_out);
1430 --if the coupon or its tax comp has not been reset
1431 --print out a warning message.
1432 if not v_ChkCpnRateReset_out.yes then
1433 FND_MESSAGE.Set_Name ('XTR','XTR_COUPON_RESET_DEAL');
1434 FND_MESSAGE.Set_Token ('DEAL_NO',v_ChkCpnRateReset_out.deal_no);
1435 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
1436 --1336492 g_gen_journal_retcode:=1;
1437
1438 -- 1336492 modification.
1439 l_ret_value := greatest(l_ret_value,1);
1440
1441 end if;
1442 end if;
1443 --end bug 2804548
1444
1445 If (l_deal_type NOT IN ('CA','IG')) then
1446 If (nvl(Q2_REC.client_broker_clracct,'N') = 'Y') then
1447
1448 -- Reverse Journal around for a Client to Company settlement
1449 -- through the Clearing Account (Only occurs in a Broker
1450 -- situation where the Brokers Clearing Acct is used for the
1451 -- purpose to CLEAR funds from the Cparty to Client
1452
1453 If (Q2_REC.dr_amount = 0) then
1454 Q2_REC.dr_amount := Q2_REC.cr_amount;
1455 Q2_REC.cr_amount := 0;
1456 Elsif (Q2_REC.cr_amount = 0) then
1457 Q2_REC.cr_amount := Q2_REC.dr_amount;
1458 Q2_REC.dr_amount := 0;
1459 End if;
1460 End if;
1461 End If;
1462
1463 /* Call private function to obtain ccid from counter party's bank account, */
1464 /* if it is an IG transaction with "dynamic" ccid selection. */
1465
1466 If (Q2_REC.ccid is NULL and Q2_REC.deal_type = 'IG') then
1467 Q2_REC.ccid := GET_IG_CCID (
1468 Q2_REC.use_prin_ccid_flag,
1469 Q2_REC.use_int_ccid_flag,
1470 Q2_REC.company_code,
1471 Q2_REC.cparty_code,
1472 Q2_REC.currency,
1473 Q2_REC.cparty_account_no);
1474 End If;
1475
1476 /* If ccid is null, use company's suspense ccid. */
1477
1478 If (Q2_REC.ccid is NULL) then
1479 Q2_REC.ccid := G_suspense_ccid;
1480 l_suspense_gl := 'Y';
1481 Else
1482 l_suspense_gl := null;
1483 End If;
1484
1485 -- Calculate accounted_dr and accounted_cr amounts.
1486
1487 BEGIN
1488 l_accounted_dr := GL_CURRENCY_API.Convert_Amount (
1489 Q2_REC.currency,
1490 l_sob_currency,
1491 Q2_REC.amount_date,
1492 l_pty_convert_type,
1493 Q2_REC.dr_amount);
1494
1495 l_accounted_cr := GL_CURRENCY_API.Convert_Amount (
1496 Q2_REC.currency,
1497 l_sob_currency,
1498 Q2_REC.amount_date,
1499 l_pty_convert_type,
1500 Q2_REC.cr_amount);
1501
1502 EXCEPTION
1503 when GL_CURRENCY_API.INVALID_CURRENCY then
1504 FND_MESSAGE.Set_Name ('XTR','XTR_2206');
1505 FND_MESSAGE.Set_Token ('CURR1',Q2_REC.currency);
1506 FND_MESSAGE.Set_Token ('CURR2',l_sob_currency);
1507 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
1508
1509 --1336492 l_error_flag := 'Y';
1510
1511 -- 1336492 modification, replaces above l_error_flag.
1512 l_ret_value := 2;
1513 l_updt_flag := 'N';
1514 -- end 1336492 modification.
1515
1516 Goto NEXT_Q2;
1517
1518 when GL_CURRENCY_API.NO_RATE then
1519 FND_MESSAGE.Set_Name ('XTR','XTR_2207');
1520 FND_MESSAGE.Set_Token ('CURR1',Q2_REC.currency);
1521 FND_MESSAGE.Set_Token ('CURR2',l_sob_currency);
1522 FND_MESSAGE.Set_Token ('XCHG_DATE',to_char(Q2_REC.amount_date));
1523 FND_MESSAGE.Set_Token ('C_TYPE', nvl(l_pty_user_convert_type,l_pty_convert_type));
1524 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.get);
1525
1526 --1336492 l_error_flag := 'Y';
1527
1528 -- 1336492 modification, replaces above l_error_flag.
1529 l_ret_value := 2;
1530 l_updt_flag := 'N';
1531 -- end 1336492 modification.
1532
1533 Goto NEXT_Q2;
1534 END;
1535
1536 BEGIN
1537 Insert into XTR_JOURNALS
1538 (batch_id,
1539 company_code,
1540 journal_date,
1541 orig_journal_date,
1542 deal_number,
1543 transaction_number,
1544 deal_type,
1545 deal_subtype,
1546 product_type,
1547 debit_amount,
1548 credit_amount,
1549 code_combination_id,
1550 amount_type,
1551 created_by,
1552 created_on,
1553 portfolio_code,
1554 currency,
1555 set_of_books_id,
1556 suspense_gl,
1557 accounted_dr,
1558 accounted_cr,
1559 settlement_number,
1560 date_type,
1561 action_code)
1562 Values (l_batch_id,
1563 Q2_REC.company_code,
1564 nvl(Q2_REC.amount_date,to_date('01/01/1997','MM/DD/YYYY')),
1565 nvl(Q2_REC.amount_date,to_date('01/01/1997','MM/DD/YYYY')),
1566 Q2_REC.deal_number,
1567 nvl(Q2_REC.transaction_number,999),
1568 Q2_REC.deal_type,
1569 Q2_REC.deal_subtype,
1570 Q2_REC.product_type,
1571 Q2_REC.dr_amount,
1572 Q2_REC.cr_amount,
1573 Q2_REC.ccid,
1574 Q2_REC.amount_type,
1575 G_user,
1576 TRUNC(SYSDATE),
1577 Q2_REC.portfolio_code,
1578 Q2_REC.currency,
1579 G_set_of_books_id,
1580 l_suspense_gl,
1581 l_accounted_dr,
1582 l_accounted_cr,
1583 Q2_REC.settlement_number, -- Bug 4004772.
1584 Q2_REC.date_type,
1585 Q2_REC.action_code);
1586 EXCEPTION
1587 when DUP_VAL_ON_INDEX then
1588 NULL;
1589 when OTHERS then
1590 FND_MESSAGE.Set_Name ('XTR','XTR_UNHANDLED_EXCEPTION');
1591 FND_MESSAGE.Set_Token ('PROCEDURE','GEN_JOURNALS');
1592 FND_MESSAGE.Set_Token ('EVENT','Inserting journal into table.');
1593 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.get);
1594
1595 -- 1336492 addition.
1596 l_ret_value := 2;
1597 END;
1598
1599 <<NEXT_Q2>>
1600
1601 Fetch Q2 INTO Q2_REC;
1602
1603 l_batch_id := in_batch_id;
1604
1605 -- Update DDA upon change in deal type, deal number, transaction number,
1606 -- date type, and amount date.
1607
1608 If (Q2_REC.deal_type <> nvl(l_prev_deal_type,'@@@@@@@') or
1609 Q2_REC.deal_number <> nvl(l_prev_deal_nbr,-1) or
1610 Q2_REC.transaction_number <> nvl(l_prev_transaction_nbr,-1) or
1611 Q2_REC.date_type <> nvl(l_prev_date_type,'@@@@@@@') or
1612 Q2_REC.amount_date <> l_prev_amount_date) then
1613
1614 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1615 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Change in deal type, deal #, trans #, date type, or amt date.');
1616 END IF;
1617
1618 --1336492 If (nvl(l_error_flag,'N') <> 'Y') then
1619
1620 -- 1336492 modification, replaces above l_error_flag condition.
1621 If (nvl(l_updt_flag,'Y') = 'Y') then
1622
1623 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1624 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Update DDA journal created flag.');
1625 END IF;
1626
1627 -- Update DDA to put BATCH_ID value once journal is generated
1628
1629 Update XTR_DEAL_DATE_AMOUNTS
1630 set BATCH_ID = l_batch_id
1631 where deal_type = l_prev_deal_type
1632 and deal_number = l_prev_deal_nbr
1633 and transaction_number = l_prev_transaction_nbr
1634 and date_type = l_prev_date_type
1635 and amount_date = l_prev_amount_date --Reverted Back to amount_date Bug 5235988
1636 and batch_id is null; -- prevent overwritting the previous batch_id
1637
1638 --Commit;
1639 End If; -- l_updt_flag = 'Y'.
1640
1641 l_prev_deal_type := Q2_REC.deal_type;
1642 l_prev_deal_nbr := Q2_REC.deal_number;
1643 l_prev_transaction_nbr := Q2_REC.transaction_number;
1644 l_prev_date_type := Q2_REC.date_type;
1645 l_prev_amount_date := Q2_REC.amount_date;
1646 End If; -- new group of entries.
1647 Else
1648 -- Begin 1336492 additions.
1649 -- Deal/Trans not validated, bypass journal creation for this group of deal type, deal nbr, and trans nbr.
1650
1651 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1652 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Deal/Trans not validated. Bypass entry creation.');
1653 END IF;
1654
1655 --1336492 l_error_flag := 'Y';
1656
1657 -- 1336492. Replaces above l_error_flag.
1658 l_updt_flag := 'N';
1659 l_ret_value := greatest(l_ret_value,1);
1660
1661 While (l_prev_deal_type = Q2_REC.deal_type and
1662 nvl(l_prev_deal_nbr,-1) = Q2_REC.deal_number and
1663 l_prev_transaction_nbr = Q2_REC.transaction_number)
1664 Loop
1665 Fetch Q2 INTO Q2_REC;
1666 Exit when Q2%NOTFOUND;
1667 End Loop;
1668
1669 If (Q2%FOUND) then
1670 l_prev_deal_type := Q2_REC.deal_type;
1671 l_prev_deal_nbr := Q2_REC.deal_number;
1672 l_prev_transaction_nbr := Q2_REC.transaction_number;
1673 l_prev_date_type := Q2_REC.date_type;
1674 l_prev_amount_date := Q2_REC.amount_date;
1675 Else
1676 l_prev_deal_nbr := null;
1677 End If;
1678
1679 -- End 1336492 additions.
1680 End If; -- deal/trans validated or no validation required.
1681 END LOOP;
1682 Close Q2;
1683
1684 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1685 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Exiting Q2.');
1686 END IF;
1687
1688 --1336492 If (l_prev_deal_nbr is NOT NULL and nvl(l_error_flag,'N') <> 'Y') then
1689
1690 -- 1336492.
1691 -- Modified condition to use l_updt_flag instead of l_error_flag.
1692 If (l_prev_deal_nbr is NOT NULL and nvl(l_updt_flag,'Y') = 'Y') then
1693
1694 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1695 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Update last batch of Q2 data.');
1696 END IF;
1697
1698 Update XTR_DEAL_DATE_AMOUNTS
1699 set batch_id = l_batch_id
1700 where deal_type = l_prev_deal_type
1701 and date_type = l_prev_date_type
1702 and deal_number = l_prev_deal_nbr
1703 and transaction_number = l_prev_transaction_nbr
1704 and amount_date = l_prev_amount_date --reverted Back to amount_date Bug 5235988
1705 and batch_id is null; -- prevent overwritting the previous batch ID
1706
1707 --Commit;
1708
1709 -- Removed for 1336492 to prevent rollback of unvalidated deals/transactions from the temporary table.
1710 /* Else
1711 -- Rollback only after entire cursor has been processed and errors were encountered.
1712 -- Otherwise, will cause "fetch out of sequence" error if done at "group" level.
1713
1714 If (nvl(l_error_flag,'N') = 'Y') then
1715 Rollback;
1716 End If;
1717 */
1718 End If;
1719
1720 l_ccid := NULL;
1721 l_prev_deal_type := NULL;
1722 l_prev_deal_nbr := to_number(NULL);
1723 l_prev_transaction_nbr := to_number(NULL);
1724 l_prev_date_type := NULL;
1725 l_prev_amount_date := to_date(NULL);
1726
1727 ---------------------------------------------------
1728 -- Q3 - Generate Journals for Exposure Transactions
1729 ---------------------------------------------------
1730
1731 Open Q3;
1732 Fetch Q3 INTO Q3_REC;
1733
1734 l_batch_id := in_batch_id;
1735
1736 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1737 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Entering Q3 loop.');
1738 END IF;
1739
1740 While Q3%FOUND LOOP
1741 --
1742 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1743 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Company Code = ' || Q3_REC.company_code);
1744 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Deal Type = ' || Q3_REC.deal_type);
1745 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Deal Number = ' || to_char(Q3_REC.deal_number));
1746 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Transaction Nbr = ' || to_char(Q3_REC.trans_number));
1747 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Amount Date = ' || to_char(Q3_REC.amount_date,'MM/DD/RRRR'));
1748 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Currency = ' || Q3_REC.currency);
1749 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Deal Subtype = ' || Q3_REC.deal_subtype);
1750 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Product Type = ' || Q3_REC.product_type);
1751 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Amount Type = ' || Q3_REC.amount_type);
1752 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Debit Amount = ' || to_char(Q3_REC.debit_amount));
1753 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Credit Amount = ' || to_char(Q3_REC.credit_amount));
1754 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'CCID = ' || to_char(Q3_REC.type_ccid));
1755 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Bank CCID = ' || to_char(Q3_REC.bank_ccid));
1756 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Settlement # = ' || to_char(Q3_REC.settlement_number));
1757 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Date Type = ' || Q3_REC.date_type);
1758 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Action Code = ' || Q3_REC.action_code);
1759 END IF;
1760
1761 -- 1336492 additions.
1762 -- Added logic to determine validation status of EXP transaction.
1763 -- If unvalidated and system param Validation Required for Accounting is set to 'Y',
1764 -- then prevent creation of journal entry for the transaction.
1765
1766 l_updt_flag := 'Y';
1767
1768 If (Q3_REC.validated_by is NULL) then
1769 l_val_flag := 'N';
1770 Else
1771 l_val_flag := 'Y';
1772 End If;
1773
1774 If (l_valreq = 'Y' and VALIDATED(Q3_REC.deal_type, Q3_REC.deal_number, Q3_REC.trans_number, l_val_flag, null)) or
1775 (l_valreq = 'N') then
1776
1777 --start bug 2804548
1778 if q3_rec.deal_type in ('BOND','EXP') and
1779 q3_rec.trans_number is not null then
1780 v_ChkCpnRateReset_in.deal_type:=q3_rec.deal_type;
1781 v_ChkCpnRateReset_in.transaction_no:=q3_rec.trans_number;
1782 v_ChkCpnRateReset_in.deal_no:=q3_rec.deal_number;
1783 xtr_mm_covers.check_coupon_rate_reset(v_ChkCpnRateReset_in,
1784 v_ChkCpnRateReset_out);
1785 --if the coupon or its tax comp has not been reset
1786 --print out a warning message.
1787 if not v_ChkCpnRateReset_out.yes then
1788 FND_MESSAGE.Set_Name ('XTR','XTR_COUPON_RESET_DEAL');
1789 FND_MESSAGE.Set_Token ('DEAL_NO',v_ChkCpnRateReset_out.deal_no);
1790 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
1791 --1336492 g_gen_journal_retcode:=1;
1792
1793 -- 1336492. Replaces above g_gen_journal_retcode.
1794 l_ret_value := greatest(l_ret_value,1);
1795
1796 end if;
1797 end if;
1798 --end bug 2804548
1799
1800 -- Use company's suspense ccid if l_ccid is null.
1801
1802 If (Q3_REC.type_ccid is NULL) then
1803 Q3_REC.type_ccid := G_suspense_ccid;
1804 l_suspense_gl := 'Y';
1805 Else
1806 l_suspense_gl := null;
1807 End If;
1808
1809 -- Calculate accounted_dr and accounted_cr amounts.
1810
1811 BEGIN
1812 l_accounted_dr := GL_CURRENCY_API.Convert_Amount (
1813 Q3_REC.currency,
1814 l_sob_currency,
1815 Q3_REC.amount_date,
1816 l_pty_convert_type,
1817 Q3_REC.debit_amount);
1818
1819 l_accounted_cr := GL_CURRENCY_API.Convert_Amount (
1820 Q3_REC.currency,
1821 l_sob_currency,
1822 Q3_REC.amount_date,
1823 l_pty_convert_type,
1824 Q3_REC.credit_amount);
1825 EXCEPTION
1826 when GL_CURRENCY_API.INVALID_CURRENCY then
1827 FND_MESSAGE.Set_Name ('XTR','XTR_2206');
1828 FND_MESSAGE.Set_Token ('CURR1',Q3_REC.currency);
1829 FND_MESSAGE.Set_Token ('CURR2',l_sob_currency);
1830 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
1831 --1336492 l_error_flag := 'Y';
1832
1833 -- 1336492. Replaces l_error_flag.
1834 l_ret_value := greatest(l_ret_value,2);
1835 l_updt_flag := 'N';
1836
1837 Goto NEXT_Q3;
1838
1839 when GL_CURRENCY_API.NO_RATE then
1840 FND_MESSAGE.Set_Name ('XTR','XTR_2207');
1841 FND_MESSAGE.Set_Token ('CURR1',Q3_REC.currency);
1842 FND_MESSAGE.Set_Token ('CURR2',l_sob_currency);
1843 FND_MESSAGE.Set_Token ('XCHG_DATE',to_char(Q3_REC.amount_date));
1844 FND_MESSAGE.Set_Token ('C_TYPE', nvl(l_pty_user_convert_type,l_pty_convert_type));
1845 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.get);
1846 --1336492 l_error_flag := 'Y';
1847
1848 -- 1336492. Replaces l_error_flag.
1849 l_ret_value := greatest(l_ret_value,2);
1850 l_updt_flag := 'N';
1851
1852 Goto NEXT_Q3;
1853 END;
1854
1855 BEGIN
1856 Insert into XTR_JOURNALS
1857 (batch_id,
1858 company_code,
1859 journal_date,
1860 orig_journal_date,
1861 deal_number,
1862 transaction_number,
1863 deal_type,
1864 deal_subtype,
1865 product_type,
1866 portfolio_code,
1867 debit_amount,
1868 credit_amount,
1869 code_combination_id,
1870 amount_type,
1871 created_by,
1872 created_on,
1873 currency,
1874 set_of_books_id,
1875 suspense_gl,
1876 accounted_dr,
1877 accounted_cr,
1878 settlement_number,
1879 date_type,
1880 action_code)
1881 Values
1882 (l_batch_id,
1883 Q3_REC.company_code,
1884 nvl(Q3_REC.amount_date,to_date('01/01/1997','MM/DD/YYYY')),
1885 nvl(Q3_REC.amount_date,to_date('01/01/1997','MM/DD/YYYY')),
1886 Q3_REC.deal_number,
1887 nvl(Q3_REC.trans_number,999),
1888 Q3_REC.deal_type,
1889 Q3_REC.deal_subtype,
1890 Q3_REC.product_type,
1891 Q3_REC.portfolio_code,
1892 Q3_REC.debit_amount,
1893 Q3_REC.credit_amount,
1894 Q3_REC.type_ccid,
1895 Q3_REC.amount_type,
1896 G_user,
1897 trunc(SYSDATE),
1898 Q3_REC.currency,
1899 G_set_of_books_id,
1900 l_suspense_gl,
1901 l_accounted_dr,
1902 l_accounted_cr,
1903 Q3_REC.settlement_number,
1904 Q3_REC.date_type,
1905 Q3_REC.action_code);
1906
1907 EXCEPTION
1908 when DUP_VAL_ON_INDEX then
1909 NULL;
1910 when OTHERS then
1911 FND_MESSAGE.Set_Name ('XTR','XTR_UNHANDLED_EXCEPTION');
1912 FND_MESSAGE.Set_Token ('PROCEDURE','GEN_JOURNALS');
1913 FND_MESSAGE.Set_Token ('EVENT','Inserting exposure journal into table.');
1914 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.get);
1915
1916 -- 1336492.
1917 l_ret_value := greatest(l_ret_value,2);
1918 END;
1919
1920 -- Create journal entry for bank acct side.
1921
1922 If (Q3_REC.bank_ccid is NULL) then
1923 Q3_REC.bank_ccid := G_suspense_ccid;
1924 l_suspense_gl := 'Y';
1925 Else
1926 l_suspense_gl := null;
1927 End If;
1928
1929 -- "Flip" DR/CR from EXP jrnl entry for bank acct side.
1930
1931 l_tmp_amt := Q3_REC.debit_amount;
1932 Q3_REC.debit_amount := Q3_REC.credit_amount;
1933 Q3_REC.credit_amount := l_tmp_amt;
1934
1935 l_tmp_amt := l_accounted_dr;
1936 l_accounted_dr := l_accounted_cr;
1937 l_accounted_cr := l_tmp_amt;
1938
1939 BEGIN
1940 Insert into XTR_JOURNALS
1941 (batch_id,
1942 company_code,
1943 journal_date,
1944 orig_journal_date,
1945 deal_number,
1946 transaction_number,
1947 deal_type,
1948 deal_subtype,
1949 product_type,
1950 portfolio_code,
1951 debit_amount,
1952 credit_amount,
1953 code_combination_id,
1954 amount_type,
1955 created_by,
1956 created_on,
1957 currency,
1958 set_of_books_id,
1959 suspense_gl,
1960 accounted_dr,
1961 accounted_cr,
1962 settlement_number,
1963 date_type,
1964 action_code)
1965 Values
1966 (l_batch_id,
1967 Q3_REC.company_code,
1968 nvl(Q3_REC.amount_date,to_date('01/01/1997','MM/DD/YYYY')),
1969 nvl(Q3_REC.amount_date,to_date('01/01/1997','MM/DD/YYYY')),
1970 Q3_REC.deal_number,
1971 nvl(Q3_REC.trans_number,999),
1972 Q3_REC.deal_type,
1973 Q3_REC.deal_subtype,
1974 Q3_REC.product_type,
1975 Q3_REC.portfolio_code,
1976 Q3_REC.debit_amount,
1977 Q3_REC.credit_amount,
1978 Q3_REC.bank_ccid,
1979 Q3_REC.amount_type,
1980 G_user,
1981 trunc(SYSDATE),
1982 Q3_REC.currency,
1983 G_set_of_books_id,
1984 l_suspense_gl,
1985 l_accounted_dr,
1986 l_accounted_cr,
1987 Q3_REC.settlement_number,
1988 Q3_REC.date_type,
1989 Q3_REC.action_code);
1990
1991 EXCEPTION
1992 when DUP_VAL_ON_INDEX then
1993 NULL;
1994 when OTHERS then
1995 FND_MESSAGE.Set_Name ('XTR','XTR_UNHANDLED_EXCEPTION');
1996 FND_MESSAGE.Set_Token ('PROCEDURE','GEN_JOURNALS');
1997 FND_MESSAGE.Set_Token ('EVENT','Inserting exposure journal into table.');
1998 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.get);
1999
2000 -- 1336492.
2001 l_ret_value := greatest(l_ret_value,2);
2002
2003 END;
2004
2005 -- 1336492. Added update condition.
2006
2007 If (nvl(l_updt_flag,'Y') = 'Y') then
2008 Update XTR_DEAL_DATE_AMOUNTS
2009 set BATCH_ID = l_batch_id
2010 where company_code = in_company
2011 and deal_type = 'EXP'
2012 and deal_number = Q3_REC.deal_number
2013 and settlement_number = Q3_REC.settlement_number
2014 and settle = 'Y'
2015 and batch_id is null; -- prevent overwritting the previous batch_id
2016 End If;
2017
2018 --Commit;
2019 Else
2020 -- branch added for 1336492.
2021 l_ret_value := greatest(l_ret_value,1);
2022 End If; -- validated or acct does not require validation.
2023
2024 <<NEXT_Q3>>
2025
2026 Fetch Q3 INTO Q3_REC;
2027
2028 l_batch_id := in_batch_id;
2029 END LOOP;
2030 Close Q3;
2031
2032 -- Removed for 1336492 to preserve list of unvalidated deals being stored in temporary table.
2033 /*
2034 -- If an error has already been encountered within the batch, rollback data.
2035 -- All cursors will be processed so that all errors will be logged for users to
2036 -- correct in one shot. But none of the batch information should be committed.
2037 -- Rolling back each cursor will help in rollback segment utilization.
2038
2039 If (nvl(l_error_flag,'N') = 'Y') then
2040 Rollback;
2041 End If;
2042 */
2043 ---------------------------------------
2044 -- Q4 - Generate Journals for Accruals.
2045 ---------------------------------------
2046
2047 l_prev_rowid := null;
2048 l_row_id := null;
2049
2050 -- Exclude accrual journal processing if Inaguaral batch or if batch is Non-Reval/Non-Accrual related.
2051
2052 if (nvl(in_upgrade_batch,'N') <> 'I') and
2053 (in_source_option is null) then
2054
2055 Open Q4;
2056 Fetch Q4 INTO Q4_REC;
2057
2058 l_prev_rowid := l_row_id;
2059
2060 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
2061 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Entering Q4 loop.');
2062 END IF;
2063
2064 While Q4%FOUND LOOP
2065 --
2066 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
2067 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Company Code = ' || Q4_REC.company_code);
2068 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Deal Type = ' || Q4_REC.deal_type);
2069 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Deal Number = ' || to_char(Q4_REC.deal_number));
2070 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Transaction Nbr = ' || to_char(Q4_REC.trans_number));
2071 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Amount Date = ' || to_char(Q4_REC.amount_date,'MM/DD/RRRR'));
2072 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Currency = ' || Q4_REC.currency);
2073 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Deal Subtype = ' || Q4_REC.deal_subtype);
2074 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Product Type = ' || Q4_REC.product_type);
2075 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Portfolio = ' || Q4_REC.portfolio_code);
2076 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Amount Type = ' || Q4_REC.amount_type);
2077 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Amount = ' || to_char(Q4_REC.accrls_amount));
2078 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'DR/CR = ' || Q4_REC.credit_or_debit);
2079 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'CCID = ' || to_char(Q4_REC.ccid));
2080 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Date Type = ' || Q4_REC.date_type);
2081 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Action Code = ' || Q4_REC.action_code);
2082 END IF;
2083
2084 If (Q4_REC.accrls_amount = 0) then
2085 NULL;
2086 Else
2087 -- Use company's suspense ccid if l_ccid is null.
2088
2089 If (Q4_REC.ccid is NULL) then
2090 Q4_REC.ccid := G_suspense_ccid;
2091 l_suspense_gl := 'Y';
2092 Else
2093 l_suspense_gl := null;
2094 End If;
2095
2096 -- Calculate accounted_dr and accounted_cr amounts.
2097
2098 BEGIN
2099 l_accounted_dr := 0;
2100 l_accounted_cr := 0;
2101
2102 If (Q4_REC.credit_or_debit = 'DR') then
2103 l_accounted_dr := GL_CURRENCY_API.Convert_Amount (
2104 Q4_REC.currency,
2105 l_sob_currency,
2106 Q4_REC.amount_date,
2107 l_pty_convert_type,
2108 Q4_REC.accrls_amount);
2109 Else
2110 l_accounted_cr := GL_CURRENCY_API.Convert_Amount (
2111 Q4_REC.currency,
2112 l_sob_currency,
2113 Q4_REC.amount_date,
2114 l_pty_convert_type,
2115 Q4_REC.accrls_amount);
2116 End If;
2117
2118 EXCEPTION
2119 when GL_CURRENCY_API.INVALID_CURRENCY then
2120 FND_MESSAGE.Set_Name ('XTR','XTR_2206');
2121 FND_MESSAGE.Set_Token ('CURR1',Q4_REC.currency);
2122 FND_MESSAGE.Set_Token ('CURR2',l_sob_currency);
2123 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
2124 --1336492 l_error_flag := 'Y';
2125 l_ret_value := greatest(l_ret_value,2);
2126 Goto NEXT_Q4;
2127
2128 when GL_CURRENCY_API.NO_RATE then
2129 FND_MESSAGE.Set_Name ('XTR','XTR_2207');
2130 FND_MESSAGE.Set_Token ('CURR1',Q4_REC.currency);
2131 FND_MESSAGE.Set_Token ('CURR2',l_sob_currency);
2132 FND_MESSAGE.Set_Token ('XCHG_DATE',to_char(Q4_REC.amount_date));
2133 FND_MESSAGE.Set_Token ('C_TYPE', nvl(l_pty_user_convert_type,l_pty_convert_type));
2134 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.get);
2135 --1336492 l_error_flag := 'Y';
2136 l_ret_value := greatest(l_ret_value,2);
2137 Goto NEXT_Q4;
2138 END;
2139
2140 BEGIN
2141 Insert into XTR_JOURNALS
2142 (batch_id,
2143 company_code,
2144 journal_date,
2145 orig_journal_date,
2146 deal_number,
2147 transaction_number,
2148 deal_type,
2149 deal_subtype,
2150 product_type,
2151 portfolio_code,
2152 debit_amount,
2153 credit_amount,
2154 code_combination_id,
2155 amount_type,
2156 created_by,
2157 created_on,
2158 currency,
2159 set_of_books_id,
2160 suspense_gl,
2161 accounted_dr,
2162 accounted_cr,
2163 date_type,
2164 action_code)
2165 Values
2166 (Q4_REC.batch_id,
2167 Q4_REC.company_code,
2168 nvl(Q4_REC.amount_date,to_date('01/01/1997','MM/DD/YYYY')),
2169 nvl(Q4_REC.amount_date,to_date('01/01/1997','MM/DD/YYYY')),
2170 Q4_REC.deal_number,
2171 nvl(Q4_REC.trans_number,999),
2172 Q4_REC.deal_type,
2173 Q4_REC.deal_subtype,
2174 Q4_REC.product_type,
2175 Q4_REC.portfolio_code,
2176 decode(Q4_REC.credit_or_debit,'DR',Q4_REC.accrls_amount,0),
2177 decode(Q4_REC.credit_or_debit,'CR',Q4_REC.accrls_amount,0),
2178 Q4_REC.ccid,
2179 Q4_REC.amount_type,
2180 G_user,
2181 trunc(SYSDATE),
2182 Q4_REC.currency,
2183 G_set_of_books_id,
2184 l_suspense_gl,
2185 l_accounted_dr,
2186 l_accounted_cr,
2187 Q4_REC.date_type,
2188 Q4_REC.action_code);
2189
2190 EXCEPTION
2191 when DUP_VAL_ON_INDEX then
2192 NULL;
2193 when OTHERS then
2194 FND_MESSAGE.Set_Name ('XTR','XTR_UNHANDLED_EXCEPTION');
2195 FND_MESSAGE.Set_Token ('PROCEDURE','GEN_JOURNALS');
2196 FND_MESSAGE.Set_Token ('EVENT','Inserting accruals journal into table');
2197 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.get);
2198
2199 -- 1336492.
2200 l_ret_value := greatest(l_ret_value,2);
2201 END;
2202
2203 End If; -- If zero amounts.
2204
2205 <<NEXT_Q4>>
2206
2207 Fetch Q4 INTO Q4_REC;
2208
2209 END LOOP;
2210 Close Q4;
2211
2212 -- Removed for 1336492 to preserve list of unvalidated deals being stored in temporary table.
2213 /* If (nvl(l_error_flag,'N') = 'Y') then
2214 Rollback;
2215 End If;
2216 */
2217 end if; -- of <nvl(in_upgrade_batch,'N') <> 'I' and p_source_option is null>
2218
2219
2220 --------------------------------------------------
2221 -- Q5 - Generate Journal rows for Revaluations.
2222 --------------------------------------------------
2223 l_prev_rowid := null;
2224 l_row_id := null;
2225
2226 -------------------------------------------------------------------------
2227 -- For inaugural batch, check if company has upgrade revaluation details
2228 -------------------------------------------------------------------------
2229 l_upgrade_reval := 'N';
2230 if (nvl(in_upgrade_batch,'N') = 'I') and
2231 (in_source_option is null) then
2232
2233 Open UPGRADE_REVAL;
2234 Fetch UPGRADE_REVAL into l_upgrade_reval;
2235 if UPGRADE_REVAL%NOTFOUND then
2236 l_upgrade_reval := 'N';
2237 end if;
2238 Close UPGRADE_REVAL;
2239 end if;
2240
2241 -----------------------------------------------------------------------------------------------
2242 -- If this is inaugural batch and there are no reval details, do not need to generate journals.
2243 -- Also exclude reval journals if batch is Non-Reval/Non-Accrual related.
2244 -----------------------------------------------------------------------------------------------
2245 if ((nvl(in_upgrade_batch,'N') = 'I' and l_upgrade_reval = 'Y') or nvl(in_upgrade_batch,'N') <> 'I') and
2246 (in_source_option is null) then
2247
2248 Open Q5;
2249 Fetch Q5 INTO Q5_REC;
2250
2251 l_prev_rowid := l_row_id;
2252
2253 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
2254 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Entering Q5 loop.');
2255 END IF;
2256
2257 While Q5%FOUND LOOP
2258 --
2259 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
2260 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Company Code = ' || Q5_REC.company_code);
2261 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Deal Type = ' || Q5_REC.deal_type);
2262 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Deal Number = ' || to_char(Q5_REC.deal_number));
2263 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Transaction Nbr = ' || to_char(Q5_REC.trans_number));
2264 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Amount Date = ' || to_char(Q5_REC.journal_date,'MM/DD/RRRR'));
2265 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Currency = ' || Q5_REC.reval_currency);
2266 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Deal Subtype = ' || Q5_REC.deal_subtype);
2267 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Product Type = ' || Q5_REC.product_type);
2268 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Portfolio = ' || Q5_REC.portfolio_code);
2269 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Amount Type = ' || Q5_REC.amount_type);
2270 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Action Code = ' || Q5_REC.action_code);
2271 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Revaluation Amount = ' || to_char(Q5_REC.amount));
2272 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'CCID = ' || to_char(Q5_REC.ccid));
2273 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'DR/CR = ' || Q5_REC.credit_or_debit);
2274 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Date Type = ' || Q5_REC.date_type);
2275 END IF;
2276
2277 If (nvl(Q5_REC.amount,0) = 0) then
2278 NULL;
2279
2280 Else
2281 -- Use company's suspense ccid if l_ccid is null.
2282
2283 If (Q5_REC.ccid is NULL) then
2284 Q5_REC.ccid := G_suspense_ccid;
2285 l_suspense_gl := 'Y';
2286 Else
2287 l_suspense_gl := null;
2288 End If;
2289
2290 -- Calculate accounted_dr and accounted_cr amounts.
2291
2292 BEGIN
2293 l_accounted_dr := 0;
2294 l_accounted_cr := 0;
2295 --l_tmp_amt := abs(l_realized_amt + l_unrealized_amt);
2296
2297 If (Q5_REC.credit_or_debit = 'DR') then
2298 l_accounted_dr := GL_CURRENCY_API.Convert_Amount (
2299 Q5_REC.reval_currency,
2300 l_sob_currency,
2301 Q5_REC.journal_date,
2302 l_pty_convert_type,
2303 Q5_REC.amount);
2304 Else
2305 l_accounted_cr := GL_CURRENCY_API.Convert_Amount (
2306 Q5_REC.reval_currency,
2307 l_sob_currency,
2308 Q5_REC.journal_date,
2309 l_pty_convert_type,
2310 Q5_REC.amount);
2311 End If;
2312
2313 EXCEPTION
2314 when GL_CURRENCY_API.INVALID_CURRENCY then
2315 FND_MESSAGE.Set_Name ('XTR','XTR_2206');
2316 FND_MESSAGE.Set_Token ('CURR1',Q5_REC.reval_currency);
2317 FND_MESSAGE.Set_Token ('CURR2',l_sob_currency);
2318 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
2319 --1336492 l_error_flag := 'Y';
2320 l_ret_value := greatest(l_ret_value,2);
2321 Goto NEXT_Q5;
2322
2323 when GL_CURRENCY_API.NO_RATE then
2324 FND_MESSAGE.Set_Name ('XTR','XTR_2207');
2325 FND_MESSAGE.Set_Token ('CURR1',Q5_REC.reval_currency);
2326 FND_MESSAGE.Set_Token ('CURR2',l_sob_currency);
2327 FND_MESSAGE.Set_Token ('XCHG_DATE',to_char(Q5_REC.journal_date));
2328 FND_MESSAGE.Set_Token ('C_TYPE', nvl(l_pty_user_convert_type,l_pty_convert_type));
2329 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.get);
2330 --1336492 l_error_flag := 'Y';
2331 l_ret_value := greatest(l_ret_value,2);
2332 Goto NEXT_Q5;
2333 END;
2334
2335
2336 --- bug 4641750 Added following lines
2337 if q5_rec.deal_type = 'FX' then
2338
2339 select currency_buy , currency_sell
2340 into l_curr_buy, l_curr_sell
2341 from
2342 xtr_deals where deal_no = Q5_REC.deal_number;
2343
2344
2345 if l_curr_buy = l_sob_currency then
2346 l_curr := l_curr_sell;
2347 elsif l_curr_sell = l_sob_currency then
2348 l_curr := l_curr_buy;
2349 end if;
2350
2351 end if;
2352
2353
2354 if q5_rec.deal_type = 'FX' and (l_curr_buy = l_sob_currency or l_curr_sell =l_sob_currency) then
2355 l_debit_amount := 0;
2356 l_credit_amount := 0;
2357
2358
2359 BEGIN
2360 Insert into XTR_JOURNALS
2361 (batch_id,
2362 company_code,
2363 journal_date,
2364 orig_journal_date,
2365 deal_number,
2366 transaction_number,
2367 deal_type,
2368 deal_subtype,
2369 product_type,
2370 portfolio_code,
2371 debit_amount,
2372 credit_amount,
2373 code_combination_id,
2374 amount_type,
2375 created_by,
2376 created_on,
2377 currency,
2378 set_of_books_id,
2379 suspense_gl,
2380 accounted_dr,
2381 accounted_cr,
2382 date_type,
2383 action_code)
2384 Values
2385 (Q5_REC.batch_id,
2386 Q5_REC.company_code,
2387 nvl(Q5_REC.journal_date,to_date('01/01/1997','MM/DD/YYYY')),
2388 nvl(Q5_REC.journal_date,to_date('01/01/1997','MM/DD/YYYY')),
2389 Q5_REC.deal_number,
2390 nvl(Q5_REC.trans_number,999),
2391 Q5_REC.deal_type,
2392 Q5_REC.deal_subtype,
2393 Q5_REC.product_type,
2394 Q5_REC.portfolio_code,
2395 l_debit_amount,
2396 l_credit_amount,
2397 Q5_REC.ccid,
2398 Q5_REC.amount_type,
2399 G_user,
2400 trunc(SYSDATE),
2401 l_curr,
2402 G_set_of_books_id,
2403 l_suspense_gl,
2404 l_accounted_dr,
2405 l_accounted_cr,
2406 Q5_REC.date_type,
2407 Q5_REC.action_code);
2408
2409
2410 EXCEPTION
2411 when DUP_VAL_ON_INDEX then
2412 NULL;
2413 when OTHERS then
2414 FND_MESSAGE.Set_Name ('XTR','XTR_UNHANDLED_EXCEPTION');
2415 FND_MESSAGE.Set_Token ('PROCEDURE','GEN_JOURNALS');
2416 FND_MESSAGE.Set_Token ('EVENT','Inserting revaluation journal into table');
2417 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.get);
2418
2419 END;
2420
2421
2422 else
2423 -- bug 4641750 End code change
2424
2425
2426
2427 BEGIN
2428 Insert into XTR_JOURNALS
2429 (batch_id,
2430 company_code,
2431 journal_date,
2432 orig_journal_date,
2433 deal_number,
2434 transaction_number,
2435 deal_type,
2436 deal_subtype,
2437 product_type,
2438 portfolio_code,
2439 debit_amount,
2440 credit_amount,
2441 code_combination_id,
2442 amount_type,
2443 created_by,
2444 created_on,
2445 currency,
2446 set_of_books_id,
2447 suspense_gl,
2448 accounted_dr,
2449 accounted_cr,
2450 date_type,
2451 action_code)
2452 Values
2453 (Q5_REC.batch_id,
2454 Q5_REC.company_code,
2455 nvl(Q5_REC.journal_date,to_date('01/01/1997','MM/DD/YYYY')),
2456 nvl(Q5_REC.journal_date,to_date('01/01/1997','MM/DD/YYYY')),
2457 Q5_REC.deal_number,
2458 nvl(Q5_REC.trans_number,999),
2459 Q5_REC.deal_type,
2460 Q5_REC.deal_subtype,
2461 Q5_REC.product_type,
2462 Q5_REC.portfolio_code,
2463 -- decode(l_amount_type,'CCYREAL',0,'CCYAMRL',0,decode(l_dr_or_cr,'DR',l_reval_amt,0)),
2464 -- decode(l_amount_type,'CCYREAL',0,'CCYAMRL',0,decode(l_dr_or_cr,'CR',l_reval_amt,0)),
2465 decode(Q5_REC.amount_type,'CCYREAL',decode(Q5_REC.deal_type,'FX',
2466 decode(Q5_REC.credit_or_debit,'DR',Q5_REC.amount,0),0),'CCYAMRL',0,
2467 decode(Q5_REC.credit_or_debit,'DR',Q5_REC.amount,0)),
2468 decode(Q5_REC.amount_type,'CCYREAL',decode(Q5_REC.deal_type,'FX',
2469 decode(Q5_REC.credit_or_debit,'CR',Q5_REC.amount,0),0),'CCYAMRL',0,
2470 decode(Q5_REC.credit_or_debit,'CR',Q5_REC.amount,0)),
2471 Q5_REC.ccid,
2472 Q5_REC.amount_type,
2473 G_user,
2474 trunc(SYSDATE),
2475 decode(Q5_REC.amount_type, 'CCYREAL', decode(Q5_REC.deal_type, 'FX', Q5_REC.reval_currency, Q5_REC.deal_currency),
2476 'CCYAMRL',Q5_REC.deal_currency,Q5_REC.reval_currency), -- bug 2376980
2477 G_set_of_books_id,
2478 l_suspense_gl,
2479 l_accounted_dr,
2480 l_accounted_cr,
2481 Q5_REC.date_type,
2482 Q5_REC.action_code);
2483
2484 EXCEPTION
2485 when DUP_VAL_ON_INDEX then
2486 NULL;
2487 when OTHERS then
2488 FND_MESSAGE.Set_Name ('XTR','XTR_UNHANDLED_EXCEPTION');
2489 FND_MESSAGE.Set_Token ('PROCEDURE','GEN_JOURNALS');
2490 FND_MESSAGE.Set_Token ('EVENT','Inserting revaluation journal into table');
2491 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.get);
2492
2493 -- 1336492.
2494 l_ret_value := greatest(l_ret_value,2);
2495 END;
2496
2497 END If; -- if fx deal and one currency is in SOB
2498
2499
2500 End If; -- If zero amounts.
2501
2502 <<NEXT_Q5>>
2503
2504 Fetch Q5 INTO Q5_REC;
2505 END LOOP;
2506 Close Q5;
2507
2508 end if; -- of <(in_upgrade_batch = 'I' and l_upgrade_reval = 'Y') or in_upgrade_batch <> 'I'>
2509
2510
2511 --------------------------------------------------
2512 -- Q6 - Generate Journal rows for FX Forward Revaluations
2513 -- and Retrospective test.
2514 --------------------------------------------------
2515 l_prev_rowid := null;
2516 l_row_id := null;
2517 l_q6_deal_no := null;
2518
2519 /*********** ***************************************************/
2520 /* Set all amount type flags as FALSE in the beginning. System */
2521 /* will process the eff flag with 'T' (retro) first. If any of*/
2522 /* the T record been found for the deal number, the associated */
2523 /* amount flag will be set the 'TRUE', and syste should not */
2524 /* process 'R' record for the same deal number to avoid */
2525 /* duplication of journal entries. */
2526 /***************************************************************/
2527 Open Q6;
2528 Fetch Q6 INTO Q6_REC;
2529
2530 l_prev_rowid := l_row_id;
2531
2532 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
2533 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Entering Q6 loop.');
2534 END IF;
2535
2536 While Q6%FOUND LOOP
2537 If nvl(l_q6_deal_no, -1) <> Q6_REC.deal_number then
2538 l_q6_deal_no := Q6_REC.deal_number;
2539 l_UNREAL := FALSE;
2540 l_CCYUNRL := FALSE;
2541 l_REAL := FALSE;
2542 l_CCYREAL := FALSE;
2543 l_EFF_EXIST := FALSE;
2544
2545 If Q6_REC.reval_eff_flag = 'T' then
2546 l_EFF_EXIST := TRUE;
2547 End if;
2548
2549 If l_EFF_EXIST = TRUE then
2550 If Q6_REC.amount_type in ('UNREAL', 'NRECUNR') then
2551 L_UNREAL := TRUE;
2552 end if;
2553 If Q6_REC.amount_type in ('CCYUNRL', 'NRECCYU') then
2554 L_CCYUNRL := TRUE;
2555 end if;
2556 If Q6_REC.amount_type in ('REAL', 'NRECUNR') then
2557 L_REAL := TRUE;
2558 end if;
2559 If Q6_REC.amount_type in ('CCYREAL', 'NRECCYU') then
2560 L_CCYREAL := TRUE;
2561 end if;
2562 End if;
2563 end if;
2564
2565 If l_EFF_EXIST = TRUE and
2566 ((Q6_REC.reval_eff_flag = 'R' and Q6_REC.amount_type = 'UNREAL' and
2567 l_UNREAL = TRUE) or
2568 (Q6_REC.reval_eff_flag = 'R' and Q6_REC.amount_type = 'CCYUNRL'
2569 and l_CCYUNRL = TRUE) or
2570 (Q6_REC.reval_eff_flag = 'R' and Q6_REC.amount_type = 'REAL' and
2571 l_REAL = TRUE) or
2572 (Q6_REC.reval_eff_flag = 'R' and Q6_REC.amount_type = 'CCYREAL'
2573 and l_CCYREAL = TRUE)) then
2574 NULL; -- Do not process this Revaluations record
2575 Else -- existing code
2576
2577 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
2578 xtr_debug_pkg.debug ('GEN_JOURNALS: ' ||'Company Code = '|| Q6_REC.company_code);
2579 xtr_debug_pkg.debug ('GEN_JOURNALS: ' ||'Deal Type = ' || Q6_REC.deal_type);
2580 xtr_debug_pkg.debug ('GEN_JOURNALS: ' ||'Deal Number = '|| to_char(Q6_REC.deal_number));
2581 xtr_debug_pkg.debug ('GEN_JOURNALS: ' ||'Transaction Nbr = '|| to_char(Q6_REC.trans_number));
2582 xtr_debug_pkg.debug ('GEN_JOURNALS: ' ||'Amount Date = '|| to_char(Q6_REC.journal_date,'MM/DD/RRRR'));
2583 xtr_debug_pkg.debug ('GEN_JOURNALS: ' ||'Currency = '|| Q6_REC.reval_currency);
2584 xtr_debug_pkg.debug ('GEN_JOURNALS: ' ||'Deal Subtype = '|| Q6_REC.deal_subtype);
2585 xtr_debug_pkg.debug ('GEN_JOURNALS: ' ||'Product Type = '|| Q6_REC.product_type);
2586 xtr_debug_pkg.debug ('GEN_JOURNALS: ' ||'Portfolio = '|| Q6_REC.portfolio_code);
2587 xtr_debug_pkg.debug ('GEN_JOURNALS: ' ||'Amount Type = '|| Q6_REC.amount_type);
2588 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Action Code = '|| Q6_REC.action_code);
2589 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Revaluation Amount = '|| to_char(Q6_REC.amount));
2590 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'CCID = '|| to_char(Q6_REC.ccid));
2591 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'DR/CR = '|| Q6_REC.credit_or_debit);
2592 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Date Type = '|| Q6_REC.date_type);
2593 END IF;
2594
2595 If (nvl(Q6_REC.amount,0) = 0) then
2596 NULL;
2597 Else
2598 -- Calculate accounted_dr and accounted_cr amounts.
2599 BEGIN
2600 l_accounted_dr := 0;
2601 l_accounted_cr := 0;
2602
2603 If (Q6_REC.credit_or_debit = 'DR') then
2604 l_accounted_dr := GL_CURRENCY_API.Convert_Amount (
2605 Q6_REC.reval_currency,
2606 l_sob_currency,
2607 Q6_REC.journal_date,
2608 l_pty_convert_type,
2609 Q6_REC.amount);
2610 Else
2611 l_accounted_cr := GL_CURRENCY_API.Convert_Amount (
2612 Q6_REC.reval_currency,
2613 l_sob_currency,
2614 Q6_REC.journal_date,
2615 l_pty_convert_type,
2616 Q6_REC.amount);
2617 End If;
2618 EXCEPTION
2619 when GL_CURRENCY_API.INVALID_CURRENCY then
2620 FND_MESSAGE.Set_Name ('XTR','XTR_2206');
2621 FND_MESSAGE.Set_Token ('CURR1',Q6_REC.reval_currency);
2622 FND_MESSAGE.Set_Token ('CURR2',l_sob_currency);
2623 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
2624 l_ret_value := greatest(l_ret_value,2);
2625 Goto NEXT_Q6;
2626
2627 when GL_CURRENCY_API.NO_RATE then
2628 FND_MESSAGE.Set_Name ('XTR','XTR_2207');
2629 FND_MESSAGE.Set_Token ('CURR1',Q6_REC.reval_currency);
2630 FND_MESSAGE.Set_Token ('CURR2',l_sob_currency);
2631 FND_MESSAGE.Set_Token ('XCHG_DATE',to_char(Q6_REC.journal_date));
2632 FND_MESSAGE.Set_Token ('C_TYPE', nvl(l_pty_user_convert_type,l_pty_convert_type));
2633 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.get);
2634 l_ret_value := greatest(l_ret_value,2);
2635 Goto NEXT_Q6;
2636 END;
2637
2638
2639 --- bug 4641750 Added following lines
2640
2641 select currency_buy , currency_sell
2642 into l_curr_buy, l_curr_sell
2643 from
2644 xtr_deals where deal_no = Q6_REC.deal_number;
2645
2646
2647 if l_curr_buy = l_sob_currency then
2648 l_curr := l_curr_sell;
2649 elsif l_curr_sell = l_sob_currency then
2650 l_curr := l_curr_buy;
2651 end if;
2652
2653
2654
2655 if (l_curr_buy = l_sob_currency or l_curr_sell =l_sob_currency) then
2656
2657 l_debit_amount := 0;
2658 l_credit_amount := 0;
2659
2660
2661 BEGIN
2662 Insert into XTR_JOURNALS
2663 (batch_id,
2664 company_code,
2665 journal_date,
2666 orig_journal_date,
2667 deal_number,
2668 transaction_number,
2669 deal_type,
2670 deal_subtype,
2671 product_type,
2672 portfolio_code,
2673 debit_amount,
2674 credit_amount,
2675 code_combination_id,
2676 amount_type,
2677 created_by,
2678 created_on,
2679 currency,
2680 set_of_books_id,
2681 suspense_gl,
2682 accounted_dr,
2683 accounted_cr,
2684 date_type,
2685 action_code)
2686 Values
2687 (Q6_REC.batch_id,
2688 Q6_REC.company_code,
2689 nvl(Q6_REC.journal_date,to_date('01/01/1997','MM/DD/YYYY')),
2690 nvl(Q6_REC.journal_date,to_date('01/01/1997','MM/DD/YYYY')),
2691 Q6_REC.deal_number,
2692 nvl(Q6_REC.trans_number,999),
2693 Q6_REC.deal_type,
2694 Q6_REC.deal_subtype,
2695 Q6_REC.product_type,
2696 Q6_REC.portfolio_code,
2697 l_debit_amount,
2698 l_credit_amount,
2699 Q6_REC.ccid,
2700 Q6_REC.amount_type,
2701 G_user,
2702 trunc(SYSDATE),
2703 l_curr,
2704 G_set_of_books_id,
2705 l_suspense_gl,
2706 l_accounted_dr,
2707 l_accounted_cr,
2708 Q6_REC.date_type,
2709 Q6_REC.action_code);
2710
2711
2712 EXCEPTION
2713 when DUP_VAL_ON_INDEX then
2714 NULL;
2715 when OTHERS then
2716 FND_MESSAGE.Set_Name ('XTR','XTR_UNHANDLED_EXCEPTION');
2717 FND_MESSAGE.Set_Token ('PROCEDURE','GEN_JOURNALS');
2718 FND_MESSAGE.Set_Token ('EVENT','Inserting revaluation journal into table');
2719 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.get);
2720
2721 END;
2722
2723
2724 else
2725
2726
2727 --- bug 4641750 Ended code change
2728
2729
2730
2731
2732 BEGIN
2733 Insert into XTR_JOURNALS
2734 (batch_id,
2735 company_code,
2736 journal_date,
2737 orig_journal_date,
2738 deal_number,
2739 transaction_number,
2740 deal_type,
2741 deal_subtype,
2742 product_type,
2743 portfolio_code,
2744 debit_amount,
2745 credit_amount,
2746 code_combination_id,
2747 amount_type,
2748 created_by,
2749 created_on,
2750 currency,
2751 set_of_books_id,
2752 suspense_gl,
2753 accounted_dr,
2754 accounted_cr,
2755 date_type,
2756 action_code)
2757 Values
2758 (Q6_REC.batch_id,
2759 Q6_REC.company_code,
2760 nvl(Q6_REC.journal_date,to_date('01/01/1997','MM/DD/YYYY')),
2761 nvl(Q6_REC.journal_date,to_date('01/01/1997','MM/DD/YYYY')),
2762 Q6_REC.deal_number,
2763 nvl(Q6_REC.trans_number,999),
2764 Q6_REC.deal_type,
2765 Q6_REC.deal_subtype,
2766 Q6_REC.product_type,
2767 Q6_REC.portfolio_code,
2768 decode(Q6_REC.credit_or_debit,'DR',Q6_REC.amount,0),
2769 decode(Q6_REC.credit_or_debit,'CR',Q6_REC.amount,0),
2770 Q6_REC.ccid,
2771 Q6_REC.amount_type,
2772 G_user,
2773 trunc(SYSDATE),
2774 decode(Q6_REC.amount_type, 'CCYREAL', Q6_REC.deal_currency,
2775 Q6_REC.reval_currency),
2776 G_set_of_books_id,
2777 l_suspense_gl,
2778 l_accounted_dr,
2779 l_accounted_cr,
2780 Q6_REC.date_type,
2781 Q6_REC.action_code);
2782
2783 if Q6_REC.reval_eff_flag = 'T' then
2784 FND_MESSAGE.set_name('XTR', 'XTR_HEDGE_JOURNAL_TREAT');
2785 FND_MESSAGE.Set_Token ('DEAL_NO', Q6_REC.deal_number);
2786 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.get);
2787 End if;
2788
2789 EXCEPTION
2790 when DUP_VAL_ON_INDEX then
2791 NULL;
2792 when OTHERS then
2793 FND_MESSAGE.Set_Name ('XTR','XTR_UNHANDLED_EXCEPTION');
2794 FND_MESSAGE.Set_Token ('PROCEDURE','GEN_JOURNALS');
2795 FND_MESSAGE.Set_Token ('EVENT','Inserting revaluation journal into table');
2796 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.get);
2797 l_ret_value := greatest(l_ret_value,2);
2798 END;
2799
2800 End if; -- Fx in SOB currency
2801
2802 End If; -- If zero amounts.
2803 End if;
2804
2805 <<NEXT_Q6>>
2806
2807 Fetch Q6 INTO Q6_REC;
2808 END LOOP;
2809 Close Q6;
2810
2811 --------------------------------------------------
2812 -- Q7 - Generate Journal rows for Hedge Items.
2813 --------------------------------------------------
2814 l_prev_rowid := null;
2815 l_row_id := null;
2816
2817 Open Q7;
2818 Fetch Q7 INTO Q7_REC;
2819
2820 l_prev_rowid := l_row_id;
2821
2822 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
2823 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Entering Q7 loop.');
2824 END IF;
2825
2826 While Q7%FOUND LOOP
2827 --
2828 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
2829 xtr_debug_pkg.debug ('GEN_JOURNALS: ' ||'Company Code = '|| Q7_REC.company_code);
2830 xtr_debug_pkg.debug ('GEN_JOURNALS: ' ||'Deal Type = ' || Q7_REC.deal_type);
2831 xtr_debug_pkg.debug ('GEN_JOURNALS: ' ||'Deal Number = '|| to_char(Q7_REC.deal_number));
2832 xtr_debug_pkg.debug ('GEN_JOURNALS: ' ||'Transaction Nbr = '|| to_char(Q7_REC.trans_number));
2833 xtr_debug_pkg.debug ('GEN_JOURNALS: ' ||'Amount Date = '|| to_char(Q7_REC.journal_date,'MM/DD/RRRR'));
2834 xtr_debug_pkg.debug ('GEN_JOURNALS: ' ||'Currency = '|| Q7_REC.reval_currency);
2835 xtr_debug_pkg.debug ('GEN_JOURNALS: ' ||'Deal Subtype = '|| Q7_REC.deal_subtype);
2836 xtr_debug_pkg.debug ('GEN_JOURNALS: ' ||'Product Type = '|| Q7_REC.product_type);
2837 xtr_debug_pkg.debug ('GEN_JOURNALS: ' ||'Portfolio = '|| Q7_REC.portfolio_code);
2838 xtr_debug_pkg.debug ('GEN_JOURNALS: ' ||'Amount Type = '|| Q7_REC.amount_type);
2839 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Action Code = '|| Q7_REC.action_code);
2840 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Revaluation Amount = '|| to_char(Q7_REC.amount));
2841 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'CCID = '|| to_char(Q7_REC.ccid));
2842 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'DR/CR = '|| Q7_REC.credit_or_debit);
2843 xtr_debug_pkg.debug ('GEN_JOURNALS: ' || 'Date Type = '|| Q7_REC.date_type);
2844 END IF;
2845
2846 If (nvl(Q7_REC.amount,0) = 0) then
2847 NULL;
2848 Else
2849 -- Calculate accounted_dr and accounted_cr amounts.
2850 BEGIN
2851 l_accounted_dr := 0;
2852 l_accounted_cr := 0;
2853
2854 If (Q7_REC.credit_or_debit = 'DR') then
2855 l_accounted_dr := GL_CURRENCY_API.Convert_Amount (
2856 Q7_REC.reval_currency,
2857 l_sob_currency,
2858 Q7_REC.journal_date,
2859 l_pty_convert_type,
2860 Q7_REC.amount);
2861 Else
2862 l_accounted_cr := GL_CURRENCY_API.Convert_Amount (
2863 Q7_REC.reval_currency,
2864 l_sob_currency,
2865 Q7_REC.journal_date,
2866 l_pty_convert_type,
2867 Q7_REC.amount);
2868 End If;
2869
2870 EXCEPTION
2871 when GL_CURRENCY_API.INVALID_CURRENCY then
2872 FND_MESSAGE.Set_Name ('XTR','XTR_2206');
2873 FND_MESSAGE.Set_Token ('CURR1',Q7_REC.reval_currency);
2874 FND_MESSAGE.Set_Token ('CURR2',l_sob_currency);
2875 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
2876 l_ret_value := greatest(l_ret_value,2);
2877 Goto NEXT_Q7;
2878
2879 when GL_CURRENCY_API.NO_RATE then
2880 FND_MESSAGE.Set_Name ('XTR','XTR_2207');
2881 FND_MESSAGE.Set_Token ('CURR1',Q7_REC.reval_currency);
2882 FND_MESSAGE.Set_Token ('CURR2',l_sob_currency);
2883 FND_MESSAGE.Set_Token ('XCHG_DATE',to_char(Q7_REC.journal_date));
2884 FND_MESSAGE.Set_Token ('C_TYPE', nvl(l_pty_user_convert_type,l_pty_convert_type));
2885 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.get);
2886 l_ret_value := greatest(l_ret_value,2);
2887 Goto NEXT_Q7;
2888 END;
2889 BEGIN
2890 Insert into XTR_JOURNALS
2891 (batch_id,
2892 company_code,
2893 journal_date,
2894 orig_journal_date,
2895 deal_number,
2896 transaction_number,
2897 deal_type,
2898 deal_subtype,
2899 product_type,
2900 portfolio_code,
2901 debit_amount,
2902 credit_amount,
2903 code_combination_id,
2904 amount_type,
2905 created_by,
2906 created_on,
2907 currency,
2908 set_of_books_id,
2909 suspense_gl,
2910 accounted_dr,
2911 accounted_cr,
2912 date_type,
2913 action_code)
2914 Values
2915 (Q7_REC.batch_id,
2916 Q7_REC.company_code,
2917 nvl(Q7_REC.journal_date,to_date('01/01/1997','MM/DD/YYYY')),
2918 nvl(Q7_REC.journal_date,to_date('01/01/1997','MM/DD/YYYY')),
2919 Q7_REC.deal_number,
2920 nvl(Q7_REC.trans_number,999),
2921 Q7_REC.deal_type,
2922 Q7_REC.deal_subtype,
2923 Q7_REC.product_type,
2924 Q7_REC.portfolio_code,
2925 decode(Q7_REC.credit_or_debit,'DR',Q7_REC.amount,0),
2926 decode(Q7_REC.credit_or_debit,'CR',Q7_REC.amount,0),
2927 Q7_REC.ccid,
2928 Q7_REC.amount_type,
2929 G_user,
2930 trunc(SYSDATE),
2931 decode(Q7_REC.amount_type, 'CCYREAL', Q7_REC.deal_currency,
2932 Q7_REC.reval_currency),
2933 G_set_of_books_id,
2934 l_suspense_gl,
2935 l_accounted_dr,
2936 l_accounted_cr,
2937 Q7_REC.date_type,
2938 Q7_REC.action_code);
2939
2940 EXCEPTION
2941 when DUP_VAL_ON_INDEX then
2942 NULL;
2943 when OTHERS then
2944 FND_MESSAGE.Set_Name ('XTR','XTR_UNHANDLED_EXCEPTION');
2945 FND_MESSAGE.Set_Token ('PROCEDURE','GEN_JOURNALS');
2946 FND_MESSAGE.Set_Token ('EVENT','Inserting revaluation journal into table');
2947 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.get);
2948 l_ret_value := greatest(l_ret_value,2);
2949 END;
2950
2951 End If; -- If zero amounts.
2952
2953 <<NEXT_Q7>>
2954
2955 Fetch Q7 INTO Q7_REC;
2956 END LOOP;
2957 Close Q7;
2958
2959
2960 ---------------------------------------------------------------------------------------
2961 -- If entire batch was successful, insert XTR_BATCH_EVENTS to add one more row to
2962 -- indicate journal batch has been generated.
2963 ---------------------------------------------------------------------------------------
2964
2965
2966 --1336492 If (nvl(l_error_flag,'N') = 'N') then
2967
2968 If (nvl(l_ret_value,0) < 2) then
2969
2970 -- Bug 3805480 begin
2971 -- Added condition to check for RA batch w/o jrnl entries.
2972 -- In such an event, do not create records in xtr_batches and
2973 -- xtr_batch_events table for it.
2974
2975 If (nvl(in_source_option,'R') = 'J') then
2976 l_empty := 0;
2977 Open Entries (in_batch_id);
2978 Fetch Entries into l_empty;
2979 Close Entries;
2980 Else
2981 l_empty := 1;
2982 End If;
2983
2984 -- condition will always be true if RA batch.
2985 If (l_empty = 1) then
2986
2987 -- Bug 3805480 end.
2988
2989 Open EVENT_ID;
2990 Fetch EVENT_ID into l_event_id;
2991 Close EVENT_ID;
2992
2993 BEGIN
2994 Insert into XTR_BATCH_EVENTS
2995 (batch_event_id,
2996 batch_id,
2997 event_code,
2998 authorized,
2999 authorized_by,
3000 authorized_on,
3001 created_by,
3002 creation_date,
3003 last_updated_by,
3004 last_update_date,
3005 last_update_login)
3006 values (l_event_id,
3007 in_batch_id,
3008 'JRNLGN',
3009 'N',
3010 null,
3011 null,
3012 fnd_global.user_id,
3013 l_sysdate,
3014 fnd_global.user_id,
3015 l_sysdate,
3016 fnd_global.login_id);
3017 EXCEPTION
3018 when OTHERS then
3019 FND_MESSAGE.Set_Name ('XTR','XTR_UNHANDLED_EXCEPTION');
3020 FND_MESSAGE.Set_Token ('PROCEDURE','GEN_JOURNALS');
3021 FND_MESSAGE.Set_Token ('EVENT','Inserting batch event record into table');
3022 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.get);
3023 PRT_UNVAL_DEALS; -- added for 1336492.
3024 Rollback;
3025 return 2; -- modified for 1336492.
3026 END;
3027
3028 PRT_UNVAL_DEALS; -- added for 1336492.
3029
3030 -- Added for Bug 4634182
3031 If(nvl(in_source_option,'Y') = 'J') then
3032
3033 OPEN C_NRA_PERIOD_FROM;
3034 FETCH C_NRA_PERIOD_FROM into l_nra_period_from;
3035 CLOSE C_NRA_PERIOD_FROM;
3036
3037 If l_nra_period_from is not null then
3038
3039 Update xtr_batches
3040 set period_start = l_nra_period_from
3041 where batch_id = in_batch_id;
3042
3043 End if;
3044
3045 End if;
3046 -- End of Bug 4634182
3047
3048 Commit;
3049 Else
3050 PRT_UNVAL_DEALS; -- added for 1336492.
3051 Rollback;
3052 End If;
3053
3054 return l_ret_value; -- modified for 1336492.
3055 Else
3056 PRT_UNVAL_DEALS;
3057 Rollback;
3058 return l_ret_value;
3059 End If;
3060
3061 End GEN_JOURNALS;
3062
3063 --------------------------------------------------------------------------------------------------------------------------
3064 /* ---------------------------------------------------------------------
3065 | PUBLIC PROCEDURE |
3066 | Update_Journals |
3067 | |
3068 | DESCRIPTION |
3069 | Procedure to delete or reverse journal entries when deals are |
3070 | cancelled |
3071 | CALLED BY |
3072 | Various DB triggers in xtrtrigg.sql |
3073 | XTRINFXR.fmb |
3074 | PARAMETERS |
3075 | l_deal_nos deal number. (required) |
3076 | l_trans_nos transaction number. (required) |
3077 | l_deal_type deal type. (required) |
3078 | HISTORY |
3079 | |
3080 | NOTES |
3081 | Possible permetations of the JNL_REVERSAL_IND, |
3082 | TRANSFER_TO_EXTERNAL_GL, and CANCELLED_IN_GL fields in |
3083 | XTR_JOURNALS Table. |
3084 | |
3085 | Reversal Transfer to Cancelled |
3086 | Indic External G/L in G/L Description |
3087 | --------- ------------ --------- -------------------------------- |
3088 | NULL NULL NULL Journal was created but has not |
3089 | been posted to the G/L. |
3090 | C XX-XXX-XX Y A Transaction was cancelled, the |
3091 | journal has been transferred to |
3092 | the G/L and it has been reversed |
3093 | by creation of a reversal entry |
3094 | in XTR_JOURNALS. |
3095 R NULL NULL Reversal entry created as a |
3096 | result of a deal cancellation or |
3097 | closure. Ineligible for journal |
3098 | clearing. |
3099 | R XX-XXX-XX NULL Reveral entry that has been |
3100 | transferred to the G/L. |
3101 --------------------------------------------------------------------- */
3102
3103 PROCEDURE UPDATE_JOURNALS (l_deal_nos IN NUMBER,
3104 l_trans_nos IN NUMBER,
3105 l_deal_type IN VARCHAR2) is
3106 --
3107 CURSOR SEL_JNL is
3108 select company_code,
3109 journal_date,
3110 orig_journal_date,
3111 deal_number,
3112 transaction_number,
3113 deal_type,
3114 deal_subtype,
3115 amount_type,
3116 debit_amount,
3117 credit_amount,
3118 code_combination_id,
3119 comments,
3120 jnl_reversal_ind,
3121 cancelled_in_gl,
3122 created_by,
3123 created_on,
3124 updated_by,
3125 updated_on,
3126 product_type,
3127 portfolio_code,
3128 audit_indicator,
3129 currency,
3130 transfer_to_external_gl,
3131 rowid,
3132 suspense_gl,
3133 accounted_cr,
3134 accounted_dr,
3135 set_of_books_id
3136 from XTR_JOURNALS
3137 where deal_number=l_deal_nos
3138 and transaction_number=l_trans_nos
3139 and deal_type=l_deal_type;
3140 --
3141 CURSOR FIND_USER (fnd_user_id in number) is
3142 select dealer_code
3143 from xtr_dealer_codes_v
3144 where user_id = fnd_user_id;
3145 --
3146 fnd_user_id number;
3147 jnl_rec SEL_JNL%ROWTYPE;
3148 --
3149 comment_msg xtr_journals.comments%TYPE;
3150 --
3151 BEGIN
3152 -- Setup reversal comment message.
3153
3154 FND_MESSAGE.set_name ('XTR','XTR_2115');
3155 comment_msg := FND_MESSAGE.get;
3156
3157 -- Set the dealer code
3158
3159 fnd_user_id := FND_GLOBAL.USER_ID;
3160 Open FIND_USER(fnd_user_id);
3161 Fetch FIND_USER into G_user;
3162 Close FIND_USER;
3163
3164 -- Begin Processing.
3165
3166 Open SEL_JNL;
3167 Fetch SEL_JNL into jnl_rec;
3168 While (SEL_JNL%FOUND) LOOP
3169 If (jnl_rec.TRANSFER_TO_EXTERNAL_GL is null) then
3170 delete from XTR_journals
3171 where rowid=jnl_rec.rowid;
3172 Else
3173 -- Create reversing journal entry.
3174
3175 If (jnl_rec.DEBIT_AMOUNT > 0) then
3176 jnl_rec.CREDIT_AMOUNT := jnl_rec.DEBIT_AMOUNT;
3177 jnl_rec.DEBIT_AMOUNT := 0;
3178 jnl_rec.ACCOUNTED_CR := jnl_rec.ACCOUNTED_DR;
3179 jnl_rec.ACCOUNTED_DR := 0;
3180 Else
3181 jnl_rec.DEBIT_AMOUNT := jnl_rec.CREDIT_AMOUNT;
3182 jnl_rec.CREDIT_AMOUNT := 0;
3183 jnl_rec.ACCOUNTED_DR := jnl_rec.ACCOUNTED_DR;
3184 jnl_rec.ACCOUNTED_CR := 0;
3185 End If;
3186
3187 INSERT into XTR_JOURNALS
3188 (COMPANY_CODE,
3189 JOURNAL_DATE,
3190 ORIG_JOURNAL_DATE,
3191 DEAL_NUMBER,
3192 TRANSACTION_NUMBER,
3193 DEAL_TYPE,
3194 DEAL_SUBTYPE,
3195 AMOUNT_TYPE,
3196 DEBIT_AMOUNT,
3197 CREDIT_AMOUNT,
3198 CODE_COMBINATION_ID,
3199 COMMENTS,
3200 CREATED_BY,
3201 CREATED_ON,
3202 PRODUCT_TYPE,
3203 PORTFOLIO_CODE,
3204 CURRENCY,
3205 SET_OF_BOOKS_ID,
3206 SUSPENSE_GL,
3207 ACCOUNTED_CR,
3208 ACCOUNTED_DR,
3209 JNL_REVERSAL_IND)
3210 Values
3211 (jnl_rec.COMPANY_CODE,
3212 jnl_rec.JOURNAL_DATE,
3213 jnl_rec.ORIG_JOURNAL_DATE,
3214 jnl_rec.DEAL_NUMBER,
3215 jnl_rec.TRANSACTION_NUMBER,
3216 jnl_rec.DEAL_TYPE,
3217 jnl_rec.DEAL_SUBTYPE,
3218 jnl_rec.AMOUNT_TYPE,
3219 jnl_rec.DEBIT_AMOUNT,
3220 jnl_rec.CREDIT_AMOUNT,
3221 jnl_rec.CODE_COMBINATION_ID,
3222 comment_msg,
3223 G_user,
3224 SYSDATE,
3225 jnl_rec.PRODUCT_TYPE,
3226 jnl_rec.PORTFOLIO_CODE,
3227 jnl_rec.CURRENCY,
3228 jnl_rec.SET_OF_BOOKS_ID,
3229 jnl_rec.SUSPENSE_GL,
3230 jnl_rec.ACCOUNTED_CR,
3231 jnl_rec.ACCOUNTED_DR,
3232 'R');
3233
3234 If (SQL%FOUND) then
3235 UPDATE XTR_JOURNALS
3236 set JNL_REVERSAL_IND = 'C',
3237 CANCELLED_IN_GL = 'Y'
3238 where ROWID = jnl_rec.ROWID;
3239 End If;
3240 End If;
3241
3242 Fetch SEL_JNL into jnl_rec;
3243 End LOOP;
3244 Close SEL_JNL;
3245
3246 EXCEPTION
3247 WHEN OTHERS THEN
3248 RAISE;
3249 END UPDATE_JOURNALS;
3250
3251
3252
3253
3254
3255 /* -----------------------------------------------------------------------------
3256 | PUBLIC PROCEDURE |
3257 | Journals |
3258 | |
3259 | DESCRIPTION |
3260 | This procedure is the main entry into the journal process, which |
3261 | includes generation of revaluation/accrual related journals, |
3262 | non-revaluation/non-accrual related journals, and transfer of generated |
3263 | journal batches. |
3264 | CALLED BY |
3265 | SRS program executable XTRJRNAL and the journal form XTRACJNL. |
3266 | PARAMETERS |
3267 | errbuf standard error text (output, optional) |
3268 | retcode standard error code (output, optional) |
3269 | p_source_option non-RA or RA related (input, required) |
3270 | 'J' -- Non-Revaluation/Non-Accrual related. |
3271 | null - Revaluation/Accrual related. |
3272 | p_company_code company code. (input, optional) |
3273 | p_batch_id_from Batch ID range (input, optional) |
3274 | p_batch_id_to Batch ID range (input, optional) |
3275 | p_cutoff_date Batch cutoff date (input, optional) |
3276 | p_dummy_date dummy used for SRS validation (input) |
3277 | p_processing_option generate/xfer/genxfer (input, required) |
3278 | p_closed_periods handling of journals in closed periods during |
3279 | transfer. (input, optional) |
3280 | 'CLOSED' -- Uses closed period, no change. |
3281 | 'NXTOPEN' - Transfer with next GL open period start date. |
3282 | null ------ Uses company parameter setting. |
3283 | p_incl_transferred include xfer batches (input, optional) |
3284 | HISTORY |
3285 | 06/21/2002 eklau Created |
3286 ----------------------------------------------------------------------------- */
3287 PROCEDURE Journals
3288 (errbuf OUT NOCOPY VARCHAR2,
3289 retcode OUT NOCOPY NUMBER,
3290 p_source_option IN VARCHAR2,
3291 p_company_code IN VARCHAR2,
3292 p_batch_id_from IN NUMBER,
3293 p_batch_id_to IN NUMBER,
3294 p_cutoff_date IN VARCHAR2,
3295 p_dummy_date IN VARCHAR2,
3296 p_processing_option IN VARCHAR2,
3297 p_dummy_proc_opt IN VARCHAR2,
3298 p_closed_periods IN VARCHAR2,
3299 p_incl_transferred IN VARCHAR2,
3300 p_multiple_acct IN VARCHAR2 ) IS -- Modified Bug 4639287
3301
3302 --
3303 l_errbuf VARCHAR2(255) := NULL;
3304 l_retcode NUMBER := 0;
3305 l_sub_errbuf VARCHAR2(255) := NULL;
3306 l_sub_retcode NUMBER := 0;
3307 l_closed_param_code XTR_COMPANY_PARAMETERS.parameter_value_code%TYPE;
3308 l_company_code XTR_PARTY_INFO.party_code%TYPE;
3309 l_sob_id XTR_PARTY_INFO.set_of_books_id%TYPE;
3310 l_cutoff_date DATE := NULL;
3311 l_next_open_start DATE := NULL;
3312 l_next_bid NUMBER := NULL;
3313
3314 Cursor USER_COMPANIES is
3315 Select party_code, set_of_books_id
3316 from xtr_parties_v
3317 where party_type = 'C'
3318 and (party_code = nvl(p_company_code, party_code));
3319
3320 Cursor BATCHES_BY_BID_RANGE (l_company_code IN VARCHAR2) is
3321 Select batch_id
3322 from xtr_batches
3323 where batch_id between nvl(p_batch_id_from, batch_id) and nvl(p_batch_id_to, batch_id)
3324 and batch_id in (Select BA.batch_id
3325 from xtr_batches BA,
3326 xtr_batch_events BE
3327 where BA.batch_id = BE.batch_id
3328 and BE.event_code = 'JRNLGN'
3329 and BA.company_code = l_company_code
3330 and BA.upgrade_batch in ('I','N')
3331 and ((p_source_option = 'J' and BA.batch_type = 'J') or
3332 (p_source_option is null and BA.batch_type is null))
3333 and ((nvl(p_incl_transferred,'N') = 'N' and BA.gl_group_id is null) or
3334 (nvl(p_incl_transferred,'N') = 'Y')))
3335 order by period_end, batch_id asc;
3336
3337 Cursor BATCHES_BY_DATE (l_company_code IN VARCHAR2, l_cutoff_date IN DATE) is
3338 Select batch_id
3339 from xtr_batches
3340 where period_end <= nvl(l_cutoff_date, sysdate)
3341 and batch_id in (Select BA.batch_id
3342 from xtr_batches BA,
3343 xtr_batch_events BE
3344 where BA.batch_id = BE.batch_id
3345 and BE.event_code = 'JRNLGN'
3346 and BA.company_code = l_company_code
3347 and BA.upgrade_batch in ('I','N')
3348 and ((p_source_option = 'J' and BA.batch_type = 'J') or
3349 (p_source_option is null and BA.batch_type is null))
3350 and ((nvl(p_incl_transferred,'N') = 'N' and BA.gl_group_id is null) or
3351 (nvl(p_incl_transferred,'N') = 'Y')))
3352 order by period_end, batch_id asc;
3353
3354 -- Added below cursors for Bug 4639287
3355 cursor c_max_journal_date is
3356 select max(journal_date)
3357 from xtr_journals
3358 where batch_id = l_next_bid;
3359
3360 cursor c_min_journal_date is
3361 select min(journal_date)
3362 from xtr_journals
3363 where batch_id = l_next_bid;
3364
3365 cursor c_acctg_period(p_journal_date DATE) is
3366 select period_name
3367 from gl_periods per,
3368 gl_sets_of_books sob,
3369 xtr_parties_v pty
3370 where pty.party_code = l_company_code
3371 and sob.set_of_books_id = pty.set_of_books_id
3372 and sob.period_set_name = per.period_set_name
3373 and sob.accounted_period_type = per.period_type
3374 and p_journal_date between per.start_date and per.end_date
3375 and per.adjustment_period_flag = 'N';
3376
3377 l_max_journal_date DATE;
3378 l_min_journal_date DATE;
3379 l_min_period_name gl_periods.period_name%TYPE;
3380 l_max_period_name gl_periods.period_name%TYPE;
3381
3382
3383 BEGIN
3384
3385 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
3386 xtr_debug_pkg.debug ('>> PROCEDURE Journals.');
3387 END IF;
3388
3389 l_cutoff_date := FND_DATE.Canonical_To_Date(p_cutoff_date);
3390
3391 ---------------------- GENERATE JOURNAL BATCHES -----------------------------
3392
3393 If (p_processing_option in ('GENERATE','GENXFER')) then
3394
3395 -- Request issued to generate or generate and transfer journals.
3396
3397 If (p_source_option = 'J') then
3398
3399 -- Generate journals for Non-Revaluation/Non-Accrual related
3400 -- data occuring on or before the cutoff date.
3401
3402 If (p_cutoff_date is null) then
3403
3404 -- Invalid combination.
3405 -- Cutoff date not provided for a Non-Revaluation/Non-Accrual
3406 -- related journal generation request.
3407
3408 FND_MESSAGE.Set_Name ('XTR','XTR_NO_CUTOFF_DATE');
3409 FND_MESSAGE.Set_Token ('COMPANY_CODE', l_company_code);
3410 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
3411 l_retcode := greatest(l_retcode,2);
3412
3413 Elsif (p_batch_id_from is not null and p_batch_id_to is not null) then
3414
3415 -- Invalid combination for Non-Revaluation/Non-Accrual
3416 -- related journal generation.
3417 -- Cannot already have a specific batch id range for a
3418 -- journal only batch that is about to be created.
3419
3420 FND_MESSAGE.Set_Name ('XTR','XTR_INVALID_PARAMETER_COMBO');
3421 FND_MESSAGE.Set_Token ('BID_FROM', to_char(p_batch_id_from));
3422 FND_MESSAGE.Set_Token ('BID_TO', to_char(p_batch_id_to));
3423 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
3424 l_retcode := greatest(l_retcode,2);
3425
3426 Elsif (p_cutoff_date is not null) then
3427
3428 Open USER_COMPANIES;
3429 Fetch USER_COMPANIES into l_company_code, l_sob_id;
3430 While USER_COMPANIES%FOUND
3431 Loop
3432 l_sub_retcode := 0;
3433
3434 FND_MESSAGE.Set_Name ('XTR','XTR_START_GEN_JRNL_BY_DATE');
3435 FND_MESSAGE.Set_Token ('COMPANY_CODE', l_company_code);
3436 FND_MESSAGE.Set_Token ('CUTOFF_DATE', p_cutoff_date);
3437 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
3438
3439 Do_Journal_Process
3440 (l_sub_errbuf,
3441 l_sub_retcode,
3442 p_source_option,
3443 l_company_code,
3444 to_number(null),
3445 to_number(null),
3446 l_cutoff_date);
3447
3448 l_retcode := greatest(l_retcode,l_sub_retcode);
3449
3450 FND_MESSAGE.Set_Name ('XTR','XTR_END_GEN_JRNL_BY_DATE');
3451 FND_MESSAGE.Set_Token ('COMPANY_CODE', l_company_code);
3452 FND_MESSAGE.Set_Token ('CUTOFF_DATE', p_cutoff_date);
3453 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
3454
3455 Fetch USER_COMPANIES into l_company_code, l_sob_id;
3456 End Loop;
3457 Close USER_COMPANIES;
3458 End If; -- existence of cutoff date
3459
3460 Elsif (p_source_option is null) then
3461
3462 -- Generate journals for Revaluation/Accrual related data
3463 -- from authorized batch(es) between the given batch id range.
3464 -- If batch range not provided, will generate journals for all
3465 -- authorized accrual batches for the given company.
3466
3467 Open USER_COMPANIES;
3468 Fetch USER_COMPANIES into l_company_code, l_sob_id;
3469 While USER_COMPANIES%FOUND
3470 Loop
3471 l_sub_retcode := 0;
3472
3473 FND_MESSAGE.Set_Name ('XTR','XTR_START_GEN_JRNL_BY_BID');
3474 FND_MESSAGE.Set_Token ('COMPANY_CODE', l_company_code);
3475 FND_MESSAGE.Set_Token ('BID_FROM', to_char(p_batch_id_from));
3476 FND_MESSAGE.Set_Token ('BID_TO', to_char(p_batch_id_to));
3477 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
3478
3479 Do_Journal_Process
3480 (l_sub_errbuf,
3481 l_sub_retcode,
3482 p_source_option,
3483 l_company_code,
3484 p_batch_id_from,
3485 p_batch_id_to,
3486 to_date(null));
3487
3488 l_retcode := greatest(l_retcode,l_sub_retcode);
3489
3490 FND_MESSAGE.Set_Name ('XTR','XTR_END_GEN_JRNL_BY_BID');
3491 FND_MESSAGE.Set_Token ('COMPANY_CODE', l_company_code);
3492 FND_MESSAGE.Set_Token ('BID_FROM', to_char(p_batch_id_from));
3493 FND_MESSAGE.Set_Token ('BID_TO', to_char(p_batch_id_to));
3494 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
3495
3496 Fetch USER_COMPANIES into l_company_code, l_sob_id;
3497 End Loop;
3498 Close USER_COMPANIES;
3499 End If; -- source option.
3500 End If; -- processing option.
3501
3502
3503 -------------------------- TRANSFER JOURNAL BATCHES ---------------------------------
3504
3505
3506 If (p_processing_option in ('TRANSFER','GENXFER')) then
3507
3508 -- Transferring of the previously or just processed batch(es) has been requested.
3509
3510 Open USER_COMPANIES;
3511 Fetch USER_COMPANIES into l_company_code, l_sob_id;
3512 While USER_COMPANIES%FOUND
3513 Loop
3514 -- Start journal transfer process for company.
3515
3516 FND_MESSAGE.Set_Name ('XTR','XTR_START_XFER_JRNL');
3517 FND_MESSAGE.Set_Token ('COMPANY_CODE', l_company_code);
3518 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
3519
3520 -- If no specific closed period posting denoted, use per company parameter.
3521 -- Otherwise, use the user overridden value.
3522
3523 l_closed_param_code := null;
3524 If (p_closed_periods is null) then
3525 l_closed_param_code := GET_CLOSED_PERIOD_PARAM (l_company_code);
3526 Else
3527 l_closed_param_code := p_closed_periods;
3528 End If;
3529
3530
3531 -- Check for existence of a specific cutoff date first.
3532 -- If it exists, implies that Non-Reval/Non-Accrual journal
3533 -- batch(es) which have not been transferred have been
3534 -- requested for processing via the SRS program XTRJNLCA.
3535 --
3536 -- If cutoff date is not present, we will assume that a
3537 -- range of batch ids have been entered and one of two
3538 -- following scenerios has occurred:
3539 --
3540 -- 1. Processing of a specific batch has been requested
3541 -- via the Journals form. Batch can either be
3542 -- Non-Reval/Non-Accrual or Reval/Accrual related.
3543 -- In addition, it can be a previously transferred
3544 -- batch being re-transferred. The p_source_option will
3545 -- be expected to properly reflect the batch source
3546 -- when the submission is executed.
3547 -- 2. Processing of a specific batch range has been requested
3548 -- via the SRS program XTRJRNAL. Reval/Accrual related batch(es)
3549 -- that have not been transferred for the specified batch range
3550 -- have been requested for processing.
3551 --
3552 -- If neither a cutoff date nor a range of batch ids are provided,
3553 -- then the routine will process all journal batch(es) for the specified
3554 -- companies which meets the criteria as determined by the parameters
3555 -- p_source_option and p_incl_transferred.
3556
3557 If (l_cutoff_date is not null) then
3558 Open BATCHES_BY_DATE (l_company_code, l_cutoff_date);
3559 Else
3560 Open BATCHES_BY_BID_RANGE (l_company_code);
3561 End If;
3562
3563 Loop
3564 If (l_cutoff_date is not null) then
3565 Fetch BATCHES_BY_DATE into l_next_bid;
3566 Exit when BATCHES_BY_DATE%NOTFOUND;
3567 Else
3568 Fetch BATCHES_BY_BID_RANGE into l_next_bid;
3569 Exit when BATCHES_BY_BID_RANGE%NOTFOUND;
3570 End If;
3571
3572 l_sub_retcode := 0;
3573
3574 -- Added below code for Bug 4639287
3575 If (p_multiple_acct = 'DONTALLOW') then
3576 open c_max_journal_date;
3577 fetch c_max_journal_date into l_max_journal_date;
3578 close c_max_journal_date;
3579
3580 open c_min_journal_date;
3581 fetch c_min_journal_date into l_min_journal_date;
3582 close c_min_journal_date;
3583
3584 open c_acctg_period(l_min_journal_date);
3585 fetch c_acctg_period into l_min_period_name;
3586 close c_acctg_period;
3587
3588 open c_acctg_period(l_max_journal_date);
3589 fetch c_acctg_period into l_max_period_name;
3590 close c_acctg_period;
3591
3592 If (nvl(l_min_period_name,'$$$') <> nvl(l_max_period_name,'@@@')) then
3593 l_sub_retcode := 1;
3594 FND_MESSAGE.Set_Name ('XTR','XTR_START_XFER_JRNL_BATCH');
3595 FND_MESSAGE.Set_Token ('COMPANY_CODE', l_company_code);
3596 FND_MESSAGE.Set_Token ('BID', to_char(l_next_bid));
3597 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
3598 FND_MESSAGE.Set_Name ('XTR','XTR_CONC_DIFF_ACCTG');
3599 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
3600 FND_MESSAGE.Set_Name ('XTR','XTR_END_XFER_JRNL_BATCH');
3601 FND_MESSAGE.Set_Token ('COMPANY_CODE', l_company_code);
3602 FND_MESSAGE.Set_Token ('BID', to_char(l_next_bid));
3603 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
3604 l_retcode := greatest(l_retcode, l_sub_retcode);
3605 Else
3606
3607 XTR_ORACLE_FIN_INTERFACES_P.Transfer_Jnls
3608 (l_sub_errbuf,
3609 l_sub_retcode,
3610 l_company_code,
3611 l_next_bid,
3612 l_closed_param_code ); -- bug 4504734
3613
3614 l_retcode := greatest(l_retcode, l_sub_retcode);
3615 End If;
3616 Else
3617
3618 XTR_ORACLE_FIN_INTERFACES_P.Transfer_Jnls
3619 (l_sub_errbuf,
3620 l_sub_retcode,
3621 l_company_code,
3622 l_next_bid,
3623 l_closed_param_code); --bug 4504734
3624
3625 l_retcode := greatest(l_retcode, l_sub_retcode);
3626 End if; -- End Bug 4639287
3627
3628 End Loop;
3629
3630 If (l_cutoff_date is not null) then
3631 Close BATCHES_BY_DATE;
3632 Else
3633 Close BATCHES_BY_BID_RANGE;
3634 End If;
3635
3636 FND_MESSAGE.Set_Name ('XTR','XTR_END_XFER_JRNL');
3637 FND_MESSAGE.Set_Token ('COMPANY_CODE', l_company_code);
3638 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
3639
3640 Fetch USER_COMPANIES into l_company_code, l_sob_id;
3641 End Loop;
3642 End If;
3643
3644 If (l_retcode = 2) then
3645 retcode := -1;
3646 Else
3647 retcode := l_retcode;
3648 End If;
3649
3650 End JOURNALS;
3651
3652 -- Added override procedure Bug 4639287
3653 PROCEDURE JOURNALS
3654 (errbuf OUT NOCOPY VARCHAR2,
3655 retcode OUT NOCOPY NUMBER,
3656 p_source_option IN VARCHAR2,
3657 p_company_code IN VARCHAR2,
3658 p_batch_id_from IN NUMBER,
3659 p_batch_id_to IN NUMBER,
3660 p_cutoff_date IN VARCHAR2,
3661 p_dummy_date IN VARCHAR2,
3662 p_processing_option IN VARCHAR2,
3663 p_dummy_proc_opt IN VARCHAR2,
3664 p_closed_periods IN VARCHAR2,
3665 p_incl_transferred IN VARCHAR2) IS
3666
3667 BEGIN
3668
3669
3670 Journals(errbuf,
3671 retcode,
3672 p_source_option,
3673 p_company_code ,
3674 p_batch_id_from,
3675 p_batch_id_to,
3676 p_cutoff_date,
3677 p_dummy_date,
3678 p_processing_option,
3679 p_dummy_proc_opt,
3680 p_closed_periods,
3681 p_incl_transferred,
3682 'ALLOW');
3683
3684 END JOURNALS;
3685
3686 --------------------------------------------------------------------------------------------------------------------------
3687 end XTR_JOURNAL_PROCESS_P;