[Home] [Help]
PACKAGE BODY: APPS.XTR_AUTO_RECONCILIATION
Source
1 PACKAGE BODY XTR_AUTO_RECONCILIATION AS
2 /* $Header: xtrarecb.pls 120.11.12010000.5 2009/11/04 21:40:20 srsampat ship $ */
3
4 --
5 CURSOR IMREF IS
6 select sim.verification_method,
7 are.import_reference,
8 itr.currency,
9 itr.account_number
10 from xtr_source_of_imports_v sim,
11 xtr_available_for_recon_v are,
12 xtr_import_trailer_details_v itr
13 where sim.source = itr.source
14 AND are.import_reference = itr.import_reference
15 AND are.import_reference >= NVL(G_import_reference_from, are.import_reference)
16 AND are.import_reference <= NVL(G_import_reference_to, are.import_reference);
17
18
19 CURSOR ACCT_INFO IS
20 select distinct sim.verification_method,
21 are.import_reference,
22 itr.currency,
23 itr.account_number
24 from xtr_source_of_imports_v sim,
25 xtr_available_for_recon_v are,
26 xtr_import_trailer_details_v itr,
27 XTR_PAY_REC_RECONCILIATION_V prr
28 where sim.source = itr.source
29 AND are.import_reference = itr.import_reference
30 AND prr.import_reference = are.import_reference
31 AND prr.import_reference = itr.import_reference
32 AND itr.account_number = NVL(G_acct_num, itr.account_number)
33 AND sim.source = NVL(G_source, sim.source)
34 --* bug#2464159, rravunny
35 --* changed the condition
36 AND prr.value_date between
37 least(nvl(date_from,prr.value_date),nvl(date_to,prr.value_date))
38 and
39 greatest(nvl(date_from,prr.value_date),nvl(date_to,prr.value_date));
40 -- AND to_char(prr.value_date, 'DD/MM/RRRR') >= NVL(date_from, to_char(prr.value_date, 'DD/MM/RRRR'))
41 -- AND to_char(prr.value_date, 'DD/MM/RRRR') <= NVL(date_to, to_char(prr.value_date, 'DD/MM/RRRR'));
42
43
44 /* ---------------------------------------------------------------------
45 | PRIVATE PROCEDURE |
46 | auto_reconciliation |
47 | |
48 | DESCRIPTION |
49 | Procedure to Automatically Reconcile Statements |
50 | |
51 | REQUIRES |
52 | import reference from |
53 | import_reference_to |
54 | acct_num |
55 | source |
56 | value_date_from |
57 | value_date_to |
58 | incl_rtm |
59 | |
60 | RETURNS |
61 | errbuf |
62 | retcode |
63 | |
64 | HISTORY |
65 --------------------------------------------------------------------- */
66
67
68
69 PROCEDURE AUTO_RECONCILIATION (errbuf OUT NOCOPY VARCHAR2,
70 retcode OUT NOCOPY NUMBER,
71 p_source VARCHAR2,
72 p_acct_num VARCHAR2,
73 p_value_date_from VARCHAR2,
74 p_value_date_to VARCHAR2,
75 p_import_reference_from NUMBER,
76 p_import_reference_to NUMBER,
77 p_incl_rtm VARCHAR2 ) IS
78
79 x_pass_code VARCHAR2(20);
80 x_record_in_process NUMBER;
81 x_party_name VARCHAR2(20);
82 x_serial_reference VARCHAR2(12);
83 x_debit_amount NUMBER;
84 x_credit_amount NUMBER;
85 x_reconciled_YN VARCHAR2(1);
86 x_min_rec_nos NUMBER;
87 x_max_rec_nos NUMBER;
88 x_rec_num NUMBER;
89 x_tot_recon NUMBER;
90
91 --
92 BEGIN
93 -- cep_standard.enable_debug;
94 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
95 xtr_debug_pkg.debug('>XTR_AUTO_RECONCILIATION.auto_reconciliation');
96 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || '>set parameters...');
97 END IF;
98 --
99 -- set parameters
100 --
101 G_import_reference_from := p_import_reference_from;
102 G_import_reference_to := p_import_reference_to;
103 G_source := p_source;
104 G_acct_num := p_acct_num;
105 G_value_date_from := to_date(p_value_date_from, 'YYYY/MM/DD HH24:MI:SS');
106 G_value_date_to := to_date(p_value_date_to, 'YYYY/MM/DD HH24:MI:SS');
107 date_from := to_date(G_value_date_from, 'DD/MM/RRRR');
108 date_to := to_date(G_value_date_to, 'DD/MM/RRRR');
109 G_incl_rtm := p_incl_rtm;
110
111 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
112 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || 'G_import_reference_from = ' || to_char(G_import_reference_from));
113 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || 'G_import_reference_to = ' || to_char(G_import_reference_to));
114 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || 'G_source = ' || G_source);
115 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || 'G_acct_num = ' || G_acct_num);
116 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || 'G_value_date_from = ' || G_value_date_from);
117 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || 'G_value_date_to = ' || G_value_date_to);
118 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || 'G_incl_rtm = ' || G_incl_rtm);
119 END IF;
120
121 IF (G_import_reference_to IS NOT NULL
122 OR G_import_reference_from IS NOT NULL) THEN
123 --
124 -- if user specify import reference number
125 -- as auto reconciliation drive and not account information
126 --
127 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
128 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || '>OPEN CURSOR IMREF');
129 END IF;
130
131 OPEN IMREF;
132
133 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
134 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || '>> LOOP ');
135 END IF;
136 LOOP
137 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
138 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || '>>FETCH CURSOR IMREF');
139 END IF;
140 Fetch IMREF into G_verification_method,
141 G_import_reference,
142 G_currency,
143 G_account_number;
144 EXIT WHEN IMREF%NOTFOUND;
145
146 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
147 xtr_debug_pkg.debug('>>call XTR_AUTO_RECONCILIATION.P_RECONCILE...');
148 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || '>>pass in -----------> ');
149 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || 'G_verfication_method ' || G_verification_method);
150 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || 'G_import_reference ' || to_char(G_import_reference));
151 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || 'G_currency ' || G_currency);
152 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || 'G_account_number ' || G_account_number);
153 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || 'G_incl_rtm ' || G_incl_rtm);
154 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || '----------------> end pass in parameters');
155 END IF;
156
157 XTR_AUTO_RECONCILIATION.P_RECONCILE ( G_verification_method,
158 x_pass_code,
159 G_import_reference,
160 G_currency,
161 sysdate,
162 G_account_number,
163 x_record_in_process,
164 x_party_name,
165 x_serial_reference,
166 x_debit_amount,
167 x_credit_amount,
168 x_reconciled_YN,
169 x_min_rec_nos,
170 x_max_rec_nos,
171 x_rec_num,
172 x_tot_recon,
173 G_incl_rtm);
174
175 IF (x_rec_num IS NOT NULL AND NVL(G_incl_rtm, 'Y') = 'Y') THEN
176
177 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
178 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || '>> call UPDATE_ROLL_TRANS -------->');
179 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || 'x_min_rec_nos = ' || to_char(x_min_rec_nos));
180 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || 'x_max_rec_nos = ' || to_char(x_max_rec_nos));
181 END IF;
182
183 UPDATE_ROLL_TRANS( G_verification_method,
184 x_min_rec_nos,
185 x_max_rec_nos,
186 'AUTO');
187 END IF;
188
189 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
190 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || '> END LOOP ');
191 END IF;
192 END LOOP;
193 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
194 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || '> CLOSE CURSOR IMREF ');
195 END IF;
196 CLOSE IMREF;
197 ELSE --
198 -- if user specify account information
199 -- and not by import reference #
200 --
201 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
202 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || '> OPEN CURSOR ACCT_INFO ');
203 END IF;
204 OPEN ACCT_INFO;
205
206 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
207 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || '>> LOOP ');
208 END IF;
209 LOOP
210 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
211 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || '>> FETCH CURSOR ACCT_INFO ');
212 END IF;
213 Fetch ACCT_INFO into G_verification_method,
214 G_import_reference,
215 G_currency,
216 G_account_number;
217 EXIT WHEN ACCT_INFO%NOTFOUND;
218
219 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
220 xtr_debug_pkg.debug('>>call XTR_AUTO_RECONCILIATION.P_RECONCILE...');
221 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || '>>pass in -----------> ');
222 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || 'G_verfication_method ' || G_verification_method);
223 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || 'G_import_reference ' || to_char(G_import_reference));
224 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || 'G_currency ' || G_currency);
225 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || 'G_account_number ' || G_account_number);
226 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || 'G_incl_rtm ' || G_incl_rtm);
227 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || '----------------> end pass in parameters');
228 END IF;
229
230 XTR_AUTO_RECONCILIATION.P_RECONCILE ( G_verification_method,
231 x_pass_code,
232 G_import_reference,
233 G_currency,
234 sysdate,
235 G_account_number,
236 x_record_in_process,
237 x_party_name,
238 x_serial_reference,
239 x_debit_amount,
240 x_credit_amount,
241 x_reconciled_YN,
242 x_min_rec_nos,
243 x_max_rec_nos,
244 x_rec_num,
245 x_tot_recon,
246 G_incl_rtm);
247
248 IF (x_rec_num IS NOT NULL AND NVL(G_incl_rtm, 'Y') = 'Y') THEN
249
250 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
251 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || '>> call UPDATE_ROLL_TRANS -------->');
252 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || 'x_min_rec_nos = ' || to_char(x_min_rec_nos));
253 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || 'x_max_rec_nos = ' || to_char(x_max_rec_nos));
254 END IF;
255
256 UPDATE_ROLL_TRANS( G_verification_method,
257 x_min_rec_nos,
258 x_max_rec_nos,
259 'AUTO');
260 END IF;
261
262 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
263 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || '> END LOOOP ');
264 END IF;
265 END LOOP;
266 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
267 xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || '> CLOSE CURSOR ACCT_INFO');
268 END IF;
269 CLOSE ACCT_INFO;
270
271 END IF;
272
273 EXCEPTION
274 WHEN OTHERS THEN
275 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
276 xtr_debug_pkg.debug('EXCEPTION: XTR_AUTO_RECONCILIATION.auto_reconciliation');
277 END IF;
278 RAISE;
279 END AUTO_RECONCILIATION;
280
281 -- ER 7601596 Start Added procedure REVERSE_ROLL_TRANS_RTMM and UPDATE_ROLL_TRANS_RTMM
282
283 PROCEDURE REVERSE_ROLL_TRANS_RTMM (p_verification_method VARCHAR2,
284 p_min_rec_nos NUMBER,
285 p_max_rec_nos NUMBER,
286 p_calling_method VARCHAR2,
287 p_val_date DATE) IS
288
289 --
290 l_subtype VARCHAR2(7);
291 l_date DATE;
292 l_count NUMBER;
293 l_ccy VARCHAR2(15);
294 l_start_date DATE;
295 l_deal_no NUMBER;
296 l_trans_no NUMBER;
297 l_amount NUMBER;
298 old_deal_no NUMBER;
299 --
300 l_rec_ref NUMBER;
301 l_rec_pass varchar2(2);
302 old_rec_ref NUMBER;
303 old_rec_pass varchar2(2);
304 old_trans_no NUMBER;
305 --
306 l_lowest_start DATE;
307 --
308 cursor DDA_REC_ROW is
309 select DEAL_NUMBER,TRANSACTION_NUMBER,AMOUNT,AMOUNT_DATE,
310 RECONCILED_REFERENCE,RECONCILED_PASS_CODE --reset -- AW 1/6/2000 Bug 1139396
311 from XTR_DEAL_DATE_AMOUNTS_V
312 where RECONCILED_REFERENCE between
313 to_number(nvl(p_min_rec_nos,9999999)) and
314 to_number(nvl(p_max_rec_nos,0))
315 --- and AMOUNT_TYPE = 'PRINFLW'
316 and DATE_TYPE = 'SETTLE'
317 and nvl(amount,0) <> 0
318 order by DEAL_NUMBER,TRANSACTION_NUMBER;
319 --
320 cursor S_DATE is
321 select START_DATE,CURRENCY,DEAL_SUBTYPE
322 from XTR_ROLLOVER_TRANSACTIONS_V
323 where DEAL_NUMBER = l_deal_no
324 and TRANSACTION_NUMBER = l_trans_no;
325 --
326 begin
327
328
329 open DDA_REC_ROW;
330 l_count := 0;
331 LOOP
332 fetch DDA_REC_ROW INTO l_deal_no,l_trans_no,l_amount,l_date,l_rec_ref,l_rec_pass;
333 EXIT WHEN DDA_REC_ROW%NOTFOUND;
334 l_count := l_count + 1;
335 if l_count <> 1 then
336 if old_deal_no <> l_deal_no then
337 -- Reconciled Deal has changed therefore recalc rollover records
338 -- using the old deal no, update rows where the start date >=
339 -- the lowest start date for this deal
340 --RECALC_ROLL_DETAILS(old_deal_no,l_subtype,l_lowest_start,l_ccy,old_trans_no,old_rec_ref,old_rec_pass);
341 XTR_AUTO_RECONCILIATION.recalc_roll_details(old_deal_no,
342 l_subtype,
343 l_lowest_start,
344 l_ccy,
345 old_trans_no,
346 old_rec_ref,
347 old_rec_pass);
348 l_lowest_start := NULL;
349 end if;
350 end if;
351 update XTR_DEAL_DATE_AMOUNTS_V
352 set AMOUNT = 0,
353 CASHFLOW_AMOUNT = 0,
354 DATE_TYPE = 'FORCAST' -- AW 1/6/2000 Bug 1139396
355 where DEAL_NUMBER = l_deal_no
356 and TRANSACTION_NUMBER = l_trans_no
357 and nvl(ACTION_CODE,'@#@') <>'INCRSE'
358 and DATE_TYPE = 'SETTLE'; -- AW 1/6/2000 Bug 1139396
359 --
360 update XTR_ROLLOVER_TRANSACTIONS_V
361 set PI_AMOUNT_RECEIVED = NULL,
362 SETTLE_DATE = NULL
363 where DEAL_NUMBER = l_deal_no
364 and TRANSACTION_NUMBER = l_trans_no;
365 /*
366 and SETTLE_DATE = l_date
367 and PI_AMOUNT_RECEIVED = l_amount;
368 */
369
370 -- Ensure only record the lowest start date for this deal
371 open S_DATE;
372 fetch S_DATE INTO l_start_date,l_ccy,l_subtype;
373 close S_DATE;
374 if l_lowest_start is NULL then
375 l_lowest_start := l_start_date;
376 elsif l_start_date < l_lowest_start then
377 l_lowest_start := l_start_date;
378 end if;
379 -- Store previous deal to compare with the fetch for the next deal
380 old_deal_no := l_deal_no;
381 old_trans_no := l_trans_no;
382 old_rec_ref :=l_rec_ref;
383 old_rec_pass :=l_rec_pass;
384
385 END LOOP;
386
387 -- add
388 if old_deal_no is not null then
389 -- Recalc Rollover Transactions for the Last deal fetched
390 -- ie the recalc did not occur within the LOOP
391 XTR_AUTO_RECONCILIATION.RECALC_ROLL_DETAILS(old_deal_no,
392 l_subtype,
393 l_lowest_start,
394 l_ccy,
395 old_trans_no,
396 old_rec_ref,
397 old_rec_pass);
398 end if;
399
400 close DDA_REC_ROW;
401 end;
402
403 PROCEDURE UPDATE_ROLL_TRANS_RTMM (p_verification_method VARCHAR2,
404 p_min_rec_nos NUMBER,
405 p_max_rec_nos NUMBER,
406 p_calling_method VARCHAR2,
407 p_val_date DATE) IS
408 --
409 l_min_rec_nos NUMBER;
410 l_max_rec_nos NUMBER;
411 --
412 l_subtype VARCHAR2(7);
413 l_date DATE;
414 l_settle_date DATE;
415 l_count NUMBER;
416 l_ccy VARCHAR2(15);
417 l_start_date DATE;
418 l_deal_no NUMBER;
419 l_trans_no NUMBER;
420 l_amount NUMBER;
421 old_deal_no NUMBER;
422 --
423 l_rec_ref NUMBER;
424 l_rec_pass VARCHAR2(2);
425 old_rec_ref NUMBER;
426 old_rec_pass VARCHAR2(2);
427 old_trans_no NUMBER;
428 --
429 l_lowest_start DATE;
430
431 ---------
432 CURSOR DDA_REC_ROW is
433 select dda.DEAL_NUMBER, dda.TRANSACTION_NUMBER, dda.AMOUNT, dda.AMOUNT_DATE,
434 dda.RECONCILED_REFERENCE,substr(dda.RECONCILED_PASS_CODE,2) ---reset
435 from XTR_DEAL_DATE_AMOUNTS_V dda
436 where dda.RECONCILED_REFERENCE between
437 nvl(L_MIN_REC_NOS,9999999) and nvl(L_MAX_REC_NOS,0)
438 and dda.DEAL_TYPE = 'RTMM'
439 and nvl(dda.amount,0) <>0
440 and dda.DATE_TYPE <> 'COMENCE' -- bug 3045394
441 -- and substr( nvl(dda.RECONCILED_PASS_CODE, '@'), 1, 1) = '^'
442 and ( nvl(p_calling_method, 'AUTO') = 'MANUAL' OR
443 exists(select 'anyrow'
444 from XTR_RECONCILIATION_PROCESS rp
445 where rp.VERIFICATION_METHOD = nvl(p_verification_method,rp.VERIFICATION_METHOD)
446 and rp.RECONCILED_PASS_CODE = substr(dda.RECONCILED_PASS_CODE,2)
447 and nvl(rp.PROCESS_TYPE, 'M') = 'A'))
448 order by DEAL_NUMBER,TRANSACTION_NUMBER;
449 --
450 CURSOR S_DATE is
451 select START_DATE,CURRENCY,DEAL_SUBTYPE
452 from XTR_ROLLOVER_TRANSACTIONS_V
453 where DEAL_NUMBER = l_deal_no
454 and TRANSACTION_NUMBER = l_trans_no;
455 -- Created cursor Bug 4226409
456 CURSOR SETTLE_DATE(p_rec_ref NUMBER) is
457 SELECT effective_date FROM ce_statement_lines WHERE statement_line_id IN (
458 SELECT statement_line_id FROM ce_statement_reconcils_all WHERE reference_id in
459 (SELECT settlement_summary_id FROM xtr_settlement_summary WHERE settlement_number
460 IN (SELECT settlement_number FROM xtr_deal_date_amounts WHERE reconciled_reference = p_rec_ref )));
461 /* select VALUE_DATE
462 from XTR_PAY_REC_RECONCILIATION_V
463 where RECONCILED_REFERENCE = p_rec_ref ; */
464 --
465 BEGIN
466 -- set parameters
467 --
468 l_min_rec_nos := p_min_rec_nos;
469 l_max_rec_nos := p_max_rec_nos;
470
471
472 OPEN DDA_REC_ROW;
473
474 l_count := 0;
475 LOOP
476
477 FETCH DDA_REC_ROW INTO l_deal_no,l_trans_no,l_amount,l_date,l_rec_ref,l_rec_pass;
478
479 EXIT WHEN DDA_REC_ROW%NOTFOUND;
480
481 l_count := l_count + 1;
482 IF l_count <> 1 then
483 IF old_deal_no <> l_deal_no THEN
484 -- Reconciled Deal has changed therefore recalc rollover records
485 -- using the old deal no, update rows where the start date >=
486 -- the lowest start date for this deal
487
488
489 XTR_AUTO_RECONCILIATION.RECALC_ROLL_DETAILS(old_deal_no,
490 l_subtype,
491 l_lowest_start,
492 l_ccy,
493 old_trans_no,
494 old_rec_ref,
495 old_rec_pass);
496 l_lowest_start := NULL;
497 END IF;
498 END IF;
499
500 -- Modified Bug 4226409
501 l_settle_date := p_val_date;
502
503
504 UPDATE XTR_ROLLOVER_TRANSACTIONS_V
505 SET PI_AMOUNT_RECEIVED = l_amount,
506 MATURITY_DATE = l_date,
507 SETTLE_DATE = l_settle_date
508 WHERE DEAL_NUMBER = l_deal_no
509 and TRANSACTION_NUMBER = l_trans_no;
510
511 -- Ensure only record the lowest start date for this deal
512 OPEN S_DATE;
513 FETCH S_DATE INTO l_start_date,l_ccy,l_subtype;
514 CLOSE S_DATE;
515 IF l_lowest_start is NULL then
516 l_lowest_start := l_start_date;
517 ELSIF l_start_date < l_lowest_start then
518 l_lowest_start := l_start_date;
519 END IF;
520
521 -- Store previous deal to compare with the fetch for the next deal
522 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
523 xtr_debug_pkg.debug('UPDATE_ROLL_TRANS: ' || 'old_rec_ref = ' ||to_char(l_rec_ref));
524 END IF;
525 old_deal_no := l_deal_no;
526 old_trans_no := l_trans_no;
527 old_rec_ref :=l_rec_ref;
528 old_rec_pass :=l_rec_pass;
529
530 END LOOP;
531 -- add
532 IF old_deal_no is not null then
533 -- Recalc Rollover Transactions for the Last deal fetched
534 -- ie the recalc did not occur within the LOOP
535
536
537 XTR_AUTO_RECONCILIATION.RECALC_ROLL_DETAILS( old_deal_no,
538 l_subtype,
539 l_lowest_start,
540 l_ccy,
541 old_trans_no,
542 old_rec_ref,
543 old_rec_pass);
544
545 END IF;
546 --
547 CLOSE DDA_REC_ROW;
548 EXCEPTION
549 WHEN OTHERS THEN
550 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
551 xtr_debug_pkg.debug('EXCEPTION: XTR_AUTO_RECONCILIATION.update_roll_trans');
552 END IF;
553 RAISE;
554 END UPDATE_ROLL_TRANS_RTMM;
555
556 -- ER 7601596 End
557
558 /* ---------------------------------------------------------------------
559 | PRIVATE PROCEDURE |
560 | update_roll_trans |
561 | |
562 | DESCRIPTION |
563 | Updates Rollover Transaction Table |
564 | |
565 | REQUIRES |
566 | p_min_rec_nos |
567 | p_max_rec_nos |
568 | |
569 | RETURNS |
570 | |
571 | HISTORY |
572 --------------------------------------------------------------------- */
573 PROCEDURE UPDATE_ROLL_TRANS (p_verification_method VARCHAR2,
574 p_min_rec_nos NUMBER,
575 p_max_rec_nos NUMBER,
576 p_calling_method VARCHAR2) IS
577 --
578 l_min_rec_nos NUMBER;
579 l_max_rec_nos NUMBER;
580 --
581 l_subtype VARCHAR2(7);
582 l_date DATE;
583 l_settle_date DATE;
584 l_count NUMBER;
585 l_ccy VARCHAR2(15);
586 l_start_date DATE;
587 l_deal_no NUMBER;
588 l_trans_no NUMBER;
589 l_amount NUMBER;
590 old_deal_no NUMBER;
591 --
592 l_rec_ref NUMBER;
593 l_rec_pass VARCHAR2(2);
594 old_rec_ref NUMBER;
595 old_rec_pass VARCHAR2(2);
596 old_trans_no NUMBER;
597 --
598 l_lowest_start DATE;
599
600 ---------
601 CURSOR DDA_REC_ROW is
602 select dda.DEAL_NUMBER, dda.TRANSACTION_NUMBER, dda.AMOUNT, dda.AMOUNT_DATE,
603 dda.RECONCILED_REFERENCE,substr(dda.RECONCILED_PASS_CODE,2) ---reset
604 from XTR_DEAL_DATE_AMOUNTS_V dda
605 where dda.RECONCILED_REFERENCE between
606 nvl(L_MIN_REC_NOS,9999999) and nvl(L_MAX_REC_NOS,0)
607 and dda.DEAL_TYPE = 'RTMM'
608 and nvl(dda.amount,0) <>0
609 and dda.DATE_TYPE <> 'COMENCE' -- bug 3045394
610 -- and substr( nvl(dda.RECONCILED_PASS_CODE, '@'), 1, 1) = '^'
611 and ( nvl(p_calling_method, 'AUTO') = 'MANUAL' OR
612 exists(select 'anyrow'
613 from XTR_RECONCILIATION_PROCESS rp
614 where rp.VERIFICATION_METHOD = nvl(p_verification_method,rp.VERIFICATION_METHOD)
615 and rp.RECONCILED_PASS_CODE = substr(dda.RECONCILED_PASS_CODE,2)
616 and nvl(rp.PROCESS_TYPE, 'M') = 'A'))
617 order by DEAL_NUMBER,TRANSACTION_NUMBER;
618 --
619 CURSOR S_DATE is
620 select START_DATE,CURRENCY,DEAL_SUBTYPE
621 from XTR_ROLLOVER_TRANSACTIONS_V
622 where DEAL_NUMBER = l_deal_no
623 and TRANSACTION_NUMBER = l_trans_no;
624 -- Created cursor Bug 4226409
625 CURSOR SETTLE_DATE(p_rec_ref NUMBER) is
626 select VALUE_DATE
627 from XTR_PAY_REC_RECONCILIATION_V
628 where RECONCILED_REFERENCE = p_rec_ref ;
629 --
630 BEGIN
631 -- set parameters
632 --
633 l_min_rec_nos := p_min_rec_nos;
634 l_max_rec_nos := p_max_rec_nos;
635
636 OPEN DDA_REC_ROW;
637 l_count := 0;
638 LOOP
639 FETCH DDA_REC_ROW INTO l_deal_no,l_trans_no,l_amount,l_date,l_rec_ref,l_rec_pass;
640 EXIT WHEN DDA_REC_ROW%NOTFOUND;
641 l_count := l_count + 1;
642 IF l_count <> 1 then
643 IF old_deal_no <> l_deal_no THEN
644 -- Reconciled Deal has changed therefore recalc rollover records
645 -- using the old deal no, update rows where the start date >=
646 -- the lowest start date for this deal
647 XTR_AUTO_RECONCILIATION.RECALC_ROLL_DETAILS(old_deal_no,
648 l_subtype,
649 l_lowest_start,
650 l_ccy,
651 old_trans_no,
652 old_rec_ref,
653 old_rec_pass);
654 l_lowest_start := NULL;
655 END IF;
656 END IF;
657
658 OPEN SETTLE_DATE(l_rec_ref);
659 FETCH SETTLE_DATE INTO l_settle_date;
660 CLOSE SETTLE_DATE;
661 -- Modified Bug 4226409
662 UPDATE XTR_ROLLOVER_TRANSACTIONS_V
663 SET PI_AMOUNT_RECEIVED = l_amount,
664 MATURITY_DATE = l_date,
665 SETTLE_DATE = l_settle_date
666 WHERE DEAL_NUMBER = l_deal_no
667 and TRANSACTION_NUMBER = l_trans_no;
668
669 -- Ensure only record the lowest start date for this deal
670 OPEN S_DATE;
671 FETCH S_DATE INTO l_start_date,l_ccy,l_subtype;
672 CLOSE S_DATE;
673 IF l_lowest_start is NULL then
674 l_lowest_start := l_start_date;
675 ELSIF l_start_date < l_lowest_start then
676 l_lowest_start := l_start_date;
677 END IF;
678 -- Store previous deal to compare with the fetch for the next deal
679 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
680 xtr_debug_pkg.debug('UPDATE_ROLL_TRANS: ' || 'old_rec_ref = ' ||to_char(l_rec_ref));
681 END IF;
682 old_deal_no := l_deal_no;
683 old_trans_no := l_trans_no;
684 old_rec_ref :=l_rec_ref;
685 old_rec_pass :=l_rec_pass;
686
687 END LOOP;
688 -- add
689 IF old_deal_no is not null then
690 -- Recalc Rollover Transactions for the Last deal fetched
691 -- ie the recalc did not occur within the LOOP
692 XTR_AUTO_RECONCILIATION.RECALC_ROLL_DETAILS( old_deal_no,
693 l_subtype,
694 l_lowest_start,
695 l_ccy,
696 old_trans_no,
697 old_rec_ref,
698 old_rec_pass);
699
700 END IF;
701 --
702 CLOSE DDA_REC_ROW;
703 EXCEPTION
704 WHEN OTHERS THEN
705 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
706 xtr_debug_pkg.debug('EXCEPTION: XTR_AUTO_RECONCILIATION.update_roll_trans');
707 END IF;
708 RAISE;
709 END UPDATE_ROLL_TRANS;
710
711 /* ---------------------------------------------------------------------
712 | PRIVATE PROCEDURE |
713 | RECALC_ROLL_DETAILS |
714 | |
715 | DESCRIPTION |
716 | Recalculate Rollover Transactions |
717 | |
718 | REQUIRES |
719 | p_deal_no |
720 | p_subtype |
721 | p_start_date |
722 | p_ccy |
723 | p_trans_no |
724 | p_rec_ref |
725 | p_rec_pass |
726 | |
727 | RETURNS |
728 | |
729 | HISTORY |
730 --------------------------------------------------------------------- */
731 PROCEDURE RECALC_ROLL_DETAILS(p_deal_no NUMBER,
732 p_subtype VARCHAR2,
733 p_start_date DATE,
734 p_ccy VARCHAR2,
735 p_trans_no NUMBER,
736 p_rec_ref NUMBER,
737 p_rec_pass VARCHAR2) IS
738 --
739 l_deal_no NUMBER;
740 l_subtype VARCHAR2(7);
741 l_start_date DATE;
742 l_ccy VARCHAR2(15);
743 l_trans_no NUMBER;
744 l_rec_ref NUMBER;
745 l_rec_pass VARCHAR2(2);
746 --
747 l_cparty VARCHAR2(7);
748 l_client VARCHAR2(7);
749 l_company VARCHAR2(7);
750 l_cparty_acct VARCHAR2(20);
751 l_dealer VARCHAR2(10);
752 l_product VARCHAR2(10);
753 l_portfolio VARCHAR2(7);
754 l_settle_acct VARCHAR2(20);
755 l_maturity DATE;
756 l_deal_date DATE;
757 l_limit_code VARCHAR2(7);
758 --
759 cursor DET is
760 select MATURITY_DATE,CPARTY_CODE,CLIENT_CODE,PRODUCT_TYPE,
761 PORTFOLIO_CODE,SETTLE_ACCOUNT_NO,CPARTY_REF,
762 COMPANY_CODE,DEALER_CODE,DEAL_DATE,LIMIT_CODE
763 from XTR_DEALS_V
764 where DEAL_NO = l_deal_no
765 and deal_type = 'RTMM';
766 --
767 begin
768 l_deal_no := p_deal_no;
769 l_subtype := p_subtype;
770 l_start_date := p_start_date;
771 l_ccy := p_ccy;
772 l_trans_no := p_trans_no;
773 l_rec_ref := p_rec_ref;
774 l_rec_pass := p_rec_pass;
775 -- Call the procedure to recalc details (This procedure will be a stored
776 -- procedure that both this form and pro0235 will use for recalc
777 -- of details.
778 open DET;
779 fetch DET into l_maturity,l_cparty,l_client,l_product,
780 l_portfolio,l_settle_acct,l_cparty_acct,
781 l_company,l_dealer,l_deal_date,l_limit_code;
782 close DET;
783 --
784 if l_deal_no is NOT NULL then
785 XTR_AUTO_RECONCILIATION.RECALC_DT_DETAILS
786 (l_deal_no,
787 l_deal_date,
788 l_company,
789 l_subtype,
790 l_product,
791 l_portfolio,
792 l_ccy,
793 l_maturity,
794 l_settle_acct,
795 l_cparty,
796 l_client,
797 l_cparty_acct,
798 l_dealer,
799 'N',
800 l_start_date,
801 l_trans_no,
802 l_rec_ref,
803 l_rec_pass,
804 l_limit_code);
805 END IF;
806 EXCEPTION
807 WHEN OTHERS THEN
808 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
809 xtr_debug_pkg.debug('EXCEPTION: XTR_AUTO_RECONCILIATION.recalc_roll_details');
810 END IF;
811 RAISE;
812 END RECALC_ROLL_DETAILS;
813
814 /* ---------------------------------------------------------------------
815 | PRIVATE PROCEDURE |
816 | RECALC_DT_DETAILS |
817 | |
818 | DESCRIPTION |
819 | Recalculate Details |
820 | |
821 | REQUIRES |
822 | p_deal_no |
823 | p_deal_date |
824 | p_company |
825 | p_subtype |
826 | p_product |
827 | p_portfolio |
828 | p_ccy |
829 | p_maturity |
830 | p_settle_acct |
831 | p_cparty |
832 | p_client |
833 | p_cparty_acct |
834 | p_dealer |
835 | p_least_inserted |
836 | p_ref_date |
837 | p_trans_no |
838 | p_rec_ref |
839 | p_rec_pass |
840 | p_limit_code |
841 | |
842 | RETURNS |
843 | |
844 | HISTORY |
845 --------------------------------------------------------------------- */
846 PROCEDURE RECALC_DT_DETAILS (p_deal_no NUMBER,
847 p_deal_date DATE,
848 p_company VARCHAR2,
849 p_subtype VARCHAR2,
850 p_product VARCHAR2,
851 p_portfolio VARCHAR2,
852 p_ccy VARCHAR2,
853 p_maturity DATE,
854 p_settle_acct VARCHAR2,
855 p_cparty VARCHAR2,
856 p_client VARCHAR2,
857 p_cparty_acct VARCHAR2,
858 p_dealer VARCHAR2,
859 p_least_inserted VARCHAR2,
860 p_ref_date DATE,
861 p_trans_no NUMBER,
862 p_rec_ref NUMBER,
863 p_rec_pass VARCHAR2,
864 p_limit_code VARCHAR2 ) IS
865 --
866 l_deal_no NUMBER;
867 l_deal_date DATE;
868 l_company VARCHAR2(7);
869 l_subtype VARCHAR2(7);
870 l_product VARCHAR2(10);
871 l_portfolio VARCHAR2(7);
872 l_ccy VARCHAR2(15);
873 l_maturity DATE;
874 l_settle_acct VARCHAR2(20);
875 l_cparty VARCHAR2(7);
876 l_client VARCHAR2(7);
877 l_cparty_acct VARCHAR2(20);
878 l_dealer VARCHAR2(10);
879 l_least_inserted VARCHAR2(1);
880 l_ref_date DATE;
881 l_trans_no NUMBER;
882 l_rec_ref NUMBER;
883 l_rec_pass VARCHAR2(2);
884 l_limit_code VARCHAR2(7);
885 --
886 l_comments VARCHAR2(30);
887 l_nill_date DATE;
888 l_compound VARCHAR2(7);
889 l_prv_row_exists VARCHAR2(1);
890 l_start_date DATE;
891 l_prin_decr NUMBER;
892 new_exp_bal NUMBER;
893 new_accum_int NUMBER;
894 new_balbf NUMBER;
895 new_start_date DATE;
896 rounding_fac NUMBER;
897 l_hce_rate NUMBER;
898 hce_interest NUMBER;
899 hce_settled NUMBER;
900 hce_accum_int_bf NUMBER;
901 hce_decr NUMBER;
902 hce_accum_int NUMBER;
903 hce_balbf NUMBER;
904 hce_balos NUMBER;
905 hce_princ NUMBER;
906 hce_due NUMBER;
907 l_exp_int NUMBER;
908 l_cum_int NUMBER;
909 l_prin_adj NUMBER;
910 l_no_of_days NUMBER;
911 l_year_basis NUMBER;
912 l_year_calc_type VARCHAR2(20);
913 --
914 cursor RND_YR is
915 select ROUNDING_FACTOR
916 from XTR_MASTER_CURRENCIES_V
917 where CURRENCY = l_ccy;
918 --
919 cursor GET_YEAR_CALC_TYPE is
920 select YEAR_CALC_TYPE
921 from XTR_DEALS_V
922 where DEAL_NO = l_deal_no;
923 --
924 cursor DT_HOME_RATE is
925 select nvl(a.HCE_RATE,1) HCE_RATE
926 from XTR_MASTER_CURRENCIES_V a
927 where a.CURRENCY = l_ccy;
928 --
929 cursor START_ROW is
930 select max(START_DATE)
931 from XTR_ROLLOVER_TRANSACTIONS_V
932 where DEAL_NUMBER = l_deal_no
933 and START_DATE <= l_ref_date
934 and STATUS_CODE = 'CURRENT';
935 --
936 cursor LAST_ROW is
937 select rowid
938 from XTR_ROLLOVER_TRANSACTIONS_V
939 where DEAL_NUMBER = l_deal_no
940 and START_DATE >= l_start_date
941 and STATUS_CODE = 'CURRENT'
942 order by START_DATE desc,nvl(SETTLE_DATE,MATURITY_DATE) desc,TRANSACTION_NUMBER desc;
943 --
944 last_pmt LAST_ROW%ROWTYPE;
945 --
946 cursor DT_ROW is
947 select DEAL_TYPE,START_DATE,MATURITY_DATE,NO_OF_DAYS,BALANCE_OUT_BF,
948 BALANCE_OUT,PRINCIPAL_ADJUST,INTEREST_RATE,INTEREST,
949 INTEREST_SETTLED,PRINCIPAL_ACTION,TRANSACTION_NUMBER,
950 SETTLE_DATE,ACCUM_INTEREST_BF,PI_AMOUNT_DUE,PI_AMOUNT_RECEIVED,
951 ACCUM_INTEREST,ROWID,ADJUSTED_BALANCE,COMMENTS,
952 EXPECTED_BALANCE_BF,EXPECTED_BALANCE_OUT,PRINCIPAL_AMOUNT_TYPE,ENDORSER_CODE
953 from XTR_ROLLOVER_TRANSACTIONS_V
954 where DEAL_NUMBER = l_deal_no
955 and START_DATE >= l_start_date
956 and STATUS_CODE = 'CURRENT'
957 order by START_DATE asc,nvl(SETTLE_DATE,MATURITY_DATE) asc,TRANSACTION_NUMBER asc
958 for UPDATE OF START_DATE;
959 --
960 pmt DT_ROW%ROWTYPE;
961 --
962 cursor COMP is
963 select b.INTEREST_ACTION
964 from XTR_DEALS_V a,
965 XTR_PAYMENT_SCHEDULE_V b
966 where a.DEAL_NO = l_deal_no
967 and b.PAYMENT_SCHEDULE_CODE = a.PAYMENT_SCHEDULE_CODE;
968 --
969
970 cursor cur_deal is
971 select day_count_type, rounding_type
972 from xtr_deals
973 where deal_no = l_deal_no;
974
975 l_day_count_type xtr_deals.day_count_type%type;
976 l_rounding_type xtr_deals.rounding_type%type;
977 l_first_trans_flag varchar2(1);
978 l_fwd_Adjust number;
979
980 l_exp_bal_adj_amt number; -- added for bug 3465496.
981
982 --
983 -- Bug 4226409
984 cursor cur_settle_detail is
985 select actual_settlement_date,settlement_number,deal_type,trans_mts
986 ,settlement_authorised_by,audit_indicator
987 from xtr_deal_date_amounts_v
988 where deal_number = l_deal_no
989 and transaction_number = pmt.transaction_number
990 and amount_type = 'INTSET';
991
992 l_settle_number number; -- added for bug 4226409.
993 l_settle_date date; -- added for bug 4226409.
994 l_deal_type xtr_deal_date_amounts.deal_type%TYPE; -- added for bug 4226409.
995 l_trans_mts xtr_deal_date_amounts.trans_mts%TYPE; -- added for bug 4226409.
996 l_settle_by xtr_deal_date_amounts.settlement_authorised_by%TYPE; -- added for bug 4226409.
997 l_audit_indicator xtr_deal_date_amounts.audit_indicator%TYPE; -- added for bug 4226409.
998
999
1000 begin
1001
1002 --- add
1003 l_deal_no := p_deal_no;
1004 l_subtype := p_subtype;
1005 l_ccy := p_ccy;
1006 l_trans_no := p_trans_no;
1007 l_rec_ref := p_rec_ref;
1008 l_rec_pass := p_rec_pass;
1009 l_deal_date := p_deal_date;
1010 l_company := p_company;
1011 l_product := p_product;
1012 l_portfolio := p_portfolio;
1013 l_maturity := p_maturity;
1014 l_settle_acct := p_settle_acct;
1015 l_cparty := p_cparty;
1016 l_client := p_client;
1017 l_cparty_acct := p_cparty_acct;
1018 l_dealer := p_dealer;
1019 l_least_inserted :=p_least_inserted;
1020 l_ref_date := p_ref_date;
1021 l_limit_code := p_limit_code;
1022
1023 ---
1024 open DT_HOME_RATE;
1025 fetch DT_HOME_RATE INTO l_hce_rate;
1026 close DT_HOME_RATE;
1027
1028 --
1029 open RND_YR;
1030 fetch RND_YR INTO rounding_fac;
1031 close RND_YR;
1032
1033 --
1034 open GET_YEAR_CALC_TYPE;
1035 fetch GET_YEAR_CALC_TYPE INTO l_year_calc_type;
1036 close GET_YEAR_CALC_TYPE;
1037
1038 --
1039 open COMP;
1040 fetch COMP INTO l_compound;
1041 close COMP;
1042 l_compound := nvl(l_compound,'N');
1043
1044 --
1045 l_hce_rate := nvl(l_hce_rate,1);
1046 rounding_fac := nvl(rounding_fac,2);
1047
1048 --
1049 l_comments := NULL;
1050 l_start_date := NULL;
1051 open START_ROW;
1052 fetch START_ROW INTO l_start_date;
1053 close START_ROW;
1054
1055 --
1056 if l_start_date is NULL then
1057 l_start_date := l_ref_date;
1058 l_prv_row_exists := 'N';
1059 else
1060 l_prv_row_exists := 'Y';
1061 end if;
1062
1063 --
1064 open LAST_ROW;
1065 fetch LAST_ROW INTO last_pmt;
1066 close LAST_ROW;
1067
1068 Open Cur_Deal;
1069 fetch cur_deal into l_day_count_type, l_rounding_type;
1070 close cur_deal;
1071
1072 --
1073 open DT_ROW;
1074 l_nill_date := NULL;
1075 fetch DT_ROW INTO pmt;
1076
1077 --
1078 /****** Recalc Each Row ******/
1079 WHILE DT_ROW%FOUND LOOP
1080 -- Reset balance bf and start date from previous row information except
1081 -- for the first row
1082
1083 if pmt.transaction_number = 1 then
1084 l_first_trans_flag := 'Y';
1085 else
1086 l_first_trans_flag := 'N';
1087 end if;
1088
1089 if pmt.PRINCIPAL_ACTION = 'DECRSE' then
1090 l_prin_adj := (-1) * nvl(pmt.PRINCIPAL_ADJUST,0);
1091 else
1092 l_prin_adj := nvl(pmt.PRINCIPAL_ADJUST,0);
1093 end if;
1094
1095
1096 if DT_ROW%ROWCOUNT <> 1 then
1097 pmt.EXPECTED_BALANCE_BF := new_exp_bal;
1098 pmt.ACCUM_INTEREST_BF := new_accum_int;
1099 pmt.BALANCE_OUT_BF := new_balbf;
1100 pmt.START_DATE := new_start_date;
1101 pmt.COMMENTS := l_comments;
1102
1103 elsif DT_ROW%ROWCOUNT = 1 then
1104 if l_prv_row_exists = 'Y' and pmt.SETTLE_DATE is NULL
1105 and nvl(l_least_inserted,'N') = 'Y' then
1106 pmt.MATURITY_DATE := l_ref_date;
1107 pmt.SETTLE_DATE := l_ref_date;
1108 pmt.PI_AMOUNT_DUE := 0;
1109 pmt.PI_AMOUNT_RECEIVED := 0;
1110
1111 -- AW 1/6/00 Bug 1139396
1112 XTR_CALC_P.CALC_DAYS_RUN(pmt.START_DATE,
1113 pmt.MATURITY_DATE,
1114 l_year_calc_type,
1115 l_no_of_days,
1116 l_year_basis,
1117 l_fwd_adjust,
1118 l_day_count_type,
1119 l_first_trans_flag);
1120 l_cum_int := (pmt.EXPECTED_BALANCE_BF
1121 + l_prin_adj
1122 * pmt.INTEREST_RATE / 100
1123 * l_no_of_days -- Bug 1139396 (pmt.MATURITY_DATE - pmt.START_DATE)
1124 / l_year_basis);
1125 l_cum_int := xtr_fps2_p.interest_round(l_cum_int, rounding_fac, l_rounding_type);
1126 else
1127 l_cum_int := 0;
1128 end if;
1129 end if;
1130 -- Recalc interest amount
1131 l_prin_decr := 0;
1132 pmt.ADJUSTED_BALANCE := nvl(pmt.BALANCE_OUT_BF,0) + l_prin_adj;
1133 -- AW 1/6/00 Bug 1139396
1134
1135 XTR_CALC_P.CALC_DAYS_RUN(pmt.START_DATE,
1136 nvl(pmt.SETTLE_DATE,pmt.MATURITY_DATE),
1137 l_year_calc_type,
1138 pmt.NO_OF_DAYS,
1139 l_year_basis,
1140 l_fwd_adjust,
1141 l_day_count_type,
1142 l_first_trans_flag);
1143 if pmt.ADJUSTED_BALANCE >0 then --- add
1144 pmt.INTEREST := (pmt.ADJUSTED_BALANCE * pmt.INTEREST_RATE / 100 *
1145 pmt.NO_OF_DAYS / l_year_basis);
1146 pmt.INTEREST := xtr_fps2_p.interest_round(pmt.INTEREST, rounding_fac, l_rounding_type);
1147 else
1148 pmt.INTEREST :=0;
1149 end if;
1150
1151 pmt.ACCUM_INTEREST := nvl(pmt.ACCUM_INTEREST_BF,0) + nvl(pmt.INTEREST,0);
1152
1153 if pmt.SETTLE_DATE is NOT NULL then
1154 --- add if 'W' not split for decrese on differnt day.
1155 if pmt.DEAL_TYPE <> 'RTMM' then
1156 l_prin_decr := pmt.PI_AMOUNT_RECEIVED;
1157 pmt.INTEREST_SETTLED :=0;
1158 else
1159 if nvl(pmt.PI_AMOUNT_RECEIVED,0) >= nvl(pmt.ACCUM_INTEREST,0) then
1160 l_prin_decr := nvl(pmt.PI_AMOUNT_RECEIVED,0) - nvl(pmt.ACCUM_INTEREST,0);
1161 pmt.INTEREST_SETTLED := nvl(pmt.ACCUM_INTEREST,0);
1162 pmt.ACCUM_INTEREST := 0;
1163 else
1164 l_prin_decr := 0;
1165 pmt.INTEREST_SETTLED := abs(nvl(pmt.PI_AMOUNT_RECEIVED,0));
1166 pmt.ACCUM_INTEREST := nvl(pmt.ACCUM_INTEREST,0) - nvl(pmt.PI_AMOUNT_RECEIVED,0);
1167 end if;
1168 end if;
1169 else
1170 NULL;
1171 end if;
1172
1173 if l_compound = 'C' then
1174 pmt.BALANCE_OUT := pmt.ADJUSTED_BALANCE - nvl(l_prin_decr,0) +
1175 nvl(pmt.ACCUM_INTEREST,0);
1176 pmt.ACCUM_INTEREST := 0;
1177 else
1178 pmt.BALANCE_OUT := pmt.ADJUSTED_BALANCE - nvl(l_prin_decr,0);
1179 end if;
1180
1181 pmt.EXPECTED_BALANCE_OUT := nvl(pmt.EXPECTED_BALANCE_BF,0) + l_prin_adj;
1182 -- AW 1/6/00 Bug 1139396
1183 XTR_CALC_P.CALC_DAYS_RUN(pmt.START_DATE,
1184 nvl(pmt.SETTLE_DATE,pmt.MATURITY_DATE),
1185 l_year_calc_type,
1186 l_no_of_days,
1187 l_year_basis,
1188 l_fwd_adjust,
1189 l_day_count_type,
1190 l_first_trans_flag);
1191 l_exp_int := (pmt.EXPECTED_BALANCE_OUT * pmt.INTEREST_RATE / 100
1192 * l_no_of_days / l_year_basis);
1193 l_exp_int := xtr_fps2_p.interest_round(l_exp_int, rounding_fac, l_rounding_type);
1194 if nvl(l_cum_int,0) <> 0 then
1195 l_exp_int := l_exp_int + l_cum_int;
1196 l_cum_int := 0;
1197 end if;
1198
1199 -- Replaced for bug 3465496.
1200 -- if pmt.PI_AMOUNT_DUE > l_exp_int then
1201 -- pmt.EXPECTED_BALANCE_OUT :=
1202 -- pmt.EXPECTED_BALANCE_OUT - pmt.PI_AMOUNT_DUE + l_exp_int;
1203 -- end if;
1204 -- end replacement
1205
1206 -- begin bug 3465496.
1207 -- pmt.SETTLE_DATE is updated at time of reconciliation.
1208
1209 If (pmt.SETTLE_DATE is not null) then
1210 l_exp_bal_adj_amt := nvl(pmt.PI_AMOUNT_RECEIVED,0);
1211 Else
1212 l_exp_bal_adj_amt := nvl(pmt.PI_AMOUNT_DUE,0);
1213 End If;
1214
1215 If (l_exp_bal_adj_amt > l_exp_int) then
1216 pmt.EXPECTED_BALANCE_OUT := pmt.EXPECTED_BALANCE_OUT - l_exp_bal_adj_amt + l_exp_int;
1217 End if;
1218
1219 -- end bug 3465496.
1220
1221 --add
1222 if pmt.EXPECTED_BALANCE_OUT < 0 then
1223 pmt.PI_AMOUNT_DUE := nvl(pmt.PI_AMOUNT_DUE,0) +
1224 nvl(pmt.EXPECTED_BALANCE_OUT,0);
1225 pmt.EXPECTED_BALANCE_OUT := 0;
1226 end if;
1227
1228 --add
1229 if pmt.MATURITY_DATE = l_maturity and pmt.ROWID=last_pmt.ROWID then
1230 -- Last transaction therefore make the repayment = Balance Out +
1231 -- Interest Due.
1232 pmt.PI_AMOUNT_DUE :=nvl(pmt.PI_AMOUNT_DUE,0)+nvl(pmt.EXPECTED_BALANCE_OUT,0);
1233 pmt.EXPECTED_BALANCE_OUT :=0 ;
1234 end if;
1235
1236 -- add 09/23/96
1237 if pmt.BALANCE_OUT_BF<0 then
1238 pmt.PI_AMOUNT_DUE :=0;
1239 end if;
1240 --
1241 -- Store balance carried fwd and start date for the next row
1242 new_exp_bal := nvl(pmt.EXPECTED_BALANCE_OUT,0);
1243 new_accum_int := nvl(pmt.ACCUM_INTEREST,0);
1244 new_balbf := pmt.BALANCE_OUT;
1245 new_start_date := nvl(pmt.SETTLE_DATE,pmt.MATURITY_DATE);
1246
1247 --
1248 if nvl(pmt.PI_AMOUNT_RECEIVED,0) <> 0 then
1249 l_comments := 'RECD SETTLEMENT ON PREV ROLL';
1250 else
1251 l_comments := NULL;
1252 end if;
1253 --
1254 l_prin_decr := nvl(l_prin_decr,0);
1255 pmt.INTEREST_SETTLED := nvl(pmt.INTEREST_SETTLED,0);
1256 -- Calc HCE amounts
1257 hce_decr := round(l_prin_decr / l_hce_rate,rounding_fac);
1258 hce_balbf := round(pmt.BALANCE_OUT_BF / l_hce_rate,rounding_fac);
1259 hce_interest := (pmt.INTEREST / l_hce_rate);
1260 hce_interest := xtr_fps2_p.interest_round(hce_interest, rounding_fac, l_rounding_type);
1261 hce_settled := round(pmt.INTEREST_SETTLED / l_hce_rate,rounding_fac);
1262 hce_accum_int_bf := (pmt.ACCUM_INTEREST_BF / l_hce_rate);
1263 hce_accum_int_bf := xtr_fps2_p.interest_round(hce_accum_int_bf, rounding_fac, l_rounding_type);
1264 hce_princ := round(pmt.PRINCIPAL_ADJUST / l_hce_rate,rounding_fac);
1265 hce_balos := round(pmt.BALANCE_OUT / l_hce_rate,rounding_fac);
1266 hce_accum_int := (pmt.ACCUM_INTEREST / l_hce_rate);
1267 hce_accum_int := xtr_fps2_p.interest_round(hce_accum_int, rounding_fac, l_rounding_type);
1268 hce_due := round(pmt.PI_AMOUNT_DUE / l_hce_rate,rounding_fac);
1269 --
1270 update XTR_ROLLOVER_TRANSACTIONS_V
1271 set START_DATE = pmt.START_DATE,
1272 BALANCE_OUT_BF = pmt.BALANCE_OUT_BF,
1273 BALANCE_OUT_BF_HCE = hce_balbf,
1274 ACCUM_INTEREST_BF = pmt.ACCUM_INTEREST_BF,
1275 ACCUM_INTEREST_BF_HCE = hce_accum_int_bf,
1276 PI_AMOUNT_DUE = pmt.PI_AMOUNT_DUE,
1277 PI_AMOUNT_RECEIVED = pmt.PI_AMOUNT_RECEIVED,
1278 ADJUSTED_BALANCE = pmt.ADJUSTED_BALANCE,
1279 BALANCE_OUT = pmt.BALANCE_OUT,
1280 BALANCE_OUT_HCE = hce_balos,
1281 PRINCIPAL_ADJUST_HCE = hce_princ,
1282 PRINCIPAL_ADJUST = pmt.PRINCIPAL_ADJUST,
1283 INTEREST = pmt.INTEREST,
1284 INTEREST_SETTLED = pmt.INTEREST_SETTLED,
1285 INTEREST_HCE = hce_interest,
1286 ACCUM_INTEREST = pmt.ACCUM_INTEREST,
1287 ACCUM_INTEREST_HCE = hce_accum_int,
1288 SETTLE_DATE = pmt.SETTLE_DATE,
1289 NO_OF_DAYS = pmt.NO_OF_DAYS,
1290 MATURITY_DATE = pmt.MATURITY_DATE,
1291 EXPECTED_BALANCE_BF = nvl(pmt.EXPECTED_BALANCE_BF,0),
1292 EXPECTED_BALANCE_OUT = pmt.EXPECTED_BALANCE_OUT
1293 where ROWID = pmt.ROWID;
1294 If SQL%FOUND then
1295 null;
1296 End if;
1297 --
1298 -- Update Interest Amounts
1299 update XTR_DEAL_DATE_AMOUNTS_V
1300 set AMOUNT = round(decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
1301 ,0,nvl(pmt.PI_AMOUNT_DUE,0)
1302 ,nvl(pmt.INTEREST_SETTLED,0)),
1303 rounding_fac),
1304 HCE_AMOUNT = round(decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
1305 ,0,hce_due
1306 ,nvl(hce_settled,
1307 nvl(pmt.INTEREST_SETTLED,0))),
1308 rounding_fac),
1309 AMOUNT_DATE = nvl(pmt.SETTLE_DATE,pmt.MATURITY_DATE),
1310 DATE_TYPE = decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
1311 ,0,'FORCAST','SETTLE'),
1312 TRANSACTION_RATE = pmt.INTEREST_RATE,
1313 CASHFLOW_AMOUNT = round(decode(l_subtype,
1314 'FUND',(-1),1) *
1315 decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
1316 ,0,nvl(pmt.PI_AMOUNT_DUE,0)
1317 ,nvl(pmt.INTEREST_SETTLED,0)),
1318 rounding_fac),
1319 RECONCILED_PASS_CODE = decode(substr(nvl(RECONCILED_PASS_CODE,'@'),1,1),'^',
1320 substr(RECONCILED_PASS_CODE,2),RECONCILED_PASS_CODE)
1321 where DEAL_NUMBER = l_deal_no
1322 and TRANSACTION_NUMBER = pmt.TRANSACTION_NUMBER
1323 and AMOUNT_TYPE = 'INTSET';
1324 If SQL%Found then
1325 null;
1326 End if;
1327 --
1328 -- Principal Repayment has/will ocurr ???
1329 if nvl(l_prin_decr,0)<>0 then
1330 update XTR_DEAL_DATE_AMOUNTS_V
1331 set AMOUNT = decode(nvl(AMOUNT,0),0,nvl(l_prin_decr,0),AMOUNT),
1332 HCE_AMOUNT = decode(nvl(HCE_AMOUNT,0),0,hce_decr,HCE_AMOUNT),
1333 AMOUNT_DATE = nvl(pmt.SETTLE_DATE,pmt.MATURITY_DATE),
1334 DATE_TYPE = decode(nvl(pmt.PI_AMOUNT_RECEIVED,0),0,'FORCAST','SETTLE'),
1335 TRANSACTION_RATE = pmt.INTEREST_RATE,
1336 SETTLE = 'Y', -- bug 3045426
1337 CASHFLOW_AMOUNT = decode(nvl(CASHFLOW_AMOUNT,0),0,decode(l_subtype
1338 ,'FUND',(-1),1) * l_prin_decr,CASHFLOW_AMOUNT),
1339 RECONCILED_PASS_CODE = decode(substr(nvl(RECONCILED_PASS_CODE,'@'),1,1),'^',
1340 substr(RECONCILED_PASS_CODE,2),'@',l_rec_pass,RECONCILED_PASS_CODE),
1341 RECONCILED_REFERENCE = nvl(RECONCILED_REFERENCE,l_rec_ref)
1342 where DEAL_NUMBER = l_deal_no
1343 and TRANSACTION_NUMBER = pmt.TRANSACTION_NUMBER
1344 and AMOUNT_TYPE = 'PRINFLW'
1345 and ACTION_CODE = 'DECRSE';
1346
1347 -- Start fix for Bug 4226409
1348 open cur_settle_detail;
1349 fetch cur_settle_detail into l_settle_date,l_settle_number,l_deal_type
1350 ,l_trans_mts,l_settle_by,l_audit_indicator;
1351 if(l_deal_type = 'RTMM') then
1352 close cur_settle_detail;
1353 update XTR_DEAL_DATE_AMOUNTS_V
1354 set SETTLEMENT_NUMBER = l_settle_number,
1355 ACTUAL_SETTLEMENT_DATE = l_settle_date,
1356 TRANS_MTS = l_trans_mts,
1357 SETTLEMENT_AUTHORISED_BY = l_settle_by,
1358 AUDIT_INDICATOR = l_audit_indicator
1359 where DEAL_NUMBER = l_deal_no
1360 and TRANSACTION_NUMBER = pmt.TRANSACTION_NUMBER
1361 and AMOUNT_TYPE = 'PRINFLW'
1362 and ACTION_CODE = 'DECRSE';
1363 end if;
1364 -- End Fix for Bug 4226409
1365
1366 If SQL%Found then
1367 null;
1368 End if;
1369 --
1370 end if;
1371 if nvl(pmt.PRINCIPAL_ADJUST,0) = 0 then
1372 delete from XTR_DEAL_DATE_AMOUNTS_V
1373 where DEAL_NUMBER = l_deal_no
1374 and TRANSACTION_NUMBER = pmt.TRANSACTION_NUMBER
1375 and AMOUNT_TYPE in ('PRINFLW')
1376 and ACTION_CODE = 'INCRSE';
1377 If SQL%Found then
1378 null;
1379 End if;
1380 end if;
1381 if pmt.BALANCE_OUT = 0 and pmt.ACCUM_INTEREST = 0 and l_nill_date is null then
1382 l_nill_date := nvl(pmt.SETTLE_DATE,pmt.MATURITY_DATE);-- :pmt.SETTLE_DATE
1383 end if;
1384 fetch DT_ROW INTO pmt;
1385 END LOOP;
1386 if l_nill_date is NOT NULL then
1387 delete from XTR_ROLLOVER_TRANSACTIONS_V
1388 where DEAL_NUMBER = l_deal_no
1389 and START_DATE >= l_nill_date;
1390 If SQL%Found then
1391 null;
1392 End if;
1393 end if;
1394
1395 -- Update BALOUT Amounts
1396 UPDATE XTR_DEAL_DATE_AMOUNTS_V
1397 set AMOUNT = nvl(pmt.BALANCE_OUT,0),
1398 HCE_AMOUNT = hce_balos
1399 where DEAL_NUMBER = l_deal_no
1400 and DEAL_TYPE='RTMM'
1401 and AMOUNT_TYPE = 'BALOUT';
1402 If SQL%found then
1403 null;
1404 End if;
1405
1406 if SQL%NOTFOUND then
1407 -- Add 1 more row to DDA for Balout
1408
1409 insert into XTR_DEAL_DATE_AMOUNTS_V
1410 (deal_type,amount_type,date_type,
1411 deal_number,transaction_number,transaction_date,currency,
1412 amount,hce_amount,amount_date,transaction_rate,
1413 cashflow_amount,company_code,account_no,action_code,
1414 cparty_account_no,deal_subtype,product_type,
1415 portfolio_code,status_code,cparty_code,dealer_code,
1416 settle,client_code,limit_code,limit_party)
1417 values ('RTMM','BALOUT','COMENCE',
1418 l_deal_no,pmt.TRANSACTION_NUMBER,
1419 l_deal_date,l_ccy,nvl(pmt.BALANCE_OUT,0),
1420 nvl(hce_balos,0),l_maturity,pmt.INTEREST_RATE,0,
1421 l_company,l_settle_acct,NULL,
1422 l_cparty_acct,l_subtype,l_product,
1423 l_portfolio,'CURRENT',l_cparty,
1424 l_dealer,'N',l_client,nvl(l_limit_code,'NILL'),l_cparty);
1425 end if;
1426 if DT_ROW%ISOPEN then
1427 close DT_ROW;
1428 end if;
1429 EXCEPTION
1430 WHEN OTHERS THEN
1431 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1432 xtr_debug_pkg.debug('EXCEPTION: XTR_AUTO_RECONCILIATION.recalc_dt_details');
1433 END IF;
1434 RAISE;
1435 END RECALC_DT_DETAILS;
1436 /* ---------------------------------------------------------------------
1437 | PRIVATE PROCEDURE |
1438 | P_RECONCILE |
1439 | |
1440 | DESCRIPTION |
1441 | Reconciliation |
1442 | |
1443 | REQUIRES |
1444 | p_verification_method |
1445 | p_pass_code |
1446 | p_import_reference |
1447 | p_currency |
1448 | p_cgu$sysdate |
1449 | p_account_number |
1450 | p_record_in_process |
1451 | p_party_name |
1452 | p_serial_reference |
1453 | p_debit_amount |
1454 | p_credit_amount |
1455 | p_reconciled_yn |
1456 | p_min_rec_nos |
1457 | p_max_rec_nos |
1458 | p_rec_nos |
1459 | p_tot_recon |
1460 | p_incl_rtm |
1461 | |
1462 | RETURNS |
1463 | p_pass_code |
1464 | p_record_in_process |
1465 | p_party_name |
1466 | p_serial_reference |
1467 | p_debit_amount |
1468 | p_credit_amount |
1469 | p_reconciled_yn |
1470 | p_min_reco_nos |
1471 | p_max_rec_nos |
1472 | p_rec_nos |
1473 | p_tot_recon |
1474 | |
1475 | HISTORY |
1476 --------------------------------------------------------------------- */
1477 PROCEDURE P_RECONCILE(
1478 P_VERIFICATION_METHOD varchar2,
1479 P_PASS_CODE IN OUT NOCOPY varchar2,
1480 P_IMPORT_REFERENCE NUMBER,
1481 P_CURRENCY varchar2,
1482 P_CGU$SYSDATE date,
1483 P_ACCOUNT_NUMBER VARCHAR2,
1484 P_RECORD_IN_PROCESS IN OUT NOCOPY number,
1485 P_PARTY_NAME IN OUT NOCOPY varchar2,
1486 P_SERIAL_REFERENCE IN OUT NOCOPY varchar2,
1487 P_DEBIT_AMOUNT IN OUT NOCOPY number,
1488 P_CREDIT_AMOUNT IN OUT NOCOPY NUMBER,
1489 P_RECONCILED_YN IN OUT NOCOPY varchar2,
1490 P_MIN_REC_NOS IN OUT NOCOPY number,
1491 P_MAX_REC_NOS IN OUT NOCOPY number,
1492 P_REC_NOS IN OUT NOCOPY number,
1493 P_tot_recon IN OUT NOCOPY number,
1494 P_INCL_RTM IN varchar2) IS
1495 --
1496 l_reset_amt NUMBER;l_num_recs NUMBER;l_found VARCHAR2(1);
1497 l_tot_record NUMBER;
1498 l_days NUMBER;l_one_date DATE;l_s_date DATE;l_sum_amt NUMBER;
1499 l_count NUMBER;l_bk_acct VARCHAR2(1);l_party VARCHAR2(1);
1500 l_serial_ref VARCHAR2(1);l_deal_no VARCHAR2(1);l_amount VARCHAR2(1);
1501 l_date VARCHAR2(1);l_date_range VARCHAR2(1);l_sum_date VARCHAR2(1);
1502 l_sum_range VARCHAR2(1);l_deal_type VARCHAR2(7);l_subtype VARCHAR2(7);
1503 l_product VARCHAR2(10);l_portfolio VARCHAR2(7); l_rec_nos number;
1504 cursor PASSES is
1505 select RECONCILED_PASS_CODE,nvl(DAYS_ADJUSTMENT,0)
1506 from XTR_RECONCILIATION_PROCESS
1507 where VERIFICATION_METHOD = P_VERIFICATION_METHOD
1508 -- and PROCESS_TYPE = 'A'
1509 order by SEQUENCE_ORDER asc;
1510 cursor PASS_DETAILS is
1511 select RECONCILE_ON_COLUMN,RECONCILE_DETAIL
1512 from XTR_RECONCILIATION_PASSES
1513 where RECONCILED_PASS_CODE = P_PASS_CODE;
1514 p_det PASS_DETAILS%ROWTYPE;
1515 cursor REC is
1516 select *
1517 from XTR_PAY_REC_RECONCILIATION
1518 where IMPORT_REFERENCE = P_IMPORT_REFERENCE
1519 and RECONCILED_PASS_CODE is NULL
1520 and RECONCILED_REFERENCE is NULL
1521 for UPDATE OF IMPORT_REFERENCE;
1522 --
1523 rec_det REC%ROWTYPE;
1524 v_netoff_number NUMBER;
1525 --
1526 cursor REC_NUM is
1527 select XTR_DEAL_DATE_AMOUNTS_S.NEXTVAL
1528 from DUAL;
1529 --
1530
1531 Cursor C1(p_netoff_number number) is
1532 select net_id from xtr_settlement_summary
1533 where settlement_number =
1534 (select settlement_number
1535 from xtr_deal_date_amounts
1536 where netoff_number = p_netoff_number
1537 and rownum < 2);
1538
1539 Cursor C2(p_reconciled_reference number) is
1540 select settlement_number
1541 from xtr_deal_date_amounts
1542 where reconciled_reference = p_reconciled_reference;
1543
1544 /************* CE Reconc project ******************/
1545 /* modified the cursor to differentiate non-netted records from netted records. incase of netted records, we need to
1546 look at the netted amount. */
1547 cursor DDA is
1548 select sum(round(CASHFLOW_AMOUNT,2)),count(*),AMOUNT_DATE, NETOFF_NUMBER
1549 from XTR_DEAL_DATE_AMOUNTS_V
1550 where ((to_char(DEAL_NUMBER) = ltrim(rtrim(rec_det.PARTICULARS))
1551 and nvl(l_deal_no,'N') = 'Y')
1552 or (l_deal_no is NULL )) --- modify
1553 and ((AMOUNT_DATE between (rec_det.VALUE_DATE - l_days) and
1554 (rec_det.VALUE_DATE + l_days) and
1555 nvl(l_date,'N') = 'N') or
1556 (AMOUNT_DATE = rec_det.VALUE_DATE and nvl(l_date,'N')='Y'))
1557 and CURRENCY =P_CURRENCY
1558 and AMOUNT_DATE <= P_CGU$SYSDATE
1559 and RECONCILED_REFERENCE is NULL
1560 and RECONCILED_PASS_CODE is NULL
1561 and NETOFF_NUMBER is NULL
1562 and ((ACCOUNT_NO = P_ACCOUNT_NUMBER and nvl(l_bk_acct,'N') = 'Y') or
1563 (nvl(l_bk_acct,'%') = '%'))
1564 and ((CASHFLOW_AMOUNT < 0 and rec_det.DEBIT_AMOUNT is NOT NULL)
1565 and ((abs(CASHFLOW_AMOUNT) = rec_det.DEBIT_AMOUNT
1566 and nvl(l_amount,'N') = 'Y') or (nvl(l_amount,'N') = 'N'))
1567 or (nvl(rec_det.DEBIT_AMOUNT,0) = 0))
1568 and ((CASHFLOW_AMOUNT > 0 and rec_det.CREDIT_AMOUNT is NOT NULL)
1569 and ((abs(CASHFLOW_AMOUNT) = rec_det.CREDIT_AMOUNT
1570 and nvl(l_amount,'N')='Y') or (nvl(l_amount,'N') = 'N'))
1571 or (nvl(rec_det.CREDIT_AMOUNT,0) = 0))
1572 and ((CPARTY_CODE = substr(rec_det.PARTY_NAME,1,7)
1573 and nvl(l_party,'N') = 'Y') or (l_party is NULL))
1574 and ((SERIAL_REFERENCE = rtrim(rec_det.SERIAL_REFERENCE)
1575 and nvl(l_serial_ref,'N') = 'Y') or (l_serial_ref is NULL))
1576 and DEAL_TYPE like nvl(l_deal_type ,'%')
1577 and DEAL_SUBTYPE like nvl(l_subtype,'%')
1578 and PRODUCT_TYPE like nvl(l_product,'%')
1579 and PORTFOLIO_CODE like nvl(l_portfolio,'%')
1580 and ((date_type <>'FORCAST' and NVL(P_INCL_RTM, 'N')='N') or NVL(P_INCL_RTM, 'N') ='Y')
1581 group by AMOUNT_DATE, NETOFF_NUMBER
1582 union all
1583 select sum(round(CASHFLOW_AMOUNT,2)),count(distinct NETOFF_NUMBER),AMOUNT_DATE, NETOFF_NUMBER
1584 from XTR_DEAL_DATE_AMOUNTS_V
1585 where
1586 /*
1587 ((to_char(DEAL_NUMBER) = ltrim(rtrim(rec_det.PARTICULARS))
1588 and nvl(l_deal_no,'N') = 'Y')
1589 or (l_deal_no is NULL )) --- modify
1590 and
1591 */
1592 ((AMOUNT_DATE between (rec_det.VALUE_DATE - l_days) and
1593 (rec_det.VALUE_DATE + l_days) and
1594 nvl(l_date,'N') = 'N') or
1595 (AMOUNT_DATE = rec_det.VALUE_DATE and nvl(l_date,'N')='Y'))
1596 and CURRENCY =P_CURRENCY
1597 and AMOUNT_DATE <= P_CGU$SYSDATE
1598 and RECONCILED_REFERENCE is NULL
1599 and RECONCILED_PASS_CODE is NULL
1600 and NETOFF_NUMBER is NOT NULL
1601 and ((ACCOUNT_NO = P_ACCOUNT_NUMBER and nvl(l_bk_acct,'N') = 'Y') or
1602 (nvl(l_bk_acct,'%') = '%'))
1603 and ((CPARTY_CODE = substr(rec_det.PARTY_NAME,1,7)
1604 and nvl(l_party,'N') = 'Y') or (l_party is NULL))
1605 and ((SERIAL_REFERENCE = rtrim(rec_det.SERIAL_REFERENCE)
1606 and nvl(l_serial_ref,'N') = 'Y') or (l_serial_ref is NULL))
1607 and DEAL_TYPE like nvl(l_deal_type ,'%')
1608 and DEAL_SUBTYPE like nvl(l_subtype,'%')
1609 and PRODUCT_TYPE like nvl(l_product,'%')
1610 and PORTFOLIO_CODE like nvl(l_portfolio,'%')
1611 and ((date_type <>'FORCAST' and NVL(P_INCL_RTM, 'N')='N') or NVL(P_INCL_RTM, 'N') ='Y')
1612 group by AMOUNT_DATE, NETOFF_NUMBER
1613 having ((sum(round(CASHFLOW_AMOUNT,2)) < 0 and rec_det.DEBIT_AMOUNT is NOT NULL)
1614 and ((abs(sum(round(CASHFLOW_AMOUNT,2))) = rec_det.DEBIT_AMOUNT
1615 and nvl(l_amount,'N') = 'Y') or (nvl(l_amount,'N') = 'N'))
1616 or (nvl(rec_det.DEBIT_AMOUNT,0) = 0))
1617 and ((sum(round(CASHFLOW_AMOUNT,2)) > 0 and rec_det.CREDIT_AMOUNT is NOT NULL)
1618 and ((abs(sum(round(CASHFLOW_AMOUNT,2))) = rec_det.CREDIT_AMOUNT
1619 and nvl(l_amount,'N')='Y') or (nvl(l_amount,'N') = 'N'))
1620 or (nvl(rec_det.CREDIT_AMOUNT,0) = 0))
1621 ;
1622
1623 /***************** CE Recon project **********************/
1624 /* non-netted records are summed up, while netted records are summed by the netoff_number */
1625 cursor DDA_SUM_DATE is
1626 select AMOUNT_DATE,sum(round(CASHFLOW_AMOUNT,2)),count(distinct nvl(NETOFF_NUMBER, -1)), NETOFF_NUMBER
1627 from XTR_DEAL_DATE_AMOUNTS_V
1628 where ((to_char(DEAL_NUMBER) = ltrim(rtrim(rec_det.PARTICULARS))
1629 and nvl(l_deal_no,'N') = 'Y')
1630 or (l_deal_no is NULL and date_type <> 'FORCAST'))
1631 and (((AMOUNT_DATE between (rec_det.VALUE_DATE - l_days) and
1632 (rec_det.VALUE_DATE + l_days) and
1633 nvl(l_date,'N') = 'N') or
1634 (AMOUNT_DATE = rec_det.VALUE_DATE and nvl(l_date,'N')='Y'))
1635 and nvl(l_sum_date,'N') = 'Y')
1636 and AMOUNT_DATE <= P_CGU$SYSDATE
1637 and CURRENCY =P_CURRENCY
1638 and RECONCILED_REFERENCE is NULL
1639 and RECONCILED_PASS_CODE is NULL
1640 and ((ACCOUNT_NO = P_ACCOUNT_NUMBER and nvl(l_bk_acct,'N') = 'Y') or
1641 (nvl(l_bk_acct,'%') = '%'))
1642 and ((CPARTY_CODE = substr(rec_det.PARTY_NAME,1,7)
1643 and nvl(l_party,'N') = 'Y') or (l_party is NULL))
1644 and ((SERIAL_REFERENCE = rtrim(rec_det.SERIAL_REFERENCE)
1645 and nvl(l_serial_ref,'N') = 'Y') or (l_serial_ref is NULL))
1646 and DEAL_TYPE like nvl(l_deal_type ,'%')
1647 and DEAL_SUBTYPE like nvl(l_subtype,'%')
1648 and PRODUCT_TYPE like nvl(l_product,'%')
1649 and PORTFOLIO_CODE like nvl(l_portfolio,'%')
1650 and ((date_type <>'FORCAST' and NVL(P_INCL_RTM, 'N')='N') or NVL(P_INCL_RTM, 'N')='Y')
1651 group by AMOUNT_DATE, NETOFF_NUMBER
1652 ;
1653 --
1654 /************* CE Recon proejct *************/
1655 /* netted records and non-netted records are dealt with separately*/
1656 cursor DDA_SUM_RANGE is
1657 select sum(round(CASHFLOW_AMOUNT,2)),count(distinct nvl(netoff_number, -1)), Netoff_number
1658 from XTR_DEAL_DATE_AMOUNTS_V
1659 where ((to_char(DEAL_NUMBER) = ltrim(rtrim(rec_det.PARTICULARS))
1660 and nvl(l_deal_no,'N') = 'Y')
1661 or (l_deal_no is NULL and date_type<>'FORCAST'))
1662 and (((AMOUNT_DATE between (rec_det.VALUE_DATE - l_days) and
1663 (rec_det.VALUE_DATE + l_days) and
1664 nvl(l_date,'N') = 'N') or
1665 (AMOUNT_DATE = rec_det.VALUE_DATE and nvl(l_date,'N')='Y')) and
1666 nvl(l_sum_range,'N') = 'Y')
1667 and AMOUNT_DATE <= P_CGU$SYSDATE
1668 and CURRENCY =P_CURRENCY and RECONCILED_REFERENCE is NULL
1669 and RECONCILED_PASS_CODE is NULL
1670 and ((ACCOUNT_NO = P_ACCOUNT_NUMBER and nvl(l_bk_acct,'N') = 'Y') or
1671 (nvl(l_bk_acct,'%') = '%'))
1672 and ((CPARTY_CODE = substr(rec_det.PARTY_NAME,1,7)
1673 and nvl(l_party,'N') = 'Y') or (l_party is NULL))
1674 and ((SERIAL_REFERENCE = rtrim(rec_det.SERIAL_REFERENCE)
1675 and nvl(l_serial_ref,'N') = 'Y') or (l_serial_ref is NULL))
1676 and DEAL_TYPE like nvl(l_deal_type ,'%')
1677 and DEAL_SUBTYPE like nvl(l_subtype,'%')
1678 and PRODUCT_TYPE like nvl(l_product,'%')
1679 and PORTFOLIO_CODE like nvl(l_portfolio,'%')
1680 and ((date_type <>'FORCAST' and NVL(P_INCL_RTM, 'N')='N') or NVL(P_INCL_RTM, 'N')='Y')
1681 group by NETOFF_NUMBER
1682 ;
1683 CURSOR c_file_dir IS
1684 SELECT SUBSTR(value,1,DECODE(INSTR(value,','),0,LENGTH(value),INSTR(value,',')-1) )
1685 FROM v$parameter
1686 WHERE name = 'utl_file_dir';
1687 l_file utl_file.file_type;
1688 l_dirname VARCHAR2(1000);
1689 --
1690 begin
1691
1692 OPEN c_file_dir;
1693 FETCH c_file_dir INTO l_dirname;
1694 CLOSE c_file_dir;
1695 l_file := utl_file.fopen(l_dirname,'xtraurec.log','w');
1696
1697 --
1698 -- set parameteres
1699 --
1700 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1701 xtr_debug_pkg.debug('>>>XTR_AUTO_RECONCILIATION.p_reconcile ');
1702 xtr_debug_pkg.debug('P_RECONCILE: ' || '>>> set parameteres ---------------->');
1703 xtr_debug_pkg.debug('P_RECONCILE: ' || 'p_verification_method = ' || p_verification_method);
1704 xtr_debug_pkg.debug('P_RECONCILE: ' || 'p_pass_code = ' || p_pass_code);
1705 xtr_debug_pkg.debug('P_RECONCILE: ' || 'p_import_reference = ' || to_char(p_import_reference));
1706 xtr_debug_pkg.debug('P_RECONCILE: ' || 'p_currency = ' || p_currency);
1707 xtr_debug_pkg.debug('P_RECONCILE: ' || 'p_account_number = ' || p_account_number);
1708 xtr_debug_pkg.debug('P_RECONCILE: ' || 'p_incl_rtm = '|| p_incl_rtm);
1709 END IF;
1710
1711 P_MIN_REC_NOS := NULL;
1712 P_MAX_REC_NOS := NULL;
1713 P_REC_NOS := NULL;
1714
1715 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1716 xtr_debug_pkg.debug('P_RECONCILE: ' || '>>> OPEN CURSOR PASSES ');
1717 END IF;
1718 open PASSES;
1719 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1720 xtr_debug_pkg.debug('P_RECONCILE: ' || '>>>> LOOP --2 ');
1721 END IF;
1722 LOOP -- 2
1723
1724 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1725 xtr_debug_pkg.debug('P_RECONCILE: ' || '>>>> FETCH PASSES into P_PASS_CODE, l_days');
1726 END IF;
1727 fetch PASSES INTO P_PASS_CODE,l_days;
1728
1729 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1730 xtr_debug_pkg.debug('P_RECONCILE: ' || 'P_PASS_CODE = ' || P_PASS_CODE);
1731 xtr_debug_pkg.debug('P_RECONCILE: ' || 'l_days = ' || to_char(l_days));
1732 END IF;
1733
1734 EXIT WHEN PASSES%NOTFOUND;
1735 l_bk_acct := NULL;l_party := NULL;l_deal_no := NULL;l_serial_ref := NULL;
1736 l_amount := NULL;l_sum_date := NULL;l_sum_range := NULL;l_date :=NULL;
1737 l_deal_type := NULL;
1738 l_subtype := NULL;l_product := NULL;l_portfolio := NULL; v_netoff_number := NULL;
1739 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1740 xtr_debug_pkg.debug('P_RECONCILE: ' || '>>>> OPEN CURSOR PASS_DETAILS ');
1741 END IF;
1742 open PASS_DETAILS;
1743 LOOP -- 3 (in 2)
1744 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1745 xtr_debug_pkg.debug('P_RECONCILE: ' || '>>>>> LOOP --3 ');
1746 xtr_debug_pkg.debug('P_RECONCILE: ' || '>>>>> FETCH PASS_DETAILS into p_det');
1747 END IF;
1748 fetch PASS_DETAILS INTO p_det;
1749
1750 --xtr_debug_pkg.debug('p_det.RECONCILE_ON_COLUMN = ' || REOCNCILE_ON_COLUMN);
1751 EXIT WHEN PASS_DETAILS%NOTFOUND;
1752 if p_det.RECONCILE_ON_COLUMN = 'BANK ACCT' then
1753 if p_det.RECONCILE_DETAIL = 'MATCH' then
1754 l_bk_acct := 'Y'; -----FOR BUG 6664952--l_bk_acct := '%';
1755 else
1756 l_bk_acct := '%'; -----FOR BUG 6664952 --l_bk_acct := 'Y';
1757 end if;
1758 elsif p_det.RECONCILE_ON_COLUMN = 'PARTY' then
1759 l_party := 'Y';
1760 elsif p_det.RECONCILE_ON_COLUMN = 'SERIAL NUM' then
1761 l_serial_ref := 'Y';
1762 elsif p_det.RECONCILE_ON_COLUMN = 'DEAL NUM' then
1763 l_deal_no := 'Y';
1764 elsif p_det.RECONCILE_ON_COLUMN = 'AMOUNT' then
1765 l_amount := 'Y';
1766 if p_det.RECONCILE_DETAIL = 'SUM DATE' then
1767 l_sum_date := 'Y';
1768 elsif p_det.RECONCILE_DETAIL = 'SUM RANGE' then
1769 l_sum_range := 'Y';
1770 end if;
1771 elsif p_det.RECONCILE_ON_COLUMN = 'DATE' then
1772 l_date := 'Y';
1773 elsif p_det.RECONCILE_ON_COLUMN = 'DEAL TYPE' then
1774 l_deal_type := substr(p_det.RECONCILE_DETAIL,1,7);
1775 elsif p_det.RECONCILE_ON_COLUMN = 'SUBTYPE' then
1776 l_subtype := substr(p_det.RECONCILE_DETAIL,1,7);
1777 elsif p_det.RECONCILE_ON_COLUMN = 'PRODUCT' then
1778 l_product := substr(p_det.RECONCILE_DETAIL,1,10);
1779 elsif p_det.RECONCILE_ON_COLUMN = 'SUBTYPE' then
1780 l_portfolio := substr(p_det.RECONCILE_DETAIL,1,7);
1781 end if;
1782 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1783 xtr_debug_pkg.debug('P_RECONCILE: ' || 'values----------------------');
1784 xtr_debug_pkg.debug('P_RECONCILE: ' || 'l_party = ' || l_party);
1785 xtr_debug_pkg.debug('P_RECONCILE: ' || 'l_serial_ref = ' || l_serial_ref);
1786 xtr_debug_pkg.debug('P_RECONCILE: ' || 'l_deal_no = ' || l_deal_no);
1787 xtr_debug_pkg.debug('P_RECONCILE: ' || 'l_amount = ' || l_amount);
1788 xtr_debug_pkg.debug('P_RECONCILE: ' || 'l_sum_date = ' || l_sum_date);
1789 xtr_debug_pkg.debug('P_RECONCILE: ' || 'l_sum_range = ' || l_sum_range);
1790 xtr_debug_pkg.debug('P_RECONCILE: ' || 'l_date = ' || l_date);
1791 xtr_debug_pkg.debug('P_RECONCILE: ' || 'l_deal_type = ' || l_deal_type);
1792 xtr_debug_pkg.debug('P_RECONCILE: ' || 'l_subtype = ' || l_subtype);
1793 xtr_debug_pkg.debug('P_RECONCILE: ' || 'l_product = ' || l_product);
1794 xtr_debug_pkg.debug('P_RECONCILE: ' || 'l_portfolio = ' || l_portfolio);
1795 xtr_debug_pkg.debug('P_RECONCILE: ' || '---------------------------');
1796 xtr_debug_pkg.debug('P_RECONCILE: ' || '>>>> END LOOP --3 ');
1797 END IF;
1798 END LOOP; -- 3 (in 2)
1799 close PASS_DETAILS;
1800 if l_days is NULL then
1801 l_days := 0;
1802 end if;
1803
1804 open REC;
1805
1806 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1807 xtr_debug_pkg.debug('P_RECONCILE: ' || '>>>>> LOOP --4 ');
1808 END IF;
1809 LOOP -- 4 (in 2)
1810 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1811 xtr_debug_pkg.debug('P_RECONCILE: ' || '>>>>> FETCH REC into rec_det');
1812 END IF;
1813
1814 fetch REC INTO rec_det;
1815 EXIT WHEN REC%NOTFOUND;
1816 P_RECORD_IN_PROCESS := nvl(P_RECORD_IN_PROCESS,0) + 1;
1817 P_PARTY_NAME := rec_det.PARTICULARS;
1818 P_SERIAL_REFERENCE := rec_det.SERIAL_REFERENCE;
1819 P_DEBIT_AMOUNT := rec_det.DEBIT_AMOUNT;
1820 P_CREDIT_AMOUNT := rec_det.CREDIT_AMOUNT;
1821 P_RECONCILED_YN := NULL;
1822 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1823 xtr_debug_pkg.debug('P_RECONCILE: ' || 'value--------------------');
1824 xtr_debug_pkg.debug('P_RECONCILE: ' || 'P_IMPORT_REFERENCE = ' || to_char(P_IMPORT_REFERENCE));
1825 xtr_debug_pkg.debug('P_RECONCILE: ' || 'P_RECORD_IN_PROCESS = ' || to_char(P_RECORD_IN_PROCESS));
1826 xtr_debug_pkg.debug('P_RECONCILE: ' || 'P_PARTY_NAME = ' || P_PARTY_NAME);
1827 xtr_debug_pkg.debug('P_RECONCILE: ' || 'P_SERIAL_REFERENCE = ' || P_SERIAL_REFERENCE);
1828 xtr_debug_pkg.debug('P_RECONCILE: ' || 'P_DEBIT_AMOUNT = ' || to_char(P_DEBIT_AMOUNT));
1829 xtr_debug_pkg.debug('P_RECONCILE: ' || 'P_CREDIT_AMOUNT = ' || to_char(P_CREDIT_AMOUNT));
1830 xtr_debug_pkg.debug('P_RECONCILED_YN = ' || P_RECONCILED_YN);
1831 END IF;
1832
1833 /* dda_sum_date */
1834 if nvl(l_sum_date,'N') = 'Y' then
1835 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1836 xtr_debug_pkg.debug('P_RECONCILE: ' || '>>>>>DDA_SUM_DATE..............');
1837 END IF;
1838
1839 open DDA_SUM_DATE;
1840
1841 LOOP -- 5 (in 4)
1842 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
1843 xtr_debug_pkg.debug('P_RECONCILE: ' || '>>>>>> LOOP --5 ');
1844 xtr_debug_pkg.debug('P_RECONCILE: ' || '>>>>>> FETCH DDA_SUM_DATE into l_s_date, l_sum_amt, l_num_recs');
1845 END IF;
1846 fetch DDA_SUM_DATE INTO l_s_date,l_sum_amt,l_num_recs, v_netoff_number;
1847 --dbms_output.put_line(' DDA_SUM_DATE date, amt, no-rec, net# '||l_s_date||' - '||l_sum_amt||' - '||l_num_recs||' - '||v_netoff_number);
1848
1849 EXIT WHEN DDA_SUM_DATE%NOTFOUND;
1850 /***************/
1851 utl_file.put_line (l_file,'>>----DDA_SUM_DATE');
1852 utl_file.put_line (l_file,'>>----net#, date, amt no-recs '||v_netoff_number||' - '||l_s_date||' - '||l_sum_amt||' - '||l_num_Recs);
1853 utl_file.put_line (l_file,'>>------------------------------');
1854 /***************/
1855
1856 if nvl(rec_det.DEBIT_AMOUNT,0) <> 0 then
1857 if ((((-1) * rec_det.DEBIT_AMOUNT) = l_sum_amt and l_amount =
1858 'Y') or (nvl(l_amount,'N') = 'N')) then
1859
1860 --- I think it never go to here, because l_amount =Y
1861 if ((-1) * rec_det.DEBIT_AMOUNT) > l_sum_amt then
1862 -- amt from reconcile is less than due
1863 l_reset_amt := abs(rec_det.DEBIT_AMOUNT + l_sum_amt) /
1864 l_num_recs;
1865 else
1866 l_reset_amt := (rec_det.DEBIT_AMOUNT - l_sum_amt) / l_num_recs;
1867 end if;
1868 p_tot_recon := nvl(p_tot_recon,0) + 1;
1869 P_RECONCILED_YN :='Y';
1870
1871 open REC_NUM;
1872 fetch REC_NUM INTO l_rec_nos;
1873 close REC_NUM;
1874
1875 if nvl(P_MIN_REC_NOS,9999999) >= l_rec_nos then
1876 P_MIN_REC_NOS := l_rec_nos;
1877 end if;
1878
1879 if nvl(P_MAX_REC_NOS,0) <= l_rec_nos then
1880 P_MAX_REC_NOS := l_rec_nos;
1881 end if;
1882
1883 if P_REC_NOS is NULL then
1884 P_REC_NOS := l_rec_nos;
1885 end if;
1886
1887
1888 If v_netoff_number is null then
1889 /***************/
1890 utl_file.put_line (l_file,'>>----DDA_SUM_DATE');
1891 utl_file.put_line (l_file,'>>---- inside IF ');
1892 utl_file.put_line (l_file,'>>------------------------------');
1893 /***************/
1894
1895 update XTR_DEAL_DATE_AMOUNTS
1896 set RECONCILED_REFERENCE = l_rec_nos,
1897 RECONCILED_PASS_CODE =
1898 decode(DATE_TYPE,'FORCAST','^'||P_PASS_CODE,P_PASS_CODE),
1899 RECONCILED_DAYS_ADJUST = (trunc(rec_det.VALUE_DATE) -
1900 trunc(l_s_date)),
1901 AMOUNT_DATE =
1902 decode(DATE_TYPE,'FORCAST',rec_det.VALUE_DATE,AMOUNT_DATE),
1903 DATE_TYPE = decode(DATE_TYPE,'FORCAST','SETTLE',DATE_TYPE),
1904 HCE_AMOUNT =
1905 decode(DATE_TYPE,'FORCAST',decode(nvl(HCE_AMOUNT,0),0,HCE_AMOUNT,
1906 (AMOUNT/HCE_AMOUNT)*(nvl(HCE_AMOUNT,0) + nvl(l_reset_amt,0))),HCE_AMOUNT),
1907 AMOUNT =
1908 decode(DATE_TYPE,'FORCAST',decode(nvl(AMOUNT,0),0,AMOUNT,nvl(AMOUNT,0) +
1909 nvl(l_reset_amt,0)),AMOUNT),
1910 CASHFLOW_AMOUNT =
1911 decode(DATE_TYPE,'FORCAST',decode(nvl(CASHFLOW_AMOUNT,0),0,
1912 CASHFLOW_AMOUNT,nvl(CASHFLOW_AMOUNT,0) + nvl(l_reset_amt,0)),CASHFLOW_AMOUNT)
1913 where ((to_char(DEAL_NUMBER) = ltrim(rtrim(rec_det.PARTICULARS))
1914 and l_deal_no = 'Y') or (l_deal_no is NULL and DATE_TYPE <>'FORCAST'))
1915 and AMOUNT_DATE = l_s_date
1916 and NETOFF_NUMBER is NULL
1917 and AMOUNT_TYPE <> 'FACEVAL'
1918 and CURRENCY =P_CURRENCY
1919 and RECONCILED_REFERENCE is NULL and RECONCILED_PASS_CODE is NULL
1920 and ((ACCOUNT_NO = P_ACCOUNT_NUMBER and l_bk_acct = 'Y') or
1921 (nvl(l_bk_acct,'%') = '%'))
1922 and ((CPARTY_CODE = substr(rec_det.PARTY_NAME,1,7)
1923 and l_party = 'Y') or (l_party is NULL))
1924 and ((SERIAL_REFERENCE = rtrim(rec_det.SERIAL_REFERENCE)
1925 and l_serial_ref = 'Y') or (l_serial_ref is NULL))
1926 and DEAL_TYPE like nvl(l_deal_type ,'%')
1927 and DEAL_SUBTYPE like nvl(l_subtype,'%')
1928 and PRODUCT_TYPE like nvl(l_product,'%')
1929 and PORTFOLIO_CODE like nvl(l_portfolio,'%')
1930 and ((date_type <>'FORCAST' and NVL(P_INCL_RTM, 'N')='N') or NVL(P_INCL_RTM, 'N')='Y');
1931 If SQL%FOUND then
1932 /***************/
1933 utl_file.put_line (l_file,'>>----DDA_SUM_DATE');
1934 utl_file.put_line (l_file,'>>---- UPD complete deal# '||rtrim(rec_det.particulars));
1935 utl_file.put_line (l_file,'>>------------------------------');
1936 /***************/
1937 For C2_Rec in C2(l_Rec_nos)
1938 Loop
1939 Update Xtr_Settlement_Summary
1940 Set status = 'R'
1941 Where settlement_number = C2_Rec.settlement_number;
1942 /***************/
1943 utl_file.put_line(l_file, '>>----DDA_SUM_DATE ------');
1944 utl_file.put_line(l_file, '>>----UPD settlement_summary complete settle# '||C2_Rec.settlement_number);
1945 utl_file.put_line(l_file, '>>------------------------------------');
1946 /**************/
1947 End Loop;
1948 End if;
1949 Else
1950 /***************/
1951 utl_file.put_line (l_file,'>>----DDA_SUM_DATE');
1952 utl_file.put_line (l_file,'>>---- inside ELSE ');
1953 utl_file.put_line (l_file,'>>------------------------------');
1954 /***************/
1955
1956 update XTR_DEAL_DATE_AMOUNTS
1957 set RECONCILED_REFERENCE = l_rec_nos,
1958 RECONCILED_PASS_CODE =
1959 decode(DATE_TYPE,'FORCAST','^'||P_PASS_CODE,P_PASS_CODE),
1960 RECONCILED_DAYS_ADJUST = (trunc(rec_det.VALUE_DATE) -
1961 trunc(l_s_date)),
1962 AMOUNT_DATE =
1963 decode(DATE_TYPE,'FORCAST',rec_det.VALUE_DATE,AMOUNT_DATE),
1964 DATE_TYPE = decode(DATE_TYPE,'FORCAST','SETTLE',DATE_TYPE),
1965 HCE_AMOUNT =
1966 decode(DATE_TYPE,'FORCAST',decode(nvl(HCE_AMOUNT,0),0,HCE_AMOUNT,
1967 (AMOUNT/HCE_AMOUNT)*(nvl(HCE_AMOUNT,0) + nvl(l_reset_amt,0))),HCE_AMOUNT),
1968 AMOUNT =
1969 decode(DATE_TYPE,'FORCAST',decode(nvl(AMOUNT,0),0,AMOUNT,nvl(AMOUNT,0) +
1970 nvl(l_reset_amt,0)),AMOUNT),
1971 CASHFLOW_AMOUNT =
1972 decode(DATE_TYPE,'FORCAST',decode(nvl(CASHFLOW_AMOUNT,0),0,
1973 CASHFLOW_AMOUNT,nvl(CASHFLOW_AMOUNT,0) + nvl(l_reset_amt,0)),CASHFLOW_AMOUNT)
1974 where
1975 /*
1976 ((to_char(DEAL_NUMBER) = ltrim(rtrim(rec_det.PARTICULARS))
1977 and l_deal_no = 'Y') or (l_deal_no is NULL and DATE_TYPE <>'FORCAST'))
1978 and
1979 */
1980 AMOUNT_DATE = l_s_date
1981 and NETOFF_NUMBER = v_netoff_number
1982 and AMOUNT_TYPE <> 'FACEVAL'
1983 and CURRENCY =P_CURRENCY
1984 and RECONCILED_REFERENCE is NULL and RECONCILED_PASS_CODE is NULL
1985 and ((ACCOUNT_NO = P_ACCOUNT_NUMBER and l_bk_acct = 'Y') or
1986 (nvl(l_bk_acct,'%') = '%'))
1987 and ((CPARTY_CODE = substr(rec_det.PARTY_NAME,1,7)
1988 and l_party = 'Y') or (l_party is NULL))
1989 and ((SERIAL_REFERENCE = rtrim(rec_det.SERIAL_REFERENCE)
1990 and l_serial_ref = 'Y') or (l_serial_ref is NULL))
1991 and DEAL_TYPE like nvl(l_deal_type ,'%')
1992 and DEAL_SUBTYPE like nvl(l_subtype,'%')
1993 and PRODUCT_TYPE like nvl(l_product,'%')
1994 and PORTFOLIO_CODE like nvl(l_portfolio,'%')
1995 and ((date_type <>'FORCAST' and NVL(P_INCL_RTM, 'N')='N') or NVL(P_INCL_RTM, 'N')='Y');
1996 If SQL%FOUND then
1997 /***************/
1998 utl_file.put_line (l_file,'>>----DDA_SUM_DATE');
1999 utl_file.put_line (l_file,'>>---- UPD complete deal# '||rtrim(rec_det.particulars));
2000 utl_file.put_line (l_file,'>>------------------------------');
2001 /***************/
2002 For C1_Rec in C1(v_netoff_number)
2003 Loop
2004 Update Xtr_Settlement_Summary
2005 Set status = 'R'
2006 Where settlement_summary_id = C1_Rec.net_ID;
2007 /***************/
2008 utl_file.put_line(l_file, '>>----DDA ------');
2009 utl_file.put_line(l_file, '>>----UPD settlement_summary complete netID '||C1_Rec.net_id);
2010 utl_file.put_line(l_file, '>>------------------------------------');
2011 /**************/
2012 End Loop;
2013 End if;
2014 End if;
2015
2016 update XTR_PAY_REC_RECONCILIATION
2017 set RECONCILED_REFERENCE = l_rec_nos,
2018 RECONCILED_PASS_CODE = P_PASS_CODE
2019 where CURRENT OF REC;
2020 EXIT;
2021 end if;
2022 elsif nvl(rec_det.CREDIT_AMOUNT,0) <> 0 then
2023 if (((rec_det.CREDIT_AMOUNT) = l_sum_amt and l_amount =
2024 'Y') or (nvl(l_amount,'N') = 'N')) then
2025 if rec_det.CREDIT_AMOUNT > l_sum_amt then
2026 l_reset_amt := (rec_det.CREDIT_AMOUNT - l_sum_amt) /
2027 l_num_recs;
2028 else
2029 l_reset_amt := (l_sum_amt - rec_det.CREDIT_AMOUNT) /
2030 l_num_recs;
2031 end if;
2032 p_tot_recon := nvl(p_tot_recon,0) + 1;
2033 P_RECONCILED_YN :='Y';
2034 open REC_NUM;
2035 fetch REC_NUM INTO l_rec_nos;
2036 close REC_NUM;
2037 if nvl(P_MIN_REC_NOS,9999999) >= l_rec_nos then
2038 P_MIN_REC_NOS := l_rec_nos;
2039 end if;
2040 if nvl(P_MAX_REC_NOS,0) <= l_rec_nos then
2041 P_MAX_REC_NOS := l_rec_nos;
2042 end if;
2043 if P_REC_NOS is NULL then
2044 P_REC_NOS := l_rec_nos;
2045 end if;
2046
2047 --dbms_output.put_line('bef UPD ');
2048 If v_netoff_number is null then
2049 /***************/
2050 utl_file.put_line (l_file,'>>----not known');
2051 utl_file.put_line (l_file,'>>---- inside IF '||v_netoff_number);
2052 utl_file.put_line (l_file,'>>------------------------------');
2053 /***************/
2054
2055 update XTR_DEAL_DATE_AMOUNTS
2056 set RECONCILED_REFERENCE = l_rec_nos,
2057 RECONCILED_PASS_CODE =
2058 decode(DATE_TYPE,'FORCAST','^'||P_PASS_CODE,P_PASS_CODE),
2059 RECONCILED_DAYS_ADJUST = (trunc(rec_det.VALUE_DATE) -
2060 trunc(l_s_date)),
2061 AMOUNT_DATE =
2062 decode(DATE_TYPE,'FORCAST',rec_det.VALUE_DATE,AMOUNT_DATE),
2063 DATE_TYPE = decode(DATE_TYPE,'FORCAST','SETTLE',DATE_TYPE),
2064 HCE_AMOUNT =
2065 decode(DATE_TYPE,'FORCAST',decode(nvl(HCE_AMOUNT,0),0,HCE_AMOUNT,
2066 (AMOUNT/HCE_AMOUNT)*(nvl(HCE_AMOUNT,0) + nvl(l_reset_amt,0))),HCE_AMOUNT),
2067 AMOUNT =
2068 decode(DATE_TYPE,'FORCAST',decode(nvl(AMOUNT,0),0,AMOUNT,nvl(AMOUNT,0) +
2069 nvl(l_reset_amt,0)),AMOUNT),
2070 CASHFLOW_AMOUNT =
2071 decode(DATE_TYPE,'FORCAST',decode(nvl(CASHFLOW_AMOUNT,0),0,
2072 CASHFLOW_AMOUNT,nvl(CASHFLOW_AMOUNT,0) + nvl(l_reset_amt,0)),CASHFLOW_AMOUNT)
2073 where ((to_char(DEAL_NUMBER) = ltrim(rtrim(rec_det.PARTICULARS))
2074 and l_deal_no = 'Y') or (l_deal_no is NULL))
2075 and AMOUNT_DATE = l_s_date
2076 and NETOFF_NUMBER is NULL
2077 and AMOUNT_TYPE <> 'FACEVAL'
2078 and CURRENCY =P_CURRENCY
2079 and RECONCILED_REFERENCE is NULL
2080 and RECONCILED_PASS_CODE is NULL
2081 and ((ACCOUNT_NO = P_ACCOUNT_NUMBER and l_bk_acct = 'Y') or
2082 (nvl(l_bk_acct,'%') = '%'))
2083 and ((CPARTY_CODE = substr(rec_det.PARTY_NAME,1,7)
2084 and l_party = 'Y') or (l_party is NULL))
2085 and ((SERIAL_REFERENCE = rtrim(rec_det.SERIAL_REFERENCE)
2086 and l_serial_ref = 'Y') or (l_serial_ref is NULL))
2087 and DEAL_TYPE like nvl(l_deal_type ,'%')
2088 and DEAL_SUBTYPE like nvl(l_subtype,'%')
2089 and PRODUCT_TYPE like nvl(l_product,'%')
2090 and PORTFOLIO_CODE like nvl(l_portfolio,'%')
2091 and ((date_type <>'FORCAST' and NVL(P_INCL_RTM,'N')='N') or NVL(P_INCL_RTM, 'N')='Y');
2092 If SQL%FOUND then
2093 /***************/
2094 utl_file.put_line (l_file,'>>----not known');
2095 utl_file.put_line (l_file,'>>---- UPD complete ');
2096 utl_file.put_line (l_file,'>>------------------------------');
2097 /***************/
2098 For C2_Rec in C2(l_rec_nos)
2099 Loop
2100 Update Xtr_Settlement_Summary
2101 Set status = 'R'
2102 Where settlement_number = C2_Rec.settlement_number;
2103 /***************/
2104 utl_file.put_line(l_file, '>>----DDA_SUM_DATE ------');
2105 utl_file.put_line(l_file, '>>----UPD settlement_summary complete settle# '||C2_Rec.settlement_number);
2106 utl_file.put_line(l_file, '>>------------------------------------');
2107 /**************/
2108 End Loop;
2109 End if;
2110 Else
2111 /***************/
2112 utl_file.put_line (l_file,'>>----not known');
2113 utl_file.put_line (l_file,'>>---- inside ELSE ');
2114 utl_file.put_line (l_file,'>>------------------------------');
2115 /***************/
2116
2117 update XTR_DEAL_DATE_AMOUNTS
2118 set RECONCILED_REFERENCE = l_rec_nos,
2119 RECONCILED_PASS_CODE =
2120 decode(DATE_TYPE,'FORCAST','^'||P_PASS_CODE,P_PASS_CODE),
2121 RECONCILED_DAYS_ADJUST = (trunc(rec_det.VALUE_DATE) -
2122 trunc(l_s_date)),
2123 AMOUNT_DATE =
2124 decode(DATE_TYPE,'FORCAST',rec_det.VALUE_DATE,AMOUNT_DATE),
2125 DATE_TYPE = decode(DATE_TYPE,'FORCAST','SETTLE',DATE_TYPE),
2126 HCE_AMOUNT =
2127 decode(DATE_TYPE,'FORCAST',decode(nvl(HCE_AMOUNT,0),0,HCE_AMOUNT,
2128 (AMOUNT/HCE_AMOUNT)*(nvl(HCE_AMOUNT,0) + nvl(l_reset_amt,0))),HCE_AMOUNT),
2129 AMOUNT =
2130 decode(DATE_TYPE,'FORCAST',decode(nvl(AMOUNT,0),0,AMOUNT,nvl(AMOUNT,0) +
2131 nvl(l_reset_amt,0)),AMOUNT),
2132 CASHFLOW_AMOUNT =
2133 decode(DATE_TYPE,'FORCAST',decode(nvl(CASHFLOW_AMOUNT,0),0,
2134 CASHFLOW_AMOUNT,nvl(CASHFLOW_AMOUNT,0) + nvl(l_reset_amt,0)),CASHFLOW_AMOUNT)
2135 where
2136 /*
2137 ((to_char(DEAL_NUMBER) = ltrim(rtrim(rec_det.PARTICULARS))
2138 and l_deal_no = 'Y') or (l_deal_no is NULL))
2139 and
2140 */
2141 AMOUNT_DATE = l_s_date
2142 and NETOFF_NUMBER = v_netoff_number
2143 and AMOUNT_TYPE <> 'FACEVAL'
2144 and CURRENCY =P_CURRENCY
2145 and RECONCILED_REFERENCE is NULL
2146 and RECONCILED_PASS_CODE is NULL
2147 and ((ACCOUNT_NO = P_ACCOUNT_NUMBER and l_bk_acct = 'Y') or
2148 (nvl(l_bk_acct,'%') = '%'))
2149 and ((CPARTY_CODE = substr(rec_det.PARTY_NAME,1,7)
2150 and l_party = 'Y') or (l_party is NULL))
2151 and ((SERIAL_REFERENCE = rtrim(rec_det.SERIAL_REFERENCE)
2152 and l_serial_ref = 'Y') or (l_serial_ref is NULL))
2153 and DEAL_TYPE like nvl(l_deal_type ,'%')
2154 and DEAL_SUBTYPE like nvl(l_subtype,'%')
2155 and PRODUCT_TYPE like nvl(l_product,'%')
2156 and PORTFOLIO_CODE like nvl(l_portfolio,'%')
2157 and ((date_type <>'FORCAST' and NVL(P_INCL_RTM,'N')='N') or NVL(P_INCL_RTM, 'N')='Y');
2158 If SQL%FOUND then
2159 /***************/
2160 utl_file.put_line (l_file,'>>----not known ---');
2161 utl_file.put_line (l_file,'>>----UPD complete ');
2162 utl_file.put_line (l_file,'>>------------------------------');
2163 /***************/
2164 For C1_Rec in C1(v_netoff_number)
2165 Loop
2166 Update Xtr_Settlement_Summary
2167 Set status = 'R'
2168 Where settlement_summary_id = C1_Rec.net_ID;
2169 /***************/
2170 utl_file.put_line(l_file, '>>----DDA ------');
2171 utl_file.put_line(l_file, '>>----UPD settlement_summary complete netID '||C1_Rec.net_id);
2172 utl_file.put_line(l_file, '>>------------------------------------');
2173 /**************/
2174 End Loop;
2175
2176 End if;
2177 End if;
2178
2179 update XTR_PAY_REC_RECONCILIATION
2180 set RECONCILED_REFERENCE = l_rec_nos,
2181 RECONCILED_PASS_CODE = P_PASS_CODE
2182 where CURRENT OF REC;
2183 EXIT;
2184 end if;
2185 end if;
2186 END LOOP; -- 5 (in 4)
2187 close DDA_SUM_DATE;
2188 elsif nvl(l_sum_range,'N') = 'Y' then
2189 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
2190 xtr_debug_pkg.debug('P_RECONCILE: ' || '>>>>> DDA_SUM_RANGE .............');
2191 END IF;
2192
2193 open DDA_SUM_RANGE;
2194 -- Sum records on Deal Date Amounts ACROSS a Range of Dates
2195 LOOP -- 6 (in 4)
2196 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
2197 xtr_debug_pkg.debug('P_RECONCILE: ' || '>>>>>> LOOP --6 ');
2198 xtr_debug_pkg.debug('P_RECONCILE: ' || '>>>>>> FETCH DDA_SUM_RANGE into l_sum_amt, l_num_recs');
2199 END IF;
2200 fetch DDA_SUM_RANGE INTO l_sum_amt,l_num_recs, v_netoff_number;
2201 --dbms_output.put_line('DDA_SUM_RANGE amt, no-recs, net# '||l_sum_amt||' - '||l_num_recs||' - '||v_netoff_number);
2202
2203 EXIT WHEN DDA_SUM_RANGE%NOTFOUND;
2204 /***************/
2205 utl_file.put_line (l_file,'>>----DDA_SUM_RANGE');
2206 utl_file.put_line (l_file,'>>----deal#, sumrange, acct, party, curr '||l_deal_no||' - '||l_sum_range||' - '||p_account_number||' - '||rec_det.party_name||' - '||p_currency);
2207 utl_file.put_line (l_file, '>>--- value-date, l-days '||rec_det.value_date||' - '||l_days);
2208 utl_file.put_line (l_file,'>>----net#, amt, no-rec '||v_netoff_number||' - '||l_sum_amt||' - '||l_num_recs);
2209 utl_file.put_line (l_file,'>>------------------------------');
2210 /***************/
2211
2212 if nvl(rec_det.DEBIT_AMOUNT,0) <> 0 then
2213 if ((((-1) * rec_det.DEBIT_AMOUNT) = l_sum_amt and l_amount =
2214 'Y') or (nvl(l_amount,'N') = 'N')) then
2215
2216 if ((-1) * rec_det.DEBIT_AMOUNT) > l_sum_amt then
2217 -- amt from reconcile is less than due
2218 l_reset_amt := abs(rec_det.DEBIT_AMOUNT + l_sum_amt) /
2219 l_num_recs;
2220 else
2221 l_reset_amt := (rec_det.DEBIT_AMOUNT - l_sum_amt) / l_num_recs;
2222 end if;
2223
2224 p_tot_recon := nvl(p_tot_recon,0) + 1;
2225 P_RECONCILED_YN :='Y';
2226 open REC_NUM;
2227 fetch REC_NUM INTO l_rec_nos;
2228 close REC_NUM;
2229 if nvl(P_MIN_REC_NOS,9999999) >= l_rec_nos then
2230 P_MIN_REC_NOS := l_rec_nos;
2231 end if;
2232 if nvl(P_MAX_REC_NOS,0) <= l_rec_nos then
2233 P_MAX_REC_NOS := l_rec_nos;
2234 end if;
2235 if P_REC_NOS is NULL then
2236 P_REC_NOS := l_rec_nos;
2237 end if;
2238
2239
2240 --dbms_output.put_line('bef UPD');
2241 If v_netoff_number is null then
2242 /***************/
2243 utl_file.put_line (l_file,'>>----DDA_SUM_RANGE');
2244 utl_file.put_line (l_file,'>>---- inside IF ');
2245 utl_file.put_line (l_file,'>>------------------------------');
2246 /***************/
2247
2248 update XTR_DEAL_DATE_AMOUNTS
2249 set RECONCILED_REFERENCE = l_rec_nos,
2250 RECONCILED_PASS_CODE =
2251 decode(DATE_TYPE,'FORCAST','^'||P_PASS_CODE,P_PASS_CODE),
2252 RECONCILED_DAYS_ADJUST = (trunc(rec_det.VALUE_DATE) -
2253 trunc(l_s_date)),
2254 AMOUNT_DATE =
2255 decode(DATE_TYPE,'FORCAST',rec_det.VALUE_DATE,AMOUNT_DATE),
2256 DATE_TYPE = decode(DATE_TYPE,'FORCAST','SETTLE',DATE_TYPE),
2257 HCE_AMOUNT =
2258 decode(DATE_TYPE,'FORCAST',decode(nvl(HCE_AMOUNT,0),0,HCE_AMOUNT,
2259 (AMOUNT/HCE_AMOUNT)*(nvl(HCE_AMOUNT,0) + nvl(l_reset_amt,0))),HCE_AMOUNT),
2260 AMOUNT =
2261 decode(DATE_TYPE,'FORCAST',decode(nvl(AMOUNT,0),0,AMOUNT,nvl(AMOUNT,0) +
2262 nvl(l_reset_amt,0)),AMOUNT),
2263 CASHFLOW_AMOUNT =
2264 decode(DATE_TYPE,'FORCAST',decode(nvl(CASHFLOW_AMOUNT,0),0,
2265 CASHFLOW_AMOUNT,nvl(CASHFLOW_AMOUNT,0) + nvl(l_reset_amt,0)),CASHFLOW_AMOUNT)
2266 where ((to_char(DEAL_NUMBER) = ltrim(rtrim(rec_det.PARTICULARS))
2267 and l_deal_no = 'Y') or (l_deal_no is NULL))
2268 and AMOUNT_DATE between (rec_det.VALUE_DATE - l_days) and
2269 P_CGU$SYSDATE
2270 and NETOFF_NUMBER is NULL
2271 and AMOUNT_TYPE <> 'FACEVAL'
2272 and CURRENCY =P_CURRENCY
2273 and RECONCILED_REFERENCE is NULL -- add
2274 and RECONCILED_PASS_CODE is NULL
2275 and ((ACCOUNT_NO = P_ACCOUNT_NUMBER and l_bk_acct = 'Y') or
2276 (nvl(l_bk_acct,'%') = '%'))
2277 and ((CPARTY_CODE = substr(rec_det.PARTY_NAME,1,7)
2278 and l_party = 'Y') or (l_party is NULL))
2279 and ((SERIAL_REFERENCE = rtrim(rec_det.SERIAL_REFERENCE)
2280 and l_serial_ref = 'Y') or (l_serial_ref is NULL))
2281 and DEAL_TYPE like nvl(l_deal_type ,'%')
2282 and DEAL_SUBTYPE like nvl(l_subtype,'%')
2283 and PRODUCT_TYPE like nvl(l_product,'%')
2284 and PORTFOLIO_CODE like nvl(l_portfolio,'%')
2285 and ((date_type <>'FORCAST' and NVL(P_INCL_RTM, 'N')='N') or NVL(P_INCL_RTM, 'N')='Y');
2286 If SQL%FOUND then
2287 /***************/
2288 utl_file.put_line (l_file,'>>----DDA_SUM_RANGE');
2289 utl_file.put_line (l_file,'>>---- UPD complete rec#, rec-pass '||l_rec_nos||' - '||p_pass_code);
2290 utl_file.put_line (l_file,'>>------------------------------');
2291 /***************/
2292 For C2_Rec in C2(l_rec_nos)
2293 Loop
2294 Update Xtr_Settlement_Summary
2295 Set status = 'R'
2296 Where settlement_number = C2_Rec.Settlement_Number;
2297 /***************/
2298 utl_file.put_line (l_file,'>>----DDA');
2299 utl_file.put_line (l_file,'>>---- UPD settlement_summary complete settle# '||c2_rec.settlement_number);
2300 utl_file.put_line (l_file,'>>------------------------------');
2301 /***************/
2302 End Loop;
2303 End if;
2304 Else
2305 /***************/
2306 utl_file.put_line (l_file,'>>----DDA_SUM_RANGE');
2307 utl_file.put_line (l_file,'>>---- inside ELSE net# '||v_netoff_number);
2308 utl_file.put_line (l_file,'>>------------------------------');
2309 /***************/
2310
2311 update XTR_DEAL_DATE_AMOUNTS
2312 set RECONCILED_REFERENCE = l_rec_nos,
2313 RECONCILED_PASS_CODE =
2314 decode(DATE_TYPE,'FORCAST','^'||P_PASS_CODE,P_PASS_CODE),
2315 RECONCILED_DAYS_ADJUST = (trunc(rec_det.VALUE_DATE) -
2316 trunc(l_s_date)),
2317 AMOUNT_DATE =
2318 decode(DATE_TYPE,'FORCAST',rec_det.VALUE_DATE,AMOUNT_DATE),
2319 DATE_TYPE = decode(DATE_TYPE,'FORCAST','SETTLE',DATE_TYPE),
2320 HCE_AMOUNT =
2321 decode(DATE_TYPE,'FORCAST',decode(nvl(HCE_AMOUNT,0),0,HCE_AMOUNT,
2322 (AMOUNT/HCE_AMOUNT)*(nvl(HCE_AMOUNT,0) + nvl(l_reset_amt,0))),HCE_AMOUNT),
2323 AMOUNT =
2324 decode(DATE_TYPE,'FORCAST',decode(nvl(AMOUNT,0),0,AMOUNT,nvl(AMOUNT,0) +
2325 nvl(l_reset_amt,0)),AMOUNT),
2326 CASHFLOW_AMOUNT =
2327 decode(DATE_TYPE,'FORCAST',decode(nvl(CASHFLOW_AMOUNT,0),0,
2328 CASHFLOW_AMOUNT,nvl(CASHFLOW_AMOUNT,0) + nvl(l_reset_amt,0)),CASHFLOW_AMOUNT)
2329 where
2330 /*
2331 ((to_char(DEAL_NUMBER) = ltrim(rtrim(rec_det.PARTICULARS))
2332 and l_deal_no = 'Y') or (l_deal_no is NULL))
2333 and
2334 */
2335 AMOUNT_DATE between (rec_det.VALUE_DATE - l_days) and
2336 P_CGU$SYSDATE
2337 and NETOFF_NUMBER = v_netoff_number
2338 and AMOUNT_TYPE <> 'FACEVAL'
2339 and CURRENCY =P_CURRENCY
2340 and RECONCILED_REFERENCE is NULL -- add
2341 and RECONCILED_PASS_CODE is NULL
2342 and ((ACCOUNT_NO = P_ACCOUNT_NUMBER and l_bk_acct = 'Y') or
2343 (nvl(l_bk_acct,'%') = '%'))
2344 and ((CPARTY_CODE = substr(rec_det.PARTY_NAME,1,7)
2345 and l_party = 'Y') or (l_party is NULL))
2346 and ((SERIAL_REFERENCE = rtrim(rec_det.SERIAL_REFERENCE)
2347 and l_serial_ref = 'Y') or (l_serial_ref is NULL))
2348 and DEAL_TYPE like nvl(l_deal_type ,'%')
2349 and DEAL_SUBTYPE like nvl(l_subtype,'%')
2350 and PRODUCT_TYPE like nvl(l_product,'%')
2351 and PORTFOLIO_CODE like nvl(l_portfolio,'%')
2352 and ((date_type <>'FORCAST' and NVL(P_INCL_RTM, 'N')='N') or NVL(P_INCL_RTM, 'N')='Y');
2353 If SQL%FOUND then
2354 /***************/
2355 utl_file.put_line (l_file,'>>----DDA_SUM_RANGE');
2356 utl_file.put_line (l_file,'>>---- UPD complete deal# '||rtrim(rec_det.particulars));
2357 utl_file.put_line (l_file,'>>------------------------------');
2358 /***************/
2359 For C1_Rec in C1(v_netoff_number)
2360 Loop
2361 Update Xtr_Settlement_Summary
2362 Set status = 'R'
2363 Where settlement_summary_id = C1_Rec.net_ID;
2364 /***************/
2365 utl_file.put_line(l_file, '>>----DDA ------');
2366 utl_file.put_line(l_file, '>>----UPD settlement_summary complete netID '||C1_Rec.net_id);
2367 utl_file.put_line(l_file, '>>------------------------------------');
2368 /**************/
2369 End Loop;
2370 End if;
2371 End if;
2372
2373 update XTR_PAY_REC_RECONCILIATION
2374 set RECONCILED_REFERENCE = l_rec_nos,
2375 RECONCILED_PASS_CODE = P_PASS_CODE
2376 where CURRENT OF REC;
2377 end if;
2378 EXIT;
2379 elsif nvl(rec_det.CREDIT_AMOUNT,0) <> 0 then
2380 if (((rec_det.CREDIT_AMOUNT) = l_sum_amt and l_amount =
2381 'Y') or (nvl(l_amount,'N') = 'N')) then
2382 if rec_det.CREDIT_AMOUNT > l_sum_amt then
2383 -- amt from reconcile is less than due
2384 l_reset_amt := (rec_det.CREDIT_AMOUNT - l_sum_amt) /
2385 l_num_recs;
2386 else
2387 l_reset_amt := (l_sum_amt - rec_det.CREDIT_AMOUNT) /
2388 l_num_recs;
2389 end if;
2390 p_tot_recon := nvl(p_tot_recon,0) + 1;
2391 P_RECONCILED_YN :='Y';
2392 open REC_NUM;
2393 fetch REC_NUM INTO l_rec_nos;
2394 close REC_NUM;
2395
2396 if nvl(P_MIN_REC_NOS,9999999) >= l_rec_nos then
2397 P_MIN_REC_NOS := l_rec_nos;
2398 end if;
2399 if nvl(P_MAX_REC_NOS,0) <= l_rec_nos then
2400 P_MAX_REC_NOS := l_rec_nos;
2401 end if;
2402 if P_REC_NOS is NULL then
2403 P_REC_NOS := l_rec_nos;
2404 end if;
2405
2406 --dbms_output.put_line('bef UPD ');
2407 If v_netoff_number is null then
2408 /***************/
2409 utl_file.put_line (l_file,'>>----2');
2410 utl_file.put_line (l_file,'>>----inside IF rec#, rec-pass '||l_rec_nos||' - '||p_pass_code);
2411 utl_file.put_line (l_file,'>>------------------------------');
2412 /***************/
2413
2414 update XTR_DEAL_DATE_AMOUNTS
2415 set RECONCILED_REFERENCE = l_rec_nos,
2416 RECONCILED_PASS_CODE =
2417 decode(DATE_TYPE,'FORCAST','^'||P_PASS_CODE,P_PASS_CODE),
2418 RECONCILED_DAYS_ADJUST = (trunc(rec_det.VALUE_DATE) -
2419 trunc(l_s_date)),
2420 AMOUNT_DATE =
2421 decode(DATE_TYPE,'FORCAST',rec_det.VALUE_DATE,AMOUNT_DATE),
2422 DATE_TYPE = decode(DATE_TYPE,'FORCAST','SETTLE',DATE_TYPE),
2423 HCE_AMOUNT =
2424 decode(DATE_TYPE,'FORCAST',decode(nvl(HCE_AMOUNT,0),0,HCE_AMOUNT,
2425 (AMOUNT/HCE_AMOUNT)*(nvl(HCE_AMOUNT,0) + nvl(l_reset_amt,0))),HCE_AMOUNT),
2426 AMOUNT =
2427 decode(DATE_TYPE,'FORCAST',decode(nvl(AMOUNT,0),0,AMOUNT,nvl(AMOUNT,0) +
2428 nvl(l_reset_amt,0)),AMOUNT),
2429 CASHFLOW_AMOUNT =
2430 decode(DATE_TYPE,'FORCAST',decode(nvl(CASHFLOW_AMOUNT,0),0,
2431 CASHFLOW_AMOUNT,nvl(CASHFLOW_AMOUNT,0) + nvl(l_reset_amt,0)),CASHFLOW_AMOUNT)
2432 where AMOUNT_DATE between (rec_det.VALUE_DATE - l_days) and
2433 (rec_det.VALUE_DATE - l_days)
2434 and ((to_char(DEAL_NUMBER) = ltrim(rtrim(rec_det.PARTICULARS))
2435 and l_deal_no = 'Y') or (l_deal_no is NULL))
2436 and AMOUNT_DATE <= P_CGU$SYSDATE
2437 and NETOFF_NUMBER is NULL
2438 and AMOUNT_TYPE <> 'FACEVAL'
2439 and CURRENCY =P_CURRENCY
2440 and RECONCILED_REFERENCE is NULL -- add
2441 and RECONCILED_PASS_CODE is NULL
2442 and ((ACCOUNT_NO = P_ACCOUNT_NUMBER and l_bk_acct = 'Y') or
2443 (nvl(l_bk_acct,'%') = '%'))
2444 and ((CPARTY_CODE = substr(rec_det.PARTY_NAME,1,7)
2445 and l_party = 'Y') or (l_party is NULL))
2446 and ((SERIAL_REFERENCE = rtrim(rec_det.SERIAL_REFERENCE)
2447 and l_serial_ref = 'Y') or (l_serial_ref is NULL))
2448 and DEAL_TYPE like nvl(l_deal_type ,'%')
2449 and DEAL_SUBTYPE like nvl(l_subtype,'%')
2450 and PRODUCT_TYPE like nvl(l_product,'%')
2451 and PORTFOLIO_CODE like nvl(l_portfolio,'%')
2452 and ((date_type <>'FORCAST' and NVL(P_INCL_RTM, 'N')='N') or NVL(P_INCL_RTM, 'N')='Y');
2453 If SQL%FOUND then
2454 /***************/
2455 utl_file.put_line (l_file,'>>----2');
2456 utl_file.put_line (l_file,'>>---- UPD complete deal# '||rtrim(rec_det.particulars));
2457 utl_file.put_line (l_file,'>>------------------------------');
2458 /***************/
2459 For C2_Rec in C2(l_rec_nos)
2460 Loop
2461 Update Xtr_Settlement_Summary
2462 Set status = 'R'
2463 Where settlement_number = C2_Rec.Settlement_Number;
2464 /***************/
2465 utl_file.put_line (l_file,'>>----DDA_SUM_RANGE');
2466 utl_file.put_line (l_file,'>>---- UPD settlement_summary complete settle# '||c2_rec.settlement_number);
2467 utl_file.put_line (l_file,'>>------------------------------');
2468 /***************/
2469 End Loop;
2470 End if;
2471 Else
2472 /***************/
2473 utl_file.put_line (l_file,'>>----2');
2474 utl_file.put_line (l_file,'>>---- inside ELSE net#, rec#, rec-pass '||v_netoff_number||' - '||l_rec_nos||' - '||p_pass_code);
2475 utl_file.put_line (l_file,'>>------------------------------');
2476 /***************/
2477
2478 update XTR_DEAL_DATE_AMOUNTS
2479 set RECONCILED_REFERENCE = l_rec_nos,
2480 RECONCILED_PASS_CODE =
2481 decode(DATE_TYPE,'FORCAST','^'||P_PASS_CODE,P_PASS_CODE),
2482 RECONCILED_DAYS_ADJUST = (trunc(rec_det.VALUE_DATE) -
2483 trunc(l_s_date)),
2484 AMOUNT_DATE =
2485 decode(DATE_TYPE,'FORCAST',rec_det.VALUE_DATE,AMOUNT_DATE),
2486 DATE_TYPE = decode(DATE_TYPE,'FORCAST','SETTLE',DATE_TYPE),
2487 HCE_AMOUNT =
2488 decode(DATE_TYPE,'FORCAST',decode(nvl(HCE_AMOUNT,0),0,HCE_AMOUNT,
2489 (AMOUNT/HCE_AMOUNT)*(nvl(HCE_AMOUNT,0) + nvl(l_reset_amt,0))),HCE_AMOUNT),
2490 AMOUNT =
2491 decode(DATE_TYPE,'FORCAST',decode(nvl(AMOUNT,0),0,AMOUNT,nvl(AMOUNT,0) +
2492 nvl(l_reset_amt,0)),AMOUNT),
2493 CASHFLOW_AMOUNT =
2494 decode(DATE_TYPE,'FORCAST',decode(nvl(CASHFLOW_AMOUNT,0),0,
2495 CASHFLOW_AMOUNT,nvl(CASHFLOW_AMOUNT,0) + nvl(l_reset_amt,0)),CASHFLOW_AMOUNT)
2496 where AMOUNT_DATE between (rec_det.VALUE_DATE - l_days) and
2497 (rec_det.VALUE_DATE - l_days)
2498 /*
2499 and ((to_char(DEAL_NUMBER) = ltrim(rtrim(rec_det.PARTICULARS))
2500 and l_deal_no = 'Y') or (l_deal_no is NULL))
2501 */
2502 and AMOUNT_DATE <= P_CGU$SYSDATE
2503 and NETOFF_NUMBER = v_netoff_number
2504 and AMOUNT_TYPE <> 'FACEVAL'
2505 and CURRENCY =P_CURRENCY
2506 and RECONCILED_REFERENCE is NULL -- add
2507 and RECONCILED_PASS_CODE is NULL
2508 and ((ACCOUNT_NO = P_ACCOUNT_NUMBER and l_bk_acct = 'Y') or
2509 (nvl(l_bk_acct,'%') = '%'))
2510 and ((CPARTY_CODE = substr(rec_det.PARTY_NAME,1,7)
2511 and l_party = 'Y') or (l_party is NULL))
2512 and ((SERIAL_REFERENCE = rtrim(rec_det.SERIAL_REFERENCE)
2513 and l_serial_ref = 'Y') or (l_serial_ref is NULL))
2514 and DEAL_TYPE like nvl(l_deal_type ,'%')
2515 and DEAL_SUBTYPE like nvl(l_subtype,'%')
2516 and PRODUCT_TYPE like nvl(l_product,'%')
2517 and PORTFOLIO_CODE like nvl(l_portfolio,'%')
2518 and ((date_type <>'FORCAST' and NVL(P_INCL_RTM, 'N')='N') or NVL(P_INCL_RTM, 'N')='Y');
2519 If SQL%FOUND then
2520 /***************/
2521 utl_file.put_line (l_file,'>>----2');
2522 utl_file.put_line (l_file,'>>---- UPD complete deal# '||rtrim(Rec_det.particulars));
2523 utl_file.put_line (l_file,'>>------------------------------');
2524 /***************/
2525 For C1_Rec in C1(v_netoff_number)
2526 Loop
2527 Update Xtr_Settlement_Summary
2528 Set status = 'R'
2529 Where settlement_summary_id = C1_Rec.net_ID;
2530 /***************/
2531 utl_file.put_line(l_file, '>>----DDA ------');
2532 utl_file.put_line(l_file, '>>----UPD settlement_summary complete netID '||C1_Rec.net_id);
2533 utl_file.put_line(l_file, '>>------------------------------------');
2534 /**************/
2535 End Loop;
2536 End if;
2537 End if;
2538
2539 update XTR_PAY_REC_RECONCILIATION
2540 set RECONCILED_REFERENCE = l_rec_nos,
2541 RECONCILED_PASS_CODE = P_PASS_CODE
2542 where CURRENT OF REC;
2543 end if;
2544 EXIT;
2545 end if;
2546 END LOOP; -- 6 (in 4)
2547 close DDA_SUM_RANGE;
2548 else
2549 /* else check amount equal to each other --> no sum */
2550 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
2551 xtr_debug_pkg.debug('P_RECONCILE: ' || '>>>>> ELSE check amount equal each other, NO SUM! ');
2552 END IF;
2553 -- Check amounts equal each other.Do Not Sum amounts for a date or range of dates.
2554 -- The imported amount has to equal a single transaction amount.
2555 open DDA;
2556 l_num_recs :=0;
2557
2558 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
2559 xtr_debug_pkg.debug('P_RECONCILE: ' || '>>>>> FETCH DDA into l_sum_amt, l_num_recs, l_one_date ');
2560 END IF;
2561 fetch DDA INTO l_sum_amt,l_num_recs,l_one_date, v_netoff_number;
2562 --dbms_output.put_line('DDA amt, no-recs, date, net# '||l_sum_amt||' - '||l_num_recs||' - '||l_one_date||' - '||v_netoff_number);
2563 /***************/
2564 utl_file.put_line (l_file,'>>----DDA');
2565 utl_file.put_line (l_file,'>>----net#, date, amt, no-recs '||v_netoff_number||' - '||l_one_date||' - '||l_sum_amt||' - '||l_num_recs);
2566 utl_file.put_line (l_file,'>>------------------------------');
2567 /***************/
2568
2569 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
2570 xtr_debug_pkg.debug('P_RECONCILE: ' || 'value after fetch -----------------');
2571 xtr_debug_pkg.debug('P_RECONCILE: ' || 'l_deal_no = ' || l_deal_no);
2572 xtr_debug_pkg.debug('P_RECONCILE: ' || 'rec_det.value_date = ' || to_char(rec_det.value_date));
2573 xtr_debug_pkg.debug('P_RECONCILE: ' || 'l_date = ' || l_date);
2574 END IF;
2575 xtr_debug_pkg.debug('P_CGU$SYSDATE = ' || P_CGU$SYSDATE);
2576 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
2577 xtr_debug_pkg.debug('P_RECONCILE: ' || 'P_CURRENCY = ' || P_CURRENCY);
2578 xtr_debug_pkg.debug('P_RECONCILE: ' || 'l_bk_acct = ' || l_bk_acct);
2579 xtr_debug_pkg.debug('P_RECONCILE: ' || 'rec_det.DEBIT_AMOUNT = ' || to_char(rec_det.DEBIT_AMOUNT));
2580 xtr_debug_pkg.debug('P_RECONCILE: ' || 'rec_det.CREDIT_AMOUNT = ' || to_char(rec_det.CREDIT_AMOUNT));
2581 xtr_debug_pkg.debug('P_RECONCILE: ' || 'l_sum_amt = '|| to_char(l_sum_amt));
2582 xtr_debug_pkg.debug('P_RECONCILE: ' || 'l_num_recs = '|| to_char(l_num_recs));
2583 xtr_debug_pkg.debug('P_RECONCILE: ' || 'l_one_date = '|| to_char(l_one_date));
2584 xtr_debug_pkg.debug('P_RECONCILE: ' || '-------------------------------');
2585 END IF;
2586
2587 if nvl(l_num_recs,0) > 0 then --- bug 5353780
2588 p_tot_recon := nvl(p_tot_recon,0) + 1;
2589 P_RECONCILED_YN :='Y';
2590 l_sum_amt := nvl(rec_det.DEBIT_AMOUNT,0) +
2591 nvl(rec_det.CREDIT_AMOUNT,0);
2592 open REC_NUM;
2593 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
2594 xtr_debug_pkg.debug('P_RECONCILE: ' || '>>>>> FETCH REC_NUM into l_rec_nos');
2595 END IF;
2596 fetch REC_NUM INTO l_rec_nos;
2597 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
2598 xtr_debug_pkg.debug('P_RECONCILE: ' || 'l_rec_nos = '|| to_char(l_rec_nos));
2599 END IF;
2600 close REC_NUM;
2601 if nvl(P_MIN_REC_NOS,9999999) >= l_rec_nos then
2602 P_MIN_REC_NOS := l_rec_nos;
2603 end if;
2604 if nvl(P_MAX_REC_NOS,0) <= l_rec_nos then
2605 P_MAX_REC_NOS := l_rec_nos;
2606 end if;
2607 if P_REC_NOS is NULL then
2608 P_REC_NOS := l_rec_nos;
2609 end if;
2610 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
2611 xtr_debug_pkg.debug('P_RECONCILE: ' || '>>>>> UPDATE DDA >>>>>>>>>>>>>>');
2612 END IF;
2613
2614 --dbms_output.put_line('bef UPD ');
2615 If v_netoff_number is null then
2616 /***************/
2617 utl_file.put_line (l_file,'>>----DDA');
2618 utl_file.put_line (l_file,'>>---- inside IF , rec#, rec-pass '||l_rec_nos||' - '||p_pass_code);
2619 utl_file.put_line (l_file,'>>------------------------------');
2620 /***************/
2621
2622 update XTR_DEAL_DATE_AMOUNTS
2623 set RECONCILED_REFERENCE = l_rec_nos,
2624 RECONCILED_PASS_CODE =
2625 decode(DATE_TYPE,'FORCAST','^'||P_PASS_CODE,P_PASS_CODE),
2626 RECONCILED_DAYS_ADJUST = (trunc(rec_det.VALUE_DATE) -
2627 trunc(l_one_date)),
2628 AMOUNT_DATE =
2629 decode(DATE_TYPE,'FORCAST',rec_det.VALUE_DATE,AMOUNT_DATE),
2630 DATE_TYPE = decode(DATE_TYPE,'FORCAST','SETTLE',DATE_TYPE),
2631 AMOUNT =
2632 decode(DATE_TYPE,'FORCAST',decode(nvl(AMOUNT,0),0,AMOUNT,abs(nvl(l_sum_amt,0))),AMOUNT),
2633 HCE_AMOUNT =
2634 decode(DATE_TYPE,'FORCAST',decode(nvl(HCE_AMOUNT,0),0,HCE_AMOUNT,
2635 (AMOUNT/HCE_AMOUNT)*abs(nvl(l_sum_amt,0))),HCE_AMOUNT),
2636 CASHFLOW_AMOUNT =
2637 decode(DATE_TYPE,'FORCAST',decode(nvl(CASHFLOW_AMOUNT,0),0,
2638 CASHFLOW_AMOUNT,nvl(l_sum_amt,0)),CASHFLOW_AMOUNT)
2639 where ((to_char(DEAL_NUMBER) = ltrim(rtrim(rec_det.PARTICULARS))
2640 and nvl(l_deal_no,'N') = 'Y')
2641 or (l_deal_no is NULL )) --- modify
2642 and AMOUNT_DATE = l_one_date
2643 and NETOFF_NUMBER is NULL
2644 and AMOUNT_TYPE <> 'FACEVAL'
2645 and CURRENCY =P_CURRENCY
2646 and AMOUNT_DATE <= P_CGU$SYSDATE
2647 and RECONCILED_REFERENCE is NULL
2648 and RECONCILED_PASS_CODE is NULL
2649 and ((ACCOUNT_NO = P_ACCOUNT_NUMBER and nvl(l_bk_acct,'N') = 'Y') or
2650 (nvl(l_bk_acct,'%') = '%'))
2651 and ((CASHFLOW_AMOUNT < 0 and rec_det.DEBIT_AMOUNT is NOT NULL)
2652 and ((abs(CASHFLOW_AMOUNT) = rec_det.DEBIT_AMOUNT and nvl(l_amount,'N') =
2653 'Y') or (nvl(l_amount,'N') = 'N' and date_type='FORCAST')) -----???
2654 or (nvl(rec_det.DEBIT_AMOUNT,0) = 0))
2655 and ((CASHFLOW_AMOUNT > 0 and rec_det.CREDIT_AMOUNT is NOT NULL)
2656 and ((abs(CASHFLOW_AMOUNT) = rec_det.CREDIT_AMOUNT
2657 and nvl(l_amount,'N')='Y') or (nvl(l_amount,'N') = 'N'))
2658 or (nvl(rec_det.CREDIT_AMOUNT,0) = 0))
2659 and ((CPARTY_CODE = substr(rec_det.PARTY_NAME,1,7)
2660 and nvl(l_party,'N') = 'Y') or (l_party is NULL))
2661 and ((SERIAL_REFERENCE = rtrim(rec_det.SERIAL_REFERENCE)
2662 and nvl(l_serial_ref,'N') = 'Y') or (l_serial_ref is NULL))
2663 and DEAL_TYPE like nvl(l_deal_type ,'%')
2664 and DEAL_SUBTYPE like nvl(l_subtype,'%')
2665 and PRODUCT_TYPE like nvl(l_product,'%')
2666 and PORTFOLIO_CODE like nvl(l_portfolio,'%')
2667 and ((date_type <>'FORCAST' and NVL(P_INCL_RTM, 'N')='N') or NVL(P_INCL_RTM, 'N')='Y')
2668 and rownum < 2; -- bug 5353780
2669 If SQL%FOUND then
2670 /***************/
2671 utl_file.put_line (l_file,'>>----DDA');
2672 utl_file.put_line (l_file,'>>---- UPD complete deal# '||rtrim(rec_det.particulars));
2673 utl_file.put_line (l_file,'>>------------------------------');
2674 /***************/
2675 For C2_Rec in C2(l_rec_nos)
2676 Loop
2677 Update Xtr_Settlement_Summary
2678 Set status = 'R'
2679 Where settlement_number = C2_Rec.Settlement_Number;
2680 /***************/
2681 utl_file.put_line (l_file,'>>----DDA');
2682 utl_file.put_line (l_file,'>>---- UPD settlement_summary complete settle# '||c2_rec.settlement_number);
2683 utl_file.put_line (l_file,'>>------------------------------');
2684 /***************/
2685 End Loop;
2686 End if;
2687 Else
2688 /***************/
2689 utl_file.put_line (l_file,'>>----DDA');
2690 utl_file.put_line (l_file,'>>---- inside ELSE rec#, rec-pass '||l_rec_nos||' - '||p_pass_code);
2691 utl_file.put_line (l_file,'>>------------------------------');
2692 /***************/
2693
2694 update XTR_DEAL_DATE_AMOUNTS
2695 set RECONCILED_REFERENCE = l_rec_nos,
2696 RECONCILED_PASS_CODE =
2697 decode(DATE_TYPE,'FORCAST','^'||P_PASS_CODE,P_PASS_CODE),
2698 RECONCILED_DAYS_ADJUST = (trunc(rec_det.VALUE_DATE) -
2699 trunc(l_one_date)),
2700 AMOUNT_DATE =
2701 decode(DATE_TYPE,'FORCAST',rec_det.VALUE_DATE,AMOUNT_DATE),
2702 DATE_TYPE = decode(DATE_TYPE,'FORCAST','SETTLE',DATE_TYPE),
2703 AMOUNT =
2704 decode(DATE_TYPE,'FORCAST',decode(nvl(AMOUNT,0),0,AMOUNT,abs(nvl(l_sum_amt,0))),AMOUNT),
2705 HCE_AMOUNT =
2706 decode(DATE_TYPE,'FORCAST',decode(nvl(HCE_AMOUNT,0),0,HCE_AMOUNT,
2707 (AMOUNT/HCE_AMOUNT)*abs(nvl(l_sum_amt,0))),HCE_AMOUNT),
2708 CASHFLOW_AMOUNT =
2709 decode(DATE_TYPE,'FORCAST',decode(nvl(CASHFLOW_AMOUNT,0),0,
2710 CASHFLOW_AMOUNT,nvl(l_sum_amt,0)),CASHFLOW_AMOUNT)
2711 where
2712 /*
2713 ((to_char(DEAL_NUMBER) = ltrim((rtrim(rec_det.PARTICULARS))
2714 and nvl(l_deal_no,'N') = 'Y')
2715 or (l_deal_no is NULL )) --- modify
2716 and
2717 */
2718 AMOUNT_DATE = l_one_date
2719 and NETOFF_NUMBER = v_netoff_number
2720 and AMOUNT_TYPE <> 'FACEVAL'
2721 and CURRENCY =P_CURRENCY
2722 and AMOUNT_DATE <= P_CGU$SYSDATE
2723 and RECONCILED_REFERENCE is NULL
2724 and RECONCILED_PASS_CODE is NULL
2725 and ((ACCOUNT_NO = P_ACCOUNT_NUMBER and nvl(l_bk_acct,'N') = 'Y') or
2726 (nvl(l_bk_acct,'%') = '%'))
2727 /*
2728 and ((CASHFLOW_AMOUNT < 0 and rec_det.DEBIT_AMOUNT is NOT NULL)
2729 and ((abs(CASHFLOW_AMOUNT) = rec_det.DEBIT_AMOUNT and nvl(l_amount,'N') =
2730 'Y') or (nvl(l_amount,'N') = 'N' and date_type='FORCAST')) -----???
2731 or (nvl(rec_det.DEBIT_AMOUNT,0) = 0))
2732 and ((CASHFLOW_AMOUNT > 0 and rec_det.CREDIT_AMOUNT is NOT NULL)
2733 and ((abs(CASHFLOW_AMOUNT) = rec_det.CREDIT_AMOUNT
2734 and nvl(l_amount,'N')='Y') or (nvl(l_amount,'N') = 'N'))
2735 or (nvl(rec_det.CREDIT_AMOUNT,0) = 0))
2736 */
2737 and ((CPARTY_CODE = substr(rec_det.PARTY_NAME,1,7)
2738 and nvl(l_party,'N') = 'Y') or (l_party is NULL))
2739 and ((SERIAL_REFERENCE = rtrim(rec_det.SERIAL_REFERENCE)
2740 and nvl(l_serial_ref,'N') = 'Y') or (l_serial_ref is NULL))
2741 and DEAL_TYPE like nvl(l_deal_type ,'%')
2742 and DEAL_SUBTYPE like nvl(l_subtype,'%')
2743 and PRODUCT_TYPE like nvl(l_product,'%')
2744 and PORTFOLIO_CODE like nvl(l_portfolio,'%')
2745 and ((date_type <>'FORCAST' and NVL(P_INCL_RTM, 'N')='N') or NVL(P_INCL_RTM, 'N')='Y');
2746 If SQL%FOUND then
2747 /***************/
2748 utl_file.put_line (l_file,'>>----DDA -----');
2749 utl_file.put_line (l_file,'>>---- UPD complete deal# '||rtrim(Rec_det.particulars));
2750 utl_file.put_line (l_file,'>>------------------------------');
2751 /***************/
2752 For C1_Rec in C1(v_netoff_number)
2753 Loop
2754 Update Xtr_Settlement_Summary
2755 Set status = 'R'
2756 Where settlement_summary_id = C1_Rec.net_ID;
2757 /***************/
2758 utl_file.put_line(l_file, '>>----DDA ------');
2759 utl_file.put_line(l_file, '>>----UPD settlement_summary complete netID '||C1_Rec.net_id);
2760 utl_file.put_line(l_file, '>>------------------------------------');
2761 /**************/
2762 End Loop;
2763 End if;
2764 End if;
2765
2766 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
2767 xtr_debug_pkg.debug('P_RECONCILE: ' || '>>>>> UPDATE XTR_PAY_REC_REOCNCILIATION ');
2768 xtr_debug_pkg.debug('P_RECONCILE: ' || 'l_rec_nos = ' || to_char(l_rec_nos));
2769 END IF;
2770 update XTR_PAY_REC_RECONCILIATION
2771 set RECONCILED_REFERENCE = l_rec_nos,
2772 RECONCILED_PASS_CODE = P_PASS_CODE
2773 where CURRENT OF REC;
2774
2775 end if;
2776 close DDA;
2777 end if;
2778
2779 END LOOP; -- 4 (in 2)
2780 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
2781 xtr_debug_pkg.debug('P_RECONCILE: ' || 'END LOOP -- 4 ');
2782 END IF;
2783 close REC;
2784 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
2785 xtr_debug_pkg.debug('P_RECONCILE: ' || 'END LOOP -- 2 ');
2786 END IF;
2787 END LOOP; -- 2
2788 close PASSES;
2789 if PASSES%ISOPEN then close PASSES; end if;
2790 if PASS_DETAILS%ISOPEN then close PASS_DETAILS; end if;
2791 if REC%ISOPEN then close REC; end if;
2792 if REC_NUM%ISOPEN then close REC_NUM; end if;
2793 if DDA%ISOPEN then close DDA; end if;
2794 if DDA_SUM_DATE%ISOPEN then close DDA_SUM_DATE; end if;
2795 if DDA_SUM_RANGE%ISOPEN then close DDA_SUM_RANGE; end if;
2796 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
2797 xtr_debug_pkg.debug('END P_RECONCILE >>>>>>>>>>');
2798 END IF;
2799
2800 utl_file.put_line (l_file,' ');
2801 utl_file.put_line (l_file,'>> Log file is located at '||l_dirname||'/xtraurec.log');
2802 utl_file.put_line (l_file,'>>----------------------------------------------------------------');
2803 utl_file.fclose(l_file);
2804
2805 end P_RECONCILE;
2806
2807 END XTR_AUTO_RECONCILIATION;