[Home] [Help]
PACKAGE BODY: APPS.HZ_PARTY_CONTACT_V2PUB
Source
1 PACKAGE BODY HZ_PARTY_CONTACT_V2PUB AS
2 /*$Header: ARH2PCSB.pls 120.18.12000000.5 2007/09/28 11:55:04 nshinde ship $ */
3
4 --------------------------------------------
5 -- declaration of global variables and types
6 --------------------------------------------
7
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'HZ_PARTY_CONTACT_V2PUB';
9 G_DEBUG_COUNT NUMBER := 0;
10 --G_DEBUG BOOLEAN := FALSE;
11
12 ------------------------------------
13 -- declaration of private procedures
14 ------------------------------------
15
16 /*PROCEDURE enable_debug;
17
18 PROCEDURE disable_debug;
19 */
20
21 PROCEDURE do_create_org_contact (
22 p_org_contact_rec IN OUT NOCOPY ORG_CONTACT_REC_TYPE,
23 x_return_status IN OUT NOCOPY VARCHAR2,
24 x_org_contact_id OUT NOCOPY NUMBER,
25 x_party_rel_id OUT NOCOPY NUMBER,
26 x_party_id OUT NOCOPY NUMBER,
27 x_party_number OUT NOCOPY VARCHAR2
28 );
29
30 PROCEDURE do_update_org_contact(
31 p_org_contact_rec IN OUT NOCOPY ORG_CONTACT_REC_TYPE,
32 p_cont_object_version_number IN OUT NOCOPY NUMBER,
33 p_rel_object_version_number IN OUT NOCOPY NUMBER,
34 p_party_object_version_number IN OUT NOCOPY NUMBER,
35 x_return_status IN OUT NOCOPY VARCHAR2
36 );
37
38 PROCEDURE do_create_org_contact_role(
39 p_org_contact_role_rec IN OUT NOCOPY ORG_CONTACT_ROLE_REC_TYPE,
40 x_org_contact_role_id OUT NOCOPY NUMBER,
41 x_return_status IN OUT NOCOPY VARCHAR2
42 );
43
44 PROCEDURE do_update_org_contact_role(
45 p_org_contact_role_rec IN OUT NOCOPY ORG_CONTACT_ROLE_REC_TYPE,
46 p_object_version_number IN OUT NOCOPY NUMBER,
47 x_return_status IN OUT NOCOPY VARCHAR2
48 );
49
50 PROCEDURE check_obsolete_columns (
51 p_create_update_flag IN VARCHAR2,
52 p_org_contact_rec IN org_contact_rec_type,
53 p_old_org_contact_rec IN org_contact_rec_type DEFAULT NULL,
54 x_return_status IN OUT NOCOPY VARCHAR2
55 );
56
57 -----------------------------
58 -- body of private procedures
59 -----------------------------
60
61 /**
62 * PRIVATE PROCEDURE enable_debug
63 *
64 * DESCRIPTION
65 * Turn on debug mode.
66 *
67 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
68 * HZ_UTILITY_V2PUB.enable_debug
69 *
70 * MODIFICATION HISTORY
71 *
72 * 07-23-2001 Jianying Huang o Created.
73 *
74 */
75
76 /*PROCEDURE enable_debug IS
77
78 BEGIN
79
80 G_DEBUG_COUNT := G_DEBUG_COUNT + 1;
81
82 IF G_DEBUG_COUNT = 1 THEN
83 IF FND_PROFILE.value( 'HZ_API_FILE_DEBUG_ON' ) = 'Y' OR
84 FND_PROFILE.value( 'HZ_API_DBMS_DEBUG_ON' ) = 'Y'
85 THEN
86 HZ_UTILITY_V2PUB.enable_debug;
87 G_DEBUG := TRUE;
88 END IF;
89 END IF;
90
91 END enable_debug;
92 */
93
94
95 /**
96 * PRIVATE PROCEDURE disable_debug
97 *
98 * DESCRIPTION
99 * Turn off debug mode.
100 *
101 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
102 * HZ_UTILITY_V2PUB.disable_debug
103 *
104 * MODIFICATION HISTORY
105 *
106 * 07-23-2001 Jianying Huang o Created.
107 *
108 */
109
110 /*PROCEDURE disable_debug IS
111
112 BEGIN
113
114 IF G_DEBUG THEN
115 G_DEBUG_COUNT := G_DEBUG_COUNT - 1;
116
117 IF G_DEBUG_COUNT = 0 THEN
118 HZ_UTILITY_V2PUB.disable_debug;
119 G_DEBUG := FALSE;
120 END IF;
121 END IF;
122
123 END disable_debug;
124 */
125
126
127 /*===========================================================================+
128 | PROCEDURE
129 | do_create_org_contact
130 |
131 | DESCRIPTION
132 | Creates org_contact, party relationship and party.
133 |
134 | SCOPE - PRIVATE
135 |
136 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
137 |
138 | ARGUMENTS : IN:
139 | OUT:
140 | x_org_contact_id
141 | x_party_rel_id
142 | x_party_id
143 | x_party_number
144 | IN/ OUT:
145 | p_org_contact_rec
146 | x_return_status
147 |
148 | RETURNS : NONE
149 |
150 | NOTES
151 |
152 | MODIFICATION HISTORY
153 |
154 +===========================================================================*/
155
156 PROCEDURE do_create_org_contact(
157 p_org_contact_rec IN OUT NOCOPY ORG_CONTACT_REC_TYPE,
158 x_return_status IN OUT NOCOPY VARCHAR2,
159 x_org_contact_id OUT NOCOPY NUMBER,
160 x_party_rel_id OUT NOCOPY NUMBER,
161 x_party_id OUT NOCOPY NUMBER,
162 x_party_number OUT NOCOPY VARCHAR2
163 ) IS
164
165 l_org_contact_id NUMBER := p_org_contact_rec.org_contact_id;
166 l_rowid ROWID := NULL;
167 l_count NUMBER;
168 l_gen_contact_number VARCHAR2(1);
169 l_contact_number VARCHAR2(30) := p_org_contact_rec.contact_number;
170 l_msg_count NUMBER;
171 l_msg_data VARCHAR2(2000);
172 l_dummy VARCHAR2(1);
173 l_debug_prefix VARCHAR2(30);
174 l_org_status VARCHAR2(1);
175 l_object_id NUMBER;
176 l_person_pre_name HZ_PARTIES.PERSON_PRE_NAME_ADJUNCT%TYPE := NULL;
177 l_orig_sys_reference_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE;
178
179 CURSOR c_rel IS
180 SELECT BACKWARD_REL_CODE
181 FROM HZ_RELATIONSHIP_TYPES
182 WHERE RELATIONSHIP_TYPE = p_org_contact_rec.party_rel_rec.relationship_type
183 AND FORWARD_REL_CODE = p_org_contact_rec.party_rel_rec.relationship_code
184 AND SUBJECT_TYPE = p_org_contact_rec.party_rel_rec.subject_type
185 AND OBJECT_TYPE = p_org_contact_rec.party_rel_rec.object_type
186 AND STATUS = 'A';
187
188 BEGIN
189
190 -- Debug info.
191 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
192 hz_utility_v2pub.debug(p_message=>'do_create_org_contact (+)',
193 p_prefix=>l_debug_prefix,
194 p_msg_level=>fnd_log.level_procedure);
195 END IF;
196
197 -- if primary key value is passed, check for uniqueness.
198 IF l_org_contact_id IS NOT NULL AND
199 l_org_contact_id <> FND_API.G_MISS_NUM
200 THEN
201 BEGIN
202 SELECT 'Y'
203 INTO l_dummy
204 FROM HZ_ORG_CONTACTS
205 WHERE ORG_CONTACT_ID = l_org_contact_id;
206
207 FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_COLUMN');
208 FND_MESSAGE.SET_TOKEN('COLUMN', 'org_contact_id');
209 FND_MSG_PUB.ADD;
210 RAISE FND_API.G_EXC_ERROR;
211
212 EXCEPTION
213 WHEN NO_DATA_FOUND THEN
214 NULL;
215 END;
216 END IF;
217
218 l_gen_contact_number := fnd_profile.value('HZ_GENERATE_CONTACT_NUMBER');
219
220 IF l_gen_contact_number = 'Y' OR l_gen_contact_number IS NULL THEN
221 IF l_contact_number = FND_API.G_MISS_CHAR OR l_contact_number IS NULL THEN
222 l_count := 1;
223
224 WHILE l_count > 0 LOOP
225 SELECT to_char(hz_contact_numbers_s.nextval)
226 INTO l_contact_number FROM dual;
227
228 BEGIN
229 SELECT 1
230 INTO l_count
231 FROM HZ_ORG_CONTACTS
232 WHERE CONTACT_NUMBER = l_contact_number;
233 l_count := 1;
234
235 EXCEPTION
236 WHEN NO_DATA_FOUND THEN
237 l_count := 0;
238 END;
239
240 END LOOP;
241 END IF;
242 END IF;
243
244 HZ_REGISTRY_VALIDATE_V2PUB.validate_org_contact(
245 'C',
246 p_org_contact_rec,
247 l_rowid,
248 x_return_status);
249
250 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
251 RAISE FND_API.G_EXC_ERROR;
252 END IF;
253 l_object_id := p_org_contact_rec.party_rel_rec.object_id;
254 p_org_contact_rec.party_rel_rec.created_by_module := p_org_contact_rec.created_by_module;
255 p_org_contact_rec.party_rel_rec.application_id := p_org_contact_rec.application_id;
256
257 /* Bug No : 2359461 */
258 IF p_org_contact_rec.party_rel_rec.object_type = 'PERSON' AND
259 p_org_contact_rec.party_rel_rec.subject_type = 'ORGANIZATION'
260 THEN
261 OPEN c_rel;
262 FETCH c_rel INTO p_org_contact_rec.party_rel_rec.relationship_code;
263 IF c_rel%NOTFOUND THEN
264 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_FK');
265 FND_MESSAGE.SET_TOKEN('FK', 'relationship_code, subject_type, object_type');
266 FND_MESSAGE.SET_TOKEN('COLUMN', 'forward_rel_code, subject_type, object_type');
267 FND_MESSAGE.SET_TOKEN('TABLE', 'hz_relationship_types');
268 FND_MSG_PUB.ADD;
269 RAISE FND_API.G_EXC_ERROR;
270 END IF;
271 CLOSE c_rel;
272
273 p_org_contact_rec.party_rel_rec.object_id := p_org_contact_rec.party_rel_rec.subject_id;
274 p_org_contact_rec.party_rel_rec.object_type := 'ORGANIZATION';
275 p_org_contact_rec.party_rel_rec.subject_id := l_object_id;
276 p_org_contact_rec.party_rel_rec.subject_type := 'PERSON';
277 END IF;
278
279 /* Bug Fix : 2500275 */
280 IF p_org_contact_rec.party_rel_rec.subject_type = 'PERSON' AND
281 p_org_contact_rec.title IS NOT NULL AND
282 p_org_contact_rec.title <> FND_API.G_MISS_CHAR
283 THEN
284 /* HZ_PARTIES */
285 BEGIN
286 SELECT person_pre_name_adjunct
287 INTO l_person_pre_name
288 FROM hz_parties
289 WHERE party_id = p_org_contact_rec.party_rel_rec.subject_id
290 AND party_type = 'PERSON'
291 FOR UPDATE NOWAIT;
292 EXCEPTION
293 WHEN OTHERS THEN
294 fnd_message.set_name('AR', 'HZ_API_RECORD_CHANGED');
295 fnd_message.set_token('TABLE', 'HZ_PARTIES');
296 fnd_msg_pub.add;
297 RAISE FND_API.G_EXC_ERROR;
298 END;
299 IF l_person_pre_name IS NULL THEN
300 UPDATE hz_parties
301 SET person_pre_name_adjunct = p_org_contact_rec.title
302 WHERE party_id = p_org_contact_rec.party_rel_rec.subject_id
303 AND party_type = 'PERSON';
304
305 /* HZ_PERSON_PROFILES */
306 BEGIN
307 SELECT 'Y'
308 INTO l_dummy
309 FROM hz_person_profiles
310 WHERE party_id = p_org_contact_rec.party_rel_rec.subject_id
311 AND effective_end_date IS NULL
312 FOR UPDATE NOWAIT;
313 EXCEPTION
314 WHEN OTHERS THEN
315 fnd_message.set_name('AR', 'HZ_API_RECORD_CHANGED');
316 fnd_message.set_token('TABLE', 'HZ_PERSON_PROFILES');
317 fnd_msg_pub.add;
318 RAISE FND_API.G_EXC_ERROR;
319 END;
320 UPDATE hz_person_profiles
321 SET person_pre_name_adjunct = p_org_contact_rec.title
322 WHERE party_id = p_org_contact_rec.party_rel_rec.subject_id
323 AND effective_end_date IS NULL;
324 END IF;
325 END IF;
326 --
327 -- create party relationship.
328 --
329 HZ_RELATIONSHIP_V2PUB.create_relationship (
330 p_relationship_rec => p_org_contact_rec.party_rel_rec,
331 x_relationship_id => x_party_rel_id,
332 x_party_id => x_party_id,
333 x_party_number => x_party_number,
334 x_return_status => x_return_status,
335 x_msg_count => l_msg_count,
336 x_msg_data => l_msg_data,
337 p_create_org_contact => 'N'
338 );
339
340 p_org_contact_rec.party_rel_rec.party_rec.party_id := x_party_id;
341 p_org_contact_rec.party_rel_rec.party_rec.party_number := x_party_number;
342 p_org_contact_rec.party_rel_rec.relationship_id := x_party_rel_id;
343 l_org_status := p_org_contact_rec.party_rel_rec.status;
344
345 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
346 RAISE FND_API.G_EXC_ERROR;
347 END IF;
348
349 -- this is for orig_system_reference defaulting
350 IF p_org_contact_rec.org_contact_id = FND_API.G_MISS_NUM THEN
351 p_org_contact_rec.org_contact_id := NULL;
352 END IF;
353
354 -- Debug info.
355 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
356 hz_utility_v2pub.debug(p_message=>'HZ_ORG_CONTACTS_PKG.Insert_Row (+)',
357 p_prefix=>l_debug_prefix,
358 p_msg_level=>fnd_log.level_procedure);
359 END IF;
360
361 -- call table-handler.
362 HZ_ORG_CONTACTS_PKG.Insert_Row (
363 X_ORG_CONTACT_ID => p_org_contact_rec.org_contact_id,
364 X_PARTY_RELATIONSHIP_ID => x_party_rel_id,
365 X_COMMENTS => p_org_contact_rec.comments,
366 X_CONTACT_NUMBER => l_contact_number,
367 X_DEPARTMENT_CODE => p_org_contact_rec.department_code,
368 X_DEPARTMENT => p_org_contact_rec.department,
369 X_TITLE => p_org_contact_rec.title,
370 X_JOB_TITLE => p_org_contact_rec.job_title,
371 X_DECISION_MAKER_FLAG => p_org_contact_rec.decision_maker_flag,
372 X_JOB_TITLE_CODE => p_org_contact_rec.job_title_code,
373 X_REFERENCE_USE_FLAG => p_org_contact_rec.reference_use_flag,
374 X_RANK => p_org_contact_rec.rank,
375 X_ORIG_SYSTEM_REFERENCE => p_org_contact_rec.orig_system_reference,
376 X_ATTRIBUTE_CATEGORY => p_org_contact_rec.attribute_category,
377 X_ATTRIBUTE1 => p_org_contact_rec.attribute1,
378 X_ATTRIBUTE2 => p_org_contact_rec.attribute2,
379 X_ATTRIBUTE3 => p_org_contact_rec.attribute3,
380 X_ATTRIBUTE4 => p_org_contact_rec.attribute4,
381 X_ATTRIBUTE5 => p_org_contact_rec.attribute5,
382 X_ATTRIBUTE6 => p_org_contact_rec.attribute6,
383 X_ATTRIBUTE7 => p_org_contact_rec.attribute7,
384 X_ATTRIBUTE8 => p_org_contact_rec.attribute8,
385 X_ATTRIBUTE9 => p_org_contact_rec.attribute9,
386 X_ATTRIBUTE10 => p_org_contact_rec.attribute10,
387 X_ATTRIBUTE11 => p_org_contact_rec.attribute11,
388 X_ATTRIBUTE12 => p_org_contact_rec.attribute12,
389 X_ATTRIBUTE13 => p_org_contact_rec.attribute13,
390 X_ATTRIBUTE14 => p_org_contact_rec.attribute14,
391 X_ATTRIBUTE15 => p_org_contact_rec.attribute15,
392 X_ATTRIBUTE16 => p_org_contact_rec.attribute16,
393 X_ATTRIBUTE17 => p_org_contact_rec.attribute17,
394 X_ATTRIBUTE18 => p_org_contact_rec.attribute18,
395 X_ATTRIBUTE19 => p_org_contact_rec.attribute19,
396 X_ATTRIBUTE20 => p_org_contact_rec.attribute20,
397 X_ATTRIBUTE21 => p_org_contact_rec.attribute21,
398 X_ATTRIBUTE22 => p_org_contact_rec.attribute22,
399 X_ATTRIBUTE23 => p_org_contact_rec.attribute23,
400 X_ATTRIBUTE24 => p_org_contact_rec.attribute24,
401 X_PARTY_SITE_ID => p_org_contact_rec.party_site_id,
402 X_OBJECT_VERSION_NUMBER => 1,
403 X_CREATED_BY_MODULE => p_org_contact_rec.created_by_module,
404 X_APPLICATION_ID => p_org_contact_rec.application_id,
405 X_STATUS => l_org_status
406 );
407
408 -- Debug info.
409 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
410 hz_utility_v2pub.debug(p_message=>'HZ_ORG_CONTACTS_PKG.Insert_Row (-)',
411 p_prefix=>l_debug_prefix,
412 p_msg_level=>fnd_log.level_procedure);
413 END IF;
414
415
416
417 if p_org_contact_rec.orig_system is not null
418 and p_org_contact_rec.orig_system <>fnd_api.g_miss_char
419 then
420 l_orig_sys_reference_rec.orig_system := p_org_contact_rec.orig_system;
421 l_orig_sys_reference_rec.orig_system_reference := p_org_contact_rec.orig_system_reference;
422 l_orig_sys_reference_rec.owner_table_name := 'HZ_ORG_CONTACTS';
423 l_orig_sys_reference_rec.owner_table_id := p_org_contact_rec.org_contact_id;
424 l_orig_sys_reference_rec.created_by_module := p_org_contact_rec.created_by_module;
425
426 hz_orig_system_ref_pub.create_orig_system_reference(
427 FND_API.G_FALSE,
428 l_orig_sys_reference_rec,
429 x_return_status,
430 l_msg_count,
431 l_msg_data);
432 IF x_return_status <> fnd_api.g_ret_sts_success THEN
433 RAISE FND_API.G_EXC_ERROR;
434 END IF;
435 end if;
436
437 x_org_contact_id := p_org_contact_rec.org_contact_id;
438
439 -- Debug info.
440 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
441 hz_utility_v2pub.debug(p_message=>'do_create_org_contact (-)',
442 p_prefix=>l_debug_prefix,
443 p_msg_level=>fnd_log.level_procedure);
444 END IF;
445
446 END do_create_org_contact;
447
448 /*===========================================================================+
449 | PROCEDURE
450 | do_update_org_contact
451 |
452 | DESCRIPTION
453 | Updates org_contact, party relationship and party.
454 |
455 | SCOPE - PRIVATE
456 |
457 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
458 |
459 | ARGUMENTS : IN:
460 | OUT:
461 | IN/ OUT:
462 | p_org_contact_rec
463 | p_org_contact_last_update_date
464 | p_party_rel_last_update_date
465 | p_party_last_update_date
466 | x_return_status
467 |
468 | RETURNS : NONE
469 |
470 | NOTES
471 |
472 | MODIFICATION HISTORY
473 | 28-SEP-2007 Neeraj Shinde BUG# 6335274:1.relationship_id is non-updatable field.
474 | If relationship_id is not supplied,
475 | do not fetch it from HZ_ORG_CONTACTS.
476 | 2.If update_relationship API results
477 | in error, Error has to be raised.
478 +===========================================================================*/
479
480 PROCEDURE do_update_org_contact(
481 p_org_contact_rec IN OUT NOCOPY ORG_CONTACT_REC_TYPE,
482 p_cont_object_version_number IN OUT NOCOPY NUMBER,
483 p_rel_object_version_number IN OUT NOCOPY NUMBER,
484 p_party_object_version_number IN OUT NOCOPY NUMBER,
485 x_return_status IN OUT NOCOPY VARCHAR2
486 ) IS
487
488 l_rowid ROWID;
489 l_party_relationship_id NUMBER := p_org_contact_rec.party_rel_rec.relationship_id;
490 l_api_version CONSTANT NUMBER := 1.0;
491 l_old_org_contact_rec ORG_CONTACT_REC_TYPE;
492 l_msg_count NUMBER;
493 l_msg_data VARCHAR2(2000);
494 l_object_version_number NUMBER;
495 l_debug_prefix VARCHAR2(30);
496 l_object_id NUMBER;
497 l_org_status VARCHAR2(1);
498 l_person_pre_name HZ_PERSON_PROFILES.PERSON_PRE_NAME_ADJUNCT%TYPE := NULL;
499 l_party_id HZ_PARTIES.PARTY_ID%TYPE := NULL;
500 BEGIN
501 -- Debug info.
502 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
503 hz_utility_v2pub.debug(p_message=>'do_update_org_contact (-)',
504 p_prefix=>l_debug_prefix,
505 p_msg_level=>fnd_log.level_procedure);
506 END IF;
507
508
509 -- check whether record has been updated by another user. If not, lock it.
510 BEGIN
511 SELECT ROWID,
512 OBJECT_VERSION_NUMBER
513 INTO l_rowid,
514 l_object_version_number
515 FROM HZ_ORG_CONTACTS
516 WHERE ORG_CONTACT_ID = p_org_contact_rec.org_contact_id
517 FOR UPDATE OF ORG_CONTACT_ID NOWAIT;
518
519 IF NOT (
520 ( p_cont_object_version_number IS NULL AND l_object_version_number IS NULL ) OR
521 ( p_cont_object_version_number IS NOT NULL AND
522 l_object_version_number IS NOT NULL AND
523 p_cont_object_version_number = l_object_version_number ) )
524 THEN
525 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_RECORD_CHANGED' );
526 FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_org_contacts' );
527 FND_MSG_PUB.ADD;
528 RAISE FND_API.G_EXC_ERROR;
529 END IF;
530
531 p_cont_object_version_number := NVL( l_object_version_number, 1 ) + 1;
532
533 EXCEPTION
534 WHEN NO_DATA_FOUND THEN
535 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_RECORD' );
536 FND_MESSAGE.SET_TOKEN( 'RECORD', 'org contact' );
537 FND_MESSAGE.SET_TOKEN( 'VALUE',
538 NVL( TO_CHAR( p_org_contact_rec.org_contact_id ), 'null' ) );
539 FND_MSG_PUB.ADD;
540 RAISE FND_API.G_EXC_ERROR;
541 END;
542 --Bug Fix 2188731
543 IF p_org_contact_rec.party_rel_rec.relationship_id IS NOT NULL THEN
544
545 BEGIN
546
547 SELECT OBJECT_ID
548 INTO l_object_id
549 FROM HZ_RELATIONSHIPS
550 WHERE RELATIONSHIP_ID = p_org_contact_rec.party_rel_rec.relationship_id
551 AND DIRECTIONAL_FLAG = 'F';
552
553
554 IF p_org_contact_rec.party_rel_rec.object_id IS NULL
555 THEN
556 p_org_contact_rec.party_rel_rec.object_id := l_object_id;
557 END IF;
558
559
560 EXCEPTION
561 WHEN NO_DATA_FOUND THEN
562 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_RECORD' );
563 FND_MESSAGE.SET_TOKEN( 'RECORD', 'party relationship' );
564 FND_MESSAGE.SET_TOKEN( 'VALUE',
565 NVL( TO_CHAR( p_org_contact_rec.party_rel_rec.relationship_id ), 'null' ) );
566 FND_MSG_PUB.ADD;
567 RAISE FND_API.G_EXC_ERROR;
568 END;
569 END IF;
570 -- call for validations.
571 HZ_REGISTRY_VALIDATE_V2PUB.validate_org_contact(
572 'U',
573 p_org_contact_rec,
574 l_rowid,
575 x_return_status);
576
577 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
578 RAISE FND_API.G_EXC_ERROR;
579 END IF;
580
581 l_org_status := p_org_contact_rec.party_rel_rec.status ;
582
583 --BUG# 6335274: Start
584 --If relationship_id is NOT supplied,do not fetch it from the table.
585 -- select rowid and other fields for later uses.
586 /*
587 SELECT ROWID,
588 PARTY_RELATIONSHIP_ID
589 INTO l_rowid, l_party_relationship_id
590 FROM HZ_ORG_CONTACTS
591 WHERE ORG_CONTACT_ID = p_org_contact_rec.org_contact_id;*/
592
593 IF (l_party_relationship_id IS NOT NULL) THEN
594 --user passes optional relationship_id
595 SELECT ROWID,
596 PARTY_RELATIONSHIP_ID
597 INTO l_rowid, l_party_relationship_id
598 FROM HZ_ORG_CONTACTS
599 WHERE ORG_CONTACT_ID = p_org_contact_rec.org_contact_id;
600 END IF;
601 --BUG# 6335274: End
602
603
604 /* Bug No : 2500275 */
605
606 IF p_org_contact_rec.title IS NOT NULL AND
607 p_org_contact_rec.title <> FND_API.G_MISS_CHAR
608 THEN
609 BEGIN
610 SELECT P.PERSON_PRE_NAME_ADJUNCT,P.PARTY_ID INTO
611 l_person_pre_name,l_party_id
612 FROM HZ_PARTIES P, HZ_RELATIONSHIPS R, HZ_ORG_CONTACTS C
613 WHERE
614 C.ORG_CONTACT_ID = p_org_contact_rec.org_contact_id
615 AND R.RELATIONSHIP_ID = C.PARTY_RELATIONSHIP_ID
616 AND R.DIRECTIONAL_FLAG = 'F'
617 AND R.SUBJECT_ID = P.PARTY_ID
618 AND P.PARTY_TYPE = 'PERSON';
619
620 IF l_person_pre_name IS NULL THEN
621 UPDATE HZ_PARTIES
622 SET PERSON_PRE_NAME_ADJUNCT = p_org_contact_rec.title
623 WHERE PARTY_ID = l_party_id;
624
625 UPDATE HZ_PERSON_PROFILES
626 SET PERSON_PRE_NAME_ADJUNCT = p_org_contact_rec.title
627 WHERE PARTY_ID = l_party_id
628 AND EFFECTIVE_END_DATE IS NULL;
629 END IF;
630 EXCEPTION
631 WHEN NO_DATA_FOUND THEN
632 NULL;
633 END;
634 END IF;
635
636 if (p_org_contact_rec.orig_system is not null
637 and p_org_contact_rec.orig_system <>fnd_api.g_miss_char)
638 and (p_org_contact_rec.orig_system_reference is not null
639 and p_org_contact_rec.orig_system_reference <>fnd_api.g_miss_char)
640 then
641 p_org_contact_rec.orig_system_reference := null;
642 -- In mosr, we have bypassed osr nonupdateable validation
643 -- but we should not update existing osr, set it to null
644 end if;
645
646 -- Debug info.
647 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
648 hz_utility_v2pub.debug(p_message=>'HZ_ORG_CONTACTS_PKG.Update_Row (+) ',
649 p_prefix=>l_debug_prefix,
650 p_msg_level=>fnd_log.level_procedure);
651 END IF;
652 -- call to table-handler
653 HZ_ORG_CONTACTS_PKG.Update_Row (
654 X_Rowid => l_rowid,
655 X_ORG_CONTACT_ID => p_org_contact_rec.org_contact_id,
656 X_PARTY_RELATIONSHIP_ID => p_org_contact_rec.party_rel_rec.relationship_id,
657 X_COMMENTS => p_org_contact_rec.comments,
658 X_CONTACT_NUMBER => p_org_contact_rec.contact_number,
659 X_DEPARTMENT_CODE => p_org_contact_rec.department_code,
660 X_DEPARTMENT => p_org_contact_rec.department,
661 X_TITLE => p_org_contact_rec.title,
662 X_JOB_TITLE => p_org_contact_rec.job_title,
663 X_DECISION_MAKER_FLAG => p_org_contact_rec.decision_maker_flag,
664 X_JOB_TITLE_CODE => p_org_contact_rec.job_title_code,
665 X_REFERENCE_USE_FLAG => p_org_contact_rec.reference_use_flag,
666 X_RANK => p_org_contact_rec.rank,
667 X_ORIG_SYSTEM_REFERENCE => p_org_contact_rec.orig_system_reference,
668 X_ATTRIBUTE_CATEGORY => p_org_contact_rec.attribute_category,
669 X_ATTRIBUTE1 => p_org_contact_rec.attribute1,
670 X_ATTRIBUTE2 => p_org_contact_rec.attribute2,
671 X_ATTRIBUTE3 => p_org_contact_rec.attribute3,
672 X_ATTRIBUTE4 => p_org_contact_rec.attribute4,
673 X_ATTRIBUTE5 => p_org_contact_rec.attribute5,
674 X_ATTRIBUTE6 => p_org_contact_rec.attribute6,
675 X_ATTRIBUTE7 => p_org_contact_rec.attribute7,
676 X_ATTRIBUTE8 => p_org_contact_rec.attribute8,
677 X_ATTRIBUTE9 => p_org_contact_rec.attribute9,
678 X_ATTRIBUTE10 => p_org_contact_rec.attribute10,
679 X_ATTRIBUTE11 => p_org_contact_rec.attribute11,
680 X_ATTRIBUTE12 => p_org_contact_rec.attribute12,
681 X_ATTRIBUTE13 => p_org_contact_rec.attribute13,
682 X_ATTRIBUTE14 => p_org_contact_rec.attribute14,
683 X_ATTRIBUTE15 => p_org_contact_rec.attribute15,
684 X_ATTRIBUTE16 => p_org_contact_rec.attribute16,
685 X_ATTRIBUTE17 => p_org_contact_rec.attribute17,
686 X_ATTRIBUTE18 => p_org_contact_rec.attribute18,
687 X_ATTRIBUTE19 => p_org_contact_rec.attribute19,
688 X_ATTRIBUTE20 => p_org_contact_rec.attribute20,
689 X_ATTRIBUTE21 => p_org_contact_rec.attribute21,
690 X_ATTRIBUTE22 => p_org_contact_rec.attribute22,
691 X_ATTRIBUTE23 => p_org_contact_rec.attribute23,
692 X_ATTRIBUTE24 => p_org_contact_rec.attribute24,
693 X_PARTY_SITE_ID => p_org_contact_rec.party_site_id,
694 X_OBJECT_VERSION_NUMBER => p_cont_object_version_number,
695 X_CREATED_BY_MODULE => p_org_contact_rec.created_by_module,
696 X_APPLICATION_ID => p_org_contact_rec.application_id,
697 X_STATUS => l_org_status
698 );
699 -- Debug info.
700 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
701 hz_utility_v2pub.debug(p_message=>'HZ_ORG_CONTACTS_PKG.Update_Row (-) ',
702 p_prefix=>l_debug_prefix,
703 p_msg_level=>fnd_log.level_procedure);
704 END IF;
705
706 -- update party relationship. party_relationship_id in hz_org_contacts is
707 -- non-updateable and l_party_relationship_id is selected from table.
708 IF nvl(p_rel_object_version_number, 1) <> FND_API.G_MISS_NUM
709 THEN
710 IF l_party_relationship_id IS NOT NULL THEN
711 p_org_contact_rec.party_rel_rec.relationship_id := l_party_relationship_id;
712 p_org_contact_rec.party_rel_rec.created_by_module := p_org_contact_rec.created_by_module;
713 p_org_contact_rec.party_rel_rec.application_id := p_org_contact_rec.application_id;
714
715 /* Bug No : 2408693 */
716 /* Bug No : 5080436 .Commented the code for toggling*/
717 /* IF p_org_contact_rec.party_rel_rec.object_type = 'PERSON' AND
718 p_org_contact_rec.party_rel_rec.subject_type = 'ORGANIZATION'
719 THEN
720 BEGIN
721 SELECT SUBJECT_ID,OBJECT_ID
722 INTO p_org_contact_rec.party_rel_rec.object_id,
723 p_org_contact_rec.party_rel_rec.subject_id
724 FROM HZ_RELATIONSHIPS
725 WHERE RELATIONSHIP_ID = p_org_contact_rec.party_rel_rec.relationship_id
726 AND DIRECTIONAL_FLAG = 'F';
727
728 EXCEPTION
729 WHEN NO_DATA_FOUND THEN
730 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_RECORD' );
731 FND_MESSAGE.SET_TOKEN( 'RECORD', 'party relationship' );
732 FND_MESSAGE.SET_TOKEN( 'VALUE', NVL( TO_CHAR( p_org_contact_rec.party_rel_rec.relationship_id ), 'null' ) );
733 FND_MSG_PUB.ADD;
734 RAISE FND_API.G_EXC_ERROR;
735 END;
736
737 p_org_contact_rec.party_rel_rec.object_type := 'ORGANIZATION';
738 p_org_contact_rec.party_rel_rec.subject_type := 'PERSON';
739 END IF; */
740
741 HZ_RELATIONSHIP_V2PUB.update_relationship (
742 p_relationship_rec => p_org_contact_rec.party_rel_rec,
743 p_object_version_number => p_rel_object_version_number,
744 p_party_object_version_number => p_party_object_version_number,
745 x_return_status => x_return_status,
746 x_msg_count => l_msg_count,
747 x_msg_data => l_msg_data
748 );
749 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
750 RAISE FND_API.G_EXC_ERROR; --BUG# 6335274
751 END IF;
752 END IF;
753 END IF;
754
755 -- Debug info.
756 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
757 hz_utility_v2pub.debug(p_message=>'do_update_org_contact (-)',
758 p_prefix=>l_debug_prefix,
759 p_msg_level=>fnd_log.level_procedure);
760 END IF;
761
762 END do_update_org_contact;
763
764
765 /*===========================================================================+
766 | PROCEDURE
767 | do_create_org_contact_role
768 |
769 | DESCRIPTION
770 | Creates org_contact_role.
771 |
772 | SCOPE - PRIVATE
773 |
774 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
775 |
776 | ARGUMENTS : IN:
777 | OUT:
778 | x_org_contact_role_id
779 | IN/ OUT:
780 | p_org_contact_role_rec
781 | x_return_status
782 |
783 | RETURNS : NONE
784 |
785 | NOTES
786 |
787 | MODIFICATION HISTORY
788 |
789 +===========================================================================*/
790
791 PROCEDURE do_create_org_contact_role(
792 p_org_contact_role_rec IN OUT NOCOPY ORG_CONTACT_ROLE_REC_TYPE,
793 x_org_contact_role_id OUT NOCOPY NUMBER,
794 x_return_status IN OUT NOCOPY VARCHAR2
795 ) IS
796
797 l_org_contact_role_id NUMBER := p_org_contact_role_rec.org_contact_role_id;
798 l_rowid ROWID := NULL;
799 l_count NUMBER;
800 l_msg_count NUMBER;
801 l_msg_data VARCHAR2(2000);
802 l_dummy VARCHAR2(1);
803 l_debug_prefix VARCHAR2(30);
804 l_orig_sys_reference_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE;
805 l_object_version_number NUMBER; -- Added: Bug#6411541
806 BEGIN
807
808 -- Debug info.
809 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
810 hz_utility_v2pub.debug(p_message=>'do_create_org_contact_role (+)',
811 p_prefix=>l_debug_prefix,
812 p_msg_level=>fnd_log.level_procedure);
813 END IF;
814
815 -- if primary key value is passed, check for uniqueness.
816 IF l_org_contact_role_id IS NOT NULL AND
817 l_org_contact_role_id <> FND_API.G_MISS_NUM
818 THEN
819 BEGIN
820 SELECT 'Y'
821 INTO l_dummy
822 FROM HZ_ORG_CONTACT_ROLES
823 WHERE ORG_CONTACT_ROLE_ID = l_org_contact_role_id;
824
825 FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_COLUMN');
826 FND_MESSAGE.SET_TOKEN('COLUMN', 'org_contact_role_id');
827 FND_MSG_PUB.ADD;
828 RAISE FND_API.G_EXC_ERROR;
829
830 EXCEPTION
831 WHEN NO_DATA_FOUND THEN
832 NULL;
833 END;
834 END IF;
835
836 HZ_REGISTRY_VALIDATE_V2PUB.validate_org_contact_role(
837 'C',
838 p_org_contact_role_rec,
839 l_rowid,
840 x_return_status);
841
842 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
843 RAISE FND_API.G_EXC_ERROR;
844 END IF;
845
846 -- this is for orig_system_reference defaulting
847 IF p_org_contact_role_rec.org_contact_role_id = FND_API.G_MISS_NUM THEN
848 p_org_contact_role_rec.org_contact_role_id := NULL;
849 END IF;
850
851 /* Bug#6411541: Start of changes made by Neeraj Shinde
852 If the role was previously defined for the contact then a
853 record exists in Inactive status. This record needs to be updated to Active. */
854
855 -- If the record exist, update status = 'Active', Else continue with Insert
856
857 IF (p_org_contact_role_rec.org_contact_id IS NOT NULL AND
858 p_org_contact_role_rec.role_type IS NOT NULL) THEN
859
860 BEGIN
861 SELECT ORG_CONTACT_ROLE_ID,
862 OBJECT_VERSION_NUMBER,
863 ROWID
864 INTO l_org_contact_role_id,
865 l_object_version_number,
866 l_rowid
867 FROM HZ_ORG_CONTACT_ROLES
868 WHERE ORG_CONTACT_ID = p_org_contact_role_rec.org_contact_id
869 AND ROLE_TYPE = p_org_contact_role_rec.role_type
870 AND STATUS = 'I';
871
872 l_object_version_number := nvl(l_object_version_number, 1) + 1;
873
874 -- call to table-handler to update the staus to active
875 HZ_ORG_CONTACT_ROLES_PKG.Update_Row (
876 X_Rowid => l_rowid,
877 X_ORG_CONTACT_ROLE_ID => l_org_contact_role_id,
878 X_ORG_CONTACT_ID => p_org_contact_role_rec.org_contact_id,
879 X_ROLE_TYPE => p_org_contact_role_rec.role_type,
880 X_ROLE_LEVEL => p_org_contact_role_rec.role_level,
881 X_PRIMARY_FLAG => p_org_contact_role_rec.primary_flag,
882 X_ORIG_SYSTEM_REFERENCE => p_org_contact_role_rec.orig_system_reference,
883 X_PRIMARY_CON_PER_ROLE_TYPE => p_org_contact_role_rec.primary_contact_per_role_type,
884 X_STATUS => 'A',
885 X_OBJECT_VERSION_NUMBER => l_object_version_number,
886 X_CREATED_BY_MODULE => p_org_contact_role_rec.created_by_module,
887 X_APPLICATION_ID => p_org_contact_role_rec.application_id
888 );
889
890 x_org_contact_role_id := l_org_contact_role_id;
891
892 EXCEPTION
893 WHEN NO_DATA_FOUND THEN
894
895 -- Debug info.
896 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
897 hz_utility_v2pub.debug(p_message=>'HZ_ORG_CONTACT_ROLES_PKG.Insert_Row (+)',
898 p_prefix=>l_debug_prefix,
899 p_msg_level=>fnd_log.level_procedure);
900 END IF;
901
902 -- call table-handler.
903 HZ_ORG_CONTACT_ROLES_PKG.Insert_Row (
904 X_ORG_CONTACT_ROLE_ID => p_org_contact_role_rec.org_contact_role_id,
905 X_ORG_CONTACT_ID => p_org_contact_role_rec.org_contact_id,
906 X_ROLE_TYPE => p_org_contact_role_rec.role_type,
907 X_ROLE_LEVEL => p_org_contact_role_rec.role_level,
908 X_PRIMARY_FLAG => p_org_contact_role_rec.primary_flag,
909 X_ORIG_SYSTEM_REFERENCE => p_org_contact_role_rec.orig_system_reference,
910 X_PRIMARY_CON_PER_ROLE_TYPE => p_org_contact_role_rec.primary_contact_per_role_type,
911 X_STATUS => p_org_contact_role_rec.status,
912 X_OBJECT_VERSION_NUMBER => 1,
913 X_CREATED_BY_MODULE => p_org_contact_role_rec.created_by_module,
914 X_APPLICATION_ID => p_org_contact_role_rec.application_id
915 );
916
917 x_org_contact_role_id := p_org_contact_role_rec.org_contact_role_id;
918
919 -- Debug info.
920 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
921 hz_utility_v2pub.debug(p_message=>'HZ_ORG_CONTACT_ROLES_PKG.Insert_Row (-) ' ||
922 'x_org_contact_role_id = ' || x_org_contact_role_id,
923 p_prefix=>l_debug_prefix,
924 p_msg_level=>fnd_log.level_procedure);
925 END IF;
926 END;
927 END IF;
928
929 -- Bug#6411541: End of changes made by Neeraj
930
931 if p_org_contact_role_rec.orig_system is not null
932 and p_org_contact_role_rec.orig_system <>fnd_api.g_miss_char
933 then
934 l_orig_sys_reference_rec.orig_system := p_org_contact_role_rec.orig_system;
935 l_orig_sys_reference_rec.orig_system_reference := p_org_contact_role_rec.orig_system_reference;
936 l_orig_sys_reference_rec.owner_table_name := 'HZ_ORG_CONTACT_ROLES';
937 l_orig_sys_reference_rec.owner_table_id := p_org_contact_role_rec.org_contact_role_id;
938 -- Bug# 6338010: Start changes made by Neeraj
939 -- Created by module should not be null
940 l_orig_sys_reference_rec.created_by_module := p_org_contact_role_rec.created_by_module;
941 -- End changes made by Neeraj
942
943 hz_orig_system_ref_pub.create_orig_system_reference(
944 FND_API.G_FALSE,
945 l_orig_sys_reference_rec,
946 x_return_status,
947 l_msg_count,
948 l_msg_data);
949 IF x_return_status <> fnd_api.g_ret_sts_success THEN
950 RAISE FND_API.G_EXC_ERROR;
951 END IF;
952 end if;
953
954 -- Debug info.
955 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
956 hz_utility_v2pub.debug(p_message=>'do_create_org_contact_role (-)',
957 p_prefix=>l_debug_prefix,
958 p_msg_level=>fnd_log.level_procedure);
959 END IF;
960
961 END do_create_org_contact_role;
962
963
964 /*===========================================================================+
965 | PROCEDURE
966 | do_update_org_contact_role
967 |
968 | DESCRIPTION
969 | Updates org_contact_role.
970 |
971 | SCOPE - PRIVATE
972 |
973 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
974 |
975 | ARGUMENTS : IN:
976 | OUT:
977 | IN/ OUT:
978 | p_org_contact_role_rec
979 | p_last_update_date
980 | x_return_status
981 |
982 | RETURNS : NONE
983 |
984 | NOTES
985 |
986 | MODIFICATION HISTORY
987 |
988 +===========================================================================*/
989
990 PROCEDURE do_update_org_contact_role(
991 p_org_contact_role_rec IN OUT NOCOPY ORG_CONTACT_ROLE_REC_TYPE,
992 p_object_version_number IN OUT NOCOPY NUMBER,
993 x_return_status IN OUT NOCOPY VARCHAR2
994 ) IS
995
996 l_object_version_number NUMBER;
997 l_rowid ROWID;
998 l_msg_count NUMBER;
999 l_msg_data VARCHAR2(2000);
1000 l_debug_prefix VARCHAR2(30);
1001
1002 BEGIN
1003
1004 -- Debug info.
1005 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1006 hz_utility_v2pub.debug(p_message=>'do_update_org_contact_role (+)',
1007 p_prefix=>l_debug_prefix,
1008 p_msg_level=>fnd_log.level_procedure);
1009 END IF;
1010
1011 -- check whether record has been updated by another user. If not, lock it.
1012 BEGIN
1013 SELECT OBJECT_VERSION_NUMBER,
1014 ROWID
1015 INTO l_object_version_number,
1016 l_rowid
1017 FROM HZ_ORG_CONTACT_ROLES
1018 WHERE ORG_CONTACT_ROLE_ID = p_org_contact_role_rec.org_contact_role_id
1019 FOR UPDATE OF ORG_CONTACT_ROLE_ID NOWAIT;
1020
1021 IF NOT (
1022 ( p_object_version_number IS NULL AND l_object_version_number IS NULL ) OR
1023 ( p_object_version_number IS NOT NULL AND
1024 l_object_version_number IS NOT NULL AND
1025 p_object_version_number = l_object_version_number ) )
1026 THEN
1027 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
1028 FND_MESSAGE.SET_TOKEN('TABLE', 'hz_org_contact_roles');
1029 FND_MSG_PUB.ADD;
1030 RAISE FND_API.G_EXC_ERROR;
1031 END IF;
1032
1033 p_object_version_number := nvl(l_object_version_number, 1) + 1;
1034
1035 EXCEPTION WHEN NO_DATA_FOUND THEN
1036 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
1037 FND_MESSAGE.SET_TOKEN('RECORD', 'contact role');
1038 FND_MESSAGE.SET_TOKEN('VALUE', NVL( TO_CHAR(p_org_contact_role_rec.org_contact_role_id ), 'null' ) );
1039 FND_MSG_PUB.ADD;
1040 RAISE FND_API.G_EXC_ERROR;
1041 END;
1042
1043 -- call for validations.
1044 HZ_REGISTRY_VALIDATE_V2PUB.validate_org_contact_role(
1045 'U',
1046 p_org_contact_role_rec,
1047 l_rowid,
1048 x_return_status);
1049
1050 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1051 RAISE FND_API.G_EXC_ERROR;
1052 END IF;
1053
1054 if (p_org_contact_role_rec.orig_system is not null
1055 and p_org_contact_role_rec.orig_system <>fnd_api.g_miss_char)
1056 and (p_org_contact_role_rec.orig_system_reference is not null
1057 and p_org_contact_role_rec.orig_system_reference <>fnd_api.g_miss_char)
1058 then
1059 p_org_contact_role_rec.orig_system_reference := null;
1060 -- In mosr, we have bypassed osr nonupdateable validation
1061 -- but we should not update existing osr, set it to null
1062 end if;
1063
1064 -- Debug info.
1065 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1066 hz_utility_v2pub.debug(p_message=>'HZ_ORG_CONTACT_ROLES_PKG.Update_Row (+) ',
1067 p_prefix=>l_debug_prefix,
1068 p_msg_level=>fnd_log.level_procedure);
1069 END IF;
1070
1071 -- call to table-handler
1072 HZ_ORG_CONTACT_ROLES_PKG.Update_Row (
1073 X_Rowid => l_rowid,
1074 X_ORG_CONTACT_ROLE_ID => p_org_contact_role_rec.org_contact_role_id,
1075 X_ORG_CONTACT_ID => p_org_contact_role_rec.org_contact_id,
1076 X_ROLE_TYPE => p_org_contact_role_rec.role_type,
1077 X_ROLE_LEVEL => p_org_contact_role_rec.role_level,
1078 X_PRIMARY_FLAG => p_org_contact_role_rec.primary_flag,
1079 X_ORIG_SYSTEM_REFERENCE => p_org_contact_role_rec.orig_system_reference,
1080 X_PRIMARY_CON_PER_ROLE_TYPE => p_org_contact_role_rec.primary_contact_per_role_type,
1081 X_STATUS => p_org_contact_role_rec.status,
1082 X_OBJECT_VERSION_NUMBER => p_object_version_number,
1083 X_CREATED_BY_MODULE => p_org_contact_role_rec.created_by_module,
1084 X_APPLICATION_ID => p_org_contact_role_rec.application_id
1085 );
1086
1087 -- Debug info.
1088 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1089 hz_utility_v2pub.debug(p_message=>'HZ_ORG_CONTACT_ROLES_PKG.Update_Row (-) ',
1090 p_prefix=>l_debug_prefix,
1091 p_msg_level=>fnd_log.level_procedure);
1092 END IF;
1093
1094 -- Debug info.
1095 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1096 hz_utility_v2pub.debug(p_message=>'do_update_org_contact_role (-)',
1097 p_prefix=>l_debug_prefix,
1098 p_msg_level=>fnd_log.level_procedure);
1099 END IF;
1100
1101 END do_update_org_contact_role;
1102
1103
1104 ----------------------------
1105 -- body of public procedures
1106 ----------------------------
1107
1108 /*===========================================================================+
1109 | PROCEDURE
1110 | create_org_contact
1111 |
1112 | DESCRIPTION
1113 | Creates org_contact and party for org_contact.
1114 |
1115 | SCOPE - PUBLIC
1116 |
1117 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1118 |
1119 | ARGUMENTS : IN:
1120 | p_init_msg_list
1121 | p_org_contact_rec
1122 | OUT:
1123 | x_return_status
1124 | x_msg_count
1125 | x_msg_data
1126 | x_org_contact_id
1127 | x_party_rel_id
1128 | x_party_id
1129 | x_party_number
1130 | IN/ OUT:
1131 |
1132 | RETURNS : NONE
1133 |
1134 | NOTES
1135 |
1136 | MODIFICATION HISTORY
1137 |
1138 +===========================================================================*/
1139
1140 PROCEDURE create_org_contact (
1141 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
1142 p_org_contact_rec IN ORG_CONTACT_REC_TYPE,
1143 x_org_contact_id OUT NOCOPY NUMBER,
1144 x_party_rel_id OUT NOCOPY NUMBER,
1145 x_party_id OUT NOCOPY NUMBER,
1146 x_party_number OUT NOCOPY VARCHAR2,
1147 x_return_status OUT NOCOPY VARCHAR2,
1148 x_msg_count OUT NOCOPY NUMBER,
1149 x_msg_data OUT NOCOPY VARCHAR2
1150 ) IS
1151
1152 l_api_name CONSTANT VARCHAR2(30) := 'create_org_contact';
1153 l_api_version CONSTANT NUMBER := 1.0;
1154 l_org_contact_rec ORG_CONTACT_REC_TYPE := p_org_contact_rec;
1155 l_debug_prefix VARCHAR2(30) := '';
1156
1157 BEGIN
1158
1159 -- standard start of API savepoint
1160 SAVEPOINT create_org_contact;
1161
1162 -- Check if API is called in debug mode. If yes, enable debug.
1163 --enable_debug;
1164
1165 -- Debug info.
1166 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1167 hz_utility_v2pub.debug(p_message=>'create_org_contact (+)',
1168 p_prefix=>l_debug_prefix,
1169 p_msg_level=>fnd_log.level_procedure);
1170 END IF;
1171
1172 -- initialize message list if p_init_msg_list is set to TRUE.
1173 IF FND_API.to_Boolean(p_init_msg_list) THEN
1174 FND_MSG_PUB.initialize;
1175 END IF;
1176
1177 -- initialize API return status to success.
1178 x_return_status := FND_API.G_RET_STS_SUCCESS;
1179
1180 -- report error on obsolete columns based on profile
1181 IF NVL(FND_PROFILE.VALUE('HZ_API_ERR_ON_OBSOLETE_COLUMN'), 'Y') = 'Y' THEN
1182 check_obsolete_columns (
1183 p_create_update_flag => 'C',
1184 p_org_contact_rec => l_org_contact_rec,
1185 x_return_status => x_return_status
1186 );
1187
1188 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1189 RAISE FND_API.G_EXC_ERROR;
1190 END IF;
1191 END IF;
1192
1193 -- call to business logic.
1194 do_create_org_contact(
1195 l_org_contact_rec,
1196 x_return_status,
1197 x_org_contact_id,
1198 x_party_rel_id,
1199 x_party_id,
1200 x_party_number
1201 );
1202
1203 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1204 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'Y')) THEN
1205 -- Invoke business event system.
1206 HZ_BUSINESS_EVENT_V2PVT.create_org_contact_event (
1207 l_org_contact_rec );
1208 END IF;
1209
1210 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'BO_EVENTS_ENABLED')) THEN
1211 -- populate function for integration service
1212 HZ_POPULATE_BOT_PKG.pop_hz_org_contacts(
1213 p_operation => 'I',
1214 p_org_contact_id => x_org_contact_id);
1215 END IF;
1216 END IF;
1217
1218 -- Call to indicate Org Contact creation to DQM
1219 --Bug 4866187
1220 --Bug 5370799
1221 IF (p_org_contact_rec.orig_system IS NULL OR p_org_contact_rec.orig_system=FND_API.G_MISS_CHAR) THEN
1222 HZ_DQM_SYNC.sync_contact(l_org_contact_rec.org_contact_id, 'C');
1223 END IF;
1224 -- standard call to get message count and if count is 1, get message info.
1225 FND_MSG_PUB.Count_And_Get(
1226 p_encoded => FND_API.G_FALSE,
1227 p_count => x_msg_count,
1228 p_data => x_msg_data);
1229
1230 -- Debug info.
1231 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1232 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1233 p_msg_data=>x_msg_data,
1234 p_msg_type=>'WARNING',
1235 p_msg_level=>fnd_log.level_exception);
1236 END IF;
1237 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1238 hz_utility_v2pub.debug(p_message=>'create_org_contact (-)',
1239 p_prefix=>l_debug_prefix,
1240 p_msg_level=>fnd_log.level_procedure);
1241 END IF;
1242
1243 -- Check if API is called in debug mode. If yes, disable debug.
1244 --disable_debug;
1245
1246 EXCEPTION
1247 WHEN FND_API.G_EXC_ERROR THEN
1248 ROLLBACK TO create_org_contact;
1249 x_return_status := FND_API.G_RET_STS_ERROR;
1250 FND_MSG_PUB.Count_And_Get(
1251 p_encoded => FND_API.G_FALSE,
1252 p_count => x_msg_count,
1253 p_data => x_msg_data);
1254
1255 -- Debug info.
1256 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1257 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1258 p_msg_data=>x_msg_data,
1259 p_msg_type=>'ERROR',
1260 p_msg_level=>fnd_log.level_error);
1261 END IF;
1262 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1263 hz_utility_v2pub.debug(p_message=>'create_org_contact (-)',
1264 p_prefix=>l_debug_prefix,
1265 p_msg_level=>fnd_log.level_procedure);
1266 END IF;
1267
1268
1269 -- Check if API is called in debug mode. If yes, disable debug.
1270 --disable_debug;
1271
1272 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1273 ROLLBACK TO create_org_contact;
1274 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1275 FND_MSG_PUB.Count_And_Get(
1276 p_encoded => FND_API.G_FALSE,
1277 p_count => x_msg_count,
1278 p_data => x_msg_data);
1279
1280 -- Debug info.
1281 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1282 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1283 p_msg_data=>x_msg_data,
1284 p_msg_type=>'UNEXPECTED ERROR',
1285 p_msg_level=>fnd_log.level_error);
1286 END IF;
1287 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1288 hz_utility_v2pub.debug(p_message=>'create_org_contact (-)',
1289 p_prefix=>l_debug_prefix,
1290 p_msg_level=>fnd_log.level_procedure);
1291 END IF;
1292
1293 -- Check if API is called in debug mode. If yes, disable debug.
1294 --disable_debug;
1295
1296 WHEN OTHERS THEN
1297 ROLLBACK TO create_org_contact;
1298 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1299 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1300 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1301 FND_MSG_PUB.ADD;
1302 FND_MSG_PUB.Count_And_Get(
1303 p_encoded => FND_API.G_FALSE,
1304 p_count => x_msg_count,
1305 p_data => x_msg_data);
1306
1307 -- Debug info.
1308 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1309 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1310 p_msg_data=>x_msg_data,
1311 p_msg_type=>'SQL ERROR',
1312 p_msg_level=>fnd_log.level_error);
1313 END IF;
1314 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1315 hz_utility_v2pub.debug(p_message=>'create_org_contact (-)',
1316 p_prefix=>l_debug_prefix,
1317 p_msg_level=>fnd_log.level_procedure);
1318 END IF;
1319
1320 -- Check if API is called in debug mode. If yes, disable debug.
1321 --disable_debug;
1322
1323 END create_org_contact;
1324
1325
1326 /*===========================================================================+
1327 | PROCEDURE
1328 | update_org_contact
1329 |
1330 | DESCRIPTION
1331 | Updates org_contact, party relationship for org_contact and
1332 | party for party relationship.
1333 |
1334 | SCOPE - PUBLIC
1335 |
1336 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1337 |
1338 | ARGUMENTS : IN:
1339 | p_init_msg_list
1340 | p_org_contact_rec
1341 | OUT:
1342 | x_return_status
1343 | x_msg_count
1344 | x_msg_data
1345 | IN/ OUT:
1346 | p_org_contact_last_update_date
1347 | p_party_rel_last_update_date
1348 | p_party_last_update_date
1349 |
1350 | RETURNS : NONE
1351 |
1352 | NOTES
1353 |
1354 | MODIFICATION HISTORY
1355 |
1356 +===========================================================================*/
1357
1358 PROCEDURE update_org_contact (
1359 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
1360 p_org_contact_rec IN ORG_CONTACT_REC_TYPE,
1361 p_cont_object_version_number IN OUT NOCOPY NUMBER,
1362 p_rel_object_version_number IN OUT NOCOPY NUMBER,
1363 p_party_object_version_number IN OUT NOCOPY NUMBER,
1364 x_return_status OUT NOCOPY VARCHAR2,
1365 x_msg_count OUT NOCOPY NUMBER,
1366 x_msg_data OUT NOCOPY VARCHAR2
1367 ) IS
1368
1369 l_api_name CONSTANT VARCHAR2(30) := 'update_org_contact';
1370 l_api_version CONSTANT NUMBER := 1.0;
1371 l_org_contact_rec ORG_CONTACT_REC_TYPE := p_org_contact_rec;
1372 l_old_org_contact_rec ORG_CONTACT_REC_TYPE;
1373 l_org_status VARCHAR2(1);
1374 l_debug_prefix VARCHAR2(30) := '';
1375
1376 BEGIN
1377
1378 -- standard start of API savepoint
1379 SAVEPOINT update_org_contact;
1380
1381 -- Check if API is called in debug mode. If yes, enable debug.
1382 --enable_debug;
1383
1384 -- Debug info.
1385 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1386 hz_utility_v2pub.debug(p_message=>'update_org_contact (+)',
1387 p_prefix=>l_debug_prefix,
1388 p_msg_level=>fnd_log.level_procedure);
1389 END IF;
1390
1391 -- initialize message list if p_init_msg_list is set to TRUE.
1392 IF FND_API.to_Boolean(p_init_msg_list) THEN
1393 FND_MSG_PUB.initialize;
1394 END IF;
1395
1396 -- initialize API return status to success.
1397 x_return_status := FND_API.G_RET_STS_SUCCESS;
1398
1399 IF (p_org_contact_rec.orig_system is not null
1400 and p_org_contact_rec.orig_system <>fnd_api.g_miss_char)
1401 and (p_org_contact_rec.orig_system_reference is not null
1402 and p_org_contact_rec.orig_system_reference <>fnd_api.g_miss_char)
1403 and (p_org_contact_rec.org_contact_id = FND_API.G_MISS_NUM or p_org_contact_rec.org_contact_id is null) THEN
1404 hz_orig_system_ref_pub.get_owner_table_id
1405 (p_orig_system => p_org_contact_rec.orig_system,
1406 p_orig_system_reference => p_org_contact_rec.orig_system_reference,
1407 p_owner_table_name => 'HZ_ORG_CONTACTS',
1408 x_owner_table_id => l_org_contact_rec.org_contact_id,
1409 x_return_status => x_return_status);
1410 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1411 RAISE FND_API.G_EXC_ERROR;
1412 END IF;
1413 END IF;
1414
1415
1416 -- Get old records. Will be used by business event system.
1417 get_org_contact_rec (
1418 p_org_contact_id => l_org_contact_rec.org_contact_id,
1419 x_org_contact_rec => l_old_org_contact_rec,
1420 x_return_status => x_return_status,
1421 x_msg_count => x_msg_count,
1422 x_msg_data => x_msg_data );
1423
1424 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1425 RAISE FND_API.G_EXC_ERROR;
1426 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1427 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1428 END IF;
1429
1430 -- report error on obsolete columns based on profile
1431 IF NVL(FND_PROFILE.VALUE('HZ_API_ERR_ON_OBSOLETE_COLUMN'), 'Y') = 'Y' THEN
1432 check_obsolete_columns (
1433 p_create_update_flag => 'U',
1434 p_org_contact_rec => l_org_contact_rec,
1435 p_old_org_contact_rec => l_old_org_contact_rec,
1436 x_return_status => x_return_status
1437 );
1438
1439 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1440 RAISE FND_API.G_EXC_ERROR;
1441 END IF;
1442 END IF;
1443
1444 -- call to business logic.
1445 do_update_org_contact(
1446 l_org_contact_rec,
1447 p_cont_object_version_number,
1448 p_rel_object_version_number,
1449 p_party_object_version_number,
1450 x_return_status
1451 );
1452
1453 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1454 l_old_org_contact_rec.orig_system := p_org_contact_rec.orig_system;
1455 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'Y')) THEN
1456 -- Invoke business event system.
1457 HZ_BUSINESS_EVENT_V2PVT.update_org_contact_event (
1458 l_org_contact_rec,
1459 l_old_org_contact_rec );
1460 END IF;
1461
1462 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'BO_EVENTS_ENABLED')) THEN
1463 -- populate function for integration service
1464 HZ_POPULATE_BOT_PKG.pop_hz_org_contacts(
1465 p_operation => 'U',
1466 p_org_contact_id => l_org_contact_rec.org_contact_id);
1467 END IF;
1468 END IF;
1469
1470 -- Call to indicate Org Contact update to DQM
1471 HZ_DQM_SYNC.sync_contact(l_org_contact_rec.org_contact_id, 'U');
1472
1473 -- standard call to get message count and if count is 1, get message info.
1474 FND_MSG_PUB.Count_And_Get(
1475 p_encoded => FND_API.G_FALSE,
1476 p_count => x_msg_count,
1477 p_data => x_msg_data);
1478
1479 -- Debug info.
1480 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1481 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1482 p_msg_data=>x_msg_data,
1483 p_msg_type=>'WARNING',
1484 p_msg_level=>fnd_log.level_exception);
1485 END IF;
1486 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1487 hz_utility_v2pub.debug(p_message=>'update_org_contact (-)',
1488 p_prefix=>l_debug_prefix,
1489 p_msg_level=>fnd_log.level_procedure);
1490 END IF;
1491
1492 -- Check if API is called in debug mode. If yes, disable debug.
1493 --disable_debug;
1494 EXCEPTION
1495 WHEN FND_API.G_EXC_ERROR THEN
1496 ROLLBACK TO update_org_contact;
1497 x_return_status := FND_API.G_RET_STS_ERROR;
1498 FND_MSG_PUB.Count_And_Get(
1499 p_encoded => FND_API.G_FALSE,
1500 p_count => x_msg_count,
1501 p_data => x_msg_data);
1502
1503 -- Debug info.
1504 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1505 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1506 p_msg_data=>x_msg_data,
1507 p_msg_type=>'ERROR',
1508 p_msg_level=>fnd_log.level_error);
1509 END IF;
1510 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1511 hz_utility_v2pub.debug(p_message=>'update_org_contact (-)',
1512 p_prefix=>l_debug_prefix,
1513 p_msg_level=>fnd_log.level_procedure);
1514 END IF;
1515
1516 -- Check if API is called in debug mode. If yes, disable debug.
1517 --disable_debug;
1518
1519 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1520 ROLLBACK TO update_org_contact;
1521 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1522 FND_MSG_PUB.Count_And_Get(
1523 p_encoded => FND_API.G_FALSE,
1524 p_count => x_msg_count,
1525 p_data => x_msg_data);
1526
1527 -- Debug info.
1528 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1529 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1530 p_msg_data=>x_msg_data,
1531 p_msg_type=>'UNEXPECTED ERROR',
1532 p_msg_level=>fnd_log.level_error);
1533 END IF;
1534 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1535 hz_utility_v2pub.debug(p_message=>'update_org_contact (-)',
1536 p_prefix=>l_debug_prefix,
1537 p_msg_level=>fnd_log.level_procedure);
1538 END IF;
1539
1540 -- Check if API is called in debug mode. If yes, disable debug.
1541 --disable_debug;
1542
1543 WHEN OTHERS THEN
1544 ROLLBACK TO update_org_contact;
1545 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1546 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1547 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1548 FND_MSG_PUB.ADD;
1549 FND_MSG_PUB.Count_And_Get(
1550 p_encoded => FND_API.G_FALSE,
1551 p_count => x_msg_count,
1552 p_data => x_msg_data);
1553
1554 -- Debug info.
1555 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1556 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1557 p_msg_data=>x_msg_data,
1558 p_msg_type=>'SQL ERROR',
1559 p_msg_level=>fnd_log.level_error);
1560 END IF;
1561 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1562 hz_utility_v2pub.debug(p_message=>'update_org_contact (-)',
1563 p_prefix=>l_debug_prefix,
1564 p_msg_level=>fnd_log.level_procedure);
1565 END IF;
1566
1567 -- Check if API is called in debug mode. If yes, disable debug.
1568 --disable_debug;
1569
1570 END update_org_contact;
1571
1572
1573 /*===========================================================================+
1574 | PROCEDURE
1575 | create_org_contact_role
1576 |
1577 | DESCRIPTION
1578 | Creates org_contact_role.
1579 |
1580 | SCOPE - PUBLIC
1581 |
1582 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1583 |
1584 | ARGUMENTS : IN:
1585 | p_init_msg_list
1586 | p_org_contact_role_rec
1587 | OUT:
1588 | x_return_status
1589 | x_msg_count
1590 | x_msg_data
1591 | x_org_contact_role_id
1592 | IN/ OUT:
1593 |
1594 | RETURNS : NONE
1595 |
1596 | NOTES
1597 |
1598 | MODIFICATION HISTORY
1599 |
1600 +===========================================================================*/
1601
1602 PROCEDURE create_org_contact_role (
1603 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
1604 p_org_contact_role_rec IN ORG_CONTACT_ROLE_REC_TYPE,
1605 x_org_contact_role_id OUT NOCOPY NUMBER,
1606 x_return_status OUT NOCOPY VARCHAR2,
1607 x_msg_count OUT NOCOPY NUMBER,
1608 x_msg_data OUT NOCOPY VARCHAR2
1609 ) IS
1610
1611 l_api_name CONSTANT VARCHAR2(30) := 'create_org_contact_role';
1612 l_api_version CONSTANT NUMBER := 1.0;
1613 l_org_contact_role_rec ORG_CONTACT_ROLE_REC_TYPE := p_org_contact_role_rec;
1614 l_debug_prefix VARCHAR2(30) := '';
1615
1616 BEGIN
1617
1618 -- standard start of API savepoint
1619 SAVEPOINT create_org_contact_role;
1620
1621 -- Check if API is called in debug mode. If yes, enable debug.
1622 --enable_debug;
1623
1624 -- Debug info.
1625 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1626 hz_utility_v2pub.debug(p_message=>'create_org_contact_role (+)',
1627 p_prefix=>l_debug_prefix,
1628 p_msg_level=>fnd_log.level_procedure);
1629 END IF;
1630
1631 -- initialize message list if p_init_msg_list is set to TRUE.
1632 IF FND_API.to_Boolean(p_init_msg_list) THEN
1633 FND_MSG_PUB.initialize;
1634 END IF;
1635
1636 -- initialize API return status to success.
1637 x_return_status := FND_API.G_RET_STS_SUCCESS;
1638
1639 -- call to business logic.
1640 do_create_org_contact_role(
1641 l_org_contact_role_rec,
1642 x_org_contact_role_id,
1643 x_return_status
1644 );
1645
1646 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1647 -- Invoke business event system.
1648 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'Y')) THEN
1649 HZ_BUSINESS_EVENT_V2PVT.create_org_contact_role_event (
1650 l_org_contact_role_rec );
1651 END IF;
1652
1653 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'BO_EVENTS_ENABLED')) THEN
1654 HZ_POPULATE_BOT_PKG.pop_hz_org_contact_roles(
1655 p_operation => 'I',
1656 p_org_contact_role_id => x_org_contact_role_id );
1657 END IF;
1658 END IF;
1659
1660 -- standard call to get message count and if count is 1, get message info.
1661 FND_MSG_PUB.Count_And_Get(
1662 p_encoded => FND_API.G_FALSE,
1663 p_count => x_msg_count,
1664 p_data => x_msg_data);
1665
1666 -- Debug info.
1667 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1668 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1669 p_msg_data=>x_msg_data,
1670 p_msg_type=>'WARNING',
1671 p_msg_level=>fnd_log.level_exception);
1672 END IF;
1673 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1674 hz_utility_v2pub.debug(p_message=>'create_org_contact_role (-)',
1675 p_prefix=>l_debug_prefix,
1676 p_msg_level=>fnd_log.level_procedure);
1677 END IF;
1678
1679 -- Check if API is called in debug mode. If yes, disable debug.
1680 --disable_debug;
1681
1682 EXCEPTION
1683 WHEN FND_API.G_EXC_ERROR THEN
1684 ROLLBACK TO create_org_contact_role;
1685 x_return_status := FND_API.G_RET_STS_ERROR;
1686 FND_MSG_PUB.Count_And_Get(
1687 p_encoded => FND_API.G_FALSE,
1688 p_count => x_msg_count,
1689 p_data => x_msg_data);
1690
1691 -- Debug info.
1692 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1693 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1694 p_msg_data=>x_msg_data,
1695 p_msg_type=>'ERROR',
1696 p_msg_level=>fnd_log.level_error);
1697 END IF;
1698 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1699 hz_utility_v2pub.debug(p_message=>'create_org_contact_role (-)',
1700 p_prefix=>l_debug_prefix,
1701 p_msg_level=>fnd_log.level_procedure);
1702 END IF;
1703
1704 -- Check if API is called in debug mode. If yes, disable debug.
1705 --disable_debug;
1706
1707 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1708 ROLLBACK TO create_org_contact_role;
1709 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1710 FND_MSG_PUB.Count_And_Get(
1711 p_encoded => FND_API.G_FALSE,
1712 p_count => x_msg_count,
1713 p_data => x_msg_data);
1714
1715 -- Debug info.
1716 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1717 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1718 p_msg_data=>x_msg_data,
1719 p_msg_type=>'UNEXPECTED ERROR',
1720 p_msg_level=>fnd_log.level_error);
1721 END IF;
1722 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1723 hz_utility_v2pub.debug(p_message=>'create_org_contact_role (-)',
1724 p_prefix=>l_debug_prefix,
1725 p_msg_level=>fnd_log.level_procedure);
1726 END IF;
1727
1728 -- Check if API is called in debug mode. If yes, disable debug.
1729 --disable_debug;
1730
1731 WHEN OTHERS THEN
1732 ROLLBACK TO create_org_contact_role;
1733 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1734 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1735 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1736 FND_MSG_PUB.ADD;
1737 FND_MSG_PUB.Count_And_Get(
1738 p_encoded => FND_API.G_FALSE,
1739 p_count => x_msg_count,
1740 p_data => x_msg_data);
1741
1742 -- Debug info.
1743 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1744 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1745 p_msg_data=>x_msg_data,
1746 p_msg_type=>'SQL ERROR',
1747 p_msg_level=>fnd_log.level_error);
1748 END IF;
1749 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1750 hz_utility_v2pub.debug(p_message=>'create_org_contact_role (-)',
1751 p_prefix=>l_debug_prefix,
1752 p_msg_level=>fnd_log.level_procedure);
1753 END IF;
1754
1755 -- Check if API is called in debug mode. If yes, disable debug.
1756 --disable_debug;
1757
1758 END create_org_contact_role;
1759
1760
1761 /*===========================================================================+
1762 | PROCEDURE
1763 | update_org_contact_role
1764 |
1765 | DESCRIPTION
1766 | Updates org_contact_role.
1767 |
1768 | SCOPE - PUBLIC
1769 |
1770 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1771 |
1772 | ARGUMENTS : IN:
1773 | p_init_msg_list
1774 | p_org_contact_role_rec
1775 | OUT:
1776 | x_return_status
1777 | x_msg_count
1778 | x_msg_data
1779 | IN/ OUT:
1780 | p_last_update_date
1781 |
1782 | RETURNS : NONE
1783 |
1784 | NOTES
1785 |
1786 | MODIFICATION HISTORY
1787 |
1788 +===========================================================================*/
1789
1790 PROCEDURE update_org_contact_role (
1791 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
1792 p_org_contact_role_rec IN ORG_CONTACT_ROLE_REC_TYPE,
1793 p_object_version_number IN OUT NOCOPY NUMBER,
1794 x_return_status OUT NOCOPY VARCHAR2,
1795 x_msg_count OUT NOCOPY NUMBER,
1796 x_msg_data OUT NOCOPY VARCHAR2
1797 ) IS
1798
1799 l_api_name CONSTANT VARCHAR2(30) := 'update_org_contact_role';
1800 l_api_version CONSTANT NUMBER := 1.0;
1801 l_org_contact_role_rec ORG_CONTACT_ROLE_REC_TYPE := p_org_contact_role_rec;
1802 l_old_org_contact_role_rec ORG_CONTACT_ROLE_REC_TYPE ;
1803 l_debug_prefix VARCHAR2(30) := '';
1804
1805 BEGIN
1806
1807 -- standard start of API savepoint
1808 SAVEPOINT update_org_contact_role;
1809
1810 -- Check if API is called in debug mode. If yes, enable debug.
1811 --enable_debug;
1812
1813 -- Debug info.
1814 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1815 hz_utility_v2pub.debug(p_message=>'update_org_contact_role (+)',
1816 p_prefix=>l_debug_prefix,
1817 p_msg_level=>fnd_log.level_procedure);
1818 END IF;
1819
1820 -- initialize message list if p_init_msg_list is set to TRUE.
1821 IF FND_API.to_Boolean(p_init_msg_list) THEN
1822 FND_MSG_PUB.initialize;
1823 END IF;
1824
1825 -- initialize API return status to success.
1826 x_return_status := FND_API.G_RET_STS_SUCCESS;
1827
1828 IF (p_org_contact_role_rec.orig_system is not null
1829 and p_org_contact_role_rec.orig_system <>fnd_api.g_miss_char)
1830 and (p_org_contact_role_rec.orig_system_reference is not null
1831 and p_org_contact_role_rec.orig_system_reference <>fnd_api.g_miss_char)
1832 and (p_org_contact_role_rec.org_contact_role_id = FND_API.G_MISS_NUM or p_org_contact_role_rec.org_contact_role_id is null) THEN
1833 hz_orig_system_ref_pub.get_owner_table_id
1834 (p_orig_system => p_org_contact_role_rec.orig_system,
1835 p_orig_system_reference => p_org_contact_role_rec.orig_system_reference,
1836 p_owner_table_name => 'HZ_ORG_CONTACT_ROLES',
1837 x_owner_table_id => l_org_contact_role_rec.org_contact_role_id,
1838 x_return_status => x_return_status);
1839 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1840 RAISE FND_API.G_EXC_ERROR;
1841 END IF;
1842 END IF;
1843
1844 --2290537
1845 get_org_contact_role_rec (
1846 p_org_contact_role_id => l_org_contact_role_rec.org_contact_role_id,
1847 x_org_contact_role_rec => l_old_org_contact_role_rec,
1848 x_return_status => x_return_status,
1849 x_msg_count => x_msg_count,
1850 x_msg_data => x_msg_data);
1851
1852 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1853 RAISE FND_API.G_EXC_ERROR;
1854 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1855 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1856 END IF;
1857
1858 -- call to business logic.
1859 do_update_org_contact_role(
1860 l_org_contact_role_rec,
1861 p_object_version_number,
1862 x_return_status
1863 );
1864
1865 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1866 -- Invoke business event system.
1867 l_old_org_contact_role_rec.orig_system := p_org_contact_role_rec.orig_system;
1868 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'Y')) THEN
1869 HZ_BUSINESS_EVENT_V2PVT.update_org_contact_role_event (
1870 l_org_contact_role_rec , l_old_org_contact_role_rec );
1871 END IF;
1872
1873 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'BO_EVENTS_ENABLED')) THEN
1874 HZ_POPULATE_BOT_PKG.pop_hz_org_contact_roles(
1875 p_operation => 'U',
1876 p_org_contact_role_id => l_org_contact_role_rec.org_contact_role_id );
1877 END IF;
1878 END IF;
1879
1880 -- standard call to get message count and if count is 1, get message info.
1881 FND_MSG_PUB.Count_And_Get(
1882 p_encoded => FND_API.G_FALSE,
1883 p_count => x_msg_count,
1884 p_data => x_msg_data);
1885
1886 -- Debug info.
1887 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1888 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1889 p_msg_data=>x_msg_data,
1890 p_msg_type=>'WARNING',
1891 p_msg_level=>fnd_log.level_exception);
1892 END IF;
1893 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1894 hz_utility_v2pub.debug(p_message=>'update_org_contact_role (-)',
1895 p_prefix=>l_debug_prefix,
1896 p_msg_level=>fnd_log.level_procedure);
1897 END IF;
1898
1899 -- Check if API is called in debug mode. If yes, disable debug.
1900 --disable_debug;
1901
1902 EXCEPTION
1903 WHEN FND_API.G_EXC_ERROR THEN
1904 ROLLBACK TO update_org_contact_role;
1905 x_return_status := FND_API.G_RET_STS_ERROR;
1906 FND_MSG_PUB.Count_And_Get(
1907 p_encoded => FND_API.G_FALSE,
1908 p_count => x_msg_count,
1909 p_data => x_msg_data);
1910
1911 -- Debug info.
1912 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1913 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1914 p_msg_data=>x_msg_data,
1915 p_msg_type=>'ERROR',
1916 p_msg_level=>fnd_log.level_error);
1917 END IF;
1918 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1919 hz_utility_v2pub.debug(p_message=>'update_org_contact_role (-)',
1920 p_prefix=>l_debug_prefix,
1921 p_msg_level=>fnd_log.level_procedure);
1922 END IF;
1923
1924 -- Check if API is called in debug mode. If yes, disable debug.
1925 --disable_debug;
1926
1927 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1928 ROLLBACK TO update_org_contact_role;
1929 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1930 FND_MSG_PUB.Count_And_Get(
1931 p_encoded => FND_API.G_FALSE,
1932 p_count => x_msg_count,
1933 p_data => x_msg_data);
1934
1935 -- Debug info.
1936 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1937 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1938 p_msg_data=>x_msg_data,
1939 p_msg_type=>'UNEXPECTED ERROR',
1940 p_msg_level=>fnd_log.level_error);
1941 END IF;
1942 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1943 hz_utility_v2pub.debug(p_message=>'update_org_contact_role (-)',
1944 p_prefix=>l_debug_prefix,
1945 p_msg_level=>fnd_log.level_procedure);
1946 END IF;
1947
1948 -- Check if API is called in debug mode. If yes, disable debug.
1949 --disable_debug;
1950
1951 WHEN OTHERS THEN
1952 ROLLBACK TO update_org_contact_role;
1953 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1954 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1955 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1956 FND_MSG_PUB.ADD;
1957 FND_MSG_PUB.Count_And_Get(
1958 p_encoded => FND_API.G_FALSE,
1959 p_count => x_msg_count,
1960 p_data => x_msg_data);
1961
1962 -- Debug info.
1963 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1964 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1965 p_msg_data=>x_msg_data,
1966 p_msg_type=>'SQL ERROR',
1967 p_msg_level=>fnd_log.level_error);
1968 END IF;
1969 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1970 hz_utility_v2pub.debug(p_message=>'update_org_contact_role (-)',
1971 p_prefix=>l_debug_prefix,
1972 p_msg_level=>fnd_log.level_procedure);
1973 END IF;
1974
1975 -- Check if API is called in debug mode. If yes, disable debug.
1976 --disable_debug;
1977
1978 END update_org_contact_role;
1979
1980 /*===========================================================================+
1981 | PROCEDURE
1982 | get_org_contact_rec
1983 |
1984 | DESCRIPTION
1985 | Gets current record.
1986 |
1987 | SCOPE - PRIVATE
1988 |
1989 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1990 |
1991 | ARGUMENTS : IN:
1992 | p_init_msg_list
1993 | p_org_contact_id
1994 | OUT:
1995 | x_org_contact_rec
1996 | x_return_status
1997 | x_msg_count
1998 | x_msg_data
1999 | IN/ OUT:
2000 |
2001 | RETURNS : NONE
2002 |
2003 | NOTES
2004 |
2005 | MODIFICATION HISTORY
2006 | Jyoti Pandey Bug: 2084351
2007 | HZ_PARTY_CONTACT_V2PUB.GET_ORG_CONTACT_REC SHOULD
2008 | RETURN THE RELATIONSHIP REC
2009 |
2010 +===========================================================================*/
2011
2012 PROCEDURE get_org_contact_rec (
2013 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2014 p_org_contact_id IN NUMBER,
2015 x_org_contact_rec OUT NOCOPY ORG_CONTACT_REC_TYPE,
2016 x_return_status OUT NOCOPY VARCHAR2,
2017 x_msg_count OUT NOCOPY NUMBER,
2018 x_msg_data OUT NOCOPY VARCHAR2
2019 ) IS
2020
2021 l_api_name CONSTANT VARCHAR2(30) := 'get_org_contact_rec';
2022 l_api_version CONSTANT NUMBER := 1.0;
2023 l_party_relationship_id NUMBER;
2024
2025 BEGIN
2026
2027 --Initialize message list if p_init_msg_list is set to TRUE.
2028 IF FND_API.to_Boolean(p_init_msg_list) THEN
2029 FND_MSG_PUB.initialize;
2030 END IF;
2031
2032 --Initialize API return status to success.
2033 x_return_status := FND_API.G_RET_STS_SUCCESS;
2034 --Check whether primary key has been passed in.
2035 IF p_org_contact_id IS NULL OR
2036 p_org_contact_id = FND_API.G_MISS_NUM THEN
2037 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
2038 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'org_contact_id' );
2039 FND_MSG_PUB.ADD;
2040 RAISE FND_API.G_EXC_ERROR;
2041 END IF;
2042
2043 x_org_contact_rec.org_contact_id := p_org_contact_id;
2044
2045 HZ_ORG_CONTACTS_PKG.Select_Row (
2046 X_ORG_CONTACT_ID => x_org_contact_rec.org_contact_id,
2047 X_PARTY_RELATIONSHIP_ID => l_party_relationship_id,
2048 X_COMMENTS => x_org_contact_rec.comments,
2049 X_CONTACT_NUMBER => x_org_contact_rec.contact_number,
2050 X_DEPARTMENT_CODE => x_org_contact_rec.department_code,
2051 X_DEPARTMENT => x_org_contact_rec.department,
2052 X_TITLE => x_org_contact_rec.title,
2053 X_JOB_TITLE => x_org_contact_rec.job_title,
2054 X_DECISION_MAKER_FLAG => x_org_contact_rec.decision_maker_flag,
2055 X_JOB_TITLE_CODE => x_org_contact_rec.job_title_code,
2056 X_REFERENCE_USE_FLAG => x_org_contact_rec.reference_use_flag,
2057 X_RANK => x_org_contact_rec.rank,
2058 X_ORIG_SYSTEM_REFERENCE => x_org_contact_rec.orig_system_reference,
2059 X_ATTRIBUTE_CATEGORY => x_org_contact_rec.attribute_category,
2060 X_ATTRIBUTE1 => x_org_contact_rec.attribute1,
2061 X_ATTRIBUTE2 => x_org_contact_rec.attribute2,
2062 X_ATTRIBUTE3 => x_org_contact_rec.attribute3,
2063 X_ATTRIBUTE4 => x_org_contact_rec.attribute4,
2064 X_ATTRIBUTE5 => x_org_contact_rec.attribute5,
2065 X_ATTRIBUTE6 => x_org_contact_rec.attribute6,
2066 X_ATTRIBUTE7 => x_org_contact_rec.attribute7,
2067 X_ATTRIBUTE8 => x_org_contact_rec.attribute8,
2068 X_ATTRIBUTE9 => x_org_contact_rec.attribute9,
2069 X_ATTRIBUTE10 => x_org_contact_rec.attribute10,
2070 X_ATTRIBUTE11 => x_org_contact_rec.attribute11,
2071 X_ATTRIBUTE12 => x_org_contact_rec.attribute12,
2072 X_ATTRIBUTE13 => x_org_contact_rec.attribute13,
2073 X_ATTRIBUTE14 => x_org_contact_rec.attribute14,
2074 X_ATTRIBUTE15 => x_org_contact_rec.attribute15,
2075 X_ATTRIBUTE16 => x_org_contact_rec.attribute16,
2076 X_ATTRIBUTE17 => x_org_contact_rec.attribute17,
2077 X_ATTRIBUTE18 => x_org_contact_rec.attribute18,
2078 X_ATTRIBUTE19 => x_org_contact_rec.attribute19,
2079 X_ATTRIBUTE20 => x_org_contact_rec.attribute20,
2080 X_ATTRIBUTE21 => x_org_contact_rec.attribute21,
2081 X_ATTRIBUTE22 => x_org_contact_rec.attribute22,
2082 X_ATTRIBUTE23 => x_org_contact_rec.attribute23,
2083 X_ATTRIBUTE24 => x_org_contact_rec.attribute24,
2084 X_PARTY_SITE_ID => x_org_contact_rec.party_site_id,
2085 X_CREATED_BY_MODULE => x_org_contact_rec.created_by_module,
2086 X_APPLICATION_ID => x_org_contact_rec.application_id
2087 );
2088
2089 ---Bug: 2084351 HZ_PARTY_CONTACT_V2PUB.GET_ORG_CONTACT_REC SHOULD
2090 --- RETURN THE RELATIONSHIP REC
2091
2092 IF l_party_relationship_id IS NOT NULL
2093 AND
2094 l_party_relationship_id <> FND_API.G_MISS_NUM
2095 THEN
2096 HZ_RELATIONSHIP_V2PUB.get_relationship_rec (
2097 p_relationship_id => l_party_relationship_id,
2098 p_directional_flag => 'F',
2099 x_rel_rec => x_org_contact_rec.party_rel_rec,
2100 x_return_status => x_return_status,
2101 x_msg_count => x_msg_count,
2102 x_msg_data => x_msg_data
2103 );
2104
2105 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2106 RAISE FND_API.G_EXC_ERROR;
2107 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2108 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2109 END IF;
2110 END IF;
2111
2112 --Standard call to get message count and if count is 1, get message info.
2113 FND_MSG_PUB.Count_And_Get(
2114 p_encoded => FND_API.G_FALSE,
2115 p_count => x_msg_count,
2116 p_data => x_msg_data );
2117
2118 EXCEPTION
2119 WHEN FND_API.G_EXC_ERROR THEN
2120 x_return_status := FND_API.G_RET_STS_ERROR;
2121
2122 FND_MSG_PUB.Count_And_Get(
2123 p_encoded => FND_API.G_FALSE,
2124 p_count => x_msg_count,
2125 p_data => x_msg_data );
2126
2127 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2128 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2129
2130 FND_MSG_PUB.Count_And_Get(
2131 p_encoded => FND_API.G_FALSE,
2132 p_count => x_msg_count,
2133 p_data => x_msg_data );
2134
2135 WHEN OTHERS THEN
2136 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2137 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
2138 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
2139 FND_MSG_PUB.ADD;
2140 FND_MSG_PUB.Count_And_Get(
2141 p_encoded => FND_API.G_FALSE,
2142 p_count => x_msg_count,
2143 p_data => x_msg_data );
2144
2145 END get_org_contact_rec;
2146
2147 /*===========================================================================+
2148 | PROCEDURE
2149 | get_org_contact_role_rec
2150 |
2151 | DESCRIPTION
2152 | Gets current record.
2153 |
2154 | SCOPE - PRIVATE
2155 |
2156 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2157 |
2158 | ARGUMENTS : IN:
2159 | p_init_msg_list
2160 | p_org_contact_id
2161 | OUT:
2162 | x_org_contact_rec
2163 | x_return_status
2164 | x_msg_count
2165 | x_msg_data
2166 | IN/ OUT:
2167 |
2168 | RETURNS : NONE
2169 |
2170 | NOTES
2171 |
2172 | MODIFICATION HISTORY
2173 |
2174 +===========================================================================*/
2175
2176 PROCEDURE get_org_contact_role_rec (
2177 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2178 p_org_contact_role_id IN NUMBER,
2179 x_org_contact_role_rec OUT NOCOPY ORG_CONTACT_ROLE_REC_TYPE,
2180 x_return_status OUT NOCOPY VARCHAR2,
2181 x_msg_count OUT NOCOPY NUMBER,
2182 x_msg_data OUT NOCOPY VARCHAR2
2183 ) IS
2184
2185 l_api_name CONSTANT VARCHAR2(30) := 'get_org_contact_role_rec';
2186
2187 BEGIN
2188
2189 --Initialize message list if p_init_msg_list is set to TRUE.
2190 IF FND_API.to_Boolean(p_init_msg_list) THEN
2191 FND_MSG_PUB.initialize;
2192 END IF;
2193
2194 --Initialize API return status to success.
2195 x_return_status := FND_API.G_RET_STS_SUCCESS;
2196
2197 --Check whether primary key has been passed in.
2198 IF p_org_contact_role_id IS NULL OR
2199 p_org_contact_role_id = FND_API.G_MISS_NUM THEN
2200 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
2201 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'org_contact_role_id' );
2202 FND_MSG_PUB.ADD;
2203 RAISE FND_API.G_EXC_ERROR;
2204 END IF;
2205
2206 x_org_contact_role_rec.org_contact_role_id := p_org_contact_role_id;
2207
2208 HZ_ORG_CONTACT_ROLES_PKG.Select_Row (
2209 X_ORG_CONTACT_ROLE_ID => x_org_contact_role_rec.org_contact_role_id,
2210 X_ORG_CONTACT_ID => x_org_contact_role_rec.org_contact_id,
2211 X_ROLE_TYPE => x_org_contact_role_rec.role_type,
2212 X_ROLE_LEVEL => x_org_contact_role_rec.role_level,
2213 X_PRIMARY_FLAG => x_org_contact_role_rec.primary_flag,
2214 X_ORIG_SYSTEM_REFERENCE => x_org_contact_role_rec.orig_system_reference,
2215 X_PRIMARY_CON_PER_ROLE_TYPE => x_org_contact_role_rec.primary_contact_per_role_type,
2216 X_STATUS => x_org_contact_role_rec.status,
2217 X_CREATED_BY_MODULE => x_org_contact_role_rec.created_by_module,
2218 X_APPLICATION_ID => x_org_contact_role_rec.application_id
2219 );
2220
2221 --Standard call to get message count and if count is 1, get message info.
2222 FND_MSG_PUB.Count_And_Get(
2223 p_encoded => FND_API.G_FALSE,
2224 p_count => x_msg_count,
2225 p_data => x_msg_data );
2226
2227 EXCEPTION
2228 WHEN FND_API.G_EXC_ERROR THEN
2229 x_return_status := FND_API.G_RET_STS_ERROR;
2230
2231 FND_MSG_PUB.Count_And_Get(
2232 p_encoded => FND_API.G_FALSE,
2233 p_count => x_msg_count,
2234 p_data => x_msg_data );
2235
2236 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2237 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2238
2239 FND_MSG_PUB.Count_And_Get(
2240 p_encoded => FND_API.G_FALSE,
2241 p_count => x_msg_count,
2242 p_data => x_msg_data );
2243
2244 WHEN OTHERS THEN
2245 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2246
2247 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
2248 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
2249 FND_MSG_PUB.ADD;
2250
2251 FND_MSG_PUB.Count_And_Get(
2252 p_encoded => FND_API.G_FALSE,
2253 p_count => x_msg_count,
2254 p_data => x_msg_data );
2255
2256 END get_org_contact_role_rec;
2257
2258 /**
2259 * PRIVATE PROCEDURE check_obsolete_columns
2260 *
2261 * DESCRIPTION
2262 * Check if user is using obsolete columns.
2263 *
2264 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2265 *
2266 * MODIFICATION HISTORY
2267 *
2268 * 07-25-2005 Jianying Huang o Created.
2269 *
2270 */
2271
2272 PROCEDURE check_obsolete_columns (
2273 p_create_update_flag IN VARCHAR2,
2274 p_org_contact_rec IN org_contact_rec_type,
2275 p_old_org_contact_rec IN org_contact_rec_type DEFAULT NULL,
2276 x_return_status IN OUT NOCOPY VARCHAR2
2277 ) IS
2278
2279 BEGIN
2280
2281 -- check title
2282 IF (p_create_update_flag = 'C' AND
2283 p_org_contact_rec.title IS NOT NULL AND
2284 p_org_contact_rec.title <> FND_API.G_MISS_CHAR) OR
2285 (p_create_update_flag = 'U' AND
2286 p_org_contact_rec.title IS NOT NULL AND
2287 p_org_contact_rec.title <> p_old_org_contact_rec.title)
2288 THEN
2289 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OBSOLETE_COLUMN');
2290 FND_MESSAGE.SET_TOKEN('COLUMN', 'title');
2291 FND_MSG_PUB.ADD;
2292 x_return_status := FND_API.G_RET_STS_ERROR;
2293 END IF;
2294
2295 END check_obsolete_columns;
2296
2297 END HZ_PARTY_CONTACT_V2PUB;