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

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(+)*/

Task Expired for Checklist(Journeys) using SQL Query in Oracle Cloud HCM

The following SQL query will help with the task 'Expired for Checklist(Journeys)'  with Checklist completed for employees in Oracle Cloud HCM.

 SELECT DISTINCT
    PAPF.person_number,
    ACT.checklist_name,
    ATT.task_name,
    CASE
    WHEN AC.checklist_status = 'COM' THEN 'Completed'
    WHEN AC.checklist_status = 'INI' THEN 'Pending'
    ELSE ' '
    END AS CHECKLIST_STATUS,
    To_char(AC.allocation_date, 'mm/dd/yyyy') as "ALLOCATION_DATE_",
    To_char(AC.completion_date, 'mm/dd/yyyy') as "COMPLETION_DATE",
    AC.checklist_category,
    AC.checklist_id,
    AC.allocated_checklist_id,
    AC.initiator_person_id,
    AT.STATUS,
    AV1.DISPLAY_NAME,
    PE.EMAIL_ADDRESS
FROM
    per_allocated_checklists AC,
    per_allocated_checklists_tl ACT,
    per_allocated_tasks AT,
    per_allocated_tasks_tl ATT,
    per_assignment_secured_list_v AV,
    PER_PERSON_NAMES_F AV1,
    per_all_people_f PAPF,
    PER_EMAIL_ADDRESSES PE,
    PER_ALL_ASSIGNMENTS_M PAAM
WHERE 1 = 1
    AND AT.STATUS <> 'COM'
    AND AT.STATUS <> 'DEP'
    AND AT.STATUS <> 'INI'
    AND AT.STATUS <> 'INP'
    AND PAAM.ASSIGNMENT_STATUS_TYPE ='ACTIVE'
    AND AC.allocated_checklist_id = ACT.allocated_checklist_id
    AND AT.allocated_checklist_id = AC.allocated_checklist_id
    AND ATT.allocated_task_id = AT.allocated_task_id
    AND AC.person_id = AV.person_id
    AND AV.person_id = AV1.person_id
    AND Trunc(sysdate) BETWEEN AV.effective_start_date AND AV.effective_end_date
    AND Trunc(sysdate) BETWEEN PAPF.effective_start_date AND PAPF.effective_end_date
    AND AV.assignment_status_type IN ( 'ACTIVE', 'SUSPENDED' )
    AND AV.effective_latest_change = 'Y'
    AND ACT.checklist_name = 'ChecklistName'
    AND PAPF.person_id = AV.person_id
    AND PAPF.person_id = PE.person_id
    AND PE.EMAIL_TYPE = 'W1'
ORDER BY PAPF.person_number

Goal and Goal Plan Details SQL query in Oracle Fusion Cloud HCM

Select 

distinct hg.GOAL_NAME

,hg.DESCRIPTION as Goal_DESCRIPTION

,hg.PUBLISH_DATE

,hg.GOAL_TYPE_CODE

,hgpl.GOAL_PLAN_NAME 

,hgpl.DESCRIPTION as GoalPlan_DESCRIPTION

,to_char(hg.START_DATE,'yyyy/mm/dd') as Goal_START_DATE

,to_char(hg.TARGET_COMPLETION_DATE,'yyyy/mm/dd') TARGET_COMPLETION_DATE

,hg.SUCCESS_CRITERIA

,hg.PERCENT_COMPLETE_CODE

,hg.STATUS_CODE

,hg.APPROVAL_STATUS_CODE

,to_char(hg.ACTUAL_COMPLETION_DATE,'yyyy/mm/dd') ACTUAL_COMPLETION_DATE

,hg.GOAL_VERSION_TYPE_CODE --,to_char(hg.VERSION_DATE,'yyyy/mm/dd') VERSION_DATE

,hg.PRIORITY_CODE GOALPRIORITY_CODE

,hg.COMMENTS

,hg.CREATED_BY GOAL_CREATED_BY

,hg.GOAL_URL

,hg.LEVEL_CODE

,hg.LEVEL_MEANING

,hg.CATEGORY_CODE

,hg.MEASURE_TYPE_CODE

,hg.MEASURE_NAME

,hg.TARGET_VALUE

,hg.TARGET_TYPE

,hg.UOM_CODE

,hg.ACTUAL_VALUE

,hg.GOAL_ACCESS_LEVEL_CODE

,hgpg.WEIGHTING

,hgpg.PRIORITY_CODE PLANPRIORITY_CODE

 from

 HRG_GOALS hg,

 HRG_GOAL_PLAN_GOALS hgpg,

 HRG_GOAL_PLANS_TL hgpl

 where hgpg.GOAL_ID=HG.GOAL_ID

 and hg.GOAL_VERSION_TYPE_CODE='ACTIVE'

 and hgpg.GOAL_PLAN_ID=hgpl.GOAL_PLAN_ID

 order by hg.GOAL_NAME