APPS.PAY_SIMULATOR_PKG SQL Statements

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

Line: 37

	SELECT	'1'
	INTO	v_dummy
	FROM	DUAL
	WHERE	exists (
			SELECT	'1'
			FROM	pay_element_entries_f peef
			WHERE	peef.assignment_id = p_ass_id
			AND	peef.element_type_id =p_ele_typ_id
			AND	p_start_date <=  peef.effective_end_date
			AND	p_end_date >= peef.effective_start_date
			);

Line: 66

   select application_column_name
	FROM	fnd_descr_flex_column_usages
	WHERE	descriptive_flexfield_name = 'Org Developer DF'
	AND	descriptive_flex_context_code = 'HR_SELF_SERVICE_BG_PREFERENCE'
	AND	end_user_column_name = 'Document Type';

Line: 74

	SELECT	application_column_name
	FROM	fnd_descr_flex_column_usages
	WHERE	descriptive_flexfield_name = 'Org Developer DF'
	AND	descriptive_flex_context_code = 'HR_SELF_SERVICE_BG_PREFERENCE'
	AND	end_user_column_name = 'Modeling Exclusion Element Set';

Line: 101

	FOR 'select '
	|| l_column_name
	|| ' from hr_organization_information
	               where organization_id = '
	|| p_business_group_id
	|| ' and org_information_context = ''HR_SELF_SERVICE_BG_PREFERENCE'''
        || ' and '||l_doc_type_col||' = ''SIMPAYSLIP''';

Line: 132

	SELECT	eei_information17 original_element
	INTO	v_original_element
	FROM	pay_element_type_extra_info
	WHERE	element_type_id = p_ele_typ_id
	AND	information_type = 'SIMULATION_ELEMENTS'
	AND	eei_information1 = 'Y'
	;

Line: 165

	SELECT	multiple_entries_allowed_flag
	INTO	v_mul_allowed
	FROM	pay_element_types_f
	WHERE	element_type_id = p_ele_typ_id
	AND	p_eff_date BETWEEN effective_start_date and effective_end_date;

Line: 184

PROCEDURE insert_new_element_entry
(
	p_start_date		DATE,
	p_end_date		DATE,
	p_assignment_id		NUMBER,
	p_element_type_id	NUMBER,
	p_num_ent_vals		NUMBER,
	p_inp_val_id_tbl	hr_entry.number_table,
	p_ent_val_tbl		hr_entry.varchar2_table
) IS
	v_ele_link_id		pay_element_links_f.element_link_id%TYPE;

Line: 198

	lv_procedure_name	VARCHAR2(100) := '.insert_new_element_entry';

Line: 212

	/*to check the usage the insert api and see what are the dates to be passed to the api*/

	IF v_ele_link_id IS NOT NULL THEN
		hr_utility.trace('The element link id is link_id='||v_ele_link_id);

Line: 216

		hr_entry_api.insert_element_entry(
					p_effective_start_date     => v_start_date,
					p_effective_end_date       => v_end_date,
					p_element_entry_id         => v_new_ee_id,
					p_assignment_id            => p_assignment_id,
					p_element_link_id          => v_ele_link_id,
					p_creator_type             => 'F',
					p_entry_type               => 'E',
					p_num_entry_values         => p_num_ent_vals,
					p_input_value_id_tbl       => p_inp_val_id_tbl,
					p_entry_value_tbl          => p_ent_val_tbl);

Line: 241

		insert_error_message(
		 p_assignment_id
		,'ERROR'
		,p_assignment_id
		,p_end_date
		,emesg
		);

Line: 249

		SELECT  nvl (nvl(eit.eei_information18
				,petl.reporting_name)
				,petl.element_name)
		INTO	elem_name
		FROM	pay_element_type_extra_info eit
			,pay_element_types_f pet
			,pay_element_types_f_tl petl
		WHERE	eit.information_type = 'SIMULATION_ELEMENTS'
		AND	eit.eei_information1 = 'Y'
		AND	eit.element_type_id = pet.element_type_id
		AND	pet.element_type_id = p_element_type_id
		AND	pet.element_type_id = petl.element_type_id
		AND	petl.language = userenv ('LANG')
		AND	p_end_date BETWEEN pet.effective_start_date
					AND pet.effective_end_date
		;

