Example of Writing SQL Query with Oracle Cloud (Employee Annual Leave Entries

Hello, today I will share the notes I took from the Oracle Cloud SQL training with you.

The requested fields were as follows:

• person_number
• Annual leave entries for employees who joined the company in 2023 in a live environment
• assignment_number (those with ET)
• employer
• absence start date
• absence start time
• absence entry id

Here, I select ‘New’ from the Catalog section and add a Data Model

The tables I will use in this SQL query are as follows:

• PER_ALL_PEOPLE_F: This table contains personal information about employees.
• PER_ALL_ASSIGNMENTS_M Table: This table contains assignment information for employees.
• HR_ORGANIZATION_UNITS Table: It contains general information about business units.
• ANC_ABSENCE_TYPES_F_TL Table: It includes language translations of absence types.
• PER_PERIODS_OF_SERVICE Table: It contains information about employees’ periods of service.
• ANC_PER_ABS_ENTRIES Table: It includes absence entries.

This way, I can see the fields of the tables I will use. I am doing this for all the tables I will use. I am writing a query that will bring the Assignment Numbers with ‘ET.’

At the beginning of my query, I write ‘Select’ inside ‘Select’ because I will only bring records with ‘ET’ in the Assignment Number. I named it PAAM1 here because I will make another naming below as PAAM.

Here, after selecting Assignment Types ‘ET’ and Assignment Status ‘Active,’ I confirm the validity of the records by bringing the system date with TRUNC SYSDATE (SYDATUM in Sap) and checking if the records are between the Effective Start Date (Begda in Sap) and End Date (Endda in Sap). (Is the record valid for the current date?)

Then, I match the personnel ID in the PAPF (Per_All_People_F) table with the personnel ID in PAAM1. After that, I add the other fields I want inside the Select. Here, after saying TO_CHAR to format the Date data as I want, I enter the desired day-month-year format.

In the ‘From’ section, I enter the tables and abbreviations I will use in my query. After taking the records with the Primary Flag field in the Period of Service table as ‘Yes,’ to prevent duplicate data and get the last record, we take the Maximum and connect it with those whose Flags are ‘Yes.’

After querying the validity date with TRUNC SYSDATE in PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_M tables, I connect them with their IDs. In the PER_ALL_ASSIGNMENTS_M table, I connect the Assignment Types E and C (E > Employee in the system, P > Pending Worker not yet an employee (opened in the system when they will start working), C > Contingent Worker subcontractor, ET > There is an assignment in the system. Used in transfer templates.) with the Assignment Status Active (Active, Passive, Suspended (Suspended)), Assignment Effective Last Change, and Primary Flag ‘Yes’ data. I match them with the Personnel IDs in PER_ALL_PEOPLE_F and PER_PERIODS_OF_SERVICE.

In the PER_PERIODS_OF_SERVICE table, I match the Primary Flag ‘Yes’ data (Is the assignment primary?), with the Date being 2023 (I formatted the Year data as I wanted with To_Char). I matched the absence IDs in ANC_ABSENCE_TYPES_F_TL with the absence IDs in ANC_PER_ABS_ENTRIES. I made the Language in the translation table ‘US.’ Then, I checked the validity dates of the data with TRUNC SYSDATE. I selected those with the word ‘Annual’ in the leave name to bring the annual leaves.

I matched the Personnel IDs in ANC_PER_ABS_ENTRIES with the PER_ALL_PEOPLE_F table. To prevent historical data, I wanted the Start Date in ANC_PER_ABS_ENTRIES to be greater than or equal to the Date Start in PER_PERIODS_OF_SERVICE. I selected the Submit and Approval status of the leave data to be Approved. After matching the Legal Entity ID in the PER_ALL_ASSIGNMENTS_M table with the Organization IDs in the HR_ORGANIZATION_UNITS table, I ensured that the dates of the leaves in ANC_PER_ABS_ENTRIES increase.

SELECT (SELECT PAAM1.ASSIGNMENT_NUMBER
            FROM PER_ALL_ASSIGNMENTS_M PAAM1
           WHERE     PAAM1.ASSIGNMENT_TYPE = 'ET'
                 AND PAAM1.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
                 AND TRUNC (SYSDATE) BETWEEN paam1.effective_start_date
                                         AND paam1.EFFECTIVE_END_DATE
                 AND PAPF.PERSON_ID = PAAM1.PERSON_ID)    ASG_NUM,
         papf.PERSON_NUMBER,
         TO_CHAR (anc.START_DATE, 'dd.mm.yyyy')           absence_date,
         anc.START_TIME                                   START_TIME,
         hou.name 
                                                 legal_emp,
                                                 
         anc.per_absence_entry_id
    FROM per_all_people_f      papf,
      
         per_all_assignments_m paam,
         HR_ORGANIZATION_UNITS hou,
         ANC_ABSENCE_TYPES_F_TL TL,
         (SELECT *
            FROM PER_PERIODS_OF_SERVICE a
           WHERE     a.primarY_flag = 'Y'
                 AND A.PERIOD_OF_SERVICE_ID =
                     (SELECT MAX (B.PERIOD_OF_SERVICE_ID)
                        FROM PER_PERIODS_OF_SERVICE B
                       WHERE B.primary_flag = 'Y' AND A.PERSON_ID = B.PERSON_ID))
         PPOS,
         ANC_PER_ABS_ENTRIES   anc
   WHERE     papf.person_id = paam.person_id
         AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                                 AND PAPF.EFFECTIVE_END_DATE
         AND TRUNC (SYSDATE) BETWEEN paam.effective_start_date
                                 AND paam.EFFECTIVE_END_DATE
         AND PAAM.ASSIGNMENT_TYPE IN ('E', 'C')
         AND PAAM.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
         AND PAAM.EFFECTIVE_LATEST_CHANGE = 'Y'
         AND PAAM.PRIMARY_FLAG = 'Y'
         AND ppos.period_of_service_id = paam.period_of_service_id
         AND ppos.primary_flag = 'Y'
         AND TO_CHAR (PPOS.DATE_START, 'YYYY') = '2023'
         AND TL.ABSENCE_TYPE_ID = ANC.ABSENCE_TYPE_ID
         AND TL.LANGUAGE = 'US'
         AND TRUNC (SYSDATE) BETWEEN TL.EFFECTIVE_START_DATE
                                 AND TL.EFFECTIVE_END_DATE
         AND TL.NAME LIKE '%Annual%'
         AND anc.person_id = papf.person_id
         AND anc.start_date >= ppos.date_start
         AND ANC.ABSENCE_STATUS_CD = 'SUBMITTED'
         AND ANC.APPROVAL_STATUS_CD = 'APPROVED'
         AND Paam.legal_entity_id = HOU.ORGANIZATION_ID
ORDER BY anc.start_date ASC