1 Reply Latest reply on May 26, 2016 7:50 AM by 1559936

    Issue With Sub Inv transfer

    SoundariyaKumar

      Hi,

           I am transferring product A from Sub inventory FG and locator L1 to subinv 3FG locator L2

      Through a concurrent program, in the plsql stored procedure i m just populating the transaction interface table and then calling the API for material transaction.

      What happens is product is moved to subinv 3FG and locator L2 and i am expecting the product should not exist in the subinventory the FG and locator L1 but what happens is that the quantity gets doubled here.

       

      Note: I am trying to transfer a negative quantity from source subinventory.

      This is my code:

      -- fetching transaction type id of project transfer transaction

               SELECT transaction_type_id

                 INTO v_trx_type_id

                 FROM mtl_transaction_types

                WHERE transaction_type_name = 'Subinventory Transfer';

       

       

               -- Fetching transaction id sequence value

               SELECT mtl_material_transactions_s.NEXTVAL

                 INTO v_tran_iface_id

                 FROM DUAL;

       

       

               BEGIN

                  -- Fetching Reason id

                  SELECT reason_id

                    INTO v_reason_id

                    FROM mtl_transaction_reasons

                   WHERE reason_name = 'PRIOR BSA';

               EXCEPTION

                  WHEN OTHERS

                  THEN

                     v_reason_id := NULL;

               END;

       

       

               BEGIN

                  SELECT csg.material_account

                    INTO v_dist_id

                    FROM cst_cost_group_accounts csg, mtl_parameters mp

                   WHERE mp.default_cost_group_id = csg.cost_group_id

                     AND mp.organization_id = r1.org_id;

               EXCEPTION

                  WHEN OTHERS

                  THEN

                     v_dist_id := NULL;

               END;

       

       

               -- inserting records into MTL_TRANSACTION_INTERFACE table

               BEGIN

                  INSERT INTO mtl_transactions_interface

                              (transaction_header_id, transaction_interface_id,

                               transaction_type_id, transaction_uom,

                               transaction_date, organization_id,

                               transaction_quantity, last_update_date,

                               last_updated_by, creation_date, created_by,

                               transaction_mode, process_flag,

                               validation_required, source_header_id,

                               source_line_id, source_code, lock_flag,

                               inventory_item_id, subinventory_code,

                               transfer_organization, transfer_subinventory,

                               loc_segment1, loc_segment2, loc_segment19,

                               xfer_loc_segment1, xfer_loc_segment2,

                               xfer_loc_segment19, to_project_id,

                               transaction_reference,

      --                          transaction_cost,

                                                     reason_id,

                               distribution_account_id

                              )

                       VALUES (v_tran_iface_id, v_tran_iface_id,

                               v_trx_type_id, r1.uom,

                               SYSDATE, r1.org_id,

                               r1.pri_qty, SYSDATE,

                               fnd_global.user_id, SYSDATE, fnd_global.user_id,

                               2, 1,

                               1, v_tran_iface_id,

                               v_tran_iface_id, 'SUBINV_TRANSFER', 2,

                               r1.item_id, r1.subinv,

                               r1.org_id, '3FG',

                               r1.loc_seg1, r1.loc_seg2, r1.loc_seg19,

                               SUBSTR ('XX' || r1.loc_seg1, 1, 6), r1.loc_seg2,

                               r1.loc_seg19, r1.project_id,

                               'Non-Stock Product Move-3FG',

      --                          r1.unit_cost,

                                                            v_reason_id,

                               v_dist_id

                              );

       

       

                  fnd_file.put_line

                      (fnd_file.LOG,

                       'Successfully inserted into MTL_TRANSACTIONS_INTERFACE Table'

                      );

               EXCEPTION

                  WHEN OTHERS

                  THEN

                     fnd_file.put_line

                          (fnd_file.LOG,

                              'Error inserting MTL_TRANSACTIONS_INTERFACE table : '

                           || SQLERRM

                           || DBMS_UTILITY.format_error_backtrace

                          );

               END;

       

       

               -- Inserting into mtl_transaction_lots_interface

               IF r1.lot_ctrl = 2

               THEN

                  BEGIN

                     INSERT INTO mtl_transaction_lots_interface

                                 (transaction_interface_id, lot_number,

                                  transaction_quantity, last_update_date,

                                  last_updated_by, creation_date, created_by,

                                  product_transaction_id, primary_quantity

                                 )

                          VALUES (v_tran_iface_id, r1.lot,

                                  r1.pri_qty, SYSDATE,

                                  fnd_global.user_id, SYSDATE, fnd_global.user_id,

                                  v_tran_iface_id, r1.pri_qty

                                 );

       

       

                     fnd_file.put_line

                        (fnd_file.LOG,

                         'Successfully inserted into MTL_TRANSACTION_LOTS_INTERFACE Table'

                        );

                  EXCEPTION

                     WHEN OTHERS

                     THEN

                        fnd_file.put_line

                           (fnd_file.LOG,

                               'Error inserting MTL_TRANSACTION_LOTS_INTERFACE table : '

                            || SQLERRM

                            || DBMS_UTILITY.format_error_backtrace

                           );

                  END;

               END IF;

       

       

               -- Processing the Interface table records using INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS API

               v_op :=

                  inv_txn_manager_pub.process_transactions

                                          (p_api_version           => 1.0,

                                           p_init_msg_list         => l_init_msg_list,

                                           p_commit                => l_commit,

                                           p_validation_level      => l_validation_level,

                                           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_tran_iface_id

                                          );

       

       

      Please let me know what am i missing here?