[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select text
from xtr_sys_languages_vl
where item_name = p_Field_Name;
Select sum(cashflow_amount) balance
from xtr_deal_date_amounts
where cparty_code = p_party_code
and company_code = p_company_code
and currency = p_currency
and deal_type = 'IG'
and amount_type = 'PRINFLW';
select 'Y'
from XTR_PARTIES_V
where party_code = c_comp
and party_type = 'C';
select mirror_Deal
from xtr_intergroup_transfers
where company_Code = c_comp
and party_Code = c_cparty
and currency = c_curr
and mirror_Deal = 'Y';
procedure UPDATE_PRICING_MODEL(p_company_code VARCHAR2,
p_party_code VARCHAR2,
p_currency VARCHAR2,
p_pricing_model VARCHAR2) is
-----------------------------------------------------------------------------------------------------
cursor get_pricing_model is
select pricing_model, deal_number
from xtr_intergroup_transfers_v
where company_code = p_company_code
and party_code = p_party_code
and currency = p_currency;
UPDATE xtr_intergroup_transfers
SET pricing_model = p_pricing_model
WHERE deal_number = l_deal_number;
select pricing_model
from xtr_intergroup_transfers_v
where company_code = p_company_code
and party_code = p_party_code
and currency = p_currency;
select default_pricing_model
from xtr_product_types_v
where deal_type = 'IG'
and product_type = p_product_type;
select count(*)
from xtr_price_models
where deal_type = 'IG'
and authorized = 'Y';
select code
from xtr_price_models
where deal_type = 'IG'
and authorized = 'Y';
select sob.currency_code
from xtr_party_info pinfo, gl_sets_of_books sob
where pinfo.party_code = p_company_code
and pinfo.set_of_books_id = sob.set_of_books_id;
select count(*)
into l_count
from XTR_INTERGROUP_TRANSFERS
where company_code = p_company_code
and party_code = p_party_code
and currency = p_currency
and transfer_date = p_transfer_Date
and principal_action = p_action_code
and principal_adjust = p_principal_adjust
and company_account_no = p_companY_account
and party_account_no = p_party_account;
select 'Y'
from xtr_ig_journal_structures
where company_code = comp_code
and cparty_code = party_code
and cp_currency = currency
and cp_acct_no = party_acct_no;
select currency
from xtr_bank_Accounts
where account_number = party_acct_no
and party_code = party_code;
select xtr_ig_journal_structures_s.nextval into l_jrnl_struc_id from dual;
insert into xtr_ig_journal_structures( xtr_ig_journal_structure_id,company_code,cparty_code,
cp_currency,cp_acct_no,created_by,creation_Date,last_updated_by,
last_update_date,last_update_login)
values (l_jrnl_struc_id,p_company_code,p_party_code,
l_currency,p_party_acct,FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,
SYSDATE,FND_GLOBAL.LOGIN_ID);
select 'Y'
from XTR_PARTIES_V
where party_code = c_cparty
and ((internal_pty = 'Y' and cross_ref_to_other_party = c_comp)
or (party_type = 'C' and party_code <> c_comp));
select ig_year_basis
from XTR_MASTER_CURRENCIES_V
where currency = c_curr
and nvl(authorised, 'N') = 'Y';
select 'Y'
from XTR_COMPANY_ACCT_LOV_V
where company_code = c_comp
and account_number = c_comp_acct
and currency = c_curr;
select 'Y'
from XTR_BANK_ACCOUNTS_V
where party_code = c_party
and currency = c_curr
and account_number = c_party_acct
and nvl(authorised,'N') = 'Y'
and nvl(setoff_account_yn,'N') <> 'Y'
and bank_code IS NOT NULL;
select ACTION_CODE
from XTR_AMOUNT_ACTIONS
where deal_type = 'IG'
and amount_type = 'PRINFLW'
and ( (user_action_code = c_action and nvl(G_Ig_External_Source,'@@@') not in (C_ZBA, C_CL)) or
(action_code = c_action and nvl(G_Ig_External_Source,'@@@') in (C_ZBA, C_CL)) ); -- 3800146 actual code
select 'Y'
from XTR_AUTH_PRODUCT_TYPES_V
where deal_type = 'IG'
and Product_Type = c_product;
select 'Y'
from XTR_PORTFOLIOS_V
where company_code = c_comp
and nvl(cmf_yn,'N') = 'N'
and (external_party is null or external_party = c_cparty)
and portfolio = c_portfolio;
select code
from xtr_price_models
where code = p_pricing_model
and deal_type = 'IG'
and nvl(authorized,'N') = 'Y';
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_dealer_codes_v
where dealer_code = p_dealer_code
and rownum = 1;
select count(*)
into l_dummy
from fnd_lookups
where lookup_type='XTR_DAY_COUNT_TYPE'
and lookup_code=p_day_count_type;
select count(*)
into l_dummy
from fnd_lookups
where lookup_type='XTR_ROUNDING_TYPE'
and lookup_code=p_rounding_type;
select 'Y'
from xtr_counterparty_limits_v a,
xtr_limit_types_v b
where a.company_code = c_comp
and a.cparty_code = c_cparty
and a.limit_code = c_limit
and a.limit_type = b.limit_type
and nvl(a.authorised,'N') = 'Y'
and (a.expiry_date > G_Ig_curr_date or a.expiry_date is null )
and ((b.fx_invest_fund_type in ('F','X') and c_limit_type = 'FUND')
or (b.fx_invest_fund_type in ('I','X') and c_limit_type = 'INVEST'));
select 'Y'
from xtr_PARTIES_v p,
xtr_MASTER_CURRENCIES_v m
where p.PARTY_CODE = p_comp
and m.CURRENCY = p.HOME_CURRENCY;
SELECT rd.batch_id
FROM xtr_intergroup_transfers it,
xtr_revaluation_details rd
WHERE it.deal_number = l_deal_no
AND it.deal_number = rd.deal_no
AND period_to >= p_transfer_date; -- bug 4367386
SELECT r.batch_id
FROM xtr_batches b,
xtr_batch_events e,
xtr_accrls_amort r
WHERE r.deal_no = l_deal_no
AND r.batch_id = b.batch_id
AND b.batch_id = e.batch_id
AND r.period_to >= p_transfer_date -- bug 4367386
AND e.event_code = 'ACCRUAL';
G_Ig_Main_Rec.UPDATED_BY := null;
G_Ig_Main_Rec.UPDATED_ON := null;
G_Ig_Main_Rec.PROGRAM_UPDATE_DATE := null;
select ROUNDING_FACTOR
from xtr_MASTER_CURRENCIES_v
where CURRENCY = G_Ig_Main_Rec.CURRENCY;
select a.TRANSFER_DATE,
a.BALANCE_OUT ,
a.BALANCE_OUT_HCE,
a.INTEREST_RATE,
(nvl(a.ACCUM_INTEREST_BF,0) + nvl(a.INTEREST,0) - nvl(a.INTEREST_SETTLED,0)),
(nvl(a.ACCUM_INTEREST_BF_HCE,0) + nvl(a.INTEREST_HCE,0) - nvl(a.INTEREST_SETTLED_HCE,0)),
a.TRANSACTION_NUMBER,
a.ACCRUAL_INTEREST,
a.ROUNDING_TYPE, --* Add for Interest Project
a.DAY_COUNT_TYPE --* Add for Interest Project
from xtr_INTERGROUP_TRANSFERS_v a
where a.PARTY_CODE = G_Ig_Main_Rec.PARTY_CODE
and a.COMPANY_CODE = G_Ig_Main_Rec.COMPANY_CODE
and a.CURRENCY = G_Ig_Main_Rec.CURRENCY
and a.TRANSFER_DATE <= G_Ig_Main_Rec.TRANSFER_DATE
order by a.TRANSFER_DATE desc, a.TRANSACTION_NUMBER desc;
select min(TRANSFER_DATE)
from xtr_intergroup_transfers
where party_code = G_Ig_Main_Rec.PARTY_CODE
and company_code = G_Ig_Main_Rec.COMPANY_CODE
and currency = G_Ig_Main_Rec.CURRENCY;
SELECT DAY_COUNT_TYPE
from XTR_INTERGROUP_TRANSFERS
where company_code = G_Ig_Main_Rec.COMPANY_CODE
and party_code = G_Ig_Main_Rec.PARTY_CODE
and currency = G_Ig_Main_Rec.CURRENCY
and transfer_date = (select max(transfer_date)
from xtr_intergroup_transfers
where company_code = G_Ig_Main_Rec.COMPANY_CODE
and party_code = G_Ig_Main_Rec.PARTY_CODE
and currency = G_Ig_Main_Rec.CURRENCY
and transfer_date < prv_date)
order by transaction_number desc;
-- Select Day Count type of previous transaction
OPEN PRV_DAY_COUNT_TYPE;
select m.ROUNDING_FACTOR
from xtr_PARTIES_v p,
xtr_MASTER_CURRENCIES_v m
where p.PARTY_CODE = G_Ig_Main_Rec.COMPANY_CODE
and m.CURRENCY = p.HOME_CURRENCY;
select nvl(round((nvl(G_Ig_Main_Rec.INTEREST,0) / s.hce_rate),roundfac),0),
nvl(round((G_Ig_Main_Rec.PRINCIPAL_ADJUST / s.hce_rate),roundfac),0),
nvl(round((G_Ig_Main_Rec.BALANCE_OUT / s.hce_rate),roundfac),0),
nvl(round((nvl(G_Ig_Main_Rec.INTEREST_SETTLED,0) / s.hce_rate),roundfac),0)
from xtr_MASTER_CURRENCIES_v s
where s.CURRENCY = G_Ig_Main_Rec.CURRENCY;
select deal_number,
transaction_number
from xtr_intergroup_transfers
where company_Code = c_comp
and party_Code = c_cparty
and currency = c_curr
order by transaction_number desc;
select XTR_DEALS_S.nextval
from dual;
Insert into XTR_INTERGROUP_TRANSFERS(
EXTERNAL_DEAL_ID ,
ACCUM_INTEREST_BF ,
ACCUM_INTEREST_BF_HCE ,
BALANCE_BF ,
BALANCE_BF_HCE ,
BALANCE_OUT ,
BALANCE_OUT_HCE ,
COMMENTS ,
COMPANY_ACCOUNT_NO ,
COMPANY_CODE ,
CREATED_BY ,
CREATED_ON ,
CURRENCY ,
DEAL_NUMBER ,
DEAL_TYPE ,
INTEREST ,
INTEREST_HCE ,
INTEREST_RATE ,
INTEREST_SETTLED ,
INTEREST_SETTLED_HCE ,
LIMIT_CODE ,
LIMIT_CODE_INVEST ,
NO_OF_DAYS ,
PARTY_ACCOUNT_NO ,
PARTY_CODE ,
PORTFOLIO ,
PRINCIPAL_ACTION ,
PRINCIPAL_ADJUST ,
PRINCIPAL_ADJUST_HCE ,
PRODUCT_TYPE ,
PRICING_MODEL ,
SETTLE_DATE ,
TRANSACTION_NUMBER ,
TRANSFER_DATE ,
ACCRUAL_INTEREST ,
FIRST_BATCH_ID ,
LAST_BATCH_ID ,
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 ,
MIRROR_DEAL ,
MIRROR_DEAL_NUMBER ,
MIRROR_TRANSACTION_NUMBER ,
ROUNDING_TYPE ,
DAY_COUNT_TYPE ,
ORIGINAL_AMOUNT ,
UPDATED_BY ,
UPDATED_ON ,
DEAL_LINKING_CODE ,
DEALER_CODE,
EXTERNAL_SOURCE -- 3800146 -- **********************************************************************************
)
Values
(
decode(G_Ig_External_Source,null,ARec_IG.EXTERNAL_DEAL_ID,null), -- 3800146 not needed for ZBA/CL ******************************
ARec_IG.ACCUM_INTEREST_BF ,
ARec_IG.ACCUM_INTEREST_BF_HCE ,
ARec_IG.BALANCE_BF ,
ARec_IG.BALANCE_BF_HCE ,
ARec_IG.BALANCE_OUT ,
ARec_IG.BALANCE_OUT_HCE ,
ARec_IG.COMMENTS ,
ARec_IG.COMPANY_ACCOUNT_NO ,
ARec_IG.COMPANY_CODE ,
G_Ig_user ,
G_Ig_SysDate ,
ARec_IG.CURRENCY ,
ARec_IG.DEAL_NUMBER ,
ARec_IG.DEAL_TYPE ,
ARec_IG.INTEREST ,
ARec_IG.INTEREST_HCE ,
ARec_IG.INTEREST_RATE ,
ARec_IG.INTEREST_SETTLED ,
ARec_IG.INTEREST_SETTLED_HCE ,
ARec_IG.LIMIT_CODE ,
ARec_IG.LIMIT_CODE_INVEST ,
ARec_IG.NO_OF_DAYS ,
ARec_IG.PARTY_ACCOUNT_NO ,
ARec_IG.PARTY_CODE ,
ARec_IG.PORTFOLIO ,
ARec_IG.PRINCIPAL_ACTION ,
ARec_IG.PRINCIPAL_ADJUST ,
ARec_IG.PRINCIPAL_ADJUST_HCE ,
ARec_IG.PRODUCT_TYPE ,
ARec_IG.PRICING_MODEL ,
ARec_IG.SETTLE_DATE ,
ARec_IG.TRANSACTION_NUMBER ,
ARec_IG.TRANSFER_DATE ,
ARec_IG.ACCRUAL_INTEREST ,
ARec_IG.FIRST_BATCH_ID ,
ARec_IG.LAST_BATCH_ID ,
ARec_IG.ATTRIBUTE_CATEGORY ,
ARec_IG.ATTRIBUTE1 ,
ARec_IG.ATTRIBUTE2 ,
ARec_IG.ATTRIBUTE3 ,
ARec_IG.ATTRIBUTE4 ,
ARec_IG.ATTRIBUTE5 ,
ARec_IG.ATTRIBUTE6 ,
ARec_IG.ATTRIBUTE7 ,
ARec_IG.ATTRIBUTE8 ,
ARec_IG.ATTRIBUTE9 ,
ARec_IG.ATTRIBUTE10 ,
ARec_IG.ATTRIBUTE11 ,
ARec_IG.ATTRIBUTE12 ,
ARec_IG.ATTRIBUTE13 ,
ARec_IG.ATTRIBUTE14 ,
ARec_IG.ATTRIBUTE15 ,
fnd_global.conc_request_id ,
fnd_global.prog_appl_id ,
fnd_global.conc_program_id ,
G_Ig_SysDate ,
G_Ig_mirror_deal ,
G_Ig_orig_deal_no ,
G_Ig_orig_trans_no ,
ARec_IG.ROUNDING_TYPE ,
ARec_IG.DAY_COUNT_TYPE ,
Arec_IG.ORIGINAL_AMOUNT ,
-- ARec_IG.MIRROR_DEAL ,
-- Arec_IG.MIRROR_DEAL_NUMBER ,
-- Arec_IG.MIRROR_TRANSACTION_NUMBER ,
G_Ig_user ,
G_Ig_SysDate ,
Arec_IG.DEAL_LINKING_CODE ,
Arec_IG.DEALER_CODE,
Arec_IG.EXTERNAL_SOURCE -- 3800146 *************************************************************************
);
UPDATE_PRICING_MODEL(ARec_IG.COMPANY_CODE,
ARec_IG.PARTY_CODE,
ARec_IG.CURRENCY,
ARec_IG.PRICING_MODEL);
select XTR_SETTLEMENT_NUMBER_S.NEXTVAL into p_settle_no from DUAL;
insert into xtr_DEAL_DATE_AMOUNTS_v
(DEAL_TYPE,AMOUNT_TYPE,DATE_TYPE,DEAL_NUMBER,TRANSACTION_NUMBER,
TRANSACTION_DATE,CURRENCY,AMOUNT,HCE_AMOUNT,AMOUNT_DATE,
CASHFLOW_AMOUNT,COMPANY_CODE,ACCOUNT_NO,ACTION_CODE,CPARTY_ACCOUNT_NO,
STATUS_CODE,CPARTY_CODE,
SETTLE,SETTLEMENT_NUMBER,SETTLEMENT_AUTHORISED_BY,ACTUAL_SETTLEMENT_DATE,TRANS_MTS,AUDIT_INDICATOR, -- 3800146 ******************
DEAL_SUBTYPE,PRODUCT_TYPE, PORTFOLIO_CODE,
dual_authorisation_by, dual_authorisation_on)
---,LIMIT_CODE)
values
('IG','PRINFLW','COMENCE',G_Ig_Main_Rec.DEAL_NUMBER,G_Ig_Main_Rec.TRANSACTION_NUMBER,
G_Ig_curr_date,G_Ig_Main_Rec.CURRENCY,abs(G_Ig_Main_Rec.PRINCIPAL_ADJUST),
abs(G_Ig_Main_Rec.PRINCIPAL_ADJUST_HCE),G_Ig_Main_Rec.TRANSFER_DATE,
decode(G_Ig_Main_Rec.COMPANY_ACCOUNT_NO,NULL,0,
decode(G_Ig_Main_Rec.PRINCIPAL_ACTION,'PAY',(-1) * G_Ig_Main_Rec.PRINCIPAL_ADJUST,
G_Ig_Main_Rec.PRINCIPAL_ADJUST)),G_Ig_Main_Rec.COMPANY_CODE,
G_Ig_Main_Rec.COMPANY_ACCOUNT_NO,G_Ig_Main_Rec.PRINCIPAL_ACTION,G_Ig_Main_Rec.PARTY_ACCOUNT_NO,
'CURRENT',G_Ig_Main_Rec.PARTY_CODE,
l_settle,l_settle_no,l_settle_auth,l_settle_date,l_trans_mts,l_audit_indic, -- 3800146 *******************************
decode(G_Ig_Main_Rec.PRINCIPAL_ACTION,'PAY',
'INVEST','FUND'),G_Ig_Main_Rec.PRODUCT_TYPE,G_Ig_Main_Rec.PORTFOLIO,
G_Ig_user, G_Ig_curr_date);
XTR_LIMITS_P.UPDATE_LIMIT_EXCESS_LOG(l_settle_no,
null,
G_Ig_user,
G_Main_log_id);
insert into xtr_DEAL_DATE_AMOUNTS_v
(DEAL_TYPE,AMOUNT_TYPE,DATE_TYPE,DEAL_NUMBER,TRANSACTION_NUMBER,
TRANSACTION_DATE,CURRENCY,AMOUNT,HCE_AMOUNT,AMOUNT_DATE,
CASHFLOW_AMOUNT,COMPANY_CODE,ACCOUNT_NO,ACTION_CODE,CPARTY_ACCOUNT_NO,
STATUS_CODE,CPARTY_CODE,SETTLE,DEAL_SUBTYPE,PRODUCT_TYPE,
PORTFOLIO_CODE,
dual_authorisation_by, dual_authorisation_on) ---- ,LIMIT_CODE)
values
('IG','INTSET','SETTLE',G_Ig_Main_Rec.DEAL_NUMBER,G_Ig_Main_Rec.TRANSACTION_NUMBER,G_Ig_curr_date,
G_Ig_Main_Rec.CURRENCY,abs(G_Ig_Main_Rec.INTEREST_SETTLED),abs(G_Ig_Main_Rec.INTEREST_SETTLED_HCE),
nvl(G_Ig_Main_Rec.SETTLE_DATE,G_Ig_Main_Rec.TRANSFER_DATE),
decode(G_Ig_Main_Rec.COMPANY_ACCOUNT_NO,null,0,G_Ig_Main_Rec.INTEREST_SETTLED),
G_Ig_Main_Rec.COMPANY_CODE,G_Ig_Main_Rec.COMPANY_ACCOUNT_NO,
decode(sign(G_Ig_Main_Rec.INTEREST_SETTLED),-1,'PAY','REC'),
G_Ig_Main_Rec.PARTY_ACCOUNT_NO,'CURRENT',G_Ig_Main_Rec.PARTY_CODE,'N',
decode(sign(G_Ig_Main_Rec.INTEREST_SETTLED),-1,'FUND','INVEST'),
G_Ig_Main_Rec.PRODUCT_TYPE,G_Ig_Main_Rec.PORTFOLIO,
G_Ig_user, G_Ig_curr_date);
insert into xtr_DEAL_DATE_AMOUNTS_v
(DEAL_TYPE,AMOUNT_TYPE,DATE_TYPE,DEAL_NUMBER,TRANSACTION_NUMBER,
TRANSACTION_DATE,CURRENCY,AMOUNT,HCE_AMOUNT,AMOUNT_DATE,
CASHFLOW_AMOUNT,COMPANY_CODE,ACCOUNT_NO,ACTION_CODE,CPARTY_ACCOUNT_NO,
STATUS_CODE,CPARTY_CODE,
SETTLE,SETTLEMENT_NUMBER,SETTLEMENT_AUTHORISED_BY,ACTUAL_SETTLEMENT_DATE,TRANS_MTS,AUDIT_INDICATOR, -- 3800146 ****************
DEAL_SUBTYPE,PRODUCT_TYPE, PORTFOLIO_CODE,
dual_authorisation_by, dual_authorisation_on)
---,LIMIT_CODE)
values
('IG','PRINFLW','COMENCE',G_Ig_Mirror_Rec.DEAL_NUMBER,G_Ig_Mirror_Rec.TRANSACTION_NUMBER,
G_Ig_curr_date,G_Ig_Mirror_Rec.CURRENCY,abs(G_Ig_Mirror_Rec.PRINCIPAL_ADJUST),
abs(G_Ig_Mirror_Rec.PRINCIPAL_ADJUST_HCE),G_Ig_Mirror_Rec.TRANSFER_DATE,
decode(G_Ig_Mirror_Rec.COMPANY_ACCOUNT_NO,NULL,0,
decode(G_Ig_Mirror_Rec.PRINCIPAL_ACTION,'PAY',(-1) * G_Ig_Mirror_Rec.PRINCIPAL_ADJUST,
G_Ig_Mirror_Rec.PRINCIPAL_ADJUST)),G_Ig_Mirror_Rec.COMPANY_CODE,
G_Ig_Mirror_Rec.COMPANY_ACCOUNT_NO,G_Ig_Mirror_Rec.PRINCIPAL_ACTION,G_Ig_Mirror_Rec.PARTY_ACCOUNT_NO,
'CURRENT',G_Ig_Mirror_Rec.PARTY_CODE,
l_settle,l_settle_no,l_settle_auth,l_settle_date,l_trans_mts,l_audit_indic, -- 3800146 ***************************
decode(G_Ig_Mirror_Rec.PRINCIPAL_ACTION,'PAY', 'INVEST','FUND'),G_Ig_Mirror_Rec.PRODUCT_TYPE,G_Ig_Mirror_Rec.PORTFOLIO,
G_Ig_user, G_Ig_curr_date);
XTR_LIMITS_P.UPDATE_LIMIT_EXCESS_LOG(l_settle_no,
null,
G_Ig_user,
G_Mirror_log_id);
p_update IN VARCHAR2,
p_rounding_type IN VARCHAR2, --* Add for Interest Override
p_day_count_type IN VARCHAR2, --* Add for Interest Override
p_types_update IN VARCHAR2) is --* Add for Interest Override
-- p_error OUT VARCHAR2) is
-----------------------------------------------------------------------------------------------------
-- only if there are subsequent transactions;
select a.hce_rate,
a.rounding_factor,
a.ig_year_basis,
m.rounding_factor
from xtr_MASTER_CURRENCIES_V a,
xtr_PARTIES_V p,
xtr_MASTER_CURRENCIES_V m
where a.CURRENCY = p_currency
and p.PARTY_CODE = p_company_code
and m.CURRENCY = p.HOME_CURRENCY;
select rowid,
DEAL_NUMBER,
TRANSFER_DATE,
nvl(BALANCE_OUT,0),
nvl(BALANCE_OUT_HCE,0),
nvl(PRINCIPAL_ADJUST,0),
PRINCIPAL_ACTION,
INTEREST_RATE,
nvl(INTEREST_SETTLED,0),
nvl(NO_OF_DAYS,0),
TRANSACTION_NUMBER,
nvl(ACCUM_INTEREST_BF,0),
nvl(INTEREST,0),
LIMIT_CODE,
LIMIT_CODE_INVEST,
PRODUCT_TYPE,
PORTFOLIO,
nvl(ACCRUAL_INTEREST,0),
ROUNDING_TYPE, --* Add for Interest Override
DAY_COUNT_TYPE --* Add for Interest Override
from XTR_INTERGROUP_TRANSFERS_v
where PARTY_CODE = p_party_code
and CURRENCY = p_currency
and COMPANY_CODE = p_company_code
and TRANSFER_DATE >= p_transfer_date
order by TRANSFER_DATE asc, TRANSACTION_NUMBER asc;
select b.fx_invest_fund_type
from xtr_counterparty_limits_v a,
xtr_limit_types_v b
where a.company_code = p_company_code
and a.cparty_code = p_party_code
and a.limit_type = b.limit_type
and a.limit_code = c_limit_code;
SELECT DAY_COUNT_TYPE
FROM XTR_INTERGROUP_TRANSFERS
WHERE PARTY_CODE = p_party_code
AND CURRENCY = p_currency
AND COMPANY_CODE = p_company_code
AND TRANSFER_DATE = (select max(transfer_date)
from xtr_intergroup_transfers
where party_code = p_party_code
and currency = p_currency
and company_code = p_company_code
and transfer_date < prv_date)
order by transaction_number desc;
select min(TRANSFER_DATE)
into l_oldest_date
from XTR_INTERGROUP_TRANSFERS
where PARTY_CODE = p_party_code
and CURRENCY = p_currency
and COMPANY_CODE = p_company_code;
if p_update = 'Y' then
update xtr_INTERGROUP_TRANSFERS_v
set BALANCE_BF = prv_bal_out,
BALANCE_BF_HCE = prv_bal_out_hce,
BALANCE_OUT = l_bal_out,
BALANCE_OUT_HCE = l_bal_out_hce,
ACCUM_INTEREST_BF = l_accum_int_bf,
ACCUM_INTEREST_BF_HCE = l_accum_int_bf_hce,
INTEREST = l_interest,
INTEREST_HCE = l_interest_hce,
NO_OF_DAYS = l_days,
ACCRUAL_INTEREST = l_accrual_int,
DAY_COUNT_TYPE = nvl(p_day_count_type,l_day_count_type),
ROUNDING_TYPE = nvl(p_rounding_type,l_rounding_type),
ORIGINAL_AMOUNT = l_accum_int_bf + l_interest
where rowid = l_rowid;
if p_types_update = 'Y' and p_update ='Y' and l_count = 0 then
update xtr_INTERGROUP_TRANSFERS_v
set DAY_COUNT_TYPE = nvl(p_day_count_type,l_day_count_type),
ROUNDING_TYPE = nvl(p_rounding_type,l_rounding_type)
where rowid=l_rowid;
if p_update = 'N' then
if l_bal_out < 0 and p_fund_limit is not null then
open get_type(p_fund_limit);
if p_update = 'Y' then
update xtr_deal_date_amounts_v
set AMOUNT = abs(nvl(l_bal_out,0)),
HCE_AMOUNT = abs(nvl(l_bal_out_hce,0)),
AMOUNT_DATE = nvl(l_date,G_Ig_curr_date),
--LIMIT_CODE = nvl(l_limit_code,'NILL'),
LIMIT_CODE = decode(sign(nvl(l_bal_out,0)),-1,nvl(p_fund_limit,'NILL'),
nvl(p_invest_limit,'NILL')),
LIMIT_PARTY = p_party_code,
PORTFOLIO_CODE = l_portfolio,
PRODUCT_TYPE = l_product,
DEAL_SUBTYPE = decode(sign(nvl(l_bal_out,0)),-1,'FUND','INVEST'),
ACTION_CODE = decode(sign(nvl(l_bal_out,0)),-1,'PAY','REC'),
TRANSACTION_RATE = l_int_rate
where DEAL_TYPE = 'IG'
and DEAL_NUMBER = l_deal_num
and AMOUNT_TYPE = 'BAL'
and CPARTY_CODE = p_party_code
and CURRENCY = p_currency
and COMPANY_CODE = p_company_code;
insert into xtr_DEAL_DATE_AMOUNTS_v (DEAL_TYPE, AMOUNT_TYPE,
DATE_TYPE, DEAL_NUMBER,
TRANSACTION_NUMBER, TRANSACTION_DATE,
AMOUNT_DATE, COMPANY_CODE,
STATUS_CODE, CPARTY_CODE, LIMIT_PARTY,
LIMIT_CODE, PORTFOLIO_CODE,
CURRENCY, TRANSACTION_RATE,
AMOUNT, HCE_AMOUNT,
ACTION_CODE,
DEAL_SUBTYPE,
PRODUCT_TYPE,
DUAL_AUTHORISATION_BY, DUAL_AUTHORISATION_ON)
values ('IG', 'BAL',
'BALANCE', l_deal_num,
l_trans_num, l_date,
l_date, p_company_code,
'CURRENT', p_party_code, p_party_code,
-- nvl(l_limit_code,'NILL'),
decode(sign(nvl(l_bal_out,0)),-1,nvl(p_fund_limit,'NILL'),
nvl(p_invest_limit,'NILL')),
l_portfolio,
p_currency, l_int_rate,
abs(l_bal_out), abs(nvl(L_bal_out_hce,0)),
decode(sign(nvl(l_bal_out,0)),-1,'PAY','REC'),
decode(sign(nvl(l_bal_out,0)),-1,'FUND','INVEST'),
nvl(l_product,'NOT APPLIC'),
G_Ig_user, G_Ig_curr_date);
end if; /* p_update = 'Y' */
select dealer_code
from xtr_dealer_codes_v
where user_id = p_fnd_user;
select 'Y'
from xtr_parties_v
where party_type = 'C'
and party_code = l_comp;
Select sysdate Into G_Ig_SysDate 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(G_Ig_Main_Rec.Deal_number,
G_Ig_Main_Rec.Transaction_number,
G_Ig_user,
v_limit_log_return);
/* Call the insert procedure to insert into xtr_intergroup_transfers */
/*-------------------------------------------------------------------*/
CREATE_IG_DEAL(G_Ig_Main_Rec);
/* Update balance_out of subsequent transactions. */
/*-------------------------------------------------*/
CASCADE_RECALC(G_Ig_Main_Rec.company_code,
G_Ig_Main_Rec.party_code,
G_Ig_Main_Rec.currency,
G_Ig_Main_Rec.transfer_date,
G_Ig_Main_Rec.limit_code,
G_Ig_Main_Rec.limit_code_invest,
'Y',
G_Ig_Main_Rec.Rounding_Type,
G_Ig_Main_Rec.Day_Count_Type);
XTR_LIMITS_P.UPDATE_LIMIT_EXCESS_LOG(G_Ig_Mirror_Rec.Deal_number,
G_Ig_Mirror_Rec.Transaction_number,
G_Ig_user,
v_mirror_limit_log_return);
G_Ig_Mirror_Rec.UPDATED_BY := G_Ig_Main_Rec.UPDATED_BY;
G_Ig_Mirror_Rec.UPDATED_ON := G_Ig_Main_Rec.UPDATED_ON;
G_Ig_Mirror_Rec.PROGRAM_UPDATE_DATE := G_Ig_Main_Rec.PROGRAM_UPDATE_DATE;
update xtr_intergroup_transfers
set mirror_deal_number = G_Ig_Mirror_Rec.deal_number,
mirror_transaction_number = G_Ig_Mirror_Rec.transaction_number
where deal_number = G_Ig_Main_Rec.deal_number
and transaction_number = G_Ig_Main_Rec.transaction_number;
/* Since the insert is done, we can now delete the rows from the interface table. */
/*---------------------------------------------------------------------------------*/
--******************************************************************************************************
-- 3800146 Not necessary for ZBA/CL *****************************************************************
--******************************************************************************************************
if G_Ig_Source is null and nvl(G_Ig_External_Source,'@@@') not in (C_ZBA,C_CL) then
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 = 'DUPLICATE_DEAL_ID',
last_update_date = G_Ig_SysDate,
last_Updated_by = G_Ig_user_id
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_Ig_SysDate,
last_Updated_by = G_Ig_user_id
where external_deal_id = ARec_Interface.external_deal_id
and deal_type = ARec_Interface.deal_type;