[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* This files conatins three procedure to insert/update/delete rows from the
xtr_position_history table for the 'STOCK' deal.
1. Maintain_stk_position_history is called from the form when the
'BUY' stock deal is created or the deal status is set to cancelled
2. Snapshot_cost_of_funds fetches all the 'BUY' stock deals with status code as
current and call the procedure snapshot_stk_position_history. This procedure is
called from the form when the sell deal is created or when the concurrent
program -update average rates is run.
When this program is called from the 'FORM' the deal number is passed for which
the resale is being created and when called through the CP the deal number is
null.
3.Snapshot_stk_position_history recalculates the values and then insert/update
or delete rows from the xtr_postion_history table. This procedure is called by
snapshot_stk_cost_of_funds
*/
PROCEDURE MAINTAIN_STK_POSITION_HISTORY(
P_START_DATE IN DATE,
P_DEAL_NUMBER IN NUMBER,
P_TRANSACTION_NUMBER IN NUMBER,
P_COMPANY_CODE IN VARCHAR2,
P_CURRENCY IN VARCHAR2,
P_DEAL_TYPE IN VARCHAR2,
P_DEAL_SUBTYPE IN VARCHAR2,
P_PRODUCT_TYPE IN VARCHAR2,
P_PORTFOLIO_CODE IN VARCHAR2,
P_CPARTY_CODE IN VARCHAR2,
P_CONTRA_CCY IN VARCHAR2,
P_CURRENCY_COMBINATION IN VARCHAR2,
P_ACCOUNT_NO IN VARCHAR2,
P_TRANSACTION_RATE IN NUMBER,
P_YEAR_CALC_TYPE IN VARCHAR2,
P_BASE_REF_AMOUNT IN NUMBER,
P_BASE_RATE IN NUMBER,
P_STATUS_CODE IN VARCHAR2,
P_INTEREST IN NUMBER,
P_ACTION IN VARCHAR2
) as
L_REF_DATE DATE;
select s.HCE_RATE,s.ROUNDING_FACTOR
from XTR_MASTER_CURRENCIES_V s
where s.CURRENCY = P_CURRENCY;
/* INSERT */
/**************/
if P_ACTION='INSERT' and P_STATUS_CODE='CURRENT' then
L_DEAL_SUBTYPE := P_DEAL_SUBTYPE;
insert into XTR_POSITION_HISTORY(
AS_AT_DATE,
DEAL_TYPE,
DEAL_NUMBER,
TRANSACTION_NUMBER,
COMPANY_CODE,
CPARTY_CODE,
DEAL_SUBTYPE,
PRODUCT_TYPE,
PORTFOLIO_CODE,
CURRENCY,
CONTRA_CCY,
CURRENCY_COMBINATION,
YEAR_CALC_TYPE,
ACCOUNT_NO,
BASE_REF_AMOUNT,
HCE_BASE_REF_AMOUNT,
TRANSACTION_RATE,
BASE_RATE,
INTEREST,
HCE_INTEREST)
values(
T_AS_AT_DATE(i),
P_DEAL_TYPE,
P_DEAL_NUMBER,
P_TRANSACTION_NUMBER,
P_COMPANY_CODE,
P_CPARTY_CODE,
L_DEAL_SUBTYPE,
P_PRODUCT_TYPE,
P_PORTFOLIO_CODE,
P_CURRENCY,
P_CONTRA_CCY,
P_CURRENCY_COMBINATION,
P_YEAR_CALC_TYPE,
P_ACCOUNT_NO,
L_BASE_REF_AMOUNT,
L_HCE_BASE_REF_AMOUNT,
L_TRANSACTION_RATE,
P_BASE_RATE,
L_DAILY_INT,
L_HCE_INT);
/* UPDATE */
/**************/
elsif P_ACTION='UPDATE' and P_STATUS_CODE= 'CANCELLED' then
delete from XTR_POSITION_HISTORY
where AS_AT_DATE >= L_REF_DATE
and DEAL_TYPE = P_DEAL_TYPE
and DEAL_NUMBER = P_DEAL_NUMBER;
/* DELETE */
/**************/
/* this function is not available for the stock deals */
end if;
select s.HCE_RATE,s.ROUNDING_FACTOR
from XTR_MASTER_CURRENCIES_V s
where s.CURRENCY = P_CURRENCY;
select max(AS_AT_DATE + 1)
from XTR_POSITION_HISTORY
where DEAL_TYPE = V_DEAL_TYPE
and DEAL_NUMBER = V_DEAL_NUMBER;
select cross_ref_start_date
from xtr_stock_alloc_details
where deal_no = V_DEAL_NUMBER
and CROSS_REF_START_DATE <= V_AS_AT_DATE
and avg_rate_last_processed is null
order by cross_ref_start_date;
Select min(remaining_quantity), max(cross_ref_start_date)
From XTR_STOCK_ALLOC_DETAILS
Where deal_no = P_DEAL_NUMBER
and cross_ref_start_date <= V_AS_AT_DATE;
select rowid
from XTR_POSITION_HISTORY
where AS_AT_DATE = V_AS_AT_DATE
and DEAL_TYPE = V_DEAL_TYPE
and DEAL_NUMBER = V_DEAL_NUMBER
for update of BASE_REF_AMOUNT NOWAIT;
Delete from XTR_POSITION_HISTORY
Where deal_number = P_DEAL_NUMBER
And as_at_date >= l_last_resale_date;
select price_per_share into
l_price_per_share
FROM xtr_stock_alloc_details a
WHERE deal_no = P_DEAL_NUMBER
and cross_ref_start_date = l_cross_ref_start_date
and cross_ref_no = ( select max(cross_ref_no) -- multiple sales on the same date
FROM xtr_stock_alloc_details
WHERE deal_no = a.deal_no
and cross_ref_start_date = l_cross_ref_start_date);
/* Insert or Update position history table */
/*========================================*/
if L_ROWID is not null then
update XTR_POSITION_HISTORY
set COMPANY_CODE = P_COMPANY_CODE,
CPARTY_CODE = P_CPARTY_CODE,
DEAL_SUBTYPE = P_DEAL_SUBTYPE,
PRODUCT_TYPE = P_PRODUCT_TYPE,
PORTFOLIO_CODE = P_PORTFOLIO_CODE,
CURRENCY = P_CURRENCY,
CONTRA_CCY = P_CONTRA_CCY,
CURRENCY_COMBINATION = P_CURRENCY_COMBINATION,
YEAR_CALC_TYPE = P_YEAR_CALC_TYPE,
ACCOUNT_NO = P_ACCOUNT_NO,
BASE_REF_AMOUNT = l_BASE_REF_AMOUNT,
HCE_BASE_REF_AMOUNT = L_HCE_BASE_REF_AMOUNT,
BASE_RATE = P_BASE_RATE,
TRANSACTION_RATE = l_TRANSACTION_RATE
where rowid=l_rowid;
insert into XTR_POSITION_HISTORY(
AS_AT_DATE,
DEAL_TYPE,
DEAL_NUMBER,
TRANSACTION_NUMBER,
COMPANY_CODE,
CPARTY_CODE,
DEAL_SUBTYPE,
PRODUCT_TYPE,
PORTFOLIO_CODE,
CURRENCY,
CONTRA_CCY,
CURRENCY_COMBINATION,
YEAR_CALC_TYPE,
ACCOUNT_NO,
BASE_REF_AMOUNT,
HCE_BASE_REF_AMOUNT,
TRANSACTION_RATE,
BASE_RATE,
INTEREST,
HCE_INTEREST)
values(
L_AS_AT_DATE,
P_DEAL_TYPE,
P_DEAL_NUMBER,
P_TRANSACTION_NUMBER,
P_COMPANY_CODE,
P_CPARTY_CODE,
P_DEAL_SUBTYPE,
P_PRODUCT_TYPE,
P_PORTFOLIO_CODE,
P_CURRENCY,
P_CONTRA_CCY,
P_CURRENCY_COMBINATION,
P_YEAR_CALC_TYPE,
P_ACCOUNT_NO,
abs(nvl(L_BASE_REF_AMOUNT, P_BASE_REF_AMOUNT)),
abs(L_HCE_BASE_REF_AMOUNT),
L_TRANSACTION_RATE,
P_BASE_RATE,
0,
0);
Update XTR_STOCK_ALLOC_DETAILS
set avg_rate_last_processed = greatest(nvl(avg_rate_last_processed,
L_AS_AT_DATE),
L_AS_AT_DATE)
where deal_no = P_DEAL_NUMBER;
select a.deal_no deal_number,
1 transaction_number,
a.status_code status_code,
a.company_code,
a.cparty_code,
a.deal_type,
a.deal_subtype,
a.currency,
a.currency_buy,
a.currency_sell,
a.product_type,
a.portfolio_code,
a.year_calc_type,
a.year_basis,
a.interest_rate interest_rate,
a.capital_price transaction_rate,
a.base_rate base_rate,
a.calc_basis,
a.start_date,
a.value_date,
a.option_commencement,
a.expiry_date,
a.maturity_date,
a.maturity_account_no account_no,
a.start_amount base_amount,
a.settle_amount second_amount,
a.maturity_amount,
a.start_amount,
a.day_count_type
from XTR_DEALS a
where a.deal_type ='STOCK'
and a.deal_subtype in ('BUY')
and a.status_code ='CURRENT'
and a.start_date <=l_date
and a.deal_no = nvl(p_deal_number, a.deal_no);
Select min(remaining_quantity), max(cross_ref_start_date)
From XTR_STOCK_ALLOC_DETAILS
Where deal_no = D.DEAL_Number
and cross_ref_start_date <= L_DATE;
Update XTR_STOCK_ALLOC_DETAILS
Set avg_rate_last_processed = L_DATE
where deal_no = d.deal_number
and cross_ref_start_date = l_cross_ref_start_date;