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(+)
Oracle Fusion Implementation Information
This will showcase to all Oracle Fusion Cloud Information details with functional and Technical.
Saturday, February 15, 2025
Requisition Pending Approval for multiple Approves SQL Report in Oracle Fusion ORC.
Employee Goals approved and total goals submitted status Report in Oracle Fusion HCM.
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...
-
Below is the SQL query with Brusting for the Welcome Onboard to Company in Oracle Cloud HCM. Step1-->SQL Query SELECT PERSON_NUMBER KEY...
-
Below is the query for the Organization(Department) Tree hierarchy in Oracle Cloud HCM. ---------------- WITH org_tree AS ( SELECT...
-
The following SQL query will help employees taken the Absence Leaves with Day Wise in Oracle Cloud HCM. with advr as (select trunc((Sel...