---Perform Layer Cost Update operations via the MTL_TRANSACTIONS_INTERFACE table.
-- Sample code below:
declare
l_trx_id Number;
begin
fnd_global.apps_initialize(FND_PROFILE.VALUE ('USER_ID')
, FND_PROFILE.VALUE ('RESP_ID')
, FND_PROFILE.VALUE ('RESP_APPL_ID')
);
select mtl_material_transactions_s.nextval into l_trx_id from dual;
INSERT INTO MTL_TXN_COST_DET_INTERFACE
(TRANSACTION_INTERFACE_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,ORGANIZATION_ID
,COST_ELEMENT_ID
,LEVEL_TYPE
,NEW_AVERAGE_COST
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,program_id
,program_update_date
,transaction_cost
,percentage_change
, value_change
)VALUES
(l_trx_id
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.user_id
,1522
,5
,1
,215.43
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL);
-- Insert into Transactions Interface
INSERT INTO MTL_TRANSACTIONS_INTERFACE
( TRANSACTION_INTERFACE_ID
,SOURCE_CODE
,PROCESS_FLAG
,TRANSACTION_MODE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,INVENTORY_ITEM_ID
,ORGANIZATION_ID
,TRANSACTION_QUANTITY
,PRIMARY_QUANTITY
,TRANSACTION_UOM
,TRANSACTION_DATE
,ACCT_PERIOD_ID
,TRANSACTION_SOURCE_ID
,TRANSACTION_SOURCE_TYPE_ID
,TRANSACTION_ACTION_ID
,TRANSACTION_TYPE_ID
,TRANSACTION_REFERENCE
,TRANSACTION_COST
,DISTRIBUTION_ACCOUNT_ID
,MATERIAL_ACCOUNT
,MATERIAL_OVERHEAD_ACCOUNT
,RESOURCE_ACCOUNT
,OUTSIDE_PROCESSING_ACCOUNT
,OVERHEAD_ACCOUNT
,COST_GROUP_ID
,SOURCE_LINE_ID
,SOURCE_HEADER_ID
)
VALUES
(l_trx_id
,'Layer Cost Update'
,1
,3 -- transaction_mode
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.user_id
,2356
,1522
,26
,26
,'Ea'
,SYSDATE
,15022
,1059
,15
,24
,28
,'Yog'
,29
,15342
,15342
,15342
,15342
,15342
,15342
,12435
,1
,1
);
end;
WARNING :
-- Layer cost update transactions can only be performed for LIFO or FIFO costing organizations.
-- Layer cost update transactions can only be performed for LIFO or FIFO costing organizations.
FIFO:
Layer Item Cost
The first layer cost is cost of the first layer with a positive quantity.
The layer item cost is the unit cost shared by the layer quantity.
It is the average unit cost of a layer.
Layer Item Cost = layer's acquisition cost / layer's quantity
FIFO item cost is the weighted–average of all inventory layer costs divided by the sum of layer quantities.
Layer 1 = A
For example:
Layer 1 LQ1 = 20 ea. LC1 = $2/each
Layer 2 LQ2 = 10 ea. LC2 = $1.40/each
FIFO Item cost = ($2*20 + $1.40*10) / (20+10) = $1.80