[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT hoi.org_information3
INTO l_state_ein
FROM hr_organization_information hoi,
pay_us_states pus
WHERE pus.state_code = pv_state_id
and hoi.organization_id = pv_tax_unit_id
and hoi.org_information_context = 'State Tax Rules'
and hoi.org_information1 = pus.state_abbrev;
** for the Selection parameter entered by the user in the SRS.
** the action_context_id returned by this cursor is used to
** retreive the jurisdiction specific level tax information.
************************************************************/
cursor c_employee (
cp_beginning_date in date
,cp_end_date in date
,cp_payroll_id in number
,cp_consolidation_set_id in number
,cp_organization_id in number
,cp_tax_unit_id in number
,cp_location_id in number
,cp_business_group_id in number
) is
SELECT action_number, last_name, first_name, middle_names,
employee_number,
assignment_number,
assignment_id,
national_identifier,
address_line, town_or_city, county, state,
postal_code,country,
tax_unit_id, gre_name, fed_ein, org_name, location_code,
action_type, person_id, effective_date
FROM pay_us_employee_action_info_v peav
WHERE peav.effective_date between cp_beginning_date and cp_end_date
and nvl(cp_business_group_id,peav.business_group_id)
= peav.business_group_id
and nvl(cp_location_id,peav.location_id) = peav.location_id
and nvl(cp_organization_id, peav.organization_id)
= peav.organization_id
and nvl(cp_payroll_id, peav.payroll_id) = peav.payroll_id
and nvl(cp_tax_unit_id, peav.tax_unit_id) = peav.tax_unit_id
and nvl(cp_consolidation_set_id, peav.consolidation_set_id)
= peav.consolidation_set_id
order by person_id, effective_date asc;
SELECT person_id, last_name, action_number
FROM pay_us_employee_action_info_v peav
WHERE peav.effective_date between cp_beginning_date and cp_end_date
and nvl(cp_business_group_id,peav.business_group_id)
= peav.business_group_id
and nvl(cp_location_id,peav.location_id) = peav.location_id
and nvl(cp_organization_id, peav.organization_id)
= peav.organization_id
and nvl(cp_payroll_id, peav.payroll_id) = peav.payroll_id
and nvl(cp_tax_unit_id, peav.tax_unit_id) = peav.tax_unit_id
and nvl(cp_consolidation_set_id, peav.consolidation_set_id)
= peav.consolidation_set_id;
** This cursor returns Federal Level Balances for the selected **
** assignments from the archiver. **
****************************************************************/
CURSOR c_federal_balances(cp_action_number in number) is
select action_number, sum(fit_gross),
sum(fit_reduced_subject),
sum(fit_withheld),
sum(futa_taxable),
sum(futa_liability),
sum(ss_ee_taxable),
sum(ss_ee_withheld),
sum(ss_er_taxable),
sum(ss_er_liability),
sum(medicare_ee_taxable),
sum(medicare_ee_withheld),
sum(medicare_er_taxable),
sum(medicare_er_liability),
sum(eic_advance)
FROM pay_us_federal_action_info_v fed
WHERE fed.action_number = cp_action_number
/* and cp_action_number not in
(select fed2.action_number
from pay_us_federal_action_info_v fed2
where (fed2.fit_gross = 0
or fed2.fit_gross is null)
and (fed2.fit_withheld = 0
or fed2.fit_withheld is null)
and (fed2.fit_reduced_subject = 0
or fed2.fit_reduced_subject is null)
and (fed2.futa_liability = 0
or fed2.futa_liability is null)
and (fed2.futa_taxable = 0
or fed2.futa_taxable is null)
and (fed2.ss_ee_withheld = 0
or fed2.ss_ee_withheld is null)
and (fed2.ss_ee_taxable = 0
or fed2.ss_ee_taxable is null)
and (fed2.ss_er_liability = 0
or fed2.ss_er_liability is null)
and (fed2.ss_er_taxable = 0
or fed2.ss_er_taxable is null)
and (fed2.medicare_ee_withheld = 0
or fed2.medicare_ee_withheld is null)
and (fed2.medicare_ee_taxable = 0
or fed2.medicare_ee_taxable is null)
and (fed2.medicare_er_taxable = 0
or fed2.medicare_er_taxable is null)
and (fed2.medicare_er_liability = 0
or fed2.medicare_er_liability is null)
and (fed2.eic_advance = 0
or fed2.eic_advance is null)) */
GROUP BY action_number;
select /*+ index(state.pai pay_action_information_n2) */ jurisdiction_code,
jurisdiction_name,
sum(nvl(sit_gross,0)),
sum(nvl(sit_reduced_subject,0)),
sum(nvl(sit_withheld,0)),
sum(nvl(sui_ee_taxable,0)),
sum(nvl(sui_ee_withheld,0)),
sum(nvl(sui_er_taxable,0)),
sum(nvl(sui_er_liability,0)),
sum(nvl(sdi_ee_taxable,0)),
sum(nvl(sdi_ee_withheld,0)),
sum(nvl(sdi_er_taxable,0)),
sum(nvl(sdi_er_liability,0)),
sum(nvl(workers_comp_withheld,0)),
sum(nvl(workers_comp2_withheld,0))
from pay_us_state_action_info_v state
where state.action_number = cp_action_number
and state.jurisdiction_code like nvl(cp_state_id,'%')||'-000-0000'
group by jurisdiction_code, jurisdiction_name;
select jurisdiction_code,
jurisdiction_name,
sum(county_gross),
sum(county_reduced_subject),
sum(county_withheld),
sum(head_tax_withheld),
decode(non_resident_flag,'NR','WK','R','RS',non_resident_flag)
from pay_us_county_action_info_v county
where county.action_number = cp_action_number
and county.jurisdiction_code
like cp_state_id||'-'||nvl(cp_county_id,'%')||'-0000'
/* and cp_action_number not in (select county2.action_number
from pay_us_county_action_info_v county2
where (county2.county_gross = 0
or county2.county_gross is null)
and (county2.county_reduced_subject = 0
or county2.county_reduced_subject is null)
and (county2.county_withheld = 0
or county2.county_withheld is null)
and (county2.head_tax_withheld = 0
or county2.head_tax_withheld is null)
and county2.jurisdiction_code
= county.jurisdiction_code) */
GROUP BY jurisdiction_code, jurisdiction_name,
decode(non_resident_flag,'NR','WK','R','RS',non_resident_flag);
select /*+ index(city.pai pay_action_information_n2) */ jurisdiction_code,
jurisdiction_name,
sum(city_gross),
sum(city_reduced_subject),
sum(city_withheld),
sum(head_tax_withheld),
decode(non_resident_flag,'NR','WK','R','RS',non_resident_flag)
from pay_us_city_action_info_v city
where city.action_number = cp_action_number
and city.jurisdiction_code
like cp_state_id||'-'||nvl(cp_county_id,'%')||'-'||nvl(cp_city_id,'%')
/* and cp_action_number not in (select city2.action_number
from pay_us_city_action_info_v city2
where (city2.city_gross = 0
or city2.city_gross is null)
and (city2.city_reduced_subject = 0
or city2.city_reduced_subject is null)
and (city2.city_withheld = 0
or city2.city_withheld is null)
and (city2.head_tax_withheld = 0
or city2.head_tax_withheld is null)
and city2.jurisdiction_code
= city.jurisdiction_code) */
GROUP BY jurisdiction_code, jurisdiction_name,
decode(non_resident_flag,'NR','WK','R','RS',non_resident_flag);
select /*+ index(school.pai pay_action_information_n2) */ jurisdiction_code,
jurisdiction_name,
sum(school_gross),
sum(school_reduced_subject),
sum(School_Withheld)
from pay_us_school_action_info_v school
where school.action_number = cp_action_number
and school.jurisdiction_code like
cp_state_id||'-'||nvl(cp_school_id,'%')
/* and cp_action_number not in (select school2.action_number
from pay_us_school_action_info_v school2
where (school2.school_gross = 0
or school2. school_gross is null)
and (school2.school_reduced_subject = 0
or school2.school_reduced_subject is null)
and (school2.School_Withheld = 0
or school2.School_Withheld is null)
and school2.jurisdiction_code
= school.jurisdiction_code)*/
GROUP BY jurisdiction_code, jurisdiction_name;
federal_bal.delete;
state_bal.delete;
county_bal.delete;
city_bal.delete;
school_bal.delete;
update fnd_concurrent_requests
set output_file_type = 'HTML'
where request_id = FND_GLOBAL.CONC_REQUEST_ID ;