How to use Change PO API group package PO_DOCUMENT_UPDATE_GRP to make multiple changes to PO at once

REM Refer to metalink Doc ID 1406047.1 for more details
REM Note:  This API supports only set of attributes at 
REM line/shipment/distribution level as listed above with 
REM Approval option.
REM Any other PO attributes  in Type PO_CHANGES_REC_TYPE then listed REM above are  reserved for Internal References/Usage and not
REM supported via this API.REM 1. PO_CHANGES_REC_TYPE:
REM This object represents header of PO. It can be created by supplying header_id of a PO.
REM e.g.
REM l_header PO_CHANGES_REC_TYPE;
REM l_header.create_object(p_po-header_id => <po_header_id>, p_po_release_id => <release_id>);

REM In case of a PO, release_id should be supplied and release_id will be null. Release_id is used while creating object of release header.

REM Optional parameters to create object are:
REM 1. p_line_changes (PO_LINES_REC_TYPE)
REM 2. p_shipment_changes(PO_SHIPMENTS_REC_TYPE)
REM 3. p_distribution_changes (PO_DISTRIBUTIONS_REC_TYPE)

REM These parameters are the objects of line, shipment and distribution respectively.

REM 2. PO_LINES_REC_TYPE:

REM This object represents line in a PO. It can be created using line_id of a line.

REM e.g.
REM l_line PO_LINES_REC_TYPE;
REM l_line.create_object(p_po_line_id => <line_id>);

REM create_object method takes following optional parameters which represent change in line.

REM 1. Unit price
REM 2. Vendor product number
REM 3. Quantity
REM 4. Start Date
REM 5. Expiration date
REM 6. Amount
REM 7. Unit of measure
REM 8. Secondary Quantity
REM 9. Secondary UOM

REM 3. PO_SHIPMENTS_REC_TYPE:

REM This object represents shipment in a PO. It can be created using line_location_id of a line.

REM e.g.
REM l_ship PO_SHIPMENTS_REC_TYPE;
REM l_ship.create_object(p_po_line_location_id => <line_location_id>);

REM create_object method takes following optional parameters which represent change in line.

REM 1. Quantity
REM 2. promised date
REM 3. Price override
REM 4. Parent line location id -- To copy data from other existing shipment.
REM 5. Need by date
REM 6. Ship to location id
REM 7. Sales order update date
REM 8. Amount
REM 9. Unit of measure
REM 10. Secondary quantity
REM 11. Secondary UOM

REM 4. PO_DISTRIBUTIONS_REC_TYPE:

REM This object represents distribution in a PO. It can be created using distribution_id of a line.

REM e.g.
REM l_dist PO_DISTRIBUTIONS_REC_TYPE;
REM l_dist.create_object(p_po_distribution_id => <distribution_id>);

REM create_object method takes following optional parameters which represent change in line.

REM 1. Quantity ordered
REM 2. Parent distribution id
REM 3. Amount ordered

REM 4. Unit of measure

REM Simple Update 

DECLARE
   l_result                  NUMBER;
   l_progress                NUMBER;
   l_errors                  po_api_errors_rec_type;
   l_chg                     po_changes_rec_type;
   l_shipment_changes        po_shipments_rec_type;
   l_return_status           VARCHAR2(30);
