APPS.HR_NL_ORG_INFO SQL Statements

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

Line: 9

			SELECT p_org_id organization_id_parent
				   ,0 lev
			FROM dual
			UNION
			SELECT organization_id_parent
				   ,level
			FROM
			(SELECT organization_id_parent
					,organization_id_child
			  FROM per_org_structure_elements
		   WHERE org_structure_version_id = latest_named_hierarchy_vers(p_org_id)
			)
			START WITH organization_id_child    = p_org_id
			CONNECT BY PRIOR organization_id_parent   = organization_id_child
	ORDER BY lev;

Line: 35

		SELECT TO_NUMBER(inf.org_information1) organization_structure_id
		FROM   hr_organization_information inf
			  ,hr_all_organization_units   org
		WHERE  org.organization_id         = vp_organization_id
		  AND  inf.organization_id         = org.business_group_id
		  AND  inf.org_information_context = 'NL_BG_INFO'
		  AND  inf.org_information1        IS NOT NULL;

Line: 78

		SELECT sv.org_structure_version_id, sv.version_number
		FROM   per_org_structure_versions  sv
			  ,fnd_sessions                ses
		WHERE  sv.organization_structure_id = named_hierarchy(vp_organization_id)
		  AND  ses.session_id               = USERENV('sessionid')
		  AND  ses.effective_date BETWEEN sv.date_from
		  AND NVL(sv.date_to, Hr_general.End_Of_time)
		ORDER BY sv.version_number DESC;

Line: 125

		SELECT se.organization_id_child
		FROM   per_org_structure_elements se
		WHERE  se.org_structure_version_id =
			   latest_named_hierarchy_vers(vp_organization_id)
		  AND  (se.organization_id_parent  = vp_organization_id OR
				se.organization_id_child   = vp_organization_id);

Line: 185

	  select
			  substr(org_information1, 1, 30),
			  substr(org_information2, 1, 30)
	  from
	  hr_organization_units d,
	  hr_organization_information e
	  where
	  d.organization_id = e.organization_id and
	  d.organization_id = l_org_id and
	  e.org_information_context in ('NL_ORG_INFORMATION');

Line: 267

	 SELECT Organization_id,
	 org_information1 Region
	 FROM Hr_Organization_information
	 WHERE Organization_ID = vp_Organization_ID
	 AND Org_Information_Context='NL_ORG_INFORMATION';

Line: 278

	 SELECT vp_Organization_ID Organization_id,
			0 lev
	 FROM dual
	 UNION
	 SELECT organization_id_parent
	  ,level lev
	 FROM
	 (
	 SELECT organization_id_parent
	 ,organization_id_child
	 FROM per_org_structure_elements
	 WHERE org_structure_version_id =
	 hr_nl_org_info.latest_named_hierarchy_vers(vp_Organization_ID)
	 )
	 START WITH organization_id_child= vp_Organization_ID
	 CONNECT BY PRIOR organization_id_parent= organization_id_child
	 ORDER BY lev;

Line: 348

		 select pae.aei_information8 provider,
		 decode(pae.aei_information3,'AMI',0,1) si_class_order
		 from per_assignment_extra_info pae
		 ,fnd_sessions s
		 where assignment_id = l_assgn_id
		 and (pae.aei_information3=decode (l_si_type,'WEWE','WW','WEWA','WW',
						'WAOB','WAO','WAOD','WAO','PRIVATE_HEALTH','ZFW',l_si_type) or
		 pae.AEI_INFORMATION3 = DECODE(l_si_type,'WEWA','AMI','WEWE','AMI','WAOD','AMI','WAOB','AMI',
						'ZFW','AMI','PRIVATE_HEALTH','AMI','ZW','AMI',
						'ZVW','AMI','WGA','AMI','IVA','AMI','UFO','AMI',l_si_type))
		and s.effective_date between
		fnd_date.canonical_to_date(pae.aei_information1)
		and nvl(fnd_date.canonical_to_date(pae.aei_information2),s.effective_date) AND
		s.session_id=userenv('sessionid')
		order by si_class_order desc;

