[Home] [Help]
2052: l_event_type ota_events.event_type%type;
2053: l_parent_event ota_events.event_id%type;
2054: l_event_id ota_events.event_id%type;
2055: --l_language_id ota_events.language_id%type; -- 2733966
2056: l_competence_id per_competence_elements.competence_id%type;
2057: l_proficiency_level_id per_competence_elements.proficiency_level_id%type;
2058: l_step_value number := null;
2059: l_language_code ota_offerings.language_code%type;
2060:
2053: l_parent_event ota_events.event_id%type;
2054: l_event_id ota_events.event_id%type;
2055: --l_language_id ota_events.language_id%type; -- 2733966
2056: l_competence_id per_competence_elements.competence_id%type;
2057: l_proficiency_level_id per_competence_elements.proficiency_level_id%type;
2058: l_step_value number := null;
2059: l_language_code ota_offerings.language_code%type;
2060:
2061: cursor c_resource
2069: IS
2070: SELECT pce.competence_id ,
2071: nvl(pce.proficiency_level_id,0) proficiency_level_id ,
2072: nvl(rat.step_value,0) step_value
2073: FROM per_competence_elements pce, per_rating_levels rat
2074: WHERE nvl(pce.effective_date_from, p_required_date_from ) <= p_required_date_from --bug 3332972
2075: AND nvl(pce.effective_date_to,p_required_date_to) >= p_required_date_to --bug 3332972
2076: AND rat.rating_level_id = pce.proficiency_level_id
2077: AND pce.type = 'OTA_OFFERING' --bug 3494404
2156: if l_trainer_id is not null then
2157: For b in c_trainer_comp(l_event_id)
2158: LOOP
2159: if l_competence is null then
2160: l_competence := ' select pce.person_id from per_competence_elements pce ,' ||
2161: ' per_rating_levels rat '||
2162: ' where ' ||
2163: ' pce.competence_id = '||b.competence_id ||
2164: ' and rat.rating_level_id = pce.proficiency_level_id ' ||
2170: else
2171:
2172: l_competence := l_competence || ' AND ' ||
2173: ' pce.person_id in ('||
2174: ' select pce.person_id from per_competence_elements pce , '||
2175: ' per_rating_levels rat ' ||
2176: ' where' ||
2177: ' pce.competence_id = '||b.competence_id ||
2178: ' and pce.effective_date_from <= '''||p_required_date_from||
2189:
2190: For lang_comp in c_comp_lang(l_language_code)
2191: LOOP
2192: if l_competence is null then
2193: l_competence := ' select pce.person_id from per_competence_elements pce, '||
2194: ' per_rating_levels rat ' ||
2195: ' where' ||
2196: ' pce.competence_id = '||lang_comp.competence_id ||
2197: ' and pce.effective_date_from <= '''||p_required_date_from||
2204: else
2205:
2206: l_competence := l_competence || ' AND ' ||
2207: ' pce.person_id in ('||
2208: ' select person_id from per_competence_elements pce , '||
2209: ' per_rating_levels rat ' ||
2210: ' where' ||
2211: ' pce.competence_id = '||lang_comp.competence_id ||
2212: ' and pce.effective_date_from <= '''||p_required_date_from||
2226:
2227: For e in c_wo_level(l_language_code)
2228: LOOP
2229: if l_competence is null then
2230: l_competence := ' select pce.person_id from per_competence_elements pce ' ||
2231: ' where ' ||
2232: ' pce.competence_id = '||e.competence_id ||
2233: ' and pce.effective_date_from <= '''||p_required_date_from||
2234: ''' and NVL(pce.effective_date_to,'''||p_end_of_time||''') >= '''||p_required_date_to||
2238: else
2239:
2240: l_competence := l_competence || ' AND ' ||
2241: ' pce.person_id in ('||
2242: ' select pce.person_id from per_competence_elements pce '||
2243: ' where' ||
2244: ' pce.effective_date_from <= '''||p_required_date_from||
2245: ''' and NVL(pce.effective_date_to,'''||p_end_of_time||''') >= '''||p_required_date_to||
2246: ''' and pce.competence_id = '||e.competence_id || ')';