SQL query to get Instructor and learner details in fusion
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'
1