Query to get the involuntary deductions like garnishments, child support etc. information attached to an employee in Fusion HCM

-
 SELECT PAP.PERSON_NUMBER, 
       PPN.FIRST_NAME, 
    PPN.LAST_NAME,
    PAY.PAYROLL_NAME,
    PDCD.BASE_DISPLAY_NAME,
    VD.SOURCE_TYPE,
    VDL.BASE_NAME,
    PRI.VALUE1 AS AMOUNT,
    SUBSTR(PRI.EFFECTIVE_START_DATE,1,10) AS EFFECTIVE_START_DATE,
    SUBSTR(PRI.EFFECTIVE_END_DATE,1,10) AS EFFECTIVE_END_DATE,
    PDCC.CONTEXT_VALUE1 AS STATE_CODE,
    (SELECT HG.GEOGRAPHY_NAME
       FROM HZ_GEOGRAPHY_IDENTIFIERS HGI,
            HZ_GEOGRAPHIES HG
      WHERE HGI.IDENTIFIER_VALUE = (PDCC.CONTEXT_VALUE1||'-0-0')
    AND HG.GEOGRAPHY_TYPE = 'STATE'
    AND HG.GEOGRAPHY_USE = 'MASTER_REF'
    AND HG.COUNTRY_CODE = 'US'
    AND HG.GEOGRAPHY_ID = HGI.GEOGRAPHY_ID
    AND HGI.GEO_DATA_PROVIDER = 'VERTEX'
    AND HGI.GEOGRAPHY_USE = 'MASTER_REF'
    AND HGI.IDENTIFIER_SUBTYPE = 'GEO_CODE'
    AND HG.GEOGRAPHY_ID = HGI.GEOGRAPHY_ID) AS STATE,
    PDCC.CONTEXT_VALUE2 AS REFERENCE_CODE
  FROM PER_ALL_PEOPLE_F PAP,
    PER_PERSON_NAMES_F PPN,
    PER_ALL_ASSIGNMENTS_M PAA,
    PAY_REL_GROUPS_DN PREL,
    PAY_ASSIGNED_PAYROLLS_DN PAD,
    PAY_ALL_PAYROLLS_F PAY, 
    PAY_DIR_CARDS_F PDCF, 
    PAY_DIR_CARD_COMPONENTS_F PDCC,
    PAY_DIR_CARD_COMP_DEFS_VL PDCCD, 
    PAY_DIR_CARD_DEFINITIONS_VL PDCD, 
    PAY_VALUE_DEFINITIONS_F VD, 
    PAY_VALUE_DEFINITIONS_VL VDL, 
    PAY_RANGE_ITEMS_F PRI, 
    PAY_ELEMENT_TYPES_F PET
 WHERE PAP.PERSON_ID = PPN.PERSON_ID
   AND PPN.NAME_TYPE = 'GLOBAL'
   AND PAP.PERSON_ID = PAA.PERSON_ID
   AND PAA.ASSIGNMENT_TYPE = 'E'
   AND PAA.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
   AND PAA.ASSIGNMENT_NUMBER = PREL.ASSIGNMENT_NUMBER(+)
   AND PREL.PARENT_REL_GROUP_ID = PAD.PAYROLL_TERM_ID(+)
   AND PAD.PAYROLL_ID = PAY.PAYROLL_ID(+)
   AND PREL.PAYROLL_RELATIONSHIP_ID = PDCF.PAYROLL_RELATIONSHIP_ID
   AND PDCF.DIR_CARD_ID = PDCC.DIR_CARD_ID
   AND PDCC.DIR_CARD_COMP_DEF_ID = PDCCD.DIR_CARD_COMP_DEF_ID
   AND PDCCD.DIR_CARD_DEFINITION_ID = PDCD.DIR_CARD_DEFINITION_ID
   AND PDCD.BASE_DISPLAY_NAME = 'Involuntary Deductions' 
   AND PDCD.DIR_CARD_DEFINITION_ID = PDCD.DIR_CARD_DEFINITION_ID
   AND PDCC.DIR_CARD_COMP_ID = VD.SOURCE_ID
   AND VD.SOURCE_TYPE = 'PDCC'
   AND VD.PARENT_VALUE_DEFN_ID = VDL.VALUE_DEFN_ID
   AND VD.VALUE_DEFN_ID = PRI.VALUE_DEFN_ID
   AND PET.ELEMENT_TYPE_ID = PDCCD.ELEMENT_TYPE_ID
   AND TRUNC(SYSDATE) BETWEEN PAP.EFFECTIVE_START_DATE AND PAP.EFFECTIVE_END_DATE
   AND TRUNC(SYSDATE) BETWEEN PPN.EFFECTIVE_START_DATE AND PPN.EFFECTIVE_END_DATE
   AND TRUNC(SYSDATE) BETWEEN PAA.EFFECTIVE_START_DATE AND PAA.EFFECTIVE_END_DATE
   AND TRUNC(SYSDATE) BETWEEN PAY.EFFECTIVE_START_DATE AND PAY.EFFECTIVE_END_DATE
   AND TRUNC(SYSDATE) BETWEEN PDCF.EFFECTIVE_START_DATE AND PDCF.EFFECTIVE_END_DATE
   AND TRUNC(SYSDATE) BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE
 ORDER BY PAP.PERSON_NUMBER

