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

 

Tuesday, October 1, 2024

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

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...