[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
de-selected from the Quickpay screen are being archived
with null values.
04-May-2012 Pkoduri 115.135 13881512 In case of an employee having bounus with separate check then
retro elements are not being individually archived.
For this the fix is to revert the chagnes done for the
bug 9207953.
27-Mar-2012 Pkoduri 115.134 13877047 In this version the fix reverted in the earlier version is
addressed again but this time carefully handling the YTD issue
reported in the bug 13785386.
02-Mar-2012 pkoduri 115.132 13785386 Reverting the changes done to the procedure
Archive_retro_element as that is multiplying the
YTD Amount and Hours values for a Retro feedin base element case.
However the changes for the bug#12548215 will be analyzed
and will be introduced once again.
07-Feb-2012 sbachu 115.131 8544619 Archiving the rate value rounding it
off to five digits after decimal.
17-Jan-2012 abellur 115.130 13554840 Updated populate_elements, so now
elements without rate and with
non zero YTD hours will be archived.
21-Nov-2012 tclewis 115.129 added emp_city_jd.delete
18-Nov-2011 Pkoduri 115.127 12903160 Skipping the rate derivation formula
in case multiple is 1- here we get the
user keyed in rate and store it.
27-Sep-2011 Pkoduri 115.126 12819017 Changed the precision for Round
function for calculated rate to 5-
this is to be in sync with SOE and
RATE on all other PDF slips.
21-Sep-2011 nkjaladi 115.125 11897283 Modified cursor get_run_action_id
of procedure get_current_elements
to add one more parameter
element_type_id so that correct
run_action_id is picked up for retro
and additional
21-Sep-2011 abellur 115.124 12548215 Updated archive_retro_elements,
logic for derived rate is updated.
12-Aug-2011 abellur 115.122 12548215 Changed archive_retro_elements
to pull information from base element
when hours are not populated and derive rate.
Also, YTD hours included into loop so it
calculates for every retro element.
03-Dec-2010 tclewis 115.19 10277479 Performance changes on cursors
c_prev_ytd_action_elem_rbr,
c_check_baladj.
17-May-2010 asgugupt 115.116 10069088 Modified Populate_elements
17-May-2010 mikarthi 115.113 8618524 Modified Populate_elements
03-Feb-2010 mikarthi 115.112 8688998 Added the Overloaded version
of procedure get_last_xfr_info.
Modified cursor c_last_xft_elements
18-Dec-2009 nkjaladi 115.111 9207953 Reverted the changes done for bug 8688998 in
order to remove dependency.
18-Dec-2009 nkjaladi 115.110 9207953 Modified the cursor get_run_action_id in procedure
get_current_elements such that payroll process with
seperate check are not picked.
19-Oct-2009 kagangul 115.109 8688998 Added the Overloaded version
of procedure get_last_xfr_info.
20-Aug-2009 asgugupt 115.108 8324157 Added hint to improve
performance of process
30-Jan-2009 sudedas 115.107 8211926 Changed get_current_elements
22-Jan-2009 sudedas 115.106 7661112 Changed archive_retro_element
,get_current_element for perf.
12-DEC-2008 tclewis 115.105 Added SDI1 EE.
04-DEC-2008 tclewis 115.105 Added SUI1 EE.
19-NOV-2008 sudedas 115.104 7580440 Changed get_current_elements
,Archive_retro_element procs.
16-SEP-2008 sudedas 115.103 7348767 Modified get_xfr_elements,
7348838 get_missing_xfr_info to
populate action_info24
11-SEP-2008 asgugupt 115.102 7197824 Changed get_run_results and
get_run_results_rate cursors
in Proc Archive_addnl_elements
23-JUN-2008 sudedas 115.101 7197824 Changed get_current_element
,archive_retro_element
,archive_addnl_element
for Work at Home Condition
02-JUN-2008 sapalani 115.100 7120430 Used fnd_number.canonical_to_number
in procedure populate_summary.
Removed trace_off at the end of
procedure populate_hours_x_rate.
14-APR-2008 asgugupt 115.99 6950970 Modified get_current_elements
29-FEB-2008 sudedas 115.98 6663135 Changed other similar cursors
20-FEB-2008 sudedas 115.97 6831411 Kept Code for Canada intact
before US California OT Enh
Changed Cursors in get_current
_elements
23-DEC-2007 sudedas 115.96 6702864 Reverted Back Changes of 115.95
Changed get_current_elements,
Archive_retro_element
populate_elements Changed.
Changes on Top of 115.94
20-DEC-2007 sudedas 115.95 Changed get_current_elements,
Archive_retro_element,
Archive_addnl_elements
03-DEC-2007 tclewis 115.94 6663135 Removed the code processing cursor
retro_parent_check_flag and use check_retro
instead as its identical code.
22-SEP-2007 sausingh 115.93 5635335 Cahnged to archive ) value in case the YTD
value is null .
22-SEP-2007 Ahanda 115.92 5635335 Made changes in the to get the the
orignating date when offset date was
mentioned.
15-sep-2007 sausingh 115.91 5635335 Added nvl condition
13-sep-2007 sausingh 115.90 5635335 Added nvl condition while archiving ytd and
current amount in case of earnings and
deduction ( withelds)
5-Sep-2007 sausingh 115.88 6392875 Archiving rate through balance call
in populate_elements
03-Aug-2007 sausingh 115.87 5635335 Changes Archive_addnl_elements to calculate
ytd values from balance call
30-Aug-2007 sudedas 115.86 Changes Incorporated for Issues
found by Rick on Aug 24, 2007
23-Aug-2007 sudedas 115.82 Closing Cursors as per requirement.
21-AUG-2007 sausingh 115.81 Added action information24 to archive display name for deductions
17-Aug-2007 sausingh 115.80 5635335 Added two procedures Archive_retro_element
and Archive_addnl_elements to archive retro
elements in separate rows depending upon the
element_entry_id
30-Jul-2007 sausingh 115.79 5635335 Added cursors to archive Rate*Multiple
in a new segment Action_information22
06-Jun-2007 sausingh 115.78 5635335 Changed get_current_elements
to archive Original Date Earned.
15-NOV-2006 ahanda 115.77 Changed sql statement to
use base table instead secure
views.
27-OCT-2006 ahanda 115.76 5582224 Checking PL/SQL table count > 0
before starting loop.
12-OCT-2006 ppanda 115.75 5599167 Cursor c_check_baladj changed by
adding hint leading(PPA)
index(PPA,PAY_PAYROLL_ACTIONS_N51)
index(PAA,PAY_ASSIGNMENT_ACTIONS_N51)
Cursor c_prev_elements modified by
adding hint
ORDERED use_nl(PAA, PPA, PPF)
19-SEP-2006 sodhingr 115.74 5549032 Added ORDERED hint to c_prev_elements
11-JUL-2006 ppanda 115.73 Changed cursor c_prev_ytd_action_elements
for fixing R12 performance bug 5042715
13-APR-2006 ahanda 115.72 Changed populate_hours_x_rate
to use amount returned by
pay_hours_by_rate_v
08-Mar-2006 vpandya 115.71 Changed populate_hours_x_rate
procedure to fix retro issue
for Canada.
14-OCT-2005 ahanda 115.70 Changed the prev_ytd .. cursors
to not do a trunc on year but
pass it as a parameter.
06-OCT-2005 ahanda 115.69 4552807 Added process_baladj_elements
28-JUL-2005 ahanda 115.68 4507782 Changed cursor
c_multi_asg_prev_information
29-DEC-2004 ahanda 115.67 4069477 Changed procedure populate_elements
to remove special logic for
Non Payroll Payments
06-OCT-2004 ahanda 115.66 3940380 Added parameter p_xfr_action_id
to get_last_xfr_info and check
in cursor.
30-JUL-2004 ssattini 115.65 3498653 Added p_action_type parameter
to get_current_elements and
populate_elements procedures,
also added logic to archive
reversals and balance adjustments
in populate_elements procedure.
28-JUL-2004 vpandya 115.64 3780256 Added ORDERED hint to
c_prev_ytd_action_elem_rbr cursor.
Changed cursor c_last_xfr_elements
in get_xfr_element procedure to
get jurisdiction_code from previous
archived value.
19-JUL-2004 ahanda 115.63 3770899 Changed c_prev_ytd_action_elements
and c_prev_ytd_action_elem_rbr
to pick up elements processed from
1st and the passed date.
16-JUL-2004 ahanda 115.62 3767301 Added rpad and ltrim for state code
as JD in run balances might just
have a space.
16-JUL-2004 ahanda 115.61 3767301 Changed the run balance cursor
to do a substr on jurisdiction code
to ensure correct distinct JDs are
fetched. The table has JD values
like 05, 05-, 05-000-, 05-000-0.
20-MAY-2004 rsethupa 115.60 3639249 procedure process_additional_elements
set the balance context 'TAX_UNIT_ID'
to p_tax_unit_id in the beginning.
10-MAY-2004 ahanda 115.59 3567107 Changed get_xfr_elements procedure
to check if element is still valid
before archiving.
03-MAY-2004 kvsankar 115.58 3585754 Added a new cursor
'c_prev_ytd_action_elem_rbr'
which uses run balances to
retrieve the elements. This
cursor has to be executed instead
of 'c_prev_ytd_action_elements'
if Balance Initialization elements
are to be archived.
26-APR-2004 rsethupa 115.57 3559626 Removed code at the end of the
file that was used to initialize
the global variable
gv_correspondence_language of the
package pyempxfrp.pkb to get the
Accrual Information based on
Correspondance language.
16-APR-2004 rsethupa 115.56 3311866 US SS Payslip currency Format Enh.
Changed code to archive currency
in canonical format for the action
info categories 'AC EARNINGS',
'AC DEDUCTIONS', 'AC SUMMARY YTD'
and 'AC SUMMARY CURRENT'.
29-JAN-2004 rsethupa 115.55 3370112 11.5.10 Performance Changes
Modified cursor c_cur_action_elements
by removing the 'and exists' clause
28-JAN-2004 rsethupa 115.54 3370112 11.5.10 Performance Changes
14-JAN-2003 RMONGE 115.53 3360805 Remove hr. from pay_action_information
25-NOV-2003 vpandya 115.52 3280589 Changed get_xfr_elements:
modified cursor c_last_per_xfr_run.
07-NOV-2003 vpandya 115.51 3225286 Changed c_prev_ytd_action_elements
cursor and added condition for
Bal Adj (B) for action_type.
06-NOV-2003 vpandya 115.50 3239376 Changed get_xfr_elements:
Retreving action_information12
(ytd_hours) and initializing
variable ln_ytd_hours.
04-NOV-2003 vpandya 115.49 3228457 Changed c_last_per_xfr_run cursor:
Remove extra table
pay_action_information.
20-OCT-2003 vpandya 115.48 3119792 Changed process_additional_elements:
calling populate_summary to archive
summary for YTD.
04-OCT-2003 ahanda 115.47 3107166 Added date joins when getting
data from pay_element_types_f
10-Sep-2003 ekim 115.46 3119792 1) Added procedure
2880047 - process_additional_elements
2) Moved c_prev_ytd_action_elements
to be global.
3) Added following in
get_last_xfr_info procedure.
Cursor:
- c_multi_asg_prev_information
- c_multi_asg_prev_nonsepchk
Parameter:
- p_sepchk_flag
26-JUN-2003 vpandya 115.45 2950628 Changed populate_summary to archive
labels for CURRENT and YTD based on
correspondence language of an
employee. Also added cursor
c_arch_labels.
19-JUN-2003 ahanda 115.44 3018135 Changed populate_summary to populate
values for Alien/Expat Earnings.
19-JUN-2003 ahanda 115.43 3016946 Changed cursor to do an nvl
reporting_name and element_name.
11-Apr-2003 vpandya 115.42 Changed get_xfr_elements:
Removed Multi GRE cond. which was
with Multi Asg and SepChk cond.
25-Mar-2003 vpandya 115.41 Changed populate_hours_x_rate:
Taken out 'Exit' from GRE loop
and put it at common place so that
it works for GRE and Tax Group.
17-Mar-2003 ekim 115.40 Added index hint in
c_last_payment_info cursor.
14-Mar-2003 ekim 115.39 2851780 Added c_last_per_xfr_run in
get_xfr_elements.
07-Mar-2003 vpandya 115.38 2834674 Changed populate_hours_x_rate:
Divided hours_by_rate cursor into
c_run_aa_id and c_hbr cursor.
24-Feb-2003 vpandya 115.37 Changed get_current_elements:
added cursor c_ytd_action_seq and
changed cursor c_cur_action_elements
to get sep check elements.
Changed get_xfr_elements:
archive all elements of previous
xfr run when gv_multi_gre_payment
is 'N'.
06-Feb-2003 ekim 115.36 2315822 changed get_xfr_elements:
Added logic to get YTD for
the elements in the previous run
for the given assignment when
Multi-Asg is 'Y' and SEPCHK = 'Y'
06-FEB-2003 vpandya 115.35 2657464 Changed to get translated name of
an element. Changed all cursors
wherever reporting name is taken
from pay_element_types_f, now it is
taking from pay_element_types_f_tl.
Also changed populate_hours_x_rate.
02-DEC-2002 ahanda 115.34 Changed package to fix GSCC warning
19-NOV-2002 vpandya 115.33 Calling set_error_message function
of pay_emp_action_arch from all
exceptions to get error message
Remote Procedure Calls(RPC or Sub
program)
13-NOV-2002 ahanda 115.32 2667749 Changed get_missing_xfr_info
to set the JD for Tax Deduction
and insert value only if non Zero
01-NOV-2002 ahanda 115.31 Changed error handling.
25-OCT-2002 ahanda 115.30 - Changed code to set up
hours_bal_id
only for earnings and
2503094 - Resetting the category in
get_missing_xfr_info.
15-OCT-2002 tmehra 115.29 Added code to archive PQP
(Alien) Earnings.
09-SEP-2002 ahanda 115.26 2558228 Modified code to only set the
Jurisdiction for Tax Deduction.
06-SEP-2002 ahanda 115.25 Added stmts for GSCC warnings.
27-JUL-2002 ahanda 115.24 Added code to get the primary
balance if it is null. This will
happen only to existing US
customers for Tax Deduction.
12-JUL-2002 ahanda 115.23 Setting JD Balance only for US
10-JUL-2002 vpandya 115.22 2455729 Modified populate_elements,
put condition like don't assign
hours to pl/sql table if ytd and
payment amounts are zero.
17-JUN-2002 ahanda 115.21 2365908 Changed package to populate tax
deductions if location has changed.
13-JUN-2002 vpandya 115.20 Added populate_hours_x_rate proc.
to populate Hours by Rate(HBR)
element.
Changed check_hours_by_rate to
check whether HBR element exists in
PL/SQL table. Setting context for
'Tax Group' if reporting level is
'TAXGRP'(Canadian Req.)
15-MAY-2002 ahanda 115.19 2339387 Changed get_xfr_elements to reset
the variable for category.
Added procedures
- get_last_xfr_info
- get_last_pymt_info
07-MAY-2002 vpandya 115.18 Modified populate_summanry,
Added 'Taxable Benefits' in it for
AC SUMMARY CURRENT, AC CURRENT YTD
24-APR-2002 ahanda 115.17 Changed get_current_elements for
performance.
08-APR-2002 ahanda 115.16 Changed
- get_missing_xfr_info
- get_current_elements
- first_time_process
to pass NULL for hours if the
classification is of type Dedutions
18-MAR-2002 ahanda 115.15 2264358 Changed cursor
c_prev_ytd_action_elements
Fixed archiving for Bal Adj for
which Pre Pay flag is checked.
22-JAN-2002 ahanda 115.14 Moved get_multi_assignment_flag
to global package (pyempxfr.pkb)
26-JAN-2002 ahanda 115.13 Added dbdrv commands.
22-JAN-2002 ahanda 115.12 Changed package to take care
of Multi Assignment Processing.
01-NOV-2001 asasthan 115.10 2034976
30-OCT-2001 asasthan 115.9 YTD Hours BUg
26-OCT-2001 asasthan 115.8 Fix for Bug 2080689
03-OCT-2001 asasthan 115.7 Fix for Bug 2028415
03-OCT-2001 asasthan 115.6 Fix for Bug 2028415
02-OCT-2001 vpandya 115.5 canada Changes
21-SEP-2001 asasthan 115.4 Removed check for 'Fees' from
get_current_elements etc.
31-AUG-2001 asasthan 115.3 Modified populate_delta_earnings
29-AUG-2001 asasthan 115.2 Modified ytd balance calls.
17-JUL-2001 vpandya 115.1 Added 'Taxable Benefits'
classification and 'Hours by Rate'
for CA.
25-JUL-2001 asasthan 115.0 Created.
*******************************************************************/
/******************************************************************
** Package Local Variables
******************************************************************/
gv_package VARCHAR2(100) := 'pay_ac_action_arch';
select pet.element_information10 primary_balance,
pet.element_information12 hours_balance
from pay_element_types_f pet
where pet.element_type_id = cp_element_type_id
and cp_effective_date between pet.effective_start_date
and pet.effective_end_date;
select /*+ ORDERED use_nl(PAA,PPA,PPF)
INDEX (paa PAY_ASSIGNMENT_ACTIONS_N51)
INDEX(ppa PAY_PAYROLL_ACTIONS_PK)
INDEX(prr PAY_RUN_RESULTS_N50)
INDEX(pcc PAY_ELEMENT_CLASSIFICATION_UK2) */
distinct
pec.classification_name,
pet.processing_priority,
nvl(decode(pec.classification_name,
'Tax Deductions', petl.reporting_name || ' Withheld',
petl.reporting_name),pet.element_name) reporting_name,
--pet.element_name,
decode(pec.classification_name,
'Tax Deductions', null,
prr.element_type_id) element_type_id,
--prr.element_type_id,
nvl(decode(pec.classification_name,
'Tax Deductions', prr.jurisdiction_code,
'Earnings',prr.jurisdiction_code), '00-000-0000'),
pet.element_information10,
pet.element_information12
from pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_run_results prr,
pay_element_types_f pet,
pay_element_classifications pec,
pay_element_types_f_tl petl
where prr.assignment_action_id = paa.assignment_action_id
and paa.assignment_id = cp_assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in (cp_action_type1, cp_action_type2, cp_action_type3)
and ppa.effective_date >= cp_start_eff_date
and ppa.effective_date <= cp_curr_eff_date
and pet.element_type_id = prr.element_type_id
and pet.element_information10 is not null
and ppa.effective_date between pet.effective_start_date
and pet.effective_end_date
and petl.element_type_id = pet.element_type_id
and petl.language = gv_person_lang
and pec.classification_id = pet.classification_id
and pec.business_group_id is NULL
and pec.legislation_code = 'US'
and pec.classification_name in ('Earnings',
'Alien/Expat Earnings',
'Supplemental Earnings',
'Imputed Earnings',
'Taxable Benefits',
'Pre-Tax Deductions',
'Involuntary Deductions',
'Voluntary Deductions',
'Non-payroll Payments',
'Tax Deductions')
and pet.element_name not like '%Calculator'
and pet.element_name not like '%Special Inputs'
and pet.element_name not like '%Special Features'
and pet.element_name not like '%Special Features 2'
and pet.element_name not like '%Verifier'
and pet.element_name not like '%Priority'
order by 1, 3, 4;
select /*+ ORDERED */
distinct pec.classification_name,
pet.processing_priority,
nvl(decode(pec.classification_name,
'Tax Deductions', petl.reporting_name || ' Withheld',
petl.reporting_name), pet.element_name) reporting_name,
decode(pec.classification_name, 'Tax Deductions', null,
pet.element_type_id) element_type_id,
nvl(decode(pec.classification_name,
'Tax Deductions',
decode(pec.legislation_code,
'CA', substr(jurisdiction_code,1,2),
decode(to_char(length(replace(jurisdiction_code, '-'))),
'7', jurisdiction_code,
rpad(nvl(substr(rtrim(ltrim(jurisdiction_code)),1,2),'0')
,2,'0') || '-'||
rpad(nvl(substr(rtrim(ltrim(jurisdiction_code)),4,3),'0')
,3,'0') ||'-' ||
rpad(nvl(substr(rtrim(ltrim(jurisdiction_code)),8,4),'0')
,4,'0')))), '00-000-0000') jurisdiction_code,
pet.element_information10,
pet.element_information12
from pay_element_classifications pec
,pay_element_types_f pet
,pay_balance_types pbt
,pay_defined_balances pdb
,pay_run_balances prb
,pay_element_types_f_tl petl
where prb.effective_date >= cp_start_eff_date
and prb.effective_date <= cp_curr_eff_date
and prb.assignment_id = cp_assignment_id
and pet.element_information10 is not null
and pet.element_information10 = pbt.balance_type_id
and pbt.balance_type_id = pdb.balance_type_id
and pdb.save_run_balance = 'Y'
and pdb.defined_balance_id = prb.defined_balance_id
and prb.effective_date between pet.effective_start_date and pet.
effective_end_date
and petl.element_type_id = pet.element_type_id
and petl.language = gv_person_lang
and pec.classification_id = pet.classification_id
and pec.classification_name in ('Earnings',
'Alien/Expat Earnings',
'Supplemental Earnings',
'Imputed Earnings',
'Taxable Benefits',
'Pre-Tax Deductions',
'Involuntary Deductions',
'Voluntary Deductions',
'Non-payroll Payments',
'Tax Deductions')
and pet.element_name not like '%Calculator'
and pet.element_name not like '%Special Inputs'
and pet.element_name not like '%Special Features'
and pet.element_name not like '%Special Features 2'
and pet.element_name not like '%Verifier'
and pet.element_name not like '%Priority'
order by 1, 3, 4;
select language, lookup_code, meaning
from fnd_lookup_values
where lookup_type = 'CA_CHEQUE_LABELS'
and lookup_code in ('CURRENT', 'YTD');
pay_ac_action_arch.lrr_act_tab.delete;
pay_ac_action_arch.emp_state_jd.delete;
pay_ac_action_arch.emp_city_jd.delete;
pay_ac_action_arch.emp_psd_jd.delete;
pay_ac_action_arch.emp_county_jd.delete;
pay_ac_action_arch.emp_school_jd.delete;
pay_ac_action_arch.emp_elements_tab.delete;
pay_ac_action_arch.lrr_act_tab.delete;
select pai.effective_date,
pai.action_context_id
from pay_action_information pai
where pai.action_context_type = 'AAP'
and pai.assignment_id = cp_assignment_id
and pai.action_information_category = cp_action_info_category
and pai.action_context_id <> cp_xfr_action_id
and pai.effective_date <= cp_effective_date
order by pai.effective_date desc
,pai.action_context_id desc;
select /*+ index(PAI PAY_ACTION_INFORMATION_N5) */ pai.effective_date,
pai.action_context_id
from per_all_assignments_f paf2
,per_all_assignments_f paf
,pay_action_information pai
where paf2.assignment_id = cp_assignment_id
and paf.person_id = paf2.person_id
and pai.assignment_id = paf.assignment_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = cp_action_info_category
and pai.effective_date <= cp_effective_date
and pai.effective_date >= trunc(cp_effective_date, 'Y')
and pai.action_context_id <> cp_xfr_action_id
order by pai.effective_date desc
,pai.action_context_id desc;
select ppa.payroll_id
from pay_payroll_actions ppa
,pay_assignment_actions paa
where paa.assignment_action_id=cp_xfr_action_id
and paa.payroll_action_id=ppa.payroll_action_id;
select pai.effective_date,
pai.action_context_id
from pay_action_information pai
where pai.action_context_type = 'AAP'
and pai.assignment_id = cp_assignment_id
and pai.action_information_category = cp_action_info_category
and pai.action_context_id <> cp_xfr_action_id
and pai.effective_date <= cp_effective_date
order by pai.effective_date desc
,pai.action_context_id desc;
select /*+ index(PAI PAY_ACTION_INFORMATION_N5) */ pai.effective_date,
pai.action_context_id
from per_all_assignments_f paf2
,per_all_assignments_f paf
,pay_action_information pai
where paf2.assignment_id = cp_assignment_id
and paf.person_id = paf2.person_id
and pai.assignment_id = paf.assignment_id
and paf.payroll_id = cp_payroll_id --condition added for the bug 14605761
and pai.action_context_type = 'AAP'
and pai.action_information_category = cp_action_info_category
and pai.effective_date <= cp_effective_date
and pai.effective_date >= trunc(cp_effective_date, 'Y')
and pai.action_context_id <> cp_xfr_action_id
order by pai.effective_date desc
,pai.action_context_id desc;
select ppa.effective_date, paa.assignment_action_id
from pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_id = p_assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('R','Q')
and ppa.effective_date < p_curr_pymt_eff_date
and ppa.effective_date in
( select /*+ index(ppa1, pay_payroll_Actions_pk) */
max(ppa1.effective_date)
from pay_payroll_actions ppa1,
pay_assignment_actions paa1
where ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.action_type in ('R','Q')
and paa1.assignment_id = p_assignment_id
and ppa1.effective_date < p_curr_pymt_eff_date);
select paa.assignment_action_id
from pay_action_interlocks pai,
pay_assignment_actions paa,
pay_payroll_actions ppa
where pai.locking_action_id = cp_pymt_assignment_action_id
and paa.assignment_action_id = pai.locked_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('Q','R')
and ((nvl(paa.run_type_id, ppa.run_type_id) is null and
source_action_id is null) or
(nvl(paa.run_type_id, ppa.run_type_id) is not null and
source_action_id is not null and
paa.run_type_id <> cp_sepchk_run_type_id));
select nvl(mul.multiple,1),mul.rate
from pay_hours_by_rate_v mul
where mul.assignment_action_id = cp_run_action_id
and legislation_code = 'US'
and mul.element_type_id >= 0 -- Bug 3370112
and mul.element_type_id = p_element_type_id
order by mul.processing_priority,mul.element_type_id;
/* Insert this into the plsql table if Current or YTD
amount is not Zero */
pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
:= lv_action_category;
select paa.assignment_action_id
,paa.run_type_id
from pay_assignment_actions paa,
pay_action_interlocks pai
where pai.locking_action_id = cp_pymt_action_id
and paa.assignment_action_id = pai.locked_action_id
and paa.assignment_id = cp_assignment_id
and paa.run_type_id is not null
and not exists ( select 1
from pay_run_types_f prt
where prt.legislation_code = 'CA'
and prt.run_type_id = paa.run_type_id
and prt.run_method = 'C' );
select hours.element_type_id,
hours.element_name,
hours.processing_priority,
hours.rate,
hours.multiple,
hours.hours,
hours.amount,
hours.assignment_action_id
from pay_hours_by_rate_v hours
where hours.assignment_action_id = cp_assignment_action_id
and legislation_code in ('US', 'CA') -- Bug 3370112
and hours.element_type_id >= 0 -- Bug 3370112
order by hours.processing_priority,hours.element_type_id;
select nvl(reporting_name, element_name)
from pay_element_types_f_tl
where element_type_id = cp_element_type_id
and language = cp_language;
select pec.classification_name,
pet.element_information10 primary_balance_id,
pet.element_information12 hours_balance_id
from pay_element_types_f pet,
pay_element_classifications pec
where pet.element_type_id = cp_element_type_id
and p_curr_pymt_eff_date between pet.effective_start_date
and pet.effective_end_date
and pec.classification_id = pet.classification_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;
hbr.delete;
/*Insert this into the plsql table */
hr_utility.set_location(gv_package || lv_procedure_name, 40);
SELECT paa_run.assignment_action_id
FROM pay_action_interlocks pai
,pay_assignment_actions paa_run
,pay_payroll_actions ppa_run
WHERE pai.locking_action_id = cp_pre_as_action_id
AND pai.locked_action_id = paa_run.assignment_action_id
AND paa_run.assignment_id = cp_assignment_id
AND paa_run.payroll_action_id = ppa_run.payroll_action_id
/* Added for Bug# 7580440 */
AND ppa_run.action_type IN ('R', 'Q')
AND ((nvl(paa_run.run_type_id, ppa_run.run_type_id) is null
and paa_run.source_action_id is null) or
(nvl(paa_run.run_type_id, ppa_run.run_type_id) is not null
and paa_run.source_action_id is not null
and paa_run.run_type_id <> cp_sepchk_run_type_id))
/* Added for Bug#9207953 */
/* AND NOT EXISTS
( SELECT 1
FROM pay_assignment_actions paa_run2
WHERE paa_run2.run_type_id is not null
AND paa_run2.source_action_id is not null
AND paa_run2.source_action_id = paa_run.source_action_id
AND paa_run2.run_type_id = cp_sepchk_run_type_id
)*/
/* Added for Bug#11897283 */
AND EXISTS
(
SELECT 1
FROM pay_run_results prr
WHERE prr.assignment_action_id = paa_run.assignment_action_id
AND prr.element_type_id = cp_element_type_id);
SELECT distinct peef.element_entry_id
FROM pay_element_entries_f peef,
pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp
WHERE paa.assignment_action_id = cp_run_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ptp.payroll_id = ppa.payroll_id
AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date
AND peef.assignment_id = cp_assignment_id
AND peef.element_type_id = cp_element_type_id
/* Commenting as Ele Entry Eff Start / End Date may not match the following
AND peef.effective_start_date BETWEEN ptp.start_date AND ptp.end_date
AND peef.effective_end_date BETWEEN ptp.start_date AND ptp.end_date
End of Comment */
AND NVL(ppa.date_earned, ppa.effective_date) BETWEEN peef.effective_start_date AND peef.effective_end_date ;
SELECT distinct 'Y'
FROM pay_element_entries_f peef,
pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp
WHERE paa.assignment_action_id = cp_run_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ptp.payroll_id = ppa.payroll_id
AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date
AND peef.assignment_id = cp_assignment_id
AND peef.element_type_id = cp_element_type_id
AND peef.creator_type IN ('R', 'EE', 'RR', 'NR', 'PR') -- Changed 25.08.2007
/* Commenting as Ele Entry Eff Start / End Date may not match the following
AND peef.effective_start_date BETWEEN ptp.start_date AND ptp.end_date
AND peef.effective_end_date BETWEEN ptp.start_date AND ptp.end_date
End of Comment*/
AND NVL(ppa.date_earned, ppa.effective_date) BETWEEN peef.effective_start_date AND peef.effective_end_date ;
SELECT DISTINCT 'Y'
FROM pay_element_entries_f peef,
pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp
WHERE paa.assignment_action_id = cp_run_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ptp.payroll_id = ppa.payroll_id
AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date
AND peef.assignment_id = cp_assignment_id
AND peef.element_type_id = cp_element_type_id
AND peef.creator_type NOT IN ('R', 'EE', 'RR', 'NR', 'PR') -- Changed on 25.08.2007
/* Commenting as Ele Entry Eff Start / End Date may not match the following
AND peef.effective_start_date BETWEEN ptp.start_date AND ptp.end_date
AND peef.effective_end_date BETWEEN ptp.start_date AND ptp.end_date
End of Comment */
AND NVL(ppa.date_earned, ppa.effective_date) BETWEEN peef.effective_start_date AND peef.effective_end_date ;
select fnd_date.date_to_canonical(ptp.start_date),
fnd_date.date_to_canonical(ptp.end_date),
hr_general.decode_lookup
(DECODE (UPPER (ec.classification_name),
'EARNINGS', 'US_EARNINGS',
'SUPPLEMENTAL EARNINGS', 'US_SUPPLEMENTAL_EARNINGS',
'IMPUTED EARNINGS', 'US_IMPUTED_EARNINGS',
'NON-PAYROLL PAYMENTS', 'US_PAYMENT',
'ALIEN/EXPAT EARNINGS', 'PER_US_INCOME_TYPES',
NULL
),
et.element_information1
) CATEGORY
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp,
pay_element_entries_f peef,
pay_element_classifications ec,
pay_element_types et
where paa.assignment_action_id = cp_run_assignment_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and ptp.payroll_id = ppa.payroll_id
and cp_original_date_paid between ptp.start_date AND ptp.end_date
and peef.element_entry_id = cp_element_entry_id
and et.element_type_id = peef.element_type_id
and et.classification_id = ec.classification_id
AND NVL(ppa.date_earned, ppa.effective_date) BETWEEN peef.effective_start_date AND peef.effective_end_date ; -- Bug# 16318258
SELECT application_column_name
FROM FND_DESCR_FLEX_COL_USAGE_VL
WHERE end_user_column_name = 'Originating Pay Period'
AND upper(descriptive_flexfield_name) = upper('PAY_ELEMENT_ENTRIES')
AND upper(descriptive_flex_context_code) = 'US EARNINGS';
SELECT fnd_flex.application_column_name
FROM fnd_application fnd_appl
,fnd_descr_flex_column_usages fnd_flex
WHERE fnd_appl.application_short_name = 'PAY'
AND fnd_appl.application_id = fnd_flex.application_id
AND fnd_flex.descriptive_flexfield_name = 'PAY_ELEMENT_ENTRIES'
AND UPPER(fnd_flex.descriptive_flex_context_code) = 'US EARNINGS'
and fnd_flex.end_user_column_name = 'Originating Pay Period';
SELECT COUNT (*)
FROM pay_element_entries_f peef,
pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp
WHERE paa.assignment_action_id = cp_run_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ptp.payroll_id = ppa.payroll_id
AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date
AND peef.assignment_id = cp_assignment_id
AND peef.element_type_id = cp_element_type_id
AND peef.creator_type NOT IN ('R', 'EE', 'RR', 'NR', 'PR')
/* Commenting as Ele Entry Eff Start / End Date may not match the following
AND peef.effective_start_date BETWEEN ptp.start_date AND ptp.end_date
AND peef.effective_end_date BETWEEN ptp.start_date AND ptp.end_date
End of Comment */
AND NVL(ppa.date_earned, ppa.effective_date) BETWEEN peef.effective_start_date AND peef.effective_end_date ;
SELECT NVL(paf.work_at_home, 'N')
,ppf.person_id
,ppf.business_group_id
FROM per_assignments_f paf
,per_all_people_f ppf
WHERE paf.assignment_id = cp_assignment_id
AND paf.person_id = ppf.person_id
and p_curr_pymt_eff_date between paf.effective_start_date and paf.effective_end_date
and p_curr_pymt_eff_date between ppf.effective_start_date and ppf.effective_end_date; -- Bug# 16318258
SELECT pus.state_code || '-000-0000'
FROM per_addresses pa
,pay_us_states pus
WHERE pa.person_id = cp_person_id
AND pa.primary_flag = 'Y'
AND p_curr_pymt_eff_date between pa.date_from AND NVL(pa.date_to, hr_general.END_OF_TIME)
AND pa.business_group_id = cp_bg_id
AND pa.region_2 = pus.state_abbrev
AND pa.style = p_legislation_code;
select distinct prr.element_type_id,
pec.classification_name,
nvl(petl.reporting_name, petl.element_name),
pet.element_information10,
pet.element_information12,
pet.processing_priority
from pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_run_results prr,
pay_element_types_f pet,
pay_element_classifications pec,
pay_element_types_f_tl petl
where paa.assignment_id = cp_assignment_id
and prr.assignment_action_id = paa.assignment_action_id
and cp_sepchk_flag = 'Y'
and paa.assignment_action_id = cp_pymt_action_id
and nvl(paa.run_type_id, cp_sepchk_run_type) = cp_sepchk_run_type
and ppa.payroll_action_id = paa.payroll_action_id
and pet.element_type_id = prr.element_type_id
and pet.element_information10 is not null
and ppa.effective_date between pet.effective_start_date
and pet.effective_end_date
and petl.element_type_id = pet.element_type_id
and petl.language = gv_person_lang
and pec.classification_id = pet.classification_id
and pec.classification_name in ('Earnings',
'Alien/Expat Earnings',
'Supplemental Earnings',
'Imputed Earnings',
'Taxable Benefits',
'Pre-Tax Deductions',
'Involuntary Deductions',
'Voluntary Deductions',
'Non-payroll Payments'
)
and pet.element_name not like '%Calculator'
and pet.element_name not like '%Special Inputs'
and pet.element_name not like '%Special Features'
and pet.element_name not like '%Special Features 2'
and pet.element_name not like '%Verifier'
and pet.element_name not like '%Priority'
order by pec.classification_name;
select distinct pet.element_type_id,
pec.classification_name,
nvl(petl.reporting_name, petl.element_name),
pet.element_information10,
pet.element_information12,
pet.processing_priority
from pay_action_interlocks pai,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_all_payrolls_f ppf,
pay_run_results prr,
pay_element_types_f pet,
pay_element_classifications pec,
pay_element_types_f_tl petl
where paa.assignment_id = cp_assignment_id
and prr.assignment_action_id = paa.assignment_action_id
and cp_sepchk_flag = 'N'
and pai.locking_action_id = cp_pymt_action_id
and paa.assignment_action_id = pai.locked_action_id
and paa.action_sequence <= cp_ytd_act_sequence
and ppa.payroll_action_id = paa.payroll_action_id
and pet.element_type_id = prr.element_type_id
and pet.element_information10 is not null
and ppa.effective_date between pet.effective_start_date
and pet.effective_end_date
and ppa.payroll_id = ppf.payroll_id -- Bug 3370112
and ppf.payroll_id >= 0
and ppa.effective_date between ppf.effective_start_date
and ppf.effective_end_date
and petl.element_type_id = pet.element_type_id
and petl.language = gv_person_lang
and pec.classification_id = pet.classification_id
and pec.classification_name in ('Earnings',
'Alien/Expat Earnings',
'Supplemental Earnings',
'Imputed Earnings',
'Taxable Benefits',
'Pre-Tax Deductions',
'Involuntary Deductions',
'Voluntary Deductions',
'Non-payroll Payments'
)
and pet.element_name not like '%Calculator'
and pet.element_name not like '%Special Inputs'
and pet.element_name not like '%Special Features'
and pet.element_name not like '%Special Features 2'
and pet.element_name not like '%Verifier'
and pet.element_name not like '%Priority'
order by pec.classification_name;
select paa.action_sequence
from pay_assignment_actions paa
where paa.assignment_action_id = cp_asg_act_id;
SELECT
TO_CHAR(TRUNC(fnd_date.canonical_to_date(fnd_date.date_to_canonical(ppa.date_earned))),'DD-MON-YYYY')
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.assignment_action_id = cp_run_action_id
AND ppa.payroll_action_id = paa.payroll_action_id;
SELECT 'select nvl('|| fdv.application_column_name || ',''AAA'')
from pay_element_entries_f where element_entry_id = :element_entry_id '
||' AND '
||' TO_DATE( :date_earned , ''DD-MON-YYYY'') '
||' BETWEEN effective_start_date AND effective_end_date '
into lv_sqlstr
FROM fnd_descr_flex_col_usage_vl fdv
WHERE fdv.application_id = 801
AND fdv.descriptive_flexfield_name LIKE 'PAY_ELEMENT_ENTRIES'
AND fdv.descriptive_flex_context_code = 'US EARNINGS';
SELECT 'select count(peef.'|| fdv.application_column_name || ') FROM pay_element_entries_f peef,
pay_assignment_actions paa, pay_payroll_actions ppa,
per_time_periods ptp WHERE paa.assignment_action_id = :run_assignment_action_id '
|| ' AND ppa.payroll_action_id = paa.payroll_action_id
AND ptp.payroll_id = ppa.payroll_id AND ppa.date_earned
BETWEEN ptp.start_date AND ptp.end_date AND peef.assignment_id = '
||' :assignment_id AND peef.element_type_id = :element_type_id '
|| ' AND NVL(ppa.date_earned, ppa.effective_date)
BETWEEN peef.effective_start_date AND peef.effective_end_date AND peef.'||fdv.application_column_name||' is not null'
into lv_sqlstr1
FROM fnd_descr_flex_col_usage_vl fdv
WHERE fdv.application_id = 801
AND fdv.descriptive_flexfield_name LIKE 'PAY_ELEMENT_ENTRIES'
AND fdv.descriptive_flex_context_code = 'US EARNINGS';
SELECT 'select max(nvl(peef.'|| fdv.application_column_name || ', ptp.start_date)) FROM pay_element_entries_f peef,
pay_assignment_actions paa, pay_payroll_actions ppa,per_time_periods ptp
WHERE paa.assignment_action_id = :run_assignment_action_id '
||' AND ppa.payroll_action_id = paa.payroll_action_id AND ptp.payroll_id = ppa.payroll_id
AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date AND
peef.assignment_id = :assignment_id AND peef.element_type_id =
:element_type_id AND NVL(ppa.date_earned, ppa.effective_date) BETWEEN
peef.effective_start_date AND peef.effective_end_date AND peef.' || fdv.application_column_name || ' is not null '
INTO lv_sqlstr_final
FROM fnd_descr_flex_col_usage_vl fdv
WHERE fdv.application_id = 801
AND fdv.descriptive_flexfield_name LIKE 'PAY_ELEMENT_ENTRIES'
AND fdv.descriptive_flex_context_code = 'US EARNINGS';
SELECT 'select nvl('|| fdv.application_column_name || ',''AAA'')
from pay_element_entries_f where element_entry_id = :element_entry_id '
into lv_sqlstr_date
FROM fnd_descr_flex_col_usage_vl fdv
WHERE fdv.application_id = 801
AND fdv.descriptive_flexfield_name LIKE 'PAY_ELEMENT_ENTRIES'
AND fdv.descriptive_flex_context_code = 'US EARNINGS';
SELECT TO_CHAR(TRUNC(fnd_date.canonical_to_date(fnd_date.date_to_canonical(p_curr_pymt_eff_date))),'DD-MON-YYYY')
INTO lv_curr_pymt_eff_date
FROM DUAL;
lv_sqlstr := 'select nvl(' || lv_application_column_name ||
',''AAA'') from pay_element_entries_f where element_entry_id = ' || ln_element_entry_id
||' AND '
||' TO_DATE('''
--bug no 6950970 starts here
-- || lv_curr_pymt_eff_date
|| l_date_earned
--bug no 6950970 ends here
||''', ''DD-MON-YYYY'') '
||' BETWEEN effective_start_date AND effective_end_date ';
lv_sqlstr1 := 'select count(peef.' || lv_application_column_name
||') FROM pay_element_entries_f peef, pay_assignment_actions paa, pay_payroll_actions ppa,per_time_periods ptp WHERE paa.assignment_action_id = '
|| ln_run_assignment_action_id
|| ' AND ppa.payroll_action_id = paa.payroll_action_id AND ptp.payroll_id = ppa.payroll_id AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date AND peef.assignment_id = '
|| p_assignment_id
||' AND peef.element_type_id = '
|| ln_element_type_id
|| ' AND NVL(ppa.date_earned, ppa.effective_date) BETWEEN peef.effective_start_date AND peef.effective_end_date AND peef.'
|| lv_application_column_name
|| ' is not null ' ;
select nvl((select peevf.screen_entry_value jurisdiction_code
from pay_input_values_f pivf,
pay_element_entry_values_f peevf
where pivf.element_type_id = ln_element_type_id
AND pivf.NAME = 'Jurisdiction'
AND peevf.element_entry_id = ln_element_entry_id
AND pivf.input_value_id = peevf.input_value_id
AND p_curr_pymt_eff_date between pivf.effective_start_date AND pivf.effective_end_date), -- Bug# 16318258
(SELECT distinct pus.state_code
|| '-'
|| puc.county_code
|| '-'
|| punc.city_code jurisdiction_code
FROM per_all_assignments_f peaf,
hr_locations_all hla,
pay_us_states pus,
pay_us_counties puc,
pay_us_city_names punc,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE peaf.assignment_id = p_assignment_id
AND paa.assignment_action_id = ln_run_assignment_action_id
AND peaf.location_id = hla.location_id
AND hla.region_2 = pus.state_abbrev
AND pus.state_code = puc.state_code
AND hla.region_1 = puc.county_name
AND hla.town_or_city = punc.city_name
AND pus.state_code = punc.state_code
AND puc.county_code = punc.county_code
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.effective_date between peaf.effective_start_date and peaf.effective_end_date
))
into lv_jurisdiction_flag
from dual;
select assignment_id, action_information_category,
action_information1 classification_name,
action_information2 element_type_id,
decode(cp_legislation_code,
'CA', jurisdiction_code,
'US', decode(jurisdiction_code, NULL, NULL,
decode(to_char(length(replace(jurisdiction_code,'-')))
,'7', jurisdiction_code,
rpad(nvl(substr(rtrim(ltrim(jurisdiction_code))
,1,2),'0'),2,'0') || '-'||
rpad(nvl(substr(rtrim(ltrim(jurisdiction_code))
,4,3),'0'),3,'0') ||'-' ||
rpad(nvl(substr(rtrim(ltrim(jurisdiction_code))
,8,4),'0'),4,'0')))) jurisdiction_code,
action_information6 primary_balance_id,
action_information7 processing_priority,
action_information9 ytd_amount,
action_information10 reporting_name,
effective_date effective_date,
action_information12 ytd_hours,
action_information24 display_name
from pay_action_information
where action_information_category in ('AC EARNINGS', 'AC DEDUCTIONS')
and action_context_id = cp_xfr_action_id;
select balance_type_id
from pay_balance_types
where legislation_code = cp_legislation_code
and balance_name = cp_balance_name;
SELECT /*+ ORDERED use_nl(PAA,PPA,PPF) */
DISTINCT
pec.classification_name,
pet.processing_priority,
decode(pec.classification_name,
'Tax Deductions',
nvl(petl.reporting_name, petl.element_name) || ' Withheld',
nvl(petl.reporting_name, petl.element_name)) reporting_name,
decode(pec.classification_name,
'Tax Deductions', null,
prr.element_type_id) element_type_id,
nvl(decode(pec.classification_name,
'Tax Deductions', prr.jurisdiction_code), '00-000-0000'),
pet.element_information10,
pet.element_information12
from PAY_ASSIGNMENT_ACTIONS PAA,
PAY_PAYROLL_ACTIONS PPA,
PAY_PAYROLLS_F PPF,
PAY_RUN_RESULTS PRR,
PAY_ELEMENT_TYPES_F PET ,
PAY_ELEMENT_CLASSIFICATIONS PEC,
PAY_ELEMENT_TYPES_F_TL PETL
/*changing the order for bug 5549032
pay_run_results prr,
pay_element_types_f pet ,
pay_element_classifications pec,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_element_types_f_tl petl,
pay_all_payrolls_f ppf */ -- Bug 3370112
where ppa.action_type in ('R', 'Q', 'B')
and ppa.effective_date > cp_last_xfr_eff_date
and ppa.effective_date <= cp_pymt_eff_date
and ppa.payroll_id = ppf.payroll_id
and ppf.payroll_id >= 0
and ppa.effective_date between ppf.effective_start_date
and ppf.effective_end_date
and paa.payroll_action_id = ppa.payroll_action_id
and paa.assignment_id = cp_assignment_id
and paa.assignment_action_id = prr.assignment_action_id
and pet.element_type_id = prr.element_type_id
and pet.element_information10 is not null
and ppa.effective_date between pet.effective_start_date
and pet.effective_end_date
and petl.element_type_id = pet.element_type_id
and petl.language = gv_person_lang
and pec.classification_id = pet.classification_id
and pec.classification_name in ('Earnings',
'Alien/Expat Earnings',
'Supplemental Earnings',
'Imputed Earnings',
'Taxable Benefits',
'Pre-Tax Deductions',
'Involuntary Deductions',
'Voluntary Deductions',
'Non-payroll Payments',
'Tax Deductions'
)
and pet.element_name not like '%Calculator'
and pet.element_name not like '%Special Inputs'
and pet.element_name not like '%Special Features'
and pet.element_name not like '%Special Features 2'
and pet.element_name not like '%Verifier'
and pet.element_name not like '%Priority'
order by 1, 3, 4;
select distinct business_group_id
from per_all_assignments_f
where assignment_id = p_assignment_id;
classifications and inserts two rows for CURRENT and
YTD Summary.
Arguments :
Notes :
******************************************************************/
PROCEDURE populate_summary(p_xfr_action_id in number)
IS
lv_earnings VARCHAR2(80):= 0;
/* Insert one row for CURRENT and one for YTD */
if pay_ac_action_arch.lrr_act_tab.count > 0 then
ln_step := 25;
and insert YTD balance to pl/sql table.
Arguments : p_assignment_id => Terminated Assignment Id
p_assignment_action_id => Max assignment action id
of given assignment
p_curr_eff_date => Current effective date
p_xfr_action_id => Current XFR action id.
Notes : This process is used to retrieve elements processed
in terminated assignments which is not picked up by
the archiver.
******************************************************************/
PROCEDURE process_additional_elements(p_assignment_id in number
,p_assignment_action_id in number
,p_curr_eff_date in date
,p_xfr_action_id in number
,p_legislation_code in varchar2
,p_tax_unit_id in number)
IS
lv_procedure_name VARCHAR2(50) := '.process_additional_elements';
and insert YTD balance to pl/sql table.
Arguments : p_assignment_id => Assignment Id
Notes : This process is used to retrieve elements processed
in balance adjustment but have never been processed in
payroll run.
******************************************************************/
PROCEDURE process_baladj_elements(
p_assignment_id in number
,p_xfr_action_id in number
,p_last_xfr_action_id in number
,p_curr_pymt_action_id in number
,p_curr_pymt_eff_date in date
,p_ytd_balcall_aaid in number
,p_sepchk_flag in varchar2
,p_sepchk_run_type_id in number
,p_payroll_id in number
,p_consolidation_set_id in number
,p_legislation_code in varchar2
,p_tax_unit_id in number)
IS
cursor c_check_baladj(cp_assignment_id in number
,cp_xfr_action_id in number
,cp_tax_unit_id in number
,cp_payroll_id in number
,cp_consolidation_set_id in number
,cp_curr_eff_date in date) is
select /*+ ORDERED */
min(ppa.effective_date)
from pay_assignment_actions paa
, pay_payroll_actions ppa
where ppa.action_type = 'B'
and paa.payroll_action_id = ppa.payroll_action_id
and paa.action_status = 'C'
and paa.assignment_action_id > cp_xfr_action_id
and paa.assignment_id = cp_assignment_id
and paa.tax_unit_id = cp_tax_unit_id
and ppa.effective_date >= trunc(cp_curr_eff_date, 'Y')
and ppa.effective_date <= cp_curr_eff_date
and ppa.payroll_id = cp_payroll_id
and ppa.consolidation_set_id = cp_consolidation_set_id;
select fnd_date.date_to_canonical(pay_paywsmee_pkg.get_original_date_earned(cp_element_entry_id)) ,
fnd_date.date_to_canonical(ptp.start_date),
fnd_date.date_to_canonical(ptp.end_date),
hr_general.decode_lookup
(DECODE (UPPER (ec.classification_name),
'EARNINGS', 'US_EARNINGS',
'SUPPLEMENTAL EARNINGS', 'US_SUPPLEMENTAL_EARNINGS',
'IMPUTED EARNINGS', 'US_IMPUTED_EARNINGS',
'NON-PAYROLL PAYMENTS', 'US_PAYMENT',
'ALIEN/EXPAT EARNINGS', 'PER_US_INCOME_TYPES',
NULL
),
et.element_information1
) CATEGORY
from pay_element_entries_f peef,
per_time_periods ptp,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_element_types_f et,
pay_element_classifications ec
where peef.element_entry_id = cp_element_entry_id
AND peef.creator_type IN ('EE', 'NR', 'PR', 'R', 'RR')
AND et.element_type_id = peef.element_type_id
AND et.classification_id = ec.classification_id
AND paa.assignment_action_id = cp_run_assignment_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ptp.payroll_id = ppa.payroll_id
AND pay_paywsmee_pkg.get_original_date_earned(cp_element_entry_id)
BETWEEN ptp.start_date
AND ptp.end_date
/*Bug# 16318258 - Start */
AND NVL(ppa.date_earned, ppa.effective_date) BETWEEN peef.effective_start_date AND
peef.effective_end_date
AND NVL(ppa.date_earned, ppa.effective_date) BETWEEN et.effective_start_date AND
et.effective_end_date;
SELECT peef.element_entry_id,
peef.creator_type,
peef.source_start_date
FROM pay_element_entries_f peef,
pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp
WHERE paa.assignment_action_id = cp_run_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ptp.payroll_id = ppa.payroll_id
AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date
AND peef.assignment_id = cp_assignment_id
AND peef.creator_id is NOT NULL
/* Following Added for Bug# 7580440 */
AND peef.creator_type IN ('EE', 'NR', 'PR', 'R', 'RR')
AND peef.element_type_id = cp_element_type_id
/* Commenting as Ele Entry Eff Start / End Date may not match the following
AND peef.effective_start_date BETWEEN ptp.start_date AND ptp.end_date
AND peef.effective_end_date BETWEEN ptp.start_date AND ptp.end_date
End of Comment */
AND NVL(ppa.date_earned, ppa.effective_date) BETWEEN peef.effective_start_date AND peef.effective_end_date
/*Start -- 14319807*/
AND EXISTS
(SELECT 1 FROM pay_run_results prr
WHERE
prr.assignment_action_id = paa.assignment_action_id
AND prr.element_entry_id = peef.element_entry_id
)
/*End -- 14319807*/
ORDER BY 3;
SELECT to_number(prrv.result_value), pivf.NAME
FROM pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f pivf
WHERE prr.assignment_action_id = cp_run_assignment_action_id
AND prr.element_entry_id = cp_element_entry_id
AND prrv.run_result_id = prr.run_result_id
AND prrv.input_value_id = pivf.input_value_id
AND pivf.NAME IN ('Pay Value', 'Hours', 'Rate')
AND cp_pymt_eff_date between pivf.effective_start_date
and pivf.effective_end_date -- Bug# 16318258
ORDER BY 2 ;
select PRRV.RESULT_VALUE as original_input_value
from pay_run_results PRR,
pay_run_result_values PRRV,
pay_input_values_f PIVF,
pay_entry_process_details PEPD
WHERE PEPD.ELEMENT_ENTRY_ID = cp_element_entry_id
AND PEPD.SOURCE_ENTRY_ID = PRR.SOURCE_ID
AND PEPD.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND PEPD.SOURCE_ELEMENT_TYPE_ID = PIVF.ELEMENT_TYPE_ID
AND PIVF.NAME in ('Hours')
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND PRRV.INPUT_VALUE_ID = PIVF.INPUT_VALUE_ID
AND cp_pymt_eff_date between pivf.effective_start_date
and pivf.effective_end_date; -- Bug# 16318258
SELECT to_number(prrv.result_value)
FROM pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f pivf
WHERE prr.assignment_action_id = cp_run_assignment_action_id
AND prr.element_entry_id = cp_element_entry_id
AND prrv.run_result_id = prr.run_result_id
AND prrv.input_value_id = pivf.input_value_id
AND pivf.NAME IN ('Rate')
AND cp_pymt_eff_date between pivf.effective_start_date
and pivf.effective_end_date; -- Bug# 16318258
SELECT NVL(paf.work_at_home, 'N')
,ppf.person_id
,ppf.business_group_id
FROM per_assignments_f paf
,per_all_people_f ppf
WHERE paf.assignment_id = cp_assignment_id
AND paf.person_id = ppf.person_id
and cp_pymt_eff_date between paf.effective_start_date and paf.effective_end_date
and cp_pymt_eff_date between ppf.effective_start_date and ppf.effective_end_date; -- Bug# 16318258
SELECT pus.state_code || '-000-0000'
FROM per_addresses pa
,pay_us_states pus
WHERE pa.person_id = cp_person_id
AND pa.primary_flag = 'Y'
AND p_pymt_eff_date between pa.date_from AND NVL(pa.date_to, hr_general.END_OF_TIME)
AND pa.business_group_id = cp_bg_id
AND pa.region_2 = pus.state_abbrev
AND pa.style = p_legislation_code;
SELECT nvl((select peevf.screen_entry_value jurisdiction_code
from pay_input_values_f pivf,
pay_element_entry_values_f peevf
where pivf.element_type_id = p_element_type_id
AND pivf.NAME = 'Jurisdiction'
AND peevf.element_entry_id = ln_element_entry_id
AND pivf.input_value_id = peevf.input_value_id
AND p_pymt_eff_date between pivf.effective_start_date AND pivf.effective_end_date), -- Bug# 16318258
(SELECT distinct pus.state_code
|| '-'
|| puc.county_code
|| '-'
|| punc.city_code jurisdiction_code
FROM per_all_assignments_f peaf,
hr_locations_all hla,
pay_us_states pus,
pay_us_counties puc,
pay_us_city_names punc,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE peaf.assignment_id = p_assignment_id
AND paa.assignment_action_id = p_run_assignment_action_id
AND peaf.location_id = hla.location_id
AND hla.region_2 = pus.state_abbrev
AND pus.state_code = puc.state_code
AND hla.region_1 = puc.county_name
AND hla.town_or_city = punc.city_name
AND pus.state_code = punc.state_code
AND puc.county_code = punc.county_code
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.effective_date between peaf.effective_start_date and peaf.effective_end_date
))
into lv_jurisdiction_flag
from dual;
/* Insert this into the plsql table if Current or YTD
amount is not Zero */
ln_step :=21;
select fnd_date.date_to_canonical(ptp.start_date),
fnd_date.date_to_canonical(ptp.end_date),
hr_general.decode_lookup
(DECODE (UPPER (ec.classification_name),
'EARNINGS', 'US_EARNINGS',
'SUPPLEMENTAL EARNINGS', 'US_SUPPLEMENTAL_EARNINGS',
'IMPUTED EARNINGS', 'US_IMPUTED_EARNINGS',
'NON-PAYROLL PAYMENTS', 'US_PAYMENT',
'ALIEN/EXPAT EARNINGS', 'PER_US_INCOME_TYPES',
NULL
),
et.element_information1
) CATEGORY
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp,
pay_element_entries_f peef,
pay_element_classifications ec,
pay_element_types et
where paa.assignment_action_id = cp_run_assignment_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and ptp.payroll_id = ppa.payroll_id
and nvl(cp_original_date_paid,ptp.start_date) between ptp.start_date AND ptp.end_date
and peef.element_entry_id = cp_element_entry_id
and et.element_type_id = peef.element_type_id
and et.classification_id = ec.classification_id
AND NVL(ppa.date_earned, ppa.effective_date) BETWEEN peef.effective_start_date AND peef.effective_end_date ; -- Bug# 16318258
select peef.element_entry_id,
peef.creator_type,
peef.source_start_date
FROM pay_element_entries_f peef,
pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp
WHERE paa.assignment_action_id = cp_run_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ptp.payroll_id = ppa.payroll_id
AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date
AND peef.assignment_id = cp_assignment_id
AND peef.element_type_id = cp_element_type_id
/* Commenting as Ele Entry Eff Start / End Date may not match the following
AND peef.effective_start_date BETWEEN ptp.start_date AND ptp.end_date
AND peef.effective_end_date BETWEEN ptp.start_date AND ptp.end_date
End of Comment */
AND NVL(ppa.date_earned, ppa.effective_date) BETWEEN peef.effective_start_date AND peef.effective_end_date
--ORDER BY 3;
SELECT to_number(prrv.result_value), pivf.NAME
FROM pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f pivf
WHERE prr.element_entry_id = cp_element_entry_id
--bug 7373188
and prr.assignment_action_id = cp_run_action_id
--bug 7373188
AND prrv.run_result_id = prr.run_result_id
AND prrv.input_value_id = pivf.input_value_id
AND pivf.NAME IN ('Pay Value', 'Hours')
AND cp_pymt_eff_date between pivf.effective_start_date and pivf.effective_end_date -- Bug# 16318258
ORDER BY 2 ;
SELECT to_number(prrv.result_value)
FROM pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f pivf
WHERE prr.element_entry_id = cp_element_entry_id
--bug 7373188
and prr.assignment_action_id = cp_run_action_id
--bug 7373188
AND prrv.run_result_id = prr.run_result_id
AND prrv.input_value_id = pivf.input_value_id
AND pivf.NAME IN ('Rate')
AND cp_pymt_eff_date between pivf.effective_start_date and pivf.effective_end_date; -- Bug# 16318258
SELECT NVL(paf.work_at_home, 'N')
,ppf.person_id
,ppf.business_group_id
FROM per_assignments_f paf
,per_all_people_f ppf
WHERE paf.assignment_id = cp_assignment_id
AND paf.person_id = ppf.person_id
and cp_pymt_eff_date between paf.effective_start_date and paf.effective_end_date
and cp_pymt_eff_date between ppf.effective_start_date and ppf.effective_end_date; -- Bug# 16318258
SELECT pus.state_code || '-000-0000'
FROM per_addresses pa
,pay_us_states pus
WHERE pa.person_id = cp_person_id
AND pa.primary_flag = 'Y'
AND p_pymt_eff_date between pa.date_from AND NVL(pa.date_to, hr_general.END_OF_TIME)
AND pa.business_group_id = cp_bg_id
AND pa.region_2 = pus.state_abbrev
AND pa.style = p_legislation_code;
lv_sqlstr1 := 'select max(nvl(peef.' || p_application_column_name ||', ptp.start_date)) FROM pay_element_entries_f peef, pay_assignment_actions paa, pay_payroll_actions ppa,per_time_periods ptp WHERE paa.assignment_action_id =' ;
lv_sqlstr := 'select nvl(' || p_application_column_name ||
',''AAA'') from pay_element_entries_f where element_entry_id = ' || ln_element_entry_id;
SELECT nvl((select peevf.screen_entry_value jurisdiction_code
from pay_input_values_f pivf,
pay_element_entry_values_f peevf
where pivf.element_type_id = p_element_type_id
AND pivf.NAME = 'Jurisdiction'
AND peevf.element_entry_id = ln_element_entry_id
AND pivf.input_value_id = peevf.input_value_id
AND p_pymt_eff_date between pivf.effective_start_date AND pivf.effective_end_date), -- Bug# 16318258
(SELECT distinct pus.state_code
|| '-'
|| puc.county_code
|| '-'
|| punc.city_code jurisdiction_code
FROM per_all_assignments_f peaf,
hr_locations_all hla,
pay_us_states pus,
pay_us_counties puc,
pay_us_city_names punc,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE peaf.assignment_id = p_assignment_id
AND paa.assignment_action_id = p_run_assignment_action_id
AND peaf.location_id = hla.location_id
AND hla.region_2 = pus.state_abbrev
AND pus.state_code = puc.state_code
AND hla.region_1 = puc.county_name
AND hla.town_or_city = punc.city_name
AND pus.state_code = punc.state_code
AND puc.county_code = punc.county_code
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.effective_date between peaf.effective_start_date and peaf.effective_end_date
))
into lv_jurisdiction_flag
from dual;
/* Insert this into the plsql table if Current or YTD
amount is not Zero */
ln_step :=21;
select fnd_date.date_to_canonical(ptp.start_date),
fnd_date.date_to_canonical(ptp.end_date),
hr_general.decode_lookup
(DECODE (UPPER (ec.classification_name),
'EARNINGS', 'US_EARNINGS',
'SUPPLEMENTAL EARNINGS', 'US_SUPPLEMENTAL_EARNINGS',
'IMPUTED EARNINGS', 'US_IMPUTED_EARNINGS',
'NON-PAYROLL PAYMENTS', 'US_PAYMENT',
'ALIEN/EXPAT EARNINGS', 'PER_US_INCOME_TYPES',
NULL
),
et.element_information1
) CATEGORY
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp,
pay_element_entries_f peef,
pay_element_classifications ec,
pay_element_types et
where paa.assignment_action_id = cp_run_assignment_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and ptp.payroll_id = ppa.payroll_id
and nvl(cp_original_date_paid,ptp.start_date) between ptp.start_date AND ptp.end_date
and peef.element_entry_id = cp_element_entry_id
and et.element_type_id = peef.element_type_id
and et.classification_id = ec.classification_id
AND NVL(ppa.date_earned, ppa.effective_date) BETWEEN peef.effective_start_date AND peef.effective_end_date ; -- Bug# 16318258
select peef.element_entry_id,
peef.creator_type,
peef.source_start_date
FROM pay_element_entries_f peef,
pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp
WHERE paa.assignment_action_id = cp_run_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ptp.payroll_id = ppa.payroll_id
AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date
AND peef.assignment_id = cp_assignment_id
AND peef.element_type_id = cp_element_type_id
/* Commenting as Ele Entry Eff Start / End Date may not match the following
AND peef.effective_start_date BETWEEN ptp.start_date AND ptp.end_date
AND peef.effective_end_date BETWEEN ptp.start_date AND ptp.end_date
End of Comment */
AND NVL(ppa.date_earned, ppa.effective_date) BETWEEN peef.effective_start_date AND peef.effective_end_date
--ORDER BY 3;
SELECT to_number(prrv.result_value), pivf.NAME
FROM pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f pivf
WHERE prr.element_entry_id = cp_element_entry_id
--bug 7373188
and prr.assignment_action_id = cp_run_action_id
--bug 7373188
AND prrv.run_result_id = prr.run_result_id
AND prrv.input_value_id = pivf.input_value_id
AND pivf.NAME IN ('Pay Value', 'Hours')
AND cp_pymt_eff_date between pivf.effective_start_date and pivf.effective_end_date -- Bug# 16318258
ORDER BY 2 ;
SELECT to_number(prrv.result_value)
FROM pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f pivf
WHERE prr.element_entry_id = cp_element_entry_id
--bug 7373188
and prr.assignment_action_id = cp_run_action_id
--bug 7373188
AND prrv.run_result_id = prr.run_result_id
AND prrv.input_value_id = pivf.input_value_id
AND pivf.NAME IN ('Rate')
AND cp_pymt_eff_date between pivf.effective_start_date and pivf.effective_end_date; -- Bug# 16318258
SELECT NVL(paf.work_at_home, 'N')
,ppf.person_id
,ppf.business_group_id
FROM per_assignments_f paf
,per_all_people_f ppf
WHERE paf.assignment_id = cp_assignment_id
AND paf.person_id = ppf.person_id
and cp_pymt_eff_date between paf.effective_start_date and paf.effective_end_date
and cp_pymt_eff_date between ppf.effective_start_date and ppf.effective_end_date; -- Bug# 16318258
SELECT pus.state_code || '-000-0000'
FROM per_addresses pa
,pay_us_states pus
WHERE pa.person_id = cp_person_id
AND pa.primary_flag = 'Y'
AND p_pymt_eff_date between pa.date_from AND NVL(pa.date_to, hr_general.END_OF_TIME)
AND pa.business_group_id = cp_bg_id
AND pa.region_2 = pus.state_abbrev
AND pa.style = p_legislation_code;
SELECT nvl((select peevf.screen_entry_value jurisdiction_code
from pay_input_values_f pivf,
pay_element_entry_values_f peevf
where pivf.element_type_id = p_element_type_id
AND pivf.NAME = 'Jurisdiction'
AND peevf.element_entry_id = ln_element_entry_id
AND pivf.input_value_id = peevf.input_value_id
AND p_pymt_eff_date between pivf.effective_start_date AND pivf.effective_end_date), -- Bug# 16318258
(SELECT distinct pus.state_code
|| '-'
|| puc.county_code
|| '-'
|| punc.city_code jurisdiction_code
FROM per_all_assignments_f peaf,
hr_locations_all hla,
pay_us_states pus,
pay_us_counties puc,
pay_us_city_names punc,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE peaf.assignment_id = p_assignment_id
AND paa.assignment_action_id = p_run_assignment_action_id
AND peaf.location_id = hla.location_id
AND hla.region_2 = pus.state_abbrev
AND pus.state_code = puc.state_code
AND hla.region_1 = puc.county_name
AND hla.town_or_city = punc.city_name
AND pus.state_code = punc.state_code
AND puc.county_code = punc.county_code
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.effective_date between peaf.effective_start_date and peaf.effective_end_date
))
into lv_jurisdiction_flag
from dual;
/* Insert this into the plsql table if Current or YTD
amount is not Zero */
ln_step :=21;