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_CBS_FILE SQL Statements

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

Line: 40


  SELECT fnd_date.canonical_to_date(pay_core_utils.get_parameter('REPORTING_DATE',legislative_parameters))
        ,pay_core_utils.get_parameter('EMPLOYER_ID',legislative_parameters)
        ,pay_core_utils.get_parameter('ORG_STRUCT_ID',legislative_parameters)
        ,pay_core_utils.get_parameter('SI_PROVIDER_ID',legislative_parameters)
        ,pay_core_utils.get_parameter('MEDIUM_CODE',legislative_parameters)
        ,pay_core_utils.get_parameter('DENSITY',legislative_parameters)
        ,effective_date
        ,business_group_id
  FROM  pay_payroll_actions
  WHERE payroll_action_id = p_payroll_action_id;

Line: 144


select lei_information10,lei_information11
from hr_location_extra_info
where information_type = 'NL_POSTAL_ADDRESS'
and  location_id = (select location_id from hr_organization_units where organization_id = p_org_id);

Line: 170


	select max(pai.action_information4)
	from    pay_action_information pai
	where pai.action_information1 = fnd_number.number_to_canonical(p_employer_id)
	and   pai.action_information2 = fnd_number.number_to_canonical(p_si_provider_id)
	and  to_char(pai.effective_date,'YYYY') = to_char(p_reporting_date,'YYYY');

Line: 341


	select ff.context_id   context_id
	      , pact.context_value  Context_value
              , decode(context_value,'ZFW',0,'ZW',1,
               'WEWE',2,'WEWA',3,'WAOD',4,'WAOB',5,6) seq
	from   ff_contexts ff, pay_action_contexts pact
	where  ff.context_name   = 'SOURCE_TEXT'  and
	       ff.context_id = pact.context_id    and
	       pact.assignment_action_id = p_ass_act_id
	ORDER BY decode(context_value,'ZFW',0,'ZW',1,
               'WEWE',2,'WEWA',3,'WAOD',4,'WAOB',5,6)	;

Line: 444


	select pay_nl_si_pkg.get_si_status(p_assignment_id,p_date,'ZFW')
	from dual;

Line: 449


SELECT ORG_INFORMATION1 FROM HR_ORGANIZATION_INFORMATION,PER_ASSIGNMENT_EXTRA_INFO PAEI
WHERE  PAEI.ASSIGNMENT_ID = p_assignment_id
AND PAEI.AEI_INFORMATION_CATEGORY = 'NL_PHI'
 AND ORGANIZATION_ID = PAEI.AEI_INFORMATION3
AND ORG_INFORMATION_CONTEXT = 'NL_PHI_ORG_INFO'
AND p_date between fnd_date.canonical_to_date(PAEI.AEI_INFORMATION1) and nvl(fnd_date.canonical_to_date(PAEI.AEI_INFORMATION2),hr_general.end_of_time);

Line: 459


SELECT ORG_INFORMATION1
FROM HR_ORGANIZATION_INFORMATION HOI,PER_ASSIGNMENT_EXTRA_INFO PAEI
WHERE  PAEI.ASSIGNMENT_ID = p_assignment_id
AND PAEI.AEI_INFORMATION_CATEGORY = 'NL_IZA_INFO'
AND HOI.ORGANIZATION_ID = PAEI.AEI_INFORMATION3
AND HOI.ORG_INFORMATION_CONTEXT = 'NL_PHI_ORG_INFO'
AND p_date between fnd_date.canonical_to_date(PAEI.AEI_INFORMATION1) and nvl(fnd_date.canonical_to_date(PAEI.AEI_INFORMATION2),hr_general.end_of_time);

