APPS.BEN_CWB_CHANGE_ACCESS SQL Statements

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

Line: 13

PROCEDURE update_group_budget
    (
      p_validate                      in     boolean  default false
     ,p_group_per_in_ler_id           in     number
     ,p_group_pl_id                   in     number
     ,p_group_oipl_id                 in     number
     ,p_access_cd                     in     varchar2 default hr_api.g_varchar2
     ,p_comments                      in     varchar2 default null
     ,p_rcvr_person_id                in     number
     ,p_from_person_id                in     number
     ,p_grp_pl_name                   in     varchar2
     ,p_grp_pl_for_strt_dt            in     varchar2
     ,p_grp_pl_for_end_dt             in     varchar2
     ,p_object_version_number         in out nocopy   number
     ,p_requestor_name                 in     varchar2
    ) IS

--
-- Declare cursors and local variables
--
  l_proc  varchar2(72) := g_package||'update_group_budget';

Line: 52

      select access_cd into l_old_access_cd
      from  ben_cwb_person_groups
      where group_per_in_ler_id = p_group_per_in_ler_id
          and group_pl_id = p_group_pl_id
          and group_oipl_id = p_group_oipl_id;

Line: 61

  /* Update the person group information with new access code */
  BEN_CWB_PERSON_GROUPS_API.update_group_budget
     (p_group_per_in_ler_id   => p_group_per_in_ler_id
     ,p_group_pl_id           => p_group_pl_id
     ,p_group_oipl_id         => p_group_oipl_id
     ,p_access_cd             => p_access_cd
     ,p_object_version_number => l_object_version_number
     );

Line: 70

 /* If comments is <> null update the transaction and send the notification */
 if( p_comments is not null) then

 /* Update the transaction table with the contents of notification */
 insert into ben_transaction ( transaction_id,
                              transaction_type,
                              attribute1, -- from_person_id,
                              attribute2, -- to_person_id,
                              attribute3, -- to_per_in_ler_id,
                              attribute4, -- plan_name
                              attribute5, -- for_strt_dt
                              attribute6, -- for_end_dt
                              attribute7, -- new_access_cd
                              attribute40,-- comments
                              attribute9,  -- last updated date/time
                              attribute10,  -- old_access_cd
                              attribute11,  -- requestor first name
                              attribute12  -- requestor last name
                              )
             values         ( ben_transaction_s.nextval,
                              'CWBNTF',
                              p_from_person_id,
                              p_rcvr_person_id,
                              p_group_per_in_ler_id,
                              p_grp_pl_name,
                              p_grp_pl_for_strt_dt,
                              p_grp_pl_for_end_dt,
                              hr_general.decode_lookup('BEN_WS_ACC', p_access_cd ),
                              p_comments,
                              fnd_date.date_to_canonical(sysdate),
                              hr_general.decode_lookup('BEN_WS_ACC', l_old_access_cd ),
                              l_requestor_first_name,
                              l_requestor_last_name
                              )
             returning transaction_id into l_transaction_id ;

Line: 128

END  update_group_budget;

Line: 134

PROCEDURE update_access (
      p_validate                      in     boolean        default false
     ,p_popl_cd                       in     varchar2
     ,p_group_per_in_ler_id           in     BEN_CWB_ACCESS_STRING_ARRAY default null
     ,p_group_pl_id                   in     number
     ,p_group_oipl_id                 in     number
     ,p_access_cd_from                in     varchar2       default 'ANY'
     ,p_access_cd_to                  in     varchar2
     ,p_cascade                       in     varchar2       default 'N'
     ,p_comments                      in     varchar2       default null
     ,p_acting_person_id              in     number
     ,p_grp_pl_name                   in     varchar2
     ,p_grp_pl_for_strt_dt            in     varchar2
     ,p_grp_pl_for_end_dt             in     varchar2
     ,p_return_status                 out nocopy number
     ,p_requestor_name                 in  varchar2
     ,p_throw_exp                     out nocopy varchar2
    ) IS
--
TYPE REF_CURSOR IS REF CURSOR;

Line: 161

  l_proc  varchar2(72) := g_package||'update_access';

Line: 200

   'select max(emp_per.person_id)              emp_person_id,
       max(mgr_hrchy.emp_per_in_ler_id)    emp_per_in_ler_id,
       max(per_grp.object_version_number)  obj_ver_no,
       max(per_grp.access_cd) access_cd,
       max(per_grp.approval_cd) appr_cd,
       max(per_grp.submit_cd) submit_cd
from
         ben_cwb_person_info    emp_per,
         ben_cwb_group_hrchy    mgr_hrchy,
         ben_cwb_person_groups  per_grp,
         ben_cwb_summary        smry
