[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
23-JAN-2004 ahanda 115.79 Modifed select stmt to get the
Spouse SSN for PR GRE to get the
last row valid for the year.
04-DEC-2003 sodhingr 115.75 Correct the values being passed to
pay_us_sqwl_udf.get_employment_code
26-NOV-2003 sodhingr 115.74 2219097 Changed package eoy_archive_data
and eoy_archive_gre_data to archive
government_employer flag and
changed logic to archive
employement code for all employees
even if the GRE is non-govement.
06-NOV-2003 sodhingr 115.73 2084862 Archiving Disability plan code
required for NJ magnetic tape
3234690 Archiving 1099R distribution code
24-OCT-2003 sodhingr 115.72 3207279 Added the check for language='US'
in the cursor csr_defined_balance
23-SEP-2003 sodhingr 115.71 3155042 Changed the cursor c_balance to
get meaning from fnd_lokkup_values
instead of fnd_common_lookups to fix
performance issue
04-SEP-2003 sodhingr 115.70 2219097 Changed procedure eoy_archive_data
to archive medicare, SS and thei
employement code
05-AUG-2003 sodhingr 115.68 2901349 Commented the cursor c_eoy_all and
eoy_all_range as GRE is manadatory
parameter for year end process so
these cursors will never be used.
Also, changed cursor eoy_gre_range
to join with pay_us_asg_reporting
instead of hr_soft_coding_keyflex.
This will ensure that assignments
are picked up year end preprocess
even if GRE is no longer valid for
that assignment.
05-AUG-2003 sodhingr 115.68 2753184 Change the logic to archive school
districtonly once. If the residence
address is changed and the school
district remains the same then
archiver
was archiving it twice, one when
archiving the city school district
and other when archiving the county
school district
18-JUN-2003 sodhingr 115.67 3011003 Commented the cursors
c_get_defined_balance_id,
c_get_puerto_rico_bal,
c_get_1099r_bal and using
pay_us_payroll_utils.
c_get_defined_balance_id
18-jun-2003 sodhingr 115.66 3011003 Changed the cursors
c_get_defined_balance_id,
c_get_puerto_rico_bal,
c_get_1099r_bal to add
join with creator_type = 'B'
27-DEC-2002 asasthan 115.65 2727539 changes to c_get_latest_asg cursor
to also pick reversal actions
24-DEC-2002 asasthan 115.65 changes to c_get_latest_asg cursor
to pick the correct action for
balance call
02-DEC-2002 asasthan 115.64 nocopy changes for gscc comp
08-NOV-2002 asasthan 115.63 2589239 Suppressed effective_date index in
archive_data procedure for
pay_payroll_actions in
c_get_latest_asg cursor
31-OCT-2002 asasthan 115.62 2589239 Suppressed effective_date index of
pay_payroll_actions in
c_get_latest_asg cursor
23-SEP-2002 asasthan 115.61 2590094 Archiving of BOX 12 cursor change
18-SEP-2002 fusman 115.60 Updated re-archiving changes.
17-SEP-2002 asasthan 115.59 Added archiving of W2 Transmitter
13-SEP-2002 fusamn 115.58 Added update if null so that mags
will not be affected.
06-SEP-2002 asasthan 115.57 Moved trace_on within range_code
06-SEP-2002 asasthan 115.56 To correct Employer Rearch print
process.
Changes for 1099 Magnetic rules
to be included in Emp REarch.
06-SEP-2002 asasthan 115.55 Employer Rearch was inserting rows
into ff_archive_items instead of
updating values. l_old_value made
null instead of 'Null'.
Also added more contexts for
Employer Rearch process to
handle
FEDERAL TAX RULES
FED TAX UNIT INFORMATION
04-SEP-2002 asasthan 115.54 Modified local variables l_old_value ,l_rowid_found ,l_fed_state_value to 240 instead of 100 varchar2
29-AUG-2002 fusman 115.53 Added new value in the State Re-archive process.
29-AUG-2002 fusman 115.52 Added a null check for the archived value.
29-AUG-2002 asasthan 115.51 Further changes for 1099 balances
28-AUG-2002 asasthan 115.49 Changed Names of 1099 balances
to Other EE Annuity Contract Amt
and Unrealized Net ER Sec Apprec.
Used plsql table for 1099R
balance feed checking
Reverted to old range code
that uses
hr_soft_coding_keyflex
Balance calls for PR use plsql tab
28-AUG-2002 fusman 115.48 Added changes for employer re-archive process.
27-AUG-2002 asasthan 115.47 Added function get_parameter
27-AUG-2002 asasthan 115.46 Added function get_report_type
so as to suppress the
call for eoy_archive_gre_data
for W2C_PRE_PROCESS.
23-AUG-2002 asasthan 115.45 Added global_variable for report_type
23-AUG-2002 asasthan 115.44 Changed names for 2 1099R balances
22-AUG-2002 asasthan 115.43 Checking for feeds for 1099R GREs
and cached user entities for
1099 and PR balances
GREs.
19-AUG-2002 asasthan 115.42 2491268 Changes for Puerto Rico and 1099R
19-AUG-2002 asasthan 115.41 2245457 Changes to archive W2 BOX 12
information thro' the
package and not thro' the formula.
15-AUG-2002 asasthan 115.40 2200920 Changed Range Cursor to go off
tax_unit_id of
pay_assignment_actions and not
hr_soft_coding_keyflex
2503639 Archiving Territory Balances
with Dimension of PER_GRE_YTD
and not PER_JD_GRE_YTD.
18-JUN-2002 ahanda 115.39 2412644 Correct Hint Syntax.
01-APR-2002 asasthan 115.38 2249870 modified Index Hint addded in
115.36 to use
PAY_ASSIGNMENT_ACTIONS_N51 instead
of PAY_ASSIGNMENT_ACTIONS_N1
22-JAN-2002 jgoswami 115.37 added checkfile command
28-DEC-2001 jgoswami 115.36 2161771 Added Index Hint in exist part of
the sql statement for c_eoy_gre in
eoy_action_creation procedure.
04-DEC-2001 jgoswami 115.35 Added Data related to Puerto Rico
A_MARITAL_STATUS,
A_CON_NATIONAL_IDENTIFIER
30-NOV-2001 jgoswami 115.34 added dbdrv command
09-NOV-2001 jgoswami 115.33 Added archive_type to ff_archive_items
insert for Payroll Action level.
15-OCT-2001 jgoswami 115.32 Added cursor c_get_latest_asg in
eoy_action_creation and eoy_archive_data
for improving performance and removed the
expensive query statement.
Remove code for SQWL and W2.
02-SEP-2001 ssarma 40.57 modified error handling to take care
of exceptions other than no_data_found.
28-AUG-2001 ssarma 40.55 TERRITORY DBI. name change.
28-AUG-2001 ssarma 40.54 TERRITORY.DBIs.should include JD
as a context.
27-AUG-2001 ssarma 40.52 TERRITORY_TAXABLE_ALLOWANCE_PER_GRE_YTD
instead of
TERRITORY_TAXABLE_ALLOWANCES_PER_GRE_YTD
23-AUG-2001 djoshi 40.49 removed comment as per sanjay
22-AUG-2001 ssarma 40.48 Revamp of create_archive,
eoy_archive_gre_data and
eoy_archive_data procedures
for employer level re-archive.
Tables used instead of variables
for user_entity_id and value in
create_archive.
14-aug-2001 djoshi 40.47 Changed the Database item name
A_TERRITORY_TAXABLE_RETIREMENT_CONTRIBUTION_PER_GRE_YTD to
A_TERRITORY_RETIREMENT_CONTRIB_PER_GRE_YTD
14-AUG-2001 SSarma 40.46 EOY 2001: Changes for security.
per_all_assignments_f instead of
per_assignmentes_f.
New items archived for Employer.
Legislation code checks for
ff_user_entities join.
Specific archiving for Puerto Rico.
03-Aug-2000 ssarma 40.43 EOY 2000: Changes to city, county cursors
Checks for formula compilation.
Check to see if jurisdiction has been
archived - city, county, state.
Change to eoy action creation cursor.
Change to select which gets latest assignment
action.
Filter for selecting employees bases on 5
balances.
20-JAN-2000 ahanda 40.42 Changed the c_eoy_gre cursor
to go of the per_assignments_f
as a driving table instead of
pay_payroll_actions.
12-dec-1999 ahanda 40.41 Added check in c_get_county and
c_get_state cursor to bypass the
picking up of user defined city tax
records.
10-dec-1999 achauhan 40.40 In c_get_city cursor added a check
to bypass the picking up of user
defined city tax records.Since we do
not withhold taxes for user defined
cities, we do not need to archive them.
27-oct-1999 djoshi 40.39 Modified the file to have the
fed_informaiton_context = '401K LIMITS'
added to the A_SS_EE_wage_BASE and
A_SS_EE_WAGE_RATE.
25-oct-1999 djoshi 40.37 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 40.33 While archiving the employer data
add the context of pay_payroll_actions
to ff_archive_item_contexts.
11-aug-1999 achauhan 40.32 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 40.31 In the archive_data routine,
removed the use of payroll_action_id
>= 0.
04-Aug-1999 VMehta 40.30 Changed eoy_archive_data to improve
performance.
02-Jun-1999 meshah 40.25 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 40.24 Added nvl while checking for l_1099R_ind
to correct the Louisiana quality jobs program
tape processing.
26-jan-1999 VMehta 40.23 Modified function report_person_on_tape to
return false for all states except California
and Massachusetts.
24-Jan-1999 VMehta 40.22 805012 Added function report_person_on_tape to perform
check for retirees having SIT w/h in california.
06-Jan-1999 MReid 40.21 Changed c_eoy_gre cursor to disable
business_group_id index on ppa side
30-dec-1998 vmehta 40.20 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 40.19 Corrected the cursor in action creation to get the
tax_unit_name from pay_assignment_actions.
21-DEC-1998 achauhan 40.18 Changed the cursor in action creation to get the
assignments from the pay_assignment_actions table.
08-DEC-1998 vmehta 40.17 Removed grouping by on assignment_id while creating
assignment_ids
08-DEC-1998 nbristow 40.16 Updated the c_state cursor to use
an exists rather than a join.
07-DEC-1998 nbristow 40.15 Resolved some issues introduced by
40.13.
04-DEC-1998 vmehta 40.14 750802 Changed the cursors/logic to
pick up people who live in
California for the California SQWL.
29-NOV-1998 nbristow 40.13 Changes to the SQWL code,
now using pay_us_asg_reporting.
25-Sep-1998 vmehta 40.5 Changed the range cursor and
the assignment_action creation
cursors to support Louisiana
Quality Jobs Program Reporting.
08-aug-1998 achauhan 40.2 Added the routines for eoy -
Year End Pre-Process
18-MAY-1998 NBRISTOW 40.1 sqwl_range cursor now checks
the tax_unit_id etc.
06-MAY-1998 NBRISTOW 40.0 Created.
27-OCT-1999 RPOTNURU 110.16 Bug fix 976472
*/
eoy_gre_range varchar2(4000);
select 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';
select
to_number(pay_us_payroll_utils.get_parameter('TRANSFER_GRE',
ppa.legislative_parameters)),
to_number(pay_us_payroll_utils.get_parameter('PER_ID',ppa.legislative_parameters)),
pay_us_payroll_utils.get_parameter('SSN',ppa.legislative_parameters),
to_number(pay_us_payroll_utils.get_parameter('ASG_SET',ppa.legislative_parameters)),
to_number(pay_us_payroll_utils.get_parameter('YEAR',ppa.legislative_parameters)),
effective_date,
start_date,
business_group_id,
creation_date
from pay_payroll_actions ppa
where ppa.payroll_action_id = cp_payroll_action_id;
select report_type
from pay_payroll_actions ppa
where ppa.payroll_action_id = p_payroll_action_id;
select 'Y'
from hr_organization_information
where organization_id = cp_tax_unit_id
and org_information16 = 'P'
and org_information_context = 'W2 Reporting Rules';
select pbt.balance_name,pdb.defined_balance_id,fue.user_entity_name
from ff_user_entities fue,
pay_defined_balances pdb,
pay_balance_dimensions pbd,
pay_balance_types pbt
where pbt.balance_name in (
'Territory Pension Annuity',
'Territory Reimb Expenses',
'Territory Taxable Comm',
'Territory Taxable Allow',
'Territory Taxable TIPS',
'Territory Retire Contrib'
)
and pbd.database_item_suffix= '_PER_GRE_YTD'
and pbt.balance_type_id = pdb.balance_type_id
and pbd.balance_dimension_id = pdb.balance_dimension_id
and fue.creator_id = pdb.defined_balance_id
and fue.creator_type = 'B'
and ((pbt.legislation_code = 'US' and
pbt.business_group_id is null)
or (pbt.legislation_code is null and
pbt.business_group_id is not null))
and ((pbd.legislation_code ='US' and
pbd.business_group_id is null)
or (pbd.legislation_code is null and
pbd.business_group_id is not null)) ;
select fue.user_entity_id
from ff_user_entities fue
where fue.user_entity_name = cp_live_database_item
and ((fue.legislation_code = 'US' and
fue.business_group_id is null)
or (fue.legislation_code is null and
fue.business_group_id is not null)) ;
pay_us_archive.ltr_pr_balances.delete;
select hoi.org_information2
from hr_organization_information hoi
where hoi.organization_id = cp_tax_unit_id
and hoi.org_information_context = '1099R Magnetic Report Rules';
select 'Y' from pay_balance_types pbt
where pbt.balance_name = cp_balance_name
and((pbt.legislation_code = 'US' and
pbt.business_group_id is null)
or(pbt.legislation_code is null and
pbt.business_group_id is not null))
and exists (
select balance_feed_id from pay_balance_feeds_f feed
where feed.balance_type_id = pbt.balance_type_id
and((feed.legislation_code = 'US' and
feed.business_group_id is null)
or(feed.legislation_code is null and
feed.business_group_id is not null))
);
select pbt.balance_name,pdb.defined_balance_id,fue.user_entity_name
from ff_user_entities fue,
pay_defined_balances pdb,
pay_balance_dimensions pbd,
pay_balance_types pbt
where pbt.balance_name in (
'Capital Gain',
'EE Contributions Or Premiums',
'Other EE Annuity Contract Amt',
'Total EE Contributions',
'Unrealized Net ER Sec Apprec'
)
and pbd.database_item_suffix= '_PER_GRE_YTD'
and pbt.balance_type_id = pdb.balance_type_id
and pbd.balance_dimension_id = pdb.balance_dimension_id
and fue.creator_id = pdb.defined_balance_id
and fue.creator_type = 'B'
and ((pbt.legislation_code = 'US' and
pbt.business_group_id is null)
or (pbt.legislation_code is null and
pbt.business_group_id is not null))
and ((pbd.legislation_code ='US' and
pbd.business_group_id is null)
or (pbd.legislation_code is null and
pbd.business_group_id is not null)) ;
select fue.user_entity_id
from ff_user_entities fue
where fue.user_entity_name = cp_live_database_item
and ((fue.legislation_code = 'US' and
fue.business_group_id is null)
or (fue.legislation_code is null and
fue.business_group_id is not null)) ;
pay_us_archive.ltr_1099_bal.delete;
select meaning
from fnd_common_lookups
where application_id = 801
and lookup_type = 'W2 BOX 12'
and enabled_flag = 'Y'
*/
select meaning
from fnd_lookup_values flv,
fnd_lookup_types flt
where flv.lookup_type = flt.lookup_type
and application_id = 801
and flt.lookup_type = 'W2 BOX 12'
and enabled_flag = 'Y'
and language = 'US';
select pdb.defined_balance_id,fue.user_entity_name
from ff_user_entities fue,
pay_defined_balances pdb,
pay_balance_dimensions pbd,
pay_balance_types pbt
where pbt.balance_name = cp_balance_name
and pbd.database_item_suffix= cp_balance_dimension
and pbt.balance_type_id = pdb.balance_type_id
and pbd.balance_dimension_id = pdb.balance_dimension_id
and fue.creator_id = pdb.defined_balance_id
and fue.creator_type = 'B'
and ((pbt.legislation_code = 'US' and
pbt.business_group_id is null)
or (pbt.legislation_code is null and
pbt.business_group_id = cp_business_group_id))
and ((pbd.legislation_code ='US' and
pbd.business_group_id is null)
or (pbd.legislation_code is null and
pbd.business_group_id = cp_business_group_id)) ;
select fue.user_entity_id
from ff_user_entities fue
where fue.user_entity_name = cp_live_database_item
and ((fue.legislation_code = 'US' and
fue.business_group_id is null)
or (fue.legislation_code is null and
fue.business_group_id is not null)) ;
pay_us_archive.ltr_pre_tax_bal.delete;
/* Variables used to hold the select columns from the SQL statement.*/
l_person_id number;
SELECT DISTINCT
ASG.person_id person_id
FROM per_all_assignments_f ASG,
pay_all_payrolls_f PPY
WHERE exists
(select /*+ INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
'x'
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.effective_date between cp_period_start
and cp_period_end
and ppa.action_type in ('R','Q','V','B','I')
and ppa.action_status = 'C'
and ppa.business_group_id + 0 = cp_business_group_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.tax_unit_id = cp_tax_unit_id
and paa.action_status = 'C'
and paa.assignment_id = ASG.assignment_id
and ppa.business_group_id = ASG.business_group_id +0
and ppa.effective_date between ASG.effective_start_date
and ASG.effective_end_date)
AND ASG.person_id between cp_start_person_id and cp_end_person_id
AND ASG.assignment_type = 'E'
AND PPY.payroll_id = ASG.payroll_id;
select DISTINCT
asg.person_id person_id
from pay_population_ranges ppr,
per_all_assignments_f asg,
pay_all_payrolls_f ppy
where exists
(select /*+ INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
'x'
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.effective_date between cp_period_start
and cp_period_end
and ppa.action_type in ('R','Q','V','B','I')
and ppa.action_status = 'C'
and ppa.business_group_id + 0 = cp_business_group_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.tax_unit_id = cp_tax_unit_id
and paa.action_status = 'C'
and paa.assignment_id = asg.assignment_id
and ppa.business_group_id = asg.business_group_id +0
and ppa.effective_date between asg.effective_start_date
and asg.effective_end_date)
and asg.person_id = ppr.person_id
and ppr.payroll_Action_id = cp_payroll_Action_id
and ppr.chunk_number = cp_chunk_number
and asg.assignment_type = 'E'
and ppy.payroll_id = asg.payroll_id;
SELECT assignment_id
from per_all_assignments_f paf
where person_id = cp_person_id
and primary_flag = 'Y'
and assignment_type = 'E'
and paf.effective_start_date = (select max(paf2.effective_start_date)
from per_all_assignments_f paf2
where paf2.primary_flag = 'Y'
and paf2.assignment_type = 'E'
and paf2.effective_start_date <= cp_period_end
and paf2.effective_end_date >= cp_period_start
and paf2.person_id = paf.person_id
) /* Bug 4163949 - Added above sub query */
ORDER BY assignment_id desc;
select fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')
||lpad(paa.assignment_action_id,15,'0')),16))
from pay_assignment_actions paa,
per_all_assignments_f paf,
pay_payroll_actions ppa,
pay_action_classifications pac
where paf.person_id = cp_person_id
and paa.assignment_id = paf.assignment_id
and paa.tax_unit_id = cp_tax_unit_id
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 +0 between paf.effective_start_date
and paf.effective_end_date
and ppa.effective_date +0 between cp_period_start
and cp_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));
select effective_date,
business_group_id,
to_number(substr(legislative_parameters,
instr(legislative_parameters,'TRANSFER_GRE=')
+ length('TRANSFER_GRE=')))
into l_effective_date,
l_bus_group_id,
l_eoy_tax_unit_id
from pay_payroll_actions
where payroll_action_id = pactid;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
/* Insert into pay_assignment_actions. */
hr_utility.trace('creating asg action');
/* Update the serial number column with the person id
so that the mag routine and the W2 view will not have
to do an additional checking against the assignment
table */
hr_utility.trace('updating asg action');
update pay_assignment_actions aa
set aa.serial_number = to_char(l_person_id)
where aa.assignment_action_id = lockingactid;
select fdi.user_entity_id
into l_user_entity_id
from ff_database_items fdi,
ff_user_entities fue
where fdi.user_name = p_dbi_name
and fue.user_entity_id = fdi.user_entity_id
and fue.legislation_code = 'US';
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 rowid,fai.value into l_rowid_found,l_old_value
from ff_archive_items fai
where user_entity_id = p_user_entity_id(j)
and context1 = p_context1
and exists (select 'x' from ff_archive_item_contexts faic
where fai.archive_item_id = faic.archive_item_id
and faic.context_id = l_tax_context_id
and faic.context = l_tuid );
hr_utility.trace('B4 update of value ');
update ff_archive_items
set value = p_value(j)
where rowid = l_rowid_found;
hr_utility.trace('In others error for update -200 ');
/* calling the print procedure only if we have not null update */
pay_us_er_rearch.print_er_rearch_data( p_user_entity_id(j),
l_fed_state_value,
l_old_value,
l_new_value);
/* here requirement is that update only if null */
IF (l_old_value is NULL
AND p_value(j) is not NULL
AND (not l_rearch) ) THEN
BEGIN
update ff_archive_items
set value = p_value(j)
where rowid = l_rowid_found;
hr_utility.trace('In others error for update -210 ');
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(j),
p_context1,
p_value(j),
l_archive_type);
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));
select rowid,fai.value into l_rowid_found,l_old_value
from ff_archive_items fai
where user_entity_id = p_user_entity_id(j)
and context1 = p_context1
and exists (select 'x' from ff_archive_item_contexts faic
where fai.archive_item_id = faic.archive_item_id
and faic.context_id = l_tax_context_id
and faic.context = l_tuid )
and exists (select 'x' from ff_archive_item_contexts faic
where fai.archive_item_id = faic.archive_item_id
and faic.context_id = l_jursd_context_id
and faic.context = l_jd );
update ff_archive_items fai
set value = p_value(j)
where rowid = l_rowid_found;
update ff_archive_items fai
set value = p_value(j)
where rowid = l_rowid_found;
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(j),
p_context1,
p_value(j),
l_archive_type);
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));
insert into ff_archive_items
(ARCHIVE_ITEM_ID,
USER_ENTITY_ID,
CONTEXT1,
VALUE)
values
(ff_archive_items_s.nextval,
p_user_entity_id(j),
p_context1,
p_value(j));
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));
select state_code
from pay_us_states pus,
hr_organization_information hoi
where hoi.organization_id = p_tax_unit_id
and hoi.org_information_context || '' = 'State Tax Rules'
and pus.state_abbrev = hoi.org_information1
and pus.state_code = decode(p_state_code,'ALL',pus.state_code,p_state_code);
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 ss_ee_wage_limit,
ss_ee_rate
into l_value1,l_value2
from pay_us_federal_tax_info_f puftif,
pay_payroll_actions ppa
where ppa.payroll_action_id = p_payroll_action_id
and ppa.effective_date between puftif.effective_start_date and effective_end_date
and puftif.fed_information_category = '401K LIMITS';
l_user_entity_id_tab.delete;
l_value_tab.delete;
l_seq_tab.delete;
l_context_id_tab.delete;
l_context_val_tab.delete;
select hrl.country,
substr(hou.name,1,240),
substr(hoi.org_information1,1,240)
into l_value1,
l_value2,
l_value3
from hr_locations hrl,
hr_all_organization_units hou,
hr_organization_information hoi
where hou.organization_id = p_tax_unit_id
and hoi.organization_id = hou.organization_id
and hoi.org_information_context||'' = 'Employer Identification'
and hrl.location_id = hou.location_id;
l_user_entity_id_tab.delete;
l_value_tab.delete;
l_seq_tab.delete;
l_context_id_tab.delete;
l_context_val_tab.delete;
select substr(hoi.org_information2,1,240),
substr(hoi.org_information1,1,240)
into l_value1,
l_value2
from hr_organization_information hoi
where hoi.organization_id = p_tax_unit_id
and hoi.org_information_context || '' = '1099R Magnetic Report Rules';
l_user_entity_id_tab.delete;
l_value_tab.delete;
l_seq_tab.delete;
l_context_id_tab.delete;
l_context_val_tab.delete;
select substr(hoi2.org_information3,1,240),
substr(hoi2.org_information4,1,240)
into l_value1, l_value2
from hr_organization_information hoi2,
hr_organization_information hoi
where hoi.organization_id = p_tax_unit_id
and hoi.org_information_context||'' = '1099R Magnetic Report Rules'
and hoi.org_information_context = hoi2.org_information_context
and hoi.org_information2 = hoi2.org_information2
and hoi2.org_information1 = 'Y';
l_user_entity_id_tab.delete;
l_value_tab.delete;
l_seq_tab.delete;
l_context_id_tab.delete;
l_context_val_tab.delete;
select
--hoi.org_information6 value1,
hoi.org_information8 value2,
hoi.org_information9 value3,
hoi.org_information10 value4,
hoi.org_information11 value5,
hoi.org_information12 value6,
hoi.org_information13 value7,
hoi.org_information14 value8,
hoi.org_information15 value9,
hoi.org_information16 value10,
--hoi.org_information2 value11,
--hoi.org_information3 value12,
--hoi.org_information4 value13,
--hoi.org_information5 value14,
--hoi.org_information7 value15, /* Job Development Fee (AL) */
hoi.org_information1 value16
into
--l_value1,
l_value2,
l_value3,
l_value4,
l_value5,
l_value6,
l_value7,
l_value8,
l_value9,
l_value10,
--l_value11,
--l_value12,
--l_value13,
--l_value14,
--l_value15, /* Job Development Fee (AL) */
l_value16
from hr_organization_information hoi
where hoi.organization_id = p_tax_unit_id
and hoi.org_information_context || '' = 'W2 Reporting Rules';
l_user_entity_id_tab.delete;
l_value_tab.delete;
l_seq_tab.delete;
l_context_id_tab.delete;
l_context_val_tab.delete;
select hoi.org_information4 value1,
hoi.org_information8 value2
into l_value1,
l_value2
from hr_organization_information hoi
where hoi.organization_id = p_tax_unit_id
and hoi.org_information_context || '' = 'Federal Tax Rules';
l_user_entity_id_tab.delete;
l_value_tab.delete;
l_seq_tab.delete;
l_context_id_tab.delete;
l_context_val_tab.delete;
l_user_entity_id_tab.delete;
l_value_tab.delete;
l_seq_tab.delete;
l_context_id_tab.delete;
l_context_val_tab.delete;
select to_char(rules.fips_code) value1,
ltrim(rtrim(target.org_information3)) value2,
ltrim(rtrim(target.org_information2)) value3
into l_value1,
l_value2,
l_value3
from pay_state_rules rules,
pay_us_states pus,
hr_organization_information target
where substr(rules.jurisdiction_code, 1, 2) = c_state.state_code
and target.organization_id = p_tax_unit_id
and target.org_information_context || '' = 'State Tax Rules'
and target.org_information1 = pus.state_abbrev
and pus.state_code = c_state.state_code;
l_user_entity_id_tab.delete;
l_value_tab.delete;
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);
select min(paa.chunk_number)
from pay_assignment_actions paa
where paa.payroll_action_id = p_payroll_action_id;
select ff.formula_name
into l_formula_name
from ff_formulas_f ff,
ff_compiled_info_f fci
where ff.formula_name = 'US_YEP_BOX_12'
and fci.formula_id = ff.formula_id;
select ff.formula_name
into l_formula_name
from ff_formulas_f ff,
ff_compiled_info_f fci
where ff.formula_name = 'US_YEP_BOX_14'
and fci.formula_id = ff.formula_id;
select ff.formula_name
into l_formula_name
from ff_formulas_f ff,
ff_compiled_info_f fci
where ff.formula_name = 'US_YEP_FEDERAL'
and fci.formula_id = ff.formula_id;
select ff.formula_name
into l_formula_name
from ff_formulas_f ff,
ff_compiled_info_f fci
where ff.formula_name = 'US_YEP_LOCALITY'
and fci.formula_id = ff.formula_id;
select ff.formula_name
into l_formula_name
from ff_formulas_f ff,
ff_compiled_info_f fci
where ff.formula_name = 'US_YEP_STATE'
and fci.formula_id = ff.formula_id;
select to_number(substr(legislative_parameters,
instr(legislative_parameters,'TRANSFER_GRE=')+ length('TRANSFER_GRE='))),
business_group_id
into l_tax_unit_id,
ln_business_group_id
from pay_payroll_actions
where payroll_action_id = p_payroll_action_id;
select context_id
into g_jursd_context_id
from ff_contexts
where context_name = 'JURISDICTION_CODE';
select context_id
into g_tax_unit_context_id
from ff_contexts
where context_name = 'TAX_UNIT_ID';
/* Name : delete_ff_archive
Purpose : Delete from ff_archive and context
Arguments :
Notes :
*/
PROCEDURE delete_ff_archive (p_context number,
p_archive_name IN VARCHAR2) IS
CURSOR c_get_ff_arch IS
select fai.archive_item_id
from ff_archive_items fai,
ff_user_entities fue
where context1 =p_context
and fai.user_entity_id = fue.user_entity_id
and user_entity_name = p_archive_name ;
delete from ff_archive_item_contexts
where archive_item_id = l_archive_itemid;
delete from ff_archive_items
where archive_item_id = l_archive_itemid;
delete_ff_archive(p_assactid,'A_ADD_ARCHIVE');
select distinct pcty.jurisdiction_code pcty
from pay_us_city_tax_info_f cti,
pay_us_emp_city_tax_rules_f pcty,
per_all_assignments_f paf1
where paf1.person_id = l_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 substr(pcty.city_code,1,1) <> 'U'
and pcty.jurisdiction_code = cti.jurisdiction_code
and cti.city_tax = 'Y'
and cti.effective_start_date <= l_year_end
and cti.effective_end_date >= l_year_start;
select distinct pcnt.jurisdiction_code
from pay_us_emp_county_tax_rules_f pcnt,
per_all_assignments_f paf1
where paf1.person_id = l_person_id
and paf1.effective_end_date >= l_year_start
and paf1.effective_start_date <= l_year_end
and pcnt.assignment_id = paf1.assignment_id
and pcnt.effective_start_date <= l_year_end
and pcnt.effective_end_date >= l_year_start;
select distinct pcnt.jurisdiction_code
from pay_us_county_tax_info_f cnti,
pay_us_emp_county_tax_rules_f pcnt,
per_all_assignments_f paf1
where paf1.person_id = l_person_id
and paf1.effective_end_date >= l_year_start
and paf1.effective_start_date <= l_year_end
and pcnt.assignment_id = paf1.assignment_id
and pcnt.effective_start_date <= l_year_end
and pcnt.effective_end_date >= l_year_start
and pcnt.jurisdiction_code = cnti.jurisdiction_code
and cnti.county_tax = 'Y'
and cnti.effective_start_date <= l_year_end
and cnti.effective_end_date >= l_year_start;
select distinct pst.jurisdiction_code
from pay_us_state_tax_info_f sti,
pay_us_emp_state_tax_rules_f pst,
per_all_assignments_f paf1
where paf1.person_id = l_person_id
and paf1.effective_end_date >= l_year_start
and paf1.effective_start_date <= l_year_end
and paf1.assignment_id = pst.assignment_id
and pst.effective_start_date <= l_year_end
and pst.effective_end_date >= l_year_start
and sti.state_code = pst.state_code
and sti.sit_exists = 'Y'
and sti.effective_start_date <= l_year_end
and sti.effective_end_date >= l_year_start;
select distinct pst.jurisdiction_code
from pay_us_emp_state_tax_rules_f pst,
per_all_assignments_f paf1
where paf1.person_id = l_person_id
and paf1.effective_end_date >= l_year_start
and paf1.effective_start_date <= l_year_end
and paf1.assignment_id = pst.assignment_id
and pst.effective_start_date <= l_year_end
and pst.effective_end_date >= l_year_start;
select distinct pcnt.state_code || '-'|| pcnt.school_district_code,
pcnt.county_code
from pay_us_emp_county_tax_rules_f pcnt,
per_all_assignments_f paf1
where paf1.person_id = l_person_id
and paf1.effective_end_date >= l_year_start
and paf1.effective_start_date <= l_year_end
and paf1.assignment_id = pcnt.assignment_id
and pcnt.school_district_code is not null
and pcnt.effective_start_date <= l_year_end
and pcnt.effective_end_date >= l_year_start;
select distinct pcty.state_code || '-'|| pcty.school_district_code,
county_code,
city_code
from pay_us_emp_city_tax_rules_f pcty,
per_all_assignments_f paf1
where paf1.person_id = l_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.school_district_code is not null
and pcty.effective_start_date <= l_year_end
and pcty.effective_end_date >= l_year_start;
select fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')
||lpad(paa.assignment_action_id,15,'0')),16))
from pay_assignment_actions paa,
per_all_assignments_f paf,
pay_payroll_actions ppa,
pay_action_classifications pac
where paf.person_id = p_person_id
and paa.assignment_id = paf.assignment_id
and paa.tax_unit_id = l_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 +0 between paf.effective_start_date
and paf.effective_end_date
and ppa.effective_date +0 between l_year_start and
l_year_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));
select aei_information1,
aei_information2,
aei_information3,
aei_information4
from per_assignment_extra_info
where information_type = 'PAY_US_PENSION_REPORTING'
and assignment_id = cp_assignment_id;
select pei_information2
from per_people_extra_info target
where person_id = cp_person_id
and target.pei_information1 = cp_tax_unit_id
and information_type= 'PAY_US_PENSION_REPORTING';
select pei_information3
from per_people_extra_info target
where person_id = cp_person_id
and target.pei_information1 = cp_tax_unit_id
and information_type= 'PAY_US_PENSION_REPORTING';
select hsck.segment19
from per_all_assignments_f paf , hr_soft_coding_keyflex hsck
where assignment_id = cp_assignment_id and
paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id and
hsck.segment1 = to_char(cp_tax_unit_id) and
paf.effective_end_date = (select max(effective_end_date)
from per_all_assignments_f paf1 , hr_soft_coding_keyflex hsck1
where paf1.assignment_id = paf.assignment_id and
paf1.soft_coding_keyflex_id = hsck1.soft_coding_keyflex_id and
hsck1.segment1 = hsck.segment1);
select aei_information1
from per_assignment_extra_info
where information_type = 'PAY_US_DISABILITY_PLAN_INFO'
and assignment_id = cp_assignment_id;
SELECT TARGET.result_value
FROM pay_assignment_actions BAL_ASSACT
, pay_payroll_actions BACT
, per_all_assignments_f ASS
, pay_assignment_actions ASSACT
, pay_payroll_actions PACT
, pay_run_results RR
, pay_run_result_values TARGET
, pay_input_values_f PIV
, pay_element_entries_f peef
, pay_element_types_f petf
where BAL_ASSACT.assignment_action_id = cp_asg_act_id
and ASS.assignment_id = cp_asg_id
and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
and ASSACT.assignment_id = ASS.assignment_id
and ASSACT.payroll_action_id = PACT.payroll_action_id
and RR.assignment_action_id = ASSACT.assignment_action_id
and TARGET.run_result_id = RR.run_result_id
and TARGET.input_value_id = PIV.input_value_id
and PIV.NAME = 'Year of Prior Deferral'
and RR.element_entry_id = peef.element_entry_id
and RR.entry_type = 'E'
and peef.element_type_id = petf.element_type_id
and petf.element_information_category = 'US_VOLUNTARY DEDUCTIONS'
and petf.element_information1 = cp_ele_info1
and PACT.effective_date between PIV.effective_start_date
and PIV.effective_end_date
and PACT.effective_date between peef.effective_start_date
and peef.effective_end_date
and PACT.effective_date between petf.effective_start_date
and petf.effective_end_date
and RR.status in ('P','PA')
and ASSACT.assignment_id = ASS.assignment_id
and ASS.person_id = (select person_id from per_all_assignments_f START_ASS
where START_ASS.assignment_id = BAL_ASSACT.assignment_id
and rownum = 1)
and PACT.effective_date between ASS.effective_start_date
and ASS.effective_end_date;
SELECT aa.assignment_id,
pay_magtape_generic.date_earned (p_effective_date,aa.assignment_id),
aa.tax_unit_id,
aa.chunk_number,
aa.payroll_action_id,
to_number(aa.serial_number)
into l_asgid,
l_date_earned,
l_taxunitid,
l_chunk,
l_payroll_action_id,
l_person_id
FROM pay_assignment_actions aa
WHERE aa.assignment_action_id = p_assactid;
select context_id
into l_jursd_context_id
from ff_contexts
where context_name = 'JURISDICTION_CODE';
select context_id
into l_tax_unit_context_id
from ff_contexts
where context_name = 'TAX_UNIT_ID';
/* Insert rows into ff_archive_items and ff_archive_item_contexts
for the city, county and state */
l_step := 17;
select city_name
into l_city_name
from pay_us_city_names pcn
where pcn.state_code = substr(l_jurisdiction,1,2)
and pcn.county_code = substr(l_jurisdiction,4,3)
and pcn.city_code = substr(l_jurisdiction,8,4)
and pcn.primary_flag = 'Y';
l_balance_feed_tab.delete;
l_defined_balance_id_tab.delete;
l_user_entity_id_tab.delete;
l_value_tab.delete;
l_seq_tab.delete;
l_context_id_tab.delete;
l_context_val_tab.delete;
select county_name
into l_county_name
from pay_us_counties puc
where puc.state_code = substr(l_jurisdiction,1,2)
and puc.county_code = substr(l_jurisdiction,4,3);
l_user_entity_id_tab.delete;
l_value_tab.delete;
l_seq_tab.delete;
l_context_id_tab.delete;
l_context_val_tab.delete;
select state_abbrev
into l_state_abbrev
from pay_us_states pus
where pus.state_code = substr(l_jurisdiction,1,2);
l_user_entity_id_tab.delete;
l_value_tab.delete;
l_seq_tab.delete;
l_context_id_tab.delete;
l_context_val_tab.delete;
select school_dst_name
into l_cnt_sd_name
from pay_us_county_school_dsts pcs
where pcs.state_code = substr(l_jurisdiction,1,2)
and pcs.county_code = l_county_code
and school_dst_code = substr(l_jurisdiction,4,5);
l_user_entity_id_tab.delete;
l_value_tab.delete;
l_seq_tab.delete;
l_context_id_tab.delete;
l_context_val_tab.delete;
select school_dst_name
into l_cty_sd_name
from pay_us_city_school_dsts pcs
where pcs.state_code = substr(l_jurisdiction,1,2)
and pcs.county_code = l_county_code
and pcs.city_code = l_city_code
and school_dst_code = substr(l_jurisdiction,4,5);
l_user_entity_id_tab.delete;
l_value_tab.delete;
l_seq_tab.delete;
l_context_id_tab.delete;
l_context_val_tab.delete;
l_jd_done_tab.delete;
l_user_entity_id_tab.delete;
l_value_tab.delete;
l_seq_tab.delete;
l_context_id_tab.delete;
l_context_val_tab.delete;
l_defined_balance_id_tab.delete;
l_balance_feed_tab.delete;
select ppf.marital_status
into l_marital_status
from per_people_f ppf
where ppf.person_id = l_person_id
and l_date_earned between ppf.effective_start_date
and ppf.effective_end_date;
select ppf.national_identifier
into l_con_national_identifier
from per_people_f ppf,
per_contact_relationships ctr
where ctr.person_id = ppf.person_id
and ctr.contact_person_id = l_person_id
/* and ctr.personal_flag = 'Y'*/
and ctr.contact_type = 'S'
and l_date_earned between ppf.effective_start_date
and ppf.effective_end_date
and ctr.date_start =
(select max(ctr1.date_start)
from per_contact_relationships ctr1
where ctr1.person_id = l_person_id
and ctr1.date_start <= l_year_end
and nvl(ctr1.date_end,
fnd_date.canonical_to_date('4712/12/31 00:00:00'))
>= l_year_start);
l_user_entity_id_tab.delete;
l_defined_balance_id_tab.delete;
l_balance_feed_tab.delete;
l_value_tab.delete;
l_seq_tab.delete;
l_context_id_tab.delete;
l_context_val_tab.delete;
hr_utility.trace('Deleted plsql tables ');
l_user_entity_id_tab.delete;
l_user_entity_tab.delete;
l_value_tab.delete;
l_user_entity_id_tab.delete;
l_value_tab.delete;
l_seq_tab.delete;
l_context_id_tab.delete;
l_context_val_tab.delete;
l_user_entity_id_tab.delete;
l_value_tab.delete;
l_seq_tab.delete;
l_context_id_tab.delete;
l_context_val_tab.delete;
l_user_entity_id_tab.delete;
l_value_tab.delete;
l_seq_tab.delete;
l_context_id_tab.delete;
l_context_val_tab.delete;
l_user_entity_id_tab.delete;
l_value_tab.delete;
l_seq_tab.delete;
l_context_id_tab.delete;
l_context_val_tab.delete;
l_jd_done_tab.delete;
l_jd_name_done_tab.delete;
Purpose : This returns the select statement that is used to created the
range rows for the Year End Pre-Process.
Arguments :
Notes :
*/
PROCEDURE eoy_range_cursor (pactid in number, sqlstr out nocopy varchar2) is
l_eoy_tax_unit_id number;
eoy_gre_range := 'SELECT distinct ASG.person_id
FROM per_all_assignments_f ASG,
pay_us_asg_reporting puar,
pay_payroll_actions PPA
WHERE PPA.payroll_action_id = :payroll_action_id
AND puar.tax_unit_id = substr(legislative_parameters,
instr(legislative_parameters,''TRANSFER_GRE='')+ length(''TRANSFER_GRE=''))
AND asg.assignment_id = puar.assignment_id
AND ASG.business_group_id + 0 = PPA.business_group_id
AND ASG.assignment_type = ''E''
AND ASG.effective_start_date <= PPA.effective_date
AND ASG.effective_end_date >= PPA.start_date
AND ASG.payroll_id is not null
ORDER BY ASG.person_id';
select to_number(substr(legislative_parameters,INSTR(legislative_parameters,
'TRANSFER_GRE=')+LENGTH('TRANSFER_GRE='),15)), business_group_id, start_date
into l_eoy_tax_unit_id, l_eoy_bg_id, l_start_date
from pay_payroll_actions
where payroll_action_id = pactid;
select hou.name into l_gre_name
from hr_organization_information hoi,
hr_organization_units hou
where hoi.org_information_context = 'CLASS'
and hoi.org_information1 = 'HR_LEGAL'
and hoi.organization_id = hou.organization_id
and hou.business_group_id = l_eoy_bg_id
and hou.organization_id = l_eoy_tax_unit_id;
select 'X' into l_processed
from pay_payroll_actions ppa1
where ppa1.report_type = 'YREND'
AND ppa1.business_group_id + 0 = l_eoy_bg_id
AND ppa1.start_date = l_start_date
AND ppa1.payroll_action_id <> pactid
AND to_char(l_eoy_tax_unit_id) =
substr(ltrim(rtrim( ppa1.legislative_parameters)),
instr(ppa1.legislative_parameters,'TRANSFER_GRE=')+ length('TRANSFER_GRE='));
l_jd_done_tab.delete;
l_jd_name_done_tab.delete;