DBA Data[Home] [Help]

APPS.OTA_COMPETENCE_SS SQL Statements

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

Line: 190

select oev.course_end_date
from ota_events oev,
ota_offerings off, ota_category_usages ocu
where oev.event_id=p_id
and (oev.parent_offering_id = off.offering_id or oev.offering_id = off.offering_id)
and off.delivery_mode_id = ocu.category_usage_id
and ocu.synchronous_flag = 'Y';
Line: 197

/*select pce.effective_date_from
	from per_competence_elements pce , ota_offerings off,ota_events oev
	where oev.parent_offering_id=off.offering_id
	and off.activity_version_id=pce.activity_version_id
	and oev.event_id= p_id
	and pce.competence_id =p_comp_id
	and type='DELIVERY'; */
Line: 208

select pce.effective_date_from
	from per_competence_elements pce
	where pce.object_id= p_id
	and pce.competence_id =p_comp_id
	and type='OTA_LEARNING_PATH';*/
Line: 221

        select trunc(sysdate) into l_eff_date from dual;
Line: 225

   select trunc(sysdate) into l_eff_date from dual;
Line: 279

  select business_group_id from per_all_people_f
  where person_id= p_person_id
  and trunc(sysdate) between trunc(effective_start_date) and trunc(nvl(effective_end_date,sysdate+1));
Line: 323

      hr_transaction_api.delete_transaction_step(p_validate                    => false
        					,p_transaction_step_id         => l_old_transaction_step_id
        					,p_person_id                   => p_person_id
       						,p_object_version_number       => l_old_object_version_number);
Line: 459

hr_transaction_api.update_transaction
            (p_transaction_id             => l_transaction_id
          --  ,p_status                     => lv_status
            ,p_transaction_state          => null
           -- ,p_transaction_effective_date => ld_trans_effec_date
            );
Line: 482

                             p_message_name => 'HR_UPDATE_NOT_ALLOWED');
Line: 500

PROCEDURE Update_competence  ( itemtype		IN WF_ITEMS.ITEM_TYPE%TYPE,
		      itemkey		IN WF_ITEMS.ITEM_KEY%TYPE,
		      actid		IN NUMBER,
	   	      funcmode		IN VARCHAR2,
		      resultout		OUT nocopy VARCHAR2 )
is

      l_transaction_step_id 	number(15);
Line: 513

hr_utility.set_location('ENTERING Update Competence', 10);
Line: 555

    hr_utility.set_location('ERROR Update Competence', 10);
Line: 576

    hr_transaction_api.update_transaction(
      p_transaction_id => hr_transaction_ss.get_transaction_id
                          (p_item_type => itemtype
                          ,p_item_key => itemkey),
                          p_status => 'E');
Line: 586

end Update_competence;
Line: 592

PROCEDURE check_Update_competence  ( itemtype		IN WF_ITEMS.ITEM_TYPE%TYPE,
		      itemkey		IN WF_ITEMS.ITEM_KEY%TYPE,
		      actid		IN NUMBER,
	   	      funcmode		IN VARCHAR2,
		      resultout		OUT nocopy VARCHAR2 )
is

      l_transaction_step_id 	number(15);
Line: 605

hr_utility.set_location('ENTERING check Update Competence', 10);
Line: 645

    hr_utility.set_location('ERROR Update Competence', 10);
Line: 666

    hr_transaction_api.update_transaction(
      p_transaction_id => hr_transaction_ss.get_transaction_id
                          (p_item_type => itemtype
                          ,p_item_key => itemkey),
                          p_status => 'E');
Line: 677

end check_Update_competence;
Line: 704

SELECT WAAV.TEXT_VALUE Value
FROM WF_ACTIVITY_ATTR_VALUES WAAV
WHERE WAAV.PROCESS_ACTIVITY_ID = actid
AND WAAV.NAME = 'HR_APPROVAL_REQ_FLAG';
Line: 710

select competency_update_level
from ota_certifications_b
where certification_id = l_id;
Line: 715

