Script to get the Child Instances (Subject id) for requested Parent Instance (Object id ) in Install Base.

CREATE OR REPLACE FUNCTION get_children( p_object_id IN NUMBER)
   RETURN csi_datastructures_pub.ii_relationship_tbl
IS
   --
   l_rel_tbl          csi_datastructures_pub.ii_relationship_tbl;
   l_rel_tbl_next_lvl csi_datastructures_pub.ii_relationship_tbl;
   l_rel_tbl_temp     csi_datastructures_pub.ii_relationship_tbl;
   l_rel_tbl_final    csi_datastructures_pub.ii_relationship_tbl;
   p_rel_tbl          csi_datastructures_pub.ii_relationship_tbl;
   l_next_ind         NUMBER := 0;
   l_final_ind        NUMBER := 0;
   l_ctr              NUMBER := 0;
   l_found            NUMBER;

   PROCEDURE get_next_level(
    p_object_id     IN            NUMBER
  , p_rel_tbl OUT NOCOPY csi_datastructures_pub.ii_relationship_tbl)
   IS
      --
      CURSOR rel_cur
      IS
         SELECT relationship_id
              , relationship_type_code
              , object_id
              , subject_id
              , position_reference
              , active_start_date
              , active_end_date
              , display_order
              , mandatory_flag
              , context
              , attribute1
              , attribute2
              , attribute3
              , attribute4
              , attribute5
              , attribute6
              , attribute7
              , attribute8
              , attribute9
              , attribute10
              , attribute11
              , attribute12
              , attribute13
              , attribute14
              , attribute15
              , object_version_number
           FROM csi_ii_relationships cir
          WHERE cir.object_id = p_object_id;

      --
      l_ctr                                        NUMBER := 0;
   BEGIN
      FOR rel IN rel_cur
      LOOP
l_ctr               := l_ctr + 1;
p_rel_tbl( l_ctr).relationship_id := rel.relationship_id;
p_rel_tbl( l_ctr).relationship_type_code:= rel.relationship_type_code;
p_rel_tbl( l_ctr).object_id           := rel.object_id;
p_rel_tbl( l_ctr).subject_id          := rel.subject_id;
p_rel_tbl( l_ctr).position_reference  := rel.position_reference      p_rel_tbl( l_ctr).active_start_date   := rel.active_start_date;
p_rel_tbl( l_ctr).active_end_date     := rel.active_end_date;
p_rel_tbl( l_ctr).display_order       := rel.display_order;
p_rel_tbl( l_ctr).mandatory_flag      := rel.mandatory_flag;
p_rel_tbl( l_ctr).context             := rel.context;
p_rel_tbl( l_ctr).attribute1          := rel.attribute1;
p_rel_tbl( l_ctr).attribute2          := rel.attribute2;
p_rel_tbl( l_ctr).attribute3          := rel.attribute3;
p_rel_tbl( l_ctr).attribute4          := rel.attribute4;
p_rel_tbl( l_ctr).attribute5          := rel.attribute5;
p_rel_tbl( l_ctr).attribute6          := rel.attribute6;
p_rel_tbl( l_ctr).attribute7          := rel.attribute7;
p_rel_tbl( l_ctr).attribute8          := rel.attribute8;
p_rel_tbl( l_ctr).attribute9          := rel.attribute9;
p_rel_tbl( l_ctr).attribute10         := rel.attribute10;
p_rel_tbl( l_ctr).attribute11         := rel.attribute11;
p_rel_tbl( l_ctr).attribute12         := rel.attribute12;
p_rel_tbl( l_ctr).attribute13         := rel.attribute13;
p_rel_tbl( l_ctr).attribute14         := rel.attribute14;
p_rel_tbl( l_ctr).attribute15         := rel.attribute15;
p_rel_tbl( l_ctr).object_version_number:= rel.object_version_number;
      END LOOP;
   END get_next_level;
BEGIN
   get_next_level(p_object_id          => p_object_id
                , p_rel_tbl            => l_rel_tbl);

  <<Next_Level>>
   l_rel_tbl_next_lvl.delete;
   l_next_ind                                               := 0;

   --
   IF l_rel_tbl.COUNT > 0
   THEN
      FOR l_ind IN l_rel_tbl.FIRST .. l_rel_tbl.LAST
      LOOP
         l_final_ind                   := l_final_ind + 1;
         l_rel_tbl_final( l_final_ind) := l_rel_tbl( l_ind);

        /* get the next level using this Subject ID as the parent */

         get_next_level(p_object_id => l_rel_tbl( l_ind).subject_id
                      , p_rel_tbl   => l_rel_tbl_temp);

         --
         IF l_rel_tbl_temp.COUNT > 0
         THEN
            FOR l_temp_ind IN l_rel_tbl_temp.FIRST .. l_rel_tbl_temp.LAST
            LOOP
               IF l_rel_tbl_final.COUNT > 0
               THEN
                  l_found       := 0;

                  FOR i IN l_rel_tbl_final.FIRST .. l_rel_tbl_final.LAST
                  LOOP
                     IF l_rel_tbl_final( i).object_id = l_rel_tbl_temp( l_temp_ind).object_id
                     THEN
                        l_found           := 1;
                        EXIT;
                     END IF;
                  END LOOP;
               END IF;

               IF l_found = 0
               THEN
                  l_next_ind       := l_next_ind + 1;
                  l_rel_tbl_next_lvl( l_next_ind):= l_rel_tbl_temp( l_temp_ind);
               END IF;
            END LOOP;
         END IF;
      END LOOP;

      --
      IF l_rel_tbl_next_lvl.COUNT > 0
      THEN
         l_rel_tbl.delete;
         l_rel_tbl             := l_rel_tbl_next_lvl;
         --
         GOTO next_level;
      END IF;
   END IF;

   --
   p_rel_tbl                   := l_rel_tbl_final;
   RETURN p_rel_tbl;
--
-- The output of l_rel_tbl_final will be Breadth first search Order.
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line(   'UNEXP_ERROR : '
                           || SUBSTR(SQLERRM
                                   , 1
                                   , 250));
      RETURN p_rel_tbl;
END get_children;