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.
In the enhanced profiles (Profile V2), for bulk upload, one has to use TalentProfile.dat file as earlier. However while loading profile items against a profile, a new attribute SectionId should be provided. SectionId is based on the content item being used.
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
Lookups are commonly used across modules in SaaS. Sometimes, the number of lookups is so much that it takes lot of time and effort to create them manually in the application. Oracle SaaS supports bulk upload of both lookup types and lookup codes.
In this post, we will see how to make use of file based loader to load lookup types.
Prepare the lookup code file as given below:
LookupType|Meaning|Description|ModuleKey|ModuleType TXX_MASS_UPLOAD|Mass Upload Lookup Definition|Test Lookup created for demo purpose|HcmCommonHrCore|LBA TXX_MASS_UPLOAD_A|Mass Upload Lookup Def – A|Test Lookup created for demo purpose-A|HcmCommonHrCore|LBA
Out of above listed attributes, only the description is optional.
Module Key and module type both are required parameters. To know what value shall be passed, please check the below post:
Oracle HCM makes use of extended lookups feature to support dependent lookup values. For example, while creating a VISA or Work Permit record for a Person for Singapore, the Category field is dependent upon Type of the pass chosen. The values of Category field are derived from Extended lookup.
Navigate to Setup and Maintenance -> Manage Extended Lookup codes -> Visa Permit Type
Now, let us take an example where we need to load 2 values for categories based on the lookup code S Pass.
Prepare the HDL file in below format:
METADATA|ExtendedLookupCode|ExtendedLookupCodeId|LookupType|LookupCode|LegislationCode|ExtendedLookupCode|ExtendedLookupCodeName|SourceSystemOwner|SourceSystemId MERGE|ExtendedLookupCode||PER_VISA_PERMIT_TYPE|SG_SP|SG|TEST_SP1|S Pass Holder – Test 1|HRC_SQLLOADER|TEST_1 MERGE|ExtendedLookupCode||PER_VISA_PERMIT_TYPE|SG_SP|SG|TEST_SP2|S Pass Holder – Test 2|HRC_SQLLOADER|TEST_2
zip the file and upload using HCM Data Loader from Data Exchange.
On successful load, the new values can be verified from either of following two places on the UI:
While defining Common Lookups or value sets, you need to provide module value. Each module has an associated module type, module key and product code associated with it. For example:
These details are stored in backed in a table – FND_APPL_TAXONOMY.
Use the below query to find module type, module key etc for a module:
select fat.MODULE_NAME
,fat.MODULE_TYPE
,fat.MODULE_KEY
,fat.PRODUCT_CODE
from FND_APPL_TAXONOMY fat
Lookups are used commonly to meet different requirements. Many a times, lookup values easily go past hundred values, in such case adding the values one by one into the lookup is very tedious and error prone job.
There is no HDL support to bulk upload the lookup values. However, a file based solution is available which is easy to use and quick.
We have already discussed on how to bulk upload lookup types in below post:
Follow the below steps to mass upload lookup values:
Create a custom lookup from UI:
[N] – Setup and Maintenance -> Search -> Manage Common Lookups
2. Click on Add New (+) under search results:
3. Provide the details and Click on Save:
4. Prepare the lookup values file in below format:
LookupType|LookupCode|DisplaySequence|EnabledFlag|StartDateActive|EndDateActive|Meaning|Description|Tag XXX_MASS_UPLOAD|MASS_01|1|Y|15/12/2001||Mass Upload Value 1|Mass Upload Value 1 Description|+GB
Below mentioned attributes in the above file are Mandatory:
-> LookupType
-> LookupCode
-> EnabledFlag
-> Meaning
Except these all other fields are optional.
Date Format for StartDateActive and EndDateActive attributes is DD/M/RRRR.
File should be pipe (|) delimited.
Save the file as csv.
5. Once the file is ready, navigate to – Tools -> File Import and Export
6. Click on Add (+) and choose your file:
Select account as :-> setup/functionalSetupManger/import
Click on Save and Close.
7. Navigate to Manage Common Lookups. Under Search Results click on Action and Import:
8. Select the account and give the file name as given in step 6 and Click on Upload button:
9. Monitor the import progress:
10. Once the import is complete, verify the uploaded values:
11. Results can be verified from Import file log as well:
Both Lookup types and lookup codes can be loaded in one shot as well. Prepare both the files simultaneously and follow the same steps as given above.
There is a common requirement to mask salary data post P2T refreshes. This should be done in order to hide the actual salaries information as salary is a very sensitive information.
Use the below query to generate data in HDL format in a test environment immediately after P2T refresh. The below query generate a random salary amount. Save the downloaded data in .dat file format and upload it back to the instance.
Select 'METADATA|Salary|AssignmentNumber|SalaryAmount|DateFrom|DateTo|SalaryBasisId|SalaryId' Header, 1 data_flow_order
from dual
UNION
SELECT 'MERGE|Salary'||'|'||
paam.assignment_number||'|'||
round(DBMS_RANDOM.VALUE (1,15000) , 2)||'|'||
TO_CHAR(cs.date_from,'RRRR/MM/DD', 'nls_date_language=American')||'|'||
TO_CHAR(cs.date_to,'RRRR/MM/DD', 'nls_date_language=American')||'|'||
cs.salary_basis_id||'|'||
cs.salary_id data_row,
2 data_flow_order
FROM cmp_salary cs,
per_all_assignments_m paam
WHERE cs.assignment_id= paam.assignment_id
AND trunc(sysdate) between paam.effective_start_date AND paam.effective_end_date
AND paam.assignment_type in ('E', 'C', 'P')
AND paam.assignment_number ='E788880'
ORDER BY data_flow_order
There are various cases where more than one attachment should be maintained against employee document record. First of all, we will create a new document type where multiple attachments will be allowed.
Navigate to Setup and Maintenance -> Document Types -> Click on Create
2. Choose multiple occurrences as Yes:
3. Enter the relevant information and submit the record.
4. Prepare the HDL in below format and load the data:
METADATA|DocumentsOfRecord|PersonNumber|DateFrom|DateTo|DocumentType|DocumentName|DocumentNumber|DocumentCode|Country|AttachmentEntityName|Comments|IssuedDate|IssuingAuthority|IssuingCountry|IssuingLocation|Publish|PublishDate|SourceSystemId|SourceSystemOwner MERGE|DocumentsOfRecord|101|2021/01/01||Test Multi Attachments Doc|Multiple Attachments||TMAD_001||||||||||HRC_SQLLOADER_101_TMAD_001|HRC_SQLLOADER
METADATA|DocumentAttachment|SourceSystemOwner|SourceSystemId|PersonNumber|DocumentType|DocumentCode|Title|Description|DataTypeCode|Country|EntityName|File|FileName|MimeType|URL|URLorTextorFileName|DmVersonNumber MERGE|DocumentAttachment|HRC_SQLLOADER|HRC_SQLLOADER_101_TMAD_001_1|101|Test Multi Attachments Doc|TMAD_001|Test Multi Attachments Doc|Test Multi Attachments Doc 1|FILE|||Historical_rating.docx|Historical_rating.docx|||Historical_rating.docx| MERGE|DocumentAttachment|HRC_SQLLOADER|HRC_SQLLOADER_101_TMAD_001_2|101|Test Multi Attachments Doc|TMAD_001|Test Multi Attachments Doc|Test Multi Attachments Doc 2|FILE|||Historical_rating1.docx|Historical_rating1.docx|||Historical_rating1.docx|
5. Place the docx files in BlobFiles folder and zip with the .dat file:
SELECT distinct houft.name,
ppnfv.full_name
FROM HR_ORGANIZATION_INFORMATION_F hoif,
per_person_names_f_v ppnfv,
hr_organization_units_f_tl houft
WHERE trunc(sysdate) BETWEEN hoif.EFFECTIVE_START_DATE AND hoif.EFFECTIVE_END_DATE
AND trunc(sysdate) BETWEEN ppnfv.effective_Start_date AND ppnfv.effective_end_date
AND ppnfv.person_id=hoif.ORG_INFORMATION2
AND houft.organization_id = hoif.organization_id
AND hoif.ORG_INFORMATION_CONTEXT = 'PER_ORG_MANAGER_INFO'
AND houft.language = 'US'
AND TRUNC(sysdate) BETWEEN houft.EFFECTIVE_START_DATE AND houft.EFFECTIVE_END_DATE
select CASE WHEN COUNT(ppos.period_of_service_id) = 0
THEN 'N'
ELSE 'Y'
END working_as_employee
from per_periods_of_service ppos
where ppos.person_id = pcr.CONTACT_PERSON_ID -- person_id of contact
and NVL(ppos.actual_termination_date, TRUNC(sysdate)) >= TRUNC(sysdate)
select papf.person_number,
aapft.name aapftan_name ,
apae.begin_bal,
apae.accrued,
apae.used,
apae.accrual_period,
apae.end_bal,
apae.first_last_prd_in_aapft_term
from per_all_peoaapfte_f papf,
anc_per_accrual_entries apae,
anc_absence_aapftans_f_tl aapft
where 1=1
and papf.person_id = apae.person_id
and apae.aapftan_id= aapft.absence_aapftan_id
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and aapft.language ='US'
and trunc(sysdate) between aapft.effective_start_date and aapft.effective_end_date
and apae.accrual_period <= TO_DATE('2021/12/31','YYYY/MM/DD')
Oracle provided a new functionality to include e signatures in checklist tasks. A custom report can be configured and upon e-signature and completion, it will be saved to worker’s document of records.
You can follow the below document on my oracle support for detailed steps – 2611795.1
Below is the basic SQL query to start the data model development. You can add additional tables/columns as per your need.
SELECT DISTINCT Person.PERSON_ID
,PAT.ALLOCATED_CHECKLIST_ID AS P_ALLOCATED_CHECKLIST_ID
--,PAT.ALLOCATED_TASK_ID AS TASK_ID
,PersonName.FULL_NAME FULL_NAME
,PersonName.DISPLAY_NAME DISPLAY_NAME
,PersonName.first_name FIRST_NAME
,PersonName.last_name LAST_NAME
,PersonName.middle_names MIDDLE_NAMES
,to_char(Person.date_of_birth,'DD-MM-RRRR', 'nls_date_language=American') DOB
,(SELECT flv.meaning
FROM FND_LOOKUP_VALUES flv
WHERE flv.lookup_type = 'TITLE'
AND flv.language = 'US'
AND flv.lookup_code = PersonName.title) TITLE
--,PAT.SIGNER_NAME AS P_SIGNER_NAME
--,TO_CHAR(PAT.SIGN_DATE,'MM/DD/YY') AS P_SIGN_DATE
,:P_SIGNER_NAME P_SIGNER_NAME
,:P_SIGN_DATE P_SIGN_DATE
FROM PER_PERSONS Person,
PER_PERSON_NAMES_F PersonName,
PER_ALLOCATED_CHECKLISTS PAC,
PER_ALLOCATED_TASKS_VL PAT
WHERE PAC.ALLOCATED_CHECKLIST_ID = NVL(:P_ALLOCATED_CHECKLIST_ID,PAC.ALLOCATED_CHECKLIST_ID)
AND PAC.ALLOCATED_CHECKLIST_ID=PAT.ALLOCATED_CHECKLIST_ID
AND PAT.PERFORMER_ORIG_SYS_ID = Person.PERSON_ID
AND PersonName.PERSON_ID = Person.PERSON_ID
AND PersonName.NAME_TYPE = 'GLOBAL'
AND GREATEST(TRUNC(PAC.ACTION_DATE),TRUNC(SYSDATE)) BETWEEN LEAST(TRUNC(SYSDATE),PersonName.EFFECTIVE_START_DATE) AND PersonName.EFFECTIVE_END_DATE
AND PAT.STATUS='COM'
AND PAT.allocated_task_id=3000012222113131
While creating a position profile, there is an option to add the attachments. In order to get the various details of the attachment like name, UCM content ID etc, please use the below query:
select hpb.PROFILE_TYPE_ID,
PROFILE_CODE,
PROFILE_STATUS_CODE,
PROFILE_USAGE_CODE,
fad.CATEGORY_NAME ,
fdt.file_name,
fdt.dm_version_number document_id,
fdt.dm_document_id UCM_file
from fnd_attached_documents fad, HRT_PROFILES_B hpb, fnd_documents_tl fdt
where ENTITY_NAME = 'HRT_PROFILES_B'
and hpb.profile_id = fad.PK1_VALUE
AND fad.document_id = fdt.document_id
AND fdt.language = 'US'
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: