1 PACKAGE BODY fnd_oam_user_info AS
2 /* $Header: AFOAMUIB.pls 120.2 2005/10/10 21:46:24 fskinner noship $ */
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 SELECT
61 per.full_name,
62 per.work_telephone,
63 per.email_address,
64 usr.fax
65 INTO full_name, phone, email, fax
66 FROM
67 fnd_user usr,
68 per_all_people_f per
69 WHERE
70 trunc(SYSDATE) between per.effective_start_date
71 and per.effective_end_date
72 and per.person_id = usr.employee_id
73 and usr.user_id = v_user_id;
74
75 ELSIF v_customer_id > 0 OR v_supplier_id > 0
76 THEN
77 SELECT
78 hz.PERSON_NAME_SUFFIX,
79 hz.PERSON_FIRST_NAME,
80 hz.PERSON_LAST_NAME,
81 hz.email_address,
82 usr.fax
83 INTO suffix, first_name, last_name, email, fax
84 FROM
85 fnd_user usr,
86 hz_parties hz
87 WHERE
88 (hz.party_id = usr.customer_id
89 OR hz.party_id = usr.supplier_id)
90 and usr.user_id = v_user_id;
91
92 IF last_name is not null THEN
93 last_name := CONCAT(last_name, ', ');
94 END IF;
95
96 IF suffix is not null THEN
97 suffix := CONCAT(suffix, ' ');
98 END IF;
99
100 full_name := CONCAT(CONCAT(last_name, suffix), first_name);
101
102 -- ELSIF user_guid is not null
103 -- THEN
104 -- it indicates that the row is being synchronized with an external directory such as OID.
105 END IF;
106
107 EXCEPTION
108 WHEN no_data_found THEN
109 full_name := null;
110
111 end;
112
113
114 END get_contact_info;
115
116
117 --
118 -- Return PARTY_ID given EMPLOYEE_ID
119 --
120 function GET_PARTY_ID(P_EMPLOYEE_ID in number) return number
121 is
122 L_EMP_PERSON_PARTY_ID number;
123 begin
124 SELECT party_id
125 INTO l_emp_person_party_id
126 FROM per_all_people_f
127 WHERE person_id = p_employee_id
128 AND trunc(sysdate) between effective_start_date and effective_end_date
129 UNION
130 SELECT party_id
131 FROM per_all_people_f p
132 WHERE p.person_id = p_employee_id
133 AND p.effective_start_date = (SELECT min(p2.effective_start_date)
134 FROM per_all_people_f p2
135 WHERE p2.person_id = p.person_id)
136 AND p.effective_start_date > trunc(sysdate);
137 return(L_EMP_PERSON_PARTY_ID);
138 end GET_PARTY_ID;
139
140 --
141 -- Given PARTY_ID return PARTY_NAME, PARTY_TYPE
142 --
143 procedure HZ_PARTY_ID_TO_NAME(P_PARTY_ID in number,
144 P_PARTY_NAME out nocopy varchar2,
145 P_PARTY_TYPE out nocopy varchar2)
146 is
147 begin
148 select PARTY_TYPE, PARTY_NAME
149 into P_PARTY_TYPE, P_PARTY_NAME
150 from HZ_PARTIES
151 where PARTY_ID = P_PARTY_ID;
152 end HZ_PARTY_ID_TO_NAME;
153
154 --
155 -- Given PARTY_NAME return PARTY_ID, PARTY_TYPE
156 --
157 procedure HZ_PARTY_NAME_TO_ID(P_PARTY_NAME in varchar2,
158 P_PARTY_ID out nocopy number,
159 P_PARTY_TYPE out nocopy varchar2)
160 is
161 begin
162 select PARTY_TYPE, PARTY_ID
163 into P_PARTY_TYPE, P_PARTY_ID
164 from HZ_PARTIES
165 where PARTY_NAME = P_PARTY_NAME;
166 end HZ_PARTY_NAME_TO_ID;
167
168 --
169 -- Get organization party ID given customer party ID
170 --
171 function GET_ORGANIZATION_ID(P_CUSTOMER_ID in number) return number
172 is
173 L_CUST_PERSON_PARTY_ID number;
174 begin
175 select rel.subject_id
176 into l_cust_person_party_id
177 from hz_relationships rel
178 where rel.party_id = p_customer_id
179 and rel.subject_table_name = 'HZ_PARTIES'
180 and rel.subject_type = 'PERSON'
181 and rel.object_table_name = 'HZ_PARTIES'
182 and rel.object_type = 'ORGANIZATION';
183
184 return(L_CUST_PERSON_PARTY_ID);
185 end GET_ORGANIZATION_ID;
186
187 END fnd_oam_user_info;
188
189
[Home] [Help]