[Home] [Help]
PACKAGE BODY: APPS.WSH_CARRIER_CONTACT_INFO_PKG
Source
4 /*============================================================================
1 PACKAGE BODY WSH_CARRIER_CONTACT_INFO_PKG as
2 /* $Header: WSHCITHB.pls 120.2 2006/06/09 09:13:32 jnpinto noship $ */
3
5 Procedure: CREATE_CONTACTINFO
6 Purpose : Creates Contact points for the person working as employee in Carrier.
7 =============================================================================*/
8
9 --
10 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_CARRIER_CONTACT_INFO_PKG';
11 --
12
13 PROCEDURE CREATE_CONTACTINFO
14 (
15 P_RELATIONSHIP_PARTY_ID IN NUMBER,
16 P_COUNTRY_CODE IN VARCHAR2,
17 P_AREA_CODE IN VARCHAR2,
18 P_PHONE_NUMBER IN VARCHAR2,
19 P_EXTENSION IN VARCHAR2,
20 P_PRIMARY IN VARCHAR2,
21 P_STATUS IN VARCHAR2,
22 P_CONTACT_TYPE IN VARCHAR2,
23 X_CONTACT_POINT_ID OUT NOCOPY NUMBER,
24 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
25 X_EXCEPTION_MSG OUT NOCOPY VARCHAR2,
26 X_SQLERR OUT NOCOPY VARCHAR2,
27 X_SQL_CODE OUT NOCOPY VARCHAR2,
28 X_POSITION OUT NOCOPY NUMBER,
29 X_PROCEDURE OUT NOCOPY VARCHAR2
30 )
31 IS
32
33 ------------------------
34 -- General Declarations.
35 ------------------------
36
37 l_return_status varchar2(100);
38 l_position number;
39 l_procedure varchar2(100);
40 l_msg_count number;
41 l_msg_data varchar2(2000);
42 l_party_number varchar2(100);
43 l_profile_id number;
44 l_exception_msg varchar2(1000);
45 HZ_FAIL_EXCEPTION exception;
46
47 l_party_relationship_id number;
48 l_relationship_party_id number;
49
50 ------------------------------------------------------------------
51 -- Declarations for Contact Point Creation for the above Relation.
52 ------------------------------------------------------------------
53
54 l_contact_point_id NUMBER;
55 l_contact_points_rec_type hz_contact_point_v2pub.contact_point_rec_type;
56 l_phone_rec_type hz_contact_point_v2pub.phone_rec_type;
57
58 --
59 l_debug_on BOOLEAN;
60 --
61 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_CONTACTINFO';
62 --
63
64 BEGIN
65
66 -- Initialize the status to SUCCESS.
67
68 --
69 -- Debug Statements
70 --
71 --
72 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
73 --
74 IF l_debug_on IS NULL
75 THEN
76 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
77 END IF;
78 --
79 IF l_debug_on THEN
80 WSH_DEBUG_SV.push(l_module_name);
81 --
82 WSH_DEBUG_SV.log(l_module_name,'P_RELATIONSHIP_PARTY_ID',P_RELATIONSHIP_PARTY_ID);
83 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
84 WSH_DEBUG_SV.log(l_module_name,'P_AREA_CODE',P_AREA_CODE);
85 WSH_DEBUG_SV.log(l_module_name,'P_PHONE_NUMBER',P_PHONE_NUMBER);
86 WSH_DEBUG_SV.log(l_module_name,'P_EXTENSION',P_EXTENSION);
87 WSH_DEBUG_SV.log(l_module_name,'P_PRIMARY',P_PRIMARY);
88 WSH_DEBUG_SV.log(l_module_name,'P_CONTACT_TYPE',P_CONTACT_TYPE);
89 -- Corrected Bug 5298638 - parameter value of contact type to be shown in log file.
90 WSH_DEBUG_SV.log(l_module_name,'P_STATUS',P_STATUS);
91 -- Added Bug 5298638 - parameter value of status to be shown in log file.
92 END IF;
93 --
94
95 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
96
97 -- Initialize Messages.
98
99 fnd_msg_pub.initialize();
100
101
102 -- Put information into email_rec_type, phone_rec_type and contact_points.
103
104 l_phone_rec_type.phone_number := P_PHONE_NUMBER;
105 l_phone_rec_type.phone_area_code := P_AREA_CODE;
106 l_phone_rec_type.phone_country_code := P_COUNTRY_CODE;
107 l_phone_rec_type.phone_extension := P_EXTENSION;
108 l_phone_rec_type.phone_line_type := P_CONTACT_TYPE;
109
110 l_contact_points_rec_type.contact_point_type := 'PHONE';
111 l_contact_points_rec_type.owner_table_name := 'HZ_PARTIES';
112 l_contact_points_rec_type.owner_table_id := p_relationship_party_id;
113 l_contact_points_rec_type.primary_flag := p_primary;
114 l_contact_points_rec_type.status := p_status;
115 l_contact_points_rec_type.created_by_module := 'ORACLE_SHIPPING';
116
117 -- Create the contact point information.
118
119 l_position := 10;
120 l_procedure := 'Calling TCA API Create_Contact_Points';
121
122 --
123 -- Debug Statements
124 --
125 IF l_debug_on THEN
126 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit HZ_CONTACT_POINT_V2PUB.Create_Contact_Point',WSH_DEBUG_SV.C_PROC_LEVEL);
127 END IF;
128 --
129
130 HZ_CONTACT_POINT_V2PUB.Create_Contact_Point
131 (
132 p_init_msg_list => FND_API.G_TRUE,
133 p_contact_point_rec => l_contact_points_rec_type,
134 p_phone_rec => l_phone_rec_type,
135 x_contact_point_id => l_contact_point_id,
136 x_return_status => l_return_status,
137 x_msg_count => l_msg_count,
138 x_msg_data => l_msg_data
139 );
140
141
142 --In case of errors, set x_return_status and raise exception.
143 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
144 x_return_status := l_return_status;
145 RAISE HZ_FAIL_EXCEPTION;
146 END IF;
147
148 --Get the contact point ID.
149 x_contact_point_id := l_contact_point_id;
150
151 --
152 -- Debug Statements
153 --
154 IF l_debug_on THEN
155 WSH_DEBUG_SV.pop(l_module_name);
156 END IF;
157 --
158
159
160 EXCEPTION
161 WHEN HZ_FAIL_EXCEPTION THEN
162 x_exception_msg := l_msg_data;
163 x_position := l_position;
164 x_procedure := l_procedure;
165 x_sqlerr := sqlerrm;
166 x_sql_code := sqlcode;
167
168 --
169 -- Debug Statements
170 --
171 IF l_debug_on THEN
172 WSH_DEBUG_SV.logmsg(l_module_name,'HZ_FAIL_EXCEPTION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
173 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:HZ_FAIL_EXCEPTION');
174 END IF;
175 --
176
177 END CREATE_CONTACTINFO;
178
179
180 PROCEDURE UPDATE_CONTACTINFO
181 (
182 P_CARRIER_PARTY_ID IN NUMBER,
183 P_CONTACT_POINT_ID IN NUMBER,
184 P_COUNTRY_CODE IN VARCHAR2,
185 P_AREA_CODE IN VARCHAR2,
186 P_PHONE_NUMBER IN VARCHAR2,
187 P_EXTENSION IN VARCHAR2,
188 P_PRIMARY IN VARCHAR2,
189 P_STATUS IN VARCHAR2,
190 P_CONTACT_TYPE IN VARCHAR2,--Added parameter for contact type - bug 5298638
191 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
192 X_EXCEPTION_MSG OUT NOCOPY VARCHAR2,
193 X_POSITION OUT NOCOPY NUMBER,
194 X_PROCEDURE OUT NOCOPY VARCHAR2,
195 X_SQLERR OUT NOCOPY VARCHAR2,
196 X_SQL_CODE OUT NOCOPY VARCHAR2 ) IS
197
198 l_contact_points_rec_type hz_contact_point_v2pub.contact_point_rec_type;
199 l_phone_rec_type hz_contact_point_v2pub.phone_rec_type;
200 l_return_status varchar2(100);
201 l_msg_count number;
202 l_msg_data varchar2(2000);
203 l_exception_msg varchar2(1000);
204 HZ_FAIL_EXCEPTION exception;
205 l_status varchar2(100);
206 l_contact_point_id NUMBER;
207 l_position number;
208 l_procedure varchar2(50);
209 l_object_version_number number;
210 l_party_relationship_id number;
211 l_relationship_party_id number;
212
213 CURSOR Get_Object_Version_Number(p_contact_point_id NUMBER) IS
214 select object_version_number
215 from hz_contact_points
216 where contact_point_id = p_contact_point_id;
217
218 --
219 l_debug_on BOOLEAN;
220 --
221 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_CONTACTINFO';
222 --
223
224 BEGIN
225
226 --Initialize the status to SUCCESS.
227
228 --
229 -- Debug Statements
230 --
231 --
232 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
233 --
234 IF l_debug_on IS NULL
235 THEN
236 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
237 END IF;
238 --
239 IF l_debug_on THEN
240 WSH_DEBUG_SV.push(l_module_name);
241 --
242 WSH_DEBUG_SV.log(l_module_name,'P_CARRIER_PARTY_ID',P_CARRIER_PARTY_ID);
243 WSH_DEBUG_SV.log(l_module_name,'P_CONTACT_POINT_ID',P_CONTACT_POINT_ID);
244 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
245 WSH_DEBUG_SV.log(l_module_name,'P_AREA_CODE',P_AREA_CODE);
246 WSH_DEBUG_SV.log(l_module_name,'P_PHONE_NUMBER',P_PHONE_NUMBER);
247 WSH_DEBUG_SV.log(l_module_name,'P_EXTENSION',P_EXTENSION);
248 WSH_DEBUG_SV.log(l_module_name,'P_PRIMARY',P_PRIMARY);
249 WSH_DEBUG_SV.log(l_module_name,'P_CONTACT_TYPE',P_CONTACT_TYPE);
250 --Corrected Bug 5298638 - Parameter value of the contact type to be shown in log file.
251 WSH_DEBUG_SV.log(l_module_name,'P_STATUS',P_STATUS);
252 --Added Bug 5298638 - Parameter value of the status to be shown in log file.
253 END IF;
254 --
255
256 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
257
258 --Initialize Messages.
259
260 fnd_msg_pub.initialize();
261
262
263 --Put Information into email_rec, phone_rec.
264
265 l_phone_rec_type.phone_number := P_PHONE_NUMBER;
266 l_phone_rec_type.phone_area_code := nvl(P_AREA_CODE, fnd_api.g_miss_char);
267 l_phone_rec_type.phone_country_code := nvl(P_COUNTRY_CODE, fnd_api.g_miss_char);
268 l_phone_rec_type.phone_extension := nvl(P_EXTENSION, fnd_api.g_miss_char);
269 l_phone_rec_type.phone_line_type := P_CONTACT_TYPE;
270 --Corrected Bug 5298638 - Instead of defaulting 'GEN' contact_type entered will be passed.
271
272 l_contact_points_rec_type.contact_point_type := 'PHONE';
273 l_contact_points_rec_type.owner_table_name := 'HZ_PARTIES';
274 l_contact_points_rec_type.primary_flag := p_primary;
275 l_contact_points_rec_type.status := p_status;
276 l_contact_points_rec_type.contact_point_id := p_contact_point_id;
277
278 --Get last_update_date for the Contact Point.
279
280 OPEN Get_Object_Version_Number (p_contact_point_id);
281 FETCH Get_Object_Version_Number INTO l_object_version_number;
282 CLOSE Get_Object_Version_Number;
283
284 --Update the contact point information.
285
286 l_position := 10;
287 l_procedure := 'Calling Update_Contact_Points';
288
289 --
290 -- Debug Statements
291 --
292 IF l_debug_on THEN
293 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit HZ_CONTACT_POINT_V2PUB.Update_Contact_Point',WSH_DEBUG_SV.C_PROC_LEVEL);
294 END IF;
295 --
296
297 HZ_CONTACT_POINT_V2PUB.Update_Contact_Point
298 (
299 p_init_msg_list => FND_API.G_TRUE,
300 p_contact_point_rec => l_contact_points_rec_type,
301 p_phone_rec => l_phone_rec_type,
302 p_object_version_number => l_object_version_number,
303 x_return_status => l_return_status,
304 x_msg_count => l_msg_count,
305 x_msg_data => l_msg_data
306 );
307
308 --In case of errors, set x_return_status and raise exception.
309
310 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
311 x_return_status := l_return_status;
312 RAISE HZ_FAIL_EXCEPTION;
313 END IF;
314
315 --
316 -- Debug Statements
317 --
318 IF l_debug_on THEN
319 WSH_DEBUG_SV.pop(l_module_name);
320 END IF;
321 --
322
323 EXCEPTION
324 WHEN NO_DATA_FOUND THEN
325 x_exception_msg := 'EXCEPTION : No Data Found';
326 x_position := l_position;
327 x_procedure := l_procedure;
328 x_sqlerr := sqlerrm;
329 x_sql_code := sqlcode;
330
331 --
332 -- Debug Statements
333 --
334 IF l_debug_on THEN
335 WSH_DEBUG_SV.logmsg(l_module_name,'NO_DATA_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
336 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
337 END IF;
338 --
339
340
341 WHEN HZ_FAIL_EXCEPTION THEN
342 x_exception_msg := l_msg_data;
343 x_position := l_position;
344 x_procedure := l_procedure;
345 x_sqlerr := sqlerrm;
346 x_sql_code := sqlcode;
347
348 --
349 -- Debug Statements
350 --
351 IF l_debug_on THEN
352 WSH_DEBUG_SV.logmsg(l_module_name,'HZ_FAIL_EXCEPTION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
353 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:HZ_FAIL_EXCEPTION');
354 END IF;
355 --
356
357 WHEN OTHERS THEN
358 x_exception_msg := 'EXCEPTION : Others';
359 x_position := l_position;
360 x_procedure := l_procedure;
361 x_sqlerr := sqlerrm;
362 x_sql_code := sqlcode;
363
364 --
365 -- Debug Statements
366 --
367 IF l_debug_on THEN
368 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
369 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
370 END IF;
371 --
372
373 END UPDATE_CONTACTINFO;
374
375 END WSH_CARRIER_CONTACT_INFO_PKG;