1 PACKAGE BODY HZ_REGISTRY_VALIDATE_BO_PVT AS
2 /*$Header: ARHBRGVB.pls 120.26 2008/02/06 10:08:56 vsegu 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 HZ_PARTY_SITE_USES
1123 WHERE party_site_id = l_party_site_id
1124 AND site_use_type = l_site_use_type
1125 AND status in ('A','I')
1126 AND rownum = 1;
1127
1128 l_ps_id NUMBER;
1129 BEGIN
1130 IF(px_party_site_use_id IS NULL) THEN
1131 OPEN is_party_site_use_exist(p_party_site_id, p_site_use_type);
1132 FETCH is_party_site_use_exist INTO x_object_version_number, px_party_site_use_id;
1133 CLOSE is_party_site_use_exist;
1134 ELSE
1135 OPEN is_party_site_use_id_exist(px_party_site_use_id);
1136 FETCH is_party_site_use_id_exist INTO x_object_version_number, l_ps_id;
1137 CLOSE is_party_site_use_id_exist;
1138 IF(l_ps_id <> p_party_site_id) OR (l_ps_id IS NULL AND p_party_site_id IS NOT NULL) THEN
1139 -- return -1 to indicate that the combination of parent and object id do not match
1140 x_object_version_number := -1;
1141 END IF;
1142 END IF;
1143 END check_party_site_use_op;
1144
1145 -- PROCEDURE check_relationship_op
1146 --
1147 -- DESCRIPTION
1148 -- Check the operation of relationship based on pass in parameter.
1149 --
1150 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1151 --
1152 -- ARGUMENTS
1153 -- IN:
1154 -- p_subject_id Subject Id.
1155 -- p_object_id Object Id.
1156 -- p_relationship_type Relationship type.
1157 -- p_relationship_code Relationship code.
1158 -- IN/OUT:
1159 -- px_relationship_id Relationship Id.
1160 -- OUT:
1161 -- x_object_version_number Object version number of relationship.
1162 -- x_party_object_version_number Object version number of relationship party.
1163 -- NOTES
1164 --
1165 -- MODIFICATION HISTORY
1166 --
1167 -- 13-Jul-2005 Arnold Ng o Created.
1168
1169 PROCEDURE check_relationship_op(
1170 p_subject_id IN NUMBER,
1171 p_object_id IN NUMBER,
1172 px_relationship_id IN OUT NOCOPY NUMBER,
1173 p_relationship_type IN VARCHAR2,
1174 p_relationship_code IN VARCHAR2,
1175 x_object_version_number OUT NOCOPY NUMBER,
1176 x_party_obj_version_number OUT NOCOPY NUMBER
1177 ) IS
1178 CURSOR is_relationship_id_exist(l_subject_id NUMBER, l_object_id NUMBER, l_rel_id NUMBER) IS
1179 SELECT rel.object_version_number, p.object_version_number
1180 FROM HZ_RELATIONSHIPS rel, HZ_PARTIES p
1181 WHERE rel.subject_id = l_subject_id
1182 AND rel.object_id = l_object_id
1183 AND rel.relationship_id = l_rel_id
1184 AND rel.party_id = p.party_id
1185 AND rel.status in ('A','I')
1186 AND rownum = 1;
1187
1188 CURSOR is_relationship_exist(l_subject_id NUMBER, l_object_id NUMBER,
1189 l_relationship_type VARCHAR2, l_relationship_code VARCHAR2)IS
1190 SELECT rel.object_version_number, p.object_version_number, rel.relationship_id
1191 FROM HZ_RELATIONSHIPS rel, HZ_PARTIES p
1192 WHERE rel.subject_id = l_subject_id
1193 AND rel.object_id = l_object_id
1194 AND rel.relationship_type = l_relationship_type
1195 AND rel.relationship_code = l_relationship_code
1196 AND sysdate between rel.start_date and nvl(rel.end_date, sysdate)
1197 AND rel.party_id = p.party_id
1198 AND rel.status in ('A','I')
1199 AND rownum = 1;
1200 BEGIN
1201 IF(px_relationship_id IS NULL) THEN
1202 OPEN is_relationship_exist(p_subject_id, p_object_id, p_relationship_type, p_relationship_code);
1203 FETCH is_relationship_exist INTO x_object_version_number, x_party_obj_version_number, px_relationship_id;
1204 CLOSE is_relationship_exist;
1205 ELSE
1206 OPEN is_relationship_id_exist(p_subject_id, p_object_id, px_relationship_id);
1207 FETCH is_relationship_id_exist INTO x_object_version_number, x_party_obj_version_number;
1208 CLOSE is_relationship_id_exist;
1209 END IF;
1210 END check_relationship_op;
1211
1212 -- PROCEDURE check_org_contact_role_op
1213 --
1214 -- DESCRIPTION
1215 -- Check the operation of org contact role based on pass in parameter.
1216 --
1217 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1218 --
1219 -- ARGUMENTS
1220 -- IN:
1221 -- p_org_contact_id Org contact Id.
1222 -- p_role_type Role type.
1223 -- IN/OUT:
1224 -- px_org_contact_role_id Org contact role Id.
1225 -- OUT:
1226 -- x_object_version_number Object version number of org contact role.
1227 -- NOTES
1228 --
1229 -- MODIFICATION HISTORY
1230 --
1231 -- 13-Jul-2005 Arnold Ng o Created.
1232
1233 PROCEDURE check_org_contact_role_op(
1234 p_org_contact_id IN NUMBER,
1235 px_org_contact_role_id IN OUT NOCOPY NUMBER,
1236 p_role_type IN VARCHAR2,
1237 x_object_version_number OUT NOCOPY NUMBER
1238 ) IS
1239 CURSOR is_org_contact_role_id_exist(l_role_id NUMBER)IS
1240 SELECT nvl(object_version_number,1), org_contact_id
1241 FROM HZ_ORG_CONTACT_ROLES
1242 WHERE org_contact_role_id = l_role_id
1243 AND rownum = 1;
1244
1245 CURSOR is_org_contact_role_exist(l_org_contact_id NUMBER, l_role_type VARCHAR2)IS
1246 SELECT nvl(object_version_number,1), org_contact_role_id
1247 FROM HZ_ORG_CONTACT_ROLES
1248 WHERE org_contact_id = l_org_contact_id
1249 AND role_type = l_role_type
1250 AND status in ('A','I')
1251 AND rownum = 1;
1252
1253 l_oc_id NUMBER;
1254 BEGIN
1255 IF(px_org_contact_role_id IS NULL) THEN
1256 OPEN is_org_contact_role_exist(p_org_contact_id, p_role_type);
1257 FETCH is_org_contact_role_exist INTO x_object_version_number, px_org_contact_role_id;
1258 CLOSE is_org_contact_role_exist;
1259 ELSE
1260 OPEN is_org_contact_role_id_exist(px_org_contact_role_id);
1261 FETCH is_org_contact_role_id_exist INTO x_object_version_number, l_oc_id;
1262 CLOSE is_org_contact_role_id_exist;
1263 IF(l_oc_id <> p_org_contact_id) OR (l_oc_id IS NULL AND p_org_contact_id IS NOT NULL) THEN
1264 -- return -1 to indicate that the combination of parent and object id do not match
1265 x_object_version_number := -1;
1266 END IF;
1267 END IF;
1268 END check_org_contact_role_op;
1269
1270 -- PROCEDURE check_certification_op
1271 --
1272 -- DESCRIPTION
1273 -- Check the operation of certification based on pass in parameter.
1274 --
1275 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1276 --
1277 -- ARGUMENTS
1278 -- IN:
1279 -- p_party_id Party Id.
1280 -- p_certification_name Name of certification.
1281 -- IN/OUT:
1282 -- px_certification_id Certification Id.
1283 -- OUT:
1284 -- x_last_update_date Last update date of certification.
1285 -- NOTES
1286 --
1287 -- MODIFICATION HISTORY
1288 --
1289 -- 13-Jul-2005 Arnold Ng o Created.
1290
1291 PROCEDURE check_certification_op(
1292 p_party_id IN NUMBER,
1293 px_certification_id IN OUT NOCOPY NUMBER,
1294 p_certification_name IN VARCHAR2,
1295 x_last_update_date OUT NOCOPY DATE,
1296 x_return_status OUT NOCOPY VARCHAR2
1297 ) IS
1298 CURSOR is_cert_id_exist(l_cert_id NUMBER) IS
1299 SELECT last_update_date, party_id
1300 FROM HZ_CERTIFICATIONS
1301 WHERE certification_id = l_cert_id
1302 AND rownum = 1;
1303
1304 CURSOR is_cert_exist(l_party_id NUMBER, l_cert_name VARCHAR2) IS
1305 SELECT last_update_date, certification_id
1306 FROM HZ_CERTIFICATIONS
1307 WHERE party_id = l_party_id
1308 AND certification_name = l_cert_name
1309 AND status in ('A','I')
1310 AND rownum = 1;
1311
1312 l_party_id NUMBER;
1313 BEGIN
1314 x_return_status := FND_API.G_RET_STS_SUCCESS;
1315 IF(px_certification_id IS NULL) THEN
1316 OPEN is_cert_exist(p_party_id, p_certification_name);
1317 FETCH is_cert_exist INTO x_last_update_date, px_certification_id;
1318 CLOSE is_cert_exist;
1319 ELSE
1320 OPEN is_cert_id_exist(px_certification_id);
1321 FETCH is_cert_id_exist INTO x_last_update_date, l_party_id;
1322 CLOSE is_cert_id_exist;
1323 IF(l_party_id <> p_party_id) OR (l_party_id IS NULL AND p_party_id IS NOT NULL) THEN
1324 -- return -1 to indicate that the combination of parent and object id do not match
1325 x_return_status := FND_API.G_RET_STS_ERROR;
1326 END IF;
1327 END IF;
1328 END check_certification_op;
1329
1330 -- PROCEDURE check_financial_prof_op
1331 --
1332 -- DESCRIPTION
1333 -- Check the operation of financial profile based on pass in parameter.
1334 --
1335 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1336 --
1337 -- ARGUMENTS
1338 -- IN:
1339 -- p_party_id Party Id.
1340 -- p_financial_profile_id Financial profile Id.
1341 -- OUT:
1342 -- x_last_update_date Last update date of financial profile.
1343 -- NOTES
1344 --
1345 -- MODIFICATION HISTORY
1346 --
1347 -- 13-Jul-2005 Arnold Ng o Created.
1348
1349 PROCEDURE check_financial_prof_op(
1350 p_party_id IN NUMBER,
1351 p_financial_profile_id IN NUMBER,
1352 x_last_update_date OUT NOCOPY DATE,
1353 x_return_status OUT NOCOPY VARCHAR2
1354 ) IS
1355 CURSOR is_fin_exist(l_fin_prof_id NUMBER) IS
1356 SELECT last_update_date, party_id
1357 FROM HZ_FINANCIAL_PROFILE
1358 WHERE financial_profile_id = l_fin_prof_id
1359 AND rownum = 1;
1360
1361 l_party_id NUMBER;
1362 BEGIN
1363 x_return_status := FND_API.G_RET_STS_SUCCESS;
1364 IF(p_financial_profile_id IS NULL) THEN
1365 x_last_update_date := NULL;
1366 ELSE
1367 OPEN is_fin_exist(p_financial_profile_id);
1368 FETCH is_fin_exist INTO x_last_update_date, l_party_id;
1369 CLOSE is_fin_exist;
1370 IF(l_party_id <> p_party_id) OR (l_party_id IS NULL AND p_party_id IS NOT NULL) THEN
1371 x_return_status := FND_API.G_RET_STS_ERROR;
1372 END IF;
1373 END IF;
1374 END check_financial_prof_op;
1375
1376 -- PROCEDURE check_code_assign_op
1377 --
1378 -- DESCRIPTION
1379 -- Check the operation of classification based on pass in parameter.
1380 --
1381 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1382 --
1383 -- ARGUMENTS
1384 -- IN:
1385 -- p_owner_table_name Owner table name.
1386 -- p_owner_table_id Owner table Id.
1387 -- p_class_category Class category.
1388 -- p_class_code Class code.
1389 -- IN/OUT:
1390 -- px_code_assignment_id Code assignment Id.
1391 -- OUT:
1392 -- x_object_version_number Object version number of classification.
1393 -- NOTES
1394 --
1395 -- MODIFICATION HISTORY
1396 --
1397 -- 13-Jul-2005 Arnold Ng o Created.
1398
1399 PROCEDURE check_code_assign_op(
1400 p_owner_table_name IN VARCHAR2,
1401 p_owner_table_id IN NUMBER,
1402 px_code_assignment_id IN OUT NOCOPY NUMBER,
1403 p_class_category IN VARCHAR2,
1404 p_class_code IN VARCHAR2,
1405 x_object_version_number OUT NOCOPY NUMBER
1406 ) IS
1407 CURSOR is_code_assign_id_exist(l_code_assignment_id NUMBER)IS
1408 SELECT nvl(object_version_number,1), owner_table_name, owner_table_id
1409 FROM HZ_CODE_ASSIGNMENTS
1410 WHERE code_assignment_id = l_code_assignment_id
1411 AND rownum = 1;
1412
1413 CURSOR is_code_assign_exist(l_owner_table_name VARCHAR2, l_owner_table_id NUMBER,
1414 l_class_category VARCHAR2, l_class_code VARCHAR2)IS
1415 SELECT nvl(object_version_number,1), code_assignment_id
1416 FROM HZ_CODE_ASSIGNMENTS
1417 WHERE owner_table_name = l_owner_table_name
1418 AND owner_table_id = l_owner_table_id
1419 AND class_category = l_class_category
1420 AND class_code = l_class_code
1421 AND sysdate between start_date_active and nvl(end_date_active, sysdate)
1422 AND status in ('A','I')
1423 AND rownum = 1;
1424
1425 l_ot_name VARCHAR2(30);
1426 l_ot_id NUMBER;
1427 BEGIN
1428 IF(px_code_assignment_id IS NULL) THEN
1429 OPEN is_code_assign_exist(p_owner_table_name, p_owner_table_id, p_class_category, p_class_code);
1430 FETCH is_code_assign_exist INTO x_object_version_number, px_code_assignment_id;
1431 CLOSE is_code_assign_exist;
1432 ELSE
1433 OPEN is_code_assign_id_exist(px_code_assignment_id);
1434 FETCH is_code_assign_id_exist INTO x_object_version_number, l_ot_name, l_ot_id;
1435 CLOSE is_code_assign_id_exist;
1436 IF(l_ot_name <> p_owner_table_name OR l_ot_id <> p_owner_table_id) OR
1437 (l_ot_id IS NULL OR (p_owner_table_name IS NOT NULL OR p_owner_table_id IS NOT NULL)) THEN
1438 -- return -1 to indicate that the combination of parent and object id do not match
1439 x_object_version_number := -1;
1440 END IF;
1441 END IF;
1442 END check_code_assign_op;
1443
1444 -- PROCEDURE check_party_pref_op
1445 --
1446 -- DESCRIPTION
1447 -- Check the operation of party preference based on pass in parameter.
1448 --
1449 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1450 --
1451 -- ARGUMENTS
1452 -- IN:
1453 -- p_party_id Party Id.
1454 -- p_module Module.
1455 -- p_category Category.
1456 -- p_preference_code Preference code.
1457 -- OUT:
1458 -- x_object_version_number Object version number of party preference.
1459 -- NOTES
1460 --
1461 -- MODIFICATION HISTORY
1462 --
1463 -- 13-Jul-2005 Arnold Ng o Created.
1464
1465 PROCEDURE check_party_pref_op(
1466 p_party_id IN NUMBER,
1467 p_module IN VARCHAR2,
1468 p_category IN VARCHAR2,
1469 p_preference_code IN VARCHAR2,
1470 x_object_version_number OUT NOCOPY NUMBER
1471 ) IS
1472 CURSOR is_party_pref_exist(l_party_id NUMBER, l_module VARCHAR2,
1473 l_category VARCHAR2, l_preference_code VARCHAR2) IS
1474 SELECT nvl(object_version_number,1)
1475 FROM HZ_PARTY_PREFERENCES
1476 WHERE party_id = l_party_id
1477 AND module = l_module
1478 AND category = l_category
1479 AND preference_code = l_preference_code
1480 AND rownum = 1;
1481 BEGIN
1482 OPEN is_party_pref_exist(p_party_id, p_module, p_category, p_preference_code);
1483 FETCH is_party_pref_exist INTO x_object_version_number;
1484 CLOSE is_party_pref_exist;
1485 END check_party_pref_op;
1486
1487 -- PROCEDURE check_credit_rating_op
1488 --
1489 -- DESCRIPTION
1490 -- Check the operation of credit rating based on pass in parameter.
1491 --
1492 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1493 --
1494 -- ARGUMENTS
1495 -- IN:
1496 -- p_party_id Party Id.
1497 -- p_rating_organization Rating organization.
1498 -- p_rated_as_of_date Rated date.
1499 -- IN/OUT:
1500 -- px_credit_rating_id Credit rating Id.
1501 -- OUT:
1502 -- x_object_version_number Object version number of credit rating.
1503 -- NOTES
1504 --
1505 -- MODIFICATION HISTORY
1506 --
1507 -- 13-Jul-2005 Arnold Ng o Created.
1508
1509 PROCEDURE check_credit_rating_op(
1510 p_party_id IN NUMBER,
1511 px_credit_rating_id IN OUT NOCOPY NUMBER,
1512 p_rating_organization IN VARCHAR2,
1513 p_rated_as_of_date IN DATE,
1514 x_object_version_number OUT NOCOPY NUMBER
1515 ) IS
1516 CURSOR is_credit_rating_id_exist(l_credit_rating_id NUMBER)IS
1517 SELECT nvl(object_version_number,1), party_id
1518 FROM HZ_CREDIT_RATINGS
1519 WHERE credit_rating_id = l_credit_rating_id
1520 AND rownum = 1;
1521
1522 CURSOR is_credit_rating_exist(l_party_id NUMBER, l_rating_organization VARCHAR2,
1523 l_rated_as_of_date DATE)IS
1524 SELECT nvl(object_version_number,1), credit_rating_id
1525 FROM HZ_CREDIT_RATINGS
1526 WHERE party_id = l_party_id
1527 AND nvl(rating_organization,'A') = nvl(l_rating_organization,'A')
1528 AND trunc(nvl(rated_as_of_date,sysdate)) = trunc(nvl(l_rated_as_of_date,sysdate))
1529 AND status in ('A','I')
1530 AND rownum = 1;
1531
1532 l_party_id NUMBER;
1533 BEGIN
1534 IF(px_credit_rating_id IS NULL) THEN
1535 OPEN is_credit_rating_exist(p_party_id, p_rating_organization, p_rated_as_of_date);
1536 FETCH is_credit_rating_exist INTO x_object_version_number, px_credit_rating_id;
1537 CLOSE is_credit_rating_exist;
1538 ELSE
1539 OPEN is_credit_rating_id_exist(px_credit_rating_id);
1540 FETCH is_credit_rating_id_exist INTO x_object_version_number, l_party_id;
1541 CLOSE is_credit_rating_id_exist;
1542 IF(l_party_id <> p_party_id) OR (l_party_id IS NULL AND p_party_id IS NOT NULL) THEN
1543 -- return -1 to indicate that the combination of parent and object id do not match
1544 x_object_version_number := -1;
1545 END IF;
1546 END IF;
1547 END check_credit_rating_op;
1548
1549 -- PROCEDURE check_fin_report_op
1550 --
1551 -- DESCRIPTION
1552 -- Check the operation of financial report based on pass in parameter.
1553 --
1554 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1555 --
1556 -- ARGUMENTS
1557 -- IN:
1558 -- p_party_id Party Id.
1559 -- p_type_of_financial_report Type of financial report.
1560 -- p_document_reference Document reference.
1561 -- p_date_report_issued Report issued date.
1562 -- p_issued_period Issued period.
1563 -- IN/OUT:
1564 -- px_fin_report_id Financial report Id.
1565 -- OUT:
1566 -- x_object_version_number Object version number of financial report.
1567 -- NOTES
1568 --
1569 -- MODIFICATION HISTORY
1570 --
1571 -- 13-Jul-2005 Arnold Ng o Created.
1572
1573 PROCEDURE check_fin_report_op(
1574 p_party_id IN NUMBER,
1575 px_fin_report_id IN OUT NOCOPY NUMBER,
1576 p_type_of_financial_report IN VARCHAR2,
1577 p_document_reference IN VARCHAR2,
1578 p_date_report_issued IN DATE,
1579 p_issued_period IN VARCHAR2,
1580 x_object_version_number OUT NOCOPY NUMBER
1581 ) IS
1582 CURSOR is_fin_report_id_exist(l_fin_report_id NUMBER)IS
1583 SELECT nvl(object_version_number,1), party_id
1584 FROM HZ_FINANCIAL_REPORTS
1585 WHERE financial_report_id = l_fin_report_id
1586 AND rownum = 1;
1587
1588 CURSOR is_fin_report_exist(l_party_id NUMBER, l_type_of_fin_report VARCHAR2,
1589 l_doc_reference VARCHAR2, l_date_report_issued DATE,
1590 l_issued_period VARCHAR2 )IS
1591 SELECT nvl(object_version_number,1), financial_report_id
1592 FROM HZ_FINANCIAL_REPORTS
1593 WHERE party_id = l_party_id
1594 AND type_of_financial_report = l_type_of_fin_report
1595 AND document_reference = l_doc_reference
1596 AND (trunc(date_report_issued) = trunc(l_date_report_issued) OR
1597 issued_period = l_issued_period OR
1598 sysdate between nvl(report_start_date,sysdate) and nvl(report_end_date, sysdate))
1599 AND status in ('A','I')
1600 AND rownum = 1;
1601
1602 l_party_id NUMBER;
1603 BEGIN
1604 IF(px_fin_report_id IS NULL) THEN
1605 OPEN is_fin_report_exist(p_party_id, p_type_of_financial_report, p_document_reference,
1606 p_date_report_issued, p_issued_period);
1607 FETCH is_fin_report_exist INTO x_object_version_number, px_fin_report_id;
1608 CLOSE is_fin_report_exist;
1609 ELSE
1610 OPEN is_fin_report_id_exist(px_fin_report_id);
1611 FETCH is_fin_report_id_exist INTO x_object_version_number, l_party_id;
1612 CLOSE is_fin_report_id_exist;
1613 IF(l_party_id <> p_party_id) OR (l_party_id IS NULL AND p_party_id IS NOT NULL) THEN
1614 -- return -1 to indicate that the combination of parent and object id do not match
1615 x_object_version_number := -1;
1616 END IF;
1617 END IF;
1618 END check_fin_report_op;
1619
1620 -- PROCEDURE check_fin_number_op
1621 --
1622 -- DESCRIPTION
1623 -- Check the operation of financial number based on pass in parameter.
1624 --
1625 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1626 --
1627 -- ARGUMENTS
1628 -- IN:
1629 -- p_fin_report_id Financial report Id.
1630 -- p_financial_number_name Name of financial number.
1631 -- IN/OUT:
1632 -- px_fin_number_id Financial number Id.
1633 -- OUT:
1634 -- x_object_version_number Object version number of financial number.
1635 -- NOTES
1636 --
1637 -- MODIFICATION HISTORY
1638 --
1639 -- 13-Jul-2005 Arnold Ng o Created.
1640
1641 PROCEDURE check_fin_number_op(
1642 p_fin_report_id IN NUMBER,
1643 px_fin_number_id IN OUT NOCOPY NUMBER,
1644 p_financial_number_name IN VARCHAR2,
1645 x_object_version_number OUT NOCOPY NUMBER
1646 ) IS
1647 CURSOR is_fin_number_id_exist(l_fin_number_id NUMBER) IS
1648 SELECT nvl(object_version_number,1), financial_report_id
1649 FROM HZ_FINANCIAL_NUMBERS
1650 WHERE financial_number_id = l_fin_number_id
1651 AND rownum = 1;
1652
1653 CURSOR is_fin_number_exist(l_fin_report_id NUMBER, l_fin_number_name VARCHAR2) IS
1654 SELECT nvl(object_version_number,1), financial_number_id
1655 FROM HZ_FINANCIAL_NUMBERS
1656 WHERE financial_report_id = l_fin_report_id
1657 AND financial_number_name = l_fin_number_name
1658 AND status in ('A','I')
1659 AND rownum = 1;
1660
1661 l_fr_id NUMBER;
1662 BEGIN
1663 IF(px_fin_number_id IS NULL) THEN
1664 OPEN is_fin_number_exist(p_fin_report_id, p_financial_number_name);
1665 FETCH is_fin_number_exist INTO x_object_version_number, px_fin_number_id;
1666 CLOSE is_fin_number_exist;
1667 ELSE
1668 OPEN is_fin_number_id_exist(px_fin_number_id);
1669 FETCH is_fin_number_id_exist INTO x_object_version_number, l_fr_id;
1670 CLOSE is_fin_number_id_exist;
1671 IF(l_fr_id <> p_fin_report_id) OR (l_fr_id IS NULL AND p_fin_report_id IS NOT NULL) THEN
1672 -- return -1 to indicate that the combination of parent and object id do not match
1673 x_object_version_number := -1;
1674 END IF;
1675 END IF;
1676 END check_fin_number_op;
1677
1678 -- PROCEDURE check_role_resp_op
1679 --
1680 -- DESCRIPTION
1681 -- Check the operation of role responsibility based on pass in parameter.
1682 --
1683 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1684 --
1685 -- ARGUMENTS
1686 -- IN:
1687 -- p_cust_acct_contact_id Customer account contact Id.
1688 -- p_responsibility_type Role responsibility type.
1689 -- IN/OUT:
1690 -- px_responsibility_id Role responsibility Id.
1691 -- OUT:
1692 -- x_object_version_number Object version number of role responsibility.
1693 -- NOTES
1694 --
1695 -- MODIFICATION HISTORY
1696 --
1697 -- 13-Jul-2005 Arnold Ng o Created.
1698
1699 PROCEDURE check_role_resp_op(
1700 p_cust_acct_contact_id IN NUMBER,
1701 px_responsibility_id IN OUT NOCOPY NUMBER,
1702 p_responsibility_type IN VARCHAR2,
1703 x_object_version_number OUT NOCOPY NUMBER
1704 ) IS
1705 CURSOR is_role_resp_id_exist(l_resp_id NUMBER) IS
1706 SELECT nvl(object_version_number,1), cust_account_role_id
1707 FROM HZ_ROLE_RESPONSIBILITY
1708 WHERE responsibility_id = l_resp_id
1709 AND rownum = 1;
1710
1711 CURSOR is_role_resp_exist(l_cac_id NUMBER, l_resp_type VARCHAR2) IS
1712 SELECT nvl(object_version_number,1), responsibility_id
1713 FROM HZ_ROLE_RESPONSIBILITY
1714 WHERE cust_account_role_id = l_cac_id
1715 AND responsibility_type = l_resp_type
1716 AND rownum = 1;
1717
1718 l_cac_id NUMBER;
1719 BEGIN
1720 IF(px_responsibility_id IS NULL) THEN
1721 OPEN is_role_resp_exist(p_cust_acct_contact_id, p_responsibility_type);
1722 FETCH is_role_resp_exist INTO x_object_version_number, px_responsibility_id;
1723 CLOSE is_role_resp_exist;
1724 ELSE
1725 OPEN is_role_resp_id_exist(px_responsibility_id);
1726 FETCH is_role_resp_id_exist INTO x_object_version_number, l_cac_id;
1727 CLOSE is_role_resp_id_exist;
1728 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
1729 -- return -1 to indicate that the combination of parent and object id do not match
1730 x_object_version_number := -1;
1731 END IF;
1732 END IF;
1733 END check_role_resp_op;
1734
1735 -- PROCEDURE check_cust_profile_op
1736 --
1737 -- DESCRIPTION
1738 -- Check the operation of customer profile based on pass in parameter.
1739 --
1740 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1741 --
1742 -- ARGUMENTS
1743 -- IN:
1744 -- p_cust_acct_id Customer account Id.
1745 -- p_site_use_id Customer site use Id.
1746 -- p_profile_class_id Profile class Id.
1747 -- IN/OUT:
1748 -- px_cust_acct_profile_id Customer profile Id.
1749 -- OUT:
1750 -- x_object_version_number Object version number of customer profile.
1751 -- NOTES
1752 --
1753 -- MODIFICATION HISTORY
1754 --
1755 -- 13-Jul-2005 Arnold Ng o Created.
1756
1757 PROCEDURE check_cust_profile_op(
1758 p_cust_acct_id IN NUMBER,
1759 px_cust_acct_profile_id IN OUT NOCOPY NUMBER,
1760 p_site_use_id IN NUMBER,
1761 p_profile_class_id IN NUMBER,
1762 x_object_version_number OUT NOCOPY NUMBER
1763 ) IS
1764 CURSOR is_cust_profile_id_exist(l_cust_acct_prof_id NUMBER) IS
1765 SELECT nvl(object_version_number,1), cust_account_id, site_use_id
1766 FROM HZ_CUSTOMER_PROFILES
1767 WHERE cust_account_profile_id = l_cust_acct_prof_id
1768 AND rownum = 1;
1769
1770 CURSOR is_cust_profile_exist(l_ca_id NUMBER, l_site_use_id NUMBER, l_profile_class_id NUMBER) IS
1771 SELECT nvl(object_version_number,1), cust_account_profile_id
1772 FROM HZ_CUSTOMER_PROFILES
1773 WHERE cust_account_id = l_ca_id
1774 AND nvl(site_use_id, -99) = nvl(l_site_use_id, -99)
1775 AND profile_class_id = l_profile_class_id
1776 AND status in ('A','I')
1777 AND rownum = 1;
1778
1779 l_ca_id NUMBER;
1780 l_casu_id NUMBER;
1781 BEGIN
1782 IF(px_cust_acct_profile_id IS NULL) THEN
1783 OPEN is_cust_profile_exist(p_cust_acct_id, p_site_use_id, p_profile_class_id);
1784 FETCH is_cust_profile_exist INTO x_object_version_number, px_cust_acct_profile_id;
1785 CLOSE is_cust_profile_exist;
1786 ELSE
1787 OPEN is_cust_profile_id_exist(px_cust_acct_profile_id);
1788 FETCH is_cust_profile_id_exist INTO x_object_version_number, l_ca_id, l_casu_id;
1789 CLOSE is_cust_profile_id_exist;
1790 IF((l_ca_id <> p_cust_acct_id) OR (nvl(l_casu_id,-99) <> nvl(p_site_use_id,-99))) OR
1791 ((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
1792 -- return -1 to indicate that the combination of parent and object id do not match
1793 x_object_version_number := -1;
1794 END IF;
1795 END IF;
1796 END check_cust_profile_op;
1797
1798 -- PROCEDURE check_cust_profile_amt_op
1799 --
1800 -- DESCRIPTION
1801 -- Check the operation of customer profile amount based on pass in parameter.
1802 --
1803 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1804 --
1805 -- ARGUMENTS
1806 -- IN:
1807 -- p_cust_profile_id Customer profile Id.
1808 -- p_currency_code Currency code.
1809 -- IN/OUT:
1810 -- px_cust_acct_prof_amt_id Customer profile amount Id.
1811 -- OUT:
1812 -- x_object_version_number Object version number of customer profile amount.
1813 -- NOTES
1814 --
1815 -- MODIFICATION HISTORY
1816 --
1817 -- 13-Jul-2005 Arnold Ng o Created.
1818
1819 PROCEDURE check_cust_profile_amt_op(
1820 p_cust_profile_id IN NUMBER,
1821 px_cust_acct_prof_amt_id IN OUT NOCOPY NUMBER,
1822 p_currency_code IN VARCHAR2,
1823 x_object_version_number OUT NOCOPY NUMBER
1824 ) IS
1825 CURSOR is_cust_profile_amt_id_exist(l_cust_prof_amt_id NUMBER) IS
1826 SELECT nvl(object_version_number,1), cust_account_profile_id
1827 FROM HZ_CUST_PROFILE_AMTS
1828 WHERE cust_acct_profile_amt_id = l_cust_prof_amt_id
1829 AND rownum = 1;
1830
1831 CURSOR is_cust_profile_amt_exist(l_cap_id NUMBER, l_currency_code VARCHAR2) IS
1832 SELECT nvl(object_version_number,1), cust_acct_profile_amt_id
1833 FROM HZ_CUST_PROFILE_AMTS
1834 WHERE cust_account_profile_id = l_cap_id
1835 AND currency_code = l_currency_code
1836 AND rownum = 1;
1837
1838 l_cap_id NUMBER;
1839 BEGIN
1840 IF(px_cust_acct_prof_amt_id IS NULL) THEN
1841 OPEN is_cust_profile_amt_exist(p_cust_profile_id, p_currency_code);
1842 FETCH is_cust_profile_amt_exist INTO x_object_version_number, px_cust_acct_prof_amt_id;
1843 CLOSE is_cust_profile_amt_exist;
1844 ELSE
1845 OPEN is_cust_profile_amt_id_exist(px_cust_acct_prof_amt_id);
1846 FETCH is_cust_profile_amt_id_exist INTO x_object_version_number, l_cap_id;
1847 CLOSE is_cust_profile_amt_id_exist;
1848 IF(l_cap_id <> p_cust_profile_id) OR (l_cap_id IS NULL AND p_cust_profile_id IS NOT NULL) THEN
1849 -- return -1 to indicate that the combination of parent and object id do not match
1850 x_object_version_number := -1;
1851 END IF;
1852 END IF;
1853 END check_cust_profile_amt_op;
1854
1855 -- PROCEDURE check_cust_acct_relate_op
1856 --
1857 -- DESCRIPTION
1858 -- Check the operation of customer account relationship based on pass in parameter.
1859 --
1860 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1861 --
1862 -- ARGUMENTS
1863 -- IN:
1864 -- p_cust_acct_id Customer account Id.
1865 -- p_related_cust_acct_id Related customer account Id.
1866 -- OUT:
1867 -- x_object_version_number Object version number of customer account relationship.
1868 -- NOTES
1869 --
1870 -- MODIFICATION HISTORY
1871 --
1872 -- 13-Jul-2005 Arnold Ng o Created.
1873
1874 PROCEDURE check_cust_acct_relate_op(
1875 p_cust_acct_id IN NUMBER,
1876 p_related_cust_acct_id IN NUMBER,
1877 x_object_version_number OUT NOCOPY NUMBER
1878 ) IS
1879 CURSOR is_cust_acct_relate_exist(l_ca_id NUMBER, l_rca_id NUMBER) IS
1880 SELECT nvl(object_version_number,1)
1881 FROM HZ_CUST_ACCT_RELATE
1882 WHERE cust_account_id = l_ca_id
1883 AND related_cust_account_id = l_rca_id
1884 AND status in ('A','I')
1885 AND rownum = 1;
1886 BEGIN
1887 OPEN is_cust_acct_relate_exist(p_cust_acct_id, p_related_cust_acct_id);
1888 FETCH is_cust_acct_relate_exist INTO x_object_version_number;
1889 CLOSE is_cust_acct_relate_exist;
1890 END check_cust_acct_relate_op;
1891
1892 -- PROCEDURE check_payment_method_op
1893 --
1894 -- DESCRIPTION
1895 -- Check the operation of payment method based on pass in parameter.
1896 --
1897 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1898 --
1899 -- ARGUMENTS
1900 -- IN:
1901 -- p_cust_receipt_method_id Payment method Id.
1902 -- OUT:
1903 -- x_last_update_date Last update date of payment method.
1904 -- NOTES
1905 --
1906 -- MODIFICATION HISTORY
1907 --
1908 -- 13-Jul-2005 Arnold Ng o Created.
1909
1910 PROCEDURE check_payment_method_op(
1911 p_cust_receipt_method_id IN NUMBER,
1912 x_last_update_date OUT NOCOPY DATE
1913 ) IS
1914 CURSOR is_payment_method_exist(l_pm_id NUMBER) IS
1915 SELECT last_update_date
1916 FROM RA_CUST_RECEIPT_METHODS
1917 WHERE cust_receipt_method_id = l_pm_id
1918 AND rownum = 1;
1919 BEGIN
1920 OPEN is_payment_method_exist(p_cust_receipt_method_id);
1921 FETCH is_payment_method_exist INTO x_last_update_date;
1922 CLOSE is_payment_method_exist;
1923 END check_payment_method_op;
1924
1925 -- FUNCTION check_bo_op
1926 --
1927 -- DESCRIPTION
1928 -- Return the operation of business object based on pass in parameter.
1929 -- Return value can be 'C' (create) or 'U' (update)
1930 --
1931 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1932 --
1933 -- ARGUMENTS
1934 -- IN:
1935 -- p_entity_id Business object Id.
1936 -- p_entity_os Business object original system.
1937 -- p_entity_osr Business object original system reference.
1938 -- p_entity_type Business object type.
1939 -- p_cp_type Contact point type.
1940 -- p_parent_id Parent Id,
1941 -- p_parent_table Parent table
1942 -- NOTES
1943 --
1944 -- MODIFICATION HISTORY
1945 --
1946 -- 13-Jul-2005 Arnold Ng o Created.
1947
1948 FUNCTION check_bo_op(
1949 p_entity_id IN NUMBER,
1950 p_entity_os IN VARCHAR2,
1951 p_entity_osr IN VARCHAR2,
1952 p_entity_type IN VARCHAR2,
1953 p_cp_type IN VARCHAR2 := NULL,
1954 p_parent_id IN NUMBER,
1955 p_parent_obj_type IN VARCHAR2
1956 ) RETURN VARCHAR2 IS
1957 CURSOR is_contact_point_exist(l_contact_point_id NUMBER, l_contact_point_type VARCHAR2) IS
1958 SELECT owner_table_id, owner_table_name
1959 FROM HZ_CONTACT_POINTS
1960 WHERE contact_point_id = l_contact_point_id
1961 AND contact_point_type = l_contact_point_type;
1962
1963 CURSOR is_party_site_exist(l_ps_id NUMBER) IS
1964 SELECT party_id
1965 FROM HZ_PARTY_SITES
1966 WHERE party_site_id = l_ps_id;
1967
1968 CURSOR is_location_exist(l_loc_id NUMBER) IS
1969 SELECT 'X'
1970 FROM HZ_LOCATIONS
1971 WHERE location_id = l_loc_id;
1972
1973 CURSOR is_party_exist(l_party_id NUMBER) IS
1974 SELECT 'X'
1975 FROM HZ_PARTIES
1976 WHERE party_id = l_party_id;
1977
1978 CURSOR is_org_contact_exist(l_org_contact_id NUMBER) IS
1979 SELECT r.object_id
1980 FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r
1981 WHERE oc.org_contact_id = l_org_contact_id
1982 AND oc.party_relationship_id = r.relationship_id
1983 AND r.object_type = 'ORGANIZATION'
1984 AND r.subject_type = 'PERSON'
1985 AND rownum = 1;
1986
1987 CURSOR is_cust_account_exist(l_cust_acct_id NUMBER) IS
1988 SELECT party_id
1989 FROM HZ_CUST_ACCOUNTS
1990 WHERE cust_account_id = l_cust_acct_id;
1991
1992 CURSOR is_cust_acct_site_exist(l_cust_acct_site_id NUMBER) IS
1993 SELECT cust_account_id
1994 FROM HZ_CUST_ACCT_SITES_ALL
1995 WHERE cust_acct_site_id = l_cust_acct_site_id;
1996
1997 CURSOR is_cust_site_use_exist(l_site_use_id NUMBER) IS
1998 SELECT cust_acct_site_id
1999 FROM HZ_CUST_SITE_USES
2000 WHERE site_use_id = l_site_use_id;
2001
2002 CURSOR is_cust_acct_role_exist(l_cust_acct_role_id NUMBER) IS
2003 SELECT cust_account_id, nvl(cust_acct_site_id, -99)
2004 FROM HZ_CUST_ACCOUNT_ROLES
2005 WHERE cust_account_role_id = l_cust_acct_role_id;
2006
2007 l_create_update_flag VARCHAR2(1);
2008 l_dummy VARCHAR2(1);
2009 l_ss_flag VARCHAR2(1);
2010 l_owner_table_id NUMBER;
2011 l_debug_prefix VARCHAR2(30);
2012 l_return_status VARCHAR2(30);
2013 l_count NUMBER;
2014 l_parent_id NUMBER;
2015 l_acct_site_id NUMBER;
2016 l_input_parent_table VARCHAR2(30);
2017 l_parent_table VARCHAR2(30);
2018 BEGIN
2019 l_dummy := NULL;
2020
2021 l_ss_flag := is_ss_provided(p_os => p_entity_os,
2022 p_osr => p_entity_osr);
2023
2024 -- Return as 'Create' if no TCA id and no os+osr pass in
2025 -- Fix bug 4748851
2026 IF(p_entity_id IS NULL) AND (l_ss_flag = 'N') THEN
2027 RETURN 'C';
2028 END IF;
2029
2030 -- if TCA id pass in, check if it is valid or not
2031 IF(p_entity_id IS NOT NULL) THEN
2032 l_input_parent_table := get_owner_table_name(p_parent_obj_type);
2033
2034 IF(p_entity_type = 'HZ_CONTACT_POINTS') THEN
2035 OPEN is_contact_point_exist(p_entity_id, p_cp_type);
2036 FETCH is_contact_point_exist INTO l_parent_id, l_parent_table;
2037 CLOSE is_contact_point_exist;
2038 IF(l_parent_id IS NULL OR l_parent_id <> p_parent_id OR l_parent_table <> l_input_parent_table) THEN
2039 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_ID');
2040 FND_MSG_PUB.ADD;
2041 RETURN 'E';
2042 END IF;
2043 l_dummy := 'X';
2044 ELSIF(p_entity_type = 'HZ_PARTY_SITES') THEN
2045 OPEN is_party_site_exist(p_entity_id);
2046 FETCH is_party_site_exist INTO l_parent_id;
2047 CLOSE is_party_site_exist;
2048 IF(l_parent_id IS NULL OR l_parent_id <> p_parent_id) THEN
2049 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_ID');
2050 FND_MSG_PUB.ADD;
2051 RETURN 'E';
2052 END IF;
2053 l_dummy := 'X';
2054 ELSIF(p_entity_type = 'HZ_LOCATIONS') THEN
2055 OPEN is_location_exist(p_entity_id);
2056 FETCH is_location_exist INTO l_dummy;
2057 CLOSE is_location_exist;
2058 ELSIF(p_entity_type = 'HZ_PARTIES') THEN
2059 OPEN is_party_exist(p_entity_id);
2060 FETCH is_party_exist INTO l_dummy;
2061 CLOSE is_party_exist;
2062 ELSIF(p_entity_type = 'HZ_ORG_CONTACTS') THEN
2063 OPEN is_org_contact_exist(p_entity_id);
2064 FETCH is_org_contact_exist INTO l_parent_id;
2065 CLOSE is_org_contact_exist;
2066 IF(l_parent_id IS NULL OR l_parent_id <> p_parent_id) THEN
2067 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_ID');
2068 FND_MSG_PUB.ADD;
2069 RETURN 'E';
2070 END IF;
2071 l_dummy := 'X';
2072 ELSIF(p_entity_type = 'HZ_CUST_ACCOUNTS') THEN
2073 OPEN is_cust_account_exist(p_entity_id);
2074 FETCH is_cust_account_exist INTO l_parent_id;
2075 CLOSE is_cust_account_exist;
2076 IF(l_parent_id IS NULL OR l_parent_id <> p_parent_id) THEN
2077 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_ID');
2078 FND_MSG_PUB.ADD;
2079 RETURN 'E';
2080 END IF;
2081 l_dummy := 'X';
2082 ELSIF(p_entity_type = 'HZ_CUST_ACCT_SITES_ALL') THEN
2083 OPEN is_cust_acct_site_exist(p_entity_id);
2084 FETCH is_cust_acct_site_exist INTO l_parent_id;
2085 CLOSE is_cust_acct_site_exist;
2086 IF(l_parent_id IS NULL OR l_parent_id <> p_parent_id) THEN
2087 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_ID');
2088 FND_MSG_PUB.ADD;
2089 RETURN 'E';
2090 END IF;
2091 l_dummy := 'X';
2092 ELSIF(p_entity_type = 'HZ_CUST_SITE_USES_ALL') THEN
2093 OPEN is_cust_site_use_exist(p_entity_id);
2094 FETCH is_cust_site_use_exist INTO l_parent_id;
2095 CLOSE is_cust_site_use_exist;
2096 IF(l_parent_id IS NULL OR l_parent_id <> p_parent_id) THEN
2097 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_ID');
2098 FND_MSG_PUB.ADD;
2099 RETURN 'E';
2100 END IF;
2101 l_dummy := 'X';
2102 ELSIF(p_entity_type = 'HZ_CUST_ACCOUNT_ROLES') THEN
2103 OPEN is_cust_acct_role_exist(p_entity_id);
2104 FETCH is_cust_acct_role_exist INTO l_parent_id, l_acct_site_id;
2105 CLOSE is_cust_acct_role_exist;
2106 IF(p_parent_obj_type = 'CUST_ACCT_SITE') THEN
2107 IF(l_parent_id IS NULL OR l_acct_site_id <> p_parent_id) THEN
2108 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_ID');
2109 FND_MSG_PUB.ADD;
2110 RETURN 'E';
2111 END IF;
2112 ELSIF(p_parent_obj_type = 'CUST_ACCT') THEN
2113 IF(l_parent_id IS NULL OR l_parent_id <> p_parent_id) THEN
2114 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_ID');
2115 FND_MSG_PUB.ADD;
2116 RETURN 'E';
2117 END IF;
2118 END IF;
2119 l_dummy := 'X';
2120 END IF;
2121 END IF;
2122
2123 IF(l_ss_flag = 'Y') THEN
2124 -- Get how many rows return
2125 l_count := HZ_MOSR_VALIDATE_PKG.get_orig_system_ref_count(
2126 p_orig_system => p_entity_os,
2127 p_orig_system_reference => p_entity_osr,
2128 p_owner_table_name => p_entity_type);
2129
2130 IF(l_count > 0) THEN
2131 HZ_ORIG_SYSTEM_REF_PUB.get_owner_table_id(
2132 p_orig_system => p_entity_os,
2133 p_orig_system_reference => p_entity_osr,
2134 p_owner_table_name => p_entity_type,
2135 x_owner_table_id => l_owner_table_id,
2136 x_return_status => l_return_status);
2137
2138 -- For contact point, check if the id and type is the same
2139 IF(p_entity_type = 'HZ_CONTACT_POINTS') THEN
2140 OPEN is_contact_point_exist(l_owner_table_id, p_cp_type);
2141 FETCH is_contact_point_exist INTO l_parent_id, l_parent_table;
2142 CLOSE is_contact_point_exist;
2143 IF(l_parent_id IS NULL OR l_parent_id <> p_parent_id OR l_parent_table <> l_input_parent_table) THEN
2144 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_ID');
2145 FND_MSG_PUB.ADD;
2146 RETURN 'E';
2147 END IF;
2148 END IF;
2149 END IF;
2150 END IF;
2151
2152 -- no TCA id
2153 IF(p_entity_id IS NULL) THEN
2154 -- ssm is invalid
2155 IF(l_ss_flag = 'Y') AND (l_count = 0) THEN
2156 RETURN 'C';
2157 END IF;
2158 -- ssm is valid
2159 IF(l_ss_flag = 'Y') AND (l_count > 0) AND (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2160 RETURN 'U';
2161 ELSE
2162 RETURN 'E';
2163 END IF;
2164 ELSE
2165 -- invalid TCA id
2166 IF(l_dummy IS NULL) THEN
2167 -- ssm is valid
2168 IF(l_ss_flag = 'Y') AND (l_count > 0) AND (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2169 RETURN 'E';
2170 END IF;
2171 RETURN 'C';
2172 -- valid TCA id
2173 ELSE
2174 RETURN 'U';
2175 END IF;
2176 END IF;
2177 END check_bo_op;
2178
2179 -- PROCEDURE check_party_usage_op
2180 --
2181 -- DESCRIPTION
2182 -- Checks if a row exists in party_usg_assigments table for agiven
2183 -- party_id and party_usages_code.
2184 -- If exists Return last_update_date value. otherwise null.
2185 --
2186 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2187 --
2188 -- ARGUMENTS
2189 -- IN:
2190 -- p_party_id id of a party for whicch party_usage was created.
2191 -- p_party_usage_code seeded usage code.
2192
2193 -- OUT:
2194 -- x_last_update_date last_update_date column,
2195 -- x_return_status status of the call
2196 -- NOTES
2197 --
2198 -- MODIFICATION HISTORY
2199 --
2200 -- 01-Mar-2006 Hadi Alatasi o Created.
2201
2202 PROCEDURE check_party_usage_op(
2203 p_party_id IN NUMBER,
2204 p_party_usage_code IN VARCHAR2,
2205 x_last_update_date OUT NOCOPY DATE,
2206 x_return_status OUT NOCOPY VARCHAR2
2207 ) IS
2208 CURSOR is_usg_exist(l_party_id NUMBER, l_party_usage_code VARCHAR2 ) IS
2209 SELECT last_update_date
2210 FROM HZ_PARTY_USG_ASSIGNMENTS
2211 WHERE PARTY_USAGE_CODE = l_party_usage_code
2212 AND PARTY_ID= l_party_id
2213 AND rownum = 1;
2214
2215 l_party_id NUMBER;
2216 BEGIN
2217 x_return_status := FND_API.G_RET_STS_SUCCESS;
2218 IF(p_party_usage_code IS NULL OR p_party_id IS NULL) THEN
2219 x_last_update_date := NULL;
2220 ELSE
2221 OPEN is_usg_exist(p_party_id,p_party_usage_code);
2222 FETCH is_usg_exist INTO x_last_update_date;
2223 CLOSE is_usg_exist;
2224 END IF;
2225 EXCEPTION
2226 when NO_DATA_FOUND then
2227 x_last_update_date := NULL;
2228 when OTHERS then
2229 x_return_status := FND_API.G_RET_STS_ERROR;
2230
2231 END check_party_usage_op;
2232
2233
2234 -- PRIVATE FUNCTION is_ss_provided
2235 --
2236 -- DESCRIPTION
2237 -- Return a flag to indicate that original system and original system reference
2238 -- are provided.
2239 --
2240 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2241 --
2242 -- ARGUMENTS
2243 -- IN:
2244 -- p_os Original system.
2245 -- p_osr Original system reference.
2246 -- NOTES
2247 --
2248 -- MODIFICATION HISTORY
2249 --
2250 -- 13-Jul-2005 Arnold Ng o Created.
2251
2252 FUNCTION is_ss_provided(
2253 p_os IN VARCHAR2,
2254 p_osr IN VARCHAR2
2255 ) RETURN VARCHAR2 IS
2256 BEGIN
2257 IF((p_os is null or p_os = fnd_api.g_miss_char)
2258 and (p_osr is null or p_osr = fnd_api.g_miss_char))THEN
2259 RETURN 'N';
2260 ELSE
2261 RETURN 'Y';
2262 END IF;
2263 END is_ss_provided;
2264
2265 -- FUNCTION get_owner_table_name
2266 --
2267 -- DESCRIPTION
2268 -- Return the owner table name based on object type.
2269 --
2270 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2271 --
2272 -- ARGUMENTS
2273 -- IN:
2274 -- p_obj_type Object type.
2275 -- NOTES
2276 --
2277 -- MODIFICATION HISTORY
2278 --
2279 -- 13-Jul-2005 Arnold Ng o Created.
2280
2281 FUNCTION get_owner_table_name(
2282 p_obj_type IN VARCHAR2
2283 ) RETURN VARCHAR2 IS
2284 BEGIN
2285 -- Base on HZ_BUSINESS_OBJECTS lookup code
2286 IF(p_obj_type = 'PARTY_SITE') THEN
2287 RETURN 'HZ_PARTY_SITES';
2288 ELSIF((p_obj_type = 'ORG') OR (p_obj_type = 'PERSON') OR (p_obj_type = 'ORG_CONTACT')) THEN
2289 RETURN 'HZ_PARTIES';
2290 ELSIF((p_obj_type = 'ORG_CUST') OR (p_obj_type = 'PERSON_CUST') OR (p_obj_type = 'CUST_ACCT')) THEN
2291 RETURN 'HZ_CUST_ACCOUNTS';
2292 ELSIF(p_obj_type = 'CUST_ACCT_CONTACT') THEN
2293 RETURN 'HZ_CUST_ACCOUNT_ROLES';
2294 ELSIF(p_obj_type = 'CUST_ACCT_SITE') THEN
2295 RETURN 'HZ_CUST_ACCT_SITES_ALL';
2296 ELSIF(p_obj_type in ('PHONE', 'EMAIL', 'TLX', 'WEB', 'EDI', 'EFT', 'SMS')) THEN
2297 RETURN 'HZ_CONTACT_POINTS';
2298 ELSIF(p_obj_type = 'LOCATION') THEN
2299 RETURN 'HZ_LOCATIONS';
2300 ELSIF(p_obj_type = 'CUST_ACCT_SITE_USE') THEN
2301 RETURN 'HZ_CUST_SITE_USES_ALL';
2302 END IF;
2303 RETURN NULL;
2304 END get_owner_table_name;
2305
2306 -- FUNCTION get_parent_object_type
2307 --
2308 -- DESCRIPTION
2309 -- Return the object type based on parent table and Id.
2310 --
2311 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2312 --
2313 -- ARGUMENTS
2314 -- IN:
2315 -- p_parent_table_name Parent table name.
2316 -- p_parent_id Parent Id.
2317 -- NOTES
2318 --
2319 -- MODIFICATION HISTORY
2320 --
2321 -- 13-Jul-2005 Arnold Ng o Created.
2322
2323 FUNCTION get_parent_object_type(
2324 p_parent_table_name IN VARCHAR2,
2325 p_parent_id IN NUMBER
2326 ) RETURN VARCHAR2 IS
2327
2328 l_party_type VARCHAR2(30) := null;
2329
2330 CURSOR c1 IS
2331 SELECT party_type FROM hz_parties
2332 WHERE party_id = p_parent_id;
2333
2334 CURSOR c2 IS
2335 SELECT party_type
2336 FROM hz_parties p, hz_cust_accounts ca
2337 WHERE p.party_id = ca.party_id
2338 AND ca.cust_account_id = p_parent_id;
2339
2340 BEGIN
2341 -- Base on owner_table_name to return HZ_BUSINESS_OBJECTS lookup code
2342 IF p_parent_table_name = 'HZ_PARTIES' THEN
2343 OPEN c1;
2344 FETCH c1 INTO l_party_type;
2345 CLOSE c1;
2346 ELSIF p_parent_table_name = 'HZ_CUST_ACCOUNTS' THEN
2347 OPEN c2;
2348 FETCH c2 INTO l_party_type;
2349 CLOSE c2;
2350 END IF;
2351
2352 IF(p_parent_table_name = 'HZ_PARTY_SITES') THEN
2353 RETURN 'PARTY_SITE';
2354 ELSIF(p_parent_table_name = 'HZ_PARTIES') THEN
2355 IF(l_party_type = 'ORGANIZATION') THEN
2356 RETURN 'ORG';
2357 ELSIF(l_party_type = 'PERSON') THEN
2358 RETURN 'PERSON';
2359 ELSIF(l_party_type = 'PARTY_RELATIONSHIP') THEN
2360 RETURN 'ORG_CONTACT';
2361 END IF;
2362 ELSIF(p_parent_table_name = 'HZ_CUST_ACCOUNTS') THEN
2363 IF(l_party_type = 'ORGANIZATION') THEN
2364 RETURN 'ORG_CUST';
2365 ELSIF(l_party_type = 'PERSON') THEN
2366 RETURN 'PERSON_CUST';
2367 ELSIF(l_party_type IS NULL) THEN
2368 RETURN 'CUST_ACCT';
2369 END IF;
2370 ELSIF(p_parent_table_name = 'HZ_CUST_ACCOUNT_ROLES') THEN
2371 RETURN 'CUST_ACCT_CONTACT';
2372 ELSIF(p_parent_table_name = 'HZ_CUST_ACCT_SITES_ALL') THEN
2373 RETURN 'CUST_ACCT_SITE';
2374 END IF;
2375 RETURN NULL;
2376 END get_parent_object_type;
2377
2378 -- FUNCTION is_cp_bo_comp
2379 --
2380 -- DESCRIPTION
2381 -- Return true if contact point object is complete. Otherwise, return false.
2382 --
2383 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2384 --
2385 -- ARGUMENTS
2386 -- IN:
2387 -- p_phone_objs List of phone business objects.
2388 -- p_email_objs List of email business objects.
2389 -- p_telex_objs List of telex business objects.
2390 -- p_web_objs List of web business objects.
2391 -- p_edi_objs List of edi business objects.
2392 -- p_eft_objs List of eft business objects.
2393 -- p_sms_objs List of sms business objects.
2394 -- p_bus_object Business object structure for contact point.
2395 -- NOTES
2396 --
2397 -- MODIFICATION HISTORY
2398 --
2399 -- 13-Jul-2005 Arnold Ng o Created.
2400
2401 FUNCTION is_cp_bo_comp(
2402 p_phone_objs IN HZ_PHONE_CP_BO_TBL,
2403 p_email_objs IN HZ_EMAIL_CP_BO_TBL,
2404 p_telex_objs IN HZ_TELEX_CP_BO_TBL,
2405 p_web_objs IN HZ_WEB_CP_BO_TBL,
2406 p_edi_objs IN HZ_EDI_CP_BO_TBL,
2407 p_eft_objs IN HZ_EFT_CP_BO_TBL,
2408 p_sms_objs IN HZ_SMS_CP_BO_TBL,
2409 p_bus_object IN COMPLETENESS_REC_TYPE
2410 ) RETURN BOOLEAN IS
2411
2412 l_phone_cpref BOOLEAN;
2413 l_email_cpref BOOLEAN;
2414 l_telex_cpref BOOLEAN;
2415 l_web_cpref BOOLEAN;
2416 l_edi_cpref BOOLEAN;
2417 l_eft_cpref BOOLEAN;
2418 l_sms_cpref BOOLEAN;
2419 l_bus_object COMPLETENESS_REC_TYPE;
2420 l_bo_num NUMBER;
2421 BEGIN
2422 -- Contact point only has contact preference entity, use boolean to
2423 -- indicate whether it must be present or not
2424 l_bo_num := 0;
2425 l_phone_cpref := FALSE;
2426 l_email_cpref := FALSE;
2427 l_telex_cpref := FALSE;
2428 l_web_cpref := FALSE;
2429 l_edi_cpref := FALSE;
2430 l_eft_cpref := FALSE;
2431 l_sms_cpref := FALSE;
2432 l_bus_object.business_object_code := boc_tbl();
2433 l_bus_object.child_bo_code := cbc_tbl();
2434 l_bus_object.tca_mandated_flag := tmf_tbl();
2435 l_bus_object.user_mandated_flag := umf_tbl();
2436 l_bus_object.root_node_flag := rnf_tbl();
2437 l_bus_object.entity_name := ent_tbl();
2438
2439 FOR i IN 1..p_bus_object.business_object_code.COUNT LOOP
2440 -- get all entity of contact point, for contact point, the only possible
2441 -- entity is HZ_CONTACT_PREFERENCES
2442 IF(p_bus_object.tca_mandated_flag(i) = 'N' AND
2443 p_bus_object.user_mandated_flag(i) = 'Y' AND
2444 p_bus_object.root_node_flag(i) = 'N') THEN
2445 CASE
2446 WHEN p_bus_object.business_object_code(i) = 'PHONE' THEN
2447 l_phone_cpref := TRUE;
2448 WHEN p_bus_object.business_object_code(i) = 'EMAIL' THEN
2449 l_email_cpref := TRUE;
2450 WHEN p_bus_object.business_object_code(i) = 'TELEX' THEN
2451 l_telex_cpref := TRUE;
2452 WHEN p_bus_object.business_object_code(i) = 'WEB' THEN
2453 l_web_cpref := TRUE;
2454 WHEN p_bus_object.business_object_code(i) = 'EDI' THEN
2455 l_edi_cpref := TRUE;
2456 WHEN p_bus_object.business_object_code(i) = 'EFT' THEN
2457 l_eft_cpref := TRUE;
2458 WHEN p_bus_object.business_object_code(i) = 'SMS' THEN
2459 l_sms_cpref := TRUE;
2460 ELSE
2461 null;
2462 END CASE;
2463 ELSIF(p_bus_object.tca_mandated_flag(i) = 'Y' AND
2464 p_bus_object.user_mandated_flag(i) = 'Y' AND
2465 p_bus_object.root_node_flag(i) = 'Y') THEN
2466 l_bo_num := l_bo_num + 1;
2467 l_bus_object.business_object_code.EXTEND;
2468 l_bus_object.child_bo_code.EXTEND;
2469 l_bus_object.tca_mandated_flag.EXTEND;
2470 l_bus_object.user_mandated_flag.EXTEND;
2471 l_bus_object.root_node_flag.EXTEND;
2472 l_bus_object.entity_name.EXTEND;
2473 l_bus_object.business_object_code(l_bo_num) := p_bus_object.business_object_code(i);
2474 l_bus_object.child_bo_code(l_bo_num) := p_bus_object.child_bo_code(i);
2475 l_bus_object.tca_mandated_flag(l_bo_num) := p_bus_object.tca_mandated_flag(i);
2476 l_bus_object.user_mandated_flag(l_bo_num) := p_bus_object.user_mandated_flag(i);
2477 l_bus_object.root_node_flag(l_bo_num) := p_bus_object.root_node_flag(i);
2478 l_bus_object.entity_name(l_bo_num) := p_bus_object.entity_name(i);
2479 END IF;
2480 END LOOP;
2481
2482 -- loop through l_bus_object to find out which contact point must be present
2483 FOR i IN 1..l_bo_num LOOP
2484 CASE
2485 WHEN l_bus_object.business_object_code(i) = 'PHONE' THEN
2486 IF(p_phone_objs IS NULL OR p_phone_objs.COUNT < 1) THEN
2487 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_OBJ');
2488 fnd_message.set_token('OBJECT' ,'PHONE');
2489 fnd_msg_pub.add;
2490 RETURN FALSE;
2491 ELSE
2492 FOR j IN 1..p_phone_objs.COUNT LOOP
2493 IF(l_phone_cpref AND
2494 (p_phone_objs(j).contact_pref_objs IS NULL OR
2495 p_phone_objs(j).contact_pref_objs.COUNT < 1)) THEN
2496 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2497 fnd_message.set_token('ENTITY' ,'PHONE-CONTACT_PREFERENCE');
2498 fnd_msg_pub.add;
2499 RETURN FALSE;
2500 END IF;
2501 END LOOP;
2502 END IF;
2503 WHEN l_bus_object.business_object_code(i) = 'EMAIL' THEN
2504 IF(p_email_objs IS NULL OR p_email_objs.COUNT < 1) THEN
2505 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_OBJ');
2506 fnd_message.set_token('OBJECT' ,'EMAIL');
2507 fnd_msg_pub.add;
2508 RETURN FALSE;
2509 ELSE
2510 FOR j IN 1..p_email_objs.COUNT LOOP
2511 IF(l_email_cpref AND
2512 (p_email_objs(j).contact_pref_objs IS NULL OR
2513 p_email_objs(j).contact_pref_objs.COUNT < 1)) THEN
2514 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2515 fnd_message.set_token('ENTITY' ,'EMAIL-CONTACT_PREFERENCE');
2516 fnd_msg_pub.add;
2517 RETURN FALSE;
2518 END IF;
2519 END LOOP;
2520 END IF;
2521 WHEN l_bus_object.business_object_code(i) = 'TLX' THEN
2522 IF(p_telex_objs IS NULL OR p_telex_objs.COUNT < 1) THEN
2523 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_OBJ');
2524 fnd_message.set_token('OBJECT' ,'TELEX');
2525 fnd_msg_pub.add;
2526 RETURN FALSE;
2527 ELSE
2528 FOR j IN 1..p_telex_objs.COUNT LOOP
2529 IF(l_telex_cpref AND
2530 (p_telex_objs(j).contact_pref_objs IS NULL OR
2531 p_telex_objs(j).contact_pref_objs.COUNT < 1)) THEN
2532 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2533 fnd_message.set_token('ENTITY' ,'TLX-CONTACT_PREFERENCE');
2534 fnd_msg_pub.add;
2535 RETURN FALSE;
2536 END IF;
2537 END LOOP;
2538 END IF;
2539 WHEN l_bus_object.business_object_code(i) = 'WEB' THEN
2540 IF(p_web_objs IS NULL OR p_web_objs.COUNT < 1) THEN
2541 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_OBJ');
2542 fnd_message.set_token('OBJECT' ,'WEB');
2543 fnd_msg_pub.add;
2544 RETURN FALSE;
2545 ELSE
2546 FOR j IN 1..p_web_objs.COUNT LOOP
2547 IF(l_web_cpref AND
2548 (p_web_objs(j).contact_pref_objs IS NULL OR
2549 p_web_objs(j).contact_pref_objs.COUNT < 1)) THEN
2550 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2551 fnd_message.set_token('ENTITY' ,'WEB-CONTACT_PREFERENCE');
2552 fnd_msg_pub.add;
2553 RETURN FALSE;
2554 END IF;
2555 END LOOP;
2556 END IF;
2557 WHEN l_bus_object.business_object_code(i) = 'EDI' THEN
2558 IF(p_edi_objs IS NULL OR p_edi_objs.COUNT < 1) THEN
2559 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_OBJ');
2560 fnd_message.set_token('OBJECT' ,'EDI');
2561 fnd_msg_pub.add;
2562 RETURN FALSE;
2563 ELSE
2564 FOR j IN 1..p_edi_objs.COUNT LOOP
2565 IF(l_edi_cpref AND
2566 (p_edi_objs(j).contact_pref_objs IS NULL OR
2567 p_edi_objs(j).contact_pref_objs.COUNT < 1)) THEN
2568 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2569 fnd_message.set_token('ENTITY' ,'EDI-CONTACT_PREFERENCE');
2570 fnd_msg_pub.add;
2571 RETURN FALSE;
2572 END IF;
2573 END LOOP;
2574 END IF;
2575 WHEN l_bus_object.business_object_code(i) = 'EFT' THEN
2576 IF(p_eft_objs IS NULL OR p_eft_objs.COUNT < 1) THEN
2577 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_OBJ');
2578 fnd_message.set_token('OBJECT' ,'EFT');
2579 fnd_msg_pub.add;
2580 RETURN FALSE;
2581 ELSE
2582 FOR j IN 1..p_eft_objs.COUNT LOOP
2583 IF(l_eft_cpref AND
2584 (p_eft_objs(j).contact_pref_objs IS NULL OR
2585 p_eft_objs(j).contact_pref_objs.COUNT < 1)) THEN
2586 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2587 fnd_message.set_token('ENTITY' ,'EFT-CONTACT_PREFERENCE');
2588 fnd_msg_pub.add;
2589 RETURN FALSE;
2590 END IF;
2591 END LOOP;
2592 END IF;
2593 WHEN l_bus_object.business_object_code(i) = 'SMS' THEN
2594 IF(p_sms_objs IS NULL OR p_sms_objs.COUNT < 1) THEN
2595 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_OBJ');
2596 fnd_message.set_token('OBJECT' ,'SMS');
2597 fnd_msg_pub.add;
2598 RETURN FALSE;
2599 ELSE
2600 FOR j IN 1..p_sms_objs.COUNT LOOP
2601 IF(l_sms_cpref AND
2602 (p_sms_objs(j).contact_pref_objs IS NULL OR
2603 p_sms_objs(j).contact_pref_objs.COUNT < 1)) THEN
2604 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2605 fnd_message.set_token('ENTITY' ,'SMS-CONTACT_PREFERENCE');
2606 fnd_msg_pub.add;
2607 RETURN FALSE;
2608 END IF;
2609 END LOOP;
2610 END IF;
2611 ELSE
2612 null;
2613 END CASE;
2614 END LOOP;
2615
2616 RETURN TRUE;
2617 END is_cp_bo_comp;
2618
2619 -- FUNCTION is_ps_bo_comp
2620 --
2621 -- DESCRIPTION
2622 -- Return true if party site object is complete. Otherwise, return false.
2623 --
2624 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2625 --
2626 -- ARGUMENTS
2627 -- IN:
2628 -- p_ps_objs List of party site business objects.
2629 -- p_bus_object Business object structure for party site.
2630 -- NOTES
2631 --
2632 -- MODIFICATION HISTORY
2633 --
2634 -- 13-Jul-2005 Arnold Ng o Created.
2635
2636 FUNCTION is_ps_bo_comp(
2637 p_ps_objs IN HZ_PARTY_SITE_BO_TBL,
2638 p_bus_object IN COMPLETENESS_REC_TYPE
2639 ) RETURN BOOLEAN IS
2640
2641 l_bus_object COMPLETENESS_REC_TYPE;
2642 l_cp_bus_object COMPLETENESS_REC_TYPE;
2643 l_valid_obj BOOLEAN;
2644 l_psu BOOLEAN;
2645 l_cpref BOOLEAN;
2646 l_ps_ext BOOLEAN;
2647 l_loc_ext BOOLEAN;
2648 l_phone_code VARCHAR2(30);
2649 l_telex_code VARCHAR2(30);
2650 l_email_code VARCHAR2(30);
2651 l_web_code VARCHAR2(30);
2652 BEGIN
2653 l_psu := FALSE;
2654 l_cpref := FALSE;
2655 l_ps_ext := FALSE;
2656 l_loc_ext := FALSE;
2657 l_phone_code := NULL;
2658 l_telex_code := NULL;
2659 l_email_code := NULL;
2660 l_web_code := NULL;
2661
2662 IF(p_ps_objs IS NULL OR p_ps_objs.COUNT < 1) THEN
2663 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_OBJ');
2664 fnd_message.set_token('OBJECT' ,'PARTY_SITE');
2665 fnd_msg_pub.add;
2666 RETURN FALSE;
2667 END IF;
2668
2669 FOR i IN 1..p_bus_object.business_object_code.COUNT LOOP
2670 -- get all entities of party site, for party site, the only possible
2671 -- entites are HZ_PARTY_SITE_USES, HZ_CONTACT_PREFERENCES
2672 IF(p_bus_object.tca_mandated_flag(i) = 'N' AND
2673 p_bus_object.user_mandated_flag(i) = 'Y' AND
2674 p_bus_object.business_object_code(i) = 'PARTY_SITE' AND
2675 p_bus_object.child_bo_code(i) IS NULL) THEN
2676 CASE
2677 WHEN p_bus_object.entity_name(i) = 'HZ_PARTY_SITE_USES' THEN
2678 l_psu := TRUE;
2679 WHEN p_bus_object.entity_name(i) = 'HZ_CONTACT_PREFERENCES' THEN
2680 l_cpref := TRUE;
2681 WHEN p_bus_object.entity_name(i) = 'HZ_PARTY_SITES_EXT_VL' THEN
2682 l_ps_ext := TRUE;
2683 END CASE;
2684 -- Get contact point business object
2685 ELSIF(p_bus_object.child_bo_code(i) IS NOT NULL AND
2686 p_bus_object.user_mandated_flag(i) = 'Y') THEN
2687 CASE
2688 WHEN p_bus_object.child_bo_code(i) = 'PHONE' THEN
2689 l_phone_code := 'PHONE';
2690 WHEN p_bus_object.child_bo_code(i) = 'TLX' THEN
2691 l_telex_code := 'TLX';
2692 WHEN p_bus_object.child_bo_code(i) = 'EMAIL' THEN
2693 l_email_code := 'EMAIL';
2694 WHEN p_bus_object.child_bo_code(i) = 'WEB' THEN
2695 l_web_code := 'WEB';
2696 ELSE
2697 null;
2698 END CASE;
2699 -- Get location object
2700 ELSIF(p_bus_object.business_object_code(i) = 'LOCATION' AND
2701 p_bus_object.user_mandated_flag(i) = 'Y' AND
2702 p_bus_object.tca_mandated_flag(i) = 'N' AND
2703 p_bus_object.child_bo_code(i) IS NULL) THEN
2704 IF p_bus_object.entity_name(i) = 'HZ_LOCATIONS_EXT_VL' THEN
2705 l_loc_ext := TRUE;
2706 END IF;
2707 END IF;
2708 END LOOP;
2709
2710 IF(l_phone_code IS NOT NULL OR l_telex_code IS NOT NULL OR
2711 l_email_code IS NOT NULL OR l_web_code IS NOT NULL) THEN
2712 get_cp_from_rec(
2713 p_phone_code => l_phone_code,
2714 p_email_code => l_email_code,
2715 p_telex_code => l_telex_code,
2716 p_web_code => l_web_code,
2717 p_edi_code => NULL,
2718 p_eft_code => NULL,
2719 p_sms_code => NULL,
2720 p_bus_object => p_bus_object,
2721 x_bus_object => l_cp_bus_object
2722 );
2723 END IF;
2724
2725 FOR i IN 1..p_ps_objs.COUNT LOOP
2726 IF(p_ps_objs(i).location_obj IS NULL) THEN
2727 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_OBJ');
2728 fnd_message.set_token('OBJECT' ,'LOCATION');
2729 fnd_msg_pub.add;
2730 RETURN FALSE;
2731 END IF;
2732 IF(l_psu AND
2733 (p_ps_objs(i).party_site_use_objs IS NULL OR
2734 p_ps_objs(i).party_site_use_objs.COUNT < 1)) THEN
2735 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2736 fnd_message.set_token('OBJECT' ,'PARTY_SITE_USE');
2737 fnd_msg_pub.add;
2738 RETURN FALSE;
2739 END IF;
2740 IF(l_cpref AND
2741 (p_ps_objs(i).contact_pref_objs IS NULL OR
2742 p_ps_objs(i).contact_pref_objs.COUNT < 1)) THEN
2743 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2744 fnd_message.set_token('OBJECT' ,'PARTY_SITE: CONTACT_PREFERENCE');
2745 fnd_msg_pub.add;
2746 RETURN FALSE;
2747 END IF;
2748 IF(l_ps_ext AND
2749 (p_ps_objs(i).ext_attributes_objs IS NULL OR
2750 p_ps_objs(i).ext_attributes_objs.COUNT < 1)) THEN
2751 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2752 fnd_message.set_token('OBJECT' ,'PARTY_SITE: EXTENSIBILITY');
2753 fnd_msg_pub.add;
2754 RETURN FALSE;
2755 END IF;
2756 IF(l_loc_ext AND
2757 (p_ps_objs(i).location_obj.ext_attributes_objs IS NULL OR
2758 p_ps_objs(i).location_obj.ext_attributes_objs.COUNT < 1)) THEN
2759 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2760 fnd_message.set_token('OBJECT' ,'LOCATION: EXTENSIBILITY');
2761 fnd_msg_pub.add;
2762 RETURN FALSE;
2763 END IF;
2764 IF(l_phone_code IS NOT NULL OR l_telex_code IS NOT NULL OR
2765 l_email_code IS NOT NULL OR l_web_code IS NOT NULL) THEN
2766 -- check contact point business object for party site
2767 l_valid_obj := is_cp_bo_comp(
2768 p_phone_objs => p_ps_objs(i).phone_objs,
2769 p_email_objs => p_ps_objs(i).email_objs,
2770 p_telex_objs => p_ps_objs(i).telex_objs,
2771 p_web_objs => p_ps_objs(i).web_objs,
2772 p_edi_objs => NULL,
2773 p_eft_objs => NULL,
2774 p_sms_objs => NULL,
2775 p_bus_object => l_cp_bus_object
2776 );
2777 IF NOT(l_valid_obj) THEN
2778 RETURN FALSE;
2779 END IF;
2780 END IF;
2781 END LOOP;
2782
2783 RETURN TRUE;
2784 END is_ps_bo_comp;
2785
2786 -- FUNCTION is_person_bo_comp
2787 --
2788 -- DESCRIPTION
2789 -- Return true if person object is complete. Otherwise, return false.
2790 --
2791 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2792 --
2793 -- ARGUMENTS
2794 -- IN:
2795 -- p_person_obj Person business objects.
2796 -- p_bus_object Business object structure for person.
2797 -- NOTES
2798 --
2799 -- MODIFICATION HISTORY
2800 --
2801 -- 13-Jul-2005 Arnold Ng o Created.
2802
2803 FUNCTION is_person_bo_comp(
2804 p_person_obj IN HZ_PERSON_BO,
2805 p_bus_object IN COMPLETENESS_REC_TYPE
2806 ) RETURN BOOLEAN IS
2807 l_bus_object COMPLETENESS_REC_TYPE;
2808 l_cp_bus_object COMPLETENESS_REC_TYPE;
2809 l_ps_bus_object COMPLETENESS_REC_TYPE;
2810 l_valid_obj BOOLEAN;
2811
2812 l_pref BOOLEAN;
2813 l_rel BOOLEAN;
2814 l_class BOOLEAN;
2815 l_lang BOOLEAN;
2816 l_edu BOOLEAN;
2817 l_citiz BOOLEAN;
2818 l_emp_hist BOOLEAN;
2819 l_work_class BOOLEAN;
2820 l_int BOOLEAN;
2821 l_cert BOOLEAN;
2822 l_fin_prof BOOLEAN;
2823 l_cpref BOOLEAN;
2824 l_ps BOOLEAN;
2825 l_ext BOOLEAN;
2826
2827 l_phone_code VARCHAR2(30);
2828 l_email_code VARCHAR2(30);
2829 l_web_code VARCHAR2(30);
2830 l_sms_code VARCHAR2(30);
2831 BEGIN
2832 l_pref := FALSE;
2833 l_rel := FALSE;
2834 l_class := FALSE;
2835 l_lang := FALSE;
2836 l_edu := FALSE;
2837 l_citiz := FALSE;
2838 l_emp_hist := FALSE;
2839 l_work_class := FALSE;
2840 l_int := FALSE;
2841 l_cert := FALSE;
2842 l_fin_prof := FALSE;
2843 l_cpref := FALSE;
2844 l_ps := FALSE;
2845 l_ext := FALSE;
2846
2847 FOR i IN 1..p_bus_object.business_object_code.COUNT LOOP
2848 -- get all entities of person, for person, the possible entites are
2849 -- HZ_PARTY_PREFERENCES, HZ_RELATIONSHIPS, HZ_CODE_ASSIGNMENTS,
2850 -- HZ_PERSON_LANGUAGE, HZ_EDUCATION, HZ_CITIZENSHIP, HZ_EMPLOYMENT_HISTORY
2851 -- HZ_PERSON_INTEREST, HZ_CERTIFICATIONS, HZ_FINANCIAL_PROFILE,
2852 -- HZ_CONTACT_PREFERENCES
2853 IF(p_bus_object.tca_mandated_flag(i) = 'N' AND
2854 p_bus_object.user_mandated_flag(i) = 'Y' AND
2855 p_bus_object.business_object_code(i) = 'PERSON' AND
2856 p_bus_object.child_bo_code(i) IS NULL) THEN
2857 CASE
2858 WHEN p_bus_object.entity_name(i) = 'HZ_PARTY_PREFERENCES' THEN
2859 l_pref := TRUE;
2860 WHEN p_bus_object.entity_name(i) = 'HZ_RELATIONSHIPS' THEN
2861 l_rel := TRUE;
2862 WHEN p_bus_object.entity_name(i) = 'HZ_CODE_ASSIGNMENTS' THEN
2863 l_class := TRUE;
2864 WHEN p_bus_object.entity_name(i) = 'HZ_PERSON_LANGUAGE' THEN
2865 l_lang := TRUE;
2866 WHEN p_bus_object.entity_name(i) = 'HZ_EDUCATION' THEN
2867 l_edu := TRUE;
2868 WHEN p_bus_object.entity_name(i) = 'HZ_CITIZENSHIP' THEN
2869 l_citiz := TRUE;
2870 WHEN p_bus_object.entity_name(i) = 'HZ_PERSON_INTEREST' THEN
2871 l_int := TRUE;
2872 WHEN p_bus_object.entity_name(i) = 'HZ_CERTIFICATIONS' THEN
2873 l_cert := TRUE;
2874 WHEN p_bus_object.entity_name(i) = 'HZ_FINANCIAL_PROFILE' THEN
2875 l_fin_prof := TRUE;
2876 WHEN p_bus_object.entity_name(i) = 'HZ_CONTACT_PREFERENCES' THEN
2877 l_cpref := TRUE;
2878 WHEN p_bus_object.entity_name(i) = 'HZ_PER_PROFILES_EXT_VL' THEN
2879 l_ext := TRUE;
2880 END CASE;
2881 ELSIF(p_bus_object.child_bo_code(i) IS NOT NULL AND
2882 p_bus_object.business_object_code(i) = 'PERSON' AND
2883 p_bus_object.user_mandated_flag(i) = 'Y') THEN
2884 CASE
2885 WHEN p_bus_object.child_bo_code(i) = 'PHONE' THEN
2886 l_phone_code := 'PHONE';
2887 WHEN p_bus_object.child_bo_code(i) = 'EMAIL' THEN
2888 l_email_code := 'EMAIL';
2889 WHEN p_bus_object.child_bo_code(i) = 'WEB' THEN
2890 l_web_code := 'WEB';
2891 WHEN p_bus_object.child_bo_code(i) = 'SMS' THEN
2892 l_sms_code := 'SMS';
2893 WHEN p_bus_object.child_bo_code(i) = 'PARTY_SITE' THEN
2894 l_ps := TRUE;
2895 WHEN p_bus_object.child_bo_code(i) = 'EMP_HIST' THEN
2896 l_emp_hist := TRUE;
2897 END CASE;
2898 ELSIF(p_bus_object.business_object_code(i) = 'EMP_HIST' AND
2899 p_bus_object.entity_name(i) = 'HZ_WORK_CLASS' AND
2900 p_bus_object.user_mandated_flag(i) = 'Y') THEN
2901 l_work_class := TRUE;
2902 END IF;
2903 END LOOP;
2904
2905 IF(l_phone_code IS NOT NULL OR l_email_code IS NOT NULL OR
2906 l_web_code IS NOT NULL OR l_sms_code IS NOT NULL) THEN
2907 get_cp_from_rec(
2908 p_phone_code => l_phone_code,
2909 p_email_code => l_email_code,
2910 p_telex_code => NULL,
2911 p_web_code => l_web_code,
2912 p_edi_code => NULL,
2913 p_eft_code => NULL,
2914 p_sms_code => l_sms_code,
2915 p_bus_object => p_bus_object,
2916 x_bus_object => l_cp_bus_object
2917 );
2918 END IF;
2919
2920 IF(l_ps) THEN
2921 get_ps_from_rec(
2922 p_bus_object => p_bus_object,
2923 x_bus_object => l_ps_bus_object
2924 );
2925 END IF;
2926
2927 IF(l_pref AND
2928 (p_person_obj.preference_objs IS NULL OR
2929 p_person_obj.preference_objs.COUNT < 1)) THEN
2930 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2931 fnd_message.set_token('OBJECT' ,'PARTY_PREFERENCE');
2932 fnd_msg_pub.add;
2933 RETURN FALSE;
2934 END IF;
2935 IF(l_class AND
2936 (p_person_obj.class_objs IS NULL OR
2937 p_person_obj.class_objs.COUNT < 1)) THEN
2938 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2939 fnd_message.set_token('OBJECT' ,'CLASSIFICATION');
2940 fnd_msg_pub.add;
2941 RETURN FALSE;
2942 END IF;
2943 IF(l_lang AND
2944 (p_person_obj.language_objs IS NULL OR
2945 p_person_obj.language_objs.COUNT < 1)) THEN
2946 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2947 fnd_message.set_token('OBJECT' ,'LANGUAGE');
2948 fnd_msg_pub.add;
2949 RETURN FALSE;
2950 END IF;
2951 IF(l_edu AND
2952 (p_person_obj.education_objs IS NULL OR
2953 p_person_obj.education_objs.COUNT < 1)) THEN
2954 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2955 fnd_message.set_token('OBJECT' ,'EDUCATION');
2956 fnd_msg_pub.add;
2957 RETURN FALSE;
2958 END IF;
2959 IF(l_citiz AND
2960 (p_person_obj.citizenship_objs IS NULL OR
2961 p_person_obj.citizenship_objs.COUNT < 1)) THEN
2962 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2963 fnd_message.set_token('OBJECT' ,'CITIZENSHIP');
2964 fnd_msg_pub.add;
2965 RETURN FALSE;
2966 END IF;
2967 IF(l_emp_hist AND
2968 (p_person_obj.employ_hist_objs IS NULL OR
2969 p_person_obj.employ_hist_objs.COUNT < 1)) THEN
2970 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2971 fnd_message.set_token('OBJECT' ,'EMPLOYMENT_HISTORY');
2972 fnd_msg_pub.add;
2973 RETURN FALSE;
2974 FOR j IN 1..p_person_obj.employ_hist_objs.COUNT LOOP
2975 IF(l_work_class AND
2976 (p_person_obj.employ_hist_objs(j).work_class_objs IS NULL OR
2977 p_person_obj.employ_hist_objs(j).work_class_objs.COUNT < 1)) THEN
2978 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2979 fnd_message.set_token('OBJECT' ,'WORK_CLASS');
2980 fnd_msg_pub.add;
2981 RETURN FALSE;
2982 END IF;
2983 END LOOP;
2984 END IF;
2985 IF(l_int AND
2986 (p_person_obj.interest_objs IS NULL OR
2987 p_person_obj.interest_objs.COUNT < 1)) THEN
2988 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2989 fnd_message.set_token('OBJECT' ,'PERSON_INTEREST');
2990 fnd_msg_pub.add;
2991 RETURN FALSE;
2992 END IF;
2993 IF(l_cert AND
2994 (p_person_obj.certification_objs IS NULL OR
2995 p_person_obj.certification_objs.COUNT < 1)) THEN
2996 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
2997 fnd_message.set_token('OBJECT' ,'CERTIFICATION');
2998 fnd_msg_pub.add;
2999 RETURN FALSE;
3000 END IF;
3001 IF(l_fin_prof AND
3002 (p_person_obj.financial_prof_objs IS NULL OR
3003 p_person_obj.financial_prof_objs.COUNT < 1)) THEN
3004 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3005 fnd_message.set_token('OBJECT' ,'FINANCIAL_PROFILE');
3006 fnd_msg_pub.add;
3007 RETURN FALSE;
3008 END IF;
3009 IF(l_rel AND
3010 (p_person_obj.relationship_objs IS NULL OR
3011 p_person_obj.relationship_objs.COUNT < 1)) THEN
3012 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3013 fnd_message.set_token('OBJECT' ,'RELATIONSHIP');
3014 fnd_msg_pub.add;
3015 RETURN FALSE;
3016 END IF;
3017 IF(l_cpref AND
3018 (p_person_obj.contact_pref_objs IS NULL OR
3019 p_person_obj.contact_pref_objs.COUNT < 1)) THEN
3020 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3021 fnd_message.set_token('OBJECT' ,'PERSON: CONTACT_PREFERENCE');
3022 fnd_msg_pub.add;
3023 RETURN FALSE;
3024 END IF;
3025 IF(l_ext AND
3026 (p_person_obj.ext_attributes_objs IS NULL OR
3027 p_person_obj.ext_attributes_objs.COUNT < 1)) THEN
3028 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3029 fnd_message.set_token('OBJECT' ,'PERSON: EXTENSIBILITY');
3030 fnd_msg_pub.add;
3031 RETURN FALSE;
3032 END IF;
3033 IF(l_phone_code IS NOT NULL OR l_email_code IS NOT NULL OR
3034 l_web_code IS NOT NULL OR l_sms_code IS NOT NULL) THEN
3035 -- check contact point business object for person
3036 l_valid_obj := is_cp_bo_comp(
3037 p_phone_objs => p_person_obj.phone_objs,
3038 p_email_objs => p_person_obj.email_objs,
3039 p_telex_objs => NULL,
3040 p_web_objs => p_person_obj.web_objs,
3041 p_edi_objs => NULL,
3042 p_eft_objs => NULL,
3043 p_sms_objs => p_person_obj.sms_objs,
3044 p_bus_object => l_cp_bus_object
3045 );
3046 IF NOT(l_valid_obj) THEN
3047 RETURN FALSE;
3048 END IF;
3049 END IF;
3050 IF(l_ps) THEN
3051 -- check contact point business object for person
3052 l_valid_obj := is_ps_bo_comp(
3053 p_ps_objs => p_person_obj.party_site_objs,
3054 p_bus_object => l_ps_bus_object
3055 );
3056 IF NOT(l_valid_obj) THEN
3057 RETURN FALSE;
3058 END IF;
3059 END IF;
3060
3061 RETURN TRUE;
3062 END is_person_bo_comp;
3063
3064 -- FUNCTION is_oc_bo_comp
3065 --
3066 -- DESCRIPTION
3067 -- Return true if org contact object is complete. Otherwise, return false.
3068 --
3069 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3070 --
3071 -- ARGUMENTS
3072 -- IN:
3073 -- p_oc_objs List of organization contact business objects.
3074 -- p_bus_object Business object structure for organization contact.
3075 -- NOTES
3076 --
3077 -- MODIFICATION HISTORY
3078 --
3079 -- 13-Jul-2005 Arnold Ng o Created.
3080
3081 FUNCTION is_oc_bo_comp(
3082 p_oc_objs IN HZ_ORG_CONTACT_BO_TBL,
3083 p_bus_object IN COMPLETENESS_REC_TYPE
3084 ) RETURN BOOLEAN IS
3085 l_bus_object COMPLETENESS_REC_TYPE;
3086 l_cp_bus_object COMPLETENESS_REC_TYPE;
3087 l_ps_bus_object COMPLETENESS_REC_TYPE;
3088 l_per_bus_object COMPLETENESS_REC_TYPE;
3089 l_valid_obj BOOLEAN;
3090
3091 l_ocr BOOLEAN;
3092 l_cpref BOOLEAN;
3093 l_ps BOOLEAN;
3094
3095 l_phone_code VARCHAR2(30);
3096 l_telex_code VARCHAR2(30);
3097 l_email_code VARCHAR2(30);
3098 l_web_code VARCHAR2(30);
3099 l_sms_code VARCHAR2(30);
3100 BEGIN
3101 -- For org contact, person bo is a must and person only has one
3102 -- object, not a list of object
3103 l_ocr := FALSE;
3104 l_cpref := FALSE;
3105 l_ps := FALSE;
3106
3107 IF(p_oc_objs IS NULL OR p_oc_objs.COUNT < 1) THEN
3108 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_OBJ');
3109 fnd_message.set_token('OBJECT' ,'ORG_CONTACT');
3110 fnd_msg_pub.add;
3111 RETURN FALSE;
3112 END IF;
3113
3114 FOR i IN 1..p_bus_object.business_object_code.COUNT LOOP
3115 -- get all entities of org contact, for org contact, the possible entites are
3116 -- HZ_ORG_CONTACT_ROLES
3117 IF(p_bus_object.tca_mandated_flag(i) = 'N' AND
3118 p_bus_object.user_mandated_flag(i) = 'Y' AND
3119 p_bus_object.business_object_code(i) = 'ORG_CONTACT' AND
3120 p_bus_object.child_bo_code(i) IS NULL) THEN
3121 IF(p_bus_object.entity_name(i) = 'HZ_ORG_CONTACT_ROLES') THEN
3122 l_ocr := TRUE;
3123 ELSIF(p_bus_object.entity_name(i) = 'HZ_CONTACT_PREFERENCES') THEN
3124 l_cpref := TRUE;
3125 END IF;
3126 -- Get contact point business object
3127 ELSIF(p_bus_object.child_bo_code(i) IS NOT NULL AND
3128 p_bus_object.business_object_code(i) = 'ORG_CONTACT' AND
3129 p_bus_object.user_mandated_flag(i) = 'Y') THEN
3130 CASE
3131 WHEN p_bus_object.child_bo_code(i) = 'PHONE' THEN
3132 l_phone_code := 'PHONE';
3133 WHEN p_bus_object.child_bo_code(i) = 'TLX' THEN
3134 l_telex_code := 'TLX';
3135 WHEN p_bus_object.child_bo_code(i) = 'EMAIL' THEN
3136 l_email_code := 'EMAIL';
3137 WHEN p_bus_object.child_bo_code(i) = 'WEB' THEN
3138 l_web_code := 'WEB';
3139 WHEN p_bus_object.child_bo_code(i) = 'SMS' THEN
3140 l_sms_code := 'SMS';
3141 WHEN p_bus_object.child_bo_code(i) = 'PARTY_SITE' THEN
3142 l_ps := TRUE;
3143 ELSE
3144 null;
3145 END CASE;
3146 END IF;
3147 END LOOP;
3148
3149 IF(l_phone_code IS NOT NULL OR l_telex_code IS NOT NULL OR
3150 l_email_code IS NOT NULL OR l_web_code IS NOT NULL OR
3151 l_sms_code IS NOT NULL) THEN
3152 get_cp_from_rec(
3153 p_phone_code => l_phone_code,
3154 p_email_code => l_email_code,
3155 p_telex_code => l_telex_code,
3156 p_web_code => l_web_code,
3157 p_edi_code => NULL,
3158 p_eft_code => NULL,
3159 p_sms_code => l_sms_code,
3160 p_bus_object => p_bus_object,
3161 x_bus_object => l_cp_bus_object
3162 );
3163 END IF;
3164
3165 IF(l_ps) THEN
3166 get_ps_from_rec(
3167 p_bus_object => p_bus_object,
3168 x_bus_object => l_ps_bus_object
3169 );
3170 END IF;
3171
3172 FOR i IN 1..p_oc_objs.COUNT LOOP
3173 IF(p_oc_objs(i).person_profile_obj IS NULL) THEN
3174 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3175 fnd_message.set_token('OBJECT' ,'PERSON_CONTACT');
3176 fnd_msg_pub.add;
3177 RETURN FALSE;
3178 END IF;
3179
3180 IF(l_ocr AND
3181 (p_oc_objs(i).org_contact_role_objs IS NULL OR
3182 p_oc_objs(i).org_contact_role_objs.COUNT < 1)) THEN
3183 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3184 fnd_message.set_token('OBJECT' ,'ORG_CONTACT_ROLE');
3185 fnd_msg_pub.add;
3186 RETURN FALSE;
3187 END IF;
3188 IF(l_cpref AND
3189 (p_oc_objs(i).contact_pref_objs IS NULL OR
3190 p_oc_objs(i).contact_pref_objs.COUNT < 1)) THEN
3191 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3192 fnd_message.set_token('OBJECT' ,'ORG_CONTACT: CONTACT_PREFERENCE');
3193 fnd_msg_pub.add;
3194 RETURN FALSE;
3195 END IF;
3196
3197 IF(l_phone_code IS NOT NULL OR l_telex_code IS NOT NULL OR
3198 l_email_code IS NOT NULL OR l_web_code IS NOT NULL OR
3199 l_sms_code IS NOT NULL) THEN
3200 -- check contact point business object for org contact
3201 l_valid_obj := is_cp_bo_comp(
3202 p_phone_objs => p_oc_objs(i).phone_objs,
3203 p_email_objs => p_oc_objs(i).email_objs,
3204 p_telex_objs => p_oc_objs(i).telex_objs,
3205 p_web_objs => p_oc_objs(i).web_objs,
3206 p_edi_objs => NULL,
3207 p_eft_objs => NULL,
3208 p_sms_objs => p_oc_objs(i).sms_objs,
3209 p_bus_object => l_cp_bus_object
3210 );
3211 IF NOT(l_valid_obj) THEN
3212 RETURN FALSE;
3213 END IF;
3214 END IF;
3215 IF(l_ps) THEN
3216 -- check contact point business object for org contact
3217 l_valid_obj := is_ps_bo_comp(
3218 p_ps_objs => p_oc_objs(i).party_site_objs,
3219 p_bus_object => l_ps_bus_object
3220 );
3221 IF NOT(l_valid_obj) THEN
3222 RETURN FALSE;
3223 END IF;
3224 END IF;
3225 END LOOP;
3226
3227 RETURN TRUE;
3228 END is_oc_bo_comp;
3229
3230 -- FUNCTION is_org_bo_comp
3231 --
3232 -- DESCRIPTION
3233 -- Return true if organization object is complete. Otherwise, return false.
3234 --
3235 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3236 --
3237 -- ARGUMENTS
3238 -- IN:
3239 -- p_organization_obj Organization business objects.
3240 -- p_bus_object Business object structure for organization.
3241 -- NOTES
3242 --
3243 -- MODIFICATION HISTORY
3244 --
3245 -- 13-Jul-2005 Arnold Ng o Created.
3246
3247 FUNCTION is_org_bo_comp(
3248 p_organization_obj IN HZ_ORGANIZATION_BO,
3249 p_bus_object IN COMPLETENESS_REC_TYPE
3250 ) RETURN BOOLEAN IS
3251 l_bus_object COMPLETENESS_REC_TYPE;
3252 l_cp_bus_object COMPLETENESS_REC_TYPE;
3253 l_ps_bus_object COMPLETENESS_REC_TYPE;
3254 l_oc_bus_object COMPLETENESS_REC_TYPE;
3255 l_valid_obj BOOLEAN;
3256
3257 l_pref BOOLEAN;
3258 l_credit BOOLEAN;
3259 l_rel BOOLEAN;
3260 l_class BOOLEAN;
3261 l_fin_report BOOLEAN;
3262 l_cpref BOOLEAN;
3263 l_cert BOOLEAN;
3264 l_fin_prof BOOLEAN;
3265 l_ps BOOLEAN;
3266 l_oc BOOLEAN;
3267 l_ext BOOLEAN;
3268
3269 l_phone_code VARCHAR2(30);
3270 l_telex_code VARCHAR2(30);
3271 l_email_code VARCHAR2(30);
3272 l_web_code VARCHAR2(30);
3273 l_edi_code VARCHAR2(30);
3274 l_eft_code VARCHAR2(30);
3275 BEGIN
3276 l_pref := FALSE;
3277 l_credit := FALSE;
3278 l_rel := FALSE;
3279 l_class := FALSE;
3280 l_fin_report := FALSE;
3281 l_cpref := FALSE;
3282 l_cert := FALSE;
3283 l_fin_prof := FALSE;
3284 l_ps := FALSE;
3285 l_oc := FALSE;
3286 l_ext := FALSE;
3287
3288 FOR i IN 1..p_bus_object.business_object_code.COUNT LOOP
3289 -- get all entities of org, for org, the possible entites are
3290 -- HZ_PARTY_PREFERENCES, HZ_CREDIT_RATINGS, HZ_RELATIONSHIPS,
3291 -- HZ_CODE_ASSIGNMENTS, HZ_FINANCIAL_REPORTS, HZ_FINANCIAL_NUMBERS,
3292 -- HZ_CERTIFICATIONS, HZ_FINANCIAL_PROFILE, HZ_CONTACT_PREFERENCES
3293 IF(p_bus_object.tca_mandated_flag(i) = 'N' AND
3294 p_bus_object.user_mandated_flag(i) = 'Y' AND
3295 p_bus_object.business_object_code(i) = 'ORG' AND
3296 p_bus_object.child_bo_code(i) IS NULL) THEN
3297 CASE
3298 WHEN p_bus_object.entity_name(i) = 'HZ_PARTY_PREFERENCES' THEN
3299 l_pref := TRUE;
3300 WHEN p_bus_object.entity_name(i) = 'HZ_CREDIT_RATINGS' THEN
3301 l_credit := TRUE;
3302 WHEN p_bus_object.entity_name(i) = 'HZ_RELATIONSHIPS' THEN
3303 l_rel := TRUE;
3304 WHEN p_bus_object.entity_name(i) = 'HZ_CODE_ASSIGNMENTS' THEN
3305 l_class := TRUE;
3306 WHEN p_bus_object.entity_name(i) = 'HZ_CERTIFICATIONS' THEN
3307 l_cert := TRUE;
3308 WHEN p_bus_object.entity_name(i) = 'HZ_FINANCIAL_PROFILE' THEN
3309 l_fin_prof := TRUE;
3310 WHEN p_bus_object.entity_name(i) = 'HZ_CONTACT_PREFERENCES' THEN
3311 l_cpref := TRUE;
3312 WHEN p_bus_object.entity_name(i) = 'HZ_ORG_PROFILES_EXT_VL' THEN
3313 l_ext := TRUE;
3314 END CASE;
3315 ELSIF(p_bus_object.child_bo_code(i) IS NOT NULL AND
3316 p_bus_object.business_object_code(i) = 'ORG' AND
3317 p_bus_object.user_mandated_flag(i) = 'Y') THEN
3318 CASE
3319 WHEN p_bus_object.child_bo_code(i) = 'PHONE' THEN
3320 l_phone_code := 'PHONE';
3321 WHEN p_bus_object.child_bo_code(i) = 'TLX' THEN
3322 l_telex_code := 'TLX';
3323 WHEN p_bus_object.child_bo_code(i) = 'EMAIL' THEN
3324 l_email_code := 'EMAIL';
3325 WHEN p_bus_object.child_bo_code(i) = 'WEB' THEN
3326 l_web_code := 'WEB';
3327 WHEN p_bus_object.child_bo_code(i) = 'EDI' THEN
3328 l_edi_code := 'EDI';
3329 WHEN p_bus_object.child_bo_code(i) = 'EFT' THEN
3330 l_eft_code := 'EFT';
3331 WHEN p_bus_object.child_bo_code(i) = 'PARTY_SITE' THEN
3332 l_ps := TRUE;
3333 WHEN p_bus_object.child_bo_code(i) = 'ORG_CONTACT' THEN
3334 l_oc := TRUE;
3335 WHEN p_bus_object.child_bo_code(i) = 'FIN_REPORT' THEN
3336 l_fin_report := TRUE;
3337 END CASE;
3338 END IF;
3339 END LOOP;
3340
3341 IF(l_phone_code IS NOT NULL OR l_telex_code IS NOT NULL OR
3342 l_email_code IS NOT NULL OR l_web_code IS NOT NULL OR
3343 l_edi_code IS NOT NULL OR l_eft_code IS NOT NULL) THEN
3344 get_cp_from_rec(
3345 p_phone_code => l_phone_code,
3346 p_email_code => l_email_code,
3347 p_telex_code => l_telex_code,
3348 p_web_code => l_web_code,
3349 p_edi_code => l_edi_code,
3350 p_eft_code => l_eft_code,
3351 p_sms_code => NULL,
3352 p_bus_object => p_bus_object,
3353 x_bus_object => l_cp_bus_object
3354 );
3355 END IF;
3356
3357 IF(l_ps) THEN
3358 get_ps_from_rec(
3359 p_bus_object => p_bus_object,
3360 x_bus_object => l_ps_bus_object
3361 );
3362 END IF;
3363
3364 IF(l_oc) THEN
3365 get_bus_obj_struct(
3366 p_bus_object_code => 'ORG_CONTACT',
3367 x_bus_object => l_oc_bus_object
3368 );
3369 END IF;
3370
3371 IF(l_pref AND
3372 (p_organization_obj.preference_objs IS NULL OR
3373 p_organization_obj.preference_objs.COUNT < 1)) THEN
3374 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3375 fnd_message.set_token('OBJECT' ,'PARTY_PREFERENCE');
3376 fnd_msg_pub.add;
3377 RETURN FALSE;
3378 END IF;
3379 IF(l_credit AND
3380 (p_organization_obj.credit_rating_objs IS NULL OR
3381 p_organization_obj.credit_rating_objs.COUNT < 1)) THEN
3382 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3383 fnd_message.set_token('OBJECT' ,'CREDIT_RATING');
3384 fnd_msg_pub.add;
3385 RETURN FALSE;
3386 END IF;
3387 IF(l_rel AND
3388 (p_organization_obj.relationship_objs IS NULL OR
3389 p_organization_obj.relationship_objs.COUNT < 1)) THEN
3390 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3391 fnd_message.set_token('OBJECT' ,'RELATIONSHIP');
3392 fnd_msg_pub.add;
3393 RETURN FALSE;
3394 END IF;
3395 IF(l_class AND
3396 (p_organization_obj.class_objs IS NULL OR
3397 p_organization_obj.class_objs.COUNT < 1)) THEN
3398 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3399 fnd_message.set_token('OBJECT' ,'CLASSIFICATION');
3400 fnd_msg_pub.add;
3401 RETURN FALSE;
3402 END IF;
3403 IF(l_fin_report AND
3404 (p_organization_obj.financial_report_objs IS NULL OR
3405 p_organization_obj.financial_report_objs.COUNT < 1)) THEN
3406 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3407 fnd_message.set_token('OBJECT' ,'FINANCIAL_REPORT');
3408 fnd_msg_pub.add;
3409 RETURN FALSE;
3410
3411 -- financial report always require financial number
3412 FOR j IN 1..p_organization_obj.financial_report_objs.COUNT LOOP
3413 IF(p_organization_obj.financial_report_objs(j).financial_number_objs IS NULL OR
3414 p_organization_obj.financial_report_objs(j).financial_number_objs.COUNT < 1) THEN
3415 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3416 fnd_message.set_token('OBJECT' ,'FINANCIAL_NUMBER');
3417 fnd_msg_pub.add;
3418 RETURN FALSE;
3419 END IF;
3420 END LOOP;
3421 END IF;
3422 IF(l_cert AND
3423 (p_organization_obj.certification_objs IS NULL OR
3424 p_organization_obj.certification_objs.COUNT < 1)) THEN
3425 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3426 fnd_message.set_token('OBJECT' ,'CERTIFICATION');
3427 fnd_msg_pub.add;
3428 RETURN FALSE;
3429 END IF;
3430 IF(l_fin_prof AND
3431 (p_organization_obj.financial_prof_objs IS NULL OR
3432 p_organization_obj.financial_prof_objs.COUNT < 1)) THEN
3433 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3434 fnd_message.set_token('OBJECT' ,'FINANCIAL_PROFILE');
3435 fnd_msg_pub.add;
3436 RETURN FALSE;
3437 END IF;
3438 IF(l_cpref AND
3439 (p_organization_obj.contact_pref_objs IS NULL OR
3440 p_organization_obj.contact_pref_objs.COUNT < 1)) THEN
3441 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3442 fnd_message.set_token('OBJECT' ,'ORG: CONTACT_PREFERENCE');
3443 fnd_msg_pub.add;
3444 RETURN FALSE;
3445 END IF;
3446 IF(l_ext AND
3447 (p_organization_obj.ext_attributes_objs IS NULL OR
3448 p_organization_obj.ext_attributes_objs.COUNT < 1)) THEN
3449 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3450 fnd_message.set_token('OBJECT' ,'ORG: EXTENSIBILITY');
3451 fnd_msg_pub.add;
3452 RETURN FALSE;
3453 END IF;
3454
3455 IF(l_phone_code IS NOT NULL OR l_telex_code IS NOT NULL OR
3456 l_email_code IS NOT NULL OR l_web_code IS NOT NULL OR
3457 l_edi_code IS NOT NULL OR l_eft_code IS NOT NULL) THEN
3458 -- check contact point business object for org
3459 l_valid_obj := is_cp_bo_comp(
3460 p_phone_objs => p_organization_obj.phone_objs,
3461 p_email_objs => p_organization_obj.email_objs,
3462 p_telex_objs => p_organization_obj.telex_objs,
3463 p_web_objs => p_organization_obj.web_objs,
3464 p_edi_objs => p_organization_obj.edi_objs,
3465 p_eft_objs => p_organization_obj.eft_objs,
3466 p_sms_objs => NULL,
3467 p_bus_object => l_cp_bus_object
3468 );
3469 IF NOT(l_valid_obj) THEN
3470 RETURN FALSE;
3471 END IF;
3472 END IF;
3473
3474 IF(l_ps) THEN
3475 -- check party site business object for org
3476 l_valid_obj := is_ps_bo_comp(
3477 p_ps_objs => p_organization_obj.party_site_objs,
3478 p_bus_object => l_ps_bus_object
3479 );
3480 IF NOT(l_valid_obj) THEN
3481 RETURN FALSE;
3482 END IF;
3483 END IF;
3484
3485 IF(l_oc) THEN
3486 -- check org contact business object for org
3487 l_valid_obj := is_oc_bo_comp(
3488 p_oc_objs => p_organization_obj.contact_objs,
3489 p_bus_object => l_oc_bus_object
3490 );
3491 IF NOT(l_valid_obj) THEN
3492 RETURN FALSE;
3493 END IF;
3494 END IF;
3495
3496 RETURN TRUE;
3497 END is_org_bo_comp;
3498
3499 -- FUNCTION is_cac_bo_comp
3500 --
3501 -- DESCRIPTION
3502 -- Return true if customer account contact object is complete.
3503 -- Otherwise, return false.
3504 --
3505 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3506 --
3507 -- ARGUMENTS
3508 -- IN:
3509 -- p_cac_objs List of customer account contact business objects.
3510 -- p_bus_object Business object structure for customer account contact.
3511 -- NOTES
3512 --
3513 -- MODIFICATION HISTORY
3514 --
3515 -- 13-Jul-2005 Arnold Ng o Created.
3516
3517 FUNCTION is_cac_bo_comp(
3518 p_cac_objs IN HZ_CUST_ACCT_CONTACT_BO_TBL,
3519 p_bus_object IN COMPLETENESS_REC_TYPE
3520 ) RETURN BOOLEAN IS
3521 l_valid_obj BOOLEAN;
3522 l_rr BOOLEAN;
3523 BEGIN
3524 l_rr := FALSE;
3525
3526 IF(p_cac_objs IS NULL OR p_cac_objs.COUNT < 1) THEN
3527 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_OBJ');
3528 fnd_message.set_token('OBJECT' ,'CUST_ACCT_CONTACT');
3529 fnd_msg_pub.add;
3530 RETURN FALSE;
3531 END IF;
3532
3533 FOR i IN 1..p_bus_object.business_object_code.COUNT LOOP
3534 -- get all entities of cust acct contact, for cust acct contact, the possible entites are
3535 -- HZ_ROLE_RESPONSIBILITY
3536 IF(p_bus_object.tca_mandated_flag(i) = 'N' AND
3537 p_bus_object.user_mandated_flag(i) = 'Y' AND
3538 p_bus_object.business_object_code(i) = 'CUST_ACCT_CONTACT' AND
3539 p_bus_object.child_bo_code(i) IS NULL) THEN
3540 IF(p_bus_object.entity_name(i) = 'HZ_ROLE_RESPONSIBILITY') THEN
3541 l_rr := TRUE;
3542 END IF;
3543 END IF;
3544 END LOOP;
3545
3546 FOR i IN 1..p_cac_objs.COUNT LOOP
3547 -- Check role responsibility objects
3548 IF(l_rr AND
3549 (p_cac_objs(i).contact_role_objs IS NULL OR
3550 p_cac_objs(i).contact_role_objs.COUNT < 1)) THEN
3551 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3552 fnd_message.set_token('OBJECT' ,'ROLE_RESPONSIBILITY');
3553 fnd_msg_pub.add;
3554 RETURN FALSE;
3555 END IF;
3556 END LOOP;
3557
3558 RETURN TRUE;
3559 END is_cac_bo_comp;
3560
3561 -- FUNCTION is_cas_bo_comp
3562 --
3563 -- DESCRIPTION
3564 -- Return true if customer account site object is complete.
3565 -- Otherwise, return false.
3566 --
3567 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3568 --
3569 -- ARGUMENTS
3570 -- IN:
3571 -- p_cas_objs List of customer account site business objects.
3572 -- p_bus_object Business object structure for customer account site.
3573 -- NOTES
3574 --
3575 -- MODIFICATION HISTORY
3576 --
3577 -- 13-Jul-2005 Arnold Ng o Created.
3578
3579 FUNCTION is_cas_bo_comp(
3580 p_cas_objs IN HZ_CUST_ACCT_SITE_BO_TBL,
3581 p_bus_object IN COMPLETENESS_REC_TYPE
3582 ) RETURN BOOLEAN IS
3583 l_valid_obj BOOLEAN;
3584 l_casu BOOLEAN;
3585 l_cp BOOLEAN;
3586 l_cac BOOLEAN;
3587 l_cpa BOOLEAN;
3588 l_bau BOOLEAN;
3589 l_pm BOOLEAN;
3590 l_cac_bus_object COMPLETENESS_REC_TYPE;
3591 BEGIN
3592 l_casu := FALSE;
3593 l_cp := FALSE;
3594 l_cpa := FALSE;
3595 l_cac := FALSE;
3596 l_bau := FALSE;
3597 l_pm := FALSE;
3598
3599 IF(p_cas_objs IS NULL OR p_cas_objs.COUNT < 1) THEN
3600 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_OBJ');
3601 fnd_message.set_token('OBJECT' ,'CUST_ACCT_SITE');
3602 fnd_msg_pub.add;
3603 RETURN FALSE;
3604 END IF;
3605
3606 FOR i IN 1..p_bus_object.business_object_code.COUNT LOOP
3607 -- no entities of cust acct site
3608 IF(p_bus_object.child_bo_code(i) IS NOT NULL AND
3609 p_bus_object.business_object_code(i) = 'CUST_ACCT_SITE' AND
3610 p_bus_object.user_mandated_flag(i) = 'Y') THEN
3611 CASE
3612 WHEN(p_bus_object.child_bo_code(i) = 'CUST_ACCT_SITE_USE' AND
3613 p_bus_object.business_object_code(i) = 'CUST_ACCT_SITE') THEN
3614 l_casu := TRUE;
3615 WHEN(p_bus_object.child_bo_code(i) = 'CUST_ACCT_CONTACT' AND
3616 p_bus_object.business_object_code(i) = 'CUST_ACCT_SITE') THEN
3617 l_cac := TRUE;
3618 END CASE;
3619 ELSIF(p_bus_object.tca_mandated_flag(i) = 'N' AND
3620 p_bus_object.user_mandated_flag(i) = 'Y' AND
3621 p_bus_object.business_object_code(i) = 'CUST_ACCT_SITE_USE' AND
3622 p_bus_object.child_bo_code(i) IS NULL) THEN
3623 CASE
3624 WHEN(p_bus_object.entity_name(i) = 'RA_CUST_RECEIPT_METHODS') THEN
3625 l_pm := TRUE;
3626 WHEN(p_bus_object.entity_name(i) = 'IBY_FNDCPT_PAYER_ASSGN_INSTR_V') THEN
3627 l_bau := TRUE;
3628 END CASE;
3629 ELSIF(p_bus_object.child_bo_code(i) = 'CUST_PROFILE' AND
3630 p_bus_object.business_object_code(i) = 'CUST_ACCT_SITE_USE' AND
3631 p_bus_object.user_mandated_flag(i) = 'Y') THEN
3632 l_cp := TRUE;
3633 ELSIF(p_bus_object.entity_name(i) = 'HZ_CUST_PROFILE_AMTS' AND
3634 p_bus_object.business_object_code(i) = 'CUST_PROFILE' AND
3635 p_bus_object.user_mandated_flag(i) = 'Y') THEN
3636 l_cpa := TRUE;
3637 END IF;
3638 END LOOP;
3639
3640 IF(l_cac) THEN
3641 get_bus_obj_struct(
3642 p_bus_object_code => 'CUST_ACCT_CONTACT',
3643 x_bus_object => l_cac_bus_object
3644 );
3645 END IF;
3646
3647 FOR i IN 1..p_cas_objs.COUNT LOOP
3648 IF(l_cac) THEN
3649 l_valid_obj := is_cac_bo_comp(
3650 p_cac_objs => p_cas_objs(i).cust_acct_contact_objs,
3651 p_bus_object => l_cac_bus_object
3652 );
3653 IF NOT(l_valid_obj) THEN
3654 RETURN FALSE;
3655 END IF;
3656 END IF;
3657
3658 IF(l_casu AND
3659 (p_cas_objs(i).cust_acct_site_use_objs IS NULL OR
3660 p_cas_objs(i).cust_acct_site_use_objs.COUNT < 1)) THEN
3661 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3662 fnd_message.set_token('OBJECT' ,'CUST_ACCT_SITE_USE');
3663 fnd_msg_pub.add;
3664 RETURN FALSE;
3665
3666 FOR j IN 1..p_cas_objs(i).cust_acct_site_use_objs.COUNT LOOP
3667 IF(l_cp AND p_cas_objs(i).cust_acct_site_use_objs(j).site_use_profile_obj IS NULL) THEN
3668 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3669 fnd_message.set_token('OBJECT' ,'SITE_USE_PROFILE');
3670 fnd_msg_pub.add;
3671 RETURN FALSE;
3672 END IF;
3673 IF(l_cpa AND
3674 (p_cas_objs(i).cust_acct_site_use_objs(j).site_use_profile_obj.cust_profile_amt_objs IS NULL OR
3675 p_cas_objs(i).cust_acct_site_use_objs(j).site_use_profile_obj.cust_profile_amt_objs.COUNT < 1)) THEN
3676 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3677 fnd_message.set_token('OBJECT' ,'SITE_USE_PROFILE_AMOUNT');
3678 fnd_msg_pub.add;
3679 RETURN FALSE;
3680 END IF;
3681 -- for bank account use and payment method
3682 IF(l_bau AND
3683 (p_cas_objs(i).cust_acct_site_use_objs(j).bank_acct_use_objs IS NULL OR
3684 p_cas_objs(i).cust_acct_site_use_objs(j).bank_acct_use_objs.COUNT < 1)) THEN
3685 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3686 fnd_message.set_token('OBJECT' ,'BANK_ACCOUNT');
3687 fnd_msg_pub.add;
3688 RETURN FALSE;
3689 END IF;
3690 IF(l_pm AND
3691 (p_cas_objs(i).cust_acct_site_use_objs(j).payment_method_obj IS NULL)) THEN
3692 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3693 fnd_message.set_token('OBJECT' ,'PAYMENT_METHOD');
3694 fnd_msg_pub.add;
3695 RETURN FALSE;
3696 END IF;
3697 END LOOP;
3698 END IF;
3699 END LOOP;
3700
3701 RETURN TRUE;
3702 END is_cas_bo_comp;
3703
3704 -- FUNCTION is_ca_bo_comp
3705 --
3706 -- DESCRIPTION
3707 -- Return true if customer account object is complete. Otherwise, return false.
3708 --
3709 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3710 --
3711 -- ARGUMENTS
3712 -- IN:
3713 -- p_ca_objs List of customer account business objects.
3714 -- p_bus_object Business object structure for customer account.
3715 -- NOTES
3716 --
3717 -- MODIFICATION HISTORY
3718 --
3719 -- 13-Jul-2005 Arnold Ng o Created.
3720
3721 FUNCTION is_ca_bo_comp(
3722 p_ca_objs IN HZ_CUST_ACCT_BO_TBL,
3723 p_bus_object IN COMPLETENESS_REC_TYPE
3724 ) RETURN BOOLEAN IS
3725 l_valid_obj BOOLEAN;
3726 l_carel BOOLEAN;
3727 l_cas BOOLEAN;
3728 l_cac BOOLEAN;
3729 l_cp BOOLEAN;
3730 l_cpa BOOLEAN;
3731 l_bau BOOLEAN;
3732 l_pm BOOLEAN;
3733 l_cas_bus_object COMPLETENESS_REC_TYPE;
3734 l_cac_bus_object COMPLETENESS_REC_TYPE;
3735 BEGIN
3736 l_carel := FALSE;
3737 l_cas := FALSE;
3738 l_cac := FALSE;
3739 l_cp := FALSE;
3740 l_cpa := FALSE;
3741 l_bau := FALSE;
3742 l_pm := FALSE;
3743
3744 IF(p_ca_objs IS NULL OR p_ca_objs.COUNT < 1) THEN
3745 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_OBJ');
3746 fnd_message.set_token('OBJECT' ,'CUST_ACCT');
3747 fnd_msg_pub.add;
3748 RETURN FALSE;
3749 END IF;
3750
3751 FOR i IN 1..p_bus_object.business_object_code.COUNT LOOP
3752 -- get all entities of cust acct, for cust acct, the possible entites are
3753 -- HZ_BANK_ACCOUNT_USE, HZ_PAYMENT_METHOD, HZ_CUST_ACCT_RELATE
3754 IF(p_bus_object.tca_mandated_flag(i) = 'N' AND
3755 p_bus_object.user_mandated_flag(i) = 'Y' AND
3756 p_bus_object.business_object_code(i) = 'CUST_ACCT' AND
3757 p_bus_object.child_bo_code(i) IS NULL) THEN
3758 CASE
3759 WHEN(p_bus_object.entity_name(i) = 'HZ_CUST_ACCT_RELATE_ALL') THEN
3760 l_carel := TRUE;
3761 WHEN(p_bus_object.entity_name(i) = 'RA_CUST_RECEIPT_METHODS') THEN
3762 l_pm := TRUE;
3763 WHEN(p_bus_object.entity_name(i) = 'IBY_FNDCPT_PAYER_ASSGN_INSTR_V') THEN
3764 l_bau := TRUE;
3765 END CASE;
3766 -- Get other business object
3767 ELSIF(p_bus_object.child_bo_code(i) IS NOT NULL AND
3768 p_bus_object.business_object_code(i) = 'CUST_ACCT' AND
3769 p_bus_object.user_mandated_flag(i) = 'Y') THEN
3770 CASE
3771 WHEN (p_bus_object.child_bo_code(i) = 'CUST_ACCT_SITE') THEN
3772 l_cas := TRUE;
3773 WHEN (p_bus_object.child_bo_code(i) = 'CUST_ACCT_CONTACT') THEN
3774 l_cac := TRUE;
3775 ELSE
3776 null;
3777 END CASE;
3778 ELSIF(p_bus_object.child_bo_code(i) IS NOT NULL AND
3779 p_bus_object.business_object_code(i) = 'CUST_PROFILE' AND
3780 p_bus_object.user_mandated_flag(i) = 'Y') THEN
3781 IF(p_bus_object.entity_name(i) = 'HZ_CUST_PROFILE_AMTS') THEN
3782 l_cpa := TRUE;
3783 END IF;
3784 END IF;
3785 END LOOP;
3786
3787 -- customer profile is mandatory for customer account
3788 l_cp := TRUE;
3789
3790 IF(l_cas) THEN
3791 get_bus_obj_struct(
3792 p_bus_object_code => 'CUST_ACCT_SITE',
3793 x_bus_object => l_cas_bus_object
3794 );
3795 END IF;
3796
3797 IF(l_cac) THEN
3798 get_bus_obj_struct(
3799 p_bus_object_code => 'CUST_ACCT_CONTACT',
3800 x_bus_object => l_cac_bus_object
3801 );
3802 END IF;
3803
3804 FOR i IN 1..p_ca_objs.COUNT LOOP
3805 IF(l_cas) THEN
3806 l_valid_obj := is_cas_bo_comp(
3807 p_cas_objs => p_ca_objs(i).cust_acct_site_objs,
3808 p_bus_object => l_cas_bus_object
3809 );
3810 IF NOT(l_valid_obj) THEN
3811 RETURN FALSE;
3812 END IF;
3813 END IF;
3814
3815 IF(l_cac) THEN
3816 l_valid_obj := is_cac_bo_comp(
3817 p_cac_objs => p_ca_objs(i).cust_acct_contact_objs,
3818 p_bus_object => l_cac_bus_object
3819 );
3820 IF NOT(l_valid_obj) THEN
3821 RETURN FALSE;
3822 END IF;
3823 END IF;
3824
3825 IF(l_carel AND
3826 (p_ca_objs(i).acct_relate_objs IS NULL OR
3827 p_ca_objs(i).acct_relate_objs.COUNT < 1)) THEN
3828 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3829 fnd_message.set_token('OBJECT' ,'CUSTOMER_ACCOUNT_RELATE');
3830 fnd_msg_pub.add;
3831 RETURN FALSE;
3832 END IF;
3833
3834 -- for bank account use and payment method
3835 IF(l_bau AND
3836 (p_ca_objs(i).bank_acct_use_objs IS NULL OR
3837 p_ca_objs(i).bank_acct_use_objs.COUNT < 1)) THEN
3838 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3839 fnd_message.set_token('OBJECT' ,'BANK_ACCOUNT');
3840 fnd_msg_pub.add;
3841 RETURN FALSE;
3842 END IF;
3843 IF(l_pm AND
3844 (p_ca_objs(i).payment_method_obj IS NULL)) THEN
3845 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3846 fnd_message.set_token('OBJECT' ,'PAYMENT_METHOD');
3847 fnd_msg_pub.add;
3848 RETURN FALSE;
3849 END IF;
3850
3851 IF(l_cp AND p_ca_objs(i).cust_profile_obj IS NULL) THEN
3852 RETURN FALSE;
3853 ELSE
3854 IF(l_cpa AND
3855 (p_ca_objs(i).cust_profile_obj.cust_profile_amt_objs IS NULL OR
3856 p_ca_objs(i).cust_profile_obj.cust_profile_amt_objs.COUNT < 1)) THEN
3857 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
3858 fnd_message.set_token('OBJECT' ,'CUSTOMER_PROFILE_AMOUNT');
3859 fnd_msg_pub.add;
3860 RETURN FALSE;
3861 END IF;
3862 END IF;
3863 END LOOP;
3864
3865 RETURN TRUE;
3866 END is_ca_bo_comp;
3867
3868 -- FUNCTION is_pca_bo_comp
3869 --
3870 -- DESCRIPTION
3871 -- Return true if person customer object is complete. Otherwise, return false.
3872 --
3873 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3874 --
3875 -- ARGUMENTS
3876 -- IN:
3877 -- p_person_obj Person business object.
3878 -- p_ca_objs List of customer account objects.
3879 -- NOTES
3880 --
3881 -- MODIFICATION HISTORY
3882 --
3883 -- 13-Jul-2005 Arnold Ng o Created.
3884
3885 FUNCTION is_pca_bo_comp(
3886 p_person_obj IN HZ_PERSON_BO,
3887 p_ca_objs IN HZ_CUST_ACCT_BO_TBL
3888 ) RETURN BOOLEAN IS
3889 l_per_bus_object COMPLETENESS_REC_TYPE;
3890 l_ca_bus_object COMPLETENESS_REC_TYPE;
3891 l_valid_obj BOOLEAN;
3892 BEGIN
3893 IF(p_person_obj IS NULL) THEN
3894 RETURN FALSE;
3895 END IF;
3896 IF(p_ca_objs IS NULL OR p_ca_objs.COUNT < 1) THEN
3897 RETURN FALSE;
3898 END IF;
3899
3900 -- check person object for person cust acct
3901 get_bus_obj_struct(
3902 p_bus_object_code => 'PERSON',
3903 x_bus_object => l_per_bus_object
3904 );
3905 l_valid_obj := is_person_bo_comp(
3906 p_person_obj => p_person_obj,
3907 p_bus_object => l_per_bus_object
3908 );
3909 IF NOT(l_valid_obj) THEN
3910 RETURN FALSE;
3911 END IF;
3912
3913 -- check cust account for person cust acct
3914 get_bus_obj_struct(
3915 p_bus_object_code => 'CUST_ACCT',
3916 x_bus_object => l_ca_bus_object
3917 );
3918 l_valid_obj := is_ca_bo_comp(
3919 p_ca_objs => p_ca_objs,
3920 p_bus_object => l_ca_bus_object
3921 );
3922 IF NOT(l_valid_obj) THEN
3923 RETURN FALSE;
3924 END IF;
3925
3926 RETURN TRUE;
3927 END is_pca_bo_comp;
3928
3929 -- FUNCTION is_oca_bo_comp
3930 --
3931 -- DESCRIPTION
3932 -- Return true if organization customer object is complete.
3933 -- Otherwise, return false.
3934 --
3935 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3936 --
3937 -- ARGUMENTS
3938 -- IN:
3939 -- p_org_obj Organization business object.
3940 -- p_ca_objs List of customer account objects.
3941 -- NOTES
3942 --
3943 -- MODIFICATION HISTORY
3944 --
3945 -- 13-Jul-2005 Arnold Ng o Created.
3946
3947 FUNCTION is_oca_bo_comp(
3948 p_org_obj IN HZ_ORGANIZATION_BO,
3949 p_ca_objs IN HZ_CUST_ACCT_BO_TBL
3950 ) RETURN BOOLEAN IS
3951 l_org_bus_object COMPLETENESS_REC_TYPE;
3952 l_ca_bus_object COMPLETENESS_REC_TYPE;
3953 l_valid_obj BOOLEAN;
3954 BEGIN
3955 IF(p_org_obj IS NULL) THEN
3956 RETURN FALSE;
3957 END IF;
3958 IF(p_ca_objs IS NULL OR p_ca_objs.COUNT < 1) THEN
3959 RETURN FALSE;
3960 END IF;
3961
3962 -- check organization object for org cust acct
3963 get_bus_obj_struct(
3964 p_bus_object_code => 'ORG',
3965 x_bus_object => l_org_bus_object
3966 );
3967 l_valid_obj := is_org_bo_comp(
3968 p_organization_obj => p_org_obj,
3969 p_bus_object => l_org_bus_object
3970 );
3971 IF NOT(l_valid_obj) THEN
3972 RETURN FALSE;
3973 END IF;
3974
3975 -- check cust account for org cust acct
3976 get_bus_obj_struct(
3977 p_bus_object_code => 'CUST_ACCT',
3978 x_bus_object => l_ca_bus_object
3979 );
3980 l_valid_obj := is_ca_bo_comp(
3981 p_ca_objs => p_ca_objs,
3982 p_bus_object => l_ca_bus_object
3983 );
3984 IF NOT(l_valid_obj) THEN
3985 RETURN FALSE;
3986 END IF;
3987
3988 RETURN TRUE;
3989 END is_oca_bo_comp;
3990
3991 -- FUNCTION get_bus_object_struct
3992 --
3993 -- DESCRIPTION
3994 -- Get contact point business object structure.
3995 --
3996 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3997 --
3998 -- ARGUMENTS
3999 -- IN:
4000 -- p_bus_object_code Business object code, such as 'PARTY_SITE',
4001 -- 'ORG_CONTACT'
4002 -- OUT:
4003 -- x_bus_object Business object structure.
4004 -- NOTES
4005 --
4006 -- MODIFICATION HISTORY
4007 --
4008 -- 13-Jul-2005 Arnold Ng o Created.
4009
4010 PROCEDURE get_bus_obj_struct(
4011 p_bus_object_code IN VARCHAR2,
4012 x_bus_object OUT NOCOPY COMPLETENESS_REC_TYPE
4013 ) IS
4014 CURSOR get_bus_obj(l_bus_obj VARCHAR2) IS
4015 SELECT d.business_object_code, d.child_bo_code, d.tca_mandated_flag,
4016 d.user_mandated_flag, d.root_node_flag, d.entity_name
4017 FROM hz_bus_obj_definitions d
4018 start with d.business_object_code = l_bus_obj and d.user_mandated_flag = 'Y'
4019 connect by prior d.child_bo_code = d.business_object_code and d.user_mandated_flag = 'Y'
4020 group by 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 BEGIN
4023 OPEN get_bus_obj(p_bus_object_code);
4024 FETCH get_bus_obj BULK COLLECT
4025 INTO x_bus_object.business_object_code,
4026 x_bus_object.child_bo_code,
4027 x_bus_object.tca_mandated_flag,
4028 x_bus_object.user_mandated_flag,
4029 x_bus_object.root_node_flag,
4030 x_bus_object.entity_name;
4031 CLOSE get_bus_obj;
4032 END get_bus_obj_struct;
4033
4034 -- FUNCTION get_cp_bus_obj_struct
4035 --
4036 -- DESCRIPTION
4037 -- Get contact point business object structure.
4038 --
4039 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4040 --
4041 -- ARGUMENTS
4042 -- IN:
4043 -- p_phone_code 'PHONE'.
4044 -- p_email_code 'EMAIL'.
4045 -- p_telex_code 'TLX'.
4046 -- p_web_code 'WEB'.
4047 -- p_edi_code 'EDI'.
4048 -- p_eft_code 'EFT'.
4049 -- p_sms_code 'SMS'.
4050 -- OUT:
4051 -- x_bus_object Contact point business object structure.
4052 -- NOTES
4053 --
4054 -- MODIFICATION HISTORY
4055 --
4056 -- 13-Jul-2005 Arnold Ng o Created.
4057
4058 PROCEDURE get_cp_bus_obj_struct(
4059 p_phone_code IN VARCHAR2,
4060 p_email_code IN VARCHAR2,
4061 p_telex_code IN VARCHAR2,
4062 p_web_code IN VARCHAR2,
4063 p_edi_code IN VARCHAR2,
4064 p_eft_code IN VARCHAR2,
4065 p_sms_code IN VARCHAR2,
4066 x_bus_object OUT NOCOPY COMPLETENESS_REC_TYPE
4067 ) IS
4068 CURSOR get_cp_bus_obj(l_phone VARCHAR2, l_email VARCHAR2,
4069 l_telex VARCHAR2, l_web VARCHAR2, l_edi VARCHAR2,
4070 l_eft VARCHAR2, l_sms VARCHAR2) IS
4071 SELECT d.business_object_code, d.child_bo_code, d.tca_mandated_flag,
4072 d.user_mandated_flag, d.root_node_flag, d.entity_name
4073 FROM hz_bus_obj_definitions d
4074 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'
4075 connect by prior d.child_bo_code = d.business_object_code
4076 and d.user_mandated_flag = 'Y'
4077 group by d.business_object_code, d.child_bo_code, d.tca_mandated_flag,
4078 d.user_mandated_flag, d.root_node_flag, d.entity_name;
4079 BEGIN
4080 OPEN get_cp_bus_obj(p_phone_code, p_email_code, p_telex_code,
4081 p_web_code, p_edi_code, p_eft_code, p_sms_code);
4082 FETCH get_cp_bus_obj BULK COLLECT
4083 INTO x_bus_object.business_object_code,
4084 x_bus_object.child_bo_code,
4085 x_bus_object.tca_mandated_flag,
4086 x_bus_object.user_mandated_flag,
4087 x_bus_object.root_node_flag,
4088 x_bus_object.entity_name;
4089 CLOSE get_cp_bus_obj;
4090 END get_cp_bus_obj_struct;
4091
4092 -- PRIVATE PROCEDURE get_cp_from_rec
4093 --
4094 -- DESCRIPTION
4095 -- Extract business object structure of contact point.
4096 --
4097 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4098 --
4099 -- ARGUMENTS
4100 -- IN:
4101 -- p_phone_code 'PHONE'.
4102 -- p_email_code 'EMAIL'.
4103 -- p_telex_code 'TLX'.
4104 -- p_web_code 'WEB'.
4105 -- p_edi_code 'EDI'.
4106 -- p_eft_code 'EFT'.
4107 -- p_sms_code 'SMS'.
4108 -- OUT:
4109 -- x_bus_object Business object structure of contact point.
4110 -- NOTES
4111 --
4112 -- MODIFICATION HISTORY
4113 --
4114 -- 13-Jul-2005 Arnold Ng o Created.
4115
4116 PROCEDURE get_cp_from_rec(
4117 p_phone_code IN VARCHAR2,
4118 p_email_code IN VARCHAR2,
4119 p_telex_code IN VARCHAR2,
4120 p_web_code IN VARCHAR2,
4121 p_edi_code IN VARCHAR2,
4122 p_eft_code IN VARCHAR2,
4123 p_sms_code IN VARCHAR2,
4124 p_bus_object IN COMPLETENESS_REC_TYPE,
4125 x_bus_object OUT NOCOPY COMPLETENESS_REC_TYPE
4126 ) IS
4127 l_count NUMBER;
4128 BEGIN
4129 l_count := 0;
4130 x_bus_object.business_object_code := boc_tbl();
4131 x_bus_object.child_bo_code := cbc_tbl();
4132 x_bus_object.tca_mandated_flag := tmf_tbl();
4133 x_bus_object.user_mandated_flag := umf_tbl();
4134 x_bus_object.root_node_flag := rnf_tbl();
4135 x_bus_object.entity_name := ent_tbl();
4136 FOR i IN 1..p_bus_object.business_object_code.COUNT LOOP
4137 IF(p_bus_object.business_object_code(i) = p_phone_code OR
4138 p_bus_object.business_object_code(i) = p_email_code OR
4139 p_bus_object.business_object_code(i) = p_telex_code OR
4140 p_bus_object.business_object_code(i) = p_web_code OR
4141 p_bus_object.business_object_code(i) = p_edi_code OR
4142 p_bus_object.business_object_code(i) = p_eft_code OR
4143 p_bus_object.business_object_code(i) = p_sms_code) THEN
4144 l_count := l_count + 1;
4145 x_bus_object.business_object_code.EXTEND;
4146 x_bus_object.child_bo_code.EXTEND;
4147 x_bus_object.tca_mandated_flag.EXTEND;
4148 x_bus_object.user_mandated_flag.EXTEND;
4149 x_bus_object.root_node_flag.EXTEND;
4150 x_bus_object.entity_name.EXTEND;
4151 x_bus_object.business_object_code(l_count) := p_bus_object.business_object_code(i);
4152 x_bus_object.child_bo_code(l_count) := p_bus_object.child_bo_code(i);
4153 x_bus_object.tca_mandated_flag(l_count) := p_bus_object.tca_mandated_flag(i);
4154 x_bus_object.user_mandated_flag(l_count) := p_bus_object.user_mandated_flag(i);
4155 x_bus_object.root_node_flag(l_count) := p_bus_object.root_node_flag(i);
4156 x_bus_object.entity_name(l_count) := p_bus_object.entity_name(i);
4157 END IF;
4158 END LOOP;
4159 END get_cp_from_rec;
4160
4161 -- PRIVATE PROCEDURE get_ps_from_rec
4162 --
4163 -- DESCRIPTION
4164 -- Extract business object structure of party site.
4165 --
4166 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4167 --
4168 -- ARGUMENTS
4169 -- IN:
4170 -- p_bus_object Business object structure.
4171 -- OUT:
4172 -- x_bus_object Business object structure of party site.
4173 -- NOTES
4174 --
4175 -- MODIFICATION HISTORY
4176 --
4177 -- 13-Jul-2005 Arnold Ng o Created.
4178
4179 PROCEDURE get_ps_from_rec(
4180 p_bus_object IN COMPLETENESS_REC_TYPE,
4181 x_bus_object OUT NOCOPY COMPLETENESS_REC_TYPE
4182 ) IS
4183 l_count NUMBER;
4184 l_phone VARCHAR2(30);
4185 l_telex VARCHAR2(30);
4186 l_email VARCHAR2(30);
4187 l_web VARCHAR2(30);
4188 l_edi VARCHAR2(30);
4189 l_eft VARCHAR2(30);
4190 l_sms VARCHAR2(30);
4191 BEGIN
4192 l_count := 0;
4193 x_bus_object.business_object_code := boc_tbl();
4194 x_bus_object.child_bo_code := cbc_tbl();
4195 x_bus_object.tca_mandated_flag := tmf_tbl();
4196 x_bus_object.user_mandated_flag := umf_tbl();
4197 x_bus_object.root_node_flag := rnf_tbl();
4198 x_bus_object.entity_name := ent_tbl();
4199 -- find all rows related to PARTY_SITE
4200 FOR i IN 1..p_bus_object.business_object_code.COUNT LOOP
4201 IF(p_bus_object.business_object_code(i) = 'PARTY_SITE') THEN
4202 l_count := l_count + 1;
4203 x_bus_object.business_object_code.EXTEND;
4204 x_bus_object.child_bo_code.EXTEND;
4205 x_bus_object.tca_mandated_flag.EXTEND;
4206 x_bus_object.user_mandated_flag.EXTEND;
4207 x_bus_object.root_node_flag.EXTEND;
4208 x_bus_object.entity_name.EXTEND;
4209 x_bus_object.business_object_code(l_count) := p_bus_object.business_object_code(i);
4210 x_bus_object.child_bo_code(l_count) := p_bus_object.child_bo_code(i);
4211 x_bus_object.tca_mandated_flag(l_count) := p_bus_object.tca_mandated_flag(i);
4212 x_bus_object.user_mandated_flag(l_count) := p_bus_object.user_mandated_flag(i);
4213 x_bus_object.root_node_flag(l_count) := p_bus_object.root_node_flag(i);
4214 x_bus_object.entity_name(l_count) := p_bus_object.entity_name(i);
4215 END IF;
4216 END LOOP;
4217
4218 -- find all PARTY_SITE rows with child_bo_code, all rows must be CONTACT POINT
4219 FOR i IN 1..x_bus_object.business_object_code.COUNT LOOP
4220 IF(x_bus_object.child_bo_code(i) IS NOT NULL) THEN
4221 CASE
4222 WHEN x_bus_object.child_bo_code(i) = 'PHONE' THEN
4223 l_phone := x_bus_object.child_bo_code(i);
4224 WHEN x_bus_object.child_bo_code(i) = 'TLX' THEN
4225 l_telex := x_bus_object.child_bo_code(i);
4226 WHEN x_bus_object.child_bo_code(i) = 'EMAIL' THEN
4227 l_email := x_bus_object.child_bo_code(i);
4228 WHEN x_bus_object.child_bo_code(i) = 'WEB' THEN
4229 l_web := x_bus_object.child_bo_code(i);
4230 WHEN x_bus_object.child_bo_code(i) = 'EDI' THEN
4231 l_edi := x_bus_object.child_bo_code(i);
4232 WHEN x_bus_object.child_bo_code(i) = 'EFT' THEN
4233 l_eft := x_bus_object.child_bo_code(i);
4234 WHEN x_bus_object.child_bo_code(i) = 'SMS' THEN
4235 l_sms := x_bus_object.child_bo_code(i);
4236 ELSE -- for 'LOCATION'
4237 NULL;
4238 END CASE;
4239 END IF;
4240 END LOOP;
4241
4242 -- find all contact point rows for PARTY_SITE
4243 FOR i IN 1..p_bus_object.business_object_code.COUNT LOOP
4244 IF(p_bus_object.business_object_code(i) = l_phone OR
4245 p_bus_object.business_object_code(i) = l_telex OR
4246 p_bus_object.business_object_code(i) = l_email OR
4247 p_bus_object.business_object_code(i) = l_web OR
4248 p_bus_object.business_object_code(i) = l_edi OR
4249 p_bus_object.business_object_code(i) = l_eft OR
4250 p_bus_object.business_object_code(i) = l_sms) THEN
4251 l_count := l_count + 1;
4252 x_bus_object.business_object_code.EXTEND;
4253 x_bus_object.child_bo_code.EXTEND;
4254 x_bus_object.tca_mandated_flag.EXTEND;
4255 x_bus_object.user_mandated_flag.EXTEND;
4256 x_bus_object.root_node_flag.EXTEND;
4257 x_bus_object.entity_name.EXTEND;
4258 x_bus_object.business_object_code(l_count) := p_bus_object.business_object_code(i);
4259 x_bus_object.child_bo_code(l_count) := p_bus_object.child_bo_code(i);
4260 x_bus_object.tca_mandated_flag(l_count) := p_bus_object.tca_mandated_flag(i);
4261 x_bus_object.user_mandated_flag(l_count) := p_bus_object.user_mandated_flag(i);
4262 x_bus_object.root_node_flag(l_count) := p_bus_object.root_node_flag(i);
4263 x_bus_object.entity_name(l_count) := p_bus_object.entity_name(i);
4264 END IF;
4265 END LOOP;
4266 END get_ps_from_rec;
4267
4268 -- FUNCTION get_id_from_ososr
4269 --
4270 -- DESCRIPTION
4271 -- Get TCA Id based on original system and original system reference.
4272 --
4273 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4274 --
4275 -- ARGUMENTS
4276 -- IN:
4277 -- p_os Original system
4278 -- p_osr Original system reference
4279 -- p_owner_table_name Owner table name
4280 -- NOTES
4281 --
4282 -- MODIFICATION HISTORY
4283 --
4284 -- 13-Jul-2005 Arnold Ng o Created.
4285
4286 FUNCTION get_id_from_ososr(
4287 p_os IN VARCHAR2,
4288 p_osr IN VARCHAR2,
4289 p_owner_table_name IN VARCHAR2
4290 ) RETURN NUMBER IS
4291 l_count NUMBER;
4292 l_owner_table_id NUMBER;
4293 l_return_status VARCHAR2(30);
4294 BEGIN
4295 l_count := HZ_MOSR_VALIDATE_PKG.get_orig_system_ref_count(
4296 p_orig_system => p_os,
4297 p_orig_system_reference => p_osr,
4298 p_owner_table_name => p_owner_table_name);
4299
4300 IF(l_count = 1) THEN
4301 -- Get owner_table_id
4302 HZ_ORIG_SYSTEM_REF_PUB.get_owner_table_id(
4303 p_orig_system => p_os,
4304 p_orig_system_reference => p_osr,
4305 p_owner_table_name => p_owner_table_name,
4306 x_owner_table_id => l_owner_table_id,
4307 x_return_status => l_return_status);
4308 END IF;
4309
4310 RETURN l_owner_table_id;
4311 END get_id_from_ososr;
4312
4313 -- FUNCTION is_cas_v2_bo_comp
4314 --
4315 -- DESCRIPTION
4316 -- Return true if customer account site object is complete.
4317 -- Otherwise, return false.
4318 --
4319 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4320 --
4321 -- ARGUMENTS
4322 -- IN:
4323 -- p_cas_v2_objs List of customer account site business objects.
4324 -- p_bus_object Business object structure for customer account site.
4325 -- NOTES
4326 --
4327 -- MODIFICATION HISTORY
4328 --
4329 -- 1-FEB-2008 vsegu o Created.
4330
4331 FUNCTION is_cas_v2_bo_comp(
4332 p_cas_v2_objs IN HZ_CUST_ACCT_SITE_V2_BO_TBL,
4333 p_bus_object IN COMPLETENESS_REC_TYPE
4334 ) RETURN BOOLEAN IS
4335 l_valid_obj BOOLEAN;
4336 l_casu BOOLEAN;
4337 l_cp BOOLEAN;
4338 l_cac BOOLEAN;
4339 l_cpa BOOLEAN;
4340 l_bau BOOLEAN;
4341 l_pm BOOLEAN;
4342 l_cac_bus_object COMPLETENESS_REC_TYPE;
4343 BEGIN
4344 l_casu := FALSE;
4345 l_cp := FALSE;
4346 l_cpa := FALSE;
4347 l_cac := FALSE;
4348 l_bau := FALSE;
4349 l_pm := FALSE;
4350
4351 IF(p_cas_v2_objs IS NULL OR p_cas_v2_objs.COUNT < 1) THEN
4352 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_OBJ');
4353 fnd_message.set_token('OBJECT' ,'CUST_ACCT_SITE');
4354 fnd_msg_pub.add;
4355 RETURN FALSE;
4356 END IF;
4357
4358 FOR i IN 1..p_bus_object.business_object_code.COUNT LOOP
4359 -- no entities of cust acct site
4360 IF(p_bus_object.child_bo_code(i) IS NOT NULL AND
4361 p_bus_object.business_object_code(i) = 'CUST_ACCT_SITE' AND
4362 p_bus_object.user_mandated_flag(i) = 'Y') THEN
4363 CASE
4364 WHEN(p_bus_object.child_bo_code(i) = 'CUST_ACCT_SITE_USE' AND
4365 p_bus_object.business_object_code(i) = 'CUST_ACCT_SITE') THEN
4366 l_casu := TRUE;
4367 WHEN(p_bus_object.child_bo_code(i) = 'CUST_ACCT_CONTACT' AND
4368 p_bus_object.business_object_code(i) = 'CUST_ACCT_SITE') THEN
4369 l_cac := TRUE;
4370 END CASE;
4371 ELSIF(p_bus_object.tca_mandated_flag(i) = 'N' AND
4372 p_bus_object.user_mandated_flag(i) = 'Y' AND
4373 p_bus_object.business_object_code(i) = 'CUST_ACCT_SITE_USE' AND
4374 p_bus_object.child_bo_code(i) IS NULL) THEN
4375 CASE
4376 WHEN(p_bus_object.entity_name(i) = 'RA_CUST_RECEIPT_METHODS') THEN
4377 l_pm := TRUE;
4378 WHEN(p_bus_object.entity_name(i) = 'IBY_FNDCPT_PAYER_ASSGN_INSTR_V') THEN
4379 l_bau := TRUE;
4380 END CASE;
4381 ELSIF(p_bus_object.child_bo_code(i) = 'CUST_PROFILE' AND
4382 p_bus_object.business_object_code(i) = 'CUST_ACCT_SITE_USE' AND
4383 p_bus_object.user_mandated_flag(i) = 'Y') THEN
4384 l_cp := TRUE;
4385 ELSIF(p_bus_object.entity_name(i) = 'HZ_CUST_PROFILE_AMTS' AND
4386 p_bus_object.business_object_code(i) = 'CUST_PROFILE' AND
4387 p_bus_object.user_mandated_flag(i) = 'Y') THEN
4388 l_cpa := TRUE;
4389 END IF;
4390 END LOOP;
4391
4392 IF(l_cac) THEN
4393 get_bus_obj_struct(
4394 p_bus_object_code => 'CUST_ACCT_CONTACT',
4395 x_bus_object => l_cac_bus_object
4396 );
4397 END IF;
4398
4399 FOR i IN 1..p_cas_v2_objs.COUNT LOOP
4400 IF(l_cac) THEN
4401 l_valid_obj := is_cac_bo_comp(
4402 p_cac_objs => p_cas_v2_objs(i).cust_acct_contact_objs,
4403 p_bus_object => l_cac_bus_object
4404 );
4405 IF NOT(l_valid_obj) THEN
4406 RETURN FALSE;
4407 END IF;
4408 END IF;
4409
4410 IF(l_casu AND
4411 (p_cas_v2_objs(i).cust_acct_site_use_objs IS NULL OR
4412 p_cas_v2_objs(i).cust_acct_site_use_objs.COUNT < 1)) THEN
4413 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
4414 fnd_message.set_token('OBJECT' ,'CUST_ACCT_SITE_USE');
4415 fnd_msg_pub.add;
4416 RETURN FALSE;
4417
4418 FOR j IN 1..p_cas_v2_objs(i).cust_acct_site_use_objs.COUNT LOOP
4419 IF(l_cp AND p_cas_v2_objs(i).cust_acct_site_use_objs(j).site_use_profile_obj IS NULL) THEN
4420 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
4421 fnd_message.set_token('OBJECT' ,'SITE_USE_PROFILE');
4422 fnd_msg_pub.add;
4423 RETURN FALSE;
4424 END IF;
4425 IF(l_cpa AND
4426 (p_cas_v2_objs(i).cust_acct_site_use_objs(j).site_use_profile_obj.cust_profile_amt_objs IS NULL OR
4427 p_cas_v2_objs(i).cust_acct_site_use_objs(j).site_use_profile_obj.cust_profile_amt_objs.COUNT < 1)) THEN
4428 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
4429 fnd_message.set_token('OBJECT' ,'SITE_USE_PROFILE_AMOUNT');
4430 fnd_msg_pub.add;
4431 RETURN FALSE;
4432 END IF;
4433 -- for bank account use and payment method
4434 IF(l_bau AND
4435 (p_cas_v2_objs(i).cust_acct_site_use_objs(j).bank_acct_use_objs IS NULL OR
4436 p_cas_v2_objs(i).cust_acct_site_use_objs(j).bank_acct_use_objs.COUNT < 1)) THEN
4437 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
4438 fnd_message.set_token('OBJECT' ,'BANK_ACCOUNT');
4439 fnd_msg_pub.add;
4440 RETURN FALSE;
4441 END IF;
4442 IF(l_pm AND
4443 (p_cas_v2_objs(i).cust_acct_site_use_objs(j).payment_method_objs IS NULL OR
4444 p_cas_v2_objs(i).cust_acct_site_use_objs(j).payment_method_objs.COUNT < 1)) THEN
4445 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
4446 fnd_message.set_token('OBJECT' ,'PAYMENT_METHOD');
4447 fnd_msg_pub.add;
4448 RETURN FALSE;
4449 END IF;
4450 END LOOP;
4451 END IF;
4452 END LOOP;
4453
4454 RETURN TRUE;
4455 END is_cas_v2_bo_comp;
4456
4457 -- FUNCTION is_ca_v2_bo_comp
4458 --
4459 -- DESCRIPTION
4460 -- Return true if customer account object is complete. Otherwise, return false.
4461 --
4462 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4463 --
4464 -- ARGUMENTS
4465 -- IN:
4466 -- p_ca_v2_objs List of customer account business objects.
4467 -- p_bus_object Business object structure for customer account.
4468 -- NOTES
4469 --
4470 -- MODIFICATION HISTORY
4471 --
4472 -- 1-FEB-2008 vsegu o Created.
4473
4474 FUNCTION is_ca_v2_bo_comp(
4475 p_ca_v2_objs IN HZ_CUST_ACCT_V2_BO_TBL,
4476 p_bus_object IN COMPLETENESS_REC_TYPE
4477 ) RETURN BOOLEAN IS
4478 l_valid_obj BOOLEAN;
4479 l_carel BOOLEAN;
4480 l_cas BOOLEAN;
4481 l_cac BOOLEAN;
4482 l_cp BOOLEAN;
4483 l_cpa BOOLEAN;
4484 l_bau BOOLEAN;
4485 l_pm BOOLEAN;
4486 l_cas_bus_object COMPLETENESS_REC_TYPE;
4487 l_cac_bus_object COMPLETENESS_REC_TYPE;
4488 BEGIN
4489 l_carel := FALSE;
4490 l_cas := FALSE;
4491 l_cac := FALSE;
4492 l_cp := FALSE;
4493 l_cpa := FALSE;
4494 l_bau := FALSE;
4495 l_pm := FALSE;
4496
4497 IF(p_ca_v2_objs IS NULL OR p_ca_v2_objs.COUNT < 1) THEN
4498 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_OBJ');
4499 fnd_message.set_token('OBJECT' ,'CUST_ACCT');
4500 fnd_msg_pub.add;
4501 RETURN FALSE;
4502 END IF;
4503
4504 FOR i IN 1..p_bus_object.business_object_code.COUNT LOOP
4505 -- get all entities of cust acct, for cust acct, the possible entites are
4506 -- HZ_BANK_ACCOUNT_USE, HZ_PAYMENT_METHOD, HZ_CUST_ACCT_RELATE
4507 IF(p_bus_object.tca_mandated_flag(i) = 'N' AND
4508 p_bus_object.user_mandated_flag(i) = 'Y' AND
4509 p_bus_object.business_object_code(i) = 'CUST_ACCT' AND
4510 p_bus_object.child_bo_code(i) IS NULL) THEN
4511 CASE
4512 WHEN(p_bus_object.entity_name(i) = 'HZ_CUST_ACCT_RELATE_ALL') THEN
4513 l_carel := TRUE;
4514 WHEN(p_bus_object.entity_name(i) = 'RA_CUST_RECEIPT_METHODS') THEN
4515 l_pm := TRUE;
4516 WHEN(p_bus_object.entity_name(i) = 'IBY_FNDCPT_PAYER_ASSGN_INSTR_V') THEN
4517 l_bau := TRUE;
4518 END CASE;
4519 -- Get other business object
4520 ELSIF(p_bus_object.child_bo_code(i) IS NOT NULL AND
4521 p_bus_object.business_object_code(i) = 'CUST_ACCT' AND
4522 p_bus_object.user_mandated_flag(i) = 'Y') THEN
4523 CASE
4524 WHEN (p_bus_object.child_bo_code(i) = 'CUST_ACCT_SITE') THEN
4525 l_cas := TRUE;
4526 WHEN (p_bus_object.child_bo_code(i) = 'CUST_ACCT_CONTACT') THEN
4527 l_cac := TRUE;
4528 ELSE
4529 null;
4530 END CASE;
4531 ELSIF(p_bus_object.child_bo_code(i) IS NOT NULL AND
4532 p_bus_object.business_object_code(i) = 'CUST_PROFILE' AND
4533 p_bus_object.user_mandated_flag(i) = 'Y') THEN
4534 IF(p_bus_object.entity_name(i) = 'HZ_CUST_PROFILE_AMTS') THEN
4535 l_cpa := TRUE;
4536 END IF;
4537 END IF;
4538 END LOOP;
4539
4540 -- customer profile is mandatory for customer account
4541 l_cp := TRUE;
4542
4543 IF(l_cas) THEN
4544 get_bus_obj_struct(
4545 p_bus_object_code => 'CUST_ACCT_SITE',
4546 x_bus_object => l_cas_bus_object
4547 );
4548 END IF;
4549
4550 IF(l_cac) THEN
4551 get_bus_obj_struct(
4552 p_bus_object_code => 'CUST_ACCT_CONTACT',
4553 x_bus_object => l_cac_bus_object
4554 );
4555 END IF;
4556
4557 FOR i IN 1..p_ca_v2_objs.COUNT LOOP
4558 IF(l_cas) THEN
4559 l_valid_obj := is_cas_v2_bo_comp(
4560 p_cas_v2_objs => p_ca_v2_objs(i).cust_acct_site_objs,
4561 p_bus_object => l_cas_bus_object
4562 );
4563 IF NOT(l_valid_obj) THEN
4564 RETURN FALSE;
4565 END IF;
4566 END IF;
4567
4568 IF(l_cac) THEN
4569 l_valid_obj := is_cac_bo_comp(
4570 p_cac_objs => p_ca_v2_objs(i).cust_acct_contact_objs,
4571 p_bus_object => l_cac_bus_object
4572 );
4573 IF NOT(l_valid_obj) THEN
4574 RETURN FALSE;
4575 END IF;
4576 END IF;
4577
4578 IF(l_carel AND
4579 (p_ca_v2_objs(i).acct_relate_objs IS NULL OR
4580 p_ca_v2_objs(i).acct_relate_objs.COUNT < 1)) THEN
4581 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
4582 fnd_message.set_token('OBJECT' ,'CUSTOMER_ACCOUNT_RELATE');
4583 fnd_msg_pub.add;
4584 RETURN FALSE;
4585 END IF;
4586
4587 -- for bank account use and payment method
4588 IF(l_bau AND
4589 (p_ca_v2_objs(i).bank_acct_use_objs IS NULL OR
4590 p_ca_v2_objs(i).bank_acct_use_objs.COUNT < 1)) THEN
4591 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
4592 fnd_message.set_token('OBJECT' ,'BANK_ACCOUNT');
4593 fnd_msg_pub.add;
4594 RETURN FALSE;
4595 END IF;
4596 IF(l_pm AND
4597 (p_ca_v2_objs(i).payment_method_objs IS NULL OR
4598 p_ca_v2_objs(i).payment_method_objs.COUNT < 1)) THEN
4599 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
4600 fnd_message.set_token('OBJECT' ,'PAYMENT_METHOD');
4601 fnd_msg_pub.add;
4602 RETURN FALSE;
4603 END IF;
4604
4605 IF(l_cp AND p_ca_v2_objs(i).cust_profile_obj IS NULL) THEN
4606 RETURN FALSE;
4607 ELSE
4608 IF(l_cpa AND
4609 (p_ca_v2_objs(i).cust_profile_obj.cust_profile_amt_objs IS NULL OR
4610 p_ca_v2_objs(i).cust_profile_obj.cust_profile_amt_objs.COUNT < 1)) THEN
4611 fnd_message.set_name('AR', 'HZ_API_MISSING_MANDATORY_ENT');
4612 fnd_message.set_token('OBJECT' ,'CUSTOMER_PROFILE_AMOUNT');
4613 fnd_msg_pub.add;
4614 RETURN FALSE;
4615 END IF;
4616 END IF;
4617 END LOOP;
4618
4619 RETURN TRUE;
4620 END is_ca_v2_bo_comp;
4621
4622 -- FUNCTION is_pca_v2_bo_comp
4623 --
4624 -- DESCRIPTION
4625 -- Return true if person customer object is complete. Otherwise, return false.
4626 --
4627 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4628 --
4629 -- ARGUMENTS
4630 -- IN:
4631 -- p_person_obj Person business object.
4632 -- p_ca_v2_objs List of customer account objects.
4633 -- NOTES
4634 --
4635 -- MODIFICATION HISTORY
4636 --
4637 -- 1-FEB-2008 vsegu o Created.
4638
4639 FUNCTION is_pca_v2_bo_comp(
4640 p_person_obj IN HZ_PERSON_BO,
4641 p_ca_v2_objs IN HZ_CUST_ACCT_V2_BO_TBL
4642 ) RETURN BOOLEAN IS
4643 l_per_bus_object COMPLETENESS_REC_TYPE;
4644 l_ca_bus_object COMPLETENESS_REC_TYPE;
4645 l_valid_obj BOOLEAN;
4646 BEGIN
4647 IF(p_person_obj IS NULL) THEN
4648 RETURN FALSE;
4649 END IF;
4650 IF(p_ca_v2_objs IS NULL OR p_ca_v2_objs.COUNT < 1) THEN
4651 RETURN FALSE;
4652 END IF;
4653
4654 -- check person object for person cust acct
4655 get_bus_obj_struct(
4656 p_bus_object_code => 'PERSON',
4657 x_bus_object => l_per_bus_object
4658 );
4659 l_valid_obj := is_person_bo_comp(
4660 p_person_obj => p_person_obj,
4661 p_bus_object => l_per_bus_object
4662 );
4663 IF NOT(l_valid_obj) THEN
4664 RETURN FALSE;
4665 END IF;
4666
4667 -- check cust account for person cust acct
4668 get_bus_obj_struct(
4669 p_bus_object_code => 'CUST_ACCT',
4670 x_bus_object => l_ca_bus_object
4671 );
4672 l_valid_obj := is_ca_v2_bo_comp(
4673 p_ca_v2_objs => p_ca_v2_objs,
4674 p_bus_object => l_ca_bus_object
4675 );
4676 IF NOT(l_valid_obj) THEN
4677 RETURN FALSE;
4678 END IF;
4679
4680 RETURN TRUE;
4681 END is_pca_v2_bo_comp;
4682
4683 -- FUNCTION is_oca_v2_bo_comp
4684 --
4685 -- DESCRIPTION
4686 -- Return true if organization customer object is complete.
4687 -- Otherwise, return false.
4688 --
4689 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4690 --
4691 -- ARGUMENTS
4692 -- IN:
4693 -- p_org_obj Organization business object.
4694 -- p_ca_v2_objs List of customer account objects.
4695 -- NOTES
4696 --
4697 -- MODIFICATION HISTORY
4698 --
4699 -- 1-FEB-2008 vsegu o Created.
4700
4701 FUNCTION is_oca_v2_bo_comp(
4702 p_org_obj IN HZ_ORGANIZATION_BO,
4703 p_ca_v2_objs IN HZ_CUST_ACCT_V2_BO_TBL
4704 ) RETURN BOOLEAN IS
4705 l_org_bus_object COMPLETENESS_REC_TYPE;
4706 l_ca_bus_object COMPLETENESS_REC_TYPE;
4707 l_valid_obj BOOLEAN;
4708 BEGIN
4709 IF(p_org_obj IS NULL) THEN
4710 RETURN FALSE;
4711 END IF;
4712 IF(p_ca_v2_objs IS NULL OR p_ca_v2_objs.COUNT < 1) THEN
4713 RETURN FALSE;
4714 END IF;
4715
4716 -- check organization object for org cust acct
4717 get_bus_obj_struct(
4718 p_bus_object_code => 'ORG',
4719 x_bus_object => l_org_bus_object
4720 );
4721 l_valid_obj := is_org_bo_comp(
4722 p_organization_obj => p_org_obj,
4723 p_bus_object => l_org_bus_object
4724 );
4725 IF NOT(l_valid_obj) THEN
4726 RETURN FALSE;
4727 END IF;
4728
4729 -- check cust account for org cust acct
4730 get_bus_obj_struct(
4731 p_bus_object_code => 'CUST_ACCT',
4732 x_bus_object => l_ca_bus_object
4733 );
4734 l_valid_obj := is_ca_v2_bo_comp(
4735 p_ca_v2_objs => p_ca_v2_objs,
4736 p_bus_object => l_ca_bus_object
4737 );
4738 IF NOT(l_valid_obj) THEN
4739 RETURN FALSE;
4740 END IF;
4741
4742 RETURN TRUE;
4743 END is_oca_v2_bo_comp;
4744
4745 END HZ_REGISTRY_VALIDATE_BO_PVT;
[Home] [Help]