5 Replies Latest reply: Mar 8, 2013 11:19 AM by 995744 RSS

    Updating Cost for Zero Onhand Items

    rrenji83
      I have a requirement in a new Implementation. The client is having some items which they don't have Onhand at present. But they want to update the average cost for these items. Misc receipt will not allow as the quantity is mandatory. Is there any way this can be accomplished. Some of these items are lot controlled, Sub-inventory & Locator also there for some.

      Tried by inserting into MTL_Transction_Interface with transaction_type_id=80 & transaction_source_type_id=13 with help of a technical. Nothing worked out.

      Is there any script available. I am a Functional consultant with some exposure to Technical.


      Regards,
      Ranjith
        • 1. Re: Updating Cost for Zero Onhand Items
          Sunny kichloo
          Is this thing related to oracle and if not Please close this thread and post it in appropriate forum.
          • 2. Re: Updating Cost for Zero Onhand Items
            rrenji83
            BEGIN
            INSERT INTO mtl_transactions_interface
            ( source_header_id
            , source_line_id
            , transaction_mode
            , source_code
            , process_flag
            ,TRANSACTION_COST
            , transaction_interface_id
            , transaction_date
            , inventory_item_id
            , transaction_uom
            , organization_id
            , subinventory_code
            , locator_id
            , transfer_lPN_ID
            , project_id
            , task_id
            , reason_id
            , transaction_quantity
            , transaction_type_id
            , transaction_source_type_id
            , distribution_account_id
            , NEW_AVERAGE_COST
            , VALUE_CHANGE
            , PERCENTAGE_CHANGE
            , COST_GROUP_ID
            , created_by
            , creation_date
            , last_updated_by
            , last_update_date
            , last_update_login
            )
            VALUES
            ( 10701
            , 0
            , 3
            , 'On HAND Cost Update'
            , 1
            , i.UNIT_COST
            , nvl(v_transaction_interface_id,10003918)
            , to_date('25-FEB-2013' )-- cv_process_rec.transaction_date
            , v_inventory_item_id
            , v_primary_uom_code
            , v_organization_id
            , I.subinventory
            , v_inventory_location_id
            , l_lpn_id
            , v_project_id
            , v_task_id
            , v_reason_id
            , I.quantity
            , 80 -- I.transaction_type_id to change
            , 13 -- I.transaction_source_type_id
            , l_dist_acct_id
            , i.UNIT_COST
            , 0
            , 0 -- percentage change
            , 122505 --cost group
            , -1
            , SYSDATE
            , -1
            , SYSDATE
            , -1 );

            dbms_output.put_line('12');
            EXCEPTION
            WHEN OTHERS THEN
            dbms_output.put_line('Error ' || sqlerrm);
            dbms_output.put_line('RECORDS Insertion into MTL_TRANSACTION_INTERFACE failed');
            END;

            /* START Added New - Ranjith*/
            BEGIN
            INSERT INTO MTL_TXN_COST_DET_INTERFACE (
            TRANSACTION_INTERFACE_ID
            ,ORGANIZATION_ID
            ,TRANSACTION_COST
            ,NEW_AVERAGE_COST
            ,PERCENTAGE_CHANGE
            ,VALUE_CHANGE
            ,LAST_UPDATE_DATE
            ,LAST_UPDATED_BY
            ,CREATION_DATE
            ,CREATED_BY)
            VALUES (
            NVL (v_transaction_interface_id, 10003918),
            v_organization_id,
            i.UNIT_COST,
            i.UNIT_COST,
            0,
            0,
            SYSDATE,
            -1,
            SYSDATE,
            -1);

            DBMS_OUTPUT.put_line('13');
            EXCEPTION
            WHEN OTHERS THEN
            DBMS_OUTPUT.put_line('Error ' || SQLERRM);
            DBMS_OUTPUT.put_line('RECORDS Insertion into MTL_TRX_CST_INTERFACE failed');
            END;


            --------------------------
            Part of code which is used to insert into interface table.
            • 3. Re: Updating Cost for Zero Onhand Items
              rrenji83
              Related to Oracle only..:)
              • 4. Re: Updating Cost for Zero Onhand Items
                991595
                Hi,

                I am not clear with this requirement due to the following reasons:

                1. just having the average cost in the system without any stock will not have any impact on the average cost calculations for future transactions...
                2. Zero stock will not have any impact on the onhand Inventory Value

                If your client still insists that they need the value in the system for the sake of some reporting requirements (only logical requirement I can think of)... then please try the simple workaround as follows:

                1. Perform misc receipt for the item for some qty (preferably 1) with the proper cost provided...
                2. Perform misc issue for the item for the same qty which was brought in...
                3. Use the same account (migration adjustment account provided by your clients' Finance team) in both the issue and receipt transactions...
                3. The cost will be updated, the stock will become zero...

                Hope this helps...

                Thanks and Regards,
                Raj
                • 5. Re: Updating Cost for Zero Onhand Items
                  995744
                  Hi Ranjith,

                  Could you please provide more info regarding the requirement?
                  Why customer wants to have a cost for items that never had onhand quantity, under average cost?

                  Thank you,
                  Sergio.