Line: 273

END insert_new_element_entry;

Line: 295

	SELECT	element_entry_id,effective_start_date,effective_end_date
	FROM	pay_element_entries_f
	WHERE	assignment_id = p_assignment_id
	AND	element_type_id = p_element_type_id
	AND	p_start_date <= effective_end_date
	AND	p_end_date >= effective_start_date
	;

Line: 305

	SELECT	eei_information17 original_element
	INTO	v_original_element
	FROM	pay_element_type_extra_info
	WHERE	element_type_id = p_element_type_id
	AND	information_type = 'SIMULATION_ELEMENTS'
	AND	eei_information1 = 'Y'
	;

Line: 318

		hr_entry_api.delete_element_entry(
							p_dt_delete_mode	=> hr_api.g_zap,
							p_session_date		=> ele_ent.effective_end_date,
							p_element_entry_id	=> ele_ent.element_entry_id
						);

Line: 343

	insert_new_element_entry (
				p_start_date		=> p_start_date,
				p_end_date		=> p_end_date,
				p_assignment_id		=> p_assignment_id,
				p_element_type_id	=> p_element_type_id,
				p_num_ent_vals		=> p_num_ent_vals,
				p_inp_val_id_tbl	=> p_inp_val_id_tbl,
				p_ent_val_tbl		=> p_ent_val_tbl);

Line: 370

	SELECT	element_entry_id,effective_start_date,effective_end_date
	FROM	pay_element_entries_f
	WHERE	assignment_id = p_assignment_id
	AND	p_start_date <= effective_end_date
	AND	p_end_date >= effective_start_date
	AND	CREATOR_TYPE IN ('RR','NR','EE','PR','R','P')
	;

Line: 382

		hr_utility.trace('Retro entry to be deleted is ee_id='||ele_ent.element_entry_id);

Line: 383

		hr_entry_api.delete_element_entry(
							p_dt_delete_mode	=> hr_api.g_zap,
							p_session_date		=> ele_ent.effective_end_date,
							p_element_entry_id	=> ele_ent.element_entry_id
						);

Line: 410

	SELECT	 element_entry_id
		,effective_start_date
		,effective_end_date
	FROM	pay_element_entries_f peef
		,pay_element_set_members pesm
	WHERE	assignment_id = p_assignment_id
	AND	pesm.element_set_id = p_elemset_id
	AND	peef.element_type_id = pesm.element_type_id
	AND	p_start_date <= effective_end_date
	AND	p_end_date >= effective_start_date
	;

Line: 429

		hr_utility.trace('Excluded entry to be deleted is ee_id='||ele_ent.element_entry_id);

Line: 430

		hr_entry_api.delete_element_entry(
							p_dt_delete_mode	=> hr_api.g_zap,
							p_session_date		=> ele_ent.effective_end_date,
							p_element_entry_id	=> ele_ent.element_entry_id
						);

Line: 457

	SELECT	element_entry_id,effective_start_date,effective_end_date
	FROM	pay_element_entries_f
	WHERE	assignment_id = p_assignment_id
	AND	element_type_id = p_element_type_id
	AND	p_start_date <= effective_end_date
	AND	p_end_date >= effective_start_date
	AND	ENTRY_TYPE IN ('A','D','R','S')
	;

Line: 470

		hr_utility.trace('Adjustment entry to be deleted is ee_id='||ele_ent.element_entry_id);

Line: 471

		hr_entry_api.delete_element_entry(
							p_dt_delete_mode	=> hr_api.g_zap,
							p_session_date		=> ele_ent.effective_end_date,
							p_element_entry_id	=> ele_ent.element_entry_id
						);

Line: 483

