[Home] [Help]
PACKAGE BODY: APPS.WSH_CONTACT_PERSON_PKG
Source
1 PACKAGE BODY WSH_CONTACT_PERSON_PKG as
2 /* $Header: WSHCPTHB.pls 120.1.12010000.2 2008/09/18 08:54:11 sankarun ship $ */
3
4 /*============================================================================
5 Procedure: CREATE_CONTACTPERSON
6 Purpose : Creates another party for the contact person.
7 =============================================================================*/
8
9 --
10 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_CONTACT_PERSON_PKG';
11 --
12
13 PROCEDURE CREATE_CONTACTPERSON(
14 P_CARRIER_PARTY_ID IN NUMBER,
15 P_STATUS IN VARCHAR2,
16 P_PERSON_NAME_PRE_ADJUNCT IN VARCHAR2,
17 P_PERSON_FIRST_NAME IN VARCHAR2 ,
18 P_PERSON_LAST_NAME IN VARCHAR2 ,
19 X_RELATIONSHIP_PARTY_ID OUT NOCOPY NUMBER,
20 X_PERSON_PARTY_ID OUT NOCOPY NUMBER,
21 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
22 X_EXCEPTION_MSG OUT NOCOPY VARCHAR2,
23 X_POSITION OUT NOCOPY NUMBER,
24 X_PROCEDURE OUT NOCOPY VARCHAR2,
25 X_SQLERR OUT NOCOPY VARCHAR2,
26 X_SQL_CODE OUT NOCOPY VARCHAR2
27 )
28 IS
29
30 -------------------------
31 -- General Declarations.
32 -------------------------
33
34 l_return_status varchar2(100);
35 l_msg_count number;
36 l_position number;
37 l_call_procedure varchar2(100);
38 l_msg_data varchar2(2000);
39 l_party_number varchar2(100);
40 l_profile_id number;
41 l_relationship_id number;
42 l_exception_msg varchar2(1000);
43 HZ_FAIL_EXCEPTION exception;
44 l_party_relationship_id number;
45 l_relationship_party_id number;
46
47 ---------------------------------------------
48 -- Declarations for Party 'PERSON' Creation.
49 ---------------------------------------------
50
51 l_per_rec HZ_PARTY_V2PUB.person_rec_type;
52 l_person_party_id number;
53
54 l_rel_rec_type HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE;
55
56 -- Bug 7391414 variables to hold profile option value 'HZ GENERATE PARTY NUMBER'
57 l_hz_profile_option varchar2(2);
58 l_hz_profile_set boolean;
59
60 --
61 l_debug_on BOOLEAN;
62 --
63 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_CONTACTPERSON';
64 --
65
66 BEGIN
67
68 -- Initialize the status to SUCCESS.
69
70 --
71 -- Debug Statements
72 --
73 --
74 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
75 --
76 IF l_debug_on IS NULL
77 THEN
78 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
79 END IF;
80 --
81 IF l_debug_on THEN
82 WSH_DEBUG_SV.push(l_module_name);
83 --
84 WSH_DEBUG_SV.log(l_module_name,'P_CARRIER_PARTY_ID',P_CARRIER_PARTY_ID);
85 WSH_DEBUG_SV.log(l_module_name,'P_STATUS',P_STATUS);
86 WSH_DEBUG_SV.log(l_module_name,'P_PERSON_NAME_PRE_ADJUNCT',P_PERSON_NAME_PRE_ADJUNCT);
87 WSH_DEBUG_SV.log(l_module_name,'P_PERSON_FIRST_NAME',P_PERSON_FIRST_NAME);
88 WSH_DEBUG_SV.log(l_module_name,'P_PERSON_LAST_NAME',P_PERSON_LAST_NAME);
89 END IF;
90 --
91
92 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
93
94 -- Initialize Messages.
95 fnd_msg_pub.initialize();
96
97
98 -- Put Information into per_rec.
99
100 l_per_rec.person_pre_name_adjunct := P_PERSON_NAME_PRE_ADJUNCT;
101 l_per_rec.person_first_name := P_PERSON_FIRST_NAME;
102 l_per_rec.person_last_name := P_PERSON_LAST_NAME;
103 l_per_rec.created_by_module := 'ORACLE_SHIPPING';
104 l_per_rec.party_rec.status := p_status;
105
106 l_position := 10;
107 l_call_procedure := 'Calling TCA API Create_Person';
108
109 -- Set the Autogenerate Party Number to 'Yes'.
110 -- Bug 7391414 Setting the profile option 'HZ GENERATE PARTY NUMBER' to Yes if it is No or Null
111 l_hz_profile_set := false;
112 l_hz_profile_option := fnd_profile.value('HZ_GENERATE_PARTY_NUMBER');
113
114 IF (l_hz_profile_option = 'N' or l_hz_profile_option is null ) THEN
115 IF l_debug_on THEN
116 WSH_DEBUG_SV.logmsg(l_module_name,'Setting profile option HZ_GENERATE_PARTY_NUMBER to Yes');
117 END IF;
118 fnd_profile.put('HZ_GENERATE_PARTY_NUMBER','Y');
119 l_hz_profile_set := true;
120 END IF;
121
122 --
123 -- Debug Statements
124 --
125 IF l_debug_on THEN
126 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit HZ_PARTY_V2PUB.CREATE_PERSON',WSH_DEBUG_SV.C_PROC_LEVEL);
127 END IF;
128 --
129
130 HZ_PARTY_V2PUB.Create_Person
131 (
132 p_init_msg_list => FND_API.G_TRUE,
133 p_person_rec => l_per_rec,
134 x_party_id => l_person_party_id,
135 x_party_number => l_party_number,
136 x_profile_id => l_profile_id,
137 x_return_status => l_return_status,
138 x_msg_count => l_msg_count,
139 x_msg_data => l_msg_data
140 );
141
142 -- Bug 7391414 Setting the profile option 'HZ GENERATE PARTY NUMBER' to previous value
143 IF l_hz_profile_set THEN
144 IF l_debug_on THEN
145 WSH_DEBUG_SV.logmsg(l_module_name,'Reverting the value of profile option HZ_GENERATE_PARTY_NUMBER');
146 END IF;
147 fnd_profile.put('HZ_GENERATE_PARTY_NUMBER',l_hz_profile_option);
148 END IF;
149
150 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
151 x_return_status := l_return_status;
152 RAISE HZ_FAIL_EXCEPTION;
153 END IF;
154
155 x_person_party_id := l_person_party_id;
156
157
158 -- Put Information into party_rel_rec.
159 l_rel_rec_type.relationship_type := 'EMPLOYMENT';
160 l_rel_rec_type.relationship_code := 'EMPLOYEE_OF';
161 l_rel_rec_type.subject_id := l_person_party_id;
162 l_rel_rec_type.subject_table_name := 'HZ_PARTIES';
163 l_rel_rec_type.subject_type := 'PERSON';
164 l_rel_rec_type.object_id := p_carrier_party_id;
165 l_rel_rec_type.object_table_name := 'HZ_PARTIES';
166 l_rel_rec_type.object_type := 'ORGANIZATION';
167 l_rel_rec_type.start_date := sysdate;
168 l_rel_rec_type.created_by_module := 'ORACLE_SHIPPING';
169 l_rel_rec_type.party_rec.status := P_STATUS;
170
171
172 -- Create relationship between contact person and organization.
173
174 l_position := 20;
175 l_call_procedure := 'Calling TCA API Create_Relationship';
176
177 --
178 -- Debug Statements
179 --
180 IF l_debug_on THEN
181 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit HZ_RELATIONSHIP_V2PUB.CREATE_RELATIONSHIP',WSH_DEBUG_SV.C_PROC_LEVEL);
182 END IF;
183 --
184
185 HZ_RELATIONSHIP_V2PUB.Create_Relationship
186 (
187 p_init_msg_list => FND_API.G_TRUE,
188 p_relationship_rec => l_rel_rec_type,
189 x_relationship_id => l_relationship_id,
190 x_party_id => l_relationship_party_id,
191 x_party_number => l_party_number,
192 x_return_status => l_return_status,
193 x_msg_count => l_msg_count,
194 x_msg_data => l_msg_data
195 );
196
197 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
198 x_return_status := l_return_status;
199 RAISE HZ_FAIL_EXCEPTION;
200 END IF;
201
202 x_relationship_party_id := l_relationship_party_id;
203 --
204 -- Debug Statements
205 --
206 IF l_debug_on THEN
207 WSH_DEBUG_SV.pop(l_module_name);
208 END IF;
209 --
210
211 EXCEPTION
212 WHEN HZ_FAIL_EXCEPTION THEN
213 x_exception_msg := l_msg_data;
214 x_position := l_position;
215 x_procedure := l_call_procedure;
216 x_sqlerr := sqlerrm;
217 x_sql_code := sqlcode;
218
219 --
220 -- Debug Statements
221 --
222 IF l_debug_on THEN
223 WSH_DEBUG_SV.logmsg(l_module_name,'HZ_FAIL_EXCEPTION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
224 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:HZ_FAIL_EXCEPTION');
225 END IF;
226 --
227
228 END CREATE_CONTACTPERSON;
229
230
231 PROCEDURE UPDATE_CONTACTPERSON (
232 P_CARRIER_PARTY_ID IN NUMBER,
233 P_PERSON_PARTY_ID IN OUT NOCOPY NUMBER,
234 P_PERSON_NAME_PRE_ADJUNCT IN VARCHAR2,
235 P_PERSON_FIRST_NAME IN VARCHAR2,
236 P_PERSON_LAST_NAME IN VARCHAR2,
237 P_STATUS IN VARCHAR2,
238 P_CONTACT_POINT_ID IN OUT NOCOPY NUMBER,
239 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
240 X_EXCEPTION_MSG OUT NOCOPY VARCHAR2,
241 X_POSITION OUT NOCOPY NUMBER,
242 X_PROCEDURE OUT NOCOPY VARCHAR2,
243 X_SQLERR OUT NOCOPY VARCHAR2,
244 X_SQL_CODE OUT NOCOPY VARCHAR2) IS
245
246 l_person_rec HZ_PARTY_V2PUB.person_rec_type;
247 l_return_status varchar2(100);
248 l_msg_count number;
249 l_msg_data varchar2(2000);
250 l_profile_id number;
251 l_object_version_number number;
252 HZ_FAIL_EXCEPTION exception;
253 l_position number;
254 l_call_procedure varchar2(100);
255
256 CURSOR Get_Object_Version_Number(p_person_party_id NUMBER) IS
257 select object_version_number
258 from hz_parties
259 where party_id = p_person_party_id;
260
261 --
262 l_debug_on BOOLEAN;
263 --
264 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_CONTACTPERSON';
265 --
266
267
268 BEGIN
269
270 --Initialize the status to SUCCESS.
271
272 --
273 -- Debug Statements
274 --
275 --
276 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
277 --
278 IF l_debug_on IS NULL
279 THEN
280 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
281 END IF;
282 --
283 IF l_debug_on THEN
284 WSH_DEBUG_SV.push(l_module_name);
285 --
286 WSH_DEBUG_SV.log(l_module_name,'P_CARRIER_PARTY_ID',P_CARRIER_PARTY_ID);
287 WSH_DEBUG_SV.log(l_module_name,'P_PERSON_PARTY_ID',P_PERSON_PARTY_ID);
288 WSH_DEBUG_SV.log(l_module_name,'P_CONTACT_POINT_ID',P_CONTACT_POINT_ID);
289 WSH_DEBUG_SV.log(l_module_name,'P_STATUS',P_STATUS);
290 WSH_DEBUG_SV.log(l_module_name,'P_PERSON_NAME_PRE_ADJUNCT',P_PERSON_NAME_PRE_ADJUNCT);
291 WSH_DEBUG_SV.log(l_module_name,'P_PERSON_FIRST_NAME',P_PERSON_FIRST_NAME);
292 WSH_DEBUG_SV.log(l_module_name,'P_PERSON_LAST_NAME',P_PERSON_LAST_NAME);
293 END IF;
294 --
295
296 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
297
298 --Initialize Messages.
299
300 fnd_msg_pub.initialize();
301
302 --Put Information into person_rec.
303
304 l_person_rec.person_pre_name_adjunct := nvl(p_person_name_pre_adjunct, fnd_api.g_miss_char);
305 l_person_rec.person_first_name := nvl(p_person_first_name, fnd_api.g_miss_char);
306 l_person_rec.person_last_name := p_person_last_name;
307 l_person_rec.party_rec.party_id := p_person_party_id;
308 l_person_rec.party_rec.status := p_status;
309
310 --Get Object_Version_Number for the Person.
311
312 OPEN Get_Object_Version_Number(p_person_party_id);
313 FETCH Get_Object_Version_Number INTO l_object_version_number;
314 CLOSE Get_Object_Version_Number ;
315
316 --Update the Person information.
317
318 l_position := 10;
319 l_call_procedure := 'Calling TCA API Update_Person';
320
321 --
322 -- Debug Statements
323 --
324 IF l_debug_on THEN
325 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit HZ_PARTY_V2PUB.UPDATE_PERSON',WSH_DEBUG_SV.C_PROC_LEVEL);
326 END IF;
327 --
328
329 HZ_PARTY_V2PUB.Update_Person
330 (
331 p_init_msg_list => FND_API.G_TRUE,
332 p_person_rec => l_person_rec,
333 p_party_object_version_number => l_object_version_number,
334 x_profile_id => l_profile_id,
335 x_return_status => l_return_status,
336 x_msg_count => l_msg_count,
337 x_msg_data => l_msg_data
338 );
339
340 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
341 x_return_status := l_return_status;
342 RAISE HZ_FAIL_EXCEPTION;
343 END IF;
344
345 --
346 -- Debug Statements
347 --
348 IF l_debug_on THEN
349 WSH_DEBUG_SV.pop(l_module_name);
350 END IF;
351 --
352
353 EXCEPTION
354 WHEN NO_DATA_FOUND THEN
355 x_exception_msg := 'EXCEPTION : No Data Found';
356 x_position := l_position;
357 x_procedure := l_call_procedure;
358 x_sqlerr := sqlerrm;
359 x_sql_code := sqlcode;
360
361 --
362 -- Debug Statements
363 --
364 IF l_debug_on THEN
365 WSH_DEBUG_SV.logmsg(l_module_name,'NO_DATA_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
366 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
367 END IF;
368 --
369
370
371 WHEN HZ_FAIL_EXCEPTION THEN
372 x_exception_msg := l_msg_data;
373 x_position := l_position;
374 x_procedure := l_call_procedure;
375 x_sqlerr := sqlerrm;
376 x_sql_code := sqlcode;
377
378 --
379 -- Debug Statements
380 --
381 IF l_debug_on THEN
382 WSH_DEBUG_SV.logmsg(l_module_name,'HZ_FAIL_EXCEPTION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
383 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:HZ_FAIL_EXCEPTION');
384 END IF;
385 --
386
387 WHEN OTHERS THEN
388 x_exception_msg := 'EXCEPTION : Others';
389 x_position := l_position;
390 x_procedure := l_call_procedure;
391 x_sqlerr := sqlerrm;
392 x_sql_code := sqlcode;
393
394 --
395 -- Debug Statements
396 --
397 IF l_debug_on THEN
398 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
399 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
400 END IF;
401 --
402
403 END UPDATE_CONTACTPERSON;
404
405 END WSH_CONTACT_PERSON_PKG;