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