[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
insert into xdp_adapter_reg (
channel_name,
fe_id,
adapter_status,
process_id,
adapter_type,
adapter_name,
adapter_display_name,
usage_code,
startup_mode,
service_instance_id,
log_level,
connect_on_demand_flag,
max_idle_time_minutes,
cmd_line_options,
cmd_line_args,
log_file_name,
seq_in_fe,
application_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
values
( p_ChannelName,
p_FeID,
p_AdapterStatus,
-1,
p_AdapterType,
p_AdapterName,
p_AdapterDispName,
p_UsageCode,
p_StartupMode,
p_ConcQID,
p_LogLevel,
p_CODFlag,
p_MaxIdleTime,
p_CmdLineOpts,
p_CmdLineArgs,
p_LogFileName,
p_SeqInFE,
XDP_ADAPTER.pv_AppID,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID);
select XDP_ADAPTER_ADMIN_REQS_S.NEXTVAL into p_RequestID from dual;
insert into xdp_adapter_admin_reqs
(request_id,
channel_name,
request_type,
request_date,
requested_by_user,
request_frequency,
job_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
values
(p_RequestID,
SubmitAdapterAdminReq.p_ChannelName,
SubmitAdapterAdminReq.p_RequestType,
SubmitAdapterAdminReq.p_RequestDate,
SubmitAdapterAdminReq.p_RequestedBy,
SubmitAdapterAdminReq.p_Freq,
p_JobID,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID);
Procedure UpdateAdapterAdminReq(p_RequestID in number,
p_RequestDate in date default sysdate,
p_RequestedBy in varchar2,
p_Freq in number default null)
is
l_jobID number := -1;
XDP_CRON_UTIL.UpdateDBJob(p_jobID => l_jobID,
p_request => p_RequestID,
p_ReqDate => p_RequestDate,
p_Freq => p_Freq);
update xdp_adapter_admin_reqs set
request_date = p_RequestDate,
requested_by_user = p_RequestedBy,
request_frequency = p_Freq,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where request_id = p_RequestID;
end UpdateAdapterAdminReq;
delete from xdp_adapter_admin_reqs where request_id = p_RequestID;
select xar.request_type, xar.request_date, xar.requested_by_user, xar.request_frequency,
xar.channel_name, xar.job_id
from xdp_adapter_admin_reqs xar
where request_id = p_RequestID;
select request_id, job_id
from xdp_adapter_admin_reqs
where channel_name = DoesSystemReqAlreadyExist.p_ChannelName
and request_type = DoesSystemReqAlreadyExist.p_RequestType
and requested_by_user = XDP_ADAPTER.pv_adminReqBySystem
and request_date <= nvl(p_RequestDate,sysdate);
select fe_id, process_id, service_instance_id
from xdp_adapter_reg
where channel_name = p_ChannelName;
select xfe.fulfillment_element_name,
xat.application_mode,
xat.cmd_line_options base_cmd_options,
xat.cmd_line_args base_cmd_args,
xat.adapter_class,
xat.inbound_required_flag,
xag.adapter_name,
xag.cmd_line_options sub_cmd_options,
xag.cmd_line_args sub_cmd_args,
xag.log_level,
xag.log_file_name,
xag.service_instance_id
from xdp_fes xfe,
xdp_adapter_types_b xat,
xdp_adapter_reg xag
where xag.channel_name = p_ChannelName
and xag.fe_id = xfe.fe_id
and xag.adapter_type = xat.adapter_type;
Procedure UpdateAdapter( p_ChannelName in varchar2,
p_Status in varchar2 default null,
p_ProcessId in number default null,
p_UsageCode in varchar2 default null,
p_StartupMode in varchar2 default null,
p_AdapterName in varchar2 default null,
p_AdapterDispName in varchar2 default null,
p_SvcInstId in number default null,
p_WFItemType in varchar2 default null,
p_WFItemKey in varchar2 default null,
p_WFActivityName in varchar2 default null,
p_CODFlag in varchar2 default null,
p_MaxIdleTime in number default -1,
p_LastVerified in date default null,
p_CmdLineOpts in varchar2 default 'CmdLineOpts',
p_CmdLineArgs in varchar2 default 'CmdLineArgs',
p_LogLevel in varchar2 default null,
p_LogFileName in varchar2 default 'LogFileName',
p_SeqInFE in number default -1)
is
l_errorCount NUMBER := 0;
XDP_ERRORS_PKG.UPDATE_ERROR_COUNT (
p_object_type => XDP_ADAPTER.pv_errorObjectTypeAdapter,
p_object_key => p_ChannelName,
p_error_count => l_errorCount);
select adapter_display_name into l_AdapterDisplayName
from xdp_adapter_reg where channel_name = p_ChannelName;
-- status_active_time is updated anytime status is updated
update xdp_adapter_reg
set adapter_status = nvl(l_Status, adapter_status),
status_active_time = decode(l_Status,
null, status_active_time,
sysdate),
process_id = nvl(p_ProcessId,
decode(l_Status,
XDP_ADAPTER.pv_statusStopped, -1,
XDP_ADAPTER.pv_statusStoppedError, -1,
XDP_ADAPTER.pv_statusTerminated, -1,
XDP_ADAPTER.pv_statusStarting, -1,
XDP_ADAPTER.pv_statusDeactivated, -1,
XDP_ADAPTER.pv_statusDeactivatedSystem, -1,
process_id)),
node = decode(l_Status,
XDP_ADAPTER.pv_statusStopped, null,
XDP_ADAPTER.pv_statusStoppedError, null,
XDP_ADAPTER.pv_statusTerminated, null,
XDP_ADAPTER.pv_statusDeactivated, null,
XDP_ADAPTER.pv_statusDeactivatedSystem, null,
node),
usage_code = nvl(p_UsageCode, usage_code),
startup_mode = nvl(p_StartupMode, startup_mode),
adapter_name = nvl(p_AdapterName, adapter_name),
adapter_display_name = nvl(p_AdapterDispName,
adapter_display_name),
service_instance_id = nvl(p_SvcInstId, service_instance_id),
wf_item_type = nvl(p_WFItemType, wf_item_type),
wf_item_key = nvl(p_WFItemKey, wf_item_key),
wf_activity_name = nvl(p_WFActivityName,wf_activity_name),
connect_on_demand_flag = nvl(p_CODFlag,connect_on_demand_flag),
max_idle_time_minutes = decode(p_MaxIdleTime,
-1, max_idle_time_minutes,
p_MaxIdleTime),
cmd_line_options = decode(p_CmdLineOpts,
'CmdLineOpts', cmd_line_options,
p_CmdLineOpts),
cmd_line_args = decode(p_CmdLineArgs,
'CmdLineArgs', cmd_line_args,
p_CmdLineArgs),
last_verified_time = nvl(p_LastVerified,
decode(l_Status,
XDP_ADAPTER.pv_statusStarting, last_verified_time,
XDP_ADAPTER.pv_statusStopping, last_verified_time,
XDP_ADAPTER.pv_statusSuspending, last_verified_time,
XDP_ADAPTER.pv_statusResuming, last_verified_time,
XDP_ADAPTER.pv_statusConnecting, last_verified_time,
XDP_ADAPTER.pv_statusDisconnecting, last_verified_time,
XDP_ADAPTER.pv_statusTerminating, last_verified_time,
sysdate)),
log_level = nvl(p_LogLevel, log_level),
log_file_name = decode(p_LogFileName,
'LogFileName', log_file_name,
p_LogFileName),
seq_in_fe = decode(p_SeqInFE,
-1, seq_in_fe,
p_SeqInFE),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where
channel_name = p_ChannelName;
end UpdateAdapter;
Procedure Update_Adapter_Active_Time(p_ChannelName IN VARCHAR2)
IS
BEGIN
UPDATE xdp_adapter_reg
SET status_active_time = sysdate,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE channel_name = p_ChannelName;
END Update_Adapter_Active_time;
SELECT a.adapter_status
FROM xdp_adapter_reg a,
xdp_adapter_types_b b
WHERE a.adapter_type = b.adapter_type AND
a.fe_id = p_fe_id AND
(b.application_mode = 'QUEUE' OR
(b.application_mode = 'NONE' AND b.inbound_required_flag = 'Y'));
SELECT a.adapter_status
FROM xdp_adapter_reg a,
xdp_adapter_types_b b
WHERE a.adapter_type = b.adapter_type AND
a.fe_id = p_fe_id AND
NOT(b.application_mode = 'QUEUE' OR
(b.application_mode = 'NONE' AND b.inbound_required_flag = 'Y'));
SELECT count(*)
INTO l_adapter_status_cnt
FROM xdp_adapter_reg a, xdp_adapter_types_b b
WHERE a.adapter_type = b.adapter_type
AND a.fe_id = p_fe_id
AND (b.application_mode = 'QUEUE' OR
(b.application_mode = 'NONE' AND b.inbound_required_flag = 'Y'))
AND a.adapter_status IN (XDP_ADAPTER.pv_statusInUse, XDP_ADAPTER.pv_statusRunning);
SELECT count(*)
INTO l_adapter_status_cnt
FROM xdp_adapter_reg a, xdp_adapter_types_b b
WHERE a.adapter_type = b.adapter_type
AND a.fe_id = p_fe_id
AND NOT(b.application_mode = 'QUEUE' OR
(b.application_mode = 'NONE' AND b.inbound_required_flag = 'Y'))
AND a.adapter_status IN (XDP_ADAPTER.pv_statusInUse, XDP_ADAPTER.pv_statusRunning);
SELECT COUNT(xomq.msg_id) num_of_jobs
INTO l_num_of_job_cnt
FROM AQ$xnp_out_msg_qtab xomq
WHERE xomq.consumer_name = p_fe_name;
SELECT COUNT(DISTINCT xaj.job_id) num_of_jobs
INTO l_num_of_job_cnt
FROM xdp_adapter_job_queue xaj, xdp_adapter_reg xar, xdp_adapter_types_b xat
WHERE xaj.fe_id = xar.fe_id
AND xar.adapter_type = xat.adapter_type
AND xaj.fe_id = p_fe_id
AND xat.application_mode <> 'QUEUE';
select xag.adapter_status
from xdp_adapter_reg xag
where channel_name = p_ChannelName;
select node into l_InstanceName from xdp_adapter_reg where channel_name = l_ChannelName;
update xdp_adapter_reg set node = pv_InstanceName where channel_name = l_ChannelName;
select NVL(CONNECT_ON_DEMAND_FLAG, 'N') into l_CODFlag
from xdp_adapter_reg
where CHANNEL_NAME = p_ChannelName;
select 'Y' into l_check
from dual
where exists
( select JOB_ID from xdp_adapter_job_queue a, xdp_adapter_reg b
where b.channel_name = p_ChannelName
and b.fe_id = a.fe_id);
Procedure Update_Adapter_Status (p_ChannelName in varchar2,
p_Status in varchar2,
p_ErrorMsg in varchar2 default null,
p_ErrorMsgParams in varchar2 default null,
p_WFItemType in varchar2 default null,
p_WFItemKey in varchar2 default null)
is
PRAGMA AUTONOMOUS_TRANSACTION;
UpdateAdapter (
p_ChannelName => p_ChannelName,
p_Status => p_Status,
p_WFItemType => p_WFItemType,
p_WFItemKey => p_WFItemKey
);
end Update_Adapter_Status;
select job_id
from xdp_adapter_admin_reqs
where request_id = p_RequestId;
select fe_id
from xdp_fes
where UPPER(fulfillment_element_name) = UPPER(p_FeName);
select count(*)
into l_CurrentCount
from XDP_ADAPTER_REG
where FE_ID = p_fe_id and
ADAPTER_STATUS not in (XDP_ADAPTER.pv_statusStopped,
XDP_ADAPTER.pv_statusStoppedError,
XDP_ADAPTER.pv_statusTerminated,
-- (ankung) XDP_ADAPTER.pv_statusStopping,
-- XDP_ADAPTER.pv_statusTerminating,
XDP_ADAPTER.pv_statusNotAvailable,
XDP_ADAPTER.pv_statusDeactivated,
XDP_ADAPTER.pv_statusDeactivatedSystem);
select MAX_CONNECTION
into l_MaxCount
from XDP_FES
where FE_ID = p_fe_id;
PROCEDURE Delete_Adapter (p_channel_name IN VARCHAR2)
IS
cursor c_GetAdapterAdminReqs is
select xar.request_id
from xdp_adapter_admin_reqs xar
where channel_name = p_channel_name;
DELETE FROM xdp_error_log WHERE
object_type = XDP_ADAPTER.pv_errorObjectTypeAdapter and
object_key = p_channel_name;
DELETE FROM xdp_adapter_audit WHERE channel_name = p_channel_name;
-- Delete all occurences of the requests and dbms_jobs for the adapter from the
-- XDP_ADAPTER_ADMIN_REQS table if present
--
for v_AdapterReq in c_GetAdapterAdminReqs loop
RemoveAdapterAdminReq (p_RequestID => v_AdapterReq.request_id);
-- Delete the Adapter from the XDP_ADAPTER_REG table
--
DELETE FROM xdp_adapter_reg WHERE channel_name = p_channel_name;
END Delete_Adapter;
PROCEDURE Delete_Adapters_For_Fe (p_fe_id IN NUMBER)
IS
cursor c_GetAdapters is
select channel_name
from xdp_adapter_reg
where fe_id = p_fe_id;
-- Delete all adapters belonging to the FE, if present
for v_Adapters in c_GetAdapters loop
Delete_Adapter (p_channel_name => v_Adapters.channel_name);
END Delete_Adapters_For_Fe;
select xag.adapter_name,
xag.adapter_status,
xag.adapter_type,
xag.service_instance_id,
xag.connect_on_demand_flag,
xag.max_idle_time_minutes,
xag.cmd_line_options,
xag.cmd_line_args,
xag.log_level,
xag.log_file_name
from
xdp_adapter_reg xag
where xag.channel_name = p_ChannelName;
insert into xdp_adapter_audit (
adapter_audit_id,
channel_name,
request_type,
request_status,
completion_date,
requested_by_user,
adapter_name,
adapter_status,
adapter_type,
service_instance_id,
connect_on_demand_flag,
max_idle_time_minutes,
cmd_line_options,
cmd_line_args,
log_file_name,
application_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
values
( XDP_ADAPTER_AUDIT_S.NEXTVAL,
p_ChannelName,
p_RequestType,
p_RequestStatus,
sysdate,
p_RequestedBy,
l_AdapterName,
l_AdapterStatus,
l_AdapterType,
l_ConcQID,
l_COD,
l_MaxIdleTime,
l_CmdOptions,
l_CmdArgs,
l_LogFileName,
XDP_ADAPTER.pv_AppID,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID);
select XGC.START_DATE
from XDP_FE_SW_GEN_LOOKUP XSW, XDP_FE_GENERIC_CONFIG XGC
where XGC.FE_ID = c_fe_id and
XSW.ADAPTER_TYPE = c_AdapterType and
XSW.FE_SW_GEN_LOOKUP_ID = XGC.FE_SW_GEN_LOOKUP_ID and
((XGC.END_DATE is null) or ((XGC.END_DATE is not null) and (XGC.END_DATE > SYSDATE)));
SELECT xad.adapter_name
FROM xdp_adapter_reg xad, xdp_adapter_types_b t,xdp_fes XFE
WHERE XAD.fe_id = XFE.fe_id
AND XFE.fulfillment_element_name = p_fe_name
AND xad.adapter_type = t.adapter_type
AND application_mode='QUEUE'
AND xad.adapter_status not in (XDP_ADAPTER.pv_statusNotAvailable)
ORDER BY
DECODE(adapter_status, xdp_adapter.pv_statusRunning, 1,
xdp_adapter.pv_statusSuspended, 2,
xdp_adapter.pv_statusDisconnected, 3,
xdp_adapter.pv_statusStopped, 4, 5)
ASC ;
SELECT xad.adapter_name
FROM xdp_adapter_reg xad, xdp_adapter_types_b t
WHERE xad.adapter_type = t.adapter_type
AND FE_ID = FEID
AND application_mode='QUEUE'
AND xad.adapter_status not in (XDP_ADAPTER.pv_statusNotAvailable)
order by
DECODE(adapter_status, xdp_adapter.pv_statusRunning, 1,
xdp_adapter.pv_statusSuspended, 2,
xdp_adapter.pv_statusDisconnected, 3,
xdp_adapter.pv_statusStopped, 4, 5)
ASC ;
select 'Y' yahoo
from dual
where exists(
select 1
from XDP_ADAPTER_REG
where fe_id = p_fe_id
and adapter_status not in (XDP_ADAPTER.pv_statusStopped,
XDP_ADAPTER.pv_statusStoppedError,
XDP_ADAPTER.pv_statusTerminated,
XDP_ADAPTER.pv_statusNotAvailable,
XDP_ADAPTER.pv_statusDeactivated,
XDP_ADAPTER.pv_statusDeactivatedSystem)
);
select 'Y' yahoo
from dual
where exists(
select 1
from XDP_ADAPTER_REG arn, XDP_FES fet
where arn.fe_id = fet.fe_id
and fet.fetype_id = p_fetype_id
and arn.adapter_status not in (XDP_ADAPTER.pv_statusStopped,
XDP_ADAPTER.pv_statusStoppedError,
XDP_ADAPTER.pv_statusTerminated,
XDP_ADAPTER.pv_statusNotAvailable,
XDP_ADAPTER.pv_statusDeactivated,
XDP_ADAPTER.pv_statusDeactivatedSystem)
);
select b.adapter_class into l_ClassName
from xdp_adapter_reg a, xdp_adapter_types_b b
where a.CHANNEL_NAME = p_ChannelName and
a.adapter_type = b.adapter_type;
-- Cannot use XDP_ADAPTER_CORE_DB.Update_Adapter_Status, an autonomous
-- procedure
XDP_ADAPTER_CORE_DB.UpdateAdapter (
p_ChannelName => p_ChannelName,
p_Status => XDP_ADAPTER.pv_statusStoppedError
);
-- Could not get lock, Adapter running, update last_verified_date
XDP_ADAPTER_CORE_DB.UpdateAdapter (
p_ChannelName => p_ChannelName,
p_LastVerified => sysdate);
-- Adapter not implemented, so Adapter is 'running', update last_verified_date
XDP_ADAPTER_CORE_DB.UpdateAdapter (
p_ChannelName => p_ChannelName,
p_LastVerified => sysdate);
select a.startup_mode into l_StartupMode
from xdp_adapter_reg a
where a.CHANNEL_NAME = p_ChannelName;
SELECT atb.application_mode
FROM xdp_adapter_reg ar, xdp_adapter_types_b atb
WHERE ar.channel_name = p_Channelname
AND ar.adapter_type = atb.adapter_type;
select ATTRIBUTE_NAME,DEFAULT_VALUE,DISPLAY_NAME,DESCRIPTION
from XDP_ADAPTER_TYPE_ATTRS_VL
where ADAPTER_TYPE = p_adapter_type
and attribute_name not in (
select FE_ATTRIBUTE_NAME from xdp_fe_attribute_def_vl
where fe_sw_gen_lookup_id = p_fe_sw_gen_lookup_id);
SELECT BASE_ADAPTER_TYPE FROM XDP_ADAPTER_TYPES_B
WHERE ADAPTER_TYPE = p_adapter_type;
select XDP.XDP_FE_ATTRIBUTE_DEF_S.nextval into l_fe_attribute_id from dual;
XDP_FE_ATTRIBUTE_DEF_PKG.INSERT_ROW(
l_rowid,
l_fe_attribute_id,
p_fe_sw_gen_lookup_id,
l_at_attr.ATTRIBUTE_NAME,
'N',
l_at_attr.DEFAULT_VALUE,
null,
l_at_attr.DISPLAY_NAME,
l_at_attr.DESCRIPTION,
sysdate,
p_caller_id,
sysdate,
p_caller_id,p_caller_id);
SELECT fetype_id,
max_connection,
min_connection,
session_controller_id,
valid_date,
invalid_date,
geo_area_id,
role_name,
network_unit_id,
description
from xdp_fes_vl
where fe_id = p_FeID;
SELECT
fe_generic_config_id,
fe_sw_gen_lookup_id,
start_date,
end_date,
sw_start_proc,
sw_exit_proc
FROM XDP_FE_GENERIC_CONFIG
WHERE fe_id = l_fe_id;
SELECT
fe_generic_config_id,
fe_attribute_id,
fe_attribute_value,
display_name,
description
FROM XDP_FE_ATTRIBUTE_VAL_VL
WHERE fe_generic_config_id = l_fe_config_id;
select XDP.xdp_fes_s.nextval into l_new_feId from dual;
XDP_FES_PKG.INSERT_ROW(
l_rowid,
l_new_feId,
l_FeRecord.fetype_id,
p_FeName,
l_FeRecord.MAX_CONNECTION,
l_FeRecord.MIN_CONNECTION,
l_FeRecord.SESSION_CONTROLLER_ID,
l_FeRecord.VALID_DATE,
l_FeRecord.INVALID_DATE,
l_FeRecord.GEO_AREA_ID,
l_FeRecord.ROLE_NAME,
l_FeRecord.NETWORK_UNIT_ID,
p_FeDisplayName,
l_FeRecord.DESCRIPTION,
SYSDATE,
p_CallerID,
SYSDATE,
p_CallerID,
p_CallerID
);
select xdp_fe_generic_config_s.nextval into l_new_generic_config_Id from dual;
insert into xdp_fe_generic_config (
fe_generic_config_id,
fe_id,
fe_sw_gen_lookup_id,
start_date,
end_date,
sw_start_proc,
sw_exit_proc,
creation_date,
last_update_date,
last_updated_by,
created_by,
last_update_login,
security_group_id)
Values (
l_new_generic_config_Id,
l_new_feId,
l_fe_gen_config.fe_sw_gen_lookup_id,
l_fe_gen_config.start_date,
l_fe_gen_config.end_date,
l_fe_gen_config.sw_start_proc,
l_fe_gen_config.sw_exit_proc,
sysdate,
sysdate,
p_CallerID,
p_CallerID,
p_CallerID,
null
);
XDP_FE_ATTRIBUTE_VAL_PKG.INSERT_ROW (
l_rowid,
l_fe_val.fe_attribute_id,
l_new_generic_config_Id,
l_fe_val.fe_attribute_value,
l_fe_val.display_name,
l_fe_val.description,
SYSDATE,
p_CallerID,
SYSDATE,
p_CallerID,
p_callerID
);
select instance_name into pv_InstanceName from v$instance;