1 PACKAGE BODY HZ_REGISTRY_VALIDATE_BO_PVT AS
2 /*$Header: ARHBRGVB.pls 120.26.12010000.3 2009/06/25 22:11:35 awu ship $ */
3
4 -- PRIVATE PROCEDURE get_ps_from_rec
5 --
6 -- DESCRIPTION
7 -- Extract business object structure of party site.
8 --
9 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
10 --
11 -- ARGUMENTS
12 -- IN:
13 -- p_bus_object Business object structure.
14 -- OUT:
15 -- x_bus_object Business object structure of party site.
16 -- NOTES
17 --
18 -- MODIFICATION HISTORY
19 --
20 -- 13-Jul-2005 Arnold Ng o Created.
21
22 PROCEDURE get_ps_from_rec(
23 p_bus_object IN COMPLETENESS_REC_TYPE,
24 x_bus_object OUT NOCOPY COMPLETENESS_REC_TYPE
25 );
26
27 -- PRIVATE PROCEDURE get_cp_from_rec
28 --
29 -- DESCRIPTION
30 -- Extract business object structure of contact point.
31 --
32 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
33 --
34 -- ARGUMENTS
35 -- IN:
36 -- p_phone_code 'PHONE'.
37 -- p_email_code 'EMAIL'.
38 -- p_telex_code 'TLX'.
39 -- p_web_code 'WEB'.
40 -- p_edi_code 'EDI'.
41 -- p_eft_code 'EFT'.
42 -- p_sms_code 'SMS'.
43 -- OUT:
44 -- x_bus_object Business object structure of contact point.
45 -- NOTES
46 --
47 -- MODIFICATION HISTORY
48 --
49 -- 13-Jul-2005 Arnold Ng o Created.
50
51 PROCEDURE get_cp_from_rec(
52 p_phone_code IN VARCHAR2,
53 p_email_code IN VARCHAR2,
54 p_telex_code IN VARCHAR2,
55 p_web_code IN VARCHAR2,
56 p_edi_code IN VARCHAR2,
57 p_eft_code IN VARCHAR2,
58 p_sms_code IN VARCHAR2,
59 p_bus_object IN COMPLETENESS_REC_TYPE,
60 x_bus_object OUT NOCOPY COMPLETENESS_REC_TYPE
61 );
62
63 -- PRIVATE FUNCTION is_ss_provided
64 --
65 -- DESCRIPTION
66 -- Return a flag to indicate that original system and original system reference
67 -- are provided.
68 --
69 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
70 --
71 -- ARGUMENTS
72 -- IN:
73 -- p_os Original system.
74 -- p_osr Original system reference.
75 -- NOTES
76 --
77 -- MODIFICATION HISTORY
78 --
79 -- 13-Jul-2005 Arnold Ng o Created.
80
81 FUNCTION is_ss_provided(
82 p_os IN VARCHAR2,
83 p_osr IN VARCHAR2
84 ) RETURN VARCHAR2;
85
86 -- PROCEDURE validate_parent_id
87 --
88 -- DESCRIPTION
89 -- Validates parent id of business object.
90 --
91 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
92 --
93 -- ARGUMENTS
94 -- IN:
95 -- px_parent_id Parent Id.
96 -- px_parent_os Parent original system.
97 -- px_parent_osr Parent original system reference.
98 -- p_person_obj_type Parent object type.
99 -- OUT:
100 -- x_return_status Return status after the call. The status can
101 -- be FND_API.G_RET_STS_SUCCESS (success),
102 -- FND_API.G_RET_STS_ERROR (error),
103 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
104 -- x_msg_count Return total number of message.
105 -- x_msg_data Return message content.
106 -- NOTES
107 --
108 -- MODIFICATION HISTORY
109 --
110 -- 13-Jul-2005 Arnold Ng o Created.
111
112 PROCEDURE validate_parent_id(
113 px_parent_id IN OUT NOCOPY NUMBER,
114 px_parent_os IN OUT NOCOPY VARCHAR2,
115 px_parent_osr IN OUT NOCOPY VARCHAR2,
116 p_parent_obj_type IN VARCHAR2,
117 x_return_status OUT NOCOPY VARCHAR2,
118 x_msg_count OUT NOCOPY NUMBER,
119 x_msg_data OUT NOCOPY VARCHAR2
120 ) IS
121 CURSOR is_p_parent_valid(l_party_id NUMBER, l_party_type VARCHAR2) IS
122 select 'Y'
123 from HZ_PARTIES
124 where party_id = l_party_id
125 and party_type = l_party_type;
126
127 CURSOR is_ps_parent_valid(l_party_site_id NUMBER) IS
128 select 'Y'
129 from HZ_PARTY_SITES
130 where party_site_id = l_party_site_id;
131
132 CURSOR is_acct_parent_valid(l_acct_id NUMBER) IS
133 select 'Y'
134 from HZ_CUST_ACCOUNTS
135 where cust_account_id = l_acct_id;
136
137 CURSOR is_acct_site_parent_valid(l_acct_site_id NUMBER) IS
138 select 'Y'
139 from HZ_CUST_ACCT_SITES_ALL
140 where cust_acct_site_id = l_acct_site_id;
141
142 l_party_type VARCHAR2(30);
143 l_valid_parent VARCHAR2(1);
144 l_owner_table_id NUMBER;
145 l_owner_table_name VARCHAR2(30);
146 l_parent_ss_flag VARCHAR2(1);
147 l_debug_prefix VARCHAR2(30);
148 l_count NUMBER;
149 BEGIN
150
151 -- Debug info.
152 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
153 hz_utility_v2pub.debug(p_message=>'validate_parent_id(+)',
154 p_prefix=>l_debug_prefix,
155 p_msg_level=>fnd_log.level_procedure);
156 END IF;
157
158 x_return_status := FND_API.G_RET_STS_SUCCESS;
159
160 l_parent_ss_flag := is_ss_provided(p_os => px_parent_os,
161 p_osr => px_parent_osr);
162
163 l_owner_table_name := get_owner_table_name(p_obj_type => p_parent_obj_type);
164
165 -- if px_id pass in, check if px_id is valid or not
166 l_valid_parent := 'N';
167
168 -- if px_parent_id is not null, check if px_parent_id is valid or not
169 IF(px_parent_id IS NOT NULL) THEN
170 IF(l_owner_table_name = 'HZ_PARTIES') THEN
171 IF(p_parent_obj_type = 'PERSON') THEN
172 l_party_type := 'PERSON';
173 ELSIF(p_parent_obj_type = 'ORG') THEN
174 l_party_type := 'ORGANIZATION';
175 ELSE
176 l_party_type := 'PARTY_RELATIONSHIP';
177 END IF;
178 OPEN is_p_parent_valid(px_parent_id, l_party_type);
179 FETCH is_p_parent_valid INTO l_valid_parent;
180 CLOSE is_p_parent_valid;
181 ELSIF(l_owner_table_name = 'HZ_PARTY_SITES') THEN
182 OPEN is_ps_parent_valid(px_parent_id);
183 FETCH is_ps_parent_valid INTO l_valid_parent;
184 CLOSE is_ps_parent_valid;
185 ELSIF(l_owner_table_name = 'HZ_CUST_ACCOUNTS') THEN
186 OPEN is_acct_parent_valid(px_parent_id);
187 FETCH is_acct_parent_valid INTO l_valid_parent;
188 CLOSE is_acct_parent_valid;
189 ELSIF(l_owner_table_name = 'HZ_CUST_ACCT_SITES_ALL') THEN
190 OPEN is_acct_site_parent_valid(px_parent_id);
191 FETCH is_acct_site_parent_valid INTO l_valid_parent;
192 CLOSE is_acct_site_parent_valid;
193 END IF;
194 -- if px_parent_id is invalid, raise error
195 IF(l_valid_parent = 'N') THEN
196 RAISE fnd_api.g_exc_error;
197 END IF;
198 END IF;
199
200 -- if px_parent_os/px_parent_osr is not null, get owner_table_id and
201 -- set local parent_ss_flag to 'Y'
202 IF(l_parent_ss_flag = 'Y') THEN
203 -- Get how many rows return
204 l_count := HZ_MOSR_VALIDATE_PKG.get_orig_system_ref_count(
205 p_orig_system => px_parent_os,
206 p_orig_system_reference => px_parent_osr,
207 p_owner_table_name => l_owner_table_name);
208
209 IF(l_count > 0) THEN
210 -- Get owner_table_id
211 HZ_ORIG_SYSTEM_REF_PUB.get_owner_table_id(
212 p_orig_system => px_parent_os,
213 p_orig_system_reference => px_parent_osr,
214 p_owner_table_name => l_owner_table_name,
215 x_owner_table_id => l_owner_table_id,
216 x_return_status => x_return_status);
217 END IF;
218 END IF;
219
220 -- if px_parent_id is passed in
221 IF(px_parent_id IS NOT NULL) THEN
222 -- check if px_parent_os/px_parent_osr is passed
223 IF(l_parent_ss_flag = 'Y') THEN
224 -- if px_parent_os/px_parent_osr is not valid, raise error
225 IF(l_count = 0) OR (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
226 RAISE fnd_api.g_exc_error;
227 END IF;
228 -- if px_parent_os/px_parent_osr is valid, but not same as px_parent_id
229 IF(l_owner_table_id <> px_parent_id) THEN
230 RAISE fnd_api.g_exc_error;
231 END IF;
232 END IF;
233 -- if px_parent_id is not passed in
234 ELSE
235 -- check if px_parent_os/px_parent_osr can find TCA identifier, owner_table_id
236 -- if not found, raise error
237 -- else, get owner_table_id and assign it to px_parent_id
238 IF(l_parent_ss_flag = 'Y') THEN
239 IF(l_count = 0) OR (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
240 RAISE fnd_api.g_exc_error;
241 ELSE
242 px_parent_id := l_owner_table_id;
243 END IF;
244 ELSE
245 RAISE fnd_api.g_exc_error;
246 END IF;
247 END IF;
248 EXCEPTION
249 WHEN fnd_api.g_exc_error THEN
250 x_return_status := fnd_api.g_ret_sts_error;
251
252 -- put error message "Error: Invalid Identifier";
253 FND_MESSAGE.SET_NAME('AR','HZ_API_INVALID_PARENT_ID');
254 FND_MSG_PUB.ADD();
255
256 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
257 p_count => x_msg_count,
258 p_data => x_msg_data);
259
260 -- Debug info.
261 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
262 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
263 p_msg_data=>x_msg_data,
264 p_msg_type=>'ERROR',
265 p_msg_level=>fnd_log.level_error);
266 END IF;
267 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
268 hz_utility_v2pub.debug(p_message=>'validate_parent_id(-)',
269 p_prefix=>l_debug_prefix,
270 p_msg_level=>fnd_log.level_procedure);
271 END IF;
272 WHEN fnd_api.g_exc_unexpected_error THEN
273 x_return_status := fnd_api.g_ret_sts_unexp_error;
274
275 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
276 p_count => x_msg_count,
277 p_data => x_msg_data);
278
279 -- Debug info.
280 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
281 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
282 p_msg_data=>x_msg_data,
283 p_msg_type=>'UNEXPECTED ERROR',
284 p_msg_level=>fnd_log.level_error);
285 END IF;
286 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
287 hz_utility_v2pub.debug(p_message=>'validate_parent_id(-)',
288 p_prefix=>l_debug_prefix,
289 p_msg_level=>fnd_log.level_procedure);
290 END IF;
291
292 WHEN OTHERS THEN
293 x_return_status := fnd_api.g_ret_sts_unexp_error;
294
295 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
296 fnd_message.set_token('ERROR' ,SQLERRM);
297 fnd_msg_pub.add;
298
299 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
300 p_count => x_msg_count,
301 p_data => x_msg_data);
302
303 -- Debug info.
304 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
305 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
306 p_msg_data=>x_msg_data,
307 p_msg_type=>'SQL ERROR',
308 p_msg_level=>fnd_log.level_error);
309 END IF;
310 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
311 hz_utility_v2pub.debug(p_message=>'validate_parent_id(-)',
312 p_prefix=>l_debug_prefix,
313 p_msg_level=>fnd_log.level_procedure);
314 END IF;
315 END validate_parent_id;
316
317 -- PROCEDURE validate_ssm_id
318 --
319 -- DESCRIPTION
320 -- Validates Id, original system and original system reference of business object.
321 --
322 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
323 --
324 -- ARGUMENTS
325 -- IN:
326 -- px_id Id.
327 -- px_os Original system.
328 -- px_osr Original system reference.
329 -- p_org_id Org_Id for customer account site, customer account
330 -- site use and customer account relationship.
331 -- p_obj_type Business object type.
332 -- p_create_or_update Flag to indicate create or update.
333 -- OUT:
334 -- x_return_status Return status after the call. The status can
335 -- be FND_API.G_RET_STS_SUCCESS (success),
336 -- FND_API.G_RET_STS_ERROR (error),
337 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
338 -- x_msg_count Return total number of message.
339 -- x_msg_data Return message content.
340 -- NOTES
341 --
342 -- MODIFICATION HISTORY
343 --
344 -- 13-Jul-2005 Arnold Ng o Created.
345
346 PROCEDURE validate_ssm_id(
347 px_id IN OUT NOCOPY NUMBER,
348 px_os IN OUT NOCOPY VARCHAR2,
349 px_osr IN OUT NOCOPY VARCHAR2,
350 p_org_id IN NUMBER := NULL,
351 p_obj_type IN VARCHAR2,
352 p_create_or_update IN VARCHAR2,
353 x_return_status OUT NOCOPY VARCHAR2,
354 x_msg_count OUT NOCOPY NUMBER,
355 x_msg_data OUT NOCOPY VARCHAR2
356 ) IS
357 CURSOR is_cp_valid(l_cp_id NUMBER, l_cp_type VARCHAR2) IS
358 SELECT 'X'
359 FROM HZ_CONTACT_POINTS
360 WHERE contact_point_id = l_cp_id
361 AND contact_point_type = l_cp_type;
362
363 CURSOR is_oc_valid(l_oc_id NUMBER) IS
364 SELECT 'X'
365 FROM HZ_ORG_CONTACTS
366 WHERE org_contact_id = l_oc_id;
367
368 CURSOR is_pty_valid(l_pty_id NUMBER, l_pty_type VARCHAR2) IS
369 SELECT 'X'
370 FROM HZ_PARTIES
371 WHERE party_id = l_pty_id
372 AND party_type = l_pty_type
373 AND status in ('A', 'I');
374
375 CURSOR is_ps_valid(l_ps_id NUMBER) IS
376 SELECT 'X'
377 FROM HZ_PARTY_SITES
378 WHERE party_site_id = l_ps_id;
379
380 CURSOR is_loc_valid(l_loc_id NUMBER) IS
381 SELECT 'X'
382 FROM HZ_LOCATIONS
383 WHERE location_id = l_loc_id;
384
385 CURSOR is_cr_valid(l_cr_id NUMBER) IS
386 SELECT 'X'
387 FROM HZ_CUST_ACCOUNT_ROLES
388 WHERE cust_account_role_id = l_cr_id;
389
390 CURSOR is_ca_valid(l_ca_id NUMBER) IS
391 SELECT 'X'
392 FROM HZ_CUST_ACCOUNTS
393 WHERE cust_account_id = l_ca_id;
394
395 CURSOR is_cas_valid(l_cas_id NUMBER, l_org_id NUMBER) IS
396 SELECT 'X'
397 FROM HZ_CUST_ACCT_SITES
398 WHERE cust_acct_site_id = l_cas_id
399 AND org_id = l_org_id;
400
401 CURSOR is_casu_valid(l_casu_id NUMBER, l_org_id NUMBER) IS
402 SELECT 'X'
403 FROM HZ_CUST_SITE_USES
404 WHERE site_use_id = l_casu_id
405 AND org_id = l_org_id;
406
407 l_owner_table_id NUMBER;
408 l_ss_flag VARCHAR2(1);
409 l_debug_prefix VARCHAR2(30);
410 l_valid_id VARCHAR2(1);
411 l_count NUMBER;
412 l_org_id NUMBER;
413 l_dummy VARCHAR2(1);
414 l_obj_type VARCHAR2(30);
415 BEGIN
416 -- Debug info.
417 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
418 hz_utility_v2pub.debug(p_message=>'validate_ssm_id(+)',
419 p_prefix=>l_debug_prefix,
420 p_msg_level=>fnd_log.level_procedure);
421 END IF;
422
423 x_return_status := FND_API.G_RET_STS_SUCCESS;
424
425 -- check if os+osr are provided
426 l_ss_flag := is_ss_provided(p_os => px_os,
427 p_osr => px_osr);
428
429 -- if px_id pass in, check if px_id is valid or not
430 IF(px_id IS NOT NULL) THEN
431 -- user must not pass TCA id when create, if passed in create,
432 -- return error
433 IF(p_create_or_update = 'C') THEN
434 FND_MESSAGE.SET_NAME('AR','HZ_API_CANNOT_PASS_PK');
435 FND_MSG_PUB.ADD();
436 RAISE FND_API.G_EXC_ERROR;
437 END IF;
438 IF(p_obj_type in ('PHONE','TLX','EMAIL','WEB','EFT','EDI','SMS')) THEN
439 OPEN is_cp_valid(px_id, p_obj_type);
440 FETCH is_cp_valid INTO l_valid_id;
441 CLOSE is_cp_valid;
442 ELSIF(p_obj_type = 'HZ_ORG_CONTACTS') THEN
443 OPEN is_oc_valid(px_id);
444 FETCH is_oc_valid INTO l_valid_id;
445 CLOSE is_oc_valid;
446 ELSIF(p_obj_type in ('PERSON','ORGANIZATION','PARTY_RELATIONSHIP')) THEN
447 OPEN is_pty_valid(px_id, p_obj_type);
448 FETCH is_pty_valid INTO l_valid_id;
449 CLOSE is_pty_valid;
450 ELSIF(p_obj_type = 'HZ_CUST_ACCOUNT_ROLES') THEN
451 OPEN is_cr_valid(px_id);
452 FETCH is_cr_valid INTO l_valid_id;
453 CLOSE is_cr_valid;
454 ELSIF(p_obj_type = 'HZ_LOCATIONS') THEN
455 OPEN is_loc_valid(px_id);
456 FETCH is_loc_valid INTO l_valid_id;
457 CLOSE is_loc_valid;
458 ELSIF(p_obj_type = 'HZ_PARTY_SITES') THEN
459 OPEN is_ps_valid(px_id);
460 FETCH is_ps_valid INTO l_valid_id;
461 CLOSE is_ps_valid;
462 ELSIF(p_obj_type = 'HZ_CUST_ACCOUNTS') THEN
463 OPEN is_ca_valid(px_id);
464 FETCH is_ca_valid INTO l_valid_id;
465 CLOSE is_ca_valid;
466 ELSIF(p_obj_type = 'HZ_CUST_ACCT_SITES_ALL') THEN
467 OPEN is_cas_valid(px_id, p_org_id);
468 FETCH is_cas_valid INTO l_valid_id;
469 CLOSE is_cas_valid;
470 ELSIF(p_obj_type = 'HZ_CUST_SITE_USES_ALL') THEN
471 OPEN is_casu_valid(px_id, p_org_id);
472 FETCH is_casu_valid INTO l_valid_id;
473 CLOSE is_casu_valid;
474 END IF;
475 END IF;
476
477 -- if px_os/px_osr pass in, get owner_table_id and set l_ss_flag to 'Y'
478 IF(l_ss_flag = 'Y')THEN
479 IF(p_obj_type in ('PHONE','TLX','EMAIL','WEB','EFT','EDI','SMS')) THEN
480 l_obj_type := 'HZ_CONTACT_POINTS';
481 ELSIF(p_obj_type in ('PERSON','ORGANIZATION','PARTY_RELATIONSHIP')) THEN
482 l_obj_type := 'HZ_PARTIES';
483 ELSE
484 l_obj_type := p_obj_type;
485 END IF;
486
487 -- Get how many rows return
488 l_count := HZ_MOSR_VALIDATE_PKG.get_orig_system_ref_count(
489 p_orig_system => px_os,
490 p_orig_system_reference => px_osr,
491 p_owner_table_name => l_obj_type);
492
493 IF(l_count = 1) THEN
494 -- Get owner_table_id
495 HZ_ORIG_SYSTEM_REF_PUB.get_owner_table_id(
496 p_orig_system => px_os,
497 p_orig_system_reference => px_osr,
498 p_owner_table_name => l_obj_type,
499 x_owner_table_id => l_owner_table_id,
500 x_return_status => x_return_status);
501
502 -- For contact point, check if the id and type is the same
503 IF(p_obj_type in ('PHONE','TLX','EMAIL','WEB','EFT','EDI','SMS')) THEN
504 OPEN is_cp_valid(l_owner_table_id, p_obj_type);
505 FETCH is_cp_valid INTO l_dummy;
506 CLOSE is_cp_valid;
507 IF(l_dummy IS NULL) THEN
508 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_ID');
509 FND_MSG_PUB.ADD;
510 RAISE fnd_api.g_exc_error;
511 END IF;
512 END IF;
513 END IF;
514 END IF;
515
516 -- for update
517 IF(p_create_or_update = 'U') THEN
518 -- if px_id pass in
519 IF(px_id IS NOT NULL) THEN
520 -- if px_id is invalid, raise error
521 IF(l_valid_id IS NULL) THEN
522 FND_MESSAGE.SET_NAME('AR','HZ_API_UPDATE_NOT_EXIST');
523 FND_MSG_PUB.ADD();
524 RAISE fnd_api.g_exc_error;
525 -- if px_id is valid
526 ELSE
527 -- check if px_os/px_osr is passed
528 IF(l_ss_flag = 'Y') THEN
529 -- if px_os/px_osr is not valid, means that this is new os+osr
530 -- we should not create ssm mapping, error out
531 IF(l_count = 0) THEN
532 FND_MESSAGE.SET_NAME('AR','HZ_API_INVALID_ID');
533 FND_MSG_PUB.ADD();
534 RAISE fnd_api.g_exc_error;
535 -- if px_os/px_osr is valid
536 ELSE
537 -- if px_os/px_osr is valid, but not same as px_id
538 IF(l_owner_table_id <> px_id) OR (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
539 FND_MESSAGE.SET_NAME('AR','HZ_API_INVALID_ID');
540 FND_MSG_PUB.ADD();
541 RAISE fnd_api.g_exc_error;
542 END IF;
543 END IF;
544 -- if px_os/px_osr is valid and return value is same as px_id
545 -- do nothing
546 END IF;
547 END IF;
548 -- if px_id not pass in
549 ELSE
550 -- check if px_os/px_osr can find TCA identifier, owner_table_id
551 -- if not found, raise error
552 -- else, get owner_table_id and assign it to px_id
553 IF(l_ss_flag = 'Y') AND (l_count = 1) AND (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
554 px_id := l_owner_table_id;
555 ELSE
556 FND_MESSAGE.SET_NAME('AR','HZ_API_INVALID_ID');
557 FND_MSG_PUB.ADD();
558 RAISE fnd_api.g_exc_error;
559 END IF;
560 END IF;
561 -- for create
562 ELSIF(p_create_or_update = 'C') THEN
563 -- if os+osr is valid, raise error
564 IF(l_ss_flag = 'Y') AND (l_count > 0) AND (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
565 FND_MESSAGE.SET_NAME('AR','HZ_API_CREATE_ALREADY_EXISTS');
566 FND_MSG_PUB.ADD();
567 RAISE fnd_api.g_exc_error;
568 END IF;
569 END IF; -- if p_create_or_update
570
571 EXCEPTION
572 WHEN fnd_api.g_exc_error THEN
573 x_return_status := fnd_api.g_ret_sts_error;
574
575 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
576 p_count => x_msg_count,
577 p_data => x_msg_data);
578
579 -- Debug info.
580 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
581 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
582 p_msg_data=>x_msg_data,
583 p_msg_type=>'ERROR',
584 p_msg_level=>fnd_log.level_error);
585 END IF;
586 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
587 hz_utility_v2pub.debug(p_message=>'validate_ssm_id(-)',
588 p_prefix=>l_debug_prefix,
589 p_msg_level=>fnd_log.level_procedure);
590 END IF;
591 WHEN fnd_api.g_exc_unexpected_error THEN
592 x_return_status := fnd_api.g_ret_sts_unexp_error;
593
594 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
595 p_count => x_msg_count,
596 p_data => x_msg_data);
597
598 -- Debug info.
599 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
600 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
601 p_msg_data=>x_msg_data,
602 p_msg_type=>'UNEXPECTED ERROR',
603 p_msg_level=>fnd_log.level_error);
604 END IF;
605 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
606 hz_utility_v2pub.debug(p_message=>'validate_ssm_id(-)',
607 p_prefix=>l_debug_prefix,
608 p_msg_level=>fnd_log.level_procedure);
609 END IF;
610 WHEN OTHERS THEN
611 x_return_status := fnd_api.g_ret_sts_unexp_error;
612
613 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
614 fnd_message.set_token('ERROR' ,SQLERRM);
615 fnd_msg_pub.add;
616
617 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
618 p_count => x_msg_count,
619 p_data => x_msg_data);
620
621 -- Debug info.
622 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
623 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
624 p_msg_data=>x_msg_data,
625 p_msg_type=>'SQL ERROR',
626 p_msg_level=>fnd_log.level_error);
627 END IF;
628 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
629 hz_utility_v2pub.debug(p_message=>'validate_ssm_id(-)',
630 p_prefix=>l_debug_prefix,
631 p_msg_level=>fnd_log.level_procedure);
632 END IF;
633 END validate_ssm_id;
634
635 -- PROCEDURE check_contact_pref_op
636 --
637 -- DESCRIPTION
638 -- Check the operation of contact preference based on pass in parameter.
639 --
640 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
641 --
642 -- ARGUMENTS
643 -- IN:
644 -- p_contact_level_table_id Contact level table Id.
645 -- p_contact_level_table Contact level table.
646 -- p_contact_type Contact preference type.
647 -- p_preference_code Contact preference code.
648 -- p_preference_start_date Contact preference start date.
649 -- p_preference_end_date Contact preference end date.
650 -- IN/OUT:
651 -- px_contact_pref_id Contact preference Id.
652 -- OUT:
653 -- x_object_version_number Object version number of contact preference.
654 -- NOTES
655 --
656 -- MODIFICATION HISTORY
657 --
658 -- 13-Jul-2005 Arnold Ng o Created.
659
660 PROCEDURE check_contact_pref_op(
661 p_contact_level_table_id IN NUMBER,
662 p_contact_level_table IN VARCHAR2,
663 px_contact_pref_id IN OUT NOCOPY NUMBER,
664 p_contact_type IN VARCHAR2,
665 p_preference_code IN VARCHAR2,
666 p_preference_start_date IN DATE,
667 p_preference_end_date IN DATE,
668 x_object_version_number OUT NOCOPY NUMBER
669 ) IS
670 CURSOR is_contact_pref_id_exist(l_contact_pref_id NUMBER)IS
671 SELECT nvl(object_version_number,1), contact_level_table_id, contact_level_table
672 FROM HZ_CONTACT_PREFERENCES
673 WHERE contact_preference_id = l_contact_pref_id
674 AND rownum = 1;
675
676 CURSOR is_contact_pref_exist(l_contact_level_table_id NUMBER,
677 l_contact_level_table VARCHAR2,
678 l_contact_type VARCHAR2,
679 l_preference_code VARCHAR2, l_preference_start_date DATE,
680 l_preference_end_date DATE)IS
681 SELECT nvl(object_version_number,1), contact_preference_id
682 FROM HZ_CONTACT_PREFERENCES
683 WHERE contact_level_table_id = l_contact_level_table_id
684 AND contact_level_table = l_contact_level_table
685 AND contact_type = l_contact_type
686 -- AND preference_code = l_preference_code
687 AND trunc(preference_start_date) = trunc(l_preference_start_date)
688 AND trunc(nvl(preference_end_date,sysdate)) = trunc(nvl(l_preference_end_date,sysdate))
689 AND status in ('A','I')
690 AND rownum = 1;
691
692 l_clt_id NUMBER;
693 l_clt VARCHAR2(30);
694 BEGIN
695 IF(px_contact_pref_id IS NULL) THEN
696 OPEN is_contact_pref_exist(p_contact_level_table_id, p_contact_level_table,
697 p_contact_type, p_preference_code,
698 p_preference_start_date, p_preference_end_date);
699 FETCH is_contact_pref_exist INTO x_object_version_number, px_contact_pref_id;
700 CLOSE is_contact_pref_exist;
701 ELSE
702 OPEN is_contact_pref_id_exist(px_contact_pref_id);
703 FETCH is_contact_pref_id_exist INTO x_object_version_number, l_clt_id, l_clt;
704 CLOSE is_contact_pref_id_exist;
705 IF((l_clt_id <> p_contact_level_table_id) OR (l_clt <> p_contact_level_table)) OR
706 (l_clt_id IS NULL AND (p_contact_level_table_id IS NOT NULL OR p_contact_level_table IS NOT NULL)) THEN
707 -- return -1 to indicate that the combination of parent and object id do not match
708 x_object_version_number := -1;
709 END IF;
710 END IF;
711 END check_contact_pref_op;
712
713 -- PROCEDURE check_language_op
714 --
715 -- DESCRIPTION
716 -- Check the operation of person language based on pass in parameter.
717 --
718 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
719 --
720 -- ARGUMENTS
721 -- IN:
722 -- p_party_id Party Id.
723 -- p_language_name Language name.
724 -- IN/OUT:
725 -- px_language_use_ref_id Language use reference Id.
726 -- OUT:
727 -- x_object_version_number Object version number of person language.
728 -- NOTES
729 --
730 -- MODIFICATION HISTORY
731 --
732 -- 13-Jul-2005 Arnold Ng o Created.
733
734 PROCEDURE check_language_op(
735 p_party_id IN NUMBER,
736 px_language_use_ref_id IN OUT NOCOPY NUMBER,
737 p_language_name IN VARCHAR2,
738 x_object_version_number OUT NOCOPY NUMBER
739 ) IS
740 CURSOR is_lang_id_exist(l_lang_id NUMBER)IS
741 SELECT nvl(object_version_number,1), party_id
742 FROM HZ_PERSON_LANGUAGE
743 WHERE language_use_reference_id = l_lang_id
744 AND rownum = 1;
745
746 CURSOR is_language_exist(l_party_id NUMBER, l_language_name VARCHAR2)IS
747 SELECT nvl(object_version_number,1), language_use_reference_id
748 FROM HZ_PERSON_LANGUAGE
749 WHERE party_id = l_party_id
750 AND language_name = l_language_name
751 AND status in ('A','I')
752 AND rownum = 1;
753
754 l_party_id NUMBER;
755 BEGIN
756 IF(px_language_use_ref_id IS NULL) THEN
757 OPEN is_language_exist(p_party_id, p_language_name);
758 FETCH is_language_exist INTO x_object_version_number, px_language_use_ref_id;
759 CLOSE is_language_exist;
760 ELSE
761 OPEN is_lang_id_exist(px_language_use_ref_id);
762 FETCH is_lang_id_exist INTO x_object_version_number, l_party_id;
763 CLOSE is_lang_id_exist;
764 IF(l_party_id <> p_party_id) OR (l_party_id IS NULL AND p_party_id IS NOT NULL) THEN
765 -- return -1 to indicate that the combination of parent and object id do not match
766 x_object_version_number := -1;
767 END IF;
768 END IF;
769 END check_language_op;
770
771 -- PROCEDURE check_education_op
772 --
773 -- DESCRIPTION
774 -- Check the operation of education based on pass in parameter.
775 --
776 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
777 --
778 -- ARGUMENTS
779 -- IN:
780 -- p_party_id Party Id.
781 -- p_course_major Course major.
782 -- p_school_attended_name Name of attended school.
783 -- p_degree_received Received degree.
784 -- IN/OUT:
785 -- px_education_id Education Id.
786 -- OUT:
787 -- x_object_version_number Object version number of education.
788 -- NOTES
789 --
790 -- MODIFICATION HISTORY
791 --
792 -- 13-Jul-2005 Arnold Ng o Created.
793
794 PROCEDURE check_education_op(
795 p_party_id IN NUMBER,
796 px_education_id IN OUT NOCOPY NUMBER,
797 p_course_major IN VARCHAR2,
798 p_school_attended_name IN VARCHAR2,
799 p_degree_received IN VARCHAR2,
800 x_object_version_number OUT NOCOPY NUMBER
801 ) IS
802 CURSOR is_edu_id_exist(l_edu_id NUMBER) IS
803 SELECT nvl(object_version_number,1), party_id
804 FROM HZ_EDUCATION
805 WHERE education_id = l_edu_id
806 AND rownum = 1;
807
808 CURSOR is_education_exist(l_party_id NUMBER, l_course_major VARCHAR2,
809 l_school_attended_name VARCHAR2,
810 l_degree_received VARCHAR2)IS
811 SELECT nvl(object_version_number,1), education_id
812 FROM HZ_EDUCATION
813 WHERE party_id = l_party_id
814 AND UPPER(ltrim(rtrim(course_major))) = UPPER(ltrim(rtrim(l_course_major)))
815 AND UPPER(ltrim(rtrim(degree_received))) = UPPER(ltrim(rtrim(l_degree_received)))
816 AND UPPER(ltrim(rtrim(school_attended_name))) = UPPER(ltrim(rtrim(l_school_attended_name)))
817 AND status in ('A','I')
818 AND rownum = 1;
819
820 l_party_id NUMBER;
821 BEGIN
822 IF(px_education_id IS NULL) THEN
823 OPEN is_education_exist(p_party_id, p_course_major, p_school_attended_name, p_degree_received);
824 FETCH is_education_exist INTO x_object_version_number, px_education_id;
825 CLOSE is_education_exist;
826 ELSE
827 OPEN is_edu_id_exist(px_education_id);
828 FETCH is_edu_id_exist INTO x_object_version_number, l_party_id;
829 CLOSE is_edu_id_exist;
830 IF(l_party_id <> p_party_id) OR (l_party_id IS NULL AND p_party_id IS NOT NULL) THEN
831 -- return -1 to indicate that the combination of parent and object id do not match
832 x_object_version_number := -1;
833 END IF;
834 END IF;
835 END check_education_op;
836
837 -- PROCEDURE check_citizenship_op
838 --
839 -- DESCRIPTION
840 -- Check the operation of citizenship based on pass in parameter.
841 --
842 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
843 --
844 -- ARGUMENTS
845 -- IN:
846 -- p_party_id Party Id.
847 -- p_country_code Country code.
848 -- IN/OUT:
849 -- px_citizenship_id Citizenship Id.
850 -- OUT:
851 -- x_object_version_number Object version number of citizenship.
852 -- NOTES
853 --
854 -- MODIFICATION HISTORY
855 --
856 -- 13-Jul-2005 Arnold Ng o Created.
857
858 PROCEDURE check_citizenship_op(
859 p_party_id IN NUMBER,
860 px_citizenship_id IN OUT NOCOPY NUMBER,
861 p_country_code IN VARCHAR2,
862 x_object_version_number OUT NOCOPY NUMBER
863 ) IS
864 CURSOR is_citizen_id_exist(l_citizen_id NUMBER)IS
865 SELECT nvl(object_version_number,1), party_id
866 FROM HZ_CITIZENSHIP
867 WHERE citizenship_id = l_citizen_id
868 AND rownum = 1;
869
870 CURSOR is_citizenship_exist(l_party_id NUMBER, l_country_code VARCHAR2)IS
871 SELECT nvl(object_version_number,1), citizenship_id
872 FROM HZ_CITIZENSHIP
873 WHERE party_id = l_party_id
874 AND country_code = l_country_code
875 AND status in ('A','I')
876 AND rownum = 1;
877
878 l_party_id NUMBER;
879 BEGIN
880 IF(px_citizenship_id IS NULL) THEN
881 OPEN is_citizenship_exist(p_party_id, p_country_code);
882 FETCH is_citizenship_exist INTO x_object_version_number, px_citizenship_id;
883 CLOSE is_citizenship_exist;
884 ELSE
885 OPEN is_citizen_id_exist(px_citizenship_id);
886 FETCH is_citizen_id_exist INTO x_object_version_number, l_party_id;
887 CLOSE is_citizen_id_exist;
888 IF(l_party_id <> p_party_id) OR (l_party_id IS NULL AND p_party_id IS NOT NULL) THEN
889 -- return -1 to indicate that the combination of parent and object id do not match
890 x_object_version_number := -1;
891 END IF;
892 END IF;
893 END check_citizenship_op;
894
895 -- PROCEDURE check_employ_hist_op
896 --
897 -- DESCRIPTION
898 -- Check the operation of employment history based on pass in parameter.
899 --
900 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
901 --
902 -- ARGUMENTS
903 -- IN:
904 -- p_party_id Party Id.
905 -- p_employed_by_name_company Name of company.
906 -- p_employed_as_title Job title.
907 -- p_begin_date Begin date.
908 -- IN/OUT:
909 -- px_emp_hist_id Employment history Id.
910 -- OUT:
911 -- x_object_version_number Object version number of employment history.
912 -- NOTES
913 --
914 -- MODIFICATION HISTORY
915 --
916 -- 13-Jul-2005 Arnold Ng o Created.
917
918 PROCEDURE check_employ_hist_op(
919 p_party_id IN NUMBER,
920 px_emp_hist_id IN OUT NOCOPY NUMBER,
921 p_employed_by_name_company IN VARCHAR2,
922 p_employed_as_title IN VARCHAR2,
923 p_begin_date IN DATE,
924 x_object_version_number OUT NOCOPY NUMBER
925 ) IS
926 CURSOR is_employ_hist_id_exist(l_emp_hist_id NUMBER)IS
927 SELECT nvl(object_version_number,1), party_id
928 FROM HZ_EMPLOYMENT_HISTORY
929 WHERE employment_history_id = l_emp_hist_id
930 AND rownum = 1;
931
932 CURSOR is_employ_hist_exist(l_party_id NUMBER, l_company VARCHAR2,
933 l_title VARCHAR2, l_begin_date DATE)IS
934 SELECT nvl(object_version_number,1), employment_history_id
935 FROM HZ_EMPLOYMENT_HISTORY
936 WHERE party_id = l_party_id
937 AND nvl(UPPER(ltrim(rtrim(employed_by_name_company))),-99) = nvl(UPPER(ltrim(rtrim(l_company))),-99)
938 AND nvl(UPPER(ltrim(rtrim(employed_as_title))),-99) = nvl(UPPER(ltrim(rtrim(l_title))), -99)
939 AND nvl(trunc(begin_date),sysdate) = nvl(trunc(l_begin_date),sysdate)
940 AND status in ('A','I')
941 AND rownum = 1;
942
943 l_party_id NUMBER;
944 BEGIN
945 IF(px_emp_hist_id IS NULL) THEN
946 OPEN is_employ_hist_exist(p_party_id, p_employed_by_name_company, p_employed_as_title, p_begin_date);
947 FETCH is_employ_hist_exist INTO x_object_version_number, px_emp_hist_id;
948 CLOSE is_employ_hist_exist;
949 ELSE
950 OPEN is_employ_hist_id_exist(px_emp_hist_id);
951 FETCH is_employ_hist_id_exist INTO x_object_version_number, l_party_id;
952 CLOSE is_employ_hist_id_exist;
953 IF(l_party_id <> p_party_id) OR (l_party_id IS NULL AND p_party_id IS NOT NULL) THEN
954 -- return -1 to indicate that the combination of parent and object id do not match
955 x_object_version_number := -1;
956 END IF;
957 END IF;
958 END check_employ_hist_op;
959
960 -- PROCEDURE check_work_class_op
961 --
962 -- DESCRIPTION
963 -- Check the operation of work class based on pass in parameter.
964 --
965 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
966 --
967 -- ARGUMENTS
968 -- IN:
969 -- p_party_id Party Id.
970 -- p_work_class_name Name of work class.
971 -- IN/OUT:
972 -- px_work_class_id Work class Id.
973 -- OUT:
974 -- x_object_version_number Object version number of work class.
975 -- NOTES
976 --
977 -- MODIFICATION HISTORY
978 --
979 -- 13-Jul-2005 Arnold Ng o Created.
980
981 PROCEDURE check_work_class_op(
982 p_employ_hist_id IN NUMBER,
983 px_work_class_id IN OUT NOCOPY NUMBER,
984 p_work_class_name IN VARCHAR2,
985 x_object_version_number OUT NOCOPY NUMBER
986 ) IS
987 CURSOR is_work_class_id_exist(l_work_class_id NUMBER)IS
988 SELECT nvl(object_version_number,1), employment_history_id
989 FROM HZ_WORK_CLASS
990 WHERE work_class_id = l_work_class_id
991 AND rownum = 1;
992
993 CURSOR is_work_class_exist(l_employ_hist_id NUMBER, l_work_class_name VARCHAR2)IS
994 SELECT nvl(object_version_number,1), work_class_id
995 FROM HZ_WORK_CLASS
996 WHERE employment_history_id = l_employ_hist_id
997 AND UPPER(ltrim(rtrim(WORK_CLASS_NAME))) = UPPER(ltrim(rtrim(l_work_class_name)))
998 AND status in ('A','I')
999 AND rownum = 1;
1000
1001 l_eh_id NUMBER;
1002 BEGIN
1003 IF(px_work_class_id IS NULL) THEN
1004 OPEN is_work_class_exist(p_employ_hist_id, p_work_class_name);
1005 FETCH is_work_class_exist INTO x_object_version_number, px_work_class_id;
1006 CLOSE is_work_class_exist;
1007 ELSE
1008 OPEN is_work_class_id_exist(px_work_class_id);
1009 FETCH is_work_class_id_exist INTO x_object_version_number, l_eh_id;
1010 CLOSE is_work_class_id_exist;
1011 IF(l_eh_id <> p_employ_hist_id) OR (l_eh_id IS NULL AND p_employ_hist_id IS NOT NULL) THEN
1012 -- return -1 to indicate that the combination of parent and object id do not match
1013 x_object_version_number := -1;
1014 END IF;
1015 END IF;
1016 END check_work_class_op;
1017
1018 -- PROCEDURE check_interest_op
1019 --
1020 -- DESCRIPTION
1021 -- Check the operation of person interest based on pass in parameter.
1022 --
1023 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1024 --
1025 -- ARGUMENTS
1026 -- IN:
1027 -- p_party_id Party Id.
1028 -- p_interest_type_code Interest type code.
1029 -- p_sub_interest_type_code Sub-interest type code.
1030 -- p_interest_name Name of interest.
1031 -- IN/OUT:
1032 -- px_interest_id Person interest Id.
1033 -- OUT:
1034 -- x_object_version_number Object version number of person interest.
1035 -- NOTES
1036 --
1037 -- MODIFICATION HISTORY
1038 --
1039 -- 13-Jul-2005 Arnold Ng o Created.
1040
1041 PROCEDURE check_interest_op(
1042 p_party_id IN NUMBER,
1043 px_interest_id IN OUT NOCOPY NUMBER,
1044 p_interest_type_code IN VARCHAR2,
1045 p_sub_interest_type_code IN VARCHAR2,
1046 p_interest_name IN VARCHAR2,
1047 x_object_version_number OUT NOCOPY NUMBER
1048 ) IS
1049 CURSOR is_interest_id_exist(l_interest_id NUMBER)IS
1050 SELECT nvl(object_version_number,1), party_id
1051 FROM HZ_PERSON_INTEREST
1052 WHERE person_interest_id = l_interest_id
1053 AND rownum = 1;
1054
1055 CURSOR is_interest_exist(l_party_id NUMBER, l_interest_type_code VARCHAR2,
1056 l_sub_interest_type_code VARCHAR2,
1057 l_interest_name VARCHAR2)IS
1058 SELECT nvl(object_version_number,1), person_interest_id
1059 FROM HZ_PERSON_INTEREST
1060 WHERE party_id = l_party_id
1061 AND (
1062 (nvl(INTEREST_TYPE_CODE,'X') = nvl(l_interest_type_code,'X') AND
1063 nvl(SUB_INTEREST_TYPE_CODE,'X') = nvl(l_sub_interest_type_code,'X'))
1064 OR
1065 UPPER(ltrim(rtrim(INTEREST_NAME))) = UPPER(ltrim(rtrim(l_interest_name)))
1066 )
1067 AND status in ('A','I')
1068 AND rownum = 1;
1069
1070 l_party_id NUMBER;
1071 BEGIN
1072 IF(px_interest_id IS NULL) THEN
1073 OPEN is_interest_exist(p_party_id, p_interest_type_code, p_sub_interest_type_code, p_interest_name);
1074 FETCH is_interest_exist INTO x_object_version_number, px_interest_id;
1075 CLOSE is_interest_exist;
1076 ELSE
1077 OPEN is_interest_id_exist(px_interest_id);
1078 FETCH is_interest_id_exist INTO x_object_version_number, l_party_id;
1079 CLOSE is_interest_id_exist;
1080 IF(l_party_id <> p_party_id) OR (l_party_id IS NULL AND p_party_id IS NOT NULL) THEN
1081 -- return -1 to indicate that the combination of parent and object id do not match
1082 x_object_version_number := -1;
1083 END IF;
1084 END IF;
1085 END check_interest_op;
1086
1087 -- PROCEDURE check_party_site_use_op
1088 --
1089 -- DESCRIPTION
1090 -- Check the operation of party site use based on pass in parameter.
1091 --
1092 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1093 --
1094 -- ARGUMENTS
1095 -- IN:
1096 -- p_party_site_id Party site Id.
1097 -- p_site_use_type Site use type.
1098 -- IN/OUT:
1099 -- px_party_site_use_id Party site use Id.
1100 -- OUT:
1101 -- x_object_version_number Object version number of party site use.
1102 -- NOTES
1103 --
1104 -- MODIFICATION HISTORY
1105 --
1106 -- 13-Jul-2005 Arnold Ng o Created.
1107
1108 PROCEDURE check_party_site_use_op(
1109 p_party_site_id IN NUMBER,
1110 px_party_site_use_id IN OUT NOCOPY NUMBER,
1111 p_site_use_type IN VARCHAR2,
1112 x_object_version_number OUT NOCOPY NUMBER
1113 ) IS
1114 CURSOR is_party_site_use_id_exist(l_site_use_id NUMBER)IS
1115 SELECT nvl(object_version_number,1), party_site_id
1116 FROM HZ_PARTY_SITE_USES
1117 WHERE party_site_use_id = l_site_use_id
1118 AND rownum = 1;
1119
1120 CURSOR is_party_site_use_exist(l_party_site_id NUMBER, l_site_use_type VARCHAR2)IS
1121 SELECT nvl(object_version_number,1), party_site_use_id
1122 FROM (SELECT object_version_number, party_site_use_id,
1123 RANK() OVER (ORDER BY status asc ) rank
1124 FROM HZ_PARTY_SITE_USES
1125 WHERE party_site_id = l_party_site_id
1126 AND site_use_type = l_site_use_type )
1127 where rank = 1
1128 AND ROWNUM = 1;
1129
1130
1131 l_ps_id NUMBER;
1132 BEGIN
1133 IF(px_party_site_use_id IS NULL) THEN
1134 OPEN is_party_site_use_exist(p_party_site_id, p_site_use_type);
1135 FETCH is_party_site_use_exist INTO x_object_version_number, px_party_site_use_id;
1136 CLOSE is_party_site_use_exist;
1137 ELSE
1138 OPEN is_party_site_use_id_exist(px_party_site_use_id);
1139 FETCH is_party_site_use_id_exist INTO x_object_version_number, l_ps_id;
1140 CLOSE is_party_site_use_id_exist;
1141 IF(l_ps_id <> p_party_site_id) OR (l_ps_id IS NULL AND p_party_site_id IS NOT NULL) THEN
1142 -- return -1 to indicate that the combination of parent and object id do not match
1143 x_object_version_number := -1;
1144 END IF;
1145 END IF;
1146 END check_party_site_use_op;
1147
1148 -- PROCEDURE check_relationship_op
1149 --
1150 -- DESCRIPTION
1151 -- Check the operation of relationship based on pass in parameter.
1152 --
1153 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1154 --
1155 -- ARGUMENTS
1156 -- IN:
1157 -- p_subject_id Subject Id.
1158 -- p_object_id Object Id.
1159 -- p_relationship_type Relationship type.
1160 -- p_relationship_code Relationship code.
1161 -- IN/OUT:
1162 -- px_relationship_id Relationship Id.
1163 -- OUT:
1164 -- x_object_version_number Object version number of relationship.
1165 -- x_party_object_version_number Object version number of relationship party.
1166 -- NOTES
1167 --
1168 -- MODIFICATION HISTORY
1169 --
1170 -- 13-Jul-2005 Arnold Ng o Created.
1171
1172 PROCEDURE check_relationship_op(
1173 p_subject_id IN NUMBER,
1174 p_object_id IN NUMBER,
1175 px_relationship_id IN OUT NOCOPY NUMBER,
1176 p_relationship_type IN VARCHAR2,
1177 p_relationship_code IN VARCHAR2,
1178 x_object_version_number OUT NOCOPY NUMBER,
1179 x_party_obj_version_number OUT NOCOPY NUMBER
1180 ) IS
1181 CURSOR is_relationship_id_exist(l_subject_id NUMBER, l_object_id NUMBER, l_rel_id NUMBER) IS
1182 SELECT rel.object_version_number, p.object_version_number
1183 FROM HZ_RELATIONSHIPS rel, HZ_PARTIES p
1184 WHERE rel.subject_id = l_subject_id
1185 AND rel.object_id = l_object_id
1186 AND rel.relationship_id = l_rel_id
1187 AND rel.party_id = p.party_id
1188 AND rel.status in ('A','I')
1189 AND rownum = 1;
1190
1191 CURSOR is_relationship_exist(l_subject_id NUMBER, l_object_id NUMBER,
1192 l_relationship_type VARCHAR2, l_relationship_code VARCHAR2)IS
1193 SELECT rel.object_version_number, p.object_version_number, rel.relationship_id
1194 FROM HZ_RELATIONSHIPS rel, HZ_PARTIES p
1195 WHERE rel.subject_id = l_subject_id
1196 AND rel.object_id = l_object_id
1197 AND rel.relationship_type = l_relationship_type
1198 AND rel.relationship_code = l_relationship_code
1199 AND sysdate between rel.start_date and nvl(rel.end_date, sysdate)
1200 AND rel.party_id = p.party_id
1201 AND rel.status in ('A','I')
1202 AND rownum = 1;
1203 BEGIN
1204 IF(px_relationship_id IS NULL) THEN
1205 OPEN is_relationship_exist(p_subject_id, p_object_id, p_relationship_type, p_relationship_code);
1206 FETCH is_relationship_exist INTO x_object_version_number, x_party_obj_version_number, px_relationship_id;
1207 CLOSE is_relationship_exist;
1208 ELSE
1209 OPEN is_relationship_id_exist(p_subject_id, p_object_id, px_relationship_id);
1210 FETCH is_relationship_id_exist INTO x_object_version_number, x_party_obj_version_number;
1211 CLOSE is_relationship_id_exist;
1212 END IF;
1213 END check_relationship_op;
1214
1215 -- PROCEDURE check_org_contact_role_op
1216 --
1217 -- DESCRIPTION
1218 -- Check the operation of org contact role based on pass in parameter.
1219 --
1220 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1221 --
1222 -- ARGUMENTS
1223 -- IN:
1224 -- p_org_contact_id Org contact Id.
1225 -- p_role_type Role type.
1226 -- IN/OUT:
1227 -- px_org_contact_role_id Org contact role Id.
1228 -- OUT:
1229 -- x_object_version_number Object version number of org contact role.
1230 -- NOTES
1231 --
1232 -- MODIFICATION HISTORY
1233 --
1234 -- 13-Jul-2005 Arnold Ng o Created.
1235
1236 PROCEDURE check_org_contact_role_op(
1237 p_org_contact_id IN NUMBER,
1238 px_org_contact_role_id IN OUT NOCOPY NUMBER,
1239 p_role_type IN VARCHAR2,
1240 x_object_version_number OUT NOCOPY NUMBER
1241 ) IS
1242 CURSOR is_org_contact_role_id_exist(l_role_id NUMBER)IS
1243 SELECT nvl(object_version_number,1), org_contact_id
1244 FROM HZ_ORG_CONTACT_ROLES
1245 WHERE org_contact_role_id = l_role_id
1246 AND rownum = 1;
1247
1248 CURSOR is_org_contact_role_exist(l_org_contact_id NUMBER, l_role_type VARCHAR2)IS
1249 SELECT nvl(object_version_number,1), org_contact_role_id
1250 FROM HZ_ORG_CONTACT_ROLES
1251 WHERE org_contact_id = l_org_contact_id
1252 AND role_type = l_role_type
1253 AND status in ('A','I')
1254 AND rownum = 1;
1255
1256 l_oc_id NUMBER;
1257 BEGIN
1258 IF(px_org_contact_role_id IS NULL) THEN
1259 OPEN is_org_contact_role_exist(p_org_contact_id, p_role_type);
1260 FETCH is_org_contact_role_exist INTO x_object_version_number, px_org_contact_role_id;
1261 CLOSE is_org_contact_role_exist;
1262 ELSE
1263 OPEN is_org_contact_role_id_exist(px_org_contact_role_id);
1264 FETCH is_org_contact_role_id_exist INTO x_object_version_number, l_oc_id;
1265 CLOSE is_org_contact_role_id_exist;
1266 IF(l_oc_id <> p_org_contact_id) OR (l_oc_id IS NULL AND p_org_contact_id IS NOT NULL) THEN
1267 -- return -1 to indicate that the combination of parent and object id do not match
1268 x_object_version_number := -1;
1269 END IF;
1270 END IF;
1271 END check_org_contact_role_op;
1272
1273 -- PROCEDURE check_certification_op
1274 --
1275 -- DESCRIPTION
1276 -- Check the operation of certification based on pass in parameter.
1277 --
1278 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1279 --
1280 -- ARGUMENTS
1281 -- IN:
1282 -- p_party_id Party Id.
1283 -- p_certification_name Name of certification.
1284 -- IN/OUT:
1285 -- px_certification_id Certification Id.
1286 -- OUT:
1287 -- x_last_update_date Last update date of certification.
1288 -- NOTES
1289 --
1290 -- MODIFICATION HISTORY
1291 --
1292 -- 13-Jul-2005 Arnold Ng o Created.
1293
1294 PROCEDURE check_certification_op(
1295 p_party_id IN NUMBER,
1296 px_certification_id IN OUT NOCOPY NUMBER,
1297 p_certification_name IN VARCHAR2,
1298 x_last_update_date OUT NOCOPY DATE,
1299 x_return_status OUT NOCOPY VARCHAR2
1300 ) IS
1301 CURSOR is_cert_id_exist(l_cert_id NUMBER) IS
1302 SELECT last_update_date, party_id
1303 FROM HZ_CERTIFICATIONS
1304 WHERE certification_id = l_cert_id
1305 AND rownum = 1;
1306
1307 CURSOR is_cert_exist(l_party_id NUMBER, l_cert_name VARCHAR2) IS
1308 SELECT last_update_date, certification_id
1309 FROM HZ_CERTIFICATIONS
1310 WHERE party_id = l_party_id
1311 AND certification_name = l_cert_name
1312 AND status in ('A','I')
1313 AND rownum = 1;
1314
1315 l_party_id NUMBER;
1316 BEGIN
1317 x_return_status := FND_API.G_RET_STS_SUCCESS;
1318 IF(px_certification_id IS NULL) THEN
1319 OPEN is_cert_exist(p_party_id, p_certification_name);
1320 FETCH is_cert_exist INTO x_last_update_date, px_certification_id;
1321 CLOSE is_cert_exist;
1322 ELSE
1323 OPEN is_cert_id_exist(px_certification_id);
1324 FETCH is_cert_id_exist INTO x_last_update_date, l_party_id;
1325 CLOSE is_cert_id_exist;
1326 IF(l_party_id <> p_party_id) OR (l_party_id IS NULL AND p_party_id IS NOT NULL) THEN
1327 -- return -1 to indicate that the combination of parent and object id do not match
1328 x_return_status := FND_API.G_RET_STS_ERROR;
1329 END IF;
1330 END IF;
1331 END check_certification_op;
1332
1333 -- PROCEDURE check_financial_prof_op
1334 --
1335 -- DESCRIPTION
1336 -- Check the operation of financial profile based on pass in parameter.
1337 --
1338 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1339 --
1340 -- ARGUMENTS
1341 -- IN:
1342 -- p_party_id Party Id.
1343 -- p_financial_profile_id Financial profile Id.
1344 -- OUT:
1345 -- x_last_update_date Last update date of financial profile.
1346 -- NOTES
1347 --
1348 -- MODIFICATION HISTORY
1349 --
1350 -- 13-Jul-2005 Arnold Ng o Created.
1351
1352 PROCEDURE check_financial_prof_op(
1353 p_party_id IN NUMBER,
1354 p_financial_profile_id IN NUMBER,
1355 x_last_update_date OUT NOCOPY DATE,
1356 x_return_status OUT NOCOPY VARCHAR2
1357 ) IS
1358 CURSOR is_fin_exist(l_fin_prof_id NUMBER) IS
1359 SELECT last_update_date, party_id
1360 FROM HZ_FINANCIAL_PROFILE
1361 WHERE financial_profile_id = l_fin_prof_id
1362 AND rownum = 1;
1363
1364 l_party_id NUMBER;
1365 BEGIN
1366 x_return_status := FND_API.G_RET_STS_SUCCESS;
1367 IF(p_financial_profile_id IS NULL) THEN
1368 x_last_update_date := NULL;
1369 ELSE
1370 OPEN is_fin_exist(p_financial_profile_id);
1371 FETCH is_fin_exist INTO x_last_update_date, l_party_id;
1372 CLOSE is_fin_exist;
1373 IF(l_party_id <> p_party_id) OR (l_party_id IS NULL AND p_party_id IS NOT NULL) THEN
1374 x_return_status := FND_API.G_RET_STS_ERROR;
1375 END IF;
1376 END IF;
1377 END check_financial_prof_op;
1378
1379 -- PROCEDURE check_code_assign_op
1380 --
1381 -- DESCRIPTION
1382 -- Check the operation of classification based on pass in parameter.
1383 --
1384 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1385 --
1386 -- ARGUMENTS
1387 -- IN:
1388 -- p_owner_table_name Owner table name.
1389 -- p_owner_table_id Owner table Id.
1390 -- p_class_category Class category.
1391 -- p_class_code Class code.
1392 -- IN/OUT:
1393 -- px_code_assignment_id Code assignment Id.
1394 -- OUT:
1395 -- x_object_version_number Object version number of classification.
1396 -- NOTES
1397 --
1398 -- MODIFICATION HISTORY
1399 --
1400 -- 13-Jul-2005 Arnold Ng o Created.
1401
1402 PROCEDURE check_code_assign_op(
1403 p_owner_table_name IN VARCHAR2,
1404 p_owner_table_id IN NUMBER,
1405 px_code_assignment_id IN OUT NOCOPY NUMBER,
1406 p_class_category IN VARCHAR2,
1407 p_class_code IN VARCHAR2,
1408 x_object_version_number OUT NOCOPY NUMBER
1409 ) IS
1410 CURSOR is_code_assign_id_exist(l_code_assignment_id NUMBER)IS
1411 SELECT nvl(object_version_number,1), owner_table_name, owner_table_id
1412 FROM HZ_CODE_ASSIGNMENTS
1413 WHERE code_assignment_id = l_code_assignment_id
1414 AND rownum = 1;
1415
1416 CURSOR is_code_assign_exist(l_owner_table_name VARCHAR2, l_owner_table_id NUMBER,
1417 l_class_category VARCHAR2, l_class_code VARCHAR2)IS
1418 SELECT nvl(object_version_number,1), code_assignment_id
1419 FROM HZ_CODE_ASSIGNMENTS
1420 WHERE owner_table_name = l_owner_table_name
1421 AND owner_table_id = l_owner_table_id
1422 AND class_category = l_class_category
1423 AND class_code = l_class_code
1424 AND sysdate between start_date_active and nvl(end_date_active, sysdate)
1425 AND status in ('A','I')
1426 AND rownum = 1;
1427
1428 l_ot_name VARCHAR2(30);
1429 l_ot_id NUMBER;
1430 BEGIN
1431 IF(px_code_assignment_id IS NULL) THEN
1432 OPEN is_code_assign_exist(p_owner_table_name, p_owner_table_id, p_class_category, p_class_code);
1433 FETCH is_code_assign_exist INTO x_object_version_number, px_code_assignment_id;
1434 CLOSE is_code_assign_exist;
1435 ELSE
1436 OPEN is_code_assign_id_exist(px_code_assignment_id);
1437 FETCH is_code_assign_id_exist INTO x_object_version_number, l_ot_name, l_ot_id;
1438 CLOSE is_code_assign_id_exist;
1439 IF(l_ot_name <> p_owner_table_name OR l_ot_id <> p_owner_table_id) OR
1440 (l_ot_id IS NULL OR (p_owner_table_name IS NOT NULL OR p_owner_table_id IS NOT NULL)) THEN
1441 -- return -1 to indicate that the combination of parent and object id do not match
1442 x_object_version_number := -1;
1443 END IF;
1444 END IF;
1445 END check_code_assign_op;
1446
1447 -- PROCEDURE check_party_pref_op
1448 --
1449 -- DESCRIPTION
1450 -- Check the operation of party preference based on pass in parameter.
1451 --
1452 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1453 --
1454 -- ARGUMENTS
1455 -- IN:
1456 -- p_party_id Party Id.
1457 -- p_module Module.
1458 -- p_category Category.
1459 -- p_preference_code Preference code.
1460 -- OUT:
1461 -- x_object_version_number Object version number of party preference.
1462 -- NOTES
1463 --
1464 -- MODIFICATION HISTORY
1465 --
1466 -- 13-Jul-2005 Arnold Ng o Created.
1467
1468 PROCEDURE check_party_pref_op(
1469 p_party_id IN NUMBER,
1470 p_module IN VARCHAR2,
1471 p_category IN VARCHAR2,
1472 p_preference_code IN VARCHAR2,
1473 x_object_version_number OUT NOCOPY NUMBER
1474 ) IS
1475 CURSOR is_party_pref_exist(l_party_id NUMBER, l_module VARCHAR2,
1476 l_category VARCHAR2, l_preference_code VARCHAR2) IS
1477 SELECT nvl(object_version_number,1)
1478 FROM HZ_PARTY_PREFERENCES
1479 WHERE party_id = l_party_id
1480 AND module = l_module
1481 AND category = l_category
1482 AND preference_code = l_preference_code
1483 AND rownum = 1;
1484 BEGIN
1485 OPEN is_party_pref_exist(p_party_id, p_module, p_category, p_preference_code);
1486 FETCH is_party_pref_exist INTO x_object_version_number;
1487 CLOSE is_party_pref_exist;
1488 END check_party_pref_op;
1489
1490 -- PROCEDURE check_credit_rating_op
1491 --
1492 -- DESCRIPTION
1493 -- Check the operation of credit rating based on pass in parameter.
1494 --
1495 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1496 --
1497 -- ARGUMENTS
1498 -- IN:
1499 -- p_party_id Party Id.
1500 -- p_rating_organization Rating organization.
1501 -- p_rated_as_of_date Rated date.
1502 -- IN/OUT:
1503 -- px_credit_rating_id Credit rating Id.
1504 -- OUT:
1505 -- x_object_version_number Object version number of credit rating.
1506 -- NOTES
1507 --
1508 -- MODIFICATION HISTORY
1509 --
1510 -- 13-Jul-2005 Arnold Ng o Created.
1511
1512 PROCEDURE check_credit_rating_op(
1513 p_party_id IN NUMBER,
1514 px_credit_rating_id IN OUT NOCOPY NUMBER,
1515 p_rating_organization IN VARCHAR2,
1516 p_rated_as_of_date IN DATE,
1517 x_object_version_number OUT NOCOPY NUMBER
1518 ) IS
1519 CURSOR is_credit_rating_id_exist(l_credit_rating_id NUMBER)IS
1520 SELECT nvl(object_version_number,1), party_id
1521 FROM HZ_CREDIT_RATINGS
1522 WHERE credit_rating_id = l_credit_rating_id
1523 AND rownum = 1;
1524
1525 CURSOR is_credit_rating_exist(l_party_id NUMBER, l_rating_organization VARCHAR2,
1526 l_rated_as_of_date DATE)IS
1527 SELECT nvl(object_version_number,1), credit_rating_id
1528 FROM HZ_CREDIT_RATINGS
1529 WHERE party_id = l_party_id
1530 AND nvl(rating_organization,'A') = nvl(l_rating_organization,'A')
1531 AND trunc(nvl(rated_as_of_date,sysdate)) = trunc(nvl(l_rated_as_of_date,sysdate))
1532 AND status in ('A','I')
1533 AND rownum = 1;
1534
1535 l_party_id NUMBER;
1536 BEGIN
1537 IF(px_credit_rating_id IS NULL) THEN
1538 OPEN is_credit_rating_exist(p_party_id, p_rating_organization, p_rated_as_of_date);
1539 FETCH is_credit_rating_exist INTO x_object_version_number, px_credit_rating_id;
1540 CLOSE is_credit_rating_exist;
1541 ELSE
1542 OPEN is_credit_rating_id_exist(px_credit_rating_id);
1543 FETCH is_credit_rating_id_exist INTO x_object_version_number, l_party_id;
1544 CLOSE is_credit_rating_id_exist;
1545 IF(l_party_id <> p_party_id) OR (l_party_id IS NULL AND p_party_id IS NOT NULL) THEN
1546 -- return -1 to indicate that the combination of parent and object id do not match
1547 x_object_version_number := -1;
1548 END IF;
1549 END IF;
1550 END check_credit_rating_op;
1551
1552 -- PROCEDURE check_fin_report_op
1553 --
1554 -- DESCRIPTION
1555 -- Check the operation of financial report based on pass in parameter.
1556 --
1557 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1558 --
1559 -- ARGUMENTS
1560 -- IN:
1561 -- p_party_id Party Id.
1562 -- p_type_of_financial_report Type of financial report.
1563 -- p_document_reference Document reference.
1564 -- p_date_report_issued Report issued date.
1565 -- p_issued_period Issued period.
1566 -- IN/OUT:
1567 -- px_fin_report_id Financial report Id.
1568 -- OUT:
1569 -- x_object_version_number Object version number of financial report.
1570 -- NOTES
1571 --
1572 -- MODIFICATION HISTORY
1573 --
1574 -- 13-Jul-2005 Arnold Ng o Created.
1575
1576 PROCEDURE check_fin_report_op(
1577 p_party_id IN NUMBER,
1578 px_fin_report_id IN OUT NOCOPY NUMBER,
1579 p_type_of_financial_report IN VARCHAR2,
1580 p_document_reference IN VARCHAR2,
1581 p_date_report_issued IN DATE,
1582 p_issued_period IN VARCHAR2,
1583 x_object_version_number OUT NOCOPY NUMBER
1584 ) IS
1585 CURSOR is_fin_report_id_exist(l_fin_report_id NUMBER)IS
1586 SELECT nvl(object_version_number,1), party_id
1587 FROM HZ_FINANCIAL_REPORTS
1588 WHERE financial_report_id = l_fin_report_id
1589 AND rownum = 1;
1590
1591 CURSOR is_fin_report_exist(l_party_id NUMBER, l_type_of_fin_report VARCHAR2,
1592 l_doc_reference VARCHAR2, l_date_report_issued DATE,
1593 l_issued_period VARCHAR2 )IS
1594 SELECT nvl(object_version_number,1), financial_report_id
1595 FROM HZ_FINANCIAL_REPORTS
1596 WHERE party_id = l_party_id
1597 AND type_of_financial_report = l_type_of_fin_report
1598 AND document_reference = l_doc_reference
1599 AND (trunc(date_report_issued) = trunc(l_date_report_issued) OR
1600 issued_period = l_issued_period OR
1601 sysdate between nvl(report_start_date,sysdate) and nvl(report_end_date, sysdate))
1602 AND status in ('A','I')
1603 AND rownum = 1;
1604
1605 l_party_id NUMBER;
1606 BEGIN
1607 IF(px_fin_report_id IS NULL) THEN
1608 OPEN is_fin_report_exist(p_party_id, p_type_of_financial_report, p_document_reference,
1609 p_date_report_issued, p_issued_period);
1610 FETCH is_fin_report_exist INTO x_object_version_number, px_fin_report_id;
1611 CLOSE is_fin_report_exist;
1612 ELSE
1613 OPEN is_fin_report_id_exist(px_fin_report_id);
1614 FETCH is_fin_report_id_exist INTO x_object_version_number, l_party_id;
1615 CLOSE is_fin_report_id_exist;
1616 IF(l_party_id <> p_party_id) OR (l_party_id IS NULL AND p_party_id IS NOT NULL) THEN
1617 -- return -1 to indicate that the combination of parent and object id do not match
1618 x_object_version_number := -1;
1619 END IF;
1620 END IF;
1621 END check_fin_report_op;
1622
1623 -- PROCEDURE check_fin_number_op
1624 --
1625 -- DESCRIPTION
1626 -- Check the operation of financial number based on pass in parameter.
1627 --
1628 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1629 --
1630 -- ARGUMENTS
1631 -- IN:
1632 -- p_fin_report_id Financial report Id.
1633 -- p_financial_number_name Name of financial number.
1634 -- IN/OUT:
1635 -- px_fin_number_id Financial number Id.
1636 -- OUT:
1637 -- x_object_version_number Object version number of financial number.
1638 -- NOTES
1639 --
1640 -- MODIFICATION HISTORY
1641 --
1642 -- 13-Jul-2005 Arnold Ng o Created.
1643
1644 PROCEDURE check_fin_number_op(
1645 p_fin_report_id IN NUMBER,
1646 px_fin_number_id IN OUT NOCOPY NUMBER,
1647 p_financial_number_name IN VARCHAR2,
1648 x_object_version_number OUT NOCOPY NUMBER
1649 ) IS
1650 CURSOR is_fin_number_id_exist(l_fin_number_id NUMBER) IS
1651 SELECT nvl(object_version_number,1), financial_report_id
1652 FROM HZ_FINANCIAL_NUMBERS
1653 WHERE financial_number_id = l_fin_number_id
1654 AND rownum = 1;
1655
1656 CURSOR is_fin_number_exist(l_fin_report_id NUMBER, l_fin_number_name VARCHAR2) IS
1657 SELECT nvl(object_version_number,1), financial_number_id
1658 FROM HZ_FINANCIAL_NUMBERS
1659 WHERE financial_report_id = l_fin_report_id
1660 AND financial_number_name = l_fin_number_name
1661 AND status in ('A','I')
1662 AND rownum = 1;
1663
1664 l_fr_id NUMBER;
1665 BEGIN
1666 IF(px_fin_number_id IS NULL) THEN
1667 OPEN is_fin_number_exist(p_fin_report_id, p_financial_number_name);
1668 FETCH is_fin_number_exist INTO x_object_version_number, px_fin_number_id;
1669 CLOSE is_fin_number_exist;
1670 ELSE
1671 OPEN is_fin_number_id_exist(px_fin_number_id);
1672 FETCH is_fin_number_id_exist INTO x_object_version_number, l_fr_id;
1673 CLOSE is_fin_number_id_exist;
1674 IF(l_fr_id <> p_fin_report_id) OR (l_fr_id IS NULL AND p_fin_report_id IS NOT NULL) THEN
1675 -- return -1 to indicate that the combination of parent and object id do not match
1676 x_object_version_number := -1;
1677 END IF;
1678 END IF;
1679 END check_fin_number_op;
1680
1681 -- PROCEDURE check_role_resp_op
1682 --
1683 -- DESCRIPTION
1684 -- Check the operation of role responsibility based on pass in parameter.
1685 --
1686 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1687 --
1688 -- ARGUMENTS
1689 -- IN:
1690 -- p_cust_acct_contact_id Customer account contact Id.
1691 -- p_responsibility_type Role responsibility type.
1692 -- IN/OUT:
1693 -- px_responsibility_id Role responsibility Id.
1694 -- OUT:
1695 -- x_object_version_number Object version number of role responsibility.
1696 -- NOTES
1697 --
1698 -- MODIFICATION HISTORY
1699 --
1700 -- 13-Jul-2005 Arnold Ng o Created.
1701
1702 PROCEDURE check_role_resp_op(
1703 p_cust_acct_contact_id IN NUMBER,
1704 px_responsibility_id IN OUT NOCOPY NUMBER,
1705 p_responsibility_type IN VARCHAR2,
1706 x_object_version_number OUT NOCOPY NUMBER
1707 ) IS
1708 CURSOR is_role_resp_id_exist(l_resp_id NUMBER) IS
1709 SELECT nvl(object_version_number,1), cust_account_role_id
1710 FROM HZ_ROLE_RESPONSIBILITY
1711 WHERE responsibility_id = l_resp_id
1712 AND rownum = 1;
1713
1714 CURSOR is_role_resp_exist(l_cac_id NUMBER, l_resp_type VARCHAR2) IS
1715 SELECT nvl(object_version_number,1), responsibility_id
1716 FROM HZ_ROLE_RESPONSIBILITY
1717 WHERE cust_account_role_id = l_cac_id
1718 AND responsibility_type = l_resp_type
1719 AND rownum = 1;
1720
1721 l_cac_id NUMBER;
1722 BEGIN
1723 IF(px_responsibility_id IS NULL) THEN
1724 OPEN is_role_resp_exist(p_cust_acct_contact_id, p_responsibility_type);
1725 FETCH is_role_resp_exist INTO x_object_version_number, px_responsibility_id;
1726 CLOSE is_role_resp_exist;
1727 ELSE
1728 OPEN is_role_resp_id_exist(px_responsibility_id);
1729 FETCH is_role_resp_id_exist INTO x_object_version_number, l_cac_id;
1730 CLOSE is_role_resp_id_exist;
1731 IF(l_cac_id <> p_cust_acct_contact_id) OR (l_cac_id IS NULL AND p_cust_acct_contact_id IS NOT NULL) THEN
1732 -- return -1 to indicate that the combination of parent and object id do not match
1733 x_object_version_number := -1;
1734 END IF;
1735 END IF;
1736 END check_role_resp_op;
1737
1738 -- PROCEDURE check_cust_profile_op
1739 --
1740 -- DESCRIPTION
1741 -- Check the operation of customer profile based on pass in parameter.
1742 --
1743 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1744 --
1745 -- ARGUMENTS
1746 -- IN:
1747 -- p_cust_acct_id Customer account Id.
1748 -- p_site_use_id Customer site use Id.
1749 -- p_profile_class_id Profile class Id.
1750 -- IN/OUT:
1751 -- px_cust_acct_profile_id Customer profile Id.
1752 -- OUT:
1753 -- x_object_version_number Object version number of customer profile.
1754 -- NOTES
1755 --
1756 -- MODIFICATION HISTORY
1757 --
1758 -- 13-Jul-2005 Arnold Ng o Created.
1759
1760 PROCEDURE check_cust_profile_op(
1761 p_cust_acct_id IN NUMBER,
1762 px_cust_acct_profile_id IN OUT NOCOPY NUMBER,
1763 p_site_use_id IN NUMBER,
1764 p_profile_class_id IN NUMBER,
1765 x_object_version_number OUT NOCOPY NUMBER
1766 ) IS
1767 CURSOR is_cust_profile_id_exist(l_cust_acct_prof_id NUMBER) IS
1768 SELECT nvl(object_version_number,1), cust_account_id, site_use_id
1769 FROM HZ_CUSTOMER_PROFILES
1770 WHERE cust_account_profile_id = l_cust_acct_prof_id
1771 AND rownum = 1;
1772
1773 CURSOR is_cust_profile_exist(l_ca_id NUMBER, l_site_use_id NUMBER, l_profile_class_id NUMBER) IS
1774 SELECT nvl(object_version_number,1), cust_account_profile_id
1775 FROM HZ_CUSTOMER_PROFILES
1776 WHERE cust_account_id = l_ca_id
1777 AND nvl(site_use_id, -99) = nvl(l_site_use_id, -99)
1778 AND profile_class_id = l_profile_class_id
1779 AND status in ('A','I')
1780 AND rownum = 1;
1781
1782 l_ca_id NUMBER;
1783 l_casu_id NUMBER;
1784 BEGIN
1785 IF(px_cust_acct_profile_id IS NULL) THEN
1786 OPEN is_cust_profile_exist(p_cust_acct_id, p_site_use_id, p_profile_class_id);
1787 FETCH is_cust_profile_exist INTO x_object_version_number, px_cust_acct_profile_id;
1788 CLOSE is_cust_profile_exist;
1789 ELSE
1790 OPEN is_cust_profile_id_exist(px_cust_acct_profile_id);
1791 FETCH is_cust_profile_id_exist INTO x_object_version_number, l_ca_id, l_casu_id;
1792 CLOSE is_cust_profile_id_exist;
1793 IF((l_ca_id <> p_cust_acct_id) OR (nvl(l_casu_id,-99) <> nvl(p_site_use_id,-99))) OR
1794 ((l_ca_id IS NULL AND p_cust_acct_id IS NOT NULL) OR (l_casu_id IS NULL AND p_site_use_id IS NOT NULL)) THEN
1795 -- return -1 to indicate that the combination of parent and object id do not match
1796 x_object_version_number := -1;
1797 END IF;
1798 END IF;
1799 END check_cust_profile_op;
1800
1801 -- PROCEDURE check_cust_profile_amt_op
1802 --
1803 -- DESCRIPTION
1804 -- Check the operation of customer profile amount based on pass in parameter.
1805 --
1806 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1807 --
1808 -- ARGUMENTS
1809 -- IN:
1810 -- p_cust_profile_id Customer profile Id.
1811 -- p_currency_code Currency code.
1812 -- IN/OUT:
1813 -- px_cust_acct_prof_amt_id Customer profile amount Id.
1814 -- OUT:
1815 -- x_object_version_number Object version number of customer profile amount.
1816 -- NOTES
1817 --
1818 -- MODIFICATION HISTORY
1819 --
1820 -- 13-Jul-2005 Arnold Ng o Created.
1821
1822 PROCEDURE check_cust_profile_amt_op(
1823 p_cust_profile_id IN NUMBER,
1824 px_cust_acct_prof_amt_id IN OUT NOCOPY NUMBER,
1825 p_currency_code IN VARCHAR2,
1826 x_object_version_number OUT NOCOPY NUMBER
1827 ) IS
1828 CURSOR is_cust_profile_amt_id_exist(l_cust_prof_amt_id NUMBER) IS
1829 SELECT nvl(object_version_number,1), cust_account_profile_id
1830 FROM HZ_CUST_PROFILE_AMTS
1831 WHERE cust_acct_profile_amt_id = l_cust_prof_amt_id
1832 AND rownum = 1;
1833
1834 CURSOR is_cust_profile_amt_exist(l_cap_id NUMBER, l_currency_code VARCHAR2) IS
1835 SELECT nvl(object_version_number,1), cust_acct_profile_amt_id
1836 FROM HZ_CUST_PROFILE_AMTS
1837 WHERE cust_account_profile_id = l_cap_id
1838 AND currency_code = l_currency_code
1839 AND rownum = 1;
1840
1841 l_cap_id NUMBER;
1842 BEGIN
1843 IF(px_cust_acct_prof_amt_id IS NULL) THEN
1844 OPEN is_cust_profile_amt_exist(p_cust_profile_id, p_currency_code);
1845 FETCH is_cust_profile_amt_exist INTO x_object_version_number, px_cust_acct_prof_amt_id;
1846 CLOSE is_cust_profile_amt_exist;
1847 ELSE
1848 OPEN is_cust_profile_amt_id_exist(px_cust_acct_prof_amt_id);
1849 FETCH is_cust_profile_amt_id_exist INTO x_object_version_number, l_cap_id;
1850 CLOSE is_cust_profile_amt_id_exist;
1851 IF(l_cap_id <> p_cust_profile_id) OR (l_cap_id IS NULL AND p_cust_profile_id IS NOT NULL) THEN
1852 -- return -1 to indicate that the combination of parent and object id do not match
1853 x_object_version_number := -1;
1854 END IF;
1855 END IF;
1856 END check_cust_profile_amt_op;
1857
1858 -- PROCEDURE check_cust_acct_relate_op
1859 --
1860 -- DESCRIPTION
1861 -- Check the operation of customer account relationship based on pass in parameter.
1862 --
1863 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1864 --
1865 -- ARGUMENTS
1866 -- IN:
1867 -- p_cust_acct_id Customer account Id.
1868 -- p_related_cust_acct_id Related customer account Id.
1869 -- OUT:
1870 -- x_object_version_number Object version number of customer account relationship.
1871 -- NOTES
1872 --
1873 -- MODIFICATION HISTORY
1874 --
1875 -- 13-Jul-2005 Arnold Ng o Created.
1876
1877 PROCEDURE check_cust_acct_relate_op(
1878 p_cust_acct_id IN NUMBER,
1879 p_related_cust_acct_id IN NUMBER,
1880 p_org_id IN NUMBER,
1881 x_object_version_number OUT NOCOPY NUMBER
1882 ) IS
1883 CURSOR is_cust_acct_relate_exist(l_ca_id NUMBER, l_rca_id NUMBER) IS
1884 SELECT nvl(object_version_number,1)
1885 FROM HZ_CUST_ACCT_RELATE
1886 WHERE cust_account_id = l_ca_id
1887 AND related_cust_account_id = l_rca_id
1888 AND status in ('A','I')
1889 AND ORG_ID = NVL(p_org_id, ORG_ID) -- bug 8549266
1890 AND rownum = 1;
1891 BEGIN
1892 OPEN is_cust_acct_relate_exist(p_cust_acct_id, p_related_cust_acct_id);
1893 FETCH is_cust_acct_relate_exist INTO x_object_version_number;
1894 CLOSE is_cust_acct_relate_exist;
1895 END check_cust_acct_relate_op;
1896
1897 -- PROCEDURE check_payment_method_op
1898 --
1899 -- DESCRIPTION
1900 -- Check the operation of payment method based on pass in parameter.
1901 --
1902 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1903 --
1904 -- ARGUMENTS
1905 -- IN:
1906 -- p_cust_receipt_method_id Payment method Id.
1907 -- OUT:
1908 -- x_last_update_date Last update date of payment method.
1909 -- NOTES
1910 --
1911 -- MODIFICATION HISTORY
1912 --
1913 -- 13-Jul-2005 Arnold Ng o Created.
1914
1915 PROCEDURE check_payment_method_op(
1916 p_cust_receipt_method_id IN NUMBER,
1917 x_last_update_date OUT NOCOPY DATE
1918 ) IS
1919 CURSOR is_payment_method_exist(l_pm_id NUMBER) IS
1920 SELECT last_update_date
1921 FROM RA_CUST_RECEIPT_METHODS
1922 WHERE cust_receipt_method_id = l_pm_id
1923 AND rownum = 1;
1924 BEGIN
1925 OPEN is_payment_method_exist(p_cust_receipt_method_id);
1926 FETCH is_payment_method_exist INTO x_last_update_date;
1927 CLOSE is_payment_method_exist;
1928 END check_payment_method_op;
1929
1930 -- FUNCTION check_bo_op
1931 --
1932 -- DESCRIPTION
1933 -- Return the operation of business object based on pass in parameter.
1934 -- Return value can be 'C' (create) or 'U' (update)
1935 --
1936 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1937 --
1938 -- ARGUMENTS
1939 -- IN:
1940 -- p_entity_id Business object Id.
1941 -- p_entity_os Business object original system.
1942 -- p_entity_osr Business object original system reference.
1943 -- p_entity_type Business object type.
1944 -- p_cp_type Contact point type.
1945 -- p_parent_id Parent Id,
1946 -- p_parent_table Parent table
1947 -- NOTES
1948 --
1949 -- MODIFICATION HISTORY
1950 --
1951 -- 13-Jul-2005 Arnold Ng o Created.
1952
1953 FUNCTION check_bo_op(
1954 p_entity_id IN NUMBER,
1955 p_entity_os IN VARCHAR2,
1956 p_entity_osr IN VARCHAR2,
1957 p_entity_type IN VARCHAR2,
1958 p_cp_type IN VARCHAR2 := NULL,
1959 p_parent_id IN NUMBER,
1960 p_parent_obj_type IN VARCHAR2
1961 ) RETURN VARCHAR2 IS
1962 CURSOR is_contact_point_exist(l_contact_point_id NUMBER, l_contact_point_type VARCHAR2) IS
1963 SELECT owner_table_id, owner_table_name
1964 FROM HZ_CONTACT_POINTS
1965 WHERE contact_point_id = l_contact_point_id
1966 AND contact_point_type = l_contact_point_type;
1967
1968 CURSOR is_party_site_exist(l_ps_id NUMBER) IS
1969 SELECT party_id
1970 FROM HZ_PARTY_SITES
1971 WHERE party_site_id = l_ps_id;
1972
1973 CURSOR is_location_exist(l_loc_id NUMBER) IS
1974 SELECT 'X'
1975 FROM HZ_LOCATIONS
1976 WHERE location_id = l_loc_id;
1977
1978 CURSOR is_party_exist(l_party_id NUMBER) IS
1979 SELECT 'X'
1980 FROM HZ_PARTIES
1981 WHERE party_id = l_party_id;
1982
1983 CURSOR is_org_contact_exist(l_org_contact_id NUMBER) IS
1984 SELECT r.object_id
1985 FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r
1986 WHERE oc.org_contact_id = l_org_contact_id
1987 AND oc.party_relationship_id = r.relationship_id
1988 AND r.object_type = 'ORGANIZATION'
1989 AND r.subject_type = 'PERSON'
1990 AND rownum = 1;
1991
1992 CURSOR is_cust_account_exist(l_cust_acct_id NUMBER) IS
1993 SELECT party_id
1994 FROM HZ_CUST_ACCOUNTS
1995 WHERE cust_account_id = l_cust_acct_id;
1996
1997 CURSOR is_cust_acct_site_exist(l_cust_acct_site_id NUMBER) IS
1998 SELECT cust_account_id
1999 FROM HZ_CUST_ACCT_SITES_ALL
2000 WHERE cust_acct_site_id = l_cust_acct_site_id;
2001
2002 CURSOR is_cust_site_use_exist(l_site_use_id NUMBER) IS
2003 SELECT cust_acct_site_id
2004 FROM HZ_CUST_SITE_USES
2005 WHERE site_use_id = l_site_use_id;
2006
2007 CURSOR is_cust_acct_role_exist(l_cust_acct_role_id NUMBER) IS
2008 SELECT cust_account_id, nvl(cust_acct_site_id, -99)
2009 FROM HZ_CUST_ACCOUNT_ROLES
2010 WHERE cust_account_role_id = l_cust_acct_role_id;
2011
2012 l_create_update_flag VARCHAR2(1);
2013 l_dummy VARCHAR2(1);
2014 l_ss_flag VARCHAR2(1);
2015 l_owner_table_id NUMBER;
2016 l_debug_prefix VARCHAR2(30);
2017 l_return_status VARCHAR2(30);
2018 l_count NUMBER;
2019 l_parent_id NUMBER;
2020 l_acct_site_id NUMBER;
2021 l_input_parent_table VARCHAR2(30);
2022 l_parent_table VARCHAR2(30);
2023 BEGIN
2024 l_dummy := NULL;
2025
2026 l_ss_flag := is_ss_provided(p_os => p_entity_os,
2027 p_osr => p_entity_osr);
2028
2029 -- Return as 'Create' if no TCA id and no os+osr pass in
2030 -- Fix bug 4748851
2031 IF(p_entity_id IS NULL) AND (l_ss_flag = 'N') THEN
2032 RETURN 'C';
2033 END IF;
2034
2035 -- if TCA id pass in, check if it is valid or not
2036 IF(p_entity_id IS NOT NULL) THEN
2037 l_input_parent_table := get_owner_table_name(p_parent_obj_type);
2038
2039 IF(p_entity_type = 'HZ_CONTACT_POINTS') THEN
2040 OPEN is_contact_point_exist(p_entity_id, p_cp_type);
2041 FETCH is_contact_point_exist INTO l_parent_id, l_parent_table;
2042 CLOSE is_contact_point_exist;
2043 IF(l_parent_id IS NULL OR l_parent_id <> p_parent_id OR l_parent_table <> l_input_parent_table) THEN
2044 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_ID');
2045 FND_MSG_PUB.ADD;
2046 RETURN 'E';
2047 END IF;
2048 l_dummy := 'X';
2049 ELSIF(p_entity_type = 'HZ_PARTY_SITES') THEN
2050 OPEN is_party_site_exist(p_entity_id);
2051 FETCH is_party_site_exist INTO l_parent_id;
2052 CLOSE is_party_site_exist;
2053 IF(l_parent_id IS NULL OR l_parent_id <> p_parent_id) THEN
2054 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_ID');
2055 FND_MSG_PUB.ADD;
2056 RETURN 'E';
2057 END IF;
2058 l_dummy := 'X';
2059 ELSIF(p_entity_type = 'HZ_LOCATIONS') THEN
2060 OPEN is_location_exist(p_entity_id);
2061 FETCH is_location_exist INTO l_dummy;
2062 CLOSE is_location_exist;
2063 ELSIF(p_entity_type = 'HZ_PARTIES') THEN
2064 OPEN is_party_exist(p_entity_id);
2065 FETCH is_party_exist INTO l_dummy;
2066 CLOSE is_party_exist;
2067 ELSIF(p_entity_type = 'HZ_ORG_CONTACTS') THEN
2068 OPEN is_org_contact_exist(p_entity_id);
2069 FETCH is_org_contact_exist INTO l_parent_id;
2070 CLOSE is_org_contact_exist;
2071 IF(l_parent_id IS NULL OR l_parent_id <> p_parent_id) THEN
2072 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_ID');
2073 FND_MSG_PUB.ADD;
2074 RETURN 'E';
2075 END IF;
2076 l_dummy := 'X';
2077 ELSIF(p_entity_type = 'HZ_CUST_ACCOUNTS') THEN
2078 OPEN is_cust_account_exist(p_entity_id);
2079 FETCH is_cust_account_exist INTO l_parent_id;
2080 CLOSE is_cust_account_exist;
2081 IF(l_parent_id IS NULL OR l_parent_id <> p_parent_id) THEN
2082 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_ID');
2083 FND_MSG_PUB.ADD;
2084 RETURN 'E';
2085 END IF;
2086 l_dummy := 'X';
2087 ELSIF(p_entity_type = 'HZ_CUST_ACCT_SITES_ALL') THEN
2088 OPEN is_cust_acct_site_exist(p_entity_id);
2089 FETCH is_cust_acct_site_exist INTO l_parent_id;
2090 CLOSE is_cust_acct_site_exist;
2091 IF(l_parent_id IS NULL OR l_parent_id <> p_parent_id) THEN
2092 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_ID');
2093 FND_MSG_PUB.ADD;
2094 RETURN 'E';
2095 END IF;
2096 l_dummy := 'X';
2097 ELSIF(p_entity_type = 'HZ_CUST_SITE_USES_ALL') THEN
2098 OPEN is_cust_site_use_exist(p_entity_id);
2099 FETCH is_cust_site_use_exist INTO l_parent_id;
2100 CLOSE is_cust_site_use_exist;
2101 IF(l_parent_id IS NULL OR l_parent_id <> p_parent_id) THEN
2102 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_ID');
2103 FND_MSG_PUB.ADD;
2104 RETURN 'E';
2105 END IF;
2106 l_dummy := 'X';
2107 ELSIF(p_entity_type = 'HZ_CUST_ACCOUNT_ROLES') THEN
2108 OPEN is_cust_acct_role_exist(p_entity_id);
2109 FETCH is_cust_acct_role_exist INTO l_parent_id, l_acct_site_id;
2110 CLOSE is_cust_acct_role_exist;
2111 IF(p_parent_obj_type = 'CUST_ACCT_SITE') THEN
2112 IF(l_parent_id IS NULL OR l_acct_site_id <> p_parent_id) THEN
2113 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_ID');
2114 FND_MSG_PUB.ADD;
2115 RETURN 'E';
2116 END IF;
2117 ELSIF(p_parent_obj_type = 'CUST_ACCT') THEN
2118 IF(l_parent_id IS NULL OR l_parent_id <> p_parent_id) THEN
2119 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_ID');
2120 FND_MSG_PUB.ADD;
2121 RETURN 'E';
2122 END IF;
2123 END IF;
2124 l_dummy := 'X';
2125 END IF;
2126 END IF;
2127
2128 IF(l_ss_flag = 'Y') THEN
2129 -- Get how many rows return
2130 l_count := HZ_MOSR_VALIDATE_PKG.get_orig_system_ref_count(
2131 p_orig_system => p_entity_os,
2132 p_orig_system_reference => p_entity_osr,
2133 p_owner_table_name => p_entity_type);
2134
2135 IF(l_count > 0) THEN
2136 HZ_ORIG_SYSTEM_REF_PUB.get_owner_table_id(
2137 p_orig_system => p_entity_os,
2138 p_orig_system_reference => p_entity_osr,
2139 p_owner_table_name => p_entity_type,
2140 x_owner_table_id => l_owner_table_id,
2141 x_return_status => l_return_status);
2142
2143 -- For contact point, check if the id and type is the same
2144 IF(p_entity_type = 'HZ_CONTACT_POINTS') THEN
2145 OPEN is_contact_point_exist(l_owner_table_id, p_cp_type);
2146 FETCH is_contact_point_exist INTO l_parent_id, l_parent_table;
2147 CLOSE is_contact_point_exist;
2148 IF(l_parent_id IS NULL OR l_parent_id <> p_parent_id OR l_parent_table <> l_input_parent_table) THEN
2149 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_ID');
2150 FND_MSG_PUB.ADD;
2151 RETURN 'E';
2152 END IF;
2153 END IF;
2154 END IF;
2155 END IF;
2156
2157 -- no TCA id
2158 IF(p_entity_id IS NULL) THEN
2159 -- ssm is invalid
2160 IF(l_ss_flag = 'Y') AND (l_count = 0) THEN
2161 RETURN 'C';
2162 END IF;
2163 -- ssm is valid
2164 IF(l_ss_flag = 'Y') AND (l_count > 0) AND (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2165 RETURN 'U';
2166 ELSE
2167 RETURN 'E';
2168 END IF;
2169 ELSE
2170 -- invalid TCA id
2171 IF(l_dummy IS NULL) THEN
2172 -- ssm is valid
2173 IF(l_ss_flag = 'Y') AND (l_count > 0) AND (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2174 RETURN 'E';
2175 END IF;
2176 RETURN 'C';
2177 -- valid TCA id
2178 ELSE
2179 RETURN 'U';
2180 END IF;
2181 END IF;
2182 END check_bo_op;
2183
2184 -- PROCEDURE check_party_usage_op
2185 --
2186 -- DESCRIPTION
2187 -- Checks if a row exists in party_usg_assigments table for agiven
2188 -- party_id and party_usages_code.
2189 -- If exists Return last_update_date value. otherwise null.
2190 --
2191 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2192 --
2193 -- ARGUMENTS
2194 -- IN:
2195 -- p_party_id id of a party for whicch party_usage was created.
2196 -- p_party_usage_code seeded usage code.
2197
2198 -- OUT:
2199 -- x_last_update_date last_update_date column,
2200 -- x_return_status status of the call
2201 -- NOTES
2202 --
2203 -- MODIFICATION HISTORY
2204 --
2205 -- 01-Mar-2006 Hadi Alatasi o Created.
2206
2207 PROCEDURE check_party_usage_op(
2208 p_party_id IN NUMBER,
2209 p_party_usage_code IN VARCHAR2,
2210 x_last_update_date OUT NOCOPY DATE,
2211 x_return_status OUT NOCOPY VARCHAR2
2212 ) IS
2213 CURSOR is_usg_exist(l_party_id NUMBER, l_party_usage_code VARCHAR2 ) IS
2214 SELECT last_update_date
2215 FROM HZ_PARTY_USG_ASSIGNMENTS
2216 WHERE PARTY_USAGE_CODE = l_party_usage_code
2217 AND PARTY_ID= l_party_id
2218 AND rownum = 1;
2219
2220 l_party_id NUMBER;
2221 BEGIN
2222 x_return_status := FND_API.G_RET_STS_SUCCESS;
2223 IF(p_party_usage_code IS NULL OR p_party_id IS NULL) THEN
2224 x_last_update_date := NULL;
2225 ELSE
2226 OPEN is_usg_exist(p_party_id,p_party_usage_code);
2227 FETCH is_usg_exist INTO x_last_update_date;
2228 CLOSE is_usg_exist;
2229 END IF;
2230 EXCEPTION
2231 when NO_DATA_FOUND then
2232 x_last_update_date := NULL;
2233 when OTHERS then
2234 x_return_status := FND_API.G_RET_STS_ERROR;
2235
2236 END check_party_usage_op;
2237
2238
2239 -- PRIVATE FUNCTION is_ss_provided
2240 --
2241 -- DESCRIPTION
2242 -- Return a flag to indicate that original system and original system reference
2243 -- are provided.
2244 --
2245 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2246 --
2247 -- ARGUMENTS
2248 -- IN:
2249 -- p_os Original system.
2250 -- p_osr Original system reference.
2251 -- NOTES
2252 --
2253 -- MODIFICATION HISTORY
2254 --
2255 -- 13-Jul-2005 Arnold Ng o Created.
2256
2257 FUNCTION is_ss_provided(
2258 p_os IN VARCHAR2,
2259 p_osr IN VARCHAR2
2260 ) RETURN VARCHAR2 IS
2261 BEGIN
2262 IF((p_os is null or p_os = fnd_api.g_miss_char)
2263 and (p_osr is null or p_osr = fnd_api.g_miss_char))THEN
2264 RETURN 'N';
2265 ELSE
2266 RETURN 'Y';
2267 END IF;
2268 END is_ss_provided;
2269
2270 -- FUNCTION get_owner_table_name
2271 --
2272 -- DESCRIPTION
2273 -- Return the owner table name based on object type.
2274 --
2275 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2276 --
2277 -- ARGUMENTS
2278 -- IN:
2279 -- p_obj_type Object type.
2280 -- NOTES
2281 --
2282 -- MODIFICATION HISTORY
2283 --
2284 -- 13-Jul-2005 Arnold Ng o Created.
2285
2286 FUNCTION get_owner_table_name(
2287 p_obj_type IN VARCHAR2
2288 ) RETURN VARCHAR2 IS
2289 BEGIN
2290 -- Base on HZ_BUSINESS_OBJECTS lookup code
2291 IF(p_obj_type = 'PARTY_SITE') THEN
2292 RETURN 'HZ_PARTY_SITES';
2293 ELSIF((p_obj_type = 'ORG') OR (p_obj_type = 'PERSON') OR (p_obj_type = 'ORG_CONTACT')) THEN
2294 RETURN 'HZ_PARTIES';
2295 ELSIF((p_obj_type = 'ORG_CUST') OR (p_obj_type = 'PERSON_CUST') OR (p_obj_type = 'CUST_ACCT')) THEN
2296 RETURN 'HZ_CUST_ACCOUNTS';
2297 ELSIF(p_obj_type = 'CUST_ACCT_CONTACT') THEN
2298 RETURN 'HZ_CUST_ACCOUNT_ROLES';
2299 ELSIF(p_obj_type = 'CUST_ACCT_SITE') THEN
2300 RETURN 'HZ_CUST_ACCT_SITES_ALL';
2301 ELSIF(p_obj_type in ('PHONE', 'EMAIL', 'TLX', 'WEB', 'EDI', 'EFT', 'SMS')) THEN
2302 RETURN 'HZ_CONTACT_POINTS';
2303 ELSIF(p_obj_type = 'LOCATION') THEN
2304 RETURN 'HZ_LOCATIONS';
2305 ELSIF(p_obj_type = 'CUST_ACCT_SITE_USE') THEN
2306 RETURN 'HZ_CUST_SITE_USES_ALL';
2307 END IF;
2308 RETURN NULL;
2309 END get_owner_table_name;
2310
2311 -- FUNCTION get_parent_object_type
2312 --
2313 -- DESCRIPTION
2314 -- Return the object type based on parent table and Id.
2315 --
2316 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2317 --
2318 -- ARGUMENTS
2319 -- IN:
2320 -- p_parent_table_name Parent table name.
2321 -- p_parent_id Parent Id.
2322 -- NOTES
2323 --
2324 -- MODIFICATION HISTORY
2325 --
2326 -- 13-Jul-2005 Arnold Ng o Created.
2327
2328 FUNCTION get_parent_object_type(
2329 p_parent_table_name IN VARCHAR2,
2330 p_parent_id IN NUMBER
2331 ) RETURN VARCHAR2 IS
2332
2333 l_party_type VARCHAR2(30) := null;
2334
2335 CURSOR c1 IS
2336 SELECT party_type FROM hz_parties
2337 WHERE party_id = p_parent_id;
2338
2339 CURSOR c2 IS
2340 SELECT party_type
2341 FROM hz_parties p, hz_cust_accounts ca
2342 WHERE p.party_id = ca.party_id
2343 AND ca.cust_account_id = p_parent_id;
2344
2345 BEGIN
2346 -- Base on owner_table_name to return HZ_BUSINESS_OBJECTS lookup code
2347 IF p_parent_table_name = 'HZ_PARTIES' THEN
2348 OPEN c1;
2349 FETCH c1 INTO l_party_type;
2350 CLOSE c1;
2351 ELSIF p_parent_table_name = 'HZ_CUST_ACCOUNTS' THEN
2352 OPEN c2;
2353 FETCH c2 INTO l_party_type;
2354 CLOSE c2;
2355 END IF;
2356
2357 IF(p_parent_table_name = 'HZ_PARTY_SITES') THEN
2358 RETURN 'PARTY_SITE';
2359 ELSIF(p_parent_table_name = 'HZ_PARTIES') THEN
2360 IF(l_party_type = 'ORGANIZATION') THEN
2361 RETURN 'ORG';
2362 ELSIF(l_party_type = 'PERSON') THEN
2363 RETURN 'PERSON';
2364 ELSIF(l_party_type = 'PARTY_RELATIONSHIP') THEN
2365 RETURN 'ORG_CONTACT';
2366 END IF;
2367 ELSIF(p_parent_table_name = 'HZ_CUST_ACCOUNTS') THEN
2368 IF(l_party_type = 'ORGANIZATION') THEN
2369 RETURN 'ORG_CUST';
2370 ELSIF(l_party_type = 'PERSON') THEN
2371 RETURN 'PERSON_CUST';
2372 ELSIF(l_party_type IS NULL) THEN
2373 RETURN 'CUST_ACCT';
2374 END IF;
2375 ELSIF(p_parent_table_name = 'HZ_CUST_ACCOUNT_ROLES') THEN
2376 RETURN 'CUST_ACCT_CONTACT';
2377 ELSIF(p_parent_table_name = 'HZ_CUST_ACCT_SITES_ALL') THEN
2378 RETURN 'CUST_ACCT_SITE';
2379 END IF;
2380 RETURN NULL;
2381 END get_parent_object_type;
2382
2383 -- FUNCTION is_cp_bo_comp
2384 --
2385 -- DESCRIPTION
2386 -- Return true if contact point object is complete. Otherwise, return false.
2387 --
2388 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2389 --
2390 -- ARGUMENTS
2391 -- IN:
2392 -- p_phone_objs List of phone business objects.
2393 -- p_email_objs List of email business objects.
2394 -- p_telex_objs List of telex business objects.
2395 -- p_web_objs List of web business objects.
2396 -- p_edi_objs List of edi business objects.
2397 -- p_eft_objs List of eft business objects.
2398 -- p_sms_objs List of sms business objects.
2399 -- p_bus_object Business object structure for contact point.
2400 -- NOTES
2401 --
2402 -- MODIFICATION HISTORY
2403 --
2404 -- 13-Jul-2005 Arnold Ng o Created.
2405
2406 FUNCTION is_cp_bo_comp(
2407 p_phone_objs IN HZ_PHONE_CP_BO_TBL,
2408 p_email_objs IN HZ_EMAIL_CP_BO_TBL,
2409 p_telex_objs IN HZ_TELEX_CP_BO_TBL,
2410 p_web_objs IN HZ_WEB_CP_BO_TBL,
2411 p_edi_objs IN HZ_EDI_CP_BO_TBL,
2412 p_eft_objs IN HZ_EFT_CP_BO_TBL,
2413 p_sms_objs IN HZ_SMS_CP_BO_TBL,
2414 p_bus_object IN COMPLETENESS_REC_TYPE
2415 ) RETURN BOOLEAN IS
2416
2417 l_phone_cpref BOOLEAN;
2418 l_email_cpref BOOLEAN;
2419 l_telex_cpref BOOLEAN;
2420 l_web_cpref BOOLEAN;
2421 l_edi_cpref BOOLEAN;
2422 l_eft_cpref BOOLEAN;
2423 l_sms_cpref BOOLEAN;
2424 l_bus_object COMPLETENESS_REC_TYPE;
2425 l_bo_num NUMBER;
2426 BEGIN
2427 -- Contact point only has contact preference entity, use boolean to
2428 -- indicate whether it must be present or not
2429 l_bo_num := 0;
2430 l_phone_cpref := FALSE;
2431 l_email_cpref := FALSE;
2432 l_telex_cpref := FALSE;
2433 l_web_cpref := FALSE;
2434 l_edi_cpref := FALSE;
2435 l_eft_cpref := FALSE;
2436 l_sms_cpref := FALSE;
2437 l_bus_object.business_object_code := boc_tbl();
2438 l_bus_object.child_bo_code := cbc_tbl();
2439 l_bus_object.tca_mandated_flag := tmf_tbl();
2440 l_bus_object.user_mandated_flag := umf_tbl();
2441 l_bus_object.root_node_flag := rnf_tbl();
2442 l_bus_object.entity_name := ent_tbl();
2443
2444 FOR i IN 1..p_bus_object.business_object_code.COUNT LOOP
2445 -- get all entity of contact point, for contact point, the only possible
2446 -- entity is HZ_CONTACT_PREFERENCES
2447 IF(p_bus_object.tca_mandated_flag(i) = 'N' AND
2448 p_bus_object.user_mandated_flag(i) = 'Y' AND
2449 p_bus_object.root_node_flag(i) = 'N') THEN
2450 CASE
2451 WHEN p_bus_object.business_object_code(i) = 'PHONE' THEN
2452 l_phone_cpref := TRUE;
2453 WHEN p_bus_object.business_object_code(i) = 'EMAIL' THEN
2454 l_email_cpref := TRUE;
2455 WHEN p_bus_object.business_object_code(i) = 'TELEX' THEN
2456 l_telex_cpref := TRUE;
2457 WHEN p_bus_object.business_object_code(i) = 'WEB' THEN
2458 l_web_cpref := TRUE;
2459 WHEN p_bus_object.business_object_code(i) = 'EDI' THEN
2460 l_edi_cpref := TRUE;
2461 WHEN p_bus_object.business_object_code(i) = 'EFT' THEN
2462 l_eft_cpref := TRUE;
2463 WHEN p_bus_object.business_object_code(i) = 'SMS' THEN
2464 l_sms_cpref := TRUE;
2465 ELSE
2466 null;
2467 END CASE;
2468 ELSIF(p_bus_object.tca_mandated_flag(i) = 'Y' AND
2469 p_bus_object.user_mandated_flag(i) = 'Y' AND
2470 p_bus_object.root_node_flag(i) = 'Y') THEN
2471 l_bo_num := l_bo_num + 1;
2472 l_bus_object.business_object_code.EXTEND;
2473 l_bus_object.child_bo_code.EXTEND;
2474 l_bus_object.tca_mandated_flag.EXTEND;
2475 l_bus_object.user_mandated_flag.EXTEND;
2476 l_bus_object.root_node_flag.EXTEND;
2477 l_bus_object.entity_name.EXTEND;
2478 l_bus_object.business_object_code(l_bo_num) := p_bus_object.business_object_code(i);
2479 l_bus_object.child_bo_code(l_bo_num) := p_bus_object.child_bo_code(i);
2480 l_bus_object.tca_mandated_flag(l_bo_num) := p_bus_object.tca_mandated_flag(i);
2481 l_bus_object.user_mandated_flag(l_bo_num) := p_bus_object.user_mandated_flag(i);
2482 l_bus_object.root_node_flag(l_bo_num) := p_bus_object.root_node_flag(i);
2483 l_bus_object.entity_name(l_bo_num) := p_bus_object.entity_name(i);
2484 END IF;
2485 END LOOP;
2486
2487 -- loop through l_bus_object to find out which contact point must be present
2488 FOR i IN 1..l_bo_num LOOP
2489 CASE
2490 WHEN l_bus_object.business_object_code(i) = 'PHONE' THEN
2491 IF(p_phone_objs IS NULL OR p_phone_objs.COUNT < 1) THEN
2492 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_OBJ');
2493 fnd_message.set_token('OBJECT' ,'PHONE');
2494 fnd_msg_pub.add;
2495 RETURN FALSE;
2496 ELSE
2497 FOR j IN 1..p_phone_objs.COUNT LOOP
2498 IF(l_phone_cpref AND
2499 (p_phone_objs(j).contact_pref_objs IS NULL OR
2500 p_phone_objs(j).contact_pref_objs.COUNT < 1)) THEN
2501 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2502 fnd_message.set_token('ENTITY' ,'PHONE-CONTACT_PREFERENCE');
2503 fnd_msg_pub.add;
2504 RETURN FALSE;
2505 END IF;
2506 END LOOP;
2507 END IF;
2508 WHEN l_bus_object.business_object_code(i) = 'EMAIL' THEN
2509 IF(p_email_objs IS NULL OR p_email_objs.COUNT < 1) THEN
2510 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_OBJ');
2511 fnd_message.set_token('OBJECT' ,'EMAIL');
2512 fnd_msg_pub.add;
2513 RETURN FALSE;
2514 ELSE
2515 FOR j IN 1..p_email_objs.COUNT LOOP
2516 IF(l_email_cpref AND
2517 (p_email_objs(j).contact_pref_objs IS NULL OR
2518 p_email_objs(j).contact_pref_objs.COUNT < 1)) THEN
2519 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2520 fnd_message.set_token('ENTITY' ,'EMAIL-CONTACT_PREFERENCE');
2521 fnd_msg_pub.add;
2522 RETURN FALSE;
2523 END IF;
2524 END LOOP;
2525 END IF;
2526 WHEN l_bus_object.business_object_code(i) = 'TLX' THEN
2527 IF(p_telex_objs IS NULL OR p_telex_objs.COUNT < 1) THEN
2528 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_OBJ');
2529 fnd_message.set_token('OBJECT' ,'TELEX');
2530 fnd_msg_pub.add;
2531 RETURN FALSE;
2532 ELSE
2533 FOR j IN 1..p_telex_objs.COUNT LOOP
2534 IF(l_telex_cpref AND
2535 (p_telex_objs(j).contact_pref_objs IS NULL OR
2536 p_telex_objs(j).contact_pref_objs.COUNT < 1)) THEN
2537 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2538 fnd_message.set_token('ENTITY' ,'TLX-CONTACT_PREFERENCE');
2539 fnd_msg_pub.add;
2540 RETURN FALSE;
2541 END IF;
2542 END LOOP;
2543 END IF;
2544 WHEN l_bus_object.business_object_code(i) = 'WEB' THEN
2545 IF(p_web_objs IS NULL OR p_web_objs.COUNT < 1) THEN
2546 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_OBJ');
2547 fnd_message.set_token('OBJECT' ,'WEB');
2548 fnd_msg_pub.add;
2549 RETURN FALSE;
2550 ELSE
2551 FOR j IN 1..p_web_objs.COUNT LOOP
2552 IF(l_web_cpref AND
2553 (p_web_objs(j).contact_pref_objs IS NULL OR
2554 p_web_objs(j).contact_pref_objs.COUNT < 1)) THEN
2555 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2556 fnd_message.set_token('ENTITY' ,'WEB-CONTACT_PREFERENCE');
2557 fnd_msg_pub.add;
2558 RETURN FALSE;
2559 END IF;
2560 END LOOP;
2561 END IF;
2562 WHEN l_bus_object.business_object_code(i) = 'EDI' THEN
2563 IF(p_edi_objs IS NULL OR p_edi_objs.COUNT < 1) THEN
2564 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_OBJ');
2565 fnd_message.set_token('OBJECT' ,'EDI');
2566 fnd_msg_pub.add;
2567 RETURN FALSE;
2568 ELSE
2569 FOR j IN 1..p_edi_objs.COUNT LOOP
2570 IF(l_edi_cpref AND
2571 (p_edi_objs(j).contact_pref_objs IS NULL OR
2572 p_edi_objs(j).contact_pref_objs.COUNT < 1)) THEN
2573 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2574 fnd_message.set_token('ENTITY' ,'EDI-CONTACT_PREFERENCE');
2575 fnd_msg_pub.add;
2576 RETURN FALSE;
2577 END IF;
2578 END LOOP;
2579 END IF;
2580 WHEN l_bus_object.business_object_code(i) = 'EFT' THEN
2581 IF(p_eft_objs IS NULL OR p_eft_objs.COUNT < 1) THEN
2582 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_OBJ');
2583 fnd_message.set_token('OBJECT' ,'EFT');
2584 fnd_msg_pub.add;
2585 RETURN FALSE;
2586 ELSE
2587 FOR j IN 1..p_eft_objs.COUNT LOOP
2588 IF(l_eft_cpref AND
2589 (p_eft_objs(j).contact_pref_objs IS NULL OR
2590 p_eft_objs(j).contact_pref_objs.COUNT < 1)) THEN
2591 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2592 fnd_message.set_token('ENTITY' ,'EFT-CONTACT_PREFERENCE');
2593 fnd_msg_pub.add;
2594 RETURN FALSE;
2595 END IF;
2596 END LOOP;
2597 END IF;
2598 WHEN l_bus_object.business_object_code(i) = 'SMS' THEN
2599 IF(p_sms_objs IS NULL OR p_sms_objs.COUNT < 1) THEN
2600 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_OBJ');
2601 fnd_message.set_token('OBJECT' ,'SMS');
2602 fnd_msg_pub.add;
2603 RETURN FALSE;
2604 ELSE
2605 FOR j IN 1..p_sms_objs.COUNT LOOP
2606 IF(l_sms_cpref AND
2607 (p_sms_objs(j).contact_pref_objs IS NULL OR
2608 p_sms_objs(j).contact_pref_objs.COUNT < 1)) THEN
2609 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2610 fnd_message.set_token('ENTITY' ,'SMS-CONTACT_PREFERENCE');
2611 fnd_msg_pub.add;
2612 RETURN FALSE;
2613 END IF;
2614 END LOOP;
2615 END IF;
2616 ELSE
2617 null;
2618 END CASE;
2619 END LOOP;
2620
2621 RETURN TRUE;
2622 END is_cp_bo_comp;
2623
2624 -- FUNCTION is_ps_bo_comp
2625 --
2626 -- DESCRIPTION
2627 -- Return true if party site object is complete. Otherwise, return false.
2628 --
2629 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2630 --
2631 -- ARGUMENTS
2632 -- IN:
2633 -- p_ps_objs List of party site business objects.
2634 -- p_bus_object Business object structure for party site.
2635 -- NOTES
2636 --
2637 -- MODIFICATION HISTORY
2638 --
2639 -- 13-Jul-2005 Arnold Ng o Created.
2640
2641 FUNCTION is_ps_bo_comp(
2642 p_ps_objs IN HZ_PARTY_SITE_BO_TBL,
2643 p_bus_object IN COMPLETENESS_REC_TYPE
2644 ) RETURN BOOLEAN IS
2645
2646 l_bus_object COMPLETENESS_REC_TYPE;
2647 l_cp_bus_object COMPLETENESS_REC_TYPE;
2648 l_valid_obj BOOLEAN;
2649 l_psu BOOLEAN;
2650 l_cpref BOOLEAN;
2651 l_ps_ext BOOLEAN;
2652 l_loc_ext BOOLEAN;
2653 l_phone_code VARCHAR2(30);
2654 l_telex_code VARCHAR2(30);
2655 l_email_code VARCHAR2(30);
2656 l_web_code VARCHAR2(30);
2657 BEGIN
2658 l_psu := FALSE;
2659 l_cpref := FALSE;
2660 l_ps_ext := FALSE;
2661 l_loc_ext := FALSE;
2662 l_phone_code := NULL;
2663 l_telex_code := NULL;
2664 l_email_code := NULL;
2665 l_web_code := NULL;
2666
2667 IF(p_ps_objs IS NULL OR p_ps_objs.COUNT < 1) THEN
2668 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_OBJ');
2669 fnd_message.set_token('OBJECT' ,'PARTY_SITE');
2670 fnd_msg_pub.add;
2671 RETURN FALSE;
2672 END IF;
2673
2674 FOR i IN 1..p_bus_object.business_object_code.COUNT LOOP
2675 -- get all entities of party site, for party site, the only possible
2676 -- entites are HZ_PARTY_SITE_USES, HZ_CONTACT_PREFERENCES
2677 IF(p_bus_object.tca_mandated_flag(i) = 'N' AND
2678 p_bus_object.user_mandated_flag(i) = 'Y' AND
2679 p_bus_object.business_object_code(i) = 'PARTY_SITE' AND
2680 p_bus_object.child_bo_code(i) IS NULL) THEN
2681 CASE
2682 WHEN p_bus_object.entity_name(i) = 'HZ_PARTY_SITE_USES' THEN
2683 l_psu := TRUE;
2684 WHEN p_bus_object.entity_name(i) = 'HZ_CONTACT_PREFERENCES' THEN
2685 l_cpref := TRUE;
2686 WHEN p_bus_object.entity_name(i) = 'HZ_PARTY_SITES_EXT_VL' THEN
2687 l_ps_ext := TRUE;
2688 END CASE;
2689 -- Get contact point business object
2690 ELSIF(p_bus_object.child_bo_code(i) IS NOT NULL AND
2691 p_bus_object.user_mandated_flag(i) = 'Y') THEN
2692 CASE
2693 WHEN p_bus_object.child_bo_code(i) = 'PHONE' THEN
2694 l_phone_code := 'PHONE';
2695 WHEN p_bus_object.child_bo_code(i) = 'TLX' THEN
2696 l_telex_code := 'TLX';
2697 WHEN p_bus_object.child_bo_code(i) = 'EMAIL' THEN
2698 l_email_code := 'EMAIL';
2699 WHEN p_bus_object.child_bo_code(i) = 'WEB' THEN
2700 l_web_code := 'WEB';
2701 ELSE
2702 null;
2703 END CASE;
2704 -- Get location object
2705 ELSIF(p_bus_object.business_object_code(i) = 'LOCATION' AND
2706 p_bus_object.user_mandated_flag(i) = 'Y' AND
2707 p_bus_object.tca_mandated_flag(i) = 'N' AND
2708 p_bus_object.child_bo_code(i) IS NULL) THEN
2709 IF p_bus_object.entity_name(i) = 'HZ_LOCATIONS_EXT_VL' THEN
2710 l_loc_ext := TRUE;
2711 END IF;
2712 END IF;
2713 END LOOP;
2714
2715 IF(l_phone_code IS NOT NULL OR l_telex_code IS NOT NULL OR
2716 l_email_code IS NOT NULL OR l_web_code IS NOT NULL) THEN
2717 get_cp_from_rec(
2718 p_phone_code => l_phone_code,
2719 p_email_code => l_email_code,
2720 p_telex_code => l_telex_code,
2721 p_web_code => l_web_code,
2722 p_edi_code => NULL,
2723 p_eft_code => NULL,
2724 p_sms_code => NULL,
2725 p_bus_object => p_bus_object,
2726 x_bus_object => l_cp_bus_object
2727 );
2728 END IF;
2729
2730 FOR i IN 1..p_ps_objs.COUNT LOOP
2731 IF(p_ps_objs(i).location_obj IS NULL) THEN
2732 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_OBJ');
2733 fnd_message.set_token('OBJECT' ,'LOCATION');
2734 fnd_msg_pub.add;
2735 RETURN FALSE;
2736 END IF;
2737 IF(l_psu AND
2738 (p_ps_objs(i).party_site_use_objs IS NULL OR
2739 p_ps_objs(i).party_site_use_objs.COUNT < 1)) THEN
2740 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2741 fnd_message.set_token('OBJECT' ,'PARTY_SITE_USE');
2742 fnd_msg_pub.add;
2743 RETURN FALSE;
2744 END IF;
2745 IF(l_cpref AND
2746 (p_ps_objs(i).contact_pref_objs IS NULL OR
2747 p_ps_objs(i).contact_pref_objs.COUNT < 1)) THEN
2748 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2749 fnd_message.set_token('OBJECT' ,'PARTY_SITE: CONTACT_PREFERENCE');
2750 fnd_msg_pub.add;
2751 RETURN FALSE;
2752 END IF;
2753 IF(l_ps_ext AND
2754 (p_ps_objs(i).ext_attributes_objs IS NULL OR
2755 p_ps_objs(i).ext_attributes_objs.COUNT < 1)) THEN
2756 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2757 fnd_message.set_token('OBJECT' ,'PARTY_SITE: EXTENSIBILITY');
2758 fnd_msg_pub.add;
2759 RETURN FALSE;
2760 END IF;
2761 IF(l_loc_ext AND
2762 (p_ps_objs(i).location_obj.ext_attributes_objs IS NULL OR
2763 p_ps_objs(i).location_obj.ext_attributes_objs.COUNT < 1)) THEN
2764 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2765 fnd_message.set_token('OBJECT' ,'LOCATION: EXTENSIBILITY');
2766 fnd_msg_pub.add;
2767 RETURN FALSE;
2768 END IF;
2769 IF(l_phone_code IS NOT NULL OR l_telex_code IS NOT NULL OR
2770 l_email_code IS NOT NULL OR l_web_code IS NOT NULL) THEN
2771 -- check contact point business object for party site
2772 l_valid_obj := is_cp_bo_comp(
2773 p_phone_objs => p_ps_objs(i).phone_objs,
2774 p_email_objs => p_ps_objs(i).email_objs,
2775 p_telex_objs => p_ps_objs(i).telex_objs,
2776 p_web_objs => p_ps_objs(i).web_objs,
2777 p_edi_objs => NULL,
2778 p_eft_objs => NULL,
2779 p_sms_objs => NULL,
2780 p_bus_object => l_cp_bus_object
2781 );
2782 IF NOT(l_valid_obj) THEN
2783 RETURN FALSE;
2784 END IF;
2785 END IF;
2786 END LOOP;
2787
2788 RETURN TRUE;
2789 END is_ps_bo_comp;
2790
2791 -- FUNCTION is_person_bo_comp
2792 --
2793 -- DESCRIPTION
2794 -- Return true if person object is complete. Otherwise, return false.
2795 --
2796 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2797 --
2798 -- ARGUMENTS
2799 -- IN:
2800 -- p_person_obj Person business objects.
2801 -- p_bus_object Business object structure for person.
2802 -- NOTES
2803 --
2804 -- MODIFICATION HISTORY
2805 --
2806 -- 13-Jul-2005 Arnold Ng o Created.
2807
2808 FUNCTION is_person_bo_comp(
2809 p_person_obj IN HZ_PERSON_BO,
2810 p_bus_object IN COMPLETENESS_REC_TYPE
2811 ) RETURN BOOLEAN IS
2812 l_bus_object COMPLETENESS_REC_TYPE;
2813 l_cp_bus_object COMPLETENESS_REC_TYPE;
2814 l_ps_bus_object COMPLETENESS_REC_TYPE;
2815 l_valid_obj BOOLEAN;
2816
2817 l_pref BOOLEAN;
2818 l_rel BOOLEAN;
2819 l_class BOOLEAN;
2820 l_lang BOOLEAN;
2821 l_edu BOOLEAN;
2822 l_citiz BOOLEAN;
2823 l_emp_hist BOOLEAN;
2824 l_work_class BOOLEAN;
2825 l_int BOOLEAN;
2826 l_cert BOOLEAN;
2827 l_fin_prof BOOLEAN;
2828 l_cpref BOOLEAN;
2829 l_ps BOOLEAN;
2830 l_ext BOOLEAN;
2831
2832 l_phone_code VARCHAR2(30);
2833 l_email_code VARCHAR2(30);
2834 l_web_code VARCHAR2(30);
2835 l_sms_code VARCHAR2(30);
2836 BEGIN
2837 l_pref := FALSE;
2838 l_rel := FALSE;
2839 l_class := FALSE;
2840 l_lang := FALSE;
2841 l_edu := FALSE;
2842 l_citiz := FALSE;
2843 l_emp_hist := FALSE;
2844 l_work_class := FALSE;
2845 l_int := FALSE;
2846 l_cert := FALSE;
2847 l_fin_prof := FALSE;
2848 l_cpref := FALSE;
2849 l_ps := FALSE;
2850 l_ext := FALSE;
2851
2852 FOR i IN 1..p_bus_object.business_object_code.COUNT LOOP
2853 -- get all entities of person, for person, the possible entites are
2854 -- HZ_PARTY_PREFERENCES, HZ_RELATIONSHIPS, HZ_CODE_ASSIGNMENTS,
2855 -- HZ_PERSON_LANGUAGE, HZ_EDUCATION, HZ_CITIZENSHIP, HZ_EMPLOYMENT_HISTORY
2856 -- HZ_PERSON_INTEREST, HZ_CERTIFICATIONS, HZ_FINANCIAL_PROFILE,
2857 -- HZ_CONTACT_PREFERENCES
2858 IF(p_bus_object.tca_mandated_flag(i) = 'N' AND
2859 p_bus_object.user_mandated_flag(i) = 'Y' AND
2860 p_bus_object.business_object_code(i) = 'PERSON' AND
2861 p_bus_object.child_bo_code(i) IS NULL) THEN
2862 CASE
2863 WHEN p_bus_object.entity_name(i) = 'HZ_PARTY_PREFERENCES' THEN
2864 l_pref := TRUE;
2865 WHEN p_bus_object.entity_name(i) = 'HZ_RELATIONSHIPS' THEN
2866 l_rel := TRUE;
2867 WHEN p_bus_object.entity_name(i) = 'HZ_CODE_ASSIGNMENTS' THEN
2868 l_class := TRUE;
2869 WHEN p_bus_object.entity_name(i) = 'HZ_PERSON_LANGUAGE' THEN
2870 l_lang := TRUE;
2871 WHEN p_bus_object.entity_name(i) = 'HZ_EDUCATION' THEN
2872 l_edu := TRUE;
2873 WHEN p_bus_object.entity_name(i) = 'HZ_CITIZENSHIP' THEN
2874 l_citiz := TRUE;
2875 WHEN p_bus_object.entity_name(i) = 'HZ_PERSON_INTEREST' THEN
2876 l_int := TRUE;
2877 WHEN p_bus_object.entity_name(i) = 'HZ_CERTIFICATIONS' THEN
2878 l_cert := TRUE;
2879 WHEN p_bus_object.entity_name(i) = 'HZ_FINANCIAL_PROFILE' THEN
2880 l_fin_prof := TRUE;
2881 WHEN p_bus_object.entity_name(i) = 'HZ_CONTACT_PREFERENCES' THEN
2882 l_cpref := TRUE;
2883 WHEN p_bus_object.entity_name(i) = 'HZ_PER_PROFILES_EXT_VL' THEN
2884 l_ext := TRUE;
2885 END CASE;
2886 ELSIF(p_bus_object.child_bo_code(i) IS NOT NULL AND
2887 p_bus_object.business_object_code(i) = 'PERSON' AND
2888 p_bus_object.user_mandated_flag(i) = 'Y') THEN
2889 CASE
2890 WHEN p_bus_object.child_bo_code(i) = 'PHONE' THEN
2891 l_phone_code := 'PHONE';
2892 WHEN p_bus_object.child_bo_code(i) = 'EMAIL' THEN
2893 l_email_code := 'EMAIL';
2894 WHEN p_bus_object.child_bo_code(i) = 'WEB' THEN
2895 l_web_code := 'WEB';
2896 WHEN p_bus_object.child_bo_code(i) = 'SMS' THEN
2897 l_sms_code := 'SMS';
2898 WHEN p_bus_object.child_bo_code(i) = 'PARTY_SITE' THEN
2899 l_ps := TRUE;
2900 WHEN p_bus_object.child_bo_code(i) = 'EMP_HIST' THEN
2901 l_emp_hist := TRUE;
2902 END CASE;
2903 ELSIF(p_bus_object.business_object_code(i) = 'EMP_HIST' AND
2904 p_bus_object.entity_name(i) = 'HZ_WORK_CLASS' AND
2905 p_bus_object.user_mandated_flag(i) = 'Y') THEN
2906 l_work_class := TRUE;
2907 END IF;
2908 END LOOP;
2909
2910 IF(l_phone_code IS NOT NULL OR l_email_code IS NOT NULL OR
2911 l_web_code IS NOT NULL OR l_sms_code IS NOT NULL) THEN
2912 get_cp_from_rec(
2913 p_phone_code => l_phone_code,
2914 p_email_code => l_email_code,
2915 p_telex_code => NULL,
2916 p_web_code => l_web_code,
2917 p_edi_code => NULL,
2918 p_eft_code => NULL,
2919 p_sms_code => l_sms_code,
2920 p_bus_object => p_bus_object,
2921 x_bus_object => l_cp_bus_object
2922 );
2923 END IF;
2924
2925 IF(l_ps) THEN
2926 get_ps_from_rec(
2927 p_bus_object => p_bus_object,
2928 x_bus_object => l_ps_bus_object
2929 );
2930 END IF;
2931
2932 IF(l_pref AND
2933 (p_person_obj.preference_objs IS NULL OR
2934 p_person_obj.preference_objs.COUNT < 1)) THEN
2935 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2936 fnd_message.set_token('OBJECT' ,'PARTY_PREFERENCE');
2937 fnd_msg_pub.add;
2938 RETURN FALSE;
2939 END IF;
2940 IF(l_class AND
2941 (p_person_obj.class_objs IS NULL OR
2942 p_person_obj.class_objs.COUNT < 1)) THEN
2943 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2944 fnd_message.set_token('OBJECT' ,'CLASSIFICATION');
2945 fnd_msg_pub.add;
2946 RETURN FALSE;
2947 END IF;
2948 IF(l_lang AND
2949 (p_person_obj.language_objs IS NULL OR
2950 p_person_obj.language_objs.COUNT < 1)) THEN
2951 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2952 fnd_message.set_token('OBJECT' ,'LANGUAGE');
2953 fnd_msg_pub.add;
2954 RETURN FALSE;
2955 END IF;
2956 IF(l_edu AND
2957 (p_person_obj.education_objs IS NULL OR
2958 p_person_obj.education_objs.COUNT < 1)) THEN
2959 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2960 fnd_message.set_token('OBJECT' ,'EDUCATION');
2961 fnd_msg_pub.add;
2962 RETURN FALSE;
2963 END IF;
2964 IF(l_citiz AND
2965 (p_person_obj.citizenship_objs IS NULL OR
2966 p_person_obj.citizenship_objs.COUNT < 1)) THEN
2967 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2968 fnd_message.set_token('OBJECT' ,'CITIZENSHIP');
2969 fnd_msg_pub.add;
2970 RETURN FALSE;
2971 END IF;
2972 IF(l_emp_hist AND
2973 (p_person_obj.employ_hist_objs IS NULL OR
2974 p_person_obj.employ_hist_objs.COUNT < 1)) THEN
2975 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2976 fnd_message.set_token('OBJECT' ,'EMPLOYMENT_HISTORY');
2977 fnd_msg_pub.add;
2978 RETURN FALSE;
2979 FOR j IN 1..p_person_obj.employ_hist_objs.COUNT LOOP
2980 IF(l_work_class AND
2981 (p_person_obj.employ_hist_objs(j).work_class_objs IS NULL OR
2982 p_person_obj.employ_hist_objs(j).work_class_objs.COUNT < 1)) THEN
2983 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2984 fnd_message.set_token('OBJECT' ,'WORK_CLASS');
2985 fnd_msg_pub.add;
2986 RETURN FALSE;
2987 END IF;
2988 END LOOP;
2989 END IF;
2990 IF(l_int AND
2991 (p_person_obj.interest_objs IS NULL OR
2992 p_person_obj.interest_objs.COUNT < 1)) THEN
2993 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2994 fnd_message.set_token('OBJECT' ,'PERSON_INTEREST');
2995 fnd_msg_pub.add;
2996 RETURN FALSE;
2997 END IF;
2998 IF(l_cert AND
2999 (p_person_obj.certification_objs IS NULL OR
3000 p_person_obj.certification_objs.COUNT < 1)) THEN
3001 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3002 fnd_message.set_token('OBJECT' ,'CERTIFICATION');
3003 fnd_msg_pub.add;
3004 RETURN FALSE;
3005 END IF;
3006 IF(l_fin_prof AND
3007 (p_person_obj.financial_prof_objs IS NULL OR
3008 p_person_obj.financial_prof_objs.COUNT < 1)) THEN
3009 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3010 fnd_message.set_token('OBJECT' ,'FINANCIAL_PROFILE');
3011 fnd_msg_pub.add;
3012 RETURN FALSE;
3013 END IF;
3014 IF(l_rel AND
3015 (p_person_obj.relationship_objs IS NULL OR
3016 p_person_obj.relationship_objs.COUNT < 1)) THEN
3017 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3018 fnd_message.set_token('OBJECT' ,'RELATIONSHIP');
3019 fnd_msg_pub.add;
3020 RETURN FALSE;
3021 END IF;
3022 IF(l_cpref AND
3023 (p_person_obj.contact_pref_objs IS NULL OR
3024 p_person_obj.contact_pref_objs.COUNT < 1)) THEN
3025 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3026 fnd_message.set_token('OBJECT' ,'PERSON: CONTACT_PREFERENCE');
3027 fnd_msg_pub.add;
3028 RETURN FALSE;
3029 END IF;
3030 IF(l_ext AND
3031 (p_person_obj.ext_attributes_objs IS NULL OR
3032 p_person_obj.ext_attributes_objs.COUNT < 1)) THEN
3033 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3034 fnd_message.set_token('OBJECT' ,'PERSON: EXTENSIBILITY');
3035 fnd_msg_pub.add;
3036 RETURN FALSE;
3037 END IF;
3038 IF(l_phone_code IS NOT NULL OR l_email_code IS NOT NULL OR
3039 l_web_code IS NOT NULL OR l_sms_code IS NOT NULL) THEN
3040 -- check contact point business object for person
3041 l_valid_obj := is_cp_bo_comp(
3042 p_phone_objs => p_person_obj.phone_objs,
3043 p_email_objs => p_person_obj.email_objs,
3044 p_telex_objs => NULL,
3045 p_web_objs => p_person_obj.web_objs,
3046 p_edi_objs => NULL,
3047 p_eft_objs => NULL,
3048 p_sms_objs => p_person_obj.sms_objs,
3049 p_bus_object => l_cp_bus_object
3050 );
3051 IF NOT(l_valid_obj) THEN
3052 RETURN FALSE;
3053 END IF;
3054 END IF;
3055 IF(l_ps) THEN
3056 -- check contact point business object for person
3057 l_valid_obj := is_ps_bo_comp(
3058 p_ps_objs => p_person_obj.party_site_objs,
3059 p_bus_object => l_ps_bus_object
3060 );
3061 IF NOT(l_valid_obj) THEN
3062 RETURN FALSE;
3063 END IF;
3064 END IF;
3065
3066 RETURN TRUE;
3067 END is_person_bo_comp;
3068
3069 -- FUNCTION is_oc_bo_comp
3070 --
3071 -- DESCRIPTION
3072 -- Return true if org contact object is complete. Otherwise, return false.
3073 --
3074 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3075 --
3076 -- ARGUMENTS
3077 -- IN:
3078 -- p_oc_objs List of organization contact business objects.
3079 -- p_bus_object Business object structure for organization contact.
3080 -- NOTES
3081 --
3082 -- MODIFICATION HISTORY
3083 --
3084 -- 13-Jul-2005 Arnold Ng o Created.
3085
3086 FUNCTION is_oc_bo_comp(
3087 p_oc_objs IN HZ_ORG_CONTACT_BO_TBL,
3088 p_bus_object IN COMPLETENESS_REC_TYPE
3089 ) RETURN BOOLEAN IS
3090 l_bus_object COMPLETENESS_REC_TYPE;
3091 l_cp_bus_object COMPLETENESS_REC_TYPE;
3092 l_ps_bus_object COMPLETENESS_REC_TYPE;
3093 l_per_bus_object COMPLETENESS_REC_TYPE;
3094 l_valid_obj BOOLEAN;
3095
3096 l_ocr BOOLEAN;
3097 l_cpref BOOLEAN;
3098 l_ps BOOLEAN;
3099
3100 l_phone_code VARCHAR2(30);
3101 l_telex_code VARCHAR2(30);
3102 l_email_code VARCHAR2(30);
3103 l_web_code VARCHAR2(30);
3104 l_sms_code VARCHAR2(30);
3105 BEGIN
3106 -- For org contact, person bo is a must and person only has one
3107 -- object, not a list of object
3108 l_ocr := FALSE;
3109 l_cpref := FALSE;
3110 l_ps := FALSE;
3111
3112 IF(p_oc_objs IS NULL OR p_oc_objs.COUNT < 1) THEN
3113 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_OBJ');
3114 fnd_message.set_token('OBJECT' ,'ORG_CONTACT');
3115 fnd_msg_pub.add;
3116 RETURN FALSE;
3117 END IF;
3118
3119 FOR i IN 1..p_bus_object.business_object_code.COUNT LOOP
3120 -- get all entities of org contact, for org contact, the possible entites are
3121 -- HZ_ORG_CONTACT_ROLES
3122 IF(p_bus_object.tca_mandated_flag(i) = 'N' AND
3123 p_bus_object.user_mandated_flag(i) = 'Y' AND
3124 p_bus_object.business_object_code(i) = 'ORG_CONTACT' AND
3125 p_bus_object.child_bo_code(i) IS NULL) THEN
3126 IF(p_bus_object.entity_name(i) = 'HZ_ORG_CONTACT_ROLES') THEN
3127 l_ocr := TRUE;
3128 ELSIF(p_bus_object.entity_name(i) = 'HZ_CONTACT_PREFERENCES') THEN
3129 l_cpref := TRUE;
3130 END IF;
3131 -- Get contact point business object
3132 ELSIF(p_bus_object.child_bo_code(i) IS NOT NULL AND
3133 p_bus_object.business_object_code(i) = 'ORG_CONTACT' AND
3134 p_bus_object.user_mandated_flag(i) = 'Y') THEN
3135 CASE
3136 WHEN p_bus_object.child_bo_code(i) = 'PHONE' THEN
3137 l_phone_code := 'PHONE';
3138 WHEN p_bus_object.child_bo_code(i) = 'TLX' THEN
3139 l_telex_code := 'TLX';
3140 WHEN p_bus_object.child_bo_code(i) = 'EMAIL' THEN
3141 l_email_code := 'EMAIL';
3142 WHEN p_bus_object.child_bo_code(i) = 'WEB' THEN
3143 l_web_code := 'WEB';
3144 WHEN p_bus_object.child_bo_code(i) = 'SMS' THEN
3145 l_sms_code := 'SMS';
3146 WHEN p_bus_object.child_bo_code(i) = 'PARTY_SITE' THEN
3147 l_ps := TRUE;
3148 ELSE
3149 null;
3150 END CASE;
3151 END IF;
3152 END LOOP;
3153
3154 IF(l_phone_code IS NOT NULL OR l_telex_code IS NOT NULL OR
3155 l_email_code IS NOT NULL OR l_web_code IS NOT NULL OR
3156 l_sms_code IS NOT NULL) THEN
3157 get_cp_from_rec(
3158 p_phone_code => l_phone_code,
3159 p_email_code => l_email_code,
3160 p_telex_code => l_telex_code,
3161 p_web_code => l_web_code,
3162 p_edi_code => NULL,
3163 p_eft_code => NULL,
3164 p_sms_code => l_sms_code,
3165 p_bus_object => p_bus_object,
3166 x_bus_object => l_cp_bus_object
3167 );
3168 END IF;
3169
3170 IF(l_ps) THEN
3171 get_ps_from_rec(
3172 p_bus_object => p_bus_object,
3173 x_bus_object => l_ps_bus_object
3174 );
3175 END IF;
3176
3177 FOR i IN 1..p_oc_objs.COUNT LOOP
3178 IF(p_oc_objs(i).person_profile_obj IS NULL) THEN
3179 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3180 fnd_message.set_token('OBJECT' ,'PERSON_CONTACT');
3181 fnd_msg_pub.add;
3182 RETURN FALSE;
3183 END IF;
3184
3185 IF(l_ocr AND
3186 (p_oc_objs(i).org_contact_role_objs IS NULL OR
3187 p_oc_objs(i).org_contact_role_objs.COUNT < 1)) THEN
3188 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3189 fnd_message.set_token('OBJECT' ,'ORG_CONTACT_ROLE');
3190 fnd_msg_pub.add;
3191 RETURN FALSE;
3192 END IF;
3193 IF(l_cpref AND
3194 (p_oc_objs(i).contact_pref_objs IS NULL OR
3195 p_oc_objs(i).contact_pref_objs.COUNT < 1)) THEN
3196 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3197 fnd_message.set_token('OBJECT' ,'ORG_CONTACT: CONTACT_PREFERENCE');
3198 fnd_msg_pub.add;
3199 RETURN FALSE;
3200 END IF;
3201
3202 IF(l_phone_code IS NOT NULL OR l_telex_code IS NOT NULL OR
3203 l_email_code IS NOT NULL OR l_web_code IS NOT NULL OR
3204 l_sms_code IS NOT NULL) THEN
3205 -- check contact point business object for org contact
3206 l_valid_obj := is_cp_bo_comp(
3207 p_phone_objs => p_oc_objs(i).phone_objs,
3208 p_email_objs => p_oc_objs(i).email_objs,
3209 p_telex_objs => p_oc_objs(i).telex_objs,
3210 p_web_objs => p_oc_objs(i).web_objs,
3211 p_edi_objs => NULL,
3212 p_eft_objs => NULL,
3213 p_sms_objs => p_oc_objs(i).sms_objs,
3214 p_bus_object => l_cp_bus_object
3215 );
3216 IF NOT(l_valid_obj) THEN
3217 RETURN FALSE;
3218 END IF;
3219 END IF;
3220 IF(l_ps) THEN
3221 -- check contact point business object for org contact
3222 l_valid_obj := is_ps_bo_comp(
3223 p_ps_objs => p_oc_objs(i).party_site_objs,
3224 p_bus_object => l_ps_bus_object
3225 );
3226 IF NOT(l_valid_obj) THEN
3227 RETURN FALSE;
3228 END IF;
3229 END IF;
3230 END LOOP;
3231
3232 RETURN TRUE;
3233 END is_oc_bo_comp;
3234
3235 -- FUNCTION is_org_bo_comp
3236 --
3237 -- DESCRIPTION
3238 -- Return true if organization object is complete. Otherwise, return false.
3239 --
3240 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3241 --
3242 -- ARGUMENTS
3243 -- IN:
3244 -- p_organization_obj Organization business objects.
3245 -- p_bus_object Business object structure for organization.
3246 -- NOTES
3247 --
3248 -- MODIFICATION HISTORY
3249 --
3250 -- 13-Jul-2005 Arnold Ng o Created.
3251
3252 FUNCTION is_org_bo_comp(
3253 p_organization_obj IN HZ_ORGANIZATION_BO,
3254 p_bus_object IN COMPLETENESS_REC_TYPE
3255 ) RETURN BOOLEAN IS
3256 l_bus_object COMPLETENESS_REC_TYPE;
3257 l_cp_bus_object COMPLETENESS_REC_TYPE;
3258 l_ps_bus_object COMPLETENESS_REC_TYPE;
3259 l_oc_bus_object COMPLETENESS_REC_TYPE;
3260 l_valid_obj BOOLEAN;
3261
3262 l_pref BOOLEAN;
3263 l_credit BOOLEAN;
3264 l_rel BOOLEAN;
3265 l_class BOOLEAN;
3266 l_fin_report BOOLEAN;
3267 l_cpref BOOLEAN;
3268 l_cert BOOLEAN;
3269 l_fin_prof BOOLEAN;
3270 l_ps BOOLEAN;
3271 l_oc BOOLEAN;
3272 l_ext BOOLEAN;
3273
3274 l_phone_code VARCHAR2(30);
3275 l_telex_code VARCHAR2(30);
3276 l_email_code VARCHAR2(30);
3277 l_web_code VARCHAR2(30);
3278 l_edi_code VARCHAR2(30);
3279 l_eft_code VARCHAR2(30);
3280 BEGIN
3281 l_pref := FALSE;
3282 l_credit := FALSE;
3283 l_rel := FALSE;
3284 l_class := FALSE;
3285 l_fin_report := FALSE;
3286 l_cpref := FALSE;
3287 l_cert := FALSE;
3288 l_fin_prof := FALSE;
3289 l_ps := FALSE;
3290 l_oc := FALSE;
3291 l_ext := FALSE;
3292
3293 FOR i IN 1..p_bus_object.business_object_code.COUNT LOOP
3294 -- get all entities of org, for org, the possible entites are
3295 -- HZ_PARTY_PREFERENCES, HZ_CREDIT_RATINGS, HZ_RELATIONSHIPS,
3296 -- HZ_CODE_ASSIGNMENTS, HZ_FINANCIAL_REPORTS, HZ_FINANCIAL_NUMBERS,
3297 -- HZ_CERTIFICATIONS, HZ_FINANCIAL_PROFILE, HZ_CONTACT_PREFERENCES
3298 IF(p_bus_object.tca_mandated_flag(i) = 'N' AND
3299 p_bus_object.user_mandated_flag(i) = 'Y' AND
3300 p_bus_object.business_object_code(i) = 'ORG' AND
3301 p_bus_object.child_bo_code(i) IS NULL) THEN
3302 CASE
3303 WHEN p_bus_object.entity_name(i) = 'HZ_PARTY_PREFERENCES' THEN
3304 l_pref := TRUE;
3305 WHEN p_bus_object.entity_name(i) = 'HZ_CREDIT_RATINGS' THEN
3306 l_credit := TRUE;
3307 WHEN p_bus_object.entity_name(i) = 'HZ_RELATIONSHIPS' THEN
3308 l_rel := TRUE;
3309 WHEN p_bus_object.entity_name(i) = 'HZ_CODE_ASSIGNMENTS' THEN
3310 l_class := TRUE;
3311 WHEN p_bus_object.entity_name(i) = 'HZ_CERTIFICATIONS' THEN
3312 l_cert := TRUE;
3313 WHEN p_bus_object.entity_name(i) = 'HZ_FINANCIAL_PROFILE' THEN
3314 l_fin_prof := TRUE;
3315 WHEN p_bus_object.entity_name(i) = 'HZ_CONTACT_PREFERENCES' THEN
3316 l_cpref := TRUE;
3317 WHEN p_bus_object.entity_name(i) = 'HZ_ORG_PROFILES_EXT_VL' THEN
3318 l_ext := TRUE;
3319 END CASE;
3320 ELSIF(p_bus_object.child_bo_code(i) IS NOT NULL AND
3321 p_bus_object.business_object_code(i) = 'ORG' AND
3322 p_bus_object.user_mandated_flag(i) = 'Y') THEN
3323 CASE
3324 WHEN p_bus_object.child_bo_code(i) = 'PHONE' THEN
3325 l_phone_code := 'PHONE';
3326 WHEN p_bus_object.child_bo_code(i) = 'TLX' THEN
3327 l_telex_code := 'TLX';
3328 WHEN p_bus_object.child_bo_code(i) = 'EMAIL' THEN
3329 l_email_code := 'EMAIL';
3330 WHEN p_bus_object.child_bo_code(i) = 'WEB' THEN
3331 l_web_code := 'WEB';
3332 WHEN p_bus_object.child_bo_code(i) = 'EDI' THEN
3333 l_edi_code := 'EDI';
3334 WHEN p_bus_object.child_bo_code(i) = 'EFT' THEN
3335 l_eft_code := 'EFT';
3336 WHEN p_bus_object.child_bo_code(i) = 'PARTY_SITE' THEN
3337 l_ps := TRUE;
3338 WHEN p_bus_object.child_bo_code(i) = 'ORG_CONTACT' THEN
3339 l_oc := TRUE;
3340 WHEN p_bus_object.child_bo_code(i) = 'FIN_REPORT' THEN
3341 l_fin_report := TRUE;
3342 END CASE;
3343 END IF;
3344 END LOOP;
3345
3346 IF(l_phone_code IS NOT NULL OR l_telex_code IS NOT NULL OR
3347 l_email_code IS NOT NULL OR l_web_code IS NOT NULL OR
3348 l_edi_code IS NOT NULL OR l_eft_code IS NOT NULL) THEN
3349 get_cp_from_rec(
3350 p_phone_code => l_phone_code,
3351 p_email_code => l_email_code,
3352 p_telex_code => l_telex_code,
3353 p_web_code => l_web_code,
3354 p_edi_code => l_edi_code,
3355 p_eft_code => l_eft_code,
3356 p_sms_code => NULL,
3357 p_bus_object => p_bus_object,
3358 x_bus_object => l_cp_bus_object
3359 );
3360 END IF;
3361
3362 IF(l_ps) THEN
3363 get_ps_from_rec(
3364 p_bus_object => p_bus_object,
3365 x_bus_object => l_ps_bus_object
3366 );
3367 END IF;
3368
3369 IF(l_oc) THEN
3370 get_bus_obj_struct(
3371 p_bus_object_code => 'ORG_CONTACT',
3372 x_bus_object => l_oc_bus_object
3373 );
3374 END IF;
3375
3376 IF(l_pref AND
3377 (p_organization_obj.preference_objs IS NULL OR
3378 p_organization_obj.preference_objs.COUNT < 1)) THEN
3379 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3380 fnd_message.set_token('OBJECT' ,'PARTY_PREFERENCE');
3381 fnd_msg_pub.add;
3382 RETURN FALSE;
3383 END IF;
3384 IF(l_credit AND
3385 (p_organization_obj.credit_rating_objs IS NULL OR
3386 p_organization_obj.credit_rating_objs.COUNT < 1)) THEN
3387 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3388 fnd_message.set_token('OBJECT' ,'CREDIT_RATING');
3389 fnd_msg_pub.add;
3390 RETURN FALSE;
3391 END IF;
3392 IF(l_rel AND
3393 (p_organization_obj.relationship_objs IS NULL OR
3394 p_organization_obj.relationship_objs.COUNT < 1)) THEN
3395 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3396 fnd_message.set_token('OBJECT' ,'RELATIONSHIP');
3397 fnd_msg_pub.add;
3398 RETURN FALSE;
3399 END IF;
3400 IF(l_class AND
3401 (p_organization_obj.class_objs IS NULL OR
3402 p_organization_obj.class_objs.COUNT < 1)) THEN
3403 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3404 fnd_message.set_token('OBJECT' ,'CLASSIFICATION');
3405 fnd_msg_pub.add;
3406 RETURN FALSE;
3407 END IF;
3408 IF(l_fin_report AND
3409 (p_organization_obj.financial_report_objs IS NULL OR
3410 p_organization_obj.financial_report_objs.COUNT < 1)) THEN
3411 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3412 fnd_message.set_token('OBJECT' ,'FINANCIAL_REPORT');
3413 fnd_msg_pub.add;
3414 RETURN FALSE;
3415
3416 -- financial report always require financial number
3417 FOR j IN 1..p_organization_obj.financial_report_objs.COUNT LOOP
3418 IF(p_organization_obj.financial_report_objs(j).financial_number_objs IS NULL OR
3419 p_organization_obj.financial_report_objs(j).financial_number_objs.COUNT < 1) THEN
3420 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3421 fnd_message.set_token('OBJECT' ,'FINANCIAL_NUMBER');
3422 fnd_msg_pub.add;
3423 RETURN FALSE;
3424 END IF;
3425 END LOOP;
3426 END IF;
3427 IF(l_cert AND
3428 (p_organization_obj.certification_objs IS NULL OR
3429 p_organization_obj.certification_objs.COUNT < 1)) THEN
3430 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3431 fnd_message.set_token('OBJECT' ,'CERTIFICATION');
3432 fnd_msg_pub.add;
3433 RETURN FALSE;
3434 END IF;
3435 IF(l_fin_prof AND
3436 (p_organization_obj.financial_prof_objs IS NULL OR
3437 p_organization_obj.financial_prof_objs.COUNT < 1)) THEN
3438 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3439 fnd_message.set_token('OBJECT' ,'FINANCIAL_PROFILE');
3440 fnd_msg_pub.add;
3441 RETURN FALSE;
3442 END IF;
3443 IF(l_cpref AND
3444 (p_organization_obj.contact_pref_objs IS NULL OR
3445 p_organization_obj.contact_pref_objs.COUNT < 1)) THEN
3446 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3447 fnd_message.set_token('OBJECT' ,'ORG: CONTACT_PREFERENCE');
3448 fnd_msg_pub.add;
3449 RETURN FALSE;
3450 END IF;
3451 IF(l_ext AND
3452 (p_organization_obj.ext_attributes_objs IS NULL OR
3453 p_organization_obj.ext_attributes_objs.COUNT < 1)) THEN
3454 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3455 fnd_message.set_token('OBJECT' ,'ORG: EXTENSIBILITY');
3456 fnd_msg_pub.add;
3457 RETURN FALSE;
3458 END IF;
3459
3460 IF(l_phone_code IS NOT NULL OR l_telex_code IS NOT NULL OR
3461 l_email_code IS NOT NULL OR l_web_code IS NOT NULL OR
3462 l_edi_code IS NOT NULL OR l_eft_code IS NOT NULL) THEN
3463 -- check contact point business object for org
3464 l_valid_obj := is_cp_bo_comp(
3465 p_phone_objs => p_organization_obj.phone_objs,
3466 p_email_objs => p_organization_obj.email_objs,
3467 p_telex_objs => p_organization_obj.telex_objs,
3468 p_web_objs => p_organization_obj.web_objs,
3469 p_edi_objs => p_organization_obj.edi_objs,
3470 p_eft_objs => p_organization_obj.eft_objs,
3471 p_sms_objs => NULL,
3472 p_bus_object => l_cp_bus_object
3473 );
3474 IF NOT(l_valid_obj) THEN
3475 RETURN FALSE;
3476 END IF;
3477 END IF;
3478
3479 IF(l_ps) THEN
3480 -- check party site business object for org
3481 l_valid_obj := is_ps_bo_comp(
3482 p_ps_objs => p_organization_obj.party_site_objs,
3483 p_bus_object => l_ps_bus_object
3484 );
3485 IF NOT(l_valid_obj) THEN
3486 RETURN FALSE;
3487 END IF;
3488 END IF;
3489
3490 IF(l_oc) THEN
3491 -- check org contact business object for org
3492 l_valid_obj := is_oc_bo_comp(
3493 p_oc_objs => p_organization_obj.contact_objs,
3494 p_bus_object => l_oc_bus_object
3495 );
3496 IF NOT(l_valid_obj) THEN
3497 RETURN FALSE;
3498 END IF;
3499 END IF;
3500
3501 RETURN TRUE;
3502 END is_org_bo_comp;
3503
3504 -- FUNCTION is_cac_bo_comp
3505 --
3506 -- DESCRIPTION
3507 -- Return true if customer account contact object is complete.
3508 -- Otherwise, return false.
3509 --
3510 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3511 --
3512 -- ARGUMENTS
3513 -- IN:
3514 -- p_cac_objs List of customer account contact business objects.
3515 -- p_bus_object Business object structure for customer account contact.
3516 -- NOTES
3517 --
3518 -- MODIFICATION HISTORY
3519 --
3520 -- 13-Jul-2005 Arnold Ng o Created.
3521
3522 FUNCTION is_cac_bo_comp(
3523 p_cac_objs IN HZ_CUST_ACCT_CONTACT_BO_TBL,
3524 p_bus_object IN COMPLETENESS_REC_TYPE
3525 ) RETURN BOOLEAN IS
3526 l_valid_obj BOOLEAN;
3527 l_rr BOOLEAN;
3528 BEGIN
3529 l_rr := FALSE;
3530
3531 IF(p_cac_objs IS NULL OR p_cac_objs.COUNT < 1) THEN
3532 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_OBJ');
3533 fnd_message.set_token('OBJECT' ,'CUST_ACCT_CONTACT');
3534 fnd_msg_pub.add;
3535 RETURN FALSE;
3536 END IF;
3537
3538 FOR i IN 1..p_bus_object.business_object_code.COUNT LOOP
3539 -- get all entities of cust acct contact, for cust acct contact, the possible entites are
3540 -- HZ_ROLE_RESPONSIBILITY
3541 IF(p_bus_object.tca_mandated_flag(i) = 'N' AND
3542 p_bus_object.user_mandated_flag(i) = 'Y' AND
3543 p_bus_object.business_object_code(i) = 'CUST_ACCT_CONTACT' AND
3544 p_bus_object.child_bo_code(i) IS NULL) THEN
3545 IF(p_bus_object.entity_name(i) = 'HZ_ROLE_RESPONSIBILITY') THEN
3546 l_rr := TRUE;
3547 END IF;
3548 END IF;
3549 END LOOP;
3550
3551 FOR i IN 1..p_cac_objs.COUNT LOOP
3552 -- Check role responsibility objects
3553 IF(l_rr AND
3554 (p_cac_objs(i).contact_role_objs IS NULL OR
3555 p_cac_objs(i).contact_role_objs.COUNT < 1)) THEN
3556 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3557 fnd_message.set_token('OBJECT' ,'ROLE_RESPONSIBILITY');
3558 fnd_msg_pub.add;
3559 RETURN FALSE;
3560 END IF;
3561 END LOOP;
3562
3563 RETURN TRUE;
3564 END is_cac_bo_comp;
3565
3566 -- FUNCTION is_cas_bo_comp
3567 --
3568 -- DESCRIPTION
3569 -- Return true if customer account site object is complete.
3570 -- Otherwise, return false.
3571 --
3572 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3573 --
3574 -- ARGUMENTS
3575 -- IN:
3576 -- p_cas_objs List of customer account site business objects.
3577 -- p_bus_object Business object structure for customer account site.
3578 -- NOTES
3579 --
3580 -- MODIFICATION HISTORY
3581 --
3582 -- 13-Jul-2005 Arnold Ng o Created.
3583
3584 FUNCTION is_cas_bo_comp(
3585 p_cas_objs IN HZ_CUST_ACCT_SITE_BO_TBL,
3586 p_bus_object IN COMPLETENESS_REC_TYPE
3587 ) RETURN BOOLEAN IS
3588 l_valid_obj BOOLEAN;
3589 l_casu BOOLEAN;
3590 l_cp BOOLEAN;
3591 l_cac BOOLEAN;
3592 l_cpa BOOLEAN;
3593 l_bau BOOLEAN;
3594 l_pm BOOLEAN;
3595 l_cac_bus_object COMPLETENESS_REC_TYPE;
3596 BEGIN
3597 l_casu := FALSE;
3598 l_cp := FALSE;
3599 l_cpa := FALSE;
3600 l_cac := FALSE;
3601 l_bau := FALSE;
3602 l_pm := FALSE;
3603
3604 IF(p_cas_objs IS NULL OR p_cas_objs.COUNT < 1) THEN
3605 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_OBJ');
3606 fnd_message.set_token('OBJECT' ,'CUST_ACCT_SITE');
3607 fnd_msg_pub.add;
3608 RETURN FALSE;
3609 END IF;
3610
3611 FOR i IN 1..p_bus_object.business_object_code.COUNT LOOP
3612 -- no entities of cust acct site
3613 IF(p_bus_object.child_bo_code(i) IS NOT NULL AND
3614 p_bus_object.business_object_code(i) = 'CUST_ACCT_SITE' AND
3615 p_bus_object.user_mandated_flag(i) = 'Y') THEN
3616 CASE
3617 WHEN(p_bus_object.child_bo_code(i) = 'CUST_ACCT_SITE_USE' AND
3618 p_bus_object.business_object_code(i) = 'CUST_ACCT_SITE') THEN
3619 l_casu := TRUE;
3620 WHEN(p_bus_object.child_bo_code(i) = 'CUST_ACCT_CONTACT' AND
3621 p_bus_object.business_object_code(i) = 'CUST_ACCT_SITE') THEN
3622 l_cac := TRUE;
3623 END CASE;
3624 ELSIF(p_bus_object.tca_mandated_flag(i) = 'N' AND
3625 p_bus_object.user_mandated_flag(i) = 'Y' AND
3626 p_bus_object.business_object_code(i) = 'CUST_ACCT_SITE_USE' AND
3627 p_bus_object.child_bo_code(i) IS NULL) THEN
3628 CASE
3629 WHEN(p_bus_object.entity_name(i) = 'RA_CUST_RECEIPT_METHODS') THEN
3630 l_pm := TRUE;
3631 WHEN(p_bus_object.entity_name(i) = 'IBY_FNDCPT_PAYER_ASSGN_INSTR_V') THEN
3632 l_bau := TRUE;
3633 END CASE;
3634 ELSIF(p_bus_object.child_bo_code(i) = 'CUST_PROFILE' AND
3635 p_bus_object.business_object_code(i) = 'CUST_ACCT_SITE_USE' AND
3636 p_bus_object.user_mandated_flag(i) = 'Y') THEN
3637 l_cp := TRUE;
3638 ELSIF(p_bus_object.entity_name(i) = 'HZ_CUST_PROFILE_AMTS' AND
3639 p_bus_object.business_object_code(i) = 'CUST_PROFILE' AND
3640 p_bus_object.user_mandated_flag(i) = 'Y') THEN
3641 l_cpa := TRUE;
3642 END IF;
3643 END LOOP;
3644
3645 IF(l_cac) THEN
3646 get_bus_obj_struct(
3647 p_bus_object_code => 'CUST_ACCT_CONTACT',
3648 x_bus_object => l_cac_bus_object
3649 );
3650 END IF;
3651
3652 FOR i IN 1..p_cas_objs.COUNT LOOP
3653 IF(l_cac) THEN
3654 l_valid_obj := is_cac_bo_comp(
3655 p_cac_objs => p_cas_objs(i).cust_acct_contact_objs,
3656 p_bus_object => l_cac_bus_object
3657 );
3658 IF NOT(l_valid_obj) THEN
3659 RETURN FALSE;
3660 END IF;
3661 END IF;
3662
3663 IF(l_casu AND
3664 (p_cas_objs(i).cust_acct_site_use_objs IS NULL OR
3665 p_cas_objs(i).cust_acct_site_use_objs.COUNT < 1)) THEN
3666 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3667 fnd_message.set_token('OBJECT' ,'CUST_ACCT_SITE_USE');
3668 fnd_msg_pub.add;
3669 RETURN FALSE;
3670
3671 FOR j IN 1..p_cas_objs(i).cust_acct_site_use_objs.COUNT LOOP
3672 IF(l_cp AND p_cas_objs(i).cust_acct_site_use_objs(j).site_use_profile_obj IS NULL) THEN
3673 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3674 fnd_message.set_token('OBJECT' ,'SITE_USE_PROFILE');
3675 fnd_msg_pub.add;
3676 RETURN FALSE;
3677 END IF;
3678 IF(l_cpa AND
3679 (p_cas_objs(i).cust_acct_site_use_objs(j).site_use_profile_obj.cust_profile_amt_objs IS NULL OR
3680 p_cas_objs(i).cust_acct_site_use_objs(j).site_use_profile_obj.cust_profile_amt_objs.COUNT < 1)) THEN
3681 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3682 fnd_message.set_token('OBJECT' ,'SITE_USE_PROFILE_AMOUNT');
3683 fnd_msg_pub.add;
3684 RETURN FALSE;
3685 END IF;
3686 -- for bank account use and payment method
3687 IF(l_bau AND
3688 (p_cas_objs(i).cust_acct_site_use_objs(j).bank_acct_use_objs IS NULL OR
3689 p_cas_objs(i).cust_acct_site_use_objs(j).bank_acct_use_objs.COUNT < 1)) THEN
3690 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3691 fnd_message.set_token('OBJECT' ,'BANK_ACCOUNT');
3692 fnd_msg_pub.add;
3693 RETURN FALSE;
3694 END IF;
3695 IF(l_pm AND
3696 (p_cas_objs(i).cust_acct_site_use_objs(j).payment_method_obj IS NULL)) THEN
3697 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3698 fnd_message.set_token('OBJECT' ,'PAYMENT_METHOD');
3699 fnd_msg_pub.add;
3700 RETURN FALSE;
3701 END IF;
3702 END LOOP;
3703 END IF;
3704 END LOOP;
3705
3706 RETURN TRUE;
3707 END is_cas_bo_comp;
3708
3709 -- FUNCTION is_ca_bo_comp
3710 --
3711 -- DESCRIPTION
3712 -- Return true if customer account object is complete. Otherwise, return false.
3713 --
3714 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3715 --
3716 -- ARGUMENTS
3717 -- IN:
3718 -- p_ca_objs List of customer account business objects.
3719 -- p_bus_object Business object structure for customer account.
3720 -- NOTES
3721 --
3722 -- MODIFICATION HISTORY
3723 --
3724 -- 13-Jul-2005 Arnold Ng o Created.
3725
3726 FUNCTION is_ca_bo_comp(
3727 p_ca_objs IN HZ_CUST_ACCT_BO_TBL,
3728 p_bus_object IN COMPLETENESS_REC_TYPE
3729 ) RETURN BOOLEAN IS
3730 l_valid_obj BOOLEAN;
3731 l_carel BOOLEAN;
3732 l_cas BOOLEAN;
3733 l_cac BOOLEAN;
3734 l_cp BOOLEAN;
3735 l_cpa BOOLEAN;
3736 l_bau BOOLEAN;
3737 l_pm BOOLEAN;
3738 l_cas_bus_object COMPLETENESS_REC_TYPE;
3739 l_cac_bus_object COMPLETENESS_REC_TYPE;
3740 BEGIN
3741 l_carel := FALSE;
3742 l_cas := FALSE;
3743 l_cac := FALSE;
3744 l_cp := FALSE;
3745 l_cpa := FALSE;
3746 l_bau := FALSE;
3747 l_pm := FALSE;
3748
3749 IF(p_ca_objs IS NULL OR p_ca_objs.COUNT < 1) THEN
3750 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_OBJ');
3751 fnd_message.set_token('OBJECT' ,'CUST_ACCT');
3752 fnd_msg_pub.add;
3753 RETURN FALSE;
3754 END IF;
3755
3756 FOR i IN 1..p_bus_object.business_object_code.COUNT LOOP
3757 -- get all entities of cust acct, for cust acct, the possible entites are
3758 -- HZ_BANK_ACCOUNT_USE, HZ_PAYMENT_METHOD, HZ_CUST_ACCT_RELATE
3759 IF(p_bus_object.tca_mandated_flag(i) = 'N' AND
3760 p_bus_object.user_mandated_flag(i) = 'Y' AND
3761 p_bus_object.business_object_code(i) = 'CUST_ACCT' AND
3762 p_bus_object.child_bo_code(i) IS NULL) THEN
3763 CASE
3764 WHEN(p_bus_object.entity_name(i) = 'HZ_CUST_ACCT_RELATE_ALL') THEN
3765 l_carel := TRUE;
3766 WHEN(p_bus_object.entity_name(i) = 'RA_CUST_RECEIPT_METHODS') THEN
3767 l_pm := TRUE;
3768 WHEN(p_bus_object.entity_name(i) = 'IBY_FNDCPT_PAYER_ASSGN_INSTR_V') THEN
3769 l_bau := TRUE;
3770 END CASE;
3771 -- Get other business object
3772 ELSIF(p_bus_object.child_bo_code(i) IS NOT NULL AND
3773 p_bus_object.business_object_code(i) = 'CUST_ACCT' AND
3774 p_bus_object.user_mandated_flag(i) = 'Y') THEN
3775 CASE
3776 WHEN (p_bus_object.child_bo_code(i) = 'CUST_ACCT_SITE') THEN
3777 l_cas := TRUE;
3778 WHEN (p_bus_object.child_bo_code(i) = 'CUST_ACCT_CONTACT') THEN
3779 l_cac := TRUE;
3780 ELSE
3781 null;
3782 END CASE;
3783 ELSIF(p_bus_object.child_bo_code(i) IS NOT NULL AND
3784 p_bus_object.business_object_code(i) = 'CUST_PROFILE' AND
3785 p_bus_object.user_mandated_flag(i) = 'Y') THEN
3786 IF(p_bus_object.entity_name(i) = 'HZ_CUST_PROFILE_AMTS') THEN
3787 l_cpa := TRUE;
3788 END IF;
3789 END IF;
3790 END LOOP;
3791
3792 -- customer profile is mandatory for customer account
3793 l_cp := TRUE;
3794
3795 IF(l_cas) THEN
3796 get_bus_obj_struct(
3797 p_bus_object_code => 'CUST_ACCT_SITE',
3798 x_bus_object => l_cas_bus_object
3799 );
3800 END IF;
3801
3802 IF(l_cac) THEN
3803 get_bus_obj_struct(
3804 p_bus_object_code => 'CUST_ACCT_CONTACT',
3805 x_bus_object => l_cac_bus_object
3806 );
3807 END IF;
3808
3809 FOR i IN 1..p_ca_objs.COUNT LOOP
3810 IF(l_cas) THEN
3811 l_valid_obj := is_cas_bo_comp(
3812 p_cas_objs => p_ca_objs(i).cust_acct_site_objs,
3813 p_bus_object => l_cas_bus_object
3814 );
3815 IF NOT(l_valid_obj) THEN
3816 RETURN FALSE;
3817 END IF;
3818 END IF;
3819
3820 IF(l_cac) THEN
3821 l_valid_obj := is_cac_bo_comp(
3822 p_cac_objs => p_ca_objs(i).cust_acct_contact_objs,
3823 p_bus_object => l_cac_bus_object
3824 );
3825 IF NOT(l_valid_obj) THEN
3826 RETURN FALSE;
3827 END IF;
3828 END IF;
3829
3830 IF(l_carel AND
3831 (p_ca_objs(i).acct_relate_objs IS NULL OR
3832 p_ca_objs(i).acct_relate_objs.COUNT < 1)) THEN
3833 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3834 fnd_message.set_token('OBJECT' ,'CUSTOMER_ACCOUNT_RELATE');
3835 fnd_msg_pub.add;
3836 RETURN FALSE;
3837 END IF;
3838
3839 -- for bank account use and payment method
3840 IF(l_bau AND
3841 (p_ca_objs(i).bank_acct_use_objs IS NULL OR
3842 p_ca_objs(i).bank_acct_use_objs.COUNT < 1)) THEN
3843 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3844 fnd_message.set_token('OBJECT' ,'BANK_ACCOUNT');
3845 fnd_msg_pub.add;
3846 RETURN FALSE;
3847 END IF;
3848 IF(l_pm AND
3849 (p_ca_objs(i).payment_method_obj IS NULL)) THEN
3850 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3851 fnd_message.set_token('OBJECT' ,'PAYMENT_METHOD');
3852 fnd_msg_pub.add;
3853 RETURN FALSE;
3854 END IF;
3855
3856 IF(l_cp AND p_ca_objs(i).cust_profile_obj IS NULL) THEN
3857 RETURN FALSE;
3858 ELSE
3859 IF(l_cpa AND
3860 (p_ca_objs(i).cust_profile_obj.cust_profile_amt_objs IS NULL OR
3861 p_ca_objs(i).cust_profile_obj.cust_profile_amt_objs.COUNT < 1)) THEN
3862 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3863 fnd_message.set_token('OBJECT' ,'CUSTOMER_PROFILE_AMOUNT');
3864 fnd_msg_pub.add;
3865 RETURN FALSE;
3866 END IF;
3867 END IF;
3868 END LOOP;
3869
3870 RETURN TRUE;
3871 END is_ca_bo_comp;
3872
3873 -- FUNCTION is_pca_bo_comp
3874 --
3875 -- DESCRIPTION
3876 -- Return true if person customer object is complete. Otherwise, return false.
3877 --
3878 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3879 --
3880 -- ARGUMENTS
3881 -- IN:
3882 -- p_person_obj Person business object.
3883 -- p_ca_objs List of customer account objects.
3884 -- NOTES
3885 --
3886 -- MODIFICATION HISTORY
3887 --
3888 -- 13-Jul-2005 Arnold Ng o Created.
3889
3890 FUNCTION is_pca_bo_comp(
3891 p_person_obj IN HZ_PERSON_BO,
3892 p_ca_objs IN HZ_CUST_ACCT_BO_TBL
3893 ) RETURN BOOLEAN IS
3894 l_per_bus_object COMPLETENESS_REC_TYPE;
3895 l_ca_bus_object COMPLETENESS_REC_TYPE;
3896 l_valid_obj BOOLEAN;
3897 BEGIN
3898 IF(p_person_obj IS NULL) THEN
3899 RETURN FALSE;
3900 END IF;
3901 IF(p_ca_objs IS NULL OR p_ca_objs.COUNT < 1) THEN
3902 RETURN FALSE;
3903 END IF;
3904
3905 -- check person object for person cust acct
3906 get_bus_obj_struct(
3907 p_bus_object_code => 'PERSON',
3908 x_bus_object => l_per_bus_object
3909 );
3910 l_valid_obj := is_person_bo_comp(
3911 p_person_obj => p_person_obj,
3912 p_bus_object => l_per_bus_object
3913 );
3914 IF NOT(l_valid_obj) THEN
3915 RETURN FALSE;
3916 END IF;
3917
3918 -- check cust account for person cust acct
3919 get_bus_obj_struct(
3920 p_bus_object_code => 'CUST_ACCT',
3921 x_bus_object => l_ca_bus_object
3922 );
3923 l_valid_obj := is_ca_bo_comp(
3924 p_ca_objs => p_ca_objs,
3925 p_bus_object => l_ca_bus_object
3926 );
3927 IF NOT(l_valid_obj) THEN
3928 RETURN FALSE;
3929 END IF;
3930
3931 RETURN TRUE;
3932 END is_pca_bo_comp;
3933
3934 -- FUNCTION is_oca_bo_comp
3935 --
3936 -- DESCRIPTION
3937 -- Return true if organization customer object is complete.
3938 -- Otherwise, return false.
3939 --
3940 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3941 --
3942 -- ARGUMENTS
3943 -- IN:
3944 -- p_org_obj Organization business object.
3945 -- p_ca_objs List of customer account objects.
3946 -- NOTES
3947 --
3948 -- MODIFICATION HISTORY
3949 --
3950 -- 13-Jul-2005 Arnold Ng o Created.
3951
3952 FUNCTION is_oca_bo_comp(
3953 p_org_obj IN HZ_ORGANIZATION_BO,
3954 p_ca_objs IN HZ_CUST_ACCT_BO_TBL
3955 ) RETURN BOOLEAN IS
3956 l_org_bus_object COMPLETENESS_REC_TYPE;
3957 l_ca_bus_object COMPLETENESS_REC_TYPE;
3958 l_valid_obj BOOLEAN;
3959 BEGIN
3960 IF(p_org_obj IS NULL) THEN
3961 RETURN FALSE;
3962 END IF;
3963 IF(p_ca_objs IS NULL OR p_ca_objs.COUNT < 1) THEN
3964 RETURN FALSE;
3965 END IF;
3966
3967 -- check organization object for org cust acct
3968 get_bus_obj_struct(
3969 p_bus_object_code => 'ORG',
3970 x_bus_object => l_org_bus_object
3971 );
3972 l_valid_obj := is_org_bo_comp(
3973 p_organization_obj => p_org_obj,
3974 p_bus_object => l_org_bus_object
3975 );
3976 IF NOT(l_valid_obj) THEN
3977 RETURN FALSE;
3978 END IF;
3979
3980 -- check cust account for org cust acct
3981 get_bus_obj_struct(
3982 p_bus_object_code => 'CUST_ACCT',
3983 x_bus_object => l_ca_bus_object
3984 );
3985 l_valid_obj := is_ca_bo_comp(
3986 p_ca_objs => p_ca_objs,
3987 p_bus_object => l_ca_bus_object
3988 );
3989 IF NOT(l_valid_obj) THEN
3990 RETURN FALSE;
3991 END IF;
3992
3993 RETURN TRUE;
3994 END is_oca_bo_comp;
3995
3996 -- FUNCTION get_bus_object_struct
3997 --
3998 -- DESCRIPTION
3999 -- Get contact point business object structure.
4000 --
4001 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4002 --
4003 -- ARGUMENTS
4004 -- IN:
4005 -- p_bus_object_code Business object code, such as 'PARTY_SITE',
4006 -- 'ORG_CONTACT'
4007 -- OUT:
4008 -- x_bus_object Business object structure.
4009 -- NOTES
4010 --
4011 -- MODIFICATION HISTORY
4012 --
4013 -- 13-Jul-2005 Arnold Ng o Created.
4014
4015 PROCEDURE get_bus_obj_struct(
4016 p_bus_object_code IN VARCHAR2,
4017 x_bus_object OUT NOCOPY COMPLETENESS_REC_TYPE
4018 ) IS
4019 CURSOR get_bus_obj(l_bus_obj VARCHAR2) IS
4020 SELECT d.business_object_code, d.child_bo_code, d.tca_mandated_flag,
4021 d.user_mandated_flag, d.root_node_flag, d.entity_name
4022 FROM hz_bus_obj_definitions d
4023 start with d.business_object_code = l_bus_obj and d.user_mandated_flag = 'Y'
4024 connect by prior d.child_bo_code = d.business_object_code and d.user_mandated_flag = 'Y'
4025 group by d.business_object_code, d.child_bo_code, d.tca_mandated_flag,
4026 d.user_mandated_flag, d.root_node_flag, d.entity_name;
4027 BEGIN
4028 OPEN get_bus_obj(p_bus_object_code);
4029 FETCH get_bus_obj BULK COLLECT
4030 INTO x_bus_object.business_object_code,
4031 x_bus_object.child_bo_code,
4032 x_bus_object.tca_mandated_flag,
4033 x_bus_object.user_mandated_flag,
4034 x_bus_object.root_node_flag,
4035 x_bus_object.entity_name;
4036 CLOSE get_bus_obj;
4037 END get_bus_obj_struct;
4038
4039 -- FUNCTION get_cp_bus_obj_struct
4040 --
4041 -- DESCRIPTION
4042 -- Get contact point business object structure.
4043 --
4044 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4045 --
4046 -- ARGUMENTS
4047 -- IN:
4048 -- p_phone_code 'PHONE'.
4049 -- p_email_code 'EMAIL'.
4050 -- p_telex_code 'TLX'.
4051 -- p_web_code 'WEB'.
4052 -- p_edi_code 'EDI'.
4053 -- p_eft_code 'EFT'.
4054 -- p_sms_code 'SMS'.
4055 -- OUT:
4056 -- x_bus_object Contact point business object structure.
4057 -- NOTES
4058 --
4059 -- MODIFICATION HISTORY
4060 --
4061 -- 13-Jul-2005 Arnold Ng o Created.
4062
4063 PROCEDURE get_cp_bus_obj_struct(
4064 p_phone_code IN VARCHAR2,
4065 p_email_code IN VARCHAR2,
4066 p_telex_code IN VARCHAR2,
4067 p_web_code IN VARCHAR2,
4068 p_edi_code IN VARCHAR2,
4069 p_eft_code IN VARCHAR2,
4070 p_sms_code IN VARCHAR2,
4071 x_bus_object OUT NOCOPY COMPLETENESS_REC_TYPE
4072 ) IS
4073 CURSOR get_cp_bus_obj(l_phone VARCHAR2, l_email VARCHAR2,
4074 l_telex VARCHAR2, l_web VARCHAR2, l_edi VARCHAR2,
4075 l_eft VARCHAR2, l_sms VARCHAR2) IS
4076 SELECT d.business_object_code, d.child_bo_code, d.tca_mandated_flag,
4077 d.user_mandated_flag, d.root_node_flag, d.entity_name
4078 FROM hz_bus_obj_definitions d
4079 start with d.business_object_code in (l_phone, l_email, l_telex, l_web, l_edi, l_eft, l_sms) and d.user_mandated_flag = 'Y'
4080 connect by prior d.child_bo_code = d.business_object_code
4081 and d.user_mandated_flag = 'Y'
4082 group by d.business_object_code, d.child_bo_code, d.tca_mandated_flag,
4083 d.user_mandated_flag, d.root_node_flag, d.entity_name;
4084 BEGIN
4085 OPEN get_cp_bus_obj(p_phone_code, p_email_code, p_telex_code,
4086 p_web_code, p_edi_code, p_eft_code, p_sms_code);
4087 FETCH get_cp_bus_obj BULK COLLECT
4088 INTO x_bus_object.business_object_code,
4089 x_bus_object.child_bo_code,
4090 x_bus_object.tca_mandated_flag,
4091 x_bus_object.user_mandated_flag,
4092 x_bus_object.root_node_flag,
4093 x_bus_object.entity_name;
4094 CLOSE get_cp_bus_obj;
4095 END get_cp_bus_obj_struct;
4096
4097 -- PRIVATE PROCEDURE get_cp_from_rec
4098 --
4099 -- DESCRIPTION
4100 -- Extract business object structure of contact point.
4101 --
4102 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4103 --
4104 -- ARGUMENTS
4105 -- IN:
4106 -- p_phone_code 'PHONE'.
4107 -- p_email_code 'EMAIL'.
4108 -- p_telex_code 'TLX'.
4109 -- p_web_code 'WEB'.
4110 -- p_edi_code 'EDI'.
4111 -- p_eft_code 'EFT'.
4112 -- p_sms_code 'SMS'.
4113 -- OUT:
4114 -- x_bus_object Business object structure of contact point.
4115 -- NOTES
4116 --
4117 -- MODIFICATION HISTORY
4118 --
4119 -- 13-Jul-2005 Arnold Ng o Created.
4120
4121 PROCEDURE get_cp_from_rec(
4122 p_phone_code IN VARCHAR2,
4123 p_email_code IN VARCHAR2,
4124 p_telex_code IN VARCHAR2,
4125 p_web_code IN VARCHAR2,
4126 p_edi_code IN VARCHAR2,
4127 p_eft_code IN VARCHAR2,
4128 p_sms_code IN VARCHAR2,
4129 p_bus_object IN COMPLETENESS_REC_TYPE,
4130 x_bus_object OUT NOCOPY COMPLETENESS_REC_TYPE
4131 ) IS
4132 l_count NUMBER;
4133 BEGIN
4134 l_count := 0;
4135 x_bus_object.business_object_code := boc_tbl();
4136 x_bus_object.child_bo_code := cbc_tbl();
4137 x_bus_object.tca_mandated_flag := tmf_tbl();
4138 x_bus_object.user_mandated_flag := umf_tbl();
4139 x_bus_object.root_node_flag := rnf_tbl();
4140 x_bus_object.entity_name := ent_tbl();
4141 FOR i IN 1..p_bus_object.business_object_code.COUNT LOOP
4142 IF(p_bus_object.business_object_code(i) = p_phone_code OR
4143 p_bus_object.business_object_code(i) = p_email_code OR
4144 p_bus_object.business_object_code(i) = p_telex_code OR
4145 p_bus_object.business_object_code(i) = p_web_code OR
4146 p_bus_object.business_object_code(i) = p_edi_code OR
4147 p_bus_object.business_object_code(i) = p_eft_code OR
4148 p_bus_object.business_object_code(i) = p_sms_code) THEN
4149 l_count := l_count + 1;
4150 x_bus_object.business_object_code.EXTEND;
4151 x_bus_object.child_bo_code.EXTEND;
4152 x_bus_object.tca_mandated_flag.EXTEND;
4153 x_bus_object.user_mandated_flag.EXTEND;
4154 x_bus_object.root_node_flag.EXTEND;
4155 x_bus_object.entity_name.EXTEND;
4156 x_bus_object.business_object_code(l_count) := p_bus_object.business_object_code(i);
4157 x_bus_object.child_bo_code(l_count) := p_bus_object.child_bo_code(i);
4158 x_bus_object.tca_mandated_flag(l_count) := p_bus_object.tca_mandated_flag(i);
4159 x_bus_object.user_mandated_flag(l_count) := p_bus_object.user_mandated_flag(i);
4160 x_bus_object.root_node_flag(l_count) := p_bus_object.root_node_flag(i);
4161 x_bus_object.entity_name(l_count) := p_bus_object.entity_name(i);
4162 END IF;
4163 END LOOP;
4164 END get_cp_from_rec;
4165
4166 -- PRIVATE PROCEDURE get_ps_from_rec
4167 --
4168 -- DESCRIPTION
4169 -- Extract business object structure of party site.
4170 --
4171 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4172 --
4173 -- ARGUMENTS
4174 -- IN:
4175 -- p_bus_object Business object structure.
4176 -- OUT:
4177 -- x_bus_object Business object structure of party site.
4178 -- NOTES
4179 --
4180 -- MODIFICATION HISTORY
4181 --
4182 -- 13-Jul-2005 Arnold Ng o Created.
4183
4184 PROCEDURE get_ps_from_rec(
4185 p_bus_object IN COMPLETENESS_REC_TYPE,
4186 x_bus_object OUT NOCOPY COMPLETENESS_REC_TYPE
4187 ) IS
4188 l_count NUMBER;
4189 l_phone VARCHAR2(30);
4190 l_telex VARCHAR2(30);
4191 l_email VARCHAR2(30);
4192 l_web VARCHAR2(30);
4193 l_edi VARCHAR2(30);
4194 l_eft VARCHAR2(30);
4195 l_sms VARCHAR2(30);
4196 BEGIN
4197 l_count := 0;
4198 x_bus_object.business_object_code := boc_tbl();
4199 x_bus_object.child_bo_code := cbc_tbl();
4200 x_bus_object.tca_mandated_flag := tmf_tbl();
4201 x_bus_object.user_mandated_flag := umf_tbl();
4202 x_bus_object.root_node_flag := rnf_tbl();
4203 x_bus_object.entity_name := ent_tbl();
4204 -- find all rows related to PARTY_SITE
4205 FOR i IN 1..p_bus_object.business_object_code.COUNT LOOP
4206 IF(p_bus_object.business_object_code(i) = 'PARTY_SITE') THEN
4207 l_count := l_count + 1;
4208 x_bus_object.business_object_code.EXTEND;
4209 x_bus_object.child_bo_code.EXTEND;
4210 x_bus_object.tca_mandated_flag.EXTEND;
4211 x_bus_object.user_mandated_flag.EXTEND;
4212 x_bus_object.root_node_flag.EXTEND;
4213 x_bus_object.entity_name.EXTEND;
4214 x_bus_object.business_object_code(l_count) := p_bus_object.business_object_code(i);
4215 x_bus_object.child_bo_code(l_count) := p_bus_object.child_bo_code(i);
4216 x_bus_object.tca_mandated_flag(l_count) := p_bus_object.tca_mandated_flag(i);
4217 x_bus_object.user_mandated_flag(l_count) := p_bus_object.user_mandated_flag(i);
4218 x_bus_object.root_node_flag(l_count) := p_bus_object.root_node_flag(i);
4219 x_bus_object.entity_name(l_count) := p_bus_object.entity_name(i);
4220 END IF;
4221 END LOOP;
4222
4223 -- find all PARTY_SITE rows with child_bo_code, all rows must be CONTACT POINT
4224 FOR i IN 1..x_bus_object.business_object_code.COUNT LOOP
4225 IF(x_bus_object.child_bo_code(i) IS NOT NULL) THEN
4226 CASE
4227 WHEN x_bus_object.child_bo_code(i) = 'PHONE' THEN
4228 l_phone := x_bus_object.child_bo_code(i);
4229 WHEN x_bus_object.child_bo_code(i) = 'TLX' THEN
4230 l_telex := x_bus_object.child_bo_code(i);
4231 WHEN x_bus_object.child_bo_code(i) = 'EMAIL' THEN
4232 l_email := x_bus_object.child_bo_code(i);
4233 WHEN x_bus_object.child_bo_code(i) = 'WEB' THEN
4234 l_web := x_bus_object.child_bo_code(i);
4235 WHEN x_bus_object.child_bo_code(i) = 'EDI' THEN
4236 l_edi := x_bus_object.child_bo_code(i);
4237 WHEN x_bus_object.child_bo_code(i) = 'EFT' THEN
4238 l_eft := x_bus_object.child_bo_code(i);
4239 WHEN x_bus_object.child_bo_code(i) = 'SMS' THEN
4240 l_sms := x_bus_object.child_bo_code(i);
4241 ELSE -- for 'LOCATION'
4242 NULL;
4243 END CASE;
4244 END IF;
4245 END LOOP;
4246
4247 -- find all contact point rows for PARTY_SITE
4248 FOR i IN 1..p_bus_object.business_object_code.COUNT LOOP
4249 IF(p_bus_object.business_object_code(i) = l_phone OR
4250 p_bus_object.business_object_code(i) = l_telex OR
4251 p_bus_object.business_object_code(i) = l_email OR
4252 p_bus_object.business_object_code(i) = l_web OR
4253 p_bus_object.business_object_code(i) = l_edi OR
4254 p_bus_object.business_object_code(i) = l_eft OR
4255 p_bus_object.business_object_code(i) = l_sms) THEN
4256 l_count := l_count + 1;
4257 x_bus_object.business_object_code.EXTEND;
4258 x_bus_object.child_bo_code.EXTEND;
4259 x_bus_object.tca_mandated_flag.EXTEND;
4260 x_bus_object.user_mandated_flag.EXTEND;
4261 x_bus_object.root_node_flag.EXTEND;
4262 x_bus_object.entity_name.EXTEND;
4263 x_bus_object.business_object_code(l_count) := p_bus_object.business_object_code(i);
4264 x_bus_object.child_bo_code(l_count) := p_bus_object.child_bo_code(i);
4265 x_bus_object.tca_mandated_flag(l_count) := p_bus_object.tca_mandated_flag(i);
4266 x_bus_object.user_mandated_flag(l_count) := p_bus_object.user_mandated_flag(i);
4267 x_bus_object.root_node_flag(l_count) := p_bus_object.root_node_flag(i);
4268 x_bus_object.entity_name(l_count) := p_bus_object.entity_name(i);
4269 END IF;
4270 END LOOP;
4271 END get_ps_from_rec;
4272
4273 -- FUNCTION get_id_from_ososr
4274 --
4275 -- DESCRIPTION
4276 -- Get TCA Id based on original system and original system reference.
4277 --
4278 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4279 --
4280 -- ARGUMENTS
4281 -- IN:
4282 -- p_os Original system
4283 -- p_osr Original system reference
4284 -- p_owner_table_name Owner table name
4285 -- NOTES
4286 --
4287 -- MODIFICATION HISTORY
4288 --
4289 -- 13-Jul-2005 Arnold Ng o Created.
4290
4291 FUNCTION get_id_from_ososr(
4292 p_os IN VARCHAR2,
4293 p_osr IN VARCHAR2,
4294 p_owner_table_name IN VARCHAR2
4295 ) RETURN NUMBER IS
4296 l_count NUMBER;
4297 l_owner_table_id NUMBER;
4298 l_return_status VARCHAR2(30);
4299 BEGIN
4300 l_count := HZ_MOSR_VALIDATE_PKG.get_orig_system_ref_count(
4301 p_orig_system => p_os,
4302 p_orig_system_reference => p_osr,
4303 p_owner_table_name => p_owner_table_name);
4304
4305 IF(l_count = 1) THEN
4306 -- Get owner_table_id
4307 HZ_ORIG_SYSTEM_REF_PUB.get_owner_table_id(
4308 p_orig_system => p_os,
4309 p_orig_system_reference => p_osr,
4310 p_owner_table_name => p_owner_table_name,
4311 x_owner_table_id => l_owner_table_id,
4312 x_return_status => l_return_status);
4313 END IF;
4314
4315 RETURN l_owner_table_id;
4316 END get_id_from_ososr;
4317
4318 -- FUNCTION is_cas_v2_bo_comp
4319 --
4320 -- DESCRIPTION
4321 -- Return true if customer account site object is complete.
4322 -- Otherwise, return false.
4323 --
4324 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4325 --
4326 -- ARGUMENTS
4327 -- IN:
4328 -- p_cas_v2_objs List of customer account site business objects.
4329 -- p_bus_object Business object structure for customer account site.
4330 -- NOTES
4331 --
4332 -- MODIFICATION HISTORY
4333 --
4334 -- 1-FEB-2008 vsegu o Created.
4335
4336 FUNCTION is_cas_v2_bo_comp(
4337 p_cas_v2_objs IN HZ_CUST_ACCT_SITE_V2_BO_TBL,
4338 p_bus_object IN COMPLETENESS_REC_TYPE
4339 ) RETURN BOOLEAN IS
4340 l_valid_obj BOOLEAN;
4341 l_casu BOOLEAN;
4342 l_cp BOOLEAN;
4343 l_cac BOOLEAN;
4344 l_cpa BOOLEAN;
4345 l_bau BOOLEAN;
4346 l_pm BOOLEAN;
4347 l_cac_bus_object COMPLETENESS_REC_TYPE;
4348 BEGIN
4349 l_casu := FALSE;
4350 l_cp := FALSE;
4351 l_cpa := FALSE;
4352 l_cac := FALSE;
4353 l_bau := FALSE;
4354 l_pm := FALSE;
4355
4356 IF(p_cas_v2_objs IS NULL OR p_cas_v2_objs.COUNT < 1) THEN
4357 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_OBJ');
4358 fnd_message.set_token('OBJECT' ,'CUST_ACCT_SITE');
4359 fnd_msg_pub.add;
4360 RETURN FALSE;
4361 END IF;
4362
4363 FOR i IN 1..p_bus_object.business_object_code.COUNT LOOP
4364 -- no entities of cust acct site
4365 IF(p_bus_object.child_bo_code(i) IS NOT NULL AND
4366 p_bus_object.business_object_code(i) = 'CUST_ACCT_SITE' AND
4367 p_bus_object.user_mandated_flag(i) = 'Y') THEN
4368 CASE
4369 WHEN(p_bus_object.child_bo_code(i) = 'CUST_ACCT_SITE_USE' AND
4370 p_bus_object.business_object_code(i) = 'CUST_ACCT_SITE') THEN
4371 l_casu := TRUE;
4372 WHEN(p_bus_object.child_bo_code(i) = 'CUST_ACCT_CONTACT' AND
4373 p_bus_object.business_object_code(i) = 'CUST_ACCT_SITE') THEN
4374 l_cac := TRUE;
4375 END CASE;
4376 ELSIF(p_bus_object.tca_mandated_flag(i) = 'N' AND
4377 p_bus_object.user_mandated_flag(i) = 'Y' AND
4378 p_bus_object.business_object_code(i) = 'CUST_ACCT_SITE_USE' AND
4379 p_bus_object.child_bo_code(i) IS NULL) THEN
4380 CASE
4381 WHEN(p_bus_object.entity_name(i) = 'RA_CUST_RECEIPT_METHODS') THEN
4382 l_pm := TRUE;
4383 WHEN(p_bus_object.entity_name(i) = 'IBY_FNDCPT_PAYER_ASSGN_INSTR_V') THEN
4384 l_bau := TRUE;
4385 END CASE;
4386 ELSIF(p_bus_object.child_bo_code(i) = 'CUST_PROFILE' AND
4387 p_bus_object.business_object_code(i) = 'CUST_ACCT_SITE_USE' AND
4388 p_bus_object.user_mandated_flag(i) = 'Y') THEN
4389 l_cp := TRUE;
4390 ELSIF(p_bus_object.entity_name(i) = 'HZ_CUST_PROFILE_AMTS' AND
4391 p_bus_object.business_object_code(i) = 'CUST_PROFILE' AND
4392 p_bus_object.user_mandated_flag(i) = 'Y') THEN
4393 l_cpa := TRUE;
4394 END IF;
4395 END LOOP;
4396
4397 IF(l_cac) THEN
4398 get_bus_obj_struct(
4399 p_bus_object_code => 'CUST_ACCT_CONTACT',
4400 x_bus_object => l_cac_bus_object
4401 );
4402 END IF;
4403
4404 FOR i IN 1..p_cas_v2_objs.COUNT LOOP
4405 IF(l_cac) THEN
4406 l_valid_obj := is_cac_bo_comp(
4407 p_cac_objs => p_cas_v2_objs(i).cust_acct_contact_objs,
4408 p_bus_object => l_cac_bus_object
4409 );
4410 IF NOT(l_valid_obj) THEN
4411 RETURN FALSE;
4412 END IF;
4413 END IF;
4414
4415 IF(l_casu AND
4416 (p_cas_v2_objs(i).cust_acct_site_use_objs IS NULL OR
4417 p_cas_v2_objs(i).cust_acct_site_use_objs.COUNT < 1)) THEN
4418 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
4419 fnd_message.set_token('OBJECT' ,'CUST_ACCT_SITE_USE');
4420 fnd_msg_pub.add;
4421 RETURN FALSE;
4422
4423 FOR j IN 1..p_cas_v2_objs(i).cust_acct_site_use_objs.COUNT LOOP
4424 IF(l_cp AND p_cas_v2_objs(i).cust_acct_site_use_objs(j).site_use_profile_obj IS NULL) THEN
4425 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
4426 fnd_message.set_token('OBJECT' ,'SITE_USE_PROFILE');
4427 fnd_msg_pub.add;
4428 RETURN FALSE;
4429 END IF;
4430 IF(l_cpa AND
4431 (p_cas_v2_objs(i).cust_acct_site_use_objs(j).site_use_profile_obj.cust_profile_amt_objs IS NULL OR
4432 p_cas_v2_objs(i).cust_acct_site_use_objs(j).site_use_profile_obj.cust_profile_amt_objs.COUNT < 1)) THEN
4433 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
4434 fnd_message.set_token('OBJECT' ,'SITE_USE_PROFILE_AMOUNT');
4435 fnd_msg_pub.add;
4436 RETURN FALSE;
4437 END IF;
4438 -- for bank account use and payment method
4439 IF(l_bau AND
4440 (p_cas_v2_objs(i).cust_acct_site_use_objs(j).bank_acct_use_objs IS NULL OR
4441 p_cas_v2_objs(i).cust_acct_site_use_objs(j).bank_acct_use_objs.COUNT < 1)) THEN
4442 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
4443 fnd_message.set_token('OBJECT' ,'BANK_ACCOUNT');
4444 fnd_msg_pub.add;
4445 RETURN FALSE;
4446 END IF;
4447 IF(l_pm AND
4448 (p_cas_v2_objs(i).cust_acct_site_use_objs(j).payment_method_objs IS NULL OR
4449 p_cas_v2_objs(i).cust_acct_site_use_objs(j).payment_method_objs.COUNT < 1)) THEN
4450 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
4451 fnd_message.set_token('OBJECT' ,'PAYMENT_METHOD');
4452 fnd_msg_pub.add;
4453 RETURN FALSE;
4454 END IF;
4455 END LOOP;
4456 END IF;
4457 END LOOP;
4458
4459 RETURN TRUE;
4460 END is_cas_v2_bo_comp;
4461
4462 -- FUNCTION is_ca_v2_bo_comp
4463 --
4464 -- DESCRIPTION
4465 -- Return true if customer account object is complete. Otherwise, return false.
4466 --
4467 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4468 --
4469 -- ARGUMENTS
4470 -- IN:
4471 -- p_ca_v2_objs List of customer account business objects.
4472 -- p_bus_object Business object structure for customer account.
4473 -- NOTES
4474 --
4475 -- MODIFICATION HISTORY
4476 --
4477 -- 1-FEB-2008 vsegu o Created.
4478
4479 FUNCTION is_ca_v2_bo_comp(
4480 p_ca_v2_objs IN HZ_CUST_ACCT_V2_BO_TBL,
4481 p_bus_object IN COMPLETENESS_REC_TYPE
4482 ) RETURN BOOLEAN IS
4483 l_valid_obj BOOLEAN;
4484 l_carel BOOLEAN;
4485 l_cas BOOLEAN;
4486 l_cac BOOLEAN;
4487 l_cp BOOLEAN;
4488 l_cpa BOOLEAN;
4489 l_bau BOOLEAN;
4490 l_pm BOOLEAN;
4491 l_cas_bus_object COMPLETENESS_REC_TYPE;
4492 l_cac_bus_object COMPLETENESS_REC_TYPE;
4493 BEGIN
4494 l_carel := FALSE;
4495 l_cas := FALSE;
4496 l_cac := FALSE;
4497 l_cp := FALSE;
4498 l_cpa := FALSE;
4499 l_bau := FALSE;
4500 l_pm := FALSE;
4501
4502 IF(p_ca_v2_objs IS NULL OR p_ca_v2_objs.COUNT < 1) THEN
4503 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_OBJ');
4504 fnd_message.set_token('OBJECT' ,'CUST_ACCT');
4505 fnd_msg_pub.add;
4506 RETURN FALSE;
4507 END IF;
4508
4509 FOR i IN 1..p_bus_object.business_object_code.COUNT LOOP
4510 -- get all entities of cust acct, for cust acct, the possible entites are
4511 -- HZ_BANK_ACCOUNT_USE, HZ_PAYMENT_METHOD, HZ_CUST_ACCT_RELATE
4512 IF(p_bus_object.tca_mandated_flag(i) = 'N' AND
4513 p_bus_object.user_mandated_flag(i) = 'Y' AND
4514 p_bus_object.business_object_code(i) = 'CUST_ACCT' AND
4515 p_bus_object.child_bo_code(i) IS NULL) THEN
4516 CASE
4517 WHEN(p_bus_object.entity_name(i) = 'HZ_CUST_ACCT_RELATE_ALL') THEN
4518 l_carel := TRUE;
4519 WHEN(p_bus_object.entity_name(i) = 'RA_CUST_RECEIPT_METHODS') THEN
4520 l_pm := TRUE;
4521 WHEN(p_bus_object.entity_name(i) = 'IBY_FNDCPT_PAYER_ASSGN_INSTR_V') THEN
4522 l_bau := TRUE;
4523 END CASE;
4524 -- Get other business object
4525 ELSIF(p_bus_object.child_bo_code(i) IS NOT NULL AND
4526 p_bus_object.business_object_code(i) = 'CUST_ACCT' AND
4527 p_bus_object.user_mandated_flag(i) = 'Y') THEN
4528 CASE
4529 WHEN (p_bus_object.child_bo_code(i) = 'CUST_ACCT_SITE') THEN
4530 l_cas := TRUE;
4531 WHEN (p_bus_object.child_bo_code(i) = 'CUST_ACCT_CONTACT') THEN
4532 l_cac := TRUE;
4533 ELSE
4534 null;
4535 END CASE;
4536 ELSIF(p_bus_object.child_bo_code(i) IS NOT NULL AND
4537 p_bus_object.business_object_code(i) = 'CUST_PROFILE' AND
4538 p_bus_object.user_mandated_flag(i) = 'Y') THEN
4539 IF(p_bus_object.entity_name(i) = 'HZ_CUST_PROFILE_AMTS') THEN
4540 l_cpa := TRUE;
4541 END IF;
4542 END IF;
4543 END LOOP;
4544
4545 -- customer profile is mandatory for customer account
4546 l_cp := TRUE;
4547
4548 IF(l_cas) THEN
4549 get_bus_obj_struct(
4550 p_bus_object_code => 'CUST_ACCT_SITE',
4551 x_bus_object => l_cas_bus_object
4552 );
4553 END IF;
4554
4555 IF(l_cac) THEN
4556 get_bus_obj_struct(
4557 p_bus_object_code => 'CUST_ACCT_CONTACT',
4558 x_bus_object => l_cac_bus_object
4559 );
4560 END IF;
4561
4562 FOR i IN 1..p_ca_v2_objs.COUNT LOOP
4563 IF(l_cas) THEN
4564 l_valid_obj := is_cas_v2_bo_comp(
4565 p_cas_v2_objs => p_ca_v2_objs(i).cust_acct_site_objs,
4566 p_bus_object => l_cas_bus_object
4567 );
4568 IF NOT(l_valid_obj) THEN
4569 RETURN FALSE;
4570 END IF;
4571 END IF;
4572
4573 IF(l_cac) THEN
4574 l_valid_obj := is_cac_bo_comp(
4575 p_cac_objs => p_ca_v2_objs(i).cust_acct_contact_objs,
4576 p_bus_object => l_cac_bus_object
4577 );
4578 IF NOT(l_valid_obj) THEN
4579 RETURN FALSE;
4580 END IF;
4581 END IF;
4582
4583 IF(l_carel AND
4584 (p_ca_v2_objs(i).acct_relate_objs IS NULL OR
4585 p_ca_v2_objs(i).acct_relate_objs.COUNT < 1)) THEN
4586 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
4587 fnd_message.set_token('OBJECT' ,'CUSTOMER_ACCOUNT_RELATE');
4588 fnd_msg_pub.add;
4589 RETURN FALSE;
4590 END IF;
4591
4592 -- for bank account use and payment method
4593 IF(l_bau AND
4594 (p_ca_v2_objs(i).bank_acct_use_objs IS NULL OR
4595 p_ca_v2_objs(i).bank_acct_use_objs.COUNT < 1)) THEN
4596 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
4597 fnd_message.set_token('OBJECT' ,'BANK_ACCOUNT');
4598 fnd_msg_pub.add;
4599 RETURN FALSE;
4600 END IF;
4601 IF(l_pm AND
4602 (p_ca_v2_objs(i).payment_method_objs IS NULL OR
4603 p_ca_v2_objs(i).payment_method_objs.COUNT < 1)) THEN
4604 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
4605 fnd_message.set_token('OBJECT' ,'PAYMENT_METHOD');
4606 fnd_msg_pub.add;
4607 RETURN FALSE;
4608 END IF;
4609
4610 IF(l_cp AND p_ca_v2_objs(i).cust_profile_obj IS NULL) THEN
4611 RETURN FALSE;
4612 ELSE
4613 IF(l_cpa AND
4614 (p_ca_v2_objs(i).cust_profile_obj.cust_profile_amt_objs IS NULL OR
4615 p_ca_v2_objs(i).cust_profile_obj.cust_profile_amt_objs.COUNT < 1)) THEN
4616 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
4617 fnd_message.set_token('OBJECT' ,'CUSTOMER_PROFILE_AMOUNT');
4618 fnd_msg_pub.add;
4619 RETURN FALSE;
4620 END IF;
4621 END IF;
4622 END LOOP;
4623
4624 RETURN TRUE;
4625 END is_ca_v2_bo_comp;
4626
4627 -- FUNCTION is_pca_v2_bo_comp
4628 --
4629 -- DESCRIPTION
4630 -- Return true if person customer object is complete. Otherwise, return false.
4631 --
4632 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4633 --
4634 -- ARGUMENTS
4635 -- IN:
4636 -- p_person_obj Person business object.
4637 -- p_ca_v2_objs List of customer account objects.
4638 -- NOTES
4639 --
4640 -- MODIFICATION HISTORY
4641 --
4642 -- 1-FEB-2008 vsegu o Created.
4643
4644 FUNCTION is_pca_v2_bo_comp(
4645 p_person_obj IN HZ_PERSON_BO,
4646 p_ca_v2_objs IN HZ_CUST_ACCT_V2_BO_TBL
4647 ) RETURN BOOLEAN IS
4648 l_per_bus_object COMPLETENESS_REC_TYPE;
4649 l_ca_bus_object COMPLETENESS_REC_TYPE;
4650 l_valid_obj BOOLEAN;
4651 BEGIN
4652 IF(p_person_obj IS NULL) THEN
4653 RETURN FALSE;
4654 END IF;
4655 IF(p_ca_v2_objs IS NULL OR p_ca_v2_objs.COUNT < 1) THEN
4656 RETURN FALSE;
4657 END IF;
4658
4659 -- check person object for person cust acct
4660 get_bus_obj_struct(
4661 p_bus_object_code => 'PERSON',
4662 x_bus_object => l_per_bus_object
4663 );
4664 l_valid_obj := is_person_bo_comp(
4665 p_person_obj => p_person_obj,
4666 p_bus_object => l_per_bus_object
4667 );
4668 IF NOT(l_valid_obj) THEN
4669 RETURN FALSE;
4670 END IF;
4671
4672 -- check cust account for person cust acct
4673 get_bus_obj_struct(
4674 p_bus_object_code => 'CUST_ACCT',
4675 x_bus_object => l_ca_bus_object
4676 );
4677 l_valid_obj := is_ca_v2_bo_comp(
4678 p_ca_v2_objs => p_ca_v2_objs,
4679 p_bus_object => l_ca_bus_object
4680 );
4681 IF NOT(l_valid_obj) THEN
4682 RETURN FALSE;
4683 END IF;
4684
4685 RETURN TRUE;
4686 END is_pca_v2_bo_comp;
4687
4688 -- FUNCTION is_oca_v2_bo_comp
4689 --
4690 -- DESCRIPTION
4691 -- Return true if organization customer object is complete.
4692 -- Otherwise, return false.
4693 --
4694 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4695 --
4696 -- ARGUMENTS
4697 -- IN:
4698 -- p_org_obj Organization business object.
4699 -- p_ca_v2_objs List of customer account objects.
4700 -- NOTES
4701 --
4702 -- MODIFICATION HISTORY
4703 --
4704 -- 1-FEB-2008 vsegu o Created.
4705
4706 FUNCTION is_oca_v2_bo_comp(
4707 p_org_obj IN HZ_ORGANIZATION_BO,
4708 p_ca_v2_objs IN HZ_CUST_ACCT_V2_BO_TBL
4709 ) RETURN BOOLEAN IS
4710 l_org_bus_object COMPLETENESS_REC_TYPE;
4711 l_ca_bus_object COMPLETENESS_REC_TYPE;
4712 l_valid_obj BOOLEAN;
4713 BEGIN
4714 IF(p_org_obj IS NULL) THEN
4715 RETURN FALSE;
4716 END IF;
4717 IF(p_ca_v2_objs IS NULL OR p_ca_v2_objs.COUNT < 1) THEN
4718 RETURN FALSE;
4719 END IF;
4720
4721 -- check organization object for org cust acct
4722 get_bus_obj_struct(
4723 p_bus_object_code => 'ORG',
4724 x_bus_object => l_org_bus_object
4725 );
4726 l_valid_obj := is_org_bo_comp(
4727 p_organization_obj => p_org_obj,
4728 p_bus_object => l_org_bus_object
4729 );
4730 IF NOT(l_valid_obj) THEN
4731 RETURN FALSE;
4732 END IF;
4733
4734 -- check cust account for org cust acct
4735 get_bus_obj_struct(
4736 p_bus_object_code => 'CUST_ACCT',
4737 x_bus_object => l_ca_bus_object
4738 );
4739 l_valid_obj := is_ca_v2_bo_comp(
4740 p_ca_v2_objs => p_ca_v2_objs,
4741 p_bus_object => l_ca_bus_object
4742 );
4743 IF NOT(l_valid_obj) THEN
4744 RETURN FALSE;
4745 END IF;
4746
4747 RETURN TRUE;
4748 END is_oca_v2_bo_comp;
4749
4750 END HZ_REGISTRY_VALIDATE_BO_PVT;
[Home] [Help]