Writing Annual Leave Reconciliation Form Query with Oracle HCM Cloud Sql

Calculated from the ANC_PER_ACRL_ENTRY_DTLS table from the Accrual For Progress Payments table.

Records with Type ‘FLDR’ are selected.

PER_PLAN_ENT_ID in the Accrual table and PER_PLAN_ENT_ID in the ANC_PER_PLAN_ENROLLMENT table for progress payments

Status is added to the Enrollment table.

PER_PLAN_ENT_ID is bound.

ANC_ABSENCE_PLANS_F_TL Table is used for your Annual Leave Plan.

PL_ID here is linked to the Absence Plan Id in the Accrual table.

I selected the Language field TR for the TL translation table.

We made an Annual Leave Plan, which is the plan we will use TL.Name part of.

We have ensured that data with a value of 0 does not come.

SELECT TL.NAME IZIN_PLANI, DTL.VALUE DURATION, DTL.PROCD_DATE,
DTL.TYPE
FROM ANC_PER_ACRL_ENTRY_DTLS DTL,

ANC_PER_PLAN_ENROLLMENT ENR,
ANC_ABSENCE_PLANS_F_TL TL
WHERE
1=1
AND DTL.TYPE= 'FLDR'

AND DTL.PER_PLAN_ENRT_ID = ENR.PER_PLAN_ENRT_ID
AND TRUNC (SYSDATE) BETWEEN ENR.ENRT_ST_DT AND ENR.ENRT_END_DT AND ENR.STATUS = 'A'
AND DTL.PL_ID = TL.ABSENCE_PLAN_ID
AND TL.LANGUAGE='TR'
AND TRUNC (SYSDATE) BETWEEN TL.EFFECTIVE_START_DATE
AND TL.EFFECTIVE_END_DATE
AND DTL.PERSON_ID = 300000436489700 
AND TL.NAME = 'Yıllık İzin Planı'
AND DTL.VALUE <> 0

Then we turn this query we wrote into an internal select and write Froma. We add Per_All_People_F and Per_All_Assigment tables.

Per_All_People_F and Assgiment Id,

We also connect Assigment Id with the assigment_id in the internal select we wrote.

After entering the validity dates conditions

We add Assigment Type, Assigment Primary Flag and Assigment Status type conditions to the query as standard.

SELECT PP.PERSON_ID,
A.DURATION, A.PROCD_DATE 
  FROM PER_ALL_PEOPLE_F      PP,
       PER_ALL_ASSIGNMENTS_M  ASG,
       (SELECT TL.NAME       IZIN_PLANI,
               DTL.VALUE     DURATION,
               DTL.PROCD_DATE,
               DTL.TYPE,
               DTL.PERSON_ID,
               DTL.ASSIGNMENT_ID
          FROM ANC_PER_ACRL_ENTRY_DTLS  DTL,
               ANC_PER_PLAN_ENROLLMENT  ENR,
               ANC_ABSENCE_PLANS_F_TL   TL
         WHERE     1 = 1
               AND DTL.TYPE = 'FLDR'
               AND DTL.PER_PLAN_ENRT_ID = ENR.PER_PLAN_ENRT_ID
               AND TRUNC (SYSDATE) BETWEEN ENR.ENRT_ST_DT AND ENR.ENRT_END_DT
               AND ENR.STATUS = 'A'
               AND DTL.PL_ID = TL.ABSENCE_PLAN_ID
               AND TL.LANGUAGE = 'TR'
               AND TRUNC (SYSDATE) BETWEEN TL.EFFECTIVE_START_DATE
                                       AND TL.EFFECTIVE_END_DATE
               AND DTL.PERSON_ID = 300000436489700
               AND TL.NAME = 'Yıllık İzin Planı'
               AND DTL.VALUE <> 0) A        
               WHERE     TRUNC (SYSDATE) BETWEEN PP.EFFECTIVE_START_DATE
                                      AND PP.EFFECTIVE_END_DATE
              AND PP.PERSON_ID = ASG.PERSON_ID
              AND TRUNC (SYSDATE) BETWEEN ASG.EFFECTIVE_START_DATE
                                      AND ASG.EFFECTIVE_END_DATE
              AND ASG.ASSIGNMENT_TYPE IN ('E', 'C')
              AND ASG.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
              AND ASG.PRIMARY_FLAG = 'Y'
              AND A.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID

