DBA Data[Home] [Help]

APPS.PQP_GB_AD_EE SQL Statements

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

Line: 26

   SELECT effective_end_date,payroll_id
    INTO l_asg_ed,l_asg_py_id
    FROM PER_ALL_ASSIGNMENTS_F
   WHERE assignment_id = p_in_asg_id
     AND p_in_eff_date BETWEEN
         effective_start_date
     AND effective_end_date;
Line: 45

   SELECT time.end_date
     INTO l_py_ed
     FROM per_time_periods time
    WHERE time.payroll_id = l_asg_py_id
     AND time.end_date > ( SELECT  MAX(effective_date)
                       FROM pay_payroll_actions act
                      WHERE  act.payroll_id =l_asg_py_id
                          and act.action_status='C')
     AND ROWNUM = 1;
Line: 99

      SELECT business_group_id
            ,effective_start_date
            ,effective_end_date
            ,person_id
        INTO l_bg_id
            ,l_asg_sd
            ,l_asg_ed
            ,l_person_id
       FROM PER_ALL_ASSIGNMENTS_F
      WHERE assignment_id = p_in_asg_id
        AND p_in_eff_date
    BETWEEN effective_start_date AND
            effective_end_date;
Line: 138

   SELECT count(asg.assignment_id)
     INTO l_asg_count
     FROM pay_element_links_f         pel,
          pay_element_entries_f       pee,
          pay_element_types_f         pet,
          per_all_assignments_f       asg,
          pay_element_type_extra_info pete,
          pay_element_entry_values_f  peev,
          pay_input_values_f          piv
    WHERE pet.element_type_id   =  pel.element_type_id
      AND pel.element_link_id   =  pee.element_link_id
      AND pet.element_type_id   =  pete.element_type_id
      AND pee.assignment_id     =  asg.assignment_id
      AND peev.element_entry_id =  pee.element_entry_id
      AND peev.input_value_id   =  piv.input_value_id
      AND pee.assignment_id     <> p_in_asg_id
      AND asg.person_id         =  l_person_id
      AND piv.name              IN  ('Vehicle Type','Rate Type')
      AND peev.screen_entry_value = p_in_veh_type
      AND asg.business_group_id =  l_bg_id
      AND p_in_eff_date BETWEEN
          pet.effective_start_date AND pet.effective_end_date
      AND asg.effective_end_date   > p_in_claim_end_date
      AND asg.effective_start_date < l_asg_ed
      AND p_in_eff_date BETWEEN
          pel.effective_start_date AND pel.effective_end_date
      AND p_in_eff_date BETWEEN
          pee.effective_start_date AND pee.effective_end_date
      AND p_in_eff_date BETWEEN
          peev.effective_start_date AND peev.effective_end_date
      AND p_in_eff_date BETWEEN
          piv.effective_start_date AND piv.effective_end_date
      AND pete.information_type = 'PQP_VEHICLE_MILEAGE_INFO'
      AND pete.eei_information1      <>'L';
Line: 202

select max(ppa.effective_date) effective_date
 from  pay_payroll_actions ppa,
       pay_assignment_actions paa,
       per_assignments_f paf
 WHERE paf.person_id =(SELECT distinct person_id from per_assignments_f
                        where assignment_id =p_assignment_id_o)
   AND paf.assignment_id=paa.assignment_id
   and paa.payroll_action_id=ppa.payroll_action_id
   AND  ppa.action_type         in ('R','Q','V');
Line: 213

SELECT effective_start_date,
       effective_end_date
  FROM pay_element_entries_f
 WHERE element_entry_id=p_element_entry_id
  AND p_effective_date BETWEEN
      effective_start_date
   AND effective_end_date;
Line: 227

SELECT pee.element_entry_id,
       pee.effective_end_date,
       pet.element_type_id,
       pel.element_link_id,
       pel.effective_end_date link_end_date,
       pee.effective_start_date
  FROM pay_element_links_f         pel,
       pay_element_entries_f       pee,
       pay_element_types_f         pet,
       pay_element_type_extra_info pete
 WHERE pet.element_type_id   = pel.element_type_id
   AND pel.element_link_id   = pee.element_link_id
   AND pet.element_type_id   = pete.element_type_id
   AND p_effective_date BETWEEN
       pel.effective_start_date AND pel.effective_end_date
   AND p_effective_date BETWEEN
       pet.effective_start_date AND pet.effective_end_date
   AND pee.assignment_id     = p_assignment_id_o
   -- Open only those entries entered after the current entry.
   AND pee.element_entry_id > p_element_entry_id
   AND pete.information_type = 'PQP_VEHICLE_MILEAGE_INFO'
   AND pete.eei_information1      <>'L'
   AND pee.effective_start_date     >= p_ele_start_date
   AND (pee.effective_end_date      <= p_ele_end_date
        OR pee.effective_end_date    = hr_general.end_of_time)
   ORDER BY 1,2 desc;
