Skip to main content

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%'

Comments

Post a Comment

Popular posts from this blog

SQL Query to extract Oracle Purchase Order Information

SELECT   poh.po_header_id,    poh.type_lookup_code PO_TYPE,   poh.authorization_status PO_STATUS,   poh.segment1 PO_NUMBER,   pov.vendor_name SUPPLIER_NAME,   povs.vendor_site_code Location,   hrls.location_code Ship_To,   hrlb.location_code Bill_to,   pol.line_num ,   msib.segment1 Item,   pol.unit_price,   pol.quantity,   pod.amount_billed Amount,   pod.destination_subinventory,   ppf.full_name Buyer_Name,   poh.closed_Code  FROM   PO_HEADERS_ALL poh,   PO_LINES_ALL pol,   mtl_system_items_b msib,   PO_LINE_LOCATIONS_ALL poll,   PO_DISTRIBUTIONS_ALL pod,   po_vendors pov,   po_vendor_sites_All povs,   hr_locations_all hrls,   hr_locations_all hrlb,   per_all_people_f ppf,   po_line_types polt WHERE   1                         =1 AND polt.line...
Post a Comment
Read more

Query to find Operating Unit, Business Group and Legal Entity Information

SELECT   DISTINCT   hrl . country ,                  hroutl_bg . name              bg ,                  hroutl_bg . organization_id ,                  lep . legal_entity_id ,                  lep . name                    legal_entity ,                  hroutl_ou . name              ou_name ,               ...
Post a Comment
Read more

How To Enable / Disable Forms Personalization Option

Forms Personalization gives great flexibility to execute custom business logic without performing so much of technical work. To start forms personalization navigate to Help -> Diagnostics -> Custom Code -> Personalize But many time when we click on personalize it give below error  " Function is not available for this respnosibility. Change responsibilities or contact your System Administrator " To Enable access to forms personalization function we need to set below profile option.  -  Utilities:Diagnostics -> Yes / No It determines the diagnostics option is enabled for a user / responsibility or site, depending on the level profile option is set. Navigate to System Administrator -> Profile -> System Query for your user / responsibility for which you want to provide access. Set the value to 'Yes' , If you want allow access to forms personalization Since we change the profile option please change the respons...
Post a Comment
Read more