APPS.WIP_EAM_WRAPPROVAL_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 61

   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 ;

Line: 71

   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;

Line: 81

	select   notes
	from   WIP_EAM_WORK_REQ_NOTES wrn
	where  work_request_id  = p_work_request_id
	order by work_request_note_id;

Line: 94

  	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;

Line: 98

  	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;

Line: 147

  select wip_Eam_wrapproval_s.nextval
  into itemkey
  from dual;

Line: 159

  select  cii.instance_description
    into  l_asset_description
    from  csi_item_instances cii
    where cii.instance_id = l_maintenance_object_id;

Line: 174

  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;

Line: 194

  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' ;

Line: 210

  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 ;

Line: 227

 		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;

Line: 249

  select location_codes
    into l_location_codes
    from MTL_EAM_LOCATIONS
  where  organization_id = p_organization_id
    and  location_id   =   l_asset_location ;

Line: 265

  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' ;

Line: 281

    we insert the header info  */
  l_stmt_number := 76 ;

Line: 284

 /* 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);

Line: 428

        SELECT user_name
        INTO   l_user_name
        FROM   fnd_user
        WHERE  user_id in (select created_by  FROM wip_eam_work_requests where work_request_id = p_work_request_id);

Line: 462

  update wip_eam_work_requests
  set wf_item_type = itemtype,
  wf_item_key = itemkey
  where work_request_id = p_work_request_id;

Line: 475

                            ' department approvers for the selected ' ||
                          ' [Asset : '  || P_asset_number || '] Assigned Department';

Line: 491

PROCEDURE Update_FROM_ROLE( itemtype  in varchar2,
                      itemkey   in varchar2,
                      actid     in number,
                      funcmode  in varchar2,
                      resultout out NOCOPY varchar2) IS
                      l_approver_name varchar2(100);

Line: 542

 END Update_FROM_ROLE;

Line: 545

/* 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');

Line: 571

  l_last_updated_by       number;

Line: 580

     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 ;

Line: 592

        select wip_eam_work_req_notes_s.nextval
          into l_work_request_note_id
        from dual ;

Line: 604

        select orig_system_id
           into l_last_updated_by
        from wf_roles
        where name = l_role_name ;

Line: 613

        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);

Line: 662

    wf_core.context('EAMWRAP','UPDATE_STATUS_AWAIT_WO '||to_char(l_stmt_number),
                     itemtype, itemkey, actid, funcmode);

Line: 666

END Update_Status_Await_Wo;

Line: 669

/* 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');

Line: 696

  l_last_updated_by      number;

Line: 704

     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 ;

Line: 714

        select wip_eam_work_req_notes_s.nextval
          into l_work_request_note_id
        from dual ;

Line: 726

        select orig_system_id
           into l_last_updated_by
        from wf_roles
        where name = l_role_name ;

Line: 736

        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);

Line: 784

    wf_core.context('EAMWRAP','UPDATE_STATUS_REJECTED '|| to_char(l_stmt_number)                    ,itemtype, itemkey, actid, funcmode);

Line: 786

END Update_status_rejected;

Line: 788

/*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');

Line: 826

  l_last_updated_by       number ;

Line: 856

    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 ;

Line: 864

        select wip_eam_work_req_notes_s.nextval
          into l_work_request_note_id
        from dual ;

Line: 869

        select orig_system_id , display_name
           into l_last_updated_by , l_display_name
        from wf_roles
        where name = l_role_name ;

Line: 903

        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);

Line: 927

      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' ;

Line: 978

    wf_core.context('EAMWRAP', 'UPDATE_STATUS_ADD '|| to_char(l_stmt_number),
                    itemtype, itemkey, actid, funcmode);

Line: 982

END Update_status_add;

Line: 1058

	     -- 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);