eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OTA_CPR_UTILITY SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 13


 Select nvl(pce.proficiency_level_id, -100)
 from per_competence_elements pce, per_rating_levels prl
 where pce.competence_id = p_comp_id
 and pce.person_id = p_person_id
 and prl.rating_level_id(+) = pce.proficiency_level_id
 and prl.step_value >= (select step_value from per_rating_levels where rating_level_id = p_level_id)
 and (trunc(sysdate) between nvl(pce.effective_date_from, trunc(sysdate)) and nvl(pce.effective_date_to, trunc(sysdate)));

Line: 22


 Select nvl(pce.proficiency_level_id, -100)
 from per_competence_elements pce
 where pce.competence_id = p_comp_id
 and pce.person_id = p_person_id
 and (trunc(sysdate) between nvl(pce.effective_date_from, trunc(sysdate)) and nvl(pce.effective_date_to, trunc(sysdate)))
 and pce.proficiency_level_id is null;

Line: 30


 Select nvl(pce.proficiency_level_id, -100)
 from per_competence_elements pce
 where pce.competence_id = p_comp_id
 and pce.person_id = p_person_id
 and (trunc(sysdate) between nvl(pce.effective_date_from, trunc(sysdate)) and nvl(pce.effective_date_to, trunc(sysdate)));

Line: 84


SELECT
  Decode(PRL.STEP_VALUE, null, -1, PRL.STEP_VALUE),
  PCE.COMPETENCE_ELEMENT_ID
FROM
  PER_RATING_LEVELS PRL,
  PER_COMPETENCE_ELEMENTS PCE
WHERE
  PCE.PROFICIENCY_LEVEL_ID = PRL.RATING_LEVEL_ID (+)
  and (trunc(sysdate) between nvl(pce.effective_date_from, trunc(sysdate)) and nvl(pce.effective_date_to, trunc(sysdate)))
  AND PCE.type = 'DELIVERY'
  AND PCE.ACTIVITY_VERSION_ID = p_act_ver_id
  AND PCE.COMPETENCE_ID = p_comp_id
ORDER BY PRL.STEP_VALUE DESC;

Line: 99


SELECT
  PRL.STEP_VALUE
FROM
  PER_RATING_LEVELS PRL
WHERE
  PRL.RATING_LEVEL_ID = p_level_id;

Line: 108


SELECT MAX(STEP_VALUE)
FROM per_competence_levels_v
WHERE COMPETENCE_ID = p_comp_id;

Line: 112


 select MAX(step_value)
 from
   per_rating_levels prl
  ,per_competences pce
where (prl.rating_scale_id = pce.rating_scale_id
     OR pce.competence_id = prl.competence_id)
     AND pce.competence_id = p_comp_id;

Line: 122


SELECT MIN(STEP_VALUE)
FROM per_competence_levels_v
WHERE COMPETENCE_ID = p_comp_id;

Line: 126


 select MIN(step_value)
 from
   per_rating_levels prl
  ,per_competences pce
where (prl.rating_scale_id = pce.rating_scale_id
     OR pce.competence_id = prl.competence_id)
     AND pce.competence_id = p_comp_id;

Line: 213


SELECT
	oev.event_id,
	OCU.online_flag,
	OFR.learning_object_id
FROM
	ota_activity_versions OAV,
	ota_offerings OFR,
	ota_events OEV,
	ota_category_usages OCU
WHERE
	OFR.activity_version_id = OAV.activity_version_id
	And OEV.parent_offering_id = OFR.offering_id
	And OFR.delivery_mode_id = OCU.category_usage_id
	And OCU.type ='DM'
	And (OEV.event_type = 'SCHEDULED' or OEV.event_type = 'SELFPACED')
	And OEV.event_status <> 'A'
	And OAV.activity_version_id = p_act_ver_id;

Line: 232


SELECT lesson_status
FROM ota_performances
WHERE
	user_id = p_user_id
	AND user_type = p_user_type
	AND learning_object_id = p_lo_id;

Line: 278


select ocp.prerequisite_course_id
from ota_activity_versions oav,
     ota_course_prerequisites ocp
where ocp.activity_version_id = p_act_ver_id
      and ocp.enforcement_mode in ('B', p_enforcement_mode)
      and ocp.prerequisite_type = 'M'
      and ocp.prerequisite_course_id = oav.activity_version_id
      and (oav.end_date is null or trunc(oav.end_date) >= trunc(sysdate));

Line: 288


SELECT cpe.competence_id,
      cpe.proficiency_level_id
FROM per_competence_elements cpe
WHERE cpe.object_id = p_act_ver_id
	and cpe.mandatory = 'Y'
	and cpe.type = 'OTA_COMP_PREREQ';

Line: 338


select ocp.prerequisite_course_id
from ota_activity_versions oav,
     ota_course_prerequisites ocp