Then I add the query containing the personnel data I wrote before as a separate data model.

SELECT PAPF.PERSON_NUMBER,
PAPF.PERSON_ID,
       UPPER (PPN.FIRST_NAME) || ' ' || UPPER (PPN.LAST_NAME)
           AS PERSONNAME,
       HAPTL.NAME
           AS POSITION,
       HOUFL.NAME
           AS DEPARTMAN,
       PJFL.NAME AS GOREV,
       TO_CHAR (PPOS.DATE_START, 'dd.mm.yyyy')
           AS START_DATE,
        TO_CHAR(   PAAM.ASS_ATTRIBUTE_DATE2,'dd.mm.yyyy')
           AS IZIN_BASLANGIC_TARIHI,
    
      (SELECT (UPPER (PPN2.FIRST_NAME) || ' ' || UPPER (PPN2.LAST_NAME))
         FROM PER_ASSIGNMENT_SUPERVISORS_F PASF, PER_PERSON_NAMES_F PPN2
        WHERE     TRUNC (SYSDATE) BETWEEN PASF.EFFECTIVE_START_DATE
                                      AND PASF.EFFECTIVE_END_DATE
              AND TRUNC (SYSDATE) BETWEEN PPN2.EFFECTIVE_START_DATE
                                      AND PPN2.EFFECTIVE_END_DATE
              AND PASF.ASSIGNMENT_ID = PAAM.ASSIGNMENT_ID
              AND PPN2.NAME_TYPE = 'GLOBAL'
              AND PASF.MANAGER_ID = PPN2.PERSON_ID
              AND PASF.PRIMARY_FLAG = 'Y'
              AND PASF.MANAGER_TYPE = 'LINE_MANAGER') LINE_MANAGER_PERSON_ID 
           
  FROM PER_ALL_PEOPLE_F               PAPF,
       PER_ALL_ASSIGNMENTS_M          PAAM,
       PER_PERSON_NAMES_F             PPN,
       HR_ALL_POSITIONS_F             HAPF,
       HR_ALL_POSITIONS_F_TL          HAPTL,
       PER_JOBS_F                     PJF,
       PER_JOBS_F_TL                  PJFL,
       HR_ORGANIZATION_UNITS          HOU,
       HR_ORG_UNIT_CLASSIFICATIONS_F  HOUCF,
       HR_ORGANIZATION_UNITS_F_TL     HOUFL,
       (SELECT *
          FROM PER_PERIODS_OF_SERVICE Z
         WHERE     Z.PRIMARY_FLAG = 'Y'
               AND Z.PERIOD_OF_SERVICE_ID =
                   (SELECT MAX (X.PERIOD_OF_SERVICE_ID)
                     FROM PER_PERIODS_OF_SERVICE X
                    WHERE X.PRIMARY_FLAG = 'Y' AND Z.PERSON_ID = X.PERSON_ID))
       PPOS
 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 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 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 PPN.EFFECTIVE_START_DATE
                               AND PPN.EFFECTIVE_END_DATE
       AND PAPF.PERSON_ID = PPN.PERSON_ID
       AND PPN.NAME_TYPE = 'GLOBAL'
       AND PAAM.POSITION_ID = HAPF.POSITION_ID
       AND PAAM.JOB_ID = PJF.JOB_ID
       AND PJF.JOB_ID = PJFL.JOB_ID
       AND PAAM.ASSIGNMENT_TYPE IN ('E', 'C')
       AND PAAM.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
       AND HAPTL.LANGUAGE = 'US'
       AND HAPF.POSITION_ID = HAPTL.POSITION_ID
       AND PJFL.LANGUAGE = 'US'
       AND HOUCF.ORGANIZATION_ID = HOU.ORGANIZATION_ID
       AND HOUCF.ORGANIZATION_ID = HOUFL.ORGANIZATION_ID
       AND HOU.ORGANIZATION_ID = PAAM.ORGANIZATION_ID
       AND HOUFL.LANGUAGE = 'US'
       AND PAAM.PERSON_ID = PAPF.PERSON_ID
       AND PAAM.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
       AND PPOS.PRIMARY_FLAG = 'Y'
       AND HOUCF.CLASSIFICATION_CODE = 'DEPARTMENT'

