[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select wfr.NAME
from wf_roles wfr ,
bom_eam_dept_approvers beda
where
beda.dept_id = p_work_request_owning_dept_id
and beda.organization_id = p_organization_id
and beda.responsibility_id = wfr.orig_system_id ;
select beda.responsibility_id,beda.responsibility_application_id,fu.user_name
from bom_eam_dept_approvers beda, fnd_user fu
where
beda.dept_id = p_work_request_owning_dept_id
and beda.organization_id = p_organization_id
and fu.user_id(+) = beda.primary_approver_id;
select instance_number, inventory_item_id into l_instance_number, l_asset_group_id
from csi_item_instances where instance_id = l_maintenance_object_id;
select instance_number, instance_id into l_instance_number, l_maintenance_object_id
from csi_item_instances where serial_number = p_asset_number
and inventory_item_id = p_asset_group;
select wip_Eam_wrapproval_s.nextval
into itemkey
from dual;
select cii.instance_description
into l_asset_description
from csi_item_instances cii
where cii.instance_id = l_maintenance_object_id;
select MSI.concatenated_segments, MSI.description
into l_asset_group_segments, l_asset_group_description
from mtl_system_items_kfv msi, mtl_parameters mp
where msi.organization_id = mp.organization_id
and mp.maint_organization_id = p_organization_id
and msi.inventory_item_id = l_asset_group_id
and rownum = 1;
select ML.meaning
into l_priority_description
from mfg_lookups ML
where ml.lookup_code = p_work_request_priority_id
and ml.lookup_type = 'WIP_EAM_ACTIVITY_PRIORITY' ;
select bd.department_code
into l_department_code
from bom_departments bd
where bd.organization_id = p_organization_id
and bd.department_id = p_work_request_owning_dept_id ;
select area_id
into l_asset_location
from eam_org_maint_defaults
where organization_id = p_organization_id
and object_type = 50
and object_id = l_maintenance_object_id;
select location_codes
into l_location_codes
from MTL_EAM_LOCATIONS
where organization_id = p_organization_id
and location_id = l_asset_location ;
select ml.meaning
into l_work_request_type
from MFG_LOOKUPS ml
where ml.lookup_code = p_work_request_type_id
and ml.lookup_type = 'WIP_EAM_WORK_REQ_TYPE' ;
we insert the header info */
l_stmt_number := 76 ;
select notes into l_notes
from WIP_EAM_WORK_REQ_NOTES wrn1
where work_request_id = p_work_request_id
and work_request_note_id in
(select min(work_request_note_id)
from WIP_EAM_WORK_REQ_NOTES wrn2
where wrn1.work_request_id = wrn2.work_request_id);
update wip_eam_work_requests
set wf_item_type = itemtype,
wf_item_key = itemkey
where work_request_id = p_work_request_id;
' department approvers for the selected ' ||
' [Asset : ' || p_asset_number || '] Assigned Department';
/* Update status to 'Awaiting Work Order' in wip_eam_work_requests */
PROCEDURE Update_Status_Await_Wo( itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2) is
l_work_request_id number :=
wf_engine.GetItemAttrNumber( itemtype => itemtype,
itemkey => itemkey,
aname => 'WORK_REQUEST_ID');
l_last_updated_by number;
Update WIP_EAM_WORK_REQUESTS
set work_request_status_id = 3 ,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = SYSDATE
Where work_request_id = l_work_request_id ;
select wip_eam_work_req_notes_s.nextval
into l_work_request_note_id
from dual ;
select orig_system_id
into l_last_updated_by
from wf_roles
where name = l_role_name ;
Insert into WIP_EAM_WORK_REQ_NOTES
(WORK_REQUEST_NOTE_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE,
CREATED_BY ,
LAST_UPDATE_LOGIN,
WORK_REQUEST_ID ,
NOTES,
WORK_REQUEST_NOTE_TYPE,
NOTIFICATION_ID )
Values
( l_work_request_note_id,
SYSDATE ,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
null,
l_work_request_id,
l_comment ,
2,
null);
wf_core.context('EAMWRAP','UPDATE_STATUS_AWAIT_WO '||to_char(l_stmt_number),
itemtype, itemkey, actid, funcmode);
END Update_Status_Await_Wo;
/* update status to Rejected in wip_eam_work_requests */
PROCEDURE Update_Status_Rejected( itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2) is
l_work_request_id number :=
wf_engine.GetItemAttrNumber( itemtype => itemtype,
itemkey => itemkey,
aname => 'WORK_REQUEST_ID');
l_last_updated_by number;
Update WIP_EAM_WORK_REQUESTS
set work_request_status_id = 5,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = SYSDATE
Where work_request_id = l_work_request_id ;
select wip_eam_work_req_notes_s.nextval
into l_work_request_note_id
from dual ;
select orig_system_id
into l_last_updated_by
from wf_roles
where name = l_role_name ;
Insert into WIP_EAM_WORK_REQ_NOTES
(WORK_REQUEST_NOTE_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE,
CREATED_BY ,
LAST_UPDATE_LOGIN,
WORK_REQUEST_ID ,
NOTES,
WORK_REQUEST_NOTE_TYPE,
NOTIFICATION_ID )
Values
( l_work_request_note_id,
SYSDATE ,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
null,
l_work_request_id,
l_comment ,
2,
null);
wf_core.context('EAMWRAP','UPDATE_STATUS_REJECTED '|| to_char(l_stmt_number) ,itemtype, itemkey, actid, funcmode);
END Update_status_rejected;
/*Update status to 'Additional Information' in wip_eam_work_requests */
PROCEDURE Update_Status_Add( itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2) is
l_work_request_id number :=
wf_engine.GetItemAttrNumber( itemtype => itemtype,
itemkey => itemkey,
aname => 'WORK_REQUEST_ID');
l_last_updated_by number ;
Update WIP_EAM_WORK_REQUESTS
set work_request_status_id = 2,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = SYSDATE
Where work_request_id = l_work_request_id ;
select wip_eam_work_req_notes_s.nextval
into l_work_request_note_id
from dual ;
select orig_system_id , display_name
into l_last_updated_by , l_display_name
from wf_roles
where name = l_role_name ;
Insert into WIP_EAM_WORK_REQ_NOTES
(WORK_REQUEST_NOTE_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE,
CREATED_BY ,
LAST_UPDATE_LOGIN,
WORK_REQUEST_ID ,
NOTES,
WORK_REQUEST_NOTE_TYPE,
NOTIFICATION_ID )
Values
( l_work_request_note_id,
SYSDATE ,
FND_GLOBAL.USER_ID ,
SYSDATE,
FND_GLOBAL.USER_ID,
null,
l_work_request_id,
l_comment ,
2,
null);
select TEXT_VALUE into l_previous_reassign_comment
from wf_item_attribute_values
where ITEM_TYPE = itemtype
and ITEM_KEY = itemkey
and NAME = 'PREVIOUS_REASSIGN_COMMENT' ;
wf_core.context('EAMWRAP', 'UPDATE_STATUS_ADD '|| to_char(l_stmt_number),
itemtype, itemkey, actid, funcmode);
END Update_status_add;
-- select employee information
begin
SELECT nvl(first_name ||' '||last_name,p_user_name)
INTO l_employee_name
FROM PER_PEOPLE_F
WHERE PERSON_ID=
(select employee_id from fnd_user where user_name=p_user_name);