[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select *
From Xtr_Deal_Date_Amounts
Where settlement_number in
(Select settlement_number
From Xtr_Settlement_Summary
Where net_ID = p_settlement_summary_ID)
For update of reconciled_reference;
/* After CE is done with Reconciliation this procedure updates the necessary records in XTR tables to denote the completion
of Reconciliation */
If p_task = 'REC' then
/* This is to update Xtr_Settlement_Summary about the successful completion of Reconciliation */
Update xtr_settlement_summary
set status = 'R'
Where settlement_summary_id = p_settlement_summary_ID;
Select settlement_number
Into v_settlement_number
From Xtr_Settlement_Summary
Where settlement_summary_id = p_settlement_summary_ID;
Select Xtr_Deal_Date_Amounts_S.Nextval
Into v_reconciled_reference
From dual;
/* This is to update DDA about the successful completion of Reconciliation */
Update Xtr_Deal_Date_Amounts
Set Reconciled_Reference = v_reconciled_reference,
Reconciled_Pass_Code = p_reconciled_method
Where settlement_number = v_settlement_number;
Update Xtr_Deal_Date_Amounts
Set reconciled_reference = v_reconciled_reference,
reconciled_pass_code = p_reconciled_method
Where current of C1;
/* After successful Un-Reconciliation, Xtr_Settlement_Summary is updated for availability of record for future reconciliation */
Update xtr_settlement_summary
set status = 'A'
Where settlement_summary_id = p_settlement_summary_ID;
Select settlement_number
Into v_settlement_number
From Xtr_Settlement_Summary
Where settlement_summary_id = p_settlement_summary_ID;
Update Xtr_Deal_Date_Amounts
Set Reconciled_Reference = null,
Reconciled_Pass_Code = null
Where settlement_number = v_settlement_number;
/* After successful un-reconciliation, DDA is updated such that the records are available for future reconciliation */
Update Xtr_Deal_Date_Amounts
Set reconciled_reference = null,
reconciled_pass_code = null
Where current of C1;
select xp.PARTY_CODE COMPANY_CODE
from XTR_PARTY_INFO xp -- BUG 2811315
where xp.legal_entity_id = P_ORG_ID -- bug 3862743
and xp.authorised = 'Y';
select xp.PARTY_CODE COMPANY_CODE
from XTR_PARTY_INFO xp
where xp.legal_entity_id = P_ORG_ID; -- bug 3862743
select ce_bank_account_id from
xtr_bank_accounts
where ce_bank_account_id = P_ce_bank_account_id
and ce_bank_account_id is not null;
select ce_bank_account_id from
xtr_bank_accounts b,
xtr_party_info p
where b.ce_bank_account_id is not null
and ce_bank_account_id = P_ce_bank_account_id
and p.party_code = b.party_code
and p.party_type = b.party_type
and p.legal_entity_id = P_ORG_ID -- bug 3862743
and currency = P_CURRENCY_CODE
and b.authorised = 'Y';
select ce_bank_account_id,account_number from
xtr_bank_accounts b,
xtr_party_info p
where b.ce_bank_account_id is not null
and ce_bank_account_id = P_ce_bank_account_id
and p.party_code = b.party_code
and p.party_type = b.party_type
and p.legal_entity_id = P_ORG_ID -- bug 3862743
and currency = P_CURRENCY_CODE;
select ROUNDING_FACTOR,YEAR_BASIS,HCE_RATE
from XTR_MASTER_CURRENCIES_V
where CURRENCY = l_ccy;
select account_number from
xtr_bank_accounts b,
xtr_party_info p
where b.ce_bank_account_id is not null
and ce_bank_account_id = P_ce_bank_account_id
and p.party_code = b.party_code
and p.party_type = b.party_type
and p.legal_entity_id = P_ORG_ID -- bug 3862743
and currency = P_CURRENCY_CODE
and b.authorised = 'Y';
select
PARTY_CODE,CURRENCY,SETOFF,PORTFOLIO_CODE,BANK_CODE,nvl(YEAR_CALC_TYPE,'ACTUAL/ACTUAL')
YEAR_CALC_TYPE
,rounding_type, day_count_type -- Added for Interest Override
from XTR_BANK_ACCOUNTS
where ACCOUNT_NUMBER = acct_no
and PARTY_CODE = new_company;
select
a.BALANCE_DATE,a.BALANCE_CFLOW,a.ACCUM_INT_CFWD,a.INTEREST_RATE,A.ACCRUAL_INTEREST,
a.rounding_type, a.day_count_type -- Added for Interest Override
from XTR_BANK_BALANCES a
where a.ACCOUNT_NUMBER = acct_no
and a.COMPANY_CODE = new_company
and a.BALANCE_DATE = (select max(b.BALANCE_DATE)
from XTR_BANK_BALANCES b
where b.ACCOUNT_NUMBER = acct_no
and b.COMPANY_CODE = new_company);
select 'x'
from XTR_BANK_BALANCES
where ACCOUNT_NUMBER = acct_no
and COMPANY_CODE = new_company
and TRUNC(BALANCE_DATE) = TRUNC(new_date);
select LIMIT_CODE
from XTR_BANK_BALANCES
where ACCOUNT_NUMBER = acct_no
and COMPANY_CODE = new_company
and BALANCE_DATE < new_date
and ((new_bal_ledger >= 0 and BALANCE_CFLOW >= 0)
or (new_bal_ledger <= 0 and BALANCE_CFLOW <= 0))
order by BALANCE_DATE;
select cl.LIMIT_CODE
from XTR_COUNTERPARTY_LIMITS cl, XTR_LIMIT_TYPES lt
where cl.COMPANY_CODE = new_company
and cl.CPARTY_CODE = l_bank_code
and cl.LIMIT_TYPE = lt.LIMIT_TYPE
and ((new_bal_ledger >= 0 and lt.FX_INVEST_FUND_TYPE = 'I')
or (new_bal_ledger <= 0 and lt.FX_INVEST_FUND_TYPE = 'OD'));
select CROSS_REF_TO_OTHER_PARTY
from XTR_PARTIES_V
where PARTY_CODE = l_comp;
select xp.PARTY_CODE COMPANY_CODE
from XTR_PARTY_INFO xp
where xp.legal_entity_id = P_ORG_ID; -- bug 3862743
SELECT MIN(a.balance_date)
FROM xtr_bank_balances a
WHERE a.account_number = acct_no
AND a.COMPANY_CODE = new_company;
SELECT a.day_count_type
FROM xtr_bank_balances a
WHERE a.account_number = acct_no
AND a.COMPANY_CODE = new_company
AND a.balance_date = (select max(b.BALANCE_DATE)
from XTR_BANK_BALANCES b
where b.ACCOUNT_NUMBER = acct_no
and b.COMPANY_CODE = new_company
AND b.balance_date < l_prv_date);
bank_balance_validate(new_company,acct_no,p_currency_code,new_date,'REVAL','INSERT',p_result,p_error_msg);
bank_balance_validate(new_company,acct_no,p_currency_code,new_date,'ACCRUAL','INSERT',p_result,p_error_msg);
insert into XTR_BANK_BALANCES
(company_code,account_number,balance_date,no_of_days,
statement_balance,balance_adjustment,balance_cflow,
accum_int_bfwd,interest,interest_rate,interest_settled,
interest_settled_hce,accum_int_cfwd,setoff,limit_code,
created_on,created_by,accrual_interest,
original_amount, rounding_type, day_count_type, -- Added for Interest Override
one_day_float, two_day_float)
values
(l_comp,acct_no,new_date,l_no_days,
new_bal_ledger,new_bal_intcalc,new_bal_cashflow,
l_int_bf,l_interest,l_new_rate,0,
0,l_int_cf,l_setoff,l_limit_code,
sysdate, fnd_global.user_id,l_accrual_int,
l_original_amount, l_rounding_type, l_day_count_type, -- Added for Interest Override
P_ONE_DAY_FLOAT, P_TWO_DAY_FLOAT);
insert into XTR_BANK_BALANCES
(company_code,account_number,balance_date,no_of_days,
statement_balance,balance_adjustment,balance_cflow,
accum_int_bfwd,interest,interest_rate,interest_settled,
interest_settled_hce,accum_int_cfwd,setoff,limit_code,
created_on,created_by,accrual_interest,
original_amount, rounding_type, day_count_type, -- Added for Interest Override
one_day_float, two_day_float)
values
(l_comp,acct_no,new_date,l_no_days,
new_bal_ledger,new_bal_intcalc,new_bal_cashflow,
l_int_bf,l_interest,l_new_rate,0,
0,l_int_cf,l_setoff,l_limit_code,
sysdate, fnd_global.user_id,l_accrual_int,
l_original_amount, l_rounding_type, l_day_count_type, -- Added for Interest Override
P_ONE_DAY_FLOAT, P_TWO_DAY_FLOAT);
bank_balance_validate(new_company,acct_no,p_currency_code,new_date,'REVAL','UPDATE',P_RESULT,P_ERROR_MSG);
bank_balance_validate(new_company,acct_no,p_currency_code,new_date,'ACCRUAL','UPDATE',P_RESULT,P_ERROR_MSG);
update XTR_BANK_BALANCES
set statement_balance=new_bal_ledger,
balance_adjustment=new_bal_intcalc,
balance_cflow=new_bal_cashflow,
accum_int_bfwd=l_int_bf,
interest=l_interest,
interest_rate=l_new_rate,
interest_settled=0,
interest_settled_hce=0,
accum_int_cfwd=l_int_cf,
setoff=l_setoff,
limit_code=l_limit_code,
updated_on=sysdate,
updated_by=fnd_global.user_id,
accrual_interest=l_accrual_int,
original_amount = l_original_amount, -- Added for Interest Override
rounding_type = l_rounding_type,
day_count_type = l_day_count_type,
one_day_float = P_ONE_DAY_FLOAT,
two_day_float = P_TWO_DAY_FLOAT
where ACCOUNT_NUMBER = acct_no
and COMPANY_CODE = new_company
and TRUNC(BALANCE_DATE) = TRUNC(new_date);
update XTR_BANK_BALANCES
set statement_balance=new_bal_ledger,
balance_adjustment=new_bal_intcalc,
balance_cflow=new_bal_cashflow,
accum_int_bfwd=l_int_bf,
interest=l_interest,
interest_rate=l_new_rate,
interest_settled=0,
interest_settled_hce=0,
accum_int_cfwd=l_int_cf,
setoff=l_setoff,
limit_code=l_limit_code,
updated_on=sysdate,
updated_by=fnd_global.user_id,
accrual_interest=l_accrual_int,
original_amount = l_original_amount, -- Added for Interest Override
rounding_type = l_rounding_type,
day_count_type = l_day_count_type,
one_day_float = P_ONE_DAY_FLOAT,
two_day_float = P_TWO_DAY_FLOAT
where ACCOUNT_NUMBER = acct_no
and COMPANY_CODE = new_company
and TRUNC(BALANCE_DATE) = TRUNC(new_date);
XTR_ACCOUNT_BAL_MAINT_P.UPDATE_BANK_ACCTS(acct_no,
l_ccy,
l_bank_code,
l_portfolio_code,
v_cross_ref,
l_comp,
new_date,
v_dummy_num,-- for bug 6247219
l_setoff);-- for bug 6247219
SELECT max(period_end)
FROM
xtr_batches b,xtr_batch_events e
WHERE
b.company_code = p_company_code
AND b.batch_id = e.batch_id
AND e.event_code = p_event_code;
SELECT max(period_to)
FROM
xtr_bank_balances bb,xtr_bank_accounts ba,
xtr_deal_date_amounts dd,xtr_revaluation_details rd
WHERE
bb.company_code = p_company_code
AND bb.account_number = p_account_number
AND bb.company_code = ba.party_code
AND bb.account_number = ba.account_number
AND ba.currency = p_currency_code
AND bb.company_code = dd.company_code
AND bb.account_number = dd.account_no
AND ba.currency = dd.currency
AND dd.deal_number = rd.deal_no;
SELECT max(period_to)
FROM
xtr_bank_balances bb,xtr_bank_accounts ba,
xtr_deal_date_amounts dd,xtr_accrls_amort aa
WHERE
bb.company_code = p_company_code
AND bb.account_number = p_account_number
AND bb.company_code = ba.party_code
AND bb.account_number = ba.account_number
AND ba.currency = p_currency_code
AND bb.company_code = dd.company_code
AND bb.account_number = dd.account_no
AND ba.currency = dd.currency
AND dd.deal_number = aa.deal_no;
If P_ACTION = 'UPDATE' then
If l_rdate is NOT NULL and l_rdate > p_balance_date then
P_RESULT := 'XTR3_BU_VAL_ERROR';
Elsif P_ACTION = 'INSERT' then
If l_rdate is NULL then
Open cur_reval_comp;
select 1
from xtr_bank_accounts
where ce_bank_account_id = P_ce_bank_account_id
and ce_bank_account_id is not null;
select xp.PARTY_CODE COMPANY_CODE
from XTR_PARTY_INFO xp
where xp.legal_entity_id = P_ORG_ID; -- bug 3862743
select 1
from XTR_PARTIES_V
where party_code = l_company;
select b.authorised, -- (5) To check for Authorised Account
b.account_number
from xtr_bank_accounts b,
xtr_party_info p
where b.ce_bank_account_id = P_ce_bank_account_id
and b.ce_bank_account_id is not null
and b.currency = P_CURRENCY
and p.party_code = b.party_code
and p.party_type = b.party_type
and p.legal_entity_id = P_ORG_ID; -- bug 3862743
select authorised,
party_code,
ce_bank_account_id
from xtr_bank_accounts
where account_number = P_ACCOUNT_NUMBER
and currency = P_CURRENCY
order by authorised desc; -- so 'Y' comes before 'N'
select INTEREST_RATE
from xtr_INTEREST_RATE_RANGES_v
where PARTY_CODE = p_party_code
and CURRENCY = nvl(p_currency,CURRENCY)
and REF_CODE = p_ref_code -- 'IG_PRO1075'
and MIN_AMT < to_number(p_balance_out)
and MAX_AMT >= to_number(p_balance_out)
and EFFECTIVE_FROM_DATE = (select max(EFFECTIVE_FROM_DATE)
from xtr_INTEREST_RATE_RANGES_v
where PARTY_CODE = p_PARTY_CODE
and CURRENCY = nvl(p_CURRENCY,CURRENCY)
and REF_CODE = p_ref_code -- 'IG_PRO1075'
and MIN_AMT < to_number(p_BALANCE_OUT)
and MAX_AMT >= to_number(p_BALANCE_OUT)
and EFFECTIVE_FROM_DATE <= p_TRANSFER_DATE );
select INTEREST_RATE
from xtr_INTEREST_RATE_RANGES_v
where PARTY_CODE = p_COMPANY_CODE
and CURRENCY = nvl(p_CURRENCY,CURRENCY)
and REF_CODE = p_ref_code -- 'IG_PRO1075'
and MIN_AMT < p_BALANCE_OUT
and MAX_AMT >= p_BALANCE_OUT
and EFFECTIVE_FROM_DATE = (select max(EFFECTIVE_FROM_DATE)
from xtr_INTEREST_RATE_RANGES_v
where EFFECTIVE_FROM_DATE <= p_TRANSFER_DATE
and PARTY_CODE = p_COMPANY_CODE
and CURRENCY = nvl(p_CURRENCY,CURRENCY)
and REF_CODE = p_ref_code -- 'IG_PRO1075'
and MIN_AMT < p_BALANCE_OUT
and MAX_AMT >= p_BALANCE_OUT);
select nvl(INTEREST_RATE,0)
from xtr_INTEREST_RATE_RANGES_v
where PARTY_CODE = p_party
and CURRENCY = nvl(p_CURRENCY,CURRENCY)
and REF_CODE = p_ref_code -- 'IG_PRO1075'
and MAX_AMT >= p_BALANCE_OUT
and MIN_AMT <= 0
and MIN_AMT < 0
and EFFECTIVE_FROM_DATE = (select max(EFFECTIVE_FROM_DATE)
from xtr_INTEREST_RATE_RANGES_v
where EFFECTIVE_FROM_DATE <= p_TRANSFER_DATE
and PARTY_CODE = p_party
and CURRENCY = nvl(p_CURRENCY,CURRENCY)
and REF_CODE = p_ref_code -- 'IG_PRO1075'
and MAX_AMT >= p_BALANCE_OUT
and MIN_AMT <= 0
and MIN_AMT < 0 )
order by MAX_AMT asc;
select nvl(INTEREST_RATE,0)
from xtr_INTEREST_RATE_RANGES_v
where PARTY_CODE = p_party
and CURRENCY = nvl(p_CURRENCY,CURRENCY)
and REF_CODE = p_ref_code -- 'IG_PRO1075'
and MIN_AMT <= p_BALANCE_OUT
and MAX_AMT >= 0
and MIN_AMT >= 0
and EFFECTIVE_FROM_DATE = (select max(EFFECTIVE_FROM_DATE)
from xtr_INTEREST_RATE_RANGES_v
where EFFECTIVE_FROM_DATE <= p_TRANSFER_DATE
and PARTY_CODE = p_party
and CURRENCY = nvl(p_CURRENCY,CURRENCY)
and REF_CODE = p_ref_code -- 'IG_PRO1075'
and MIN_AMT <= p_BALANCE_OUT
and MAX_AMT >= 0
and MIN_AMT >= 0)
order by MIN_AMT desc;
select INTEREST_RATE,
ROUNDING_TYPE,
DAY_COUNT_TYPE,
PRICING_MODEL,
BALANCE_OUT -- not BALANCE_BF
,deal_number, transaction_number -- not used
from xtr_intergroup_transfers_v
where company_code = p_company_code
and party_code = p_party_code
and currency = p_currency
and transfer_date <= p_transfer_date
order by TRANSFER_DATE desc, TRANSACTION_NUMBER desc;
select deal_number,
transaction_number
from xtr_intergroup_transfers
where company_code = l_company_code
and party_code = l_party_code
and currency = l_currency
and principal_adjust = l_principal_adjust
and transfer_date = l_transfer_date
and principal_action = l_principal_action
and portfolio = l_portfolio
and product_type = l_product_type
and company_account_no = l_company_account_no
and party_account_no = l_party_account_no
and external_source = 'ZBA'
order by transfer_date desc, transaction_number desc;
select 1
from xtr_deal_date_amounts
where deal_number = l_deal_num
and transaction_number = l_tran_num
and amount_type = l_amt_type
and nvl(cashflow_amount,0) <> 0
and reconciled_reference is null;
select A.transaction_number
from xtr_interacct_transfers A,
xtr_deal_date_amounts B,
xtr_deal_date_amounts C
where A.company_code = l_company_code
and A.transfer_amount = l_transfer_amount
and A.transfer_date = l_transfer_date
and A.portfolio_code = l_portfolio
and A.product_type = l_product_type
and A.account_no_from = l_from_account_no
and A.account_no_to = l_to_account_no
and A.external_source = 'ZBA'
and B.deal_number = 0
and B.transaction_number = A.transaction_number
and B.deal_type = l_deal_type
and C.deal_number = 0
and C.transaction_number = A.transaction_number
and C.deal_type = l_deal_type
and (B.reconciled_reference is null or C.reconciled_reference is null);
select 1
from XTR_PARTY_INFO
where party_code = l_comp
and party_type = 'C';
select PARTY_CODE
from ce_cashpools
where cashpool_id = l_cashpool_id
and currency_code = l_ccy;
select PARTY_CODE
from ce_cashpool_sub_accts
where cashpool_id = l_cashpool_id
and account_id = l_party_bank_id;
select rounding_type, day_count_type, pricing_model,
product_type, portfolio, fund_limit_code,
invest_limit_code, party_pricing_model, party_product_type,
party_portfolio, party_fund_limit_code, party_invest_limit_code
from xtr_cashpool_attributes
where cashpool_id = l_cashpool_id
and party_code = l_intercomp_code;
select account_number
from xtr_bank_accounts
where party_code = l_party
and currency = l_ccy
and ce_bank_account_id = l_bank_id;
select dealer_code
from xtr_dealer_codes_v
where user_id = p_fnd_user;
select party_code,
currency_code
from ce_cashpools
where cashpool_id = l_cashpool_id;
select iac_product_type,
iac_portfolio
from xtr_cashpool_attributes
where cashpool_id = l_cashpool_id
and product_type is null; -- Condition added Bug 4309871
select account_number
from xtr_bank_accounts
where party_code = l_party
and currency = l_ccy
and ce_bank_account_id = l_bank_id;
select param_value
from xtr_pro_param
where param_name = l_name;
SELECT VALIDATION_AUTHORIZED
FROM XTR_AUTH_TYPE_SUBTYPE_PROD_V
WHERE USER_NAME = l_DEALER
AND DEAL_TYPE = 'IAC'
AND DEAL_SUBTYPE = 'FIRM'
AND PRODUCT_TYPE = l_PRODUCT;
select dealer_code
from xtr_dealer_codes_v
where user_id = p_fnd_user;