APPS.HR_COMP_OUTCOME_PROFILE_SS SQL Statements

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

Line: 15

  select cvl.name
        ,cmpe.COMP_ELEMENT_OUTCOME_ID
        ,cmpe.object_version_number
        ,cmpe.COMPETENCE_ELEMENT_ID
        ,cmpe.OUTCOME_ID
        ,cmpe.date_from
        ,cmpe.date_to
 from   per_comp_element_outcomes  cmpe
        ,per_competence_outcomes_VL  cvl
  where  cmpe.comp_element_outcome_id = p_comp_element_outcome_id
  and    cmpe.outcome_id = cvl.outcome_id
  and    trunc(sysdate) between nvl(cmpe.date_from, trunc(sysdate))
         and nvl(cmpe.date_to, trunc(sysdate));

Line: 29

     select name
     from per_competence_outcomes_vl
     where outcome_id = p_outcome_id;

Line: 33

     select date_from
     from per_comp_element_outcomes
     where comp_element_outcome_id = p_comp_element_outcome_id;

Line: 37

    select varchar2_value
    from hr_api_transaction_values
    where transaction_step_id = p_transaction_step_id and name = 'P_CHANGE_MODE';

Line: 66

Procedure check_delete_rec(p_item_type  IN varchar2
                ,p_item_key   IN varchar2
                ,p_actid      IN varchar2
                ,p_person_id  IN number
                ,p_outcome_id IN number);

Line: 283

       SELECT object_version_number FROM per_comp_element_outcomes
       WHERE comp_element_outcome_id = p_comp_element_outcome_id;

Line: 429

Select ts.transaction_step_id
FROM hr_api_transaction_steps ts,
     hr_api_transaction_values tv,
     hr_api_transaction_values tv1,
     hr_api_transaction_values tv2,
     hr_api_transaction_values tv3,
     hr_api_transaction_values tv4
Where ts.transaction_step_id = tv.transaction_step_id
AND ts.item_type = p_item_type
AND ts.item_key = p_item_key
AND ts.activity_id = p_activity_id
AND ts.API_NAME = 'HR_COMP_OUTCOME_PROFILE_SS.PROCESS_API'
And tv4.transaction_step_id = ts.transaction_step_id
And tv4.NAME = 'P_PERSON_ID'
AND tv4.Number_Value = p_person_id
AND tv1.transaction_step_id(+) = ts.transaction_step_id
AND tv.NAME = 'P_COMPETENCE_ID'
AND tv.number_value = p_comp_id
AND tv1.NAME(+) = 'P_COMPETENCE_ELEMENT_ID'
AND tv1.number_value(+) = p_comp_ele_id
AND tv2.transaction_step_id = ts.transaction_step_id
AND tv2.NAME = 'P_DATE_FROM'
AND tv3.transaction_step_id = ts.transaction_step_id
AND tv3.NAME = 'P_DATE_TO';

Line: 456

      Select ceo.comp_element_outcome_id ,ts.transaction_step_id,ceo.object_version_number
      FROM
             per_comp_element_outcomes ceo,
             hr_api_transaction_steps ts,
             hr_api_transaction_values tv
      where
            ceo.competence_element_id = p_comp_ele_id
        AND ts.item_type = p_item_type
        AND ts.item_key = p_item_key
        AND ts.activity_id = p_activity_id
        AND ts.transaction_step_id = tv.transaction_step_id
        AND tv.NAME = 'P_COMP_ELEMENT_OUTCOME_ID'
        AND ts.API_NAME = 'HR_COMP_OUTCOME_PROFILE_SS.DELETE'
        AND tv.number_value = ceo.COMP_ELEMENT_OUTCOME_ID;

Line: 472

Select co.outcome_id
       ,ceo.DATE_FROM
       ,ceo.DATE_TO
       ,ceo.object_version_number
  From per_Competence_Outcomes co
       ,Per_comp_element_outcomes ceo
