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 |