Line: 520


	select sck.segment6,
	SUM(decode(sign(p_reporting_end_date - paa.effective_end_date),-1,p_reporting_end_date,paa.effective_end_date)-decode(sign(paa.effective_start_date - p_reporting_start_date),-1,p_reporting_start_date,paa.effective_start_date)+1) Days
	from per_all_assignments_f paa,hr_soft_coding_keyflex sck
	where paa.assignment_id = p_assignment_id
	and   (paa.effective_start_date >= p_reporting_start_date or p_reporting_start_date between paa.effective_start_date and paa.effective_end_date)
	and   (paa.effective_end_date <= p_reporting_end_date or paa.effective_start_date <= p_reporting_end_date)
	and   sck.soft_coding_keyflex_id=paa.soft_coding_keyflex_id
	group  by sck.segment6
	order by Days desc;

Line: 564


	select sck.segment13,
	SUM(decode(sign(p_reporting_end_date - paa.effective_end_date),-1,p_reporting_end_date,paa.effective_end_date)-decode(sign(paa.effective_start_date - p_reporting_start_date),-1,p_reporting_start_date,paa.effective_start_date)+1) Days
	from per_all_assignments_f paa,hr_soft_coding_keyflex sck
	where paa.assignment_id = p_assignment_id
	and   (paa.effective_start_date >= p_reporting_start_date or p_reporting_start_date between paa.effective_start_date and paa.effective_end_date)
	and   (paa.effective_end_date <= p_reporting_end_date or paa.effective_start_date <= p_reporting_end_date)
	and   sck.soft_coding_keyflex_id=paa.soft_coding_keyflex_id
	group  by sck.segment13
	order by Days desc;

Line: 605


	select sck.segment6,paa.employment_category,
	SUM(decode(sign(p_reporting_end_date - paa.effective_end_date),-1,p_reporting_end_date,paa.effective_end_date)-decode(sign(paa.effective_start_date - p_reporting_start_date),-1,p_reporting_start_date,paa.effective_start_date)+1) Days
	from per_all_assignments_f paa,hr_soft_coding_keyflex sck
	where paa.assignment_id = p_assignment_id
	and   (paa.effective_start_date >= p_reporting_start_date or p_reporting_start_date between paa.effective_start_date and paa.effective_end_date)
	and   (paa.effective_end_date <= p_reporting_end_date or paa.effective_start_date <= p_reporting_end_date)
	and   sck.soft_coding_keyflex_id=paa.soft_coding_keyflex_id
	group  by sck.segment6,paa.employment_category
	order by Days desc;

Line: 662


select sck.segment28, paa.frequency
from PER_ALL_ASSIGNMENTS_F paa,HR_SOFT_CODING_KEYFLEX sck
where paa.assignment_id = l_assignment_id
and   paa.SOFT_CODING_KEYFLEX_ID = sck.SOFT_CODING_KEYFLEX_ID
and   l_reporting_date between paa.effective_start_date and paa.effective_end_date;

Line: 754


select AEI_INFORMATION5 from per_assignment_extra_info aei
WHERE aei.information_type like 'NL_CADANS_INFO' and aei.assignment_id = l_assignment_id
and  p_rep_date between fnd_date.canonical_to_date(aei.AEI_INFORMATION1) and nvl(fnd_date.canonical_to_date(aei.AEI_INFORMATION2),hr_general.end_of_time);

Line: 790


Select sequence from per_grades pg
Where pg.grade_id = p_grade_id
And pg.business_group_id = p_business_group_id;

Line: 796


Select max(sequence) from per_spinal_point_steps_f psps
Where psps.grade_spine_id = (select grade_spine_id from per_grade_spines_f pgs
			     Where grade_id = p_grade_id
			     And p_reporting_date between pgs.effective_start_date and pgs.effective_end_date
			     and pgs.business_Group_id = p_businesS_group_id)
And p_reporting_date between psps.effective_start_date and psps.effective_end_date
And psps.business_group_id = p_business_group_id;

Line: 807


Select sequence
from per_spinal_point_steps_f psps
Where psps.grade_spine_id = (select grade_spine_id from per_grade_spines_f pgs
			    Where grade_id = p_grade_id
			    And p_reporting_date between pgs.effective_start_date and  pgs.effective_end_date
			    and pgs.business_Group_id = p_business_group_id)
