APPS.PAY_NL_LSS_FUNCTIONS SQL Statements

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

Line: 110

          SELECT asg.effective_start_date asg_start_date
          ,asg.effective_end_date asg_end_date
          FROM PER_ALL_ASSIGNMENTS_F asg
               ,PER_ASSIGNMENT_STATUS_TYPES past
          WHERE asg.assignment_id = p_other_assignment_id
          AND   past.per_system_status = 'ACTIVE_ASSIGN'
          AND   asg.assignment_status_type_id = past.assignment_status_type_id
      --  AND   asg.business_group_id    =  p_business_group
          AND   asg.effective_start_date <= p_period_end_date
          AND   NVL(asg.effective_end_date,p_period_end_date) >= p_period_start_date;

Line: 124

          SELECT global_name g_name, fnd_number.canonical_to_number(global_value) g_val FROM FF_GLOBALS_F
          WHERE legislation_code = 'NL' AND
                global_name in ('NL_AVERAGE_DAYS_WEEKLY', 'NL_AVERAGE_DAYS_4WEEKLY', 'NL_AVERAGE_DAYS_MONTHLY',
               'NL_AVERAGE_DAYS_QUARTERLY') AND
               (EFFECTIVE_START_DATE <= p_application_date and EFFECTIVE_END_DATE >= p_application_date);

Line: 238

SELECT defined_balance_id
  FROM pay_defined_balances pdb,
       pay_balance_types pbt,
       pay_balance_dimensions pbd
 WHERE pbt.balance_name = c_balance_name
   AND pbd.legislation_code = 'NL'
   AND pbd.DIMENSION_NAME = c_dimension_name
   AND pdb.balance_type_id = pbt.balance_type_id
   AND pdb.balance_dimension_id = pbd.balance_dimension_id;

Line: 249

 select min(effective_start_date) from
  per_all_assignments_f
 where assignment_id = assg_id;

Line: 258

SELECT assignment_id
 FROM per_all_assignments_f
 WHERE person_id = p_person_id
 AND assignment_status_type_id IN (SELECT assignment_status_type_id
                                     FROM per_assignment_status_types
                                    WHERE per_system_status = 'ACTIVE_ASSIGN'
                                      AND active_flag = 'Y')
 AND l_date BETWEEN effective_start_date and effective_end_date
 AND payroll_id IS NOT NULL
 AND NOT EXISTS (SELECT 1 from per_all_assignments_f
                 WHERE person_id = p_person_id
                   AND assignment_status_type_id IN (SELECT assignment_status_type_id
                                                      FROM per_assignment_status_types
                                                     WHERE per_system_status = 'TERM_ASSIGN'
                                                       AND active_flag = 'Y')
                   AND l_date BETWEEN effective_start_date and effective_end_date);

Line: 284

SELECT effective_start_date - 1 term_dt
      ,assignment_id
 FROM per_all_assignments_f
 WHERE person_id = p_person_id
 AND assignment_status_type_id IN (SELECT assignment_status_type_id
                                                      FROM per_assignment_status_types
                                                     WHERE per_system_status = 'TERM_ASSIGN'
                                                       AND active_flag = 'Y')
 AND effective_start_date BETWEEN l_start_date and l_end_date
 AND payroll_id IS NOT NULL
UNION
SELECT effective_end_date ,
       assignment_id
  FROM per_all_assignments_f asg
  WHERE person_id = p_person_id
  AND effective_end_date BETWEEN l_start_date and l_end_date
  AND payroll_id IS NOT NULL
  AND NOT EXISTS( SELECT 1
                     FROM per_all_assignments_f  asg1
                    WHERE person_id = p_person_id
                      AND effective_start_date = asg.effective_end_date + 1
                      AND asg.assignment_id = asg1.assignment_id );

Line: 308

 SELECT person_id
 FROM  per_all_assignments_f
 WHERE assignment_id     = p_assignment_id
 AND   business_group_id = p_business_group;

Line: 314

 SELECT pet.element_type_id, piv.name
 FROM pay_element_types_f pet, pay_input_values_f piv
 WHERE pet.element_name = 'Life Savings Scheme General Information'
 AND pet.element_type_id = piv.element_type_id
 AND piv.legislation_code = 'NL'
 AND upper(piv.name) = 'CURRENT YEAR COLUMN 6';

Line: 522

SELECT element_type_id
  FROM pay_element_types_f
 WHERE element_name = 'Life Savings Scheme General Information'
   AND legislation_code = 'NL'
   AND p_date_earned BETWEEN effective_start_date AND
                             effective_end_date;

Line: 602

   select unique(assignment_id) from per_all_assignments_f paaf, PER_ASSIGNMENT_STATUS_TYPES past
   where paaf.assignment_id = p_assg_id
   AND past.per_system_status = 'ACTIVE_ASSIGN'
   AND paaf.assignment_status_type_id = past.assignment_status_type_id
   and (to_char(effective_start_date,'YYYY') = p_year
        OR to_char(effective_end_date,'YYYY') = p_year);

Line: 611

   select GLOBAL_VALUE from ff_globals_f
   where global_name = 'PAY_NL_MAX_LCLD_PER_YEAR'
   and legislation_code = 'NL'
   and p_year between fnd_number.canonical_to_number(to_char(EFFECTIVE_START_DATE,'YYYY'))
   and fnd_number.canonical_to_number(to_char(EFFECTIVE_END_DATE,'YYYY'));

Line: 620

   SELECT defined_balance_id
    FROM pay_defined_balances pdb,
         pay_balance_types pbt,
         pay_balance_dimensions pbd
    WHERE pbt.balance_name = c_balance_name
    AND pbd.legislation_code = 'NL'
    AND pbd.DIMENSION_NAME = c_dimension_name
    AND pdb.balance_type_id = pbt.balance_type_id
    AND pdb.balance_dimension_id = pbd.balance_dimension_id;