Saturday, February 15, 2025

Requisition Pending Approval for multiple Approves SQL Report in Oracle Fusion ORC.

 WITH RAW_DATA AS (
SELECT DISTINCT
IRV.REQUISITION_NUMBER,
IRV.TITLE,
WFS.ENDDATE,
WFS.STARTDATE,
WFS.ASSIGNEE
FROM
FA_FUSION_SOAINFRA.WFTASK WFT,
FA_FUSION_SOAINFRA.WFTASKASSIGNMENTSTATISTIC WFS,
IRC_REQUISITIONS_VL IRV,
IRC_PHASES_VL IPV,
IRC_STATES_VL ISV

WHERE
WFT.IDENTIFICATIONKEY = TO_CHAR(IRV.REQUISITION_ID)
AND IRV.CURRENT_PHASE_ID = IPV.PHASE_ID
AND IRV.CURRENT_STATE_ID = ISV.STATE_ID
AND WFT.TASKID = WFS.TASKID
AND IPV.NAME = 'Approval'
AND ISV.NAME = 'Pending'
AND (WFS.ENDACTION IN ('OUTCOME_UPDATE','COMPLETE')
OR WFS.ENDACTION IS NULL)
--AND IRV.REQUISITION_NUMBER = '10175'
),

ROWNUM_TABLE AS (
SELECT DISTINCT
REQUISITION_NUMBER,
TITLE,
ASSIGNEE,
STARTDATE,
ENDDATE,
(ROW_NUMBER() OVER (PARTITION BY REQUISITION_NUMBER ORDER BY STARTDATE)) ROW_NUM1
FROM
RAW_DATA
),

MAIN_TABLE AS (
SELECT DISTINCT
REQUISITION_NUMBER,
TITLE,
MIN(CASE WHEN ROW_NUM1=1 THEN ASSIGNEE END) ASSIGNEE1,
MIN(CASE WHEN ROW_NUM1=1 THEN STARTDATE END) STARTDATE1,
MIN(CASE WHEN ROW_NUM1=1 THEN ENDDATE END) ENDDATE1,
MIN(CASE WHEN ROW_NUM1=2 THEN ASSIGNEE END) ASSIGNEE2,
MIN(CASE WHEN ROW_NUM1=2 THEN STARTDATE END) STARTDATE2,
MIN(CASE WHEN ROW_NUM1=2 THEN ENDDATE END) ENDDATE2
FROM
ROWNUM_TABLE

GROUP BY REQUISITION_NUMBER,
TITLE
)

SELECT DISTINCT IRV.REQUISITION_NUMBER,
IRV.TITLE REQ_TITLE,
HAP.NAME POSITION_NAME,
HAP.POSITION_CODE,
HAOU_LE.NAME LE_NAME,
HAOU_BU.NAME BU_NAME,
HAOU_DEP.NAME DEP_NAME,
HLA.LOCATION_NAME,
PJ.NAME JOB_NAME,
PG.NAME GRADE_NAME,
MT.ASSIGNEE1 APPROVER1,
MT.STARTDATE1 ASSIGNED1DATE,
MT.ENDDATE1 APPROVED1DATE,
(CASE WHEN MT.STARTDATE1 IS NOT NULL AND MT.ENDDATE1 IS NULL THEN 'Pending'
WHEN MT.STARTDATE1 IS NOT NULL AND MT.ENDDATE1 IS NOT NULL THEN 'Completed' END) APPROVER1STATUS,
MT.ASSIGNEE2 APPROVER2,
MT.STARTDATE2 ASSIGNED2DATE,
MT.ENDDATE2 APPROVED2DATE,
(CASE WHEN MT.STARTDATE2 IS NOT NULL AND MT.ENDDATE2 IS NULL THEN 'Pending'
WHEN MT.STARTDATE2 IS NOT NULL AND MT.ENDDATE2 IS NOT NULL THEN 'Completed' END) APPROVER2STATUS

FROM
MAIN_TABLE MT,
HR_ALL_POSITIONS HAP,
HR_LOCATIONS_ALL HLA,
HR_ALL_ORGANIZATION_UNITS HAOU_DEP,
HR_ALL_ORGANIZATION_UNITS HAOU_BU,
HR_ALL_ORGANIZATION_UNITS HAOU_LE,
PER_JOBS PJ,
PER_GRADES PG,
IRC_REQUISITIONS_VL IRV

WHERE
IRV.REQUISITION_NUMBER = MT.REQUISITION_NUMBER
AND IRV.POSITION_ID = HAP.POSITION_ID
AND HAP.LOCATION_ID = HLA.LOCATION_ID(+)
AND HAP.ORGANIZATION_ID = HAOU_DEP.ORGANIZATION_ID(+)
AND HAP.BUSINESS_UNIT_ID = HAOU_BU.ORGANIZATION_ID(+)
AND IRV.LEGAL_EMPLOYER_ID = HAOU_LE.ORGANIZATION_ID(+)
AND HAP.JOB_ID = PJ.JOB_ID(+)
AND HAP.ENTRY_GRADE_ID = PG.GRADE_ID(+)

