create stock for exists LOT or increase onhand quantity for existing LOT

DECLARE
   v_object_id       NUMBER;
   v_return_status   VARCHAR2 (1)    := NULL;
   v_msg_count       NUMBER          := 0;
   v_msg_data        VARCHAR2 (2000);
   l_org_code        VARCHAR2 (240);
   retval            NUMBER;
   ln_interface_id   NUMBER;
   l_return_status   VARCHAR2 (100);
   l_msg_cnt         NUMBER;
   l_msg_data        VARCHAR2 (500);
   l_trans_count     NUMBER;
   v_header_id       NUMBER;
   ln_out            NUMBER;
   ln_quantity       NUMBER;
   ln_trn_type_id    NUMBER;
   l_error           VARCHAR2 (240);
   l_locator_id      NUMBER;
   l_uom             VARCHAR2 (240);
   p_sub_inv         VARCHAR2 (240)  := 'HIMPRD';
   l_revision        VARCHAR2 (240);

   CURSOR c1
   IS
      SELECT *
        FROM mtl_lot_numbers
       WHERE lot_number IN
                ('035636');
BEGIN
   FOR i IN c1
   LOOP
      BEGIN
         SELECT inventory_location_id
           INTO l_locator_id
           FROM mtl_item_locations
          WHERE segment1 = 'W300';
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;

      BEGIN
         SELECT primary_uom_code
           INTO l_uom
           FROM mtl_system_items_b
          WHERE organization_id = i.organization_id
            AND inventory_item_id = i.inventory_item_id;
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;

      BEGIN
         SELECT MAX (revision)
           INTO l_revision
           FROM mtl_item_revisions
          WHERE inventory_item_id IN (
                   SELECT inventory_item_id
                     FROM mtl_lot_numbers
                    WHERE lot_number = i.lot_number
                      AND organization_id = i.organization_id)
            AND organization_id = i.organization_id;
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;

      BEGIN
         SELECT mtl_material_transactions_s.NEXTVAL
           INTO ln_interface_id
           FROM DUAL;
      EXCEPTION
         WHEN OTHERS
         THEN
            ln_interface_id := 0;
      END;

      BEGIN
         SELECT transaction_type_id
           INTO ln_trn_type_id
           FROM mtl_transaction_types
          WHERE transaction_type_name LIKE 'Miscellaneous receipt%';
      EXCEPTION
         WHEN OTHERS
         THEN
            ln_trn_type_id := 0;
      END;

      BEGIN
         INSERT INTO mtl_transactions_interface
                     (source_code, source_line_id, source_header_id,
                      process_flag, transaction_mode, inventory_item_id,
                      organization_id, subinventory_code, locator_id,
                      transaction_quantity, transaction_uom,
                      transaction_date, transaction_type_id,
                      transfer_subinventory, lock_flag, last_update_date,
                      last_updated_by, created_by, creation_date,
                      transaction_interface_id, transfer_locator, revision,
                      distribution_account_id, primary_quantity
                     )
              VALUES ('Miscellaneous receipt', 99, 99,
                      1, 3, i.inventory_item_id,
                      i.organization_id, p_sub_inv, l_locator_id,
                      10000, l_uom,
                      SYSDATE, ln_trn_type_id,
                      NULL, 2, SYSDATE,
                      3842, 3842, SYSDATE,
                      ln_interface_id, NULL, l_revision,
                      35449, 10000
                     );
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line
                   (   'Error while inserting into MTL_TRANSACTION_INTERFACE'
                    || SQLERRM
                    || SQLCODE
                   );
      END;

      ln_quantity := 10000;

      BEGIN
         INSERT INTO mtl_transaction_lots_interface
                     (transaction_interface_id, lot_number,
                      lot_expiration_date, transaction_quantity,
                      last_update_date, last_updated_by, creation_date,
                      created_by, primary_quantity
                     )
              VALUES (ln_interface_id, i.lot_number,
                      i.expiration_date, 10000,
                      SYSDATE, 3842, SYSDATE,
                      3842, 10000
                     );
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line
               (   'Error while inserting into MTL_TRANSACTIONS_LOT_INTERFACE'
                || SQLERRM
                || SQLCODE
               );
      END;

      BEGIN
         SELECT mtl_material_transactions_s.NEXTVAL
           INTO v_header_id
           FROM DUAL;
      EXCEPTION
         WHEN OTHERS
         THEN
            v_header_id := 0;
      END;

      BEGIN
         UPDATE mtl_transactions_interface
            SET transaction_header_id = v_header_id,
                transaction_date = SYSDATE
          WHERE source_code = 'Miscellaneous receipt'
            AND transaction_header_id IS NULL;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line
                           ('Error while updating MTL_TRANSACTIONS_INTERFACE');
      END;

      COMMIT;

      BEGIN
         retval :=
            apps.inv_txn_manager_pub.process_transactions
                                         (p_api_version           => 1.0,
                                          p_init_msg_list         => 'T',
                                          p_commit                => 'T',
                                          p_validation_level      => 100,
                                          x_return_status         => l_return_status,
                                          x_msg_count             => l_msg_cnt,
                                          x_msg_data              => l_msg_data,
                                          x_trans_count           => l_trans_count,
                                          p_table                 => 1,
                                          p_header_id             => v_header_id
                                         );
         DBMS_OUTPUT.put_line ('l_msg_data:' || l_msg_data);
         DBMS_OUTPUT.put_line ('l_return_status:' || l_return_status);
         DBMS_OUTPUT.put_line ('v_header_id:' || v_header_id);
         DBMS_OUTPUT.put_line ('retval:' || retval);
         COMMIT;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_error := SQLERRM || SQLCODE;
            DBMS_OUTPUT.put_line ('ERROR occur:' || l_error);
      END;
   END LOOP;
