[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
insert into XDP_ADAPTER_JOB_QUEUE (
JOB_ID,
FE_ID,
ORDER_ID,
WORKITEM_INSTANCE_ID,
FA_INSTANCE_ID,
QUEUED_ON,
WF_ITEM_TYPE,
CHANNEL_USAGE_CODE,
WF_ITEM_KEY,
SYSTEM_HOLD,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
values (XDP_ADAPTER_JOB_QUEUE_S.NEXTVAL,
l_FeID,
l_OrderID,
l_WIInstanceID,
l_FAInstanceID,
NVL(l_ReProcessEnqTime, SYSDATE),
EnqueueFPQueue.itemtype,
l_ChannelUsageCode,
EnqueueFPQueue.itemkey,
p_system_hold,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID);
UPDATE xdp_fa_runtime_list
SET status_code = lv_fa_status
WHERE fa_instance_id = l_fainstanceid ;
PROCEDURE UPDATE_FA_STATUS(p_fa_instance_id IN NUMBER,
p_status_code IN VARCHAR2,
p_itemtype IN VARCHAR2,
p_itemkey IN VARCHAR2 ) ;
select FA_INSTANCE_ID, PROVISIONING_SEQUENCE
from XDP_FA_RUNTIME_LIST
where WORKITEM_INSTANCE_ID = wi_id
and STATUS_CODE = 'STANDBY'
and PROVISIONING_SEQUENCE = (
select MIN(PROVISIONING_SEQUENCE)
from XDP_FA_RUNTIME_LIST
where WORKITEM_INSTANCE_ID = wi_id
and STATUS_CODE = 'STANDBY'
and PROVISIONING_SEQUENCE > prov_seq);
/* Update the XDP_FA_RUNTIME_LIST table with the User defined Workitem Item Type and Item Key */
update XDP_FA_RUNTIME_LIST
set WF_ITEM_TYPE = l_FAItemType,
WF_ITEM_KEY = l_FAItemKey,
LAST_UPDATE_DATE = sysdate, LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where FA_INSTANCE_ID = l_FAInstanceID;
/* Update the XDP_FA_RUNTIME table with the status of the FA, the state of processing,
* FA WF item type and item key
*/
UPDATE_FA_STATUS( p_fa_instance_id => l_FAInstanceID ,
p_status_code => 'IN PROGRESS',
p_itemtype => InitializeFA.itemtype,
p_itemkey => InitializeFA.itemkey );
cursor c_UpdateFEID (p_FAInstanceID number) is
select 'Update FE ID'
from XDP_FA_RUNTIME_LIST
where FA_INSTANCE_ID = p_FAInstanceID
for update of FE_ID, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN;
SELECT display_name into l_routing_proc_disp_name FROM XDP_PROC_BODY_VL
WHERE proc_name = l_LocateFEProc;
FOR v_UpdateFEID in c_UpdateFEID(l_FAInstanceID) LOOP
update XDP_FA_RUNTIME_LIST set FE_ID = l_FeID,
LAST_UPDATE_DATE = sysdate, LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where current of c_UpdateFEID;
update XDP_FA_RUNTIME_LIST set PROC_NAME = l_FaProvProc,
LAST_UPDATE_DATE = sysdate, LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where fa_instance_id = l_FAInstanceID;
XDP_ADAPTER_CORE_DB.Update_Adapter_Status (
p_ChannelName => l_ChannelName,
p_Status => XDP_ADAPTER.pv_statusInUse,
p_WFItemType => IsChannelAvailable.itemtype,
p_WFItemKey => IsChannelAvailable.itemkey);
select adapter_status
from XDP_ADAPTER_REG
where channel_name = ChannelName;
/* Raja: Added on 8/31/1999. Verify Channel activity should also update the channel status */
/* Update the Adapter Status to be BUSY */
-- Also why are we checking for above statuses?
if XDP_ADAPTER_CORE_DB.Verify_Adapter (l_ChannelName) then
l_Result := 'CONTINUE';
XDP_ADAPTER_CORE_DB.Update_Adapter_Status (
p_ChannelName => l_ChannelName,
p_Status => XDP_ADAPTER.pv_statusInUse,
p_WFItemType => VerifyChannel.itemtype,
p_WFItemKey => VerifyChannel.itemkey);
cursor c_UpdateFAStatus(FAInstanceID number)is
select 'Update FA Status'
from XDP_FA_RUNTIME_LIST
where FA_INSTANCE_ID = FAInstanceID
for update of STATUS_CODE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN;
select adapter_status into l_AdapterStatus
from XDP_ADAPTER_REG
where channel_name = l_ChannelName;
XDP_ADAPTER_CORE_DB.Update_Adapter_Status (
p_ChannelName => l_ChannelName,
p_Status => XDP_ADAPTER.pv_statusError,
p_ErrorMsg => 'XDP_DYNAMIC_PROC_EXEC_ERROR',
p_ErrorMsgParams => x_parameters
);
XDP_ADAPTER_CORE_DB.Update_Adapter_Status (
p_ChannelName => l_ChannelName,
p_Status => XDP_ADAPTER.pv_statusError,
p_ErrorMsg => 'XDP_INTERNAL_CHANNEL_ERROR',
p_ErrorMsgParams => x_parameters
);
XDP_ADAPTER_CORE_DB.Update_Adapter_Status (
p_ChannelName => l_ChannelName,
p_Status => XDP_ADAPTER.pv_statusDisconnected
);
XDP_ADAPTER_CORE_DB.Update_Adapter_Status (
p_ChannelName => l_ChannelName,
p_Status => XDP_ADAPTER.pv_statusError,
p_ErrorMsg => 'XDP_UNHANDLED_FP_EXEC_ERROR',
p_ErrorMsgParams => x_parameters
);
FOR v_UpdateFAStatus in c_UpdateFAStatus(l_FAInstanceID) LOOP
update XDP_FA_RUNTIME_LIST set STATUS_CODE = l_Result,
LAST_UPDATE_DATE = sysdate, LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where current of c_UpdateFAStatus;
update XDP_FA_RUNTIME_LIST
set STATUS_CODE = 'ABORTED'
where FA_INSTANCE_ID = l_FAInstanceID;
select to_char(XDP_WF_ITEMKEY_S.NEXTVAL) into l_tempKey from dual;
select to_char(XDP_WF_ITEMKEY_S.NEXTVAL) into l_tempKey from dual;
select FA_INSTANCE_ID, FULFILLMENT_ACTION_ID, PRIORITY
from XDP_FA_RUNTIME_LIST
where WORKITEM_INSTANCE_ID = WIInstanceID
and STATUS_CODE = 'STANDBY'
and (PROVISIONING_SEQUENCE IS NULL or PROVISIONING_SEQUENCE = 0) ;
select FA_INSTANCE_ID, FULFILLMENT_ACTION_ID, PRIORITY
from XDP_FA_RUNTIME_LIST
where WORKITEM_INSTANCE_ID = WIInstanceID
and STATUS_CODE = 'STANDBY'
and PROVISIONING_SEQUENCE > 0;
select to_char(XDP_WF_ITEMKEY_S.NEXTVAL) into l_tempKey from dual;
select to_char(XDP_WF_ITEMKEY_S.NEXTVAL) into l_tempKey from dual;
select FULFILLMENT_ACTION_ID, FA_INSTANCE_ID, PROVISIONING_SEQUENCE, PRIORITY
from XDP_FA_RUNTIME_LIST
where WORKITEM_INSTANCE_ID = wi_id
and PROVISIONING_SEQUENCE = (
select MIN(PROVISIONING_SEQUENCE)
from XDP_FA_RUNTIME_LIST
where WORKITEM_INSTANCE_ID = wi_id
and PROVISIONING_SEQUENCE > prov_seq);
/* Update the XDP_FA_RUNTIME_LIST table with the User defined Workitem Item Type and Item Key */
update XDP_FA_RUNTIME_LIST
set WF_ITEM_TYPE = t_ChildTypes(l_Counter),
WF_ITEM_KEY = t_childKeys(l_Counter),
LAST_UPDATE_DATE = sysdate, LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where FA_INSTANCE_ID = l_FAInstanceID;
select ROWID
from XDP_ADAPTER_JOB_QUEUE
where FE_ID = p_FeID
and channel_usage_code = p_ChannelUsageCode
and SYSTEM_HOLD = 'N'
order by QUEUED_ON ASC;
e_UpdateAdapterRegException Exception;
select fe_id
from xdp_fes
where fulfillment_element_name = p_FeName;
select USAGE_CODE
from xdp_adapter_reg
where channel_name = p_ChannelName;
* Then try to obtain a lock on one of these (select for update with no wait)
* When u get a lock process it.
*/
arrsize := 0;
select WF_ITEM_TYPE, WF_ITEM_KEY, ROWID, ORDER_ID, WORKITEM_INSTANCE_ID, FA_INSTANCE_ID
into l_New_itemtype, l_New_itemkey, l_rowID, l_OrderID, l_WIInstanceID, l_FAInstanceID
from XDP_ADAPTER_JOB_QUEUE
where ROWID = idarr(i)
for update NOWAIT;
* If so the FA must actually be deleted from the adapter job queue
*/
if IsFAAborted(l_FAInstanceID) = FALSE then
eligible := TRUE;
delete from XDP_ADAPTER_JOB_QUEUE
where ROWID = l_rowid;
/* By the time the list of FA's was selected into the array. Some other dqer
* had obtained a lock and already done the hand over process
*/
eligible := FALSE;
delete from XDP_ADAPTER_JOB_QUEUE
where ROWID = l_rowid;
XDP_ADAPTER_CORE_DB.Update_Adapter_Status (
p_ChannelName => p_ChannelName,
p_Status => XDP_ADAPTER.pv_statusInUse
);
XDP_ADAPTER_CORE_DB.Update_Adapter_Status (
p_ChannelName => p_ChannelName,
p_Status => XDP_ADAPTER.pv_statusError,
p_ErrorMsg => 'XDP_INTERNAL_CHANNEL_ERROR',
p_ErrorMsgParams => x_parameters
);
XDP_ADAPTER_CORE_DB.Update_Adapter_Status (
p_ChannelName => p_ChannelName,
p_Status => XDP_ADAPTER.pv_statusRunning
);
RAISE e_UpdateAdapterRegException;
when e_UpdateAdapterRegException then
IF c_AdapterJobQueue%ISOPEN THEN
close c_AdapterJobQueue;
select ROWID
from XDP_ADAPTER_JOB_QUEUE
where FE_ID = p_FeID
and channel_usage_code = p_ChannelUsageCode
and SYSTEM_HOLD = 'N'
order by QUEUED_ON ASC;
e_UpdateAdapterRegException Exception;
select fe_id, usage_code
into l_FeID, l_ChannelUsageCode
from XDP_ADAPTER_REG
where channel_name = ChannelName;
* Then try to obtain a lock on one of these (select for update with no wait)
* When u get a lock process it.
*/
arrsize := 0;
select WF_ITEM_TYPE, WF_ITEM_KEY, ROWID, ORDER_ID, WORKITEM_INSTANCE_ID, FA_INSTANCE_ID
into l_New_itemtype, l_New_itemkey, l_rowID, l_OrderID, l_WIInstanceID, l_FAInstanceID
from XDP_ADAPTER_JOB_QUEUE
where ROWID = idarr(i)
for update NOWAIT;
* If so the FA must actually be deleted from the adapter job queue
*/
if IsFAAborted(l_FAInstanceID) = FALSE then
eligible := TRUE;
delete from XDP_ADAPTER_JOB_QUEUE
where ROWID = l_rowid;
/* By the time the list of FA's was selected into the array. Some other dqer
* had obtained a lock and already done the hand over process
*/
eligible := FALSE;
delete from XDP_ADAPTER_JOB_QUEUE
where ROWID = l_rowid;
select FULFILLMENT_ELEMENT_NAME into l_FeName
from XDP_FES
where FE_ID = l_FeID;
XDP_ADAPTER_CORE_DB.Update_Adapter_Status (
p_ChannelName => ChannelName,
p_Status => XDP_ADAPTER.pv_statusInUse
);
XDP_ADAPTER_CORE_DB.Update_Adapter_Status (
p_ChannelName => ChannelName,
p_Status => XDP_ADAPTER.pv_statusError,
p_ErrorMsg => 'XDP_INTERNAL_CHANNEL_ERROR',
p_ErrorMsgParams => x_parameters
);
XDP_ADAPTER_CORE_DB.Update_Adapter_Status (
p_ChannelName => ChannelName,
p_Status => XDP_ADAPTER.pv_statusRunning
);
RAISE e_UpdateAdapterRegException;
when e_UpdateAdapterRegException then
IF c_AdapterJobQueue%ISOPEN THEN
close c_AdapterJobQueue;
select REQUEST_ID, REQUEST_TYPE
from XDP_ADAPTER_ADMIN_REQS
where CHANNEL_NAME = ChannelName and
REQUEST_STATUS = 'PENDING' and
REQUEST_TYPE IN ('SUSPEND', 'DISCONNECT', 'SHUTDOWN') and
REQUEST_DATE = (
select MIN(REQUEST_DATE)
from XDP_ADAPTER_ADMIN_REQS
where CHANNEL_NAME = ChannelName and
REQUEST_STATUS = 'PENDING' and
REQUEST_TYPE IN ('SUSPEND', 'DISCONNECT', 'SHUTDOWN') and
REQUEST_DATE <= SYSDATE );
select XDW.FA_EXEC_MAP_PROC
into MappingProc
from XDP_WORKITEMS XDW, XDP_FULFILL_WORKLIST XFW
where XFW.WORKITEM_INSTANCE_ID = WIInstanceID
and XDW.FA_EXEC_MAP_PROC is not null
and XFW.WORKITEM_ID = XDW.WORKITEM_ID;
select XWF.FULFILLMENT_ACTION_ID, XWF.PROVISIONING_SEQ
from XDP_WI_FA_MAPPING XWF, XDP_FULFILL_WORKLIST XFW
where XFW.WORKITEM_INSTANCE_ID = wi_id
and XFW.WORKITEM_ID = XWF.WORKITEM_ID;
select XFA.FE_ROUTING_PROC
from XDP_FULFILL_ACTIONS XFA, XDP_FA_RUNTIME_LIST XFL
where XFL.FA_INSTANCE_ID = FaID
and XFA.FULFILLMENT_ACTION_ID = XFL.FULFILLMENT_ACTION_ID;
select XFP.FULFILLMENT_PROC
from XDP_FA_FULFILLMENT_PROC XFP, XDP_FA_RUNTIME_LIST XFL, XDP_FE_SW_GEN_LOOKUP XFS
where XFL.FA_INSTANCE_ID = FaID
and XFP.FULFILLMENT_ACTION_ID = XFL.FULFILLMENT_ACTION_ID
and XFP.FE_SW_GEN_LOOKUP_ID = XFS.FE_SW_GEN_LOOKUP_ID
and XFS.FETYPE_ID = FeTypeID
and XFS.SW_GENERIC = FeSWGeneric
and XFS.ADAPTER_TYPE = AdapterType;
select NVL(FE_ID, null)
from XDP_FA_RUNTIME_LIST
where FA_INSTANCE_ID = FaID;
select ROWID, channel_name
from XDP_ADAPTER_REG
where FE_ID = feid
and ADAPTER_STATUS = AdapterStatus
and USAGE_CODE = ChannelUsageCode
order by NVL(SEQ_IN_FE, 10);
select ROWID, channel_name
from XDP_ADAPTER_REG
where FE_ID = feid
and ADAPTER_STATUS = AdapterStatus
and NVL(CONNECT_ON_DEMAND_FLAG, 'N') = CODFlag
and USAGE_CODE = ChannelUsageCode
order by NVL(SEQ_IN_FE, 10);
select status_code ,workitem_instance_id from
xdp_fa_runtime_list
where fa_instance_id = p_FAInstanceID
for update;
select status_code from
XDP_FULFILL_WORKLIST
where workitem_instance_id = p_WIInstanceID
for update;
-- Updated sacsharm
-- Adapter business object API should not be used as it locks/releases lock
-- does Handover, etc.
if (XDP_ADAPTER_CORE_DB.Is_Adapter_Implemented (p_ChannelName=>p_Channel_Name)) then
XDP_ADAPTER_CORE.ProcessControlCommand(p_ChannelName => p_Channel_Name,
p_Operation => XDP_ADAPTER.pv_opConnect,
p_Status => l_Status1,
p_ErrorMessage => x_error_description);
XDP_ADAPTER_CORE_DB.Update_Adapter_Status (
p_ChannelName => p_Channel_Name,
-- p_Status => XDP_ADAPTER.pv_statusDisconnected,
p_Status => XDP_ADAPTER.pv_statusError,
p_ErrorMsg => 'INTERNAL_ERROR',
p_ErrorMsgParams => x_parameters
);
XDP_ADAPTER_CORE_DB.Update_Adapter_Status (
p_ChannelName => p_Channel_Name,
p_Status => XDP_ADAPTER.pv_statusInUse
);
select to_char(XDP_WF_ITEMKEY_S.NEXTVAL) into l_item_key from dual;
PROCEDURE UPDATE_FA_STATUS(p_fa_instance_id IN NUMBER,
p_status_code IN VARCHAR2,
p_itemtype IN VARCHAR2,
p_itemkey IN VARCHAR2 ) IS
PRAGMA AUTONOMOUS_TRANSACTION;
UPDATE xdp_fa_runtime_list
SET status_code = p_status_code ,
wf_item_type = p_itemtype ,
wf_item_key = p_itemkey ,
last_update_date = sysdate ,
last_updated_by = fnd_global.user_id ,
last_update_login = fnd_global.login_id
WHERE fa_instance_id = p_fa_instance_id ;
x_Progress := 'XDPCORE_FA.UPDATE_FA_STATUS. Unhandled Exception: ' || SUBSTR(SQLERRM, 1,1500);
wf_core.context('XDPCORE_FA', 'UPDATE_FA_STATUS', p_itemtype, p_itemkey, null, x_Progress);
END UPDATE_FA_STATUS ;
select FA_INSTANCE_ID, FULFILLMENT_ACTION_ID, PRIORITY
from XDP_FA_RUNTIME_LIST
where WORKITEM_INSTANCE_ID = WIInstanceID
and STATUS_CODE = 'STANDBY'
and (PROVISIONING_SEQUENCE IS NULL or PROVISIONING_SEQUENCE = 0) ;
select FA_INSTANCE_ID, FULFILLMENT_ACTION_ID, PRIORITY
from XDP_FA_RUNTIME_LIST
where WORKITEM_INSTANCE_ID = WIInstanceID
and STATUS_CODE = 'STANDBY'
and PROVISIONING_SEQUENCE > 0;
select FA_INSTANCE_ID, FULFILLMENT_ACTION_ID, PRIORITY
from XDP_FA_RUNTIME_LIST
where WORKITEM_INSTANCE_ID = WIInstanceID
and STATUS_CODE = 'STANDBY'
and (PROVISIONING_SEQUENCE IS null or PROVISIONING_SEQUENCE = 0) ;
/* Update the XDP_FA_RUNTIME_LIST table with the User defined Workitem Item Type and Item Key */
update XDP_FA_RUNTIME_LIST
set WF_ITEM_TYPE = l_FAItemType,
WF_ITEM_KEY = l_FAItemKey,
LAST_UPDATE_DATE = sysdate, LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where FA_INSTANCE_ID = l_FAInstanceID;
SELECT display_name into l_display_name
FROM xdp_fulfill_actions_vl fas
WHERE fas.fulfillment_action_id IN ( SELECT lst.fulfillment_action_id
FROM xdp_fa_runtime_list lst
WHERE fa_instance_id = p_FAInstanceID );
SELECT error_count
FROM xdp_error_count
WHERE object_key = p_fp_name
AND object_type = XDP_UTILITIES.g_fp_object_type FOR UPDATE;
SELECT error_count
FROM xdp_error_count
WHERE object_key = p_fp_name
AND object_type = XDP_UTILITIES.g_fp_object_type FOR UPDATE;
UPDATE xdp_error_count
SET error_count = lv_error_count,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.USER_ID
WHERE CURRENT OF cur_error_count;
INSERT INTO XDP_ERROR_COUNT(
object_type,
object_key,
error_count,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES (
XDP_UTILITIES.g_fp_object_type,
p_fp_name,
1,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID);
SELECT error_count
FROM xdp_error_count
WHERE object_key = p_fp_name
AND object_type = XDP_UTILITIES.g_fp_object_type FOR UPDATE;
UPDATE xdp_adapter_job_queue
SET system_hold = 'N',
last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.USER_ID
WHERE fa_instance_id in ( SELECT fa_instance_id
FROM xdp_fa_runtime_list
WHERE proc_name = p_fp_name
AND status_code = XDP_UTILITIES.g_system_hold );
UPDATE xdp_fa_runtime_list
SET status_code = XDP_UTILITIES.g_wait_for_resource,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.USER_ID
WHERE proc_name = p_fp_name
AND status_code = XDP_UTILITIES.g_system_hold;
UPDATE xdp_error_count
SET error_count = 0,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.USER_ID
WHERE CURRENT OF cur_error_count;
SELECT fe_id
FROM XDP_FES
WHERE fulfillment_element_name = p_FEName;
UPDATE xdp_fa_runtime_list
SET fe_id = lv_rec.fe_id
WHERE fa_instance_id = p_faInstanceID;