Line: 373

		 select
		 e.org_information_id,
		 e.org_information4 provider,nvl(e.org_information7,'N') p_flag,
		 decode(e.org_information3,'AMI',0,1) si_class_order
		 from
		 hr_organization_information e
		 ,fnd_sessions s
		 where
		 e.organization_id=l_org_id and
		 e.org_information_context = 'NL_SIP' and
		 (e.org_information3=DECODE(l_si_type,'WEWE','WW','WEWA','WW','WAOB','WAO','WAOD','WAO',
							 'PRIVATE_HEALTH','ZFW',l_si_type) or
		 e.org_information3 = DECODE(l_si_type,'WEWE','AMI','WEWA','AMI','WAOB','AMI','WAOD','AMI',
						'ZFW','AMI','PRIVATE_HEALTH','AMI','ZW','AMI',
						'ZVW','AMI','WGA','AMI','IVA','AMI','UFO','AMI',l_si_type)) and
		 e.org_information4 = NVL(l_uwv_id,e.org_information4)
		 and s.effective_date between
		   fnd_date.canonical_to_date(e.org_INFORMATION1)
		   and nvl(fnd_date.canonical_to_date(e.org_INFORMATION2),s.effective_date) AND
		 s.session_id=userenv('sessionid')
		 order by p_flag desc,si_class_order desc;

Line: 492

		 select pae.aei_information8 provider,
		 decode(pae.aei_information3,'AMI',0,1) si_class_order
		 from per_assignment_extra_info pae
		 ,fnd_sessions s
		 where assignment_id = l_assgn_id
		 and (pae.aei_information3=decode (l_si_type,'WEWE','WW','WEWA','WW',
						'WAOB','WAO','WAOD','WAO','PRIVATE_HEALTH','ZFW',l_si_type) or
		 pae.aei_information3 = DECODE(l_si_type,'WEWE','AMI','WEWA','AMI',
						'WAOB','AMI','WAOD','AMI',
						'ZFW','AMI','PRIVATE_HEALTH','AMI','ZW','AMI',
						'ZVW','AMI','WGA','AMI','IVA','AMI','UFO','AMI',l_si_type))
		and s.effective_date between
		fnd_date.canonical_to_date(pae.aei_information1)
		and nvl(fnd_date.canonical_to_date(pae.aei_information2),s.effective_date) AND
		s.session_id=userenv('sessionid')
		order by si_class_order desc;

Line: 516

		 select
		 e.org_information4 provider,nvl(e.org_information7,'N') p_flag,
		 decode(e.org_information3,'AMI',0,1) si_class_order
		 from
		 hr_organization_information e
		 ,fnd_sessions s
		 where
		 e.organization_id=l_org_id and
		 e.org_information_context = 'NL_SIP' and
		 (e.org_information3=DECODE(l_si_type,'WEWE','WW','WEWA','WW','WAOB','WAO','WAOD','WAO',
							 'PRIVATE_HEALTH','ZFW',l_si_type) or
		 e.org_information3 = DECODE(l_si_type,'WEWE','AMI','WEWA','AMI','WAOB','AMI','WAOD','AMI',
						'ZFW','AMI','PRIVATE_HEALTH','AMI','ZW','AMI',
						'ZVW','AMI','WGA','AMI','IVA','AMI','UFO','AMI',l_si_type)) and
		 s.effective_date between
		   fnd_date.canonical_to_date(e.org_INFORMATION1)
		   and nvl(fnd_date.canonical_to_date(e.org_INFORMATION2),s.effective_date) AND
		 s.session_id=userenv('sessionid')
		 order by p_flag desc,si_class_order desc;

Line: 625

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

Line: 661

		 select
		 e.org_information4 provider
		 from
		 hr_organization_information e
		 where
		 e.organization_id=l_org_id and
		 e.org_information_context = 'NL_SIP' and
		 e.org_information4 =l_uwv_org_id ;

Line: 722

		select pae.assignment_extra_info_id,
		decode(pae.aei_information3,'AMI',0,1) si_class_order
		from per_assignment_extra_info pae
		,fnd_sessions s
		where assignment_id = l_assgn_id
		and (pae.aei_information3=decode (l_si_type,'WEWE','WW','WEWA','WW',
					'WAOB','WAO','WAOD','WAO','PRIVATE_HEALTH','ZFW',l_si_type) or
		pae.AEI_INFORMATION3 = DECODE(l_si_type,'WEWA','AMI','WEWE','AMI','WAOD','AMI','WAOB','AMI',
					'ZFW','AMI','PRIVATE_HEALTH','AMI','ZW','AMI',
					'ZVW','AMI','WGA','AMI','IVA','AMI','UFO','AMI',l_si_type))
		and s.effective_date between
		fnd_date.canonical_to_date(pae.aei_information1)
		and nvl(fnd_date.canonical_to_date(pae.aei_information2),s.effective_date)
		and session_id = userenv('sessionid')
		order by si_class_order desc;

Line: 755

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