Comments

Post a Comment

Popular posts from this blog

Query to get Salary Change details in Fusion HCM

-
 SELECT papf.person_number PERSON_NUMBER, (select full_name from per_person_names_f ppnf where ppnf.person_id = papf.person_id and name_type = 'GLOBAL' and trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date) person_name, ple.name LEGAL_EMPLOYE, (CASE  when cs.SALARY_BASIS_CODE = 'ANNUAL' THEN 'Salary Change'  else  'Hourly Rate Change' end)  ACTION_NAME, to_char(cs_old.salary_amount) OLD_DATA,  to_char(CS.salary_amount) NEW_DATA    ,TO_CHAR(cs.date_from, 'MM/DD/YYYY') EFFECTIVE_DATE    ,  TO_CHAR(cs.creation_date, 'MM/DD/YYYY') CHANGED_DATE    , cs.LAST_UPDATED_BY CHANGED_BY           from  fusion.CMP_SALARY cs,           CMP_SALARY cs_old,   PER_ALL_ASSIGNMENTS_M  PAAM,   PER_ALL_PEOPLE_F PAPF   ,per_legal_employers ple          where 1 = 1    AND PAPF.PERSON_ID = P...
Read more

Oracle Fusion HCM - Enhancements to Summary of Changes and History Sections in Redwood Workforce Structures Pages

-
Image
 The  Summary of Changes  and  History  sections on Redwood  Workforce Structures  pages now display  additional attributes , providing a more detailed view of historical and recent changes to your workforce data. These enhancements apply to the following Redwood pages: Redwood Page New Attributes in Summary of Changes and History Sections Departments Status Divisions Status Disability Organizations Status Enterprise HCM Information Status Jobs Annual Working Duration Scheduling Group Standard Annual Working Duration Standard Working Hours Standard Working Frequency Locations Annual Working Duration Standard Annual Working Duration Standard Working Hours Standard Working Frequency Reporting Establishments Status Worker Unions Status These additional attributes are now visible in both the  Summary of Changes  and  History  sections, enabling users to quickly access and review key data points related to structural and job-related...
Read more

🧾 Streamlining Payroll Operations with Oracle Payroll Activity Center

-
Image
Managing payroll efficiently across global teams has never been more critical—or more complex. Oracle Fusion HCM introduces the  Payroll Activity Center , part of the Redwood user experience, to help payroll teams take control with a centralized, intelligent, and highly actionable interface. Designed with usability and impact in mind, this hub simplifies payroll monitoring, boosts accuracy, and brings AI-powered insight to the forefront. 🌟 What Is the Payroll Activity Center? The  Payroll Activity Center  is an action-focused workspace tailored to payroll professionals. It aggregates relevant applications, displays role-specific KPIs, and provides intuitive navigation through the payroll cycle—all within Oracle’s new Redwood design system. This replaces the older Payroll Dashboard and brings everything you need into a single, streamlined view. 🔑 Key Features Aggregated Applications View relevant payroll applications, flows, and data all in one place. In-Workflow Communi...
Read more