1 PACKAGE BODY HZ_EXTRACT_BO_UTIL_PVT AS
2 /*$Header: ARHEUTVB.pls 120.11.12010000.2 2009/06/25 06:04:58 vsegu ship $ */
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 IF G_RETURN_USER_NAME = 'Y' THEN
136 open get_user_name_csr;
137 fetch get_user_name_csr into l_name;
138 close get_user_name_csr;
139 return l_name;
140 ELSE
141 return p_user_id;
142 END IF;
143 end;
144
145
146 -- Central procedure for getting root event id.
147
148 procedure get_bo_root_ids(
149 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
150 p_event_id IN NUMBER,
151 x_obj_root_ids OUT NOCOPY BO_ID_TBL,
152 x_return_status OUT NOCOPY VARCHAR2,
153 x_msg_count OUT NOCOPY NUMBER,
154 x_msg_data OUT NOCOPY VARCHAR2
155 ) is
156 l_debug_prefix VARCHAR2(30) := '';
157
158 cursor c1 is
159 SELECT child_id
160 FROM HZ_BUS_OBJ_TRACKING
161 WHERE parent_bo_code is null
162 and event_id = p_event_id
163 and child_entity_name = 'HZ_PARTIES';
164
165 begin
166 -- initialize API return status to success.
167 x_return_status := FND_API.G_RET_STS_SUCCESS;
168
169 -- Initialize message list if p_init_msg_list is set to TRUE
170 IF FND_API.to_Boolean(p_init_msg_list) THEN
171 FND_MSG_PUB.initialize;
172 END IF;
173
174
175 -- Debug info.
176 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
177 hz_utility_v2pub.debug(p_message=>'get_bo_root_ids(+)',
178 p_prefix=>l_debug_prefix,
179 p_msg_level=>fnd_log.level_procedure);
180 END IF;
181
182
183 open c1;
184 fetch c1 bulk collect into x_obj_root_ids;
185 close c1;
186
187 -- Debug info.
188 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
189 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
190 p_msg_data=>x_msg_data,
191 p_msg_type=>'WARNING',
192 p_msg_level=>fnd_log.level_exception);
193 END IF;
194
195 -- Debug info.
196 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
197 hz_utility_v2pub.debug(p_message=>'get_bo_root_ids (-)',
198 p_prefix=>l_debug_prefix,
199 p_msg_level=>fnd_log.level_procedure);
200 END IF;
201
202
203 EXCEPTION
204
205 WHEN fnd_api.g_exc_error THEN
206 x_return_status := fnd_api.g_ret_sts_error;
207
208 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
209 p_count => x_msg_count,
210 p_data => x_msg_data);
211
212 -- Debug info.
213 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
214 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
215 p_msg_data=>x_msg_data,
216 p_msg_type=>'ERROR',
217 p_msg_level=>fnd_log.level_error);
218 END IF;
219 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
220 hz_utility_v2pub.debug(p_message=>'get_bo_root_ids(-)',
221 p_prefix=>l_debug_prefix,
222 p_msg_level=>fnd_log.level_procedure);
223 END IF;
224 WHEN fnd_api.g_exc_unexpected_error THEN
225 x_return_status := fnd_api.g_ret_sts_unexp_error;
226
227 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
228 p_count => x_msg_count,
229 p_data => x_msg_data);
230
231 -- Debug info.
232 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
233 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
234 p_msg_data=>x_msg_data,
235 p_msg_type=>'UNEXPECTED ERROR',
236 p_msg_level=>fnd_log.level_error);
237 END IF;
238 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
239 hz_utility_v2pub.debug(p_message=>'get_bo_root_ids(-)',
240 p_prefix=>l_debug_prefix,
241 p_msg_level=>fnd_log.level_procedure);
242 END IF;
243 WHEN OTHERS THEN
244 x_return_status := fnd_api.g_ret_sts_unexp_error;
245
246 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
247 fnd_message.set_token('ERROR' ,SQLERRM);
248 fnd_msg_pub.add;
249
250 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
251 p_count => x_msg_count,
252 p_data => x_msg_data);
253
254 -- Debug info.
255 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
256 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
257 p_msg_data=>x_msg_data,
258 p_msg_type=>'SQL ERROR',
259 p_msg_level=>fnd_log.level_error);
260 END IF;
261 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
262 hz_utility_v2pub.debug(p_message=>'get_bo_root_ids(-)',
263 p_prefix=>l_debug_prefix,
264 p_msg_level=>fnd_log.level_procedure);
265 END IF;
266
267 end;
268
269 FUNCTION is_ss_provided(
270 p_os IN VARCHAR2,
271 p_osr IN VARCHAR2
272 ) RETURN VARCHAR2 IS
273 BEGIN
274 IF((p_os is null or p_os = fnd_api.g_miss_char)
275 and (p_osr is null or p_osr = fnd_api.g_miss_char))THEN
276 RETURN 'N';
277 ELSE
278 RETURN 'Y';
279 END IF;
280 END is_ss_provided;
281
282 PROCEDURE validate_ssm_id(
283 px_id IN OUT NOCOPY NUMBER,
284 px_os IN OUT NOCOPY VARCHAR2,
285 px_osr IN OUT NOCOPY VARCHAR2,
286 p_org_id IN NUMBER := NULL,
287 p_obj_type IN VARCHAR2,
288 x_return_status OUT NOCOPY VARCHAR2,
289 x_msg_count OUT NOCOPY NUMBER,
290 x_msg_data OUT NOCOPY VARCHAR2
291 ) IS
292 CURSOR is_cp_valid(l_cp_id NUMBER, l_cp_type VARCHAR2) IS
293 SELECT 'X'
294 FROM HZ_CONTACT_POINTS
295 WHERE contact_point_id = l_cp_id
296 AND contact_point_type = l_cp_type;
297
298 CURSOR is_oc_valid(l_oc_id NUMBER) IS
299 SELECT 'X'
300 FROM HZ_ORG_CONTACTS
301 WHERE org_contact_id = l_oc_id;
302
303 CURSOR is_pty_valid(l_pty_id NUMBER, l_pty_type VARCHAR2) IS
304 SELECT 'X'
305 FROM HZ_PARTIES
306 WHERE party_id = l_pty_id
307 AND party_type = l_pty_type
308 AND status in ('A', 'I');
309
310 CURSOR is_ps_valid(l_ps_id NUMBER) IS
311 SELECT 'X'
312 FROM HZ_PARTY_SITES
313 WHERE party_site_id = l_ps_id;
314
315 CURSOR is_loc_valid(l_loc_id NUMBER) IS
316 SELECT 'X'
317 FROM HZ_LOCATIONS
318 WHERE location_id = l_loc_id;
319
320 CURSOR is_cr_valid(l_cr_id NUMBER) IS
321 SELECT 'X'
322 FROM HZ_CUST_ACCOUNT_ROLES
323 WHERE cust_account_role_id = l_cr_id;
324
325 CURSOR is_ca_valid(l_ca_id NUMBER) IS
326 SELECT 'X'
327 FROM HZ_CUST_ACCOUNTS
328 WHERE cust_account_id = l_ca_id;
329
330 CURSOR is_cas_valid(l_cas_id NUMBER, l_org_id NUMBER) IS
331 SELECT 'X'
332 FROM HZ_CUST_ACCT_SITES
333 WHERE cust_acct_site_id = l_cas_id;
334
335 CURSOR is_casu_valid(l_casu_id NUMBER, l_org_id NUMBER) IS
336 SELECT 'X'
337 FROM HZ_CUST_SITE_USES
338 WHERE site_use_id = l_casu_id;
339
340 l_owner_table_id NUMBER;
341 l_ss_flag VARCHAR2(1);
342 l_debug_prefix VARCHAR2(30);
343 l_valid_id VARCHAR2(1);
344 l_count NUMBER;
345 l_org_id NUMBER;
346 l_dummy VARCHAR2(1);
347 l_obj_type VARCHAR2(30);
348 BEGIN
349 -- Debug info.
350 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
351 hz_utility_v2pub.debug(p_message=>'validate_ssm_id(+)',
352 p_prefix=>l_debug_prefix,
353 p_msg_level=>fnd_log.level_procedure);
354 END IF;
355
356 x_return_status := FND_API.G_RET_STS_SUCCESS;
357
358 l_ss_flag := is_ss_provided(p_os => px_os,
359 p_osr => px_osr);
360
361 -- if px_id pass in, check if px_id is valid or not
362 IF(px_id IS NOT NULL) THEN
363
364 IF(p_obj_type in ('PHONE','TLX','EMAIL','WEB','EFT','EDI','SMS')) THEN
365 OPEN is_cp_valid(px_id, p_obj_type);
366 FETCH is_cp_valid INTO l_valid_id;
367 CLOSE is_cp_valid;
368 ELSIF(p_obj_type = 'HZ_ORG_CONTACTS') THEN
369 OPEN is_oc_valid(px_id);
370 FETCH is_oc_valid INTO l_valid_id;
371 CLOSE is_oc_valid;
372 ELSIF(p_obj_type in ('PERSON','ORGANIZATION','PARTY_RELATIONSHIP')) THEN
373 OPEN is_pty_valid(px_id, p_obj_type);
374 FETCH is_pty_valid INTO l_valid_id;
375 CLOSE is_pty_valid;
376 ELSIF(p_obj_type = 'HZ_CUST_ACCOUNT_ROLES') THEN
377 OPEN is_cr_valid(px_id);
378 FETCH is_cr_valid INTO l_valid_id;
379 CLOSE is_cr_valid;
380 ELSIF(p_obj_type = 'HZ_LOCATIONS') THEN
381 OPEN is_loc_valid(px_id);
382 FETCH is_loc_valid INTO l_valid_id;
383 CLOSE is_loc_valid;
384 ELSIF(p_obj_type = 'HZ_PARTY_SITES') THEN
385 OPEN is_ps_valid(px_id);
386 FETCH is_ps_valid INTO l_valid_id;
387 CLOSE is_ps_valid;
388 ELSIF(p_obj_type = 'HZ_CUST_ACCOUNTS') THEN
389 OPEN is_ca_valid(px_id);
390 FETCH is_ca_valid INTO l_valid_id;
391 CLOSE is_ca_valid;
392 ELSIF(p_obj_type = 'HZ_CUST_ACCT_SITES_ALL') THEN
393 OPEN is_cas_valid(px_id, p_org_id);
394 FETCH is_cas_valid INTO l_valid_id;
395 CLOSE is_cas_valid;
399 CLOSE is_casu_valid;
396 ELSIF(p_obj_type = 'HZ_CUST_SITE_USES_ALL') THEN
397 OPEN is_casu_valid(px_id, p_org_id);
398 FETCH is_casu_valid INTO l_valid_id;
400 END IF;
401 END IF;
402
403
404 -- if px_os/px_osr pass in, get owner_table_id and set l_ss_flag to 'Y'
405 IF(l_ss_flag = 'Y')THEN
406 IF(p_obj_type in ('PHONE','TLX','EMAIL','WEB','EFT','EDI','SMS')) THEN
407 l_obj_type := 'HZ_CONTACT_POINTS';
408 ELSIF(p_obj_type in ('PERSON','ORGANIZATION','PARTY_RELATIONSHIP')) THEN
409 l_obj_type := 'HZ_PARTIES';
410 ELSE
411 l_obj_type := p_obj_type;
412 END IF;
413
414
415 -- Get how many rows return
416 l_count := HZ_MOSR_VALIDATE_PKG.get_orig_system_ref_count(
417 p_orig_system => px_os,
418 p_orig_system_reference => px_osr,
419 p_owner_table_name => l_obj_type);
420
421 IF(l_count = 1) THEN
422 -- Get owner_table_id
423 HZ_ORIG_SYSTEM_REF_PUB.get_owner_table_id(
424 p_orig_system => px_os,
425 p_orig_system_reference => px_osr,
426 p_owner_table_name => l_obj_type,
427 x_owner_table_id => l_owner_table_id,
428 x_return_status => x_return_status);
429
430 -- For contact point, check if the id and type is the same
431 IF(p_obj_type in ('PHONE','TLX','EMAIL','WEB','EFT','EDI','SMS')) THEN
432 OPEN is_cp_valid(l_owner_table_id, p_obj_type);
433 FETCH is_cp_valid INTO l_dummy;
434 CLOSE is_cp_valid;
435 IF(l_dummy IS NULL) THEN
436 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_TCA_ID');
437 FND_MSG_PUB.ADD;
438 RAISE fnd_api.g_exc_error;
439 END IF;
440 END IF;
441 END IF;
442 END IF;
443
444 -- if px_id pass in
445 IF(px_id IS NOT NULL) THEN
446 -- if px_id is invalid, raise error
447 IF(l_valid_id IS NULL) THEN
448 FND_MESSAGE.SET_NAME('AR','HZ_API_INVALID_TCA_ID');
449 FND_MSG_PUB.ADD();
450 RAISE fnd_api.g_exc_error;
451 -- if px_id is valid
452 ELSE
453 -- check if px_os/px_osr is passed
454 IF(l_ss_flag = 'Y') THEN
455 IF(l_count = 0) THEN
456 FND_MESSAGE.SET_NAME('AR','HZ_API_INVALID_SSM_ID');
457 FND_MSG_PUB.ADD();
458 RAISE fnd_api.g_exc_error;
459 -- if px_os/px_osr is valid
460 ELSE
461 -- if px_os/px_osr is valid, but not same as px_id
462 IF(l_owner_table_id <> px_id) OR (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
463 FND_MESSAGE.SET_NAME('AR','HZ_API_INVALID_TCA_SSM_ID');
464 FND_MSG_PUB.ADD();
465 RAISE fnd_api.g_exc_error;
466 END IF;
467 END IF;
468 -- if px_os/px_osr is valid and return value is same as px_id
469 -- do nothing
470 END IF;
471 END IF;
472 -- if px_id not pass in
473 ELSE
474 -- check if px_os/px_osr can find TCA identifier, owner_table_id
475 -- if not found, raise error
476 -- else, get owner_table_id and assign it to px_id
477 IF(l_ss_flag = 'Y') AND (l_count = 1) AND (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
478 px_id := l_owner_table_id;
479 ELSE
480 FND_MESSAGE.SET_NAME('AR','HZ_API_INVALID_SSM_ID');
481 FND_MSG_PUB.ADD();
482 RAISE fnd_api.g_exc_error;
483 END IF;
484 END IF;
485
486 EXCEPTION
487 WHEN fnd_api.g_exc_error THEN
488 x_return_status := fnd_api.g_ret_sts_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=>'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=>'validate_ssm_id(-)',
503 p_prefix=>l_debug_prefix,
504 p_msg_level=>fnd_log.level_procedure);
505 END IF;
506 WHEN fnd_api.g_exc_unexpected_error THEN
507 x_return_status := fnd_api.g_ret_sts_unexp_error;
508
509 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
510 p_count => x_msg_count,
511 p_data => x_msg_data);
512
513 -- Debug info.
514 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
515 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
516 p_msg_data=>x_msg_data,
517 p_msg_type=>'UNEXPECTED ERROR',
518 p_msg_level=>fnd_log.level_error);
519 END IF;
520 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
521 hz_utility_v2pub.debug(p_message=>'validate_ssm_id(-)',
522 p_prefix=>l_debug_prefix,
523 p_msg_level=>fnd_log.level_procedure);
524 END IF;
525 WHEN OTHERS THEN
526 x_return_status := fnd_api.g_ret_sts_unexp_error;
527
531
528 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
529 fnd_message.set_token('ERROR' ,SQLERRM);
530 fnd_msg_pub.add;
532 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
533 p_count => x_msg_count,
534 p_data => x_msg_data);
535
536 -- Debug info.
537 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
538 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
539 p_msg_data=>x_msg_data,
540 p_msg_type=>'SQL ERROR',
541 p_msg_level=>fnd_log.level_error);
542 END IF;
543 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
544 hz_utility_v2pub.debug(p_message=>'validate_ssm_id(-)',
545 p_prefix=>l_debug_prefix,
546 p_msg_level=>fnd_log.level_procedure);
547 END IF;
548 END validate_ssm_id;
549
550
551 END HZ_EXTRACT_BO_UTIL_PVT;
[Home] [Help]