Script for Miscellaneous Issue of all the items in given Subinventory in Oracle APPS R12

 CREATE OR replace PROCEDURE XX_pkmtl_transaction(

p_item_id             IN VARCHAR2,
p_organization_id     IN NUMBER,
p_subinv              IN VARCHAR2,
p_loc                 IN VARCHAR2,
p_lot_no              IN VARCHAR2,
p_uom_code            IN VARCHAR2,
p_quantity            IN NUMBER,
p_new_average_cost    IN NUMBER,
p_transaction_type    IN VARCHAR2,
p_trans_type_ref      IN VARCHAR2,
p_primary_qty         IN NUMBER,
p_revision            IN VARCHAR2,
p_code_combination_id IN NUMBER,
p_fm_serial_number    IN VARCHAR2,
p_to_serial_number    IN VARCHAR2,
x_return_status       OUT VARCHAR2,
x_msg_count           OUT NUMBER,
x_msg_data            OUT VARCHAR2)
IS
  l_transaction_id   NUMBER;
  l_loc_id           NUMBER;
  l_trn_type_id      NUMBER;
  l_segment1         VARCHAR2(150);
  l_segment2         VARCHAR2(150);
  l_segment3         VARCHAR2(150);
  l_segment4         VARCHAR2(150);
  l_segment5         VARCHAR2(150);
  l_expiration_date  DATE;
  l_trans_count      NUMBER;
  lc_result          NUMBER;
  l_subinv_code      mtl_item_locations_kfv.subinventory_code%TYPE;
  r_mtl_trns_rec     mtl_transactions_interface%ROWTYPE;
  r_mtl_lot_rec      mtl_transaction_lots_interface%ROWTYPE;
  r_mtl_ser_rec      mtl_serial_numbers_interface%ROWTYPE;
  g_l_transaction_id NUMBER;
  g_l_lpn_id         NUMBER := 0;
  CURSOR cur_tns_rec IS
    SELECT mtt.*
    FROM   mtl_transaction_types mtt
    WHERE  MTT.transaction_type_name = p_transaction_type;
  r_tns_rec          cur_tns_rec%ROWTYPE;
  PRAGMA autonomous_transaction;
BEGIN
    x_return_status := NULL;

    x_msg_data := NULL;

dbms_output.Put_line('================================================================================');

BEGIN
    SELECT mtl_material_transactions_s.NEXTVAL
    INTO   l_transaction_id
    FROM   dual;
EXCEPTION
    WHEN OTHERS THEN
      l_transaction_id := NULL;
END;

OPEN cur_tns_rec;

FETCH cur_tns_rec INTO r_tns_rec;

CLOSE cur_tns_rec;

BEGIN
    SELECT gcc.segment1,
           gcc.segment2,
           gcc.segment3,
           gcc.segment4,
           gcc.segment5
    INTO   l_segment1, l_segment2, l_segment3, l_segment4, l_segment5
    FROM   gl_code_combinations gcc
    WHERE  GCC.code_combination_id = p_code_combination_id;
EXCEPTION
    WHEN OTHERS THEN
      l_segment1 := NULL;

      l_segment2 := NULL;

      l_segment3 := NULL;

      l_segment4 := NULL;

      l_segment5 := NULL;
END;

BEGIN
    SELECT inventory_location_id,
           subinventory_code
    INTO   l_loc_id, l_subinv_code
    FROM   mtl_item_locations_kfv
    WHERE  concatenated_segments = p_loc
           AND organization_id = p_organization_id;
EXCEPTION
    WHEN OTHERS THEN
      l_loc_id := NULL;
END;

dbms_output.Put_line('p_loc: '
                     ||p_loc);

dbms_output.Put_line('l_loc_id: '
                     ||l_loc_id);

dbms_output.Put_line('Transaction_type: '
                     ||r_tns_rec.transaction_type_name);

dbms_output.Put_line('p_quantity: '
                     ||p_quantity);