Line: 766

	 select
	 e.org_information_id,
	 e.org_information5 Avg_days_Per_Month
	 from
	 hr_organization_information e
	 where
	 e.organization_id=l_org_id and
	 e.org_information_context= 'NL_ORG_INFORMATION'
	 and e.org_information5 IS NOT NULL;

Line: 852

	 select
	 e.org_information_id,
	 e.org_information4 tax_information
	 from
	 hr_organization_information e
	 where
	 e.organization_id=l_org_id and
	 e.org_information_context= 'NL_ORG_INFORMATION'
	 and e.org_information3 IS NOT NULL
	 and e.org_information4 IS NOT NULL;

Line: 869

	SELECT tax_org_id,lev from hr_organization_information e,(
		SELECT l_org_id tax_org_id,0 lev from dual
		UNION
		SELECT distinct organization_id_parent
				   ,level
			FROM (
				SELECT distinct organization_id_parent, organization_id_child
				FROM per_org_structure_elements pose
				 where   pose.org_structure_version_id = l_org_struct_version_id)
		START WITH organization_id_child    = l_org_id
		CONNECT BY PRIOR organization_id_parent   = organization_id_child)
	where
	e.organization_id=tax_org_id and
	e.org_information_context= 'NL_ORG_INFORMATION'
	and e.org_information3 IS NOT NULL
	and e.org_information4 IS NOT NULL
	ORDER BY lev;

Line: 930

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

Line: 941

         select
         e.org_information_id,
         e.org_information7 working_hours
         from
         hr_organization_information e
         where
         e.organization_id=l_org_id and
         e.org_information_context= 'NL_ORG_INFORMATION'
         and e.org_information7 IS NOT NULL;

Line: 1023

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

Line: 1034

	 select
	 e.org_information_id,
	 e.org_information8 Part_Time_Percentage_Method
	 from
	 hr_organization_information e
	 where
	 e.organization_id=l_org_id and
	 e.org_information_context= 'NL_ORG_INFORMATION'
	 and e.org_information8 IS NOT NULL;

Line: 1120

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

Line: 1131

	 select
	 e.org_information_id,
	 e.org_information9 Lunar_5_Week_Month_Wage_Method
	 from
	 hr_organization_information e
	 where
	 e.organization_id=l_org_id and
	 e.org_information_context= 'NL_ORG_INFORMATION'
	 and e.org_information9 IS NOT NULL;

Line: 1215

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

Line: 1227

         select
         e.org_information_id,
         e.org_information10 Proration_Tax_Table
         from
         hr_organization_information e
         where
         e.organization_id=l_org_id and
         e.org_information_context= 'NL_ORG_INFORMATION'
         and e.org_information10 IS NOT NULL;

Line: 1333

		 select pae.aei_information8 provider,
		 decode(pae.aei_information3,'AMI',0,1) si_class_order
		 from per_assignment_extra_info pae
		 ,fnd_sessions s
		 where assignment_id = l_assgn_id
		 and (pae.aei_information3=decode (l_si_type,'WEWE','WW','WEWA','WW',
						'WAOB','WAO','WAOD','WAO','PRIVATE_HEALTH','ZFW',l_si_type) or
		 pae.aei_information3 = DECODE(l_si_type,'WEWE','AMI','WEWA','AMI',
						'WAOB','AMI','WAOD','AMI',
						'ZFW','AMI','PRIVATE_HEALTH','AMI','ZW','AMI',
						'ZVW','AMI','WGA','AMI','IVA','AMI','UFO','AMI',l_si_type))
		and s.effective_date between
		fnd_date.canonical_to_date(pae.aei_information1)
		and nvl(fnd_date.canonical_to_date(pae.aei_information2),s.effective_date) AND
		s.session_id=userenv('sessionid')
		order by si_class_order desc;

Line: 1357

		 select
		 e.org_information4 provider,nvl(e.org_information7,'N') p_flag,
		 decode(e.org_information3,'AMI',0,1) si_class_order
		 from
		 hr_organization_information e
		 ,fnd_sessions s
		 where
		 e.organization_id=l_org_id and
		 e.org_information_context = 'NL_SIP' and
		 (e.org_information3=DECODE(l_si_type,'WEWE','WW','WEWA','WW','WAOB','WAO','WAOD','WAO',
							 'PRIVATE_HEALTH','ZFW',l_si_type) or
		 e.org_information3 = DECODE(l_si_type,'WEWE','AMI','WEWA','AMI','WAOB','AMI','WAOD','AMI',
						'ZFW','AMI','PRIVATE_HEALTH','AMI','ZW','AMI',
						'ZVW','AMI','WGA','AMI','IVA','AMI','UFO','AMI',l_si_type)) and
		 s.effective_date between
		   fnd_date.canonical_to_date(e.org_INFORMATION1)
		   and nvl(fnd_date.canonical_to_date(e.org_INFORMATION2),s.effective_date) AND
		 s.session_id=userenv('sessionid')
		 order by p_flag desc,si_class_order desc;

