0 Replies Latest reply on Dec 31, 2012 11:03 AM by 790456

    projects query

    790456
      Hi

      Can someone help me in reducing the cost the of the below query.

      SELECT gcc.segment3 cost_centre
      , pe.expenditure_ending_date-6 date_from
      , pe.expenditure_ending_date date_to
      , ppx.employee_number employee_number
      , ppx.full_name employee_name
      , asgf.ass_attribute8 employee_type
      , ppx.person_id person_id
      , ppa.segment1 project_number
      , ppa.project_id project_id
      , ppa.description project_description
      , pt.task_number task_number
      , pt.task_id task_id
      , pt.description task_description
      , ppa.project_type project_type
      , man.full_name project_manager
      , null approval_status
      , SUM (pei.quantity) hours_entered
      , null site_code
      , null site
      , asgf.ass_attribute11 costing_job
      , pei.expenditure_type charge_type
      , sup.full_name supervisor
      , null service_number
      , null timecard_day
      , null daily_comment
      FROM pa_expenditure_items_all pei
      , pa_expenditures_all pe
      , pa_cost_distribution_lines_all pcdl
      , pa_projects_all ppa
      , pa_project_classes ppc
      , pa_tasks pt
      , per_people_x ppx
      , per_people_x sup
      , per_people_x man
      , per_assignments_f asgf
      , gl_code_combinations gcc
      , pa_project_players ppp
      WHERE pe.expenditure_id = pei.expenditure_id
      AND pei.project_id = ppa.project_id
      AND ppc.project_id = ppa.project_id
      AND pei.task_id = pt.task_id
      AND pei.project_id = pt.project_id
      AND ppx.person_id = pe.incurred_by_person_id
      AND sup.person_id(+) = asgf.supervisor_id
      AND asgf.person_id = ppx.person_id
      AND pei.expenditure_item_id = pcdl.expenditure_item_id
      AND gcc.code_combination_id = pcdl.cr_code_combination_id
      AND ppp.project_id = ppa.project_id
      AND ppp.person_id = man.person_id
      AND pei.expenditure_item_date between asgf.effective_start_date and nvl(asgf.effective_end_date,sysdate+1)
      AND SYSDATE BETWEEN ppp.start_date_active AND NVL (ppp.end_date_active, SYSDATE + 1)
      AND pe.incurred_by_person_id = NVL (:p_employee_id, pe.incurred_by_person_id)
      AND pei.expenditure_item_date BETWEEN NVL (:cp_date_from, pei.expenditure_item_date)
      AND NVL (:cp_date_to, pei.expenditure_item_date)
      AND gcc.segment3 BETWEEN NVL (:p_emp_cost_centre_from, gcc.segment3)
      AND NVL(:p_emp_cost_centre_to, gcc.segment3)
      AND gcc.segment2 = NVL(:p_emp_business_stream, gcc.segment2)
      AND ( ( ( :p_project_id IS NULL ) AND ( ppa.project_id IN (SELECT ppa.project_id --, ppc.class_code
      FROM pa_project_classes ppc, pa_projects_all ppa, per_people_x man, pa_project_players ppp
      WHERE ppc.project_id = ppa.project_id
      AND ppp.person_id = man.person_id
      AND man.person_id = NVL(:p_project_manager, man.person_id)
      AND ppc.class_code = NVL(:p_pa_business_area, ppc.class_code)
      AND ppp.project_role_type = 'PROJECT MANAGER'
      AND ppc.class_category = 'Business Area' ) )
      )
      OR ( (:p_project_id IS NOT NULL ) AND ( ppa.project_id = :p_project_id ) ))
      AND pei.quantity > 0
      AND ppc.class_category = 'Business Area'
      AND asgf.primary_flag = 'Y'
      AND asgf.assignment_type IN ('C', 'E')
      AND pcdl.line_type = 'R'
      AND ppp.project_role_type = 'PROJECT MANAGER'
      AND pei.cost_distributed_flag = 'Y'
      AND pei.transaction_source = 'LABOUR COST ADJUSTMENT'
      GROUP BY gcc.segment3
      , pe.expenditure_ending_date-6
      , pe.expenditure_ending_date
      , ppx.employee_number
      , ppx.full_name
      , asgf.ass_attribute8
      , ppx.person_id
      , ppa.segment1
      , ppa.description
      , pt.task_number
      , pt.task_id
      , pt.description
      , ppa.project_type
      , man.full_name
      , asgf.ass_attribute11
      , pei.expenditure_type
      , sup.full_name
      , ppa.project_id

      ~
      Thanks
      Sekhar