Search for:
Oct 24 2021
BIP – Query to Extract Learner Completion Details

Use below query to extract the learner completion details. The same query can be used to generate the Learning Record HDL format with small changes.

SELECT TO_CHAR(warf.EFFECTIVE_START_DATE,'RRRR/MM/DD') "Learning Record EffectiveStartDate"
	   ,warf.ASSIGNMENT_RECORD_NUMBER "Learning Record Number"
	   ,weaf.ASSIGNMENT_PROFILE_NUMBER "Assignment Number"
	   ,wlif.LEARNING_ITEM_TYPE "Learning ItemType"
	   ,wlif.LEARNING_ITEM_NUMBER "Learning ItemNumber"
	   ,warf.EVENT_TYPE "Assignment Type"
	   ,warf.EVENT_SUB_TYPE "Assignment SubType"
	   ,warf.ATTRIBUTION_TYPE "Assignment AttributionType"
	   ,warf.ATTRIBUTION_LOOKUP_CODE "Assignment AttributionCode"
       ,papf.PERSON_NUMBER "Learner Number"
	   ,warf.STATUS "Learning RecordStatus"
       ,TO_CHAR(NVL(warf.ASSIGNMENT_STARTED_ON_DATE,warf.EFFECTIVE_START_DATE),'RRRR/MM/DD') "Learning Record StartDate"
       ,TO_CHAR(warf.CALCULATED_DUE_DATE,'RRRR/MM/DD') "Due Date"	   
       ,TO_CHAR(warf.COMPLETION_DATE,'RRRR/MM/DD') "Completion Date"	   
       ,warf.ACTUAL_SCORE "Actual Score"
  FROM WLF_LEARNING_ITEMS_F wlif,
       WLF_ASSIGNMENT_RECORDS_F warf,
       WLF_EVENT_ASSIGNMENTS_F weaf,
       PER_ALL_PEOPLE_F papf	   
 WHERE to_char(warf.LEARNING_ITEM_ID) = to_char(wlif.LEARNING_ITEM_ID)
   AND warf.STATUS = 'ORA_ASSN_REC_COMPLETE'
   AND TRUNC(SYSDATE) BETWEEN warf.EFFECTIVE_START_DATE AND warf.EFFECTIVE_END_DATE
   AND TRUNC(SYSDATE) BETWEEN wlif.EFFECTIVE_START_DATE AND wlif.EFFECTIVE_END_DATE
   AND warf.learner_id = papf.person_id
   AND TRUNC(SYSDATE) BETWEEN papf.EFFECTIVE_START_DATE AND papf.EFFECTIVE_END_DATE
   AND weaf.EVENT_ASSIGNMENT_ID = warf.EVENT_ASSIGNMENT_ID
Mandeep Gupta 0 Comments
Oct 5 2021
BIP – Query to extract GL Cost Details from Department

GL Cost center Information can be maintained at Department Level.

This information can be extracted using below SQL:

SELECT DISTINCT houft.name, hoif.ORG_INFORMATION2	"Value Set Id"
       ,hoif.ORG_INFORMATION3	"Cost Center 1"
	   ,hoif.ORG_INFORMATION7	"Record Identifier"
  FROM HR_ORGANIZATION_INFORMATION_F hoif,
       HR_ORGANIZATION_UNITS_F_TL houft
 WHERE trunc(sysdate) BETWEEN hoif.EFFECTIVE_START_DATE AND hoif.EFFECTIVE_END_DATE
   AND houft.organization_id = hoif.organization_id
   AND hoif.ORG_INFORMATION_CONTEXT = 'PER_GL_COST_CENTER_INFO'
   AND houft.language = 'US'
   AND TRUNC(sysdate) BETWEEN houft.EFFECTIVE_START_DATE AND houft.EFFECTIVE_END_DATE
ORDER by 1

To bulk upload GL Cost Centers Organization.dat HDL can be used. Please check the below link for more details:

https://fusionhcmconsulting.com/2021/01/hdl-sample-hdl-to-load-gl-information-at-department-level/

Mandeep Gupta 0 Comments