[shahidulshahin]

/* 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.