APPS.PQP_FTE_UTILITIES SQL Statements

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

Line: 57

SELECT 1
FROM   per_assignment_budget_values_f
WHERE  assignment_id = p_assignment_id
  AND  unit = 'FTE'
  AND  ROWNUM < 2;

Line: 67

SELECT assignment_budget_value_id,
       value,
       effective_start_date,
       effective_end_date,
       object_version_number
FROM   per_assignment_budget_values_f
WHERE  assignment_id = p_assignment_id
  AND  unit = 'FTE'
  AND  p_effective_date
         BETWEEN effective_start_date
             AND effective_end_date;

Line: 83

SELECT asg.business_group_id
      ,asg.normal_hours
      ,asg.frequency
FROM   per_all_assignments_f asg
WHERE  asg.assignment_id = p_assignment_id
AND    p_effective_date
         BETWEEN asg.effective_start_date
             AND asg.effective_end_date;

Line: 94

SELECT TO_NUMBER('0') annual_hours
      ,TO_NUMBER('0') period_divisor
      ,row_low_range_or_name contract_type
      ,user_row_id
FROM  pay_user_rows_f
WHERE user_row_id = 0;

Line: 107

SELECT pur.user_row_id, aat.contract_type
FROM   pqp_assignment_attributes_f aat
      ,pay_user_rows_f             pur
WHERE  aat.assignment_id = p_assignment_id
  AND  p_effective_date
           BETWEEN aat.effective_start_date
               AND aat.effective_end_date
  AND  pur.user_table_id = p_pqp_contract_table_id
  AND  pur.business_group_id = aat.business_group_id
  AND  pur.row_low_range_or_name = aat.contract_type
  AND  aat.effective_start_date
         BETWEEN pur.effective_start_date
             AND pur.effective_end_date;

Line: 127

SELECT inst.value
FROM   pay_user_column_instances_f inst
WHERE  inst.user_column_id = p_contract_column_id
  AND  inst.user_row_id    = p_contract_row_id
  AND  p_effective_date
         BETWEEN inst.effective_start_date
             AND inst.effective_end_date;

Line: 387

SELECT status
FROM   fnd_product_installations
WHERE  application_id = p_application_id;

Line: 464

  SELECT user_table_id
  FROM   pay_user_tables
  WHERE  user_table_name = 'PQP_CONTRACT_TYPES'
    AND  legislation_code = 'GB';

Line: 473

  SELECT user_column_id
  FROM   pay_user_columns
  WHERE  user_table_id = p_user_table_id
    AND  UPPER(user_column_name) = UPPER(p_user_column_name)
    AND  legislation_code = 'GB';

Line: 604

  SELECT MIN(aat.effective_start_date)
  FROM   pqp_assignment_attributes_f aat
  WHERE  aat.assignment_id = p_assignment_id
    AND  aat.contract_type IS NOT NULL;

Line: 612

  SELECT MIN(asg.effective_start_date)
  FROM   per_all_assignments_f asg
  WHERE  asg.assignment_id = p_assignment_id
    AND  asg.normal_hours IS NOT NULL;

Line: 943

SELECT effective_end_date
FROM   per_assignment_budget_values_f
WHERE  assignment_budget_value_id = p_assignment_budget_value_id
  AND  effective_start_date > p_effective_date
  AND  ROWNUM < 2;

Line: 978

          ,p_program_update_date        => null
          ,p_assignment_budget_value_id => l_effective_fte_row.assignment_budget_value_id
          ,p_object_version_number      => l_effective_fte_row.object_version_number -- new param added
          ,p_effective_start_date       => l_effective_fte_row.effective_start_date
          ,p_effective_end_date         => l_effective_fte_row.effective_end_date
           );

Line: 987

      g_output_file_records(g_output_file_records.LAST).change_type := 'INSERT';

Line: 1045

      l_datetrack_mode := 'UPDATE_OVERRIDE';

Line: 1052

        l_datetrack_mode := 'UPDATE';

Line: 1065

   IF l_datetrack_mode <> 'UPDATE_OVERRIDE'
   THEN

     -- only do a datetrack UPDATE or correction if the value is different

     IF ROUND(l_effective_fte_row.value,5) <> ROUND(p_fte_value,5)
     THEN

     per_abv_upd.upd(
       p_effective_date             => p_calculation_date
      ,p_datetrack_mode             => l_datetrack_mode
      ,p_assignment_budget_value_id => l_effective_fte_row.assignment_budget_value_id
      ,p_object_version_number      => l_effective_fte_row.object_version_number -- new param added
      ,p_unit                       => 'FTE'
      ,p_value                      => p_fte_value
      ,p_request_id                 => null
      ,p_program_application_id     => null
      ,p_program_id                 => null
      ,p_program_update_date        => null
      ,p_effective_start_date       => l_effective_fte_row.effective_start_date
      ,p_effective_end_date         => l_effective_fte_row.effective_end_date
       );

