APPS.PAY_KR_YEA_MED_EFILE_CONC_PKG SQL Statements

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

Line: 73

                select  hou.business_group_id
		from    hr_organization_units           hou
                where  hou.organization_id = p_business_place;

Line: 93

	select count(*) from
	(
	     select res_reg_no, count(*)
	     from
		 (
		      select
			  aei_information8    res_reg_no
			 ,aei_information7    relationship
			 ,count(*)                 cnt
		     from
			  per_assignment_extra_info where assignment_id = p_assignment_id
			  and information_type    =  'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
			  and  trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') =
					       trunc(p_yea_date, 'yyyy')
		    group by
			 aei_information8
			,aei_information7
		 )
	    group by res_reg_no
	    having count(*) > 1
	);

Line: 116

	select count(*) from
	(
	     select res_reg_no, count(*)
	     from
		 (
		      select
			  aei_information8    res_reg_no
			 ,aei_information9    aged_disabled
			 ,count(*)                 cnt
		     from
			  per_assignment_extra_info where assignment_id = p_assignment_id
			  and information_type    =  'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
			  and  trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') =
					       trunc(p_yea_date, 'yyyy')
		    group by
			 aei_information8
			,aei_information9
		 )
	    group by res_reg_no
	    having count(*) > 1
	);

Line: 139

	select count(*) from
	(
	     select med_ser_prov_reg_no, count(*)
	     from
		 (
		      select
			  aei_information5    med_ser_prov_reg_no
			 ,aei_information6    med_ser_prov_name
			 ,count(*)                 cnt
		     from
			  per_assignment_extra_info where assignment_id = p_assignment_id
			  and information_type    =  'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
			  and  trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') =
					       trunc(p_yea_date, 'yyyy')
                          and ( aei_information13 <> '1'  or (aei_information5 is not null and aei_information6 is not null)) -- Bug 8280944
		    group by
			 aei_information5
			,aei_information6
		 )
	    group by med_ser_prov_reg_no
	    having count(*) > 1
	);

Line: 210

	select med_ser_prov_reg_no
	     from
		 (
		      select
			  aei_information5    med_ser_prov_reg_no
			 ,aei_information6    med_ser_prov_name
			 ,count(*)                 cnt
		     from
			  per_assignment_extra_info where assignment_id = p_assignment_id
			  and information_type    =  'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
			  and  trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') =
					       trunc(p_yea_date, 'yyyy')
                          and ( aei_information13 <> '1'  or (aei_information5 is not null and aei_information6 is not null)) -- Bug 8280944
		    group by
			 aei_information5
			,aei_information6
		 )
        group by med_ser_prov_reg_no
        having count(*) > 1;

Line: 264

	select res_reg_no
	     from
		 (
		      select
			  aei_information8    res_reg_no
			 ,aei_information7    relationship
			 ,count(*)                 cnt
		     from
			  per_assignment_extra_info where assignment_id = p_assignment_id
			  and information_type    =  'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
			  and  trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') =
					       trunc(p_yea_date, 'yyyy')
		    group by
			 aei_information8
			,aei_information7
		 )
	group by res_reg_no
	having count(*) > 1;

Line: 284

	select res_reg_no
	from
		 (
		      select
			  aei_information8    res_reg_no
			 ,aei_information9    aged_disabled
			 ,count(*)                 cnt
		     from
			  per_assignment_extra_info where assignment_id = p_assignment_id
			  and information_type    =  'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
			  and  trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') =
					       trunc(p_yea_date, 'yyyy')
		    group by
			 aei_information8
			,aei_information9
		 )
	group by res_reg_no
	having count(*) > 1;