This discussion is archived
8 Replies Latest reply: Sep 28, 2011 10:17 AM by 849554 RSS

Table Changes of OPM Costing between 11i and R12

849554 Newbie
Currently Being Moderated
Hi Guys,

Please if any one can help me.

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.

Please help me. Thanks in advance.

Best Regards,
Ganxin
  • 1. Re: Table Changes of OPM Costing between 11i and R12
    Ahmed.Abbas Explorer
    Currently Being Moderated
    Hi Ganxin ,
    For 1 : yes these tables still in use
    view this link for all opm costing tables
    http://etrm.oracle.com/pls/et1211d9/etrm_pnav.ls_object?c_name=*&c_owner=GMF&c_type=TABLE&c_status=*

    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
    AND c.period_id
    IN (
    SELECT period_id
    FROM gmf_period_statuses
    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.
  • 2. Re: Table Changes of OPM Costing between 11i and R12
    849554 Newbie
    Currently Being Moderated
    Hi Ahmed,

    Thanks your answer. It helps me a lot.

    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?

    Thank you very much.
  • 3. Re: Table Changes of OPM Costing between 11i and R12
    849554 Newbie
    Currently Being Moderated
    Hi Ahmed,

    May I have your email address?
    Mine is ganxin.hu@oracle.com.
    Hope we can communicate more.
    Thanks a lot.

    Best regards,
    Ganxin
  • 6. Re: Table Changes of OPM Costing between 11i and R12
    Ahmed.Abbas Explorer
    Currently Being Moderated
    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 ahmed.abbas269@gmail.com
    Best regards.
  • 7. Re: Table Changes of OPM Costing between 11i and R12
    849554 Newbie
    Currently Being Moderated
    Hi Ahmed,

    Thanks for your reply.

    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.

    It really helps me a lot.
    Thank you very much.

    Best Regards,
    Ganxin
  • 8. Re: Table Changes of OPM Costing between 11i and R12
    849554 Newbie
    Currently Being Moderated
    Hi Ahmed,

    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.



    Best Regards,
    Ganxin

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points