APPS.HR_TRANSACTION_SS SQL Statements

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

Line: 7

g_update_object_version varchar2(30) := 'update_object_version';

Line: 29

  select user_id from fnd_user where user_name=contextUser;

Line: 31

   select name from wf_roles where UPPER(EMAIL_ADDRESS) = UPPER(substr (contextUser,  7)) and ORIG_SYSTEM = 'PER' and STATUS = 'ACTIVE';

Line: 105

       select count(name) into l_user_role_count from wf_roles where UPPER(EMAIL_ADDRESS) = UPPER(substr (contextUser,  7)) and ORIG_SYSTEM = 'PER' and STATUS = 'ACTIVE';

Line: 299

        select  employee_id into c_return_person from fnd_user where user_name = c_return_user ;

Line: 306

    hr_utility.trace('calling ame_api..updateApprovalStatus2 ');

Line: 315

	    ame_api.updateApprovalStatus2(applicationIdIn => c_application_id,
                                  transactionIdIn         => c_transaction_id,
                                  approvalStatusIn        => null,
                                  approverPersonIdIn      => c_all_approvers(i).person_id,
                                  approverUserIdIn        => null,
                                  transactionTypeIn       => c_transaction_type,
                                  forwardeeIn             => null);

Line: 526

  select item_type,item_key
    from hr_api_transaction_steps
   where transaction_step_id = p_transaction_step_id;

Line: 585

    hr_transaction_api.update_transaction(p_transaction_id => p_transaction_id,
                                        p_status => p_status );

Line: 617

procedure set_delete_save_for_later
 (itemtype in varchar2,
  itemkey      in varchar2,
  actid        in number,
  funmode      in varchar2,
  result       out nocopy varchar2 ) is
ln_transaction_id hr_api_transactions.transaction_id%TYPE;

Line: 624

l_proc constant varchar2(100) := g_package || ' set_delete_save_for_later';

Line: 642

end set_delete_save_for_later;

Line: 718

        select transaction_ref_table
        into l_transaction_ref_table
        from hr_api_transactions
        where transaction_id=ln_transaction_id;

Line: 798

    select transaction_id into l_transaction_id from hr_api_transactions where item_type = p_item_type and item_key = p_item_key;

Line: 808

    ,p_selected_person_id           in number
    ,p_process_name                 in varchar2
    ,p_status                       in varchar2
    ,p_section_display_name          in varchar2
    ,p_assignment_id                in number
    ,p_transaction_effective_date   in date
    ,p_transaction_type             in varchar2
    ,l_function_id                  in out nocopy hr_api_transactions.function_id%TYPE
    ,ln_selected_person_id          in out nocopy hr_api_transactions.selected_person_id%TYPE
    ,lv_process_name                in out nocopy hr_api_transactions.process_name%TYPE
    ,lv_status                      in out nocopy hr_api_transactions.status%TYPE
    ,lv_section_display_name        in out nocopy hr_api_transactions.section_display_name%TYPE
    ,ln_assignment_id               in out nocopy hr_api_transactions.assignment_id%TYPE
    ,ld_trans_effec_date            in out nocopy hr_api_transactions.transaction_effective_date%TYPE
    ,lv_transaction_type            in out nocopy hr_api_transactions.transaction_type%TYPE
    )
AS
        cursor get_function_info ( p_item_type HR_API_TRANSACTION_STEPS.item_type%TYPE
                              ,p_item_key HR_API_TRANSACTION_STEPS.item_key%TYPE ) is
        select fff.function_id, fff.function_name from
        fnd_form_functions_vl fff
        where fff.function_name = ( select iav.text_value
                                    from wf_item_attribute_values iav
                                    where iav.item_type = p_item_type
                                    and iav.item_key = p_item_key
                                    and iav.name = 'P_CALLED_FROM') ;

Line: 858

       If p_selected_person_id is  null then
          If p_item_type is not null and p_item_key is not null then
            ln_selected_person_id := wf_engine.GetItemAttrNumber(p_item_type,
                                        p_item_key,
                                        'CURRENT_PERSON_ID');

Line: 865

            ln_selected_person_id := p_selected_person_id;

Line: 947

  ,p_selected_person_id           in number default null
  ,p_transaction_effective_date       in date default null
  ,p_process_name                 in varchar2 default null
  ,p_plan_id                      in number default null
  ,p_rptg_grp_id                  in number default null
  ,p_effective_date_option        in varchar2 default null
  ,result         out nocopy  varchar2) is
  -- --------------------------------------------------------------------------
  -- declare local variables
  -- --------------------------------------------------------------------------
  l_transaction_privilege    hr_api_transactions.transaction_privilege%type;

Line: 961

  ln_selected_person_id   hr_api_transactions.selected_person_id%TYPE;

Line: 982

    ,p_selected_person_id
    ,p_process_name
    ,p_status
    ,p_section_display_name
    ,p_assignment_id
    ,p_transaction_effective_date
    ,p_transaction_type
    ,l_function_id
    ,ln_selected_person_id
    ,lv_process_name
    ,lv_status
    ,lv_section_display_name
    ,ln_assignment_id
    ,ld_trans_effec_date
    ,lv_transaction_type
    );

