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.PQH_SS_UTILITY SQL Statements

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

Line: 5


SELECT to_char(object_version_number), effective_start_date
FROM   per_all_assignments_f
WHERE  assignment_id        = c_assignmentId
AND    c_effectiveDate BETWEEN effective_start_date AND effective_end_date;

Line: 22


select business_group_id
from per_all_people_f
where person_id = p_person_id;

Line: 28


select 'x'
from BEN_POPL_RPTG_GRP_F prg
where rptg_grp_id = p_rptg_grp_id
and business_group_id = p_business_group_id
and prg.pgm_id is null
and sysdate between prg.effective_start_date and prg.effective_end_date;

Line: 36


select 'x'
from dual
where exists
(select eper.pl_id
from BEN_POPL_RPTG_GRP_F prg, ben_elig_per_f eper
where rptg_grp_id = p_rptg_grp_id
and person_id = p_person_id
and prg.pl_id = eper.pl_id
and eper.pgm_id is null
and sysdate between prg.effective_start_date and prg.effective_end_date
and sysdate between eper.effective_start_date and eper.effective_end_date
and eper.elig_flag = 'Y'
);

Line: 51


select 'x'
from dual
where exists
(select eper.pl_id
from BEN_POPL_RPTG_GRP_F prg, ben_elig_per_f eper
where rptg_grp_id = p_rptg_grp_id
and person_id = p_person_id
and prg.pl_id = eper.pl_id
and eper.pgm_id is null
and sysdate between prg.effective_start_date and prg.effective_end_date
and sysdate between eper.effective_start_date and eper.effective_end_date
and eper.elig_flag = 'N'
);

Line: 122


select rptg_grp_id
from ben_rptg_grp
where function_code = p_function_name
and rptg_prps_cd = 'PERACT'
and nvl(business_group_id, p_business_group_id) = p_business_group_id
order by business_group_id;

Line: 146


select 'x'
from dual
where exists
(select eper.pl_id
from BEN_POPL_RPTG_GRP prg, ben_elig_per_f eper
where rptg_grp_id = p_rptg_grp_id
and person_id = p_person_id
and prg.pl_id = eper.pl_id
and eper.pgm_id is null
and sysdate between prg.effective_start_date and prg.effective_end_date
and sysdate between eper.effective_start_date and eper.effective_end_date
and eper.elig_flag = 'Y'
);

Line: 182


 select ver.pos_structure_version_id
 from per_position_structures str, per_pos_structure_versions ver
 where str.position_structure_id = ver.position_structure_id
 and sysdate between ver.date_from and nvl(ver.date_to, hr_general.end_of_time)
 and str.primary_position_flag = 'Y'
 and str.business_group_id = p_business_group_id;

Line: 208


SELECT decode(business_group_id,null,'Y','N') global_role, role_name, role_id
FROM   pqh_roles
WHERE  role_type_cd      = p_roleTypeCd
AND    enable_flag       = 'Y'
AND  ( business_group_id = p_businessGroupId OR  business_group_id IS NULL);

Line: 279


SELECT role_id
FROM   pqh_roles
WHERE  role_type_cd      = p_role_type_cd
AND    enable_flag       = 'Y'
AND  ( business_group_id = p_businessGroupId OR  business_group_id IS NULL);  -- either cross business_group or the bg of logged user

Line: 287


SELECT 'X'
from  per_people_extra_info pei , pqh_roles rls
WHERE information_type              = 'PQH_ROLE_USERS'
 and  rls.role_id                   = to_number(pei.pei_information3)
 and  nvl(pei.pei_information5,'Y') ='Y'
 and  rls.role_id                   = p_roleId
 and  pei.person_id                 = p_personId;

Line: 377


SELECT object_version_number
FROM   per_all_assignments_f
WHERE  assignment_id        = p_assignmentId
AND    effective_start_date > p_effectiveDate;

Line: 388


