SQL Query to Extract Loaded Offerings from Oracle Fusion

In the below query, you need to change SOURCE_SYSTEM_OWNER value as per your legacy system value stored in HRC_SOURCE_SYSTEM_OWNER lookup value.

SELECT DISTINCT

'OfferingV3' "OBJECT_NAME",

TO_CHAR(wlfli.EFFECTIVE_START_DATE,'DD/MM/YYYY') AS EFFECTIVE_START_DATE,        

    TO_CHAR(wlfli.EFFECTIVE_END_DATE,'DD/MM/YYYY') EFFECTIVE_END_DATE,

wlfli.LEARNING_ITEM_NUMBER AS OFFERING_NUMBER,

wlflitl.NAME AS TITLE,

wlflitl.DESCRIPTION AS DESCRIPTION_TEXT,

wlicf.DELIVERY_MODE AS OFFERING_TYPE,

wlficf.CONTENT_NUMBER,

TO_CHAR(wlfli.START_DATE,'DD/MM/YYYY') PublishStartDate,   

    TO_CHAR(wlfli.END_DATE,'DD/MM/YYYY') PublishEndDate,

TO_CHAR(wlfli.LI_START_DATE,'DD/MM/YYYY') OFFERING_START_DATE,

    TO_CHAR(wlfli.LI_END_DATE,'DD/MM/YYYY') OFFERING_END_DATE,

wlfrb.RESOURCE_NUMBER AS PRIMARY_LOCATION_NAME,

wlfli.LANGUAGE_CODE,

wlicf.FACILITATOR_TYPE,

(SELECT PERSON_NUMBER FROM PER_ALL_PEOPLE_F WHERE PERSON_ID IN (SELECT person_id FROM wlf_instructor_resources WHERE INSTRUCTOR_ID IN

(SELECT PRIMARY_INSTRUCTOR_ID FROM WLF_LI_CLASSES_F WHERE learning_item_id = wlfli.learning_item_id))) PRIMARY_INSTRUCTOR_NUMBER,

(SELECT PERSON_NUMBER FROM PER_ALL_PEOPLE_F WHERE PERSON_ID IN (SELECT COORDINATOR_ID FROM WLF_LI_CLASSES_F 

WHERE learning_item_id = wlfli.learning_item_id)) COORDINATOR_NUMBER,

wlflif.LEARNING_ITEM_NUMBER AS COURSE_NUMBER,

(select distinct person_number from per_all_people_f where person_id = wlfli.OWNED_BY_ID ) OwnedByPersonNumber,

hrcik.SOURCE_SYSTEM_OWNER,

hrcik.SOURCE_SYSTEM_ID

FROM 

WLF_LEARNING_ITEMS_F wlfli,

HRC_INTEGRATION_KEY_MAP hrcik,

WLF_LEARNING_ITEMS_F_TL wlflitl,

WLF_LI_CLASSES_F wlicf,

WLF_LI_COURSES_F wlic,

WLF_LI_CONTENT_F wlficf,

WLF_RESOURCES_B wlfrb,

WLF_LEARNING_ITEMS_F wlflif

WHERE  wlfli.LEARNING_ITEM_ID = hrcik.SURROGATE_ID

AND wlfli.LEARNING_ITEM_ID = wlflitl.LEARNING_ITEM_ID

AND wlfli.LEARNING_ITEM_ID = wlicf.LEARNING_ITEM_ID

AND wlicf.COURSE_LEARNING_ITEM_ID = wlic.LEARNING_ITEM_ID

AND wlfli.LEARNING_ITEM_ID = wlficf.LEARNING_ITEM_ID(+)

AND wlicf.PRIMARY_CLASSROOM_ID = wlfrb.RESOURCE_ID(+)

AND wlflif.LEARNING_ITEM_ID = wlicf.COURSE_LEARNING_ITEM_ID

AND hrcik.SOURCE_SYSTEM_OWNER = 'HRC_SQLLDR'

AND wlfli.CREATED_BY like 'FUSION_APPS_HCM_ESS_LOADER_APPID'

AND wlfli.LEARNING_ITEM_TYPE = 'ORA_CLASS'

AND wlflitl.LANGUAGE = USERENV('LANG')