Learning admin can attachments on offering level for different roles – Admin/ Employee etc.
In case, you need to extract the attachment details for an offering, below BIP can be used:
SELECT wlf_learning_items_f.learning_item_id
,wlf_learning_items_f.learning_item_number
,wlf_li_relations_f.relation_id
,fad.pk1_value
,fad.attached_document_id
,fdt.document_id
,fdt.title
,fdt.file_name
,fad.creation_date
FROM fnd_attached_documents fad
,fnd_documents_tl fdt
,wlf_learning_items_f wlf_learning_items_f
,wlf_li_relations_f wlf_li_relations_f
WHERE fad.document_id = fdt.document_id
AND fdt.language = 'US'
AND fad.entity_name = 'WLF_LI_RELATIONS_F'
AND fad.pk1_value = wlf_li_relations_f.relation_id
AND wlf_learning_items_f.learning_item_id = wlf_li_relations_f.source_id
AND TRUNC(SYSDATE) BETWEEN wlf_learning_items_f.effective_start_date AND wlf_learning_items_f.effective_end_date
AND TRUNC(SYSDATE) BETWEEN wlf_li_relations_f.effective_start_date AND wlf_li_relations_f.effective_end_date
ORDER BY fad.creation_date DESC
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
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:
In Oracle Learning Cloud, a specialization can be created as a combination of multiple sections with each section comprising of multiple courses.
A learning admin can setup the specialization by navigating to My Client Groups -> Learning -> Learning Catalog -> Specializations:
Example:
Use the below query to get the above details:
WITH specialization as
(SELECT
LearningItemDEO.LEARNING_ITEM_ID,
LearningItemDEO.EFFECTIVE_START_DATE SPESD,
LearningItemDEO.EFFECTIVE_END_DATE SPEED,
LearningItemDEO.LEARNING_ITEM_TYPE SPLIT,
LearningItemDEO.LEARNING_ITEM_SUB_TYPE SPLIST,
LearningItemDEO.LEARNING_ITEM_NUMBER,
LearningItemDEO.STATUS SP_STATUS,
LearningItemDEO.START_DATE SPSD,
LearningItemDEO.END_DATE SPED,
to_date(TO_CHAR( LearningItemDEO.CREATION_DATE,'MM/DD/YYYY'),'MM/DD/YYYY') AS SPCreationDate,
LearningItemTranslationDEO.NAME,
LearningItemDEO.LI_START_DATE as SP_LiStartDate,
LearningItemDEO.LI_END_DATE as SP_LiEndDate
FROM WLF_LEARNING_ITEMS_F LearningItemDEO,
WLF_LEARNING_ITEMS_F_TL LearningItemTranslationDEO
WHERE LearningItemDEO.LEARNING_ITEM_TYPE IN ('ORA_SPECIALIZATION')
AND LearningItemDEO.LEARNING_ITEM_ID = LearningItemTranslationDEO.LEARNING_ITEM_ID
AND TRUNC(SYSDATE) BETWEEN LearningItemTranslationDEO.EFFECTIVE_START_DATE AND LearningItemTranslationDEO.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN LearningItemDEO.EFFECTIVE_START_DATE AND LearningItemDEO.EFFECTIVE_END_DATE
AND LearningItemTranslationDEO.LANGUAGE = USERENV('lang')
--AND LearningItemTranslationDEO.NAME LIKE ('Working From Home Guidelines and Tips')
)
, Section as
(SELECT
LearningItemDEO.LEARNING_ITEM_ID,
LearningItemDEO.EFFECTIVE_START_DATE,
LearningItemDEO.EFFECTIVE_END_DATE,
LearningItemDEO.LEARNING_ITEM_TYPE,
LearningItemDEO.LEARNING_ITEM_SUB_TYPE,
LearningItemDEO.LEARNING_ITEM_NUMBER,
LearningItemDEO.STATUS,
LearningItemDEO.START_DATE,
LearningItemDEO.END_DATE,
to_date(TO_CHAR( LearningItemDEO.CREATION_DATE,'MM/DD/YYYY'),'MM/DD/YYYY') AS LearningItemDEOCreationDate,
LearningItemTranslationDEO.NAME,
LearningItemTranslationDEO.DESCRIPTION,
LearningItemDEO.LI_START_DATE as LearningItemDEOLiStartDate,
LearningItemDEO.LI_END_DATE as LearningItemDEOLiEndDate
FROM WLF_LEARNING_ITEMS_F LearningItemDEO,
WLF_LEARNING_ITEMS_F_TL LearningItemTranslationDEO
WHERE LearningItemDEO.LEARNING_ITEM_TYPE IN ('ORA_SPECL_SECTION')
AND LearningItemDEO.LEARNING_ITEM_ID = LearningItemTranslationDEO.LEARNING_ITEM_ID
AND TRUNC(SYSDATE) BETWEEN LearningItemTranslationDEO.EFFECTIVE_START_DATE AND LearningItemTranslationDEO.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN LearningItemDEO.EFFECTIVE_START_DATE AND LearningItemDEO.EFFECTIVE_END_DATE
AND LearningItemTranslationDEO.LANGUAGE = USERENV('lang')
)
, course as
(
SELECT
LearningItemDEO.LEARNING_ITEM_ID,
LearningItemDEO.EFFECTIVE_START_DATE,
LearningItemDEO.EFFECTIVE_END_DATE,
LearningItemDEO.LEARNING_ITEM_TYPE,
LearningItemDEO.LEARNING_ITEM_SUB_TYPE,
LearningItemDEO.LEARNING_ITEM_NUMBER,
LearningItemDEO.STATUS,
to_date(TO_CHAR( LearningItemDEO.CREATION_DATE,'MM/DD/YYYY'),'MM/DD/YYYY') AS LearningItemDEOCreationDate,
LearningItemTranslationDEO.NAME,
LearningItemTranslationDEO.DESCRIPTION,
LearningItemDEO.LI_START_DATE as LearningItemDEOLiStartDate,
LearningItemDEO.LI_END_DATE as LearningItemDEOLiEndDate
FROM WLF_LEARNING_ITEMS_F LearningItemDEO,
WLF_LEARNING_ITEMS_F_TL LearningItemTranslationDEO
WHERE LearningItemDEO.LEARNING_ITEM_TYPE IN ('ORA_COURSE')
AND LearningItemDEO.LEARNING_ITEM_ID = LearningItemTranslationDEO.LEARNING_ITEM_ID
AND TRUNC(SYSDATE) BETWEEN LearningItemTranslationDEO.EFFECTIVE_START_DATE AND LearningItemTranslationDEO.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN LearningItemDEO.EFFECTIVE_START_DATE AND LearningItemDEO.EFFECTIVE_END_DATE
AND LearningItemTranslationDEO.LANGUAGE = USERENV('lang')
)
, offering as
(
select WLFI.LEARNING_ITEM_NUMBER
,TO_CHAR(WLFI.EFFECTIVE_END_DATE,'YYYY/MM/DD')
,TO_CHAR(WLFI.EFFECTIVE_START_DATE,'YYYY/MM/DD')
,WLFT.DESCRIPTION
,WLFT.DESCRIPTION_LONG
,WLCF.ENABLE_CAPACITY
,WLCF.ENABLE_WAITLIST
,WLFI.LANGUAGE_CODE
,WLFI.STATUS
,WLCLF.MAXIMUM_ATTENDEES
,WLCLF.MINIMUM_ATTENDEES
,TO_CHAR(WLCLF.ENROLMENT_END_DATE,'YYYY/MM/DD')
,TO_CHAR(WLCLF.ENROLMENT_START_DATE,'YYYY/MM/DD')
,WLCLF.DELIVERY_MODE
,TO_CHAR(WLFI.END_DATE,'YYYY/MM/DD')
,TO_CHAR(WLFI.START_DATE,'YYYY/MM/DD')
,WLFT.NAME
,WLCF.LEARNING_ITEM_ID WLCF_LEARNING_ITEM_ID
FROM WLF_LEARNING_ITEMS_F WLFI,
WLF_LI_COURSES_F WLCF, --LEARNING_ITEM_ID course
WLF_LI_CLASSES_F WLCLF,
WLF_LEARNING_ITEMS_F_TL WLFT
WHERE WLFI.LEARNING_ITEM_ID = WLFT.LEARNING_ITEM_ID
AND WLFI.LEARNING_ITEM_ID = WLCLF.LEARNING_ITEM_ID
AND WLCLF.COURSE_LEARNING_ITEM_ID = WLCF.LEARNING_ITEM_ID
AND WLFT.LANGUAGE = USERENV('lang')
AND TRUNC(SYSDATE) BETWEEN WLFI.EFFECTIVE_START_DATE AND WLFI.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN WLCF.EFFECTIVE_START_DATE AND WLCF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN WLCLF.EFFECTIVE_START_DATE AND WLCLF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN WLFT.EFFECTIVE_START_DATE AND WLFT.EFFECTIVE_END_DATE
AND WLFI.LEARNING_ITEM_TYPE = 'ORA_CLASS'
)
SELECT specialization.name "Specialization Name"
,specialization.LEARNING_ITEM_NUMBER "Specialization Number"
,Section.name "Section Name"
,Section.LEARNING_ITEM_NUMBER "Section Number"
,WLF_LI_HIERARCHIES_F_SS.position "Section Position"
,course.name "Course Name"
,course.learning_item_number "Course Number"
,WLF_LI_HIERARCHIES_F_SC.HIERARCHY_NUMBER "Activity Number"
,WLF_LI_HIERARCHIES_F_SC.position "Course Position"
,offering.name "Offering Name"
,offering.LEARNING_ITEM_NUMBER "Offering Number"
,offering.DELIVERY_MODE "Offering Delivery Mode"
,offering.STATUS "Offering Status"
FROM WLF_LI_HIERARCHIES_F WLF_LI_HIERARCHIES_F_SS,
WLF_LI_HIERARCHIES_F WLF_LI_HIERARCHIES_F_SC,
specialization,
section,
course,
offering
WHERE specialization.name = 'Essential Worker'
AND WLF_LI_HIERARCHIES_F_SS.LEARNING_ITEM_ID = specialization.LEARNING_ITEM_ID
AND WLF_LI_HIERARCHIES_F_SS.CHILD_LEARNING_ITEM_ID = section.LEARNING_ITEM_ID
AND TRUNC(SYSDATE) BETWEEN WLF_LI_HIERARCHIES_F_SS.EFFECTIVE_START_DATE AND WLF_LI_HIERARCHIES_F_SS.EFFECTIVE_END_DATE
AND WLF_LI_HIERARCHIES_F_SC.LEARNING_ITEM_ID = section.LEARNING_ITEM_ID
AND WLF_LI_HIERARCHIES_F_SC.CHILD_LEARNING_ITEM_ID = course.LEARNING_ITEM_ID
AND TRUNC(SYSDATE) BETWEEN WLF_LI_HIERARCHIES_F_SC.EFFECTIVE_START_DATE AND WLF_LI_HIERARCHIES_F_SC.EFFECTIVE_END_DATE
AND course.LEARNING_ITEM_ID = offering.WLCF_LEARNING_ITEM_ID
ORDER BY specialization.name,WLF_LI_HIERARCHIES_F_SS.position,WLF_LI_HIERARCHIES_F_SC.position
Default Assignment Rules like Validity Period, Expiration, Renewal Options, Renewal Period etc are maintained at the course level in Oracle Learning Cloud.
These details are stored in the backend table ‘WLF_ASSIGNMENT_RULES’.
The query from below post can be joined with WLF_ASSIGNMENT_RULES using ASSIGNMENT_RULE_ID column:
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.
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
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'
Also, learners can be assigned using Access Groups.
To extract this information below SQL can be used:
SELECT wlif.learning_item_id
,wlif.learning_item_number course_Number
,wlif_tl.name course_Name
,wlif_tl.description_short description
,wlif_ag.learning_item_number access_group_number
,wlif_tl_ag.name access_group_name
FROM wlf_learning_items_f wlif
,wlf_learning_items_f_tl wlif_tl
,wlf_learning_items_f wlif_ag
,wlf_learning_items_f_tl wlif_tl_ag
,wlf_li_ag_relations_f wlarf
WHERE 1=1
AND wlif_tl.learning_item_id = wlif.learning_item_id
AND wlif_tl.name = 'Test 1234'
AND wlif_ag.learning_item_type = 'ORA_ACCESS_GROUP'
AND wlif_tl_ag.learning_item_id = wlif_ag.learning_item_id
AND wlarf.access_learning_item_id = wlif_ag.learning_item_id
AND wlarf.catalog_learning_item_id = wlif.learning_item_id
AND TRUNC(SYSDATE) BETWEEN wlif_tl_ag.effective_start_date AND wlif_tl_ag.effective_end_date
AND TRUNC(SYSDATE) BETWEEN wlif_ag.effective_start_date AND wlif_ag.effective_end_date
AND TRUNC(SYSDATE) BETWEEN wlif_tl.effective_start_date AND wlif_tl.effective_end_date
AND TRUNC(SYSDATE) BETWEEN wlif.effective_start_date AND wlif.effective_end_date
wlif.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