[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
update_ytd_withheld for action_info8
and actioninfo9
21-08-2007 sausingh 115.110 Added Cursor get_display_name
to proc update_ytd_withheld
26-JUN-2007 asasthan 115.109 5868672 Head Tax info retrieved from
JIT table.
28-NOV-2006 saikrish 115.108 5683349 Corrected the signature of
pay_get_tax_exists_pkg.
15-NOV-2006 ahanda 115.107 5264527 Changed select for action_creation
code to add hints.
Also, changed sql statement to
use base table instead secure
views.
13-APR-2006 ahanda 115.106 Changed HBR code to use amount
from pay_hours_by_rate_v.
24-MAR-2006 ahanda 115.105 4924263 Changed archive code to not check
if tax exists in a JD for
Bal Adj action types.
27-FEB-2006 ahanda 115.104 5058507 Changed HBR code to store the
pay value as amount as for Retro,
Hours or Rate could be null.
27-FEB-2006 ahanda 115.103 5058507 Changed HBR code to check for
Zero Retro Hours.
12-FEB-2006 ahanda 115.102 5003054 Changed code to archive multiple
retro entries for same element type
03-FEB-2006 ahanda 115.101 5003054 Added logic to retropay and hrsXrate
07-OCT-2005 ahanda 115.100 4642121 Changed the logic to archive element
even if Gross and Net Run are zero
4552807 Added call to process_baladj_elements
to archive baladj elements.
4640155 Changed cursor to get distinct JD
20-JUL-2005 ahanda 115.99 4500097 Added Ordered hint for subquery
28-JUN-2005 ahanda 115.98 4449712 Changed call to get_last_xfr_info
to pass EMPLOYEE DETAILS.
Changed cursor c_get_jd to not
pick up Federal JD.
06-JUN-2005 ahanda 115.97 Changed populate_emp_hours_by_rate
Storing Hours and Rate in canonical
format.
14-MAR-2005 sackumar 115.96 4222032 Change in the Range Cursor removing
use of bind Variable -
:payroll_action_id
17-MAR-2005 ahanda 115.94 4247361 Changed cursor c_payment_info
to add a distinct to ensure
and assignment is returned
only once.
4212744 Changed cursor c_get_baladj_jd
to remove join to
pay_run_result_values and other
tables.
05-NOV-2004 ahanda 115.94 3995766 Archiver not archiving
Balance Adjustment and Reversal
03-NOV-2004 ahanda 115.93 3979501 Added support for RANGE_PERSON_ID
06-OCT-2004 ahanda 115.92 3940380 Added parameter p_xfr_action_id
to get_last_xfr_info
06-AUG-2004 ahanda 115.91 3814488 Added populate_emp_hours_by_rate
to archive all elements returned
by pay_hours_by_rate view.
05-AUG-2004 ahanda 115.90 3814488 Added logic for Hours By Rate
20-JUL-2004 ahanda 115.89 3780771 Changed order by in the action
creation code
23-JUN-2004 ahanda 115.88 3711280 Changed the logic for NR/R
Also, balance call for Medicare ER
and SS ER Liability
14-MAY-2004 rsethupa 115.87 3231253 Added Comments
13-MAY-2004 rsethupa 115.86 3231253 Added code to archive STEIC Advance
balance in the categories
'AC DEDUCTIONS' and 'US STATE'
30-APR-2004 saurgupt 115.85 3561821 Modified the procedure
update_ytd_withheld
to check for current values for
the balances along with the YTD
values before archiving.
16-APR-2004 rsethupa 115.84 3311866 US SS Payslip currency Format Enh.
Changed code to archive currency
data in canonical format for the
action info categories 'AC
DEDUCTIONS'and 'US WITHHOLDINGS'.
12-MAR-2004 rsethupa 115.83 3452149 Modified procedures process_actions
and action_archive_data to
archive Employee and W4 Information
for primary assignment in case
multiple assignments flag is checked
and any assignment other than the
primary is paid.
16-JAN-2004 kvsankar 115.82 3349855 Modified query for performance
enhancement
02-JAN-2004 ahanda 115.81 Changed cursor c_get_baladj_jd
to pick up distinct jurisdictions
04-DEC-2003 vpandya 115.80 Added logic to call
first_time_process
even though the Ext Proc Arch
has already been run for the
current year before.
25-NOV-2003 vpandya 115.79 3280589 Changed action_archive_data:
populating gv_multi_payroll_pymt.
Changed condition for Termi Asg.
Removed create_child_actions proc.
17-Nov-2003 vpandya 115.78 3262057 Changed populate_puv_tax_balances:
Added condition in the cursor
c_get_jd in the second select
clause.
07-Nov-2003 vpandya 115.77 3243503 Changed action_archive_data:
selecting assignment_action_id
in c_payment_info cursor to
get master pp asg act id again in
ln_asg_action_id variable.
06-Nov-2003 vpandya 115.76 3231337 Changed populate_puv_tax_balances:
Removed cursors c_get_sp_rr_jd and
c_get_rr_jd. Added cursor c_get_jd.
Added function check_tax_exists.
05-Nov-2003 vpandya 115.75 3237538 Changed action_archive_data:
added cursor c_all_runs.
31-Oct-2003 vpandya 115.74 3225286 Changed process_actions
Added cursor c_chk_act_type to check
if previous archiver is of balance
adjustement then call first_time..
procedure instead of calling
get_current_elements.
08-Oct-2003 ahanda 115.73 3181365 Changed -
populate_federal_tax_balances
and update_ytd_withheld
18-Sep-2003 vpandya 115.72 Changed range cursor to fix gscc
error on date conversion. Using
fnd_date.date_to_canonical instead
to_char and canonical_to_date
instead of to_date.
10-Sep-2003 ekim 115.71 3119792 Added check for whether the archiver
2880047 is run for a given payroll
in action_archive_data procedure.
This sets variable g_xfr_run_exists.
Added call to
process_additional_elements
Added procedure
change_processing_priority
and called this procedure before
insert_rows_thro_api_process.
Terminated Assignment check:
c_get_term_asg
06-Aug-2003 vpandya 115.70 3050620 Changed action_action_creation to
create assignment action for
zero net pay using view
pay_payment_information.
28-Jul-2003 vpandya 115.69 3053917 Passing parameter
p_ytd_balcall_aaid to
get_personal_information.
13-Jun-2003 ekim 115.68 3005678 Removed call update_federal_values
in between if..else statements
in populate_federal_tax_balances
and left one call at the end of the
procedure.
02-Apr-2003 ekim 115.67 Removed ppa.action_status='C'
for all queries as paa.action_status
='C' is the only required.
28-Mar-2003 ekim 115.66 2875350 Made performance fix on
c_get_rr_jd, c_get_sp_rr_jd,
c_get_baladj_jd
- Added parameter
cp_run_effective_date in cursor.
2874412 Changed c_get_employee_info to
add pre_name_adjunt and suffix.
18-Mar-2003 ekim 115.65 2855261 Changed default processing
priority in update_ytd_withheld
to 10.
Changed processing priority
for all Tax Deductions.
06-Feb-2003 ekim 115.64 2315822 Added additional parameter
p_sepchk_flag,p_assignment_id
in get_xfr_elements procedure call.
31-Jan-2003 ekim 115.63 2752134 Added YTD balance to be archived
for EIC Advance
02-DEC-2002 ahanda 115.62 Changed package to fix GSCC warnings
25-NOV-2002 ahanda 115.61 2658611 Changed update_employee_information
to pass assignment_id.
19-NOV-2002 vpandya 115.60 Calling set_error_message function
of pay_emp_action_arch from all
exceptions to get error message
Remote Procedure Calls(RPC or Sub
program)
17-NOV-2002 ahanda 115.59 Added function get_balance_value
Balance call done only if def bal id
is not null.
01-NOV-2002 ahanda 115.58 Changed error handling.
14-OCT-2002 ahanda 115.57 2500413 Changed calls to update_ytd_withheld
to populate processing_prioirty for
Tax Deductions
2500381 Changed Code to update Employee Name
2562608 Changed range and action creation
cursor to pick up reversals.
15-OCT-2002 tmehra 115.56 Added code to archive the PQP
(Alien) balances.
14-OCT-2002 ahanda 115.55 Changed update_ytd_withheld to
populate processing priority.
23-SEP-2002 ahanda 115.54 2498029 Changed populate_school_tax_balances
2532436 and populate_state_tax_balances
06-SEP-2002 ahanda 115.53 Fixed GSCC Warnings.
17-JUN-2002 ahanda 115.52 2447717 Changed package to populate tax
2365908 deductions if location has changed.
14-MAY-2002 ahanda 115.51 Moved procedures
- get_last_xfr_info
- get_last_pymt_info
to pay_ac_action_arch
24-APR-2002 ahanda 115.50 Changed c_get_rr_jd for performance.
18-MAR-2002 ahanda 115.49 2204512 Changed the way we populate NR/R.
Fixed archiving for Bal Adj for
which Pre Pay flag is checked.
18-FEB-2002 ahanda 115.48 2200748 Changed W4 to archive the Work and
Resident JDs. Changed Adj Bal proc
to pass the bal adj action_id.
14-FEB-2002 ahanda 115.47 2189810 Changed c_time_period to get the
time_period_id from per_time_periods
14-FEB-2002 ahanda 115.46 Changed archinit to check for the
new dimension only if multi asgn
is enabled. This removed dependency
on HRGLOBAL for one off patch.
11-FEB-2002 ahanda 115.45 Changed fetch for cursor
c_get_states_jit in archinit
05-FEB-2002 ahanda 115.44 Changed package for Bal Adjustments.
26-JAN-2002 ahanda 115.43 Added dbdrv commands.
22-JAN-2002 ahanda 115.42 Changed package to take care
of Multi Assignment Processing.
****************************************************************************
25-JAN-2001 asasthan 115.0 Created.
******************************************************************************/
/******************************************************************************
** Package Local Variables
******************************************************************************/
gv_package VARCHAR2(100) := 'pay_us_action_arch';
select effective_date,
start_date,
business_group_id,
to_number(substr(legislative_parameters,
instr(legislative_parameters,
'TRANSFER_CONSOLIDATION_SET_ID=')
+ length('TRANSFER_CONSOLIDATION_SET_ID='))),
to_number(ltrim(rtrim(substr(legislative_parameters,
instr(legislative_parameters,
'TRANSFER_PAYROLL_ID=')
+ length('TRANSFER_PAYROLL_ID='),
(instr(legislative_parameters,
'TRANSFER_CONSOLIDATION_SET_ID=') - 1 )
- (instr(legislative_parameters,
'TRANSFER_PAYROLL_ID=')
+ length('TRANSFER_PAYROLL_ID='))))))
from pay_payroll_actions
where payroll_action_id = cp_payroll_action_id;
select pdb.defined_balance_id
from 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 ((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 ((pdb.legislation_code ='US' and
pdb.business_group_id is null)
or (pdb.legislation_code is null and
pdb.business_group_id = cp_business_group_id));
Purpose : This returns the select statement that is
used to created the range rows for the
Tax Filing (FLS)/Payslip Archiver.
Arguments :
Notes : Calls procedure - get_payroll_action_info
******************************************************************/
PROCEDURE action_range_cursor(
p_payroll_action_id in number
,p_sqlstr out nocopy varchar2)
IS
ld_end_date DATE;
'select distinct paf.person_id
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_assignments_f paf
where ppa.business_group_id = ''' || ln_business_group_id || '''
and ppa.effective_date between fnd_date.canonical_to_date(''' ||
fnd_date.date_to_canonical(ld_start_date) || ''')
and fnd_date.canonical_to_date(''' ||
fnd_date.date_to_canonical(ld_end_date) || ''')
and ppa.action_type in (''U'',''P'',''B'',''V'')
and decode(ppa.action_type,
''B'', nvl(ppa.future_process_mode, ''Y''),
''N'') = ''N''
and ppa.consolidation_set_id = ''' || ln_cons_set_id || '''
and ppa.payroll_id = ''' || ln_payroll_id || '''
and ppa.payroll_action_id = paa.payroll_action_id
and paa.action_status = ''C''
and paa.source_action_id is null
and paf.assignment_id = paa.assignment_id
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date
and not exists
(select /*+ ORDERED */
1
from pay_action_interlocks pai,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
where pai.locked_action_id = paa.assignment_action_id
and paa1.assignment_action_id = pai.locking_action_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.action_type =''X''
and ppa1.report_type = ''XFR_INTERFACE'')
and :payroll_action_id is not null
order by paf.person_id';
select /*+ INDEX(PAF PER_ASSIGNMENTS_F_N12)
INDEX(PPA PAY_PAYROLL_ACTIONS_N50)
INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N51) */
paa.assignment_id,
paa.tax_unit_id,
ppa.effective_date,
ppa.date_earned,
ppa.action_type,
paa.assignment_action_id,
paa.payroll_action_id
from pay_payroll_actions ppa,
pay_assignment_actions paa,
per_assignments_f paf
where paf.person_id between cp_start_person_id
and cp_end_person_id
and paa.assignment_id = paf.assignment_id
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date
and ppa.consolidation_set_id = cp_cons_set_id
and paa.action_status = 'C'
and ppa.payroll_id = cp_payroll_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.business_group_id = cp_business_group_id
and ppa.effective_date between cp_start_date
and cp_end_date
and ppa.action_type in ('U','P','B','V')
and decode(ppa.action_type,
'B', nvl(ppa.future_process_mode, 'Y'),
'N') = 'N'
and paa.source_action_id is null
and not exists
(select 'x'
from pay_action_interlocks pai1,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
where pai1.locked_action_id = paa.assignment_action_id
and paa1.assignment_action_id = pai1.locking_action_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.action_type ='X'
and ppa1.report_type = 'XFR_INTERFACE')
order by 1,2,3,5,6;
select /*+ INDEX(PPR PAY_POPULATION_RANGES_N4)
INDEX(PAF PER_ASSIGNMENTS_F_N12)
INDEX(PPA PAY_PAYROLL_ACTIONS_N50)
INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N51) */
paa.assignment_id,
paa.tax_unit_id,
ppa.effective_date,
ppa.date_earned,
ppa.action_type,
paa.assignment_action_id,
paa.payroll_action_id
from pay_payroll_actions ppa,
pay_assignment_actions paa,
per_assignments_f paf,
pay_population_ranges ppr
where ppr.payroll_action_id = cp_payroll_action_id
and ppr.chunk_number = cp_chunk_number
and paf.person_id = ppr.person_id
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date
and paa.assignment_id = paf.assignment_id
and ppa.consolidation_set_id = cp_cons_set_id
and paa.action_status = 'C'
and ppa.payroll_id = cp_payroll_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.business_group_id = cp_business_group_id
and ppa.effective_date between cp_start_date
and cp_end_date
and ppa.action_type in ('U','P','B','V')
and decode(ppa.action_type,
'B', nvl(ppa.future_process_mode, 'Y'),
'N') = 'N'
and paa.source_action_id is null
and not exists
(select 'x'
from pay_action_interlocks pai1,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
where pai1.locked_action_id = paa.assignment_action_id
and paa1.assignment_action_id = pai1.locking_action_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.action_type ='X'
and ppa1.report_type = 'XFR_INTERFACE')
order by 1,2,3,5,6;
select max(paa.assignment_action_id)
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai
where pai.locking_action_Id = cp_prepayment_action_id
and paa.assignment_action_id = pai.locked_action_id
and paa.source_action_id is null
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('R', 'Q');
select pay_assignment_actions_s.nextval
into ln_xfr_action_id
from dual;
update pay_assignment_actions
set serial_number = lv_serial_number
where assignment_action_id = ln_xfr_action_id;
select ppa.effective_date
from pay_payroll_actions ppa
where ppa.payroll_action_id = cp_payroll_action_id;
select balance_type_id
from pay_balance_types
where balance_name = cp_balance_name
and legislation_code = 'US';
select fdu.form_left_prompt, pbt.balance_type_id
from fnd_descr_flex_col_usage_tl fdu,
pay_balance_types pbt,
fnd_application fa
where fdu.descriptive_flexfield_name = 'Action Information DF'
and fdu.language = 'US'
and pbt.balance_name = fdu.form_left_prompt
and pbt.legislation_code = 'US'
and fdu.descriptive_flex_context_code = cp_action_context
and fdu.form_left_prompt <> 'Resident/Non-Resident Flag'
and fdu.form_left_prompt <> 'Resident Jurisdiction'
and fa.application_id = fdu.application_id
and fa.application_short_name = 'PAY'
order by fdu.descriptive_flex_context_code, fdu.form_left_prompt;
select state_code,
sit_exists,
decode(sui_ee_wage_limit, null, 'N', 'Y'),
decode(sui_er_wage_limit, null, 'N', 'Y'),
decode(sdi_ee_wage_limit, null, 'N', 'Y'),
decode(sdi_er_wage_limit, null, 'N', 'Y')
from pay_us_state_tax_info_f
where cp_effective_date between effective_start_date
and effective_end_date
and sta_information_category = 'State tax limit rate info'
order by 1 ;
select jurisdiction_code,
county_tax,
head_tax,
school_tax
from pay_us_county_tax_info_f
where cp_effective_date between effective_start_date
and effective_end_date
and cnty_information_category = 'County tax status info'
order by 1 ;
select parameter_value
from pay_action_parameters
where parameter_name = 'INIT_PAY_ARCHIVE';
select hours.element_type_id,
hours.element_name,
hours.processing_priority,
hours.rate,
nvl(hours.multiple,1),
hours.hours,
hours.amount
from pay_hours_by_rate_v hours
where hours.assignment_action_id = cp_run_action_id
and legislation_code = 'US'
and hours.element_type_id >= 0 -- Bug 3370112
order by hours.processing_priority,hours.element_type_id;
select pepd.element_entry_id,
sum(decode(piv.name, 'Pay Value', prrv.result_value)),
sum(decode(piv.name, 'Hours', prrv.result_value)),
nvl(sum(decode(piv.name, 'Multiple', prrv.result_value)),1),
sum(decode(piv.name, 'Rate', prrv.result_value))
from pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_entry_process_details pepd
where piv.input_value_id = prrv.input_value_id
and prr.element_type_id = cp_element_type_id
and prr.run_result_id = prrv.run_result_id
and prr.assignment_action_id = cp_run_action_id
and prr.source_type = 'E'
and pepd.element_entry_id = prr.source_id
and pepd.source_asg_action_id is not null
and result_value is not null
group by pepd.element_entry_id;
** Insert into seperate table
******************************************************/
ln_hrs_index := ltr_hours_x_rate.count;
Name : update_federal_values
Purpose : The procedure set the Federal level balance value
in the PL/SQL table.
Arguments :
Notes :
**************************************************************/
PROCEDURE update_federal_values(p_balance in varchar2
,p_bal_value in number
,p_index in number
,p_category in varchar2
)
IS
lv_procedure_name VARCHAR2(100) := '.update_federal_values';
END update_federal_values;
Name : update_sit_values
Purpose : The procedure set the SIT level balance value
in the PL/SQL table.
Arguments :
Notes :
**************************************************************/
PROCEDURE update_sit_values(p_balance in varchar2
,p_bal_value in number
,p_index in number
)
IS
lv_procedure_name VARCHAR2(100) := '.update_sit_values';
END update_sit_values;
Name : update_sdi_ee_values
Purpose : The procedure set the SDI EE level balance value
in the PL/SQL table.
Arguments :
Notes :
**************************************************************/
PROCEDURE update_sdi_ee_values(p_balance in varchar2
,p_bal_value in number
,p_index in number
)
IS
lv_procedure_name VARCHAR2(100) := '.update_sdi_ee_values';
END update_sdi_ee_values;
Name : update_sdi_er_values
Purpose : The procedure set the SDI ER level balance value
in the PL/SQL table.
Arguments :
Notes :
**************************************************************/
PROCEDURE update_sdi_er_values(p_balance in varchar2
,p_bal_value in number
,p_index in number
)
IS
lv_procedure_name VARCHAR2(100) := '.update_sdi_ee_values';
END update_sdi_er_values;
Name : update_sui_ee_values
Purpose : The procedure set the SUI EE level balance value
in the PL/SQL table.
Arguments :
Notes :
**************************************************************/
PROCEDURE update_sui_ee_values(p_balance in varchar2
,p_bal_value in number
,p_index in number
)
IS
lv_procedure_name VARCHAR2(100) := '.update_sui_ee_values';
END update_sui_ee_values;
Name : update_sui_er_values
Purpose : The procedure set the SUI ER level balance value
in the PL/SQL table.
Arguments :
Notes :
**************************************************************/
PROCEDURE update_sui_er_values(p_balance in varchar2
,p_bal_value in number
,p_index in number
)
IS
lv_procedure_name VARCHAR2(100) := '.update_sui_er_values';
END update_sui_er_values;
Name : update_work_comp_values
Purpose : The procedure set the Worker's Comp level balance
value in the PL/SQL table.
Arguments :
Notes :
**************************************************************/
PROCEDURE update_work_comp_values(p_balance in varchar2
,p_bal_value in number
,p_index in number
)
IS
lv_procedure_name VARCHAR2(100) := '.update_work_comp_values';
END update_work_comp_values;
Name : update_steic_values
Purpose : The procedure sets the STEIC Advance balance value
in the PL/SQL table.
Arguments :
Notes :
**************************************************************/
PROCEDURE update_steic_values(p_balance in varchar2
,p_bal_value in number
,p_index in number
)
IS
lv_procedure_name VARCHAR2(100) := '.update_steic_values';
END update_steic_values;
Name : update_county_values
Purpose : The procedure set the County level balance value
in the PL/SQL table.
Arguments :
Notes :
**************************************************************/
PROCEDURE update_county_values(p_balance in varchar2
,p_bal_value in number
,p_index in number
)
IS
lv_procedure_name VARCHAR2(100) := '.update_county_values';
END update_county_values;
Name : update_county_head_values
Purpose : The procedure set the County Head level balance
values in the PL/SQL table.
Arguments :
Notes :
**************************************************************/
PROCEDURE update_county_head_values(p_balance in varchar2
,p_bal_value in number
,p_index in number
)
IS
lv_procedure_name VARCHAR2(100) := '.update_county_head_values';
END update_county_head_values;
Name : update_city_values
Purpose : The procedure set the City level balance value
in the PL/SQL table.
Arguments :
Notes :
**************************************************************/
PROCEDURE update_city_values(p_balance in varchar2
,p_bal_value in number
,p_index in number
)
IS
lv_procedure_name VARCHAR2(100) := '.update_city_values';
END update_city_values;
Name : update_city_head_values
Purpose : The procedure set the City Head level balance value
in the PL/SQL table.
Arguments :
Notes :
**************************************************************/
PROCEDURE update_city_head_values(p_balance in varchar2
,p_bal_value in number
,p_index in number
)
IS
lv_procedure_name VARCHAR2(100) := '.update_city_head_values';
END update_city_head_values;
Name : update_school_values
Purpose : The procedure set the School level balance value
in the PL/SQL table.
Arguments :
Notes :
**************************************************************/
PROCEDURE update_school_values(p_balance in varchar2
,p_bal_value in number
,p_index in number
)
IS
lv_procedure_name VARCHAR2(100) := '.update_school_values';
END update_school_values;
select pcnt.jurisdiction_code
from pay_us_emp_county_tax_rules_f pcnt
where pcnt.assignment_id = p_assignment_id
and pcnt.school_district_code = substr(p_school_jurisdiction,4,5)
and pcnt.state_code = substr(p_school_jurisdiction,1,2)
and pcnt.effective_start_date <= p_end_date
and pcnt.effective_end_date >= p_start_date;
select pcty.jurisdiction_code
from pay_us_emp_city_tax_rules_f pcty
where pcty.assignment_id = p_assignment_id
and pcty.school_district_code = substr(p_school_jurisdiction,4,5)
and pcty.state_code = substr(p_school_jurisdiction,1,2)
and pcty.effective_start_date <= p_end_date
and pcty.effective_end_date >= p_start_date;
select nvl(ADDR.add_information19,ADDR.region_1),
nvl(ADDR.add_information17,ADDR.region_2),
nvl(addr.add_information18,addr.town_or_city)
from per_addresses addr
,per_all_assignments_f assign
where cp_run_effective_date between assign.effective_start_date
and assign.effective_end_date
and assign.assignment_id = cp_assignment_id
and addr.person_id = assign.person_id
and addr.primary_flag = 'Y'
and cp_run_effective_date between addr.date_from
and nvl(addr.date_to,
to_date('31/12/4712', 'DD/MM/YYYY'));
select pcn.state_code, pcn.county_code, pcn.city_code
from pay_us_states pus,
pay_us_counties puc,
pay_us_city_names pcn
where pus.state_abbrev = cp_state_abbrev
and puc.state_code = pus.state_code
and puc.county_name = cp_county_name
and pcn.state_code = puc.state_code
and pcn.county_code = puc.county_code
and pcn.city_name = cp_city_name ;
Name : update_ytd_withheld
Purpose : This procedure inserts the Witheld Current and YTD
amounts into the PL/SQL table for Employee Taxes
Arguments :
Notes :
******************************************************************/
PROCEDURE update_ytd_withheld(p_xfr_action_id in number
,p_balance_name in varchar2
,p_balance_type_id in varchar2
,p_processing_priority in varchar2 default 10
,p_jurisdiction in varchar2
,p_curr_withheld in number
,p_ytd_withheld in number
)
IS
CURSOR get_display_name ( cp_reporting_name in varchar2 ,
cp_jurisdiction_code in varchar2) IS
select decode(length(cp_jurisdiction_code),
11, decode(cp_jurisdiction_code,
'00-000-0000', null,
decode(cp_reporting_name,
'Head Tax Withheld', null,
pay_us_employee_payslip_web.get_jurisdiction_name(
cp_jurisdiction_code) || ' ')),
8, pay_us_employee_payslip_web.get_jurisdiction_name(
substr(cp_jurisdiction_code,1,2)||'-000-0000') || ' ') ||
decode(fl.description, '', null,
nvl(fl.description, cp_reporting_name)) || ' ' ||
decode(length(cp_jurisdiction_code),
8, decode(substr(cp_jurisdiction_code,1,2), '36', substr(cp_jurisdiction_code, 4),
pay_us_employee_payslip_web.get_jurisdiction_name(cp_jurisdiction_code)),
11, decode(cp_reporting_name,
'Head Tax Withheld', pay_us_employee_payslip_web.get_jurisdiction_name(
cp_jurisdiction_code)))display_name
from fnd_common_lookups fl
where fl.lookup_type(+) = 'PAY_US_LABELS'
and upper(cp_reporting_name) = fl.lookup_code(+) ;
lv_procedure_name VARCHAR2(100) := '.update_ytd_withheld';
END update_ytd_withheld;
update_federal_values(p_balance => lv_balance_name
,p_bal_value => ln_bal_value
,p_index => ln_index
,p_category => 'US FEDERAL');
** Insert data for Payslip
******************************************************************/
if lv_balance_name = 'FIT Withheld' then
ln_step := 12;
update_ytd_withheld(
p_xfr_action_id => p_xfr_action_id
,p_balance_name => lv_balance_name
,p_balance_type_id => ln_balance_type_id
,p_processing_priority => 1
,p_jurisdiction => '00-000-0000'
,p_curr_withheld => ln_curr_withheld
,p_ytd_withheld => ln_ytd_withheld);
update_ytd_withheld(
p_xfr_action_id => p_xfr_action_id
,p_balance_name => lv_balance_name
,p_balance_type_id => ln_balance_type_id
,p_processing_priority => 4
,p_jurisdiction => '00-000-0000'
,p_curr_withheld => ln_curr_withheld
,p_ytd_withheld => ln_ytd_withheld);
update_ytd_withheld(
p_xfr_action_id => p_xfr_action_id
,p_balance_name => lv_balance_name
,p_balance_type_id => ln_balance_type_id
,p_processing_priority => 3
,p_jurisdiction => '00-000-0000'
,p_curr_withheld => ln_curr_withheld
,p_ytd_withheld => ln_ytd_withheld);
update_ytd_withheld(
p_xfr_action_id => p_xfr_action_id
,p_balance_name => lv_balance_name
,p_balance_type_id => ln_balance_type_id
,p_processing_priority => 2
,p_jurisdiction => '00-000-0000'
,p_curr_withheld => ln_curr_withheld
,p_ytd_withheld => ln_ytd_withheld);
update_ytd_withheld(
p_xfr_action_id => p_xfr_action_id
,p_balance_name => lv_balance_name
,p_balance_type_id => ln_balance_type_id
,p_jurisdiction => '00-000-0000'
,p_curr_withheld => ln_curr_withheld
,p_ytd_withheld => ln_ytd_withheld);
update_ytd_withheld(
p_xfr_action_id => p_xfr_action_id
,p_balance_name => lv_balance_name
,p_balance_type_id => ln_balance_type_id
,p_jurisdiction =>
p_jurisdiction_tab(i).emp_jd
,p_curr_withheld => ln_curr_withheld
,p_ytd_withheld => ln_ytd_withheld);
update_ytd_withheld(
p_xfr_action_id => p_xfr_action_id
,p_balance_name => lv_balance_name
,p_balance_type_id => ln_balance_type_id
,p_processing_priority => 5
,p_jurisdiction =>
p_jurisdiction_tab(i).emp_jd
,p_curr_withheld => ln_curr_withheld
,p_ytd_withheld => ln_ytd_withheld);
update_sit_values(p_balance => lv_balance_name
,p_bal_value => ln_bal_value
,p_index => ln_index);
update_ytd_withheld(
p_xfr_action_id => p_xfr_action_id
,p_balance_name => 'SDI Withheld'
,p_balance_type_id => ln_balance_type_id
,p_jurisdiction =>
p_jurisdiction_tab(i).emp_jd
,p_curr_withheld => ln_curr_withheld
,p_ytd_withheld => ln_ytd_withheld);
update_sdi_ee_values(p_balance => lv_balance_name
,p_bal_value => ln_bal_value
,p_index => ln_index);
update_sdi_er_values(p_balance => lv_balance_name
,p_bal_value => ln_bal_value
,p_index => ln_index);
update_ytd_withheld(
p_xfr_action_id => p_xfr_action_id
,p_balance_name => 'SUI Withheld'
,p_balance_type_id => ln_balance_type_id
,p_jurisdiction =>
p_jurisdiction_tab(i).emp_jd
,p_curr_withheld => ln_curr_withheld
,p_ytd_withheld => ln_ytd_withheld);
update_sui_ee_values(p_balance => lv_balance_name
,p_bal_value => ln_bal_value
,p_index => ln_index);
update_sui_er_values(p_balance => lv_balance_name
,p_bal_value => ln_bal_value
,p_index => ln_index);
update_ytd_withheld(
p_xfr_action_id => p_xfr_action_id
,p_balance_name => 'WC Withheld'
,p_balance_type_id => ln_balance_type_id
,p_jurisdiction =>
p_jurisdiction_tab(i).emp_jd
,p_processing_priority => 10
,p_curr_withheld => ln_curr_withheld
,p_ytd_withheld => ln_ytd_withheld);
update_ytd_withheld(
p_xfr_action_id => p_xfr_action_id
,p_balance_name => 'WC2 Withheld'
,p_balance_type_id => ln_balance_type_id
,p_jurisdiction =>
p_jurisdiction_tab(i).emp_jd
,p_processing_priority =>10
,p_curr_withheld => ln_curr_withheld
,p_ytd_withheld => ln_ytd_withheld);
update_work_comp_values(p_balance => lv_balance_name
,p_bal_value => ln_bal_value
,p_index => ln_index);
update_ytd_withheld(
p_xfr_action_id => p_xfr_action_id
,p_balance_name => 'STEIC Advance'
,p_balance_type_id => ln_balance_type_id
,p_jurisdiction =>
p_jurisdiction_tab(i).emp_jd
,p_processing_priority => 10
,p_curr_withheld => ln_curr_withheld
,p_ytd_withheld => ln_ytd_withheld);
update_steic_values(p_balance => lv_balance_name
,p_bal_value => ln_bal_value
,p_index => ln_index);
update_ytd_withheld(
p_xfr_action_id => p_xfr_action_id
,p_balance_name => 'County Withheld'
,p_balance_type_id => ln_balance_type_id
,p_processing_priority => 6
,p_jurisdiction =>
p_jurisdiction_tab(i).emp_jd
,p_curr_withheld => ln_curr_withheld
,p_ytd_withheld => ln_ytd_withheld);
update_county_values(p_balance => lv_balance_name
,p_bal_value => ln_bal_value
,p_index => ln_index);
update_ytd_withheld(
p_xfr_action_id => p_xfr_action_id
,p_balance_name => 'Head Tax Withheld'
,p_balance_type_id => ln_balance_type_id
,p_jurisdiction =>
p_jurisdiction_tab(i).emp_jd
,p_curr_withheld => ln_curr_withheld
,p_ytd_withheld => ln_ytd_withheld);
update_county_head_values(
p_balance => lv_balance_name
,p_bal_value => ln_bal_value
,p_index => ln_index);
update_ytd_withheld(
p_xfr_action_id => p_xfr_action_id
,p_balance_name => 'City Withheld'
,p_balance_type_id => ln_balance_type_id
,p_jurisdiction =>
p_jurisdiction_tab(i).emp_jd
,p_curr_withheld => ln_curr_withheld
,p_ytd_withheld => ln_ytd_withheld);
update_city_values(
p_balance => lv_balance_name
,p_bal_value => ln_bal_value
,p_index => ln_index);
update_ytd_withheld(
p_xfr_action_id => p_xfr_action_id
,p_balance_name => 'Head Tax Withheld'
,p_balance_type_id => ln_balance_type_id
,p_jurisdiction =>
p_jurisdiction_tab(i).emp_jd
,p_curr_withheld => ln_curr_withheld
,p_ytd_withheld => ln_ytd_withheld);
update_city_head_values(
p_balance => lv_balance_name
,p_bal_value => ln_bal_value
,p_index => ln_index);
update_ytd_withheld(
p_xfr_action_id => p_xfr_action_id
,p_balance_name => 'School Withheld'
,p_balance_type_id => ln_balance_type_id
,p_jurisdiction => lv_emp_school_jd
,p_curr_withheld => ln_curr_withheld
,p_ytd_withheld => ln_ytd_withheld);
update_school_values(
p_balance => lv_balance_name
,p_bal_value => ln_bal_value
,p_index => ln_index);
select nvl(head_tax,'N')
from pay_us_city_tax_info_f
where jurisdiction_code = cp_jd
and cp_date between effective_start_date and effective_end_date;
select substr(puar.jurisdiction_code,1,2)||'-000-0000'
from pay_us_asg_reporting puar
where puar.assignment_id = cp_assignment_id
and puar.tax_unit_id = cp_tax_unit_id
union
select substr(puar.jurisdiction_code,1,6)||'-0000'
from pay_us_asg_reporting puar
where puar.assignment_id = cp_assignment_id
and puar.tax_unit_id = cp_tax_unit_id
and length(puar.jurisdiction_code) <> 8
union
select puar.jurisdiction_code
from pay_us_asg_reporting puar
where puar.assignment_id = cp_assignment_id
and puar.tax_unit_id = cp_tax_unit_id;
select locked_action_id
from pay_action_interlocks
where locking_action_id = cp_xfr_action_id;
select distinct prr.jurisdiction_code
from pay_run_results prr
where prr.assignment_action_id = cp_baladj_action_id
and length(prr.jurisdiction_code) >= 8
and substr(prr.jurisdiction_code,8,1) <> 'U'
order by prr.jurisdiction_code;
pay_ac_action_arch.emp_state_jd.delete;
pay_ac_action_arch.emp_city_jd.delete;
pay_ac_action_arch.emp_county_jd.delete;
pay_ac_action_arch.emp_school_jd.delete;
Name : update_employee_information
Purpose : This function updates the Employee Information which is
archived by the global archive procedure.
The only thing which is updated is employee name. The
Global package archvies the full name for the employee.
This procedure will update the name to
First Name[space]Middle Initial[.][space]Last Name
Arguments : IN
p_assignment_action_id number;
PROCEDURE update_employee_information(
p_action_context_id in number
,p_assignment_id in number)
IS
cursor c_get_archive_info(cp_action_context_id in number
,cp_assignment_id in number) is
select action_information_id, effective_date,
object_version_number
from pay_action_information
where action_context_id = cp_action_context_id
and action_context_type = 'AAP'
and assignment_id = cp_assignment_id
and action_information_category = 'EMPLOYEE DETAILS';
select ltrim(rtrim(
first_name || ' ' ||
decode(nvl(length(ltrim(rtrim(middle_names))),0), 0, null,
upper(substr(middle_names,1,1)) || '. ' ) ||
pre_name_adjunct || last_name || ' '|| suffix))
from per_all_people_f ppf
where ppf.person_id =
(select person_id from per_all_assignments_f paf
where assignment_id = cp_assignment_id
and cp_effective_date between paf.effective_start_date
and paf.effective_end_date)
and cp_effective_date between ppf.effective_start_date
and ppf.effective_end_date;
lv_procedure_name VARCHAR2(200) := '.update_employee_information';
pay_action_information_api.update_action_information
(p_action_information_id => ln_action_information_id
,p_object_version_number => ln_ovn
,p_action_information1 => lv_employee_name
);
END update_employee_information;
select fed.filing_status_code,
hl.meaning,
fed.withholding_allowances,
fed.fit_additional_tax,
fed.fit_override_amount,
fed.fit_override_rate
from pay_us_emp_fed_tax_rules_f fed,
hr_lookups hl
where fed.assignment_id = cp_assignment_id
and hl.lookup_code = fed.filing_status_code
and hl.lookup_type = 'US_FIT_FILING_STATUS'
and cp_run_effective_date between effective_start_date
and effective_end_date;
select nvl(paf.work_at_home, 'N')
from per_all_assignments_f paf
where paf.assignment_id = cp_assignment_id
and p_run_effective_date between paf.effective_start_date
and paf.effective_end_date;
select pts.time_in_state,
pts.state_name,
pts.jurisdiction_code,
pts.filing_status_code,
pts.meaning,
pts.withholding_allowances,
pts.sit_additional_tax,
pts.sit_override_amount,
pts.sit_override_rate
from pay_us_emp_time_in_state_v pts
where pts.assignment_id = cp_assignment_id
and pts.jurisdiction_code like cp_jurisdiction_code;
select effective_date
from fnd_sessions fs
where session_id = userenv('sessionid');
insert into fnd_sessions (session_id, effective_date) values
(userenv('sessionid'), p_run_effective_date);
update fnd_sessions
set effective_date = p_run_effective_date
where session_id = userenv('sessionid');
select paa.assignment_action_id
from pay_assignment_actions paa,
pay_action_interlocks pai,
pay_payroll_actions ppa
where pai.locking_action_id = cp_prepayment_action_id
and paa.assignment_action_id = pai.locked_action_id
and paa.assignment_id = cp_assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and nvl(paa.run_type_id,0) <> cp_sepchk_run_type
order by paa.assignment_action_id desc;
select ptp.time_period_id,
--bug 7379102
-- ppa.date_earned,
nvl(ppa.date_earned,ppa.effective_date),
--bug 7379102
ppa.effective_date
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp
where paa.assignment_action_id = cp_run_assignment_action
and ppa.payroll_action_id = paa.payroll_action_id
and ptp.payroll_id = ppa.payroll_id
--bug 7379102
-- and ppa.date_earned between ptp.start_date and ptp.end_date;
select substr(serial_number,1,1)
from pay_assignment_actions paa
where paa.assignment_action_id = cp_last_xfr_act_id;
pay_emp_action_arch.insert_rows_thro_api_process(
p_action_context_id => p_xfr_action_id
,p_action_context_type=> 'AAP'
,p_assignment_id => p_assignment_id
,p_tax_unit_id => p_tax_unit_id
,p_curr_pymt_eff_date => p_curr_pymt_eff_date
,p_tab_rec_data => pay_ac_action_arch.lrr_act_tab
);
update_employee_information(
p_action_context_id => p_xfr_action_id
,p_assignment_id => p_assignment_id);
select paa.payroll_action_id,
paa.assignment_action_id,
paa.assignment_id,
paa.tax_unit_id,
paa.serial_number,
paa.chunk_number
from pay_assignment_actions paa
where paa.assignment_action_id = cp_assignment_action;
select org_information9
from hr_organization_information
where org_information_context = 'Business Group Information'
and organization_id = cp_business_group;
select prt.run_type_id
from pay_run_types_f prt
where prt.shortname = 'SEPCHECK'
and prt.legislation_code = 'US';
select distinct paa.assignment_id
from pay_action_interlocks pai,
pay_assignment_actions paa,
pay_payroll_actions ppa
where pai.locking_action_id = cp_prepayment_action_id
and paa.assignment_action_id = pai.locked_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('R', 'Q', 'B')
and ((ppa.run_type_id is null and paa.source_action_id is null) or
(ppa.run_type_id is not null and paa.source_action_id is not null))
and paa.action_status = 'C';
select paa.assignment_action_id, paa.payroll_action_id,
ppa.action_type
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai
where pai.locking_action_id = cp_prepayment_action_id
and pai.locked_action_id = paa.assignment_action_id
and paa.assignment_id = cp_assignment_id
and paa.source_action_id is null
and ppa.payroll_action_id = paa.payroll_action_id
order by paa.assignment_action_id desc;
select ppa.effective_date
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = cp_prepayment_action_id;
select count(*)
from pay_action_information
where action_context_id = cp_payroll_action_id
and action_context_type = 'PA';
select distinct
assignment_id
,tax_unit_id
,nvl(source_action_id,-999)
,assignment_action_id
from pay_payment_information_v
where assignment_action_id = cp_prepay_action_id
order by 3,1,2;
select paa.assignment_action_id
,paa.source_action_id
from pay_assignment_actions paa
,pay_action_interlocks pai
where pai.locking_action_id = cp_pp_asg_act_id
and paa.assignment_action_id = pai.locked_action_id
and paa.assignment_id = cp_assignment_id
and paa.tax_unit_id = cp_tax_unit_id
and paa.source_action_id is not null
and not exists ( select 1
from pay_run_types_f prt
where prt.legislation_code = 'US'
and prt.run_type_id = paa.run_type_id
and prt.run_method in ('C', 'S'))
order by paa.action_sequence desc;
select ppp.assignment_action_id
from pay_assignment_actions paa
,pay_pre_payments ppp
where ( paa.assignment_action_id = cp_asg_act_id OR
paa.source_action_id = cp_asg_act_id )
and ppp.assignment_action_id = paa.assignment_action_id
and ppp.source_action_id = cp_source_act_id;
select /*+ ORDERED */
paf1.assignment_id,
paa.assignment_action_id
from per_all_assignments_f paf1
,pay_assignment_actions paa
,pay_payroll_actions ppa
where paf1.person_id = (select /*+ PUSH_SUBQ */ person_id
from per_all_assignments_f START_ASS
where START_ASS.assignment_id = cp_assignment_id
and rownum = 1)
and paf1.effective_end_date >= trunc(cp_effective_date,'Y')
and paf1.effective_start_date <= cp_effective_date
and paa.assignment_id = paf1.assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.payroll_id = cp_payroll_id
and ppa.action_type in ('Q', 'R', 'I','B')
and not exists (select 'x'
from pay_action_information pai
where pai.action_context_id = cp_xfr_action_id
and pai.assignment_id = paf1.assignment_id)
and not exists (select 1
from pay_payment_information_v ppi
where ppi.assignment_action_id = cp_prepay_action_id
and ppi.assignment_id = paf1.assignment_id
and ppi.source_action_id is null)
order by paf1.assignment_id,
paa.action_sequence desc;
select max(pai.effective_date)
from pay_action_information pai
where pai.action_context_type = 'AAP'
and pai.assignment_id = cp_assignment_id
and pai.action_information_category in ('AC EARNINGS', 'AC DEDUCTIONS')
and pai.action_context_id <> cp_xfr_action_id
and pai.effective_date <= cp_effective_date;
select paa.assignment_action_id
,paa.source_action_id
from pay_assignment_actions paa
,pay_action_interlocks pai
where pai.locking_action_id = cp_pp_asg_act_id
and paa.assignment_action_id = pai.locked_action_id
and paa.assignment_id = cp_assignment_id
and paa.tax_unit_id = cp_tax_unit_id
order by paa.action_sequence desc;
select paa.assignment_action_id
from pay_assignment_actions paa,
pay_action_interlocks pai
where pai.locking_action_id = cp_pp_asg_act_id
and paa.assignment_action_id = pai.locked_action_id
and paa.assignment_id = cp_assignment_id
and paa.tax_unit_id = cp_tax_unit_id
and nvl(paa.run_type_id,0) <> cp_sepchk_run_type
and not exists ( select 1
from pay_run_types_f prt
where prt.legislation_code = 'US'
and prt.run_type_id = nvl(paa.run_type_id,0)
and prt.run_method = 'C' );
select pay_assignment_actions_s.nextval
into ln_child_xfr_action_id
from dual;
update pay_assignment_actions
set serial_number = lv_serial_number
where assignment_action_id = ln_child_xfr_action_id;
pay_ac_action_arch.emp_elements_tab.delete;
pay_ac_action_arch.lrr_act_tab.delete;
pay_emp_action_arch.insert_rows_thro_api_process(
p_action_context_id => ln_child_xfr_action_id
,p_action_context_type=> 'AAP'
,p_assignment_id => ln_np_asg_id
,p_tax_unit_id => ln_tax_unit_id
,p_curr_pymt_eff_date => ld_curr_pymt_eff_date
,p_tab_rec_data => pay_ac_action_arch.lrr_act_tab
);
pay_emp_action_arch.insert_rows_thro_api_process(
p_action_context_id => p_xfr_action_id
,p_action_context_type=> 'AAP'
,p_assignment_id => ln_xfr_assignment_id
,p_tax_unit_id => ln_tax_unit_id
,p_curr_pymt_eff_date => ld_curr_pymt_eff_date
,p_tab_rec_data => pay_ac_action_arch.lrr_act_tab
);
pay_emp_action_arch.insert_rows_thro_api_process(
p_action_context_id => p_xfr_action_id
,p_action_context_type=> 'AAP'
,p_assignment_id => ln_xfr_assignment_id
,p_tax_unit_id => ln_tax_unit_id
,p_curr_pymt_eff_date => ld_curr_pymt_eff_date
,p_tab_rec_data => pay_ac_action_arch.lrr_act_tab
);
select assignment_id
from pay_assignment_actions
where assignment_action_id = cp_assignment_action_id;