Query to get ‘External Learning’ Data – Based on OTA Tables

select NOTA_HISTORY_ID,onh.PERSON_ID,ppf.employee_number,ppf.full_name,onh.NTH_INFORMATION1 Course_Code,onh.TRNG_TITLE
,oav.version_code equivalent_Course_code, oav.version_name 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_INFORMATION4
from ota_notrng_histories onh, per_all_people_f ppf,XXALB_CATL_OBJ_COURSES_V oav
where onh.PERSON_ID = ppf.person_id
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and oav.ACTIVITY_VERSION_ID = onh.ACTIVITY_VERSION_ID
–and oav.PARENT_CAT_USAGE_ID = nvl(:P_PARENT_CAT_USAGE_ID,oav.PARENT_CAT_USAGE_ID)
–and oav.CATEGORY_USAGE_ID =  NVL(:P_CATEGORY_USAGE_ID,oav.CATEGORY_USAGE_ID)
–and ppf.employee_number = nvl(:P_employee_number, ppf.employee_number)