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

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

Line: 149


        p_mag_update_status() ;

Line: 645


	select hou.name
              ,hou.location_id
	from hr_organization_units hou
	where hou.business_group_id = P_BUSINESS_GROUP_ID
and hou.organization_id = TAX_UNIT_ID;

Line: 740


PROCEDURE P_MAG_UPDATE_STATUS IS
BEGIN
DECLARE
CURSOR c_person_id is
select
	 ppf.PERSON_ID
	,ppf.LAST_NAME		 LAST_NAME
	,ppf.FIRST_NAME      FIRST_NAME
	,hl.region_2		 STATE
From
	 per_all_people_f 		ppf

        ,hr_locations_all		hl
	,hr_soft_coding_keyflex	hscf
	,per_all_assignments_f	paf
	,per_periods_of_service	pps
	,hr_organization_information	hoi4
	,hr_organization_information	hoi3
	,hr_organization_information 	hoi2
	,hr_organization_information	hoi1
	,hr_organization_units	hou
Where
	pps.person_id			= ppf.person_id

And	fnd_date.canonical_to_date(P_REPORT_DATE)
	between 	pps.date_start and NVL(pps.actual_termination_date,C_END_OF_TIME)
And 	fnd_date.canonical_to_date(P_REPORT_DATE)
	between ppf.effective_start_date and ppf.effective_end_date
And	ppf.person_id			= paf.person_id
And 	fnd_date.canonical_to_date(P_REPORT_DATE)
	between 	paf.effective_start_date and paf.effective_end_date

and hscf.segment1 = to_char(hou.organization_id)
and hou.business_group_id = p_business_group_id
and hou.organization_id = NVL(p_tax_unit_id,hou.organization_id)
and hl.region_2 = DECODE(p_multi_state_1,'N',p_state_code,hl.region_2)
And	paf.soft_coding_keyflex_id	= hscf.soft_coding_keyflex_id
And	paf.assignment_type		= 'E'
And	paf.primary_flag		= 'Y'
And	paf.location_id			= hl.location_id


And    	ppf.business_group_id +0	= P_BUSINESS_GROUP_ID
And	ppf.per_information_category    = 'US'
And    	pps.date_start  		<= fnd_date.canonical_to_date(P_REPORT_DATE)
And     ppf.per_information7 	= 'INCL'
 and	hou.business_group_id		= ppf.business_group_id
and	hoi1.organization_id		= hou.organization_id
and	hoi1.org_information_context	= 'CLASS'
and 	hoi1.org_information1		= 'HR_LEGAL'
and	hoi1.org_information2		='Y'
and 	hoi2.organization_id(+)		= hou.organization_id
and	hoi2.org_information_context	='Employer Identification'
and	hoi3.organization_id(+) 	= hou.organization_id
and	hoi3.org_information_context(+)	= 'New Hire Reporting'
and	hoi4.organization_id(+)		= hou.organization_id
and	hoi4.org_information_context(+)	= 'State Tax Rules'
and	hoi4.org_information1(+)	= nvl(P_STATE_CODE,hoi4.org_information1(+))
UNION

select   ppf.PERSON_ID
	,ppf.LAST_NAME	         LAST_NAME
	,ppf.FIRST_NAME        	 FIRST_NAME
	,hl.region_2		 STATE
From
	per_all_people_f 		ppf

        ,hr_locations_all		hl
	,hr_soft_coding_keyflex		hscf
	,per_all_assignments_f		paf
	,per_periods_of_service		pps
        ,hr_organization_information	hoi4
	,hr_organization_information	hoi3
	,hr_organization_information 	hoi2
	,hr_organization_information	hoi1
	,hr_organization_units		hou
Where
	pps.person_id			= ppf.person_id

And	fnd_date.canonical_to_date(P_REPORT_DATE)
	between 	ppf.effective_start_date and ppf.effective_end_date
And	pps.actual_termination_date	IS NOT NULL
And	ppf.person_id			= paf.person_id
And 	not exists (select 1 from per_all_assignments_f paf2
   	where ppf.person_id = paf2.person_id
    	and fnd_date.canonical_to_date(P_REPORT_DATE)
   	between paf2.effective_start_date and paf2.effective_end_date
   	)
And	pps.date_start			= paf.effective_start_date

and hscf.segment1 = to_char(hou.organization_id)
and hou.business_group_id = p_business_group_id
and hou.organization_id = NVL(p_tax_unit_id,hou.organization_id)
and hl.region_2 = DECODE(p_multi_state_1,'N',p_state_code,hl.region_2)
And	paf.soft_coding_keyflex_id	= hscf.soft_coding_keyflex_id
And paf.assignment_type			= 'E'
And	paf.primary_flag		= 'Y'
And	paf.location_id			= hl.location_id


