[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select xtr_deals_s.nextval
from dual;
Select Trunc(sysdate) Into G_Curr_Date From Dual;
delete from xtr_interface_errors
where external_deal_id = ARec_Interface.external_deal_id
and deal_type = ARec_Interface.deal_type;
XTR_LIMITS_P.UPDATE_LIMIT_EXCESS_LOG(deal_num,
1,
Fnd_Global.User_Id,
v_limit_log_return);
/* Call the insert procedure to insert into xtr_deals */
/*----------------------------------------------------*/
CREATE_FX_DEAL(G_Fx_Main_Rec, deal_num);
/* Since the insert is done, we can now delete the rows from the interface table. */
/*---------------------------------------------------------------------------------*/
delete from xtr_deals_interface
where external_deal_id = ARec_Interface.external_deal_id
and deal_type = ARec_Interface.deal_type;
update xtr_deals_interface
set load_status_code = 'ERROR',
last_update_date = G_Curr_Date,
Last_Updated_by = g_user_id
where external_deal_id = ARec_Interface.external_deal_id
and deal_type = ARec_Interface.deal_type;
select 'Y'
into l_dummy
from xtr_parties_v
where party_type = 'C'
and party_code = p_company_code
and rownum = 1;
select 'Y'
into l_temp
from xtr_parties_v
where party_type = 'CP'
and party_category = 'CL'
and party_code = p_client_code
and rownum = 1;
select 'Y'
into l_temp
from xtr_portfolios_v
where company_code = p_company_code
and (external_party is null or external_party = p_cparty_code)
and portfolio = p_portfolio_code
and rownum = 1;
select 'Y'
into l_temp
from xtr_counterparty_limits_v a, xtr_limit_types_v b
where a.company_code = p_company_code
and a.cparty_code = p_cparty_code
and a.limit_code <> 'SETTLE'
and a.limit_code = p_limit_code
and a.limit_type = b.limit_type
and b.fx_invest_fund_type='X'
and nvl(a.authorised,'N') = 'Y'
and nvl(a.expiry_date,sysdate+1) > sysdate
and rownum = 1;
Select 'Y'
Into l_temp
from xtr_master_currencies_v
where nvl(authorised,'N') = 'Y'
And currency = p_currency
and rownum = 1;
select currency_first, currency_second
into g_currency_first, g_currency_second
from xtr_buy_sell_combinations_v
where nvl(authorised,'N') = 'Y'
and currency_buy = p_buy_currency
and currency_sell = p_sell_currency
and rownum = 1;
select 'Y'
into l_temp
from xtr_company_acct_lov_v
where company_code = p_company_code
and currency = p_currency
and account_number = p_account_no
and rownum = 1;
select 'Y'
into l_temp
from xtr_bank_accounts_v
where bank_short_code = p_cparty_ref
and party_code = p_cparty_code
and account_number = p_cparty_account_no
and nvl(authorised,'N') = 'Y'
and currency = p_currency_b
and rownum = 1;
select 'Y'
into l_temp
from xtr_deal_linking_v
where deal_linking_code = p_deal_linking_code
and rownum = 1;
select 'Y'
into l_temp
from xtr_tax_brokerage_setup_v a, xtr_deduction_calcs_v b
where a.reference_code = p_brokerage_code
and a.deal_type = G_Fx_Deal_Type
and a.deduction_type = 'B'
and a.deal_type = b.deal_type
and a.calc_type = b.calc_type
and nvl(a.authorised,'N') = 'Y'
and rownum = 1;
select 'Y'
into l_temp
from xtr_dealer_codes_v
where dealer_code = p_dealer_code
and rownum = 1;
select 'Y'
into l_temp
from xtr_party_info_v
where ((party_type = 'CP' and fx_cparty='Y')
or party_type = 'C')
and party_code = p_cparty_code
and party_code <> p_company_code
and nvl(authorised,'N') = 'Y'
and rownum = 1;
select 'Y'
into l_temp
from xtr_auth_deal_subtypes_v
where deal_type = p_deal_type
and deal_subtype = p_deal_subtype
and rownum = 1;
select deal_subtype
into l_deal_subtype
from xtr_deal_subtypes
where deal_type = p_deal_type
and user_deal_subtype = p_deal_subtype
and nvl(authorised,'N') = 'Y'
and rownum = 1;
select 'Y'
into l_temp
from xtr_auth_product_types_v
where product_type = p_product_type
and deal_type = p_deal_type
and deal_subtype = l_deal_subtype
and rownum = 1;
select code
from xtr_price_models
where code = p_pricing_model
and deal_type = 'FX'
and nvl(authorized,'N') = 'Y';
select 'Y'
into l_temp
from xtr_rm_md_sets
where set_code = p_market_data_set
and nvl(authorized_yn,'N') = 'Y'
and rownum = 1;
select d.amount_type
into l_amount_type
from xtr_tax_brokerage_setup a,
xtr_deduction_calcs_v d
where a.deal_type = p_deal_type
and a.reference_code = p_brokerage_code
and nvl(a.authorised,'N')= 'Y'
and d.deal_type = a.deal_type
and d.calc_type = a.calc_type
and rownum =1;
SELECT deal_subtype
INTO l_deal_subtype
FROM xtr_deal_subtypes
WHERE deal_type = G_Fx_Deal_Type
AND user_deal_subtype = ARec_Interface.DEAL_SUBTYPE
AND rownum = 1;
select m.rounding_factor
from xtr_master_currencies_v m
where m.currency = p_curr;
select p.home_currency,
m.rounding_factor
from xtr_parties_v p,
xtr_master_currencies_v m
where p.party_code = G_Fx_Main_Rec.company_code
and p.party_type = 'C'
and m.currency = p.home_currency;
select round((G_Fx_Main_Rec.buy_amount/ s.hce_rate ),roundfac)
from xtr_master_currencies_v s
where s.currency = G_Fx_Main_Rec.currency_buy;
select round(((G_Fx_Main_Rec.buy_amount / G_Fx_Main_Rec.base_rate -
G_Fx_Main_Rec.sell_amount )/ s.hce_rate),roundfac),
round(((G_Fx_Main_Rec.buy_amount * G_Fx_Main_Rec.base_rate -
G_Fx_Main_Rec.sell_amount )/ s.hce_rate),roundfac)
from xtr_master_currencies_v s
where s.currency = G_Fx_Main_Rec.currency_sell ;
select m.rounding_factor
from xtr_master_currencies_v m
where m.currency = G_Fx_Main_Rec.currency_sell;
select m.rounding_factor
from xtr_master_currencies_v m
where m.currency = p_curr;
select parameter_value
from xtr_company_parameters
where company_code = G_Fx_Main_Rec.COMPANY_CODE
and parameter_code = 'IMPORT_FXTOL';
select dealer_code
from xtr_dealer_codes_v
where user_id = p_fnd_user;
INSERT INTO xtr_deals
(
EXTERNAL_DEAL_ID ,
DEAL_NO ,
DEAL_TYPE ,
DEALER_CODE ,
COMPANY_CODE ,
CPARTY_CODE ,
CLIENT_CODE ,
PORTFOLIO_CODE ,
LIMIT_CODE ,
DEAL_SUBTYPE ,
PRODUCT_TYPE ,
DEAL_DATE ,
VALUE_DATE ,
BASE_DATE ,
CURRENCY_BUY ,
CURRENCY_SELL ,
BUY_AMOUNT ,
SELL_AMOUNT ,
BUY_ACCOUNT_NO ,
SELL_ACCOUNT_NO ,
BASE_RATE ,
TRANSACTION_RATE ,
COMMENTS ,
EXTERNAL_COMMENTS ,
INTERNAL_TICKET_NO ,
EXTERNAL_CPARTY_NO ,
CPARTY_ACCOUNT_NO ,
CPARTY_REF ,
NO_OF_DAYS ,
PRICING_MODEL ,
MARKET_DATA_SET ,
DEAL_LINKING_CODE ,
BROKERAGE_CODE ,
BROKERAGE_RATE ,
BROKERAGE_AMOUNT ,
BROKERAGE_CURRENCY ,
BUY_HCE_AMOUNT ,
SELL_HCE_AMOUNT ,
FORWARD_HCE_AMOUNT ,
PORTFOLIO_AMOUNT ,
STATUS_CODE ,
DUAL_AUTHORISATION_BY ,
DUAL_AUTHORISATION_ON ,
CREATED_BY ,
CREATED_ON ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE )
VALUES
(
ARec_Fx.EXTERNAL_DEAL_ID ,
p_deal_no ,
ARec_Fx.DEAL_TYPE ,
ARec_Fx.DEALER_CODE ,
ARec_Fx.COMPANY_CODE ,
ARec_Fx.CPARTY_CODE ,
ARec_Fx.CLIENT_CODE ,
ARec_Fx.PORTFOLIO_CODE ,
ARec_Fx.LIMIT_CODE ,
ARec_Fx.DEAL_SUBTYPE ,
ARec_Fx.PRODUCT_TYPE ,
ARec_Fx.DEAL_DATE ,
ARec_Fx.VALUE_DATE ,
ARec_Fx.DEAL_DATE ,
ARec_Fx.CURRENCY_BUY ,
ARec_Fx.CURRENCY_SELL ,
ARec_Fx.BUY_AMOUNT ,
ARec_Fx.SELL_AMOUNT ,
ARec_Fx.BUY_ACCOUNT_NO ,
ARec_Fx.SELL_ACCOUNT_NO ,
ARec_Fx.BASE_RATE ,
ARec_Fx.TRANSACTION_RATE ,
ARec_Fx.COMMENTS ,
ARec_Fx.EXTERNAL_COMMENTS ,
ARec_Fx.INTERNAL_TICKET_NO ,
ARec_Fx.EXTERNAL_CPARTY_NO ,
Arec_Fx.CPARTY_ACCOUNT_NO ,
ARec_Fx.CPARTY_REF ,
ARec_Fx.NO_OF_DAYS ,
ARec_Fx.PRICING_MODEL ,
ARec_Fx.MARKET_DATA_SET ,
ARec_Fx.DEAL_LINKING_CODE ,
ARec_Fx.BROKERAGE_CODE ,
ARec_Fx.BROKERAGE_RATE ,
ARec_Fx.BROKERAGE_AMOUNT ,
ARec_Fx.BROKERAGE_CURRENCY ,
ARec_Fx.BUY_HCE_AMOUNT ,
ARec_Fx.SELL_HCE_AMOUNT ,
ARec_Fx.FORWARD_HCE_AMOUNT ,
ARec_Fx.PORTFOLIO_AMOUNT ,
ARec_Fx.STATUS_CODE ,
l_dual_user ,
l_dual_date ,
nvl(l_user,G_User_Id) ,
g_curr_date ,
--decode(ARec_Fx.ATTRIBUTE_CATEGORY,'Global Data Elements','',ARec_Fx.ATTRIBUTE_CATEGORY) ,
ARec_Fx.ATTRIBUTE_CATEGORY ,
ARec_Fx.ATTRIBUTE1 ,
ARec_Fx.ATTRIBUTE2 ,
ARec_Fx.ATTRIBUTE3 ,
ARec_Fx.ATTRIBUTE4 ,
ARec_Fx.ATTRIBUTE5 ,
ARec_Fx.ATTRIBUTE6 ,
ARec_Fx.ATTRIBUTE7 ,
ARec_Fx.ATTRIBUTE8 ,
ARec_Fx.ATTRIBUTE9 ,
ARec_Fx.ATTRIBUTE10 ,
ARec_Fx.ATTRIBUTE11 ,
ARec_Fx.ATTRIBUTE12 ,
ARec_Fx.ATTRIBUTE13 ,
ARec_Fx.ATTRIBUTE14 ,
ARec_Fx.ATTRIBUTE15 ,
FND_GLOBAL.conc_request_id ,
FND_GLOBAL.prog_appl_id ,
FND_GLOBAL.conc_program_id ,
g_curr_date
);
UPDATE xtr_deal_date_amounts
SET dual_authorisation_by = l_dual_user,
dual_authorisation_on = l_dual_date
WHERE deal_number = p_deal_no;
UPDATE xtr_confirmation_details
SET confirmation_validated_by = l_dual_user,
confirmation_validated_on = l_dual_date
WHERE deal_no = p_deal_no;
UPDATE xtr_deals
SET dual_authorisation_on = l_dual_date
WHERE deal_no = p_deal_no;