select papf.person_number,
ppnf.full_name
from per_All_people_f papf
,per_person_names_f ppnf
where papf.person_id =ppnf.person_id
and ppnf.name_type= 'GLOBAL'
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 papf.person_id = HRC_SESSION_UTIL.GET_USER_PERSONIDSELECT 'METADATA'
,'ContentItem'
,hctb.content_type_id
,hctb.context_name
,hcibt.name
,hctvt.value_set_name
,hg.geography_name
,hcibt.item_description
,to_char(hcib.date_from,'yyyy/mm/dd')
,hg.geography_code
,hcib.content_item_code
,to_char(hcib.date_to,'yyyy/mm/dd')
,hrmb.rating_model_code
,hrmb.rating_model_id
,hikm.source_system_id
,hikm.source_system_owner
FROM HRT_CONTENT_ITEMS_B hcib
,HRT_CONTENT_ITEMS_TL hcibt
,HRT_CONTENT_TYPES_B hctb
,HZ_GEOGRAPHIES hg
,HRT_RATING_MODELS_B hrmb
,HRT_CONTENT_TP_VALUESETS_TL hctvt
,HRC_INTEGRATION_KEY_MAP hikm
where hcib.CONTENT_ITEM_ID=hcibt.CONTENT_ITEM_ID
AND hcibt.LANGUAGE=userenv('LANG')
AND hcib.CONTENT_TYPE_ID=hctb.CONTENT_TYPE_ID(+)
AND hikm.surrogate_id = hcib.CONTENT_ITEM_ID
AND hcib.COUNTRY_ID=hg.GEOGRAPHY_ID(+)
AND hg.GEOGRAPHY_TYPE(+)='COUNTRY'
AND trunc(hcib.DATE_FROM) between hg.START_DATE(+) and nvl(hg.END_DATE(+),to_date('4712/12/31','YYYY/MM/DD'))
AND hcib.RATING_MODEL_ID=hrmb.RATING_MODEL_ID(+)
AND trunc(hcib.DATE_FROM) between hrmb.DATE_FROM(+) and nvl(hrmb.DATE_TO(+),to_date('4712/12/31','YYYY/MM/DD'))
AND hcib.CONTENT_VALUE_SET_ID=hctvt.CONTENT_VALUE_SET_ID(+)
AND hctvt.LANGUAGE(+)=userenv('LANG')
AND hctb.CONTEXT_NAME = 'LANGUAGE'SELECT a.datarow
FROM (
SELECT 'METADATA|AssignmentSupervisor|AssignmentSupervisorId|AssignmentNumber|ManagerAssignmentNumber|ManagerPersonNumber|ManagerType|AssignmentId|EffectiveEndDate|EffectiveStartDate|ActionCode|ManagerId|PersonId|PrimaryFlag|ReasonCode|ManagerAssignmentId|SourceSystemId|SourceSystemOwner' DATAROW, 1 SEQUENCE
FROM DUAL
UNION ALL
SELECT DISTINCT 'MERGE'
|| CHR (124)
||'AssignmentSupervisor'
|| CHR (124)
||NULL
|| CHR (124)
||paaf.assignment_number
|| CHR (124)
||paaf_m.assignment_number
|| CHR (124)
||papf.person_number
|| CHR (124)
||pasup.manager_type
|| CHR (124)
||NULL
|| CHR (124)
||TO_CHAR (pasup.effective_end_date,'DD/MON/YYYY')
|| CHR (124)
||TO_CHAR (pasup.effective_start_date,'DD/MON/YYYY')
|| CHR (124)
||pab.action_code
|| CHR (124)
||NULL
|| CHR (124)
||NULL
|| CHR (124)
||pasup.primary_flag
|| CHR (124)
||parb.action_reason_code
|| CHR (124)
||NULL
|| CHR (124)
||hikm.source_system_id
|| CHR (124)
||hikm.source_system_owner DATAROW, 2 SEQUENCE
FROM per_all_people_f papf,
per_assignment_supervisors_f pasup,
per_all_assignments_m paaf,
per_all_assignments_m paaf_m,
hrc_integration_key_map hikm,
per_action_occurrences pao,
per_action_reasons_b parb,
per_actions_b pab
WHERE pasup.assignment_id = paaf.assignment_id
AND pasup.manager_assignment_id = paaf_m.assignment_id
AND papf.person_id = paaf_m.person_id
AND paaf.assignment_type = 'E'
--AND paaf.assignment_status_type = 'ACTIVE'
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND pasup.effective_start_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND pasup.effective_start_date BETWEEN paaf_m.effective_start_date AND paaf_m.effective_end_date
AND pasup.assignment_supervisor_id = hikm.surrogate_id
AND OBJECT_NAME = 'AssignmentSupervisor'
AND pao.action_occurrence_id = pasup.action_occurrence_id
AND pao.action_id = pab.action_id
AND pao.action_reason_id = parb.action_reason_id
) a
ORDER BY SEQUENCESELECT DISTINCT papf.person_number "Emp Person Number"
,papf_cont.person_number "Contact Person Number"
,hl.meaning "Relationship"
,TO_CHAR (papf_cont.effective_start_date,'RRRR/MM/DD') "Contact Eff Start D"
,TO_CHAR (papf_cont.effective_end_date,'RRRR/MM/DD') "Contact Eff End D"
,TO_CHAR (papf_cont.start_date, 'RRRR/MM/DD') "Contact Start Date"
,TO_CHAR (pcrf.effective_start_date,'RRRR/MM/DD') "Rel Start Date"
,TO_CHAR (pcrf.effective_end_date,'RRRR/MM/DD') "Rel End Date"
,TO_CHAR (pp.date_of_birth, 'RRRR/MM/DD') "Contact Person DOB"
,ppnf.first_name "Contact First Name"
,ppnf.last_name "Contact Last Name"
FROM per_contact_relships_f pcrf
,per_all_people_f papf_cont
,per_all_people_f papf
,per_persons pp
,per_person_names_f ppnf
,hcm_lookups hl
WHERE papf_cont.person_id = pcrf.contact_person_id
AND papf.person_id = pcrf.person_id
AND pp.person_id = pcrf.contact_person_id
AND ppnf.person_id = pcrf.contact_person_id
AND ppnf.name_type = 'GLOBAL'
AND hl.lookup_code = pcrf.contact_type
AND hl.lookup_type = 'CONTACT'
ORDER BY papf.person_number, hl.meaningThere is one common requirement where Employees should access reports directly from self-service instead of navigating to analytics. In such cases, a report link can be created and added as a static link on Navigator. This will enable the employee to access report directly. The report can be then viewed in different views using different parameters in report link.
Steps to enable report link on self-service:
Prepare the report link in below format:
https://xxxx-fa-ext.oracledemos.com/xmlpserver/Custom/HCM/INVOKE_BIP_RPT.xdo?_xpt=0&_xmode=4&_xf=pdf&_xautorun=true
Refer the below link for more details on link parameters
Xmode parameter is used for different views. Below are different views depending upon xmode value:
xmode=0
xmode=1
xmode=2
xmode=4
Passing Parameter in report link:
https://ucf6-zuyv-fa-ext.oracledemos.com/xmlpserver/Custom/HCM/INVOKE_BIP_RPT.xdo?_xpt=0&_xmode=4&p_emp_num=10&_xt=Simple&_xf=html
Click on Tools under sandbox and click on Create Page Entry:
Create a page entry with below details and click on Save and Close:
You can now see Report Link under Me:
Click on report link and it will open the report in a new tab:
If everything looks ok, you can publish the Sandbox.
Each person can publish public info in HCM Cloud by navigating to Me -> Quick Actions -> Public Info. There are various sections under Public Info like Public Message, Contact Info, About Me etc.
You can use the below SQL queries to extract the data for relevant sections.
Public Message:
SQL Query:
SELECT papf.person_number
,ppnf.full_name
,ppmv.PORTRAIT_MESSAGE_CONTENT
,ppmv.TIME_FROM
,ppmv.TIME_TO
FROM per_all_people_f papf
,per_person_names_f ppnf
,per_portrait_messages_vl ppmv
WHERE papf.person_id = ppnf.person_id
AND ppnf.name_type = 'GLOBAL'
AND papf.person_id = ppmv.target_person_id
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_dateAbout Me:
SQL Query:
select papf.person_number
,ppnf.full_name
,hpv.summary about_me
,hpkaoe.keywords area_of_expertise
,regexp_replace(regexp_replace(regexp_replace(hpkaoe.KEYWORDS, '</p><br/>'), '</p>'),'<p>') as Area_Of_Expert
ise_wo_html
,hpkaoi.keywords area_of_interest
,regexp_replace(regexp_replace(regexp_replace(hpkaoi.KEYWORDS, '</p><br/>'), '</p>'),'<p>') as area_of_interest_wo_html
from PER_ALL_PEOPLE_F papf,
PER_PERSON_NAMES_F ppnf,
HRT_PROFILES_VL hpv,
HRT_PROFILE_KEYWORDS hpkaoe,
HRT_PROFILE_KEYWORDS hpkaoi
where hpv.profile_id = hpkaoe.profile_id
and hpkaoe.profile_id = hpkaoi.profile_id
and hpkaoe.keyword_type = 'AOE'
and hpkaoi. keyword_type = 'AOI'
and papf.person_id = hpv.person_id
and papf.person_id = ppnf.person_id
and ppnf.name_type = 'GLOBAL'
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_dateThere are multiple objects in Oracle HCM Cloud which support attachments. Often, there is a need to extract the attachment details stored at the object level. All the uploaded attachments are stored in fnd_attached_documents table. Below query is used to extract the attachment details stored at Service Request level in HR Helpdesk. You can replace the table name and primary key join to extract the data as per your requirement:
select ssr.sr_id,
ssr.title,
fad.CATEGORY_NAME ,
fdt.file_name,
fdt.dm_version_number document_id,
fdt.dm_document_id UCM_file
from fnd_attached_documents fad, svc_service_requests ssr, fnd_documents_tl fdt
where ENTITY_NAME = 'SVC_SERVICE_REQUESTS'
and ssr.sr_id = fad.PK1_VALUE
and fad.document_id = fdt.document_id
and fdt.language = 'US'Query to get list of Jobs having an attachment:
select pjft.name
from per_jobs_f_tl pjft
where pjft.language = 'US'
and exists (select 1
from fnd_attached_documents fad, PER_JOBS_F pjf, fnd_documents_tl fdt
where ENTITY_NAME = 'PER_JOBS_F'
and pjf.job_id = fad.PK1_VALUE
and fad.document_id = fdt.document_id
and fdt.language = 'US'
and pjf.job_id = pjft.job_id)Refer below link for attachments on Position profile:
In order to download the attchments from UCM, the user should have AttachmentsRead role attached. Please check the below post on how to create AttachmentsRead role:
https://fusionhcmconsulting.com/2021/03/security-role-to-view-document-of-record-attachments-on-ucm/
SELECT hapft.name
,pcaa.segment1
,pcaa.segment2
,pcaa.segment3
,pcaa.segment4
,pcaa.segment5
,pcaa.segment6
,pcaa.segment7
,pcaa.segment8
FROM PAY_COST_ALLOCATIONS_F pacf
,PAY_COST_ALLOC_ACCOUNTS pcaa
,HR_ALL_POSITIONS_F hapf
,HR_ALL_POSITIONS_F_TL hapft
WHERE pacf.cost_allocation_record_id = pcaa.cost_allocation_record_id
AND pacf.source_type = 'POS'
AND pacf.source_id = hapf.position_id
AND hapft.position_id = hapf.position_id
AND hapft.language = 'US'
AND TRUNC(SYSDATE) BETWEEN pacf.effective_start_date AND pacf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN hapf.effective_start_date AND hapf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN hapft.effective_start_date AND hapft.effective_end_dateWhen position hierarchy is enabled in Cloud HCM, Manage Positions UI shows some dynamically populated fields along with Parent position.
For example:
Use the below query to extract above details:
SELECT Positions.name "Position Name"
,Positions.FTE "Position Current FTE"
,Positions.INCUMBENT_FTE "Current Incumbent FTE"
,(Positions.FTE - Positions.INCUMBENT_FTE) "Difference FTE"
FROM
(SELECT HAPFT.NAME,
HAPF.FTE,
(select SUM(PAWMF.VALUE)
from PER_ALL_ASSIGNMENTS_M PAAM,
PER_ASSIGN_WORK_MEASURES_F PAWMF
where 1=1
AND PAAM.POSITION_ID = HAPF.POSITION_ID
AND SYSDATE BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
AND PAAM.ASSIGNMENT_TYPE = 'E'
AND PAAM.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
AND PAAM.ASSIGNMENT_ID = PAWMF.ASSIGNMENT_ID
AND PAWMF.UNIT = 'FTE') AS INCUMBENT_FTE
FROM HR_ALL_POSITIONS_F HAPF,
HR_ALL_POSITIONS_F_TL HAPFT
WHERE HAPF.POSITION_ID = HAPFT.POSITION_ID
AND USERENV('LANG') = HAPFT.LANGUAGE
AND TRUNC(SYSDATE) BETWEEN HAPF.EFFECTIVE_START_DATE AND HAPF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN HAPFT.EFFECTIVE_START_DATE AND HAPFT.EFFECTIVE_END_DATE
AND HAPFT.NAME IN('Test Position')
ORDER BY HAPFT.NAME ) Positions
Below query will extract all the custom fast formulas along with the name of formula and formula type.
select fff.formula_name
,fft.formula_type_name
,fff.formula_text
,fff.compile_flag
,fff.legislation_code
from ff_formulas_f fff
,ff_formula_types fft
where fff.formula_type_id = fft.formula_type_id
--and fft.formula_type_name = 'Extract Criteria'
and fff.LAST_UPDATED_BY <> 'SEED_DATA_FROM_APPLICATION'
AND TRUNC(SYSDATE) BETWEEN fff.effective_start_date AND fff.effective_end_date
ORDER BY 2,1To extract list of fast formulas used in HCM Extracts, run below query:
SELECT pedv.definition_name
,pedv.description
,pedv.legislation_code
,pedv.xml_tag_name
,pedv.ext_type_code
,fff.formula_name
,fft.formula_type_name
FROM pay_rep_criteria_f prcf
,pay_report_blocks_vl prbv
,per_ext_definitions_vl pedv
,ff_formulas_f fff
,ff_formula_types fft
WHERE prbv.ext_definition_id = pedv.ext_definition_id
AND prcf.report_block_id = prbv.report_block_id
AND fff.formula_id = prcf.formula_id
AND TRUNC(SYSDATE) BETWEEN fff.effective_start_date AND fff.effective_end_date
AND TRUNC(SYSDATE) BETWEEN prcf.effective_start_date AND prcf.effective_end_date
AND fff.formula_type_id = fft.formula_type_id
select hauft.NAME
from HR_ORG_UNIT_CLASSIFICATIONS_F houcf,
HR_ALL_ORGANIZATION_UNITS_F haouf,
HR_ORGANIZATION_UNITS_F_TL hauft
where haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID
AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID
AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE
AND hauft.LANGUAGE = 'US'
AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE
AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE
AND houcf.CLASSIFICATION_CODE = 'FUN_BUSINESS_UNIT'
AND trunc(SYSDATE) BETWEEN hauft.effective_start_date AND hauft.effective_end_dateSELECT papf.person_number, ITEM_TEXT2000_1 STATEMENT, ITEM_CLOB_1
FROM HRT_PROFILE_ITEMS ProfileItemPEO,
HRT_PROFILES_VL ProfilePEO,
HRT_PROFILE_TYP_SECTIONS ProfileTypeSectionPEO,
PER_ALL_PEOPLE_F papf
WHERE ProfilePEO.PROFILE_ID= ProfileItemPEO.PROFILE_ID
AND ProfileTypeSectionPEO.SECTION_CONTEXT = 'PERSON_CAREER_STATEMENT'
AND ProfileTypeSectionPEO.BUSINESS_GROUP_ID=ProfilePEO.BUSINESS_GROUP_ID
AND ProfileItemPEO.CONTENT_TYPE_ID=ProfileTypeSectionPEO.CONTENT_TYPE_ID
AND papf.person_id = ProfilePEO.person_id
AND ITEM_TEXT2000_1 IS NOT NULLUse below query to extract files loaded through Tools -> File Import and Export along with UCM Content ID and the account used:
SELECT DDOCTITLE "File Name"
,DWEBEXTENSION "File Extension"
,DDOCACCOUNT "Account"
,DDOCAUTHOR "Owner"
,DINDATE "Upload Date"
,DDOCNAME "Content Id"
,DDOCTYPE "Doc Type"
FROM revisions
WHERE DWEBEXTENSION <> 'log'
AND DDOCTITLE NOT LIKE 'ESS%'
ORDER BY DCREATEDATE DESCConfigure HCM Data Loader is a task that is used to define the HCM Data Loader parameters. The parameters are broadly divided into following categories:- Availability, Diagnostic, File Definition, Performance and Scheduling Default. These are the default settings which are applied to any HCM Data Load.
You can override some of these parameters at the individual dat file level i.e. using SET commands or some of the parameters can be overridden while submitting the Import and Load job.
Please note that the default values vary from HDL to HSDL (Spreadsheet Loader).
You can use below mentioned query to get the details from backend using a BIP:
select PARAM_CATEGORY
,PARAM_NAME
,DEFAULT_VALUE
,HSDL_DEFAULT_VALUE
,HDL_ENABLED
,HDL_OVERRIDE_ENABLED
,HSDL_ENABLED
,HSDL_OVERRIDE_ENABLED
,VALUE_LOOKUP_TYPE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
from hrc_dl_all_parameters
ORDER By 1,2Query to get list of overridden values:
select hdap.PARAM_CATEGORY
,hdap.PARAM_NAME
,hdap.DEFAULT_VALUE
,hdap.HSDL_DEFAULT_VALUE
,hdap.HDL_ENABLED
,hdap.HDL_OVERRIDE_ENABLED
,hdap.HSDL_ENABLED
,hdap.HSDL_OVERRIDE_ENABLED
,hdap.VALUE_LOOKUP_TYPE
,hdap.CREATED_BY
,hdap.CREATION_DATE
,hdap.LAST_UPDATED_BY
,hdap.LAST_UPDATE_DATE
,hdpo.OVERRIDE_LEVEL "OVERRIDDEN_AT_LEVEL"
,hdpo.OVERRIDE_VALUE "OVERRIDDEN_VALUE"
from hrc_dl_all_parameters hdap
,hrc_dl_parameter_overrides hdpo
where hdap.parameter_id = hdpo.parameter_id
ORDER By 1,2In 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
Select DISTINCT peevf.element_entry_value_id
,peef.element_entry_id
,petf.base_element_name
,peevf.effective_start_date
,paam.assignment_number
,pivf.base_name
from per_all_assignments_m paam
,pay_element_types_f petf
,pay_element_entries_f peef
,pay_element_entry_values_f peevf
,pay_input_values_f pivf
where 1=1
and paam.person_id = peef.person_id
and peef.element_type_id = petf.element_type_id
and pivf.element_type_id = petf.element_type_id
and peef.element_entry_id = peevf.element_entry_id
and paam.ASSIGNMENT_TYPE in ('E')
and paam.primary_assignment_flag = 'Y'
and petf.base_element_name = 'Dental Plan'
and pivf.base_name = 'Amount'
and paam.assignment_number = 'E1111'
and trunc(sysdate) between petf.effective_start_date and petf.effective_end_date
and trunc(sysdate) between paam.effective_start_date and paam.effective_end_date
and trunc(sysdate) between pivf.effective_start_date and pivf.effective_end_dateselect hapf.position_code
,hpb.profile_code
,hpt.description profile_desc
,hpt.summary
,hpeiv.DESCRIPTION
,hpeiv.RESPONSIBILITIES
,hpeiv.QUALIFICATIONS
from HRT_PROFILE_ITEMS hpi
,HRT_PROFILES_B hpb
,HRT_PROFILES_TL hpt
,HRT_PROFILE_RELATIONS hpr
,HRT_PROFILE_EXTRA_INFO_VL hpeiv
,HR_ALL_POSITIONS_F hapf
where hpi.profile_id = hpb.profile_id
and hpb.profile_usage_code = 'M'
and hpi.profile_id = hpr.profile_id
and hpi.profile_id = hpt.profile_id
and hpi.profile_id = hpeiv.profile_id
and hapf.position_id = hpr.object_id
and trunc(sysdate) between hapf.effective_start_date and hapf.effective_end_Date
and hapf.position_code= '1099'
and hpt.language = 'US'Query to extract only the profile data:
select hpb.profile_code
,hpt.description
,hpt.summary
,hpeiv.DESCRIPTION desc1
,hpeiv.RESPONSIBILITIES
,hpeiv.QUALIFICATIONS
,hikm.source_system_id
,hikm.source_system_owner
from HRT_PROFILES_B hpb
,HRT_PROFILES_TL hpt
,HRT_PROFILE_EXTRA_INFO_VL hpeiv
,HRC_INTEGRATION_KEY_MAP hikm
where hpeiv.profile_id = hpb.profile_id
and hpb.profile_usage_code = 'M'
and hpt.language = 'US'
and hpb.profile_code like '%TEST%'
and hikm.surrogate_id = hpeiv.PROFILE_EXTRA_INFO_ID
order by hpb.creation_date descUse the below query to get the details of Community in Learning:
SELECT asg.status,
asg.EVENT_TYPE,
asg.LEARNER_ID,
asg.EVENT_SUB_TYPE,
itm_tl.name learning_community_name,
(select full_name from per_person_names_f ppnf
where name_type ='GLOBAL'
and person_id = asg.LEARNER_ID
and trunc(sysdate) between effective_start_date and effective_end_date) member_name,
(select person_number from per_all_people_f papf
where person_id = asg.LEARNER_ID
and trunc(sysdate) between effective_start_date and effective_end_date) member_number
FROM wlf_assignment_records_f asg, wlf_learning_items_f itm,wlf_learning_items_f_tl itm_tl, WLF_LI_COMMUNITIES_F wlcf
WHERE TRUNC(SYSDATE) BETWEEN asg.effective_start_date AND asg.effective_end_date
and asg.status <> 'ORA_ASSN_REC_WITHDRAWN'
and itm.learning_item_id = asg.learning_item_id
and itm.learning_item_type in ('ORA_COMMUNITY')
and itm.learning_item_id =wlcf.learning_item_id
and itm.learning_item_id = itm_tl.learning_item_id
and itm_tl.language = 'US'
and TRUNC(SYSDATE) BETWEEN itm.effective_start_date AND itm.effective_end_date
and TRUNC(SYSDATE) BETWEEN itm_tl.effective_start_date AND itm_tl.effective_end_date
and itm_tl.name = 'Essential Workers'
ORDER by 5Default 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:
https://fusionhcmconsulting.com/2021/03/reports-bip-query-to-get-assignee-details-for-a-course/?amp
and warf.ASSIGNMENT_RULE_ID = war.ASSIGNMENT_RULE_ID
And you will get the required output.
select
WLFI.LEARNING_ITEM_NUMBER
,WLFI.LEARNING_ITEM_ID
,TO_CHAR(WLFI.EFFECTIVE_END_DATE,'YYYY/MM/DD') "Offering Effective End Date"
,TO_CHAR(WLFI.EFFECTIVE_START_DATE,'YYYY/MM/DD') "Offering Effective Start Date"
,WLFT.NAME
,WLCLF.MAXIMUM_ATTENDEES
,WLCLF.MINIMUM_ATTENDEES
,TO_CHAR(WLCLF.ENROLMENT_END_DATE,'YYYY/MM/DD') "Offering Enrollment End Date"
,TO_CHAR(WLCLF.ENROLMENT_START_DATE,'YYYY/MM/DD') "Offering Enrollment Start Date"
,WLCLF.DELIVERY_MODE
,TO_CHAR(WLFI.END_DATE,'YYYY/MM/DD') "Offering Publish End Date"
,TO_CHAR(WLFI.START_DATE,'YYYY/MM/DD') "Offering Publish Start Date"
,TO_CHAR(WLFI.LI_START_DATE,'YYYY/MM/DD') "Offering Start Date"
,TO_CHAR(WLFI.LI_END_DATE,'YYYY/MM/DD') "Offering End Date"
FROM WLF_LEARNING_ITEMS_F WLFI,
WLF_LI_COURSES_F WLCF,
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(+) = 'US'
AND WLFI.EFFECTIVE_START_DATE BETWEEN WLFT.EFFECTIVE_START_DATE AND WLFT.EFFECTIVE_END_DATE
AND WLFI.EFFECTIVE_END_DATE BETWEEN WLFT.EFFECTIVE_START_DATE AND WLFT.EFFECTIVE_END_DATE
AND WLFI.LEARNING_ITEM_TYPE = 'ORA_CLASS'
AND WLFI.LEARNING_ITEM_NUMBER = 'OLC245030'