Then I drag and drop it to the relevant field.

This now returns the record in this query

When we take the Duration from the Leave table, we take it from the Progress payment table because the holidays are not deducted.

Since we will join the Query with Union All, we assign columns to tables in the form of null.

We replace Assignment Id with Person Id.

We created a new field called tour to see which is entitlement and which is permission

We added our STATUS_CD conditions to see the permissions submitted, approved and pending approval in the system.

SELECT PP.PERSON_ID,
A.DURATION,
A.START_DATE,
A.END_DATE,
A.TUR 
FROM PER_ALL_PEOPLE_F PP,
     PER_ALL_ASSIGNMENTS_M ASG,
     (SELECT TL.NAME     IZIN_PLANI,
      DTL.VALUE          DURATION,
      DTL.PROCD_DATE     START_DATE,
      NULL               END_DATE,
      DTL.TYPE,
      DTL.PERSON_ID,
      DTL.ASSIGNMENT_ID,
      'Hak Ediş'       TUR
      FROM ANC_PER_ACRL_ENTRY_DTLS DTL,
      ANC_PER_PLAN_ENROLLMENT ENR,
      ANC_ABSENCE_PLANS_F_TL TL
      WHERE DTL.TYPE ='FLDR'
      AND DTL.PER_PLAN_ENRT_ID = ENR.PER_PLAN_ENRT_ID
      AND TRUNC (SYSDATE) BETWEEN ENR.ENRT_ST_DT AND ENR.ENRT_END_DT
      AND ENR.STATUS = 'A'
      AND DTL.PL_ID = TL.ABSENCE_PLAN_ID
      AND TL.LANGUAGE = 'TR'
      AND TRUNC (SYSDATE) BETWEEN TL.EFFECTIVE_START_DATE 
                           AND TL.EFFECTIVE_END_DATE
                           
AND TL.NAME = 'Yıllık İzin Planı '
AND DTL.VALUE <> 0
UNION ALL
SELECT AAT.NAME   IZIN_PLANI,
        NULL      DURATION,
        ANC.START_DATE,
        ANC.END_DATE,
        NULL TYPE ,
        ANC.PERSON_ID,
        NULL    ASSIGMENT_ID,
        'IZIN' TUR
        FROM ANC_PER_ABS_ENTRIES ANC, ANC_ABSENCE_TYPES_F_TL AAT
        WHERE ANC.ABSENCE_STATUS_CD = 'SUBMITTED'
        AND ANC.APPROVAL_STATUS_CD IN ('APPROVED', 'AWAITING')
        AND ANC.ABSENCE_TYPE_ID = AAT.ABSENCE_TYPE_ID
        AND AAT.LANGUAGE = 'TR'
        AND TRUNC (SYSDATE) BETWEEN AAT.EFFECTIVE_START_DATE 
        AND AAT.EFFECTIVE_END_DATE 
        AND AAT.NAME = 'Yıllık İzin') A
        WHERE TRUNC (SYSDATE) BETWEEN PP.EFFECTIVE_START_DATE 
        AND PP.EFFECTIVE_END_DATE 
        AND PP.PERSON_ID = ASG.PERSON_ID
        AND TRUNC (SYSDATE) BETWEEN ASG.EFFECTIVE_START_DATE 
        AND ASG.EFFECTIVE_END_DATE 
        AND ASG.ASSIGNMENT_TYPE IN ('E', 'C')
        AND ASG.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
        AND ASG.PRIMARY_FLAG = 'Y'
        AND A.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
        ORDER BY START_DATE DESC

