[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT eff_dt
FROM ben_ext_rslt res
WHERE ext_dfn_id IN (SELECT ext_dfn_id
FROM pqp_extract_attributes
WHERE ext_dfn_type = 'NL_FPR')
AND ext_stat_cd = 'A'
AND EXISTS ( SELECT 1 FROM ben_ext_rslt_dtl dtl
WHERE dtl.ext_rslt_id = res.ext_rslt_id
AND dtl.person_id = p_person_id)
ORDER BY ext_rslt_id DESC;
SELECT eff_dt
FROM ben_ext_rslt res
WHERE ext_dfn_id IN (SELECT ext_dfn_id
FROM pqp_extract_attributes
WHERE ext_dfn_type = 'NL_FPR')
AND ext_stat_cd = 'A'
AND EXISTS ( SELECT 1 FROM ben_ext_rslt_dtl dtl
WHERE dtl.ext_rslt_id = res.ext_rslt_id
AND dtl.person_id = p_person_id)
ORDER BY ext_rslt_id DESC;
SELECT effective_date
FROM pay_payroll_actions pact
WHERE action_type IN ('Q','R')
AND action_status = 'C'
AND EXISTS (SELECT 1 FROM pay_assignment_actions act
WHERE act.payroll_action_id = pact.payroll_action_id
AND assignment_id = p_assignment_id
AND action_status = 'C')
AND effective_date >= c_eff_dt
ORDER BY payroll_action_id desc;
SELECT effective_start_date
FROM per_all_assignments_f
WHERE (assignment_id = p_assignment_id)
AND assignment_type = 'E'
AND (TO_DATE('01/01/'||TO_CHAR(p_eff_date,'YYYY'),'dd/mm/yyyy')
BETWEEN effective_start_date and effective_end_date);
SELECT MIN(effective_start_date)
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND assignment_type = 'E'
AND (effective_start_date
BETWEEN TO_DATE('01/01/'||TO_CHAR(p_eff_date,'YYYY'),'dd/mm/yyyy')
AND TO_DATE('31/12/'||TO_CHAR(p_eff_date,'YYYY'),'dd/mm/yyyy'));
SELECT piv.input_value_id
,pet.element_type_id
FROM pay_input_values_f piv
,pay_element_types_f pet
WHERE piv.element_type_id = pet.element_type_id
AND pet.element_name = 'ABP Pensions Part Time Percentage'
AND piv.name = 'Part Time Percentage'
AND pet.legislation_code = 'NL'
AND piv.legislation_code = 'NL'
AND p_effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND p_effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date;
SELECT pee.element_entry_id
FROM pay_element_entries_f pee
,pay_element_links_f pel
WHERE pee.element_link_id = pel.element_link_id
AND pee.assignment_id = p_assignment_id
AND pel.element_type_id = c_element_type_id
AND p_effective_date BETWEEN pel.effective_start_date
AND pel.effective_end_date
AND p_effective_date BETWEEN pee.effective_start_date
AND pee.effective_end_date;
SELECT 1
FROM pay_payroll_actions ppa
WHERE ppa.action_status = 'C'
AND ppa.action_type IN ('Q','R')
AND ppa.effective_date >= p_effective_date
AND EXISTS ( SELECT 1
FROM pay_assignment_actions paa
WHERE ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_id = p_assignment_id
AND paa.action_status = 'C' )
AND rownum = 1;
hr_entry_api.update_element_entry
(p_dt_update_mode => 'CORRECTION',
p_session_date => p_effective_date,
p_element_entry_id => l_element_entry_id,
p_input_value_id1 => l_input_value_id,
p_entry_value1 => 100,
p_entry_information_category => NULL,
p_override_user_ent_chk => 'Y'
);
hr_entry_api.update_element_entry
(p_dt_update_mode => 'CORRECTION',
p_session_date => p_effective_date,
p_element_entry_id => l_element_entry_id,
p_input_value_id1 => l_input_value_id,
p_entry_value1 => NULL,
p_entry_information_category => NULL,
p_override_user_ent_chk => 'Y'
);
SELECT org_information1
,nvl(org_information2,'4712/12/31') org_information2
FROM hr_organization_information
WHERE org_information3 = p_org_information3
AND organization_id = p_organization_id
AND org_information_id <> p_org_information_id
AND org_information_context = 'PQP_NL_ABP_PT';
SELECT org_information1
,nvl(org_information2,'4712/12/31') org_information2
FROM hr_organization_information
WHERE org_information3 = p_org_information3
AND organization_id = p_organization_id
AND org_information_id <> p_org_information_id
AND org_information_context = 'PQP_NL_PGGM_PT';
SELECT org_information1
,nvl(org_information2,'4712/12/31') org_information2
FROM hr_organization_information
WHERE organization_id = p_organization_id
AND org_information_id <> p_org_information_id
AND org_information_context = 'PQP_NL_PGGM_INFO';
SELECT pension_sub_category,
threshold_conversion_rule,
contribution_conversion_rule,
pension_basis_calc_method
FROM pqp_pension_types_f
WHERE pension_type_id = to_number(c_pension_type_id);
SELECT effective_start_date,effective_end_date
FROM pqp_pension_types_f
WHERE pension_type_id = to_number(c_pension_type_id);
SELECT effective_date
FROM fnd_sessions
WHERE session_id IN
(SELECT userenv('sessionid')
FROM dual
);
SELECT org_information3
FROM hr_organization_information
WHERE organization_id = p_organization_id
AND org_information_id <> p_org_information_id
AND org_information_context = 'PQP_NL_ABP_PT'
AND ((trunc(to_date(substr(org_information1,1,10),'YYYY/MM/DD'))
>= trunc(to_date(substr(p_org_information1,1,10),'YYYY/MM/DD'))
AND trunc(to_date(substr(org_information1,1,10),'YYYY/MM/DD'))
<= trunc(to_date(substr(nvl(p_org_information2,'4712/12/31'),1,10),'YYYY/MM/DD'))
)
OR
(trunc(to_date(substr(nvl(org_information2,'4712/12/31'),1,10),'YYYY/MM/DD'))
>= trunc(to_date(substr(p_org_information1,1,10),'YYYY/MM/DD'))
AND trunc(to_date(substr(nvl(org_information2,'4712/12/31'),1,10),'YYYY/MM/DD'))
<= trunc(to_date(substr(nvl(p_org_information2,'4712/12/31'),1,10),'YYYY/MM/DD'))
)
OR
(trunc(to_date(substr(org_information1,1,10),'YYYY/MM/DD'))
<= trunc(to_date(substr(p_org_information1,1,10),'YYYY/MM/DD'))
AND trunc(to_date(substr(nvl(org_information2,'4712/12/31'),1,10),'YYYY/MM/DD'))
>= trunc(to_date(substr(nvl(p_org_information2,'4712/12/31'),1,10),'YYYY/MM/DD'))
)
);
SELECT to_date(substr(eei_information10,1,10),'DD/MM/YYYY') start_date,
to_date(substr(eei_information11,1,10),'DD/MM/YYYY') end_date
FROM pay_element_type_extra_info
WHERE eei_information_category = 'PQP_NL_ABP_DEDUCTION'
AND information_type = 'PQP_NL_ABP_DEDUCTION'
AND eei_information2 = c_pension_type_id;
SELECT org_information1
,nvl(org_information2,'4712/12/31') org_information2
FROM hr_organization_information
WHERE org_information3 = p_org_information3
AND organization_id = p_organization_id
AND org_information_id <> p_org_information_id
AND org_information_context = 'PQP_NL_ABP_PT';
SELECT org_information1
,nvl(org_information2,'4712/12/31') org_information2
FROM hr_organization_information
WHERE org_information3 = p_org_information3
AND organization_id = p_organization_id
AND org_information_id <> p_org_information_id
AND org_information_context = 'PQP_NL_PGGM_PT';
SELECT org_information1
,nvl(org_information2,'4712/12/31') org_information2
FROM hr_organization_information
WHERE organization_id = p_organization_id
AND org_information_id <> p_org_information_id
AND org_information_context = 'PQP_NL_PGGM_INFO';
SELECT effective_start_date,effective_end_date
FROM pqp_pension_types_f
WHERE pension_type_id = to_number(c_pension_type_id);
SELECT pension_sub_category
,threshold_conversion_rule
,contribution_conversion_rule
,pension_basis_calc_method
FROM pqp_pension_types_f
WHERE pension_type_id = to_number(c_pension_type_id);
SELECT org_information3
FROM hr_organization_information
WHERE organization_id = p_organization_id
AND org_information_id <> p_org_information_id
AND org_information_context = 'PQP_NL_ABP_PT'
AND ((trunc(to_date(substr(org_information1,1,10),'YYYY/MM/DD'))
>= trunc(to_date(substr(p_org_information1,1,10),'YYYY/MM/DD'))
AND trunc(to_date(substr(org_information1,1,10),'YYYY/MM/DD'))
<= trunc(to_date(substr(nvl(p_org_information2,'4712/12/31'),1,10),'YYYY/MM/DD'))
)
OR
(trunc(to_date(substr(nvl(org_information2,'4712/12/31'),1,10),'YYYY/MM/DD'))
>= trunc(to_date(substr(p_org_information1,1,10),'YYYY/MM/DD'))
AND trunc(to_date(substr(nvl(org_information2,'4712/12/31'),1,10),'YYYY/MM/DD'))
<= trunc(to_date(substr(nvl(p_org_information2,'4712/12/31'),1,10),'YYYY/MM/DD'))
)
OR
(trunc(to_date(substr(org_information1,1,10),'YYYY/MM/DD'))
<= trunc(to_date(substr(p_org_information1,1,10),'YYYY/MM/DD'))
AND trunc(to_date(substr(nvl(org_information2,'4712/12/31'),1,10),'YYYY/MM/DD'))
>= trunc(to_date(substr(nvl(p_org_information2,'4712/12/31'),1,10),'YYYY/MM/DD'))
)
);
SELECT to_date(substr(eei_information10,1,10),'DD/MM/YYYY') start_date,
to_date(substr(eei_information11,1,10),'DD/MM/YYYY') end_date
FROM pay_element_type_extra_info
WHERE eei_information_category = 'PQP_NL_ABP_DEDUCTION'
AND information_type = 'PQP_NL_ABP_DEDUCTION'
AND eei_information2 = c_pension_type_id;
SELECT paa.assignment_id,ppa.date_earned
FROM pay_assignment_actions paa,pay_payroll_actions ppa
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND ppa.date_earned between
fnd_date.canonical_to_date(p_org_information1_o)
AND fnd_date.canonical_to_date(nvl(p_org_information2_o,fnd_date.date_to_canonical(hr_api.g_eot)))
AND paa.assignment_action_id IN
(SELECT assignment_action_id
FROM pay_run_results
WHERE element_type_id IN
(SELECT element_type_id
FROM pay_element_type_extra_info
WHERE information_type = 'PQP_NL_ABP_DEDUCTION'
AND eei_information_category = 'PQP_NL_ABP_DEDUCTION'
AND eei_information2 = p_org_information3_o
)
);
SELECT effective_date
FROM fnd_sessions
WHERE session_id IN
(SELECT userenv('sessionid')
FROM dual
);
l_allow_update NUMBER := 1;
hr_utility.set_location('in update',10);
l_allow_update := 0;
IF l_allow_update = 0 THEN
IF p_org_information3 <> p_org_information3_o THEN
hr_utility.set_message(8303,'PQP_230101_UPD_NOT_ALLOWED');
ELSIF l_allow_update = 1 THEN
IF (fnd_date.canonical_to_date(nvl(p_org_information2,fnd_date.date_to_canonical(hr_api.g_eot)))
<> fnd_date.canonical_to_date(nvl(p_org_information2_o,fnd_date.date_to_canonical(hr_api.g_eot))))
AND (l_eff_date > fnd_date.canonical_to_date(nvl(p_org_information2,fnd_date.date_to_canonical(hr_api.g_eot)))) THEN
hr_utility.set_message(8303,'PQP_230099_DT_TO_BEF_END_DT');
hr_utility.set_location('calling insert process',20);
hr_utility.set_location('in update',10);
hr_utility.set_location('in update',10);
SELECT aei_information1
,nvl(aei_information2,'4712/12/31') aei_information2
FROM per_assignment_extra_info
WHERE aei_information3 = p_aei_information3
AND assignment_id = p_assignment_id
AND assignment_extra_info_id <> p_assignment_extra_info_id
AND aei_information_category = 'NL_ABP_PI'
AND information_type = 'NL_ABP_PI';
SELECT aei_information1
,nvl(aei_information2,'4712/12/31') aei_information2
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND assignment_extra_info_id <> p_assignment_extra_info_id
AND aei_information_category = 'NL_ABP_PAR_INFO'
AND information_type = 'NL_ABP_PAR_INFO';
SELECT 1
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND assignment_extra_info_id <> p_assignment_extra_info_id
AND aei_information_category = 'NL_SAV_INFO'
AND information_type = 'NL_SAV_INFO'
AND aei_information1 = p_aei_information1
AND aei_information2 = p_aei_information2;
SELECT decode(hrl.lookup_code,'W',53,number_per_fiscal_year)
FROM per_time_period_types,hr_lookups hrl
WHERE period_type =
(SELECT period_type
FROM pay_payrolls_f
WHERE payroll_id =
(SELECT payroll_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND c_eff_date BETWEEN effective_start_date
AND nvl(effective_end_date,hr_api.g_eot)
)
AND c_eff_date BETWEEN effective_start_date
AND nvl(effective_end_date,hr_api.g_eot)
)
AND hrl.lookup_type = 'PROC_PERIOD_TYPE'
AND hrl.meaning = period_type;
SELECT effective_start_date,effective_end_date
FROM pqp_pension_types_f
WHERE pension_type_id = to_number(c_pension_type_id);
SELECT aei_information1,aei_information2
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND assignment_extra_info_id <> p_assignment_extra_info_id
AND aei_information_category = 'NL_ABP_PAR_INFO'
AND information_type = 'NL_ABP_PAR_INFO'
AND to_char(trunc(fnd_date.canonical_to_date(aei_information1)),'YYYY')
= c_year
AND to_char(trunc(fnd_date.canonical_to_date(nvl(aei_information2,
fnd_date.date_to_canonical(hr_api.g_eot)))),'YYYY')
= c_year
AND aei_information6 IS NOT NULL;
SELECT pension_sub_category
,threshold_conversion_rule
,contribution_conversion_rule
,pension_basis_calc_method
FROM pqp_pension_types_f
WHERE pension_type_id = to_number(c_pension_type_id);
SELECT aei_information3
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND assignment_extra_info_id <> p_assignment_extra_info_id
AND aei_information_category = 'NL_ABP_PI'
AND information_type = 'NL_ABP_PI'
AND ((trunc(to_date(substr(aei_information1,1,10),'YYYY/MM/DD'))
>= trunc(to_date(substr(p_aei_information1,1,10),'YYYY/MM/DD'))
AND trunc(to_date(substr(aei_information1,1,10),'YYYY/MM/DD'))
<= trunc(to_date(substr(nvl(p_aei_information2,'4712/12/31'),1,10),'YYYY/MM/DD'))
)
OR
(trunc(to_date(substr(nvl(aei_information2,'4712/12/31'),1,10),'YYYY/MM/DD'))
>= trunc(to_date(substr(p_aei_information1,1,10),'YYYY/MM/DD'))
AND trunc(to_date(substr(nvl(aei_information2,'4712/12/31'),1,10),'YYYY/MM/DD'))
<= trunc(to_date(substr(nvl(p_aei_information2,'4712/12/31'),1,10),'YYYY/MM/DD'))
)
OR
(trunc(to_date(substr(aei_information1,1,10),'YYYY/MM/DD'))
<= trunc(to_date(substr(p_aei_information1,1,10),'YYYY/MM/DD'))
AND trunc(to_date(substr(nvl(aei_information2,'4712/12/31'),1,10),'YYYY/MM/DD'))
>= trunc(to_date(substr(nvl(p_aei_information2,'4712/12/31'),1,10),'YYYY/MM/DD'))
)
);
SELECT max(date_start)
FROM per_all_assignments_f asg
,per_periods_of_service pps
WHERE pps.person_id = asg.person_id
AND asg.assignment_id = p_assignment_id
AND pps.business_group_id = asg.business_group_id
AND date_start <= c_eff_date;
SELECT to_date(substr(eei_information10,1,10),'DD/MM/YYYY') start_date,
to_date(substr(eei_information11,1,10),'DD/MM/YYYY') end_date
FROM pay_element_type_extra_info
WHERE eei_information_category = 'PQP_NL_ABP_DEDUCTION'
AND information_type = 'PQP_NL_ABP_DEDUCTION'
AND eei_information2 = c_pension_type_id;
Select person_id from per_all_assignments_f
where assignment_id = p_assignment_id;
SELECT effective_date
FROM fnd_sessions
WHERE session_id = userenv('sessionid');
SELECT aei_information1
,nvl(aei_information2,'4712/12/31') aei_information2
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND assignment_extra_info_id <> p_assignment_extra_info_id
AND aei_information_category = 'NL_ADDL_CALC'
AND information_type = 'NL_ADDL_CALC';
SELECT aei_information1
,nvl(aei_information2,'4712/12/31') aei_information2
FROM per_assignment_extra_info
WHERE aei_information3 = p_aei_information3
AND assignment_id = p_assignment_id
AND assignment_extra_info_id <> p_assignment_extra_info_id
AND aei_information_category = 'NL_ABP_PI'
AND information_type = 'NL_ABP_PI';
SELECT aei_information1
,nvl(aei_information2,'4712/12/31') aei_information2
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND assignment_extra_info_id <> p_assignment_extra_info_id
AND aei_information_category = 'NL_ABP_PAR_INFO'
AND information_type = 'NL_ABP_PAR_INFO';
SELECT 1
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND assignment_extra_info_id <> p_assignment_extra_info_id
AND aei_information_category = 'NL_SAV_INFO'
AND information_type = 'NL_SAV_INFO'
AND aei_information1 = p_aei_information1
AND aei_information2 = p_aei_information2;
SELECT aei_information1,aei_information2
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND assignment_extra_info_id <> p_assignment_extra_info_id
AND aei_information_category = 'NL_ABP_PAR_INFO'
AND information_type = 'NL_ABP_PAR_INFO'
AND to_char(trunc(fnd_date.canonical_to_date(aei_information1)),'YYYY')
= c_year
AND to_char(trunc(fnd_date.canonical_to_date(nvl(aei_information2,
fnd_date.date_to_canonical(hr_api.g_eot)))),'YYYY')
= c_year
AND aei_information6 IS NOT NULL;
SELECT decode(hrl.lookup_code,'W',53,number_per_fiscal_year)
FROM per_time_period_types,hr_lookups hrl
WHERE period_type =
(SELECT period_type
FROM pay_payrolls_f
WHERE payroll_id =
(SELECT payroll_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND c_eff_date BETWEEN effective_start_date
AND nvl(effective_end_date,hr_api.g_eot)
)
AND c_eff_date BETWEEN effective_start_date
AND nvl(effective_end_date,hr_api.g_eot)
)
AND hrl.lookup_type = 'PROC_PERIOD_TYPE'
AND hrl.meaning = period_type;
SELECT effective_start_date,effective_end_date
FROM pqp_pension_types_f
WHERE pension_type_id = to_number(c_pension_type_id);
SELECT pension_sub_category
,threshold_conversion_rule
,contribution_conversion_rule
,pension_basis_calc_method
FROM pqp_pension_types_f
WHERE pension_type_id = to_number(c_pension_type_id);
SELECT aei_information3
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND assignment_extra_info_id <> p_assignment_extra_info_id
AND information_type = 'NL_ABP_PI'
AND aei_information_category = 'NL_ABP_PI'
AND ((trunc(to_date(substr(aei_information1,1,10),'YYYY/MM/DD'))
>= trunc(to_date(substr(p_aei_information1,1,10),'YYYY/MM/DD'))
AND trunc(to_date(substr(aei_information1,1,10),'YYYY/MM/DD'))
<= trunc(to_date(substr(nvl(p_aei_information2,'4712/12/31'),1,10),'YYYY/MM/DD'))
)
OR
(trunc(to_date(substr(nvl(aei_information2,'4712/12/31'),1,10),'YYYY/MM/DD'))
>= trunc(to_date(substr(p_aei_information1,1,10),'YYYY/MM/DD'))
AND trunc(to_date(substr(nvl(aei_information2,'4712/12/31'),1,10),'YYYY/MM/DD'))
<= trunc(to_date(substr(nvl(p_aei_information2,'4712/12/31'),1,10),'YYYY/MM/DD'))
)
OR
(trunc(to_date(substr(aei_information1,1,10),'YYYY/MM/DD'))
<= trunc(to_date(substr(p_aei_information1,1,10),'YYYY/MM/DD'))
AND trunc(to_date(substr(nvl(aei_information2,'4712/12/31'),1,10),'YYYY/MM/DD'))
>= trunc(to_date(substr(nvl(p_aei_information2,'4712/12/31'),1,10),'YYYY/MM/DD'))
)
);
SELECT max(date_start)
FROM per_all_assignments_f asg
,per_periods_of_service pps
WHERE pps.person_id = asg.person_id
AND asg.assignment_id = p_assignment_id
AND pps.business_group_id = asg.business_group_id
AND date_start <= c_eff_date;
SELECT to_date(substr(eei_information10,1,10),'DD/MM/YYYY') start_date,
to_date(substr(eei_information11,1,10),'DD/MM/YYYY') end_date
FROM pay_element_type_extra_info
WHERE eei_information_category = 'PQP_NL_ABP_DEDUCTION'
AND information_type = 'PQP_NL_ABP_DEDUCTION'
AND eei_information2 = c_pension_type_id;
SELECT effective_date
FROM fnd_sessions
WHERE session_id = userenv('sessionid');
Select person_id from per_all_assignments_f
where assignment_id = p_assignment_id;
SELECT ppa.date_earned
FROM pay_assignment_actions paa,pay_payroll_actions ppa
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_id = p_assignment_id
AND ppa.date_earned between
fnd_date.canonical_to_date(p_aei_information1_o)
AND nvl(fnd_date.canonical_to_date(p_aei_information2_o),hr_api.g_eot)
AND paa.assignment_action_id IN
(SELECT assignment_action_id
FROM pay_run_results
WHERE element_type_id IN
(SELECT element_type_id
FROM pay_element_type_extra_info
WHERE information_type = 'PQP_NL_ABP_DEDUCTION'
AND eei_information_category = 'PQP_NL_ABP_DEDUCTION'
AND eei_information2 = p_aei_information3_o
)
);
SELECT aei_information1
,nvl(aei_information2,'4712/12/31') aei_information2
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND assignment_extra_info_id <> p_assignment_extra_info_id
AND aei_information_category = 'NL_ADDL_CALC'
AND information_type = 'NL_ADDL_CALC';
l_allow_update NUMBER := 1;
l_allow_update := 0;
IF l_allow_update = 0 THEN
IF (p_aei_information3 <> p_aei_information3_o) THEN
hr_utility.set_message(8303,'PQP_230101_UPD_NOT_ALLOWED');
SELECT ptp.start_date,ptp.end_date
FROM per_all_assignments_f asg
,per_time_periods ptp
WHERE asg.assignment_id = p_assignment_id
AND asg.payroll_id = ptp.payroll_id
AND p_date_earned BETWEEN ptp.start_date
AND ptp.end_date;
SELECT min(asg.effective_start_date)
,max(asg.effective_end_date)
FROM per_assignments_f asg
,per_assignment_status_types past
WHERE asg.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status = 'ACTIVE_ASSIGN'
AND asg.effective_start_date <= trunc(c_end_date)
AND nvl(asg.effective_end_date, trunc(c_end_date)) >= trunc(c_start_date)
AND asg.assignment_id = p_assignment_id
group by asg.assignment_id;
SELECT fnd_number.canonical_to_number(nvl(aei_information5,'1')) VOP
,TRUNC(fnd_date.canonical_to_date(aei_information1)) St_Dt
,TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt)) Ed_Dt
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND information_type = 'NL_ABP_PAR_INFO'
AND aei_information_category = 'NL_ABP_PAR_INFO'
AND TRUNC(fnd_date.canonical_to_date(aei_information1))
BETWEEN c_st_dt AND c_ed_dt
UNION
--
-- Rows that end in the current period
--
SELECT fnd_number.canonical_to_number(nvl(aei_information5,'1')) VOP
,TRUNC(fnd_date.canonical_to_date(aei_information1)) St_Dt
,TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt)) Ed_Dt
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND information_type = 'NL_ABP_PAR_INFO'
AND aei_information_category = 'NL_ABP_PAR_INFO'
AND TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt))
BETWEEN c_st_dt AND c_ed_dt
UNION
--
-- Rows that neither start or end in the current period
-- but the data in the EIT is valid for the current period
--
SELECT fnd_number.canonical_to_number(nvl(aei_information5,'1')) VOP
,TRUNC(fnd_date.canonical_to_date(aei_information1)) St_Dt
,TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt)) Ed_Dt
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND information_type = 'NL_ABP_PAR_INFO'
AND aei_information_category = 'NL_ABP_PAR_INFO'
AND c_ed_dt BETWEEN TRUNC(fnd_date.canonical_to_date(aei_information1))
AND TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt))
ORDER BY st_dt;
SELECT fnd_number.canonical_to_number(nvl(aei_information23,'1')) VOP
,TRUNC(fnd_date.canonical_to_date(aei_information1)) St_Dt
,TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt)) Ed_Dt
FROM per_assignment_extra_info aei,
pqp_pension_types_f pty
WHERE assignment_id = p_assignment_id
AND aei_information3 = p_pension_type_id
AND pty.pension_type_id = fnd_number.canonical_to_number(aei.aei_information3)
AND pension_sub_category IN ('PPP' ,'OPNP' ,'OPNP_65'
,'OPNP_AOW' ,'OPNP_W25','OPNP_W50', 'AAOP'
,'FPU_B', 'FPU_C', 'FPU_E', 'FPU_R', 'FPU_S', 'FPU_T')
AND c_ed_dt BETWEEN pty.effective_start_date AND NVL(pty.effective_end_date,to_date('31/12/4712','DD/MM/YYYY'))
AND aei_information23 IS NOT NULL
AND information_type = 'NL_ABP_PI'
AND aei_information_category = 'NL_ABP_PI'
AND TRUNC(fnd_date.canonical_to_date(aei_information1))
BETWEEN c_st_dt AND c_ed_dt
UNION
--
-- Rows that end in the current period
--
SELECT fnd_number.canonical_to_number(nvl(aei_information23,'1')) VOP
,TRUNC(fnd_date.canonical_to_date(aei_information1)) St_Dt
,TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt)) Ed_Dt
FROM per_assignment_extra_info aei,
pqp_pension_types_f pty
WHERE assignment_id = p_assignment_id
AND aei_information3 = p_pension_type_id
AND pty.pension_type_id = fnd_number.canonical_to_number(aei.aei_information3)
AND pension_sub_category in ('PPP' ,'OPNP' ,'OPNP_65'
,'OPNP_AOW' ,'OPNP_W25','OPNP_W50', 'AAOP'
,'FPU_B', 'FPU_C', 'FPU_E', 'FPU_R', 'FPU_S', 'FPU_T')
AND c_ed_dt BETWEEN pty.effective_start_date AND NVL(pty.effective_end_date ,to_date('31/12/4712','DD/MM/YYYY'))
AND aei_information23 IS NOT NULL
AND information_type = 'NL_ABP_PI'
AND aei_information_category = 'NL_ABP_PI'
AND TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt))
BETWEEN c_st_dt AND c_ed_dt
UNION
--
-- Rows that neither start or end in the current period
-- but the data in the EIT is valid for the current period
--
SELECT fnd_number.canonical_to_number(nvl(aei_information23,'1')) VOP
,TRUNC(fnd_date.canonical_to_date(aei_information1)) St_Dt
,TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt)) Ed_Dt
FROM per_assignment_extra_info aei,
pqp_pension_types_f pty
WHERE assignment_id = p_assignment_id
AND aei_information3 = p_pension_type_id
AND pty.pension_type_id = fnd_number.canonical_to_number(aei.aei_information3)
AND pension_sub_category in ('PPP' ,'OPNP' ,'OPNP_65'
,'OPNP_AOW' ,'OPNP_W25','OPNP_W50', 'AAOP'
,'FPU_B', 'FPU_C', 'FPU_E', 'FPU_R', 'FPU_S', 'FPU_T')
AND c_ed_dt BETWEEN pty.effective_start_date and NVL(pty.effective_end_date ,to_date('31/12/4712','DD/MM/YYYY'))
AND aei_information23 IS NOT NULL
AND information_type = 'NL_ABP_PI'
AND aei_information_category = 'NL_ABP_PI'
AND c_ed_dt BETWEEN TRUNC(fnd_date.canonical_to_date(aei_information1))
AND TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt))
ORDER BY st_dt;
Select 1
from pqp_pension_types_f pty
where pty.pension_type_id = p_pension_type_id
and pty.business_group_id = p_business_group_id
and p_date_earned between pty.effective_start_date and pty.effective_end_date;
Select pension_type_name
from pqp_pension_types_f
where pension_type_id = p_pension_type_id
and business_group_id = p_business_group_id;
Select paei.assignment_extra_info_id
from per_assignment_extra_info paei,
pqp_pension_types_f pty
where paei.information_type = 'NL_ABP_PI'
and paei.aei_information_category = 'NL_ABP_PI'
and paei.aei_information3 = to_char(p_pension_type_id)
and paei.assignment_id = p_assignment_id
and p_date_earned between trunc(to_date(substr(paei.aei_information1,1,10),'YYYY/MM/DD'))
and trunc(to_date(substr(nvl(paei.aei_information2,'4712/12/31'),1,10),'YYYY/MM/DD'));
Select *
from per_assignment_extra_info
where assignment_extra_info_id = c_asg_extra_info_id;
SELECT assignment_extra_info_id
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND information_type = 'NL_ABP_PAR_INFO'
AND aei_information_category = 'NL_ABP_PAR_INFO'
AND p_date_earned BETWEEN trunc(fnd_date.canonical_to_date(aei_information1))
AND trunc(nvl(fnd_date.canonical_to_date(aei_information2),hr_api.g_eot));
SELECT to_char(per.date_of_birth,'RRRR')
FROM per_all_people_f per,per_all_assignments_f paa
WHERE per.person_id = paa.person_id
AND p_date_earned between paa.effective_start_date and paa.effective_end_date
AND p_date_earned between per.effective_start_date and per.effective_end_date
AND paa.assignment_id = p_assignment_id;
SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = 'PQP_PENSION_SUB_CATEGORY'
AND lookup_code = c_sub_cat
AND language = 'US'
AND nvl(enabled_flag,'N') = 'Y';
SELECT aei_information3
FROM per_assignment_extra_info
WHERE p_date_earned between fnd_date.canonical_to_date(aei_information1)
AND fnd_date.canonical_to_date(nvl(aei_information2,fnd_date.date_to_canonical(hr_api.g_eot)))
AND assignment_id = p_assignment_id
AND aei_information_category = 'NL_ABP_PAR_INFO';
SELECT 1
FROM hr_organization_information hoi
WHERE hoi.org_information_context = 'PQP_NL_ABP_PT'
AND hoi.org_information3 = TO_CHAR(p_pt_id)
AND NVL(hoi.org_information6,'N') = 'Y'
AND NVL(hoi.org_information7,'N') = 'Y'
AND hoi.organization_id = p_hr_org_org_id
AND p_date_earned BETWEEN fnd_date.canonical_to_date(hoi.org_information1)
AND fnd_date.canonical_to_date(NVL(hoi.org_information2,
fnd_date.date_to_canonical(hr_api.g_eot)));
SELECT 1
FROM hr_organization_information hoi
WHERE hoi.org_information_context = 'PQP_NL_ABP_PT'
AND hoi.org_information3 = TO_CHAR(p_pt_id)
AND NVL(hoi.org_information6,'N') = 'Y'
AND NVL(hoi.org_information7,'N') = 'Y'
AND hoi.organization_id = p_le_org_id
AND p_date_earned BETWEEN fnd_date.canonical_to_date(hoi.org_information1)
AND fnd_date.canonical_to_date(NVL(hoi.org_information2,
fnd_date.date_to_canonical(hr_api.g_eot)))
AND NOT EXISTS ( SELECT 1
FROM hr_organization_information hoi
WHERE hoi.org_information_context = 'PQP_NL_ABP_PT'
AND hoi.org_information3 = TO_CHAR(p_pt_id)
AND ( NVL(hoi.org_information6,'N')= 'N'
OR NVL(hoi.org_information7,'N')= 'N')
AND hoi.organization_id = p_hr_org_org_id
AND p_date_earned BETWEEN fnd_date.canonical_to_date(hoi.org_information1)
AND fnd_date.canonical_to_date(NVL(hoi.org_information2,
fnd_date.date_to_canonical(hr_api.g_eot))));
SELECT fnd_number.canonical_to_number(NVL(hoi.org_information4,'-1'))
,fnd_number.canonical_to_number(NVL(hoi.org_information5,'-1'))
FROM hr_organization_information hoi
WHERE hoi.org_information_context = 'PQP_NL_ABP_PT'
AND hoi.org_information3 = TO_CHAR(p_pension_type_id)
AND hoi.org_information6 = 'Y'
AND hoi.organization_id = c_org_id
AND p_date_earned BETWEEN fnd_date.canonical_to_date(hoi.org_information1)
AND fnd_date.canonical_to_date(NVL(hoi.org_information2,
fnd_date.date_to_canonical(hr_api.g_eot)));
SELECT asg.organization_id
,asg.payroll_id
,fnd_number.canonical_to_number(ppf.prl_information1)
FROM per_all_assignments_f asg,
pay_payrolls_f ppf
WHERE asg.assignment_id = p_assignment_id
AND asg.payroll_id = ppf.payroll_id
AND TRUNC(p_date_earned) BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND TRUNC(p_date_earned) BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND asg.business_group_id = p_business_group_id;
SELECT fnd_number.canonical_to_number(nvl(ee_contribution_percent,'0'))
,fnd_number.canonical_to_number(nvl(er_contribution_percent,'0'))
FROM pqp_pension_types_f
WHERE pension_type_id = p_pension_type_id
AND p_date_earned BETWEEN effective_start_date
AND effective_end_date;
SELECT fnd_number.canonical_to_number(eei_information2) pty_id
FROM pay_element_type_extra_info pete,
pay_element_types_f pet
WHERE pete.information_type = 'PQP_NL_ABP_DEDUCTION'
AND pete.element_type_id = pet.element_type_id
AND p_date_earned BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND pet.business_group_id = p_business_group_id
AND pete.eei_information12 = p_pension_sub_cat
AND p_date_earned between TO_DATE(pete.eei_information10,'DD/MM/YYYY')
and TO_DATE(pete.eei_information11,'DD/MM/YYYY');
SELECT effective_date, date_earned
INTO l_eff_dt , l_dt_earned
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT NVL(paei.aei_information13,'PE'),
fnd_number.canonical_to_number(NVL(paei.aei_information14,'-1'))
,NVL(paei.aei_information15,'PE'),
fnd_number.canonical_to_number(NVL(paei.aei_information16,'-1'))
FROM per_assignment_extra_info paei
WHERE paei.information_type = 'NL_ABP_PI'
AND paei.aei_information_category = 'NL_ABP_PI'
AND paei.assignment_id = p_assignment_id
AND fnd_number.canonical_to_number(NVL(aei_information3,-1)) = c_pty_id
AND c_date_earned between fnd_date.canonical_to_date(paei.aei_information1)
AND fnd_date.canonical_to_date(NVL(paei.aei_information2,
fnd_date.date_to_canonical(hr_api.g_eot)));
SELECT fnd_number.canonical_to_number(NVL(hoi.org_information4,'-1'))
,fnd_number.canonical_to_number(NVL(hoi.org_information5,'-1'))
FROM hr_organization_information hoi
WHERE hoi.org_information_context = 'PQP_NL_ABP_PT'
AND hoi.org_information3 = TO_CHAR(c_pt_id)
AND hoi.org_information6 = 'Y'
AND NVL(hoi.org_information7,'Y') = 'Y'
AND hoi.organization_id = c_org_id
AND c_date_earned BETWEEN fnd_date.canonical_to_date(hoi.org_information1)
AND fnd_date.canonical_to_date(NVL(hoi.org_information2,
fnd_date.date_to_canonical(hr_api.g_eot)));
SELECT NVL(pty.ee_age_contribution,'N')
,NVL(pty.er_age_contribution,'N')
,ee_contribution_percent
,er_contribution_percent
FROM pqp_pension_types_f pty
WHERE c_date_earned BETWEEN pty.effective_start_date
AND pty.effective_end_date
AND pension_type_id = c_pty_id;
SELECT asg.organization_id
,asg.payroll_id
,fnd_number.canonical_to_number(ppf.prl_information1)
FROM per_all_assignments_f asg,
pay_payrolls_f ppf
WHERE asg.assignment_id = p_assignment_id
AND asg.payroll_id = ppf.payroll_id
AND TRUNC(c_date_earned) BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND TRUNC(c_date_earned) BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND asg.business_group_id = p_business_group_id;
SELECT LEAST(fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100')),125) ptp
FROM per_assignments_f asg
,hr_soft_coding_keyflex target
WHERE target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND asg.assignment_id = c_asg_id
AND target.enabled_flag = 'Y'
AND TRUNC(c_eff_dt) BETWEEN asg.effective_start_date AND
asg.effective_end_date;
SELECT effective_date
INTO l_date_earned
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT effective_date
INTO l_date_earned
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT effective_date
INTO l_date_earned
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT DECODE(l_ee_contrib_type,'PE',0,'FA',1)
INTO p_ee_contrib_type
FROM dual;
SELECT DECODE(l_er_contrib_type,'PE',0,'FA',1)
INTO p_er_contrib_type
FROM dual;
SELECT organization_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND trunc(p_date_earned) between effective_start_date and effective_end_date
AND business_group_id = p_business_group_id;
select org_information1
from hr_organization_information
where organization_id = p_business_group_id
and org_information_context = 'NL_BG_INFO';
select ORG_STRUCTURE_VERSION_ID
from per_org_structure_versions_v
where organization_structure_id = c_hierarchy_id
and p_date_earned between date_from
and nvl(date_to,hr_api.g_eot);
select ORG_STRUCTURE_VERSION_ID
from per_org_structure_versions_v
where business_group_id = p_business_group_id
and p_date_earned between date_from
and nvl( date_to,hr_api.g_eot);
select organization_id_parent
from per_org_structure_elements
where organization_id_child = c_org_id
AND org_structure_version_id = c_version_id
AND business_group_id = p_business_group_id;
Select paei.assignment_extra_info_id
from per_assignment_extra_info paei
where paei.information_type = 'NL_ABP_PI'
and paei.aei_information_category = 'NL_ABP_PI'
and paei.aei_information3 = to_char(p_pension_type_id)
and paei.assignment_id = p_assignment_id;
Select hoi.org_information_id
from hr_organization_information hoi
where hoi.org_information_context = 'PQP_NL_ABP_PT'
and hoi.org_information3 = to_char(p_pension_type_id)
AND hoi.org_information6 = 'Y'
AND NVL(hoi.org_information7,'Y') = 'Y'
and hoi.organization_id = c_org_id;
SELECT fnd_date.canonical_to_date(paei.aei_information1)
FROM per_assignment_extra_info paei
WHERE paei.information_type = 'NL_ABP_PI'
and paei.aei_information_category = 'NL_ABP_PI'
and paei.aei_information3 = to_char(p_pension_type_id)
and paei.assignment_id = p_assignment_id
and p_date_earned between fnd_date.canonical_to_date(paei.aei_information1)
and fnd_date.canonical_to_date(nvl(paei.aei_information2,fnd_date.date_to_canonical(hr_api.g_eot)));
SELECT fnd_date.canonical_to_date(hoi.org_information1)
FROM hr_organization_information hoi
where hoi.org_information_context = 'PQP_NL_ABP_PT'
and hoi.org_information3 = to_char(p_pension_type_id)
and hoi.org_information6 = 'Y'
and NVL(hoi.org_information7,'Y') = 'Y'
and hoi.organization_id = c_org_id
and p_date_earned between fnd_date.canonical_to_date(hoi.org_information1)
and fnd_date.canonical_to_date(nvl(hoi.org_information2,fnd_date.date_to_canonical(hr_api.g_eot)));
SELECT organization_id,business_group_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND trunc(p_date_earned) between effective_start_date and effective_end_date;
select org_information1
from hr_organization_information
where organization_id = c_bgid
and org_information_context = 'NL_BG_INFO';
select ORG_STRUCTURE_VERSION_ID
from per_org_structure_versions_v
where organization_structure_id = c_hierarchy_id
and p_date_earned between date_from
and nvl(date_to,hr_api.g_eot);
select ORG_STRUCTURE_VERSION_ID
from per_org_structure_versions_v
where business_group_id = c_bgid
and p_date_earned between date_from
and nvl( date_to,hr_api.g_eot);
select organization_id_parent
from per_org_structure_elements
where organization_id_child = c_org_id
AND org_structure_version_id = c_version_id
AND business_group_id = c_bgid;
Select paei.assignment_extra_info_id
from per_assignment_extra_info paei
where paei.information_type = 'NL_ABP_PI'
and paei.aei_information_category = 'NL_ABP_PI'
and paei.aei_information3 = to_char(p_pension_type_id)
and paei.assignment_id = p_assignment_id
and p_date_earned between trunc(to_date(substr(paei.aei_information1,1,10),'YYYY/MM/DD'))
and trunc(to_date(substr(nvl(paei.aei_information2,'4712/12/31'),1,10),'YYYY/MM/DD'));
Select hoi.org_information_id
from hr_organization_information hoi
where hoi.org_information_context = 'PQP_NL_ABP_PT'
and hoi.org_information3 = to_char(p_pension_type_id)
and hoi.org_information6 = 'Y'
AND NVL(hoi.org_information7,'Y') = 'Y'
and hoi.organization_id = c_org_id
and p_date_earned between trunc(to_date(substr(hoi.org_information1,1,10),'YYYY/MM/DD'))
and trunc(to_date(substr(nvl(hoi.org_information2,'4712/12/31'),1,10),'YYYY/MM/DD'));
SELECT effective_date
FROM fnd_sessions
WHERE session_id = userenv('sessionid');
SELECT 1
FROM hr_organization_information
WHERE EXISTS
(SELECT 1
FROM hr_organization_information
WHERE organization_id = p_organization_id
AND org_information_context = p_org_information_context
AND org_information_id <> p_org_information_id
);
SELECT effective_date
FROM fnd_sessions
WHERE session_id = userenv('sessionid');
SELECT ptp.start_date,ptp.end_date
FROM per_all_assignments_f asg
,per_time_periods ptp
WHERE asg.assignment_id = p_assignment_id
AND asg.payroll_id = ptp.payroll_id
AND p_date_earned BETWEEN ptp.start_date
AND ptp.end_date;
SELECT min(asg.effective_start_date)
,max(asg.effective_end_date)
FROM per_assignments_f asg
,per_assignment_status_types past
WHERE asg.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status = 'ACTIVE_ASSIGN'
AND asg.effective_start_date <= trunc(c_end_date)
AND nvl(asg.effective_end_date, trunc(c_end_date)) >= trunc(c_start_date)
AND asg.assignment_id = p_assignment_id
group by asg.assignment_id;
SELECT piv.input_value_id
,pet.element_type_id
FROM pay_input_values_f piv
,pay_element_types_f pet
WHERE piv.name = 'Reduction Percentage'
AND piv.element_type_id = pet.element_type_id
AND pet.element_name = 'ABP Pensions Premium Reduction Information'
AND p_date_earned BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND p_date_earned BETWEEN pet.effective_start_date
AND pet.effective_end_date;
SELECT fnd_number.canonical_to_number(nvl(screen_entry_value,'0')) perc_value
,pee.effective_start_date start_date
,pee.effective_end_date end_date
FROM pay_element_entry_values_f pef,
pay_element_entries_f pee
WHERE pef.input_value_id = c_input_value_id
AND pef.element_entry_id = pee.element_entry_id
AND pef.effective_start_date = pee.effective_start_date
AND pef.effective_end_date = pee.effective_end_date
AND pee.assignment_id = p_assignment_id
AND pee.element_type_id = c_element_type_id
AND (c_effective_date BETWEEN pee.effective_start_date AND
pee.effective_end_date OR
( pee.effective_start_date > c_effective_date
AND pee.effective_start_date <= c_period_end_date ));
SELECT ptp.start_date,ptp.end_date
FROM per_all_assignments_f asg
,per_time_periods ptp
WHERE asg.assignment_id = p_assignment_id
AND asg.payroll_id = ptp.payroll_id
AND p_date_earned BETWEEN ptp.start_date
AND ptp.end_date;
SELECT min(asg.effective_start_date)
,max(asg.effective_end_date)
FROM per_assignments_f asg
,per_assignment_status_types past
WHERE asg.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status = 'ACTIVE_ASSIGN'
AND asg.effective_start_date <= trunc(c_end_date)
AND nvl(asg.effective_end_date, trunc(c_end_date)) >= trunc(c_start_date)
AND asg.assignment_id = p_assignment_id
group by asg.assignment_id;
SELECT max(date_start)
FROM per_all_assignments_f asg
,per_periods_of_service pps
WHERE pps.person_id = asg.person_id
AND asg.assignment_id = c_asg_id
AND pps.business_group_id = p_business_group_id
AND date_start <= p_date_earned;
SELECT min(effective_start_date)
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND assignment_type = 'E';
SELECT organization_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND trunc(p_date_earned) between effective_start_date and effective_end_date
AND business_group_id = p_business_group_id;
select org_information1
from hr_organization_information
where organization_id = p_business_group_id
and org_information_context = 'NL_BG_INFO';
select ORG_STRUCTURE_VERSION_ID
from per_org_structure_versions_v
where organization_structure_id = c_hierarchy_id
and p_date_earned between date_from
and nvl(date_to,hr_api.g_eot);
select ORG_STRUCTURE_VERSION_ID
from per_org_structure_versions_v
where business_group_id = p_business_group_id
and p_date_earned between date_from
and nvl( date_to,hr_api.g_eot);
select organization_id_parent
from per_org_structure_elements
where organization_id_child = c_org_id
AND org_structure_version_id = c_version_id
AND business_group_id = p_business_group_id;
SELECT org_information3
FROM hr_organization_information
WHERE organization_id = c_org_id
AND org_information_context = 'PQP_NL_ABP_PTP_METHOD'
AND org_information3 IS NOT NULL;
SELECT fnd_number.canonical_to_number(segment29)
FROM hr_soft_coding_keyflex
WHERE soft_coding_keyflex_id = c_kff_id;
SELECT *
FROM ben_ext_chg_evt_log
WHERE chg_evt_cd = 'COPTP'
AND person_id = p_person_id
AND prmtr_04 = c_st_dt
-- AND prmtr_05 = c_ed_dt
AND prmtr_01 = c_asg_id
AND prmtr_02 = c_kff_id;
SELECT ext_chg_evt_log_id
,prmtr_09
FROM ben_ext_chg_evt_log
WHERE chg_evt_cd = 'COPTP'
AND person_id = p_person_id
AND prmtr_01 = c_asg_id
AND prmtr_02 = p_old_val1
AND DECODE(prmtr_05,'4712/12/31 00:00:00',
TRUNC(p_chg_eff_dt) - 1,
TRUNC(fnd_date.canonical_to_date(prmtr_05)))
= TRUNC(p_chg_eff_dt) - 1;
SELECT ext_chg_evt_log_id
,prmtr_09
FROM ben_ext_chg_evt_log
WHERE chg_evt_cd = 'COPTP'
AND person_id = p_person_id
AND prmtr_01 = c_asg_id
AND prmtr_02 = p_old_val1;
SELECT object_version_number
,ext_chg_evt_log_id
FROM ben_ext_chg_evt_log
WHERE chg_evt_cd = 'COPTP'
AND person_id = p_person_id
AND prmtr_01 = c_asg_id
AND prmtr_02 = p_old_val1;
hr_utility.set_location('GAA -- Updated prmtr_09 to the reporting date',10 );
IF p_prmtr_10 = 'UPDATE' THEN
l_asg_st_dt := p_prmtr_04;
UPDATE ben_ext_chg_evt_log
SET new_val1 = fnd_number.number_to_canonical(l_new_ptp)
,old_val1 = fnd_number.number_to_canonical(l_old_ptp)
,prmtr_09 = l_reporting_dt
WHERE ext_chg_evt_log_id = l_xst_log_rec.ext_chg_evt_log_id;
,p_prmtr_03 => p_prmtr_10 -- Update or Correction
,p_prmtr_04 => l_asg_st_dt -- Start of change
,p_prmtr_05 => l_asg_ed_dt -- End of change
,p_prmtr_06 => NULL
,p_prmtr_07 => NULL
,p_prmtr_08 => NULL
,p_prmtr_09 => l_reporting_dt -- Reporting Date
,p_prmtr_10 => NULL
,p_person_id => p_person_id
,p_business_group_id => p_business_group_id
,p_object_version_number => l_ovn
,p_effective_date => TRUNC(p_effective_date)
,p_chg_actl_dt => p_chg_actl_dt
,p_new_val1 => fnd_number.number_to_canonical(l_new_ptp)
,p_new_val2 => NULL
,p_new_val3 => NULL
,p_new_val4 => NULL
,p_new_val5 => NULL
,p_new_val6 => NULL
,p_old_val1 => fnd_number.number_to_canonical(l_old_ptp)
,p_old_val2 => NULL
,p_old_val3 => NULL
,p_old_val4 => NULL
,p_old_val5 => NULL
,p_old_val6 => NULL);
IF p_prmtr_10 = 'UPDATE' THEN
ben_xcl_ins.ins
(p_ext_chg_evt_log_id => l_id1
,p_chg_evt_cd => 'COPTP'
,p_chg_eff_dt => p_chg_eff_dt
,p_chg_user_id => p_chg_user_id
,p_prmtr_01 => p_prmtr_01 -- Assignment Id
,p_prmtr_02 => p_new_val1 -- New KFF Id
,p_prmtr_03 => p_prmtr_10 -- Update or Correction
,p_prmtr_04 => l_asg_st_dt -- Start of change
,p_prmtr_05 => l_asg_ed_dt -- End of change
,p_prmtr_06 => NULL
,p_prmtr_07 => NULL
,p_prmtr_08 => NULL
,p_prmtr_09 => l_reporting_dt -- Reporting Date
,p_prmtr_10 => NULL
,p_person_id => p_person_id
,p_business_group_id => p_business_group_id
,p_object_version_number => l_ovn1
,p_effective_date => TRUNC(p_effective_date)
,p_chg_actl_dt => p_chg_actl_dt
,p_new_val1 => fnd_number.number_to_canonical(l_new_ptp)
,p_new_val2 => NULL
,p_new_val3 => NULL
,p_new_val4 => NULL
,p_new_val5 => NULL
,p_new_val6 => NULL
,p_old_val1 => fnd_number.number_to_canonical(l_old_ptp)
,p_old_val2 => NULL
,p_old_val3 => NULL
,p_old_val4 => NULL
,p_old_val5 => NULL
,p_old_val6 => NULL);
hr_utility.set_location('GAA -- log row to track update of KFF Id . No chages to ptp ',10 );
IF p_prmtr_10 = 'UPDATE' THEN
--
-- Update any existing log rows based on the changes made
-- also set the end date in case of an update
--
FOR upd_rec IN c_upd_log_kff_upd(c_asg_id => p_prmtr_01) LOOP
IF trunc(fnd_date.canonical_to_date(upd_rec.prmtr_09)) =
trunc(fnd_date.canonical_to_date(l_reporting_dt)) THEN
UPDATE ben_ext_chg_evt_log
SET prmtr_05 = fnd_date.date_to_canonical(TRUNC(p_chg_eff_dt)-1)
,prmtr_09 = l_reporting_dt
WHERE ext_chg_evt_log_id = upd_rec.ext_chg_evt_log_id;
UPDATE ben_ext_chg_evt_log
SET prmtr_05 = fnd_date.date_to_canonical(TRUNC(p_chg_eff_dt)-1)
WHERE ext_chg_evt_log_id = upd_rec.ext_chg_evt_log_id;
hr_utility.set_location('GAA -- Updated the existing log to set the end as eff dt -1 ',10 );
UPDATE ben_ext_chg_evt_log
SET prmtr_02 = p_new_val1
,prmtr_09 = l_reporting_dt
WHERE ext_chg_evt_log_id = upd_rec.ext_chg_evt_log_id;
UPDATE ben_ext_chg_evt_log
SET prmtr_02 = p_new_val1
WHERE ext_chg_evt_log_id = upd_rec.ext_chg_evt_log_id;
UPDATE ben_ext_chg_evt_log
SET prmtr_05 = fnd_date.date_to_canonical(TRUNC(p_chg_eff_dt)-1)
,prmtr_09 = l_reporting_dt
WHERE ext_chg_evt_log_id = upd_rec.ext_chg_evt_log_id;
UPDATE ben_ext_chg_evt_log
SET prmtr_05 = fnd_date.date_to_canonical(TRUNC(p_chg_eff_dt)-1)
WHERE ext_chg_evt_log_id = upd_rec.ext_chg_evt_log_id;
hr_utility.set_location('GAA -- Updated the existing log to set the KFF id as the new KFF id ',10 );
END IF; -- Check for update or correction
hr_utility.set_location('... -- Updated prmtr_09 to the reporting date',10 );
hr_utility.set_location('... -- Updated prmtr_09 to the reporting date',10 );
update per_assignment_extra_info
set aei_information22 = NULL
where assignment_extra_info_id = fnd_number.canonical_to_number(p_prmtr_03);
SELECT LEAST(fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100')),125) ptp
FROM per_assignments_f asg
,hr_soft_coding_keyflex target
WHERE target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND asg.assignment_id = c_asg_id
AND target.enabled_flag = 'Y'
AND trunc(c_eff_dt) BETWEEN asg.effective_start_date AND
asg.effective_end_date;
SELECT ts.time_span_id
FROM pay_time_definitions s, pay_time_definitions e, pay_time_spans ts, pay_retro_components prc
WHERE ts.creator_id = prc.retro_component_id
AND prc.component_name ='Correction'
AND prc.legislation_code = 'NL'
AND ts.creator_type = 'RC'
AND ts.start_time_def_id = s.time_definition_id
AND ts.end_time_def_id = e.time_definition_id
AND s.legislation_code = 'NL'
AND s.definition_name = 'Start of Time'
AND e.legislation_code = 'NL'
AND e.definition_name = 'End of Previous Year';
SELECT peev1.screen_entry_value basis,
peev2.screen_entry_value paid,
pay_paywsmee_pkg.get_original_date_earned(pee.element_entry_id) orig_date_earned
FROM pay_element_entry_values_f peev1,
pay_element_entry_values_f peev2,
pay_element_entries_f pee,
pay_retro_component_usages prcu,
pay_retro_components prc,
pay_element_span_usages pesu
WHERE (prc.component_name ='Adjustment' OR (prc.component_name ='Correction'
AND pesu.TIME_SPAN_ID = p_time_span_id) )
AND pee.assignment_id = p_asg_id
AND prc.legislation_code = 'NL'
AND prcu.retro_component_id = prc.retro_component_id
AND prcu.creator_id = p_element_type_id
AND pesu.retro_component_usage_id = prcu.retro_component_usage_id
AND pesu.retro_element_type_id = pee.element_type_id
AND pee.element_entry_id = peev1.element_entry_id
AND peev1.input_value_id = p_iv_id
AND pee.element_entry_id = peev2.element_entry_id
AND peev2.input_value_id = p_iv_id_pv
AND p_date_earned BETWEEN pee.effective_start_date AND pee.effective_end_date
AND p_date_earned BETWEEN peev1.effective_start_date AND peev1.effective_end_date
AND TO_NUMBER(TO_CHAR(pay_paywsmee_pkg.get_original_date_earned(pee.element_entry_id),'YYYY'))
< TO_NUMBER(TO_CHAR(p_date_earned,'YYYY'))
AND p_date_earned BETWEEN peev2.effective_start_date AND peev2.effective_end_date;
SELECT piv.element_type_id ele_id, piv.input_value_id iv_id
FROM pay_input_values_f piv,
pay_retro_component_usages prcu,
pay_retro_components prc,
pay_element_span_usages pesu
WHERE prc.legislation_code = 'NL'
AND prcu.retro_component_id = prc.retro_component_id
AND prcu.creator_id = p_element_type_id
AND (prc.component_name ='Adjustment' OR (prc.component_name ='Correction'
AND pesu.TIME_SPAN_ID = p_time_span_id) )
AND pesu.retro_component_usage_id = prcu.retro_component_usage_id
AND piv.name = 'Pay Value'
AND piv.element_type_id = pesu.retro_element_type_id
AND p_date_earned BETWEEN piv.effective_start_date AND piv.effective_end_date;
SELECT input_value_id
FROM pay_input_values_f
WHERE element_type_id = p_retr_ele_type_id
AND name = p_iv_name
AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
SELECT NVL(aei_information3,'Y') aei_information3
FROM per_assignment_extra_info
WHERE information_type = 'NL_ADDL_CALC'
AND assignment_id = p_asg_id
--AND p_date_earned BETWEEN fnd_date.canonical_to_date(aei_information1) AND fnd_date.canonical_to_date(nvl(aei_information2,'4712/12/31'));