[Home] [Help]
PACKAGE BODY: APPS.HZ_EXTRACT_PARTY_SITE_BO_PVT
Source
1 PACKAGE BODY HZ_EXTRACT_PARTY_SITE_BO_PVT AS
2 /*$Header: ARHEPSVB.pls 120.5 2006/06/13 20:28:42 acng noship $ */
3 /*
4 * This package contains the private APIs for logical party site.
5 * @rep:scope private
6 * @rep:product HZ
7 * @rep:displayname party site
8 * @rep:category BUSINESS_ENTITY HZ_PARTIE_SITES
9 * @rep:lifecycle active
10 * @rep:doccd 115hztig.pdf party site Get APIs
11 */
12
13 --------------------------------------
14 --
15 -- PROCEDURE get_party_site_bo
16 --
17 -- DESCRIPTION
18 -- Get a logical party site.
19 --
20 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
21 --
22 -- ARGUMENTS
23 -- IN:
24 -- p_init_msg_list Initialize message stack if it is set to FND_API.G_TRUE. Default is FND_API.G_FALSE.
25 -- p_party_id party ID.
26 -- p_party_site_id party site ID. If this id is not passed in, multiple site objects will be returned.
27 -- p_party_site_os party site orig system.
28 -- p_party_site_osr party site orig system reference.
29 --
30 -- OUT:
31 -- x_party_site_objs Logical party site records.
32 -- x_return_status Return status after the call. The status can
33 -- be fnd_api.g_ret_sts_success (success),
34 -- fnd_api.g_ret_sts_error (error),
35 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
36 -- x_msg_count Number of messages in message stack.
37 -- x_msg_data Message text if x_msg_count is 1.
38 --
39 -- NOTES
40 --
41 -- MODIFICATION HISTORY
42 --
43 --
44 -- 1-JUNE-2005 AWU Created.
45 --
46
47 /*
48 The Get party site API Procedure is a retrieval service that returns a full party site business object.
49 The user identifies a particular party site business object using the TCA identifier and/or
50 the object Source System information. Upon proper validation of the object,
51 the full party site business object is returned. The object consists of all data included within
52 the party site business object, at all embedded levels. This includes the set of all data stored
53 in the TCA tables for each embedded entity.
54
55 To retrieve the appropriate embedded business objects within the party site business object,
56 the Get procedure calls the equivalent procedure for the following embedded objects:
57
58 Embedded BO Mandatory Multiple Logical API Procedure Comments
59 Phone N Y get_phone_bos
60 Telex N Y get_telex_bos
61 Email N Y get_email_bos
62 Web N Y get_web_bos
63
64 To retrieve the appropriate embedded entities within the party site business object,
65 the Get procedure returns all records for the particular party site from these TCA entity tables:
66
67 Embedded TCA Entity Mandatory Multiple TCA Table Entities
68
69 Location Y N HZ_LOCATIONS
70 Party Site Y N HZ_PARTY_SITES
71 Party Site Use N Y HZ_PARTY_SITE_USES
72 Contact Preference N Y HZ_CONTACT_PREFERENCES
73 */
74
75
76 PROCEDURE get_party_site_bos(
77 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
78 p_party_id IN NUMBER,
79 p_party_site_id IN NUMBER,
80 p_action_type IN VARCHAR2 := NULL,
81 x_party_site_objs OUT NOCOPY HZ_PARTY_SITE_BO_TBL,
82 x_return_status OUT NOCOPY VARCHAR2,
83 x_msg_count OUT NOCOPY NUMBER,
84 x_msg_data OUT NOCOPY VARCHAR2
85 ) is
86
87 CURSOR C1 IS
88 SELECT HZ_PARTY_SITE_BO(
89 P_ACTION_TYPE,
90 NULL, -- COMMON_OBJ_ID
91 PS.PARTY_SITE_ID,
92 NULL, --PS.ORIG_SYSTEM,
93 NULL, --PS.ORIG_SYSTEM_REFERENCE,
94 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type('HZ_PARTIES',PS.PARTY_ID),
95 PS.PARTY_ID,
96 PS.PARTY_SITE_NUMBER,
97 PS.MAILSTOP,
98 PS.IDENTIFYING_ADDRESS_FLAG,
99 PS.STATUS,
100 PS.PARTY_SITE_NAME,
101 PS.ATTRIBUTE_CATEGORY,
102 PS.ATTRIBUTE1,
103 PS.ATTRIBUTE2,
104 PS.ATTRIBUTE3,
105 PS.ATTRIBUTE4,
106 PS.ATTRIBUTE5,
107 PS.ATTRIBUTE6,
108 PS.ATTRIBUTE7,
109 PS.ATTRIBUTE8,
110 PS.ATTRIBUTE9,
111 PS.ATTRIBUTE10,
112 PS.ATTRIBUTE11,
113 PS.ATTRIBUTE12,
114 PS.ATTRIBUTE13,
115 PS.ATTRIBUTE14,
116 PS.ATTRIBUTE15,
117 PS.ATTRIBUTE16,
118 PS.ATTRIBUTE17,
119 PS.ATTRIBUTE18,
120 PS.ATTRIBUTE19,
121 PS.ATTRIBUTE20,
122 PS.LANGUAGE,
123 PS.ADDRESSEE,
124 PS.PROGRAM_UPDATE_DATE,
125 PS.CREATED_BY_MODULE,
126 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(PS.CREATED_BY),
127 PS.CREATION_DATE,
128 PS.LAST_UPDATE_DATE,
129 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(PS.LAST_UPDATED_BY),
130 PS.ACTUAL_CONTENT_SOURCE,
131 PS.GLOBAL_LOCATION_NUMBER,
132 CAST(MULTISET (
133 SELECT HZ_ORIG_SYS_REF_OBJ(
134 NULL, --P_ACTION_TYPE,
135 ORIG_SYSTEM_REF_ID,
136 ORIG_SYSTEM,
137 ORIG_SYSTEM_REFERENCE,
138 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
139 OWNER_TABLE_ID,
140 STATUS,
141 REASON_CODE,
142 OLD_ORIG_SYSTEM_REFERENCE,
143 START_DATE_ACTIVE,
144 END_DATE_ACTIVE,
145 PROGRAM_UPDATE_DATE,
146 CREATED_BY_MODULE,
147 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
148 CREATION_DATE,
149 LAST_UPDATE_DATE,
150 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
151 ATTRIBUTE_CATEGORY,
152 ATTRIBUTE1,
153 ATTRIBUTE2,
154 ATTRIBUTE3,
155 ATTRIBUTE4,
156 ATTRIBUTE5,
157 ATTRIBUTE6,
158 ATTRIBUTE7,
159 ATTRIBUTE8,
160 ATTRIBUTE9,
161 ATTRIBUTE10,
162 ATTRIBUTE11,
163 ATTRIBUTE12,
164 ATTRIBUTE13,
165 ATTRIBUTE14,
166 ATTRIBUTE15,
167 ATTRIBUTE16,
168 ATTRIBUTE17,
169 ATTRIBUTE18,
170 ATTRIBUTE19,
171 ATTRIBUTE20)
172 FROM HZ_ORIG_SYS_REFERENCES OSR
173 WHERE
174 OSR.OWNER_TABLE_ID = PS.PARTY_SITE_ID
175 AND OWNER_TABLE_NAME = 'HZ_PARTY_SITES'
176 AND STATUS = 'A') AS HZ_ORIG_SYS_REF_OBJ_TBL),
177 HZ_EXT_ATTRIBUTE_OBJ_TBL(),
178 HZ_LOCATION_OBJ(
179 P_ACTION_TYPE,
180 NULL, -- COMMON_OBJ_ID
181 LOC.LOCATION_ID,
182 NULL, --ORIG_SYSTEM,
183 NULL, --ORIG_SYSTEM_REFERENCE,
184 LOC.COUNTRY,
185 LOC.ADDRESS1,
186 LOC.ADDRESS2,
187 LOC.ADDRESS3,
188 LOC.ADDRESS4,
189 LOC.CITY,
190 LOC.POSTAL_CODE,
191 LOC.STATE,
192 LOC.PROVINCE,
193 LOC.COUNTY,
194 LOC.ADDRESS_KEY,
195 LOC.ADDRESS_STYLE,
196 LOC.VALIDATED_FLAG,
197 LOC.ADDRESS_LINES_PHONETIC,
198 /* LOC.PO_BOX_NUMBER,
199 LOC.HOUSE_NUMBER,
200 LOC.STREET_SUFFIX,
201 LOC.STREET,
202 LOC.STREET_NUMBER,
203 LOC.FLOOR,
204 LOC.SUITE, */
205 LOC.POSTAL_PLUS4_CODE,
206 LOC.POSITION,
207 LOC.LOCATION_DIRECTIONS,
208 LOC.ADDRESS_EFFECTIVE_DATE,
209 LOC.ADDRESS_EXPIRATION_DATE,
210 LOC.CLLI_CODE,
211 LOC.LANGUAGE,
212 LOC.SHORT_DESCRIPTION,
213 LOC.DESCRIPTION,
214 LOC_HIERARCHY_ID,
215 LOC.SALES_TAX_GEOCODE,
216 LOC.SALES_TAX_INSIDE_CITY_LIMITS,
217 LOC.FA_LOCATION_ID,
218 LOC.TIMEZONE_ID,
219 LOC.ATTRIBUTE_CATEGORY,
220 LOC.ATTRIBUTE1,
221 LOC.ATTRIBUTE2,
222 LOC.ATTRIBUTE3,
223 LOC.ATTRIBUTE4,
224 LOC.ATTRIBUTE5,
225 LOC.ATTRIBUTE6,
226 LOC.ATTRIBUTE7,
227 LOC.ATTRIBUTE8,
228 LOC.ATTRIBUTE9,
229 LOC.ATTRIBUTE10,
230 LOC.ATTRIBUTE11,
231 LOC.ATTRIBUTE12,
232 LOC.ATTRIBUTE13,
233 LOC.ATTRIBUTE14,
234 LOC.ATTRIBUTE15,
235 LOC.ATTRIBUTE16,
236 LOC.ATTRIBUTE17,
237 LOC.ATTRIBUTE18,
238 LOC.ATTRIBUTE19,
239 LOC.ATTRIBUTE20,
240 LOC.PROGRAM_UPDATE_DATE,
241 LOC.CREATED_BY_MODULE,
242 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LOC.CREATED_BY),
243 LOC.CREATION_DATE,
244 LOC.LAST_UPDATE_DATE,
245 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LOC.LAST_UPDATED_BY),
246 LOC.ACTUAL_CONTENT_SOURCE,
247 LOC.DELIVERY_POINT_CODE,
248 LOC.GEOMETRY_STATUS_CODE,
249 LOC.GEOMETRY,
250 HZ_ORIG_SYS_REF_OBJ_TBL(),
251 HZ_EXT_ATTRIBUTE_OBJ_TBL()),
252 CAST(MULTISET (
253 SELECT
254 HZ_PARTY_SITE_USE_OBJ(
255 P_ACTION_TYPE,
256 NULL, -- COMMON_OBJ_ID
257 PARTY_SITE_USE_ID,
258 COMMENTS,
259 SITE_USE_TYPE,
260 PARTY_SITE_ID,
261 PRIMARY_PER_TYPE,
262 STATUS,
263 PROGRAM_UPDATE_DATE,
264 CREATED_BY_MODULE,
265 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
266 CREATION_DATE,
267 LAST_UPDATE_DATE,
268 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY))
269 FROM HZ_PARTY_SITE_USES
270 WHERE PARTY_SITE_ID = PS.PARTY_SITE_ID) AS HZ_PARTY_SITE_USE_OBJ_TBL),
271 HZ_PHONE_CP_BO_TBL(),
272 HZ_TELEX_CP_BO_TBL(),
273 HZ_EMAIL_CP_BO_TBL(),
274 HZ_WEB_CP_BO_TBL(),
275 CAST(MULTISET (
276 SELECT HZ_CONTACT_PREF_OBJ(
277 P_ACTION_TYPE,
278 NULL, -- COMMON_OBJ_ID
279 CONTACT_PREFERENCE_ID,
280 'PARTY_SITE',
281 CONTACT_LEVEL_TABLE_ID,
282 CONTACT_TYPE,
283 PREFERENCE_CODE,
284 PREFERENCE_TOPIC_TYPE,
285 PREFERENCE_TOPIC_TYPE_ID,
286 PREFERENCE_TOPIC_TYPE_CODE,
287 PREFERENCE_START_DATE,
288 PREFERENCE_END_DATE,
289 PREFERENCE_START_TIME_HR,
290 PREFERENCE_END_TIME_HR,
291 PREFERENCE_START_TIME_MI,
292 PREFERENCE_END_TIME_MI,
293 MAX_NO_OF_INTERACTIONS,
294 MAX_NO_OF_INTERACT_UOM_CODE,
295 REQUESTED_BY,
296 REASON_CODE,
297 STATUS,
298 PROGRAM_UPDATE_DATE,
299 CREATED_BY_MODULE,
300 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
301 CREATION_DATE,
302 LAST_UPDATE_DATE,
303 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY))
304 FROM HZ_CONTACT_PREFERENCES CPREF
305 WHERE CONTACT_LEVEL_TABLE = 'HZ_PARTY_SITES'
306 AND CONTACT_LEVEL_TABLE_ID = PS.PARTY_SITE_ID) AS HZ_CONTACT_PREF_OBJ_TBL))
307 FROM HZ_PARTY_SITES PS, HZ_LOCATIONS LOC WHERE PS.LOCATION_ID = LOC.LOCATION_ID
308 AND ((P_PARTY_SITE_ID IS NULL AND PARTY_ID = P_PARTY_ID)
309 OR (P_PARTY_SITE_ID IS NOT NULL AND PARTY_SITE_ID = P_PARTY_SITE_ID));
310
311
312
313 l_debug_prefix VARCHAR2(30) := '';
314 BEGIN
315
316
317 -- initialize API return status to success.
318 x_return_status := FND_API.G_RET_STS_SUCCESS;
319
320 -- Initialize message list if p_init_msg_list is set to TRUE
321 IF FND_API.to_Boolean(p_init_msg_list) THEN
322 FND_MSG_PUB.initialize;
323 END IF;
324
325
326 -- Debug info.
327 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
328 hz_utility_v2pub.debug(p_message=>'get_party_site_bos(+)',
329 p_prefix=>l_debug_prefix,
330 p_msg_level=>fnd_log.level_procedure);
331 END IF;
332
333
334 x_party_site_objs := HZ_PARTY_SITE_BO_TBL();
335 open c1;
336 fetch c1 BULK COLLECT into x_party_site_objs;
337 close c1;
338
339 for i in 1..x_party_site_objs.count loop
340
341 hz_extract_cont_point_bo_pvt.get_phone_bos
342 (p_init_msg_list => fnd_api.g_false,
343 p_phone_id => null,
344 p_parent_id => x_party_site_objs(i).party_site_id,
345 p_parent_table_name => 'HZ_PARTY_SITES',
346 p_action_type => p_action_type,
347 x_phone_objs => x_party_site_objs(i).phone_objs,
348 x_return_status => x_return_status,
349 x_msg_count => x_msg_count,
350 x_msg_data => x_msg_data);
351
352 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
353 RAISE FND_API.G_EXC_ERROR;
354 END IF;
355
356 hz_extract_ext_attri_bo_pvt.get_ext_attribute_bos
357 (p_init_msg_list => fnd_api.g_false,
358 p_ext_object_id => x_party_site_objs(i).party_site_id,
359 p_ext_object_name => 'HZ_PARTY_SITES',
360 p_action_type => p_action_type,
361 x_ext_attribute_objs => x_party_site_objs(i).ext_attributes_objs,
362 x_return_status => x_return_status,
363 x_msg_count => x_msg_count,
364 x_msg_data => x_msg_data);
365
366 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
367 RAISE FND_API.G_EXC_ERROR;
368 END IF;
369
370 hz_extract_cont_point_bo_pvt.get_telex_bos
371 (p_init_msg_list => fnd_api.g_false,
372 p_telex_id => null,
376 x_telex_objs => x_party_site_objs(i).telex_objs,
373 p_parent_id => x_party_site_objs(i).party_site_id,
374 p_parent_table_name => 'HZ_PARTY_SITES',
375 p_action_type => p_action_type,
377 x_return_status => x_return_status,
378 x_msg_count => x_msg_count,
379 x_msg_data => x_msg_data);
380
381 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
382 RAISE FND_API.G_EXC_ERROR;
383 END IF;
384
385
386 hz_extract_cont_point_bo_pvt.get_email_bos
387 (p_init_msg_list => fnd_api.g_false,
388 p_email_id => null,
389 p_parent_id => x_party_site_objs(i).party_site_id,
390 p_parent_table_name => 'HZ_PARTY_SITES',
391 p_action_type => p_action_type,
392 x_email_objs => x_party_site_objs(i).email_objs,
393 x_return_status => x_return_status,
394 x_msg_count => x_msg_count,
395 x_msg_data => x_msg_data);
396
397 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
398 RAISE FND_API.G_EXC_ERROR;
399 END IF;
400
401
402 hz_extract_cont_point_bo_pvt.get_web_bos
403 (p_init_msg_list => fnd_api.g_false,
404 p_web_id => null,
405 p_parent_id => x_party_site_objs(i).party_site_id,
406 p_parent_table_name => 'HZ_PARTY_SITES',
407 p_action_type => p_action_type,
408 x_web_objs => x_party_site_objs(i).web_objs,
409 x_return_status => x_return_status,
410 x_msg_count => x_msg_count,
411 x_msg_data => x_msg_data);
412
413 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
414 RAISE FND_API.G_EXC_ERROR;
415 END IF;
416
417 -- SSM for location obj
418 HZ_EXTRACT_ORIG_SYS_REF_BO_PVT.get_orig_sys_ref_bos
419 (p_init_msg_list => fnd_api.g_false,
420 p_owner_table_id => x_party_site_objs(i).location_obj.location_id,
421 p_owner_table_name => 'HZ_LOCATIONS',
422 p_action_type => NULL, --p_action_type,
423 x_orig_sys_ref_objs => x_party_site_objs(i).location_obj.orig_sys_objs,
424 x_return_status => x_return_status,
425 x_msg_count => x_msg_count,
426 x_msg_data => x_msg_data);
427
428 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
429 RAISE FND_API.G_EXC_ERROR;
430 END IF;
431
432 -- Ext attributes for location obj
433
434 hz_extract_ext_attri_bo_pvt.get_ext_attribute_bos
435 (p_init_msg_list => fnd_api.g_false,
436 p_ext_object_id => x_party_site_objs(i).location_obj.location_id,
437 p_ext_object_name => 'HZ_LOCATIONS',
438 p_action_type => p_action_type,
439 x_ext_attribute_objs => x_party_site_objs(i).location_obj.ext_attributes_objs,
440 x_return_status => x_return_status,
441 x_msg_count => x_msg_count,
442 x_msg_data => x_msg_data);
443
444 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
445 RAISE FND_API.G_EXC_ERROR;
446 END IF;
447
448 end loop;
449
450 -- Debug info.
451 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
452 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
453 p_msg_data=>x_msg_data,
454 p_msg_type=>'WARNING',
455 p_msg_level=>fnd_log.level_exception);
456 END IF;
457
458 -- Debug info.
459 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
460 hz_utility_v2pub.debug(p_message=>'get_party_site_bos (-)',
461 p_prefix=>l_debug_prefix,
462 p_msg_level=>fnd_log.level_procedure);
463 END IF;
464
465
466 EXCEPTION
467
468 WHEN fnd_api.g_exc_error THEN
469 x_return_status := fnd_api.g_ret_sts_error;
470
471 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
472 p_count => x_msg_count,
473 p_data => x_msg_data);
474
475 -- Debug info.
476 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
477 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
478 p_msg_data=>x_msg_data,
479 p_msg_type=>'ERROR',
480 p_msg_level=>fnd_log.level_error);
481 END IF;
482 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
483 hz_utility_v2pub.debug(p_message=>'get_party_site_bos(-)',
484 p_prefix=>l_debug_prefix,
485 p_msg_level=>fnd_log.level_procedure);
486 END IF;
487 WHEN fnd_api.g_exc_unexpected_error THEN
488 x_return_status := fnd_api.g_ret_sts_unexp_error;
489
490 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
491 p_count => x_msg_count,
492 p_data => x_msg_data);
493
494 -- Debug info.
495 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
496 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
497 p_msg_data=>x_msg_data,
498 p_msg_type=>'UNEXPECTED ERROR',
499 p_msg_level=>fnd_log.level_error);
500 END IF;
501 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
502 hz_utility_v2pub.debug(p_message=>'get_party_site_bos(-)',
503 p_prefix=>l_debug_prefix,
504 p_msg_level=>fnd_log.level_procedure);
505 END IF;
506 WHEN OTHERS THEN
507 x_return_status := fnd_api.g_ret_sts_unexp_error;
508
509 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
510 fnd_message.set_token('ERROR' ,SQLERRM);
511 fnd_msg_pub.add;
512
513 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
514 p_count => x_msg_count,
515 p_data => x_msg_data);
516
517 -- Debug info.
518 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
519 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
520 p_msg_data=>x_msg_data,
521 p_msg_type=>'SQL ERROR',
522 p_msg_level=>fnd_log.level_error);
523 END IF;
524 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
525 hz_utility_v2pub.debug(p_message=>'get_party_site_bos(-)',
526 p_prefix=>l_debug_prefix,
527 p_msg_level=>fnd_log.level_procedure);
528 END IF;
529
530 end;
531
532
533 END HZ_EXTRACT_PARTY_SITE_BO_PVT;