Employee Goals approved and total goals submitted status Report in Oracle Fusion HCM.

Select Distinct
HG.Person_ID
,papf.Person_Number
,ppnf.Display_Name
,TO_CHAR(HG.TARGET_COMPLETION_DATE,'DD-MM-YYYY') AS "TARGET_COMPLETION_DATE"
,TO_CHAR(HG.Start_Date,'DD-MM-YYYY') AS "GOAL_START_DATE"
--,HG.CATEGORY_CODE
,HG.GOAL_TYPE_CODE
--,HG.GOAL_NAME
,(select
    count(e.GOAL_PLAN_NAME)
from
    HRG_GOALS a
    ,HRG_GOAL_PLANS_VL b
    ,HRT_REVIEW_PERIODS_TL  c
    ,HRG_GOAL_PLAN_GOALS  d
    ,HRG_GOAL_PLANS_TL e
where a.person_id=hg.person_id
--and a.CATEGORY_CODE='BC_BGI'
and a.STATUS_CODE <>'CANCEL'
and e.GOAL_PLAN_NAME='Goals'
AND a.GOAL_TYPE_CODE='PERFORMANCE'
AND a.GOAL_ID = d.GOAL_ID
AND e.GOAL_PLAN_ID = d.GOAL_PLAN_ID
AND c.REVIEW_PERIOD_ID = b.REVIEW_PERIOD_ID
AND d.GOAL_PLAN_ID = b.GOAL_PLAN_ID
AND b.GOAL_PLAN_ID = e.GOAL_PLAN_ID
AND GOAL_VERSION_TYPE_CODE = 'ACTIVE'  
and (c.REVIEW_PERIOD_NAME in (:p_REVIEW_PERIOD) or 'ALL' in (:p_REVIEW_PERIOD || 'ALL'))) as     "BGoals"


,(select
    count(e.GOAL_PLAN_ID)
from
    HRG_GOALS a
    ,HRG_GOAL_PLANS_VL b
    ,HRT_REVIEW_PERIODS_TL  c
    ,HRG_GOAL_PLAN_GOALS  d
    ,HRG_GOAL_PLANS_TL e
where a.person_id=hg.person_id
--and a.CATEGORY_CODE='BC_WOG'
and e.GOAL_PLAN_NAME='WOGoals'
and a.STATUS_CODE <>'CANCEL'
AND a.GOAL_TYPE_CODE='PERFORMANCE'
AND a.GOAL_ID = d.GOAL_ID
AND e.GOAL_PLAN_ID = d.GOAL_PLAN_ID
AND c.REVIEW_PERIOD_ID = b.REVIEW_PERIOD_ID
AND d.GOAL_PLAN_ID = b.GOAL_PLAN_ID
AND b.GOAL_PLAN_ID = e.GOAL_PLAN_ID
AND GOAL_VERSION_TYPE_CODE = 'ACTIVE'  
and (c.REVIEW_PERIOD_NAME in (:p_REVIEW_PERIOD) or 'ALL' in (:p_REVIEW_PERIOD || 'ALL'))) as     "WOGoal"

