1 PACKAGE BODY XTR_FPS3_P as
2 /* $Header: xtrfps3b.pls 120.8 2005/06/29 07:52:08 badiredd ship $ */
3 ----------------------------------------------------------------------------------------------------------------
4 -- Procedure to validate foreign key value/query for deal
5 -- subtype entered
6 PROCEDURE CHK_DEAL_SUBTYPE (l_deal_type IN VARCHAR2,
7 l_deal_subtype IN VARCHAR2,
8 l_subtype_name IN OUT NOCOPY VARCHAR2,
9 l_limit_weighting IN OUT NOCOPY NUMBER,
10 l_tolerance IN OUT NOCOPY NUMBER,
11 l_err_code OUT NOCOPY NUMBER,
12 l_level OUT NOCOPY VARCHAR2) is
13 --
14 -- Since we change NAME's length 35 to 80 so we return NAME as NULL
15 --- to avoid truncation error
16 cursor C_DS is
17 select NULL NAME,LIMIT_WEIGHTING,TOLERANCE
18 from XTR_DEAL_SUBTYPES
19 where DEAL_TYPE = l_deal_type
20 and DEAL_SUBTYPE = l_deal_subtype;
21 --
22 begin
23 if (l_deal_type is NOT NULL and l_deal_subtype is NOT NULL) then
24 open C_DS;
25 fetch C_DS INTO l_subtype_name,l_limit_weighting,l_tolerance;
26 if C_DS%NOTFOUND then
27 l_err_code := 701; l_level := 'E';--This deal subtype does not exist
28 end if;
29 close C_DS;
30 end if;
31 end CHK_DEAL_SUBTYPE;
32
33 ----------------------------------------------------------------------------------------------------------------
34 -- Procedure to import holidays from GL
35 PROCEDURE IMPORT_GL_HOLIDAYS(p_calendar_in IN gl_transaction_calendar.name%TYPE,
36 p_currency_in IN xtr_holidays.currency%TYPE) IS
37
38 v_calendar gl_transaction_calendar%ROWTYPE;
39 v_holiday_seq xtr_holidays.hol_seq%TYPE;
40 v_nls_adjust NUMBER;
41
42 cursor get_calendar_id(p_calendar_name gl_transaction_calendar.name%TYPE) is
43 select *
44 from gl_transaction_calendar
45 where name=p_calendar_name;
46
47 cursor get_gl_holidays(p_calendar gl_transaction_calendar%ROWTYPE,
48 p_currency xtr_holidays.currency%TYPE,
49 p_sun NUMBER,
50 p_mon NUMBER,
51 p_tue NUMBER,
52 p_wed NUMBER,
53 p_thu NUMBER,
54 p_fri NUMBER,
55 p_sat NUMBER
56 ) is
57 select transaction_date
58 from gl_transaction_dates
59 where transaction_calendar_id = p_calendar.transaction_calendar_id
60 and business_day_flag = 'N'
61 -- The following code converts to an american week where Sun=1 to match GL calendar setup
62 and 'Y'=decode(((to_char(transaction_date,'D'))-1),
63 p_sun,p_calendar.sun_business_day_flag,
64 p_mon,p_calendar.mon_business_day_flag,
65 p_tue,p_calendar.tue_business_day_flag,
66 p_wed,p_calendar.wed_business_day_flag,
67 p_thu,p_calendar.thu_business_day_flag,
68 p_fri,p_calendar.fri_business_day_flag,
69 p_sat,p_calendar.sat_business_day_flag)
70 and not exists (
71 select holiday_date
72 from xtr_holidays
73 where currency=p_currency
74 and holiday_date=transaction_date);
75
76 BEGIN
77 if ((p_calendar_in is not null) and (p_currency_in is not null)) then
78 open get_calendar_id(p_calendar_in);
79 fetch get_calendar_id into v_calendar;
80 close get_calendar_id;
81
82 if (v_calendar.transaction_calendar_id is not null) then
83 select XTR_HOLIDAYS_S.nextval
84 into v_holiday_seq
85 from dual;
86
87 if (v_holiday_seq is null) then
88 v_holiday_seq:=1;
89 end if;
90
91 -- 01-jan-1995 is Sunday, we need to set up an adjustment value for NLS issues
92 v_nls_adjust:=(to_char(to_date('01/01/1995','MM/DD/YYYY'),'D')-1);
93
94 for gl_holiday in get_gl_holidays(v_calendar,
95 p_currency_in,
96 (0+v_nls_adjust) mod 7,
97 (1+v_nls_adjust) mod 7,
98 (2+v_nls_adjust) mod 7,
99 (3+v_nls_adjust) mod 7,
100 (4+v_nls_adjust) mod 7,
101 (5+v_nls_adjust) mod 7,
102 (6+v_nls_adjust) mod 7
103 ) loop
104 insert into xtr_holidays(
105 comments,
106 currency,
107 day_desc,
108 holiday_date,
109 hol_seq)
110 values (
111 'IG Imported Holiday',
112 p_currency_in,
113 gl_holiday.transaction_date,
114 gl_holiday.transaction_date,
115 v_holiday_seq);
116 end loop;
117 end if;
118 end if;
119
120 END IMPORT_GL_HOLIDAYS;
121
122
123
124
125
126
127 ----------------------------------------------------------------------------------------------------------------
128 -- Procedure to check to if Date is a Holiday or a Week End.
129 PROCEDURE CHK_HOLIDAY (in_date IN DATE,
130 l_currency IN VARCHAR2,
131 l_err_code OUT NOCOPY NUMBER,
132 l_level OUT NOCOPY VARCHAR2) is
133 --
134 v_dummy_count number;
135 v_dummy_char varchar2(1);
136 v_gl_calendar_name xtr_master_currencies_v.gl_calendar_name%TYPE;
137 v_nls_adjust number;
138 v_bHasGLCalendar boolean := false;
139
140 cursor HOL is
141 select count(*)
142 from XTR_HOLIDAYS
143 where HOLIDAY_DATE =in_date
144 and CURRENCY = l_currency;
145
146 cursor get_calendar_name(p_currency xtr_master_currencies_v.currency%TYPE) is
147 select gl_calendar_name
148 from xtr_master_currencies
149 where currency=p_currency
150 and authorised='Y';
151
152 cursor get_gl_weekend(p_calendar_name gl_transaction_calendar.name%TYPE,
153 p_date DATE,
154 p_sun NUMBER,
155 p_mon NUMBER,
156 p_tue NUMBER,
157 p_wed NUMBER,
158 p_thu NUMBER,
159 p_fri NUMBER,
160 p_sat NUMBER
161 ) is
162 select decode(((to_char(p_date,'D'))-1),
163 p_sun,sun_business_day_flag,
164 p_mon,mon_business_day_flag,
165 p_tue,tue_business_day_flag,
166 p_wed,wed_business_day_flag,
167 p_thu,thu_business_day_flag,
168 p_fri,fri_business_day_flag,
169 p_sat,sat_business_day_flag)
170 from gl_transaction_calendar
171 where name = p_calendar_name;
172
173 --
174 begin
175 if in_date is NOT NULL then
176
177 open get_calendar_name(l_currency);
178 fetch get_calendar_name into v_gl_calendar_name;
179 close get_calendar_name;
180
181 if (v_gl_calendar_name is not null) then
182
183 -- Jan 1st 1995 is a Sunday, this is to fix NLS weekday starts
184
185 v_nls_adjust:=(to_char(to_date('01/01/1995','MM/DD/YYYY'),'D')-1);
186
187 open get_gl_weekend(v_gl_calendar_name,
188 in_date,
189 (0+v_nls_adjust) mod 7,
190 (1+v_nls_adjust) mod 7,
191 (2+v_nls_adjust) mod 7,
192 (3+v_nls_adjust) mod 7,
193 (4+v_nls_adjust) mod 7,
194 (5+v_nls_adjust) mod 7,
195 (6+v_nls_adjust) mod 7
196 );
197 fetch get_gl_weekend into v_dummy_char;
198 close get_gl_weekend;
199 if (v_dummy_char is not null) then
200 v_bHasGLCalendar:=true;
201 end if;
202 if (v_dummy_char='N') then --dummy char is Y for weekday, N for weekend
203 l_err_code := 128;
204 l_level := 'W';--This date is not a week day
205 end if;
206 end if;
207
208 if (v_bHasGLCalendar = false) then
209 -- force into english where the weekend starts with S
210 if ((substrb(
211 to_char(in_date,
212 'Day',
213 'nls_date_language=American'),
214 1,
215 1)
216 )='S') then
217 --Changed because NLS settings might dictate Monday to be day 1
218 --if not (to_char(in_date,'D') between 2 and 6) then --default to weekends when no calendar
219 l_err_code := 128;
220 l_level := 'W';--This date is not a week day
221 end if;
222 end if;
223
224 if (l_level is null) then
225 open HOL;
226 fetch HOL into v_dummy_count;
227 close HOL;
228 if (v_dummy_count>0) then
229 l_err_code := 126;
230 l_level := 'W';--This date is a holiday for this currency
231 end if;
232 end if;
233 end if;
234 end CHK_HOLIDAY;
235 ----------------------------------------------------------------------------------------------------------------
236 -- Procedure to check that no portfolios already exist for
237 -- this deal because only one is allowed per deal.
238 PROCEDURE CHK_NO_PORTFOLIOS (l_company_code IN VARCHAR2,
239 l_deal_number IN NUMBER,
240 l_err_code OUT NOCOPY NUMBER,
241 l_level OUT NOCOPY VARCHAR2) is
242 cursor PORT_NOS is
243 select 1
244 from xtr_portfolio_deal_amounts
245 where company_code = l_company_code
246 and deal_number = l_deal_number;
247 --
248 v_dummy number(1);
249 begin
250 open PORT_NOS;
251 fetch PORT_NOS INTO v_dummy;
252 if PORT_NOS%FOUND then
253 close PORT_NOS;
254 l_err_code := 240; l_level := 'E';--Only one portfolio may be created for each deal
255 else
256 close PORT_NOS;
257 end if;
258 end CHK_NO_PORTFOLIOS;
259 ----------------------------------------------------------------------------------------------------------------
260 -- Procedure to validate the portfolio code.
261 PROCEDURE CHK_PORT_CODE (l_portfolio_code IN VARCHAR2,
262 l_company_code IN VARCHAR2,
263 l_portfolio_name IN OUT NOCOPY VARCHAR2,
264 l_err_code OUT NOCOPY NUMBER,
265 l_level OUT NOCOPY VARCHAR2) is
266 --
267 cursor PORTFOLIO is
268 select NULL NAME
269 from XTR_PORTFOLIOS
270 where PORTFOLIO = l_portfolio_code
271 and COMPANY_CODE = l_company_code;
272 --
273 begin
274 if (l_company_code is NOT NULL and l_portfolio_code is NOT NULL) then
275 open PORTFOLIO;
276 fetch PORTFOLIO INTO l_portfolio_name;
277 if PORTFOLIO%NOTFOUND then
278 l_err_code := 701; l_level := 'E';--This portfolio does not exist
279 end if;
280 close PORTFOLIO;
281 end if;
282 end CHK_PORT_CODE;
283 ----------------------------------------------------------------------------------------------------------------
284 -- Procedure to check constraint for portfolio nos.
285 PROCEDURE CHK_PORT_CONST ( l_portfolio_code IN VARCHAR2,
286 l_deal_number IN NUMBER,
287 l_err_code OUT NOCOPY NUMBER,
288 l_level OUT NOCOPY VARCHAR2) is
289 --
290 cursor PORTFOLIO_NOS is
291 select 1
292 from XTR_PORTFOLIO_DEAL_AMOUNTS
293 where DEAL_NUMBER = l_deal_number
294 and PORTFOLIO_CODE = l_portfolio_code;
295 --
296 v_dummy number(1);
297 begin
298 open PORTFOLIO_NOS;
299 fetch PORTFOLIO_NOS INTO v_dummy;
300 if PORTFOLIO_NOS%FOUND then
301 close PORTFOLIO_NOS;
302 l_err_code := 212; l_level := 'E';--Row exists already with same Deal No,Portfolio
303 else
304 close PORTFOLIO_NOS;
305 end if;
306 close PORTFOLIO_NOS;
307 end CHK_PORT_CONST ;
308 ----------------------------------------------------------------------------------------------------------------
309 -- Procedure to check that bank account has been entered if
310 -- principal amt is not null
311 PROCEDURE CHK_PRINCIPAL_BANK (l_company_code IN VARCHAR2,
312 l_currency IN VARCHAR2,
313 l_prin_adjust IN NUMBER,
314 l_prin_acct IN VARCHAR2,
315 l_err_code OUT NOCOPY NUMBER,
316 l_level OUT NOCOPY VARCHAR2) is
317 --
318 cursor BANK_AC is
319 select 1
320 from XTR_BANK_ACCOUNTS
321 where PARTY_CODE = l_company_code
322 and CURRENCY = l_currency;
323 --
324 v_dummy number(1);
325 begin
326 if l_prin_adjust <> 0 and l_prin_acct is NULL then
327 open BANK_AC;
328 fetch BANK_AC into v_dummy;
329 if BANK_AC%FOUND then
330 close BANK_AC;
331 l_err_code := 140; l_level := 'E';--Please enter a bank account for Principal Flow
332 else
333 close BANK_AC;
334 end if;
335 end if;
336 end CHK_PRINCIPAL_BANK;
337 ------------------------------------------------------------------------------------------------------------------
338 -- Procedure to validate the printer name entered
342 l_level OUT NOCOPY VARCHAR2) is
339 PROCEDURE CHK_PRINTER_NAME(l_p_name IN VARCHAR2,
340 l_p_value IN OUT NOCOPY VARCHAR2,
341 l_err_code OUT NOCOPY NUMBER,
343 --
344 cursor CHK_PTR is
345 select PARAM_VALUE
346 from XTR_PRO_PARAM
347 where PARAM_NAME = l_p_name
348 and PARAM_TYPE = 'PRINTER';
349 --
350 begin
351 if l_p_name is NOT NULL then
352 open CHK_PTR;
353 fetch CHK_PTR INTO l_p_value;
354 if CHK_PTR%NOTFOUND then
355 l_err_code := 701; l_level := 'E';-- Invalid Value, Refer <LIST>.
356 end if;
357 close CHK_PTR;
358 end if;
359 end CHK_PRINTER_NAME;
360 ----------------------------------------------------------------------------------------------------------------
361 -- Procedure to check constraint for rollover transactions.
362 PROCEDURE CHK_ROLLOVER (l_deal_number IN NUMBER,
363 l_start_date IN DATE,
364 l_err_code OUT NOCOPY NUMBER,
365 l_level OUT NOCOPY VARCHAR2) is
366 --
367 cursor CHK_RT_ROW is
368 select 1
369 from XTR_ROLLOVER_TRANSACTIONS_V
370 where START_DATE = l_start_date
371 and DEAL_NUMBER = l_deal_number;
372 --
373 v_dummy number(1);
374 begin
375 open CHK_RT_ROW;
376 fetch CHK_RT_ROW INTO v_dummy;
377 if CHK_RT_ROW%FOUND then
378 close CHK_RT_ROW;
379 l_err_code := 236; l_level := 'E';-- Row exists already with same Start Date, Deal Number
380 else
381 close CHK_RT_ROW;
382 end if;
383 end CHK_ROLLOVER;
384 ----------------------------------------------------------------------------------------------------------------
385 -- Procedure to validate deal status code input is correct
386 PROCEDURE CHK_STATUS_CODE (
387 l_status_code IN VARCHAR2,
388 l_deal_type IN VARCHAR2,
389 l_record_status IN VARCHAR2,
390 l_status_name IN OUT NOCOPY VARCHAR2,
391 l_statcode_updateable IN OUT NOCOPY VARCHAR2,
392 l_err_code OUT NOCOPY NUMBER,
393 l_level OUT NOCOPY VARCHAR2) is
394 --
395 cursor STATUS is
396 select NULL DESCRIPTION, UPDATEABLE
397 from XTR_DEAL_STATUSES
398 where STATUS_CODE = l_status_code
399 and DEAL_TYPE = l_deal_type
400 and AUTO_USER_SET = decode(l_record_status,'QUERY',
401 AUTO_USER_SET,'U');
402 --
403 begin
404 open STATUS;
405 fetch STATUS INTO l_status_name, l_statcode_updateable;
406 if STATUS%NOTFOUND then
407 l_err_code := 701; l_level := 'E';--This Deal Status does not exist
408 end if;
409 close STATUS;
410 end CHK_STATUS_CODE;
411 ----------------------------------------------------------------------------------------------------------------
412 PROCEDURE CHK_FX_TOLERANCE(l_rate IN NUMBER,
413 l_currency_a IN VARCHAR2,
414 l_currency_b IN VARCHAR2,
415 l_tolerance IN NUMBER,
416 l_err_code OUT NOCOPY NUMBER,
417 l_level OUT NOCOPY VARCHAR2) is
418 --
419 /* Procedure for FX INSTRUMENTS using tolerance retrieved from Deal
420 Subtypes to calculate allowable base rate and transaction rate ranges. If the tolerance
421 is null then there is no further checking. If the tolerance is not null
422 then find the latest bid rate found for this buy/sell currency comb and
423 add and subtract the tolerance from this to create an acceptable rate
424 range.*/
425 --
426
427 /* Bug 3142490
428 mkt_rate number(9,5);
429 mkt_rate1 number(9,5);
430 mkt_rate2 number(9,5);
431 low_rate number(9,5);
432 high_rate number(9,5);
433 */
434
435 mkt_rate number;
436 mkt_rate1 number;
437 mkt_rate2 number;
438 low_rate number;
439 high_rate number;
440 ccy_f varchar2(15);
441 --
442 -- Get ccy first
443 cursor CF is
444 select a.CURRENCY_FIRST
445 from XTR_BUY_SELL_COMBINATIONS a
446 where ((a.CURRENCY_BUY = l_currency_a and a.CURRENCY_SELL = l_currency_b)
447 or (a.CURRENCY_BUY = l_currency_b and a.CURRENCY_SELL = l_currency_a));
448 --
449 cursor TOL1 is
450 select a.USD_QUOTED_SPOT
451 from XTR_MASTER_CURRENCIES a
452 where a.CURRENCY = l_currency_a;
453 --
454 cursor TOL2 is
455 select a.USD_QUOTED_SPOT
456 from XTR_MASTER_CURRENCIES a
457 where a.CURRENCY = l_currency_b;
458 --
459 begin
460 if l_tolerance is NOT NULL then
461 -- Determine which ccy is quoted first
462 open CF;
463 fetch CF INTO ccy_f;
464 close CF;
465 -- First Ccy USD spot Rate
466 open TOL1;
467 fetch TOL1 INTO mkt_rate1;
468 close TOL1;
469 -- Second Ccy USD spot Rate
470 open TOL2;
471 fetch TOL2 INTO mkt_rate2;
472 close TOL2;
473 -- Calc rate
474 if l_currency_a = ccy_f then
475 mkt_rate := mkt_rate2 / mkt_rate1;
476 else
477 mkt_rate := mkt_rate1 / mkt_rate2;
478 end if;
482 --
479 --
480 low_rate := mkt_rate - (mkt_rate * (l_tolerance / 100));
481 high_rate := mkt_rate + (mkt_rate * (l_tolerance / 100));
483 if l_rate < low_rate OR l_rate > high_rate then
484 l_err_code := 598; l_level := 'W';--FX Rate does not fall within acceptable
485 --tolerance
486 end if;
487 else
488 l_err_code := 599; l_level := 'W';--Could not find record in table
489 end if;
490 end CHK_FX_TOLERANCE;
491 ----------------------------------------------------------------------------------------------------------------
492 PROCEDURE CHK_TOLERANCE (l_rate IN NUMBER,
493 l_currency IN VARCHAR2,
494 l_tolerance IN NUMBER,
495 l_period IN NUMBER,
496 l_unique_id IN VARCHAR2,
497 l_err_code OUT NOCOPY NUMBER,
498 l_level OUT NOCOPY VARCHAR2) is
499 --
500 /* Procedure for INTEREST RATE INSTRUMENTS using tolerance retrieved from Deal
501 Subtypes to calculate allowable base rate and transaction rate ranges. If the tolerance
502 is null then there is no further checking. If the tolerance is not null
503 then find the latest bid rate found for this buy/sell currency comb and
504 add and subtract the tolerance from this to create an acceptable rate
505 range.*/
506
507 --
508
509 /* BUG 3142490
510 mkt_rate number(9,5);
511 low_rate number(9,5);
512 high_rate number(9,5);
513 */
514
515 mkt_rate number;
516 low_rate number;
517 high_rate number;
518
519 --
520 cursor TOL is
521 select (a.BID_PRICE + a.ASK_PRICE) / 2
522 from XTR_MARKET_PRICES a
523 where a.CURRENCY_A = l_currency
524 and ((a.NOS_OF_DAYS <= l_period and nvl(l_unique_id,'%') = '%'
525 and a.NOS_OF_DAYS <> 0 and a.TERM_TYPE NOT IN('S','F','W','V','O')) or
526 (a.RIC_CODE = l_unique_id and nvl(l_unique_id,'%') <> '%'))
527 order by NOS_OF_DAYS desc;
528 --
529 begin
530 if l_tolerance is NOT NULL then
531 open TOL;
532 fetch TOL INTO mkt_rate;
533 if TOL%FOUND then
534 low_rate := mkt_rate - l_tolerance;
535 high_rate := mkt_rate + l_tolerance;
536 end if;
537 close TOL;
538 --
539 if l_rate < low_rate OR l_rate > high_rate then
540 l_err_code := 598; l_level := 'W';--Interest Rate does not fall within acceptable
541 --tolerance
542 end if;
543 else
544 l_err_code := 599; l_level := 'W';--Could not find record in table
545 end if;
546 end CHK_TOLERANCE;
547 ----------------------------------------------------------------------------------------------------------------
548 PROCEDURE CHK_TIME_RESTRICTIONS (l_deal_type IN VARCHAR2,
549 l_deal_subtype IN VARCHAR2,
550 l_product_type IN VARCHAR2,
551 l_cparty_code IN VARCHAR2,
552 l_date IN DATE,
553 l_max_date OUT NOCOPY DATE,
554 l_err_code OUT NOCOPY NUMBER,
555 l_level OUT NOCOPY VARCHAR2) is
556
557 --
558 cursor T_RES is
559 select RANGE -- range is nos of days max period contract can go out until
560 from XTR_TIME_RESTRICTIONS
561 where DEAL_TYPE = l_deal_type
562 and (DEAL_SUBTYPE = l_deal_subtype or DEAL_SUBTYPE is NULL)
563 and (SECURITY_NAME = l_product_type or SECURITY_NAME is NULL)
564 and (CPARTY_CODE = l_cparty_code or CPARTY_CODE is NULL)
565 order by CPARTY_CODE asc,SECURITY_NAME asc,DEAL_SUBTYPE asc;
566 --
567 l_max_days NUMBER(7);
568 --
569 begin
570 open T_RES;
571 fetch T_RES INTO l_max_days;
572 if T_RES%FOUND then
573 if (l_date - sysdate) > l_max_days then
574 -- This Deal exceeds max length of contract allowed
575 l_err_code := 124;
576 l_level := 'E';
577 l_max_date := sysdate + l_max_days;
578 end if;
579 end if;
580 close T_RES;
581 end CHK_TIME_RESTRICTIONS;
582
583
584 --Bug 2804548
585 --Previous business day: The convention that if a value date in the future
586 --falls on a non-business day, the value date will be moved to the previous
587 --business day.
588 FUNCTION previous_bus_day(p_date IN DATE,
589 p_ccy IN VARCHAR2) RETURN DATE IS
590 v_date_out DATE;
591 BEGIN
592 xtr_market_data_p.Modified_Following_Holiday(p_ccy,
593 p_date,
594 v_date_out);
595 return v_date_out;
596 END previous_bus_day;
597
598
599
600 --Bug 2804548
601 --Following business day: The convention that if a value date in the future
602 --falls on a non-business day, the value date will be moved to the next
603 --business day.
604 FUNCTION following_bus_day(p_date IN DATE,
605 p_ccy IN VARCHAR2) RETURN DATE IS
606
607 v_err_code number(8);
608 v_level varchar2(2) := ' ';
609 v_date DATE;
610 v_date_out DATE;
611
612 BEGIN
613
614 v_date:= p_date;
615 LOOP
616 -- keep on subtracting a day until it's not a holiday or weekend
617 v_date := v_date + 1;
621 v_level);
618 XTR_fps3_P.CHK_HOLIDAY (v_date,
619 p_ccy,
620 v_err_code,
622 EXIT WHEN v_err_code is null;
623 END LOOP;
624 v_date_out := v_date;
625 return v_date_out;
626
627 END following_bus_day;
628
629
630
631 --Bug 2804548
632 --Modified following business day: The convention that if a value date in
633 --the future falls on a non-business day, the value date will be moved to the
634 --next following business day, unless this moves the value date to the next
635 --month, in which case the value date is moved back to the previous business
636 --day.
637 FUNCTION mod_following_bus_day(p_date IN DATE,
638 p_ccy IN VARCHAR2) RETURN DATE IS
639 v_in_rec xtr_market_data_p.following_holiday_in_rec_type;
640 v_out_rec xtr_market_data_p.following_holiday_out_rec_type;
641 v_date_out DATE;
642 BEGIN
643 v_in_rec.p_term_type := 'M';
644 v_in_rec.p_currency := p_ccy;
645 v_in_rec.p_future_date := p_date;
646 v_in_rec.p_period_code := 1;
647 xtr_market_data_p.Following_Holiday(v_in_rec,v_out_rec);
648 if v_out_rec.p_date_out is null then
649 v_date_out := p_date;
650 else
651 v_date_out := v_out_rec.p_date_out;
652 end if;
653 return v_date_out;
654 END mod_following_bus_day;
655
656
657
658 --Bug 2804548
659 --Modified previous business day: The convention that if a value date in the
660 --future falls on a non-business day, the value date will be moved to the
661 --previous business day, unless this moves the value date to the previous
662 --month, in which case the value date is moved forward to the following
663 --business day.
664 FUNCTION mod_previous_bus_day(p_date IN DATE,
665 p_ccy IN VARCHAR2) RETURN DATE IS
666 v_date DATE := p_date;
667 v_date_out DATE;
668 v_err_code number(8);
669 v_level varchar2(2) := ' ';
670 BEGIN
671
672 LOOP
673 -- keep on adding a day until it's not a holiday or weekend
674 v_date:=v_date - 1;
675 XTR_fps3_P.CHK_HOLIDAY (v_date,
676 p_ccy,
677 v_err_code,
678 v_level);
679 EXIT WHEN v_err_code is null;
680 END LOOP;
681
682 -- if the month changed during the loop, do following_bus_day
683 IF TO_CHAR(v_date,'MM') <> TO_CHAR(p_date,'MM') THEN
684 v_date_out := Following_bus_day(v_date,p_ccy);
685 ELSE
686 v_date_out := v_date;
687 END IF;
688 return v_date_out;
689 END mod_previous_bus_day;
690
691
692
693 --Bug 2804548
694 --This procedure calculate the given date using the given Settlement
695 --Basis
696 PROCEDURE settlement_basis_calc(p_in_rec IN settlementbasis_in_rec,
697 p_out_rec IN OUT NOCOPY settlementbasis_out_rec) IS
698 v_err_code number(8);
699 v_level varchar2(2) := ' ';
700 v_date DATE;
701 BEGIN
702 XTR_fps3_P.CHK_HOLIDAY (p_in_rec.date_in,
703 p_in_rec.ccy,
704 v_err_code,
705 v_level);
706 if v_err_code is not null then --is holiday
707 if p_in_rec.settlement_basis='P' then
708 v_date := previous_bus_day(p_in_rec.date_in,
709 p_in_rec.ccy);
710 elsif p_in_rec.settlement_basis='F' then
711 v_date := following_bus_day(p_in_rec.date_in,
712 p_in_rec.ccy);
713 elsif p_in_rec.settlement_basis='MF' then
714 v_date := mod_following_bus_day(p_in_rec.date_in,
715 p_in_rec.ccy);
716 elsif p_in_rec.settlement_basis='MP' then
717 v_date := mod_previous_bus_day(p_in_rec.date_in,
718 p_in_rec.ccy);
719 else
720 v_date := p_in_rec.date_in;
721 end if;
722 else
723 v_date := p_in_rec.date_in;
724 end if;
725 p_out_rec.date_out := v_date;
726 END settlement_basis_calc;
727
728
729
730 --Bug 2804548
731 --Returning boolean to check whether any components of the CURRENT coupon
732 --has been settled or not.
733 PROCEDURE settled_validation(p_in_rec IN validation_in_rec,
734 p_out_rec IN OUT NOCOPY validation_out_rec) IS
735 cursor bond_coupon_settled(p_bond_issue_code VARCHAR2,
736 p_coupon_date DATE) is
737 select 1
738 from xtr_deals d, xtr_rollover_transactions rt, xtr_deal_date_amounts dda
739 where rt.deal_number=d.deal_no
740 and d.bond_issue=p_bond_issue_code
741 and rt.maturity_date=p_coupon_date
742 and dda.settle='Y'
743 and rt.deal_type='BOND'
744 and dda.deal_number=0
745 and dda.deal_type='EXP'
746 and ((rt.tax_settled_reference is not null
747 and dda.transaction_number=rt.tax_settled_reference)
748 or (rt.principal_tax_settled_ref is not null
749 and dda.transaction_number=rt.principal_tax_settled_ref))
750 UNION ALL
751 select 1
752 from xtr_deals d, xtr_rollover_transactions rt, xtr_deal_date_amounts dda
753 where rt.deal_number=d.deal_no
754 and d.bond_issue=p_bond_issue_code
755 and rt.maturity_date=p_coupon_date
756 and dda.settle='Y'
760 and dda.deal_type='BOND';
757 and rt.deal_type='BOND'
758 and dda.deal_number=rt.deal_number
759 and dda.transaction_number=rt.transaction_number
761
762 cursor bond_coupon_settled_all(p_bond_issue_code VARCHAR2) is
763 select 1
764 from xtr_deals d, xtr_rollover_transactions rt, xtr_deal_date_amounts dda
765 where rt.deal_number=d.deal_no
766 and d.bond_issue=p_bond_issue_code
767 and dda.settle='Y'
768 and rt.deal_type='BOND'
769 and dda.deal_number=0
770 and dda.deal_type='EXP'
771 and ((rt.tax_settled_reference is not null
772 and dda.transaction_number=rt.tax_settled_reference)
773 or (rt.principal_tax_settled_ref is not null
774 and dda.transaction_number=rt.principal_tax_settled_ref))
775 UNION ALL
776 select 1
777 from xtr_deals d, xtr_rollover_transactions rt, xtr_deal_date_amounts dda
778 where rt.deal_number=d.deal_no
779 and d.bond_issue=p_bond_issue_code
780 and dda.settle='Y'
781 and rt.deal_type='BOND'
782 and dda.deal_number=rt.deal_number
783 and dda.transaction_number=rt.transaction_number
784 and dda.deal_type='BOND';
785
786 v_dummy NUMBER;
787 BEGIN
788 p_out_rec.yes := FALSE;
789 if p_in_rec.deal_type='BOND' then
790 if p_in_rec.bond_coupon_date is not null then
791 open bond_coupon_settled(p_in_rec.bond_issue_code,
792 p_in_rec.bond_coupon_date);
793 fetch bond_coupon_settled into v_dummy;
794 p_out_rec.yes := bond_coupon_settled%FOUND;
795 close bond_coupon_settled;
796 else
797 open bond_coupon_settled_all(p_in_rec.bond_issue_code);
798 fetch bond_coupon_settled_all into v_dummy;
799 p_out_rec.yes := bond_coupon_settled_all%FOUND;
800 close bond_coupon_settled_all;
801 end if;
802 end if;
803 END settled_validation;
804
805
806
807 --Bug 2804548
808 --Returning boolean to check whether any components of the CURRENT coupon
809 --has been journaled or not.
810 PROCEDURE journaled_validation(p_in_rec IN validation_in_rec,
811 p_out_rec IN OUT NOCOPY validation_out_rec) IS
812 cursor bond_coupon_journaled (p_bond_issue_code VARCHAR2,
813 p_coupon_date DATE) is
814 select 1
815 from xtr_deals d, xtr_rollover_transactions rt, xtr_journals j
816 where rt.deal_number=d.deal_no
817 and d.bond_issue=p_bond_issue_code
818 and rt.maturity_date=p_coupon_date
819 and j.orig_journal_date is not null
820 and rt.deal_type='BOND'
821 and j.deal_number=rt.deal_number
822 and j.deal_type='EXP'
823 and ((rt.tax_settled_reference is not null
824 and j.transaction_number=rt.tax_settled_reference)
825 or (rt.principal_tax_settled_ref is not null
826 and j.transaction_number=rt.principal_tax_settled_ref))
827 UNION ALL
828 select 1
829 from xtr_deals d, xtr_rollover_transactions rt, xtr_journals j
830 where rt.deal_number=d.deal_no
831 and d.bond_issue=p_bond_issue_code
832 and rt.maturity_date=p_coupon_date
833 and j.orig_journal_date is not null
834 and rt.deal_type='BOND'
835 and j.deal_type=rt.deal_type
836 and rt.deal_number=j.deal_number
837 and rt.transaction_number=j.transaction_number;
838
839 cursor bond_coupon_journaled_all (p_bond_issue_code VARCHAR2) is
840 select 1
841 from xtr_deals d, xtr_rollover_transactions rt, xtr_journals j
842 where rt.deal_number=d.deal_no
843 and d.bond_issue=p_bond_issue_code
844 and j.orig_journal_date is not null
848 and ((rt.tax_settled_reference is not null
845 and rt.deal_type='BOND'
846 and j.deal_number=rt.deal_number
847 and j.deal_type='EXP'
849 and j.transaction_number=rt.tax_settled_reference)
850 or (rt.principal_tax_settled_ref is not null
851 and j.transaction_number=rt.principal_tax_settled_ref))
852 UNION ALL
853 select 1
854 from xtr_deals d, xtr_rollover_transactions rt, xtr_journals j
855 where rt.deal_number=d.deal_no
856 and d.bond_issue=p_bond_issue_code
857 and j.orig_journal_date is not null
858 and rt.deal_type='BOND'
859 and j.deal_type=rt.deal_type
860 and rt.deal_number=j.deal_number
861 and rt.transaction_number=j.transaction_number;
862
863 v_dummy NUMBER;
864 BEGIN
865 p_out_rec.yes := FALSE;
866 if p_in_rec.deal_type='BOND' then
867 if p_in_rec.bond_coupon_date is not null then
868 open bond_coupon_journaled(p_in_rec.bond_issue_code,
869 p_in_rec.bond_coupon_date);
870 fetch bond_coupon_journaled into v_dummy;
871 p_out_rec.yes := bond_coupon_journaled%FOUND;
872 close bond_coupon_journaled;
873 else
874 open bond_coupon_journaled_all(p_in_rec.bond_issue_code);
875 fetch bond_coupon_journaled_all into v_dummy;
876 p_out_rec.yes := bond_coupon_journaled_all%FOUND;
877 close bond_coupon_journaled_all;
878 end if;
879 end if;
880 END journaled_validation;
881
882
883
884 --Bug 2804548
885 --Returning boolean to check whether any components of the CURRENT coupon
886 --has been reconciled or not.
887 PROCEDURE reconciled_validation(p_in_rec IN validation_in_rec,
888 p_out_rec IN OUT NOCOPY validation_out_rec) IS
889 cursor bond_coupon_reconciled (p_bond_issue_code VARCHAR2,
890 p_coupon_date DATE) is
891 select 1
892 from xtr_deals d, xtr_rollover_transactions rt, xtr_deal_date_amounts dda
893 where rt.deal_number=d.deal_no
894 and d.bond_issue=p_bond_issue_code
895 and rt.maturity_date=p_coupon_date
896 and dda.reconciled_reference is not null
897 and rt.deal_type='BOND'
898 and dda.deal_number=0
899 and dda.deal_type='EXP'
900 and ((rt.tax_settled_reference is not null
901 and dda.transaction_number=rt.tax_settled_reference)
902 or (rt.principal_tax_settled_ref is not null
903 and dda.transaction_number=rt.principal_tax_settled_ref))
904 UNION ALL
905 select 1
906 from xtr_deals d, xtr_rollover_transactions rt, xtr_deal_date_amounts dda
907 where rt.deal_number=d.deal_no
908 and d.bond_issue=p_bond_issue_code
909 and rt.maturity_date=p_coupon_date
910 and dda.reconciled_reference is not null
911 and rt.deal_type='BOND'
912 and dda.deal_number=rt.deal_number
913 and dda.transaction_number=rt.transaction_number
914 and dda.deal_type='BOND';
915
916 cursor bond_coupon_reconciled_all (p_bond_issue_code VARCHAR2) is
917 select 1
918 from xtr_deals d, xtr_rollover_transactions rt, xtr_deal_date_amounts dda
919 where rt.deal_number=d.deal_no
920 and d.bond_issue=p_bond_issue_code
921 and dda.reconciled_reference is not null
922 and rt.deal_type='BOND'
923 and dda.deal_number=0
924 and dda.deal_type='EXP'
925 and ((rt.tax_settled_reference is not null
926 and dda.transaction_number=rt.tax_settled_reference)
927 or (rt.principal_tax_settled_ref is not null
928 and dda.transaction_number=rt.principal_tax_settled_ref))
929 UNION ALL
930 select 1
931 from xtr_deals d, xtr_rollover_transactions rt, xtr_deal_date_amounts dda
932 where rt.deal_number=d.deal_no
933 and d.bond_issue=p_bond_issue_code
934 and dda.reconciled_reference is not null
935 and rt.deal_type='BOND'
936 and dda.deal_number=rt.deal_number
937 and dda.transaction_number=rt.transaction_number
938 and dda.deal_type='BOND';
939
940 v_dummy NUMBER;
941 BEGIN
942 p_out_rec.yes := FALSE;
943 if p_in_rec.deal_type='BOND' then
944 if p_in_rec.bond_coupon_date is not null then
945 open bond_coupon_reconciled(p_in_rec.bond_issue_code,
946 p_in_rec.bond_coupon_date);
947 fetch bond_coupon_reconciled into v_dummy;
948 p_out_rec.yes := bond_coupon_reconciled%FOUND;
949 close bond_coupon_reconciled;
950 else
951 open bond_coupon_reconciled_all(p_in_rec.bond_issue_code);
952 fetch bond_coupon_reconciled_all into v_dummy;
953 p_out_rec.yes := bond_coupon_reconciled_all%FOUND;
954 close bond_coupon_reconciled_all;
955 end if;
956 end if;
957 END reconciled_validation;
958
959
960
961 --Bug 2804548
962 --Returning boolean to check whether any components of the CURRENT coupon
963 --has been accrued or not.
964 PROCEDURE accrued_validation(p_in_rec IN validation_in_rec,
965 p_out_rec IN OUT NOCOPY validation_out_rec) IS
966 cursor bond_coupon_accrued (p_bond_issue_code VARCHAR2,
967 p_coupon_date DATE) is
968 select 1
969 from xtr_rollover_transactions rt, xtr_accrls_amort a, xtr_deals d
970 where rt.deal_number=a.deal_no
971 and rt.transaction_number=a.trans_no
972 and d.deal_no=rt.deal_number
973 and d.bond_issue=p_bond_issue_code
974 and rt.maturity_date=p_coupon_date;
975
976 cursor bond_coupon_accrued_all (p_bond_issue_code VARCHAR2) is
977 select 1
978 from xtr_accrls_amort a, xtr_deals d
979 where amount_type='CPMADJ'
980 and d.deal_no=a.deal_no
981 and d.bond_issue=p_bond_issue_code;
982
983 v_dummy NUMBER;
984 BEGIN
985 p_out_rec.yes := FALSE;
986 if p_in_rec.deal_type='BOND' then
987 if p_in_rec.bond_coupon_date is not null then
988 open bond_coupon_accrued(p_in_rec.bond_issue_code,
989 p_in_rec.bond_coupon_date);
990 fetch bond_coupon_accrued into v_dummy;
991 p_out_rec.yes := bond_coupon_accrued%FOUND;
992 close bond_coupon_accrued;
993 else
994 open bond_coupon_accrued_all(p_in_rec.bond_issue_code);
995 fetch bond_coupon_accrued_all into v_dummy;
996 p_out_rec.yes := bond_coupon_accrued_all%FOUND;
997 close bond_coupon_accrued_all;
998 end if;
999 end if;
1000 END accrued_validation;
1001
1002 ----------------------------------------------------------------------------------------------------------------
1003 end XTR_FPS3_P;
[Home] [Help]