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
Apr 6 2021
Reports (BIP) – Query to get learning outcomes

Oracle Learning Cloud supports to define learning outcomes against each course as shown in below fig:

For reporting and integration purposes, there is a need to extract the learning outcomes assigned against each course. Learning outcomes are stored as profile relationship against each course. Below query can be used to extract the asked data:

select wlifv.learning_item_number,
wlifv.name learning_item_name,
wlifv.learning_item_type,
wlifv.status,
wlifv.effective_start_date,
wlifv.effective_end_date,
hpi.content_type_id,
hpi.date_from,
hpi.date_to,
hpi.content_item_id competency_id,
hctt.content_type_name,
hcit.name content_item_name
from HRT_RELATION_CONFIG_B hrcb
,HRT_PROFILE_RELATIONS hpr
,HRT_CONTENT_TYPES_B hctb
,HRT_CONTENT_TYPES_TL hctt
,HRT_CONTENT_ITEMS_TL hcit
,HRT_PROFILE_ITEMS hpi
,HRT_PROFILES_B hpb
,WLF_LEARNING_ITEMS_F_VL wlifv
where hrcb.key_table_name = 'WLF_LEARNING_ITEMS_F_VL'
and hrcb.relation_code = 'LEARNING_ITEM'
and hrcb.relation_id = hpr.relation_id
and hpi.profile_id = hpr.profile_id
and hpi.content_type_id = hctb.content_type_id
and hctt.content_type_id = hctb.content_type_id
and hctb.context_name = 'COMPETENCY'
and hpi.profile_id = hpb.profile_id
and hpr.object_id = wlifv.learning_item_id
and hpb.profile_usage_code = 'L'
and hpi.content_item_id = hcit.content_item_id
and TRUNC(SYSDATE) BETWEEN wlifv.effective_start_date and wlifv.effective_end_date
and hctt.language = 'US'
and hcit.language = 'US'
ORDER BY 1
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
Mar 7 2021
Reports (BIP) – Query to extract default Access Group data for a Course/Offering

In Oracle Learning cloud, Learning Admin can define the Default access at Course or offering level. Course.dat can be used to load this information.

In order to extract this information from DB in HDL format, please use below query:

select 'MERGE' "METADATA"       
      ,'CourseDefaultAccess' "CourseDefaultAccess"                  ,to_char(wapf.effective_start_date,'RRRR/MM/DD') "EffectiveStartDate"           ,to_char(wapf.effective_end_date,'RRRR/MM/DD') "EffectiveEndDate"       ,wapf.ACCESS_PERMISSION_NUMBER "DefaultAccessNumber"       
,wlif.learning_item_number "CourseNumber"       
,wapf.FOLLOW_SPEC "FollowSpecialization"       
,wapf.ASSIGN_AS_MANAGER                              "MgrInitialStatus"       ,wapf.ASSIGN_FOR_SELF                                "SelfInitialStatus"        ,wlif.SS_VIEW_MODE                                 "SelfViewMode"    
 from WLF_ACCESS_PERMISSIONS_F wapf      
     ,WLF_LEARNING_ITEMS_F wlif 
where 1=1   
  and wapf.access_permission_id= wlif.access_permission_id   
--and trunc(sysdate) between wapf.effective_start_date and wapf.effective_end_date   
  and trunc(sysdate) between wlif.effective_start_date and wlif.effective_end_date   
  and wlif.learning_item_number = 'OLC251051'
Mandeep Gupta 0 Comments