[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
06-FEB-2013 emunisek 120.3 Resolved issues in update_asg_data
Procedure
07-FEB-2013 emunisek 120.4 Resolved issues with display of Tax
Deductions and Tax Withholding Details.
13-Feb-2013 emunisek 120.5 Made changes to allow user to enter
NULL values to Tax Amount and Override
Fields. Made changes to show Simulation
Name on Output also.
15-Feb-2013 emunisek 120.6 Made change to make sure Elements
associated to other Salary Basis than
Assignment's Salary Basis does not appear
in Earnings/Deductions LOV as their Element
Entry creation ends in error.
Made changes to make sure number related
fields are handled correctly with the
canonical conversion as required.
18-Feb-2013 emunisek 120.7 Made changes so that the City Tax gets
displayed in Simulation Output without
the Tag.
20-Feb-2013 emunisek 120.8 Made changes so that the Simulation Output
shows Employee and Employer details in the
event of Zero wages in the processing.
Also made changes to pickup the Exclusion
element set details from Record with Document
Type as Payroll Simulator.
22-Feb-2013 emunisek 120.9 Corrected the Query which gathers Element-Input
details to make sure that the total length is
restricted to 60 Characters.
07-Mar-2013 emunisek 120.10 16281614 Created procedure simulation_generate. This
was delivered earlier through package
pay_payroll_xml_extract_pkg. Added additional
procedures build_sql,print_blob and flex_seg_enabled
which are needed for simulation_generate
14-Mar-2013 emunisek 120.11 16482011 Made changes so that Employee Name and Employer
Address details get displayed in the same way as
in Payslip
08-Apr-2013 emunisek 120.12 16605117 Made changes so that Element Name under Rate Details
section displays Simulation Name if present.
*/
gv_package VARCHAR2(100) := 'pay_us_payslip_simulation_main';
SELECT language,
lookup_code,
meaning
FROM fnd_lookup_values
WHERE lookup_type = 'CA_CHEQUE_LABELS'
AND lookup_code IN ('CURRENT', 'YTD');
SELECT org_information1
FROM hr_organization_information hoi,
pay_payroll_actions ppa
where ppa.payroll_action_id = cp_payroll_action_id
and hoi.organization_id = ppa.business_group_id
and hoi.org_information_context = 'Payroll Archiver Level';
pay_us_payslip_simulation_main.lrr_act_tab.delete;
pay_us_payslip_simulation_main.emp_elements_tab.delete;
/* Procedure : update_asg_data
Purpose : This procedure is to update the Assignment related data as
per the modifications specified on Payroll Simulator page
so that Payroll calculations happen accordingly. The
details specified on Payroll Simulator page are stored to
table PER_ASSIGNMENT_EXTRA_INFO by Core Payroll. This
procedure determines the necessary updates to Assignment
data and carries them as required.
Important : The changes made in this procedure are on the actual data.
But since the entire Payroll simulation process is rolled
back at Database level, none of these changes will get
saved to the database.
*/
PROCEDURE update_asg_data(p_source_action_id NUMBER,
p_effective_date DATE DEFAULT NULL)
IS
CURSOR get_details (cp_source_action_id NUMBER)
IS
SELECT paa.assignment_id,
ppa.business_group_id,
ppa.effective_date,
ppa.date_earned
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.assignment_action_id = cp_source_action_id
AND ppa.payroll_action_id = paa.payroll_action_id;
SELECT pbg.legislation_code
FROM per_business_groups pbg
WHERE pbg.business_group_id = cp_business_group_id
AND cp_effective_date BETWEEN pbg.date_from
AND NVL(pbg.date_to,TO_DATE('31/12/4712','DD/MM/YYYY'));
SELECT paf.person_id,
paf.object_version_number
FROM per_all_assignments_f paf
WHERE paf.assignment_id = cp_assignment_id
AND cp_effective_date BETWEEN effective_start_date
AND effective_end_date;
SELECT address_id,
date_from,
date_to,
object_version_number
FROM per_addresses
WHERE person_id = cp_person_id
AND cp_effective_date BETWEEN date_from
AND NVL(date_to,TO_DATE('31-12-4712','DD-MM-YYYY'))
AND primary_flag = 'Y'
AND style = cp_legislation_code;
SELECT NVL(hl.loc_information17,hl.region_2)
FROM hr_locations_all hl
WHERE hl.location_id = cp_location_id;
SELECT MIN(puzc.zip_start)
FROM pay_us_zip_codes puzc
WHERE puzc.state_code = SUBSTR(cp_jurisdiction_code,1,2)
AND puzc.county_code = SUBSTR(cp_jurisdiction_code,4,3)
AND puzc.city_code = SUBSTR(cp_jurisdiction_code,8,4);
SELECT paei.aei_information_category,
paei.aei_information1,
paei.aei_information2,
paei.aei_information3,
paei.aei_information4,
paei.aei_information5,
paei.aei_information6,
paei.aei_information7,
paei.aei_information8,
paei.aei_information9,
paei.aei_information10,
paei.aei_information11,
paei.aei_information12,
paei.aei_information13,
paei.aei_information14,
paei.aei_information15,
paei.aei_information16,
paei.aei_information17,
paei.aei_information18,
paei.aei_information19,
paei.aei_information20,
paei.aei_information21,
paei.aei_information22,
paei.aei_information23,
paei.aei_information24,
paei.aei_information25,
paei.aei_information26,
paei.aei_information27,
paei.aei_information28,
paei.aei_information29,
paei.aei_information30
FROM per_assignment_extra_info paei
WHERE paei.assignment_id = cp_assignment_id
AND (paei.aei_information_category
LIKE '%'||cp_legislation_code||'_SIMULATION_REGION1'
OR
paei.aei_information_category
LIKE '%'||cp_legislation_code||'_SIMULATION_REGION2'
);
SELECT pft.emp_fed_tax_rule_id,
pft.object_version_number
FROM pay_us_emp_fed_tax_rules_f pft
WHERE pft.assignment_id = cp_assignment_id
AND cp_effective_date BETWEEN pft.effective_start_date
AND pft.effective_end_date;
SELECT pst.emp_state_tax_rule_id,
pst.object_version_number
FROM pay_us_emp_state_tax_rules_f pst,
pay_us_states pus
WHERE pst.assignment_id = cp_assignment_id
AND cp_effective_date BETWEEN pst.effective_start_date
AND pst.effective_end_date
AND pst.state_code = pus.state_code
AND pus.state_abbrev = cp_state_abbrev;
SELECT hsck.segment18
FROM hr_soft_coding_keyflex hsck,
per_all_assignments_f paf
WHERE hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
AND paf.assignment_id = cp_assignment_id
AND cp_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date;
lv_procedure_name VARCHAR2(100) := '.update_asg_data';
lv_datetrack_update_mode VARCHAR2(50) := 'CORRECTION';
lb_spp_delete_warning BOOLEAN;
lv_procedure_name VARCHAR2(100) := '.update_asg_data.value_changed';
updated with the latest details. This update will be done along with removal
of Taxation Override section of Address if any so that the latest address
will be considered. This is done to make sure that the latest address does not
get ignored in the event of Taxation Override address already present*/
/* Retrieve the State Abbreviation, County Name, City Name and Zip Code
to update the address */
SELECT pus.state_abbrev
INTO lv_resident_state
FROM pay_us_states pus
WHERE pus.state_code = lct_region1.aei_information1;
SELECT puc.county_name
INTO lv_resident_county
FROM pay_us_counties puc
WHERE puc.state_code = lct_region1.aei_information1
AND puc.county_code = SUBSTR(lct_region1.aei_information2,4,3);
on Date Earned is considered. So we will update the Address Record with the latest
address accordingly */
IF lv_resident_state = 'IN' THEN
OPEN get_address_details(ln_person_id,ld_effective_date,lv_legislation_code);
hr_person_address_api.update_us_person_address
( p_effective_date => ld_effective_date
,p_address_id => lct_address_details.address_id
,p_object_version_number => lct_address_details.object_version_number
,p_date_from => lct_address_details.date_from
,p_date_to => lct_address_details.date_to
,p_city => lv_resident_city
,p_county => lv_resident_county
,p_state => lv_resident_state
,p_zip_code => lv_resident_zip_code
,p_add_information18 => NULL
,p_add_information19 => NULL
,p_add_information17 => NULL
,p_add_information20 => NULL
);
/* This API call updates the Work At Home Preference also. If there is
change to this value, then it will also happen as we are passing the
latest value stored in lct_region1.aei_information5*/
hr_assignment_api.update_emp_asg
( p_effective_date => ld_effective_date
,p_datetrack_update_mode => lv_datetrack_update_mode
,p_assignment_id => ln_assignment_id
,p_object_version_number => ln_asg_object_version_number
,p_work_at_home => lct_region1.aei_information5
,p_segment18 => NULL
,p_cagr_grade_def_id => ln_cagr_grade_def_id
,p_cagr_concatenated_segments => lv_cagr_concatenated_segments
,p_comment_id => ln_comment_id
,p_soft_coding_keyflex_id => ln_soft_coding_keyflex_id
,p_effective_start_date => ld_effective_start_date
,p_effective_end_date => ld_effective_end_date
,p_concatenated_segments => lv_concatenated_segments
,p_no_managers_warning => lb_no_managers_warning
,p_other_manager_warning => lb_other_manager_warning
,p_hourly_salaried_warning => lb_hourly_salaried_warning
,p_gsp_post_process_warning => lb_gsp_post_process_warning
);
not present, then we need to call below API to update Work At Home value
and this needs to be done only if there is a change to Work At Home*/
hr_assignment_api.update_emp_asg
( p_effective_date => ld_effective_date
,p_datetrack_update_mode => lv_datetrack_update_mode
,p_assignment_id => ln_assignment_id
,p_object_version_number => ln_asg_object_version_number
,p_work_at_home => lct_region1.aei_information5
,p_cagr_grade_def_id => ln_cagr_grade_def_id
,p_cagr_concatenated_segments => lv_cagr_concatenated_segments
,p_comment_id => ln_comment_id
,p_soft_coding_keyflex_id => ln_soft_coding_keyflex_id
,p_effective_start_date => ld_effective_start_date
,p_effective_end_date => ld_effective_end_date
,p_concatenated_segments => lv_concatenated_segments
,p_no_managers_warning => lb_no_managers_warning
,p_other_manager_warning => lb_other_manager_warning
,p_hourly_salaried_warning => lb_hourly_salaried_warning
,p_gsp_post_process_warning => lb_gsp_post_process_warning
);
hr_assignment_api.update_emp_asg_criteria
( p_effective_date => ld_effective_date
,p_datetrack_update_mode => lv_datetrack_update_mode
,p_assignment_id => ln_assignment_id
,p_location_id => lct_region1.aei_information4
,p_object_version_number => ln_asg_object_version_number
,p_special_ceiling_step_id => ln_special_ceiling_step_id
,p_people_group_id => ln_people_group_id
,p_soft_coding_keyflex_id => ln_soft_coding_keyflex_id
,p_group_name => lv_group_name
,p_effective_start_date => ld_effective_start_date
,p_effective_end_date => ld_effective_end_date
,p_org_now_no_manager_warning => lb_no_managers_warning
,p_other_manager_warning => lb_other_manager_warning
,p_spp_delete_warning => lb_spp_delete_warning
,p_entries_changed_warning => lv_entries_changed_warning
,p_tax_district_changed_warning => lb_tax_dist_changed_warning
,p_concatenated_segments => lv_concatenated_segments
,p_gsp_post_process_warning => lb_gsp_post_process_warning
);
will be created by Update Assignment Code. The details selected on
Simulation Page are to be saved to the corresponding tax record */
lv_rs_state_tax_changed := 'Y';
SELECT pus.state_abbrev
INTO lv_resident_state
FROM pay_us_states pus
WHERE pus.state_code = lct_region1.aei_information1;
update will be sufficient. Work Tax Record update can be skipped as
we have same tax record for both Resident and Work States*/
IF lv_same_rs_wk_state = 'N' THEN
IF lv_wk_state_changed = 'Y' THEN
lv_wk_state_tax_changed := 'Y';
pay_federal_tax_rule_api.update_fed_tax_rule
(p_effective_date => ld_effective_date
,p_datetrack_update_mode => lv_datetrack_update_mode
,p_emp_fed_tax_rule_id => ln_emp_fed_tax_rule_id
,p_object_version_number => ln_ft_object_version_number
,p_filing_status_code => lct_region2.aei_information1
,p_fit_override_amount => NVL(fnd_number.canonical_to_number(lct_region2.aei_information6),0)
,p_fit_override_rate => NVL(fnd_number.canonical_to_number(lct_region2.aei_information5),0)
,p_withholding_allowances => NVL(fnd_number.canonical_to_number(lct_region2.aei_information3),0)
,p_fit_additional_tax => NVL(fnd_number.canonical_to_number(lct_region2.aei_information4),0)
,p_fit_exempt => lct_region2.aei_information2
,p_supp_tax_override_rate => NVL(fnd_number.canonical_to_number(lct_region2.aei_information7),0)
,p_effective_start_date => ld_effective_start_date
,p_effective_end_date => ld_effective_end_date
);
pay_state_tax_rule_api.update_state_tax_rule
(p_effective_date => ld_effective_date
,p_datetrack_update_mode => lv_datetrack_update_mode
,p_emp_state_tax_rule_id => ln_emp_state_tax_rule_id
,p_object_version_number => ln_st_object_version_number
,p_filing_status_code => LPAD(lct_region2.aei_information8,2,'0')
,p_sit_additional_tax => NVL(fnd_number.canonical_to_number(lct_region2.aei_information11),0)
,p_sit_override_amount => NVL(fnd_number.canonical_to_number(lct_region2.aei_information13),0)
,p_sit_override_rate => NVL(fnd_number.canonical_to_number(lct_region2.aei_information12),0)
,p_withholding_allowances => NVL(fnd_number.canonical_to_number(lct_region2.aei_information10),0)
,p_sit_exempt => lct_region2.aei_information9
,p_supp_tax_override_rate => NVL(fnd_number.canonical_to_number(lct_region2.aei_information14),0)
,p_effective_start_date => ld_effective_start_date
,p_effective_end_date => ld_effective_end_date
);
pay_state_tax_rule_api.update_state_tax_rule
(p_effective_date => ld_effective_date
,p_datetrack_update_mode => lv_datetrack_update_mode
,p_emp_state_tax_rule_id => ln_emp_state_tax_rule_id
,p_object_version_number => ln_st_object_version_number
,p_filing_status_code => LPAD(lct_region2.aei_information15,2,'0')
,p_sit_additional_tax => NVL(fnd_number.canonical_to_number(lct_region2.aei_information18),0)
,p_sit_override_amount => NVL(fnd_number.canonical_to_number(lct_region2.aei_information20),0)
,p_sit_override_rate => NVL(fnd_number.canonical_to_number(lct_region2.aei_information19),0)
,p_withholding_allowances => NVL(fnd_number.canonical_to_number(lct_region2.aei_information17),0)
,p_sit_exempt => lct_region2.aei_information16
,p_supp_tax_override_rate => NVL(fnd_number.canonical_to_number(lct_region2.aei_information21),0)
,p_effective_start_date => ld_effective_start_date
,p_effective_end_date => ld_effective_end_date
);
SELECT pdb.defined_balance_id
FROM pay_defined_balances pdb,
pay_balance_dimensions pbd,
pay_balance_types pbt
WHERE pbt.balance_name = NVL(cp_balance_name,pbt.balance_name)
AND pbt.balance_type_id = NVL(cp_balance_type_id,pbt.balance_type_id)
AND pbd.database_item_suffix= cp_balance_dimension
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND ((pbt.legislation_code = cp_legislation_code and
pbt.business_group_id IS NULL)
OR (pbt.legislation_code IS NULL AND
pbt.business_group_id = cp_business_group_id))
AND ((pdb.legislation_code = cp_legislation_code AND
pdb.business_group_id IS NULL)
OR (pdb.legislation_code IS NULL AND
pdb.business_group_id = cp_business_group_id));
SELECT NVL(addr.add_information17,addr.region_2) state_abbrev,
NVL(addr.add_information19,addr.region_1) county_name,
NVL(addr.add_information18,addr.town_or_city) city_name
from per_addresses addr,
per_all_assignments_f asg
WHERE cp_effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND asg.assignment_id = cp_assignment_id
AND addr.person_id = asg.person_id
AND addr.primary_flag = 'Y'
AND cp_effective_date BETWEEN addr.date_from
AND NVL(addr.date_to,TO_DATE('31/12/4712', 'DD/MM/YYYY'));
SELECT pcn.state_code||'-'||pcn.county_code||'-'||pcn.city_code
FROM pay_us_states pus,
pay_us_counties puc,
pay_us_city_names pcn
WHERE pus.state_abbrev = cp_state_abbrev
AND puc.state_code = pus.state_code
AND puc.county_name = cp_county_name
AND pcn.state_code = puc.state_code
AND pcn.county_code = puc.county_code
AND pcn.city_name = cp_city_name;
SELECT ppf.full_name,
ppf.national_identifier,
ppf.person_id,
pps.date_start,
ppf.employee_number,
ppf.original_date_of_hire,
pps.adjusted_svc_date,
paf.assignment_number,
paf.location_id,
paf.organization_id,
paf.job_id,
paf.position_id,
paf.pay_basis_id,
paf.frequency,
paf.grade_id,
paf.bargaining_unit_code,
paf.collective_agreement_id,
paf.contract_id,
paf.special_ceiling_step_id,
paf.people_group_id,
paf.normal_hours,
paf.time_normal_start,
paf.time_normal_finish,
paf.business_group_id,
paf.soft_coding_keyflex_id,
paf.hourly_salaried_code
FROM per_assignments_f paf,
per_all_people_f ppf,
per_all_people_f ppf1,
per_periods_of_service pps
WHERE paf.person_id = ppf.person_id
AND paf.assignment_id = cp_assignment_id
AND ppf1.person_id = ppf.person_id
AND ((ppf1.current_employee_flag = 'Y'
AND cp_date_earned BETWEEN paf.effective_start_date
AND paf.effective_end_date)
OR
(
ppf1.current_employee_flag <> 'Y'
AND cp_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date))
AND cp_date_earned BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND pps.person_id = ppf.person_id
AND pps.date_start = (SELECT MAX(pps1.date_start)
FROM per_periods_of_service pps1
WHERE pps1.person_id = paf.person_id
AND pps1.date_start <= cp_date_earned);
SELECT payroll_id, period_type, start_date, cut_off_date
FROM per_time_periods
WHERE time_period_id = cp_time_period_id;
select count(*)
from per_spinal_points psp,
per_spinal_points psp2,
per_spinal_point_steps_f psps,
per_spinal_point_steps_f psps2
where psps.step_id = cp_sp_ceil_step_id
and psp.spinal_point_id = psps.spinal_point_id
and psps.grade_spine_id = psps2.grade_spine_id
and psp2.spinal_point_id = psps2.spinal_point_id
and psp.sequence >= psp2.sequence
and cp_effective_date between psps.effective_start_date
and psps.effective_end_date
and cp_effective_date between psps2.effective_start_date
and psps2.effective_end_date
group by psp.spinal_point,
psps.step_id,
psps.sequence,
psps.effective_start_date,
psps.effective_end_date;
select telephone_number_1
from hr_locations hl,
hr_organization_units hou
where hou.organization_id = cp_organization_id
and hou.location_id = hl.location_id;
SELECT pbg.legislation_code
FROM per_business_groups pbg
WHERE pbg.business_group_id = cp_business_group_id
AND cp_effective_date BETWEEN pbg.date_from
AND NVL(pbg.date_to,TO_DATE('31/12/4712','DD/MM/YYYY'));
SELECT LTRIM(RTRIM(
first_name || ' ' ||
DECODE(NVL(LENGTH(LTRIM(RTRIM(middle_names))),0), 0, NULL,
UPPER(SUBSTR(middle_names,1,1)) || '. ' ) ||
pre_name_adjunct || last_name || ' '|| suffix))
FROM per_all_people_f ppf
WHERE ppf.person_id = cp_person_id
AND cp_effective_date between ppf.effective_start_date
and ppf.effective_end_date;
Level Balances and Elements selected in "Payslip Information" Category and
determine the corresponding values. Please note that as of now we are Archiving
this data and XML also gets generated including this information. But the Payroll
Simulator Output will not have these details. Any customer who needs this extra
information can add the required sections in their template.*/
pay_emp_action_arch.get_employee_other_info(p_run_action_id => p_assignment_action_id
,p_assignment_id => p_assignment_id
,p_organization_id => ln_organization_id
,p_business_group_id => ln_business_group_id
,p_curr_pymt_eff_date => p_effective_date
,p_tax_unit_id => p_tax_unit_id
,p_ppp_source_action_id => p_assignment_action_id
,p_ytd_balcall_aaid => p_assignment_action_id
) ;
pay_simulator_pkg.insert_simulation_rows(
p_action_context_id => p_assignment_action_id
,p_action_context_type => 'AAP'
,p_assignment_id => p_assignment_id
,p_tax_unit_id => p_tax_unit_id
,p_effective_date => p_effective_date
,p_tab_rec_data => pay_emp_action_arch.lrr_act_tab
);
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 = cp_legislation_code
AND mul.element_type_id >= 0
AND mul.element_type_id = p_element_type_id
ORDER BY mul.processing_priority,mul.element_type_id;
SELECT DECODE(LENGTH(cp_jurisdiction_code),
11,DECODE(cp_jurisdiction_code,
'00-000-0000', NULL,
DECODE(cp_reporting_name,
'Head Tax Withheld', NULL,
pay_us_employee_payslip_web.get_jurisdiction_name(cp_jurisdiction_code) || ' ')),
8, pay_us_employee_payslip_web.get_jurisdiction_name(
SUBSTR(cp_jurisdiction_code,1,2)||'-000-0000') || ' ')||
DECODE(fl.description,
'', NULL,
NVL(fl.description, cp_reporting_name)) || ' ' ||
DECODE(LENGTH(cp_jurisdiction_code),
8, DECODE(SUBSTR(cp_jurisdiction_code,1,2),
'36', SUBSTR(cp_jurisdiction_code, 4),
pay_us_employee_payslip_web.get_jurisdiction_name(cp_jurisdiction_code)),
11, DECODE(cp_reporting_name,
'Head Tax Withheld', pay_us_employee_payslip_web.get_jurisdiction_name(
cp_jurisdiction_code))) display_name
FROM fnd_common_lookups fl
WHERE fl.lookup_type(+) = 'PAY_US_LABELS'
AND upper(cp_reporting_name) = fl.lookup_code(+);
SELECT SUBSTR(petei.eei_information18,1,80)
FROM pay_element_type_extra_info petei
WHERE petei.element_type_id = cp_element_type_id
AND petei.eei_information18 IS NOT NULL;
Separate Check or Tax Separately selected, we will
consider all the elements in a single set unlike the
Regular archiver where we process these type of elements
separately.
Important : As the current procedure is only for Simulation, it does
not look for YTD Elements and Retro Elements which are
usually fetched in normal Archiver code with extra
processing. The Elements that got processed and generated
[i.e Indirect Elements that got saved like Tax Related
Elements] in current simulation run are only considered by
this procedure.
*/
PROCEDURE get_current_elements(p_source_action_id NUMBER
,p_effective_date DATE
,p_assignment_id NUMBER
,p_tax_unit_id NUMBER
,p_ytd_balcall_aaid NUMBER
,p_legislation_code VARCHAR2
,p_business_group_id NUMBER
)
IS
CURSOR get_element_entry_id( cp_run_action_id NUMBER ,
cp_assignment_id NUMBER ,
cp_element_type_id NUMBER ) IS
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;
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 cp_effective_date BETWEEN pa.date_from
AND NVL(pa.date_to, hr_general.END_OF_TIME)
AND pa.business_group_id = cp_business_group_id
AND pa.region_2 = pus.state_abbrev
AND pa.style = cp_legislation_code;
SELECT peevf.screen_entry_value
FROM pay_input_values_f pivf,
pay_element_entry_values_f peevf
WHERE pivf.element_type_id = cp_element_type_id
AND pivf.name = 'Jurisdiction'
AND peevf.element_entry_id = cp_element_entry_id
AND pivf.input_value_id = peevf.input_value_id;
SELECT DISTINCT pus.state_code
|| '-'
|| puc.county_code
|| '-'
|| punc.city_code
FROM per_all_assignments_f peaf,
hr_locations_all hla,
pay_us_states pus,
pay_us_counties puc,
pay_us_city_names punc
WHERE peaf.assignment_id = p_assignment_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 cp_effective_date BETWEEN peaf.effective_start_date
AND peaf.effective_end_date;
SELECT DISTINCT
DECODE(pec.classification_name,
'Tax Deductions', NULL,
prr.element_type_id) element_type_id,
pec.classification_name,
NVL(DECODE(pec.classification_name,
'Tax Deductions', petl.reporting_name || ' Withheld',
petl.reporting_name), petl.element_name) reporting_name,
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,
pet.processing_priority
FROM 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 paa.source_action_id = cp_source_action_id
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
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.legislation_code = cp_legislation_code
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 pec.classification_name;
SELECT /*+ ORDERED */ DISTINCT
DECODE(pec.classification_name, 'Tax Deductions', null,
pet.element_type_id) element_type_id,
pec.classification_name,
NVL(DECODE(pec.classification_name,
'Tax Deductions', petl.reporting_name || ' Withheld',
petl.reporting_name), petl.element_name) reporting_name,
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,
pet.processing_priority
FROM pay_assignment_actions paa
,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 paa.assignment_id = cp_assignment_id
AND paa.source_action_id = cp_source_action_id
AND prb.assignment_id = paa.assignment_id
AND prb.assignment_action_id = paa.assignment_action_id
AND pet.element_information10 IS NOT NULL
AND NVL(pet.legislation_code,cp_legislation_code) = cp_legislation_code
AND NVL(pet.business_group_id,cp_business_group_id) = cp_business_group_id
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.legislation_code = cp_legislation_code
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 pec.classification_name;
SELECT hours.element_type_id,
hours.element_name,
hours.processing_priority,
hours.rate,
nvl(hours.multiple,1),
hours.hours,
hours.amount
FROM pay_hours_by_rate_v hours
WHERE hours.assignment_action_id = cp_ytd_action_id
AND legislation_code = 'US'
AND hours.element_type_id >= 0
ORDER BY hours.processing_priority,hours.element_type_id;
SELECT petei.eei_information18
FROM pay_element_type_extra_info petei
WHERE petei.element_type_id = cp_element_type_id
AND petei.eei_information18 IS NOT NULL;
SELECT paf.person_id
FROM per_all_assignments_f paf
WHERE paf.assignment_id = cp_assignment_id
AND cp_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date;
SELECT pai.action_information4 earnings,
pai.action_information5 supplemental_earnings,
pai.action_information6 imputed_earnings,
pai.action_information7 pre_tax_deductions,
pai.action_information8 involuntary_deductions,
pai.action_information9 voluntary_deductions,
pai.action_information10 tax_deductions,
pai.action_information11 taxable_benefits,
pai.action_information12 alien_expat_earnings,
pai.action_information13 non_payroll_payments
FROM pay_emp_payslip_action_info_v payslipv,
pay_assignment_actions paa,
pay_action_information pai,
fnd_sessions fs
WHERE paa.assignment_id = cp_assignment_id
AND payslipv.person_id = cp_person_id
AND payslipv.action_context_id = paa.assignment_action_id
AND payslipv.effective_date < cp_effective_date
AND pai.assignment_id = paa.assignment_id
AND pai.action_context_id = payslipv.action_context_id
AND pai.action_context_type = 'AAP'
AND pai.action_information_category = 'AC SUMMARY CURRENT'
AND fs.session_id = USERENV('SESSIONID')
AND payslipv.effective_date < fs.effective_date
ORDER BY payslipv.effective_date DESC,
payslipv.action_context_id DESC;
/* Insert one row for CURRENT and one for YTD */
IF pay_us_payslip_simulation_main.lrr_act_tab.count > 0 THEN
/* CURRENT Values */
ln_index := pay_us_payslip_simulation_main.lrr_act_tab.count;
SELECT paa.assignment_action_id
FROM pay_assignment_actions paa
WHERE paa.source_action_id = cp_source_action_id
AND paa.assignment_id = cp_assignment_id
AND paa.payroll_action_id = cp_payroll_action_id
ORDER BY paa.assignment_action_id DESC;
SELECT ptp.time_period_id,
NVL(ppa.date_earned,ppa.effective_date)
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp
WHERE paa.assignment_action_id = cp_run_assignment_action
AND ppa.payroll_action_id = paa.payroll_action_id
AND ptp.payroll_id = ppa.payroll_id
AND NVL(ppa.date_earned,ppa.effective_date)
BETWEEN ptp.start_date AND ptp.end_date;
pay_simulator_pkg.insert_simulation_rows(p_action_context_id => p_source_action_id
,p_action_context_type => 'AAP'
,p_assignment_id => p_assignment_id
,p_tax_unit_id => p_tax_unit_id
,p_effective_date => p_effective_date
,p_tab_rec_data => pay_us_payslip_simulation_main.lrr_act_tab
);
pay_simulator_pkg.insert_simulation_rows(p_action_context_id => p_source_action_id
,p_action_context_type => 'AAP'
,p_assignment_id => p_assignment_id
,p_tax_unit_id => p_tax_unit_id
,p_effective_date => p_effective_date
,p_tab_rec_data => pay_ac_action_arch.lrr_act_tab
);
ltr_info_arch.DELETE;
pay_simulator_pkg.insert_simulation_rows(p_action_context_id => p_payroll_action_id
,p_action_context_type => 'ACTION INFO'
,p_assignment_id => p_assignment_id
,p_tax_unit_id => p_tax_unit_id
,p_effective_date => p_effective_date
,p_tab_rec_data => ltr_info_arch
);
SELECT DISTINCT paf.organization_id,
paf.business_group_id
FROM per_all_assignments_f paf
WHERE paf.payroll_id = cp_payroll_id
AND paf.assignment_id = cp_assignment_id
AND cp_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date;
SELECT NVL(psi.tax_unit_id,-1)
FROM pay_simulation_information psi
WHERE psi.action_information_category = 'EMPLOYEE DETAILS'
AND psi.action_context_type = 'AAP'
AND psi.assignment_id = cp_assignment_id
ORDER BY psi.action_context_id DESC;
pay_simulator_pkg.insert_simulation_rows(p_action_context_id => p_payroll_action_id
,p_action_context_type => 'PA'
,p_assignment_id => p_assignment_id
,p_tax_unit_id => NULL
,p_effective_date => p_effective_date
,p_tab_rec_data => pay_emp_action_arch.ltr_ppa_arch
);
procedure is inserted into PAY_SIMULATION_INFORMATION
table autonomously so that the data can be used to
generate the Output, post Database level rollback. Any
information required for generating Output, that will not
be available due to rollback should be captured through
archive_data procedure
*/
PROCEDURE archive_data(p_source_action_id NUMBER,
p_effective_date DATE DEFAULT NULL)
IS
CURSOR c_get_action_info(cp_assignment_action_id NUMBER)
IS
SELECT assignment_id,
payroll_action_id
FROM pay_assignment_actions
WHERE assignment_action_id = cp_assignment_action_id;
SELECT ppa.business_group_id,
ppa.consolidation_set_id,
ppa.payroll_id,
ppa.time_period_id,
ppa.effective_date
FROM pay_payroll_actions ppa
WHERE payroll_action_id = cp_payroll_action_id;
SELECT org_information9
FROM hr_organization_information
WHERE org_information_context = 'Business Group Information'
AND organization_id = cp_business_group;
SELECT paa.assignment_action_id,
paa.assignment_id,
paa.tax_unit_id,
paa.payroll_action_id
FROM pay_assignment_actions paa,
pay_run_types_f prt
WHERE paa.source_action_id = cp_master_assignment_action_id
AND paa.run_type_id = prt.run_type_id
AND paa.action_status = 'C'
AND prt.run_method <> 'C'
AND prt.run_method IN ('N','S')
ORDER BY DECODE(prt.run_method,'N',1,2),
paa.assignment_action_id;
/*Capture user selected details into archive table*/
pay_simulator_pkg.capture_user_inputs(ln_assignment_id,
p_source_action_id,
ld_effective_date);
SELECT 'Y'
FROM fnd_descr_flex_col_usage_vl
WHERE descriptive_flexfield_name like 'Action Information DF'
AND descriptive_flex_context_code = p_context_code
AND application_column_name like p_application_column_name
AND application_id = 801 /* Bug 6522667 */
AND enabled_flag = 'Y';
pay_payroll_xml_extract_pkg.g_xml_table.delete();
SELECT DECODE (p_action_information_id,
NULL, 'NULL',
to_char(p_action_information_id)),
DECODE (p_action_context_id,
NULL, 'NULL',
to_char(p_action_context_id))
INTO l_action_information_id,
l_action_context_id
FROM DUAL;
' SELECT NVL((select lei_information13 from hr_location_extra_info hle ,HR_LOCATIONS_ALL HLA '||
'WHERE hle.information_type = ''HR_SELF_SERVICE_LOC_PREFERENCE''');
' SELECT NVL((select org_information13 FROM hr_organization_information '||
'WHERE org_information_context = ''HR_SELF_SERVICE_ORG_PREFERENCE'' and ORG_INFORMATION1=''PAYSLIP''');
' SELECT NVL((select org_information13 FROM hr_organization_information '||
'WHERE org_information_context = ''HR_SELF_SERVICE_BG_PREFERENCE'' and ORG_INFORMATION1=''PAYSLIP''');
'SELECT NVL(hruserdt.get_table_value('||lt_per_bgId_value||',''ONLINE_PAYSLIP_DATE_CONTROL_TBL'',lt_state_cd'||
',lt_element_type,lt_pay_date),''N'') INTO lt_ret_val FROM DUAL ;');
'pay_payroll_xml_extract_pkg.g_custom_xml.delete();');
'pay_payroll_xml_extract_pkg.g_custom_xml.delete();');
'pay_payroll_xml_extract_pkg.g_custom_xml.delete();');
'pay_payroll_xml_extract_pkg.g_custom_xml.delete();');
pay_payroll_xml_extract_pkg.g_xml_table.delete();
SELECT psi.action_information2
FROM pay_simulation_information psi
WHERE psi.assignment_id = cp_assignment_id
AND psi.action_information_category = 'SIMULATION_ACTION_INFORMATION'
AND psi.action_context_type = 'ACTION INFO'
ORDER BY psi.action_information1 DESC,psi.action_information2 DESC;
SELECT fs.effective_date
FROM fnd_sessions fs
WHERE fs.session_id = USERENV('SESSIONID');
SELECT paaf.person_id,
paaf.business_group_id,
paaf.payroll_id,
paaf.pay_basis_id
FROM per_all_assignments_f paaf,
fnd_sessions fs
WHERE paaf.assignment_id = cp_assignment_id
AND cp_effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date;
SELECT ppf.period_type
FROM pay_payrolls_f ppf
WHERE ppf.payroll_id = cp_payroll_id
AND cp_effective_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date;
SELECT pbg.legislation_code
FROM per_business_groups pbg,
fnd_sessions fs
WHERE pbg.business_group_id = cp_business_group_id
AND fs.session_id = USERENV('SESSIONID')
AND fs.effective_date BETWEEN pbg.date_from
AND NVL(pbg.date_to,TO_DATE('31/12/4712','DD/MM/YYYY'));
SELECT org_information7
FROM hr_organization_information hoi
WHERE hoi.organization_id = cp_business_group_id
AND hoi.org_information_context = 'HR_SELF_SERVICE_BG_PREFERENCE'
AND org_information1 = 'SIMPAYSLIP';
SELECT ppb.name
FROM per_pay_bases ppb,
pay_input_values_f piv
WHERE ppb.pay_basis_id = cp_pay_basis_id
AND piv.input_value_id = ppb.input_value_id
AND cp_effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date;
SELECT payslipv.effective_date,
payslipv.action_context_id
FROM pay_emp_payslip_action_info_v payslipv,
pay_assignment_actions paa
WHERE paa.assignment_id = cp_assignment_id
AND payslipv.person_id = cp_person_id
AND payslipv.action_context_id = paa.assignment_action_id
AND payslipv.effective_date < cp_effective_date
ORDER BY payslipv.effective_date DESC;
SELECT gross_pay
FROM pay_ac_emp_sum_action_info_v
WHERE action_context_id = cp_action_context_id
AND action_information_category = 'AC SUMMARY YTD';
DELETE FROM pay_simulation_information
WHERE assignment_id = p_assignment_id
AND action_context_type IN ('INPUTSLOV','SIMDISPLAY')
AND action_context_id = p_assignment_id;
INSERT INTO pay_simulation_information
(action_information_id,
action_context_id,
action_context_type,
action_information_category,
action_information1,
effective_date,
assignment_id
)
VALUES
(pay_simulation_information_s.NEXTVAL,
p_assignment_id,
'INPUTSLOV',
'LAST_PAYSLIP_DATE',
TO_CHAR(ld_last_payslip_date,'DD/MM/YYYY'),
ld_effective_date,
p_assignment_id
);
INSERT INTO pay_simulation_information
(action_information_id,
action_context_id,
action_context_type,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
effective_date,
assignment_id
)
VALUES
(pay_simulation_information_s.NEXTVAL,
p_assignment_id,
'SIMDISPLAY',
'US_SIMULATION_DISPLAY',
lv_pay_basis,
ln_proposed_salary,
ln_annual_salary,
ln_gross_salary_ytd,
ld_effective_date,
p_assignment_id
);
INSERT INTO pay_simulation_information
(action_information_id,
action_context_id,
action_context_type,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
effective_date,
assignment_id
)
SELECT pay_simulation_information_s.NEXTVAL,
p_assignment_id,
'INPUTSLOV',
DECODE(pec.classification_name,
'Earnings','EARNINGS INPUT VALUES',
'Supplemental Earnings','EARNINGS INPUT VALUES',
'Imputed Earnings','EARNINGS INPUT VALUES',
'DEDUCTIONS INPUT VALUES'),
SUBSTR(NVL(NVL(eit.eei_information18,petl.reporting_name),petl.element_name),1,37)||
' - '||SUBSTR(pivtl.name,1,20) element_input_name,
pet.element_type_id,
piv.input_value_id,
pec.classification_name,
pec.classification_id,
pel.element_link_id,
pel.location_id,
ld_effective_date,
p_assignment_id
FROM pay_element_type_extra_info eit,
pay_element_types_f pet,
pay_element_types_f_tl petl,
pay_element_classifications pec,
pay_input_values_f piv,
pay_input_values_f_tl pivtl,
pay_element_links_f pel
WHERE eit.information_type = 'SIMULATION_ELEMENTS'
AND eit.eei_information1 = 'Y'
AND eit.element_type_id = pet.element_type_id
AND (NVL(eit.eei_information2,'-1') = TO_CHAR(piv.input_value_id)
OR NVL(eit.eei_information3,'-1') = TO_CHAR(piv.input_value_id)
OR NVL(eit.eei_information4,'-1') = TO_CHAR(piv.input_value_id)
OR NVL(eit.eei_information5,'-1') = TO_CHAR(piv.input_value_id)
OR NVL(eit.eei_information6,'-1') = TO_CHAR(piv.input_value_id)
OR NVL(eit.eei_information7,'-1') = TO_CHAR(piv.input_value_id)
OR NVL(eit.eei_information8,'-1') = TO_CHAR(piv.input_value_id)
OR NVL(eit.eei_information9,'-1') = TO_CHAR(piv.input_value_id)
OR NVL(eit.eei_information10,'-1') = TO_CHAR(piv.input_value_id)
OR NVL(eit.eei_information11,'-1') = TO_CHAR(piv.input_value_id)
OR NVL(eit.eei_information12,'-1') = TO_CHAR(piv.input_value_id)
OR NVL(eit.eei_information13,'-1') = TO_CHAR(piv.input_value_id)
OR NVL(eit.eei_information14,'-1') = TO_CHAR(piv.input_value_id)
OR NVL(eit.eei_information15,'-1') = TO_CHAR(piv.input_value_id)
OR NVL(eit.eei_information16,'-1') = TO_CHAR(piv.input_value_id))
AND pet.element_type_id = piv.element_type_id
AND ld_effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND pet.element_type_id = petl.element_type_id
AND petl.language = USERENV('LANG')
AND ld_effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND ld_effective_date BETWEEN pel.effective_start_date
AND pel.effective_end_date
AND piv.input_value_id = pivtl.input_value_id
AND pivtl.language = USERENV('LANG')
AND pec.classification_id = pet.classification_id
AND pec.legislation_code = lv_legislation_code
AND pec.classification_name IN
('Earnings',
'Supplemental Earnings',
'Imputed Earnings',
'Involuntary Deductions',
'Pre-Tax Deductions',
'Voluntary Deductions')
AND pel.element_type_id = pet.element_type_id
AND NOT EXISTS
(SELECT NULL
FROM pay_element_set_members pesm
WHERE pesm.element_set_id = ln_exclusion_set_id
AND pesm.element_type_id = pet.element_type_id
)
AND EXISTS
(SELECT NULL
FROM per_all_assignments_f asgt
WHERE asgt.assignment_id = p_assignment_id
AND ld_effective_date BETWEEN asgt.effective_start_date
AND asgt.effective_end_date
AND asgt.business_group_id = pel.business_group_id
AND ((
pel.payroll_id IS NOT NULL
AND pel.payroll_id = asgt.payroll_id
)
OR(
pel.link_to_all_payrolls_flag = 'Y'
AND asgt.payroll_id IS NOT NULL
)
OR(
pel.payroll_id IS NULL
AND pel.link_to_all_payrolls_flag = 'N'
)
)
AND ( pel.organization_id = asgt.organization_id
OR pel.organization_id IS NULL
)
AND ( pel.position_id = asgt.position_id
OR pel.position_id IS NULL
)
AND ( pel.job_id = asgt.job_id
OR pel.job_id IS NULL
)
AND ( pel.grade_id = asgt.grade_id
OR pel.grade_id IS NULL
)
AND ( pel.pay_basis_id = asgt.pay_basis_id
OR
( pel.pay_basis_id IS NULL
AND NOT EXISTS
(SELECT ppb.pay_basis_id
FROM per_pay_bases ppb,
pay_input_values_f piv1
WHERE piv1.element_type_id = pet.element_type_id
AND ld_effective_date
BETWEEN piv1.effective_start_date
AND piv1.effective_end_date
AND ppb.input_value_id = piv1.input_value_id
AND ppb.business_group_id = asgt.business_group_id
AND ppb.pay_basis_id <> asgt.pay_basis_id
)
)
)
AND ( pel.employment_category = asgt.employment_category
OR pel.employment_category IS NULL
)
AND ( pel.people_group_id = asgt.people_group_id
OR pel.people_group_id IS NULL
)
);