BEGIN
   --to set org context in a R12 env
   mo_global.set_policy_context('S'
                              , &org_id);

   -- Create an Object for Changes
   -- po_changes_rec_type constructor takes either po_header_id OR      --- po_release_id to construct the object. In case of purchase 
   ---order pass po_release_id as NULL.

   l_chg                                                    :=
      po_changes_rec_type.create_object(
                           p_po_header_id  => &header_id
                         , p_po_release_id => &release_id);

   -- Add a Line Changes to the Change Object
   l_chg.line_changes.add_change(
                           p_po_line_id      => &po_line_id
                         , p_quantity        => &value);

   -- Add Shipment Changes to the Change Object
   l_chg.shipment_changes.add_change(
                   p_po_line_location_id => &line_location_id
                 , p_quantity         => &value);

   -- Add Distribution Level Changes
   l_chg.distribution_changes.add_change(
    p_po_distribution_id    => &po_distribution_id
  , p_quantity_ordered      => &value);

   -- Now call the change api to execute the above changes
   po_document_update_grp.update_document(
      p_api_version    => 1.0 -- pass this as 1.0
    , p_init_msg_list  => fnd_api.g_true -- pass this as TRUE
    , x_return_status  => l_return_status -- returns the result of 
 ---execution
    , p_changes        => l_chg -- changes obj. contains all changes    --- intended to be made on document
    , p_run_submission_checks  => fnd_api.g_false -- set to TRUE if --- want to perform submission check
    , p_launch_approvals_flag  => fnd_api.g_fals
-- set to TRUE if want to launch approval work flow after making the --changes
    , p_buyer_id       => NULL -- buyer id
    , p_update_source  => NULL -- name of a source who is calling     --- this API. In case of manual call can be passed as NULL
    , p_override_date  => NULL
    , x_api_errors     => l_errors -- list of errors if any occurred -- in execution
    , p_mass_update_releases   => NULL);

   IF l_errors IS NOT NULL
   THEN
      FOR i IN 1 .. l_errors.MESSAGE_TEXT.COUNT
      LOOP
         DBMS_OUTPUT.put_line(   ' Error is '
                              || l_errors.MESSAGE_TEXT( i)
                              || ' - name'
                              || l_errors.message_name( i));
      END LOOP;
   END IF;

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line(   'error :'
                           || SQLERRM);
END;


REM Update and Approve 
DECLARE
   l_result                                     NUMBER;
   l_progress                                   NUMBER;
   l_errors              po_api_errors_rec_type;
   l_chg                 po_changes_rec_type;
   l_shipment_changes    po_shipments_rec_type;
   l_return_status       VARCHAR2(30);
BEGIN
   fnd_global.apps_initialize(user_id                                   => &user_id
                            , -- This function execution is required before launching the approval work flow.
                             resp_id                                    => &responsibility_id
                            , resp_appl_id                              => &resp_application_id);

   --to set org context in a R12 env
   mo_global.set_policy_context('S'
                              , &org_id);

   -- Create an Object for Changes
   -- po_changes_rec_type constructor takes either po_header_id OR      -- po_release_id to construct the object.
   -- In case of purchase order pass po_release_id as NULL.

   l_chg                                                    :=
      po_changes_rec_type.create_object(
    p_po_header_id            => &header_id
  , p_po_release_id           => &release_id);

   -- Add a Line Changes to the Change Object
   l_chg.line_changes.add_change(
      p_po_line_id   => &po_line_id
    , p_quantity     => &value);

   -- Add Shipment Changes to the Change Object
   l_chg.shipment_changes.add_change(
    p_po_line_location_id    => &line_location_id
  , p_quantity               => &value);

   -- Add Distribution Level Changes
   l_chg.distribution_changes.add_change(
       p_po_distribution_id    => &po_distribution_id
     , p_quantity_ordered      => &value);

   -- Now call the change api to execute the above changes
   po_document_update_grp.update_document(
  p_api_version    => 1.0 -- pass this as 1.0
, p_init_msg_list  => fnd_api.g_true -- pass this as TRUE
, x_return_status  => l_return_status -- returns the result of 
  --- execution
, p_changes        => l_chg -- changes obj. contains all changes 
  ---intended to be made on document
, p_run_submission_checks => fnd_api.g_false  -- set to TRUE if want --- to perform submission check
, p_launch_approvals_flag  => fnd_api.g_true -- set to TRUE if want   ---to launch approval work flow after making the changes
, p_buyer_id               => NULL -- buyer id
, p_update_source          => NULL -- name of a source who is 
  ---calling this API. In case of manual call can be passed as NULL
, p_override_date          => NULL
, x_api_errors             => l_errors -- list of errors if any 
   --- occurred in execution
, p_mass_update_releases   => NULL);


   IF l_errors IS NOT NULL
   THEN
      FOR i IN 1 .. l_errors.MESSAGE_TEXT.COUNT
      LOOP
         DBMS_OUTPUT.put_line(   ' Error is '
                              || l_errors.MESSAGE_TEXT( i)
                              || ' - name'
                              || l_errors.message_name( i));
      END LOOP;
   END IF;

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line(   'error :'
                           || SQLERRM);
END;

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;