Where ceo.COMPETENCE_ELEMENT_ID = p_comp_ele_id
      AND co.outcome_id = ceo.outcome_id
      AND NOT EXISTS (Select 1 FROM hr_api_transaction_values tv1,
                               hr_api_transaction_values tv2,
                               hr_api_transaction_values tv3,
                               hr_api_transaction_values tv4,
                               hr_api_transaction_steps s
                  WHERE tv1.transaction_step_id = s.transaction_step_id
                                  and  s.item_type = p_item_type
                  and s.item_key = p_item_key
                  and s.activity_id = nvl(to_number(p_activity_id),s.activity_id)
                  and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.PROCESS_API'
                  And tv2.transaction_step_id = s.transaction_step_id
                  And tv3.transaction_step_id = s.transaction_step_id
                  AND tv4.transaction_step_id = s.transaction_step_id
                  AND tv4.NAME = 'P_PERSON_ID'
                  AND tv4.number_value = p_person_id
                  AND tv1.name = 'P_OUTCOME_ID'
                  AND tv1.number_value = co.OUTCOME_ID
                  AND tv2.name = 'P_DATE_FROM'
                                  AND tv3.name = 'P_DATE_TO'
                  AND tv2.date_Value >= co.date_from
     AND nvl(tv3.date_Value,trunc(sysdate)) <= nvl(co.date_to,nvl(tv3.date_Value,trunc(sysdate)))
and not exists (
Select 1 from per_comp_element_outcomes pco
         Where pco.competence_element_id = ceo.COMPETENCE_ELEMENT_ID
		 and pco.outcome_id = co.OUTCOME_ID
		 and pco.date_from = tv2.date_value
		 and nvl(pco.date_to,to_date('01-01-1001','DD-MM-YYYY')) = nvl(tv3.
		 date_value,to_date('01-01-1001','DD-MM-YYYY')))
	 )
     And NOT EXISTS (SELECT 1
      FROM hr_api_transaction_steps S1,
            hr_api_transaction_values C
     Where  s1.item_type = p_item_type
     and s1.item_key = p_item_key
     and s1.activity_id = nvl((p_activity_id),s1.activity_id)
     and s1.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.DELETE'
          and c.transaction_step_id = s1.transaction_step_id
          AND C.NAME = 'P_COMP_ELEMENT_OUTCOME_ID'
          AND ceo.comp_element_outcome_id = C.number_value);

Line: 520

       Select ce.Effective_date_From,ce.Effective_date_to
       From per_competence_elements ce
       Where ce.COMPETENCE_ELEMENT_ID = p_new_comp_ele_id;

Line: 523

/*      Select ceo.outcome_id,ceo.date_from,ceo.date_to,
             ce.Effective_date_From,ce.Effective_date_to, ceo.object_version_number
             From per_comp_element_outcomes ceo,
                  per_competence_elements ce
             Where ceo.competence_element_id = p_comp_ele_id
             AND   ce.competence_element_id = p_new_comp_ele_id;*/

Line: 563

       delete_transaction_step_id(outids.transaction_step_id);

Line: 674

         Select COMPETENCE_ELEMENT_ID ,
                OUTCOME_ID,
                DATE_FROM,
                DATE_TO
         FROM per_comp_element_outcomes
         Where comp_element_outcome_id = p_comp_element_outcome_id;

Line: 749

  hr_utility.set_location(' Entering: Inserting new record' || l_proc,30);

Line: 941

    select date_to end_date,
           date_from start_date
    from   per_competence_outcomes_vl
    where  outcome_id = p_outcome_id;

Line: 989

        delete_transaction_step_id(p_transaction_step_id);

