Hello, today I will share with you an SQL query that retrieves the following information for active employees: name, surname, full name, personnel number, start date, position, role, department, location, location code, gender, date of birth, place of birth, ID number, salary, currency, expense center, and work type. Afterward, we will create a report using this query.

Before proceeding, I would like to mention the tables I will use in the query. The TL tables are translation tables

PER_PERSON_NAMES_F (PPNF): This table contains information related to individuals’ names.

PER_ALL_PEOPLE_F (PAPF): It includes general information about individuals.

HR_ALL_POSITIONS_F (HAPF): Contains general information about positions.

HR_ALL_POSITIONS_F_TL (HAPTL): Includes translations of position names in various languages.

PER_JOBS_F (PJF): Contains information related to job descriptions.

PER_JOBS_F_TL (PJFL): Includes translations of job descriptions in various languages.

PER_ALL_ASSIGNMENTS_M (PAAM): Contains information related to personnel assignments. The Assignment_id key in this table is often used in many queries.

HR_ORGANIZATION_UNITS (HOU): Includes general information about organizational units.

HR_ORG_UNIT_CLASSIFICATIONS_F (HOUCF): Contains information about the classification of organizational units.

HR_ORGANIZATION_UNITS_F_TL (HOUFL): Includes translations of organizational unit names in various languages.

PER_LOCATIONS (PL): Contains general information about locations.

PER_LOCATION_DETAILS_F (PLFD): Includes information about location details.

PER_LOCATION_DETAILS_F_TL (PLFL): Includes translations of location names in various languages.

PER_PERSONS (PP): Contains general information about individuals.

PER_NATIONAL_IDENTIFIERS (PNI): Includes information about national identification.

FND_LOOKUP_VALUES (FLV): This table contains “lookup” values used within the system, along with detailed information about these values. Lookup values typically represent constant values used in various lists within the system.

PER_PERIODS_OF_SERVICE (PPOS): Contains information about employment periods. It may include details about the time periods an individual has spent at a specific workplace.

CMP_SALARY (CS): A table that includes information about salary. It may contain details about employee salaries, personnel information, and salary dates.

I will write each of the requested fields in a separate SQL, and then I will show you the final version.

Name -Surname

We are using the PPNF table for the Name Surname.

Here, I selected the name, surname, and full name fields, and entered the tables I will use into the FROM clause.

I checked whether the effective start dates of the tables’ data are valid by retrieving the current date.

To retrieve employee data, I linked the personnel number to my name table.

I also ensured that it brings the name type globally