And     	ppf.business_group_id +0	= P_BUSINESS_GROUP_ID
And	ppf.per_information_category    	= 'US'
And    	pps.date_start  			<= fnd_date.canonical_to_date(P_REPORT_DATE)
And     ppf.per_information7 			= 'INCL'
 and	hou.business_group_id		= ppf.business_group_id
and	hoi1.organization_id		= hou.organization_id
and	hoi1.org_information_context	= 'CLASS'
and 	hoi1.org_information1		= 'HR_LEGAL'
and	hoi1.org_information2		='Y'
and 	hoi2.organization_id(+)		= hou.organization_id
and	hoi2.org_information_context	='Employer Identification'
and	hoi3.organization_id(+) 	= hou.organization_id
and	hoi3.org_information_context(+)	= 'New Hire Reporting'
and	hoi4.organization_id(+)		= hou.organization_id
and	hoi4.org_information_context(+)	= 'State Tax Rules'
and	hoi4.org_information1(+)	= nvl(P_STATE_CODE,hoi4.org_information1(+))
order by    4,2,3;

Line: 863


  l_name   	varchar(60) := 'P_MAG_UPDATE_STATUS';

Line: 880


	  UPDATE per_people_f
	  SET 	 per_information7	= 'DONE'
	  WHERE	 person_id = v_person_id
          AND    per_information7 = 'INCL';

Line: 925


/*srw.message('102', 'Called Status Update');*/null;

Line: 1139


	SELECT
	       distinct
	       hou.name                 	transmitter_name
	,      replace(hoi2.org_information1 ,'-',null)  trans_federal_id
	,      hou.organization_id     	trans_tax_unit_id
	,      hou.location_id         	trans_location_id
	FROM
	       hr_organization_information          hoi3
	,      hr_organization_information          hoi2
	,      hr_organization_information          hoi1
	,      hr_organization_units                hou
	WHERE
	       hou.business_group_id            = P_BUSINESS_GROUP_ID
	AND    hoi1.organization_id 		= hou.organization_id
	AND    hoi1.org_information_context 	= 'CLASS'
	AND    hoi1.org_information1 		= 'HR_LEGAL'
	AND    hoi1.org_information2 		= 'Y'
	AND    hoi2.organization_id(+) 		= hou.organization_id
	AND    hoi2.org_information_context 	= 'Employer Identification'
	AND    hoi3.organization_id 		= hou.organization_id
	AND    hoi3.org_information_context 	= 'New Hire Reporting'
	AND    hoi3.org_information2       	= 'Y'
    	;

Line: 1164


	SELECT
	       count(hou.organization_id)
	FROM
	       hr_organization_information          hoi3
	,      hr_organization_units                hou
	WHERE
	       hou.business_group_id            = P_BUSINESS_GROUP_ID
	AND    hoi3.organization_id 		= hou.organization_id
	AND    hoi3.org_information_context 	= 'New Hire Reporting'
	AND    hoi3.org_information2       	= 'Y'
    	;

Line: 1177


		select
	 	        count(ppf.person_id)

		From
			per_all_people_f 		ppf
			,per_all_assignments_f		paf
			,hr_soft_coding_keyflex		hscf
			,hr_locations_all		hl   			,per_jobs			job
			,per_periods_of_service 	pps
                        ,hr_organization_information          hoi4
                        ,hr_organization_information          hoi3
	                ,hr_organization_information          hoi2
	                ,hr_organization_information          hoi1
	                ,hr_organization_units                hou

                Where
			pps.person_id				= ppf.person_id
					And	fnd_date.canonical_to_date(P_REPORT_DATE)
			between pps.date_start and NVL(pps.actual_termination_date, C_END_OF_TIME)
		And	fnd_date.canonical_to_date(P_REPORT_DATE)
			between ppf.effective_start_date and ppf.effective_end_date
		And	ppf.person_id				= paf.person_id
		And 	fnd_date.canonical_to_date(P_REPORT_DATE)
			between paf.effective_start_date and paf.effective_end_date

		And	hscf.segment1			= to_char(hou.organization_id)
		and	hou.business_group_id		= P_BUSINESS_GROUP_ID
		and 	hou.organization_id		= NVL(P_TAX_UNIT_ID,hou.organization_id)
		and	hl.region_2			= DECODE(P_MULTI_STATE_1,'N',P_STATE_CODE,hl.region_2)
				And	paf.soft_coding_keyflex_id		= hscf.soft_coding_keyflex_id
		And 	paf.assignment_type			= 'E'
		And	paf.primary_flag			= 'Y'
		And	paf.location_id				= hl.location_id
		And	paf.job_id				= job.job_id(+)
		And	fnd_date.canonical_to_date(P_REPORT_DATE)	between job.date_from(+)
								and     nvl(job.date_to, C_END_OF_TIME)
		And     ppf.business_group_id	 		= P_BUSINESS_GROUP_ID
		And	ppf.per_information_category    	= 'US'
		And    	ppf.start_date  			<= fnd_date.canonical_to_date(P_REPORT_DATE)
		And     ppf.per_information7 	= 'INCL'                 And     hou.business_group_id           = ppf.business_group_id
                	        AND     hoi1.organization_id 		= hou.organization_id
	        AND     hoi1.org_information_context 	= 'CLASS'
	        AND     hoi1.org_information1 		= 'HR_LEGAL'
	        AND     hoi1.org_information2 		= 'Y'
	        AND     hoi2.organization_id(+) 	= hou.organization_id
	        AND     hoi2.org_information_context 	= 'Employer Identification'
	        AND     hoi3.organization_id(+) 	= hou.organization_id
	        AND     hoi3.org_information_context(+)	= 'New Hire Reporting'
	        AND     hoi4.organization_id(+)      = hou.organization_id
                AND     hoi4.org_information_context(+) = 'State Tax Rules'
                AND     hoi4.org_information1(+) = NVL(P_STATE_CODE,hoi4.org_information4(+))
                ;

