[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
query= select psif.sit_exists
into l_sit_exists...
This query was returning multiple rows and
the Multi Work Site report was failing.
12-OCT-2004 rmonge 115.16 3909329 Changed/Modified the cursor
'c_derive_wksite_estab due to performance problems.
12-OCT-2004 rmonge 115.17 No changes.
15-DEC-2004 rmonge 115.18 4047812 Modified c_derive_wksite_estab due to
performance problems.
14-MAR-2005 sackumar 115.19 4222032 Change in the Range Cursor removing redundant
use of bind Variable (:payroll_action_id)
18-aug-2005 sackumar 115.20 3613544 changed the c_get_sui_code cursor introduce use_nl hint.
18-aug-2006 schowta 115.21 5399921 added code fix to include the work at home employee count in load_rpt_totals
08-Aug-2011 nkjaladi 115.28 11936382 Modified procedure load_rpt_totals to handle the
following scenarios:
1. If SUI state change happens in middle of the quarer
then the employee would be reported in corresponding
location of the quarter instead of 'Invalid Location'.
2. For 'MA' resident employees SQWL archives these
employees even if the earnigs in resident state
is zero. These employees gets picked up by
the multi work site report and get reported
under 'Invalid Location'. Such employees shouldn't
be counted as they are getting reported under the
corresponding employee earnings state
3. If Location of the employee has override
payroll tax state then these MWSR is not
considering the override tax state. Modified
code to consider the override tax state of
the assignment location.
/******************************************************************
** Package Local Variables
******************************************************************/
gv_package varchar2(50) := 'pay_us_mwr_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 distinct paa.assignment_id
from pay_assignment_actions paa -- SQWL assignment action
, pay_payroll_actions ppa
where ppa.business_group_id = ' || ln_business_group_id || '
and ppa.effective_date between to_date(''' || to_char(ld_start_date, 'dd-mon-yyyy') || ''', ''dd-mon-yyyy'') --Bug 3362257
and to_date(''' || to_char(ld_end_date, 'dd-mon-yyyy') || ''', ''dd-mon-yyyy'') --Bug 3362257
and ppa.action_type = ''X''
and ppa.report_type = ''SQWL''
and ppa.action_status =''C''
and ppa.payroll_action_id = paa.payroll_action_id
and :payroll_action_id is not null
order by paa.assignment_id
';
select paa.assignment_id,
ppa.effective_date,
paa.tax_unit_id,
paa.assignment_action_id
from pay_assignment_actions paa -- SQWL assignment action
, pay_payroll_actions ppa
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 = 'SQWL'
and ppa.action_status ='C'
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id between cp_start_assignment_id
and cp_end_assignment_id;
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 paa.assignment_id
,paa.tax_unit_id
,paa.serial_number
,ppa.business_group_id
,ppa.effective_date
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE ppa.payroll_action_id = p_payroll_action_id
AND ppa.payroll_action_id = paa.payroll_action_id;
SELECT fai.value,
pus.state_code || '-000-0000',
pus.state_abbrev
FROM ff_archive_items fai
,ff_user_entities ue
,pay_us_states pus
,hr_locations hl
where hl.location_id = fai.value
and fai.user_entity_id = ue.user_entity_id
and ue.user_entity_name =
decode(p_mon_of_qtr,4,'A_SQWL_LOC_QTR_END','A_SQWL_LOC_MON_' || to_char(p_mon_of_qtr))
and nvl(hl.loc_information17,hl.region_2) = pus.state_abbrev --#11936382 added nvl(hl.loc_information17)
and fai.context1 = to_char(p_ass_act_id); -- context of assignment action id
SELECT /*+ use_nl (hoi1, hoi2)*/
hoi1.org_information2,
hoi2.org_information1
FROM pay_state_rules SR,
hr_organization_information hoi1,
hr_organization_information hoi2
WHERE hoi1.organization_id = p_tax_unit_id
AND hoi1.org_information_context = 'State Tax Rules'
AND hoi1.org_information1 = SR.state_code
AND SR.jurisdiction_code = substr(p_jurisdiction,1,2)||'-000-0000'
AND hoi2.organization_id = hoi1.organization_id
AND hoi2.org_information_context = 'Employer Identification' ;
SELECT fai.value
FROM ff_archive_item_contexts con3,
ff_archive_item_contexts con2,
ff_contexts fc3,
ff_contexts fc2,
ff_archive_items fai
WHERE fai.user_entity_id = p_user_entity_id
and fai.context1 = to_char(p_ass_act_id)
/* context assignment action id */
and fc2.context_name = 'TAX_UNIT_ID'
and con2.archive_item_id = fai.archive_item_id
and con2.context_id = fc2.context_id
and ltrim(rtrim(con2.context)) = to_char(p_tax_unit_id)
/* 2nd context of tax_unit_id */
and fc3.context_name = 'JURISDICTION_CODE'
and con3.archive_item_id = fai.archive_item_id
and con3.context_id = fc3.context_id
and substr(con3.context,1,2) = substr(p_jurisdiction_code,1,2)
/* 3rd context of state jurisdiction_code*/;
SELECT pghn2.entity_id
FROM per_gen_hierarchy pgh
,per_gen_hierarchy_versions pghv
,per_gen_hierarchy_nodes pghn2 -- establishment organizations
,pay_payroll_actions ppa
where ppa.payroll_action_id = p_payroll_action_id
and pgh.hierarchy_id = p_est_hierarchy_id
and pgh.business_group_id = ppa.business_group_id
and pgh.hierarchy_id = pghv.hierarchy_id
and pghv.HIERARCHY_VERSION_id = p_hierarchy_ver_id
and pghv.hierarchy_version_id = pghn2.hierarchy_version_id
and ( ( pghn2.node_type = 'EST'
and pghn2.entity_id = p_location_id
)
OR
( pghn2.node_type = 'EST'
AND p_location_id in
( SELECT pghn3.entity_id
FROM per_gen_hierarchy_nodes pghn3
WHERE pghn3.node_type = 'LOC'
AND pghn3.hierarchy_version_id = pghv.HIERARCHY_VERSION_id
AND pghn3.parent_hierarchy_node_id = pghn2.hierarchy_node_id
)
)
);
SELECT pghn2.entity_id
FROM per_gen_hierarchy_nodes pghn2 -- establishment organizations
where p_hierarchy_ver_id = pghn2.hierarchy_version_id
and ( ( pghn2.node_type = 'EST'
and pghn2.entity_id = p_location_id
)
OR
( pghn2.node_type = 'EST'
AND p_location_id in
( SELECT /*+ pghn3 PER_GEN_HIER_NOD_VER_N4 */ pghn3.entity_id
FROM per_gen_hierarchy_nodes pghn3
WHERE pghn3.node_type = 'LOC'
AND pghn3.hierarchy_version_id =
pghn2.hierarchy_version_id --p_hierarchy_ver_id
AND pghn3.parent_hierarchy_node_id =
pghn2.hierarchy_node_id
)
)
);
select fai.value,
ppa.report_qualifier
from ff_archive_items fai,
ff_user_entities ue,
pay_assignment_actions paa,
pay_payroll_actions ppa
where fai.context1 = cp_sqwl_assact
and paa.assignment_action_id = fai.context1
and fai.user_entity_id = ue.user_entity_id
and ue.user_entity_name = 'A_SQWL_MONTH' || to_char(cp_month_of_quarter) || '_COUNT'
and ppa.payroll_action_id = paa.payroll_action_id ;
SELECT fai.value
FROM ff_archive_item_contexts con3,
ff_archive_item_contexts con2,
ff_contexts fc3,
ff_contexts fc2,
ff_archive_items fai
WHERE fai.user_entity_id = cp_user_entity_id
and fai.context1 = to_char(cp_ass_act_id)
/* context assignment action id */
and fc2.context_name = 'TAX_UNIT_ID'
and con2.archive_item_id = fai.archive_item_id
and con2.context_id = fc2.context_id
and ltrim(rtrim(con2.context)) = to_char(cp_tax_unit_id)
/* 2nd context of tax_unit_id */
and fc3.context_name = 'JURISDICTION_CODE'
and con3.archive_item_id = fai.archive_item_id
and con3.context_id = fc3.context_id
and substr(con3.context,1,2) = substr(cp_jurisdiction_code,1,2)
/* 3rd context of state jurisdiction_code*/;
SELECT fue.user_entity_id
INTO l_user_entity_id
FROM ff_user_entities fue
WHERE fue.user_entity_name = 'A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_QTD'
AND fue.legislation_code = 'US';
SELECT COUNT(DISTINCT peft.sui_state_code)
FROM pay_us_emp_fed_tax_rules_f peft,
per_all_assignments_f paa,
hr_soft_coding_keyflex hscf
WHERE peft.effective_end_date >= trunc(p_effective_date,'Q')
AND peft.effective_start_date <= p_effective_date
AND peft.assignment_id = p_assignment_id
AND peft.business_group_id = p_business_group_id
AND peft.sui_state_code in (p_loc_state_code,p_sqwl_state_code)
AND paa.assignment_id = peft.assignment_id
AND paa.business_group_id = peft.business_group_id
AND paa.effective_end_date >= trunc(p_effective_date,'Q')
AND paa.effective_start_date <= p_effective_date
AND paa.business_group_id = peft.business_group_id
AND hscf.segment1 = p_tax_unit_id
AND hscf.soft_coding_keyflex_id = paa.soft_coding_keyflex_id;
SELECT paaf.location_id
FROM per_all_assignments_f paaf,
hr_locations_all hl,
pay_us_states pus
WHERE paaf.effective_start_date <= p_effective_date
AND paaf.effective_end_date >= trunc(p_effective_date,'Q')
AND paaf.assignment_id = p_assignment_id
AND paaf.business_group_id = p_business_group_id
AND paaf.location_id = hl.location_id
AND nvl(hl.loc_information17,hl.region_2) = pus.state_abbrev
AND pus.state_code = p_state_code
AND EXISTS
(
SELECT null
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE ppa.effective_date BETWEEN trunc(p_effective_date,'Q')
AND p_effective_date
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.tax_unit_id = p_tax_unit_id
AND paa.assignment_id = paaf.assignment_id
)
ORDER BY paaf.effective_end_date desc;
SELECT paaf.location_id
FROM per_all_assignments_f paaf,
hr_locations_all hl,
pay_us_states pus
WHERE paaf.effective_start_date <= p_effective_date
AND paaf.effective_end_date >= trunc(p_effective_date,'Q')
AND paaf.assignment_id = p_assignment_id
AND paaf.business_group_id = p_business_group_id
AND paaf.location_id = hl.location_id
AND nvl(hl.loc_information17,hl.region_2) = pus.state_abbrev
AND pus.state_code = p_state_code
ORDER BY paaf.effective_end_date desc;
SELECT userenv('sessionid')
INTO v_session_id
FROM dual;
SELECT ppa.legislative_parameters
INTO l_ppa_legislative_parameters
FROM pay_payroll_actions ppa
where ppa.payroll_action_id = p_payroll_action_id;
SELECT ue.user_entity_id
INTO l_user_entity_id
FROM ff_user_entities ue
WHERE ue.user_entity_name = 'A_SIT_GROSS_PER_JD_GRE_MON_' || to_char(i)
AND ue.legislation_code = 'US';
SELECT ue.user_entity_id
INTO l_user_entity_id
FROM ff_user_entities ue
WHERE ue.user_entity_name = 'A_SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD'
AND ue.legislation_code = 'US';
SELECT psr.jurisdiction_code
INTO l_sqwl_jurisdiction_code
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_state_rules psr
WHERE paa.assignment_action_id = l_sqwl_assact
AND ppa.payroll_action_id = paa.payroll_action_id
AND psr.state_code = ppa.report_qualifier;
SELECT report_qualifier
INTO l_state_abbrev
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_action_id = l_sqwl_assact;
SELECT hoi1.org_information2
INTO l_sui_id
FROM pay_state_rules SR,
hr_organization_information hoi1
WHERE hoi1.organization_id = l_tax_unit_id
AND hoi1.org_information_context = 'State Tax Rules'
AND hoi1.org_information1 = SR.state_code
AND SR.jurisdiction_code =
substr(l_sqwl_jurisdiction_code,1,2)||'-000-0000';
SELECT NVL(paf.work_at_home,'N')
INTO l_work_at_home
FROM per_all_assignments_f paf
WHERE paf.assignment_id = l_ass_id
AND l_effective_date between paf.effective_start_date and paf.effective_end_date;
Select psif.sit_exists
into l_sit_exists
from pay_us_state_tax_info_f psif,
pay_payroll_actions ppa
where ppa.payroll_action_id = p_payroll_action_id
and psif.state_code = substr(l_jurisdiction,1,2)
and ppa.effective_date
BETWEEN psif.effective_start_date AND psif.effective_end_date
and sta_information_category = 'State tax limit rate info';
SELECT state_code INTO l_month_count_code
FROM pay_us_states
WHERE state_abbrev = l_month_count_state_code;
SELECT psr.jurisdiction_code
INTO l_sqwl_jurisdiction_code
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_state_rules psr
WHERE paa.assignment_action_id = l_sqwl_assact
AND ppa.payroll_action_id = paa.payroll_action_id
AND psr.state_code = ppa.report_qualifier;
SELECT hoi1.org_information2
INTO l_sui_id
FROM pay_state_rules SR,
hr_organization_information hoi1
WHERE hoi1.organization_id = l_tax_unit_id
AND hoi1.org_information_context = 'State Tax Rules'
AND hoi1.org_information1 = SR.state_code
AND SR.jurisdiction_code = substr(l_sqwl_jurisdiction_code,1,2)||'-000-0000';
SELECT '1'
INTO l_ma_resides_true
FROM dual
WHERE EXISTS (
SELECT '1'
FROM per_assignments_f paf,
per_addresses pad
WHERE paf.assignment_id = l_ass_id
AND paf.person_id = pad.person_id
AND pad.date_from <= l_effective_date
AND NVL(pad.date_to ,l_effective_date) >= trunc(l_effective_date,'Q')
AND pad.region_2 = l_state_abbrev
AND pad.primary_flag = 'Y');
SELECT ue.user_entity_id
INTO l_user_entity_id
FROM ff_user_entities ue
WHERE ue.user_entity_name = 'A_SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD'
AND ue.legislation_code = 'US';
IF i = 1 THEN -- UPDATING / INSERTING into column value1
hr_utility.set_location(gv_package || '.' || l_procedure , 90);
UPDATE pay_us_rpt_totals prt
SET prt.value1 = NVL(prt.value1,0) + 1
WHERE prt.session_id = v_session_id
AND prt.organization_id = p_payroll_action_id
AND prt.location_id = l_worksite
AND prt.state_abbrev = l_state_abbrev
AND prt.attribute1 = 'MWS_EST'
and prt.attribute2 = l_sui_id
and prt.attribute3 = l_fed_ein;
IF SQL%ROWCOUNT = 0 THEN --- Row doesn't exist in table must insert.
hr_utility.set_location(gv_package || '.' || l_procedure , 100);
INSERT into pay_us_rpt_totals
( session_id
,organization_id
,location_id
,state_abbrev
,attribute1
,attribute2
,attribute3
,value1)
VALUES
( v_session_id
,p_payroll_action_id
,l_worksite
,l_state_abbrev
,'MWS_EST'
,l_sui_id
,l_fed_ein
,1);
ELSIF i = 2 THEN -- UPDATING / INSERTING into Column value2
hr_utility.set_location(gv_package || '.' || l_procedure , 110);
UPDATE pay_us_rpt_totals prt
SET prt.value2 = NVL(prt.value2,0) + 1
WHERE prt.session_id = v_session_id
AND prt.organization_id = p_payroll_action_id
AND prt.location_id = l_worksite
AND prt.state_abbrev = l_state_abbrev
AND prt.attribute1 = 'MWS_EST'
and prt.attribute2 = l_sui_id
and prt.attribute3 = l_fed_ein;
IF SQL%ROWCOUNT = 0 THEN --- Row doesn't exist in table must insert.
hr_utility.set_location(gv_package || '.' || l_procedure , 120);
INSERT into pay_us_rpt_totals
( session_id
,organization_id
,location_id
,state_abbrev
,attribute1
,attribute2
,attribute3
,value2)
VALUES
( v_session_id
,p_payroll_action_id
,l_worksite
,l_state_abbrev
,'MWS_EST'
,l_sui_id
,l_fed_ein
,1);
ELSIF i = 3 THEN -- UPDATING / INSERTING into column value3
hr_utility.set_location(gv_package || '.' || l_procedure , 130);
UPDATE pay_us_rpt_totals prt
SET prt.value3 = NVL(prt.value3,0) + 1
WHERE prt.session_id = v_session_id
AND prt.organization_id = p_payroll_action_id
AND prt.location_id = l_worksite
AND prt.state_abbrev = l_state_abbrev
AND prt.attribute1 = 'MWS_EST'
and prt.attribute2 = l_sui_id
and prt.attribute3 = l_fed_ein;
IF SQL%ROWCOUNT = 0 THEN --- Row doesn't exist in table must insert.
hr_utility.set_location(gv_package || '.' || l_procedure , 140);
INSERT into pay_us_rpt_totals
( session_id
,organization_id
,location_id
,state_abbrev
,attribute1
,attribute2
,attribute3
,value3)
VALUES
( v_session_id
,p_payroll_action_id
,l_worksite
,l_state_abbrev
,'MWS_EST'
,l_sui_id
,l_fed_ein
,1);
ELSE -- UPDATING / INSERTING into column value4
hr_utility.set_location(gv_package || '.' || l_procedure , 150);
UPDATE pay_us_rpt_totals prt
SET prt.value4 = NVL(prt.value4,0) + l_archive_value
WHERE prt.session_id = v_session_id
AND prt.organization_id = p_payroll_action_id
AND prt.location_id = l_worksite
AND prt.state_abbrev = l_state_abbrev
AND prt.attribute1 = 'MWS_EST'
and prt.attribute2 = l_sui_id
and prt.attribute3 = l_fed_ein;
IF SQL%ROWCOUNT = 0 THEN --- Row doesn't exist in table must insert.
hr_utility.set_location(gv_package || '.' || l_procedure , 160);
INSERT into pay_us_rpt_totals
( session_id
,organization_id
,location_id
,state_abbrev
,attribute1
,attribute2
,attribute3
,value4)
VALUES
( v_session_id
,p_payroll_action_id
,l_worksite
,l_state_abbrev
,'MWS_EST'
,l_sui_id
,l_fed_ein
,l_archive_value);
SELECT state_code
INTO l_state_code
FROM pay_state_rules
where fips_code = to_number(p_fips_code);
SELECT nvl(sum(prt.value1),0),
nvl(sum(prt.value2),0),
nvl(sum(prt.value3),0),
nvl(sum(prt.value4),0)
INTO l_month_1_count,
l_month_2_count,
l_month_3_count,
l_est_wages
FROM pay_us_rpt_totals prt
WHERE prt.organization_id = p_payroll_action_id
AND prt.location_id = to_number(p_est_id)
AND prt.state_abbrev = l_state_code
AND prt.attribute2 = p_sui_id
AND prt.attribute3 = p_fed_ein
and prt.attribute1 = 'MWS_EST';
DELETE
FROM pay_us_rpt_totals prt
WHERE prt.organization_id = p_payroll_action_id
AND prt.attribute1 = 'MWS_EST';
FUNCTION update_global_values(p_estab_ID number,
p_state_abbrev varchar2)
RETURN NUMBER
IS
BEGIN
IF p_estab_id <> pay_us_mwr_reporting_pkg.est_id
OR p_state_abbrev <> pay_us_mwr_reporting_pkg.state_abbrev
OR pay_us_mwr_reporting_pkg.estab_count = 20 THEN
pay_us_mwr_reporting_pkg.estab_count := 0;
END update_global_values;