,(select
    count(e.GOAL_PLAN_ID)
from
    HRG_GOALS a
    ,HRG_GOAL_PLANS_VL b
    ,HRT_REVIEW_PERIODS_TL  c
    ,HRG_GOAL_PLAN_GOALS  d
    ,HRG_GOAL_PLANS_TL e
where a.person_id=hg.person_id
--and a.CATEGORY_CODE  in ('BC_WOG','BC_BGI')
and e.GOAL_PLAN_NAME in ('WOGoals','BGoals')
and a.STATUS_CODE <>'CANCEL'
AND a.GOAL_TYPE_CODE='PERFORMANCE'
AND a.GOAL_ID = d.GOAL_ID
AND e.GOAL_PLAN_ID = d.GOAL_PLAN_ID
AND c.REVIEW_PERIOD_ID = b.REVIEW_PERIOD_ID
AND d.GOAL_PLAN_ID = b.GOAL_PLAN_ID
AND b.GOAL_PLAN_ID = e.GOAL_PLAN_ID
AND GOAL_VERSION_TYPE_CODE = 'ACTIVE'
and (c.REVIEW_PERIOD_NAME in (:p_REVIEW_PERIOD) or 'ALL' in (:p_REVIEW_PERIOD || 'ALL'))) as     "Total Number of Goals"
,(case  when (select
                  count(e.GOAL_PLAN_ID)
                from
                    HRG_GOALS a
                    ,HRG_GOAL_PLANS_VL b
                    ,HRT_REVIEW_PERIODS_TL  c
                    ,HRG_GOAL_PLAN_GOALS  d
                    ,HRG_GOAL_PLANS_TL e
                where a.person_id=hg.person_id
               -- and a.CATEGORY_CODE  in ('BC_WOG','BC_BGI')
                and e.GOAL_PLAN_NAME in ('WOGoals','BGoals')
                and a.STATUS_CODE <>'CANCEL'
                AND a.GOAL_TYPE_CODE='PERFORMANCE'
                AND a.GOAL_ID = d.GOAL_ID
                AND e.GOAL_PLAN_ID = d.GOAL_PLAN_ID
                AND c.REVIEW_PERIOD_ID = b.REVIEW_PERIOD_ID
                AND d.GOAL_PLAN_ID = b.GOAL_PLAN_ID
                AND b.GOAL_PLAN_ID = e.GOAL_PLAN_ID
                AND GOAL_VERSION_TYPE_CODE = 'ACTIVE'  and (c.REVIEW_PERIOD_NAME in (:p_REVIEW_PERIOD) or 'ALL' in (:p_REVIEW_PERIOD || 'ALL'))) >= 4
              then 'Minimum Goals Added'    
        when (select
                   count(e.GOAL_PLAN_ID)
               from
                   HRG_GOALS a
                   ,HRG_GOAL_PLANS_VL b
                   ,HRT_REVIEW_PERIODS_TL  c
                   ,HRG_GOAL_PLAN_GOALS  d
                   ,HRG_GOAL_PLANS_TL e
               where a.person_id=hg.person_id
               and e.GOAL_PLAN_NAME in ('WOGoals','BGoals')
              -- and a.CATEGORY_CODE  in ('BC_WOG','BC_BGI')
               and a.STATUS_CODE <>'CANCEL'
               AND a.GOAL_TYPE_CODE='PERFORMANCE'
               AND a.GOAL_ID = d.GOAL_ID
               AND e.GOAL_PLAN_ID = d.GOAL_PLAN_ID
               AND c.REVIEW_PERIOD_ID = b.REVIEW_PERIOD_ID
               AND d.GOAL_PLAN_ID = b.GOAL_PLAN_ID
               AND b.GOAL_PLAN_ID = e.GOAL_PLAN_ID
               AND GOAL_VERSION_TYPE_CODE = 'ACTIVE'  and (c.REVIEW_PERIOD_NAME in (:p_REVIEW_PERIOD) or 'ALL' in (:p_REVIEW_PERIOD || 'ALL'))) <4
        then 'No Minimum Added'
end) as "Final_Status"
,'Approved' Status
,(
    Select PAPF_MANAGER.PERSON_NUMBER
    From PER_PERSON_NAMES_F PPNF_MANAGER,
    PER_ASSIGNMENT_SUPERVISORS_F PASF,
    PER_ALL_PEOPLE_F PAPF_MANAGER,
    PER_ASSIGNMENT_SECURED_LIST_V PAAM_MANAGER
    Where PAPF.PERSON_ID=PASF.PERSON_ID
    AND PASF.MANAGER_TYPE = 'LINE_MANAGER'
    AND PAPF_MANAGER.PERSON_ID=PASF.MANAGER_ID
    AND TRUNC(SYSDATE) BETWEEN PASF.EFFECTIVE_START_DATE AND PASF.EFFECTIVE_END_DATE
    AND TRUNC(SYSDATE) BETWEEN PAPF_MANAGER.EFFECTIVE_START_DATE AND PAPF_MANAGER.EFFECTIVE_END_DATE
    AND PAPF_MANAGER.PERSON_ID =PPNF_MANAGER.PERSON_ID
    AND PPNF_MANAGER.NAME_TYPE='GLOBAL'
    AND PAAM.ASSIGNMENT_ID=PASF.ASSIGNMENT_ID
    AND TRUNC(SYSDATE) BETWEEN PPNF_MANAGER.EFFECTIVE_START_DATE AND PPNF_MANAGER.EFFECTIVE_END_DATE
    and PAAM_MANAGER.PERSON_ID=PAPF_MANAGER.PERSON_ID
    AND PAAM_MANAGER.PRIMARY_FLAG='Y'
    AND PAAM_MANAGER.ASSIGNMENT_STATUS_TYPE='ACTIVE'
    AND PAAM_MANAGER.PRIMARY_ASSIGNMENT_FLAG='Y'
    AND TRUNC(SYSDATE) BETWEEN PAAM_MANAGER.EFFECTIVE_START_DATE AND PAAM_MANAGER.EFFECTIVE_END_DATE
) "Reporting Manager Number"

