1 PACKAGE BODY HZ_COPY_REL_PVT AS
2 /* $Header: ARHCPRLB.pls 120.19 2006/03/22 13:52:59 jgjoseph noship $ */
3
4 -- Bug 3615970: various fixes in convert_rel_type
5
6 TYPE t_indexed_number_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
7 TYPE t_indexed_varchar400_tbl IS TABLE OF VARCHAR2(400) INDEX BY BINARY_INTEGER;
8 TYPE t_varchar400_tbl IS TABLE OF VARCHAR2(400);
9 TYPE t_varchar4000_tbl IS TABLE OF VARCHAR2(4000);
10 TYPE t_number_tbl IS TABLE OF NUMBER;
11 TYPE t_date_tbl IS TABLE OF DATE;
12 TYPE t_varchar30_tbl IS TABLE OF VARCHAR2(30);
13 -- Bug 4288839
14 G_RET_CODE BOOLEAN := TRUE;
15
16 -- one relationship type can have relationships in different tables + object types.
17 -- we have to consider table_name + object_type in case of the same id has been
18 -- used in different combination of table_name + object_type
19
20 g_passed_nodes_tbl t_indexed_varchar400_tbl;
21 g_failed_nodes_tbl t_indexed_varchar400_tbl;
22 g_indexed_parent_nodes_tbl t_indexed_varchar400_tbl;
23 g_parent_nodes_tbl t_varchar400_tbl := t_varchar400_tbl();
24 g_relationship_id_tbl t_number_tbl := t_number_tbl();
25 g_message_fmt1 t_varchar4000_tbl := t_varchar4000_tbl();
26 g_message_fmt2 t_varchar4000_tbl := t_varchar4000_tbl();
27
28 --------------------------------------------
29 -- AUTHOR : COLATHUR VIJAYAN ("VJN")
30 --------------------------------------------
31
32 --------------------------------------------
33 -- a procedure to log messages
34 --------------------------------------------
35 PROCEDURE log(
36 message IN VARCHAR2,
37 newline IN BOOLEAN DEFAULT TRUE) IS
38 BEGIN
39 IF message = 'NEWLINE' THEN
40 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
41 ELSIF (newline) THEN
42 FND_FILE.put_line(fnd_file.log,message);
43 ELSE
44 FND_FILE.put_line(fnd_file.log,message);
45 END IF;
46 END log;
47
48 -----------------------------------------------------------------------
49 -- Function to fetch messages of the stack and log the error
50 -----------------------------------------------------------------------
51 PROCEDURE logerror(SQLERRM VARCHAR2 DEFAULT NULL)
52 IS
53 l_msg_data VARCHAR2(2000);
54 BEGIN
55 FND_MSG_PUB.Reset;
56 log('---------------------------');
57 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
58 l_msg_data := l_msg_data || FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE );
59 END LOOP;
60 IF (SQLERRM IS NOT NULL) THEN
61 l_msg_data := l_msg_data || SQLERRM;
62 END IF;
63 log(l_msg_data);
64 END logerror;
65
66
67
68 --------------------------------------------
69 -- get_meaning
70 --------------------------------------------
71
72 FUNCTION get_meaning
73 (p_lookup_code VARCHAR2 )
74 RETURN VARCHAR2
75 IS
76 CURSOR c0
77 IS
78 SELECT meaning
79 FROM ar_lookups
80 WHERE lookup_code = p_lookup_code
81 and lookup_type = 'HZ_RELATIONSHIP_ROLE'
82 and rownum = 1 ;
83 meaning VARCHAR2(200);
84 BEGIN
85 for cursor_rec in c0
86 loop
87 EXIT WHEN c0%NOTFOUND;
88 meaning := cursor_rec.meaning ;
89 end loop;
90 RETURN meaning;
91 END get_meaning ;
92
93 --------------------------------------------
94 -- get_description
95 --------------------------------------------
96
97 FUNCTION get_description
98 (p_lookup_code VARCHAR2 )
99 RETURN VARCHAR2
100 IS
101 CURSOR c0
102 IS
103 SELECT description
104 FROM ar_lookups
105 WHERE lookup_code = p_lookup_code
106 and lookup_type = 'HZ_RELATIONSHIP_ROLE'
107 and rownum = 1 ;
108 description VARCHAR2(200);
109 BEGIN
110 for cursor_rec in c0
111 loop
112 EXIT WHEN c0%NOTFOUND;
113 description := cursor_rec.description ;
114 end loop;
115 RETURN description;
116 END get_description ;
117
118
119 --------------------------------------------
120 -- create_lookup
121 -- Bug 3620141. Added parameter x_return_status.
122 -- Handled the scenario where user was passing the same meaning as that of an existing record.
123 --------------------------------------------
124
125 PROCEDURE create_lookup
126 (p_lookup_code VARCHAR2, p_lookup_meaning VARCHAR2, p_lookup_description VARCHAR2 , x_return_status IN OUT NOCOPY VARCHAR2)
127 IS
128 x_rowid varchar2(64);
129 l_count NUMBER := 0;
130 begin
131
132 /* Bug 3620141*/
133 SELECT COUNT(*)
134 INTO l_count
135 FROM FND_LOOKUP_VALUES
136 WHERE lookup_type = 'HZ_RELATIONSHIP_ROLE'
137 AND (meaning = p_lookup_meaning
138 OR description = p_lookup_description);
139 IF l_count <> 0
140 THEN
141 FND_MESSAGE.SET_NAME('AR', 'HZ_REL_TYPE_ROLE_MEANING_ERR');
142 FND_MSG_PUB.ADD;
143 x_return_status := fnd_api.g_ret_sts_error;
144 ELSE
145 BEGIN
146 FND_LOOKUP_VALUES_PKG.INSERT_ROW(
147 X_ROWID => x_rowid,
148 X_LOOKUP_TYPE => 'HZ_RELATIONSHIP_ROLE',
149 X_SECURITY_GROUP_ID => 0,
150 X_VIEW_APPLICATION_ID => 222,
151 X_LOOKUP_CODE => p_lookup_code,
152 X_TAG => null,
153 X_ATTRIBUTE_CATEGORY => null,
154 X_ATTRIBUTE1 => null,
155 X_ATTRIBUTE2 => null,
156 X_ATTRIBUTE3 => null,
157 X_ATTRIBUTE4 => null,
158 X_ENABLED_FLAG => 'Y',
159 X_START_DATE_ACTIVE => null,
160 X_END_DATE_ACTIVE => null,
161 X_TERRITORY_CODE => null,
162 X_ATTRIBUTE5 => null,
163 X_ATTRIBUTE6 => null,
164 X_ATTRIBUTE7 => null,
165 X_ATTRIBUTE8 => null,
166 X_ATTRIBUTE9 => null,
167 X_ATTRIBUTE10 => null,
168 X_ATTRIBUTE11 => null,
169 X_ATTRIBUTE12 => null,
170 X_ATTRIBUTE13 => null,
171 X_ATTRIBUTE14 => null,
172 X_ATTRIBUTE15 => null,
173 X_MEANING => p_lookup_meaning,
174 X_DESCRIPTION => p_lookup_description,
175 X_CREATION_DATE => HZ_UTILITY_V2PUB.CREATION_DATE,
176 X_CREATED_BY => HZ_UTILITY_V2PUB.CREATED_BY,
177 X_LAST_UPDATE_DATE => HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
178 X_LAST_UPDATED_BY => HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
179 X_LAST_UPDATE_LOGIN => HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN
180 );
181 EXCEPTION
182 WHEN OTHERS THEN
183 FND_MESSAGE.SET_NAME('AR', 'HZ_COPY_REL_API_ERROR');
184 FND_MESSAGE.SET_TOKEN('PROC' ,'CREATE_LOOKUP');
185 FND_MESSAGE.SET_TOKEN('ERROR' , SQLERRM);
186 FND_MSG_PUB.ADD;
187 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
188 END;
189 END IF;
190
191 end;
192
193 /**
194 * PRIVATE FUNCTION get_party_name
195 *
196 * DESCRIPTION
197 * added for bug fix 3615970
198 * return party name by given the party id.
199 *
200 * MODIFICATION HISTORY
201 *
202 * 05-30-2004 Jianying Huang o Created.
203 *
204 */
205
206 FUNCTION get_party_name (
207 p_party_id IN NUMBER
208 ) RETURN VARCHAR2 IS
209
210 CURSOR c_party IS
211 SELECT party_name
212 FROM hz_parties
213 WHERE party_id = p_party_id;
214
215 l_party_name VARCHAR2(400);
216
217 BEGIN
218
219 OPEN c_party;
220 FETCH c_party INTO l_party_name;
221 IF c_party%NOTFOUND THEN
222 l_party_name := 'Party Not Found';
223 END IF;
224 CLOSE c_party;
225
226 RETURN l_party_name;
227
228 END get_party_name;
229
230 /**
231 * PRIVATE PROCEDURE do_circularity_check
232 *
233 * DESCRIPTION
234 * added for bug fix 3615970
235 * do circularity check recursively.
236 *
237 * MODIFICATION HISTORY
238 *
239 * 05-30-2004 Jianying Huang o Created.
240 *
241 */
242
243 PROCEDURE do_circularity_check (
244 p_child_id IN NUMBER,
245 p_child_table_name IN VARCHAR2,
246 p_child_type IN VARCHAR2,
247 p_rel_type IN VARCHAR2,
248 p_start_date IN DATE,
249 p_end_date IN DATE,
250 x_return_status OUT NOCOPY VARCHAR2
251 ) IS
252
253 -- this cursor retrieves parents for a given child in a
254 -- particular hierarchy.
255
256 CURSOR c_parents IS
257 SELECT r.relationship_id,
258 r.subject_id,
259 r.subject_table_name,
260 r.subject_type,
261 r.start_date,
262 r.end_date
263 FROM hz_relationships r,
264 hz_relationship_types t
265 WHERE r.object_id = p_child_id
266 AND r.object_table_name = p_child_table_name
267 AND r.object_type = p_child_type
268 AND r.relationship_type = p_rel_type
269 AND r.relationship_type = t.relationship_type
270 AND r.relationship_code = t.forward_rel_code
271 AND r.subject_type = t.subject_type
272 AND r.object_type = t.object_type
273 AND t.direction_code = 'P'
274 AND (r.start_date BETWEEN NVL(p_start_date, SYSDATE)
275 AND NVL(p_end_date, TO_DATE('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS'))
276 OR
277 r.end_date BETWEEN NVL(p_start_date, SYSDATE)
278 AND NVL(p_end_date, TO_DATE('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS'))
279 OR
280 NVL(p_start_date, SYSDATE) BETWEEN r.start_date AND r.end_date
281 OR
282 NVL(p_end_date, TO_DATE('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS')) BETWEEN r.start_date AND r.end_date
283 );
284
285 i_relationship_id t_number_tbl;
286 i_parent_id t_number_tbl;
287 i_parent_table_name t_varchar30_tbl;
288 i_parent_type t_varchar30_tbl;
289 i_start_date t_date_tbl;
290 i_end_date t_date_tbl;
291
292 l_str VARCHAR2(400);
293 l_str1 VARCHAR2(400);
294 l_id NUMBER;
295 l_party_name VARCHAR2(400);
296 l_table_name VARCHAR2(30);
297
298 BEGIN
299
300 x_return_status := 'S';
301
302 l_str := p_child_id||'#'||p_child_table_name||'#'||p_child_type;
303
304 log('l_str = '||l_str);
305
306 -- record the parent path
307 --
308 g_parent_nodes_tbl.extend(1);
309 g_parent_nodes_tbl(g_parent_nodes_tbl.LAST) := l_str;
310
311 IF (NOT g_indexed_parent_nodes_tbl.EXISTS(p_child_id)) OR
312 (g_indexed_parent_nodes_tbl.EXISTS(p_child_id) AND
313 INSTRB(g_indexed_parent_nodes_tbl(p_child_id), l_str) = 0)
314 THEN
315 IF g_indexed_parent_nodes_tbl.EXISTS(p_child_id) THEN
316 l_str1 := g_indexed_parent_nodes_tbl(p_child_id)||',';
317 ELSE
318 l_str1 := '';
319 END IF;
320 g_indexed_parent_nodes_tbl(p_child_id) := l_str1||'-1#'||l_str;
321 END IF;
322
323 -- retrieve all of the parents
324 --
325 OPEN c_parents;
326 FETCH c_parents BULK COLLECT INTO
327 i_relationship_id,
328 i_parent_id,
329 i_parent_table_name,
330 i_parent_type,
331 i_start_date,
332 i_end_date;
333 CLOSE c_parents;
334
335 IF i_relationship_id.COUNT = 0 THEN
336 log('top parent ... ');
337 RETURN;
338 END IF;
339
340 -- loop for every parent
341 --
342 FOR i IN 1..i_relationship_id.COUNT LOOP
343
344 g_relationship_id_tbl.extend(1);
345 g_relationship_id_tbl(g_relationship_id_tbl.LAST) := i_relationship_id(i);
346
347 l_str1 := i_parent_id(i)||'#'||i_parent_table_name(i)||'#'||i_parent_type(i);
348
349 log('i_relationship_id('||i||') = '||i_relationship_id(i));
350 log(l_str1);
351
352 IF (g_passed_nodes_tbl.EXISTS(p_child_id) AND
353 INSTRB(g_passed_nodes_tbl(p_child_id), l_str) > 0) OR
354 (g_passed_nodes_tbl.EXISTS(i_parent_id(i)) AND
355 INSTRB(g_passed_nodes_tbl(i_parent_id(i)), l_str1) > 0)
356 THEN
357 log('case 1: in passed node table ...');
358
359 x_return_status := 'S';
360
361 ELSIF (g_failed_nodes_tbl.EXISTS(p_child_id) AND
362 INSTRB(g_failed_nodes_tbl(p_child_id), l_str) > 0) OR
363 (g_failed_nodes_tbl.EXISTS(i_parent_id(i)) AND
364 INSTRB(g_failed_nodes_tbl(i_parent_id(i)), l_str1) > 0)
365 THEN
366 log('case 2: in failed node table ...');
367
368 x_return_status := 'E';
369
370 ELSIF l_str = l_str1 THEN
371 log('case 3: self-related ...');
372
373 x_return_status := 'E';
374
375 -- prepare for message format 1
376 --
377 IF p_child_table_name = 'HZ_PARTIES' THEN
378 l_party_name := get_party_name(p_child_id);
379 ELSE
380 l_party_name := p_child_table_name||':'||p_child_id;
381 END IF;
382
383 g_message_fmt1.extend(1);
384 g_message_fmt1(g_message_fmt1.LAST) := l_party_name||' <- '||l_party_name;
385
386 -- prepare for message format 2
387 --
388 g_message_fmt2.extend(1);
389 g_message_fmt2(g_message_fmt2.LAST) :=
390 i_parent_table_name(i)||':'||i_parent_id(i)||' <- '||
391 i_relationship_id(i)||' <- '||
392 p_child_table_name||':'||p_child_id;
393
394 ELSIF (g_indexed_parent_nodes_tbl.EXISTS(i_parent_id(i)) AND
395 INSTRB(g_indexed_parent_nodes_tbl(i_parent_id(i)), '-1#'||l_str1) > 0)
396 THEN
397 log('case 4: in parent table ...');
398
399 x_return_status := 'E';
400
401 -- prepare for message format 1
402 --
403 IF i_parent_table_name(i) = 'HZ_PARTIES' THEN
404 l_party_name := get_party_name(i_parent_id(i));
405 ELSE
406 l_party_name := i_parent_table_name(i)||':'||i_parent_id(i);
407 END IF;
408
409 g_message_fmt1.extend(1);
410 g_message_fmt1(g_message_fmt1.LAST) := l_party_name||' <- ';
411
412 -- prepare for message format 2
413 --
414 g_message_fmt2.extend(1);
415 g_message_fmt2(g_message_fmt2.LAST) :=
416 i_parent_table_name(i)||':'||i_parent_id(i)||' <- '||
417 i_relationship_id(i)||' <- ';
418
422 LENGTHB(l_id)+2,
419 FOR j IN REVERSE 1..g_parent_nodes_tbl.COUNT LOOP
420 l_id := SUBSTRB(g_parent_nodes_tbl(j), 1, INSTRB(g_parent_nodes_tbl(j), '#')-1);
421 l_table_name := SUBSTRB(g_parent_nodes_tbl(j),
423 INSTRB(g_parent_nodes_tbl(j), '#', LENGTH(l_id)+2)-LENGTHB(l_id)-2);
424
425 IF INSTRB(g_indexed_parent_nodes_tbl(l_id), '-1#'||g_parent_nodes_tbl(j)) > 0 THEN
426 IF l_table_name = 'HZ_PARTIES' THEN
427 l_party_name := get_party_name(l_id);
428 ELSE
429 l_party_name := l_table_name||':'||l_id;
430 END IF;
431
432 g_message_fmt1(g_message_fmt1.LAST) :=
433 g_message_fmt1(g_message_fmt1.LAST)||l_party_name||fnd_global.NEWLINE;
434
435 g_message_fmt2(g_message_fmt2.LAST) :=
436 g_message_fmt2(g_message_fmt2.LAST)||l_table_name||':'||l_id||fnd_global.NEWLINE;
437 END IF;
438
439 IF l_str1 = g_parent_nodes_tbl(j) THEN
440 EXIT;
441 ELSIF INSTRB(g_indexed_parent_nodes_tbl(l_id), '-1#'||g_parent_nodes_tbl(j)) > 0 THEN
442 g_message_fmt1(g_message_fmt1.LAST) :=
443 g_message_fmt1(g_message_fmt1.LAST)||l_party_name||' <- ';
444
445 g_message_fmt2(g_message_fmt2.LAST) :=
446 g_message_fmt2(g_message_fmt2.LAST)||l_table_name||':'||l_id||' <- '||g_relationship_id_tbl(j-1)||' <- ';
447 END IF;
448 END LOOP;
449 ELSE
450 log('case 5: do_child_circularity ...');
451
452 do_circularity_check (
453 i_parent_id(i),
454 i_parent_table_name(i),
455 i_parent_type(i),
456 p_rel_type,
457 i_start_date(i),
458 i_end_date(i),
459 x_return_status
460 );
461 END IF;
462
463 log('x_return_status = '||x_return_status);
464
465 IF x_return_status <> 'S' THEN
466 RETURN;
467 ELSE
468 IF g_indexed_parent_nodes_tbl.EXISTS(i_parent_id(i)) THEN
469 g_indexed_parent_nodes_tbl(i_parent_id(i)) :=
470 REPLACE(g_indexed_parent_nodes_tbl(i_parent_id(i)),'-1#'||l_str1, '1#'||l_str1);
471 END IF;
472 END IF;
473 END LOOP;
474
475 END do_circularity_check;
476
477 ------------------------------
478 -- copy_relationships
479 -----------------------------
480
481 PROCEDURE copy_relationships
482 -- copy all the relationships from source rel type to des rel type, by calling the create relationship API.
483 (p_source_rel_type VARCHAR2, p_dest_rel_type VARCHAR2, p_rel_valid_date DATE,
484 x_return_status out NOCOPY VARCHAR2, x_msg_count out number, x_msg_data out VARCHAR2)
485 IS
486 /***** FOR CREATING RELATIONSHIPS **********/
487 p_relationship_rec HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE;
488 x_relationship_id NUMBER;
489 x_party_id NUMBER;
490 x_party_number VARCHAR2(2000);
491 cursor c0
492 IS
493 -- BASICALLY, GET ALL RELATIONSHIPS THAT ARE VALID ON P_VALID_DATE
494 -- IE., WHOSE END DATE IS EITHER NULL OR LATER THAN P_VALID_DATE
495 -- AND MORE IMPORTANTLY, THE START DATE CANNOT BE LATER THAN P_VALID_DATE
496
497 -- Bug 3651949: take care of copy in the same day as relationships created
498 --
499 select relationship_id
500 FROM hz_relationships r,
501 hz_relationship_types t
502 where r.relationship_type = p_source_rel_type
503 AND r.relationship_type = t.relationship_type
504 AND t.relationship_type = p_source_rel_type
505 AND r.relationship_code = t.forward_rel_code
506 AND r.subject_type = t.subject_type
507 AND r.object_type = t.object_type
508 AND (r.end_date is null or trunc(r.end_date) >= p_rel_valid_date)
509 AND trunc(r.start_date) <= p_rel_valid_date
510 AND directional_flag='F'
511 ;
512
513 l_message_text VARCHAR2(1000);
514 BEGIN
515
516 -- Bug 3651949: let system generate party number. this is to
517 -- avoid api error.
518 FND_PROFILE.PUT('HZ_GENERATE_PARTY_NUMBER', 'Y');
519
520 -- initialize return status
521 x_return_status := FND_API.G_RET_STS_SUCCESS;
522
523
524 FOR id_cur in c0
525 LOOP
526 -- GET RELATIONSHIP RECORD
527 hz_relationship_v2pub.get_relationship_rec(
528 FND_API.G_FALSE,
529 id_cur.relationship_id,
530 'F',
531 p_relationship_rec,
532 x_return_status,
533 x_msg_count,
534 x_msg_data
535 );
536
537 -- BEFORE CREATING THE IDENTICAL RELATIONSHIP MAKE SURE THAT THE APPROPRIATE
538 -- ATTRIBUTES ARE SET PROPERLY
539 p_relationship_rec.relationship_type := p_dest_rel_type;
540 p_relationship_rec.relationship_id := NULL;
541 p_relationship_rec.party_rec.party_id := NULL;
545 -- CALL RELATIONSHIP API
542 p_relationship_rec.party_rec.party_number := NULL;
543 p_relationship_rec.created_by_module := 'HZ_COPY_SCRIPT_CREATED';
544
546 hz_relationship_v2pub.create_relationship('T',p_relationship_rec,x_relationship_id,x_party_id,x_party_number,x_return_status,x_msg_count,x_msg_data,'');
547
548
549 -- RAISE HELL WHEN return status is not success
550
551 -- Bug 4288839.
552 -- Donot raise error when create relationship fails.
553 -- Instead log the errors in the log file and continue processing.
554 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
555
556 THEN
557 log('NEWLINE');
558 log('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
559
560 logerror;
561
562 log('NEWLINE');
563
564 log('Error while trying to copy the following source relationship:');
565 log('Source relationship type relationship_id -- '|| id_cur.relationship_id);
566 log('subject id -- ' || p_relationship_rec.subject_id );
567 log('object id -- ' || p_relationship_rec.object_id );
568
569 log('NEWLINE');
570
571 FND_MESSAGE.SET_NAME('AR', 'HZ_COPY_REL_API_ERROR');
572 FND_MESSAGE.SET_TOKEN('PROC' ,'HZ_RELATIONSHIP_V2PUB.CREATE_RELATIONSHIP');
573 FND_MESSAGE.SET_TOKEN('ERROR' , SQLERRM);
574 --FND_MSG_PUB.ADD;
575 l_message_text := FND_MESSAGE.GET;
576 log(l_message_text);
577
578 log('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
579 log('NEWLINE');
580
581 G_RET_CODE := FALSE;
582 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
583
584 END IF;
585
586
587 END LOOP;
588
589 EXCEPTION
590 WHEN OTHERS
591 THEN
592 FND_MESSAGE.SET_NAME('AR', 'HZ_COPY_REL_API_ERROR');
593 FND_MESSAGE.SET_TOKEN('PROC' ,'COPY_RELATIONSHIPS');
594 FND_MESSAGE.SET_TOKEN('ERROR' , SQLERRM);
595 FND_MSG_PUB.ADD;
596 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
597 END copy_relationships ;
598
599
600 --------------------------------------------------------------------------------------
601 -- copy_selected_phrase pairs ::: This will take 2 existing relationship types A,B
602 -- and copy specific phrase pairs from A to B.
603 -- HOWEVER, THIS PROCEDURE WILL NOT COPY RELATIONSHIPS UNDER A TO B.
604 --------------------------------------------------------------------------------------
605
606 PROCEDURE copy_selected_phrase_pair(p_source_rel_type VARCHAR2 , p_dest_rel_type VARCHAR2,
607 p_dest_rel_type_role_prefix VARCHAR2, p_dest_rel_type_role_suffix VARCHAR2,
608 p_forward_rel_code VARCHAR2, p_backward_rel_code VARCHAR2,
609 p_subject_type VARCHAR2, p_object_type VARCHAR2,
610 x_return_status OUT NOCOPY VARCHAR2, x_msg_count OUT NUMBER, x_msg_data OUT VARCHAR2)
611 IS
612 p_relationship_type_rec HZ_RELATIONSHIP_TYPE_V2PUB.RELATIONSHIP_TYPE_REC_TYPE;
613 x_relationship_type_id NUMBER;
614 temp1 varchar2(200);
615 temp2 varchar2(200);
616 forward_role varchar2(200);
617 backward_role varchar2(200);
618 temp number;
619
620 CREATE_LOOKUP_EXCEPTION EXCEPTION;
621 -- THE CURSOR HERE SHOULD FETCH ONLY THE ROWS WHOSE DIRECTION CODE IS EITHER 'P' OR 'N'
622 -- AFTER THE SELF JOIN AND CALL THE RELATIONSHIP TYPE API ONLY FOR THOSE ROWS.
623
624 CURSOR c_get_rel_type_record
625 IS
626 select hrt1.relationship_type as relationship_type,
627 hrt1.direction_code as direction_code,
628 hrt1.role as forward_role,
629 hrt2.role as backward_role,
630 hrt1.forward_rel_code as forward_rel_code,
631 hrt1.backward_rel_code as backward_rel_code,
632 hrt1.hierarchical_flag as hierarchical_flag,
633 hrt1.create_party_flag as create_party_flag,
634 hrt1.allow_relate_to_self_flag as allow_relate_to_self_flag,
635 hrt1.allow_circular_relationships as allow_circular_relationships,
636 hrt1.subject_type as subject_type,
637 hrt1.object_type as object_type,
638 hrt1.status as status,
639 hrt1.created_by_module as created_by_module,
640 hrt1.application_id as application_id,
641 hrt1.multiple_parent_allowed as multiple_parent_allowed,
642 hrt1.incl_unrelated_entities as incl_unrelated_entities
643 from hz_relationship_types hrt1, hz_relationship_types hrt2
644 where hrt1.relationship_type = hrt2.relationship_type
645 and hrt1.subject_type = hrt2.object_type
646 and hrt1.object_type = hrt2.subject_type
647 and hrt1.forward_rel_code = hrt2.backward_rel_code
648 and hrt1.backward_rel_code = hrt2.forward_rel_code
649 and (hrt1.direction_code = 'P' or hrt1.direction_code = 'N')
650 -- Constraints based on what is passed in to this function
651 and hrt1.relationship_type = p_source_rel_type
652 and hrt1.forward_rel_code = p_forward_rel_code
656
653 and hrt1.backward_rel_code = p_backward_rel_code
654 and hrt1.subject_type = p_subject_type
655 and hrt1.object_type = p_object_type ;
657
658 BEGIN
659 -- INITIALIZE RETURN STATUS
660 x_return_status := FND_API.G_RET_STS_SUCCESS;
661
662
663 -- initialize the temporary variable
664 temp := -1;
665
666
667 -- LOOP THROUGH THE CURSOR
668 FOR rel_type_record IN c_get_rel_type_record
669 LOOP
670
671 -- CREATE TWO LOOKUPS OR ONE LOOKUP, DEPENDING ON THE DIRECTION CODE.
672 -- THE ROLES WILL BE SYSTEM GENERATED IN ANY CASE.
673 -- WHEN DIRECTION IS NOT 'P', ONLY ONE ROLE NEEDS TO BE GENERATED.
674 IF rel_type_record.direction_code = 'P'
675 THEN
676 temp1 := p_dest_rel_type_role_prefix || get_meaning(rel_type_record.forward_role)
677 || p_dest_rel_type_role_suffix ;
678 temp2 := p_dest_rel_type_role_prefix || get_description(rel_type_record.forward_role)
679 || p_dest_rel_type_role_suffix ;
680
681 -- if first time in the loop, temp is current time
682 -- else it is incremented by 1
683 IF temp = -1
684 THEN
685 temp := dbms_utility.get_time;
686 ELSE
687 temp := temp + 1;
688 END IF;
689
690 forward_role := 'USER_ROLE'|| to_char(temp) ;
691
692 -- dbms_output.put_line('forward role is' || forward_role);
693 -- dbms_output.put_line('meaning is' || temp1);
694 -- dbms_output.put_line('description is' || temp2);
695 log('-------------------------------------------');
696 log('forward role code is ' || forward_role);
697 log('meaning is ' || temp1);
698 log('description is ' || temp2);
699 create_lookup(forward_role, temp1, temp2,x_return_status);
700 /* Bug 3620141 */
701 if (x_return_status <> FND_API.G_RET_STS_SUCCESS )
702 THEN
703 RAISE CREATE_LOOKUP_EXCEPTION;
704 END IF;
705 temp1 := p_dest_rel_type_role_prefix || get_meaning(rel_type_record.backward_role)
706 || p_dest_rel_type_role_suffix ;
707 temp2 := p_dest_rel_type_role_prefix || get_description(rel_type_record.backward_role)
708 || p_dest_rel_type_role_suffix ;
709
710 -- always increment by 1
711 temp := temp + 1;
712 backward_role := 'USER_ROLE'|| to_char(temp) ;
713
714 -- dbms_output.put_line('backward role is' || backward_role);
715 -- dbms_output.put_line('meaning is' || temp1);
716 -- dbms_output.put_line('description is' || temp2);
717 log('-------------------------------------------');
718 log('backward role code is ' || backward_role);
719 log('meaning is ' || temp1);
720 log('description is ' || temp2);
721 create_lookup(backward_role, temp1, temp2,x_return_status);
722 /* Bug 3620141 */
723 if (x_return_status <> FND_API.G_RET_STS_SUCCESS )
724 THEN
725 RAISE CREATE_LOOKUP_EXCEPTION;
726 END IF;
727
728 ELSE
729 temp1 := p_dest_rel_type_role_prefix || get_meaning(rel_type_record.forward_role)
730 || p_dest_rel_type_role_suffix ;
731 temp2 := p_dest_rel_type_role_prefix || get_description(rel_type_record.forward_role)
732 || p_dest_rel_type_role_suffix ;
733
734 -- if first time in the loop, temp is current time
735 -- else it is incremented by 1
736 IF temp = -1
737 THEN
738 temp := dbms_utility.get_time;
739 ELSE
740 temp := temp + 1;
741 END IF;
742
743 log('-------------------------------------------');
744 log('forward role code = backward role code = ' || forward_role);
745 log('meaning is ' || temp1);
746 log('description is ' || temp2);
747 create_lookup(forward_role, temp1, temp2,x_return_status);
748 /* Bug 3620141 */
749 if (x_return_status <> FND_API.G_RET_STS_SUCCESS)
750 THEN
754
751 RAISE CREATE_LOOKUP_EXCEPTION;
752 END IF;
753 backward_role := forward_role;
755 END IF;
756
757
758 -- CREATE THE NEW RELATIONSHIP TYPE WITH THE CORRESPONDING ROLE - PHRASE PAIR
759 p_relationship_type_rec.relationship_type_id := NULL;
760 p_relationship_type_rec.relationship_type := p_dest_rel_type ;
761 p_relationship_type_rec.forward_rel_code := rel_type_record.forward_rel_code ;
762 p_relationship_type_rec.backward_rel_code := rel_type_record.backward_rel_code;
763 p_relationship_type_rec.direction_code := rel_type_record.direction_code;
764 p_relationship_type_rec.hierarchical_flag := rel_type_record.hierarchical_flag ;
765 p_relationship_type_rec.create_party_flag := rel_type_record.create_party_flag;
766 p_relationship_type_rec.allow_relate_to_self_flag := rel_type_record.allow_relate_to_self_flag ;
767 p_relationship_type_rec.allow_circular_relationships := rel_type_record.allow_circular_relationships ;
768 p_relationship_type_rec.subject_type := rel_type_record.subject_type ;
769 p_relationship_type_rec.object_type := rel_type_record.object_type;
770 p_relationship_type_rec.status := rel_type_record.status ;
771 p_relationship_type_rec.created_by_module := 'HZ_COPY_SCRIPT_CREATED' ;
772 p_relationship_type_rec.application_id := NULL;
773 p_relationship_type_rec.multiple_parent_allowed := rel_type_record.multiple_parent_allowed ;
774 p_relationship_type_rec.incl_unrelated_entities := rel_type_record.incl_unrelated_entities ;
775 p_relationship_type_rec.forward_role := forward_role ;
776 p_relationship_type_rec.backward_role := backward_role;
777
778 hz_relationship_type_v2pub.create_relationship_type('T',p_relationship_type_rec,
779 x_relationship_type_id,x_return_status,
780 x_msg_count,x_msg_data);
781
782 -- RAISE HELL WHEN return status is not success
783 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
784 THEN
785 FND_MESSAGE.SET_NAME('AR', 'HZ_COPY_REL_API_ERROR');
786 FND_MESSAGE.SET_TOKEN('PROC' ,'HZ_RELATIONSHIP_TYPE_V2PUB.CREATE_RELATIONSHIP_TYPE');
787 FND_MESSAGE.SET_TOKEN('ERROR' , SQLERRM);
788 FND_MSG_PUB.ADD;
789 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
790 END IF;
791
792 END LOOP;
793
794
795
796
797
798 EXCEPTION
799 WHEN CREATE_LOOKUP_EXCEPTION THEN
800 FND_MSG_PUB.COUNT_AND_GET(
801 p_encoded => FND_API.G_FALSE,
802 p_count => x_msg_count,
803 p_data => x_msg_data
804 );
805 WHEN OTHERS THEN
806 FND_MESSAGE.SET_NAME('AR', 'HZ_COPY_REL_API_ERROR');
807 FND_MESSAGE.SET_TOKEN('PROC' ,'COPY_SELECTED_PHRASE_PAIR');
808 FND_MESSAGE.SET_TOKEN('ERROR' , SQLERRM);
809 FND_MSG_PUB.ADD;
810 FND_MSG_PUB.COUNT_AND_GET(
811 p_encoded => FND_API.G_FALSE,
812 p_count => x_msg_count,
813 p_data => x_msg_data
814 );
815 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
816
817 END ;
818
819 -------------------------------------------------------------------------------------
820 -- create_hierarchy ::: The recursive procedure, that will take the passed in party_id
821 -- find its immediate child and create the appropriate relationship
822 -- in dest_rel_type.
823 ----------------------------------------------------------------------------------------
824
825 PROCEDURE create_hierarchy(p_party_id NUMBER, p_source_rel_type VARCHAR2, p_dest_rel_type VARCHAR2,
826 p_dest_rel_type_role_prefix VARCHAR2, p_dest_rel_type_role_suffix VARCHAR2,
827 p_rel_valid_date DATE, x_return_status OUT NOCOPY VARCHAR2, x_msg_count OUT NUMBER,
828 x_msg_data OUT NOCOPY VARCHAR2)
829 IS
830 /***** FOR CREATING RELATIONSHIPS **********/
831 p_relationship_rec HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE;
832 p_child NUMBER;
833 x_relationship_id NUMBER;
834 x_party_id NUMBER;
835 x_party_number VARCHAR2(2000);
836
837 -- this cursor will get the relationship id for the relationship
838 -- in which the passed in party id is the parent
839 cursor c0
840 IS
841 select relationship_id
842 from hz_relationships
843 where subject_id = p_party_id
844 and relationship_type = p_source_rel_type
845 and direction_code = 'P'
846 and (end_date is null or end_date > p_rel_valid_date )
850 x_return_status := FND_API.G_RET_STS_SUCCESS;
847 and start_date < p_rel_valid_date ;
848 BEGIN
849 -- INITIALIZE RETURN STATUS
851
852 FOR id_rec in c0
853 LOOP
854 -- GET RELATIONSHIP RECORD
855 hz_relationship_v2pub.get_relationship_rec(
856 FND_API.G_FALSE,
857 id_rec.relationship_id,
858 'F',
859 p_relationship_rec,
860 x_return_status,
861 x_msg_count,
862 x_msg_data
863 );
864
865 -- BEFORE CREATING THE IDENTICAL RELATIONSHIP MAKE SURE THAT THE APPROPRIATE
866 -- ATTRIBUTES ARE SET PROPERLY
867 p_relationship_rec.relationship_type := p_dest_rel_type;
868 p_relationship_rec.relationship_id := NULL;
869 p_relationship_rec.party_rec.party_id := NULL;
870 p_relationship_rec.party_rec.party_number := NULL;
871 p_relationship_rec.created_by_module := 'HZ_COPY_SCRIPT_CREATED';
872
873 log('-----------------------------------------------------------');
874 log('In create hierarchy: Parent = ' || p_relationship_rec.subject_id ||
875 ' Child = ' || p_relationship_rec.object_id);
876 log('Relationship Id = ' || p_relationship_rec.relationship_id );
877 log('Relationship = ' || p_relationship_rec.relationship_code );
878 log('About to create relationship');
879 -- CALL RELATIONSHIP API
880 hz_relationship_v2pub.create_relationship('T',p_relationship_rec,x_relationship_id,
881 x_party_id,x_party_number,x_return_status,x_msg_count,x_msg_data,'');
882
883
884 IF x_return_status = FND_API.G_RET_STS_SUCCESS
885 THEN
886 -- this is the tricky bit, the child could be either the subject or the object.
887 -- in any case, the child should be the id in the record, which is not the parent.
888 IF p_relationship_rec.object_id = p_party_id
889 THEN
890 p_child := p_relationship_rec.subject_id ;
891 ELSE
892 p_child := p_relationship_rec.object_id ;
893
894 END IF;
895
896
897 -- RECURSION ::: PASS IT ON TO THE CHILD
898 create_hierarchy(p_child, p_source_rel_type, p_dest_rel_type,
899 p_dest_rel_type_role_prefix, p_dest_rel_type_role_suffix, p_rel_valid_date,
900 x_return_status, x_msg_count, x_msg_data);
901
902 -- RAISE HELL WHEN return status is not success
903 -- Bug 4288839. Donot raise hell. Log all errors and continue processing forward.
904
905 ELSE
906 DECLARE
907 l_message_text VARCHAR2(400);
908 BEGIN
909 log('NEWLINE');
910 log('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
911
912 logerror;
913
914 log('NEWLINE');
915
916 log('Error while trying to copy the following source relationship:');
917 log('Source relationship type relationship_id -- '|| id_rec.relationship_id);
918 log('subject id -- ' || p_relationship_rec.subject_id );
919 log('object id -- ' || p_relationship_rec.object_id );
920
921 log('NEWLINE');
922
923 FND_MESSAGE.SET_NAME('AR', 'HZ_COPY_REL_API_ERROR');
924 FND_MESSAGE.SET_TOKEN('PROC' ,'HZ_RELATIONSHIP_V2PUB.CREATE_RELATIONSHIP');
925 FND_MESSAGE.SET_TOKEN('ERROR' , SQLERRM);
926 -- FND_MSG_PUB.ADD;
927 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
928 l_message_text := FND_MESSAGE.GET;
929 log(l_message_text);
930
931 log('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
932 log('NEWLINE');
933
934 G_RET_CODE := FALSE;
935
936 END;
937
938 END IF;
939
940
941
942 END LOOP;
943
944
945 EXCEPTION
946 WHEN OTHERS THEN
947 FND_MESSAGE.SET_NAME('AR', 'HZ_COPY_REL_API_ERROR');
948 FND_MESSAGE.SET_TOKEN('PROC' ,'CREATE_HIERARCHY');
949 FND_MESSAGE.SET_TOKEN('ERROR' , SQLERRM);
950 FND_MSG_PUB.ADD;
951 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
952 END create_hierarchy;
953
954
955 --------------------------------------------------------------------------------------
956 -- copy_rel_type_only ::: This will take a relationship type A and create a relationship
957 -- type B, which is a copy of A in the following sense:
961 --
958 -- B will be identical to A in terms of all the properties( phrase pairs,hierarchical type,
959 -- circular flag etc., as seen in HZ_RELATIONSHIP_TYPES), that are
960 -- associated with any relationship type.
962 -- HOWEVER, THIS PROCEDURE WILL NOT COPY RELATIONSHIPS UNDER A TO B.
963 --------------------------------------------------------------------------------------
964
965 PROCEDURE copy_rel_type_only (
966 -- in parameters
967 p_source_rel_type IN VARCHAR2
968 ,p_dest_rel_type IN VARCHAR2
969 ,p_dest_rel_type_role_prefix IN VARCHAR2
970 ,p_dest_rel_type_role_suffix IN VARCHAR2
971 -- out NOCOPY parameters
972 ,x_return_status OUT NOCOPY VARCHAR2
973 ,x_msg_count OUT NOCOPY NUMBER
974 ,x_msg_data OUT NOCOPY VARCHAR2
975 )
976 IS
977 p_relationship_type_rec HZ_RELATIONSHIP_TYPE_V2PUB.RELATIONSHIP_TYPE_REC_TYPE;
978 x_relationship_type_id NUMBER;
979 temp1 varchar2(200);
980 temp2 varchar2(200);
981 forward_role varchar2(200);
982 backward_role varchar2(200);
983 ret_value number;
984 temp number;
985 -- THE CURSOR HERE SHOULD FETCH ONLY THE ROWS WHOSE DIRECTION CODE IS EITHER 'P' OR 'N'
986 -- AFTER THE SELF JOIN AND CALL THE RELATIONSHIP TYPE API ONLY FOR THOSE ROWS.
987 -- NEED TO ADD MORE ATTRIBUTES FROM hrt1 TO THE SELECT IN QUERY.
988 CREATE_LOOKUP_EXCEPTION EXCEPTION;
989
990 CURSOR c_get_rel_type_record
991 IS
992 select hrt1.relationship_type as relationship_type,
993 hrt1.direction_code as direction_code,
994 hrt1.role as forward_role,
995 hrt2.role as backward_role,
996 hrt1.forward_rel_code as forward_rel_code,
997 hrt1.backward_rel_code as backward_rel_code,
998 hrt1.hierarchical_flag as hierarchical_flag,
999 hrt1.create_party_flag as create_party_flag,
1000 hrt1.allow_relate_to_self_flag as allow_relate_to_self_flag,
1001 hrt1.allow_circular_relationships as allow_circular_relationships,
1002 hrt1.subject_type as subject_type,
1003 hrt1.object_type as object_type,
1004 hrt1.status as status,
1005 hrt1.created_by_module as created_by_module,
1006 hrt1.application_id as application_id,
1007 hrt1.multiple_parent_allowed as multiple_parent_allowed,
1008 hrt1.incl_unrelated_entities as incl_unrelated_entities
1009 from hz_relationship_types hrt1, hz_relationship_types hrt2
1010 where hrt1.relationship_type = hrt2.relationship_type
1011 and hrt1.subject_type = hrt2.object_type
1012 and hrt1.object_type = hrt2.subject_type
1013 and hrt1.forward_rel_code = hrt2.backward_rel_code
1014 and hrt1.backward_rel_code = hrt2.forward_rel_code
1015 and (hrt1.direction_code = 'P' or hrt1.direction_code = 'N')
1016 and hrt1.relationship_type = p_source_rel_type ;
1017
1018
1019 BEGIN
1020 savepoint copy_rel_type_only ;
1021 -- INITIALIZE RETURN STATUS
1022 x_return_status := FND_API.G_RET_STS_SUCCESS;
1023
1024 -- initialize the temporary variable
1025 temp := -1;
1026
1027 -- LOOP THROUGH THE CURSOR
1028 FOR rel_type_record IN c_get_rel_type_record
1029 LOOP
1030
1031 -- CREATE TWO LOOKUPS OR ONE LOOKUP, DEPENDING ON THE DIRECTION CODE.
1032 -- THE ROLES WILL BE SYSTEM GENERATED IN ANY CASE.
1033 -- WHEN DIRECTION IS 'P', CREATE TWO LOOKUPS ONE FOR EACH ROLE.
1034 -- WHEN DIRECTION IS 'N', CREATE ONE LOOKUP AND USE IT FOR BOTH FORWARD AND BACKWARD ROLES.
1035 IF rel_type_record.direction_code = 'P'
1036 THEN
1037 temp1 := p_dest_rel_type_role_prefix || get_meaning(rel_type_record.forward_role)
1038 || p_dest_rel_type_role_suffix ;
1039 temp2 := p_dest_rel_type_role_prefix || get_description(rel_type_record.forward_role)
1040 || p_dest_rel_type_role_suffix ;
1041
1042 -- if first time in the loop, temp is current time
1043 -- else it is incremented by 1
1044 IF temp = -1
1045 THEN
1046 temp := dbms_utility.get_time;
1047 ELSE
1048 temp := temp + 1;
1049 END IF;
1050
1051 forward_role := 'USER_ROLE'|| to_char(temp) ;
1052
1053 -- dbms_output.put_line('forward role is' || forward_role);
1054 -- dbms_output.put_line('meaning is' || temp1);
1055 -- dbms_output.put_line('description is' || temp2);
1056 -- dbms_output.put_line('forward_role is' || forward_role );
1057 log('-------------------------------------------');
1058 log('forward role code is ' || forward_role);
1059 log('meaning is ' || temp1);
1060 log('description is ' || temp2);
1061 create_lookup(forward_role, temp1, temp2,x_return_status);
1062 /* Bug 3620141 */
1066 END IF;
1063 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS )
1064 THEN
1065 RAISE CREATE_LOOKUP_EXCEPTION;
1067
1068 temp1 := p_dest_rel_type_role_prefix || get_meaning(rel_type_record.backward_role)
1069 || p_dest_rel_type_role_suffix ;
1070 temp2 := p_dest_rel_type_role_prefix || get_description(rel_type_record.backward_role)
1071
1072 || p_dest_rel_type_role_suffix ;
1073
1074 -- always increment by 1
1075 temp := temp + 1;
1076 backward_role := 'USER_ROLE'|| to_char(temp) ;
1077
1078 -- dbms_output.put_line('backward role is' || backward_role);
1079 -- dbms_output.put_line('meaning is' || temp1);
1080 -- dbms_output.put_line('description is' || temp2);
1081 -- dbms_output.put_line('backward_role is' || backward_role );
1082 log('-------------------------------------------');
1083 log('backward role code is ' || backward_role);
1084 log('meaning is ' || temp1);
1085 log('description is ' || temp2);
1086 create_lookup(backward_role, temp1, temp2, x_return_status);
1087 /* Bug 3620141 */
1088 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS )
1089 THEN
1090 RAISE CREATE_LOOKUP_EXCEPTION;
1091 END IF;
1092
1093 ELSIF rel_type_record.direction_code = 'N'
1094 THEN
1095 temp1 := p_dest_rel_type_role_prefix || get_meaning(rel_type_record.forward_role)
1096 || p_dest_rel_type_role_suffix ;
1097 temp2 := p_dest_rel_type_role_prefix || get_description(rel_type_record.forward_role)
1098 || p_dest_rel_type_role_suffix ;
1099
1100
1101 -- if first time in the loop, temp is current time
1102 -- else it is incremented by 1
1103 IF temp = -1
1104 THEN
1105 temp := dbms_utility.get_time;
1106 ELSE
1107 temp := temp + 1;
1108 END IF;
1109
1110 forward_role := 'USER_ROLE'|| to_char(temp) ;
1111
1112 -- dbms_output.put_line('fackward role and backward_role is' || forward_role);
1113 -- dbms_output.put_line('meaning is' || temp1);
1114 -- dbms_output.put_line('description is' || temp2);
1115 -- dbms_output.put_line('forward_role is' || forward_role );
1116 log('-------------------------------------------');
1117 log('forward role code = backward role code = ' || forward_role);
1118 log('meaning is ' || temp1);
1119 log('description is ' || temp2);
1120 create_lookup(forward_role, temp1, temp2,x_return_status);
1121 /* Bug 3620141 */
1122 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS )
1123 THEN
1124 RAISE CREATE_LOOKUP_EXCEPTION;
1125 END IF;
1126 backward_role := forward_role;
1127 END IF;
1128
1129
1130 -- CREATE THE NEW RELATIONSHIP TYPE WITH THE CORRESPONDING ROLE - PHRASE PAIR
1131 p_relationship_type_rec.relationship_type_id := NULL;
1132 p_relationship_type_rec.relationship_type := p_dest_rel_type ;
1133 p_relationship_type_rec.forward_rel_code := rel_type_record.forward_rel_code ;
1134 p_relationship_type_rec.backward_rel_code := rel_type_record.backward_rel_code;
1135 p_relationship_type_rec.direction_code := rel_type_record.direction_code;
1136 p_relationship_type_rec.hierarchical_flag := rel_type_record.hierarchical_flag ;
1137 p_relationship_type_rec.create_party_flag := rel_type_record.create_party_flag;
1138 p_relationship_type_rec.allow_relate_to_self_flag := rel_type_record.allow_relate_to_self_flag ;
1139 p_relationship_type_rec.allow_circular_relationships := rel_type_record.allow_circular_relationships ;
1140 p_relationship_type_rec.subject_type := rel_type_record.subject_type ;
1141 p_relationship_type_rec.object_type := rel_type_record.object_type;
1142 p_relationship_type_rec.status := rel_type_record.status ;
1143 p_relationship_type_rec.created_by_module := 'HZ_COPY_SCRIPT_CREATED' ;
1144 p_relationship_type_rec.application_id := NULL;
1145 p_relationship_type_rec.multiple_parent_allowed := rel_type_record.multiple_parent_allowed ;
1146 p_relationship_type_rec.incl_unrelated_entities := rel_type_record.incl_unrelated_entities ;
1147 p_relationship_type_rec.forward_role := forward_role ;
1148 p_relationship_type_rec.backward_role := backward_role;
1152 x_msg_count,x_msg_data);
1149
1150 hz_relationship_type_v2pub.create_relationship_type('T',p_relationship_type_rec,
1151 x_relationship_type_id,x_return_status,
1153 -- RAISE HELL WHEN return status is not success
1154 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1155 THEN
1156 FND_MESSAGE.SET_NAME('AR', 'HZ_COPY_REL_API_ERROR');
1157 FND_MESSAGE.SET_TOKEN('PROC' ,'HZ_RELATIONSHIP_TYPE_V2PUB.CREATE_RELATIONSHIP_TYPE');
1158 FND_MESSAGE.SET_TOKEN('ERROR' , SQLERRM);
1159 FND_MSG_PUB.ADD;
1160 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1161 END IF;
1162
1163 END LOOP;
1164
1165
1166 EXCEPTION
1167 WHEN CREATE_LOOKUP_EXCEPTION THEN
1168 ROLLBACK TO copy_rel_type_only;
1169 x_return_status := FND_API.G_RET_STS_ERROR;
1170 FND_MSG_PUB.Count_And_Get(
1171 p_encoded => FND_API.G_FALSE,
1172 p_count => x_msg_count,
1173 p_data => x_msg_data);
1174
1175
1176 WHEN OTHERS THEN
1177 rollback to copy_rel_type_only ;
1178 x_return_status := FND_API.G_RET_STS_ERROR;
1179 FND_MESSAGE.SET_NAME('AR', 'HZ_COPY_REL_API_ERROR');
1180 FND_MESSAGE.SET_TOKEN('PROC' ,'COPY_REL_TYPE_ONLY');
1181 FND_MESSAGE.SET_TOKEN('ERROR' , SQLERRM);
1182 FND_MSG_PUB.ADD;
1183 FND_MSG_PUB.Count_And_Get(
1184 p_encoded => FND_API.G_FALSE,
1185 p_count => x_msg_count,
1186 p_data => x_msg_data);
1187
1188 END copy_rel_type_only ;
1189
1190
1191
1192
1193
1194 --------------------------------------------------------------------------------------
1195 -- copy_rel_type_and_all_rels ::: This will take a relationship type A and create a relationship
1196 -- type B, which is a copy of A in the following sense:
1197 -- 1. B will be identical to A in terms of all the properties( hierarchical type, circular flag
1198 -- etc., as seen in HZ_RELATIONSHIP_TYPES), that are
1199 -- associated with any relationship type.
1200 -- 2. ALL THE RELATIONSHIPS UNDER A WILL BE CREATED UNDER B.
1201 --------------------------------------------------------------------------------------
1202
1203 PROCEDURE copy_rel_type_and_all_rels (
1204 errbuf OUT NOCOPY VARCHAR2
1205 ,Retcode OUT NOCOPY VARCHAR2
1206 ,p_source_rel_type IN VARCHAR2
1207 ,p_dest_rel_type IN VARCHAR2
1208 ,p_dest_rel_type_role_prefix IN VARCHAR2
1209 ,p_dest_rel_type_role_suffix IN VARCHAR2
1210 ,p_rel_valid_date IN DATE
1211 )
1212 IS
1213 x_return_status VARCHAR2(1);
1214 x_msg_count NUMBER;
1215 x_msg_data VARCHAR2(2000);
1216 BEGIN
1217
1218 savepoint copy_rel_type_and_all_rels ;
1219
1220 G_RET_CODE := TRUE;
1221
1222 -- return is status unless otherwise changed
1223 x_return_status := FND_API.G_RET_STS_SUCCESS;
1224
1225 log('-------------------------------------------');
1226 log('Passed in Source Rel Type is ' || p_source_rel_type );
1227 log('Passed in Destination Rel Type is ' || p_dest_rel_type );
1228 log('Passed in Prefix is ' || p_dest_rel_type_role_prefix );
1229 log('Passed in Suffix is ' || p_dest_rel_type_role_suffix );
1230 log('Passed in Date is ' || p_rel_valid_date );
1231
1232 -- CREATE RELATIONSHIP TYPE FIRST
1233
1234 copy_rel_type_only(p_source_rel_type,p_dest_rel_type,p_dest_rel_type_role_prefix,
1235 p_dest_rel_type_role_suffix,
1236 x_return_status, x_msg_count, x_msg_data );
1237
1238
1239
1240
1241 -- RAISE HELL WHEN return status is not success
1242 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1243 THEN
1244 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1245 END IF;
1246
1247
1248 -- NOW, CREATE ALL THE RELATIONSHIPS FOR THE NEWLY CREATED RELATIONSHIP TYPE
1249 copy_relationships(p_source_rel_type, p_dest_rel_type, nvl(p_rel_valid_date,SYSDATE),
1250 x_return_status, x_msg_count, x_msg_data);
1251
1252 -- Bug 4288839
1253 -- If G_RET_CODE IS FALSE, then it means that some relationships could not be created.
1254 -- Error out the concurrent program and rollback all changes.
1255
1256
1257 -- RAISE HELL WHEN return status is not success
1258 /* IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1259 THEN
1263
1260 -- dbms_output.put_line('Return status after copy relationships is ' || x_return_status);
1261 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1262 END IF;*/
1264 IF G_RET_CODE = FALSE THEN
1265 ROLLBACK TO copy_rel_type_and_all_rels;
1266 Retcode := 2;
1267
1268 END IF;
1269
1270
1271 EXCEPTION
1272 WHEN OTHERS
1273 THEN
1274 ROLLBACK TO copy_rel_type_and_all_rels ;
1275 x_return_status := FND_API.G_RET_STS_ERROR;
1276 FND_MESSAGE.SET_NAME('AR', 'HZ_COPY_REL_API_ERROR');
1277 FND_MESSAGE.SET_TOKEN('PROC' ,'COPY_REL_TYPE_AND_ALL_RELS');
1278 FND_MESSAGE.SET_TOKEN('ERROR' , SQLERRM);
1279 FND_MSG_PUB.ADD;
1280 FND_MSG_PUB.Count_And_Get(
1281 p_encoded => FND_API.G_FALSE,
1282 p_count => x_msg_count,
1283 p_data => x_msg_data);
1284
1285
1286 -- LOG MESSAGE TO FILE
1287 /*
1288 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1289 log(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1290 END LOOP;
1291 */
1292 logerror;
1293 FND_MESSAGE.CLEAR;
1294
1295 -- Bug 3651949: let concurrent program error out.
1296 Retcode := 2;
1297
1298 END copy_rel_type_and_all_rels ;
1299
1300 ----------------------------------------------------------------
1301 -- WRAPPER ON TOP OF THE copy_rel_typ_and_all_relships PROCEDURE
1302 -- SO THAT IT CAN BE CALLED AS A CONCURRENT PROGRAM
1303 ----------------------------------------------------------------
1304
1305
1306 PROCEDURE submit_copy_rel_type_rels_conc (
1307 -- in parameters
1308 p_source_rel_type IN VARCHAR2
1309 ,p_dest_rel_type IN VARCHAR2
1310 ,p_dest_rel_type_role_prefix IN VARCHAR2
1311 ,p_dest_rel_type_role_suffix IN VARCHAR2
1312 ,p_rel_valid_date IN DATE
1313 ,x_request_id OUT NOCOPY NUMBER
1314 ,x_return_status OUT NOCOPY VARCHAR2
1315 ,x_msg_count OUT NOCOPY NUMBER
1316 ,x_msg_data OUT NOCOPY VARCHAR2 )
1317 IS
1318 l_request_id NUMBER := NULL;
1319
1320 BEGIN
1321 x_return_status := FND_API.G_RET_STS_SUCCESS;
1322
1323
1324 -- CALL THE PROCEDURE THAT IS RUN AS A CONCURRENT REQUEST
1325 l_request_id := fnd_request.submit_request('AR','ARHCPRLA','Copy Rel Type and All Rels',
1326 to_char(sysdate,'DD-MON-YY HH24:MI:SS'),
1327 FALSE,p_source_rel_type,p_dest_rel_type,
1328 p_dest_rel_type_role_prefix,p_dest_rel_type_role_suffix,
1329 -- Bug 3651949: make sure the length of p_rel_valid_date is 9 characters.
1330 -- this is what we defined in the concurrent program.
1331 TO_CHAR(p_rel_valid_date, 'DD-MON-YY'));
1332 -- COMPLAIN IF IT DOES NOT RETURN A PROPER REQUEST ID.
1333 IF l_request_id = 0
1334 THEN
1335 FND_MESSAGE.SET_NAME('AR', 'AR_CUST_CONC_ERROR');
1336 FND_MSG_PUB.ADD;
1337 RAISE FND_API.G_EXC_ERROR;
1338 END IF;
1339
1340 x_request_id := l_request_id;
1341
1342
1343 EXCEPTION
1344 WHEN OTHERS
1345 THEN
1346 x_return_status := FND_API.G_RET_STS_ERROR;
1347 FND_MESSAGE.SET_NAME('AR', 'HZ_COPY_REL_API_ERROR');
1348 FND_MESSAGE.SET_TOKEN('PROC' ,'SUBMIT_COPY_REL_TYPE_RELS_CONC');
1349 FND_MESSAGE.SET_TOKEN('ERROR' , SQLERRM);
1350 FND_MSG_PUB.ADD;
1351 FND_MSG_PUB.Count_And_Get(
1352 p_encoded => FND_API.G_FALSE,
1353 p_count => x_msg_count,
1354 p_data => x_msg_data);
1355
1356 END submit_copy_rel_type_rels_conc ;
1357
1358
1359
1360
1361 --------------------------------------------------------------------------------------
1362 -- copy_hierarchy ::: This will take 2 Hierarchical relationship types A, B and do the following:
1363 -- 1. If B does not exist already, create B as a copy of A, in the sense of
1364 -- copy_rel_type_only, mentioned above.
1365 -- 2. Given a party id P, copy the complete hierarchy tree under P in A, to B.
1366 -- In other words, copy all relationships under A, that pertain to P's
1367 -- Hierarchy tree ( ie., the tree starting from P and going down) to B.
1368 -- If B exists already, this would mean that, when ever we create relationships in B,
1369 -- we need to make sure, that they do not already exist in A.
1370 --
1371 -- IT SHOULD BE NOTED THAT IF B EXISTS ALREADY, THEN ALL THE PHRASE PAIRS
1372 -- PERTAINING TO A THAT DO NOT ALREADY EXIST IN B, SHOULD BE FIRST CREATED IN B,
1373 -- BEFORE PROCEEDING TO STEP 2.
1377 errbuf OUT NOCOPY VARCHAR2
1374 --------------------------------------------------------------------------------------
1375
1376 PROCEDURE copy_hierarchy (
1378 ,Retcode OUT NOCOPY VARCHAR2
1379 ,p_source_rel_type IN VARCHAR2
1380 ,p_dest_rel_type IN VARCHAR2
1381 ,p_dest_rel_type_role_prefix IN VARCHAR2
1382 ,p_dest_rel_type_role_suffix IN VARCHAR2
1383 ,p_rel_valid_date IN DATE
1384 ,p_party_id IN NUMBER
1385 )
1386 IS
1387 x_return_status VARCHAR2(1);
1388 x_msg_count NUMBER;
1389 x_msg_data VARCHAR2(2000);
1390 -- GET ALL THE PHRASE PAIRS THAT ARE IN SOURCE BUT NOT IN DESTINATION
1391 cursor c0
1392 is
1393 select * from
1394 hz_relationship_types
1395 where relationship_type = p_source_rel_type
1396 and (direction_code = 'P' or direction_code = 'N')
1397 and (forward_rel_code, backward_rel_code, subject_type, object_type) not in
1398 (select forward_rel_code, backward_rel_code, subject_type, object_type
1399 from hz_relationship_types
1400 where relationship_type = p_dest_rel_type );
1401 BEGIN
1402 -- save and be ready to rollback
1403 savepoint copy_hierarchy ;
1404
1405 G_RET_CODE := TRUE;
1406
1407 -- return is status unless otherwise changed
1408 x_return_status := FND_API.G_RET_STS_SUCCESS;
1409
1410 log('-------------------------------------------');
1411 log('Passed in Source Rel Type is ' || p_source_rel_type );
1412 log('Passed in Destination Rel Type is ' || p_dest_rel_type );
1413 log('Passed in Prefix is ' || p_dest_rel_type_role_prefix );
1414 log('Passed in Suffix is ' || p_dest_rel_type_role_suffix );
1415 log('Passed in Date is ' || p_rel_valid_date );
1416 log('Passed in Party ID is ' || p_party_id );
1417
1418 -- FIRST COPY THOSE SPECIFIC PHRASE PAIRS FROM SOURCE TO DESTINATION
1419 FOR rel_type_rec in c0
1420 LOOP
1421 copy_selected_phrase_pair(p_source_rel_type,p_dest_rel_type,
1422 p_dest_rel_type_role_prefix, p_dest_rel_type_role_suffix,
1423 rel_type_rec.forward_rel_code, rel_type_rec.backward_rel_code,
1424 rel_type_rec.subject_type, rel_type_rec.object_type,
1425 x_return_status, x_msg_count, x_msg_data );
1426
1427 END LOOP;
1428
1429 -- RAISE HELL WHEN return status is not success
1430 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1431 THEN
1432 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1433 END IF;
1434
1435 -- COPY THE HIERARCHY (STARTING FROM P AND GOING BELOW) FROM SOURCE TO DESTINATION,
1436 -- BY CREATING ALL NECESSARY RELATIONSHIPS IN B.
1437 create_hierarchy(p_party_id, p_source_rel_type, p_dest_rel_type,
1438 p_dest_rel_type_role_prefix, p_dest_rel_type_role_suffix,
1439 nvl(p_rel_valid_date,SYSDATE), x_return_status, x_msg_count, x_msg_data );
1440
1441 -- Bug 4288839
1442 -- If G_RET_CODE IS FALSE, then it means that some relationships could not be created.
1443 -- Error out the concurrent program and rollback all changes.
1444 /*
1445 -- RAISE HELL WHEN return status is not success
1446 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1447 THEN
1448 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1449 END IF;
1450 */
1451 IF G_RET_CODE = FALSE THEN
1452 rollback to copy_hierarchy;
1453 Retcode := 2;
1454 END IF;
1455
1456 EXCEPTION
1457 WHEN OTHERS
1458 THEN
1459 -- rollback if you get this far
1460 rollback to copy_hierarchy ;
1461 x_return_status := FND_API.G_RET_STS_ERROR;
1462 FND_MESSAGE.SET_NAME('AR', 'HZ_COPY_REL_API_ERROR');
1463 FND_MESSAGE.SET_TOKEN('PROC' ,'COPY_HIERARCHY');
1464 FND_MESSAGE.SET_TOKEN('ERROR' , SQLERRM);
1465 FND_MSG_PUB.ADD;
1466 FND_MSG_PUB.Count_And_Get(
1467 p_encoded => FND_API.G_FALSE,
1468 p_count => x_msg_count,
1469 p_data => x_msg_data);
1470
1471 -- LOG MESSAGE TO FILE
1472 /*
1473 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1474 log(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1475 END LOOP;
1476 */
1477 logerror;
1478
1479 END copy_hierarchy;
1480
1481
1482
1483
1484 ----------------------------------------------------------------
1485 -- WRAPPER ON TOP OF THE copy_hierarchy PROCEDURE
1486 -- SO THAT IT CAN BE CALLED AS A CONCURRENT PROGRAM
1487 ----------------------------------------------------------------
1488
1489 PROCEDURE submit_copy_hierarchy_conc (
1490 -- in parameters
1491 p_source_rel_type IN VARCHAR2
1492 ,p_dest_rel_type IN VARCHAR2
1493 ,p_dest_rel_type_role_prefix IN VARCHAR2
1497 ,x_request_id OUT NOCOPY NUMBER
1494 ,p_dest_rel_type_role_suffix IN VARCHAR2
1495 ,p_rel_valid_date IN DATE
1496 ,p_party_id IN NUMBER
1498 ,x_return_status OUT NOCOPY VARCHAR2
1499 ,x_msg_count OUT NOCOPY NUMBER
1500 ,x_msg_data OUT NOCOPY VARCHAR2 )
1501 IS
1502 l_request_id NUMBER := NULL;
1503
1504 BEGIN
1505 x_return_status := FND_API.G_RET_STS_SUCCESS;
1506
1507
1508
1509 -- CALL THE PROCEDURE THAT IS RUN AS A CONCURRENT REQUEST
1510 l_request_id := fnd_request.submit_request('AR','ARHCPRLH','Copy Hierarchy of a Given Party',sysdate,
1511 FALSE,p_source_rel_type,p_dest_rel_type,
1512 p_dest_rel_type_role_prefix,p_dest_rel_type_role_suffix,p_rel_valid_date,p_party_id );
1513
1514 -- COMPLAIN IF IT DOES NOT RETURN A PROPER REQUEST ID.
1515 IF l_request_id = 0
1516 THEN
1517 FND_MESSAGE.SET_NAME('AR', 'AR_CUST_CONC_ERROR');
1518 FND_MSG_PUB.ADD;
1519 RAISE FND_API.G_EXC_ERROR;
1520 END IF;
1521
1522 x_request_id := l_request_id;
1523
1524
1525 EXCEPTION
1526 WHEN OTHERS
1527 THEN
1528 x_return_status := FND_API.G_RET_STS_ERROR;
1529 FND_MESSAGE.SET_NAME('AR', 'HZ_COPY_REL_API_ERROR');
1530 FND_MESSAGE.SET_TOKEN('PROC' ,'SUBMIT_COPY_HIERARCHY_CONC');
1531 FND_MESSAGE.SET_TOKEN('ERROR' , SQLERRM);
1532 FND_MSG_PUB.ADD;
1533 FND_MSG_PUB.Count_And_Get(
1534 p_encoded => FND_API.G_FALSE,
1535 p_count => x_msg_count,
1536 p_data => x_msg_data);
1537 END submit_copy_hierarchy_conc ;
1538
1539 ------------------------------------------------------------------------
1540 -- This procedure will convert a non-hierarchical relationship type to a
1541 -- hierarchical relationship type.
1542 --
1543 -- Bug 3615970: fixed various issues
1544 ------------------------------------------------------------------------
1545
1546 PROCEDURE convert_rel_type (
1547 errbuf OUT NOCOPY VARCHAR2
1548 ,Retcode OUT NOCOPY VARCHAR2
1549 ,p_rel_type IN VARCHAR2
1550 )
1551 IS
1552
1553 -- Bug 3615970: point 8
1554 --
1555 CURSOR c1 IS
1556 SELECT r.relationship_id,
1557 r.status,
1558 r.subject_id,
1559 r.subject_table_name,
1560 r.subject_type,
1561 r.object_id,
1562 r.object_table_name,
1563 r.object_type,
1564 r.start_date,
1565 r.end_date,
1566 r.relationship_code,
1567 t.backward_rel_code
1568 FROM hz_relationships r,
1569 hz_relationship_types t
1570 WHERE r.relationship_type = p_rel_type
1571 AND r.relationship_type = t.relationship_type
1572 AND t.relationship_type = p_rel_type
1573 AND r.relationship_code = t.forward_rel_code
1574 AND r.subject_type = t.subject_type
1575 AND r.object_type = t.object_type
1576 AND t.direction_code = 'P';
1577
1578 -- Bug 3615970: point 9. removed cursor c2.
1579 --
1580
1581 -- Bug 3615970: moved the circularity check to do_circularity_check
1582
1583 l_hierarchy_rec HZ_HIERARCHY_PUB.HIERARCHY_NODE_REC_TYPE;
1584 l_return_status VARCHAR2(1);
1585 l_msg_count NUMBER;
1586 l_msg_data VARCHAR2(2000);
1587
1588 i_relationship_id t_number_tbl;
1589 i_status t_varchar30_tbl;
1590 i_parent_id t_number_tbl;
1591 i_parent_type t_varchar30_tbl;
1592 i_parent_table_name t_varchar30_tbl;
1593 i_child_id t_number_tbl;
1594 i_child_type t_varchar30_tbl;
1595 i_child_table_name t_varchar30_tbl;
1596 i_start_date t_date_tbl;
1597 i_end_date t_date_tbl;
1598 i_relationship_code t_varchar30_tbl;
1599 i_backward_rel_code t_varchar30_tbl;
1600
1601 l_create_hierarchy_link VARCHAR2(1) := 'Y';
1602 l_id NUMBER;
1603 l_meaning VARCHAR2(400);
1604 x_return_status VARCHAR2(1);
1605
1606 rows NUMBER := 1000;
1607 l_last_fetch BOOLEAN := FALSE;
1608
1609 BEGIN
1610
1611 -- save and be ready to rollback
1612 savepoint convert_rel_type ;
1613
1614 -- initialize return status
1615 x_return_status := FND_API.G_RET_STS_SUCCESS;
1616
1617 -- Bug 3615970: point 2
1618 -- clean up hierarchy nodes table
1619 DELETE hz_hierarchy_nodes
1620 WHERE hierarchy_type = p_rel_type;
1621
1625 FETCH c1 BULK COLLECT INTO
1622 -- get all the relationships for this relationship type
1623 OPEN c1;
1624 LOOP
1626 i_relationship_id, i_status,
1627 i_parent_id, i_parent_table_name, i_parent_type,
1628 i_child_id, i_child_table_name, i_child_type,
1629 i_start_date, i_end_date,
1630 i_relationship_code, i_backward_rel_code
1631 LIMIT rows;
1632
1633 IF c1%NOTFOUND THEN
1634 l_last_fetch := TRUE;
1635 END IF;
1636 IF i_relationship_id.COUNT = 0 AND l_last_fetch THEN
1637 EXIT;
1638 END IF;
1639
1640 -- for each relationship
1641 --
1642 FOR i IN 1..i_relationship_id.COUNT LOOP
1643
1644 log('relationship_id = '||i_relationship_id(i)||' , '||
1645 'subject_id = '||i_parent_id(i)||' , '||
1646 'object_id = '||i_child_id(i));
1647
1648 g_parent_nodes_tbl.DELETE;
1649 g_relationship_id_tbl.DELETE;
1650
1651 -- Bug 3615970: point 3, 4
1652
1653 do_circularity_check (
1654 i_child_id(i),
1655 i_child_table_name(i),
1656 i_child_type(i),
1657 p_rel_type,
1658 i_start_date(i),
1659 i_end_date(i),
1660 x_return_status
1661 );
1662
1663 log('main: x_return_status = '||x_return_status);
1664
1665 IF x_return_status = 'S' THEN
1666 FOR i IN 1..g_parent_nodes_tbl.COUNT LOOP
1667 l_id := SUBSTRB(g_parent_nodes_tbl(i), 1, INSTRB(g_parent_nodes_tbl(i), '#')-1);
1668 g_passed_nodes_tbl(l_id) := g_parent_nodes_tbl(i);
1669 END LOOP;
1670
1671 ELSE
1672 FOR i IN 1..g_parent_nodes_tbl.COUNT LOOP
1673 l_id := SUBSTRB(g_parent_nodes_tbl(i), 1, INSTRB(g_parent_nodes_tbl(i), '#')-1);
1674 g_failed_nodes_tbl(l_id) := g_parent_nodes_tbl(i);
1675 END LOOP;
1676
1677 -- as long as there is one circularity check failed, the
1678 -- concurrent program will do circularity check only to
1679 -- report all of errors
1680 --
1681 IF l_create_hierarchy_link = 'Y' THEN
1682 ROLLBACK TO convert_rel_type ;
1683
1684 l_create_hierarchy_link := 'N';
1685 END IF;
1686
1687 END IF;
1688
1689 IF l_create_hierarchy_link = 'Y' THEN
1690 l_hierarchy_rec.hierarchy_type := p_rel_type;
1691 l_hierarchy_rec.parent_id := i_parent_id(i);
1692 l_hierarchy_rec.parent_table_name := i_parent_table_name(i);
1693 l_hierarchy_rec.parent_object_type := i_parent_type(i);
1694 l_hierarchy_rec.child_id := i_child_id(i);
1695 l_hierarchy_rec.child_table_name := i_child_table_name(i);
1696 l_hierarchy_rec.child_object_type := i_child_type(i);
1697 l_hierarchy_rec.effective_start_date := i_start_date(i);
1698 l_hierarchy_rec.effective_end_date := NVL(i_end_date(i), TO_DATE('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS'));
1699 l_hierarchy_rec.relationship_id := i_relationship_id(i);
1700 l_hierarchy_rec.status := NVL(i_status(i), 'A');
1701
1702 HZ_HIERARCHY_PUB.create_link(
1703 p_init_msg_list => FND_API.G_FALSE,
1704 p_hierarchy_node_rec => l_hierarchy_rec,
1705 x_return_status => l_return_status,
1706 x_msg_count => l_msg_count,
1707 x_msg_data => l_msg_data
1708 );
1709
1710 -- RAISE HELL WHEN return status is not success
1711 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1712 FND_MESSAGE.SET_NAME('AR', 'HZ_CONVERT_REL_API_ERROR');
1713 FND_MESSAGE.SET_TOKEN('PROC' ,'HZ_HIERARCHY_PUB.CREATE_LINK');
1714 FND_MESSAGE.SET_TOKEN('ERROR' , SQLERRM);
1715 FND_MSG_PUB.ADD;
1716 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1717
1718 ROLLBACK TO convert_rel_type ;
1719
1720 l_create_hierarchy_link := 'N';
1721 END IF;
1722
1723 -- Bug 3615970: can not assume direction code has value.
1724 -- it may be null because we don't have data fix script
1725 -- when the new column is introduced.
1726 --
1727 UPDATE hz_relationships
1728 SET direction_code = 'P'
1729 WHERE direction_code IS NULL
1730 AND relationship_id = i_relationship_id(i)
1731 AND relationship_type = p_rel_type
1732 AND relationship_code = i_relationship_code(i)
1733 AND subject_type = i_parent_type(i)
1734 AND object_type = i_child_type(i);
1735
1736 UPDATE hz_relationships
1737 SET direction_code = 'C'
1738 WHERE direction_code IS NULL
1739 AND relationship_id = i_relationship_id(i)
1740 AND relationship_type = p_rel_type
1741 AND relationship_code = i_backward_rel_code(i)
1742 AND subject_type = i_child_type(i)
1743 AND object_type = i_parent_type(i);
1744
1745 END IF;
1746
1747 END LOOP;
1748
1749 IF l_last_fetch = TRUE THEN
1750 EXIT;
1751 END IF;
1752 END LOOP;
1753
1754 CLOSE c1;
1755
1756 IF l_create_hierarchy_link = 'N' THEN
1757 -- Bug 3615970: point 7
1758
1759 -- prepare output file
1760 --
1761 SELECT meaning
1762 INTO l_meaning
1763 FROM ar_lookups
1764 WHERE lookup_type = 'HZ_RELATIONSHIP_TYPE'
1765 AND lookup_code = p_rel_type;
1766
1767 FND_MESSAGE.SET_NAME('AR', 'HZ_CIRCULAR_REL_EXIST_FMT1');
1768 FND_MESSAGE.SET_TOKEN('RELTYPE' , l_meaning);
1769
1770 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET()||fnd_global.NEWLINE);
1771
1772 FOR i IN 1..g_message_fmt1.COUNT LOOP
1773 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, i||' : ');
1774 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, g_message_fmt1(i)||fnd_global.NEWLINE);
1775 END LOOP;
1776
1777 -- prepare log file
1778 --
1779 FND_MESSAGE.SET_NAME('AR', 'HZ_CIRCULAR_REL_EXIST_FMT2');
1780 FND_MESSAGE.SET_TOKEN('RELTYPE' , p_rel_type);
1781
1782 log('===================================================================');
1783 log(fnd_global.NEWLINE||fnd_global.NEWLINE||FND_MESSAGE.GET()||fnd_global.NEWLINE);
1784
1785 FOR i IN 1..g_message_fmt2.COUNT LOOP
1786 log(i||' : ');
1787 log(g_message_fmt2(i)||fnd_global.NEWLINE);
1788 END LOOP;
1789
1790 -- Bug 3615970: point 6
1791 Retcode := 2;
1792
1793 ELSE
1794
1795 UPDATE HZ_RELATIONSHIP_TYPES
1796 SET HIERARCHICAL_FLAG = 'Y',
1797 MULTIPLE_PARENT_ALLOWED = 'N',
1798 INCL_UNRELATED_ENTITIES = 'N',
1799 ALLOW_CIRCULAR_RELATIONSHIPS = 'N',
1800 -- Bug 3615970: point 5
1801 ALLOW_RELATE_TO_SELF_FLAG = 'N',
1802 -- Bug 3615905: set DO_NOT_ALLOW_CONVERT
1803 DO_NOT_ALLOW_CONVERT = 'Y'
1804 WHERE RELATIONSHIP_TYPE = p_rel_type;
1805
1806 COMMIT;
1807
1808 END IF;
1809
1810 EXCEPTION
1811 WHEN OTHERS THEN
1812 -- rollback if this happens
1813 rollback to convert_rel_type ;
1814
1815 x_return_status := FND_API.G_RET_STS_ERROR;
1816 FND_MESSAGE.SET_NAME('AR', 'HZ_CONVERT_REL_API_ERROR');
1817 FND_MESSAGE.SET_TOKEN('PROC' ,'CONVERT_REL_TYPE');
1818 FND_MESSAGE.SET_TOKEN('ERROR' , SQLERRM);
1819 FND_MSG_PUB.ADD;
1820 FND_MSG_PUB.Count_And_Get(
1821 p_encoded => FND_API.G_FALSE,
1822 p_count => l_msg_count,
1823 p_data => l_msg_data);
1824
1825 logerror;
1826 FND_MESSAGE.CLEAR;
1827
1828 -- Bug 3615970: point 6
1829 Retcode := 2;
1830
1831 END convert_rel_type;
1832
1833 ----------------------------------------------------------------
1834 -- WRAPPER ON TOP OF THE convert_rel_type PROCEDURE
1835 -- SO THAT IT CAN BE CALLED AS A CONCURRENT PROGRAM
1836 ----------------------------------------------------------------
1837
1838 PROCEDURE submit_convert_rel_type_conc (
1839 -- in parameters
1840 p_rel_type IN VARCHAR2
1841 -- out NOCOPY parameters
1842 ,x_request_id OUT NOCOPY NUMBER
1843 ,x_return_status OUT NOCOPY VARCHAR2
1844 ,x_msg_count OUT NOCOPY NUMBER
1845 ,x_msg_data OUT NOCOPY VARCHAR2
1846 )
1847 IS
1848 l_request_id NUMBER := NULL;
1849
1850 BEGIN
1851 x_return_status := FND_API.G_RET_STS_SUCCESS;
1852
1853
1854
1855 -- CALL THE PROCEDURE THAT IS RUN AS A CONCURRENT REQUEST
1856 l_request_id := fnd_request.submit_request(
1857 'AR','ARHCPRLC',
1858 -- Bug 3615970: point 1
1859 'Convert a Relationship Type to Hierarchical',
1860 to_char(sysdate,'DD-MON-YY HH24:MI:SS'),
1861 FALSE, p_rel_type );
1862
1863 -- COMPLAIN IF IT DOES NOT RETURN A PROPER REQUEST ID.
1864 IF l_request_id = 0
1865 THEN
1866 FND_MESSAGE.SET_NAME('AR', 'AR_CUST_CONC_ERROR');
1867 FND_MSG_PUB.ADD;
1868 RAISE FND_API.G_EXC_ERROR;
1869 END IF;
1870
1871 x_request_id := l_request_id;
1872
1873 EXCEPTION
1874 WHEN OTHERS
1875 THEN
1876 x_return_status := FND_API.G_RET_STS_ERROR;
1877 FND_MESSAGE.SET_NAME('AR', 'HZ_CONVERT_REL_API_ERROR');
1878 FND_MESSAGE.SET_TOKEN('PROC' ,'SUBMIT_CONVERT_REL_TYPE_CONC');
1879 FND_MESSAGE.SET_TOKEN('ERROR' , SQLERRM);
1880 FND_MSG_PUB.ADD;
1881 FND_MSG_PUB.Count_And_Get(
1882 p_encoded => FND_API.G_FALSE,
1883 p_count => x_msg_count,
1884 p_data => x_msg_data);
1885
1886 END submit_convert_rel_type_conc ;
1887 END HZ_COPY_REL_PVT ;
[Home] [Help]