select competency_update_level
from ota_learning_paths
where learning_path_id = l_id;
Line: 720

select oav.competency_update_level,off.competency_update_level
from ota_activity_versions oav,ota_offerings off,ota_events oev
where oav.activity_version_id = off.activity_version_id
and off.offering_id = oev.parent_offering_id
and
oev.event_id = l_id;
Line: 729

SELECT paf.ASSIGNMENT_ID
FROM per_all_assignments_f paf
WHERE  paf.person_id         = p_person_id
AND  TRUNC(SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date
AND paf.primary_flag ='Y'
AND paf.assignment_type in ('E','A', 'C');
Line: 762

          l_item_value:='NOTIFYUPDATE';
Line: 808

              elsif l_item_value = 'NOTIFYUPDATE' then

                   resultout:='COMPLETE:NOTIFYUPDATE';
Line: 858

select INSTR(inString,'^',startPos) into l_posFound from dual;
Line: 861

    select SUBSTR(inString,startPos,((length(inString)+1)-startPos)) into l_value from dual;
Line: 863

	select SUBSTR(inString,startPos,(l_posFound-startPos)) into l_value from dual;
Line: 961

 Select Proficiency_level_id
 from per_competence_elements
 where Competence_id = CompId
 and person_id = personId;
Line: 1272

procedure validate_competence_update
 (p_item_type     in varchar2,
  p_item_key      in varchar2,
  p_message out nocopy varchar2) is

  l_transaction_step_id 	number(15);
Line: 1299

end validate_competence_update;
Line: 1312

 Select Proficiency_level_id
 from per_competence_elements
 where Competence_id = CompId
 and person_id = personId;
Line: 1386

 return 'NOUPDATE';
Line: 1389

 return 'UPDATE';
Line: 1453

l_will_comp_update varchar2(50);
Line: 1459

SELECT wrpv.display_name displayName
FROM   wf_runnable_processes_v wrpv
WHERE wrpv.item_type = p_itemtype
AND wrpv.process_name = p_process;
Line: 1466

Select user_id ,user_name
from
fnd_user
where employee_id=p_person_id;
Line: 1472

SELECT
 EMPLOYEE_ID
FROM
 FND_USER
WHERE
 user_id = l_creator_user_id ;
Line: 1480

SELECT
paf.ASSIGNMENT_ID
FROM    per_all_assignments_f paf
    WHERE  paf.person_id         = p_person_id
       AND  TRUNC(SYSDATE) BETWEEN
     paf.effective_start_date
    AND paf.effective_end_date
    AND paf.primary_flag ='Y'
    AND paf.assignment_type in ('E','A', 'C');
Line: 1492

    SELECT
    paf.ASSIGNMENT_ID
    FROM    per_all_assignments_f paf
        WHERE  paf.person_id         = p_person_id
        AND paf.primary_flag ='Y'
    AND paf.assignment_type in ('E','A', 'C')
    order by paf.effective_end_date;
Line: 1501

   Select ppf.full_name
   FROM per_all_people_f ppf
   where ppf.person_id = p_person_id
   AND  TRUNC(SYSDATE) BETWEEN ppf.effective_start_date
    AND ppf.effective_end_date;
Line: 1509

  SELECT asg.supervisor_id, per.full_name
    FROM per_all_assignments_f asg,
         per_all_people_f per
   WHERE asg.person_id = p_person_id
     AND per.person_id = asg.supervisor_id
     AND asg.primary_flag = 'Y'
     AND trunc(sysdate)
 BETWEEN asg.effective_start_date AND asg.effective_end_date
     AND trunc(sysdate)
 BETWEEN per.effective_start_date AND per.effective_end_date;
Line: 1521

 SELECT user_name
   FROM fnd_user
  WHERE employee_id= l_supervisor_id;
Line: 1526

  select oev.title,oav.version_name
from ota_Events_vl oev ,ota_activity_versions_tl oav
--,ota_offerings off
where
--oev.parent_offering_id=off.offering_id	and
    oev.activity_version_id= oav.activity_version_id
	and oev.event_id= p_eventid
    and Language= USERENV('LANG');
Line: 1536

    select name from ota_learning_paths_TL
    where learning_path_id =csr_lp_id
    and Language= USERENV('LANG');
Line: 1542

    Select 1 from
    per_all_people_f
    where person_id= p_person_id
    and (fnd_profile.value('OTA_HR_GLOBAL_BUSINESS_GROUP_ID') is not null or --Bug#6869342
        business_group_id = ota_general.get_business_group_id);
Line: 1550

  is select name from ota_certifications_tl
  where certification_id = p_certification_id
  and Language= USERENV('LANG');
Line: 1555

SELECT decode(instr(web_html_call, param), 0, '-1',
substr(substr(web_html_call, instr(web_html_call, param),
    (decode(instr(web_html_call, '&', instr(web_html_call, param), 1), 0, (length(web_html_call)+1),
      instr(web_html_call, '&', instr(web_html_call, param), 1))-instr(web_html_call, param))),
      instr(substr(web_html_call, instr(web_html_call, param),
    (decode(instr(web_html_call, '&', instr(web_html_call, param), 1), 0, (length(web_html_call)+1),
      instr(web_html_call, '&', instr(web_html_call, param), 1))-instr(web_html_call, param))), '=')+1)) "paramvalue"
FROM fnd_form_functions
WHERE function_name = 'OTA_LEARNER_HOME_SS';
Line: 1631

l_will_comp_update := chk_comp_level(l_comp_ids,l_level_ids,p_person_id);
Line: 1685

  select hr_workflow_item_key_s.nextval
  into   l_item_key
  from   sys.dual;
Line: 1914

    l_func:='OTA_ADMIN_COMPETENCE_UPDATE';
Line: 1965

	SELECT pce.competence_id CompetenceId
	               ,nvl(pce.proficiency_level_id,-1) LevelId
	FROM per_competence_elements pce
		   , ota_offerings OFF
		   , ota_events EVT
       , per_competences pc
	WHERE off.activity_version_id = pce.activity_version_id
		AND evt.parent_offering_id = off.offering_id
		AND evt.event_id = p_event_id
    and pce.competence_id = pc.competence_id
    and trunc(sysdate) between pc.DATE_FROM and nvl(pc.DATE_TO,(sysdate+1));
Line: 1980

SELECT
pc.competence_id,
pc.renewal_period_frequency,
pc.renewal_period_units
FROM
per_competences pc
where
pc.competence_id = p_competence_id
AND pc.renewal_period_frequency IS NOT NULL
--AND pc.renewal_period_units IS NOT NULL  modified for bug8410902
AND pc.renewal_period_units IN ('Y','D','H','MIN','M','Q','W');
Line: 1993

 Select nvl(fnd_profile.value('OTA_ALLOW_COMPETENCY_UPDATE_WITH_RENEWAL_PERIOD'),'N') from dual;
Line: 1996

	SELECT pce.competence_id CompetenceId
	       ,nvl(pce.proficiency_level_id,-1) LevelId
	FROM per_competence_elements pce
       , per_competences pc
	WHERE pce.type = 'OTA_LEARNING_PATH'
		AND pce.object_id = l_learning_path_id
    AND pce.competence_id = pc.competence_id
    AND trunc(sysdate) between pc.DATE_FROM and nvl(pc.DATE_TO,(sysdate+1));
Line: 2006

	SELECT pce.competence_id CompetenceId
	       ,nvl(pce.proficiency_level_id,-1) LevelId
	FROM per_competence_elements pce
       , per_competences pc
	WHERE pce.type = 'OTA_CERTIFICATION'    ---Batra to revisit*********************
		AND pce.object_id = p_certification_id
    AND pce.competence_id = pc.competence_id
    AND trunc(sysdate) between pc.DATE_FROM and nvl(pc.DATE_TO,(sysdate+1));
Line: 2016

  select cenr.expiration_date
  from ota_cert_enrollments cenr
  where certification_id = p_certification_id
  and person_id = p_person_id;