eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PSP_WF_EFF_PKG SQL Statements

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

Line: 5


   *Function UPDATE_STATUS updates the status of effort		   *
   *report. It is called by STATUS_APPROVED, STATUS_REJECTED,	   *
   *STATUS_CERTIFIED, STATUS_NEW, STATUS_SUPERSEDED.		   *
   *****************************************************************/
  function UPDATE_STATUS(itemkey  in varchar2,
		         funcmode in varchar2,
		         status   in varchar2) return VARCHAR2;

Line: 40


    SELECT effort_report_id, max(version_num)
    FROM   psp_effort_reports
    WHERE  template_id=a_template_id
    GROUP BY effort_report_id;

Line: 46


    SELECT person_id
    FROM   psp_effort_reports
    WHERE  effort_report_id = l_report_id;

Line: 51


    SELECT Begin_date,
           End_date
    FROM   psp_effort_report_templates
    WHERE  template_id = a_template_id;

Line: 57


   SELECT full_name
   FROM   per_all_people_f
   WHERE person_id =l_person_id
   AND   effective_end_date   >= l_begin_date
   AND   effective_start_date <= l_end_date
   ORDER BY effective_start_date desc;

Line: 334


    SELECT assignment.supervisor_id
    FROM   per_assignments_f assignment,
           per_people_f      people
    WHERE  assignment.person_id = l_employee_id
    AND    assignment.supervisor_id = people.person_id
    AND    assignment.assignment_type ='E'  --Added for bug 2624259.
    AND    trunc(SYSDATE) BETWEEN people.effective_start_date AND people.effective_end_date
    AND    assignment.primary_flag = 'Y';

Line: 386


**Function UPDATE_STATUS updates the status of effort		**
**report. It is called by STATUS_APPROVED, STATUS_REJECTED,	**
**STATUS_CERTIFIED, STATUS_NEW, STATUS_SUPERSEDED.		**
******************************************************************/
function UPDATE_STATUS(itemkey  in varchar2,
		       funcmode in varchar2,
		       status   in varchar2)
return VARCHAR2
IS
  l_version_num NUMBER;

Line: 407


    SELECT max(version_num) INTO l_version_num
    FROM psp_effort_reports
--    WHERE effort_report_id = to_number(itemkey);

Line: 413


    UPDATE psp_effort_reports
    SET status_code = status
--    WHERE effort_report_id = to_number(itemkey);

Line: 428


END UPDATE_STATUS;

Line: 431


**PROCEDURE STATUS_APPROVED updates the status of effort	**
**report to 'A'. It is called in the workflow activity UPDATE_EF**
**FORT_REPORT_STATUS_TO_APPROVED.				**
******************************************************************/
procedure STATUS_APPROVED(itemtype in varchar2,
                          itemkey  in varchar2,
                          actid    in number,
                          funcmode in varchar2,
                          result   out NOCOPY varchar2) IS
BEGIN

  IF (funcmode = 'RUN') THEN
    result := update_status(itemkey, funcmode, 'A');

Line: 454


**PROCEDURE STATUS_SUPERSEDED updates the status of effort **
**report to 'S'. It is called in workflow activity 	   **
**UPDATE_EFFORT_REPORT_STATUS_TO_SUPERSEDED.               **
*************************************************************/
procedure STATUS_SUPERSEDED(itemtype in varchar2,
                            itemkey  in varchar2,
                            actid    in number,
                            funcmode in varchar2,
                            result   out NOCOPY varchar2) IS
BEGIN

  IF (funcmode = 'RUN') THEN
    result := update_status(itemkey, funcmode, 'S');

Line: 477


**PROCEDURE STATUS_CERTIFIED updates the status of effort**
**report to 'C'. It is called in workflow activity       **
**UPDATE_EFFORT_REPORT_STATUS_TO_CERTIFIED.		 **
***********************************************************/
procedure STATUS_CERTIFIED(itemtype in varchar2,
                           itemkey  in varchar2,
                           actid    in number,
                           funcmode in varchar2,
                           result   out NOCOPY varchar2) IS
