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