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