[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_DELETE_ACTION_TYPE constant number := 0;
select req_def.object_definition_id
,req_def.object_type_code
,req_def.prev_approval_status_code
from fem_wf_requests req
,fem_wf_req_object_defs req_def
,fem_object_definition_b def
where req.wf_request_id = p_request_id
and req.wf_request_item_code = p_request_item_code
and req.wf_request_type_code = p_request_type_code
and req_def.wf_request_id = req.wf_request_id
and def.object_definition_id = req_def.object_definition_id
and def.old_approved_copy_flag = G_CURRENT_COPY;
select def.object_definition_id
,def.display_name as object_definition_name
,def.old_approved_copy_obj_def_id
,def.object_id
,obj.object_name
,obj.object_type_code
,typ.object_type_name
,obj.folder_id
,fol.folder_name
,typ.view_only_oa_function_name
from fem_wf_requests req
,fem_wf_req_object_defs req_def
,fem_object_definition_vl def
,fem_object_catalog_vl obj
,fem_object_types_vl typ
,fem_folders_vl fol
where req.wf_request_id = p_request_id
and req.wf_request_item_code = p_request_item_code
and req.wf_request_type_code = p_request_type_code
and req_def.wf_request_id = req.wf_request_id
and def.object_definition_id = req_def.object_definition_id
and def.old_approved_copy_flag = G_CURRENT_COPY
and obj.object_id = def.object_id
and typ.object_type_code = obj.object_type_code
and fol.folder_id = obj.folder_id;
select def.object_definition_id
,def.display_name as object_definition_name
,def.object_id
,obj.object_name
,fol.folder_name
,def.effective_start_date
,def.effective_end_date
from fem_object_definition_vl def
,fem_object_catalog_vl obj
,fem_folders_vl fol
where def.object_id = obj.object_id
and fol.folder_id = obj.folder_id
and def.old_approved_copy_flag = G_CURRENT_COPY
and def.object_id = p_obj_id
and def.object_definition_id <> nvl(p_exclude_obj_def_id, -1)
and (
(
(def.effective_start_date <= p_effective_start_date)
and
(def.effective_end_date >= p_effective_start_date)
)
or
(
(def.effective_start_date <= p_effective_end_date)
and
(def.effective_end_date >= p_effective_end_date)
)
or
(
(def.effective_start_date >= p_effective_start_date)
and
(def.effective_end_date <= p_effective_end_date)
)
);
PROCEDURE DeleteObjectDefinitionInternal(
p_object_type_code in varchar2
,p_obj_def_id in number
);
PROCEDURE DeleteObjectDetailsInternal(
p_object_type_code in varchar2
,p_obj_id in number
);
PROCEDURE DeleteObjectRec (
p_obj_id in number
);
PROCEDURE DeleteObjectDefinitionRec(
p_obj_def_id in number
);
PROCEDURE DeleteObjectDependencyRecs(
p_obj_def_id in number
);
PROCEDURE DeleteVtObjDefAttrRec(
p_obj_def_id in number
);
PROCEDURE DeleteObjectDefinition (
p_api_version in number := 1.0
,p_init_msg_list in varchar2 := FND_API.G_FALSE
,p_commit in varchar2 := FND_API.G_FALSE
,x_return_status out nocopy varchar2
,x_msg_count out nocopy number
,x_msg_data out nocopy varchar2
,p_object_type_code in varchar2
,p_obj_def_id in number
,p_process_type in number := G_DEFAULT
)
--------------------------------------------------------------------------------
IS
l_delete_request_id number;
DeleteObjectDefinition (
p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_object_type_code => p_object_type_code
,p_obj_def_id => p_obj_def_id
,p_process_type => p_process_type
,x_delete_request_id => l_delete_request_id
);
END DeleteObjectDefinition;
PROCEDURE DeleteObjectDefinition (
p_api_version in number := 1.0
,p_init_msg_list in varchar2 := FND_API.G_FALSE
,p_commit in varchar2 := FND_API.G_FALSE
,x_return_status out nocopy varchar2
,x_msg_count out nocopy number
,x_msg_data out nocopy varchar2
,p_object_type_code in varchar2
,p_obj_def_id in number
,p_process_type in number := G_DEFAULT
,x_delete_request_id out nocopy number
)
--------------------------------------------------------------------------------
IS
l_api_name constant varchar2(30) := 'DeleteObjectDefinition';
l_can_delete varchar2(1);
savepoint DeleteObjectDefinition_PVT;
x_delete_request_id := null;
FEM_PL_PKG.can_delete_object (
p_api_version => 1.0
,p_init_msg_list => FND_API.G_FALSE
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_object_id => l_obj_id
,p_process_type => p_process_type
,x_can_delete_obj => l_can_delete
);
FEM_PL_PKG.can_delete_object_def (
p_api_version => 1.0
,p_init_msg_list => FND_API.G_FALSE
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_object_definition_id => p_obj_def_id
,p_process_type => p_process_type
,x_can_delete_obj_def => l_can_delete
);
,p_approval_status_code => G_SUBMIT_DELETE_STATUS
);
x_delete_request_id :=
FEM_FEMAPPR_ITEM_TYPE.CreateWfRequestRow (
p_request_item_code => FEM_FEMAPPR_ITEM_TYPE.G_BUSINESS_RULE_ITEM
,p_request_type_code => FEM_FEMAPPR_ITEM_TYPE.G_DELETE_TYPE
);
,p_msg_text => 'After FEM_FEMAPPR_ITEM_TYPE.CreateWfRequestRow x_delete_request_id:' || x_delete_request_id);
p_wf_request_id => x_delete_request_id
,p_obj_def_id => p_obj_def_id
,p_object_type_code => p_object_type_code
,p_prev_approval_status_code => l_approval_status_code
);
p_event_name => FEM_FEMAPPR_ITEM_TYPE.G_BUSINESS_RULE_EVENT_DELETE
,p_request_id => x_delete_request_id
,p_user_id => FND_GLOBAL.User_Id
,p_user_name => FND_GLOBAL.User_Name
,p_responsibility_id => FND_GLOBAL.Resp_Id
,p_application_id => FND_GLOBAL.Resp_Appl_Id
,p_org_id => FND_GLOBAL.Org_Id
);
DeleteObjectDefinitionInternal(
p_object_type_code => p_object_type_code
,p_obj_def_id => p_obj_def_id
);
,p_msg_text => 'After DeleteObjectDefinitionInternal');
,p_msg_text => 'not null l_old_approved_obj_def_id Before DeleteObjectDefinitionInternal');
DeleteObjectDefinitionInternal(
p_object_type_code => p_object_type_code
,p_obj_def_id => l_old_approved_obj_def_id
);
,p_msg_text => 'not null l_old_approved_obj_def_id After DeleteObjectDefinitionInternal');
,p_msg_text => 'IsObjectEmpty 1 Before DeleteObjectDefinitionInternal, l_obj_id:' || l_obj_id);
DeleteObjectDetailsInternal (
p_object_type_code => p_object_type_code
,p_obj_id => l_obj_id
);
,p_msg_text => 'IsObjectEmpty 1 After DeleteObjectDefinitionInternal, l_obj_id:' || l_obj_id);
DeleteObjectRec(l_obj_id);
,p_msg_text => 'After DeleteObjectRec');
ROLLBACK TO DeleteObjectDefinition_PVT;
ROLLBACK TO DeleteObjectDefinition_PVT;
ROLLBACK TO DeleteObjectDefinition_PVT;
END DeleteObjectDefinition;
DeleteObjectDefinitionInternal (
p_object_type_code => p_object_type_code,
p_obj_def_id => l_old_approved_obj_def_id
);
DeleteObjectDefinitionInternal(
p_object_type_code => p_object_type_code
,p_obj_def_id => p_obj_def_id
);
PROCEDURE DeleteObject (
p_api_version in number
,p_init_msg_list in varchar2 := FND_API.G_FALSE
,p_commit in varchar2 := FND_API.G_FALSE
,x_return_status out nocopy varchar2
,x_msg_count out nocopy number
,x_msg_data out nocopy varchar2
,p_object_type_code in varchar2
,p_obj_id in number
,p_process_type in number := G_DEFAULT
)
--------------------------------------------------------------------------------
IS
-----------------------
-- Declare constants --
-----------------------
l_api_name constant varchar2(30) := 'DeleteObject';
l_can_delete_obj varchar2(1);
select object_definition_id
from fem_object_definition_b
where object_id = p_obj_id
and old_approved_copy_flag = G_CURRENT_COPY;
savepoint DeleteObject_PVT;
FEM_PL_PKG.can_delete_object (
p_api_version => 1.0
,p_init_msg_list => FND_API.G_FALSE
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_object_id => p_obj_id
,p_process_type => p_process_type
,x_can_delete_obj => l_can_delete_obj
);
,p_msg_text => 'After call FEM_PL_PKG.can_delete_object'
);
,p_msg_text => 'Before call DeleteObjectDefinition'
);
DeleteObjectDefinition (
p_object_type_code => p_object_type_code
,p_obj_def_id => l_obj_defs_rec.object_definition_id
,p_init_msg_list => FND_API.G_FALSE
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_process_type => p_process_type
);
,p_msg_text => 'After call DeleteObjectDefinition'
);
,p_msg_text => 'Before call DeleteObjectDetailsInternal'
);
DeleteObjectDetailsInternal (
p_object_type_code => p_object_type_code
,p_obj_id => p_obj_id
);
,p_msg_text => 'After call DeleteObjectDetailsInternal'
);
DeleteObjectRec(p_obj_id);
,p_msg_text => 'After call DeleteObjectRec'
);
ROLLBACK TO DeleteObject_PVT;
ROLLBACK TO DeleteObject_PVT;
ROLLBACK TO DeleteObject_PVT;
END DeleteObject;
DeleteObjectDefinition (
p_object_type_code => p_object_type_code
,p_obj_def_id => x_target_obj_def_id
,p_init_msg_list => FND_API.G_FALSE
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_process_type => G_WORKFLOW
);
select obj.object_id
,typ.object_type_name
, f.folder_name
, obj.object_name
from fem_object_catalog_vl obj
,fem_folders_vl f
,fem_object_types_vl typ
where upper(obj.object_name) = upper(p_obj_name)
and obj.object_id <> nvl(p_obj_id,-1)
and f.folder_id = obj.folder_id
and typ.object_type_code = obj.object_type_code;
select def.object_definition_id as obj_def_id
,def.display_name as obj_def_name
,def.object_id
,obj.object_name
from fem_object_definition_vl def
,fem_object_catalog_vl obj
where upper(def.display_name) = upper(p_obj_def_name)
and def.object_id = p_obj_id
and def.old_approved_copy_flag = G_CURRENT_COPY
and def.object_definition_id <> nvl(p_obj_def_id,-1)
and obj.object_id = def.object_id;
select fem_object_id_seq.NEXTVAL
into l_obj_id
from dual;
select fem_object_definition_id_seq.NEXTVAL
into l_obj_def_id
from dual;
select rs_type.isetup_import_export_flag
into l_isetup_import_export_flag
from fem_rule_sets rs
,fem_object_types_b rs_type
where rs.rule_set_obj_def_id = p_ruleset_obj_def_id
and rs_type.object_type_code = rs.rule_set_object_type_code;
select object_id
into l_obj_id
from fem_object_definition_b
where object_definition_id = p_obj_def_id;
select old_approved_copy_obj_def_id
into l_old_approved_obj_def_id
from fem_object_definition_b
where old_approved_copy_flag = G_CURRENT_COPY
and object_definition_id = p_obj_def_id;
elsif (l_request_type_code = G_DELETE_TYPE) then
l_approval_status_code := G_SUBMIT_DELETE_STATUS;
elsif (l_request_type_code = G_DELETE_TYPE) then
for req_obj_def_rec in req_obj_defs_cur(
l_request_id
,l_request_item_code
,l_request_type_code) loop
DeleteObjectDefinition(
p_object_type_code => req_obj_def_rec.object_type_code
,p_obj_def_id => req_obj_def_rec.object_definition_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_process_type => G_WORKFLOW
);
if (l_request_type_code in (G_APPROVAL_TYPE, G_DELETE_TYPE)) then
for req_obj_def_rec in req_obj_defs_cur(
l_request_id
,l_request_item_code
,l_request_type_code) loop
-- Set to the previous approval status
SetApprovalStatus(
p_obj_def_id => req_obj_def_rec.object_definition_id
,p_approval_status_code => req_obj_def_rec.prev_approval_status_code
);
if (l_request_type_code in (G_APPROVAL_TYPE, G_DELETE_TYPE)) then
l_request_item_code := WF_ENGINE.GetItemAttrText(
p_item_type, p_item_key, G_REQUEST_ITEM_CODE);
if (l_request_type_code in (G_APPROVAL_TYPE, G_DELETE_TYPE)) then
for info_obj_def_rec in info_obj_defs_cur(
l_request_id
,l_request_item_code
,l_request_type_code) loop
l_obj_def_id := info_obj_def_rec.object_definition_id;
elsif (l_request_type_code = G_DELETE_TYPE) then
null;
delete from fem_wf_req_object_defs
where wf_request_id = l_request_id;
update fem_object_definition_b
set approval_status_code =
decode(p_approval_status_code
,FND_API.G_MISS_CHAR,approval_status_code
,p_approval_status_code)
,approved_by =
decode(p_approver_user_id
,FND_API.G_MISS_NUM,approved_by
,p_approver_user_id)
,approval_date =
decode(p_approval_date
,FND_API.G_MISS_DATE,approval_date
,p_approval_date)
,last_updated_by = FND_GLOBAL.user_id
,last_update_date = sysdate
,last_update_login = FND_GLOBAL.login_id
where object_definition_id = p_obj_def_id;
select approval_status_code
into l_approval_status_code
from fem_object_definition_b
where object_definition_id = p_obj_def_id;
select object_plsql_pkg_name
into l_package_name
from fem_object_types
where object_type_code = p_object_type_code;
PROCEDURE DeleteObjectDefinitionInternal(
p_object_type_code in varchar2
,p_obj_def_id in number
)
--------------------------------------------------------------------------------
IS
l_package_name t_plsql_pkg_name%TYPE;
l_api_name constant varchar2(30) := 'DeleteObjectDefinitionInternal';
DeleteObjectDependencyRecs(p_obj_def_id);
l_package_name||'.DeleteObjectDefinition(:p_obj_def_id);'||
DeleteVtObjDefAttrRec(p_obj_def_id);
DeleteObjectDefinitionRec(p_obj_def_id);
END DeleteObjectDefinitionInternal;
PROCEDURE DeleteObjectDetailsInternal(
p_object_type_code in varchar2
,p_obj_id in number
)
--------------------------------------------------------------------------------
IS
l_package_name t_plsql_pkg_name%TYPE;
,p_module => G_BLOCK||'.'|| 'DeleteObjectDetailsInternal'
,p_msg_text => 'BEGIN, p_object_type_code:' || p_object_type_code || ' p_obj_id:' || p_obj_id
);
,p_module => G_BLOCK||'.'|| 'DeleteObjectDetailsInternal'
,p_msg_text => 'l_package_name:' || l_package_name
);
l_package_name||'.DeleteObjectDetails(:p_obj_id);'||
,p_module => G_BLOCK||'.'|| 'DeleteObjectDetailsInternal'
,p_msg_text => 'l_dynamic_query:' || l_dynamic_query
);
l_package_name||'.DeleteTuningOptionDetails(:p_obj_id);'||
END DeleteObjectDetailsInternal;
select p_target_obj_id
,object_type_code
,folder_id
,local_vs_combo_id
,object_access_code
,object_origin_code
,p_target_obj_name
,decode(p_target_obj_desc,FND_API.G_MISS_CHAR,description,p_target_obj_desc)
,nvl(p_creation_date,creation_date)
,nvl(p_created_by,created_by)
,sysdate
,FND_GLOBAL.user_id
,FND_GLOBAL.login_id
,object_version_number
into l_obj_vl_rec.object_id
,l_obj_vl_rec.object_type_code
,l_obj_vl_rec.folder_id
,l_obj_vl_rec.local_vs_combo_id
,l_obj_vl_rec.object_access_code
,l_obj_vl_rec.object_origin_code
,l_obj_vl_rec.object_name
,l_obj_vl_rec.description
,l_obj_vl_rec.creation_date
,l_obj_vl_rec.created_by
,l_obj_vl_rec.last_update_date
,l_obj_vl_rec.last_updated_by
,l_obj_vl_rec.last_update_login
,l_obj_vl_rec.object_version_number
from fem_object_catalog_vl
where object_id = p_source_obj_id;
FEM_OBJECT_CATALOG_PKG.Insert_Row (
x_rowid => l_rowid
,x_object_id => l_obj_vl_rec.object_id
,x_object_type_code => l_obj_vl_rec.object_type_code
,x_folder_id => l_obj_vl_rec.folder_id
,x_local_vs_combo_id => l_obj_vl_rec.local_vs_combo_id
,x_object_access_code => l_obj_vl_rec.object_access_code
,x_object_origin_code => l_obj_vl_rec.object_origin_code
,x_object_name => l_obj_vl_rec.object_name
,x_description => l_obj_vl_rec.description
,x_creation_date => l_obj_vl_rec.creation_date
,x_created_by => l_obj_vl_rec.created_by
,x_last_update_date => l_obj_vl_rec.last_update_date
,x_last_updated_by => l_obj_vl_rec.last_updated_by
,x_last_update_login => l_obj_vl_rec.last_update_login
,x_object_version_number => l_obj_vl_rec.object_version_number
);
select p_target_obj_def_id
,decode(p_target_obj_id,FND_API.G_MISS_NUM,object_id,p_target_obj_id)
,decode(p_target_start_date,FND_API.G_MISS_DATE,effective_start_date,p_target_start_date)
,decode(p_target_end_date,FND_API.G_MISS_DATE,effective_end_date,p_target_end_date)
,object_origin_code
,approval_status_code
,approved_by
,approval_date
,p_target_copy_flag
,decode(p_target_copy_obj_def_id,FND_API.G_MISS_NUM,old_approved_copy_obj_def_id,p_target_copy_obj_def_id)
,decode(p_target_obj_def_name,FND_API.G_MISS_CHAR,display_name,p_target_obj_def_name)
,decode(p_target_obj_def_desc,FND_API.G_MISS_CHAR,description,p_target_obj_def_desc)
,nvl(p_creation_date,creation_date)
,nvl(p_created_by,created_by)
,sysdate
,FND_GLOBAL.user_id
,FND_GLOBAL.login_id
,object_version_number
into l_obj_def_vl_rec.object_definition_id
,l_obj_def_vl_rec.object_id
,l_obj_def_vl_rec.effective_start_date
,l_obj_def_vl_rec.effective_end_date
,l_obj_def_vl_rec.object_origin_code
,l_obj_def_vl_rec.approval_status_code
,l_obj_def_vl_rec.approved_by
,l_obj_def_vl_rec.approval_date
,l_obj_def_vl_rec.old_approved_copy_flag
,l_obj_def_vl_rec.old_approved_copy_obj_def_id
,l_obj_def_vl_rec.display_name
,l_obj_def_vl_rec.description
,l_obj_def_vl_rec.creation_date
,l_obj_def_vl_rec.created_by
,l_obj_def_vl_rec.last_update_date
,l_obj_def_vl_rec.last_updated_by
,l_obj_def_vl_rec.last_update_login
,l_obj_def_vl_rec.object_version_number
from fem_object_definition_vl
where object_definition_id = p_source_obj_def_id;
FEM_OBJECT_DEFINITION_PKG.Insert_Row (
x_rowid => l_rowid
,x_object_definition_id => l_obj_def_vl_rec.object_definition_id
,x_object_id => l_obj_def_vl_rec.object_id
,x_effective_start_date => l_obj_def_vl_rec.effective_start_date
,x_effective_end_date => l_obj_def_vl_rec.effective_end_date
,x_object_origin_code => l_obj_def_vl_rec.object_origin_code
,x_approval_status_code => l_obj_def_vl_rec.approval_status_code
,x_approved_by => l_obj_def_vl_rec.approved_by
,x_approval_date => l_obj_def_vl_rec.approval_date
,x_old_approved_copy_flag => l_obj_def_vl_rec.old_approved_copy_flag
,x_old_approved_copy_obj_def_id => l_obj_def_vl_rec.old_approved_copy_obj_def_id
,x_display_name => l_obj_def_vl_rec.display_name
,x_description => l_obj_def_vl_rec.description
,x_creation_date => l_obj_def_vl_rec.creation_date
,x_created_by => l_obj_def_vl_rec.created_by
,x_last_update_date => l_obj_def_vl_rec.last_update_date
,x_last_updated_by => l_obj_def_vl_rec.last_updated_by
,x_last_update_login => l_obj_def_vl_rec.last_update_login
,x_object_version_number => l_obj_def_vl_rec.object_version_number
);
insert into fem_object_dependencies (
object_definition_id
,required_object_id
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
,object_version_number
) select
p_target_obj_def_id
,required_object_id
,nvl(p_created_by,created_by)
,nvl(p_creation_date,creation_date)
,FND_GLOBAL.user_id
,sysdate
,FND_GLOBAL.login_id
,object_version_number
from fem_object_dependencies
where object_definition_id = p_source_obj_def_id;
insert into fem_vt_obj_def_attribs (
object_definition_id
,source_enabled_flg
,driver_enabled_flg
,trace_contribution_enabled_flg
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
,object_version_number
) select
p_target_obj_def_id
,source_enabled_flg
,driver_enabled_flg
,trace_contribution_enabled_flg
,nvl(p_created_by,created_by)
,nvl(p_creation_date,creation_date)
,FND_GLOBAL.user_id
,sysdate
,FND_GLOBAL.login_id
,object_version_number
from fem_vt_obj_def_attribs
where object_definition_id = p_source_obj_def_id;
PROCEDURE DeleteObjectRec(
p_obj_id in number
)
--------------------------------------------------------------------------------
IS
l_count number;
FEM_OBJECT_CATALOG_PKG.Delete_Row (
x_object_id => p_obj_id
);
END DeleteObjectRec;
PROCEDURE DeleteObjectDefinitionRec(
p_obj_def_id in number
)
--------------------------------------------------------------------------------
IS
BEGIN
FEM_OBJECT_DEFINITION_PKG.Delete_Row (
x_object_definition_id => p_obj_def_id
);
END DeleteObjectDefinitionRec;
PROCEDURE DeleteObjectDependencyRecs(
p_obj_def_id in number
)
--------------------------------------------------------------------------------
IS
BEGIN
delete from fem_object_dependencies
where object_definition_id = p_obj_def_id;
END DeleteObjectDependencyRecs;
PROCEDURE DeleteVtObjDefAttrRec(
p_obj_def_id in number
)
--------------------------------------------------------------------------------
IS
BEGIN
delete from fem_vt_obj_def_attribs
where object_definition_id = p_obj_def_id;
END DeleteVtObjDefAttrRec;
update fem_object_definition_b
set old_approved_copy_obj_def_id = p_old_approved_obj_def_id
where old_approved_copy_flag = G_CURRENT_COPY
and object_definition_id = p_obj_def_id;
select count(object_definition_id)
into l_count
from fem_object_definition_b
where object_definition_id = p_obj_def_id;
select count(object_definition_id)
into l_count
from fem_object_definition_b
where old_approved_copy_flag = G_CURRENT_COPY
and object_id = p_obj_id
and object_definition_id <> nvl(p_exclude_obj_def_id, -1);
insert into fem_wf_req_object_defs (
wf_request_id
,object_definition_id
,prev_approval_status_code
,object_type_code
,creation_date
,created_by
,last_updated_by
,last_update_date
,last_update_login
,object_version_number
) values (
p_wf_request_id
,p_obj_def_id
,p_prev_approval_status_code
,p_object_type_code
,sysdate
,FND_GLOBAL.User_Id
,FND_GLOBAL.User_Id
,sysdate
,FND_GLOBAL.Login_Id
,1
);
select count(*)
into l_count
from fem_wf_requests req
,fem_wf_req_object_defs req_def
,fem_object_definition_b def
,fem_object_catalog_b obj
,fem_object_types typ
where req.wf_request_id = p_request_id
and req.wf_request_item_code = p_request_item_code
and req.wf_request_type_code = p_request_type_code
and req_def.wf_request_id = req.wf_request_id
and def.object_definition_id = req_def.object_definition_id
and def.old_approved_copy_flag = G_CURRENT_COPY
and obj.object_id = def.object_id
and typ.object_type_code = obj.object_type_code
and typ.workflow_enabled_flag = 'Y';
select effective_start_date, effective_end_date
into x_effective_start_date, x_effective_end_date
from fem_object_definition_b
where object_definition_id = p_obj_def_id;