Line: 1136

           ,p_program_update_date        => null
           ,p_effective_start_date       => l_effective_fte_row.effective_start_date
           ,p_effective_end_date         => l_effective_fte_row.effective_end_date
            );

Line: 1164

           ,p_program_update_date        => null
           ,p_effective_start_date       => l_effective_fte_row.effective_start_date
           ,p_effective_end_date         => l_effective_fte_row.effective_end_date
            );

Line: 1172

         g_output_file_records(g_output_file_records.LAST).change_type := 'UPDATE_OVERRIDE';

Line: 1179

         g_output_file_records(g_output_file_records.LAST).change_type := 'UPDATE_OVERRIDE';

Line: 1186

   END IF; -- IF l_datetrack_mode <> 'UPDATE_OVERRIDE'

Line: 1648

       'Updated, '||p_assignment_id||', '||
       fnd_date.date_to_displaydate(p_effective_date)||', '||
       l_FTE_value||', '||
       l_assignment_details.normal_hours||', '||
--       l_assignment_details.contract_type||', '||
       l_contract_details.annual_hours||', '||
       l_contract_details.period_divisor
      );  */

Line: 1657

       'Updated, '||p_assignment_id||', '||
       fnd_date.date_to_displaydate(p_effective_date, calendar_aware => FND_DATE.calendar_aware_alt)||', '||
       l_FTE_value||', '||
       l_assignment_details.normal_hours||', '||
--       l_assignment_details.contract_type||', '||
       l_contract_details.annual_hours||', '||
       l_contract_details.period_divisor
      );

Line: 1697

PROCEDURE update_fte_for_assignment
  (p_assignment_id                NUMBER
  ,p_effective_date               DATE
  )
IS

  l_proc_name             VARCHAR2(61):=
    g_package_name||'update_fte_for_assignment';

Line: 1727

  SELECT 'Normal Hours' change_type,asg2.effective_start_date
  FROM   per_all_assignments_f asg1
        ,per_all_assignments_f asg2
  WHERE  asg1.assignment_id = p_assignment_id
    AND  ( asg1.effective_start_date >= p_min_effective_start_date
          OR
           p_min_effective_start_date
             BETWEEN asg1.effective_start_date
                 AND asg1.effective_end_date
         )
    AND  asg2.assignment_id = asg1.assignment_id
    AND  asg2.effective_start_date = asg1.effective_end_date+1
    AND  NVL(asg2.normal_hours,-1) <> NVL(asg1.normal_hours,-2)
  UNION ALL
  SELECT 'Assignment Contract' change_type,aat2.effective_start_date
  FROM   pqp_assignment_attributes_f aat1
        ,pqp_assignment_attributes_f aat2
  WHERE  aat1.assignment_id = p_assignment_id
    AND  ( aat1.effective_start_date >= p_min_effective_start_date
          OR
           p_min_effective_start_date
             BETWEEN aat1.effective_start_date
                 AND aat1.effective_end_date
         )
    AND  aat1.assignment_id = aat2.assignment_id
    AND  aat2.effective_start_date = aat1.effective_end_date+1
    AND  NVL(aat2.contract_type,'{null}') <> NVL(aat1.contract_type,'[NULL]')
  UNION ALL
  SELECT 'Contract Type' change_type,inst2.effective_start_date
  FROM   pqp_assignment_attributes_f aat
        ,pay_user_rows_f             pur
        ,pay_user_column_instances_f inst1
        ,pay_user_column_instances_f inst2
  WHERE  aat.assignment_id = p_assignment_id
    AND  ( aat.effective_start_date >= p_min_effective_start_date
          OR
           p_min_effective_start_date
             BETWEEN aat.effective_start_date
                 AND aat.effective_end_date
         )
    AND  pur.user_table_id = p_pqp_contract_table_id
    AND  pur.business_group_id = aat.business_group_id
    AND  pur.row_low_range_or_name = aat.contract_type
    AND  aat.effective_start_date
           BETWEEN pur.effective_start_date
               AND pur.effective_end_date
    AND  inst1.user_column_id IN
           (p_annual_hours_col_id
           ,p_period_divisor_col_id
           )
    AND  ( inst1.effective_start_date >= p_min_effective_start_date
          OR
           p_min_effective_start_date
             BETWEEN inst1.effective_start_date
                 AND inst1.effective_end_date
         )
    AND  inst1.user_row_id = pur.user_row_id
    AND  inst2.user_column_instance_id = inst1.user_column_instance_id
    AND  inst2.effective_start_date = inst1.effective_end_date+1
    AND  NVL(inst2.value,'{null}') <> NVL(inst1.value,'~NULL~')
  ORDER BY 2 ASC;

Line: 1797

BEGIN -- update_fte_for_assignment


IF NOT g_is_concurrent_program_run  THEN
  g_debug := hr_utility.debug_enabled;

