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

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

Line: 29


select unique(paa1.assignment_id) assignment_id
from per_all_assignments_f paa
,per_all_assignments_f paa1
where paa.assignment_id=p_assignment_id
and paa.person_id=paa1.person_id;

Line: 38


select payroll_id
from per_all_assignments_f
where assignment_id=p_assignment_id
and p_effective_date between effective_start_date
	             and effective_end_date;

Line: 45


select min(paa.effective_start_date)
      ,max(paa.effective_end_date)
from per_all_assignments_f paa
,per_assignment_status_types pas
where assignment_id=p_assignment_id
and paa.assignment_status_type_id=pas.assignment_status_type_id
and pas.PER_SYSTEM_STATUS='ACTIVE_ASSIGN';

Line: 55


select START_DATE
       ,END_DATE
       ,PERIOD_NUM
from
per_time_periods
where payroll_id=l_payroll_id
and period_name like '%'||l_prev_year||'%';

Line: 67


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

Line: 75


select 	ptp.start_date
       ,ptp.end_date
from    pay_payroll_actions ppa
       ,per_time_periods ptp
where   ppa.payroll_action_id = c_pay_act_id
AND     ptp.time_period_id    = ppa.time_period_id;

Line: 85


select 	paa.assignment_action_id
from    pay_assignment_actions paa
       ,pay_payroll_actions ppa
where   paa.assignment_id     = c_assignment_id
and     paa.payroll_action_id = ppa.payroll_action_id
AND    ppa.action_type IN ('Q','R')
AND    ppa.action_status in ('C','P')
AND    ppa.date_earned  BETWEEN  c_start_date AND c_end_date
AND     paa.source_action_id IS NOT NULL
ORDER BY 1 desc;

Line: 970


  l_datetrack_update_mode		varchar2(2000);

Line: 980


  select asg.assignment_id
  ,asg.person_id
  ,asg.object_version_number
  ,asg.assignment_number
  ,asg.effective_start_date
  ,asg.effective_end_date
  , scl.segment12 last_year_sal
  , ast.user_status
  from per_all_assignments_f asg
  ,hr_soft_coding_keyflex scl
  , per_assignment_status_types ast
  where organization_id in
  (select pose.organization_id_child
  from per_org_structure_elements pose,per_org_structure_versions posv
  where
  posv.org_structure_version_id = pose.org_structure_version_id
  and posv.organization_structure_id=p_org_struct_id
  and posv.business_group_id = pose.business_group_id
  and posv.business_group_id=p_business_group_id
  UNION ALL
  select p_business_group_id FROM DUAL)
  and nvl(p_hr_org_id,organization_id)=organization_id
  and fnd_date.canonical_to_date(p_date) between effective_start_date and effective_end_date
  and asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
  and scl.segment12 is not null
  and ast.assignment_status_type_id = asg.assignment_status_type_id
  and asg.business_group_id = p_business_group_id
  and ast.PER_SYSTEM_STATUS='ACTIVE_ASSIGN';

Line: 1024


      l_datetrack_update_mode := 'CORRECTION';

Line: 1027


      l_datetrack_update_mode := 'UPDATE_CHANGE_INSERT';

Line: 1029


      l_datetrack_update_mode := 'UPDATE';

Line: 1042


      hr_nl_assignment_api.update_nl_emp_asg
	    (p_validate => FALSE
	    ,p_effective_date => fnd_date.canonical_to_date(p_date)
	    ,p_person_id => l_asg_details.person_id
	    ,p_datetrack_update_mode => l_datetrack_update_mode
	    ,p_assignment_id => l_asg_details.assignment_id
	    ,p_object_version_number => l_object_version_number
	    ,p_assignment_number => l_asg_details.assignment_number
	    ,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_last_year_salary => NULL
	    ,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
	    ,p_gsp_post_process_warning => l_gsp_post_process_warning
	    );

Line: 1105


Procedure update_assignments (p_assignment_id   IN NUMBER
			,p_person_id  		IN  NUMBER
			,p_effective_date 	IN  DATE
			,p_override_value  	IN  NUMBER
			,p_dt_update_mode       IN  VARCHAR2) IS

  CURSOR get_all_assignments (p_person_id NUMBER
                             ,p_effective_date DATE) IS
   select paa.assignment_id
  	,paa.effective_start_date
  	,paa.effective_end_date
  	,paa.object_version_number
  	,hsck.segment12
  from per_all_assignments_f paa
      ,hr_soft_coding_keyflex hsck
      ,per_assignment_status_types pas
  where  person_id=p_person_id
  and  paa.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id (+)
  and pas.assignment_status_type_id=paa.assignment_status_type_id
  and pas.PER_SYSTEM_STATUS='ACTIVE_ASSIGN'
  and pas.business_group_id IS NULL and pas.legislation_code IS NULL
  and p_effective_date between effective_start_date and effective_end_date;

Line: 1142


  l_datetrack_update_mode		varchar2(2000);

Line: 1148


hr_utility.set_location('Inside update_assignments: p_person_id'||p_person_id,900);

Line: 1149


hr_utility.set_location('Inside update_assignments: p_effective_date'||p_effective_date,900);

Line: 1150


hr_utility.set_location('Inside update_assignments: p_override_value'||p_override_value,900);

Line: 1156


  hr_utility.set_location('Inside update_assignments: l_object_version_number'||l_object_version_number,1000);

Line: 1157


  hr_utility.set_location('Inside update_assignments: p_override_value'||p_override_value,1000);

Line: 1159


/*  l_datetrack_update_mode:='UPDATE';

Line: 1167


  l_datetrack_update_mode:='CORRECTION';

Line: 1169


  l_datetrack_update_mode := 'UPDATE_CHANGE_INSERT';

Line: 1173


hr_utility.set_location('Inside l_datetrack_update_mode'||l_datetrack_update_mode,1200);

Line: 1177


hr_utility.set_location('Inside update_assignments: p_override_value'||fnd_number.number_to_canonical(p_override_value),1200);

Line: 1183


 hr_nl_assignment_api.update_nl_emp_asg
     (p_validate => FALSE
     ,p_effective_date => p_effective_date
     ,p_person_id => p_person_id
     ,p_datetrack_update_mode => p_dt_update_mode
     ,p_assignment_id => l_assignment.assignment_id
     ,p_object_version_number =>   l_assignment.object_version_number
     ,p_last_year_salary => fnd_number.number_to_canonical(p_override_value)
     ,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
     ,p_gsp_post_process_warning => l_gsp_post_process_warning);

Line: 1208


hr_utility.set_location('In update_assignments SQLERRM'||SQLERRM||'SQLCODE'||SQLCODE,2000);

Line: 1210


End update_assignments;