[Home] [Help]
PACKAGE BODY: APPS.HZ_RELATIONSHIP_V2PUB
Source
1 PACKAGE BODY HZ_RELATIONSHIP_V2PUB AS
2 /*$Header: ARH2RESB.pls 120.32.12010000.2 2008/09/17 11:37:58 rgokavar ship $ */
3
4 ----------------------------------
5 -- declaration of global variables
6 ----------------------------------
7
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'HZ_RELATIONSHIP_V2PUB';
9
10 G_DEBUG_COUNT NUMBER := 0;
11 --G_DEBUG BOOLEAN := FALSE;
12
13 -- Bug 2197181: added for mix-n-match project.
14
15 g_rel_mixnmatch_enabled VARCHAR2(1);
16 g_rel_selected_datasources VARCHAR2(255);
17 g_rel_is_datasource_selected VARCHAR2(1) := 'N';
18 g_rel_entity_attr_id NUMBER;
19
20 ------------------------------------
21 -- declaration of private procedures
22 ------------------------------------
23
24 /*PROCEDURE enable_debug;
25
26 PROCEDURE disable_debug;
27 */
28
29
30 PROCEDURE do_create_rel(
31 p_relationship_rec IN OUT NOCOPY RELATIONSHIP_REC_TYPE,
32 x_created_party OUT NOCOPY VARCHAR2,
33 x_relationship_id OUT NOCOPY NUMBER,
34 x_party_id OUT NOCOPY NUMBER,
35 x_party_number OUT NOCOPY VARCHAR2,
36 x_return_status IN OUT NOCOPY VARCHAR2,
37 p_create_org_contact IN VARCHAR2,
38 p_contact_party_id IN NUMBER,
39 p_contact_party_usage_code IN VARCHAR2
40 );
41
42 PROCEDURE do_update_rel(
43 p_relationship_rec IN OUT NOCOPY RELATIONSHIP_REC_TYPE,
44 p_old_relationship_rec IN RELATIONSHIP_REC_TYPE,
45 p_object_version_number IN OUT NOCOPY NUMBER,
46 p_party_object_version_number IN OUT NOCOPY NUMBER,
47 x_return_status IN OUT NOCOPY VARCHAR2
48 );
49
50 PROCEDURE do_create_party(
51 p_party_type IN VARCHAR2,
52 p_relationship_rec IN RELATIONSHIP_REC_TYPE,
53 x_party_id OUT NOCOPY NUMBER,
54 x_party_number OUT NOCOPY VARCHAR2,
55 x_profile_id OUT NOCOPY NUMBER,
56 x_return_status IN OUT NOCOPY VARCHAR2
57 );
58
59 PROCEDURE do_update_party(
60 p_party_type IN VARCHAR2,
61 p_relationship_rec IN RELATIONSHIP_REC_TYPE,
62 p_old_relationship_rec IN RELATIONSHIP_REC_TYPE,
63 p_party_object_version_number IN OUT NOCOPY NUMBER,
64 x_profile_id OUT NOCOPY NUMBER,
65 x_return_status IN OUT NOCOPY VARCHAR2
66 );
67
68 PROCEDURE do_update_party_flags(
69 p_relationship_rec IN RELATIONSHIP_REC_TYPE,
70 p_party_id IN NUMBER
71 );
72
73 -----------------------------
74 -- body of private procedures
75 -----------------------------
76
77
78 /**
79 * PRIVATE PROCEDURE do_circularity_check
80 *
81 * DESCRIPTION
82 *
83 * Performs a circularity check on a relationship network (check for a circuit).
84 *
85 * A circuit is detected if a starting node is found again while traversing
86 * the network from that starting point.
87 *
88 * Because relationships are date-effective, date checking comes into play.
89 * The circuit must exist for a given point in time - there must be date overlap
90 * between *all* the relationships in the circuit. Otherwise it's not a circuit.
91 *
92 * The implementation of the date checking is slightly complex. A method to
93 * determine whether there's a common date range to a set of dates is to
94 * simply take the max of the start dates and the min of the end dates.
95 * If this is a valid date range (max start date <= min end date) then this
96 * date range represents a range that is valid for all the dates in the set.
97 * That logic is employed in this procedure.
98 *
99 * However, the complication:
100 *
101 * Since we may be traversing multiple paths in order to find the circuit,
102 * we must only be comparing dates to all the relationships *in the current path*.
103 * Therefore, we must maintain a "stack" of the max start/min end dates so that
104 * when we start travering a new path, we can restore state to that point.
105 *
106 *
107 * MODIFICATION HISTORY
108 *
109 * 02-12-2003 Chris Saulit o Created.
110 *
111 */
112
113 PROCEDURE do_circularity_check(
114 p_relationship_id IN VARCHAR2 DEFAULT NULL,
115 p_relationship_type IN VARCHAR2,
116 p_start_date IN DATE,
117 p_end_date IN DATE,
118 p_subject_id IN VARCHAR2,
119 p_object_id IN VARCHAR2,
120 p_object_type IN VARCHAR2,
121 p_object_table_name IN VARCHAR2
122 ) IS
123 l_start_date DATE;
124 l_end_date DATE;
125
126 TYPE t_datestack IS TABLE OF DATE INDEX BY BINARY_INTEGER;
127
128 l_min_end_date_stack t_datestack;
129 l_max_start_date_stack t_datestack;
130
131 -- This cursor recursively retrieves all the ancestors for a given child in a particular hierarchy.
132
133 CURSOR c_parent (
134 p_parent_id NUMBER, p_parent_table_name VARCHAR2, p_parent_object_type VARCHAR2,
135 p_relationship_type VARCHAR2, p_relationship_id NUMBER,
136 p_start_date DATE, p_end_date DATE)
137 IS
138 SELECT LEVEL, RELATIONSHIP_ID, SUBJECT_ID, OBJECT_ID, STATUS, START_DATE, END_DATE,
139 SUBJECT_TABLE_NAME, SUBJECT_TYPE
140 FROM HZ_RELATIONSHIPS
141 WHERE RELATIONSHIP_ID <> p_relationship_id
142 START WITH OBJECT_ID = p_parent_id
143 AND OBJECT_TABLE_NAME = p_parent_table_name
144 AND OBJECT_TYPE = p_parent_object_type
145 AND DIRECTION_CODE = 'P'
146 AND RELATIONSHIP_TYPE = p_relationship_type
147 AND RELATIONSHIP_ID <> p_relationship_id
148 -- check for intersection with base relationship
149 AND START_DATE <= p_end_date
150 AND END_DATE >= p_start_date
151 -- Bug 3364626 : Added status='A' condition
152 AND STATUS='A'
153
154 CONNECT BY OBJECT_ID = PRIOR SUBJECT_ID AND OBJECT_TYPE = PRIOR SUBJECT_TYPE AND OBJECT_TABLE_NAME = PRIOR SUBJECT_TABLE_NAME
155 AND DIRECTION_CODE = 'P' AND RELATIONSHIP_TYPE = p_relationship_type
156 AND RELATIONSHIP_ID <> p_relationship_id
157 -- check for intersection against prior relationship
158 AND START_DATE <= PRIOR END_DATE
159 AND END_DATE >= PRIOR START_DATE
160 -- check for intersection against base relationship
161 AND START_DATE <= p_end_date
162 AND END_DATE >= p_start_date
163 -- Bug 3364626 : Added status='A' condition
164 AND STATUS='A';
165
166
167 v_rel c_parent%ROWTYPE;
168 BEGIN
169 l_start_date := NVL(p_start_date,SYSDATE);
170 l_end_date := NVL(p_end_date,TO_DATE('4712.12.31 00:01','YYYY.MM.DD HH24:MI'));
171
172 l_max_start_date_stack(1) := l_start_date;
173 l_min_end_date_stack(1) := l_end_date;
174
175 OPEN c_parent (
176 p_parent_id => p_object_id,
177 p_parent_table_name => p_object_table_name,
178 p_parent_object_type => p_object_type,
179 p_relationship_type => p_relationship_type,
180 p_relationship_id => NVL(p_relationship_id,-1),
181 p_start_date => l_start_date,
182 p_end_date => l_end_date
183 );
184
185 LOOP
186 FETCH c_parent INTO v_rel;
187 IF c_parent%NOTFOUND THEN
188 EXIT;
189 END IF;
190
191 -- The following is a pseudo-stack.
192 -- We either went down a level, or came back up 1 or more levels.
193 -- Need to obtain the max start and min end dates as per the previous
194 -- parent relationship in the branch (or new branch) being traversed.
195
196 IF l_max_start_date_stack.COUNT > v_rel.level THEN
197 -- we came up n levels, so pop the unneeded dates off the stack and throw away
198 l_max_start_date_stack.DELETE(v_rel.level+1,l_max_start_date_stack.COUNT);
199 l_min_end_date_stack.DELETE(v_rel.level+1,l_min_end_date_stack.COUNT);
200 END IF;
201 IF l_max_start_date_stack.COUNT <= v_rel.level+1 THEN
202 -- we went down a level, so push the dates down one level too
203 l_max_start_date_stack(v_rel.level+1) := l_max_start_date_stack(v_rel.level);
204 l_min_end_date_stack(v_rel.level+1) := l_min_end_date_stack(v_rel.level);
205 END IF;
206
207 -- Compare and Save
208
209 IF v_rel.start_date > l_max_start_date_stack(v_rel.level+1) THEN
210 l_max_start_date_stack(v_rel.level+1) := v_rel.start_date;
211 END IF;
212 IF v_rel.end_date < l_min_end_date_stack(v_rel.level+1) THEN
213 l_min_end_date_stack(v_rel.level+1) := v_rel.end_date;
214 END IF;
215
216 IF v_rel.subject_id = p_subject_id THEN
217 -- If the "date tunnel" in the path intersects with the current relationship
218 -- then we have a circuit
219 IF l_max_start_date_stack(v_rel.level+1) <= l_min_end_date_stack(v_rel.level+1)
220 AND
221 -- check if the tunnel intersects with current relationship
222 l_max_start_date_stack(v_rel.level+1) <= p_end_date
223 AND l_min_end_date_stack(v_rel.level+1) >= p_start_date
224 THEN
225 CLOSE c_parent;
226 FND_MESSAGE.SET_NAME('AR', 'HZ_API_CIRCULAR_REL');
227 FND_MESSAGE.SET_TOKEN('RELTYPE', p_relationship_type);
228 FND_MSG_PUB.ADD;
229 RAISE FND_API.G_EXC_ERROR;
230 END IF;
231 END IF;
232
233 END LOOP;
234
235 CLOSE c_parent;
236
237 END do_circularity_check;
238
239
240 /**
241 * PRIVATE PROCEDURE enable_debug
242 *
243 * DESCRIPTION
244 * Turn on debug mode.
245 *
246 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
247 * HZ_UTILITY_V2PUB.enable_debug
248 *
249 * MODIFICATION HISTORY
250 *
251 * 07-23-2001 Jianying Huang o Created.
252 *
253 */
254
255 /*PROCEDURE enable_debug IS
256
257 BEGIN
258
259 G_DEBUG_COUNT := G_DEBUG_COUNT + 1;
260
261 IF G_DEBUG_COUNT = 1 THEN
262 IF FND_PROFILE.value( 'HZ_API_FILE_DEBUG_ON' ) = 'Y' OR
263 FND_PROFILE.value( 'HZ_API_DBMS_DEBUG_ON' ) = 'Y'
264 THEN
265 HZ_UTILITY_V2PUB.enable_debug;
266 G_DEBUG := TRUE;
267 END IF;
268 END IF;
269
270 END enable_debug;
271 */
272
273 /**
274 * PRIVATE PROCEDURE disable_debug
275 *
276 * DESCRIPTION
277 * Turn off debug mode.
278 *
279 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
280 * HZ_UTILITY_V2PUB.disable_debug
281 *
282 * MODIFICATION HISTORY
283 *
284 * 07-23-2001 Jianying Huang o Created.
285 *
286 */
287
288 /*PROCEDURE disable_debug IS
289
290 BEGIN
291
292 IF G_DEBUG THEN
293 G_DEBUG_COUNT := G_DEBUG_COUNT - 1;
294
295 IF G_DEBUG_COUNT = 0 THEN
296 HZ_UTILITY_V2PUB.disable_debug;
297 G_DEBUG := FALSE;
298 END IF;
299 END IF;
300
301 END disable_debug;
302 */
303
304 /*===========================================================================+
305 | PROCEDURE
306 | do_create_rel
307 |
308 | DESCRIPTION
309 | Creates relationship and party for party_relationship.
310 |
311 | SCOPE - PRIVATE
312 |
313 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
314 |
315 | ARGUMENTS : IN:
316 | p_create_party
317 | OUT:
318 | x_party_relationship_id
319 | x_party_id
320 | x_party_number
321 | IN/ OUT:
322 | p_party_rel_rec
323 | x_return_status
324 |
325 | RETURNS : NONE
326 |
327 | NOTES
328 |
329 | MODIFICATION HISTORY
330 |
331 | 19-FEB-2004 Rajib Ranjan Borah o Bug 3306941.The meaning is to be
332 | displayed instead of the lookup_code
333 | in error message HZ_API_MULTIPLE_PARENT.
334 | 04-JAN-2005 Rajib Ranjan Borah o SSM SST Integration and Extension.
335 | For non-profile entities, the concept of select
336 | /de-select data-sources is obsoleted.
337 +===========================================================================*/
338
339 PROCEDURE do_create_rel(
340 p_relationship_rec IN OUT NOCOPY RELATIONSHIP_REC_TYPE,
341 x_created_party OUT NOCOPY VARCHAR2,
342 x_relationship_id OUT NOCOPY NUMBER,
343 x_party_id OUT NOCOPY NUMBER,
344 x_party_number OUT NOCOPY VARCHAR2,
345 x_return_status IN OUT NOCOPY VARCHAR2,
346 p_create_org_contact IN VARCHAR2,
347 p_contact_party_id IN NUMBER,
348 p_contact_party_usage_code IN VARCHAR2
349 ) IS
350
351 -- this cursor will uniquely identify a record
352 -- in hz_relationship_types package
353 CURSOR c_rel_type
354 IS
355 SELECT RELATIONSHIP_TYPE,
356 FORWARD_REL_CODE,
357 BACKWARD_REL_CODE,
358 CREATE_PARTY_FLAG,
359 ALLOW_RELATE_TO_SELF_FLAG,
360 HIERARCHICAL_FLAG,
361 ALLOW_CIRCULAR_RELATIONSHIPS,
362 DIRECTION_CODE,
363 RELATIONSHIP_TYPE_ID,
364 NVL(MULTIPLE_PARENT_ALLOWED, 'N') MULTIPLE_PARENT_ALLOWED
365 FROM HZ_RELATIONSHIP_TYPES
366 WHERE RELATIONSHIP_TYPE = p_relationship_rec.relationship_type
367 AND FORWARD_REL_CODE = p_relationship_rec.relationship_code
368 AND SUBJECT_TYPE = p_relationship_rec.subject_type
369 AND OBJECT_TYPE = p_relationship_rec.object_type
370 AND STATUS = 'A';
371
372 r_rel_type c_rel_type%ROWTYPE;
373
374 l_relationship_id NUMBER := p_relationship_rec.relationship_id;
375 l_rowid ROWID := NULL;
376 l_count NUMBER;
377 l_profile_id NUMBER;
378 l_directional_flag VARCHAR2(1);
379 l_back_direction VARCHAR2(30);
380 l_msg_count NUMBER;
381 l_msg_data VARCHAR2(2000);
382 l_end_date DATE;
383 l_party_rel_rec RELATIONSHIP_REC_TYPE;
384 l_dummy VARCHAR2(1) := 'Y';
385 l_debug_prefix VARCHAR2(30) := '';
386 l_hierarchy_rec HZ_HIERARCHY_PUB.HIERARCHY_NODE_REC_TYPE;
387 l_parent_id NUMBER;
388 l_parent_object_type VARCHAR2(30);
389 l_parent_table_name VARCHAR2(30);
390 l_child_id NUMBER;
391 l_child_object_type VARCHAR2(30);
392 l_child_table_name VARCHAR2(30);
393 l_temp_parent_id NUMBER;
394 l_temp_parent_table_name VARCHAR2(30);
395 l_temp_parent_object_type VARCHAR2(30);
396 l_parent_flag VARCHAR2(1);
397 p_org_contact_rec HZ_PARTY_CONTACT_V2PUB.org_contact_rec_type;
398 l_contact_number VARCHAR2(30) := p_org_contact_rec.contact_number;
399 l_gen_contact_number VARCHAR2(1);
400 -- Bug 3306941.
401 l_meaning VARCHAR2(80);
402
403 l_party_usg_assignment_rec HZ_PARTY_USG_ASSIGNMENT_PVT.party_usg_assignment_rec_type;
404 l_party_usage_validation_level NUMBER;
405 TYPE t_number15_table IS TABLE OF NUMBER(15);
406 TYPE t_varchar30_table IS TABLE OF VARCHAR2(30);
407 l_party_id_tbl t_number15_table := t_number15_table();
408 l_party_usage_code_tbl t_varchar30_table := t_varchar30_table();
409
410 BEGIN
411
412 -- Debug info.
413 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
414 hz_utility_v2pub.debug(p_message=>'do_create_rel (+)',
415 p_prefix=>l_debug_prefix,
416 p_msg_level=>fnd_log.level_procedure);
417 END IF;
418
419 -- Generate primary key from sequence if not passed in. If this values already exists in
420 -- the database, keep generating till a unique value is found.
421 -- If primary key value is passed, check for uniqueness.
422
423 IF l_relationship_id = FND_API.G_MISS_NUM
424 OR
425 l_relationship_id IS NULL
426 THEN
427 WHILE l_dummy = 'Y' LOOP
428 BEGIN
429 SELECT HZ_RELATIONSHIPS_S.NEXTVAL
430 INTO l_relationship_id
431 FROM DUAL;
432
433 SELECT 'Y'
434 INTO l_dummy
435 FROM HZ_RELATIONSHIPS
436 WHERE RELATIONSHIP_ID = l_relationship_id
437 AND DIRECTIONAL_FLAG = 'F';
438
439 EXCEPTION
440 WHEN NO_DATA_FOUND THEN
441 l_dummy := 'N';
442 END;
443 END LOOP;
444
445 ELSE
446 BEGIN
447 SELECT 'Y'
448 INTO l_dummy
449 FROM HZ_RELATIONSHIPS
450 WHERE RELATIONSHIP_ID = l_relationship_id
451 AND DIRECTIONAL_FLAG = 'F';
452
453 FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_COLUMN');
454 FND_MESSAGE.SET_TOKEN('COLUMN', 'relationship_id');
455 FND_MSG_PUB.ADD;
456 RAISE FND_API.G_EXC_ERROR;
457
458 EXCEPTION
459 WHEN NO_DATA_FOUND THEN
460 NULL;
461 END;
462
463 END IF;
464
465 x_relationship_id := l_relationship_id;
466
467 -- Debug info.
468 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
469 hz_utility_v2pub.debug(p_message=>'Obtained relationship_id : '||x_relationship_id,
470 p_prefix =>l_debug_prefix,
471 p_msg_level=>fnd_log.level_statement);
472 END IF;
473
474
475 -- validate the relationship record
476 HZ_REGISTRY_VALIDATE_V2PUB.validate_relationship(
477 'C',
478 p_relationship_rec,
479 l_rowid,
480 x_return_status);
481
482 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
483 RAISE FND_API.G_EXC_ERROR;
484 END IF;
485
486 -- default end date to 31-DEC-4712
487 IF p_relationship_rec.end_date IS NULL
488 OR
489 p_relationship_rec.end_date = FND_API.G_MISS_DATE
490 THEN
491 l_end_date := to_date('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS');
492 ELSE
493 l_end_date := p_relationship_rec.end_date;
494 END IF;
495
496 -- Open the relationship_type record and get all the info
497 OPEN c_rel_type;
498 FETCH c_rel_type INTO r_rel_type;
499 CLOSE c_rel_type;
500
501 -- if no relationship type record found, then error out NOCOPY
502 IF r_rel_type.relationship_type is null THEN
503 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_FK');
504 FND_MESSAGE.SET_TOKEN('FK', 'relationship_code, subject_type, object_type');
505 FND_MESSAGE.SET_TOKEN('COLUMN', 'forward_rel_code, subject_type, object_type');
506 FND_MESSAGE.SET_TOKEN('TABLE', 'hz_relationship_types');
507 FND_MSG_PUB.ADD;
508 RAISE FND_API.G_EXC_ERROR;
509 END IF;
510
511 -- Debug info.
512 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
513 hz_utility_v2pub.debug(p_message=>'relationship type record found',
514 p_prefix =>l_debug_prefix,
515 p_msg_level=>fnd_log.level_statement);
516 END IF;
517
518
519 -- decide who is parent and who is child in this relationship.
520 -- if relationship type record is 'P' type, then subject is parent, else object
521 IF r_rel_type.direction_code = 'P' THEN
522 l_parent_id := p_relationship_rec.subject_id;
523 l_parent_table_name := p_relationship_rec.subject_table_name;
524 l_parent_object_type := p_relationship_rec.subject_type;
525 l_child_id := p_relationship_rec.object_id;
526 l_child_table_name := p_relationship_rec.object_table_name;
527 l_child_object_type := p_relationship_rec.object_type;
528 ELSIF r_rel_type.direction_code = 'C' THEN
529 l_parent_id := p_relationship_rec.object_id;
530 l_parent_table_name := p_relationship_rec.object_table_name;
531 l_parent_object_type := p_relationship_rec.object_type;
532 l_child_id := p_relationship_rec.subject_id;
533 l_child_table_name := p_relationship_rec.subject_table_name;
534 l_child_object_type := p_relationship_rec.subject_type;
535 END IF;
536
537 -- if the relationship type is hierarchical, then we have to check
538 -- whether there is already a parent present for the child in the same
539 -- hierarchy/relationship type. if so, then we would not allow creation.
540 IF r_rel_type.hierarchical_flag = 'Y' THEN
541 -- it needs to be done if multiple_parent_allowed is 'N'
542 IF r_rel_type.multiple_parent_allowed = 'N' THEN
543 BEGIN
544 SELECT 1 INTO l_count
545 FROM HZ_RELATIONSHIPS
546 WHERE OBJECT_ID = l_child_id
547 AND OBJECT_TABLE_NAME = l_child_table_name
548 AND OBJECT_TYPE = l_child_object_type
549 AND RELATIONSHIP_TYPE = p_relationship_rec.relationship_type
550 AND DIRECTION_CODE = 'P'
551 -- Bug 3817438 : Added condition to check only active relationships
552 AND STATUS= 'A'
553 AND (START_DATE BETWEEN NVL(p_relationship_rec.start_date, SYSDATE)
554 AND NVL(p_relationship_rec.end_date, TO_DATE('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS'))
555 OR
556 END_DATE BETWEEN NVL(p_relationship_rec.start_date, SYSDATE)
557 AND NVL(p_relationship_rec.end_date, TO_DATE('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS'))
558 OR
559 NVL(p_relationship_rec.start_date, SYSDATE) BETWEEN START_DATE AND END_DATE
560 OR
561 NVL(p_relationship_rec.end_date, TO_DATE('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS')) BETWEEN START_DATE AND END_DATE
562 )
563 -- Bug 3294936 : Added ROWNUM=1 condition
564 AND ROWNUM=1;
565
566
567 -- there is already a parent, so raise error
568 --Bug 3306941.The meaning is to be displayed instead of the lookup_code.
569 SELECT MEANING
570 INTO l_meaning
571 -- Bug 3664939 : Use fnd_lookup_values_vl to get lookup meaning
572 FROM FND_LOOKUP_VALUES_VL
573 WHERE LOOKUP_TYPE = 'HZ_RELATIONSHIP_TYPE'
574 AND LOOKUP_CODE = p_relationship_rec.relationship_type
575 AND VIEW_APPLICATION_ID = 222
576 AND ROWNUM =1;
577
578 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MULTIPLE_PARENT');
579 FND_MESSAGE.SET_TOKEN('RELTYPE', l_meaning);
580 FND_MSG_PUB.ADD;
581 RAISE FND_API.G_EXC_ERROR;
582
583 EXCEPTION
584 WHEN NO_DATA_FOUND THEN
585 -- no parent found, proceed
586 NULL;
587 END;
588 END IF;
589 END IF;
590
591
592
593 -- Bug 2797506 begin. Circularity check logic is in a new procedure.
594
595 IF r_rel_type.hierarchical_flag = 'Y' OR r_rel_type.allow_circular_relationships = 'N'
596 THEN
597 -- check for circularity. This procedure will raise exception if found.
598 do_circularity_check(
599 p_relationship_id => NULL, -- relationship has not been created yet!
600 p_relationship_type => r_rel_type.relationship_type,
601 p_start_date => NVL(p_relationship_rec.start_date, SYSDATE),
602 p_end_date => NVL(p_relationship_rec.end_date, TO_DATE('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS')),
603 p_subject_id => l_child_id,
604 p_object_id => l_parent_id,
605 p_object_type => l_parent_object_type,
606 p_object_table_name => l_parent_table_name
607 );
608 END IF;
609
610 -- Bug 2797506 end.
611
612 -- subject_id and object_id must not have the same value, unless relationship type allows
613 IF r_rel_type.allow_relate_to_self_flag = 'N'
614 AND
615 p_relationship_rec.subject_id = p_relationship_rec.object_id
616 THEN
617 FND_MESSAGE.SET_NAME('AR', 'HZ_API_SUBJECT_OBJECT_IDS');
618 FND_MSG_PUB.ADD;
619 RAISE FND_API.G_EXC_ERROR;
620 END IF;
621
622 -- build the record for creation of relationship party record
623 l_party_rel_rec.party_rec := p_relationship_rec.party_rec;
624 l_party_rel_rec.subject_id := p_relationship_rec.subject_id;
625 l_party_rel_rec.object_id := p_relationship_rec.object_id;
626 l_party_rel_rec.created_by_module := p_relationship_rec.created_by_module;
627 l_party_rel_rec.application_id := p_relationship_rec.application_id;
628
629 -- the PARTY_RELATIONSHIP type party will be created if
630 -- the relationship type has create_party_flag = 'Y' and
631 -- both the subject_table_name and object_table_name are
632 -- 'HZ_PARTIES'
633 IF r_rel_type.create_party_flag = 'Y'
634 AND
635 p_relationship_rec.subject_table_name = 'HZ_PARTIES'
636 AND
637 p_relationship_rec.object_table_name = 'HZ_PARTIES'
638 THEN
639
640 -- Debug info.
641 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
642 hz_utility_v2pub.debug(p_message=>'creating relationship party',
643 p_prefix =>l_debug_prefix,
644 p_msg_level=>fnd_log.level_statement);
645 END IF;
646
647
648 x_created_party := 'Y';
649 do_create_party(
650 p_party_type => 'PARTY_RELATIONSHIP',
651 p_relationship_rec => l_party_rel_rec,
652 x_party_id => x_party_id,
653 x_party_number => x_party_number,
654 x_profile_id => l_profile_id,
655 x_return_status => x_return_status
656 );
657 p_relationship_rec.party_rec.party_id := x_party_id;
658 p_relationship_rec.party_rec.party_number := x_party_number;
659
660 ELSE
661 x_created_party := 'N';
662 END IF;
663
664 -- Denormalize flags to HZ_PARTIES:
665 -- COMPETITOR_FLAG
666 -- REFERENCE_USE_FLAG
667 -- THIRD_PARTY_FLAG
668 -- Denormalization will be done only if content_source_type
669 -- is 'USER_ENTERED' and both subject_table_name and
670 -- object_table_name are 'HZ_PARTIES'
671
672 -- Bug 2197181: added for mix-n-match project. Denormalize
673 -- the three flags when the data source is visible (i.e.
674 -- selected).
675
676 -- SSM SST Integration and Extension
677 -- For non-profile entities, the concept of select/de-select data-sources is obsoleted.
678 -- There is no need to check if the data-source is selected.
679
680 IF p_relationship_rec.relationship_code IN
681 ('COMPETITOR_OF', 'REFERENCE_FOR', 'PARTNER_OF') AND
682 /* g_rel_is_datasource_selected = 'Y' AND */
683 /*
684 (p_relationship_rec.content_source_type = 'USER_ENTERED'
685 OR
686 p_relationship_rec.content_source_type IS NULL
687 )
688 */
689 p_relationship_rec.subject_table_name = 'HZ_PARTIES'
690 AND
691 p_relationship_rec.object_table_name = 'HZ_PARTIES'
692 THEN
693
694 -- Debug info.
695 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
696 hz_utility_v2pub.debug(p_message=>'denormalizing to hz_parties',
697 p_prefix =>l_debug_prefix,
698 p_msg_level=>fnd_log.level_statement);
699 END IF;
700
701
702 do_update_party_flags(
703 p_relationship_rec,
704 p_relationship_rec.subject_id);
705 END IF;
706
707 p_relationship_rec.relationship_id := l_relationship_id;
708
709 -- Debug info.
710 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
711 hz_utility_v2pub.debug(p_message=>'HZ_RELATIONSHIPS_PKG.Insert_Row-1 (+)',
712 p_prefix=>l_debug_prefix,
713 p_msg_level=>fnd_log.level_procedure);
714 END IF;
715
716 -- Call table-handler to create the forward record.
717 HZ_RELATIONSHIPS_PKG.Insert_Row (
718 X_RELATIONSHIP_ID => p_relationship_rec.relationship_id,
719 X_SUBJECT_ID => p_relationship_rec.subject_id,
720 X_SUBJECT_TYPE => p_relationship_rec.subject_type,
721 X_SUBJECT_TABLE_NAME => p_relationship_rec.subject_table_name,
722 X_OBJECT_ID => p_relationship_rec.object_id,
723 X_OBJECT_TYPE => p_relationship_rec.object_type,
724 X_OBJECT_TABLE_NAME => p_relationship_rec.object_table_name,
725 X_PARTY_ID => x_party_id,
726 X_RELATIONSHIP_CODE => p_relationship_rec.relationship_code,
727 X_DIRECTIONAL_FLAG => 'F',
728 X_COMMENTS => p_relationship_rec.comments,
729 X_START_DATE => p_relationship_rec.start_date,
730 X_END_DATE => l_end_date,
731 X_STATUS => p_relationship_rec.status,
732 X_ATTRIBUTE_CATEGORY => p_relationship_rec.attribute_category,
733 X_ATTRIBUTE1 => p_relationship_rec.attribute1,
734 X_ATTRIBUTE2 => p_relationship_rec.attribute2,
735 X_ATTRIBUTE3 => p_relationship_rec.attribute3,
736 X_ATTRIBUTE4 => p_relationship_rec.attribute4,
737 X_ATTRIBUTE5 => p_relationship_rec.attribute5,
738 X_ATTRIBUTE6 => p_relationship_rec.attribute6,
739 X_ATTRIBUTE7 => p_relationship_rec.attribute7,
740 X_ATTRIBUTE8 => p_relationship_rec.attribute8,
741 X_ATTRIBUTE9 => p_relationship_rec.attribute9,
742 X_ATTRIBUTE10 => p_relationship_rec.attribute10,
743 X_ATTRIBUTE11 => p_relationship_rec.attribute11,
744 X_ATTRIBUTE12 => p_relationship_rec.attribute12,
745 X_ATTRIBUTE13 => p_relationship_rec.attribute13,
746 X_ATTRIBUTE14 => p_relationship_rec.attribute14,
747 X_ATTRIBUTE15 => p_relationship_rec.attribute15,
748 X_ATTRIBUTE16 => p_relationship_rec.attribute16,
749 X_ATTRIBUTE17 => p_relationship_rec.attribute17,
750 X_ATTRIBUTE18 => p_relationship_rec.attribute18,
751 X_ATTRIBUTE19 => p_relationship_rec.attribute19,
752 X_ATTRIBUTE20 => p_relationship_rec.attribute20,
753 X_CONTENT_SOURCE_TYPE => p_relationship_rec.content_source_type,
754 X_RELATIONSHIP_TYPE => p_relationship_rec.relationship_type,
755 X_OBJECT_VERSION_NUMBER => 1,
756 X_CREATED_BY_MODULE => p_relationship_rec.created_by_module,
757 X_APPLICATION_ID => p_relationship_rec.application_id,
758 X_ADDITIONAL_INFORMATION1 => p_relationship_rec.additional_information1,
759 X_ADDITIONAL_INFORMATION2 => p_relationship_rec.additional_information2,
760 X_ADDITIONAL_INFORMATION3 => p_relationship_rec.additional_information3,
761 X_ADDITIONAL_INFORMATION4 => p_relationship_rec.additional_information4,
762 X_ADDITIONAL_INFORMATION5 => p_relationship_rec.additional_information5,
763 X_ADDITIONAL_INFORMATION6 => p_relationship_rec.additional_information6,
764 X_ADDITIONAL_INFORMATION7 => p_relationship_rec.additional_information7,
765 X_ADDITIONAL_INFORMATION8 => p_relationship_rec.additional_information8,
766 X_ADDITIONAL_INFORMATION9 => p_relationship_rec.additional_information9,
767 X_ADDITIONAL_INFORMATION10 => p_relationship_rec.additional_information10,
768 X_ADDITIONAL_INFORMATION11 => p_relationship_rec.additional_information11,
769 X_ADDITIONAL_INFORMATION12 => p_relationship_rec.additional_information12,
770 X_ADDITIONAL_INFORMATION13 => p_relationship_rec.additional_information13,
771 X_ADDITIONAL_INFORMATION14 => p_relationship_rec.additional_information14,
772 X_ADDITIONAL_INFORMATION15 => p_relationship_rec.additional_information15,
773 X_ADDITIONAL_INFORMATION16 => p_relationship_rec.additional_information16,
774 X_ADDITIONAL_INFORMATION17 => p_relationship_rec.additional_information17,
775 X_ADDITIONAL_INFORMATION18 => p_relationship_rec.additional_information18,
776 X_ADDITIONAL_INFORMATION19 => p_relationship_rec.additional_information19,
777 X_ADDITIONAL_INFORMATION20 => p_relationship_rec.additional_information20,
778 X_ADDITIONAL_INFORMATION21 => p_relationship_rec.additional_information21,
779 X_ADDITIONAL_INFORMATION22 => p_relationship_rec.additional_information22,
780 X_ADDITIONAL_INFORMATION23 => p_relationship_rec.additional_information23,
781 X_ADDITIONAL_INFORMATION24 => p_relationship_rec.additional_information24,
782 X_ADDITIONAL_INFORMATION25 => p_relationship_rec.additional_information25,
783 X_ADDITIONAL_INFORMATION26 => p_relationship_rec.additional_information26,
784 X_ADDITIONAL_INFORMATION27 => p_relationship_rec.additional_information27,
785 X_ADDITIONAL_INFORMATION28 => p_relationship_rec.additional_information28,
786 X_ADDITIONAL_INFORMATION29 => p_relationship_rec.additional_information29,
787 X_ADDITIONAL_INFORMATION30 => p_relationship_rec.additional_information30,
788 X_DIRECTION_CODE => r_rel_type.direction_code,
789 X_PERCENTAGE_OWNERSHIP => p_relationship_rec.percentage_ownership,
790 X_ACTUAL_CONTENT_SOURCE => p_relationship_rec.actual_content_source
791 );
792
793 -- Debug info.
794 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
795 hz_utility_v2pub.debug(p_message=>'HZ_RELATIONSHIPS_PKG.Insert_Row-1 (-) ' ||
796 'x_relationship_id = ' || p_relationship_rec.relationship_id,
797 p_prefix=>l_debug_prefix,
798 p_msg_level=>fnd_log.level_procedure);
799 END IF;
800
801 -- Debug info.
802 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
803 hz_utility_v2pub.debug(p_message=>'HZ_RELATIONSHIPS_PKG.Insert_Row-2 (+) ',
804 p_prefix=>l_debug_prefix,
805 p_msg_level=>fnd_log.level_procedure);
806 END IF;
807
808 -- determine the direction_code for the backward record
809 IF r_rel_type.direction_code = 'P' THEN
810 l_back_direction := 'C';
811 ELSIF r_rel_type.direction_code = 'C' THEN
812 l_back_direction := 'P';
813 ELSE
814 l_back_direction := 'N';
815 END IF;
816
817 -- Call table-handler again to create the backward record.
818 -- This is done because for every relationship we want to
819 -- create both forward and backward relationship.
820 HZ_RELATIONSHIPS_PKG.Insert_Row (
821 X_RELATIONSHIP_ID => p_relationship_rec.relationship_id,
822 X_SUBJECT_ID => p_relationship_rec.object_id,
823 X_SUBJECT_TYPE => p_relationship_rec.object_type,
824 X_SUBJECT_TABLE_NAME => p_relationship_rec.object_table_name,
825 X_OBJECT_ID => p_relationship_rec.subject_id,
826 X_OBJECT_TYPE => p_relationship_rec.subject_type,
827 X_OBJECT_TABLE_NAME => p_relationship_rec.subject_table_name,
828 X_PARTY_ID => x_party_id,
829 X_RELATIONSHIP_CODE => r_rel_type.backward_rel_code,
830 X_DIRECTIONAL_FLAG => 'B',
831 X_COMMENTS => p_relationship_rec.comments,
832 X_START_DATE => p_relationship_rec.start_date,
833 X_END_DATE => l_end_date,
834 X_STATUS => p_relationship_rec.status,
835 X_ATTRIBUTE_CATEGORY => p_relationship_rec.attribute_category,
836 X_ATTRIBUTE1 => p_relationship_rec.attribute1,
837 X_ATTRIBUTE2 => p_relationship_rec.attribute2,
838 X_ATTRIBUTE3 => p_relationship_rec.attribute3,
839 X_ATTRIBUTE4 => p_relationship_rec.attribute4,
840 X_ATTRIBUTE5 => p_relationship_rec.attribute5,
841 X_ATTRIBUTE6 => p_relationship_rec.attribute6,
842 X_ATTRIBUTE7 => p_relationship_rec.attribute7,
843 X_ATTRIBUTE8 => p_relationship_rec.attribute8,
844 X_ATTRIBUTE9 => p_relationship_rec.attribute9,
845 X_ATTRIBUTE10 => p_relationship_rec.attribute10,
846 X_ATTRIBUTE11 => p_relationship_rec.attribute11,
847 X_ATTRIBUTE12 => p_relationship_rec.attribute12,
848 X_ATTRIBUTE13 => p_relationship_rec.attribute13,
849 X_ATTRIBUTE14 => p_relationship_rec.attribute14,
850 X_ATTRIBUTE15 => p_relationship_rec.attribute15,
851 X_ATTRIBUTE16 => p_relationship_rec.attribute16,
852 X_ATTRIBUTE17 => p_relationship_rec.attribute17,
853 X_ATTRIBUTE18 => p_relationship_rec.attribute18,
854 X_ATTRIBUTE19 => p_relationship_rec.attribute19,
855 X_ATTRIBUTE20 => p_relationship_rec.attribute20,
856 X_CONTENT_SOURCE_TYPE => p_relationship_rec.content_source_type,
857 X_RELATIONSHIP_TYPE => r_rel_type.relationship_type,
858 X_OBJECT_VERSION_NUMBER => 1,
859 X_CREATED_BY_MODULE => p_relationship_rec.created_by_module,
860 X_APPLICATION_ID => p_relationship_rec.application_id,
861 X_ADDITIONAL_INFORMATION1 => p_relationship_rec.additional_information1,
862 X_ADDITIONAL_INFORMATION2 => p_relationship_rec.additional_information2,
863 X_ADDITIONAL_INFORMATION3 => p_relationship_rec.additional_information3,
864 X_ADDITIONAL_INFORMATION4 => p_relationship_rec.additional_information4,
865 X_ADDITIONAL_INFORMATION5 => p_relationship_rec.additional_information5,
866 X_ADDITIONAL_INFORMATION6 => p_relationship_rec.additional_information6,
867 X_ADDITIONAL_INFORMATION7 => p_relationship_rec.additional_information7,
868 X_ADDITIONAL_INFORMATION8 => p_relationship_rec.additional_information8,
869 X_ADDITIONAL_INFORMATION9 => p_relationship_rec.additional_information9,
870 X_ADDITIONAL_INFORMATION10 => p_relationship_rec.additional_information10,
871 X_ADDITIONAL_INFORMATION11 => p_relationship_rec.additional_information11,
872 X_ADDITIONAL_INFORMATION12 => p_relationship_rec.additional_information12,
873 X_ADDITIONAL_INFORMATION13 => p_relationship_rec.additional_information13,
874 X_ADDITIONAL_INFORMATION14 => p_relationship_rec.additional_information14,
875 X_ADDITIONAL_INFORMATION15 => p_relationship_rec.additional_information15,
876 X_ADDITIONAL_INFORMATION16 => p_relationship_rec.additional_information16,
877 X_ADDITIONAL_INFORMATION17 => p_relationship_rec.additional_information17,
878 X_ADDITIONAL_INFORMATION18 => p_relationship_rec.additional_information18,
879 X_ADDITIONAL_INFORMATION19 => p_relationship_rec.additional_information19,
880 X_ADDITIONAL_INFORMATION20 => p_relationship_rec.additional_information20,
881 X_ADDITIONAL_INFORMATION21 => p_relationship_rec.additional_information21,
882 X_ADDITIONAL_INFORMATION22 => p_relationship_rec.additional_information22,
883 X_ADDITIONAL_INFORMATION23 => p_relationship_rec.additional_information23,
884 X_ADDITIONAL_INFORMATION24 => p_relationship_rec.additional_information24,
885 X_ADDITIONAL_INFORMATION25 => p_relationship_rec.additional_information25,
886 X_ADDITIONAL_INFORMATION26 => p_relationship_rec.additional_information26,
887 X_ADDITIONAL_INFORMATION27 => p_relationship_rec.additional_information27,
888 X_ADDITIONAL_INFORMATION28 => p_relationship_rec.additional_information28,
889 X_ADDITIONAL_INFORMATION29 => p_relationship_rec.additional_information29,
890 X_ADDITIONAL_INFORMATION30 => p_relationship_rec.additional_information30,
891 X_DIRECTION_CODE => l_back_direction,
892 X_PERCENTAGE_OWNERSHIP => p_relationship_rec.percentage_ownership,
893 X_ACTUAL_CONTENT_SOURCE => p_relationship_rec.actual_content_source
894 );
895
896 -- Debug info.
897 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
898 hz_utility_v2pub.debug(p_message=>'HZ_RELATIONSHIPS_PKG.Insert_Row-2 (-) ' ||
899 'x_relationship_id = ' || p_relationship_rec.relationship_id,
900 p_prefix=>l_debug_prefix,
901 p_msg_level=>fnd_log.level_procedure);
902 END IF;
903
904 -- maintain hierarchy of relationships
905 -- check if the relationship type is hierarchical
906 IF r_rel_type.hierarchical_flag = 'Y' THEN
907 -- check if relationship type is parent one
908 IF r_rel_type.direction_code = 'P' THEN
909 -- assign the subject to parent for hierarchy
910 l_hierarchy_rec.hierarchy_type := r_rel_type.relationship_type;
911 l_hierarchy_rec.parent_id := p_relationship_rec.subject_id;
912 l_hierarchy_rec.parent_table_name := p_relationship_rec.subject_table_name;
913 l_hierarchy_rec.parent_object_type := p_relationship_rec.subject_type;
914 l_hierarchy_rec.child_id := p_relationship_rec.object_id;
915 l_hierarchy_rec.child_table_name := p_relationship_rec.object_table_name;
916 l_hierarchy_rec.child_object_type := p_relationship_rec.object_type;
917 l_hierarchy_rec.effective_start_date := NVL(p_relationship_rec.start_date,SYSDATE);
918 l_hierarchy_rec.effective_end_date := l_end_date;
919 l_hierarchy_rec.relationship_id := p_relationship_rec.relationship_id;
920 l_hierarchy_rec.status := NVL(p_relationship_rec.status, 'A');
921 ELSIF r_rel_type.direction_code = 'C' THEN
922 -- assign the object to parent
923 l_hierarchy_rec.hierarchy_type := r_rel_type.relationship_type;
924 l_hierarchy_rec.parent_id := p_relationship_rec.object_id;
925 l_hierarchy_rec.parent_table_name := p_relationship_rec.object_table_name;
926 l_hierarchy_rec.parent_object_type := p_relationship_rec.object_type;
927 l_hierarchy_rec.child_id := p_relationship_rec.subject_id;
928 l_hierarchy_rec.child_table_name := p_relationship_rec.subject_table_name;
929 l_hierarchy_rec.child_object_type := p_relationship_rec.subject_type;
930 /* Put in the NVL since otherwise insert to hz_hierarchy_nodes was failing */
931 l_hierarchy_rec.effective_start_date := NVL(p_relationship_rec.start_date,SYSDATE);
932 l_hierarchy_rec.effective_end_date := l_end_date;
933 l_hierarchy_rec.relationship_id := p_relationship_rec.relationship_id;
934 l_hierarchy_rec.status := NVL(p_relationship_rec.status, 'A');
935 END IF;
936
937 HZ_HIERARCHY_PUB.create_link(
938 p_init_msg_list => FND_API.G_FALSE,
939 p_hierarchy_node_rec => l_hierarchy_rec,
940 x_return_status => x_return_status,
941 x_msg_count => l_msg_count,
942 x_msg_data => l_msg_data
943 );
944
945 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
946 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
947 RAISE FND_API.G_EXC_ERROR;
948 ELSE
949 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
950 END IF;
951 END IF;
952
953 END IF;
954 /*****************************************************************************
955 Create Org Contact
956 *****************************************************************************/
957 -- Check whether the relationship has SUBJECT_TYPE = 'PERSON' or OBJECTTYPE = 'PERSON'
958 -- and the other entity is a 'PERSON' or 'ORGANIZATION' or 'GROUP'.
959
960 IF ( ((p_relationship_rec.object_type = 'PERSON' AND
961 p_relationship_rec.subject_type IN ('ORGANIZATION','PERSON','GROUP')) OR
962 (p_relationship_rec.subject_type = 'PERSON' AND
963 p_relationship_rec.object_type IN ('ORGANIZATION','GROUP')))
964 OR
965 ((p_relationship_rec.object_type = 'ORGANIZATION' AND
966 p_relationship_rec.subject_type IN ('ORGANIZATION','PERSON','GROUP')) OR
967 (p_relationship_rec.subject_type = 'ORGANIZATION' AND
968 p_relationship_rec.object_type IN ('PERSON','GROUP')))
969 OR
970 ((p_relationship_rec.object_type = 'GROUP' AND
971 p_relationship_rec.subject_type IN ('ORGANIZATION','PERSON','GROUP')) OR
972 (p_relationship_rec.subject_type = 'GROUP' AND
973 p_relationship_rec.object_type IN ('PERSON','ORGANIZATION')))
974 )
975 THEN
976 IF p_create_org_contact = 'Y' THEN
977 IF l_contact_number = FND_API.G_MISS_CHAR OR l_contact_number IS NULL THEN
978 l_count := 1;
979 WHILE l_count > 0 LOOP
980 SELECT to_char(hz_contact_numbers_s.nextval)
981 INTO l_contact_number FROM dual;
982
983 BEGIN
984 SELECT 1
985 INTO l_count
986 FROM HZ_ORG_CONTACTS
987 WHERE CONTACT_NUMBER = l_contact_number;
988 l_count := 1;
989
990 EXCEPTION
991 WHEN NO_DATA_FOUND THEN
992 l_count := 0;
993 END;
994
995 END LOOP;
996 END IF;
997 p_org_contact_rec.created_by_module := p_relationship_rec.created_by_module;
998 p_org_contact_rec.application_id := p_relationship_rec.application_id;
999 p_org_contact_rec.party_rel_rec.status := p_relationship_rec.status;
1000 p_org_contact_rec.contact_number := l_contact_number;
1001 HZ_ORG_CONTACTS_PKG.Insert_Row (
1002 X_ORG_CONTACT_ID => p_org_contact_rec.org_contact_id,
1003 X_PARTY_RELATIONSHIP_ID => p_relationship_rec.relationship_id,
1004 X_COMMENTS => p_org_contact_rec.comments,
1005 X_CONTACT_NUMBER => p_org_contact_rec.contact_number,
1006 X_DEPARTMENT_CODE => p_org_contact_rec.department_code,
1007 X_DEPARTMENT => p_org_contact_rec.department,
1008 X_TITLE => p_org_contact_rec.title,
1009 X_JOB_TITLE => p_org_contact_rec.job_title,
1010 X_DECISION_MAKER_FLAG => p_org_contact_rec.decision_maker_flag,
1011 X_JOB_TITLE_CODE => p_org_contact_rec.job_title_code,
1012 X_REFERENCE_USE_FLAG => p_org_contact_rec.reference_use_flag,
1013 X_RANK => p_org_contact_rec.rank,
1014 X_ORIG_SYSTEM_REFERENCE => p_org_contact_rec.orig_system_reference,
1015 X_ATTRIBUTE_CATEGORY => p_org_contact_rec.attribute_category,
1016 X_ATTRIBUTE1 => p_org_contact_rec.attribute1,
1017 X_ATTRIBUTE2 => p_org_contact_rec.attribute2,
1018 X_ATTRIBUTE3 => p_org_contact_rec.attribute3,
1019 X_ATTRIBUTE4 => p_org_contact_rec.attribute4,
1020 X_ATTRIBUTE5 => p_org_contact_rec.attribute5,
1021 X_ATTRIBUTE6 => p_org_contact_rec.attribute6,
1022 X_ATTRIBUTE7 => p_org_contact_rec.attribute7,
1023 X_ATTRIBUTE8 => p_org_contact_rec.attribute8,
1024 X_ATTRIBUTE9 => p_org_contact_rec.attribute9,
1025 X_ATTRIBUTE10 => p_org_contact_rec.attribute10,
1026 X_ATTRIBUTE11 => p_org_contact_rec.attribute11,
1027 X_ATTRIBUTE12 => p_org_contact_rec.attribute12,
1028 X_ATTRIBUTE13 => p_org_contact_rec.attribute13,
1029 X_ATTRIBUTE14 => p_org_contact_rec.attribute14,
1030 X_ATTRIBUTE15 => p_org_contact_rec.attribute15,
1031 X_ATTRIBUTE16 => p_org_contact_rec.attribute16,
1032 X_ATTRIBUTE17 => p_org_contact_rec.attribute17,
1033 X_ATTRIBUTE18 => p_org_contact_rec.attribute18,
1034 X_ATTRIBUTE19 => p_org_contact_rec.attribute19,
1035 X_ATTRIBUTE20 => p_org_contact_rec.attribute20,
1036 X_ATTRIBUTE21 => p_org_contact_rec.attribute21,
1037 X_ATTRIBUTE22 => p_org_contact_rec.attribute22,
1038 X_ATTRIBUTE23 => p_org_contact_rec.attribute23,
1039 X_ATTRIBUTE24 => p_org_contact_rec.attribute24,
1040 X_PARTY_SITE_ID => p_org_contact_rec.party_site_id,
1041 X_OBJECT_VERSION_NUMBER => 1,
1042 X_CREATED_BY_MODULE => p_org_contact_rec.created_by_module,
1043 X_APPLICATION_ID => p_org_contact_rec.application_id,
1044 X_STATUS => p_org_contact_rec.party_rel_rec.status
1045 );
1046
1047 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'Y')) THEN
1048 -- Invoke business event system.
1049 HZ_BUSINESS_EVENT_V2PVT.create_org_contact_event (p_org_contact_rec);
1050 END IF;
1051
1052 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'BO_EVENTS_ENABLED')) THEN
1053 IF(p_relationship_rec.subject_type = 'PERSON' AND p_relationship_rec.object_type = 'ORGANIZATION') THEN
1054 HZ_POPULATE_BOT_PKG.pop_hz_org_contacts(
1055 p_operation => 'I',
1056 p_org_contact_id => p_org_contact_rec.org_contact_id);
1057 END IF;
1058 END IF;
1059
1060 -- Call to indicate Org Contact creation to DQM
1061 HZ_DQM_SYNC.sync_contact(p_org_contact_rec.org_contact_id, 'C');
1062
1063 END IF;
1064 END IF;
1065
1066
1067 --
1068 -- added for R12 party usage project
1069 --
1070 IF (p_relationship_rec.subject_type = 'PERSON' AND
1071 p_relationship_rec.object_type IN ('PERSON', 'ORGANIZATION') OR
1072 p_relationship_rec.object_type = 'PERSON' AND
1073 p_relationship_rec.subject_type IN ('PERSON', 'ORGANIZATION')) AND
1074 p_relationship_rec.subject_table_name = 'HZ_PARTIES' AND
1075 p_relationship_rec.object_table_name = 'HZ_PARTIES'
1076 THEN
1077 IF (p_contact_party_id IS NOT NULL AND
1078 p_contact_party_id <> FND_API.G_MISS_NUM AND
1079 (p_contact_party_usage_code IS NULL OR
1080 p_contact_party_usage_code = FND_API.G_MISS_CHAR)) OR
1081 (p_contact_party_usage_code IS NOT NULL AND
1082 p_contact_party_usage_code <> FND_API.G_MISS_CHAR AND
1083 (p_contact_party_id IS NULL OR
1084 p_contact_party_id = FND_API.G_MISS_NUM))
1085 THEN
1086 fnd_message.set_name('AR', 'HZ_PU_REL_MISSING_COLUMN');
1087 fnd_msg_pub.add;
1088 RAISE FND_API.G_EXC_ERROR;
1089 ELSIF p_contact_party_id IS NOT NULL AND
1090 (p_contact_party_id <> p_relationship_rec.subject_id AND
1091 p_contact_party_id <> p_relationship_rec.object_id OR
1092 p_contact_party_id = p_relationship_rec.subject_id AND
1093 p_relationship_rec.subject_type <> 'PERSON' OR
1094 p_contact_party_id = p_relationship_rec.object_id AND
1095 p_relationship_rec.object_type <> 'PERSON')
1096 THEN
1097 fnd_message.set_name('AR', 'HZ_PU_REL_INVALID_CONTACT_ID');
1098 fnd_msg_pub.add;
1099 RAISE FND_API.G_EXC_ERROR;
1100 END IF;
1101
1102 l_party_id_tbl.extend(1);
1103 l_party_usage_code_tbl.extend(1);
1104
1105 IF p_contact_party_usage_code IS NOT NULL THEN
1106 l_party_id_tbl(1) := p_contact_party_id;
1107 l_party_usage_code_tbl(1) := p_contact_party_usage_code;
1108 l_party_usg_assignment_rec.created_by_module := p_relationship_rec.created_by_module;
1109 l_party_usage_validation_level := HZ_PARTY_USG_ASSIGNMENT_PVT.G_VALID_LEVEL_MEDIUM;
1110 ELSE
1111 l_party_usage_code_tbl(1) := 'ORG_CONTACT';
1112 l_party_usg_assignment_rec.created_by_module := 'TCA_V2_API';
1113 l_party_usage_validation_level := HZ_PARTY_USG_ASSIGNMENT_PVT.G_VALID_LEVEL_LOW;
1114
1115 IF p_relationship_rec.subject_type = 'PERSON' AND
1116 p_relationship_rec.object_type = 'ORGANIZATION'
1117 THEN
1118 l_party_id_tbl(1) := p_relationship_rec.subject_id;
1119 ELSIF p_relationship_rec.subject_type = 'ORGANIZATION' AND
1120 p_relationship_rec.object_type = 'PERSON'
1121 THEN
1122 l_party_id_tbl(1) := p_relationship_rec.object_id;
1123 ELSIF p_relationship_rec.subject_type = 'PERSON' AND
1124 p_relationship_rec.object_type = 'PERSON'
1125 THEN
1126 l_party_id_tbl(1) := p_relationship_rec.subject_id;
1127 l_party_usage_code_tbl(1) := 'RELATED_PERSON';
1128
1129 IF p_relationship_rec.subject_id <> p_relationship_rec.object_id THEN
1130 l_party_id_tbl.extend(1);
1131 l_party_usage_code_tbl.extend(1);
1132 l_party_id_tbl(2) := p_relationship_rec.object_id;
1133 l_party_usage_code_tbl(2) := 'RELATED_PERSON';
1134 END IF;
1135 END IF;
1136 END IF;
1137
1138 l_party_usg_assignment_rec.owner_table_name := 'HZ_RELATIONSHIPS';
1139 l_party_usg_assignment_rec.owner_table_id := p_relationship_rec.relationship_id;
1140 l_party_usg_assignment_rec.effective_start_date := p_relationship_rec.start_date;
1141 l_party_usg_assignment_rec.effective_end_date := p_relationship_rec.end_date;
1142
1143 IF p_relationship_rec.status = 'I' THEN
1144 IF p_relationship_rec.start_date IS NULL OR
1145 p_relationship_rec.start_date = fnd_api.g_miss_date OR
1146 trunc(p_relationship_rec.start_date) > trunc(sysdate)
1147 THEN
1148 l_party_usg_assignment_rec.effective_start_date := trunc(sysdate);
1149 END IF;
1150
1151 IF p_relationship_rec.end_date IS NULL OR
1152 p_relationship_rec.end_date = fnd_api.g_miss_date OR
1153 trunc(p_relationship_rec.end_date) > trunc(sysdate)
1154 THEN
1155 l_party_usg_assignment_rec.effective_end_date := trunc(sysdate);
1156 END IF;
1157 END IF;
1158
1159 FOR i IN 1..l_party_usage_code_tbl.count LOOP
1160 l_party_usg_assignment_rec.party_id := l_party_id_tbl(i);
1161 l_party_usg_assignment_rec.party_usage_code := l_party_usage_code_tbl(i);
1162
1163 HZ_PARTY_USG_ASSIGNMENT_PVT.assign_party_usage (
1164 p_validation_level => l_party_usage_validation_level,
1165 p_party_usg_assignment_rec => l_party_usg_assignment_rec,
1166 x_return_status => x_return_status,
1167 x_msg_count => l_msg_count,
1168 x_msg_data => l_msg_data
1169 );
1170
1171 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1172 RAISE fnd_api.g_exc_error;
1173 END IF;
1174 END LOOP;
1175 END IF;
1176
1177 -- Debug info.
1178 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1179 hz_utility_v2pub.debug(p_message=>'do_create_rel (-)',
1180 p_prefix=>l_debug_prefix,
1181 p_msg_level=>fnd_log.level_procedure);
1182 END IF;
1183
1184 END do_create_rel;
1185
1186 /*===========================================================================+
1187 | PROCEDURE
1188 | do_update_rel
1189 |
1190 | DESCRIPTION
1191 | Updates relationship and party for party_relationship.
1192 |
1193 | SCOPE - PRIVATE
1194 |
1195 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1196 |
1197 | ARGUMENTS : IN:
1198 | OUT:
1199 | IN/OUT:
1200 | p_party_rel_rec
1201 |
1202 | RETURNS : NONE
1203 |
1204 | NOTES
1205 |
1206 | MODIFICATION HISTORY
1207 | 09-DEC-2003 Rajib Ranjan Borah o Bug 3274236.Start date and end date were not
1208 | getting updated if the user passed FND_API.G_MISS_CHAR.
1209 | o Modified some previous code to remove redundancy.
1210 | 19-FEB-2004 Rajib Ranjan Borah o Bug 3306941.The meaning should be passed as token
1211 | for the error message HZ_API_MULTIPLE_PARENT instead
1212 | of the relationsihp_type.
1213 | 04-JAN-2005 Rajib Ranjan Borah o SSM SST Integration and Extension.
1214 | For non-profile entities, the concept of select
1215 | /de-select data-sources is obsoleted.
1216 +===========================================================================*/
1217
1218 PROCEDURE do_update_rel(
1219 p_relationship_rec IN OUT NOCOPY RELATIONSHIP_REC_TYPE,
1220 p_old_relationship_rec IN RELATIONSHIP_REC_TYPE,
1221 p_object_version_number IN OUT NOCOPY NUMBER,
1222 p_party_object_version_number IN OUT NOCOPY NUMBER,
1223 x_return_status IN OUT NOCOPY VARCHAR2
1224 ) IS
1225
1226 l_object_version_number NUMBER;
1227 l_party_object_version_number NUMBER;
1228 l_rowid ROWID;
1229 l_party_id NUMBER := p_relationship_rec.party_rec.party_id;
1230 l_profile_id NUMBER;
1231 l_rel_rec RELATIONSHIP_REC_TYPE ;
1232 l_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;
1233 l_group_rec HZ_PARTY_V2PUB.GROUP_REC_TYPE;
1234 l_person_rec HZ_PARTY_V2PUB.PERSON_REC_TYPE;
1235 l_party_rel_rec RELATIONSHIP_REC_TYPE := p_relationship_rec;
1236 l_old_rel_rec RELATIONSHIP_REC_TYPE;
1237 l_msg_count NUMBER;
1238 l_msg_data VARCHAR2(2000);
1239 l_content_source_type VARCHAR2(30);
1240 l_subject_table_name VARCHAR2(30);
1241 l_object_table_name VARCHAR2(30);
1242 l_debug_prefix VARCHAR2(30) := '';
1243 l_subject_id NUMBER;
1244 l_object_id NUMBER;
1245 l_relationship_code VARCHAR2(30);
1246 l_start_date DATE;
1247 l_end_date DATE;
1248 l_relationship_type VARCHAR2(30);
1249 l_hierarchical_flag VARCHAR2(1) := 'N';
1250 l_direction_code VARCHAR2(30);
1251 l_subject_type VARCHAR2(30);
1252 l_object_type VARCHAR2(30);
1253 l_status VARCHAR2(1);
1254 l_hierarchy_rec HZ_HIERARCHY_PUB.HIERARCHY_NODE_REC_TYPE;
1255 l_allow_circular_relationships VARCHAR2(1);
1256 l_mult_parent_allowed VARCHAR2(1);
1257 l_parent_id NUMBER;
1258 l_parent_object_type VARCHAR2(30);
1259 l_parent_table_name VARCHAR2(30);
1260 l_child_id NUMBER;
1261 l_child_object_type VARCHAR2(30);
1262 l_child_table_name VARCHAR2(30);
1263 l_temp_parent_id NUMBER;
1264 l_temp_parent_table_name VARCHAR2(30);
1265 l_temp_parent_object_type VARCHAR2(30);
1266 l_parent_flag VARCHAR2(1);
1267 l_count NUMBER;
1268 l_new_start_date DATE;
1269 l_new_end_date DATE;
1270 e_loop EXCEPTION;
1271 pragma exception_init(e_loop, -01436);
1272 --Bug 3306941.
1273 l_meaning VARCHAR2(80);
1274
1275 -- Bug 2197181: added for mix-n-match project.
1276 db_actual_content_source hz_relationships.actual_content_source%TYPE;
1277
1278 l_party_usg_assignment_rec HZ_PARTY_USG_ASSIGNMENT_PVT.party_usg_assignment_rec_type;
1279
1280 -- Bug 4693719 : Added for local assignment
1281 l_acs hz_relationships.actual_content_source%TYPE;
1282
1283 -- Bug 4873016 : Added to select directional_flag
1284 l_directional_flag hz_relationships.directional_flag%TYPE;
1285
1286 BEGIN
1287
1288 -- Debug info.
1289 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1290 hz_utility_v2pub.debug(p_message=>'do_update_rel (+)',
1291 p_prefix=>l_debug_prefix,
1292 p_msg_level=>fnd_log.level_procedure);
1293 END IF;
1294
1295 /* Bug 4873016 : Select the record from hz_relationships based on
1296 * the values passed by the user to this API. If there is no record
1297 * found, it means that user is trying to update any of the non-updateable
1298 * columns. In this case, we continue to selecte the forward relationship
1299 * record and error will be raised from the validate_relationship call.
1300 * If a record is found, user the selected directional_flag to update
1301 * the reciprocal record
1302 */
1303 BEGIN
1304 SELECT OBJECT_VERSION_NUMBER,
1305 ROWID,
1306 CONTENT_SOURCE_TYPE,
1307 SUBJECT_TABLE_NAME,
1308 OBJECT_TABLE_NAME,
1309 SUBJECT_ID,
1310 OBJECT_ID,
1311 SUBJECT_TYPE,
1312 OBJECT_TYPE,
1313 RELATIONSHIP_TYPE,
1314 RELATIONSHIP_CODE,
1315 START_DATE,
1316 END_DATE,
1317 DIRECTION_CODE,
1318 STATUS,
1319 actual_content_source,
1320 DIRECTIONAL_FLAG
1321 INTO l_object_version_number,
1322 l_rowid,
1323 l_content_source_type,
1324 l_subject_table_name,
1325 l_object_table_name,
1326 l_subject_id,
1327 l_object_id,
1328 l_subject_type,
1329 l_object_type,
1330 l_relationship_type,
1331 l_relationship_code,
1332 l_start_date,
1333 l_end_date,
1334 l_direction_code,
1335 l_status,
1336 db_actual_content_source,
1337 l_directional_flag
1338 FROM HZ_RELATIONSHIPS
1339 WHERE RELATIONSHIP_ID = p_relationship_rec.relationship_id
1340 AND SUBJECT_TABLE_NAME = nvl(p_relationship_rec.SUBJECT_TABLE_NAME, SUBJECT_TABLE_NAME)
1341 AND OBJECT_TABLE_NAME = nvl(p_relationship_rec.OBJECT_TABLE_NAME, OBJECT_TABLE_NAME)
1342 AND SUBJECT_ID = nvl(p_relationship_rec.SUBJECT_ID, SUBJECT_ID)
1343 AND OBJECT_ID = nvl(p_relationship_rec.OBJECT_ID, OBJECT_ID)
1344 AND SUBJECT_TYPE = nvl(p_relationship_rec.SUBJECT_TYPE, SUBJECT_TYPE)
1345 AND OBJECT_TYPE = nvl(p_relationship_rec.OBJECT_TYPE, OBJECT_TYPE)
1346 AND RELATIONSHIP_TYPE = nvl(p_relationship_rec.RELATIONSHIP_TYPE, RELATIONSHIP_TYPE)
1347 AND RELATIONSHIP_CODE = nvl(p_relationship_rec.RELATIONSHIP_CODE, RELATIONSHIP_CODE)
1348 AND ROWNUM = 1
1349 FOR UPDATE OF RELATIONSHIP_ID NOWAIT;
1350
1351 IF NOT
1352 (
1353 ( p_object_version_number IS NULL AND l_object_version_number IS NULL )
1354 OR
1355 ( p_object_version_number IS NOT NULL AND
1356 l_object_version_number IS NOT NULL AND
1357 p_object_version_number = l_object_version_number
1358 )
1359 )
1360 THEN
1361 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_RECORD_CHANGED' );
1362 FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_relationships' );
1363 FND_MSG_PUB.ADD;
1364 RAISE FND_API.G_EXC_ERROR;
1365 END IF;
1366
1367 p_object_version_number := NVL( l_object_version_number, 1 ) + 1;
1368
1369 EXCEPTION
1370 WHEN NO_DATA_FOUND THEN
1371
1372 -- Check whether record has been updated by another user. If not, lock it.
1373
1374 -- Bug 2197181: selecting actual_content_source for mix-n-match project.
1375
1376 BEGIN
1377 SELECT OBJECT_VERSION_NUMBER,
1378 ROWID,
1379 CONTENT_SOURCE_TYPE,
1380 SUBJECT_TABLE_NAME,
1381 OBJECT_TABLE_NAME,
1382 SUBJECT_ID,
1383 OBJECT_ID,
1384 SUBJECT_TYPE,
1385 OBJECT_TYPE,
1386 RELATIONSHIP_TYPE,
1387 RELATIONSHIP_CODE,
1388 START_DATE,
1389 END_DATE,
1390 DIRECTION_CODE,
1391 STATUS,
1392 actual_content_source,
1393 -- Bug 4873016 : select DIRECTIONAL_FLAG also
1394 DIRECTIONAL_FLAG
1395 INTO l_object_version_number,
1396 l_rowid,
1397 l_content_source_type,
1398 l_subject_table_name,
1399 l_object_table_name,
1400 l_subject_id,
1401 l_object_id,
1402 l_subject_type,
1403 l_object_type,
1404 l_relationship_type,
1405 l_relationship_code,
1406 l_start_date,
1407 l_end_date,
1408 l_direction_code,
1409 l_status,
1410 db_actual_content_source,
1411 l_directional_flag
1412 FROM HZ_RELATIONSHIPS
1413 WHERE RELATIONSHIP_ID = p_relationship_rec.relationship_id
1414 AND DIRECTIONAL_FLAG = 'F'
1415 FOR UPDATE OF RELATIONSHIP_ID NOWAIT;
1416
1417 IF NOT
1418 (
1419 ( p_object_version_number IS NULL AND l_object_version_number IS NULL )
1420 OR
1421 ( p_object_version_number IS NOT NULL AND
1422 l_object_version_number IS NOT NULL AND
1423 p_object_version_number = l_object_version_number
1424 )
1425 )
1426 THEN
1427 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_RECORD_CHANGED' );
1428 FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_relationships' );
1429 FND_MSG_PUB.ADD;
1430 RAISE FND_API.G_EXC_ERROR;
1431 END IF;
1432
1433 p_object_version_number := NVL( l_object_version_number, 1 ) + 1;
1434
1435 EXCEPTION
1436 WHEN NO_DATA_FOUND THEN
1437 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_RECORD' );
1438 FND_MESSAGE.SET_TOKEN( 'RECORD', 'relationship' );
1439 FND_MESSAGE.SET_TOKEN( 'VALUE', NVL(TO_CHAR( p_relationship_rec.relationship_id ),'null'));
1440 FND_MSG_PUB.ADD;
1441 RAISE FND_API.G_EXC_ERROR;
1442 END;
1443 END;
1444
1445 -- Debug info.
1446 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1447 hz_utility_v2pub.debug(p_message=>'Done with locking',
1448 p_prefix =>l_debug_prefix,
1449 p_msg_level=>fnd_log.level_statement);
1450 END IF;
1451
1452 -- get the relationship type for its attributes
1453 SELECT HIERARCHICAL_FLAG,
1454 ALLOW_CIRCULAR_RELATIONSHIPS,
1455 NVL(MULTIPLE_PARENT_ALLOWED, 'N') MULTIPLE_PARENT_ALLOWED
1456 INTO l_hierarchical_flag,
1457 l_allow_circular_relationships,
1458 l_mult_parent_allowed
1459 FROM HZ_RELATIONSHIP_TYPES
1460 WHERE RELATIONSHIP_TYPE = l_relationship_type
1461 AND ROWNUM = 1;
1462
1463 -- decide who is parent and who is child in this relationship.
1464 -- if relationship type record is 'P' type, then subject is parent, else object
1465 IF l_direction_code = 'P' THEN
1466 l_parent_id := l_subject_id;
1467 l_parent_table_name := l_subject_table_name;
1468 l_parent_object_type := l_subject_type;
1469 l_child_id := l_object_id;
1470 l_child_table_name := l_object_table_name;
1471 l_child_object_type := l_object_type;
1472 ELSIF l_direction_code = 'C' THEN
1473 l_parent_id := l_object_id;
1474 l_parent_table_name := l_object_table_name;
1475 l_parent_object_type := l_object_type;
1476 l_child_id := l_subject_id;
1477 l_child_table_name := l_subject_table_name;
1478 l_child_object_type := l_subject_type;
1479 END IF;
1480
1481 IF p_relationship_rec.start_date IS NOT NULL THEN
1482 IF p_relationship_rec.start_date = FND_API.G_MISS_DATE THEN
1483 l_new_start_date := sysdate;
1484 ELSE
1485 l_new_start_date := p_relationship_rec.start_date;
1486 END IF;
1487 ELSE
1488 l_new_start_date := l_start_date;
1489 END IF;
1490
1491 IF p_relationship_rec.end_date IS NOT NULL THEN
1492 IF p_relationship_rec.end_date = FND_API.G_MISS_DATE THEN
1493 l_new_end_date := to_date('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS');
1494 ELSE
1495 l_new_end_date := p_relationship_rec.end_date;
1496 END IF;
1497 ELSE
1498 l_new_end_date := l_end_date;
1499 END IF;
1500
1501
1502 -- Bug 2797506 begin. Circularity check logic is in a new procedure.
1503
1504 IF l_hierarchical_flag = 'Y' OR l_allow_circular_relationships = 'N'
1505 THEN
1506 -- check for circularity. This procedure will raise exception if found.
1507 do_circularity_check(
1508 p_relationship_id => p_relationship_rec.relationship_id,
1509 p_relationship_type => l_relationship_type,
1510 p_start_date => l_new_start_date,
1511 p_end_date => l_new_end_date,
1512 p_subject_id => l_child_id,
1513 p_object_id => l_parent_id,
1514 p_object_type => l_parent_object_type,
1515 p_object_table_name => l_parent_table_name
1516 );
1517 END IF;
1518
1519 -- Bug 2797506 end.
1520
1521
1522 -- check for multiple parent
1523 IF l_hierarchical_flag = 'Y' AND l_mult_parent_allowed = 'N'
1524 AND
1525 (l_start_date <> NVL(p_relationship_rec.start_date, l_start_date) OR
1526 l_end_date <> NVL(p_relationship_rec.end_date, l_end_date)
1527 )
1528 THEN
1529 -- code for multiple parent check
1530 BEGIN
1531 SELECT 1 INTO l_count
1532 FROM HZ_RELATIONSHIPS
1533 WHERE OBJECT_ID = l_child_id
1534 AND OBJECT_TABLE_NAME = l_child_table_name
1535 AND OBJECT_TYPE = l_child_object_type
1536 AND RELATIONSHIP_TYPE = l_relationship_type
1537 AND DIRECTION_CODE = 'P'
1538 AND RELATIONSHIP_ID <> p_relationship_rec.relationship_id
1539 -- Bug 3817438 : Added condition to check only active relationships
1540 AND STATUS= 'A'
1541 AND (START_DATE BETWEEN NVL(p_relationship_rec.start_date, l_start_date)
1542 AND NVL(p_relationship_rec.end_date, l_end_date)
1543 OR
1544 END_DATE BETWEEN NVL(p_relationship_rec.start_date, l_start_date)
1545 AND NVL(p_relationship_rec.end_date, l_end_date)
1546 OR
1547 NVL(p_relationship_rec.start_date, l_start_date) BETWEEN START_DATE AND END_DATE
1548 OR
1549 NVL(p_relationship_rec.end_date, l_end_date) BETWEEN START_DATE AND END_DATE
1550 )
1551 AND ROWNUM = 1;
1552
1553 -- there is already a parent, so raise error
1554 --Bug 3306941.Display meaning instead of relationship_type.
1555 SELECT MEANING
1556 INTO l_meaning
1557 -- Bug 3664939 : Use fnd_lookup_values_vl to get lookup meaning
1558 FROM FND_LOOKUP_VALUES_VL
1559 WHERE LOOKUP_TYPE='HZ_RELATIONSHIP_TYPE'
1560 AND LOOKUP_CODE = l_relationship_type
1561 AND VIEW_APPLICATION_ID = 222
1562 AND ROWNUM = 1;
1563
1564 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MULTIPLE_PARENT');
1565 FND_MESSAGE.SET_TOKEN('RELTYPE', l_meaning);
1566 FND_MSG_PUB.ADD;
1567 RAISE FND_API.G_EXC_ERROR;
1568
1569 EXCEPTION
1570 WHEN NO_DATA_FOUND THEN
1571 -- no parent found, proceed
1572 NULL;
1573 END;
1574 END IF;
1575
1576 -- Call for validations.
1577 --Bug 2133648
1578 -- Bug 4873016 : user values passed to API if not NULL
1579 p_relationship_rec.subject_id := nvl(p_relationship_rec.SUBJECT_ID, l_subject_id);
1580 p_relationship_rec.object_id := nvl(p_relationship_rec.OBJECT_ID, l_object_id);
1581 p_relationship_rec.relationship_code := nvl(p_relationship_rec.RELATIONSHIP_CODE, l_relationship_code);
1582 --2226526,passed object_type
1583 p_relationship_rec.object_type := nvl(p_relationship_rec.OBJECT_TYPE, l_object_type);
1584 -- Bug 3274236 l_rel_rec := p_relationship_rec;
1585
1586
1587 IF p_relationship_rec.start_date IS NULL OR
1588 p_relationship_rec.start_date = FND_API.G_MISS_DATE
1589 THEN
1590 -- Bug 3274236 p_relationship_rec.start_date := l_start_date;
1591 p_relationship_rec.start_date := l_new_start_date;
1592 END IF;
1593
1594 IF p_relationship_rec.end_date IS NULL OR
1595 p_relationship_rec.end_date = FND_API.G_MISS_DATE
1596 THEN
1597 -- Bug 3274236 p_relationship_rec.end_date := l_end_date;
1598 p_relationship_rec.end_date := l_new_end_date;
1599 END IF;
1600
1601 -- Bug 3274236
1602 l_rel_rec := p_relationship_rec ;
1603
1604 HZ_REGISTRY_VALIDATE_V2PUB.validate_relationship(
1605 'U',
1606 p_relationship_rec,
1607 l_rowid,
1608 x_return_status);
1609
1610 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1611 RAISE FND_API.G_EXC_ERROR;
1612 END IF;
1613
1614 --start_date of hz_party_relationships has been changed
1615 --to be updateable. We need to pass in the new start_date when we
1616 --denormalize flags.
1617 /* Bug 3274236.This code is redundant as l_rel_rec is now assigned after
1618 making the changes to p_relationship_rec.
1619
1620 IF p_relationship_rec.start_date IS NULL OR
1621 p_relationship_rec.start_date <> FND_API.G_MISS_DATE
1622 THEN
1623 l_rel_rec.start_date := p_relationship_rec.start_date;
1624 END IF;
1625
1626 IF p_relationship_rec.end_date IS NULL OR
1627 p_relationship_rec.end_date <> FND_API.G_MISS_DATE
1628 THEN
1629 l_rel_rec.end_date := p_relationship_rec.end_date;
1630 END IF;
1631 */
1632 -- Denormalization will be done only if content_source_type
1633 -- is 'USER_ENTERED' and both subject_table_name and
1634 -- object_table_name are 'HZ_PARTIES'
1635
1636 -- Bug 2197181: added for mix-n-match project. Denormalize
1637 -- the three flags when the data source is visible (i.e.
1638 -- selected).
1639
1640 -- SSM SST Integration and Extension
1641 -- For non-profile entities, the concept of select/de-select data-sources is obsoleted.
1642 -- There is no need to check if the data-source is selected.
1643
1644 IF l_relationship_code IN
1645 ('COMPETITOR_OF', 'REFERENCE_FOR', 'PARTNER_OF') AND
1646 /* g_rel_is_datasource_selected = 'Y' AND */
1647 /*
1648 l_content_source_type = 'USER_ENTERED'
1649 AND
1650 */
1651 l_subject_table_name = 'HZ_PARTIES'
1652 AND
1653 l_object_table_name = 'HZ_PARTIES'
1654 THEN
1655
1656 -- Debug info.
1657 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1658 hz_utility_v2pub.debug(p_message=>'denormalizing to hz_parties',
1659 p_prefix =>l_debug_prefix,
1660 p_msg_level=>fnd_log.level_statement);
1661 END IF;
1662
1663 do_update_party_flags(l_rel_rec,
1664 l_rel_rec.subject_id);
1665 END IF;
1666
1667 -- Debug info.
1668 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1669 hz_utility_v2pub.debug(p_message=>'HZ_RELATIONSHIPS_PKG.Update_Row-1 (+) ',
1670 p_prefix=>l_debug_prefix,
1671 p_msg_level=>fnd_log.level_procedure);
1672 END IF;
1673
1674 -- Bug 4693719 : pass NULL if the secure data is not updated
1675 IF HZ_UTILITY_V2PUB.G_UPDATE_ACS = 'Y' THEN
1676 l_acs := nvl(p_relationship_rec.actual_content_source, 'USER_ENTERED');
1677 ELSE
1678 l_acs := NULL;
1679 END IF;
1680
1681
1682 --Call to table-handler.
1683 HZ_RELATIONSHIPS_PKG.Update_Row (
1684 X_Rowid => l_rowid,
1685 X_RELATIONSHIP_ID => p_relationship_rec.relationship_id,
1686 -- Bug 4873016 : pass NULL for non updateable columns
1687 /*
1688 X_SUBJECT_ID => p_relationship_rec.subject_id,
1689 X_SUBJECT_TYPE => p_relationship_rec.subject_type,
1690 X_SUBJECT_TABLE_NAME => p_relationship_rec.subject_table_name,
1691 X_OBJECT_ID => p_relationship_rec.object_id,
1692 X_OBJECT_TYPE => p_relationship_rec.object_type,
1693 X_OBJECT_TABLE_NAME => p_relationship_rec.object_table_name,
1694 X_PARTY_ID => NULL,
1695 X_RELATIONSHIP_CODE => p_relationship_rec.relationship_code,
1696 */
1697 X_SUBJECT_ID => NULL,
1698 X_SUBJECT_TYPE => NULL,
1699 X_SUBJECT_TABLE_NAME => NULL,
1700 X_OBJECT_ID => NULL,
1701 X_OBJECT_TYPE => NULL,
1702 X_OBJECT_TABLE_NAME => NULL,
1703 X_PARTY_ID => NULL,
1704 X_RELATIONSHIP_CODE => NULL,
1705 X_DIRECTIONAL_FLAG => NULL,
1706 X_COMMENTS => p_relationship_rec.comments,
1707 X_START_DATE => p_relationship_rec.start_date,
1708 X_END_DATE => p_relationship_rec.end_date,
1709 X_STATUS => p_relationship_rec.status,
1710 X_ATTRIBUTE_CATEGORY => p_relationship_rec.attribute_category,
1711 X_ATTRIBUTE1 => p_relationship_rec.attribute1,
1712 X_ATTRIBUTE2 => p_relationship_rec.attribute2,
1713 X_ATTRIBUTE3 => p_relationship_rec.attribute3,
1714 X_ATTRIBUTE4 => p_relationship_rec.attribute4,
1715 X_ATTRIBUTE5 => p_relationship_rec.attribute5,
1716 X_ATTRIBUTE6 => p_relationship_rec.attribute6,
1717 X_ATTRIBUTE7 => p_relationship_rec.attribute7,
1718 X_ATTRIBUTE8 => p_relationship_rec.attribute8,
1719 X_ATTRIBUTE9 => p_relationship_rec.attribute9,
1720 X_ATTRIBUTE10 => p_relationship_rec.attribute10,
1721 X_ATTRIBUTE11 => p_relationship_rec.attribute11,
1722 X_ATTRIBUTE12 => p_relationship_rec.attribute12,
1723 X_ATTRIBUTE13 => p_relationship_rec.attribute13,
1724 X_ATTRIBUTE14 => p_relationship_rec.attribute14,
1725 X_ATTRIBUTE15 => p_relationship_rec.attribute15,
1726 X_ATTRIBUTE16 => p_relationship_rec.attribute16,
1727 X_ATTRIBUTE17 => p_relationship_rec.attribute17,
1728 X_ATTRIBUTE18 => p_relationship_rec.attribute18,
1729 X_ATTRIBUTE19 => p_relationship_rec.attribute19,
1730 X_ATTRIBUTE20 => p_relationship_rec.attribute20,
1731 -- Bug 2197181 : content_source_type is obsolete and it is non-updateable.
1732 X_CONTENT_SOURCE_TYPE => NULL,
1733 X_RELATIONSHIP_TYPE => p_relationship_rec.relationship_type,
1734 X_OBJECT_VERSION_NUMBER => p_object_version_number,
1735 X_CREATED_BY_MODULE => p_relationship_rec.created_by_module,
1736 X_APPLICATION_ID => p_relationship_rec.application_id,
1737 X_ADDITIONAL_INFORMATION1 => p_relationship_rec.additional_information1,
1738 X_ADDITIONAL_INFORMATION2 => p_relationship_rec.additional_information2,
1739 X_ADDITIONAL_INFORMATION3 => p_relationship_rec.additional_information3,
1740 X_ADDITIONAL_INFORMATION4 => p_relationship_rec.additional_information4,
1741 X_ADDITIONAL_INFORMATION5 => p_relationship_rec.additional_information5,
1742 X_ADDITIONAL_INFORMATION6 => p_relationship_rec.additional_information6,
1743 X_ADDITIONAL_INFORMATION7 => p_relationship_rec.additional_information7,
1744 X_ADDITIONAL_INFORMATION8 => p_relationship_rec.additional_information8,
1745 X_ADDITIONAL_INFORMATION9 => p_relationship_rec.additional_information9,
1746 X_ADDITIONAL_INFORMATION10 => p_relationship_rec.additional_information10,
1747 X_ADDITIONAL_INFORMATION11 => p_relationship_rec.additional_information11,
1748 X_ADDITIONAL_INFORMATION12 => p_relationship_rec.additional_information12,
1749 X_ADDITIONAL_INFORMATION13 => p_relationship_rec.additional_information13,
1750 X_ADDITIONAL_INFORMATION14 => p_relationship_rec.additional_information14,
1751 X_ADDITIONAL_INFORMATION15 => p_relationship_rec.additional_information15,
1752 X_ADDITIONAL_INFORMATION16 => p_relationship_rec.additional_information16,
1753 X_ADDITIONAL_INFORMATION17 => p_relationship_rec.additional_information17,
1754 X_ADDITIONAL_INFORMATION18 => p_relationship_rec.additional_information18,
1755 X_ADDITIONAL_INFORMATION19 => p_relationship_rec.additional_information19,
1756 X_ADDITIONAL_INFORMATION20 => p_relationship_rec.additional_information20,
1757 X_ADDITIONAL_INFORMATION21 => p_relationship_rec.additional_information21,
1758 X_ADDITIONAL_INFORMATION22 => p_relationship_rec.additional_information22,
1759 X_ADDITIONAL_INFORMATION23 => p_relationship_rec.additional_information23,
1760 X_ADDITIONAL_INFORMATION24 => p_relationship_rec.additional_information24,
1761 X_ADDITIONAL_INFORMATION25 => p_relationship_rec.additional_information25,
1762 X_ADDITIONAL_INFORMATION26 => p_relationship_rec.additional_information26,
1763 X_ADDITIONAL_INFORMATION27 => p_relationship_rec.additional_information27,
1764 X_ADDITIONAL_INFORMATION28 => p_relationship_rec.additional_information28,
1765 X_ADDITIONAL_INFORMATION29 => p_relationship_rec.additional_information29,
1766 X_ADDITIONAL_INFORMATION30 => p_relationship_rec.additional_information30,
1767 X_DIRECTION_CODE => NULL,
1768 X_PERCENTAGE_OWNERSHIP => p_relationship_rec.percentage_ownership,
1769 -- Bug 4693719 : Pass correct value for ACS
1770 X_ACTUAL_CONTENT_SOURCE => l_acs
1771 );
1772
1773 -- Debug info.
1774 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1775 hz_utility_v2pub.debug(p_message=>'HZ_RELATIONSHIPS_PKG.Update_Row-1 (-) ',
1776 p_prefix=>l_debug_prefix,
1777 p_msg_level=>fnd_log.level_procedure);
1778 END IF;
1779
1780 -- get the reciprocal record information
1781 SELECT ROWID
1782 INTO l_rowid
1783 FROM HZ_RELATIONSHIPS
1784 WHERE RELATIONSHIP_ID = p_relationship_rec.relationship_id
1785 /* Bug 4873016 : query the reciprocal record based on previously
1786 * selected directional flag. If it was 'F' select 'B'
1787 * if it was 'B' select 'F'
1788 */
1789 AND DIRECTIONAL_FLAG = decode(l_directional_flag, 'F', 'B','B', 'F');
1790
1791 -- Debug info.
1792 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1793 hz_utility_v2pub.debug(p_message=>'HZ_RELATIONSHIPS_PKG.Update_Row-2 (+) ',
1794 p_prefix=>l_debug_prefix,
1795 p_msg_level=>fnd_log.level_procedure);
1796 END IF;
1797
1798 -- update the reciprocal record
1799 HZ_RELATIONSHIPS_PKG.Update_Row (
1800 X_Rowid => l_rowid,
1801 X_RELATIONSHIP_ID => p_relationship_rec.relationship_id,
1802 X_SUBJECT_ID => NULL,
1803 X_SUBJECT_TYPE => NULL,
1804 X_SUBJECT_TABLE_NAME => NULL,
1805 X_OBJECT_ID => NULL,
1806 X_OBJECT_TYPE => NULL,
1807 X_OBJECT_TABLE_NAME => NULL,
1808 X_PARTY_ID => NULL,
1809 X_RELATIONSHIP_CODE => NULL,
1810 X_DIRECTIONAL_FLAG => NULL,
1811 X_COMMENTS => p_relationship_rec.comments,
1812 X_START_DATE => p_relationship_rec.start_date,
1813 X_END_DATE => p_relationship_rec.end_date,
1814 X_STATUS => p_relationship_rec.status,
1815 X_ATTRIBUTE_CATEGORY => p_relationship_rec.attribute_category,
1816 X_ATTRIBUTE1 => p_relationship_rec.attribute1,
1817 X_ATTRIBUTE2 => p_relationship_rec.attribute2,
1818 X_ATTRIBUTE3 => p_relationship_rec.attribute3,
1819 X_ATTRIBUTE4 => p_relationship_rec.attribute4,
1820 X_ATTRIBUTE5 => p_relationship_rec.attribute5,
1821 X_ATTRIBUTE6 => p_relationship_rec.attribute6,
1822 X_ATTRIBUTE7 => p_relationship_rec.attribute7,
1823 X_ATTRIBUTE8 => p_relationship_rec.attribute8,
1824 X_ATTRIBUTE9 => p_relationship_rec.attribute9,
1825 X_ATTRIBUTE10 => p_relationship_rec.attribute10,
1826 X_ATTRIBUTE11 => p_relationship_rec.attribute11,
1827 X_ATTRIBUTE12 => p_relationship_rec.attribute12,
1828 X_ATTRIBUTE13 => p_relationship_rec.attribute13,
1829 X_ATTRIBUTE14 => p_relationship_rec.attribute14,
1830 X_ATTRIBUTE15 => p_relationship_rec.attribute15,
1831 X_ATTRIBUTE16 => p_relationship_rec.attribute16,
1832 X_ATTRIBUTE17 => p_relationship_rec.attribute17,
1833 X_ATTRIBUTE18 => p_relationship_rec.attribute18,
1834 X_ATTRIBUTE19 => p_relationship_rec.attribute19,
1835 X_ATTRIBUTE20 => p_relationship_rec.attribute20,
1836 X_CONTENT_SOURCE_TYPE => NULL,
1837 X_RELATIONSHIP_TYPE => NULL,
1838 X_OBJECT_VERSION_NUMBER => p_object_version_number,
1839 X_CREATED_BY_MODULE => p_relationship_rec.created_by_module,
1840 X_APPLICATION_ID => p_relationship_rec.application_id,
1841 X_ADDITIONAL_INFORMATION1 => p_relationship_rec.additional_information1,
1842 X_ADDITIONAL_INFORMATION2 => p_relationship_rec.additional_information2,
1843 X_ADDITIONAL_INFORMATION3 => p_relationship_rec.additional_information3,
1844 X_ADDITIONAL_INFORMATION4 => p_relationship_rec.additional_information4,
1845 X_ADDITIONAL_INFORMATION5 => p_relationship_rec.additional_information5,
1846 X_ADDITIONAL_INFORMATION6 => p_relationship_rec.additional_information6,
1847 X_ADDITIONAL_INFORMATION7 => p_relationship_rec.additional_information7,
1848 X_ADDITIONAL_INFORMATION8 => p_relationship_rec.additional_information8,
1849 X_ADDITIONAL_INFORMATION9 => p_relationship_rec.additional_information9,
1850 X_ADDITIONAL_INFORMATION10 => p_relationship_rec.additional_information10,
1851 X_ADDITIONAL_INFORMATION11 => p_relationship_rec.additional_information11,
1852 X_ADDITIONAL_INFORMATION12 => p_relationship_rec.additional_information12,
1853 X_ADDITIONAL_INFORMATION13 => p_relationship_rec.additional_information13,
1854 X_ADDITIONAL_INFORMATION14 => p_relationship_rec.additional_information14,
1855 X_ADDITIONAL_INFORMATION15 => p_relationship_rec.additional_information15,
1856 X_ADDITIONAL_INFORMATION16 => p_relationship_rec.additional_information16,
1857 X_ADDITIONAL_INFORMATION17 => p_relationship_rec.additional_information17,
1858 X_ADDITIONAL_INFORMATION18 => p_relationship_rec.additional_information18,
1859 X_ADDITIONAL_INFORMATION19 => p_relationship_rec.additional_information19,
1860 X_ADDITIONAL_INFORMATION20 => p_relationship_rec.additional_information20,
1861 X_ADDITIONAL_INFORMATION21 => p_relationship_rec.additional_information21,
1862 X_ADDITIONAL_INFORMATION22 => p_relationship_rec.additional_information22,
1863 X_ADDITIONAL_INFORMATION23 => p_relationship_rec.additional_information23,
1864 X_ADDITIONAL_INFORMATION24 => p_relationship_rec.additional_information24,
1865 X_ADDITIONAL_INFORMATION25 => p_relationship_rec.additional_information25,
1866 X_ADDITIONAL_INFORMATION26 => p_relationship_rec.additional_information26,
1867 X_ADDITIONAL_INFORMATION27 => p_relationship_rec.additional_information27,
1868 X_ADDITIONAL_INFORMATION28 => p_relationship_rec.additional_information28,
1869 X_ADDITIONAL_INFORMATION29 => p_relationship_rec.additional_information29,
1870 X_ADDITIONAL_INFORMATION30 => p_relationship_rec.additional_information30,
1871 X_DIRECTION_CODE => NULL,
1872 X_PERCENTAGE_OWNERSHIP => p_relationship_rec.percentage_ownership,
1873 X_ACTUAL_CONTENT_SOURCE => p_relationship_rec.actual_content_source
1874 );
1875
1876 -- Debug info.
1877 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1878 hz_utility_v2pub.debug(p_message=>'HZ_RELATIONSHIPS_PKG.Update_Row-2 (-) ',
1879 p_prefix=>l_debug_prefix,
1880 p_msg_level=>fnd_log.level_procedure);
1881 END IF;
1882
1883 -- Check if party exists for party_relationship. If yes, update party.
1884 -- build the record for creation of relationship party record
1885 l_party_rel_rec.relationship_id := p_relationship_rec.relationship_id;
1886 l_party_rel_rec.subject_id := p_relationship_rec.subject_id;
1887 l_party_rel_rec.object_id := p_relationship_rec.object_id;
1888 l_party_rel_rec.party_rec := p_relationship_rec.party_rec;
1889 l_party_rel_rec.party_rec.party_id := l_party_id;
1890 l_party_rel_rec.created_by_module := p_relationship_rec.created_by_module;
1891 l_party_rel_rec.application_id := p_relationship_rec.application_id;
1892
1893 --Bug 6732835 Start of changes
1894 --The status of the party must be in sync with the status of
1895 --the corresponding relationship record
1896 --Bug 7280211 added status 'M' into list
1897 IF (l_party_rel_rec.status IN ('I','A','M'))
1898 THEN
1899 l_party_rel_rec.party_rec.status := p_relationship_rec.status;
1900 ELSE
1901 l_party_rel_rec.party_rec.status := p_relationship_rec.party_rec.status;
1902 END IF;
1903 --Bug 6732835 End of changes
1904
1905 IF nvl(p_party_object_version_number,1) <> FND_API.G_MISS_NUM
1906 THEN
1907 IF l_party_id IS NOT NULL THEN
1908
1909 -- Debug info.
1910 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1911 hz_utility_v2pub.debug(p_message=>'updating party record',
1912 p_prefix =>l_debug_prefix,
1913 p_msg_level=>fnd_log.level_statement);
1914 END IF;
1915
1916 IF p_party_object_version_number IS NULL THEN
1917 l_party_object_version_number := 1;
1918 ELSE
1919 l_party_object_version_number := p_party_object_version_number;
1920 END IF;
1921 do_update_party(
1922 p_party_type => 'PARTY_RELATIONSHIP',
1923 p_relationship_rec => l_party_rel_rec,
1924 p_old_relationship_rec => p_old_relationship_rec,
1925 p_party_object_version_number => l_party_object_version_number,
1926 x_profile_id => l_profile_id,
1927 x_return_status => x_return_status
1928 );
1929 p_party_object_version_number := l_party_object_version_number;
1930 END IF;
1931 END IF;
1932
1933 -- maintain hierarchy information
1934 -- hierarchy needs to be maintained if the relationship type used
1935 -- is hierarchical and the update is trying to change start_date,
1936 -- end_date or status of the relationship
1937 IF l_hierarchical_flag = 'Y' AND
1938 (NVL(p_relationship_rec.start_date, l_start_date) <> l_start_date OR
1939 NVL(p_relationship_rec.end_date, l_end_date) <> l_end_date OR
1940 NVL(p_relationship_rec.status, l_status) <> l_status
1941 )
1942 THEN
1943 -- check if relationship type is parent one
1944 IF l_direction_code = 'P' THEN
1945 -- assign the subject to parent for hierarchy
1946 l_hierarchy_rec.hierarchy_type := l_relationship_type;
1947 l_hierarchy_rec.parent_id := l_subject_id;
1948 l_hierarchy_rec.parent_table_name := l_subject_table_name;
1949 l_hierarchy_rec.parent_object_type := l_subject_type;
1950 l_hierarchy_rec.child_id := l_object_id;
1951 l_hierarchy_rec.child_table_name := l_object_table_name;
1952 l_hierarchy_rec.child_object_type := l_object_type;
1953 l_hierarchy_rec.effective_start_date := NVL(p_relationship_rec.start_date, l_start_date);
1954 l_hierarchy_rec.effective_end_date := NVL(p_relationship_rec.end_date, l_end_date);
1955 l_hierarchy_rec.relationship_id := p_relationship_rec.relationship_id;
1956 l_hierarchy_rec.status := NVL(p_relationship_rec.status, l_status);
1957 ELSIF l_direction_code = 'C' THEN
1958 -- assign the object to parent
1959 l_hierarchy_rec.hierarchy_type := l_relationship_type;
1960 l_hierarchy_rec.parent_id := l_object_id;
1961 l_hierarchy_rec.parent_table_name := l_object_table_name;
1962 l_hierarchy_rec.parent_object_type := l_object_type;
1963 l_hierarchy_rec.child_id := l_subject_id;
1964 l_hierarchy_rec.child_table_name := l_subject_table_name;
1965 l_hierarchy_rec.child_object_type := l_subject_type;
1966 l_hierarchy_rec.effective_start_date := NVL(p_relationship_rec.start_date, l_start_date);
1967 l_hierarchy_rec.effective_end_date := NVL(p_relationship_rec.end_date, l_end_date);
1968 l_hierarchy_rec.relationship_id := p_relationship_rec.relationship_id;
1969 l_hierarchy_rec.status := NVL(p_relationship_rec.status, l_status);
1970 END IF;
1971
1972 HZ_HIERARCHY_PUB.update_link(
1973 p_init_msg_list => FND_API.G_FALSE,
1974 p_hierarchy_node_rec => l_hierarchy_rec,
1975 x_return_status => x_return_status,
1976 x_msg_count => l_msg_count,
1977 x_msg_data => l_msg_data
1978 );
1979
1980 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1981 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1982 RAISE FND_API.G_EXC_ERROR;
1983 ELSE
1984 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1985 END IF;
1986 END IF;
1987
1988 END IF;
1989
1990 --
1991 -- added for R12 party usage project
1992 --
1993 IF ((p_relationship_rec.status IS NULL OR
1994 p_relationship_rec.status = 'A') AND
1995 l_status = 'A' AND
1996 (p_relationship_rec.start_date IS NOT NULL AND
1997 p_relationship_rec.start_date <> l_start_date AND
1998 p_relationship_rec.start_date <> fnd_api.g_miss_date OR
1999 p_relationship_rec.end_date IS NOT NULL AND
2000 p_relationship_rec.end_date <> l_end_date AND
2001 p_relationship_rec.end_date <> fnd_api.g_miss_date) OR
2002 p_relationship_rec.status = 'I' AND
2003 l_status = 'A' OR
2004 p_relationship_rec.status = 'A' AND
2005 l_status = 'I') AND
2006 (l_subject_type = 'PERSON' AND
2007 l_object_type IN ('PERSON', 'ORGANIZATION') OR
2008 l_object_type = 'PERSON' AND
2009 l_subject_type IN ('PERSON', 'ORGANIZATION')) AND
2010 l_subject_table_name = 'HZ_PARTIES' AND
2011 l_object_table_name = 'HZ_PARTIES'
2012 THEN
2013 l_party_usg_assignment_rec.owner_table_name := 'HZ_RELATIONSHIPS';
2014 l_party_usg_assignment_rec.owner_table_id := p_relationship_rec.relationship_id;
2015 l_party_usg_assignment_rec.effective_start_date := p_relationship_rec.start_date;
2016 l_party_usg_assignment_rec.effective_end_date := p_relationship_rec.end_date;
2017
2018 IF p_relationship_rec.status = 'A' AND l_status = 'I' THEN
2019 IF p_relationship_rec.start_date IS NULL THEN
2020 l_party_usg_assignment_rec.effective_start_date := l_start_date;
2021 END IF;
2022
2023 IF p_relationship_rec.end_date IS NULL THEN
2024 l_party_usg_assignment_rec.effective_end_date := l_end_date;
2025 END IF;
2026 ELSIF p_relationship_rec.status = 'I' AND l_status = 'A' THEN
2027 IF p_relationship_rec.start_date IS NULL AND
2028 trunc(l_start_date) > trunc(sysdate) OR
2029 p_relationship_rec.start_date IS NOT NULL AND
2030 trunc(p_relationship_rec.start_date) > trunc(sysdate)
2031 THEN
2032 l_party_usg_assignment_rec.effective_start_date := trunc(sysdate);
2033 END IF;
2034
2035 IF p_relationship_rec.end_date IS NULL AND
2036 trunc(l_end_date) > trunc(sysdate) OR
2037 p_relationship_rec.end_date IS NOT NULL AND
2038 trunc(p_relationship_rec.end_date) > trunc(sysdate)
2039 THEN
2040 l_party_usg_assignment_rec.effective_end_date := trunc(sysdate);
2041 END IF;
2042 END IF;
2043
2044 HZ_PARTY_USG_ASSIGNMENT_PVT.update_usg_assignment (
2045 p_validation_level => HZ_PARTY_USG_ASSIGNMENT_PVT.G_VALID_LEVEL_NONE,
2046 p_party_usg_assignment_rec => l_party_usg_assignment_rec,
2047 x_return_status => x_return_status,
2048 x_msg_count => l_msg_count,
2049 x_msg_data => l_msg_data
2050 );
2051
2052 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2053 RAISE fnd_api.g_exc_error;
2054 END IF;
2055 END IF;
2056
2057 -- Debug info.
2058 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2059 hz_utility_v2pub.debug(p_message=>'do_update_rel (-)',
2060 p_prefix=>l_debug_prefix,
2061 p_msg_level=>fnd_log.level_procedure);
2062 END IF;
2063
2064 END do_update_rel;
2065
2066
2067 /*===========================================================================+
2068 | PROCEDURE
2069 | do_update_party_flags
2070 |
2071 | DESCRIPTION
2072 | Denormalize flags to hz_parties:
2073 | COMPETITOR_FLAG, REFERENCE_USE_FLAG, THIRD_PARTY_FLAG
2074 |
2075 | SCOPE - PRIVATE
2076 |
2077 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2078 |
2079 | ARGUMENTS : IN:
2080 | OUT:
2081 | IN/ OUT:
2082 | p_relationship_rec
2083 | p_party_id
2084 |
2085 | RETURNS : NONE
2086 |
2087 | NOTES
2088 | If the end_date is today, we will denormailize the flags to 'N'
2089 |
2090 | MODIFICATION HISTORY
2091 |
2092 +===========================================================================*/
2093
2094 PROCEDURE do_update_party_flags(
2095 p_relationship_rec IN RELATIONSHIP_REC_TYPE,
2096 p_party_id IN NUMBER
2097 ) IS
2098
2099 l_party_id NUMBER;
2100 l_reference_use_flag VARCHAR2(1) := 'N';
2101 l_third_party_flag VARCHAR2(1) := 'N';
2102 l_competitor_flag VARCHAR2(1) := 'N';
2103 l_end_date DATE := p_relationship_rec.end_date;
2104 l_status VARCHAR2(1) := p_relationship_rec.status;
2105
2106 BEGIN
2107
2108 --check if party record is locked by any one else.
2109 BEGIN
2110 SELECT party_id INTO l_party_id
2111 FROM hz_parties
2112 WHERE party_id = p_party_id
2113 FOR UPDATE NOWAIT;
2114 EXCEPTION WHEN OTHERS THEN
2115 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
2116 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_PARTIES');
2117 FND_MSG_PUB.ADD;
2118 RAISE FND_API.G_EXC_ERROR;
2119 END;
2120
2121 IF l_end_date IS NULL
2122 OR l_end_date = FND_API.G_MISS_DATE
2123 THEN
2124 l_end_date := to_date('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS');
2125 ELSIF l_end_date = sysdate THEN
2126 l_end_date := sysdate-1;
2127 END IF;
2128
2129 IF l_status IS NULL
2130 OR l_status = FND_API.G_MISS_CHAR
2131 THEN
2132 l_status := 'A';
2133 END IF;
2134
2135 IF p_relationship_rec.relationship_code = 'COMPETITOR_OF' THEN
2136 IF l_status = 'A'
2137 AND
2138 (SYSDATE BETWEEN p_relationship_rec.start_date AND l_end_date)
2139 THEN
2140 l_competitor_flag := 'Y';
2141 END IF;
2142
2143 UPDATE HZ_PARTIES
2144 SET COMPETITOR_FLAG = l_competitor_flag
2145 WHERE PARTY_ID = p_party_id;
2146
2147 ELSIF p_relationship_rec.relationship_code = 'REFERENCE_FOR' THEN
2148 IF l_status = 'A'
2149 AND
2150 (SYSDATE BETWEEN p_relationship_rec.start_date AND l_end_date)
2151 THEN
2152 l_reference_use_flag := 'Y';
2153 END IF;
2154
2155 UPDATE HZ_PARTIES
2156 SET REFERENCE_USE_FLAG = l_reference_use_flag
2157 WHERE PARTY_ID = p_party_id;
2158
2159 ELSIF p_relationship_rec.relationship_code = 'PARTNER_OF' THEN
2160 IF l_status = 'A'
2161 AND (SYSDATE BETWEEN p_relationship_rec.start_date AND l_end_date)
2162 THEN
2163 l_third_party_flag := 'Y';
2164 END IF;
2165
2166 UPDATE HZ_PARTIES
2167 SET THIRD_PARTY_FLAG = l_third_party_flag
2168 WHERE PARTY_ID = p_party_id;
2169
2170 END IF;
2171
2172 END do_update_party_flags;
2173
2174
2175 /*===========================================================================+
2176 | PROCEDURE
2177 | do_create_party
2178 |
2179 | DESCRIPTION
2180 | Creates party.
2181 |
2182 | SCOPE - PUBLIC
2183 |
2184 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2185 |
2186 | ARGUMENTS : IN:
2187 | p_party_type
2188 |
2189 | OUT:
2190 | x_party_id
2191 | x_party_number
2192 | x_profile_id
2193 | IN/ OUT:
2194 | p_person_rec
2195 | p_organization_rec
2196 | p_group_rec
2197 | p_party_rel_rec
2198 | x_return_status
2199 |
2200 | RETURNS : NONE
2201 |
2202 | NOTES
2203 |
2204 | MODIFICATION HISTORY
2205 |
2206 | 06-APR-2005 Rajib Ranjan Borah o Bug 4284731. If profile option for
2207 | generating party number is set to NO,
2208 | and no value is passed for party_number,
2209 | then donot throw any error. Instead generate
2210 | party_number from sequence.
2211 +===========================================================================*/
2212
2213
2214 PROCEDURE do_create_party(
2215 p_party_type IN VARCHAR2,
2216 p_relationship_rec IN RELATIONSHIP_REC_TYPE,
2217 x_party_id OUT NOCOPY NUMBER,
2218 x_party_number OUT NOCOPY VARCHAR2,
2219 x_profile_id OUT NOCOPY NUMBER,
2220 x_return_status IN OUT NOCOPY VARCHAR2
2221 ) IS
2222
2223 l_party_id NUMBER;
2224 l_party_number VARCHAR2(30);
2225 l_generate_party_number VARCHAR2(1);
2226 l_rowid ROWID := NULL;
2227 l_count NUMBER;
2228 l_party_rec HZ_PARTY_V2PUB.PARTY_REC_TYPE := p_relationship_rec.party_rec;
2229 l_party_name HZ_PARTIES.PARTY_NAME%TYPE;
2230 l_subject_name HZ_PARTIES.PARTY_NAME%TYPE;
2231 l_object_name HZ_PARTIES.PARTY_NAME%TYPE;
2232 l_customer_key HZ_PARTIES.CUSTOMER_KEY%TYPE;
2233 l_code_assignment_id NUMBER;
2234 l_msg_count NUMBER;
2235 l_msg_data VARCHAR2(2000);
2236 l_dummy VARCHAR2(1);
2237 l_debug_prefix VARCHAR2(30) := '';
2238
2239 BEGIN
2240
2241 -- Debug info.
2242 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2243 hz_utility_v2pub.debug(p_message=>'do_create_party (+)',
2244 p_prefix=>l_debug_prefix,
2245 p_msg_level=>fnd_log.level_procedure);
2246 END IF;
2247
2248 l_party_id := l_party_rec.party_id;
2249 l_party_number := l_party_rec.party_number;
2250
2251 -- if primary key value is passed, check for uniqueness.
2252 IF l_party_id IS NOT NULL AND
2253 l_party_id <> FND_API.G_MISS_NUM
2254 THEN
2255 BEGIN
2256 SELECT 'Y'
2257 INTO l_dummy
2258 FROM HZ_PARTIES
2259 WHERE PARTY_ID = l_party_id;
2260
2261 FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_COLUMN');
2262 FND_MESSAGE.SET_TOKEN('COLUMN', 'party_id');
2263 FND_MSG_PUB.ADD;
2264 RAISE FND_API.G_EXC_ERROR;
2265
2266 EXCEPTION
2267 WHEN NO_DATA_FOUND THEN
2268 NULL;
2269 END;
2270 END IF;
2271
2272 x_party_id := l_party_id;
2273
2274 -- if GENERATE_PARTY_NUMBER is 'N', then if party_number is not passed or is
2275 -- a duplicate raise error.
2276 l_generate_party_number := fnd_profile.value('HZ_GENERATE_PARTY_NUMBER');
2277
2278 IF l_generate_party_number = 'N' THEN
2279 IF l_party_number = FND_API.G_MISS_CHAR
2280 OR
2281 l_party_number IS NULL
2282 THEN
2283 -- Bug 4284731. If no party_number is passed in, do_not throw any error.
2284 -- Parties of type 'PARTY_RELATIONSHIP' are mostly an internal TCA concept.
2285 -- Even if such parties are used by other teams, the party number of such
2286 -- parties will not be displayed on the UI.
2287 NULL;
2288
2289 /* FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
2290 FND_MESSAGE.SET_TOKEN('COLUMN', 'party number');
2291 FND_MSG_PUB.ADD;
2292 RAISE FND_API.G_EXC_ERROR;
2293 END IF;
2294 */
2295 ELSE
2296
2297 -- Bug 4284731. No changes have been made as the user is explicitely passing a duplicate value.
2298
2299 BEGIN
2300 SELECT 'Y'
2301 INTO l_dummy
2302 FROM HZ_PARTIES
2303 WHERE PARTY_NUMBER = l_party_number;
2304
2305 FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_COLUMN');
2306 FND_MESSAGE.SET_TOKEN('COLUMN', 'party_number');
2307 FND_MSG_PUB.ADD;
2308 RAISE FND_API.G_EXC_ERROR;
2309
2310 EXCEPTION
2311 WHEN NO_DATA_FOUND THEN
2312 NULL;
2313 END;
2314 END IF;
2315 ELSIF l_generate_party_number = 'Y'
2316 OR
2317 l_generate_party_number IS NULL
2318 THEN
2319
2320 IF l_party_number <> FND_API.G_MISS_CHAR
2321 AND
2322 l_party_number IS NOT NULL
2323 THEN
2324 -- Bug 4284731. No changes were made here as party_number was explicitly
2325 -- passed despite the fact that auto numbering is on.
2326
2327 FND_MESSAGE.SET_NAME('AR', 'HZ_API_PARTY_NUMBER_AUTO_ON');
2328 FND_MSG_PUB.ADD;
2329 RAISE FND_API.G_EXC_ERROR;
2330 END IF;
2331
2332 END IF;
2333
2334 x_party_number := l_party_number;
2335
2336 HZ_REGISTRY_VALIDATE_V2PUB.validate_party(
2337 'C',
2338 l_party_rec,
2339 NULL, NULL,
2340 x_return_status);
2341
2342 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2343 RAISE FND_API.G_EXC_ERROR;
2344 END IF;
2345
2346 l_party_rec.party_id := l_party_id;
2347 l_party_rec.party_number := l_party_number;
2348
2349 -- build the party_name for relationship party
2350 SELECT PARTY_NAME
2351 INTO l_subject_name
2352 FROM HZ_PARTIES
2353 WHERE PARTY_ID = p_relationship_rec.subject_id;
2354
2355 SELECT PARTY_NAME
2356 INTO l_object_name
2357 FROM HZ_PARTIES
2358 WHERE PARTY_ID = p_relationship_rec.object_id;
2359
2360 l_party_name := SUBSTRB(l_subject_name || '-' ||
2361 l_object_name || '-' ||
2362 l_party_number, 1, 360);
2363
2364 -- this is for orig_system_defaulting
2365 IF l_party_rec.party_id = FND_API.G_MISS_NUM THEN
2366 l_party_rec.party_id := NULL;
2367 END IF;
2368
2369 -- Debug info.
2370 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2371 hz_utility_v2pub.debug(p_message=>'HZ_PARTIES_PKG.Insert_Row (+)',
2372 p_prefix=>l_debug_prefix,
2373 p_msg_level=>fnd_log.level_procedure);
2374 END IF;
2375
2376 HZ_PARTIES_PKG.Insert_Row (
2377 X_PARTY_ID => l_party_rec.party_id,
2378 X_PARTY_NUMBER => l_party_rec.party_number,
2379 X_PARTY_NAME => l_party_name,
2380 X_PARTY_TYPE => p_party_type,
2381 X_VALIDATED_FLAG => l_party_rec.validated_flag,
2382 X_ATTRIBUTE_CATEGORY => l_party_rec.attribute_category,
2383 X_ATTRIBUTE1 => l_party_rec.attribute1,
2384 X_ATTRIBUTE2 => l_party_rec.attribute2,
2385 X_ATTRIBUTE3 => l_party_rec.attribute3,
2386 X_ATTRIBUTE4 => l_party_rec.attribute4,
2387 X_ATTRIBUTE5 => l_party_rec.attribute5,
2388 X_ATTRIBUTE6 => l_party_rec.attribute6,
2389 X_ATTRIBUTE7 => l_party_rec.attribute7,
2390 X_ATTRIBUTE8 => l_party_rec.attribute8,
2391 X_ATTRIBUTE9 => l_party_rec.attribute9,
2392 X_ATTRIBUTE10 => l_party_rec.attribute10,
2393 X_ATTRIBUTE11 => l_party_rec.attribute11,
2394 X_ATTRIBUTE12 => l_party_rec.attribute12,
2395 X_ATTRIBUTE13 => l_party_rec.attribute13,
2396 X_ATTRIBUTE14 => l_party_rec.attribute14,
2397 X_ATTRIBUTE15 => l_party_rec.attribute15,
2398 X_ATTRIBUTE16 => l_party_rec.attribute16,
2399 X_ATTRIBUTE17 => l_party_rec.attribute17,
2400 X_ATTRIBUTE18 => l_party_rec.attribute18,
2401 X_ATTRIBUTE19 => l_party_rec.attribute19,
2402 X_ATTRIBUTE20 => l_party_rec.attribute20,
2403 X_ATTRIBUTE21 => l_party_rec.attribute21,
2404 X_ATTRIBUTE22 => l_party_rec.attribute22,
2405 X_ATTRIBUTE23 => l_party_rec.attribute23,
2406 X_ATTRIBUTE24 => l_party_rec.attribute24,
2407 X_ORIG_SYSTEM_REFERENCE => l_party_rec.orig_system_reference,
2408 X_SIC_CODE => null,
2409 X_HQ_BRANCH_IND => null,
2410 X_CUSTOMER_KEY => null,
2411 X_TAX_REFERENCE => null,
2412 X_JGZZ_FISCAL_CODE => null,
2413 X_PERSON_PRE_NAME_ADJUNCT => null,
2414 X_PERSON_FIRST_NAME => null,
2415 X_PERSON_MIDDLE_NAME => null,
2416 X_PERSON_LAST_NAME => null,
2417 X_PERSON_NAME_SUFFIX => null,
2418 X_PERSON_TITLE => null,
2419 X_PERSON_ACADEMIC_TITLE => null,
2420 X_PERSON_PREVIOUS_LAST_NAME => null,
2421 X_KNOWN_AS => null,
2422 X_PERSON_IDEN_TYPE => null,
2423 X_PERSON_IDENTIFIER => null,
2424 X_GROUP_TYPE => null,
2425 X_COUNTRY => NULL,
2426 X_ADDRESS1 => NULL,
2427 X_ADDRESS2 => NULL,
2428 X_ADDRESS3 => NULL,
2429 X_ADDRESS4 => NULL,
2430 X_CITY => NULL,
2431 X_POSTAL_CODE => NULL,
2432 X_STATE => NULL,
2433 X_PROVINCE => NULL,
2434 X_STATUS => l_party_rec.status,
2435 X_COUNTY => NULL,
2436 X_SIC_CODE_TYPE => null,
2437 X_URL => NULL,
2438 X_EMAIL_ADDRESS => NULL,
2439 X_ANALYSIS_FY => null,
2440 X_FISCAL_YEAREND_MONTH => null,
2441 X_EMPLOYEES_TOTAL => null,
2442 X_CURR_FY_POTENTIAL_REVENUE => null,
2443 X_NEXT_FY_POTENTIAL_REVENUE => null,
2444 X_YEAR_ESTABLISHED => null,
2445 X_GSA_INDICATOR_FLAG => null,
2446 X_MISSION_STATEMENT => null,
2447 X_ORGANIZATION_NAME_PHONETIC => null,
2448 X_PERSON_FIRST_NAME_PHONETIC => null,
2449 X_PERSON_LAST_NAME_PHONETIC => null,
2450 X_LANGUAGE_NAME => NULL,
2451 X_CATEGORY_CODE => l_party_rec.category_code,
2452 X_SALUTATION => l_party_rec.salutation,
2453 X_KNOWN_AS2 => null,
2454 X_KNOWN_AS3 => null,
2455 X_KNOWN_AS4 => null,
2456 X_KNOWN_AS5 => null,
2457 X_OBJECT_VERSION_NUMBER => 1,
2458 X_DUNS_NUMBER_C => null,
2459 X_CREATED_BY_MODULE => p_relationship_rec.created_by_module,
2460 X_APPLICATION_ID => p_relationship_rec.application_id
2461 );
2462
2463 x_party_id := l_party_rec.party_id;
2464 x_party_number := l_party_rec.party_number;
2465
2466 -- Debug info.
2467 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2468 hz_utility_v2pub.debug(p_message=>'HZ_PARTIES_PKG.Insert_Row (-) ' ||
2469 'x_party_id = ' || x_party_id,
2470 p_prefix=>l_debug_prefix,
2471 p_msg_level=>fnd_log.level_procedure);
2472 END IF;
2473
2474 -- update the party_name
2475 l_party_name := SUBSTRB(l_subject_name || '-' ||
2476 l_object_name || '-' ||
2477 x_party_number, 1, 360);
2478
2479 UPDATE HZ_PARTIES SET PARTY_NAME = l_party_name WHERE PARTY_ID = x_party_id;
2480
2481 -- Debug info.
2482 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2483 hz_utility_v2pub.debug(p_message=>'do_create_party (-)',
2484 p_prefix=>l_debug_prefix,
2485 p_msg_level=>fnd_log.level_procedure);
2486 END IF;
2487
2488 END do_create_party;
2489
2490
2491 /*===========================================================================+
2492 | PROCEDURE
2493 | do_update_party
2494 |
2495 | DESCRIPTION
2496 | Updates person and party for person.
2497 |
2498 | SCOPE - PRIVATE
2499 |
2500 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2501 |
2502 | ARGUMENTS : IN:
2503 | p_party_type
2504 | OUT:
2505 | x_profile_id
2506 | IN/ OUT:
2507 | p_person_rec
2508 | p_organization_rec
2509 | p_group_rec
2510 | p_party_rel_rec
2511 | x_return_status
2512 |
2513 | RETURNS : NONE
2514 |
2515 | NOTES
2516 |
2517 | MODIFICATION HISTORY
2518 |
2519 +===========================================================================*/
2520
2521 PROCEDURE do_update_party(
2522 p_party_type IN VARCHAR2,
2523 p_relationship_rec IN RELATIONSHIP_REC_TYPE,
2524 p_old_relationship_rec IN RELATIONSHIP_REC_TYPE,
2525 p_party_object_version_number IN OUT NOCOPY NUMBER,
2526 x_profile_id OUT NOCOPY NUMBER,
2527 x_return_status IN OUT NOCOPY VARCHAR2
2528 ) IS
2529
2530 l_party_rec HZ_PARTY_V2PUB.PARTY_REC_TYPE := p_relationship_rec.party_rec;
2531 l_rowid ROWID;
2532 l_party_name HZ_PARTIES.PARTY_NAME%TYPE := FND_API.G_MISS_CHAR;
2533 l_first_name HZ_PARTIES.PERSON_FIRST_NAME%TYPE;
2534 l_last_name HZ_PARTIES.PERSON_LAST_NAME%TYPE;
2535 l_profile_id NUMBER;
2536 l_effective_start_date DATE;
2537 l_code_assignment_id NUMBER;
2538 l_sic_code HZ_PARTIES.SIC_CODE%TYPE;
2539 l_sic_code_type HZ_PARTIES.SIC_CODE_TYPE%TYPE;
2540 l_content_source_type HZ_RELATIONSHIPS.CONTENT_SOURCE_TYPE%TYPE;
2541 l_msg_count NUMBER;
2542 l_msg_data VARCHAR2(2000);
2543 l_party_object_version_number NUMBER;
2544 l_debug_prefix VARCHAR2(30);
2545
2546 db_created_by_module HZ_PARTIES.CREATED_BY_MODULE%TYPE;
2547
2548 BEGIN
2549
2550 -- Debug info.
2551 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2552 hz_utility_v2pub.debug(p_message=>'do_update_party (+)',
2553 p_prefix=>l_debug_prefix,
2554 p_msg_level=>fnd_log.level_procedure);
2555 END IF;
2556
2557 -- check whether record has been updated by another user.
2558 BEGIN
2559 SELECT NVL(OBJECT_VERSION_NUMBER,1),
2560 ROWID,
2561 CREATED_BY_MODULE
2562 INTO l_party_object_version_number,
2563 l_rowid,
2564 db_created_by_module
2565 FROM HZ_PARTIES
2566 WHERE PARTY_ID = l_party_rec.party_id
2567 FOR UPDATE OF PARTY_ID NOWAIT;
2568
2569 -- lock the current record. if the record is locked by some one else,
2570 -- error out NOCOPY with mesasge indicating that the record has been changed.
2571 -- get the value of profile_id for the current record in the database.
2572
2573 IF NOT
2574 (
2575 ( p_party_object_version_number IS NULL AND l_party_object_version_number IS NULL )
2576 OR
2577 ( p_party_object_version_number IS NOT NULL AND
2578 l_party_object_version_number IS NOT NULL AND
2579 p_party_object_version_number = l_party_object_version_number
2580 )
2581 )
2582 THEN
2583 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
2584 FND_MESSAGE.SET_TOKEN('TABLE', 'hz_parties');
2585 FND_MSG_PUB.ADD;
2586 RAISE FND_API.G_EXC_ERROR;
2587 END IF;
2588
2589 p_party_object_version_number := nvl(l_party_object_version_number, 1) + 1;
2590
2591 EXCEPTION WHEN NO_DATA_FOUND THEN
2592 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
2593 FND_MESSAGE.SET_TOKEN('RECORD', 'parties');
2594 FND_MESSAGE.SET_TOKEN('VALUE', NVL(TO_CHAR(l_party_rec.party_id),'null'));
2595 FND_MSG_PUB.ADD;
2596 RAISE FND_API.G_EXC_ERROR;
2597 END;
2598
2599
2600 HZ_REGISTRY_VALIDATE_V2PUB.validate_party(
2601 'U',
2602 l_party_rec,
2603 p_old_relationship_rec.party_rec,
2604 NVL(db_created_by_module, fnd_api.g_miss_char),
2605 x_return_status);
2606
2607 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2608 RAISE FND_API.G_EXC_ERROR;
2609 END IF;
2610
2611 -- Debug info.
2612 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2613 hz_utility_v2pub.debug(p_message=>'HZ_PARTIES_PKG.Update_Row (+) ',
2614 p_prefix=>l_debug_prefix,
2615 p_msg_level=>fnd_log.level_procedure);
2616 END IF;
2617
2618 -- call table handler to update the record
2619 HZ_PARTIES_PKG.Update_Row (
2620 X_Rowid => l_rowid,
2621 X_PARTY_ID => l_party_rec.party_id,
2622 X_PARTY_NUMBER => NULL,
2623 X_PARTY_NAME => NULL,
2624 -- X_VALIDATED_FLAG => l_party_rec.validated_flag, -- Bug #6341070
2625 X_VALIDATED_FLAG => NULL, -- Set NULL value as per Bug #6341070
2626 X_PARTY_TYPE => p_party_type,
2627 X_ATTRIBUTE_CATEGORY => l_party_rec.attribute_category,
2628 X_ATTRIBUTE1 => l_party_rec.attribute1,
2629 X_ATTRIBUTE2 => l_party_rec.attribute2,
2630 X_ATTRIBUTE3 => l_party_rec.attribute3,
2631 X_ATTRIBUTE4 => l_party_rec.attribute4,
2632 X_ATTRIBUTE5 => l_party_rec.attribute5,
2633 X_ATTRIBUTE6 => l_party_rec.attribute6,
2634 X_ATTRIBUTE7 => l_party_rec.attribute7,
2635 X_ATTRIBUTE8 => l_party_rec.attribute8,
2636 X_ATTRIBUTE9 => l_party_rec.attribute9,
2637 X_ATTRIBUTE10 => l_party_rec.attribute10,
2638 X_ATTRIBUTE11 => l_party_rec.attribute11,
2639 X_ATTRIBUTE12 => l_party_rec.attribute12,
2640 X_ATTRIBUTE13 => l_party_rec.attribute13,
2641 X_ATTRIBUTE14 => l_party_rec.attribute14,
2642 X_ATTRIBUTE15 => l_party_rec.attribute15,
2643 X_ATTRIBUTE16 => l_party_rec.attribute16,
2644 X_ATTRIBUTE17 => l_party_rec.attribute17,
2645 X_ATTRIBUTE18 => l_party_rec.attribute18,
2646 X_ATTRIBUTE19 => l_party_rec.attribute19,
2647 X_ATTRIBUTE20 => l_party_rec.attribute20,
2648 X_ATTRIBUTE21 => l_party_rec.attribute21,
2649 X_ATTRIBUTE22 => l_party_rec.attribute22,
2650 X_ATTRIBUTE23 => l_party_rec.attribute23,
2651 X_ATTRIBUTE24 => l_party_rec.attribute24,
2652 X_ORIG_SYSTEM_REFERENCE => l_party_rec.orig_system_reference,
2653 X_SIC_CODE => null,
2654 X_HQ_BRANCH_IND => null,
2655 X_CUSTOMER_KEY => null,
2656 X_TAX_REFERENCE => null,
2657 X_JGZZ_FISCAL_CODE => null,
2658 X_PERSON_PRE_NAME_ADJUNCT => null,
2659 X_PERSON_FIRST_NAME => null,
2660 X_PERSON_MIDDLE_NAME => null,
2661 X_PERSON_LAST_NAME => null,
2662 X_PERSON_NAME_SUFFIX => null,
2663 X_PERSON_TITLE => null,
2664 X_PERSON_ACADEMIC_TITLE => null,
2665 X_PERSON_PREVIOUS_LAST_NAME => null,
2666 X_KNOWN_AS => null,
2667 X_PERSON_IDEN_TYPE => null,
2668 X_PERSON_IDENTIFIER => null,
2669 X_GROUP_TYPE => null,
2670 X_COUNTRY => NULL,
2671 X_ADDRESS1 => NULL,
2672 X_ADDRESS2 => NULL,
2673 X_ADDRESS3 => NULL,
2674 X_ADDRESS4 => NULL,
2675 X_CITY => NULL,
2676 X_POSTAL_CODE => NULL,
2677 X_STATE => NULL,
2678 X_PROVINCE => NULL,
2679 X_STATUS => l_party_rec.status,
2680 X_COUNTY => NULL,
2681 X_SIC_CODE_TYPE => null,
2682 X_URL => NULL,
2683 X_EMAIL_ADDRESS => NULL,
2684 X_ANALYSIS_FY => null,
2685 X_FISCAL_YEAREND_MONTH => null,
2686 X_EMPLOYEES_TOTAL => null,
2687 X_CURR_FY_POTENTIAL_REVENUE => null,
2688 X_NEXT_FY_POTENTIAL_REVENUE => null,
2689 X_YEAR_ESTABLISHED => null,
2690 X_GSA_INDICATOR_FLAG => null,
2691 X_MISSION_STATEMENT => null,
2692 X_ORGANIZATION_NAME_PHONETIC => null,
2693 X_PERSON_FIRST_NAME_PHONETIC => null,
2694 X_PERSON_LAST_NAME_PHONETIC => null,
2695 X_LANGUAGE_NAME => NULL,
2696 X_CATEGORY_CODE => l_party_rec.category_code,
2697 X_SALUTATION => l_party_rec.salutation,
2698 X_KNOWN_AS2 => null,
2699 X_KNOWN_AS3 => null,
2700 X_KNOWN_AS4 => null,
2701 X_KNOWN_AS5 => null,
2702 X_OBJECT_VERSION_NUMBER => p_party_object_version_number,
2703 X_DUNS_NUMBER_C => null,
2704 X_CREATED_BY_MODULE => p_relationship_rec.created_by_module,
2705 X_APPLICATION_ID => p_relationship_rec.application_id
2706 );
2707
2708 -- Debug info.
2709 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2710 hz_utility_v2pub.debug(p_message=>'HZ_PARTIES_PKG.Update_Row (-) ',
2711 p_prefix=>l_debug_prefix,
2712 p_msg_level=>fnd_log.level_procedure);
2713 END IF;
2714
2715 -- Debug info.
2716 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2717 hz_utility_v2pub.debug(p_message=>'do_update_party (-)',
2718 p_prefix=>l_debug_prefix,
2719 p_msg_level=>fnd_log.level_procedure);
2720 END IF;
2721
2722 END do_update_party;
2723
2724
2725 ----------------------------
2726 -- body of public procedures
2727 ----------------------------
2728
2729 /*===========================================================================+
2730 | PROCEDURE
2731 | create_relationship
2732 |
2733 | DESCRIPTION
2734 | Creates relationship and party for party_relationship
2735 |
2736 | SCOPE - PUBLIC
2737 |
2738 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2739 |
2740 | ARGUMENTS : IN:
2741 | p_init_msg_list
2742 | p_relationship_rec
2743 | p_create_org_contact
2744 | p_party_usage_code
2745 | OUT:
2746 | x_return_status
2747 | x_msg_count
2748 | x_msg_data
2749 | x_party_relationship_id
2750 | x_party_id
2751 | x_party_number
2752 | IN/ OUT:
2753 |
2754 | RETURNS : NONE
2755 |
2756 | NOTES
2757 |
2758 | MODIFICATION HISTORY
2759 | 07-DEC-2004. V.Ravichandran Bug 3801870. Removed defaulting
2760 | for the overloading the procedure
2761 | create_relationship.
2762 | 04-JAN-2005 Rajib Ranjan Borah SSM SST Integration and Extension.
2763 | For non-profile entities, the concept of select
2764 | /de-select data-sources is obsoleted.
2765 +===========================================================================*/
2766
2767 PROCEDURE create_relationship (
2768 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
2769 p_relationship_rec IN RELATIONSHIP_REC_TYPE,
2770 x_relationship_id OUT NOCOPY NUMBER,
2771 x_party_id OUT NOCOPY NUMBER,
2772 x_party_number OUT NOCOPY VARCHAR2,
2773 x_return_status OUT NOCOPY VARCHAR2,
2774 x_msg_count OUT NOCOPY NUMBER,
2775 x_msg_data OUT NOCOPY VARCHAR2
2776 ) IS
2777
2778 BEGIN
2779
2780 create_relationship_with_usg (
2781 p_init_msg_list => p_init_msg_list,
2782 p_relationship_rec => p_relationship_rec,
2783 p_contact_party_id => null,
2784 p_contact_party_usage_code => null,
2785 p_create_org_contact => 'Y',
2786 x_relationship_id => x_relationship_id,
2787 x_party_id => x_party_id,
2788 x_party_number => x_party_number,
2789 x_return_status => x_return_status,
2790 x_msg_count => x_msg_count,
2791 x_msg_data => x_msg_data
2792 );
2793
2794 END create_relationship;
2795
2796
2797 /*===========================================================================+
2798 | PROCEDURE
2799 | create_relationship
2800 |
2801 | DESCRIPTION
2802 | Creates relationship and party for party_relationship.
2803 | This is the overloaded procedure which accepts the
2804 | old signature that doesnt expect the parameter
2805 | p_create_org_contact.
2806 |
2807 | SCOPE - PUBLIC
2808 |
2809 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2810 |
2811 | ARGUMENTS : IN:
2812 | p_init_msg_list
2813 | p_relationship_rec
2814 | p_create_org_contact
2815 | OUT:
2816 | x_return_status
2817 | x_msg_count
2818 | x_msg_data
2819 | x_relationship_id
2820 | x_party_id
2821 | x_party_number
2822 | IN/ OUT:
2823 |
2824 | RETURNS : NONE
2825 |
2826 | NOTES
2827 |
2828 | MODIFICATION HISTORY
2829 |
2830 |
2831 +===========================================================================*/
2832
2833 PROCEDURE create_relationship (
2834 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
2835 p_relationship_rec IN RELATIONSHIP_REC_TYPE,
2836 x_relationship_id OUT NOCOPY NUMBER,
2837 x_party_id OUT NOCOPY NUMBER,
2838 x_party_number OUT NOCOPY VARCHAR2,
2839 x_return_status OUT NOCOPY VARCHAR2,
2840 x_msg_count OUT NOCOPY NUMBER,
2841 x_msg_data OUT NOCOPY VARCHAR2,
2842 p_create_org_contact IN VARCHAR2
2843 ) IS
2844
2845 BEGIN
2846
2847 create_relationship_with_usg (
2848 p_init_msg_list => p_init_msg_list,
2849 p_relationship_rec => p_relationship_rec,
2850 p_contact_party_id => null,
2851 p_contact_party_usage_code => null,
2852 p_create_org_contact => p_create_org_contact,
2853 x_relationship_id => x_relationship_id,
2854 x_party_id => x_party_id,
2855 x_party_number => x_party_number,
2856 x_return_status => x_return_status,
2857 x_msg_count => x_msg_count,
2858 x_msg_data => x_msg_data
2859 );
2860
2861 END create_relationship;
2862
2863
2864 /*===========================================================================+
2865 | PROCEDURE
2866 | create_relationship_with_usg
2867 |
2868 | DESCRIPTION
2869 | Creates relationship and party for party_relationship.
2870 | It also creates party usage assignment.
2871 |
2872 | SCOPE - PUBLIC
2873 |
2874 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2875 |
2876 | ARGUMENTS : IN:
2877 | p_init_msg_list
2878 | p_relationship_rec
2879 | p_contact_party_id
2880 | p_contact_party_usage_code
2881 | p_create_org_contact
2882 | OUT:
2883 | x_return_status
2884 | x_msg_count
2885 | x_msg_data
2886 | x_relationship_id
2887 | x_party_id
2888 | x_party_number
2889 | IN/ OUT:
2890 |
2891 | RETURNS : NONE
2892 |
2893 | NOTES
2894 |
2895 | MODIFICATION HISTORY
2896 |
2897 |
2898 +===========================================================================*/
2899
2900 PROCEDURE create_relationship_with_usg (
2901 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
2902 p_relationship_rec IN RELATIONSHIP_REC_TYPE,
2903 p_contact_party_id IN NUMBER,
2904 p_contact_party_usage_code IN VARCHAR2,
2905 p_create_org_contact IN VARCHAR2,
2906 x_relationship_id OUT NOCOPY NUMBER,
2907 x_party_id OUT NOCOPY NUMBER,
2908 x_party_number OUT NOCOPY VARCHAR2,
2909 x_return_status OUT NOCOPY VARCHAR2,
2910 x_msg_count OUT NOCOPY NUMBER,
2911 x_msg_data OUT NOCOPY VARCHAR2
2912 ) IS
2913
2914 l_rel_rec RELATIONSHIP_REC_TYPE := p_relationship_rec;
2915 l_created_party VARCHAR2(1);
2916
2917 dss_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2918 dss_msg_count NUMBER := 0;
2919 dss_msg_data VARCHAR2(2000):= null;
2920 l_test_security VARCHAR2(1):= 'F';
2921 l_debug_prefix VARCHAR2(30) := '';
2922
2923 -- Bug 3801870.
2924 l_create_org_contact VARCHAR2(1) := NVL(p_create_org_contact,'Y');
2925
2926 BEGIN
2927
2928 -- Standard start of API savepoint
2929 SAVEPOINT create_relationship;
2930
2931 -- Check if API is called in debug mode. If yes, enable debug.
2932 --enable_debug;
2933
2934 -- Debug info.
2935 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2936 hz_utility_v2pub.debug(p_message=>'create_relationship (+)',
2937 p_prefix=>l_debug_prefix,
2938 p_msg_level=>fnd_log.level_procedure);
2939 END IF;
2940
2941 -- Initialize message list if p_init_msg_list is set to TRUE.
2942 IF FND_API.to_Boolean(p_init_msg_list) THEN
2943 FND_MSG_PUB.initialize;
2944 END IF;
2945
2946 -- Initialize API return status to success.
2947 x_return_status := FND_API.G_RET_STS_SUCCESS;
2948
2949
2950 -- Bug 2197181: added for mix-n-match project. first load data
2951 -- sources for this entity. Then assign the actual_content_source
2952 -- to the real data source. The value of content_source_type is
2953 -- depended on if data source is seleted. If it is selected, we reset
2954 -- content_source_type to user-entered. We also check if user
2955 -- has the privilege to create user-entered data if mix-n-match
2956 -- is enabled.
2957
2958 -- Bug 2444678: Removed caching.
2959
2960 -- IF g_rel_mixnmatch_enabled IS NULL THEN
2961 /* SSM SST Integration and Extension
2962 * For non-profile entities, the concept of select/de-select data-sources is obsoleted.
2963
2964 HZ_MIXNM_UTILITY.LoadDataSources(
2965 p_entity_name => 'HZ_RELATIONSHIPS',
2966 p_entity_attr_id => g_rel_entity_attr_id,
2967 p_mixnmatch_enabled => g_rel_mixnmatch_enabled,
2968 p_selected_datasources => g_rel_selected_datasources );
2969 */
2970 -- END IF;
2971
2972 HZ_MIXNM_UTILITY.AssignDataSourceDuringCreation (
2973 p_entity_name => 'HZ_RELATIONSHIPS',
2974 p_entity_attr_id => g_rel_entity_attr_id,
2975 p_mixnmatch_enabled => g_rel_mixnmatch_enabled,
2976 p_selected_datasources => g_rel_selected_datasources,
2977 p_content_source_type => l_rel_rec.content_source_type,
2978 p_actual_content_source => l_rel_rec.actual_content_source,
2979 x_is_datasource_selected => g_rel_is_datasource_selected,
2980 x_return_status => x_return_status );
2981
2982 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2983 RAISE FND_API.G_EXC_ERROR;
2984 END IF;
2985
2986 -- Call to business logic.
2987 do_create_rel(
2988 l_rel_rec,
2989 l_created_party,
2990 x_relationship_id,
2991 x_party_id,
2992 x_party_number,
2993 x_return_status,
2994 -- 3801870.
2995 l_create_org_contact,
2996 p_contact_party_id,
2997 p_contact_party_usage_code);
2998
2999 --
3000 -- Bug 2486394 -Check if the DSS security is granted to the user
3001 -- Bug 3818648: do dss check in party context only. check dss
3002 -- profile before call test_instance.
3003 --
3004 IF NVL(fnd_profile.value('HZ_DSS_ENABLED'), 'N') = 'Y' AND
3005 (l_rel_rec.subject_table_name = 'HZ_PARTIES' OR
3006 l_rel_rec.object_table_name = 'HZ_PARTIES')
3007 THEN
3008 l_test_security :=
3009 hz_dss_util_pub.test_instance(
3010 p_operation_code => 'INSERT',
3011 p_db_object_name => 'HZ_RELATIONSHIPS',
3012 p_instance_pk1_value => x_relationship_id,
3013 p_instance_pk2_value => 'F',
3014 p_user_name => fnd_global.user_name,
3015 x_return_status => dss_return_status,
3016 x_msg_count => dss_msg_count,
3017 x_msg_data => dss_msg_data);
3018
3019 if dss_return_status <> fnd_api.g_ret_sts_success THEN
3020 RAISE FND_API.G_EXC_ERROR;
3021 end if;
3022
3023 if (l_test_security <> 'T' OR l_test_security <> FND_API.G_TRUE) then
3024 --
3025 -- Bug 3835601: replaced the dss message with a more user friendly message
3026 --
3027 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_NO_INSERT_PRIVILEGE');
3028 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',
3029 hz_dss_util_pub.get_display_name('HZ_RELATIONSHIPS', null));
3030 FND_MSG_PUB.ADD;
3031 RAISE FND_API.G_EXC_ERROR;
3032 end if;
3033 END IF;
3034
3035
3036 -- Invoke business event system.
3037
3038 -- SSM SST Integration and Extension
3039 -- For non-profile entities, the concept of select/de-select data-sources is obsoleted.
3040 -- There is no need to check if the data-source is selected.
3041 IF x_return_status = FND_API.G_RET_STS_SUCCESS /* AND
3042 -- Bug 2197181: Added below condition for Mix-n-Match
3043 g_rel_is_datasource_selected = 'Y' */
3044 THEN
3045 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'Y')) THEN
3046 HZ_BUSINESS_EVENT_V2PVT.create_relationship_event (
3047 l_rel_rec, l_created_party );
3048 END IF;
3049
3050 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'BO_EVENTS_ENABLED')) THEN
3051 -- populate function for integration service
3052 HZ_POPULATE_BOT_PKG.pop_hz_relationships(
3053 p_operation => 'I',
3054 p_RELATIONSHIP_ID => x_relationship_id );
3055 END IF;
3056 END IF;
3057
3058 -- Standard call to get message count and if count is 1, get message info.
3059 FND_MSG_PUB.Count_And_Get(
3060 p_encoded => FND_API.G_FALSE,
3061 p_count => x_msg_count,
3062 p_data => x_msg_data);
3063
3064 -- Debug info.
3065 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
3066 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
3067 p_msg_data=>x_msg_data,
3068 p_msg_type=>'WARNING',
3069 p_msg_level=>fnd_log.level_exception);
3070 END IF;
3071 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3072 hz_utility_v2pub.debug(p_message=>'create_relationship (-)',
3073 p_prefix=>l_debug_prefix,
3074 p_msg_level=>fnd_log.level_procedure);
3075 END IF;
3076
3077 -- Check if API is called in debug mode. If yes, disable debug.
3078 --disable_debug;
3079
3080 EXCEPTION
3081 WHEN FND_API.G_EXC_ERROR THEN
3082 ROLLBACK TO create_relationship;
3083 x_return_status := FND_API.G_RET_STS_ERROR;
3084 FND_MSG_PUB.Count_And_Get(
3085 p_encoded => FND_API.G_FALSE,
3086 p_count => x_msg_count,
3087 p_data => x_msg_data);
3088
3089 -- Debug info.
3090 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
3091 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
3092 p_msg_data=>x_msg_data,
3093 p_msg_type=>'ERROR',
3094 p_msg_level=>fnd_log.level_error);
3095
3096 END IF;
3097 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3098 hz_utility_v2pub.debug(p_message=>'create_relationship (-)',
3099 p_prefix=>l_debug_prefix,
3100 p_msg_level=>fnd_log.level_procedure);
3101 END IF;
3102
3103 -- Check if API is called in debug mode. If yes, disable debug.
3104 --disable_debug;
3105
3106 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3107 ROLLBACK TO create_relationship;
3108 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3109 FND_MSG_PUB.Count_And_Get(
3110 p_encoded => FND_API.G_FALSE,
3111 p_count => x_msg_count,
3112 p_data => x_msg_data);
3113
3114 -- Debug info.
3115 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
3116 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
3117 p_msg_data=>x_msg_data,
3118 p_msg_type=>'UNEXPECTED ERROR',
3119 p_msg_level=>fnd_log.level_error);
3120
3121 END IF;
3122 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3123 hz_utility_v2pub.debug(p_message=>'create_relationship (-)',
3124 p_prefix=>l_debug_prefix,
3125 p_msg_level=>fnd_log.level_procedure);
3126 END IF;
3127
3128 -- Check if API is called in debug mode. If yes, disable debug.
3129 --disable_debug;
3130
3131 WHEN OTHERS THEN
3132 ROLLBACK TO create_relationship;
3133 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3134 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
3135 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3136 FND_MSG_PUB.ADD;
3137 FND_MSG_PUB.Count_And_Get(
3138 p_encoded => FND_API.G_FALSE,
3139 p_count => x_msg_count,
3140 p_data => x_msg_data);
3141
3142 -- Debug info.
3143 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
3144 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
3145 p_msg_data=>x_msg_data,
3146 p_msg_type=>'SQL ERROR',
3147 p_msg_level=>fnd_log.level_error);
3148
3149 END IF;
3150 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3151 hz_utility_v2pub.debug(p_message=>'create_relationship (-)',
3152 p_prefix=>l_debug_prefix,
3153 p_msg_level=>fnd_log.level_procedure);
3154 END IF;
3155
3156 -- Check if API is called in debug mode. If yes, disable debug.
3157 --disable_debug;
3158
3159 END create_relationship_with_usg;
3160
3161
3162 /*===========================================================================+
3163 | PROCEDURE
3164 | update_relationship
3165 |
3166 | DESCRIPTION
3167 | Updates relationship and party for party_relationship.
3168 |
3169 | SCOPE - PUBLIC
3170 |
3171 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
3172 |
3173 | ARGUMENTS : IN:
3174 | p_init_msg_list
3175 | p_party_rel_rec
3176 | OUT:
3177 | x_return_status
3178 | x_msg_count
3179 | x_msg_data
3180 | IN/ OUT:
3181 |
3182 | RETURNS : NONE
3183 |
3184 | NOTES
3185 |
3186 | MODIFICATION HISTORY
3187 |
3188 | 04-JAN-2005 Rajib Ranjan Borah o SSM SST Integration and Extension.
3189 | For non-profile entities, the concept of select
3190 | /de-select data-sources is obsoleted.
3191 +===========================================================================*/
3192
3193 PROCEDURE update_relationship (
3194 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
3195 p_relationship_rec IN RELATIONSHIP_REC_TYPE,
3196 p_object_version_number IN OUT NOCOPY NUMBER,
3197 p_party_object_version_number IN OUT NOCOPY NUMBER,
3198 x_return_status OUT NOCOPY VARCHAR2,
3199 x_msg_count OUT NOCOPY NUMBER,
3200 x_msg_data OUT NOCOPY VARCHAR2
3201 ) IS
3202
3203 l_rel_rec RELATIONSHIP_REC_TYPE := p_relationship_rec;
3204 l_old_rel_rec RELATIONSHIP_REC_TYPE;
3205 l_data_source_from VARCHAR2(30);
3206
3207 dss_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3208 dss_msg_count NUMBER := 0;
3209 dss_msg_data VARCHAR2(2000):= null;
3210 l_test_security VARCHAR2(1):= 'F';
3211 l_debug_prefix VARCHAR2(30) := '';
3212
3213 BEGIN
3214
3215 -- Standard start of API savepoint
3216 SAVEPOINT update_relationship;
3217
3218 -- Check if API is called in debug mode. If yes, enable debug.
3219 --enable_debug;
3220
3221 -- Debug info.
3222 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3223 hz_utility_v2pub.debug(p_message=>'update_relationship (+)',
3224 p_prefix=>l_debug_prefix,
3225 p_msg_level=>fnd_log.level_procedure);
3226 END IF;
3227
3228 -- Initialize message list if p_init_msg_list is set to TRUE.
3229 IF FND_API.to_Boolean(p_init_msg_list) THEN
3230 FND_MSG_PUB.initialize;
3231 END IF;
3232
3233 -- Initialize API return status to success.
3234 x_return_status := FND_API.G_RET_STS_SUCCESS;
3235
3236 -- Get old records. Will be used by business event system.
3237 get_relationship_rec (
3238 p_relationship_id => l_rel_rec.relationship_id,
3239 p_directional_flag => 'F',
3240 x_rel_rec => l_old_rel_rec,
3241 x_return_status => x_return_status,
3242 x_msg_count => x_msg_count,
3243 x_msg_data => x_msg_data );
3244
3245 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3246 RAISE FND_API.G_EXC_ERROR;
3247 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3248 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3249 END IF;
3250
3251 --
3252 -- Bug 2486394 -Check if the DSS security is granted to the user
3253 -- Bug 3818648: do dss check in party context only. check dss
3254 -- profile before call test_instance.
3255 --
3256 IF NVL(fnd_profile.value('HZ_DSS_ENABLED'), 'N') = 'Y' AND
3257 (l_old_rel_rec.subject_table_name = 'HZ_PARTIES' OR
3258 l_old_rel_rec.object_table_name = 'HZ_PARTIES')
3259 THEN
3260 l_test_security :=
3261 hz_dss_util_pub.test_instance(
3262 p_operation_code => 'UPDATE',
3263 p_db_object_name => 'HZ_RELATIONSHIPS',
3264 p_instance_pk1_value => l_rel_rec.relationship_id,
3265 p_instance_pk2_value => 'F',
3266 p_user_name => fnd_global.user_name,
3267 x_return_status => dss_return_status,
3268 x_msg_count => dss_msg_count,
3269 x_msg_data => dss_msg_data);
3270
3271 if dss_return_status <> fnd_api.g_ret_sts_success THEN
3272 RAISE FND_API.G_EXC_ERROR;
3273 end if;
3274
3275 if (l_test_security <> 'T' OR l_test_security <> FND_API.G_TRUE) then
3276 --
3277 -- Bug 3835601: replaced the dss message with a more user friendly message
3278 --
3279 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_NO_UPDATE_PRIVILEGE');
3280 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',
3281 hz_dss_util_pub.get_display_name('HZ_RELATIONSHIPS', null));
3282 FND_MSG_PUB.ADD;
3283 RAISE FND_API.G_EXC_ERROR;
3284 end if;
3285 END IF;
3286
3287 -- Bug 2197181: added for mix-n-match project. first load data
3288 -- sources for this entity.
3289
3290 -- Bug 2444678: Removed caching.
3291
3292 /* SSM SST Integration and Extension
3293 * For non-profile entities, the concept of select/de-select data-sources is obsoleted.
3294 * There is no need to check if the data-source is selected.
3295 -- IF g_rel_mixnmatch_enabled IS NULL THEN
3296 HZ_MIXNM_UTILITY.LoadDataSources(
3297 p_entity_name => 'HZ_RELATIONSHIPS',
3298 p_entity_attr_id => g_rel_entity_attr_id,
3299 p_mixnmatch_enabled => g_rel_mixnmatch_enabled,
3300 p_selected_datasources => g_rel_selected_datasources );
3301 -- END IF;
3302
3303 -- Bug 2197181: added for mix-n-match project.
3304 -- check if the data source is seleted.
3305
3306 g_rel_is_datasource_selected :=
3307 HZ_MIXNM_UTILITY.isDataSourceSelected (
3308 p_selected_datasources => g_rel_selected_datasources,
3309 p_actual_content_source => l_old_rel_rec.actual_content_source );
3310 */
3311 -- Call to business logic.
3312 do_update_rel(
3313 l_rel_rec,
3314 l_old_rel_rec,
3315 p_object_version_number,
3316 p_party_object_version_number,
3317 x_return_status);
3318
3319 -- Call to indicate relationship update to DQM
3320 HZ_DQM_SYNC.sync_relationship(l_rel_rec.relationship_id, 'U');
3321
3322 -- Invoke business event system.
3323
3324 -- SSM SST Integration and Extension
3325 -- For non-profile entities, the concept of select/de-select data-sources is obsoleted.
3326 -- There is no need to check if the data-source is selected.
3327
3328 IF x_return_status = FND_API.G_RET_STS_SUCCESS /* AND
3329 -- Bug 2197181: Added below condition for Mix-n-Match
3330 g_rel_is_datasource_selected = 'Y' */
3331 THEN
3332 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'Y')) THEN
3333 HZ_BUSINESS_EVENT_V2PVT.update_relationship_event (
3334 l_rel_rec, l_old_rel_rec );
3335 END IF;
3336
3337 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'BO_EVENTS_ENABLED')) THEN
3338 -- populate function for integration service
3339 HZ_POPULATE_BOT_PKG.pop_hz_relationships(
3340 p_operation => 'U',
3341 p_RELATIONSHIP_ID => l_rel_rec.relationship_id );
3342 END IF;
3343 END IF;
3344
3345 HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
3346 -- Standard call to get message count and if count is 1, get message info.
3347 FND_MSG_PUB.Count_And_Get(
3348 p_encoded => FND_API.G_FALSE,
3349 p_count => x_msg_count,
3350 p_data => x_msg_data);
3351
3352 -- Debug info.
3353 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
3354 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
3355 p_msg_data=>x_msg_data,
3356 p_msg_type=>'WARNING',
3357 p_msg_level=>fnd_log.level_exception);
3358 END IF;
3359 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3360 hz_utility_v2pub.debug(p_message=>'update_relationship (-)',
3361 p_prefix=>l_debug_prefix,
3362 p_msg_level=>fnd_log.level_procedure);
3363 END IF;
3364
3365 -- Check if API is called in debug mode. If yes, disable debug.
3366 --disable_debug;
3367
3368 EXCEPTION
3369 WHEN FND_API.G_EXC_ERROR THEN
3370 ROLLBACK TO update_relationship;
3371 HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
3372 x_return_status := FND_API.G_RET_STS_ERROR;
3373 FND_MSG_PUB.Count_And_Get(
3374 p_encoded => FND_API.G_FALSE,
3375 p_count => x_msg_count,
3376 p_data => x_msg_data);
3377
3378 -- Debug info.
3379 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
3380 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
3381 p_msg_data=>x_msg_data,
3382 p_msg_type=>'ERROR',
3383 p_msg_level=>fnd_log.level_error);
3384
3385 END IF;
3386 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3387 hz_utility_v2pub.debug(p_message=>'update_relationship (-)',
3388 p_prefix=>l_debug_prefix,
3389 p_msg_level=>fnd_log.level_procedure);
3390 END IF;
3391
3392 -- Check if API is called in debug mode. If yes, disable debug.
3393 --disable_debug;
3394
3395 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3396 ROLLBACK TO update_relationship;
3397 HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
3398 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3399 FND_MSG_PUB.Count_And_Get(
3400 p_encoded => FND_API.G_FALSE,
3401 p_count => x_msg_count,
3402 p_data => x_msg_data);
3403
3404 -- Debug info.
3405 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
3406 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
3407 p_msg_data=>x_msg_data,
3408 p_msg_type=>'UNEXPECTED ERROR',
3409 p_msg_level=>fnd_log.level_error);
3410
3411 END IF;
3412 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3413 hz_utility_v2pub.debug(p_message=>'update_relationship (-)',
3414 p_prefix=>l_debug_prefix,
3415 p_msg_level=>fnd_log.level_procedure);
3416 END IF;
3417
3418 -- Check if API is called in debug mode. If yes, disable debug.
3419 --disable_debug;
3420
3421 WHEN OTHERS THEN
3422 ROLLBACK TO update_relationship;
3423 HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
3424 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3425 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
3426 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3427 FND_MSG_PUB.ADD;
3428 FND_MSG_PUB.Count_And_Get(
3429 p_encoded => FND_API.G_FALSE,
3430 p_count => x_msg_count,
3431 p_data => x_msg_data);
3432
3433 -- Debug info.
3434 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
3435 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
3436 p_msg_data=>x_msg_data,
3437 p_msg_type=>'SQL ERROR',
3438 p_msg_level=>fnd_log.level_error);
3439
3440 END IF;
3441 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3442 hz_utility_v2pub.debug(p_message=>'update_relationship (-)',
3443 p_prefix=>l_debug_prefix,
3444 p_msg_level=>fnd_log.level_procedure);
3445 END IF;
3446 -- Check if API is called in debug mode. If yes, disable debug.
3447 --disable_debug;
3448
3449 END update_relationship;
3450
3451 /*===========================================================================+
3452 | PROCEDURE
3453 | get_relationship_rec
3454 |
3455 | DESCRIPTION
3456 | Gets relationship and party for party_relationship.
3457 |
3458 | SCOPE - PUBLIC
3459 |
3460 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
3461 |
3462 | ARGUMENTS : IN:
3463 | p_init_msg_list
3464 | p_party_rel_rec
3465 | OUT:
3466 | x_return_status
3467 | x_msg_count
3468 | x_msg_data
3469 | IN/ OUT:
3470 |
3471 | RETURNS : NONE
3472 |
3473 | NOTES
3474 |
3475 | MODIFICATION HISTORY
3476 |
3477 +===========================================================================*/
3478
3479 PROCEDURE get_relationship_rec (
3480 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3481 p_relationship_id IN NUMBER,
3482 p_directional_flag IN VARCHAR2 := 'F',
3483 x_rel_rec OUT NOCOPY RELATIONSHIP_REC_TYPE,
3484 x_return_status OUT NOCOPY VARCHAR2,
3485 x_msg_count OUT NOCOPY NUMBER,
3486 x_msg_data OUT NOCOPY VARCHAR2
3487 ) IS
3488
3489 l_party_id NUMBER;
3490 l_directional_flag VARCHAR2(1);
3491 l_direction_code VARCHAR2(1);
3492 l_debug_prefix VARCHAR2(30) := '';
3493
3494 BEGIN
3495
3496 -- Check if API is called in debug mode. If yes, enable debug.
3497 --enable_debug;
3498
3499 -- Debug info.
3500 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3501 hz_utility_v2pub.debug(p_message=>'get_relationship_rec (+)',
3502 p_prefix=>l_debug_prefix,
3503 p_msg_level=>fnd_log.level_procedure);
3504 END IF;
3505
3506
3507 --Initialize message list if p_init_msg_list is set to TRUE.
3508 IF FND_API.to_Boolean(p_init_msg_list) THEN
3509 FND_MSG_PUB.initialize;
3510 END IF;
3511
3512 --Initialize API return status to success.
3513 x_return_status := FND_API.G_RET_STS_SUCCESS;
3514
3515 --Check whether primary key has been passed in.
3516 IF p_relationship_id IS NULL OR
3517 p_relationship_id = FND_API.G_MISS_NUM THEN
3518 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
3519 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'relationship_id' );
3520 FND_MSG_PUB.ADD;
3521 RAISE FND_API.G_EXC_ERROR;
3522 END IF;
3523
3524 x_rel_rec.relationship_id := p_relationship_id;
3525 IF p_directional_flag <> 'F'
3526 AND
3527 p_directional_flag <> 'B'
3528 THEN
3529 l_directional_flag := 'F';
3530 ELSE
3531 l_directional_flag := NVL(p_directional_flag, 'F');
3532 END IF;
3533
3534 -- Debug info.
3535 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3536 hz_utility_v2pub.debug(p_message=>'HZ_RELATIONSHIPS_PKG.Select_Row',
3537 p_prefix =>l_debug_prefix,
3538 p_msg_level=>fnd_log.level_statement);
3539 END IF;
3540
3541 HZ_RELATIONSHIPS_PKG.Select_Row (
3542 X_RELATIONSHIP_ID => x_rel_rec.relationship_id,
3543 X_DIRECTIONAL_FLAG => l_directional_flag,
3544 X_SUBJECT_ID => x_rel_rec.subject_id,
3545 X_SUBJECT_TYPE => x_rel_rec.subject_type,
3546 X_SUBJECT_TABLE_NAME => x_rel_rec.subject_table_name,
3547 X_OBJECT_ID => x_rel_rec.object_id,
3548 X_OBJECT_TYPE => x_rel_rec.object_type,
3549 X_OBJECT_TABLE_NAME => x_rel_rec.object_table_name,
3550 X_PARTY_ID => l_party_id,
3551 X_RELATIONSHIP_CODE => x_rel_rec.relationship_code,
3552 X_COMMENTS => x_rel_rec.comments,
3553 X_START_DATE => x_rel_rec.start_date,
3554 X_END_DATE => x_rel_rec.end_date,
3555 X_STATUS => x_rel_rec.status,
3556 X_ATTRIBUTE_CATEGORY => x_rel_rec.attribute_category,
3557 X_ATTRIBUTE1 => x_rel_rec.attribute1,
3558 X_ATTRIBUTE2 => x_rel_rec.attribute2,
3559 X_ATTRIBUTE3 => x_rel_rec.attribute3,
3560 X_ATTRIBUTE4 => x_rel_rec.attribute4,
3561 X_ATTRIBUTE5 => x_rel_rec.attribute5,
3562 X_ATTRIBUTE6 => x_rel_rec.attribute6,
3563 X_ATTRIBUTE7 => x_rel_rec.attribute7,
3564 X_ATTRIBUTE8 => x_rel_rec.attribute8,
3565 X_ATTRIBUTE9 => x_rel_rec.attribute9,
3566 X_ATTRIBUTE10 => x_rel_rec.attribute10,
3567 X_ATTRIBUTE11 => x_rel_rec.attribute11,
3568 X_ATTRIBUTE12 => x_rel_rec.attribute12,
3569 X_ATTRIBUTE13 => x_rel_rec.attribute13,
3570 X_ATTRIBUTE14 => x_rel_rec.attribute14,
3571 X_ATTRIBUTE15 => x_rel_rec.attribute15,
3572 X_ATTRIBUTE16 => x_rel_rec.attribute16,
3573 X_ATTRIBUTE17 => x_rel_rec.attribute17,
3574 X_ATTRIBUTE18 => x_rel_rec.attribute18,
3575 X_ATTRIBUTE19 => x_rel_rec.attribute19,
3576 X_ATTRIBUTE20 => x_rel_rec.attribute20,
3577 X_CONTENT_SOURCE_TYPE => x_rel_rec.content_source_type,
3578 X_RELATIONSHIP_TYPE => x_rel_rec.relationship_type,
3579 X_CREATED_BY_MODULE => x_rel_rec.created_by_module,
3580 X_APPLICATION_ID => x_rel_rec.application_id,
3581 X_ADDITIONAL_INFORMATION1 => x_rel_rec.additional_information1,
3582 X_ADDITIONAL_INFORMATION2 => x_rel_rec.additional_information2,
3583 X_ADDITIONAL_INFORMATION3 => x_rel_rec.additional_information3,
3584 X_ADDITIONAL_INFORMATION4 => x_rel_rec.additional_information4,
3585 X_ADDITIONAL_INFORMATION5 => x_rel_rec.additional_information5,
3586 X_ADDITIONAL_INFORMATION6 => x_rel_rec.additional_information6,
3587 X_ADDITIONAL_INFORMATION7 => x_rel_rec.additional_information7,
3588 X_ADDITIONAL_INFORMATION8 => x_rel_rec.additional_information8,
3589 X_ADDITIONAL_INFORMATION9 => x_rel_rec.additional_information9,
3590 X_ADDITIONAL_INFORMATION10 => x_rel_rec.additional_information10,
3591 X_ADDITIONAL_INFORMATION11 => x_rel_rec.additional_information11,
3592 X_ADDITIONAL_INFORMATION12 => x_rel_rec.additional_information12,
3593 X_ADDITIONAL_INFORMATION13 => x_rel_rec.additional_information13,
3594 X_ADDITIONAL_INFORMATION14 => x_rel_rec.additional_information14,
3595 X_ADDITIONAL_INFORMATION15 => x_rel_rec.additional_information15,
3596 X_ADDITIONAL_INFORMATION16 => x_rel_rec.additional_information16,
3597 X_ADDITIONAL_INFORMATION17 => x_rel_rec.additional_information17,
3598 X_ADDITIONAL_INFORMATION18 => x_rel_rec.additional_information18,
3599 X_ADDITIONAL_INFORMATION19 => x_rel_rec.additional_information19,
3600 X_ADDITIONAL_INFORMATION20 => x_rel_rec.additional_information20,
3601 X_ADDITIONAL_INFORMATION21 => x_rel_rec.additional_information21,
3602 X_ADDITIONAL_INFORMATION22 => x_rel_rec.additional_information22,
3603 X_ADDITIONAL_INFORMATION23 => x_rel_rec.additional_information23,
3604 X_ADDITIONAL_INFORMATION24 => x_rel_rec.additional_information24,
3605 X_ADDITIONAL_INFORMATION25 => x_rel_rec.additional_information25,
3606 X_ADDITIONAL_INFORMATION26 => x_rel_rec.additional_information26,
3607 X_ADDITIONAL_INFORMATION27 => x_rel_rec.additional_information27,
3608 X_ADDITIONAL_INFORMATION28 => x_rel_rec.additional_information28,
3609 X_ADDITIONAL_INFORMATION29 => x_rel_rec.additional_information29,
3610 X_ADDITIONAL_INFORMATION30 => x_rel_rec.additional_information30,
3611 X_DIRECTION_CODE => l_direction_code,
3612 X_PERCENTAGE_OWNERSHIP => x_rel_rec.percentage_ownership,
3613 X_ACTUAL_CONTENT_SOURCE => x_rel_rec.actual_content_source
3614 );
3615
3616 -- Debug info.
3617 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3618 hz_utility_v2pub.debug(p_message=>'HZ_PARTY_V2PUB.get_party_rec',
3619 p_prefix =>l_debug_prefix,
3620 p_msg_level=>fnd_log.level_statement);
3621 END IF;
3622
3623 IF l_party_id IS NOT NULL
3624 AND
3625 l_party_id <> FND_API.G_MISS_NUM
3626 THEN
3627 HZ_PARTY_V2PUB.get_party_rec (
3628 p_party_id => l_party_id,
3629 x_party_rec => x_rel_rec.party_rec,
3630 x_return_status => x_return_status,
3631 x_msg_count => x_msg_count,
3632 x_msg_data => x_msg_data
3633 );
3634
3635 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3636 RAISE FND_API.G_EXC_ERROR;
3637 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3638 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3639 END IF;
3640 END IF;
3641
3642 --Standard call to get message count and if count is 1, get message info.
3643 FND_MSG_PUB.Count_And_Get(
3644 p_encoded => FND_API.G_FALSE,
3645 p_count => x_msg_count,
3646 p_data => x_msg_data );
3647
3648 -- Debug info.
3649 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
3650 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
3651 p_msg_data=>x_msg_data,
3652 p_msg_type=>'WARNING',
3653 p_msg_level=>fnd_log.level_exception);
3654 END IF;
3655 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3656 hz_utility_v2pub.debug(p_message=>'get_relationhsip_rec (-)',
3657 p_prefix=>l_debug_prefix,
3658 p_msg_level=>fnd_log.level_procedure);
3659 END IF;
3660
3661 -- Check if API is called in debug mode. If yes, disable debug.
3662 --disable_debug;
3663
3664 EXCEPTION
3665 WHEN FND_API.G_EXC_ERROR THEN
3666 x_return_status := FND_API.G_RET_STS_ERROR;
3667
3668 FND_MSG_PUB.Count_And_Get(
3669 p_encoded => FND_API.G_FALSE,
3670 p_count => x_msg_count,
3671 p_data => x_msg_data );
3672
3673 -- Debug info.
3674 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
3675 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
3676 p_msg_data=>x_msg_data,
3677 p_msg_type=>'ERROR',
3678 p_msg_level=>fnd_log.level_error);
3679
3680 END IF;
3681 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3682 hz_utility_v2pub.debug(p_message=>'get_relationsip_rec (-)',
3683 p_prefix=>l_debug_prefix,
3684 p_msg_level=>fnd_log.level_procedure);
3685 END IF;
3686
3687 -- Check if API is called in debug mode. If yes, disable debug.
3688 --disable_debug;
3689
3690 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3691 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3692
3693 FND_MSG_PUB.Count_And_Get(
3694 p_encoded => FND_API.G_FALSE,
3695 p_count => x_msg_count,
3696 p_data => x_msg_data );
3697
3698 -- Debug info.
3699 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
3700 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
3701 p_msg_data=>x_msg_data,
3702 p_msg_type=>'UNEXPECTED ERROR',
3703 p_msg_level=>fnd_log.level_error);
3704
3705 END IF;
3706 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3707 hz_utility_v2pub.debug(p_message=>'get_relationsip_rec (-)',
3708 p_prefix=>l_debug_prefix,
3709 p_msg_level=>fnd_log.level_procedure);
3710 END IF;
3711
3712 -- Check if API is called in debug mode. If yes, disable debug.
3713 --disable_debug;
3714
3715 WHEN OTHERS THEN
3716 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3717
3718 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
3719 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
3720 FND_MSG_PUB.ADD;
3721
3722 FND_MSG_PUB.Count_And_Get(
3723 p_encoded => FND_API.G_FALSE,
3724 p_count => x_msg_count,
3725 p_data => x_msg_data );
3726
3727 -- Debug info.
3728 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
3729 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
3730 p_msg_data=>x_msg_data,
3731 p_msg_type=>'SQL ERROR',
3732 p_msg_level=>fnd_log.level_error);
3733
3734 END IF;
3735 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3736 hz_utility_v2pub.debug(p_message=>'get_relationsip_rec (-)' ,
3737 p_prefix=>l_debug_prefix,
3738 p_msg_level=>fnd_log.level_procedure);
3739 END IF;
3740
3741
3742 -- Check if API is called in debug mode. If yes, disable debug.
3743 --disable_debug;
3744
3745 END get_relationship_rec;
3746
3747 END HZ_RELATIONSHIP_V2PUB;