Line: 257

SELECT 'x'
  FROM pay_element_links_f         pel,
       pay_element_entries_f       pee,
       pay_element_types_f         pet,
       pay_element_type_extra_info pete
 WHERE pet.element_type_id  = pel.element_type_id
   AND pel.element_link_id  = pee.element_link_id
   AND pet.element_type_id  = pete.element_type_id
   AND pee.assignment_id    = p_assignment_id_o
   AND pee.element_entry_id = p_element_entry_id
   AND p_effective_date BETWEEN
       pet.effective_start_date AND pet.effective_end_date
   AND p_effective_date BETWEEN
       pel.effective_start_date AND pel.effective_end_date
   AND p_effective_date BETWEEN
       pee.effective_start_date AND pee.effective_end_date
   AND pete.information_type = 'PQP_VEHICLE_MILEAGE_INFO'
   AND pete.eei_information1      <>'L';
Line: 282

SELECT peev.screen_entry_value
  FROM pay_element_links_f         pel,
       pay_element_entries_f       pee,
       pay_element_types_f         pet,
       pay_element_type_extra_info pete,
       pay_element_entry_values_f  peev,
       pay_input_values_f          piv
 WHERE pet.element_type_id   = pel.element_type_id
   AND pel.element_link_id   = pee.element_link_id
   AND pet.element_type_id   = pete.element_type_id
   AND peev.element_entry_id = pee.element_entry_id
   AND peev.input_value_id   = piv.input_value_id
   AND piv.name              = p_name
   AND pee.assignment_id     = p_assignment_id_o
   AND pee.element_entry_id  = p_in_element_entry_id
   AND p_effective_date BETWEEN
       pet.effective_start_date AND pet.effective_end_date
   AND p_effective_date BETWEEN
       pel.effective_start_date AND pel.effective_end_date
   AND p_effective_date BETWEEN
       pee.effective_start_date AND pee.effective_end_date
   AND p_effective_date BETWEEN
       peev.effective_start_date AND peev.effective_end_date
   AND p_effective_date BETWEEN
       piv.effective_start_date AND piv.effective_end_date
   AND pete.information_type = 'PQP_VEHICLE_MILEAGE_INFO'
   AND pete.eei_information1      <>'L';
Line: 315

SELECT peev.screen_entry_value
  FROM pay_element_links_f         pel,
       pay_element_entries_f       pee,
       pay_element_types_f         pet,
       pay_element_type_extra_info pete,
       pay_element_entry_values_f  peev,
       pay_input_values_f          piv
 WHERE pet.element_type_id   = pel.element_type_id
   AND pel.element_link_id   = pee.element_link_id
   AND pet.element_type_id   = pete.element_type_id
   AND peev.element_entry_id = pee.element_entry_id
   AND peev.input_value_id   = piv.input_value_id
   AND piv.name              IN ('Vehicle Type','Rate Type')
   AND pee.assignment_id     = p_assignment_id_o
   AND pee.element_entry_id  = p_in_element_entry_id
   AND p_effective_date BETWEEN
       pet.effective_start_date AND pet.effective_end_date
   AND p_effective_date BETWEEN
       pel.effective_start_date AND pel.effective_end_date
   AND p_effective_date BETWEEN
       pee.effective_start_date AND pee.effective_end_date
   AND p_effective_date BETWEEN
       peev.effective_start_date AND peev.effective_end_date
   AND p_effective_date BETWEEN
       piv.effective_start_date AND piv.effective_end_date
   AND pete.information_type = 'PQP_VEHICLE_MILEAGE_INFO'
   AND pete.eei_information1      <>'L';
Line: 352

SELECT peev.screen_entry_value
  FROM pay_element_links_f         pel,
       pay_element_entries_f       pee,
       pay_element_types_f         pet,
       pay_element_type_extra_info pete,
       pay_element_entry_values_f  peev,
       pay_input_values_f          piv
 WHERE pet.element_type_id   = pel.element_type_id
   AND pel.element_link_id   = pee.element_link_id
   AND pet.element_type_id   = pete.element_type_id
   AND peev.element_entry_id = pee.element_entry_id
   AND peev.input_value_id   = piv.input_value_id
   AND piv.name              = p_name
   AND pee.assignment_id     = p_assignment_id_o
   AND pee.element_entry_id  = p_in_element_entry_id
   AND p_effective_date BETWEEN
       pet.effective_start_date AND pet.effective_end_date
   AND p_effective_date BETWEEN
       pel.effective_start_date AND pel.effective_end_date
   -- Code commented out to fix bug .
   -- If the session date is before the EE start date
   -- the hook failed. Go on effective end date instead.
   -- AND p_effective_date BETWEEN
   --    pee.effective_start_date AND pee.effective_end_date
   AND p_effective_date BETWEEN
       piv.effective_start_date AND piv.effective_end_date
   AND peev.effective_end_date       =  p_in_end_date
   AND pee.effective_end_date        =  p_in_end_date
   AND pete.information_type = 'PQP_VEHICLE_MILEAGE_INFO'
   AND pete.eei_information1      <>'L';
