[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
v_select varchar2(1000);
v_old_updated_on date;
v_old_updated_by varchar2(30);
v_new_updated_on date;
v_new_updated_by varchar2(30);
select table_name,'XTR_A_'||substr(table_name,5)
from XTR_SETUP_AUDIT_REQMTS
where event = pc_event;
select table_column, column_title, upper(nvl(p_key_yn,'N')), upper(column_type)
from XTR_AUDIT_COLUMNS
where event = pc_event
and ( nvl(audit_yn, 'N') = 'Y' or
nvl(P_KEY_YN, 'N') = 'Y') ;
v_select := 'nvl(UPDATED_ON,to_date(''01/01/1900'',''DD/MM/YYYY'')),UPDATED_BY';
v_select := v_select || ', to_char('||v_table_column||',''DD/MM/YYYY HH24:MI:SS'')';
v_select := v_select || ',' || v_table_column;
v_select := v_select || ',to_char(' || v_table_column || ')';
v_sql := 'select ''B'',' || v_select || ' FROM '||v_table_name||' '||
'WHERE (updated_on between to_date('''|| p_date_from ||''',''DD/MM/YYYY HH24:MI:SS'') '||
'and to_date('''|| p_date_to ||''',''DD/MM/YYYY HH24:MI:SS''))';
v_sql := v_sql || ' union ' || 'select ''A'',' || v_select ||' from '||v_audit_table_name||' '||
'WHERE (updated_on between to_date('''|| p_date_from ||''',''DD/MM/YYYY HH24:MI:SS'') '||
'and to_date('''|| p_date_to ||''',''DD/MM/YYYY HH24:MI:SS'')) OR '||
'(audit_date_stored between to_date('''|| p_date_from ||''',''DD/MM/YYYY HH24:MI:SS'') '||
'and to_date('''|| p_date_to ||''',''DD/MM/YYYY HH24:MI:SS'')) '||'order by ';
v_sql := v_sql||'1,2,3'; -- Add B/A, "updated_on, updated_by" to SORT BY clause
dbms_sql.define_column(v_cursor,2,v_new_updated_on);
dbms_sql.define_column(v_cursor,3,v_new_updated_by,30);
dbms_sql.column_value(v_cursor,2,v_new_updated_on);
dbms_sql.column_value(v_cursor,3,v_new_updated_by);
insert into XTR_AUDIT_SUMMARY
(AUDIT_REQUESTED_BY,AUDIT_REQUESTED_ON,
AUDIT_RECORDS_FROM,AUDIT_RECORDS_TO,
NAME_OF_COLUMN_CHANGED,TABLE_NAME,
REFERENCE_CODE,UPDATED_ON_DATE,
UPDATED_BY_USER,OLD_VALUE,NEW_VALUE,TRANSACTION_REF,
NON_TRANSACTION_REF)
values
(p_audit_requested_by,sysdate,
to_date(p_date_from,'DD/MM/YYYY HH24:MI:SS'),
to_date(p_date_to,'DD/MM/YYYY HH24:MI:SS'),
rtrim(v_col_title(v_counter)),upper(v_table_name),
rtrim(substr(v_reference_code,1,20)),
to_char(v_new_updated_on,'DD/MM/YYYY HH24:MI:SS'),
substr(v_new_updated_by,1,10),rtrim(substr(v_old(v_counter),1,255)),
rtrim(substr(v_new(v_counter),1,255)),null,v_new_letter);
v_old_updated_on := v_new_updated_on;
v_old_updated_by := v_new_updated_by;
select tl.MODULE_NAME, tl.CANVAS_TYPE, tl.ITEM_NAME
from XTR_SYS_LANGUAGES_TL tl , xtr_sys_languages sl
where tl.MODULE_NAME like v_module_name
and tl.CANVAS_TYPE = v_canvas_type
and tl.LANGUAGE = v_language
and tl.TEXT = v_text
and tl.canvas_type = sl.canvas_type
and tl.module_name = sl.module_name
and sl.ORIGINAL_TEXT = v_original_text
and tl.item_name =sl.item_name;
/* select tl.MODULE_NAME, tl.CANVAS_TYPE, tl.ITEM_NAME
from XTR_SYS_LANGUAGES_TL tl
where tl.MODULE_NAME like v_module_name
and tl.CANVAS_TYPE = v_canvas_type
and tl.LANGUAGE = v_language
and tl.TEXT = v_text; */
XTR_SYS_LANGUAGES_PKG.Update_Row
(X_MODULE_NAME => v_module_name,
X_CANVAS_TYPE => v_canvas_type,
X_ITEM_NAME => v_item_name,
X_ORIGINAL_TEXT => null,
X_TEXT => l_new_val,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID);
XTR_SYS_LANGUAGES_PKG.Insert_Row
(X_ROWID => l_rowid,
X_MODULE_NAME => l_form,
X_CANVAS_TYPE => 'TEXT',
X_ITEM_NAME => l_item,
X_ORIGINAL_TEXT => l_orginal_text,
X_TEXT => l_new_val,
X_CREATION_DATE => sysdate,
X_CREATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID);
/* deleted Treasury_Archive to fix bug # 1855372
*/
PROCEDURE DEAL_ACTIONS(p_deal_type IN VARCHAR2,
p_deal_number IN NUMBER,
p_transaction_number IN NUMBER,
p_action_type IN VARCHAR2,
p_cparty_code IN VARCHAR2,
p_client_code IN VARCHAR2,
p_date_created IN DATE,
p_company_code IN VARCHAR2,
p_status_code IN VARCHAR2,
p_file_name IN VARCHAR2,
p_deal_subtype IN VARCHAR2,
p_currency IN VARCHAR2,
p_cparty_advice IN VARCHAR2,
p_client_advice IN VARCHAR2,
p_amount IN NUMBER,
p_org_flag IN VARCHAR2) is
--
-- Purpose: Populate CONFIRMATION_DETAILS table with action codes
-- (used in Forms PRO1011 - Confirmations and PRO1012 - Dual Validation).
--
cursor CHK_CONFO_REQD(l_party_code varchar2) is
select 1
from XTR_PARTIES_V a,
XTR_CONFIRMATION_ACTIONS b
where a.party_code= l_party_code
and a.confo_group_code = b. confo_action_group
and b.action_type = p_action_type
and b.confo_reqd = 'Y';
select nvl(to_number(PARAM_VALUE),0)
from XTR_PRO_PARAM
where upper(PARAM_NAME) = 'VALIDATE ABOVE AMNT';
select p_amount / hce_rate
from XTR_MASTER_CURRENCIES
where CURRENCY = p_currency;
select dual_authorisation_by, dual_authorisation_on
from xtr_deal_date_amounts
where transaction_number=p_transaction_number
and deal_number = 0 -- bug 4957910
and dual_authorisation_by is not null
and deal_type='IAC';
insert into xtr_confirmation_details(
ACTION_TYPE,COMPANY_CODE,CONFO_PARTY_CODE,CONFO_VALIDATION_BOTH,
CPARTY_CODE,DATE_ACTION_INITIATED,DEAL_NO,STATUS_CODE,TRANSACTION_NO,AMOUNT,AMOUNT_TYPE,CLIENT_CODE,
CURRENCY,DEAL_SUBTYPE,DEAL_TYPE,CONFIRMATION_VALIDATED_BY,CONFIRMATION_VALIDATED_ON)
values(p_action_type,p_company_code,NULL,'V',p_cparty_code,trunc(p_date_created),
p_deal_number,p_status_code,p_transaction_number,p_amount,NULL,p_client_code,
p_currency,p_deal_subtype,p_deal_type,confirmed_by,confirmed_on);
insert into xtr_confirmation_details(
ACTION_TYPE,COMPANY_CODE,CONFO_PARTY_CODE,CONFO_VALIDATION_BOTH,
CPARTY_CODE,DATE_ACTION_INITIATED,DEAL_NO,STATUS_CODE,TRANSACTION_NO,
AMOUNT,AMOUNT_TYPE,CLIENT_CODE,CURRENCY,DEAL_SUBTYPE,DEAL_TYPE,
CONFIRMATION_VALIDATED_BY,CONFIRMATION_VALIDATED_ON)
values( p_action_type,p_company_code, NULL,'V',p_cparty_code,trunc(p_date_created),
p_deal_number,p_status_code, p_transaction_number, p_amount,NULL,p_client_code,
p_currency,p_deal_subtype,p_deal_type,confirmed_by,confirmed_on);
insert into xtr_confirmation_details(
ACTION_TYPE,COMPANY_CODE,CONFO_PARTY_CODE,CONFO_VALIDATION_BOTH,
CPARTY_CODE,DATE_ACTION_INITIATED,DEAL_NO,STATUS_CODE,TRANSACTION_NO,AMOUNT,
AMOUNT_TYPE,CLIENT_CODE,CURRENCY,DEAL_SUBTYPE,DEAL_TYPE,
CONFIRMATION_VALIDATED_BY,CONFIRMATION_VALIDATED_ON)
values(p_action_type,p_company_code,p_cparty_code,'B',p_cparty_code,trunc(p_date_created),
p_deal_number,p_status_code,p_transaction_number,p_amount,NULL,p_client_code,
p_currency,p_deal_subtype,p_deal_type,confirmed_by,confirmed_on);
insert into xtr_confirmation_details(
ACTION_TYPE,COMPANY_CODE,CONFO_PARTY_CODE,CONFO_VALIDATION_BOTH,
CPARTY_CODE,DATE_ACTION_INITIATED,DEAL_NO,STATUS_CODE,TRANSACTION_NO,AMOUNT,
AMOUNT_TYPE,CLIENT_CODE,CURRENCY,DEAL_SUBTYPE,DEAL_TYPE,
CONFIRMATION_VALIDATED_BY,CONFIRMATION_VALIDATED_ON)
values(p_action_type,p_company_code, NULL,'V',p_cparty_code,trunc(p_date_created),
p_deal_number,p_status_code,p_transaction_number,p_amount,NULL,p_client_code,
p_currency,p_deal_subtype,p_deal_type,confirmed_by,confirmed_on);
insert into xtr_confirmation_details(
ACTION_TYPE,COMPANY_CODE,CONFO_PARTY_CODE,CONFO_VALIDATION_BOTH,
CPARTY_CODE,DATE_ACTION_INITIATED,DEAL_NO,STATUS_CODE,TRANSACTION_NO,AMOUNT,
AMOUNT_TYPE,CLIENT_CODE,CURRENCY,DEAL_SUBTYPE,DEAL_TYPE,
CONFIRMATION_VALIDATED_BY,CONFIRMATION_VALIDATED_ON)
values(p_action_type,p_company_code,p_client_code,'B',p_cparty_code,trunc(p_date_created),
p_deal_number,p_status_code,p_transaction_number,p_amount,NULL,p_client_code,
p_currency,p_deal_subtype,p_deal_type,confirmed_by,confirmed_on);
insert into xtr_confirmation_details(
ACTION_TYPE,COMPANY_CODE,CONFO_PARTY_CODE,CONFO_VALIDATION_BOTH,
CPARTY_CODE,DATE_ACTION_INITIATED,DEAL_NO,STATUS_CODE,TRANSACTION_NO,AMOUNT,
AMOUNT_TYPE,CLIENT_CODE,CURRENCY,DEAL_SUBTYPE,DEAL_TYPE,
CONFIRMATION_VALIDATED_BY,CONFIRMATION_VALIDATED_ON)
values(p_action_type,p_company_code,NULL,'V',p_cparty_code,trunc(p_date_created),
p_deal_number,p_status_code,p_transaction_number,p_amount,NULL,p_client_code,
p_currency,p_deal_subtype,p_deal_type,confirmed_by,confirmed_on);
insert into xtr_confirmation_details(
ACTION_TYPE,COMPANY_CODE,CONFO_PARTY_CODE,CONFO_VALIDATION_BOTH,
CPARTY_CODE,DATE_ACTION_INITIATED,DEAL_NO,STATUS_CODE,TRANSACTION_NO,AMOUNT,
AMOUNT_TYPE,CLIENT_CODE,CURRENCY,DEAL_SUBTYPE,DEAL_TYPE,
CONFIRMATION_VALIDATED_BY,CONFIRMATION_VALIDATED_ON)
values(p_action_type,p_company_code,p_cparty_code,'B',p_cparty_code,trunc(p_date_created),
p_deal_number,p_status_code,p_transaction_number,p_amount,NULL,p_client_code,
p_currency,p_deal_subtype,p_deal_type,confirmed_by,confirmed_on);
insert into xtr_confirmation_details(
ACTION_TYPE,COMPANY_CODE,CONFO_PARTY_CODE,CONFO_VALIDATION_BOTH,
CPARTY_CODE,DATE_ACTION_INITIATED,DEAL_NO,STATUS_CODE,TRANSACTION_NO,AMOUNT,
AMOUNT_TYPE,CLIENT_CODE,CURRENCY,DEAL_SUBTYPE,DEAL_TYPE)
values(p_action_type,p_company_code,p_client_code,'C',p_cparty_code,trunc(p_date_created),
p_deal_number,p_status_code,p_transaction_number,p_amount,NULL,p_client_code,
p_currency,p_deal_subtype,p_deal_type);
select a.company_code,a.portfolio_code,a.deal_number,a.transaction_number,a.deal_type,a.deal_subtype,a.currency,a.transaction_rate,a.amount_date start_date,a.amount_date maturity_date,p_to_date to_date,p_from_date from_date,
'FLOAT' FIXED_OR_FLOAT,nvl(a.amount,0) gross_amount,1 no_of_days
from XTR_DEAL_DATE_AMOUNTS_V a
where a.amount_date >=p_from_date
and a.company_code like p_company_code and a.currency like p_currency
and a.portfolio_code like p_portfolio_code
and a.status_code='CURRENT'
and ((a.DEAL_TYPE ='CA' and a.AMOUNT_TYPE='BAL')
or (a.DEAL_TYPE ='IG' and a.AMOUNT_TYPE='BAL' and p_inc_ig='Y'))
and ( (a.DEAL_SUBTYPE='INVEST' and p_fund_invest='INVEST')
or (a.DEAL_SUBTYPE='FUND' and p_fund_invest='FUND')
or (p_fund_invest='NONE'));
select a.company_code,a.portfolio_code,a.deal_number,a.transaction_number,a.deal_type,
a.deal_subtype,a.currency,a.transaction_rate,
decode(a.deal_type,'IRO',nvl(b.start_date,b.expiry_date),b.start_date) start_date,
b.maturity_date,p_to_date to_date,p_from_date from_date,
decode(a.deal_type||a.deal_subtype,
'IROBCAP','FIXED',
'IROBFLOOR','FIXED',
'IROSFLOOR','FLOAT',
'IROSCAP','FLOAT',
'BDOBCAP','FIXED',
'BDOBFLOOR','FIXED',
'BDOSFLOOR','FLOAT',
'BDOSCAP','FLOAT',
'SWPTNSELL','FLOAT',
'SWPTNBUY','FIXED',
'FRAFUND','FIXED',
'FRAINVEST','FIXED',
decode(sign(p_to_date-b.maturity_date+1),1,'FLOAT','FIXED')) FIXED_OR_FLOAT,
decode(a.deal_type,
'IRO',nvl(b.maturity_date-nvl(b.start_date,b.expiry_date)+decode(sign(nvl(b.start_date,b.expiry_date)-p_from_date),-1,nvl(b.start_date,b.expiry_date)-p_from_date,0),0),
'BDO',nvl(b.maturity_date-nvl(b.start_date,b.expiry_date)+decode(sign(nvl(b.start_date,b.expiry_date)-p_from_date),-1,nvl(b.start_date,b.expiry_date)-p_from_date,0),0),
nvl(b.maturity_date-b.start_date+decode(sign(b.start_date-p_from_date),-1,b.start_date-p_from_date,0),0)) no_of_days,
nvl(sum(decode(a.deal_type||a.amount_type,
'NIBAL_FV',a.amount,
'BONDINTL_FV',a.amount,
'IROFACEVAL',a.amount,
'BDOFACEVAL',a.amount,
'SWPTNFACEVAL',a.amount,
'FRAFACEVAL',a.amount,0)),0) gross_amount
from XTR_DEAL_DATE_AMOUNTS_V a,
XTR_DEALS_V b
where a.amount_date >= p_from_date
and a.currency like p_currency
and a.company_code like p_company_code
and a.portfolio_code like p_portfolio_code
and a.status_code='CURRENT'
and a.deal_number = b.deal_no
and a.DEAL_TYPE in('NI','SWPTN','FRA','IRO','BOND','BDO')
and ((a.DEAL_TYPE in('FRA') AND a.DEAL_SUBTYPE = 'INVEST' and p_fund_invest='INVEST')
or (a.DEAL_TYPE in('FRA') AND a.DEAL_SUBTYPE = 'FUND' and p_fund_invest='FUND')
or (p_fund_invest = 'NONE')
or (a.DEAL_TYPE = 'NI' AND a.DEAL_SUBTYPE in('BUY','COVER') and p_fund_invest='INVEST')
or (a.DEAL_TYPE = 'NI' AND a.DEAL_SUBTYPE in('SELL','SHORT','ISSUE') and p_fund_invest = 'FUND')
or (a.DEAL_TYPE in('BOND') AND a.DEAL_SUBTYPE='BUY' and p_fund_invest = 'INVEST')
or (a.DEAL_TYPE in('BOND') AND a.DEAL_SUBTYPE='SHORT' and p_fund_invest = 'FUND')
or (a.DEAL_TYPE in('BOND') AND a.DEAL_SUBTYPE='ISSUE' and p_fund_invest = 'FUND')
or (a.DEAL_TYPE in('IRO') AND a.DEAL_SUBTYPE in('BFLOOR','SFLOOR') and p_fund_invest = 'INVEST')
or (a.DEAL_TYPE in('IRO') AND a.DEAL_SUBTYPE in('SCAP','BCAP') and p_fund_invest = 'FUND')
or (a.DEAL_TYPE in('BDO') AND a.DEAL_SUBTYPE in('BFLOOR','SFLOOR') and p_fund_invest = 'INVEST')
or (a.DEAL_TYPE in('BDO') AND a.DEAL_SUBTYPE in('SCAP','BCAP') and p_fund_invest = 'FUND')
or (a.DEAL_TYPE in('SWPTN') and b.COUPON_ACTION='PAY' and p_fund_invest='FUND')
or (a.DEAL_TYPE in('SWPTN') and b.COUPON_ACTION='REC' and p_fund_invest='INVEST'))
having nvl(sum(decode(a.deal_type||a.amount_type,
'NIBAL_FV',a.amount,
'BONDINTL_FV',a.amount,
'IROFACEVAL',a.amount,
'BDOFACEVAL',a.amount,
'SWPTNFACEVAL',a.amount,
'FRAFACEVAL',a.amount,0)),0) >0
group by a.company_code,a.portfolio_code,a.deal_number,a.transaction_number,a.deal_type,
a.deal_subtype,a.currency,a.transaction_rate,
decode(a.deal_type,'IRO',nvl(b.start_date,b.expiry_date),b.start_date),b.maturity_date,p_to_date,p_from_date,
decode(a.deal_type,'BDO',nvl(b.start_date,b.expiry_date),b.start_date),b.maturity_date,p_to_date,p_from_date,
decode(a.deal_type||a.deal_subtype,
'IROBCAP','FIXED',
'IROBFLOOR','FIXED',
'IROSFLOOR','FLOAT',
'IROSCAP','FLOAT',
'BDOBCAP','FIXED',
'BDOBFLOOR','FIXED',
'BDOSFLOOR','FLOAT',
'BDOSCAP','FLOAT',
'SWPTNSELL','FLOAT',
'SWPTNBUY','FIXED',
'FRAFUND','FIXED',
'FRAINVEST','FIXED',
decode(sign(p_to_date-b.maturity_date+1),1,'FLOAT','FIXED')),
decode(a.deal_type,
'IRO',nvl(b.maturity_date-nvl(b.start_date,b.expiry_date)+decode(sign(nvl(b.start_date,b.expiry_date)-p_from_date),-1,nvl(b.start_date,b.expiry_date)-p_from_date,0),0),
'BDO',nvl(b.maturity_date-nvl(b.start_date,b.expiry_date)+decode(sign(nvl(b.start_date,b.expiry_date)-p_from_date),-1,nvl(b.start_date,b.expiry_date)-p_from_date,0),0),
nvl(b.maturity_date-b.start_date+decode(sign(b.start_date-p_from_date),-1,b.start_date-p_from_date,0),0));
select a.company_code,a.portfolio_code,a.deal_number,a.transaction_number,a.deal_type,
a.deal_subtype,a.currency,a.transaction_rate,a.amount_date start_date,a.amount_date maturity_date,p_to_date to_date,p_from_date from_date,
'FIXED' FIXED_OR_FLOAT,nvl(a.amount,0) gross_amount,1 no_of_days
from XTR_DEAL_DATE_AMOUNTS_V a,
XTR_FUTURES b
where a.amount_date >=p_from_date
and a.company_code like p_company_code
and a.currency like p_currency
and a.portfolio_code like p_portfolio_code
and a.amount_type='FACEVAL'
and a.status_code='CURRENT'
and a.deal_type='FUT' and ((a.deal_subtype='BUY' and p_fund_invest='INVEST') or
(a.deal_subtype = 'SELL' and p_fund_invest = 'FUND') or p_fund_invest='NONE')
and a.contract_code = b.contract_code
and b.financial_contract = 'F';
select a.company_code,a.portfolio_code,a.deal_number,a.transaction_number,
a.deal_type,a.deal_subtype,a.currency,a.INTEREST_RATE transaction_rate,
a.start_date,a.maturity_date,p_to_date to_date,p_from_date from_date,
decode(a.deal_type,
'TMM',decode(sign(p_to_date-greatest(nvl(a.maturity_date,p_from_date),
nvl(b.settle_date,b.start_date))+1),1,'FLOAT',
decode(sign(nvl(b.settle_date,b.start_date)-a.maturity_date+1),1,'FIXED','FLOAT')),
'RTMM',decode(sign(p_to_date-greatest(nvl(a.maturity_date,p_from_date),
nvl(b.settle_date,b.start_date))+1),1,'FLOAT',
decode(sign(nvl(b.settle_date,b.start_date)-a.maturity_date+1),1,'FIXED','FLOAT')),
'IRS',decode(sign(p_to_date-nvl(a.maturity_date,p_from_date)+1),1,b.fixed_or_floating_rate,'FLOAT'),
decode(sign(p_to_date-nvl(a.maturity_date,p_from_date)+1),1,'FLOAT','FIXED')) FIXED_OR_FLOAT,
nvl(decode(a.deal_type,
'TMM',decode(decode(sign(p_to_date-greatest(nvl(a.maturity_date,p_from_date),nvl(b.settle_date,b.start_date))+1),1,'FLOAT',decode(sign(nvl(b.settle_date,b.start_date)-a.maturity_date+1),1,'FIXED','FLOAT')),
'FLOAT',
decode(sign(p_to_date-nvl(b.settle_date,b.start_date)+1),1,
nvl(b.maturity_date,p_from_date+1)-a.start_date+decode(sign(a.start_date-p_from_date),-1,a.start_date-p_from_date,0),
nvl(b.maturity_date,p_from_date+1)-a.start_date+decode(sign(a.start_date-p_from_date),-1,a.start_date-p_from_date,0)-
(nvl(b.settle_date,b.start_date)-p_to_date)),
'FIXED',
nvl(b.settle_date,b.start_date)-p_to_date),
'RTMM',decode(decode(sign(p_to_date-greatest(nvl(a.maturity_date,p_from_date),nvl(b.settle_date,b.start_date))+1),1,'FLOAT',decode(sign(nvl(b.settle_date,b.start_date)-a.maturity_date+1),1,'FIXED','FLOAT')),
'FLOAT',
decode(sign(p_to_date-nvl(b.settle_date,b.start_date)+1),1,
nvl(b.maturity_date,p_from_date+1)-a.start_date+decode(sign(a.start_date-p_from_date),-1,a.start_date-p_from_date,0),
nvl(b.maturity_date,p_from_date+1)-a.start_date+decode(sign(a.start_date-p_from_date),-1,a.start_date-p_from_date,0)-
(nvl(b.settle_date,b.start_date)-p_to_date)),
'FIXED',
nvl(b.settle_date,b.start_date)-p_to_date),
'IRS',nvl(b.maturity_date,p_from_date+1)-a.start_date+decode(sign(a.start_date-p_from_date),-1,a.start_date-p_from_date,0),
'DEB',nvl(b.maturity_date,p_from_date+1)-a.start_date+decode(sign(a.start_date-p_from_date),-1,a.start_date-p_from_date,0),
nvl(a.maturity_date,p_from_date+1)-a.start_date+decode(sign(a.start_date-p_from_date),-1,a.start_date-p_from_date,0)),0) no_of_days,
nvl(sum(decode(a.deal_type,
'TMM',decode(nvl(a.balance_out,0),0,a.balance_out_bf,a.balance_out),
'RTMM',decode(nvl(a.balance_out,0),0,a.balance_out_bf,a.balance_out),
'DEB',decode(nvl(a.balance_out,0),0,a.balance_out_bf,a.balance_out),
a.balance_out)),0) gross_amount
from XTR_ROLLOVER_TRANSACTIONS_V a,
XTR_DEALS_V b
where a.company_code like p_company_code and a.currency like p_currency
and a.status_code='CURRENT'
and a.portfolio_code like p_portfolio_code
and nvl(a.maturity_date,p_from_date+1) >p_from_date
and a.start_date <=p_from_date
and a.deal_type in('ONC','CMF','IRS','DEB','FX','TMM','RTMM')
and ((a.deal_type in('ONC','RTMM','TMM','IRS','CMF','FX') and (a.deal_subtype='INVEST' and p_fund_invest='INVEST'))
or (a.deal_type in('ONC','TMM','RTMM','IRS','CMF','FX') and (a.deal_subtype='FUND' and p_fund_invest='FUND'))
or (p_fund_invest='NONE')
or (a.DEAL_TYPE in('DEB') AND a.DEAL_SUBTYPE='BUY' and p_fund_invest='INVEST')
or (a.DEAL_TYPE in('DEB') AND a.DEAL_SUBTYPE='ISSUE' and p_fund_invest='FUND')
)
and a.deal_number=b.deal_no
having nvl(sum(decode(a.deal_type,
'TMM',decode(nvl(a.balance_out,0),0,a.balance_out_bf,a.balance_out),
'RTMM',decode(nvl(a.balance_out,0),0,a.balance_out_bf,a.balance_out),
'DEB',decode(nvl(a.balance_out,0),0,a.balance_out_bf,a.balance_out),
a.balance_out)),0) >0
group by a.company_code,a.portfolio_code,a.deal_number,a.transaction_number,a.deal_type,a.deal_subtype,a.currency,a.INTEREST_RATE,a.start_date,a.maturity_date,p_to_date,p_from_date,
decode(a.deal_type,
'TMM',decode(sign(p_to_date-greatest(nvl(a.maturity_date,p_from_date),nvl(b.settle_date,b.start_date))+1),1,'FLOAT',
decode(sign(nvl(b.settle_date,b.start_date)-a.maturity_date+1),1,'FIXED','FLOAT')),
'RTMM',decode(sign(p_to_date-greatest(nvl(a.maturity_date,p_from_date),nvl(b.settle_date,b.start_date))+1),1,'FLOAT',
decode(sign(nvl(b.settle_date,b.start_date)-a.maturity_date+1),1,'FIXED','FLOAT')),
'IRS',decode(sign(p_to_date-nvl(a.maturity_date,p_from_date)+1),1,b.fixed_or_floating_rate,'FLOAT'),
decode(sign(p_to_date-nvl(a.maturity_date,p_from_date)+1),1,'FLOAT','FIXED')),
nvl(decode(a.deal_type,
'TMM',decode(decode(sign(p_to_date-greatest(nvl(a.maturity_date,p_from_date),nvl(b.settle_date,b.start_date))+1),1,'FLOAT',decode(sign(nvl(b.settle_date,b.start_date)-a.maturity_date+1),1,'FIXED','FLOAT')),
'FLOAT',
decode(sign(p_to_date-nvl(b.settle_date,b.start_date)+1),1,
nvl(b.maturity_date,p_from_date+1)-a.start_date+decode(sign(a.start_date-p_from_date),-1,a.start_date-p_from_date,0),
nvl(b.maturity_date,p_from_date+1)-a.start_date+decode(sign(a.start_date-p_from_date),-1,a.start_date-p_from_date,0)- (nvl(b.settle_date,b.start_date)-p_to_date)),
'FIXED',
nvl(b.settle_date,b.start_date)-p_to_date),
'RTMM',decode(decode(sign(p_to_date-greatest(nvl(a.maturity_date,p_from_date),nvl(b.settle_date,b.start_date))+1),1,'FLOAT',decode(sign(nvl(b.settle_date,b.start_date)-a.maturity_date+1),1,'FIXED','FLOAT')),
'FLOAT',
decode(sign(p_to_date-nvl(b.settle_date,b.start_date)+1),1,
nvl(b.maturity_date,p_from_date+1)-a.start_date+decode(sign(a.start_date-p_from_date),-1,a.start_date-p_from_date,0),
nvl(b.maturity_date,p_from_date+1)-a.start_date+decode(sign(a.start_date-p_from_date),-1,a.start_date-p_from_date,0)- (nvl(b.settle_date,b.start_date)-p_to_date)),
'FIXED',
nvl(b.settle_date,b.start_date)-p_to_date),
'IRS',nvl(b.maturity_date,p_from_date+1)-a.start_date+decode(sign(a.start_date-p_from_date),-1,a.start_date-p_from_date,0),
'DEB',nvl(b.maturity_date,p_from_date+1)-a.start_date+decode(sign(a.start_date-p_from_date),-1,a.start_date-p_from_date,0),
nvl(a.maturity_date,p_from_date+1)-a.start_date+decode(sign(a.start_date-p_from_date),-1,a.start_date-p_from_date,0)),0);
delete from XTR_INTEREST_RATE_EXPOSURE where created_on
insert into XTR_INTEREST_RATE_EXPOSURE(FUND_INVEST,CREATED_BY,
COMPANY_CODE,CREATED_ON,CURRENCY,DEAL_NUMBER,DEAL_SUBTYPE,DEAL_TYPE,
FIXED_OR_FLOAT,FROM_DATE,GROSS_AMOUNT,MATURITY_DATE,NO_OF_DAYS,
PORTFOLIO_CODE,REF_NUMBER,START_DATE,TO_DATE,TRANSACTION_NUMBER,TRANSACTION_RATE,SYN_FLAG,SYN_PHY)
values(p_fund_invest,fnd_global.user_id,c.COMPANY_CODE,l_sysdate,c.CURRENCY,c.DEAL_NUMBER,c.DEAL_SUBTYPE,c.DEAL_TYPE,
c.FIXED_OR_FLOAT,c.FROM_DATE,c.GROSS_AMOUNT,c.MATURITY_DATE,c.NO_OF_DAYS,
c.PORTFOLIO_CODE,p_unique_ref_number,c.START_DATE,c.TO_DATE,c.TRANSACTION_NUMBER,c.TRANSACTION_RATE,'N','P');
insert into XTR_INTEREST_RATE_EXPOSURE(FUND_INVEST,CREATED_BY,
COMPANY_CODE,CREATED_ON,CURRENCY,DEAL_NUMBER,DEAL_SUBTYPE,DEAL_TYPE,
FIXED_OR_FLOAT,FROM_DATE,GROSS_AMOUNT,MATURITY_DATE,NO_OF_DAYS,
PORTFOLIO_CODE,REF_NUMBER,START_DATE,TO_DATE,TRANSACTION_NUMBER,TRANSACTION_RATE,SYN_FLAG,SYN_PHY)
values(p_fund_invest,fnd_global.user_id,c.COMPANY_CODE,l_sysdate,c.CURRENCY,c.DEAL_NUMBER,c.DEAL_SUBTYPE,c.DEAL_TYPE,
c.FIXED_OR_FLOAT,c.FROM_DATE,c.GROSS_AMOUNT,c.MATURITY_DATE,c.NO_OF_DAYS,
c.PORTFOLIO_CODE,p_unique_ref_number,c.START_DATE,c.TO_DATE,c.TRANSACTION_NUMBER,c.TRANSACTION_RATE,'N',l_syn_phy);
insert into XTR_INTEREST_RATE_EXPOSURE(FUND_INVEST,CREATED_BY,
COMPANY_CODE,CREATED_ON,CURRENCY,DEAL_NUMBER,DEAL_SUBTYPE,DEAL_TYPE,
FIXED_OR_FLOAT,FROM_DATE,GROSS_AMOUNT,MATURITY_DATE,NO_OF_DAYS,
PORTFOLIO_CODE,REF_NUMBER,START_DATE,TO_DATE,TRANSACTION_NUMBER,TRANSACTION_RATE,SYN_FLAG,SYN_PHY)
values(p_fund_invest,fnd_global.user_id,c.COMPANY_CODE,l_sysdate,c.CURRENCY,c.DEAL_NUMBER,c.DEAL_SUBTYPE,c.DEAL_TYPE,
l_fixed_or_float,c.FROM_DATE,-c.GROSS_AMOUNT,c.MATURITY_DATE,c.NO_OF_DAYS,
c.PORTFOLIO_CODE,p_unique_ref_number,c.START_DATE,c.TO_DATE,c.TRANSACTION_NUMBER,c.TRANSACTION_RATE,'Y',l_syn_phy);
insert into XTR_INTEREST_RATE_EXPOSURE(FUND_INVEST,CREATED_BY,
COMPANY_CODE,CREATED_ON,CURRENCY,DEAL_NUMBER,DEAL_SUBTYPE,DEAL_TYPE,
FIXED_OR_FLOAT,FROM_DATE,GROSS_AMOUNT,MATURITY_DATE,NO_OF_DAYS,
PORTFOLIO_CODE,REF_NUMBER,START_DATE,TO_DATE,TRANSACTION_NUMBER,TRANSACTION_RATE,SYN_FLAG,SYN_PHY)
values(p_fund_invest,fnd_global.user_id,c.COMPANY_CODE,l_sysdate,c.CURRENCY,c.DEAL_NUMBER,c.DEAL_SUBTYPE,c.DEAL_TYPE,
c.FIXED_OR_FLOAT,c.FROM_DATE,c.GROSS_AMOUNT,c.MATURITY_DATE,c.NO_OF_DAYS,
c.PORTFOLIO_CODE,p_unique_ref_number,c.START_DATE,c.TO_DATE,c.TRANSACTION_NUMBER,c.TRANSACTION_RATE,'N','S');
insert into XTR_INTEREST_RATE_EXPOSURE(FUND_INVEST,CREATED_BY,
COMPANY_CODE,CREATED_ON,CURRENCY,DEAL_NUMBER,DEAL_SUBTYPE,DEAL_TYPE,
FIXED_OR_FLOAT,FROM_DATE,GROSS_AMOUNT,MATURITY_DATE,NO_OF_DAYS,
PORTFOLIO_CODE,REF_NUMBER,START_DATE,TO_DATE,TRANSACTION_NUMBER,TRANSACTION_RATE,SYN_FLAG,SYN_PHY)
values(p_fund_invest,fnd_global.user_id,c.COMPANY_CODE,l_sysdate,c.CURRENCY,c.DEAL_NUMBER,c.DEAL_SUBTYPE,c.DEAL_TYPE,
l_fixed_or_float,c.FROM_DATE,-c.GROSS_AMOUNT,c.MATURITY_DATE,c.NO_OF_DAYS,
c.PORTFOLIO_CODE,p_unique_ref_number,c.START_DATE,c.TO_DATE,c.TRANSACTION_NUMBER,c.TRANSACTION_RATE,'Y','S');
insert into XTR_INTEREST_RATE_EXPOSURE(FUND_INVEST,CREATED_BY,
COMPANY_CODE,CREATED_ON,CURRENCY,DEAL_NUMBER,DEAL_SUBTYPE,DEAL_TYPE,
FIXED_OR_FLOAT,FROM_DATE,GROSS_AMOUNT,MATURITY_DATE,NO_OF_DAYS,
PORTFOLIO_CODE,REF_NUMBER,START_DATE,TO_DATE,TRANSACTION_NUMBER,TRANSACTION_RATE,SYN_FLAG,SYN_PHY)
values(p_fund_invest,fnd_global.user_id,c.COMPANY_CODE,l_sysdate,c.CURRENCY,c.DEAL_NUMBER,c.DEAL_SUBTYPE,c.DEAL_TYPE,
c.FIXED_OR_FLOAT,c.FROM_DATE,c.GROSS_AMOUNT,c.MATURITY_DATE,c.NO_OF_DAYS,
c.PORTFOLIO_CODE,p_unique_ref_number,c.START_DATE,c.TO_DATE,c.TRANSACTION_NUMBER,c.TRANSACTION_RATE,'N',l_syn_phy);
insert into XTR_INTEREST_RATE_EXPOSURE(FUND_INVEST,CREATED_BY,
COMPANY_CODE,CREATED_ON,CURRENCY,DEAL_NUMBER,DEAL_SUBTYPE,DEAL_TYPE,
FIXED_OR_FLOAT,FROM_DATE,GROSS_AMOUNT,MATURITY_DATE,NO_OF_DAYS,
PORTFOLIO_CODE,REF_NUMBER,START_DATE,TO_DATE,TRANSACTION_NUMBER,TRANSACTION_RATE,SYN_FLAG,SYN_PHY)
values(p_fund_invest,fnd_global.user_id,c.COMPANY_CODE,l_sysdate,c.CURRENCY,c.DEAL_NUMBER,c.DEAL_SUBTYPE,c.DEAL_TYPE,
l_fixed_or_float,c.FROM_DATE,-c.GROSS_AMOUNT,c.MATURITY_DATE,c.NO_OF_DAYS,
c.PORTFOLIO_CODE,p_unique_ref_number,c.START_DATE,c.TO_DATE,c.TRANSACTION_NUMBER,c.TRANSACTION_RATE,'Y',l_syn_phy);
select INCREASE_EFFECTIVE_FROM_DATE,FROM_START_DATE,EFFECTIVE_FROM_DATE
from XTR_TERM_ACTIONS
where DEAL_NO = p_deal_no;
select b.deal_no
from xtr_deals_v a, xtr_deals_v b
where a.int_swap_ref = b.int_swap_ref
and a.deal_type = b.deal_type
and a.deal_type = 'IRS'
and a.deal_subtype <> b.deal_subtype
and a.deal_no = p_deal_no;
update XTR_DEALS
set DUAL_AUTHORISATION_BY = p_validated_by,
DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
where DEAL_NO = p_deal_no
and DEAL_TYPE = p_deal_type;
update XTR_DEALS
set SETTLE_DUAL_AUTHORISATION_BY = p_validated_by,
SETTLE_DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
where DEAL_NO = p_deal_no
and DEAL_TYPE = p_deal_type;
update XTR_DEALS
set DUAL_AUTHORISATION_BY = p_validated_by,
DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
where DEAL_NO = p_deal_no
and DEAL_TYPE = p_deal_type;
update XTR_DEALS
set SETTLE_DUAL_AUTHORISATION_BY = p_validated_by,
SETTLE_DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
where DEAL_NO = p_deal_no
and DEAL_TYPE = p_deal_type;
update XTR_DEALS
set DUAL_AUTHORISATION_BY = p_validated_by,
DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
where DEAL_NO = p_deal_no
and DEAL_TYPE = p_deal_type;
update XTR_DEALS
set SETTLE_DUAL_AUTHORISATION_BY = p_validated_by,
SETTLE_DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
where DEAL_NO = p_deal_no
and DEAL_TYPE = p_deal_type;
update XTR_DEALS
set DUAL_AUTHORISATION_BY = p_validated_by,
DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
where DEAL_NO = p_deal_no
and DEAL_TYPE = p_deal_type;
update XTR_DEALS
set SETTLE_DUAL_AUTHORISATION_BY = p_validated_by,
SETTLE_DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
where DEAL_NO = p_deal_no
and DEAL_TYPE = p_deal_type;
update XTR_DEALS
set DUAL_AUTHORISATION_BY = p_validated_by,
DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
where DEAL_NO = p_deal_no
and DEAL_TYPE = p_deal_type;
update XTR_DEALS
set DUAL_AUTHORISATION_BY = p_validated_by,
DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
where DEAL_NO = p_deal_no
and DEAL_TYPE = p_deal_type;
update XTR_DEALS
set DUAL_AUTHORISATION_BY = p_validated_by,
DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
where DEAL_NO = p_deal_no
and DEAL_TYPE = p_deal_type;
update XTR_DEALS
set SETTLE_DUAL_AUTHORISATION_BY = p_validated_by,
SETTLE_DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
where DEAL_NO = p_deal_no
and DEAL_TYPE = p_deal_type;
update XTR_DEALS -- Update paying side deal
set DUAL_AUTHORISATION_BY = p_validated_by,
DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
where DEAL_NO = p_deal_no
and DEAL_TYPE = p_deal_type;
update XTR_DEALS -- Update receiving side deal
set DUAL_AUTHORISATION_BY = p_validated_by,
DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
where DEAL_NO = receive_deal_no
and DEAL_TYPE = p_deal_type;
update XTR_DEALS
set DUAL_AUTHORISATION_BY = p_validated_by,
DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
where DEAL_NO = p_deal_no
and DEAL_TYPE = p_deal_type;
update XTR_ROLLOVER_TRANSACTIONS
set DUAL_AUTHORISATION_BY = p_validated_by,
DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
where DEAL_NUMBER = p_deal_no
and DEAL_TYPE = p_deal_type
and settle_date is null;
update XTR_DEALS
set DUAL_AUTHORISATION_BY = p_validated_by,
DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
where DEAL_NO = p_deal_no
and DEAL_TYPE = p_deal_type;
update XTR_DEAL_DATE_AMOUNTS
set DUAL_AUTHORISATION_BY = p_validated_by,
DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
where DEAL_NUMBER = p_deal_no
and TRANSACTION_NUMBER = p_trans_no
and DEAL_TYPE = p_deal_type;
update XTR_ROLLOVER_TRANSACTIONS
set DUAL_AUTHORISATION_BY = p_validated_by,
DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
where DEAL_NUMBER = p_deal_no
and TRANSACTION_NUMBER = p_trans_no
and DEAL_TYPE = p_deal_type;
update XTR_DEAL_DATE_AMOUNTS
set DUAL_AUTHORISATION_BY = p_validated_by,
DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
where DEAL_NUMBER = p_deal_no
and TRANSACTION_NUMBER = p_trans_no
and DEAL_TYPE = p_deal_type;
update XTR_DEALS
set DUAL_AUTHORISATION_BY = p_validated_by,
DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
where DEAL_NO = p_deal_no
and DEAL_TYPE = p_deal_type;
update XTR_DEAL_DATE_AMOUNTS
set DUAL_AUTHORISATION_BY = p_validated_by,
DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
where DEAL_NUMBER = p_deal_no
and DEAL_TYPE = p_deal_type;
update XTR_DEALS
set DUAL_AUTHORISATION_BY = p_validated_by,
DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
where DEAL_NO = p_deal_no
and DEAL_TYPE = p_deal_type;
update XTR_ROLLOVER_TRANSACTIONS
set DUAL_AUTHORISATION_BY = p_validated_by,
DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
where DEAL_NUMBER = p_deal_no
and DEAL_TYPE = p_deal_type;
update XTR_TERM_ACTIONS
set DUAL_AUTHORISATION_BY = p_validated_by,
DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
where DEAL_NO = p_deal_no;
update XTR_ROLLOVER_TRANSACTIONS
set DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate),
DUAL_AUTHORISATION_BY = p_validated_by
where DEAL_NUMBER = p_deal_no
and START_DATE >= l_date
and DEAL_TYPE = p_deal_type;
update XTR_ROLLOVER_TRANSACTIONS
set DUAL_AUTHORISATION_BY = p_validated_by,
DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
where DEAL_NUMBER = p_deal_no
and TRANSACTION_NUMBER = p_trans_no
and DEAL_TYPE = p_deal_type;
update XTR_EXPOSURE_TRANSACTIONS
set DUAL_AUTHORISATION_BY = p_validated_by,
DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
where TRANSACTION_NUMBER = p_trans_no;
update XTR_INTERACCT_TRANSFERS
set DUAL_AUTHORISATION_BY = p_validated_by,
DUAL_AUTHORISATION_ON= decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
where TRANSACTION_NUMBER = p_trans_no;
select a.ACCOUNT_NUMBER,a.OPENING_BALANCE,a.STATEMENT_DATE,a.CURRENCY,a.PARTY_CODE
from XTR_BANK_ACCOUNTS a,
XTR_PARTIES_V b
where nvl(a.PROJECTED_BALANCE_UPDATED_ON,to_date('01/01/1980','DD/MM/YYYY')) < trunc(SYSDATE)
and nvl(a.SETOFF_ACCOUNT_YN,'N') <> 'Y'
and b.PARTY_CODE = a.PARTY_CODE
and (b.PARTY_TYPE = 'C' or b.INTERNAL_PTY = 'Y');
select sum(CASHFLOW_AMOUNT),COMPANY_ACCOUNT,CURRENCY
from XTR_SETTLEMENTS_V
where AMOUNT_DATE > nvl(l_state_date,to_date('01/01/1980','DD/MM/YYYY'))
and AMOUNT_DATE < trunc(SYSDATE)
and COMPANY = l_acct_party
and COMPANY_ACCOUNT = l_acct
and CURRENCY = l_ccy
group by COMPANY_ACCOUNT,CURRENCY;
update XTR_BANK_ACCOUNTS
set PROJECTED_BALANCE = OPENING_BALANCE,
PROJECTED_BALANCE_UPDATED_ON = trunc(SYSDATE),
PROJECTED_BALANCE_DATE = STATEMENT_DATE
where ACCOUNT_NUMBER = l_acct
and PARTY_CODE = l_acct_party
and CURRENCY = l_ccy;
update XTR_BANK_ACCOUNTS
set PROJECTED_BALANCE = nvl(l_op_balance,0) + nvl(l_cflow,0),
PROJECTED_BALANCE_UPDATED_ON = trunc(SYSDATE),
PROJECTED_BALANCE_DATE = (trunc(SYSDATE) -1)
where ACCOUNT_NUMBER = l_settle_acct
and PARTY_CODE = l_acct_party
and CURRENCY = l_acct_ccy;
update XTR_BANK_ACCOUNTS
set PROJECTED_BALANCE = nvl(l_op_balance,0),
PROJECTED_BALANCE_UPDATED_ON = trunc(SYSDATE),
PROJECTED_BALANCE_DATE = (trunc(SYSDATE) -1)
where ACCOUNT_NUMBER = l_acct
and PARTY_CODE = l_acct_party
and CURRENCY = l_ccy;
ex_error_insert exception;
ex_error_delete exception;
ex_error_update exception;
SELECT AUTHORISED,AUTH_TO_CREATE,AUTH_TO_DELETE,
AUTH_TO_UPDATE,PASSWORD_EXPIRY
FROM XTR_USER_AUTHORITIES
where USER_NAME=p_user
and FORM_NOS=l_form_nos;
l_insert varchar2(1);
l_delete varchar2(1);
l_update varchar2(1);
SELECT 'Y'
FROM XTR_COMPANY_AUTHORITIES
where DEALER_CODE=p_user
and COMPANY_AUTHORISED_FOR_INPUT='Y'
and PARTY_CODE =p_company_code;
select name
from xtr_deal_types
where deal_type=p_deal_type;
fetch get_user_auth into l_auth,l_insert,l_delete,l_update,l_expiry_date;
elsif p_action='INSERT' and l_insert <>'Y' then
raise ex_error_insert;
elsif p_action='DELETE' and l_delete <>'Y' then
raise ex_error_delete;
elsif p_action='UPDATE' and l_update <>'Y' then
raise ex_error_update;
when ex_error_insert then
FND_MESSAGE.Set_Name('XTR', 'XTR_1003');
when ex_error_delete then
FND_MESSAGE.Set_Name('XTR', 'XTR_1004');
when ex_error_update then
FND_MESSAGE.Set_Name('XTR', 'XTR_1005');
select distinct rtrim(SETOFF),rtrim(BANK_CODE),
SETOFF_COMPANY,CURRENCY
from XTR_BANK_ACCOUNTS;
select INTEREST_RATE
from XTR_INTEREST_RATE_RANGES
where REF_CODE = l_setoff||'-'||l_bank_code
and MIN_AMT <= l_this_bal
and MAX_AMT >= l_this_bal;
select ROUNDING_FACTOR,YEAR_BASIS
from XTR_MASTER_CURRENCIES_V
where CURRENCY = l_ccy;
select distinct BALANCE_DATE
from XTR_BANK_BALANCES
where BALANCE_DATE >= (select max(BALANCE_DATE)
from XTR_BANK_BAL_INTERFACE)
and SETOFF = l_setoff
order by BALANCE_DATE asc;
select INTEREST_RATE
from XTR_BANK_BALANCES
where ACCOUNT_NUMBER = l_setoff||'-'||l_bank_code
and BALANCE_DATE = l_prv_date;
select a.BALANCE_DATE,nvl(sum(a.BALANCE_CFLOW),0),
nvl(sum(a.ACCUM_INT_CFWD),0)
from XTR_BANK_BALANCES a
where ACCOUNT_NUMBER = l_setoff||'-'||l_bank_code
and a.BALANCE_DATE = (select max(b.BALANCE_DATE)
from XTR_BANK_BALANCES b
where ACCOUNT_NUMBER = l_setoff||'-'||l_bank_code
and b.BALANCE_DATE < l_calc_date)
group by a.BALANCE_DATE,a.ACCOUNT_NUMBER;
select nvl(sum(a.BALANCE_CFLOW),0)
from XTR_BANK_BALANCES a
where SETOFF = l_setoff
and a.BALANCE_DATE = l_calc_date;
delete XTR_BANK_BALANCES
where ACCOUNT_NUMBER = l_setoff||'-'||l_bank_code
and BALANCE_DATE >= (select max(BALANCE_DATE)
from XTR_BANK_BAL_INTERFACE);
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,
created_on, created_by)
values
(l_setoff_company,l_setoff||'-'||l_bank_code,
l_calc_date,l_no_days,l_this_bal,0,l_this_bal,l_int_bf,
l_interest,l_rate,0,0,l_int_cf,
sysdate, fnd_global.user_id);