BEGIN
    r_mtl_trns_rec := NULL;

    g_l_transaction_id := NULL;

    r_mtl_trns_rec.transaction_uom := p_uom_code;

    r_mtl_trns_rec.transaction_date := SYSDATE;

    r_mtl_trns_rec.source_code := p_transaction_type;

    r_mtl_trns_rec.source_line_id := l_transaction_id; -- 422170

    r_mtl_trns_rec.source_header_id := l_transaction_id; -- 167091

    r_mtl_trns_rec.process_flag := 1;

    r_mtl_trns_rec.transaction_mode := 3;

    r_mtl_trns_rec.lock_flag := 2;

    r_mtl_trns_rec.locator_id := l_loc_id;

    r_mtl_trns_rec.revision := p_revision;

    r_mtl_trns_rec.last_update_date := SYSDATE;

    r_mtl_trns_rec.last_updated_by := fnd_global.user_id;

    r_mtl_trns_rec.creation_date := SYSDATE;

    r_mtl_trns_rec.created_by := fnd_global.user_id;

    r_mtl_trns_rec.inventory_item_id := p_item_id;

    r_mtl_trns_rec.subinventory_code := Nvl(p_subinv, l_subinv_code);

    r_mtl_trns_rec.organization_id := p_organization_id;

    r_mtl_trns_rec.transaction_quantity := p_quantity;

    r_mtl_trns_rec.primary_quantity := p_primary_qty;

    r_mtl_trns_rec.transaction_type_id := r_tns_rec.transaction_type_id;

    r_mtl_trns_rec.transaction_source_type_id :=
    r_tns_rec.transaction_source_type_id;

    r_mtl_trns_rec.transaction_action_id := r_tns_rec.transaction_action_id;

    r_mtl_trns_rec.transaction_cost := p_new_average_cost;

    --r_mtl_trns_rec.new_average_cost            :=  p_new_average_cost;
    r_mtl_trns_rec.dst_segment1 := l_segment1;

    r_mtl_trns_rec.dst_segment2 := l_segment2;

    r_mtl_trns_rec.dst_segment3 := l_segment3;

    r_mtl_trns_rec.dst_segment4 := l_segment4;

    r_mtl_trns_rec.dst_segment5 := l_segment5;

    r_mtl_trns_rec.transaction_interface_id := l_transaction_id;

    r_mtl_trns_rec.transaction_header_id := l_transaction_id;

    r_mtl_trns_rec.transaction_reference := p_trans_type_ref;

    IF g_l_lpn_id > 0 THEN
      r_mtl_trns_rec.transfer_lpn_id := g_l_lpn_id;

      dbms_output.Put_line('g_l_lpn_id: '
                           ||g_l_lpn_id);
    END IF;

    -- Insert MTI for inv open transactions.
    INSERT INTO mtl_transactions_interface
    VALUES r_mtl_trns_rec;

dbms_output.Put_line('Record inserted into mtl_transactions_interface with transaction_header_id: '
                     ||l_transaction_id);

g_l_transaction_id := l_transaction_id;

--dbms_output.put_line('Inserted a record'||l_transaction_id);
x_return_status := fnd_api.g_ret_sts_success;
EXCEPTION
    WHEN OTHERS THEN
fnd_file.Put_line
(fnd_file.output, 'in exception ---- inserting data into  MTL_TRANSACTIONS_INTERFACE : '
                  || SQLERRM);

dbms_output.Put_line('Error'
                     ||SQLERRM);

x_return_status := fnd_api.g_ret_sts_error;

x_msg_count := x_msg_count + 1;

x_msg_data := 'Error While Inserting data to MTL_TRANSACTIONS_INTERFACE :'
              ||SQLERRM;
END;

dbms_output.Put_line('STEP1');