Line: 1071

     Select count(*) INTO x_count
       from per_comp_element_outcomes ceo
       where competence_element_id = x_comp_ele_id
             and outcome_id = p_outcome_id and
             ceo.date_from <= to_date(p_date_from,g_date_format) and
             nvl(ceo.date_to,trunc(sysdate)) >= to_date(p_date_from,g_date_format)
             AND NOT exists(
             Select 1 FROM
             hr_api_transaction_steps S, hr_api_transaction_values C
                Where  s.item_type = p_item_type
                and s.item_key = p_item_key
                and s.activity_id = nvl(to_number(p_activity_id),s.activity_id)
                and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.DELETE'
                and c.transaction_step_id = s.transaction_step_id
                AND C.NAME = 'P_COMP_ELEMENT_OUTCOME_ID'
                AND C.NUMBER_VALUE = CEO.COMP_ELEMENT_OUTCOME_ID);

Line: 1096

  Select count(*) INTO x_count
  from per_comp_element_outcomes ceo
  where competence_element_id = x_comp_ele_id
        and outcome_id = p_outcome_id and
        ceo.date_from >= to_date(p_date_from,g_date_format)
             AND NOT exists(
             Select 1 FROM
             hr_api_transaction_steps S, hr_api_transaction_values C
                Where  s.item_type = p_item_type
                and s.item_key = p_item_key
                and s.activity_id = nvl(to_number(p_activity_id),s.activity_id)
                and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.DELETE'
                and c.transaction_step_id = s.transaction_step_id
                AND C.NAME = 'P_COMP_ELEMENT_OUTCOME_ID'
                AND C.NUMBER_VALUE = CEO.COMP_ELEMENT_OUTCOME_ID);

Line: 1113

  Select count(*) INTO x_count
  from per_comp_element_outcomes ceo
  where competence_element_id = x_comp_ele_id
        and outcome_id = p_outcome_id and
        ceo.date_from >= to_date(p_date_from,g_date_format)
        AND ceo.date_from <= to_date(p_date_to,g_date_format)
             AND NOT exists(
             Select 1 FROM
             hr_api_transaction_steps S, hr_api_transaction_values C
                Where  s.item_type = p_item_type
                and s.item_key = p_item_key
                and s.activity_id = nvl(to_number(p_activity_id),s.activity_id)
                and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.DELETE'
                and c.transaction_step_id = s.transaction_step_id
                AND C.NAME = 'P_COMP_ELEMENT_OUTCOME_ID'
                AND C.NUMBER_VALUE = CEO.COMP_ELEMENT_OUTCOME_ID);

Line: 1151

   delete_transaction_step_id(p_transaction_step_id);

Line: 1172

check_delete_rec(p_item_type => p_item_type
                ,p_item_key => p_item_key
                ,p_actid    => p_activity_id
                ,p_person_id => p_person_id
                ,p_outcome_id => p_outcome_id);

Line: 1194

  select step.transaction_step_id
  from hr_api_transaction_steps step, hr_api_transaction_values val
  where step.transaction_id = p_transaction_id
    and val.transaction_step_id = step.transaction_step_id
    and val.varchar2_value = 'ADD';

Line: 1217

  select steps.transaction_step_id
    from hr_api_transaction_values val, hr_api_transaction_steps steps
   where steps.transaction_id = p_transaction_id
     and steps.transaction_step_id = val.transaction_step_id
     and val.name = 'P_CHANGE_MODE'
     and val.varchar2_value  IN ('UPDATE_APPLY','UPDATE_HIST');

Line: 1230

       delete from hr_api_transaction_values
        where transaction_step_id = I.transaction_step_id;

Line: 1232

       delete from hr_api_transaction_steps
        where transaction_step_id = I.transaction_step_id;

Line: 1242

  select steps.transaction_step_id
    from hr_api_transaction_values val, hr_api_transaction_steps steps
   where steps.transaction_id = p_transaction_id
     and steps.transaction_step_id = val.transaction_step_id
     and val.varchar2_value = 'ADD';

Line: 1254

       delete from hr_api_transaction_values
        where transaction_step_id = I.transaction_step_id;

