[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
prior to inserting to asg_reportings
in proc GET_PSD_JD_CODE.
05-JAN-12 emunisek 115.124 12618403 Added new function get_ht_withheld_per_jd_month.This will be
using same route text as the DBI HEAD_TAX_WITHHELD_PER_JD_MONTH
but will check for Jurisdiction Code without county code to give
City level value.
05-JAN-12 emunisek 115.126 12618403 Resolved GSCC Errors
*/
FUNCTION get_tax_jurisdiction(p_assignment_id number
,p_date_earned date
)
RETURN varchar2
IS
l_return_value varchar2(1);
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,
ptp.regular_payment_date, /* Bug#8592027 */
ptp.payroll_id, /*7520832*/
paaf.person_id /* 765549 */
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_periods_of_service
where person_id = l_person_id
and l_date between date_start and nvl(actual_termination_date,to_date('12/31/4712','MM/DD/YYYY'));
select max(nvl(regular_payment_date,end_date))
from per_time_periods
where payroll_id = l_payroll_id
and nvl(regular_payment_date,end_date) <= l_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);
only one city is selected to collect head tax.
4)For a payroll with duration less than a month, use of Date Paid in different
month will cause Head Tax to be skipped in some cases.A warning message
will be thrown in the Concurrent Request Log file for such assignments
with Assignment ID,Assignment Number,Jurisdiction code to help customer to
do Balance adjustment for skipped assignments.
*/
FUNCTION coloradocity_ht_collectornot(
p_assignment_id NUMBER,
p_date_earned DATE,
p_payroll_action_id NUMBER,
p_jurisdiction_code VARCHAR2,
p_prim_jurisdiction_code VARCHAR2,
p_monthly_gross NUMBER )
RETURN NUMBER
IS
l_max_jd_code VARCHAR2(11); --Jurisdiction for City with maximum percentage
select ptp.start_date,
ptp.end_date,
ptp.payroll_id
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 max(end_date)
from per_time_periods
where payroll_id = l_payroll_id
and end_date <= l_date;
select jurisdiction_code,
round(sum(percentage*days)/(last_day(p_date_earned)-trunc(p_date_earned,'MONTH')+1),2) percentage
from
( select pev1.element_entry_id,
pev1.screen_entry_value Jurisdiction_code,
pev2.screen_entry_value Percentage,
least(last_day(p_date_earned),pee.effective_end_date)
-greatest(pee.effective_start_date,trunc(p_date_earned,'MONTH') )+1 days
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 = p_assignment_id
and pee.effective_start_date<=last_day(p_date_earned)
and pee.effective_end_date>=trunc(p_date_earned,'MONTH')
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'
and pev1.screen_entry_value in ('06-001-0030','06-005-0030',
'06-035-0030','06-005-0870',
'06-031-0140','06-005-0450',
'06-005-0850'))
group by jurisdiction_code order by percentage desc,jurisdiction_code;
select effective_date
from pay_payroll_actions
where payroll_action_id = p_payroll_action_id;
SELECT number_per_fiscal_year
FROM per_time_period_types pttt,
pay_all_payrolls_f papf
WHERE pttt.period_type=papf.period_type
AND papf.payroll_id=p_payroll_id;
SELECT assignment_number
FROM per_all_assignments_f
WHERE assignment_id=p_assignment_id;
SELECT decode(count(regular_payment_date),0,1,count(regular_payment_date))
FROM pay_payroll_actions paction,
per_time_periods target
WHERE paction.payroll_action_id = p_payroll_action_id
AND target.payroll_id = paction.payroll_id
AND to_char(target.regular_payment_date,'YYYY')=to_char(paction.effective_date,'YYYY')
AND target.regular_payment_date >= p_logical_hire_date;
SELECT school_district_code
INTO l_res_city_school_district
FROM PAY_US_EMP_CITY_TAX_RULES_F
WHERE assignment_id = p_assignment_id
AND jurisdiction_code = p_per_addr_geocode
AND l_date_paid between effective_start_date and effective_end_date;
SELECT sch_information_category
INTO l_res_psd_code
FROM pay_us_school_dsts_tax_info_f
WHERE jurisdiction_code = p_per_addr_geocode
AND school_dsts_code = l_res_city_school_district
AND l_date_paid between effective_start_date and effective_end_date
AND SCH_INFORMATION1 = 'PSDCODES';
SELECT sch_information_category
INTO l_work_psd_code
FROM pay_us_school_dsts_tax_info_f
WHERE jurisdiction_code = p_loc_addr_geocode
AND l_date_paid between effective_start_date and effective_end_date
AND SCH_INFORMATION1 = 'PSDCODES';
SELECT school_district_code
INTO l_work_city_school_district
FROM PAY_US_EMP_CITY_TAX_RULES_F
WHERE assignment_id = p_assignment_id
AND jurisdiction_code = p_loc_addr_geocode
AND l_date_paid between effective_start_date and effective_end_date;
SELECT sch_information_category
INTO l_work_psd_code
FROM pay_us_school_dsts_tax_info_f
WHERE jurisdiction_code = p_loc_addr_geocode
AND school_dsts_code = l_work_city_school_district
AND l_date_paid between effective_start_date and effective_end_date
AND SCH_INFORMATION1 = 'PSDCODES';
SELECT 'Y'
INTO l_asg_rep_row_exists
FROM PAY_US_ASG_REPORTING
WHERE assignment_id = p_assignment_id
AND JURISDICTION_CODE = l_psd_jurisdiction_code
AND TAX_UNIT_ID = p_tax_unit_id;
hr_utility.trace('GET_PSD_JD_CODE: PSD ROW ' || l_psd_jurisdiction_code || ' not found in ASG_REPORTING.. INSERTING');
INSERT INTO PAY_US_ASG_REPORTING
( ASSIGNMENT_ID
,JURISDICTION_CODE
,TAX_UNIT_ID)
VALUES
( p_assignment_id
,l_psd_jurisdiction_code
,p_tax_unit_id);
SELECT balance_type_id
FROM pay_balance_types
WHERE balance_name = 'Head Tax Withheld'
AND legislation_code = 'US';
SELECT /*+ RULE*/ nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale),0)
FROM pay_run_result_values TARGET
,pay_balance_types BAL
,pay_balance_feeds_f FEED
,pay_run_results RR
,pay_assignment_actions ASSACT
,pay_assignment_actions BAL_ASSACT
,pay_payroll_actions PACT
,pay_payroll_actions BACT
,per_all_assignments_f ASS
WHERE BAL_ASSACT.assignment_action_id = p_asgmnt_action_id
AND BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
AND FEED.balance_type_id = p_balance_type_id + decode(TARGET.INPUT_VALUE_ID, null, 0, 0)
AND FEED.input_value_id = TARGET.input_value_id
AND nvl(TARGET.result_value,'0') <> '0'
AND TARGET.run_result_id = RR.run_result_id
AND RR.assignment_action_id = ASSACT.assignment_action_id
AND ASSACT.payroll_action_id = PACT.payroll_action_id
AND PACT.effective_date between FEED.effective_start_date
and FEED.effective_end_date
AND RR.status in ('P','PA')
AND ASSACT.action_sequence <= BAL_ASSACT.action_sequence
AND ASSACT.assignment_id = ASS.assignment_id
AND ASS.person_id = (select person_id from per_all_assignments_f START_ASS
where START_ASS.assignment_id = BAL_ASSACT.assignment_id
and rownum = 1)
AND PACT.effective_date between ASS.effective_start_date
and ASS.effective_end_date
/* Month To Date */
AND trunc(PACT.effective_date,'MM')
= trunc(BACT.effective_date,'MM')
/* Within Jurisdiction */
and BAL.balance_type_id = p_balance_type_id
and substr (SUBSTR(RR.jurisdiction_code,1,3)||'000'||SUBSTR(RR.jurisdiction_code,7), 1, BAL.jurisdiction_level) =
substr (SUBSTR(p_jurisdiction_code,1,3)||'000'||SUBSTR(p_jurisdiction_code,7), 1, BAL.jurisdiction_level);