/*This procedure updates the Element Entries of a given assignment with the what if values provided
  by the user.
*/
--
PROCEDURE update_element_entries
(
	p_assignment_id		NUMBER,
	p_period_start_date	DATE,
	p_period_end_date	DATE
) IS

	v_value	VARCHAR2(40);

Line: 504

	lv_procedure_name	VARCHAR2(100) := '.update_element_entries';

Line: 509

	SELECT	DISTINCT
		piv.element_type_id
	FROM	per_assignment_extra_info pae,
		pay_input_values_f piv
	WHERE	pae.assignment_id = p_ass_id
	AND	pae.information_type IN (p_sim_region3,p_sim_region4)
	AND	piv.input_value_id = pae.aei_information1
	;

Line: 520

	SELECT	DISTINCT
		pae.aei_information1 input_value_id,
		pae.aei_information2 what_if_value
	FROM	per_assignment_extra_info pae,
		pay_input_values_f piv
	WHERE	pae.assignment_id = p_ass_id
	AND	pae.information_type IN (p_sim_region3,p_sim_region4)
	AND	piv.input_value_id = pae.aei_information1
	AND	piv.element_type_id = p_ele_type_id
	;

Line: 536

	SELECT	element_entry_id,effective_start_date,effective_end_date
	FROM	pay_element_entries_f
	WHERE	assignment_id = p_assignment_id
	AND	element_type_id = p_element_type_id
	AND	p_start_date <= effective_end_date
	AND	p_end_date >= effective_start_date
	AND	ENTRY_TYPE = 'E'
	AND	CREATOR_TYPE IN ('F','H','SP','UT')
	;

Line: 554

	SELECT /*+ INDEX(paf PER_ASSIGNMENTS_F_PK)*/
		pbg.legislation_code
	INTO	v_legislation
	FROM	per_all_assignments_f    paf,
		per_business_groups_perf pbg
	WHERE	paf.assignment_id = p_assignment_id
	AND	p_period_end_date BETWEEN paf.effective_start_date AND paf.effective_end_date
	AND	paf.business_group_id = pbg.business_group_id
	;

Line: 636

			/*For all the entries existing in the current period will update each datetracked row with the correspodning simulated value*/
			/*have to check if there are any adjustment entries present for the same period */
				FOR ele_ent IN csr_element_entries(p_assignment_id,ele_type.element_type_id,p_period_start_date,p_period_end_date) LOOP
					hr_entry_api.update_element_entry(
										p_dt_update_mode	=> hr_api.g_correction,
										p_session_date		=> ele_ent.effective_end_date,
										p_element_entry_id	=> ele_ent.element_entry_id,
										p_num_entry_values	=> v_num_inp_val_mod,
										p_input_value_id_tbl	=> inp_value_id_tbl,
										p_entry_value_tbl	=> scr_valuetbl);

Line: 651

				insert_new_element_entry (
							p_start_date		=> p_period_start_date,
							p_end_date		=> p_period_end_date,
							p_assignment_id		=> p_assignment_id,
							p_element_type_id	=> ele_type.element_type_id,
							p_num_ent_vals		=> v_num_inp_val_mod,
							p_inp_val_id_tbl	=> inp_value_id_tbl,
							p_ent_val_tbl		=> scr_valuetbl);

Line: 666

	hr_utility.set_location('In update_element_entries updated with what-if values',80);

Line: 668

	/*Delete all the retro entries for the assignment in the simulation period*/
	del_retro_entries(p_assignment_id,p_period_start_date,p_period_end_date);

Line: 673

END update_element_entries ;

Line: 688

	SELECT	paa.assignment_action_id assact
		,ppa.payroll_action_id pact_id
		,ppa.action_type act_type
	FROM	pay_assignment_actions paa
		,pay_payroll_actions ppa
	WHERE	paa.assignment_id = p_ass_id
	AND	paa.source_action_id IS NULL
	AND	ppa.payroll_action_id = paa.payroll_action_id
	AND	ppa.action_type IN ('Q','R','V'
				   ,'B','L','F'
				   ,'W','O','G')
	AND	ppa.effective_date BETWEEN p_period_start_date AND p_period_end_date
	AND	NVL(ppa.legislative_parameters,'TEST') <> 'PAY_SIM'
	ORDER BY paa.action_sequence DESC
	;

