[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
22-Nov-2004 saikrish 115.47 3923296 Changed get_selection_information to check
SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD for Indiana
28-OCT-2004 saikrish 115.46 3923296 Changed get_selection_information to check
SUI_ER_GROSS_PER_JD_GRE_QTD for Indiana
22-OCT-2004 jgoswami 115.45 Fix Check Patch error
30-SEP-2004 jgoswami 115.44 3925772 modified archive_data, modified
cursor c_get_latest_asg to check for
all assignments for person which are
valid and paid in quarter.
01-MAR-2004 jgoswami 115.43 3416806 modified action_creation cursors to check for
assignment_type of Employee only.
Clean Package, removed unnecessary code.
19-FEB-2004 jgoswami 115.42 3331021 modified archive_data, remove query with RULE hint
and added cursor c_get_latest_asg
21-JAN-2004 jgoswami 115.41 3388513 Changed the criteria for picking up the emps
in fourth quarter.
check for SIT_SUBJ_WHABLE_PER_JD_GRE_YTD,
SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD,
SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD.
18-DEC-2003 jgoswami 115.40 3324974 comment correctly to initialize
l_prev_tax_unit_id to -99999999.
04-DEC-2003 fusman 115.39 3281209 Checked the checking criteria for NY
in fourth quarter.
30-JUL-2003 fusman 115.38 2922028 Changed the criteria for picking up the emps
for NY on fourth QTR.
07-JUL-2003 sodhingr 115.37 changed the cursor c_state_pr,c_ct_non_profit,
c_state,c_state_la_quality for performance.
Added the check for
asg.effective_end_date >= l_period_start
and asg.effective_start_date <= l_period_end
instead of ppa.effective_date between
ASG.effective_start_date and ASG.effective_end_date
02-Jun-2003 fusman 115.36 2965887 Checked for archive type in chk_gre_archive
and inserted archive_type in ff_archive_items.
28-MAY-2003 tmehra 115.35 2981455 Made changes to the action_creation
Added code to error out in case
if the wage plan is missing at both
the Asg and the GRE level for CA.
27-MAY-2003 tmehra 115.34 Made changes to the c_chk_asg_wp
cursor, The Asg's with NULL SUI
ID does not get falgged off now.
22-MAY-2003 tmehra 115.33 2707698 Replaced c_dup_orgn_info
cursor with a new select
statement due to performance
issues.
19-MAY-2003 tmehra 115.32 Made changes to the archiver
Pre-Process c_chk_gre_wp cursor.
15-MAY-2003 tmehra 115.31 Made changes to the archiver
Pre-Process.
07-MAY-2003 tmehra 115.30 Merged Single and Multi Wage Plan
logic for California.
23-APR-2003 tclewis 115.29 2924361 added a order by paf.effective_end_date
to the cursor c_asg_loc_end.
this is to return the latest
location id in the cursor.
30-MAR-2003 sodhingr 115.28 changed the cursor csr_defined_balance
in the function bal_db_item to join
with the legislation_code = 'US'
18-MAR-2003 sodhingr 115.27 changed the cursor c_state_pr, to
compare effective_date between
l_period_start and l_period_end
instead of comparing between l_period_start
and l_period start.
25-FEB-2003 sodhingr 115.22 2717128 Changed the cursors c_state ,
c_ct_non_profit,c_state_la_quality
for performance.
2809506 changed the cursor c_asg_loc_end for
performance, commenting the redundant
join with business group id
12-FEB-2002 sodhingr 115.21 2779152 Changed action_creation, added the
cursor c_state_pr, for PR.
11-SEP-2002 sodhingr 115.20 2549213 Changed the foloowing cursors to user
per_all_assignments_f instead of per_assignments_f
c_ct_non_profit, c_state_la_quality, c_state
30-MAY-2002 asasthan 115.19 2396909 For MMREF states SQWLs now
give warning when there is
no W2 Reporting Rules set up
for transmitter GRE.
Removed following procedures
that were earlier used by EOY
process and are not reqd by
SQWL process. These are
PROCEDURE EOY_RANGE_CURSOR
PROCEDURE EOY_ACTION_CREATION
PROCEDURE EOY_ARCHIVE_DATA
PROCEDURE EOY_ARCHINIT
25-MAR-2002 asasthan 115.18 Added ORDERED hint in action
creation cursor
20-MAR-2002 djoshi 115.17 Initalized l_prev_tax_unit_id
to -9999999;
to update_ff_archive_items.
22-AUG-2000 ashgupta 110.12 1382408 Changed the SQL statement of
c_rts_dup_wage cursor. This SQL was
changed due to Fidelity issue. Now
the cursor does not check for Multi
ple wage plan codes across the
assignments of a person. It just
checks that each individual assgn-
ment should not be having more than
one wage plan code. It takes care
of only paid assignments.
19-JUL-2000 ashgupta 40.14 1354144 Changed the SQL statement of
c_rts_dup_wage cursor. This SQL was
changed due to Fidelity performance
problem.
12-JUN-2000 asasthan 115.5 update till Q2 2000 changes and includes
the 11i fnd_date and fnd_number changes
22-MAY-2000 ashgupta 40.12 1237099 Added the error messages in the
preprocess_check function
02-MAR-2000 rpotnuru 40.11 1220213 Terminated Employees not showing for $th Qtr
NY sqwl. Range cursor date range will now the whole
Year for NY 4th Qtr SQWL.
08-FEB-2000 ashgupta 40.9 SQWL changes for city of Oakland
Added code in archinit
archive_data
range_cursor
Added a new fn preprocess_check
This was done for the enhancement
req 1063413
03_DEC-1999 asasthan 40.6 1093595
03-DEC-1999 rpotnuru 40.5 1095096 NY sqwl for 4th qtr date range is Year St to
1085774 Year End. so for reporting QTD balances
setting a flag in pay_assignment_actions
if the employee doesnt have balances for the QTD.
Added function update_ff_archive_item.
17-NOV-1999 asasthan Performance Tuning 1079787
27-OCT-1999 RPOTNURU 110.0 Bug fix 976472
25-oct-1999 djoshi added the A_SS_EE_WAGE_BASE and
A_SS_EE_WAGE rate to archive the data
related to bug 983094 and 101435
01-sep-1999 achauhan While archiving the employer data
add the context of pay_payroll_actions
to ff_archive_item_contexts.
11-aug-1999 achauhan Added the call to
eoy_archive_gre_data in the
eoy_range_cursor procedure. This is
being done to handle the situation
of archiving employer level data
even when there are no employees in
a GRE.
10-aug-1999 achauhan In the archive_data routine,
removed the use of payroll_action_id
>= 0.
04-Aug-1999 VMehta Changed eoy_archive_data to improve performance.
02-Jun-1999 meshah added new cursors in the range and action
creation cursors to check for non profit
gre's for the state of connecticut.
08-mar-1999 VMehta Added nvl while checking for l_1099R_ind
to correct the Louisiana quality jobs program
tape processing.
26-jan-1999 VMehta Modified function report_person_on_tape to
return false for all states except California
and Massachusetts.
24-Jan-1999 VMehta 805012 Added function report_person_on_tape to perform
check for retirees having SIT w/h in california.
06-Jan-1999 MReid Changed c_eoy_gre cursor to disable
business_group_id index on ppa side
30-dec-1998 vmehta 709641 Look at SUI_ER_SUBJ_WHABLE instead of SUI_ER_GROSS
for picking up people for SQWL . This makes sure
that only people with SUI wages are picked up.
27-dec-1998 vmehta Corrected the cursor in action creation to get the
tax_unit_name from pay_assignment_actions.
21-DEC-1998 achauhan Changed the cursor in action creation to get the
assignments from the pay_assignment_actions table.
08-DEC-1998 vmehta Removed grouping by on assignment_id while creating
assignment_ids
08-DEC-1998 nbristow Updated the c_state cursor to use
an exists rather than a join.
07-DEC-1998 nbristow Resolved some issues introduced by
40.13.
04-DEC-1998 vmehta 750802 Changed the cursors/logic to
pick up people who live in
California for the California SQWL.
29-NOV-1998 nbristow Changes to the SQWL code,
now using pay_us_asg_reporting.
25-Sep-1998 vmehta Changed the range cursor and
the assignment_action creation
cursors to support Louisiana
Quality Jobs Program Reporting.
08-aug-1998 achauhan Added the routines for eoy -
Year End Pre-Process
18-MAY-1998 NBRISTOW sqwl_range cursor now checks
the tax_unit_id etc.
06-MAY-1998 NBRISTOW
14-MAR-2005 sackumar 115.49 4222032 Change in the Range Cursor removing redundant
use of bind Variable (:payroll_action_id)
*/
function chk_gre_archive (p_payroll_action_id number) return boolean;
select fnd_number.canonical_to_number(UE.creator_id)
from ff_user_entities UE,
ff_database_items DI
where DI.user_name = p_db_item_name
and UE.user_entity_id = DI.user_entity_id
and Ue.creator_type = 'B'
and UE.legislation_code = 'US';
/* Name : get_selection_information
Purpose : Returns information used in the selection of people to be reported on.
Arguments :
The following values are returned :-
p_period_start - The start of the period over which to select
the people.
p_period_end - The end of the period over which to select
the people.
p_defined_balance_id - The balance which must be non zero for each
person to be included in the report.
p_group_by_gre - should the people be grouped by GRE.
p_group_by_medicare - Should the people ,be grouped by medicare
within GRE NB. this is not currently supported.
p_tax_unit_context - Should the TAX_UNIT_ID context be set up for
the testing of the balance.
p_jurisdiction_context - Should the JURISDICTION_CODE context be set up
for the testing of the balance.
Notes : This routine provides a way of coding explicit rules for
individual reports where they are different from the
standard selection criteria for the report type ie. in
NY state the selection of people in the 4th quarter is
different from the first 3.
*/
procedure get_selection_information
(
/* Identifies the type of report, the authority for which it is being run,
and the period being reported. */
p_report_type varchar2,
p_state varchar2,
p_quarter_start date,
p_quarter_end date,
p_year_start date,
p_year_end date,
/* Information returned is used to control the selection of people to
report on. */
p_period_start in out nocopy date,
p_period_end in out nocopy date,
p_defined_balance_id in out nocopy number,
p_group_by_gre in out nocopy boolean,
p_group_by_medicare in out nocopy boolean,
p_tax_unit_context in out nocopy boolean,
p_jurisdiction_context in out nocopy boolean
) is
begin
/* Depending on the report being processed, derive all the information
required to be able to select the people to report on. */
/* State Quarterly Wage Listings. */
if p_report_type = 'SQWL' then
/* New York state settings NB. the difference is that the criteria for
selecting people in the 4th quarter is different to that used for the
first 3 quarters of the tax year. */
if p_state = 'NY' then
if instr(to_char(p_quarter_end,'MM'), '12') = 0 then
/* Period is one of the first 3 quarters of tax year. */
p_period_start := p_quarter_start;
end get_selection_information;
select SR.jurisdiction_code
from pay_state_rules SR
where SR.state_code = p_state;
SELECT '1'
INTO l_resides_true
FROM dual
WHERE EXISTS (
SELECT '1'
FROM per_assignments_f paf,
per_addresses pad
WHERE paf.assignment_id = p_assignment_id AND
paf.person_id = pad.person_id AND
pad.date_from <= p_period_end AND
NVL(pad.date_to ,p_period_end) >= p_period_start AND
pad.region_2 = p_state AND
pad.primary_flag = 'Y');
select user_entity_id
into l_user_entity_id
from ff_database_items
where user_name = p_dbi_name;
/* Variables used to hold the select columns from the SQL statement.*/
l_person_id number;
/*This select is same as cursor c_state except the check for
NVL(HOI.org_information16, 'No') = 'Yes'*/
CURSOR c_state_la_quality IS
SELECT
ASG.person_id person_id,
ASG.assignment_id assignment_id,
paa.tax_unit_id tax_unit_id,
ppa.effective_date effective_end_date
FROM per_all_assignments_f ASG,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE ppa.effective_date between l_period_start
and l_period_end
and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
and paa.payroll_action_id = ppa.payroll_action_id
and paa.assignment_id = ASG.assignment_id
/*added to ignore skipped assignment */
and paa.action_status <> 'S'
-- and ppa.effective_date between ASG.effective_start_date and ASG.effective_end_date
/* Added for Performance, 01-JUL-2003 */
and asg.effective_end_date >= l_period_start
and asg.effective_start_date <= l_period_end
and ASG.business_group_id + 0 = l_bus_group_id
and ASG.person_id between stperson and endperson
and ASG.assignment_type = 'E'
and ASG.business_group_id = ppa.business_group_id -- 5152728
and exists (select '1'
from pay_us_asg_reporting puar,
pay_state_rules SR
where SR.state_code = l_state
and substr(SR.jurisdiction_code ,1,2) =
substr(puar.jurisdiction_code,1,2)
and ASG.assignment_id = puar.assignment_id
)
ORDER BY 1, 3, 4 DESC, 2 ;
SELECT
ASG.person_id person_id,
ASG.assignment_id assignment_id,
paa.tax_unit_id tax_unit_id,
ppa.effective_date effective_end_date
FROM per_all_assignments_f ASG,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_population_ranges ppr
WHERE ppa.effective_date between l_period_start
and l_period_end
and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
and paa.payroll_action_id = ppa.payroll_action_id
and paa.assignment_id = ASG.assignment_id
/*added to ignore skipped assignment */
and paa.action_status <> 'S'
-- and ppa.effective_date between ASG.effective_start_date and ASG.effective_end_date
/* Added for Performance, 01-JUL-2003 */
and asg.effective_end_date >= l_period_start
and asg.effective_start_date <= l_period_end
and ASG.business_group_id + 0 = l_bus_group_id
--and ASG.person_id between stperson and endperson
and ppr.payroll_action_id = pactid
and ppr.chunk_number = chunk
and ppr.person_id = ASG.person_id
and ASG.assignment_type = 'E'
and ASG.business_group_id = ppa.business_group_id -- 5152728
and exists (select '1'
from pay_us_asg_reporting puar,
pay_state_rules SR
where SR.state_code = l_state
and substr(SR.jurisdiction_code ,1,2) =
substr(puar.jurisdiction_code,1,2)
and ASG.assignment_id = puar.assignment_id
)
ORDER BY 1, 3, 4 DESC, 2 ;
SELECT
ASG.person_id person_id,
ASG.assignment_id assignment_id,
paa.tax_unit_id tax_unit_id,
ppa.effective_date effective_end_date
FROM per_all_assignments_f ASG,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE ppa.effective_date between l_period_start
and l_period_end
and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
and paa.payroll_action_id = ppa.payroll_action_id
and paa.assignment_id = ASG.assignment_id
/*added to ignore skipped assignment */
and paa.action_status <> 'S'
-- and ppa.effective_date between ASG.effective_start_date and ASG.effective_end_date
/* Added for Performance, 01-JUL-2003 */
and asg.effective_end_date >= l_period_start
and asg.effective_start_date <= l_period_end
and ASG.business_group_id + 0 = l_bus_group_id
and ASG.person_id between stperson and endperson
and ASG.assignment_type = 'E'
and ASG.business_group_id = ppa.business_group_id -- 5152728
and exists (select '1'
from pay_us_asg_reporting puar,
pay_state_rules SR
where SR.state_code = l_state
and substr(SR.jurisdiction_code ,1,2) =
substr(puar.jurisdiction_code,1,2)
and ASG.assignment_id = puar.assignment_id
)
ORDER BY 1, 3, 4 DESC, 2 ;
SELECT
ASG.person_id person_id,
ASG.assignment_id assignment_id,
paa.tax_unit_id tax_unit_id,
ppa.effective_date effective_end_date
FROM per_all_assignments_f ASG,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_population_ranges ppr
WHERE ppa.effective_date between l_period_start
and l_period_end
and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
and paa.payroll_action_id = ppa.payroll_action_id
and paa.assignment_id = ASG.assignment_id
/*added to ignore skipped assignment */
and paa.action_status <> 'S'
-- and ppa.effective_date between ASG.effective_start_date and ASG.effective_end_date
/* Added for Performance, 01-JUL-2003 */
and asg.effective_end_date >= l_period_start
and asg.effective_start_date <= l_period_end
and ASG.business_group_id + 0 = l_bus_group_id
-- and ASG.person_id between stperson and endperson
and ppr.payroll_action_id = pactid
and ppr.chunk_number = chunk
and asg.person_id = ppr.person_id
and ASG.assignment_type = 'E'
and ASG.business_group_id = ppa.business_group_id -- 5152728
and exists (select '1'
from pay_us_asg_reporting puar,
pay_state_rules SR
where SR.state_code = l_state
and substr(SR.jurisdiction_code ,1,2) =
substr(puar.jurisdiction_code,1,2)
and ASG.assignment_id = puar.assignment_id
)
ORDER BY 1, 3, 4 DESC, 2 ;
/*This select in c_ct_non_profit is same as cursor c_state except the check for
NVL(HOI.org_information20, 'No') = 'Yes'*/
CURSOR c_ct_non_profit IS
SELECT
ASG.person_id person_id,
ASG.assignment_id assignment_id,
paa.tax_unit_id tax_unit_id,
ppa.effective_date effective_end_date
FROM per_all_assignments_f ASG,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE ppa.effective_date between l_period_start
and l_period_end
and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
and paa.payroll_action_id = ppa.payroll_action_id
and paa.assignment_id = ASG.assignment_id
/*added to ignore skipped assignment */
and paa.action_status <> 'S'
-- and ppa.effective_date between ASG.effective_start_date and ASG.effective_end_date
/* Added for Performance, 01-JUL-2003 */
and asg.effective_end_date >= l_period_start
and asg.effective_start_date <= l_period_end
and ASG.business_group_id + 0 = l_bus_group_id
and ASG.person_id between stperson and endperson
and ASG.assignment_type = 'E'
and exists (select '1'
from pay_us_asg_reporting puar,
pay_state_rules SR
where SR.state_code = l_state
and substr(SR.jurisdiction_code ,1,2) =
substr(puar.jurisdiction_code,1,2)
and ASG.assignment_id = puar.assignment_id
)
ORDER BY 1, 3, 4 DESC, 2 ;
SELECT
ASG.person_id person_id,
ASG.assignment_id assignment_id,
paa.tax_unit_id tax_unit_id,
ppa.effective_date effective_end_date
FROM per_all_assignments_f ASG,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_population_ranges ppr
WHERE ppa.effective_date between l_period_start
and l_period_end
and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
and paa.payroll_action_id = ppa.payroll_action_id
and paa.assignment_id = ASG.assignment_id
/*added to ignore skipped assignment */
and paa.action_status <> 'S'
-- and ppa.effective_date between ASG.effective_start_date and ASG.effective_end_date
/* Added for Performance, 01-JUL-2003 */
and asg.effective_end_date >= l_period_start
and asg.effective_start_date <= l_period_end
and ASG.business_group_id + 0 = l_bus_group_id
--and ASG.person_id between stperson and endperson
and ppr.payroll_action_id = pactid
and ppr.chunk_number = chunk
and ppr.person_id = ASG.person_id
and ASG.assignment_type = 'E'
and exists (select '1'
from pay_us_asg_reporting puar,
pay_state_rules SR
where SR.state_code = l_state
and substr(SR.jurisdiction_code ,1,2) =
substr(puar.jurisdiction_code,1,2)
and ASG.assignment_id = puar.assignment_id
)
ORDER BY 1, 3, 4 DESC, 2 ;
SELECT ASG.person_id person_id,
ASG.assignment_id assignment_id,
paa.tax_unit_id tax_unit_id,
ppa.effective_date effective_end_date
FROM per_all_assignments_f ASG,
pay_assignment_actions paa,
pay_payroll_actions ppa,
hr_organization_information HOI_PR
WHERE ppa.effective_date between l_period_start and l_period_end
AND ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
AND paa.payroll_action_id = ppa.payroll_action_id
AND hoi_pr.organization_id = paa.tax_unit_id
AND HOI_pr.org_information_context = 'W2 Reporting Rules'
AND NVL(HOI_pr.org_information16, 'A') = 'P'
AND paa.assignment_id = ASG.assignment_id
/*added to ignore skipped assignment */
and paa.action_status <> 'S'
-- and ppa.effective_date between ASG.effective_start_date and ASG.effective_end_date
/* Added for Performance, 01-JUL-2003 */
and asg.effective_end_date >= l_period_start
and asg.effective_start_date <= l_period_end
AND ASG.business_group_id + 0 = l_bus_group_id
AND ASG.person_id between stperson and endperson
and ASG.assignment_type = 'E'
AND EXISTS (select 'x'
from pay_us_asg_reporting puar,
pay_state_rules SR
where substr(SR.jurisdiction_code ,1,2) =
substr(puar.jurisdiction_code,1,2)
and ASG.assignment_id = puar.assignment_id
and puar.tax_unit_id = hoi_pr.organization_id
and SR.state_code = l_state)
/* there shouldn't be any dependency on state tax rules
AND EXISTS (select 'x'
from hr_organization_information HOI
where hoi.organization_id = hoi_pr.organization_id
AND HOI.org_information_context = 'State Tax Rules'
AND HOI.org_information1 = l_state
AND NVL(HOI.org_information16, 'No') = 'No'
AND NVL(HOI.org_information20, 'No') = 'No') */
ORDER BY 1, 3, 4 DESC, 2;
SELECT ASG.person_id person_id,
ASG.assignment_id assignment_id,
paa.tax_unit_id tax_unit_id,
ppa.effective_date effective_end_date
FROM per_all_assignments_f ASG,
pay_assignment_actions paa,
pay_payroll_actions ppa,
hr_organization_information HOI_PR,
pay_population_ranges ppr
WHERE ppa.effective_date between l_period_start and l_period_end
AND ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
AND paa.payroll_action_id = ppa.payroll_action_id
AND hoi_pr.organization_id = paa.tax_unit_id
AND HOI_pr.org_information_context = 'W2 Reporting Rules'
AND NVL(HOI_pr.org_information16, 'A') = 'P'
AND paa.assignment_id = ASG.assignment_id
/*added to ignore skipped assignment */
and paa.action_status <> 'S'
-- and ppa.effective_date between ASG.effective_start_date and ASG.effective_end_date
/* Added for Performance, 01-JUL-2003 */
and asg.effective_end_date >= l_period_start
and asg.effective_start_date <= l_period_end
AND ASG.business_group_id + 0 = l_bus_group_id
--AND ASG.person_id between stperson and endperson
and ppr.payroll_action_id = pactid
and ppr.chunk_number = chunk
and ppr.person_id = ASG.person_id
and ASG.assignment_type = 'E'
AND EXISTS (select 'x'
from pay_us_asg_reporting puar,
pay_state_rules SR
where substr(SR.jurisdiction_code ,1,2) =
substr(puar.jurisdiction_code,1,2)
and ASG.assignment_id = puar.assignment_id
and puar.tax_unit_id = hoi_pr.organization_id
and SR.state_code = l_state)
/* there shouldn't be any dependency on state tax rules
AND EXISTS (select 'x'
from hr_organization_information HOI
where hoi.organization_id = hoi_pr.organization_id
AND HOI.org_information_context = 'State Tax Rules'
AND HOI.org_information1 = l_state
AND NVL(HOI.org_information16, 'No') = 'No'
AND NVL(HOI.org_information20, 'No') = 'No') */
ORDER BY 1, 3, 4 DESC, 2;
SELECT count(*) ct
FROM hr_organization_information
WHERE organization_id = p_tax_unit_id
AND org_information_context = 'PAY_US_STATE_WAGE_PLAN_INFO'
AND org_information1 = 'CA'
AND org_information4 = 'Y';
SELECT count(*) ct
FROM per_assignment_extra_info paei
WHERE paei.assignment_id = p_assignment_id
AND paei.information_type = 'PAY_US_ASG_STATE_WAGE_PLAN_CD'
AND paei.aei_information1 = 'CA'
AND paei.aei_information3 IS NOT NULL;
/* Return details used to control the selection of people to report on ie.
the SQL statement to run, the period over which to look for the people,
how to group the people, etc... */
select effective_date,
report_type,
report_qualifier,
report_category,
business_group_id
into l_effective_date,
l_report_type,
l_state,
l_report_cat,
l_bus_group_id
from pay_payroll_actions
where payroll_action_id = pactid;
get_selection_information
(l_report_type,
l_state,
l_quarter_start,
l_quarter_end,
l_year_start,
l_year_end,
l_period_start,
l_period_end,
l_defined_balance_id,
l_group_by_gre,
l_group_by_medicare,
l_tax_unit_context,
l_jurisdiction_context);
select report_format
into l_report_format
from pay_report_format_mappings_f
where report_type = l_report_type
and report_qualifier = l_state
and report_category = l_report_cat ;
select 'Y'
into l_1099R_ind
from hr_organization_information
where organization_id = l_tax_unit_id
and org_information_context = '1099R Magnetic Report Rules';
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
select min(paa.chunk_number)
from pay_assignment_actions paa
where paa.payroll_action_id = p_payroll_action_id;
select report_qualifier,
report_category
into l_state,
l_report_cat
from pay_payroll_actions
where payroll_action_id = p_payroll_action_id;
/* Added the select of report_category field in the above SQL on
10-FEB-2000 by Ashu Gupta (ashgupta) */
hr_utility.set_location ('archinit',2);
SELECT sr.jurisdiction_code
INTO jurisdiction_code
FROM pay_state_rules sr
WHERE sr.state_code = l_state;
select frpv.value
into l_jursd_value
from ff_route_parameter_values frpv,
ff_route_parameters frp
where frpv.route_parameter_id = frp.route_parameter_id
and frpv.user_entity_id = p_user_entity_id
and frp.route_id = p_route_id
and frp.parameter_name = 'Jursd. Level';
insert into ff_archive_items
(ARCHIVE_ITEM_ID,
USER_ENTITY_ID,
CONTEXT1,
VALUE,
ARCHIVE_TYPE)
values
(ff_archive_items_s.nextval,
p_user_entity_id,
p_context1,
p_value,
'PPA'); /* Bug:2965887 */
insert into ff_archive_item_contexts
(ARCHIVE_ITEM_ID,
SEQUENCE_NO,
CONTEXT,
CONTEXT_ID)
values
(ff_archive_items_s.currval,
p_sequence(i),
p_context(i),
p_context_id(i));
hr_utility.trace('Error while inserting into ff_archive_items'
|| to_char(sqlcode));
hr_utility.trace('Error while inserting into ff_archive_item_contexts'
|| to_char(sqlcode));
select context_id
into l_tax_context_id
from ff_contexts
where context_name = 'TAX_UNIT_ID';
select context_id
into l_jursd_context_id
from ff_contexts
where context_name = 'JURISDICTION_CODE';
select INSTR(legislative_parameters,'TRANSFER_STATE=')
+ LENGTH('TRANSFER_STATE=')
into l_from
from pay_payroll_actions
where payroll_action_id = p_payroll_action_id;
select INSTR(legislative_parameters,'TRANSFER_REPORTING_YEAR=')
into l_to
from pay_payroll_actions
where payroll_action_id = p_payroll_action_id;
Select state_code
into l_state_code
from pay_us_states
where state_abbrev = (
select substr(legislative_parameters,l_from, l_length )
from pay_payroll_actions
where payroll_action_id = p_payroll_action_id);
select to_char(sti.sui_er_wage_limit)
into l_value
from pay_us_state_tax_info_f sti,
pay_payroll_actions ppa
where ppa.payroll_action_id = p_payroll_action_id
and sti.state_code = l_state_code
and ppa.effective_date between sti.effective_start_date
and sti.effective_end_date
and sti.sta_information_category = 'State tax limit rate info';
l_seq_tab.delete;
l_context_id_tab.delete;
l_context_val_tab.delete;
select 'Y'
from dual
where exists (select null
from ff_archive_items fai
where fai.context1 = p_payroll_action_id
and archive_type = 'PPA'); /* Bug:2965887 */
select distinct pcty.jurisdiction_code pcty
from pay_us_emp_city_tax_rules_f pcty,
per_assignments_f paf1,
per_assignments_f paf
where paf.assignment_id = asgid
and paf.effective_end_date >= l_year_start
and paf.effective_start_date <= l_year_end
and paf1.person_id = paf.person_id
and paf1.effective_end_date >= l_year_start
and paf1.effective_start_date <= l_year_end
and pcty.assignment_id = paf1.assignment_id
and pcty.effective_start_date <= l_year_end
and pcty.effective_end_date >= l_year_start
and pcty.jurisdiction_code in ('33-005-2010',
'33-047-2010',
'33-061-2010',
'33-081-2010',
'33-085-2010',
'33-119-3230');
SELECT DISTINCT aei_information3
FROM per_assignment_extra_info paei
WHERE paei.assignment_id = asgid
AND paei.aei_information1 = g_sqwl_state
AND paei.information_type = 'PAY_US_ASG_STATE_WAGE_PLAN_CD';
SELECT hoi.org_information3 wage_plan
FROM hr_organization_information hoi
WHERE hoi.org_information_context = 'PAY_US_STATE_WAGE_PLAN_INFO'
AND hoi.organization_id = taxunitid
AND hoi.org_information1 = g_sqwl_state
AND hoi.org_information4 = 'Y';
select paa.assignment_action_id,
ppa.effective_date
from pay_assignment_actions paa,
per_all_assignments_f paf,
pay_payroll_actions ppa,
pay_action_classifications pac,
per_all_assignments_f paf1
where paf1.assignment_id = p_assignment_id
and paf.person_id = paf1.person_id
and paa.assignment_id = paf.assignment_id
and paa.tax_unit_id = taxunitid
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type = pac.action_type
and pac.classification_name = 'SEQUENCED'
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date
and ppa.effective_date between l_period_start and
l_period_end
and ((nvl(paa.run_type_id, ppa.run_type_id) is null
and paa.source_action_id is null)
or (nvl(paa.run_type_id, ppa.run_type_id) is not null
and paa.source_action_id is not null )
or (ppa.action_type = 'V' and ppa.run_type_id is null
and paa.run_type_id is not null
and paa.source_action_id is null))
order by paa.action_sequence desc;
SELECT /*+ORDERED*/
PAA.ASSIGNMENT_ACTION_ID,
PPA.EFFECTIVE_DATE
FROM PER_ALL_ASSIGNMENTS_F PAF1,
PER_ALL_ASSIGNMENTS_F PAF ,
PAY_ASSIGNMENT_ACTIONS PAA,
PAY_PAYROLL_ACTIONS PPA ,
PAY_ACTION_CLASSIFICATIONS PAC
WHERE PAF1.ASSIGNMENT_ID = p_assignment_id
AND PAF.PERSON_ID = PAF1.PERSON_ID
AND PAA.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID
AND PAA.TAX_UNIT_ID = taxunitid
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE = PAC.ACTION_TYPE
AND PAC.CLASSIFICATION_NAME = 'SEQUENCED'
AND PPA.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE AND PAF.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN l_period_start AND l_period_end
AND ((NVL(PAA.RUN_TYPE_ID, PPA.RUN_TYPE_ID) IS NULL
AND PAA.SOURCE_ACTION_ID IS NULL)
OR (NVL(PAA.RUN_TYPE_ID, PPA.RUN_TYPE_ID) IS NOT NULL
AND PAA.SOURCE_ACTION_ID IS NOT NULL )
OR (PPA.ACTION_TYPE = 'V'
AND PPA.RUN_TYPE_ID IS NULL
AND PAA.RUN_TYPE_ID IS NOT NULL
AND PAA.SOURCE_ACTION_ID IS NULL))
ORDER BY PAA.ACTION_SEQUENCE DESC;
SELECT aa.assignment_id,
pay_magtape_generic.date_earned (p_effective_date,aa.assignment_id),
aa.tax_unit_id,
aa.chunk_number, /* Bug 773937 */
aa.payroll_action_id /* Bug 773937 */
into asgid,
date_earned,
taxunitid,
l_chunk, /* Bug 773937 */
l_payroll_action_id /* Bug 773937 */
FROM pay_assignment_actions aa
WHERE aa.assignment_action_id = p_assactid;
SELECT context_id
INTO l_context_id_assignment_id
FROM ff_contexts
WHERE context_name = 'ASSIGNMENT_ID';
to select their values every time. This will improve performance */
--
-- IF (g_report_cat = 'RTM') THEN
-- OPEN c_archive_wage_plan_code_rtm;
INSERT INTO ff_archive_items (archive_item_id,
user_entity_id,
context1,
value)
VALUES( ff_archive_items_s.NEXTVAL ,
l_user_entity_id ,
p_assactid ,
l_wage_plan_code );
INSERT INTO ff_archive_item_contexts (archive_item_id,
sequence_no ,
context ,
context_id )
VALUES (ff_archive_items_s.currval,
1 ,
asgid ,
l_context_id_assignment_id);
INSERT INTO ff_archive_items (archive_item_id,
user_entity_id,
context1,
value)
VALUES( ff_archive_items_s.NEXTVAL ,
l_user_entity_id ,
p_assactid ,
l_wage_plan_code );
INSERT INTO ff_archive_item_contexts (archive_item_id,
sequence_no ,
context ,
context_id )
VALUES (ff_archive_items_s.currval,
1 ,
asgid ,
l_context_id_assignment_id);
/* Modifying to select effective_date from pay_payroll_actions corrsponding to
the assignment action selected to solve th e new York SQWL 4th quarter problem */
--Bug 3331021 : Remove Query with Rule hint and added cursor c_get_latest_asg
/* Get the effective_date and start_date of the payroll_Action_id */
select effective_date,
start_date
into l_period_end,
l_period_start
from pay_payroll_actions
where payroll_action_id = l_payroll_action_id;
update pay_assignment_actions paa
set serial_number = 1
where paa.assignment_action_id = p_assactid;
/* Name : update_ff_archive_items
Purpose : Given the SQWL payroll_action_id, identifies SQWL assignment actions for which
serial number is set to 1 (those employee assgnment actions who doesnt have balances
in the 4th Qtr while running 4th qtr new york SQWL report ) and update QTD balances
to zero for the assignment action in ff_archive_items.
Arguments : SQWL Payroll Action ID
*/
/* added A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_QTD and A_SIT_PRE_TAX_REDNS_PER_JD_GRE_QTD
for bug 1494215 of NY Q4 */
FUNCTION Update_ff_archive_items (p_payroll_action_id in VARCHAR2)
return varchar is
BEGIN
update ff_archive_items ffai
set ffai.value = 0
where ffai.user_entity_id in (
select user_entity_id
from ff_database_items
where user_name in ('A_SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD',
'A_SUI_ER_SUBJ_NWHABLE_PER_JD_GRE_QTD',
'A_SUI_ER_125_REDNS_PER_JD_GRE_QTD',
'A_SUI_ER_401_REDNS_PER_JD_GRE_QTD',
'A_SUI_ER_DEP_CARE_REDNS_PER_JD_GRE_QTD',
'A_SUI_ER_TAXABLE_PER_JD_GRE_QTD',
'A_SIT_SUBJ_WHABLE_PER_JD_GRE_QTD',
'A_SIT_SUBJ_NWHABLE_PER_JD_GRE_QTD',
'A_SIT_125_REDNS_PER_JD_GRE_QTD',
'A_SIT_401_REDNS_PER_JD_GRE_QTD',
'A_SIT_DEP_CARE_REDNS_PER_JD_GRE_QTD',
'A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_QTD',
'A_SIT_PRE_TAX_REDNS_PER_JD_GRE_QTD',
'A_SIT_WITHHELD_PER_JD_GRE_QTD')
)
and ffai.context1 in (
select paa.assignment_action_id
from pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.payroll_action_id = paa.payroll_action_id
and ppa.report_type = 'SQWL'
and ppa.report_qualifier = 'NY'
and ppa.payroll_action_id = to_number(p_payroll_action_id)
and paa.serial_number = 1
);
END Update_ff_archive_items;
SELECT count(*) ct
FROM per_assignments_f paf,
per_assignment_extra_info paei
WHERE paf.business_group_id = l_bus_group_id
AND paf.effective_end_date >= l_period_start
AND paf.effective_start_date <= l_period_end
AND paei.information_type = 'PAY_US_ASG_STATE_WAGE_PLAN_CD'
AND paei.aei_information1 = l_state /* Added for performance improvement Bug# 4344959 */
AND paei.assignment_id = paf.assignment_id
AND NOT EXISTS (SELECT null
FROM hr_organization_information orgi,
hr_soft_coding_keyflex sft
WHERE orgi.organization_id = to_number(sft.segment1)
AND sft.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
AND orgi.org_information1 = paei.aei_information1
AND (orgi.org_information2 = paei.aei_information2
OR paei.aei_information2 IS NULL)
AND orgi.org_information_context = 'PAY_US_STATE_WAGE_PLAN_INFO'
AND orgi.org_information3 = paei.aei_information3);
SELECT count(*) ct
FROM hr_legal_entities org
WHERE org.business_group_id = l_bus_group_id
AND EXISTS (SELECT null
FROM hr_organization_information orgi
WHERE organization_id = org.organization_id
AND org_information_context = 'PAY_US_STATE_WAGE_PLAN_INFO'
AND org_information1 = 'CA')
AND NOT EXISTS ( SELECT null
FROM hr_organization_information orgi
WHERE organization_id = org.organization_id
AND org_information_context = 'PAY_US_STATE_WAGE_PLAN_INFO'
AND org_information1 = 'CA'
AND org_information4 = 'Y');
SELECT count(*) ct
FROM hr_legal_entities org,
(select distinct
a.organization_id,
a.org_information1,
a.org_information3
FROM hr_organization_information a
WHERE org_information_context = 'PAY_US_STATE_WAGE_PLAN_INFO') b
WHERE org.business_group_id = l_bus_group_id
AND b.organization_id = org.organization_id
AND 1 < ( SELECT count(*)
FROM hr_organization_information orgi
WHERE organization_id = org.organization_id
AND org_information_context = 'PAY_US_STATE_WAGE_PLAN_INFO'
AND org_information1 = b.org_information1
AND org_information3 = b.org_information3);
SELECT ASG.LOCATION_ID
FROM per_assignments_f ASG
, pay_assignment_actions ASSACT
, pay_payroll_actions PACT
WHERE ASSACT.assignment_action_id = p_ass_act_id
AND ASSACT.payroll_action_id = PACT.payroll_action_id
AND ASSACT.assignment_id = ASG.assignment_id
AND add_months(trunc (PACT.effective_date, 'Q'), p_mon_of_qtr - 1) + 11
BETWEEN ASG.effective_start_date
AND ASG.Effective_end_date;
SELECT paf.location_id
FROM per_assignments_f paf,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE (paa.assignment_action_id = p_ass_acti_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_id = paf.assignment_id
AND ppa.business_group_id = paf.business_group_id
AND ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
)
OR (paa.assignment_action_id = p_ass_acti_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_id = paf.assignment_id
AND ppa.business_group_id = paf.business_group_id
AND paf.effective_end_date =
(SELECT max(paf1.effective_end_date)
FROM per_assignments_f paf1
WHERE paf1.assignment_id = paf.assignment_id
AND paf1.effective_end_date BETWEEN ppa.start_date
AND ppa.effective_date
)
);
SELECT paf.location_id
FROM per_assignments_f paf,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.assignment_action_id = p_ass_acti_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_id = paf.assignment_id
-- commenting the redundant join with business group id for bug 2809506
-- AND ppa.business_group_id = paf.business_group_id
AND ((ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date)
OR
(paf.effective_end_date =
(SELECT max(paf1.effective_end_date)
FROM per_assignments_f paf1
WHERE paf1.assignment_id = paf.assignment_id
AND paf1.effective_end_date BETWEEN ppa.start_date
AND ppa.effective_date)
)
)
order by paf.effective_end_date desc;
SELECT user_entity_id
INTO l_user_entity_id
FROM ff_user_entities
WHERE user_entity_name = 'A_SQWL_LOC_MON_' || to_char(i);
SELECT user_entity_id
INTO l_user_entity_id
FROM ff_user_entities
WHERE user_entity_name = 'A_SQWL_LOC_QTR_END';
Purpose : This returns the select statement that is used to created the
range rows.
Arguments :
Notes :
*/
procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
l_state pay_payroll_actions.report_qualifier%type;
select '1' from hr_organization_information
where organization_id = cp_tax_unit_id
and org_information_context = 'W2 Reporting Rules';
SELECT report_qualifier,
report_category,
effective_date,
start_date,
business_group_id
INTO l_state,
l_report_cat,
l_effective_date,
l_start_date,
l_business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = pactid;
hr_utility.trace('Selected from pay_payroll_actions ');
sqwl_range := 'SELECT distinct ASG.person_id
FROM hr_organization_information HOI,
per_assignments_f ASG,
pay_us_asg_reporting puar,
pay_state_rules SR
WHERE SR.state_code = ''' || l_state || '''
AND puar.jurisdiction_code like substr(SR.jurisdiction_code ,1,2)||''%''
AND ASG.assignment_id = puar.assignment_id
AND ASG.assignment_type = ''E''
AND ASG.effective_start_date <= ''' || l_effective_date || '''
AND ASG.effective_end_date >= ''' || l_start_date || '''
AND ASG.business_group_id + 0 = ''' || l_business_group_id || '''
AND HOI.organization_id = puar.tax_unit_id
AND HOI.ORG_INFORMATION_CONTEXT = ''State Tax Rules''
AND HOI.ORG_INFORMATION1 = ''' || l_state || '''
AND NVL(HOI.ORG_INFORMATION16,''No'') = ''Yes''
AND not exists (select ''x''
from hr_organization_information HOI2
where HOI2.organization_id = puar.tax_unit_id
AND HOI2.ORG_INFORMATION_CONTEXT = ''1099R Magnetic Report Rules''
AND HOI2.ORG_INFORMATION2 is not null)
AND ASG.payroll_id is not null
AND :payroll_action_id is not null
ORDER BY ASG.person_id';
sqwl_range := 'SELECT distinct ASG.person_id
FROM hr_organization_information HOI,
per_assignments_f ASG,
pay_us_asg_reporting puar,
pay_state_rules SR
WHERE SR.state_code = ''' || l_state || '''
AND puar.jurisdiction_code like substr(SR.jurisdiction_code ,1,2)||''%''
AND ASG.assignment_id = puar.assignment_id
AND ASG.assignment_type = ''E''
AND ASG.effective_start_date <= ''' || l_effective_date || '''
AND ASG.effective_end_date >= ''' || l_start_date || '''
AND ASG.business_group_id + 0 = ''' || l_business_group_id || '''
AND HOI.organization_id = puar.tax_unit_id
AND HOI.ORG_INFORMATION_CONTEXT = ''State Tax Rules''
AND HOI.ORG_INFORMATION1 = ''' || l_state || '''
AND NVL(HOI.ORG_INFORMATION20,''No'') = ''Yes''
AND not exists (select ''x''
from hr_organization_information HOI2
where HOI2.organization_id = puar.tax_unit_id
AND HOI2.ORG_INFORMATION_CONTEXT = ''1099R Magnetic Report Rules''
AND HOI2.ORG_INFORMATION2 is not null)
AND ASG.payroll_id is not null
AND :payroll_action_id is not null
ORDER BY ASG.person_id';
sqwl_range := 'SELECT distinct ASG.person_id
FROM hr_organization_information HOI,
per_assignments_f ASG,
pay_us_asg_reporting puar,
pay_state_rules SR
WHERE SR.state_code = ''' || l_state || '''
AND puar.jurisdiction_code like substr(SR.jurisdiction_code ,1,2)||''%''
AND ASG.assignment_id = puar.assignment_id
AND ASG.assignment_type = ''E''
AND ASG.effective_start_date <= ''' || l_effective_date || '''
AND ASG.effective_end_date >= ''' || l_start_date || '''
AND ASG.business_group_id + 0 = ''' || l_business_group_id || '''
AND ((''' || l_state || ''' IN ( ''CA'',''ME''))
OR (not exists (select ''x''
from hr_organization_information HOI2
where HOI2.organization_id = puar.tax_unit_id
AND HOI2.ORG_INFORMATION_CONTEXT = ''1099R Magnetic Report Rules''
AND HOI2.ORG_INFORMATION2 is not null)))
AND HOI.organization_id = puar.tax_unit_id
AND HOI.ORG_INFORMATION_CONTEXT = ''State Tax Rules''
AND HOI.ORG_INFORMATION1 = ''' || l_state || '''
AND NVL(HOI.ORG_INFORMATION16,''No'') = ''No''
AND NVL(HOI.ORG_INFORMATION20,''No'') = ''No''
AND ASG.payroll_id is not null
AND :payroll_action_id is not null
ORDER BY ASG.person_id';
sqwl_range := 'SELECT distinct ASG.person_id
FROM pay_payrolls_f PPY,
hr_organization_information HOI,
per_assignments_f ASG,
pay_us_asg_reporting puar,
pay_state_rules SR
WHERE SR.state_code = ''' || l_state || '''
AND substr(SR.jurisdiction_code ,1,2) =
substr(puar.jurisdiction_code,1,2)
AND ASG.assignment_id = puar.assignment_id
AND ASG.assignment_type = ''E''
AND ASG.effective_start_date <= ''' || l_effective_date || '''
AND ASG.effective_end_date >= ''' || l_start_date || '''
AND ASG.business_group_id + 0 = ''' || l_business_group_id || '''
AND ((''' || l_state || ''' IN ( ''CA'',''ME''))
OR (not exists (select ''x''
from hr_organization_information HOI2
where HOI2.organization_id = puar.tax_unit_id
AND HOI2.ORG_INFORMATION_CONTEXT = ''1099R Magnetic Report Rules''
AND HOI2.ORG_INFORMATION2 is not null)))
AND HOI.organization_id = puar.tax_unit_id
AND HOI.ORG_INFORMATION_CONTEXT = ''State Tax Rules''
AND HOI.ORG_INFORMATION1 = ''' || l_state || '''
AND NVL(HOI.ORG_INFORMATION16,''No'') = ''No''
AND NVL(HOI.ORG_INFORMATION20,''No'') = ''No''
AND PPY.payroll_id = ASG.payroll_id
AND :payroll_action_id is not null
ORDER BY ASG.person_id';
/* Select Tax unit Id from legislative parameters */
select INSTR(legislative_parameters,'TRANSFER_TRANS_LEGAL_CO_ID=')
+ LENGTH('TRANSFER_TRANS_LEGAL_CO_ID=')
into l_from
from pay_payroll_actions
where payroll_action_id = pactid;
select INSTR(legislative_parameters,'TRANSFER_DATE=')
into l_to
from pay_payroll_actions
where payroll_action_id = pactid;
select fnd_number.canonical_to_number(substr(legislative_parameters, l_from , l_length ))
into l_tax_unit_id
from pay_payroll_actions
where payroll_action_id = pactid;