Thursday, 19 November 2015

Query to Get Employee Leave Details in Project Expense Moduke

SELECT PAPF.EMPLOYEE_NUMBER,
       PAPF.FULL_NAME "EMPLOYEE NAME" ,
        T.DESCRIPTION "LEAVE DESC",
       TO_DATE(trunc(ei.EXPENDITURE_ITEM_DATE),'DD-MON-YYYY') "DATE",
       EI.QUANTITY "LEAVE HOUR",
       PEC.EXPENDITURE_COMMENT
   FROM pa_projects_all p,
          pa_tasks t,
          pa_expenditure_items_all ei,
          pa_expenditures_all x,
          pa_project_types_all pt,
          pa_transaction_sources tr,
          hr_all_organization_units_tl o1,
          per_jobs j,
          per_all_people_f papf,
          pa_expenditure_comments pec
    WHERE t.project_id = p.project_id
      AND ei.project_id = p.project_id
      AND p.project_type = pt.project_type
      AND p.org_id = pt.org_id
      AND ei.task_id = t.task_id
      AND ei.expenditure_id = x.expenditure_id
      AND NVL (ei.override_to_organization_id, x.incurred_by_organization_id) =
                                                            o1.organization_id
      AND ei.job_id = j.job_id(+)
      AND ei.transaction_source = tr.transaction_source(+)
      AND P.PROJECT_ID =20006
     -- and ei.EXPENDITURE_TYPE='Leave'
     -- AND X.PERSON_TYPE='EMP'
      AND papf.person_id =  x.incurred_by_person_id
      AND  ei.expenditure_item_id =pec.expenditure_item_id(+)
      AND papf.FULL_NAME='SHIRKE, KUNAL S'
      order by ei.EXPENDITURE_ITEM_DATE asc;

No comments:

Post a Comment