,TO_CHAR(ppos.Date_Start,'yyyy/mm/dd') hiredate
,HAOU.NAME Department
,ppnf.FIRST_NAME
,ppnf.LAST_NAME
,paam.ASSIGNMENT_NUMBER
,PJ.NAME JOB_TITLE
,pgft.NAME   Grade
,PAAM.ASS_ATTRIBUTE1 "Plan Eligility"
,fabuv.BU_NAME
,HRPL.REVIEW_PERIOD_NAME
From
 HRG_GOALS HG
,PER_ALL_PEOPLE_F papf
,PER_PERSON_NAMES_F ppnf
,PER_ALL_ASSIGNMENTS_M paam
,PER_PERIODS_OF_SERVICE ppos
,HR_ALL_ORGANIZATION_UNITS HAOU
,PER_JOBS PJ
,PER_GRADES_F_TL pgft
,Fun_all_business_units_v fabuv
,HRG_GOAL_PLANS_VL HGVL
,HRT_REVIEW_PERIODS_TL HRPL
,HRG_GOAL_PLAN_GOALS HGPG
,HRG_GOAL_PLANS_TL HGPT
Where
    HG.PERSON_ID                 = papf.PERSON_ID
AND HG.GOAL_VERSION_TYPE_CODE = 'ACTIVE'
AND    papf.PERSON_ID               = ppnf.PERSON_ID
AND ppnf.NAME_TYPE               = 'GLOBAL'
AND papf.PERSON_ID               = paam.PERSON_ID
AND paam.PRIMARY_ASSIGNMENT_FLAG = 'Y'
AND paam.EFFECTIVE_LATEST_CHANGE = 'Y'
AND paam.ASSIGNMENT_TYPE         = 'E'
AND paam.ASSIGNMENT_STATUS_TYPE  = 'ACTIVE'
AND paam.assignment_id           = hg.assignment_id
--AND HG.CATEGORY_CODE in ('BGI','WOG')
AND HG.GOAL_TYPE_CODE='PERFORMANCE'
AND papf.PERSON_ID = ppos.PERSON_ID(+)
AND paam.PERIOD_OF_SERVICE_ID = ppos.PERIOD_OF_SERVICE_ID(+)
AND ppos.PRIMARY_FLAG = 'Y'
AND paam.ORGANIZATION_ID = HAOU.ORGANIZATION_ID(+)
AND TRUNC(SYSDATE) BETWEEN HAOU.EFFECTIVE_START_DATE(+) AND HAOU.EFFECTIVE_END_DATE(+)
AND PAAM.JOB_ID=PJ.JOB_ID(+)
and trunc(sysdate) between PJ.EFFECTIVE_START_DATE(+) and PJ.EFFECTIVE_END_DATE(+)
and paam.GRADE_ID = pgft.GRADE_ID(+)
AND TRUNC(SYSDATE) BETWEEN pgft.EFFECTIVE_START_DATE AND pgft.EFFECTIVE_END_DATE
and fabuv.BU_ID(+) = paam.BUSINESS_UNIT_ID
and fabuv.STATUS='A'
AND 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 paam.EFFECTIVE_START_DATE AND paam.EFFECTIVE_END_DATE
and (papf.PERSON_NUMBER in (:p_Person_Number) or 'ALL' in (:p_Person_Number || 'ALL'))
and HG.STATUS_CODE <>'CANCEL'
   AND HG.GOAL_ID = HGPG.GOAL_ID
   AND HGPT.GOAL_PLAN_ID = HGPG.GOAL_PLAN_ID
   AND HRPL.REVIEW_PERIOD_ID = HGVL.REVIEW_PERIOD_ID
   AND HGPG.GOAL_PLAN_ID = HGVL.GOAL_PLAN_ID
   AND HGVL.GOAL_PLAN_ID = HGPT.GOAL_PLAN_ID
   and to_char(hg.TARGET_COMPLETION_DATE(+),'yyyy/mm/dd')='2025/03/31'
   and (HRPL.REVIEW_PERIOD_NAME in (:p_REVIEW_PERIOD) or 'ALL' in (:p_REVIEW_PERIOD || 'ALL'))

Wednesday, November 13, 2024

SQL query will help the decide which employees to hire tomorrow in Oracle Fusion Cloud HCM

 The following SQL query will help the organization decide which employees to hire tomorrow. It will also prepare a report and scheduled as per businesses requirement in Oracle Fusion Cloud HCM

 

