PA AUD: Expenditure Batch Status (1)

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Expenditure Batch Status
Application: Projects
Source: AUD: Expenditure Batch Status (XML)
Short Name: PAXPCEGS_XML
DB package: PA_PAXPCEGS_XMLP_PKG
Run PA AUD: Expenditure Batch Status (1) and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Parameter Name SQL text Validation
Operating Unit
 
LOV
Organization Name
 
LOV Oracle
Expenditure Ending Date
 
Date
Expenditure Type Class
 
LOV Oracle
Expenditure Batch Status
 
LOV Oracle
Display Released Batch?
 
LOV Oracle
Blitz Report™