END;

LOT creation using api

DECLARE
   v_object_id           NUMBER;
   v_return_status       VARCHAR2 (1)    := NULL;
   v_msg_count           NUMBER          := 0;
   v_msg_data            VARCHAR2 (2000);
   v_lot_number          VARCHAR2 (50);
   v_expire_date         DATE            := SYSDATE + 180;
   v_organization_id     NUMBER          := 122;
   v_inventory_item_id   NUMBER          := 19160;
BEGIN
   fnd_global.apps_initialize (3842, 51327, 385);
   DBMS_OUTPUT.put_line ('Calling API to Create Lot Number');
   v_lot_number :=
      inv_lot_api_pub.auto_gen_lot
                           (p_org_id                 => v_organization_id,
                            p_inventory_item_id      => v_inventory_item_id,
                            p_parent_lot_number      => NULL,
                            p_subinventory_code      => NULL,
                            p_locator_id             => NULL,
                            p_api_version            => 1.0,
                            p_init_msg_list          => fnd_api.g_true,
                            p_commit                 => fnd_api.g_false,
                            p_validation_level       => fnd_api.g_valid_level_full,
                            x_return_status          => v_return_status,
                            x_msg_count              => v_msg_count,
                            x_msg_data               => v_msg_data
                           );

   IF v_return_status = fnd_api.g_ret_sts_success
   THEN
      COMMIT;
      DBMS_OUTPUT.put_line
                       (   'The Auto generation of Lot Number is Sucessful: '
                        || v_lot_number
                       );
   ELSE
      DBMS_OUTPUT.put_line ('The Auto generation of Lot Number Failed');
      ROLLBACK;

      FOR i IN 1 .. v_msg_count
      LOOP
         v_msg_data := fnd_msg_pub.get (p_msg_index => i, p_encoded => 'F');
         DBMS_OUTPUT.put_line (i || ') ' || v_msg_data);
      END LOOP;
   END IF;

   IF v_lot_number IS NOT NULL
   THEN
      DBMS_OUTPUT.put_line ('Calling API to Create/Insert a Lot Numbers');
      inv_lot_api_pub.insertlot
                           (p_api_version            => 1,
                            p_init_msg_list          => fnd_api.g_false,
                            p_commit                 => fnd_api.g_false,
                            p_validation_level       => fnd_api.g_valid_level_full,
                            p_inventory_item_id      => v_inventory_item_id,
                            p_organization_id        => v_organization_id,
                            p_lot_number             => v_lot_number,
                            p_expiration_date        => v_expire_date,
                            x_object_id              => v_object_id,
                            x_return_status          => v_return_status,
                            x_msg_count              => v_msg_count,
                            x_msg_data               => v_msg_data
                           );

      IF v_return_status = fnd_api.g_ret_sts_success
      THEN
         COMMIT;
         DBMS_OUTPUT.put_line (   'The Creation of Lot Number is Sucessful: '
                               || v_object_id
                              );
      ELSE
         DBMS_OUTPUT.put_line ('The Creation of Lot Number Failed');
         ROLLBACK;

         FOR i IN 1 .. v_msg_count
         LOOP
            v_msg_data :=
                         fnd_msg_pub.get (p_msg_index      => i,
                                          p_encoded        => 'F');
            DBMS_OUTPUT.put_line (i || ') ' || v_msg_data);
         END LOOP;
      END IF;
   END IF;
END;

API to Add and Delete Values in Value Set

DECLARE

   v_enabled_flag        VARCHAR2 (2)                  := 'Y';

   v_summary_flag        VARCHAR2 (2)                  := 'Y';

   v_start_date_active   DATE                          := SYSDATE;

   v_error_msg           VARCHAR2 (1000)               := NULL;

   v_who_type            fnd_flex_loader_apis.who_type;

   v_request_id          NUMBER;

   v_rec_success         NUMBER;

   v_rec_error           NUMBER;

   v_rec_cnt             NUMBER                        := 0;

   v_user_id             NUMBER                        := fnd_global.user_id;

   v_login_id            NUMBER                        := fnd_global.login_id;

   v_req_id              NUMBER                 := fnd_global.conc_request_id;

   v_value_set_name      VARCHAR2 (50)                 := 'TESTVALUESET';

   v_value_set_value     VARCHAR2 (50)                 := 'SAMPLEVALUE';

