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;

API for Expire Instance relationship in Oracle install base

PROCEDURE csi_expire_relationship( p_object_id IN NUMBER)
IS
   l_relationship_rec                           csi_datastructures_pub.ii_relationship_rec;
   l_txn_rec                                    csi_datastructures_pub.transaction_rec;
   x_instance_id_lst                            csi_datastructures_pub.id_tbl;
   l_msg_index_out                              NUMBER;
   l_return_msg                                 VARCHAR2(4000);
   x_return_status                              VARCHAR2(2000);
   x_msg_count                                  NUMBER;
   x_msg_data                                   VARCHAR2(500);

   CURSOR rel_cur
   IS
      SELECT relationship_id
           , object_version_number
        FROM csi_ii_relationships
       WHERE object_id = p_object_id
         AND relationship_type_code = 'COMPONENT-OF'
         AND NVL(active_end_date, SYSDATE + 1) > SYSDATE;
BEGIN
   FOR rel_rec IN rel_cur
   LOOP
    l_relationship_rec.relationship_id := rel_rec.relationship_id;
    l_relationship_rec.object_version_number:= rel_rec.object_version_number;
    l_txn_rec.transaction_id              := NULL;
      l_txn_rec.transaction_date          := SYSDATE;
      l_txn_rec.source_transaction_date   := SYSDATE;
      l_txn_rec.transaction_type_id       := 1;

      fnd_msg_pub.initialize;

      csi_ii_relationships_pub.expire_relationship(
     p_api_version               => 1.0
   , p_commit                    => fnd_api.g_false
   , p_init_msg_list             => fnd_api.g_true
   , p_validation_level          => fnd_api.g_valid_level_full
   , p_relationship_rec          => l_relationship_rec
   , p_txn_rec                   => l_txn_rec
   , x_instance_id_lst           => x_instance_id_lst
   , x_return_status             => x_return_status
   , x_msg_count                 => x_msg_count
   , x_msg_data                  => x_msg_data);

      IF x_return_status = fnd_api.g_ret_sts_success
      THEN
         DBMS_OUTPUT.put_line( 'Expired relationship Successfully ');
         COMMIT;
      ELSE
         IF x_msg_count > 0
         THEN
            FOR i IN 1 .. x_msg_count
            LOOP
               fnd_msg_pub.get(
     p_msg_index                               => i
   , p_encoded                                 => fnd_api.g_false
   , p_data                                    => x_msg_data
   , p_msg_index_out                           => l_msg_index_out);

               IF l_return_msg IS NULL
               THEN
                  l_return_msg     :=
                        l_msg_index_out
                     || ':'
                     || x_msg_data;
               ELSE
                  l_return_msg  :=
                        l_return_msg
                     || '/'
                     || l_msg_index_out
                     || ':'
                     || x_msg_data;
               END IF;
            END LOOP;
         END IF;

         DBMS_OUTPUT.put_line('Expire instance relationship API failure : '                         || NVL(l_return_msg, x_msg_data));
      END IF;
   END LOOP;
END;