Line: 1256

       delete from hr_api_transaction_steps
        where transaction_id = l_transaction_id
          and transaction_step_id = I.transaction_step_id;

Line: 1319

PROCEDURE delete_all_ids
          (p_item_type in varchar2
          ,p_item_key  in varchar2) is
cursor get_all_ids (p_transaction_id number) is
 select transaction_step_id
   from hr_api_transaction_steps
  where transaction_id = p_transaction_id;

Line: 1336

       delete from hr_api_transaction_values
        where transaction_step_id = l_result.transaction_step_id;

Line: 1339

       delete from hr_api_transaction_steps
        where transaction_id = l_transaction_id
          and transaction_step_id = l_result.transaction_step_id;

Line: 1345

end delete_all_ids;

Line: 1348

PROCEDURE delete_transaction_step_id
          (p_transaction_step_id IN number) is
l_transaction_step_id  number;

Line: 1356

CURSOR get_txn_step_id IS  select transaction_id
    from hr_api_transaction_steps
    where transaction_step_id = p_transaction_step_id
    and rownum = 1;

Line: 1365

        delete from hr_api_transaction_values where transaction_step_id = l_transaction_step_id ;

Line: 1366

        delete from hr_api_transaction_steps  where transaction_step_id = l_transaction_step_id ;

Line: 1369

END delete_transaction_step_id;

Line: 1374

procedure save_update_details
          (p_item_type                  in varchar2
          ,p_item_key                   in varchar2
          ,p_activity_id                in varchar2
          ,p_outcome_id                 in number
          ,p_competence_element_id      in number default null
          ,p_comp_element_outcome_id    in number default null
          ,p_date_from                  in varchar2 default null
          ,p_date_to                    in varchar2 default null
          ,p_preupd_obj_vers_num        in number default null
          ,p_transaction_step_id        in number
          ,p_prev_date_from         in varchar2 default null
          ,p_pre_date_to            in varchar2 default null
          ,p_person_id              IN VARCHAR2 DEFAULT null
          ,p_error_message              out nocopy long) is
--
--
l_user_date_format      varchar2(20) ;

Line: 1407

    l_proc := g_package || 'save_update_details';

Line: 1497

end save_update_details;

Line: 1502

Procedure delete_add_page
          (p_transaction_step_id in number) is
--
CURSOR get_step_ids (txn_step_id IN number) is
   Select outcome.transaction_step_id
      FROM hr_api_transaction_steps outcome,
          hr_api_transaction_values ocomp,
          hr_api_transaction_values operson,
          hr_api_transaction_values ofrom_dt,
          hr_api_transaction_values oto_dt,
          hr_api_transaction_steps competence,
          hr_api_transaction_values ccomp,
          hr_api_transaction_values cperson,
          hr_api_transaction_values cfrom_dt,
          hr_api_transaction_values cto_dt
Where competence.transaction_step_id = txn_step_id
      AND ccomp.transaction_step_id = competence.transaction_step_id
      AND cperson.transaction_step_id = competence.transaction_step_id
      AND cfrom_dt.transaction_step_id = competence.transaction_step_id
      AND cto_dt.transaction_step_id = competence.transaction_step_id
      AND ccomp.NAME = 'P_COMPETENCE_ID'
      AND cperson.NAME = 'P_PERSON_ID'
      AND cfrom_dt.NAME = 'P_EFF_DATE_FROM'
      AND cto_dt.NAME = 'P_EFF_DATE_TO'
      And outcome.item_key = competence.item_key
      And outcome.item_type = competence.item_type
      And outcome.activity_id = competence.activity_id
      And outcome.api_name = HR_COMP_OUTCOME_PROFILE_SS.g_api_name
      AND ocomp.transaction_step_id = competence.transaction_step_id
      AND operson.transaction_step_id = competence.transaction_step_id
      AND ofrom_dt.transaction_step_id = competence.transaction_step_id
      AND oto_dt.transaction_step_id = competence.transaction_step_id
      AND ocomp.NAME = 'P_COMPETENCE_ID'
      AND operson.NAME = 'P_PERSON_ID'
      AND ofrom_dt.NAME = 'P_EFF_DATE_FROM'
      AND oto_dt.NAME = 'P_EFF_DATE_TO'
      And ocomp.number_value = ccomp.number_value
      And operson.number_value = cperson.number_value
      AND ofrom_dt.date_value >= cfrom_dt.date_value
      AND nvl(oto_dt.date_value,trunc(sysdate)) <= nvl(cto_dt.date_value,trunc(sysdate));

