[Home] [Help]
196:
197: CURSOR c_qual_types(p_person PER_ALL_PEOPLE_F.PERSON_ID%TYPE) IS
198: SELECT QTYP.NAME,qtyp.rank
199: FROM PER_QUALIFICATION_TYPES QTYP,
200: PER_QUALIFICATIONS QUAL,
201: PER_ALL_PEOPLE_F ppf,
202: PER_ALL_PEOPLE_F ppf2
203: WHERE ppf.person_id = p_person
204: AND ppf.party_id = ppf2.party_id
211: CURSOR c_qual_types2(p_person PER_ALL_PEOPLE_F.PERSON_ID%TYPE
212: ,p_max_rank number) IS
213: SELECT QTYP.NAME
214: FROM PER_QUALIFICATION_TYPES QTYP,
215: PER_QUALIFICATIONS QUAL,
216: PER_ESTABLISHMENT_ATTENDANCES ESTAB,
217: PER_ALL_PEOPLE_F ppf,
218: PER_ALL_PEOPLE_F ppf2
219: WHERE ppf.person_id = p_person
260:
261: CURSOR c_qual_types IS
262: SELECT QTYP.NAME
263: FROM PER_QUALIFICATION_TYPES QTYP,
264: PER_QUALIFICATIONS QUAL,
265: PER_ALL_PEOPLE_F ppf
266: WHERE ppf.party_id = p_party_id
267: AND trunc(sysdate) BETWEEN ppf.effective_start_date and ppf.effective_end_date
268: AND ppf.person_id = QUAL.PERSON_ID
271:
272: CURSOR c_qual_types2 IS
273: SELECT /*+ FIRST_ROWS */ QTYP.NAME
274: FROM PER_QUALIFICATION_TYPES QTYP,
275: PER_QUALIFICATIONS QUAL,
276: PER_ESTABLISHMENT_ATTENDANCES ESTAB,
277: PER_ALL_PEOPLE_F ppf
278: WHERE ppf.party_id = p_party_id
279: AND trunc(sysdate) BETWEEN ppf.effective_start_date and ppf.effective_end_date
279: AND trunc(sysdate) BETWEEN ppf.effective_start_date and ppf.effective_end_date
280: AND ppf.person_id = ESTAB.PERSON_ID
281: AND estab.attendance_id=qual.attendance_id
282: AND QUAL.QUALIFICATION_TYPE_ID = QTYP.QUALIFICATION_TYPE_ID
283: and not exists (select 1 from per_qualifications qual2,per_qualification_types qtyp2
284: where qual2.person_id=ppf.person_id
285: and qtyp2.qualification_type_id=qual2.qualification_type_id
286: and nvl(qtyp2.rank,-1)>nvl(qtyp.rank,-1))
287: order by QTYP.RANK desc, qual.awarded_date desc, qual.creation_date desc;
797: IS
798: --
799: CURSOR c_qualifications IS
800: SELECT pqt.name
801: FROM per_qualifications pq
802: ,per_qualification_types_vl pqt
803: WHERE pq.party_id = p_party_id
804: AND pq.attendance_id IS NOT NULL
805: AND pq.awarded_date IS NOT NULL