[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_error(errbuf OUT nocopy VARCHAR2,
retcode OUT nocopy NUMBER,
p_payroll_action_id IN NUMBER,
p_qtrname IN VARCHAR2) is
cursor c_assignments (cp_payroll_action_id in number) is
SELECT hou.name,
full_name,
paa.assignment_action_id,
paa.assignment_id,
paa.tax_unit_id,
state_code,
paf.assignment_number,
hou1.name,
pus.state_abbrev,
ppa.report_category
FROM pay_us_states pus,
hr_organization_units hou,
hr_organization_units hou1,
per_people_f ppf,
per_assignments_f paf,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE ppa.payroll_action_id = p_payroll_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_id = paf.assignment_id
/* Added for bug 2506588 */
AND paf.effective_start_date =
(select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.effective_start_date <= ppa.effective_date)
AND paf.effective_end_date >= ppa.start_date
AND paf.assignment_type = 'E'
/* Commented for bug 2506588
AND ppa.effective_date between paf.effective_start_date
and paf.effective_end_date*/
AND paf.person_id = ppf.person_id
AND ppa.effective_date between ppf.effective_start_date
and ppf.effective_end_date
AND paa.tax_unit_id = hou.organization_id
AND report_qualifier = pus.state_abbrev
and paf.organization_id = hou1.organization_id;
select fdi.user_name,fai.value
from ff_database_items fdi,
ff_archive_items fai,
ff_archive_item_contexts fac,
ff_archive_item_contexts fac1,
ff_contexts fc,
ff_contexts fc1
where fai.user_entity_id = fdi.user_entity_id
and fai.context1 = to_char(cp_asg_act_id)
/* context assignment action id */
and fac.archive_item_id = fai.archive_item_id
and fc.context_name = 'TAX_UNIT_ID'
and fc.context_id = fac.context_id
and ltrim(rtrim(fac.context)) = to_char(cp_tax_unit_id)
/* 2nd context of tax_unit_id */
and fac1.archive_item_id = fai.archive_item_id
and fc1.context_name = 'JURISDICTION_CODE'
and fc1.context_id = fac1.context_id
and substr(fac1.context,1,2) = substr(cp_state_code,1,2) ;
select fdi.user_name,fai.value
from ff_database_items fdi,
ff_archive_items fai,
ff_archive_item_contexts fac,
ff_contexts fc
where fai.user_entity_id = fdi.user_entity_id
and fai.context1 = to_char(cp_asg_act_id)
/* context assignment action id */
and fc.context_name = 'TAX_UNIT_ID'
and fc.context_id = fac.context_id
and fac.archive_item_id = fai.archive_item_id
and ltrim(rtrim(fac.context)) = to_char(cp_tax_unit_id);
select report_qualifier,business_group_id
from pay_payroll_actions
where payroll_action_id = cp_payroll_action_id;
select fdi.user_name,fai.value
from ff_database_items fdi,
ff_archive_items fai,
ff_archive_item_contexts fac,
ff_contexts fc
where fai.user_entity_id = fdi.user_entity_id
and fai.context1 = to_char(p_asg_action_id)
/* context assignment action id */
and fc.context_name = 'ASSIGNMENT_ID'
and fc.context_id = fac.context_id
and fac.archive_item_id = fai.archive_item_id
and ltrim(rtrim(fac.context)) = to_char(p_asg_id)
and user_name in ('A_SCL_ASG_US_WORK_SCHEDULE','A_ASG_HOURS','A_ASG_FREQ');
select org_information14
from hr_organization_information hoi
where org_information_context like 'SQWL GN Transmitter Rules%'
and hoi.organization_id = p_tax_unit_id;
insert into pay_us_rpt_totals (gre_name,
organization_name,
attribute2,
attribute3,
attribute30)
values(p_qtrname,lv_state_abbrev,
'No assignment actions have been created by the State quarterly Wage listing process.State Tax Rules may not have been defined properly','For California there could a setup issue related with Single/Multiple Wage plan',
'STATE_QUARTERLY_ERROR_REPORT');
insert into pay_us_rpt_totals (gre_name,
organization_name,
attribute2,
attribute30)
values(p_qtrname,lv_state_abbrev,
'No assignment actions have been created by the State quarterly Wage listing process.State Tax Rules may not have been defined properly',
'STATE_QUARTERLY_ERROR_REPORT');
/* This query selects the value for the Flag Hours Worked Calculation Method */
OPEN c_get_hour_calc_method(ln_tax_unit_id);
hr_utility.trace('Inserted header in pay_us_rpt_totals');
hr_utility.trace('Inserted header in pay_us_rpt_totals');