Line: 724

		insert_error_message(
		 p_assact_id
		,'ERROR'
		,p_ass_id
		,p_period_end_date
		,emesg
		);

Line: 755

	SELECT	paa.assignment_id, ptp.start_date, ptp.end_date
	INTO	v_ass_id,v_period_start_date,v_period_end_date
	FROM	pay_assignment_actions paa,
		pay_payroll_actions ppa,
		per_time_periods ptp
	WHERE	paa.assignment_action_id = p_assact_id
	AND	ppa.payroll_action_id = paa.payroll_action_id
	AND	ptp.time_period_id = ppa.time_period_id
	;

Line: 765

	SELECT /*+ INDEX(paf PER_ASSIGNMENTS_F_PK)*/
		pbg.legislation_code
		,paf.business_group_id
	INTO	l_legislation
		,l_bg_id
	FROM	per_all_assignments_f	paf,
		per_business_groups_perf pbg
	WHERE	paf.assignment_id = v_ass_id
	AND	v_period_end_date BETWEEN paf.effective_start_date AND paf.effective_end_date
	AND	paf.business_group_id = pbg.business_group_id
	;

Line: 780

	/*Delete the element entries of the elements in the simulation exclusion set*/
	del_excluded_entries(v_ass_id,v_period_start_date,v_period_end_date,l_bg_id);

Line: 783

	preprocess_statement := 'begin pay_'||l_legislation||'_simulation.update_asg_data(
				p_source_action_id =>:p_assact_id); end;';

Line: 797

	update_element_entries(v_ass_id,v_period_start_date,v_period_end_date);

Line: 806

		insert_error_message(
		 p_assact_id
		,'ERROR'
		,v_ass_id
		,v_period_end_date
		,emesg
		);

Line: 825

	SELECT	source_id
		,source_type
		,line_text
	FROM	pay_message_lines
	WHERE	source_id = p_asg_act_id
	AND	source_type = 'A'
	;

Line: 841

	SELECT	paa.assignment_id, ptp.end_date
	INTO	v_asg_id,v_effective_date
	FROM	pay_assignment_actions paa,
		pay_payroll_actions ppa,
		per_time_periods ptp
	WHERE	paa.assignment_action_id = p_assact_id
	AND	ppa.payroll_action_id = paa.payroll_action_id
	AND	ptp.time_period_id = ppa.time_period_id
	;

Line: 857

	insert_simulation_rows(p_action_context_id   => p_assact_id
		,p_action_context_type => 'ERROR'
		,p_assignment_id       => v_asg_id
		,p_tax_unit_id         => NULL
		,p_effective_date      => v_effective_date
		,p_tab_rec_data        => err_msg_tab
		);

Line: 888

	SELECT	DISTINCT
		pet.element_name
		,piv.name
		,prr.run_result_id
		,prv.result_value
		,prr.entry_type
		,prr.source_id
		,prr.source_type
		,prr.start_date
		,prr.end_date
		,prr.element_entry_id
		,prv.formula_result_flag
	FROM	pay_element_types_f pet
		,pay_input_values_f piv
		,pay_run_results prr
		,pay_run_result_values prv
	WHERE	prr.assignment_action_id = v_assact_id
	AND	prr.run_result_id = prv.run_result_id
	AND	prv.input_value_id = piv.input_value_id
	AND	pet.element_type_id = prr.element_type_id
	AND	piv.element_type_id = pet.element_type_id
	ORDER BY 1
		,2
		,3
		,4
		,9
		,10;

Line: 917

	select	assignment_action_id
	from	pay_assignment_actions
	where	source_action_id =  p_assact_id;

