[Home] [Help]
PACKAGE BODY: APPS.XTR_STOCK_PKG
Source
1 PACKAGE BODY XTR_STOCK_PKG AS
2 /* $Header: xtrstckb.pls 120.11.12000000.2 2007/03/21 06:59:44 kbabu ship $ */
3
4 /************************************************************************/
5 /* This procedure is to insert information into DDA table after deal is */
6 /* commit. */
7 /************************************************************************/
8 PROCEDURE INS_STOCK_DDA (p_deal_no IN NUMBER,
9 p_reverse_dda IN BOOLEAN) as
10
11 cursor CUR_DEAL is
12 select *
13 from XTR_DEALS_V
14 where deal_no = p_deal_no;
15
16 D CUR_DEAL%ROWTYPE;
17
18 Cursor cur_div_share is
19 Select cash_dividend_id, dividend_per_share, declare_date
20 From xtr_stock_cash_dividends
21 Where stock_issue_code = D.bond_issue
22 And record_date >= D.start_date
23 And nvl(generated_flag, 'N') IN ('G', 'Y');
24 /* Bug 3737048 Added the new status in the cursor. */
25
26 Cursor cur_cparty_acct is
27 select ACCOUNT_NUMBER
28 from XTR_BANK_ACCOUNTS_V
29 where PARTY_CODE = D.CPARTY_CODE
30 and CURRENCY = D.CURRENCY
31 and ((PARTY_TYPE = 'CP' and BANK_SHORT_CODE = D.CPARTY_REF)
32 or(PARTY_TYPE = 'C' and substr(BANK_SHORT_CODE, 1, 7) = D.CPARTY_REF));
33
34 cursor HCE_RATE(p_currency VARCHAR2) is
35 select rounding_factor, hce_rate
36 from XTR_MASTER_CURRENCIES_V
37 where currency = p_currency;
38
39 l_div_id NUMBER;
40 l_div NUMBER;
41 l_declare_date DATE;
42 l_rev_amt NUMBER:= NULL;
43 l_rev_amt_hce NUMBER:= NULL;
44 l_sysdate DATE :=trunc(sysdate);
45 p_one_step_rec xtr_fps2_p.one_step_rec_type;
46 l_round NUMBER;
47 l_hce_rate NUMBER;
48 l_amt_date DATE;
49 l_cparty_acct VARCHAR2(20):= NULL;
50
51 BEGIN
52 Open CUR_DEAL;
53 Fetch CUR_DEAL into D;
54 IF CUR_DEAL%FOUND then
55 Open cur_cparty_acct;
56 Fetch cur_cparty_acct into l_cparty_acct;
57 Close cur_cparty_acct;
58
59 If D.deal_subtype = 'BUY' then
60 -- COMENCE row Details
61 INSERT INTO XTR_DEAL_DATE_AMOUNTS
62 (deal_type, amount_type, date_type, deal_number, transaction_number,
63 transaction_date, currency,amount, hce_amount, amount_date, transaction_rate,
64 cashflow_amount, company_code, account_no, status_code,
65 portfolio_code, dealer_code, client_code, deal_subtype, cparty_code, settle,
66 product_type, cparty_account_no)
67 values
68 ('STOCK', 'COMENCE', 'SETTLE', D.DEAL_NO, 1, D.DEAL_DATE, D.CURRENCY,
69 D.START_AMOUNT, D.START_HCE_AMOUNT, D.START_DATE, D.CAPITAL_PRICE,
70 decode(D.DEAL_SUBTYPE,'BUY',-1,1) * D.START_AMOUNT, D.COMPANY_CODE,
71 D.SETTLE_ACCOUNT_NO, D.STATUS_CODE, D.PORTFOLIO_CODE,
72 D.DEALER_CODE, D.CLIENT_CODE, D.DEAL_SUBTYPE, D.CPARTY_CODE, 'N',
73 D.PRODUCT_TYPE, l_cparty_acct);
74
75 -- Dealt Row with Counterparties
76 INSERT INTO XTR_DEAL_DATE_AMOUNTS
77 (deal_type, amount_type, date_type, deal_number, transaction_number,
78 transaction_date, currency, amount,hce_amount, amount_date, transaction_rate,
79 cashflow_amount, company_code, account_no, status_code,
80 portfolio_code, dealer_code, client_code, deal_subtype, cparty_code, settle,
81 product_type)
82 values
83 ('STOCK', 'N/A', 'DEALT', D.DEAL_NO, 1, D.DEAL_DATE, D.CURRENCY,
84 D.START_AMOUNT, D.START_HCE_AMOUNT, D.DEAL_DATE, D.CAPITAL_PRICE,0,
85 D.COMPANY_CODE, D.SETTLE_ACCOUNT_NO, D.STATUS_CODE,
86 D.PORTFOLIO_CODE, D.DEALER_CODE, D.CLIENT_CODE, D.DEAL_SUBTYPE, D.CPARTY_CODE,
87 'N', D.PRODUCT_TYPE);
88
89 -- Limit Row
90 insert INTO XTR_DEAL_DATE_AMOUNTS
91 (deal_type, amount_type, date_type, deal_number, transaction_number,
92 transaction_date, currency, amount,hce_amount, amount_date, transaction_rate,
93 cashflow_amount, company_code, account_no, status_code,
94 portfolio_code, dealer_code, client_code, deal_subtype, cparty_code, settle,
95 product_type, limit_code, limit_party)
96 values ('STOCK', 'LIMIT', 'LIMIT', D.DEAL_NO, 1, D.DEAL_DATE, D.CURRENCY,
97 D.START_AMOUNT, D.START_HCE_AMOUNT, D.DEAL_DATE,D.CAPITAL_PRICE, 0,
98 D.COMPANY_CODE, D.SETTLE_ACCOUNT_NO, D.STATUS_CODE,
99 D.PORTFOLIO_CODE, D.DEALER_CODE, D.CLIENT_CODE, D.DEAL_SUBTYPE, D.CPARTY_CODE,
100 'N', D.PRODUCT_TYPE, nvl(D.LIMIT_CODE, 'NILL'),
101 nvl(D.ACCEPTOR_CODE,D.CPARTY_CODE));
102
103 -- Insert into xtr_confirmation details
104 XTR_MISC_P.DEAL_ACTIONS
105 (D.deal_type, D.deal_no, 1, 'NEW_STOCK_BUY_CONTRACT',
106 D.cparty_code, D.client_code, l_sysdate,
107 D.company_code, D.status_code, null,
108 D.deal_subtype, D.currency, D.cparty_advice,
109 D.client_advice, D.start_amount, null);
110
111
112 Else -- deal_type 'SELL'
113 -- COMENCE row Details
114 INSERT INTO XTR_DEAL_DATE_AMOUNTS
115 (deal_type, amount_type, date_type, deal_number, transaction_number,
116 transaction_date, currency,amount, hce_amount, amount_date, transaction_rate,
117 cashflow_amount, company_code, account_no, status_code,
118 portfolio_code, dealer_code, client_code, deal_subtype, cparty_code, settle,
119 product_type, cparty_account_no)
120 values
121 ('STOCK', 'COMENCE', 'SETTLE', D.DEAL_NO, 1, D.DEAL_DATE, D.CURRENCY,
122 D.START_AMOUNT, D.START_HCE_AMOUNT, D.START_DATE, D.CAPITAL_PRICE,
123 decode(D.DEAL_SUBTYPE,'BUY',-1,1) * D.START_AMOUNT, D.COMPANY_CODE,
124 D.SETTLE_ACCOUNT_NO, D.STATUS_CODE, D.PORTFOLIO_CODE,
125 D.DEALER_CODE, D.CLIENT_CODE, D.DEAL_SUBTYPE, D.CPARTY_CODE, 'N',
126 D.PRODUCT_TYPE, l_cparty_acct);
127
128 -- Dealt Row with Counterparties
129 insert INTO XTR_DEAL_DATE_AMOUNTS
130 (deal_type, amount_type, date_type, deal_number, transaction_number,
131 transaction_date, currency, amount,hce_amount, amount_date, transaction_rate,
132 cashflow_amount, company_code, account_no, status_code,
133 portfolio_code, dealer_code, client_code, deal_subtype, cparty_code, settle,
134 product_type)
135 values ('STOCK', 'N/A', 'DEALT', D.DEAL_NO, 1, D.DEAL_DATE, D.CURRENCY,
136 D.START_AMOUNT, D.START_HCE_AMOUNT, D.DEAL_DATE, D.CAPITAL_PRICE,0,
137 D.COMPANY_CODE, D.SETTLE_ACCOUNT_NO, D.STATUS_CODE,
138 D.PORTFOLIO_CODE, D.DEALER_CODE, D.CLIENT_CODE, D.DEAL_SUBTYPE, D.CPARTY_CODE,
139 'N', D.PRODUCT_TYPE);
140
141 -- if resale start_date <= dividend's record date, need to reverse cash dividend
142 -- on SELL side where date_type = 'DIVDAT' and amount_type 'DIVEXP'
143 If p_reverse_dda = TRUE then
144 Open cur_div_share;
145 Fetch cur_div_share into l_div_id, l_div, l_declare_date;
146 While cur_div_share%FOUND loop
147 L_rev_amt := D.quantity * l_div;
148
149 Open hce_rate(D.currency);
150 Fetch hce_rate into l_round, l_hce_rate;
151 close hce_rate;
152
153 l_rev_amt_hce := round((L_rev_amt / l_hce_rate), l_round);
154
155 if l_declare_date > D.DEAL_DATE then
156 l_amt_date := l_declare_date;
157 else
158 l_amt_date := D.DEAL_DATE;
159 end if;
160
161 Insert into XTR_DEAL_DATE_AMOUNTS
162 (deal_type, amount_type, date_type, deal_number, transaction_number,
163 transaction_date, currency, amount,hce_amount, amount_date, transaction_rate,
164 cashflow_amount, company_code, account_no, status_code,
165 portfolio_code, dealer_code, client_code, deal_subtype, cparty_code, settle,
166 product_type, action_code)
167 Values ('STOCK', 'DIVEXP', 'DIVDAT', D.DEAL_NO, l_div_id, D.DEAL_DATE, D.CURRENCY,
168 l_rev_amt, l_rev_amt_hce, l_amt_date, l_div, 0, D.COMPANY_CODE,
169 D.SETTLE_ACCOUNT_NO, D.STATUS_CODE, D.PORTFOLIO_CODE,
170 D.DEALER_CODE, D.CLIENT_CODE, D.DEAL_SUBTYPE, D.CPARTY_CODE, 'N',
171 D.PRODUCT_TYPE, 'REV');
172
173 Fetch cur_div_share into l_div_id, l_div, l_declare_date;
174 End loop;
175
176 end if;
177
178 -- Insert into xtr_confirmation details
179 XTR_MISC_P.DEAL_ACTIONS
180 (D.deal_type, D.deal_no, 1, 'NEW_STOCK_SELL_CONTRACT',
181 D.cparty_code, D.client_code, l_sysdate,
182 D.company_code, D.status_code, null,
183 D.deal_subtype, D.currency, D.cparty_advice,
184 D.client_advice, D.start_amount, null);
185
186 end if;
187
188 /****************************************************************************/
189 /* This procedure will insert an EXP deal into DDA table if user select */
190 /* a tax code which is an one-step settlement method */
191 /****************************************************************************/
192
193 If D.TAX_CODE is NOT NULL then
194 p_one_step_rec.p_schedule_code := D.TAX_CODE;
195 p_one_step_rec.p_currency := D.CURRENCY;
196 p_one_step_rec.p_amount := D.TAX_AMOUNT;
197 p_one_step_rec.p_settlement_date := D.START_DATE;
198 p_one_step_rec.p_settlement_account := D.SETTLE_ACCOUNT_NO;
199 p_one_step_rec.p_company_code := D.COMPANY_CODE;
200 p_one_step_rec.p_cparty_code := D.CPARTY_CODE;
201 p_one_step_rec.p_cparty_account_no := D.CPARTY_REF;
202
203 XTR_FPS2_P.one_step_settlement(p_one_step_rec);
204
205 If p_one_step_rec.p_exp_number is NOT NULL then
206 Update XTR_DEALS
207 Set tax_settled_reference = p_one_step_rec.p_exp_number
208 Where deal_no = D.deal_no;
209 End if;
210 End if;
211
212 END IF;
213 Close CUR_DEAL;
214
215 end INS_STOCK_DDA;
216
217 -----------------------------------------------------------------------------------
218 /************************************************************/
219 /* This procedure is to update related tables when user */
220 /* set Stock deal status from 'CURRENT' to 'CANCELLED' */
221 /************************************************************/
222 PROCEDURE CANCEL_STOCK (p_deal_no IN NUMBER,
223 p_deal_subtype IN VARCHAR2,
224 p_currency IN VARCHAR2) IS
225
226 cursor LOCK_DDA_DEAL is
227 select ROWID
228 from XTR_DEAL_DATE_AMOUNTS
229 where deal_number = p_deal_no
230 for update of status_code nowait;
231
232 cursor LOCK_ROLL is
233 select ROWID
234 from XTR_ROLLOVER_TRANSACTIONS
235 where deal_number = p_deal_no
236 for update of status_code nowait;
237
238 cursor BKGE_EXP is
239 select BKGE_SETTLED_REFERENCE
240 from XTR_DEALS_V
241 where deal_type = 'STOCK'
242 and deal_no = p_deal_no
243 and bkge_settled_reference is NOT NULL;
244
245
246 -- Add for SELL Stock cancellation. Bug 3222956
247 cursor UPD_BUY_DEAL is
248 select deal_no, quantity, cross_ref_start_date, init_consideration
249 from XTR_STOCK_ALLOC_DETAILS
250 where cross_ref_no = p_deal_no;
251
252 cursor UPD_BUY_DIV(p_buy_deal NUMBER, p_sell_date DATE) is
253 select transaction_number, quantity, interest_rate,
254 tax_settled_reference, tax_rate
255 from XTR_ROLLOVER_TRANSACTIONS
256 where deal_number = p_buy_deal
257 and start_date >= p_sell_date;
258
259 cursor cur_hce is
260 select rounding_factor, hce_rate
261 from XTR_MASTER_CURRENCIES_V
262 where currency = p_currency;
263
264 l_rowid VARCHAR2(30);
265 l_tax_ref NUMBER;
266 l_tax_rate NUMBER;
267 l_tax_amt NUMBER;
268 l_tax_amt_hce NUMBER;
269 l_bkge_ref NUMBER;
270 l_dummy NUMBER:= NULL;
271 l_buy_deal NUMBER;
272 l_quantity NUMBER;
273 l_sell_start_date DATE;
274 l_init_cons NUMBER;
275 l_trans_no NUMBER;
276 l_trans_quantity NUMBER;
277 l_trans_rate NUMBER;
278 l_trans_int NUMBER;
279 l_round NUMBER;
280 l_hce_rate NUMBER;
281 l_hce_int NUMBER;
282 l_limit_amt NUMBER;
283
284 BEGIN
285 --------------------------------------------------
286 -- Delete entries from XTR_DEAL_DATE_AMOUNTS table
287 --------------------------------------------------
288 Open lock_dda_deal;
289 Fetch lock_dda_deal into l_rowid;
290 while lock_dda_deal%FOUND loop
291 delete from XTR_DEAL_DATE_AMOUNTS
292 where rowid = l_rowid;
293 fetch lock_dda_deal into l_rowid;
294 end loop;
295
296 --------------------------------------------------
297 -- Set Status to CANCELLED for XTR_ROLLOVER_TRANSACTIONS table
298 ----------------------------------------------------
299 if p_deal_subtype = 'BUY' then
300 Open lock_roll;
301 fetch lock_roll into l_rowid;
302 while lock_roll%FOUND loop
303 update XTR_ROLLOVER_TRANSACTIONS
304 set status_code = 'CANCELLED'
305 where rowid = l_rowid;
306 fetch lock_roll into l_rowid;
307 end loop;
308 end if;
309
310
311 -----------------------------------------------------
312 -- Delete entris from XTR_CONFIRMATION_DETAILS table
313 -----------------------------------------------------
314 delete xtr_confirmation_details
315 where deal_type = 'STOCK'
319 -- Delete EXP record from XTR_EXPOSURE_TRANSACTIONS and DDA if there is any
316 and deal_no = p_deal_no;
317
318 ----------------------------------------------------------------------------
320 --------------------------------------------------------------------------
321
322 XTR_FPS2_P.DELETE_TAX_EXPOSURE(p_deal_no, l_dummy);
323
324 Open BKGE_EXP;
325 Fetch BKGE_EXP into l_bkge_ref;
326 if BKGE_EXP%FOUND then
327 Delete from XTR_EXPOSURE_TRANSACTIONS
328 Where transaction_number = l_bkge_ref;
329
330 Delete from XTR_DEAL_DATE_AMOUNTS
331 Where deal_type = 'EXP'
332 and deal_number = 0
333 and transaction_number = l_bkge_ref;
334 end if;
335 Close BKGE_EXP;
336
337 -----------------------------------------------------------------------
338 -- For cancellation of SELL Stock deal
339 -----------------------------------------------------------------------
340 if p_deal_subtype = 'SELL' then
341
342 Open cur_hce;
343 Fetch cur_hce into l_round, l_hce_rate;
344 Close cur_hce;
345 l_round := nvl(l_round, 2);
346 l_hce_rate := nvl(l_hce_rate, 1);
347
348 Open UPD_BUY_DEAL;
349 Fetch UPD_BUY_DEAL into l_buy_deal, l_quantity, l_sell_start_date, l_init_cons;
350 While UPD_BUY_DEAL%FOUND loop
351 -- Update BUY deal's remaining quantity and limit amount
352 l_limit_amt := l_init_cons;
353
354 Update XTR_DEALS
355 Set remaining_quantity = remaining_quantity + l_quantity,
356 status_code = 'CURRENT'
357 where deal_no = l_buy_deal;
358
359 Update XTR_DEAL_DATE_AMOUNTS
360 Set amount = amount + l_limit_amt,
361 hce_amount = round((amount+l_limit_amt)/l_hce_rate, l_round)
362 where deal_number = l_buy_deal
363 and amount_type = 'LIMIT'
364 and date_type = 'LIMIT';
365
366 Update XTR_DEAL_DATE_AMOUNTS
367 Set status_code = 'CURRENT'
368 where deal_number = l_buy_deal
369 and status_code = 'CLOSED';
370
371 -- Update BUY deal's dividend that is issue after SELL deal
372 Open UPD_BUY_DIV(l_buy_deal, l_sell_start_date);
373 Fetch UPD_BUY_DIV into l_trans_no, l_trans_quantity, l_trans_rate,
374 l_tax_ref, l_tax_rate;
375 While UPD_BUY_DIV%FOUND loop
376 l_trans_quantity := l_trans_quantity + l_quantity;
377 l_trans_int := round(l_trans_quantity * l_trans_rate, l_round);
378 l_hce_int := round(l_trans_int / l_hce_rate, l_round);
379 if l_tax_ref is NOT NULL then -- dividend's tax
380 l_tax_amt := round(l_tax_rate * l_trans_int /100, l_round);
381 l_tax_amt_hce:= round(l_tax_amt /l_hce_rate, l_round);
382
383 Update XTR_DEAL_DATE_AMOUNTS
384 set amount = l_tax_amt,
385 hce_amount = l_tax_amt_hce,
386 cashflow_amount = (-1) * l_tax_amt
387 where deal_type = 'EXP'
388 and deal_number = 0
389 and transaction_number = l_tax_ref;
390 end if;
391
392 Update XTR_ROLLOVER_TRANSACTIONS
393 Set quantity = l_trans_quantity,
394 interest = l_trans_int,
395 interest_hce = l_hce_int,
396 tax_amount = l_tax_amt,
397 tax_amount_hce = l_tax_amt_hce
398 where deal_number = l_buy_deal
399 and transaction_number = l_trans_no;
400
401 Update XTR_DEAL_DATE_AMOUNTS
402 Set AMOUNT = l_trans_int,
403 CASHFLOW_AMOUNT = l_trans_int,
404 HCE_AMOUNT = l_hce_int
405 Where deal_number = l_buy_deal
406 and transaction_number = l_trans_no
407 and amount_type = 'DIVSET';
408
409 Fetch UPD_BUY_DIV into l_trans_no, l_trans_quantity, l_trans_rate,
410 l_tax_ref, l_tax_rate;
411
412 End loop;
413 CLOSE UPD_BUY_DIV;
414
415 Update XTR_STOCK_ALLOC_DETAILS
416 set remaining_quantity = remaining_quantity + l_quantity
417 where deal_no = l_buy_deal
418 and ((cross_ref_start_date > l_sell_start_date)
419 or (cross_ref_start_date = l_sell_start_date and cross_ref_no > p_deal_no));
420
421 Fetch UPD_BUY_DEAL into l_buy_deal, l_quantity, l_sell_start_date, l_init_cons;
422 End loop;
423 Close UPD_BUY_DEAL;
424
425 Delete from XTR_STOCK_ALLOC_DETAILS
426 where cross_ref_no = p_deal_no;
427
428 end if;
429
430 END CANCEL_STOCK;
431
432 --------------------------------------------------------------------------------
433 /*============================================================================*/
434 /*=================== BEGIN CASH DIVIDEND PROCEDURES =======================*/
435 /*============================================================================*/
436
437 ------------------------------------------------------
438 -- Check all dividends are correct
439 ------------------------------------------------------
440 FUNCTION INVALID_DIV_DATE(p_declare_date DATE,
441 p_record_date DATE,
442 p_payment_date DATE) return BOOLEAN IS
443
444 BEGIN
445
446 if (p_declare_date is not null and p_record_date is not null and p_declare_date > p_record_date) or
447 (p_record_date is not null and p_payment_date is not null and p_record_date > p_payment_date) then
448 --fnd_message.set_name ('XTR','XTR_DIVIDEND_DATE_ERROR');
449 return TRUE;
450 end if;
451
452 return FALSE;
453
454 END;
455
456 -------------------------------------------------------------------------
457 -- Check that stock dividend combination is unique
458 -------------------------------------------------------------------------
459 FUNCTION UNIQUE_STOCK_DIV_EXIST(p_stock_issue VARCHAR2,
460 p_declare_date DATE,
461 p_record_date DATE,
462 p_payment_date DATE) return BOOLEAN IS
463
464 cursor EXIST_STOCK_DIV is
465 select 1
466 from XTR_STOCK_CASH_DIVIDENDS
467 where STOCK_ISSUE_CODE = p_stock_issue
468 and DECLARE_DATE = p_declare_date
469 and RECORD_DATE = p_record_date
470 and PAYMENT_DATE = p_payment_date;
471
472 l_dummy NUMBER := 0;
473
474 BEGIN
475
476 if p_stock_issue is not null then
477
478 open EXIST_STOCK_DIV;
479 fetch EXIST_STOCK_DIV into l_dummy;
480 close EXIST_STOCK_DIV;
481
482 if l_dummy = 1 then
483 --fnd_message.set_name('XTR','XTR_UNIQUE_CASH_DIVIDEND');
484 return TRUE;
485 end if;
486
487 end if;
488
489 return FALSE;
490
491 END;
492
493 /* Bug 3737048. The procedure below has been added to take care
494 of the situation when payment date is updated for a record that has
495 the status 'Not Generated'. */
496
497 -------------------------------------------------------------------------
498 -- Check that stock dividend combination is unique during update
499 -- by using the cash dividend id of the record being updated.
500 -------------------------------------------------------------------------
501 FUNCTION UNIQUE_STOCK_DIV_EXIST(p_cash_dividend_id NUMBER,
502 p_stock_issue VARCHAR2,
503 p_declare_date DATE,
504 p_record_date DATE) return BOOLEAN IS
505
506 cursor EXIST_STOCK_DIV is
507 select 1
508 from XTR_STOCK_CASH_DIVIDENDS
509 where STOCK_ISSUE_CODE = p_stock_issue
510 and DECLARE_DATE = p_declare_date
511 and RECORD_DATE = p_record_date
512 and cash_dividend_id <> p_cash_dividend_id;
513
514 l_dummy NUMBER := 0;
515
516 BEGIN
517
518 if p_cash_dividend_id is not null then
519
520 open EXIST_STOCK_DIV;
521 fetch EXIST_STOCK_DIV into l_dummy;
522 close EXIST_STOCK_DIV;
523
524 if l_dummy = 1 then
525 return TRUE;
526 end if;
527
528 end if;
529
530 return FALSE;
531
532 END;
533
534 ---------------------------------------------------------------
535 -- Disable delete function if dividend has been settled/journal
536 ---------------------------------------------------------------
537 FUNCTION DISABLE_DELETE (p_div_id NUMBER) return BOOLEAN IS
538
539 cursor CHK_SETTLE is
540 select 1
541 from xtr_deal_date_amounts dda
542 where dda.deal_type = 'STOCK'
543 and dda.transaction_number = p_div_id
544 and dda.amount_type = 'DIVSET' and dda.deal_subtype = 'BUY'
545 and nvl(dda.settle,'N') = 'Y'
546 union
547 select 1
548 from xtr_deal_date_amounts a,
549 xtr_rollover_transactions b
550 where a.deal_type = 'EXP'
551 and a.transaction_number = b.tax_settled_reference
552 and nvl(a.settle,'N') = 'Y'
553 and b.deal_type = 'STOCK'
554 and b.transaction_number = p_div_id
555 and b.tax_settled_reference is not null;
556
557 cursor CHK_JOURNAL is
558 select 2
559 from xtr_journals jnl
560 where jnl.deal_type = 'STOCK'
561 and jnl.transaction_number = p_div_id
562 and ((jnl.amount_type = 'DIVSET' and jnl.deal_subtype = 'BUY')
563 or (jnl.amount_type = 'DIVEXP'))
564 union
565 select 2
566 from xtr_journals a,
567 xtr_rollover_transactions b
568 where a.deal_type = 'EXP'
569 and a.transaction_number = b.tax_settled_reference
570 and b.deal_type = 'STOCK'
571 and b.transaction_number = p_div_id
572 and b.tax_settled_reference is not null;
573
574 cursor CHK_CONFIRMATION is
575 select 3
576 from xtr_confirmation_details
577 where deal_type = 'STOCK'
578 and transaction_no = p_div_id
579 and (confirmation_validated_by is not null
580 or confirmation_validated_on is not null);
581
582 l_dummy NUMBER := 0;
583
584 BEGIN
585 open CHK_SETTLE;
586 fetch CHK_SETTLE into l_dummy;
587 close CHK_SETTLE;
588 if l_dummy = 1 then -- cannot delete
589 return TRUE;
590 else
591 open CHK_JOURNAL;
592 fetch CHK_JOURNAL into l_dummy;
593 close CHK_JOURNAL;
594 if l_dummy = 2 then -- cannot delete
595 return TRUE;
596 else
597 open CHK_CONFIRMATION;
598 fetch CHK_CONFIRMATION into l_dummy;
599 close CHK_CONFIRMATION;
600 if l_dummy = 3 then -- cannot delete
601 return TRUE;
602 else
603 return FALSE;
604 end if;
605 end if;
606 end if;
607
608 END;
609
610 --------------------------------------------------------
611 -- Count how many deals will generate dividend
612 --------------------------------------------------------
613 FUNCTION GENERATE_CNT (p_div_id IN NUMBER,
614 p_stock_issue IN VARCHAR2,
615 p_declare_date IN DATE,
616 p_record_date IN DATE,
617 p_payment_date IN DATE,
618 p_div_per_share IN NUMBER) return NUMBER IS
619
620 l_dummy NUMBER := 0;
621
622 begin
623
624 ------------------------------------------------------------------------------
625 -- Same select statement as GENERATE_DIV, changing this must also change that.
626 ------------------------------------------------------------------------------
627 /* Bug 3737048. Added the condition for the settle date. */
628
629 select count(*)
630 into l_dummy
631 from xtr_deals d
632 where d.deal_type = 'STOCK'
633 and d.deal_subtype = 'BUY'
634 and d.status_code <> 'CANCELLED'
635 and d.bond_issue = p_stock_issue
636 and d.start_date <= p_record_date
637 and d.quantity > (select nvl(sum(b.quantity),0)
638 from xtr_stock_alloc_details b
639 where b.deal_no = d.deal_no
640 and b.cross_ref_start_date <= p_record_date)
641 and not exists (select 1
642 from xtr_rollover_transactions r
643 where r.transaction_number = p_div_id
644 and r.deal_number = d.deal_no
645 and r.settle_date IS NOT NULL) ;
646
647
648 return (l_dummy);
649
650 end;
651
652
653 -----------------------------------------------------
654 -- Generate dividend for the BUY deals, create RT,DDA
655 -----------------------------------------------------
656 /* Bug 3737048. Added the last parameter. */
657 PROCEDURE GENERATE_DIV (p_div_id NUMBER,
658 p_stock_issue VARCHAR2,
659 p_currency VARCHAR2,
660 p_declare_date DATE,
661 p_record_date DATE,
662 p_payment_date DATE,
663 p_div_per_share NUMBER,
664 p_sys_user VARCHAR2,
665 p_sys_date DATE,
666 p_deal_no NUMBER,
667 p_reverse VARCHAR2) IS
668
669 ------------------------------------------------------------------------------
670 -- Same select statement as GENERATE_CNT, changing this must also change that.
671 ------------------------------------------------------------------------------
672 /* Bug 3737048. Added the condition for settle date. */
673
674 cursor BUY_DEAL is
675 select deal_no
676 from xtr_deals d
677 where d.deal_type = 'STOCK'
678 and d.deal_subtype = 'BUY'
679 and d.status_code <> 'CANCELLED'
680 and d.deal_no = nvl(p_deal_no,deal_no)
681 and d.bond_issue = p_stock_issue
682 and d.start_date <= p_record_date
683 and d.quantity > (select nvl(sum(b.quantity),0)
684 from xtr_stock_alloc_details b
685 where b.deal_no = d.deal_no
686 and b.cross_ref_start_date <= p_record_date)
687 and not exists (select 1
688 from xtr_rollover_transactions r
689 where r.transaction_number = p_div_id
690 and r.deal_number = d.deal_no
691 and r.settle_date IS NOT NULL);
692
693 cursor DEAL_DETAIL (l_deal_no NUMBER) is
694 select *
695 from xtr_deals
696 where deal_no = l_deal_no;
697
698 BUY DEAL_DETAIL%ROWTYPE;
699
700 Cursor cur_cparty_acct is
701 select ACCOUNT_NUMBER
702 from XTR_BANK_ACCOUNTS_V
703 where PARTY_CODE = BUY.CPARTY_CODE
704 and CURRENCY = BUY.CURRENCY
705 and ((PARTY_TYPE = 'CP' and BANK_SHORT_CODE = BUY.CPARTY_REF)
706 or(PARTY_TYPE = 'C' and substr(BANK_SHORT_CODE, 1, 7) = BUY.CPARTY_REF));
707
708 --------------------------------------------
709 -- Get rounding currency and hce_rate
710 --------------------------------------------
711 cursor ROUND_FACTOR is
712 select rounding_factor, hce_rate
713 from XTR_MASTER_CURRENCIES_V
714 where currency = p_currency;
715
716 --------------------------------------------
717 -- Get home rounding curency
718 --------------------------------------------
719 cursor HCE_ROUND_FACTOR is
720 select a.rounding_factor
721 from XTR_MASTER_CURRENCIES_V a,
722 XTR_PRO_PARAM b
723 where b.param_name = 'SYSTEM_FUNCTIONAL_CCY'
724 and a.currency = param_value;
725
726 -------------------------------------------------
727 -- Find total quantity sold if there are any sell
728 -------------------------------------------------
729 cursor QTY_SOLD (l_deal_no NUMBER) is
730 select nvl(sum(QUANTITY),0)
731 from xtr_stock_alloc_details
732 where deal_no = l_deal_no
733 and cross_ref_start_date <= p_record_date;
734
735 -------------------------------------------------
736 -- Find individual resale to reverse DIVEXP
737 -------------------------------------------------
738 cursor SELL_REV (l_buy_deal_no NUMBER) is
739 select d.DEAL_NO,
740 s.QUANTITY,
741 d.DEAL_DATE,
742 d.START_DATE,
743 d.CURRENCY,
744 d.COMPANY_CODE,
745 d.SETTLE_ACCOUNT_NO,
746 d.STATUS_CODE,
747 d.PORTFOLIO_CODE,
748 d.DEALER_CODE,
749 d.CLIENT_CODE,
750 d.CPARTY_CODE,
751 d.PRODUCT_TYPE
752 from xtr_stock_alloc_details s,
753 xtr_deals d
754 where s.deal_no = l_buy_deal_no
755 and s.cross_ref_start_date <= p_record_date
756 and s.cross_ref_no = d.deal_no
757 and d.deal_type = 'STOCK'
758 and d.deal_subtype = 'SELL'
759 and d.status_code = 'CURRENT';
760
761
762 /* for bug 5917859 starts */
763
764 L_STOCK_ISSUER XTR_STOCK_ISSUES.STOCK_ISSUER%TYPE;
765 l_cparty_ref XTR_BANK_ACCOUNTS.BANK_SHORT_CODE%TYPE;
766
767
768 CURSOR C_STOCK_ISSUER IS
769 SELECT STOCK_ISSUER
770 FROM XTR_STOCK_ISSUES
771 WHERE STOCK_ISSUE_CODE = p_stock_issue;
772
773 /* for bug 5917859 ENDS */
774
775 SELL SELL_REV%ROWTYPE;
776
777 l_rounding NUMBER;
778 l_hce_rate NUMBER;
779 l_hce_rounding NUMBER;
780 l_int_amt NUMBER;
781 l_int_amt_hce NUMBER;
782 l_exp_amt NUMBER;
783 l_exp_amt_hce NUMBER;
784 l_qty_sold NUMBER;
785 l_remain_qty NUMBER;
786 l_tran_declare_date DATE;
787
788 l_tax_rate NUMBER;
789 l_tax_amt NUMBER;
790 l_tax_amt_hce NUMBER;
791
792 l_dummy NUMBER;
793 l_err_code NUMBER;
794 l_level VARCHAR2(50);
795 l_cparty_acct VARCHAR2(20);
796
797 /* Bug 3737048 Added the variable below. */
798 l_tax_settled_reference XTR_EXPOSURE_TRANSACTIONS.Transaction_Number%Type;
799
800 one_step_rec XTR_FPS2_P.one_step_rec_type; /* p_schedule_code
801 p_currency
802 p_amount
803 p_settlement_date
804 p_settlement_account
805 p_company_code
806 p_cparty_code
807 p_cparty_account_no
808 p_error
809 p_settle_method
810 p_exp_number */
811
812 BEGIN
813
814 open ROUND_FACTOR;
815 fetch ROUND_FACTOR into l_rounding, l_hce_rate;
816 close ROUND_FACTOR;
817
818 open HCE_ROUND_FACTOR;
819 fetch HCE_ROUND_FACTOR into l_hce_rounding;
820 close HCE_ROUND_FACTOR;
821
822 for BUY_D in BUY_DEAL loop
823
824 open DEAL_DETAIL(BUY_D.deal_no);
825 fetch DEAL_DETAIL into BUY;
826 if DEAL_DETAIL%FOUND then
827
828 /* for bug 5917859 starts */
829
830 OPEN C_STOCK_ISSUER;
831 FETCH C_STOCK_ISSUER INTO L_STOCK_ISSUER;
832 CLOSE C_STOCK_ISSUER;
833
834 IF L_STOCK_ISSUER <> BUY.cparty_code THEN
835 XTR_fps2_P.STANDING_SETTLEMENTS (L_STOCK_ISSUER,
836 BUY.CURRENCY,
837 'STOCK',
838 BUY.DEAL_SUBTYPE,
839 BUY.PRODUCT_TYPE,
840 Null,
841 l_cparty_ref ,
842 l_cparty_acct);
843
844 ELSE
845 /* for bug 5917859 ENDS */
846
847
848 Open cur_cparty_acct;
849 Fetch cur_cparty_acct into l_cparty_acct;
850 Close cur_cparty_acct;
851
852 END IF; -- for bug 5917859
853
854 -------------------------------
855 -- Find actual declare date
856 -------------------------------
857 if BUY.deal_date <= p_declare_date then -- AW Bug 2486820 issue 13
858 l_tran_declare_date := p_declare_date;
859 else
860 l_tran_declare_date := BUY.deal_date; -- AW Bug 2486820 issue 13
861 end if;
862
863 -------------------------------
864 -- Find remaining shares
865 -------------------------------
866 l_qty_sold := 0;
867 open QTY_SOLD (BUY.deal_no);
868 fetch QTY_SOLD into l_qty_sold;
869 close QTY_SOLD;
870
871 l_remain_qty := BUY.quantity - l_qty_sold;
872
873 if nvl(l_remain_qty,0) <> 0 then
874
875 l_int_amt := round(l_remain_qty * p_div_per_share,nvl(l_rounding,2));
876 l_int_amt_hce := round(l_int_amt / l_hce_rate,nvl(l_hce_rounding,2));
877
878 l_exp_amt := round(BUY.quantity * p_div_per_share,nvl(l_rounding,2));
879 l_exp_amt_hce := round(l_exp_amt / l_hce_rate,nvl(l_hce_rounding,2));
880
881 -------------------------------------------------
882 -- Tax Calculation
883 -------------------------------------------------
884 if BUY.income_tax_code is not null then
885 l_tax_rate := NULL;
886
887 XTR_FPS1_P.CALC_TAX_AMOUNT (BUY.deal_type, -- IN deal type
888 p_declare_date, -- IN deal date
889 null, -- IN principal tax schedule
890 BUY.income_tax_code, -- IN income tax schedule
891 p_currency, -- IN currency (buy ccy for FX)
892 null, -- IN sell ccy if FX
893 null, -- IN year basis
894 null, -- IN number of days
895 null, -- IN principal tax amount
896 l_dummy, -- IN/OUT principal tax rate
897 l_int_amt, -- IN income tax amount
898 l_TAX_RATE, -- IN/OUT income tax rate
899 l_dummy, -- IN/OUT calculated principal tax
900 l_tax_amt, -- IN/OUT calculated income tax
901 l_err_code, -- OUT
902 l_level); -- OUT
903 l_tax_amt_hce := round(l_tax_amt / l_hce_rate,nvl(l_hce_rounding,2));
904
905 end if;
906
907 -------------------------------------------------
908 -- Insert cash dividend RT row for the BUY deal
909 -------------------------------------------------
910 /* Bug 3737048. Modified Code Begins. */
911 Update xtr_rollover_transactions
912 set settle_date = p_payment_date
913 where transaction_number = p_div_id
914 and deal_number = BUY.deal_no;
915
916 IF SQL%NOTFOUND THEN /* End Code Added. */
917 Insert into xtr_rollover_transactions
918 (deal_number, transaction_number,
919 deal_type, deal_subtype,
920 quantity, interest,
921 interest_hce, tax_code,
922 tax_rate, tax_amount,
923 tax_amount_hce, interest_rate,
924 currency, dealer_code,
925 status_code, portfolio_code,
926 client_code, company_code,
927 cparty_code, product_type,
928 deal_date, start_date,
929 settle_date,
930 created_by, created_on)
931 values (BUY.deal_no, p_div_id,
932 BUY.deal_type, BUY.deal_subtype,
933 l_remain_qty, l_int_amt,
934 l_int_amt_hce, BUY.income_tax_code,
935 l_tax_rate, l_tax_amt,
936 l_tax_amt_hce, p_div_per_share,
937 BUY.currency, BUY.dealer_code,
938 BUY.status_code, BUY.portfolio_code,
939 BUY.client_code, BUY.company_code,
940 L_STOCK_ISSUER, -- FOR BUG 5917859 BUY.cparty_code,
941 BUY.product_type,
942 l_tran_declare_date, p_record_date,
943 p_payment_date,
944 p_sys_user, p_sys_date);
945
946 END IF; /* Bug 3737048 Added. */
947
948 -----------------------------------------------------
949 -- Insert cash dividend DDA - DIVSET for the BUY deal
950 -----------------------------------------------------
951 /* Bug 3737048 Added the IF Condition below. */
952 IF (p_payment_date is not null) THEN
953 Insert into XTR_DEAL_DATE_AMOUNTS
954 (deal_type, deal_number,
955 transaction_number, deal_subtype,
956 date_type, amount_type,
957 action_code, transaction_date,
958 transaction_rate, currency,
959 company_code, account_no,
960 status_code, portfolio_code,
961 dealer_code, client_code,
962 cparty_code, settle,
963 product_type, amount_date,
964 amount, cashflow_amount,
965 hce_amount, cparty_account_no,
966 created_by, created_on)
967 values (BUY.deal_type, BUY.deal_no,
968 p_div_id, BUY.deal_subtype,
969 'PAYMENT', 'DIVSET',
970 null, BUY.deal_date,
971 p_div_per_share, BUY.currency,
972 BUY.company_code, BUY.settle_account_no,
973 BUY.status_code, BUY.portfolio_code,
974 BUY.dealer_code, BUY.client_code,
975 L_STOCK_ISSUER, -- FOR BUG 5917859 BUY.cparty_code,
976 'N',
977 BUY.product_type, p_payment_date,
978 l_int_amt, l_int_amt,
979 l_int_amt_hce, l_cparty_acct,
980 p_sys_user, p_sys_date);
981 END IF;
982
983 ----------------------------------------------------------
984 -- Insert cash dividend DDA - DIVEXP(POS) for the BUY deal
985 ----------------------------------------------------------
986 /* Bug 3737048. Added the Update statement and the IF condition. */
987 update XTR_DEAL_DATE_AMOUNTS
988 set currency = BUY.currency
989 where deal_number = BUY.deal_no
990 and transaction_number = p_div_id
991 and date_type = 'DIVDAT'
992 and amount_type = 'DIVEXP';
993
994 IF SQL%NOTFOUND THEN
995 Insert into XTR_DEAL_DATE_AMOUNTS
996 (deal_type, deal_number,
997 transaction_number,
998 deal_subtype, date_type,
999 amount_type, action_code,
1000 transaction_date, transaction_rate,
1001 currency, company_code,
1002 account_no, status_code,
1003 portfolio_code, dealer_code,
1004 client_code, cparty_code,
1005 settle, product_type,
1006 amount_date, amount,
1007 cashflow_amount, hce_amount,
1008 created_by, created_on)
1009 values (BUY.deal_type, BUY.deal_no,
1010 p_div_id,
1011 BUY.deal_subtype, 'DIVDAT',
1012 'DIVEXP', 'POS',
1013 BUY.deal_date, p_div_per_share,
1014 BUY.currency, BUY.company_code,
1015 BUY.settle_account_no, BUY.status_code,
1016 BUY.portfolio_code, BUY.dealer_code,
1017 BUY.client_code, L_STOCK_ISSUER, -- FOR BUG 5917859 BUY.cparty_code,
1018 'N', BUY.product_type,
1019 l_tran_declare_date, l_exp_amt,
1020 0, l_exp_amt_hce,
1021 p_sys_user, p_sys_date);
1022
1023 END IF; /* Bug 3737048 Added. */
1024
1025 --------------------------------------------------------------
1026 -- Adjust cash dividend DDA - DIVEXP(REV) for the SELL deal
1027 --------------------------------------------------------------
1028 /* Bug 3737048 Don't adjust DIVEXP from flag 'G' to 'Y'. */
1029
1030 IF p_reverse IS NULL THEN
1031 l_tran_declare_date := null;
1032 l_exp_amt := null;
1033 l_exp_amt_hce := null;
1034
1035 open SELL_REV(BUY.deal_no);
1036 fetch SELL_REV into SELL;
1037 while SELL_REV%FOUND loop
1038
1039 -------------------------------
1040 -- Find actual declare date
1041 -------------------------------
1042 if SELL.deal_date <= p_declare_date then
1043 /* Bug 2486820 issue 13 */
1044 l_tran_declare_date := p_declare_date;
1045 else
1046 l_tran_declare_date := SELL.deal_date;
1047 /* Bug 2486820 issue 13 */
1048 end if;
1049
1050 l_exp_amt := round(SELL.quantity *
1051 p_div_per_share,nvl(l_rounding,2));
1052 l_exp_amt_hce := round(l_exp_amt / l_hce_rate,
1053 nvl(l_hce_rounding,2));
1054
1055 /*-----------------------------------------------------------
1056 Insert/Update cash dividend DDA - DIVEXP(REV) for
1057 the SELL deal
1058 ------------------------------------------------------------*/
1059 -- Bug 2517289
1060 update XTR_DEAL_DATE_AMOUNTS
1061 set amount = amount + l_exp_amt,
1062 hce_amount = hce_amount + l_exp_amt_hce
1063 where deal_number = SELL.deal_no
1064 and transaction_number = p_div_id
1065 and date_type = 'DIVDAT'
1066 and amount_type = 'DIVEXP'
1067 and action_code = 'REV';
1068
1069 if SQL%NOTFOUND then
1070 Insert into XTR_DEAL_DATE_AMOUNTS
1071 (deal_type, deal_number,
1072 transaction_number,
1073 deal_subtype, date_type,
1074 amount_type, action_code,
1075 transaction_date, transaction_rate,
1076 currency, company_code,
1077 account_no, status_code,
1078 portfolio_code, dealer_code,
1079 client_code, cparty_code,
1080 settle, product_type,
1081 amount_date, amount,
1082 cashflow_amount, hce_amount,
1083 created_by, created_on)
1084 values ('STOCK', SELL.deal_no,
1085 p_div_id,
1086 'SELL', 'DIVDAT',
1087 'DIVEXP', 'REV',
1088 SELL.deal_date, p_div_per_share,
1089 SELL.currency, SELL.company_code,
1090 SELL.settle_account_no,SELL.status_code,
1091 SELL.portfolio_code, SELL.dealer_code,
1092 SELL.client_code, L_STOCK_ISSUER, -- FOR BUG 5917859 SELL.cparty_code,
1093 'N', SELL.product_type,
1094 l_tran_declare_date, l_exp_amt,
1095 0, l_exp_amt_hce,
1096 p_sys_user, p_sys_date);
1097 end if;
1098
1099 fetch SELL_REV into SELL;
1100
1101 end loop; -- of SELL_REV
1102 close SELL_REV;
1103
1104 end if;
1105
1106 -------------------------------------------------
1107 -- Insert confirmation details
1108 -------------------------------------------------
1109 XTR_MISC_P.DEAL_ACTIONS (BUY.deal_type, BUY.deal_no, p_div_id,
1110 'NEW_STOCK_CASH_DIVIDEND',
1111 L_STOCK_ISSUER, -- FOR BUG 5917859 BUY.cparty_code,
1112 BUY.client_code,
1113 p_sys_date,
1114 BUY.company_code, BUY.status_code, null,
1115 BUY.deal_subtype, BUY.currency,
1116 BUY.cparty_advice,
1117 BUY.client_advice,l_int_amt, null);
1118
1119 /* Bug 3737048. Added the select below.
1120 Determine if the tax settlement exposure record has been
1121 generated already. */
1122
1123 select tax_settled_reference
1124 into l_tax_settled_reference
1125 from xtr_rollover_transactions
1126 where deal_number = BUY.deal_no
1127 and deal_type = 'STOCK'
1128 and transaction_number = p_div_id;
1129
1130 /* Bug 3737048. Added the last 2 conditions in the IF statement. */
1131 /* Bug 4383634. Changed the last condition. */
1132
1133 if BUY.income_tax_code is not null and
1134 BUY.settle_account_no is not null and
1135 l_tax_amt is not null and l_tax_amt_hce is not null and
1136 p_payment_date is not null and
1137 l_tax_settled_reference is null then
1138 -------------------------------------------------
1139 -- Insert exposure transactions
1140 -------------------------------------------------
1141 one_step_rec.p_schedule_code := BUY.income_tax_code;
1142 one_step_rec.p_currency := BUY.currency;
1143 one_step_rec.p_amount := l_tax_amt;
1144 one_step_rec.p_settlement_date := p_payment_date;
1145 one_step_rec.p_company_code := BUY.company_code;
1146 one_step_rec.p_settlement_account := BUY.settle_account_no;
1147 one_step_rec.p_cparty_code := BUY.cparty_code;
1148 one_step_rec.p_cparty_account_no := BUY.cparty_ref;
1149
1150 XTR_FPS2_P.ONE_STEP_SETTLEMENT(one_step_rec);
1151
1152 -------------------------------------------------------
1153 -- Update Rollover Rows for related transactions
1154 -------------------------------------------------------
1155
1156 if one_step_rec.p_exp_number is not null then
1157 update XTR_ROLLOVER_TRANSACTIONS
1158 set TAX_SETTLED_REFERENCE = one_step_rec.p_exp_number
1159 where DEAL_TYPE = 'STOCK'
1160 and DEAL_NUMBER = BUY.deal_no
1161 and TRANSACTION_NUMBER = p_div_id;
1162 end if;
1163
1164 end if;
1165
1166 end if; -- remaining_qty <> 0
1167
1168 end if; -- DEAL_DETAIL%found
1169 close DEAL_DETAIL;
1170
1171 end loop; -- BUY_D
1172
1173 END;
1174
1175 ------------------------------------------------------
1176 -- Count if dividends have been created
1177 ------------------------------------------------------
1178 FUNCTION DELETE_CNT(p_div_id NUMBER) return NUMBER is
1179 l_dummy NUMBER := 0;
1180 BEGIN
1181
1182 select count(*)
1183 into l_dummy
1184 from xtr_rollover_transactions
1185 where transaction_number = p_div_id
1186 and deal_type = 'STOCK';
1187
1188 return l_dummy;
1189
1190 END;
1191
1192 ---------------------------------------------
1193 -- Delete Dividend, RT, DDA, Exposure records
1194 ---------------------------------------------
1195 FUNCTION DELETE_DIV (p_div_id NUMBER) return BOOLEAN IS
1196
1197 cursor TAX_TRANS is
1198 select deal_number, -- Bug 2506786 tax_settled_reference
1199 transaction_number
1200 from XTR_ROLLOVER_TRANSACTIONS
1201 where deal_type = 'STOCK'
1202 and transaction_number = p_div_id
1203 and tax_settled_reference is not null;
1204
1205 BEGIN
1206
1207 if not DISABLE_DELETE(p_div_id) then
1208
1209 FOR EXP_ID in TAX_TRANS loop
1210
1211 ----------------------------------------
1212 -- Delete Exposure Transactions and DDA
1213 ----------------------------------------
1214 -- Bug 2506786
1215 XTR_FPS2_P.DELETE_TAX_EXPOSURE(EXP_ID.deal_number,EXP_ID.transaction_number);
1216
1217 END LOOP;
1218
1219 -------------------------------
1220 -- Any DDA's DIVEXP and DIVSET
1221 -------------------------------
1222 delete xtr_deal_date_amounts
1223 where deal_type = 'STOCK'
1224 and deal_subtype in ('BUY','SELL')
1225 and amount_type in ('DIVSET','DIVEXP')
1226 and transaction_number = p_div_id;
1227
1228 -------------------------------
1229 -- Any RT's cash dividend
1230 -------------------------------
1231 delete xtr_rollover_transactions
1232 where deal_type = 'STOCK'
1233 and transaction_number = p_div_id;
1234
1235 -------------------------------
1236 -- Any confirmation details
1237 -------------------------------
1238 delete xtr_confirmation_details
1239 where deal_type = 'STOCK'
1240 and transaction_no = p_div_id;
1241
1242 return TRUE;
1243
1244 else
1245 return FALSE;
1246 end if;
1247
1248 END;
1249
1250 /*============================================================================*/
1251 /*===================== END CASH DIVIDEND PROCEDURES =======================*/
1252 /*============================================================================*/
1253
1254
1255 END XTR_STOCK_PKG;