where
             mgr_hrchy.mgr_per_in_ler_id        in (' || l_concat_str || ')
         and mgr_hrchy.lvl_num                  = 1
         and smry.group_per_in_ler_id           = mgr_hrchy.emp_per_in_ler_id
         and smry.elig_count_all                > 0
         and emp_per.group_per_in_ler_id        = mgr_hrchy.emp_per_in_ler_id
         and per_grp.group_per_in_ler_id        = mgr_hrchy.emp_per_in_ler_id
         and per_grp.group_pl_id                = ' || p_group_pl_id || '
         and per_grp.group_oipl_id              = -1
         and upper(per_grp.access_cd)           = decode(upper(''' || upper(p_access_cd_from) || '''), ''ANY'', upper(per_grp.access_cd), '''|| upper(p_access_cd_from) || ''')
         -- Additional check to avoid records which have worksheet status Approved
	     -- or approval status null and submit status as sumitted
	 --    and nvl(per_grp.approval_cd, ''XX'') <> ''AP''
         -- and NOT ( nvl(per_grp.submit_cd, ''XX'') = ''SU'' and per_grp.approval_cd is null )
group by smry.group_per_in_ler_id'; --

Line: 261

	update_group_budget ( p_validate, l_per_in_ler_id, p_group_pl_id,
                           p_group_oipl_id, p_access_cd_to, p_comments,
                           l_emp_person_id, p_acting_person_id, p_grp_pl_name, p_grp_pl_for_strt_dt,
                           p_grp_pl_for_end_dt, l_ovn , p_requestor_name);--process;

Line: 271

	update_group_budget ( p_validate, l_per_in_ler_id, p_group_pl_id,
                           p_group_oipl_id, p_access_cd_to, p_comments,
                           l_emp_person_id, p_acting_person_id, p_grp_pl_name, p_grp_pl_for_strt_dt,
                           p_grp_pl_for_end_dt, l_ovn , p_requestor_name);--process;

Line: 281

	update_group_budget ( p_validate, l_per_in_ler_id, p_group_pl_id,
                           p_group_oipl_id, p_access_cd_to, p_comments,
                           l_emp_person_id, p_acting_person_id, p_grp_pl_name, p_grp_pl_for_strt_dt,
                           p_grp_pl_for_end_dt, l_ovn , p_requestor_name);--process;

Line: 291

	update_group_budget ( p_validate, l_per_in_ler_id, p_group_pl_id,
                           p_group_oipl_id, p_access_cd_to, p_comments,
                           l_emp_person_id, p_acting_person_id, p_grp_pl_name, p_grp_pl_for_strt_dt,
                           p_grp_pl_for_end_dt, l_ovn , p_requestor_name);--process;

Line: 315

       update_group_budget ( p_validate, l_per_in_ler_id, p_group_pl_id,
                           p_group_oipl_id, p_access_cd_to, p_comments,
                           l_emp_person_id, p_acting_person_id, p_grp_pl_name, p_grp_pl_for_strt_dt,
                           p_grp_pl_for_end_dt, l_ovn , p_requestor_name);

Line: 339

    select max(emp_per.person_id)              emp_person_id,
       max(mgr_hrchy.emp_per_in_ler_id)    emp_per_in_ler_id,
       max(per_grp.object_version_number)  obj_ver_no,
       max(per_grp.access_cd) access_cd,
       max(per_grp.approval_cd) appr_cd,
       max(per_grp.submit_cd) submit_cd
from
         ben_cwb_person_info    emp_per,
         ben_cwb_group_hrchy    mgr_hrchy,
         ben_cwb_person_groups  per_grp,
         ben_cwb_summary        smry
where
             mgr_hrchy.mgr_per_in_ler_id        in (' || l_concat_str || ')
         and mgr_hrchy.lvl_num                  > 0
         and smry.group_per_in_ler_id           = mgr_hrchy.emp_per_in_ler_id
         and smry.elig_count_all                > 0
         and emp_per.group_per_in_ler_id        = mgr_hrchy.emp_per_in_ler_id
         and per_grp.group_per_in_ler_id        = mgr_hrchy.emp_per_in_ler_id
         and per_grp.group_pl_id                = ' || p_group_pl_id || '
         and per_grp.group_oipl_id              = -1
         and upper(per_grp.access_cd)           = decode(upper(''' || upper(p_access_cd_from) || '''), ''ANY'', upper(per_grp.access_cd), '''|| upper(p_access_cd_from) || ''')
         -- Additional check to avoid records which have worksheet status Approved
	     -- or approval status null and submit status as sumitted
	 --    and nvl(per_grp.approval_cd, ''XX'') <> ''AP''
         -- and NOT ( nvl(per_grp.submit_cd, ''XX'') = ''SU'' and per_grp.approval_cd is null )
group by smry.group_per_in_ler_id';

Line: 399

	update_group_budget ( p_validate, l_per_in_ler_id, p_group_pl_id,
                           p_group_oipl_id, p_access_cd_to, p_comments,
                           l_emp_person_id, p_acting_person_id, p_grp_pl_name, p_grp_pl_for_strt_dt,
                           p_grp_pl_for_end_dt, l_ovn , p_requestor_name);--process;

Line: 409

	update_group_budget ( p_validate, l_per_in_ler_id, p_group_pl_id,
                           p_group_oipl_id, p_access_cd_to, p_comments,
                           l_emp_person_id, p_acting_person_id, p_grp_pl_name, p_grp_pl_for_strt_dt,
                           p_grp_pl_for_end_dt, l_ovn , p_requestor_name);--process;

