1 PACKAGE BODY XTR_FPS2_P as
2 /* $Header: xtrfps2b.pls 120.19 2006/06/23 06:46:16 kbabu ship $ */
3 -------------------------------------------------------------------------------------------
4 -- Procedure to Calculate Currency Cross Rates
5 PROCEDURE CALC_CROSS_RATE(quoted_1st IN varchar2,
6 start_date IN date) is
7 /*
8 This procedure calculates currency cross rates using
9 spot rates and loads them into the appropriate table
10 It uses the following input parameters :
11 --
12 quoted_1st - currency quoted first,
13 start_date - start date for calculation,
14 */
15 --
16 l_round NUMBER;
17 ccya VARCHAR2(15);
18 ccyb VARCHAR2(15);
19 ccy1 VARCHAR2(15);
20 ccy2 VARCHAR2(15);
21 l_bid_rate NUMBER;
22 l_offer_rate NUMBER;
23 l_last_archive_time DATE;
24 l_archive_freq_type VARCHAR2(1);
25 l_freq_archive NUMBER;
26 --
27 cursor PREV_ARCHIVE is
28 select last_archive_time,archive_freq_type,freq_archive
29 from XTR_MARKET_PRICES
30 where (currency_a = quoted_1st or currency_b = quoted_1st)
31 and (currency_a = 'USD' or currency_b = 'USD')
32 and term_type = 'S';
33 --
34 cursor SPOT_CUR is
35 select sr1.currency tmp_currency_first,
36 decode(sr2.currency_a,'USD',sr2.currency_b,sr2.currency_a)
37 tmp_currency_second,
38 decode(sr2.currency_a,'USD',sr2.bid_price ,(1/sr2.ask_price)) /
39 decode(sr3.currency_a, 'USD', sr3.ask_price, (1/sr3.bid_price))
40 tmp_bid_rate,
41 decode(sr2.currency_a,'USD',sr2.ask_price, (1/sr2.bid_price)) /
42 decode(sr3.currency_a, 'USD', sr3.bid_price, (1/sr3.ask_price))
43 tmp_offer_rate,
44 nvl(sr1.rate_date,sr1.spot_date) tmp_rate_date
45 from XTR_MASTER_CURRENCIES sr1,
46 XTR_MARKET_PRICES sr2,
47 XTR_MARKET_PRICES sr3
48 where sr1.currency = quoted_1st
49 and (sr2.currency_a = 'USD' or sr2.currency_b = 'USD')
50 and (sr3.currency_a = 'USD' or sr3.currency_b = 'USD')
51 and sr2.currency_a <> quoted_1st
52 and sr2.currency_b <> quoted_1st
53 and (sr3.currency_a = quoted_1st or sr3.currency_b= quoted_1st)
54 and sr2.term_type = 'S'
55 and sr3.term_type = 'S';
56 --
57 spot spot_cur%rowtype;
58 --
59 cursor F1F2 is
60 select CURRENCY_FIRST,CURRENCY_SECOND
61 from XTR_BUY_SELL_COMBINATIONS
62 where ((CURRENCY_BUY = ccy1 and
63 CURRENCY_SELL = ccy2) or
64 (CURRENCY_BUY = ccy2 and
65 CURRENCY_SELL = ccy1));
66 --
67 begin
68 open PREV_ARCHIVE;
69 fetch PREV_ARCHIVE INTO l_last_archive_time,
70 l_archive_freq_type,
71 l_freq_archive;
72 close PREV_ARCHIVE;
73 --
74 if ((l_LAST_ARCHIVE_TIME is NULL) or
75 (l_ARCHIVE_FREQ_TYPE = 'A') or
76 (l_LAST_ARCHIVE_TIME is NOT NULL and
77 ((l_ARCHIVE_FREQ_TYPE = 'S')
78 and (SYSDATE >= (l_LAST_ARCHIVE_TIME
79 + l_FREQ_ARCHIVE / 60 / 60 / 24))) or
80 ((l_ARCHIVE_FREQ_TYPE = 'M')
81 and (SYSDATE >= (l_LAST_ARCHIVE_TIME
82 + l_FREQ_ARCHIVE / 60 / 24))) or
83 ((l_ARCHIVE_FREQ_TYPE = 'H')
84 and (SYSDATE >= (l_LAST_ARCHIVE_TIME
85 + l_FREQ_ARCHIVE / 24))) or
86 ((l_ARCHIVE_FREQ_TYPE = 'D')
87 and (SYSDATE >= (l_LAST_ARCHIVE_TIME
88 + l_FREQ_ARCHIVE))) or
89 ((l_ARCHIVE_FREQ_TYPE = 'W')
90 and (SYSDATE >= (l_LAST_ARCHIVE_TIME
91 + l_FREQ_ARCHIVE * 7))) or
92 ((l_ARCHIVE_FREQ_TYPE = 'T')
93 and (l_LAST_ARCHIVE_TIME <
94 to_date(to_char(SYSDATE,'DD/MON/YYYY')||':'||
95 lpad(to_char(l_FREQ_ARCHIVE),'0',2),
96 'DD/MON/YYYY:HH24'))
97 and (SYSDATE >= to_date(to_char(SYSDATE,'DD/MON/YYYY')||':'||
98 lpad(to_char(l_FREQ_ARCHIVE),'0',2),
99 'DD/MON/YYYY:HH24'))))) then
100 -- :NEW.LAST_ARCHIVE_TIME := SYSDATE;
101 FOR spot IN spot_cur LOOP
102 ccy1 := spot.tmp_currency_first;
103 ccy2 := spot.tmp_currency_second;
104 open F1F2;
105 fetch F1F2 INTO ccya,ccyb;
106 if F1F2%NOTFOUND then
107 -- We Don't require this cross rate to be saved
108 close F1F2;
109 goto NEXTREC;
110 end if;
111 close F1F2;
112 if ccya <> ccy1 then
113 l_bid_rate := 1 / spot.tmp_offer_rate;
114 l_offer_rate := 1 / spot.tmp_bid_rate;
115 else
116 l_bid_rate := spot.tmp_bid_rate;
117 l_offer_rate := spot.tmp_offer_rate;
118 end if;
119
120 begin
121 insert into XTR_CURRENCY_CROSS_RATES
122 (rate_date,currency_first,currency_second,bid_rate,offer_rate)
123 values
124 (nvl(spot.tmp_rate_date,SYSDATE),ccya,ccyb,
125 l_bid_rate,l_offer_rate);
126 exception
127 when OTHERS then
128 NULL;
129 end;
130 <<NEXTREC>>
131 NULL;
132 END LOOP;
133 end if;
134 end CALC_CROSS_RATE;
135 ----------------------------------------------------------------------------------------------------------------
136 -- Procedure to calculate revaluations
137 PROCEDURE CALC_REVALS ( p_start_date IN date,
138 p_end_date IN date,
139 p_sysdate IN date,
140 p_user IN varchar2,
141 p_company_code IN varchar2,
142 p_deal_type IN varchar2,
143 p_deal_subtype IN varchar2,
144 p_product_type IN varchar2,
145 p_portfolio_code IN varchar2) is
146 --
147 begin
148 -- This is now in its own package ---> REVAL_PROCESS
149 NULL;
150 end CALC_REVALS;
151 ----------------------------------------------------------------------------------------------------------------
152 -- Procedure to calculate the price of a FRA
153 PROCEDURE CALCULATE_FRA_PRICE (t IN NUMBER,
154 T1 IN NUMBER,
155 Rt IN NUMBER,
156 Rt1 IN NUMBER,
157 l_year_basis IN NUMBER,
158 fra_rate IN OUT NOCOPY NUMBER) is
159 --
160 -- t NUMBER (Days from today to start date)
161 -- T1 NUMBER (Days from today to maturity date)
162 -- Rt NUMBER (Interest Rate for maturity t days)
163 -- RT1 NUMBER (Interest Rate for maturity T1 days)
164 --
165 l_invest_at NUMBER;
166 l_borrow_at NUMBER;
167 --
168 begin
169 if t is NOT NULL and T1 is NOT NULL and Rt is NOT NULL and Rt1 is NOT NULL and
170 l_year_basis is NOT NULL then
171 -- Calc for Invest Rate
172 l_invest_at := (((1 + RT1 * T1 / (l_year_basis * 100))) /
173 (1 + Rt * t / (l_year_basis * 100)) - 1) *
174 (l_year_basis * 100 / (T1 - t));
175 -- Calc for Borrow Rate
176 l_borrow_at := (((1 + RT1 * T1 / (l_year_basis * 100))) /
177 (1 + Rt * t / (l_year_basis * 100)) - 1) *
178 (l_year_basis * 100 / (T1 - t));
179
180 fra_rate := (l_invest_at + l_borrow_at) / 2;
181 end if;
182 end CALCULATE_FRA_PRICE;
183 ----------------------------------------------------------------------------------------------------------------
184 PROCEDURE DEAL_EXISTS (l_date IN DATE,
185 l_company IN VARCHAR2,
186 l_d_type IN VARCHAR2,
187 l_d_subty IN VARCHAR2,
188 l_dealer IN VARCHAR2,
189 l_exists IN OUT NOCOPY VARCHAR2) as
190 cursor DEAL_EXISTS_YN is
191 select 'Y'
192 from XTR_DEAL_DATE_AMOUNTS_V
193 where AMOUNT_DATE = l_date
194 and COMPANY_CODE like nvl(l_company,'%')
195 and DEAL_TYPE like nvl(l_d_type,'%')
196 and DEAL_SUBTYPE like nvl(l_d_subty,'%')
197 and DEALER_CODE like nvl(l_dealer,'%');
198 --
199 begin
200 open DEAL_EXISTS_YN;
201 fetch DEAL_EXISTS_YN INTO l_exists;
202 close DEAL_EXISTS_YN;
203 l_exists := nvl(l_exists,'N');
204 end DEAL_EXISTS;
205 ----------------------------------------------------------------------------------------------------------------
206 -- Local Procedure to Default Company, Currency, Portfolio
207 PROCEDURE DEFAULTS (l_comp IN OUT NOCOPY VARCHAR2,
208 l_comp_name IN OUT NOCOPY VARCHAR2,
209 l_ccy IN OUT NOCOPY VARCHAR2,
210 l_ccy_name IN OUT NOCOPY VARCHAR2,
211 l_port IN OUT NOCOPY VARCHAR2) is
212 --
213 /* Modified below cursors Bug 4647357
214 cursor COMP_DFLT is
215 select a.PARTY_CODE, a.SHORT_NAME, d.CURRENCY_CODE, b.NAME
216 from XTR_PARTIES_V a,
217 XTR_MASTER_CURRENCIES b,
218 HR_LEGAL_ENTITIES c,
219 GL_SETS_OF_BOOKS d
220 where a.PARTY_TYPE = 'C'
221 and a.DEFAULT_COMPANY = 'Y'
222 and c.ORGANIZATION_ID = a.LEGAL_ENTITY_ID
223 and c.SET_OF_BOOKS_ID = d.SET_OF_BOOKS_ID
224 and b.CURRENCY = d.CURRENCY_CODE;
225 --
226 cursor COMP_CCY is
227 select d.CURRENCY_CODE, b.NAME
228 from XTR_PARTIES_V a,
229 XTR_MASTER_CURRENCIES b,
230 HR_LEGAL_ENTITIES c,
231 GL_SETS_OF_BOOKS d
232 where a.PARTY_TYPE = 'C'
233 and a.PARTY_CODE = l_comp
234 and c.ORGANIZATION_ID = a.LEGAL_ENTITY_ID
235 and c.SET_OF_BOOKS_ID = d.SET_OF_BOOKS_ID
236 and b.CURRENCY = d.CURRENCY_CODE;
237 */
238 cursor COMP_DFLT is
239 select a.PARTY_CODE, a.SHORT_NAME, d.CURRENCY_CODE, b.NAME
240 from XTR_PARTIES_V a,
241 XTR_MASTER_CURRENCIES b,
242 GL_LEDGER_LE_V c,
243 GL_SETS_OF_BOOKS d
244 where a.PARTY_TYPE = 'C'
245 and a.DEFAULT_COMPANY = 'Y'
246 and c.LEGAL_ENTITY_ID = a.LEGAL_ENTITY_ID
247 and c.LEDGER_ID = d.SET_OF_BOOKS_ID
248 and c.LEDGER_CATEGORY_CODE = 'PRIMARY'
249 and b.CURRENCY = d.CURRENCY_CODE;
250 --
251 cursor COMP_CCY is
252 select d.CURRENCY_CODE, b.NAME
253 from XTR_PARTIES_V a,
254 XTR_MASTER_CURRENCIES b,
255 GL_LEDGER_LE_V c,
256 GL_SETS_OF_BOOKS d
257 where a.PARTY_TYPE = 'C'
258 and a.PARTY_CODE = l_comp
259 and c.LEGAL_ENTITY_ID = a.LEGAL_ENTITY_ID
260 and c.LEDGER_ID = d.SET_OF_BOOKS_ID
261 and c.LEDGER_CATEGORY_CODE = 'PRIMARY'
262 and b.CURRENCY = d.CURRENCY_CODE;
263 --
264 cursor PORT is
265 select PORTFOLIO
266 from XTR_PORTFOLIOS
267 where COMPANY_CODE = l_comp
268 and DEFAULT_PORTFOLIO = 'Y';
269 --
270 begin
271 if l_comp is NULL then
272 open COMP_DFLT;
273 fetch COMP_DFLT INTO l_comp,l_comp_name,l_ccy,l_ccy_name;
274 close COMP_DFLT;
275 else
276 open COMP_CCY;
277 fetch COMP_CCY INTO l_ccy,l_ccy_name;
278 close COMP_CCY;
279 end if;
280 if l_comp is not null then
281 open PORT;
282 fetch PORT INTO l_port;
283 close PORT;
284 end if;
285 end DEFAULTS;
286 ----------------------------------------------------------------------------------------------------------------
287 /* Bug 1708946
288 -- Procedure to default currency for the company entered
289 PROCEDURE DEFAULT_CCY (l_pty IN VARCHAR2,
290 l_ccy IN OUT NOCOPY VARCHAR2,
291 l_ccy_name IN OUT NOCOPY VARCHAR2) is
292 cursor DFLT is
293 select a.HOME_CURRENCY,b.NAME
294 from XTR_PARTIES_V a,
295 XTR_MASTER_CURRENCIES b
296 where a.PARTY_CODE = l_pty
297 and b.CURRENCY = a.HOME_CURRENCY;
298 --
299 begin
300 if l_pty is NOT NULL then
301 open DFLT;
302 fetch DFLT INTO l_ccy,l_ccy_name;
303 close DFLT;
304 end if;
305 end DEFAULT_CCY;
306 */
307 ----------------------------------------------------------------------------------------------------------------
308 -- Procedure to set default company account number.
309 PROCEDURE DEFAULT_COMP_ACCT (l_company IN VARCHAR2,
310 l_currency IN VARCHAR2,
311 l_acct_nos IN OUT NOCOPY VARCHAR2) is
312 --
313 cursor DEFAULT_ACCOUNT is
314 select ACCOUNT_NUMBER
315 from XTR_BANK_ACCOUNTS
316 where PARTY_CODE = l_company
317 and CURRENCY = l_currency
318 and DEFAULT_ACCT = 'Y';
319 --
320 begin
321 open DEFAULT_ACCOUNT;
322 fetch DEFAULT_ACCOUNT INTO l_acct_nos;
323 close DEFAULT_ACCOUNT;
324 end DEFAULT_COMP_ACCT;
325 ----------------------------------------------------------------------------------------------------------------
326 -- Procedure to Default the Spot Date
327 PROCEDURE DEFAULT_SPOT_DATE (l_sysdate IN DATE,
328 l_ccy1 IN VARCHAR2,
329 l_ccy2 IN VARCHAR2,
330 out_date IN OUT NOCOPY DATE) is
331 --
332 --in_date VARCHAR2(10);
333 v_new_date DATE;
334
335 v_err_code NUMBER;
336 v_err_level VARCHAR2(10);
337 --
338 /*
339 cursor WK_DAY is
340 select 1
341 from DUAL
342 where to_char(to_date(in_date),'D') between 2 and 6;
343 --
344 cursor HOL is
345 select 1
346 from XTR_HOLIDAYS
347 where HOLIDAY_DATE = in_date
348 and CURRENCY IN (l_ccy1,l_ccy2);
349 --
350 v_dummy VARCHAR2(1);
351 */
352 --
353 begin
354 --in_date := to_char(l_sysdate + 2);
355 v_new_date := l_sysdate+2;
356 if v_new_date is NOT NULL then
357 LOOP
358 --check currency 1
359 XTR_FPS3_P.CHK_HOLIDAY(v_new_date,l_ccy1,v_err_code,v_err_level);
360 if (v_err_level is not null) then
361 goto REDO;
362 end if;
363 --check currency 2
364 XTR_FPS3_P.CHK_HOLIDAY(v_new_date,l_ccy2,v_err_code,v_err_level);
365 if (v_err_level is not null) then
366 goto REDO;
367 end if;
368 -- good to go
369 EXIT;
370 <<REDO>>
371 -- Date is either a Holiday or Weekend then add 1 day and recheck
372 --in_date := to_char(to_date(in_date) + 1);
373 v_new_date := v_new_date + 1;
374 END LOOP;
375 out_date := v_new_date;
376 --out_date := to_date(in_date);
377 end if;
378 end DEFAULT_SPOT_DATE;
379 ----------------------------------------------------------------------------------------------------------------
380 -- Procedure to Calulate Yields
381 PROCEDURE DISCOUNT_INTEREST_CALC(days_in_year IN NUMBER,
382 amount IN NUMBER,
383 rate IN NUMBER,
384 no_of_days IN NUMBER,
385 round_factor IN NUMBER,
386 interest IN OUT NOCOPY NUMBER,
387 rounding_type IN VARCHAR2) is
388 /* This procedure calculates interest amounts.
389 It uses the following input parameters :
390 amount - transaction amount,
391 rate - interest rate,
392 days_in_year - year basis for this currency,
393 no_of_days - duration of deal,
394 round_factor - the rounding factor to be applied for this ccy.
395 It returns the following output parameters :
396 interest - amount of interest to be paid/received*/
397 --
398 begin
399 --Change Interest Override
400 interest := interest_round(amount - (amount /
401 (1 + (rate / (days_in_year * 100) * no_of_days))),round_factor,nvl(rounding_type,'R'));
402 --Original-----------------------------------------------
403 -- interest := round(amount - (amount /
404 -- (1 + (rate / (days_in_year * 100) * no_of_days))),round_factor);
405 --End of Change------------------------------------------
406 end DISCOUNT_INTEREST_CALC;
407
408
409 -- Procedure to Calulate Present Value (simple interest )
410 PROCEDURE PRESENT_VALUE_CALC(days_in_year IN NUMBER,
411 amount IN NUMBER,
412 rate IN NUMBER,
413 no_of_days IN NUMBER,
414 round_factor IN NUMBER,
415 present_value IN OUT NOCOPY NUMBER) is
416 /* This procedure calculates present values.
417 It uses the following input parameters :
418 amount - transaction amount,
419 rate - interest rate,
420 days_in_year - year basis for this currency,
421 no_of_days - duration of deal,
422 round_factor - the rounding factor to be applied for this ccy.
423 It returns the following output parameters :
424 present_value - present value to be paid/received*/
425 --
426 begin
427 present_value := round(amount /
428 (1 + (rate / (days_in_year * 100) * no_of_days)),round_factor);
429 end PRESENT_VALUE_CALC;
430
431 --
432 -- Procedure to Calulate Present Value (simple interest )
433 PROCEDURE PRESENT_VALUE_COMPOUND(days_in_year IN NUMBER,
434 amount IN NUMBER,
435 rate IN NUMBER,
436 no_of_days IN NUMBER,
437 round_factor IN NUMBER,
438 present_value IN OUT NOCOPY NUMBER) is
439 --
440 begin
441 if 1+rate/100 >=0 then
442 present_value := round(amount /
443 POWER((1 + rate/100),no_of_days/days_in_year),round_factor);
444 end if;
445
446 end PRESENT_VALUE_COMPOUND;
447
448 -- Procedure to Extrapolate the rate from a specific yield curve
449 PROCEDURE EXTRAPOLATE_FROM_YIELD_CURVE(l_ccy IN CHAR,
450 l_days IN NUMBER,
451 l_yield_curve IN VARCHAR2,
452 l_rate IN OUT NOCOPY NUMBER) is
453 --
454 l_lower_rate NUMBER;
455 l_lower_days NUMBER;
456 l_higher_rate NUMBER;
457 l_higher_days NUMBER;
458 l_diff NUMBER;
459 --
460 cursor GET_LOWER_RATE is
461 select (a.BID_PRICE + a.ASK_PRICE) / 2,a.NOS_OF_DAYS
462 from XTR_YIELD_CURVE_DETAILS a
463 where a.CURRENCY = l_ccy
464 and a.GROUP_CODE = l_yield_curve
465 and a.NOS_OF_DAYS =
466 (select max(c.NOS_OF_DAYS)
467 from XTR_YIELD_CURVE_DETAILS c
468 where c.GROUP_CODE = l_yield_curve
469 and c.CURRENCY = l_ccy
470 and c.NOS_OF_DAYS < l_days);
471 --
472 cursor GET_HIGHER_RATE is
473 select (a.BID_PRICE + a.ASK_PRICE) / 2,a.NOS_OF_DAYS
474 from XTR_YIELD_CURVE_DETAILS a
475 where a.CURRENCY = l_ccy
476 and a.GROUP_CODE = l_yield_curve
477 and a.NOS_OF_DAYS =
478 (select max(c.NOS_OF_DAYS)
479 from XTR_YIELD_CURVE_DETAILS c
480 where c.GROUP_CODE = l_yield_curve
481 and c.CURRENCY = l_ccy
482 and c.NOS_OF_DAYS >= l_days);
483 --
484 begin
485 open GET_LOWER_RATE;
486 fetch GET_LOWER_RATE INTO l_lower_rate,l_lower_days;
487 close GET_LOWER_RATE;
488 --
489 open GET_HIGHER_RATE;
490 fetch GET_HIGHER_RATE INTO l_higher_rate,l_higher_days;
491 close GET_HIGHER_RATE;
492 if l_lower_days is NULL and l_higher_days is NULL then
493 l_rate := 0;
494 elsif l_lower_days is NOT NULL and l_higher_days is NULL then
495 l_rate := l_lower_rate;
496 elsif l_lower_days is NULL and l_higher_days is NOT NULL then
497 l_rate := l_higher_rate;
498 elsif l_lower_days is NOT NULL and l_higher_days is NOT NULL then
499 -- Extrapolate the rate
500 l_diff := l_higher_days - l_lower_days;
501 if l_diff = 0 then
502 l_diff := 1;
503 end if;
504 l_rate :=
505 (l_lower_rate * ((l_higher_days - l_days) / l_diff)) +
506 (l_higher_rate * ((l_days - l_lower_days) / l_diff));
507 else
508 l_rate := 0;
509 end if;
510 l_rate := round(nvl(l_rate,0),5);
511 end EXTRAPOLATE_FROM_YIELD_CURVE;
512 ----------------------------------------------------------------------------------------------------------------
513 -- Procedure to Extrapolate the rate from a specific yield curve
514 PROCEDURE EXTRAPOLATE_FROM_MARKET_PRICES(l_ccy IN CHAR,
515 l_days IN NUMBER,
516 l_rate IN OUT NOCOPY NUMBER) is
517 --
518 l_lower_rate NUMBER;
519 l_lower_days NUMBER;
520 l_higher_rate NUMBER;
521 l_higher_days NUMBER;
522 l_diff NUMBER;
523 --
524 cursor GET_LOWER_RATE is
525 select (a.BID_PRICE + a.ASK_PRICE) / 2,a.NOS_OF_DAYS
526 from XTR_MARKET_PRICES a
527 where a.CURRENCY_A = l_ccy
528 and a.TERM_TYPE IN('D','M','Y','A')
529 and a.NOS_OF_DAYS =
530 (select max(c.NOS_OF_DAYS)
531 from XTR_MARKET_PRICES c
532 where c.TERM_TYPE IN('D','M','Y','A')
533 and c.CURRENCY_A = l_ccy
534 and c.NOS_OF_DAYS < l_days);
535 --
536 cursor GET_HIGHER_RATE is
537 select (a.BID_PRICE + a.ASK_PRICE) / 2,a.NOS_OF_DAYS
538 from XTR_MARKET_PRICES a
539 where a.CURRENCY_A = l_ccy
540 and a.TERM_TYPE IN('D','M','Y','A')
541 and a.NOS_OF_DAYS =
542 (select max(c.NOS_OF_DAYS)
543 from XTR_MARKET_PRICES c
544 where c.TERM_TYPE IN('D','M','Y','A')
545 and c.CURRENCY_A = l_ccy
546 and c.NOS_OF_DAYS >= l_days);
547 --
548 begin
549 open GET_LOWER_RATE;
550 fetch GET_LOWER_RATE INTO l_lower_rate,l_lower_days;
551 close GET_LOWER_RATE;
552 --
553 open GET_HIGHER_RATE;
554 fetch GET_HIGHER_RATE INTO l_higher_rate,l_higher_days;
555 close GET_HIGHER_RATE;
556 if l_lower_days is NULL and l_higher_days is NULL then
557 l_rate := 0;
558 elsif l_lower_days is NOT NULL and l_higher_days is NULL then
559 l_rate := l_lower_rate;
560 elsif l_lower_days is NULL and l_higher_days is NOT NULL then
561 l_rate := l_higher_rate;
562 elsif l_lower_days is NOT NULL and l_higher_days is NOT NULL then
563 -- Extrapolate the rate
564 l_diff := l_higher_days - l_lower_days;
565 if l_diff = 0 then
566 l_diff := 1;
567 end if;
568 l_rate :=
569 (l_lower_rate * ((l_higher_days - l_days) / l_diff)) +
570 (l_higher_rate * ((l_days - l_lower_days) / l_diff));
571 else
572 l_rate := 0;
573 end if;
574 l_rate := round(nvl(l_rate,0),5);
575 end EXTRAPOLATE_FROM_MARKET_PRICES;
576 ----------------------------------------------------------------------------------------------------------------
577 -- Procedure to Extrapolate the rate from the reval rates WITHOUT referring to a
578 -- specific yield curve
579 PROCEDURE EXTRAPOLATE_RATE(l_company IN VARCHAR2,
580 l_period_from IN DATE,
581 l_period_to IN DATE,
582 l_ccy IN VARCHAR2,
583 l_days IN NUMBER,
584 l_reval_rate IN OUT NOCOPY NUMBER) is
585 begin
586 null;
587 end EXTRAPOLATE_RATE;
588 ----------------------------------------------------------------------------------------------------------------
589 /*
590 --- This new procedure has to move to another new parkage PRORATE_DB_PKG, because it causes GPF.
591 --- old libary,new parkage,new forms GPF
592 --- old libary,new parkage,old forms OK
593 --- new libary,new parkge,new forms OK
594
595 -- Procedure to Extrapolate the FWDS from the reval rates WITHOUT referring to a
596 -- specific yield curve
597 PROCEDURE EXTRAPOLATE_FWDS(l_company IN VARCHAR2,
598 l_period_from IN DATE,
599 l_period_to IN DATE,
600 l_ccy IN VARCHAR2,
601 l_ccyb IN VARCHAR2,
602 l_days IN NUMBER,
603 l_fwds IN OUT NOCOPY NUMBER) is
604 --
605 l_lower_rate NUMBER;
606 l_lower_days NUMBER;
607 l_higher_rate NUMBER;
608 l_higher_days NUMBER;
609 l_diff NUMBER;
610 --
611 l_round_ccy varchar2(15);
612 l_round number;
613 cursor get_round is
614 select rounding_factor
615 from master_currencies
616 where currency=l_round_ccy;
617 cursor GET_LOWER_RATE is
618 select a.REVAL_RATE,a.NUMBER_OF_DAYS
619 from XTR_REVALUATION_RATES a
620 where a.COMPANY_CODE = l_company
621 and a.PERIOD_TO = l_period_to
622 and a.CURRENCYA = l_ccy
623 and a.CURRENCYB = l_ccyb
624 and a.VOLATILITY_OR_RATE = 'FWDS'
625 and a.NUMBER_OF_DAYS =
626 (select max(b.NUMBER_OF_DAYS)
627 from XTR_REVALUATION_RATES b
628 where b.COMPANY_CODE = l_company
629 and b.PERIOD_TO = l_period_to
630 and b.CURRENCYA = l_ccy
631 and b.CURRENCYB = l_ccyb
632 and b.VOLATILITY_OR_RATE = 'FWDS'
633 and b.NUMBER_OF_DAYS < l_days);
634 --
635 cursor GET_HIGHER_RATE is
636 select a.REVAL_RATE,a.NUMBER_OF_DAYS
637 from XTR_REVALUATION_RATES a
638 where a.COMPANY_CODE = l_company
639 and a.PERIOD_TO = l_period_to
640 and a.CURRENCYA = l_ccy
641 and a.CURRENCYB = l_ccyb
642 and a.VOLATILITY_OR_RATE = 'FWDS'
643 and a.NUMBER_OF_DAYS =
644 (select min(b.NUMBER_OF_DAYS)
645 from XTR_REVALUATION_RATES b
646 where b.COMPANY_CODE = l_company
647 and b.PERIOD_TO = l_period_to
648 and b.CURRENCYA = l_ccy
649 and b.CURRENCYB = l_ccyb
650 and b.VOLATILITY_OR_RATE = 'FWDS'
651 and b.NUMBER_OF_DAYS >= l_days);
652 --
653 begin
654 open GET_LOWER_RATE;
655 fetch GET_LOWER_RATE INTO l_lower_rate,l_lower_days;
656 close GET_LOWER_RATE;
657 --
658 open GET_HIGHER_RATE;
659 fetch GET_HIGHER_RATE INTO l_higher_rate,l_higher_days;
660 close GET_HIGHER_RATE;
661 if l_lower_days is NULL and l_higher_days is NULL then
662 l_fwds := 0;
663 else
664 -- Extrapolate the rate
665 l_diff := nvl(l_higher_days,0) - nvl(l_lower_days,0);
666 if l_diff = 0 then
667 l_diff := 1;
668 end if;
669 l_fwds :=
670 (nvl(l_lower_rate,0) * ((nvl(l_higher_days,0) - nvl(l_days,0)) / l_diff)) +
671 (nvl(l_higher_rate,0) * ((nvl(l_days,0) - nvl(l_lower_days,0)) / l_diff));
672 end if;
673 if l_ccy='USD' then
674 l_round_ccy :=l_ccyb;
675 elsif l_ccyb='USD' then
676 l_round_ccy :=l_ccy;
677 else
678 l_round_ccy :=l_ccyb;
679 end if;
680 open get_round;
681 fetch get_round into l_round;
682 close get_round;
683 l_fwds := round(nvl(l_fwds/power(10,nvl(l_round,0)+2),0),5);
684 end EXTRAPOLATE_FWDS;
685 */
686 ----------------------------------------------------------------------------------------------------------------
687 /* This procedure calculates interest amounts.
688 It uses the following input parameters :
689 amount - transaction amount,
690 rate - interest rate,
691 days_in_year - year basis for this currency,
692 no_of_days - duration of deal,
693 round_factor - the rounding factor to be applied for this ccy.
694 It returns the following output parameters :
695 interest - amount of interest to be paid/received
696 */
697 PROCEDURE INTEREST_CALCULATOR (days_in_year IN NUMBER,
698 amount IN NUMBER,
699 rate IN NUMBER,
700 no_of_days IN NUMBER,
701 round_factor IN NUMBER,
702 interest IN OUT NOCOPY NUMBER,
703 round_type IN VARCHAR2
704 ) is
705 --
706 begin
707
708 --
709 -- Added the rounding_type for the interest override feature
710 --
711 -- interest := round((amount * rate / (days_in_year * 100) *
712 -- no_of_days),round_factor);
713
714 interest := interest_round((amount * rate / (days_in_year * 100) *
715 no_of_days),round_factor,round_type);
716
717 end INTEREST_CALCULATOR ;
718 ----------------------------------------------------------------------------------------------------------------
719 -- Procedure to set defaults for Deal block.
720 PROCEDURE SET_DEFAULTS (l_company_code IN OUT NOCOPY VARCHAR2,
721 l_company_name IN OUT NOCOPY VARCHAR2) is
722 --
723 cursor PTY_CODE is
724 select PARTY_CODE, SHORT_NAME
725 from XTR_PARTIES_V
726 where PARTY_TYPE = 'C'
727 and DEFAULT_COMPANY = 'Y';
728 --
729 begin
730 open PTY_CODE;
731 fetch PTY_CODE INTO l_company_code,l_company_name;
732 close PTY_CODE;
733 end SET_DEFAULTS;
734 ----------------------------------------------------------------------------------------------------------------
735 -- Procedure to set defaults for Portfolio block.
736 PROCEDURE SET_DEFAULTS_PDA (l_company_code IN VARCHAR2,
737 l_portfolio_code IN OUT NOCOPY VARCHAR2,
738 l_portfolio_name IN OUT NOCOPY VARCHAR2) is
739 --
740 -- NOTE this brings back the portfolio name whereas the procedure below
741 -- brings back only the code
742 cursor PORT_DFLT is
743 select PORTFOLIO, NULL NAME
744 from xtr_portfolios pf
745 where COMPANY_CODE = l_company_code
746 and DEFAULT_PORTFOLIO = 'Y';
747 --
748 begin
749 if l_portfolio_code is NULL then
750 open PORT_DFLT;
751 fetch PORT_DFLT INTO l_portfolio_code, l_portfolio_name;
752 close PORT_DFLT;
753 end if;
754 end SET_DEFAULTS_PDA;
755 ----------------------------------------------------------------------------------------------------------------
756 -- Procedure to set default company portfolio code.
757 PROCEDURE DEFAULT_PORTFOLIO (l_company_code IN VARCHAR2,
758 l_portfolio_code IN OUT NOCOPY VARCHAR2) is
759 --
760 cursor PORT is
761 select PORTFOLIO
762 from XTR_PORTFOLIOS
763 where COMPANY_CODE = l_company_code
764 and DEFAULT_PORTFOLIO = 'Y';
765 --
766 begin
767 open PORT;
768 fetch PORT INTO l_portfolio_code;
769 close PORT;
770 end DEFAULT_PORTFOLIO;
771 ----------------------------------------------------------------------------------------------------------------
772 -- Procedure to Default Cparty Account Details
773 PROCEDURE STANDING_SETTLEMENTS (l_party IN VARCHAR2,
774 l_ccy IN VARCHAR2,
775 l_deal_type IN VARCHAR2,
776 l_subtype IN VARCHAR2,
777 l_product IN VARCHAR2,
778 l_amount_type IN VARCHAR2,
779 l_cparty_ref IN OUT NOCOPY VARCHAR2,
780 l_account IN OUT NOCOPY VARCHAR2) is
781 --
782 cursor DFLT_ACCT is
783 select b.BANK_SHORT_CODE,a.ACCOUNT_NO
784 from XTR_STANDING_INSTRUCTIONS a,
785 XTR_BANK_ACCOUNTS b
786 where a.PARTY_CODE = l_party
787 and a.CURRENCY = l_ccy
788 and (a.DEAL_TYPE = l_deal_type
789 or a.DEAL_TYPE is NULL)
790 and (a.DEAL_SUBTYPE = l_subtype
791 or a.DEAL_SUBTYPE is NULL)
792 and (a.PRODUCT_TYPE = l_product
793 or a.PRODUCT_TYPE is NULL)
794 and (a.AMOUNT_TYPE = l_amount_type
795 or a.AMOUNT_TYPE is NULL)
796 and a.PARTY_CODE = b.PARTY_CODE
797 and a.CURRENCY = b.CURRENCY
798 and a.ACCOUNT_NO = b.ACCOUNT_NUMBER
799 and nvl(b.AUTHORISED,'N') = 'Y'
800 order by a.DEAL_TYPE,a.DEAL_SUBTYPE,a.PRODUCT_TYPE,a.AMOUNT_TYPE;
801 --
802 l_dummy NUMBER;
803 --
804 cursor NO_DFLT_ACCT is
805 select a.BANK_SHORT_CODE,a.ACCOUNT_NUMBER
806 from XTR_BANK_ACCOUNTS a
807 where a.PARTY_CODE = l_party
808 and a.CURRENCY = l_ccy
809 and nvl(a.AUTHORISED,'N') = 'Y'
810 --* bug #1723491, rravunny
811 --* default account column should also be checked
812 and Nvl(a.Default_Acct,'N') = 'Y'
813 --* end of fix
814 order by nvl(a.DEFAULT_ACCT,'N') desc;
815 --
816 begin
817 if l_party is NOT NULL and l_ccy is NOT NULL then
818 open DFLT_ACCT;
819 fetch DFLT_ACCT INTO l_cparty_ref,l_account;
820 if DFLT_ACCT%NOTFOUND then
821 close DFLT_ACCT;
822 open NO_DFLT_ACCT;
823 fetch NO_DFLT_ACCT INTO l_cparty_ref,l_account;
824 IF NO_DFLT_ACCT%NOTFOUND then
825 l_cparty_ref := NULL;
826 l_account := NULL;
827 END IF;
828 close NO_DFLT_ACCT;
829 end if;
830 IF DFLT_ACCT%ISOPEN then
831 close DFLT_ACCT;
832 END IF;
833 end if;
834 end STANDING_SETTLEMENTS;
835
836 ---------------------------------
837 -- Local Procedure to default client setup information
838 PROCEDURE TAX_BROKERAGE_DEFAULTS(l_deal_type IN VARCHAR2,
839 l_subtype IN VARCHAR2,
840 l_product IN VARCHAR2,
841 l_ref_party IN VARCHAR2,
842 l_prin_settled_by IN OUT NOCOPY VARCHAR2,
843 l_bkr_ref IN OUT NOCOPY VARCHAR2,
844 l_tax_ref IN OUT NOCOPY VARCHAR2,
845 l_int_settled_by IN OUT NOCOPY VARCHAR2,
846 l_int_freq IN OUT NOCOPY VARCHAR2,
847 l_bkr_amt_type IN OUT NOCOPY VARCHAR2,
848 l_tax_amt_type IN OUT NOCOPY VARCHAR2) is
849 --
850 -- l_deal_date IN DATE,
851 -- l_ref_amount IN NUMBER,
852 -- l_bkr_rate IN OUT NOCOPY NUMBER,
853 -- l_bkr_amount IN OUT NOCOPY NUMBER,
854 -- l_tax_rate IN OUT NOCOPY NUMBER,
855 --
856 l_dmmy_num NUMBER;
857 l_dmmy_char VARCHAR2(20);
858 --
859 cursor SETTLE_DFLTS is
860 select pd.PRINCIPAL_SETTLED_BY,
861 pd.INTEREST_SETTLED_BY,
862 pd.FREQ_INTEREST_SETTLED
863 from XTR_PARTY_DEFAULTS pd,
864 XTR_PARTIES_V p
865 where p.PARTY_CODE = l_ref_party
866 and pd.SETTLEMENT_DEFAULT_CATEGORY = p.SETTLEMENT_DEFAULT_CATEGORY
867 and pd.DEFAULT_TYPE = 'S'
868 and pd.DEAL_TYPE = l_deal_type
869 and (pd.DEAL_SUBTYPE = l_subtype or pd.DEAL_SUBTYPE is NULL)
870 and (pd.PRODUCT_TYPE = l_product or pd.PRODUCT_TYPE is NULL)
871 and (pd.PARTY_CODE = l_ref_party or pd.PARTY_CODE is NULL)
872 order by pd.DEAL_TYPE,pd.DEAL_SUBTYPE,pd.PRODUCT_TYPE;
873 --
874 cursor BROKER_DFLTS is
875 --select b.BROKERAGE_REFERENCE,nvl(d.INTEREST_RATE,0),d.FLAT_AMOUNT
876 select b.BROKERAGE_REFERENCE, d.AMOUNT_TYPE
877 from XTR_PARTY_DEFAULTS b,
878 XTR_PARTIES_V p,
879 XTR_TAX_BROKERAGE_SETUP a,
880 XTR_DEDUCTION_CALCS_V d
881 where p.PARTY_CODE = l_ref_party
882 and b.BROKERAGE_CATEGORY = p.BROKERAGE_CATEGORY
883 and b.DEFAULT_TYPE = 'B'
884 and b.DEAL_TYPE = l_deal_type
885 and (b.DEAL_SUBTYPE = l_subtype or b.DEAL_SUBTYPE is NULL)
886 and (b.PRODUCT_TYPE = l_product or b.PRODUCT_TYPE is NULL)
887 and (b.PARTY_CODE = l_ref_party or b.PARTY_CODE is NULL)
888 and nvl(a.AUTHORISED,'N')= 'Y'
889 and a.REFERENCE_CODE = b.BROKERAGE_REFERENCE
890 and a.DEAL_TYPE = l_deal_type
891 and d.DEAL_TYPE = a.DEAL_TYPE
892 and d.CALC_TYPE = a.CALC_TYPE
893 order by b.PARTY_CODE;
894 --
895 cursor TAX_DFLTS is
896 --select b.TAX_REFERENCE, d.INTEREST_RATE
897 select b.TAX_REFERENCE, d.AMOUNT_TYPE
898 from XTR_PARTY_DEFAULTS b,
899 XTR_PARTIES_V p,
900 XTR_TAX_BROKERAGE_SETUP a,
901 XTR_DEDUCTION_CALCS_V d
902 where p.PARTY_CODE = l_ref_party
903 and b.TAX_CATEGORY = p.TAX_CATEGORY
904 and b.DEFAULT_TYPE = 'T'
905 and b.DEAL_TYPE = l_deal_type
906 and (b.DEAL_SUBTYPE = l_subtype or b.DEAL_SUBTYPE is NULL)
907 and (b.PRODUCT_TYPE = l_product or b.PRODUCT_TYPE is NULL)
908 and (b.PARTY_CODE = l_ref_party or b.PARTY_CODE is NULL)
909 and nvl(a.AUTHORISED,'N')= 'Y'
910 and a.REFERENCE_CODE = b.TAX_REFERENCE
911 and a.DEAL_TYPE = l_deal_type
912 and d.DEAL_TYPE = a.DEAL_TYPE
913 and d.CALC_TYPE = a.CALC_TYPE
914 order by b.PARTY_CODE;
915 --
916 begin
917 open SETTLE_DFLTS;
918 fetch SETTLE_DFLTS INTO l_prin_settled_by,l_int_settled_by,l_int_freq;
919 if SETTLE_DFLTS%NOTFOUND then
920 l_prin_settled_by := 'D';
921 l_int_settled_by := 'D';
922 l_int_freq := 'M';
923 end if;
924 close SETTLE_DFLTS;
925 --
926 open BROKER_DFLTS;
927 --fetch BROKER_DFLTS INTO l_bkr_ref,l_bkr_rate,l_bkr_amount;
928 fetch BROKER_DFLTS INTO l_bkr_ref,l_bkr_amt_type;
929 close BROKER_DFLTS;
930 --
931 open TAX_DFLTS;
932 --fetch TAX_DFLTS INTO l_tax_ref,l_tax_rate;
933 fetch TAX_DFLTS INTO l_tax_ref,l_tax_amt_type;
934 close TAX_DFLTS;
935 end TAX_BROKERAGE_DEFAULTS;
936 --
937 ----------------------------------------------------------------------------------------------------------------
938 -- Local Procedure to default client setup information
939 /*****************************************************************************/
940 -- This procedure overrides the above procedure, tax_brokerage_defaults,
941 -- for defaulting tax/brokerage values. As tax features are added to
942 -- deals, this procedure should replace tax_brokerage_defaults.
943 -- Parameters:
944 -- l_deal_type = deal type
945 -- l_subtype = deal subtype
946 -- l_product = product type
947 -- l_ref_party = client code or counterparty code
948 -- l_prin_settled_by = principal settled by
949 -- l_bkr_ref = defaulted brokerage reference code
950 -- l_prin_tax_ref = defaulted principal tax schedule code
951 -- l_income_tax_ref = defaulted income tax schedule code
952 -- l_ccy = for FX, input as buy currency, output as tax currency;
953 -- else deal currency
954 -- l_sell_ccy = for FX, sell currency; else null
955 -- l_int_settled_by = interest settled by
956 -- l_int_freq = interest frequency
957 -- l_bkr_amt_type = brokerage amount type; null if l_bkr_ref is null
958
959 PROCEDURE TAX_BROKERAGE_DEFAULTING(l_deal_type IN VARCHAR2,
960 l_subtype IN VARCHAR2,
961 l_product IN VARCHAR2,
962 l_ref_party IN VARCHAR2,
963 l_prin_settled_by IN OUT NOCOPY VARCHAR2,
964 l_bkr_ref IN OUT NOCOPY VARCHAR2,
965 l_prin_tax_ref IN OUT NOCOPY VARCHAR2,
966 l_income_tax_ref IN OUT NOCOPY VARCHAR2,
967 -- for FX deals, inputted as buy ccy
968 -- outputted as tax ccy
969 l_ccy IN OUT NOCOPY VARCHAR2,
970 l_sell_ccy IN VARCHAR2,
971 l_int_settled_by IN OUT NOCOPY VARCHAR2,
972 l_int_freq IN OUT NOCOPY VARCHAR2,
973 l_bkr_amt_type IN OUT NOCOPY VARCHAR2) is
974 --
975 -- l_deal_date IN DATE,
976 -- l_ref_amount IN NUMBER,
977 -- l_bkr_rate IN OUT NOCOPY NUMBER,
978 -- l_bkr_amount IN OUT NOCOPY NUMBER,
979 -- l_tax_rate IN OUT NOCOPY NUMBER,
980 --
981 l_dmmy_num NUMBER;
982 l_dmmy_char VARCHAR2(20);
983
984 l_calc_type VARCHAR2(9);
985
986 --
987 cursor SETTLE_DFLTS is
988 select pd.PRINCIPAL_SETTLED_BY,
989 pd.INTEREST_SETTLED_BY,
990 pd.FREQ_INTEREST_SETTLED
991 from XTR_PARTY_DEFAULTS pd,
992 XTR_PARTIES_V p
993 where p.PARTY_CODE = l_ref_party
994 and pd.SETTLEMENT_DEFAULT_CATEGORY = p.SETTLEMENT_DEFAULT_CATEGORY
995 and pd.DEFAULT_TYPE = 'S'
996 and pd.DEAL_TYPE = l_deal_type
997 and (pd.DEAL_SUBTYPE = l_subtype or pd.DEAL_SUBTYPE is NULL)
998 and (pd.PRODUCT_TYPE = l_product or pd.PRODUCT_TYPE is NULL)
999 and (pd.PARTY_CODE = l_ref_party or pd.PARTY_CODE is NULL)
1000 order by pd.DEAL_TYPE,pd.DEAL_SUBTYPE,pd.PRODUCT_TYPE;
1001 --
1002 cursor BROKER_DFLTS is
1003 --select b.BROKERAGE_REFERENCE,nvl(d.INTEREST_RATE,0),d.FLAT_AMOUNT
1004 select b.BROKERAGE_REFERENCE, d.AMOUNT_TYPE
1005 from XTR_PARTY_DEFAULTS b,
1006 XTR_PARTIES_V p,
1007 XTR_TAX_BROKERAGE_SETUP a,
1008 XTR_DEDUCTION_CALCS_V d
1009 where p.PARTY_CODE = l_ref_party
1010 and b.BROKERAGE_CATEGORY = p.BROKERAGE_CATEGORY
1011 and b.DEFAULT_TYPE = 'B'
1012 and b.DEAL_TYPE = l_deal_type
1013 and (b.DEAL_SUBTYPE = l_subtype or b.DEAL_SUBTYPE is NULL)
1014 and (b.PRODUCT_TYPE = l_product or b.PRODUCT_TYPE is NULL)
1015 and (b.PARTY_CODE = l_ref_party or b.PARTY_CODE is NULL)
1016 and nvl(a.AUTHORISED,'N')= 'Y'
1017 and a.REFERENCE_CODE = b.BROKERAGE_REFERENCE
1018 and a.DEAL_TYPE = l_deal_type
1019 and d.DEAL_TYPE = a.DEAL_TYPE
1020 and d.CALC_TYPE = a.CALC_TYPE
1021 order by b.PARTY_CODE;
1022 --
1023 cursor TAX_DFLTS is
1024 --select b.TAX_REFERENCE, d.INTEREST_RATE
1025 select b.TAX_REFERENCE, b.INCOME_TAX_REFERENCE, d.CALC_TYPE
1026 from XTR_PARTY_DEFAULTS b,
1027 XTR_PARTIES_V p,
1028 XTR_TAX_BROKERAGE_SETUP a,
1029 XTR_TAX_DEDUCTION_CALCS_V d
1030 where p.PARTY_CODE = l_ref_party
1031 and b.TAX_CATEGORY = p.TAX_CATEGORY
1032 and b.DEFAULT_TYPE = 'T'
1033 and b.DEAL_TYPE = l_deal_type
1034 and (b.DEAL_SUBTYPE = l_subtype or b.DEAL_SUBTYPE is NULL)
1035 and (b.PRODUCT_TYPE = l_product or b.PRODUCT_TYPE is NULL)
1036 and (b.PARTY_CODE = l_ref_party or b.PARTY_CODE is NULL)
1037 and nvl(a.AUTHORISED,'N')= 'Y'
1038 and a.DEAL_TYPE = l_deal_type
1039 and d.DEAL_TYPE = a.DEAL_TYPE
1040 and d.CALC_TYPE = a.CALC_TYPE
1041 order by b.DEAL_TYPE, b.DEAL_SUBTYPE, nvl(b.PRODUCT_TYPE, '');
1042 --
1043 begin
1044 open SETTLE_DFLTS;
1045 fetch SETTLE_DFLTS INTO l_prin_settled_by,l_int_settled_by,l_int_freq;
1046 if SETTLE_DFLTS%NOTFOUND then
1047 l_prin_settled_by := 'D';
1048 l_int_settled_by := 'D';
1049 l_int_freq := 'M';
1050 end if;
1051 close SETTLE_DFLTS;
1052 --
1053 open BROKER_DFLTS;
1054 --fetch BROKER_DFLTS INTO l_bkr_ref,l_bkr_rate,l_bkr_amount;
1055 fetch BROKER_DFLTS INTO l_bkr_ref,l_bkr_amt_type;
1056 close BROKER_DFLTS;
1057 --
1058 open TAX_DFLTS;
1059 --fetch TAX_DFLTS INTO l_tax_ref,l_tax_rate;
1060 fetch TAX_DFLTS INTO l_prin_tax_ref, l_income_tax_ref, l_calc_type;
1061 close TAX_DFLTS;
1062
1063 if (l_deal_type='FX') then
1064 if (l_calc_type = 'SELL_F') then
1065 l_ccy := l_sell_ccy;
1066 end if;
1067 end if;
1068
1069 end TAX_BROKERAGE_DEFAULTING;
1070 --
1071 ----------------------------------------------------------------------------------------------------------------
1072 -- Procedure to find amount type for the broker and tax reference
1073 PROCEDURE TAX_BROKERAGE_AMT_TYPE(l_deal_type IN VARCHAR2,
1074 l_bkr_ref IN VARCHAR2,
1075 l_tax_ref IN VARCHAR2,
1076 l_bkr_amt_type IN OUT NOCOPY VARCHAR2,
1077 l_tax_amt_type IN OUT NOCOPY VARCHAR2) is
1078 --
1079 cursor BKR_AMT_TYPE is
1080 select d.AMOUNT_TYPE
1081 from XTR_TAX_BROKERAGE_SETUP a,
1082 XTR_DEDUCTION_CALCS_V d
1083 where a.DEAL_TYPE = l_deal_type
1084 and a.REFERENCE_CODE = l_bkr_ref
1085 and nvl(a.AUTHORISED,'N')= 'Y'
1086 and d.DEAL_TYPE = a.DEAL_TYPE
1087 and d.CALC_TYPE = a.CALC_TYPE;
1088 --
1089 cursor TAX_AMT_TYPE is
1090 select d.AMOUNT_TYPE
1091 from XTR_TAX_BROKERAGE_SETUP a,
1092 XTR_DEDUCTION_CALCS_V d
1093 where a.DEAL_TYPE = l_deal_type
1094 and a.REFERENCE_CODE = l_tax_ref
1095 and nvl(a.AUTHORISED,'N')= 'Y'
1096 and d.DEAL_TYPE = a.DEAL_TYPE
1097 and d.CALC_TYPE = a.CALC_TYPE;
1098 --
1099 begin
1100 open BKR_AMT_TYPE;
1101 fetch BKR_AMT_TYPE INTO l_bkr_amt_type;
1102 close BKR_AMT_TYPE;
1103 --
1104 open TAX_AMT_TYPE;
1105 fetch TAX_AMT_TYPE INTO l_tax_amt_type;
1106 close TAX_AMT_TYPE;
1107 --
1108 end TAX_BROKERAGE_AMT_TYPE;
1109
1110 ----------------------------------------------------------------------------------------------------------------
1111 /******** One Step Settlement Method ****************/
1112 Procedure One_Step_Settlement (p_one_step_rec IN OUT NOCOPY one_step_rec_type)
1113 is
1114 v_deal_error Boolean;
1115 v_user_error Boolean;
1116 v_duplicate_error Boolean;
1117 v_mandatory_error Boolean;
1118 v_validation_error Boolean;
1119 v_limit_error Boolean;
1120
1121 v_exp_rec Xtr_Exposure_Transactions%Rowtype;
1122 v_cparty Xtr_Tax_Brokerage_Setup_V.Payee%Type;
1123 v_settlement_code Xtr_One_Step_Settle_Codes.Settlement_Code%Type;
1124 v_amount_hce Xtr_Exposure_Transactions.Amount_HCE%Type;
1125 v_comments Xtr_Exposure_Transactions.Comments%Type;
1126 v_settle_method Xtr_Tax_Brokerage_Setup.Tax_Settle_Method%Type;
1127 v_party_type Xtr_Parties_V.Party_Type%Type;
1128
1129 cursor CALC_HCE_AMTS is
1130 select round(abs(p_one_step_rec.p_amount) / s.HCE_RATE,2)
1131 from XTR_MASTER_CURRENCIES_V s
1132 where s.CURRENCY = upper(p_one_step_rec.p_CURRENCY);
1133
1134 BEGIN
1135 /**** for a given schedule code what is the tax settle method defined in setup is derived here *****/
1136
1137 Begin
1138 Select tax_settle_method
1139 Into v_settle_method
1140 From Xtr_Tax_Brokerage_Setup_V
1141 Where reference_code = p_one_step_rec.p_schedule_code;
1142 Exception
1143 When no_data_found then
1144 null;
1145 End;
1146
1147 p_one_step_rec.p_settle_method := v_settle_method;
1148
1149 -- Do not proceed if the schedule settle method is not of One Step
1150 -- Settlement. The only exception is if the caller coming from ONC
1151 -- (p_source = 'TAX_CP_G') and the reneg interest action is
1152 -- Compound Gross and the settlement method is not NIA.
1153 If (v_settle_method <> 'OSG' and
1154 (p_one_step_rec.p_source <> 'TAX_CP_G' or
1155 v_settle_method = 'NIA')) then
1156 return;
1157 End if;
1158
1159 /***** bug # 2488461 issue 7
1160 settlement code defined during setup is obtained here. if not found, then error out *****/
1161 Begin
1162 Select settlement_code
1163 Into v_settlement_code
1164 From XTR_ONE_STEP_SETTLE_CODES_V
1165 Where company_code = p_one_step_rec.p_company_code
1166 And schedule = p_one_step_rec.p_schedule_code;
1167 Exception
1168 When NO_DATA_FOUND then
1169 /***** if no settlement code is found for this schedule, then
1170 v_settlement_code is equated to null ********/
1171 v_settlement_code := null;
1172
1173 When OTHERS then
1174 null;
1175 End;
1176
1177 If v_settlement_code is null then
1178 /******* bug 2488461 issue 7
1179 if for the given schedule code no settlement code is found then,
1180 get settlement code which has no schedule defined ****/
1181 Begin
1182 Select settlement_code
1183 Into v_settlement_code
1184 From XTR_ONE_STEP_SETTLE_CODES_V
1185 Where company_code = p_one_step_rec.p_company_code
1186 And schedule is null;
1187 Exception
1188 When NO_DATA_FOUND then
1189 p_one_step_rec.p_error := 'XTR_MISSING_SETTLE_CODE';
1190 return;
1191 End;
1192 End if;
1193
1194 /***** settlement code name is provided to comments column ****/
1195 Begin
1196 Select name
1197 Into v_comments
1198 From Xtr_Exposure_Types_V
1199 Where Exposure_Type = v_settlement_code
1200 And Company_Code = p_one_step_rec.p_company_code;
1201 Exception
1202 When no_data_found then
1203 p_one_step_rec.p_error := 'XTR_MISSING_SETTLE_CODE_NAME';
1204 return;
1205 End;
1206
1207 /**** amount hce is calculated here *****/
1208 open CALC_HCE_AMTS;
1209 fetch CALC_HCE_AMTS INTO v_amount_hce;
1210 if CALC_HCE_AMTS%NOTFOUND then
1211 v_amount_hce := 0;
1212 close CALC_HCE_AMTS;
1213 end if;
1214 close CALC_HCE_AMTS;
1215
1216 /*************Exp api preparatory ***********************/
1217 v_exp_rec.company_code := p_one_step_rec.p_company_code;
1218 v_exp_rec.DEAL_TYPE := 'EXP';
1219 v_exp_rec.DEAL_SUBTYPE := 'FIRM';
1220 v_exp_rec.EXPOSURE_TYPE := v_settlement_code;
1221 v_exp_rec.CURRENCY := p_one_step_rec.p_Currency;
1222 v_exp_rec.VALUE_DATE := p_one_step_rec.p_settlement_date ;
1223 v_exp_rec.amount := abs(p_one_step_rec.p_Amount);
1224 v_exp_rec.SETTLE_ACTION_REQD := 'Y';
1225 v_exp_rec.amount_hce := abs(v_Amount_Hce);
1226 v_exp_rec.THIRDPARTY_CODE := p_one_step_rec.p_Cparty_Code;
1227 v_exp_rec.action_code := 'PAY';
1228 v_exp_rec.AMOUNT_TYPE := 'AMOUNT';
1229 v_exp_rec.account_no := p_one_step_rec.p_Settlement_Account;
1230 v_exp_rec.TAX_BROKERAGE_TYPE := 'Y';
1231 v_exp_rec.COMMENTS := v_comments;
1232 v_exp_rec.CPARTY_ACCOUNT_NO := p_one_step_rec.p_cparty_account_no;
1233
1234 /***** EXP open API which inserts into Xtr_Exposure_Transactions and Xtr_Deal_Date_Amounts ******/
1235
1236 XTR_EXP_TRANSFERS_PKG.TRANSFER_EXP_DEALS(v_exp_rec,
1237 p_one_step_rec.p_source,
1238 v_user_error,
1239 v_mandatory_error,
1240 v_validation_error,
1241 v_limit_error);
1242 /**** exp transaction number generated in EXP API is passed out to the calling form *****/
1243 p_one_step_rec.p_exp_number := v_exp_rec.Transaction_Number;
1244
1245 END One_Step_Settlement;
1246
1247 ----------------------------------------------------------------------------------------------------------------
1248 -- Procedure to update / delete existing journal entries
1249 -- when cancelling a deal(s).
1250 PROCEDURE UPDATE_JOURNALS (l_deal_nos IN NUMBER,
1251 l_trans_nos IN NUMBER,
1252 l_deal_type IN VARCHAR2) is
1253 --
1254 l_sysdate DATE;
1255 l_user VARCHAR2(30);
1256 --
1257 /* AW Bug 1216835
1258 cursor TOD_DATE is
1259 select sysdate,user
1260 from DUAL;
1261 */
1262
1263 cursor TOD_DATE is
1264 select sysdate, dealer_code
1265 from xtr_dealer_codes_v
1266 where user_id = fnd_global.user_id;
1267
1268 --
1269 begin
1270 null;
1271 /* move to db_trigger on deals,rollover_transactions
1272 open TOD_DATE;
1273 fetch TOD_DATE INTO l_sysdate,l_user;
1274 close TOD_DATE;
1275 -- Update rows in journals where journal HAS NOT been transferred to
1276 -- the General Ledger. Set the cancelled_in_gl to Y.
1277 update JOURNALS
1278 set JNL_REVERSAL_IND = 'C',
1279 CANCELLED_IN_GL = 'Y'
1280 where DEAL_NUMBER = l_deal_nos
1281 and TRANSACTION_NUMBER = l_trans_nos
1282 and DEAL_TYPE = l_deal_type
1283 and GL_TRANSFER_DATE is null;
1284 --
1285 -- Update rows in journals where journal HAS been transferred to the
1286 -- General Ledger, this indicates that the journal requires reversal.
1287 update JOURNALS
1288 set JNL_REVERSAL_IND = 'Y',
1289 UPDATED_ON = l_sysdate,
1290 UPDATED_BY = l_user
1291 where DEAL_NUMBER = l_deal_nos
1292 and TRANSACTION_NUMBER = l_trans_nos
1293 and DEAL_TYPE = l_deal_type
1294 and GL_TRANSFER_DATE is not null;
1295 */
1296 end UPDATE_JOURNALS;
1297
1298 ---------------------------------------------------------------
1299 /*************************************************************/
1300 /* Updates transaction's floating rate based on the deal's */
1301 /* Benchmark Rate and Margin. */
1302 /* The parameters are passed in through concurrent program. */
1303 /*************************************************************/
1304 PROCEDURE RESET_FLOATING_RATES(errbuf OUT NOCOPY VARCHAR2,
1305 retcode OUT NOCOPY NUMBER,
1306 p_rateset_from IN VARCHAR2,
1307 p_rateset_to IN VARCHAR2,
1308 p_rateset_adj IN NUMBER,
1309 p_deal_type IN VARCHAR2,
1310 p_company IN VARCHAR2,
1311 p_cparty IN VARCHAR2,
1312 p_portfolio IN VARCHAR2,
1313 p_currency IN VARCHAR2,
1314 p_ric_code IN VARCHAR2,
1315 p_source IN VARCHAR2) IS
1316
1317 l_buf VARCHAR2(300);
1318 l_rowid VARCHAR2(30);
1319 l_company XTR_DEALS.COMPANY_CODE%TYPE;
1320 l_deal_no NUMBER;
1321 l_tran_no NUMBER;
1322 l_deal_type XTR_DEALS.DEAL_TYPE%TYPE;
1323 l_start_date DATE;
1324 l_ratefix_date DATE;
1325 l_ric_code XTR_DEALS.RATE_BASIS%TYPE;
1326 l_margin NUMBER;
1327 l_new_rate NUMBER;
1328 l_rate NUMBER;
1329 l_valid_ok BOOLEAN;
1330 l_error NUMBER;
1331 l_count NUMBER:= 0;
1332 l_hold VARCHAR2(1);
1333
1334 /*-------------------------------------------------*/
1335 /* Selection criteria for transactions : */
1336 /* - only companies that are authorised to user */
1337 /* - only TMM and IRS deals with Benchmark Rate */
1338 /* - RATE_FIXING_DATE is not null on transaction */
1339 /* - RATE_FIXING_DATE within parameter date range */
1340 /*-------------------------------------------------*/
1341 cursor curr_all_tran is
1342 select distinct a.ROWID,
1343 a.COMPANY_CODE,
1344 a.DEAL_NUMBER,
1345 a.TRANSACTION_NUMBER,
1346 a.DEAL_TYPE,
1347 a.START_DATE,
1348 a.RATE_FIXING_DATE,
1349 b.RATE_BASIS,
1350 nvl(b.MARGIN,0)
1351 from XTR_ROLLOVER_TRANSACTIONS a,
1352 XTR_DEALS b,
1353 XTR_PARTIES_V c
1354 where a.deal_type = NVL(p_deal_type,a.deal_type)
1355 and a.deal_type in ('TMM','IRS')
1356 and a.company_code = NVL(p_company,a.company_code)
1357 and a.company_code = c.party_code -- user access
1358 and a.cparty_code = NVL(p_cparty,a.cparty_code)
1359 and a.portfolio_code = NVL(p_portfolio,a.portfolio_code)
1360 and a.currency = NVL(p_currency,a.currency)
1361 and a.deal_number = b.deal_no
1362 and a.rate_fixing_date is not null
1363 and a.rate_fixing_date between fnd_date.canonical_to_date(p_rateset_from)
1364 and fnd_date.canonical_to_date(p_rateset_to)
1365 and b.rate_basis is not null
1366 and b.rate_basis = nvl(p_ric_code, b.rate_basis)
1367 and b.STATUS_CODE = 'CURRENT'
1368 order by a.deal_type, a.deal_number,
1369 a.start_date, a.transaction_number;
1370
1371 -- The following cursor will determine whether the record is the
1372 -- latest transaction with the same deal number. If no data found, then
1373 -- it's the latest transactions and we want to update the subsequent row.
1374 -- If something found, then we only want to update one row.
1375 cursor C_ONE_ROW is
1376 select 'Y'
1377 from xtr_rollover_transactions
1378 where deal_number = l_deal_no
1379 and transaction_number = l_tran_no
1380 and rate_fixing_date <
1381 (select max(rate_fixing_date)
1382 from xtr_rollover_transactions
1383 where deal_number = l_deal_no
1384 and rate_fixing_date between fnd_date.canonical_to_date(p_rateset_from)
1385 and fnd_date.canonical_to_date(p_rateset_to));
1386
1387 Begin
1388
1389 retcode := 0;
1390 -- Modified Bug 4514808
1391 if fnd_date.canonical_to_date(p_rateset_from) > sysdate then
1392 retcode := 2;
1393 FND_MESSAGE.SET_NAME('XTR', 'XTR_RESET_DATE_FROM');
1394 l_buf := FND_MESSAGE.GET;
1395 fnd_file.put_line(fnd_file.log, l_buf);
1396 end if;
1397 if fnd_date.canonical_to_date(p_rateset_to) > sysdate then
1398 retcode := 2;
1399 FND_MESSAGE.SET_NAME('XTR', 'XTR_RESET_DATE_TO');
1400 l_buf := FND_MESSAGE.GET;
1401 fnd_file.put_line(fnd_file.log, l_buf);
1402 end if;
1403 If(retcode = 0)then
1404 OPEN curr_all_tran;
1405 LOOP
1406 FETCH curr_all_tran into l_rowid, l_company,
1407 l_deal_no, l_tran_no,
1408 l_deal_type, l_start_date,
1409 l_ratefix_date, l_ric_code,
1410 l_margin;
1411 EXIT when curr_all_tran%notfound;
1412 l_count := l_count +1;
1413
1414 VALIDATE_TRANSACTION (l_company,
1415 l_deal_no,
1416 l_deal_type,
1417 l_start_date,
1418 l_valid_ok,
1419 l_error);
1420
1421 if l_valid_ok then
1422 GET_BENCHMARK_RATE(l_ric_code,
1423 l_ratefix_date,
1424 nvl(p_rateset_adj,0),
1425 l_new_rate);
1426
1427 if l_new_rate is not null then
1428 open C_ONE_ROW;
1429 fetch C_ONE_ROW into l_hold;
1430 if C_ONE_ROW%FOUND then -- update only one row
1431 UPDATE_RATE_ONE_TRANSACTION(l_deal_no,
1432 l_tran_no,
1433 l_deal_type,
1434 l_start_date,
1435 l_new_rate + (l_margin/100));
1436 l_rate := l_new_rate + (l_margin/100);
1437 FND_MESSAGE.SET_NAME('XTR', 'XTR_UPDATE_BENCH_RATE');
1438 FND_MESSAGE.SET_TOKEN('BENCH_RATE', l_rate);
1439 FND_MESSAGE.SET_TOKEN('DEAL_TYPE', l_deal_type);
1440 FND_MESSAGE.SET_TOKEN('DEAL_NO', l_deal_no);
1441 FND_MESSAGE.SET_TOKEN('TRANS_NO', l_tran_no);
1442 FND_MESSAGE.SET_TOKEN('RATE_DATE', l_ratefix_date);
1443 l_buf := FND_MESSAGE.GET;
1444 fnd_file.put_line(fnd_file.log, l_buf);
1445 else --update current record as well as subsequent transactions
1446 UPDATE_RATE_SEQ_TRANSACTION(l_deal_no,
1447 l_tran_no,
1448 l_deal_type,
1449 l_start_date,
1450 l_new_rate + (l_margin/100));
1451 l_rate := l_new_rate + (l_margin/100) ;
1452 FND_MESSAGE.SET_NAME('XTR', 'XTR_UPDATE_BENCH_SEQ_RATE');
1453 FND_MESSAGE.SET_TOKEN('BENCH_RATE', l_rate);
1454 FND_MESSAGE.SET_TOKEN('DEAL_TYPE', l_deal_type);
1455 FND_MESSAGE.SET_TOKEN('DEAL_NO', l_deal_no);
1456 FND_MESSAGE.SET_TOKEN('TRANS_NO', l_tran_no);
1457 FND_MESSAGE.SET_TOKEN('RATE_DATE', l_ratefix_date);
1458 l_buf := FND_MESSAGE.GET;
1459 fnd_file.put_line(fnd_file.log, l_buf);
1460 end if;
1461 close C_ONE_ROW;
1462 else
1463 retcode := 1;
1464 FND_MESSAGE.SET_NAME('XTR', 'XTR_NO_BENCH_RATE');
1465 FND_MESSAGE.SET_TOKEN('DEAL_TYPE', l_deal_type);
1466 FND_MESSAGE.SET_TOKEN('DEAL_NO', l_deal_no);
1467 FND_MESSAGE.SET_TOKEN('TRANS_NO', l_tran_no);
1468 FND_MESSAGE.SET_TOKEN('RATE_DATE', l_ratefix_date);
1469 l_buf := FND_MESSAGE.GET;
1470 fnd_file.put_line(fnd_file.log, l_buf);
1471 end if;
1472 else -- do not pass validation. return error message
1473 retcode := 1;
1474 if l_error = 1 then -- deal been settled
1475 FND_MESSAGE.SET_NAME('XTR', 'XTR_BENCH_SETTLE');
1476 FND_MESSAGE.SET_TOKEN('DEAL_TYPE', l_deal_type);
1477 FND_MESSAGE.SET_TOKEN('DEAL_NO', l_deal_no);
1478 FND_MESSAGE.SET_TOKEN('TRANS_NO', l_tran_no);
1479 FND_MESSAGE.SET_TOKEN('RATE_DATE', l_ratefix_date);
1480 l_buf := FND_MESSAGE.GET;
1481 fnd_file.put_line(fnd_file.log, l_buf);
1482 elsif l_error = 2 then -- Accrual has been generated
1483 FND_MESSAGE.SET_NAME('XTR', 'XTR_BENCH_ACCRUAL');
1484 FND_MESSAGE.SET_TOKEN('DEAL_TYPE', l_deal_type);
1485 FND_MESSAGE.SET_TOKEN('DEAL_NO', l_deal_no);
1486 FND_MESSAGE.SET_TOKEN('TRANS_NO', l_tran_no);
1487 FND_MESSAGE.SET_TOKEN('RATE_DATE', l_ratefix_date);
1488 l_buf := FND_MESSAGE.GET;
1489 fnd_file.put_line(fnd_file.log, l_buf);
1490 elsif l_error = 3 then -- revalaution has been done
1491 FND_MESSAGE.SET_NAME('XTR', 'XTR_BENCH_REVAL');
1492 FND_MESSAGE.SET_TOKEN('DEAL_TYPE', l_deal_type);
1493 FND_MESSAGE.SET_TOKEN('DEAL_NO', l_deal_no);
1494 FND_MESSAGE.SET_TOKEN('TRANS_NO', l_tran_no);
1495 FND_MESSAGE.SET_TOKEN('RATE_DATE', l_ratefix_date);
1496 l_buf := FND_MESSAGE.GET;
1497 fnd_file.put_line(fnd_file.log, l_buf);
1498 elsif l_error = 4 then -- journal has been generated
1499 FND_MESSAGE.SET_NAME('XTR', 'XTR_BENCH_JOURNAL');
1500 FND_MESSAGE.SET_TOKEN('DEAL_TYPE', l_deal_type);
1501 FND_MESSAGE.SET_TOKEN('DEAL_NO', l_deal_no);
1502 FND_MESSAGE.SET_TOKEN('TRANS_NO', l_tran_no);
1503 FND_MESSAGE.SET_TOKEN('RATE_DATE', l_ratefix_date);
1504 l_buf := FND_MESSAGE.GET;
1505 fnd_file.put_line(fnd_file.log, l_buf);
1506 end if;
1507 end if; -- valid OK
1508 END LOOP;
1509 CLOSE curr_all_tran;
1510
1511 if l_count = 0 then
1512 retcode := 1;
1513 -- No deals/transactions were found using the specified search criteria.
1514 FND_MESSAGE.SET_NAME('XTR', 'XTR_NO_ELIGI_BENCH');
1515 l_buf := FND_MESSAGE.GET;
1516 fnd_file.put_line(fnd_file.log, l_buf);
1517 end if;
1518 end if; -- Bug 4514808
1519 End RESET_FLOATING_RATES;
1520
1521 --------------------------------------------------
1522 PROCEDURE VALIDATE_TRANSACTION(p_company IN VARCHAR2,
1523 p_deal_no IN NUMBER,
1524 p_deal_type IN VARCHAR2,
1525 p_start_date IN DATE,
1526 p_valid_ok OUT NOCOPY BOOLEAN,
1527 p_error OUT NOCOPY NUMBER)IS
1528
1529 l_error BOOLEAN;
1530 l_date DATE:= null;
1531
1532
1533 cursor check_settlement_done is
1534 select nvl(min(amount_date),p_start_date) -- Ilavenil modified for witholding tax project
1535 from xtr_deal_date_amounts
1536 where company_code = p_company
1537 and deal_number = p_deal_no
1538 and settle = 'Y'
1539 and amount_date > p_start_date
1540 /******** Ilavenil modified for witholding tax *********/
1541 union
1542 select nvl(min(a.amount_date),p_start_date) -- Ilavenil modified for witholding tax project
1543 from xtr_deal_date_amounts a,
1544 xtr_rollover_transactions b
1545 where b.company_code = p_company
1546 and b.deal_number = p_deal_no
1547 and (b.tax_settled_reference is not null)
1548 and a.company_code = p_company
1549 and a.deal_type = 'EXP'
1550 and a.transaction_number in (b.tax_settled_reference)
1551 and nvl(a.settle,'N') = 'Y'
1552 and b.maturity_date > p_start_date
1553 order by 1 desc;
1554 /**********/
1555
1556
1557 /********* cursor below added by Ilavenil for witholding tax project *********/
1558 cursor last_intset_date is
1559 select nvl(max(journal_date), p_start_date)
1560 from xtr_journals
1561 where deal_number = p_deal_no
1562 and amount_type in ('INTSET','TAX') -- Ilavenil modified for witholding tax
1563 union
1564 /********* Ilavenil modified for witholding tax *********/
1565 select nvl(max(b.maturity_date), p_start_date)
1566 from xtr_journals a,
1567 xtr_rollover_transactions b
1568 where b.company_code =p_company
1569 and b.deal_number = p_deal_no
1570 and b.tax_settled_reference is not null
1571 and a.company_code = p_company
1572 and a.deal_type = 'EXP'
1573 and a.transaction_number = b.tax_settled_reference
1574 order by 1 desc;
1575 /**********/
1576
1577 /********************************/
1578 /* Prepaid Interest */
1579 /********************************/
1580 Cursor c_prepaid_int is
1581 select prepaid_interest
1582 from XTR_DEALS_V
1583 where deal_no = p_deal_no;
1584 l_pre_int VARCHAR2(1) := NULL;
1585
1586 Cursor last_int_set is
1587 select max(maturity_date)
1588 from xtr_rollover_transactions_v
1589 where deal_number = p_deal_no
1590 and org_trans_no in (select min(transaction_number)
1591 from xtr_deal_date_amounts
1592 where deal_number = p_deal_no
1593 and amount_type = 'INTSET'
1594 and nvl(settle, 'N') = 'Y')
1595 and maturity_date > p_start_date;
1596 l_int_set_date DATE := NULL;
1597
1598
1599 cursor check_jrnl_done is
1600 select nvl(max(journal_date), p_start_date)
1601 from xtr_journals
1602 where deal_number = p_deal_no
1603 and amount_type in ('INTSET','TAX')
1604 union
1605 select nvl(max(b.maturity_date), p_start_date)
1606 from xtr_journals a,
1607 xtr_rollover_transactions b
1608 where b.company_code = p_company
1609 and b.deal_number = p_deal_no
1610 and b.tax_settled_reference is not null
1611 and a.company_code = p_company
1612 and a.deal_type = 'EXP'
1613 and a.transaction_number = b.tax_settled_reference
1614 order by 1 desc;
1615
1616 Cursor last_int_journal is
1617 select nvl(max(maturity_date), p_start_date)
1618 from xtr_rollover_transactions
1619 where deal_number = p_deal_no
1620 and transaction_number in (select transaction_number
1621 from xtr_deal_date_amounts
1622 where deal_number = p_deal_no
1623 and amount_type = 'INTSET'
1624 and batch_id is NOT NULL);
1625
1626 l_int_journal_date DATE := NULL;
1627
1628 cursor check_accrual_done is
1629 select max(period_to)
1630 from xtr_accrls_amort
1631 where deal_no = p_deal_no
1632 and period_to > p_start_date;
1633
1634 cursor check_reval_done is
1635 select max(b.period_end)
1636 from xtr_batches b, xtr_batch_events e, xtr_revaluation_details r
1637 where b.batch_id = e.batch_id
1638 and b.batch_id = r.batch_id
1639 and r.deal_no = p_deal_no
1640 and b.company_code = p_company
1641 and e.event_code = 'REVAL'
1642 and b.period_end > p_start_date;
1643
1644 Begin
1645 p_valid_ok := TRUE;
1646 if p_deal_type = 'TMM' then
1647 open c_prepaid_int;
1648 fetch c_prepaid_int into l_pre_int;
1649 close c_prepaid_int;
1650 end if;
1651
1652 Open check_settlement_done;
1653 Fetch check_settlement_done into l_date;
1654 If (l_date is NOT NULL and l_date > p_start_date) then
1655 close check_settlement_done;
1656 p_valid_ok := FALSE;
1657 p_error := 1;
1658 return;
1659 else
1660 close check_settlement_done;
1661 if p_deal_type = 'TMM' and nvl(l_pre_int, 'N') = 'Y' then -- TMM prepaid interest deal
1662 Open last_int_set;
1663 Fetch last_int_set into l_int_set_date;
1664 if l_int_set_date is NOT NULL and l_int_set_date > p_start_date then
1665 Close last_int_set;
1666 p_valid_ok := FALSE;
1667 p_error := 1;
1668 return;
1669 else
1670 Close last_int_set;
1671 p_valid_ok := TRUE;
1672 end if;
1673 else
1674 p_valid_ok := TRUE;
1675 end if;
1676 end if;
1677
1678 Open check_jrnl_done;
1679 Fetch check_jrnl_done into l_date;
1680 if l_date is NOT NULL and l_date > p_start_date then
1681 close check_jrnl_done;
1682 p_valid_ok := FALSE;
1683 p_error := 4;
1684 return;
1685 else
1686 close check_jrnl_done;
1687 if p_deal_type = 'TMM' and nvl(l_pre_int, 'N') = 'Y' then -- TMM prepaid interest deal
1688 open last_int_journal;
1689 fetch last_int_journal into l_int_journal_date;
1690 if l_int_journal_date is NOT NULL and l_int_journal_date > p_start_date then
1691 Close last_int_journal;
1692 p_valid_ok := FALSE;
1693 p_error := 4;
1694 return;
1695 else
1696 Close last_int_journal;
1697 p_valid_ok := TRUE;
1698 end if;
1699 else
1700 p_valid_ok := TRUE;
1701 end if;
1702 end if;
1703
1704 Open check_accrual_done;
1705 Fetch check_accrual_done into l_date;
1706 if l_date is NOT NULL then
1707 close check_accrual_done;
1708 p_valid_ok := FALSE;
1709 p_error := 2;
1710 return;
1711 else
1712 close check_accrual_done;
1713 p_valid_ok := TRUE;
1714 end if;
1715
1716 Open check_reval_done;
1717 Fetch check_reval_done into l_date;
1718 if l_date is NOT NULL then
1719 close check_reval_done;
1720 p_valid_ok := FALSE;
1721 p_error := 3;
1722 return;
1723 else
1724 close check_reval_done;
1725 p_valid_ok := TRUE;
1726 end if;
1727
1728 Open last_intset_date;
1729 Fetch last_intset_date into l_date;
1730 if (l_date is NOT NULL and l_date > p_start_date )then
1731 close last_intset_date;
1732 p_valid_ok := FALSE;
1733 p_error := 1;
1734 return;
1735 else
1736 close last_intset_date;
1737 p_valid_ok := TRUE;
1738 end if;
1739
1740 End VALIDATE_TRANSACTION;
1741
1742 ----------------------------------------------------------
1743 /*************************************************************/
1744 /* The following code finds the next interest rate based on */
1745 /* the benchmark rate of the deal and the rate fixing date */
1746 /* of the transaction.^ */
1747 /* It will first look at BID rate, and if it is null, then */
1748 /* look for ASK rate, if both are null, then the next closest*/
1749 /* interest rate will be used. If none are found, then log */
1750 /* this to the file. */
1751 /* Parameters: */
1752 /* p_ric_code - Benchmark Rate code of the deal */
1753 /* p_rate_date - Transaction's Rate Fixing Date */
1754 /* p_rateset_adj - No of backdated days to search for interest*/
1755 /* rate. Entered by user. */
1756 /* p_rate - The benchmark Interest Rate */
1757 /*************************************************************/
1758 PROCEDURE GET_BENCHMARK_RATE(p_ric_code IN VARCHAR2,
1759 p_rate_date IN DATE,
1760 p_rateset_adj IN NUMBER,
1761 p_rate OUT NOCOPY NUMBER)IS
1762 /*-------------------------------------------------*/
1763 /* Select interest rate that is : */
1764 /* - either BID rate or ASK rate is not null */
1765 /* - closest to the transaction Rate Reset Date, */
1766 /* with rateset day adjustment. */
1767 /*-------------------------------------------------*/
1768 cursor curr_bench_rate is
1769 select nvl(BID_RATE,OFFER_RATE)
1770 from XTR_INTEREST_PERIOD_RATES_V
1771 where UNIQUE_PERIOD_ID = p_ric_code
1772 and trunc(rate_date) between (p_rate_date-nvl(p_rateset_adj,0)) -- Bug 5259621
1773 and p_rate_date -- Bug 5259621
1774 and (bid_rate is not null or offer_rate is not null)
1775 order by rate_date desc;
1776
1777 Begin
1778 p_rate := null;
1779
1780 OPEN curr_bench_rate;
1781 LOOP
1782 FETCH curr_bench_rate into p_rate;
1783 if curr_bench_rate%notfound then -- No Interest Rate found.
1784 EXIT;
1785 else
1786 if p_rate is not null then -- New Interest Rate is found.
1787 EXIT;
1788 end if;
1789 end if;
1790 END LOOP;
1791 CLOSE curr_bench_rate;
1792
1793 End GET_BENCHMARK_RATE;
1794 --------------------------------------------------------
1795 PROCEDURE UPDATE_RATE_ONE_TRANSACTION(p_deal_no IN NUMBER,
1796 p_trans_no IN NUMBER,
1797 p_deal_type IN VARCHAR2,
1798 p_start_date IN DATE,
1799 p_new_rate IN NUMBER)IS
1800 -- Add xtr_deals.rounding_type for Interest Override
1801 cursor TMM_ROLL is
1802 select r.deal_subtype, r.currency, r.rowid, r.adjusted_balance,
1803 r.no_of_days,r.year_basis,r.interest, r.settle_term_interest,
1804 r.accum_interest_bf, r.accum_interest,r.interest_hce,
1805 r.interest_settled, r.trans_closeout_no, r.start_date,
1806 r.maturity_date, r.transaction_number, r.interest_rate,
1807 r.principal_action,r.principal_adjust, r.balance_out_bf,
1808 d.rounding_type,
1809 /**** code below added by Ilavenil for witholding tax project *****/
1810 r.tax_amount,
1811 r.tax_code,
1812 r.tax_settled_reference, r.tax_amount_hce ,
1813 r.tax_rate,
1814 r.balance_out,
1815 d.settle_account_no,
1816 d.company_code, d.cparty_code, d.year_calc_type
1817 /**********/
1818 from XTR_ROLLOVER_TRANSACTIONS r,
1819 XTR_DEALS d
1820 where r.deal_number = d.deal_no
1821 and r.deal_type = p_deal_type
1822 and r.deal_type = 'TMM'
1823 and r.deal_number = p_deal_no
1824 and r.transaction_number = p_trans_no; -- bug 3814944
1825
1826 pmt TMM_ROLL%ROWTYPE;
1827
1828 cursor LAST_TMM is
1829 select rowid
1830 from XTR_ROLLOVER_TRANSACTIONS_V
1831 where deal_number = p_deal_no
1832 and status_code = 'CURRENT'
1833 order by maturity_date desc, start_date desc, transaction_number desc;
1834
1835 last_pmt LAST_TMM%ROWTYPE;
1836
1837 --Add d.rounding_type for Interest Override
1838 cursor IRS_ROLL is
1839 select r.currency, r.balance_out, r.no_of_days, d.year_basis,
1840 d.deal_subtype, d.rounding_type
1841 from XTR_DEALS d,
1842 XTR_ROLLOVER_TRANSACTIONS r
1843 where d.deal_type = p_deal_type
1844 and d.deal_type = 'IRS'
1845 and d.deal_no = r.deal_number
1846 and r.deal_number = p_deal_no
1847 and r.transaction_number = p_trans_no;
1848
1849 l_currency VARCHAR2(15);
1850 cursor RND_YR_TMM is
1851 select rounding_factor, hce_rate
1852 from XTR_MASTER_CURRENCIES_V
1853 where currency = pmt.currency;
1854
1855 cursor RND_YR_IRS is
1856 select rounding_factor, hce_rate
1857 from XTR_MASTER_CURRENCIES_V
1858 where currency = l_currency;
1859
1860 l_no_of_days NUMBER;
1861 l_year_basis NUMBER;
1862 l_interest NUMBER;
1863 l_int_rate NUMBER;
1864 l_accum_int NUMBER;
1865 l_int_settled NUMBER;
1866 l_int_hce NUMBER;
1867 l_accum_int_hce NUMBER;
1868 l_accum_int_bf_hce NUMBER;
1869 l_balance_out NUMBER;
1870 new_accum_int NUMBER;
1871 l_round NUMBER;
1872 l_hce_rate NUMBER;
1873 l_deal_subtype VARCHAR2(7);
1874 l_int_settle_hce NUMBER;
1875 l_prin_adj NUMBER;
1876 l_rounding_type VARCHAR2(1); -- Add Interest Override
1877
1878 v_RT Xtr_Rollover_Transactions_V%Rowtype;
1879 v_first_time Char(1) := 'Y';
1880 v_last_trans Varchar2(1) ;
1881 v_prncpl_ctype Xtr_Tax_Brokerage_Setup.Calc_Type%type;
1882 v_prncpl_method Xtr_Tax_Brokerage_Setup.tax_settle_method%type;
1883 v_income_ctype Xtr_Tax_Brokerage_Setup.Calc_Type%type;
1884 v_income_method Xtr_Tax_Brokerage_Setup.tax_settle_method%type;
1885
1886 Begin
1887
1888 If p_deal_type = 'TMM' then
1889 /*------------------------------*/
1890 /* XTRINWHL.invalidate_deal */
1891 /*------------------------------*/
1892 Update XTR_CONFIRMATION_DETAILS
1893 set confirmation_validated_by = null,
1894 confirmation_validated_on = to_date(null)
1895 where deal_no = p_deal_no;
1896
1897 Update XTR_ROLLOVER_TRANSACTIONS
1898 set interest_rate = p_new_rate
1899 where deal_number = p_deal_no
1900 and transaction_number = p_trans_no;
1901
1902 update XTR_DEAL_DATE_AMOUNTS
1903 set transaction_rate = p_new_rate
1904 where deal_number = p_deal_no
1905 and transaction_number = p_trans_no;
1906
1907 open LAST_TMM;
1908 fetch LAST_TMM INTO last_pmt;
1909 close LAST_TMM;
1910
1911 /*------------------------------*/
1912 /* XTRINWHL.recalc_this_dt_row */
1913 /*------------------------------*/
1914 Open TMM_ROLL;
1915 fetch TMM_ROLL into pmt;
1916 LOOP
1917 EXIT when TMM_ROLL%NOTFOUND;
1918 /******** code below added by Ilavenil for witholding tax project *********/
1919 If v_first_time = 'Y' then
1920 XTR_FPS2_P.Get_Settle_Method (null,
1921 v_prncpl_ctype,
1922 v_prncpl_method,
1923 pmt.tax_code,
1924 v_income_ctype,
1925 v_income_method);
1926 v_first_time := 'N';
1927 End if;
1928 /**********/
1929
1930 open RND_YR_TMM;
1931 fetch RND_YR_TMM into l_round, l_hce_rate;
1932 close RND_YR_TMM;
1933
1934 l_hce_rate := nvl(l_hce_rate, 1);
1935 l_round := nvl(l_round, 2);
1936
1937 if NVL(pmt.PRINCIPAL_ACTION,'@#@') = 'DECRSE' then
1938 l_prin_adj := (-1) * nvl(pmt.PRINCIPAL_ADJUST,0);
1939 else
1940 l_prin_adj := nvl(pmt.PRINCIPAL_ADJUST,0);
1941 end if;
1942
1943 pmt.ADJUSTED_BALANCE := nvl(pmt.BALANCE_OUT_BF,0) + l_prin_adj;
1944
1945 pmt.accum_interest_bf := nvl(new_accum_int,pmt.accum_interest_bf);
1946 if pmt.ROWID=last_pmt.ROWID then
1947
1948 --Add Interest Override
1949 pmt.interest := INTEREST_ROUND(pmt.balance_out_bf * p_new_rate /100 *
1950 pmt.no_of_days / pmt.year_basis, l_round, pmt.rounding_type);
1951 else
1952 pmt.interest := INTEREST_ROUND(pmt.adjusted_balance * p_new_rate /100 *
1953 pmt.no_of_days / pmt.year_basis, l_round, pmt.rounding_type);
1954 end if;
1955
1956 if pmt.SETTLE_TERM_INTEREST = 'Y' then
1957 if pmt.trans_closeout_no is null then
1958 pmt.accum_interest := 0;
1959 pmt.interest_settled := nvl(pmt.accum_interest_bf,0) +
1960 nvl(pmt.interest,0);
1961 else
1962 pmt.accum_interest := nvl(pmt.accum_interest_bf,0) +
1963 nvl(pmt.interest,0) - nvl(pmt.interest_settled,0);
1964 end if;
1965 else -- not settled yet
1966 if pmt.trans_closeout_no is null then
1967 pmt.interest_settled := 0;
1968 end if;
1969 pmt.accum_interest := nvl(pmt.accum_interest_bf,0) +
1970 nvl(pmt.interest,0) - nvl(pmt.interest_settled,0);
1971 end if;
1972
1973 -- Calcuate HCE amounts
1974 new_accum_int := nvl(pmt.accum_interest,0);
1975 l_int_hce := round(pmt.interest /l_hce_rate, l_round);
1976 l_int_settle_hce := round(pmt.interest_settled /l_hce_rate, l_round);
1977 l_accum_int_hce := round(pmt.accum_interest /l_hce_rate, l_round);
1978 l_accum_int_bf_hce := round(pmt.accum_interest_bf /l_hce_rate, l_round);
1979
1980 /******* code below added by Ilavenil *********/
1981 v_RT.TAX_SETTLED_REFERENCE := pmt.TAX_SETTLED_REFERENCE;
1982 v_RT.TAX_RATE := pmt.TAX_RATE;
1983 v_RT.TAX_AMOUNT := pmt.TAX_AMOUNT;
1984 v_RT.TAX_AMOUNT_HCE := pmt.TAX_AMOUNT_HCE;
1985 v_RT.TAX_CODE := pmt.TAX_CODE;
1986
1987 v_RT.INTEREST_SETTLED := pmt.INTEREST_SETTLED;
1988 v_RT.DEAL_NUMBER := p_DEAL_NO;
1989 v_RT.TRANSACTION_NUMBER := pmt.TRANSACTION_NUMBER;
1990 v_RT.START_DATE := pmt.START_DATE;
1991 v_RT.MATURITY_DATE := pmt.MATURITY_DATE;
1992
1993 v_RT.CURRENCY := pmt.CURRENCY;
1994 v_RT.COMPANY_CODE := pmt.COMPANY_CODE;
1995 v_RT.CPARTY_CODE := pmt.CPARTY_CODE;
1996 v_RT.YEAR_CALC_TYPE := pmt.YEAR_CALC_TYPE;
1997
1998 if pmt.rowid = last_pmt.rowid then
1999 v_last_trans := 'Y';
2000 else
2001 v_last_trans := 'N';
2002 end if;
2003
2004 XTR_FPS2_P.CALC_TMM_TAX (v_prncpl_ctype,
2005 v_prncpl_method,
2006 v_income_ctype,
2007 v_income_method,
2008 pmt.settle_account_no,
2009 v_last_trans,
2010 v_rt
2011 );
2012
2013 update XTR_ROLLOVER_TRANSACTIONS
2014 set accum_interest_bf = pmt.accum_interest_bf,
2015 accum_interest_bf_hce = l_accum_int_bf_hce,
2016 accum_interest_hce = l_accum_int_hce,
2017 accum_interest = pmt.accum_interest,
2018 interest = pmt.interest,
2019 interest_settled = pmt.interest_settled,
2020 interest_hce = l_int_hce,
2021 original_amount = pmt.interest, --Add Interest Override
2022 TAX_SETTLED_REFERENCE = v_RT.TAX_SETTLED_REFERENCE, -- Ilavenil Bug 234413
2023 TAX_AMOUNT = v_RT.TAX_AMOUNT, -- Ilavenil Bug 234413
2024 TAX_AMOUNT_HCE = v_RT.TAX_AMOUNT_HCE -- Ilavenil Bug 234413
2025 where rowid = pmt.ROWID;
2026
2027 update XTR_DEAL_DATE_AMOUNTS
2028 set amount = pmt.interest,
2029 hce_amount = l_int_hce
2030 where deal_number = p_deal_no
2031 and transaction_number = pmt.transaction_number
2032 and amount_type = 'INTERST';
2033
2034 if pmt.settle_term_interest = 'Y' then
2035 update XTR_DEAL_DATE_AMOUNTS
2036 set amount = pmt.interest_settled,
2037 hce_amount = l_int_settle_hce,
2038 cashflow_amount = decode(pmt.deal_subtype, 'FUND', (-1), 1) *
2039 pmt.interest_settled
2040 where deal_number = p_deal_no
2041 and transaction_number = pmt.transaction_number
2042 and amount_type = 'INTSET';
2043 end if;
2044
2045 Fetch TMM_ROLL into pmt;
2046 End loop;
2047 Close TMM_ROLL;
2048
2049 Elsif p_deal_type = 'IRS' then
2050 Open IRS_ROLL;
2051 Fetch IRS_ROLL into l_currency, l_balance_out, l_no_of_days,
2052 l_year_basis, l_deal_subtype, l_rounding_type; --Add Interest Override
2053 If IRS_ROLL%FOUND then
2054 open RND_YR_IRS;
2055 fetch RND_YR_IRS into l_round, l_hce_rate;
2056 close RND_YR_IRS;
2057
2058 l_hce_rate := nvl(l_hce_rate, 1);
2059 l_round := nvl(l_round, 2);
2060
2061 l_int_rate := p_new_rate;
2062 --Add Interest Override
2063 l_interest := INTEREST_ROUND(l_balance_out * l_int_rate /100 *
2064 l_no_of_days / l_year_basis, l_round, l_rounding_type);
2065 --ORIGINAL---------------------------------------
2066 -- l_interest := round(l_balance_out * l_int_rate /100 *
2067 -- l_no_of_days / l_year_basis, l_round);
2068 -------------------------------------------------
2069 l_int_settled := l_interest;
2070 l_int_hce := round(l_int_settled /l_hce_rate, l_round);
2071 End if;
2072 Close IRS_ROLL;
2073
2074 Update XTR_ROLLOVER_TRANSACTIONS
2075 set interest_rate = l_int_rate,
2076 interest = l_interest,
2077 interest_settled = l_int_settled,
2078 interest_hce = l_int_hce,
2079 original_amount = l_interest --Add Interest Override
2080 where deal_number = p_deal_no
2081 and transaction_number = p_trans_no;
2082
2083 Update XTR_DEAL_DATE_AMOUNTS
2084 set transaction_rate = l_int_rate
2085 where deal_number = p_deal_no
2086 and transaction_number = p_trans_no;
2087
2088 Update XTR_DEAL_DATE_AMOUNTS
2089 set amount = l_interest,
2090 hce_amount = l_int_hce,
2091 cashflow_amount = decode(l_deal_subtype, 'FUND',
2092 l_interest * (-1), l_interest)
2093 where deal_number = p_deal_no
2094 and transaction_number = p_trans_no
2095 and amount_type = 'INTSET';
2096 End if;
2097
2098
2099 End UPDATE_RATE_ONE_TRANSACTION;
2100 ---------------------------------------------------------------------------------------------------
2101 PROCEDURE UPDATE_RATE_SEQ_TRANSACTION(p_deal_no IN NUMBER,
2102 p_trans_no IN NUMBER,
2103 p_deal_type IN VARCHAR2,
2104 p_start_date IN DATE,
2105 p_new_rate IN NUMBER)IS
2106 -- Add xtr_deals.rounding_type for Interest Override
2107 cursor TMM_ROLL is
2108 select r.deal_subtype, r.currency, r.rowid, r.adjusted_balance, r.no_of_days, r.year_basis,
2109 r.interest, r.settle_term_interest, r.accum_interest_bf, r.accum_interest,
2110 r.interest_hce, r.interest_settled, r.trans_closeout_no, r.start_date,
2111 r.maturity_date, r.transaction_number, r.interest_rate, r.principal_action,
2112 r.principal_adjust, r.balance_out_bf, d.rounding_type,
2113 /**** code below added by Ilavenil for witholding tax project *****/
2114 r.tax_amount,
2115 r.tax_code,
2116 r.tax_settled_reference, r.tax_amount_hce ,
2117 r.tax_rate,
2118 r.balance_out,
2119 d.settle_account_no,
2120 d.company_code, d.cparty_code, d.year_calc_type
2121 /**********/
2122 from XTR_ROLLOVER_TRANSACTIONS_V r,
2123 XTR_DEALS d
2124 where r.deal_number = d.deal_no
2125 and r.deal_type = p_deal_type
2126 and r.deal_type = 'TMM'
2127 and r.deal_number = p_deal_no
2128 and r.start_date >= p_start_date
2129 order by r.start_date asc, r.maturity_date asc, r.transaction_number asc;
2130
2131 pmt TMM_ROLL%ROWTYPE;
2132
2133 cursor LAST_TMM is
2134 select rowid
2135 from XTR_ROLLOVER_TRANSACTIONS_V
2136 where deal_number = p_deal_no
2137 and status_code = 'CURRENT'
2138 order by maturity_date desc, start_date desc, transaction_number desc;
2139
2140 last_pmt LAST_TMM%ROWTYPE;
2141
2142 --Add d.rounding_type for Interest Override
2143 cursor IRS_ROLL is
2144 select r.rowid, r.transaction_number, d.currency, r.balance_out,
2145 r.no_of_days, d.year_basis, d.deal_subtype, d.rounding_type
2146 from XTR_DEALS d,
2147 XTR_ROLLOVER_TRANSACTIONS r
2148 where d.deal_type = p_deal_type
2149 and d.deal_type = 'IRS'
2150 and d.deal_no = r.deal_number
2151 and d.deal_no = p_deal_no
2152 and r.start_date >= p_start_date;
2153
2154 pms IRS_ROLL%ROWTYPE;
2155
2156 cursor RND_YR_TMM is
2157 select rounding_factor, hce_rate
2158 from XTR_MASTER_CURRENCIES_V
2159 where currency = pmt.currency;
2160
2161 cursor RND_YR_IRS is
2162 select rounding_factor, hce_rate
2163 from XTR_MASTER_CURRENCIES_V
2164 where currency = pms.currency;
2165
2166 l_interest NUMBER;
2167 l_accum_int NUMBER;
2168 l_int_settled NUMBER;
2169 l_int_hce NUMBER;
2170 l_accum_int_hce NUMBER;
2171 l_accum_int_bf_hce NUMBER;
2172 new_accum_int NUMBER;
2173 l_round NUMBER;
2174 l_hce_rate NUMBER;
2175 l_int_settle_hce NUMBER;
2176 l_prin_adj NUMBER;
2177
2178 v_RT Xtr_Rollover_Transactions_V%Rowtype;
2179 v_first_time Char(1) := 'Y';
2180 v_last_trans Varchar2(1) ;
2181 v_prncpl_ctype Xtr_Tax_Brokerage_Setup.Calc_Type%type;
2182 v_prncpl_method Xtr_Tax_Brokerage_Setup.tax_settle_method%type;
2183 v_income_ctype Xtr_Tax_Brokerage_Setup.Calc_Type%type;
2184 v_income_method Xtr_Tax_Brokerage_Setup.tax_settle_method%type;
2185
2186 Begin
2187 If p_deal_type = 'TMM' then
2188 -- Invalid deals
2189 Update XTR_CONFIRMATION_DETAILS
2190 set confirmation_validated_by = null,
2191 confirmation_validated_on = to_date(null)
2192 where deal_no = p_deal_no;
2193
2194 open LAST_TMM;
2195 fetch LAST_TMM INTO last_pmt;
2196 close LAST_TMM;
2197
2198 Open TMM_ROLL;
2199 fetch TMM_ROLL into pmt;
2200 LOOP
2201 EXIT when TMM_ROLL%NOTFOUND;
2202
2203 /******** code below added by Ilavenil for witholding tax project *********/
2204 If v_first_time = 'Y' then
2205 XTR_FPS2_P.Get_Settle_Method (null,
2206 v_prncpl_ctype,
2207 v_prncpl_method,
2208 pmt.tax_code,
2209 v_income_ctype,
2210 v_income_method);
2211 v_first_time := 'N';
2212 End if;
2213 /**********/
2214
2215 open RND_YR_TMM;
2216 fetch RND_YR_TMM into l_round, l_hce_rate;
2217 close RND_YR_TMM;
2218
2219 l_hce_rate := nvl(l_hce_rate, 1);
2220 l_round := nvl(l_round, 2);
2221
2222 if NVL(pmt.PRINCIPAL_ACTION,'@#@') = 'DECRSE' then
2223 l_prin_adj := (-1) * nvl(pmt.PRINCIPAL_ADJUST,0);
2224 else
2225 l_prin_adj := nvl(pmt.PRINCIPAL_ADJUST,0);
2226 end if;
2227
2228 pmt.ADJUSTED_BALANCE := nvl(pmt.BALANCE_OUT_BF,0) + l_prin_adj;
2229
2230 pmt.accum_interest_bf := nvl(new_accum_int,pmt.accum_interest_bf);
2231 if pmt.ROWID=last_pmt.ROWID then
2232
2233 --Add Interest Override
2234 pmt.interest := INTEREST_ROUND(pmt.balance_out_bf * p_new_rate /100 *
2235 pmt.no_of_days / pmt.year_basis, l_round, pmt.rounding_type);
2236 else
2237 pmt.interest := INTEREST_ROUND(pmt.adjusted_balance * p_new_rate /100 *
2238 pmt.no_of_days / pmt.year_basis, l_round, pmt.rounding_type);
2239 --ORIGINAL--------------------------------------------------
2240 -- pmt.interest := round(pmt.balance_out_bf * p_new_rate /100 *
2241 -- pmt.no_of_days / pmt.year_basis, l_round);
2242 -- else
2243 -- pmt.interest := round(pmt.adjusted_balance * p_new_rate /100 *
2244 -- pmt.no_of_days / pmt.year_basis, l_round);
2245 --End of Change-----------------------------------------------
2246 end if;
2247
2248 if pmt.SETTLE_TERM_INTEREST = 'Y' then
2249 if pmt.trans_closeout_no is null then
2250 pmt.accum_interest := 0;
2251 pmt.interest_settled := nvl(pmt.accum_interest_bf,0) +
2252 nvl(pmt.interest,0);
2253 else
2254 pmt.accum_interest := nvl(pmt.accum_interest_bf,0) +
2255 nvl(pmt.interest,0) - nvl(pmt.interest_settled,0);
2256 end if;
2257 else -- not settled yet
2258 if pmt.trans_closeout_no is null then
2259 pmt.interest_settled := 0;
2260 end if;
2261 pmt.accum_interest := nvl(pmt.accum_interest_bf,0) +
2262 nvl(pmt.interest,0) - nvl(pmt.interest_settled,0);
2263 end if;
2264
2265 -- Calcuate HCE amounts
2266 new_accum_int := nvl(pmt.accum_interest,0);
2267 l_int_hce := round(pmt.interest /l_hce_rate, l_round);
2268 l_int_settle_hce := round(pmt.interest_settled /l_hce_rate, l_round);
2269 l_accum_int_hce := round(pmt.accum_interest /l_hce_rate, l_round);
2270 l_accum_int_bf_hce := round(pmt.accum_interest_bf /l_hce_rate, l_round);
2271
2272 /******* code below added by Ilavenil *********/
2273 v_RT.TAX_SETTLED_REFERENCE := pmt.TAX_SETTLED_REFERENCE;
2274 v_RT.TAX_RATE := pmt.TAX_RATE;
2275 v_RT.TAX_AMOUNT := pmt.TAX_AMOUNT;
2276 v_RT.TAX_AMOUNT_HCE := pmt.TAX_AMOUNT_HCE;
2277 v_RT.TAX_CODE := pmt.TAX_CODE;
2278
2279 v_RT.INTEREST_SETTLED := pmt.INTEREST_SETTLED;
2280 v_RT.DEAL_NUMBER := p_DEAL_NO;
2281 v_RT.TRANSACTION_NUMBER := pmt.TRANSACTION_NUMBER;
2282 v_RT.START_DATE := pmt.START_DATE;
2283 v_RT.MATURITY_DATE := pmt.MATURITY_DATE;
2284
2285 v_RT.CURRENCY := pmt.CURRENCY;
2286 v_RT.COMPANY_CODE := pmt.COMPANY_CODE;
2287 v_RT.CPARTY_CODE := pmt.CPARTY_CODE;
2288 v_RT.YEAR_CALC_TYPE := pmt.YEAR_CALC_TYPE;
2289
2290 if pmt.rowid = last_pmt.rowid then
2291 v_last_trans := 'Y';
2292 else
2293 v_last_trans := 'N';
2294 end if;
2295
2296 XTR_FPS2_P.CALC_TMM_TAX (v_prncpl_ctype,
2297 v_prncpl_method,
2298 v_income_ctype,
2299 v_income_method,
2300 pmt.settle_account_no,
2301 v_last_trans,
2302 v_rt
2303 );
2304
2305 update XTR_ROLLOVER_TRANSACTIONS
2306 set interest_rate = p_new_rate,
2307 accum_interest_bf = pmt.accum_interest_bf,
2308 accum_interest_bf_hce = l_accum_int_bf_hce,
2309 accum_interest_hce = l_accum_int_hce,
2310 accum_interest = pmt.accum_interest,
2311 interest = pmt.interest,
2312 interest_settled = pmt.interest_settled,
2313 interest_hce = l_int_hce,
2314 original_amount = pmt.interest, --Add Interest Override
2315 TAX_SETTLED_REFERENCE = v_RT.TAX_SETTLED_REFERENCE, -- Ilavenil Bug 234413
2316 TAX_AMOUNT = v_RT.TAX_AMOUNT, -- Ilavenil Bug 234413
2317 TAX_AMOUNT_HCE = v_RT.TAX_AMOUNT_HCE -- Ilavenil Bug 234413
2318 where rowid = pmt.ROWID;
2319
2320 update XTR_DEAL_DATE_AMOUNTS
2321 set transaction_rate = p_new_rate
2322 where deal_number = p_deal_no
2323 and transaction_number = pmt.transaction_number;
2324
2325 update XTR_DEAL_DATE_AMOUNTS
2326 set amount = pmt.interest,
2327 hce_amount = l_int_hce
2328 where deal_number = p_deal_no
2329 and transaction_number = pmt.transaction_number
2330 and amount_type = 'INTERST';
2331
2332 if pmt.settle_term_interest = 'Y' then
2333 update XTR_DEAL_DATE_AMOUNTS
2334 set amount = pmt.interest_settled,
2335 hce_amount = l_int_settle_hce,
2336 cashflow_amount = decode(pmt.deal_subtype, 'FUND', (-1), 1) *
2337 pmt.interest_settled
2338 where deal_number = p_deal_no
2339 and transaction_number = pmt.transaction_number
2340 and amount_type = 'INTSET';
2341 end if;
2342
2343 Fetch TMM_ROLL into pmt;
2344 End loop;
2345 Close TMM_ROLL;
2346
2347 Elsif p_deal_type = 'IRS' then
2348 Open IRS_ROLL;
2349 fetch IRS_ROLL into pms;
2350 LOOP
2351 EXIT when IRS_ROLL%NOTFOUND;
2352 open RND_YR_IRS;
2353 fetch RND_YR_IRS into l_round, l_hce_rate;
2354 close RND_YR_IRS;
2355
2356 l_hce_rate := nvl(l_hce_rate, 1);
2357 l_round := nvl(l_round, 2);
2358
2359 --Add Interest Override
2360 l_interest := INTEREST_ROUND(pms.balance_out * p_new_rate /100 *
2361 pms.no_of_days / pms.year_basis, l_round, pms.rounding_type);
2362 --ORIGINAL--------------------------------------------
2363 -- l_interest := round(pms.balance_out * p_new_rate /100 *
2364 -- pms.no_of_days / pms.year_basis, l_round);
2365 --End of Change ----------------------------------------
2366 l_int_settled := l_interest;
2367 l_int_hce := round(l_int_settled /l_hce_rate, l_round);
2368
2369 Update XTR_ROLLOVER_TRANSACTIONS
2370 set interest_rate = p_new_rate,
2371 interest = l_interest,
2372 interest_settled = l_int_settled,
2373 interest_hce = l_int_hce,
2374 original_amount = l_interest --Add Interest Override
2375 where rowid = pms.rowid;
2376
2377 Update XTR_DEAL_DATE_AMOUNTS
2378 set transaction_rate = p_new_rate
2379 where deal_number = p_deal_no
2380 and transaction_number = pms.transaction_number;
2381
2382 Update XTR_DEAL_DATE_AMOUNTS
2383 set amount = l_interest,
2384 hce_amount = l_int_hce,
2385 cashflow_amount = decode(pms.deal_subtype, 'FUND',
2386 l_interest * (-1), l_interest)
2387 where deal_number = p_deal_no
2388 and transaction_number = pms.transaction_number
2389 and amount_type = 'INTSET';
2390
2391 Fetch IRS_ROLL into pms;
2392 End loop;
2393 Close IRS_ROLL;
2394 End if;
2395
2396
2397 End UPDATE_RATE_SEQ_TRANSACTION;
2398
2399 FUNCTION ROUNDUP(p_amount NUMBER,
2400 p_round_factor NUMBER) RETURN NUMBER IS
2401
2402 l_amount number;
2403 l_rounded_amount number;
2404
2405 BEGIN
2406
2407 l_amount := abs(p_amount);
2408
2409 l_rounded_amount := Ceil(l_amount*Power(10,p_round_factor))/Power(10,p_round_factor);
2410
2411 if p_amount < 0 then
2412 l_rounded_amount := (-1)*l_rounded_amount;
2413 end if;
2414
2415 return(l_rounded_amount);
2416
2417 END ROUNDUP;
2418
2419 FUNCTION INTEREST_ROUND
2420 (p_amount NUMBER,
2421 p_round_factor NUMBER,
2422 p_rounding_type VARCHAR2
2423 ) RETURN NUMBER IS
2424 BEGIN
2425 IF p_rounding_type='T' THEN
2426 RETURN (Trunc(p_amount,p_round_factor));
2427 ELSIF p_rounding_type='U' THEN
2428 RETURN (roundup(p_amount,p_round_factor));
2429 ELSE
2430 RETURN (Round(p_amount,p_round_factor));
2431 END IF;
2432
2433 END interest_round;
2434
2435 PROCEDURE CURRENCY_CROSS_RATE (p_currency_from IN VARCHAR2,
2436 p_currency_to IN VARCHAR2,
2437 p_rate OUT NOCOPY NUMBER)
2438 IS
2439 --
2440 -- This procedure return the conversion rate for
2441 -- Ineterest Toeralance checks.
2442 --
2443
2444 l_ask_price NUMBER;
2445 l_bit_price NUMBER;
2446
2447 CURSOR usd_cross IS
2448 SELECT decode(currency_a, p_currency_from ,ask_price,1/ask_price),
2449 decode(currency_a, p_currency_from ,bid_price,1/bid_price)
2450 FROM xtr_market_prices
2451 WHERE ((currency_a= p_currency_from AND currency_b=p_currency_to)
2452 OR (currency_a= p_currency_to AND currency_b=p_currency_from))
2453 AND term_type='S';
2454
2455 CURSOR other_cross IS
2456 select
2457 decode(sr2.currency_a,p_currency_from,sr2.bid_price ,(1/sr2.ask_price)) /
2458 decode(sr3.currency_a, p_currency_to, sr3.bid_price, (1/sr3.ask_price)),
2459 decode(sr2.currency_a,p_currency_from,sr2.ask_price, (1/sr2.bid_price)) /
2460 decode(sr3.currency_a, p_currency_to, sr3.ask_price, (1/sr3.bid_price))
2461 from
2462 XTR_MARKET_PRICES sr2,
2463 XTR_MARKET_PRICES sr3
2464 where (sr2.currency_a = 'USD' or sr2.currency_b = 'USD')
2465 and (sr3.currency_a = 'USD' or sr3.currency_b = 'USD')
2466 and (sr2.currency_a = p_currency_from or sr2.currency_b = p_currency_from)
2467 and (sr3.currency_a = p_currency_to or sr3.currency_b= p_currency_to)
2468 and sr2.term_type = 'S'
2469 and sr3.term_type = 'S';
2470
2471 BEGIN
2472
2473 IF p_currency_from ='USD' OR p_currency_to='USD' THEN
2474 OPEN usd_cross;
2475 FETCH usd_cross INTO l_ask_price, l_bit_price;
2476 CLOSE usd_cross;
2477 ELSE
2478 OPEN other_cross;
2479 FETCH other_cross INTO l_ask_price, l_bit_price;
2480 CLOSE other_cross;
2481 END IF;
2482
2483 p_rate := (l_ask_price + l_bit_price) /2;
2484
2485
2486 END CURRENCY_CROSS_RATE;
2487
2488
2489 PROCEDURE GET_SETTLE_METHOD (p_prncpl_tax IN VARCHAR2,
2490 p_prncpl_ctype OUT NOCOPY VARCHAR2,
2491 p_prncpl_method OUT NOCOPY VARCHAR2,
2492 p_income_tax IN VARCHAR2,
2493 p_income_ctype OUT NOCOPY VARCHAR2,
2494 p_income_method OUT NOCOPY VARCHAR2) IS
2495
2496 CURSOR settle_method (l_tax_code VARCHAR2) IS
2497 SELECT calc_type,
2498 tax_settle_method
2499 FROM XTR_TAX_BROKERAGE_SETUP
2500 WHERE reference_code = l_tax_code;
2501
2502 BEGIN
2503
2504 p_prncpl_ctype := null;
2505 p_prncpl_method := null;
2506 p_income_ctype := null;
2507 p_income_method := null;
2508
2509 if p_prncpl_tax is not null then
2510 OPEN settle_method (p_prncpl_tax);
2511 FETCH settle_method INTO p_prncpl_ctype, p_prncpl_method;
2512 CLOSE settle_method;
2513 end if;
2514
2515 if p_income_tax is not null then
2516 OPEN settle_method (p_income_tax);
2517 FETCH settle_method INTO p_income_ctype, p_income_method;
2518 CLOSE settle_method;
2519 end if;
2520
2521 END GET_SETTLE_METHOD;
2522
2523 PROCEDURE CALC_TMM_TAX (p_prncpl_ctype IN VARCHAR2,
2524 p_prncpl_method IN VARCHAR2,
2525 p_income_ctype IN VARCHAR2,
2526 p_income_method IN VARCHAR2,
2527 p_settle_acct IN VARCHAR2,
2528 p_last_tran IN VARCHAR2,
2529 p_RT IN OUT NOCOPY XTR_ROLLOVER_TRANSACTIONS_V%ROWTYPE) IS
2530
2531 l_hce_rate NUMBER;
2532 l_hce_rounding NUMBER;
2533 l_orig_prncpl_amt NUMBER;
2534 l_orig_income_amt NUMBER;
2535 l_prncpl_amt_hce NUMBER;
2536 l_income_amt_hce NUMBER;
2537 l_prn_a_amt NUMBER := 0;
2538 l_num_days NUMBER;
2539 l_dummy_num NUMBER;
2540 l_yr_basis NUMBER;
2541 l_err_code NUMBER;
2542 l_level VARCHAR2(20);
2543 l_p_tax_date DATE;
2544 l_i_tax_date DATE;
2545
2546 --------------------------------------------
2547 -- Get BALANCE_OUT for each transaction
2548 --------------------------------------------
2549 cursor RT_BAL_OUT IS
2550 select START_DATE,
2551 MATURITY_DATE,
2552 BALANCE_OUT
2553 from XTR_ROLLOVER_TRANSACTIONS
2554 where deal_number = p_RT.deal_number
2555 order by start_date, maturity_date, transaction_number;
2556
2557 --------------------------------------------
2558 -- Get rounding currency and hce_rate
2559 --------------------------------------------
2560 cursor ROUND_FACTOR (l_ccy VARCHAR2) is
2561 select hce_rate
2562 from XTR_MASTER_CURRENCIES_V
2563 where currency = l_ccy;
2564
2565 --------------------------------------------
2566 -- Get home rounding curency
2567 --------------------------------------------
2568 cursor HCE_ROUND_FACTOR is
2569 select a.rounding_factor
2570 from XTR_MASTER_CURRENCIES_V a,
2571 XTR_PRO_PARAM b
2572 where b.param_name = 'SYSTEM_FUNCTIONAL_CCY'
2573 and a.currency = param_value;
2574
2575 one_step_rec ONE_STEP_REC_TYPE;
2576
2577
2578 BEGIN
2579
2580 l_orig_prncpl_amt := nvl(p_RT.PRINCIPAL_TAX_AMOUNT,0);
2581 l_orig_income_amt := nvl(p_RT.TAX_AMOUNT,0);
2582
2583 if p_RT.START_DATE is not null and p_RT.CURRENCY is not null and
2584 (p_RT.PRINCIPAL_TAX_CODE is not null or p_RT.TAX_CODE is not null) then
2585
2586 open ROUND_FACTOR(p_RT.currency);
2587 fetch ROUND_FACTOR into l_hce_rate;
2588 close ROUND_FACTOR;
2589
2590 open HCE_ROUND_FACTOR;
2591 fetch HCE_ROUND_FACTOR into l_hce_rounding;
2592 close HCE_ROUND_FACTOR;
2593
2594 IF ((p_prncpl_ctype = 'PRN_F' and p_RT.transaction_number = 1) or
2595 (p_prncpl_ctype = 'MAT_F' and p_last_tran = 'Y') or
2596 (p_prncpl_ctype = 'PRN_INC_F' and p_RT.principal_action = 'INCRSE') or
2597 (p_prncpl_ctype = 'PRN_DEC_F' and p_RT.principal_action = 'DECRSE')) and
2598 p_RT.PRINCIPAL_TAX_CODE is not null and p_RT.PRINCIPAL_TAX_RATE is not null THEN
2599
2600 if p_prncpl_ctype = 'MAT_F' then
2601 l_p_tax_date := p_RT.maturity_date;
2602 else
2603 l_p_tax_date := p_RT.start_date;
2604 end if;
2605
2606 XTR_FPS1_P.CALC_TAX_AMOUNT('TMM', -- IN deal type
2607 l_p_tax_date, -- IN deal date
2608 p_RT.PRINCIPAL_TAX_CODE, -- IN principal tax schedule
2609 null, -- IN income tax schedule
2610 p_RT.CURRENCY, -- IN currency (buy ccy for FX)
2611 null, -- IN sell ccy if FX
2612 null, -- IN year basis
2613 null, -- IN number of days
2614 p_RT.PRINCIPAL_ADJUST, -- IN principal tax amount
2615 p_RT.PRINCIPAL_TAX_RATE , -- IN/OUT principal tax rate
2616 0, -- IN income tax amount
2617 l_dummy_num , -- IN/OUT income tax rate
2618 p_RT.PRINCIPAL_TAX_AMOUNT, -- IN/OUT calculated principal tax
2619 l_dummy_num, -- IN/OUT calculated income tax
2620 l_err_code, -- OUT
2621 l_level); -- OUT
2622
2623 END IF;
2624
2625
2626 IF p_prncpl_ctype = 'PRN_A' and p_last_tran = 'Y' and
2627 p_RT.PRINCIPAL_TAX_CODE is not null and p_RT.PRINCIPAL_TAX_RATE is not null then
2628
2629 p_RT.PRINCIPAL_TAX_AMOUNT := 0;
2630
2631 FOR PRN_A in RT_BAL_OUT LOOP
2632
2633 l_p_tax_date := PRN_A.MATURITY_DATE;
2634 l_prn_a_amt := 0;
2635
2636 XTR_CALC_P.CALC_DAYS_RUN(PRN_A.START_DATE,
2637 PRN_A.MATURITY_DATE,
2638 p_RT.YEAR_CALC_TYPE,
2639 l_num_days,
2640 l_yr_basis);
2641
2642 XTR_FPS1_P.CALC_TAX_AMOUNT('TMM', -- IN deal type
2643 l_p_tax_date, -- IN deal date
2644 p_RT.PRINCIPAL_TAX_CODE, -- IN principal tax schedule
2645 null, -- IN income tax schedule
2646 p_RT.CURRENCY, -- IN currency (buy ccy for FX)
2647 null, -- IN sell ccy if FX
2648 l_yr_basis, -- IN year basis
2649 l_num_days, -- IN number of days
2650 PRN_A.BALANCE_OUT, -- IN principal tax amount
2651 p_RT.PRINCIPAL_TAX_RATE , -- IN/OUT principal tax rate
2652 0, -- IN income tax amount
2653 l_dummy_num, -- IN/OUT income tax rate
2654 l_prn_a_amt, -- IN/OUT calculated principal tax
2655 l_dummy_num, -- IN/OUT calculated income tax
2656 l_err_code, -- OUT
2657 l_level); -- OUT
2658
2659 p_RT.PRINCIPAL_TAX_AMOUNT := p_RT.PRINCIPAL_TAX_AMOUNT + nvl(l_prn_a_amt,0);
2660
2661 END LOOP;
2662
2663 END IF;
2664
2665 IF p_income_ctype = 'INS_F' and p_RT.TAX_CODE is not null and
2666 p_RT.TAX_RATE is not null THEN
2667
2668 l_i_tax_date := p_RT.SETTLE_DATE; -- Interest Settlement Date -- bug 3018106
2669
2670 XTR_FPS1_P.CALC_TAX_AMOUNT('TMM', -- IN deal type
2671 l_i_tax_date, -- IN deal date
2672 null, -- IN principal tax schedule
2673 p_RT.TAX_CODE, -- IN income tax schedule
2674 p_RT.CURRENCY, -- IN currency (buy ccy for FX)
2675 null, -- IN sell ccy if FX
2676 null, -- IN year basis
2677 null, -- IN number of days
2678 null, -- IN principal tax amount
2679 l_dummy_num , -- IN/OUT principal tax rate
2680 p_RT.INTEREST_SETTLED, -- IN income tax amount
2681 p_RT.TAX_RATE, -- IN/OUT income tax rate
2682 l_dummy_num, -- IN/OUT calculated principal tax
2683 p_RT.TAX_AMOUNT, -- IN/OUT calculated income tax
2684 l_err_code, -- OUT
2685 l_level); -- OUT
2686
2687 p_RT.TAX_AMOUNT_HCE := round(nvl(p_RT.TAX_AMOUNT,0)/l_hce_rate,nvl(l_hce_rounding,2));
2688
2689 END IF;
2690
2691 l_prncpl_amt_hce := round(nvl(p_RT.PRINCIPAL_TAX_AMOUNT,0)/l_hce_rate,nvl(l_hce_rounding,2));
2692 l_income_amt_hce := nvl(p_RT.TAX_AMOUNT_HCE,0);
2693
2694 -----------------------------------------------------------------------
2695 -- Delete tax related EXP and DDA -- Bug 2506786
2696 -----------------------------------------------------------------------
2697 if ((p_RT.PRINCIPAL_TAX_SETTLED_REF is not null and
2698 l_orig_prncpl_amt <> nvl(p_RT.PRINCIPAL_TAX_AMOUNT,0)) or
2699 (p_RT.TAX_SETTLED_REFERENCE is not null)) then
2700 DELETE_TAX_EXPOSURE(p_RT.DEAL_NUMBER, p_RT.TRANSACTION_NUMBER);
2701 p_RT.PRINCIPAL_TAX_SETTLED_REF := null;
2702 p_RT.TAX_SETTLED_REFERENCE := null;
2703 end if;
2704
2705 ---------------------------------------------------------------------
2706 -- One Step Method - Generate EXP and DDA
2707 ---------------------------------------------------------------------
2708 if p_prncpl_method = 'OSG' and p_RT.PRINCIPAL_TAX_SETTLED_REF is null and
2709 l_orig_prncpl_amt <> nvl(p_RT.PRINCIPAL_TAX_AMOUNT,0) then
2710 one_step_rec.p_source := 'TAX';
2711 one_step_rec.p_schedule_code := p_RT.PRINCIPAL_TAX_CODE;
2712 one_step_rec.p_currency := p_RT.CURRENCY;
2713 one_step_rec.p_amount := p_RT.PRINCIPAL_TAX_AMOUNT;
2714 one_step_rec.p_settlement_date := l_p_tax_date;
2715 one_step_rec.p_settlement_account := p_settle_acct;
2716 one_step_rec.p_company_code := p_RT.COMPANY_CODE;
2717 one_step_rec.p_cparty_code := p_RT.CPARTY_CODE;
2718
2719 CALC_TMM_ONE_STEP ('P', p_RT.deal_number,
2720 p_RT.transaction_number,
2721 l_prncpl_amt_hce,
2722 p_RT.PRINCIPAL_TAX_SETTLED_REF,
2723 p_prncpl_method,
2724 null,
2725 null,
2726 one_step_rec);
2727
2728 p_RT.PRINCIPAL_TAX_SETTLED_REF := one_step_rec.p_exp_number;
2729
2730 end if;
2731
2732 if p_income_method = 'OSG' and p_RT.TAX_SETTLED_REFERENCE is null then
2733 -- l_orig_income_amt <> nvl(p_RT.TAX_AMOUNT,0) then -- bug 3018106
2734
2735 one_step_rec.p_source := 'TAX';
2736 one_step_rec.p_schedule_code := p_RT.TAX_CODE;
2737 one_step_rec.p_currency := p_RT.CURRENCY;
2738 one_step_rec.p_amount := p_RT.TAX_AMOUNT;
2739 one_step_rec.p_settlement_date := l_i_tax_date;
2740 one_step_rec.p_settlement_account := p_settle_acct;
2741 one_step_rec.p_company_code := p_RT.COMPANY_CODE;
2742 one_step_rec.p_cparty_code := p_RT.CPARTY_CODE;
2743
2744 CALC_TMM_ONE_STEP('I', p_RT.deal_number,
2745 p_RT.transaction_number,
2746 l_income_amt_hce,
2747 null,
2748 null,
2749 p_RT.TAX_SETTLED_REFERENCE,
2750 p_income_method,
2751 one_step_rec);
2752
2753 p_RT.TAX_SETTLED_REFERENCE := one_step_rec.p_exp_number;
2754
2755 end if;
2756
2757 end if;
2758
2759 END CALC_TMM_TAX;
2760
2761
2762 PROCEDURE CALC_TMM_ONE_STEP (p_tax_type IN VARCHAR2,
2763 p_deal_no IN NUMBER,
2764 p_tran_no IN NUMBER,
2765 p_amt_hce IN NUMBER,
2766 p_prncpl_ref IN NUMBER,
2767 p_prncpl_method IN VARCHAR2,
2768 p_income_ref IN NUMBER,
2769 p_income_method IN VARCHAR2,
2770 p_one_step IN OUT NOCOPY ONE_STEP_REC_TYPE) IS
2771
2772 BEGIN
2773
2774 p_one_step.p_exp_number := null;
2775
2776 --------------------------------------------------------------------
2777 -- Principal Tax
2778 --------------------------------------------------------------------
2779 if p_tax_type = 'P' then
2780
2781 if p_prncpl_method = 'OSG' and nvl(p_one_step.p_amount,0) <> 0 then
2782
2783 ONE_STEP_SETTLEMENT(p_one_step);
2784
2785 end if;
2786
2787 ---------------------------------------------------------------------------
2788 /* The following logic handles update for OSG only if it is a 1-1 relationship
2789 ---------------------------------------------------------------------------
2790 if p_prncpl_ref is not null and p_prncpl_method <> 'OSG' then
2791
2792 ---------------------------------------
2793 -- Replace this with DELETE_TAX_EXPOSURE
2794 ---------------------------------------
2795 delete XTR_EXPOSURE_TRANSACTIONS
2796 where TRANSACTION_NUMBER = p_prncpl_ref;
2797
2798 delete XTR_DEAL_DATE_AMOUNTS
2799 where DEAL_TYPE = 'EXP'
2800 and TRANSACTION_NUMBER = p_prncpl_ref;
2801
2802 end if;
2803
2804 if p_prncpl_method = 'OSG' then
2805
2806 if p_prncpl_ref is null and nvl(p_one_step.p_amount,0) <> 0 then
2807
2808 ONE_STEP_SETTLEMENT(p_one_step);
2809
2810 elsif p_prncpl_ref is not null then
2811
2812 if nvl(p_one_step.p_amount,0) = 0 then
2813
2814 delete XTR_EXPOSURE_TRANSACTIONS
2815 where TRANSACTION_NUMBER = p_prncpl_ref;
2816
2817 delete XTR_DEAL_DATE_AMOUNTS
2818 where DEAL_TYPE = 'EXP'
2819 and TRANSACTION_NUMBER = p_prncpl_ref;
2820
2821 p_one_step.p_exp_number := null;
2822
2823 else
2824
2825 update XTR_EXPOSURE_TRANSACTIONS
2826 set AMOUNT = abs(nvl(p_one_step.p_amount,0)),
2827 AMOUNT_HCE = abs(nvl(p_amt_hce,0)),
2828 VALUE_DATE = p_one_step.p_settlement_date
2829 where TRANSACTION_NUMBER = p_prncpl_ref;
2830
2831 update XTR_DEAL_DATE_AMOUNTS
2832 set AMOUNT = abs(p_one_step.p_amount),
2833 HCE_AMOUNT = abs(p_amt_hce),
2834 AMOUNT_DATE = p_one_step.p_settlement_date,
2835 CASHFLOW_AMOUNT = decode(ACTION_CODE,'PAY',-1,1) * abs(p_one_step.p_amount)
2836 where DEAL_TYPE = 'EXP'
2837 and TRANSACTION_NUMBER = p_prncpl_ref;
2838
2839 p_one_step.p_exp_number := p_prncpl_ref;
2840
2841 end if;
2842
2843 end if;
2844
2845 end if;
2846 */
2847
2848 --------------------------------------------------------------------
2849 -- Income Tax
2850 --------------------------------------------------------------------
2851 elsif p_tax_type = 'I' then
2852
2853 if p_income_method = 'OSG' and nvl(p_one_step.p_amount,0) <> 0 then
2854
2855 ONE_STEP_SETTLEMENT(p_one_step);
2856
2857 end if;
2858
2859 ---------------------------------------------------------------------------
2860 /* The following logic handles update for OSG only if it is a 1-1 relationship
2861 ---------------------------------------------------------------------------
2862 if p_income_ref is not null and p_income_method <> 'OSG' then
2863
2864 ---------------------------------------
2865 -- Replace this with Jeremy's procedure
2866 ---------------------------------------
2867 delete XTR_EXPOSURE_TRANSACTIONS
2868 where TRANSACTION_NUMBER = p_income_ref;
2869
2870 delete XTR_DEAL_DATE_AMOUNTS
2871 where DEAL_TYPE = 'EXP'
2872 and TRANSACTION_NUMBER = p_income_ref;
2873
2874 end if;
2875
2876 if p_income_method = 'OSG' then
2877
2878 if p_income_ref is null and nvl(p_one_step.p_amount,0) <> 0 then
2879
2880 ONE_STEP_SETTLEMENT(p_one_step);
2881
2882 elsif p_income_ref is not null then
2883
2884 if nvl(p_one_step.p_amount,0) = 0 then
2885
2886 delete XTR_EXPOSURE_TRANSACTIONS
2887 where TRANSACTION_NUMBER = p_income_ref;
2888
2889 delete XTR_DEAL_DATE_AMOUNTS
2890 where DEAL_TYPE = 'EXP'
2891 and TRANSACTION_NUMBER = p_income_ref;
2892
2893 p_one_step.p_exp_number := null;
2894
2895 else
2896
2897 update XTR_EXPOSURE_TRANSACTIONS
2898 set AMOUNT = abs(nvl(p_one_step.p_amount,0)),
2899 AMOUNT_HCE = abs(nvl(p_amt_hce,0)),
2900 VALUE_DATE = p_one_step.p_settlement_date
2901 where TRANSACTION_NUMBER = p_income_ref;
2902
2903 update XTR_DEAL_DATE_AMOUNTS
2904 set AMOUNT = abs(p_one_step.p_amount),
2905 HCE_AMOUNT = abs(p_amt_hce),
2906 AMOUNT_DATE = p_one_step.p_settlement_date,
2907 CASHFLOW_AMOUNT = decode(ACTION_CODE,'PAY',-1,1) * abs(p_one_step.p_amount)
2908 where DEAL_TYPE = 'EXP'
2909 and TRANSACTION_NUMBER = p_income_ref;
2910
2911 p_one_step.p_exp_number := p_income_ref;
2912
2913 end if;
2914
2915 end if;
2916
2917 end if;
2918 */
2919
2920 end if;
2921
2922 END CALC_TMM_ONE_STEP;
2923
2924
2925 -- This procedure removes exps and ddas corresponding to a deal_no and
2926 -- transaction number. It also updates other deals that may refer to the
2927 -- same exp to set their tax ref to null. It does not commit.
2928 PROCEDURE DELETE_TAX_EXPOSURE(p_deal_no IN NUMBER,
2929 p_trans_no IN NUMBER)
2930
2931 IS
2932 -- The following 2 cursors are used if given transaction number
2933 cursor t_rollover_deal_exposures is
2934 select a.tax_settled_reference
2935 from xtr_rollover_transactions a
2936 where a.deal_number = p_deal_no and
2937 a.tax_settled_reference is not null and
2938 a.transaction_number = p_trans_no;
2939
2940 cursor t_rollover_deal_exposures_p is
2941 select a.principal_tax_settled_ref
2942 from xtr_rollover_transactions a
2943 where a.deal_number= p_deal_no and
2944 a.principal_tax_settled_ref is not null and
2945 a.transaction_number = p_trans_no;
2946
2947 -- The following 2 cursor are used if not give a trasaction number
2948 cursor rollover_deal_exposures is
2949 select a.tax_settled_reference
2950 from xtr_rollover_transactions a
2951 where a.deal_number = p_deal_no and
2952 a.tax_settled_reference is not null;
2953
2954 cursor rollover_deal_exposures_p is
2955 select a.principal_tax_settled_ref
2956 from xtr_rollover_transactions a
2957 where a.deal_number= p_deal_no and
2958 a.principal_tax_settled_ref is not null;
2959
2960 cursor xtr_deals is
2961 select a.tax_settled_reference
2962 from xtr_deals a where a.deal_no = p_deal_no;
2963
2964 --bug 2727920
2965 cursor xtr_deals_int is
2966 select a.income_tax_settled_ref
2967 from xtr_deals a where a.deal_no = p_deal_no;
2968
2969 BEGIN
2970
2971
2972 --It transaction number is null then all deals in both the deals table
2973 -- and rollover table have their tax_exps deleted.
2974 if p_trans_no is null then
2975
2976 for deal_record in xtr_deals LOOP
2977 DELETE_TAX_EXP_AND_UPDATE(deal_record.tax_settled_reference);
2978 END LOOP;
2979
2980 for deal_record_int in xtr_deals_int LOOP
2981 DELETE_TAX_EXP_AND_UPDATE(deal_record_int.income_tax_settled_ref);
2982 END LOOP;
2983
2984 for roll_record in rollover_deal_exposures LOOP
2985 DELETE_TAX_EXP_AND_UPDATE(roll_record.tax_settled_reference);
2986 END LOOP;
2987
2988 for roll_record_p in rollover_deal_exposures_p LOOP
2989 DELETE_TAX_EXP_AND_UPDATE(roll_record_p.principal_tax_settled_ref);
2990 END LOOP;
2991
2992 -- If a transaction number is given only tax_exps related to that
2993 -- transaction are deleted.
2994 else
2995 for trans_roll_record in t_rollover_deal_exposures LOOP
2996 DELETE_TAX_EXP_AND_UPDATE(trans_roll_record.tax_settled_reference);
2997 END LOOP;
2998
2999 for trans_roll_record_p in t_rollover_deal_exposures_p LOOP
3000 DELETE_TAX_EXP_AND_UPDATE(trans_roll_record_p.principal_tax_settled_ref);
3001 END LOOP;
3002
3003 end if;
3004
3005 END DELETE_TAX_EXPOSURE;
3006
3007
3008 --Procedure removes exp and dda given a tax reference number. It also updates
3009 -- any deals with this reference number with a reference number of null.
3010 -- It does not commit.
3011 PROCEDURE DELETE_TAX_EXP_AND_UPDATE(p_tax_settle_no IN NUMBER)
3012 IS
3013
3014 BEGIN
3015
3016 if p_tax_settle_no is not null then
3017 delete from XTR_EXPOSURE_TRANSACTIONS
3018 where TRANSACTION_NUMBER = p_tax_settle_no;
3019
3020 delete from XTR_DEAL_DATE_AMOUNTS_V
3021 where DEAL_TYPE = 'EXP'
3022 and deal_number = 0
3023 and TRANSACTION_NUMBER = p_tax_settle_no;
3024
3025 update XTR_ROLLOVER_TRANSACTIONS
3026 set tax_settled_reference = null
3027 where tax_settled_reference = p_tax_settle_no;
3028
3029 update XTR_ROLLOVER_TRANSACTIONS
3030 set principal_tax_settled_ref = null
3031 where principal_tax_settled_ref = p_tax_settle_no;
3032
3033 update XTR_DEALS
3034 set tax_settled_reference = null
3035 where tax_settled_reference = p_tax_settle_no;
3036
3037 update XTR_DEALS
3038 set income_tax_settled_ref = null
3039 where income_tax_settled_ref = p_tax_settle_no;
3040
3041 END IF;
3042
3043
3044 END DELETE_TAX_EXP_AND_UPDATE;
3045
3046
3047
3048 --Bug 2804548
3049 --This procedure will update the Tax Entries in XTR_DEAL_DATE_AMOUNT
3050 --for One Step and Two Step Settlement Method.
3051 --p_amount = Old Tax Amount - New Tax Amount
3052 --p_exp_number = Tax Settled Reference Number
3053 PROCEDURE UPDATE_TAX_DDA (p_exp_number NUMBER,
3054 p_amount NUMBER) IS
3055 cursor get_dda_cashflow is
3056 select cashflow_amount
3057 from xtr_deal_date_amounts_v
3058 where transaction_number=p_exp_number
3059 and deal_type='EXP'
3060 and deal_number=0;
3061 v_cashflow NUMBER;
3062 BEGIN
3063 open get_dda_cashflow;
3064 fetch get_dda_cashflow into v_cashflow;
3065 close get_dda_cashflow;
3066 if v_cashflow=0 then
3067 --need to update with ADDition since the DDA cashflow_amount for TAX
3068 --is always negative
3069 update xtr_deal_date_amounts_v
3070 set amount=amount-nvl(p_amount,0)
3071 where transaction_number=p_exp_number
3072 and deal_type='EXP'
3073 and deal_number=0;
3074 else
3075 update xtr_deal_date_amounts_v
3076 set amount=amount-nvl(p_amount,0),
3077 cashflow_amount=cashflow_amount+nvl(p_amount,0)
3078 where transaction_number=p_exp_number
3079 and deal_type='EXP'
3080 and deal_number=0;
3081 end if;
3082 END UPDATE_TAX_DDA;
3083
3084
3085
3086 --Bug 2804548
3087 --This procedure will update the Tax Entries in XTR_EXPOSURE_TRANSACTIONS
3088 --for One Step and Two Step Settlement Method.
3089 --p_amount = Old Tax Amount - New Tax Amount
3090 --p_exp_number = Tax Settled Reference Number
3091 PROCEDURE UPDATE_TAX_EXP (p_exp_number NUMBER,
3092 p_amount NUMBER) IS
3093
3094 BEGIN
3095 update xtr_exposure_transactions
3096 set amount=amount-nvl(p_amount,0)
3097 where transaction_number=p_exp_number;
3098 END update_tax_exp;
3099
3100
3101 end XTR_FPS2_P;
[Home] [Help]