[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
/******************************************************************
** 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
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 fai.user_entity_id = ue.user_entity_id
and fai.context1 = to_char(p_ass_act_id) -- context of assignment action 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 fai.value = hl.location_id
and hl.region_2 = pus.state_abbrev;
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 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 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 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';
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;