Line: 385

SELECT peev.screen_entry_value
  FROM pay_element_links_f         pel,
       pay_element_entries_f       pee,
       pay_element_types_f         pet,
       pay_element_type_extra_info pete,
       pay_element_entry_values_f  peev,
       pay_input_values_f          piv
 WHERE pet.element_type_id   = pel.element_type_id
   AND pel.element_link_id   = pee.element_link_id
   AND pet.element_type_id   = pete.element_type_id
   AND peev.element_entry_id = pee.element_entry_id
   AND peev.input_value_id   = piv.input_value_id
   AND piv.name              IN ('Vehicle Type','Rate Type')
   AND pee.assignment_id     = p_assignment_id_o
   AND pee.element_entry_id  = p_in_element_entry_id
   AND p_effective_date BETWEEN
       pet.effective_start_date AND pet.effective_end_date
   AND p_effective_date BETWEEN
       pel.effective_start_date AND pel.effective_end_date
   -- Code commented out to fix bug .
   -- If the session date is before the EE start date
   -- the hook failed. Go on effective end date instead.
   -- AND p_effective_date BETWEEN
   --    pee.effective_start_date AND pee.effective_end_date
   AND p_effective_date BETWEEN
       piv.effective_start_date AND piv.effective_end_date
   AND peev.effective_end_date       =  p_in_end_date
   AND pee.effective_end_date        =  p_in_end_date
   AND pete.information_type = 'PQP_VEHICLE_MILEAGE_INFO'
   AND pete.eei_information1      <>'L';
Line: 450

     IF p_datetrack_mode <> 'DELETE' THEN -- DT_MODE_CHK
        -- Check if asg expires
        -- Check not required as you cannot perform a NEXT / ALL
        -- if the ASG is already end dated
        --IF NOT asg_expires (p_assignment_id_o,p_effective_date) THEN

        -- Get values from the screen for Claim End Date and Vehicle Type
        -- OR Rate Type for the session date
        OPEN c_chk_payrun;
Line: 628

                     SELECT effective_end_date
                       INTO l_asg_eff_ed
                       FROM PER_ALL_ASSIGNMENTS_F
                      WHERE assignment_id = p_assignment_id_o
                        AND p_effective_date BETWEEN
                            effective_start_date
                        AND effective_end_date;
Line: 647

                     UPDATE pay_element_entries_f
                        SET effective_end_date =
                            LEAST(temp_rec.link_end_date,l_asg_eff_ed)
                      WHERE element_entry_id   = temp_rec.element_entry_id
                        AND effective_end_date = temp_rec.effective_end_date;
Line: 663

                          'DELETE_NEXT_CHANGE',      --dt_delete_mode,
                          --p_effective_date,            --p_session_date,
                          -- Open the entry values from the start_date of
                          -- the element entry.
                          temp_rec.effective_start_date, --p_session_date,
                          NULL,                      --validation_start_date,
                          NULL);                     --validation_end_date);
Line: 680

     END IF; -- Check for DateTrack delete mode
Line: 709

 SELECT pete.eei_information1  ele_type
  FROM  pay_element_type_extra_info pete
 WHERE  pete.information_type='PQP_VEHICLE_MILEAGE_INFO'
   AND  pete.element_type_id= cp_element_type_id;
Line: 718

SELECT 'Y' terminated
  FROM  per_periods_of_service  pds
       ,per_assignments_f       pas
 WHERE  NVL(pds.final_process_date,hr_api.g_eot)    >= p_effective_date
   AND  pds.last_standard_process_date <= p_effective_date
   AND  pds.period_of_service_id        = pas.period_of_service_id
   AND  p_effective_date
   BETWEEN pas.effective_start_date
   AND  pas.effective_end_date
   AND  pas.primary_flag                = 'Y'
   AND  pas.assignment_id               =p_assignment_id;
Line: 732

 SELECT 'Y'
  FROM pay_element_entries_f pef
  WHERE pef.assignment_id  = p_assignment_id
    AND pef.element_link_id=cp_link_id
    AND p_effective_date
    BETWEEN pef.effective_start_date
    AND     pef.effective_end_date;
Line: 742

