[Home] [Help]
PACKAGE BODY: APPS.XTR_COF_P
Source
1 PACKAGE BODY XTR_COF_P as
2 /* $Header: xtrcostb.pls 120.12 2005/10/24 10:47:31 eaggarwa ship $ */
3 ----------------------------------------------------------------------------------------------------------------
4 PROCEDURE SET_CURR_IG_DEAL_DETAILS(
5 P_DEAL_NUMBER IN XTR_INTERGROUP_TRANSFERS.DEAL_NUMBER%TYPE,
6 P_TRANSACTION_NUMBER IN XTR_INTERGROUP_TRANSFERS.TRANSACTION_NUMBER%TYPE,
7 P_MATURITY_DATE IN DATE) IS
8 BEGIN
9 g_ig_curr_deal_number := p_deal_number;
10 g_ig_curr_transaction_number := p_transaction_number;
11 g_ig_curr_maturity_date := p_maturity_date;
12 END SET_CURR_IG_DEAL_DETAILS;
13
14 PROCEDURE GET_CURR_IG_DEAL_DETAILS(
15 P_DEAL_NUMBER IN XTR_INTERGROUP_TRANSFERS.DEAL_NUMBER%TYPE,
16 P_TRANSACTION_NUMBER IN XTR_INTERGROUP_TRANSFERS.TRANSACTION_NUMBER%TYPE,
17 P_MATURITY_DATE OUT NOCOPY DATE) IS
18 BEGIN
19 if (p_deal_number = g_ig_curr_deal_number and
20 p_transaction_number = g_ig_curr_transaction_number) then
21 p_maturity_date := g_ig_curr_maturity_date;
22 g_ig_curr_maturity_date := null;
23 g_ig_curr_deal_number := null;
24 g_ig_curr_transaction_number := null;
25 end if;
26 END GET_CURR_IG_DEAL_DETAILS;
27
28 PROCEDURE MAINTAIN_POSITION_HISTORY(
29 P_START_DATE IN DATE,
30 P_MATURITY_DATE IN DATE,
31 P_OTHER_DATE IN DATE,
32 P_DEAL_NUMBER IN NUMBER,
33 P_TRANSACTION_NUMBER IN NUMBER,
34 P_COMPANY_CODE IN VARCHAR2,
35 P_CURRENCY IN VARCHAR2,
36 P_DEAL_TYPE IN VARCHAR2,
37 P_DEAL_SUBTYPE IN VARCHAR2,
38 P_PRODUCT_TYPE IN VARCHAR2,
39 P_PORTFOLIO_CODE IN VARCHAR2,
40 P_CPARTY_CODE IN VARCHAR2,
41 P_CONTRA_CCY IN VARCHAR2,
42 P_CURRENCY_COMBINATION IN VARCHAR2,
43 P_ACCOUNT_NO IN VARCHAR2,
44 P_TRANSACTION_RATE IN NUMBER,
45 P_YEAR_CALC_TYPE IN VARCHAR2,
46 P_BASE_REF_AMOUNT IN NUMBER,
47 P_BASE_RATE IN NUMBER,
48 P_STATUS_CODE IN VARCHAR2,
49 P_INTEREST IN NUMBER,
50 P_MATURITY_AMOUNT IN NUMBER,
51 P_START_AMOUNT IN NUMBER,
52 P_CALC_BASIS IN VARCHAR2,
53 P_CALC_TYPE IN VARCHAR2,
54 P_ACTION IN VARCHAR2,
55 P_DAY_COUNT_TYPE IN VARCHAR2,
56 P_FIRST_TRANS_FLAG IN VARCHAR2
57 ) as
58 ---
59 cursor HCE is
60 select s.HCE_RATE,s.ROUNDING_FACTOR
61 from XTR_MASTER_CURRENCIES_V s
62 where s.CURRENCY = P_CURRENCY;
63
64 L_HCE_BASE_REF_AMOUNT NUMBER;
65 L_REF_DATE DATE;
66 L_END_DATE DATE;
67 L_SYS_DATE DATE :=trunc(sysdate);
68 L_AS_AT_DATE DATE;
69 L_ROWID VARCHAR2(30);
70 L_AMOUNT NUMBER;
71 L_HCE_RATE NUMBER;
72 L_FAC NUMBER;
73 L_BASE_REF_AMOUNT NUMBER;
74 L_YEAR_BASIS NUMBER;
75 L_NO_OF_DAYS NUMBER;
76 L_NO_OF_DAYS_IN NUMBER;
77 L_NO_OF_DAYS_OUT NUMBER;
78 L_YEAR_BASIS_IN NUMBER;
79 L_YEAR_BASIS_OUT NUMBER;
80 L_YIELD_RATE NUMBER;
81 L_CALC_BASIS XTR_DEALS.calc_basis%TYPE;
82 L_CALC_TYPE XTR_BOND_ISSUES.calc_type%TYPE;
83 L_COUPON_RATE NUMBER;
84 L_MATURITY_AMT NUMBER;
85 L_CONSIDERATION NUMBER;
86 L_CONVERT_RATE NUMBER;
87 L_CONVERT_TYPE CONSTANT VARCHAR2(15) := 'ACTUAL365';
88 L_TOTAL_INT NUMBER;
89 L_DAILY_INT NUMBER;
90 L_HCE_INT NUMBER;
91 L_NEXT_YEAR DATE;
92
93 L_DEAL_SUBTYPE XTR_POSITION_HISTORY.deal_subtype%TYPE;
94 L_TRANSACTION_RATE NUMBER;
95 T_AS_AT_DATE DBMS_SQL.DATE_TABLE;
96 T_AS_AT_DATE_INS DBMS_SQL.DATE_TABLE;
97 T_ROWID DBMS_SQL.VARCHAR2_TABLE;
98 N_COUNTER NUMBER;
99 N_AS_AT_DATE_CP NUMBER;
100 N_AS_AT_DATE_INS_CP NUMBER;
101
102 /*********************************/
103 /* For DEAL_TYPE in('NI','ONC') */
104 /*********************************/
105 cursor CHK_LOCK_ROWS_ONC(V_START_DATE DATE,
106 V_END_DATE DATE,
107 V_DEAL_TYPE VARCHAR2,
108 V_DEAL_NUMBER NUMBER,
109 V_TRANSACTION_NUMBER NUMBER,
110 V_COMPANY_CODE VARCHAR2) is
111 select rowid, as_at_date
112 from XTR_POSITION_HISTORY
113 where AS_AT_DATE >= V_START_DATE
114 and AS_AT_DATE < V_END_DATE
115 and DEAL_TYPE = V_DEAL_TYPE
116 and DEAL_NUMBER = V_DEAL_NUMBER
117 and TRANSACTION_NUMBER = V_TRANSACTION_NUMBER
118 order by AS_AT_DATE
119 for update of BASE_REF_AMOUNT NOWAIT;
120
121 /***********************************************/
122 /* For DEAL_TYPE in('TMM','RTMM', 'BOND', 'FX')*/
123 /***********************************************/
127 V_DEAL_NUMBER NUMBER,
124 cursor CHK_LOCK_ROWS_TMM(V_START_DATE DATE,
125 V_END_DATE DATE,
126 V_DEAL_TYPE VARCHAR2,
128 V_COMPANY_CODE VARCHAR2) is
129 select rowid, as_at_date
130 from XTR_POSITION_HISTORY
131 where AS_AT_DATE >= V_START_DATE
132 and AS_AT_DATE < V_END_DATE
133 and DEAL_TYPE = V_DEAL_TYPE
134 and DEAL_NUMBER = V_DEAL_NUMBER
135 order by AS_AT_DATE
136 for update of BASE_REF_AMOUNT NOWAIT;
137
138 /*************************/
139 /* For deal Type CA */
140 /*************************/
141 cursor CHK_LOCK_ROWS_CA(V_START_DATE DATE,
142 V_END_DATE DATE,
143 V_DEAL_TYPE VARCHAR2,
144 V_COMPANY_CODE VARCHAR2,
145 V_ACCOUNT_NO VARCHAR2) is
146 select rowid, as_at_date
147 from XTR_POSITION_HISTORY
148 where AS_AT_DATE >= V_START_DATE
149 and AS_AT_DATE < V_END_DATE
150 and DEAL_TYPE = V_DEAL_TYPE
151 and COMPANY_CODE = V_COMPANY_CODE
152 and ACCOUNT_NO = V_ACCOUNT_NO
153 order by AS_AT_DATE
154 for update of BASE_REF_AMOUNT NOWAIT;
155
156 /*************************/
157 /* For deal Type IG */
158 /*************************/
159 cursor CHK_LOCK_ROWS_IG(V_START_DATE DATE,
160 V_END_DATE DATE,
161 V_DEAL_TYPE VARCHAR2,
162 V_DEAL_NUMBER NUMBER) is
163 select rowid, as_at_date
164 from XTR_POSITION_HISTORY
165 where AS_AT_DATE >= V_START_DATE
166 and AS_AT_DATE < V_END_DATE
167 and DEAL_TYPE = V_DEAL_TYPE
168 and DEAL_NUMBER = V_DEAl_NUMBER
169 order by AS_AT_DATE
170 for update of BASE_REF_AMOUNT NOWAIT;
171
172
173 /*********************************************/
174 /* For deal manager performance issue for IG */
175 /*********************************************/
176 /* This is a pure preformance hack for the IG deal manager
177 The idea is that the correspondence between an IG deal in XTR_POSITION_HISTORY and XTR_COST_OF_FUNDS
178 is one to one. Therefore it is much more effecient to update XTR_COST_OF_FUNDS at the same time
179 we update XTR_POSITION_HISTORY. Block update. However to do this it was necessicary to duplicate
180 some of the logic from maintain_cof procedure. This procedure is for IG deals and only IG deals.
181 In order to use this hack, the developer must adhere to the following requirements and must not
182 deviate or modify them. Be careful when making modifications to any of the affected code areas so
183 as not to violate these assumptions.
184
185 First, INSERT and UPDATE for IG deals are executed ONLY by the maintain position history procedure.
186 The maintain COF procedure will do no processing for IG deals for INSERT and UPDATE but will continue
187 to be responsible for IG DELETEs. Call MAINTAIN_COF_IG for IG deals and IG deals only. Position
188 History is now responsible for IG data in COF so it must not forget to call this function for INSERT
189 and UPDATE. For anyone writing SQL upgrade scripts, if data is inserted or updated in XTR_POSITION_HISTORY
190 for IG deals then make sure the snapshot of COF is updated appropriately.
191 */
192
193 procedure maintain_cof_ig is
194
195 l_fac number;
196 l_weighted_avg_prin number;
197 l_base_weighted_avg_prin number;
198
199 N_COF_COUNTER NUMBER;
200 N_COF_AS_AT_DATE_CP NUMBER;
201 N_COF_AS_AT_DATE_INS_CP NUMBER;
202 T_COF_AS_AT_DATE DBMS_SQL.DATE_TABLE;
203 T_COF_AS_AT_DATE_INS DBMS_SQL.DATE_TABLE;
204 T_COF_ROWID DBMS_SQL.VARCHAR2_TABLE;
205
206 cursor get_rounding_factor(l_in_currency in varchar2) is
207 select hce_rate,rounding_factor
208 from XTR_MASTER_CURRENCIES_V
209 where currency=l_in_currency;
210
211 cursor CHK_LOCK_ROWS_COF_IG(V_START_DATE DATE,
212 V_END_DATE DATE,
213 V_COMPANY_CODE VARCHAR2,
214 V_CPARTY_CODE VARCHAR2,
215 V_DEAL_TYPE VARCHAR2,
216 V_CURRENCY VARCHAR2) is
217 select ROWID,AS_AT_DATE
218 from XTR_COST_OF_FUNDS
219 where AS_AT_DATE >= V_START_DATE
220 and AS_AT_DATE < V_END_DATE
221 and DEAL_TYPE = V_DEAL_TYPE
222 --and DEAL_SUBTYPE = V_DEAL_SUBTYPE /* IG DEAL HAS ONE AND ONLY ONE ENTRY. UPDATE RESPECTIVELY */
223 and COMPANY_CODE = V_COMPANY_CODE
224 and CURRENCY = V_CURRENCY
225 --and nvl(CURRENCY_COMBINATION,'%')=nvl(V_CURRENCY_COMBINATION,'%') /* IG, NOT FX */
226 --and nvl(PRODUCT_TYPE,'%') = nvl(V_PRODUCT_TYPE,'%') /* SAME AS DEAL_SUBTYPE */
227 --and nvl(PORTFOLIO_CODE,'%') = nvl(V_PORTFOLIO_CODE,'%') /* SAME AS DEAL_SUBTYPE */
228 and nvl(PARTY_CODE,'%') = nvl(V_CPARTY_CODE,'%')
229 for update of GROSS_PRINCIPAL NOWAIT;
230
231 begin
232
233 open get_rounding_factor(p_currency);
234 fetch get_rounding_factor into l_hce_rate,l_fac;
235 close get_rounding_factor;
236 l_fac :=nvl(l_fac,2);
237
238 l_weighted_avg_prin := round(nvl(L_BASE_REF_AMOUNT,0)*nvl(L_CONVERT_RATE,0)/100,l_fac);
239 l_base_weighted_avg_prin := round(nvl(L_BASE_REF_AMOUNT,0)*nvl(P_BASE_RATE,0)/100,l_fac);
240
241 open CHK_LOCK_ROWS_COF_IG(L_REF_DATE,L_END_DATE,P_COMPANY_CODE,P_CPARTY_CODE,P_DEAL_TYPE,P_CURRENCY);
242 fetch CHK_LOCK_ROWS_COF_IG bulk collect into T_COF_ROWID,T_COF_AS_AT_DATE;
243 close chk_LOCK_ROWS_COF_IG;
244
248
245 N_COF_COUNTER := T_COF_AS_AT_DATE.COUNT;
246 N_COF_AS_AT_DATE_CP := 1;
247 N_COF_AS_AT_DATE_INS_CP := 1;
249 /* The following loop determines which dates are not already in the table */
250 /* all dates not in the table are saved in T_AS_AT_DATE_INS to be inserted */
251 FOR I IN 1..(L_END_DATE-L_REF_DATE) LOOP
252 IF N_COF_AS_AT_DATE_CP <= N_COF_COUNTER AND T_COF_AS_AT_DATE(N_COF_AS_AT_DATE_CP)=L_REF_DATE+I-1 THEN
253 N_COF_AS_AT_DATE_CP:=N_COF_AS_AT_DATE_CP+1;
254 ELSE
255 T_COF_AS_AT_DATE_INS(N_COF_AS_AT_DATE_INS_CP):=L_REF_DATE+I-1;
256 N_COF_AS_AT_DATE_INS_CP:=N_COF_AS_AT_DATE_INS_CP+1;
257 END IF;
258 END LOOP;
259
260 FORALL i in 1..T_COF_AS_AT_DATE.COUNT
261 update XTR_COST_OF_FUNDS
262 set GROSS_PRINCIPAL = L_BASE_REF_AMOUNT,
263 HCE_GROSS_PRINCIPAL = L_HCE_BASE_REF_AMOUNT,
264 WEIGHTED_AVG_PRINCIPAL = L_WEIGHTED_AVG_PRIN,
265 AVG_INTEREST_RATE = L_CONVERT_RATE,
266 BASE_WEIGHTED_AVG_PRINCIPAL = L_BASE_WEIGHTED_AVG_PRIN,
267 AVG_BASE_RATE = P_BASE_RATE,
268 INTEREST = L_DAILY_INT,
269 HCE_INTEREST = L_HCE_INT,
270 DEAL_SUBTYPE = L_DEAL_SUBTYPE,
271 PRODUCT_TYPE = P_PRODUCT_TYPE,
272 PORTFOLIO_CODE = P_PORTFOLIO_CODE
273 where rowid=T_COF_ROWID(i);
274
275 FORALL i in 1..T_COF_AS_AT_DATE_INS.COUNT
276 insert into XTR_COST_OF_FUNDS
277 (as_at_date,company_code,deal_type,
278 deal_subtype,party_code,portfolio_code,product_type,
279 currency,currency_combination,contra_ccy,
280 account_no,created_on,
281 gross_principal,hce_gross_principal,
282 weighted_avg_principal,avg_interest_rate,interest,hce_interest,
283 base_weighted_avg_principal,avg_base_rate, gross_base_amount,
284 gross_contra_trans_amount, gross_contra_spot_amount)
285 values(
286 T_COF_AS_AT_DATE_INS(i),
287 P_COMPANY_CODE,
288 P_DEAL_TYPE,
289 L_DEAL_SUBTYPE,
290 P_CPARTY_CODE,
291 P_PORTFOLIO_CODE,
292 P_PRODUCT_TYPE,
293 P_CURRENCY,
294 NULL,
295 P_CONTRA_CCY,
296 P_ACCOUNT_NO,
297 sysdate,
298 L_BASE_REF_AMOUNT,
299 L_HCE_BASE_REF_AMOUNT,
300 L_WEIGHTED_AVG_PRIN,
301 L_CONVERT_RATE, --avg_int_rate
302 L_DAILY_INT, -- interest
303 L_HCE_INT, -- hce_interest
304 L_BASE_WEIGHTED_AVG_PRIN,
305 P_BASE_RATE, -- avg_base_rate
306 NULL, -- gross_base_amount
307 NULL, --gross_contra_trans
308 NULL --gross_contra_spot
309 );
310
311
312
313 end;
314
315
316 begin
317 open HCE;
318 fetch HCE into L_HCE_RATE,L_FAC;
319 close HCE;
320
321 /***********************/
322 /* Common Calculations */
323 /***********************/
324 L_REF_DATE :=nvl(P_START_DATE,L_SYS_DATE);
325 L_END_DATE :=least(nvl(P_MATURITY_DATE,L_SYS_DATE),L_SYS_DATE);
326 if p_deal_type = 'IG' then
327 get_curr_ig_deal_details(P_DEAL_NUMBER,P_TRANSACTION_NUMBER,L_END_DATE);
328 L_END_DATE := least(nvl(L_END_DATE,L_SYS_DATE),nvl(P_MATURITY_DATE,L_SYS_DATE),L_SYS_DATE);
329 end if;
330
331
332 L_HCE_BASE_REF_AMOUNT :=round(P_BASE_REF_AMOUNT/L_HCE_RATE,L_FAC);
333 L_BASE_REF_AMOUNT :=P_BASE_REF_AMOUNT;
334
335 /*====================================================*/
336 /* Calculate the following columns' values: */
337 /* (1) Daily Interst */
338 /* (2) HCE_INTEREST */
339 /* (3) transaction rate in terms of Actual/365 basis */
340 /*====================================================*/
341 If P_DEAL_TYPE = 'NI' then
342 /** Need to convert to yield rate first if it's Discount basis **/
343 if P_CALC_BASIS = 'DISCOUNT' THEN
344 -- Added the parameters for Interest Override feature
345 XTR_CALC_P.calc_days_run(p_start_date,
346 p_maturity_date,
347 p_year_calc_type,
348 l_no_of_days,
349 l_year_basis,
350 NULL,
351 p_day_count_type,
352 p_first_trans_flag
353 );
354
355 XTR_RATE_CONVERSION.Discount_To_Yield_Rate(P_TRANSACTION_RATE, L_NO_OF_DAYS,
356 L_YEAR_BASIS, L_YIELD_RATE);
357 else
358 L_YIELD_RATE := P_TRANSACTION_RATE;
359 end if;
360 Else
361 L_YIELD_RATE := P_TRANSACTION_RATE;
362 End if;
363
364 /*-----------------------------*/
365 /* NI, TMM, RTMM, ONC mature */
366 /*-----------------------------*/
367 -- Changed for Interest Override feature
368 If P_DEAL_TYPE in ('NI', 'TMM', 'RTMM') or
369 (P_DEAL_TYPE = 'ONC' and P_MATURITY_DATE is not null) then
370 If ((p_maturity_date - p_start_date) <> 0)
371 -- Added for Interest Override
372 AND( p_day_count_type <> 'B'
373 OR (p_day_count_type ='B' AND p_first_trans_flag <>'Y'))
374 --
375 THEN
376 L_DAILY_INT := P_INTEREST / (p_maturity_date - p_start_date);
377 L_HCE_INT := L_DAILY_INT / L_HCE_RATE;
381 L_HCE_INT := L_DAILY_INT / L_HCE_RATE;
378 -- Added for Intreest Override
379 ELSIF p_day_count_type ='B' AND p_first_trans_flag='Y' then
380 L_DAILY_INT := P_INTEREST / (p_maturity_date - p_start_date +1);
382 --
383 ELSE
384 L_DAILY_INT := 0;
385 L_HCE_INT := 0;
386 END IF;
387
388 -- Added paramters for Interest Override feature
389 XTR_CALC_P.calc_days_run(p_start_date,
390 p_maturity_date,
391 p_year_calc_type,
392 l_no_of_days_in,
393 l_year_basis_in,
394 NULL,
395 p_day_count_type,
396 p_first_trans_flag);
397
398 XTR_CALC_P.calc_days_run(p_start_date,
399 p_maturity_date,
400 l_convert_type,
401 l_no_of_days_out,
402 l_year_basis_out,
403 NULL,
404 p_day_count_type,
405 p_first_trans_flag);
406
407 XTR_RATE_CONVERSION.day_count_basis_conv(l_no_of_days_in,
408 l_no_of_days_out,
409 l_year_basis_in,
410 l_year_basis_out,
411 l_yield_rate,
412 L_CONVERT_RATE);
413 /*--------------------------*/
414 /* CA, IG, ONC not mature */
415 /*--------------------------*/
416 Elsif P_DEAL_TYPE in ('CA', 'IG') or
417 (P_DEAL_TYPE = 'ONC' and P_MATURITY_DATE is null) then
418 L_NEXT_YEAR := add_months(p_start_date, 12);
419
420 XTR_CALC_P.calc_days_run(p_start_date,
421 l_next_year,
422 p_year_calc_type,
423 l_no_of_days_in,
424 l_year_basis_in,
425 NULL,
426 p_day_count_type,
427 p_first_trans_flag
428 );
429
430 XTR_RATE_CONVERSION.day_count_basis_conv(1,
431 1,
432 l_year_basis_in,
433 365,
434 l_yield_rate,
435 L_CONVERT_RATE);
436 L_DAILY_INT := P_TRANSACTION_RATE * P_BASE_REF_AMOUNT / (100 * l_year_basis_in);
437 L_HCE_INT := L_DAILY_INT / L_HCE_RATE;
438
439 -- Added for Interest Override
440 IF P_DEAL_TYPE in ('CA', 'IG') AND P_INTEREST IS NOT NULL THEN
441 L_DAILY_INT := L_DAILY_INT + P_INTEREST;
442 L_HCE_INT := L_DAILY_INT / L_HCE_RATE;
443 END IF;
444 --
445
446 /*--------*/
447 /* BOND */
448 /*--------*/
449 Elsif P_DEAL_TYPE = 'BOND' THEN
450 -- Added paramters for Interest Override feature
451 XTR_COF_P.Calculate_Bond_rate(P_DEAL_NUMBER,
452 P_MATURITY_AMOUNT,
453 P_START_AMOUNT,
454 P_TRANSACTION_RATE, -- jhung make sure it's coupon rate
455 P_START_DATE,
456 P_MATURITY_DATE,
457 P_CALC_TYPE,
458 L_DAILY_INT,
459 L_CONVERT_RATE,
460 P_DAY_COUNT_TYPE -- Added for Interest Overide
461 );
462 L_HCE_INT := L_DAILY_INT / L_HCE_RATE;
463 End if;
464
465
466 /**************/
467 /* INSERT */
468 /**************/
469 if P_ACTION='INSERT' and P_STATUS_CODE='CURRENT' then
470
471 L_DEAL_SUBTYPE := P_DEAL_SUBTYPE;
472 L_BASE_REF_AMOUNT := P_BASE_REF_AMOUNT;
473
474 if (P_DEAL_TYPE in ('CA','IG')) then
475 if (nvl(P_BASE_REF_AMOUNT,0)<0) then
476 L_DEAL_SUBTYPE := 'FUND';
477 else
478 L_DEAL_SUBTYPE := 'INVEST';
479 end if;
480 elsif (P_DEAL_TYPE in ('FX')) then
481 L_CONVERT_RATE := P_TRANSACTION_RATE;
482 L_DAILY_INT := 0;
483 L_HCE_INT := 0;
484 end if;
485
486 FOR i in 1..(L_END_DATE-L_REF_DATE) LOOP
487 T_AS_AT_DATE(i) := L_REF_DATE+i-1;
488 END LOOP;
489
490 if (P_DEAL_TYPE = 'IG') then
491 maintain_cof_ig;
492 end if;
493
494 forall i in 1..T_AS_AT_DATE.COUNT
495 insert into XTR_POSITION_HISTORY(
496 AS_AT_DATE,
497 DEAL_TYPE,
498 DEAL_NUMBER,
499 TRANSACTION_NUMBER,
500 COMPANY_CODE,
501 CPARTY_CODE,
502 DEAL_SUBTYPE,
503 PRODUCT_TYPE,
504 PORTFOLIO_CODE,
505 CURRENCY,
506 CONTRA_CCY,
507 CURRENCY_COMBINATION,
508 YEAR_CALC_TYPE,
509 ACCOUNT_NO,
510 BASE_REF_AMOUNT,
511 HCE_BASE_REF_AMOUNT,
512 TRANSACTION_RATE,
513 BASE_RATE,
514 INTEREST,
515 HCE_INTEREST)
516 values(
517 T_AS_AT_DATE(i),
518 P_DEAL_TYPE,
519 P_DEAL_NUMBER,
520 P_TRANSACTION_NUMBER,
521 P_COMPANY_CODE,
522 P_CPARTY_CODE,
523 L_DEAL_SUBTYPE,
524 P_PRODUCT_TYPE,
525 P_PORTFOLIO_CODE,
529 P_YEAR_CALC_TYPE,
526 P_CURRENCY,
527 P_CONTRA_CCY,
528 P_CURRENCY_COMBINATION,
530 P_ACCOUNT_NO,
531 L_BASE_REF_AMOUNT,
532 L_HCE_BASE_REF_AMOUNT,
533 L_CONVERT_RATE,
534 P_BASE_RATE,
535 L_DAILY_INT,
536 L_HCE_INT);
537
538
539
540 /*
541 WHILE L_AS_AT_DATE < L_END_DATE LOOP
542 -- insert new row
543 insert into XTR_POSITION_HISTORY(
544 AS_AT_DATE,
545 DEAL_TYPE,
546 DEAL_NUMBER,
547 TRANSACTION_NUMBER,
548 COMPANY_CODE,
549 CPARTY_CODE,
550 DEAL_SUBTYPE,
551 PRODUCT_TYPE,
552 PORTFOLIO_CODE,
553 CURRENCY,
554 CONTRA_CCY,
555 CURRENCY_COMBINATION,
556 YEAR_CALC_TYPE,
557 ACCOUNT_NO,
558 BASE_REF_AMOUNT,
559 HCE_BASE_REF_AMOUNT,
560 TRANSACTION_RATE,
561 BASE_RATE,
562 INTEREST,
563 HCE_INTEREST)
564 values(
565 L_AS_AT_DATE,
566 P_DEAL_TYPE,
567 P_DEAL_NUMBER,
568 P_TRANSACTION_NUMBER,
569 P_COMPANY_CODE,
570 P_CPARTY_CODE,
571 decode(P_DEAL_TYPE,
572 'CA',decode(sign(nvl(P_BASE_REF_AMOUNT,0)),-1,'FUND','INVEST'),
573 'IG',decode(sign(nvl(P_BASE_REF_AMOUNT,0)),-1, 'FUND','INVEST'),
574 P_DEAL_SUBTYPE), -- bug 2345708
575 P_PRODUCT_TYPE,
576 P_PORTFOLIO_CODE,
577 P_CURRENCY,
578 P_CONTRA_CCY,
579 P_CURRENCY_COMBINATION,
580 P_YEAR_CALC_TYPE,
581 P_ACCOUNT_NO,
582 P_BASE_REF_AMOUNT,
583 L_HCE_BASE_REF_AMOUNT,
584 decode(P_DEAL_TYPE, 'FX', P_TRANSACTION_RATE, L_CONVERT_RATE),
585 P_BASE_RATE,
586 decode(P_DEAL_TYPE, 'FX', 0, L_DAILY_INT),
587 decode(P_DEAL_TYPE, 'FX', 0, L_HCE_INT));
588 L_AS_AT_DATE :=L_AS_AT_DATE +1;
589 end loop;
590 */
591
592
593 /**************/
594 /* DELETE */
595 /**************/
596 elsif P_ACTION = 'DELETE' then
597 if P_DEAL_TYPE='CA' then
598 delete from XTR_POSITION_HISTORY
599 where DEAL_TYPE='CA'
600 and AS_AT_DATE >= P_START_DATE
601 and ACCOUNT_NO = P_ACCOUNT_NO
602 and COMPANY_CODE = P_COMPANY_CODE;
603 elsif P_DEAL_TYPE='IG' then
604 delete from XTR_POSITION_HISTORY
605 where DEAL_TYPE='IG'
606 and AS_AT_DATE >= P_START_DATE
607 and DEAL_NUMBER = P_DEAL_NUMBER;
608 elsif P_DEAL_TYPE ='ONC' then
609 if P_STATUS_CODE = 'CLOSED' THEN
610 L_END_DATE :=least(nvl(P_MATURITY_DATE,L_SYS_DATE),L_SYS_DATE);
611 delete from XTR_POSITION_HISTORY
612 where AS_AT_DATE >= L_END_DATE
613 and DEAL_TYPE = P_DEAL_TYPE
614 and DEAL_NUMBER = P_DEAL_NUMBER
615 and TRANSACTION_NUMBER = P_TRANSACTION_NUMBER;
616 -- Added for Interest Override
617 L_REF_DATE :=P_START_DATE;
618 L_AS_AT_DATE :=L_REF_DATE;
619
620 open CHK_LOCK_ROWS_ONC(L_REF_DATE,L_AS_AT_DATE,P_DEAL_TYPE,P_DEAL_NUMBER,
621 P_TRANSACTION_NUMBER,P_COMPANY_CODE);
622 fetch CHK_LOCK_ROWS_ONC BULK COLLECT into T_ROWID, T_AS_AT_DATE;
623 close CHK_LOCK_ROWS_ONC;
624
625 FORALL I in 1..T_ROWID.COUNT
626 update XTR_POSITION_HISTORY
627 set COMPANY_CODE = P_COMPANY_CODE,
628 CPARTY_CODE = P_CPARTY_CODE,
629 DEAL_SUBTYPE = p_deal_subtype,
630 PRODUCT_TYPE = P_PRODUCT_TYPE,
631 PORTFOLIO_CODE = P_PORTFOLIO_CODE,
632 CURRENCY = P_CURRENCY,
633 CURRENCY_COMBINATION = P_CURRENCY_COMBINATION,
634 YEAR_CALC_TYPE = P_YEAR_CALC_TYPE,
635 ACCOUNT_NO = P_ACCOUNT_NO,
636 BASE_REF_AMOUNT = p_base_ref_amount,
637 HCE_BASE_REF_AMOUNT = l_hce_base_ref_amount,
638 BASE_RATE = p_base_rate,
639 TRANSACTION_RATE = l_convert_rate,
640 INTEREST = l_daily_int,
641 HCE_INTEREST = l_hce_int
642 where rowid=T_ROWID(I);
643 --
644 else
645 delete from XTR_POSITION_HISTORY
646 where DEAL_NUMBER = P_DEAL_NUMBER
647 and DEAL_TYPE = P_DEAL_TYPE
648 and TRANSACTION_NUMBER = P_TRANSACTION_NUMBER;
649 end if;
650
651 elsif P_DEAL_TYPE in('TMM','RTMM') then
652 delete from XTR_POSITION_HISTORY
653 where DEAL_NUMBER = P_DEAL_NUMBER
654 and DEAL_TYPE = P_DEAL_TYPE
655 and TRANSACTION_NUMBER = P_TRANSACTION_NUMBER;
656
657 elsif P_DEAL_TYPE ='FX' then
658 if P_STATUS_CODE = 'CLOSED' then
659 L_END_DATE :=least(nvl(P_MATURITY_DATE,L_SYS_DATE),L_SYS_DATE);
660 delete from XTR_POSITION_HISTORY
661 where AS_AT_DATE >= L_END_DATE
662 and DEAL_NUMBER = P_DEAL_NUMBER
663 and DEAL_TYPE = P_DEAL_TYPE
664 and TRANSACTION_NUMBER = P_TRANSACTION_NUMBER;
665 else
666 delete from XTR_POSITION_HISTORY
667 where DEAL_NUMBER = P_DEAL_NUMBER
668 and DEAL_TYPE = P_DEAL_TYPE
672 if P_STATUS_CODE = 'CLOSED' then
669 and TRANSACTION_NUMBER = P_TRANSACTION_NUMBER;
670 end if;
671 elsif P_DEAL_TYPE ='NI' then
673 L_END_DATE :=least(nvl(P_MATURITY_DATE,L_SYS_DATE),L_SYS_DATE);
674 delete from XTR_POSITION_HISTORY
675 where AS_AT_DATE >= L_END_DATE
676 and DEAL_NUMBER = P_DEAL_NUMBER
677 and DEAL_TYPE = P_DEAL_TYPE
678 and TRANSACTION_NUMBER = P_TRANSACTION_NUMBER;
679 else
680 delete from XTR_POSITION_HISTORY
681 where DEAL_TYPE = P_DEAL_TYPE
682 and DEAL_NUMBER = P_DEAL_NUMBER;
683 end if;
684 elsif P_DEAL_TYPE ='BOND' then
685 if P_STATUS_CODE = 'CLOSED' then
686 L_END_DATE :=least(nvl(P_MATURITY_DATE,L_SYS_DATE),L_SYS_DATE);
687 delete from XTR_POSITION_HISTORY
688 where AS_AT_DATE >= L_END_DATE
689 and DEAL_TYPE = P_DEAL_TYPE
690 and DEAL_NUMBER = P_DEAL_NUMBER
691 and TRANSACTION_NUMBER = P_TRANSACTION_NUMBER;
692 else
693 delete from XTR_POSITION_HISTORY
694 where DEAL_NUMBER = P_DEAL_NUMBER
695 and DEAL_TYPE = P_DEAL_TYPE
696 and TRANSACTION_NUMBER = P_TRANSACTION_NUMBER;
697 end if;
698 end if;
699
700 /**************/
701 /* UPDATE */
702 /**************/
703 elsif P_ACTION='UPDATE' then
704
705 if P_DEAL_TYPE NOT in('TMM','RTMM','CA','IG') then
706 open CHK_LOCK_ROWS_ONC(L_REF_DATE,L_END_DATE,P_DEAL_TYPE,P_DEAL_NUMBER,
707 P_TRANSACTION_NUMBER,P_COMPANY_CODE);
708 fetch CHK_LOCK_ROWS_ONC BULK COLLECT into T_ROWID, T_AS_AT_DATE;
709 close CHK_LOCK_ROWS_ONC;
710
711 elsif P_DEAL_TYPE in('TMM','RTMM') then
712 open CHK_LOCK_ROWS_TMM(L_REF_DATE,L_END_DATE,P_DEAL_TYPE,P_DEAL_NUMBER,
713 P_COMPANY_CODE);
714 fetch CHK_LOCK_ROWS_TMM BULK COLLECT into T_ROWID, T_AS_AT_DATE;
715 close CHK_LOCK_ROWS_TMM;
716 elsif P_DEAL_TYPE = 'CA' then
717 open CHK_LOCK_ROWS_CA(L_REF_DATE,L_END_DATE,P_DEAL_TYPE,
718 P_COMPANY_CODE,P_ACCOUNT_NO);
719 fetch CHK_LOCK_ROWS_CA BULK COLLECT into T_ROWID, T_AS_AT_DATE;
720 close CHK_LOCK_ROWS_CA;
721 elsif P_DEAL_TYPE = 'IG' then
722 open CHK_LOCK_ROWS_IG(L_REF_DATE,L_END_DATE,P_DEAL_TYPE,P_DEAL_NUMBER);
723 fetch CHK_LOCK_ROWS_IG BULK COLLECT into T_ROWID, T_AS_AT_DATE;
724 close CHK_LOCK_ROWS_IG;
725 end if;
726
727 N_COUNTER := T_AS_AT_DATE.COUNT;
728 N_AS_AT_DATE_CP := 1;
729 N_AS_AT_DATE_INS_CP := 1;
730
731 /* The following loop determines which dates are not already in the table */
732 /* all dates not in the table are saved in T_AS_AT_DATE_INS to be inserted */
733 FOR I IN 1..(L_END_DATE-L_REF_DATE) LOOP
734 IF N_AS_AT_DATE_CP <= N_COUNTER AND T_AS_AT_DATE(N_AS_AT_DATE_CP)=L_REF_DATE+I-1 THEN
735 N_AS_AT_DATE_CP:=N_AS_AT_DATE_CP+1;
736 ELSE
737 T_AS_AT_DATE_INS(N_AS_AT_DATE_INS_CP):=L_REF_DATE+I-1;
738 N_AS_AT_DATE_INS_CP:=N_AS_AT_DATE_INS_CP+1;
739 END IF;
740 END LOOP;
741
742 L_DEAL_SUBTYPE := P_DEAL_SUBTYPE;
743
744 if P_DEAL_TYPE in('CA','IG') then
745 if (nvl(P_BASE_REF_AMOUNT,0)<0) then
746 L_DEAL_SUBTYPE := 'FUND';
747 else
748 L_DEAL_SUBTYPE := 'INVEST';
749 end if;
750 L_BASE_REF_AMOUNT :=abs(P_BASE_REF_AMOUNT);
751 L_HCE_BASE_REF_AMOUNT :=abs(L_HCE_BASE_REF_AMOUNT);
752 L_DAILY_INT :=abs(L_DAILY_INT);
753 L_HCE_INT :=abs(L_HCE_INT);
754 elsif p_DEAL_TYPE in('FX') then
755 L_DAILY_INT :=0;
756 L_HCE_INT :=0;
757 L_CONVERT_RATE := P_TRANSACTION_RATE;
758 end if;
759
760 if (P_DEAL_TYPE = 'IG') then
761 maintain_cof_ig;
762 end if;
763
764 FORALL I in 1..T_AS_AT_DATE.COUNT
765 update XTR_POSITION_HISTORY
766 set -- COMPANY_CODE removed from update because it cannot change and it was causing an index to be recalculated
767 CPARTY_CODE = P_CPARTY_CODE,
768 DEAL_SUBTYPE = L_DEAL_SUBTYPE,
769 PRODUCT_TYPE = P_PRODUCT_TYPE,
770 PORTFOLIO_CODE = P_PORTFOLIO_CODE,
771 CURRENCY = P_CURRENCY,
772 CURRENCY_COMBINATION = P_CURRENCY_COMBINATION,
773 YEAR_CALC_TYPE = P_YEAR_CALC_TYPE,
774 ACCOUNT_NO = P_ACCOUNT_NO,
775 BASE_REF_AMOUNT = L_BASE_REF_AMOUNT,
776 HCE_BASE_REF_AMOUNT = L_HCE_BASE_REF_AMOUNT,
777 BASE_RATE = P_BASE_RATE,
778 TRANSACTION_RATE = L_CONVERT_RATE,
779 INTEREST = L_DAILY_INT,
780 HCE_INTEREST = L_HCE_INT
781 where rowid=T_ROWID(I);
782
783 FORALL I in 1..T_AS_AT_DATE_INS.COUNT
784 -- insert new row
785 insert into XTR_POSITION_HISTORY(
786 AS_AT_DATE,
787 DEAL_TYPE,
788 DEAL_NUMBER,
789 TRANSACTION_NUMBER,
790 COMPANY_CODE,
791 CPARTY_CODE,
792 DEAL_SUBTYPE,
793 PRODUCT_TYPE,
794 PORTFOLIO_CODE,
795 CURRENCY,
796 CONTRA_CCY,
797 CURRENCY_COMBINATION,
798 YEAR_CALC_TYPE,
802 TRANSACTION_RATE,
799 ACCOUNT_NO,
800 BASE_REF_AMOUNT,
801 HCE_BASE_REF_AMOUNT,
803 BASE_RATE,
804 INTEREST,
805 HCE_INTEREST)
806 values(
807 T_AS_AT_DATE_INS(I),
808 P_DEAL_TYPE,
809 P_DEAL_NUMBER,
810 P_TRANSACTION_NUMBER,
811 P_COMPANY_CODE,
812 P_CPARTY_CODE,
813 L_DEAL_SUBTYPE,
814 P_PRODUCT_TYPE,
815 P_PORTFOLIO_CODE,
816 P_CURRENCY,
817 P_CONTRA_CCY,
818 P_CURRENCY_COMBINATION,
819 P_YEAR_CALC_TYPE,
820 P_ACCOUNT_NO,
821 L_BASE_REF_AMOUNT,
822 L_HCE_BASE_REF_AMOUNT,
823 L_CONVERT_RATE,
824 P_BASE_RATE,
825 L_DAILY_INT,
826 L_HCE_INT);
827
828
829 /*
830 L_REF_DATE :=P_START_DATE;
831 L_END_DATE :=least(nvl(P_MATURITY_DATE,L_SYS_DATE),L_SYS_DATE);
832 L_AS_AT_DATE :=L_REF_DATE;
833
834 WHILE L_AS_AT_DATE < L_END_DATE LOOP
835
836 L_ROWID :=NULL;
837 if P_DEAL_TYPE NOT in('TMM','RTMM','CA','IG') then
838 open CHK_LOCK_ROWS_ONC(L_AS_AT_DATE,P_DEAL_TYPE,P_DEAL_NUMBER,
839 P_TRANSACTION_NUMBER,P_COMPANY_CODE);
840 fetch CHK_LOCK_ROWS_ONC into L_ROWID;
841 close CHK_LOCK_ROWS_ONC;
842
843 elsif P_DEAL_TYPE in('TMM','RTMM') then
844 open CHK_LOCK_ROWS_TMM(L_AS_AT_DATE,P_DEAL_TYPE,P_DEAL_NUMBER,
845 P_COMPANY_CODE);
846 fetch CHK_LOCK_ROWS_TMM into L_ROWID;
847 close CHK_LOCK_ROWS_TMM;
848 elsif P_DEAL_TYPE = 'CA' then
849 open CHK_LOCK_ROWS_CA(L_AS_AT_DATE,P_DEAL_TYPE,
850 P_COMPANY_CODE,P_ACCOUNT_NO);
851 fetch CHK_LOCK_ROWS_CA into L_ROWID;
852 close CHK_LOCK_ROWS_CA;
853 elsif P_DEAL_TYPE = 'IG' then
854 open CHK_LOCK_ROWS_IG(L_AS_AT_DATE,P_DEAL_TYPE,P_DEAL_NUMBER);
855 fetch CHK_LOCK_ROWS_IG into L_ROWID;
856 close CHK_LOCK_ROWS_IG;
857 end if;
858
859 if L_ROWID is not null then
860 update XTR_POSITION_HISTORY
861 set COMPANY_CODE = P_COMPANY_CODE,
862 CPARTY_CODE = P_CPARTY_CODE,
863 DEAL_SUBTYPE = decode(DEAL_TYPE,
864 'CA',decode(sign(nvl(P_BASE_REF_AMOUNT,0)),-1,'FUND','INVEST'),
865 'IG',decode(sign(nvl(P_BASE_REF_AMOUNT,0)),-1, 'FUND','INVEST'),
866 P_DEAL_SUBTYPE),
867 PRODUCT_TYPE = P_PRODUCT_TYPE,
868 PORTFOLIO_CODE = P_PORTFOLIO_CODE,
869 CURRENCY = P_CURRENCY,
870 CURRENCY_COMBINATION = P_CURRENCY_COMBINATION,
871 YEAR_CALC_TYPE = P_YEAR_CALC_TYPE,
872 ACCOUNT_NO = P_ACCOUNT_NO,
873 BASE_REF_AMOUNT = decode(DEAL_TYPE,'CA',abs(P_BASE_REF_AMOUNT),
874 'IG',abs(P_BASE_REF_AMOUNT),
875 P_BASE_REF_AMOUNT),
876 HCE_BASE_REF_AMOUNT =decode(DEAL_TYPE,'CA',abs(L_HCE_BASE_REF_AMOUNT),
877 'IG',abs(L_HCE_BASE_REF_AMOUNT),
878 L_HCE_BASE_REF_AMOUNT),
879 BASE_RATE = P_BASE_RATE,
880 TRANSACTION_RATE = decode(P_DEAL_TYPE, 'FX', P_TRANSACTION_RATE, L_CONVERT_RATE),
881 INTEREST = decode(DEAL_TYPE, 'CA', abs(L_DAILY_INT),
882 'IG', abs(L_DAILY_INT),
883 'FX', 0,
884 L_DAILY_INT),
885 HCE_INTEREST = decode(DEAL_TYPE, 'CA', abs(L_HCE_INT),
886 'IG', abs(L_HCE_INT),
887 'FX', 0,
888 L_HCE_INT)
889 where rowid=l_rowid;
890 else
891 -- insert new row
892 if P_DEAL_TYPE in('CA','IG') then
893 L_BASE_REF_AMOUNT :=abs(P_BASE_REF_AMOUNT);
894 L_HCE_BASE_REF_AMOUNT :=abs(L_HCE_BASE_REF_AMOUNT);
895 end if;
896 insert into XTR_POSITION_HISTORY(
897 AS_AT_DATE,
898 DEAL_TYPE,
899 DEAL_NUMBER,
900 TRANSACTION_NUMBER,
901 COMPANY_CODE,
902 CPARTY_CODE,
903 DEAL_SUBTYPE,
904 PRODUCT_TYPE,
905 PORTFOLIO_CODE,
906 CURRENCY,
907 CONTRA_CCY,
908 CURRENCY_COMBINATION,
909 YEAR_CALC_TYPE,
910 ACCOUNT_NO,
911 BASE_REF_AMOUNT,
912 HCE_BASE_REF_AMOUNT,
913 TRANSACTION_RATE,
914 BASE_RATE,
915 INTEREST,
916 HCE_INTEREST)
917 values(
918 L_AS_AT_DATE,
919 P_DEAL_TYPE,
920 P_DEAL_NUMBER,
921 P_TRANSACTION_NUMBER,
922 P_COMPANY_CODE,
923 P_CPARTY_CODE,
924 decode(P_DEAL_TYPE,
925 'CA',decode(sign(nvl(P_BASE_REF_AMOUNT,0)),-1,'FUND','INVEST'),
926 'IG',decode(sign(nvl(P_BASE_REF_AMOUNT,0)),-1, 'FUND','INVEST'),
927 P_DEAL_SUBTYPE), --bug 2345708
928 P_PRODUCT_TYPE,
929 P_PORTFOLIO_CODE,
930 P_CURRENCY,
931 P_CONTRA_CCY,
932 P_CURRENCY_COMBINATION,
936 L_HCE_BASE_REF_AMOUNT,
933 P_YEAR_CALC_TYPE,
934 P_ACCOUNT_NO,
935 L_BASE_REF_AMOUNT,
937 decode(P_DEAL_TYPE, 'FX', P_TRANSACTION_RATE, L_CONVERT_RATE),
938 P_BASE_RATE,
939 decode(P_DEAL_TYPE, 'FX', 0, abs(L_DAILY_INT)),
940 decode(P_DEAL_TYPE, 'FX', 0, abs(L_HCE_INT)));
941 end if;
942 L_AS_AT_DATE :=L_AS_AT_DATE +1;
943 END LOOP;
944 */
945 end if;
946
947 exception
948 when app_exceptions.RECORD_LOCK_EXCEPTION then
949 if CHK_LOCK_ROWS_ONC%ISOPEN then
950 close CHK_LOCK_ROWS_ONC;
951 end if;
952 if CHK_LOCK_ROWS_TMM%ISOPEN then
953 close CHK_LOCK_ROWS_TMM;
954 end if;
955 if CHK_LOCK_ROWS_CA%ISOPEN then
956 close CHK_LOCK_ROWS_CA;
957 end if;
958 if CHK_LOCK_ROWS_IG%ISOPEN then
959 close CHK_LOCK_ROWS_IG;
960 end if;
961 raise app_exceptions.RECORD_LOCK_EXCEPTION;
962
963 end MAINTAIN_POSITION_HISTORY;
964
965
966 PROCEDURE SNAPSHOT_POSITION_HISTORY(
967 P_AS_AT_DATE IN DATE,
968 P_DEAL_NUMBER IN NUMBER,
969 P_TRANSACTION_NUMBER IN NUMBER,
970 P_COMPANY_CODE IN VARCHAR2,
971 P_CURRENCY IN VARCHAR2,
972 P_DEAL_TYPE IN VARCHAR2,
973 P_DEAL_SUBTYPE IN VARCHAR2,
974 P_PRODUCT_TYPE IN VARCHAR2,
975 P_PORTFOLIO_CODE IN VARCHAR2,
976 P_CPARTY_CODE IN VARCHAR2,
977 P_CONTRA_CCY IN VARCHAR2,
978 P_CURRENCY_COMBINATION IN VARCHAR2,
979 P_ACCOUNT_NO IN VARCHAR2,
980 P_TRANSACTION_RATE IN NUMBER,
981 P_YEAR_CALC_TYPE IN VARCHAR2,
982 P_BASE_REF_AMOUNT IN NUMBER,
983 P_BASE_RATE IN NUMBER,
984 P_STATUS_CODE IN VARCHAR2,
985 P_START_DATE IN DATE,
986 P_MATURITY_DATE IN DATE,
987 P_INTEREST IN NUMBER,
988 P_MATURITY_AMOUNT IN NUMBER,
989 P_START_AMOUNT IN NUMBER,
990 P_CALC_BASIS IN VARCHAR2,
991 P_CALC_TYPE IN VARCHAR2,
992 -- Added the new parameters for Intrest Override feature
993 P_DAY_COUNT_TYPE IN VARCHAR2,
994 P_FIRST_TRANS_FLAG IN VARCHAR2
995 --
996 ) as
997
998 ---
999 cursor HCE is
1000 select s.HCE_RATE,s.ROUNDING_FACTOR
1001 from XTR_MASTER_CURRENCIES_V s
1002 where s.CURRENCY = P_CURRENCY;
1003
1004 --
1005 L_HCE_BASE_REF_AMOUNT NUMBER;
1006 L_REF_DATE DATE;
1007 L_END_DATE DATE;
1008 L_AS_AT_DATE DATE;
1009 L_PROC_DATE DATE;
1010 L_RESALE_DATE DATE;
1011 L_LAST_PROC_DATE DATE;
1012 L_EARLY_START_DATE DATE;
1013 L_ROWID VARCHAR2(30);
1014 L_AMOUNT NUMBER;
1015 L_HCE_RATE NUMBER;
1016 L_FAC NUMBER;
1017 L_YEAR_BASIS NUMBER;
1018 L_NO_OF_DAYS NUMBER;
1019 L_NO_OF_DAYS_IN NUMBER;
1020 L_NO_OF_DAYS_OUT NUMBER;
1021 L_YEAR_BASIS_IN NUMBER;
1022 L_YEAR_BASIS_OUT NUMBER;
1023 L_YIELD_RATE NUMBER;
1024 L_CALC_BASIS XTR_DEALS.calc_basis%TYPE;
1025 L_CALC_TYPE XTR_BOND_ISSUES.calc_type%TYPE;
1026 L_COUPON_RATE NUMBER;
1027 L_MATURITY_AMT NUMBER;
1028 L_CONSIDERATION NUMBER;
1029 L_CONVERT_RATE NUMBER;
1030 L_CONVERT_TYPE CONSTANT VARCHAR2(15) := 'ACTUAL365';
1031 L_TOTAL_INT NUMBER;
1032 L_DAILY_INT NUMBER;
1033 L_HCE_INT NUMBER;
1034 L_NEXT_YEAR DATE;
1035 L_FACE_VALUE_SOLD NUMBER := NULL;
1036 L_LAST_RESALE_DATE DATE:= NULL;
1037 L_BASE_REF_AMOUNT NUMBER;
1038 -- Added for Interest Override
1039 l_interest NUMBER;
1040 l_original_amount NUMBER;
1041 l_face_value NUMBER;
1042 l_fully_resold VARCHAR2(1);
1043 l_till_date DATE;
1044 L_DAILY_INT_INIT NUMBER;
1045 L_HCE_INT_INIT NUMBER;
1046 L_HCE_BASE_REF_AMOUNT_INIT NUMBER;
1047 l_maturity_date DATE;
1048
1049
1050 /*********************************/
1051 /* For DEAL_TYPE in('NI','ONC') */
1052 /*********************************/
1053 cursor GET_PRV_ROWS_ONC(V_DEAL_TYPE VARCHAR2,
1054 V_DEAL_NUMBER NUMBER,
1055 V_TRANSACTION_NUMBER NUMBER) is
1056 select max(AS_AT_DATE + 1)
1057 from XTR_POSITION_HISTORY
1058 where DEAL_TYPE = V_DEAL_TYPE
1059 and DEAL_NUMBER = V_DEAL_NUMBER
1060 and TRANSACTION_NUMBER = V_TRANSACTION_NUMBER;
1061
1062
1063 cursor CHK_LOCK_ROWS_ONC(V_AS_AT_DATE DATE,
1064 V_DEAL_TYPE VARCHAR2,
1065 V_DEAL_NUMBER NUMBER,
1066 V_TRANSACTION_NUMBER NUMBER) is
1067 select rowid
1068 from XTR_POSITION_HISTORY
1069 where AS_AT_DATE = V_AS_AT_DATE
1070 and DEAL_TYPE = V_DEAL_TYPE
1071 and DEAL_NUMBER = V_DEAL_NUMBER
1072 and TRANSACTION_NUMBER = V_TRANSACTION_NUMBER
1073 for update of BASE_REF_AMOUNT NOWAIT;
1074
1075 /*************************/
1076 /* For DEAL_TYPE 'BOND' */
1077 /*************************/
1078 cursor GET_PRV_ROWS_BOND(V_DEAL_TYPE VARCHAR2,
1079 V_DEAL_NUMBER NUMBER) is
1080 select max(AS_AT_DATE + 1)
1081 from XTR_POSITION_HISTORY
1082 where DEAL_TYPE = V_DEAL_TYPE
1083 and DEAL_NUMBER = V_DEAL_NUMBER;
1084
1085 cursor BOND_LAST_PROC_DATE(V_AS_AT_DATE DATE,
1089 where deal_no = V_DEAL_NUMBER
1086 V_DEAL_NUMBER NUMBER)is
1087 select cross_ref_start_date, avg_rate_last_processed
1088 from xtr_bond_alloc_details
1090 and CROSS_REF_START_DATE <= V_AS_AT_DATE
1091 and avg_rate_last_processed is null
1092 order by cross_ref_start_date; -- 4470022
1093
1094 cursor CHK_LOCK_ROWS_BOND(V_AS_AT_DATE DATE,
1095 V_DEAL_TYPE VARCHAR2,
1096 V_DEAL_NUMBER NUMBER) is
1097 select rowid
1098 from XTR_POSITION_HISTORY
1099 where AS_AT_DATE = V_AS_AT_DATE
1100 and DEAL_TYPE = V_DEAL_TYPE
1101 and DEAL_NUMBER = V_DEAL_NUMBER
1102 for update of BASE_REF_AMOUNT NOWAIT;
1103
1104
1105 /***********************************************/
1106 /* For DEAL_TYPE in('TMM','RTMM')*/
1107 /***********************************************/
1108 cursor GET_PRV_ROWS_TMM(V_DEAL_TYPE VARCHAR2,
1109 V_DEAL_NUMBER NUMBER) is
1110 select max(AS_AT_DATE + 1)
1111 from XTR_POSITION_HISTORY
1112 where DEAL_TYPE = V_DEAL_TYPE
1113 and DEAL_NUMBER = V_DEAL_NUMBER;
1114
1115 cursor CHK_LOCK_ROWS_TMM(V_AS_AT_DATE DATE,
1116 V_DEAL_TYPE VARCHAR2,
1117 V_DEAL_NUMBER NUMBER) is
1118 select rowid
1119 from XTR_POSITION_HISTORY
1120 where AS_AT_DATE = V_AS_AT_DATE
1121 and DEAL_TYPE = V_DEAL_TYPE
1122 and DEAL_NUMBER = V_DEAL_NUMBER
1123 for update of BASE_REF_AMOUNT NOWAIT;
1124
1125 /***********************/
1126 /* For DEAL_TYPE 'CA' */
1127 /***********************/
1128 cursor GET_PRV_ROWS_CA(V_DEAL_TYPE VARCHAR2,
1129 V_COMPANY_CODE VARCHAR2,
1130 V_ACCOUNT_NO VARCHAR2) is
1131 select max(AS_AT_DATE + 1)
1132 from XTR_POSITION_HISTORY
1133 where DEAL_TYPE = V_DEAl_TYPE
1134 and COMPANY_CODE = V_COMPANY_CODE
1135 and ACCOUNT_NO = V_ACCOUNT_NO;
1136
1137 cursor CHK_LOCK_ROWS_CA(V_AS_AT_DATE DATE,
1138 V_DEAL_TYPE VARCHAR2,
1139 V_COMPANY_CODE VARCHAR2,
1140 V_ACCOUNT_NO VARCHAR2) is
1141 select rowid
1142 from XTR_POSITION_HISTORY
1143 where AS_AT_DATE = V_AS_AT_DATE
1144 and DEAL_TYPE = V_DEAL_TYPE
1145 and COMPANY_CODE = V_COMPANY_CODE
1146 and ACCOUNT_NO = V_ACCOUNT_NO
1147 for update of BASE_REF_AMOUNT NOWAIT;
1148
1149 /***********************/
1150 /* For DEAL_TYPE 'IG' */
1151 /***********************/
1152 cursor GET_PRV_ROWS_IG(V_DEAL_TYPE VARCHAR2,
1153 V_DEAL_NUMBER NUMBER) is
1154 select MAX(AS_AT_DATE + 1)
1155 from XTR_POSITION_HISTORY
1156 where DEAL_TYPE = V_DEAL_TYPE
1157 and DEAL_NUMBER = V_DEAl_NUMBER;
1158
1159 cursor CHK_LOCK_ROWS_IG(V_AS_AT_DATE DATE,
1160 V_DEAL_TYPE VARCHAR2,
1161 V_DEAL_NUMBER NUMBER) is
1162 select rowid
1163 from XTR_POSITION_HISTORY
1164 where AS_AT_DATE = V_AS_AT_DATE
1165 and DEAL_TYPE = V_DEAL_TYPE
1166 and DEAL_NUMBER = V_DEAl_NUMBER
1167 for update of BASE_REF_AMOUNT NOWAIT;
1168
1169
1170 cursor get_bond_calc is
1171 select b.calc_type, d.coupon_rate, d.maturity_amount, d.start_amount
1172 from xtr_deals D, xtr_bond_issues B
1173 where d.bond_issue = b.bond_issue_code
1174 and d.deal_no = P_DEAL_NUMBER;
1175
1176 cursor get_bond_resale is
1177 Select sum(face_value), max(cross_ref_start_date)
1178 From XTR_BOND_ALLOC_DETAILS
1179 Where deal_no = P_DEAL_NUMBER
1180 and cross_ref_start_date <= P_AS_AT_DATE;
1181
1182 -- bug 4539511
1183
1184 cursor get_bond_maturity_date is
1185 Select maturity_date
1186 from xtr_deals
1187 where deal_no = p_deal_number
1188 and deal_type = 'BOND'
1189 and maturity_date <= P_AS_AT_DATE;
1190
1191
1192 BEGIN
1193 open HCE;
1194 fetch HCE into L_HCE_RATE,L_FAC;
1195 close HCE;
1196
1197 L_HCE_BASE_REF_AMOUNT :=round(P_BASE_REF_AMOUNT/L_HCE_RATE,L_FAC);
1198
1199 /*====================================================*/
1200 /* Calculate the following columns' values: */
1201 /* (1) Daily Interst */
1202 /* (2) HCE_INTEREST */
1203 /* (3) transaction rate in terms of Actual/365 basis */
1204 /*====================================================*/
1205 If P_DEAL_TYPE = 'NI' then
1206 if P_CALC_BASIS = 'DISCOUNT' then
1207 /** Need to convert to yield rate first if it's Discount basis **/
1208 -- Added the parameters for Interest Override feature
1209 XTR_CALC_P.calc_days_run(p_start_date,
1210 p_maturity_date,
1211 p_year_calc_type,
1212 l_no_of_days,
1213 l_year_basis,
1214 p_day_count_type,
1215 p_first_trans_flag
1216 );
1217
1218 XTR_RATE_CONVERSION.Discount_To_Yield_Rate(P_TRANSACTION_RATE, L_NO_OF_DAYS,
1219 L_YEAR_BASIS, L_YIELD_RATE);
1220 else
1221 L_YIELD_RATE := P_TRANSACTION_RATE;
1222 end if;
1223 Else
1224 L_YIELD_RATE := P_TRANSACTION_RATE;
1225 End if;
1226
1227 /*-----------------------------*/
1228 /* NI, TMM, RTMM, ONC mature */
1229 /*-----------------------------*/
1230 If P_DEAL_TYPE in ('NI', 'TMM', 'RTMM') or
1231 (P_DEAL_TYPE = 'ONC' and P_MATURITY_DATE is not null) THEN
1235 L_HCE_INT := L_DAILY_INT / L_HCE_RATE;
1232 -- Changed for Interest Override feature
1233 IF p_day_count_type ='B' AND p_first_trans_flag='Y' THEN
1234 L_DAILY_INT := P_INTEREST / (p_maturity_date - p_start_date +1);
1236 ELSE
1237 L_DAILY_INT := P_INTEREST / (p_maturity_date - p_start_date);
1238 L_HCE_INT := L_DAILY_INT / L_HCE_RATE;
1239 END IF;
1240
1241 -- Added the new parameter for Interest Override feature
1242 XTR_CALC_P.calc_days_run(p_start_date,
1243 p_maturity_date,
1244 p_year_calc_type,
1245 l_no_of_days_in,
1246 l_year_basis_in,
1247 NULL,
1248 p_day_count_type,
1249 p_first_trans_flag
1250 );
1251 XTR_CALC_P.calc_days_run(p_start_date,
1252 p_maturity_date,
1253 l_convert_type,
1254 l_no_of_days_out,
1255 l_year_basis_out,
1256 NULL,
1257 p_day_count_type,
1258 p_first_trans_flag
1259 );
1260
1261 XTR_RATE_CONVERSION.day_count_basis_conv(l_no_of_days_in,
1262 l_no_of_days_out,
1263 l_year_basis_in,
1264 l_year_basis_out,
1265 l_yield_rate,
1266 L_CONVERT_RATE);
1267 /*--------------------------*/
1268 /* CA, IG, ONC not mature */
1269 /*--------------------------*/
1270 Elsif P_DEAL_TYPE in ('CA', 'IG') or
1271 (P_DEAL_TYPE = 'ONC' and P_MATURITY_DATE is null) then
1272 -- L_DAILY_INT := P_TRANSACTION_RATE * P_BASE_REF_AMOUNT / (100 * 365);
1273 -- L_HCE_INT := L_DAILY_INT / L_HCE_RATE;
1274
1275 -- Added for Interest Override
1276 IF P_DEAL_TYPE in ('CA', 'IG') AND P_INTEREST IS NOT NULL THEN
1277 L_DAILY_INT := L_DAILY_INT + P_INTEREST;
1278 L_HCE_INT := L_DAILY_INT / L_HCE_RATE;
1279 END IF;
1280 --
1281
1282 L_NEXT_YEAR := add_months(p_start_date, 12);
1283
1284 -- Added the new parameter for Interest Override feature
1285 XTR_CALC_P.calc_days_run(p_start_date,
1286 l_next_year,
1287 p_year_calc_type,
1288 l_no_of_days_in,
1289 l_year_basis_in,
1290 NULL,
1291 p_day_count_type,
1292 p_first_trans_flag
1293 );
1294
1295 XTR_RATE_CONVERSION.day_count_basis_conv(1,
1296 1,
1297 l_year_basis_in,
1298 365,
1299 l_yield_rate,
1300 L_CONVERT_RATE);
1301 L_DAILY_INT := P_TRANSACTION_RATE * P_BASE_REF_AMOUNT / (100 * l_year_basis_in);
1302 L_HCE_INT := L_DAILY_INT / L_HCE_RATE;
1303
1304 /*--------*/
1305 /* BOND */
1306 /*--------*/
1307 Elsif P_DEAL_TYPE = 'BOND' THEN
1308 -- Added the new parameter for Interest Override feature
1309 XTR_COF_P.Calculate_Bond_rate(P_DEAL_NUMBER,
1310 P_MATURITY_AMOUNT,
1311 P_START_AMOUNT,
1312 P_TRANSACTION_RATE, -- jhung make sure it's coupon rate
1313 P_START_DATE,
1314 P_MATURITY_DATE,
1315 P_CALC_TYPE,
1316 L_DAILY_INT,
1317 L_CONVERT_RATE,
1318 P_DAY_COUNT_TYPE -- Added for Interest Override
1319 );
1320 L_HCE_INT := L_DAILY_INT / L_HCE_RATE;
1321
1322 Open get_bond_resale;
1323 Fetch get_bond_resale into l_face_value_sold, l_last_resale_date;
1324 If get_bond_resale%FOUND then
1325 If nvl(l_face_value_sold, 0) = P_MATURITY_AMOUNT then -- totally resale
1326 Delete from XTR_POSITION_HISTORY
1327 Where deal_number = P_DEAL_NUMBER
1328 And as_at_date >= l_last_resale_date;
1329
1330 close get_bond_resale;
1331 l_fully_resold := 'Y';
1332
1333
1334 -- Shifted the else in while loop for bug 4470022
1335 end if;
1336
1337 Else
1338 close get_bond_resale;
1339 End if;
1340
1341 End if;
1342
1343 if P_DEAL_TYPE NOT in('BOND','TMM','RTMM','CA','IG') then
1344 open GET_PRV_ROWS_ONC(P_DEAL_TYPE,P_DEAL_NUMBER,
1345 P_TRANSACTION_NUMBER);
1346 fetch GET_PRV_ROWS_ONC into L_AS_AT_DATE;
1347 close GET_PRV_ROWS_ONC;
1348 elsif P_DEAL_TYPE = 'BOND' then
1349 open GET_PRV_ROWS_BOND(P_DEAL_TYPE,P_DEAL_NUMBER);
1350 fetch GET_PRV_ROWS_BOND into L_AS_AT_DATE;
1351 if GET_PRV_ROWS_BOND%FOUND then -- deal no has some data in PH
1352 open BOND_LAST_PROC_DATE(L_AS_AT_DATE,P_DEAL_NUMBER);
1353 fetch BOND_LAST_PROC_DATE into L_RESALE_DATE, L_LAST_PROC_DATE;
1354 if BOND_LAST_PROC_DATE%FOUND then
1355 L_EARLY_START_DATE := nvl(L_LAST_PROC_DATE, L_RESALE_DATE);
1356 if L_EARLY_START_DATE < nvl(L_AS_AT_DATE, sysdate) then
1357 L_AS_AT_DATE := L_EARLY_START_DATE;
1358 end if;
1359 close BOND_LAST_PROC_DATE;
1360 else
1361 close BOND_LAST_PROC_DATE;
1362 end if;
1363 close GET_PRV_ROWS_BOND;
1364 else
1365 close GET_PRV_ROWS_BOND;
1366 end if;
1367
1368 elsif P_DEAL_TYPE in('TMM','RTMM') then
1369 open GET_PRV_ROWS_TMM(P_DEAL_TYPE,P_DEAL_NUMBER);
1370 fetch GET_PRV_ROWS_TMM into L_AS_AT_DATE;
1371 close GET_PRV_ROWS_TMM;
1372 elsif P_DEAL_TYPE = 'CA' then
1373 open GET_PRV_ROWS_CA(P_DEAL_TYPE,
1374 P_COMPANY_CODE,P_ACCOUNT_NO);
1375 fetch GET_PRV_ROWS_CA into L_AS_AT_DATE;
1376 close GET_PRV_ROWS_CA;
1377 elsif P_DEAL_TYPE = 'IG' then
1378 open GET_PRV_ROWS_IG(P_DEAL_TYPE,P_DEAL_NUMBER);
1379 fetch GET_PRV_ROWS_IG into L_AS_AT_DATE;
1380 close GET_PRV_ROWS_IG;
1381 end if;
1382
1383 L_AS_AT_DATE :=nvl(L_AS_AT_DATE,P_AS_AT_DATE);
1384
1385
1386 -- bug 4470022 Added the following lines
1387
1388 IF p_deal_type = 'BOND' THEN
1389
1390 L_DAILY_INT_INIT:= L_DAILY_INT;
1391 L_HCE_INT_INIT := L_HCE_INT;
1392 L_HCE_BASE_REF_AMOUNT_INIT := l_hce_base_ref_amount ;
1393
1394 -- bug 4539511
1395 open get_bond_maturity_date;
1396 fetch get_bond_maturity_date into l_maturity_date;
1397 close get_bond_maturity_date;
1398
1399 l_till_date := nvl(l_maturity_date -1 , p_as_at_date);
1400
1401
1402
1403 if l_fully_resold = 'Y' then
1404
1405 l_till_date := l_last_resale_date -1 ;
1406
1407 end if;
1408
1409 ELSE
1410
1411 l_till_date := p_as_at_date;
1412 end if;
1413
1414
1415 -- Bug 4470022 ended
1416
1417
1418 WHILE L_AS_AT_DATE <= L_TILL_DATE LOOP
1419 L_ROWID :=NULL;
1420 if P_DEAL_TYPE NOT in('BOND', 'TMM','RTMM','CA','IG') then
1421 open CHK_LOCK_ROWS_ONC(L_AS_AT_DATE,P_DEAL_TYPE,P_DEAL_NUMBER,
1422 P_TRANSACTION_NUMBER);
1423 fetch CHK_LOCK_ROWS_ONC into L_ROWID;
1424 close CHK_LOCK_ROWS_ONC;
1425 elsif P_DEAL_TYPE = 'BOND' then
1426 open CHK_LOCK_ROWS_BOND(L_AS_AT_DATE,P_DEAL_TYPE,P_DEAL_NUMBER);
1427 fetch CHK_LOCK_ROWS_BOND into L_ROWID;
1428 close CHK_LOCK_ROWS_BOND;
1429
1430 -- bug 4470022 Added the following lines
1431 Select sum(face_value)
1432 into l_face_value
1433 From XTR_BOND_ALLOC_DETAILS
1434 Where deal_no = P_DEAL_NUMBER
1435 and cross_ref_start_date <= L_AS_AT_DATE;
1436
1437 IF l_face_value <> 0 then
1438
1439 -- Added and changed for Interest Override
1440 SELECT SUM(interest), SUM(original_amount)
1441 INTO l_interest,l_original_amount
1442 FROM xtr_rollover_transactions
1443 WHERE deal_number = P_DEAL_NUMBER;
1444
1445
1446 IF l_interest = l_original_amount THEN
1447
1448 L_DAILY_INT := L_DAILY_INT_INIT* (P_MATURITY_AMOUNT - l_face_value)/P_MATURITY_AMOUNT;
1449 L_HCE_INT := L_HCE_INT_INIT* (P_MATURITY_AMOUNT -l_face_value)/P_MATURITY_AMOUNT;
1450 L_BASE_REF_AMOUNT := p_base_ref_amount * (P_MATURITY_AMOUNT -l_face_value)/P_MATURITY_AMOUNT;
1451 L_HCE_BASE_REF_AMOUNT := l_hce_base_ref_amount_init *
1452 (P_MATURITY_AMOUNT -l_face_value)/P_MATURITY_AMOUNT;
1453 L_CONVERT_RATE := L_DAILY_INT * 365 * 100 / L_BASE_REF_AMOUNT;
1454
1455 END If;
1456 END if;
1457 -- bug 4470022 ended
1458
1459 elsif P_DEAL_TYPE in('TMM','RTMM') then
1460 open CHK_LOCK_ROWS_TMM(L_AS_AT_DATE,P_DEAL_TYPE,P_DEAL_NUMBER);
1461 fetch CHK_LOCK_ROWS_TMM into L_ROWID;
1462 close CHK_LOCK_ROWS_TMM;
1463 elsif P_DEAL_TYPE = 'CA' then
1464 open CHK_LOCK_ROWS_CA(L_AS_AT_DATE,P_DEAL_TYPE,
1465 P_COMPANY_CODE,P_ACCOUNT_NO);
1466 fetch CHK_LOCK_ROWS_CA into L_ROWID;
1467 close CHK_LOCK_ROWS_CA;
1468 elsif P_DEAL_TYPE = 'IG' then
1469 open CHK_LOCK_ROWS_IG(L_AS_AT_DATE,P_DEAL_TYPE,P_DEAL_NUMBER);
1470 fetch CHK_LOCK_ROWS_IG into L_ROWID;
1471 close CHK_LOCK_ROWS_IG;
1472 end if;
1473
1474 /*========================================*/
1475 /* Insert or Update postion history table */
1476 /*========================================*/
1477 if L_ROWID is not null then
1478 update XTR_POSITION_HISTORY
1479 set COMPANY_CODE = P_COMPANY_CODE,
1480 CPARTY_CODE = P_CPARTY_CODE,
1481 /* DEAL_SUBTYPE = decode(DEAL_TYPE,
1482 'CA',decode(sign(nvl(BASE_REF_AMOUNT,0)
1483 +nvl(P_BASE_REF_AMOUNT,0)),-1,'FUND','INVEST'),
1484 'IG',decode(sign(nvl(BASE_REF_AMOUNT,0)
1485 +nvl(P_BASE_REF_AMOUNT,0)),-1, 'FUND','INVEST'),
1486 P_DEAL_SUBTYPE), */
1487 DEAL_SUBTYPE = decode(DEAL_TYPE,
1488 'CA',decode(sign(nvl(P_BASE_REF_AMOUNT,0)),-1,'FUND','INVEST'),
1489 'IG',decode(sign(nvl(P_BASE_REF_AMOUNT,0)),-1, 'FUND','INVEST'),
1490 P_DEAL_SUBTYPE), -- bug 2345708
1491 PRODUCT_TYPE = P_PRODUCT_TYPE,
1492 PORTFOLIO_CODE = P_PORTFOLIO_CODE,
1493 CURRENCY = P_CURRENCY,
1494 CONTRA_CCY = P_CONTRA_CCY,
1495 CURRENCY_COMBINATION = P_CURRENCY_COMBINATION,
1496 YEAR_CALC_TYPE = P_YEAR_CALC_TYPE,
1497 ACCOUNT_NO = P_ACCOUNT_NO,
1498 BASE_REF_AMOUNT = decode(DEAL_TYPE,'CA',abs(P_BASE_REF_AMOUNT),
1499 'IG',abs(P_BASE_REF_AMOUNT),
1500 'BOND', abs(nvl(L_BASE_REF_AMOUNT,P_BASE_REF_AMOUNT)),
1501 P_BASE_REF_AMOUNT),
1502 HCE_BASE_REF_AMOUNT =decode(DEAL_TYPE,'CA',abs(L_HCE_BASE_REF_AMOUNT),
1503 'IG',abs(L_HCE_BASE_REF_AMOUNT),
1504 L_HCE_BASE_REF_AMOUNT), -- bug2345708
1505 BASE_RATE = P_BASE_RATE,
1506 TRANSACTION_RATE = decode(P_DEAL_TYPE, 'FX', P_TRANSACTION_RATE, L_CONVERT_RATE),
1507 INTEREST = decode(DEAL_TYPE, 'CA', abs(L_DAILY_INT),
1508 'IG', abs(L_DAILY_INT),
1509 'FX', 0,
1510 L_DAILY_INT),
1511 HCE_INTEREST = decode(DEAL_TYPE, 'CA', abs(L_HCE_INT),
1512 'IG', abs(L_HCE_INT),
1513 'FX', 0,
1514 L_HCE_INT)
1515 where rowid=l_rowid;
1516 else
1517 -- insert new row
1518 insert into XTR_POSITION_HISTORY(
1519 AS_AT_DATE,
1520 DEAL_TYPE,
1521 DEAL_NUMBER,
1522 TRANSACTION_NUMBER,
1523 COMPANY_CODE,
1524 CPARTY_CODE,
1525 DEAL_SUBTYPE,
1526 PRODUCT_TYPE,
1527 PORTFOLIO_CODE,
1528 CURRENCY,
1529 CONTRA_CCY,
1530 CURRENCY_COMBINATION,
1531 YEAR_CALC_TYPE,
1532 ACCOUNT_NO,
1533 BASE_REF_AMOUNT,
1534 HCE_BASE_REF_AMOUNT,
1535 TRANSACTION_RATE,
1536 BASE_RATE,
1537 INTEREST,
1538 HCE_INTEREST)
1539 values(
1540 L_AS_AT_DATE,
1541 P_DEAL_TYPE,
1542 P_DEAL_NUMBER,
1543 P_TRANSACTION_NUMBER,
1544 P_COMPANY_CODE,
1545 P_CPARTY_CODE,
1546 P_DEAL_SUBTYPE,
1547 P_PRODUCT_TYPE,
1548 P_PORTFOLIO_CODE,
1549 P_CURRENCY,
1550 P_CONTRA_CCY,
1551 P_CURRENCY_COMBINATION,
1552 P_YEAR_CALC_TYPE,
1553 P_ACCOUNT_NO,
1554 abs(nvl(L_BASE_REF_AMOUNT, P_BASE_REF_AMOUNT)),
1555 abs(L_HCE_BASE_REF_AMOUNT),
1556 decode(P_DEAL_TYPE, 'FX', P_TRANSACTION_RATE, L_CONVERT_RATE),
1557 P_BASE_RATE,
1558 decode(P_DEAL_TYPE, 'FX', 0, abs(L_DAILY_INT)),
1559 decode(P_DEAL_TYPE, 'FX', 0, abs(L_HCE_INT)));
1560 end if;
1561
1562 if P_DEAL_TYPE = 'BOND' and l_face_value_sold is not null then
1563 Update XTR_BOND_ALLOC_DETAILS
1564 set avg_rate_last_processed = greatest(nvl(avg_rate_last_processed, L_AS_AT_DATE),
1565 L_AS_AT_DATE)
1566 where deal_no = P_DEAL_NUMBER;
1567 end if;
1568 L_AS_AT_DATE :=L_AS_AT_DATE +1;
1569 END LOOP;
1570
1571 exception
1572 when app_exceptions.RECORD_LOCK_EXCEPTION then
1573 if CHK_LOCK_ROWS_ONC%ISOPEN then
1574 close CHK_LOCK_ROWS_ONC;
1575 end if;
1576 if CHK_LOCK_ROWS_TMM%ISOPEN then
1577 close CHK_LOCK_ROWS_TMM;
1578 end if;
1579 if CHK_LOCK_ROWS_CA%ISOPEN then
1580 close CHK_LOCK_ROWS_CA;
1581 end if;
1582 if CHK_LOCK_ROWS_IG%ISOPEN then
1583 close CHK_LOCK_ROWS_IG;
1584 end if;
1585 raise app_exceptions.RECORD_LOCK_EXCEPTION;
1586
1590 PROCEDURE MAINTAIN_COST_OF_FUND(
1587 end SNAPSHOT_POSITION_HISTORY;
1588
1589
1591 OLD_AS_AT_DATE IN date,
1592 OLD_COMPANY_CODE IN VARCHAR2,
1593 OLD_CURRENCY IN VARCHAR2,
1594 OLD_DEAL_TYPE IN VARCHAR2,
1595 OLD_DEAL_SUBTYPE IN VARCHAR2,
1596 OLD_PRODUCT_TYPE IN VARCHAR2,
1597 OLD_PORTFOLIO_CODE IN VARCHAR2,
1598 OLD_CPARTY_CODE IN VARCHAR2,
1599 OLD_CONTRA_CCY IN VARCHAR2,
1600 OLD_CURRENCY_COMBINATION IN VARCHAR2,
1601 OLD_ACCOUNT_NO IN VARCHAR2,
1602 OLD_TRANSACTION_RATE IN NUMBER,
1603 OLD_YEAR_CALC_TYPE IN VARCHAR2,
1604 OLD_BASE_REF_AMOUNT IN NUMBER,
1605 OLD_HCE_BASE_REF_AMOUNT IN NUMBER,
1606 OLD_BASE_RATE IN NUMBER,
1607 OLD_INTEREST IN NUMBER,
1608 OLD_HCE_INTEREST IN NUMBER,
1609 NEW_AS_AT_DATE IN date,
1610 NEW_COMPANY_CODE IN VARCHAR2,
1611 NEW_CURRENCY IN VARCHAR2,
1612 NEW_DEAL_TYPE IN VARCHAR2,
1613 NEW_DEAL_SUBTYPE IN VARCHAR2,
1614 NEW_PRODUCT_TYPE IN VARCHAR2,
1615 NEW_PORTFOLIO_CODE IN VARCHAR2,
1616 NEW_CPARTY_CODE IN VARCHAR2,
1617 NEW_CONTRA_CCY IN VARCHAR2,
1618 NEW_CURRENCY_COMBINATION IN VARCHAR2,
1619 NEW_ACCOUNT_NO IN VARCHAR2,
1620 NEW_TRANSACTION_RATE IN NUMBER,
1621 NEW_YEAR_CALC_TYPE IN VARCHAR2,
1622 NEW_BASE_REF_AMOUNT IN NUMBER,
1623 NEW_HCE_BASE_REF_AMOUNT IN NUMBER,
1624 NEW_BASE_RATE IN NUMBER,
1625 NEW_INTEREST IN NUMBER,
1626 NEW_HCE_INTEREST IN NUMBER,
1627 P_ACTION IN VARCHAR2) as
1628
1629 ---
1630
1631 L_ROWID VARCHAR2(30);
1632
1633 cursor CHK_LOCK_ROWS(V_AS_AT_DATE DATE,
1634 V_COMPANY_CODE VARCHAR2,
1635 V_CPARTY_CODE VARCHAR2,
1636 V_DEAL_TYPE VARCHAR2,
1637 V_DEAL_SUBTYPE VARCHAR2,
1638 V_PRODUCT_TYPE VARCHAR2,
1639 V_PORTFOLIO_CODE VARCHAR2,
1640 V_CURRENCY VARCHAR2,
1641 V_CONTRA_CCY VARCHAR2,
1642 V_CURRENCY_COMBINATION VARCHAR2,
1643 V_ACCOUNT_NO VARCHAR2) is
1644 select ROWID,GROSS_PRINCIPAL
1645 from XTR_COST_OF_FUNDS
1646 where AS_AT_DATE = V_AS_AT_DATE
1647 and DEAL_TYPE = V_DEAL_TYPE
1648 and DEAL_SUBTYPE = V_DEAL_SUBTYPE
1649 and COMPANY_CODE = V_COMPANY_CODE
1650 and CURRENCY = V_CURRENCY
1651 and nvl(CURRENCY_COMBINATION,'%')=nvl(V_CURRENCY_COMBINATION,'%')
1652 and nvl(PRODUCT_TYPE,'%') = nvl(V_PRODUCT_TYPE,'%')
1653 and nvl(PORTFOLIO_CODE,'%') = nvl(V_PORTFOLIO_CODE,'%')
1654 and nvl(PARTY_CODE,'%') = nvl(V_CPARTY_CODE,'%')
1655 for update of GROSS_PRINCIPAL NOWAIT;
1656
1657 l_gross NUMBER;
1658 l_currency VARCHAR2(15);
1659 l_hce_rate NUMBER;
1660 l_fac NUMBER;
1661 v_100 NUMBER;
1662 l_interest NUMBER;
1663 l_interest_hce NUMBER;
1664
1665 cursor get_rounding_factor is
1666 select hce_rate,rounding_factor
1667 from XTR_MASTER_CURRENCIES_V
1668 where currency=l_currency;
1669
1670 BEGIN
1671 /**************/
1672 /* INSERT */
1673 /**************/
1674 if P_ACTION='INSERT' then
1675 if (NEW_DEAL_TYPE<>'IG') then /* IG handled by maintain_position_history, see comments for maintain_cof_ig */
1676 l_currency :=NEW_CURRENCY;
1677 open get_rounding_factor;
1678 fetch get_rounding_factor into l_hce_rate,l_fac;
1679 close get_rounding_factor;
1680 l_fac :=nvl(l_fac,2);
1681
1682 L_INTEREST :=NULL;
1683 L_INTEREST_HCE :=NULL;
1684
1685 if NEW_DEAL_TYPE = 'FX' then
1686 v_100 :=1;
1687 l_fac :=4;
1688 else
1689 v_100 :=100;
1690 end if;
1691
1692 open CHK_LOCK_ROWS(
1693 NEW_AS_AT_DATE ,
1694 NEW_COMPANY_CODE,
1695 NEW_CPARTY_CODE,
1696 NEW_DEAL_TYPE,
1697 NEW_DEAL_SUBTYPE,
1698 NEW_PRODUCT_TYPE,
1699 NEW_PORTFOLIO_CODE,
1700 NEW_CURRENCY,
1701 NEW_CONTRA_CCY,
1702 NEW_CURRENCY_COMBINATION,
1703 NEW_ACCOUNT_NO);
1704 fetch CHK_LOCK_ROWS into l_rowid,l_gross;
1705 if CHK_LOCK_ROWS%FOUND then
1706 close CHK_LOCK_ROWS;
1707 If NEW_DEAL_TYPE = 'FX' then
1708 -- For FX, adding 3 new columns to calculate avg_interest_rate and avg_base_rate
1709 update XTR_COST_OF_FUNDS
1710 set GROSS_PRINCIPAL = nvl(GROSS_PRINCIPAL,0)+nvl(NEW_BASE_REF_AMOUNT,0),
1711 HCE_GROSS_PRINCIPAL =nvl(HCE_GROSS_PRINCIPAL,0)+nvl(NEW_HCE_BASE_REF_AMOUNT,0),
1712 WEIGHTED_AVG_PRINCIPAL = nvl(WEIGHTED_AVG_PRINCIPAL,0)
1713 +round(nvl(NEW_BASE_REF_AMOUNT,0)*
1714 nvl(NEW_TRANSACTION_RATE,0)/v_100,l_fac),
1715 GROSS_BASE_AMOUNT = nvl(GROSS_BASE_AMOUNT,0)+abs(nvl(NEW_BASE_REF_AMOUNT,0)),
1716 GROSS_CONTRA_TRANS_AMOUNT = nvl(GROSS_CONTRA_TRANS_AMOUNT,0)+
1717 abs(nvl(NEW_BASE_REF_AMOUNT,0))*nvl(NEW_TRANSACTION_RATE,0),
1718 GROSS_CONTRA_SPOT_AMOUNT = nvl(GROSS_CONTRA_SPOT_AMOUNT,0)+
1719 abs(nvl(NEW_BASE_REF_AMOUNT,0))*nvl(NEW_BASE_RATE,0),
1720 AVG_INTEREST_RATE = decode(nvl(GROSS_BASE_AMOUNT,0)+abs(nvl(NEW_BASE_REF_AMOUNT,0)),0,NULL,
1721 round((nvl(GROSS_CONTRA_TRANS_AMOUNT,0)+
1722 abs(nvl(NEW_BASE_REF_AMOUNT,0))*nvl(NEW_TRANSACTION_RATE,0))/
1723 (nvl(GROSS_BASE_AMOUNT,0)+abs(nvl(NEW_BASE_REF_AMOUNT,0))),l_fac)),
1724 AVG_BASE_RATE = decode(nvl(GROSS_BASE_AMOUNT,0)+abs(nvl(NEW_BASE_REF_AMOUNT,0)),0,NULL,
1725 round((nvl(GROSS_CONTRA_SPOT_AMOUNT,0)+
1726 abs(nvl(NEW_BASE_REF_AMOUNT,0))*nvl(NEW_BASE_RATE,0))/
1727 (nvl(GROSS_BASE_AMOUNT,0)+abs(nvl(NEW_BASE_REF_AMOUNT,0))),l_fac)),
1728 BASE_WEIGHTED_AVG_PRINCIPAL = nvl(BASE_WEIGHTED_AVG_PRINCIPAL,0)
1729 +round(nvl(NEW_BASE_REF_AMOUNT,0)*nvl(NEW_BASE_RATE,0)/v_100,l_fac),
1730 INTEREST = 0,
1731 HCE_INTEREST = 0
1732 where rowid=l_rowid;
1733 else -- For other deal types (TMM, RTMM, CA, IG, ONC, NI, BOND)
1734 update XTR_COST_OF_FUNDS
1735 set GROSS_PRINCIPAL = nvl(GROSS_PRINCIPAL,0)+nvl(NEW_BASE_REF_AMOUNT,0),
1736 HCE_GROSS_PRINCIPAL =nvl(HCE_GROSS_PRINCIPAL,0)+nvl(NEW_HCE_BASE_REF_AMOUNT,0),
1737 WEIGHTED_AVG_PRINCIPAL = nvl(WEIGHTED_AVG_PRINCIPAL,0)
1738 +round(nvl(NEW_BASE_REF_AMOUNT,0)*
1739 nvl(NEW_TRANSACTION_RATE,0)/v_100,l_fac),
1740 AVG_INTEREST_RATE = decode((nvl(GROSS_PRINCIPAL,0) - nvl(NEW_BASE_REF_AMOUNT,0)),0,null,
1741 abs((nvl(INTEREST,0) - nvl(NEW_INTEREST,0))/
1742 (nvl(GROSS_PRINCIPAL,0) - nvl(NEW_BASE_REF_AMOUNT,0)))) * 36500,
1743 -- bug 2345708
1744 BASE_WEIGHTED_AVG_PRINCIPAL = nvl(BASE_WEIGHTED_AVG_PRINCIPAL,0)
1745 +round(nvl(NEW_BASE_REF_AMOUNT,0)*nvl(NEW_BASE_RATE,0)/v_100,l_fac),
1746 AVG_BASE_RATE = abs(decode(nvl(GROSS_PRINCIPAL,0)+nvl(NEW_BASE_REF_AMOUNT,0),0,null,
1747 decode(nvl(WEIGHTED_AVG_PRINCIPAL,0)
1748 +round(nvl(NEW_BASE_REF_AMOUNT,0)*
1749 nvl(NEW_TRANSACTION_RATE,0)/v_100,l_fac),
1750 0,null,
1751 round(v_100*(nvl(BASE_WEIGHTED_AVG_PRINCIPAL,0)
1752 +round(nvl(NEW_BASE_REF_AMOUNT,0)*nvl(NEW_BASE_RATE,0)/v_100,l_fac))/
1753 (nvl(GROSS_PRINCIPAL,0)+nvl(NEW_BASE_REF_AMOUNT,0)),l_fac)))),
1754 INTEREST = nvl(INTEREST,0) + nvl(NEW_INTEREST,0),
1755 HCE_INTEREST = nvl(HCE_INTEREST,0) + nvl(NEW_HCE_INTEREST,0)
1756 where rowid=l_rowid;
1757 end if;
1758 else
1759 close CHK_LOCK_ROWS;
1760 -- insert new row
1761 insert into XTR_COST_OF_FUNDS
1762 (as_at_date,company_code,deal_type,
1763 deal_subtype,party_code,portfolio_code,product_type,
1764 currency,currency_combination,contra_ccy,
1765 account_no,created_on,
1766 gross_principal,hce_gross_principal,
1767 weighted_avg_principal,avg_interest_rate,interest,hce_interest,
1768 base_weighted_avg_principal,avg_base_rate,gross_base_amount,
1769 gross_contra_trans_amount, gross_contra_spot_amount)
1770 values(
1771 NEW_AS_AT_DATE,
1772 NEW_COMPANY_CODE,
1773 NEW_DEAL_TYPE,
1774 NEW_DEAL_SUBTYPE,
1775 NEW_CPARTY_CODE,
1776 NEW_PORTFOLIO_CODE,
1777 NEW_PRODUCT_TYPE,
1778 NEW_CURRENCY,
1779 NEW_CURRENCY_COMBINATION,
1780 NEW_CONTRA_CCY,
1781 NEW_ACCOUNT_NO,
1782 sysdate, -- created_on
1783 NEW_BASE_REF_AMOUNT, -- gross_principal
1784 NEW_HCE_BASE_REF_AMOUNT, -- hce_gross_principal
1785 round(nvl(NEW_BASE_REF_AMOUNT,0)*nvl(NEW_TRANSACTION_RATE,0)/v_100,l_fac), -- weightedavgprin
1786 NEW_TRANSACTION_RATE, -- avg_interest_rate
1787 decode(NEW_DEAL_TYPE, 'FX', 0, NEW_INTEREST), -- interest
1788 decode(NEW_DEAL_TYPE, 'FX', 0, NEW_HCE_INTEREST), -- hce_interest
1789 round(nvl(NEW_BASE_REF_AMOUNT,0)*nvl(NEW_BASE_RATE,0)/v_100,l_fac), -- base_w_avg_prin
1790 NEW_BASE_RATE, -- avg_base_rate
1791 decode(NEW_DEAL_TYPE, 'FX', abs(NEW_BASE_REF_AMOUNT), NULL), -- gross_base_amount
1792 decode(NEW_DEAL_TYPE, 'FX', round(abs(nvl(NEW_BASE_REF_AMOUNT,0)
1793 *nvl(NEW_TRANSACTION_RATE,0)),l_fac), NULL), --gross_contra_trans
1794 decode(NEW_DEAL_TYPE, 'FX', round(abs(nvl(NEW_BASE_REF_AMOUNT,0)
1795 *nvl(NEW_BASE_RATE,0)),l_fac), NULL) --gross_contra_spot
1796 );
1797 end if;
1798 end if; /* Not IG deal_type */
1799 /**************/
1800 /* DELETE */
1801 /**************/
1802 elsif P_ACTION = 'DELETE' then
1803 l_currency :=OLD_CURRENCY;
1804 open get_rounding_factor;
1805 fetch get_rounding_factor into l_hce_rate,l_fac;
1809 v_100 :=1;
1806 close get_rounding_factor;
1807
1808 if OLD_DEAL_TYPE = 'FX' then
1810 l_fac :=4;
1811 else
1812 v_100 :=100;
1813 end if;
1814
1815 Open CHK_LOCK_ROWS(
1816 OLD_AS_AT_DATE ,
1817 OLD_COMPANY_CODE,
1818 OLD_CPARTY_CODE,
1819 OLD_DEAL_TYPE,
1820 OLD_DEAL_SUBTYPE,
1821 OLD_PRODUCT_TYPE,
1822 OLD_PORTFOLIO_CODE,
1823 OLD_CURRENCY,
1824 OLD_CONTRA_CCY,
1825 OLD_CURRENCY_COMBINATION,
1826 OLD_ACCOUNT_NO
1827 );
1828 fetch CHK_LOCK_ROWS into l_rowid,l_gross;
1829 if CHK_LOCK_ROWS%FOUND then
1830 close CHK_LOCK_ROWS;
1831 if OLD_DEAL_TYPE <> 'FX' and nvl(l_gross,0) = nvl(OLD_BASE_REF_AMOUNT,0) then
1832 delete from XTR_COST_OF_FUNDS
1833 where rowid=l_rowid;
1834 else
1835 if OLD_DEAL_TYPE = 'FX' then
1836 update XTR_COST_OF_FUNDS
1837 set GROSS_PRINCIPAL = nvl(GROSS_PRINCIPAL,0)-nvl(OLD_BASE_REF_AMOUNT,0),
1838 HCE_GROSS_PRINCIPAL =nvl(HCE_GROSS_PRINCIPAL,0)-nvl(OLD_HCE_BASE_REF_AMOUNT,0),
1839 WEIGHTED_AVG_PRINCIPAL = nvl(WEIGHTED_AVG_PRINCIPAL,0)
1840 -round(nvl(OLD_BASE_REF_AMOUNT,0)*
1841 nvl(OLD_TRANSACTION_RATE,0)/v_100,l_fac),
1842 GROSS_BASE_AMOUNT = nvl(GROSS_BASE_AMOUNT,0)-abs(nvl(OLD_BASE_REF_AMOUNT,0)),
1843 GROSS_CONTRA_TRANS_AMOUNT = nvl(GROSS_CONTRA_TRANS_AMOUNT,0)-
1844 abs(nvl(OLD_BASE_REF_AMOUNT,0))*nvl(OLD_TRANSACTION_RATE,0),
1845 GROSS_CONTRA_SPOT_AMOUNT = nvl(GROSS_CONTRA_SPOT_AMOUNT,0)-
1846 abs(nvl(OLD_BASE_REF_AMOUNT,0))*nvl(OLD_BASE_RATE,0),
1847 AVG_INTEREST_RATE = decode(nvl(GROSS_BASE_AMOUNT,0)-abs(nvl(OLD_BASE_REF_AMOUNT,0)),0,NULL,
1848 round((nvl(GROSS_CONTRA_TRANS_AMOUNT,0)-
1849 abs(nvl(OLD_BASE_REF_AMOUNT,0))*nvl(OLD_TRANSACTION_RATE,0))/
1850 (nvl(GROSS_BASE_AMOUNT,0)-abs(nvl(OLD_BASE_REF_AMOUNT,0))),l_fac)),
1851 AVG_BASE_RATE = decode(nvl(GROSS_BASE_AMOUNT,0)-abs(nvl(OLD_BASE_REF_AMOUNT,0)),0,NULL,
1852 round((nvl(GROSS_CONTRA_SPOT_AMOUNT,0)-
1853 abs(nvl(OLD_BASE_REF_AMOUNT,0))*nvl(OLD_BASE_RATE,0))/
1854 (nvl(GROSS_BASE_AMOUNT,0)-abs(nvl(OLD_BASE_REF_AMOUNT,0))),l_fac)),
1855 BASE_WEIGHTED_AVG_PRINCIPAL = nvl(BASE_WEIGHTED_AVG_PRINCIPAL,0)
1856 -round(nvl(OLD_BASE_REF_AMOUNT,0)*nvl(OLD_BASE_RATE,0)/v_100,l_fac),
1857 INTEREST = 0,
1858 HCE_INTEREST = 0
1859 where rowid=l_rowid;
1860 else -- deal type TMM, RTMM, NI, BOND, CA, IG
1861 update XTR_COST_OF_FUNDS
1862 set GROSS_PRINCIPAL = nvl(GROSS_PRINCIPAL,0)-nvl(OLD_BASE_REF_AMOUNT,0),
1863 HCE_GROSS_PRINCIPAL =nvl(HCE_GROSS_PRINCIPAL,0)-nvl(OLD_HCE_BASE_REF_AMOUNT,0),
1864 WEIGHTED_AVG_PRINCIPAL = nvl(WEIGHTED_AVG_PRINCIPAL,0)
1865 -round(nvl(OLD_BASE_REF_AMOUNT,0)*
1866 nvl(OLD_TRANSACTION_RATE,0)/v_100,l_fac),
1867 AVG_INTEREST_RATE = decode((nvl(INTEREST,0) - nvl(NEW_INTEREST,0)), 0, null,
1868 abs((nvl(INTEREST,0) - nvl(NEW_INTEREST,0))/
1869 (nvl(GROSS_PRINCIPAL,0) - nvl(NEW_BASE_REF_AMOUNT,0)))),
1870 BASE_WEIGHTED_AVG_PRINCIPAL = nvl(BASE_WEIGHTED_AVG_PRINCIPAL,0)
1871 -round(nvl(OLD_BASE_REF_AMOUNT,0)*nvl(OLD_BASE_RATE,0)/v_100,l_fac),
1872 AVG_BASE_RATE = abs(decode(nvl(GROSS_PRINCIPAL,0)-nvl(OLD_BASE_REF_AMOUNT,0),0,null,
1873 decode(nvl(WEIGHTED_AVG_PRINCIPAL,0)
1874 -round(nvl(OLD_BASE_REF_AMOUNT,0)*
1875 nvl(OLD_TRANSACTION_RATE,0)/v_100,l_fac),0,null,
1876 round(v_100*(nvl(BASE_WEIGHTED_AVG_PRINCIPAL,0)
1877 -round(nvl(OLD_BASE_REF_AMOUNT,0)*
1878 nvl(OLD_BASE_RATE,0)/v_100,l_fac))/
1879 (nvl(GROSS_PRINCIPAL,0)-nvl(OLD_BASE_REF_AMOUNT,0)),l_fac)))),
1880 INTEREST = nvl(INTEREST,0) - nvl(OLD_INTEREST,0),
1881 HCE_INTEREST = nvl(HCE_INTEREST,0) - nvl(OLD_HCE_INTEREST,0)
1882 where rowid=l_rowid;
1883 end if;
1884 end if;
1885 else
1886 close CHK_LOCK_ROWS;
1887 -- insert new row
1888 insert into XTR_COST_OF_FUNDS
1889 (as_at_date,company_code,deal_type,
1890 deal_subtype,party_code,portfolio_code,product_type,
1891 currency,currency_combination,contra_ccy,
1892 account_no,created_on,
1893 gross_principal,hce_gross_principal,
1894 weighted_avg_principal,avg_interest_rate,interest,hce_interest,
1895 base_weighted_avg_principal,avg_base_rate,gross_base_amount,
1896 gross_contra_trans_amount, gross_contra_spot_amount)
1897 values(
1898 OLD_AS_AT_DATE,
1899 OLD_COMPANY_CODE,
1900 OLD_DEAL_TYPE,
1901 OLD_DEAL_SUBTYPE,
1902 OLD_CPARTY_CODE,
1903 OLD_PORTFOLIO_CODE,
1904 OLD_PRODUCT_TYPE,
1905 OLD_CURRENCY,
1906 OLD_CURRENCY_COMBINATION,
1907 OLD_CONTRA_CCY,
1908 OLD_ACCOUNT_NO,
1909 sysdate,
1913 OLD_TRANSACTION_RATE,
1910 OLD_BASE_REF_AMOUNT,
1911 OLD_HCE_BASE_REF_AMOUNT,
1912 0-round(nvl(OLD_BASE_REF_AMOUNT,0)*nvl(OLD_TRANSACTION_RATE,0)/v_100,l_fac),
1914 decode(OLD_DEAL_TYPE, 'FX', 0, OLD_INTEREST),
1915 decode(OLD_DEAL_TYPE, 'FX', 0, OLD_HCE_INTEREST),
1916 0-round(nvl(OLD_BASE_REF_AMOUNT,0)*nvl(OLD_BASE_RATE,0)/v_100,l_fac),
1917 OLD_BASE_RATE,
1918 decode(OLD_DEAL_TYPE, 'FX', abs(OLD_BASE_REF_AMOUNT), NULL), -- gross_base_amount
1919 decode(OLD_DEAL_TYPE, 'FX', round(abs(nvl(OLD_BASE_REF_AMOUNT,0)
1920 *nvl(OLD_TRANSACTION_RATE,0)),l_fac), NULL), --gross_contra_trans
1921 decode(OLD_DEAL_TYPE, 'FX', round(abs(nvl(OLD_BASE_REF_AMOUNT,0)
1922 *nvl(OLD_BASE_RATE,0)),l_fac), NULL) --gross_contra_spot
1923 );
1924 end if;
1925
1926 /**************/
1927 /* UPDATE */
1928 /**************/
1929 elsif P_ACTION='UPDATE' then
1930 if (NEW_DEAL_TYPE<>'IG') then /* IG handled by maintain_position_history, see comments for maintain_cof_ig */
1931
1932 l_currency :=NEW_CURRENCY;
1933 open get_rounding_factor;
1934 fetch get_rounding_factor into l_hce_rate,l_fac;
1935 close get_rounding_factor;
1936
1937 if NEW_DEAL_TYPE = 'FX' then
1938 v_100 :=1;
1939 l_fac :=4;
1940 else
1941 v_100 :=100;
1942 end if;
1943
1944 L_INTEREST :=NULL;
1945 L_INTEREST_HCE :=NULL;
1946
1947 Open CHK_LOCK_ROWS(
1948 NEW_AS_AT_DATE ,
1949 NEW_COMPANY_CODE,
1950 NEW_CPARTY_CODE,
1951 NEW_DEAL_TYPE,
1952 NEW_DEAL_SUBTYPE,
1953 NEW_PRODUCT_TYPE,
1954 NEW_PORTFOLIO_CODE,
1955 NEW_CURRENCY,
1956 NEW_CONTRA_CCY,
1957 NEW_CURRENCY_COMBINATION,
1958 NEW_ACCOUNT_NO
1959 );
1960 fetch CHK_LOCK_ROWS into l_rowid,l_gross;
1961 if CHK_LOCK_ROWS%FOUND then
1962 close CHK_LOCK_ROWS;
1963 if NEW_DEAL_TYPE = 'FX' then
1964 update XTR_COST_OF_FUNDS
1965 set GROSS_PRINCIPAL = nvl(GROSS_PRINCIPAL,0)+nvl(NEW_BASE_REF_AMOUNT,0),
1966 HCE_GROSS_PRINCIPAL =nvl(HCE_GROSS_PRINCIPAL,0)+nvl(NEW_HCE_BASE_REF_AMOUNT,0),
1967 WEIGHTED_AVG_PRINCIPAL = nvl(WEIGHTED_AVG_PRINCIPAL,0)
1968 +round(nvl(NEW_BASE_REF_AMOUNT,0)*
1969 nvl(NEW_TRANSACTION_RATE,0)/v_100,l_fac),
1970 GROSS_BASE_AMOUNT = nvl(GROSS_BASE_AMOUNT,0)+abs(nvl(NEW_BASE_REF_AMOUNT,0)),
1971 GROSS_CONTRA_TRANS_AMOUNT = nvl(GROSS_CONTRA_TRANS_AMOUNT,0)+
1972 abs(nvl(NEW_BASE_REF_AMOUNT,0))*nvl(NEW_TRANSACTION_RATE,0),
1973 GROSS_CONTRA_SPOT_AMOUNT = nvl(GROSS_CONTRA_SPOT_AMOUNT,0)+
1974 abs(nvl(NEW_BASE_REF_AMOUNT,0))*nvl(NEW_BASE_RATE,0),
1975 AVG_INTEREST_RATE = decode(nvl(GROSS_BASE_AMOUNT,0)+abs(nvl(NEW_BASE_REF_AMOUNT,0)),0,NULL,
1976 round((nvl(GROSS_CONTRA_TRANS_AMOUNT,0)+
1977 abs(nvl(NEW_BASE_REF_AMOUNT,0))*nvl(NEW_TRANSACTION_RATE,0))/
1978 (nvl(GROSS_BASE_AMOUNT,0)+abs(nvl(NEW_BASE_REF_AMOUNT,0))),l_fac)),
1979 AVG_BASE_RATE = decode(nvl(GROSS_BASE_AMOUNT,0)+abs(nvl(NEW_BASE_REF_AMOUNT,0)),0,NULL,
1980 round((nvl(GROSS_CONTRA_SPOT_AMOUNT,0)+
1981 abs(nvl(NEW_BASE_REF_AMOUNT,0))*nvl(NEW_BASE_RATE,0))/
1982 (nvl(GROSS_BASE_AMOUNT,0)+abs(nvl(NEW_BASE_REF_AMOUNT,0))),l_fac)),
1983 BASE_WEIGHTED_AVG_PRINCIPAL = nvl(BASE_WEIGHTED_AVG_PRINCIPAL,0)
1984 +round(nvl(NEW_BASE_REF_AMOUNT,0)*nvl(NEW_BASE_RATE,0)/v_100,l_fac),
1985 INTEREST = 0,
1986 HCE_INTEREST = 0
1987 where rowid=l_rowid;
1988 else -- deal type TMM, RTMM, NI, BOND, ONC, CA, IG
1989 update XTR_COST_OF_FUNDS
1990 set GROSS_PRINCIPAL = nvl(GROSS_PRINCIPAL,0)+nvl(NEW_BASE_REF_AMOUNT,0),
1991 HCE_GROSS_PRINCIPAL =nvl(HCE_GROSS_PRINCIPAL,0)+nvl(NEW_HCE_BASE_REF_AMOUNT,0),
1992 WEIGHTED_AVG_PRINCIPAL = nvl(WEIGHTED_AVG_PRINCIPAL,0)
1993 +round(nvl(NEW_BASE_REF_AMOUNT,0)*
1994 nvl(NEW_TRANSACTION_RATE,0)/v_100,l_fac),
1995 AVG_INTEREST_RATE = decode((nvl(GROSS_PRINCIPAL,0)-nvl(OLD_BASE_REF_AMOUNT,0)
1996 +nvl(NEW_BASE_REF_AMOUNT,0)), 0,null,
1997 abs((nvl(INTEREST,0)-nvl(OLD_INTEREST,0)+nvl(NEW_INTEREST,0))/
1998 (nvl(GROSS_PRINCIPAL,0)-nvl(OLD_BASE_REF_AMOUNT,0)+nvl(NEW_BASE_REF_AMOUNT,0))
1999 )) * 36500, -- bug 2345708
2000 BASE_WEIGHTED_AVG_PRINCIPAL = nvl(BASE_WEIGHTED_AVG_PRINCIPAL,0)
2001 +round(nvl(NEW_BASE_REF_AMOUNT,0)*nvl(NEW_BASE_RATE,0)/v_100,l_fac),
2002 AVG_BASE_RATE = abs(decode(nvl(GROSS_PRINCIPAL,0)+nvl(NEW_BASE_REF_AMOUNT,0),0,null,
2003 decode(nvl(WEIGHTED_AVG_PRINCIPAL,0)
2004 +round(nvl(NEW_BASE_REF_AMOUNT,0)*
2005 nvl(NEW_TRANSACTION_RATE,0)/v_100,l_fac),0,null,
2006 round(v_100*(nvl(BASE_WEIGHTED_AVG_PRINCIPAL,0)
2007 +round(nvl(NEW_BASE_REF_AMOUNT,0)*nvl(NEW_BASE_RATE,0)/v_100,l_fac))/
2011 where rowid=l_rowid;
2008 (nvl(GROSS_PRINCIPAL,0)+nvl(NEW_BASE_REF_AMOUNT,0)),l_fac)))),
2009 INTEREST = nvl(INTEREST,0) + nvl(NEW_INTEREST,0),
2010 HCE_INTEREST = nvl(HCE_INTEREST,0) + nvl(NEW_HCE_INTEREST,0)
2012 end if;
2013 else
2014 close CHK_LOCK_ROWS;
2015 -- insert new row
2016 insert into XTR_COST_OF_FUNDS
2017 (as_at_date,company_code,deal_type,
2018 deal_subtype,party_code,portfolio_code,product_type,
2019 currency,currency_combination,contra_ccy,
2020 account_no,created_on,
2021 gross_principal,hce_gross_principal,
2022 weighted_avg_principal,avg_interest_rate,interest,hce_interest,
2023 base_weighted_avg_principal,avg_base_rate, gross_base_amount,
2024 gross_contra_trans_amount, gross_contra_spot_amount)
2025 values(
2026 NEW_AS_AT_DATE,
2027 NEW_COMPANY_CODE,
2028 NEW_DEAL_TYPE,
2029 NEW_DEAL_SUBTYPE,
2030 NEW_CPARTY_CODE,
2031 NEW_PORTFOLIO_CODE,
2032 NEW_PRODUCT_TYPE,
2033 NEW_CURRENCY,
2034 NEW_CURRENCY_COMBINATION,
2035 NEW_CONTRA_CCY,
2036 NEW_ACCOUNT_NO,
2037 sysdate,
2038 NEW_BASE_REF_AMOUNT,
2039 NEW_HCE_BASE_REF_AMOUNT,
2040 round(nvl(NEW_BASE_REF_AMOUNT,0)*nvl(NEW_TRANSACTION_RATE,0)/v_100,l_fac),
2041 NEW_TRANSACTION_RATE,
2042 decode(NEW_DEAL_TYPE, 'FX', 0, NEW_INTEREST), -- interest
2043 decode(NEW_DEAL_TYPE, 'FX', 0, NEW_HCE_INTEREST), -- hce_interest
2044 round(nvl(NEW_BASE_REF_AMOUNT,0)*nvl(NEW_BASE_RATE,0)/v_100,l_fac),
2045 NEW_BASE_RATE,
2046 decode(NEW_DEAL_TYPE, 'FX', abs(NEW_BASE_REF_AMOUNT), NULL), -- gross_base_amount
2047 decode(NEW_DEAL_TYPE, 'FX', round(abs(nvl(NEW_BASE_REF_AMOUNT,0)
2048 *nvl(NEW_TRANSACTION_RATE,0)),l_fac), NULL), --gross_contra_trans
2049 decode(NEW_DEAL_TYPE, 'FX', round(abs(nvl(NEW_BASE_REF_AMOUNT,0)
2050 *nvl(NEW_BASE_RATE,0)),l_fac), NULL) --gross_contra_spot
2051 );
2052 end if;
2053
2054 l_currency :=OLD_CURRENCY;
2055 open get_rounding_factor;
2056 fetch get_rounding_factor into l_hce_rate,l_fac;
2057 close get_rounding_factor;
2058
2059 if NEW_DEAL_TYPE = 'FX' then
2060 v_100 :=1;
2061 l_fac :=4;
2062 else
2063 v_100 :=100;
2064 end if;
2065
2066 open CHK_LOCK_ROWS(
2067 OLD_AS_AT_DATE ,
2068 OLD_COMPANY_CODE,
2069 OLD_CPARTY_CODE,
2070 OLD_DEAL_TYPE,
2071 OLD_DEAL_SUBTYPE,
2072 OLD_PRODUCT_TYPE,
2073 OLD_PORTFOLIO_CODE,
2074 OLD_CURRENCY,
2075 OLD_CONTRA_CCY,
2076 OLD_CURRENCY_COMBINATION,
2077 OLD_ACCOUNT_NO
2078 );
2079 fetch CHK_LOCK_ROWS into l_rowid,l_gross;
2080 if CHK_LOCK_ROWS%FOUND then
2081 close CHK_LOCK_ROWS;
2082
2083 if OLD_DEAL_TYPE <> 'FX' and nvl(l_gross,0) = nvl(OLD_BASE_REF_AMOUNT,0) then
2084 delete from XTR_COST_OF_FUNDS
2085 where rowid=l_rowid;
2086 else
2087 If NEW_DEAL_TYPE = 'FX' then
2088 -- For FX, we add 3 columns to calculate correct avg_interest_rate and avg_base_rate
2089 update XTR_COST_OF_FUNDS
2090 set GROSS_PRINCIPAL = nvl(GROSS_PRINCIPAL,0)-nvl(OLD_BASE_REF_AMOUNT,0),
2091 HCE_GROSS_PRINCIPAL =nvl(HCE_GROSS_PRINCIPAL,0)-nvl(OLD_HCE_BASE_REF_AMOUNT,0),
2092 WEIGHTED_AVG_PRINCIPAL = nvl(WEIGHTED_AVG_PRINCIPAL,0)
2093 -round(nvl(OLD_BASE_REF_AMOUNT,0)*
2094 nvl(OLD_TRANSACTION_RATE,0)/v_100,l_fac),
2095 GROSS_BASE_AMOUNT = nvl(GROSS_BASE_AMOUNT,0)-abs(nvl(OLD_BASE_REF_AMOUNT,0)),
2096 GROSS_CONTRA_TRANS_AMOUNT = nvl(GROSS_CONTRA_TRANS_AMOUNT,0)-
2097 abs(nvl(OLD_BASE_REF_AMOUNT,0))*nvl(OLD_TRANSACTION_RATE,0),
2098 GROSS_CONTRA_SPOT_AMOUNT = nvl(GROSS_CONTRA_SPOT_AMOUNT,0)-
2099 abs(nvl(OLD_BASE_REF_AMOUNT,0))*nvl(OLD_BASE_RATE,0),
2100 AVG_INTEREST_RATE = decode(nvl(GROSS_BASE_AMOUNT,0)-abs(nvl(OLD_BASE_REF_AMOUNT,0)),0,NULL,
2101 round((nvl(GROSS_CONTRA_TRANS_AMOUNT,0)-
2102 abs(nvl(OLD_BASE_REF_AMOUNT,0))*nvl(OLD_TRANSACTION_RATE,0))/
2103 (nvl(GROSS_BASE_AMOUNT,0)-abs(nvl(OLD_BASE_REF_AMOUNT,0))),l_fac)),
2104 AVG_BASE_RATE = decode(nvl(GROSS_BASE_AMOUNT,0)-abs(nvl(OLD_BASE_REF_AMOUNT,0)),0,NULL,
2105 round((nvl(GROSS_CONTRA_SPOT_AMOUNT,0)-
2106 abs(nvl(OLD_BASE_REF_AMOUNT,0))*nvl(OLD_BASE_RATE,0))/
2107 (nvl(GROSS_BASE_AMOUNT,0)-abs(nvl(OLD_BASE_REF_AMOUNT,0))),l_fac)),
2108 BASE_WEIGHTED_AVG_PRINCIPAL = nvl(BASE_WEIGHTED_AVG_PRINCIPAL,0)
2109 -round(nvl(OLD_BASE_REF_AMOUNT,0)*nvl(OLD_BASE_RATE,0)/v_100,l_fac),
2110 INTEREST = 0,
2111 HCE_INTEREST = 0
2112 where rowid=l_rowid;
2113 else -- deal type TMM, RTMM, CA, IG, ONC, BOND, NI
2114 update XTR_COST_OF_FUNDS
2118 -round(nvl(OLD_BASE_REF_AMOUNT,0)*
2115 set GROSS_PRINCIPAL = nvl(GROSS_PRINCIPAL,0)-nvl(OLD_BASE_REF_AMOUNT,0),
2116 HCE_GROSS_PRINCIPAL =nvl(HCE_GROSS_PRINCIPAL,0)-nvl(OLD_HCE_BASE_REF_AMOUNT,0),
2117 WEIGHTED_AVG_PRINCIPAL = nvl(WEIGHTED_AVG_PRINCIPAL,0)
2119 nvl(OLD_TRANSACTION_RATE,0)/v_100,l_fac),
2120 AVG_INTEREST_RATE =decode((nvl(GROSS_PRINCIPAL,0)-nvl(OLD_BASE_REF_AMOUNT,0)),0,null,
2121 abs((nvl(INTEREST,0)-nvl(OLD_INTEREST,0))/
2122 (nvl(GROSS_PRINCIPAL,0)-nvl(OLD_BASE_REF_AMOUNT,0)))) * 36500
2123 , -- bug 2345708
2124 BASE_WEIGHTED_AVG_PRINCIPAL = nvl(BASE_WEIGHTED_AVG_PRINCIPAL,0)
2125 -round(nvl(OLD_BASE_REF_AMOUNT,0)*nvl(OLD_BASE_RATE,0)/v_100,l_fac),
2126 AVG_BASE_RATE = abs(decode(nvl(GROSS_PRINCIPAL,0)-nvl(OLD_BASE_REF_AMOUNT,0),0,null,
2127 decode(nvl(WEIGHTED_AVG_PRINCIPAL,0)
2128 -round(nvl(OLD_BASE_REF_AMOUNT,0)*
2129 nvl(OLD_TRANSACTION_RATE,0)/v_100,l_fac),0,null,
2130 round(v_100*(nvl(BASE_WEIGHTED_AVG_PRINCIPAL,0)
2131 -round(nvl(OLD_BASE_REF_AMOUNT,0)*nvl(OLD_BASE_RATE,0)/v_100,l_fac))/
2132 (nvl(GROSS_PRINCIPAL,0)-nvl(OLD_BASE_REF_AMOUNT,0)),l_fac)))),
2133 INTEREST = nvl(INTEREST,0) - nvl(OLD_INTEREST,0),
2134 HCE_INTEREST = nvl(HCE_INTEREST,0) - nvl(OLD_HCE_INTEREST,0)
2135 where rowid=l_rowid;
2136 end if;
2137 end if;
2138 else
2139 close CHK_LOCK_ROWS;
2140 -- insert new row
2141 insert into XTR_COST_OF_FUNDS
2142 (as_at_date,company_code,deal_type,
2143 deal_subtype,party_code,portfolio_code,product_type,
2144 currency,currency_combination,contra_ccy,
2145 account_no,created_on,
2146 gross_principal,hce_gross_principal,
2147 weighted_avg_principal,avg_interest_rate,interest,hce_interest,
2148 base_weighted_avg_principal,avg_base_rate, gross_base_amount,
2149 gross_contra_trans_amount, gross_contra_spot_amount)
2150 values(
2151 OLD_AS_AT_DATE,
2152 OLD_COMPANY_CODE,
2153 OLD_DEAL_TYPE,
2154 OLD_DEAL_SUBTYPE,
2155 OLD_CPARTY_CODE,
2156 OLD_PORTFOLIO_CODE,
2157 OLD_PRODUCT_TYPE,
2158 OLD_CURRENCY,
2159 OLD_CURRENCY_COMBINATION,
2160 OLD_CONTRA_CCY,
2161 OLD_ACCOUNT_NO,
2162 sysdate,
2163 OLD_BASE_REF_AMOUNT,
2164 OLD_HCE_BASE_REF_AMOUNT,
2165 0-round(nvl(OLD_BASE_REF_AMOUNT,0)*nvl(OLD_TRANSACTION_RATE,0)/v_100,l_fac),
2166 OLD_TRANSACTION_RATE,
2167 decode(OLD_DEAL_TYPE, 'FX', 0, 0-OLD_INTEREST), -- interest
2168 decode(OLD_DEAL_TYPE, 'FX', 0, 0-OLD_HCE_INTEREST), --hce_interest
2169 0-round(nvl(OLD_BASE_REF_AMOUNT,0)*nvl(OLD_BASE_RATE,0)/v_100,l_fac),
2170 OLD_BASE_RATE,
2171 decode(OLD_DEAL_TYPE, 'FX', abs(OLD_BASE_REF_AMOUNT), NULL), -- gross_base_amount
2172 decode(OLD_DEAL_TYPE, 'FX', round(abs(nvl(OLD_BASE_REF_AMOUNT,0)
2173 *nvl(OLD_TRANSACTION_RATE,0)),l_fac), NULL), --gross_contra_trans
2174 decode(OLD_DEAL_TYPE, 'FX', round(abs(nvl(OLD_BASE_REF_AMOUNT,0)
2175 *nvl(OLD_BASE_RATE,0)),l_fac), NULL) --gross_contra_spot
2176 );
2177 end if;
2178 end if; /* Not IG deal type */
2179 end if;
2180
2181 exception
2182 when app_exceptions.RECORD_LOCK_EXCEPTION then
2183 if CHK_LOCK_ROWS%ISOPEN then
2184 close CHK_LOCK_ROWS;
2185 end if;
2186 raise app_exceptions.RECORD_LOCK_EXCEPTION;
2187 end MAINTAIN_COST_OF_FUND;
2188
2189
2190 PROCEDURE SNAPSHOT_COST_OF_FUNDS(errbuf OUT NOCOPY VARCHAR2,
2191 retcode OUT NOCOPY NUMBER) as
2192 --
2193 l_run_date date := sysdate;
2194 l_date date;
2195 --
2196 L_COMPANY_CODE VARCHAR2(7);
2197 L_CURRENCY VARCHAR2(15);
2198 L_DEAL_SUBTYPE VARCHAR2(7);
2199 L_PRODUCT_TYPE VARCHAR2(10);
2200 L_PORTFOLIO_CODE VARCHAR2(10);
2201 L_CPARTY_CODE VARCHAR2(7);
2202 L_AMOUNT NUMBER;
2203 L_CONTRA_CCY VARCHAR2(15);
2204 L_CURRENCY_COMBINATION VARCHAR2(31);
2205 L_TRANSACTION_RATE NUMBER;
2206 L_YEAR_CALC_TYPE VARCHAR2(15);
2207 L_ACCOUNT_NO VARCHAR2(50);
2208 L_CALC_BASIS VARCHAR2(15);
2209 L_YEAR_BASIS NUMBER;
2210 L_DEAL_NUMBER NUMBER;
2211 L_TRANSACTION_NUMBER NUMBER;
2212 L_CALC_TYPE VARCHAR2(15);
2213 L_TOTAL_RESALE NUMBER := NULL;
2214 L_START_DATE DATE;
2215 -- Added the variable for Interest Override feature
2216 L_FIRST_TRANS_FLAG VARCHAR2(1):= NULL;
2217 L_DAY_COUNT_TYPE VARCHAR2(1):= NULL;
2218 L_cross_ref_start_date DATE;
2219 L_ERRBUF VARCHAR2(50);
2220 L_RETCODE NUMBER;
2221 --
2222
2223 /*************************************/
2224 /* Find FX, BOND deal information */
2225 /*************************************/
2226 cursor get_deals is
2227 /*******/
2228 /* FX */
2229 /*******/
2230 select a.deal_no deal_number,
2231 1 transaction_number,
2232 a.status_code status_code,
2233 a.company_code company_code,
2234 a.cparty_code cparty_code,
2235 a.deal_type deal_type,
2236 a.deal_subtype deal_subtype,
2237 a.currency currency,
2238 a.currency_buy currency_buy,
2239 a.currency_sell currency_sell,
2240 a.product_type,
2241 a.portfolio_code,
2242 'ACTUAL/ACTUAL' year_calc_type,
2243 a.year_basis,
2244 a.interest_rate interest_rate,
2245 a.transaction_rate,
2246 a.base_rate base_rate,
2247 a.calc_basis,
2248 a.start_date,
2249 a.value_date,
2250 a.option_commencement,
2251 a.expiry_date,
2252 a.maturity_date,
2253 a.maturity_account_no account_no,
2254 a.buy_amount base_amount,
2255 a.sell_amount second_amount,
2256 a.maturity_amount,
2257 a.start_amount,
2258 -- Added for Interest Override feature
2259 -- But FX is always populate null value to day_count_type
2260 a.day_count_type
2261 from XTR_DEALS a
2262 where a.deal_type ='FX'
2263 and a.status_code <> 'CANCELLED'
2264 and a.deal_date <= l_date
2265 and nvl(a.start_date,l_date+1) > l_date
2266 and value_date >l_date
2267 Union all
2268 /*********/
2269 /* BOND */
2270 /*********/
2271 select a.deal_no deal_number,
2272 1 transaction_number,
2273 a.status_code status_code,
2274 a.company_code,
2275 a.cparty_code,
2276 a.deal_type,
2277 a.deal_subtype,
2278 a.currency,
2279 a.currency_buy,
2280 a.currency_sell,
2281 a.product_type,
2282 a.portfolio_code,
2283 a.year_calc_type,
2284 a.year_basis,
2285 a.interest_rate interest_rate,
2286 a.transaction_rate,
2287 a.base_rate base_rate,
2288 a.calc_basis,
2289 a.start_date,
2290 a.value_date,
2291 a.option_commencement,
2292 a.expiry_date,
2293 a.maturity_date,
2294 a.maturity_account_no account_no,
2295 a.start_amount base_amount,
2296 a.settle_amount second_amount,
2297 a.maturity_amount,
2298 a.start_amount,
2299 -- Added for Interest Override feature
2300 a.day_count_type
2301 from XTR_DEALS a
2302 where a.deal_type ='BOND'
2303 and a.deal_subtype in ('BUY', 'ISSUE')
2304 and a.status_code ='CURRENT'
2305 and a.start_date <=l_date
2306 and a.maturity_date > l_date
2307 Union All
2308 /****************/
2309 /* Bond resale */
2310 /****************/
2311 select a.deal_no deal_number,
2312 1 transaction_number,
2313 a.status_code status_code,
2314 a.company_code,
2315 a.cparty_code,
2316 a.deal_type,
2317 a.deal_subtype,
2318 a.currency,
2319 a.currency_buy,
2320 a.currency_sell,
2321 a.product_type,
2322 a.portfolio_code,
2323 a.year_calc_type,
2324 a.year_basis,
2325 a.interest_rate interest_rate,
2326 a.transaction_rate,
2327 a.base_rate base_rate,
2328 a.calc_basis,
2329 a.start_date,
2330 a.value_date,
2331 a.option_commencement,
2332 a.expiry_date,
2333 a.maturity_date,
2334 a.maturity_account_no account_no,
2335 a.start_amount base_amount,
2336 a.settle_amount second_amount,
2337 a.maturity_amount,
2338 a.start_amount,
2339 -- Added for the Interest Override feature
2340 a.day_count_type
2341 from XTR_DEALS a
2342 where a.deal_type ='BOND'
2343 and a.deal_subtype in ('BUY', 'ISSUE') -- Bond Repurchase Project - 2879858.
2344 and a.status_code ='CURRENT'
2345 and a.deal_no in (select distinct d.deal_no
2346 from XTR_DEALS d,
2347 XTR_BOND_ALLOC_DETAILS R
2348 where d.deal_no = r.deal_no
2349 and d.maturity_date <= l_date
2350 and nvl(r.avg_rate_last_processed, r.cross_ref_start_date)
2351 < d.maturity_date);
2352
2353 /*************************************/
2354 /* Find CA deal information */
2355 /*************************************/
2356 cursor get_ca is
2357 select a.party_code,
2358 a.currency,
2359 a.account_number,
2360 a.bank_code,
2361 a.portfolio_code,
2362 a.year_calc_type
2363 from xtr_bank_accounts a,
2364 xtr_party_info b
2365 where a.party_code=b.party_code
2366 and b.party_type='C'
2367 and nvl(a.setoff_account_yn,'N') ='N'
2368 and nvl(a.opening_balance,0) <> 0 ;
2369
2370 cursor get_ca_row is
2371 select nvl(balance_adjustment,0)+nvl(statement_balance,0) base_amount,
2372 interest_rate, balance_date, day_count_type -- Added day_count_type for Interest Override
2373 from xtr_bank_balances
2374 where company_code = L_COMPANY_CODE
2375 and account_number = L_ACCOUNT_NO
2376 and balance_date <= l_date
2377 order by balance_date desc;
2378
2379
2380 /*************************************/
2381 /* Find IG deal information */
2382 /*************************************/
2383 cursor get_ig is
2384 select a.deal_number, -- bug 2345708
2385 a.amount_date,
2386 a.company_code,
2387 a.currency,
2388 a.account_no,
2389 a.limit_party,
2390 a.portfolio_code,
2391 a.product_type
2392 from xtr_mirror_dda_limit_row a
2393 where a.deal_type = 'IG';
2394
2395 cursor get_ig_row is
2396 select nvl(balance_out,0) base_amount,
2397 interest_rate,
2398 day_count_type -- Added for Interest Override feature
2399 from xtr_intergroup_transfers
2400 where company_code = L_COMPANY_CODE
2401 and party_code= L_CPARTY_CODE
2402 and currency = L_CURRENCY
2403 and deal_number = L_DEAL_NUMBER
2404 and transfer_date <= l_date
2405 order by transfer_date desc,transaction_number desc;
2406
2407
2408 /**********************************************/
2409 /* Find TMM, RTMM, ONC, NI deal information */
2410 /**********************************************/
2411 cursor get_rt is
2412 select a.deal_number,
2413 a.transaction_number,
2414 a.status_code,
2415 a.company_code,
2416 a.cparty_code,
2417 a.deal_type,
2418 a.deal_subtype,
2419 a.currency,
2420 a.product_type,
2421 a.portfolio_code,
2422 a.year_calc_type,
2423 a.interest_rate,
2424 a.start_date,
2425 a.maturity_date,
2426 a.balance_out_bf base_amount,
2427 decode(nvl(a.principal_action,'DECRSE'),'INCRSE',
2428 nvl(a.PRINCIPAL_ADJUST,0),0-nvl(a.PRINCIPAL_ADJUST,0)) second_amount,
2429 a.interest,
2430 -- Added for Interest Override feature
2431 a.first_transaction_flag,
2432 d.day_count_type
2433 from XTR_ROLLOVER_TRANSACTIONS a,
2434 -- Added for Interest Override feature
2435 XTR_DEALS d
2436 where a.deal_type in('TMM','RTMM')
2437 and a.start_date <=l_date
2438 and a.maturity_date >l_date
2439 and a.status_code='CURRENT'
2440 -- Added for Interest Override feature
2441 and d.deal_no = a.deal_number
2442 Union all
2443 select a.deal_number,
2444 a.transaction_number,
2445 a.status_code,
2446 a.company_code,
2447 a.cparty_code,
2448 a.deal_type,
2449 a.deal_subtype,
2450 a.currency,
2451 a.product_type,
2452 a.portfolio_code,
2453 a.year_calc_type,
2454 a.interest_rate,
2455 a.start_date,
2456 a.maturity_date,
2457 a.balance_out base_amount,
2458 0 second_amount,
2459 a.interest,
2460 -- Added for Interest Override feature
2461 a.first_transaction_flag,
2462 d.day_count_type
2463 from XTR_ROLLOVER_TRANSACTIONS a,
2464 -- Added for Interest Override feature
2465 XTR_DEALS d
2466 where a.deal_type ='ONC'
2467 and a.start_date <=l_date
2468 and nvl(a.maturity_date,l_date+1) >l_date
2469 and a.interest_rate is not null
2470 and a.status_code='CURRENT'
2471 -- Added for Interest Override feature
2472 and d.deal_no = a.deal_number
2473 Union all
2474 select a.deal_number,
2475 a.transaction_number,
2476 a.status_code,
2477 a.company_code,
2478 a.cparty_code,
2479 a.deal_type,
2480 a.deal_subtype,
2481 a.currency,
2482 a.product_type,
2483 a.portfolio_code,
2484 a.year_calc_type,
2485 a.interest_rate,
2486 a.start_date,
2487 nvl(a.ni_reneg_date,a.maturity_date) maturity_date,
2488 nvl(a.balance_out,0)-nvl(a.interest,0) base_amount,
2489 0 second_amount,
2490 a.interest,
2491 -- Added for Interest Override feature
2492 a.first_transaction_flag,
2493 d.day_count_type
2494 from XTR_ROLLOVER_TRANSACTIONS a,
2495 -- Added for Interest Override feature
2496 XTR_DEALS d
2497 where a.deal_type ='NI'
2498 and a.deal_subtype in ('BUY', 'ISSUE')
2499 and a.start_date <=l_date
2500 and nvl(a.ni_reneg_date,a.maturity_date) >l_date
2501 -- Added for Interest Override feature
2502 and d.deal_no = a.deal_number;
2503
2504 D get_deals%rowtype;
2505 C get_ca%rowtype;
2506 G get_ig%rowtype;
2507 R get_rt%rowtype;
2508
2509 cursor GET_COM(P_CURRENCY_BUY varchar2,P_CURRENCY_SELL varchar2) is
2510 select CURRENCY_FIRST||'/'||CURRENCY_SECOND
2511 from XTR_BUY_SELL_COMBINATIONS
2512 where (CURRENCY_BUY = P_CURRENCY_BUY and CURRENCY_SELL = P_CURRENCY_SELL)
2513 or (CURRENCY_BUY = P_CURRENCY_SELL and CURRENCY_SELL = P_CURRENCY_BUY);
2514 --
2515 cursor get_ig_year_calc is
2516 select ig_year_basis
2517 from XTR_MASTER_CURRENCIES
2518 where currency = L_CURRENCY;
2519 --
2520 cursor get_calc_basis is
2521 select calc_basis
2522 from xtr_deals
2523 where deal_no=L_DEAL_NUMBER;
2524
2525 cursor get_bond_type is
2526 select b.year_calc_type, b.calc_type
2527 from XTR_DEALS A, XTR_BOND_ISSUES B
2528 where a.bond_issue = b.bond_issue_code
2529 and a.deal_no = D.DEAL_NUMBER;
2530
2531 cursor chk_date_exits is
2532 select 1
2533 from XTR_COST_OF_FUNDS
2534 where as_at_date = l_date;
2535
2536 cursor bond_total_resale is
2537 select sum(face_value), max(cross_ref_start_date)
2538 from xtr_bond_alloc_details
2539 where deal_no = D.DEAL_NUMBER
2540 and CROSS_REF_START_DATE <= l_date;
2541
2542
2543 BEGIN
2544 -- check time
2545 --if to_number(to_char(l_run_date,'HH24')) <6 then
2546 l_date :=trunc(sysdate)-1;
2547 --else
2548 -- l_date :=trunc(sysdate);
2549 --end if;
2550
2551 /**************/
2552 /* FX, BOND */
2553 /**************/
2554 open get_deals;
2555 LOOP
2556 fetch get_deals into D;
2557 exit WHEN get_deals%NOTFOUND;
2558 L_CURRENCY :=D.CURRENCY;
2559 L_AMOUNT :=D.BASE_AMOUNT;
2560 L_CONTRA_CCY :=NULL;
2561 L_CURRENCY_COMBINATION :=NULL;
2562 L_TRANSACTION_RATE :=D.INTEREST_RATE;
2563 L_ACCOUNT_NO :=NULL;
2564 L_YEAR_CALC_TYPE :=nvl(D.YEAR_CALC_TYPE,'ACTUAL/ACTUAL');
2565
2566 If D.DEAL_TYPE = 'FX' then
2567 L_TRANSACTION_RATE :=D.TRANSACTION_RATE;
2568
2569 open GET_COM(D.CURRENCY_BUY,D.CURRENCY_SELL);
2570 fetch GET_COM into L_CURRENCY_COMBINATION;
2571 close GET_COM;
2572
2573 if D.CURRENCY_BUY =substr(L_CURRENCY_COMBINATION,1,3) then
2574 L_AMOUNT :=D.BASE_AMOUNT;
2575 L_CURRENCY :=D.CURRENCY_BUY;
2576 L_CONTRA_CCY :=D.CURRENCY_SELL;
2577 else
2578 L_AMOUNT :=0-D.SECOND_AMOUNT;
2579 L_CURRENCY :=D.CURRENCY_SELL;
2580 L_CONTRA_CCY :=D.CURRENCY_BUY;
2581 end if;
2582 elsif D.DEAL_TYPE ='BOND' then
2583 OPEN get_bond_type;
2584 FETCH get_bond_type into l_year_calc_type, l_calc_type;
2585 CLOSE get_bond_type;
2586
2587 OPEN bond_total_resale;
2588 FETCH bond_total_resale into l_total_resale,l_cross_ref_start_date;
2589 if l_total_resale = D.MATURITY_AMOUNT then
2590 Update XTR_BOND_ALLOC_DETAILS
2591 Set avg_rate_last_processed = L_DATE
2592 where deal_no = d.deal_number -- bug 4470022 Added where clause
2593 and cross_ref_start_date = l_cross_ref_start_date;
2594 close bond_total_resale;
2595 else
2596 CLOSE bond_total_resale;
2597 end if;
2598
2599 -- Added for Interest Override feature
2600 IF D.DAY_COUNT_TYPE ='B' THEN
2601 l_first_trans_flag :='Y';
2602 END IF;
2603 --
2604
2605 end if;
2606 if nvl(L_AMOUNT,0) <> 0 or (nvl(l_total_resale,0) <> D.MATURITY_AMOUNT) then
2607 XTR_COF_P.SNAPSHOT_POSITION_HISTORY(
2608 P_AS_AT_DATE => L_DATE,
2609 P_DEAL_NUMBER => D.DEAL_NUMBER,
2610 P_TRANSACTION_NUMBER => D.TRANSACTION_NUMBER,
2611 P_COMPANY_CODE => D.COMPANY_CODE,
2612 P_CURRENCY => L_CURRENCY,
2613 P_DEAL_TYPE => D.DEAl_TYPE,
2614 P_DEAL_SUBTYPE => D.DEAL_SUBTYPE,
2615 P_PRODUCT_TYPE => D.PRODUCT_TYPE,
2616 P_PORTFOLIO_CODE => D.PORTFOLIO_CODE,
2617 P_CPARTY_CODE => D.CPARTY_CODE,
2618 P_CONTRA_CCY => L_CONTRA_CCY,
2619 P_CURRENCY_COMBINATION => L_CURRENCY_COMBINATION,
2620 P_ACCOUNT_NO => L_ACCOUNT_NO,
2621 P_TRANSACTION_RATE => L_TRANSACTION_RATE,
2622 P_YEAR_CALC_TYPE => L_YEAR_CALC_TYPE,
2623 P_BASE_REF_AMOUNT => L_AMOUNT,
2624 P_BASE_RATE => D.BASE_RATE,
2625 P_STATUS_CODE => D.STATUS_CODE,
2626 P_START_DATE => D.START_DATE,
2627 P_MATURITY_DATE => D.MATURITY_DATE,
2628 P_INTEREST => NULL,
2629 P_MATURITY_AMOUNT => D.MATURITY_AMOUNT,
2630 P_START_AMOUNT => D.START_AMOUNT,
2631 P_CALC_TYPE => L_CALC_TYPE,
2632 P_CALC_BASIS => NULL,
2633 P_DAY_COUNT_TYPE => D.DAY_COUNT_TYPE, -- Added for Interest Override
2634 P_FIRST_TRANS_FLAG => L_FIRST_TRANS_FLAG -- Added for Interest Override
2635 );
2636 end if;
2637 END LOOP;
2638 close get_deals;
2639
2640 /**************/
2641 /* CA */
2642 /**************/
2643 L_CONTRA_CCY :=NULL;
2644 L_CURRENCY_COMBINATION :=NULL;
2645 L_PRODUCT_TYPE :=NULL;
2646 open get_ca;
2647 LOOP
2648 fetch get_ca into C;
2649 exit WHEN get_ca%NOTFOUND;
2650 L_COMPANY_CODE :=C.PARTY_CODE;
2651 L_CURRENCY :=C.CURRENCY;
2652 L_YEAR_CALC_TYPE :=nvl(C.YEAR_CALC_TYPE,'ACTUAL/ACTUAL');
2653 L_CPARTY_CODE :=C.BANK_CODE;
2654 L_PORTFOLIO_CODE :=C.PORTFOLIO_CODE;
2655 L_ACCOUNT_NO :=C.ACCOUNT_NUMBER;
2656
2657 L_AMOUNT :=0;
2658 L_TRANSACTION_RATE :=0;
2659 L_FIRST_TRANS_FLAG := NULL; -- Added for Intrest Override
2660
2661 open get_ca_row;
2662 fetch get_ca_row into L_AMOUNT,L_TRANSACTION_RATE, L_START_DATE,
2663 L_DAY_COUNT_TYPE; -- Added for Interest Override
2664 close get_ca_row;
2665
2666 if L_AMOUNT >0 then
2667 L_DEAL_SUBTYPE :='INVEST';
2668 else
2669 L_DEAL_SUBTYPE :='FUND';
2670 end if;
2671
2672 if nvl(L_AMOUNT,0) <> 0 then
2673 XTR_COF_P.SNAPSHOT_POSITION_HISTORY(
2674 P_AS_AT_DATE => L_DATE,
2675 P_DEAL_NUMBER => NULL,
2676 P_TRANSACTION_NUMBER => NULL,
2677 P_COMPANY_CODE => L_COMPANY_CODE,
2678 P_CURRENCY => L_CURRENCY,
2679 p_DEAL_TYPE => 'CA',
2680 P_DEAL_SUBTYPE => L_DEAL_SUBTYPE,
2681 P_PRODUCT_TYPE => L_PRODUCT_TYPE,
2682 P_PORTFOLIO_CODE => L_PORTFOLIO_CODE,
2683 P_CPARTY_CODE => L_CPARTY_CODE,
2684 P_CONTRA_CCY => L_CONTRA_CCY,
2685 P_CURRENCY_COMBINATION => L_CURRENCY_COMBINATION,
2686 P_ACCOUNT_NO => L_ACCOUNT_NO,
2687 P_TRANSACTION_RATE => L_TRANSACTION_RATE,
2688 P_YEAR_CALC_TYPE => L_YEAR_CALC_TYPE,
2689 P_BASE_REF_AMOUNT => L_AMOUNT,
2690 P_BASE_RATE => NULL,
2691 P_STATUS_CODE => 'CURRENT',
2692 P_START_DATE => L_START_DATE,
2693 P_MATURITY_DATE => NULL,
2694 P_INTEREST => NULL,
2695 P_MATURITY_AMOUNT => NULL,
2696 P_START_AMOUNT => NULL,
2697 P_CALC_BASIS => NULL,
2698 P_CALC_TYPE => NULL,
2699 P_DAY_COUNT_TYPE => L_DAY_COUNT_TYPE, -- Added for Interest Override
2700 P_FIRST_TRANS_FLAG => L_FIRST_TRANS_FLAG -- Added for Interest Override
2701 );
2702 end if;
2703 END LOOP;
2704 close get_ca;
2705
2706 /**************/
2707 /* IG */
2708 /**************/
2709 L_CONTRA_CCY :=NULL;
2710 L_CURRENCY_COMBINATION :=NULL;
2711 open get_ig;
2712 LOOP
2713 fetch get_ig into G;
2714 exit WHEN get_ig%NOTFOUND;
2715 L_DEAL_NUMBER := G.DEAL_NUMBER;
2716 L_START_DATE := G.AMOUNT_DATE;
2717 L_COMPANY_CODE :=G.COMPANY_CODE;
2718 L_CURRENCY :=G.CURRENCY;
2719 L_CPARTY_CODE :=G.LIMIT_PARTY;
2720 L_PORTFOLIO_CODE :=G.PORTFOLIO_CODE;
2721 L_PRODUCT_TYPE :=G.PRODUCT_TYPE;
2722 L_ACCOUNT_NO :=G.ACCOUNT_NO;
2723
2724 open get_ig_year_calc;
2725 fetch get_ig_year_calc into L_YEAR_CALC_TYPE;
2726 close get_ig_year_calc;
2727
2728 L_AMOUNT :=0;
2729 L_TRANSACTION_RATE :=0;
2730
2731 open get_ig_row;
2732 fetch get_ig_row into L_AMOUNT,L_TRANSACTION_RATE, L_DAY_COUNT_TYPE;
2733 close get_ig_row;
2734
2735 if L_AMOUNT >0 then
2736 L_DEAL_SUBTYPE :='INVEST';
2737 else
2738 L_DEAL_SUBTYPE :='FUND';
2739 end if;
2740
2741 if L_AMOUNT <>0 then
2742 XTR_COF_P.SNAPSHOT_POSITION_HISTORY(
2743 P_AS_AT_DATE => L_DATE,
2744 P_DEAL_NUMBER => L_DEAL_NUMBER,
2745 P_TRANSACTION_NUMBER => 1,
2746 P_COMPANY_CODE => L_COMPANY_CODE,
2747 P_CURRENCY => L_CURRENCY,
2748 p_DEAL_TYPE => 'IG',
2749 P_DEAL_SUBTYPE => L_DEAL_SUBTYPE,
2750 P_PRODUCT_TYPE => L_PRODUCT_TYPE,
2751 P_PORTFOLIO_CODE => L_PORTFOLIO_CODE,
2752 P_CPARTY_CODE => L_CPARTY_CODE,
2753 P_CONTRA_CCY => L_CONTRA_CCY,
2754 P_CURRENCY_COMBINATION => L_CURRENCY_COMBINATION,
2755 P_ACCOUNT_NO => L_ACCOUNT_NO,
2756 P_TRANSACTION_RATE => L_TRANSACTION_RATE,
2757 P_YEAR_CALC_TYPE => L_YEAR_CALC_TYPE,
2758 P_BASE_REF_AMOUNT => L_AMOUNT,
2759 P_BASE_RATE => NULL,
2760 P_STATUS_CODE => 'CURRENT',
2761 P_START_DATE => L_START_DATE,
2762 P_MATURITY_DATE => NULL,
2763 P_INTEREST => NULL,
2767 P_CALC_TYPE => NULL,
2764 P_MATURITY_AMOUNT => NULL,
2765 P_START_AMOUNT => NULL,
2766 P_CALC_BASIS => NULL,
2768 P_DAY_COUNT_TYPE => L_DAY_COUNT_TYPE, -- Added for Interest Override
2769 P_FIRST_TRANS_FLAG => L_FIRST_TRANS_FLAG -- Added for Interest Override
2770 );
2771 end if;
2772 END LOOP;
2773 close get_ig;
2774
2775 /***********************/
2776 /* TMM, RTMM, NI, ONC */
2777 /***********************/
2778 open get_rt;
2779 LOOP
2780 fetch get_rt into R;
2781 exit WHEN get_rt%NOTFOUND;
2782 L_CURRENCY :=R.CURRENCY;
2783 L_AMOUNT :=nvl(R.BASE_AMOUNT,0)+nvl(R.SECOND_AMOUNT,0);
2784 L_CONTRA_CCY :=NULL;
2785 L_CURRENCY_COMBINATION :=NULL;
2786 L_TRANSACTION_RATE :=R.INTEREST_RATE;
2787 L_ACCOUNT_NO :=NULL;
2788
2789 -- Added for the Interest Override feature
2790 -- Set FIRST_TRANS_FLAG
2791 IF R.DEAL_TYPE IN ('TMM','RTMM') THEN
2792 IF r.day_count_type ='B' AND r.first_transaction_flag=1 THEN
2793 L_FIRST_TRANS_FLAG :='Y';
2794 ELSE
2795 L_FIRST_TRANS_FLAG := NULL;
2796 END IF;
2797 ELSIF R.DEAL_TYPE ='NI' THEN
2798 L_FIRST_TRANS_FLAG := 'Y';
2799 ELSIF R.DEAL_TYPE ='ONC' THEN
2800 L_FIRST_TRANS_FLAG := R.FIRST_TRANSACTION_FLAG;
2801 ELSE
2802 L_FIRST_TRANS_FLAG := NULL;
2803 END IF;
2804 --
2805
2806 OPEN get_calc_basis;
2807 FETCH get_calc_basis into L_CALC_BASIS;
2808 CLOSE get_calc_basis;
2809
2810 if nvl(L_AMOUNT,0) <> 0 then
2811 XTR_COF_P.SNAPSHOT_POSITION_HISTORY(
2812 P_AS_AT_DATE => L_DATE,
2813 P_DEAL_NUMBER => R.DEAL_NUMBER,
2814 P_TRANSACTION_NUMBER => R.TRANSACTION_NUMBER,
2815 P_COMPANY_CODE => R.COMPANY_CODE,
2816 P_CURRENCY => L_CURRENCY,
2817 P_DEAL_TYPE => R.DEAl_TYPE,
2818 P_DEAL_SUBTYPE => R.DEAL_SUBTYPE,
2819 P_PRODUCT_TYPE => R.PRODUCT_TYPE,
2820 P_PORTFOLIO_CODE => R.PORTFOLIO_CODE,
2821 P_CPARTY_CODE => R.CPARTY_CODE,
2822 P_CONTRA_CCY => L_CONTRA_CCY,
2823 P_CURRENCY_COMBINATION => L_CURRENCY_COMBINATION,
2824 P_ACCOUNT_NO => L_ACCOUNT_NO,
2825 P_TRANSACTION_RATE => L_TRANSACTION_RATE,
2826 P_YEAR_CALC_TYPE => L_YEAR_CALC_TYPE,
2827 P_BASE_REF_AMOUNT => L_AMOUNT,
2828 P_BASE_RATE => NULL,
2829 P_STATUS_CODE => R.STATUS_CODE,
2830 P_START_DATE => R.START_DATE,
2831 P_MATURITY_DATE => R.MATURITY_DATE,
2832 P_INTEREST => R.INTEREST,
2833 P_MATURITY_AMOUNT => NULL,
2834 P_START_AMOUNT => NULL,
2835 P_CALC_BASIS => L_CALC_BASIS,
2836 P_CALC_TYPE => NULL,
2837 P_DAY_COUNT_TYPE => R.DAY_COUNT_TYPE, -- Added for Interest Override
2838 P_FIRST_TRANS_FLAG => L_FIRST_TRANS_FLAG -- Added for Interest Override
2839 );
2840 end if;
2841 END LOOP;
2842 close get_rt;
2843
2844 xtr_stock_position_p.snapshot_stk_cost_of_funds(l_errbuf, l_retcode); -- bug 4466775 nyse enh
2845
2846
2847 end SNAPSHOT_COST_OF_FUNDS;
2848
2849 --
2850 /*******************************************************************************/
2851 /* This procedure is to calculate BOND yield rate based on ACTUAL/365. It is */
2852 /* called by XTR_COF_P.SNAPSHOT_COST_OF_FUND and trigger XTR_AI_DEALS_T */
2853 /* and XTR_AU_DEALS_T. It inputs the following information: */
2854 /* p_deal_no : XTR_DEALS.deal_no */
2855 /* p_maturity_amt : XTR_DEALS.maturity_amount */
2856 /* p_consideration: XTR_DEALS.start_amount */
2857 /* p_coupon_rate : XTR_DEALS.coupon_rate */
2858 /* p_start_date : XTR_DEALS.start_date */
2859 /* p_maturity_date: XTR_DEALS.maturity_date */
2860 /* p_year_basis : year basis (365 or 360) */
2861 /* p_disc_prem : discount or premium */
2862 /* p_yield_rate : output rate as simple, annualized rate */
2863 /*******************************************************************************/
2864 PROCEDURE CALCULATE_BOND_RATE(
2865 p_deal_no IN NUMBER,
2866 p_maturity_amt IN NUMBER,
2867 p_consideration IN NUMBER,
2868 p_coupon_rate IN NUMBER,
2869 p_start_date IN DATE,
2870 p_maturity_date IN DATE,
2871 p_calc_type IN VARCHAR2,
2872 p_daily_int OUT NOCOPY NUMBER,
2873 p_yield_rate OUT NOCOPY NUMBER,
2874 p_day_count_type IN VARCHAR2 -- Added for Interest Override
2875 )IS
2876
2877 l_year_basis_in NUMBER;
2878 l_year_basis_out NUMBER;
2879 l_no_of_days_in NUMBER;
2880 l_no_of_days_out NUMBER;
2881 l_coupon NUMBER;
2882 l_rate1 NUMBER;
2883 -- Added for Interest Override feature
2884 l_num_of_days NUMBER;
2885 l_interest NUMBER;
2886 l_original_amount NUMBER;
2887 l_orig_coupon NUMBER;
2888 l_resale_count NUMBER;
2889
2890 BEGIN
2891 /*------------------------------------------------*/
2892 /* Step 1: Calculate total coupon interest amount */
2893 /*------------------------------------------------*/
2897 -- From xtr_rollover_transactions
2894 -- Changed for Interest Override
2895 -- Select SUM(orig_coupon_amount)
2896 -- Into l_coupon
2898 -- Where deal_number = p_deal_no;
2899 Select SUM(orig_coupon_amount),SUM(interest),SUM(original_amount)
2900 Into l_orig_coupon,l_interest,l_original_amount
2901 From xtr_rollover_transactions
2902 Where deal_number = p_deal_no;
2903
2904 SELECT COUNT(deal_no)
2905 INTO l_resale_count
2906 FROM xtr_bond_alloc_details
2907 WHERE deal_no=p_deal_no;
2908
2909 IF l_resale_count>0 AND l_interest <> l_original_amount THEN
2910 l_coupon := l_interest;
2911 ELSE
2912 l_coupon := l_orig_coupon;
2913 END IF;
2914 --
2915 /*----------------------------------*/
2916 /* Step 2: Calculate Daily Interest */
2917 /*----------------------------------*/
2918
2919 -- Added for the Interest Override feature
2920 IF p_day_count_type ='B' THEN
2921 l_num_of_days := p_maturity_date - p_start_date +1;
2922 ELSE
2923 l_num_of_days := p_maturity_date - p_start_date;
2924 END IF;
2925
2926 if p_calc_type = 'ZERO COUPON' then
2927 -- Changed to l_num_of_days for Interest Override feature
2928 p_daily_int := (p_maturity_amt - p_consideration) /l_num_of_days;
2929 else
2930 p_daily_int := (l_coupon + p_maturity_amt - p_consideration)
2931 / l_num_of_days;
2932 end if;
2933
2934 /*-------------------------------------------------------------*/
2935 /* Step 3: Calculate a simple, annual rate based on ACTUAL/365 */
2936 /*-------------------------------------------------------------*/
2937 p_yield_rate := (p_daily_int * 365) * 100 / p_consideration;
2938
2939 END CALCULATE_BOND_RATE;
2940
2941 /******************************************************************************/
2942 /* Upload_Avg_Rates_Results can be called from a form via Find Window or
2943 * from a report with the necessary parameters to query the Cost of Funds table
2944 * and insert that summary information in the "temporary" XTR_AVG_RATES_RESULTS
2945 * table. This table is actually always in the database but data that is
2946 * inserted is never committed so the results can be seen during a session
2947 * and erased once the session is over. This way no maintenance is necessary
2948 * for this table.
2949 * This procedure takes an unique batch id so the caller can distinguish one
2950 * result set from another. Currently, the XTR_EXPOSURE_TRANS_S sequence is
2951 * used to generate the id so it is advisable to use it to keep the uniqueness.
2952 */
2953 PROCEDURE UPLOAD_AVG_RATES_RESULTS(
2954 p_batch_id IN NUMBER,
2955 p_group_type IN VARCHAR2,
2956 p_date_from IN DATE,
2957 p_date_to IN DATE,
2958 p_company_code IN VARCHAR2,
2959 p_deal_type IN VARCHAR2,
2960 p_currency IN VARCHAR2,
2961 p_contra_ccy IN VARCHAR2,
2962 p_cparty_code IN VARCHAR2,
2963 p_product_type IN VARCHAR2,
2964 p_portfolio_code IN VARCHAR2,
2965 p_group_by_month IN VARCHAR2,
2966 p_group_by_year IN VARCHAR2,
2967 p_group_by_company IN VARCHAR2,
2968 p_group_by_deal IN VARCHAR2,
2969 p_group_by_currency IN VARCHAR2,
2970 p_group_by_cparty IN VARCHAR2,
2971 p_group_by_product IN VARCHAR2,
2972 p_group_by_portfolio IN VARCHAR2) IS
2973 --
2974 v_date_format VARCHAR2(15);
2975 v_from_date DATE;
2976 v_to_date DATE;
2977 v_p_i VARCHAR2(1);
2978 v_period VARCHAR2(15);
2979 v_company VARCHAR2(7);
2980 v_user_deal VARCHAR2(7);
2981 v_deal VARCHAR2(7);
2982 v_product VARCHAR2(10);
2983 v_portfolio VARCHAR2(7);
2984 v_cparty VARCHAR2(7);
2985 v_currency VARCHAR2(15);
2986 v_currency_combination VARCHAR2(31);
2987 v_principal NUMBER;
2988 v_interest NUMBER;
2989 v_avg_contract_rate NUMBER;
2990 v_avg_spot_rate NUMBER;
2991 v_min_rate NUMBER;
2992 v_max_rate NUMBER;
2993 v_num_deals NUMBER;
2994 -- Obtain COF summary for group type of INVEST and company is specified
2995 CURSOR c_get_invest_comp IS
2996 SELECT 'P', -- p_i,
2997 Decode(v_date_format, 'MM/DD/YYYY', Null, to_char(as_at_date, v_date_format)), -- as_at_date,
2998 Decode(p_group_by_company, 'Y', company_code, Null), -- company_code,
2999 Decode(p_group_by_deal, 'Y', deal_type, Null), -- deal_type,
3000 Decode(p_group_by_currency, 'Y', currency, Null), -- currency,
3001 Decode(p_group_by_product, 'Y', product_type, Null), -- product_type,
3002 Decode(p_group_by_portfolio, 'Y', portfolio_code, Null), -- portfolio_code,
3003 Decode(p_group_by_cparty, 'Y', party_code, Null), -- party_code,
3004 Sum(Decode(p_currency, Null, hce_gross_principal, gross_principal))/(p_date_to - p_date_from + 1) principal,
3005 -- Sum(interest) interest, RV Bug# 1291156 06-APR-2001
3006 Sum(Decode(p_currency,Null,hce_interest,interest)) interest,
3007 decode(Sum(gross_principal),0,0,
3008 (sum(interest) * 36500 /(sum(gross_principal)/(p_date_to-p_date_from+1))
3009 /(p_date_to - p_date_from +1))) avg_rate,
3010 Sum(no_of_deals) num_deals
3011 FROM xtr_cost_of_funds_v
3012 WHERE as_at_date BETWEEN p_date_from AND p_date_to
3013 AND company_code = p_company_code
3014 AND deal_type LIKE Nvl(p_deal_type,'%')
3015 AND ( (deal_type = 'NI' AND deal_subtype = 'BUY')
3016 OR (deal_type = 'BOND' AND deal_subtype = 'BUY')
3017 OR (deal_type IN ('ONC', 'CA', 'IG', 'TMM', 'RTMM') AND deal_subtype = p_group_type) )
3018 AND currency LIKE Nvl(p_currency,'%')
3019 AND Nvl(party_code,'%') LIKE Nvl(p_cparty_code,'%')
3020 AND Nvl(portfolio_code,'%') LIKE Nvl(p_portfolio_code,'%')
3021 AND Nvl(product_type,'%') LIKE Nvl(p_product_type,'%')
3022 GROUP BY 'P',
3023 Decode(v_date_format, 'MM/DD/YYYY', Null, to_char(as_at_date, v_date_format)),
3027 Decode(p_group_by_product, 'Y', product_type, Null),
3024 Decode(p_group_by_company, 'Y', company_code, Null),
3025 Decode(p_group_by_deal, 'Y', deal_type, Null),
3026 Decode(p_group_by_currency, 'Y', currency, Null),
3028 Decode(p_group_by_portfolio, 'Y', portfolio_code, Null),
3029 Decode(p_group_by_cparty, 'Y', party_code, Null);
3030
3031
3032 ----
3033 -- Obtain COF summary for group type of INVEST and company is NOT specified
3034 CURSOR c_get_invest_all IS
3035 SELECT 'P', -- p_i,
3036 Decode(v_date_format, 'MM/DD/YYYY', Null, to_char(as_at_date, v_date_format)), -- as_at_date,
3037 Decode(p_group_by_company, 'Y', company_code, Null), -- company_code,
3038 Decode(p_group_by_deal, 'Y', deal_type, Null), -- deal_type,
3039 Decode(p_group_by_currency, 'Y', currency, Null), -- currency,
3040 Decode(p_group_by_product, 'Y', product_type, Null), -- product_type,
3041 Decode(p_group_by_portfolio, 'Y', portfolio_code, Null), -- portfolio_code,
3042 Decode(p_group_by_cparty, 'Y', party_code, Null), -- party_code,
3043 Sum(Decode(p_currency, Null, hce_gross_principal, gross_principal))/(p_date_to - p_date_from + 1) principal,
3044 -- Sum(interest) interest, RV Bug# 1291156 06-APR-2001
3045 Sum(Decode(p_currency,Null,hce_interest,interest)) interest,
3046 decode(Sum(gross_principal),0,0,
3047 (sum(interest) * 36500 /(sum(gross_principal)/(p_date_to-p_date_from+1))
3048 /(p_date_to - p_date_from +1))) avg_rate,
3049 Sum(no_of_deals) num_deals
3050 FROM xtr_cost_of_funds_v
3051 WHERE as_at_date BETWEEN p_date_from AND p_date_to
3052 AND company_code IN (SELECT p.party_code
3053 FROM xtr_parties_v p
3054 WHERE p.party_type = 'C')
3055 AND deal_type LIKE Nvl(p_deal_type,'%')
3056 AND ( (deal_type = 'NI' AND deal_subtype = 'BUY')
3057 OR (deal_type = 'BOND' AND deal_subtype = 'BUY')
3058 OR (deal_type IN ('ONC', 'CA', 'IG', 'TMM', 'RTMM') AND deal_subtype = p_group_type) )
3059 AND currency LIKE Nvl(p_currency,'%')
3060 AND Nvl(party_code,'%') LIKE Nvl(p_cparty_code,'%')
3061 AND Nvl(portfolio_code,'%') LIKE Nvl(p_portfolio_code,'%')
3062 AND Nvl(product_type,'%') LIKE Nvl(p_product_type,'%')
3063 GROUP BY 'P',
3064 Decode(v_date_format, 'MM/DD/YYYY', Null, to_char(as_at_date, v_date_format)),
3065 Decode(p_group_by_company, 'Y', company_code, Null),
3066 Decode(p_group_by_deal, 'Y', deal_type, Null),
3067 Decode(p_group_by_currency, 'Y', currency, Null),
3068 Decode(p_group_by_product, 'Y', product_type, Null),
3069 Decode(p_group_by_portfolio, 'Y', portfolio_code, Null),
3070 Decode(p_group_by_cparty, 'Y', party_code, Null);
3071
3072 ----
3073 -- Obtain COF summary for group type of FUND and company is specified
3074 CURSOR c_get_fund_comp IS
3075 SELECT 'P', -- p_i,
3076 Decode(v_date_format, 'MM/DD/YYYY', Null, to_char(as_at_date, v_date_format)), -- as_at_date,
3077 Decode(p_group_by_company, 'Y', company_code, Null), -- company_code,
3078 Decode(p_group_by_deal, 'Y', deal_type, Null), -- deal_type,
3079 Decode(p_group_by_currency, 'Y', currency, Null), -- currency,
3080 Decode(p_group_by_product, 'Y', product_type, Null), -- product_type,
3081 Decode(p_group_by_portfolio, 'Y', portfolio_code, Null), -- portfolio_code,
3082 Decode(p_group_by_cparty, 'Y', party_code, Null), -- party_code,
3083 Sum(Decode(p_currency, Null, hce_gross_principal, gross_principal))/(p_date_to - p_date_from + 1) principal,
3084 -- Sum(interest) interest, RV Bug# 1291156 06-APR-2001
3085 Sum(Decode(p_currency,Null,hce_interest,interest)) interest,
3086 decode(Sum(gross_principal),0,0,
3087 (sum(interest) * 36500 /(sum(gross_principal)/(p_date_to-p_date_from+1))
3088 /(p_date_to - p_date_from +1))) avg_rate,
3089 Sum(no_of_deals) num_deals
3090 FROM xtr_cost_of_funds_v
3091 WHERE as_at_date BETWEEN p_date_from AND p_date_to
3092 AND company_code = p_company_code
3093 AND deal_type LIKE Nvl(p_deal_type,'%')
3094 AND ( (deal_type = 'NI' AND deal_subtype = 'ISSUE')
3095 OR (deal_type = 'BOND' AND deal_subtype = 'ISSUE')
3096 OR (deal_type IN ('ONC', 'CA', 'IG', 'TMM', 'RTMM') AND deal_subtype = p_group_type) )
3097 AND currency LIKE Nvl(p_currency,'%')
3098 AND Nvl(party_code,'%') LIKE Nvl(p_cparty_code,'%')
3099 AND Nvl(portfolio_code,'%') LIKE Nvl(p_portfolio_code,'%')
3100 AND Nvl(product_type,'%') LIKE Nvl(p_product_type,'%')
3101 GROUP BY 'P',
3102 Decode(v_date_format, 'MM/DD/YYYY', Null, to_char(as_at_date, v_date_format)),
3103 Decode(p_group_by_company, 'Y', company_code, Null),
3104 Decode(p_group_by_deal, 'Y', deal_type, Null),
3105 Decode(p_group_by_currency, 'Y', currency, Null),
3106 Decode(p_group_by_product, 'Y', product_type, Null),
3107 Decode(p_group_by_portfolio, 'Y', portfolio_code, Null),
3108 Decode(p_group_by_cparty, 'Y', party_code, Null);
3109
3110 ----
3111 -- Obtain COF summary for group type of FUND and company is NOT specified
3112 CURSOR c_get_fund_all IS
3113 SELECT 'P', -- p_i,
3114 Decode(v_date_format, 'MM/DD/YYYY', Null, to_char(as_at_date, v_date_format)), -- as_at_date,
3115 Decode(p_group_by_company, 'Y', company_code, Null), -- company_code,
3116 Decode(p_group_by_deal, 'Y', deal_type, Null), -- deal_type,
3117 Decode(p_group_by_currency, 'Y', currency, Null), -- currency,
3118 Decode(p_group_by_product, 'Y', product_type, Null), -- product_type,
3119 Decode(p_group_by_portfolio, 'Y', portfolio_code, Null), -- portfolio_code,
3120 Decode(p_group_by_cparty, 'Y', party_code, Null), -- party_code,
3121 Sum(Decode(p_currency, Null, hce_gross_principal, gross_principal))/(p_date_to - p_date_from + 1) principal,
3122 -- Sum(interest) interest, RV Bug# 1291156 06-APR-2001
3123 Sum(Decode(p_currency,Null,hce_interest,interest)) interest,
3124 decode(Sum(gross_principal),0,0,
3125 (sum(interest) * 36500 /(sum(gross_principal)/(p_date_to-p_date_from+1))
3126 /(p_date_to - p_date_from +1))) avg_rate,
3127 Sum(no_of_deals) num_deals
3128 FROM xtr_cost_of_funds_v
3129 WHERE as_at_date BETWEEN p_date_from AND p_date_to
3130 AND company_code IN (SELECT p.party_code
3131 FROM xtr_parties_v p
3132 WHERE p.party_type = 'C')
3133 AND deal_type LIKE Nvl(p_deal_type,'%')
3134 AND ( (deal_type = 'NI' AND deal_subtype = 'ISSUE')
3135 OR (deal_type = 'BOND' AND deal_subtype = 'ISSUE')
3136 OR (deal_type IN ('ONC', 'CA', 'IG', 'TMM', 'RTMM') AND deal_subtype = p_group_type) )
3137 AND currency LIKE Nvl(p_currency,'%')
3138 AND Nvl(party_code,'%') LIKE Nvl(p_cparty_code,'%')
3139 AND Nvl(portfolio_code,'%') LIKE Nvl(p_portfolio_code,'%')
3140 AND Nvl(product_type,'%') LIKE Nvl(p_product_type,'%')
3141 GROUP BY 'P',
3142 Decode(v_date_format, 'MM/DD/YYYY', Null, to_char(as_at_date, v_date_format)),
3143 Decode(p_group_by_company, 'Y', company_code, Null),
3144 Decode(p_group_by_deal, 'Y', deal_type, Null),
3145 Decode(p_group_by_currency, 'Y', currency, Null),
3146 Decode(p_group_by_product, 'Y', product_type, Null),
3147 Decode(p_group_by_portfolio, 'Y', portfolio_code, Null),
3148 Decode(p_group_by_cparty, 'Y', party_code, Null);
3149
3150 ----
3151 -- Obtain COF summary for group type of FX and company is specified
3152 CURSOR c_get_fx_comp IS
3153 SELECT 'P', -- p_i,
3154 Decode(v_date_format, 'MM/DD/YYYY', Null, to_char(as_at_date, v_date_format)), -- as_at_date,
3155 Decode(p_group_by_company, 'Y', company_code, Null), -- company_code,
3156 Decode(p_group_by_deal, 'Y', deal_type, Null), -- deal_type,
3157 currency_combination,
3158 Decode(p_group_by_product, 'Y', product_type, Null), -- product_type,
3159 Decode(p_group_by_portfolio, 'Y', portfolio_code, Null), -- portfolio_code,
3160 Decode(p_group_by_cparty, 'Y', party_code, Null), -- party_code,
3161 Sum(gross_principal)/((p_date_to-p_date_from)+1) principal,
3162 Sum(interest) interest,
3163 Decode(sum(gross_base_amount),0,0, Sum(gross_contra_trans_amount)/Sum(gross_base_amount)) avg_rate,
3164 Sum(no_of_deals) num_deals,
3165 Decode(Sum(gross_base_amount),0,0, Sum(gross_contra_spot_amount)/Sum(gross_base_amount)) avg_spot_rate
3166 FROM xtr_cost_of_funds_v
3167 WHERE as_at_date BETWEEN p_date_from AND p_date_to
3168 AND company_code = p_company_code
3169 AND deal_type LIKE Nvl(p_deal_type,'%')
3170 AND deal_type = 'FX'
3171 AND currency LIKE Nvl(p_currency,'%')
3172 AND Nvl(contra_ccy,'%') LIKE Nvl(p_contra_ccy,'%')
3173 AND Nvl(party_code,'%') LIKE Nvl(p_cparty_code,'%')
3174 AND Nvl(portfolio_code,'%') LIKE Nvl(p_portfolio_code,'%')
3175 AND Nvl(product_type,'%') LIKE Nvl(p_product_type,'%')
3176 GROUP BY 'P',
3177 Decode(v_date_format, 'MM/DD/YYYY', Null, to_char(as_at_date, v_date_format)),
3178 Decode(p_group_by_company, 'Y', company_code, Null),
3179 Decode(p_group_by_deal, 'Y', deal_type, Null),
3180 currency_combination,
3181 Decode(p_group_by_product, 'Y', product_type, Null),
3182 Decode(p_group_by_portfolio, 'Y', portfolio_code, Null),
3183 Decode(p_group_by_cparty, 'Y', party_code, Null);
3184
3185 ----
3186 -- Obtain COF summary for group type of FX and company is NOT specified
3187 CURSOR c_get_fx_all IS
3188 SELECT 'P', -- p_i,
3189 Decode(v_date_format, 'MM/DD/YYYY', Null, to_char(as_at_date, v_date_format)), -- as_at_date,
3190 Decode(p_group_by_company, 'Y', company_code, Null), -- company_code,
3191 Decode(p_group_by_deal, 'Y', deal_type, Null), -- deal_type,
3192 currency_combination,
3193 Decode(p_group_by_product, 'Y', product_type, Null), -- product_type,
3194 Decode(p_group_by_portfolio, 'Y', portfolio_code, Null), -- portfolio_code,
3195 Decode(p_group_by_cparty, 'Y', party_code, Null), -- party_code,
3196 Sum(gross_principal)/((p_date_to-p_date_from)+1) principal,
3197 Sum(interest) interest,
3198 Decode(sum(gross_base_amount),0,0, Sum(gross_contra_trans_amount)/Sum(gross_base_amount)) avg_rate,
3199 Sum(no_of_deals) num_deals,
3200 Decode(Sum(gross_base_amount),0,0, Sum(gross_contra_spot_amount)/Sum(gross_base_amount)) avg_spot_rate
3201 FROM xtr_cost_of_funds_v
3202 WHERE as_at_date BETWEEN p_date_from AND p_date_to
3203 AND company_code IN (SELECT p.party_code
3204 FROM xtr_parties_v p
3205 WHERE p.party_type = 'C')
3206 AND deal_type LIKE Nvl(p_deal_type,'%')
3207 AND deal_type = 'FX'
3208 AND currency LIKE Nvl(p_currency,'%')
3209 AND Nvl(contra_ccy,'%') LIKE Nvl(p_contra_ccy,'%')
3210 AND Nvl(party_code,'%') LIKE Nvl(p_cparty_code,'%')
3211 AND Nvl(portfolio_code,'%') LIKE Nvl(p_portfolio_code,'%')
3212 AND Nvl(product_type,'%') LIKE Nvl(p_product_type,'%')
3213 GROUP BY 'P',
3214 Decode(v_date_format, 'MM/DD/YYYY', Null, to_char(as_at_date, v_date_format)),
3215 Decode(p_group_by_company, 'Y', company_code, Null),
3219 Decode(p_group_by_portfolio, 'Y', portfolio_code, Null),
3216 Decode(p_group_by_deal, 'Y', deal_type, Null),
3217 currency_combination,
3218 Decode(p_group_by_product, 'Y', product_type, Null),
3220 Decode(p_group_by_cparty, 'Y', party_code, Null);
3221
3222 ----
3223 -- Find min and max trans rates for INVEST deals queried with the given criteria
3224 CURSOR c_get_minmax_rate_invest (p_from_date VARCHAR2, p_to_date VARCHAR2, p_p_i VARCHAR2) IS
3225 SELECT Min(transaction_rate), Max(transaction_rate)
3226 FROM xtr_position_history
3227 WHERE as_at_date BETWEEN p_from_date and p_to_date
3228 AND ((v_company IS NOT NULL AND company_code = v_company)
3229 OR (v_company IS NULL AND
3230 company_code IN (SELECT p.party_code
3231 FROM xtr_parties_v p
3232 WHERE p.party_type = 'C')))
3233 AND deal_type LIKE Nvl(v_deal, '%')
3234 AND (product_type LIKE Nvl(v_product, '%') or product_type is null)
3235 AND (portfolio_code LIKE Nvl(v_portfolio, '%') or portfolio_code is null)
3236 AND (cparty_code LIKE Nvl(v_cparty, '%') or cparty_code is null)
3237 AND ((p_p_i = 'P') AND ((deal_type = 'NI' AND deal_subtype = 'BUY')
3238 OR (deal_type = 'BOND' AND deal_subtype = 'BUY')
3239 OR ((deal_type IN ('ONC', 'CA', 'IG', 'TMM', 'RTMM')) AND (deal_subtype = p_group_type))));
3240
3241 --
3242 -- Find min and max trans rates for FUND deals queried with the given criteria
3243 CURSOR c_get_minmax_rate_fund (p_from_date VARCHAR2, p_to_date VARCHAR2, p_p_i VARCHAR2) IS
3244 SELECT Min(transaction_rate), Max(transaction_rate)
3245 FROM xtr_position_history
3246 WHERE as_at_date BETWEEN p_from_date and p_to_date
3247 AND ((v_company IS NOT NULL AND company_code = v_company)
3248 OR (v_company IS NULL AND
3249 company_code IN (SELECT p.party_code
3250 FROM xtr_parties_v p
3251 WHERE p.party_type = 'C')))
3252 AND deal_type LIKE Nvl(v_deal, '%')
3253 AND (product_type LIKE Nvl(v_product, '%') or product_type is null)
3254 AND (portfolio_code LIKE Nvl(v_portfolio, '%') or portfolio_code is null)
3255 AND (cparty_code LIKE Nvl(v_cparty, '%') or cparty_code is null)
3256 AND ((p_p_i = 'P') AND ((deal_type = 'NI' AND deal_subtype = 'ISSUE')
3257 OR (deal_type = 'BOND' AND deal_subtype = 'ISSUE')
3258 OR ((deal_type IN ('ONC', 'CA', 'IG', 'TMM', 'RTMM')) AND (deal_subtype = p_group_type))));
3259 --
3260 -- Find min and max trans rates for FX deals queried with the given criteria
3261 CURSOR c_get_minmax_rate_fx (p_from_date VARCHAR2, p_to_date VARCHAR2, p_p_i VARCHAR2) IS
3262 SELECT Min(transaction_rate), Max(transaction_rate)
3263 FROM xtr_position_history
3264 WHERE as_at_date BETWEEN p_from_date and p_to_date
3265 AND ((v_company IS NOT NULL AND company_code = v_company)
3266 OR (v_company IS NULL AND
3267 company_code IN (SELECT p.party_code
3268 FROM xtr_parties_v p
3269 WHERE p.party_type = 'C')))
3270 AND deal_type LIKE Nvl(v_deal, '%')
3271 AND (product_type LIKE Nvl(v_product, '%') or product_type is null)
3272 AND (portfolio_code LIKE Nvl(v_portfolio, '%') or portfolio_code is null)
3273 AND (cparty_code LIKE Nvl(v_cparty, '%') or cparty_code is null)
3274 AND (currency_combination LIKE Nvl(v_currency_combination, '%') or
3275 currency_combination is null)
3276 AND ((p_p_i = 'P') AND (deal_type = 'FX'));
3277 --
3278 BEGIN
3279 IF Nvl(p_group_by_month,'N') = 'Y' THEN
3280 v_date_format := 'MM/YYYY';
3281 ELSIF Nvl(p_group_by_year,'N') = 'Y' THEN
3282 v_date_format := 'YYYY';
3283 ELSE
3284 v_date_format := 'MM/DD/YYYY';
3285 END IF;
3286 -- Figure out which cursor to open
3287 IF p_company_code IS NULL THEN
3288 IF p_group_type = 'INVEST' THEN
3289 OPEN c_get_invest_all;
3290 ELSIF p_group_type = 'FUND' THEN
3291 OPEN c_get_fund_all;
3292 ELSE
3293 OPEN c_get_fx_all;
3294 END IF;
3295 ELSE
3296 IF p_group_type = 'INVEST' THEN
3297 OPEN c_get_invest_comp;
3298 ELSIF p_group_type = 'FUND' THEN
3299 OPEN c_get_fund_comp;
3300 ELSE
3301 OPEN c_get_fx_comp;
3302 END IF;
3303 END IF;
3304 LOOP
3305 IF p_company_code IS NULL THEN
3306 IF p_group_type = 'INVEST' THEN
3307 FETCH c_get_invest_all INTO v_p_i, v_period, v_company,
3308 v_deal, v_currency,
3309 v_product, v_portfolio, v_cparty, v_principal,
3310 v_interest, v_avg_contract_rate, v_num_deals;
3311 EXIT WHEN c_get_invest_all%NOTFOUND;
3312 ELSIF p_group_type = 'FUND' THEN
3313 FETCH c_get_fund_all INTO v_p_i, v_period, v_company,
3314 v_deal, v_currency,
3315 v_product, v_portfolio, v_cparty, v_principal,
3316 v_interest, v_avg_contract_rate, v_num_deals;
3317 EXIT WHEN c_get_fund_all%NOTFOUND;
3318 ELSE
3319 FETCH c_get_fx_all INTO v_p_i, v_period, v_company,
3320 v_deal, v_currency_combination,
3321 v_product, v_portfolio, v_cparty, v_principal,
3322 v_interest, v_avg_contract_rate, v_num_deals, v_avg_spot_rate;
3323 EXIT WHEN c_get_fx_all%NOTFOUND;
3324 END IF;
3325 ELSE
3326 IF p_group_type = 'INVEST' THEN
3327 FETCH c_get_invest_comp INTO v_p_i, v_period, v_company,
3328 v_deal, v_currency,
3329 v_product, v_portfolio, v_cparty, v_principal,
3330 v_interest, v_avg_contract_rate, v_num_deals;
3331 EXIT WHEN c_get_invest_comp%NOTFOUND;
3332 ELSIF p_group_type = 'FUND' THEN
3333 FETCH c_get_fund_comp INTO v_p_i, v_period, v_company,
3334 v_deal, v_currency,
3335 v_product, v_portfolio, v_cparty, v_principal,
3336 v_interest, v_avg_contract_rate, v_num_deals;
3337 EXIT WHEN c_get_fund_comp%NOTFOUND;
3338 ELSE
3339 FETCH c_get_fx_comp INTO v_p_i, v_period, v_company,
3340 v_deal, v_currency_combination,
3341 v_product, v_portfolio, v_cparty, v_principal,
3342 v_interest, v_avg_contract_rate, v_num_deals, v_avg_spot_rate;
3343 EXIT WHEN c_get_fx_comp%NOTFOUND;
3344 END IF;
3345 END IF;
3346 -- Find the date range
3347 -- Assume By Month is 'MM/YYYY'
3348 -- By Year is 'YYYY'
3349 IF v_period IS NOT NULL THEN
3350 IF v_period = To_Char(p_date_from, v_date_format) THEN
3351 v_from_date := p_date_from;
3352 ELSE
3353 IF Nvl(p_group_by_month, 'N') = 'Y' THEN
3354 v_from_date := to_date('01/'||v_period, 'DD/MM/YYYY');
3355 ELSE
3356 v_from_date := to_date('01/01'||v_period, 'DD/MM/YYYY');
3357 END IF;
3358 END IF;
3359 IF v_period = To_Char(p_date_to, v_date_format) THEN
3360 v_to_date := p_date_to;
3361 ELSE
3362 IF Nvl(p_group_by_month, 'N') = 'Y' THEN
3363 v_to_date := last_day(to_date(v_period, v_date_format));
3364 ELSE
3365 v_to_date := to_date('31/12'||v_period, 'DD/MM/YYYY');
3366 END IF;
3367 END IF;
3368 ELSE
3369 v_from_date := p_date_from;
3370 v_to_date := p_date_to;
3371 END IF;
3372
3373 -- get min/max transaction rate per row
3374 IF p_group_type = 'INVEST' THEN
3375 OPEN c_get_minmax_rate_invest(v_from_date, v_to_date, v_p_i);
3376 FETCH c_get_minmax_rate_invest INTO v_min_rate, v_max_rate;
3377 CLOSE c_get_minmax_rate_invest;
3378 ELSIF p_group_type = 'FUND' THEN
3379 OPEN c_get_minmax_rate_fund(v_from_date, v_to_date, v_p_i);
3380 FETCH c_get_minmax_rate_fund INTO v_min_rate, v_max_rate;
3381 CLOSE c_get_minmax_rate_fund;
3382 ELSE
3383 OPEN c_get_minmax_rate_fx(v_from_date, v_to_date, v_p_i);
3384 FETCH c_get_minmax_rate_fx INTO v_min_rate, v_max_rate;
3385 CLOSE c_get_minmax_rate_fx;
3386 END IF;
3387
3388 INSERT INTO xtr_avg_rates_results(unique_id, p_i, period, date_from, date_to, company_code, deal_type,
3389 product_type, portfolio_code, cparty_code, currency, currency_combination, principal,
3390 interest, average_contract_rate, average_spot_rate, minimum_rate, maximum_rate, num_deals)
3391 VALUES(
3392 p_batch_id, v_p_i, v_period,
3393 v_from_date, v_to_date, v_company,
3394 v_deal, v_product, v_portfolio, v_cparty,
3395 v_currency, v_currency_combination, v_principal,
3396 v_interest, v_avg_contract_rate, v_avg_spot_rate,
3397 v_min_rate, v_max_rate, v_num_deals);
3398 END LOOP;
3399
3400 -- Figure out which cursor to close
3401 IF p_company_code IS NULL THEN
3402 IF p_group_type = 'INVEST' THEN
3403 CLOSE c_get_invest_all;
3404 ELSIF p_group_type = 'FUND' THEN
3405 CLOSE c_get_fund_all;
3406 ELSE
3407 CLOSE c_get_fx_all;
3408 END IF;
3409 ELSE
3410 IF p_group_type = 'INVEST' THEN
3411 CLOSE c_get_invest_comp;
3412 ELSIF p_group_type = 'FUND' THEN
3413 CLOSE c_get_fund_comp;
3414 ELSE
3415 CLOSE c_get_fx_comp;
3416 END IF;
3417 END IF;
3418
3419 END UPLOAD_AVG_RATES_RESULTS;
3420
3421 end XTR_COF_P;