APPS.GHR_MTI_INT SQL Statements

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

Line: 40

			-- for update of transfer status, and to find all data in the
			-- interface table for ther person
			return FALSE;

Line: 60

		-- update interface table to indicate result of transfer
		update ghr_mt_people_f_v
		set mt_status = p_status_code
		where mt_name = p_transfer_name
		and last_name = p_last_name
		and mt_person_id = p_person_id;

Line: 76

	procedure update_mass_transfer_in_flag(p_person_id	number,
														p_transfer_id varchar2,
														p_effective_date date) is

		cursor cur_pei_id is
			select  person_extra_info_id, object_version_number
			from per_people_extra_info
			where person_id = p_person_id
			and information_type = 'GHR_US_PER_MASS_ACTIONS';

Line: 89

		l_proc			varchar2(72) := g_package||'update_mass_transfer_in_flag';

Line: 113

			ghr_person_extra_info_api.update_person_extra_info (
				p_person_extra_info_id		=> l_person_extra_info_id
				,p_object_version_number	=> l_object_version_number
				,p_effective_date				=> p_effective_date
				,p_pei_information6			=> 'Y'
				,p_pei_information8			=> p_transfer_id
			);

Line: 264

		select id_flex_num
		from fnd_id_flex_structures_tl
		where id_flex_structure_name = p_id_flex_name
		and language = 'US';

Line: 385

			-- select all people extra info rows by transfer name and
			-- person_id, The person_id must uniquely identify all information
			-- in the interface table for a person by transfer_name.
			--
			cursor cur_people_ei is
				select *
				from ghr_mt_people_ei_v
				where mt_name = p_transfer_name
				and mt_person_id = p_tran.mt_person_id;

Line: 396

				select *
				from ghr_mt_analysis_criteria_v
				where mt_name = p_transfer_name
				and mt_person_id = p_tran.mt_person_id;

Line: 402

				select *
				from ghr_mt_addresses_v
				where mt_name = p_transfer_name
				and mt_person_id = p_tran.mt_person_id
				and mt_contact_name is null;

Line: 410

				select *
				from ghr_mt_addresses_v
				where mt_name = p_transfer_name
				and mt_person_id = p_tran.mt_person_id
				and mt_contact_name is not null;

Line: 420

            select ASSIGNMENT_STATUS_TYPE_ID
            from PER_ASSIGNMENT_STATUS_TYPES
            where ASSIGNMENT_STATUS_TYPE_ID < 10
            and user_status like '%Accepted%'
            and business_group_id is null;

Line: 428

            select person_id, full_name
            from per_people_f
            where national_identifier = p_tran.national_identifier
            and business_group_id     = p_business_group_id
            and p_effective_date
                between effective_start_date and effective_end_date;

Line: 575

				,P_RESUME_LAST_UPDATED			=>
						to_date(p_tran.RESUME_LAST_UPDATED,g_datefmt)
				,P_WORK_SCHEDULE					=> p_tran.WORK_SCHEDULE
				,p_person_id						=> l_person_id
				,p_assignment_id					=> l_assignment_id
				,p_application_id					=> l_application_id
				,p_per_object_version_number	=> l_per_object_version_number
				,p_asg_object_version_number	=> l_asg_object_version_number
				,p_apl_object_version_number  => l_apl_object_version_number
				,p_per_effective_start_date	=> l_per_effective_start_date
				,p_per_effective_end_date		=> l_per_effective_end_date
				,p_full_name						=> l_full_name
				,p_per_comment_id					=> l_per_comment_id
				,p_assignment_sequence			=> l_assignment_sequence
				,p_name_combination_warning	=> l_name_combination_warning
                                ,p_orig_hire_warning            => l_orig_hire_warning
			);

Line: 622

				hr_assignment_internal.update_status_type_apl_asg (
					 p_effective_date 				=> l_accept_date
   					,p_datetrack_update_mode		=> 'CORRECTION'
					,p_assignment_id					=> l_assignment_id
--					,p_object_version_number		=> l_per_object_version_number
-- Sundar Commented the above line and added the below one. Asg OVN shd be passed instead of per ovn. Bug 2944589
					,p_object_version_number		=> l_asg_object_version_number
					,p_expected_system_status		=> 'ACCEPTED'
               ,p_assignment_status_type_id  => l_assignment_status_type_id
					,p_effective_start_date			=> l_per_effective_start_date
					,p_effective_end_date			=> l_per_effective_end_date
				);

Line: 930

				select person_type_id
				from per_person_types
				where business_group_id = p_business_group_id
				and user_person_type = 'Applicant';

Line: 936

            select b.USER_PERSON_TYPE   usertype,
                   b.person_type_id     person_type_id
            from   per_all_people_f a,
                   per_person_types b
            where a.BUSINESS_GROUP_ID = b.BUSINESS_GROUP_ID
            and   a.business_group_id = p_business_group_id
            and   a.PERSON_TYPE_ID    = b.PERSON_TYPE_ID
            and   a.person_id         = l_person_row.mt_person_id
            and   l_effective_date
                  between a.effective_start_date and a.effective_end_date;

Line: 949

			-- selected for transfer in
			cursor cur_people is
				select *
				from ghr_mt_people_f_v
				where mt_name = l_transfer_name
				and mt_status = 'Y';

Line: 995

			-- for all people selected for transfer in
			for c_person in cur_people loop

				l_person_row := c_person;

Line: 1013

                'Error: Selected person: '||c_person.full_name||'(SSN: '||c_person.national_identifier||
                ') contains an invalid Person Type as of the Effective ' ||
                'Date of the Transfer In action.  Person Type may not be Employee.  Please  ' ||
                'make sure this person is not an Employee as of the Effective Date of the   ' ||
                'Transfer In, and then resubmit the Transfer In action.');

Line: 1057

					update_mass_transfer_in_flag(
							p_person_id			=> l_person_id,
							p_transfer_id		=> p_transfer_id,
							p_effective_date	=> l_effective_date);