IF x_return_status != fnd_api.g_ret_sts_error THEN
  --Successful return of first insert
  IF p_lot_no IS NOT NULL THEN
    --Derive lot expiration date for the lot number
    BEGIN
        SELECT expiration_date
        INTO   l_expiration_date
        FROM   mtl_lot_numbers
        WHERE  lot_number = p_lot_no
               AND inventory_item_id = p_item_id
               AND organization_id = p_organization_id;
    EXCEPTION
        WHEN OTHERS THEN
          dbms_output.Put_line('Exception in fetching lot expiration_date: '
                               ||SQLERRM);

          l_expiration_date := NULL;

          dbms_output.Put_line('STEP2');
    END; dbms_output.Put_line('STEP3');
  dbms_output.Put_line('Expiration_date: '||l_expiration_date);
  dbms_output.Put_line('STEP3.1'); BEGIN IF
  p_lot_no IS NOT NULL THEN dbms_output.Put_line('STEP4');
  --Insert lot number interface if the item is lot controlled.
  r_mtl_lot_rec := NULL; g_l_transaction_id := l_transaction_id;
  r_mtl_lot_rec.transaction_interface_id := l_transaction_id;
  r_mtl_lot_rec.source_code :=
  p_transaction_type; r_mtl_lot_rec.source_line_id := l_transaction_id;
  r_mtl_lot_rec.last_update_date := SYSDATE; r_mtl_lot_rec.last_updated_by :=
  fnd_global.user_id; r_mtl_lot_rec.creation_date := SYSDATE;
  r_mtl_lot_rec.created_by := fnd_global.user_id;
  r_mtl_lot_rec.last_update_login :=
  fnd_global.login_id; r_mtl_lot_rec.lot_number := p_lot_no;
  r_mtl_lot_rec.lot_expiration_date := l_expiration_date;
  r_mtl_lot_rec.transaction_quantity :=
  p_quantity; r_mtl_lot_rec.primary_quantity := p_quantity;
  r_mtl_lot_rec.serial_transaction_temp_id := l_transaction_id;
  dbms_output.Put_line('STEP5'); INSERT INTO
  mtl_transaction_lots_interface VALUES r_mtl_lot_rec;
  dbms_output.Put_line('STEP6'); END IF; EXCEPTION WHEN OTHERS THEN
  x_return_status :=
  fnd_api.g_ret_sts_error; x_msg_count := x_msg_count+1; x_msg_data :=
  'Error While Inserting data to MTL_TRANSACTION_LOTS_INTERFACE :'||SQLERRM;
  dbms_output.Put_line('STEP7'); END;
  END IF;

  /*MTL_SERIAL_NUMBERS_INTERFACE*/
  BEGIN
      IF p_fm_serial_number IS NOT NULL THEN
        dbms_output.Put_line('STEP 4S');

        --Insert lot number interface if the item is lot controlled.
        r_mtl_ser_rec := NULL;

        g_l_transaction_id := l_transaction_id;

        r_mtl_ser_rec.transaction_interface_id := l_transaction_id;

        r_mtl_ser_rec.fm_serial_number := p_fm_serial_number;

        r_mtl_ser_rec.to_serial_number := p_to_serial_number;

        r_mtl_ser_rec.last_update_date := SYSDATE;

        r_mtl_ser_rec.last_updated_by := fnd_global.user_id;

        r_mtl_ser_rec.creation_date := SYSDATE;

        r_mtl_ser_rec.created_by := fnd_global.user_id;

        r_mtl_ser_rec.last_update_login := fnd_global.login_id;

        dbms_output.Put_line('STEP 5S');

        INSERT INTO mtl_serial_numbers_interface
        VALUES r_mtl_ser_rec;

        dbms_output.Put_line('STEP 6S');
      END IF;
  EXCEPTION
      WHEN OTHERS THEN
        x_return_status := fnd_api.g_ret_sts_error;

        x_msg_count := x_msg_count + 1;

        x_msg_data :=
        'Error While Inserting data to MTL_SERIAL_NUMBERS_INTERFACE :'
        ||SQLERRM;

        dbms_output.Put_line('STEP 7S');
  END;
END IF;

dbms_output.Put_line('STEP8');

COMMIT;

--call Inventory open transaction api to perform transfer of material from source locator to destination locator.
dbms_output.Put_line('STEP9');

x_return_status := NULL;

x_msg_count := 0;

x_msg_data := NULL;

dbms_output.Put_line('Before Calling process_transactions'
                     ||l_transaction_id);

lc_result := inv_txn_manager_pub.Process_transactions (p_api_version => 1.0,
                          p_init_msg_list => fnd_api.g_false,
             p_commit => fnd_api.g_true,
                          p_validation_level => fnd_api.g_valid_level_full,
                          x_return_status => x_return_status,
             x_msg_count => x_msg_count,
                          x_msg_data => x_msg_data,
             x_trans_count => l_trans_count, p_table
                          => 1, p_header_id => l_transaction_id);

dbms_output.Put_line('STEP10');

dbms_output.Put_line('Return Status'
                     ||x_return_status);

IF Nvl(x_return_status, 'N') <> fnd_api.g_ret_sts_success THEN
  x_return_status := fnd_api.g_ret_sts_error;

  dbms_output.Put_line('Return Status: '
                       ||x_return_status);

  dbms_output.Put_line('Message: '
                       ||x_msg_data);

  dbms_output.Put_line('STEP11');
END IF;

COMMIT;
EXCEPTION
  WHEN OTHERS THEN
             dbms_output.Put_line('In Exception'
                                  ||SQLERRM);

             fnd_msg_pub.Count_and_get (p_count => x_msg_count,
             p_data => x_msg_data,
             p_encoded => fnd_api.g_false);

             dbms_output.Put_line('STEP12');

             ROLLBACK;
END xx_pkmtl_transaction; 