SELECT element.element_type_id , link.element_link_id,element.business_group_id
 FROM  pay_element_types_f_tl       elementtl,
       pay_element_types_f          element,
       pay_element_links_f          link,
       per_all_assignments_f        asgt ,
       per_periods_of_service       service_period
 WHERE
   --element.element_type_id = elementtl.element_type_id
   -- AND elementtl.language = USERENV('LANG')
   --AND
     asgt.business_group_id = link.business_group_id
  AND asgt.business_group_id =service_period.business_group_id
   AND element.element_type_id = link.element_type_id
   AND service_period.period_of_service_id = asgt.period_of_service_id
   AND p_effective_date
       between element.effective_start_date and element.effective_end_date
   AND p_effective_date
        between asgt.effective_start_date and asgt.effective_end_date
   AND p_effective_date
        between link.effective_start_date and link.effective_end_date
        AND element.indirect_only_flag = 'N'
   AND ((link.payroll_id is NOT NULL AND
           link.payroll_id = asgt.payroll_id)
           OR (link.link_to_all_payrolls_flag = 'Y'
           AND asgt.payroll_id IS NOT NULL)
           OR (link.payroll_id IS NULL
           AND link.link_to_all_payrolls_flag = 'N'))
           AND (link.organization_id = asgt.organization_id
           OR link.organization_id IS NULL)
           AND (link.position_id = asgt.position_id
           OR link.position_id IS NULL)
           AND (link.job_id = asgt.job_id OR link.job_id IS NULL)
           AND (link.grade_id = asgt.grade_id OR link.grade_id IS NULL)
           AND (link.location_id = asgt.location_id
           OR link.location_id IS NULL)
           AND (link.pay_basis_id = asgt.pay_basis_id
           OR link.pay_basis_id IS NULL)
           AND (link.employment_category = asgt.employment_category
           OR link.employment_category IS NULL)
           AND (link.people_group_id IS NULL OR EXISTS
                 ( SELECT 1 FROM pay_assignment_link_usages_f usage
                    WHERE usage.assignment_id = asgt.assignment_id
                      AND usage.element_link_id = link.element_link_id
                      AND p_effective_date
                      BETWEEN usage.effective_start_date
                          AND usage.effective_end_date))
                          AND (service_period.actual_termination_date
                IS NULL OR (service_period.actual_termination_date IS NOT NULL
                 AND p_effective_date <=
                 DECODE(element.post_termination_rule, 'L',
                 service_period.last_standard_process_date, 'F',
                 NVL(service_period.final_process_date,hr_api.g_eot),
                 service_period.actual_termination_date) ))
                 AND asgt.assignment_id=p_assignment_id
         -- AND asgt.business_group_id=2899
          AND element.element_name='Recurring Entry Processor for Terminated EE'
          ORDER BY element.effective_start_date DESC;
Line: 899

PROCEDURE UPDATE_PSI_ASS_DFF_COL
   (
     p_effective_start_date        date,
     p_element_entry_id            number,
     p_assignment_id               number,
     p_element_type_id             Number
   )
   IS

      --Cursor to fetch current Context value, Employment category and Business group for Employee
        Cursor csr_get_curr_asg_dtls
        IS
         SELECT ass_attribute_category,
                employment_category,
              business_group_id,
              effective_start_date,
              effective_end_date,
              object_version_number,
              soft_coding_keyflex_id,
              cagr_grade_def_id,
              ass_attribute1,
              ass_attribute2,
              ass_attribute3,
              ass_attribute4,
              ass_attribute5,
              ass_attribute6,
              ass_attribute7,
              ass_attribute8,
              ass_attribute9,
              ass_attribute10,
              ass_attribute11,
              ass_attribute12,
              ass_attribute13,
              ass_attribute14,
              ass_attribute15,
              ass_attribute16,
              ass_attribute17,
              ass_attribute18,
              ass_attribute19,
              ass_attribute20,
              ass_attribute21,
              ass_attribute22,
              ass_attribute23,
              ass_attribute24,
              ass_attribute25,
              ass_attribute26,
              ass_attribute27,
              ass_attribute28,
              ass_attribute29,
              ass_attribute30
           FROM per_all_assignments_f
           WHERE assignment_id = p_assignment_id
           AND p_effective_start_date between effective_start_date and effective_end_date;
Line: 956

           SELECT pcv_information1 --element_type_id
           FROM pqp_configuration_values
           WHERE pcv_information_category = 'PQP_GB_PENSERV_SCHEME_MAP_INFO'
           AND business_group_id =c_business_group_id
           AND pcv_information2 in ('NUVOS','PARTNER');
Line: 966

          SELECT pcv_information1, --penserver_eligibility_context
               pcv_information2, --mapped_segment
                 pcv_information3 --mapped_dff_segment
          FROM pqp_configuration_values
          WHERE pcv_information_category='PQP_GB_PENSERVER_ELIGBLTY_CONF'
          AND business_group_id = c_business_group_id;
Line: 976

            SELECT pcv_information1 --mapped_casual_emp_categories
            FROM pqp_configuration_values
          WHERE pcv_information_category='PQP_GB_PENSERVER_EMPLYMT_TYPE'
          AND business_group_id = c_business_group_id
          AND pcv_information2 = 'CASUAL';
Line: 985

            SELECT pcv_information1 --mapped_non_casual_emp_cate
            FROM pqp_configuration_values
              WHERE pcv_information_category='PQP_GB_PENSERVER_EMPLYMT_TYPE'
              AND business_group_id = c_business_group_id
              AND pcv_information2 in ('FIXED','REGULAR');