Line: 1080

               ,p_selected_person_id=>ln_selected_person_id
               ,p_item_type=>itemtype
               ,p_item_key=>itemkey
               ,p_transaction_effective_date=>ld_trans_effec_date
               ,p_process_name=>lv_process_name
               ,p_plan_id=>p_plan_id
               ,p_rptg_grp_id=>p_rptg_grp_id
               ,p_effective_date_option=>p_effective_date_option
               ,p_api_addtnl_info=>p_api_addtnl_info);

Line: 1136

     SELECT pa.appraisal_system_status
     FROM   per_appraisals pa,
            hr_api_transactions txn
     WHERE  txn.transaction_id = p_transaction_id
     AND    txn.transaction_ref_table = 'PER_APPRAISALS'
     AND    txn.transaction_ref_id  = pa.appraisal_id;

Line: 1143

  l_allow_delete VARCHAR2(1) := 'Y';

Line: 1159

           select hr_api_transactions.transaction_ref_table
            into lv_transaction_ref_table
            from hr_api_transactions
            where hr_api_transactions.transaction_id=(get_transaction_id(itemtype,itemkey));

Line: 1168

               IF NVL(l_appr_status,'OLD') NOT IN ('COMPLETED','APPRFEEDBACK','DELETED') THEN
                  l_allow_delete := 'N';

Line: 1188

        IF l_allow_delete = 'Y' THEN
          hr_transaction_api.rollback_transaction
                 (p_transaction_id => get_transaction_id
                             (p_item_type => itemtype
                             ,p_item_key  => itemkey));

Line: 1262

    select nvl(varchar2_value, hr_api.g_varchar2)          varchar2_value
          ,nvl(number_value, hr_api.g_number)              number_value
          ,nvl(date_value, hr_api.g_date)                  date_value
          ,nvl(original_varchar2_value, hr_api.g_varchar2) original_varchar2_value
          ,nvl(original_number_value, hr_api.g_number)     original_number_value
          ,nvl(original_date_value, hr_api.g_date)         original_date_value
    from   hr_api_transaction_values hatv
    where  hatv.transaction_step_id = p_transaction_step_id;

Line: 1396

  ,p_update_object_version in varchar2 default 'N'
  ,p_effective_date      in varchar2 default null) is
  -- --------------------------------------------------------------------------
  -- declare local variables
  -- --------------------------------------------------------------------------
  l_transaction_id      hr_api_transactions.transaction_id%type;

Line: 1412

    select trs.transaction_step_id
          ,trs.api_name
          ,trs.item_type
          ,trs.item_key
          ,trs.activity_id
          ,trs.creator_person_id
    from   hr_api_transaction_steps trs
    where  trs.transaction_id = l_transaction_id
    and trs.api_name <> 'HR_COMP_OUTCOME_PROFILE_SS.PROCESS_API' --#4110654
    and object_type is null
    order by trs.processing_order,trs.transaction_step_id ; --#2313279

Line: 1425

    select fff.parameters
      from fnd_form_functions fff, hr_api_transactions hat
     where fff.function_id = hat.function_id
       and hat.transaction_id = l_transaction_id;

Line: 1466

                      p_selected_person_id      => hr_transaction_swi.g_txn_ctx.SELECTED_PERSON_ID,
                      p_selected_assignment_id  => hr_transaction_swi.g_txn_ctx.ASSIGNMENT_ID,
                      p_effective_date          => hr_transaction_swi.g_txn_ctx.EFFECTIVE_DATE
                   );

Line: 1472

  hr_utility.set_location('select each transaction steps to process: '|| l_proc, 20);

Line: 1491

      if p_update_object_version = 'Y' then
        -- update object version for each step
        l_obj_api_name := substr(I.api_name,1, instr(I.api_name,'.'));

Line: 1494

        l_obj_api_name := l_obj_api_name || g_update_object_version;

Line: 1628

  ,p_update_object_version in varchar2 default 'N'
  ,p_result         out nocopy varchar2) is
l_proc constant varchar2(100) := g_package || '  validate_transaction';

Line: 1637

      ,p_update_object_version => p_update_object_version
      ,p_ignore_warnings  => 'N'
      ,p_result         => p_result );

Line: 1657

  ,p_update_object_version in varchar2 default 'N'
  ,p_ignore_warnings in varchar2 default 'N'
  ,p_result         out nocopy varchar2) is
  l_proc constant varchar2(100) := g_package || ' validate_transaction';

Line: 1670

        ,p_update_object_version => p_update_object_version
        ,p_effective_date  => p_effective_date
        ,p_ignore_warnings => p_ignore_warnings
        ,p_validate => false);

Line: 1859

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

Line: 1907

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

Line: 2026

                ,p_selected_person_id           in number default null
                ,p_transaction_effective_date       in date default null
                ,p_process_name                 in varchar2 default null
                ,p_plan_id                      in number default null
                ,p_rptg_grp_id                  in number default null
                ,p_effective_date_option        in varchar2 default null
) AS


