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

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

Line: 31


  SELECT formula_id
  FROM ff_formulas_x
  WHERE formula_name = 'BUDGET_' || p_formula_name
  AND business_group_id = p_business_group_id;

Line: 37


  SELECT formula_id
  FROM ff_formulas_x
  WHERE formula_name = 'TEMPLATE_' || p_formula_name
  AND business_group_id IS NULL;

Line: 119


  SELECT
   b.unit
  ,b.business_group_id
  FROM per_budgets   b
  WHERE b.budget_id = p_budget_id;

Line: 126


  SELECT tp.end_date
  FROM per_time_periods    tp
  WHERE tp.time_period_id	= p_time_period_id;

Line: 134


/* passed in a different cursor is used. The most selective parameter to use  */
/* is p_position_id, so this is checked first, followed by grade, job and     */
/* organization. If all parameters are null, then business group id is used.  */
/* Note that the option of using this index is disabled in the former four    */
/* cursors. This is to prevent an inefficient query plan being used.          */
/*----------------------------------------------------------------------------*/
/* To be accessed if p_position_id is not null */
  CURSOR pos_assignment_csr(
         p_business_group_id	NUMBER
        ,p_grade_id		NUMBER
        ,p_job_id		NUMBER
        ,p_organization_id	NUMBER
        ,p_position_id		NUMBER
        ,p_period_end_date	DATE) is
  SELECT asg.assignment_id
  FROM  per_assignments_f    asg
       ,per_assignment_status_types   ast
  WHERE p_position_id = asg.position_id
  AND (p_organization_id IS NULL OR asg.organization_id = p_organization_id)
  AND (p_job_id IS NULL OR asg.job_id = p_job_id)
  AND (p_grade_id IS NULL OR asg.grade_id = p_grade_id)
  AND asg.business_group_id + 0 = p_business_group_id
  AND asg.assignment_type = 'E'
  AND p_period_end_date BETWEEN asg.effective_start_date
                            AND asg.effective_end_date
  AND asg.assignment_status_type_id = ast.assignment_status_type_id
  AND ast.per_system_status = 'ACTIVE_ASSIGN';

Line: 169


  SELECT asg.assignment_id
  FROM  per_assignments_f    asg
       ,per_assignment_status_types   ast
  WHERE (p_organization_id IS NULL OR asg.organization_id = p_organization_id)
  AND (p_job_id IS NULL OR asg.job_id = p_job_id)
  AND p_grade_id = asg.grade_id
  AND asg.business_group_id + 0 = p_business_group_id
  AND asg.assignment_type = 'E'
  AND p_period_end_date BETWEEN asg.effective_start_date
                            AND asg.effective_end_date
  AND asg.assignment_status_type_id = ast.assignment_status_type_id
  AND ast.per_system_status = 'ACTIVE_ASSIGN';

Line: 188


  SELECT asg.assignment_id
  FROM  per_assignments_f    asg
       ,per_assignment_status_types   ast
  WHERE (p_organization_id IS NULL OR asg.organization_id = p_organization_id)
  AND p_job_id = asg.job_id
  AND asg.business_group_id + 0 = p_business_group_id
  AND asg.assignment_type = 'E'
  AND p_period_end_date BETWEEN asg.effective_start_date
                            AND asg.effective_end_date
  AND asg.assignment_status_type_id = ast.assignment_status_type_id
  AND ast.per_system_status = 'ACTIVE_ASSIGN';

Line: 205


  SELECT asg.assignment_id
  FROM  per_assignments_f    asg
       ,per_assignment_status_types   ast
  WHERE p_organization_id = asg.organization_id
  AND asg.business_group_id + 0 = p_business_group_id
  AND asg.assignment_type = 'E'
  AND p_period_end_date BETWEEN asg.effective_start_date
                            AND asg.effective_end_date
  AND asg.assignment_status_type_id = ast.assignment_status_type_id
  AND ast.per_system_status = 'ACTIVE_ASSIGN';

Line: 220


  SELECT asg.assignment_id
  FROM  per_assignments_f    asg
       ,per_assignment_status_types   ast
  WHERE asg.business_group_id = p_business_group_id
  AND asg.assignment_type = 'E'
  AND p_period_end_date BETWEEN asg.effective_start_date
                            AND asg.effective_end_date
  AND asg.assignment_status_type_id = ast.assignment_status_type_id
  AND ast.per_system_status = 'ACTIVE_ASSIGN';

Line: 279


/* passed in a different cursor is used. The most selective parameter to use  */
/* is p_position_id, so this is checked first, followed by grade, job and     */
/* organization. If all parameters are null, then business group id is used.  */
/* Note that the option of using this index is disabled in the former four    */
/* cursors. This is to prevent an inefficient query plan being used.          */
/*----------------------------------------------------------------------------*/

  IF (p_position_id IS NOT NULL) THEN

    FOR assignment_rec in pos_assignment_csr(
	 l_business_group_id
	,p_grade_id
	,p_job_id
	,p_organization_id
	,p_position_id
	,l_period_end_date) LOOP

      l_assignment_id := assignment_rec.assignment_id;