Line: 419

	update_group_budget ( p_validate, l_per_in_ler_id, p_group_pl_id,
                           p_group_oipl_id, p_access_cd_to, p_comments,
                           l_emp_person_id, p_acting_person_id, p_grp_pl_name, p_grp_pl_for_strt_dt,
                           p_grp_pl_for_end_dt, l_ovn , p_requestor_name);--process;

Line: 429

	update_group_budget ( p_validate, l_per_in_ler_id, p_group_pl_id,
                           p_group_oipl_id, p_access_cd_to, p_comments,
                           l_emp_person_id, p_acting_person_id, p_grp_pl_name, p_grp_pl_for_strt_dt,
                           p_grp_pl_for_end_dt, l_ovn , p_requestor_name);--process;

Line: 453

       update_group_budget ( p_validate, l_per_in_ler_id, p_group_pl_id,
                           p_group_oipl_id, p_access_cd_to, p_comments,
                           l_emp_person_id, p_acting_person_id, p_grp_pl_name, p_grp_pl_for_strt_dt,
                           p_grp_pl_for_end_dt, l_ovn , p_requestor_name);

Line: 473

     select   distinct
         max(emp_per.person_id)              emp_person_id,
         max(mgr_hrchy.emp_per_in_ler_id)    emp_per_in_ler_id,
         max(per_grp.object_version_number)  obj_ver_no,
	 max(per_grp.access_cd) access_cd,
         max(per_grp.approval_cd) appr_cd,
         max(per_grp.submit_cd) submit_cd
from
         ben_cwb_person_info    emp_per,
         ben_cwb_group_hrchy    mgr_hrchy,
         ben_cwb_person_groups  per_grp,
         ben_cwb_summary        smry
where
         /* Looking for Direct Reports */
             mgr_hrchy.mgr_per_in_ler_id        in (' || l_concat_str || ')
         and ((''' || p_cascade || '''= ''Y'' and mgr_hrchy.lvl_num >=0) or
               (''' || p_cascade || '''= ''N'' and mgr_hrchy.lvl_num =0))
         and smry.group_per_in_ler_id           = mgr_hrchy.emp_per_in_ler_id
         and smry.elig_count_all                > 0
         and emp_per.group_per_in_ler_id        = mgr_hrchy.emp_per_in_ler_id
         and per_grp.group_per_in_ler_id        = mgr_hrchy.emp_per_in_ler_id
         and per_grp.group_pl_id                = ' || p_group_pl_id || '
         and per_grp.group_oipl_id              = -1
         and upper(per_grp.access_cd)           = decode(upper(''' || upper(p_access_cd_from) || '''), ''ANY'', upper(per_grp.access_cd), '''|| upper(p_access_cd_from) || ''')
         -- Additional check to avoid records which have worksheet status Approved
	     -- or approval status null and submit status as sumitted
         -- and nvl(per_grp.approval_cd, ''XX'') <> ''AP''
         -- and NOT ( nvl(per_grp.submit_cd, ''XX'') = ''SU'' and per_grp.approval_cd is null )
group by smry.group_per_in_ler_id'; --

Line: 536

	update_group_budget ( p_validate, l_per_in_ler_id, p_group_pl_id,
                           p_group_oipl_id, p_access_cd_to, p_comments,
                           l_emp_person_id, p_acting_person_id, p_grp_pl_name, p_grp_pl_for_strt_dt,
                           p_grp_pl_for_end_dt, l_ovn , p_requestor_name);--process;

Line: 546

	update_group_budget ( p_validate, l_per_in_ler_id, p_group_pl_id,
                           p_group_oipl_id, p_access_cd_to, p_comments,
                           l_emp_person_id, p_acting_person_id, p_grp_pl_name, p_grp_pl_for_strt_dt,
                           p_grp_pl_for_end_dt, l_ovn , p_requestor_name);--process;

Line: 556

	update_group_budget ( p_validate, l_per_in_ler_id, p_group_pl_id,
                           p_group_oipl_id, p_access_cd_to, p_comments,
                           l_emp_person_id, p_acting_person_id, p_grp_pl_name, p_grp_pl_for_strt_dt,
                           p_grp_pl_for_end_dt, l_ovn , p_requestor_name);--process;

Line: 566

	update_group_budget ( p_validate, l_per_in_ler_id, p_group_pl_id,
                           p_group_oipl_id, p_access_cd_to, p_comments,
                           l_emp_person_id, p_acting_person_id, p_grp_pl_name, p_grp_pl_for_strt_dt,
                           p_grp_pl_for_end_dt, l_ovn , p_requestor_name);--process;

Line: 590

       update_group_budget ( p_validate, l_per_in_ler_id, p_group_pl_id,
                           p_group_oipl_id, p_access_cd_to, p_comments,
                           l_emp_person_id, p_acting_person_id, p_grp_pl_name, p_grp_pl_for_strt_dt,
                           p_grp_pl_for_end_dt, l_ovn , p_requestor_name);

Line: 606

END update_access;