/*Script for Miscellaneous issue of all the items in given Subinventory*/
DECLARE
    v_return_status VARCHAR2(1000);
    v_msg_count     NUMBER;
    v_msg_data      VARCHAR2(1000);
    CURSOR c1 IS
      SELECT moqd.organization_id,
             moqd.inventory_item_id,
             msib.segment1                          item_code,
             moqd.subinventory_code                 ISSUE_SUBINV,
             moqd.revision,
             msib.primary_uom_code,--moqd.TRANSACTION_UOM_CODE,
             moqd.locator_id,
             moqd.lot_number,
             NULL                                   serial_number,
             SUM(moqd.primary_transaction_quantity) onhand_quantity,
             --SUM(moqd.transaction_quantity) onhand_quantity,
             milkfv.concatenated_segments           locators
      FROM   apps.mtl_system_items_b msib,
             apps.mtl_onhand_quantities_detail moqd,
             apps.mtl_item_locations_kfv milkfv
      WHERE  msib.organization_id = 105
             AND msib.inventory_item_id = moqd.inventory_item_id
             AND msib.organization_id = moqd.organization_id
             AND moqd.subinventory_code = 'S19'
             AND milkfv.inventory_location_id(+) = moqd.locator_id
             AND msib.inventory_item_status_code = 'Active'
             AND msib.mtl_transactions_enabled_flag = 'Y'
      --AND msib.segment1 = :p_item
      GROUP  BY moqd.organization_id,
                moqd.inventory_item_id,
                msib.segment1,
                moqd.subinventory_code,
                moqd.revision,
                msib.primary_uom_code,--moqd.TRANSACTION_UOM_CODE,
                moqd.locator_id,
                moqd.lot_number,
                milkfv.concatenated_segments
      UNION
      SELECT moqd.organization_id,
             moqd.inventory_item_id,
             msib.segment1                item_code,
             moqd.subinventory_code,
             moqd.revision,
             msib.primary_uom_code,--moqd.TRANSACTION_UOM_CODE,
             moqd.locator_id,
             moqd.lot_number,
             serial_number,
             1                            onhand_quantity,
             --SUM(moqd.transaction_quantity) onhand_quantity,
             milkfv.concatenated_segments locators
      FROM   mtl_system_items_b msib,
             mtl_onhand_quantities_detail moqd,
             mtl_item_locations_kfv milkfv,
             mtl_serial_numbers msn
      WHERE  1 = 1
             AND msib.organization_id = 105
             AND msib.inventory_item_id = moqd.inventory_item_id
             AND msib.organization_id = moqd.organization_id
             AND moqd.subinventory_code = 'S19'
             AND milkfv.inventory_location_id(+) = moqd.locator_id
             AND msib.inventory_item_status_code = 'Active'
             AND msib.mtl_transactions_enabled_flag = 'Y'
             AND msn.current_organization_id = moqd.organization_id
             AND moqd.subinventory_code = msn.current_subinventory_code
             AND msn.current_status = 3 --Resides in stores
             AND moqd.inventory_item_id = msn.inventory_item_id
             AND moqd.lot_number = msn.lot_number
      GROUP  BY moqd.organization_id,
                moqd.inventory_item_id,
                msib.segment1,
                moqd.subinventory_code,
                moqd.revision,
                msib.primary_uom_code,--moqd.TRANSACTION_UOM_CODE,
                moqd.locator_id,
                moqd.lot_number,
                serial_number,
                milkfv.concatenated_segments;
BEGIN
    dbms_output.Put_line('Hi....!!!! ');

    fnd_global.Apps_initialize(1250, 51441, 401);

    FOR c IN c1 LOOP
        Xx_pkmtl_transaction(p_item_id => c.inventory_item_id,
        p_organization_id => c.organization_id, p_subinv => c.issue_subinv,
        p_loc => c.locators,
        p_lot_no => c.lot_number, p_uom_code => c.primary_uom_code,
        p_quantity => Abs(c.onhand_quantity) *- 1,
        p_transaction_type => 'Miscellaneous issue',
        p_trans_type_ref => 'REMARK.....', p_primary_qty => NULL,
        p_revision => c.revision, p_new_average_cost => NULL, --C.cost,
        p_code_combination_id => NULL, --C.DISTRIBUTION_ACCOUNT_ID,
        p_fm_serial_number => c.serial_number, --C.FM_SERIAL_NUMBER,
        p_to_serial_number => c.serial_number, --C.TO_SERIAL_NUMBER,
        x_return_status => v_return_status, x_msg_count => v_msg_count,
        x_msg_data => v_msg_data);

        dbms_output.Put_line('Item : '
                             ||c.item_code
                             ||' X_RETURN_STATUS->'
                             ||v_return_status
                             ||'    X_MSG_COUNT->'
                             ||v_msg_count
                             ||'    X_MSG_DATA->'
                             ||v_msg_data);

        COMMIT;
    END LOOP;

    dbms_output.Put_line('Done...!!!');
EXCEPTION
    WHEN OTHERS THEN
      dbms_output.Put_line('Error  '
                           ||SQLERRM);
END;