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