BEGIN

  IF (funcmode = 'RUN') THEN
    result := update_status(itemkey, funcmode, 'C');

Line: 500


**PROCEDURE STATUS_REJECTED updates the status of effort**
**report to 'R'. It is called by a workflow activity    **
**"Update Effort Report Status to Rejected".   		**
**********************************************************/
procedure STATUS_REJECTED    (itemtype in varchar2,
                              itemkey  in varchar2,
                              actid    in number,
                              funcmode in varchar2,
                              result   out NOCOPY varchar2) IS
BEGIN

  IF (funcmode = 'RUN') THEN
    result := update_status(itemkey, funcmode, 'R');

Line: 523


**Procedure SELECT_CERTIFIER is called by a workflow process "Select  **
**Certifier". It is invoked if user profile option "PSP:Can Supervisor**
**Certify" is set to "No". Procedure SELECT_CERTIFIER makes a call to **
**PSP_WF_CUSTOM.EFFORT_SELECT_CERTIFIER, where users can customize who**
**the certifer is.                                                    **
************************************************************************/
procedure SELECT_CERTIFIER(itemtype in varchar2,
                           itemkey  in varchar2,
                           actid    in number,
                           funcmode in varchar2,
                           result   out NOCOPY varchar2)
IS
  l_emp_id 		NUMBER;

Line: 543


    psp_wf_custom.effort_select_certifier(l_emp_id, l_certifier_id);

Line: 559


    WF_CORE.CONTEXT('PSP_WF_EFF_PKG', 'SELECT_CERTIFIER', itemtype, itemkey, to_char(actid), funcmode);

Line: 562


END SELECT_CERTIFIER;

Line: 565


**Procedure SELECT_APPROVER is called by a workflow process "Select   **
**Approver". It is invoked if user profile option "PSP:Can Employee   **
**Approve" is set to "No". Procedure SELECT_APPROVER makes a call to  **
**PSP_WF_CUSTOM.EFFORT_SELECT_APPROVER, where users can customize who **
**the approver is.                                                    **
************************************************************************/
procedure SELECT_APPROVER     (itemtype in varchar2,
                              itemkey  in varchar2,
                              actid    in number,
                              funcmode in varchar2,
                              result   out NOCOPY varchar2)
IS
  l_emp_id			NUMBER;

Line: 585


    psp_wf_custom.effort_select_approver(l_emp_id, l_approver_id);

Line: 601


    WF_CORE.CONTEXT('PSP_WF_EFF_PKG', 'SELECT_APPROVER', itemtype, itemkey, to_char(actid), funcmode);

Line: 604


END SELECT_APPROVER;

Line: 620


    SELECT responder
    FROM   wf_notifications
    WHERE  notification_id =
	--(SELECT MAX(notification_id) 			Commented for bug fix 3263333
	(SELECT notification_id 			-- Introduced for bug fix 3263333
	 FROM   wf_item_activity_statuses		-- Changed to base table for bug fix 3263333
	 WHERE  item_type = 'PSPEFFWF' AND
      	        item_key = itemkey AND
		process_activity = actid);		-- Introduced for bug fix 3263333

Line: 666


    SELECT responder
    FROM   wf_notifications
    WHERE  notification_id =
	--(SELECT MAX(notification_id) 			Commented for bug fix 3263333
	(SELECT notification_id				-- Introduced for bug fix 3263333
	 FROM   wf_item_activity_statuses		-- changed to base table for bug fix 3263333
	 WHERE  item_type = 'PSPEFFWF' AND
      	        item_key = itemkey AND
		process_activity = actid);		-- Introduced for bug fix 3263333

Line: 729


    SELECT ppf.current_employee_flag
    FROM   per_people_f ppf
    WHERE  ppf.person_id = l_person_id
    AND    trunc(SYSDATE) BETWEEN trunc(ppf.effective_start_date) AND trunc(ppf.effective_end_date);