Install Base Expiration Program

CREATE OR REPLACE PROCEDURE apps.xxstx_expire_ib_instance
IS
   l_instance_rec            csi_datastructures_pub.instance_rec;
   l_txn_rec                 csi_datastructures_pub.transaction_rec;
   l_instance_id_lst         csi_datastructures_pub.id_tbl;
   l_cust_account_id         NUMBER;
   l_party_id                NUMBER;
   l_line_type_id            NUMBER;
   l_msg_index_out           NUMBER;                         -- scratch index
   x_return_status           VARCHAR2 (1);
   x_msg_data                VARCHAR2 (4000);
   x_msg_count               NUMBER;
   g_message                 VARCHAR2 (4000);
   l_contract_number         NUMBER;
   l_inventory_item_id       NUMBER;
   l_segment1                VARCHAR2 (100);
   l_serial_number           VARCHAR2 (100);
   l_start_date              DATE;
   l_count                   NUMBER;
   l_count1                  NUMBER;
   l_instance_id             VARCHAR2 (20);
   l_msg_data                VARCHAR2 (2000);
   l_mesg                    VARCHAR2 (4000);
   l_mesg_count              NUMBER;
   l_item                    VARCHAR2 (100);
   l_instance_number         VARCHAR2 (150);
   l_inv_master_org_id       NUMBER;
   l_object_version_number   NUMBER;
   no_need_expire            EXCEPTION;

   CURSOR c1
   IS
      SELECT *
        FROM xxstx_bulk_exp_temp where REC_STS IS NULL;  --and serial_number = 'DMPJG2NNF18Y'; --Temp Table to store data, Custome table.

