Build the BI learning community report

SELECT 
  (Select MEANING from fnd_lookup_values_tl where LOOKUP_CODE = asg.status and LOOKUP_TYPE = 'ORA_WLF_ASSIGN_RECORD_STATUS')   status,
  offical_email.email_address Email,
  --asg.EVENT_TYPE, 
  --asg.LEARNER_ID, 
  --asg.EVENT_SUB_TYPE,
  --asg.STATUS     ASGStatus,
  ppnf1.display_name     AddedBy,
  --asg.CREATED_BY     AddedBy,  --John
  to_char(asg.CREATION_DATE,'mm-dd-yyyy')     CreationDate,
  itm_tl.name CommunityName,
  itm.LEARNING_ITEM_NUMBER     CommunityNumber,
  (Select MEANING from fnd_lookup_values_tl where LOOKUP_CODE = itm.VISIBILITY and LOOKUP_TYPE = 'ORA_WLF_SS_CMNTY_VISIBILITY')   VISIBILITY,
  (Select MEANING from fnd_lookup_values_tl where LOOKUP_CODE = wlcf.COMMUNITY_TYPE and LOOKUP_TYPE = 'ORA_WLF_COMMUNITY_TYPE_CODE')   COMMUNITY_TYPE,
  (
    select 
      display_name 
    from 
      per_person_names_f ppnf 
    where 
      name_type = 'GLOBAL' 
      and person_id = asg.LEARNER_ID 
      and trunc(sysdate) between effective_start_date 
      and effective_end_date
  ) member_name,
  (
    select 
      person_number 
    from 
      per_all_people_f papf 
    where 
      person_id = asg.LEARNER_ID 
      and trunc(sysdate) between effective_start_date 
      and effective_end_date
  ) member_number 
FROM 
  wlf_assignment_records_f asg, 
  wlf_learning_items_f itm, 
  wlf_learning_items_f_tl itm_tl, 
  WLF_LI_COMMUNITIES_F wlcf,
  PER_USERS pu,
  per_person_names_f ppnf1,
  per_email_addresses offical_email
  
  
WHERE
  TRUNC(SYSDATE) BETWEEN asg.effective_start_date AND asg.effective_end_date 
  and asg.status <> 'ORA_ASSN_REC_WITHDRAWN' 
  and itm.learning_item_id = asg.learning_item_id 
  and itm.learning_item_type in ('ORA_COMMUNITY') 
  and itm.learning_item_id = wlcf.learning_item_id 
  and itm.learning_item_id = itm_tl.learning_item_id 
  and itm_tl.language = 'US' 
  and TRUNC(SYSDATE) BETWEEN itm.effective_start_date AND itm.effective_end_date 
  and TRUNC(SYSDATE) BETWEEN itm_tl.effective_start_date AND itm_tl.effective_end_date
  
  and pu.USERNAME = asg.CREATED_BY
  and pu.person_id = ppnf1.person_id
  and ppnf1.NAME_TYPE = 'GLOBAL'
  and trunc(sysdate) between ppnf1.effective_start_date and ppnf1.effective_end_date
  and offical_email.person_id (+) = asg.LEARNER_ID
  and offical_email.email_type (+)  = 'W1'
  
  --and itm_tl.name = 'ASW 2 - 2022-2'
order by itm.LEARNING_ITEM_NUMBER

I hope this blog post was helpful for you. If you have any questions or feedback, please leave a comment below.