The following lines contain the word 'select', 'insert', 'update' or 'delete':
select XTR_ROLES_S.nextval
into l_wf_sequence
from dual;
select fu.USER_NAME, xwf.AMOUNT,xle.EXCEEDED_BY_AMOUNT
from XTR_WF_USER_ROLES xwf,
XTR_INTERGROUP_TRANSFERS_V xds,
XTR_LIMIT_EXCESS_LOG_V xle,
FND_USER fu
where xwf.ROLE_NAME = 'XTR_LIMITS_NOTIFICATION'
and xds.DEAL_NUMBER = p_deal_no
and xds.TRANSACTION_NUMBER = p_trans_no
and xle.LOG_ID = p_log_id
and fu.USER_ID = xwf.USER_ID
and fu.start_date <= trunc(sysdate)
and nvl(fu.end_date, sysdate+1) >= trunc(sysdate)
and nvl(xwf.COMPANY, xds.COMPANY_CODE) = xds.COMPANY_CODE
and nvl(xwf.PORTFOLIO, xds.PORTFOLIO) = xds.PORTFOLIO
and nvl(xwf.DEAL_TYPE, xds.DEAL_TYPE) = xds.DEAL_TYPE
and nvl(xwf.PRODUCT_TYPE, xds.PRODUCT_TYPE) = xds.PRODUCT_TYPE
and nvl(xwf.CPARTY, xds.PARTY_CODE) = xds.PARTY_CODE
and nvl(xwf.LIMIT_CHECK_TYPE, xle.LIMIT_CHECK_TYPE) = xle.LIMIT_CHECK_TYPE
order by fu.USER_NAME, xwf.PRIORITY,nvl(xle.EXCEEDED_BY_AMOUNT,0) desc;
select fu.USER_NAME, xwf.AMOUNT,xle.EXCEEDED_BY_AMOUNT
from XTR_WF_USER_ROLES xwf,
XTR_ROLLOVER_TRANSACTIONS_V xds,
XTR_LIMIT_EXCESS_LOG_V xle,
FND_USER fu
where xwf.ROLE_NAME = 'XTR_LIMITS_NOTIFICATION'
and xds.DEAL_NUMBER = p_deal_no
and xds.TRANSACTION_NUMBER = p_trans_no
and xle.LOG_ID = p_log_id
and fu.USER_ID = xwf.USER_ID
and fu.start_date <= trunc(sysdate)
and nvl(fu.end_date, sysdate+1) >= trunc(sysdate)
and nvl(xwf.COMPANY, xds.COMPANY_CODE) = xds.COMPANY_CODE
and nvl(xwf.PORTFOLIO, xds.PORTFOLIO_CODE) = xds.PORTFOLIO_CODE
and nvl(xwf.DEAL_TYPE, xds.DEAL_TYPE) = xds.DEAL_TYPE
and nvl(xwf.DEAL_SUBTYPE, xds.DEAL_SUBTYPE) = xds.DEAL_SUBTYPE
and nvl(xwf.PRODUCT_TYPE, xds.PRODUCT_TYPE) = xds.PRODUCT_TYPE
and nvl(xwf.CPARTY, xds.CPARTY_CODE) = xds.CPARTY_CODE
and nvl(xwf.DEALER, xds.DEALER_CODE) = xds.DEALER_CODE
and nvl(xwf.LIMIT_CHECK_TYPE, xle.LIMIT_CHECK_TYPE) = xle.LIMIT_CHECK_TYPE
order by fu.USER_NAME, xwf.PRIORITY,nvl(xle.EXCEEDED_BY_AMOUNT,0) desc;
select fu.USER_NAME, xwf.AMOUNT,xle.EXCEEDED_BY_AMOUNT
from XTR_WF_USER_ROLES xwf,
XTR_DEALS_V xds,
XTR_LIMIT_EXCESS_LOG_V xle,
FND_USER fu
where xwf.ROLE_NAME = 'XTR_LIMITS_NOTIFICATION'
and xds.DEAL_NO = p_deal_no
and nvl(xds.TRANSACTION_NO,0) = nvl(p_trans_no,0)
and xle.LOG_ID = p_log_id
and fu.USER_ID = xwf.USER_ID
and fu.start_date <= trunc(sysdate)
and nvl(fu.end_date, sysdate+1) >= trunc(sysdate)
and nvl(xwf.COMPANY, xds.COMPANY_CODE) = xds.COMPANY_CODE
and nvl(xwf.PORTFOLIO, xds.PORTFOLIO_CODE) = xds.PORTFOLIO_CODE
and nvl(xwf.DEAL_TYPE, xds.DEAL_TYPE) = xds.DEAL_TYPE
and nvl(xwf.DEAL_SUBTYPE, xds.DEAL_SUBTYPE) = xds.DEAL_SUBTYPE
and nvl(xwf.PRODUCT_TYPE, xds.PRODUCT_TYPE) = xds.PRODUCT_TYPE
and nvl(xwf.CPARTY, xds.CPARTY_CODE) = xds.CPARTY_CODE
and nvl(xwf.DEALER, xds.DEALER_CODE) = xds.DEALER_CODE
and nvl(xwf.LIMIT_CHECK_TYPE, xle.LIMIT_CHECK_TYPE) = xle.LIMIT_CHECK_TYPE
order by fu.USER_NAME, xwf.PRIORITY,nvl(xle.EXCEEDED_BY_AMOUNT,0) desc;
select XTR_WF_S.nextval
into l_wf_sequence
from dual;
select xle.deal_number, xle.transaction_number, decode(xle.exception_type,
'EXCEEDED', FND_MESSAGE.GET_STRING('XTR','XTR_2242'), 'WARNING', FND_MESSAGE.GET_STRING('XTR','XTR_2241'), 'NO_AUTHO',
FND_MESSAGE.GET_STRING('XTR','XTR_2243'), 'NO_LIMIT', FND_MESSAGE.GET_STRING('XTR','XTR_2244')) as exception_type,
decode(xle.exception_type, 'EXCEEDED', 'XTR_2245',
'WARNING', 'XTR_2246', 'NO_AUTHO', 'XTR_2247',
'NO_LIMIT','XTR_2248') as exception_desc,
decode(xle.LIMIT_CHECK_TYPE,
'GLOBAL',FND_MESSAGE.GET_STRING('XTR','XTR_2233'),
'SOVRN', FND_MESSAGE.GET_STRING('XTR','XTR_2234'),
'DLR_DEAL', FND_MESSAGE.GET_STRING('XTR','XTR_2235'),
'CPARTY', FND_MESSAGE.GET_STRING('XTR','XTR_2237'),
'SETTLE', FND_MESSAGE.GET_STRING('XTR','XTR_2238'),
'CCY', FND_MESSAGE.GET_STRING('XTR','XTR_2239'),
'GROUP', FND_MESSAGE.GET_STRING('XTR','XTR_2250'),
'TIME', FND_MESSAGE.GET_STRING('XTR','XTR_2240') ) as exception_token,
xle.limit_code,
xle.exceeded_by_amount exceeded_by_amount_dsp,
xle.exceeded_on_date exceeded_on_date_dsp,
xle.currency, xle.company_code, xle.limit_party,
xle.amount_date amount_date_dsp,
xle.limiting_amount limiting_amount_dsp,
xle.authorised_by, xle.dealer_code, xle.log_id
from XTR_LIMIT_EXCESS_LOG_V xle
where xle.log_id = p_log_id
order by xle.deal_number, xle.transaction_number;
SELECT nvl(param_value,'USD')
INTO l_ccy_code
FROM xtr_pro_param_v
WHERE param_name = 'SYSTEM_FUNCTIONAL_CCY';
PROCEDURE DELETE_XTR_USERS(p_name IN VARCHAR2,
p_dsp_name IN VARCHAR2,
p_email IN VARCHAR2)
IS
BEGIN
delete from WF_LOCAL_USERS
where NAME = p_name
and DISPLAY_NAME = p_dsp_name
and EMAIL_ADDRESS = p_email;
delete from WF_LOCAL_USER_ROLES
where USER_NAME = p_name
and ROLE_ORIG_SYSTEM = 'WF_LOCAL_ROLES';
END DELETE_XTR_USERS;