I am working on upgrading an OPM report from 11i to R12, which shows transactions of items, including Quantity and Cost information.
My questions are that:
1. I know tables 'ic_tran_pnd', 'ic_tran_cmp' are substituted by 'MTL_MATERIAL_TRANSACTIONS' in R12.
Do the tables, such as 'cm_acst_led', 'cm_cmpt_dtl', 'cm_whse_asc', 'cm_acst_led', 'cm_adjs_dtl', are still in use?
2. How can I get the Cost of OPM item? Could I get it from table 'MTL_MATERIAL_TRANSACTIONS'?
Now I can get Quantity from it, but after creating a Sales Order, Pick and Ship it, the columns 'ACTUAL_COST' of corresponding records in 'MTL_MATERIAL_TRANSACTIONS' are still Null.
Hi Ganxin ,
For 1 : yes these tables still in use
view this link for all opm costing tables
For 2: No you can't view the cost from mtl_material_transactions table , the actual_cost column shows you the cost for this transactions not the average for sure.
use this script to get the cost of opm item
SELECT msi.SEGMENT1,msi.DESCRIPTION , c.period_id, SUM (c.cmpnt_cost) COST
FROM cm_cmpt_dtl c , mtl_system_items msi
WHERE c.organization_id = :P_ORG
AND msi.SEGMENT1 between nvl (:P_item_lo , msi.SEGMENT1 ) and nvl(:P_item_hi , msi.SEGMENT1)
And msi.inventory_item_id = c.inventory_item_id
WHERE calendar_code = :P_Calendar_Code
AND period_code = :P_Period_COde
/* and LEGAL_ENTITY_ID = :P_LEGAL_ENTITY_ID */)
group by msi.SEGMENT1,msi.DESCRIPTION , c.period_id
it just retrieves the average cost , but if u want to get the detailed cost its another issue.
hope that helps you.
But For 2, I still have some questions, what I need to be show in the report is the ACTUAL COST for every transation. According your answer, whether it means I can get it from 'mtl_material_transactions'?
Actually, after doing a PO and Receipt, I can get the ACTUAL COST from columns 'ACTUAL_COST' in 'mtl_material_transactions', but can't get it after doing SO, Pick and Ship.
Could you please tell me why? Anything I missed?
Hi Ganxin ,
I am sorry for this delay.
Please clarify; you are doing SO for the same item you received from PO?
It doesn't make sense, and if then it should take the same cost if no further receipts are done .
But if this item is new, and have no cost at all, so it’s normal that the transaction won’t has any cost .
The cost is adjusted according to receipts transactions not issue.
If u r using actual cost then the cost of all issue transactions will be the same at the end of the period “Average Cost ‘ which will calculated after running “actual cost process’ and ‘ cost update’ .
I hope that is clear .
My email firstname.lastname@example.org
1. I did PO and SO for the same item.
And when creating PO, the price is automatically filled by '0' after selected item, and then I enter a value manually. Finally, actual_cost in 'mtl_material_transations' is the value I entered.
And when creating SO, the price is automatically filled by a value, but finally, actual_cost in 'mtl_material_transations' is NULL.
2. What you mean 'The cost is adjusted according to receipts transactions not issue.' ?
You mean cost of issue transactions will be different with the cost when doing transactions?
3. After running 'actual cost process' and 'cost update', the actual_cost in 'mtl_material_transations' will be re-calculated?
I'll try it, thanks.
I have tried to run 'actual cost process' and 'cost update'. It seems that all transactions in 'mtl_material_transactions' which are in period and actual_cost > 0 are collected for updating 'cm_cmpt_dtl' and 'cm_acst_led', and the cost of item is updated too.
But after doing a SO, Pich and Ship, the actual_cost in 'mtl_material_transactions' is still NULL.
Could you please explain it for me? Any steps I missed?
Thanks a lot.