APPS.AP_WEB_PROXY_ASSIGN_PKG SQL Statements

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

Line: 7

PROCEDURE all_assignee_update(p_assignor_id IN NUMBER,
                   p_responsibility_id IN NUMBER,
                   p_resp_app_id IN NUMBER,
                   p_sec_id IN NUMBER,
                   p_app_short_name IN VARCHAR2,
                   p_end_date IN DATE,
                   p_notification IN VARCHAR2) AS

  l_assignee_name varchar2(100);

Line: 25

          select c.user_name l_assignee_name,
             b.responsibility_name l_responsibility_name,
             pa.effective_start_date l_effecttive_start_date,
             pa.effective_end_date l_effective_end_date,
             b.application_id as resp_application_id,
             d.responsibility_key, e.security_group_key
          from AP_WEB_PROXY_ASSIGNMENTS pa, fnd_responsibility_tl b, fnd_user c,
             fnd_responsibility d, fnd_security_groups e, per_people_f ppf
          where   pa.ASSIGNEE_ID = c.user_id
          and pa.RESPONSIBILITY_ID = b.RESPONSIBILITY_ID
          and pa.ASSIGNOR_ID = p_assignor_id
          and b.language = userenv('LANG')
          and b.responsibility_id = d.responsibility_id
          and b.application_id = d.application_id
          and pa.security_group_id = e.security_group_id and  c.employee_id = ppf.person_id
          and pa.RESPONSIBILITY_ID = p_responsibility_id
          and pa.responsibility_app_id = p_resp_app_id
          and pa.responsibility_app_id = b.application_id
          and e.security_group_id = p_sec_id --
          for update of pa.effective_end_date;

Line: 46

         select c.user_name l_assignee_name,
             b.responsibility_name l_responsibility_name,
             pa.effective_start_date l_effecttive_start_date,
             pa.effective_end_date l_effective_end_date,
             b.application_id as resp_application_id,
             d.responsibility_key, e.security_group_key
             from AP_WEB_PROXY_ASSIGNMENTS pa, fnd_responsibility_tl b, fnd_user c,
             fnd_responsibility d, fnd_security_groups e, per_people_f ppf
             where   pa.ASSIGNEE_ID = c.user_id
             and pa.RESPONSIBILITY_ID = b.RESPONSIBILITY_ID
             and pa.ASSIGNOR_ID = p_assignor_id
             and b.language = userenv('LANG')
             and b.responsibility_id = d.responsibility_id
             and b.application_id = d.application_id
             and pa.security_group_id = e.security_group_id
             and  c.employee_id = ppf.person_id
             and pa.responsibility_app_id = b.application_id
             for update of pa.effective_end_date;

Line: 66

        fnd_log.string(fnd_log.LEVEL_STATEMENT,'AP_WEB_PROXY_ASSIGN_PKG.all_assignee_update','Enter');

Line: 80

                            'UPDATED',
                            l_effective_start_date,
                            l_end_date);

Line: 86

            update ap_web_proxy_assignments
            set effective_end_date = l_end_date
            where current of c1;

Line: 100

                            'UPDATED',
                            l_effective_start_date,
                            l_end_date);

Line: 106

            update ap_web_proxy_assignments
            set effective_end_date = l_end_date
            where current of c2;

Line: 113

        fnd_log.string(fnd_log.LEVEL_STATEMENT,'AP_WEB_PROXY_ASSIGN_PKG.all_assignee_update','Exit');

Line: 117

END all_assignee_update;

Line: 127

 |          oracle.apps.fnd.wf.ds.userRole.updated
 |          oracle.apps.fnd.wf.ds.user.updated
 | Based on event key and its paramters, function would call other private procedure within
 | this package to update the ap_web_proxy_assignments table, update fnd responsibilities
 | by calling fnd_user_pkg.addresp api and sending notificaiton to assignee.
 |
 | PARAMETERS
 |     	p_subscription_guid
 |      p_event
 |
 | RETURNS
 |     	SUCCESS/ ERROR
 *----------------------------------------------------------------------------*/
FUNCTION proxy_assignments  (p_subscription_guid  IN RAW,
   p_event              IN OUT NOCOPY WF_EVENT_T) RETURN VARCHAR2
AS
   l_event_key VARCHAR2(1000);

Line: 165

   if (l_event_name = 'oracle.apps.fnd.user.role.update') then

      l_user_id := p_event.GetValueForParameter('FND_USER_ID');

Line: 172

      select end_date, security_group_id
      into l_end_date, l_sec_group_id
      from fnd_user_resp_groups_direct
      where responsibility_id = l_resp_id
      and user_id = l_user_id
      and rownum = 1;

Line: 179

      all_assignee_update(l_user_id, l_resp_id, l_resp_app_id, l_sec_group_id, l_app_short_name, l_end_date, 'N');

Line: 181

   elsif (l_event_name = 'oracle.apps.fnd.wf.ds.user.updated') then
         if (p_event.GetValueForParameter('PARENT_ORIG_SYSTEM') = 'PER') then
             if ( (p_event.GetValueForParameter('STATUS') = 'INACTIVE')
                  or ((p_event.GetValueForParameter('EXPIRATION_DATE')) <
                      (p_event.GetValueForParameter('OLD_END_DATE')) ) ) then
             l_user_name := p_event.GetValueForParameter('USER_NAME');

Line: 187

             select user_id, nvl(end_date, sysdate)
             into l_user_id, l_end_date from fnd_user
             where user_name = l_event_key;

Line: 194

         all_assignee_update(l_user_id, null, null, null, 'SQLAP', l_end_date, 'N');

Line: 252

  select p_user_name || to_char(sysdate, 'DDMONYYYYHH24MISS') into l_request_id from dual;

Line: 255

  select full_name
  into l_full_name
  from fnd_user fu, hr_employees hre
  where fu.employee_id = hre.employee_id
  and user_name = p_assignor_name;