Line: 2014

END update_fte_for_assignment;

Line: 2017

   Name    : Update_FTE_For Assignment_Set
   Purpose : Update multiple FTE values. Normally called as a
             concurrent process.
   Returns :
   ---------------------------------------------------------------------*/
PROCEDURE update_fte_for_assignment_set
  (ERRBUF                        OUT NOCOPY VARCHAR2
  ,RETCODE                       OUT NOCOPY NUMBER
  ,p_contract_type               IN  VARCHAR2
  ,p_payroll_id                  IN  NUMBER
  ,p_calculation_date            IN  VARCHAR2
  ,p_trace                       IN  VARCHAR2
  )
IS

l_proc_step                    NUMBER(20,10):=0;

Line: 2034

  g_package_name||'update_fte_for_assignment_set';

Line: 2049

SELECT DISTINCT asg.assignment_id
FROM   per_all_assignments_f asg
WHERE  asg.payroll_id = p_payroll_id
  AND  ( p_effective_date
           BETWEEN asg.effective_start_date AND asg.effective_end_date
        OR
         asg.effective_start_date > p_effective_date
       );

Line: 2065

SELECT DISTINCT aat.assignment_id
FROM   pqp_assignment_attributes_f aat
WHERE  aat.business_group_id = p_business_group_id
  AND  aat.contract_type = NVL(p_contract_type,aat.contract_type)
  AND  ( p_effective_date
           BETWEEN aat.effective_start_date AND aat.effective_end_date
        OR
         aat.effective_start_date > p_effective_date
       );

Line: 2081

SELECT  DISTINCT asg.assignment_id
FROM    per_all_assignments_f asg,
        pqp_assignment_attributes_f aat
WHERE   asg.payroll_id    = p_payroll_id
  AND   ( p_effective_date
           BETWEEN asg.effective_start_date AND asg.effective_end_date
        OR
         asg.effective_start_date > p_effective_date
        )
  AND   aat.assignment_id = asg.assignment_id
  AND   aat.contract_type = p_contract_type
  AND   ( p_effective_date
           BETWEEN aat.effective_start_date AND aat.effective_end_date
        OR
         aat.effective_start_date > p_effective_date
        );

Line: 2101

SELECT per.full_name
FROM   per_all_people_f per,
       per_all_assignments_f asg
WHERE  asg.person_id = per.person_id
  AND  asg.assignment_id = p_assignment_id
  AND  l_calculation_date
       BETWEEN asg.effective_start_date AND asg.effective_end_date
  AND  l_calculation_date
       BETWEEN per.effective_start_date AND per.effective_end_date;

Line: 2117

BEGIN -- update_fte_for_assignment_set

  g_is_concurrent_program_run := TRUE;

Line: 2170

  SELECT name
  INTO   l_log_string
  FROM   per_business_groups_perf
  WHERE  business_group_id = l_business_group_id;

Line: 2183

    SELECT a.payroll_name
    INTO   l_log_string
    FROM   pay_all_payrolls_f a
    WHERE  a.payroll_id = p_payroll_id
      AND  effective_start_date =
             (SELECT MAX(b.effective_start_date)
              FROM   pay_all_payrolls_f b
              WHERE  b.payroll_id = a.payroll_id
              );

Line: 2274

  DELETE FROM fnd_sessions WHERE session_id = USERENV('sessionid');

Line: 2275

  INSERT INTO fnd_sessions
    (session_id
    ,effective_date
    )
  VALUES
    (USERENV('sessionid')
    ,l_calculation_date
    );

Line: 2358

      g_output_file_records.DELETE;

Line: 2395

      SELECT employee_number
      INTO   g_output_file_records(i).employee_number
      FROM   per_all_people_f a
      WHERE  a.person_id =
               (SELECT asg.person_id
                FROM   per_all_assignments_f asg
                WHERE  asg.assignment_id = l_assignment(i)
                  AND  ROWNUM < 2
                )
        AND  effective_start_date =
               (SELECT MAX(b.effective_start_date)
                FROM   per_all_people_f b
                WHERE  b.person_id = a.person_id
               );

Line: 2415

      SELECT assignment_number
      INTO   g_output_file_records(i).assignment_number
      FROM   per_all_assignments_f a
      WHERE  a.assignment_id = l_assignment(i)
        AND  a.effective_start_date =
               (SELECT MAX(b.effective_start_date)
                FROM   per_all_assignments_f b
                WHERE  b.assignment_id = a.assignment_id
               );

Line: 2434

      update_fte_for_assignment
        (p_assignment_id  => l_assignment(i)
        ,p_effective_date => l_FTE_processing_start_date
        );

Line: 2505

          g_output_file_records.DELETE; -- do not include in clear cache

Line: 2518

  DELETE FROM fnd_sessions WHERE session_id = USERENV('sessionid');

Line: 2550

END update_fte_for_assignment_set;