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