Then we write a separate internal select to calculate the duration of the leave record.

Since the permit records are kept in ABS, we entered the Type as abs.

SELECT SUM(DTL.VALUE)
FROM ANC_per_ACRL_ENTRY_DTLS DTL,
     ANC_PER_PLAN_ENROLLMENT ENR,
     ANC_ABSENCE_PLANS_F_TL TL
     WHERE 
     DTL.TYPE = 'ABS'
     AND DTL.PER_PLAN_ENTR_ID = ENR.PER_PLAN_ENTR_ID
     AND TRUNC (SYSDATE) BETWEEN ENR.ENRT_ST_DT AND ENR.ENRT_END_DT
     AND ENR.STATUS = 'A'
     AND DTL.PL_ID = TL.ABSENCE_PLAN_ID
     AND TL.LANGUAGE = 'TR'
     AND TRUNC (SYSDATE) BETWEEN TL.EFFECTIVE_START_DATE AND TL.EFFECTIVE_END_DATE
     AND DTL.PER_ABSENCE_ENTRY_ID = ANC.PER_ABSENCE_ENTRY_ID
     AND TL.NAME = 'Yıllık İzin Planı'
     and dtl.value <> 0

I paste my query that I wrote in Duration section with NVL function to return the duration in anc if it is empty.

  SELECT PP.PERSON_ID,
         A.DURATION,
         A.START_DATE,
         A.END_DATE,
         A.TUR
    FROM PER_ALL_PEOPLE_F     PP,
         PER_ALL_ASSIGNMENTS_M ASG,
         (SELECT TL.NAME            IZIN_PLANI,
                 DTL.VALUE          DURATION,
                 DTL.PROCD_DATE     START_DATE,
                 NULL               END_DATE,
                 DTL.TYPE,
                 DTL.PERSON_ID,
                 DTL.ASSIGNMENT_ID,
                 'Hak Ediş'        TUR
            FROM ANC_PER_ACRL_ENTRY_DTLS DTL,
                 ANC_PER_PLAN_ENROLLMENT ENR,
                 ANC_ABSENCE_PLANS_F_TL TL
           WHERE     DTL.TYPE = 'FLDR'
                 AND DTL.PER_PLAN_ENRT_ID = ENR.PER_PLAN_ENRT_ID
                 AND TRUNC (SYSDATE) BETWEEN ENR.ENRT_ST_DT AND ENR.ENRT_END_DT
                 AND ENR.STATUS = 'A'
                 AND DTL.PL_ID = TL.ABSENCE_PLAN_ID
                 AND TL.LANGUAGE = 'TR'
                 AND TRUNC (SYSDATE) BETWEEN TL.EFFECTIVE_START_DATE
                                         AND TL.EFFECTIVE_END_DATE
                 AND TL.NAME = 'Yıllık İzin Planı '
                 AND DTL.VALUE <> 0
          UNION ALL
          SELECT AAT.NAME    IZIN_PLANI,
                 NVL (
                     (SELECT SUM (DTL.VALUE)
                       FROM ANC_PER_ACRL_ENTRY_DTLS DTL,
                            ANC_PER_PLAN_ENROLLMENT ENR,
                            ANC_ABSENCE_PLANS_F_TL TL
                      WHERE     DTL.TYPE = 'ABS'
                            AND DTL.PER_PLAN_ENTR_ID = ENR.PER_PLAN_ENTR_ID
                            AND TRUNC (SYSDATE) BETWEEN ENR.ENRT_ST_DT
                                                    AND ENR.ENRT_END_DT
                            AND ENR.STATUS = 'A'
                            AND DTL.PL_ID = TL.ABSENCE_PLAN_ID
                            AND TL.LANGUAGE = 'TR'
                            AND TRUNC (SYSDATE) BETWEEN TL.EFFECTIVE_START_DATE
                                                    AND TL.EFFECTIVE_END_DATE
                            AND DTL.PER_ABSENCE_ENTRY_ID =
                                ANC.PER_ABSENCE_ENTRY_ID
                            AND TL.NAME = 'Yıllık İzin Planı'
                            AND DTL.VALUE <> 0),
                     ANC.DURATION)      DURATION,
                 ANC.START_DATE,
                 ANC.END_DATE,
                 NULL        TYPE,
                 ANC.PERSON_ID,
                 NULL        ASSIGMENT_ID,
                 'IZIN'      TUR
            FROM ANC_PER_ABS_ENTRIES ANC, ANC_ABSENCE_TYPES_F_TL AAT
           WHERE     ANC.ABSENCE_STATUS_CD = 'SUBMITTED'
                 AND ANC.APPROVAL_STATUS_CD IN ('APPROVED', 'AWAITING')
                 AND ANC.ABSENCE_TYPE_ID = AAT.ABSENCE_TYPE_ID
                 AND AAT.LANGUAGE = 'TR'
                 AND TRUNC (SYSDATE) BETWEEN AAT.EFFECTIVE_START_DATE
                                         AND AAT.EFFECTIVE_END_DATE
                 AND AAT.NAME = 'YıLLıK İZIN') A
   WHERE     TRUNC (SYSDATE) BETWEEN PP.EFFECTIVE_START_DATE
                                 AND PP.EFFECTIVE_END_DATE
         AND PP.PERSON_ID = ASG.PERSON_ID
         AND TRUNC (SYSDATE) BETWEEN ASG.EFFECTIVE_START_DATE
                                 AND ASG.EFFECTIVE_END_DATE
         AND ASG.ASSIGNMENT_TYPE IN ('E', 'C')
         AND ASG.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
         AND ASG.PRIMARY_FLAG = 'Y'
         AND A.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