Line: 946

	SELECT	paa.assignment_id, ptp.start_date, ptp.end_date
	INTO	v_ass_id,v_period_start_date,v_period_end_date
	FROM	pay_assignment_actions paa,
		pay_payroll_actions ppa,
		per_time_periods ptp
	WHERE	paa.assignment_action_id = p_assact_id
	AND	ppa.payroll_action_id = paa.payroll_action_id
	AND	ptp.time_period_id = ppa.time_period_id
	;

Line: 956

	SELECT /*+ INDEX(paf PER_ASSIGNMENTS_F_PK)*/
		pbg.legislation_code
	INTO	l_legislation
	FROM	per_all_assignments_f	paf,
		per_business_groups_perf pbg
	WHERE	paf.assignment_id = v_ass_id
	AND	v_period_end_date BETWEEN paf.effective_start_date AND paf.effective_end_date
	AND	paf.business_group_id = pbg.business_group_id
	;

Line: 989

	insert_error_message(
		 p_assact_id
		,'ERROR'
		,v_ass_id
		,v_period_end_date
		,emesg
		);

Line: 999

PROCEDURE insert_simulation_rows(
		 p_action_context_id	NUMBER
		,p_action_context_type	VARCHAR2
		,p_assignment_id	NUMBER
		,p_tax_unit_id		NUMBER
		,p_effective_date	DATE
		,p_tab_rec_data		pay_emp_action_arch.action_info_table
		)
IS
	pragma AUTONOMOUS_TRANSACTION;

Line: 1009

	lv_procedure_name	VARCHAR2(100) := '.insert_simulation_rows';

Line: 1018

			INSERT INTO pay_simulation_information
			(
				ACTION_INFORMATION_ID,
				ACTION_CONTEXT_ID,
				ACTION_CONTEXT_TYPE,
				TAX_UNIT_ID,
				JURISDICTION_CODE,
				SOURCE_ID,
				SOURCE_TEXT,
				TAX_GROUP,
				ACTION_INFORMATION_CATEGORY,
				ACTION_INFORMATION1,
				ACTION_INFORMATION2,
				ACTION_INFORMATION3,
				ACTION_INFORMATION4,
				ACTION_INFORMATION5,
				ACTION_INFORMATION6,
				ACTION_INFORMATION7,
				ACTION_INFORMATION8,
				ACTION_INFORMATION9,
				ACTION_INFORMATION10,
				ACTION_INFORMATION11,
				ACTION_INFORMATION12,
				ACTION_INFORMATION13,
				ACTION_INFORMATION14,
				ACTION_INFORMATION15,
				ACTION_INFORMATION16,
				ACTION_INFORMATION17,
				ACTION_INFORMATION18,
				ACTION_INFORMATION19,
				ACTION_INFORMATION20,
				ACTION_INFORMATION21,
				ACTION_INFORMATION22,
				ACTION_INFORMATION23,
				ACTION_INFORMATION24,
				ACTION_INFORMATION25,
				ACTION_INFORMATION26,
				ACTION_INFORMATION27,
				ACTION_INFORMATION28,
				ACTION_INFORMATION29,
				ACTION_INFORMATION30,
				EFFECTIVE_DATE,
				ASSIGNMENT_ID
			)
			VALUES
			(
				pay_simulation_information_s.NEXTVAL,
				p_action_context_id,
				p_action_context_type,
				p_tax_unit_id,
				p_tab_rec_data(i).jurisdiction_code,
				NULL,
				NULL,
				NULL,
				p_tab_rec_data(i).action_info_category,
				p_tab_rec_data(i).act_info1,
				p_tab_rec_data(i).act_info2,
				p_tab_rec_data(i).act_info3,
				p_tab_rec_data(i).act_info4,
				p_tab_rec_data(i).act_info5,
				p_tab_rec_data(i).act_info6,
				p_tab_rec_data(i).act_info7,
				p_tab_rec_data(i).act_info8,
				p_tab_rec_data(i).act_info9,
				p_tab_rec_data(i).act_info10,
				p_tab_rec_data(i).act_info11,
				p_tab_rec_data(i).act_info12,
				p_tab_rec_data(i).act_info13,
				p_tab_rec_data(i).act_info14,
				p_tab_rec_data(i).act_info15,
				p_tab_rec_data(i).act_info16,
				p_tab_rec_data(i).act_info17,
				p_tab_rec_data(i).act_info18,
				p_tab_rec_data(i).act_info19,
				p_tab_rec_data(i).act_info20,
				p_tab_rec_data(i).act_info21,
				p_tab_rec_data(i).act_info22,
				p_tab_rec_data(i).act_info23,
				p_tab_rec_data(i).act_info24,
				p_tab_rec_data(i).act_info25,
				p_tab_rec_data(i).act_info26,
				p_tab_rec_data(i).act_info27,
				p_tab_rec_data(i).act_info28,
				p_tab_rec_data(i).act_info29,
				p_tab_rec_data(i).act_info30,
				p_effective_date,
				p_assignment_id
			);

