1 PACKAGE BODY HZ_DNB_HIERARCHY_PVT AS
2 /* $Header: ARHDNBHB.pls 120.19 2006/02/13 12:09:46 vravicha noship $ */
3 -------------------------------------------------------------------------------------------
4 -- AUTHOR :::::: COLATHUR VIJAYAN ("VJN")
5 -- ----------------------------------------------------------------------------------------
6
7
8 ---------------------------------------------------------------------------------------------
9 ---------------------------------------------------------------------------------------------
10 -- NOTE ::::: ALL THE SCENARIOS (RELATIONSHIPS BETWEEN PARTIES) THAT ARE DESCRIBED HERE
11 -- PERTAIN TO DATA THAT IS OBTAINED FROM DNB AND IMPORTED INTO TCA. SINCE NOT
12 -- ALL PARTIES NEED TO BE NECESSARILY PURCHASED BY A TYPICAL USER OF TCA, THE DIFFERENT
13 -- RELATIONSHIPS THAT EXIST BETWEEN PARTIES, IN THE HZ_RELATIONSHIPS TABLE, AT ANY TIME CANNOT
14 -- REPRESENT REALITY ( ALL POSSIBLE RELATIONSHIPS BETWEEN ALL KINDS OF PARTIES, THAT DNB HAS
15 -- IN ITS DATABASE).
16 -- SO THE QUESTION IS, HOW DO WE BEST REPRESENT THE DIFFERENT RELATIONSHIPS BETWEEN DNB IMPORTED
17 -- PARTIES, AS FOUND IN HZ_RELATIONSHIPS, IN A HIERARCHICAL FASHION, WITH THE UNDERSTANDING THAT
18 -- THE HZ_RELATIONSHIPS TABLE, MAY ONLY REPRESENT A SUBSET OF REALITY AT ANY GIVEN TIME.
19
20 ------------------------------------------------------------------------------------------------------------
21 ------------------------------------------------------------------------------------------------------------
22 -- SOME DEFINITIONS
23 -- TCA-DNB_HIERARCHY TREE :::: THIS IS THE TREE THAT WE WOULD LIKE TO SEE IN THE HIERARCHY VIEWER FOR DNB DATA.
24 -- CONSIDERING THAT TCA MAY NOT HAVE ALL DNB DATA (SEE THE FIRST NOTE ABOVE), THE
25 -- HIERARCHY TREE THAT WE WOULD BE BUILDING, MAY AT BEST, BE A SUBSET OF THE ACTUAL
26 -- TREE. THE TREE WILL HAVE THE FOLLOWING PROPERTIES ::::::::
27 -- 1. IT WILL START FROM A LEAF NODE AND GO ALL THE WAY UP TO THE GLOBAL ULTIMATE.
28 -- 2. BETWEEN ANY PARTY (P1, SAY) AND A PARTY (P2, SAY) AT THE NEXT HIGHER LEVEL, THE RELATIONSHIP
29 -- WOULD BE ONE OF THE FOLLOWING :
30 -- a. A PARENT-SUBSIDIARY OR A HEADQUARTER-DIVISION.
31 -- b. UNKNOWN
32 -- ( THIS HAPPENS WHEN EVER --- P2 IS A GUP OF P1 OR P2 IS A DUP OF P1 AND THERE IS
33 -- NO DIRECT PARENT_SUBSIDIARY OR A HEADQUARTER-DIVISION RELATIONSHIP BETWEEN
34 -- P1 AND P2).
35 -- POSITIONAL/IMMEDIATE PARENT :::::::: A POSITIONAL/IMMEDIATE PARENT OF A PARTY IS A PARTY THAT IS ONE LEVEL
36 -- ABOVE IT, IN THE TCA_DNB_HIERARCHY TREE.
37 -- LEAF NODE PARTY ::::::: THIS IS A PARTY WHICH IS A CHILD OF SOME PARTY AND A PARENT OF NO PARTY.
38 -- ORPHAN PARTY ::::: THIS IS A PARTY WHICH HAS NO DIRECT DNB RELATIONSHIP OF THE TYPE
39 -- DUP, GUP, PARENT/HQ WITH ANY OTHER PARTY. IT IS AN ORPHAN IN THE SENSE
40 -- THAT IT HAS NO DIRECT POSITIONAL/IMMEDIATE PARENT. HOWEVER FOR THESE
41 -- PARTIES WE COULD DERIVE POSITIONAL/IMMEDIATE PARENTS, BY USING THE INFORMATION
42 -- FROM THEIR CHILDREN.
43 -------------------------------------------------------------------------------------------------------------
44 -------------------------------------------------------------------------------------------------------------
45
46 ------------------------------
47 -- relationship_exists
48 ------------------------------
49
50 FUNCTION relationship_exists
51 -- Return Y if there exists any relationship what so ever between subject and object,
52 -- under the passed in relationship type (NOTE: Relationship code is insignificant in this check !!!)
53 -- N otherwise
54 (p_subject_id NUMBER, p_object_id NUMBER, p_relationship_code VARCHAR2 , p_relationship_type VARCHAR2)
55 RETURN VARCHAR2
56 IS
57 CURSOR c0
58 IS
59 select 'Y'
60 from hz_relationships
61 where subject_id = p_subject_id
62 and object_id = p_object_id
63 and actual_content_source = 'DNB'
64 and relationship_type = p_relationship_type
65 and (end_date is null
66 or end_date > sysdate) ;
67 l_yn VARCHAR2(1);
68 result VARCHAR2(1);
69 BEGIN
70
71 -- Force the function to return a 'Y' , when ever the subject and object are the same
72 IF p_subject_id = p_object_id
73 THEN
74 RETURN 'Y' ;
75 END IF;
76
77 OPEN c0;
78 FETCH c0 INTO l_yn;
79 IF c0%NOTFOUND THEN
80 result := 'N';
81 ELSE
82 result := 'Y';
83 END IF;
84 CLOSE c0;
85 RETURN result;
86 END relationship_exists ;
87
88
89 ------------------------------
90 -- get_parent_subject_id_info
91 -----------------------------
92
93
94 FUNCTION get_parent_subject_id_info
95 (p_subject_id NUMBER, p_relationship_type VARCHAR2 )
96 RETURN dnb_dfs_rec_type
97 IS
98 CURSOR c0
99 IS
100 SELECT subject_id, start_date
101 FROM hz_relationships
102 WHERE relationship_code = 'PARENT_OF'
103 and relationship_type = p_relationship_type
104 and actual_content_source = 'DNB'
105 and object_id = p_subject_id
106 and object_id <> subject_id
107 and (end_date is null
108 or end_date > sysdate) ;
109 l_yn DNB_DFS_REC_TYPE ;
110 result VARCHAR2(1);
111 BEGIN
112 OPEN c0;
113 FETCH c0 INTO l_yn;
114 CLOSE c0;
115 RETURN l_yn ;
116 END get_parent_subject_id_info ;
117
118
119
120 ------------------------------
121 -- get_parent_subject_id
122 -----------------------------
123
124
125 FUNCTION get_parent_subject_id
126 (p_subject_id NUMBER, p_relationship_type VARCHAR2 )
127 RETURN NUMBER
128 IS
129 CURSOR c0
130 IS
131 SELECT subject_id
132 FROM hz_relationships
133 WHERE relationship_code = 'PARENT_OF'
134 and relationship_type = p_relationship_type
135 and actual_content_source = 'DNB'
136 and object_id = p_subject_id
137 and object_id <> subject_id
138 and (end_date is null
139 or end_date > sysdate) ;
140 l_yn number ;
141 result VARCHAR2(1);
142 BEGIN
143 OPEN c0;
144 FETCH c0 INTO l_yn;
145 CLOSE c0;
146 RETURN l_yn ;
147 END get_parent_subject_id ;
148
149
150 ------------------------------
151 -- get_hq_subject_id_info
152 -----------------------------
153
154
155 FUNCTION get_hq_subject_id_info
156 (p_subject_id NUMBER, p_relationship_type VARCHAR2)
157 RETURN dnb_dfs_rec_type
158 IS
159 CURSOR c0
160 IS
161 SELECT subject_id, start_date
162 FROM hz_relationships
163 WHERE relationship_code = 'HEADQUARTERS_OF'
164 and relationship_type = p_relationship_type
165 and actual_content_source = 'DNB'
166 and object_id = p_subject_id
167 and object_id <> subject_id
168 and (end_date is null
169 or end_date > sysdate) ;
170 l_yn dnb_dfs_rec_type ;
171 result VARCHAR2(1);
172 BEGIN
173 OPEN c0;
174 FETCH c0 INTO l_yn;
175 CLOSE c0;
176 RETURN l_yn ;
177 END get_hq_subject_id_info ;
178
179
180
181 ------------------------------
182 -- get_hq_subject_id
183 -----------------------------
184
185
186 FUNCTION get_hq_subject_id
187 (p_subject_id NUMBER, p_relationship_type VARCHAR2)
188 RETURN NUMBER
189 IS
190 CURSOR c0
191 IS
192 SELECT subject_id
193 FROM hz_relationships
194 WHERE relationship_code = 'HEADQUARTERS_OF'
195 and relationship_type = p_relationship_type
196 and actual_content_source = 'DNB'
197 and object_id = p_subject_id
198 and object_id <> subject_id
199 and (end_date is null
200 or end_date > sysdate) ;
201 l_yn number ;
202 result VARCHAR2(1);
203 BEGIN
204 OPEN c0;
205 FETCH c0 INTO l_yn;
206 CLOSE c0;
207 RETURN l_yn ;
208 END get_hq_subject_id ;
209
210
211 ------------------------------
212 -- get_dup_subject_id_info
213 -----------------------------
214
215 FUNCTION get_dup_subject_id_info
216 (p_subject_id NUMBER, p_relationship_type VARCHAR2)
217 RETURN dnb_dfs_rec_type
218 IS
219 CURSOR c0
220 IS
221 SELECT subject_id, start_date
222 FROM hz_relationships
223 WHERE relationship_code = 'DOMESTIC_ULTIMATE_OF'
224 and relationship_type = p_relationship_type
225 and actual_content_source = 'DNB'
226 and object_id = p_subject_id
227 and (end_date is null
228 or end_date > sysdate) ;
229 l_yn dnb_dfs_rec_type ;
230 result VARCHAR2(1);
231 BEGIN
232 OPEN c0;
233 FETCH c0 INTO l_yn;
234 CLOSE c0;
235 RETURN l_yn ;
236 END get_dup_subject_id_info ;
237
238
239
240
241 ------------------------------
242 -- get_dup_subject_id
243 -----------------------------
244
245 FUNCTION get_dup_subject_id
246 (p_subject_id NUMBER, p_relationship_type VARCHAR2)
247 RETURN NUMBER
248 IS
249 CURSOR c0
250 IS
251 SELECT subject_id
252 FROM hz_relationships
253 WHERE relationship_code = 'DOMESTIC_ULTIMATE_OF'
254 and relationship_type = p_relationship_type
255 and actual_content_source = 'DNB'
256 and object_id = p_subject_id
257 and (end_date is null
258 or end_date > sysdate) ;
259 l_yn number ;
260 result VARCHAR2(1);
261 BEGIN
262 OPEN c0;
263 FETCH c0 INTO l_yn;
264 CLOSE c0;
265 RETURN l_yn ;
266 END get_dup_subject_id ;
267
268
269 ------------------------------
270 -- get_gup_subject_id_info
271 -----------------------------
272 FUNCTION get_gup_subject_id_info
273 (p_subject_id NUMBER, p_relationship_type VARCHAR2 )
274 RETURN dnb_dfs_rec_type
275 IS
276 CURSOR c0
277 IS
278 SELECT subject_id, start_date
279 FROM hz_relationships
280 WHERE relationship_code = 'GLOBAL_ULTIMATE_OF'
281 and relationship_type = p_relationship_type
282 and actual_content_source = 'DNB'
283 and object_id = p_subject_id
284 and (end_date is null
285 or end_date > sysdate) ;
286 l_yn dnb_dfs_rec_type ;
287 result VARCHAR2(1);
288 BEGIN
289 OPEN c0;
290 FETCH c0 INTO l_yn;
291 CLOSE c0;
292 RETURN l_yn ;
293 END get_gup_subject_id_info ;
294
295
296
297 ------------------------------
298 -- get_gup_subject_id
299 -----------------------------
300 FUNCTION get_gup_subject_id
301 (p_subject_id NUMBER, p_relationship_type VARCHAR2 )
302 RETURN NUMBER
303 IS
304 CURSOR c0
305 IS
306 SELECT subject_id
307 FROM hz_relationships
308 WHERE relationship_code = 'GLOBAL_ULTIMATE_OF'
309 and relationship_type = p_relationship_type
310 and actual_content_source = 'DNB'
311 and object_id = p_subject_id
312 and (end_date is null
313 or end_date > sysdate) ;
314 l_yn number ;
315 result VARCHAR2(1);
316 BEGIN
317 OPEN c0;
318 FETCH c0 INTO l_yn;
319 CLOSE c0;
320 RETURN l_yn ;
321 END get_gup_subject_id ;
322
323
324 ------------------------------
325 -- get_child_subject_id
326 -----------------------------
327 FUNCTION get_child_subject_id
328 (p_subject_id NUMBER, p_relationship_type VARCHAR2)
329 RETURN NUMBER
330 IS
331 CURSOR c0
332 IS
333 SELECT subject_id
334 FROM hz_relationships
335 WHERE relationship_code = 'SUBSIDIARY_OF'
336 and relationship_type = p_relationship_type
337 and actual_content_source = 'DNB'
338 and object_id = p_subject_id
339 and object_id <> subject_id
340 and (end_date is null
341 or end_date > sysdate) ;
342 l_yn number ;
343 result VARCHAR2(1);
344 BEGIN
345 OPEN c0;
346 FETCH c0 INTO l_yn;
347 CLOSE c0;
348 RETURN l_yn ;
349 END get_child_subject_id ;
350
351 ------------------------------
352 -- get_division_subject_id
353 -----------------------------
354 FUNCTION get_division_subject_id
355 (p_subject_id NUMBER, p_relationship_type VARCHAR2)
356 RETURN NUMBER
357 IS
358 CURSOR c0
359 IS
360 SELECT subject_id
361 FROM hz_relationships
362 WHERE relationship_code = 'DIVISION_OF'
363 and relationship_type = p_relationship_type
364 and actual_content_source = 'DNB'
365 and object_id = p_subject_id
366 and object_id <> subject_id
367 and (end_date is null
368 or end_date > sysdate) ;
369 l_yn number ;
370 result VARCHAR2(1);
371 BEGIN
372 OPEN c0;
373 FETCH c0 INTO l_yn;
374 CLOSE c0;
375 RETURN l_yn ;
376 END get_division_subject_id ;
377
378
379
380 ------------------------------
381 -- get_party_subordinate_to
382 -----------------------------
383 FUNCTION get_party_subordinate_to
384 (p_subject_id NUMBER, p_relationship_type VARCHAR2 )
385 RETURN NUMBER
386 IS
387 CURSOR c0
388 IS
389 SELECT object_id
390 FROM hz_relationships
391 WHERE relationship_type = p_relationship_type
392 and (relationship_code = 'GLOBAL_SUBSIDIARY_OF'
393 or relationship_code = 'DOMESTIC_SUBSIDIARY_OF'
394 or relationship_code = 'DIVISION_OF'
395 or relationship_code = 'SUBSIDIARY_OF')
396 and actual_content_source = 'DNB'
397 and subject_id = p_subject_id
398 and (end_date is null
399 or end_date > sysdate) ;
400 l_yn number ;
401 result VARCHAR2(1);
402 BEGIN
403 OPEN c0;
404 FETCH c0 INTO l_yn;
405 CLOSE c0;
406 RETURN l_yn ;
407 END get_party_subordinate_to ;
408
409
410 --------------------------------------------
411 -- party_exists
412 --------------------------------------------
413
414 FUNCTION party_exists
415 -- Return Y if the passed in subject id exists in new rel types
416 -- N otherwise
417 (p_subject_id NUMBER )
418 RETURN VARCHAR2
419 IS
420 CURSOR c0
421 IS
422 select 'Y'
423 from hz_relationships
424 where subject_id = p_subject_id
425 and relationship_type = 'DNB_HIERARCHY'
426 and rownum = 1
427 and (end_date is null
428 or end_date > sysdate) ;
429 l_yn VARCHAR2(1);
430 result VARCHAR2(1);
431 BEGIN
432 OPEN c0;
433 FETCH c0 INTO l_yn;
434 IF c0%NOTFOUND THEN
435 result := 'N';
436 ELSE
437 result := 'Y';
438 END IF;
439 CLOSE c0;
440 RETURN result;
441 END party_exists ;
442
443 --------------------------------------------
444 -- gup_of_party_exists
445 --------------------------------------------
446
447 FUNCTION gup_of_party_exists
448 -- Return Y if the passed in gup_subject_id exists in new rel types
449 -- N otherwise
450 (gup_subject_id NUMBER )
451 RETURN VARCHAR2
452 IS
453 CURSOR c0
454 IS
455 select 'Y'
456 from hz_relationships
457 where subject_id = gup_subject_id
458 and relationship_type = 'DNB_HIERARCHY'
459 and rownum = 1
460 and (end_date is null
461 or end_date > sysdate) ;
462 l_yn VARCHAR2(1);
463 result VARCHAR2(1);
464 BEGIN
465 OPEN c0;
466 FETCH c0 INTO l_yn;
467 IF c0%NOTFOUND THEN
468 result := 'N';
469 ELSE
470 result := 'Y';
471 END IF;
472 CLOSE c0;
473 RETURN result;
474 END gup_of_party_exists ;
475
476 --------------------------------------------
477 -- party_has_hq
478 --------------------------------------------
479
480 FUNCTION party_has_hq
481 -- Return Y if the passed in p_subject_id has a HQ in new rel types
482 -- N otherwise
483 (p_subject_id NUMBER )
484 RETURN VARCHAR2
485 IS
486 CURSOR c0
487 IS
488 select 'Y'
489 from hz_relationships
490 where object_id = p_subject_id
491 and relationship_code = 'HEADQUARTERS_OF'
492 and relationship_type = 'DNB_HIERARCHY'
493 and (end_date is null
494 or end_date > sysdate) ;
495 l_yn VARCHAR2(1);
496 result VARCHAR2(1);
497 BEGIN
498 OPEN c0;
499 FETCH c0 INTO l_yn;
500 IF c0%NOTFOUND THEN
501 result := 'N';
502 ELSE
503 result := 'Y';
504 END IF;
505 CLOSE c0;
506 RETURN result;
507 END party_has_hq ;
508
509 --------------------------------------------
510 -- party_has_parent
511 --------------------------------------------
512
513 FUNCTION party_has_parent
514 -- Return Y if the passed in p_subject_id has a PARENT in new rel types
515 -- N otherwise
516 (p_subject_id NUMBER )
517 RETURN VARCHAR2
518 IS
519 CURSOR c0
520 IS
521 select 'Y'
522 from hz_relationships
523 where object_id = p_subject_id
524 and relationship_code = 'PARENT_OF'
525 and relationship_type = 'DNB_HIERARCHY'
526 and (end_date is null
527 or end_date > sysdate) ;
528 l_yn VARCHAR2(1);
529 result VARCHAR2(1);
530 BEGIN
531 OPEN c0;
532 FETCH c0 INTO l_yn;
533 IF c0%NOTFOUND THEN
534 result := 'N';
535 ELSE
536 result := 'Y';
537 END IF;
538 CLOSE c0;
539 RETURN result;
540 END party_has_parent ;
541
542 --------------------------------------------
543 -- party_has_gup_relationship
544 --------------------------------------------
545
546 FUNCTION party_has_gup_relationship
547 -- Return Y if the passed in p_subject_id has a GLOBAL SUBSIDIARY RELATIONSHIP
548 -- with the pased in GUP in new rel types
549 -- N otherwise
550 (p_subject_id NUMBER, gup_subject_id NUMBER )
551 RETURN VARCHAR2
552 IS
553 CURSOR c0
554 IS
555 select 'Y'
556 from hz_relationships
557 where subject_id = p_subject_id
558 and object_id = gup_subject_id
559 and relationship_code = 'GLOBAL_SUBSIDIARY_OF'
560 and relationship_type = 'DNB_HIERARCHY'
561 and (end_date is null
562 or end_date > sysdate) ;
563 l_yn VARCHAR2(1);
564 result VARCHAR2(1);
565 BEGIN
566 OPEN c0;
567 FETCH c0 INTO l_yn;
568 IF c0%NOTFOUND THEN
569 result := 'N';
570 ELSE
571 result := 'Y';
572 END IF;
573 CLOSE c0;
574 RETURN result;
575 END party_has_gup_relationship ;
576
577 --------------------------------------------
578 -- party_has_dup_relationship
579 --------------------------------------------
580
581 FUNCTION party_has_dup_relationship
582 -- Return Y if the passed in p_subject_id participates in a
583 -- DOMESTIC SUBSIDIARY RELATIONSHIP in new rel types
584 -- N otherwise
585 (p_subject_id NUMBER)
586 RETURN VARCHAR2
587 IS
588 CURSOR c0
589 IS
590 select 'Y'
591 from hz_relationships
592 where subject_id = p_subject_id
593 and relationship_code = 'DOMESTIC_SUBSIDIARY_OF'
594 and relationship_type = 'DNB_HIERARCHY'
595 and (end_date is null
596 or end_date > sysdate) ;
597 l_yn VARCHAR2(1);
598 result VARCHAR2(1);
599 BEGIN
600 OPEN c0;
601 FETCH c0 INTO l_yn;
602 IF c0%NOTFOUND THEN
603 result := 'N';
604 ELSE
605 result := 'Y';
606 END IF;
607 CLOSE c0;
608 RETURN result;
609 END party_has_dup_relationship ;
610
611 --------------------------------------------
612 -- party_is_a_gup
613 --------------------------------------------
614
615 FUNCTION party_is_a_gup
616 -- Return Y if the passed in p_subject_id is a GUP
617 -- in new rel types
618 -- N otherwise
619 (p_subject_id NUMBER)
620 RETURN VARCHAR2
621 IS
622 CURSOR c0
623 IS
624 select 'Y'
625 from hz_relationships
626 where subject_id = p_subject_id
627 and relationship_code = 'GLOBAL_ULTIMATE_OF'
628 and relationship_type = 'DNB_HIERARCHY'
629 and (end_date is null
630 or end_date > sysdate) ;
631 l_yn VARCHAR2(1);
632 result VARCHAR2(1);
633 BEGIN
634 OPEN c0;
635 FETCH c0 INTO l_yn;
636 IF c0%NOTFOUND THEN
637 result := 'N';
638 ELSE
639 result := 'Y';
640 END IF;
641 CLOSE c0;
642 RETURN result;
643 END party_is_a_gup ;
644
645 --------------------------------------------
646 -- is_a_gup
647 --------------------------------------------
648
649 FUNCTION is_a_gup
650 -- Return Y if the passed in subject id is a GUP
651 -- N otherwise
652 (p_subject_id NUMBER, p_relationship_type VARCHAR2 )
653 RETURN VARCHAR2
654 IS
655 CURSOR c0
656 IS
657 select 'Y'
658 from hz_relationships
659 where subject_id = p_subject_id
660 and relationship_type = p_relationship_type
661 and relationship_code = 'GLOBAL_ULTIMATE_OF'
662 and (end_date is null
663 or end_date > sysdate) ;
664 l_yn VARCHAR2(1);
665 result VARCHAR2(1);
666 BEGIN
667 OPEN c0;
668 FETCH c0 INTO l_yn;
669 IF c0%NOTFOUND THEN
670 result := 'N';
671 ELSE
672 result := 'Y';
673 END IF;
674 CLOSE c0;
675 RETURN result;
676 END is_a_gup ;
677
678
679 --------------------------------------------
680 -- is_a_dup
681 --------------------------------------------
682
683 FUNCTION is_a_dup
684 -- Return Y if the passed in subject id is a DUP
685 -- N otherwise
686 (p_subject_id NUMBER, p_relationship_type VARCHAR2 )
687 RETURN VARCHAR2
688 IS
689 CURSOR c0
690 IS
691 select 'Y'
692 from hz_relationships
693 where subject_id = p_subject_id
694 and relationship_type = p_relationship_type
695 and relationship_code = 'DOMESTIC_ULTIMATE_OF'
696 and (end_date is null
697 or end_date > sysdate) ;
698 l_yn VARCHAR2(1);
699 result VARCHAR2(1);
700 BEGIN
701 OPEN c0;
702 FETCH c0 INTO l_yn;
703 IF c0%NOTFOUND THEN
704 result := 'N';
705 ELSE
706 result := 'Y';
707 END IF;
708 CLOSE c0;
709 RETURN result;
710 END is_a_dup ;
711
712
713 --------------------------------------------
714 -- get_country
715 --------------------------------------------
716
717 FUNCTION get_country
718 -- Return the country to which this party id belongs to
719 (p1 NUMBER)
720 RETURN VARCHAR2
721 IS
722 CURSOR c0
723 IS
724 select hl.country from
725 hz_locations hl, hz_party_sites hps
726 where hl.location_id=hps.location_id
727 and hps.party_id = p1;
728 l_yn VARCHAR2(20);
729 BEGIN
730 OPEN c0;
731 FETCH c0 INTO l_yn;
732 CLOSE c0;
733 RETURN l_yn ;
734 END get_country ;
735
736
737 ------------------------------
738 -- get_relationship_id
739 -----------------------------
740
741 FUNCTION get_relationship_id
742 -- get relationship id, when given 2 parties and a relationship between them
743 (p_subject_id NUMBER, p_object_id NUMBER, p_relationship_code VARCHAR2, p_relationship_type VARCHAR2)
744 RETURN NUMBER
745 IS
746 CURSOR c0
747 IS
748 select relationship_id
749 from hz_relationships
750 where subject_id = p_subject_id
751 and relationship_type = p_relationship_type
752 and object_id = p_object_id
753 and relationship_code = p_relationship_code ;
754 l_yn NUMBER;
755 BEGIN
756 OPEN c0;
757 FETCH c0 INTO l_yn;
758 CLOSE c0;
759 RETURN l_yn ;
760 END get_relationship_id ;
761
762
763
764 ------------------------------
765 -- create_rel
766 -----------------------------
767
768 FUNCTION create_rel
769 -- create relationship between two parties, by calling the create relationship API
770 -- using DNB_HIERARCHY, as the default relationship type.
771 -- NOTE:: By virtue of the fact that the new relationship type 'DNB_HIERARCHY' should create
772 -- a true Hierarchy, we make sure that this function will create one and only one
773 -- pair (forward and backward) of relationships between the passed in subject and
774 -- object.
775 (p_subject_id NUMBER, p_object_id NUMBER, p_relationship_code VARCHAR2, p_start_date DATE)
776 RETURN NUMBER
777 IS
778 /***** FOR CREATING RELATIONSHIPS **********/
779 p_relationship_rec HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE;
780 x_relationship_id NUMBER;
781 x_party_id NUMBER;
782 x_party_number VARCHAR2(2000);
783 x_return_status VARCHAR2(2000);
784 x_msg_count NUMBER;
785 x_msg_data VARCHAR2(2000);
786 BEGIN
787 p_relationship_rec.subject_id := p_subject_id ;
788 p_relationship_rec.subject_type := 'ORGANIZATION';
789 p_relationship_rec.subject_table_name := 'HZ_PARTIES';
790 p_relationship_rec.object_id := p_object_id ;
791 p_relationship_rec.object_type := 'ORGANIZATION';
792 p_relationship_rec.object_table_name := 'HZ_PARTIES';
793 p_relationship_rec.relationship_code := p_relationship_code ;
794 p_relationship_rec.relationship_type := 'DNB_HIERARCHY';
795 p_relationship_rec.comments := 'DNB DFS CREATED';
796 --3903207: commented code that passes content_source_type
797 -- and added code to pass actual_content_source
798 --p_relationship_rec.content_source_type := 'DNB';
799 p_relationship_rec.actual_content_source := 'DNB';
800 p_relationship_rec.created_by_module :='DNB_DFS'; /* DFS = Data Fix script */
801 p_relationship_rec.start_date:= nvl(p_start_date, SYSDATE) ;
802
803 -- CALL RELATIONSHIP API, AFTER MAKING SURE THAT THE RELATIONSHIP WE ARE
804 -- TRYING TO CREATE, DOES NOT EXIST ALREADY UNDER THE 'DNB_HIERARCHY'
805 -- RELATIONSHIP TYPE
806 IF relationship_exists( p_subject_id, p_object_id, p_relationship_code, 'DNB_HIERARCHY' ) = 'N'
807 THEN
808 hz_relationship_v2pub.create_relationship('T',p_relationship_rec,
809 x_relationship_id,x_party_id,x_party_number,x_return_status,x_msg_count,x_msg_data,'');
810 END IF;
811 return 0 ;
812 END create_rel ;
813
814 ------------------------------
815 -- create_rel_cps
816 -----------------------------
817
818 FUNCTION create_rel_cps
819 -- create relationship between two parties, by calling the create relationship API
820 -- using DNB_HIERARCHY, as the default relationship type.
821 -- NOTE:: By virtue of the fact that the new relationship type 'DNB_HIERARCHY' should create
822 -- a true Hierarchy, we make sure that this function will create one and only one
823 -- pair (forward and backward) of relationships between the passed in subject and
824 -- object.
825 (p_subject_id NUMBER, p_object_id NUMBER, p_relationship_code VARCHAR2, p_start_date DATE)
826 RETURN NUMBER
827 IS
828 /***** FOR CREATING RELATIONSHIPS **********/
829 p_relationship_rec HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE;
830 x_relationship_id NUMBER;
831 x_party_id NUMBER;
832 x_party_number VARCHAR2(2000);
833 x_return_status VARCHAR2(2000);
834 x_msg_count NUMBER;
835 x_msg_data VARCHAR2(2000);
836 BEGIN
837 p_relationship_rec.subject_id := p_subject_id ;
838 p_relationship_rec.subject_type := 'ORGANIZATION';
839 p_relationship_rec.subject_table_name := 'HZ_PARTIES';
840 p_relationship_rec.object_id := p_object_id ;
841 p_relationship_rec.object_type := 'ORGANIZATION';
842 p_relationship_rec.object_table_name := 'HZ_PARTIES';
843 p_relationship_rec.relationship_code := p_relationship_code ;
844 p_relationship_rec.relationship_type := 'DNB_HIERARCHY';
845 p_relationship_rec.comments := 'DNB CPS CREATED';
846 --3903207: commented code that passes content_source_type
847 --and added code to pass actual_content_source
848 --p_relationship_rec.content_source_type := 'DNB';
849 p_relationship_rec.actual_content_source := 'DNB';
850 /* Fix bug 4659246: Change DNB_CPS to TCA_DNB_MAPPING.
851 DNB_CPS was not seeded as a lookup code of HZ_CREATED_BY_MODULES */
852 p_relationship_rec.created_by_module :='TCA_DNB_MAPPING';
853
854 -- we always delay the start date time by 2 mins, so that we can avoid overlaps
855 -- for example when A -- B has to be end dated and A -- C has to created,
856 -- the creation time of A -- C , will always be 2 mins past the end dation of
857 -- A -- B.
858 IF p_start_date is null
859 THEN
860 p_relationship_rec.start_date:= sysdate + 120/(24*60*60);
861 ELSE
862 p_relationship_rec.start_date:= p_start_date + 120/(24*60*60);
863 END IF;
864
865
866 -- CALL RELATIONSHIP API, AFTER MAKING SURE THAT THE RELATIONSHIP WE ARE
867 -- TRYING TO CREATE, DOES NOT EXIST ALREADY UNDER THE 'DNB_HIERARCHY'
868 -- RELATIONSHIP TYPE
869 IF relationship_exists( p_subject_id, p_object_id, p_relationship_code, 'DNB_HIERARCHY' ) = 'N'
870 THEN
871 hz_relationship_v2pub.create_relationship('T',p_relationship_rec,
872 x_relationship_id,x_party_id,x_party_number,x_return_status,x_msg_count,x_msg_data,'');
873 END IF;
874
875
876 -- RAISE HELL WHEN return status is not success
877 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
878 THEN
879 FND_MESSAGE.SET_NAME('AR', 'HZ_CREATE_REL_SUB_OBJ_ERROR');
880 FND_MESSAGE.SET_TOKEN('RELATIONSHIP' ,p_relationship_code);
881 FND_MESSAGE.SET_TOKEN('REL_TYPE' , 'DNB_HIERARCHY');
882 FND_MESSAGE.SET_TOKEN('SUB' , to_char(p_subject_id));
883 FND_MESSAGE.SET_TOKEN('OBJ' , to_char(p_object_id));
884 FND_MSG_PUB.ADD;
885 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
886
887 END IF;
888 return 0 ;
889 END create_rel_cps ;
890
891 ------------------------------
892 -- end_date_rel
893 -----------------------------
894
895 FUNCTION end_date_rel
896 -- end date relationship between two parties, by calling the update relationship API
897 (p_subject_id NUMBER, p_object_id NUMBER, p_relationship_code VARCHAR2 )
898 RETURN NUMBER
899 IS
900 x_relationship_rec HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE;
901 x_relationship_id NUMBER;
902 x_directional_flag VARCHAR2(1);
903 x_object_version_number NUMBER;
904 x_party_object_version_number NUMBER;
905 x_return_status VARCHAR2(2000);
906 x_msg_count NUMBER;
907 x_msg_data VARCHAR2(2000);
908 BEGIN
909 -- GET THE RELATIONSHIP ID FOR THE RELATIONSHIP
910 x_relationship_id := get_relationship_id(p_subject_id, p_object_id, p_relationship_code, 'DNB_HIERARCHY');
911
912 -- GET OBJECT VERSION NUMBER FOR THE FETCHED RELATIONSHIP ID
913 SELECT object_version_number INTO x_object_version_number
914 FROM hz_relationships
915 WHERE relationship_id = x_relationship_id
916 AND subject_table_name = 'HZ_PARTIES'
917 AND object_table_name = 'HZ_PARTIES'
918 AND directional_flag = 'F';
919
920 -- CALL THE GET RELATIONSHIP API TO GET THE CORRESPONDING RELATIONSHIP RECORD
921 hz_relationship_v2pub.get_relationship_rec ('T', x_relationship_id, x_directional_flag, x_relationship_rec,
922 x_return_status, x_msg_count, x_msg_data );
923 -- UPDATE THE CORRESPONDING RELATIONSHIP RECORD, BY END DATING IT AND CALLING THE UPDATE RELATIONSHIP API
924 x_relationship_rec.end_date := sysdate;
925 hz_relationship_v2pub.update_relationship ('T', x_relationship_rec,
926 x_object_version_number, x_party_object_version_number,x_return_status, x_msg_count, x_msg_data );
927 return 0;
928
929 END end_date_rel ;
930
931
932 -------------------------------------
933 -- is_a_descendant
934 -- Given two parties A,B this recursive function would, figure out NOCOPY if B is a descendant of A
935 -- ie., if B lies on the same branch as A and is at a lower level than A
936 -------------------------------------
937
938 FUNCTION is_a_descendant
939 (p_subject_id NUMBER, p_object_id NUMBER)
940 -- p_subject_id -- is the purchased party.
941 -- p_object_id -- is the party we are checking, if it is a descendant of P.
942 RETURN VARCHAR2
943 IS
944 ret_value VARCHAR2(1) ;
945 temp number;
946 BEGIN
947 -- p_subject_id = A, p_object_id = B.
948
949 -- If B is a GLOBAL ULTIMATE, there is no way B could be at a lower level than P
950 -- Just return a NO in this case
951 IF is_a_gup (p_object_id, 'GLOBAL_ULTIMATE') = 'N'
952 THEN
953 return 'N';
954
955 -- If B has a headquarter see, if it is A, else continue the recursion
956 ELSIF (temp = get_hq_subject_id ( p_object_id, 'DNB_HIERARCHY') ) is not null
957 THEN
958 IF temp = p_subject_id
959 THEN
960 return 'Y';
961 ELSE
962 return is_a_descendant(p_subject_id, temp) ;
963 END IF;
964 -- Else if B has a parent see, if it is A, else continue the recursion
965 ELSIF (temp = get_parent_subject_id ( p_object_id, 'DNB_HIERARCHY') ) is not null
966 THEN
967 IF temp = p_subject_id
968 THEN
969 return 'Y';
970 ELSE
971 return is_a_descendant(p_subject_id, temp) ;
972 END IF;
973 -- Else if B has a domestic ultimate see, if it is A, else continue the recursion
974 ELSIF (temp = get_dup_subject_id ( p_object_id, 'DNB_HIERARCHY')) is not null
975 THEN
976 IF temp = p_subject_id
977 THEN
978 return 'Y';
979 ELSE
980 return is_a_descendant(p_subject_id, temp) ;
981 END IF;
982 -- Else if B has a global ultimate see, if it is A, else continue the recursion
983 ELSIF (temp = get_gup_subject_id ( p_object_id, 'DNB_HIERARCHY')) is not null
984 THEN
985 IF temp = p_subject_id
986 THEN
987 return 'Y';
988 ELSE
989 return is_a_descendant(p_subject_id, temp) ;
990 END IF;
991 END IF;
992
993 -- IF IT GETS THIS FAR RETURN N
994 return 'N';
995
996 END is_a_descendant ;
997
998
999
1000 -------------------------------------
1001 -- is_party_dangling
1002 -- Given a party A, this function will find out NOCOPY if A participates in any relationship
1003 -- whatsoever, in which it is subordinate to some other party.
1004 -------------------------------------
1005
1006 FUNCTION is_party_dangling
1007 (p_subject_id NUMBER)
1008 -- p_subject_id -- is the party.
1009 RETURN VARCHAR2
1010 IS
1011 CURSOR c0
1012 IS
1013 select 'Y'
1014 from hz_relationships
1015 where subject_id = p_subject_id
1016 and actual_content_source = 'DNB'
1017 and relationship_type = 'DNB_HIERARCHY'
1018 and (relationship_code = 'GLOBAL_SUBSIDIARY_OF'
1019 or relationship_code = 'DOMESTIC_SUBSIDIARY_OF'
1020 or relationship_code = 'DIVISION_OF'
1021 or relationship_code = 'SUBSIDIARY_OF')
1022 and (end_date is null
1023 or end_date > sysdate) ;
1024 l_yn VARCHAR2(1);
1025 result VARCHAR2(1);
1026 BEGIN
1027
1028
1029 OPEN c0;
1030 FETCH c0 INTO l_yn;
1031 -- if no matches found, the party is dangling.
1032 IF c0%NOTFOUND THEN
1033 result := 'Y';
1034 -- else it is not dangling
1035 ELSE
1036 result := 'N';
1037 END IF;
1038 CLOSE c0;
1039 RETURN result;
1040 END is_party_dangling ;
1041
1042
1043
1044
1045
1046
1047 -------------------------------------
1048 -- is_penultimate_node
1049 -- Given a party A and the global ultimate party, this function will find out NOCOPY if A is
1050 -- a penultimate node -- ITS IMMEDIATE PARENT IS THE GLOBAL ULTIMATE
1051 ------------------------------------
1052
1053 FUNCTION is_penultimate_node
1054 (p_subject_id NUMBER, gup_subject_id NUMBER)
1055 -- p_subject_id -- is the party.
1056 -- gup_subject_id -- the global ultimate party.
1057 RETURN VARCHAR2
1058 IS
1059 CURSOR c0
1060 IS
1061 select 'Y'
1062 from hz_relationships
1063 where subject_id = p_subject_id
1064 and object_id = gup_subject_id
1065 and actual_content_source = 'DNB'
1066 and relationship_type = 'DNB_HIERARCHY'
1067 and (relationship_code = 'GLOBAL_SUBSIDIARY_OF'
1068 or relationship_code = 'DOMESTIC_SUBSIDIARY_OF'
1069 or relationship_code = 'DIVISION_OF'
1070 or relationship_code = 'SUBSIDIARY_OF')
1071 and (end_date is null
1072 or end_date > sysdate) ;
1073 l_yn VARCHAR2(1);
1074 result VARCHAR2(1);
1075 BEGIN
1076
1077
1078 OPEN c0;
1079 FETCH c0 INTO l_yn;
1080 -- if no matches found, the party is not a penultimate node
1081 IF c0%NOTFOUND THEN
1082 result := 'N';
1083 -- else it is not dangling
1084 ELSE
1085 result := 'Y';
1086 END IF;
1087 CLOSE c0;
1088 RETURN result;
1089 END is_penultimate_node ;
1090
1091
1092
1093 -------------------------------------
1094 -- unlink_immediate_children
1095 -- Given a party A, find all its children, unlink all the immediate children and connect them
1096 -- to the global ultimate.
1097 ------------------------------------
1098
1099 FUNCTION unlink_immediate_children
1100 (p_subject_id NUMBER, gup_subject_id NUMBER)
1101 -- p_subject_id -- is the party.
1102 -- gup_subject_id -- global ultimate of purchased party.
1103 RETURN NUMBER
1104 IS
1105 CURSOR c0
1106 IS
1107 select subject_id, relationship_code
1108 from hz_relationships
1109 where object_id = p_subject_id
1110 and actual_content_source = 'DNB'
1111 and relationship_type = 'DNB_HIERARCHY'
1112 and (relationship_code = 'GLOBAL_SUBSIDIARY_OF'
1113 or relationship_code = 'DOMESTIC_SUBSIDIARY_OF'
1114 or relationship_code = 'DIVISION_OF'
1115 or relationship_code = 'SUBSIDIARY_OF')
1116 and (end_date is null
1117 or end_date > sysdate) ;
1118
1119 ret_value NUMBER;
1120
1121 BEGIN
1122 FOR party_rec IN c0
1123 LOOP
1124 -- END DATE THE RELATIONSHIP BETWEEN THE PARTY AND ITS IMMEDIATE CHILD
1125 ret_value := end_date_rel (p_subject_id, party_rec.subject_id, party_rec.relationship_code );
1126 -- CREATE RELATIONSHIP BETWEEN IMMEDIATE CHILD AND THE GLOBAL ULTIMATE
1127 ret_value := create_rel_cps(party_rec.subject_id, gup_subject_id, 'GLOBAL_SUBSIDIARY_OF', SYSDATE );
1128 END LOOP;
1129
1130 -- JUST TO MAKE SURE FUNCTION RETURNS A VALUE
1131 return 0;
1132
1133 -- THROW EXCEPTIONS UP TO THE CALLER WHEN CREATING RELATIONSHIPS FAIL
1134 EXCEPTION
1135 WHEN OTHERS
1136 THEN
1137 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1138
1139 END unlink_immediate_children ;
1140
1141
1142 ---------------------------------------------------------------
1143 -- conform_dom_ult_of_party
1144 -- Given a purchased party A, conform its DUP to the Hierarchy.
1145 ---------------------------------------------------------------
1146
1147 FUNCTION conform_dom_ult_of_party
1148 (p_subject_id NUMBER, parent_subject_id NUMBER, dup_subject_id NUMBER, gup_subject_id NUMBER)
1149 -- p_subject_id -- is the purchased party.
1150 -- parent_subject_id -- parent of the purchased party.
1151 -- dup_subject_id -- domestic ultimate of the purchased party.
1152 -- gup_subject_id -- global ultimate of purchased party.
1153 RETURN NUMBER
1154 IS
1155 ret_value NUMBER;
1156
1157 BEGIN
1158 -- NOTE::: SINCE THE DUP OF P, NEED NOT HAVE BEEN ITS DOMESTIC ULTIMATE IN A PREVIOUS PURCHASE
1159 -- WE NEED TO TAKE CARE OF THIS PRETTY ELABORATELY, BY UNDOING THINGS
1160 -- THAT WERE DONE BEFORE.
1161
1162 -- CONFORM DUP, IF PASSED IN DUP IS NOT THE SAME AS THE PARTY.
1163 IF dup_subject_id <> p_subject_id
1164 THEN
1165 -- IF THE DOMESTIC ULTIMATE IS AT A LOWER LEVEL THAN P, ON THE SAME BRANCH, FIX IT.
1166 IF is_a_descendant(p_subject_id, dup_subject_id) = 'Y'
1167 THEN
1168 ret_value := unlink_immediate_children(p_subject_id, gup_subject_id);
1169 ELSE
1170 -- IF THE DOMESTIC ULTIMATE IS NOT AT A LOWER LEVEL, CREATE APPROPRIATE RELATIONSHIPS
1171 -- OFCOURSE, AFTER MAKING SURE THAT IT IS NOT ALREADY SUBORDINATE TO SOME OTHER PARTY
1172
1173 IF get_party_subordinate_to(dup_subject_id, 'DNB_HIERARCHY') is null
1174 THEN
1175 ret_value := create_rel_cps(dup_subject_id, gup_subject_id, 'GLOBAL_SUBSIDIARY_OF', SYSDATE );
1176 END IF;
1177
1178 -- NEED TO BE ELABORATE IF THE DUP IS NOT THE SAME AS THE PARENT, ELSE THE WORK IS ALREADY DONE
1179 IF parent_subject_id <> dup_subject_id
1180 THEN
1181 -- CREATE THE DOMESTIC SUBSIDIARY RELATIONSHIP BASED ON THE PARENT BEING SUBORDINATE
1182 -- TO ANOTHER PARTY (UNDER DNB_HIERARCHY RELATIONSHIP TYPE) AND THE SAME COUNTRY CHECK.
1183
1184 -- PARENT IS NOT SUBORDINATE TO ANY PARTY
1185 IF get_party_subordinate_to(parent_subject_id, 'DNB_HIERARCHY') is null
1186 THEN
1187 -- SAME COUNTRY CHECK
1188 IF get_country(parent_subject_id) = get_country(p_subject_id)
1189 THEN
1190 ret_value := create_rel_cps(parent_subject_id, dup_subject_id, 'DOMESTIC_SUBSIDIARY_OF', SYSDATE );
1191 END IF;
1192 -- PARENT IS SUBORDINATE TO THE GUP
1193 ELSIF get_gup_subject_id(parent_subject_id, 'DNB_HIERARCHY') = gup_subject_id
1194 THEN
1195 -- SAME COUNTRY CHECK
1196 IF get_country(parent_subject_id) = get_country(p_subject_id)
1197 THEN
1198 ret_value := end_date_rel(parent_subject_id, gup_subject_id, 'GLOBAL_SUBSIDIARY_OF');
1199 ret_value := create_rel_cps(parent_subject_id, dup_subject_id, 'DOMESTIC_SUBSIDIARY_OF', SYSDATE );
1200 END IF;
1201 END IF;
1202 END IF;
1203 END IF;
1204
1205 END IF;
1206
1207
1208 -- CONFORM PARENT IF NECESSARY
1209 -- WE BASICALLY SEE IF PARENT IS DANGLING AND FIX IT, BY CREATING A RELATIONSHIP BETWEEN ITSELF AND THE GUP.
1210 IF get_party_subordinate_to(parent_subject_id, 'DNB_HIERARCHY') is null
1211 THEN
1212 ret_value := create_rel_cps(parent_subject_id, gup_subject_id, 'GLOBAL_SUBSIDIARY_OF', SYSDATE );
1213 END IF;
1214
1215
1216 -- JUST TO MAKE SURE FUNCTION RETURNS A VALUE
1217 return 0;
1218
1219 -- THROW EXCEPTIONS UP TO THE CALLER WHEN CREATING RELATIONSHIPS FAIL
1220 EXCEPTION
1221 WHEN OTHERS
1222 THEN
1223 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1224
1225 END conform_dom_ult_of_party ;
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236 -------------------------------------
1237 -- create_hierarchy_tree
1238 -- The recursive function that would create the hierarchy tree,
1239 -- given the leaf node.
1240 -- Please note that recursion would stop if a party is either a
1241 -- global ultimate or if it is an orphan.
1242 -------------------------------------
1243
1244 FUNCTION create_hierarchy_tree
1245 (p_subject_id NUMBER, p_gup_party_id NUMBER, p_child_party_id NUMBER)
1246 RETURN NUMBER
1247 IS
1248 ret_value number;
1249 gup_party_id number;
1250 parent_dnb_dfs_rec_type dnb_dfs_rec_type;
1251 hq_dnb_dfs_rec_type dnb_dfs_rec_type;
1252 dup_dnb_dfs_rec_type dnb_dfs_rec_type;
1253 gup_dnb_dfs_rec_type dnb_dfs_rec_type;
1254 BEGIN
1255
1256 -- IN THE FIRST CALL TO THIS FUNCTION, FOR A PARTICULAR RECURSION, THE GUP WILL BE CALCULATED,
1257 -- BUT WILL BE RETAINED FOR ALL SUBSEQUENT RECURSIONS.
1258 IF p_gup_party_id = -1
1259 THEN
1260 gup_party_id := get_gup_subject_id(p_subject_id, 'GLOBAL_ULTIMATE' );
1261 ELSE
1262 gup_party_id := p_gup_party_id ;
1263 END IF;
1264
1265 -- Get PARENT/HQ info about the passed in party
1266 -- At any point of time, one and only one of these record types should have data.
1267 parent_dnb_dfs_rec_type := get_parent_subject_id_info(p_subject_id, 'PARENT/SUBSIDIARY');
1268 hq_dnb_dfs_rec_type := get_hq_subject_id_info(p_subject_id, 'HEADQUARTERS/DIVISION' );
1269
1270 -- The passed in party is a GUP. Get the hell out of here.........
1271 IF is_a_gup(p_subject_id, 'GLOBAL_ULTIMATE') = 'Y'
1272 THEN
1273 -- NO NEED FOR RECURSION ::::: EXIT
1274 RETURN 0;
1275
1276
1277 -- OBSERVATION::: Any party that is not a GUP, has to fall into the following mutually exclusive categories:
1278 -- It has a parent.
1279 -- It has a HQ.
1280 -- It has neither a parent nor a HQ.
1281
1282 -- The passed in party has a parent
1283 ELSIF parent_dnb_dfs_rec_type.party_id is not null
1284 THEN
1285 -- CREATE THE PARENT RELATIONSHIP
1286 ret_value := create_rel(parent_dnb_dfs_rec_type.party_id, p_subject_id, 'PARENT_OF',
1287 parent_dnb_dfs_rec_type.start_date );
1288
1289
1290 -- USE THE DUP INFORMATION !!!!!!!!!
1291 -- CREATE THE GUP RELATIONSHIP DIRECTLY BETWEEN THE GUP AND THE DOMESTIC ULTIMATE, OF THE PASSED IN PARTY,
1292 -- IF IN CASE THE DUP IS NEITHER EQUAL TO THE PARENT NOR EQUAL TO THE PASSED IN PARTY.
1293
1294 dup_dnb_dfs_rec_type := get_dup_subject_id_info ( p_subject_id, 'DOMESTIC_ULTIMATE' );
1295 IF dup_dnb_dfs_rec_type.party_id <> parent_dnb_dfs_rec_type.party_id and
1296 dup_dnb_dfs_rec_type.party_id <> p_subject_id
1297 THEN
1298 -- CREATE THE GUP RELATIONSHIP BETWEEN THE PASSED IN GUP AND THE DUP OF THE PASSED IN PARTY
1299 ret_value := create_rel( gup_party_id, dup_dnb_dfs_rec_type.party_id, 'GLOBAL_ULTIMATE_OF',
1300 dup_dnb_dfs_rec_type.start_date );
1301 END IF;
1302 -- RECURSION
1303 RETURN create_hierarchy_tree(parent_dnb_dfs_rec_type.party_id, gup_party_id, p_subject_id );
1304
1305 -- The passed in party has a headquarters
1306 ELSIF hq_dnb_dfs_rec_type.party_id is not null
1307 THEN
1308
1309 -- CREATE THE HEAD QUARTERS RELATIONSHIP
1310 ret_value := create_rel(hq_dnb_dfs_rec_type.party_id, p_subject_id, 'HEADQUARTERS_OF',
1311 hq_dnb_dfs_rec_type.start_date );
1312
1313
1314 -- USE THE DUP INFORMATION !!!!!!!!!
1315 -- CREATE THE GUP RELATIONSHIP DIRECTLY BETWEEN THE GUP AND THE DOMESTIC ULTIMATE, OF THE PASSED IN PARTY,
1316 -- IF IN CASE THE DUP IS NEITHER EQUAL TO THE HQ NOR EQUAL TO THE PASSED IN PARTY.
1317
1318 dup_dnb_dfs_rec_type := get_dup_subject_id_info ( p_subject_id, 'DOMESTIC_ULTIMATE' );
1319 IF dup_dnb_dfs_rec_type.party_id <> hq_dnb_dfs_rec_type.party_id and
1320 dup_dnb_dfs_rec_type.party_id <> p_subject_id
1321 THEN
1322 -- CREATE THE GUP RELATIONSHIP BETWEEN THE PASSED IN GUP AND THE DUP OF THE PASSED IN PARTY
1323 ret_value := create_rel( gup_party_id, dup_dnb_dfs_rec_type.party_id, 'GLOBAL_ULTIMATE_OF',
1324 dup_dnb_dfs_rec_type.start_date );
1325 END IF;
1326 -- RECURSION
1327 RETURN create_hierarchy_tree(hq_dnb_dfs_rec_type.party_id, gup_party_id, p_subject_id );
1328
1329 -- The passed in party is an orphan -- no parent and no HQ.
1330 -- For this case, we derive the relationships, through its child.
1331 ELSE
1332 -- GET THE DOMESTIC ULTIMATE OF THE PASSED IN CHILD PARTY
1333 dup_dnb_dfs_rec_type := get_dup_subject_id_info ( p_child_party_id, 'DOMESTIC_ULTIMATE' );
1334
1335 -- VERY WEIRD IF THIS HAPPENS -- BAD DATA
1336 IF dup_dnb_dfs_rec_type.party_id is null
1337 THEN
1338 RETURN 0;
1339 END IF;
1340
1341 -- DO THE DERIVATION (IE., EXTRACTING INFORMATION FROM THE IMMEDIATE CHILD)OF THE DUP,
1342 -- ONLY IF THE DUP OF THE CHILD IS NEITHER THE CHILD NOR THE PASSED IN PARTY
1343 IF dup_dnb_dfs_rec_type.party_id <> p_child_party_id and dup_dnb_dfs_rec_type.party_id <> p_subject_id
1344 THEN
1345
1346 -- CREATE THE GUP RELATIONSHIP BETWEEN THE PASSED IN GUP AND CHILD's DUP
1347 ret_value := create_rel(gup_party_id, dup_dnb_dfs_rec_type.party_id, 'GLOBAL_ULTIMATE_OF',
1348 dup_dnb_dfs_rec_type.start_date );
1349
1350 -- ORPHAN AND ITS CHILD ARE IN THE SAME COUNTRY
1351 IF get_country(p_subject_id) = get_country(p_child_party_id)
1352 THEN
1353 -- THE DERIVED (CHILD's) DUP WOULD BE THE DUP OF THE PASSED IN PARTY
1354 ret_value := create_rel(dup_dnb_dfs_rec_type.party_id, p_subject_id, 'DOMESTIC_ULTIMATE_OF',
1355 dup_dnb_dfs_rec_type.start_date );
1356 ELSE
1357 -- THE PASSED IN GUP WOULD BE THE GUP OF THE PASSED IN PARTY
1358 ret_value := create_rel(gup_party_id, p_subject_id, 'GLOBAL_ULTIMATE_OF',
1359 dup_dnb_dfs_rec_type.start_date );
1360
1361 END IF;
1362 ELSE
1363 -- THE GUP OF THE CHILD WOULD BE THE GUP OF THE PASSED IN PARTY
1364 -- ALTHOUGH THE GUP IS PASSED IN IN THE RECURSION, WE NEED TO DO THIS
1365 -- MANUEVOUR, TO GET THE START DATE
1366 gup_dnb_dfs_rec_type := get_gup_subject_id_info ( p_child_party_id, 'GLOBAL_ULTIMATE' );
1367 ret_value := create_rel(gup_party_id, p_subject_id, 'GLOBAL_ULTIMATE_OF',
1368 gup_dnb_dfs_rec_type.start_date );
1369
1370 END IF;
1371
1372 -- NO RECURSION REQUIRED
1373 RETURN 0;
1374
1375
1376 END IF;
1377
1378 -- JUST TO MAKE SURE FUNCTION RETURNS A VALUE
1379 return 0;
1380 END create_hierarchy_tree ;
1381
1382
1383 /**
1384 * PROCEDURE create_dnb_hierarchy
1385 *
1386 * DESCRIPTION
1387 *
1388 *
1389 *
1390 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1391 *
1392 *
1393 * ARGUMENTS
1394 *
1395 *
1396 * NOTES
1397 *
1398 * MODIFICATION HISTORY
1399 *
1400 * 12-13-2002 Colathur Vijayan o Created.
1401 *
1402 */
1403
1404 -------------------------------------
1405 -- create_dnb_hierarchy
1406 -- Main Procedure::: This would first do a clean up, by clearing up the existing hz_dnb_hierarchy_dump table,
1407 -- find all root parties and for every root party construct the hierarchy tree by
1408 -- finding successive parents through recursion.
1409 -------------------------------------
1410 PROCEDURE create_dnb_hierarchy (
1411 -- input parameters
1412 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1413 -- output parameters
1414 x_return_status OUT NOCOPY VARCHAR2,
1415 x_msg_count OUT NOCOPY NUMBER,
1416 x_msg_data OUT NOCOPY VARCHAR2
1417 ) IS
1418 l_root_object_id NUMBER;
1419 ret_value NUMBER;
1420
1421 -- THE CURSOR THAT WILL GET ALL ROOT PARTIES. A ROOT PARTY IS A PARTY THAT HAS NO CHILDREN, BUT ALWAYS HAS A PARENT.
1422
1423 -- Bug 4945516
1424 CURSOR c_get_root_parties is
1425 select subject_id
1426 from hz_relationships rel
1427 where rel.actual_content_source = 'DNB'
1428 and rel.relationship_type in ( 'PARENT/SUBSIDIARY' , 'HEADQUARTERS/DIVISION' )
1429 and rel.relationship_code in ( 'SUBSIDIARY_OF' , 'DIVISION_OF' )
1430 and ( rel.end_date is null
1431 or rel.end_date > sysdate )
1432 and rel.object_table_name='HZ_PARTIES'
1433 and rel.object_type='ORGANIZATION'
1434 and rel.subject_table_name='HZ_PARTIES'
1435 and rel.subject_type='ORGANIZATION'
1436 and rel.directional_flag in ('F','B')
1437 and rel.status='A'
1438 and not exists ( select 1
1439 from hz_relationships
1440 where relationship_type in ( 'PARENT/SUBSIDIARY' , 'HEADQUARTERS/DIVISION' )
1441 and relationship_code in ( 'PARENT_OF' , 'HEADQUARTERS_OF' )
1442 and subject_table_name='HZ_PARTIES'
1443 and subject_type='ORGANIZATION'
1444 and ( end_date is null
1445 or end_date > sysdate )
1446 and actual_content_source = 'DNB'
1447 and status='A'
1448 and subject_id=rel.subject_id);
1449
1450 BEGIN
1451
1452 -- initialize API return status to success.
1453 x_return_status := FND_API.G_RET_STS_SUCCESS;
1454
1455 -- BEFORE WE MESS WITH ANY DNB DATA WE NEED TO CALL THE DNB POLICY FUNCTION
1456 hz_common_pub.disable_cont_source_security;
1457
1458 -- OPEN THE CURSOR THAT WILL GET YOU ALL ROOT PARTIES
1459 OPEN c_get_root_parties ;
1460
1461 -- LOOP THROUGH
1462 LOOP
1463 FETCH c_get_root_parties INTO l_root_object_id ;
1464
1465 -- CONSTRUCT THE TREE FOR EVERY ROOT PARTY
1466 ret_value := create_hierarchy_tree(l_root_object_id, -1, l_root_object_id );
1467
1468 EXIT WHEN c_get_root_parties%NOTFOUND ;
1469 END LOOP;
1470
1471 -- CLOSE CURSOR
1472 CLOSE c_get_root_parties;
1473
1474
1475
1476 -- standard call to get message count and if count is 1, get message info.
1477 FND_MSG_PUB.Count_And_Get(
1478 p_encoded => FND_API.G_FALSE,
1479 p_count => x_msg_count,
1480 p_data => x_msg_data);
1481 END create_dnb_hierarchy ;
1482
1483
1484 /**
1485 * PROCEDURE create_dnb_hierarchy
1486 *
1487 * DESCRIPTION
1488 *
1489 *
1490 *
1491 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1492 *
1493 *
1494 * ARGUMENTS
1495 *
1496 *
1497 * NOTES
1498 *
1499 * MODIFICATION HISTORY
1500 *
1501 * 12-13-2002 Colathur Vijayan o Created.
1502 *
1503 */
1504
1505 -------------------------------------
1506 -- create_dnb_hierarchy
1507 -- Main Procedure::: The overloaded procedure, that could be called from the concurrent program
1508 -------------------------------------
1509 PROCEDURE create_dnb_hierarchy (
1510 errbuf OUT NOCOPY VARCHAR2,
1511 Retcode OUT NOCOPY VARCHAR2,
1512 cleanup_required IN VARCHAR2 DEFAULT 'N'
1513 ) IS
1514 l_root_object_id NUMBER;
1515 ret_value NUMBER;
1516
1517 -- THE CURSOR THAT WILL GET ALL ROOT PARTIES. A ROOT PARTY IS A PARTY THAT HAS NO CHILDREN, BUT ALWAYS HAS A PARENT.
1518
1519 -- Bug 4945516
1520 CURSOR c_get_root_parties is
1521 select rel.subject_id
1522 from hz_relationships rel
1523 where rel.actual_content_source = 'DNB'
1524 and rel.relationship_code in ( 'SUBSIDIARY_OF' , 'DIVISION_OF' )
1525 and ( rel.end_date is null
1526 or rel.end_date > sysdate )
1527 and rel.object_table_name='HZ_PARTIES'
1528 and rel.object_type='ORGANIZATION'
1529 and rel.subject_table_name='HZ_PARTIES'
1530 and rel.subject_type='ORGANIZATION'
1531 and rel.directional_flag in ('F','B')
1532 and not exists ( select 1
1533 from hz_relationships
1534 where relationship_code in ( 'PARENT_OF' , 'HEADQUARTERS_OF' )
1535 and subject_table_name='HZ_PARTIES'
1536 and subject_type='ORGANIZATION'
1537 and ( end_date is null
1538 or end_date > sysdate )
1539 and actual_content_source = 'DNB'
1540 and subject_id=rel.subject_id );
1541
1542 BEGIN
1543
1544
1545 -- BEFORE WE MESS WITH ANY DNB DATA WE NEED TO CALL THE DNB POLICY FUNCTION
1546 hz_common_pub.disable_cont_source_security;
1547
1548
1549 FND_FILE.put_line(FND_FILE.log,'Start time to create DNB Hierarchy '||to_char(sysdate,'hh24:mi:ss'));
1550
1551 -- OPEN THE CURSOR THAT WILL GET YOU ALL ROOT PARTIES
1552 OPEN c_get_root_parties ;
1553
1554 -- LOOP THROUGH
1555 LOOP
1556 FETCH c_get_root_parties INTO l_root_object_id ;
1557
1558 -- CONSTRUCT THE TREE FOR EVERY ROOT PARTY
1559 ret_value := create_hierarchy_tree(l_root_object_id, -1, l_root_object_id );
1560
1561 EXIT WHEN c_get_root_parties%NOTFOUND ;
1562 END LOOP;
1563
1564 -- CLOSE CURSOR
1565 CLOSE c_get_root_parties;
1566
1567 FND_FILE.put_line(FND_FILE.log,'End time to create DNB Hierarchy '||to_char(sysdate,'hh24:mi:ss'));
1568
1569
1570
1571 END create_dnb_hierarchy ;
1572
1573
1574
1575 /**
1576 * PROCEDURE:: conform_party_to_dnb_hierarchy
1577 *
1578 * DESCRIPTION
1579 *
1580 *
1581 *
1582 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1583 *
1584 *
1585 * ARGUMENTS
1586 *
1587 *
1588 * NOTES
1589 *
1590 * MODIFICATION HISTORY
1591 *
1592 * 1-28-2003 Colathur Vijayan o Created.
1593 *
1594 */
1595
1596 -------------------------------------
1597 -- conform_party_to_dnb_hierarchy
1598 -- Main Procedure::: This procedure, will conform a purchased party to the DNB Hierarchy (created by
1599 -- running the DATA FIX SCRIPT).
1600 --
1601 -------------------------------------
1602 PROCEDURE conform_party_to_dnb_hierarchy (
1603 -- input parameters
1604 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1605 party_id IN NUMBER,
1606 parent_party_id IN NUMBER,
1607 dup_party_id IN NUMBER,
1608 gup_party_id IN NUMBER,
1609 parent_type_flag IN VARCHAR2,
1610 -- output parameters
1611 x_return_status OUT NOCOPY VARCHAR2,
1612 x_msg_count OUT NOCOPY NUMBER,
1613 x_msg_data OUT NOCOPY VARCHAR2
1614 )
1615 IS
1616 ret_value NUMBER;
1617 temp NUMBER;
1618 BEGIN
1619 -- initialize API return status to success.
1620 x_return_status := FND_API.G_RET_STS_SUCCESS;
1621
1622 -- If any of the passed in party ids or the parent type flag is null return the hell out of here
1623 IF party_id IS NULL
1624 OR
1625 parent_party_id IS NULL
1626 OR
1627 dup_party_id IS NULL
1628 OR
1629 gup_party_id IS NULL
1630 OR
1631 parent_type_flag is NULL
1632 THEN
1633 -- dbms_output.put_line ('One of the ids or the parent type flag is null');
1634 RETURN;
1635 END IF;
1636
1637 -- PARTY ALREADY EXISTS IN SOME BRANCH
1638 IF party_exists(party_id) = 'Y'
1639 THEN
1640 -- IF PARTY IS GUP, DO NOTHING
1641 IF party_is_a_gup(party_id) = 'Y'
1642 THEN
1643 NULL;
1644 -- PARTY HAS A HQ UNDER NEW REL TYPES
1645 ELSIF party_has_hq(party_id) = 'Y'
1646 THEN
1647 temp := get_hq_subject_id(party_id, 'DNB_HIERARCHY');
1648 IF parent_party_id = temp
1649 THEN
1650 ret_value := conform_dom_ult_of_party(party_id, parent_party_id, dup_party_id, gup_party_id);
1651 ELSE
1652 ret_value := end_date_rel(party_id, temp, 'DIVISION_OF');
1653 ret_value := create_rel_cps(party_id, parent_party_id, 'DIVISION_OF', SYSDATE );
1654 ret_value := conform_dom_ult_of_party(party_id, parent_party_id, dup_party_id, gup_party_id);
1655 IF is_a_descendant(party_id, parent_party_id) = 'Y'
1656 THEN
1657 ret_value := unlink_immediate_children(party_id, gup_party_id);
1658 END IF;
1659
1660 END IF;
1661 -- PARTY HAS A PARENT UNDER NEW REL TYPES
1662 ELSIF party_has_parent(party_id) = 'Y'
1663 THEN
1664 temp := get_parent_subject_id(party_id, 'DNB_HIERARCHY');
1665 IF parent_party_id = temp
1666 THEN
1667 ret_value := conform_dom_ult_of_party(party_id, parent_party_id, dup_party_id, gup_party_id);
1668 ELSE
1669 ret_value := end_date_rel(party_id, temp, 'SUBSIDIARY_OF');
1670 ret_value := create_rel_cps(party_id, parent_party_id, 'SUBSIDIARY_OF', SYSDATE );
1671 ret_value := conform_dom_ult_of_party(party_id, parent_party_id, dup_party_id, gup_party_id);
1672 IF is_a_descendant(party_id, parent_party_id) = 'Y'
1673 THEN
1674 ret_value := unlink_immediate_children(party_id, gup_party_id);
1675 END IF;
1676
1677 END IF;
1678 -- PARTY HAS A GLOBAL SUBSIDIARY RELATIONSHIP WITH ITS GUP UNDER NEW REL TYPES
1679 ELSIF party_has_gup_relationship(party_id, gup_party_id) = 'Y'
1680 THEN
1681 ret_value := end_date_rel(party_id, gup_party_id, 'GLOBAL_SUBSIDIARY_OF');
1682 IF parent_party_id = gup_party_id
1683 THEN
1684 IF parent_type_flag = 'P'
1685 THEN
1686 ret_value := create_rel_cps(party_id, gup_party_id, 'SUBSIDIARY_OF', SYSDATE );
1687 ELSIF parent_type_flag = 'H'
1688 THEN
1689 ret_value := create_rel_cps(party_id, gup_party_id, 'DIVISION_OF', SYSDATE );
1690 END IF;
1691 ELSE
1692 IF parent_type_flag = 'P'
1693 THEN
1694 ret_value := create_rel_cps(party_id, parent_party_id, 'SUBSIDIARY_OF', SYSDATE );
1695 ELSIF parent_type_flag = 'H'
1696 THEN
1697 ret_value := create_rel_cps(party_id, parent_party_id, 'DIVISION_OF',SYSDATE );
1698 END IF;
1699
1700 ret_value := conform_dom_ult_of_party(party_id, parent_party_id, dup_party_id, gup_party_id);
1701 IF is_a_descendant(party_id, parent_party_id) = 'Y'
1702 THEN
1703 ret_value := unlink_immediate_children(party_id, gup_party_id);
1704 END IF;
1705
1706 END IF;
1707
1708 -- PARTY HAS A DOMESTIC SUBSIDIARY RELATIONSHIP WITH ITS IMMEDIATE PARENT UNDER NEW REL TYPES
1709 ELSIF party_has_dup_relationship(party_id) = 'Y'
1710 THEN
1711 temp := get_dup_subject_id(party_id, 'DNB_HIERARCHY');
1712 ret_value := end_date_rel(party_id, temp, 'DOMESTIC_SUBSIDIARY_OF');
1713
1714 IF parent_party_id = temp
1715 THEN
1716 IF parent_type_flag = 'P'
1717 THEN
1718 ret_value := create_rel_cps(party_id, temp, 'SUBSIDIARY_OF', SYSDATE );
1719 ELSIF parent_type_flag = 'H'
1720 THEN
1721 ret_value := create_rel_cps(party_id, temp, 'DIVISION_OF', SYSDATE );
1722 END IF;
1723 ret_value := conform_dom_ult_of_party(party_id, parent_party_id, dup_party_id, gup_party_id);
1724 ELSE
1725 IF parent_type_flag = 'P'
1726 THEN
1727 ret_value := create_rel_cps(party_id, parent_party_id, 'SUBSIDIARY_OF', SYSDATE );
1728 ELSIF parent_type_flag = 'H'
1729 THEN
1730 ret_value := create_rel_cps(party_id, parent_party_id, 'DIVISION_OF', SYSDATE );
1731 END IF;
1732
1733 ret_value := conform_dom_ult_of_party(party_id, parent_party_id, dup_party_id, gup_party_id);
1734 IF is_a_descendant(party_id, parent_party_id) = 'Y'
1735 THEN
1736 ret_value := unlink_immediate_children(party_id, gup_party_id);
1737 END IF;
1738
1739 END IF;
1740
1741
1742
1743 END IF;
1744 -- PARTY DOES NOT EXIST IN ANY BRANCH
1745 ELSE
1746 -- DEPENDING ON THE FLAG, CREATE APPROPRIATE RELATIONSHIPS -- PARENT/HEADQUARTERS.
1747 IF parent_type_flag = 'H'
1748 THEN
1749 ret_value := create_rel_cps(party_id, parent_party_id, 'DIVISION_OF', SYSDATE );
1750 ret_value := conform_dom_ult_of_party(party_id, parent_party_id, dup_party_id, gup_party_id);
1751 ELSIF parent_type_flag = 'P'
1752 THEN
1753 ret_value := create_rel_cps(party_id, parent_party_id, 'SUBSIDIARY_OF', SYSDATE );
1754 ret_value := conform_dom_ult_of_party(party_id, parent_party_id, dup_party_id, gup_party_id);
1755 END IF;
1756
1757 END IF;
1758
1759
1760 -- CATCH ANY EXCEPTIONS THROWN WHEN RELATIONSHIPS ARE CREATED
1761 EXCEPTION
1762 WHEN OTHERS
1763 THEN
1764 x_return_status := FND_API.G_RET_STS_ERROR ;
1765 -- standard call to get message count and if count is 1, get message info.
1766 FND_MSG_PUB.Count_And_Get(
1767 p_encoded => FND_API.G_FALSE,
1768 p_count => x_msg_count,
1769 p_data => x_msg_data);
1770 END conform_party_to_dnb_hierarchy ;
1771
1772
1773 END; -- Package Body HZ_DNB_HIERARCHY_PVT
1774
1775
1776
[Home] [Help]