Line: 1546

l_proc  := g_package || 'delete_add_page';

Line: 1551

  delete from hr_api_transaction_values where transaction_step_id = txnStepIds.transaction_step_id;

Line: 1552

  delete from hr_api_transaction_steps where transaction_step_id = txnStepIds.transaction_step_id;

Line: 1557

end delete_add_page;

Line: 1726

  SELECT comp_element_outcome_id
        ,competence_element_id
        ,outcome_id
        ,date_from
        ,date_to
  from  per_comp_element_outcomes
  where comp_element_outcome_id = p_comp_element_outcome_id;

Line: 1759

      ELSE  -- pre-update is null
         IF p_date_from is not null THEN
            l_changed := true;

Line: 1776

      ELSE  -- pre-update is null
         IF p_date_to is not null THEN
            l_changed := true;

Line: 1798

PROCEDURE mark_for_delete
          (p_item_type                in varchar2
          ,p_item_key                  in varchar2
          ,p_activity_id              in varchar2
          ,p_comp_element_outcome_id  in number
          ,p_transaction_step_id      in varchar2 default null
          ,p_error_message            OUT nocopy long ) IS
l_transaction_id NUMBER DEFAULT null;

Line: 1813

  l_proc  := g_package || 'mark_for_delete';

Line: 1839

                  ,p_api_name => 'HR_COMP_OUTCOME_PROFILE_SS.DELETE'
                  ,p_item_type => p_item_type
                  ,p_item_key => p_item_key
                      ,p_activity_id => p_activity_id
                      ,p_transaction_step_id => l_transaction_step_id
                  ,p_object_version_number => x_trans_ovn);

Line: 1859

                ,p_api_name => 'HR_COMP_OUTCOME_PROFILE_SS.DELETE'
                ,p_transaction_data => l_trans_tbl);

Line: 1861

END mark_for_delete;

Line: 1865

PROCEDURE check_delete_rec
                ( p_item_type IN varchar2
                ,p_item_key IN varchar2
                ,p_actid    IN varchar2
                ,p_person_id IN number
                ,p_outcome_id IN number) IS
l_txn_step_id   hr_api_transaction_steps.transaction_step_id%type;

Line: 1874

l_proc  := g_package || 'check_delete_rec';

Line: 1876

SELECT s.transaction_step_id INTO l_txn_step_id
       FROM hr_api_transaction_steps S,
            hr_api_transaction_values C,
            per_comp_element_outcomes ceo,
            per_competence_elements pce
                   Where  s.item_type = p_item_type
     and s.item_key = p_item_key
     and s.activity_id = nvl((p_actid),s.activity_id)
     and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.DELETE'
          and c.transaction_step_id = s.transaction_step_id
          AND C.NAME = 'P_COMP_ELEMENT_OUTCOME_ID'
          AND ceo.outcome_id = p_outcome_id
          AND ceo.Competence_Element_id = pce.competence_element_id
          AND pce.PERSON_ID = p_person_id
          AND ceo.comp_element_outcome_id = C.number_value;

Line: 1893

delete_transaction_step_id(l_txn_step_id);

Line: 1907

End check_delete_rec;

Line: 1929

