[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select value
INTO l_utl_file_dir
FROM v$parameter
WHERE name = 'utl_file_dir';
SELECT eng_change_actions_s.nextval into l_new_action_id
FROM DUAL;
ENG_CHANGE_ACTIONS_PKG.INSERT_ROW(
X_ROWID => l_rowid, -- rowid (in/out)
X_ACTION_ID => l_new_action_id, -- action_id
X_ORIGINAL_SYSTEM_REFERENCE => null, -- original_system_reference
X_WORKFLOW_ITEM_KEY => p_workflow_item_key, -- workflow_item_key
X_REQUEST_ID => null, -- request_id
X_STATUS_CODE => p_status_code, -- status_code
X_PRIORITY_CODE => p_priority_code, -- priority_code
X_ASSIGNEE_ID => p_assignee_id, -- assignee_id
X_RESPONSE_BY_DATE => p_response_by_date, -- response_by_date
X_PARTY_ID_LIST => p_party_id_list, -- party_id_list
X_PARENT_STATUS_CODE => p_parent_status_code, -- parent_status_code
X_WORKFLOW_ITEM_TYPE => p_workflow_item_type, -- workflow_item_type
X_ROUTE_ID => p_route_id, -- route_id for approval routing
X_PARENT_ACTION_ID => l_parent_action_id, -- parent_action_id
X_ACTION_TYPE => p_action_type, -- action_type
X_OBJECT_NAME => p_object_name, -- object_name
X_OBJECT_ID1 => p_object_id1,
X_OBJECT_ID2 => p_object_id2,
X_OBJECT_ID3 => p_object_id3,
X_OBJECT_ID4 => p_object_id4,
X_OBJECT_ID5 => p_object_id5,
X_DESCRIPTION => p_change_description, -- description
X_PROGRAM_ID => null, -- program_id
X_PROGRAM_APPLICATION_ID => null, -- program_application_id
X_PROGRAM_UPDATE_DATE => null, -- program_update_date
X_CREATION_DATE => p_action_date, -- creation_date
X_CREATED_BY => l_fnd_user_id, -- created_by
X_LAST_UPDATE_DATE => p_action_date, -- last_update_date
X_LAST_UPDATED_BY => l_fnd_user_id, -- last_updated_by
X_LAST_UPDATE_LOGIN => l_fnd_login_id -- last_update_login
-- X_IMPLEMENTATION_REQ_ID => NULL
,X_LOCAL_ORGANIZATION_ID => p_local_organization_id --Bug 4704384
) ;
Write_Debug('Action row inserted successfully... ' );
* to update the workflow-related columns value given the action_id
*/
PROCEDURE Update_Workflow_Info
(
p_api_version IN NUMBER --
,p_init_msg_list IN VARCHAR2 --
,p_commit IN VARCHAR2 --
,p_validation_level IN NUMBER --
,p_debug IN VARCHAR2 --
,p_output_dir IN VARCHAR2 := '/nfs/log/bis_top/utl/plm115dv/log'
,p_debug_filename IN VARCHAR2 --
,x_return_status OUT NOCOPY VARCHAR2 --
,x_msg_count OUT NOCOPY NUMBER --
,x_msg_data OUT NOCOPY VARCHAR2 --
,p_change_action_id IN NUMBER --
,p_workflow_item_type IN VARCHAR2 --
,p_workflow_item_key IN VARCHAR2 --
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Workflow_Info';
SAVEPOINT Update_Workflow_Info_PUB;
Write_Debug('Eng_Change_Action_Util.Update_Workflow_Info log');
UPDATE Eng_Change_Actions
SET workflow_item_type = p_workflow_item_type
,workflow_item_key = p_workflow_item_key
WHERE action_id = p_change_action_id;
Write_Debug('Action row updated successfully... ' );
ROLLBACK TO Update_Workflow_Info_PUB;
ROLLBACK TO Update_Workflow_Info_PUB;
ROLLBACK TO Update_Workflow_Info_PUB;
END Update_Workflow_Info;
* updates the workflow notification with the response comment, and returns
* the corresponding notification id;
* otherwise no update is performed and notification_id is returned as null
*/
PROCEDURE Respond_Notification
(
p_api_version IN NUMBER --
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
,p_commit IN VARCHAR2 := FND_API.G_FALSE --
,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
,p_debug IN VARCHAR2 := FND_API.G_FALSE --
,p_output_dir IN VARCHAR2 := '/nfs/log/bis_top/utl/plm115dv/log' --
,p_debug_filename IN VARCHAR2 := 'eng.chgmt.action.respNotif.log'
,x_return_status OUT NOCOPY VARCHAR2 --
,x_msg_count OUT NOCOPY NUMBER --
,x_msg_data OUT NOCOPY VARCHAR2 --
,p_parent_action_id IN NUMBER --
,p_comment IN VARCHAR2 DEFAULT NULL --
,p_fnd_user_name IN VARCHAR2 --
,x_processed_ntf_id OUT NOCOPY NUMBER --
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Respond_Notification';
SELECT action_type, workflow_item_type, workflow_item_key
INTO l_action_type, l_wf_item_type, l_wf_item_key
FROM eng_change_actions
where action_id = p_parent_action_id;
SELECT ntf.notification_id
FROM wf_item_activity_statuses ias,
wf_notifications ntf
WHERE ias.item_type = p_workflow_item_type
AND ias.item_key = p_workflow_item_key
AND ias.notification_id IS NOT NULL
AND ias.notification_id = ntf.group_id
AND ntf.recipient_role = p_username
AND ntf.status = 'OPEN'
AND ( EXISTS
( SELECT NULL
FROM WF_MESSAGE_ATTRIBUTES WMA
WHERE WMA.MESSAGE_NAME = ntf.MESSAGE_NAME
AND WMA.MESSAGE_TYPE = ntf.MESSAGE_TYPE
AND WMA.MESSAGE_NAME <> 'ENG_STATUS_REVIEW'
AND WMA.SUBTYPE = 'RESPOND' )
);
* Called when a line is deleted
* to delete all the action log entries associated with that line
*/
PROCEDURE Delete_Line_Actions
(
p_api_version IN NUMBER --
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
,p_commit IN VARCHAR2 := FND_API.G_FALSE --
,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
,p_debug IN VARCHAR2 := FND_API.G_FALSE --
,p_output_dir IN VARCHAR2 := '/nfs/log/bis_top/utl/plm115dv/log' --
,p_debug_filename IN VARCHAR2 := 'eng.chgmt.action.line.log'
,x_return_status OUT NOCOPY VARCHAR2 --
,x_msg_count OUT NOCOPY NUMBER --
,x_msg_data OUT NOCOPY VARCHAR2 --
,p_change_id IN NUMBER
,p_change_line_id IN NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Line_Actions';
SELECT ACTION_ID
FROM ENG_CHANGE_ACTIONS
WHERE OBJECT_ID1 = p_change_id
AND OBJECT_ID2 = p_change_line_id
FOR UPDATE;
SAVEPOINT Delete_Line_Actions_PUB;
Write_Debug('Eng_Change_Action_Util.Delete_Line_Actions log');
ENG_CHANGE_ACTIONS_PKG.DELETE_ROW( l_action_id );
Write_Debug('Action rows deleted successfully... ' );
ROLLBACK TO Delete_Line_Actions_PUB;
ROLLBACK TO Delete_Line_Actions_PUB;
ROLLBACK TO Delete_Line_Actions_PUB;
END Delete_Line_Actions;