[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
02-May-2013 abellur 115.139 16318258 Updated get_attachment_number,
cursors c_get_att_no_new, jur
and upgrade_status modified to
include dates in where clause.
08-Mar-2013 abellur 115.138 14098797 Updated GSCC error on to_date
05-Mar-2013 abellur 115.137 14098797 Modified get_attachment_number,
element entry details fetch cursor
logic is changed to use the new
or old architecture cursor based
on the run result status and upgrade
date.
29-Jan-2013 nvelaga 115.136 14793173 Modified update_federal_values by
adding Medicare EE Taxable Over Limit
19-mar-2012 tclewis 115.35 13719809 fixed an issue in
populate_psd_tax_balances moved
update_psd_values to ouside of
the if statement for action
types of balance adjustment.
06-Mar-2012 abellur 115.134 13814029 Included code for Medical Support
indicator. Updated date formats for
earned date and payment date for
'US THIRD PARTY CHECKS'
23-Feb-2012 abellur 115.133 13029995 Updated the out variables - nocopy in
get_attachment_number.
20-Feb-2012 abellur 115.132 13029995 Added procedure get_3rdparty_check and
function get_attachment_number.
get_3rdparty_check will be called from
process actions and get_attachment_number
is called from get_3rdparty_check.
19-JAN-2012 nkjaladi 115.131 13436969 Modified procedure populate_psd_tax_balances
to pass g_cty_wk_psd_wh_def_bal_id and
p_ytd_balcall_aaid to parameters
p_defined_balance_id and p_balcall_aaid
inorder to derive the R/NR correctly.
21-OCT-3011 tclewis 115.128 10460539 change lv_rr_jurisdiction_code
to varchar2(30).
17-AUG-2011 nkjaladi 115.127 9242223 Modified cursor c_get_unproc_asg
in procedure action_archive_data
to add condition of payroll id
15-JUL-2010 emunisek 115.126 9872952 Modified procedure get_employee_withholding_info
to fetch Federal Wage Exempt Flag details and archive
it in ACTION_INFORMATION10 of pay_action_infromation with
action_information_category as 'US WITHHOLDINGS' and
ACTION_INFORMATION4 as 'Federal'.The same flag value
will be archived in ACTION_INFORMATION27 under
action_information_category as 'US FEDERAL', so that
we can control the FIT values read from US FEDERAL
based on this flag for Periodic Tax Filing FLS Interface.
09-JUL-2010 tclewis 115.125 9841827 Modified
populate_federal_tax_balances
commented out code that
handles loadign SS ER Taxable
balance. The code was using
The value of SS EE taxable.
Code now forces a direct
access to the SS ER Taxable
run results for the archive.
Note": The code may need to
be modified for Medicare ER
Taxable as the code is doing
the same there.
03-feb-2010 mikarthi 115.124 8688998 Adding two more parameters
1. p_archive_balance_info
2. p_legislation_code
while calling the procedure
pay_ac_action_arch.get_last_xfr_info
28-Jan-2010 npannamp 115.123 8768738 Removed fix done in 8688998.
28-Dec-2009 npannamp 115.122 8768738 Added code to update payroll_id column
in pay_payroll_actions table. Procedure
action_archinit modified.
19-Oct-2009 kagangul 115.121 8688998 Adding two more parameters
1. p_archive_balance_info
2. p_legislation_code
while calling the procedure
pay_ac_action_arch.get_last_xfr_info
24-AUG-2009 rnestor 115.120 8832183 Added Jurst check
18-AUG-2009 rnestor 115.119 8804636 Added date check to c_emp_state_info
04-MAY-2009 skpatil 115.118 8433161 To display FIt Exempt and SIT Exempt
status at Check writer XML
31-MAR-2009 sudedas 115.117 3816988 Added proc action_archdeinit
04-DEC-2008 tclewis 115.115 Added code for SUI 1 EE.
27-SEP-2008 sudedas 115.114 7418142 Changed cursor c_get_unproc_asg
05-SEP-2008 asgugupt 115.113 7379102 Changed c_time_period
03-MAR-2008 sudedas 115.112 6827021 Changed action_archive_data
For Multi Assignment Enabled
Payroll.
13-09-2007 sausingh 115.111 5635335 Added nvl condition in procedure
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 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 = substr(fdu.form_left_prompt,
1 , decode(instr(fdu.form_left_prompt, 'YTD'),
0, length(fdu.form_left_prompt),
instr(fdu.form_left_prompt, 'YTD')-2))
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 legislative_parameters,payroll_id
INTO leg_param,l_ppa_payroll_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id ;
UPDATE pay_payroll_actions
SET payroll_id = l_payroll_id
WHERE payroll_action_id = p_payroll_action_id;
select effective_date
from pay_payroll_actions
where payroll_action_id = cp_payroll_action_id;
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 ppa.effective_date
from pay_assignment_actions paa,
pay_assignment_actions paa1,
pay_payroll_actions ppa
where paa.assignment_action_id = cp_run_action_id
and paa1.assignment_action_id=to_number(substr(paa.serial_number,3))
and paa1.payroll_action_id=ppa.payroll_action_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
and ln_pymt_eff_date between piv.effective_start_date and piv.effective_end_date -- Bug# 16318258
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_ee_values
Purpose : The procedure set the SUI EE level balance value
in the PL/SQL table.
Arguments :
Notes : 04-DEC-2008 Only SUI1 EE Withheld maintained
however, kept all balance's for future need.
**************************************************************/
PROCEDURE update_sui1_ee_values(p_balance in varchar2
,p_bal_value in number
,p_index in number
)
IS
lv_procedure_name VARCHAR2(100) := '.update_sui1_ee_values';
END update_sui1_ee_values;
Name : update_sdi_ee_values
Purpose : The procedure set the SDI EE level balance value
in the PL/SQL table.
Arguments :
Notes : 04-DEC-2008 Only SDI1 EE Withheld maintained
however, kept all balance's for future need.
**************************************************************/
PROCEDURE update_sdi1_ee_values(p_balance in varchar2
,p_bal_value in number
,p_index in number
)
IS
lv_procedure_name VARCHAR2(100) := '.update_sdi1_ee_values';
END update_sdi1_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_psd_values
Purpose : The procedure set the PSD level balance value
in the PL/SQL table.
Arguments :
Notes :
**************************************************************/
PROCEDURE update_psd_values(p_balance in varchar2
,p_bal_value in number
,p_index in number
)
IS
lv_procedure_name VARCHAR2(100) := '.update_psd_values';
END update_psd_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 => 'SDI1 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_sdi1_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);
hr_utility.trace('US STATE2 Before update_ytd_withheld ');
update_ytd_withheld(
p_xfr_action_id => p_xfr_action_id
,p_balance_name => 'SUI1 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);
hr_utility.trace('US STATE2 Before update_sui1_ee_values ');
update_sui1_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 => '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_psd_values( p_balance => lv_balance_name
,p_bal_value => ln_bal_value
,p_index => ln_index);
update_psd_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,
decode(fed.fit_exempt,'Y','Exempt',hl.meaning), --8433161
fed.withholding_allowances,
fed.fit_additional_tax,
fed.fit_override_amount,
fed.fit_override_rate,
fed.wage_exempt /*Added for Bug#9872952*/
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,
decode(pst.sit_exempt,'Y','Exempt',pts.meaning), --8433161
pts.withholding_allowances,
pts.sit_additional_tax,
pts.sit_override_amount,
pts.sit_override_rate
from pay_us_emp_time_in_state_v pts,
pay_us_emp_state_tax_rules_f pst --8433161
where pts.assignment_id = cp_assignment_id
and pts.jurisdiction_code like cp_jurisdiction_code
and pst.jurisdiction_code = pts.jurisdiction_code --8832183 RLN
and pst.assignment_id = pts.assignment_id --8433161
and cp_effective_date between pst.effective_start_date
and pst.effective_end_date -- 8804636 RLN
and cp_effective_date between pts.effective_start_date
and pts.effective_end_date; -- 8804636 RLN
select effective_date
from fnd_sessions fs
where session_id = userenv('sessionid');
SELECT parameter_value
FROM pay_action_parameters
WHERE parameter_name = 'WAGE_ACCUMULATION_ENABLED';
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 peev.screen_entry_value garn_ref_no
,round(ppp.value ,2)
,TO_CHAR(pay_us_payment_pkg.get_trx_date( NULL, --:business_group_id,
NULL, --:ppa_payroll_action_id,
NULL,
NULL, --:ppa_payroll_id,
NULL, --:ppa_consolidation_set_id,
NULL, --:ppa_org_payment_method_id,
NULL, --:ppa_effective_date,
NULL, --:ppa_date_earned,
NULL, --:ppa_override_date,
p_pre_payment_id, --ppp.pre_payment_id,
NULL --ppa.effective_date
),'DD-MON-YYYY') payment_date
,TO_CHAR(ppa.date_earned, 'DD-MON-YYYY')
,prr_att.run_result_id
,peef.entry_information2 -- FIPS code
,peef.entry_information1 -- Medical Indicator
,peef.entry_information3 -- DOC Number
,paa.assignment_action_id
,pet.element_type_id
from pay_element_entry_values_f peev,
pay_input_values_f piv_att,
pay_element_entries_f peef,
pay_run_results prr_att,
pay_run_results prr_pay,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai,
pay_pre_payments ppp,
pay_input_values_f piv_pay,
pay_run_result_values prrv_att,
pay_run_result_values prrv_pay,
pay_element_types_f pet,
pay_element_types_f pet_calc,
pay_element_classifications pec
where round(ppp.value ,2) = round(p_value ,2)
and ppp.pre_payment_id = p_pre_payment_id
and ppp.assignment_action_id = pai.locking_action_id
and pai.locked_action_id = paa.assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('R', 'Q')
and ((paa.source_action_id is not null and ppa.run_type_id is not null) or
(paa.source_action_id is null and ppa.run_type_id is null))
and ppp.personal_payment_method_id = peef.personal_payment_method_id
and peef.assignment_id = paa.assignment_id
and ppa.date_earned between peev.effective_start_date
and peev.effective_end_date
and ppa.date_earned between peef.effective_start_date
and peef.effective_end_date
and piv_att.input_value_id = peev.input_value_id
and upper(piv_att.name) = 'ATTACHMENT NUMBER'
and ppa.effective_Date between piv_att.effective_start_date
and piv_att.effective_end_date
and piv_att.input_value_id = prrv_att.input_value_id
and prrv_att.result_value = peev.screen_entry_value
and prrv_att.run_result_id = prr_att.run_result_id
and paa.assignment_Action_id = prr_att.assignment_Action_id
and prr_att.element_type_id = peef.element_type_id
and peef.element_type_id = pet.element_type_id
and pet.classification_id = pec.classification_id
and pec.classification_name = 'Involuntary Deductions'
and pec.legislation_code = 'US'
and pec.business_group_id IS NULL
and fnd_number.canonical_to_number(pet.element_information5) = pet_calc.element_type_id
and pet_calc.element_name like pet.element_name || '%Calculator'
and pet_calc.element_type_id = piv_pay.element_type_id
and NVL(ppa.date_earned, ppa.effective_date) between piv_pay.effective_start_date and piv_pay.effective_end_date
and piv_pay.input_value_id = prrv_pay.input_value_id
and upper(piv_pay.name) = 'PAY VALUE'
and fnd_number.number_to_canonical(ppp.value) = prrv_pay.result_value
and prr_pay.run_result_id = prrv_pay.run_result_id
and prr_pay.assignment_action_id = paa.assignment_action_id
and prr_pay.element_type_id = pet_calc.element_type_id
and NVL(ppa.date_earned, ppa.effective_date) between pet.effective_start_date
and pet.effective_end_date
and NVL(ppa.date_earned, ppa.effective_date) between pet_calc.effective_start_date
and pet_calc.effective_end_date
order by prr_att.run_result_id;
select peev.screen_entry_value garn_ref_no
,round(ppp.value ,2)
,TO_CHAR(pay_us_payment_pkg.get_trx_date( NULL, --:business_group_id,
NULL, --:ppa_payroll_action_id,
NULL,
NULL, --:ppa_payroll_id,
NULL, --:ppa_consolidation_set_id,
NULL, --:ppa_org_payment_method_id,
NULL, --:ppa_effective_date,
NULL, --:ppa_date_earned,
NULL, --:ppa_override_date,
p_pre_payment_id, --ppp.pre_payment_id,
NULL --ppa.effective_date
),'DD-MON-YYYY') payment_date
,TO_CHAR(ppa.date_earned, 'DD-MON-YYYY')
,prr_pay.run_result_id
,peef.entry_information2 -- FIPS code
,peef.entry_information1 -- Medical Indicator
,peef.entry_information3 -- DOC Number
,paa.assignment_action_id
,peef.element_type_id
from pay_element_entry_values_f peev,
pay_element_entry_values_f peev_pay,
pay_input_values_f piv_att,
pay_element_entries_f peef,
pay_run_results prr_att,
pay_run_results prr_pay,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai,
pay_pre_payments ppp,
pay_input_values_f piv_pay,
pay_run_result_values prrv_att,
pay_run_result_values prrv_pay
where round(ppp.value ,2) = round(p_value ,2)
and ppp.pre_payment_id = p_pre_payment_id
and ppp.assignment_action_id = pai.locking_action_id
and pai.locked_action_id = paa.assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('R', 'Q')
and ((paa.source_action_id is not null and ppa.run_type_id is not null) or
(paa.source_action_id is null and ppa.run_type_id is null))
and ppp.personal_payment_method_id = peef.personal_payment_method_id
and peef.assignment_id = paa.assignment_id
and ppa.date_earned between peev.effective_start_date
and peev.effective_end_date
and ppa.date_earned between peef.effective_start_date
and peef.effective_end_date
and piv_att.input_value_id = peev.input_value_id
and upper(piv_att.name) = 'ATTACHMENT NUMBER'
and ppa.effective_Date between piv_att.effective_start_date
and piv_att.effective_end_date
and piv_att.input_value_id = prrv_att.input_value_id
and prrv_att.result_value = peev.screen_entry_value
and piv_pay.input_value_id = peev_pay.input_value_id
and ppa.effective_date between piv_pay.effective_start_date
and piv_pay.effective_end_date
and ppa.date_earned between peev_pay.effective_start_date
and peev_pay.effective_end_date
and piv_pay.input_value_id = prrv_pay.input_value_id
and upper(piv_pay.name) = 'PAY VALUE'
and fnd_number.number_to_canonical(ppp.value) = prrv_pay.result_value
and peef.element_entry_id = peev.element_entry_id
and peef.element_entry_id = peev_pay.element_entry_id
and paa.assignment_Action_id = prr_att.assignment_Action_id
and prr_att.run_result_id = prrv_att.run_result_id
and paa.assignment_action_id = prr_pay.assignment_action_id
and prr_pay.run_result_id = prrv_pay.run_result_id
and prr_att.element_type_id = peef.element_type_id
and prr_pay.element_type_id = peef.element_type_id
order by prr_pay.run_result_id;
select distinct 'Y'
from pay_upgrade_definitions pud
,pay_upgrade_status pus
where pud.short_name = 'US_INV_DEDN_UPGRADE'
and pud.legislation_code = 'US'
and pud.upgrade_definition_id = pus.upgrade_definition_id
and pus.status = 'C'
and pus.business_group_id = p_business_group_id;
select parameter_value
from pay_action_parameters
where parameter_name = 'US_ADVANCED_WAGE_ATTACHMENT';
select prrv.result_value
from pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_element_types_f pet,
pay_element_types_f pet1
where prr.assignment_action_id = ass_act_id and
pet1.element_type_id = ele_type_id and
pet.element_name = pet1.element_name ||' Calculator' and
prr.element_type_id = pet.element_type_id and
prrv.run_result_id = prr.run_result_id and
prrv.input_value_id = piv.input_value_id and
piv.element_type_id = prr.element_type_id and
piv.name='Jurisdiction' and
date_ear between piv.effective_start_date and piv.effective_end_date and
date_ear between pet.effective_start_date and pet.effective_end_date and
date_ear between pet1.effective_start_date and pet1.effective_end_date;
select cnty_information1
from pay_us_county_tax_info_f
where jurisdiction_code = jur_code and
date_ear between effective_start_date and effective_end_date;
select sta_information12,sta_information13
from pay_us_state_tax_info_f
where state_code = st_code and
date_ear between effective_start_date and effective_end_date;
select county_name
from pay_us_counties
where state_code = substr(jur_code,1,2) and
county_code = substr(jur_code,4,3);
select pet.element_type_id
,to_char(ppa.date_earned, 'DD-MM-YYYY')
,nvl(element_information4,-1)
from pay_element_entry_values_f peev,
pay_input_values_f piv_att,
pay_element_entries_f peef,
pay_run_results prr_att,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai,
pay_pre_payments ppp,
pay_run_result_values prrv_att,
pay_element_types_f pet,
pay_element_classifications pec
WHERE round(ppp.value ,2) = round(p_value ,2)
and ppp.pre_payment_id = p_pre_payment_id
and ppp.assignment_action_id = pai.locking_action_id
and pai.locked_action_id = paa.assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('R', 'Q')
and ((paa.source_action_id is not null and ppa.run_type_id is not null) or
(paa.source_action_id is null and ppa.run_type_id is null))
and ppp.personal_payment_method_id = peef.personal_payment_method_id
and peef.assignment_id = paa.assignment_id
and ppa.date_earned between peev.effective_start_date
and peev.effective_end_date
and ppa.date_earned between peef.effective_start_date
and peef.effective_end_date
and piv_att.input_value_id = peev.input_value_id
and upper(piv_att.name) = 'ATTACHMENT NUMBER'
and ppa.date_earned between piv_att.effective_start_date
and piv_att.effective_end_date
and piv_att.input_value_id = prrv_att.input_value_id
and prrv_att.result_value = peev.screen_entry_value
and prrv_att.run_result_id = prr_att.run_result_id
and paa.assignment_Action_id = prr_att.assignment_Action_id
and prr_att.element_type_id = peef.element_type_id
and peef.element_type_id = pet.element_type_id
and pet.classification_id = pec.classification_id
and ppa.date_earned between pet.effective_start_date
and pet.effective_end_date
and pec.classification_name = 'Involuntary Deductions'
and pec.legislation_code = 'US'
and pec.business_group_id IS NULL;
select to_char(min(petf_calc.creation_date),'DD-MM-YYYY')
from pay_element_types_f petf_calc,
pay_element_types_f petf
where petf.element_type_id = ele_type_id
and petf_calc.element_name like petf.element_name||'\_New_ Calculator' escape '\';
select paa.assignment_action_id
from pay_assignment_actions paa
where paa.source_action_id = cp_pre_pay_action_id
and paa.assignment_id = cp_assignment_id
and paa.action_status = 'C';
select ppp.value amount,
ppp.pre_payment_id,
popm.org_payment_method_id,
popm.org_payment_method_name,
pppm.personal_payment_method_id,
pppm.payee_id,
pppm.payee_type
from pay_assignment_actions paa,
pay_pre_payments ppp,
pay_org_payment_methods_f popm ,
pay_personal_payment_methods_f pppm,
pay_payment_types ppt
where ppt.payment_type_id = popm.payment_type_id
and ppt.category = 'CH'
and paa.assignment_action_id = cp_pre_pay_action_id
and ppp.assignment_action_id = paa.assignment_action_id
and paa.assignment_id = cp_assignment_id
and ( ( ppp.source_action_id is null
and cp_ppp_source_action_id is null)
or
-- is it a Normal or Process Separate specific
-- Payments should be included in the Standard
-- SOE. Only Separate Payments should be in
-- a Separate SOE.
(ppp.source_action_id is not null
and cp_ppp_source_action_id is null
and exists (
select ''
from pay_run_types_f prt,
pay_assignment_actions paa_run,
pay_payroll_actions ppa_run
where paa_run.assignment_action_id
= ppp.source_action_id
and paa_run.payroll_action_id
= ppa_run.payroll_action_id
and paa_run.run_type_id = prt.run_type_id
and prt.run_method in ('P', 'N')
and ppa_run.effective_date
between prt.effective_start_date
and prt.effective_end_date
)
)
or
(cp_ppp_source_action_id is not null
and ppp.source_action_id = cp_ppp_source_action_id)
)
and ppp.org_payment_method_id = popm.org_payment_method_id
and popm.defined_balance_id is null
and pppm.personal_payment_method_id(+)
= ppp.personal_payment_method_id
and cp_curr_pymt_eff_date between popm.effective_start_date
and popm.effective_end_date
and cp_curr_pymt_eff_date between nvl(pppm.effective_start_date,
cp_curr_pymt_eff_date)
and nvl(pppm.effective_end_date,
cp_curr_pymt_eff_date);
select initcap(rtrim(ppf.title))||' '||
rtrim(ppf.first_name)||' '||rtrim(ppf.last_name),
addr.address_line1,
addr.address_line2,
addr.address_line3,
rtrim(addr.town_or_city),
addr.region_2,
addr.postal_code
from
per_addresses addr,
per_all_people_f ppf
where
ppf.person_id = cp_payee_id
and ppf.business_group_id = cp_business_group_id
and cp_payment_date between ppf.effective_start_date
and ppf.effective_end_date
and addr.person_id = ppf.person_id
and addr.primary_flag = 'Y'
and cp_payment_date between addr.date_from
and nvl(addr.date_to, cp_payment_date);
select hou.name,
loc.address_line_1,
loc.address_line_2,
loc.address_line_3,
rtrim(loc.town_or_city),
loc.region_2,
loc.postal_code
from
hr_locations_all loc,
hr_all_organization_units hou
where
hou.organization_id = cp_payee_id
and hou.business_group_id = cp_business_group_id
and cp_payment_date between hou.date_from
and nvl(hou.date_to, cp_payment_date)
and loc.location_id = hou.location_id;
select org_information9
from hr_organization_information
where org_information_context = 'Business Group Information'
and organization_id = cp_business_group_id;
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;
select paf.business_group_id
from per_assignments_f paf
where paf.assignment_id = p_assignment_id
and p_curr_pymt_eff_date between paf.effective_start_date
and paf.effective_end_date;
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 paf1.payroll_id = cp_payroll_id -- #9242223
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;