SELECT
p.project_id PROJECT_ID1,
p.name project_name,
p.segment1 project_num,
emp.full_name Employee,
substr(o.name,1,60) Organization,
ei.expenditure_item_date Item_Date,
ei.expenditure_type Type,
t.task_number,
ei.non_labor_resource ,
to_char( eia.activity_date, 'DD-MON-RR' ) adjustment_date,
l.meaning adjustment_activity,
emp2.full_name Adjusted_By
FROM
pa_projects p
, pa_tasks t
, pa_expenditure_items_all ei
, pa_expenditures_all e
, pa_expend_item_adj_activities eia
, per_people_f emp
, per_people_f emp2
, fnd_user u
, hr_organization_units o
, pa_lookups l
WHERE
p.project_id = :project_id
and eia.expenditure_item_id = ei.expenditure_item_id
and ei.expenditure_id = e.expenditure_id
and ei.org_id = e.org_id
and e.incurred_by_person_id = emp.person_id (+)
and ( :person_id IS NULL
OR e.incurred_by_person_id = :person_id )
and sysdate between emp.effective_start_date (+)
and nvl(emp.effective_end_date (+) ,sysdate + 1)
and nvl( ei.override_to_organization_id,
e.incurred_by_organization_id ) = o.organization_id
and ei.task_id = t.task_id
and ( :task_id IS NULL OR t.task_id = :task_id )
and t.project_id = p.project_id
and eia.created_by = u.user_id
and u.employee_id = emp2.person_id
and sysdate between emp2.effective_start_date
and nvl(emp2.effective_end_date,sysdate + 1)
and ( emp2.employee_number IS NOT NULL OR emp2.npw_number is not null )
and trunc(eia.activity_date)
BETWEEN nvl( :start_activity_date,trunc(eia.activity_date))
AND nvl(:end_activity_date, trunc(eia.activity_date))
and eia.exception_activity_code = l.lookup_code
and l.lookup_type = decode(nvl(emp2.employee_number, emp2.npw_number), NULL, 'EXCEPTION ACTIVITY', 'EXCEPTION ACTIVITY')
GROUP BY
p.project_id
, p.segment1
, p.name
, emp.full_name
, o.name
, ei.expenditure_item_date
, ei.expenditure_type
, t.task_number
, ei.non_labor_resource
, eia.activity_date
, l.meaning
, eia.created_by
, emp2.full_name
, eia.expenditure_item_id
ORDER BY 1 ASC,3 ASC,2 ASC,4 ASC,5 ASC,6 ASC,7 ASC,8 ASC,9 ASC |
