[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select ffff.formula_id
From FF_FORMULAS_F ffff
where UPPER(ffff.FORMULA_NAME) = UPPER(P_FORMULA_NAME)
and ffff.business_group_id = p_business_group_id
and p_effective_date between ffff.EFFECTIVE_START_DATE and ffff.EFFECTIVE_END_DATE;
SELECT peevf.element_entry_id
,peevf.input_value_id
FROM pay_element_types_f petf
,pay_element_entries_f peef
,pay_element_entry_values_f peevf
where petf.element_name = 'PQP LGPS Additional Pensionable Pay'
and petf.legislation_code = 'GB'
-- and petf.business_group_id = p_business_group_id 6652351
and petf.element_type_id = peef.element_type_id
and peef.element_entry_id = peevf.element_entry_id
and peef.assignment_id = p_assignment_id
--and p_effective_date between petf.EFFECTIVE_START_DATE and petf.EFFECTIVE_END_DATE --6666135
and p_effective_date between peef.EFFECTIVE_START_DATE and peef.EFFECTIVE_END_DATE
and p_effective_date between peevf.EFFECTIVE_START_DATE and peevf.EFFECTIVE_END_DATE;
select pelf.element_link_id,plivf.input_value_id
from pay_element_links_f pelf
,pay_link_input_values_f plivf
,pay_element_types_f petf
where petf.element_name = 'PQP LGPS Additional Pensionable Pay'
and petf.legislation_code = 'GB'
and petf.element_type_id = pelf.element_type_id
and pelf.ELEMENT_LINK_ID = plivf.ELEMENT_LINK_ID
and pelf.business_group_id = p_business_group_id
and pelf.LINK_TO_ALL_PAYROLLS_FLAG = 'Y'
and p_effective_date between pelf.EFFECTIVE_START_DATE and pelf.EFFECTIVE_END_DATE;
SELECT max(paa.assignment_action_id) ASSIGNMENT_ACTION_ID
,max(ppa.payroll_action_id) PAYROLL_ACTION_ID
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE ppa.action_type in ('Q','R')
AND paa.action_status = 'C'
AND ppa.business_group_id = p_business_group_id
AND paa.assignment_id = p_assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND effective_date <= p_effective_date;
hr_utility.trace(' Inserting seeded element with value'||n_sum_formula_val);
hr_entry_api.insert_element_entry(
p_effective_start_date => v_eff_start_date,
p_effective_end_date => v_eff_end_date,
p_element_entry_id => v_element_entry_id,
p_assignment_id => p_assignment_id,
p_element_link_id => c_seeded_element_det_row.element_link_id,
p_creator_type => 'F',
p_entry_type => 'E',
p_date_earned => v_eff_start_date,
p_input_value_id1 => c_seeded_element_det_row.input_value_id,
p_entry_value1 => to_char(n_sum_formula_val)
);
hr_entry_api.update_element_entry (p_dt_update_mode =>'CORRECTION',
p_session_date => p_effective_date,
p_check_for_update =>'N',
p_creator_type => 'F',
p_element_entry_id => c_additional_pension_row.element_entry_id,
p_input_value_id1 => c_additional_pension_row.input_value_id,
p_entry_value1 => n_sum_formula_val --n_pen_value 6666135
);
SELECT paaf.assignment_id, paaf.assignment_number,
paaf.payroll_id, MIN(paaf.EFFECTIVE_START_DATE) EFFECTIVE_START_DATE
FROM per_all_assignments_f paaf,
per_all_people_f papf,
per_assignment_status_types past
WHERE paaf.business_group_id = p_business_group_id
AND paaf.payroll_id= nvl(p_payroll_id, paaf.payroll_id)
-- AND paaf.assignment_id = nvl(p_assignment_id, paaf.assignment_id)
AND paaf.assignment_number = nvl(p_assignment_number, paaf.assignment_number)
AND paaf.EFFECTIVE_START_DATE <= v_given_end_date
AND paaf.EFFECTIVE_END_DATE > v_eff_start_date
AND paaf.person_id = papf.person_id
AND papf.employee_number = nvl(p_employee_no, papf.employee_number)
AND past.ASSIGNMENT_STATUS_TYPE_ID = paaf.ASSIGNMENT_STATUS_TYPE_ID
AND past.PER_SYSTEM_STATUS in ('ACTIVE_ASSIGN','SUSP_ASSIGN', 'TERM_ASSIGN')-- 'TERM_ASSIGN' added for bug 6868115
--6813970 begin
/* Moving Assignment set check from here, so that both types of Assignment set can be processed.
AND (p_assignment_set_id IS NULL -- don't check for assignment set in this case
OR EXISTS (SELECT 1 FROM hr_assignment_sets has1
WHERE has1.assignment_set_id = p_assignment_set_id
AND has1.business_group_id = paaf.business_group_id
AND nvl(has1.payroll_id, paaf.payroll_id) = paaf.payroll_id
AND (NOT EXISTS (SELECT 1 -- chk no amendmts
FROM hr_assignment_set_amendments hasa1
WHERE hasa1.assignment_set_id =
has1.assignment_set_id)
OR EXISTS (SELECT 1 -- chk include amendmts
FROM hr_assignment_set_amendments hasa2
WHERE hasa2.assignment_set_id =
has1.assignment_set_id
AND hasa2.assignment_id = paaf.assignment_id
AND nvl(hasa2.include_or_exclude,'I') = 'I')
OR (NOT EXISTS (SELECT 1 --chk no exlude amendmts
FROM hr_assignment_set_amendments hasa3
WHERE hasa3.assignment_set_id =
has1.assignment_set_id
AND hasa3.assignment_id = paaf.assignment_id
AND nvl(hasa3.include_or_exclude,'I') = 'E')
AND NOT EXISTS (SELECT 1 --and chk no Inc amendmts
FROM hr_assignment_set_amendments hasa4
WHERE hasa4.assignment_set_id =
has1.assignment_set_id
AND nvl(hasa4.include_or_exclude,'I') = 'I') ) -- end checking exclude amendmts
) -- done checking amendments
) -- done asg set check when not null
) -- end of asg set check
6813970 end */
GROUP BY assignment_id, assignment_number, payroll_id
ORDER BY assignment_id, EFFECTIVE_START_DATE;
select distinct PCV_INFORMATION1
From pqp_configuration_values
where pcv_information_category = 'PQP_GB_LGPS_SCOTLAND_FF_INFO'
AND business_group_id=p_business_group_id; --New business group specific Condition is added in this cursor for bug 6856733
SELECT pqpaaf.lgps_process_flag,
pqpaaf.assignment_attribute_id,
pqpaaf.EFFECTIVE_START_DATE,
pqpaaf.object_version_number
FROM pqp_assignment_attributes_f pqpaaf
WHERE pqpaaf.assignment_id = n_assignment_id
AND pqpaaf.business_group_id = p_business_group_id
AND nvl(pqpaaf.lgps_process_flag,'Nul') = nvl(l_mode,nvl(pqpaaf.lgps_process_flag,'Nul')) --l_mode will have value only in case of Incomplete and reprocess
AND ( v_assignment_eff_date between pqpaaf.EFFECTIVE_START_DATE and pqpaaf.EFFECTIVE_END_DATE
OR pqpaaf.EFFECTIVE_START_DATE = (select min(EFFECTIVE_START_DATE) from pqp_assignment_attributes_f where assignment_id = n_assignment_id
AND lgps_process_flag = nvl(l_mode,lgps_process_flag) AND business_group_id = p_business_group_id
AND EFFECTIVE_START_DATE BETWEEN v_assignment_eff_date AND v_eff_end_date));
SELECT peef.ELEMENT_TYPE_ID,petf.ELEMENT_NAME
FROM pay_element_entries_f peef, pay_element_types_f petf
WHERE peef.ASSIGNMENT_ID=n_assignment_id
AND peef.ELEMENT_TYPE_ID = petf.ELEMENT_TYPE_ID
AND petf.business_group_id = p_business_group_id
AND v_assignment_eff_date between peef.EFFECTIVE_START_DATE and peef.EFFECTIVE_END_DATE
AND v_assignment_eff_date between petf.EFFECTIVE_START_DATE and petf.EFFECTIVE_END_DATE;
SELECT pet.EEI_INFORMATION1
FROM pay_element_type_extra_info pet
WHERE pet.element_type_id = n_element_type_id
AND pet.INFORMATION_TYPE = 'PQP_UK_RATE_TYPE'
AND pet.EEI_INFORMATION1 IN ('PQP_LGPS_PENSION_PAY','PQP_LGPS_SCOTLAND_PENSION_PAY');
SELECT 1
FROM pay_element_type_extra_info pet
WHERE pet.element_type_id = n_element_type_id
AND pet.INFORMATION_TYPE = 'PQP_UK_ELEMENT_ATTRIBUTION';
SELECT max(EFFECTIVE_START_DATE)
FROM pqp_assignment_attributes_f pqpaaf
WHERE pqpaaf.assignment_id = n_assignment_id
AND pqpaaf.business_group_id = p_business_group_id;
SELECT ASSIGNMENT_ATTRIBUTE_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, object_version_number
FROM pqp_assignment_attributes_f pqpaaf
WHERE pqpaaf.assignment_id = n_assignment_id
and pqpaaf.business_group_id = p_business_group_id
and pqpaaf.EFFECTIVE_START_DATE > v_max_date
-- and pqpaaf.EFFECTIVE_START_DATE between v_eff_start_date and v_eff_end_date_corr;
insert into fnd_sessions (SESSION_ID, EFFECTIVE_DATE)
values(userenv('sessionid'), trunc(SYSDATE));
v_mode := 'UPDATE_CHANGE_INSERT';
v_mode := 'UPDATE';
hr_utility.set_location('Mode for update v_mode: ' || v_mode, 3);
hr_utility.set_location('Calling API to update LGPS Process Flag I',35);
pqp_aat_api.update_assignment_attribute
(p_validate => false
,p_effective_date => v_assignment_eff_date --v_eff_start_date
,p_datetrack_mode => v_mode
,p_assignment_attribute_id => c_pqp_assignment_row.assignment_attribute_id
,p_business_group_id => p_business_group_id
,p_effective_start_date => v_max_date
,p_effective_end_date => v_eff_end_date
,p_assignment_id => c_all_assignments.assignment_id
,p_object_version_number => n_object_version_no
,p_lgps_process_flag => l_lgps_process_flag
);
pqp_aat_api.update_assignment_attribute
(p_validate => false
,p_effective_date => v_eff_start_date_corr
,p_datetrack_mode => 'CORRECTION'
,p_assignment_attribute_id => K.assignment_attribute_id
,p_business_group_id => p_business_group_id
,p_effective_start_date => K.EFFECTIVE_START_DATE
,p_effective_end_date => K.EFFECTIVE_END_DATE
,p_assignment_id => c_all_assignments.assignment_id
,p_object_version_number => n_object_version_no
,p_lgps_process_flag => l_lgps_process_flag
);
ELSIF p_mode = 'New Hires' then --insert
--NO CURRENT RECORD IN pqp SO INSERT A RECORD WITH CP START DATE
--6813970 begin
-- IF (not b_element_present) or (not b_rate_type)
-- THEN
-- l_warning_msg := 'PQP_LGPS_MISSING_RATE_INFO: Historic Rate - Rate Type "LGPS Pensionable Pay" not set for elements against Assignment '||c_all_assignments.assignment_number;
hr_utility.set_location('Calling API to insert LGPS Process Flag I',355);
pqp_aat_api.update_assignment_attribute
(p_validate => false
,p_effective_date => v_assignment_eff_date --v_eff_start_date
,p_datetrack_mode => v_mode
,p_assignment_attribute_id => c_pqp_assignment_row.assignment_attribute_id
,p_business_group_id => p_business_group_id
,p_effective_start_date => v_max_date
,p_effective_end_date => v_eff_end_date
,p_assignment_id => c_all_assignments.assignment_id
,p_object_version_number => n_object_version_no
,p_lgps_process_flag => l_lgps_process_flag
,p_lgps_pensionable_pay => l_lgps_pensionable_pay
);
pqp_aat_api.update_assignment_attribute
(p_validate => false
,p_effective_date => v_eff_start_date_corr
,p_datetrack_mode => 'CORRECTION'
,p_assignment_attribute_id => K.assignment_attribute_id
,p_business_group_id => p_business_group_id
,p_effective_start_date => K.EFFECTIVE_START_DATE
,p_effective_end_date => K.EFFECTIVE_END_DATE
,p_assignment_id => c_all_assignments.assignment_id
,p_object_version_number => n_object_version_no
,p_lgps_process_flag => l_lgps_process_flag
,p_lgps_pensionable_pay => l_lgps_pensionable_pay
);
hr_utility.set_location('Inserting the Contractual pay for effective date'||v_assignment_eff_date,555);
hr_utility.set_location('Calling API to update LGPS Process Flag I',30);
pqp_aat_api.update_assignment_attribute
(p_validate => false
,p_effective_date => v_assignment_eff_date --v_eff_start_date
,p_datetrack_mode => v_mode
,p_assignment_attribute_id => c_pqp_assignment_row.assignment_attribute_id
,p_business_group_id => p_business_group_id
,p_effective_start_date => v_max_date
,p_effective_end_date => v_eff_end_date
,p_assignment_id => c_all_assignments.assignment_id
,p_object_version_number => n_object_version_no
,p_lgps_process_flag => l_lgps_process_flag
);
pqp_aat_api.update_assignment_attribute
(p_validate => false
,p_effective_date => v_eff_start_date_corr
,p_datetrack_mode => 'CORRECTION'
,p_assignment_attribute_id => K.assignment_attribute_id
,p_business_group_id => p_business_group_id
,p_effective_start_date => K.EFFECTIVE_START_DATE
,p_effective_end_date => K.EFFECTIVE_END_DATE
,p_assignment_id => c_all_assignments.assignment_id
,p_object_version_number => n_object_version_no
,p_lgps_process_flag => l_lgps_process_flag
);
hr_utility.set_location('Calling API to insert LGPS Process Flag I',305);
SELECT LGPS_TRANS_ARRANG_FLAG
FROM pqp_assignment_attributes_f pqaaf
WHERE pqaaf.assignment_id = p_assignment_id
AND pqaaf.business_group_id = p_business_group_id
AND p_effective_date between pqaaf.effective_start_date and pqaaf.effective_end_date;
SELECT nvl(LGPS_PENSIONABLE_PAY,-1)
FROM pqp_assignment_attributes_f pqaaf
WHERE pqaaf.assignment_id = p_assignment_id
AND pqaaf.business_group_id = p_business_group_id
AND p_effective_date between pqaaf.effective_start_date and pqaaf.effective_end_date;