PACKAGE BODY: APPS.UMX_PROXY_USER_PVT

Source

1 PACKAGE BODY UMX_PROXY_USER_PVT as
2 /*$Header: UMXVPRXB.pls 120.2 2005/10/13 16:45:45 cmehta noship $*/
3 
4   /**
5    * Private function
6    */
7 
8 function get_job_title(p_person_id in per_all_people_f.person_id%type) return varchar2 is
9 
10  cursor get_title is select job.name
11         from per_all_assignments_f passign, per_jobs_vl job
12 	where passign.person_id = p_person_id
13         and   passign.job_id = job.job_id
14 	and   passign.effective_start_date <= sysdate
15 	and   nvl(passign.effective_end_date, sysdate+1) > sysdate;
16 
17  l_title per_jobs_vl.name%type;
18  begin
19 
20    open get_title;
21    fetch get_title into l_title;
22    close get_title;
23  return  l_title;
24 end get_job_title;
25 /**
26    * Please refer to the package specifications for details
27    */
28 
29 function GET_PERSON_ID (p_party_id  in hz_parties.party_id%type) return number IS
30 
31 begin
32 
33 return UMX_REGISTRATION_PVT.get_person_id(p_party_id);
34 
35 END GET_PERSON_ID;
36 
37    /**
38    * Please refer to the package specifications for details
39    */
40 
41   function GET_PHONE_NUMBER(p_person_id  in per_all_people_f.person_id%type) return varchar2 IS
42 
43 l_phone_number per_phones.phone_number%type;
44 cursor find_phone_number is
45  select perph.phone_number
46  from   per_phones perph
47  where  perph.phone_type = 'W1'
48  and    perph.parent_id = p_person_id
49  and    perph.parent_table = 'PER_ALL_PEOPLE_F'
50  and    perph.date_from <= sysdate
51  and    nvl(perph.date_to, sysdate + 1) > sysdate;
52 
53 
54 begin
55 
56 open find_phone_number;
57 fetch find_phone_number into l_phone_number;
58 close find_phone_number;
59 
60 return l_phone_number;
61 
62 END GET_PHONE_NUMBER;
63 
64 
65    /**
66    * Please refer to the package specifications for details
67    */
68 
69 
70  procedure GET_EMP_DATA(p_person_id  in per_all_people_f.person_id%type,
71                          x_phone_number out NOCOPY PER_PHONES.PHONE_NUMBER%TYPE ,
72                          x_job_title out NOCOPY PER_JOBS_VL.NAME%TYPE
73                          ) IS
74 
75  begin
76 
77     x_phone_number := GET_PHONE_NUMBER(p_person_id);
78     x_job_title := GET_JOB_TITLE(p_person_id);
79 
80  end GET_EMP_DATA;
81 
82 
83 
84 end UMX_PROXY_USER_PVT;