Line: 995

            SELECT employment_category,
                     ass_attribute_category
             FROM per_all_assignments_f
             WHERE assignment_id = p_assignment_id
             AND effective_start_date < p_effective_start_date;
Line: 1006

             SELECT MIN(effective_start_date)
             FROM per_all_assignments_f
             WHERE assignment_id = p_assignment_id
             AND employment_category = c_employment_category;
Line: 1014

             SELECT element_type_id
             FROM pay_element_entries_f
             WHERE assignment_id = p_assignment_id
             AND effective_start_date BETWEEN c_asg_start_date AND (p_effective_start_date-1)
             ORDER BY effective_start_date;
Line: 1072

      hr_utility.set_location('Entering procedure pqp_gb_ad_ee.update_ass_dff_col',10);
Line: 1121

                  l_segment_val_query := 'select '||l_rec_get_mapped_context.pcv_information2||' '||
                                         'from per_all_assignments_f'||' '||
                                         'where assignment_id = '||p_assignment_id||' '||
                                         'and to_date('''||TO_CHAR(p_effective_start_date,'dd/mm/yyyy')||''',''dd/mm/yyyy'')  between effective_start_date'||' '||
                                         'and effective_end_date';
Line: 1178

                        l_prior_asg_dtls_query := 'select employment_category, ass_attribute_category, '||
                                                  l_rec_get_mapped_context.pcv_information2||' '||
                                                  'from per_all_assignments_f'||' '||
                                                  'where assignment_id = '||p_assignment_id||' '||
                                                  'and to_date('''||TO_CHAR(p_effective_start_date,'dd/mm/yyyy')||''',''dd/mm/yyyy'') > effective_start_date';
Line: 1285

                                l_query := 'select '||l_rec_get_mapped_context.pcv_information3||' '||
                                           'from per_all_assignments_f'||' '||
                                           'where assignment_id = '||p_assignment_id||' '||
                                           'and to_date('''||TO_CHAR(p_effective_start_date,'dd/mm/yyyy')||''',''dd/mm/yyyy'')  between effective_start_date'||' '||
                                           'and effective_end_date';
Line: 1305

                                hr_utility.set_location('Call update asg API in correction mode', 19);
Line: 1309

                                  hr_utility.set_location('Call update asg API in update_change_insert mode', 20);
Line: 1310

                                  l_call_mode := 'UPDATE_CHANGE_INSERT';
Line: 1312

                                          hr_utility.set_location('Call update asg API in update mode', 19);
Line: 1313

                                  l_call_mode := 'UPDATE';
Line: 1447

                            hr_assignment_api.update_gb_emp_asg
                            (p_validate                    => false
                            ,p_effective_date              => p_effective_start_date
                            ,p_datetrack_update_mode       => l_call_mode
                            ,p_assignment_id               => p_assignment_id
                            ,p_object_version_number       => l_object_version_number
                            ,p_ass_attribute1              => l_rec_curr_asg_dtls.ass_attribute1
                            ,p_ass_attribute2              => l_rec_curr_asg_dtls.ass_attribute2
                            ,p_ass_attribute3              => l_rec_curr_asg_dtls.ass_attribute3
                            ,p_ass_attribute4              => l_rec_curr_asg_dtls.ass_attribute4
                            ,p_ass_attribute5              => l_rec_curr_asg_dtls.ass_attribute5
                            ,p_ass_attribute6              => l_rec_curr_asg_dtls.ass_attribute6
                            ,p_ass_attribute7              => l_rec_curr_asg_dtls.ass_attribute7
                            ,p_ass_attribute8              => l_rec_curr_asg_dtls.ass_attribute8
                            ,p_ass_attribute9              => l_rec_curr_asg_dtls.ass_attribute9
                            ,p_ass_attribute10             => l_rec_curr_asg_dtls.ass_attribute10
                            ,p_ass_attribute11             => l_rec_curr_asg_dtls.ass_attribute11
                            ,p_ass_attribute12             => l_rec_curr_asg_dtls.ass_attribute12
                            ,p_ass_attribute13             => l_rec_curr_asg_dtls.ass_attribute13
                            ,p_ass_attribute14             => l_rec_curr_asg_dtls.ass_attribute14
                            ,p_ass_attribute15             => l_rec_curr_asg_dtls.ass_attribute15
                            ,p_ass_attribute16             => l_rec_curr_asg_dtls.ass_attribute16
                            ,p_ass_attribute17             => l_rec_curr_asg_dtls.ass_attribute17
                            ,p_ass_attribute18             => l_rec_curr_asg_dtls.ass_attribute18
                            ,p_ass_attribute19             => l_rec_curr_asg_dtls.ass_attribute19
                            ,p_ass_attribute20             => l_rec_curr_asg_dtls.ass_attribute20
                            ,p_ass_attribute21             => l_rec_curr_asg_dtls.ass_attribute21
                            ,p_ass_attribute22             => l_rec_curr_asg_dtls.ass_attribute22
                            ,p_ass_attribute23             => l_rec_curr_asg_dtls.ass_attribute23
                            ,p_ass_attribute24             => l_rec_curr_asg_dtls.ass_attribute24
                            ,p_ass_attribute25             => l_rec_curr_asg_dtls.ass_attribute25
                            ,p_ass_attribute26             => l_rec_curr_asg_dtls.ass_attribute26
                            ,p_ass_attribute27             => l_rec_curr_asg_dtls.ass_attribute27
                            ,p_ass_attribute28             => l_rec_curr_asg_dtls.ass_attribute28
                            ,p_ass_attribute29             => l_rec_curr_asg_dtls.ass_attribute29
                            ,p_ass_attribute30             => l_rec_curr_asg_dtls.ass_attribute30
                            ,p_cagr_grade_def_id             =>  l_cagr_grade_def_id
                            ,p_cagr_concatenated_segments    =>  l_cagr_concatenated_segments
                            ,p_concatenated_segments         =>  l_concatenated_segments
                            ,p_soft_coding_keyflex_id        =>  l_soft_coding_keyflex_id
                            ,p_comment_id                    =>  l_comment_id
                            ,p_effective_start_date          =>  l_effective_start_date
                            ,p_effective_end_date            =>  l_effective_end_date
                            ,p_no_managers_warning           =>  l_no_managers_warning
                            ,p_other_manager_warning         =>  l_other_manager_warning
                            ,p_hourly_salaried_warning       =>  l_hourly_salaried_warning
                            );
Line: 1495

                            hr_utility.set_location('Update of assignment complete',21);
Line: 1508

       hr_utility.set_location('Leaving procedure pqp_gb_ad_ee.update_ass_dff_col',22);
Line: 1512

            hr_utility.set_location('Proc: pqp_gb_ad_ee.update_ass_dff_col: Exception Section',23);
Line: 1514

END UPDATE_PSI_ASS_DFF_COL;
Line: 1536

				SELECT max(decode (piv.name, 'Category', peev.screen_entry_value)) Category,
               max(decode (piv.name,'Pension', peev.screen_entry_value)) Pension
         FROM pay_element_entry_values_f peev,
              pay_input_values_f piv
         WHERE peev.element_entry_id = p_element_type_id
           AND p_effective_start_date BETWEEN peev.effective_start_date AND peev.effective_end_date
           AND peev.input_value_id = piv.input_value_id
           AND piv.element_type_id = p_element_type_id
           AND p_effective_start_date BETWEEN piv.effective_start_date AND piv.effective_end_date
           AND piv.name in ('Category', 'Pension')
           AND piv.legislation_code = 'GB'
				  AND peev.screen_entry_value in ('F','G','S','A');
Line: 1551

select pap.per_information10 per_agg_flag
from
per_all_people_f pap,
pay_element_entries_f peef,
per_all_assignments_f paaf
where
peef.element_entry_id = p_element_entry_id
and paaf.assignment_id = peef.assignment_id
and paaf.person_id = pap.person_id
and p_effective_start_date BETWEEN peef.effective_start_date AND peef.effective_end_date
and p_effective_start_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
and p_effective_start_date BETWEEN pap.effective_start_date AND pap.effective_end_date;
Line: 1566

         SELECT element_name
         FROM pay_element_types_f
         WHERE element_type_id = p_element_type_id
         AND p_effective_start_date BETWEEN effective_start_date AND effective_end_date
           AND legislation_code = 'GB';
Line: 1574

         SELECT max (decode (piv.name, 'Type', peev.screen_entry_value)),
                max (decode (piv.name,'Reference', peev.screen_entry_value)),
								max (decode (piv.name,'Order Amount', peev.screen_entry_value)),
								max (decode (piv.name,'DEO Overriding Frequency', peev.screen_entry_value))
         FROM pay_element_entry_values_f peev,
              pay_input_values_f piv
         WHERE peev.element_entry_id = p_element_entry_id
           AND p_effective_start_date BETWEEN peev.effective_start_date AND peev.effective_end_date
           AND peev.input_value_id = piv.input_value_id
           AND piv.element_type_id = p_element_type_id
           AND p_effective_start_date BETWEEN piv.effective_start_date AND piv.effective_end_date
           AND piv.name in ('Type','Reference','Order Amount','DEO Overriding Frequency')
           AND piv.legislation_code = 'GB'
         ORDER BY piv.name DESC;
Line: 1668

selects any of these categories or pension.
*/
if l_element_name = 'NI' then

	hr_utility.trace('NI Element');
Line: 1714

				SELECT max(decode (piv.name, 'Category', peev.screen_entry_value)) Category,
               max(decode (piv.name,'Pension', peev.screen_entry_value)) Pension
         FROM pay_element_entry_values_f peev,
              pay_input_values_f piv
         WHERE peev.element_entry_id = p_element_entry_id
           AND p_effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
           AND peev.input_value_id = piv.input_value_id
           AND piv.element_type_id = p_element_type_id_o
           AND p_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
           AND piv.name in ('Category', 'Pension')
           AND piv.legislation_code = 'GB'
				  AND peev.screen_entry_value in ('F','G','S','A');
Line: 1729

         SELECT element_name
         FROM pay_element_types_f
         WHERE element_type_id = p_element_type_id_o
         AND p_effective_date BETWEEN effective_start_date AND effective_end_date
           AND legislation_code = 'GB';
Line: 1737

         SELECT max (decode (piv.name, 'Type', peev.screen_entry_value)),
                max (decode (piv.name,'Reference', peev.screen_entry_value)),
								max (decode (piv.name,'Order Amount', peev.screen_entry_value)),
								max (decode (piv.name,'DEO Overriding Frequency', peev.screen_entry_value))
         FROM pay_element_entry_values_f peev,
              pay_input_values_f piv
         WHERE peev.element_entry_id = p_element_entry_id
           AND p_effective_start_date BETWEEN peev.effective_start_date AND peev.effective_end_date
           AND peev.input_value_id = piv.input_value_id
           AND piv.element_type_id = p_element_type_id_o
           AND p_effective_start_date BETWEEN piv.effective_start_date AND piv.effective_end_date
           AND piv.name in ('Type','Reference','Order Amount','DEO Overriding Frequency')
           AND piv.legislation_code = 'GB'
         ORDER BY piv.name DESC;
Line: 1754

select pap.per_information10 per_agg_flag
from
per_all_people_f pap,
pay_element_entries_f peef,
per_all_assignments_f paaf
where
peef.element_entry_id = p_element_entry_id
and paaf.assignment_id = peef.assignment_id
and paaf.person_id = pap.person_id
and p_effective_start_date BETWEEN peef.effective_start_date AND peef.effective_end_date
and p_effective_start_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
and p_effective_start_date BETWEEN pap.effective_start_date AND pap.effective_end_date;
Line: 1769

      SELECT nvl(prrv.result_value,'Unknown')
      FROM   pay_run_results prr,
             pay_run_result_values prrv,
             pay_assignment_actions pac,
             pay_input_values_f piv ,
             pay_payroll_actions ppa
      WHERE  prr.run_result_id = prrv.run_result_id
      AND    prr.entry_type = 'E'
      AND    PRR.source_type  IN ('E', 'I')
      AND    prr.source_id = p_element_entry_id
      AND    pac.assignment_action_id = prr.assignment_action_id
      AND    pac.action_status IN ('C')
      and    ppa.action_type IN ('R','Q')
      AND    ppa.payroll_action_id  = pac.payroll_action_id
      AND    pac.assignment_action_id = (SELECT max(pac1.assignment_action_id)
                                 FROM  pay_assignment_actions pac1,
                                     pay_run_results prr1,
                                     pay_payroll_actions ppa1
                                 WHERE pac1.assignment_action_id = prr1.assignment_action_id
                                   AND   ppa1.payroll_action_id         = pac1.payroll_action_id
                                 AND   prr1.source_id = p_element_entry_id
                                 AND   pac1.action_status IN ('C')
                                 and   ppa1.action_type IN ('R','Q')
                                 and   prr1.entry_type = 'E'
                                 AND   PRR1.source_type IN ('E', 'I') )
      AND   piv.legislation_code = 'GB'
      AND   piv.name = 'Reference'
      AND   piv.input_value_id = prrv.input_value_id
      AND   p_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date ;
Line: 1802

      SELECT      PRR.run_result_id
      FROM  pay_run_results         PRR,
            pay_assignment_actions  ASA,
            pay_payroll_actions     PPA
      WHERE   PRR.source_id           = p_element_entry_id
      AND     PRR.source_type       IN ('E', 'I')
      AND     PRR.status            IN ('P', 'PA', 'R', 'O')
      AND   ASA.assignment_action_id      = PRR.assignment_action_id
      AND     asa.action_status IN ( 'C')
      and     ppa.action_type IN ('R','Q')
      AND   PPA.payroll_action_id         = ASA.payroll_action_id
      -- Check whether the run_result has been revered.
      AND     NOT EXISTS (SELECT null
                      FROM pay_run_results prr2
                      WHERE prr2.source_id = PRR.run_result_id
                      AND prr2.source_type IN ('R', 'V'));
Line: 1826

   SELECT hsck.segment1
     FROM hr_soft_coding_keyflex hsck,
          pay_all_payrolls_f papf,
          per_all_assignments_f paaf
    WHERE hsck.soft_coding_keyflex_id = papf.soft_coding_keyflex_id
      AND papf.payroll_id =paaf.payroll_id
      AND paaf.assignment_id = c_assignment_id
      AND p_effective_date between paaf.effective_start_date and paaf.effective_end_date
      AND p_effective_date between papf.effective_start_date and papf.effective_end_date;
Line: 1840

   SELECT pev.element_entry_id,
          paf.assignment_id
		  , pev.effective_start_date --10157179
     FROM pay_paye_element_entries_v pev,
          per_all_assignments_f paf,
          per_assignment_status_types past
    WHERE pev.assignment_id = paf.assignment_id
      AND past.assignment_status_type_id = paf.assignment_status_type_id
      AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN', 'TERM_ASSIGN')
	  /*    10157179. These two conditions are executed first in certain instances which makes the program to run for more than 20 hours
      AND pay_gb_eoy_archive.get_agg_active_end(c_assignment_id, c_tax_district, p_effective_date)
                                        =  pay_gb_eoy_archive.get_agg_active_end(paf.assignment_id, c_tax_district, p_effective_date)
      AND pay_gb_eoy_archive.get_agg_active_start(c_assignment_id, c_tax_district, p_effective_date)
                                        =  pay_gb_eoy_archive.get_agg_active_start(paf.assignment_id, c_tax_district, p_effective_date)    */
      AND p_effective_date between paf.effective_start_date and paf.effective_end_date
      AND paf.person_id =(SELECT person_id
                            FROM per_all_assignments_f paf
                           WHERE assignment_id = c_assignment_id
                             AND p_effective_date between paf.effective_start_date and paf.effective_end_date)
	  AND c_assignment_id <> paf.assignment_id; 	--10157179
Line: 1864

   SELECT input_value_id1,
          tax_code,
          input_value_id2,
          d_tax_basis,
          input_value_id3,
          d_refundable,
          input_value_id4,
          d_pay_previous,
          input_value_id5,
          d_tax_previous,
          input_value_id6,
          d_authority,
          entry_information1,   -- For bug 8548190
          entry_information2
     FROM pay_paye_element_entries_v
    WHERE element_entry_id = p_element_entry_id
      AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
Line: 1885

         SELECT max (decode (piv.name, 'Tax Code', peev.screen_entry_value))
         FROM pay_element_entry_values_f peev,
              pay_input_values_f piv
         WHERE peev.element_entry_id = p_element_entry_id
           AND p_effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
           AND peev.input_value_id = piv.input_value_id
           AND piv.element_type_id = p_element_type_id_o
           AND p_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
           AND piv.name in ('Tax Code')
           AND piv.legislation_code = 'GB'
         ORDER BY piv.name DESC;
Line: 2078

         SELECT assignment_id
           INTO l_paye_assg_id
           FROM pay_element_entries_f
          WHERE element_entry_id = p_element_entry_id
            AND p_effective_start_date BETWEEN effective_start_date AND effective_end_date;
Line: 2091

         SELECT papf.per_information10
           INTO l_paye_agg
           FROM per_all_people_f papf,
                per_all_assignments_f paaf
          WHERE paaf.assignment_id = l_paye_assg_id
            AND paaf.person_id = papf.person_id
            AND p_effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date
            AND p_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date;
Line: 2132

			 select count(*) into l_session_present from fnd_sessions ses
			 where ses.session_id = userenv('SESSIONID');
Line: 2135

				hr_utility.set_location('No session date so inserting ',18);
Line: 2136

				     insert into fnd_sessions (SESSION_ID, EFFECTIVE_DATE)
					 values(userenv('sessionid'), p_effective_date);
Line: 2190

						ELSIF substr(P_DATETRACK_MODE,1,6) = 'DELETE' then
							L_DATETRACK_MODE := 'UPDATE';
Line: 2221

                        hr_entry_api.update_element_entry
                        (p_dt_update_mode       => L_DATETRACK_MODE,
                         p_session_date         => P_EFFECTIVE_DATE,
                         p_element_entry_id     => g_element_entry_rec_tab(l_cnt).eeid,
                         p_input_value_id1      => l_input_value_id1,
                         P_entry_value1         => l_tax_code,
                         p_input_value_id2      => l_input_value_id2,
                         P_entry_value2         => l_d_tax_basis,
                         p_input_value_id3      => l_input_value_id3,
                         P_entry_value3         => l_d_refundable,
                         p_input_value_id4      => l_input_value_id4,
                         P_entry_value4         => l_d_pay_previous,
                         p_input_value_id5      => l_input_value_id5,
                         P_entry_value5         => l_d_tax_previous,
                         p_input_value_id6      => l_input_value_id6,
                         P_entry_value6         => l_authority,
                         P_entry_information_category => 'GB_PAYE',
                         P_entry_information1   =>  l_ele_information1,
                         P_entry_information2   =>  l_ele_information2
                        );
Line: 2250

                   g_element_entry_rec_tab.DELETE;
Line: 2277

selects any of these categories or pension.
*/
if l_element_name = 'NI' then
	hr_utility.trace('NI Element');