SELECT PPNF.FIRST_NAME, PPNF.LAST_NAME, PPNF.FULL_NAME
 FROM PER_PERSON_NAMES_F PPNF, PER_ALL_PEOPLE_F PAPF
 WHERE TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE
 AND PAPF.EFFECTIVE_END_DATE
 AND TRUNC (SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE
 AND PPNF.EFFECTIVE_END_DATE
 AND PAPF.PERSON_ID = PPNF.PERSON_ID
 AND PPNF.NAME_TYPE = 'GLOBAL'

Here is output of this SQL.

Personal Number

Here, I used the same conditions for the personnel name

SELECT  PAPF.PERSON_NUMBER FROM PER_ALL_PEOPLE_F  PAPF
WHERE  TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE
                                 AND PAPF.EFFECTIVE_END_DATE

Starting Date

Here, in the SELECT part, I formatted the Hire Date from the ‘Period of Service’ table using the TO_CHAR function, specifying the desired day, month, year format within parentheses.

In the FROM part, we used a subquery. The reason for doing this is to reach the record with the most up-to-date ID (the most recent entry) in the PPOS table. After selecting the data with Primary_Flag set to Yes (Primary data), we matched the unique ID of this table to the record with the highest ID number in this table using the A and B abbreviations. (The highest ID represents the most recent data).

In doing so; After the Max command, we matched the data with Primary flags, and then, by matching the Person_ids of the abbreviations used in the query, we ensured it works for the same data.

SELECT   TO_CHAR (PPOS.DATE_START, 'dd.mm.yyyy')     AS START_DATE
FROM (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, PER_ALL_ASSIGNMENTS_M PAAM
         WHERE
             TRUNC (SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE
                                 AND PAAM.EFFECTIVE_END_DATE
         AND PAAM.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
         AND PPOS.PRIMARY_FLAG = 'Y'

Position

Here, after fetching the position name from HR_ALL_POSITIONS_F_TL (translation table of the position table), I enter my position table, translation table, personnel person table, and my assignment table, which I use as a bridge in all organizational data, into my FROM clause.

In the WHERE condition, after checking the effectiveness of the tables, I link the translation table with the position table using the position ID, my assignment table with the personnel table to get the data of the personnel using the personnel ID, and I also link my assignment table with HAPF table using the position ID. I entered the language option as Turkish.

SELECT HAPTL.NAME     AS POSITION
  FROM HR_ALL_POSITIONS_F     HAPF,
       HR_ALL_POSITIONS_F_TL  HAPTL,
       PER_ALL_ASSIGNMENTS_M  PAAM,
       PER_ALL_PEOPLE_F       PAPF
 WHERE     TRUNC (SYSDATE) BETWEEN HAPF.EFFECTIVE_START_DATE
                               AND HAPF.EFFECTIVE_END_DATE
       AND TRUNC (SYSDATE) BETWEEN HAPTL.EFFECTIVE_START_DATE
                               AND HAPTL.EFFECTIVE_END_DATE
       AND TRUNC (SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE
                               AND PAAM.EFFECTIVE_END_DATE
       AND TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE
                               AND PAPF.EFFECTIVE_END_DATE
       AND HAPF.POSITION_ID = HAPTL.POSITION_ID
       AND PAAM.PERSON_ID = PAPF.PERSON_ID
       AND PAAM.POSITION_ID = HAPF.POSITION_ID
       AND HAPTL.LANGUAGE = 'US'

Department

After selecting the name field from HR_ORGANIZATION_UNITS_F_TL,

In the FROM clause, I entered HR Organization tables, my translation table, and my bridge tables — Assignment and Personnel tables.

After specifying parameters to bring the valid dates in the WHERE condition,

I selected the Classification Code from the Organization table as the Department.

Then, after linking my translation table and organization tables with the organization ID, I connected them with the Assignment table as well.

I set the language option to English and linked my Personnel table with my Assignment table.

SELECT HOUFL.NAME     AS DEPARTMAN
  FROM HR_ORG_UNIT_CLASSIFICATIONS_F  HOUCF,
       HR_ORGANIZATION_UNITS_F_TL     HOUFL,
       PER_ALL_ASSIGNMENTS_M          PAAM,
       PER_ALL_PEOPLE_F               PAPF,
       HR_ORGANIZATION_UNITS          HOU
 WHERE     TRUNC (SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE
                               AND PAAM.EFFECTIVE_END_DATE
       AND TRUNC (SYSDATE) BETWEEN HOUCF.EFFECTIVE_START_DATE
                               AND HOUCF.EFFECTIVE_END_DATE
       AND TRUNC (SYSDATE) BETWEEN HOUFL.EFFECTIVE_START_DATE
                               AND HOUFL.EFFECTIVE_END_DATE
       AND TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE
                               AND PAPF.EFFECTIVE_END_DATE
       AND HOUCF.CLASSIFICATION_CODE = 'DEPARTMENT'
       AND HOUCF.ORGANIZATION_ID = HOU.ORGANIZATION_ID
       AND HOU.ORGANIZATION_ID = HOUFL.ORGANIZATION_ID
       AND HOU.ORGANIZATION_ID = PAAM.ORGANIZATION_ID
       AND HOUFL.LANGUAGE = 'US'
       AND PAAM.PERSON_ID = PAPF.PERSON_ID

Job

After entering the task name from the translation table in the SELECT part,

In the FROM part, I added the task table, translation table, Assignment table, and Personnel table.

After checking the validity of the tables,

I linked my Assignment table with the Personnel table using the person ID.

I connected the Assignment table with the task table using the task ID and linked the translation table with the task table using the task ID as well.

I selected English as the language in the translation table.

SELECT PJFL.NAME AS JOB
  FROM PER_JOBS_F             PJF,
       PER_JOBS_F_TL          PJFL,
       PER_ALL_ASSIGNMENTS_M  PAAM,
       PER_ALL_PEOPLE_F       PAPF
 WHERE     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 TRUNC (SYSDATE) BETWEEN PJF.EFFECTIVE_START_DATE
                               AND PJF.EFFECTIVE_END_DATE
       AND TRUNC (SYSDATE) BETWEEN PJFL.EFFECTIVE_START_DATE
                               AND PJFL.EFFECTIVE_END_DATE
       AND PAAM.PERSON_ID = PAPF.PERSON_ID
       AND PAAM.JOB_ID = PJF.JOB_ID
       AND PJF.JOB_ID = PJFL.JOB_ID
       AND PJFL.LANGUAGE = 'US'

Location Code and Location

After retrieving the location code and location name from the translation table using SELECT,

In the FROM clause, I added the location tables, translation table, my Assignment table, and my Personnel table.

After checking the validity date of the data,

I linked my Personnel table with my Assignment table using the person ID.

I connected the location tables with each other and linked them with the Assignment table using the location ID.

Afterward, I linked the Location Details table and the translation table through the location_details table.

Then, I selected English as the language in the translation table.

SELECT PL.INTERNAL_LOCATION_CODE, PLFL.LOCATION_NAME
  FROM PER_LOCATIONS              PL,
       PER_LOCATION_DETAILS_F     PLFD,
       PER_LOCATION_DETAILS_F_TL  PLFL,
       PER_ALL_ASSIGNMENTS_M      PAAM,
       PER_ALL_PEOPLE_F           PAPF
 WHERE     TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE
                               AND PAPF.EFFECTIVE_END_DATE
       AND TRUNC (SYSDATE) BETWEEN PLFD.EFFECTIVE_START_DATE
                               AND PLFD.EFFECTIVE_END_DATE
       AND TRUNC (SYSDATE) BETWEEN PLFL.EFFECTIVE_START_DATE
                               AND PLFL.EFFECTIVE_END_DATE
       AND TRUNC (SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE
                               AND PAAM.EFFECTIVE_END_DATE
       AND PAAM.PERSON_ID = PAPF.PERSON_ID
       AND PL.LOCATION_ID = PAAM.LOCATION_ID
       AND PL.LOCATION_ID = PLFD.LOCATION_ID
       AND PLFD.LOCATION_ID = PAAM.LOCATION_ID
       AND PLFD.LOCATION_DETAILS_ID = PLFL.LOCATION_DETAILS_ID
       AND PLFL.LANGUAGE = 'US'

Wage and Currency

After writing the salary amount and currency in the SELECT part from the salary table,

In the FROM part, I wrote the same inner select query we used for the entry date. (To retrieve the most up-to-date data again)

Then, I linked the salary table with the personnel table using the person ID.

SELECT CS.SALARY_AMOUNT,
         CS.CURRENCY_CODE FROM (SELECT *
            FROM CMP_SALARY a
           WHERE A.SALARY_ID = (SELECT MAX (B.SALARY_ID)
                                  FROM CMP_SALARY B
                                 WHERE A.PERSON_ID = B.PERSON_ID)) CS,
                                 PER_ALL_PEOPLE_F PAPF
                              
                                 WHERE TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE
                                 AND PAPF.EFFECTIVE_END_DATE 
                                 
                                 AND PAPF.PERSON_ID = CS.PERSON_ID

Gender

In this query, since I will use multiple Lookup tables and to avoid them overriding each other, in the SELECT part, I use inner select queries to specify the Lookup type and language so that they do not conflict. I name those equal to the gender table where the Lookup Code matches as ‘gender.’

In the FROM part, I added my gender table and my personnel table

After checking the validity date of the data, I matched the person ID in the personnel table with the person ID in the gender table and entered ‘TR’ as the Legislative Code in the gender table since I want to see Turkey

SELECT (SELECT flvg.meaning
         FROM FND_LOOKUP_VALUES FLVG
        WHERE     flvg.lookup_type = 'SEX'
              AND flvg.language = 'TR'
              AND flvg.lookup_code = pplf.sex)    AS GENDER
  FROM PER_PEOPLE_LEGISLATIVE_F PPLF,
       PER_ALL_PEOPLE_F PAPF
 WHERE     TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE
                               AND PAPF.EFFECTIVE_END_DATE
       AND TRUNC (SYSDATE) BETWEEN PPLF.EFFECTIVE_START_DATE
                               AND PPLF.EFFECTIVE_END_DATE
       AND PAPF.PERSON_ID = PPLF.PERSON_ID
       AND PPLF.LEGISLATION_CODE = 'TR'

Doğum Tarihi ve Doğum Yeri

In the SELECT part, I formatted the date data using TO_CHAR to represent it in the day, month, year format and added the place of birth field next to it

In the FROM part, I added the PER_PERSONS table where the birth data is stored and my personnel table

In the WHERE part, after checking the effective dates of the data, I link the tables I used based on the person ID.

SELECT TO_CHAR (PP.DATE_OF_BIRTH, 'dd.mm.yyyy')     AS BIRTH_DATE,
       PP.TOWN_OF_BIRTH
  FROM PER_PERSONS PP, per_all_people_f papf
 WHERE     TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE
                               AND PAPF.EFFECTIVE_END_DATE
       AND PAPF.PERSON_ID = PP.PERSON_ID

TC Identification Number

After writing the ID number from the table containing national identification information into the SELECT part, I added the table I used

Here, after checking the validity date of the data, I linked the identity table with the personnel table using the person ID. I selected the identifier type in the identity table as ‘TC Identification Number.’

SELECT PNI.NATIONAL_IDENTIFIER_NUMBER
  FROM PER_NATIONAL_IDENTIFIERS PNI, PER_ALL_PEOPLE_F PAPF
 WHERE     TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE
                               AND PAPF.EFFECTIVE_END_DATE
       AND PAPF.PERSON_ID = PNI.PERSON_ID
       AND PNI.NATIONAL_IDENTIFIER_TYPE = 'ORA_HRX_TC_KIMLIK_NO'

Expense Center

Here, after receiving support from the functional consultant and learning the process, I executed the process using the Case When structure.

Here, sequentially, if the data in the Assignment table is not empty, I instructed it to take the data from the Assignment table; if the data in the Position table is not empty, I told it to take the data from the Position table. If both are empty, I instructed it to take the data from the HR Organization Unit table.

In the FROM part, I entered my tables.

In the WHERE part, after querying the validity date of the data, I linked my Assignment-Personnel table with the person ID, my Assignment-Position table with the position ID, and my Assignment-Organizational Unit table with the organization ID.

-- PAAM. If ASS_ATTRIBUTE2 is not empty, I took it
-- If it is empty and HR_ALL_POSITIONS_F ATTRIBUTE1 is not empty, I took it
-- If it is still empty, I took the HR_ORGANIZATION_UNITS ATTRIBUTE1 (which cannot be empty)
SELECT (CASE
            WHEN PAAM.ASS_ATTRIBUTE2 IS NOT NULL THEN PAAM.ASS_ATTRIBUTE2
            WHEN HAPF.ATTRIBUTE1 IS NOT NULL THEN HAPF.ATTRIBUTE1
            ELSE HOU.ATTRIBUTE1
        END)    AS MASRAF
  FROM PER_ALL_ASSIGNMENTS_M  PAAM,
       HR_ALL_POSITIONS_f     HAPF,
       HR_ORGANIZATION_UNITS  HOU,
       PER_ALL_PEOPLE_F       PAPF
 WHERE  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 TRUNC (SYSDATE) BETWEEN HAPF.EFFECTIVE_START_DATE
                               AND HAPF.EFFECTIVE_END_DATE
                               
                                  AND PAAM.PERSON_ID = PAPF.PERSON_ID
                                  AND PAAM.POSITION_ID = HAPF.POSITION_ID
                                  AND HOU.ORGANIZATION_ID = PAAM.ORGANIZATION_ID

Work Type

Here, similar to Gender, we use a Lookup Table. To prevent Lookup tables from conflicting, we utilized an inner select structure.

After adding the Lookup table to the FROM clause, we entered the Lookup Code, language, and Lookup Type for the relevant data.

In the FROM part, I added my Assignment and Personnel tables

. After checking the validity date of the data in the WHERE part, I linked my Assignment table with my Personnel table, as usual, using the Person_Id.

SELECT (SELECT FLVA.MEANING
         FROM FND_LOOKUP_VALUES FLVA
        WHERE     FLVA.LOOKUP_CODE = PAAM.EMPLOYMENT_CATEGORY
              AND FLVA.LANGUAGE = 'TR'
              AND FLVA.LOOKUP_TYPE = 'EMP_CAT')    AS CALISMAS
  FROM PER_ALL_ASSIGNMENTS_M PAAM, PER_ALL_PEOPLE_F PAPF
 WHERE     TRUNC (SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE
                               AND PAAM.EFFECTIVE_END_DATE
       AND TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE
                               AND PAPF.EFFECTIVE_END_DATE
       AND  PAAM.PERSON_ID = PAPF.PERSON_ID

AND PAAM.ASSIGNMENT_TYPE IN (‘E’, ‘C’) Here, I am selecting employees with Assignment Types ‘E’ (Employee in the system) and ‘C’ (Contingent Worker, contractor) where: — ‘E’ represents an employee currently active in the system. — ‘C’ represents a Contingent Worker who is not actively working yet (will start working when opened in the system).

AND PAAM.ASSIGNMENT_STATUS_TYPE = ‘ACTIVE’
Here, I set the Status Type in the Assignment table to ‘ACTIVE’.
Finally, I ensured that the data is retrieved in ascending order based on the personnel name.

SELECT PPNF.FIRST_NAME,
         PPNF.LAST_NAME,
         PPNF.FULL_NAME,
         PAPF.PERSON_NUMBER,
         TO_CHAR (PPOS.DATE_START, 'dd.mm.yyyy')     AS START_DATE,
         HAPTL.NAME                                  AS POSITION,
         HOUFL.NAME                                  AS DEPARTMAN,
         PJFL.NAME                                   AS JOB,
         PL.INTERNAL_LOCATION_CODE,
         PLFL.LOCATION_NAME,
         PNI.NATIONAL_IDENTIFIER_NUMBER,
         CS.SALARY_AMOUNT,
         CS.CURRENCY_CODE,
         (SELECT flvg.meaning
           FROM FND_LOOKUP_VALUES FLVG
          WHERE     flvg.lookup_type = 'SEX'
                AND flvg.language = 'TR'
                AND flvg.lookup_code = pplf.sex)     AS GENDER,
         TO_CHAR (PP.DATE_OF_BIRTH, 'dd.mm.yyyy')    AS BIRTH_DATE,
         PP.TOWN_OF_BIRTH,
         (SELECT FLVA.MEANING
           FROM FND_LOOKUP_VALUES FLVA
          WHERE     FLVA.LOOKUP_CODE = PAAM.EMPLOYMENT_CATEGORY
                AND FLVA.LANGUAGE = 'TR'
                AND FLVA.LOOKUP_TYPE = 'EMP_CAT')    AS CALISMAS,
         --PAAM. ASS_ATTRIBUTE2ye baktım DOLUYSA Aldım
         --boş ise pozisyon HR_ALL_POSITIONS_Fde ATTRIBUTE1 doluysa aldım
         --boş ise departman HR_ORGANIZATION_UNITS ATTRIBUTE1 aldım (boş olamaz)
         (CASE
              WHEN PAAM.ASS_ATTRIBUTE2 IS NOT NULL THEN PAAM.ASS_ATTRIBUTE2
              WHEN HAPF.ATTRIBUTE1 IS NOT NULL THEN HAPF.ATTRIBUTE1
              ELSE HOU.ATTRIBUTE1
          END)                                       AS MASRAF
    FROM PER_PERSON_NAMES_F           PPNF,
         PER_ALL_PEOPLE_F             PAPF,
         HR_ALL_POSITIONS_F           HAPF,
         HR_ALL_POSITIONS_F_TL        HAPTL,
         PER_JOBS_F                   PJF,
         PER_JOBS_F_TL                PJFL,
         PER_ALL_ASSIGNMENTS_M        PAAM,
         HR_ORGANIZATION_UNITS        HOU,
         HR_ORG_UNIT_CLASSIFICATIONS_F HOUCF,
         HR_ORGANIZATION_UNITS_F_TL   HOUFL,
         PER_LOCATIONS                PL,
         PER_LOCATION_DETAILS_F       PLFD,
         PER_LOCATION_DETAILS_F_TL    PLFL,
         PER_PERSONS                  PP,
         PER_NATIONAL_IDENTIFIERS     PNI,
         (SELECT *
            FROM CMP_SALARY a
           WHERE A.SALARY_ID = (SELECT MAX (B.SALARY_ID)
                                  FROM CMP_SALARY B
                                 WHERE A.PERSON_ID = B.PERSON_ID)) CS,
         PER_PEOPLE_LEGISLATIVE_F     PPLF,
         (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
   WHERE                                   --PAPF.PERSON_NUMBER = 'Test051223'
             TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE
                                 AND PAPF.EFFECTIVE_END_DATE
         AND TRUNC (SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE
                                 AND PPNF.EFFECTIVE_END_DATE
         AND TRUNC (SYSDATE) BETWEEN HAPF.EFFECTIVE_START_DATE
                                 AND HAPF.EFFECTIVE_END_DATE
         AND TRUNC (SYSDATE) BETWEEN HAPTL.EFFECTIVE_START_DATE
                                 AND HAPTL.EFFECTIVE_END_DATE
         AND TRUNC (SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE
                                 AND PAAM.EFFECTIVE_END_DATE
         AND TRUNC (SYSDATE) BETWEEN HOUCF.EFFECTIVE_START_DATE
                                 AND HOUCF.EFFECTIVE_END_DATE
         AND TRUNC (SYSDATE) BETWEEN HOUFL.EFFECTIVE_START_DATE
                                 AND HOUFL.EFFECTIVE_END_DATE
         AND TRUNC (SYSDATE) BETWEEN PLFD.EFFECTIVE_START_DATE
                                 AND PLFD.EFFECTIVE_END_DATE
         AND TRUNC (SYSDATE) BETWEEN PLFL.EFFECTIVE_START_DATE
                                 AND PLFL.EFFECTIVE_END_DATE
         AND TRUNC (SYSDATE) BETWEEN PPLF.EFFECTIVE_START_DATE
                                 AND PPLF.EFFECTIVE_END_DATE
         AND TRUNC (SYSDATE) BETWEEN PJF.EFFECTIVE_START_DATE
                                 AND PJF.EFFECTIVE_END_DATE
         AND TRUNC (SYSDATE) BETWEEN PJFL.EFFECTIVE_START_DATE
                                 AND PJFL.EFFECTIVE_END_DATE
         AND PAPF.PERSON_ID = PPNF.PERSON_ID
         AND PAAM.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
         AND PPOS.PRIMARY_FLAG = 'Y'
         AND PPNF.NAME_TYPE = 'GLOBAL'
         AND HAPF.POSITION_ID = HAPTL.POSITION_ID
         AND PAAM.PERSON_ID = PAPF.PERSON_ID
         AND PAAM.POSITION_ID = HAPF.POSITION_ID
         AND PAAM.JOB_ID = PJF.JOB_ID
         AND PJF.JOB_ID = PJFL.JOB_ID
         AND PL.LOCATION_ID = PAAM.LOCATION_ID
         AND PL.LOCATION_ID = PLFD.LOCATION_ID
         AND PLFD.LOCATION_ID = PAAM.LOCATION_ID
         AND PAPF.PERSON_ID = PP.PERSON_ID
         AND PAPF.PERSON_ID = PNI.PERSON_ID
         AND PLFD.LOCATION_DETAILS_ID = PLFL.LOCATION_DETAILS_ID
         AND PAPF.PERSON_ID = CS.PERSON_ID
         -- AND CS.ASSIGNMENT_ID = PAAM.ASSIGNMENT_ID
         AND PAPF.PERSON_ID = PPLF.PERSON_ID
         AND PAAM.ASSIGNMENT_TYPE IN ('E', 'C')
         AND PAAM.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
         AND HAPTL.LANGUAGE = 'US'
         AND PJFL.LANGUAGE = 'US'
         AND HOUCF.CLASSIFICATION_CODE = 'DEPARTMENT'
         AND HOUCF.ORGANIZATION_ID = HOU.ORGANIZATION_ID
         AND HOU.ORGANIZATION_ID = HOUFL.ORGANIZATION_ID
         AND HOU.ORGANIZATION_ID = PAAM.ORGANIZATION_ID
         AND HOUFL.LANGUAGE = 'US'
         AND PLFL.LANGUAGE = 'US'
         AND PPLF.LEGISLATION_CODE = 'TR'
         --  AND PAPF.PERSON_NUMBER = 'TST0032'
         AND PNI.NATIONAL_IDENTIFIER_TYPE = 'ORA_HRX_TC_KIMLIK_NO'
ORDER BY PPNF.FULL_NAME ASC

Afterwards, I am saving my data model and the included information. Then, I press the ‘Create Report’ button

Here, we select the data model and proceed.

We removed the checkmark for the ‘Total’ row at the bottom of this screen. We are bringing the data on this screen using drag-and-drop

From here, we choose ‘Edit Report’

We selected ‘View as a list’ at the top right corner of the screen.

I chose ‘Interactive’ as the default format since I will use Interactive in my example.

We adjust the width and length of the page from here. You can click on the screen area for this section to appear

From here, we can adjust the number of rows visible on the screen.

By clicking on the fields on the screen, we can make text and visual adjustments

I blurred the data on the screen as it contains personal information. The overall appearance of the report looks like this.