[Home] [Help]
PACKAGE BODY: APPS.XTR_STREAMLINE_P
Source
1 PACKAGE BODY XTR_STREAMLINE_P as
2 /* $Header: xtrstrmb.pls 120.7 2005/11/24 09:58:14 badiredd ship $ */
3 -------------------------------------------------------------------------------------------------
4
5
6 /*-------------------------------------------------------------------------*/
7 FUNCTION REVAL_DETAILS_INCOMPLETE (p_company IN VARCHAR2,
8 p_batch_start IN DATE,
9 p_batch_end IN DATE,
10 p_batch_id IN NUMBER) RETURN BOOLEAN AS
11 /*-------------------------------------------------------------------------*
12 * *
13 * To find if there are incomplete revaluation details in the batch. *
14 * *
15 *-------------------------------------------------------------------------*/
16
17 l_dummy VARCHAR2(1);
18 l_batch_id NUMBER;
19
20 cursor c_batch_id is
21 select batch_id
22 from xtr_batches
23 where period_start = p_batch_start
24 and period_end = p_batch_end
25 and company_code = p_company
26 and batch_type is null; -- 3527080 exclude NRA batch
27
28 cursor c_incomplete is
29 select 'Y'
30 from xtr_revaluation_details
31 where batch_id = l_batch_id
32 and company_code = p_company
33 and nvl(complete_flag,'N') = 'N';
34
35
36 BEGIN
37 --=================================== DEBUG ===============================================--
38 IF xtr_risk_debug_pkg.g_Debug THEN
39 xtr_risk_debug_pkg.dpush('REVAL_DETAILS_INCOMPLETE - In Parameters');
40 xtr_risk_debug_pkg.dlog('p_company ' , p_company);
41 xtr_risk_debug_pkg.dlog('p_batch_start ' , p_batch_start);
42 xtr_risk_debug_pkg.dlog('p_batch_end ' , p_batch_end);
43 xtr_risk_debug_pkg.dlog('p_batch_id ' , p_batch_id);
44 xtr_risk_debug_pkg.dpop('REVAL_DETAILS_INCOMPLETE - In Parameters');
45 END IF;
46 --==========================================================================================--
47
48 if p_batch_id is null then
49 open c_batch_id;
50 fetch c_batch_id into l_batch_id;
51 close c_batch_id;
52 else
53 l_batch_id := p_batch_id;
54 end if;
55
56 open c_incomplete;
57 fetch c_incomplete into l_dummy;
58 if c_incomplete%notfound then
59 close c_incomplete;
60 return FALSE;
61 end if;
62 close c_incomplete;
63 return TRUE;
64 END;
65
66
67 /*-------------------------------------------------------------------------*/
68 FUNCTION RETRO_DETAILS_INCOMPLETE (p_company IN VARCHAR2,
69 p_batch_start IN DATE,
70 p_batch_end IN DATE,
71 p_batch_id IN NUMBER) RETURN BOOLEAN AS
72 /*-------------------------------------------------------------------------*
73 * Bug 3378028 FAS *
74 * To find if there are incomplete retrospective details in the batch. *
75 * *
76 *-------------------------------------------------------------------------*/
77
78 l_dummy VARCHAR2(1);
79 l_batch_id NUMBER;
80
81 cursor c_batch_id is
82 select batch_id
83 from xtr_batches
84 where period_start = p_batch_start
85 and period_end = p_batch_end
86 and company_code = p_company
87 and batch_type is null;
88
89 cursor c_incomplete is
90 select 'Y'
91 from xtr_hedge_retro_tests
92 where batch_id = l_batch_id
93 and company_code = p_company
94 and nvl(complete_flag,'N') = 'N';
95
96 BEGIN
97 --=================================== DEBUG ===============================================--
98 IF xtr_risk_debug_pkg.g_Debug THEN
99 xtr_risk_debug_pkg.dpush('RETRO_DETAILS_INCOMPLETE - In Parameters');
100 xtr_risk_debug_pkg.dlog('p_company ' , p_company);
101 xtr_risk_debug_pkg.dlog('p_batch_start ' , p_batch_start);
102 xtr_risk_debug_pkg.dlog('p_batch_end ' , p_batch_end);
103 xtr_risk_debug_pkg.dlog('p_batch_id ' , p_batch_id);
104 xtr_risk_debug_pkg.dpop('RETRO_DETAILS_INCOMPLETE - In Parameters');
105 END IF;
106 --==========================================================================================--
107
108 if p_batch_id is null then
109 open c_batch_id;
110 fetch c_batch_id into l_batch_id;
111 close c_batch_id;
112 else
113 l_batch_id := p_batch_id;
114 end if;
115
116 open c_incomplete;
117 fetch c_incomplete into l_dummy;
118 if c_incomplete%notfound then
119 close c_incomplete;
120 return FALSE;
121 end if;
122 close c_incomplete;
123 return TRUE;
124 END;
125
126 /*---------------------------------------------------------------------*/
127 FUNCTION GET_EVENT_STATUS (p_company IN VARCHAR2,
128 p_batch_id IN NUMBER,
129 p_batch_BED IN DATE, -- Batch End Date
130 p_event IN VARCHAR2,
131 p_authorize IN VARCHAR2) RETURN BOOLEAN AS
132 /*---------------------------------------------------------------------*
133 | |
134 | To determine if a given p_event exists and/or authorised. |
135 | |
136 | p_authorize : 'Y' to check for authorise or NULL to check event |
137 | exists. |
138 | |
139 | Valid p_event: 'RATES', 'REVAL', 'ACCRUAL', 'JOURNAL', 'TRANSFER' |
140 | |
141 *---------------------------------------------------------------------*/
142
143 cursor find_rates is
144 select 'Y'
145 from xtr_revaluation_rates
146 where batch_id = nvl(p_batch_id, batch_id)
147 and company_code = p_company
148 and period_to = nvl(p_batch_BED, period_to);
149
150 cursor find_transfer is
151 select 'Y'
152 from xtr_batches
153 where batch_id = nvl(p_batch_id, batch_id)
154 and company_code = p_company
155 and period_end = nvl(p_batch_BED, period_end)
156 and gl_group_id is not null
157 and batch_type is null; -- 3527080 exclude NRA batch
158
159 cursor find_event is
160 select 'Y'
161 from xtr_batch_events a,
162 xtr_batches b
163 where a.batch_id = nvl(p_batch_id, a.batch_id)
164 and a.event_code = p_event
165 and a.authorized = nvl(p_authorize,a.authorized)
166 and b.batch_id = a.batch_id
167 and b.company_code = p_company
168 and b.period_end = nvl(p_batch_BED, b.period_end)
169 and b.batch_type is null; -- 3527080 exclude NRA batch
170
171
172 l_dummy VARCHAR2(1);
173
174 BEGIN
175 --=================================== DEBUG ===============================================--
176 IF xtr_risk_debug_pkg.g_Debug THEN
177 xtr_risk_debug_pkg.dpush('GET_EVENT_STATUS - In Parameters');
178 xtr_risk_debug_pkg.dlog('p_company ', p_company);
179 xtr_risk_debug_pkg.dlog('p_batch_id ', p_batch_id);
180 xtr_risk_debug_pkg.dlog('p_batch_BED ', p_batch_BED);
181 xtr_risk_debug_pkg.dlog('p_event ', p_event);
182 xtr_risk_debug_pkg.dlog('p_authorize ', p_authorize);
183 xtr_risk_debug_pkg.dpop('GET_EVENT_STATUS - In Parameters');
184 END IF;
185 --==========================================================================================--
186
187 if p_event = C_RATES then
188 open find_rates;
189 fetch find_rates into l_dummy;
190 if find_rates%notfound then
191 close find_rates;
192 return FALSE;
193 end if;
194 close find_rates;
195 ------------------------------------------
196 -- This is included but is not used.
197 ------------------------------------------
198 elsif p_event = C_TRANSFER then
199 open find_transfer;
200 fetch find_transfer into l_dummy;
201 if find_transfer%notfound then
202 close find_transfer;
203 return FALSE;
204 end if;
205 close find_transfer;
206 ------------------------------------------
207 else
208 open find_event;
209 fetch find_event into l_dummy;
210 if find_event%notfound then
211 close find_event;
212 return FALSE;
213 end if;
214 close find_event;
215 end if;
216 return TRUE;
217
218 END;
219
220
221 /*---------------------------------------------------------------------*/
222 FUNCTION EVENT_EXISTS (p_company IN VARCHAR2,
223 p_batch_id IN NUMBER,
224 p_batch_BED IN DATE,
225 p_event IN VARCHAR2) RETURN BOOLEAN AS
226 /*---------------------------------------------------------------------*
227 * *
228 * To check that event exists, authorise or unauthorised. *
229 * *
230 *---------------------------------------------------------------------*/
231 BEGIN
232 --=================================== DEBUG ===============================================--
233 IF xtr_risk_debug_pkg.g_Debug THEN
234 xtr_risk_debug_pkg.dpush('EVENT_EXISTS - In Parameters');
235 xtr_risk_debug_pkg.dlog('p_company ', p_company);
236 xtr_risk_debug_pkg.dlog('p_batch_id ', p_batch_id);
237 xtr_risk_debug_pkg.dlog('p_batch_BED ', p_batch_BED);
238 xtr_risk_debug_pkg.dlog('p_event ', p_event);
239 xtr_risk_debug_pkg.dpop('EVENT_EXISTS - In Parameters');
240 END IF;
241 --==========================================================================================--
242
243 return GET_EVENT_STATUS (p_company, p_batch_id, p_batch_BED, p_event, NULL);
244
245 END;
246
247
248 /*---------------------------------------------------------------------*/
249 FUNCTION EVENT_AUTHORIZED (p_company IN VARCHAR2,
250 p_batch_id IN NUMBER,
251 p_event IN VARCHAR2) RETURN BOOLEAN AS
252 /*---------------------------------------------------------------------*
253 * *
254 * To check that event exists and authorised. *
255 * *
256 *---------------------------------------------------------------------*/
257 BEGIN
258 --=================================== DEBUG ===============================================--
259 IF xtr_risk_debug_pkg.g_Debug THEN
260 xtr_risk_debug_pkg.dpush('EVENT_AUTHORIZED - In Parameters');
261 xtr_risk_debug_pkg.dlog('p_company ', p_company);
262 xtr_risk_debug_pkg.dlog('p_batch_id ', p_batch_id);
263 xtr_risk_debug_pkg.dlog('p_event ', p_event);
264 xtr_risk_debug_pkg.dpop('EVENT_AUTHORIZED - In Parameters');
265 END IF;
266 --==========================================================================================--
267
268 return GET_EVENT_STATUS (p_company, p_batch_id, NULL, p_event, 'Y');
269
270 END;
271
272
273 /*---------------------------------------------------------------------*/
274 FUNCTION GET_PARTY_CREATED_ON (p_company IN VARCHAR2) RETURN DATE AS
275 /*---------------------------------------------------------------------*
276 * *
277 * To get creation date of company. Same logic as form. *
278 * *
279 *---------------------------------------------------------------------*/
280 cursor party_created IS
281 select created_on
282 from xtr_party_info
283 where party_code = p_company;
284
285 l_date DATE;
286
287 BEGIN
288
289 open PARTY_CREATED;
290 fetch PARTY_CREATED into l_date;
291 close PARTY_CREATED;
292
293 --=================================== DEBUG ===============================================--
294 IF xtr_risk_debug_pkg.g_Debug THEN
295 xtr_risk_debug_pkg.dpush('GET_PARTY_CREATED_ON');
296 xtr_risk_debug_pkg.dlog('p_company ', p_company);
297 xtr_risk_debug_pkg.dlog('l_date ', l_date);
298 xtr_risk_debug_pkg.dpop('GET_PARTY_CREATED_ON');
299 END IF;
300 --==========================================================================================--
301
302 return l_date;
303
304 END;
305
306 /*---------------------------------------------------------------------*/
307 FUNCTION LOCK_BATCH (p_batch_id IN NUMBER,
308 p_company IN VARCHAR2,
309 p_no_data_error IN VARCHAR2,
310 p_locking_error IN VARCHAR2) RETURN NUMBER AS
311 /*---------------------------------------------------------------------*
312 * *
313 * Locks the entire batch. *
314 * *
315 *---------------------------------------------------------------------*/
316 l_rowid ROWID;
317
318 BEGIN
319 --=================================== DEBUG ===============================================--
320 IF xtr_risk_debug_pkg.g_Debug THEN
321 xtr_risk_debug_pkg.dpush('LOCK_BATCH - In Parameters');
322 xtr_risk_debug_pkg.dlog('p_batch_id ' , p_batch_id);
323 xtr_risk_debug_pkg.dlog('p_company ' , p_company);
324 xtr_risk_debug_pkg.dlog('p_no_data_error ' , p_no_data_error);
325 xtr_risk_debug_pkg.dlog('p_locking_error ' , p_locking_error);
326 xtr_risk_debug_pkg.dpop('LOCK_BATCH - In Parameters');
327 END IF;
328 --==========================================================================================--
329
330 select rowid
331 into l_rowid
332 from xtr_batches
333 where batch_id = p_batch_id
334 and company_code = p_company
335 and batch_type is null -- 3527080 exclude NRA batch
336 for update of last_update_date nowait;
337
338 return(0);
339
340 EXCEPTION
341 when no_data_found then
342 rollback;
343 FND_MESSAGE.Set_Name('XTR', p_no_data_error);
344 FND_MESSAGE.Set_Token('BATCH', p_batch_id);
345 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
346 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
347 --=================================== DEBUG ===============================================--
348 IF xtr_risk_debug_pkg.g_Debug THEN
349 xtr_risk_debug_pkg.dpush('LOCK_BATCH - error');
350 xtr_risk_debug_pkg.dlog('Exception Error ' , 'NO_DATA_FOUND');
351 xtr_risk_debug_pkg.dpop('LOCK_BATCH - error');
352 END IF;
353 --==========================================================================================--
354 return(2);
355 when e_record_locked then
356 rollback;
357 FND_MESSAGE.Set_Name('XTR', p_locking_error);
358 FND_MESSAGE.Set_Token('BATCH', p_batch_id);
359 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
360 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
361 --=================================== DEBUG ===============================================--
362 IF xtr_risk_debug_pkg.g_Debug THEN
363 xtr_risk_debug_pkg.dpush('LOCK_BATCH - error');
364 xtr_risk_debug_pkg.dlog('Exception Error ' , 'E_RECORD_LOCKED');
365 xtr_risk_debug_pkg.dpop('LOCK_BATCH - error');
366 END IF;
367 --==========================================================================================--
368 return(2);
369 when others then
370 rollback;
371 FND_MESSAGE.Set_Name('XTR', C_BATCH_ERROR);
372 FND_MESSAGE.Set_Token('BATCH', p_batch_id);
373 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
374 FND_FILE.Put_line (FND_FILE.LOG, sqlcode||'- '||sqlerrm(sqlcode));
375 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
376 --=================================== DEBUG ===============================================--
377 IF xtr_risk_debug_pkg.g_Debug THEN
378 xtr_risk_debug_pkg.dpush('LOCK_BATCH - error');
379 xtr_risk_debug_pkg.dlog('Exception Error ' , 'OTHERS');
380 xtr_risk_debug_pkg.dpop('LOCK_BATCH - error');
381 END IF;
382 --==========================================================================================--
383 return(2);
384 END LOCK_BATCH;
385
386
387 /*---------------------------------------------------------------------*/
388 FUNCTION LOCK_EVENT (p_batch_id IN NUMBER,
389 p_event IN VARCHAR2,
390 p_authorized IN VARCHAR2,
391 p_no_data_error IN VARCHAR2,
392 p_locking_error IN VARCHAR2) RETURN NUMBER AS
393 /*---------------------------------------------------------------------*
394 * *
395 * p_authorized - leave NULL if you are not concerned whether or *
396 * not event is authorised. *
397 * *
398 * p_no_data_error - error code for NO_DATA_FOUND *
399 * *
400 * p_locking_error - error code for record locked *
401 * *
402 *---------------------------------------------------------------------*/
403 l_rowid ROWID;
404
405 BEGIN
406 --=================================== DEBUG ===============================================--
407 IF xtr_risk_debug_pkg.g_Debug THEN
408 xtr_risk_debug_pkg.dpush('LOCK_EVENT - In Parameters');
409 xtr_risk_debug_pkg.dlog('p_batch_id ' , p_batch_id);
410 xtr_risk_debug_pkg.dlog('p_event ' , p_event);
411 xtr_risk_debug_pkg.dlog('p_authorized ' , p_authorized);
412 xtr_risk_debug_pkg.dlog('p_no_data_error ' , p_no_data_error);
413 xtr_risk_debug_pkg.dlog('p_locking_error ' , p_locking_error);
414 xtr_risk_debug_pkg.dpop('LOCK_EVENT - In Parameters');
415 END IF;
416 --==========================================================================================--
417
418 select rowid
419 into l_rowid
420 from xtr_batch_events a
421 where batch_id = p_batch_id
422 and event_code = p_event
423 and authorized = nvl(p_authorized,authorized)
424 for update of authorized nowait;
425
426 return(0);
427
428 EXCEPTION
429 when no_data_found then
430 rollback;
431 FND_MESSAGE.Set_Name('XTR', p_no_data_error);
432 FND_MESSAGE.Set_Token('BATCH', p_batch_id);
433 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
434 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
435 --=================================== DEBUG ===============================================--
436 IF xtr_risk_debug_pkg.g_Debug THEN
437 xtr_risk_debug_pkg.dpush('LOCK_EVENT - error');
438 xtr_risk_debug_pkg.dlog('Exception Error ' , 'NO_DATA_FOUND');
439 xtr_risk_debug_pkg.dpop('LOCK_EVENT - error');
440 END IF;
441 --==========================================================================================--
442 return(2);
443 when e_record_locked then
444 rollback;
445 FND_MESSAGE.Set_Name('XTR', p_locking_error);
446 FND_MESSAGE.Set_Token('BATCH', p_batch_id);
447 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
448 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
449 --=================================== DEBUG ===============================================--
450 IF xtr_risk_debug_pkg.g_Debug THEN
451 xtr_risk_debug_pkg.dpush('LOCK_EVENT - error');
452 xtr_risk_debug_pkg.dlog('Exception Error ' , 'E_RECORD_LOCKED');
453 xtr_risk_debug_pkg.dpop('LOCK_EVENT - error');
454 END IF;
455 --==========================================================================================--
456 return(2);
457 when others then
458 rollback;
459 FND_MESSAGE.Set_Name('XTR', C_BATCH_ERROR);
460 FND_MESSAGE.Set_Token('BATCH', p_batch_id);
461 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
462 FND_FILE.Put_line (FND_FILE.LOG, sqlcode||'- '||sqlerrm(sqlcode));
463 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
464 --=================================== DEBUG ===============================================--
465 IF xtr_risk_debug_pkg.g_Debug THEN
466 xtr_risk_debug_pkg.dpush('LOCK_EVENT - error');
467 xtr_risk_debug_pkg.dlog('Exception Error ' , 'OTHERS');
468 xtr_risk_debug_pkg.dpop('LOCK_EVENT - error');
469 END IF;
470 --==========================================================================================--
471 return(2);
472 END LOCK_EVENT;
473
474
475 /*------------------------------------------------------------------------------*/
476 FUNCTION CHK_ELIGIBLE_COMPANY (p_company IN VARCHAR2,
477 p_cutoff_date IN DATE,
478 p_do_reval IN VARCHAR2,
479 p_do_retro IN VARCHAR2, -- 3378028 FAS
480 p_start_process IN VARCHAR2,
481 p_end_process IN VARCHAR2) RETURN NUMBER AS
482 /*------------------------------------------------------------------------------*
483 * *
484 * To check if company has incomplete reval details, incomplete previous *
485 * batches, incomplete inaugural batch, etc. *
486 * *
487 *------------------------------------------------------------------------------*/
488
489 cursor INCOMPLETE_TRANSFER_BATCH is
490 select batch_id,
491 period_start,
492 period_end
493 from xtr_batches a
494 where company_code = p_company
495 and period_end <= p_cutoff_date
496 and nvl(upgrade_batch,'N') = 'N'
497 and batch_type is null
498 and gl_group_id is null
499 order by period_end, period_start;
500
501 cursor INCOMPLETE_JOURNAL_BATCH is
502 select batch_id,
503 period_start,
504 period_end
505 from xtr_batches a
506 where company_code = p_company
507 and period_end <= p_cutoff_date
508 and nvl(upgrade_batch,'N') = 'N'
509 and batch_type is null
510 and not exists (select 1
511 from XTR_BATCH_EVENTS b
512 where b.batch_id = a.batch_id
513 and event_code = C_JOURNAL)
514 order by period_end, period_start;
515
516 cursor INCOMPLETE_ACCRUAL_BATCH is
517 select batch_id,
518 period_start,
519 period_end
520 from xtr_batches a
521 where company_code = p_company
522 and period_end <= p_cutoff_date
523 and nvl(upgrade_batch,'N') = 'N'
524 and batch_type is null
525 and not exists (select 1
526 from XTR_BATCH_EVENTS b
527 where b.batch_id = a.batch_id
528 and event_code = C_ACCRUAL
529 and nvl(authorized,'N') = 'Y')
530 order by period_end, period_start;
531
532 ------------------------------------------------------------------------------------------------
533 -- 3378028 FAS
534 -- ===========
535 -- An incomplete RETRO batch is one without authorised RETROET event and without ACCRUAL event.
536 ------------------------------------------------------------------------------------------------
537 cursor INCOMPLETE_RETRO_BATCH is
538 select batch_id,
539 period_start,
540 period_end
541 from xtr_batches a
542 where company_code = p_company
543 and period_end <= p_cutoff_date
544 and nvl(upgrade_batch,'N') = 'N'
545 and batch_type is null
546 and not exists (select 1
547 from XTR_BATCH_EVENTS b
548 where b.batch_id = a.batch_id
549 and event_code = C_RETROET
550 and nvl(authorized,'N') = 'Y')
551 and not exists (select 1
552 from XTR_BATCH_EVENTS c
553 where c.batch_id = a.batch_id
554 and event_code = C_ACCRUAL) -- Intended for company that skips RETROSPECTIVE TEST
555 -- Need not check for AUTHORIZED flag in this case.
556 order by period_end, period_start;
557
558
559 cursor INCOMPLETE_REVAL_BATCH is
560 select batch_id,
561 period_start,
562 period_end
563 from xtr_batches a
564 where company_code = p_company
565 and period_end <= p_cutoff_date
566 and nvl(upgrade_batch,'N') = 'N'
567 and batch_type is null
568 and not exists (select 1
569 from XTR_BATCH_EVENTS b
570 where b.batch_id = a.batch_id
571 and event_code = C_REVAL
572 and nvl(authorized,'N') = 'Y')
573 order by period_end, period_start;
574
575 cursor UPGRADE_REQUIRED is
576 SELECT 'Y'
577 FROM xtr_batches b,
578 xtr_revaluation_details a
579 WHERE b.company_code = p_company
580 AND b.batch_id = a.batch_id
581 AND NVL(b.upgrade_batch,'N') = 'Y'
582 AND b.batch_type is null -- 3527080 exclude NRA batch
583 UNION
584 SELECT 'Y'
585 FROM xtr_batches b,
586 xtr_accrls_amort a
587 WHERE b.company_code = p_company
588 AND b.batch_id = a.batch_id
589 AND NVL(b.upgrade_batch,'N') = 'Y'
590 AND b.batch_type is null; -- 3527080 exclude NRA batch
591
592
593 l_retcode NUMBER := 0;
594 l_batch_id NUMBER := null;
595 l_batch_start DATE := to_date(null);
596 l_batch_end DATE := to_date(null);
597
598 l_latest_BID NUMBER := null;
599 l_dummy_BSD DATE := to_date(null);
600 l_latest_BED DATE := to_date(null);
601 l_group_id NUMBER := null;
602 l_latest_upgrade VARCHAR2(1) := null;
603 l_upgrade_required VARCHAR2(1) := null;
604
605 FUNCTION LOG_ERROR (p_err_code IN VARCHAR2, p_batch_id IN NUMBER, p_batch_BED IN DATE) RETURN NUMBER AS
606 BEGIN
607 --=================================== DEBUG ===============================================--
608 IF xtr_risk_debug_pkg.g_Debug THEN
609 xtr_risk_debug_pkg.dpush('CHK_ELIGIBLE_COMPANY - LOG_ERROR');
610 xtr_risk_debug_pkg.dlog('p_err_code ' , p_err_code);
611 xtr_risk_debug_pkg.dlog('p_batch_id ' , p_batch_id);
612 xtr_risk_debug_pkg.dlog('p_batch_BED ' , p_batch_BED);
613 xtr_risk_debug_pkg.dpop('CHK_ELIGIBLE_COMPANY - LOG_ERROR');
614 END IF;
615 --==========================================================================================--
616
617 FND_MESSAGE.Set_Name('XTR', p_err_code);
618 if p_err_code in (C_NO_REVAL_DATA, C_NO_ACCRUAL_DATA, C_NO_JOURNAL_DATA, C_INCOMPLETE_REVAL,
619 C_NO_RETROET_DATA, C_INCOMPLETE_RETROET ) then -- FAS 3378028 errors
620 FND_MESSAGE.Set_Token('BATCH', p_batch_id);
621 elsif p_err_code in (C_MISSING_REVAL, C_MISSING_ACCRUAL, C_MISSING_JOURNAL,
622 C_MISSING_RETROET) then -- FAS 3378028 errors
623 FND_MESSAGE.Set_Token('BED', p_batch_BED);
624 end if;
625 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
626 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
627 return(2);
628
629 END;
630
631 BEGIN
632 --=================================== DEBUG ===============================================--
633 IF xtr_risk_debug_pkg.g_Debug THEN
634 xtr_risk_debug_pkg.dpush('CHK_ELIGIBLE_COMPANY - In Parameters');
635 xtr_risk_debug_pkg.dlog('p_company ' , p_company);
636 xtr_risk_debug_pkg.dlog('p_cutoff_date ' , p_cutoff_date);
637 xtr_risk_debug_pkg.dlog('p_do_reval ' , p_do_reval);
638 xtr_risk_debug_pkg.dlog('p_do_retro ' , p_do_retro);
639 xtr_risk_debug_pkg.dlog('p_start_process ' , p_start_process);
640 xtr_risk_debug_pkg.dlog('p_end_process ' , p_end_process);
641 xtr_risk_debug_pkg.dpop('CHK_ELIGIBLE_COMPANY - In Parameters');
642 END IF;
643 --==========================================================================================--
644
645 if p_end_process = C_PROCESS_REVAL and p_do_reval = 'Y' then
646 open INCOMPLETE_REVAL_BATCH;
647 fetch INCOMPLETE_REVAL_BATCH into l_batch_id, l_batch_start, l_batch_end;
648 close INCOMPLETE_REVAL_BATCH;
649 --elsif p_end_process = C_PROCESS_RETROET and p_do_retro = 'Y' and p_do_reval = 'Y' then
650 elsif p_end_process = C_PROCESS_RETROET and p_do_reval = 'Y' then -- 3378028 FAS
651 open INCOMPLETE_RETRO_BATCH;
652 fetch INCOMPLETE_RETRO_BATCH into l_batch_id, l_batch_start, l_batch_end;
653 close INCOMPLETE_RETRO_BATCH;
654 elsif p_end_process = C_PROCESS_ACCRUAL then
655 open INCOMPLETE_ACCRUAL_BATCH;
656 fetch INCOMPLETE_ACCRUAL_BATCH into l_batch_id, l_batch_start, l_batch_end;
657 close INCOMPLETE_ACCRUAL_BATCH;
658 elsif p_end_process = C_PROCESS_JOURNAL then
659 open INCOMPLETE_JOURNAL_BATCH;
660 fetch INCOMPLETE_JOURNAL_BATCH into l_batch_id, l_batch_start, l_batch_end;
661 close INCOMPLETE_JOURNAL_BATCH;
662 elsif p_end_process = C_PROCESS_TRANSFER then
663 open INCOMPLETE_TRANSFER_BATCH;
664 fetch INCOMPLETE_TRANSFER_BATCH into l_batch_id, l_batch_start, l_batch_end;
665 close INCOMPLETE_TRANSFER_BATCH;
666 end if;
667
668 --=================================== DEBUG ===============================================--
669 IF xtr_risk_debug_pkg.g_Debug THEN
670 xtr_risk_debug_pkg.dpush('CHK_ELIGIBLE_COMPANY - check incomplete batch');
671 xtr_risk_debug_pkg.dlog('l_batch_id ' , l_batch_id);
672 xtr_risk_debug_pkg.dlog('l_batch_start ' , l_batch_start);
673 xtr_risk_debug_pkg.dlog('l_batch_end ' , l_batch_end);
674 xtr_risk_debug_pkg.dpop('CHK_ELIGIBLE_COMPANY - check incomplete batch');
675 END IF;
676 --==========================================================================================--
677
678 if l_batch_id is not null then
679
680 l_retcode := 1; -- Batch is incomplete, but need to check other validations.
681
682 if l_batch_end > p_cutoff_date then
683
684 l_retcode := LOG_ERROR(C_COMPLETED_BATCH,null,null); -- Batch End Date is completed.
685
686 else
687
688 --------------------------------------------------------------------------------------------------------
689 -- Batch eligible - 3378028 FAS Begin
690 --------------------------------------------------------------------------------------------------------
691 if p_start_process = C_PROCESS_REVAL and p_do_reval = 'Y' then
692
693 if EVENT_EXISTS(p_company, l_batch_id,NULL, C_REVAL) then
694
695 if REVAL_DETAILS_INCOMPLETE (p_company, l_batch_start, l_batch_end, l_batch_id) then
696 l_retcode := LOG_ERROR(C_INCOMPLETE_REVAL, l_batch_id, null);
697 else
698 --=============================================================================================
699 -- 3378028 FAS
700 -- For companies that does not do RETRO and does not have incomplete REVAL event.
701 -- Should end process with error here so child process is not submitted.
702 --=============================================================================================
703 if p_do_retro = 'N' and p_end_process = C_PROCESS_RETROET then
704 if l_batch_end = p_cutoff_date and EVENT_AUTHORIZED (p_company, l_batch_id, C_REVAL) then
705 l_retcode := LOG_ERROR(C_COMPLETED_BATCH, null, null);
706 l_retcode := LOG_ERROR(C_COMPANY_SKIP_RETROET, l_batch_id, null);
707 end if;
708 end if;
709 --=============================================================================================
710 end if;
711 end if;
712
713 elsif p_start_process = C_PROCESS_RETROET and p_do_retro = 'Y' then
714
715 if EVENT_EXISTS(p_company, l_batch_id,NULL, C_RETROET) then
716
717 if RETRO_DETAILS_INCOMPLETE (p_company, l_batch_start, l_batch_end, l_batch_id) then
718 l_retcode := LOG_ERROR(C_INCOMPLETE_RETROET, l_batch_id, null);
719 end if;
720
721 elsif not EVENT_AUTHORIZED (p_company, l_batch_id, C_REVAL) then
722 l_retcode := LOG_ERROR(C_NO_REVAL_DATA, l_batch_id, null);
723
724 end if;
725
726
727 elsif p_start_process = C_PROCESS_ACCRUAL and p_do_reval = 'Y' then
728 if p_do_retro = 'Y' then
729 if not EVENT_AUTHORIZED (p_company, l_batch_id, C_RETROET) then
730 l_retcode := LOG_ERROR(C_NO_RETROET_DATA, l_batch_id, null);
731 end if;
732 else
733 if not EVENT_AUTHORIZED (p_company, l_batch_id, C_REVAL) then
734 l_retcode := LOG_ERROR(C_NO_REVAL_DATA, l_batch_id, null);
735 end if;
736 end if;
737 --------------------------------------------------------------------------------------------------------
738 -- 3378028 FAS End
739 --------------------------------------------------------------------------------------------------------
740
741 elsif p_start_process = C_PROCESS_JOURNAL then
742 if not EVENT_AUTHORIZED (p_company, l_batch_id,C_ACCRUAL) then
743 l_retcode := LOG_ERROR(C_NO_ACCRUAL_DATA, l_batch_id,null);
744 end if;
745
746 elsif p_start_process = C_PROCESS_TRANSFER then
747 if not EVENT_EXISTS (p_company, l_batch_id,NULL,C_JOURNAL) then
748 l_retcode := LOG_ERROR(C_NO_JOURNAL_DATA, l_batch_id,null);
749 end if;
750 end if;
751
752 end if; -- l_batch_end > p_cutoff_date
753
754 else -- l_batch_id is null (no incomplete batches)
755
756 GET_LATEST_BATCH (p_company, l_latest_BID, l_dummy_BSD, l_latest_BED,
757 l_group_id, l_latest_upgrade);
758
759 if l_latest_upgrade = 'Y' then
760 l_upgrade_required := 'N';
761 open UPGRADE_REQUIRED;
762 fetch UPGRADE_REQUIRED into l_upgrade_required;
763 close UPGRADE_REQUIRED;
764 if l_upgrade_required = 'Y' then
765 l_retcode := LOG_ERROR(C_INAUGURAL_MISSING,null,null);
766 end if;
767 else
768 if l_latest_BED >= p_cutoff_date then
769 l_retcode := LOG_ERROR(C_COMPLETED_BATCH,null,null);
770 end if;
771 if l_latest_upgrade = 'I' and l_group_id is null then
772 l_retcode := LOG_ERROR(C_INAUGURAL_TRANSFER,null,null);
773 end if;
774 end if;
775
776
777 if l_retcode = 0 then
778 -------------------------------------------------------
779 -- 3378028 FAS
780 --
781 -- The following section is rewritten for FAS.
782 ------------------------------------------------------
783 --
784 -- Check that start process is a valid one.
785 -- For example, the last event was Journal and it finished on 1/15/04. Now user select to
786 -- Start from Accrual to Journal with new Cutoff date of 2/1/04. Company starts from revaluation.
787 -- In this case, program should error out because user need to start from Reval with the new Cutoff Date.
788 --
789 /***********************************************/
790 /* Start from RETRO */
791 /***********************************************/
792 if p_start_process = C_PROCESS_RETROET and p_do_retro = 'Y' then
793 if not EVENT_EXISTS (p_company, null, p_cutoff_date, C_REVAL) then
794 l_retcode := LOG_ERROR (C_MISSING_REVAL, null, p_cutoff_date);
795 end if;
796
797 /***********************************************/
798 /* Start from ACCRUAL */
799 /***********************************************/
800 elsif p_start_process = C_PROCESS_ACCRUAL and p_do_reval = 'Y' then
801 if EVENT_EXISTS (p_company, null, p_cutoff_date, C_REVAL) then
802 if p_do_retro = 'Y' and not EVENT_EXISTS (p_company, null, p_cutoff_date, C_RETROET) then
803 l_retcode := LOG_ERROR (C_MISSING_RETROET, null, p_cutoff_date);
804 end if;
805 else
806 l_retcode := LOG_ERROR (C_MISSING_REVAL,null, p_cutoff_date);
807 end if;
808
809 /***********************************************/
810 /* Start from JOURNAL */
811 /***********************************************/
812 elsif p_start_process = C_PROCESS_JOURNAL then
813 if p_do_reval = 'N' or (p_do_reval = 'Y' and EVENT_EXISTS (p_company, null, p_cutoff_date, C_REVAL)) then
814 if p_do_retro = 'N' or (p_do_retro = 'Y' and EVENT_EXISTS (p_company, null, p_cutoff_date, C_RETROET)) then
815 if not EVENT_EXISTS (p_company, null, p_cutoff_date, C_ACCRUAL) then
816 l_retcode := LOG_ERROR (C_MISSING_ACCRUAL, null, p_cutoff_date);
817 end if;
818 else
819 l_retcode := LOG_ERROR (C_MISSING_RETROET, null, p_cutoff_date);
820 end if;
821 else
822 l_retcode := LOG_ERROR (C_MISSING_REVAL, null, p_cutoff_date);
823 end if;
824
825 /***********************************************/
826 /* Start from TRANSFER */
827 /***********************************************/
828 elsif p_start_process = C_PROCESS_TRANSFER then
829 if p_do_reval = 'N' or (p_do_reval = 'Y' and EVENT_EXISTS (p_company, null, p_cutoff_date, C_REVAL)) then
830 if p_do_retro = 'N' or (p_do_retro = 'Y' and EVENT_EXISTS (p_company, null, p_cutoff_date, C_RETROET)) then
831 if EVENT_EXISTS(p_company, null, p_cutoff_date, C_ACCRUAL) then
832 if not EVENT_EXISTS (p_company, null, p_cutoff_date, C_JOURNAL) then
833 l_retcode:= LOG_ERROR (C_MISSING_JOURNAL, null, p_cutoff_date);
834 end if;
835 else
836 l_retcode := LOG_ERROR (C_MISSING_ACCRUAL, null, p_cutoff_date);
837 end if;
838 else
839 l_retcode := LOG_ERROR (C_MISSING_RETROET, null, p_cutoff_date);
840 end if;
841 else
842 l_retcode := LOG_ERROR (C_MISSING_REVAL, null, p_cutoff_date);
843 end if;
844 end if;
845
846 end if; -- l_retcode = 0
847
848 end if; -- l_batch_id is not null
849
850 return(l_retcode);
851
852 END CHK_ELIGIBLE_COMPANY;
853
854
855 /*---------------------------------------------------------------------*/
856 PROCEDURE GET_PREV_NORMAL_BATCH (p_company IN VARCHAR2,
857 p_curr_BED IN DATE,
858 p_prev_BID OUT NOCOPY NUMBER,
859 p_prev_BED OUT NOCOPY DATE) AS
860 /*---------------------------------------------------------------------*
861 | |
862 | Finds any previous normal batch information for locking purpose |
863 | in later processes. |
864 | Only Reval/Accrual journals will be included. |
865 | |
866 *---------------------------------------------------------------------*/
867
868 cursor prev_normal is
869 select batch_id,
870 period_end
871 from xtr_batches
872 where company_code = p_company
873 and period_end < p_curr_BED
874 and nvl(upgrade_batch,'N') = 'N'
875 and batch_type is null -- RA batch only
876 order by period_end desc, period_start desc, batch_id desc;
877
878 l_dummy NUMBER;
879
880 BEGIN
881 p_prev_BID := null;
882 p_prev_BED := to_date(null);
883
884 open prev_normal;
885 fetch prev_normal into p_prev_BID, p_prev_BED;
886 close prev_normal;
887
888 --=================================== DEBUG ===============================================--
889 IF xtr_risk_debug_pkg.g_Debug THEN
890 xtr_risk_debug_pkg.dpush('GET_PREV_NORMAL_BATCH');
891 xtr_risk_debug_pkg.dlog('p_company ' , p_company);
892 xtr_risk_debug_pkg.dlog('p_curr_BED ' , p_curr_BED);
893 xtr_risk_debug_pkg.dlog('p_prev_BID ' , p_prev_BID);
894 xtr_risk_debug_pkg.dlog('p_prev_BED ' , p_prev_BED);
895 xtr_risk_debug_pkg.dpop('GET_PREV_NORMAL_BATCH');
896 END IF;
897 --==========================================================================================--
898
899 END GET_PREV_NORMAL_BATCH;
900
901
902 /*---------------------------------------------------------------------*/
903 PROCEDURE GET_LATEST_BATCH (p_company IN VARCHAR2,
904 p_batch_id OUT NOCOPY NUMBER,
905 p_batch_start OUT NOCOPY DATE,
906 p_batch_end OUT NOCOPY DATE,
907 p_gl_group_id OUT NOCOPY NUMBER,
908 p_upgrade_batch OUT NOCOPY VARCHAR2) AS
909 /*---------------------------------------------------------------------*
910 | |
911 | Finds the latest batch information. |
912 | Only Reval/Accrual journals will be included. |
913 | |
914 *---------------------------------------------------------------------*/
915 cursor last_batch is
916 select batch_id,
917 period_start,
918 period_end,
919 gl_group_id,
920 nvl(upgrade_batch,'N')
921 from xtr_batches
922 where company_code = p_company
923 and batch_type is null -- RA batch only
924 order by period_end desc, period_start desc, batch_id desc;
925
926 BEGIN
927
928 p_batch_id := null;
929 p_batch_end := to_date(null);
930 p_gl_group_id := null;
931 p_upgrade_batch := null;
932
933 open last_batch;
934 fetch last_batch into p_batch_id, p_batch_start, p_batch_end, p_gl_group_id, p_upgrade_batch;
935 close last_batch;
936
937 --=================================== DEBUG ===============================================--
938 IF xtr_risk_debug_pkg.g_Debug THEN
939 xtr_risk_debug_pkg.dpush('GET_LATEST_BATCH');
940 xtr_risk_debug_pkg.dlog('p_company ' , p_company);
941 xtr_risk_debug_pkg.dlog('p_batch_id ' , p_batch_id);
942 xtr_risk_debug_pkg.dlog('p_batch_start ' , p_batch_start);
943 xtr_risk_debug_pkg.dlog('p_batch_end ' , p_batch_end);
944 xtr_risk_debug_pkg.dlog('p_gl_group_id ' , p_gl_group_id);
945 xtr_risk_debug_pkg.dlog('p_upgrade_batch ' , p_upgrade_batch);
946 xtr_risk_debug_pkg.dpop('GET_LATEST_BATCH');
947 END IF;
948 --==========================================================================================--
949
950 END;
951
952
953
954 /*-----------------------------------------------------------------------------*/
955 PROCEDURE GENERATE_REVAL_RATES (p_company IN VARCHAR2,
956 p_batch_start IN DATE,
957 p_batch_end IN DATE,
958 p_prev_batch_id IN NUMBER,
959 p_batch_id IN OUT NOCOPY NUMBER,
960 -- do not pass batch id for new batch
961 p_retcode OUT NOCOPY NUMBER) AS
962 /*------------------------------------------------------------------------------*
963 * *
964 * Generates the reval rates - *
965 * calls procedure XTR_REVAL_PROCESS_P.GET_ALL_REVAL_RATES *
966 * *
967 *------------------------------------------------------------------------------*/
968
969 BEGIN
970 --=================================== DEBUG ===============================================--
971 IF xtr_risk_debug_pkg.g_Debug THEN
972 xtr_risk_debug_pkg.dpush('GENERATE_REVAL_RATES - In Parameters');
973 xtr_risk_debug_pkg.dlog('p_company ' , p_company);
974 xtr_risk_debug_pkg.dlog('p_batch_start ' , p_batch_start);
975 xtr_risk_debug_pkg.dlog('p_batch_end ' , p_batch_end);
976 xtr_risk_debug_pkg.dlog('p_prev_batch_id ' , p_prev_batch_id);
977 xtr_risk_debug_pkg.dlog('p_batch_id ' , p_batch_id);
978 xtr_risk_debug_pkg.dpop('GENERATE_REVAL_RATES - In Parameters');
979 END IF;
980 --==========================================================================================--
981
982 p_retcode := 0;
983 if p_prev_batch_id is not null then
984 p_retcode := LOCK_EVENT(p_prev_batch_id,
985 C_REVAL,
986 C_AUTH_YES,
987 C_NO_REVAL_DATA,
988 C_LOCKED_REVAL);
989 end if;
990
991 /*===================================================*/
992 /* Calculate Revaluation Rates */
993 /*===================================================*/
994 if p_retcode = 0 then
995 XTR_REVAL_PROCESS_P.GET_ALL_REVAL_RATES(p_company,
996 p_batch_start,
997 p_batch_end,
998 'N', -- only generate rates for Normal batch
999 p_batch_id);
1000 COMMIT;
1001 FND_MESSAGE.Set_Name('XTR', C_GENERATED_RATES);
1002 FND_MESSAGE.Set_Token('BATCH', p_batch_id);
1003 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
1004 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
1005
1006 end if;
1007
1008 --=================================== DEBUG ===============================================--
1009 IF xtr_risk_debug_pkg.g_Debug THEN
1010 xtr_risk_debug_pkg.dpush('GENERATE_REVAL_RATES - Out Parameters');
1011 xtr_risk_debug_pkg.dlog('p_retcode ' , p_retcode);
1012 xtr_risk_debug_pkg.dpop('GENERATE_REVAL_RATES - Out Parameters');
1013 END IF;
1014 --==========================================================================================--
1015
1016 END GENERATE_REVAL_RATES;
1017
1018
1019 /*------------------------------------------------------------------------------*/
1020 PROCEDURE GENERATE_REVAL_DETAILS (p_retcode OUT NOCOPY NUMBER,
1021 p_company IN VARCHAR2,
1022 p_batch_start IN DATE,
1023 p_batch_end IN DATE,
1024 p_batch_id IN NUMBER,
1025 p_prev_batch_id IN NUMBER) AS
1026 /*------------------------------------------------------------------------------*
1027 * *
1028 * Generates the reval details - *
1029 * calls procedure XTR_REVAL_PROCESS_P.CALC_REVALS *
1030 * *
1031 *------------------------------------------------------------------------------*/
1032
1033 l_errbuf VARCHAR2(255) := null;
1034 l_retcode NUMBER := 0;
1035 l_batch_id NUMBER := 0;
1036
1037 cursor c_batch_id is
1038 select batch_id
1039 from xtr_batches
1040 where period_start = p_batch_start
1041 and period_end = p_batch_end
1042 and company_code = p_company
1043 and batch_type is null; -- 3527080 exclude NRA batch
1044
1045
1046 BEGIN
1047 --=================================== DEBUG ===============================================--
1048 IF xtr_risk_debug_pkg.g_Debug THEN
1049 xtr_risk_debug_pkg.dpush('GENERATE_REVAL_DETAILS - In Parameters');
1050 xtr_risk_debug_pkg.dlog('p_company ' , p_company);
1051 xtr_risk_debug_pkg.dlog('p_batch_start ' , p_batch_start);
1052 xtr_risk_debug_pkg.dlog('p_batch_end ' , p_batch_end);
1053 xtr_risk_debug_pkg.dlog('p_batch_id ' , p_batch_id);
1054 xtr_risk_debug_pkg.dlog('p_prev_batch_id ' , p_prev_batch_id);
1055 xtr_risk_debug_pkg.dpop('GENERATE_REVAL_DETAILS - In Parameters');
1056 END IF;
1057 --==========================================================================================--
1058
1059 p_retcode := 0;
1060
1061 if p_prev_batch_id is not null then
1062 p_retcode := LOCK_EVENT(p_prev_batch_id,
1063 C_REVAL,
1064 C_AUTH_YES,
1065 C_NO_REVAL_DATA,
1066 C_LOCKED_REVAL);
1067 end if;
1068
1069 if p_retcode = 0 then
1070 p_retcode := LOCK_BATCH(p_batch_id,
1071 p_company,
1072 C_NO_BATCH,
1073 C_LOCKED_BATCH);
1074 end if;
1075
1076 /*===================================================*/
1077 /* Calculate Revaluation Details */
1078 /*===================================================*/
1079 if p_retcode = 0 then
1080 XTR_REVAL_PROCESS_P.CALC_REVALS(l_errbuf,
1081 l_retcode,
1082 p_company,
1083 p_batch_id);
1084
1085 --=================================== DEBUG ===============================================--
1086 IF xtr_risk_debug_pkg.g_Debug THEN
1087 xtr_risk_debug_pkg.dpush('GENERATE_REVAL_DETAILS - Retcode from CALC_REVALS');
1088 xtr_risk_debug_pkg.dlog('l_retcode ' , l_retcode);
1089 xtr_risk_debug_pkg.dpop('GENERATE_REVAL_DETAILS - Retcode from CALC_REVALS');
1090 END IF;
1091 --==========================================================================================--
1092
1093 l_retcode := nvl(l_retcode,0);
1094
1095 /*=====================================================================================*/
1096 /* Need to check since CALC_REVALS can return 0 if there are incomplete details */
1097 /*=====================================================================================*/
1098 if l_retcode >= 0 then
1099
1100 if p_batch_id is null then
1101 open c_batch_id;
1102 fetch c_batch_id into l_batch_id;
1103 close c_batch_id;
1104 else
1105 l_batch_id := p_batch_id;
1106 end if;
1107
1108 if REVAL_DETAILS_INCOMPLETE (p_company, p_batch_start, p_batch_end, l_batch_id) then
1109
1110 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
1111 FND_MESSAGE.Set_Name('XTR', C_INCOMPLETE_REVAL);
1112 FND_MESSAGE.Set_Token('BATCH', l_batch_id);
1113 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
1114 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
1115 p_retcode := 2;
1116
1117 else
1118 FND_MESSAGE.Set_Name('XTR', C_GENERATED_REVAL);
1119 FND_MESSAGE.Set_Token('BATCH', l_batch_id);
1120 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
1121 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
1122 p_retcode := l_retcode; -- return any retcode from revaluation
1123 end if;
1124
1125 else
1126 rollback;
1127 p_retcode := 2;
1128 end if;
1129
1130 COMMIT;
1131
1132 end if; -- p_retcode = 0
1133
1134 --================================ DEBUG ==================================--
1135 IF xtr_risk_debug_pkg.g_Debug THEN
1136 xtr_risk_debug_pkg.dpush('GENERATE_REVAL_DETAILS - Out Parameters');
1137 xtr_risk_debug_pkg.dlog('p_retcode ' , p_retcode);
1138 xtr_risk_debug_pkg.dpop('GENERATE_REVAL_DETAILS - Out Parameters');
1139 END IF;
1140 --==========================================================================--
1141
1142 END GENERATE_REVAL_DETAILS;
1143
1144 /*--------------------------------------------------------------------------*/
1145 PROCEDURE AUTHORIZE_REVAL_EVENT (p_retcode OUT NOCOPY NUMBER,
1146 p_company IN VARCHAR2,
1147 p_batch_id IN NUMBER,
1148 p_prev_batch_id IN NUMBER) AS
1149 /*- -------------------------------------------------------------------------*
1150 * *
1151 * Authorise the reval details. *
1152 * *
1153 *---------------------------------------------------------------------------*/
1154
1155 BEGIN
1156 --=================================== DEBUG ===============================--
1157 IF xtr_risk_debug_pkg.g_Debug THEN
1158 xtr_risk_debug_pkg.dpush('AUTHORIZE_REVAL_EVENT - In Parameters');
1159 xtr_risk_debug_pkg.dlog('p_company ' , p_company);
1160 xtr_risk_debug_pkg.dlog('p_batch_id ' , p_batch_id);
1161 xtr_risk_debug_pkg.dlog('p_prev_batch_id ' , p_prev_batch_id);
1162 xtr_risk_debug_pkg.dpop('AUTHORIZE_REVAL_EVENT - In Parameters');
1163 END IF;
1164 --==========================================================================--
1165
1166 p_retcode := 0;
1167
1168 if p_prev_batch_id is not null then
1169 p_retcode := LOCK_EVENT(p_prev_batch_id,
1170 C_REVAL,
1171 C_AUTH_YES,
1172 C_NO_REVAL_DATA,
1173 C_LOCKED_REVAL);
1174 end if;
1175
1176 if p_retcode = 0 then
1177 p_retcode := LOCK_EVENT(p_batch_id,
1178 C_REVAL,
1179 NULL,
1180 C_NO_REVAL_DATA,
1181 C_LOCKED_REVAL);
1182 end if;
1183
1184 if p_retcode = 0 then
1185 /*===================================================*/
1186 /* Authorize Revaluation Details */
1187 /*===================================================*/
1188 /* 3050444 old issue 2
1189 update xtr_batch_events
1190 set authorized = 'Y',
1191 authorized_by = fnd_global.user_id,
1192 authorized_on = trunc(sysdate),
1193 last_updated_by = fnd_global.user_id,
1194 last_update_date = trunc(sysdate),
1195 last_update_login = fnd_global.user_id
1196 where batch_id = p_batch_id
1197 and event_code = C_REVAL;
1198 */
1199
1200 -- 3050444 new issue 2
1201 BEGIN
1202 xtr_dnm_pkg.authorize(p_batch_id);
1203 EXCEPTION
1204 when others then
1205 p_retcode := 2;
1206 END;
1207
1208 if p_retcode = 0 then
1209 COMMIT;
1210
1211 FND_MESSAGE.Set_Name('XTR', C_AUTHORIZED_REVAL);
1212 FND_MESSAGE.Set_Token('BATCH', p_batch_id);
1213 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
1214 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
1215
1216 end if;
1217
1218 end if;
1219
1220 --================================= DEBUG ===================================--
1221 IF xtr_risk_debug_pkg.g_Debug THEN
1222 xtr_risk_debug_pkg.dpush('AUTHORIZE_REVAL_EVENT - Out Parameters');
1223 xtr_risk_debug_pkg.dlog('p_retcode ' , p_retcode);
1224 xtr_risk_debug_pkg.dpop('AUTHORIZE_REVAL_EVENT - Out Parameters');
1225 END IF;
1226 --===========================================================================--
1227
1228 END AUTHORIZE_REVAL_EVENT;
1229
1230 /*--------------------------------------------------------------------------*/
1231 PROCEDURE GENERATE_RETRO_DETAILS (p_retcode OUT NOCOPY NUMBER,
1232 p_company IN VARCHAR2,
1233 p_batch_start IN DATE,
1234 p_batch_end IN DATE,
1235 p_batch_id IN NUMBER,
1236 p_prev_batch_id IN NUMBER) AS
1237 /*----------------------------------------------------------------------------*
1238 * *
1239 * Bug 3378028 FAS *
1240 * Generates the retro details - *
1241 * call procedure XTR_HEDGE_PROCESS_P.RETRO_EFF_TEST *
1242 * *
1243 *-----------------------------------------------------------------------------*/
1244
1245 l_errbuf VARCHAR2(255) := null;
1246 l_retcode NUMBER := 0;
1247 l_batch_id NUMBER := 0;
1248
1249 BEGIN
1250 --=================================== DEBUG ===============================================--
1251 IF xtr_risk_debug_pkg.g_Debug THEN
1252 xtr_risk_debug_pkg.dpush('GENERATE_RETRO_DETAILS - In Parameters');
1253 xtr_risk_debug_pkg.dlog('p_company ' , p_company);
1254 xtr_risk_debug_pkg.dlog('p_batch_start ' , p_batch_start);
1255 xtr_risk_debug_pkg.dlog('p_batch_end ' , p_batch_end);
1256 xtr_risk_debug_pkg.dlog('p_batch_id ' , p_batch_id);
1257 xtr_risk_debug_pkg.dlog('p_prev_batch_id ' , p_prev_batch_id);
1258 xtr_risk_debug_pkg.dpop('GENERATE_RETRO_DETAILS - In Parameters');
1259 END IF;
1260 --==========================================================================================--
1261
1262 p_retcode := 0;
1263
1264 if p_prev_batch_id is not null then
1265 -----------------------------------------------------
1266 -- If previous batch has RETROET event
1267 -----------------------------------------------------
1268 if EVENT_EXISTS(p_company, p_prev_batch_id, null, C_RETROET) then
1269 p_retcode := LOCK_EVENT(p_prev_batch_id,
1270 C_RETROET,
1271 C_AUTH_YES,
1272 C_NO_RETROET_DATA,
1273 C_LOCKED_RETROET);
1274
1275 -----------------------------------------------------
1276 -- If previous batch does not have RETROET event
1277 -----------------------------------------------------
1278 else
1279 if not EVENT_EXISTS(p_company, p_prev_batch_id, null, C_ACCRUAL) then
1280 ------------------------------------------------------
1281 -- Previous batch Should have ACCRUAL event, if not,
1282 -- the following will fail because RETROET is missing.
1283 -- THIS IS INTENDED.
1284 ------------------------------------------------------
1285 p_retcode := LOCK_EVENT(p_prev_batch_id,
1286 C_RETROET,
1287 NULL,
1288 C_NO_RETROET_DATA,
1289 C_LOCKED_RETROET);
1290 end if;
1291 end if;
1292 end if;
1293
1294 if p_retcode = 0 then
1295 p_retcode := LOCK_EVENT(p_batch_id,
1296 C_REVAL,
1297 C_AUTH_YES,
1298 C_NO_REVAL_DATA,
1299 C_LOCKED_REVAL);
1300
1301 end if;
1302
1303 if p_retcode = 0 then
1304 XTR_HEDGE_PROCESS_P.RETRO_EFF_TEST(l_errbuf,
1305 l_retcode,
1306 p_company,
1307 p_batch_id);
1308
1309 --=================================== DEBUG ===============================================--
1310 IF xtr_risk_debug_pkg.g_Debug THEN
1311 xtr_risk_debug_pkg.dpush('GENERATE_RETRO_DETAILS - Retcode from RETRO_EFF_TEST');
1312 xtr_risk_debug_pkg.dlog('l_retcode ' , l_retcode);
1313 xtr_risk_debug_pkg.dpop('GENERATE_RETRO_DETAILS - Retcode from RETRO_EFF_TEST');
1314 END IF;
1315 --==========================================================================================--
1316
1317 l_retcode := nvl(l_retcode,0);
1318
1319 /*=============================================================*/
1320 /* Check if retrospective tests has incomplete details */
1321 /*=============================================================*/
1322 if l_retcode = 0 then -- no special requirement for FAILURE/WARNING setting in retro process.
1323
1324 if RETRO_DETAILS_INCOMPLETE(p_company, p_batch_start, p_batch_end,p_batch_id) then
1325 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
1326 FND_MESSAGE.Set_Name('XTR', C_INCOMPLETE_RETROET);
1327 FND_MESSAGE.Set_Token('BATCH', p_batch_id);
1328 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
1329 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
1330 p_retcode := 2;
1331
1332 else
1333
1334 FND_MESSAGE.Set_Name('XTR', C_GENERATED_RETROET);
1335 FND_MESSAGE.Set_Token('BATCH', p_batch_id);
1336 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
1337 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
1338 p_retcode := l_retcode;
1339
1340 end if;
1341
1342 else
1343 rollback;
1344 p_retcode := 2;
1345 end if;
1346
1347 COMMIT;
1348
1349 end if; -- p_retcode = 0
1350
1351 --=================================== DEBUG ===============================================--
1352 IF xtr_risk_debug_pkg.g_Debug THEN
1353 xtr_risk_debug_pkg.dpush('GENERATE_RETRO_DETAILS - Out Parameters');
1354 xtr_risk_debug_pkg.dlog('p_retcode ' , p_retcode);
1355 xtr_risk_debug_pkg.dpop('GENERATE_RETRO_DETAILS - Out Parameters');
1356 END IF;
1357 --==========================================================================================--
1358
1359 END GENERATE_RETRO_DETAILS;
1360
1361
1362
1363 /*----------------------------------------------------------------------------*/
1364 PROCEDURE AUTHORIZE_RETRO_EVENT (p_retcode OUT NOCOPY NUMBER,
1365 p_company IN VARCHAR2,
1366 p_batch_id IN NUMBER,
1367 p_prev_batch_id IN NUMBER) AS
1368 /*-----------------------------------------------------------------------------*
1369 * *
1370 * Bug 3378028 FAS *
1371 * Authorise the retro details. *
1372 * *
1373 *-----------------------------------------------------------------------------*/
1374 BEGIN
1375
1376 --=================================== DEBUG =================================--
1377 IF xtr_risk_debug_pkg.g_Debug THEN
1378 xtr_risk_debug_pkg.dpush('AUTHORIZE_RETRO_EVENT - In Parameters');
1379 xtr_risk_debug_pkg.dlog('p_company ' , p_company);
1380 xtr_risk_debug_pkg.dlog('p_batch_id ' , p_batch_id);
1381 xtr_risk_debug_pkg.dlog('p_prev_batch_id ' , p_prev_batch_id);
1382 xtr_risk_debug_pkg.dpop('AUTHORIZE_RETRO_EVENT - In Parameters');
1383 END IF;
1384 --============================================================================--
1385
1386 p_retcode := 0;
1387
1388 -------------------------------------------------------------
1389 -- ONLY needed if previous batch has RETROET event
1390 -------------------------------------------------------------
1391 if p_prev_batch_id is not null then
1392 if EVENT_EXISTS(p_company, p_prev_batch_id, null, C_RETROET) then
1393 ----------------------------------------------
1394 -- previous batch might not have RETROET event
1395 ----------------------------------------------
1396 p_retcode := LOCK_EVENT(p_prev_batch_id,
1397 C_RETROET,
1398 C_AUTH_YES,
1399 C_NO_RETROET_DATA,
1400 C_LOCKED_RETROET);
1401 else
1402 if not EVENT_EXISTS(p_company, p_prev_batch_id, null, C_ACCRUAL) then
1403 ------------------------------------------------------
1404 -- Previous batch should have ACCRUAL event, so below
1405 -- is intended to fail because RETROET is missing.
1406 ------------------------------------------------------
1407 p_retcode := LOCK_EVENT(p_prev_batch_id,
1408 C_RETROET,
1409 NULL,
1410 C_NO_RETROET_DATA,
1411 C_LOCKED_RETROET);
1412 end if;
1413 end if;
1414 end if;
1415
1416 ------------------------------
1417 -- Locks current RETROET event
1418 ------------------------------
1419 if p_retcode = 0 then
1420 p_retcode := LOCK_EVENT(p_batch_id,
1421 C_RETROET,
1422 NULL,
1423 C_NO_RETROET_DATA,
1424 C_LOCKED_RETROET);
1425 end if;
1426
1427 if p_retcode = 0 then
1428 /*===================================================*/
1429 /* Authorize Retrospective Details */
1430 /*===================================================*/
1431
1432 BEGIN
1433
1434 XTR_HEDGE_PROCESS_P.AUTHORIZE(p_company, p_batch_id);
1435
1436 EXCEPTION
1437 when others then
1438 p_retcode := 2;
1439 END;
1440
1441 COMMIT;
1442 FND_MESSAGE.Set_Name('XTR', C_AUTHORIZED_RETROET);
1443 FND_MESSAGE.Set_Token('BATCH', p_batch_id);
1444 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
1445 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
1446
1447 end if;
1448
1449 --=================================== DEBUG =================================--
1450 IF xtr_risk_debug_pkg.g_Debug THEN
1451 xtr_risk_debug_pkg.dpush('AUTHORIZE_RETRO_EVENT - Out Parameters');
1452 xtr_risk_debug_pkg.dlog('p_retcode ' , p_retcode);
1453 xtr_risk_debug_pkg.dpop('AUTHORIZE_RETRO_EVENT - Out Parameters');
1454 END IF;
1455 --============================================================================--
1456
1457 END AUTHORIZE_RETRO_EVENT;
1458
1459 /*------------------------------------------------------------------------------*/
1460 PROCEDURE GENERATE_ACCRUAL_DETAILS (p_retcode OUT NOCOPY NUMBER,
1461 p_company IN VARCHAR2,
1462 p_do_reval IN VARCHAR2,
1463 p_do_retro IN VARCHAR2,-- 3378028 FAS
1464 p_batch_start IN DATE,
1465 p_batch_end IN DATE,
1466 p_batch_id IN OUT NOCOPY NUMBER,
1467 -- do not pass batch id for new batch
1468 p_prev_batch_id IN NUMBER) AS
1469 /*------------------------------------------------------------------------------*
1470 * *
1471 * Generates the accrual details - *
1472 * calls procedure XTR_ACCRUAL_PROCESS_P.CALCULATE_ACCRUAL_AMORTISATION *
1473 * *
1474 *------------------------------------------------------------------------------*/
1475
1476 cursor cur_new_BID is
1477 select batch_id
1478 from xtr_batches
1479 where company_code = p_company
1480 and period_start = p_batch_start
1481 and period_end = p_batch_end
1482 and batch_type is null; -- 3527080 exclude NRA batch
1483
1484 l_errbuf VARCHAR2(255) := null;
1485
1486 BEGIN
1487 --=================================== DEBUG ===============================================--
1488 IF xtr_risk_debug_pkg.g_Debug THEN
1489 xtr_risk_debug_pkg.dpush('GENERATE_ACCRUAL_DETAILS - In Parameters');
1490 xtr_risk_debug_pkg.dlog('p_company ' , p_company);
1491 xtr_risk_debug_pkg.dlog('p_do_reval ' , p_do_reval);
1492 xtr_risk_debug_pkg.dlog('p_do_retro ' , p_do_retro);
1493 xtr_risk_debug_pkg.dlog('p_batch_start ' , p_batch_start);
1494 xtr_risk_debug_pkg.dlog('p_batch_end ' , p_batch_end);
1495 xtr_risk_debug_pkg.dlog('p_batch_id ' , p_batch_id);
1496 xtr_risk_debug_pkg.dlog('p_prev_batch_id ' , p_prev_batch_id);
1497 xtr_risk_debug_pkg.dpop('GENERATE_ACCRUAL_DETAILS - In Parameters');
1498 END IF;
1499 --==========================================================================================--
1500
1501 p_retcode := 0;
1502
1503 if p_prev_batch_id is not null then
1504 p_retcode := LOCK_EVENT(p_prev_batch_id,
1505 C_ACCRUAL,
1506 C_AUTH_YES,
1507 C_NO_ACCRUAL_DATA,
1508 C_LOCKED_ACCRUAL);
1509 end if;
1510
1511 if p_retcode = 0 then
1512 if p_do_reval = 'Y' then
1513 if p_do_retro = 'Y' then -- 3378028 FAS Extra locking for Retrospective Testing.
1514 p_retcode := LOCK_EVENT(p_batch_id,
1515 C_RETROET,
1516 C_AUTH_YES,
1517 C_NO_RETROET_DATA,
1518 C_LOCKED_RETROET);
1519 else
1520 p_retcode := LOCK_EVENT(p_batch_id,
1521 C_REVAL,
1522 C_AUTH_YES,
1523 C_NO_REVAL_DATA,
1524 C_LOCKED_REVAL);
1525 end if;
1526 end if;
1527 end if;
1528
1529 if p_retcode = 0 then
1530 /*===================================================*/
1531 /* Calculate Accrual Details */
1532 /*===================================================*/
1533 XTR_ACCRUAL_PROCESS_P.CALCULATE_ACCRUAL_AMORTISATION(l_errbuf,
1534 p_retcode,
1535 p_company,
1536 p_batch_id, -- no batch id for new batch
1537 FND_DATE.date_to_canonical(p_batch_start),
1538 FND_DATE.date_to_canonical(p_batch_end),
1539 'N');
1540 --=================================== DEBUG ===============================================--
1541 IF xtr_risk_debug_pkg.g_Debug THEN
1542 xtr_risk_debug_pkg.dpush('GENERATE_ACCRUAL_DETAILS - Retcode from CALCULATE_ACCRUAL_AMORTISATION');
1543 xtr_risk_debug_pkg.dlog('p_retcode ' , p_retcode);
1544 xtr_risk_debug_pkg.dpop('GENERATE_ACCRUAL_DETAILS - Retcode from CALCULATE_ACCRUAL_AMORTISATION');
1545 END IF;
1546 --==========================================================================================--
1547
1548 p_retcode := nvl(p_retcode,0); -- floating rate bond may return '1' as a warning
1549
1550 if p_retcode = -1 then
1551 rollback;
1552 p_retcode := 2;
1553
1554 else
1555
1556 if p_batch_id is null then
1557 open cur_new_BID;
1558 fetch cur_new_BID into p_batch_id;
1559 close cur_new_BID;
1560 end if;
1561
1562 FND_MESSAGE.Set_Name('XTR', C_GENERATED_ACCRUAL);
1563 FND_MESSAGE.Set_Token('BATCH', p_batch_id);
1564 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
1565 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
1566
1567 end if;
1568
1569 end if;
1570
1571 --=================================== DEBUG ===============================================--
1572 IF xtr_risk_debug_pkg.g_Debug THEN
1573 xtr_risk_debug_pkg.dpush('GENERATE_ACCRUAL_DETAILS - Out Parameters');
1574 xtr_risk_debug_pkg.dlog('p_retcode ' , p_retcode);
1575 xtr_risk_debug_pkg.dpop('GENERATE_ACCRUAL_DETAILS - Out Parameters');
1576 END IF;
1577 --==========================================================================================--
1578
1579 END GENERATE_ACCRUAL_DETAILS;
1580
1581
1582 /*------------------------------------------------------------------------------*/
1583 PROCEDURE AUTHORIZE_ACCRUAL_EVENT (p_retcode OUT NOCOPY NUMBER,
1584 p_company IN VARCHAR2,
1585 p_batch_id IN NUMBER,
1586 p_prev_batch_id IN NUMBER) AS
1587 /*------------------------------------------------------------------------------*
1588 * *
1589 * Authorise accrual details. *
1590 * *
1591 *------------------------------------------------------------------------------*/
1592
1593 l_rowid ROWID;
1594
1595 BEGIN
1596 --=================================== DEBUG ===============================================--
1597 IF xtr_risk_debug_pkg.g_Debug THEN
1598 xtr_risk_debug_pkg.dpush('AUTHORIZE_ACCRUAL_EVENT - In Parameters');
1599 xtr_risk_debug_pkg.dlog('p_company ' , p_company);
1600 xtr_risk_debug_pkg.dlog('p_batch_id ' , p_batch_id);
1601 xtr_risk_debug_pkg.dlog('p_prev_batch_id ' , p_prev_batch_id);
1602 xtr_risk_debug_pkg.dpop('AUTHORIZE_ACCRUAL_EVENT - In Parameters');
1603 END IF;
1604 --==========================================================================================--
1605
1606 p_retcode := 0;
1607
1608 if p_prev_batch_id is not null then
1609 p_retcode := LOCK_EVENT(p_prev_batch_id,
1610 C_ACCRUAL,
1611 C_AUTH_YES,
1612 C_NO_ACCRUAL_DATA,
1613 C_LOCKED_ACCRUAL);
1614 end if;
1615
1616 if p_retcode = 0 then
1617 p_retcode := LOCK_EVENT(p_batch_id,
1618 C_ACCRUAL,
1619 NULL,
1620 C_NO_ACCRUAL_DATA,
1621 C_LOCKED_ACCRUAL);
1622 end if;
1623
1624 if p_retcode = 0 then
1625 /*===================================================*/
1626 /* Authorize Accrual Details */
1627 /*===================================================*/
1628 update xtr_batch_events
1629 set authorized = 'Y',
1630 authorized_by = fnd_global.user_id,
1631 authorized_on = trunc(sysdate),
1632 last_updated_by = fnd_global.user_id,
1633 last_update_date = trunc(sysdate),
1634 last_update_login = fnd_global.user_id
1635 where batch_id = p_batch_id
1636 and event_code = C_ACCRUAL;
1637
1638 COMMIT;
1639
1640 FND_MESSAGE.Set_Name('XTR', C_AUTHORIZED_ACCRUAL);
1641 FND_MESSAGE.Set_Token('BATCH', p_batch_id);
1642 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
1643 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
1644
1645 end if;
1646
1647 --=================================== DEBUG ===============================================--
1648 IF xtr_risk_debug_pkg.g_Debug THEN
1649 xtr_risk_debug_pkg.dpush('AUTHORIZE_ACCRUAL_EVENT - Out Parameters');
1650 xtr_risk_debug_pkg.dlog('p_retcode ' , p_retcode);
1651 xtr_risk_debug_pkg.dpop('AUTHORIZE_ACCRUAL_EVENT - Out Parameters');
1652 END IF;
1653 --==========================================================================================--
1654
1655 END AUTHORIZE_ACCRUAL_EVENT;
1656
1657
1658
1659 /*------------------------------------------------------------------------------*/
1660 PROCEDURE GENERATE_JOURNAL_DETAILS (p_retcode OUT NOCOPY NUMBER,
1661 p_company IN VARCHAR2,
1662 p_batch_id IN NUMBER,
1663 p_prev_batch_id IN NUMBER) AS
1664 /*------------------------------------------------------------------------------*
1665 * *
1666 * Generates the journal details - *
1667 * calls procedure XTR_JOURNAL_PROCESS_P.JOURNALS *
1668 * *
1669 * *
1670 *------------------------------------------------------------------------------*/
1671 l_errbuf VARCHAR2(255) := null;
1672
1673 BEGIN
1674 --=================================== DEBUG ===============================================--
1675 IF xtr_risk_debug_pkg.g_Debug THEN
1676 xtr_risk_debug_pkg.dpush('GENERATE_JOURNAL_DETAILS - In Parameters');
1677 xtr_risk_debug_pkg.dlog('p_company ' , p_company);
1678 xtr_risk_debug_pkg.dlog('p_batch_id ' , p_batch_id);
1679 xtr_risk_debug_pkg.dlog('p_prev_batch_id ' , p_prev_batch_id);
1680 xtr_risk_debug_pkg.dpop('GENERATE_JOURNAL_DETAILS - In Parameters');
1681 END IF;
1682 --==========================================================================================--
1683
1684 p_retcode := 0;
1685
1686 if p_prev_batch_id is not null then
1687 p_retcode := LOCK_EVENT(p_prev_batch_id,
1688 C_JOURNAL,
1689 NULL,
1690 C_NO_JOURNAL_DATA,
1691 C_LOCKED_JOURNAL);
1692 end if;
1693
1694 if p_retcode = 0 then
1695 p_retcode := LOCK_EVENT(p_batch_id,
1696 C_ACCRUAL,
1697 C_AUTH_YES,
1698 C_NO_ACCRUAL_DATA,
1699 C_LOCKED_ACCRUAL);
1700 end if;
1701
1702 if p_retcode = 0 then
1703 /*===================================================*/
1704 /* Calculate Journal Details */
1705 /*===================================================*/
1706 XTR_JOURNAL_PROCESS_P.JOURNALS(l_errbuf,
1707 p_retcode,
1708 null, -- p_source_option
1709 p_company,
1710 p_batch_id, -- p_batch_id_from
1711 p_batch_id, -- p_batch_id_to
1712 null, -- p_cutoff_date
1713 null, -- p_dummy_date
1714 C_GENERATE, -- p_processing_option
1715 null, -- p_dummy_proc_opt
1716 null,
1717 'N'); -- p_incl_transferred
1718
1719 p_retcode := nvl(p_retcode,0);
1720
1721 if p_retcode = -1 then
1722 rollback;
1723 p_retcode := 2;
1724
1725 else
1726
1727 FND_MESSAGE.Set_Name('XTR', C_GENERATED_JOURNAL);
1728 FND_MESSAGE.Set_Token('BATCH', p_batch_id);
1729 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
1730 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
1731
1732 end If;
1733
1734 end if;
1735
1736 --=================================== DEBUG ===============================================--
1737 IF xtr_risk_debug_pkg.g_Debug THEN
1738 xtr_risk_debug_pkg.dpush('GENERATE_JOURNAL_DETAILS - Out Parameters');
1739 xtr_risk_debug_pkg.dlog('p_retcode ' , p_retcode);
1740 xtr_risk_debug_pkg.dpop('GENERATE_JOURNAL_DETAILS - Out Parameters');
1741 END IF;
1742 --==========================================================================================--
1743
1744 END GENERATE_JOURNAL_DETAILS;
1745
1746
1747 /*------------------------------------------------------------------------------*/
1748 PROCEDURE TRANSFER_JOURNALS (p_retcode OUT NOCOPY NUMBER,
1749 p_company IN VARCHAR2,
1750 p_batch_id IN NUMBER,
1751 p_prev_batch_id IN NUMBER,
1752 p_closed_periods IN VARCHAR2) AS
1753 /*------------------------------------------------------------------------------*
1754 * *
1755 * Transfer the journal details - *
1756 * calls procedure XTR_JOURNAL_PROCESS_P.JOURNALS *
1757 * This should only be called if process starts and ends after *
1758 * transfer of journals. *
1759 * *
1760 *------------------------------------------------------------------------------*/
1761 l_errbuf VARCHAR2(255) := null;
1762
1763 BEGIN
1764 --=================================== DEBUG ===============================================--
1765 IF xtr_risk_debug_pkg.g_Debug THEN
1766 xtr_risk_debug_pkg.dpush('TRANSFER_JOURNALS - In Parameters');
1767 xtr_risk_debug_pkg.dlog('p_company ' , p_company);
1768 xtr_risk_debug_pkg.dlog('p_batch_id ' , p_batch_id);
1769 xtr_risk_debug_pkg.dlog('p_prev_batch_id ' , p_prev_batch_id);
1770 xtr_risk_debug_pkg.dlog('p_closed_periods ' , p_closed_periods);
1771 xtr_risk_debug_pkg.dpop('TRANSFER_JOURNALS - In Parameters');
1772 END IF;
1773 --==========================================================================================--
1774
1775 p_retcode := 0;
1776
1777 p_retcode := LOCK_EVENT(p_batch_id,
1778 C_JOURNAL,
1779 NULL,
1780 C_NO_JOURNAL_DATA,
1781 C_LOCKED_JOURNAL);
1782
1783 if p_retcode = 0 then
1784 /*===================================================*/
1785 /* Transfer Journal Details */
1786 /*===================================================*/
1787 XTR_JOURNAL_PROCESS_P.JOURNALS(l_errbuf,
1788 p_retcode,
1789 null, -- p_source_option
1790 p_company,
1791 p_batch_id, -- p_batch_id_from
1792 p_batch_id, -- p_batch_id_to
1793 null, -- p_cutoff_date
1794 null, -- p_dummy_date
1795 C_TRANSFER, -- p_processing_option
1796 null, -- p_dummy_proc_opt
1797 p_closed_periods,
1798 'N', -- p_incl_transferred
1799 G_MULTIPLE_ACCT); -- Bug 4639287 Multiple account Transfer
1800
1801 p_retcode := nvl(p_retcode,0);
1802
1803 if p_retcode = -1 then
1804 rollback;
1805 p_retcode := 2;
1806
1807 else
1808
1809 FND_MESSAGE.Set_Name('XTR', C_TRANSFERRED_JOURNAL);
1810 FND_MESSAGE.Set_Token('BATCH', p_batch_id);
1811 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
1812 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
1813
1814 end If;
1815
1816 end if;
1817
1818 --=================================== DEBUG ===============================================--
1819 IF xtr_risk_debug_pkg.g_Debug THEN
1820 xtr_risk_debug_pkg.dpush('TRANSFER_JOURNALS - Out Parameters');
1821 xtr_risk_debug_pkg.dlog('p_retcode ' , p_retcode);
1822 xtr_risk_debug_pkg.dpop('TRANSFER_JOURNALS - Out Parameters');
1823 END IF;
1824 --==========================================================================================--
1825
1826 END TRANSFER_JOURNALS;
1827
1828
1829 /*------------------------------------------------------------------------------*/
1830 PROCEDURE REVAL_SUBPROCESS (p_retcode OUT NOCOPY NUMBER,
1831 p_company IN VARCHAR2,
1832 p_cutoff_date IN DATE) AS
1833 /*------------------------------------------------------------------------------*
1834 * *
1835 * Process incomplete reval batches. *
1836 * Only Reval/Accrual journals will be included. *
1837 * *
1838 *------------------------------------------------------------------------------*/
1839
1840 cursor INCOMPLETE_REVAL_BATCH is
1841 select batch_id,
1842 period_start,
1843 period_end
1844 from xtr_batches a
1845 where company_code = p_company
1846 and period_end <= p_cutoff_date
1847 and batch_type is null
1848 and nvl(upgrade_batch,'N') = 'N'
1849 and not exists (select 1
1850 from xtr_batch_events b
1851 where b.batch_id = a.batch_id
1852 and event_code = C_REVAL
1853 and nvl(authorized,'N') = 'Y')
1854 order by period_end, period_start;
1855
1856 l_sub_retcode NUMBER := 0;
1857 l_batch_id NUMBER;
1858 l_batch_start DATE;
1859 l_batch_end DATE;
1860 l_prvBID NUMBER;
1861 l_prvBED DATE;
1862
1863 BEGIN
1864 --=================================== DEBUG ===============================================--
1865 IF xtr_risk_debug_pkg.g_Debug THEN
1866 xtr_risk_debug_pkg.dpush('REVAL_SUBPROCESS - In Parameters');
1867 xtr_risk_debug_pkg.dlog('p_company ' , p_company);
1868 xtr_risk_debug_pkg.dlog('p_cutoff_date ' , p_cutoff_date);
1869 xtr_risk_debug_pkg.dpop('REVAL_SUBPROCESS - In Parameters');
1870 END IF;
1871 --==========================================================================================--
1872
1873 p_retcode := 0;
1874 l_prvBID := null;
1875
1876 open INCOMPLETE_REVAL_BATCH;
1877 fetch INCOMPLETE_REVAL_BATCH into l_batch_id, l_batch_start, l_batch_end;
1878 while INCOMPLETE_REVAL_BATCH%FOUND and p_retcode <> 2 loop
1879
1880 if l_prvBID is null then
1881 GET_PREV_NORMAL_BATCH(p_company, l_batch_end, l_prvBID, l_prvBED);
1882 end if;
1883
1884 if not EVENT_EXISTS(p_company, l_batch_id, NULL, C_REVAL) then
1885 if not EVENT_EXISTS(p_company, l_batch_id, NULL, C_RATES) then
1886 GENERATE_REVAL_RATES(p_company,
1887 l_batch_start,
1888 l_batch_end,
1889 l_prvBID,
1890 l_batch_id,
1891 l_sub_retcode);
1892 p_retcode := greatest(l_sub_retcode, p_retcode);
1893 end if;
1894
1895 if p_retcode = 0 then
1896 GENERATE_REVAL_DETAILS(l_sub_retcode,
1897 p_company,
1898 l_batch_start,
1899 l_batch_end,
1900 l_batch_id,
1901 l_prvBID);
1902 p_retcode := greatest(l_sub_retcode, p_retcode);
1903 end if;
1904 end if;
1905
1906 if p_retcode <> 2 then
1907 AUTHORIZE_REVAL_EVENT(l_sub_retcode,
1908 p_company,
1909 l_batch_id,
1910 l_prvBID);
1911 p_retcode := greatest(l_sub_retcode, p_retcode);
1912
1913 if p_retcode <> 2 then
1914 l_prvBID := l_batch_id;
1915 l_prvBED := l_batch_end;
1916 fetch INCOMPLETE_REVAL_BATCH into l_batch_id, l_batch_start, l_batch_end;
1917 end if;
1918
1919 end if;
1920
1921 end loop;
1922
1923 close INCOMPLETE_REVAL_BATCH;
1924
1925 --=================================== DEBUG ===============================================--
1926 IF xtr_risk_debug_pkg.g_Debug THEN
1927 xtr_risk_debug_pkg.dpush('REVAL_SUBPROCESS - Out Parameters');
1928 xtr_risk_debug_pkg.dlog('p_retcode ' , p_retcode);
1929 xtr_risk_debug_pkg.dpop('REVAL_SUBPROCESS - Out Parameters');
1930 END IF;
1931 --==========================================================================================--
1932
1933 END REVAL_SUBPROCESS;
1934
1935
1936 /*------------------------------------------------------------------------------*/
1937 PROCEDURE CREATE_NEW_REVAL (p_retcode OUT NOCOPY NUMBER,
1938 p_company IN VARCHAR2,
1939 p_incomplete IN VARCHAR2,
1940 p_cutoff_date IN DATE) AS
1941 /*------------------------------------------------------------------------------*
1942 * Creates a new reval batch. *
1943 * Only Reval/Accrual journals will be included. *
1944 * *
1945 *------------------------------------------------------------------------------*/
1946
1947 l_upgrade VARCHAR2(1) := null;
1948 l_sub_retcode NUMBER := 0;
1949 l_batch_id NUMBER;
1950 l_period_start DATE;
1951 l_lastBSD DATE;
1952 l_lastBED DATE;
1953 l_prvBID NUMBER := null;
1954 l_dummy_date DATE;
1955 l_dummy_BID NUMBER;
1956 l_dummy_id NUMBER;
1957
1958 BEGIN
1959 --=================================== DEBUG ===============================================--
1960 IF xtr_risk_debug_pkg.g_Debug THEN
1961 xtr_risk_debug_pkg.dpush('CREATE_NEW_REVAL - In Parameters');
1962 xtr_risk_debug_pkg.dlog('p_company ' , p_company);
1963 xtr_risk_debug_pkg.dlog('p_incomplete ' , p_incomplete);
1964 xtr_risk_debug_pkg.dlog('p_cutoff_date ' , p_cutoff_date);
1965 xtr_risk_debug_pkg.dpop('CREATE_NEW_REVAL - In Parameters');
1966 END IF;
1967 --==========================================================================================--
1968
1969 p_retcode := 0;
1970
1971 GET_LATEST_BATCH (p_company, l_dummy_BID, l_lastBSD, l_lastBED, l_dummy_id, l_upgrade);
1972
1973 ----------------------------------------------------------------------------------
1974 -- Only perform for a new batch if cutoff date is higher than last batch end date
1975 ----------------------------------------------------------------------------------
1976 --if p_incomplete = 'N' or (p_incomplete = 'Y' and l_lastBED < p_cutoff_date) then
1977 if ((p_incomplete = 'N' and nvl(l_lastBED,p_cutoff_date) <= p_cutoff_date and
1978 not EVENT_EXISTS (p_company, null,p_cutoff_date,C_RATES))
1979 or (p_incomplete = 'Y' and l_lastBED < p_cutoff_date)) then
1980
1981 if nvl(l_upgrade,'Y') = 'N' then
1982 GET_PREV_NORMAL_BATCH(p_company,p_cutoff_date,l_prvBID,l_dummy_date); -- l_lastBED
1983 end if;
1984
1985 if l_upgrade is not null then
1986 if l_lastBED >= p_cutoff_date then
1987 FND_MESSAGE.Set_Name('XTR', C_COMPLETED_BATCH);
1988 FND_MESSAGE.Set_Token('BATCH_END', to_char(p_cutoff_date));
1989 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
1990 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
1991 p_retcode := 2;
1992 else
1993 l_period_start := l_lastBED + 1;
1994 end if;
1995
1996 else -- First batch
1997
1998 l_period_start := GET_PARTY_CREATED_ON (p_company);
1999
2000 -------------------------------------------------------
2001 -- In case cutoff date is earlier than created_on
2002 -------------------------------------------------------
2003 l_period_start := least(l_period_start, p_cutoff_date);
2004
2005 end if;
2006
2007 if p_retcode = 0 then
2008
2009 GENERATE_REVAL_RATES(p_company,
2010 l_period_start,
2011 p_cutoff_date,
2012 l_prvBID,
2013 l_batch_id,
2014 l_sub_retcode);
2015 p_retcode := greatest(l_sub_retcode, p_retcode);
2016
2017 if p_retcode = 0 then
2018 GENERATE_REVAL_DETAILS(l_sub_retcode,
2019 p_company,
2020 l_period_start,
2021 p_cutoff_date,
2022 l_batch_id,
2023 l_prvBID);
2024 p_retcode := greatest(l_sub_retcode, p_retcode);
2025 end if;
2026
2027 if p_retcode <> 2 then
2028 AUTHORIZE_REVAL_EVENT(l_sub_retcode,
2029 p_company,
2030 l_batch_id,
2031 l_prvBID);
2032 p_retcode := greatest(l_sub_retcode, p_retcode);
2033
2034 end if;
2035
2036 if p_retcode = -1 then -- just in case
2037 p_retcode := 2;
2038 elsif p_retcode <> 2 then
2039 FND_MESSAGE.Set_Name('XTR', C_NEW_BATCH);
2040 FND_MESSAGE.Set_Token('BATCH', l_batch_id);
2041 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
2042 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
2043 end if;
2044
2045 end if; -- p_retcode = 0
2046
2047 end if;
2048
2049 --=================================== DEBUG ===============================================--
2050 IF xtr_risk_debug_pkg.g_Debug THEN
2051 xtr_risk_debug_pkg.dpush('CREATE_NEW_REVAL - Out Parameters');
2052 xtr_risk_debug_pkg.dlog('p_retcode ' , p_retcode);
2053 xtr_risk_debug_pkg.dpop('CREATE_NEW_REVAL - Out Parameters');
2054 END IF;
2055 --==========================================================================================--
2056
2057 END CREATE_NEW_REVAL;
2058
2059
2060 /*------------------------------------------------------------------------------*/
2061 PROCEDURE RETRO_SUBPROCESS (p_retcode OUT NOCOPY NUMBER,
2062 p_company IN VARCHAR2,
2063 p_cutoff_date IN DATE) AS
2064 /*------------------------------------------------------------------------------*
2065 * *
2066 * Bug 3378028 FAS *
2067 * Process incomplete retrospective test batches. *
2068 * Only Reval/Accrual journals will be included. *
2069 * *
2070 *------------------------------------------------------------------------------*/
2071 cursor INCOMPLETE_RETRO_BATCH is
2072 select batch_id,
2073 period_start,
2074 period_end
2075 from xtr_batches a
2076 where company_code = p_company
2077 and period_end <= p_cutoff_date
2078 and batch_type is null
2079 and nvl(upgrade_batch,'N') = 'N'
2080 and not exists (select 1
2081 from XTR_BATCH_EVENTS b
2082 where b.batch_id = a.batch_id
2083 and event_code = C_RETROET
2084 and nvl(authorized,'N') = 'Y')
2085 and not exists (select 1
2086 from XTR_BATCH_EVENTS b
2087 where b.batch_id = a.batch_id
2088 and event_code = C_ACCRUAL) -- Need not check for AUTHORISED flag
2089 order by period_end, period_start;
2090
2091 l_sub_retcode NUMBER := 0;
2092 l_batch_id NUMBER;
2093 l_batch_start DATE;
2094 l_batch_end DATE;
2095 l_prvBID NUMBER;
2096 l_prvBED DATE;
2097
2098 BEGIN
2099 --=================================== DEBUG ===============================================--
2100 IF xtr_risk_debug_pkg.g_Debug THEN
2101 xtr_risk_debug_pkg.dpush('RETRO_SUBPROCESS - In Parameters');
2102 xtr_risk_debug_pkg.dlog('p_company ' , p_company);
2103 xtr_risk_debug_pkg.dlog('p_cutoff_date ' , p_cutoff_date);
2104 xtr_risk_debug_pkg.dpop('RETRO_SUBPROCESS - In Parameters');
2105 END IF;
2106 --==========================================================================================--
2107
2108 p_retcode := 0;
2109 l_prvBID := null;
2110
2111 open INCOMPLETE_RETRO_BATCH;
2112 fetch INCOMPLETE_RETRO_BATCH into l_batch_id, l_batch_start, l_batch_end;
2113 while INCOMPLETE_RETRO_BATCH%FOUND and p_retcode <> 2 loop
2114
2115 if l_prvBID is null then
2116 GET_PREV_NORMAL_BATCH(p_company, l_batch_end, l_prvBID, l_prvBED);
2117
2118 end if;
2119
2120 if not EVENT_EXISTS(p_company, l_batch_id, NULL, C_RETROET) then
2121
2122 GENERATE_RETRO_DETAILS(l_sub_retcode,
2123 p_company,
2124 l_batch_start,
2125 l_batch_end,
2126 l_batch_id,
2127 l_prvBID);
2128 p_retcode := greatest(l_sub_retcode, p_retcode);
2129 end if;
2130
2131 if p_retcode <> 2 then
2132
2133 if RETRO_DETAILS_INCOMPLETE (p_company, l_batch_start, l_batch_end, l_batch_id) then
2134 -----------------------------------------------------------------------------
2135 -- FAS 3378028
2136 -- If start from REVAL/RETRO and end at RETRO, but the first incomplete batch
2137 -- has RETRO event but some of the details are incomplete.
2138 -----------------------------------------------------------------------------
2139 FND_MESSAGE.Set_Name('XTR', C_INCOMPLETE_RETROET);
2140 FND_MESSAGE.Set_Token('BATCH', l_batch_id);
2141 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
2142 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
2143 p_retcode := 2; -- should this be WARNING or ERROR
2144
2145 else
2146 AUTHORIZE_RETRO_EVENT(l_sub_retcode,
2147 p_company,
2148 l_batch_id,
2149 l_prvBID);
2150 p_retcode := greatest(l_sub_retcode, p_retcode);
2151 if p_retcode <> 2 then
2152 l_prvBID := l_batch_id;
2153 l_prvBED := l_batch_end;
2154 l_batch_id := null;
2155 fetch INCOMPLETE_RETRO_BATCH into l_batch_id, l_batch_start, l_batch_end;
2156 end if;
2157 end if;
2158
2159 end if;
2160 end loop;
2161 close INCOMPLETE_RETRO_BATCH;
2162
2163 --=================================== DEBUG ===============================================--
2164 IF xtr_risk_debug_pkg.g_Debug THEN
2165 xtr_risk_debug_pkg.dpush('RETRO_SUBPROCESS - Out Parameters');
2166 xtr_risk_debug_pkg.dlog('p_retcode ' , p_retcode);
2167 xtr_risk_debug_pkg.dpop('RETRO_SUBPROCESS - Out Parameters');
2168 END IF;
2169 --==========================================================================================--
2170
2171 END RETRO_SUBPROCESS;
2172
2173
2174 /*------------------------------------------------------------------------------*/
2175 PROCEDURE ACCRUAL_SUBPROCESS (p_retcode OUT NOCOPY NUMBER,
2176 p_company IN VARCHAR2,
2177 p_do_reval IN VARCHAR2,
2178 p_do_retro IN VARCHAR2, -- 3378028 FAS
2179 p_cutoff_date IN DATE) AS
2180 /*------------------------------------------------------------------------------*
2181 * *
2182 * Process incomplete accrual batches. *
2183 * Only Reval/Accrual journals will be included. *
2184 * *
2185 *------------------------------------------------------------------------------*/
2186
2187 cursor INCOMPLETE_ACCRUAL_BATCH is
2188 select batch_id,
2189 period_start,
2190 period_end
2191 from xtr_batches a
2192 where company_code = p_company
2193 and period_end <= p_cutoff_date
2194 and batch_type is null
2195 and nvl(upgrade_batch,'N') = 'N'
2196 and not exists (select 1
2197 from XTR_BATCH_EVENTS b
2198 where b.batch_id = a.batch_id
2199 and event_code = C_ACCRUAL
2200 and nvl(authorized,'N') = 'Y')
2201 order by period_end, period_start;
2202
2203 l_sub_retcode NUMBER := 0;
2204 l_batch_id NUMBER;
2205 l_batch_start DATE;
2206 l_batch_end DATE;
2207 l_prvBID NUMBER;
2208 l_prvBED DATE;
2209
2210 BEGIN
2211 --=================================== DEBUG ===============================================--
2212 IF xtr_risk_debug_pkg.g_Debug THEN
2213 xtr_risk_debug_pkg.dpush('ACCRUAL_SUBPROCESS - In Parameters');
2214 xtr_risk_debug_pkg.dlog('p_company ' , p_company);
2215 xtr_risk_debug_pkg.dlog('p_do_reval ' , p_do_reval);
2216 xtr_risk_debug_pkg.dlog('p_do_retro ' , p_do_retro);
2217 xtr_risk_debug_pkg.dlog('p_cutoff_date ' , p_cutoff_date);
2218 xtr_risk_debug_pkg.dpop('ACCRUAL_SUBPROCESS - In Parameters');
2219 END IF;
2220 --==========================================================================================--
2221
2222 p_retcode := 0;
2223 l_prvBID := null;
2224
2225 open INCOMPLETE_ACCRUAL_BATCH;
2226 fetch INCOMPLETE_ACCRUAL_BATCH into l_batch_id, l_batch_start, l_batch_end;
2227 while INCOMPLETE_ACCRUAL_BATCH%FOUND and p_retcode <> 2 loop
2228
2229 if l_prvBID is null then
2230 GET_PREV_NORMAL_BATCH(p_company, l_batch_end, l_prvBID, l_prvBED);
2231 end if;
2232
2233 if not EVENT_EXISTS(p_company, l_batch_id, NULL, C_ACCRUAL) then
2234 GENERATE_ACCRUAL_DETAILS(l_sub_retcode,
2235 p_company,
2236 p_do_reval,
2237 p_do_retro, -- 3378028 FAS
2238 l_batch_start,
2239 l_batch_end,
2240 l_batch_id,
2241 l_prvBID);
2242 p_retcode := greatest(l_sub_retcode, p_retcode);
2243 end if;
2244
2245 if p_retcode <> 2 then
2246
2247 AUTHORIZE_ACCRUAL_EVENT(l_sub_retcode,
2248 p_company,
2249 l_batch_id,
2250 l_prvBID);
2251 p_retcode := greatest(l_sub_retcode, p_retcode);
2252
2253 if p_retcode <> 2 then
2254 l_prvBID := l_batch_id;
2255 l_prvBED := l_batch_end;
2256 fetch INCOMPLETE_ACCRUAL_BATCH into l_batch_id, l_batch_start, l_batch_end;
2257 end if;
2258
2259 end if;
2260
2261 end loop;
2262
2263 close INCOMPLETE_ACCRUAL_BATCH;
2264
2265 --=================================== DEBUG ===============================================--
2266 IF xtr_risk_debug_pkg.g_Debug THEN
2267 xtr_risk_debug_pkg.dpush('ACCRUAL_SUBPROCESS - Out Parameters');
2268 xtr_risk_debug_pkg.dlog('p_retcode ' , p_retcode);
2269 xtr_risk_debug_pkg.dpop('ACCRUAL_SUBPROCESS - Out Parameters');
2270 END IF;
2271 --==========================================================================================--
2272
2273 END ACCRUAL_SUBPROCESS;
2274
2275
2276 /*------------------------------------------------------------------------------*/
2277 PROCEDURE CREATE_NEW_ACCRUAL (p_retcode OUT NOCOPY NUMBER,
2278 p_company IN VARCHAR2,
2279 p_do_reval IN VARCHAR2,
2280 p_incomplete IN VARCHAR2,
2281 p_cutoff_date IN DATE) AS
2282 /*------------------------------------------------------------------------------*
2283 * Creates a new accrual batch ONLY for company that does not use reval. *
2284 * Only Reval/Accrual journals will be included. *
2285 * *
2286 *------------------------------------------------------------------------------*/
2287
2288 l_upgrade VARCHAR2(1) := null;
2289 l_sub_retcode NUMBER := 0;
2290 l_batch_id NUMBER;
2291 l_period_start DATE;
2292 l_lastBSD DATE;
2293 l_lastBED DATE;
2294 l_prvBID NUMBER := null;
2295 l_dummy_date DATE;
2296 l_dummy_BID NUMBER;
2297 l_dummy_id NUMBER;
2298
2299 BEGIN
2300 --=================================== DEBUG ===============================================--
2301 IF xtr_risk_debug_pkg.g_Debug THEN
2302 xtr_risk_debug_pkg.dpush('CREATE_NEW_ACCRUAL - In Parameters');
2303 xtr_risk_debug_pkg.dlog('p_company ' , p_company);
2304 xtr_risk_debug_pkg.dlog('p_do_reval ' , p_do_reval);
2305 xtr_risk_debug_pkg.dlog('p_incomplete ' , p_incomplete);
2306 xtr_risk_debug_pkg.dlog('p_cutoff_date ' , p_cutoff_date);
2307 xtr_risk_debug_pkg.dpop('CREATE_NEW_ACCRUAL - In Parameters');
2308 END IF;
2309 --==========================================================================================--
2310
2311 p_retcode := 0;
2312
2313 GET_LATEST_BATCH (p_company, l_dummy_BID, l_lastBSD, l_lastBED, l_dummy_id, l_upgrade);
2314
2315 ----------------------------------------------------------------------------------
2316 -- Only perform for a new batch if cutoff date is higher than last batch end date
2317 ----------------------------------------------------------------------------------
2318 --if p_incomplete = 'N' or (p_incomplete = 'Y' and l_lastBED < p_cutoff_date) then
2319 if ((p_incomplete = 'N' and nvl(l_lastBED,p_cutoff_date) <= p_cutoff_date and
2320 not EVENT_EXISTS (p_company, null,p_cutoff_date,C_ACCRUAL))
2321 or (p_incomplete = 'Y' and l_lastBED < p_cutoff_date)) then
2322
2323 if nvl(l_upgrade,'Y') = 'N' then
2324 GET_PREV_NORMAL_BATCH(p_company,p_cutoff_date,l_prvBID,l_dummy_date);
2325 end if;
2326
2327 if l_upgrade is not null then
2328 if l_lastBED >= p_cutoff_date then
2329 FND_MESSAGE.Set_Name('XTR', C_COMPLETED_BATCH);
2330 FND_MESSAGE.Set_Token('BATCH_END', to_char(p_cutoff_date));
2331 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
2332 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
2333 p_retcode := 2;
2334 else
2335 l_period_start := l_lastBED + 1;
2336 end if;
2337
2338 else -- First batch
2339
2340 l_period_start := GET_PARTY_CREATED_ON (p_company);
2341
2342 -------------------------------------------------------
2343 -- In case cutoff date is earlier than created_on
2344 -------------------------------------------------------
2345 l_period_start := least(l_period_start, p_cutoff_date);
2346
2347 end if;
2348
2349 if p_retcode = 0 then
2350
2351 GENERATE_ACCRUAL_DETAILS(p_retcode,
2352 p_company,
2353 p_do_reval,
2354 'N', -- 3378028 FAS for p_do_reval
2355 l_period_start,
2356 p_cutoff_date,
2357 l_batch_id,
2358 l_prvBID);
2359
2360 if p_retcode <> 2 then
2361 AUTHORIZE_ACCRUAL_EVENT(l_sub_retcode,
2362 p_company,
2363 l_batch_id,
2364 l_prvBID);
2365 p_retcode := greatest(l_sub_retcode, p_retcode);
2366 end if;
2367
2368 if p_retcode <> 2 then
2369 FND_MESSAGE.Set_Name('XTR', C_NEW_BATCH);
2370 FND_MESSAGE.Set_Token('BATCH', l_batch_id);
2371 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
2372 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
2373 end if;
2374
2375 end if;
2376
2377 end if;
2378
2379 --=================================== DEBUG ===============================================--
2380 IF xtr_risk_debug_pkg.g_Debug THEN
2381 xtr_risk_debug_pkg.dpush('CREATE_NEW_ACCRUAL - Out Parameters');
2382 xtr_risk_debug_pkg.dlog('p_retcode ' , p_retcode);
2383 xtr_risk_debug_pkg.dpop('CREATE_NEW_ACCRUAL - Out Parameters');
2384 END IF;
2385 --==========================================================================================--
2386
2387 END CREATE_NEW_ACCRUAL;
2388
2389
2390 /*---------------------------------------------------------------------------*/
2391 PROCEDURE JOURNAL_SUBPROCESS (p_retcode OUT NOCOPY NUMBER,
2392 p_company IN VARCHAR2,
2393 p_cutoff_date IN DATE) AS
2394 /*------------------------------------------------------------------------------*
2395 * *
2396 * Process any incomplete journal batches. *
2397 * *
2398 *------------------------------------------------------------------------------*/
2399 cursor INCOMPLETE_JOURNAL_BATCH is
2400 select batch_id,
2401 period_start,
2402 period_end
2403 from XTR_BATCHES a
2404 where company_code = p_company
2405 and period_end <= p_cutoff_date
2406 and batch_type is null
2407 and nvl(upgrade_batch,'N') = 'N'
2408 and not exists (select 1
2409 from XTR_BATCH_EVENTS b
2410 where b.batch_id = a.batch_id
2411 and event_code = C_JOURNAL)
2412 order by period_end, period_start;
2413
2414 l_batch_id NUMBER;
2415 l_batch_start DATE;
2416 l_batch_end DATE;
2417 l_prvBID NUMBER;
2418 l_prvBED DATE;
2419 l_sub_retcode NUMBER := 0;
2420
2421 BEGIN
2422 --=================================== DEBUG ===============================================--
2423 IF xtr_risk_debug_pkg.g_Debug THEN
2424 xtr_risk_debug_pkg.dpush('JOURNAL_SUBPROCESS - In Parameters');
2425 xtr_risk_debug_pkg.dlog('p_company ' , p_company);
2426 xtr_risk_debug_pkg.dlog('p_cutoff_date ' , p_cutoff_date);
2427 xtr_risk_debug_pkg.dpop('JOURNAL_SUBPROCESS - In Parameters');
2428 END IF;
2429 --==========================================================================================--
2430
2431 p_retcode := 0;
2432 l_prvBID := null;
2433
2434 open INCOMPLETE_JOURNAL_BATCH;
2435 fetch INCOMPLETE_JOURNAL_BATCH into l_batch_id, l_batch_start, l_batch_end;
2436 while INCOMPLETE_JOURNAL_BATCH%FOUND and p_retcode <> 2 loop
2437
2438 if l_prvBID is null then
2439 GET_PREV_NORMAL_BATCH(p_company, l_batch_end, l_prvBID, l_prvBED);
2440 end if;
2441
2442 GENERATE_JOURNAL_DETAILS(l_sub_retcode,
2443 p_company,
2444 l_batch_id,
2445 l_prvBID);
2446
2447 p_retcode := greatest(l_sub_retcode, p_retcode);
2448
2449 if p_retcode <> 2 then
2450 l_prvBID := l_batch_id;
2451 l_prvBED := l_batch_end;
2452 fetch INCOMPLETE_JOURNAL_BATCH into l_batch_id, l_batch_start, l_batch_end;
2453 end if;
2454
2455 end loop;
2456
2457 close INCOMPLETE_JOURNAL_BATCH;
2458
2459 --=================================== DEBUG ===============================================--
2460 IF xtr_risk_debug_pkg.g_Debug THEN
2461 xtr_risk_debug_pkg.dpush('JOURNAL_SUBPROCESS - Out Parameters');
2462 xtr_risk_debug_pkg.dlog('p_retcode ' , p_retcode);
2463 xtr_risk_debug_pkg.dpop('JOURNAL_SUBPROCESS - Out Parameters');
2464 END IF;
2465 --==========================================================================================--
2466
2467 END JOURNAL_SUBPROCESS;
2468
2469
2470 /*----------------------------------------------------------------------------*/
2471 PROCEDURE TRANSFER_SUBPROCESS (p_retcode OUT NOCOPY NUMBER,
2472 p_company IN VARCHAR2,
2473 p_cutoff_date IN DATE,
2474 p_closed_periods IN VARCHAR2) AS
2475 /*------------------------------------------------------------------------------*
2476 * *
2477 * Process any incomplete transfer batches. *
2478 * *
2479 *------------------------------------------------------------------------------*/
2480 cursor INCOMPLETE_TRANSFER_BATCH is
2481 select batch_id,
2482 period_start,
2483 period_end
2484 from xtr_batches a
2485 where company_code = p_company
2486 and period_end <= p_cutoff_date
2487 and batch_type is null
2488 and gl_group_id is null
2489 and nvl(upgrade_batch,'N') = 'N'
2490 order by period_end, period_start;
2491
2492 l_batch_id NUMBER;
2493 l_batch_start DATE;
2494 l_batch_end DATE;
2495 l_prvBID NUMBER;
2496 l_prvBED DATE;
2497 l_sub_retcode NUMBER := 0;
2498
2499 BEGIN
2500 --=================================== DEBUG ===============================================--
2501 IF xtr_risk_debug_pkg.g_Debug THEN
2502 xtr_risk_debug_pkg.dpush('TRANSFER_SUBPROCESS - In Parameters');
2503 xtr_risk_debug_pkg.dlog('p_company ' , p_company);
2504 xtr_risk_debug_pkg.dlog('p_cutoff_date ' , p_cutoff_date);
2505 xtr_risk_debug_pkg.dlog('p_closed_periods ' , p_closed_periods);
2506 xtr_risk_debug_pkg.dpop('TRANSFER_SUBPROCESS - In Parameters');
2507 END IF;
2508 --==========================================================================================--
2509
2510 p_retcode := 0;
2511 l_prvBID := null;
2512 l_batch_id := null;
2513
2514 open INCOMPLETE_TRANSFER_BATCH;
2515 fetch INCOMPLETE_TRANSFER_BATCH into l_batch_id, l_batch_start, l_batch_end;
2516 while INCOMPLETE_TRANSFER_BATCH%FOUND and p_retcode <> 2 loop
2517 if l_prvBID is null then
2518 GET_PREV_NORMAL_BATCH(p_company, l_batch_end, l_prvBID, l_prvBED);
2519 end if;
2520
2521 TRANSFER_JOURNALS(l_sub_retcode,
2522 p_company,
2523 l_batch_id,
2524 l_prvBID,
2525 p_closed_periods);
2526 p_retcode := greatest(l_sub_retcode, p_retcode);
2527
2528 if p_retcode <> 2 then
2529 l_prvBID := l_batch_id;
2530 l_prvBED := l_batch_end;
2531 l_batch_id := null;
2532 fetch INCOMPLETE_TRANSFER_BATCH into l_batch_id, l_batch_start, l_batch_end;
2533 end if;
2534
2535 end loop;
2536 close INCOMPLETE_TRANSFER_BATCH;
2537
2538 --=================================== DEBUG ===============================================--
2539 IF xtr_risk_debug_pkg.g_Debug THEN
2540 xtr_risk_debug_pkg.dpush('TRANSFER_SUBPROCESS - Out Parameters');
2541 xtr_risk_debug_pkg.dlog('p_retcode ' , p_retcode);
2542 xtr_risk_debug_pkg.dpop('TRANSFER_SUBPROCESS - Out Parameters');
2543 END IF;
2544 --==========================================================================================--
2545
2546 END TRANSFER_SUBPROCESS;
2547
2548
2549 /*------------------------------------------------------------------------------*/
2550 PROCEDURE PROCESS_COMPANY (p_errbuf OUT NOCOPY VARCHAR2,
2551 p_retcode OUT NOCOPY NUMBER,
2552 p_company IN VARCHAR2,
2553 p_do_reval IN VARCHAR2,
2554 p_do_retro IN VARCHAR2, -- 3378028 FAS
2555 p_incomplete IN VARCHAR2,
2556 p_cutoff_date IN VARCHAR2,
2557 p_start_process IN VARCHAR2,
2558 p_end_process IN VARCHAR2,
2559 p_closed_periods IN VARCHAR2,
2560 p_multiple_acct IN VARCHAR2) AS -- Bug 4639287
2561 /*------------------------------------------------------------------------------*
2562 * *
2563 * Subprocess submitted by MAIN_PROCESS for each company. *
2564 * *
2565 *------------------------------------------------------------------------------*/
2566
2567 l_dummy NUMBER;
2568 l_retcode NUMBER := 0;
2569 l_cutoff_date DATE := FND_DATE.Canonical_To_Date(p_cutoff_date);
2570
2571 BEGIN
2572
2573 --=================================== DEBUG ===============================================--
2574 xtr_risk_debug_pkg.start_conc_prog;
2575 IF xtr_risk_debug_pkg.g_Debug THEN
2576 --------------------------------------------------------------------------------------------------
2577 -- Special call only when debugging is switched on.
2578 --------------------------------------------------------------------------------------------------
2579 l_dummy := CHK_ELIGIBLE_COMPANY(p_company,l_cutoff_date,p_do_reval,p_do_retro,p_start_process,p_end_process);
2580 --------------------------------------------------------------------------------------------------
2581 xtr_risk_debug_pkg.dpush('PROCESS_COMPANY - In Parameters');
2582 xtr_risk_debug_pkg.dlog('p_company ' , p_company);
2583 xtr_risk_debug_pkg.dlog('p_do_reval ' , p_do_reval);
2584 xtr_risk_debug_pkg.dlog('p_do_retro ' , p_do_retro);
2585 xtr_risk_debug_pkg.dlog('p_incomplete ' , p_incomplete);
2586 xtr_risk_debug_pkg.dlog('p_cutoff_date ' , p_cutoff_date);
2587 xtr_risk_debug_pkg.dlog('l_cutoff_date ' , l_cutoff_date);
2588 xtr_risk_debug_pkg.dlog('p_start_process ' , p_start_process);
2589 xtr_risk_debug_pkg.dlog('p_end_process ' , p_end_process);
2590 xtr_risk_debug_pkg.dlog('p_closed_periods ' , p_closed_periods);
2591 xtr_risk_debug_pkg.dpop('PROCESS_COMPANY - In Parameters');
2592 END IF;
2593 --==========================================================================================--
2594
2595 G_MULTIPLE_ACCT := p_multiple_acct; -- Bug 4639287
2596 p_retcode := 0;
2597 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
2598
2599 --------------------------------------------------------------------
2600 -- 1. Revaluation
2601 --------------------------------------------------------------------
2602
2603 if p_do_reval = 'Y' and p_start_process = C_PROCESS_REVAL then
2604
2605 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
2606 FND_MESSAGE.Set_Name('XTR', C_SUBPROCESS_REVAL);
2607 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
2608 FND_FILE.Put_Line (FND_FILE.LOG, '============================================');
2609
2610 if p_incomplete = 'Y' then
2611 REVAL_SUBPROCESS(l_retcode,
2612 p_company,
2613 l_cutoff_date);
2614 p_retcode := greatest(l_retcode, p_retcode);
2615 end if;
2616 if l_retcode <> 2 then
2617 CREATE_NEW_REVAL(l_retcode,
2618 p_company,
2619 p_incomplete,
2620 l_cutoff_date);
2621 p_retcode := greatest(l_retcode, p_retcode);
2622 end if;
2623 end if;
2624
2625 -------------------------------------------------------------
2626 -- 2. Retrospective - FAS 3378028
2627 -------------------------------------------------------------
2628 -- Do not need to check for p_incomplete for subprocesses. It is only needed for a NEW Reval or Accrual batch.
2629 -- For example, p_incomplete might be 'N' for Start Process of Reval. And if End Process is Retro, then even though
2630 -- p_incomplete is 'N', Retro subprocess should continue.
2631 -- (If we check for p_incomplete, then Retro will not be processed at all, which will be wrong.)
2632 --
2633 if p_do_retro = 'Y' and p_start_process <= C_PROCESS_RETROET and p_end_process >= C_PROCESS_RETROET then
2634 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
2635 FND_MESSAGE.Set_Name('XTR', C_SUBPROCESS_RETROET);
2636 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
2637 FND_FILE.Put_Line (FND_FILE.LOG, '============================================');
2638 RETRO_SUBPROCESS (l_retcode,
2639 p_company,
2640 l_cutoff_date);
2641 p_retcode := greatest(l_retcode, p_retcode);
2642 end if;
2643
2644 --------------------------------------------------------------------
2645 -- 3. Accrual
2646 --------------------------------------------------------------------
2647 if p_start_process <= C_PROCESS_ACCRUAL and p_end_process >= C_PROCESS_ACCRUAL then
2648
2649 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
2650 FND_MESSAGE.Set_Name('XTR', C_SUBPROCESS_ACCRUAL);
2651 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
2652 FND_FILE.Put_Line (FND_FILE.LOG, '============================================');
2653
2654 ACCRUAL_SUBPROCESS (l_retcode,
2655 p_company,
2656 p_do_reval,
2657 p_do_retro, -- 3378028 FAS
2658 l_cutoff_date);
2659 p_retcode := greatest(l_retcode, p_retcode);
2660
2661 if p_do_reval = 'N' and l_retcode <> 2 then
2662 CREATE_NEW_ACCRUAL (l_retcode,
2663 p_company,
2664 p_do_reval,
2665 p_incomplete,
2666 l_cutoff_date);
2667 p_retcode := greatest(l_retcode, p_retcode);
2668 end if;
2669 end if;
2670
2671 --------------------------------------------------------------------
2672 -- 4. Journals
2673 --------------------------------------------------------------------
2674 if p_start_process <= C_PROCESS_JOURNAL and p_end_process >= C_PROCESS_JOURNAL then
2675
2676 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
2677 FND_MESSAGE.Set_Name('XTR', C_SUBPROCESS_JOURNAL);
2678 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
2679 FND_FILE.Put_Line (FND_FILE.LOG, '============================================');
2680
2681 JOURNAL_SUBPROCESS (l_retcode,
2682 p_company,
2683 l_cutoff_date);
2684 p_retcode := greatest(l_retcode, p_retcode);
2685 end if;
2686
2687 --------------------------------------------------------------------
2688 -- 5. Transfer
2689 --------------------------------------------------------------------
2690 if p_end_process = C_PROCESS_TRANSFER then
2691
2692 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
2693 FND_MESSAGE.Set_Name('XTR', C_SUBPROCESS_TRANSFER);
2694 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
2695 FND_FILE.Put_Line (FND_FILE.LOG, '============================================');
2696
2697 TRANSFER_SUBPROCESS (l_retcode,
2698 p_company,
2699 l_cutoff_date,
2700 p_closed_periods);
2701 p_retcode := greatest(l_retcode, p_retcode);
2702 end if;
2703
2704 if p_retcode = 2 then
2705 p_retcode := -1;
2706 end if;
2707
2708 --=================================== DEBUG ===============================================--
2709 xtr_risk_debug_pkg.stop_conc_debug;
2710 --==========================================================================================--
2711
2712 END PROCESS_COMPANY;
2713
2714
2715 /*----------------------------------------------------------------------------*/
2716 PROCEDURE MAIN_PROCESS (p_errbuf OUT NOCOPY VARCHAR2,
2717 p_retcode OUT NOCOPY NUMBER,
2718 p_company IN VARCHAR2,
2719 p_cutoff_date IN VARCHAR2,
2720 p_dummy_date IN VARCHAR2,
2721 p_start_process IN VARCHAR2,
2722 p_end_process IN VARCHAR2,
2723 p_dummy_process IN VARCHAR2,
2724 p_closed_periods IN VARCHAR2,
2725 p_multiple_acct IN VARCHAR2) AS -- Bug 4639287
2726 /*------------------------------------------------------------------------------*
2727 * *
2728 * Main Streamline Accounting Processing *
2729 * *
2730 *------------------------------------------------------------------------------*/
2731
2732 cursor ALL_COMPANIES is
2733 select a.party_code,
2734 decode(b.PARAMETER_VALUE_CODE,'REVAL','Y','N'),
2735 c.PARAMETER_VALUE_CODE
2736 from xtr_parties_v a,
2737 xtr_company_parameters b,
2738 xtr_company_parameters c
2739 where a.party_code = nvl(p_company,a.party_code)
2740 and b.company_code = a.party_code
2741 and b.parameter_code = C_REVAL_PARAM
2742 and c.company_code = b.company_code
2743 and c.parameter_code = C_RETRO_PARAM -- 3378028 FAS
2744 order by a.party_code;
2745
2746 l_company XTR_PARTY_INFO.PARTY_CODE%TYPE;
2747 l_do_reval VARCHAR2(1);
2748 l_do_retro VARCHAR2(1);
2749 l_incomplete VARCHAR2(1);
2750
2751 l_retcode NUMBER := 0;
2752 l_sub_retcode NUMBER := 0;
2753
2754 l_request_id NUMBER := 0;
2755 l_success NUMBER := 0;
2756 l_failure NUMBER := 0;
2757
2758 l_cutoff_date DATE := FND_DATE.Canonical_To_Date(p_cutoff_date);
2759
2760 BEGIN
2761
2762 --=================================== DEBUG ===============================================--
2763 xtr_risk_debug_pkg.start_conc_prog;
2764 IF xtr_risk_debug_pkg.g_Debug THEN
2765 xtr_risk_debug_pkg.dpush('MAIN_PROCESS - In Parameters');
2766 xtr_risk_debug_pkg.dlog('p_company ' , p_company);
2767 xtr_risk_debug_pkg.dlog('p_cutoff_date ' , p_cutoff_date);
2768 xtr_risk_debug_pkg.dlog('l_cutoff_date ' , l_cutoff_date);
2769 xtr_risk_debug_pkg.dlog('p_start_process ' , p_start_process);
2770 xtr_risk_debug_pkg.dlog('p_end_process ' , p_end_process);
2771 xtr_risk_debug_pkg.dlog('p_closed_periods ' , p_closed_periods);
2772 xtr_risk_debug_pkg.dpop('MAIN_PROCESS - In Parameters');
2773 END IF;
2774 --==========================================================================================--
2775
2776 -- 3378028 FAS To check that new options are submitted.
2777 if p_start_process not in (C_PROCESS_REVAL, C_PROCESS_RETROET, C_PROCESS_ACCRUAL, C_PROCESS_JOURNAL, C_PROCESS_TRANSFER) or
2778 p_end_process not in (C_PROCESS_REVAL, C_PROCESS_RETROET, C_PROCESS_ACCRUAL, C_PROCESS_JOURNAL, C_PROCESS_TRANSFER) then
2779
2780 FND_MESSAGE.Set_Name('XTR',C_INVALID_STRM_PROCESS);
2781 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
2782 l_retcode := 2;
2783
2784 elsif l_cutoff_date > sysdate then
2785
2786 FND_MESSAGE.Set_Name('XTR',C_CUTOFF_DATE_ERROR);
2787 FND_MESSAGE.Set_Token('CUTOFF', l_cutoff_date);
2788 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
2789 l_retcode := 2;
2790
2791 else
2792
2793 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
2794
2795 open ALL_COMPANIES;
2796 fetch ALL_COMPANIES into l_company, l_do_reval, l_do_retro;
2797 while ALL_COMPANIES%FOUND loop
2798
2799 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
2800 FND_FILE.Put_Line (FND_FILE.LOG, l_company);
2801 FND_FILE.Put_Line (FND_FILE.LOG, '============================================================================');
2802
2803 l_sub_retcode := 0;
2804 l_incomplete := 'N';
2805
2806 --------------------------------------------------------------
2807 -- Check Company performs revaluation
2808 --------------------------------------------------------------
2809 --=================================== DEBUG ===============================================--
2810 IF xtr_risk_debug_pkg.g_Debug THEN
2811 xtr_risk_debug_pkg.dpush('MAIN_PROCESS Check Company perfoms revaluation');
2812 xtr_risk_debug_pkg.dlog('l_do_reval ' , l_do_reval);
2813 xtr_risk_debug_pkg.dlog('l_do_retro ' , l_do_retro);
2814 xtr_risk_debug_pkg.dlog('p_company ' , p_company);
2815 xtr_risk_debug_pkg.dlog('p_start_process ' , p_start_process);
2816 xtr_risk_debug_pkg.dlog('p_end_process ' , p_end_process);
2817 xtr_risk_debug_pkg.dpop('MAIN_PROCESS Check Company perfoms revaluation');
2818 END IF;
2819 --==========================================================================================--
2820
2821 /*****************************************************/
2822 /* Terminate immediately if Start Process is invalid */
2823 /* Display warning if End Process is invalid */
2824 /*****************************************************/
2825
2826 if l_do_reval = 'N' then
2827
2828 -- if (p_company is not null and p_start_process = C_PROCESS_REVAL) or -- 3050444 old issue 1
2829 -- (p_company is null and p_end_process = C_PROCESS_REVAL) then -- 3050444 old issue 1
2830 --
2831 -------------------------------------------------------------------------------------------------
2832 -- 3378028 FAS
2833 -- Modify 3050444 issue 1 fix. For consistency, check Start instead of End Process
2834 -------------------------------------------------------------------------------------------------
2835 -- if (p_end_process = C_PROCESS_REVAL) then -- 3050444 new issue 1
2836 --
2837 if p_start_process = C_PROCESS_REVAL then
2838 FND_MESSAGE.Set_Name('XTR', C_COMPANY_NO_REVAL);
2839 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
2840 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
2841 l_sub_retcode := 2;
2842 elsif p_start_process = C_PROCESS_RETROET then
2843 FND_MESSAGE.Set_Name('XTR', C_COMPANY_NO_RETROET);
2844 FND_FILE.Put_Line(FND_FILE.LOG, FND_MESSAGE.Get);
2845 FND_FILE.Put_Line(FND_FILE.LOG, ' ');
2846 l_sub_retcode := 2;
2847 end if;
2848
2849 else -- l_do_reval = 'Y'
2850
2851 --------------------------------------------------------------------------------
2852 -- 3378028 Check that company runs retrospective testing.
2853 -- Only failed for companies starts from Reval and not use Effectiveness Testing
2854 --------------------------------------------------------------------------------
2855 if l_do_retro = 'N' and p_start_process = C_PROCESS_RETROET then
2856 FND_MESSAGE.Set_Name('XTR',C_COMPANY_NO_RETROET);
2857 FND_FILE.Put_Line(FND_FILE.LOG, FND_MESSAGE.Get);
2858 FND_FILE.Put_Line(FND_FILE.LOG, ' ');
2859 l_sub_retcode := 2;
2860 end if;
2861
2862 end if;
2863
2864 --------------------------------------------------------------
2865 -- Check eligibility
2866 --------------------------------------------------------------
2867 if l_sub_retcode = 0 then
2868 l_sub_retcode := CHK_ELIGIBLE_COMPANY(l_company,l_cutoff_date,l_do_reval,l_do_retro,p_start_process,p_end_process);
2869
2870 if l_sub_retcode = 1 then
2871 l_incomplete := 'Y';
2872 else
2873 l_incomplete := 'N';
2874 end if;
2875 end if;
2876
2877 --=================================== DEBUG ===============================================--
2878 IF xtr_risk_debug_pkg.g_Debug THEN
2879 xtr_risk_debug_pkg.dpush('MAIN_PROCESS After Eligibility');
2880 xtr_risk_debug_pkg.dlog('l_sub_retcode ' , l_sub_retcode);
2881 xtr_risk_debug_pkg.dlog('l_incomplete ' , l_incomplete);
2882 xtr_risk_debug_pkg.dpop('MAIN_PROCESS After Eligibility');
2883 END IF;
2884 --==========================================================================================--
2885
2886 --------------------------------------------------------------
2887 -- Passed eligibility
2888 --------------------------------------------------------------
2889 if l_sub_retcode <> 2 then
2890
2891 ---------------------------------------------------------------------------------
2892 -- 3378028 FAS
2893 -- Only WARNING for companies starts from Reval and not use Effectiveness Testing
2894 ---------------------------------------------------------------------------------
2895 if l_do_reval = 'Y' and l_do_retro = 'N' then
2896 ------------------------------------------------------------------------------------------------
2897 -- NOTE: if company's reval is all authorised and has no other process to perform, and
2898 -- if p_end_process = C_PROCESS_RETROET, then it should be caught in CHK_ELIGIBLE_COMPANY
2899 -- and a child process should not be submitted.
2900 ------------------------------------------------------------------------------------------------
2901 if p_start_process = C_PROCESS_REVAL and p_end_process >= C_PROCESS_RETROET then
2902 FND_MESSAGE.Set_Name('XTR', C_COMPANY_SKIP_RETROET);
2903 FND_FILE.Put_Line(FND_FILE.LOG, FND_MESSAGE.Get);
2904 FND_FILE.Put_Line(FND_FILE.LOG, ' ');
2905 -- Do not set l_sub_retcode to 1 here.
2906 -- It is not necessary to display WARNING due to the possibilities of ALL companies
2907 -- submitted and if ALL/many do not require to run Retrospective Testing we do not
2908 -- want a list of child process submitted with a WARNING sign.
2909 end if;
2910 end if;
2911
2912 l_sub_retcode := 0;
2913 l_request_id := 0;
2914
2915 --=================================== DEBUG ===============================================--
2916 IF xtr_risk_debug_pkg.g_Debug THEN
2917 xtr_risk_debug_pkg.dpush('MAIN_PROCESS - In Parameters to XTRSTRMC');
2918 xtr_risk_debug_pkg.dlog('l_company ' , l_company);
2919 xtr_risk_debug_pkg.dlog('l_do_reval ' , l_do_reval);
2920 xtr_risk_debug_pkg.dlog('l_do_retro ' , l_do_retro);
2921 xtr_risk_debug_pkg.dlog('l_incomplete ' , l_incomplete);
2922 xtr_risk_debug_pkg.dlog('p_cutoff_date ' , p_cutoff_date);
2923 xtr_risk_debug_pkg.dlog('p_start_process ' , p_start_process);
2924 xtr_risk_debug_pkg.dlog('p_end_process ' , p_end_process);
2925 xtr_risk_debug_pkg.dlog('p_closed_periods' , p_closed_periods);
2926 xtr_risk_debug_pkg.dpop('MAIN_PROCESS - In Parameters to XTRSTRMC');
2927 END IF;
2928 --==========================================================================================--
2929
2930 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
2931 'XTR','XTRSTRMC', -- calls XTR_STREAMLINE_P.PROCESS_COMPANY
2932 '','',FALSE,
2933 l_company,
2934 l_do_reval,
2935 l_do_retro, -- 3378028 FAS
2936 l_incomplete,
2937 p_cutoff_date,
2938 p_start_process,
2939 p_end_process,
2940 p_closed_periods,
2941 p_multiple_acct, -- Bug 4639287
2942 CHR(0),'','','','','','','','','','',
2943 '','','','','','','','','','','','','','','','','','','','',
2944 '','','','','','','','','','','','','','','','','','','','',
2945 '','','','','','','','','','','','','','','','','','','','',
2946 '','','','','','','','','','','','','','','','','','','','');
2947
2948 if l_request_id <> 0 then
2949 l_success := l_success + 1;
2950 FND_MESSAGE.Set_Name('XTR', C_SUBMIT_REQUEST);
2951 FND_MESSAGE.Set_Token('REQUEST', l_request_id);
2952 FND_MESSAGE.Set_Token('DATETIME', FND_DATE.date_to_canonical(sysdate));
2953 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
2954 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
2955 else
2956 l_failure := l_failure + 1;
2957 l_sub_retcode := 2;
2958 FND_MESSAGE.Set_Name('XTR', C_SUBMIT_FAILURE);
2959 FND_MESSAGE.Set_Token('COMPANY', p_company);
2960 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
2961 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
2962 end If;
2963
2964 else -- error in eligible check
2965
2966 l_failure := l_failure + 1;
2967
2968 end if; -- l_sub_retcode <> 2
2969
2970 l_retcode := greatest(l_sub_retcode, l_retcode);
2971 fetch ALL_COMPANIES into l_company, l_do_reval, l_do_retro;
2972
2973 end loop;
2974 close ALL_COMPANIES;
2975
2976 end if; -- l_retcode = 0 and if l_cutoff_date > sysdate then
2977
2978 -----------------------------------------------------------
2979 -- Summary Log
2980 -----------------------------------------------------------
2981 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
2982 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
2983 FND_MESSAGE.Set_Name ('XTR', C_TOTAL_SUBMIT);
2984 FND_MESSAGE.Set_Token('TOTAL_SUBMIT', l_success);
2985 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
2986 FND_MESSAGE.Set_Name ('XTR', C_TOTAL_FAIL);
2987 FND_MESSAGE.Set_Token('TOTAL_FAIL', l_failure);
2988 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
2989 FND_MESSAGE.Set_Name ('XTR', C_TOTAL_COMPANY);
2990 FND_MESSAGE.Set_Token('TOTAL', l_success + l_failure);
2991 FND_FILE.Put_Line (FND_FILE.LOG, FND_MESSAGE.Get);
2992 FND_FILE.Put_Line (FND_FILE.LOG, ' ');
2993 -----------------------------------------------------------
2994
2995 if l_retcode <> 0 then
2996 p_retcode := 1; -- just warning, instead of '-1' for error
2997 else
2998 p_retcode := 0;
2999 end If;
3000
3001 --=================================== DEBUG ===============================================--
3002 xtr_risk_debug_pkg.stop_conc_debug;
3003 --==========================================================================================--
3004
3005 END MAIN_PROCESS;
3006 ---------------------------------------------------------------------------------------------
3007 end XTR_STREAMLINE_P;