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)

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)

Query to get ‘Course’ Data

create or replace view  LSG_CATL_OBJ_COURSES_V
as select ocu.PARENT_CAT_USAGE_ID,ocup.category parent_ctagory,ocu.CATEGORY_USAGE_ID,ocu.category,ocu.type,ocu.DATA_SOURCE, oav.version_code,oav.version_name,oav.activity_version_id
from ota_category_usages ocu, ota_category_usages ocup,ota_activity_definitions oad,ota_activity_versions oav
where 1=1
and ocup.CATEGORY_USAGE_ID = ocu.PARENT_CAT_USAGE_ID
and ocu.CATEGORY_USAGE_ID = oad.CATEGORY_USAGE_ID
and oav.activity_id = oad.activity_id

Query to get ‘Sub Category’ Data

create or replace view  LSG_CATL_OBJ_SUB_CATGRY_V
as select ocu.PARENT_CAT_USAGE_ID,ocup.category parent_category,ocu.CATEGORY_USAGE_ID,ocu.category,ocu.type,ocu.DATA_SOURCE
from ota_category_usages ocu, ota_category_usages ocup
where ocup.CATEGORY_USAGE_ID = ocu.PARENT_CAT_USAGE_ID
order by 2

Query to get ‘Category’ Data

create or replace view  LSG_CATL_OBJ_PARNT_CATGRY_V
as select distinct ocu.PARENT_CAT_USAGE_ID,ocup.category parent_ctagory–,ocu.CATEGORY_USAGE_ID,ocu.category,ocu.type,ocu.DATA_SOURCE
from ota_category_usages ocu, ota_category_usages ocup
where ocup.CATEGORY_USAGE_ID = ocu.PARENT_CAT_USAGE_ID
order by 2

OLM Queries Part 2

SELECT bgrt.NAME business_group_name, orgt.NAME department,
apse.full_name appraisee, appr.full_name appraiser,
mapr.full_name main_appraiser, apr.appraisal_date appraisal_date,
apr.appraisal_period_start_date appraisal_start_date,
apr.appraisal_period_end_date appraisal_end_date,
apr.next_appraisal_date next_appraisal_date,
apr.comments appraisal_comments, rtt.NAME rating_level_name,
rtl.step_value step_value,
hr_bis.bis_decode_lookup ('APPRAISAL_TYPE',
apr.TYPE) appraisal_type,
hr_bis.bis_decode_lookup
('APPRAISAL_SYSTEM_STATUS',
apr.appraisal_system_status
) appraisal_status,
hr_bis.bis_decode_lookup ('APPRAISEE_ACCESS',
apr.appraisee_access
) appraisee_access,
apr.TYPE appraisal_type_code,
apr.appraisal_system_status appraisal_status_code,
apr.appraisee_access appraisee_access_code,
apr.creation_date creation_date,
apr.last_update_date last_update_date, '_DF:PER:PER_APPRAISALS:APR',
apr.appraisal_id appraisal_id,
apr.business_group_id business_group_id,
apr.appraisal_template_id appraisal_template_id,
apr.appraisee_person_id appraisee_person_id,
apr.appraiser_person_id appraiser_person_id,
apr.group_initiator_id group_initiator_id,
apr.overall_performance_level_id overall_performance_level_id,
apr.main_appraiser_id main_appraiser_id,
apr.assignment_id assignment_id, apr.event_id event_id,
rtl.rating_scale_id rating_scale_id,
rtl.competence_id competence_id,
orgt.organization_id organization_id
FROM per_appraisals apr,
hr_all_organization_units_tl bgrt,
hr_all_organization_units_tl orgt,
per_people_x apse,
per_people_x appr,
per_people_x mapr,
per_rating_levels rtl,
per_rating_levels_tl rtt
WHERE apr.business_group_id = bgrt.organization_id
AND bgrt.LANGUAGE = USERENV ('LANG')
AND apr.assignment_organization_id = orgt.organization_id
AND orgt.LANGUAGE = USERENV ('LANG')
AND apr.appraisee_person_id = apse.person_id
AND apr.appraiser_person_id = appr.person_id
AND apr.main_appraiser_id = mapr.person_id(+)
AND apr.overall_performance_level_id = rtl.rating_level_id(+)
AND rtl.rating_level_id = rtt.rating_level_id(+)
AND rtt.LANGUAGE(+) = USERENV ('LANG')
AND apr.business_group_id =
NVL (hr_bis.get_sec_profile_bg_id, apr.business_group_id)
WITH READ ONLY;

OLM Queries Part 1

SELECT APPRAISAL_TYPE_M
,A.APPRAISEE
,C.EMPLOYEE_NUMBER
,A.MAIN_APPRAISER
,A.DEPARTMENT
,A.APPRAISAL_STATUS_M APPRAISAL_STATUS
, DECODE (B.APPRAISAL_SYSTEM_STATUS ,'ONGOING','Appraisal Pending With - '||A.MAIN_APPRAISER
,'APPRFEEDBACK','Appraisal Pending With - '||a.APPRAISEE
,'SAVED',' Appraisal Pending With - '||a.APPRAISEE
,'TRANSFER','Appraisal Pending With - '||a.APPRAISEE
,'COMPLETED', 'Appraisal Completed'
,'PENDINGAPPR','Appraisal Pending With - '||A.MAIN_APPRAISER
,B.APPRAISAL_SYSTEM_STATUS ) ownership
FROM APPS.HRFV_APPRAISAL_DETAILS A
,PER_APPRAISALS B
,PER_ALL_PEOPLE_F C
WHERE C.PERSON_ID = A.APPRAISEE_PERSON_ID
AND A.APPRAISAL_ID = B.APPRAISAL_ID
AND A.appraisal_id in ( select max(e.appraisal_id) from per_appraisals e
where e.plan_id is null
group by e.appraisee_person_id
having count(*) >= 1)
AND TRUNC (SYSDATE) BETWEEN TRUNC (C.effective_start_date)
AND TRUNC (C.effective_end_date)
order by A.APPRAISEE