And p_reporting_date between psps.effective_start_date and psps.effective_end_date
And psps.business_group_id = p_business_group_id
And psps.step_id = (select step_id
		    from per_spinal_point_placements_f  psp
		    where assignment_id = p_assignment_id
		    and p_reporting_date between psp.effective_start_date and psp.effective_end_date
		    and psp.business_group_id = p_business_group_id);

Line: 881


select date_earned
from pay_payroll_actions ppa,pay_assignment_Actions paa
where
ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = p_max_assgt_act_id;

Line: 888


select prr.run_result_id
from pay_run_results prr
where
prr.element_type_id=p_element_type_id and
prr.assignment_action_id=p_assignment_action_id;

Line: 939


|Description    : This procedure returns a sql string to select a range of 	|
|		  assignments eligible for archival		  		|
*********************************************************************************/

Procedure RANGE_CODE (pactid    IN    NUMBER
                     ,sqlstr    OUT   NOCOPY VARCHAR2) IS
--
--      Variables for GET ALL PARAMETERS
--
        l_business_group_id		 NUMBER;

Line: 1154


sqlstr := 'SELECT DISTINCT person_id
	FROM  per_people_f ppf
	,pay_payroll_actions ppa
	WHERE ppa.payroll_action_id = :payroll_action_id
	AND   ppa.business_group_id = ppf.business_group_id
        ORDER BY ppf.person_id';

Line: 1178


       sqlstr := 'SELECT DISTINCT person_id
       FROM  per_people_f ppf
       ,pay_payroll_actions ppa
       WHERE ppa.payroll_action_id = :payroll_action_id
       AND   1 = 2
       AND   ppa.business_group_id = ppf.business_group_id
       ORDER BY ppf.person_id';

Line: 1248


	select max(asg.effective_end_date) asg_end_date
	from   per_all_assignments_f asg,
	per_assignment_status_types past
	where  asg.assignment_id = p_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_rep_date
	and    asg.effective_end_date = p_rep_date;

Line: 1293


Select
        paa.organization_id,
        paa.soft_coding_keyflex_id,
        pap.person_id,
        paa.assignment_id,
        pap.last_name,
        paa.assignment_number,
        pap.full_name,
        pap.Date_of_Birth,
        pap.national_identifier,
	paa.grade_id
from
 per_all_people_f pap
,per_all_assignments_f paa
,hr_soft_coding_keyflex scl_flx
where
pap.business_group_id =p_business_group_id
and pap.person_id = paa.person_id
and paa.person_id BETWEEN p_start_person_id AND p_end_person_id
and scl_flx.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
and p_reporting_end_Date between pap.effective_start_date and pap.effective_end_date
and paa.effective_start_date =
		(
		SELECT MIN(asg.effective_start_date)
		FROM per_assignment_status_types past, per_all_assignments_f asg
		WHERE asg.assignment_id = paa.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_reporting_End_Date
		and   nvl(asg.effective_end_date, p_reporting_End_Date) >= p_reporting_Start_Date
		)
and p_employer_id in
(select hr_nl_org_info.get_tax_org_id(p_org_struct_version_id,paa.organization_id) from dual)
and not exists
(select 1
from
pay_action_information ee_ats
WHERE ee_ats.action_context_type='AAP'
AND ee_ats.action_information_category = 'NL CBS EMPLOYEE DETAILS'
AND ee_ats.action_information1  =p_employer_id
AND ee_ats.action_information4  =pap.person_id
AND ee_ats.action_information2  =p_si_provider_id
AND ee_ats.action_information3  =paa.assignment_id
AND ee_ats.effective_date       =p_reporting_end_date);

Line: 1596


	    SELECT pay_assignment_actions_s.NEXTVAL
	    INTO   l_asg_act_id
	    FROM   dual;

Line: 1604


       	    -- hr_utility.set_location('Before Inserting new assignment action id',600);

Line: 1607


       	    -- hr_utility.set_location('Inserted new assignment action id',600);