ORDER BY START_DATE DESC

Then we changed the Text of the Type section using Case when.

CASE WHEN ANC.APPROVAL_STATUS_CD = 'APPROVED' THEN 'Onaylandı' ELSE 'Onay Bekliyor' END
                      TUR

We have added the Value field to the Leave Progress section as Progress Period.

DTL.VALUE          HAKEDIS_DURATION,
Null                      Absence_Duration

Followed by

NVL (
(SELECT SUM (DTL.VALUE)
FROM ANC_PER_ACRL_ENTRY_DTLS DTL,
ANC_PER_PLAN_ENROLLMENT ENR,
ANC_ABSENCE_PLANS_F_TL TL
WHERE DTL.TYPE = 'ABS'
AND DTL.PER_PLAN_ENTR_ID = ENR.PER_PLAN_ENTR_ID
AND TRUNC (SYSDATE) BETWEEN ENR.ENRT_ST_DT
AND ENR.ENRT_END_DT
AND ENR.STATUS = 'A'
AND DTL.PL_ID = TL.ABSENCE_PLAN_ID
AND TL.LANGUAGE = 'TR'
AND TRUNC (SYSDATE) BETWEEN TL.EFFECTIVE_START_DATE
AND TL.EFFECTIVE_END_DATE
AND DTL.PER_ABSENCE_ENTRY_ID =
ANC.PER_ABSENCE_ENTRY_ID
AND TL.NAME = 'Yıllık İzin Planı'
AND DTL.VALUE <> 0),
ANC.DURATION)

I paste this part to Absence_Duration toUnion All Part 2.

In the Select section, I enter the progress payment and absence periods.

Then, we subtract the leave based on person id from the progress payments for the running leave balance and then add them up.

SUM (NVL (A.HAKEDIS_DURATION, 0 ) - NVL(A.ABSENCE_DURATION,0))
         OVER (PARTITION BY PP.PERSON_ID ORDER BY A.START_DATE) RUNNING_TOTAL

Leave used without entitlement to annual leave becomes negative and then increases with annual leave entitlement

Then we write the permissions used with the running balance of permissions into the NVL function and add them up and write the days that can be used.

