[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* This procedure is to insert information into DDA table after deal is */
/* commit. */
/************************************************************************/
PROCEDURE INS_STOCK_DDA (p_deal_no IN NUMBER,
p_reverse_dda IN BOOLEAN) as
cursor CUR_DEAL is
select *
from XTR_DEALS_V
where deal_no = p_deal_no;
Select cash_dividend_id, dividend_per_share, declare_date
From xtr_stock_cash_dividends
Where stock_issue_code = D.bond_issue
And record_date >= D.start_date
And nvl(generated_flag, 'N') IN ('G', 'Y');
select ACCOUNT_NUMBER
from XTR_BANK_ACCOUNTS_V
where PARTY_CODE = D.CPARTY_CODE
and CURRENCY = D.CURRENCY
and ((PARTY_TYPE = 'CP' and BANK_SHORT_CODE = D.CPARTY_REF)
or(PARTY_TYPE = 'C' and substr(BANK_SHORT_CODE, 1, 7) = D.CPARTY_REF));
select rounding_factor, hce_rate
from XTR_MASTER_CURRENCIES_V
where currency = p_currency;
INSERT INTO XTR_DEAL_DATE_AMOUNTS
(deal_type, amount_type, date_type, deal_number, transaction_number,
transaction_date, currency,amount, hce_amount, amount_date, transaction_rate,
cashflow_amount, company_code, account_no, status_code,
portfolio_code, dealer_code, client_code, deal_subtype, cparty_code, settle,
product_type, cparty_account_no)
values
('STOCK', 'COMENCE', 'SETTLE', D.DEAL_NO, 1, D.DEAL_DATE, D.CURRENCY,
D.START_AMOUNT, D.START_HCE_AMOUNT, D.START_DATE, D.CAPITAL_PRICE,
decode(D.DEAL_SUBTYPE,'BUY',-1,1) * D.START_AMOUNT, D.COMPANY_CODE,
D.SETTLE_ACCOUNT_NO, D.STATUS_CODE, D.PORTFOLIO_CODE,
D.DEALER_CODE, D.CLIENT_CODE, D.DEAL_SUBTYPE, D.CPARTY_CODE, 'N',
D.PRODUCT_TYPE, l_cparty_acct);
INSERT INTO XTR_DEAL_DATE_AMOUNTS
(deal_type, amount_type, date_type, deal_number, transaction_number,
transaction_date, currency, amount,hce_amount, amount_date, transaction_rate,
cashflow_amount, company_code, account_no, status_code,
portfolio_code, dealer_code, client_code, deal_subtype, cparty_code, settle,
product_type)
values
('STOCK', 'N/A', 'DEALT', D.DEAL_NO, 1, D.DEAL_DATE, D.CURRENCY,
D.START_AMOUNT, D.START_HCE_AMOUNT, D.DEAL_DATE, D.CAPITAL_PRICE,0,
D.COMPANY_CODE, D.SETTLE_ACCOUNT_NO, D.STATUS_CODE,
D.PORTFOLIO_CODE, D.DEALER_CODE, D.CLIENT_CODE, D.DEAL_SUBTYPE, D.CPARTY_CODE,
'N', D.PRODUCT_TYPE);
insert INTO XTR_DEAL_DATE_AMOUNTS
(deal_type, amount_type, date_type, deal_number, transaction_number,
transaction_date, currency, amount,hce_amount, amount_date, transaction_rate,
cashflow_amount, company_code, account_no, status_code,
portfolio_code, dealer_code, client_code, deal_subtype, cparty_code, settle,
product_type, limit_code, limit_party)
values ('STOCK', 'LIMIT', 'LIMIT', D.DEAL_NO, 1, D.DEAL_DATE, D.CURRENCY,
D.START_AMOUNT, D.START_HCE_AMOUNT, D.DEAL_DATE,D.CAPITAL_PRICE, 0,
D.COMPANY_CODE, D.SETTLE_ACCOUNT_NO, D.STATUS_CODE,
D.PORTFOLIO_CODE, D.DEALER_CODE, D.CLIENT_CODE, D.DEAL_SUBTYPE, D.CPARTY_CODE,
'N', D.PRODUCT_TYPE, nvl(D.LIMIT_CODE, 'NILL'),
nvl(D.ACCEPTOR_CODE,D.CPARTY_CODE));
INSERT INTO XTR_DEAL_DATE_AMOUNTS
(deal_type, amount_type, date_type, deal_number, transaction_number,
transaction_date, currency,amount, hce_amount, amount_date, transaction_rate,
cashflow_amount, company_code, account_no, status_code,
portfolio_code, dealer_code, client_code, deal_subtype, cparty_code, settle,
product_type, cparty_account_no)
values
('STOCK', 'COMENCE', 'SETTLE', D.DEAL_NO, 1, D.DEAL_DATE, D.CURRENCY,
D.START_AMOUNT, D.START_HCE_AMOUNT, D.START_DATE, D.CAPITAL_PRICE,
decode(D.DEAL_SUBTYPE,'BUY',-1,1) * D.START_AMOUNT, D.COMPANY_CODE,
D.SETTLE_ACCOUNT_NO, D.STATUS_CODE, D.PORTFOLIO_CODE,
D.DEALER_CODE, D.CLIENT_CODE, D.DEAL_SUBTYPE, D.CPARTY_CODE, 'N',
D.PRODUCT_TYPE, l_cparty_acct);
insert INTO XTR_DEAL_DATE_AMOUNTS
(deal_type, amount_type, date_type, deal_number, transaction_number,
transaction_date, currency, amount,hce_amount, amount_date, transaction_rate,
cashflow_amount, company_code, account_no, status_code,
portfolio_code, dealer_code, client_code, deal_subtype, cparty_code, settle,
product_type)
values ('STOCK', 'N/A', 'DEALT', D.DEAL_NO, 1, D.DEAL_DATE, D.CURRENCY,
D.START_AMOUNT, D.START_HCE_AMOUNT, D.DEAL_DATE, D.CAPITAL_PRICE,0,
D.COMPANY_CODE, D.SETTLE_ACCOUNT_NO, D.STATUS_CODE,
D.PORTFOLIO_CODE, D.DEALER_CODE, D.CLIENT_CODE, D.DEAL_SUBTYPE, D.CPARTY_CODE,
'N', D.PRODUCT_TYPE);
Insert into XTR_DEAL_DATE_AMOUNTS
(deal_type, amount_type, date_type, deal_number, transaction_number,
transaction_date, currency, amount,hce_amount, amount_date, transaction_rate,
cashflow_amount, company_code, account_no, status_code,
portfolio_code, dealer_code, client_code, deal_subtype, cparty_code, settle,
product_type, action_code)
Values ('STOCK', 'DIVEXP', 'DIVDAT', D.DEAL_NO, l_div_id, D.DEAL_DATE, D.CURRENCY,
l_rev_amt, l_rev_amt_hce, l_amt_date, l_div, 0, D.COMPANY_CODE,
D.SETTLE_ACCOUNT_NO, D.STATUS_CODE, D.PORTFOLIO_CODE,
D.DEALER_CODE, D.CLIENT_CODE, D.DEAL_SUBTYPE, D.CPARTY_CODE, 'N',
D.PRODUCT_TYPE, 'REV');
/* This procedure will insert an EXP deal into DDA table if user select */
/* a tax code which is an one-step settlement method */
/****************************************************************************/
If D.TAX_CODE is NOT NULL then
p_one_step_rec.p_schedule_code := D.TAX_CODE;
Update XTR_DEALS
Set tax_settled_reference = p_one_step_rec.p_exp_number
Where deal_no = D.deal_no;
/* This procedure is to update related tables when user */
/* set Stock deal status from 'CURRENT' to 'CANCELLED' */
/************************************************************/
PROCEDURE CANCEL_STOCK (p_deal_no IN NUMBER,
p_deal_subtype IN VARCHAR2,
p_currency IN VARCHAR2) IS
cursor LOCK_DDA_DEAL is
select ROWID
from XTR_DEAL_DATE_AMOUNTS
where deal_number = p_deal_no
for update of status_code nowait;
select ROWID
from XTR_ROLLOVER_TRANSACTIONS
where deal_number = p_deal_no
for update of status_code nowait;
select BKGE_SETTLED_REFERENCE
from XTR_DEALS_V
where deal_type = 'STOCK'
and deal_no = p_deal_no
and bkge_settled_reference is NOT NULL;
select deal_no, quantity, cross_ref_start_date, init_consideration
from XTR_STOCK_ALLOC_DETAILS
where cross_ref_no = p_deal_no;
select transaction_number, quantity, interest_rate,
tax_settled_reference, tax_rate
from XTR_ROLLOVER_TRANSACTIONS
where deal_number = p_buy_deal
and start_date >= p_sell_date;
select rounding_factor, hce_rate
from XTR_MASTER_CURRENCIES_V
where currency = p_currency;
delete from XTR_DEAL_DATE_AMOUNTS
where rowid = l_rowid;
update XTR_ROLLOVER_TRANSACTIONS
set status_code = 'CANCELLED'
where rowid = l_rowid;
delete xtr_confirmation_details
where deal_type = 'STOCK'
and deal_no = p_deal_no;
XTR_FPS2_P.DELETE_TAX_EXPOSURE(p_deal_no, l_dummy);
Delete from XTR_EXPOSURE_TRANSACTIONS
Where transaction_number = l_bkge_ref;
Delete from XTR_DEAL_DATE_AMOUNTS
Where deal_type = 'EXP'
and deal_number = 0
and transaction_number = l_bkge_ref;
-- Update BUY deal's remaining quantity and limit amount
l_limit_amt := l_init_cons;
Update XTR_DEALS
Set remaining_quantity = remaining_quantity + l_quantity,
status_code = 'CURRENT'
where deal_no = l_buy_deal;
Update XTR_DEAL_DATE_AMOUNTS
Set amount = amount + l_limit_amt,
hce_amount = round((amount+l_limit_amt)/l_hce_rate, l_round)
where deal_number = l_buy_deal
and amount_type = 'LIMIT'
and date_type = 'LIMIT';
Update XTR_DEAL_DATE_AMOUNTS
Set status_code = 'CURRENT'
where deal_number = l_buy_deal
and status_code = 'CLOSED';
Update XTR_DEAL_DATE_AMOUNTS
set amount = l_tax_amt,
hce_amount = l_tax_amt_hce,
cashflow_amount = (-1) * l_tax_amt
where deal_type = 'EXP'
and deal_number = 0
and transaction_number = l_tax_ref;
Update XTR_ROLLOVER_TRANSACTIONS
Set quantity = l_trans_quantity,
interest = l_trans_int,
interest_hce = l_hce_int,
tax_amount = l_tax_amt,
tax_amount_hce = l_tax_amt_hce
where deal_number = l_buy_deal
and transaction_number = l_trans_no;
Update XTR_DEAL_DATE_AMOUNTS
Set AMOUNT = l_trans_int,
CASHFLOW_AMOUNT = l_trans_int,
HCE_AMOUNT = l_hce_int
Where deal_number = l_buy_deal
and transaction_number = l_trans_no
and amount_type = 'DIVSET';
Update XTR_STOCK_ALLOC_DETAILS
set remaining_quantity = remaining_quantity + l_quantity
where deal_no = l_buy_deal
and ((cross_ref_start_date > l_sell_start_date)
or (cross_ref_start_date = l_sell_start_date and cross_ref_no > p_deal_no));
Delete from XTR_STOCK_ALLOC_DETAILS
where cross_ref_no = p_deal_no;
select 1
from XTR_STOCK_CASH_DIVIDENDS
where STOCK_ISSUE_CODE = p_stock_issue
and DECLARE_DATE = p_declare_date
and RECORD_DATE = p_record_date
and PAYMENT_DATE = p_payment_date;
of the situation when payment date is updated for a record that has
the status 'Not Generated'. */
-------------------------------------------------------------------------
-- Check that stock dividend combination is unique during update
-- by using the cash dividend id of the record being updated.
-------------------------------------------------------------------------
FUNCTION UNIQUE_STOCK_DIV_EXIST(p_cash_dividend_id NUMBER,
p_stock_issue VARCHAR2,
p_declare_date DATE,
p_record_date DATE) return BOOLEAN IS
cursor EXIST_STOCK_DIV is
select 1
from XTR_STOCK_CASH_DIVIDENDS
where STOCK_ISSUE_CODE = p_stock_issue
and DECLARE_DATE = p_declare_date
and RECORD_DATE = p_record_date
and cash_dividend_id <> p_cash_dividend_id;
FUNCTION DISABLE_DELETE (p_div_id NUMBER) return BOOLEAN IS
cursor CHK_SETTLE is
select 1
from xtr_deal_date_amounts dda
where dda.deal_type = 'STOCK'
and dda.transaction_number = p_div_id
and dda.amount_type = 'DIVSET' and dda.deal_subtype = 'BUY'
and nvl(dda.settle,'N') = 'Y'
union
select 1
from xtr_deal_date_amounts a,
xtr_rollover_transactions b
where a.deal_type = 'EXP'
and a.transaction_number = b.tax_settled_reference
and nvl(a.settle,'N') = 'Y'
and b.deal_type = 'STOCK'
and b.transaction_number = p_div_id
and b.tax_settled_reference is not null;
select 2
from xtr_journals jnl
where jnl.deal_type = 'STOCK'
and jnl.transaction_number = p_div_id
and ((jnl.amount_type = 'DIVSET' and jnl.deal_subtype = 'BUY')
or (jnl.amount_type = 'DIVEXP'))
union
select 2
from xtr_journals a,
xtr_rollover_transactions b
where a.deal_type = 'EXP'
and a.transaction_number = b.tax_settled_reference
and b.deal_type = 'STOCK'
and b.transaction_number = p_div_id
and b.tax_settled_reference is not null;
select 3
from xtr_confirmation_details
where deal_type = 'STOCK'
and transaction_no = p_div_id
and (confirmation_validated_by is not null
or confirmation_validated_on is not null);
if l_dummy = 1 then -- cannot delete
return TRUE;
if l_dummy = 2 then -- cannot delete
return TRUE;
if l_dummy = 3 then -- cannot delete
return TRUE;
select count(*)
into l_dummy
from xtr_deals d
where d.deal_type = 'STOCK'
and d.deal_subtype = 'BUY'
and d.status_code <> 'CANCELLED'
and d.bond_issue = p_stock_issue
and d.start_date <= p_record_date
and d.quantity > (select nvl(sum(b.quantity),0)
from xtr_stock_alloc_details b
where b.deal_no = d.deal_no
and b.cross_ref_start_date <= p_record_date)
and not exists (select 1
from xtr_rollover_transactions r
where r.transaction_number = p_div_id
and r.deal_number = d.deal_no
and r.settle_date IS NOT NULL) ;
select deal_no
from xtr_deals d
where d.deal_type = 'STOCK'
and d.deal_subtype = 'BUY'
and d.status_code <> 'CANCELLED'
and d.deal_no = nvl(p_deal_no,deal_no)
and d.bond_issue = p_stock_issue
and d.start_date <= p_record_date
and d.quantity > (select nvl(sum(b.quantity),0)
from xtr_stock_alloc_details b
where b.deal_no = d.deal_no
and b.cross_ref_start_date <= p_record_date)
and not exists (select 1
from xtr_rollover_transactions r
where r.transaction_number = p_div_id
and r.deal_number = d.deal_no
and r.settle_date IS NOT NULL);
select *
from xtr_deals
where deal_no = l_deal_no;
select ACCOUNT_NUMBER
from XTR_BANK_ACCOUNTS_V
where PARTY_CODE = BUY.CPARTY_CODE
and CURRENCY = BUY.CURRENCY
and ((PARTY_TYPE = 'CP' and BANK_SHORT_CODE = BUY.CPARTY_REF)
or(PARTY_TYPE = 'C' and substr(BANK_SHORT_CODE, 1, 7) = BUY.CPARTY_REF));
select rounding_factor, hce_rate
from XTR_MASTER_CURRENCIES_V
where currency = p_currency;
select a.rounding_factor
from XTR_MASTER_CURRENCIES_V a,
XTR_PRO_PARAM b
where b.param_name = 'SYSTEM_FUNCTIONAL_CCY'
and a.currency = param_value;
select nvl(sum(QUANTITY),0)
from xtr_stock_alloc_details
where deal_no = l_deal_no
and cross_ref_start_date <= p_record_date;
select d.DEAL_NO,
s.QUANTITY,
d.DEAL_DATE,
d.START_DATE,
d.CURRENCY,
d.COMPANY_CODE,
d.SETTLE_ACCOUNT_NO,
d.STATUS_CODE,
d.PORTFOLIO_CODE,
d.DEALER_CODE,
d.CLIENT_CODE,
d.CPARTY_CODE,
d.PRODUCT_TYPE
from xtr_stock_alloc_details s,
xtr_deals d
where s.deal_no = l_buy_deal_no
and s.cross_ref_start_date <= p_record_date
and s.cross_ref_no = d.deal_no
and d.deal_type = 'STOCK'
and d.deal_subtype = 'SELL'
and d.status_code = 'CURRENT';
SELECT STOCK_ISSUER
FROM XTR_STOCK_ISSUES
WHERE STOCK_ISSUE_CODE = p_stock_issue;
Update xtr_rollover_transactions
set settle_date = p_payment_date
where transaction_number = p_div_id
and deal_number = BUY.deal_no;
Insert into xtr_rollover_transactions
(deal_number, transaction_number,
deal_type, deal_subtype,
quantity, interest,
interest_hce, tax_code,
tax_rate, tax_amount,
tax_amount_hce, interest_rate,
currency, dealer_code,
status_code, portfolio_code,
client_code, company_code,
cparty_code, product_type,
deal_date, start_date,
settle_date,
created_by, created_on)
values (BUY.deal_no, p_div_id,
BUY.deal_type, BUY.deal_subtype,
l_remain_qty, l_int_amt,
l_int_amt_hce, BUY.income_tax_code,
l_tax_rate, l_tax_amt,
l_tax_amt_hce, p_div_per_share,
BUY.currency, BUY.dealer_code,
BUY.status_code, BUY.portfolio_code,
BUY.client_code, BUY.company_code,
L_STOCK_ISSUER, -- FOR BUG 5917859 BUY.cparty_code,
BUY.product_type,
l_tran_declare_date, p_record_date,
p_payment_date,
p_sys_user, p_sys_date);
Insert into XTR_DEAL_DATE_AMOUNTS
(deal_type, deal_number,
transaction_number, deal_subtype,
date_type, amount_type,
action_code, transaction_date,
transaction_rate, currency,
company_code, account_no,
status_code, portfolio_code,
dealer_code, client_code,
cparty_code, settle,
product_type, amount_date,
amount, cashflow_amount,
hce_amount, cparty_account_no,
created_by, created_on)
values (BUY.deal_type, BUY.deal_no,
p_div_id, BUY.deal_subtype,
'PAYMENT', 'DIVSET',
null, BUY.deal_date,
p_div_per_share, BUY.currency,
BUY.company_code, BUY.settle_account_no,
BUY.status_code, BUY.portfolio_code,
BUY.dealer_code, BUY.client_code,
L_STOCK_ISSUER, -- FOR BUG 5917859 BUY.cparty_code,
'N',
BUY.product_type, p_payment_date,
l_int_amt, l_int_amt,
l_int_amt_hce, l_cparty_acct,
p_sys_user, p_sys_date);
/* Bug 3737048. Added the Update statement and the IF condition. */
update XTR_DEAL_DATE_AMOUNTS
set currency = BUY.currency
where deal_number = BUY.deal_no
and transaction_number = p_div_id
and date_type = 'DIVDAT'
and amount_type = 'DIVEXP';
Insert into XTR_DEAL_DATE_AMOUNTS
(deal_type, deal_number,
transaction_number,
deal_subtype, date_type,
amount_type, action_code,
transaction_date, transaction_rate,
currency, company_code,
account_no, status_code,
portfolio_code, dealer_code,
client_code, cparty_code,
settle, product_type,
amount_date, amount,
cashflow_amount, hce_amount,
created_by, created_on)
values (BUY.deal_type, BUY.deal_no,
p_div_id,
BUY.deal_subtype, 'DIVDAT',
'DIVEXP', 'POS',
BUY.deal_date, p_div_per_share,
BUY.currency, BUY.company_code,
BUY.settle_account_no, BUY.status_code,
BUY.portfolio_code, BUY.dealer_code,
BUY.client_code, L_STOCK_ISSUER, -- FOR BUG 5917859 BUY.cparty_code,
'N', BUY.product_type,
l_tran_declare_date, l_exp_amt,
0, l_exp_amt_hce,
p_sys_user, p_sys_date);
Insert/Update cash dividend DDA - DIVEXP(REV) for
the SELL deal
------------------------------------------------------------*/
-- Bug 2517289
update XTR_DEAL_DATE_AMOUNTS
set amount = amount + l_exp_amt,
hce_amount = hce_amount + l_exp_amt_hce
where deal_number = SELL.deal_no
and transaction_number = p_div_id
and date_type = 'DIVDAT'
and amount_type = 'DIVEXP'
and action_code = 'REV';
Insert into XTR_DEAL_DATE_AMOUNTS
(deal_type, deal_number,
transaction_number,
deal_subtype, date_type,
amount_type, action_code,
transaction_date, transaction_rate,
currency, company_code,
account_no, status_code,
portfolio_code, dealer_code,
client_code, cparty_code,
settle, product_type,
amount_date, amount,
cashflow_amount, hce_amount,
created_by, created_on)
values ('STOCK', SELL.deal_no,
p_div_id,
'SELL', 'DIVDAT',
'DIVEXP', 'REV',
SELL.deal_date, p_div_per_share,
SELL.currency, SELL.company_code,
SELL.settle_account_no,SELL.status_code,
SELL.portfolio_code, SELL.dealer_code,
SELL.client_code, L_STOCK_ISSUER, -- FOR BUG 5917859 SELL.cparty_code,
'N', SELL.product_type,
l_tran_declare_date, l_exp_amt,
0, l_exp_amt_hce,
p_sys_user, p_sys_date);
/* Bug 3737048. Added the select below.
Determine if the tax settlement exposure record has been
generated already. */
select tax_settled_reference
into l_tax_settled_reference
from xtr_rollover_transactions
where deal_number = BUY.deal_no
and deal_type = 'STOCK'
and transaction_number = p_div_id;
update XTR_ROLLOVER_TRANSACTIONS
set TAX_SETTLED_REFERENCE = one_step_rec.p_exp_number
where DEAL_TYPE = 'STOCK'
and DEAL_NUMBER = BUY.deal_no
and TRANSACTION_NUMBER = p_div_id;
FUNCTION DELETE_CNT(p_div_id NUMBER) return NUMBER is
l_dummy NUMBER := 0;
select count(*)
into l_dummy
from xtr_rollover_transactions
where transaction_number = p_div_id
and deal_type = 'STOCK';
FUNCTION DELETE_DIV (p_div_id NUMBER) return BOOLEAN IS
cursor TAX_TRANS is
select deal_number, -- Bug 2506786 tax_settled_reference
transaction_number
from XTR_ROLLOVER_TRANSACTIONS
where deal_type = 'STOCK'
and transaction_number = p_div_id
and tax_settled_reference is not null;
if not DISABLE_DELETE(p_div_id) then
FOR EXP_ID in TAX_TRANS loop
----------------------------------------
-- Delete Exposure Transactions and DDA
----------------------------------------
-- Bug 2506786
XTR_FPS2_P.DELETE_TAX_EXPOSURE(EXP_ID.deal_number,EXP_ID.transaction_number);
delete xtr_deal_date_amounts
where deal_type = 'STOCK'
and deal_subtype in ('BUY','SELL')
and amount_type in ('DIVSET','DIVEXP')
and transaction_number = p_div_id;
delete xtr_rollover_transactions
where deal_type = 'STOCK'
and transaction_number = p_div_id;
delete xtr_confirmation_details
where deal_type = 'STOCK'
and transaction_no = p_div_id;