SELECT transaction_step_id
FROM   hr_api_transaction_steps
WHERE  transaction_id = c_txnId
AND    api_name       IN ( 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API', 'HR_SUPERVISOR_SS.PROCESS_API');

Line: 447


SELECT 'X'
FROM   hr_api_transaction_steps ts
WHERE  ts.transaction_id     = p_txnId
  AND  ts.api_name IN ('HR_SUPERVISOR_SS.PROCESS_API',
                       'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API',
                       'HR_PAY_RATE_SS.PROCESS_API',
                       'HR_TERMINATION_SS.PROCESS_API' );

Line: 456


SELECT 'X'
  FROM hr_api_transactions t, hr_api_transaction_steps ts
 WHERE t.transaction_id    = ts.transaction_id
   AND ts.item_type        = p_itemType
   AND ts.update_person_id = p_personId
   AND t.status in ('Y','YS','RO','ROS')  -- not considering C, RI, RIS as they are with init
   AND ts.api_name IN ('HR_SUPERVISOR_SS.PROCESS_API','HR_PROCESS_ASSIGNMENT_SS.PROCESS_API',
                       'HR_PAY_RATE_SS.PROCESS_API',  'HR_TERMINATION_SS.PROCESS_API' )
   AND EXISTS (SELECT NULL
                 FROM hr_api_transaction_values tsv
                WHERE tsv.transaction_step_id = ts.transaction_step_id
                  AND tsv.name        = 'P_REVIEW_PROC_CALL'
                  AND tsv.varchar2_value IS NOT NULL)
  AND EXISTS (SELECT NULL
                FROM wf_item_attribute_values iav2
               WHERE iav2.item_type   = ts.item_type
                 AND iav2.item_key    = ts.item_key
                 AND iav2.name        = 'TRAN_SUBMIT'
                 AND iav2.text_value  = 'Y')
  AND EXISTS (SELECT NULL
                FROM wf_item_attribute_values iav
               WHERE iav.item_type    = ts.item_type
                 AND iav.item_key     = ts.item_key
                 AND iav.name         = 'CURRENT_ASSIGNMENT_ID'
                 AND iav.number_value = p_assignId)
  AND ts.transaction_id  <> p_txnId ;

Line: 520


SELECT eper.elig_flag eligibility_flag
FROM   ben_elig_per_f eper
WHERE  eper.pl_id     = p_planId
  AND  eper.pgm_id    IS NULL
  AND  eper.person_id = p_personId
  AND  p_EffectiveDate  BETWEEN eper.effective_start_date AND eper.effective_end_date
  ORDER BY eper.effective_start_date desc;

Line: 551


   SELECT   business_group_id
   FROM     per_all_people_f
   WHERE    person_id  = p_personId
   AND      p_effectiveDate BETWEEN effective_start_date AND effective_end_date  ;

Line: 580


  execute immediate 'begin select '||p_function||'into pqh_ss_utility.l_description from dual; end;';

Line: 590


SELECT null
FROM   per_all_assignments_f  af, hr_api_transactions  hat
WHERE  af.assignment_id        = hat.assignment_id
AND    af.effective_start_date = hat.transaction_effective_date
AND    hat.transaction_id       = p_txnId;

Line: 608


    UPDATE  hr_api_transaction_values
       SET  varchar2_value      = l_mode
     WHERE  name                = 'P_DATETRACK_UPDATE_MODE'
       AND  transaction_step_id = (
            SELECT  transaction_step_id
            FROM    hr_api_transaction_steps
            WHERE   transaction_id   = p_txnId
            AND     api_name         = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API' )
       AND  varchar2_value      <> l_mode;

Line: 627


SELECT  parameters
FROM    fnd_form_functions
WHERE   function_id         = p_functionId;

Line: 653


  SELECT  min(effective_Start_date)
  FROM    per_all_assignments_f
  WHERE   assignment_id   = p_assignmentId
  AND     assignment_type in ('E','C');

Line: 791


   SELECT name,
          atv.varchar2_value|| atv.number_value||atv.date_value txn_curr_value,
          atv.original_varchar2_value|| atv.original_number_value||atv.original_date_value txn_old_value
     FROM hr_api_transaction_values atv,
          pqh_attributes att,
          pqh_table_route   ptr
    WHERE att.column_name  = name
      AND att.master_table_route_id = ptr.table_route_id
      AND ptr.table_alias IN ('PQH_SS_ASG_PG1','PQH_SS_OTHER_EMP_INFO_PG1')
      AND atv.transaction_step_id  IN ( SELECT transaction_step_id
                                          FROM hr_api_transaction_steps
                                         WHERE transaction_id = p_txnId
                                           AND api_name = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API');

Line: 806


    SELECT *
    FROM  per_all_assignments_f
    WHERE assignment_id = p_assignmentId
    AND   p_effectiveDate between effective_start_date and effective_end_date
    AND   assignment_type IN ('E','A','C');

Line: 813


  SELECT NVL(original_number_value,number_value)
  FROM   hr_api_transaction_values
  WHERE  transaction_step_id  = p_stepId
  AND    name                 = p_param_name;

Line: 819


SELECT transaction_step_id
FROM   hr_api_transaction_steps
WHERE  transaction_id = c_txnId
AND    api_name       = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API';

Line: 905


SELECT transaction_step_id
FROM   hr_api_transaction_steps
WHERE  item_type      = p_itemType
AND    item_key       = p_itemKey
AND    api_name       = p_apiName;

Line: 927


 SELECT 'Y'
   FROM hr_api_transaction_steps
  WHERE transaction_id = p_transaction_id;

Line: 951


SELECT 'x'
FROM ben_popl_rptg_grp_f
where rptg_grp_id = p_reporting_group_id
and business_group_id = p_business_group_id
and trunc(p_effective_date) between effective_start_date and effective_end_date;