SQL query to get Instructor and learner details in fusion — Cloud Customer Connect
You're almost there! Please answer a few more questions for access to the Applications content. Complete registration
Interested in joining? Complete your registration by providing Areas of Interest here. Register
Get Started with Redwood: Oracle Cloud SCM and Purchasing

SQL query to get Instructor and learner details in fusion

edited Jul 16, 2021 2:12PM in Learning 2 comments

Content

Hi,

I have a requirement where I need to fetch instructor and learner details of the offering. I have developed the SQL query and attached for the reference.

Issue: I am getting multiple rows for an offering which consist the redundant data. I doubt that I missed some joining. 

Code Snippet

with learner as
(
SELECT 
      ppnf.full_name
	, (
	   case 
	   when
	   PER_EXTRACT_UTILITY.GET_DECODED_LOOKUP('ORA_WLF_ASSIGN_RECORD_STATUS',WARF.STATUS) like 'Pending%' then 
	   PER_EXTRACT_UTILITY.GET_DECODED_LOOKUP('ORA_WLF_ASSIGN_RECORD_STATUS',WARF.STATUS)
	   else PER_EXTRACT_UTILITY.GET_DECODED_LOOKUP('ORA_WLF_ASSIGN_RECORD_STATUS',WARF.SUB_STATUS)
	   end
	   )Off_status
	   ,WARF.LEARNING_ITEM_ID as warf_lid
	   ,WAAV.LEARNING_ITEM_ID as wav_lid
	   ,WARF.EVENT_ASSIGNMENT_ID as warf_eai

  FROM 
    WLF_ALL_ASSIGNMENTS_V WAAV,
    per_all_people_f papf,
    per_person_names_f ppnf,
    WLF_ASSIGNMENT_RECORDS_F WARF

 WHERE 
   WAAV.LEARNER_ID = papf.person_id
   AND papf.person_id = ppnf.person_id
   AND ppnf.name_type = 'GLOBAL'   
  

Howdy, Stranger!

Log In

To view full details, sign in.

Register

Don't have an account? Click here to get started!