[Home] [Help]
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,
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,
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,
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,
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,
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,
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
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
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:
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;
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'
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:
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'
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
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'
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'
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,
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'
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,
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
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,
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;