Query to get ‘External Learning’ Data – Based on Views

If you are following the approach to make the view for catalog objects, following is the query to get the data for external learning. Catalog Objects Views are used in where clause of this query.

select NOTA_HISTORY_ID
,onh.PERSON_ID,ppf.employee_number,ppf.full_name,onh.NTH_INFORMATION1 Course_Code,onh.TRNG_TITLE,NULL equivalent_Course_code, NULL equivalent_Course_name,onh.PROVIDER,
hr_general.DECODE_LOOKUP(‘OTA_TRAINING_TYPES’,onh.TYPE) TYPE,onh.CENTRE,onh.COMPLETION_DATE,onh.AWARD,onh.RATING
,onh.DURATION,hr_general.DECODE_LOOKUP(‘OTA_DURATION_UNITS’,onh.DURATION_UNITS) DURATION_UNITS
,hr_general.DECODE_LOOKUP(‘OTA_TRAINING_STATUSES’,onh.STATUS) STATUS
,onh.NTH_INFORMATION2 Instructor ,onh.NTH_INFORMATION3
,onh.NTH_INFORMATION8,onh.NTH_INFORMATION9
,onh.CUSTOMER_ID,onh.ORGANIZATION_ID
from ota_notrng_histories onh, per_all_people_f ppf–,XXALB_CATL_OBJ_COURSES_V oav
where onh.PERSON_ID = ppf.person_id
and onh.ACTIVITY_VERSION_ID is null
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
–and onh.NTH_INFORMATION8 = nvl((select distinct parent_category from LSG_CATL_OBJ_SUB_CATGRY_Vwhere PARENT_CAT_USAGE_ID = :P_PARENT_CAT_USAGE_ID),onh.NTH_INFORMATION8) 
–and onh.NTH_INFORMATION9 = nvl((select distinct category from LSG_CATL_OBJ_SUB_CATGRY_V whereCATEGORY_USAGE_ID = :P_CATEGORY_USAGE_ID),onh.NTH_INFORMATION9)
–and ppf.employee_number = nvl(:P_employee_number, ppf.employee_number)