1 PACKAGE BODY HZ_EXTRACT_BO_UTIL_PVT AS
2 /*$Header: ARHEUTVB.pls 120.11 2006/06/13 20:27:13 acng noship $ */
3 /*
4 * @rep:scope public
5 * @rep:product HZ
6 * @rep:displayname Contact Point
7 * @rep:category BUSINESS_ENTITY
8 * @rep:lifecycle active
9 * @rep:doccd 115hztig.pdf Oracle Trading Community Architecture Technical Implementation Guide
10 */
11
12 procedure validate_event_id(p_event_id in number,
13 p_party_id in number,
14 p_event_type in varchar2,
15 p_bo_code in varchar2,
16 x_return_status out nocopy varchar2) is
17
18 cursor c1 is
19 SELECT 'Y'
20 FROM HZ_BUS_OBJ_TRACKING
21 WHERE parent_bo_code is null
22 and event_id = p_event_id
23 and child_entity_name = 'HZ_PARTIES'
24 and child_id = nvl(p_party_id, child_id)
25 and child_bo_code = p_bo_code
26 and nvl(parent_event_flag, p_event_type) = p_event_type
27 and rownum = 1;
28
29 l_valid_flag varchar2(1);
30 begin
31 -- initialize API return status to success.
32 x_return_status := FND_API.G_RET_STS_SUCCESS;
33
34 open c1;
35 fetch c1 into l_valid_flag;
36 close c1;
37
38 if NVL(l_valid_flag, 'N') <> 'Y'
39 then
40 if p_party_id is null
41 then
42 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_EVENT_ID');
43 FND_MSG_PUB.ADD;
44 x_return_status := FND_API.G_RET_STS_ERROR;
45 RAISE FND_API.G_EXC_ERROR;
46 else
47 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_EVENT_OBJ_ID');
48 FND_MSG_PUB.ADD;
49 x_return_status := FND_API.G_RET_STS_ERROR;
50 RAISE FND_API.G_EXC_ERROR;
51 end if;
52 end if;
53
54
55 end;
56
57 FUNCTION get_parent_object_type(
58 p_parent_table_name IN VARCHAR2,
59 p_parent_id IN NUMBER
60 ) RETURN VARCHAR2 IS
61
62 l_party_type varchar2(30) := null;
63
64 cursor c1 is
65 select party_type
66 from hz_parties
67 where party_id = p_parent_id;
68
69 cursor c2 is
70 select party_type
71 from hz_parties p, hz_cust_accounts ca
72 where p.party_id = ca.party_id
73 and ca.cust_account_id = p_parent_id;
74
75 cursor c_cp is
76 select contact_point_type
77 from HZ_CONTACT_POINTS
78 where contact_point_id = p_parent_id;
79
80 BEGIN
81 if p_parent_table_name = 'HZ_PARTIES'
82 then
83 open c1;
84 fetch c1 into l_party_type;
85 close c1;
86 elsif p_parent_table_name = 'HZ_CONTACT_POINTS'
87 then
88 open c_cp;
89 fetch c_cp into l_party_type;
90 close c_cp;
91 elsif p_parent_table_name = 'HZ_CUST_ACCOUNTS'
92 then
93 open c2;
94 fetch c2 into l_party_type;
95 close c2;
96 end if;
97
98 -- Base on owner_table_name to return HZ_BUSINESS_OBJECTS lookup code
99 IF(p_parent_table_name = 'HZ_PARTY_SITES') THEN
100 RETURN 'PARTY_SITE';
101 ELSIF(p_parent_table_name = 'HZ_PARTIES') THEN
102 IF(l_party_type = 'ORGANIZATION') THEN
103 RETURN 'ORG';
104 ELSIF(l_party_type = 'PERSON') THEN
105 RETURN 'PERSON';
106 ELSIF(l_party_type = 'PARTY_RELATIONSHIP') THEN
107 RETURN 'ORG_CONTACT';
108 END IF;
109 ELSIF(p_parent_table_name = 'HZ_CONTACT_POINTS') THEN
110 RETURN l_party_type;
111 ELSIF(p_parent_table_name = 'HZ_CUST_ACCOUNTS') THEN
112 IF(l_party_type = 'ORGANIZATION') THEN
113 RETURN 'ORG_CUST';
114 ELSIF(l_party_type = 'PERSON') THEN
115 RETURN 'PERSON_CUST';
116 ELSIF(l_party_type IS NULL) THEN
117 RETURN 'CUST_ACCT';
118 END IF;
119 ELSIF(p_parent_table_name = 'HZ_CUST_ACCOUNT_ROLES') THEN
120 RETURN 'CUST_ACCT_CONTACT';
121 ELSIF(p_parent_table_name = 'HZ_CUST_ACCT_SITES_ALL') THEN
122 RETURN 'CUST_ACCT_SITE';
123 END IF;
124 RETURN NULL;
125 END get_parent_object_type;
126
127 FUNCTION get_user_name(p_user_id in number) return varchar2 is
128 cursor get_user_name_csr is
129 select user_name
130 from fnd_user
131 where user_id = p_user_id;
132
133 l_name varchar2(100);
134 begin
135 open get_user_name_csr;
136 fetch get_user_name_csr into l_name;
137 close get_user_name_csr;
138 return l_name;
139 end;
140
141
142 -- Central procedure for getting root event id.
143
144 procedure get_bo_root_ids(
145 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
146 p_event_id IN NUMBER,
147 x_obj_root_ids OUT NOCOPY BO_ID_TBL,
148 x_return_status OUT NOCOPY VARCHAR2,
149 x_msg_count OUT NOCOPY NUMBER,
150 x_msg_data OUT NOCOPY VARCHAR2
151 ) is
152 l_debug_prefix VARCHAR2(30) := '';
153
154 cursor c1 is
155 SELECT child_id
156 FROM HZ_BUS_OBJ_TRACKING
157 WHERE parent_bo_code is null
158 and event_id = p_event_id
159 and child_entity_name = 'HZ_PARTIES';
160
161 begin
162 -- initialize API return status to success.
163 x_return_status := FND_API.G_RET_STS_SUCCESS;
164
165 -- Initialize message list if p_init_msg_list is set to TRUE
166 IF FND_API.to_Boolean(p_init_msg_list) THEN
167 FND_MSG_PUB.initialize;
168 END IF;
169
170
171 -- Debug info.
172 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
173 hz_utility_v2pub.debug(p_message=>'get_bo_root_ids(+)',
174 p_prefix=>l_debug_prefix,
175 p_msg_level=>fnd_log.level_procedure);
176 END IF;
177
178
179 open c1;
180 fetch c1 bulk collect into x_obj_root_ids;
181 close c1;
182
183 -- Debug info.
184 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
185 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
186 p_msg_data=>x_msg_data,
187 p_msg_type=>'WARNING',
188 p_msg_level=>fnd_log.level_exception);
189 END IF;
190
191 -- Debug info.
192 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
193 hz_utility_v2pub.debug(p_message=>'get_bo_root_ids (-)',
194 p_prefix=>l_debug_prefix,
195 p_msg_level=>fnd_log.level_procedure);
196 END IF;
197
198
199 EXCEPTION
200
201 WHEN fnd_api.g_exc_error THEN
202 x_return_status := fnd_api.g_ret_sts_error;
203
204 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
205 p_count => x_msg_count,
206 p_data => x_msg_data);
207
208 -- Debug info.
209 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
210 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
211 p_msg_data=>x_msg_data,
212 p_msg_type=>'ERROR',
213 p_msg_level=>fnd_log.level_error);
214 END IF;
215 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
216 hz_utility_v2pub.debug(p_message=>'get_bo_root_ids(-)',
217 p_prefix=>l_debug_prefix,
218 p_msg_level=>fnd_log.level_procedure);
219 END IF;
220 WHEN fnd_api.g_exc_unexpected_error THEN
221 x_return_status := fnd_api.g_ret_sts_unexp_error;
222
223 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
224 p_count => x_msg_count,
225 p_data => x_msg_data);
226
227 -- Debug info.
228 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
229 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
230 p_msg_data=>x_msg_data,
231 p_msg_type=>'UNEXPECTED ERROR',
232 p_msg_level=>fnd_log.level_error);
233 END IF;
234 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
235 hz_utility_v2pub.debug(p_message=>'get_bo_root_ids(-)',
236 p_prefix=>l_debug_prefix,
237 p_msg_level=>fnd_log.level_procedure);
238 END IF;
239 WHEN OTHERS THEN
240 x_return_status := fnd_api.g_ret_sts_unexp_error;
241
242 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
243 fnd_message.set_token('ERROR' ,SQLERRM);
244 fnd_msg_pub.add;
245
246 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
247 p_count => x_msg_count,
248 p_data => x_msg_data);
249
250 -- Debug info.
251 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
252 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
253 p_msg_data=>x_msg_data,
254 p_msg_type=>'SQL ERROR',
255 p_msg_level=>fnd_log.level_error);
256 END IF;
257 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
258 hz_utility_v2pub.debug(p_message=>'get_bo_root_ids(-)',
259 p_prefix=>l_debug_prefix,
260 p_msg_level=>fnd_log.level_procedure);
261 END IF;
262
263 end;
264
265 FUNCTION is_ss_provided(
266 p_os IN VARCHAR2,
267 p_osr IN VARCHAR2
268 ) RETURN VARCHAR2 IS
269 BEGIN
270 IF((p_os is null or p_os = fnd_api.g_miss_char)
271 and (p_osr is null or p_osr = fnd_api.g_miss_char))THEN
272 RETURN 'N';
273 ELSE
274 RETURN 'Y';
275 END IF;
276 END is_ss_provided;
277
278 PROCEDURE validate_ssm_id(
279 px_id IN OUT NOCOPY NUMBER,
280 px_os IN OUT NOCOPY VARCHAR2,
281 px_osr IN OUT NOCOPY VARCHAR2,
282 p_org_id IN NUMBER := NULL,
283 p_obj_type IN VARCHAR2,
284 x_return_status OUT NOCOPY VARCHAR2,
285 x_msg_count OUT NOCOPY NUMBER,
286 x_msg_data OUT NOCOPY VARCHAR2
287 ) IS
288 CURSOR is_cp_valid(l_cp_id NUMBER, l_cp_type VARCHAR2) IS
289 SELECT 'X'
290 FROM HZ_CONTACT_POINTS
291 WHERE contact_point_id = l_cp_id
292 AND contact_point_type = l_cp_type;
293
294 CURSOR is_oc_valid(l_oc_id NUMBER) IS
295 SELECT 'X'
296 FROM HZ_ORG_CONTACTS
297 WHERE org_contact_id = l_oc_id;
298
299 CURSOR is_pty_valid(l_pty_id NUMBER, l_pty_type VARCHAR2) IS
300 SELECT 'X'
301 FROM HZ_PARTIES
302 WHERE party_id = l_pty_id
303 AND party_type = l_pty_type
304 AND status in ('A', 'I');
305
306 CURSOR is_ps_valid(l_ps_id NUMBER) IS
307 SELECT 'X'
308 FROM HZ_PARTY_SITES
309 WHERE party_site_id = l_ps_id;
310
311 CURSOR is_loc_valid(l_loc_id NUMBER) IS
312 SELECT 'X'
313 FROM HZ_LOCATIONS
314 WHERE location_id = l_loc_id;
315
316 CURSOR is_cr_valid(l_cr_id NUMBER) IS
317 SELECT 'X'
318 FROM HZ_CUST_ACCOUNT_ROLES
319 WHERE cust_account_role_id = l_cr_id;
320
321 CURSOR is_ca_valid(l_ca_id NUMBER) IS
322 SELECT 'X'
323 FROM HZ_CUST_ACCOUNTS
324 WHERE cust_account_id = l_ca_id;
325
326 CURSOR is_cas_valid(l_cas_id NUMBER, l_org_id NUMBER) IS
327 SELECT 'X'
328 FROM HZ_CUST_ACCT_SITES
329 WHERE cust_acct_site_id = l_cas_id;
330
331 CURSOR is_casu_valid(l_casu_id NUMBER, l_org_id NUMBER) IS
332 SELECT 'X'
333 FROM HZ_CUST_SITE_USES
334 WHERE site_use_id = l_casu_id;
335
336 l_owner_table_id NUMBER;
337 l_ss_flag VARCHAR2(1);
338 l_debug_prefix VARCHAR2(30);
339 l_valid_id VARCHAR2(1);
340 l_count NUMBER;
341 l_org_id NUMBER;
342 l_dummy VARCHAR2(1);
343 l_obj_type VARCHAR2(30);
344 BEGIN
345 -- Debug info.
346 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
347 hz_utility_v2pub.debug(p_message=>'validate_ssm_id(+)',
348 p_prefix=>l_debug_prefix,
349 p_msg_level=>fnd_log.level_procedure);
350 END IF;
351
352 x_return_status := FND_API.G_RET_STS_SUCCESS;
353
354 l_ss_flag := is_ss_provided(p_os => px_os,
355 p_osr => px_osr);
356
357 -- if px_id pass in, check if px_id is valid or not
358 IF(px_id IS NOT NULL) THEN
359
360 IF(p_obj_type in ('PHONE','TLX','EMAIL','WEB','EFT','EDI','SMS')) THEN
361 OPEN is_cp_valid(px_id, p_obj_type);
362 FETCH is_cp_valid INTO l_valid_id;
363 CLOSE is_cp_valid;
364 ELSIF(p_obj_type = 'HZ_ORG_CONTACTS') THEN
365 OPEN is_oc_valid(px_id);
366 FETCH is_oc_valid INTO l_valid_id;
367 CLOSE is_oc_valid;
368 ELSIF(p_obj_type in ('PERSON','ORGANIZATION','PARTY_RELATIONSHIP')) THEN
369 OPEN is_pty_valid(px_id, p_obj_type);
370 FETCH is_pty_valid INTO l_valid_id;
371 CLOSE is_pty_valid;
372 ELSIF(p_obj_type = 'HZ_CUST_ACCOUNT_ROLES') THEN
373 OPEN is_cr_valid(px_id);
374 FETCH is_cr_valid INTO l_valid_id;
375 CLOSE is_cr_valid;
376 ELSIF(p_obj_type = 'HZ_LOCATIONS') THEN
377 OPEN is_loc_valid(px_id);
378 FETCH is_loc_valid INTO l_valid_id;
379 CLOSE is_loc_valid;
380 ELSIF(p_obj_type = 'HZ_PARTY_SITES') THEN
381 OPEN is_ps_valid(px_id);
382 FETCH is_ps_valid INTO l_valid_id;
383 CLOSE is_ps_valid;
384 ELSIF(p_obj_type = 'HZ_CUST_ACCOUNTS') THEN
385 OPEN is_ca_valid(px_id);
386 FETCH is_ca_valid INTO l_valid_id;
387 CLOSE is_ca_valid;
388 ELSIF(p_obj_type = 'HZ_CUST_ACCT_SITES_ALL') THEN
389 OPEN is_cas_valid(px_id, p_org_id);
390 FETCH is_cas_valid INTO l_valid_id;
391 CLOSE is_cas_valid;
392 ELSIF(p_obj_type = 'HZ_CUST_SITE_USES_ALL') THEN
393 OPEN is_casu_valid(px_id, p_org_id);
394 FETCH is_casu_valid INTO l_valid_id;
395 CLOSE is_casu_valid;
396 END IF;
397 END IF;
398
399
400 -- if px_os/px_osr pass in, get owner_table_id and set l_ss_flag to 'Y'
401 IF(l_ss_flag = 'Y')THEN
402 IF(p_obj_type in ('PHONE','TLX','EMAIL','WEB','EFT','EDI','SMS')) THEN
403 l_obj_type := 'HZ_CONTACT_POINTS';
404 ELSIF(p_obj_type in ('PERSON','ORGANIZATION','PARTY_RELATIONSHIP')) THEN
405 l_obj_type := 'HZ_PARTIES';
406 ELSE
407 l_obj_type := p_obj_type;
408 END IF;
409
410
411 -- Get how many rows return
412 l_count := HZ_MOSR_VALIDATE_PKG.get_orig_system_ref_count(
413 p_orig_system => px_os,
414 p_orig_system_reference => px_osr,
415 p_owner_table_name => l_obj_type);
416
417 IF(l_count = 1) THEN
418 -- Get owner_table_id
419 HZ_ORIG_SYSTEM_REF_PUB.get_owner_table_id(
420 p_orig_system => px_os,
421 p_orig_system_reference => px_osr,
422 p_owner_table_name => l_obj_type,
423 x_owner_table_id => l_owner_table_id,
424 x_return_status => x_return_status);
425
426 -- For contact point, check if the id and type is the same
427 IF(p_obj_type in ('PHONE','TLX','EMAIL','WEB','EFT','EDI','SMS')) THEN
428 OPEN is_cp_valid(l_owner_table_id, p_obj_type);
429 FETCH is_cp_valid INTO l_dummy;
430 CLOSE is_cp_valid;
431 IF(l_dummy IS NULL) THEN
432 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_TCA_ID');
433 FND_MSG_PUB.ADD;
434 RAISE fnd_api.g_exc_error;
435 END IF;
436 END IF;
437 END IF;
438 END IF;
439
440 -- if px_id pass in
441 IF(px_id IS NOT NULL) THEN
442 -- if px_id is invalid, raise error
443 IF(l_valid_id IS NULL) THEN
444 FND_MESSAGE.SET_NAME('AR','HZ_API_INVALID_TCA_ID');
445 FND_MSG_PUB.ADD();
446 RAISE fnd_api.g_exc_error;
447 -- if px_id is valid
448 ELSE
449 -- check if px_os/px_osr is passed
450 IF(l_ss_flag = 'Y') THEN
451 IF(l_count = 0) THEN
452 FND_MESSAGE.SET_NAME('AR','HZ_API_INVALID_SSM_ID');
453 FND_MSG_PUB.ADD();
454 RAISE fnd_api.g_exc_error;
455 -- if px_os/px_osr is valid
456 ELSE
457 -- if px_os/px_osr is valid, but not same as px_id
458 IF(l_owner_table_id <> px_id) OR (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
459 FND_MESSAGE.SET_NAME('AR','HZ_API_INVALID_TCA_SSM_ID');
460 FND_MSG_PUB.ADD();
461 RAISE fnd_api.g_exc_error;
462 END IF;
463 END IF;
464 -- if px_os/px_osr is valid and return value is same as px_id
465 -- do nothing
466 END IF;
467 END IF;
468 -- if px_id not pass in
469 ELSE
470 -- check if px_os/px_osr can find TCA identifier, owner_table_id
471 -- if not found, raise error
472 -- else, get owner_table_id and assign it to px_id
473 IF(l_ss_flag = 'Y') AND (l_count = 1) AND (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
474 px_id := l_owner_table_id;
475 ELSE
476 FND_MESSAGE.SET_NAME('AR','HZ_API_INVALID_SSM_ID');
477 FND_MSG_PUB.ADD();
478 RAISE fnd_api.g_exc_error;
479 END IF;
480 END IF;
481
482 EXCEPTION
483 WHEN fnd_api.g_exc_error THEN
484 x_return_status := fnd_api.g_ret_sts_error;
485
486 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
487 p_count => x_msg_count,
488 p_data => x_msg_data);
489
490 -- Debug info.
491 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
492 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
493 p_msg_data=>x_msg_data,
494 p_msg_type=>'ERROR',
495 p_msg_level=>fnd_log.level_error);
496 END IF;
497 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
498 hz_utility_v2pub.debug(p_message=>'validate_ssm_id(-)',
499 p_prefix=>l_debug_prefix,
500 p_msg_level=>fnd_log.level_procedure);
501 END IF;
502 WHEN fnd_api.g_exc_unexpected_error THEN
503 x_return_status := fnd_api.g_ret_sts_unexp_error;
504
505 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
506 p_count => x_msg_count,
507 p_data => x_msg_data);
508
509 -- Debug info.
510 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
511 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
512 p_msg_data=>x_msg_data,
513 p_msg_type=>'UNEXPECTED ERROR',
514 p_msg_level=>fnd_log.level_error);
515 END IF;
516 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
517 hz_utility_v2pub.debug(p_message=>'validate_ssm_id(-)',
518 p_prefix=>l_debug_prefix,
519 p_msg_level=>fnd_log.level_procedure);
520 END IF;
521 WHEN OTHERS THEN
522 x_return_status := fnd_api.g_ret_sts_unexp_error;
523
524 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
525 fnd_message.set_token('ERROR' ,SQLERRM);
526 fnd_msg_pub.add;
527
528 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
529 p_count => x_msg_count,
530 p_data => x_msg_data);
531
532 -- Debug info.
533 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
534 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
535 p_msg_data=>x_msg_data,
536 p_msg_type=>'SQL ERROR',
537 p_msg_level=>fnd_log.level_error);
538 END IF;
539 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
540 hz_utility_v2pub.debug(p_message=>'validate_ssm_id(-)',
541 p_prefix=>l_debug_prefix,
542 p_msg_level=>fnd_log.level_procedure);
543 END IF;
544 END validate_ssm_id;
545
546
547 END HZ_EXTRACT_BO_UTIL_PVT;
[Home] [Help]