APPS.PAY_NL_SI_PKG SQL Statements

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

Line: 47

	select *
	from pqp_assignment_attributes_f paa,
	fnd_sessions ses
	where assignment_id = p_assignment_id
    and ses.session_id = userenv('sessionid')
    and ses.effective_date between paa.effective_start_date and paa.effective_end_date;

Line: 62

	  SELECT paei.AEI_INFORMATION4 SI_Status,
	  paei.AEI_INFORMATION3 SI_Class,
	  DECODE(paei.AEI_INFORMATION3,'AMI',0,1) si_class_order
	  FROM
	  per_assignment_extra_info  paei
	  WHERE
	  assignment_id = p_assignment_id
	  and paei.aei_information_category='NL_SII'
	  and (paei.AEI_INFORMATION3 = p_si_class  or
	       paei.AEI_INFORMATION3 = DECODE(p_si_class,'ZFW','AMI','ZW','AMI','WW','AMI','WAO','AMI',
                                              'ZVW','AMI','WGA','AMI','IVA','AMI','UFO','AMI',p_si_class))
	  and p_date_earned between
	  FND_DATE.CANONICAL_TO_DATE(paei.AEI_INFORMATION1)
	  and nvl(FND_DATE.CANONICAL_TO_DATE(paei.AEI_INFORMATION2),hr_general.END_OF_TIME)
	  order by si_class_order desc;

Line: 80

	  SELECT nvl(paei.AEI_INFORMATION2,'N')    ZVW_Excluded
	  FROM
	  per_assignment_extra_info  paei
	  WHERE assignment_id = p_assignment_id
	  and   paei.aei_information_category='NL_EXCL_ZVW'
      and   (to_char(fnd_date.canonical_to_date(paei.AEI_INFORMATION1),'RRRR') <= p_dt_earned
             AND nvl(to_char(fnd_date.canonical_to_date(paei.AEI_INFORMATION3),'RRRR'),'4712') >= p_dt_earned);

Line: 170

	  SELECT
	  DECODE(paei.AEI_INFORMATION3,'AMI',1,0) si_class_order
	  FROM
	  per_assignment_extra_info  paei
	  WHERE
	  assignment_id = p_assignment_id
	  and paei.aei_information_category='NL_SII'
	  and p_date_earned between
	  FND_DATE.CANONICAL_TO_DATE(paei.AEI_INFORMATION1)
	  and nvl(FND_DATE.CANONICAL_TO_DATE(paei.AEI_INFORMATION2),hr_general.END_OF_TIME)
	  order by si_class_order desc;

Line: 203

		select period_type
		from   pay_all_payrolls_f pap
		where  payroll_id = p_payroll_id;

Line: 350

		select date_start,date_end,time_start,time_end
		from per_absence_attendances_v
		where person_id=p_person_id
		and ((ABS_INFORMATION_CATEGORY='NL' and ABS_INFORMATION1='Y')
		OR  (ABS_INFORMATION_CATEGORY='NL_S' and ABS_INFORMATION2='Y'))
		and (((p_start_date between date_start and date_end)
		or (p_end_date between date_start and date_end))
		or ((date_start between p_start_date and p_end_date)
		or (date_end between p_start_date and p_end_date)));

Line: 378

		select person_id into l_person_id
		from per_all_assignments_f paa,
		fnd_sessions ses
		where paa.assignment_id =p_assignment_id
		and ses.session_id = userenv('sessionid')
		and ses.effective_date between paa.effective_start_date and paa.effective_end_date;

Line: 472

		select count(uci.value)
		from pay_user_tables put,
		pay_user_columns puc,
		pay_user_column_instances_f uci,
		fnd_sessions ses
		where put.user_table_id = puc.user_table_id
		and puc.business_group_id = p_business_group_id      -- Fix for bug 3977437
		and uci.user_column_id = puc.user_column_id
		and put.user_table_name = g_udt_name
		and puc.user_column_name = p_wrk_pattern
		and ses.session_id = userenv('sessionid')
		and ses.effective_date between uci.effective_start_date and uci.effective_end_date;

