[Home] [Help]
PACKAGE BODY: APPS.XTR_STOCK_POSITION_P
Source
1 PACKAGE BODY XTR_STOCK_POSITION_P as
2 /* $Header: xtrsposb.pls 120.1 2005/11/23 12:26:15 eaggarwa noship $ */
3 /* This files conatins three procedure to insert/update/delete rows from the
4 xtr_position_history table for the 'STOCK' deal.
5
6 1. Maintain_stk_position_history is called from the form when the
7 'BUY' stock deal is created or the deal status is set to cancelled
8
9 2. Snapshot_cost_of_funds fetches all the 'BUY' stock deals with status code as
10 current and call the procedure snapshot_stk_position_history. This procedure is
11 called from the form when the sell deal is created or when the concurrent
12 program -update average rates is run.
13
14 When this program is called from the 'FORM' the deal number is passed for which
15 the resale is being created and when called through the CP the deal number is
16 null.
17
18 3.Snapshot_stk_position_history recalculates the values and then insert/update
19 or delete rows from the xtr_postion_history table. This procedure is called by
20 snapshot_stk_cost_of_funds
21
22 */
23
24
25 PROCEDURE MAINTAIN_STK_POSITION_HISTORY(
26 P_START_DATE IN DATE,
27 P_DEAL_NUMBER IN NUMBER,
28 P_TRANSACTION_NUMBER IN NUMBER,
29 P_COMPANY_CODE IN VARCHAR2,
30 P_CURRENCY IN VARCHAR2,
31 P_DEAL_TYPE IN VARCHAR2,
32 P_DEAL_SUBTYPE IN VARCHAR2,
33 P_PRODUCT_TYPE IN VARCHAR2,
34 P_PORTFOLIO_CODE IN VARCHAR2,
35 P_CPARTY_CODE IN VARCHAR2,
36 P_CONTRA_CCY IN VARCHAR2,
37 P_CURRENCY_COMBINATION IN VARCHAR2,
38 P_ACCOUNT_NO IN VARCHAR2,
39 P_TRANSACTION_RATE IN NUMBER,
40 P_YEAR_CALC_TYPE IN VARCHAR2,
41 P_BASE_REF_AMOUNT IN NUMBER,
42 P_BASE_RATE IN NUMBER,
43 P_STATUS_CODE IN VARCHAR2,
44 P_INTEREST IN NUMBER,
45 P_ACTION IN VARCHAR2
46 ) as
47
48
49
50 L_REF_DATE DATE;
51 L_END_DATE DATE;
52 L_SYS_DATE DATE :=trunc(sysdate);
53 L_HCE_RATE NUMBER;
54 L_FAC NUMBER;
55 L_HCE_BASE_REF_AMOUNT NUMBER;
56 L_BASE_REF_AMOUNT NUMBER;
57 L_DAILY_INT NUMBER;
58 L_HCE_INT NUMBER;
59 L_DEAL_SUBTYPE XTR_POSITION_HISTORY.deal_subtype%TYPE;
60 T_AS_AT_DATE DBMS_SQL.DATE_TABLE;
61 L_TRANSACTION_RATE NUMBER;
62
63 --
64 cursor HCE is
65 select s.HCE_RATE,s.ROUNDING_FACTOR
66 from XTR_MASTER_CURRENCIES_V s
67 where s.CURRENCY = P_CURRENCY;
68
69
70 begin
71 open HCE;
72 fetch HCE into L_HCE_RATE,L_FAC;
73 close HCE;
74
75 /***********************/
76 /* Common Calculations */
77 /***********************/
78 L_REF_DATE :=nvl(P_START_DATE,L_SYS_DATE);
79 L_END_DATE := L_SYS_DATE;
80 L_HCE_BASE_REF_AMOUNT :=round(P_BASE_REF_AMOUNT/L_HCE_RATE,L_FAC);
81 L_BASE_REF_AMOUNT :=P_BASE_REF_AMOUNT;
82 L_TRANSACTION_RATE := P_TRANSACTION_RATE;
83 L_DAILY_INT := 0;
84 L_HCE_INT := 0;
85
86
87 /**************/
88 /* INSERT */
89 /**************/
90 if P_ACTION='INSERT' and P_STATUS_CODE='CURRENT' then
91
92 L_DEAL_SUBTYPE := P_DEAL_SUBTYPE;
93 L_BASE_REF_AMOUNT := P_BASE_REF_AMOUNT;
94
95
96 FOR i in 1..(L_END_DATE-L_REF_DATE) LOOP
97 T_AS_AT_DATE(i) := L_REF_DATE+i-1;
98 END LOOP;
99
100
101 forall i in 1..T_AS_AT_DATE.COUNT
102 insert into XTR_POSITION_HISTORY(
103 AS_AT_DATE,
104 DEAL_TYPE,
105 DEAL_NUMBER,
106 TRANSACTION_NUMBER,
107 COMPANY_CODE,
108 CPARTY_CODE,
109 DEAL_SUBTYPE,
110 PRODUCT_TYPE,
111 PORTFOLIO_CODE,
112 CURRENCY,
113 CONTRA_CCY,
114 CURRENCY_COMBINATION,
115 YEAR_CALC_TYPE,
116 ACCOUNT_NO,
117 BASE_REF_AMOUNT,
118 HCE_BASE_REF_AMOUNT,
119 TRANSACTION_RATE,
120 BASE_RATE,
121 INTEREST,
122 HCE_INTEREST)
123 values(
124 T_AS_AT_DATE(i),
125 P_DEAL_TYPE,
126 P_DEAL_NUMBER,
127 P_TRANSACTION_NUMBER,
128 P_COMPANY_CODE,
129 P_CPARTY_CODE,
130 L_DEAL_SUBTYPE,
131 P_PRODUCT_TYPE,
132 P_PORTFOLIO_CODE,
133 P_CURRENCY,
134 P_CONTRA_CCY,
135 P_CURRENCY_COMBINATION,
136 P_YEAR_CALC_TYPE,
137 P_ACCOUNT_NO,
138 L_BASE_REF_AMOUNT,
139 L_HCE_BASE_REF_AMOUNT,
140 L_TRANSACTION_RATE,
141 P_BASE_RATE,
142 L_DAILY_INT,
143 L_HCE_INT);
144
145
146
147
148 /**************/
149 /* UPDATE */
150 /**************/
151 elsif P_ACTION='UPDATE' and P_STATUS_CODE= 'CANCELLED' then
152
153 delete from XTR_POSITION_HISTORY
154 where AS_AT_DATE >= L_REF_DATE
155 and DEAL_TYPE = P_DEAL_TYPE
156 and DEAL_NUMBER = P_DEAL_NUMBER;
157
158
159
160 /**************/
161 /* DELETE */
162 /**************/
163 /* this function is not available for the stock deals */
164
165
166 end if;
167
168 end MAINTAIN_STK_POSITION_HISTORY;
169
170
171 PROCEDURE SNAPSHOT_STK_POSITION_HISTORY(
172 P_AS_AT_DATE IN DATE,
173 P_DEAL_NUMBER IN NUMBER,
174 P_TRANSACTION_NUMBER IN NUMBER,
175 P_COMPANY_CODE IN VARCHAR2,
176 P_CURRENCY IN VARCHAR2,
177 P_DEAL_TYPE IN VARCHAR2,
178 P_DEAL_SUBTYPE IN VARCHAR2,
179 P_PRODUCT_TYPE IN VARCHAR2,
180 P_PORTFOLIO_CODE IN VARCHAR2,
181 P_CPARTY_CODE IN VARCHAR2,
182 P_CONTRA_CCY IN VARCHAR2,
183 P_CURRENCY_COMBINATION IN VARCHAR2,
184 P_ACCOUNT_NO IN VARCHAR2,
185 P_TRANSACTION_RATE IN NUMBER,
186 P_YEAR_CALC_TYPE IN VARCHAR2,
187 P_BASE_REF_AMOUNT IN NUMBER,
188 P_BASE_RATE IN NUMBER,
189 P_STATUS_CODE IN VARCHAR2,
190 P_START_DATE IN DATE,
191 P_INTEREST IN NUMBER,
192 P_START_AMOUNT IN NUMBER
193
194 ) as
195
196 --
197 L_HCE_BASE_REF_AMOUNT NUMBER;
198 L_REF_DATE DATE;
199 L_END_DATE DATE;
200 L_AS_AT_DATE DATE;
201 L_PROC_DATE DATE;
202 L_RESALE_DATE DATE;
203 L_LAST_PROC_DATE DATE;
204 L_EARLY_START_DATE DATE;
205 L_ROWID VARCHAR2(30);
206 L_AMOUNT NUMBER;
207 L_HCE_RATE NUMBER;
208 L_FAC NUMBER;
209 L_LAST_RESALE_DATE DATE:= NULL;
210 L_BASE_REF_AMOUNT NUMBER;
211 L_TILL_DATE DATE;
212 l_complete_resale varchar2(1);
213 l_remaining_quantity number;
214 l_transaction_rate number;
215 l_price_per_share number;
216 l_cross_ref_start_date Date;
217 /*************************/
218 /* For DEAL_TYPE 'STOCK' */
219 /*************************/
220
221 cursor HCE is
222 select s.HCE_RATE,s.ROUNDING_FACTOR
223 from XTR_MASTER_CURRENCIES_V s
224 where s.CURRENCY = P_CURRENCY;
225
226
227
228 cursor GET_PRV_ROWS_STOCK(V_DEAL_TYPE VARCHAR2,
229 V_DEAL_NUMBER NUMBER) is
230 select max(AS_AT_DATE + 1)
231 from XTR_POSITION_HISTORY
232 where DEAL_TYPE = V_DEAL_TYPE
233 and DEAL_NUMBER = V_DEAL_NUMBER;
234
235 cursor STOCK_LAST_PROC_DATE(V_AS_AT_DATE DATE,
236 V_DEAL_NUMBER NUMBER)is
237 select cross_ref_start_date
238 from xtr_stock_alloc_details
239 where deal_no = V_DEAL_NUMBER
240 and CROSS_REF_START_DATE <= V_AS_AT_DATE
241 and avg_rate_last_processed is null
242 order by cross_ref_start_date;
243
244
245 cursor get_stock_resale ( V_AS_AT_DATE DATE) is
246 Select min(remaining_quantity), max(cross_ref_start_date)
247 From XTR_STOCK_ALLOC_DETAILS
248 Where deal_no = P_DEAL_NUMBER
249 and cross_ref_start_date <= V_AS_AT_DATE;
250
251
252 cursor CHK_LOCK_ROWS_STOCK(V_AS_AT_DATE DATE,
253 V_DEAL_TYPE VARCHAR2,
254 V_DEAL_NUMBER NUMBER) is
255 select rowid
256 from XTR_POSITION_HISTORY
257 where AS_AT_DATE = V_AS_AT_DATE
258 and DEAL_TYPE = V_DEAL_TYPE
259 and DEAL_NUMBER = V_DEAL_NUMBER
260 for update of BASE_REF_AMOUNT NOWAIT;
261
262
263 BEGIN
264
265 open HCE;
266 fetch HCE into L_HCE_RATE,L_FAC;
267 close HCE;
268
269 L_BASE_REF_AMOUNT := P_BASE_REF_AMOUNT;
270 L_HCE_BASE_REF_AMOUNT :=round(P_BASE_REF_AMOUNT/L_HCE_RATE,L_FAC);
271 l_transaction_rate := P_TRANSACTION_RATE;
272
273
274
275 l_complete_resale := 'N';
276
277 Open get_stock_resale(p_as_at_date);
278 Fetch get_stock_resale into l_remaining_quantity,l_last_resale_date;
279 If get_stock_resale%FOUND then
280
281 If nvl(l_remaining_quantity, -1) = 0 then -- total resale
282 Delete from XTR_POSITION_HISTORY
283 Where deal_number = P_DEAL_NUMBER
284 And as_at_date >= l_last_resale_date;
285 l_complete_resale := 'Y';
286 end if;
287
288 close get_stock_resale;
289
290 Else
291 close get_stock_resale;
292
293 End if;
294
295 open GET_PRV_ROWS_STOCK(P_DEAL_TYPE,P_DEAL_NUMBER);
296 fetch GET_PRV_ROWS_STOCK into L_AS_AT_DATE;
297
298 if GET_PRV_ROWS_STOCK%FOUND then -- deal no has some data in PH
299
300 open STOCK_LAST_PROC_DATE(L_AS_AT_DATE,P_DEAL_NUMBER);
301 fetch STOCK_LAST_PROC_DATE into L_RESALE_DATE;
302 if STOCK_LAST_PROC_DATE%FOUND then
303 if L_RESALE_DATE < nvl(L_AS_AT_DATE, sysdate) then
304 L_AS_AT_DATE := L_RESALE_DATE;
305 end if;
306 close STOCK_LAST_PROC_DATE;
307 else
308 close STOCK_LAST_PROC_DATE;
309 l_as_at_date := p_start_date; -- incase no rows exist in position history
310 end if;
311
312 close GET_PRV_ROWS_STOCK;
313 else
314 close GET_PRV_ROWS_STOCK;
315
316 end if;
317
318
319 L_AS_AT_DATE :=nvl(L_AS_AT_DATE,P_AS_AT_DATE);
320
321 if l_complete_resale = 'Y' then
322 l_till_date := l_last_resale_date -1 ;
323 ELSE
324 l_till_date := p_as_at_date;
325 end if;
326
327
328 WHILE L_AS_AT_DATE <= L_TILL_DATE LOOP
329
330
331
332 L_ROWID := NULL;
333
334 open CHK_LOCK_ROWS_STOCK(L_AS_AT_DATE,P_DEAL_TYPE,P_DEAL_NUMBER);
335 fetch CHK_LOCK_ROWS_STOCK into L_ROWID;
336 close CHK_LOCK_ROWS_STOCK;
337
338
339 Open get_stock_resale(l_as_at_date);
340 Fetch get_stock_resale into l_remaining_quantity,l_cross_ref_start_date;
341
342
343 if get_stock_resale%found and l_cross_ref_start_date is not null then
344
345 select price_per_share into
346 l_price_per_share
347 FROM xtr_stock_alloc_details a
348 WHERE deal_no = P_DEAL_NUMBER
349 and cross_ref_start_date = l_cross_ref_start_date
350 and cross_ref_no = ( select max(cross_ref_no) -- multiple sales on the same date
351 FROM xtr_stock_alloc_details
352 WHERE deal_no = a.deal_no
353 and cross_ref_start_date = l_cross_ref_start_date);
354
355 l_transaction_rate := l_price_per_share;
356 L_BASE_REF_AMOUNT := l_remaining_quantity * l_price_per_share;
357 L_HCE_BASE_REF_AMOUNT := round(L_BASE_REF_AMOUNT/L_HCE_RATE,L_FAC);
358 close get_stock_resale;
359 else
360 close get_stock_resale;
361 end if;
362
363
364
365
366 /*========================================*/
367 /* Insert or Update position history table */
368 /*========================================*/
369 if L_ROWID is not null then
370 update XTR_POSITION_HISTORY
371 set COMPANY_CODE = P_COMPANY_CODE,
372 CPARTY_CODE = P_CPARTY_CODE,
373 DEAL_SUBTYPE = P_DEAL_SUBTYPE,
374 PRODUCT_TYPE = P_PRODUCT_TYPE,
375 PORTFOLIO_CODE = P_PORTFOLIO_CODE,
376 CURRENCY = P_CURRENCY,
377 CONTRA_CCY = P_CONTRA_CCY,
378 CURRENCY_COMBINATION = P_CURRENCY_COMBINATION,
379 YEAR_CALC_TYPE = P_YEAR_CALC_TYPE,
380 ACCOUNT_NO = P_ACCOUNT_NO,
381 BASE_REF_AMOUNT = l_BASE_REF_AMOUNT,
382 HCE_BASE_REF_AMOUNT = L_HCE_BASE_REF_AMOUNT,
383 BASE_RATE = P_BASE_RATE,
384 TRANSACTION_RATE = l_TRANSACTION_RATE
385 where rowid=l_rowid;
386 else
387
388 -- insert new row
389 insert into XTR_POSITION_HISTORY(
390 AS_AT_DATE,
391 DEAL_TYPE,
392 DEAL_NUMBER,
393 TRANSACTION_NUMBER,
394 COMPANY_CODE,
395 CPARTY_CODE,
396 DEAL_SUBTYPE,
397 PRODUCT_TYPE,
398 PORTFOLIO_CODE,
399 CURRENCY,
400 CONTRA_CCY,
401 CURRENCY_COMBINATION,
402 YEAR_CALC_TYPE,
403 ACCOUNT_NO,
404 BASE_REF_AMOUNT,
405 HCE_BASE_REF_AMOUNT,
406 TRANSACTION_RATE,
407 BASE_RATE,
408 INTEREST,
409 HCE_INTEREST)
410 values(
411 L_AS_AT_DATE,
412 P_DEAL_TYPE,
413 P_DEAL_NUMBER,
414 P_TRANSACTION_NUMBER,
415 P_COMPANY_CODE,
416 P_CPARTY_CODE,
417 P_DEAL_SUBTYPE,
418 P_PRODUCT_TYPE,
419 P_PORTFOLIO_CODE,
420 P_CURRENCY,
421 P_CONTRA_CCY,
422 P_CURRENCY_COMBINATION,
423 P_YEAR_CALC_TYPE,
424 P_ACCOUNT_NO,
425 abs(nvl(L_BASE_REF_AMOUNT, P_BASE_REF_AMOUNT)),
429 0,
426 abs(L_HCE_BASE_REF_AMOUNT),
427 L_TRANSACTION_RATE,
428 P_BASE_RATE,
430 0);
431
432 end if;
433
434 if P_DEAL_TYPE = 'STOCK' and l_remaining_quantity is not null then
435 Update XTR_STOCK_ALLOC_DETAILS
436 set avg_rate_last_processed = greatest(nvl(avg_rate_last_processed,
437 L_AS_AT_DATE),
438 L_AS_AT_DATE)
439 where deal_no = P_DEAL_NUMBER;
440 end if;
441
442 L_AS_AT_DATE :=L_AS_AT_DATE +1;
443 END LOOP;
444
445
446
447 exception
448 when app_exceptions.RECORD_LOCK_EXCEPTION then
449 if CHK_LOCK_ROWS_STOCK%ISOPEN then
450 close CHK_LOCK_ROWS_STOCK;
451 end if;
452 raise app_exceptions.RECORD_LOCK_EXCEPTION;
453
454
455 end SNAPSHOT_STK_POSITION_HISTORY;
456
457
458
459
460 PROCEDURE SNAPSHOT_STK_COST_OF_FUNDS(
461 errbuf OUT NOCOPY VARCHAR2,
462 retcode OUT NOCOPY NUMBER,
463 p_deal_number IN NUMBER default NULL) as
464
465 l_run_date date := sysdate;
466 l_date date;
467 --
468 L_COMPANY_CODE VARCHAR2(7);
469 L_CURRENCY VARCHAR2(15);
470 L_DEAL_SUBTYPE VARCHAR2(7);
471 L_PRODUCT_TYPE VARCHAR2(10);
472 L_PORTFOLIO_CODE VARCHAR2(10);
473 L_CPARTY_CODE VARCHAR2(7);
474 L_AMOUNT NUMBER;
475 L_CONTRA_CCY VARCHAR2(15);
476 L_CURRENCY_COMBINATION VARCHAR2(31);
477 L_TRANSACTION_RATE NUMBER;
478 L_YEAR_CALC_TYPE VARCHAR2(15);
479 L_ACCOUNT_NO VARCHAR2(50);
480 L_CALC_BASIS VARCHAR2(15);
481 L_YEAR_BASIS NUMBER;
482 L_DEAL_NUMBER NUMBER;
483 L_TRANSACTION_NUMBER NUMBER;
484 L_CALC_TYPE VARCHAR2(15);
485 L_TOTAL_RESALE NUMBER := NULL;
486 L_START_DATE DATE;
487 L_cross_ref_start_date DATE;
488 L_remaining_quantity NUMBER;
489
490
491 cursor get_stock_deals is
492 select a.deal_no deal_number,
493 1 transaction_number,
494 a.status_code status_code,
495 a.company_code,
496 a.cparty_code,
497 a.deal_type,
498 a.deal_subtype,
499 a.currency,
500 a.currency_buy,
501 a.currency_sell,
502 a.product_type,
503 a.portfolio_code,
504 a.year_calc_type,
505 a.year_basis,
506 a.interest_rate interest_rate,
507 a.capital_price transaction_rate,
508 a.base_rate base_rate,
509 a.calc_basis,
510 a.start_date,
511 a.value_date,
512 a.option_commencement,
513 a.expiry_date,
514 a.maturity_date,
515 a.maturity_account_no account_no,
516 a.start_amount base_amount,
517 a.settle_amount second_amount,
518 a.maturity_amount,
519 a.start_amount,
520 a.day_count_type
521 from XTR_DEALS a
522 where a.deal_type ='STOCK'
523 and a.deal_subtype in ('BUY')
524 and a.status_code ='CURRENT'
525 and a.start_date <=l_date
526 and a.deal_no = nvl(p_deal_number, a.deal_no);
527
528
529 D get_stock_deals%rowtype;
530
531 cursor get_stock_total_resale is
532 Select min(remaining_quantity), max(cross_ref_start_date)
533 From XTR_STOCK_ALLOC_DETAILS
534 Where deal_no = D.DEAL_Number
535 and cross_ref_start_date <= L_DATE;
536
537 BEGIN
538
539
540 l_date :=trunc(sysdate)-1;
541
542 open get_stock_deals;
543 LOOP
544 fetch get_stock_deals into D;
545 exit WHEN get_stock_deals%NOTFOUND;
546
547
548
549 OPEN get_stock_total_resale;
550 FETCH get_stock_total_resale into
551 l_remaining_quantity,l_cross_ref_start_date;
552 if nvl(l_remaining_quantity,0)= 0 then
553 Update XTR_STOCK_ALLOC_DETAILS
554 Set avg_rate_last_processed = L_DATE
555 where deal_no = d.deal_number
556 and cross_ref_start_date = l_cross_ref_start_date;
557 close get_stock_total_resale;
558 else
559 CLOSE get_stock_total_resale;
560 end if;
561
562 if d.deal_type = 'STOCK' then
563 XTR_STOCK_POSITION_P.SNAPSHOT_STK_POSITION_HISTORY(
564 P_AS_AT_DATE => L_DATE,
565 P_DEAL_NUMBER => D.DEAL_NUMBER,
566 P_TRANSACTION_NUMBER => D.TRANSACTION_NUMBER,
567 P_COMPANY_CODE => D.COMPANY_CODE,
568 P_CURRENCY => D.CURRENCY,
569 P_DEAL_TYPE => D.DEAl_TYPE,
570 P_DEAL_SUBTYPE => D.DEAL_SUBTYPE,
571 P_PRODUCT_TYPE => D.PRODUCT_TYPE,
572 P_PORTFOLIO_CODE => D.PORTFOLIO_CODE,
573 P_CPARTY_CODE => D.CPARTY_CODE,
574 P_CONTRA_CCY => null,
575 P_CURRENCY_COMBINATION => null,
576 P_ACCOUNT_NO => D.ACCOUNT_NO,
577 P_TRANSACTION_RATE => D.TRANSACTION_RATE,
578 P_YEAR_CALC_TYPE => D.YEAR_CALC_TYPE,
579 P_BASE_REF_AMOUNT => D.BASE_AMOUNT,
580 P_BASE_RATE => D.BASE_RATE,
581 P_STATUS_CODE => D.STATUS_CODE,
582 P_START_DATE => D.START_DATE,
583 P_INTEREST => NULL,
584 P_START_AMOUNT => D.START_AMOUNT
585 );
586 end if;
587 END LOOP;
588
589
590 end SNAPSHOT_STK_COST_OF_FUNDS;
591
592
593 end XTR_STOCK_POSITION_P;
594