5 Replies Latest reply: Feb 2, 2013 12:34 AM by Arun Prasath RSS

    What is the relationship?

    Arun Prasath
      Can anyone tell me the relationship between these two tables. MTL_MATERIAL_TRANSACTIONS & MTL_CST_ACTUAL_COST_DETAILS. If i want to relate, what is the foreign key?
        • 1. Re: What is the relationship?
          Hussein Sawwan-Oracle
          Arun Prasath wrote:
          Can anyone tell me the relationship between these two tables. MTL_MATERIAL_TRANSACTIONS & MTL_CST_ACTUAL_COST_DETAILS. If i want to relate, what is the foreign key?
          Did you check eTRM website? -- http://etrm.oracle.com

          Thanks,
          Hussein
          • 2. Re: What is the relationship?
            Arun Prasath
            Wow!! Such a good reference for all tables.. Thanks a lot..
            Unfortunately there is no relationship between the tables i asked for.
            Do u know how to get the material cost of particular date?
            • 3. Re: What is the relationship?
              Yuvaraj C.
              Hi Arun,
              If you are using Average Costing then you can try this...

              SELECT SUM (transaction_quantity * appld_cost) inv_cost
              FROM (SELECT TRUNC (transaction_date) trx_dt,
              subinventory_code,
              mtt.transaction_type_name,
              transaction_quantity,
              new_cost,
              TO_CHAR (TRUNC (transaction_date), 'MMRRRR') cost_period,
              (SELECT MAX (new_cost)
              FROM mtl_material_transactions
              WHERE inventory_item_id = mmt.inventory_item_id
              AND organization_id = mmt.organization_id
              AND TO_CHAR (transaction_date, 'MMYYYY') =
              TO_CHAR (mmt.transaction_date, 'MMYYYY'))
              appld_cost
              FROM mtl_material_transactions mmt, mtl_transaction_types mtt
              WHERE 1 = 1
              AND mtt.transaction_type_id = mmt.transaction_type_id
              AND organization_id = 110
              AND TRUNC (transaction_date) <= '********DATE********'
              AND SUBINVENTORY_CODE NOT LIKE 'Stag%')

              In case if you are using standard costing, then you should be deriving the item cost from cst_item_costs (not from mtl_material_transactions.new_cost)

              Hope it helps!!!!

              Regards,
              Yuvaraj

              Edited by: Yuvaraj.C on Jan 11, 2013 9:46 AM
              • 4. Re: What is the relationship?
                Arun Prasath
                Hi Yuvaraj,
                Actually i want to know a particular item's cost in a particular date, which can be seen in 'Item Cost History' form of Oracle Apps.
                • 5. Re: What is the relationship?
                  Arun Prasath
                  Me itself found the answer. Here i'm giving the query for others to use..

                  SELECT COST_ELEMENT_ID,SUM(MACD.ACTUAL_COST)
                       FROM MTL_CST_ACTUAL_COST_DETAILS MACD
                       WHERE COST_ELEMENT_ID IN (1,2,3,4)
                       AND MACD.ORGANIZATION_ID= 'Organization_id'
                       AND MACD.INVENTORY_ITEM_ID= 'Item ID'
                       AND TRANSACTION_ID= (SELECT MAX(MACD2.TRANSACTION_ID)
                                                FROM APPS.MTL_CST_ACTUAL_COST_DETAILS MACD2
                                                WHERE MACD2.INVENTORY_ITEM_ID= 'Item ID'
                                                AND TRUNC(MACD2.TRANSACTION_COSTED_DATE) <= 'The desired date'
                                                AND MACD2.ORGANIZATION_ID= 'Organization_id')
                  GROUP BY COST_ELEMENT_ID

                  This query will give the various costs like Material cost, Resource cost, Overhead cost, Outside processing cost and Material Overhead cost of a particular item.

                  Thank you.. :)