Get Started with Redwood for Oracle Cloud HCM Begin Now
To ensure that questions get required attention from community members and are NOT left unanswered, it’s important for the author to indicate (by selecting “Yes” or “No” when prompted) whether the question was answered. (newly added) Please note that it is also important to respond to EACH comment your question receives. Your Yes or No response ensures an accurate status for your question.
For more information, please refer to this announcement explaining best practices for getting answers to questions.
For more information, please refer to this announcement explaining best practices for getting answers to questions.
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