eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.XTR_RATE_CHANGE

Source


1 PACKAGE BODY XTR_RATE_CHANGE AS
2 /* $Header: xtrpdrtb.pls 120.3 2005/06/29 10:48:38 rjose ship $ */
3 
4 
5 
6 PROCEDURE PRODUCT_RATE_CHANGE(
7 	errbuf       			OUT NOCOPY VARCHAR2,
8 	retcode      			OUT NOCOPY VARCHAR2,
9 	p_effective_from_date		IN	VARCHAR2,
10  	p_eff_from_next_rollover_yn	IN	VARCHAR2,
11 	p_new_interest_rate		IN	VARCHAR2,
12 	p_change_pi_yn			IN	VARCHAR2,
13 	p_deal_subtype			IN	VARCHAR2,
14 	p_payment_schedule_code		IN	VARCHAR2,
15 	p_currency			IN	VARCHAR2,
16 	p_min_balance			IN	NUMBER,
17 	p_max_balance			IN	NUMBER)
18 IS
19 
20   g_expected_balance_bf		NUMBER;
21   g_balance_out_bf		NUMBER;
22   g_accum_interest_bf		NUMBER;
23   g_principal_adjust		NUMBER;
24   l_new_pi_amount_due 		NUMBER;
25   l_effective_from_date		DATE;
26   l_effective_date		DATE;
27   l_created_on    		DATE;
28   p_created_by    		VARCHAR2(30);
29   CHK_DATE			VARCHAR2(1) := 'N';
30   l_pi_amount_due 		NUMBER;
31   l_pi_amount_received 		NUMBER;
32   l_tran_num      		NUMBER;
33   l_interest_rate 		NUMBER;
34   l_maturity_date		DATE;
35   l_row_inserted		VARCHAR2(1) := null;
36 
37   cursor DEAL_CUR is
38      select 	DEAL_NO, PAYMENT_SCHEDULE_CODE, DEAL_TYPE,
39 		DEAL_SUBTYPE, PRODUCT_TYPE, INTEREST_RATE,
40 		PI_AMOUNT_DUE, CURRENCY, SETTLE_DATE,
41 		PORTFOLIO_CODE,COMPANY_CODE,DEAL_DATE,
42       		CPARTY_CODE,CLIENT_CODE,DEALER_CODE
43      from XTR_DEALS_V d
44      where d.DEAL_TYPE = 'RTMM'
45      and   d.DEAL_SUBTYPE = nvl(p_deal_subtype, d.DEAL_SUBTYPE)
46      and   d.CURRENCY = nvl(p_currency, d.CURRENCY)
47      and   d.PAYMENT_SCHEDULE_CODE = nvl(p_payment_schedule_code, d.PAYMENT_SCHEDULE_CODE)
48      and   d.STATUS_CODE = 'CURRENT'
49      and   exists( select 'ANY TRANS'
50 		   from XTR_ROLLOVER_TRANSACTIONS_V  t
51     		   where  nvl(t.STATUS_CODE, 'CURRENT') = 'CURRENT'
52     		   and    t.BALANCE_OUT between nvl(p_min_balance, 0) and
53 			       nvl(p_max_balance, t.BALANCE_OUT + 1)
54     		   and    t.SETTLE_DATE is NULL
55     		   and    t.START_DATE >= l_effective_from_date
56 		   and    t.START_DATE >= nvl(d.SETTLE_DATE, t.START_DATE)
57 	           --and    nvl(RATE_EFFECTIVE_CREATED,l_effective_from_date )
58                    --	<= l_effective_from_date
59     		   and    t.DEAL_NUMBER = d.DEAL_NO);
60 
61   cursor CHECK_ON_ROLLOVER(p_deal_no NUMBER, p_date DATE) is
62 	select 'Y'
63 	from XTR_ROLLOVER_TRANSACTIONS
64 	where DEAL_NUMBER = p_deal_no
65 	and   START_DATE = p_date;
66 
67   cursor T_NOS(p_deal_no NUMBER) is
68   	select nvl(max(TRANSACTION_NUMBER),0) + 1
69    	from XTR_ROLLOVER_TRANSACTIONS_V
70    	where DEAL_NUMBER = p_deal_no;
71 
72   cursor THIS_ROW(p_deal_no NUMBER, p_date DATE) is
73   	select 	PI_AMOUNT_DUE,
74 		INTEREST_RATE,
75 		PI_AMOUNT_RECEIVED,
76 		MATURITY_DATE
77    	from XTR_ROLLOVER_TRANSACTIONS_V
78    	where DEAL_NUMBER = p_deal_no
79    	and START_DATE < p_date
80     	and MATURITY_DATE >= p_date
81    	and nvl(PI_AMOUNT_DUE,0) <> 0
82    	order by START_DATE desc,TRANSACTION_NUMBER desc;
83 
84  cursor DEALER is
85   select dealer_code
86   from   xtr_dealer_codes_v
87   where  user_id = fnd_global.user_id;
88 
89 
90 BEGIN
91   --cep_standard.enable_debug;
92   IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
93      xtr_debug_pkg.debug('XTR_RATE_CHANGE.PRODUCT_RATE_CHANGE');
94      xtr_debug_pkg.debug('PRODUCT_RATE_CHANGE: ' || 'p_effective_from_date  = ' ||p_effective_from_date );
95      xtr_debug_pkg.debug('PRODUCT_RATE_CHANGE: ' || 'p_deal_subtype  = ' ||p_deal_subtype );
96      xtr_debug_pkg.debug('PRODUCT_RATE_CHANGE: ' || 'p_payment_schedule_code  = ' ||p_payment_schedule_code );
97      xtr_debug_pkg.debug('PRODUCT_RATE_CHANGE: ' || 'p_currency  = ' ||p_currency );
98      xtr_debug_pkg.debug('PRODUCT_RATE_CHANGE: ' || 'p_min_balance  = ' ||p_min_balance );
99      xtr_debug_pkg.debug('PRODUCT_RATE_CHANGE: ' || 'p_max_balance  = ' ||p_max_balance );
100      xtr_debug_pkg.debug('PRODUCT_RATE_CHANGE: ' || 'p_change_pi_yn	  = ' ||p_change_pi_yn	 );
101      xtr_debug_pkg.debug('PRODUCT_RATE_CHANGE: ' || 'p_change_pi_yn	  = ' ||p_change_pi_yn	 );
102      xtr_debug_pkg.debug('PRODUCT_RATE_CHANGE: ' || 'p_new_interest_rate  = ' ||p_new_interest_rate );
103   END IF;
104 
105   open DEALER;
106   fetch DEALER into p_created_by;
107   if DEALER%NOTFOUND then
108      p_created_by := null;
109   end if;
110   close DEALER;
111   l_created_on := sysdate;
112 
113   l_effective_from_date := to_date(
114 	to_date(p_effective_from_date, 'YYYY/MM/DD HH24:MI:SS'), 'DD-MON-RR');
115 
116   IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
117      xtr_debug_pkg.debug('PRODUCT_RATE_CHANGE: ' || 'l_effective_from_date  = ' ||l_effective_from_date );
118   END IF;
119 
120   FOR deal in DEAL_CUR LOOP
121     IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
122        xtr_debug_pkg.debug('PRODUCT_RATE_CHANGE: ' || 'deal_no  = ' ||deal.DEAL_NO );
123     END IF;
124 
125 ----IF deal.DEAL_NO = 13135 THEN ----------------------------
126 
127     if nvl(deal.SETTLE_DATE, l_effective_from_date) > l_effective_from_date then
128       l_effective_date := deal.SETTLE_DATE;
129     else
130       l_effective_date := l_effective_from_date;
131     end if;
132 
133     if nvl(p_change_pi_yn, 'N') = 'Y' then
134       XTR_RATE_CHANGE.RECALC_PI_AMOUNT( deal.DEAL_NO,
135 			p_new_interest_rate,
136 			l_effective_date,
137 			p_eff_from_next_rollover_yn,
138     			l_new_pi_amount_due);
139     else
140       l_new_pi_amount_due := null;
141     end if;
142 
143     update XTR_ROLLOVER_TRANSACTIONS_V
144     set    INTEREST_RATE          = p_new_interest_rate,
145            RATE_EFFECTIVE_CREATED = l_created_on,  -- AW 7/15 sysdate,
146  	   PI_AMOUNT_DUE          = nvl(l_new_pi_amount_due, PI_AMOUNT_DUE)
147     where  DEAL_NUMBER            = deal.DEAL_NO
148     and    nvl(STATUS_CODE, 'CURRENT') = 'CURRENT'
149     and    (BALANCE_OUT between nvl(p_min_balance, 0) and
150 			       nvl(p_max_balance, BALANCE_OUT + 1))
151     and    SETTLE_DATE is NULL
152     and    START_DATE >= l_effective_date;
153     -- and    nvl(RATE_EFFECTIVE_CREATED,l_effective_from_date )
154     --             <= l_effective_from_date;
155 
156     if SQL%FOUND then
157       update XTR_DEALS
158       set INTEREST_RATE = p_new_interest_rate
159       where DEAL_NO = deal.DEAL_NO;
160     end if;
161 
162     open CHECK_ON_ROLLOVER(deal.DEAL_NO, l_effective_date);
163     fetch CHECK_ON_ROLLOVER into CHK_DATE;
164     close CHECK_ON_ROLLOVER;
165 
166     if nvl(p_eff_from_next_rollover_yn, 'N') = 'N' and nvl(CHK_DATE, 'N') = 'N' then
167 
168       open T_NOS(deal.DEAL_NO);
169       fetch T_NOS INTO l_tran_num;
170       close T_NOS;
171 
172       open THIS_ROW(deal.DEAL_NO, l_effective_date);
173       fetch THIS_ROW INTO 	l_pi_amount_due,
174 				l_interest_rate,
175 				l_pi_amount_received,
176 				l_maturity_date;
177       close THIS_ROW;
178 
179       if l_tran_num is NOT NULL then
180         insert into XTR_ROLLOVER_TRANSACTIONS_V
181      		(DEAL_NUMBER,
182 		TRANSACTION_NUMBER,
183 		DEAL_TYPE,
184 		RATE_FIXING_DATE,
185 		START_DATE,
186 		MATURITY_DATE,
187       		INTEREST_RATE,
188 		NO_OF_DAYS,
189 		PI_AMOUNT_DUE,
190 		BALANCE_OUT_BF,
191       		BALANCE_OUT,
192 		CREATED_BY,
193 		CREATED_ON,
194 		PRINCIPAL_ADJUST,
195 		STATUS_CODE,
196       		PORTFOLIO_CODE,
197 		CURRENCY,
198 		DEAL_SUBTYPE,
199 		COMPANY_CODE,
200 		DEAL_DATE,
201 		PRODUCT_TYPE,
202 		CPARTY_CODE,
203       		CLIENT_CODE,
204 		DEALER_CODE)
205     	  values
206      		(deal.DEAL_NO,
207 		l_tran_num,
208 		'RTMM',
209 		l_effective_date,
210 		l_effective_date,
211       		l_maturity_date,
212 		p_new_interest_rate,
213 		(l_maturity_date - l_effective_date),
214       		l_pi_amount_due,
215       		0,
216 		0,
217 		'-1',
218 		SYSDATE,
219 		0,
220       		'CURRENT',
221 		deal.PORTFOLIO_CODE,
222 		deal.CURRENCY,
223 		deal.DEAL_SUBTYPE,
224 		deal.COMPANY_CODE,
225 		deal.DEAL_DATE,
226       		deal.PRODUCT_TYPE,
227 		deal.CPARTY_CODE,
228 		deal.CLIENT_CODE,
229 		deal.DEALER_CODE);
230         end if;
231 
232        l_row_inserted := 'Y';
233 
234     end if;
235 
236     /*  AW 7/15 Bug 914129  Should not allow to update previous actions for this deal !!!
237     update XTR_term_actions_V
238     set NEW_INTEREST_RATE      = p_new_interest_rate,
239         EFFECTIVE_FROM_DATE    = sysdate,
240         CREATED_ON	       = SYSDATE,
241         CREATED_BY             = '-1'
242     where deal_no = deal.DEAL_NO;
243     if SQL%NOTFOUND then
244     */
245       insert into XTR_term_actions_V(
246         	DEAL_NO,
247         	NEW_INTEREST_RATE,
248         	EFFECTIVE_FROM_DATE,
249         	CREATED_ON,
250        		CREATED_BY,
251                 MASS_RATE_UPDATE)
252       values   (deal.DEAL_NO,
253 		p_new_interest_rate,
254 		l_effective_date,  --  AW 7/15 Bug 914129 sysdate,
255 		l_created_on,      --  AW 7/15 Bug 914129 sysdate,
256 		p_created_by,
257                 'Y');
258     /*  AW 7/15 Bug 914129
259       select    deal.DEAL_NO,
260 		p_new_interest_rate,
261 		l_effective_date,  --  AW 7/15 Bug 914129 sysdate,
262 		l_created_on,      --  AW 7/15 Bug 914129 sysdate,
263 		'-1'
264       from dual;
265 
266     end if;
267     */
268 
269     XTR_CALC_P.RECALC_DT_DETAILS(
270               	     deal.DEAL_NO,
271                      l_row_inserted,
272                      l_effective_date,
273                      l_tran_num,
274 		     'N',
275 		     'N',
276 		     g_expected_balance_bf,
277                      g_balance_out_bf,
278                      g_accum_interest_bf,
279                      g_principal_adjust,
280                      null,
281                      null,
282                      null,
283                      null,
284                      null );
285 
286 
287 ----END IF; ---------------
288 
289   END LOOP;
290 
291  -- AW 7/27 939515
292   update XTR_RATE_SETS
293   set    CONCURRENT_REQUEST = 'Y'
294   where  EFFECTIVE_FROM           = to_date(p_effective_from_date,'YYYY/MM/DD')
295   and    RATE                     = p_new_interest_rate
296   and    DEAL_SUBTYPE             = p_deal_subtype
297   and    PRODUCT_TYPE             = p_payment_schedule_code
298   and    CURRENCY                 = p_currency
299   and    LOW_RANGE                = p_min_balance
300   and    HIGH_RANGE               = p_max_balance;
301   commit;
302 
303 END PRODUCT_RATE_CHANGE;
304 
305 PROCEDURE RECALC_PI_AMOUNT(
306 	p_deal_number			IN	NUMBER,
307 	p_new_interest_rate 		IN 	NUMBER,
308 	p_effective_from_date 		IN 	DATE,
309 	p_eff_from_next_rollover_yn  	IN	VARCHAR2,
310 	p_new_pi_amount_due 		OUT 	NOCOPY NUMBER)
311 IS
312   cursor DEAL_CUR is
313      select 	PAYMENT_SCHEDULE_CODE, DEAL_TYPE,DEAL_SUBTYPE,
314 		PRODUCT_TYPE, INTEREST_RATE, MATURITY_DATE,
315 		PI_AMOUNT_DUE, CURRENCY
316      from XTR_DEALS_V d
317      where d.DEAL_NO = p_deal_number;
318 
319   cursor THIS_ROW is
320   	select  START_DATE, MATURITY_DATE,
321 	        EXPECTED_BALANCE_BF,EXPECTED_BALANCE_OUT,
322 		PRINCIPAL_ADJUST,
323 	        INTEREST_RATE
324    	from  XTR_ROLLOVER_TRANSACTIONS_V
325    	where DEAL_NUMBER = p_deal_number
326    	and STATUS_CODE = 'CURRENT'
327    	and MATURITY_DATE >= p_effective_from_date
328  	and START_DATE < p_effective_from_date
329    	order by START_DATE asc,TRANSACTION_NUMBER asc;
330 
331   cursor START_ROW is
332   	select START_DATE,EXPECTED_BALANCE_BF, PRINCIPAL_ADJUST
333    	from  XTR_ROLLOVER_TRANSACTIONS_V
334    	where DEAL_NUMBER = p_deal_number
335    	and STATUS_CODE = 'CURRENT'
336    	and START_DATE >= p_effective_from_date
337    	order by START_DATE asc,TRANSACTION_NUMBER asc;
338 
339   l_payment_schedule_code	varchar2(7);
340   l_deal_type 			varchar2(7);
341   l_deal_subtype 		varchar2(7);
342   l_product_type 		varchar2(10);
343   l_interest_rate		NUMBER;
344   l_maturity_date 	 	DATE;
345   l_pi_amount_due		NUMBER;
346   l_currency 		        VARCHAR2(15);
347   l_start_date			DATE;
348   l_expected_balance_bf		NUMBER;
349   l_principal_adjust		NUMBER;
350   l_year_basis			NUMBER := 365;
351   l_this_start_date 		DATE;
352   l_this_maturity_date		DATE;
353   l_this_expected_balance_bf	NUMBER;
354   l_this_expected_balance_out   NUMBER;
355   l_this_principal_adjust	NUMBER;
356   l_this_interest_rate		NUMBER;
357 
358   cursor FREQ is
359   	select PAYMENT_FREQUENCY,
360 	JAN_YN,FEB_YN,MAR_YN,APR_YN,MAY_YN,JUN_YN,
361 	JUL_YN,AUG_YN,SEP_YN,OCT_YN,NOV_YN,DEC_YN
362    	from  XTR_PAYMENT_SCHEDULE_V
363    	where PAYMENT_SCHEDULE_CODE = l_payment_schedule_code
364    	and DEAL_TYPE = 'RTMM'
365    	and DEAL_SUBTYPE = l_deal_subtype;
366 
367   l_mth1           VARCHAR2(1);
368   l_mth2           VARCHAR2(1);
369   l_mth3           VARCHAR2(1);
370   l_mth4           VARCHAR2(1);
371   l_mth5           VARCHAR2(1);
372   l_mth6           VARCHAR2(1);
373   l_mth7           VARCHAR2(1);
374   l_mth8           VARCHAR2(1);
375   l_mth9           VARCHAR2(1);
376   l_mth10          VARCHAR2(1);
377   l_mth11          VARCHAR2(1);
378   l_mth12          VARCHAR2(1);
379   l_pymts_per_year NUMBER;
380   l_tot_pymts      NUMBER;
381 
382   cursor RND_YR is
383   	select ROUNDING_FACTOR
384    	from  XTR_MASTER_CURRENCIES_V
385    	where CURRENCY = l_currency;
386 
387   L_ROUND	 NUMBER;
388   L_PAYMENT_FREQ VARCHAR2(12);
389 
390 BEGIN
391 
392   open DEAL_CUR;
393   fetch DEAL_CUR into l_payment_schedule_code, l_deal_type, l_deal_subtype,
394 		      l_product_type, l_interest_rate,l_maturity_date,
395 		      l_pi_amount_due,l_currency;
396   close DEAL_CUR;
397 
398   open START_ROW;
399   fetch START_ROW into l_start_date, l_expected_balance_bf, l_principal_adjust;
400   close START_ROW;
401 
402   open THIS_ROW;
403   fetch THIS_ROW into  	l_this_start_date, l_this_maturity_date,
404 			l_this_expected_balance_bf, l_this_expected_balance_out,
405 			l_this_principal_adjust,l_this_interest_rate;
406   close THIS_ROW;
407 
408   open FREQ;
409   fetch FREQ into  L_PAYMENT_FREQ,l_mth1,l_mth2,l_mth3,l_mth4,l_mth5,
410 		   l_mth6,l_mth7,l_mth8,l_mth9,l_mth10,l_mth11,l_mth12;
411   close FREQ;
412 
413   if L_PAYMENT_FREQ = 'WEEKLY' then
414     l_pymts_per_year := 52;
415   elsif L_PAYMENT_FREQ = 'FORTNIGHTLY' then
416     l_pymts_per_year := 26;
417   elsif L_PAYMENT_FREQ = 'FOUR WEEKLY' then
418     l_pymts_per_year := 12;
419   elsif L_PAYMENT_FREQ = 'MONTHLY' then
420     l_pymts_per_year := 12;
421   elsif L_PAYMENT_FREQ = 'BI MONTHLY' then
422     l_pymts_per_year := 6;
423   elsif L_PAYMENT_FREQ = 'QUARTERLY' then
424     l_pymts_per_year := 4;
425   elsif L_PAYMENT_FREQ = 'SEMI ANNUAL' then
426     l_pymts_per_year := 2;
427   elsif L_PAYMENT_FREQ = 'ANNUAL' then
428     l_pymts_per_year := 1;
429   elsif L_PAYMENT_FREQ = 'AD HOC' then
430     l_pymts_per_year := 0;
431     if l_mth1 = 'Y' then
432       l_pymts_per_year := l_pymts_per_year + 1;
433     end if;
434     if l_mth2 = 'Y' then
435       l_pymts_per_year := l_pymts_per_year + 1;
436     end if;
437     if l_mth3 = 'Y' then
438       l_pymts_per_year := l_pymts_per_year + 1;
439     end if;
440     if l_mth4 = 'Y' then
441       l_pymts_per_year := l_pymts_per_year + 1;
442     end if;
443     if l_mth5 = 'Y' then
444       l_pymts_per_year := l_pymts_per_year + 1;
445     end if;
446     if l_mth6 = 'Y' then
447       l_pymts_per_year := l_pymts_per_year + 1;
448     end if;
449     if l_mth7 = 'Y' then
450       l_pymts_per_year := l_pymts_per_year + 1;
451     end if;
452     if l_mth8 = 'Y' then
453       l_pymts_per_year := l_pymts_per_year + 1;
454     end if;
455     if l_mth9 = 'Y' then
456       l_pymts_per_year := l_pymts_per_year + 1;
457     end if;
458     if l_mth10 = 'Y' then
459       l_pymts_per_year := l_pymts_per_year + 1;
460     end if;
461     if l_mth11 = 'Y' then
462       l_pymts_per_year := l_pymts_per_year + 1;
463     end if;
464     if l_mth12 = 'Y' then
465       l_pymts_per_year := l_pymts_per_year + 1;
466     end if;
467   else
468     l_pymts_per_year := 12;
469   end if;
470 
471   l_tot_pymts := round(l_pymts_per_year *
472      	  months_between(l_maturity_date, l_start_date)/12, 0);
473 
474   OPEN RND_YR;
475   FETCH RND_YR INTO L_ROUND;
476   CLOSE RND_YR;
477 
478   if nvl(p_eff_from_next_rollover_yn, 'N') = 'N' then
479 
480 	l_expected_balance_bf := l_expected_balance_bf +
481 		  nvl(round((nvl(l_this_expected_balance_bf, 0)+nvl(l_this_principal_adjust,0)) *
482 		  (p_new_interest_rate-l_this_interest_rate)*(l_this_maturity_date - p_effective_from_date )
483                   / (100 * l_year_basis),l_round),0);
484 
485   end if;
486 
487   select round((nvl(l_expected_balance_bf,0) + nvl(l_principal_adjust,0)) *
488    	  power((1 + (nvl(p_new_interest_rate, 0)/(l_pymts_per_year * 100))),l_tot_pymts)/
489  	((power((1 + (nvl(p_new_interest_rate, 0)/(l_pymts_per_year * 100))),l_tot_pymts) - 1) /
490               (nvl(p_new_interest_rate, 0)/(l_pymts_per_year * 100))),
491               NVL(l_round,2))
492   into p_new_pi_amount_due
493   from dual;
494 
495 END RECALC_PI_AMOUNT;
496 
497 END XTR_RATE_CHANGE;