PA Project Expenditure Adjustment Activity

Description
Categories: BI Publisher, Financials
Application: Projects
Source: AUD: Project Expenditure Adjustment Activity (XML)
Short Name: PAXEXADJ_XML
DB package: PA_PAXEXADJ_XMLP_PKG
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
Parameter Name SQL text Validation
To Adjustment Date
 
Date
From Adjustment Date
 
Date
Employee Name
 
LOV Oracle
Task Number
 
LOV Oracle
Project Number
 
LOV Oracle