BEGIN

   v_who_type.created_by := v_user_id;

   v_who_type.creation_date := SYSDATE;

   v_who_type.last_updated_by := v_user_id;

   v_who_type.last_update_date := SYSDATE;

   v_who_type.last_update_login := v_login_id;

   BEGIN

      fnd_flex_values_pkg.load_row

                                 (x_flex_value_set_name             => v_value_set_name,

                                  x_parent_flex_value_low           => NULL,

                                  x_flex_value                      => v_value_set_value,

                                  x_who                             => v_who_type,

                                  x_enabled_flag                    => v_enabled_flag,

                                  x_summary_flag                    => v_summary_flag,

                                  x_start_date_active               => v_start_date_active,

                                  x_end_date_active                 => NULL,

                                  x_parent_flex_value_high          => NULL,

                                  x_structured_hierarchy_level      => NULL,

                                  x_hierarchy_level                 => NULL,

                                  x_compiled_value_attributes       => NULL,

                                  x_value_category                  => NULL,

                                  x_attribute1                      => NULL,

                                  x_attribute2                      => NULL,

                                  x_attribute3                      => NULL,

                                  x_attribute4                      => NULL,

                                  x_attribute5                      => NULL,

                                  x_attribute6                      => NULL,

                                  x_attribute7                      => NULL,

                                  x_attribute8                      => NULL,

                                  x_attribute9                      => NULL,

                                  x_attribute10                     => NULL,

                                  x_attribute11                     => NULL,

                                  x_attribute12                     => NULL,

                                  x_attribute13                     => NULL,

                                  x_attribute14                     => NULL,

                                  x_attribute15                     => NULL,

                                  x_attribute16                     => NULL,

                                  x_attribute17                     => NULL,

                                  x_attribute18                     => NULL,

                                  x_attribute19                     => NULL,

                                  x_attribute20                     => NULL,

                                  x_attribute21                     => NULL,

                                  x_attribute22                     => NULL,

                                  x_attribute23                     => NULL,

                                  x_attribute24                     => NULL,

                                  x_attribute25                     => NULL,

                                  x_attribute26                     => NULL,

                                  x_attribute27                     => NULL,

                                  x_attribute28                     => NULL,

                                  x_attribute29                     => NULL,

                                  x_attribute30                     => NULL,

                                  x_attribute31                     => NULL,

                                  x_attribute32                     => NULL,

                                  x_attribute33                     => NULL,

                                  x_attribute34                     => NULL,

                                  x_attribute35                     => NULL,

                                  x_attribute36                     => NULL,

                                  x_attribute37                     => NULL,

                                  x_attribute38                     => NULL,

                                  x_attribute39                     => NULL,

                                  x_attribute40                     => NULL,

                                  x_attribute41                     => NULL,

                                  x_attribute42                     => NULL,

                                  x_attribute43                     => NULL,

                                  x_attribute44                     => NULL,

                                  x_attribute45                     => NULL,

                                  x_attribute46                     => NULL,

                                  x_attribute47                     => NULL,

                                  x_attribute48                     => NULL,

                                  x_attribute49                     => NULL,

                                  x_attribute50                     => NULL,

                                  x_attribute_sort_order            => NULL,

                                  x_flex_value_meaning              => v_value_set_value,

                                  x_description                     => v_value_set_value

                                 );

      COMMIT;

   EXCEPTION

      WHEN OTHERS

      THEN

         DBMS_OUTPUT.put_line ('Error is ' || SUBSTR (SQLERRM, 1, 1000));

   END;

END;

API to Delete Values in Value Set:

DECLARE

   l_err_msg   VARCHAR2 (500) := NULL;

   CURSOR c1

   IS

      SELECT ffv.flex_value_id, ffv.flex_value

        FROM fnd_flex_value_sets ffvs,

             fnd_flex_values ffv,

             fnd_flex_values_tl ffvt

       WHERE flex_value_set_name = 'XXHL_DATE_FORMAT'          -- Value Set Name

         AND ffv.flex_value_set_id = ffvs.flex_value_set_id

         AND ffvt.flex_value_id = ffv.flex_value_id

         AND ffvs.flex_value_set_id = ffv.flex_value_set_id

         AND ffvt.LANGUAGE = 'US'

         AND ffv.enabled_flag = 'Y'

         AND ffv.summary_flag = 'N'

         AND ffv.flex_value_id = 309564;

BEGIN

   FOR i IN c1

   LOOP

      fnd_flex_values_pkg.delete_row (i.flex_value_id);

      COMMIT;

      DBMS_OUTPUT.put_line (i.flex_value_id || ' Deleted  Successfully !!!!');

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      l_err_msg := SQLERRM;

      DBMS_OUTPUT.put_line ('Exception: ' || l_err_msg);

END;

value set tables in oracle apps r12

  SELECT v.*
  FROM fnd_flex_value_sets s, fnd_flex_validation_tables v
 WHERE s.flex_value_set_name = 'XHL_ORG_CODE'
   AND s.flex_value_set_id = v.flex_value_set_id