API to create Instance relationship in Oracle install Base

PROCEDURE csi_create_relationship(
   p_relationship_tbl IN     csi_datastructures_pub.ii_relationship_tbl
 , x_return_status                                 OUT VARCHAR2
 , x_return_msg                                    OUT VARCHAR2)
IS
   l_relationship_id        NUMBER;
   l_txn_rec                csi_datastructures_pub.transaction_rec;
   l_msg_index_out          NUMBER;
   x_msg_count              NUMBER;
   x_msg_data               VARCHAR2(500);
BEGIN
   fnd_msg_pub.initialize;
   l_relationship_id             := csi_ii_relationships_s.NEXTVAL;
   p_relationship_tbl( 1).relationship_id := l_relationship_id;
   p_relationship_tbl( 1).relationship_type_code := 'COMPONENT-OF';
   p_relationship_tbl( 1).object_id              := 4959909;
   p_relationship_tbl( 1).subject_id             := 4959910;
   p_relationship_tbl( 1).subject_has_child      := 'N';
   p_relationship_tbl( 1).position_reference     := NULL;
   p_relationship_tbl( 1).active_start_date      := SYSDATE;
   p_relationship_tbl( 1).active_end_date        := NULL;
   p_relationship_tbl( 1).display_order          := NULL;
   p_relationship_tbl( 1).mandatory_flag         := 'N';
   p_relationship_tbl( 1).context                := NULL;
   p_relationship_tbl( 1).attribute1             := NULL;
   p_relationship_tbl( 1).attribute2             := NULL;
   p_relationship_tbl( 1).attribute3             := NULL;
   p_relationship_tbl( 1).attribute4             := NULL;
   p_relationship_tbl( 1).attribute5             := NULL;
   p_relationship_tbl( 1).attribute6             := NULL;
   p_relationship_tbl( 1).attribute7             := NULL;
   p_relationship_tbl( 1).attribute8             := NULL;
   p_relationship_tbl( 1).attribute9             := NULL;
   p_relationship_tbl( 1).attribute10            := NULL;
   p_relationship_tbl( 1).attribute11            := NULL;
   p_relationship_tbl( 1).attribute12            := NULL;
   p_relationship_tbl( 1).attribute13            := NULL;
   p_relationship_tbl( 1).attribute14            := NULL;
   p_relationship_tbl( 1).attribute15            := NULL;
   p_relationship_tbl( 1).object_version_number  := 1;

   -- Setting the transaction record type
   l_txn_rec.transaction_date            := TRUNC( SYSDATE);
   l_txn_rec.source_transaction_date     := TRUNC( SYSDATE);
   l_txn_rec.transaction_type_id         := 1;
   l_txn_rec.object_version_number       := 1;
   --
   csi_ii_relationships_pub.create_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_tbl         => p_relationship_tbl
    , p_txn_rec                                 => l_txn_rec
    , 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( 'Relationship created 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 x_return_msg IS NULL
            THEN
               x_return_msg     :=
                     l_msg_index_out
                  || ':'
                  || x_msg_data;
            ELSE
               x_return_msg     :=
                     x_return_msg
                  || '/'
                  || l_msg_index_out
                  || ':'
                  || x_msg_data;
            END IF;
         END LOOP;
      END IF;

      DBMS_OUTPUT.put_line(   'Relationship creation failure : '
                           || x_return_msg);
   END IF;
END;

API to Expire Instance in Oracle Install Base

DECLARE
   l_record_status                              VARCHAR2(1);
   l_sv_status                                  VARCHAR2(2000);
   l_msg_data                                   VARCHAR2(2000);
   l_mesg                                       VARCHAR2(4000);
   l_mesg_len                                   NUMBER;
   l_mesg_count                                 NUMBER;


   SUBTYPE instance_rec IS csi_datastructures_pub.instance_rec;

   SUBTYPE transaction_rec IS csi_datastructures_pub.transaction_rec;

   SUBTYPE id_tbl IS csi_datastructures_pub.id_tbl;

   SUBTYPE instance_query_rec IS csi_datastructures_pub.instance_query_rec;

   SUBTYPE party_query_rec IS csi_datastructures_pub.party_query_rec;

   SUBTYPE party_account_query_rec IS csi_datastructures_pub.party_account_query_rec;

   SUBTYPE instance_header_tbl IS csi_datastructures_pub.instance_header_tbl;

   -- Get Item Instance parameters
   l_instance_query_rec                         instance_query_rec;
   l_party_query_rec                            party_query_rec;
   l_account_query_rec                          party_account_query_rec;
   l_instance_header_tbl                        instance_header_tbl;

   -- Expire Item Instance parameters
   l_instance_rec                               instance_rec;
   l_txn_rec                                    transaction_rec;
   l_instance_id_lst                            id_tbl;

   l_return_status       VARCHAR2(1) := okl_api.g_ret_sts_success;
   l_overall_status      VARCHAR2(1) := okl_api.g_ret_sts_success;

   l_api_name            CONSTANT VARCHAR2(30) := 'expire_item';
   l_api_version         CONSTANT NUMBER := 1;
   l_msg_count           NUMBER := fnd_api.g_miss_num;
BEGIN
   l_instance_query_rec.instance_id  := 5321932;

   csi_item_instance_pub.get_item_instances(
   p_api_version          => l_api_version
 , p_commit               => fnd_api.g_false
 , p_init_msg_list        => fnd_api.g_false
 , p_validation_level     => fnd_api.g_valid_level_full
 , p_instance_query_rec   => l_instance_query_rec
 , p_party_query_rec      => l_party_query_rec
 , p_account_query_rec    => l_account_query_rec
 , p_transaction_id       => NULL
 , p_resolve_id_columns   => fnd_api.g_false
 , p_active_instance_only => fnd_api.g_true
 , x_instance_header_tbl  => l_instance_header_tbl
 , x_return_status        => l_return_status
 , x_msg_count            => l_msg_count
 , x_msg_data             => l_msg_data);


l_instance_rec.instance_id:= l_instance_header_tbl( 1).instance_id;
l_instance_rec.object_version_number:=l_instance_header_tbl( 1).object_version_number;
   l_instance_rec.active_end_date   := SYSDATE;

   l_txn_rec.transaction_date         := SYSDATE;
   l_txn_rec.source_transaction_date  := SYSDATE;
   l_txn_rec.transaction_id           := NULL;
   l_txn_rec.transaction_type_id      := 1;

   -- **************************************
   -- Call Installed Base API to expire item
   -- **************************************

   csi_item_instance_pub.expire_item_instance(
   p_api_version                             => l_api_version
 , p_commit                                  => fnd_api.g_false
 , p_init_msg_list                           => fnd_api.g_false
 , p_validation_level                        => fnd_api.g_valid_level_full
 , p_instance_rec                            => l_instance_rec
 , p_expire_children                         => fnd_api.g_true
 , p_txn_rec                                 => l_txn_rec
 , x_instance_id_lst                         => l_instance_id_lst
 , x_return_status                           => l_return_status
 , x_msg_count                               => l_msg_count
 , x_msg_data                                => l_msg_data);

   IF l_return_status = 'S'
   THEN
      DBMS_OUTPUT.put_line(   'instance '
                           || l_instance_rec.instance_id
                           || ' expired successfully');
   -- **************************************
   -- Display errors encounted for the expiration
   -- **************************************
   ELSE
      l_mesg_count          := fnd_msg_pub.count_msg;

      IF l_mesg_count > 0
      THEN
         l_mesg             :=
               CHR( 10)
            || SUBSTR(fnd_msg_pub.get(fnd_msg_pub.g_first
                                    , fnd_api.g_false)
                    , 1
                    , 512);

         FOR i IN 1 .. (l_mesg_count - 1)
         LOOP
            l_mesg      :=
                  l_mesg
               || CHR( 10)
               || SUBSTR(fnd_msg_pub.get(fnd_msg_pub.g_next
                                       , fnd_api.g_false)
                       , 1
                       , 512);
         END LOOP;

         fnd_msg_pub.delete_msg();

         l_mesg_len           := LENGTH( l_mesg);

         FOR i IN 1 .. CEIL( l_mesg_len / 255)
         LOOP
            DBMS_OUTPUT.put_line(SUBSTR(l_mesg
                                      , ((i * 255) - 254)
                                      , 255));
         END LOOP;
      END IF;
   END IF;
END;
/