SELECT DISTINCT
        PAPF.PERSON_NUMBER as "EmployeeNumber",
         TO_CHAR (PAAM.PROJECTED_START_DATE,'DD-Mon-YYYY', 'nls_date_language=American') as "HireDate",
        PAAM.PROJECTED_START_DATE,
        PPNF.FIRST_NAME as "FirstName",
        PPNF.LAST_NAME as "LastName",
        PPNF.DISPLAY_NAME,
        PPNF.FIRST_NAME || (CASE WHEN PPNF.MIDDLE_NAMES IS NOT NULL THEN ' '||PPNF.MIDDLE_NAMES END) || ' '||PPNF.LAST_NAME as "FullName",
        PLDFT.LOCATION_NAME as "LocationName",
        PAPF_MGR.PERSON_NUMBER as "ManagerEmpNo",
        PPNF_MGR.DISPLAY_NAME as "ManagerName",
        HAPFT_MGR.NAME as "ManagerPostion",
        PJFT_MGR.NAME as "ManagerJob",
        PEA_MGR_W.EMAIL_ADDRESS "ManagerEmail",
        HL_WORK_CAT.MEANING as "WorkerCategory",
        HL_EMP_CAT.MEANING as "EmployeeCategory",
        (SELECT PEA.EMAIL_ADDRESS,
                   FROM PER_EMAIL_ADDRESSES PEA
                   WHERE EMAIL_TYPE ='W1'
                   AND PEA.PERSON_ID =PAPF.PERSON_ID AND Rownum=1 ) as "Work_Email"
FROM
        PER_ALL_ASSIGNMENTS_M PAAM,
        PER_PERIODS_OF_SERVICE PPOS,
        PER_ALL_PEOPLE_F PAPF,
        PER_PERSON_NAMES_F PPNF,
        --------------Location starts------------------
        PER_LOCATION_DETAILS_F PLDF,
        PER_LOCATION_DETAILS_F_TL PLDFT,
        -------------Manager starts--------------------
        PER_ASSIGNMENT_SUPERVISORS_F PASF,
        PER_ALL_PEOPLE_F PAPF_MGR,
        PER_PERSON_NAMES_F PPNF_MGR,
        PER_ALL_ASSIGNMENTS_M PAAM_MGR,
        HR_ALL_POSITIONS_F_TL HAPFT_MGR,
        PER_JOBS_F_TL PJFT_MGR,
        -----------------Manager emails--------
        PER_EMAIL_ADDRESSES PEA_MGR_W,
        ------------work category lookup--------
        HCM_LOOKUPS HL_WORK_CAT,
        ------------Employee category lookup--------
        HCM_LOOKUPS HL_EMP_CAT,
        ------------Phone category lookup--------
        PER_PHONES PP