Line: 502

		select count(uci.value)
		from pay_user_tables put,
		pay_user_columns puc,
		pay_user_column_instances_f uci,
		fnd_sessions ses
		where put.user_table_id = puc.user_table_id
		and puc.business_group_id = p_business_group_id    -- Fix for bug 3977437
		and uci.user_column_id = puc.user_column_id
		and put.user_table_name = g_udt_name
		and puc.user_column_name = p_wrk_pattern
		and uci.value <> '0'
		and ses.session_id = userenv('sessionid')
		and ses.effective_date between uci.effective_start_date and uci.effective_end_date;

Line: 532

	   SELECT payroll_action_id
	   FROM   pay_assignment_actions
	   WHERE  assignment_action_id = c_assignment_action_id;

Line: 605

	   SELECT payroll_action_id
	   FROM   pay_assignment_actions
	   WHERE  assignment_action_id = c_assignment_action_id;

Line: 679

	   SELECT payroll_action_id
	   FROM   pay_assignment_actions
	   WHERE  assignment_action_id = c_assignment_action_id;

Line: 753

	   SELECT payroll_action_id
	   FROM   pay_assignment_actions
	   WHERE  assignment_action_id = c_assignment_action_id;

Line: 826

	   SELECT payroll_action_id
	   FROM   pay_assignment_actions
	   WHERE  assignment_action_id = c_assignment_action_id;

Line: 917

SELECT 	FND_NUMBER.CANONICAL_TO_NUMBER(hs.segment29)	 	segment29	,
	pas.effective_start_date 				effective_start_date,
	pas.effective_end_date 					effective_end_date
FROM
	hr_soft_coding_keyflex hs 	,
	per_all_assignments_f pas
WHERE
     pas.assignment_id		  	=	p_assignment_id 			AND
     hs.soft_coding_keyflex_id	  	=	pas.soft_coding_keyflex_id 		AND
     pas.effective_start_date 		<=     	p_period_end_date   			AND
     pas.effective_end_date		>=	p_period_start_date ;

Line: 1057

SELECT 	FND_NUMBER.CANONICAL_TO_NUMBER(scl.segment28)	 	segment28	,
        paa.frequency ,
	paa.effective_start_date 				effective_start_date,
	paa.effective_end_date 					effective_end_date
FROM
	hr_soft_coding_keyflex scl 	,
	per_all_assignments_f paa
WHERE
     paa.assignment_id		  	=	p_assignment_id 			AND
     scl.soft_coding_keyflex_id	  	=	paa.soft_coding_keyflex_id 		AND
     paa.effective_start_date 		<=     	p_period_end_date   			AND
     paa.effective_end_date		>=	p_period_start_date ;

Line: 1173

select 	START_DATE
       ,END_DATE
from
per_time_periods
where payroll_id=p_payroll_id
and p_effective_date between START_DATE and END_DATE;

Line: 1221

   /* If the selected method is the number of week days in a payroll period/year.  This is
   forced if there are multi assignments.  Then call relevant formula function.*/
  Max_SI_Days := GET_WEEK_DAYS (Period_Start_Date, Period_End_Date);

Line: 1225

  /* If the selected method is 5 days per week for the weeks worked. Then call relevant formula
   function */
  Max_SI_Days := GET_MAX_SI_DAYS (p_assignment_id,Period_Start_Date, Period_End_Date);

Line: 1338

select 	START_DATE
       ,END_DATE
from
per_time_periods
where payroll_id=p_payroll_id
and p_effective_date between START_DATE and END_DATE;

Line: 1356

   /* If the selected method is the number of week days in a payroll period/year.  This is
   forced if there are multi assignments.  Then call relevant formula function.*/
  Max_SI_Days := GET_WEEK_DAYS (Period_Start_Date, Period_End_Date);

