Query to find the Whether the Approver Status is Active or Suspended.

Query to find the Assignment Status for a Particular Employee

SELECT 
       P.full_name, 
       PAST.per_system_status STATUS, 
       ORG.NAME               ORG_NAME, 
       p.employee_number, 
FROM   apps.per_people_f p, 
       apps.per_assignments_f a, 
       apps.hr_organization_units org, 
       apps.per_assignment_status_types past 
WHERE  a.person_id = p.person_id 
       AND a.assignment_status_type_id = past.assignment_status_type
       AND a.primary_flag = 'Y' 
       AND Trunc(sysdate) BETWEEN p.effective_start_date AND 
                                  p.effective_end_date 
       AND Trunc(sysdate) BETWEEN a.effective_start_date AND 
                                  a.effective_end_date 
       AND a.assignment_type IN ( 'E', 'C' ) 
       AND a.organization_id = org.organization_id 
       AND past.per_system_status IN ( 'SUSP_ASSIGN' ) 
       AND Upper(full_name) LIKE '%SREEKANTH%'

per_system_status value will be 'ACTIVE_ASSIGN', 'ACTIVE_CWK' or 'SUSP_ASSIGN'.


Query to find All Employees/Contingent Workers with Active Assignment Status 

SELECT P.person_id, 
       P.full_name, 
       A.organization_id, 
       A.assignment_type, 
       PAST.per_system_status STATUS, 
       P.email_address, 
       ORG.NAME               ORG_NAME, 
       p.employee_number, 
       p.npw_number 
FROM   apps.per_people_f p, 
       apps.per_assignments_f a, 
       apps.hr_organization_units org, 
       apps.per_assignment_status_types past 
WHERE  a.person_id = p.person_id 
       AND a.assignment_status_type_id = past.assignment_status_type_id 
       AND past.per_system_status IN ( 'ACTIVE_ASSIGN', 'ACTIVE_CWK' ) 
       AND a.primary_flag = 'Y' 
       AND Trunc(sysdate) BETWEEN p.effective_start_date AND 
                                  p.effective_end_date 
       AND Trunc(sysdate) BETWEEN a.effective_start_date AND 
                                  a.effective_end_date 
       AND a.assignment_type IN ( 'E', 'C' ) 
       AND a.organization_id = org.organization_id 
      -- AND Upper(full_name) LIKE '%SREEKANTH%'


Query to find All Employees/Contingent Workers with Suspended Status 

SELECT P.person_id, 
       P.full_name, 
       A.organization_id, 
       A.assignment_type, 
       PAST.per_system_status STATUS, 
       P.email_address, 
       ORG.NAME               ORG_NAME, 
       p.employee_number, 
       p.npw_number 
FROM   apps.per_people_f p, 
       apps.per_assignments_f a, 
       apps.hr_organization_units org, 
       apps.per_assignment_status_types past 
WHERE  a.person_id = p.person_id 
       AND a.assignment_status_type_id = past.assignment_status_type_id 
       
       AND a.primary_flag = 'Y' 
       AND Trunc(sysdate) BETWEEN p.effective_start_date AND 
                                  p.effective_end_date 
       AND Trunc(sysdate) BETWEEN a.effective_start_date AND 
                                  a.effective_end_date 
       AND a.assignment_type IN ( 'E', 'C' ) 
       AND a.organization_id = org.organization_id 
       AND past.per_system_status IN ( 'SUSP_ASSIGN' ) 
     --  AND Upper(full_name) LIKE '%SREEKANTH%'