Oracle Forum - The Knowledge Center for Oracle Professionals - Looking Beyond the Possibilities
Project Expenditure type/category wise details with material - Printable Version

+- Oracle Forum - The Knowledge Center for Oracle Professionals - Looking Beyond the Possibilities (http://www.oraerp.com)
+-- Forum: Oracle Applications (http://www.oraerp.com/forum-39.html)
+--- Forum: Oracle E-Business Suite (EBS) (http://www.oraerp.com/forum-1.html)
+---- Forum: Manufacturing and Projects (http://www.oraerp.com/forum-7.html)
+---- Thread: Project Expenditure type/category wise details with material (/thread-71436.html)



Project Expenditure type/category wise details with material - shahidulshahin - 06-22-2015

/* Expenditure type/category wise project expenditure details */
 
SELECT
       ppa.segment1 project_code
       , ppa.name project_name
       , pt.task_number task_No
       , pt.task_name
       , pea.expenditure_group batch
       , pet.expenditure_category exp_Category
       , pet.expenditure_type exp_type
       , peia.transaction_source tran_source
       , msib.SEGMENT1 ||'.'|| msib.SEGMENT2 ||'.'|| msib.SEGMENT3 ||':' || msib.Description ITEM_DESC
       , msib.PRIMARY_UOM_CODE 
       , peia.quantity
       , peia.raw_cost
    FROM pa.pa_projects_all ppa
       , pa.pa_expenditure_items_all peia
       , pa.pa_expenditures_all pea
       , pa.pa_tasks pt
       , pa.pa_expenditure_types pet
       , MTL_SYSTEM_ITEMS_B msib
       , mtl_material_transactions mmt
   WHERE ppa.segment1= '3000004'
        AND peia.expenditure_id = pea.expenditure_id(+)
        AND peia.project_id = ppa.project_id
        AND peia.task_id = pt.task_id(+)
        AND peia.expenditure_type = pet.expenditure_type(+)
        AND ppa.project_id = pt.project_id
--     AND msib.INVENTORY_ITEM_ID = '11008'
AND mmt.source_project_id = '6001' --ppa.project_id
AND mmt.source_task_id = peia.task_id
AND mmt.inventory_item_id = msib.INVENTORY_ITEM_ID
ORDER BY peia.transaction_source
       , peia.creation_date DESC
       , pt.task_number DESC;
 
 
1. I need help of the experts about the above query. When executing the query it is giving more rows (redundent) than expected. Whats wrong with my query.
2. How could I determine whether the task is parent task or child task?
3. Any Suggestion or betterment of that query/information.
 
Thanks
-Shahidul.