Learn about Redwood and be one of the first to join the conversation

Visit Redwood Community
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

SQL query to get Instructor and learner details in fusion

Accepted answer
15
Views
2
Comments
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!