Line: 1360

  /* If the selected method is 5 days per week for the weeks worked. Then call relevant formula
   function */
  Max_SI_Days := GET_MAX_SI_DAYS (p_assignment_id,Period_Start_Date, Period_End_Date);

Line: 1414

select 	START_DATE
       ,END_DATE
from
per_time_periods
where payroll_id=p_payroll_id
and p_effective_date between START_DATE and END_DATE;

Line: 1497

       SELECT MIN(asg.effective_start_date) asg_start_date
              ,MAX(asg.effective_end_date) asg_end_date
       FROM PER_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.effective_start_date <= p_period_end_date
       AND   NVL(asg.effective_end_date,p_period_end_date) >= p_period_start_date;

Line: 1508

       SELECT DISTINCT asg1.assignment_id asgid
       FROM per_assignments_f asg1
            ,per_assignments_f asg2
            ,pay_object_groups pog1
            ,pay_object_groups pog2
       WHERE asg1.person_id = asg2.person_id
       AND   asg2.assignment_id = p_assignment_id
       AND   asg1.assignment_id <> p_assignment_id
       AND   pog1.source_id = asg1.assignment_id
       AND   pog1.source_type = 'PAF'
       AND   pog2.source_id = asg2.assignment_id
       AND   pog2.source_type = 'PAF'
       AND   pog1.parent_object_group_id = pog2.parent_object_group_id;

Line: 1523

       SELECT MIN(asg.effective_start_date) per_start_date
              ,MAX(asg.effective_end_date) per_end_date
       FROM PER_ASSIGNMENTS_F asg,
            PER_ASSIGNMENT_STATUS_TYPES past,
            PAY_OBJECT_GROUPS pog
       WHERE asg.person_id in
       (select asg2.person_id from PER_ASSIGNMENTS_F asg2, PAY_OBJECT_GROUPS pog2
       where asg2.assignment_id = p_assignment_id
       and pog2.source_id = asg2.assignment_id
       and pog2.source_type = 'PAF'
       and pog.parent_object_group_id = pog2.parent_object_group_id)
       AND   past.per_system_status = 'ACTIVE_ASSIGN'
       AND   asg.assignment_status_type_id = past.assignment_status_type_id
       AND   asg.effective_start_date <= p_period_end_date
       AND   NVL(asg.effective_end_date,p_period_end_date) >= p_period_start_date
       AND   pog.source_id = asg.assignment_id
       AND   pog.source_type = 'PAF';

Line: 1567

select element_type_id
from pay_element_types_f
where
element_name = 'NL Tax and SI Proration Indicator'
and p_proration_start_date between effective_start_date and effective_end_date
and legislation_code = 'NL';

Line: 1576

select input_value_id from pay_input_values_f
where element_type_id = p_element_type_id
and p_proration_start_date between effective_start_date and effective_end_date
and legislation_code = 'NL';

Line: 1586

SELECT EEV.screen_entry_value
FROM
        pay_element_entry_values_f               EEV,
        pay_element_entries_f                    EE,
        pay_link_input_values_f                  LIV,
        pay_input_values_f                       INPUTV
WHERE   INPUTV.input_value_id                  = l_input_value_id
AND     p_proration_start_date BETWEEN INPUTV.effective_start_date AND INPUTV.effective_end_date
AND     INPUTV.element_type_id		     = l_element_type_id
AND     LIV.input_value_id                     = INPUTV.input_value_id
AND     p_proration_start_date BETWEEN LIV.effective_start_date AND LIV.effective_end_date
AND     EEV.input_value_id                     = INPUTV.input_value_id
AND     EEV.element_entry_id                   = EE.element_entry_id
AND     EEV.effective_start_date               = EE.effective_start_date
AND     EEV.effective_end_date                 = EE.effective_end_date
AND     EE.element_link_id                     = LIV.element_link_id
AND     EE.assignment_id                       = p_assignment_id
AND     p_proration_start_date BETWEEN EE.effective_start_date AND EE.effective_end_date
AND     nvl(EE.ENTRY_TYPE, 'E')                = 'E';