Select Count(*) INTO l_count
  FROM hr_api_transaction_steps s, per_competence_outcomes_vl co,
      hr_api_transaction_values a, hr_api_transaction_values b,
      hr_api_transaction_values c, hr_api_transaction_values p
      Where  s.item_type = p_item_type
     and s.item_key = p_item_key
     and s.activity_id = nvl(p_activity_id,s.activity_id)
     and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.PROCESS_API'
     and c.transaction_step_id = s.transaction_step_id
     AND CO.COMPETENCE_ID =p_competence_id
     AND co.date_from <= trunc(sysdate)
     AND nvl(co.date_to,trunc(sysdate)) >= trunc(sysdate)
     AND b.date_value >= co.date_from
     AND nvl(c.date_Value,trunc(sysdate)) <= nvl(co.date_to, nvl(c.date_Value,trunc(sysdate)))
and a.name = 'P_OUTCOME_ID'
and a.transaction_step_id = s.transaction_step_id
and a.number_value= co.outcome_id
and b.name = 'P_DATE_FROM'
and b.transaction_step_id = s.transaction_step_id
and c.name = 'P_DATE_TO'
and p.transaction_step_id = s.transaction_step_id
and p.name = 'P_PERSON_ID'
and p.Number_Value = p_pid;

Line: 1957

SELECT COUNT(*) INTO l_count
       FROM hr_api_transaction_steps S,
            hr_api_transaction_values C,
            per_comp_element_outcomes ceo
     Where  s.item_type = p_item_type
     and s.item_key = p_item_key
     and s.activity_id = nvl((p_activity_id),s.activity_id)
     and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.DELETE'
          and c.transaction_step_id = s.transaction_step_id
          AND C.NAME = 'P_COMP_ELEMENT_OUTCOME_ID'
          AND ceo.Competence_Element_id = p_competence_element_id
          AND ceo.comp_element_outcome_id = C.number_value;

Line: 1979

Procedure delete(p_validate            in boolean default false
                     ,p_transaction_step_id in number
                     ,p_effective_date        in varchar2 default null) is
begin
null;

Line: 2002

     Select DISTINCT s.transaction_step_id
     FROM hr_api_transaction_steps S,
          hr_api_transaction_values C,
          per_comp_element_outcomes ceo
     Where  s.item_type = p_item_type
     and s.item_key = p_item_key
     and s.activity_id = nvl((p_activity_id),s.activity_id)
     and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.DELETE'
          and c.transaction_step_id = s.transaction_step_id
          AND C.NAME = 'P_COMP_ELEMENT_OUTCOME_ID'
          AND c.number_value = ceo.comp_element_outcome_id
          AND ceo.competence_element_id = p_competence_element_id;

Line: 2016

   Select DISTINCT s.transaction_step_id
     FROM hr_api_transaction_steps S,
          hr_api_transaction_values C
     Where s.item_type = p_item_type
     and s.item_key = p_item_key
     and s.activity_id = nvl((p_activity_id),s.activity_id)
     and s.api_name = 'HR_COMP_PROFILE_SS.PROCESS_API'
     AND c.transaction_step_id = s.transaction_step_id
     AND c.NAME = 'P_COMPETENCE_ELEMENT_ID'
     AND c.number_value = p_competence_element_id;

Line: 2028

   Select DISTINCT s.transaction_step_id
     FROM hr_api_transaction_steps S,
          hr_api_transaction_values C
     Where s.item_type = p_item_type
     and s.item_key = p_item_key
     and s.activity_id = nvl((p_activity_id),s.activity_id)
     and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.PROCESS_API'
     AND c.transaction_step_id = s.transaction_step_id
     AND c.NAME = 'P_COMPETENCE_ELEMENT_ID'
     AND c.number_value = p_competence_element_id;

Line: 2041

delete_transaction_step_id(sid_cur.transaction_step_id);

Line: 2046

delete_transaction_step_id(sid_comp_cur.transaction_step_id);

Line: 2050

delete_transaction_step_id(sid_out_cur.transaction_step_id);