Line: 1231


								select
	 		count(ppf.person_id)

		From
			per_all_people_f 		ppf
			,per_all_assignments_f		paf
			,hr_soft_coding_keyflex		hscf
			,hr_locations_all		hl   			,per_jobs			job
			,per_periods_of_service 	pps
                        ,hr_organization_information          hoi4
                        ,hr_organization_information          hoi3
	                ,hr_organization_information          hoi2
	                ,hr_organization_information          hoi1
	                ,hr_organization_units                hou

                Where
			pps.person_id				= ppf.person_id

		And	fnd_date.canonical_to_date(P_REPORT_DATE)
			between ppf.effective_start_date and ppf.effective_end_date
				And	ppf.person_id				= paf.person_id
		And 	not exists (select 1 from per_all_assignments_f paf2
				where ppf.person_id = paf2.person_id
				and fnd_date.canonical_to_date(P_REPORT_DATE)
				between paf2.effective_start_date and paf2.effective_end_date
			)
		And	pps.date_start				= paf.effective_start_date

		And	hscf.segment1			= to_char(hou.organization_id)
		and	hou.business_group_id		= P_BUSINESS_GROUP_ID
		and 	hou.organization_id		= NVL(P_TAX_UNIT_ID,hou.organization_id)
		and	hl.region_2			= DECODE(P_MULTI_STATE_1,'N',P_STATE_CODE,hl.region_2)
				And	paf.soft_coding_keyflex_id		= hscf.soft_coding_keyflex_id
		And 	paf.assignment_type			= 'E'
		And	paf.primary_flag			= 'Y'
		And	paf.location_id				= hl.location_id
               	And	paf.job_id				= job.job_id(+)
		And	fnd_date.canonical_to_date(P_REPORT_DATE)	between job.date_from(+)
								and     nvl(job.date_to, C_END_OF_TIME)
		And     ppf.business_group_id	 		= P_BUSINESS_GROUP_ID
		And	ppf.per_information_category    	= 'US'
		And    	ppf.start_date  	<= fnd_date.canonical_to_date(P_REPORT_DATE)
		And     ppf.per_information7 	= 'INCL' 		And     hou.business_group_id           = ppf.business_group_id
               	        AND     hoi1.organization_id 		= hou.organization_id
	        AND     hoi1.org_information_context 	= 'CLASS'
	        AND     hoi1.org_information1 		= 'HR_LEGAL'
	        AND     hoi1.org_information2 		= 'Y'
	        AND     hoi2.organization_id(+) 	= hou.organization_id
	        AND     hoi2.org_information_context 	= 'Employer Identification'
	        AND     hoi3.organization_id(+) 	= hou.organization_id
	        AND     hoi3.org_information_context(+)	= 'New Hire Reporting'
	        AND     hoi4.organization_id(+)      = hou.organization_id
                AND     hoi4.org_information_context(+) = 'State Tax Rules'
                AND     hoi4.org_information1(+) = NVL(P_STATE_CODE,hoi4.org_information4(+))
                ;

Line: 1368


	    /*srw.message('100', 'You have selected New York to be your reporting state, but have not identified a GRE as the transmitter for the New Hire report.');*/null;

Line: 1370


            /*srw.message('100', 'Please select one of your GREs as the transmitter for this report in the New Hire Reporting organization information type.');*/null;

Line: 1383


              /*srw.message('100', 'You have selected New York to be your reporting state, and have identified two GREs as the transmitter for the New Hire report.');*/null;

Line: 1385


              /*srw.message('100', 'Please select only one of your GREs as the transmitter for this report in the New Hire Reporting organization information type. ');*/null;

