+- 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.