Search for:
Oct 6 2022
BIP – Query to extract assignment rules at course level in Oracle Learning Cloud

Use the below SQL to extract assignment rules defined at course level:

SELECT wlift.NAME

   ,war.DEFAULT_INITIAL_REC_SUBSTA

   ,war.DEFAULT_INITIAL_ACT_SCORE

   ,war.DEFAULT_INITIAL_COMMENTS

   ,war.DEFAULT_INITIAL_COMPL_DATE

   ,war.DEFAULT_INITIAL_TOTACT_EFF

   ,war.DEFAULT_INITIAL_REASON_CODE

   ,war.INITIAL_DUE_DATE_OPTION

   ,war.INITIAL_DUE_DATE

   ,war.INITIAL_DUE_IN_DAYS

   ,war.VALIDITY_OPTION

   ,war.EXPIRY_OPTION

   ,war.EXPIRY_IN_DAYS

   ,war.EXPIRY_DATE

   ,war.EXPIRY_IN_NUM_YRS

   ,war.RENEWAL_OPTIONS

   ,war.RENEWAL_BEFORE_EXPIRY_DAYS

 FROM WLF_ASSIGNMENT_RULES war

   ,WLF_LEARNING_ITEMS_F_TL wlift

   ,WLF_LEARNING_ITEMS_F wlif

 WHERE war.ASSIGNMENT_RULE_ID = wlif.ASSIGNMENT_RULE_ID

  AND wlif.LEARNING_ITEM_ID = wlift.LEARNING_ITEM_ID

  AND wlift.language = 'US'

  AND TRUNC(sysdate) BETWEEN wlif.effective_start_date AND wlif.effective_end_date

  AND TRUNC(sysdate) BETWEEN wlift.effective_start_date AND wlift.effective_end_date
Mandeep Gupta 0 Comments
May 10 2021
HDL – Loading CoverArt File at Course Level

While creating the Courses in Oracle Learning Cloud, there is a capability to load CoverArt for each course. If no cover art is loaded, the learner is presented with a blue strip film.

HCM Data Loaders provides an option to bulk upload the CoverArt file for multiple courses at one go. All the cover art related files (jpg, jepg) should be included in BlobFiles folder and the name of the jpg/jpeg file should be provided in the dat file.

Below is the sample Course dat file:

METADATA|Course|CourseId|EffectiveStartDate|EffectiveEndDate|CourseNumber|Title|ShortDescription|Syllabus|PublishStartDate|PublishEndDate|MinimumExpectedEffort|MaximumExpectedEffort|CurrencyCode|MinimumPrice|MaximumPrice|CoverArtFile|CoverArtFileName|TrailerLiId|TrailerLiNumber|OwnedByPersonId|OwnedByPersonNumber|SourceType|SourceId|SourceInfo|SourceSystemOwner|SourceSystemId
MERGE|Course||2016/01/01||COURSEN_1866|Test Staff Orientation|||2016/01/01||||USD|||Capture.jpg|Capture.jpg||||1010|TALEO|1111|TALEO_1111|HRC_SQLLOADER|HRC_SQLLOADER_1111

Once the BlobFiles Folder and dat file are ready, zip them together and trigger the HDL Import and Load process:

Once the Import and Load process is successful, login to Learning Admin to verify:

My Client Groups -> Learning -> Learning Catalog -> Courses

Mandeep Gupta 0 Comments
Mar 7 2021
Reports (BIP) – Query to get Assignee details for a course
SELECT warf.ASSIGNMENT_RECORD_ID,
warf.EVENT_TYPE,
we.EVENT_TYPE we_event_type,
warf.EVENT_SUB_TYPE,
we.EVENT_SUB_TYPE we_EVENT_SUB_TYPE,
(SELECT person_number
FROM per_all_people_f
WHERE person_id = warf.EVENT_CREATED_BY_ID
AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date) created_By,
we.CREATED_BY_ID "Assigned_By_Person_Id",
(SELECT person_number
FROM per_all_people_f
WHERE person_id = we.CREATED_BY_ID
AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date) "Assigned_By",
warf.ATTRIBUTION_TYPE,
we.ATTRIBUTION_TYPE we_ATTRIBUTION_TYPE,
(SELECT person_number
FROM per_all_people_f
WHERE person_id = warf.ATTRIBUTION_ID
AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date) attribution_id,
warf.ATTRIBUTION_ID warf_ATTRIBUTION_ID,
we.ATTRIBUTION_ID we_ATTRIBUTION_ID,
warf.learner_id,
warf.ATTRIBUTION_LOOKUP_CODE,
warf.EVENT_ASSIGNMENT_ID
from WLF_ASSIGNMENT_RECORDS_F warf,
WLF_EVENT_ASSIGNMENTS_F weaf,
WLF_EVENTS we
where warf.EVENT_ASSIGNMENT_ID = weaf.EVENT_ASSIGNMENT_ID
and trunc(sysdate) between warf.effective_start_date and warf.effective_end_date
and trunc(sysdate) between weaf.effective_start_date and weaf.effective_end_date
and we.event_id = weaf.event_id
Mandeep Gupta 0 Comments
Feb 5 2021
Value Set – Table Value set to get list of courses
*FROM Clausewlf_learning_items_f wlif, wlf_learning_items_f_tl wlift
Value Attributes Table Alias 
*Value Column NameSUBSTR(wlift.name,1,150)
Value Column TypeVARCHAR2
Value Column Length150
Description Column Name 
Description Column Type 
Description Column Length 
ID Column NameTO_CHAR(wlif.LEARNING_ITEM_ID)
ID Column TypeVARCHAR2
ID Column Length40
Enabled Flag Column Name 
Start Date Column Name 
End Date Column Name 
WHERE Clausewlif.LEARNING_ITEM_ID = wlift.LEARNING_ITEM_ID AND wlift.language = ‘US’ AND wlif.LEARNING_ITEM_TYPE = ‘ORA_COURSE’ AND TRUNC(SYSDATE) BETWEEN wlif.effective_start_date and wlif.effective_end_date AND wlif.effective_start_date BETWEEN wlift.effective_start_date and wlift.effective_end_date
ORDER BY Clause 
Mandeep Gupta 0 Comments