1 PACKAGE BODY fnd_oam_user_info AS
2 /* $Header: AFOAMUIB.pls 120.5 2011/06/30 19:48:08 rlandows ship $ */
3
4
5
6 -- Name
7 -- get_contact_info
8 --
9 -- Purpose
10 -- Returns the contact information of a user
11 --
12 -- Input Arguments
13 -- p_username - User name.
14 --
15 -- Output Arguments
16 -- full_name - Full name of the person
17 -- phone - Phone number
18 -- email - Email address
19 -- fax - Fax number
20 --
21 --
22 PROCEDURE get_contact_info
23 (p_username IN VARCHAR2,
24 full_name OUT NOCOPY VARCHAR2,
25 phone OUT NOCOPY VARCHAR2,
26 email OUT NOCOPY VARCHAR2,
27 fax OUT NOCOPY VARCHAR2,
28 user_guid OUT NOCOPY VARCHAR2)
29 IS
30 v_user_id NUMBER;
31 v_employee_id NUMBER;
32 v_customer_id NUMBER;
33 v_supplier_id NUMBER;
34 suffix varchar2(10);
35 first_name varchar2(100);
36 last_name varchar2(100);
37 BEGIN
38
39 -- get user_id, employee_id, customer_id, and supplier_id
40
41 begin
42 select u.user_id, nvl(u.employee_id, -1),
43 nvl(u.customer_id, -1), nvl(u.supplier_id, -1),
44 user_guid
45 into v_user_id, v_employee_id, v_customer_id,
46 v_supplier_id, user_guid
47 from fnd_user u
48 where upper(user_name) = upper(p_username);
49 exception
50 when no_data_found then
51 v_employee_id := -1;
52 v_customer_id := -1;
53 v_supplier_id := -1;
54 user_guid := null;
55 end;
56
57 begin
58 IF v_employee_id > 0
59 THEN
60
61 --9835588, work_telephone is no longer used
62 --in per_all_people_f according to HR dev
63 --and hasn't been even in 11i. Getting it from
64 --per_phones.
65
66 SELECT
67 per.full_name,
68 per.email_address,
69 usr.fax
70 INTO full_name, email, fax
71 FROM
72 fnd_user usr,
73 per_all_people_f per
74 WHERE
75 trunc(SYSDATE) between per.effective_start_date
76 and per.effective_end_date
77 and per.person_id = usr.employee_id
78 and usr.user_id = v_user_id;
79
80
81 begin
82
83 select phone_number
84 into phone
85 from per_phones
86 where phone_type = 'W1'
87 and parent_table = 'PER_ALL_PEOPLE_F'
88 and sysdate between nvl(date_from, sysdate - 1)
89 and nvl(date_to, sysdate + 1)
90 and parent_id = v_employee_id;
91
92 exception
93 when others then
94 phone := null;
95 end;
96
97 ELSIF v_customer_id > 0 OR v_supplier_id > 0
98 THEN
99 SELECT
100 hz.PERSON_NAME_SUFFIX,
101 hz.PERSON_FIRST_NAME,
102 hz.PERSON_LAST_NAME,
103 hz.email_address,
104 usr.fax
105 INTO suffix, first_name, last_name, email, fax
106 FROM
107 fnd_user usr,
108 hz_parties hz
109 WHERE
110 (hz.party_id = usr.customer_id
111 OR hz.party_id = usr.supplier_id)
112 and usr.user_id = v_user_id;
113
114 IF last_name is not null THEN
115 last_name := CONCAT(last_name, ', ');
116 END IF;
117
118 IF suffix is not null THEN
119 suffix := CONCAT(suffix, ' ');
120 END IF;
121
122 full_name := CONCAT(CONCAT(last_name, suffix), first_name);
123
124 -- ELSIF user_guid is not null
125 -- THEN
126 -- it indicates that the row is being synchronized with an external directory such as OID.
127 END IF;
128
129 EXCEPTION
130 WHEN no_data_found THEN
131 full_name := null;
132
133 end;
134
135
136 END get_contact_info;
137
138
139 --
140 -- Return PARTY_ID given EMPLOYEE_ID
141 --
142 function GET_PARTY_ID(P_EMPLOYEE_ID in number) return number
143 is
144 L_EMP_PERSON_PARTY_ID number;
145 begin
146 SELECT party_id
147 INTO l_emp_person_party_id
148 FROM per_all_people_f
149 WHERE person_id = p_employee_id
150 AND trunc(sysdate) between effective_start_date and effective_end_date
151 UNION
152 SELECT party_id
153 FROM per_all_people_f p
154 WHERE p.person_id = p_employee_id
155 AND p.effective_start_date = (SELECT min(p2.effective_start_date)
156 FROM per_all_people_f p2
157 WHERE p2.person_id = p.person_id)
158 AND p.effective_start_date > trunc(sysdate);
159 return(L_EMP_PERSON_PARTY_ID);
160 end GET_PARTY_ID;
161
162 --
163 -- Given PARTY_ID return PARTY_NAME, PARTY_TYPE
164 --
165 procedure HZ_PARTY_ID_TO_NAME(P_PARTY_ID in number,
166 P_PARTY_NAME out nocopy varchar2,
167 P_PARTY_TYPE out nocopy varchar2)
168 is
169 begin
170 select PARTY_TYPE, PARTY_NAME
171 into P_PARTY_TYPE, P_PARTY_NAME
172 from HZ_PARTIES
173 where PARTY_ID = P_PARTY_ID;
174 end HZ_PARTY_ID_TO_NAME;
175
176 --
177 -- Given PARTY_NAME return PARTY_ID, PARTY_TYPE
178 --
179 procedure HZ_PARTY_NAME_TO_ID(P_PARTY_NAME in varchar2,
180 P_PARTY_ID OUT NOCOPY number,
181 P_PARTY_TYPE out nocopy varchar2)
182 is
183 begin
184 select PARTY_TYPE, PARTY_ID
185 into P_PARTY_TYPE, P_PARTY_ID
186 from HZ_PARTIES
187 where PARTY_NAME = P_PARTY_NAME;
188 end HZ_PARTY_NAME_TO_ID;
189
190 --
191 -- Get organization party ID given customer party ID
192 --
193 function GET_ORGANIZATION_ID(P_CUSTOMER_ID in number) return number
194 is
195 L_CUST_PERSON_PARTY_ID number;
196 begin
197
198 -- Bug 4596696 indicates that hz_party_relationships is obsolete in R12.
199 -- So, check which EBS release version and use the appropriate query.
200
201 --12430929, broke dual checkin and removed reference to
202 --hz_party_relationships
203
204 select distinct rel.subject_id
205 into l_cust_person_party_id
206 from hz_relationships rel
207 where rel.party_id = p_customer_id
208 and rel.subject_table_name = 'HZ_PARTIES'
209 and rel.subject_type = 'PERSON'
210 and rel.object_table_name = 'HZ_PARTIES'
211 and rel.object_type = 'ORGANIZATION';
212
213
214 return(L_CUST_PERSON_PARTY_ID);
215 end GET_ORGANIZATION_ID;
216
217 END fnd_oam_user_info;
218
[Home] [Help]