Line: 1832

select element_type_id
from pay_element_types_f
where
element_name = 'NL Tax and SI Proration Indicator'
and p_date_earned between effective_start_date and effective_end_date
and legislation_code = 'NL';

Line: 1841

select input_value_id from pay_input_values_f
where element_type_id = p_element_type_id
and p_date_earned between effective_start_date and effective_end_date
and legislation_code = 'NL';

Line: 1851

SELECT EEV.screen_entry_value
FROM
        pay_element_entry_values_f               EEV,
        pay_element_entries_f                    EE,
        pay_link_input_values_f                  LIV,
        pay_input_values_f                       INPUTV
WHERE   INPUTV.input_value_id                  = l_input_value_id
AND     p_date_earned BETWEEN INPUTV.effective_start_date AND INPUTV.effective_end_date
AND     INPUTV.element_type_id		     = l_element_type_id
AND     LIV.input_value_id                     = INPUTV.input_value_id
AND     p_date_earned BETWEEN LIV.effective_start_date AND LIV.effective_end_date
AND     EEV.input_value_id                     = INPUTV.input_value_id
AND     EEV.element_entry_id                   = EE.element_entry_id
AND     EEV.effective_start_date               = EE.effective_start_date
AND     EEV.effective_end_date                 = EE.effective_end_date
AND     EE.element_link_id                     = LIV.element_link_id
AND     EE.assignment_id                       = p_assignment_id
AND     p_date_earned BETWEEN EE.effective_start_date AND EE.effective_end_date
AND     nvl(EE.ENTRY_TYPE, 'E')                = 'E';

Line: 1925

       SELECT MIN(asg.effective_start_date) asg_start_date
              ,MAX(asg.effective_end_date) asg_end_date
       FROM PER_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.effective_start_date <= p_period_end_date
       AND   NVL(asg.effective_end_date,p_period_end_date) >= p_period_start_date;

Line: 1936

       SELECT DISTINCT asg1.assignment_id asgid
       FROM per_assignments_f asg1
            ,per_assignments_f asg2
            ,pay_object_groups pog1
            ,pay_object_groups pog2
       WHERE asg1.person_id = asg2.person_id
       AND   asg2.assignment_id = p_assignment_id
       AND   asg1.assignment_id <> p_assignment_id
       AND   pog1.source_id = asg1.assignment_id
       AND   pog1.source_type = 'PAF'
       AND   pog2.source_id = asg2.assignment_id
       AND   pog2.source_type = 'PAF'
       AND   pog1.parent_object_group_id = pog2.parent_object_group_id;

Line: 1952

       SELECT MIN(asg.effective_start_date) per_start_date
              ,MAX(asg.effective_end_date) per_end_date
       FROM PER_ASSIGNMENTS_F asg,
            PER_ASSIGNMENT_STATUS_TYPES past,
            PAY_OBJECT_GROUPS pog
       WHERE asg.person_id in
       (select asg2.person_id from PER_ASSIGNMENTS_F asg2, PAY_OBJECT_GROUPS pog2
       where asg2.assignment_id = p_assignment_id
       and pog2.source_id = asg2.assignment_id
       and pog2.source_type = 'PAF'
       and pog.parent_object_group_id = pog2.parent_object_group_id)
       AND   past.per_system_status = 'ACTIVE_ASSIGN'
       AND   asg.assignment_status_type_id = past.assignment_status_type_id
       AND   asg.effective_start_date <= p_period_end_date
       AND   NVL(asg.effective_end_date,p_period_end_date) >= p_period_start_date
       AND   pog.source_id = asg.assignment_id
       AND   pog.source_type = 'PAF';