WHERE    1=1
        AND PAAM.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
        AND PAAM.ASSIGNMENT_TYPE IN ('P')
        AND PAAM.EFFECTIVE_LATEST_CHANGE = 'Y'
        AND TRUNC(SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
        AND PAAM.PERSON_ID = PAPF.PERSON_ID
        AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
        AND PAAM.PERSON_ID = PPNF.PERSON_ID
        AND PPNF.NAME_TYPE(+) = 'GLOBAL'
        AND TRUNC(SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
        -------------location starts-------------------
        AND PAAM.LOCATION_ID = PLDF.LOCATION_ID(+)
        AND PLDF.LOCATION_DETAILS_ID = PLDFT.LOCATION_DETAILS_ID(+)
        AND PLDFT.LANGUAGE(+) = USERENV('LANG')
        AND TRUNC(SYSDATE) BETWEEN PLDF.EFFECTIVE_START_DATE(+) AND PLDF.EFFECTIVE_END_DATE(+)
        AND TRUNC(SYSDATE) BETWEEN PLDFT.EFFECTIVE_START_DATE(+) AND PLDFT.EFFECTIVE_END_DATE(+)
        ----------------Manager starts-----------------
        AND PAAM.ASSIGNMENT_ID = PASF.ASSIGNMENT_ID(+)
        AND PASF.MANAGER_TYPE(+) = 'LINE_MANAGER'
        AND TRUNC(SYSDATE) BETWEEN PASF.EFFECTIVE_START_DATE(+) AND PASF.EFFECTIVE_END_DATE(+)
        AND PASF.MANAGER_ID = PAPF_MGR.PERSON_ID(+)
        AND PASF.MANAGER_ASSIGNMENT_ID = PAAM_MGR.ASSIGNMENT_ID(+)
        AND PAAM_MGR.ASSIGNMENT_TYPE(+) = 'E'
        AND PAAM_MGR.EFFECTIVE_LATEST_CHANGE(+) = 'Y'
        AND TRUNC(SYSDATE) BETWEEN PAAM_MGR.EFFECTIVE_START_DATE(+) AND PAAM_MGR.EFFECTIVE_END_DATE(+)
        AND TRUNC(SYSDATE) BETWEEN PAPF_MGR.EFFECTIVE_START_DATE(+) AND PAPF_MGR.EFFECTIVE_END_DATE(+)
        AND PAPF_MGR.PERSON_ID = PPNF_MGR.PERSON_ID(+)
        AND PPNF_MGR.NAME_TYPE(+) = 'GLOBAL'
        AND TRUNC(SYSDATE) BETWEEN PPNF_MGR.EFFECTIVE_START_DATE(+) AND PPNF_MGR.EFFECTIVE_END_DATE(+)
        AND PAAM_MGR.POSITION_ID = HAPFT_MGR.POSITION_ID(+)
        AND TRUNC(SYSDATE) BETWEEN HAPFT_MGR.EFFECTIVE_START_DATE(+) AND HAPFT_MGR.EFFECTIVE_END_DATE(+)
        AND HAPFT_MGR.LANGUAGE(+) = SYS_CONTEXT('USERENV', 'LANG')
        AND PAAM_MGR.JOB_ID = PJFT_MGR.JOB_ID(+)
        AND PJFT_MGR.LANGUAGE(+) = SYS_CONTEXT('USERENV', 'LANG')
        AND TRUNC(SYSDATE) BETWEEN PJFT_MGR.EFFECTIVE_START_DATE(+) AND PJFT_MGR.EFFECTIVE_END_DATE(+)
        -----------------Manager emails --------
        AND PAAM_MGR.PERSON_ID = PEA_MGR_W.PERSON_ID(+)
        AND PEA_MGR_W.EMAIL_TYPE(+) = 'W1'
        ------------work category lookup starts--------
        AND HL_WORK_CAT.LOOKUP_TYPE(+) = 'EMPLOYEE_CATG'
        AND HL_WORK_CAT.LOOKUP_CODE(+) = PAAM.EMPLOYEE_CATEGORY
        ------------work category lookup ends----------
        AND HL_EMP_CAT.LOOKUP_TYPE(+) = 'EMP_CAT'
        AND HL_EMP_CAT.LOOKUP_CODE(+) = PAAM.EMPLOYMENT_CATEGORY
        AND PP.PERSON_ID(+) = PAAM.PERSON_ID
        AND PP.PHONE_TYPE(+) = 'HM'
        AND TRUNC(PAAM.PROJECTED_START_DATE) = TRUNC(SYSDATE+1)
        ORDER BY PAAM.PROJECTED_START_DATE

Friday, November 1, 2024

Absence Day Wise SQL Query in Oracle Cloud HCM

 

The following SQL query will help employees taken the Absence Leaves with Day Wise in Oracle Cloud HCM.

 

with advr as
(select trunc((Select min(start_date) from ANC_PER_ABS_ENTRIES)+lvl) dayy from
  (select level lvl from dual connect by level <= ((Select max(start_date) from ANC_PER_ABS_ENTRIES)-(Select min(start_date) from ANC_PER_ABS_ENTRIES)) ))

SELECT DISTINCT
       papf.person_number "EmployeeNumber"
     , pn.person_id
     , TO_CHAR(PPOS.DATE_START, 'MM/DD/YYYY') AS "HireDate"
     , pn.last_name
     , pn.first_name
     , pn.middle_names
     , pn.display_name
     , pn.full_name
     , hou.name                        DepartmentName
     , hou.organization_id             department_id
     , paa.assignment_id               asg_id
     , paa.assignment_type             asg_type
     , paa.assignment_number           asg_num
     , paa.primary_flag
     , employer.name                   LEemployer_name
     ,DECODE(abs.UOM,'D','Days','C','Calender Days',abs.UOM) UOM
     ,TO_CHAR(abs.start_date, 'DD-Mon-YYYY','NLS_DATE_LANGUAGE=AMERICAN') start_date
     ,TO_CHAR(abs.end_date, 'DD-Mon-YYYY','NLS_DATE_LANGUAGE=AMERICAN') end_date
     , (CASE WHEN abs.start_date=abs.END_date THEN TO_CHAR(abs.end_date, 'DD-Mon-YYYY','NLS_DATE_LANGUAGE=AMERICAN') ELSE TO_CHAR(abs.start_date, 'DD-Mon-YYYY','NLS_DATE_LANGUAGE=AMERICAN')||' - '||TO_CHAR(abs.end_date, 'DD-Mon-YYYY','NLS_DATE_LANGUAGE=AMERICAN') END ) Absence
     ,TO_CHAR(advr.dayy, 'DD-Mon-YYYY','NLS_DATE_LANGUAGE=AMERICAN') dayy
     ,advr.dayy din
     ,TO_CHAR(advr.dayy, 'Day','NLS_DATE_LANGUAGE=AMERICAN') Day_Name
     ,abs_typ_tl."NAME"                          absence_type_name
     ,PE.EMAIL_ADDRESS
  FROM per_person_names_f_v         pn
     , PER_PERSON_SECURED_LIST_V             papf
     , ANC_PER_ABS_ENTRIES          abs
     , anc_per_abs_type_entries     abs_typ_ent  
     , hr_all_organization_units_vl hou
     , PER_ASSIGNMENT_SECURED_LIST_V        paa
     , hr_all_organization_units_vl employer
     ,advr
     ,fusion.ANC_ABSENCE_TYPES_F                abs_typ_b
     ,fusion.ANC_ABSENCE_TYPES_F_TL             abs_typ_tl
     ,PER_EMAIL_ADDRESSES PE
     ,PER_PERIODS_OF_SERVICE PPOS
 WHERE abs.start_date
       BETWEEN NVL( :pStartDate, abs.start_date )
            AND NVL( :pEndDate, abs.start_date )  
   AND (    :pPersonNumber IS NULL
         OR UPPER(:pPersonNumber) in UPPER(papf.person_number)
        )
   AND abs.person_id = papf.person_id
   AND pn.person_id = abs.person_id
   AND abs.start_date
       BETWEEN pn.EFFECTIVE_START_DATE  AND pn.EFFECTIVE_END_DATE
   AND papf.person_id = abs.person_id
   AND abs.start_date
       BETWEEN papf.EFFECTIVE_START_DATE AND papf.EFFECTIVE_END_DATE
   AND abs_typ_ent.per_absence_entry_id
       = abs.per_absence_entry_id
 /******************** Joining with Email Table ******************/       
           AND PAPF.person_id = PE.person_id
    AND PE.EMAIL_TYPE = 'W1'
    /******************** Joining with HireDate Table ******************/
    AND papf.PERSON_ID = PPOS.PERSON_ID
   AND PPOS.PERIOD_OF_SERVICE_ID = PAA.PERIOD_OF_SERVICE_ID
   /******************** Joining with Assignments Table ******************/
  AND paa.person_id  = abs.person_id
  AND abs.absence_status_cd <> 'CANCELED'
  AND paa.assignment_id = abs_typ_ent.assignment_id
  AND abs.start_date BETWEEN paa.effective_start_date AND paa.effective_end_date
  AND paa.assignment_type= 'E'
  AND paa.primary_flag = 'Y'
  /********************  Joining with Departmetn Org Units ************/
  AND hou.organization_id(+) = paa.organization_id

  /********************  Joining Employer Org Units ******************* */
  AND employer.organization_id = paa.legal_entity_id
  AND abs.start_date BETWEEN employer.effective_start_date AND employer.effective_end_date
  AND advr.dayy between abs.start_date and abs.end_date
  AND abs.absence_type_id = abs_typ_b.absence_type_id
  AND abs_typ_b.absence_type_id                 = abs_typ_tl.absence_type_id
            AND abs_typ_tl.language =  USERENV('LANG')
            AND SYSDATE                           BETWEEN abs_typ_b.effective_start_date   AND abs_typ_b.effective_end_date
            AND SYSDATE                             BETWEEN abs_typ_tl.effective_start_date  AND abs_typ_tl.effective_end_date
 order by papf.person_number, advr.dayy

Thursday, October 31, 2024

Benefits Person dependent SQL Query in Oracle Cloud HCM

 

 

 Below is the query for the Benefits Person dependent Report in Oracle Cloud HCM.

SELECT
     papf.person_number                                "DependentEmpNo"
    ,ppnf.first_name                                "DependentEmpFirst_Name"
    ,ppnf.last_name                                    "DependentEmpLastName"
    ,bpg.name                                        "DEPT_EMP_PROGRAM_NAME"
    ,bptf.name                                        "DEPT_EMP_PLAN_TYPE_NAME"
    ,bpf.name                                        "DEPT_EMP_PLAN_NAME"
    ,bof.name                                        "DEPT_EMP_OPTION_NAME"
    ,bper.bnft_amt                                    "COVERAGE"
    ,papf2.person_number                            "DEPT_PERSON_NUMBER"
    ,ppnf2.first_name                                "DEPT_FIRST_NAME"
    ,ppnf2.last_name                                "DEPT_LAST_NAME"
    ,ppl.sex                                        "DEPT_GENDER"
    ,to_char(pp2.date_of_birth,'YYYY/MM/DD')        "DEPT_BIRTH_DATE"    
    ,pni.national_identifier_number                    "DEPT_SSN"    
    ,to_char(pdp.cvg_strt_dt,'YYYY/MM/DD')            "DEPT_COVERAGE_START_DATE"
    ,pdp.rlnshp_cd                                    "RELATIONSHIP_TYPE"


FROM
    per_all_people_f    papf
    ,per_all_people_f    papf2
    ,per_person_names_f ppnf
    ,per_person_names_f ppnf2
    ,per_persons        pp2
    ,per_people_legislative_f ppl
    ,per_national_identifiers pni
    ,ben_prtt_enrt_rslt bper
    ,ben_per_in_ler bpil
    ,ben_pgm_f bpg
    ,ben_pl_f bpf
    ,ben_pl_typ_f bptf
    ,ben_opt_f bof
    ,ben_elig_cvrd_dpnt    pdp


WHERE 1=1
    -- AND papf.person_number = '13705'
    AND papf.person_id = ppnf.person_id
    AND ppnf.name_type = 'GLOBAL'
    --AND :Effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date
    --AND :Effective_date BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date

    AND papf.person_id = bper.person_id
    AND bper.pgm_id = bpg.pgm_id
    AND bper.pl_id = bpf.pl_id
    AND bper.pl_typ_id = bptf.pl_typ_id
    AND bper.opt_id = bof.opt_id(+)
    AND bper.per_in_ler_id = bpil.per_in_ler_id
    AND bpil.per_in_ler_stat_cd in ('STRTD', 'PROCD')
    
    AND bper.prtt_enrt_rslt_id = pdp.prtt_enrt_rslt_id
    AND pdp.dpnt_person_id = papf2.person_id
    AND papf2.person_id = pp2.person_id
    AND papf2.person_id = ppnf2.person_id
    AND ppnf2.name_type = 'GLOBAL'
    AND papf2.person_id = ppl.person_id
    AND papf2.person_id = pni.person_id(+)
    -- AND pni.national_identifier_type = 'SSN'
    --AND :Effective_date BETWEEN papf2.effective_start_date AND papf2.effective_end_date
    --AND :Effective_date BETWEEN ppnf2.effective_start_date AND ppnf2.effective_end_date
    --AND :Effective_date BETWEEN ppl.effective_start_date AND ppl.effective_end_date
        
    --AND ((COALESCE(:Plan_Name,NULL) IS NULL) OR ((COALESCE(:Plan_Name,NULL) IS NOT NULL) AND bpf.pl_id in (:Plan_Name)))
    --AND ((COALESCE(:Plan_Type,NULL) IS NULL) OR ((COALESCE(:Plan_Type,NULL) IS NOT NULL) AND bptf.pl_typ_id in (:Plan_Type)))
    --AND ((COALESCE(:Program_Name,NULL) IS NULL) OR ((COALESCE(:Program_Name,NULL) IS NOT NULL) AND bpg.pgm_id in (:Program_Name)))
    /*AND :Effective_date BETWEEN bper.enrt_cvg_strt_dt AND nvl(bper.enrt_cvg_thru_dt,to_date('4712/12/31','YYYY/MM/DD'))
    AND :Effective_date BETWEEN pdp.CVG_STRT_DT AND nvl(pdp.CVG_THRU_DT,to_date('4712/12/31','YYYY/MM/DD'))
    AND :Effective_date BETWEEN bpg.effective_start_date AND bpg.effective_end_date
    AND :Effective_date BETWEEN bpf.effective_start_date AND bpf.effective_end_date
    AND :Effective_date BETWEEN bptf.effective_start_date AND bptf.effective_end_date
    AND :Effective_date BETWEEN bof.effective_start_date(+) AND bof.effective_end_date(+)*/

End-to-end configuration implementation task for Oracle Fusion cloud Absence Management.

Below are the details of the end-to-end configuration implementation task for Oracle Fusion cloud Absence Management.

 

Sno

Task Name

Functional/Technical

1

Absence Management Implementation Overview

Functional

2

Responsive User Experience Setup

Functional

3

Absence Lookups, Value Sets, and Flexfields

Functional

4

Formula Creation and Error Handling for Absence Management

Functional

5

Eligibility Profiles for Absence Plans

Functional

6

Rate Definitions for Absence Payments

Functional

7

Elements for Absence Management

Functional

8

Effective Dates in Absence Management

Functional

9

Accrual Absence Plans

Functional

10

Qualification Absence Plans

Functional

11

Compensatory Time

Functional

12

Donation

Functional

13

Absence Types, Reasons, and Categories

Functional

14

Absence Certifications

Functional

15

Absence Processes

Functional

16

Integrated Workbooks for Loading(HDL/HSDL) Absence Data

Technical

17

HCM BI Report for Absence Data

Technical

18

HCM OTBI for Absence Data

Technical

19

HCM Extracts for Absence Data

Technical

 

Requisition Pending Approval for multiple Approves SQL Report in Oracle Fusion ORC.

 WITH RAW_DATA AS ( SELECT DISTINCT IRV.REQUISITION_NUMBER, IRV.TITLE, WFS.ENDDATE, WFS.STARTDATE, WFS.ASSIGNEE FROM FA_FUSION_SOAINFRA.WFTA...