Line: 456


select  pst1.system_type_cd     unit1_name
      , pst2.system_type_cd     unit2_name
      , pst3.system_type_cd     unit3_name
      , bgt.business_group_id   business_group_id
from    pqh_budgets     bgt
      , per_shared_types_vl pst1
      , per_shared_types_vl pst2
      , per_shared_types_vl pst3
where bgt.budget_id     = p_budget_id
AND   bgt.budget_unit1_id               = pst1.shared_type_id (+)
AND   bgt.budget_unit2_id               = pst2.shared_type_id (+)
AND   bgt.budget_unit3_id               = pst3.shared_type_id (+);

Line: 470


select  tp.end_date
from    per_time_periods        tp, pqh_budget_periods bpr
where   bpr.budget_period_id = p_time_period_id
and     tp.time_period_id    = bpr.end_time_period_id;

Line: 479


/* passed in a different cursor is used. The most selective parameter to use  */
/* is p_position_id, so this is checked first, followed by grade, job and     */
/* organization. If all parameters are null, then business group id is used.  */
/* Note that the option of using this index is disabled in the former four    */
/* cursors. This is to prevent an inefficient query plan being used.          */
/*----------------------------------------------------------------------------*/
/* To be used if p_position_id is not null */
cursor pos_assignment_csr(
         p_business_group_id    NUMBER
        ,p_grade_id             NUMBER
        ,p_job_id               NUMBER
        ,p_organization_id      NUMBER
        ,p_position_id          NUMBER
        ,p_period_end_date      DATE
        ) is
select  asg.assignment_id
from     per_assignments_f              asg
        ,per_assignment_status_types    ast
where   (p_organization_id IS NULL OR asg.organization_id = p_organization_id)
and     p_position_id = asg.position_id
and     (p_job_id IS NULL OR asg.job_id = p_job_id)
and     (p_grade_id IS NULL OR asg.grade_id = p_grade_id)
and     asg.business_group_id + 0 = p_business_group_id
and     asg.assignment_type     = 'E'
and     p_period_end_date between asg.effective_start_date and
        asg.effective_end_date
and     asg.assignment_status_type_id   = ast.assignment_status_type_id
and     ast.per_system_status           = 'ACTIVE_ASSIGN';

Line: 516


select  asg.assignment_id
from     per_assignments_f              asg
        ,per_assignment_status_types    ast
where   (p_organization_id IS NULL OR asg.organization_id = p_organization_id)
and     (p_job_id IS NULL OR asg.job_id = p_job_id)
and     p_grade_id = asg.grade_id
and     asg.business_group_id + 0 = p_business_group_id
and     asg.assignment_type     = 'E'
and     p_period_end_date between asg.effective_start_date and
        asg.effective_end_date
and     asg.assignment_status_type_id   = ast.assignment_status_type_id
and     ast.per_system_status           = 'ACTIVE_ASSIGN';

Line: 536


select  asg.assignment_id
from     per_assignments_f              asg
        ,per_assignment_status_types    ast
where   (p_organization_id IS NULL OR asg.organization_id = p_organization_id)
and     p_job_id = asg.job_id
and     asg.business_group_id + 0 = p_business_group_id
and     asg.assignment_type     = 'E'
and     p_period_end_date between asg.effective_start_date and
        asg.effective_end_date
and     asg.assignment_status_type_id   = ast.assignment_status_type_id
and     ast.per_system_status           = 'ACTIVE_ASSIGN';

Line: 554


select  asg.assignment_id
from     per_assignments_f              asg
        ,per_assignment_status_types    ast
where   p_organization_id = asg.organization_id
and     asg.business_group_id + 0 = p_business_group_id
and     asg.assignment_type     = 'E'
and     p_period_end_date between asg.effective_start_date and
        asg.effective_end_date
and     asg.assignment_status_type_id   = ast.assignment_status_type_id
and     ast.per_system_status           = 'ACTIVE_ASSIGN';

Line: 570


select  asg.assignment_id
from     per_assignments_f              asg
        ,per_assignment_status_types    ast
where   asg.business_group_id = p_business_group_id
and     asg.assignment_type     = 'E'
and     p_period_end_date between asg.effective_start_date and
        asg.effective_end_date
and     asg.assignment_status_type_id   = ast.assignment_status_type_id
and     ast.per_system_status           = 'ACTIVE_ASSIGN';

Line: 643


/* passed in a different cursor is used. The most selective parameter to use  */
/* is p_position_id, so this is checked first, followed by grade, job and     */
/* organization. If all parameters are null, then business group id is used.  */
/* Note that the option of using this index is disabled in the former four    */
/* cursors. This is to prevent an inefficient query plan being used.          */
/*----------------------------------------------------------------------------*/

  IF (p_position_id IS NOT NULL) THEN

    FOR assignment_rec IN pos_assignment_csr(
         l_business_group_id
        ,p_grade_id
        ,p_job_id
        ,p_organization_id
        ,p_position_id
        ,l_period_end_date
        ) LOOP

      l_assignment_id := assignment_rec.assignment_id;

