3 Replies Latest reply: Oct 16, 2013 10:21 AM by rukbat RSS

    Update cost layer with price variance.

    FrancescoDM
      Dear All,

      here is my understanding on FIFO cost layer update for price variance. Currently we are researching a solution in order to automate the update the cost layers to reflect also additional cost that at the moment of receipt were not valued in material account, but that arrived after when the Invoice was received.
      In the country we are working in, it is a legal requirement and the volume of Invoices is pretty high, so the manual procedure is difficult to manage.

      I collected some documentation.

      The crude news is that apparently even if there is a specific transaction for Layer Cost Update in the open interface the transaction is not supported.

      1) on the document “FIFO/LIFO Perpetual Costing Overview” is written “Layer cost update form updates the cost of the inventory layer by inserting a layer

      cost update transaction in MMT (txn_action_id = 24, txn_src_type_id =15) and cost

      details into MSTCD”.


      The full document is at this link. (https://metalink.oracle.com/metalink/plsql/f?p=130:14:9568511493470538626::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,160424.1,1,0,1,helvetica)

      2) The cost manager user Guide says on page 6-28 “You can directly update each layer cost of an item to include additional
      costs such as: freight, invoice price variances, or job variances. [..]
      Layer cost update transactions are inserted into the Transaction Open
      Interface in Oracle Inventory
      . Transaction details are viewed in the
      Create Transaction tabbed region.”


      So we were lead to think we can do something on the open interface, but we recently discovered that the Note:550650.1 FIFO Layer Cost Update Not Processing from MTL_TRANSACTIONS_INTERFACE Table States that: Layer cost updates through Interface is not recommended and not supported currently.
      (The last update date is 01 Feb 2008.)


      So basically this leaves only the manual solution, which is really time consuming.

      So my question is.

      Is it possible to have another automatic/semiautomatic solution like APIs, WebAdi or some custom hook?

      As anyone found a solution?

      I cannot beleive that Oracle eBS does not have any solution for this problem (apart from manual update!)
        • 1. Re: Update cost layer with price variance.
          VikramAwotar
          hello!
          I'm working on a similar project as u, where i need to automate the process u described..
          so we cannot use the OIT: MTL_TRANSACTIONS_INTERFACE for the cost update. while investigating on forums, I've saw the following:
          To update your average cost from transactions you can use the "Mass Edit" program that is available withint the cost management module. It will let you edit cost based on average PO price, receipt price or average inventory cost.
          Link: Average Cost Update

          I'm new to this form but if ever this help u, let me know.
          Ill be investigating also.
          • 2. Re: Update cost layer with price variance.
            Tusker

            Hi

             

            I had a similar requirement, where in, we opted to go for FIFO.

             

            When an Item is received, the Costing engine, picks up the cost from the PO and maintains that as the cost for the corresponding layer, through which the item was received.

             

            But for our business, we had to add 'Import Duty', 'Freight Cost' & 'Finishing  Cost' to the base Material Cost. After a lot of research, I have arrived at an working solution.

            It goes with creating a new records in mtl_material_transaction & mtl_cst_txn_cost_details

             

            declare
            l_new_transaction_id    NUMBER;
            l_old_transaction_id    NUMBER;
            l_cost_account          NUMBER   := 10152;
            l_old_cost              NUMBER;
            l_po_cost_usd           NUMBER   := 0;
            l_po_cost_usd_uom       NUMBER   := 0;
            l_duty                  NUMBER   := 0.01;
            l_freight_cost          NUMBER   := 0.16;
            l_finishing_cost        NUMBER   := 0.01;
            BEGIN
               FOR i IN (  SELECT   MMT.transaction_id
                                    ,POL.unit_meas_lookup_code
                                    ,POL.unit_price
                                    ,POH.currency_code
                                    ,POH.rate
                                    ,RCT.quantity
                                    ,RCT.unit_of_measure
                                    ,MMT.rcv_transaction_id
                                    ,CIL.inv_layer_id
                           FROM     mtl_material_transactions  MMT
                                   ,rcv_transactions           RCT
                                   ,po_lines_all               POL
                                   ,po_headers_all             POH
                                   ,cst_inv_layers             CIL
                           WHERE    RCT.po_line_id             = POL.po_line_id
                           AND      POH.po_header_id           = POL.po_header_id
                           AND      POL.po_header_id           = 925512
                           AND      RCT.transaction_type       = 'DELIVER'
                           AND      RCT.transaction_id         = MMT.rcv_transaction_id
                           AND      RCT.organization_id        = MMT.organization_id
                           AND      CIL.create_transaction_id  = MMT.transaction_id
                           AND      MMT.inventory_item_id      = POL.item_id
                           AND      RCT.transaction_date       > SYSDATE -1
                           AND   NOT EXISTS  (  SELECT   'x'
                                                FROM     mtl_material_transactions  MTX
                                                WHERE    MTX.inventory_item_id      = MMT.inventory_item_id
                                                AND      MTX.organization_id        = MMT.organization_id
                                                AND      MTX.transaction_source_id  = CIL.inv_layer_id
                                                AND      MTX.transaction_type_id    = 28
                                                AND      MTX.transaction_quantity   = 0)) LOOP
                  l_old_transaction_id    := i.transaction_id;
                  l_new_transaction_id    := mtl_material_transactions_s.nextval;
                  IF i.currency_code <> 'USD'   THEN
                     l_po_cost_usd  := i.unit_price * NVL(i.rate,1);
                  ELSE
                     l_po_cost_usd  := i.unit_price;
                  END IF;
                  l_po_cost_usd_uom := ROUND((l_po_cost_usd * INV_CONVERT.inv_um_convert(NULL,NULL,NULL,NULL,NULL,NULL,NULL,i.unit_of_measure,i.unit_meas_lookup_code)),2);
                  INSERT   INTO  mtl_material_transactions
                           (transaction_id
                           ,last_update_date
                           ,last_updated_by
                           ,creation_date
                           ,created_by
                           ,last_update_login
                           ,inventory_item_id
                           ,organization_id
                           ,transaction_type_id
                           ,transaction_action_id
                           ,transaction_source_type_id
                           ,transaction_source_id
                           ,transaction_quantity
                           ,transaction_uom
                           ,primary_quantity
                           ,transaction_date
                           ,acct_period_id
                           ,costed_flag
                           ,percentage_change
                           ,material_account
                           ,material_overhead_account
                           ,resource_account
                           ,outside_processing_account
                           ,overhead_account
                           ,cost_group_id)
                  SELECT   l_new_transaction_id
                           ,SYSDATE
                           ,last_updated_by
                           ,SYSDATE
                           ,created_by
                           ,last_update_login
                           ,inventory_item_id
                           ,organization_id
                           ,28
                           ,24
                           ,15
                           ,i.inv_layer_id
                           ,0
                           ,transaction_uom
                           ,0
                           ,transaction_date
                           ,acct_period_id
                           ,'N'
                           ,0
                           ,l_cost_account
                           ,l_cost_account
                           ,l_cost_account
                           ,l_cost_account
                           ,l_cost_account
                           ,cost_group_id
                  FROM     mtl_material_transactions
                  WHERE    transaction_id    = l_old_transaction_id;
            
                  INSERT   INTO mtl_cst_txn_cost_details
                           (transaction_id
                           ,organization_id
                           ,cost_element_id
                           ,level_type
                           ,last_update_date
                           ,last_updated_by
                           ,creation_date
                           ,created_by
                           ,inventory_item_id
                           ,transaction_cost
                           ,new_average_cost)
                  SELECT   l_new_transaction_id
                           ,organization_id
                           ,cost_element_id
                           ,level_type
                           ,SYSDATE
                           ,last_updated_by
                           ,SYSDATE
                           ,created_by
                           ,inventory_item_id
                           ,transaction_cost
                           ,l_po_cost_usd_uom
                  FROM     mtl_cst_txn_cost_details
                  WHERE    transaction_id    = l_old_transaction_id
                  UNION
                  SELECT   l_new_transaction_id
                           ,organization_id
                           ,2
                           ,level_type
                           ,SYSDATE
                           ,last_updated_by
                           ,SYSDATE
                           ,created_by
                           ,inventory_item_id
                           ,NULL
                           ,l_duty
                  FROM     mtl_cst_txn_cost_details
                  WHERE    transaction_id    = l_old_transaction_id
                  AND      l_duty            > 0
                  UNION
                  SELECT   l_new_transaction_id
                           ,organization_id
                           ,3
                           ,level_type
                           ,SYSDATE
                           ,last_updated_by
                           ,SYSDATE
                           ,created_by
                           ,inventory_item_id
                           ,NULL
                           ,l_freight_cost
                  FROM     mtl_cst_txn_cost_details
                  WHERE    transaction_id    = l_old_transaction_id
                  AND      l_freight_cost    > 0
                  UNION
                  SELECT   l_new_transaction_id
                           ,organization_id
                           ,4
                           ,level_type
                           ,SYSDATE
                           ,last_updated_by
                           ,SYSDATE
                           ,created_by
                           ,inventory_item_id
                           ,NULL
                           ,l_finishing_cost
                  FROM     mtl_cst_txn_cost_details
                  WHERE    transaction_id    = l_old_transaction_id
                  AND      l_finishing_cost  > 0;
               END LOOP;
            END;
            /
            

             

            Hope this helps

            • 3. Re: Update cost layer with price variance.
              rukbat

              Tusker wrote:


              Hope this helps


              Moderator Action:

              Look at the datestamps.  You are five years (almost six) too late.

              This thread is locked to prevent it being resurrected again.