APPS.PSP_WF_ADJ_PKG SQL Statements

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

Line: 17

         SELECT supervisor_id
         FROM   per_assignments_f
         WHERE  person_id = p_person_id
         AND    assignment_number = p_assignment_number
 	 AND    sysdate between effective_start_date and effective_end_date;

Line: 26

      	 SELECT paf1.supervisor_id
      	 FROM   per_assignments_f paf1
      	 WHERE  paf1.person_id = p_person_id
      	 AND	paf1.assignment_number = p_assignment_number
      	 AND	paf1.effective_end_date = (SELECT max(paf2.effective_end_date)
      	 				FROM	per_assignments_f paf2
      	 				WHERE	paf2.assignment_id = paf1.assignment_id);

Line: 55

	SELECT	count(*)
	INTO	l_valid_supervisor
-- 	FROM	per_people_f ppf  Commented for Bug 3741272
	FROM	per_all_people_f ppf
	WHERE	ppf.person_id = p_supervisor_id
	AND	trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
	AND 	current_employee_flag = 'Y';

Line: 72

   /*Procedure UPDATE_ADJUSTMENT_CONTROL_TABLE updated the table
     PSP_ADJUSTMENT_CONTROL_TABLE with the         */
   /*approver's UserID.                                 */
   /**************************************************************************/
   PROCEDURE update_adj_ctrl_table(p_batch_name       IN  VARCHAR2,
			           p_approver_userID  IN  NUMBER,
				   p_comments	      IN  VARCHAR2,
			           return_code        OUT NOCOPY NUMBER) IS
   BEGIN

      UPDATE psp_adjustment_control_table
      SET    approver_id = p_approver_userID,
             approval_date = SYSDATE,  -- Added to fix bug 1661405. approval_date is a new column added to table
	     comments = p_comments
      WHERE  adjustment_batch_name = p_batch_name;

Line: 93

      update psp_payroll_controls
	set status_code = 'N'
      where batch_name = p_batch_name
      and   source_type = 'A'
      and   status_code = 'C';

Line: 106

   END update_adj_ctrl_table;

Line: 241

** Procedure SELECT_APPROVER is called by "Select Approver" activity in the
** distribution adjustment workflow process.
** By default, the supervisor is the approver.
** If customization is needed,  enter your code in
** PSP_WF_ADJ_CUSTOM.select_approver_custom.
****************************************************************************/
PROCEDURE select_approver(itemtype IN  VARCHAR2,
                          itemkey  IN  VARCHAR2,
                          actid    IN  NUMBER,
                          funcmode IN  VARCHAR2,
                          result   OUT NOCOPY VARCHAR2)
IS

   l_person_id 			NUMBER;

Line: 276

      ** to customize the program to select the approver.
      ** Customize code can be entered in psp_wf_adj_custom.select_approver_custom
      ** procedure and returns the appropriate supervisior id.
      **---------------------------------------------------------------------*/

      l_supervisor_id := get_supervisor(l_person_id, l_assignment_number);

Line: 293

  	psp_wf_adj_custom.select_approver_custom(itemtype,
                          		         itemkey,
                          			 actid,
                          		         funcmode,
						 l_person_id,
						 l_assignment_number,
						 l_custom_supervisor_id);

Line: 337

    wf_core.context('PSP_WF_ADJ_PKG', 'SELECT_APPROVER', itemtype, itemkey, to_char(actid), funcmode);

Line: 339

END select_approver;

Line: 407

    SELECT responder
    FROM   wf_notifications
    WHERE  notification_id =
--	Introduced the following for bug fix 3263333
				(SELECT	ias.notification_id
				FROM	wf_lookups l_at,
					wf_lookups l_as,
					wf_activities_vl a,
					wf_process_activities pa,
					wf_item_types_vl it,
					wf_items i,
					wf_item_activity_statuses ias
				WHERE	ias.item_type = itemtype
				AND	ias.item_key = itemkey
				AND	i.item_type = itemtype
				AND	i.item_key = itemkey
				AND	i.begin_date between a.begin_date AND nvl(a.end_date, i.begin_date)
				AND	i.item_type = it.name
				AND	ias.process_activity = pa.instance_id
				AND	pa.activity_name = a.name
				AND	pa.activity_item_type = a.item_type
				AND	l_at.lookup_type = 'WFENG_ACTIVITY_TYPE'
				AND	l_at.lookup_code = a.type
				AND	l_as.lookup_type = 'WFENG_STATUS'
				AND	l_as.lookup_code = ias.activity_status
				AND	a.name = 'NOT_APPROVAL_REQUIRED');

Line: 434

	--(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 = 'PSPADJWF' AND
      	        item_key = itemkey AND
		process_activity = actid);			-- Introduced for bug fix 3263333

Line: 482

      SELECT user_id
      FROM   fnd_user
      WHERE  user_name = l_approver_username;

Line: 505

     	 update_adj_ctrl_table(l_batch_name, l_approver_userID,l_comments, l_return_code);

Line: 576

      SELECT user_id
      into l_creator_user_id
      FROM   fnd_user
      WHERE  user_name = l_creator_username;

Line: 606

     	 update_adj_ctrl_table(l_batch_name, l_creator_user_id,l_comments, l_return_code);