where ocp.activity_version_id = p_act_ver_id
      and ocp.enforcement_mode in ('B', p_enforcement_mode)
      and ocp.prerequisite_type = 'A'
      and ocp.prerequisite_course_id = oav.activity_version_id
      and (oav.end_date is null or trunc(oav.end_date) >= trunc(sysdate));

Line: 348


SELECT cpe.competence_id,
      cpe.proficiency_level_id
FROM per_competence_elements cpe
WHERE cpe.object_id = p_act_ver_id
	and ( cpe.mandatory = 'N' or cpe.mandatory is null )
	and cpe.type = 'OTA_COMP_PREREQ';

Line: 398


SELECT  activity_version_id
FROM 	ota_events
WHERE	event_id = p_event_id;

Line: 436


SELECT  ofr.activity_version_id
FROM 	ota_events evt, ota_offerings ofr
WHERE	evt.event_id = p_event_id
	and evt.parent_offering_id = ofr.offering_id;

Line: 442


select party.party_id
from  HZ_CUST_ACCOUNT_ROLES acct_role,
      HZ_PARTIES party,
      HZ_RELATIONSHIPS rel,
      HZ_ORG_CONTACTS org_cont,
      HZ_PARTIES rel_party,
      HZ_CUST_ACCOUNTS role_acct
where acct_role.party_id = rel.party_id
   and acct_role.role_type = 'CONTACT'
   and org_cont.party_relationship_id = rel.relationship_id
   and rel.subject_id = party.party_id
   and rel.party_id = rel_party.party_id
   and rel.subject_table_name = 'HZ_PARTIES'
   and rel.object_table_name = 'HZ_PARTIES'
   and acct_role.cust_account_id = role_acct.cust_account_id
   and role_acct.party_id	= rel.object_id
   and ACCT_ROLE.cust_account_role_id = p_delegate_contact_id;

Line: 500


SELECT cpr.prerequisite_course_id
FROM ota_course_prerequisites cpr
WHERE cpr.activity_version_id = p_act_ver_id
	and cpr.prerequisite_type = 'M'
	and cpr.enforcement_mode in ('B', p_enforcement_mode);

Line: 533


SELECT cpe.competence_id,
      cpe.proficiency_level_id
FROM per_competence_elements cpe
WHERE cpe.object_id = p_act_ver_id
	and cpe.mandatory = 'Y'
	and cpe.type = 'OTA_COMP_PREREQ';

Line: 562


select
  ota_cpr_utility.is_course_completed
    (odb.delegate_person_id
    ,odb.delegate_contact_id
    ,nvl(odb.delegate_person_id,odb.delegate_contact_id)
    ,decode(odb.delegate_person_id,null,'C', 'E')
    ,p_prerequisite_course_id
  ) prereq_met
from
  ota_delegate_bookings odb,
  ota_booking_status_types bst,
  ota_events evb
where
  odb.booking_status_type_id = bst.booking_status_type_id
  and evb.event_id = odb.event_id
  and evb.event_id = p_event_id;

Line: 605


select
  ota_cpr_utility.is_competency_acheived
    (odb.delegate_person_id
    ,p_comp_id
    ,p_level_id
  ) prereq_met
from
  ota_delegate_bookings odb,
  ota_events evb
where
  evb.event_id = odb.event_id
  and evb.event_id = p_event_id;

Line: 713


  SELECT activity_version_id
  FROM ota_events
  WHERE event_id = p_event_id;

Line: 737


  SELECT activity_version_id
  FROM ota_events
  WHERE event_id = p_event_id;

Line: 801


    select 'Y'
      from OTA_EVENTS oev
     where oev.ACTIVITY_VERSION_ID = p_prerequisite_course_id
           and (oev.EVENT_TYPE = 'SCHEDULED' or oev.EVENT_TYPE = 'SELFPACED')
	   and oev.EVENT_STATUS <> 'A'
	   and nvl(trunc(oev.course_end_date), trunc(sysdate)) >= trunc(sysdate);

Line: 844


  select oavt.version_name Dependant_Course_Name,
       ocp.prerequisite_type Prerequisite_Type,
       ocp.enforcement_mode Enforcement_Mode,
       ocp.activity_version_id Activity_Version_Id,
       ocp.activity_version_id Dependant_Course_Id,
       ocp.business_group_id Business_Group_Id
from ota_activity_versions oav,
     ota_activity_versions_tl oavt,
     ota_course_prerequisites ocp
where  ocp.activity_version_id = oav.activity_version_id
      and oav.activity_version_id = oavt.activity_version_id
      and oavt.language = userenv('LANG')
      and ocp.prerequisite_course_id = p_act_ver_id
      and ( (oav.END_DATE is null ) or (trunc(oav.END_DATE) > trunc(to_date(p_pre_req_crs_end_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')))) )
Order By Dependant_Course_Name desc;