cursor get_trans_id ( p_trans_step_id HR_API_TRANSACTION_STEPS.transaction_step_id%TYPE) is
        select transaction_id from hr_api_transaction_steps
        where transaction_step_id = p_trans_step_id;

Line: 2050

ln_selected_person_id   hr_api_transactions.selected_person_id%TYPE;

Line: 2069

    ,p_selected_person_id
    ,p_process_name
    ,p_status
    ,p_section_display_name
    ,p_assignment_id
    ,p_transaction_effective_date
    ,p_transaction_type
    ,l_function_id
    ,ln_selected_person_id
    ,lv_process_name
    ,lv_status
    ,lv_section_display_name
    ,ln_assignment_id
    ,ld_trans_effec_date
    ,lv_transaction_type
    );

Line: 2110

      ,p_selected_person_id  => ln_selected_person_id
      ,p_transaction_effective_date => ld_trans_effec_date
      ,p_process_name => lv_process_name
      ,p_plan_id=> p_plan_id
      ,p_rptg_grp_id=> p_rptg_grp_id
      ,p_effective_date_option=> p_effective_date_option
    );

Line: 2133

  hr_transaction_api.g_update_flag := 'N';

Line: 2216

  if (hr_transaction_api.g_update_flag = 'Y') then
	l_transaction_state := 'W';

Line: 2218

        hr_transaction_api.g_update_flag := 'N';

Line: 2246

            hr_transaction_api.update_transaction
            (p_transaction_id             => ln_transaction_id
            ,p_status                     => lv_status
            ,p_transaction_state          => l_transaction_state
            ,p_transaction_effective_date => ld_trans_effec_date
            );

Line: 2360

PROCEDURE delete_trn_step_by_act_name(
    p_item_type     IN varchar2,
    p_item_key      IN varchar2 ,
    p_actid         IN varchar2 ,
    p_activity_name IN varchar2,
    p_login_person_id IN varchar2 )
IS
    ln_ovn NUMBER ;

Line: 2374

l_proc constant varchar2(100) := g_package || ' delete_trn_step_by_act_name';

Line: 2403

        delete_transaction_step(
          p_transaction_step_id => ln_transaction_step_id,
          p_login_person_id => p_login_person_id);

Line: 2415

END delete_trn_step_by_act_name;

Line: 2422

PROCEDURE delete_transaction_steps(
  p_item_type IN     varchar2,
  p_item_key  IN     varchar2,
  p_actid     IN     varchar2 default null,
  p_login_person_id  IN varchar2) IS

  l_trans_step_ids hr_util_web.g_varchar2_tab_type;

Line: 2431

l_proc constant varchar2(100) := g_package || ' delete_transaction_steps';

Line: 2455

    delete_transaction_step
      (p_transaction_step_id => l_trans_step_ids(i)
      ,p_object_version_number => l_trans_obj_vers_num(i)
      ,p_login_person_id => p_login_person_id);

Line: 2470

END delete_transaction_steps;

Line: 2477

PROCEDURE delete_transaction_step(
  p_transaction_step_id IN varchar2,
  p_object_version_number IN varchar2 default null,
  p_login_person_id  IN varchar2) IS

  l_object_version_number number;

Line: 2483

l_proc constant varchar2(100) := g_package || ' delete_transaction_step';

Line: 2516

    if L_DEL_PHONE_ID is not null and L_DEL_PHONE_TYPE = 'DELETE' and L_DEL_PHONE_NUMBER = 'DELETE_NUMBER' then
       null;

Line: 2521

    hr_transaction_api.delete_transaction_step
      (p_validate => FALSE
      ,p_transaction_step_id => to_number(p_transaction_step_id)
      ,p_object_version_number => l_object_version_number
      ,p_person_id => to_number(p_login_person_id));

Line: 2537

END delete_transaction_step;

Line: 2544

    select hats.object_version_number
    from   hr_api_transaction_steps hats
    where  hats.transaction_step_id = p_transaction_step_id;

Line: 2701

    select val.varchar2_value, val.transaction_step_id
    from  hr_api_transaction_values val, hr_api_transaction_steps step
    where step.item_type = p_item_type
      and step.item_key  = p_item_key
      and step.transaction_step_id = val.transaction_step_id
      and val.name = p_name
      and val.varchar2_value Is Not Null
    order by step.processing_order, step.transaction_step_id asc;

Line: 2715

   select varchar2_value
   from   hr_api_transaction_values
   where  transaction_step_id =  p_transaction_step_id
   and name = p_name
   and varchar2_value Is Not Null;

Line: 2726

   select count(transaction_step_id)
     into l_tx_step_count
     from  hr_api_transaction_steps
    where  item_key = p_item_key
      and  item_type = p_item_type;

Line: 2807

        p_review_regions := 'NO_REVIEW_PROC_CALL';  -- Update page has not stored the P_REVIEW_PROC_CALL or P_REVIEW_ACTID.

Line: 2829

    select NAME,
           VARCHAR2_VALUE,
           NUMBER_VALUE,
           DATE_VALUE
    from   hr_api_transaction_values
    where  transaction_step_id = p_transaction_step_id
    order by transaction_value_id;