Tuesday, 18 June 2019

Query to Find System Cost Calculation Of Inventory Items In Oracle Apps R12

SELECT msi.segment1                                       item_code, 
       msi.organization_id                                org, 
       (SELECT name 
        FROM   hr_organization_units hou 
        WHERE  hou.organization_id = msi.organization_id) organization_name, 
       transaction_costed_date, 
       transaction_date, 
       primary_quantity, 
       actual_cost, 
       new_quantity, 
       new_cost, 
       prior_costed_quantity, 
       prior_cost, 
       transaction_type, 
       cost_group_id, 
       actual_material_overhead, 
       actual_resource, 
       actual_outside_processing, 
       actual_overhead, 
       new_material, 
       new_material_overhead, 
       new_resource, 
       new_outside_processing, 
       new_overhead, 
       prior_material, 
       prior_material_overhead, 
       prior_resource, 
       prior_outside_processing, 
       prior_overhead 
FROM   cst_cg_cost_history_v xx, 
       mtl_system_items_b msi, 
       mtl_parameters mt 
WHERE  xx.inventory_item_id = msi.inventory_item_id 
       AND xx.organization_id = msi.organization_id 
       AND xx.organization_id = mt.organization_id 
       AND xx.cost_group_id = mt.default_cost_group_id 
       AND msi.organization_id = <<Inventory_Organization_ID>>
       AND xx.inventory_item_id IN ( <<List_Of_Inventory_Item_IDs>> )
       AND transaction_date >= To_date ('2018/04/01 00:00:00', 
                               'YYYY/MM/DD HH24:MI:SS') 
       AND transaction_date <= To_date ('2019/03/31 23:59:59', 
                               'YYYY/MM/DD HH24:MI:SS'); 

No comments:

Post a Comment