Line: 830


   SELECT count(*) INTO v_direct_reports
   FROM   per_all_assignments_f   asg, per_all_people_f peo
   WHERE  asg.supervisor_id = v_person_id
   AND    v_effective_end_date BETWEEN asg.effective_start_date
                               AND     asg.effective_end_date
   AND    asg.person_id = peo.person_id
   AND    v_effective_end_date
      BETWEEN peo.effective_start_date AND peo.effective_end_date
   AND    peo.current_employee_flag = 'Y' ;

Line: 850


  SELECT lookup_code
  FROM hr_standard_lookups
  WHERE lookup_type = 'BUDGET_MEASUREMENT_TYPE'
  AND meaning = p_bmt_meaning;

Line: 940


SELECT  pst1.system_type_cd     unit1_name
      , pst2.system_type_cd     unit2_name
      , pst3.system_type_cd     unit3_name
      , bgt.business_group_id   business_group_id
FROM    pqh_budgets     bgt
      , per_shared_types_vl pst1
      , per_shared_types_vl pst2
      , per_shared_types_vl pst3
WHERE bgt.budget_id     = p_budget_id
AND   bgt.budget_unit1_id               = pst1.shared_type_id (+)
AND   bgt.budget_unit2_id               = pst2.shared_type_id (+)
AND   bgt.budget_unit3_id               = pst3.shared_type_id (+);

Line: 963


select  asg.assignment_id
from     per_assignments_f              asg
        ,per_assignment_status_types    ast
where   (p_organization_id IS NULL OR asg.organization_id = p_organization_id)
and     p_position_id = asg.position_id
and     (p_job_id IS NULL OR asg.job_id = p_job_id)
and     (p_grade_id IS NULL OR asg.grade_id = p_grade_id)
and     asg.business_group_id   = p_business_group_id
and     asg.assignment_type     = 'E'
and     p_effective_date between asg.effective_start_date and
        asg.effective_end_date
and     asg.assignment_status_type_id   = ast.assignment_status_type_id
and     ast.per_system_status           = 'ACTIVE_ASSIGN';

Line: 985


select  asg.assignment_id
from     per_assignments_f              asg
        ,per_assignment_status_types    ast
where   (p_organization_id IS NULL OR asg.organization_id = p_organization_id)
and     (p_job_id IS NULL OR asg.job_id = p_job_id)
and     p_grade_id = asg.grade_id
and     asg.business_group_id  = p_business_group_id
and     asg.assignment_type     = 'E'
and     p_effective_date between asg.effective_start_date and
        asg.effective_end_date
and     asg.assignment_status_type_id   = ast.assignment_status_type_id
and     ast.per_system_status           = 'ACTIVE_ASSIGN';

Line: 1005


select  asg.assignment_id
from     per_assignments_f              asg
        ,per_assignment_status_types    ast
where   (p_organization_id IS NULL OR asg.organization_id = p_organization_id)
and     p_job_id = asg.job_id
and     asg.business_group_id   = p_business_group_id
and     asg.assignment_type     = 'E'
and     p_effective_date between asg.effective_start_date and
        asg.effective_end_date
and     asg.assignment_status_type_id   = ast.assignment_status_type_id
and     ast.per_system_status           = 'ACTIVE_ASSIGN';

Line: 1023


select  asg.assignment_id
from     per_assignments_f              asg
        ,per_assignment_status_types    ast
where   p_organization_id = asg.organization_id
and     asg.business_group_id  = p_business_group_id
and     asg.assignment_type     = 'E'
and     p_effective_date between asg.effective_start_date and
        asg.effective_end_date
and     asg.assignment_status_type_id   = ast.assignment_status_type_id
and     ast.per_system_status           = 'ACTIVE_ASSIGN';

Line: 1039


select  asg.assignment_id
from     per_assignments_f              asg
        ,per_assignment_status_types    ast
where   asg.business_group_id = p_business_group_id
and     asg.assignment_type     = 'E'
and     p_effective_date between asg.effective_start_date and
        asg.effective_end_date
and     asg.assignment_status_type_id   = ast.assignment_status_type_id
and     ast.per_system_status           = 'ACTIVE_ASSIGN';

Line: 1267


SELECT sum(months_between(least(nvl(actual_termination_date + 1,
       p_effective_date + 1), p_effective_date + 1), date_start)) total_months
FROM  per_periods_of_service
WHERE  person_id             = p_person_id
AND    date_start           <= p_effective_date
AND    period_of_service_id <= p_period_of_service_id;

Line: 1305


SELECT sum(months_between
                (least(nvl(actual_termination_date + 1, p_effective_date + 1),
                   p_effective_date + 1), date_start)) / 12   total_years
FROM  per_periods_of_service
WHERE  person_id             = p_person_id
AND    date_start           <= p_effective_date
AND    period_of_service_id <= p_period_of_service_id;