Line: 1116

END insert_simulation_rows;

Line: 1119

PROCEDURE insert_error_message(
		 p_action_context_id	NUMBER
		,p_action_context_type	VARCHAR2
		,p_assignment_id	NUMBER
		,p_effective_date	DATE
		,p_err_msg		VARCHAR2
		)
IS
	pragma AUTONOMOUS_TRANSACTION;

Line: 1128

	lv_procedure_name	VARCHAR2(100) := '.insert_error_message';

Line: 1131

	INSERT INTO pay_simulation_information
	(
		ACTION_INFORMATION_ID,
		ACTION_CONTEXT_ID,
		ACTION_CONTEXT_TYPE,
		ACTION_INFORMATION_CATEGORY,
		ACTION_INFORMATION1,
		EFFECTIVE_DATE,
		ASSIGNMENT_ID
	)
	VALUES
	(
		pay_simulation_information_s.NEXTVAL,
		p_action_context_id,
		p_action_context_type,
		'SIMULATION_ERROR',
		p_err_msg,
		p_effective_date,
		p_assignment_id
	);

Line: 1164

	SELECT	parameter_value
	FROM	pay_action_parameters
	WHERE	parameter_name = 'RETAIN_SIMULATION_DATA';

Line: 1192

		DELETE FROM pay_simulation_information
		WHERE assignment_id = p_assignment_id
		;

Line: 1213

	INSERT
	INTO	pay_simulation_information
		(action_information_id
		,action_context_id
		,action_context_type
		,action_information_category
		,effective_date
		,assignment_id
		,action_information1
		,action_information2
		,action_information3
		,action_information4
		,action_information5
		,action_information6
		,action_information7
		,action_information8
		,action_information9
		,action_information10
		,action_information11
		,action_information12
		,action_information13
		,action_information14
		,action_information15
		,action_information16
		,action_information17
		,action_information18
		,action_information19
		,action_information20
		,action_information21
		,action_information22
		,action_information23
		,action_information24
		,action_information25
		,action_information26
		,action_information27
		,action_information28
		,action_information29
		,action_information30
		)
	SELECT	pay_simulation_information_s.nextval
		,p_source_id
		,'USER_INPUTS'
		,information_type
		,p_effective_date
		,p_assignment_id
		,aei_information1
		,aei_information2
		,aei_information3
		,aei_information4
		,aei_information5
		,aei_information6
		,aei_information7
		,aei_information8
		,aei_information9
		,aei_information10
		,aei_information11
		,aei_information12
		,aei_information13
		,aei_information14
		,aei_information15
		,aei_information16
		,aei_information17
		,aei_information18
		,aei_information19
		,aei_information20
		,aei_information21
		,aei_information22
		,aei_information23
		,aei_information24
		,aei_information25
		,aei_information26
		,aei_information27
		,aei_information28
		,aei_information29
		,aei_information30
	FROM	per_assignment_extra_info
	WHERE	assignment_id = p_assignment_id;