[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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 INDEX(PRB PAY_RUN_BALANCES_N1
,PDB PAY_DEFINED_BALANCES_PK
,PBT PAY_BALANCE_TYPES_PK,
,PET PAY_ELEMENT_TYPES_F_PK
,PEC PAY_ELEMENT_CLASSIFICATION_PK
,PETL PAY_ELEMENT_TYPES_F_TL_PK)
USE_NL(PRB, PDB, PBT, PET, PEC, PETL) */
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_run_balances prb
,pay_defined_balances pdb
,pay_balance_types pbt
,pay_element_types_f pet
,pay_element_classifications pec
,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.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_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 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.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));
/* 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
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.assignment_action_id
FROM pay_action_interlocks pai, pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE pai.locking_action_id =cp_pre_as_action_id
AND pai.locked_action_id = paa.assignment_action_id
AND paa.assignment_id = cp_assignment_id
AND paa.source_action_id IS NOT NULL
AND paa.payroll_action_id = ppa.payroll_action_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;
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 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;
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 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),(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
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 /*+ leading(PPA) index(PPA, PAY_PAYROLL_ACTIONS_N51)
index(PAA, PAY_ASSIGNMENT_ACTIONS_N51) */
min(ppa.effective_date)
from pay_payroll_actions ppa
,pay_assignment_actions paa
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 ;
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
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
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')
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
AND prrv.run_result_id = prr.run_result_id
AND prrv.input_value_id = pivf.input_value_id
AND pivf.NAME IN ('Rate');
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;
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),(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;
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')
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');
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;
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),(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;