Line: 1472

	select pose.organization_id_child employer
	from per_org_structure_elements pose,hr_organization_information e
	where pose.org_structure_version_id = P_Org_Struct_Version_Id
	and e.organization_id=pose.organization_id_child
	and ((e.org_information_context= 'NL_ORG_INFORMATION'
	and e.org_information3 IS NOT NULL
	and e.org_information4 IS NOT NULL)
	or (e.org_information_context= 'NL_LE_TAX_DETAILS'
	and e.org_information1 IS NOT NULL
	and e.org_information2 IS NOT NULL))
	start with pose.organization_id_parent = p_top_org_id
	connect by prior pose.organization_id_child = pose.organization_id_parent
	union
	select to_number(p_top_org_id) employer from dual;

Line: 1513

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

Line: 1524

	 select
	 e.org_information_id,
	 e.org_information11 Parental_Leave_Perc
	 from
	 hr_organization_information e
	 where
	 e.organization_id=l_org_id and
	 e.org_information_context= 'NL_ORG_INFORMATION'
	 and e.org_information11 IS NOT NULL;

Line: 1608

         select
         e.org_information_id,
         e.org_information15 cbs_reporting_frequency
         from
         hr_organization_information e
         where
         e.organization_id=l_org_id and
         e.org_information_context= 'NL_ORG_INFORMATION'
         and e.org_information15 IS NOT NULL;

Line: 1686

         select
         e.org_information_id,
         e.org_information16 cbs_cust_number
         from
         hr_organization_information e
         where
         e.organization_id=l_org_id and
         e.org_information_context= 'NL_ORG_INFORMATION'
         and e.org_information16 IS NOT NULL;

Line: 1758

         select
         e.org_information_id,
         e.org_information18 cbs_company_unit
         from
         hr_organization_information e
         where
         e.organization_id=l_org_id and
         e.org_information_context= 'NL_ORG_INFORMATION'
         and e.org_information18 IS NOT NULL;

Line: 1834

         select
         e.org_information_id,
         e.org_information17 cbs_public_sector_org
         from
         hr_organization_information e
         where
         e.organization_id=l_org_id and
         e.org_information_context= 'NL_ORG_INFORMATION'
         and e.org_information17 IS NOT NULL;

Line: 1909

         select
         e.org_information_id,
         e.org_information19 full_sick_wage_paid
         from
         hr_organization_information e
         where
         e.organization_id=l_org_id and
         e.org_information_context= 'NL_ORG_INFORMATION'
         and e.org_information19 IS NOT NULL;

Line: 1980

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

Line: 1987

         select
         e.org_information_id,
         e.org_information20 IZA_Weekly_Hours
         from
         hr_organization_information e
         where
         e.organization_id=l_org_id and
         e.org_information_context= 'NL_ORG_INFORMATION'
         and e.org_information20 IS NOT NULL;

Line: 2069

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

Line: 2077

         select
         e.org_information_id,
         e.org_information12 IZA_Monthly_Hours
         from
         hr_organization_information e
         where
         e.organization_id=l_org_id and
         e.org_information_context= 'NL_ORG_INFORMATION'
         and e.org_information12 IS NOT NULL;

Line: 2160

 	 select
 	 e.org_information_id,
 	 e.org_information1 iza_information
 	 from
 	 hr_organization_information e
 	 where
 	 e.organization_id=l_org_id and
 	 e.org_information_context= 'NL_IZA_REPO_INFO'
 	 and e.org_information1 IS NOT NULL
 	 and e.org_information2 IS NOT NULL;

Line: 2177

 	SELECT iza_org_id,lev from hr_organization_information e,(
 		SELECT l_org_id iza_org_id,0 lev from dual
 		UNION
 		SELECT distinct organization_id_parent
 				   ,level
 			FROM per_org_structure_elements pose
 			 where   pose.org_structure_version_id = l_org_struct_version_id
 		START WITH organization_id_child    = l_org_id
 		CONNECT BY PRIOR organization_id_parent   = organization_id_child)
 	where
 	e.organization_id=iza_org_id and
 	e.org_information_context= 'NL_IZA_REPO_INFO'
 	and e.org_information1 IS NOT NULL
 	and e.org_information2 IS NOT NULL
 	ORDER BY lev;