[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
30-Jan-2013 sjawid 115.86 14456648 Modified get_selection_information
25-Jan-2013 sjawid 115.85 14456648 Modified Action Creation code to implement
SMWL 'IL' state legislative requirement to
report the Employer(GRE) with zero wages but
not display employees with zero wages.
Added code to update Serial Number column
of pay_assignment_actions to 'X' when the
SUI wages value is zero for the employee.
The assignments which are marked as 'X' in the Serial_number
columns will be excluded from the flat file. The cursor
smwl_employee at package specifications handles this case.
22-Jan-2013 schowl 115.84 14456648 Modified procedures 'get_dates' and
'get_selection_information' for SMWL report type
26-Aug-2012 nvelaga 115.83 14541245 Modified the calculation of Out of State Wages
for AR SQWL.
26-Jul-2012 nvelaga 115.82 14371049 Modified the cursors to handle Multi-Assignments.
23-Jul-2012 nvelaga 115.81 14358354 Modified the AR SUI Limit reached check to be
based on Reduced Subject AR Wages reported in
previous Quarters.
16-Jul-2012 nvelaga 115.80 14097843 Modified the calculation of AR Out of State Wages,
based on Reduced Subject AR Wages.
18-Jun-2012 nvelaga 115.79 14097843 Replaced != with <> for GSCC failure.
15-Jun-2012 nvelaga 115.78 14097843 Modified archive_data procedure to calculate
the AR Out of State Wages for reporting in AR SQWL.
3-May-2011 rosuri 115.77 12322280 Added code to archive Ohio SQWL related data
to procedure archive_data.
14-Mar-2011 sjawid 115.72-115.76 10649380 Modified the package to add necessary changes
for SQWL GRE wise enhancement.
1. Range Cursor Modified
2. Raising Error if Customer use
old sqwl process, Customer should always
use new conc program , "SQWL (Enhanced)".
3. Modified Action_creation code to skip
other GREs when Customer selects
Report Output option as "Single GRE".
13-Apr-2010 emunisek 115.72 9561700 Added date check condition in
cursor get_previous_fl_taxable
12-Apr-2010 emunisek 115.71 9561700 Made changes to use the maximum
effective date of Assignment's
payroll actions in Balance Call
if the assignment ends in between
the Quarter for FL SQWL.
30-Mar-2010 emunisek 115.68 9356178 Modified to fetch the balances in archive_data
for Florida SQWL based on virtual date
24-Mar-2010 emunisek 115.67 9356178 Reorganized the code as per the suggestions
made in codereview.
23-Mar-2010 emunisek 115.66 9356178 Made changes to make file GSCC Compliant
23-Mar-2010 emunisek 115.65 9356178 Added code to archive Florida SQWL
related data to procedure archive_data
06-Jun-2008 mikarthi 115.63 6774422 Changed _cursor c_get_latest_asg
for improving performance
14-Mar-2007 saurgupt 115.62 5152728 Changed the range_cursor and action_creation to
improve perf. In range_cursor, removed pay_payrolls_f.
07-Apr-2006 sudedas 115.60 4344959 changing preprocess_check, cursor (c_chk_asg_wp)
01-Feb-2006 sudedas 115.59 4890376 Removing hr_organization_information
from action_creation cursors (including
LA,CT) as the checks are there for range_cursor
24-JAN-2006 sackumar 115.58 4869678 Modified the c_get_latest_asg cursor in
archive_data procedure.removed the +0 from
the query to enable the indexes.
16-AUG-2005 sudedas 115.55 Adding some trace messages for
procedure archive_asg_locs.
10-AUG-2005 sudedas 115.54 4349864 action_creation is modified to
enable Range Person ID functionality
for LA, PR and CT (non-profit)
24-JUN-2005 sudedas 115.53 4310812 action_creation is modified for
State of Maine.
22-JUN-2005 sudedas 115.52 4310812 range_cursor is changed to include
Maine like California.
30-MAY-2005 sudedas 115.51 3843134 action_creation is modified for performance
25-MAY-2005 sudedas 115.50 4310812 action_creation and report_person_on_tape
is modified for Maine Sqwl.
24-Nov-2004 saikrish 115.48 Commented the trace.
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' or p_report_type = 'SMWL' then /* Modified for Bug 14456648 */
/* 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 pay_us_payroll_utils.get_parameter('TRANSFER_GRE',legislative_parameters)
INTO l_transfer_gre_id
FROM pay_payroll_actions
WHERE payroll_action_id = pactid;
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 pay_us_payroll_utils.get_parameter('TRANSFER_GRE',legislative_parameters)
INTO l_transfer_gre_id
FROM pay_payroll_actions
WHERE payroll_action_id = pactid;
-- insert into pay_assignment_actions.
/* Bug: 10649380 : Create Assignment action only if GRE is equal to the
Tranfer GRE parameter which is getting passed through the new SQWL process
State Quarterly Wage Listing (Enhanced) */
IF l_transfer_gre_id is null OR l_transfer_gre_id = l_tax_unit_id THEN
hr_utility.set_location ('actio_creation',9);
UPDATE pay_assignment_actions
SET SERIAL_NUMBER = 'X'
WHERE assignment_action_id = lockingactid;
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 pdb.defined_balance_id,pbd.dimension_name
from pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
where pbt.legislation_code = 'US'
and pbt.balance_name = 'SUI ER Taxable'
and pbd.dimension_name in ('Person in JD within GRE Quarter to Date',
'Person in JD within GRE Year to Date',
'Person within Government Reporting Entity Year to Date')
and pbd.legislation_code = 'US'
and pdb.balance_type_id = pbt.balance_type_id
and pdb.balance_dimension_id = pbd.balance_dimension_id;
select pdb.defined_balance_id,pbd.dimension_name
from pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
where pbt.legislation_code = 'US'
and pbt.balance_name = 'SUI ER Taxable'
and pbd.dimension_name in ('Person in JD within GRE Quarter to Date',
'Person within Government Reporting Entity Quarter to Date')
and pbd.legislation_code = 'US'
and pdb.balance_type_id = pbt.balance_type_id
and pdb.balance_dimension_id = pbd.balance_dimension_id;
select sum(to_number(nvl(value,'0')))
from ff_archive_items ffai,
pay_assignment_actions paa,
pay_payroll_actions ppa
where ffai.user_entity_id = l_user_entity_id
and ffai.context1=to_char(paa.assignment_action_id)
and paa.tax_unit_id = taxunitid
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.report_type = 'SQWL'
and ppa.report_qualifier = 'FL'
and ppa.action_type = 'X'
and trunc(ppa.effective_date,'YEAR') = trunc(p_effective_date,'YEAR')
and ppa.effective_date < p_effective_date
and paa.assignment_id in
(select distinct paaf2.assignment_id
from per_all_assignments_f paaf1,
per_all_assignments_f paaf2
where paaf1.assignment_id = asgid
and paaf1.person_id = paaf2.person_id
and paaf2.effective_start_date <= p_effective_date);
select max(ppa.effective_date)
from per_all_assignments_f asg,
pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.effective_date between p_quarter_start_date
and p_quarter_end_date
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
and paa.action_status <> 'S'
and asg.effective_end_date >= p_quarter_start_date
and asg.effective_start_date <= p_quarter_end_date
and asg.business_group_id = ppa.business_group_id
and paa.tax_unit_id = taxunitid
and asg.assignment_id = asgid;
select pdb.defined_balance_id, pbt.balance_name, pbd.dimension_name
from pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
where pbt.legislation_code = 'US'
and (( pbt.balance_name = 'SUI ER Taxable'
and pbd.dimension_name in ('Person in JD within GRE Year to Date',
'Person within Government Reporting Entity Year to Date'))
or ( pbt.balance_name in ('SUI ER Subj Whable', 'SUI ER Pre Tax Redns')
and pbd.dimension_name in ('Person in JD within GRE Quarter to Date',
'Person in JD within GRE Year to Date'))
)
and pbd.legislation_code = 'US'
and pdb.balance_type_id = pbt.balance_type_id
and pdb.balance_dimension_id = pbd.balance_dimension_id;
select sum(to_number(nvl(value,'0')))
from ff_archive_items ffai,
pay_assignment_actions paa,
pay_payroll_actions ppa
where ffai.user_entity_id = l_user_entity_id
and ffai.context1=to_char(paa.assignment_action_id)
and paa.tax_unit_id = taxunitid
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.report_type = 'SQWL'
and ppa.report_qualifier = 'AR'
and ppa.action_type = 'X'
and trunc(ppa.effective_date,'YEAR') = trunc(p_effective_date,'YEAR')
and ppa.effective_date < p_effective_date
and paa.assignment_id in
(select distinct paaf2.assignment_id
from per_all_assignments_f paaf1,
per_all_assignments_f paaf2
where paaf1.assignment_id = asgid
and paaf1.person_id = paaf2.person_id
and paaf2.effective_start_date <= p_effective_date);
SELECT pus.state_abbrev
FROM pay_us_emp_fed_tax_rules_f pef,
pay_us_states pus
WHERE pef.sui_state_code = pus.state_code
AND pus.state_abbrev <> l_ar_state_abbrev
AND pef.effective_start_date <= l_eff_end_date
AND pef.effective_end_date >= l_eff_start_date
AND pef.assignment_id IN (SELECT paa2.assignment_id
FROM per_all_assignments_f paa1,
per_all_assignments_f paa2
WHERE paa1.assignment_id = l_assignment_id
AND paa1.person_id = paa2.person_id
AND paa1.assignment_type = 'E'
AND paa1.effective_start_date <= l_eff_end_date
AND paa1.effective_end_date >= l_eff_start_date
AND paa2.assignment_type = 'E'
AND paa2.effective_start_date <= l_eff_end_date
AND paa2.effective_end_date >= l_eff_start_date
)
ORDER BY pef.assignment_id, pef.effective_start_date;
SELECT pus.state_abbrev
FROM pay_us_emp_state_tax_rules_f pes,
pay_us_states pus
WHERE pes.state_code = pus.state_code
AND pus.state_abbrev <> l_ar_state_abbrev
AND pes.effective_start_date <= l_eff_end_date
AND pes.effective_end_date >= l_eff_start_date
AND pes.assignment_id IN (SELECT paa2.assignment_id
FROM per_all_assignments_f paa1,
per_all_assignments_f paa2
WHERE paa1.assignment_id = l_assignment_id
AND paa1.person_id = paa2.person_id
AND paa1.assignment_type = 'E'
AND paa1.effective_start_date <= l_eff_end_date
AND paa1.effective_end_date >= l_eff_start_date
AND paa2.assignment_type = 'E'
AND paa2.effective_start_date <= l_eff_end_date
AND paa2.effective_end_date >= l_eff_start_date
)
ORDER BY pes.assignment_id, pes.effective_start_date, pes.state_code;
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 least(max(effective_end_date),p_effective_date)
INTO l_effective_end_date
FROM per_all_assignments_f
WHERE assignment_id = asgid
AND assignment_type = 'E'
AND effective_end_date >= add_months(last_day(p_effective_date),-3)+1 ; /*Quarter Start Date */
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_sui_oh_taxable_qtd );
SELECT context_id
INTO l_context_id_tax_unit_id
FROM ff_contexts
WHERE context_name = 'TAX_UNIT_ID';
INSERT INTO ff_archive_item_contexts
(archive_item_id,sequence_no,context,context_id)
VALUES (ff_archive_items_s.CURRVAL,1,taxunitid,l_context_id_tax_unit_id);
SELECT context_id
INTO l_context_id_jurisdiction_code
FROM ff_contexts
WHERE context_name = 'JURISDICTION_CODE';
INSERT INTO ff_archive_item_contexts
(archive_item_id,sequence_no,context,context_id)
VALUES (ff_archive_items_s.CURRVAL,1,l_oh_jurisdiction_code,l_context_id_jurisdiction_code);
SELECT LEAST(MAX(pef.effective_end_date), p_effective_date)
INTO l_effective_date
FROM per_all_assignments_f paa,
pay_us_emp_fed_tax_rules_f pef,
pay_us_states pus
WHERE paa.assignment_id = pef.assignment_id
AND paa.assignment_id = asgid
AND paa.assignment_type = 'E'
AND pef.sui_state_code = pus.state_code
AND pus.state_abbrev = g_sqwl_state
AND paa.effective_end_date >= ADD_MONTHS(LAST_DAY(p_effective_date),-3)+1 -- Quarter Start Date
AND paa.effective_start_date <= p_effective_date
AND pef.effective_end_date >= ADD_MONTHS(LAST_DAY(p_effective_date),-3)+1 -- Quarter Start Date
AND pef.effective_start_date <= p_effective_date;
INSERT INTO ff_archive_items (archive_item_id,
user_entity_id,
context1,
value)
VALUES(ff_archive_items_s.NEXTVAL ,
l_sui_ar_tax_user_ent_id ,
p_assactid ,
l_sui_ar_adj_taxable_qtd );
SELECT context_id
INTO l_context_id_tax_unit_id
FROM ff_contexts
WHERE context_name = 'TAX_UNIT_ID';
INSERT INTO ff_archive_item_contexts
(archive_item_id,sequence_no,context,context_id)
VALUES (ff_archive_items_s.CURRVAL,1,taxunitid,l_context_id_tax_unit_id);
SELECT context_id
INTO l_context_id_jurisdiction_code
FROM ff_contexts
WHERE context_name = 'JURISDICTION_CODE';
INSERT INTO ff_archive_item_contexts
(archive_item_id,sequence_no,context,context_id)
VALUES (ff_archive_items_s.CURRVAL,1,l_ar_jurisdiction_code,l_context_id_jurisdiction_code);
INSERT INTO ff_archive_items (archive_item_id,
user_entity_id,
context1,
value)
VALUES(ff_archive_items_s.NEXTVAL ,
l_sui_ar_oos_rpt_user_ent_id,
p_assactid ,
l_sui_ar_oos_rpt_qtd );
INSERT INTO ff_archive_item_contexts
(archive_item_id,sequence_no,context,context_id)
VALUES (ff_archive_items_s.CURRVAL,1,taxunitid,l_context_id_tax_unit_id);
INSERT INTO ff_archive_item_contexts
(archive_item_id,sequence_no,context,context_id)
VALUES (ff_archive_items_s.CURRVAL,1,l_ar_jurisdiction_code,l_context_id_jurisdiction_code);
INSERT INTO ff_archive_items (archive_item_id,
user_entity_id,
context1,
value)
VALUES(ff_archive_items_s.NEXTVAL ,
l_sui_ar_oos_stcd_user_ent_id,
p_assactid ,
l_sui_ar_oos_state_code );
SELECT least(max(effective_end_date),p_effective_date)
INTO l_effective_end_date
FROM per_all_assignments_f
WHERE assignment_id = asgid
AND assignment_type = 'E'
AND effective_end_date >= add_months(last_day(p_effective_date),-3)+1 ; /*Quarter Start Date */
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_sui_fl_taxable_qtd );
SELECT context_id
INTO l_context_id_tax_unit_id
FROM ff_contexts
WHERE context_name = 'TAX_UNIT_ID';
INSERT INTO ff_archive_item_contexts
(archive_item_id,sequence_no,context,context_id)
VALUES (ff_archive_items_s.CURRVAL,1,taxunitid,l_context_id_tax_unit_id);
SELECT context_id
INTO l_context_id_jurisdiction_code
FROM ff_contexts
WHERE context_name = 'JURISDICTION_CODE';
INSERT INTO ff_archive_item_contexts
(archive_item_id,sequence_no,context,context_id)
VALUES (ff_archive_items_s.CURRVAL,1,fl_jurisdiction_code,l_context_id_jurisdiction_code);
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 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)
BETWEEN ASG.effective_start_date
AND ASG.Effective_end_date
OR last_day(add_months(trunc (PACT.effective_date, 'Q'), p_mon_of_qtr - 1))
BETWEEN ASG.effective_start_date
AND ASG.Effective_end_date)
ORDER BY ASG.effective_start_date desc ;
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 ');
SELECT pay_us_payroll_utils.get_parameter('TRANSFER_PARENT_PAYROLL_ACTION',legislative_parameters)
INTO l_parent_payroll_action_id
FROM pay_payroll_actions
WHERE payroll_action_id = pactid;
SELECT pay_us_payroll_utils.get_parameter('TRANSFER_GRE',legislative_parameters)
INTO l_gre
FROM pay_payroll_actions
WHERE payroll_action_id = pactid;
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 puar.tax_unit_id = nvl(''' || l_gre || ''',puar.tax_unit_id) /* bug 10649380 */
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 puar.tax_unit_id = nvl(''' || l_gre || ''',puar.tax_unit_id) /* bug 10649380 */
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 puar.tax_unit_id = nvl(''' || l_gre || ''',puar.tax_unit_id) /* bug 10649380 */
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;