PA AUD: Expenditure Batch Status

SELECT
        substr(o.name,1,60)   org_name 
,       l.meaning   status
,       sl.meaning   linkage
,       emp.full_name entered_by
,      substr(e.expenditure_group,1,20) expenditure_group  
,       avg( nvl( eg.control_total_amount, 0 )) control_tot  
,       sum( nvl( ei.quantity, 0 )) qty  
 FROM
        pa_lookups l
,       pa_org_reporting_view orv
,       hr_organization_units o
,       per_assignments_f a
,       per_people_f emp
,       fnd_user u
,       pa_system_linkages sl
,       pa_expenditure_groups eg
,       pa_expenditures_all e
,       pa_expenditure_items_all ei
 WHERE
             orv.organization_id = o.organization_id
   AND  o.organization_id = a.organization_id
   AND  a.primary_flag = 'Y'          
   AND  a.assignment_type IN ( 'E'  , 'C')   
   AND  a.person_id = emp.person_id
   AND   sysdate between emp.effective_start_date
					 and     nvl(emp.effective_end_date,sysdate + 1)
   AND ( emp.employee_number IS NOT NULL OR emp.npw_number IS NOT NULL )  
   AND  emp.person_id = u.employee_id
   AND  u.user_id = eg.created_by
   AND  eg.creation_date BETWEEN a.effective_start_date
                             AND a.effective_end_date
   AND  eg.expenditure_group = e.expenditure_group
   AND  e.expenditure_id = ei.expenditure_id (+)
   AND  eg.system_linkage_function = sl.function
   AND  (    :linkage IS NULL
          OR sl.function = :linkage )
   AND  (    :ending_date IS NULL
          OR eg.expenditure_ending_date = :ending_date )
   AND  eg.expenditure_group_status_code = l.lookup_code
   AND  l.lookup_type = 'EXP GROUP REPORTING STATUS'
   AND  (    :status IS NULL
          OR l.lookup_code = :status )
   AND  (    :display_released_group  = 'Y'
          OR l.lookup_code != 'RELEASED' )
GROUP BY
        a.organization_id
,       o.name
,       l.meaning
,       sl.meaning
,       emp.full_name
,       e.expenditure_group
order by org_name,status,linkage,entered_by