Line: 1533


		select
	 		 ppf.person_id
			,ppf.last_name 		LAST_NAME
			,ppf.first_name		FIRST_NAME
                        ,substr(ppf.middle_names,1,1) middle_name
                	,ppf.national_identifier
			,ppf.date_of_birth
                        ,pps.date_start
                        ,hl.region_2 	 	STATE

		From
			per_all_people_f 		ppf
			,per_all_assignments_f		paf
			,hr_soft_coding_keyflex		hscf
			,hr_locations_all		hl   			,per_jobs			job
			,per_periods_of_service 	pps

                Where
			pps.person_id				= ppf.person_id
					And	fnd_date.canonical_to_date(P_REPORT_DATE)
			between pps.date_start and NVL(pps.actual_termination_date, C_END_OF_TIME)
			And	fnd_date.canonical_to_date(P_REPORT_DATE)
			between ppf.effective_start_date and ppf.effective_end_date
				And	ppf.person_id				= paf.person_id
				And 	fnd_date.canonical_to_date(P_REPORT_DATE)
			between paf.effective_start_date and paf.effective_end_date
		And	hscf.segment1				in
				(SELECT to_char(hou.organization_id )
				FROM     hr_organization_units    hou
				WHERE    hou.business_group_id    = P_BUSINESS_GROUP_ID
 				)
		And	paf.soft_coding_keyflex_id		= hscf.soft_coding_keyflex_id
		And 	paf.assignment_type			= 'E'
		And	paf.primary_flag			= 'Y'
		And	paf.location_id				= hl.location_id
                And     ((P_STATE_CODE = 'FL' and  P_MULTI_STATE_1 <> 'Y' and hl.region_2 = 'FL')
    			 or (P_STATE_CODE = 'FL' and  P_MULTI_STATE_1 = 'Y')
			 or (P_STATE_CODE <> 'FL')
                        )
		And	paf.job_id				= job.job_id(+)
		And	fnd_date.canonical_to_date(P_REPORT_DATE)	between job.date_from(+)
								and     nvl(job.date_to, C_END_OF_TIME)
		And     ppf.business_group_id	 		= P_BUSINESS_GROUP_ID
		And	ppf.per_information_category    	= 'US'
		And    	ppf.start_date  			<= fnd_date.canonical_to_date(P_REPORT_DATE)
		And     ppf.per_information7 	is NULL
		UNION
								select
	 		 ppf.person_id
			,ppf.last_name 		LAST_NAME
			,ppf.first_name		FIRST_NAME
                        ,substr(ppf.middle_names,1,1) middle_name
                	,ppf.national_identifier
			,ppf.date_of_birth
                        ,pps.date_start
                        ,hl.region_2 		STATE

		From
			per_all_people_f 		ppf
			,per_all_assignments_f		paf
			,hr_soft_coding_keyflex		hscf
			,hr_locations_all		hl   			,per_jobs			job
			,per_periods_of_service 	pps

                Where
			pps.person_id				= ppf.person_id

		And	fnd_date.canonical_to_date(P_REPORT_DATE)
			between ppf.effective_start_date and ppf.effective_end_date
				And	ppf.person_id				= paf.person_id
		And 	not exists (select 1 from per_all_assignments_f paf2
				where ppf.person_id = paf2.person_id
				and fnd_date.canonical_to_date(P_REPORT_DATE)
				between paf2.effective_start_date and paf2.effective_end_date
			)
		And	pps.date_start				= paf.effective_start_date
		And	hscf.segment1				in
				(SELECT to_char(hou.organization_id )
				FROM     hr_organization_units    hou
				WHERE    hou.business_group_id    = P_BUSINESS_GROUP_ID
 				)
		And	paf.soft_coding_keyflex_id		= hscf.soft_coding_keyflex_id
		And 	paf.assignment_type			= 'E'
		And	paf.primary_flag			= 'Y'
		And	paf.location_id				= hl.location_id
                And     ((P_STATE_CODE = 'FL' and  P_MULTI_STATE_1 <> 'Y' and hl.region_2 = 'FL')
    			 or (P_STATE_CODE = 'FL' and  P_MULTI_STATE_1 = 'Y')
			 or (P_STATE_CODE <> 'FL')
                        )
		And	paf.job_id				= job.job_id(+)
		And	fnd_date.canonical_to_date(P_REPORT_DATE)	between job.date_from(+)
								and     nvl(job.date_to, C_END_OF_TIME)
		And     ppf.business_group_id	 		= P_BUSINESS_GROUP_ID
		And	ppf.per_information_category    	= 'US'
		And    	ppf.start_date  			<= fnd_date.canonical_to_date(P_REPORT_DATE)
		And     ppf.per_information7 	is NULL
		Order by   2,3;

Line: 1657


      fnd_file.put_line(1,'Warning : Please update the New Hire field.');