BEGIN
   -- fnd_global.apps_initialize(1230,22935,542);
   -- mo_global.set_policy_context('S',82);
   DBMS_OUTPUT.put_line ('IN FIRST BEGIN');

   FOR i IN c1
   LOOP
      DBMS_OUTPUT.put_line (   'Values got from Cursor = Serial_number:'
                            || i.serial_number
                            || ',item:'
                            || i.item
                           );

      BEGIN
         SELECT cii.instance_id, cii.serial_number, cii.inventory_item_id,
                cii.instance_number, cii.inv_master_organization_id,
                cii.object_version_number
           INTO l_instance_id, l_serial_number, l_inventory_item_id,
                l_instance_number, l_inv_master_org_id,
                l_object_version_number
           FROM csi_item_instances cii, mtl_system_items mtl
          WHERE cii.serial_number = i.serial_number            --'35200400288'
            AND mtl.segment1 = i.item                     --'760B85G4E800N000'
            AND mtl.organization_id = 101
            AND mtl.inventory_item_id = cii.inventory_item_id
            AND cii.instance_number =
                                  NVL (i.instance_number, cii.instance_number);

         BEGIN
            DBMS_OUTPUT.put_line (   'Values got from Query :'
                                  || l_instance_id
                                  || l_serial_number
                                  || l_inventory_item_id
                                  || l_instance_number
                                  || l_inv_master_org_id
                                  || l_object_version_number
                                 );

            SELECT COUNT (contract_number)
              INTO l_count
              FROM (SELECT contract_number
                      FROM okc_k_headers_all_b
                     WHERE ID IN (SELECT MAX (dnz_chr_id)
                                    FROM okc_k_items
                                   WHERE object1_id1 = l_instance_id)
                       AND sts_code IN
                                   ('ACTIVE', 'QA_HOLD', 'SIGNED', 'ENTERED')
                    UNION
                    SELECT contract_number
                      FROM okc_k_headers_all_b
                     WHERE ID IN (SELECT MAX (dnz_chr_id)
                                    FROM okc_k_items
                                   WHERE object1_id1 = l_inventory_item_id)
                       AND sts_code IN
                                   ('ACTIVE', 'QA_HOLD', 'SIGNED', 'ENTERED'));

            DBMS_OUTPUT.put_line
                  (   'If statement for serial nas active number of contracts:  '
                   || l_count
                  );

            IF l_count = 0
            THEN
               DBMS_OUTPUT.put_line ('If statement if  count =0');
               l_instance_rec.instance_id := l_instance_id;
               l_instance_rec.instance_number := l_instance_number;
               l_instance_rec.external_reference := NULL;
               l_instance_rec.inventory_item_id := l_inventory_item_id;
               l_instance_rec.inv_master_organization_id :=
                                                          l_inv_master_org_id;
               l_instance_rec.active_end_date := TRUNC (SYSDATE);
               l_instance_rec.object_version_number :=
                                                      l_object_version_number;
               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;
               DBMS_OUTPUT.put_line ('EXPIRE INSATCNE');
               csi_item_instance_pub.expire_item_instance
                                     (p_api_version          => 1.0
                                                                   -- IN      NUMBER
               ,
                                      p_instance_rec         => l_instance_rec
                                                                              -- IN      csi_datastructures_pub.instance_rec
               ,
                                      p_txn_rec              => l_txn_rec
                                                                         -- IN OUT  NOCOPY csi_datastructures_pub.transaction_rec
               ,
                                      x_instance_id_lst      => l_instance_id_lst
                                                                                 -- OUT     NOCOPY csi_datastructures_pub.id_tbl
               ,
                                      x_return_status        => x_return_status
                                                                               -- OUT     NOCOPY VARCHAR2
               ,
                                      x_msg_count            => x_msg_count
                                                                           -- OUT     NOCOPY NUMBER
               ,
                                      x_msg_data             => x_msg_data
                                     -- OUT     NOCOPY VARCHAR2
                                     );
               DBMS_OUTPUT.put_line ('EXPIRE INSATCNE DONE');
               DBMS_OUTPUT.put_line ('API Status:=' || x_return_status);

               IF x_return_status != apps.fnd_api.g_ret_sts_success
               THEN
                  DBMS_OUTPUT.put_line
                     ('failed. printing error msg... at owner and LOCATION changes'
                     );
                  fnd_msg_pub.get (p_msg_index          => apps.fnd_msg_pub.g_last,
                                   p_encoded            => apps.fnd_api.g_false,
                                   p_data               => x_msg_data,
                                   p_msg_index_out      => l_msg_index_out
                                  );
                  g_message := x_msg_data;
                  --fnd_file.put_line (fnd_file.LOG,
                               --          'Instance # '
                                ----      || l_instance_number                                     || ' could not be expired. Error: '                                    || g_message
                                --      );
                  DBMS_OUTPUT.put_line (   'Instance # '
                                        || l_instance_number
                                        || ' could not be expired. Error: '
                                        || g_message
                                       );
                  ROLLBACK;
               ELSE
                  DBMS_OUTPUT.put_line
                     (' IB instance updated sucessfully at owner and LOCATION changes '
                     );
                  DBMS_OUTPUT.put_line ('Step8 : end update API -');
                  NULL;
               END IF;

               UPDATE xxstx_bulk_exp_temp
                  SET rec_sts = 'PROCESSED'
                WHERE serial_number = l_serial_number;

               DBMS_OUTPUT.put_line ('UPDATED TABLE');
            ELSE
               UPDATE xxstx_bulk_exp_temp
                  SET rec_sts = 'ACTIVE CONTRACT'
                WHERE serial_number = l_serial_number;

               DBMS_OUTPUT.put_line ('l_count is More than 0' || l_count);
            END IF;
         END;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            DBMS_OUTPUT.put_line (' Exception NO DATA FOUND');
      END;
   END LOOP;
   COMMIT;
EXCEPTION
   WHEN no_need_expire
   THEN
      DBMS_OUTPUT.put_line (' no_need_expire NO DATA FOUND');
   WHEN OTHERS
   THEN
      fnd_file.put_line (fnd_file.LOG, 'Error in Main:=' || SQLERRM);
END xxstx_expire_ib_instance;
/