[Home] [Help]
PACKAGE BODY: APPS.XTR_EXP_SUMM_P
Source
1 PACKAGE BODY XTR_EXP_SUMM_P as
2 /* $Header: xtrexpob.pls 120.1 2005/06/29 06:25:54 badiredd ship $ */
3 ----------------------------------------------------------------------------------------------------------------
4 PROCEDURE CALC_HEDGE_DETAILS(ref_number IN NUMBER,
5 sel_ccy IN VARCHAR2,
6 l_base_ccy IN VARCHAR2,
7 l_company IN VARCHAR2,
8 incl_options IN VARCHAR2,
9 incl_indic_exp IN VARCHAR2,
10 l_portfolio IN VARCHAR2,
11 perspective IN VARCHAR2,
12 l_yield_curve IN VARCHAR2,
13 l_year_basis IN NUMBER,
14 l_dflt_disc_rate IN NUMBER,
15 l_rounding IN NUMBER,
16 l_wk_mth IN VARCHAR2) is
17 --
18 disc_rate NUMBER := 0;
19 disc_value NUMBER := 0;
20 l_num_items NUMBER := 0;
21 l_error NUMBER := 0;
22 l_rate NUMBER;
23 l_date VARCHAR2(11);
24 l_ins_date DATE;
25 l_combination VARCHAR2(31);
26 l_amount NUMBER;
27 l_onc_amount NUMBER;
28 l_avg_days NUMBER := 0;
29 l_weight_avg NUMBER := 0;
30 l_deal_ty NUMBER; l_amount_date DATE;
31 l_fwd_fx_rate NUMBER;
32 --
33 cursor GET_EXPOSURES is
34 select decode(l_wk_mth,'W',to_char(next_day(nvl(EXPOSURE_REF_DATE,AMOUNT_DATE),to_char(to_date('09/03/1997','DD/MM/YYYY'),'DY')),'DD-MM-YYYY')
35 ,to_char(AMOUNT_DATE,'MON-YYYY')),AMOUNT_DATE,
36 sum(CASHFLOW_AMOUNT),CURRENCY_COMBINATION,sum(TRANSACTION_RATE),
37 decode(DEAL_TYPE,'FX',TRANSACTION_NUMBER,'FXO',TRANSACTION_NUMBER,1),
38 decode(DEAL_TYPE,'FX',nvl(EXPOSURE_REF_DATE,AMOUNT_DATE),'FXO',nvl(EXPOSURE_REF_DATE,AMOUNT_DATE),NULL),count(AMOUNT)
39 from XTR_DEAL_DATE_AMOUNTS_V
40 where STATUS_CODE = 'CURRENT'
41 and (AMOUNT_DATE >= trunc(SYSDATE) or EXPOSURE_REF_DATE >=trunc(SYSDATE))
42 and COMPANY_CODE = l_company
43 and CASHFLOW_AMOUNT <> 0
44 and CURRENCY = sel_ccy
45 and nvl(PORTFOLIO_CODE,'%') like nvl(upper(l_portfolio),'%')
46 and (incl_options = 'Y' or (incl_options = 'N' and AMOUNT_TYPE NOT IN('FXOBUY','FXOSELL')))
47 and ((DEAL_SUBTYPE = 'INDIC' and incl_indic_exp = 'Y') or
48 DEAL_SUBTYPE <> 'INDIC')
49 and nvl(multiple_settlements,'N') <> 'Y'
50 group by decode(l_wk_mth,'W',to_char(next_day(nvl(EXPOSURE_REF_DATE,AMOUNT_DATE),to_char(to_date('09/03/1997','DD/MM/YYYY'),'DY')),'DD-MM-YYYY')
51 ,to_char(AMOUNT_DATE,'MON-YYYY')),AMOUNT_DATE,CURRENCY_COMBINATION,
52 decode(DEAL_TYPE,'FX',TRANSACTION_NUMBER,'FXO',TRANSACTION_NUMBER,1),
53 decode(DEAL_TYPE,'FX',nvl(EXPOSURE_REF_DATE,AMOUNT_DATE),'FXO',nvl(EXPOSURE_REF_DATE,AMOUNT_DATE),NULL);
54 --
55 cursor GET_ACCT_BALS is
56 select sum(nvl(OPENING_BALANCE,0)),count(ACCOUNT_NUMBER)
57 from XTR_BANK_ACCOUNTS
58 where PARTY_CODE = l_company
59 and CURRENCY like nvl(sel_ccy,'%')
60 and nvl(PORTFOLIO_CODE,'%') like nvl(upper(l_portfolio),'%')
61 and nvl(SETOFF_ACCOUNT_YN,'N') <> 'Y';
62 --
63 -- Show reverse of call cash on hand where no maturity date exists
64 -- this is because the cashflow will/would have gone through the account but is
65 -- not reflected anywhere in the future
66 -- eg INVEST initial cflow is -ve therefore show as cash on hand (+ve)
67 --
68 cursor GET_ONC_BALS is
69 select sum((-1) * d.cashflow_amount) CASH_ON_HAND
70 from XTR_DEAL_DATE_AMOUNTS_V d,
71 XTR_ROLLOVER_TRANSACTIONS_V r
72 where d.STATUS_CODE = 'CURRENT'
73 and d.DEAL_TYPE = 'ONC'
74 and d.COMPANY_CODE = l_company
75 and d.CURRENCY like upper(nvl(sel_ccy,'%'))
76 and nvl(d.PORTFOLIO_CODE,'%') like nvl(upper(l_portfolio),'%')
77 and nvl(d.multiple_settlements,'N') = 'N'
78 and d.CASHFLOW_AMOUNT <> 0
79 and r.deal_number = d.deal_number
80 and r.transaction_number = d.transaction_number
81 and r.MATURITY_DATE is NULL;
82 --
83 begin
84 delete from XTR_exposure_summary
85 where created_on < (trunc(sysdate) - 7);
86 commit;
87 open GET_EXPOSURES;
88 LOOP
89 fetch GET_EXPOSURES INTO l_date,l_ins_date,l_amount,l_combination,l_rate,l_deal_ty,l_amount_date,l_num_items;
90 EXIT WHEN GET_EXPOSURES%NOTFOUND or l_amount is NULL;
91 if l_amount_date is NOT NULL then
92 l_avg_days := round(l_amount_date - trunc(sysdate),0);
93 else
94 if l_wk_mth = 'W' then
95 l_avg_days := round(to_date(l_date,'DD-MM-YYYY') - trunc(sysdate) + 15,0);
96 else
97 -- l_avg_days := round(to_date(l_date,'MON-YYYY') - trunc(sysdate) + 15,0);
98 l_avg_days := round(l_ins_date - trunc(sysdate) + 15,0);
99 end if;
100 end if;
101 if l_avg_days = 0 then
102 l_avg_days := 1;
103 end if;
104 if l_yield_curve is NOT NULL then
105 XTR_fps2_P.EXTRAPOLATE_FROM_YIELD_CURVE(sel_ccy,l_avg_days,l_yield_curve,disc_rate);
106 else
107 XTR_fps2_P.EXTRAPOLATE_FROM_MARKET_PRICES(sel_ccy,l_avg_days,disc_rate);
108 end if;
109 if nvl(disc_rate,0) = 0 then
110 -- Use Default Discount Rate
111 if nvl(l_dflt_disc_rate,0) = 0 then
112 disc_value := l_amount;
113 else
114 disc_rate := l_dflt_disc_rate;
115 XTR_fps2_P.DISCOUNT_INTEREST_CALC(nvl(l_year_basis,360),l_amount,disc_rate,l_avg_days,
116 nvl(l_rounding,2),disc_value);
117 disc_value := l_amount - disc_value;
118 end if;
119 else
120 XTR_fps2_P.DISCOUNT_INTEREST_CALC(nvl(l_year_basis,360),l_amount,disc_rate,l_avg_days,
121 nvl(l_rounding,2),disc_value);
122 disc_value := l_amount - disc_value;
123 end if;
124 --
125 /*
126 -- Calculate FX Forward Rate for Ccy Combinations
127 if l_combination is NOT NULL then
128 CALC_FX_FWD_RATE(substr(l_combination,1,3),substr(l_combination,5,3),l_company,
129 nvl(l_year_basis,360),(trunc(sysdate) + l_avg_days),
130 substr(l_combination,1,3),l_fwd_fx_rate);
131 else
132 l_fwd_fx_rate := NULL;
133 end if;
134 */
135 --
136 begin
137 insert into XTR_EXPOSURE_SUMMARY
138 (unique_ref_number,currency,period,amount,average_days,weighted_average,currency_combination,
139 transaction_rate,created_on,created_by,incl_fx_options,incl_indic_exposures,company,base_currency,
140 number_of_items,acct_balance,portfolio_code,hedge_or_trade_view,discount_rate,discounted_back_to,
141 discounted_value,yield_curve,forward_fx_rate,selected_ccy,selected_indic,selected_options,
142 hedge_trade_whatif,selected_portfolio,month_or_week,period_date)
143 values
144 (ref_number,sel_ccy,l_date,round(l_amount,2),l_avg_days,l_weight_avg,l_combination,round(l_rate,5),
145 trunc(sysdate),fnd_global.user_id,
146 incl_options,incl_indic_exp,l_company,l_base_ccy,l_num_items,'N',l_portfolio,perspective,disc_rate,
147 trunc(sysdate),disc_value,l_yield_curve,l_fwd_fx_rate,sel_ccy,incl_indic_exp,incl_options,'H',l_portfolio,
148 nvl(l_wk_mth,'M'),decode(l_wk_mth,'W',to_date(l_date,'DD-MM-YYYY'),
149 -- to_date(l_date,'MON-YYYY')));
150 l_ins_date));
151 exception
152 when others then
153 l_error := l_error + 1;
154 end;
155 END LOOP;
156 close GET_EXPOSURES;
157 --
158 l_onc_amount :=0;
159 open GET_ONC_BALS;
160 fetch GET_ONC_BALS into l_onc_amount;
161 close GET_ONC_BALS;
162 --
163 l_amount := 0;
164 l_num_items := 0;
165 open GET_ACCT_BALS;
166 fetch GET_ACCT_BALS into l_amount,l_num_items;
167 close GET_ACCT_BALS;
168 --
169 -- add On Call Cash Balances to Account Balances
170 l_amount := nvl(l_amount,0) + nvl(l_onc_amount,0);
171 --
172 begin
173 insert into XTR_EXPOSURE_SUMMARY
174 (unique_ref_number,currency,period,amount,average_days,weighted_average,currency_combination,
175 transaction_rate,created_on,created_by,incl_fx_options,incl_indic_exposures,company,base_currency,
176 number_of_items,acct_balance,portfolio_code,hedge_or_trade_view,discount_rate,discounted_back_to,
177 discounted_value,yield_curve,selected_ccy,selected_indic,selected_options,
178 hedge_trade_whatif,selected_portfolio,month_or_week,period_date)
179 values
180 (ref_number,sel_ccy,decode(l_wk_mth,'W',to_char(next_day(trunc(sysdate),to_char(to_date('09/03/1997','DD/MM/YYYY'),'DY')),'DD-MM-YYYY'),
181 to_char(trunc(sysdate),'MON-YYYY')),round(l_amount,2),1,0,null,null,trunc(sysdate),fnd_global.user_id,
182 incl_options,incl_indic_exp,l_company,l_base_ccy,l_num_items,'Y',l_portfolio,perspective,
183 0,trunc(sysdate),l_amount,l_yield_curve,sel_ccy,incl_indic_exp,incl_options,'H',l_portfolio,
184 nvl(l_wk_mth,'M'),trunc(sysdate));
185 exception
186 when others then
187 l_error := l_error + 1;
188 end;
189 commit;
190 end CALC_HEDGE_DETAILS;
191 ----------------------------------------------------------------------------------------------------------------
192 PROCEDURE CALC_TRADING_DETAILS(ref_number IN NUMBER,
193 l_ccy_a IN VARCHAR2,
194 l_ccy_b IN VARCHAR2,
195 l_company IN VARCHAR2,
196 incl_options IN VARCHAR2,
197 l_portfolio IN VARCHAR2,
198 perspective IN VARCHAR2,
199 l_year_basis IN NUMBER,
200 l_rounding IN NUMBER) is
201 --
202 l_ccy VARCHAR2(15);
203 l_num_items NUMBER := 0;
204 l_error NUMBER := 0;
205 l_rate NUMBER;
206 l_date VARCHAR2(8);
207 l_ins_date DATE;
208 l_combination VARCHAR2(31);
209 l_amount NUMBER;
210 l_avg_days NUMBER := 0;
211 l_weight_avg NUMBER := 0;
212 l_deal_ty NUMBER;
213 l_amount_date DATE;
214 l_fwd_fx_rate NUMBER;
215 l_hce NUMBER;
216 l_pl NUMBER;
217 l_base VARCHAR2(15);
218 --
219 cursor GET_CONTRACTS is
220 select CURRENCY,to_char(nvl(EXPOSURE_REF_DATE,AMOUNT_DATE),'MON-YYYY'),
221 nvl(EXPOSURE_REF_DATE,AMOUNT_DATE),
222 sum(CASHFLOW_AMOUNT),
223 CURRENCY_COMBINATION,sum(TRANSACTION_RATE),
224 TRANSACTION_NUMBER,nvl(EXPOSURE_REF_DATE,AMOUNT_DATE),count(AMOUNT)
225 from XTR_DEAL_DATE_AMOUNTS_V
226 where STATUS_CODE = 'CURRENT'
227 and CURRENCY_COMBINATION like upper(nvl(l_ccy_a,'%'))||'/'||upper(nvl(l_ccy_b,'%'))
228 and (AMOUNT_DATE >= trunc(SYSDATE) or EXPOSURE_REF_DATE >=trunc(sysdate))
229 and CURRENCY = substr(CURRENCY_COMBINATION,1,3)
230 and DEAL_TYPE like 'FX%'
231 and AMOUNT_TYPE <> 'EXPIRY'
232 and COMPANY_CODE = l_company
233 and CASHFLOW_AMOUNT <> 0
234 and nvl(PORTFOLIO_CODE,'%') like nvl(upper(l_portfolio),'%')
235 and (incl_options = 'Y' or (incl_options = 'N' and AMOUNT_TYPE NOT IN('FXOBUY','FXOSELL')))
236 group by CURRENCY,to_char(nvl(EXPOSURE_REF_DATE,AMOUNT_DATE),'MON-YYYY'),
237 nvl(EXPOSURE_REF_DATE,AMOUNT_DATE),
238 CURRENCY_COMBINATION,
239 TRANSACTION_NUMBER,nvl(EXPOSURE_REF_DATE,AMOUNT_DATE);
240 --
241 cursor HCE is
242 select HCE_RATE
243 from XTR_MASTER_CURRENCIES
244 where CURRENCY = l_ccy;
245 --
246 begin
247 delete from XTR_exposure_summary
248 where created_on < (trunc(sysdate) - 7);
249 commit;
250 open GET_CONTRACTS;
251 LOOP
252 fetch GET_CONTRACTS INTO l_ccy,l_date,l_ins_date,l_amount,l_combination,l_rate,l_deal_ty,l_amount_date,l_num_items;
253 EXIT WHEN GET_CONTRACTS%NOTFOUND or l_amount is NULL;
254 if l_amount_date is NOT NULL then
255 l_avg_days := round(l_amount_date - trunc(sysdate),0);
256 else
257 -- l_avg_days := round(to_date(l_date,'MON-YYYY') - trunc(sysdate) + 15,0);
258 l_avg_days := round(l_ins_date - trunc(sysdate) + 15,0);
259 end if;
260 if l_avg_days = 0 then
261 l_avg_days := 1;
262 end if;
263 /*
264 -- Calculate FX Forward Rate for Ccy Combinations
265 if l_combination is NOT NULL then
266 CALC_FX_FWD_RATE(substr(l_combination,1,3),substr(l_combination,5,3),l_company,
267 nvl(l_year_basis,360),(trunc(sysdate) + l_avg_days),
268 substr(l_combination,1,3),l_fwd_fx_rate);
269 else
270 l_fwd_fx_rate := NULL;
271 end if;
272 */
273 --
274 open HCE;
275 fetch HCE INTO l_hce;
276 close HCE;
277 l_pl := round(l_amount / l_hce,0);
278 if l_ccy = substr(l_combination,1,3) then
279 l_base := l_ccy;
280 else
281 l_base := substr(l_combination,5,3);
282 end if;
283 --
284 begin
285 insert into XTR_EXPOSURE_SUMMARY
286 (unique_ref_number,currency,period,amount,average_days,weighted_average,currency_combination,
287 transaction_rate,created_on,created_by,incl_fx_options,company,base_currency,discounted_value,
288 number_of_items,acct_balance,portfolio_code,hedge_or_trade_view,discounted_back_to,forward_fx_rate,
289 selected_ccy,selected_ccy2,selected_indic,selected_options,hedge_trade_whatif,selected_portfolio,period_date)
290 values
291 (ref_number,l_ccy,l_date,round(l_amount,2),l_avg_days,l_weight_avg,l_combination,round(l_rate,5),
292 trunc(sysdate),fnd_global.user_id,
293 incl_options,l_company,l_base,l_pl,l_num_items,'N',l_portfolio,perspective,trunc(sysdate),l_fwd_fx_rate,
294 l_ccy_a,l_ccy_b,'N',incl_options,'T',l_portfolio,
295 --to_date(l_date,'MON-YYYY'));
296 l_ins_date);
297 exception
298 when others then
299 l_error := l_error + 1;
300 end;
301 END LOOP;
302 commit;
303 end CALC_TRADING_DETAILS;
304 ----------------------------------------------------------------------------------------------------
305 PROCEDURE CALC_FX_FWD_RATE(l_ccya IN VARCHAR2,
306 l_ccyb IN VARCHAR2,
307 l_company_code IN VARCHAR2,
308 l_yr_basis IN NUMBER,
309 l_end_date IN DATE,
310 l_base_ccy IN VARCHAR2,
311 l_answer IN OUT NOCOPY NUMBER) is
312 --
313 l_days NUMBER;
314 l_round NUMBER;
315 l_round1 NUMBER;
316 l_round2 NUMBER;
317 l_round3 NUMBER;
318 l_ccy VARCHAR2(15);
319 l_ccya_spot NUMBER;
320 l_ccyb_spot NUMBER;
321 tmp_calc_h NUMBER;
322 tmp_calc_k NUMBER;
323 tmp_calc_l NUMBER;
324 tmp_calc_o NUMBER;
325 base_curr_year_basis NUMBER;
326 contra_curr_year_basis NUMBER;
327 usd_curr_year_basis NUMBER;
328 l_base_int_rate NUMBER;
329 l_contra_int_rate NUMBER;
330 l_usd_int_rate NUMBER;
331 l_base_contra_ccya VARCHAR2(6);
332 l_base_contra_ccyb VARCHAR2(6);
333 --
334 cursor FX_SPOT_RATE is
335 select (r.BID_PRICE + r.ASK_PRICE) / 2
336 from XTR_MARKET_PRICES r
337 where ((r.CURRENCY_A = l_ccy
338 and r.CURRENCY_B = 'USD') or
339 (r.CURRENCY_A = 'USD'
340 and r.CURRENCY_B = l_ccy))
341 and r.TERM_TYPE = 'S';
342 --
343 cursor C1 is
344 select m1.year_basis,m1.rounding_factor,m2.year_basis,m2.rounding_factor,
345 m3.year_basis,m3.rounding_factor,
346 round(decode(m1.divide_or_multiply,'*',1 / l_ccya_spot,l_ccya_spot),9),
347 decode(m1.divide_or_multiply,'*','BASE','CONTRA'),
348 round(decode(m2.divide_or_multiply,'*',1 / l_ccyb_spot,l_ccyb_spot),9),
349 decode(m2.divide_or_multiply,'*','BASE','CONTRA')
350 from XTR_MASTER_CURRENCIES_V m1,
351 XTR_MASTER_CURRENCIES_V m2,
352 XTR_MASTER_CURRENCIES_V m3
353 where m1.CURRENCY = l_ccya
354 and m2.CURRENCY = l_ccyb
355 and m3.CURRENCY = 'USD';
356 --
357 begin
358 -- Fetch Spot rates agst USD for each ccy
359 l_ccy := l_ccya;
363 else
360 l_days := l_end_date - trunc(sysdate);
361 if l_ccy = 'USD' then
362 l_ccya_spot := 1;
364 open FX_SPOT_RATE;
365 fetch FX_SPOT_RATE INTO l_ccya_spot;
366 if FX_SPOT_RATE%NOTFOUND then
367 close FX_SPOT_RATE;
368 l_answer := 0;
369 goto ERROR_OCCURRED;
370 end if;
371 close FX_SPOT_RATE;
372 end if;
373 XTR_fps2_P.EXTRAPOLATE_FROM_MARKET_PRICES(l_ccy,l_days,l_base_int_rate);
374 --
375 l_ccy := l_ccyb;
376 if l_ccy = 'USD' then
377 l_ccyb_spot := 1;
378 else
379 open FX_SPOT_RATE;
380 fetch FX_SPOT_RATE INTO l_ccyb_spot;
381 if FX_SPOT_RATE%NOTFOUND then
382 close FX_SPOT_RATE;
383 l_answer := 0;
384 goto ERROR_OCCURRED;
385 end if;
386 close FX_SPOT_RATE;
387 end if;
388 XTR_fps2_P.EXTRAPOLATE_FROM_MARKET_PRICES(l_ccy,l_days,l_contra_int_rate);
389 --
390 l_ccy := 'USD';
391 XTR_fps2_P.EXTRAPOLATE_FROM_MARKET_PRICES(l_ccy,l_days,l_usd_int_rate);
392 --
393 open C1;
394 fetch C1 INTO base_curr_year_basis,l_round1,contra_curr_year_basis,l_round2,
395 usd_curr_year_basis,l_round3,tmp_calc_h,l_base_contra_ccya,tmp_calc_k,l_base_contra_ccyb;
396 if C1%NOTFOUND then
397 close C1;
398 l_answer := 0;
399 goto ERROR_OCCURRED;
400 end if;
401 close C1;
402 --
403 if l_base_int_rate = 0 then
404 l_base_int_rate := 0.0001;
405 end if;
406 if l_contra_int_rate = 0 then
407 l_contra_int_rate := 0.0001;
408 end if;
409 if l_usd_int_rate = 0 then
410 l_usd_int_rate := 0.0001;
411 end if;
412 if l_ccya = 'USD' then
413 tmp_calc_l := 1;
414 else
415 tmp_calc_l := round(((100000 * tmp_calc_h) +
416 (100000 * tmp_calc_h * l_base_int_rate /
417 (base_curr_year_basis * 100) * l_days)) /
418 (100000 + (100000 * l_usd_int_rate /
419 (usd_curr_year_basis * 100) * l_days)),9);
420 end if;
421 if l_ccyb = 'USD' then
422 tmp_calc_o := 1;
423 else
424 tmp_calc_o := round(((100000 * tmp_calc_k) +
425 (100000 * tmp_calc_k * l_contra_int_rate /
426 (contra_curr_year_basis * 100 ) * l_days)) /
427 (100000 + (100000 * l_usd_int_rate /
428 (usd_curr_year_basis * 100 ) * l_days)),9);
429 end if;
430 if l_ccya = l_base_ccy then
431 l_answer := round(tmp_calc_o / tmp_calc_l,nvl(l_round,5));
432 else
433 l_answer := round(tmp_calc_l / tmp_calc_o,nvl(l_round,5));
434 end if;
435 <<ERROR_OCCURRED>>
436 null;
437 end CALC_FX_FWD_RATE;
438 -------------------------------------------------------------------------------------------
439 PROCEDURE CALC_ALL_CCY_EXPOSURES(ref_number IN NUMBER,
440 p_sel_ccy IN VARCHAR2,
441 l_base_ccy IN VARCHAR2,
442 l_company IN VARCHAR2,
443 incl_options IN VARCHAR2,
444 incl_indic_exp IN VARCHAR2,
445 l_portfolio IN VARCHAR2,
446 perspective IN VARCHAR2,
447 l_yield_curve IN VARCHAR2,
448 p_year_basis IN NUMBER,
449 l_dflt_disc_rate IN NUMBER,
450 p_rounding IN NUMBER,
451 p_count_months_from IN DATE) is
452 --
453 l_ccy VARCHAR2(15);
454 disc_rate NUMBER := 0;
455 disc_value NUMBER := 0;
456 l_num_items NUMBER := 0;
457 l_error NUMBER := 0;
458 l_rate NUMBER;
459 l_date VARCHAR2(11);
460 l_combination VARCHAR2(31);
461 l_amount NUMBER;
462 l_onc_amount NUMBER;
463 l_avg_days NUMBER := 0;
464 l_weight_avg NUMBER := 0;
465 l_deal_ty NUMBER;
466 l_amount_date DATE;
467 l_fwd_fx_rate NUMBER;
468 l_row_ccy VARCHAR2(15);
469 sel_ccy VARCHAR2(15);
470 l_combin VARCHAR2(31);
471 count_months_from DATE;
472 --
473 cursor GET_FX is
474 select CURRENCY,to_char(nvl(EXPOSURE_REF_DATE,AMOUNT_DATE),'DD-MM-YYYY'),sum(CASHFLOW_AMOUNT),
475 CURRENCY_COMBINATION,sum(TRANSACTION_RATE),
476 decode(DEAL_TYPE,'FX',TRANSACTION_NUMBER,'FXO',TRANSACTION_NUMBER,1),
477 decode(DEAL_TYPE,'FX',nvl(EXPOSURE_REF_DATE,AMOUNT_DATE),'FXO',nvl(EXPOSURE_REF_DATE,AMOUNT_DATE),NULL),count(AMOUNT)
478 from XTR_DEAL_DATE_AMOUNTS_V
479 where STATUS_CODE = 'CURRENT'
480 and (AMOUNT_DATE >= trunc(SYSDATE)
481 or EXPOSURE_REF_DATE >=trunc(SYSDATE))
482 and COMPANY_CODE = l_company
483 and CASHFLOW_AMOUNT <> 0
484 and CURRENCY = substr(l_combin,1,3)
485 and nvl(PORTFOLIO_CODE,'%') like nvl(upper(l_portfolio),'%')
486 and CURRENCY_COMBINATION = upper(l_combin)
487 and (incl_options = 'Y' or (incl_options = 'N' and AMOUNT_TYPE NOT IN('FXOBUY','FXOSELL')))
488 and nvl(multiple_settlements,'N') <> 'Y'
489 group by CURRENCY,to_char(nvl(EXPOSURE_REF_DATE,AMOUNT_DATE),'DD-MM-YYYY'),CURRENCY_COMBINATION,
490 decode(DEAL_TYPE,'FX',TRANSACTION_NUMBER,'FXO',TRANSACTION_NUMBER,1),
494 select CURRENCY,to_char(AMOUNT_DATE,'DD-MM-YYYY'),sum(CASHFLOW_AMOUNT),
491 decode(DEAL_TYPE,'FX',nvl(EXPOSURE_REF_DATE,AMOUNT_DATE),'FXO',nvl(EXPOSURE_REF_DATE,AMOUNT_DATE),NULL);
492 --
493 cursor GET_EXPOSURES is
495 CURRENCY_COMBINATION,sum(TRANSACTION_RATE),
496 decode(DEAL_TYPE,'FX',TRANSACTION_NUMBER,'FXO',TRANSACTION_NUMBER,1),
497 decode(DEAL_TYPE,'FX',AMOUNT_DATE,'FXO',AMOUNT_DATE,NULL),count(AMOUNT)
498 from XTR_DEAL_DATE_AMOUNTS_V
499 where STATUS_CODE = 'CURRENT'
500 and AMOUNT_DATE >= trunc(SYSDATE)
501 and COMPANY_CODE = l_company
502 and ((deal_type='EXP' and perspective='^') or perspective <>'^')
503 and CASHFLOW_AMOUNT <> 0
504 and CURRENCY like upper(nvl(sel_ccy,'%'))
505 and nvl(PORTFOLIO_CODE,'%') like nvl(upper(l_portfolio),'%')
506 and CURRENCY_COMBINATION is NULL
507 and ((DEAL_SUBTYPE = 'INDIC' and incl_indic_exp = 'Y') or
508 DEAL_SUBTYPE <> 'INDIC')
509 and nvl(multiple_settlements,'N') <> 'Y'
510 group by CURRENCY,to_char(AMOUNT_DATE,'DD-MM-YYYY'),CURRENCY_COMBINATION,
511 decode(DEAL_TYPE,'FX',TRANSACTION_NUMBER,'FXO',TRANSACTION_NUMBER,1),
512 decode(DEAL_TYPE,'FX',AMOUNT_DATE,'FXO',AMOUNT_DATE,NULL);
513 --
514 cursor GET_ACCT_BALS is
515 select currency,sum(nvl(OPENING_BALANCE,0)),count(ACCOUNT_NUMBER)
516 from XTR_BANK_ACCOUNTS
517 where PARTY_CODE = l_company
518 and CURRENCY like upper(nvl(sel_ccy,'%'))
519 and nvl(PORTFOLIO_CODE,'%') like nvl(upper(l_portfolio),'%')
520 and nvl(SETOFF_ACCOUNT_YN,'N') <> 'Y'
521 group by currency;
522 --
523 -- Show reverse of call cash on hand where no maturity date exists
524 -- this is because the cashflow will/would have gone through the account but is
525 -- not reflected anywhere in the future
526 -- eg INVEST initial cflow is -ve therefore show as cash on hand (+ve)
527 --
528 cursor GET_ONC_BALS is
529 select d.currency,sum((-1) * d.cashflow_amount) CASH_ON_HAND
530 from XTR_DEAL_DATE_AMOUNTS_V d,
531 XTR_ROLLOVER_TRANSACTIONS_V r
532 where d.STATUS_CODE = 'CURRENT'
533 and d.DEAL_TYPE = 'ONC'
534 and d.COMPANY_CODE = l_company
535 and d.CURRENCY like upper(nvl(sel_ccy,'%'))
536 and nvl(d.PORTFOLIO_CODE,'%') like nvl(upper(l_portfolio),'%')
537 and nvl(d.multiple_settlements,'N') = 'N'
538 and d.CASHFLOW_AMOUNT <> 0
539 and r.deal_number = d.deal_number
540 and r.transaction_number = d.transaction_number
541 and r.maturity_date is NULL
542 group by d.currency;
543 --
544 cursor SELECTED_CCY is
545 select CURRENCY
546 from XTR_FX_WHAT_IFS
547 where UNIQUE_REF_NUMBER = ref_number
548 and REVIEW = 'Y';
549 --
550 cursor DIST_CCY_COMBIN is
551 select distinct CURRENCY_FIRST||'/'||CURRENCY_SECOND
552 from XTR_BUY_SELL_COMBINATIONS
553 where CURRENCY_FIRST IN(select CURRENCY
554 from XTR_FX_WHAT_IFS
555 where UNIQUE_REF_NUMBER = ref_number)
556 or CURRENCY_SECOND IN(select CURRENCY
557 from XTR_FX_WHAT_IFS
558 where UNIQUE_REF_NUMBER = ref_number);
559 --
560 cursor GET_FX_PERIOD is
561 select currency,nvl(PERIOD_DESC,nvl(PERIOD_FROM,'0')||decode(nvl(TYPE_PERIOD_FROM_MONTH_YEAR,'M'),nvl(TYPE_PERIOD_TO_MONTH_YEAR,'M'),NULL,decode(nvl(TYPE_PERIOD_FROM_MONTH_YEAR,'M'),'M','Months','Years'))||'-'||
562 nvl(PERIOD_TO,'0')||decode(nvl(TYPE_PERIOD_TO_MONTH_YEAR,'M'),'M','Months','Years')) PERIOD_NAME,
563 add_months(trunc(count_months_from),decode(nvl(TYPE_PERIOD_FROM_MONTH_YEAR,'M'),'M',1,12)*nvl(PERIOD_FROM,0)) P_FROM,
564 decode(PERIOD_TO,NULL,to_date('01/01/2200','DD/MM/YYYY'),add_months(trunc(count_months_from),decode(nvl(TYPE_PERIOD_TO_MONTH_YEAR,'M'),'M',1,12)*PERIOD_TO)) P_TO,
565 FX_PERCENT_MAX,FX_PERCENT_MIN,period_desc
566 from XTR_INTEREST_RATE_BANDS
567 where currency like upper(nvl(p_sel_ccy,'%'))
568 and currency not in(select home_currency
569 from XTR_parties_v
570 where party_type='C');
571 --
572 l_fx_ccy varchar2(15);
573 l_period_name varchar2(20);
574 l_p_from date;
575 l_p_to date;
576 l_max number;
577 l_min number;
578 l_period_desc varchar2(20);
579 --
580 cursor GET_FX_POSTION is
581 SELECT nvl(sum(decode(CURRENCY_COMBINATION,NULL,AMOUNT,0)),0) exp_amt,
582 nvl(sum(decode(CURRENCY_COMBINATION,NULL,0,
583 decode(currency,l_fx_ccy,AMOUNT,-AMOUNT*round(TRANSACTION_RATE,5)))),0) fx_amt
584 from XTR_EXPOSURE_SUMMARY
585 where UNIQUE_REF_NUMBER = ref_number
586 and((currency_combination is null and currency = l_fx_ccy) or
587 (currency_combination is not null and
588 (substr(currency_combination,1,3) = l_fx_ccy or substr(currency_combination,5,3) = l_fx_ccy)))
589 and period_date >= l_p_from
590 and period_date < l_p_to
591 and hedge_trade_whatif = 'W';
592 --
593 l_exp_amt NUMBER;
594 l_fx_amt NUMBER;
595 --
596 cursor get_ccy_exp is
597 select YEAR_BASIS, ROUNDING_FACTOR
598 from XTR_MASTER_CURRENCIES_V
599 where CURRENCY = l_ccy;
600 --
601 cursor get_ccy_fx is
602 select YEAR_BASIS, ROUNDING_FACTOR
603 from XTR_MASTER_CURRENCIES_V
604 where CURRENCY = l_row_ccy;
605 --
606 l_year_basis NUMBER;
607 l_rounding NUMBER;
608 --
609 begin
610 delete from XTR_exposure_summary
614 --
611 where created_on < (trunc(sysdate) - 7);
612 delete from XTR_tmp_fx_exposure
613 where created_on < (trunc(sysdate) - 7);
615 commit;
616 --
617 if p_count_months_from is NULL then
618 count_months_from := trunc(sysdate);
619 else
620 count_months_from := trunc(p_count_months_from);
621 end if;
622 --
623 open SELECTED_CCY;
624 LOOP
625 fetch SELECTED_CCY INTO sel_ccy;
626 EXIT WHEN SELECTED_CCY%NOTFOUND;
627 --
628 open GET_EXPOSURES;
629 LOOP
630 fetch GET_EXPOSURES INTO l_ccy,l_date,l_amount,l_combination,l_rate,l_deal_ty,l_amount_date,l_num_items;
631 EXIT WHEN GET_EXPOSURES%NOTFOUND or l_amount is NULL;
632 --
633 open get_ccy_exp;
634 fetch get_ccy_exp into l_year_basis,l_rounding;
635 close get_ccy_exp;
636 --
637 if l_amount_date is NOT NULL then
638 l_avg_days := round(l_amount_date - trunc(count_months_from),0);
639 else
640 l_avg_days := round(to_date(l_date,'DD-MM-YYYY') - trunc(count_months_from),0);
641 end if;
642 if l_avg_days = 0 then
643 l_avg_days := 1;
644 end if;
645 if l_yield_curve is NOT NULL then
646 XTR_fps2_P.EXTRAPOLATE_FROM_YIELD_CURVE(sel_ccy,l_avg_days,l_yield_curve,disc_rate);
647 else
648 XTR_fps2_P.EXTRAPOLATE_FROM_MARKET_PRICES(sel_ccy,l_avg_days,disc_rate);
649 end if;
650 if nvl(disc_rate,0) = 0 then
651 -- Use Default Discount Rate
652 if nvl(l_dflt_disc_rate,0) = 0 then
653 disc_value := l_amount;
654 else
655 disc_rate := l_dflt_disc_rate;
656 XTR_fps2_P.DISCOUNT_INTEREST_CALC(nvl(l_year_basis,360),l_amount,disc_rate,l_avg_days,
657 nvl(l_rounding,2),disc_value);
658 disc_value := l_amount - disc_value;
659 end if;
660 else
661 XTR_fps2_P.DISCOUNT_INTEREST_CALC(nvl(l_year_basis,360),l_amount,disc_rate,l_avg_days,
662 nvl(l_rounding,2),disc_value);
663 disc_value := l_amount - disc_value;
664 end if;
665 --
666 /*
667 -- Calculate FX Forward Rate for Ccy Combinations
668 if l_combination is NOT NULL then
669 CALC_FX_FWD_RATE(substr(l_combination,1,3),substr(l_combination,5,3),l_company,
670 nvl(l_year_basis,360),(trunc(count_months_from) + l_avg_days),
671 substr(l_combination,1,3),l_fwd_fx_rate);
672 else
673 l_fwd_fx_rate := NULL;
674 end if;
675 */
676 --
677 begin
678 if nvl(l_amount,0) <>0 then
679 insert into XTR_EXPOSURE_SUMMARY
680 (unique_ref_number,currency,period,amount,average_days,weighted_average,currency_combination,
681 transaction_rate,created_on,created_by,incl_fx_options,incl_indic_exposures,company,base_currency,
682 number_of_items,acct_balance,portfolio_code,hedge_or_trade_view,discount_rate,discounted_back_to,
683 discounted_value,yield_curve,forward_fx_rate,selected_ccy,selected_indic,selected_options,
684 hedge_trade_whatif,selected_portfolio,period_date)
685 values
686 (ref_number,l_ccy,l_date,round(l_amount,2),l_avg_days,l_weight_avg,l_combination,round(l_rate,5),
687 trunc(sysdate),fnd_global.user_id,
688 incl_options,incl_indic_exp,l_company,l_base_ccy,l_num_items,'N',l_portfolio,decode(perspective,'^','W',perspective),disc_rate,
689 trunc(sysdate),disc_value,l_yield_curve,l_fwd_fx_rate,nvl(p_sel_ccy,'%'),incl_indic_exp,incl_options,'W',l_portfolio,
690 to_date(l_date,'DD-MM-YYYY'));
691 end if;
692 exception
693 when others then
694 l_error := l_error + 1;
695 end;
696 END LOOP;
697 close GET_EXPOSURES;
698 --
699 if perspective <>'^' then
700 l_onc_amount := 0;
701 open GET_ONC_BALS;
702 fetch GET_ONC_BALS into l_row_ccy,l_onc_amount;
703 close GET_ONC_BALS;
704
705 l_amount := 0;
706 l_num_items := 0;
707
708 open GET_ACCT_BALS;
709 fetch GET_ACCT_BALS into l_row_ccy,l_amount,l_num_items;
710 close GET_ACCT_BALS;
711 --
712 -- Add On Call Cash Balances to Account Balances
713 l_amount := nvl(l_amount,0) + nvl(l_onc_amount,0);
714 --
715 begin
716 if nvl(l_amount,0) <>0 then
717 insert into XTR_EXPOSURE_SUMMARY
718 (unique_ref_number,currency,period,amount,average_days,weighted_average,currency_combination,
719 transaction_rate,created_on,created_by,incl_fx_options,incl_indic_exposures,company,base_currency,
720 number_of_items,acct_balance,portfolio_code,hedge_or_trade_view,discount_rate,discounted_back_to,
721 discounted_value,yield_curve,selected_ccy,selected_indic,selected_options,
722 hedge_trade_whatif,selected_portfolio,period_date)
723 values
724 (ref_number,l_row_ccy,to_char(trunc(sysdate),'DD-MM-YYYY'),round(l_amount,2),1,0,null,null,trunc(sysdate),
725 fnd_global.user_id,incl_options,incl_indic_exp,l_company,l_base_ccy,l_num_items,'Y',l_portfolio,perspective,
726 0,trunc(sysdate),l_amount,l_yield_curve,nvl(p_sel_ccy,'%'),incl_indic_exp,incl_options,'W',l_portfolio,trunc(sysdate));
727 end if;
728 exception
729 when others then
730 l_error := l_error + 1;
731 end;
732 end if;
733 END LOOP;
734 close SELECTED_CCY;
735 --
736 --
737 open DIST_CCY_COMBIN;
738 LOOP
739 fetch DIST_CCY_COMBIN INTO l_combin;
740 --
741 EXIT WHEN DIST_CCY_COMBIN%NOTFOUND;
742 open GET_FX;
743 LOOP
744 fetch GET_FX INTO l_row_ccy,l_date,l_amount,l_combination,l_rate,l_deal_ty,
748 fetch get_ccy_fx into l_year_basis,l_rounding;
745 l_amount_date,l_num_items;
746 EXIT WHEN GET_FX%NOTFOUND;
747 open get_ccy_fx;
749 close get_ccy_fx;
750
751 if l_amount_date is NOT NULL then
752 l_avg_days := round(l_amount_date - trunc(count_months_from),0);
753 else
754 l_avg_days := round(to_date(l_date,'DD-MM-YYYY') - trunc(count_months_from),0);
755 end if;
756 if l_avg_days = 0 then
757 l_avg_days := 1;
758 end if;
759 if l_yield_curve is NOT NULL then
760 XTR_fps2_P.EXTRAPOLATE_FROM_YIELD_CURVE(l_row_ccy,l_avg_days,l_yield_curve,disc_rate);
761 else
762 XTR_fps2_P.EXTRAPOLATE_FROM_MARKET_PRICES(l_row_ccy,l_avg_days,disc_rate);
763 end if;
764 if nvl(disc_rate,0) = 0 then
765 -- Use Default Discount Rate
766 if nvl(l_dflt_disc_rate,0) = 0 then
767 disc_value := l_amount;
768 else
769 disc_rate := l_dflt_disc_rate;
770 XTR_fps2_P.DISCOUNT_INTEREST_CALC(nvl(l_year_basis,360),l_amount,disc_rate,l_avg_days,
771 nvl(l_rounding,2),disc_value);
772 disc_value := l_amount - disc_value;
773 end if;
774 else
775 XTR_fps2_P.DISCOUNT_INTEREST_CALC(nvl(l_year_basis,360),l_amount,disc_rate,l_avg_days,
776 nvl(l_rounding,2),disc_value);
777 disc_value := l_amount - disc_value;
778 end if;
779 --
780 /*
781 -- Calculate FX Forward Rate for Ccy Combinations
782 if l_combination is NOT NULL then
783 CALC_FX_FWD_RATE(substr(l_combination,1,3),substr(l_combination,5,3),l_company,
784 nvl(l_year_basis,360),(trunc(sysdate) + l_avg_days),
785 substr(l_combination,1,3),l_fwd_fx_rate);
786 else
787 l_fwd_fx_rate := NULL;
788 end if;
789 */
790 --
791 begin
792 if nvl(l_amount,0) <>0 then
793 insert into XTR_EXPOSURE_SUMMARY
794 (unique_ref_number,currency,period,amount,average_days,weighted_average,currency_combination,
795 transaction_rate,created_on,created_by,incl_fx_options,incl_indic_exposures,company,base_currency,
796 number_of_items,acct_balance,portfolio_code,hedge_or_trade_view,discount_rate,discounted_back_to,
797 discounted_value,yield_curve,forward_fx_rate,selected_ccy,selected_indic,selected_options,
798 hedge_trade_whatif,selected_portfolio,period_date)
799 values
800 (ref_number,l_row_ccy,l_date,round(l_amount,2),l_avg_days,l_weight_avg,l_combination,round(l_rate,5),
801 trunc(sysdate),fnd_global.user_id,
802 incl_options,incl_indic_exp,l_company,l_base_ccy,l_num_items,'N',l_portfolio,decode(perspective,'^','W',perspective),disc_rate,
803 trunc(sysdate),disc_value,l_yield_curve,l_fwd_fx_rate,nvl(p_sel_ccy,'%'),incl_indic_exp,incl_options,'W',l_portfolio,
804 to_date(l_date,'DD-MM-YYYY'));
805 end if;
806 exception
807 when others then
808 l_error := l_error + 1;
809 end;
810 END LOOP;
811 close GET_FX;
812 --
813 END LOOP;
814 close DIST_CCY_COMBIN;
815 ------------
816 -- FX POSITION
817 open GET_FX_PERIOD;
818 LOOP
819 fetch GET_FX_PERIOD into l_fx_ccy,l_period_name,l_p_from,l_p_to,l_max,l_min,l_period_desc;
820 EXIT WHEN GET_FX_PERIOD%NOTFOUND;
821 --
822 l_exp_amt := 0;
823 l_fx_amt := 0;
824 --
825 open GET_FX_POSTION;
826 fetch GET_FX_POSTION into l_exp_amt,l_fx_amt;
827 if GET_FX_POSTION%NOTFOUND then
828 l_exp_amt := 0;
829 l_fx_amt := 0;
830 end if;
831 close GET_FX_POSTION;
832 --
833 insert into XTR_TMP_FX_EXPOSURE
834 (unique_ref_number,currency,period_name,period_desc,FX_PERCENT_MAX,FX_PERCENT_MIN,
835 exp_amount,fx_amount,created_on,created_by,incl_fx_options,incl_indic_exposures,
836 company,base_currency,selected_base_currency,selected_ccy,selected_portfolio,yield_curve,
837 period_from,period_to,net_exposure,cover_to_forecast,max_amount,min_amount)
838 values
839 (ref_number,l_fx_ccy,l_period_name,l_period_desc,l_max,l_min,round(l_exp_amt,2),round(l_fx_amt,2),
840 trunc(sysdate),fnd_global.user_id,incl_options,incl_indic_exp,l_company,l_base_ccy,l_base_ccy,nvl(p_sel_ccy,'%'),
841 l_portfolio,l_yield_curve,l_p_from,l_p_to,nvl(round(l_exp_amt,2),0)+nvl(round(l_fx_amt,2),0),
842 decode(nvl(round(l_exp_amt,2),0),0,null,round(nvl(-100*round(l_fx_amt,2),0)/round(l_exp_amt,2),2)),
843 (nvl(l_max,0)*nvl(round(-l_exp_amt,2),0))/100-nvl(round(l_fx_amt,2),0),
844 (nvl(l_min,0)*nvl(round(-l_exp_amt,2),0))/100-nvl(round(l_fx_amt,2),0));
845 END LOOP;
846 close GET_FX_PERIOD;
847 commit;
848 end CALC_ALL_CCY_EXPOSURES;
849 --------------------------------------------------------------------------------------------------------------------------------------------------------
850 FUNCTION GET_SPOT_RATE(p_base_ccy VARCHAR2,
851 p_contra_ccy VARCHAR2,
852 p_date DATE) RETURN NUMBER IS
853 cursor get_cross is
854 SELECT BID_RATE
855 FROM XTR_CURRENCY_CROSS_RATES
856 WHERE CURRENCY_FIRST=p_base_ccy
857 and CURRENCY_SECOND=p_contra_ccy
858 and rate_date in(select max(rate_date)
859 from XTR_spot_rates
860 where CURRENCY_FIRST=p_base_ccy
861 and CURRENCY_SECOND=p_contra_ccy
862 and to_char(rate_date,'DD-MM-YYYY')=to_char(p_date,'DD-MM-YYYY'));
863 --
864 cursor get_rate(l_ccy varchar2) is
865 SELECT USD_BASE_CURR_BID_RATE
869 from XTR_spot_rates
866 FROM XTR_spot_rates
867 WHERE currency = l_ccy
868 and rate_date in(select max(rate_date)
870 where currency=l_ccy
871 and to_char(rate_date,'DD-MM-YYYY')=to_char(p_date,'DD-MM-YYYY'));
872 l_base_rate number;
873 l_contra_rate number;
874 begin
875 l_base_rate :=null;
876 if p_base_ccy <>'USD' and p_contra_ccy <>'USD' then
877 open get_cross;
878 fetch get_cross into l_base_rate;
879 close get_cross;
880 end if;
881 if l_base_rate is null then
882 l_base_rate :=null;
883 l_contra_rate :=null;
884 if p_base_ccy ='USD' then
885 l_base_rate :=1;
886 else
887 open get_rate(p_base_ccy);
888 fetch get_rate into l_base_rate;
889 close get_rate;
890 end if;
891 if p_contra_ccy ='USD' then
892 l_contra_rate :=1;
893 else
894 open get_rate(p_contra_ccy);
895 fetch get_rate into l_contra_rate;
896 close get_rate;
897 end if;
898 if nvl(l_base_rate,0) <>0 then
899 return(round(l_contra_rate/l_base_rate,4));
900 else
901 return(NULL);
902 end if;
903 else
904 return(l_base_rate);
905 end if;
906 end GET_SPOT_RATE;
907 -------------------------------------------------------------------------------------------------
908 FUNCTION GET_HCE_RATE(p_base_ccy VARCHAR2,
909 p_date DATE) RETURN NUMBER IS
910
911 cursor get_rate is
912 select HCE_RATE
913 from XTR_spot_rates
914 where currency=p_base_ccy
915 and rate_date in(select max(rate_date)
916 from XTR_spot_rates
917 where currency = p_base_ccy
918 and to_char(rate_date,'DD-MM-YYYY')=to_char(p_date,'DD-MM-YYYY'));
919 --
920 l_hce_rate number;
921 --
922 begin
923 open get_rate;
924 fetch get_rate into l_hce_rate;
925 close get_rate;
926 return(l_hce_rate);
927 end GET_HCE_RATE;
928 ---------------------------------------------------------------------
929 --PROCEDURE SUMMARY_COST_OF_FUNDS is
930 PROCEDURE SUMMARY_COST_OF_FUNDS(errbuf OUT NOCOPY VARCHAR2,
931 retcode OUT NOCOPY NUMBER) is
932 --
933 l_run_date date := sysdate;
934 l_date date;
935 --
936 /*
937 cursor get_cof is
938 select company_code,deal_type,currency,contra_ccy,currency_combination,
939 decode(deal_type,'FX','%',deal_subtype) deal_subtype,
940 limit_party,
941 product_type,
942 portfolio_code,
943 sum(nvl(amount_indic,1)*nvl(amount,0)) gross_principal,
944 sum(nvl(amount_indic,1)*nvl(amount,0)*nvl(transaction_rate,0)
945 /(decode(deal_type,'FX',1,'FXO',1,100))) weighted_amt,
946 count(distinct decode(deal_type,'ONC',transaction_number,deal_number)) no_of_deals
947 from XTR_MIRROR_DDA_LIMIT_ROW_V
948 where (amount_date >= l_date or deal_type = 'ONC' or deal_type = 'CMF')
949 and deal_type <>'CA'
950 group by company_code,deal_type,currency,contra_ccy,currency_combination,
951 decode(deal_type,'FX','%',deal_subtype),limit_party,product_type,portfolio_code;
952 */
953 --
954 cursor get_cof is
955 select a.company_code,a.deal_type,a.currency,a.contra_ccy,a.currency_combination,
956 decode(a.deal_type,'FX','%',a.deal_subtype) subtype,
957 a.limit_party,
958 a.product_type,
959 a.portfolio_code,
960 round(sum(nvl(a.amount_indic,1)*nvl(a.amount,0)),0) gross_principal,
961 round(sum(nvl(a.amount_indic,1)*nvl(a.amount,0)*nvl(a.transaction_rate,0)
962 /(decode(a.deal_type,'FX',1,'FXO',1,100))),0) weighted_amt,
963 count(distinct a.deal_number) no_of_deals
964 from XTR_MIRROR_DDA_LIMIT_ROW_V a,
965 XTR_DEALS_V b
966 where a.amount_date > l_date
967 and a.deal_type not in('CA','ONC','CMF','IG')
968 and a.deal_number=b.deal_no
969 and (nvl(b.start_date,l_date) <= l_date or a.deal_type='FXO')
970 group by a.company_code,a.deal_type,a.currency,a.contra_ccy,a.currency_combination,
971 decode(a.deal_type,'FX','%',a.deal_subtype),a.limit_party,a.product_type,a.portfolio_code
972 union all
973 select a.company_code,a.deal_type,a.currency,a.contra_ccy,a.currency_combination,
974 a.deal_subtype subtype,
975 a.limit_party,
976 a.product_type,
977 a.portfolio_code,
978 round(sum(nvl(a.amount_indic,1)*nvl(a.amount,0)),0) gross_principal,
979 round(sum(nvl(a.amount_indic,1)*nvl(a.amount,0)*nvl(a.transaction_rate,0)/100),0) weighted_amt,
980 count(distinct decode(a.deal_type,'ONC',a.transaction_number,a.deal_number)) no_of_deals
981 from XTR_MIRROR_DDA_LIMIT_ROW_V a,
982 XTR_ROLLOVER_TRANSACTIONS_V b
983 where a.deal_type in('ONC','CMF','IG')
984 and a.deal_number=b.deal_number
985 and a.transaction_number=b.transaction_number
986 and nvl(b.start_date,l_date) <=l_date
987 group by a.company_code,a.deal_type,a.currency,a.contra_ccy,a.currency_combination,
988 a.deal_subtype,a.limit_party,a.product_type,a.portfolio_code;
989 --
990 cof get_cof%rowtype;
991 l_avg_rate number;
992 l_interest number;
993 --
994 cursor get_prv_row is
995 select as_at_date
996 from XTR_cost_of_funds
997 where as_at_date < l_date
998 order by as_at_date desc;
999 --
1000 l_prv_date date;
1001 l_ins_date date;
1002 --
1003 cursor chk_exits is
1007 --
1004 select 1
1005 from XTR_cost_of_funds
1006 where as_at_date = l_date;
1008 l_dummy number;
1009 --
1010 cursor get_tmm_row is
1011 select rowid,deal_number
1012 from XTR_mirror_dda_limit_row_V
1013 where amount_date > l_date
1014 and deal_type = 'TMM';
1015 --
1016 l_deal_no number;
1017 l_rowid varchar2(30);
1018 --
1019 cursor get_tmm_rate is
1020 select INTEREST_RATE
1021 from XTR_rollover_transactions_V
1022 where deal_number=l_deal_no
1023 and deal_type='TMM'
1024 and start_date<=l_date
1025 and maturity_date>l_date
1026 order by start_date desc;
1027 --
1028 l_transaction_rate number;
1029 --
1030 cursor get_ca_row is
1031 select COMPANY_CODE,CURRENCY,DEAL_TYPE,AMOUNT_DATE,TRANSACTION_RATE,
1032 CONTRA_CCY,CURRENCY_COMBINATION,DEAL_SUBTYPE,
1033 AMOUNT,HCE_AMOUNT,LIMIT_PARTY,PORTFOLIO_CODE,PRODUCT_TYPE,ACCOUNT_NO
1034 from XTR_mirror_dda_limit_row_V
1035 where deal_type='CA';
1036
1037 --
1038 bal get_ca_row%ROWTYPE;
1039 --
1040 cursor get_hce_rate(l_ccy varchar2) is
1041 select nvl(hce_rate,1),year_basis
1042 from XTR_master_currencies
1043 where currency = l_ccy;
1044 --
1045 l_hce_rate number;
1046 l_year_basis number;
1047 --
1048
1049 begin
1050 -- check time
1051 if to_number(to_char(l_run_date,'HH24')) <6 then
1052 l_date :=trunc(sysdate)-1;
1053 else
1054 l_date :=trunc(sysdate);
1055 end if;
1056
1057 -- delete COF if already exits
1058 open chk_exits;
1059 fetch chk_exits into l_dummy;
1060 if chk_exits%FOUND then
1061 delete from XTR_cost_of_funds
1062 where as_at_date = l_date;
1063 end if;
1064 close chk_exits;
1065
1066 -- get previous run date and copy into COF if has not run this script everyday.
1067
1068 l_prv_date :=null;
1069 open get_prv_row;
1070 fetch get_prv_row into l_prv_date;
1071 if get_prv_row%FOUND then
1072 close get_prv_row;
1073 l_ins_date :=l_prv_date+1;
1074 WHILE TRUE LOOP
1075 if l_ins_date<=l_date-1 then
1076 insert into XTR_COST_OF_FUNDS
1077 (AS_AT_DATE,COMPANY_CODE,CURRENCY,DEAL_TYPE,AVG_INTEREST_RATE,
1078 CONTRA_CCY,CURRENCY_COMBINATION,DEAL_SUBTYPE,
1079 GROSS_PRINCIPAL,HCE_GROSS_PRINCIPAL,INTEREST,HCE_INTEREST,
1080 NO_OF_DAYS,NO_OF_DEALS,PARTY_CODE, PORTFOLIO_CODE,PRODUCT_TYPE,
1081 WEIGHTED_AVG_PRINCIPAL,ACCOUNT_NO,CREATED_ON)
1082 select l_ins_date,COMPANY_CODE,CURRENCY,DEAL_TYPE,AVG_INTEREST_RATE,
1083 CONTRA_CCY,CURRENCY_COMBINATION,DEAL_SUBTYPE,
1084 GROSS_PRINCIPAL,HCE_GROSS_PRINCIPAL,INTEREST,HCE_INTEREST,
1085 NO_OF_DAYS,NO_OF_DEALS,PARTY_CODE, PORTFOLIO_CODE,PRODUCT_TYPE,
1086 WEIGHTED_AVG_PRINCIPAL,ACCOUNT_NO,trunc(l_run_date)
1087 from XTR_COST_OF_FUNDS where as_at_date=l_prv_date;
1088 elsif l_ins_date=l_date then
1089 insert into XTR_COST_OF_FUNDS
1090 (AS_AT_DATE,COMPANY_CODE,CURRENCY,DEAL_TYPE,AVG_INTEREST_RATE,
1091 CONTRA_CCY,CURRENCY_COMBINATION,DEAL_SUBTYPE,
1092 GROSS_PRINCIPAL,HCE_GROSS_PRINCIPAL,INTEREST,HCE_INTEREST,
1093 NO_OF_DAYS,NO_OF_DEALS,PARTY_CODE, PORTFOLIO_CODE,PRODUCT_TYPE,
1094 WEIGHTED_AVG_PRINCIPAL,ACCOUNT_NO,CREATED_ON)
1095 select l_ins_date,COMPANY_CODE,CURRENCY,DEAL_TYPE,NULL,
1096 CONTRA_CCY,CURRENCY_COMBINATION,DEAL_SUBTYPE,
1097 0,0,0,0,
1098 1,0,PARTY_CODE, PORTFOLIO_CODE,PRODUCT_TYPE,
1099 0,ACCOUNT_NO,l_run_date
1100 from XTR_COST_OF_FUNDS where as_at_date=l_prv_date;
1101 end if;
1102 l_ins_date :=l_ins_date+1;
1103 if l_ins_date >=l_date then
1104 exit;
1105 end if;
1106 END LOOP;
1107 else
1108 close get_prv_row;
1109 end if;
1110
1111 ----
1112 -- Only for CA, update amount,rate where as_at_date >=balance_date
1113 open get_ca_row;
1114 LOOP
1115 fetch get_ca_row into bal;
1116 open get_hce_rate(bal.currency);
1117 fetch get_hce_rate into l_hce_rate,l_year_basis;
1118 close get_hce_rate;
1119 exit WHEN get_ca_row%NOTFOUND;
1120
1121 update XTR_cost_of_funds
1122 set AVG_INTEREST_RATE = round(bal.transaction_rate,5),
1123 GROSS_PRINCIPAL = round(bal.amount,0),
1124 HCE_GROSS_PRINCIPAL = round(bal.amount/l_hce_rate,0),
1125 WEIGHTED_AVG_PRINCIPAL = round(bal.amount*bal.transaction_rate/100,0),
1126 INTEREST = round(no_of_days*bal.amount*bal.transaction_rate/(100*l_year_basis),2),
1127 HCE_INTEREST = round((no_of_days*bal.amount*bal.transaction_rate/(100*l_year_basis))/l_hce_rate,2)
1128 where as_at_date >= bal.amount_date and as_at_date <>l_date
1129 and deal_type='CA'
1130 and deal_subtype=bal.deal_subtype
1131 and currency=bal.currency
1132 and company_code=bal.company_code
1133 and account_no=bal.account_no
1134 and nvl(portfolio_code,'@#@')=nvl(bal.portfolio_code,'@#@')
1135 and nvl(product_type,'@#@')=nvl(bal.product_type,'@#@')
1136 and nvl(party_code,'@#@')=nvl(bal.limit_party,'@#@');
1137
1138 ---- for today's
1139
1140 update XTR_cost_of_funds
1141 set AVG_INTEREST_RATE = round(bal.transaction_rate,5),
1142 GROSS_PRINCIPAL = round(bal.amount,0),
1143 HCE_GROSS_PRINCIPAL = round(bal.amount/l_hce_rate,0),
1147 where as_at_date =l_date
1144 WEIGHTED_AVG_PRINCIPAL = round(bal.amount*bal.transaction_rate/100,0),
1145 INTEREST = round(no_of_days*bal.amount*bal.transaction_rate/(100*l_year_basis),2),
1146 HCE_INTEREST = round((no_of_days*bal.amount*bal.transaction_rate/(100*l_year_basis))/l_hce_rate,2)
1148 and deal_type='CA'
1149 and deal_subtype=bal.deal_subtype
1150 and currency=bal.currency
1151 and company_code=bal.company_code
1152 and account_no=bal.account_no
1153 and nvl(portfolio_code,'@#@')=nvl(bal.portfolio_code,'@#@')
1154 and nvl(product_type,'@#@')=nvl(bal.product_type,'@#@')
1155 and nvl(party_code,'@#@')=nvl(bal.limit_party,'@#@');
1156 if SQL%NOTFOUND then
1157 insert into XTR_COST_OF_FUNDS
1158 (AS_AT_DATE,COMPANY_CODE,CURRENCY,DEAL_TYPE,AVG_INTEREST_RATE,
1159 CONTRA_CCY,CURRENCY_COMBINATION,DEAL_SUBTYPE,
1160 GROSS_PRINCIPAL,HCE_GROSS_PRINCIPAL,INTEREST,NO_OF_DAYS,NO_OF_DEALS,PARTY_CODE,
1161 PORTFOLIO_CODE,PRODUCT_TYPE,WEIGHTED_AVG_PRINCIPAL,ACCOUNT_NO,CREATED_ON)
1162 values(l_date,bal.COMPANY_CODE,bal.CURRENCY,bal.DEAL_TYPE,round(bal.TRANSACTION_RATE,5),
1163 bal.CONTRA_CCY,bal.CURRENCY_COMBINATION,bal.DEAL_SUBTYPE,
1164 round(bal.AMOUNT,0),round(bal.HCE_AMOUNT,0),
1165 round(bal.AMOUNT*bal.TRANSACTION_RATE/(100*l_year_basis),4),
1166 1,1,bal.LIMIT_PARTY,bal.PORTFOLIO_CODE,bal.PRODUCT_TYPE,
1167 round(bal.AMOUNT*bal.TRANSACTION_RATE/(100*l_year_basis),2),bal.ACCOUNT_NO,l_run_date);
1168 end if;
1169 END LOOP;
1170 close get_ca_row;
1171
1172 --
1173 -- update transaction_rate for TMM in mirror_dda
1174 open get_tmm_row;
1175 LOOP
1176 fetch get_tmm_row into l_rowid,l_deal_no;
1177 exit WHEN get_tmm_row%NOTFOUND;
1178 l_transaction_rate :=null;
1179 open get_tmm_rate;
1180 fetch get_tmm_rate into l_transaction_rate;
1181 close get_tmm_rate;
1182 if l_transaction_rate is not null then
1183 update XTR_mirror_dda_limit_row
1184 set transaction_rate=l_transaction_rate
1185 where rowid=l_rowid;
1186 end if;
1187 END LOOP;
1188 close get_tmm_row;
1189 --
1190
1191 --insert into COF
1192 open get_cof;
1193 LOOP
1194 fetch get_cof into cof;
1195 exit WHEN get_cof%NOTFOUND;
1196 open get_hce_rate(cof.currency);
1197 fetch get_hce_rate into l_hce_rate,l_year_basis;
1198 close get_hce_rate;
1199
1200 if cof.deal_type in('ONC','CA','IG','NI','TMM','BOND','DEB','IRS','CMF') then
1201 if nvl(cof.gross_principal,0) <>0 then
1202 l_avg_rate :=round(100*cof.weighted_amt/cof.gross_principal,5);
1203 else
1204 l_avg_rate :=null;
1205 end if;
1206 l_interest :=round(cof.gross_principal*l_avg_rate/(100*l_year_basis),2);
1207 else
1208 l_interest := null;
1209 l_avg_rate := null;
1210 if cof.deal_type in('FX','FXO') then
1211 if nvl(cof.gross_principal,0) <>0 then
1212 l_avg_rate := round(cof.weighted_amt/cof.gross_principal,5);
1213 else
1214 l_avg_rate := null;
1215 end if;
1216 end if;
1217 end if;
1218 --
1219 update XTR_cost_of_funds
1220 set AVG_INTEREST_RATE = l_avg_rate,
1221 GROSS_PRINCIPAL = cof.gross_principal,
1222 HCE_GROSS_PRINCIPAL = round(cof.GROSS_PRINCIPAL/l_hce_rate,0),
1223 WEIGHTED_AVG_PRINCIPAL = cof.weighted_amt,
1224 INTEREST = l_interest,
1225 HCE_INTEREST =round(l_interest/l_hce_rate,2),
1226 NO_OF_DAYS =1,
1227 NO_OF_DEALS=cof.NO_OF_DEALS
1228 where as_at_date =l_date
1229 and company_code=cof.company_code
1230 and deal_type=cof.deal_type
1231 and deal_subtype=cof.subtype
1232 and currency=cof.currency
1233 and nvl(contra_ccy,'@#@')=nvl(cof.contra_ccy,'@#@')
1234 and nvl(currency_combination,'@#@')=nvl(cof.currency_combination,'@#@')
1235 and nvl(portfolio_code,'@#@')=nvl(cof.portfolio_code,'@#@')
1236 and nvl(product_type,'@#@')=nvl(cof.product_type,'@#@')
1237 and nvl(party_code,'@#@')=nvl(cof.limit_party,'@#@');
1238 if SQL%NOTFOUND then
1239 insert into XTR_COST_OF_FUNDS
1240 (AS_AT_DATE,COMPANY_CODE,CURRENCY,DEAL_TYPE,AVG_INTEREST_RATE,
1241 CONTRA_CCY,CURRENCY_COMBINATION,DEAL_SUBTYPE,
1242 GROSS_PRINCIPAL,HCE_GROSS_PRINCIPAL,INTEREST,HCE_INTEREST,
1243 NO_OF_DAYS,NO_OF_DEALS,PARTY_CODE, PORTFOLIO_CODE,PRODUCT_TYPE,
1244 WEIGHTED_AVG_PRINCIPAL,ACCOUNT_NO,CREATED_ON)
1245 values(l_date,cof.COMPANY_CODE,cof.CURRENCY,cof.DEAL_TYPE,
1246 round(l_avg_rate,5),cof.CONTRA_CCY,cof.CURRENCY_COMBINATION,
1247 cof.SUBTYPE,round(cof.GROSS_PRINCIPAL,0),round(cof.GROSS_PRINCIPAL/l_hce_rate,0),
1248 l_interest,round(l_interest/l_hce_rate,2),1,cof.NO_OF_DEALS,cof.LIMIT_PARTY,
1249 cof.PORTFOLIO_CODE,cof.PRODUCT_TYPE,cof.weighted_amt,'%',l_run_date);
1250 end if;
1251 end LOOP;
1252 close get_cof;
1253 ----commit;
1254 end SUMMARY_COST_OF_FUNDS;
1255 ---------------------------------------------------------------------
1256 PROCEDURE MAINTAIN_COST_OF_FUNDS(
1257 L_REF_DATE IN date,
1258 L_COMPANY_CODE IN VARCHAR2,
1259 L_CURRENCY IN VARCHAR2,
1260 L_DEAL_TYPE IN VARCHAR2,
1261 L_DEAL_SUBTYPE IN VARCHAR2,
1262 L_PRODUCT_TYPE IN VARCHAR2,
1263 L_PORTFOLIO_CODE IN VARCHAR2,
1264 L_PARTY_CODE IN VARCHAR2,
1265 L_CONTRA_CCY IN VARCHAR2,
1269 L_TRANSACTION_RATE IN NUMBER,
1266 L_CURRENCY_COMBINATION IN VARCHAR2,
1267 L_ACCOUNT IN VARCHAR2,
1268 L_AMOUNT_DATE IN DATE,
1270 L_AMOUNT IN NUMBER,
1271 L_AMOUNT_INDIC IN NUMBER,
1272 L_ACTION_INDIC IN NUMBER) is
1273
1274 ---
1275 cursor GET_DIST_DATE is
1276 select DISTINCT AS_AT_DATE
1277 from XTR_COST_OF_FUNDS
1278 where AS_AT_DATE >= L_REF_DATE
1279 and (DEAL_TYPE in('ONC','CA') or AS_AT_DATE < L_AMOUNT_DATE)
1280 ORDER BY AS_AT_DATE ASC;
1281
1282 l_dist_date date;
1283
1284 cursor DET is
1285 select GROSS_PRINCIPAL,
1286 GROSS_PRINCIPAL * AVG_INTEREST_RATE / decode(DEAL_TYPE,'FX',1,'FXO',1,100),
1287 NO_OF_DAYS,ROWID
1288 from XTR_COST_OF_FUNDS
1289 where AS_AT_DATE = l_dist_date
1290 and DEAL_TYPE = L_DEAL_TYPE
1291 and COMPANY_CODE = L_COMPANY_CODE
1292 and CURRENCY = L_CURRENCY
1293 and nvl(CONTRA_CCY,'%')=nvl(L_CONTRA_CCY,'%')
1294 and nvl(CURRENCY_COMBINATION,'%')=nvl(L_CURRENCY_COMBINATION,'%')
1295 and DEAL_SUBTYPE = L_DEAL_SUBTYPE
1296 and nvl(PRODUCT_TYPE,'%') = nvl(L_PRODUCT_TYPE,'%')
1297 and nvl(PORTFOLIO_CODE,'%') = nvl(L_PORTFOLIO_CODE,'%')
1298 and nvl(PARTY_CODE,'%') = nvl(L_PARTY_CODE,'%')
1299 and nvl(ACCOUNT_NO,'%') = nvl(L_ACCOUNT,'%');
1300 --
1301 cursor get_prv_date is
1302 select AS_AT_DATE
1303 from XTR_COST_OF_FUNDS
1304 where AS_AT_DATE <= L_REF_DATE
1305 order by as_at_date desc;
1306
1307 --
1308 cursor get_nxt_date is
1309 select AS_AT_DATE
1310 from XTR_COST_OF_FUNDS
1311 where AS_AT_DATE > L_REF_DATE
1312 order by as_at_date asc;
1313 l_nxt_date date;
1314 --
1315 cursor get_hce_rate(l_ccy varchar2) is
1316 select nvl(hce_rate,1),year_basis
1317 from XTR_master_currencies
1318 where currency = l_ccy;
1319 --
1320 l_hce_rate number;
1321 l_year_basis number;
1322 --
1323 l_prv_date date;
1324
1325 cursor get_cof_row is
1326 select rowid row_id,deal_type,currency,gross_principal,avg_interest_rate
1327 from XTR_cost_of_funds
1328 where as_at_date = l_prv_date;
1329
1330 cursor get_no_of_days is
1331 select decode(AS_AT_DATE,L_REF_DATE,no_of_days,as_at_date - L_REF_DATE)
1332 from XTR_COST_OF_FUNDS
1333 where AS_AT_DATE >= L_REF_DATE
1334 order by AS_AT_DATE;
1335
1336 cursor get_prv_row is
1337 select AS_AT_DATE,CURRENCY,DEAL_TYPE,GROSS_PRINCIPAL,AVG_INTEREST_RATE,ROWID
1338 from XTR_COST_OF_FUNDS
1339 where AS_AT_DATE = L_REF_DATE
1340 and (DEAL_TYPE in('ONC','CA') or AS_AT_DATE < L_AMOUNT_DATE)
1341 and DEAL_TYPE = L_DEAL_TYPE
1342 and COMPANY_CODE = L_COMPANY_CODE
1343 and CURRENCY = L_CURRENCY
1344 and nvl(CONTRA_CCY,'%')=nvl(L_CONTRA_CCY,'%')
1345 and nvl(CURRENCY_COMBINATION,'%')=nvl(L_CURRENCY_COMBINATION,'%')
1346 and DEAL_SUBTYPE = L_DEAL_SUBTYPE
1347 and nvl(PRODUCT_TYPE,'%') = nvl(L_PRODUCT_TYPE,'%')
1348 and nvl(PORTFOLIO_CODE,'%') = nvl(L_PORTFOLIO_CODE,'%')
1349 and nvl(PARTY_CODE,'%') = nvl(L_PARTY_CODE,'%')
1350 and nvl(ACCOUNT_NO,'%') = nvl(L_ACCOUNT,'%')
1351 order by AS_AT_DATE desc;
1352
1353 l_dummy NUMBER;
1354 l_as_at_date DATE;
1355 l_rowid VARCHAR2(30);
1356 l_gross NUMBER;
1357 l_gross_hce NUMBER;
1358 l_rate NUMBER;
1359 l_wavg NUMBER;
1360 l_interest NUMBER;
1361 l_interest_hce NUMBER;
1362 l_no_of_days NUMBER;
1363 c_no_of_days NUMBER;
1364 c_interest NUMBER;
1365 c_interest_hce NUMBER;
1366
1367 l_days NUMBER;
1368 l_dummy_code NUMBER;
1369 l_dummy_buf VARCHAR2(100);
1370
1371 p_ccy VARCHAR2(15);
1372 p_avg_rate NUMBER;
1373 p_deal_type VARCHAR2(7);
1374 --
1375 cursor HCE is
1376 select round(l_interest / s.HCE_RATE,2),
1377 round(l_gross / s.HCE_RATE,0)
1378 from XTR_MASTER_CURRENCIES s
1379 where s.CURRENCY = L_CURRENCY;
1380 --
1381
1382 begin
1383 if nvl(L_AMOUNT,0) <>0 and L_REF_DATE <trunc(sysdate) then
1384
1385 l_prv_date :=null;
1386 open get_prv_date;
1387 fetch get_prv_date into l_prv_date;
1388 close get_prv_date;
1389
1390 if l_prv_date <>L_REF_DATE then
1391 XTR_EXP_SUMM_P.SUMMARY_COST_OF_FUNDS(l_dummy_buf, l_dummy_code);
1392 l_prv_date :=L_REF_DATE;
1393 end if;
1394
1395 if l_prv_date <> L_REF_DATE or l_prv_date is null then
1396 if l_prv_date is not null then
1397 l_nxt_date :=null;
1398
1399 open get_nxt_date;
1400 fetch get_nxt_date into l_nxt_date;
1401 close get_nxt_date;
1402
1403 l_nxt_date :=nvl(l_nxt_date,trunc(sysdate));
1404
1405 -- update previous row's no of days and interests;
1406 --
1407 l_no_of_days :=L_REF_DATE-l_prv_date;
1408 c_no_of_days :=l_nxt_date-l_REF_DATE;
1409 --
1410 for c in get_cof_row loop
1411 update XTR_cost_of_funds
1412 set no_of_days =l_no_of_days
1413 where rowid=c.row_id;
1414 --
1415 if c.deal_type in('ONC','CA','IG','NI','TMM','BOND','DEB','IRS','CMF') then
1416
1417 open get_hce_rate(c.currency);
1418 fetch get_hce_rate into l_hce_rate,l_year_basis;
1419 close get_hce_rate;
1420 l_interest :=round((c.gross_principal*l_no_of_days*c.avg_interest_rate)/(l_year_basis*100),2);
1424 c_interest_hce := round((c.gross_principal*c_no_of_days*c.avg_interest_rate)/(l_hce_rate*l_year_basis*100),2);
1421 l_interest_hce := round((c.gross_principal*l_no_of_days*c.avg_interest_rate)/(l_hce_rate*l_year_basis*100),2);
1422
1423 c_interest :=round((c.gross_principal*c_no_of_days*c.avg_interest_rate)/(l_year_basis*100),2);
1425 else
1426 l_interest :=null;
1427 l_interest_hce :=null;
1428 c_interest :=null;
1429 c_interest_hce :=null;
1430 end if;
1431
1432 update XTR_cost_of_funds
1433 set interest = l_interest,
1434 hce_interest = l_interest_hce,
1435 no_of_days =l_no_of_days
1436 where rowid = c.row_id;
1437
1438 insert into XTR_COST_OF_FUNDS
1439 (as_at_date,company_code,currency,deal_type,
1440 deal_subtype,party_code,portfolio_code,product_type,
1441 gross_principal,hce_gross_principal,
1442 weighted_avg_principal,avg_interest_rate,interest,
1443 hce_interest,no_of_days,no_of_deals,contra_ccy,
1444 currency_combination,account_no,created_on)
1445 select L_REF_DATE,company_code,currency,deal_type,
1446 deal_subtype,party_code,portfolio_code,product_type,
1447 gross_principal,hce_gross_principal,
1448 weighted_avg_principal,avg_interest_rate,c_interest,
1449 c_interest_hce,c_no_of_days,no_of_deals,contra_ccy,
1450 currency_combination,account_no,L_REF_DATE
1451 from XTR_cost_of_funds
1452 where rowid = c.row_id;
1453 end loop;
1454
1455 end if;
1456 end if;
1457
1458 ---
1459 l_no_of_days :=null;
1460
1461 open get_no_of_days;
1462 fetch get_no_of_days into l_no_of_days;
1463 if get_no_of_days%NOTFOUND then
1464 l_no_of_days := trunc(sysdate) - l_ref_date;
1465 end if;
1466 close get_no_of_days;
1467
1468 l_no_of_days :=nvl(l_no_of_days,0);
1469
1470 l_as_at_date :=null;
1471 l_rowid :=null;
1472
1473 open get_prv_row;
1474 fetch get_prv_row into l_as_at_date,p_ccy,p_deal_type,l_gross,p_avg_rate,l_rowid;
1475 if get_prv_row%NOTFOUND then
1476 insert into XTR_COST_OF_FUNDS
1477 (as_at_date,company_code,currency,deal_type,
1478 deal_subtype,party_code,portfolio_code,product_type,
1479 gross_principal,hce_gross_principal,
1480 weighted_avg_principal,avg_interest_rate,interest,
1481 hce_interest,no_of_days,no_of_deals,contra_ccy,
1482 currency_combination,account_no,created_on)
1483 values
1484 (L_REF_DATE,L_COMPANY_CODE,L_CURRENCY,L_DEAL_TYPE,
1485 L_DEAL_SUBTYPE,L_PARTY_CODE,L_PORTFOLIO_CODE,
1486 L_PRODUCT_TYPE,0,0,0,0,0,
1487 0,l_no_of_days,0,L_CONTRA_CCY,L_CURRENCY_COMBINATION,L_ACCOUNT,
1488 trunc(sysdate));
1489 end if;
1490 close get_prv_row;
1491 ---
1492
1493 open get_hce_rate(L_CURRENCY);
1494 fetch get_hce_rate into l_hce_rate,l_year_basis;
1495 close get_hce_rate;
1496
1497 l_dist_date :=null;
1498 open GET_DIST_DATE;
1499 LOOP
1500 fetch GET_DIST_DATE into l_dist_date;
1501 exit when GET_DIST_DATE%NOTFOUND;
1502 l_gross :=null;
1503 l_rate :=null;
1504 l_wavg :=null;
1505 l_days :=null;
1506 l_rowid :=null;
1507 open DET;
1508 fetch DET INTO l_gross,l_wavg,l_days,l_rowid;
1509 close DET;
1510 l_gross := round(nvl(l_gross,0) + nvl(L_ACTION_INDIC,1)*nvl(L_AMOUNT,0)*nvl(L_AMOUNT_INDIC,1),0);
1511 if L_DEAL_TYPE in('FX','FXO') then
1512 l_wavg := round(nvl(l_wavg,0) + nvl(L_ACTION_INDIC,1)*nvl(L_AMOUNT,0)*nvl(L_AMOUNT_INDIC,1)*nvl(L_TRANSACTION_RATE,0),0);
1513 if nvl(l_gross,0) <>0 then
1514 l_rate :=round(l_wavg / l_gross,5);
1515 else
1516 l_rate :=null;
1517 end if;
1518 l_interest :=null;
1519 else
1520 l_wavg := round(nvl(l_wavg,0) + nvl(L_ACTION_INDIC,1)*nvl(L_AMOUNT,0)*nvl(L_AMOUNT_INDIC,1)*nvl(L_TRANSACTION_RATE,0)/100,0);
1521 if nvl(l_gross,0) <>0 then
1522 l_rate := round(l_wavg / l_gross*100,5);
1523 else
1524 l_rate :=null;
1525 end if;
1526 l_interest :=round((nvl(l_days,0)*l_wavg)/l_year_basis,2);
1527 end if;
1528 open HCE;
1529 fetch HCE INTO l_interest_hce,l_gross_hce;
1530 close HCE;
1531
1532 -- insert / update record
1533 /*
1534 if nvl(l_gross,0)=0 then
1535 delete from XTR_COST_OF_FUNDS
1536 where rowid=l_rowid;
1537 else
1538 */
1539 update XTR_COST_OF_FUNDS
1540 set GROSS_PRINCIPAL = round(l_gross,0),
1541 HCE_GROSS_PRINCIPAL = round(l_gross_hce,0),
1542 AVG_INTEREST_RATE = round(l_rate,5),
1543 HCE_INTEREST = round(l_interest_hce,2),
1544 WEIGHTED_AVG_PRINCIPAL = round(l_wavg,0),
1545 INTEREST = round(l_interest,2),
1546 NO_OF_DEALS=nvl(NO_OF_DEALS,0)+L_ACTION_INDIC
1547 where rowid=l_rowid;
1548 if SQL%NOTFOUND then
1549 -- insert new row
1550 insert into XTR_COST_OF_FUNDS
1551 (as_at_date,company_code,currency,deal_type,
1552 deal_subtype,party_code,portfolio_code,product_type,
1553 gross_principal,hce_gross_principal,
1554 weighted_avg_principal,avg_interest_rate,interest,
1555 hce_interest,no_of_days,no_of_deals,contra_ccy,
1556 currency_combination,account_no,created_on)
1557 values
1558 (nvl(l_dist_date,L_REF_DATE),L_COMPANY_CODE,L_CURRENCY,L_DEAL_TYPE,
1559 L_DEAL_SUBTYPE,L_PARTY_CODE,L_PORTFOLIO_CODE,
1563 end if;
1560 L_PRODUCT_TYPE,round(l_gross,0),round(l_gross_hce,0),round(l_wavg,0),round(l_rate,5),round(l_interest,2),
1561 round(l_interest_hce,2),nvl(l_days,0),1,L_CONTRA_CCY,L_CURRENCY_COMBINATION,L_ACCOUNT,
1562 trunc(sysdate));
1564 --- end if;
1565 end loop;
1566 close GET_DIST_DATE;
1567 end if;
1568 end MAINTAIN_COST_OF_FUNDS;
1569 ----------------------------------------------------------------------------------------
1570 end XTR_EXP_SUMM_P;