VIEW: APPS.HRFV_QUALIFICATIONS

Source

View Text - Preformatted

SELECT bgrT.name business_group_name ,peo.full_name person_name ,qat.title qualification_title ,qat.grade_attained grade_attained ,hr_bis.bis_decode_lookup('PER_SUBJECT_STATUSES',qua.status) qualification_status ,qua.awarded_date awarded_date ,qua.fee fee ,qua.fee_currency fee_currency ,qua.training_completed_amount training_completed_amount ,qat.reimbursement_arrangements reimbursement_arrangements ,qua.total_training_amount total_training_amount ,qat.training_completed_units training_completed_units ,qua.start_date qualification_start_date ,qua.end_date qualification_end_date ,qua.license_number license_number ,qua.expiry_date license_expiry_date ,qat.license_restrictions license_restrictions ,qua.projected_completion_date projected_completion_date ,qat.awarding_body awarding_body ,hr_bis.bis_decode_lookup('PER_TUITION_METHODS', qua.tuition_method) method_of_reimbursement ,qat.group_ranking group_ranking ,qua.comments comments ,nvl(est.name, att.establishment) school_or_college_name ,hr_bis.bis_decode_lookup('PER_CATEGORIES', qut.category) qualification_category ,qut.name qualification_type_name ,'_DF:PER:PER_QUALIFICATIONS:qua' ,qua.business_group_id business_group_id ,peo.person_id person_id ,est.establishment_id school_or_college_id ,qua.qualification_id qualification_id FROM per_qualification_types qut ,per_people_x peo ,per_establishment_attendances att ,per_establishments est ,per_qualifications qua ,per_qualifications_tl qat ,hr_all_organization_units_tl bgrT WHERE qua.qualification_type_id = qut.qualification_type_id AND qua.qualification_id = qat.qualification_id AND qat.language = userenv('LANG') AND qua.attendance_id = att.attendance_id AND att.person_id = peo.person_id (+) AND att.establishment_id = est.establishment_id(+) AND qua.business_group_id = bgrT.organization_id AND bgrT.language = userenv('LANG') AND qua.business_group_id = NVL(hr_bis.get_sec_profile_bg_id,qua.business_group_id) UNION ALL SELECT bgrT.name business_group_name ,peo.full_name person_name ,qat.title qualification_title ,qat.grade_attained grade_attained ,hr_bis.bis_decode_lookup('PER_SUBJECT_STATUSES', qua.status) qualification_status ,qua.awarded_date awarded_date ,qua.fee fee ,qua.fee_currency fee_currency ,qua.training_completed_amount training_completed_amount ,qat.reimbursement_arrangements reimbursement_arrangements ,qua.total_training_amount total_training_amount ,qat.training_completed_units training_completed_units ,qua.start_date qualification_start_date ,qua.end_date qualification_end_date ,qua.license_number license_number ,qua.expiry_date license_expiry_date ,qat.license_restrictions license_restrictions ,qua.projected_completion_date projected_completion_date ,qat.awarding_body awarding_body ,hr_bis.bis_decode_lookup('PER_TUITION_METHODS',qua.tuition_method) method_of_reimbursement ,qat.group_ranking group_ranking ,qua.comments comments ,null school_or_college_name ,hr_bis.bis_decode_lookup('PER_CATEGORIES', qut.category) qualification_category ,qut.name qualification_type_name ,'_DF:PER:PER_QUALIFICATIONS:qua' ,qua.business_group_id business_group_id ,peo.person_id person_id ,to_number(null) school_or_college_id ,qua.qualification_id qualification_id FROM per_qualification_types qut ,per_people_x peo ,per_qualifications qua ,per_qualifications_tl qat ,hr_all_organization_units_tl bgrT WHERE qua.qualification_type_id = qut.qualification_type_id AND qua.qualification_id = qat.qualification_id AND qat.language = userenv('LANG') AND qua.person_id = peo.person_id AND qua.business_group_id = bgrT.organization_id AND bgrT.language = userenv('LANG') AND qua.business_group_id = NVL(hr_bis.get_sec_profile_bg_id,qua.business_group_id) WITH READ ONLY

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, PEO.FULL_NAME PERSON_NAME
, QAT.TITLE QUALIFICATION_TITLE
, QAT.GRADE_ATTAINED GRADE_ATTAINED
, HR_BIS.BIS_DECODE_LOOKUP('PER_SUBJECT_STATUSES'
, QUA.STATUS) QUALIFICATION_STATUS
, QUA.AWARDED_DATE AWARDED_DATE
, QUA.FEE FEE
, QUA.FEE_CURRENCY FEE_CURRENCY
, QUA.TRAINING_COMPLETED_AMOUNT TRAINING_COMPLETED_AMOUNT
, QAT.REIMBURSEMENT_ARRANGEMENTS REIMBURSEMENT_ARRANGEMENTS
, QUA.TOTAL_TRAINING_AMOUNT TOTAL_TRAINING_AMOUNT
, QAT.TRAINING_COMPLETED_UNITS TRAINING_COMPLETED_UNITS
, QUA.START_DATE QUALIFICATION_START_DATE
, QUA.END_DATE QUALIFICATION_END_DATE
, QUA.LICENSE_NUMBER LICENSE_NUMBER
, QUA.EXPIRY_DATE LICENSE_EXPIRY_DATE
, QAT.LICENSE_RESTRICTIONS LICENSE_RESTRICTIONS
, QUA.PROJECTED_COMPLETION_DATE PROJECTED_COMPLETION_DATE
, QAT.AWARDING_BODY AWARDING_BODY
, HR_BIS.BIS_DECODE_LOOKUP('PER_TUITION_METHODS'
, QUA.TUITION_METHOD) METHOD_OF_REIMBURSEMENT
, QAT.GROUP_RANKING GROUP_RANKING
, QUA.COMMENTS COMMENTS
, NVL(EST.NAME
, ATT.ESTABLISHMENT) SCHOOL_OR_COLLEGE_NAME
, HR_BIS.BIS_DECODE_LOOKUP('PER_CATEGORIES'
, QUT.CATEGORY) QUALIFICATION_CATEGORY
, QUT.NAME QUALIFICATION_TYPE_NAME
, '_DF:PER:PER_QUALIFICATIONS:QUA'
, QUA.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PEO.PERSON_ID PERSON_ID
, EST.ESTABLISHMENT_ID SCHOOL_OR_COLLEGE_ID
, QUA.QUALIFICATION_ID QUALIFICATION_ID
FROM PER_QUALIFICATION_TYPES QUT
, PER_PEOPLE_X PEO
, PER_ESTABLISHMENT_ATTENDANCES ATT
, PER_ESTABLISHMENTS EST
, PER_QUALIFICATIONS QUA
, PER_QUALIFICATIONS_TL QAT
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
WHERE QUA.QUALIFICATION_TYPE_ID = QUT.QUALIFICATION_TYPE_ID
AND QUA.QUALIFICATION_ID = QAT.QUALIFICATION_ID
AND QAT.LANGUAGE = USERENV('LANG')
AND QUA.ATTENDANCE_ID = ATT.ATTENDANCE_ID
AND ATT.PERSON_ID = PEO.PERSON_ID (+)
AND ATT.ESTABLISHMENT_ID = EST.ESTABLISHMENT_ID(+)
AND QUA.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND QUA.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, QUA.BUSINESS_GROUP_ID) UNION ALL SELECT BGRT.NAME BUSINESS_GROUP_NAME
, PEO.FULL_NAME PERSON_NAME
, QAT.TITLE QUALIFICATION_TITLE
, QAT.GRADE_ATTAINED GRADE_ATTAINED
, HR_BIS.BIS_DECODE_LOOKUP('PER_SUBJECT_STATUSES'
, QUA.STATUS) QUALIFICATION_STATUS
, QUA.AWARDED_DATE AWARDED_DATE
, QUA.FEE FEE
, QUA.FEE_CURRENCY FEE_CURRENCY
, QUA.TRAINING_COMPLETED_AMOUNT TRAINING_COMPLETED_AMOUNT
, QAT.REIMBURSEMENT_ARRANGEMENTS REIMBURSEMENT_ARRANGEMENTS
, QUA.TOTAL_TRAINING_AMOUNT TOTAL_TRAINING_AMOUNT
, QAT.TRAINING_COMPLETED_UNITS TRAINING_COMPLETED_UNITS
, QUA.START_DATE QUALIFICATION_START_DATE
, QUA.END_DATE QUALIFICATION_END_DATE
, QUA.LICENSE_NUMBER LICENSE_NUMBER
, QUA.EXPIRY_DATE LICENSE_EXPIRY_DATE
, QAT.LICENSE_RESTRICTIONS LICENSE_RESTRICTIONS
, QUA.PROJECTED_COMPLETION_DATE PROJECTED_COMPLETION_DATE
, QAT.AWARDING_BODY AWARDING_BODY
, HR_BIS.BIS_DECODE_LOOKUP('PER_TUITION_METHODS'
, QUA.TUITION_METHOD) METHOD_OF_REIMBURSEMENT
, QAT.GROUP_RANKING GROUP_RANKING
, QUA.COMMENTS COMMENTS
, NULL SCHOOL_OR_COLLEGE_NAME
, HR_BIS.BIS_DECODE_LOOKUP('PER_CATEGORIES'
, QUT.CATEGORY) QUALIFICATION_CATEGORY
, QUT.NAME QUALIFICATION_TYPE_NAME
, '_DF:PER:PER_QUALIFICATIONS:QUA'
, QUA.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PEO.PERSON_ID PERSON_ID
, TO_NUMBER(NULL) SCHOOL_OR_COLLEGE_ID
, QUA.QUALIFICATION_ID QUALIFICATION_ID
FROM PER_QUALIFICATION_TYPES QUT
, PER_PEOPLE_X PEO
, PER_QUALIFICATIONS QUA
, PER_QUALIFICATIONS_TL QAT
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
WHERE QUA.QUALIFICATION_TYPE_ID = QUT.QUALIFICATION_TYPE_ID
AND QUA.QUALIFICATION_ID = QAT.QUALIFICATION_ID
AND QAT.LANGUAGE = USERENV('LANG')
AND QUA.PERSON_ID = PEO.PERSON_ID
AND QUA.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND QUA.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, QUA.BUSINESS_GROUP_ID) WITH READ ONLY