1 package body ONT_icp_get_user_details as
2 /*$Header: OEXONTHB.pls 120.1 2005/07/29 13:04:07 jvicenti noship $ */
3 procedure get_user_customer(in_user IN NUMBER,
4 out_name OUT NOCOPY VARCHAR2,
5
6 out_customer OUT NOCOPY VARCHAR2,
7
8 out_customer_id OUT NOCOPY NUMBER,
9
10 out_contact_id OUT NOCOPY NUMBER,
11
12 out_status OUT NOCOPY VARCHAR2
13
14 ) IS
15
16 CURSOR c_fnd_user IS
17 SELECT employee_id,customer_id
18 FROM fnd_user
19 WHERE user_id = in_user;
20
21
22 CURSOR c_party(in_customer_id IN NUMBER) IS
23 SELECT party_name,party_type,party_id
24 FROM hz_parties
25 WHERE party_id = in_customer_id;
26
27 CURSOR c_customer(in_party_id IN VARCHAR2) IS
28 SELECT cust_account_id,
29 party_name
30 FROM hz_parties h,hz_cust_accounts c
31 WHERE c.party_id = h.party_id
32 AND h.party_id = ( SELECT object_id
33 FROM hz_relationships r
34 WHERE r.party_id = in_party_id
35 AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
36 AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES'
37 AND r.DIRECTIONAL_FLAG = 'F'
38 );
39
40 CURSOR c_user(in_party_id IN VARCHAR2) IS
41 SELECT cust_account_role_id,party_name
42 FROM hz_parties h,hz_cust_account_roles c
43 WHERE c.party_id = h.party_id
44 AND h.party_id = ( SELECT subject_id
45 FROM hz_relationships r
46 WHERE r.party_id = in_party_id
47 AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
48 AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES'
49 AND r.DIRECTIONAL_FLAG = 'F' );
50
51
52 CURSOR c_employee(in_employee IN NUMBER) IS
53 SELECT full_name
54 FROM per_all_people_f
55 WHERE person_id = in_employee;
56
57 vc_party_name hz_parties.party_name%TYPE;
58 vn_party_id hz_parties.party_id%TYPE;
59 vc_party_type hz_parties.party_type%TYPE;
60 e_invalid_party_type EXCEPTION;
61
62 vn_customer_id fnd_user.customer_id%TYPE;
63 vn_emp_id fnd_user.employee_id%TYPE;
64 vc_last VARCHAR2(50);
65 vc_first VARCHAR2(40);
66
67 --
68 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
69 --
70 BEGIN
71
72 --==========================================================
73 -- Getting the attached employee and customer to the fnd user
74 --==========================================================
75 OPEN c_fnd_user;
76 FETCH c_fnd_user
77 INTO vn_emp_id,
78 vn_customer_id;
79 CLOSE c_fnd_user;
80
81 --==========================================================
82 -- if the employee is attached to the fnd_user than we
83 -- select employee first and last name
84 --==========================================================
85 IF vn_emp_id IS NOT NULL THEN
86
87 OPEN c_employee(vn_emp_id);
88 FETCH c_employee
89 INTO out_name;
90 CLOSE c_employee;
91
92 out_customer := NULL;
93 out_contact_id := NULL;
94 out_customer_id := NULL;
95
96 --==========================================================
97 -- if the employee is not attached to the fnd_user than we
98 -- select the customer and contact details
99 --==========================================================
100 ELSE
101
102
103 --==========================================================
104 -- Getting the attached party to the Fnd User
105 --==========================================================
106 OPEN c_party(vn_customer_id);
107 FETCH c_party
108 INTO vc_party_name,
109 vc_party_type,
110 vn_party_id;
111 CLOSE c_party;
112
113 --==========================================================
114 -- if the party is of type 'PERSON' than it is an internal
115 -- person
116 --==========================================================
117 IF vc_party_type = 'PERSON' THEN
118
119 out_name := vc_party_name;
120 out_customer := NULL;
121 out_contact_id := NULL;
122 out_customer_id := NULL;
123
124 --==========================================================
125 -- if the party attached is of type 'party_relationship' then
126 -- we find the Customer and the Contact in this relationship
127 --==========================================================
128 ELSIF vc_party_type = 'PARTY_RELATIONSHIP' THEN
129
130 OPEN c_customer(vn_party_id);
131 FETCH c_customer
132 INTO out_customer_id,
133 out_customer;
134 CLOSE c_customer;
135
136 OPEN c_user(vn_party_id);
137 FETCH c_user
138 INTO out_contact_id,
139 out_name;
140 CLOSE c_user;
141 -- hard coded for test purposes
142 --out_customer_id := 1006;
143
144 ELSE
145 RAISE e_invalid_party_type;
146
147 END IF; /* if party_type */
148
149 END IF; /* if the person is employee */
150
151 out_status := 'TRUE';
152
153 --dbms_output.put_line('user name='||out_name);
154 --dbms_output.put_line('customer='||out_customer);
155
156 EXCEPTION
157
158 WHEN OTHERS THEN
159 IF c_party%ISOPEN THEN
160 CLOSE c_party;
161 END IF;
162 IF c_fnd_user%ISOPEN THEN
163 CLOSE c_fnd_user;
164 END IF;
165 IF c_employee%ISOPEN THEN
166 CLOSE c_employee;
167 END IF;
168 IF c_user%ISOPEN THEN
169 CLOSE c_user;
170 END IF;
171 IF c_customer%ISOPEN THEN
172 CLOSE c_customer;
173 END IF;
174
175 IF sqlerrm IS NOT NULL THEN
176 --fnd_message.set_string(sqlerrm);
177 --fnd_message.error;
178 out_status := 'FALSE';
179 NULL;
180 END IF;
181
182
183 END get_user_customer; /* end of procedure*/
184
185 end ONT_icp_get_user_details; /* end of package body */
[Home] [Help]