[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
1849359 Added to_number in select from
pay_action_information to
work around bug 1822467
22-APR-2001 ahanda 115.9 Changed range code to error out
if FEIN is not 9 chars.
17-APR-2001 ahanda 115.8 Getting value if SS EE Withhled
from action_information8 instead
of action_information9.
15-APR-2001 ahanda 115.7 Changed apps.package name to
package name.
13-APR-2001 ahanda 115.6 Modified functions
- get_tax_exists
to return N for FUTA EE
- get_fls_tax_type_values
to return formated
+ve and - ve values.
27-MAR-2001 ahanda 115.5 Modified functions
- get_tax_exists
- get_fls_agency_code
- get_fls_tax_type_values
Changed the above function as
agency code is now dependent
on Tax Types.
Also fixed bug 1680396.
12-MAR-2001 asasthan 115.4 Modified functions:
- get_fls_agency_code
- get_fls_tax_type_values.
02-MAR-2001 asasthan 115.3 Changed the function to get
the agency code from
sta_information9 of
'State tax limit rate info'
record.
22-FEB-2001 ahanda 115.3 Changes get_fls_tax_type_values
20-FEB-2001 ahanda 115.2 Removed comment in range
19-FEB-2001 ahanda 115.1 Removed comment in range
and action creation.
28-JAN-2001 ahanda 115.0 Created.
*******************************************************************/
/******************************************************************
** Package Local Variables
******************************************************************/
gv_package varchar2(50) := 'pay_us_fls_reporting_pkg';
select ppa.start_date
,ppa.effective_date
,ppa.business_group_id
,ppa.report_qualifier
,ppa.report_type
,ppa.report_category
,ppa.legislative_parameters
from pay_payroll_actions ppa
where payroll_action_id = cp_payroll_action_id;
select replace( replace(replace(hoi.org_information1,'-'),'/'),' ')
from hr_organization_information hoi
where hoi.organization_id = cp_tax_unit_id
and hoi.org_information_context = 'Employer Identification';
select hou.name
from hr_all_organization_units hou
where hou.organization_id = cp_tax_unit_id;
insert into pay_message_lines
(line_sequence, payroll_id, message_level,
source_id, source_type, line_text) values
(pay_message_lines_s.nextval, NULL, 'F',
p_payroll_action_id, 'P',
'FEIN is not 9 charcters for GRE: ' || lv_gre_name);
select organization_id,
replace( replace(replace(hoi.org_information1,'-'),'/'),' ')
from hr_organization_information hoi
where org_information_context = 'Employer Identification'
and exists (select 'x'
from pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.payroll_action_id = paa.payroll_action_id
and ppa.business_group_id = cp_business_group_id
and ppa.effective_date between cp_start_date
and cp_end_date
and ppa.action_type = 'X'
and ppa.report_type = 'XFR_INTERFACE'
and ppa.action_status = 'C'
and paa.action_status = 'C'
and paa.tax_unit_id = hoi.organization_id
);
'select distinct paf.person_id
from pay_payroll_actions ppa,
pay_assignment_actions paa,
per_assignments_f paf
where ppa.business_group_id = ' || ln_business_group_id || '
and ppa.effective_date
between to_date(''' || to_char(ld_start_date, 'dd/mm/yyyy')
|| ''',''dd/mm/yyyy'')
and to_date(''' || to_char(ld_end_date, 'dd/mm/yyyy')
|| ''',''dd/mm/yyyy'')
and ppa.action_type = ''X''
and ppa.report_type = ''XFR_INTERFACE''
and ppa.action_status =''C''
and ppa.payroll_action_id = paa.payroll_action_id
and paa.action_status = ''C''
and paa.tax_unit_id = nvl('''|| ln_tax_unit_id ||
''', paa.tax_unit_id)
and paf.assignment_id = paa.assignment_id
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date
and not exists
(select /*+ ORDERED */
''x''
from pay_action_interlocks pai,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
where pai.locked_action_id = paa.assignment_action_id
and paa1.assignment_action_id = pai.locking_action_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.action_type =''X''
and ppa1.report_type = ''FLS''
and ppa1.report_qualifier = ''PERIODIC''
and ppa1.report_category = ''RT'')
and :payroll_action_id is not null
and rtrim(pay_mag_utils.get_parameter(
''TRANSFER_PAYROLL_ID''
,''TRANSFER_CONSOLIDATION_SET_ID''
,ppa.legislative_parameters)) =
nvl('''||ln_payroll_id
||''', rtrim(pay_mag_utils.get_parameter(
''TRANSFER_PAYROLL_ID''
,''TRANSFER_CONSOLIDATION_SET_ID''
,ppa.legislative_parameters)))
and rtrim(pay_mag_utils.get_parameter(
''TRANSFER_CONSOLIDATION_SET_ID''
,null
,ppa.legislative_parameters)) =
nvl('''||ln_consolidation_set_id
||''', rtrim(pay_mag_utils.get_parameter(
''TRANSFER_CONSOLIDATION_SET_ID''
,null
,ppa.legislative_parameters)))
order by paf.person_id';
select paa.assignment_id,
ppa.effective_date,
paa.tax_unit_id,
paa.assignment_action_id
from pay_payroll_actions ppa,
pay_assignment_actions paa,
per_assignments_f paf
where ppa.business_group_id = cp_business_group_id
and ppa.effective_date between cp_start_date
and cp_end_date
and ppa.action_type = 'X'
and ppa.report_type = 'XFR_INTERFACE'
and ppa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and paa.action_status = 'C'
and paa.tax_unit_id = nvl(to_char(cp_tax_unit_id), paa.tax_unit_id)
and paf.assignment_id = paa.assignment_id
and paf.person_id between cp_start_person_id
and cp_end_person_id
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date
and not exists
(select /*+ ORDERED */
'x'
from pay_action_interlocks pai,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
where pai.locked_action_id = paa.assignment_action_id
and paa1.assignment_action_id = pai.locking_action_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.action_type = 'X'
and ppa1.report_type = 'FLS'
and ppa1.report_qualifier = 'PERIODIC'
and ppa1.report_category = 'RT')
and rtrim(pay_mag_utils.get_parameter(
'TRANSFER_PAYROLL_ID'
,'TRANSFER_CONSOLIDATION_SET_ID'
,ppa.legislative_parameters)) =
nvl(to_char(cp_payroll_id),
rtrim(pay_mag_utils.get_parameter(
'TRANSFER_PAYROLL_ID'
,'TRANSFER_CONSOLIDATION_SET_ID'
,ppa.legislative_parameters)))
and rtrim(pay_mag_utils.get_parameter(
'TRANSFER_CONSOLIDATION_SET_ID'
,null
,ppa.legislative_parameters)) =
nvl(to_char(cp_consolidation_set_id),
rtrim(pay_mag_utils.get_parameter(
'TRANSFER_CONSOLIDATION_SET_ID'
,null
,ppa.legislative_parameters))) ;
select pay_assignment_actions_s.nextval
into ln_locking_action_id
from dual;
update pay_assignment_actions paa
set paa.serial_number = ln_assignment_action_id
where paa.assignment_action_id = ln_locking_action_id;
select pust.sit_exists,
decode(pust.sdi_ee_wage_limit, null, 'N', 'Y'),
decode(pust.sdi_er_wage_limit, null, 'N', 'Y'),
decode(pust.sui_ee_wage_limit, null, 'N', 'Y'),
decode(pust.sui_er_wage_limit, null, 'N', 'Y')
from pay_us_state_tax_info_f pust
where cp_effective_date between pust.effective_start_date
and pust.effective_end_date
and pust.state_code = substr(cp_jurisdiction_code, 1,2)
and pust.sta_information_category = 'State tax limit rate info';
select puct.county_tax, puct.head_tax, puct.school_tax
from pay_us_county_tax_info_f puct
where cp_effective_date between puct.effective_start_date
and puct.effective_end_date
and puct.jurisdiction_code = cp_jurisdiction_code;
select city_tax, head_tax, school_tax
from pay_us_city_tax_info_f
where cp_effective_date between effective_start_date
and effective_end_date
and jurisdiction_code = cp_jurisdiction_code;
select puft.fed_information1,
nvl(puft.fed_information2, puft.fed_information1)
from pay_us_federal_tax_info_f puft
where cp_effective_date between puft.effective_start_date
and puft.effective_end_date
and puft.fed_information_category = 'FLS Interface Mapping';
select pust.sta_information9
from pay_us_state_tax_info_f pust
where cp_effective_date between pust.effective_start_date
and pust.effective_end_date
and pust.state_code = substr(cp_jurisdiction_code, 1,2)
and pust.sta_information_category = 'State tax limit rate info';
select puct.cnty_attribute1,
nvl(puct.cnty_attribute2, puct.cnty_attribute1)
from pay_us_county_tax_info_f puct
where cp_effective_date between puct.effective_start_date
and puct.effective_end_date
and puct.jurisdiction_code = cp_jurisdiction_code
and (puct.cnty_attribute1 is not null or
puct.cnty_attribute2 is not null);
select city_attribute1,
nvl(city_attribute2, city_attribute1)
from pay_us_city_tax_info_f
where cp_effective_date between effective_start_date
and effective_end_date
and jurisdiction_code = cp_jurisdiction_code
and (city_attribute1 is not null or
city_attribute2 is not null);
select pusd.sch_information1
from pay_us_school_dsts_tax_info_f pusd
where cp_effective_date between pusd.effective_start_date
and pusd.effective_end_date
and pusd.state_code = substr(cp_jurisdiction_code, 1, 2)
and pusd.school_dsts_code = substr(cp_jurisdiction_code, 4)
and pusd.jurisdiction_code = cp_resident_jurisdiction
and pusd.sch_information_category = 'FLS Interface Mapping'
and pusd.sch_information1 is not null;
select jurisdiction_code
,nvl(sum(to_number(action_information1)),0) action_information1
,nvl(sum(to_number(action_information2)),0) action_information2
,nvl(sum(to_number(action_information3)),0) action_information3
,nvl(sum(to_number(action_information4)),0) action_information4
,nvl(sum(to_number(action_information5)),0) action_information5
,nvl(sum(to_number(action_information6)),0) action_information6
,nvl(sum(to_number(action_information7)),0) action_information7
,nvl(sum(to_number(action_information8)),0) action_information8
,nvl(sum(to_number(action_information9)),0) action_information9
,nvl(sum(to_number(action_information10)),0) action_information10
,nvl(sum(to_number(action_information11)),0) action_information11
,nvl(sum(to_number(action_information12)),0) action_information12
,nvl(sum(to_number(action_information13)),0) action_information13
,nvl(sum(to_number(action_information14)),0) action_information14
,nvl(sum(to_number(action_information15)),0) action_information15
,nvl(sum(to_number(action_information16)),0) action_information16
,nvl(sum(to_number(action_information17)),0) action_information17
,nvl(sum(to_number(action_information18)),0) action_information18
,nvl(sum(to_number(action_information19)),0) action_information19
,nvl(sum(to_number(action_information20)),0) action_information20
,nvl(sum(to_number(action_information21)),0) action_information21
,nvl(sum(to_number(action_information22)),0) action_information22
,nvl(sum(to_number(action_information23)),0) action_information23
,nvl(sum(to_number(action_information24)),0) action_information24
,nvl(sum(to_number(action_information25)),0) action_information25
,nvl(sum(to_number(action_information26)),0) action_information26
,nvl(sum(to_number(action_information27)),0) action_information27
,nvl(sum(to_number(action_information28)),0) action_information28
,nvl(sum(to_number(action_information29)),0) action_information29
,action_information30
from pay_action_information pai,
pay_assignment_actions paa,
pay_payroll_actions ppa -- Bug 3343962
where pai.tax_unit_id = cp_tax_unit_id
and paa.payroll_action_id = cp_payroll_action_id
and ppa.payroll_action_id = cp_payroll_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and pai.action_context_id = paa.serial_number
and pai.action_context_type = 'AAP'
and pai.action_information_category in ('US FEDERAL',
'US STATE',
'US COUNTY',
'US CITY',
'US SCHOOL DISTRICT'
)
group by jurisdiction_code,
action_information30;
pay_us_fls_reporting_pkg.ltr_action_info.delete;
select ltrim(rtrim(to_char(ln_gross_amt, decode(sign(ln_gross_amt),
-1, '0000000000.00',
'00000000000.00')))) ||
ltrim(rtrim(to_char(ln_subject_amt, decode(sign(ln_subject_amt),
-1, '0000000000.00',
'00000000000.00')))) ||
ltrim(rtrim(to_char(ln_taxable_amt, decode(sign(ln_taxable_amt),
-1, '0000000000.00',
'00000000000.00')))) ||
ltrim(rtrim(to_char(ln_r_ee_tax_amt, decode(sign(ln_r_ee_tax_amt),
-1, '0000000000.00',
'00000000000.00')))) ||
ltrim(rtrim(to_char(ln_r_er_tax_amt, decode(sign(ln_r_er_tax_amt),
-1, '0000000000.00',
'00000000000.00')))) ||
ltrim(rtrim(to_char(ln_nr_ee_tax_amt, decode(sign(ln_nr_ee_tax_amt),
-1, '0000000000.00',
'00000000000.00'))))
into lv_return from dual;