Search for:
Jul 10 2021
Configuration – Value set to get Grade Steps based on Worker Grade

Assignment DFF supports a number of parameters which can be used to get dynamic values based on Worker assignment.

List of supported parameters for a DFF can be found using below link:

Configuration – How to get list of supported parameters for a DFF?

In this example, we will create a value set to display list of Grade steps based on Worker’s assigned grade. Create a Table validated value set using below:

FROM ClausePER_GRADE_STEPS_F_TL pgftl, PER_GRADE_STEPS_F pgsf,PER_GRADES_F pgf
Value Attributes Table Alias 
*Value Column NameSUBSTR(pgftl.NAME,1,150)
Value Column TypeVARCHAR2
Value Column Length150
Description Column NameSUBSTR(pgftl.NAME,1,150)
Description Column Type VARCHAR2
Description Column Length 150
ID Column NameSUBSTR(pgftl.NAME,1,150)
ID Column TypeVARCHAR2
ID Column Length150
Enabled Flag Column Name 
Start Date Column Name 
End Date Column Name 
WHERE Clausepgftl.GRADE_STEP_ID=pgsf.GRADE_STEP_ID
     AND pgftl.language=’US’
AND trunc(sysdate) between trunc(pgftl.effective_start_date) and trunc(pgftl.effective_end_date)
AND pgsf.GRADE_ID=pgf.GRADE_ID
     AND trunc(sysdate) between trunc(pgsf.effective_start_date) and trunc(pgsf.effective_end_date)
     AND trunc(sysdate) between trunc(pgf.effective_start_date) and trunc(pgf.effective_end_date)
AND pgf.grade_id = :{PARAMETER.GRADE_ID}
ORDER BY ClauseSUBSTR(pgftl.NAME,1,150)
Mandeep Gupta 1 Comments
Jul 6 2021
BIP – Query to get Active Users for terminated employees

Once a worker is terminated in Oracle HCM Cloud, one would expect that associated User account to be inactive as well. For this to happen automatically, autoprovisioning rules should be setup which will remove the associated roles to the terminated worker’s user. In case, there is even a single role attached to the user, the user will show as active in Security Console.

From the backend, Suspended attribute in PER_USERS table is mapped to active checkbox in UI. If the user is active, the suspended flag will hold N value. For inactive users, the value of this attribute will be Y.

You can use below query to get the list of terminated workers for whom the user is still active:

SELECT papf.person_number,pu.username
  FROM per_all_people_f papf
      ,per_all_assignments_m paam
	  ,per_users pu
 WHERE papf.person_id = paam.person_id
   AND paam.effective_latest_change = 'Y'
   AND paam.effective_sequence = 1
   AND paam.assignment_status_type like 'INACTIVE%' 
   AND paam.assignment_type NOT LIKE '%T'
   AND 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 papf.person_id = pu.person_id
   AND pu.suspended = 'N'
Mandeep Gupta 0 Comments
Jul 6 2021
HDL – Loading Classroom Resources in Bulk

HCM Data Loader object ClassroomResource can be used to bulk upload classroom resources in Oracle Learning Cloud. Existing locations created as part of Global HR can also be designated as classroom resources.

Below is a sample file to upload classroom resources using HDL:

METADATA|ClassroomResource|ClassroomResourceNumber|Title|Description|Capacity|SetCode|LocationCode|SourceSystemOwner|SourceSystemId|OwnedByPersonNumber
MERGE|ClassroomResource|CLASS1001|Room 1|Room 1|10|COMMON|JPLoc004 Kyushu|HRC_SQLLOADER|1001|101
MERGE|ClassroomResource|CLASS1002|Room 2|Room 2|15|COMMON|JPLoc004 Kyushu|HRC_SQLLOADER|1002|101
MERGE|ClassroomResource|CLASS1003|Room 3|Room 3|20|COMMON|JPLoc004 Kyushu|HRC_SQLLOADER|1003|101

Once the data is loaded successfully, you can verify the results from UI:

Navigation :- My Client Groups -> Learning -> Catalog Resources -> Classrooms

Mandeep Gupta 0 Comments