[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select nvl(hoi.org_information16,'N')
into l_return_value
from per_assignments_f paf,
hr_organization_information hoi,
hr_soft_coding_keyflex hsk
where paf.assignment_id = p_assignment_id
and p_date_earned between paf.effective_start_date
and paf.effective_end_date
and paf.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id
and hsk.segment1 = hoi.organization_id
and hoi.org_information_context = 'W2 Reporting Rules';
SELECT hr_us_ff_udfs.addr_val(NVL(add_information17, region_2),
NVL(add_information19, region_1),
NVL(add_information18, town_or_city),
NVL(add_information20, postal_code)
)
FROM per_addresses pad,
per_assignments_f paf,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE pad.primary_flag = 'Y'
AND pad.person_id = paf.person_id
AND ppa.date_earned BETWEEN pad.date_from
AND NVL(pad.date_to, TO_DATE('12/31/4712',
'MM/DD/YYYY'))
AND ppa.date_earned BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.assignment_id = paa.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_action_id = p_assignment_action_id;
SELECT paa.assignment_id
INTO l_assignment_id
FROM pay_assignment_actions paa
where paa.assignment_action_id = p_assignment_action_id;
SELECT hr_us_ff_udfs.addr_val(NVL(add_information17, region_2),
NVL(add_information19, region_1),
NVL(add_information18, town_or_city),
NVL(add_information20, postal_code)
)
INTO l_home_juris
FROM per_addresses pad,
per_assignments_f paf,
fnd_sessions fs
WHERE pad.primary_flag = 'Y'
AND pad.person_id = paf.person_id
AND fs.effective_date BETWEEN pad.date_from
AND NVL(pad.date_to, TO_DATE('12/31/4712',
'MM/DD/YYYY'))
AND fs.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND fs.session_id = USERENV('sessionid')
AND paf.assignment_id = l_assignment_id; */
SELECT /*+ RULE */ paa1.assignment_action_id,
ppa1.payroll_id
INTO l_bal_assact,
l_payroll_id
FROM pay_assignment_Actions paa1,
pay_payroll_actions ppa1
WHERE paa1.assignment_id = l_assignment_id
AND paa1.tax_unit_id = p_tax_unit_id
AND paa1.action_sequence =
(SELECT max(paa_prev.action_sequence)
FROM per_time_periods ptp
, pay_payroll_actions ppa
, pay_assignment_actions paa
, per_time_periods ptp_prev
, pay_payroll_actions ppa_prev
, pay_assignment_actions paa_prev
WHERE paa.assignment_action_id = p_assignment_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
-- AND ptp.time_period_id = ppa.time_period_id
AND ppa.effective_date between ptp.start_date /*Bug:3909937*/
and ptp.end_date
AND ptp.payroll_id = ppa.payroll_id
AND ptp_prev.payroll_id = ppa.payroll_id
AND ptp.start_date - 1 between ptp_prev.start_date
and ptp_prev.end_date
AND paa_prev.assignment_id = paa.assignment_id
AND paa_prev.payroll_action_id = ppa_prev.payroll_action_id
AND ppa_prev.action_type IN ('R', 'Q', 'B')
-- AND ppa_prev.time_period_id = ptp_prev.time_period_id)
--AND ppa_prev.date_earned between ptp_prev.start_date
AND ppa_prev.effective_date between ptp_prev.start_date
and ptp_prev.end_date)
AND paa1.payroll_action_id = ppa1.payroll_action_id ;
SELECT creator_id
INTO l_defined_balance_tab(1).defined_balance_id
FROM ff_user_entities
WHERE user_entity_name = 'REGULAR_EARNINGS_' || l_asg_type || '_GRE_PTD'
AND legislation_code = 'US';
SELECT creator_id
INTO l_defined_balance_tab(2).defined_balance_id
FROM ff_user_entities
WHERE user_entity_name = 'SUPPLEMENTAL_EARNINGS_FOR_FIT_SUBJECT_TO_TAX_' || l_asg_type || '_GRE_PTD'
AND legislation_code = 'US';
SELECT creator_id
INTO l_defined_balance_tab(3).defined_balance_id
FROM ff_user_entities
WHERE user_entity_name = 'FIT_NON_AGGREGATE_RED_SUBJ_WHABLE_' || l_asg_type || '_GRE_PTD'
AND legislation_code = 'US';
SELECT creator_id
INTO l_defined_balance_tab(4).defined_balance_id
FROM ff_user_entities
WHERE user_entity_name = 'DEF_COMP_401K_' || l_asg_type || '_GRE_PTD'
AND legislation_code = 'US';
SELECT creator_id
INTO l_defined_balance_tab(5).defined_balance_id
FROM ff_user_entities
WHERE user_entity_name = 'DEF_COMP_403B_' || l_asg_type || '_GRE_PTD'
AND legislation_code = 'US';
SELECT creator_id
INTO l_defined_balance_tab(6).defined_balance_id
FROM ff_user_entities
WHERE user_entity_name = 'DEF_COMP_457_' || l_asg_type || '_GRE_PTD'
AND legislation_code = 'US';
SELECT creator_id
INTO l_defined_balance_tab(7).defined_balance_id
FROM ff_user_entities
WHERE user_entity_name = 'OTHER_PRETAX_' || l_asg_type || '_GRE_PTD'
AND legislation_code = 'US';
SELECT creator_id
INTO l_defined_balance_tab(8).defined_balance_id
FROM ff_user_entities
WHERE user_entity_name = 'SECTION_125_' || l_asg_type || '_GRE_PTD'
AND legislation_code = 'US';
SELECT creator_id
INTO l_defined_balance_tab(9).defined_balance_id
FROM ff_user_entities
WHERE user_entity_name = 'DEPENDENT_CARE_' || l_asg_type || '_GRE_PTD'
AND legislation_code = 'US';
SELECT creator_id
INTO l_defined_balance_tab(10).defined_balance_id
FROM ff_user_entities
WHERE user_entity_name = 'DEF_COMP_401K_FOR_FIT_SUBJECT_TO_TAX_' || l_asg_type || '_GRE_PTD'
AND legislation_code = 'US';
SELECT creator_id
INTO l_defined_balance_tab(11).defined_balance_id
FROM ff_user_entities
WHERE user_entity_name = 'DEF_COMP_403B_FOR_FIT_SUBJECT_TO_TAX_' || l_asg_type || '_GRE_PTD'
AND legislation_code = 'US';
SELECT creator_id
INTO l_defined_balance_tab(12).defined_balance_id
FROM ff_user_entities
WHERE user_entity_name = 'DEF_COMP_457_FOR_FIT_SUBJECT_TO_TAX_' || l_asg_type || '_GRE_PTD'
AND legislation_code = 'US';
SELECT creator_id
INTO l_defined_balance_tab(13).defined_balance_id
FROM ff_user_entities
WHERE user_entity_name = 'OTHER_PRETAX_FOR_FIT_SUBJECT_TO_TAX_' || l_asg_type || '_GRE_PTD'
AND legislation_code = 'US';
SELECT creator_id
INTO l_defined_balance_tab(14).defined_balance_id
FROM ff_user_entities
WHERE user_entity_name = 'SECTION_125_FOR_FIT_SUBJECT_TO_TAX_' || l_asg_type || '_GRE_PTD'
AND legislation_code = 'US';
SELECT creator_id
INTO l_defined_balance_tab(15).defined_balance_id
FROM ff_user_entities
WHERE user_entity_name = 'DEPENDENT_CARE_FOR_FIT_SUBJECT_TO_TAX_' || l_asg_type || '_GRE_PTD'
AND legislation_code = 'US';
SELECT creator_id
INTO l_defined_balance_tab(16).defined_balance_id
FROM ff_user_entities
WHERE user_entity_name = 'FIT_NON_W2_DEF_COMP_401_' || l_asg_type || '_GRE_PTD'
AND legislation_code = 'US';
SELECT creator_id
INTO l_defined_balance_tab(17).defined_balance_id
FROM ff_user_entities
WHERE user_entity_name = 'FIT_NON_W2_DEF_COMP_403_' || l_asg_type || '_GRE_PTD'
AND legislation_code = 'US';
SELECT creator_id
INTO l_defined_balance_tab(18).defined_balance_id
FROM ff_user_entities
WHERE user_entity_name = 'FIT_NON_W2_DEF_COMP_457_' || l_asg_type || '_GRE_PTD'
AND legislation_code = 'US';
SELECT creator_id
INTO l_defined_balance_tab(19).defined_balance_id
FROM ff_user_entities
WHERE user_entity_name = 'FIT_NON_W2_SECTION_125_' || l_asg_type || '_GRE_PTD'
AND legislation_code = 'US';
SELECT creator_id
INTO l_defined_balance_tab(20).defined_balance_id
FROM ff_user_entities
WHERE user_entity_name = 'FIT_NON_W2_DEPENDENT_CARE_' || l_asg_type || '_GRE_PTD'
AND legislation_code = 'US';
SELECT creator_id
INTO l_defined_balance_tab(21).defined_balance_id
FROM ff_user_entities
WHERE user_entity_name = 'FIT_NON_W2_OTHER_PRETAX_' || l_asg_type || '_GRE_PTD'
AND legislation_code = 'US';
SELECT creator_id
INTO l_defined_balance_tab(22).defined_balance_id
FROM ff_user_entities
WHERE user_entity_name = 'FIT_WITHHELD_' || l_asg_type || '_GRE_PTD'
AND legislation_code = 'US';
SELECT creator_id
INTO l_defined_balance_tab(23).defined_balance_id
FROM ff_user_entities
WHERE user_entity_name = 'FIT_SUPP_WITHHELD_' || l_asg_type || '_GRE_PTD'
AND legislation_code = 'US';
SELECT creator_id
INTO l_defined_balance_tab(1).defined_balance_id
FROM ff_user_entities
WHERE user_entity_name = 'SIT_SUBJ_WHABLE_' || l_asg_type || '_JD_GRE_PTD'
AND legislation_code = 'US';
SELECT creator_id
INTO l_defined_balance_tab(2).defined_balance_id
FROM ff_user_entities
WHERE user_entity_name = 'SIT_NON_AGGREGATE_RED_SUBJ_WHABLE_' || l_asg_type || '_JD_GRE_PTD'
AND legislation_code = 'US';
SELECT creator_id
INTO l_defined_balance_tab(3).defined_balance_id
FROM ff_user_entities
WHERE user_entity_name = 'SIT_PRE_TAX_REDNS_' || l_asg_type || '_JD_GRE_PTD'
AND legislation_code = 'US';
SELECT creator_id
INTO l_defined_balance_tab(4).defined_balance_id
FROM ff_user_entities
WHERE user_entity_name = 'SIT_WITHHELD_' || l_asg_type || '_JD_GRE_PTD'
AND legislation_code = 'US';
SELECT creator_id
INTO l_defined_balance_tab(5).defined_balance_id
FROM ff_user_entities
WHERE user_entity_name = 'SIT_SUPP_WITHHELD_' || l_asg_type || '_JD_GRE_PTD'
AND legislation_code = 'US';
SELECT creator_id
INTO l_defined_balance_tab(1).defined_balance_id
FROM ff_user_entities
WHERE user_entity_name = 'CITY_WITHHELD_' || l_asg_type || '_JD_GRE_PTD'
AND legislation_code = 'US';
SELECT creator_id
INTO l_defined_balance_tab(1).defined_balance_id
FROM ff_user_entities
WHERE user_entity_name = 'COUNTY_WITHHELD_' || l_asg_type || '_JD_GRE_PTD'
AND legislation_code = 'US';
SELECT creator_id
INTO l_defined_balance_tab(1).defined_balance_id
FROM ff_user_entities
WHERE user_entity_name = 'SCHOOL_WITHHELD_' || l_asg_type || '_JD_GRE_PTD'
AND legislation_code = 'US';
select pev1.element_entry_id,
pev1.screen_entry_value Jurisdiction_code,
pev2.screen_entry_value Percentage
from pay_element_entry_values_f pev1,
pay_element_entry_values_f pev2,
pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet,
pay_input_values_f piv1,
pay_input_values_f piv2
where pee.assignment_id = l_assignment_id
-- and l_date_paid between pee.effective_start_date
and l_date_earned between pee.effective_start_date
and pee.effective_end_date
and pee.element_link_id = pel.element_link_id
and pee.effective_start_date between pel.effective_start_date
and pel.effective_end_date
and pel.element_type_id = pet.element_type_id
and pet.element_name = 'VERTEX'
and pee.effective_start_date between pet.effective_start_date
and pet.effective_end_date
and pee.element_entry_id = pev1.element_entry_id
and pee.effective_start_date between pev1.effective_start_date
and pev1.effective_end_date
and pev1.input_value_id = piv1.input_value_id
and pee.effective_start_date between piv1.effective_start_date
and piv1.effective_end_date
and piv1.name = 'Jurisdiction'
and pee.element_entry_id = pev2.element_entry_id
and pee.effective_start_date between pev2.effective_start_date
and pev2.effective_end_date
and pev2.input_value_id = piv2.input_value_id
and pee.effective_start_date between piv2.effective_start_date
and piv2.effective_end_date
and piv2.name = 'Percentage';
select /*+ INDEX (paa pay_assignment_actions_n51) */ distinct
peev.element_entry_id,
peev.screen_entry_value
from pay_element_classifications pec
,pay_element_types_f pet
,pay_element_entries_f pee
,pay_element_links_f pel
,pay_input_values_f piv
,pay_element_entry_values_f peev
where pec.classification_name in
( 'Earnings', 'Supplemental Earnings','Imputed Earnings' )
and pet.classification_id = pec.classification_id
and pee.effective_start_date between pet.effective_start_date
and pet.effective_end_date
and pee.assignment_id = l_assignment_id
and l_date_earned between pee.effective_start_date
and pee.effective_end_date
and pet.element_type_id = pel.element_type_id
and pel.element_link_id = pee.element_link_id
and pee.effective_start_date between pel.effective_start_date
and pel.effective_end_date
and pet.element_type_id = piv.element_type_id
and piv.name = 'Jurisdiction'
and pee.effective_start_date between piv.effective_start_date
and piv.effective_end_date
and pee.element_entry_id = peev.element_entry_id
and peev.input_value_id = piv.input_value_id
and pee.effective_start_date between peev.effective_start_date
and peev.effective_end_date
and peev.screen_entry_value is not null;
SELECT NVL(fed.fed_information1,'N'),paa.assignment_id
FROM pay_us_emp_fed_tax_rules_f fed,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.assignment_id = fed.assignment_id
AND paa.assignment_action_id = p_assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND NVL(ppa.date_earned,ppa.effective_date)
BETWEEN fed.effective_start_date AND fed.effective_end_date;
SELECT paa.assignment_id,
ppa.EFFECTIVE_DATE,
ppa.date_earned,
ppa.time_period_id,
ppa.payroll_id,
ppa.business_group_id,
paa.tax_unit_id
INTO l_assignment_id,
l_date_paid,
l_date_earned,
l_time_period_id,
l_payroll_id,
l_business_group_id,
l_tax_unit_id
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.assignment_action_id = p_assignment_action_id
AND ppa.payroll_action_id = paa.payroll_action_id;
jurisdiction_codes_tbl.delete;
res_jurisdiction_codes_tbl.delete; -- Added for Bug # 4715851
state_processed_tbl.delete;
county_processed_tbl.delete;
city_processed_tbl.delete;
SELECT nvl(ADDR.add_information17,ADDR.region_2) state,
nvl(ADDR.add_information19,ADDR.region_1) county,
nvl(ADDR.add_information18,ADDR.town_or_city) city,
nvl(ADDR.add_information20,ADDR.postal_code) zip,
nvl(ASSIGN.work_at_home,'N')
INTO l_res_state,
l_res_county,
l_res_city,
l_res_zip,
l_wah
FROM per_addresses ADDR
,per_all_assignments_f ASSIGN
WHERE l_date_earned BETWEEN ASSIGN.effective_start_date
AND ASSIGN.effective_end_date
and ASSIGN.assignment_id = l_assignment_id
and ADDR.person_id = ASSIGN.person_id
and ADDR.primary_flag = 'Y'
and l_date_earned BETWEEN nvl(ADDR.date_from, l_date_earned)
AND nvl(ADDR.date_to, l_date_earned);
SELECT nvl(HRLOC.loc_information18,HRLOC.town_or_city),
nvl(HRLOC.loc_information19,HRLOC.region_1),
nvl(HRLOC.loc_information17,HRLOC.region_2),
substr(nvl(HRLOC.loc_information20,HRLOC.postal_code)
,1,5)
INTO l_city,
l_county,
l_state,
l_zip_code
FROM hr_locations HRLOC
, hr_soft_coding_keyflex HRSCKF
, per_all_assignments_f ASSIGN
WHERE l_date_earned BETWEEN ASSIGN.effective_start_date
AND ASSIGN.effective_end_date
AND ASSIGN.assignment_id = l_assignment_id
AND ASSIGN.soft_coding_keyflex_id = HRSCKF.soft_coding_keyflex_id
AND nvl(HRSCKF.segment18,
ASSIGN.location_id) = HRLOC.location_id;
select sta_information2
from pay_us_emp_state_tax_rules_f
where assignment_id = p_assignment_id
and p_date_earned between effective_start_date and effective_end_date
and jurisdiction_code = p_jurisdiction_code;
select ptp.start_date,
ptp.end_date
from per_all_assignments_f paaf,
pay_all_payrolls_f papf,
per_time_periods ptp
where paaf.payroll_id = papf.payroll_id
and papf.payroll_id = ptp.payroll_id
and assignment_id = p_assignment_id
and p_date_earned between ptp.start_date
and ptp.end_date
and p_date_earned between papf.effective_start_date
and papf.effective_end_date
and p_date_earned between paaf.effective_start_date
and paaf.effective_end_date;
SELECT 1
FROM per_all_assignments_f paaf
WHERE l_date between paaf.effective_start_date
and paaf.effective_end_date;
SELECT NVL(prl_information12 ,'P') --Defaulted to Previous
,NVL(prl_information13 ,'YTD') --defaulted to Tax Year
FROM pay_payrolls_f payroll,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE ppa.payroll_id = payroll.payroll_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_action_id = p_assignment_action_id
AND NVL(ppa.date_earned,ppa.effective_date)
BETWEEN payroll.effective_start_date
AND payroll.effective_end_date;
SELECT pev1.screen_entry_value Jurisdiction,
SUM(pev2.screen_entry_value) Hours
FROM pay_element_entry_values_f pev1,
pay_element_entry_values_f pev2,
pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet,
pay_input_values_f piv1,
pay_input_values_f piv2,
pay_element_type_extra_info extra
WHERE extra.information_type = 'PAY_US_INFORMATION_TIME'
AND extra.eei_information1 = 'Y'
AND extra.element_type_id = pet.element_type_id
AND pet.element_type_id = pel.element_type_id
AND p_end_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND pel.element_link_id = pee.element_link_id
AND p_end_date BETWEEN pel.effective_start_date
AND pel.effective_end_date
AND pee.assignment_id = p_assignment_id
AND ( (pee.effective_start_date BETWEEN p_start_date
AND p_end_date)
OR
(pee.effective_end_date BETWEEN p_start_date
AND p_end_date)
)
AND pee.element_entry_id = pev1.element_entry_id
AND p_end_date BETWEEN pee.effective_start_date
AND pee.effective_end_date
AND pev1.input_value_id = piv1.input_value_id
AND p_end_date BETWEEN pev1.effective_start_date
AND pev1.effective_end_date
AND piv1.name = 'Jurisdiction'
AND p_end_date BETWEEN piv1.effective_start_date
AND piv1.effective_end_date
AND pee.element_entry_id = pev2.element_entry_id
AND p_end_date BETWEEN pee.effective_start_date
AND pee.effective_end_date
AND pev2.input_value_id = piv2.input_value_id
AND piv2.name = 'Hours'
AND p_end_date BETWEEN piv2.effective_start_date
AND piv2.effective_end_date
AND pev1.screen_entry_value IS NOT NULL
AND pev2.screen_entry_value IS NOT NULL
GROUP BY pev1.screen_entry_value;
SELECT end_date,
start_date
FROM per_time_periods
WHERE time_period_id = p_time_period_id
AND payroll_id = p_payroll_id;
SELECT end_date,
start_date
FROM per_time_periods
WHERE end_date = p_prev_end_date
AND payroll_id = p_payroll_id;
SELECT NVL(hrloc.loc_information18,hrloc.town_or_city),
NVL(hrloc.loc_information19,hrloc.region_1),
NVL(hrloc.loc_information17,hrloc.region_2),
SUBSTR(NVL(hrloc.loc_information20,hrloc.postal_code),1,5)
FROM hr_locations hrloc
,hr_soft_coding_keyflex hrsckf
,per_all_assignments_f assign
WHERE p_date_earned BETWEEN assign.effective_start_date
AND assign.effective_end_date
AND assign.assignment_id = p_assignment_id
AND assign.soft_coding_keyflex_id = hrsckf.soft_coding_keyflex_id
AND NVL(hrsckf.segment18,
assign.location_id) = hrloc.location_id;
SELECT pev1.screen_entry_value Jurisdiction,
SUM(pev2.screen_entry_value) Hours
FROM pay_element_entry_values_f pev1,
pay_element_entry_values_f pev2,
pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet,
pay_input_values_f piv1,
pay_input_values_f piv2,
pay_element_classifications pec
WHERE pec.classification_name IN ( 'Earnings', 'Supplemental Earnings','Imputed Earnings' )
AND pec.legislation_code = 'US'
AND pec.classification_id = pet.classification_id
AND pet.element_type_id = pel.element_type_id
AND p_date_earned BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND pel.element_link_id = pee.element_link_id
AND p_date_earned BETWEEN pel.effective_start_date
AND pel.effective_end_date
AND pee.assignment_id = p_assignment_id
AND pee.effective_start_date BETWEEN p_start_date
AND p_end_date
AND pee.element_entry_id = pev1.element_entry_id
AND p_date_earned BETWEEN pee.effective_start_date
AND pee.effective_end_date
AND pev1.input_value_id = piv1.input_value_id
AND p_date_earned BETWEEN pev1.effective_start_date
AND pev1.effective_end_date
AND piv1.name = 'Jurisdiction'
AND p_date_earned BETWEEN piv1.effective_start_date
AND piv1.effective_end_date
AND pee.element_entry_id = pev2.element_entry_id
AND p_date_earned BETWEEN pee.effective_start_date
AND pee.effective_end_date
AND pev2.input_value_id = piv2.input_value_id
AND piv2.name = 'Hours'
AND p_date_earned BETWEEN piv2.effective_start_date
AND piv2.effective_end_date
AND pev1.screen_entry_value IS NOT NULL
AND pev2.screen_entry_value IS NOT NULL
GROUP BY pev1.screen_entry_value;
SELECT /*+ INDEX (paa pay_assignment_actions_n51) */ DISTINCT
peev.screen_entry_value Jurisdiction,
0 Hours
FROM pay_element_classifications pec
,pay_element_types_f pet
,pay_element_entries_f pee
,pay_element_links_f pel
,pay_input_values_f piv
,pay_element_entry_values_f peev
WHERE pec.classification_name in
( 'Earnings', 'Supplemental Earnings','Imputed Earnings' )
AND pec.legislation_code = 'US'
AND pet.classification_id = pec.classification_id
AND (( pee.effective_start_date BETWEEN p_start_date
AND p_end_date)
OR
( pee.effective_end_date BETWEEN p_start_date
AND p_end_date)
)
AND p_end_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND pee.assignment_id = p_assignment_id
AND pet.element_type_id = pel.element_type_id
AND pel.element_link_id = pee.element_link_id
AND p_end_date BETWEEN pel.effective_start_date
AND pel.effective_end_date
AND pet.element_type_id = piv.element_type_id
AND piv.name = 'Jurisdiction'
AND pee.effective_start_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND pee.element_entry_id = peev.element_entry_id
AND peev.input_value_id = piv.input_value_id
AND pee.effective_start_date BETWEEN peev.effective_start_date
AND peev.effective_end_date
AND peev.screen_entry_value IS NOT NULL;
SELECT segment4
FROM hr_soft_coding_keyflex target,
per_all_assignments_f assign
WHERE ASSIGN.assignment_id = p_assignment_id
AND target.soft_coding_keyflex_id = ASSIGN.soft_coding_keyflex_id
AND target.enabled_flag = 'Y'
AND p_date_earned BETWEEN assign.effective_start_date
AND assign.effective_end_date;
SELECT NVL(addr.add_information17,addr.region_2) state,
NVL(addr.add_information19,addr.region_1) county,
NVL(addr.add_information18,addr.town_or_city) city,
NVL(addr.add_information20,addr.postal_code) zip,
NVL(assign.work_at_home,'N')
FROM per_addresses addr
,per_all_assignments_f assign
WHERE p_date_earned BETWEEN assign.effective_start_date
AND assign.effective_end_date
AND assign.assignment_id = p_assignment_id
AND addr.person_id = assign.person_id
AND addr.primary_flag = 'Y'
AND p_date_earned BETWEEN NVL(addr.date_from, p_date_earned)
AND NVL(addr.date_to, p_date_earned);
SELECT ppf.full_name, paf.assignment_number
FROM per_all_people_f ppf,
per_all_assignments_f paf
WHERE ppf.person_id = paf.person_id
AND paf.assignment_id = p_assignment_id
AND p_date_paid BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND p_date_paid BETWEEN ppf.effective_start_date
AND ppf.effective_end_date;
SELECT paa.effective_start_date,
paa.effective_end_date
FROM per_all_assignments_f paa
WHERE paa.assignment_id = p_assignment_id
AND p_date_paid BETWEEN paa.effective_start_date
AND paa.effective_end_date;
SELECT pps.actual_termination_date,pps.date_start
FROM per_periods_of_service pps,
per_all_assignments_f paa
WHERE paa.assignment_id = p_assignment_id
AND paa.person_id = pps.person_id
AND p_date_earned BETWEEN paa.effective_start_date
AND paa.effective_end_date
AND p_date_end >= pps.date_start
AND p_date_start <= NVL(pps.actual_termination_date,to_date('31-12-4712','DD-MM-YYYY'));
SELECT person_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id;
jurisdiction_codes_tbl.delete;
state_processed_tbl.delete;
county_processed_tbl.delete;
city_processed_tbl.delete;
jurisdiction_codes_tbl_stg.delete;
-- Insert work jurisdiction into pl table with jd_type as WK
jurisdiction_codes_tbl_stg(
TO_NUMBER(SUBSTR(l_work_jurisdiction_code,1,2) ||
SUBSTR(l_work_jurisdiction_code,4,3) ||
SUBSTR(l_work_jurisdiction_code,8,4) )
).jurisdiction_code := l_work_jurisdiction_code;
jd_codes_tbl_state_stg.delete;
jd_codes_tbl_state.delete;
jd_codes_tbl_county_stg.delete;
jd_codes_tbl_county.delete;
jd_codes_tbl_city_stg.delete;
assignemnt has crossed the threshold limit already and inserts the
current record. But there are some situations where tax is withheld
for an assignment even before the threshold limit is reached. So commented
the following code so that it can go on with threshold checking irrespective
of SIT Withheld balance.
*/
/* l_sit_withheld :=
hr_us_ff_udf1.get_jd_tax_balance(p_threshold_basis => l_threshold_basis
,p_assignment_action_id => p_assignment_action_id
,p_jurisdiction_code => l_jurisdiction
,p_tax_unit_id => p_tax_unit_id
,p_jurisdiction_level => 'STATE'
,p_effective_date => p_date_paid
,p_assignment_id => p_assignment_id);
the assignemnt has crossed the threshold limit already and inserts the
current record. Commented the following code so that it can go on with
threshold checking irrespective of county tax Withheld balance.
*/
/* l_county_withheld :=
hr_us_ff_udf1.get_jd_tax_balance(p_threshold_basis => l_threshold_basis
,p_assignment_action_id=> p_assignment_action_id
,p_jurisdiction_code => l_jurisdiction
,p_tax_unit_id => p_tax_unit_id
,p_jurisdiction_level => 'COUNTY'
,p_effective_date => p_date_paid
,p_assignment_id => p_assignment_id);
the assignemnt has crossed the threshold limit already and inserts the
current record. Commented the following code so that it can go on with
threshold checking irrespective of city tax Withheld balance.
*/
/* l_city_withheld :=
hr_us_ff_udf1.get_jd_tax_balance(p_threshold_basis => l_threshold_basis
,p_assignment_action_id=> p_assignment_action_id
,p_jurisdiction_code => l_jurisdiction
,p_tax_unit_id => p_tax_unit_id
,p_jurisdiction_level => 'CITY'
,p_effective_date => p_date_paid
,p_assignment_id => p_assignment_id);
SELECT pdb.defined_balance_id, pbt.balance_type_id
FROM pay_defined_balances pdb,
pay_balance_types pbt,
pay_balance_dimensions pbd
WHERE pdb.balance_dimension_id = pbd.balance_dimension_id
AND pdb.balance_type_id = pbt.balance_type_id
AND pbt.balance_name = p_balance_name
AND pbd.database_item_suffix = p_database_item_suffix
AND pdb.legislation_code = 'US';
SELECT NVL(org_information2,0)
FROM hr_organization_information hoi,
pay_us_states pus
WHERE hoi.organization_id = p_tax_unit_id
AND hoi.org_information_context = 'State Tax Rules 2'
AND hoi.org_information1 = pus.state_abbrev
AND pus.state_code = p_state_code;
SELECT NVL(org_information4,0)
FROM hr_organization_information
WHERE organization_id = p_tax_unit_id
AND org_information_context = 'Local Tax Rules'
AND org_information1 = p_jurisdiction_code;
SELECT SUM(pev2.screen_entry_value) Hours
FROM pay_element_entry_values_f pev1,
pay_element_entry_values_f pev2,
pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet,
pay_input_values_f piv1,
pay_input_values_f piv2,
pay_element_type_extra_info extra,
per_assignments_f paf
WHERE extra.information_type = 'PAY_US_INFORMATION_TIME'
AND extra.eei_information1 = 'Y'
AND extra.element_type_id = pet.element_type_id
AND pet.element_type_id = pel.element_type_id
AND pee.effective_start_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND pel.element_link_id = pee.element_link_id
AND pee.effective_start_date BETWEEN pel.effective_start_date
AND pel.effective_end_date
AND pee.effective_start_date BETWEEN p_start_date
AND p_end_date
AND paf.assignment_id = pee.assignment_id
AND pee.effective_start_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.person_id = p_person_id
AND pee.element_entry_id = pev1.element_entry_id
AND pee.effective_start_date BETWEEN pee.effective_start_date
AND pee.effective_end_date
AND pev1.input_value_id = piv1.input_value_id
AND pee.effective_start_date BETWEEN pev1.effective_start_date
AND pev1.effective_end_date
AND piv1.name = 'Jurisdiction'
AND pee.effective_start_date BETWEEN piv1.effective_start_date
AND piv1.effective_end_date
AND pee.element_entry_id = pev2.element_entry_id
AND pee.effective_start_date BETWEEN pee.effective_start_date
AND pee.effective_end_date
AND pev2.input_value_id = piv2.input_value_id
AND piv2.name = 'Hours'
AND pee.effective_start_date BETWEEN piv2.effective_start_date
AND piv2.effective_end_date
AND SUBSTR(pev1.screen_entry_value,1,p_jd_level)
= SUBSTR(p_jurisdiction_code,1,p_jd_level);
select effective_date,
date_earned
from pay_payroll_actions
where payroll_action_id = p_payroll_action_id;
SELECT NVL(sui_er_wage_limit,0)
FROM pay_us_state_tax_info_f
WHERE state_code = SUBSTR(p_jurisdiction_code,1,2)
AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
SELECT NVL(sui_ee_wage_limit,0)
FROM pay_us_state_tax_info_f
WHERE state_code = SUBSTR(p_jurisdiction_code,1,2)
AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
SELECT NVL(sta_information18,' ')
FROM pay_us_state_tax_info_f
WHERE state_code = SUBSTR(p_jurisdiction_code,1,2)
AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
SELECT puc.state_code || '-' || puc.county_code || '-' ||pucty.city_code
FROM pay_us_counties puc,
pay_us_states pus,
pay_us_city_names pucty,
per_addresses pa,
per_assignments_f paf
WHERE paf.assignment_id = p_assignment_id
AND paf.person_id = pa.person_id
AND p_date_earned BETWEEN paf.effective_start_date AND paf.effective_end_date
AND paf.primary_flag = 'Y'
AND paf.assignment_type = 'E'
AND pa.primary_flag = 'Y'
AND pa.country = 'US'
AND pa.style = 'US'
AND TO_DATE('01-01-'||TO_CHAR(p_date_earned,'YYYY'), 'DD-MM-YYYY') BETWEEN
pa.date_from AND NVL(pa.date_to,to_date('31-12-4712','DD-MM-YYYY'))
AND pus.state_abbrev = pa.add_information17 --override state
AND puc.state_code = pus.state_code
AND puc.county_name = pa.add_information19 --Override County
AND pucty.state_code = pus.state_code
AND pucty.county_code = puc.county_code
AND pucty.city_name = pa.add_information18; -- Override City.
SELECT puc.state_code || '-' || puc.county_code || '-' ||pucty.city_code
FROM pay_us_counties puc,
pay_us_states pus,
pay_us_city_names pucty,
per_addresses pa,
per_assignments_f paf
WHERE paf.assignment_id = p_assignment_id
AND paf.person_id = pa.person_id
AND p_date_earned BETWEEN paf.effective_start_date AND paf.effective_end_date
AND paf.primary_flag = 'Y'
AND paf.assignment_type = 'E'
AND pa.primary_flag = 'Y'
AND pa.country = 'US'
AND pa.style = 'US'
AND TO_DATE('01-01-'||TO_CHAR(p_date_earned,'YYYY'), 'DD-MM-YYYY') BETWEEN
pa.date_from AND NVL(pa.date_to,to_date('31-12-4712','DD-MM-YYYY'))
AND pus.state_abbrev = pa.region_2 --Regular state
AND puc.state_code = pus.state_code
AND puc.county_name = pa.region_1 --Regular County
AND pucty.state_code = pus.state_code
AND pucty.county_code = puc.county_code
AND pucty.city_name = pa.town_or_city; -- Regular City.
SELECT puc.state_code || '-' || puc.county_code || '-' ||pucty.city_code
FROM pay_us_counties puc,
pay_us_states pus,
pay_us_city_names pucty,
hr_locations hl,
per_assignments_f paf
WHERE paf.assignment_id = p_assignment_id
AND TO_DATE('01-01-'||TO_CHAR(p_date_earned,'YYYY'), 'DD-MM-YYYY') BETWEEN
paf.effective_start_date AND paf.effective_end_date
AND paf.location_id = hl.location_id
AND hl.loc_information17 = pus.state_abbrev --override state
AND puc.state_code = pus.state_code
AND hl.loc_information19 = puc.county_name --override county
AND puc.state_code = SUBSTR(p_jurisdiction_code, 1, 2)
AND puc.county_code = SUBSTR(p_jurisdiction_code, 4, 3)
AND hl.loc_information18 = pucty.city_name --override city
AND pucty.state_code = SUBSTR(p_jurisdiction_code, 1, 2)
AND pucty.county_code = SUBSTR(p_jurisdiction_code, 4, 3)
AND pucty.city_code = SUBSTR(p_jurisdiction_code, 8, 4);
SELECT puc.state_code || '-' || puc.county_code || '-' ||pucty.city_code
FROM pay_us_counties puc,
pay_us_states pus,
pay_us_city_names pucty,
hr_locations hl,
per_assignments_f paf
WHERE paf.assignment_id = p_assignment_id
AND TO_DATE('01-01-'||TO_CHAR(p_date_earned,'YYYY'), 'DD-MM-YYYY') BETWEEN
paf.effective_start_date AND paf.effective_end_date
AND paf.location_id = hl.location_id
AND hl.region_2 = pus.state_abbrev --reg state
AND puc.state_code = pus.state_code
AND hl.region_1 = puc.county_name --reg county
AND puc.state_code = SUBSTR(p_jurisdiction_code, 1, 2)
AND puc.county_code = SUBSTR(p_jurisdiction_code, 4, 3)
AND hl.town_or_city = pucty.city_name --reg city
AND pucty.state_code = SUBSTR(p_jurisdiction_code, 1, 2)
AND pucty.county_code = SUBSTR(p_jurisdiction_code, 4, 3)
AND pucty.city_code = SUBSTR(p_jurisdiction_code, 8, 4);