7 Replies Latest reply: Jan 7, 2013 10:15 PM by PranitSaha RSS

    Unable to transfer stock in order to close off subinventory

    mlang
      Hi. Hope someone can help.

      I want to close off a very old subinventory that is no longer used. It's holding stock at the moment, but the items aren't available to transact or reserve and I don't know why. They're not reserved and there's no outstanding supply/demand. It looks like the stock was put there during a warehouse migration 5 years ago and no one's been able to clear them out in subsequent moves. What I want to is either issue the stock out or transfer it to a valid subinventory. Alternatively, just purging the lines would be better than the current state. Ideally, I want to do this via forms.

      The items themselves look OK, and we've been transacting them in our current subinventories, so I'm presuming it's something to do with old codes being closed off elsewhere. I just don't know where to look.

      One of the items originated from a PO Receipt, so I thought I'd try a return to lower the figure at its source, but I get the "The transaction quantity 1 in primary unit of measure exceeds the available quantity 0 in the subinventory", so that's hitting the same wall.

      So how can I get rid of this stock?

      Ta
      Matt
        • 1. Re: Unable to transfer stock in order to close off subinventory
          851026
          Hi Matt,

          If you want to remove them from the system, You can achieve this by using counting. You can try to do a cycle count or Physical inventory to remove these items from the system.

          -----
          Pubbs
          • 2. Re: Unable to transfer stock in order to close off subinventory
            mlang
            Thanks for the suggestion, but I keep getting error messages such as "you are trying to perform physical inventory adjustment for material that has reservations/allocations against it". But I can't find any reservations or allocations against them.

            For example, for one item, I see 196 items receipted, then 195 items transferred to a different subinventory. Either they missed 1 in error, or it was already reservered/allocated somewhere. But that's all I can see. mtl_reservations doesn't have anything for it. OM doesn't show anything for it. Supply/Demand doesn't show anything.

            I can only presume that I can't transact or reserve the item because the subinventory or location it's in doesn't allow me to. Still stuck.

            Thanks though :)
            • 3. Re: Unable to transfer stock in order to close off subinventory
              mlang
              Just showing some of my workings so far...

              SELECT
              moqd.creation_date created_on
              , fu.user_name created_by
              , moqd.inventory_item_id item_id
              , msi.segment1 || '-' || msi.segment2 || '-' || msi.segment3 || '-' || msi.segment4 item
              , moqd.subinventory_code sub
              , mil.description
              , moqd.transaction_quantity moqd_qnt
              , moqd.create_Transaction_id crt_id
              , mttc.transaction_type_name crt_type
              , mmtc.transaction_quantity crt_qnt
              , moqd.update_Transaction_id upd_id
              , mttu.transaction_type_name upd_type
              , mmtu.transaction_quantity upd_qnt
              , mmtu.transaction_date upd_date
              , mmtu.transfer_transaction_id trns_id
              , mmtut.transaction_quantity trns_qnt
              , mmtut.subinventory_code trns_sub
              , mr.reservation_quantity rsrvd

              FROM
              inv.mtl_onhand_quantities_detail moqd

              LEFT JOIN inv.MTL_SYSTEM_ITEMS_B msi ON moqd.inventory_item_id = msi.inventory_item_id AND moqd.organization_id = msi.organization_id
              LEFT JOIN applsys.fnd_user fu ON moqd.created_by = fu.user_id
              LEFT JOIN inv.mtl_item_locations mil ON moqd.locator_id = mil.inventory_location_id
              LEFT JOIN (inv.mtl_material_transactions mmtc
              LEFT JOIN inv.mtl_transaction_types mttc ON mmtc.transaction_type_id = mttc.transaction_type_id)
              ON moqd.create_transaction_id = mmtc.transaction_id
              LEFT JOIN (inv.mtl_material_transactions mmtu
              LEFT JOIN inv.mtl_transaction_types mttu ON mmtu.transaction_type_id = mttu.transaction_type_id
              LEFT JOIN inv.mtl_material_transactions mmtut ON mmtu.transfer_transaction_id = mmtut.transaction_id)
              ON moqd.update_transaction_id = mmtu.transaction_id
              LEFT JOIN inv.mtl_reservations mr ON moqd.inventory_item_id = mr.inventory_item_id


              WHERE 1=1
              AND moqd.subinventory_code = 'Stock'

              Gives me...

              CREATED_ON     CREATED_BY     ITEM_ID     ITEM     SUB     DESCRIPTION     MOQD_QNT     CRT_ID     CRT_TYPE     CRT_QNT     UPD_ID     UPD_TYPE     UPD_QNT     UPD_DATE     TRNS_ID     TRNS_QNT     TRNS_SUB     RSRVD
              01-MAY-02     DATACONV     2746     MS2/3-0069-4-SPC     Stock     4R14D     11     7032               11339687     Subinventory Transfer     -37     08-MAR-10     11339688     37     Telford     
              09-MAY-07     XSERCSS     830     INDICATOR-0004-3-000     Stock     4R07B     1     11159417     PO Receipt     196     11337542     Subinventory Transfer     -195     02-MAR-10     11337543     195     Telford
              • 4. Re: Unable to transfer stock in order to close off subinventory
                Stick
                anything in the Transaction Open Interface in the forms?
                • 5. Re: Unable to transfer stock in order to close off subinventory
                  PranitSaha
                  Just check whether the below SQL returns any row.

                  SELECT *
                  FROM
                  mtl_material_transactions_temp
                  WHERE inventory_item_id = <INVENTORY_ITEM_ID>.

                  Thanks,

                  PS.
                  • 6. Re: Unable to transfer stock in order to close off subinventory
                    mlang
                    This is good - I've at least found the transactions holding things up. They are in mmtt, but they're not in the interface.
                    I can see them in Pending Transactions, but that form is protected.

                    The data I'm seeing is...
                    Transaction Date     13/06/2002 11:22     05/08/2002 15:02
                    Transaction Temp ID     461078     1051018
                    Org Code     YAT     YAT
                    Subinventory     Stock     Stock
                    Transfer Subinv     Suspect     Despatch
                    Locator     4R07B     4R14D
                    Item     INDICATOR-0004-3-000     MS2/3-0069-4-SPC
                    Revision     0     0
                    Transaction Qty     1     11
                    Transaction UOM     EA     EA
                    Transaction Status     Allocated     Allocated
                    Transaction Source Type     Move order     Move order
                    Transaction Type     Move Order Transfer     Move Order Transfer
                    Transaction Action     Subinventory transfer     Subinventory transfer
                    Transaction Mode     On-line processing     On-line processing
                    Process Flag     1     1
                    TRANSACTION_SOURCE_ID     39     233
                    Transaction Source Line ID     434     1294
                    CREATION_DATE     13-JUN-02     05-AUG-02
                    LAST_UPDATE_DATE     13-JUN-02     05-AUG-02
                    LOCK_FLAG     N     N
                    PROCESS_FLAG     Y     Y

                    I've read it's safe to do a table update to clear historic transactions on mmtt. Is there a better way - ie forms?

                    Ta
                    Matt
                    • 7. Re: Unable to transfer stock in order to close off subinventory
                      PranitSaha
                      Hi Matt,

                      You need to delete the record from the temp table through the backend only.

                      Make sure to to delete the child records created in the LOT and SERIAL temp tables if the item is lot/serial or both controlled.

                      Thanks,

                      PS.