NVL ( SUM (NVL (A.HAKEDIS_DURATION, 0 ) - NVL(A.ABSENCE_DURATION,0))
         OVER (PARTITION BY PP.PERSON_ID ORDER BY A.START_DATE), 0 ) + NVL (A.ABSENCE_DURATION,0)  KULLANıLABILECEK_GUN

We add Adjustment records for the scenario where an adjustment record is entered manually.

  DTL.TYPE IN ( 'FLDR', 'ADJOTH')

After adding the PER_SENIORITY_DATES_F psd table to the froma PER_SENIORITY_DATES_F psd table for the start date of leave entitlement, I connect it with my personnel table.

SELECT TO_CHAR (psd.SENIORITY_DATE, 'DD.MM.YYYY') Izın_Baslangıc_Tarihi 
FROM PER_SENIORITY_DATES_F psd
  WHERE   psd.SENIORITY_DATE_CODE = 'IZIN_HAK_TAR'
       AND papf.person_id = psd.person_id

We added a person parameter so that it can run individually for people.

 ppn.display_name = :pDisplayName

We add the Per_Users table for each user to see only themselves.

After adding my Froma per_users table, I mapped the user person id to the person_id in my person table.

In order to see the report myself, I added my username to the condition.

AND pu.person_id = papf.person_id
       AND (   pu.username = fnd_global.user_name
            OR fnd_global.user_name LIKE '%calkan%')

You can go to my account to look at the username.

To add a date parameter, after entering :pdate in the Select section, we replace the Trunc Sydate section in our Assigment table with :Pdate. Then we ensure that data greater than or equal to PPOS.Dateden comes. And in our internal select, we ensure that DTL. Procd_date is less than or equal to the date entered.

AND :pDate BETWEEN PAAM.EFFECTIVE_START_DATE
                      AND PAAM.EFFECTIVE_END_DATE


AND :pDate >= PPOS.DATE_sTART


AND DTL.PROCD_DATE <= :pDate

Then I write Display Name List of Valuem and connect it.

SELECT PPN.DISPLAY_NAME
  FROM per_all_assignments_m asg, per_person_names_f ppn, PER_USERS PU
 WHERE     1 = 1
       AND PPn.PERSON_ID = ASG.PERSON_ID
       AND TRUNC (SYSDATE) BETWEEN ASG.EFFECTIVE_START_DATE
                               AND ASG.EFFECTIVE_END_DATE
       AND ASG.ASSIGNMENT_TYPE IN ('E', 'C')
       AND ASG.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
       AND ASG.PRIMARY_FLAG = 'Y'
       AND TRUNC (SYSDATE) BETWEEN ppn.EFFECTIVE_START_DATE
                               AND ppn.EFFECTIVE_END_DATE
       AND ppn.name_type = 'GLOBAL'
       AND pu.person_id = PPN.person_id
       AND (   pu.username = fnd_global.user_name
            
            OR fnd_global.user_name LIKE '%calkan%')

Here for the p:date parameter, I entered the Sysdate system date as Default Value.

We write internal select to bring in those whose leave start date is greater than or equal to the start date of the leave plan.

 AND a.start_date >=
             (SELECT ENR.ENRT_ST_DT
                FROM ANC_PER_PLAN_ENROLLMENT ENR, ANC_ABSENCE_PLANS_F_TL TL
               WHERE     1 = 1
                     AND TRUNC (SYSDATE) BETWEEN ENR.ENRT_ST_DT
                                             AND ENR.ENRT_END_DT
                     AND ENR.STATUS = 'A'
                     AND enr.PLan_ID = TL.ABSENCE_PLAN_ID
                     AND TL.LANGUAGE = 'TR'
                     AND TRUNC (SYSDATE) BETWEEN TL.EFFECTIVE_START_DATE
                                             AND TL.EFFECTIVE_END_DATE
                     AND TL.NAME = 'Yıllık İzin Planı'
                     AND enr.person_id = pp.person_id)